Python下使用MySQL(包括MySQL教程)

本文最后更新于:2022年7月21日 下午

Python下使用MySQL(包括MySQL教程)

MySQL基本使用

简介

MySQL是一个开源关系型数据库管理系统,即RDBMS(Relational Database Management System)。使用SQL语言访问数据库。

关系型数据库依据关系模型来创建的数据库,关系模型包含“一对一、一对多、多对多”等,分别对应一本书的ISBN、一本书的作者、一个班的老师和学生。一个关系型数据库就是由二维表及其之间的练习组成的一个数据组织。

关系 一张二维表,每个关系都有个名字,即表名
元组 行,在数据库中叫做记录
属性 列,在数据库中交做字段
属性的取值范围,就是一列的取值限制(数字/字符串)
关键字 唯一标识元组的属性,在数据库中叫主键,由一个或多个列组成
关系模式 对关系的描述。关系名(属性1,属性2…)在数据库中叫表结构

非关系型数据库类似一个巨大的map数据结构或者键值对数据结构。结构不固定,每一个元组可以有不一样的字段。

MySQL由瑞典MySQL AB公司开发,这时候Oracle和MySQL是对头,后来被Sun公司收购,Sun后来又被Oracle公司收购。

MySQL的服务结构

graph LR
A(MySQL-client) -->|SQL|B[MySQL-server]
	B --> A
	B --> C[数据库1]
		C --> C1[数据表1]
		C --> C2[数据表2]
		C --> C3[数据表3]
	B --> D[数据库2]
	B --> E[数据库3]

MySQL通过SQL语言来与服务器沟通,一个数据库服务器可以有多个数据库,每个数据库可以有多个数据表。

包括数据类型约束

类型 大小 范围(signed) 范围(unsigned) 用途
TINYINT 1 字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
类型 大小 (字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值,时间戳
类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据
约束 解释
主键(Primary key) 物理上存储的顺序
非空(not NULL) 不允许填空值
唯一(unique) 不允许重复
默认(default) 不填写时用默认值
外键(foreign key) 只能填写另一处字段的值

SQL语言

简介

SQL(Structured Query Language),指结构化查询语言。SQL语言不区分大小写。

SQL语句的分号不是必须的,使用分号可以分隔多条SQL语句,可以在对服务器相同请求中执行一条以上的SQL语句。

数据库操作(不常用)

1
2
3
4
5
6
7
8
9
10
-- 查看所有数据库
show databases;
-- 使用数据库
use 数据库名;
-- 查看当前数据库
select database();
-- 创建数据库
create database 数据库名 charset=utf8;
-- 删除数据库
drop database 数据库名

数据表操作

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
-- 查看当前所有数据表
show tables;
-- 查看表结构
desc 名字
-- 删除表
drop table 名字
-- 查看表的创建语句
show create table 名字
-- 创建表
create table 表名(字段名字 数据结构 约束,字段名字 数据结构);
create table people(
id int unsigned primary key not null auto_increment,
age int unsigned,
height decimal(5,2) default 1.70
gender enum("男","女","保密") default "保密"
name varchar(30)
);
-- 这个表id是主键,且不能空,且自动增长;age是无符号类型;height是2位小数的5位(包括小数位)数;gender只能有三种值,默认为保密

-- 增删查改字段
alter table 名字 操作 字段名字 数据结构 约束
-- 添加
alter table people add birthday datetime
-- 修改-不重命名
alter table people modify birthday date
-- 修改-重命名
alter table people change birthday birth date
-- 删除
alter table people drop height

数据的增删改查(重重重点)(CURD-create update retrieve delete)

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
-- 增加
-- 完全插入:参数必须对应字段
insert into 表名字 values(字段参数)
-- 若有些字段不想写,且可以不用写,可用null或者default占位
insert into people(default,13,1.80,"男","Tom")
-- 部分插入
insert into 表名字 (字段名) values (加入的数据)
insert into people (age, name) values (13,"Jack")
-- 多行插入
insert into 表名字 values (字段参数1),(字段参数2)
insert into 表名字 (字段名) values (加入的数据1),(加入的数据2)

-- 修改
-- 修改所有记录该字段的值
update 表名字 set 字段1=新值1,字段2=新值2
-- 修改一定某个记录中字段的值 where
update 表名字 set 字段=新值 where 字段与值关系(可用大于小于号)
update people set age = 18 where id=1

-- 删除
-- 清空表!别用!
delete from 表名字
-- 删除某个记录
delete from 表名字 where
-- 逻辑删除,新增一个is_delete操作,标记这行可不可用
alter table 表名字 add is_delete bit default 0
update 表名字 set is_delete where ...

-- 查询(基本)
-- 查询表所有记录
select * from 表名字
-- 查询某个记录
select * from 表名字 where 字段与值关系
-- 查询某个字段
select 字段名字 from 表名字
-- 指定别名,指定别名后不能用本名了
select 字段名字 as 别名 from 表名字 as 别名
select 表名.字段名 from 表名
-- 指定列的顺序
select 第一个字段,第二个字段 from 表名字
-- 去重
select distinct 字段 from 表名

查询

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
-- 条件查询
-- 使用where关键字
> < >= <= -- 和别的语言一样
= -- 单等于表示判断
<> != -- 不等于
and or not -- 与或非,同Python
() -- 括号,优先级很高

-- 模糊查询
-- 使用like关键字
-- % 替换0个或多个 | _ 替换一个 | [charlist] 字符列中任意单一字符 | []不在字符列中的任何单一字符
select name from people where name like "T%" -- 名字以T开头
-- 使用rlike关键字
-- 正则表达式
select name from people where name rlike "正则表达式"

-- 范围查询
-- (非连续)使用in和not in关键字
select name from people where age in (12,18,6) -- {12,18,6}
-- (连续)使用between...and...关键字和not between...and...关键字 左闭右闭区间
select name from people where age between 6 and 18 -- [6,18]

-- 空判断
-- 使用is null关键字和is not null关键字
select name from people where age is null

排序

1
2
3
4
5
6
7
-- order by 字段
-- asc 升序(从小到大) desc 降序
select name from people order by age
select name from people order by age asc
-- order by 多个字段
-- 在第一个字段相同情况下,再按第二个字段排...
select name from people order by age asc, id desc

聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 聚合函数只能输出一个结果
-- count 总数
-- 就是计算输出的东西一共多少行,结果表头是count(*),可用as
select count(*) from people
select count(*) as "人数" from people
-- max 最大值 min 最小值
select max(age) from people
select min(height) from people
-- sum 求和
select sum(age) from people
-- avg 平均
select avg(age) from people
select sum(age) / count(*) from people -- select后可以用运算和多个函数
-- round(x,保留小数位数) 四舍五入
select rount(avg(age),1) from people -- 保留平均数的一位小数

分组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- group by
-- xxx是能唯一标记分组后各组之间不同的字段
-- xxx不能填*
select gender from people group by gender -- 1*

-- 与聚合函数一起用,查找不同性别各自的人数
select gender,count(*) from people group by gender

-- group_concat(字段1,字段2) 写什么有什么,链接所有参数
-- 查询group中某字段的总和
select gender,group_concat(name) from people group by gender -- 2*
select gender,group_concat(name,age) from people group by gender
select gender,group_concat(name,"_",age) from people group by gender -- 3*

-- 与where一起用,先判断,然后从中分组
select gender,group_concat(name) from people where age<18 group by gender

-- having 显示符合条件的分组,与where不同的是写在group后面
-- 所以where是对表进行判断,having是对分组结果进行判断
select gender,group_concat(name) from people group by gender having min(age)<5 -- 4*

1* 的结果

gender count(*)
5
3

2* 的结果

gender group_concat(name)
Mike, Leo, Tom
Lucy, Mary

3* 的结果

gender group_concat(name,”_”,age)
Mike_12, Leo_18, Tom_6
Lucy_12, Mary_3

4* 的结果

gender group_concat(name)
Lucy, Mary

分页

1
2
3
4
5
6
7
8
9
10
11
-- 使用limit限制
-- limit 数字m 查询前m个
select * from people limit 5
-- limit 数字n,数字m 查询从n开始,查询共m个
-- n从0开始
select * from peolpe limit 2,6 -- [3,9]
select * from peolpe limit 0,5 -- [1,5]
select * from peolpe limit 5,5 -- [6,10]
select * from peolpe limit 10,5 -- [11,15]
-- limit放最后
select * from peolpe order by age limit 0,5 -- 按age排序的[1,5]

链接查询

结合多个表的查询,比如一张表存放班级信息,另一张表存放同学信息,要显示同学信息的同时再显示该同学的班级信息,就用链接查询。

略。

自关联

一个表中某个字段用的是另一个字段的值

比如公司中上下属关系,A是B的属下,B是D的属下,C是D的属下,于是数据表中可以新建一个字段,表示每个员工的老板是谁,同时这个老板也在同一个表中,并且也有他的老板。

略。

子查询

select中嵌入另一个select语句

比如查找人群中最高的人的信息,需要select最高的人,然后select *

略。

Python 与 MySQL 交互

graph LR
A(开始) --> B[创建connection]
B --> C[获取cursor]
C --> D[操作]
D --> E[关闭cursor]
E --> F[关闭connection]
F --> G(结束)

查询

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
from pymysql import *
def main():
# 创建connection
conn = connect(
host='localhost',
port=3306,
user='root',
password='root',
database='study',
charset='utf8'
)
# 获取cursor
cursor = conn.sursor()

# 执行sql语句获取数据 select返回获取的行数
cursor.execute("""select * from people;""")
# 从游标中获取数据(fetch)
# fetchone()得到一个元组,然后游标向下走一行
oneline = cursor.fetchone()
# fetchmany(int)元组里面套元组,然后游标向下走那么多行
manylines = cursor.fetchmany(3)
# fetchall() 元组里面套元组,获取游标下所有
alllines = cursor.fetchall()

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

增删改

增删改操作需要增加一个提交connection.commit()操作

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
from pymysql import *
def main():
# 创建connection
conn = connect(
host='localhost',
port=3306,
user='root',
password='root',
database='study',
charset='utf8'
)
# 获取cursor
cursor = conn.sursor()

# 执行sql语句 返回生效行数
cursor.execute("""insert into people(name, age) values("Ash", 31);""") # 增
cursor.execute("""delete from people where id=6 ;""") # 删
cursor.execute("""update people set age=18 where name="Tom";""") # 改

# 取消所有请求 但是自动增长(例如id)会持续生效
# conn.rollback()
# 提交请求
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

防止SQL注入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
## 前略

name = input("请输入你要查询的人的名字:")
cursor.execute("""select * from people where name = "{}" """.format(name))
# 输入 Tom
# 结果 (1,18,1.80,"男","Tom")
# 输入 "or 1=1 or "1
# execute内容: select * from people where name = ""or 1=1 or "1"
# 结果输出所有人的名字
# SQL注入!!!!!

# 防止方法
# 构建参数列表
params = [name]
sql = "select * from people where name=%s, id=%d, age=%d "
cursor.execute(sql,params) # 自动填充

## 后略