mysql数据库学习过程的问题 mysql数据库知识

MySQL数据库基础知识

一、数据库说明

1.1 什么是数据库

数据库（Database）是一种存储结构化数据的方式，一般存在两种类型的数据库： 关系型数据库、非关系型数据库。

关系型数据库： 存储二维关系表的仓库， 二维关系表是由列（特征）和行组成的。提供标准的SQL语句进行管理（创建、修改、删除）。关系型数据库一般都支持约束、索引、视图、函数、存储过程、触发器、表、字典（动态视图）等对象。【重点】

非关系型数据库： 不需要使用SQL,通过SDK（软件开发环境-模块或库， Software Development Kit）相关的方法进行操作，不支持二维关系结构，又称之为NoSQL数据库。包含redis、mongodb等。

1.2 有哪些关系型数据库

大厂的关系型数据库： DB2(IBM公司)、Oracle（甲骨文公司）、MySQL（原Sun公司，目前也是甲骨文公司）。

其它的关系型数据库：MariDB(个人， 同MySQL一个作者)、 posgreSQL(开源的数据库)

Hadoop家族的数据库： Hive(依赖MySQL, MapReduce), Hbase(实时)

嵌入式设备中的数据库： sqlite3(没有特定的数据类型)、IotDB(分布式物联网存储的实时数据库)

1.3 数据范式

限制表的设计， 让表的结构更合理、减少数据冗余。

如设计存储书相关信息的表：

ID 书编号
name 书名
cover 封面图片连接
summary 介绍
author 作者
author_years 作者的写作年限
author_nickname 作者别名
author_info 作者的介绍
author_jg   作者的家乡
author_primary_book_name 作者代表作品
store_name 书店名称
store_city 书店所在的城市
store_boss 书店的老板姓名
store_tel  书店的联系电话
publish_date 书的发布时间
publisher   出版社

假如 某一个作者有10本书，作者的相关信息重复了10次，每一条数据的作者相关信息存在近500个汉字，按UTF8编码计算，每一行作者数据的字节大小为 500*3=1500，近1.2K, 10条件记录近12K。

表的第一范式： 确保每一个列保持原子性（列不可分隔）。

根据第一范式分隔书的表为某本书的表、作者表、书店表：

-- book表
ID 书编号
name 书名
cover 封面图片连接
summary 介绍
publisher   出版社
publish_date 书的发布时间

-- 作者表
author 作者姓名
author_years 作者的写作年限
author_nickname 作者别名
author_info 作者的介绍
author_jg   作者的家乡
author_primary_book_name 作者代表作品

-- store 书店表
store_name 书店名称
store_city 书店所在的城市
store_boss 书店的老板姓名
store_tel  书店的联系电话

表的第二范式： 确保每一列的数据都和主键相关， 即表中存在主键约束。

-- 作者表
ID 作者编号（主键）
author 作者姓名
author_years 作者的写作年限
author_nickname 作者别名
author_info 作者的介绍
author_jg   作者的家乡
author_primary_book_name 作者代表作品

-- store 书店表
ID 书店（主键）
store_name 书店名称
store_city 书店所在的城市
store_addres 书店的地址
store_boss 书店的老板姓名
store_tel  书店的联系电话

表的第三范式： 确保每一列都和主键直接相关，而不能间接相关。即存在主外键关系。

-- book表
ID 书编号
name 书名
..
author_id 作者的ID， 外键
store_id  书店的ID， 外键

二、DDL语句

DDL 数据库描述或定义语言（Data Definition Language）,包含数据库、表、视图、函数、索引、存储过程、触发器等对象的创建、修改和删除等操作。

2.1 创建和删除数据库

创建数据库语法：

create database <数据库名> [ charset <字符集> ];

如，创建bookdb数据库

create database bookdb;

删除数据库，即将库中所有对象全部删除【慎用】

drop database [if exists] <数据库名称>;

if exists 确保SQL正常执行，如果数据库存在，则删除，不存在pass。

如，删除bookdb库

drop database bookdb;

2.2 创建表

官方文档：https://dev.mysql.com/doc/refman/8.0/en/create-table.html

创建表之前，必须先使用 use <数据库名> 语句打开某一个数据库，表示在某一数据库下创建表。

简洁的语法：

create table [if not exists] <表名>(
   <字段名> <字段类型>[ (字段数据长度) ] [约束] [comment 注释文本],
   ...,
   [constraint 表级约束语句]
) [engine=INNODB|MYISAM|MEMERY|CSV] [character set 字符集];

说明：

• 字段名，不能和数据库的关系冲突，一般同Python语言标识符的命名类同，由字母、数字和下划线及特殊符号组成。
• 字段类型，mysql提供了丰富的字段类型，包含：

• 字符类型char, 固定长度的字符类型，即分配多少长度（字符的个数），即用多少长度。
  【扩展】正则表达式中匹配中文的unicode编码范围： \u4e00-\u9fa5
• 可变字符类型varchar，与char不同，根据内容器的多少，使用多少。不会使用给定的最大长度。
• 整数类型int和integer
• 小数类型float、double、decimal(可以设置小数点后的精确位)
• 日期类型 date
• 日期时间类型timestamp 包含日期部分和时间部分
• 文本类型 text
• 大文本类型clob
• 二进制类型 blob

• 约束， 根据不同的约束类型，检查数据，如果不合法，则抛出异常显示违反xxx约束。

• 主键约束 primary key: 非空，唯一的
• 外键约束 foreign key: 值，依赖于外部主表的主键值。外键字段的值可以为空的。
• 唯一约束 unique : 值在整个一列中是唯一的 ，值可以存在一个空值。
• 非空约束 not null : 列值不能为空
• 默认值 default ： 列值在设计时，可以指定默认值
• 自增 auto_increment: 列值是某一个序列的下一个值，不需要指定。

• 引擎

• InnoDB ： 支持事务、行级锁、外键约束 【默认】
• MEMERY: 使用hash函数， 存储于内存，使用临时表。用于缓存
• MyISAM: 不支持事务、行级锁和外键约束
• CSV: 以CSV格式存储数据

如，创建附合第三个范式的图书表、作者表

drop database if exists bookdb;
create database bookdb;
use bookdb;

drop table if exists author;
drop table if exists book;

create table if not exists author
(
    author_id integer primary key auto_increment,
    name varchar(20),
    city varchar(20),
    phone char(11)
);


create table if not exists book
(
    book_id integer  auto_increment comment '图书编号',
    name varchar(50) unique comment '书名',
    summary text comment '简介',
    price decimal(5, 2) comment '单价',
    act_price decimal(5, 2) comment '活动价',
    cover_url varchar(100) comment '封面的URL地址',
    author_id integer,
    constraint book_pk  primary key (book_id),
    constraint book_author_fk foreign key (author_id) references author(author_id) on delete cascade
);

show tables;

【重要说明】constraint子句

constraint [约束名称] 约束类型 [index_type: {BTREE, HASH}] (字段名,...)
[references 引用表的名称(引用的字段名)  on <delete|update> <restrict|cascade|set null|no action|set default> ]

约束类型： primary key、unique、foreign key

只有primary key和unique 存在index_type关键字

只有foreign key 存在 references 关键字。

【扩展-docker创建与宿主机同步的mysql容器】

准备工作：

1. 确认 /root/mysql 目录是否从某一个mysql已运行容器的/etc/mysql复制的
2. 确认/root/mysql/my.cnf 文件中关于character-set-server 是否配置
3. 确认/root/mysql/conf.d/mysql.cnf 文件中default-character-set是否配置
4. 确认/root/mysql/sql/init.sql 脚本是否存在

docker run -d --name db000 -e MYSQL_ROOT_PASSWORD=root -p 3313:3306 --network db-net -v /root/mysql:/etc/mysql mysql

docker exec db000 ls /etc/mysql

docker exec -it db000 mysql -uroot -proot
mysql> show databases;
mysql> create database if not exists bookdb;
mysql> use bookdb;
mysql> source /etc/mysql/sql/init.sql
mysql> show tables;

2.3 修改表

修改表： 表名、表字段名、字段类型及字段的约束。

2.3.1 修改表名

alter table <表名> rename [to | as] <新表名>;

修改book表为tb_book

alter table book rename as tb_book;

修改author表为tb_author

alter table author rename to tb_author;

2.3.2 修改字段名

alter table <表名> change [column] <旧列名> <新列名> <类型[(长度)]>

修改tb_book表中的name字段为book_name

alter table tb_book change column name book_name varchar(40);

修改tb_book表的price为src_price名称，数据类型保持不变

alter table tb_book change price src_price decimal(5,2);

2.3.3 修改字段类型

alter table <表名> modify <列名> <类型[(数据长度)]> [约束]

修改tb_book表的summary的类型改为 varchar，且数据长度为200个字符

alter table tb_book modify summary varchar(200);

2.3.4 删除字段主外键约束

2.3.4.1 删除外键约束

alter table <表名> drop foreign key <约束名称>;

删除tb_book表的book_author_fk外键约束：

alter table tb_book drop foreign key book_author_fk;

2.3.4.2【查询表的相关约束】

依据information_schema字典库的table_constraints表

查询bookdb数据库中所有表的约束信息

select table_name,constraint_name, constraint_type
from information_schema.table_constraints 
where table_schema='bookdb';

+------------+-----------------+-----------------+
| TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE |
+------------+-----------------+-----------------+
| tb_author  | PRIMARY         | PRIMARY KEY     |
| tb_book    | name            | UNIQUE          |
| tb_book    | PRIMARY         | PRIMARY KEY     |
| tb_book    | book_author_fk  | FOREIGN KEY     |
+------------+-----------------+-----------------+

2.3.4.3 删除主键约束

alter table <表名> drop primary key;

删除tb_author表的主键约束

alter table tb_author modify author_id integer ;
alter table tb_author drop primary key;

2.3.4.4 删除唯一约束

alter table <表名> drop {index|key} 唯一约束名或列名

【注意】默认情况唯一约束名即为列名。

删除tb_book表的name字段的唯一约束

alter table tb_book drop index name;

2.3.5 删除字段

alter table <表名> drop [column] <字段名>;

删除tb_book的summary字段

alter table tb_book drop summary;

删除tb_book的author_id字段

alter table tb_book drop author_id;

2.3.6 添加字段

alter table <表名> 
add [column] <列名> 数据类型[(数据长度)] [约束] [comment 注释信息]

添加tb_book的summary字段，类型为text, 不能为空

alter table tb_book
add column summary text not null;

添加tb_book的author_id字段，类型为integer

alter table tb_book
add column author_id integer;

2.3.7 添加字段约束

alter table <表名>
add [constraint [约束名] ] 
| primary key [index_type: {using btree|hash}] (字段1, 字段2, ....) 
| unique [key | index ] [index_name] [index_type] (字段1, 字段2, ...)
| foreign key (字段1, 字段2,...) references 引用表名(字段1,字段2, ...) [on delete|update restrict|cascade|set null|no action|set default]

【索引分类】主键索引、唯一索引、普通索引、组合索引

【索引类型】BTREE、HASH

添加tb_author表的author_id字段的主键约束，并设置为自增

alter table tb_author modify author_id integer primary key auto_increment;
-- alter table tb_author add primary key (author_id);

添加tb_book的author_id的外键约束，引用tb_author的author_id字段

alter table tb_book
add constraint book_author_fk foreign key (author_id) references tb_author(author_id);

2.4 删除表

drop table [if exists] <表名>

如，删除tb_book表

drop table tb_book；

【注意】无论是删除表或列，则表存在的相关约束都会被删除。

2.5 查询表结构

方式1：

DESC <表名>

方式2：

show create table <表名>

查看表创建时的SQL语句。

同样，查看数据库的创建SQL语句：

show create database <数据库名>

2.6 查看所有的表

方式1： show tables

方式2： 使用information_schema字典的tables表

select table_name,table_type,table_schema
from information_schema.tables
where table_schema='bookdb';

2.7 子查询创建表

基于SQL查询语句，快速创建一张无约束的表。

create table <表名>
select子句;

如：创建北京作者信息的表

create table tb_bejin_author
select * from tb_author where city='北京';

三、DML语句

DML(Data Manipulation Language) 数据操纵语言，主要负责数据（行或记录）的插入、更新和删除操作。

3.1 insert语句

insert语句是向表中插入一行或多行数据。

3.1.1 单行数据插入

insert into <表名> [(字段1, 字段2, ...)]
values (字段值1, 字段值2, ...)

说明：

• 表名之后，通过()声明向哪些字段插入数据；默认是全部字段，插入值的顺序按创建表时声明字段顺序。
• values后的()中不同类型的数据，必须按插入值字段的类型匹配，如字符类型必须使用单引号''，数值类型不能使用单引号，日期类型可以使用单引号。

如，新增一位作者，姓名为disen,电话[phone],城市是西安

-- author_id, name, city, phone
insert into tb_author
values(1, 'disen', '西安', '17791692095');

如，新增西安城市的李九江作者，他的电话是120

insert into tb_author(city, name, phone)
values('西安','李九江','120');

【注意】如果遇到自增列auto_increment时，避免显式方式指定列值。

3.1.2 多行数据插入

insert into <表名> [((字段1, 字段2, ...))] values
(字段值1, 字段值2, ...),
(字段值1, 字段值2, ...),
(字段值1, 字段值2, ...),
...
(字段值1, 字段值2, ...);

如，插入3位作者信息

insert into tb_author(city, name, phone) values
('北京', '老邓', '18918810018'),
('北京', '老刘', '17866541239'),
('杭州', '老马', '18766541908');

3.1.3 子查询方式插入

使用场景： 一般在分表存储数据时使用。

insert into <表名> [(字段1, ...)]
select子句;

如：将tb_author表中的所有北京的作者插入到tb_bejin_author表

insert into tb_bejin_author
select * from tb_author where city='北京';

【练习-添加tb_book表数据】

insert into tb_book(book_name, src_price, summary, cover_url) values
('Python入门第一版本', 20, 'Python2.7精讲', null),
('Python3精通', 50, 'Disen主讲Python3.7', '/static/img/1001.jpg'),
('MySQL5.7', 90, 'Mysql 5.7的DDL,DML,DQL等', '/static/img/1002.jpg'),
('MySQL8.0', 12, 'MySQL 8.0的全部内容', null),
('Linux精通到放弃', 28, 'Linux Centos7', null),
('Linux Shell编程', 21, 'Shell脚本开发',null);

3.2 update语句

update主要负责数据的更新或修改。

update <表名> set
字段1=字段值1 [,字段2=字段值2, ...]
[where 子句]

说明： where子句，支持等值条件、逻辑运算、空值。

• 等值条件： 字段名=值， 字段名 >= | < | <= | != 值， 字段名 between 起始值 and 结束值 闭区间
• 逻辑运算： 指多个条件之间的逻辑关系，包含 and、or 、not, 另外，字段名 in(值1, 值2, 值3,...) 等价于字段名=值1 or 字段名=值2 or 字段名=值3。
• 空值： is null、not null

如，更新tb_author西安的disen作者的城市为上海

update tb_author set
city='上海'
where city='西安' and name='disen';

如， 修改tb_author表中disen作者的姓名为’老狄’,城市修改为’北京’

update tb_author set
city='北京',name='老狄'
where name='disen';

如，修改tb_book表，将原价的20到30之间的所有图书打7折

select book_name, src_price,act_price
from tb_book;

-- tb_book(book_id, book_name, src_price, act_price, ....)
update tb_book set
act_price=src_price*0.7
where src_price between 20 and 30;

【注意】在更新某一字值时，可以引用原字段的数据。如act_price=src_price*0.7。

如，将tb_book表中所有cover_url字段为空修改为/static/img/default.jpg

select book_name, cover_url from tb_book;

update tb_book set
cover_url='/static/img/default.jpg'
where cover_url is null;

【注意】更新数据时，避免全部数据更新，即必须带有where条件

3.3 delete语句

delete删除表的数据,不会影响表结构本身。

delete from <表名>
[where 子句]

【注意】如果delete语句不存在where子句时，表示清空表的内容。

如， 删除tb_book表中活动价格低于10元的数据

select book_name, act_price from tb_book;

begin;
delete from tb_book
where act_price <= 15;
commit;

【练习-外键约束的数据插入和级联操作】

-- tb_author(author_id, name, city, phone)
-- tb_book(book_id, book_name, ...,  author_id) 

insert into tb_author(name, city, phone)
values
('老李','西安', '19887726619'),
('小王','北京', '19826661190');

mysql> select * from tb_author;
+-----------+--------+--------+-------------+
| author_id | name   | city   | phone       |
+-----------+--------+--------+-------------+
|         1 | 老李   | 西安   | 19887726619 |
|         2 | 小王   | 北京   | 19826661190 |
+-----------+--------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select book_id,book_name, author_id from tb_book;
+---------+----------------------+-----------+
| book_id | book_name            | author_id |
+---------+----------------------+-----------+
|       2 | Python3精通          |      NULL |
|       3 | MySQL5.7             |      NULL |
|       4 | MySQL8.0             |      NULL |
|       5 | Linux精通到放弃      |      NULL |
+---------+----------------------+-----------+

-- 修改tb_book表的book_id是2的author_id为3，是否可以成功呢？如果不成功，抛出什么错误呢？
-- 原因： 更改的author_id不在tb_author表的主键中。
update tb_book
set author_id=3
where book_id=2;

mysql> update tb_book
    -> set author_id=3
    -> where book_id=2;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookdb`.`tb_book`, CONSTRAINT `book_author_fk` FOREIGN KEY (`author_id`) REFERENCES `tb_author` (`author_id`) ON DELETE CASCADE)

-- 将所有书的作者全部修改为author_id为1的作者
update tb_book
set author_id=1;

-- 删除author_id为1的作者记录
-- 外键约束的级联操作： ON DELETE CASCADE 级联删除
delete from tb_author where author_id=1;

【注意】当事务开启后，且执行了多个DML语句，在未提交事务之前，如果发生了DDL语句，则会自动提交事务。

alter table tb_book
drop foreign key book_author_fk;

-- 添加tb_book和tb_author的外键约束，支持级联删除和级联更新
alter table tb_book
add constraint book_author_fk foreign key (author_id) 
references tb_author(author_id) on delete cascade on update cascade;

-- ON UPDATE CASCADE 级联更新
-- 修改author_id为1的作者编号为110:  如果外键约束中存在级联更新，则直接修改外键字段的值。
update tb_author
set author_id=110
where author_id=1;

-- 外键约束：级联删除置空
alter table tb_book
drop foreign key book_author_fk;

alter table tb_book
add constraint book_author_fk foreign key (author_id) 
references tb_author(author_id) on delete set null;

begin;
delete from tb_author where author_id=110;
select book_name, author_id from tb_book;
rollback;

四、DQL语句

DQL(Data Query Language)数据查询语言，主要负责数据的查询，包含单表查询、多表查询、子查询、分组查询、条件查询、模糊查询、分页查询、窗口函数统计等。

官方文档：https://dev.mysql.com/doc/refman/8.0/en/select.html

4.1 select语句

select [distict] 字段名|表达式 [ [AS] 别名] [, 字段|表达式,...]
[ from <表名>|<select 子查询> [ [as] 别名] [, <表名>|<select子查询>,...] ]
[[left|right|]  join <表名>|<select 子查询> [[as] 别名] on (连接条件),... ]
[where 子句]
[group by 子句]
[having 子句]
[order by 列名|表达式 [ASC | DESC]]
[limit offset, rows]

4.1.1 select中的字段

在select子句中，可以选择表中的字段，也可以使用表达式（算术、函数、 关系、逻辑）。

如，查看当前时间（current_date、now()）

select current_date;
select now();

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-09-03 06:09:03 |
+---------------------+

mysql> select now() 'this time';
+---------------------+
| this time           |
+---------------------+
| 2021-09-03 06:09:03 |
+---------------------+

mysql>  select current_date as cdate;
+------------+
| cdate      |
+------------+
| 2021-09-03 |
+------------+

mysql> select 100>20 as ret;
+-----+
| ret |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> select 100<20 as ret;
+-----+
| ret |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)


mysql> select 1 and 2 or 0 a123;
+------+
| a123 |
+------+
|    1 |
+------+

mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 8f232ae9-0c80-11ec-96d6-0242ac110002 |
+--------------------------------------+

mysql> select replace(uuid(), '-', '') as USERID;
+----------------------------------+
| USERID                           |
+----------------------------------+
| bca7edc00c8011ec96d60242ac110002 |
+----------------------------------+

【扩展-面试题】

create table A(year int, month int, amount float);
insert into A values
(1991, 1, 1.1),
(1991,2, 1.2),
(1992,1, 2.1),
(1992,2, 2.2);

问题存在一张A表，内容如下：

+------+-------+--------+
| year | month | amount |
+------+-------+--------+
| 1991 |     1 |    1.1 |
| 1991 |     2 |    1.2 |
| 1992 |     1 |    2.1 |
| 1992 |     2 |    2.2 |
+------+-------+--------+

请写出如下结果的SQL语句：

+------+-------+--------+
| year | m1    | m2     |
+------+-------+--------+
| 1991 |   1.1 |   1.2  |
| 1992 |   2.1 |   2.2  |

最有效的写法-SQL:

select year, 
  round(max(if(month=1,amount,0.0)),1) as m1,
  round(max(if(month=2, amount,0.0)),1) as m2 
from A
group by year;

4.1.2 from子句

from 关键字后面可以跟表名或子查询，如果是子查询时，必须提供别名，作为子查询结果的临时表名。 表名的别名，可以在select子句中使用。

from 后跟多个表时，需要用逗号分隔。 如果从多个表中查询数据，必须提供多个表之间的连接条件，否则会出现笛卡尔集。

select b.book_name, b.author_id, a.name as author_name
from tb_book b, tb_author a
where b.author_id = a.author_id;

4.2 where子句

where子句主要的作用是限制数据的范围，在select 的where子句中的字段，一般都是index索引字段（加速查询）。

where 子句中支持非常丰富的条件表达式，包含between-and、like、函数表达式、in、is null、not null等。

-- 查看活动价格在10-20之间的所有书的信息
select book_id,book_name,act_price
from tb_book
where act_price between 10 and 20;

-- 查看姓名中带`李`的作者
select * from tb_author
where name like '%李%';

【like说明】like是一种模糊匹配的查询条件，可以使用%和_通配符。

• % 代表任意长度的任意字符
• _ 代表一个长度的任意字符

-- 查看姓名中第三个字符为`丽`的作者
select * from tb_author
where name like '__丽%';

-- 查看书编号为1或3或5的书信息
select * from tb_book
where book_id in (1, 3, 5);

-- 查看除作者编号为1和5之外的所有作者信息
select * from tb_author
where author_id not in (1, 5);

4.3 join语句

join语句支持多个表之间的连接，通过on指定表连接的条件。

使用join可以减少where等值连接的子句。

4.3.1 内连接

内连接即表示两个表都具有的数据

mysql> update tb_book set author_id=null where book_id=4;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select book_id,book_name, author_id from tb_book;
+---------+---------------+-----------+
| book_id | book_name     | author_id |
+---------+---------------+-----------+
|       2 | Python3精通   |       110 |
|       3 | MySQL5.7      |       110 |
|       4 | MySQL8.0      |      NULL |
+---------+---------------+-----------+

mysql> select author_id,name from tb_author;
+-----------+--------+
| author_id | name   |
+-----------+--------+
|         2 | 小王   |
|       110 | 老李   |
+-----------+--------+

select a.book_id, a.book_name, b.name as author_name,b.author_id
from tb_book a
join tb_author b on (a.author_id = b.author_id);

4.3.2 左外连接

除了内连接的数据之前，也会选择左表的其它数据。

select a.book_id, a.book_name, b.name as author_name,b.author_id
from tb_book a
left join tb_author b on (a.author_id = b.author_id);

4.3.3 右外连接

除了内连接的数据之前，也会选择右表的其它数据。

select a.book_id, a.book_name, b.name as author_name,b.author_id
from tb_book a
right join tb_author b on (a.author_id = b.author_id);

4.3.4 自然连接

根据两个表之间的共同的列名，自动连接。默认为内连接，也可以是左外连接或右外连接。自然连接即省略了on条件。

select a.book_id, a.book_name, b.name as author_name,b.author_id
from tb_book a
natural join tb_author b;

select a.book_id, a.book_name, b.name as author_name,b.author_id
from tb_book a
natural left join tb_author b;

select a.book_id, a.book_name, b.name as author_name,b.author_id
from tb_book a
natural right join tb_author b;

4.4 分组语句

group by 分组子句，可以按列中相同的数据进行分组，分组之后的数据可以聚合计算，聚合计算函数包含最小min()、最大max()、求和sum()、统计数量count和平均avg()。

-- 统计每位作者的书的数量
-- select 1;
select a.author_id,a.name, count(1) as books
from tb_book b
natural join tb_author a
group by a.author_id,a.name;

-- 统计每位作者出书的最大价格、最小价格、平均价格
select author_id,
       max(src_price) 最高价,
       min(src_price) 最低价,
       avg(src_price) 均价
from tb_book
group by author_id;

【扩展-设计书的订单表】

drop table if exists tb_app_user;
drop table if exists tb_order;

-- 设计会员表
create table tb_app_user(
    user_id integer primary key auto_increment,
    phone char(11) unique,
    authentication_string varchar(255) not null,
    nick_name varchar(50)
);

insert into tb_app_user(phone, authentication_string) values
('17791692095', sha1('692095')),
('19524662948', sha1('662948')),
('18766541234', sha1('541234')),
('19077653412', sha1('653412'));

-- 订单表
create table tb_order(order_id char(32) primary key, 
                      order_title varchar(50),
                      book_id integer references tb_book(book_id),
                      order_price decimal(10, 2),
                      order_pay_status int default 0,
                      order_pay_time timestamp,
                      user_id integer references tb_app_user(user_id)
                     );
                     
insert into tb_order values
(replace(uuid(), '-',''),  '<西安千锋>Python3.7', 2, 39, 1, '2020-12-11 14:35:12', 1),
(replace(uuid(), '-',''),  '<西安千锋>Python3.7', 2, 31, 1, '2020-12-11 17:35:12', 2),
(replace(uuid(), '-',''),  '<西安千锋>MySQL5.7', 3, 88, 1,  '2020-12-11 15:12:12', 1),
(replace(uuid(), '-',''),  '<西安千锋>MySQL5.7', 3, 88, 1, '2020-12-11 17:45:12', 2),
(replace(uuid(), '-',''),  '<西安千锋>Python3.7', 2, 39, 1, '2020-12-13 10:35:12', 3),
(replace(uuid(), '-',''),  '<西安千锋>Python3.7', 2, 39, 1, '2020-12-13 11:35:12', 4),
(replace(uuid(), '-',''),  '<西安千锋>MySQL5.7', 3, 88, 1, '2020-12-13 14:10:12', 3);

-- [任务] 统计每一位作者的图书总销量情况
-- 【注意】列的别名，可以用于order by 、group by、having的子句中。
select a.name as author_name, count(1) as books, sum(o.order_price) total_price
from tb_order o 
natural join tb_book b
natural join tb_author a
where o.order_pay_status=1
group by author_name 
-- having books > 3;
order by total_price DESC, books DESC;

+-------------+-------+--------------------+
| author_name | books | total_price |
+-------------+-------+--------------------+
| 老李        |     4 |             148.00 |
| 小王        |     3 |             264.00 |
+-------------+-------+--------------------+

4.5 排序语句

order by 子句可以按列的值进行升序ASC、降序DESC进行排序。

【注意】order by 可以对多个列进行排序，先左后右。如果左边的值相同的情况下，按右边的列值进行排序。

-- 练习： 查看已支付图书的信息，按支付的时间进行降序排序，如果时间相同，按图书的编号进行升序排序。
-- 时间格式化的函数： date_format(日期时间字符串， ‘%Y-%m-%d %H:%i:%s’)
select order_title, 
       if(order_pay_status=1,'OK','CANCEL') as pay_status,
       book_id,
       order_pay_time
from tb_order
order by date_format(order_pay_time,'%Y-%m-%d') desc,  book_id;

4.6分页语句

mysql 中分页使用limit 关键字, limit子句在select查询语句的最后位置。

limit 起始行号(从0开始), 记录个数（行数，一般使用10, 20, 50, 100）

-- 练习： 按每页显示5条记录，显示第2页的所有订单数据
-- 提示： offset起始位置：  (page-1)*page_size
select order_title, book_id, order_pay_time
from tb_order
limit 5, 5;

4.7 union语句

union 语句实现两个select子查询的数据联合。默认是去重的。

union all 保留所有子查询的结果，不去重。

-- 实现全外连接
select a.book_id, a.book_name, b.name as author_name,b.author_id
from tb_book a
natural left join tb_author b
union
select a.book_id, a.book_name, b.name as author_name,b.author_id
from tb_book a
natural right join tb_author b;

4.8 window语句

mysql 从8.0开始支持窗口语句查询，即分区统计和现在数据进行连接显示（输出 ）。一般配合 聚合函数+ over()， over()函数中可以指定分区（分组）的字段，即指定partition by子句。当然也可以使用其它的窗口函数，如rank实现排名。

4.8.1 聚合函数+over

-- 查看每天的图书销售信息，同时显示当天的总销售额。
select order_title, order_pay_time,
       sum(order_price) over(partition by date_format(order_pay_time, '%Y-%m-%d')) as day_total_amount
 from tb_order;

4.8.2 非聚合函数+over

4.8.2.1 row_number()

计算行的序号，在over()函数中支持的order by子句。

-- 查看作者的信息，并按注册时间的年份编排一个序号
-- 修改作者表，添加一个 join_date 列
alter table tb_author
add join_date date;

update tb_author set join_date='2010-10-12' where author_id=2;
update tb_author set join_date='2011-11-23' where author_id=110;

insert into tb_author values
(1, '老狄', '西安', '19877796155','2010-08-01'),
(3, '老杨', '天水', '19998122112','2011-07-12');

select *, 
     row_number() over(partition by year(join_date) order by join_date) as number
from tb_author;

4.8.2.2 rank()排名

rank()函数实现名次排序，同时会考虑到相同名次。

-- 查询学生的总成绩并进行排名
-- 查询总成绩第三名的学生信息
select * from (
    select s.*, A.total,rank() over(order by total DESC) as rank_number 
    from (
        select sid, sum(score)  as total
        from SC
        group by sid
     ) A
     join Student s on (s.sid=A.sid)
) C
where rank_number=3;

-- 不使用rank()函数时，显示排名
select A1.sid, count(A2.sid)+1 as rank_num
from 
  (
    select sid, sum(score)  as total
    from SC
    group by sid
  ) A1
  left join (
     select sid, sum(score)  as total
     from SC
     group by sid
  ) A2 on (A1.total < A2.total)
  group by A1.sid
  order by rank_num;

-- 使用 @变量方式
select A.*, (@i:=@i+1) as rank_num
from (
    select sid, sum(score) as total
    from SC
    group by sid
    order by total DESC
) A, (select @i:=0) C;

set @crank=0;
select q.sid, total, @crank := @crank +1 as rank1 from(
select sid, sum(score) as total from SC
group by SC.sid
order by total desc)q;

【周未任务】

1. 分析某一个app页面，设计表（符合第三范式），表的数量不低20张。(PowerDesigner设计工具-物理模型)

2. 完成SQL练习.doc文件中至少5题。

五、DTL语句

DTL(Data transaction Language ) 数据事务语言，主要控制当前操作的事务性，包含开启事务、提交事务、回滚事务、保存事务还原点、及事务的隔离级别设置等。

事务具有四个特性（ACID）：

• (Atomicity)原子性： 事务中的操作要么全执行，要么全失败。
• (Consistency)一致性： 事务操作的前后，数据保持一致
• (Isolation)隔离性： 事务之间互不影响, 具有四个级别

• 读未提交 read uncommitted
• 读已提交 read committed
• 可重复读 repeatable read
• 串行化 serializable

• (Durability)持久性： 事务提交后，数据将会保存，即使系统崩溃了，也不会影响数据。

5.1 事务的基本操作

5.1.1 开启事务

begin;

5.1.2 提交事务

commit;

5.1.3 回滚事务

rollback;

六、常用函数

6.1 时间函数

6.1.1 获取时间函数

current_date, current_date(),curdate() : 当前日期
now():  当前日期时间

year(d)：获取日期时间中的年
month(d): 获取日期时间中的月份
dayofmonth(d):  获取日期中的 日， 即月中第几天
hour(t): 获取时间的小时
minute(t): 获取时间的分钟
second(t): 获取时间的秒

dayofyear(d): 获取日期中一年中第几天
dayofweek(d): 获取日期中一周的星期几

如， 查看当前日期是一年中第几天？

select dayofyear(now());

6.1.2 时间转换函数

time_to_sec(t): 将时间转化为秒数
sec_to_time(s): 将秒数转化为时间

to_days(d) : 将日期转化为 0000年1月1日的天数
from_days(n): 将距离0000-00-00时间的n天转化为日期

date_format(d, f): 将日期转化为f格式的字符串， 
	- %Y 四位的年
	- %m 2位的月份， 01-12月
	- %d 2位的日， 01-31
	- %h 12小时制的时
	- %H 24小时制的时
	- %i 2位分钟，00-59
	- %s 2位秒， 00-59
	- %M 月份的英文名全写
	- %a 星期几的名称简写
	- %W 星期几的名称全写
	- %u 一年中第几周 00-53， 星期一是一周的第一天。
	- %p 上午或下午的表示AM、PM
time_format(t, f): 将时间转化为f格式的字符串

6.1.3 时间间隔

datediff(d1, d2): d1和d2的间隔天数
timestampdiff(时间函数, d1, d2) ：计算两个时间的时间函数的间隔数值
adddate(d, n):  d日期增加n天后的日期
subdate(d, n): d日期减去n天后的日期
addtime(t, s):  t时间增加s秒后的时间
subtime(t, s):  t时间减去s秒后的时间

date_add(d, interval n interval_type): 当前时间 间隔 n的 interval_type后的日期时间。interval_type可以是年、月、日、小时、分钟和秒的表示字符：
	- YEAR 年
	- MONTH 月
	- DAY 日
	- HOUR 小时
	- MINUTE 分钟
	- SECOND 秒
	
   【注意】 n可以使用负数，表示之前的，如果是正数表示之后的间隔。
   
date_sub(d, interval n interval_type) ： 当前时间 间隔 n之前的 interval_type的日期或时间。 等同于 data_add(d, interval -n i_type )。

6.1.4 修改数据库时区

修改my.cnf配置文件，在[mysqld]下增加一行，内容如下：

default-time-zone = '+08:00'

重新启动mysql服务或容器，并进入容器中，通过

select now();
select @@time_zone;

6.2 字符函数

lower(s)/upper(s) 大小字母转化
concat(s1, s2,s3,....sn) : 可以将多个字符串连接成新的字符串
substr(s, start, [len]):  截取s的子字符串, 第一个字符的位置是1。
length(s) ：获取字符串的长度（存储的字节大小）
char_length(s) : 获取字符个数
lpad(str, len, padstr): 如果str的长度不足len, 则使用padstr字符在左边填充。
rpad(str, len, padstr): ...
trim(str), ltrim(), rtrim()： 删除字符串中的（左、右）空格。
replace(str, old_str, new_str): 从str中将old_str替换为new_str

6.3 数值函数

abs(n): 绝对值
round(n): 四舍五入
ceil(n): 上行取整
floor(n)： 下行取整
sin(n)
cos(n)
tan(n)
atan(n)
asin(n)
acos(n)：
sqrt(n)： 开平方根
power(n, m): n的m次方/幂

6.4 聚合函数

min()
max()
sum()
avg()
count()

6.5 加密函数

-- 不可逆：只能加密，不能解密
sha1(s): sha1 签名信息， 40位。
sha2(s, 0|224|256|384|512): 比sha1更强大的数据加密算法
md5(s)： md5签名信息，32位。

aes_encrypt(str, key):  使用key密钥对str进行AES加密（对称加密-可逆的）
aes_decrypt(encrypt_str, key):  使用key密钥进encrypt_str密文解密。
-----
以上两者加密返回的都是十六进制数据，可以通过cast()函数转换数值
cast(hex_number as unsigned)

七、视图与索引

mysql索引是一种数据结构(btree, hash)，在DQL语句的where条件中使用索引字段，则先从索引中查询，达到加速查询效果。

【重点】一张表中的索引个数，原则上没有限制，但索引不能过量创建，否则会影响DML语句的性能。

视图是一种高级子查询，即将查询语句存储，以便之后使用。视图同表一样，存在创建、修改和查询。

7.1 创建索引语法

create [ unique|fulltext|spatial ] index <索引名>
[ using <index_type> ] 
on <表名>(<字段名> [ （length）] [ ASC | DESC ])

如，创建订单表的order_pay_time字段的索引，索引值按降序排，索引以以btree结构存储。

create index pay_time_index using btree
on tb_order(order_pay_time DESC);

【注意】另外创建索引还存在两种方式，一种基于alter table ， 另一种基于create table。

-- 基于alter table
alter table <表名>
add index <索引名> <index_type> (<字段名> [(长度)] [ASC|DESC] )
| add [fulltext|spatial] index [index_name] ( <字段名> ,...)
| drop index <索引名>;


-- 基于create table
create table [if not exists] <表名>
(
    ... [字段描述],
    index <索引名> [using <index_type> ] (<字段名> [(长度)] [ASC|DESC])
)

7.2 索引视图

创建索引之后，可以通过索引视图查询已存在的索引。

-- INNODB_INDEXES
-- innodb_tables

select a.index_id,a.name idx_name,a.type, b.name tab_name
from INNODB_INDEXES a  
join innodb_tables b on (a.table_id=b.table_id)
where b.name like 'bookdb%';

7.3 创建索引案例

7.3.1 创建表时

-- 创建图书的分类表
drop table if exists tb_cate;
create table if not exists tb_cate(
    id integer primary key auto_increment,
    name varchar(20),
    ord int,
    constraint unique index name_uniq using btree  (name)
);

7.3.2 修改表时

drop table if exists tb_cate;

create table if not exists tb_cate(
    id integer primary key auto_increment,
    name varchar(20),
    ord int
);

alter table tb_cate
add  fulltext index cate_name_index (name);


insert into tb_cate(name, ord) values
('教育类',1),
('科普类',2),
('军事类',3),
('生物类',4),
('文学类',5),
('历史类',6);

【注意】select语句避免使用*而使得索引失效。

select id,name from tb_cate
where name like '%史%';

7.4 删除索引

7.4.1 删除主键和唯一索引

-- 删除唯一或普通的索引
alter table tb_cate
drop index name_uniq;

-- 如果主键是自增字段，在删除主键索引时，必须先去掉auto_increment约束。
alter table tb_cate
modify id integer;

alter table tb_cate
drop primary key;

7.4.2 删除其它索引

-- 查询相关的索引
select a.name idx_name, b.name tb_name
from information_schema.innodb_sys_indexes a  
join information_schema.innodb_sys_tables b on (a.table_id = b.table_id);

【字典表】information_schema.tables|views|schema_privileges|table_privileges

alter table <表名>
drop index <索引名>

【注意】使用索引字段作为查询条件时， select关键字选择查询的字段避免使用 *和发生全表查询的条件。

7.5 视图view

视图用于缓存复杂的sql语句， 查询的用法同表。

视图本质上是一张虚拟的表。

语法

create [or replace] view <视图名> as
<select 语句>

【注释】or replace可选，表示更新视图。

【示例】

create or replace view account_top10 as 
select * from account
where money >= 1000000;

-- 查看百万资产的大佬
select * from account_top10;

-- 创建视图 存储不同学员的总成绩
create or replace view total_score as
select sid, sum(score) as total
from SC
group by sid;

-- 查看每位学员的排名
select sid, rank() over(order by total)
from total_score;

-- 创建图书、作者和订单连接的视图
create or replace view book_author_order  as
select c.name author_name, b.book_name, a.order_title,
       a.order_price,
       a.order_pay_time,
       a.order_pay_status
from tb_order a 
natural join tb_book b
natural join tb_author c;

删除视图

drop view [if exists] <视图名>;

查看视图的字典表

-- 从information_schema字典库中找到查看视图的字典表
select table_schema, table_name as view_name 
from information_schema.views
where table_schema='bookdb';

【练习-50道练习之18题】

-- 查询各科成绩最高分、最低分和平均分及相关比例值
-- 以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率（>=60），中等率(70-80)，优良率(80-90)，优秀率(>=90)

select c.cid,c.name,
       max(score) max_score,
       min(score) min_score,
       round(avg(score),2) avg_score,
       concat(round(sum(s1)*100/count(1)),'%') as '及格率',
       concat(round(sum(s2)*100/count(1)), '%') as '中等率',
       concat(round(sum(s3)*100/count(1)), '%') as '优良率',
       concat(round(sum(s4)*100/count(1)),'%') as '优秀率'
from (
    select cid,
           score,
           if(score >= 60, 1, 0) as s1,
           if(score >=70 and score <80,1, 0) as s2,
           if(score >=80 and score <90, 1, 0 ) as s3,
           if(score >=90,1, 0) as s4
    from SC
) A
natural join Course c
group by c.cid,c.name;

【扩展-mysql数据备份与恢复】

-- mysqldump 命令备份数据到 sql脚本中
mysqldump -uroot -proot sctc > /etc/mysql/sql/sctc.sql

-- 恢复数据: 1. 创建并打开数据库  2. source /etc/mysql/sql/sctc.sql

八、mysql编程语句

官方文档： https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

8.1 函数

8.1.1 创建函数

create [definer=user[@host] ] function <函数名> ( [IN |OUT|INOUT] <参数名> <参数类型>, ...) 
returns <函数返回数据的类型>
[comment 注释] [deterministic] 
<函数的功能>;

函数中的功能语句，一般是一个表达式。IN表示接收外部传入的值; OUT表示功能语句中将结果输出; INOUT即接收外部传入的值，同时在功能语句中将结果输出。默认的模式为IN。

如， 定义百分比的函数，给定小数点的值，返回ff.ff%格式的结果

create function pct_format(val float) returns varchar(10) deterministic return concat(round(val * 100), '%');

-- 创建一个加密的函数， s是明文，flag支持0->md5, 1->sha1, 2->sha2(s, 512), other-> 原文
create function crypt(s varchar(255), flag int) 
returns varchar(255) 
deterministic return if(flag=0, md5(s), if(flag=1,  sha1(s), if(flag=2, sha2(s, 512), s)));

-- 创建可逆加密解密函数, flag为0时加密， 为1时解密
-- 只针对数值型加密与解密
create function aes(s varchar(255), k varchar(255), flag int)
returns varchar(255) deterministic return if(flag=0,lower(hex(aes_encrypt(s, k))),cast(aes_decrypt(unhex(s), k) as char));

8.1.2 使用函数

-- 使用pct_format()函数
select c.cid,c.name,
       max(score) max_score,
       min(score) min_score,
       round(avg(score),2) avg_score,
       pct_format(sum(s1)/count(1)) as '及格率',
       pct_format(sum(s2)/count(1)) as '中等率',
       pct_format(sum(s3)/count(1)) as '优良率',
       pct_format(sum(s4)/count(1)) as '优秀率'
from (
    select cid,
           score,
           if(score >= 60, 1, 0) as s1,
           if(score >=70 and score <80,1, 0) as s2,
           if(score >=80 and score <90, 1, 0 ) as s3,
           if(score >=90,1, 0) as s4
    from SC
) A
natural join Course c
group by c.cid,c.name;

输出结果：

+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| cid  | name   | max_score | min_score | avg_score | 及格率    | 中等率    | 优良率    | 优秀率    |
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01   | 语文   |      80.0 |      31.0 |     64.50 | 67%       | 33%       | 33%       | 0%        |
| 02   | 数学   |      90.0 |      30.0 |     72.67 | 83%       | 0%        | 50%       | 17%       |
| 03   | 英语   |      99.0 |      20.0 |     68.50 | 67%       | 0%        | 33%       | 33%       |
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

-- 使用加密函数
select crypt('123', 0);

mysql> select aes(123, 'abc', 0);
+----------------------------------+
| aes(123, 'abc', 0)               |
+----------------------------------+
| a4bf9afce727dbd2805393a86a24096c |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select aes('a4bf9afce727dbd2805393a86a24096c','abc',1);
+-------------------------------------------------+
| aes('a4bf9afce727dbd2805393a86a24096c','abc',1) |
+-------------------------------------------------+
| 123                                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

【待解决的问题：aes字符串加密之后的解密】

解决方法：
cast(0xababab as char) 将二进制数据转化为字符串

其它数据转化：
cast('1991-10-12' as date) 将日期格式的字符串转化为日期类型
cast(0xabfe as signed | unsigned ) 将二进制转化为有负号的数值
cast('19.88' as float) 将数据转化为float小数类型
cast(0x123 as unsigned) 将数据转化为无负号数值

8.1.3 删除函数

drop function [if exists] <函数名>;

drop function if exists crypt;

8.2 存储过程

存储过程可以实现比较复杂业务逻辑的SQL语句，SQL语句可以是DML或DQL语句。

8.2.1 创建存储过程

CREATE [DEFINER = user] procedure <存储过程名> ([proc_parameter[,...]])
[characteristic ...] 
begin
   <存储过程体>
end;


characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

-- 定义存储过程，实现计算某一学生的总成绩和成绩级别（优 >=240，良 >=200，及格>=180和不及格）
-- select * into @rowvar from xxx
-- select id,name into @id,@name from xxx

delimiter &&
create procedure stu_total_level
(sid varchar(50), out sum_score float, out level varchar(10))
begin
   select sum(score) ,
          case when sum(score)>=240 then '优'
               when sum(score)>=200 then '良'
               when sum(score)>=180 then '及格'
               else '不及格' end into sum_score, level
   from SC
   group by sid;
end &&
delimiter ;

-- delimiter 声明SQL语句结束符

delimiter &&
drop procedure if exists stu_total_level &&
create procedure stu_total_level
(sid varchar(50))
begin
   select sum(score) as total_score,
          case when sum(score)>=240 then '优'
               when sum(score)>=200 then '良'
               when sum(score)>=180 then '及格'
               else '不及格' end as level
   from SC
   group by sid;
end &&
delimiter ;

8.2.2 调用存储过程

使用call调用存储过程，如果存储过程需要变量输出，则直接使用@变量名声明

mysql> call stu_total_level('01', @score, @level);
mysql> select @score, @level;

mysql>  call stu_total_level('01')

8.2.3 删除存储过程

drop procedure if exists <存储过程名>;

8.3 触发器

trigger存储器是基于行级事件，监测发生或改变的数据。

如，删除用户信息时，在删除之前可以将数据存储到日志文件中（备份）。

8.3.1 创建触发器

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

说明： 在触发器的body中，可以使用NEW 表示插入或更新中的数据，OLD表示之前的数据。

-- 定义tb_app_user表的触发器，监听用户删除的事件，事件发生之前将数据写入到注销用户表中。
-- create table tb_app_user_del select * from tb_app_user where 1>2;

delimiter &&
create trigger app_user_del_copy before delete
on tb_app_user for each row
begin
   -- 即将删除的数据 OLD写入到用户注销表中
   insert into tb_app_user_del values (OLD.user_id, OLD.phone, '', OLD.nick_name);
end &&
delimiter ;

8.3.2 删除触发器

drop trigger if exists <触发器名>;

drop trigger if exists app_user_del_copy;

九、用户与权限

9.1 数据库用户

数据库的管理用户信息存储在mysql.user表中。

一般用户信息包含user用户名、host主机、authentication_string 口令等。

9.1.1 创建用户

create user <user>@<host> 
identified [with mysql_native_password] by <口令> 
[password expire [default | never | interval N day] ]
[account lock|unlock]

如， 创建disen用户，用于管理studb数据库，口令插件使用mysql_native_password,口令设置为disen,此用户可以在远程的任何机器中连接数据库。

create user 'disen'@'%' 
identified with mysql_native_password by 'disen';

说明： 用户创建成功后，可以远程登录 information_schema库，但没有自己管理的数据库。在没有授权之前，不能创建或管理任何对象。

编写一个python3的脚本，测试数据库的连接

from sys import argv
# 从命令行中读取参数
# 命令行中的参数位置 host port db user pwd
host,port,db,user,pwd = argv[1:]

from pymysql import Connect

conn = Connect(host=host,port=int(port),db=db,user=user,passwd=pwd,charset='utf8')
print('--conn OK---')

如，脚本名为test_conn_mysql.py，运行脚本如下：

python3 test_conn_mysql.py 127.0.0.1 3307 information_schema disen disen

9.1.2 修改用户

alter user [if exists] <user>@<host>
identified [with mysql_native_password] by <口令> 
[password expire [default | never | interval N day] ]
[account lock|unlock]

如，锁定disen用户

alter user 'disen'@'%' account lock;

如，修改disen用户的口令为disen123, 并解锁。

alter user 'disen'@'%' identified with mysql_native_password by 'disen123' account unlock;

9.1.3 删除用户

drop user [if exists] <user>@<host>

如，删除disen用户

drop user 'disen'@'%';

9.2 权限

mysql中存在非常多的权限，以便于为不同的管理员提供不同的权限操作。

9.2.1 管理员权限

参考：https://dev.mysql.com/doc/refman/8.0/en/grant.html

ALL 具有所有权限
CREATE TABLESPACE  创建表空间
CREATE USER  创建用户
FILE  输出文件和加载文件
PROCESS 访问线程信息的权限
RELOAD  重载权限，如具有flush相关的功能（flush-privileges、flush-status）
REPLICATION CLIENT 客户端备份权限
REPLICATION SLAVE  主从备份权限
SHOW DATABASES   显示数据库信息的权限
SHOW VIEW 显示视图的权限
SHUTDOWN  关闭或重启服务的权限，针对mysqladmin shutdown|restart命令。
SUPER 具有超级用户权限，如设置全局变量等
TRIGGER 具有触发器相关权限
UPDATE 具有更新表记录的权限
SELECT 具有查询表的权限
INSERT 具有插入表权限
INDEX  具有索引相关的管理权限
DELETE 具有删除表记录的权限
CREATE　View 具有创建视图的权限
CREATE 具有创建数据库和表的权限
DROP 具有删除对象的权限
ALTER 具有修改对象的权限

9.2.2 授权

grant <权限> on <数据库>[.<对象>] to <用户名>@<主机>

所有权限表示： all 或 all privileges

如，授权disen查看和修改studb库的Student表和Teacher

grant select,update on studb.Student to 'disen'@'%';
grant select,update on studb.Teacher to disen;

如，授disen用户在studb库下创建对象的权限

grant create on  studb.* to disen;

如，授disen用户在studb库下删除和更新A表的权限

grant drop,alter on studb.A to disen;

9.2.3 查询权限

show grants可以查看某一个用户下所有权限。

show grants;
show grants for <user>@<host> ;
show grants current_user();

9.2.4 撤消权限

参考：https://dev.mysql.com/doc/refman/8.0/en/revoke.html

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...

如，撤消disen用户在studb库下删除A表的权限

revoke drop on studb.A from disen;

十、SQL优化

参考：https://dev.mysql.com/doc/refman/8.0/en/optimization.html

10.1 优化SQL语句

10.2 explain优化查询

十一、Python交互MySQL

Python和mysql进行交互的库： pymysql, mysqlclient, sqlalchemy

11.1 pymysql的curd操作

pip安装： pip install pymysql

11.1.1 连接数据库

from pymysql import Connect
from pymysql.cursors import Cursor, DictCursor

# Cursor： 查询的结果的每一条记录是以tuple方式显示的，没有列名
# DictCursor: 查询的结果每一记录是dict方式显示的，key列名，value数据

conn = Connect(host='10.36.174.50', port=3307, 
              user='book', passwd='book', db='bookdb', 
              charset='utf8',
              cursorclass=DictCursor)

print('-OK-')

conn对象具有以下功能：

• .cursor(cursor=Cursor|DictCursor|None) 打开游标，返回一个Cursor实例对象
• .commit() 提交事务
• .rollback() 回滚事务
• .begin() 开启事务
• .select_db(db=数据库名) 选择数据库
• .close() 关闭连接

11.1.2 游标对象操作

游标类： pymysql.cursors.Cursor/DictCursor

游标实例职责： 执行有效的SQL语句，包含DDL/DML和DQL等语句。

11.1.2.1 核心方法

• __enter__和__exit__支持with关键字的使用
• execute(sql, args=None|( )|{ } ) 执行sql语句，sql语句中可以使用%s或%(name)s占位符，使用%s时，args是以tuple元组的方式从左到右依次指定相关数据。如果使用%(name)s时，args是以dict字典的方式指定不同name的value值。

# 使用%s占位符
c.execute('select %s,%s from %s', args=('id', 'name', 'tb_book'))

# 使用%(name)s 占位符
sql = 'insert into tb_author(name,phone) values(%(name)s, %(phone)s)'
c.execute(sql, args={'name':'disen', 'phone':'16617111129'})

• fetchone() 如果执行的语句是DQL， 此方法只获取第一条记录的数据
• fetchall() 如果执行的语句是DQL, 此方法获取所有查询记录的数据，返回一个list对象。
• callproc(name, args=()) 调用存储过程，如果存储过程有参数时，使用args传参

with conn.cursor() as c:
    # 调用存储过程
    # root用户为book用户授权：  grant all on studb.* to book;
    c.callproc('studb.stu_total_level', args=('01',))
    print(c.fetchall())

• close() 关闭游标

11.1.2.2 核心属性

• rowcount 如果执行的是DML语句，此属性则获取影响的行数。

with conn.cursor() as c:
    ret = c.execute('delete from tb_order')
    print('删除语句的结果：', ret, '影响行数:', c.rowcount)

11.1.3 事务处理

pymysql支持事务处理，默认情况下，不提交事务。

一般在确认所有的DML操作结束后，一次性提交事务。

# 正常的DML语句执行后，提交事务
with conn.cursor() as c:
    insert_sql = 'insert into tb_author(name, phone,city) values(%s,%s,%s)'
    ret = c.execute(insert_sql, args=('九江','18999991100','南昌'))
    if ret > 0:
        conn.commit() # 手动提交事务
        print('插入数据成功')
    else:
        print('插入数据失败')

# 如果DML语句执行异常，则回滚事务 rollback
with conn.cursor() as c:
    insert_sql = 'insert into tb_author(name, phone,city1) values(%s,%s,%s)'
    try:
        ret = c.execute(insert_sql, args=('九江','18999991100','南昌'))
        if ret > 0:
            conn.commit() # 手动提交事务
            print('插入数据成功')
        else:
            print('插入数据失败')
    except Exception as e:
        print('sql语句异常：', e)
        conn.rollback()

11.2 封装DB类

1. 数据库连接类 在一个连接实例中，可以执行n次数据库操作。
2. 数据库操作类，具体针对哪些对象（表）进行DML操作。

11.2.1 封装数据库连接类

from pymysql import Connect
from pymysql.cursors import DictCursor

class Conn():
    def __init__(self, **config):
        config.setdefault('charset','utf8')
        config.setdefault('cursorclass', DictCursor)
        try:
            # 创建数据库的连接
        	self.conn = Connect(**config)
        except:
            raise Exception(f'初始化的参数不正确,正确参数名:host,port,user,passwd,db,charset(utf8); 实际参数:{config}')
            
    def __enter__(self):
        return self.conn.cursor()
    
    def __exit__(self, except_type, except_val, except_tb):
        # 如果存在异常，except_type是某一个Exception类
        if except_type:  
            self.conn.rollback()
            print('数据库操作出现异常：', except_val)
        else:  # 不存在异常，即except_type为None
            self.conn.commit()

        # 如果上下文的语句块出现异常之后，返回True表示，自处理，不向解释器抛出
        # 返回False，表示异常继续向解释器抛出
        return True
            
    def close(self):
        self.conn.close()

用法

config = {
    'host': '10.36.174.50',
    'port':3307,
    'user':'book',
    'passwd': 'book',
    'db': 'bookdb'
}

with Conn(**config) as cursor:
    cursor.execute('select * from tb_author')
    print(cursor.fetchall())
# 跳出conn对象的上下文管理器 ，会调用它的__exit__()方法

conn = Conn(**config)
with conn as cursor:
    cursor.execute('select * from tb_author')
    print(*cursor.fetchall(), sep='\n')

with conn as c:
    c.execute('select * from tb_order')
    print(*c.fetchall(), sep='\n')

conn.close()

11.2.2 封装数据库操作类

操作类主要支持CURD（插入数据、更新数据、查询数据和删除数据），针对不同表的操作，尝试支持多表连接查询。

class DB():
    def __init__(self, conn):
        self.conn = conn

    def save(self,table, **item) -> bool:
        sql = 'insert into %s(%s) values(%s)'
        cols = ','.join(item)
        vals = ','.join([f'%({k})s' for k in item]) # %(name)s,%(age)s
        with self.conn as c:
            return c.execute(sql % (table, cols, vals), args=item) > 0

    def update(self, table, pk='id', **item):
        sql = f'update %s set %s where {pk}=%s'
        sets = ','.join([f'{k}=%({k})s' for k in item if k!=pk])
        with self.conn as c:
            return c.execute(sql % (table, sets, f'%({pk})s'), args=item) > 0

    def delete(self, table, pk_val, pk='id'):
        sql = f'delete from {table} where {pk}=%s'
        with self.conn as c:
            return c.execute(sql, args=(pk_val,)) > 0

    def query(self, table, *columns, where=None, args=None):
        sql = 'select %s from %s ' % (','.join(columns) if columns else '*', table)
        if where:
            sql += where

        with self.conn as c:
            c.execute(sql, args=args)
            return c.fetchall()

用法：

db = DB(conn)
print(*db.query('tb_book','book_id', 'book_name'), sep='\n')
print('--西安的作者---')
print(*db.query('tb_author','author_id', 'name', 'phone',
				where='where city=%s', args=('西安',)), sep='\n')
conn.close()

author={
'name': '李哲',
'city': '咸阳',
'phone': '18325563596',
'join_date': '2011-09-09'
}
if db.save('tb_author',**author):
	print('--新增作者成功--')
else:
	print('--新增作者失败-')

update_author={'author_id': 112, 'name':'小哲哲'}
db.update('tb_author',pk='author_id',**update_author)

print('--西安的作者---')
print(*db.query('tb_author','author_id', 'name', 'phone',
                where='where city=%s', args=('咸阳',)), sep='\n')

11.3 基于type元类设计ORM

11.3.1 type类

type类是元类的最基本的父类，用于创建类对象的。即元类是创建类的类。

Python中有两大超类（基类）： object, type

用法

# 创建员工类，包含员工的姓名name、工龄years和基本薪资salary, 同时存在加薪add_salary和减薪sub_salary两个方法。

# 一般的写法
class Person:
    def __init__(self, name, years, salary):
        self.name = name
        self.years=years
        self.salary = salary
        
    def add_salary(self, salary):
        self.salary += salary
    def sub_salary(self, salary):
        self.salary -= salary
        
# 使用type创建类
# type(类名, 父类的元组, 类属性attrs字典) 返回类的对象
def add_salary(self, salary):
    self.salary += salary
    
def sub_salary(self, salary):
    self.salary -= salary

# 定义Person
Person = type('Person', (), {'age':10})
# 定义工人类，继承Person类
Worker = type('Worker', (Person,), {'name': '', 'years': 1, 'salary': 0,
                             'add_salary': add_salary,
                             'sub_salary': sub_salary
                             })

w1 = Worker()
w1.name = 'disen'
w1.years = 10
w1.age = 20
w1.salary = 20000

w1.add_salary(1000)
print(w1.salary, w1.name, w1.years, w1.age)

w2 = Worker()
print(w2.name, w2.years, w2.salary, w2.age)

11.3.2 自定义type元类

因为元类是创建类的类，在class 语句中定义类时，可以通过metaclass指定创建类对象的元类。

用法：

# 定义一个元类
class PersonMeta(type):
    pass

# 指定哪一个元类，来创建当前类
class Person(metaclass=PersonMeta):
   pass

# 定义一个元类
class PersonMeta(type):
    def __new__(cls, cls_name, bases, attrs):
        print(cls_name, bases, attrs)
        return type(cls_name, bases, attrs)


# 指定哪一个元类，来创建当前类
class Person(object,metaclass=PersonMeta):
    age = 20
    sex = '男'

    def __init__(self, name):
       self.name = name

    def hi(self):
       print(self.name)

执行以上脚本时，创建Person对象，则PersonMeta的__new__()方法创建的。

11.3.3 ORM设计思想

ORM( Object Relationship Mapping , 对象关系映射) 是一种简化非数据库人员操作数据库思想，即将类或对象与关系型数据库的表或记录进行映射，对类的创建生成对数据库的表创建的SQL语句，对类实例的操作即是对数据库表的某一条记录操作。

类 -> 表结构

类实例 -> 表的某一条记录

从表中查询的结果 映射成 类实例的列表。

11.3.4 设计ORM相关类

如果想让某一类与表映射，必须存在类的属性与表的字段一一对应。

需要定义一个模型类的元类，获取类中声明的属性，方便之后生成相关的SQL语句。

需要定义在一个模型类的父类，确定对模型的操作（CURD) 从而生成相关的SQL语句并执行。

具体的模型类，声明类对应的表（名）、属性的字段（名）等。

需要定义一个字段类，方便生成不同的数据类型的SQL语句。

11.3.4.1 设计模型类的元类

class ModelMeta(type):
	def __new__(cls, name, bases, attrs):
        # 提取模型类的成员属性（Field字段类）
        # 创建类对象的顺序： 先父类，后当前类
        print('-->', name, bases, attrs)
        if name == 'BaseModel':
            return super().__new__(cls, name, bases, attrs)

        # 具体模型类的对象
        # 收集所有Field字段类的实例--字段实例与表的字段对应
        fields = {attr_name: field for attr_name, field in attrs.items() if isinstance(field, Field)}
        # 设置字段的对应的列名
        for attr_name, field in fields.items():
            if field.column is None:
                field.column = attr_name.lower()
        table_name = attrs['__table__'] if '__table__' in attrs else name.lower()
        # 创建模型类对象时，动态添加两个属性
        # table 表名
        # fields 字段
        attrs['table'] = table_name
        attrs['fields'] = fields
        return super().__new__(cls, name, bases, attrs)

11.3.4.2 设计模型类和字段类

在BaseModel类中，根据具体模型类的属性生成sql语句并执行。

class BaseModel(metaclass=ModelMeta):
    def __init__(self,db_conn,**attrs):
        # 支持初始化的方式实例化模型类实例，即向数据表中插入数据
        self.db_conn = db_conn
    
    def create(self): # 创建表
        # 根据模型类的描述：字段，表名， 生成create table 语句
        # 通过数据库的连接执行DDL语句
        sql = 'create table %s(%s)'
        cols = ','.join([field.to_db() for field in self.fields.values()])
        ddl_sql = sql % (self.table, cols)
        with self.db_conn as c:
            c.execute(ddl_sql)
            print('sql执行成功: ', ddl_sql)
    
    def save(self):  # 插入或更新数据
        sql = 'insert into %s(%s) values(%s)'
        cols = ','.join([field.column for field in self.fields.values()
                         if not field.auto_increment])
        vals = ','.join([ f'%({attr_name})s'
                          for attr_name,field in self.fields.items()
                          if not field.auto_increment])
        item = { attr_name: getattr(self, attr_name)
                 for attr_name,field in self.fields.items()
                 if not field.auto_increment}
        with self.db_conn as c:
            c.execute(sql % (self.table, cols, vals), args=item)
            print('新增数据成功!')
    
    def delete(self): # 删除数据
        pass
    
    def get_pk_column(self):
        for attr, field in self.fields.items():
            if field.primary:
                return attr, field.column

    def get(self, pk):
        # 根据主键值获取一条记录并返回类实例
        with self.db_conn as c:
            c.execute(f'select * from {self.table} where {self.get_pk_column()[1]}=%s',
                      args=(pk,))
            row = c.fetchone()
            return self.__to_instance(row)

    def __to_instance(self, row):  # 将某一行数据查询的数据转化为模型类实例
        model_instance = self.__class__(self.db_conn)
        for attr_name, field in self.fields.items():
            setattr(model_instance, attr_name, row[field.column])

        return model_instance

    def all(self):
        # 查询所有数据
        instances = []
        with self.db_conn as c:
            c.execute(f'select * from {self.table}')
            for row in c.fetchall():
                instances.append(self.__to_instance(row))
        return instances

因为数据库中字段类型比较多，不同的字段提供不同的SQL关键字，如支持varchar、char、float、timestamp等。

# 字段类的父类， 支持某一个属性与表的字段名映射
# 支持数据库中支持的约束： primary key,  unique, not null
class Field():
	def __init__(self, column=None, primary=False, unique=False, auto_increment=False, null=True):
        self.column = column
        self.primary=primary
        self.unique=unique
        self.auto_increment = auto_increment
        self.null = null
        
    def dbtype(self):
        raise Exception('子类必须重写此方法')
        
    def to_db(self):
        return f'{self.column} {self.dbtype()} '\
               + ('primary key ' if self.primary else '')\
               + (' unique ' if self.unique else '') \
               + (' auto_increment ' if self.auto_increment else '') \
               + ('' if self.null else ' not null ')
                
class CharField(Field):
    def __init__(self, max_length, **attrs):
        super().__init__(**attrs)
        self.max_length=max_length
    def dbtype(self):
        return f'char({self.max_length})'
    
class VarcharField(CharField):
	def dbtype(self):
        return f'varchar({self.max_length})'
   
class IntegerField(Field):
    def dbtype(self):
        return 'integer'
    
class FloatField(Field):
    def dbtype(self):
        return 'float'
    
class DecimalField(Field):
    def __init__(self, length, precise, **attrs):
        super().__init__(**attrs)
        self.length=length
        self.precise = precise
    def dbtype(self):
        return f'decimal({self.length}, {self.precise})'
    
class DateTimeField(Field):
    def dbtype(self):
        return f'timestamp'
    
class DateField(Field):
    def dbtype(self):
        return f'date'

11.3.4.3 具体的模型类

具体描述与数据库表的模型类，继承BaseModel,同时，类属性或字段使用Field的子类。

class OrderReply(BaseModel):
    __table__ = 'tb_order_reply' # 类对应的表名
    id = IntegerField(primary=True, auto_increment=True)
    order_id = IntegerField()
    content = VarcharField(max_length=200)

from db_ import DB, Conn

config = {
    'host': '10.36.174.50',
    'port': 3307,
    'user': 'book',
    'passwd': 'book',
    'db': 'bookdb'
}

conn = Conn(**config)

# 创建表
OrderReply(conn).create()

# 插入数据
reply1 = OrderReply(conn)
reply1.order_id=1
reply1.content='强烈推荐，丰常好！客服服务态度'

reply1.save()

# 查询数据
print(reply1.all())
print(reply1.get(1))

# 删除数据
r1 = OrderReply(conn).get(1)
r1.delete()

# 更新数据
r2= OrderReply(conn).get(2)
r2.order_id=3
r2.save()

# 定义与tb_author表对应的模型类
class Author(BaseModel):
    __table__ = 'tb_author'
    id = IntegerField(column='author_id', primary=True, auto_increment=True)
    name = VarcharField(max_length=50)
    phone = CharField(max_length=11, unique=True)
    join_date = DateField()
    city = VarcharField(20)

# 查询所有作者信息
authors = Author(conn).all()
    for author in authors:
        print(author.id, author.name, author.city, author.phone)

11.4 sqlalchemy的应用

文档： https://www.sqlalchemy.org/

教程： https://docs.sqlalchemy.org/en/14/tutorial/index.html

sqlalchemy是Python实现ORM设计思想的第三库，支持大多数的关系型数据库，如mysql、maridb、postgresql、 sqlserver、oracle、sqlite3等数据。但是需要不同数据库操作的driver驱动，如mysql对应的驱动库是pymysql或mysqlclient。

11.4.1 安装与连接

11.4.1.1 安装

通过pip库安装：

pip install sqlalchemy

11.4.1.2 普通连接

普通连接，适合原生的SQL语句的执行。

from sqlalchemy import create_engine

# 1. 创建引擎实例
engine = create_engine('mysql+pymysql://book:book@10.36.174.50:3307/bookdb',
                       encoding='utf8', echo=True)

# 2. 打开数据库的连接
conn = engine.connect()

# 3. 执行sql语句,返回一个游标对象
#    sqlalchemy.engine.cursor.LegacyCursorResult
cursor = conn.execute('select * from tb_author')
print(type(cursor), cursor)

# 4.获取游标的数据
ret = cursor.fetchall()
print(*ret, sep='\n')

# 5. 执行dml语句
insert_sql = 'insert into tb_author(name, city, phone) values(%s, %s, %s)'
ret = conn.execute(insert_sql,'小李子2','西安','19818818119')
print('插入成功', ret.rowcount)

insert_sql = 'insert into tb_author(name, city, phone) ' \
             'values(%(name)s, %(city)s, %(phone)s)'
ret = conn.execute(insert_sql,name='小李子12',city='西安',phone='19118818119')
print('插入成功', ret.rowcount)

11.4.1.3 会话连接

会话连接适合ORM模型类的操作, 也可以执行原生的SQL语句。

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

# 1. 创建引擎实例
engine = create_engine('mysql+pymysql://book:book@10.36.174.50:3307/bookdb',
                       encoding='utf8', echo=True)

# 2.创建会话对象，使用with
# session = Session(engine)
with Session(engine) as s:
    # 3. 会话中执行sql语句
    ret = s.execute(text('select * from tb_book where book_id=:id').bindparams(id=2))
    print(ret.one())
    # 无论是connection连接还是session，在上下文中执行完之后， 则会自动回滚事务
    # 如果执行了DML语句，则需要手动提交事务
    s.commit()

11.4.2 设计模型类

11.4.2.1 MetaData设计表

from sqlalchemy import create_engine, MetaData,Table,Column,ForeignKey,Integer,String,Date,DateTime,Float,DECIMAL
from sqlalchemy.orm import Session

# 1. 创建引擎实例
engine = create_engine('mysql+pymysql://book:book@10.36.174.50:3307/bookdb',
                       encoding='utf8', echo=True)

# 2. 创建元数据实例，用于提取表定义类的属性
metadata = MetaData(engine)

# 3. 声明新表模型类
sys_user = Table('tb_sys_user', metadata,
                 Column('id', Integer, primary_key=True,autoincrement=True),
                 Column('name', String(30), unique=True))

sys_role = Table('tb_role', metadata,
                 Column('id', Integer, primary_key=True, autoincrement=True),
                 Column('name', String(20), comment='角色名称'),
                 Column('user_id', Integer, ForeignKey('tb_sys_user.id', ondelete='CASCADE')))

# 4. 执行DDL语句
metadata.create_all()

11.4.2.2 MetaData设计类

from sqlalchemy import create_engine, MetaData,Table,Column,ForeignKey,Integer,String,Date,DateTime,Float,DECIMAL
from sqlalchemy.orm import Session, relationship
from sqlalchemy.orm import declarative_base

# 1. 创建引擎实例
engine = create_engine('mysql+pymysql://book:book@10.36.174.50:3307/bookdb',
                       encoding='utf8', echo=True)

# 2. 创建元数据实例，用于提取表定义类的属性
metadata = MetaData(engine)

# 3.声明模型类的父类
BaseModel = declarative_base(metadata=metadata)

# 4. 声明具体的模型类

class SysRole(BaseModel):
    __tablename__ = 'tb_role'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20), unique=True)

    # 如果引用的类是后声明的，则使用字符串，解析时来获取类信息
    user = relationship('SysUser', back_populates='role')
    
    def __repr__(self):
        return f"<id={self.id}, name={self.name}>"

class SysUser(BaseModel):
    __tablename__ = 'tb_sys_user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(30), unique=True)

    role_id = Column(Integer, ForeignKey('tb_role.id', ondelete='set null'))

    role = relationship(SysRole, back_populates='user')


# 5. 创建所有表对象
metadata.create_all()

11.4.3 模型类的CURD操作

文档： https://docs.sqlalchemy.org/en/14/tutorial/orm_data_manipulation.html

ORM的宗旨是简化程序对SQL编写，通过类或类实例进行数据的操作，保存实例即向数据库插入记录，查询数据的结果封装成（模型）类实例的列表。

一般情况下，使用Session会话连接对象进行模型类的操作，包含查询、保存、更新、删除以及多表关联查询或操作。

11.4.3.1 保存数据

role1 = SysRole(name='超级系统管理员')
role2 = SysRole(name='商品管理员')

with Session(engine) as s:
    # 单个实例保存: s.add(role1)
    
    # 批量实例保存
    s.add_all([role1, role2])
    # print(s.new) # 未提交到数据库的实例数据
    s.flush() # 执行以上的行为（向数据库发送DML操作）
    s.commit()
    print(role1, role2)

11.4.3.2 修改数据

修改某一实体数据，前提是先从数据库查询出来。

session = Session(engine)

# s指向的是session.__enter__()方法返回的实例对象
with session as s:
    # 获取role的id为15的角色信息
    role3 = s.get(SysRole, 15)
    print(role3)
    # 更新角色名为“系统管理员”
    role3.name = '系统管理员'
    # s.flush()
    s.commit()

11.4.3.3 删除数据

删除数据之前，先查询到实体，然后通过session删除。

session = Session(engine, autocommit=True)
# 删除商品管理员角色 ,id为
with session as s:
    r = s.get(SysRole, 16)
    s.delete(r)
    s.flush()

可以通过sqlalchemy提供的delete()快速构造deleteSQL语句

with session as s:
    # 通过sqlalchemy.delete()快速删除
    result = s.execute(delete(SysRole).where(SysRole.id==15))
    print(result.rowcount)

11.4.3.4 查询数据

一般情况下，查询数据，可以使用原生的SQL， 也可以使用更加方便的select()来查询。

from sqlalchemy.engine import LegacyCursorResult

with session as s:
    selector = select(SysRole)
    raw_sql = 'select * from tb_role'
    result: LegacyCursorResult = s.execute(selector)
    rs = result.scalars()  # .scalar_one() 转化为单个实例对象
    for role in rs:
        print(role)

说明： result.scalars()是一个将行数据转化模型类实例的生成器。

【重点】使用Query进行查询数据

with session as s:
    # 获取Query查询器
    query = s.query(SysUser)
    
    # 显示一条记录 first()或所有记录all()
    print(query.first())

with session as s:
    # 获取Query查询器
    query = s.query(SysUser)

    # .filter() 根据模型类的属性（Column类实例）查询的
    # 关系的表示： ==，!=, >=, <=
    # print(query.filter(SysUser.id<=2).all())
    # 模糊查询：.like()
    # print(query.filter(SysUser.name.like('%s%')).all())
    print(query.filter(SysUser.name.contains('s')).all())
    # in查询： .in_((v1, v2,...))
    print(query.filter(SysUser.id.in_((1, 2))).all())
    
    
    # sqlalchemy.and_()/or_()/not_() 表示多个查询条件的逻辑关系
    print(query.filter(and_(SysUser.id>=2,
                            SysUser.name.contains('s'))).all())
    
    # filter()支持带有参数名的条件字符串，然后使用.params()指定条件中的名称值
    print(query.filter(text('name = :name')).params(name='disen').all())
    
    # .filter_by() 根据当前模型类的属性进行简化的查询
    # 使用了filter()或filter_by()之后，可以使用.all()和.one()
    # filter_by()方法中，不能使用or_/and_/not_()逻辑关系函数
    print(query.filter_by(id=3, name='boss').all())
    
    
    # 排序 .desc()/.asc() 默认升序
    # 分页 .limit(page_size) / .offset(start_row) start_row从0开始
    print(query.order_by(SysUser.id).limit(2).offset(0).all())

# 聚合查询

from engine_ import session
from models import TbBook
from sqlalchemy import func

with session as s:

    # 查询原价最高的数据
    print(s.query(func.max(TbBook.src_price).label('max_price'),
          func.avg(TbBook.src_price).label('mean_price')).filter_by(author_id=110).all())

11.4.3.5 关联数据

11.4.3.5.1 关联保存数据

with session as s:
    r1 = s.get(SysRole, 1)
    # 指定关联属性的实体（SysRole的实例）
    u1 = SysUser(name='disen', role=r1)
    s.add(u1)
    s.commit()

11.4.3.5.2 关联删除数据

with session as s:
    s.delete(s.get(SysRole, 1))  # 级联置空
    s.commit()

11.4.3.5.3 关联更新数据

with session as s:
    r2 = s.get(SysRole, 2)
    u1 = s.get(SysUser,1)
    u1.role = r2  # 指定用户1的角色为r2
    s.commit()

11.4.3.5.4 关联查询数据

# 在一对多的关系中的一端（角色）
with session as s:
    r2 = s.get(SysRole,2)
    # 通过role实例的user反查询关联的用户实体
    print(r2.user)   # 显示所有用户实体（模型类的实例）

# 在多端查询（延迟查询: 用到时再查）
with session as s:
    u1 = s.get(SysUser,1)
    # 通过role实例的user反查询关联的用户实体
    print(u1, '用户角色->', u1.role.name)

11.4.4 逆向生成模型类

可以将已存在的表，生成sqlalchemy的模型类。

安装python的第三方库： pip install sqlacodegen

生成模型类的命令：

sqlacodegen mysql+pymysql://book:book@10.36.174.50:3307/bookdb > models.py

单独创建engine_.py脚本， 内容如下：

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import Session
from sqlalchemy.orm import declarative_base

# 1. 创建引擎实例
engine = create_engine('mysql+pymysql://book:book@10.36.174.50:3307/bookdb',
                       encoding='utf8', echo=True)

# 2. 创建元数据实例，用于提取表定义类的属性
metadata = MetaData(engine)

# 3.创建模型类的父类, 如果是低版本时，可以不用声明
Base = declarative_base(engine, metadata=metadata)

# 4. 创建Session
session = Session(engine)

测试生成的模型类是否可以使用：

from engine_ import session
from models import TbBook

with session as s:
    books = s.query(TbBook).all()
    for book in books:
        print(book.book_id, book.book_name, book.src_price, book.act_price)

在生成的模型类中，需要导入自定义的Base父类， 将原有的Base删除或更改, 主要修改内容如下：

# coding: utf-8
from sqlalchemy import CHAR, Column, DECIMAL, Date, Float, ForeignKey, Integer, String, TIMESTAMP, Table, Text, VARBINARY, text
from sqlalchemy.orm import relationship

# 导入的部分（自定义模块）
from engine_ import Base

class TbAppUser(Base):
    __tablename__ = 'tb_app_user'

    user_id = Column(Integer, primary_key=True)
    phone = Column(CHAR(11), unique=True)
    authentication_string = Column(String(255), nullable=False)
    nick_name = Column(String(50))


class TbAuthor(Base):
    __tablename__ = 'tb_author'

    author_id = Column(Integer, primary_key=True)
    name = Column(String(20))
    city = Column(String(20))
    phone = Column(CHAR(11))
    join_date = Column(Date)


class TbCate(Base):
    __tablename__ = 'tb_cate'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), index=True)
    ord = Column(Integer)


class TbOrder(Base):
    __tablename__ = 'tb_order'

    order_id = Column(CHAR(32), primary_key=True)
    order_title = Column(String(50))
    book_id = Column(Integer)
    order_price = Column(DECIMAL(10, 2))
    order_pay_status = Column(Integer, server_default=text("'0'"))
    order_pay_time = Column(TIMESTAMP, index=True)
    user_id = Column(Integer)


class TbOrderReply(Base):
    __tablename__ = 'tb_order_reply'

    reply_id = Column(Integer, primary_key=True)
    order_id = Column(Integer)
    content = Column(String(200))


class TbRole(Base):
    __tablename__ = 'tb_role'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)


class TbBook(Base):
    __tablename__ = 'tb_book'

    book_id = Column(Integer, primary_key=True)
    book_name = Column(String(50), unique=True)
    summary = Column(Text)
    src_price = Column(DECIMAL(5, 2))
    act_price = Column(DECIMAL(5, 2))
    cover_url = Column(String(100))
    author_id = Column(ForeignKey('tb_author.author_id', ondelete='SET NULL'), index=True)

    author = relationship('TbAuthor')


class TbSysUser(Base):
    __tablename__ = 'tb_sys_user'

    id = Column(Integer, primary_key=True)
    name = Column(String(30), unique=True)
    role_id = Column(ForeignKey('tb_role.id', ondelete='SET NULL'), index=True)

    role = relationship('TbRole')

11.4.4 逆向生成模型类

可以将已存在的表，生成sqlalchemy的模型类。

安装python的第三方库： pip install sqlacodegen

生成模型类的命令：

sqlacodegen mysql+pymysql://book:book@10.36.174.50:3307/bookdb > models.py

单独创建engine_.py脚本， 内容如下：

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import Session
from sqlalchemy.orm import declarative_base

# 1. 创建引擎实例
engine = create_engine('mysql+pymysql://book:book@10.36.174.50:3307/bookdb',
                       encoding='utf8', echo=True)

# 2. 创建元数据实例，用于提取表定义类的属性
metadata = MetaData(engine)

# 3.创建模型类的父类, 如果是低版本时，可以不用声明
Base = declarative_base(engine, metadata=metadata)

# 4. 创建Session
session = Session(engine)

测试生成的模型类是否可以使用：

from engine_ import session
from models import TbBook

with session as s:
    books = s.query(TbBook).all()
    for book in books:
        print(book.book_id, book.book_name, book.src_price, book.act_price)

在生成的模型类中，需要导入自定义的Base父类， 将原有的Base删除或更改, 主要修改内容如下：

# coding: utf-8
from sqlalchemy import CHAR, Column, DECIMAL, Date, Float, ForeignKey, Integer, String, TIMESTAMP, Table, Text, VARBINARY, text
from sqlalchemy.orm import relationship

# 导入的部分（自定义模块）
from engine_ import Base

class TbAppUser(Base):
    __tablename__ = 'tb_app_user'

    user_id = Column(Integer, primary_key=True)
    phone = Column(CHAR(11), unique=True)
    authentication_string = Column(String(255), nullable=False)
    nick_name = Column(String(50))


class TbAuthor(Base):
    __tablename__ = 'tb_author'

    author_id = Column(Integer, primary_key=True)
    name = Column(String(20))
    city = Column(String(20))
    phone = Column(CHAR(11))
    join_date = Column(Date)


class TbCate(Base):
    __tablename__ = 'tb_cate'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), index=True)
    ord = Column(Integer)


class TbOrder(Base):
    __tablename__ = 'tb_order'

    order_id = Column(CHAR(32), primary_key=True)
    order_title = Column(String(50))
    book_id = Column(Integer)
    order_price = Column(DECIMAL(10, 2))
    order_pay_status = Column(Integer, server_default=text("'0'"))
    order_pay_time = Column(TIMESTAMP, index=True)
    user_id = Column(Integer)


class TbOrderReply(Base):
    __tablename__ = 'tb_order_reply'

    reply_id = Column(Integer, primary_key=True)
    order_id = Column(Integer)
    content = Column(String(200))


class TbRole(Base):
    __tablename__ = 'tb_role'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)


class TbBook(Base):
    __tablename__ = 'tb_book'

    book_id = Column(Integer, primary_key=True)
    book_name = Column(String(50), unique=True)
    summary = Column(Text)
    src_price = Column(DECIMAL(5, 2))
    act_price = Column(DECIMAL(5, 2))
    cover_url = Column(String(100))
    author_id = Column(ForeignKey('tb_author.author_id', ondelete='SET NULL'), index=True)

    author = relationship('TbAuthor')


class TbSysUser(Base):
    __tablename__ = 'tb_sys_user'

    id = Column(Integer, primary_key=True)
    name = Column(String(30), unique=True)
    role_id = Column(ForeignKey('tb_role.id', ondelete='SET NULL'), index=True)

    role = relationship('TbRole')