MySQL最最基础的一些东西

1. 下载

下载就看看以后发的文件，现在没时间

2. 配置

制作windows服务，基于windows服务管理。

>>>"C:\Program Files\mysql-5.7.31\bin\mysqld.exe" --install mysql57

创建好服务之后，可以通过命令启动和关闭服务，例如：

>>> net start mysql57
>>> net stop mysql57

登录mysql

mysql -u root -p

3. MySQL

安装上数据库之后，就需要开始学习指令了，通过指令让MySQL去做出一些文件操作。

如果将数据库管理系统与之前的文件管理做类比的话：

数据库管理系统	文件管理
数据库	文件夹
数据表	文件夹下的excel文件

接下来，我们先学习数据库（文件夹）相关操作的指令。

3.1 SQL语句 — 数据库管理

当连接上MySQL之后，执行如下指令（一般称为SQL语句），就可以对MySQL的数据进行操作。

查看当前所有的数据库： show databases;

创建数据库：create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

create database day25db;
  
create database day25db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

删除数据库：drop database 数据库名;
进入数据（进入文件）：use 数据库;

# 1.登录MySQL
wupeiqi@wupeiqideMBP ~ % mysql -u root -p

# 2.查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# 3. 创建数据库:  create database 数据库名 default charset 编码 collate 排序规则;
mysql> create database db1 default charset utf8 collate utf8_general_ci;

mysql> show databases;  -- 查看当前所有的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# 4. 删除数据库
mysql> drop database db1;

# 5. 查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# 6. 进入数据库
mysql> use mysql;

# 7. 进入mysql数据库（文件夹），查看此数据库下的所有表(Excel表格)
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| user                      |
+---------------------------+

# 8. 退出
mysql>exit; -- 或 \q

3.2 Python 代码操作

无论通过何种方式去连接MySQL，本质上发送的指令都是相同的，只是连接的方式和操作形式不同而已。

想要使用Python操作MySQL需要安装第三方模块：

pip3 install pymysql

安装完成后，就可以编写代码:

import pymysql

# 连接MySQL（socket）
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
cursor = conn.cursor()

# 1. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()	
print(result) 
输出 
#(('information_schema',), ('mysql',), 
#('performance_schema',), ('sys',))

# 2. 创建数据库（新增、删除、修改）
# 发送指令
cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
conn.commit()

# 3. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('db3',), ('mysql',), ('performance_schema',), ('sys',))

# 4. 删除数据库
# 发送指令
cursor.execute("drop database db3")
conn.commit()

# 3. 查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

# 5. 进入数据库，查看表
# 发送指令
cursor.execute("use mysql")
cursor.execute("show tables")
result = cursor.fetchall()
print(result) # (('columns_priv',), ('db',), ('engine_cost',), ('event',), ('func',), ('general_log',),....

# 关闭连接
cursor.close()
conn.close()

3.3 SQL语句 — 数据表管理

select * from 表名
进入数据库 use 数据库;，查看当前所有表：show tables;
创建表结构
数据表，就相当于Excel表格

安装上数据库之后，就需要开始学习指令了，通过指令让MySQL去做出一些文件操作。

其实在数据库中创建数据库和创建Excel非常类似，需要指定：

表名、列名称、类型（整型、字符串，时间，浮点数等)

3.3.1 创建表

create table tb5(
	id int not null auto_increment primary key,	-- 不允许为空 & 主键 & 自增
    name varchar(16) not null,   		-- 不允许为空
    email varchar(32) null,      		-- 允许为空（默认）
    age int default 3            		-- 插入数据时，如果不给age列设置值，默认值：3
)default charset=utf8;

create table tb5 –> 创造一个名字为tb5的表
– 后面为注解
id int not null auto_increment primary key, – 不允许为空 ,主键 ,自增
- id int –> 创造一列,为整形，名字为id
- not null – > 本列不能为空
- auto_increment –> 自增列，也就是从1开始完后每个加 1
- primary key –> 主键（不允许为空、不能重复) , 一般每个表都有一个主键
- 注意：一个表中只能有一个自增列【自增列，一般都是主键】。
name varchar(16) not null
- varchar(16) – >创造一列，为字符串最多可以储存 16个字节
- not null – > 本列不能为空
email varchar(32) null
- null – > 允许为空
age int default 3
- age int – >创造一列,为整形,名字为age
- default 3 –> 插入数据时，如果不给age列设置值，默认值：3
default charset=utf8;
- 创造表格设置编码

3.3.2 对表进行增删改查

删除表 drop table 表名;
清空表 delete from 表名; 或 truncate table 表名;（速度快、无法回滚撤销等）

修改表

添加列

alter table 表名 add 列名 类型;	-- 如果后面有其他 
alter table 表名 add 列名 类型 DEFAULT 默认值;
alter table 表名 add 列名 类型 not null default 默认值;
alter table 表名 add 列名 类型 not null primary key auto_increment;

删除列

alter table 表名 drop column 列名;

修改列类型

alter table 表名 modify column 列名 类型;

修改列类型 + 名称

alter table 表名 change 原列名 新列名 新类型;

alter table  tb change id nid int not null;
alter table  tb change id id int not null default 5;
alter table  tb change id id int not null primary key auto_increment;
  
alter table  tb change id id int; -- 允许为空，删除默认值，删除自增。

修改列默认值

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;

添加主键

alter table 表名 add primary key(列名);

删除主键

alter table 表名 drop primary key;

3.3.3 常见列类型

整数：

int[(m)][unsigned][zerofill]

int				表示有符号，取值范围：-2147483648 ～ 2147483647
int unsigned	表示无符号，取值范围：0 ～ 4294967295
int(5)zerofill	仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。

例子：

mysql> create table L1(id int, uid int unsigned, zid int(5) zerofill) default charset=utf8;

mysql> insert into L1(id,uid,zid) values(1,2,3); -- 插入数据

mysql> insert into L1(id,uid,zid) -- values(2147483641,4294967294,300000);

mysql> select * from L1;  -- 查看表 
+------------+------------+--------+
| id         | uid        | zid    |
+------------+------------+--------+
|          1 |          2 |  00003 |
| 2147483641 | 4294967294 | 300000 |
+------------+------------+--------+

mysql> insert into L1(id,uid,zid) values(214748364100,4294967294,300000);
ERROR 1264 (22003): Out of range value for column 'id' at row 1	-- 报错

tinyint[(m)] [unsigned] [zerofill]

有符号，取值范围：-128 ～ 127.
无符号，取值范围：0 ～ 255

bigint[(m)][unsigned][zerofill]

有符号，取值范围：-9223372036854775808 ～ 9223372036854775807
无符号，取值范围：0  ～  18446744073709551615

decimal[(m[,d])] [unsigned] [zerofill]

准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

例如：
create table L2(
	id int not null primary key auto_increment,
	salary decimal(8,2)
)default charset=utf8;

例子，看老师文档

字符串：

char(m)

定长字符串，m代表字符串的长度，最多可容纳255个字符。

定长的体现：即使内容长度小于m，也会占用m长度。例如：char(5)，数据是：yes，底层也会占用5个字符；如果超出m长度限制（默认MySQL是严格模式，所以会报错）。
    如果在配置文件中加入如下配置，
        sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。。

注意：默认底层存储是固定的长度（不够则用空格补齐），但是查询数据时，会自动将空白去除。 如果想要保留空白，在sql-mode中加入 PAD_CHAR_TO_FULL_LENGTH 即可。
查看模式sql-mode，执行命令：show variables  like 'sql_mode';

一般适用于：固定长度的内容。

create table L3(
    id int not null primary key auto_increment,
    name varchar(5),
    depart char(3)
)default charset=utf8;

insert into L3(name,depart) values("alexsb","sbalex");

varchar(m)

变长字符串，m代表字符串的长度，最多可容纳65535个字节。

变长的体现：内容小于m时，会按照真实数据长度存储；如果超出m长度限制（（默认MySQL是严格模式，所以会报错）。
    如果在配置文件中加入如下配置，
        sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。

例如：
create table L3(
    id int not null primary key auto_increment,
    name varchar(5),
    depart char(3)
)default charset=utf8;

例子看老师文档

text

text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
  
一般情况下，长文本会用text类型。例如：文章、新闻等。

create table L4(
	id int not null primary key auto_increment,
    title varchar(128),
	content text
)default charset=utf8;

时间：

timestamp

YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00/2037年）

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储，查询时，将其又转化为客户端当前时区进行返回。

对于DATETIME，不做任何改变，原样输入和输出。

date

YYYY-MM-DD（1000-01-01/9999-12-31）

3.3.4 MySQL,用python操作

基于Python去连接MySQL之后，想要进行数据表的管理的话，发送的指令其实都是相同的，例如：

例子：

import pymysql

# 连接MySQL
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
cursor = conn.cursor()

# 1. 创建数据库
"""
cursor.execute("create database db4 default charset utf8 collate utf8_general_ci")
conn.commit()
"""

# 2. 进入数据库、查看数据表
"""
cursor.execute("use db4")
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
"""

# 3. 进入数据库创建表
cursor.execute("use db4")
sql = """
create table L4(
    id int not null primary key auto_increment,
    title varchar(128),
    content text,
    ctime datetime
)default charset=utf8;
"""
cursor.execute(sql)
conn.commit()

# 4. 查看数据库中的表
"""
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
"""

# 5. 其他 drop table... 略过

# 关闭连接
cursor.close()
conn.close()

3.4 数据行

数据行操作的相关SQL语句（指令）如下：

新增数据

insert into 表名 (列名,列名,列名) values(对应列的值,对应列的值,对应列的值);

insert into tb1(name,password) values('武沛齐','123123');
  
insert into tb1(name,password) values('武沛齐','123123'),('alex','123');
  
insert into tb1 values('武沛齐','123123'),('alex','123'); -- 如果表中只有2列

删除数据

delete from 表名;
delete from 表名 where 条件;

delete from tb1;
delete from tb1 where name="wupeiqi";
delete from tb1 where name="wupeiqi" and password="123";
delete from tb1 where id>9;

修改数据

update 表名 set 列名=值;
update 表名 set 列名=值 where 条件;

update tb1 set name="wupeiqi";
update tb1 set name="wupeiqi" where id=1;
  
update tb1 set age=age+1;  -- 整型
update tb1 set age=age+1 where id=2;
  
update L3 set name=concat(name,"db");
update L3 set name=concat(name,"123")  where id=2;  -- concat一个函数，可以拼接字符串

查询数据

select * from 表名;
select 列名,列名,列名 from 表名;
select 列名,列名 as 别名,列名 from 表名;
select * from 表名 where 条件;

select * from tb1;
select id,name,age from tb1;
select id,name as N,age, from tb1;
select id,name as N,age, 111 from tb1;
  
select * from tb1 where id = 1;
select * from tb1 where id > 1;
select * from tb1 where id != 1;
select * from tb1 where name="wupeiqi" and password="123";

3.4.1 Python代码操作

import pymysql

# 连接MySQL，自动执行 use userdb; -- 进入数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()


# 1.新增（需commit）
"""
cursor.execute("insert into tb1(name,password) values('武沛齐','123123')")
conn.commit()
"""

# 2.删除（需commit）
"""
cursor.execute("delete from tb1 where id=1")
conn.commit()
"""

# 3.修改（需commit)
"""
cursor.execute("update tb1 set name='xx' where id=1")
conn.commit()
"""

# 4.查询
"""
cursor.execute("select * from tb where id>10")
data = cursor.fetchone() # cursor.fetchall()
print(data)
"""

# 关闭连接
cursor.close()
conn.close()

其实在真正做项目开发时，流程如下：

第一步：根据项目的功能来设计相应的数据库 & 表结构（不会经常变动，在项目设计之初就确定好了）。
第二步：操作表结构中的数据，已达到实现业务逻辑的目的。

4.MySQL 实现一个用户管理系统

再在程序中执行编写相应的功能实现注册、登录等功能。

import pymysql


def register():
    print("用户注册")

    user = input("请输入用户名：") # alex
    password = input("请输入密码：") # sb

    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb")
    cursor = conn.cursor()

    # 执行SQL语句（有SQL注入风险，稍后讲解）
    # sql = 'insert into users(name,password)values("alex","sb")'
    sql = 'insert into users(name,password) values("{}","{}")'.format(user, password)
    
    cursor.execute(sql)
    conn.commit()

    # 关闭数据库连接
    cursor.close()
    conn.close()

    print("注册成功，用户名：{},密码:{}".format(user, password))


def login():
    print("用户登录")

    user = input("请输入用户名：")
    password = input("请输入密码：")

    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb")
    cursor = conn.cursor()

    # 执行SQL语句（有SQL注入风险，稍后讲解）
    # sql = select * from users where name='wupeiqi' and password='123'
    sql = "select * from users where name='{}' and password='{}'".format(user, password)
    cursor.execute(sql)
    
    result = cursor.fetchone() # 去向mysql获取结果
    # None
    # (1,wupeiqi,123)
        
    # 关闭数据库连接
    cursor.close()
    conn.close()

    if result:
        print("登录成功", result)
    else:
        print("登录失败")

def run():
    choice = input("1.注册；2.登录")
    if choice == '1':
        register()
    elif choice == '2':
        login()
    else:
        print("输入错误")

if __name__ == '__main__':
    run()

所以，你会发现，在项目开发时，数据库 & 数据表的操作其实就做那么一次，最最常写的还是对数据行的操作。

5.关于SQL注入

假如，你开发了一个用户认证的系统，应该用户登录成功后才能正确的返回相应的用户结果。

import pymysql

# 输入用户名和密码
user = input("请输入用户名：") # ' or 1=1 -- 
pwd = input("请输入密码：") # 123


conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8",db='usersdb')
cursor = conn.cursor()

# 基于字符串格式化来 拼接SQL语句
# sql = "select * from users where name='alex' and password='123'"
# sql = "select * from users where name='' or 1=1 -- ' and password='123'"
sql = "select * from users where name='{}' and password='{}'".format(user, pwd)
cursor.execute(sql)

result = cursor.fetchone()
print(result) # None，不是None

cursor.close()
conn.close()

如果用户在输入user时，输入了： ` ‘ or 1=1 – ` ，这样即使用户输入的密码不存在，也会可以通过验证。

为什么呢？

因为在SQL拼接时，拼接后的结果是：

select * from users where name='' or 1=1 -- ' and password='123'

注意：在MySQL中 -- 表示注释。

那么，在Python开发中如何来避免SQL注入呢？

切记，SQL语句不要在使用python的字符串格式化，而是使用pymysql的execute方法。

import pymysql

# 输入用户名和密码
user = input("请输入用户名：")
pwd = input("请输入密码：")

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')

cursor = conn.cursor()

cursor.execute("select * from users where name=%s and password=%s", [user, pwd])
# 或
# cursor.execute("select * from users where name=%(n1)s and password=%(n2)s", {"n1": user, 'n2': pwd})

result = cursor.fetchone()
print(result)

cursor.close()
conn.close()

6. 总结

除了【第5步用户授权】以外，现在使用的是默认root账户（拥有最大权限），上述所有的过程已讲解完毕。

本节内容大家需要掌握：

安装和启动MySQL
SQL语句：
- 数据库操作
- 表操作
- 数据行操作
基于Python操作MySQL
注意SQL注入的问题

Mysql最基础的

Study Python | Study linux

MySQL最最基础的一些东西

1. 下载

2. 配置

3. MySQL

3.1 SQL语句 — 数据库管理

3.2 Python 代码操作

3.3 SQL语句 — 数据表管理

3.3.1 创建表

3.3.2 对表进行增删改查

3.3.3 常见列类型

整数：

字符串：

时间：

3.3.4 MySQL,用python操作

3.4 数据行

3.4.1 Python代码操作

4.MySQL 实现一个用户管理系统

5.关于SQL注入

6. 总结

MySQL最最基础的一些东西

1. 下载

2. 配置

3. MySQL

3.1 SQL语句 — 数据库 管理

3.2 Python 代码操作

3.3 SQL语句 — 数据表 管理

3.3.1 创建表

3.3.2 对表进行 增删改查

3.3.3 常见列类型

整数：

字符串：

时间：

3.3.4 MySQL,用python操作

3.4 数据行

3.4.1 Python代码操作

4.MySQL 实现一个 用户管理系统

5.关于SQL注入

6. 总结

3.1 SQL语句 — 数据库管理

3.3 SQL语句 — 数据表管理

3.3.2 对表进行增删改查

4.MySQL 实现一个用户管理系统