Python—MySQL

---

数据库管理系统-DBMS(Database Management System)

     数据库管理系统(DBMS) 是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。
     它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。 用户通过DBMS访问数据库中的数据，数据库管理员也通过DBMS进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。
     种类：SqlServer, Oracle, sqlite, access, MySQL…

---

一、安装MySQL

window:https://dev.mysql.com/downloads/mysql/

下载-解压-加入环境变量

mysqld --initialize-insecure    # 初始化用户 id:root 没密码
1

使用方法1（手动运行）：

mysqld    # 运行服务端
mysql -u root -p    # 运行客户端
1
2

使用方法2（默认开机运行mysql）：

mysqld path --install    # 给windows安装mysql服务
mysqld path --remove    # 给windows移除mysql服务
# 若已加入环境变量：
mysqld --install
mysqld --remove
1
2
3
4
5

# Install/Remove of the Service Denied! 如果出现这个错误以管理员身份打开命令提示符（win10：右键开始-A）

启动/停止mysql服务：

net start mysql    # 在windows服务中启动mysql
net stop mysql    # 在windows服务中停止mysql
# 或者在任务管理器中启动/停止服务
# 如果报错，请用管理员身份打开命令提示符
1
2
3
4

---

二、MySQL数据库

1、概念

	数据库: database
	表: table
	行
1
2
3

2、默认数据库

	mysql: 用户权限相关
	test: 用户测试
	infomation_schema: MySQL本身构架相关
1
2
3

3、授权

用户管理特殊命令

    创建用户/修改密码：

create user 'name'@'IP_address' identified by 'password';   # 创建用户
drop user 'name'@'IP_address';    # 删除用户
rename user 'name'@'IP_address' to name@IP_address;    # 修改用户
alter user 'name'@'ip_address' identified by password;    # 新版修改密码
# set password for 'name'@'IP_address' = Password('new password');   # 旧版修改密码
1
2
3
4
5

用户登陆

mysql -u name -h 127.0.0.1 -p
# 输入密码
mysql -u root -p    # 没有-h 默认为localhost
1
2
3

赋予权限

show grants for 'name'@'IP'    # 查看权限
grant 权限 on 数据库.表 to 'name'@'IP'    # 授权
revoke 权限 on 数据库.表 from 'name'@'IP'    # 取消权限

"""
权限：
all privileges    除grant外的所有权限
usage             无访问权限
alter             alter table
alter routine     alter procedure/drop procedure
create            create table
create routine    create procedure
create temporary  create temporary tables
create user       create user/drop user/rename user/revoke all privileges
create view       create view
delete            delete
drop              drop table
execute           call/存储
file              select into oufile/load data infile
grant option      grant/revoke
index             index
insert            insert
lock tables       lock table
process           show full processlist
select            select
show databases    show databases
show view         show view
update            update
reload            flush
shutdown          mysqladmin shutdown
super             change master/kill/logs/purge/master/set global
replication client    服务器位置访问
replication slave    
"""
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

授权中使用通配符

create user 'name'@'192.168.1.%' identified by 'password';    # 表示name在192.168.1.下所有子网都可以登陆
1

---

三、数据表基础

1. 数据库操作

show databases;              # 显示所有数据库名称
create database db_name;     # 创建数据库
create database db_name default charset utf8 collate utf8_general_ci;   # 创建数据库设置编码
use db_name;                 # 进入数据库
drop database db_name;       # 删除数据库
1
2
3
4
5

2. 数据表操作

创建表

1. $表名$
2. $列名$
3. $类型$

'''数值:'''
# 整数
bit [(n)]	# 二进制位，n表示二进制位的长度(1-64),默认为1
tinyint[(n)][unsigned][zerofill]	# 符号0 ~ 2^8-1
int [(n)] [unsigned] [zerofill]		# 有符号 -2^31 ~ 2^31-1, 无符号 0 ~ 2^32-1
bigint [(n)] [unsigned] [zerofill]  
# 小数
decimal		# 精确小数
	nid decimal(5, 10) # 保留10位小数,多出的四舍五入，少了用0补足
float:
double:
'''字符串:'''
# 定长
char	#
	# 长度以设置为准，浪费空间，但是查询速度相对较快
# 变长
varchar 
	# 节省空间，但是查找速度相对较慢
text
mediumtext
longtext
'''二进制数据:'''
TinyBlob、Blob、MediumBlob、LongBlob
	# 上传文件
	# Blob, 强制二进制方式
	# varchar(65), "D:\program" 将上传的文件保存到文件
	# 一般将文件路径保存到数据库，从数据库中取到路径操作文件
'''时间:'''
date
	# YYYY-MM-DD
time
	# HH:MM:SS
year
	# YYYY
datetime
	# YYYY-MM-DD HH:MM:SS
timestamp
	# YYYYMMDD HHMMSS
'''枚举enum'''
Enum week:
	x = 1001
	y = 1002
	z = 1003
print(Enum.x)	# 1001

create table shirts(
					nid int primary key,
					size enum('small', 'medium', 'large') not null
);
	# insert 插入时size 列只能从enum中选择一个
'''集合set'''
create table shirts(
			color set('blue', 'red', 'orange', 'green')
);
# insert插入时color列可以从set中选择1或多个
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

4. $是否可以为空$：NULL/not NULL
5. $默认值$：default 0
6. $自增列$：auto_increment (自增列必须是键-索引)
7. $engine=innodb$
8. $默认编码$：default charset=utf8

 '创建表'     # 表名   列名 类型 是否可以为空 自增列 主键
create table tb_name(nid int,
					name varchar(20));
create table tb_name(nid int NULL default 0, 
					 name varchar(20) not NULL, 
					sex varchar(20) not NULL)engine=innodb default charset=utf8;
'设置自增列' # 自增列数据会随着数据插入自增，保证此列数据不重复
create table tb_name(nid int not NULL auto_increment primary key,
					 name varchar(20) NULL)engine=innodb default charset=utf8;
1
2
3
4
5
6
7
8
9

9. $主键-索引(加速查找)$：primary key(一张表只能有一个主键，唯一不能重复，not NULL, 一般情况将自增列设置为主键)
       $唯一列-约束$：数据唯一但是可以为null
   $可以多列组合成唯一列或主键列$

create table tb_name(nid int not NULL auto_increment,
                     name varchar(20) NULL,
                     gender varchar(20),
                     primary key(nid, name)
)engine=innodb default charset=utf8;
1
2
3
4
5

10. $外键$：两张表建立约束, 一对多

	# 创建表1 -- 学号 名字 年龄 班级id
create table student1(nid int not null auto_increment primary key,
                      name varchar(20) not null,
                      age int not null,
                      class_nid int not null)engine=innodb default charset=utf8;
# 创建表2 -- 班级id 班级名称
create table class(nid int not null auto_increment primary key,
                    c_name varchar(20) not null)engine=innodb default charset=utf8;
'''创建表后添加表关联'''
alter table student1 add constraint fk_s_c foreign key student1(class_nid) references class(c_name);
'''此时添加表1数据中class_nid如果没有在表2中的nid，语句会报错'''


'''创建表时直接建立关联'''
# 先创建表1 -- 班级id 班级名称（代码如上）
# 创建表2
create table student1(nid int not NULL primary key,
                    name varchar(20) not null,
                    age int not null,
                    class_nid int not null,
                    constraint fk_s_c foreign key (class_nid) references class(nid)
)engine=innodb default charset=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

表操作

show tables;             # 显示所有表
desc tb_name;            # 显示表描述
drop table tb_name;      # 删除表
delete from tb_name;     # 清空表内容
truncate table tb1;      # 清空表内容,自增回到原点
select * from tb_name;   # 查看表内容
'''修改列'''
alter table 表名 add 列名 类型;                # 添加列
alter table 表名 drop column 列名;             # 删除列
alter table 表名 modify column 列名 类型;      # 修改类型
alter table 表名 change 原列名 新列名 类型;     # 修改列名和类型
'''修改主键'''
alter table 表名 add primary key(*列名);        # 添加主键
alter table 表名 drop primary key;              # 删除主键
alter table 表名 modify 列名 int, drop primary key;    # 删除主键
'''修改外键'''
alter table 从表 add constraint fk_1_2 foreign key 从表(字段) references 主表(字段);
alter table 表名 drop foreign key 外键名称;
'''修改默认值'''
alter table 表名 alter 列名 set default 1000;
alter table 表名 alter 列名 drop default;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3. 数据行操作

增

insert into tb_name(name, age) values('wolf', 25);
# 增加多行
insert into tb_name(name, age) values('wolf', 25), ('name2', 18), ('name3', 20);
# 从一个表复制到另外一个表
insert into 表1(列，列，列) select 列，列，列 from 表2		# 两个表对应列必须数据类型一样或者可转换
1
2
3
4
5

删

delete from 表 where 列=value;
delete from 表 where nid>3;
> < = != and or
1
2
3

改

udpate 表 set name='wolfx',age=22 where name='wolf' and age>25;
1

查

select * from 表;
select * from 表 where nid>5;
select nid, name from 表;
1
2
3

其它*

1. 条件

select * from 表 where nid > 4 and name != 'wolf' and age > 18;
							    age not in (27, 28, 29);
							    age in ( 12, 15, 18);
							    age in (select age from 表);
							    age between 21 and 29;
1
2
3
4
5

2. 通配符 -模糊匹配

select * from 表 where name like 'wolf%';	# 以wolf开头的多个字符
								 '%lf_'; 	# 以wolf开头的一个字符
1
2

3. 分页

select * from 表 limit 7;		# 表前7行
					   7, 11;	# 表7 ~ 18行, 从第7行开始的11行
					   11 offset 7;	# 表7 ~ 18行
1
2
3

4. 排序

select * from 表 order by nid asc;	# 按nid排序，顺序
							  desc  # 按nid排序，逆序
select * from 表 order by nid asc, name desc;	# 先按nid顺序，相同的再按name逆序
1
2
3

5. 分组

# 按nid分组
select nid from 表 group by nid;	
# 按名字分组后取出最高成绩
select name, max(score) from 表 group by name;	
# 按名字分组后计算总成绩，最低分数，计数各id数量，并给表头重命名
select name, sum(score) as a_score, count(id) as c_id, min(score) from 表 group by name;	
# 对于使用聚合条件取到的数据，要使用having进行匹配，不能用where
select name, sum(score) as a_score from 表 group by name having a_score > 650;	
1
2
3
4
5
6
7
8

6. 组合

select nid from 表1 union select nid from 表2;	# union 联合显示2张表的数据，默认去重
select nid from 表1 union all select nid from 表2;	# union all 不去重
1
2

7. 连表**

select * from 表1, 表2;		# 将表2所有数据与表1每行数据对应显示
select * from 表1, 表2 where 表1.列 = 表2.列;	# 显示表1某列与表2某列对应相等时的数据
select * from 表1 left join 表2 on 表1.列 = 表2.列;		# 主表在前
# 可以连接多个表
select * from 表1 left join 表2 on 表1.列 = 表2.列 
left join 表3 on 表1.列=表3.列 
left join 表4 on 表2.列=表4.列;
select * from 表2 inner join 表1 on 表1.列 = 表2.列;	# 在left join基础上去掉Null数据
# 优先用left join ，效率更高
1
2
3
4
5
6
7
8
9

8. 临时表**

select 表1.nid, tb2.name from (select nid from 表2) as tb2 left join 表3 on 表1.nid > 18;
1

9. 去重distinct

# 查询哪些学生有得分记录，可能有多门选课，去掉重复项
select distinct student_id from score;
1
2

10. avg

# 计算分组之后某列数据各组的平均值
select avg(num) from score group by student_id having avg(num)>60;
1
2

11. 试图view

# 将临时表创建为试图，下次调用时直接调用v_name作为临时表名字即可，mysql会自动解析v_name的sql语句
create view v_name as 
	select 表1.列, 表2.列 from 表1 left join 表2 on 表1.列=表2.列;
# 直接调用v_name
select v_name.列 from v_name;
1
2
3
4
5

12. 存储过程

# 存储过程（写在客户端，类似一个函数，在客户端中直接调用）
delimiter //		# 将;终止符修改为//
create procedure p_name()
begin
	select * from 表;
end //
delimiter;		# 将//终止符改回;

# 取出存储结果
call p_name()
1
2
3
4
5
6
7
8
9
10

4. 报错处理

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
1

翻译：错误1055(42000)：选择列表的表达式#2不在GROUP BY子句中，并且包含非聚合列‘xxxxxx’，该列在功能上不依赖于GROUP BY子句中的列；这与sql_mode=only_full_group_by不兼容

意思是：$select$中选择显示的列名必须有出现在$group$ $by$后面的

---

四、Python中使用pymysql

1. 安装pymysql

pip install pymysql
1

pymysql: 专门用来操作MySQL的python模块

2. 操作

import pymysql


# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='test', charset='utf8')
# 创建游标
# cursor = conn.cursor()	# 查看数据时获取到的数据为元组
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)		# 取数据时将数据改为字典形式
# 执行SQL，并返回影响行数
effected_row = cursor.execute('insert into table values('xxx', xxx)')
# 提交操作
conn.commit()

new_nid = cursor.lastrowid		# 获取最新的一条自增列数据
print(new_nid)

# 关闭游标
cursor.close()
# 关闭连接
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3. 注意事项*

1. 一种错误的插入字符串的方式：

import pymysql


# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='test', charset='utf8')
# 创建游标
cursor = conn.cursor()	

data = input('请输入成绩：')
sql = 'insert into table(name) values('%s')'%data
# 执行SQL，并返回影响行数
effected_row = cursor.execute(sql)

# 提交操作
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

以上操作会造成$SQL注入$，是$绝对不能使用$的。

$别人可能不需要密码就能登陆数据库，并获取数据库内容$

'''用户登陆'''

import pymysql


# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='test', charset='utf8')
# 创建游标
cursor = conn.cursor()
1
2
3
4
5
6
7
8
9
10

$SQL注入：$

sql = "select * from table where user_id='%s' and pwd='%s'"	# 可以取到数据

# 执行SQL，并返回影响行数
effected_row = cursor.execute(sql)

user_id = "xxx' or 1=1 --"
pwd = '000'
sql = sql%(user_id, pwd)
sql = "select * from table where user_id='xxx' or 1=1 --' and pwd='000'"	
# -- 在mysql中为注释语句，会注释后面的条件，1=1为已成立条件
'''输入数据时，在数据中加入绝对成立的条件，和注释符号 -- ，会改变sql语句，从而导致数据库侵入'''

1
2
3
4
5
6
7
8
9
10
11
12

# 提交操作
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
1
2
3
4
5
6

2. 正确的参数传递方式：

data = input('请输入成绩：')
# 执行SQL，并返回影响行数
effected_row = cursor.execute('insert into table(name) values(%s)', data)
# 多条参数
effected_row1 = cursor.execute('insert into table(nid, name, age) values(%s, %s, %s)', (1, 'wolf', 25))
# 多列参数
list1 = [(2, 'Peter', 22), 
(3, 'Michale', 21), 
(4, 'Lion', 23)]
effected_row1 = cursor.executemany('insert into table(nid, name, age) values(%s, %s, %s)', list1)
1
2
3
4
5
6
7
8
9
10

其余SQL操作替换掉SQL语句即可

3. 查看数据表内容

data = cursor.execute('select * from table')

# res = data.fetchone()		# 取第一条数据
# res = data.fetchmany(4)	# 取4条数据，以一个元组输出，即使是一条数据
res = data.fetchall()
'''取数据时会移动指针'''
print(res)

# 移动指针
cursor.scroll(0, mode='abssolute')	# 指针直接回到多少行，绝对
cursor.scroll(1, mode='relative')	# 指针向下走一行，相对
cursor.scroll(-1, mode='relative')	# 指针向上走一行，相对
''' '''
'''查看数据不需要提交操作'''
# conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

4.Pymysql中执行存储过程

# 执行存储过程 （客户端）
coursor.callproc('p_name')
# 取出数据
res = cursor.fetchall()
print(res)
1
2
3
4
5