MySQL数据库(Python)_pymysql

常用指令

一、数据库安装（Windows版）

到mysql数据库官网下载数据库：https://dev.mysql.com/downloads/mysql/，点击download

进去之后再点No thanks, just start my download.

下载完后，我们将 zip 包解压到相应的目录，这里我放在 D:\MySQL\mysql-8.0.32-winx64 下，在文件夹下创建 my.ini 配置文件，内容如下

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\MySQL\\mysql-8.0.32-winx64
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

在该路径下打开终端，对数据库进行初始化，用户管理模式打开cmd（如果切换不到该目录，则运行下D:）,cd到该文件夹的D目录，然后运行指令mysqld --initialize --console（刚开始运行mysql --initialize --console报错），运行之后，后面的是数据库密码

接着可以安装数据库，修改密码，在该目录下执行指令mysqld --install mysql安装数据库

接着启动数据库服务net start mysql

连接数据库mysql -uroot -p，输入刚刚的密码

运行指令修改密码：ALTER USER 'root'@'localhost' IDENTIFIED BY '新的密码';

在环境变量的系统变量中找到Path，点击编辑，在其中加入该数据库的路径，添加之后，要运行数据库就可以打开cmd直接使用了，不用到指定的目录

二、数据库概念

数据库就是存储和管理数据的仓库，数据按照一定的格式进行存储，用户可以对数据库中的数据进行增加修改、删除、查询等操作。数据库的分为关系型数据库和非关系型数据库。

关系型数据库是指采用了关系模型来组织数据的数据库，简单来说就是二维表格模型，好比Excel文件中的表格，强调使用表格的方式存储数据。关系型数据库核心元素：数据行、数据列、数据表、数据库（数据表的集合），常用关系数据库：MySQL、SQLite、Oracle等
非关系型数据库强调Key-Value的方式存储数据，常见数据库：MongoDB、Redis

数据库特点：持久化存储、读写速度极高、保证数据的有效性

关系型数据库管理系统是为管理关系型数据库而设计的软件系统，如果要使用关系型数据库就需要安装数据库管理系统，其实就是一个应用软件。关系型数据库管理系统分为关系型数据库服务端软件和关系型数据库客户端软件。
关系型数据库服务端软件主要负责管理不同的数据库，而每个数据库里面会有一系列数据文件，数据文件是用来存储数据的，其实数据库就是一系列数据文件的集合。
关系型数据库客户端软件主要负责和关系型数据库服务端软件进行通信,向服务端传输数据或者从服务端获取数据.

SQL是结构化查询语言，是一种用来操作RDBMS的数据库的语言，也就是说通过 SQL可以操作 oracle,sgl server,mysql,sglite 等关系型的数据库。SQL语言不区分大小写。SQL的作用是实现数据库客户端和数据库服务端之间的通信，SQL就是通信的桥梁。
SQL语言主要分为:

DQL:数据查询语言，用于对数据进行查询，如select
DML:数据操作语言，对数据进行增加、修改、删除，如insert、update、delete。
TPL: 事务处理语言，对事务进行处理，包括begin transaction、commit、rollback。
DCL:数据控制语言，进行授权与权限回收，如grant、revoke
DDL:数据定义语言，进行数据库、表的管理等，如create、drop

三、MySQL数据库

linux下常用指令

安装MySQL数据库：sudo apt-get install mysql-server
查看MySQL服务效果：ps -aux | grep mysql
查看MySQL服务状态：sudo service mysql status
停止MySQL服务：sudo service mysql stop
启动MySQL服务：sudo service mysql start
重启MySQL服务：sudo service mysql restart
连接MySQL服务：mysql -u root -p

MySQL配置文件位置：etc/mysql/mysql.conf.d/mysqld.cnf

配置信息说明：
port表示端口号，默认为3306
bind-address表示服务器绑定的ip，默认为127.0.0.1
datadir表示数据库保存路径，默认为/var/lib/mysql
log_error表示错误日志，默认为/var/log/mysql/error.log

3.1 数据类型

数据类型是指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来

数值类型：包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)

日期时间类型：表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR

字符串类型：指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

3.2 数据库基本操作（windows）

启动MySQL服务：net start mysql
连接MySQL：mysql -uroot -p （mysql -hlocalhost -P3306 -uroot -p123456）
退出MySQL：exit
创建MySQL：CREATE DATABASE 数据库名（或者在连接数据库之前mysqladmin -u root -p create 数据库名）
删除数据库：drop database 数据库名（或者在连接数据库之前mysqladmin -u root -p drop 数据库名）
选择数据库：use 数据库名

数据库操作指令（指令操作在连上数据库之后操作mysql -uroot -p，输入结束一定要输入;）

3.3 数据表的操作

常见数据约束：保证数据的完整性和准确性

primary key：主键，物理上存储的顺序.MySQL 建议所有表的主键字段都叫 id,类型为 int unsigned.
unsigned：表示无符号
not null：此字段不允许填写空值
unique：惟一，此字段的值不允许重复
default：默认，当不填写字段对应的值会使用默认值，如果填写时以填写为准
foreign key：外键，对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常.
zerofill ：0填充
auto_increment： 自动增长，添加数据没有写该值时使用之前数据最大值自动加一补充
decimal(5,2) ：五位保留两位小数
enum：枚举

在启动数据库，使用数据库之后，可以在该数据库里面创建表格，对表格进行各种操作，一个数据库可以拥有多个表格，数据保存在表格中。数据表的操作也和数据库一样，要在连上数据库并且使用数据库（use 数据库名）（即指定对哪个数据库中的表格进行操作）的基础上才可以操作（刚连上直接使用会提示没有使用数据库）

# 创建表格
create table 表格名(字段 类型 [约束]，字段，类型 [约束]，...);
# unsigned表示无符号 
# primary key表示主键（唯一）
# not null表示不能为空
# zerofill 0填充
# auto_increment 自动增长，添加数据没有写该值时使用之前数据最大值自动加一补充
# decimal(5,2) 五位保留两位小数
# default 默认值
# enum枚举
create table t_stu(
	id int(5) unsigned zerofill primary key auto_increment not null,
	name char(5) not null,
	height decimal(5,2) default 0,
	gender enum('男', '女')
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3.4 表数据操作

# 查询表的所有数据
select * from 表格名;

# 指定列操作
select 列1,列2,... from 表名;

# 添加数据
# 全部列插入：值的顺序与表结构字段顺序一致
insert into 表格名 values (...);
# 多行数据插入
insert into 表格名 values (...),(...),...;
# 部分列插入：值的顺序与给出的列顺序对应
insert into 表格名 (列1,列2,...) values (...);

# 删除全表数据
delete from 表格名;
# 删除特定条件的数据
delete from 表格名 where 条件;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

给表或字段起别名

# 使用as给列名取别名 (可以将as省略简写）
select id as 序号, name as 名字, gender as 性别 from t_stu;

# 使用as给表取别名
# 单表查询可以省略表名
select id, name, gender from t_stu;
# 不省略时
select t_stu.id, t_stu.name, t_stu.gender from t_stu;
# 取别名时
select t.id, t.name, t.gender from t_stu as t;
1
2
3
4
5
6
7
8
9
10

数据去重：distinct 选择某些列，没一行数据都不一样

select distinct 列1,... from 表格名;
1

查询：可以使用比较运算符、逻辑运算符（and or not）等（注：不能使用=null或!=null来判断是否为空或非空，null也不为’'字符串）

# where查询  条件相当于if判断，符合则打印
select * from 表格名 where 条件;
select * from 表格名 where height>160;
1
2
3

模糊查询：like是模糊查询关键字，%表示任意多个任意字符，_表示一个任意字符

# 查询name为黄字开头的数据
select * from 表格名 where name like '黄%';
# 查询name为黄且名字为一个字的数据
select * from 表格名 where name like '黄_';
1
2
3
4

范围查询
between...and...：表示在一个连续的范围内查询
in：表示在一个非连续的范围查询

# 查询编号为1到10的学生
select * from students where id between 1 and 10;

# 查询编号不是1到10的女学生
select * from students where (not id between 1 and 10) and gender='女';

#查询编号为1,3,5,7,9的学生
select * from students where id in (1,3,5,7,9);
1
2
3
4
5
6
7
8

空判断查询：判断为空使用is null，非空使用is not null

# 查询没写性别的学生
select * from students where gender is null;
1
2

排序查询：使用order by后面根列名，先按照列1进行排序，如果列1的值相同时，则按照列2排序，以此类推；asc从小到大排列；desc从大到小排序；默认从小到大排序，如果要获取排序后的某部分数据，可以排序后加上分页查询。

# asc可替换成天desc
select * from 表名 [where ...] order by 列1 asc [,列2 asc,...]
select * from 表名 [where ...] order by 列1 desc [,列2 asc,...]
1
2
3

分页查询：limit是分页查询关键字、start是索引开始，省略默认为0，count是查询数目

select * from 表名 limit [start,]count；
1

聚合函数：又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。

select 聚合函数位置 from 表名;

# 返回某列非null数据的总行数
select count(列名) from 表名;
# 返回总行数，包含null值
select count(*) from 表名;

# ifnull表示如果height为空，用0替代
select avg(ifnull(height,0)) from 表名;
1
2
3
4
5
6
7
8
9

分组查询：将查询结果进行分组，数据相同的为一组
group by 列名 [HAVING 条件表达式] [WITH ROLLUPOP]：按列名进行分组，HAVING对分组后的表格进行过滤，WITH ROLLUPOP在记录的之后加上查询时聚合函数的统计和计算结果。查询列只能是分组列内的列，如果要看其他的，可以使用group_concat(列名)：统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割，也可以结合聚合函数使用

# 查询列只能是分组列内的列
select 列名,... from 表名 group by 分组列名,...;
# 使用group_concat(列名)查看其他列
select gender,group_concat(name) from t_stu group by gender;

# 结合聚合函数使用
select gender,avg(height) from t_stu group by gender;

# 结合having使用
select gender,avg(height) from t_stu group by gender having gender='男';

# 使用with rollup汇总
select gender,count(*) from t_stu group by gender with rollup;
1
2
3
4
5
6
7
8
9
10
11
12
13

连接查询：可以实现多个表格查询，分为内连接、左连接、右连接、自连接

内连接查询：将左边的任何数据和右边的任何数据进行连接，可以利用一定的条件查询两个表中符合条件的共有记录

# inner join为内连接关键字，  on后面为条件
select 表名.列名 from 表1 inner join 表2 on 表1.列1 = 表2.列2;
1
2

比如t_stu表格为全校学生数据，表t_class为1班学生数据，现在利用内连接在全校学生表格中查询一班学生数据

select * from t_stu inner join t_class on t_stu.id = t_class.id;
1

左连接：以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充

# left join为内连接关键字，  on后面为条件，不能不加条件
select 列名 from 表1 left join 表2 on 表1.列名1 = 表2.列名2;
1
2

右连接：以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充

# right join为内连接关键字，  on后面为条件，不能不加条件
select 列名 from 表1 right join 表2 on 表1.列名1 = 表2.列名2;
1
2

自连接：左表和右表是同一个表，根据连接查询条件查询两个表中的数据

select * from 表名 where 表名.列1 = 表名.列2;
# 比如一个表有三列，id、城市、p_id，p_id保存该城市所在省份的id
# 表1和表2为同一个表
select 表1.列名,表2.列名 from 表1 inner join 表2 on 表1.id = 表2.p_id;

1
2
3
4
5

子查询：在一个 select 语句中,嵌入了另外一个 select 语句,那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询。它们关系是子查询是嵌入到主查询中；子查询是辅助主查询的,要么充当条件,要么充当数据源；子查询是可以独立存在的语句,是一条完整的 select 语句

# 查询大于平均年龄的学生
select * from students where age > (select avg(age) from students);
# 查询学生在班的所有班级名字:
select name from classes where id in (select cls id from students where cls id is not null);
# 查找年龄最大,身高最高的学生:
select * from students where (age, height) = (select max(age), max(height) from students);
1
2
3
4
5
6

范式：对设计数据库提出的一些规范，目前有迹可寻的共有8种范式，经常使用的是三范式

第一范式(1NF)：强调的是列的原子性，即列不能够再分成其他几列。
第二范式(2NF)：满足1NF，另外包含两部分内容，一是表必须有一个主键;二是非主键字段 必须完全依赖于主键，而不能只依赖于主键的一部分。
第三范式(3NF)：满足2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A依赖于非主键列 B，非主键列B依赖于主键的情况。

外键约束：对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性（一个组可以有多个外键，但每个外键必须是其它表的主键）

# 设置外键约束
# 法一：为某建添加外键约束    用这个表格的s_id列关联school表格的id列(id列为school的主键)
alter table t_stu add foreign key(s_id) references school(id);

# 法二：在创建表格的时候设置外键约束
create table school(
	id int(4) not null peimary key auto_increment,
	name char(5)
);   # 创建school表
create table t_stu(
	id int(4) not null primary key auto_increment,
	name char(5),
	s_id int(4) not null,
	foreign key(s_id) references school(id) # 用这个表格的s_id列关联school表格的id列(id列为school的主键)
);

# 删除外键约束
alter table 表名 drop foreign key 列名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

数据导入：当有个sql文件（里面是创建表格插入数据之类的语句），在终端使用数据库之后，使用指令source 文件.sql可以将数据导入

将查询结果插入到其他表格中：insert into ... select ...;

# 将表2的查询数据插入到表1中
insert into 表1(列,...) select distinct 列,... from 表2;
1
2

连接更新表中某列数据：update ... join ...

# 将表1的列1由表2的列1改为表2的列2    
update 表1 inner join 表2 on 表1.列1 = 表2.列1 set 表1.列1=表2.列2;
# 此时修改了列数据，并没有修改列属性
alter table 表 change 原列名 新列名 int(5) not null; 
1
2
3
4

创建表并给某列添加数据：create table 表() select 列 from 表;

create table 表(
id int(5) unsigned primary key auto_increment,
name char(5) not null) select 列 from 表;
1
2
3

四、PyMySQL的使用

需要安装库：pip install pymysql
步骤

1、连接数据库（连接的主机、端口、用户、密码、指定数据库、字符集）
2、获取游标对象（获取的目的是为了执行SQL语句，实现增删改查）
3、执行数据库操作指令
4、关闭游标对象
5、关闭数据库连接

连接数据库：conn = connect(参数列表)

host：连接数据库主机，如果本地则为localhost
port：端口，默认3306
user：用户名
password：密码
database：数据库名称
charset：通讯采用编码方式，推荐utf8

关闭连接：conn.close()
提交数据：conn.commit()
撤销数据：conn.rollback()
获取游标对象：cur = connn.cursor()
使用游标执行SQL语句：execute(operation [parameters])

主要用于执行增删改查

获取查询结果的一条数据：cur.fetchone()
获取查询结果的所有数据：cur.fetchall()
关闭游标：cur.close()

一个数据库可以创建多个游标，一般情况只会建立一个，游标在建立的时候会默认开启一个事务环境

执行SQL查询获取数据：有三种方式

fetchone()：获取取出结果的一行数据
fetchmany(2)：获取指定条数的数据
fetchall()：获取所有数据

import pymysql

# 创建数据库连接
conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='msql', charset='utf8')
# 获取游标对象
cursor = conn.cursor()



# SQL语句
sql = '''select * from t_class;'''
print(sql)
# 执行SQL语句，返回结果是SQL语句影响的行数
row_count = cursor.execute(sql)
print(row_count)
# 取出结果的一行数据
print(cursor.fetchone())
# 获取指定条数的数据
for t in cursor.fetchmany(2):
    print(t)
# 获取所有数据
for line in cursor.fetchall():
    print(line)



# 关闭游标
cursor.close()
# 关闭连接
conn.close()
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

插入数据：对数据库执行增删改查的时候，默认会在事务环境中进行操作，操作完成后要进行手动提交， 如果不提交，程序默认操作为回滚，即更改的操作不被记录，事务提交操作由数据库连接对象来完成（conn.commit()）

# SQL语句
sql = '''insert into t_class values (4,'李',90,89),(5,'黄',78,97);'''
# 执行SQL语句，返回结果是SQL语句影响的行数
row_count = cursor.execute(sql)
print(row_count)

# 对数据库执行增删改查的时候，默认会在事务环境中进行操作，操作完成后要进行手动提交
# 如果不提交，程序默认操作为回滚，即更改的操作不被记录，事务提交操作由数据库连接对象来完成
conn.commit()
1
2
3
4
5
6
7
8
9

更改数据

# 将黄的Chinese列改为100
sql = '''update t_class set Chinese = 100 where name='黄';'''
# 执行SQL语句，返回结果是SQL语句影响的行数
row_count = cursor.execute(sql)
# 提交
conn.commit()
1
2
3
4
5
6

删除数据

# 删除张三的数据
sql = '''delete from t_class where name='张三';'''
# 执行SQL语句，返回结果是SQL语句影响的行数
row_count = cursor.execute(sql)
# 提交
conn.commit()
1
2
3
4
5
6

SQL注入问题产生：如下代码，当输入一个数字的时候可以正常运行，当输入连个数的时候，比如3 or 4就会出现问题，这里不是这种效果where id=(3or 4)，而是where (id=3) or 4这种效果，即where 1，这样所得到的数据并不是我们所想要的数据

id = input('输入id编号：')
sql = '''select * from t_class where id = %s;''' % id
# 执行SQL语句，返回结果是SQL语句影响的行数
row_count = cursor.execute(sql)
# 获取所有数据
for line in cursor.fetchall():
    print(line)
1
2
3
4
5
6
7

可以用如下方式解决上面的SQL注入问题

id = input('输入id编号：')
sql = '''select * from t_class where id = %s;'''
# 利用execute的第二个参数来解决SQL的注入问题
row_count = cursor.execute(sql, (id,))
# 获取所有数据
for line in cursor.fetchall():
    print(line)
1
2
3
4
5
6
7

事务：是用户定义的一系列执行SQL语句的操作，这些操作要么全部执行，要么全部不执行。事务具有原子性、一致性、隔离性、持久性

原子性：要么全部执行，要么全部不执行
一致性：数据库从一个一致性状态转换到另一个一致性状态
隔离性：通常来说，一个事务所做的修改在提交事务之前，对于其他事务来说是不可见的
持久性：一旦提交，数据将永久保存

表的存储引擎就是提供存储数据的一种机制，不同表的存储引擎提供不同的存储机制，查看引擎指令：show engines;常用的引擎InnoDB和MyISAM。可以使用指令修改引擎：alter table 表名 engine='InnoDB';

InnoDB：支持事务
MyISAM：不支持事务，访问速度快，

在CMD中，执行start transaction或者begin表示取消自动提交事务，需要手动提交，不然操作不会提交到数据库，开启事务之后，对数据库的表格操作之后，如果不执行提交，查询得到的结果只是当前的结果，当前结果并没有保存至数据库，只是虚拟的表格，使用commit;（提交）指令后才会提交到数据库，如果使用rollback;（回退）指令，数据会返回到操作之前，等于没有操作（对应于python中的conn.commit()和conn.rollbvack()）

索引：索引相当于书本的目录，能够加快数据库的查询。主键会自动创建索引

索引优点：建立索引之后，可以加快指令查询数据速率
索引缺点：创建索引会耗费时间和空间，并且随着数据量的增加时间耗费也会增加
原则：
1、索引不是越多越好，要适量合理使用
2、对经常更新的表不建议创建过多的索引，对常用于查询的表应该创建索引
3、数据流小的表最好不使用索引，因为数据量少，肯查询全部数据花费时间比遍历索引的时间还要端，索引可能没效果
4、在一字段上相同值比较多的列不建立索引。
联合索引：可以减少磁盘空间开销

# 查看表中已有的索引   
show index from 表名;

# 创建索引（联合索引，多个列）
alter table 表名 add index 索引名[可选](列名,...);
# 给name添加索引
alter table 表名 add index my_name (name);

# 删除索引
alter table 表名 drop index 索引名;
# 如果不知道索引名，可以查看创建sql语句
show create table 表名;
alter table 表名 drop index my_name;

# 联合索引最左原则
alter table 表名 drop index (name, age);
# 下面使用了索引
select * from 表名 name = '张三'；
select * from 表名 name = '张三' and age = 20;
# 下面没使用到缩影
# 没使用name而直接使用age作为条件没准寻最左原则
select * from 表名 age = 20;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22