MySQL自学（从入门到高手）

参考资料：
5天自学mysql（入门到高手）

MySQL基础

数据库介绍

数据库工作模式

以前，在开发程序时，我们会把很多的数据和信息存储到某个文件夹中的文件中，例如：user.txt 、db.xlsx 等。

现在，有那么一个叫：数据库管理系统（DBMS，Database Management System）的软件，可以帮助我们实现对文件夹中的文件进行操作，而我们只要学习DBMS能识别的指令， 就能控制它去帮助我们实现的文件和文件夹的处理。例如：

数据库产品

业内有很多的的数据库管理系统产品，例如：

• MySQL，原来是sun公司，后来被甲骨文收购。现在互联网企业几乎都在使用。【免费 + 收费】
• Oracle，甲骨文。收费，一般国企、事业单位居多。【收费】
• Microsoft SQL Server，微软。【收费】
• DB2，IBM。【免费 + 收费】
• SQLite，D. Richard Hipp个人开发。【免费】
• Access， 微软。【收费】
• PostgreSQL，加州大学伯克利分校。【免费】
• 等众多…

MySQL学习路线

MySQL安装

Windows安装：

方式一：自动化安装（MSI）安装

见说明文档

方式二：压缩包解压安装

第1步：下载安装

第2步：解压至安装文件夹

第3步：创建配置文件

在MySQL的安装目录下创建 my.ini 的文件，作为MySQL的配置文件。

其实，MySQL的配置文件可以放在很多的目录，下图是配置文件的优先级：

Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf D:\MySQL\MySQL Server 8.0\my.ini D:\MySQL\MySQL Server 8.0\my.cnf

建议大家还是把配置文件放在MySQL安装目录下，这样以后电脑上想要安装多个版本的MySQL时，配置文件可以相互独立不影响。

第4步：初始化

mysqld.exe  --initialize-insecure

初始化命令在执行时，会自动读取配置文件并执行初始化，此过程主要会做两件事：

• 自动创建data目录，以后我们的数据都会存放在这个目录。
• 同时创建建必备一些的数据，例如默认账户 root （无密码），用于登录MySQL并通过指令操作MySQL。

第5步：启动

启动MySQL常见的有两种方式：

1. 临时启动**

> mysqld.exe

注意：此时程序会挂起，内部就是可以接收客户端发来的MySQL指令，关闭窗口或Ctrl+c 就可以停止运行。

2. 后台启动

创建windows服务

> mysqld.exe --install mysql57

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

> net start mysql57
> net stop mysql57

剔除windows服务

mysqld.exe --remove mysql57

配置文件

在my.ini文件中可以自行添加其他配置选项。

登录密码

在windows系统中模块默认 root 账户是没有密码的，如果想要为账户设定密码，可以在利用root账户登录成功之后，执行：

set password = password("密码")

遗失密码

如果忘记了MySQL账户的密码，采取如下步骤：

1. 修改配置文件，在 [mysqld] 节点下添加 skip-grant-tables=1

   [mysqld]
   ...
   skip-grant-tables=1
   ...
   

2. 重启MySQL，再次登录时，不需要密码直接可以进去了

3. 进入数据库后执行修改密码命令

   use mysql;
   update user set authentication_string = password('新密码'),password_last_changed=now() where user='root';
   

4. 退出并再次修改配置文件，删除 [mysqld] 节点下的 skip-grant-tables=1

   [mysqld]
   ...
   # skip-grant-tables=1
   ...
   

5. 再次重启，以后就可以使用新密码登录了。

数据库管理

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

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

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

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

SQL命令操作

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

数据库操作：

• 查看当前所有的数据库： show databases;
• 创建数据库：create database <数据库名> DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

create database lab DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

• 删除数据库：drop database 数据库名;
• 进入数据库（操作数据库）：use 数据库名;

示例：

# 1.登录MySQL
wupeiqi@wupeiqideMBP ~ % /usr/local/mysql/bin/mysql -u root -p
Enter password:

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

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

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

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

# 6. 进入数据库
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

# 7. 进入mysql数据库（文件夹），查看此数据库下的所有表。
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| db                        |
| event                     |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

# 8. 退出
mysql>exit;

python操作数据库

借助第三方库pymysql实现对数据库的操作，详细代码

数据表管理

在数据库中创建数据库 和 创建Excel类似，需要指定： 表名、列名称、类型（整型、字符串或其他)。

SQL命令操作

• 进入数据库 use 数据库;，查看当前所有表：show tables;

• 创建表结构

create table 表名(
    列名  类型,
    列名  类型,
    列名  类型
)default charset=utf8;

示例：

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

注：主键一般用于表示当前这条数据的ID编号（类似于人的身份证），需要我们自己来维护一个不重复的值，比较繁琐。所以，在数据库中一般会将主键和自增结合。同时，一个表中只能有一个自增列，因此自增列一般都作为主键。

• 删除表 drop table 表名;

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

• 修改表:

  • 添加列

    alter table 表名 add 列名 类型;
    alter table 表名 add 列名 类型 DEFAULT 默认值;
    alter table 表名 add 列名 类型 not null default 默认值;
    alter table 表名 add 列名 类型 not null primary key auto_increment;
    

  • 删除列

    alter table 表名 drop column 列名;
    

  • 修改列类型

    alter table 表名 modify column 列名 类型;
    

  • 修改列 类型 + 名称

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

    注：--为MySQL中的注释标识符

  • 修改列 默认值

    ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;
    

  • 删除列 默认值

    ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
    

  • 添加主键

    alter table 表名 add primary key 列名;
    

  • 删除主键

    alter table 表名 drop primary key;
    

常见列类型

1. 整形（int）

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

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

  示例：

  mysql> create table L1(id int, uid int unsigned, zid int(5) zerofill) default charset=utf8;
  
  mysql> insert into L1(id,uid,zid) values(1,2,3);
  
  mysql> insert into L1(id,uid,zid) values(2147483641,4294967294,300000);
  

  输出结果：

  mysql> select * from L1;
  +------------+------------+--------+
  | id         | uid        | zid    |
  +------------+------------+--------+
  |          1 |          2 |  00003 |
  | 2147483641 | 4294967294 | 300000 |
  +------------+------------+--------+
  2 rows in set (0.00 sec)
  

  错误输入：数值越界

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

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

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

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

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

2. 浮点类型（float）

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

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

  示例：

  mysql> create table L2(
      id int not null primary key auto_increment,
      salary decimal(8,2)
  )default charset=utf8;
  
  mysql> insert into L2(salary) values(1.28);
  
  mysql> insert into L2(salary) values(5.289);
  
  mysql> insert into L2(salary) values(5.282);
  
  mysql> insert into L2(salary) values(512132.28);
  
  mysql> insert into L2(salary) values(512132.283);
  

  输出：

  mysql> select * from L2;
  +----+-----------+
  | id | salary    |
  +----+-----------+
  |  1 |      1.28 |
  |  2 |      5.29 |
  |  3 |      5.28 |
  |  4 | 512132.28 |
  |  5 | 512132.28 |
  +----+-----------+
  

  错误输入：数值越界

  mysql> insert into L2(salary) values(5121321.283);
  ERROR 1264 (22003): Out of range value for column 'salary' at row 1
  

• FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

  单精度浮点数（非准确小数值）：m是数字总个数，d是小数点后个数。

• DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

  双精度浮点数（非准确小数值）：m是数字总个数，d是小数点后个数。

3. 字符类型（char）

• char(m)

  定长字符串：m代表字符串的长度，最多可容纳255个字符，一般适用于固定长度的内容。

  定长的体现：即使内容长度小于m，也会占用m长度。例如：char(5)，数据是：yes，底层也会占用5个字符；如果超出m长度限制（默认MySQL是严格模式，所以会报错）。

  退出严格模式：

  # my.ini
  # 添加如下配置
  sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  

  保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）

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

  查看模式（sql-mode）：

  show variables like 'sql_mode';
  

• varchar(m)

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

  变长的体现：内容小于m时，会按照真实数据长度存储；如果超出m长度限制（（默认MySQL是严格模式，所以会报错）。

示例：

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

-- 插入多行
mysql> insert into L3(name,depart) values("wu","WU"),("wupei","ALS");

mysql> select * from L3;
+----+-------+--------+
| id | name  | depart |
+----+-------+--------+
|  1 | wu    | WU     |
|  2 | wupei | ALS    |
+----+-------+--------+

-- 非严格模式下，不会报错。
mysql> insert into L3(name,depart) values("wupeiqi","ALS");
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into L3(name,depart) values("wupei","ALSB");
ERROR 1406 (22001): Data too long for column 'depart' at row 1

-- 如果 sql-mode 中加入了 PAD_CHAR_TO_FULL_LENGTH ，则查询时char时空白会保留。
mysql> select name,length(name),depart,length(depart) from L3;
+-------+--------------+--------+----------------+
| name  | length(name) | depart | length(depart) |
+-------+--------------+--------+----------------+
| wu    |            2 | WU     |              3 |
| wupei |            5 | ALS    |              3 |
+-------+--------------+--------+----------------+

4. 文本类型（text）

• text

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

  示例：

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

• mediumtext

  A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters

• longtext

  A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1)

5. 时间类型（time）

• datetime

  YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）

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

• timestamp

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

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

示例：

mysql> create table L5(
    -> id int not null primary key auto_increment,
    -> dt datetime,
    -> tt timestamp
    -> )default charset=utf8;

mysql> insert into L5(dt,tt) values("2025-11-11 11:11:44", "2025-11-11 11:11:44");

mysql> select * from L5;
+----+---------------------+---------------------+
| id | dt                  | tt                  |
+----+---------------------+---------------------+
|  1 | 2025-11-11 11:11:44 | 2025-11-11 11:11:44 |
+----+---------------------+---------------------+

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    | 
| time_zone        | SYSTEM |
+------------------+--------+
-- “CST”指的是MySQL所在主机的系统时间，是中国标准时间的缩写，China Standard Time UT+8:00

mysql> set time_zone='+0:00';

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +00:00 |
+------------------+--------+

mysql> select * from L5;
+----+---------------------+---------------------+
| id | dt                  | tt                  |
+----+---------------------+---------------------+
|  1 | 2025-11-11 11:11:44 | 2025-11-11 03:11:44 |
+----+---------------------+---------------------+

• date

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

• time

  HH:MM:SS（'-838:59:59'/'838:59:59'）

除了上述常用类型，MySQL还有很多其他的数据类型，例如：set、enum、TinyBlob、Blob、MediumBlob、LongBlob 等，详细见官方文档

数据管理（重点）

当数据库和数据表创建完成之后，就需要对数据表中的内容进行：增、删、改、查了。

管理语句

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

• 新增数据

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

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

• 删除数据

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

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

• 修改数据

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

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

查询语句（重要）

前提条件：创建如下两张表

create database lab default charset utf8 collate utf8_general_ci;

create table depart(
	id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table info(
	id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int
)default charset=utf8;

insert into depart(title) values("开发"),("运营"),("销售");

insert into info(name,email,age,depart_id) values("武沛齐","wupeiqi@live.com",19,1);
insert into info(name,email,age,depart_id) values("于超","pyyu@live.com",49,1);
insert into info(name,email,age,depart_id) values("alex","alex@live.com",9,2);
insert into info(name,email,age,depart_id) values("tony","tony@live.com",29,1);
insert into info(name,email,age,depart_id) values("kelly","kelly@live.com",99,3);
insert into info(name,email,age,depart_id) values("james","james@live.com",49,1);
insert into info(name,email,age,depart_id) values("李杰","lijie@live.com",49,1);

1.1 条件查询

格式：select * from <表名> where <条件>

多表查询时，需要在判断字段前加上表名，例：info.age指明筛选字段的归属

（1）范围查找：

select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4;   -- id大于等于2、且小于等于4

（2）多条件查找：

mysql中同样可以使用：and、or逻辑判断关键词去扩展判断条件

select * from info where name = '武沛齐' and age = 19;
select * from info where name = 'alex' or age = 49;
select * from info where (name = '李杰' or email="pyyu@live.com")  and age=49;

（3）选择查找：

从某一些范围内，选择对应的表项

select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart); -- 等价于：select * from info where id in (1,2,3);

注意：判断条件数量/类型，必须与给定访问内的条件相对应，不允许出现where id in (select id, title from depart) ，即一对多的情况

（4）判断（存在/不存在）查找：

-- 去查数据是否存在:如果存在，继续执行前面的查询语句;如果不存在，不会继续去搜索。
select * from info where exists (select * from depart where id=5); 
select * from info where not exists (select * from depart where id=5);

（5）筛选查找：

一般情况下，查询语句需要从table中获取数据，但也可以通过条件语句筛选出临时表，并赋予别名，来执行后续查询

select * from (select * from info where id>2) as T where age > 10;

1.2 通配符

一般用于模糊搜索，适用于数据量小的情况，有两种类型的通配符%、_:

• %：匹配任意多个字符
• _：匹配一个字符，需要匹配几个字符就需要写几个_

select * from info where name like "%沛%";
select * from info where name like "%沛";
select * from info where email like "%@live.com";
select * from info where name like "武%齐";
select * from info where name like "k%y";
select * from info where email like "wupeiqi%";

select * from info where email like "_@live.com"; -- 无法匹配到
select * from info where email like "_upeiqi@live.com";
select * from info where email like "__peiqi@live.com";
select * from info where email like "__peiqi_live.co_";

1.3 映射

指定搜索字段（列）

格式：select <字段> [as <别名>]， ... from <table>

-- 最常用
select id, name				from info;
select id, name as NM 		from info;
select id, name as NM, 123  from info;

select 
	id,
	name,
	666 as num,  -- 添加新的一列，并命名为num
	( select max(id) from depart ) as mid,  -- 注意返回值(...)内的返回值，只能唯一
	( select min(id) from depart) as nid,  -- max/min/sum
	age
from info;

-- 不常用
select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1
from info;
-- 注意：逐行查询，效率很低

select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1,
	( select title from depart where depart.id=info.id) as x2
from info;

-- 添加自定义的字段
select 
	id,
	name,
	case depart_id when 1 then "第1部门" end v1
from info;

select 
	id,
	name,
	case depart_id when 1 then "第1部门" else "其他" end v2
from info;

select 
	id,
	name,
	case depart_id when 1 then "第1部门" end v1,
	case depart_id when 1 then "第1部门" else "其他" end v2,
	case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
	case when age<18 then "少年" end v4,
	case when age<18 then "少年" else "油腻男" end v5,
	case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;

1.4 排序

select * from info order by age desc; -- 倒序
select * from info order by age asc;  -- 顺序

select * from info order by id desc;
select * from info order by id asc;
select * from info order by age asc,id desc; -- 优先按照age从小到大；如果age相同则按照id从大到小。


select * from info where id>10 order by age asc,id desc;
select * from info where id>6 or name like "%y" order by age asc,id desc;

1.5 取部分

select * from info limit 5;   										-- 获取前5条数据
select * from info order by id desc limit 3;						-- 先排序，再获取前3条数据
select * from info where id > 4 order by id desc limit 3;			-- 先排序，再获取前3条数据

select * from info limit 3 offset 2;	-- 从位置2开始，向后获取前3数据

应用：Web开发中，显示最近访问的若干数据

数据库表中：1000条数据。

• 第一页：select * from info limit 10 offset 0;
• 第二页：select * from info limit 10 offset 10;
• 第三页：select * from info limit 10 offset 20;
• 第四页：select * from info limit 10 offset 30;
• …

1.6 分组

格式：select <字段>或<聚合函数表达式> , ... from <table> group by <分组依据>

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
select age,count(1) from info group by age; -- 按年龄分组，并统计各分组的数量

select depart_id,count(id) from info group by depart_id;
select depart_id,count(id) from info group by depart_id having count(id) > 2; -- 分组后，再进行条件筛选

注意：分组后再进行条件筛选，不能使用where，而需要使用having

select age,name from info group by age;  -- 不建议
select * from info where id in (select max(id) from info group by age);

select age,count(id) from info group by age having count(id) > 2;
select age,count(id) from info where id > 4 group by age having count(id) > 2;  -- 聚合条件放在having后面

到目前为止SQL执行顺序：

1. where
2. group by
3. having
4. order by
5. limit

select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
- 要查询的表info
- 条件 id>2
- 根据age分组
- 对分组后的数据再根据聚合条件过滤 count(id)>1
- 根据age从大到小排序
- 获取第1条

1.7 左右连表

前提条件：两张表具有相关性

格式：

• 左外连接：<主表> left outer join <从表> on <主表.x> = <从表.id> ，简写：select * from depart left join info on ....

• 右外连接：<从表> right outer join <主表> on <主表.x> = <从表.id>

  注：两种连接方式没有本质上的区别，使用任意的连接方式都可以。

• 内连接：<表> inner join on <条件>

  注：内连接不再区分主/次表，仅根据”连接条件“将关联项进行连接。

select * from info left outer join depart on info.depart_id = depart.id;

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;
select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

为了查看左右连表的区别，我们分别在 depart 表 和 info 中额外插入一条数据：

insert into depart(title) values("运维");

这样一来主从表就有区别：

• info主表，就以info数据为主，depart为辅。

  select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;
  
  +----+--------+------------------+-------+
  | id | name   | email            | title |
  +----+--------+------------------+-------+
  |  1 | 武沛齐 | wupeiqi@live.com | 开发  |
  |  2 | 于超   | pyyu@live.com    | 开发  |
  |  3 | alex   | alex@live.com    | 运营  |
  |  4 | tony   | tony@live.com    | 开发  |
  |  5 | kelly  | kelly@live.com   | 销售  |
  |  6 | james  | james@live.com   | 开发  |
  |  7 | 李杰   | lijie@live.com   | 开发  |
  +----+--------+------------------+-------+
  

• depart主表，，就以depart数据为主，info为辅。

  select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;
  
  +------+--------+------------------+-------+
  | id   | name   | email            | title |
  +------+--------+------------------+-------+
  |    7 | 李杰   | lijie@live.com   | 开发  |
  |    6 | james  | james@live.com   | 开发  |
  |    4 | tony   | tony@live.com    | 开发  |
  |    2 | 于超   | pyyu@live.com    | 开发  |
  |    1 | 武沛齐 | wupeiqi@live.com | 开发  |
  |    3 | alex   | alex@live.com    | 运营  |
  |    5 | kelly  | kelly@live.com   | 销售  |
  | NULL | NULL   | NULL             | 运维  |
  +------+--------+------------------+-------+
  

-- 内连接:    表  inner join 表  on 条件
select * from info inner join depart on info.depart_id=depart.id;

+----+-----------+------------------+------+-----------+----+--------+
| id | name      | email            | age  | depart_id | id | title  |
+----+-----------+------------------+------+-----------+----+--------+
|  1 | 武沛齐    | wupeiqi@live.com |   19 |         1 |  1 | 开发   |
|  2 | 于超      | pyyu@live.com    |   49 |         1 |  1 | 开发   |
|  3 | alex      | alex@live.com    |    9 |         2 |  2 | 运营   |
|  4 | tony      | tony@live.com    |   29 |         1 |  1 | 开发   |
|  5 | kelly     | kelly@live.com   |   99 |         3 |  3 | 销售   |
|  6 | james     | james@live.com   |   49 |         1 |  1 | 开发   |
|  7 | 李杰      | lijie@live.com   |   49 |         1 |  1 | 开发   |
+----+-----------+------------------+------+-----------+----+--------+

多张表连接：<主表> left outer join <从表1> on <主表.x> = <从表.id> left outer join <从表2> on <条件>

1.8 联合

上下表相连，列数相同情况下。

-- 需要保证列数需相同
select id,title from depart 
union
select id,name from info;

select id,title from depart 
union
select email,name from info;

-- 自动去重
select id from depart 
union
select id from info;

-- 保留所有
select id from depart 
union all
select id from info;

SQL执行顺序

到目前为止SQL执行顺序：

1. join
2. on
3. where
4. group by
5. having
6. order by
7. limit

表关系

在开发项目时，需要根据业务需求去创建很多的表结构，以此来实现业务逻辑，一般表结构有三类：

• 单表：单独一张表就可以将信息保存。

• 一对多，需要两张表来存储信息，且两张表存在 一对多 或 多对一关系。

• 多对多，需要三张表来存储信息，两张单表 + 关系表，创造出两个单表之间多对多关系。

  

在上述的表：一对多的 info.depart_id字段、多对多的 boy_girl.boy_id 、girl_id 直接用整型存储就可以，因为他们只要存储关联表的主键ID即可。

在开发中往往还会为他们添加一个 外键约束，保证某一个列的值必须是其他表中的特定列已存在的值，例如：info.depart_id的值必须是 depart.id中已存在的值。

外键约束格式：

constraint <外键名> foreign key <从表字段> references <主表字段>
constraint fk_info_depart foreign key (depart_id) references depart(id)

一般情况下，外键名的命名规范：fk_主表名_连表名

一对多示例

创建表时，添加外键约束：

create table depart(
	id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table info(
	id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int not null,
    constraint fk_info_depart foreign key (depart_id) references depart(id)
)default charset=utf8;

表结构已经创建好后，添加外键：

alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

删除外键：

alter table info drop foreign key fk_info_depart;

多对多示例

创建表时，添加外键约束：

create table boy(
	id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;

create table girl(
	id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;


create table boy_girl(
	id int not null auto_increment primary key,
    boy_id int not null,
    girl_id int not null,
    constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id),
    constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id)
)default charset=utf8;

表结构已经创建好后，添加外键：

alter table boy_girl add constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id);
alter table boy_girl add constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id);

删除外键：

alter table info drop foreign key fk_boy_girl_boy;
alter table info drop foreign key fk_boy_girl_girl;

在以后项目开发时，设计表结构及其关系的是一个非常重要的技能。一般项目开始开发的步骤：

• 需求调研
• 设计数据库表结构（根据需求）
• 项目开发（写代码）

大量的工作应该放在前2个步骤，前期的设计完成之后，后续的功能代码开发就比较简单了。

账户管理

在本地测试时，一般使用的都是 root 账户，拥有对MySQL数据库操作的所有权限。

如果有多个程序的数据库都放在同一个MySQL中，如果程序都用root账户就存在风险了。

解决方法：

在MySQL中支持创建账户，并给账户分配权限，例如：只拥有数据库A操作的权限、只拥有数据库B中某些表的权限、只拥有数据库B中某些表的读权限等。

用户管理

在MySQL的默认数据库 mysql 中的 user 表中存储着所有的账户信息（含账户、权限等）。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| day26              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql> select user,authentication_string,host from  mysql.user;
+----------------------------------+-------------------------------------------+-------------------------------+
| user                             | authentication_string                     | host                          |
+----------------------------------+-------------------------------------------+-------------------------------+
| root                             | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | localhost                     |
| mysql.session                    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost                     |
| mysql.sys                        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost                     |
+----------------------------------+-------------------------------------------+-------------------------------+

注：host是捆绑用户的登录ip，如果用户不是从特定ip登录，则数据库会拒绝访问

创建和删除用户

格式：create user '用户名'@'连接者的IP地址' identified by '密码';

create user wupeiqi1@127.0.0.1 identified by 'root123';
drop user wupeiqi1@127.0.0.1;

create user wupeiqi2@'127.0.0.%' identified by 'root123';
drop user wupeiqi2@'127.0.0.%';

create user wupeiqi3@'%' identified by 'root123';
drop user wupeiqi3@'%';

create user 'wupeiqi4'@'%' identified by 'root123';
drop user 'wupeiqi4'@'%';

修改用户

格式：rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

rename user wupeiqi1@127.0.0.1 to wupeiqi1@localhost;

rename user 'wupeiqi1'@'127.0.0.1' to 'wupeiqi1'@'localhost';

修改密码

格式：set password for '用户名'@'IP地址' = Password('新密码')

set password for '用户名'@'IP地址' = Password('新密码')

授权管理

创建好用户之后，还需要对用户进行授权。

授权命令

格式：grant <权限类型> on <数据库.表> to <'用户'@'IP地址'>

grant all privileges on *.* TO 'wupeiqi'@'localhost';         -- 用户wupeiqi拥有所有数据库的所有权限
grant all privileges on day26.* TO 'wupeiqi'@'localhost';     -- 用户wupeiqi拥有数据库day26的所有权限
grant all privileges on day26.info TO 'wupeiqi'@'localhost';  -- 用户wupeiqi拥有数据库day26中info表的所有权限

grant select on day26.info TO 'wupeiqi'@'localhost';          -- 用户wupeiqi拥有数据库day26中info表的查询权限
grant select,insert on day26.* TO 'wupeiqi'@'localhost';      -- 用户wupeiqi拥有数据库day26所有表的查询和插入权限

grant all privileges on day26db.* to 'wupeiqi4'@'%';

flush privileges;   -- 将数据读取到内存中，从而立即生效。

• 权限类型：

  all privileges  除grant外的所有权限
  select          仅查权限
  select,insert   查和插入权限
  ...
  usage                   无访问权限
  alter                   使用alter table
  alter routine           使用alter procedure和drop procedure
  create                  使用create table
  create routine          使用create procedure
  create temporary tables 使用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 outfile 和 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(关闭MySQL)
  super                   􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆
  replication client      服务器位置的访问
  replication slave       由复制从属使用
  

• 授权对象（数据库和表）

  数据库名.*            数据库中的所有
  数据库名.表名          指定数据库中的某张表
  数据库名.存储过程名     指定数据库中的存储过程
  *.*                  所有数据库
  

查看授权

格式：show grants for '用户'@'IP地址'

show grants for 'wupeiqi'@'localhost';
show grants for 'wupeiqi4'@'%';

取消授权

格式：revoke 权限 on 数据库.表 from '用户'@'IP地址'

revoke ALL PRIVILEGES on day26.* from 'wupeiqi'@'localhost';
revoke ALL PRIVILEGES on day26db.* from 'wupeiqi4'@'%';

flush privileges;   -- 将数据读取到内存中，从而立即生效。

一般情况下，在很多的 正规 公司，数据库都是由 DBA 来统一进行管理，DBA为每个项目的数据库创建用户，并赋予相关的权限。

MySQL进阶

1. 数据索引

在数据库中索引最核心的作用是：加速查找。 例如：在含有300w条数据的表中查询，无索引需要700秒，而利用索引可能仅需1秒。

在开发过程中会为哪些 经常会被搜索的列 创建索引，以提高程序的响应速度。例如：查询手机号、邮箱、用户名等。

1.1 索引原理

索引的底层是基于B+Tree的数据结构存储的：

很明显，如果有了索引结构的查询效率比表中逐行查询的速度要快很多且数据量越大越明显（B+Tree结构）

数据库的索引是基于上述B+Tree的数据结构实现，但在创建数据库表时，如果指定不同的引擎，底层使用的B+Tree结构的原理有些不同:

• myisam引擎：非聚簇索引（数据 和 索引结构 分开存储）

• innodb引擎：聚簇索引（数据 和 主键索引结构 存储在一起）

1.1.1 非聚簇索引（mysiam引擎）

create table 表名(
    id int not null auto_increment primary key, 
    name varchar(32) not null,
    age int
)engine=myisam default charset=utf8;

1.1.2 聚簇索引（innodb引擎）

create table 表名(
    id int not null auto_increment primary key, 
    name varchar(32) not null,
    age int
)engine=innodb default charset=utf8;

1.1.3 两种索引方式对比

文件存储形式：

• innodb包含：*.frm、*.ibd两种文件
  • *.frm：存储表结构
  • *.ibd：存储数据和索引结构
• mysiam包含：*.frm、*.MYD、*.MYI三种文件
  • *.frm：存储表结构
  • *.MYD：存储数据
  • *.MYI：存储索引结构

上述 聚簇索引 和 非聚簇索引 底层均利用了B+Tree结构结构，只不过内部数据存储有些不同罢了。

在企业开发中一般都会使用 innodb 引擎（内部支持事务、行级锁、外键等特点），在MySQL5.5版本之后默认引擎也是innodb。

mysql> show create table users \G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  `age` int(11) DEFAULT '5',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show index from users \G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE   -- 虽然显示BTree，但底层数据结构基于B+Tree。
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified

1.2 常见索引

在开发过程中常见的索引类型有：

• 主键索引：加速查找、不能为空、不能重复。 + 联合主键索引
• 唯一索引：加速查找、不能重复、只允许有一个为空。 + 联合唯一索引
• 普通索引：加速查找。 + 联合索引

1.2.1 主键和联合主键索引

-- 主键索引
create table 表名(
    id int not null auto_increment primary key,   -- 主键
    name varchar(32) not null
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(id)
);

-- 联合主键索引
create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(列1,列2)          -- 如果有多列，称为联合主键（不常用且仅myisam引擎支持）
);

已存在字段创建主键索引：

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

删除主键索引：

alter table 表名 drop primary key;

注意：删除索引时可能会报错，自增列必须定义为键（自增或唯一）。

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

-- 解决方法：
alter table 表 change id id int not null;

1.2.2 唯一和联合唯一索引

-- 唯一索引
create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique ix_name (name),
    unique ix_email (email),
);

-- 联合唯一索引
create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    unique (列1,列2)               -- 如果有多列，称为联合唯一索引。
);

联合唯一索引允许索引内的独立字段重复，但不允许组合重复

已存在字段创建唯一索引：

create unique index 索引名 on 表名(列名);

删除唯一索引：

drop unique index 索引名 on 表名;

1.2.3 索引和联合索引

-- 普通索引
create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (email),
    index ix_name (name),
);

-- 联合索引
create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (name,email)     -- 如果有多列，称为联合索引。
);

已存在字段创建索引：

create index 索引名 on 表名(列名);

删除唯一索引：

drop index 索引名 on 表名;

1.3 操作表 & 索引失效

在数据库的表中创建索引之后优缺点如下：

• 优点：查找速度快、约束（唯一、主键、联合唯一）
• 缺点：插入、删除、更新速度比较慢，因为每次操作都需要调整整个B+Tree的数据结构关系。

所以，在表中不要无节制的去创建索引。同时，在表中创建索引后，查询时一定要命中索引！！！

在开发中，我们会对表中经常被搜索的列创建索引，从而提高程序的响应速度，但需要保证索引命中。

CREATE TABLE `big` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) DEFAULT NULL,
    `email` varchar(64) DEFAULT NULL,
    `password` varchar(64) DEFAULT NULL,
    `age` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),                       -- 主键索引
    UNIQUE KEY `big_unique_email` (`email`),  -- 唯一索引
    index `ix_name_pwd` (`name`,`password`)     -- 联合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8

无法命中索引的情况：

• 类型不一致

  select * from big where name = 123;		-- 未命中(name为string类型，无法通过索引查询)
  select * from big where email = 123;	-- 未命中
  
  特殊的:主键
  	select * from big where id = "123";	-- 命中
  

• 使用不等于

  select * from big where name != "武沛齐";				-- 未命中
  select * from big where email != "wupeiqi@live.com";  -- 未命中
  
  特殊的:主键
  	select * from big where id != 123;	-- 命中
  

• or：当or条件中有未建立索引的列才失效

  select * from big where id = 123 or password="xx";			-- 未命中
  select * from big where name = "wupeiqi" or password="xx";	-- 未命中
  
  特别的：
  	select * from big where id = 10 or password="xx" and name="xx"; -- 命中
  

• 排序：当根据索引排序时候，选择的映射如果不是索引，则不走索引

  select * from big order by name asc;     -- 未命中
  select * from big order by name desc;    -- 未命中
  
  select name from big order by name asc;     -- 命中
  select name from big order by name desc;     -- 命中
  
  特别的:主键
  	select * from big order by id desc;  -- 命中
  

• 模糊匹配时

  select * from big where name like "%u-12-19999";	-- 未命中
  select * from big where name like "_u-12-19999";	-- 未命中
  select * from big where name like "wu-%-10";		-- 未命中
  
  特别的：
  	select * from big where name like "wu-1111-%";	-- 命中（通配符在后面）
  	select * from big where name like "wuw-%";		-- 命中
  

• 使用函数

  select * from big where reverse(name) = "wupeiqi";  -- 未命中
  
  特别的：
  	select * from big where name = reverse("wupeiqi");  -- 命中
  

• 最左前缀，如果是联合索引，要遵循最左前缀原则

  如果联合索引为：(name,password)
      name and password       -- 命中
      name                 	-- 命中
      password                -- 未命中
      name or password       	-- 未命中
  

1.4 执行计划

通过MySQL执行计划操作，预判SQL的执行性能（只能给到一定的参考，不一定完全能预判准确）。

格式：explain + SQL语句;

其中判断执行效率的重要依据为：type字段

按照执行效率（查询快慢）从低到高依次：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

• ALL：全表扫描，数据表从头到尾找一遍。(一般未命中索引，都是会执行权标扫描)

  select * from big;
  
  特别的：如果有limit，则找到之后就不在继续向下扫描.
  	select * from big limit 1;
  

• INDEX：全索引扫描，对索引从头到尾找一遍

  explain select id from big;
  explain select name from big;
  

• RANGE：对索引列进行范围查找

  explain select * from big where id > 10;
  explain select * from big where id in (11,22,33);
  explain select * from big where id between 10 and 20;
  explain select * from big where name > "wupeiqi" ;
  

• INDEX_MERGE：合并索引，使用多个单列索引搜索

  explain select * from big where id = 10 or name="武沛齐";
  

• REF：根据 索引 直接去查找（非键）

  select *  from big where name = '武沛齐';
  

• EQ_REF：连表操作时常见

  explain select big.name,users.id from big left join users on big.age = users.id;
  

• CONST：常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快

  explain select * from big where id=11;					-- 主键
  explain select * from big where email="w-11-0@qq.com";	-- 唯一索引
  

• SYSTEM：系统，表仅有一行(=系统表)。这是const联接类型的一个特例

  explain select * from (select * from big where id=1 limit 1) as A;
  

其他字段：

• id：查询顺序标识

• select_type：查询类型

  • SIMPLE 简单查询
  • PRIMARY 最外层查询
  • SUBQUERY 映射为子查询
  • DERIVED 子查询
  • UNION 联合
  • UNION RESULT 使用联合的结果
  • …

• table：正在访问的表名

• partitions：涉及的分区（MySQL支持将数据划分到不同的idb文件中，详单与数据的拆分）。 一个特别大的文件拆分成多个小文件（分区）

• possible_keys：查询涉及到的字段上若存在索引，则该索引将被列出，即：可能使用的索引

• key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。例如：有索引但未命中，则possible_keys显示、key则显示NULL

• key_len：表示索引字段的最大可能长度。(类型字节长度 + 变长2 + 可空1)，例如：key_len=195，类型varchar(64)，195=64*3+2+1

• ref：连表时显示的关联信息。例如：A和B连表，显示连表的字段信息

• rows：估计读取的数据行数（只是预估值）

  explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c";
  explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c" limit 1;
  

• filtered：返回结果的行占需要读到的行的百分比

  	explain select * from big where id=1;  -- 100，只读了一个1行，返回结果也是1行。
  	explain select * from big where password="27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3";  -- 10，读取了10行，返回了1行。
  	注意：密码27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3在第10行
  

• extra：该列包含MySQL解决查询的详细信息

  • “Using index”：此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
  • “Using where”：这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引
  • “Using temporary”：这意味着mysql在对查询结果排序时会使用一个临时表。
  • “Using filesort”：这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
  • “Range checked for each record(index map: N)”：这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

2. 函数

MySQL中提供了很多函数，为我们的SQL操作提供便利，例如：

-- 求和，求最大/小值、平均值
mysql> select count(id), max(id),min(id),avg(id) from d1;
+-----------+---------+---------+---------+
| count(id) | max(id) | min(id) | avg(id) |
+-----------+---------+---------+---------+
|         3 |       4 |       1 |  2.6667 |
+-----------+---------+---------+---------+

-- 反转字段
mysql> select id,reverse(name) from d1;
+----+---------------+
| id | reverse(name) |
+----+---------------+
|  1 | 齐沛武        |
|  3 | xxx           |
|  4 | uyyp          |
+----+---------------+

-- 拼接、求当前时间、序列化
mysql> select id, reverse(name),concat(name,name), NOW(), DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s')  from d1;
+----+---------------+--------------------+---------------------+-----------------------------------------+
| id | reverse(name) | concat(name,name)  | NOW()               | DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s') |
+----+---------------+--------------------+---------------------+-----------------------------------------+
|  1 | 齐沛武        | 武沛齐武沛齐       | 2021-05-27 09:18:07 | 2021-05-27 09:18:07                     |
|  3 | xxx           | xxxxxx             | 2021-05-27 09:18:07 | 2021-05-27 09:18:07                     |
|  4 | uyyp          | pyyupyyu           | 2021-05-27 09:18:07 | 2021-05-27 09:18:07                     |
+----+---------------+--------------------+---------------------+-----------------------------------------+

-- 休眠函数
mysql> select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+

部分函数列表：

CHAR_LENGTH(str)
    返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
    对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

CONCAT(str1,str2,...)
    字符串拼接
    如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
    字符串拼接（自定义连接符）
    CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

CONV(N,from_base,to_base)
    进制转换
    例如：
        SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

FORMAT(X,D)
    将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
    例如：
        SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr)
    在str的指定位置插入字符串
        pos：要替换位置其实位置
        len：替换的长度
        newstr：新字符串
    特别的：
        如果pos超过原字符串长度，则返回原字符串
        如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr)
    返回字符串 str 中子字符串的第一个出现位置。

LEFT(str,len)
    返回字符串str 从开始的len位置的子序列字符。

LOWER(str)
    变小写

UPPER(str)
    变大写

LTRIM(str)
    返回字符串 str ，其引导空格字符被删除。
RTRIM(str)
    返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)
    获取字符串子序列

LOCATE(substr,str,pos)
    获取子序列索引位置

REPEAT(str,count)
    返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
    若 count <= 0,则返回一个空字符串。
    若str 或 count 为 NULL，则返回 NULL 。
REPLACE(str,from_str,to_str)
    返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
    返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)
    从字符串str 开始，返回从后边开始len个字符组成的子序列

SPACE(N)
    返回一个由N空格组成的字符串。

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
    不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

    mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'

    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'

    mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'

    mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'

    mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'

    mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
    返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

    mysql> SELECT TRIM('  bar   ');
            -> 'bar'

    mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
            -> 'barxxx'

    mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
            -> 'bar'

    mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
            -> 'barx'   

更多函数：https://dev.mysql.com/doc/refman/5.7/en/functions.html

自定义函数：

• 创建函数

  delimiter $$  -- 将语句终止符改为$$
  -- 函数体
  create function f1(
      i1 int,
      i2 int)
  returns int
  BEGIN
      declare num int;
      declare maxId int;
      select max(id) from big into maxId;
      
      set num = i1 + i2 + maxId;
      return(num);
  END $$ -- 结束
  delimiter ; -- 将语句终止符改回;
  

• 执行函数

  select f1(11,22);
  select f1(11,id),name from d1;
  

• 删除函数

  select f1(11,id),name from d1;
  

3. 存储过程

存储过程是一个存储在MySQL中的SQL语句集合（类似于批处理文件），当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。主要是DBA（数据库管理员）关注的内容，开发人员不作为重点要求。

• 创建存储过程

  delimiter $$
  create procedure p1()
  BEGIN
      select * from d1; -- 类似于：函数体
  END $$
  delimiter ;
  

• 执行存储过程

  call p1(); -- sql语句调用方法
  

  # python 调用方法
  import pymysql
  
  conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
  cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
  # 执行存储过程
  cursor.callproc('p1')
  result = cursor.fetchall()
  print(result)
  
  cursor.close()
  conn.close()
  

• 删除存储过程

  drop procedure proc_name;
  

3.1 参数类型

存储过程的参数可以有如下三种：

• in，仅用于传入参数用
• out，仅用于返回值用
• inout，既可以传入又可以当作返回值

创建过程：

delimiter $$
create procedure p2(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    
    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;
    
    set i3 = i3 + 100;

end $$
delimiter ;

执行（调用）过程：

set @t1 =4; -- 类似于python的引用，取得的是变量的地址
set @t2 = 0;
CALL p2 (1, 2 ,@t1, @t2); 
SELECT @t1,@t2;

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 执行存储过程
cursor.callproc('p2',args=(1, 22, 3, 4))

# 获取执行完存储的参数
cursor.execute("select @_p2_0,@_p2_1,@_p2_2,@_p2_3")
result = cursor.fetchall()
# result: {"@_p2_0":11 }

cursor.close()
conn.close()

print(result)

3.2 返回值 & 结果集

创建过程：

delimiter $$
create procedure p3(
    in n1 int,
    inout n2 int,
    out n3 int
)
begin
    set n2 = n1 + 100;
    set n3 = n2 + n1 + 100;
    select * from d1;  -- 结果集
end $$
delimiter ;

执行过程：

set @t1 =4;
set @t2 = 0;
CALL p3 (1,@t1, @t2);
SELECT @t1,@t2;

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p3',args=(22, 3, 4))
table = cursor.fetchall() # 接收执行存储过程的结果集

# 获取执行完存储的参数
cursor.execute("select @_p3_0,@_p3_1,@_p3_2")
rets = cursor.fetchall()

cursor.close()
conn.close()

print(table)
print(rets)

3.3 事务 & 异常

事务：要么全部语句都执行成功，要么一条都不执行（回退已执行的结果）。

创建异常：

delimiter $$
create PROCEDURE p4(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 

创建事务：

START TRANSACTION;  -- 开启事务
delete from d1;
insert into tb(name)values('seven');
COMMIT;  -- 提交事务

-- SUCCESS 
set p_return_code = 0; 

END $$
delimiter ; 

执行存储过程：

set @ret =100;
CALL p4(@ret);
SELECT @ret;

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p4',args=(100))

# 获取执行完存储的参数
cursor.execute("select @_p4_0")
rets = cursor.fetchall()

cursor.close()
conn.close()

print(table)
print(rets)

3.4 游标

个人理解：类似于可迭代类型

delimiter $$
create procedure p5()
begin 
    declare sid int;
    declare sname varchar(50); 
    declare done int default false;


    declare my_cursor CURSOR FOR select id,name from d1;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    open my_cursor;
        xxoo: LOOP
            fetch my_cursor into sid,sname;
            IF done then 
                leave xxoo;
            END IF;
            insert into t1(name) values(sname);
        end loop xxoo;
    close my_cursor;
end $$
delimiter ; 

call p5();

4. 视图

视图其实是一个虚拟表（非真实存在），其本质是：根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用**“名称”**即可获取结果集，并可以将其当作表来使用。

SELECT
    *
FROM
    (SELECT nid,name FROM tb1 WHERE nid > 2) AS A
WHERE
    A.name > 'alex';

• 创建视图

  create view v1 as select id,name from d1 where id > 1;
  

• 使用视图

  select * from v1;
  
  -- select * from (select id,name from d1 where id > 1) as v1;
  

• 删除视图

  drop view v1;
  

• 修改视图

  alter view v1 as SQL语句
  

注意：基于视图只能查询，针对视图不能执行 增加、修改、删除。 如果源表发生变化，视图表也会发生变化。

5. 触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器。

创建触发器：

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW -- 每一行都执行
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

删除触发器：

DROP TRIGGER tri_after_insert_tb1;

示例：

• 在 t1 表中插入数据之前，先在 t2 表中插入一行数据。

  delimiter $$
  CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW
  BEGIN
  	-- 例如需要向表t1中插入：insert into t1(id,name,email)values(1,"alex","xxx@qq.com")
  	-- 可通过 NEW.id  NEW.name  NEW.email 代指新插入的数据
  	IF NEW.name = 'alex' THEN
          INSERT INTO t2 (name) VALUES(NEW.id);
      END IF;
  
  END $$
  delimiter ;
  

• 在t1表中删除数据之后，再在t2表中插入一行数据。

  delimiter $$
  CREATE TRIGGER tri_after_insert_t1 AFTER DELETE ON t1 FOR EACH ROW
  BEGIN
  
  IF OLD.name = 'alex' THEN
      INSERT INTO t2 (name) VALUES(OLD.id);
  END IF;
  
  END $$
  delimiter ;
  

​ 特别的：NEW表示新数据，OLD表示原来的数据。

MySQL实战

Python 操作数据库

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

pip3 install pymysql

数据库管理

Python操作数据库本质上与直接发送 SQL指令 都是相同的，只是连接的方式和操作形式不同而已，操作代码如下所示：

0. 创建数据库

无法通过Python去创建数据库，数据库的创建操作只能预先执行。

1. 连接数据库

import pymysql

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

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

2. 查看数据库

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

3. 创建数据库

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

4. 删除数据库

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

5.进入数据库，查看表

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

数据表管理

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

1. 查看数据表

cursor.execute("use db4")
cursor.execute("show tables")
result = cursor.fetchall()
print(result)

2. 创建数据表

cursor.execute("use db4")
sql = """
create table L4(
    id int not null primary key auto_increment,
    title varchar(128),
    content text,
    ctime datetime
)default charset=utf8;
"""
cursor.execute(sql)
conn.commit()

数据管理

数据的增、删、改操作都需要执行.commit()方法

1. 新增

cursor.execute("insert into tb1(name,password) values('武沛齐','123123')")
conn.commit()

2. 删除

cursor.execute("delete from tb1 where id=1")
conn.commit()

3. 修改

cursor.execute("update tb1 set name='xx' where id=1")
conn.commit()

4. 查询

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

• fetchone()：仅返回第一行数据，返回值包括

  • None：数据不存在；
  • Data：查询的匹配的数据行；

• fetchall()：返回所有数据项

  • None：数据不存在；
  • Data：((data1), (data2), …)

SQL注入

SQL注入原理

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

import pymysql

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

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

sql = "select * from users where name='{}' and password='{}'".format(user, pwd)
# 基于字符串格式化来 拼接SQL语句:
# 正常情况下：sql = "select * from users where name='alex' and password='123'"
# SQL注入：sql = "select * from users where name='' or 1=1 -- ' and password='123'"

cursor.execute(sql)

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

cursor.close()
conn.close()

正常情况下，sql语句应该拼接为：

sql = "select * from users where name='alex' and password='123'"

SQL注入过程中，输入内容为：user = ' or 1=1 -- ，pwd = 123（任意密码），因此sql语句拼接为：

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

• name='' or 1=1 将被判别为恒真式，进而获取访问权限
• -- ' and password='123'将当作注释，不作执行

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

解决方法

基本思路：对输入指令进行语法检查

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

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()
cursor.execute("select * from users where name=%s and password=%s", [user, pwd])
# 或 cursor.execute("select * from users where name=%(n1)s and password=%(n2)s", {"n1": user, 'n2': pwd})

MySQL实战

1. 事务

事务：主要用于执行原子性操作。 简单来说：要成功都成功；要失败都失败。

事务的具有四大特性（ACID）：

• 原子性（Atomicity）

  原子性是指事务包含的所有操作不可分割，要么全部成功，要么全部失败回滚。

• 一致性（Consistency）

  执行的前后数据的完整性保持一致。

• 隔离性（Isolation）

  一个事务执行的过程中,不应该受到其他事务的干扰。

• 持久性（Durability）

  事务一旦结束，数据就持久化存储到数据库

注意：innodb引擎中支持事务，myisam不支持。

示例：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(32) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QnhXgZ8v-1689237781861)(assets/image-20210527145549634.png)]

例如：李杰 给 武沛齐 转账 100，那就会涉及2个步骤。

• 李杰账户 减100
• 武沛齐账户 加 100

这两个步骤必须同时完成才算完成，并且如果第一个完成、第二步失败，还是回滚到初始状态。

1.1 MySQL客户端操作

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | wupeiqi |    5    |
|  2 |  alex   |    6    |
+----+---------+---------+
3 rows in set (0.00 sec)

mysql> begin; -- 或者使用命令：start transaction;  -- 开启事务; 
Query OK, 0 rows affected (0.00 sec)

mysql> update users set amount=amount-2 where id=1;   -- 执行操作1
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update users set amount=amount+2 where id=2;   -- 执行操作2
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;  -- 提交事务
# mysql> rollback; -- 或者执行“回滚”操作（回到原来的状态）
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | wupeiqi |    3    |
|  2 |  ale x  |    8    |
+----+---------+---------+
3 rows in set (0.00 sec)

1.2 python代码

import pymysql

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

# 开启事务
conn.begin()

try:
    cursor.execute("update users set amount=1 where id=1")
    int('asdf')
    cursor.execute("update tran set amount=2 where id=2")
except Exception as e:
    # 回滚
    print("回滚")
    conn.rollback()
else:
    # 提交
    print("提交")
    conn.commit()

cursor.close()
conn.close()

2. 锁

在用MySQL时，不知你是否会疑问：同时有很多做更新、插入、删除动作，MySQL如何保证数据不出错呢？

MySQL中自带了锁的功能，可以帮助我们实现开发过程中遇到的同时处理数据的情况。对于数据库中的锁，从锁的范围来讲有：

• 表级锁，即A操作表时，其他人对整个表都不能操作，等待A操作完之后，才能继续。
• 行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待A操作完之后，才能继续。

注意：

• MYISAM支持表锁，不支持行锁；
• InnoDB引擎支持行锁和表锁；

即：在MYISAM下如果要加锁，无论怎么加都会是表锁。 在InnoDB引擎支持下如果是基于索引查询的数据则是行级锁，否则就是表锁。

所以，一般情况下我们会选择使用innodb引擎，并且在 搜索 时也会使用索引（命中索引）。

接下来的操作就基于innodb引擎来操作：

CREATE TABLE `L1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

在innodb引擎中，update、insert、delete的行为内部都会先申请锁（排它锁），申请到之后才执行相关操作，最后再释放锁。

当多个人同时像数据库执行：insert、update、delete等操作时，内部加锁后会排队逐一执行。

而select操作则默认不会申请锁

如果，你想要让select去申请锁，则需要配合 事务 + 特殊语法来实现。

• for update，排它锁，加锁之后，其他不可以读写。

  begin; 
  	select * from L1 where name="武沛齐" for update;    -- name列不是索引（表锁）
  commit;
  

  begin; -- 或者 start transaction;
  	select * from L1 where id=1 for update;			  -- id列是索引（行锁）
  commit;
  

• lock in share mode ，共享锁，加锁之后，其他可读但不可写。

  begin; 
  	select * from L1 where name="武沛齐" lock in share mode;    -- 假设name列不是索引（表锁）
  commit;
  

  begin; -- 或者 start transaction;
  	select * from L1 where id=1 lock in share mode;           -- id列是索引（行锁）
  commit;
  

2.1 排它锁

排它锁（ for update），加锁之后，其他事务不可以读写。

应用场景：总共100件商品，每次购买一件需要让商品个数减1 。

A: 访问页面查看商品剩余 100
B: 访问页面查看商品剩余 100

此时 A、B 同时下单，那么他们同时执行SQL：
	update goods set count=count-1 where id=3
由于Innodb引擎内部会加锁，所以他们两个即使同一时刻执行，内部也会排序逐步执行。


但是，当商品剩余 1个时，就需要注意了。
A: 访问页面查看商品剩余 1
B: 访问页面查看商品剩余 1

此时 A、B 同时下单，那么他们同时执行SQL：
	update goods set count=count-1 where id=3
这样剩余数量就会出现 -1，很显然这是不正确的，所以应该怎么办呢？


这种情况下，可以利用 排它锁，在更新之前先查询剩余数量，只有数量 >0 才可以购买，所以，下单时应该执行：
	begin; -- start transaction;
	select count from goods where id=3 for update;
	-- 获取个数进行判断
	if 个数>0:
		update goods set count=count-1 where id=3;
	else:
		-- 已售罄
	commit;

基于Python代码示例：

import pymysql
import threading


def task():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
    cursor = conn.cursor(pymysql.cursors.DictCursor) # fetch得到的结果为：元组套字典 ( {"id":1,"age":10},{"id":2,"age":10}, ... ) 
    # cursor = conn.cursor()  # fetch得到的结果为：元组套元组 ((1,10),(2,10))
	
    # 开启事务
    conn.begin()

    cursor.execute("select id,age from tran where id=2 for update")
    # fetchall                     # 得到的结果：( {"id":1,"age":10},{"id":2,"age":10}, ... )  
    result = cursor.fetchone()     # 得到的结果：{"id":1,"age":10}   
    current_age = result['age']
    
    if current_age > 0:
        cursor.execute("update tran set age=age-1 where id=2")
    else:
        print("已售罄")

    conn.commit()

    cursor.close()
    conn.close()


def run():
    for i in range(5):
        t = threading.Thread(target=task)
        t.start()

if __name__ == '__main__':
    run()

2.2 共享锁

共享锁（ lock in share mode），可以读，但不允许写。

加锁之后，后续其他事物可以可以进行读，但不允许写（update、delete、insert），因为写的默认也会加锁。

3. 数据库连接池

在操作数据库时需要使用数据库连接池。

pip3.9 install pymysql
pip3.9 install dbutils

import threading
import pymysql
from dbutils.pooled_db import PooledDB

MYSQL_DB_POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,     # ping MySQL服务端，检查是否服务可用。
    # 包含选择参数：0 = None = never, 1 = default = whenever it is requested, 
    # 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


def task():
    # 去连接池获取一个连接
    conn = MYSQL_DB_POOL.connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    cursor.execute('select sleep(2)')
    result = cursor.fetchall()
    print(result)

    cursor.close()
    # 将连接交换给连接池
    conn.close()

def run():
    for i in range(10):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

4. SQL工具类

4.1 单例模式

仅进行一次实例化，外部程序调用的是实例化后的实例对象

# db.py
import pymysql
from dbutils.pooled_db import PooledDB

class DBHelper(object):

    def __init__(self):
        # TODO 此处配置，可以去配置文件中读取。
        self.pool = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host='127.0.0.1',
            port=3306,
            user='root',
            password='root123',
            database='userdb',
            charset='utf8'
        )

    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

    def close_conn_cursor(self, *args):
        for item in args:
            item.close()

    def exec(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        conn.commit()

        self.close_conn_cursor(conn, cursor)

    def fetch_one(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = cursor.fetchone()

        self.close_conn_cursor(conn, cursor)
        return result

    def fetch_all(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = cursor.fetchall()

        self.close_conn_cursor(conn, cursor)

        return result

db = DBHelper() # 实例化（单次）

# 外部程序
from db import db # 导入实例对象

db.exec("insert into d1(name) values(%(name)s)", name="武沛齐666")

ret = db.fetch_one("select * from d1")
print(ret)

ret = db.fetch_one("select * from d1 where id=%(nid)s", nid=3)
print(ret)

ret = db.fetch_all("select * from d1")
print(ret)

ret = db.fetch_all("select * from d1 where id>%(nid)s", nid=2)
print(ret)

4.2 上下文管理

with 获取连接：
	执行SQL（执行完毕后，自动将连接交还给连接池）

# db_context.py
import threading
import pymysql
from dbutils.pooled_db import PooledDB

POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


class Connect(object):
    def __init__(self):
        self.conn = conn = POOL.connection()
        self.cursor = conn.cursor(pymysql.cursors.DictCursor)

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb): # 退出时，释放连接
        self.cursor.close()
        self.conn.close()

    def exec(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        self.conn.commit()

    def fetch_one(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchone()
        return result

    def fetch_all(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchall()
        return result

from db_context import Connect

with Connect() as obj:
    # print(obj.conn)
    # print(obj.cursor)
    ret = obj.fetch_one("select * from d1")
    print(ret)

    ret = obj.fetch_one("select * from d1 where id=%(id)s", id=3)
    print(ret)

实践案例

学生管理系统

1. 创建数据

根据上图创建 数据库 & 表结构 并 录入数据（可以自行创造数据）

create database day27db default charset utf8 collate utf8_general_ci;
use day27db;

drop database day27db;
drop database IF EXISTS day27db;

利用sql文件导入数据库命令：

• 导入：mysql -u root -p <数据库名> < ~/db.sql
• 导出：
  • 表单结构+数据：mysqldump -u root -p <数据库名> > ~/db.sql
  • 表单结构：mysqldump -u root -p -d <数据库名> > ~/db.sql

# db.sql
create table class(
	cid int not null auto_increment primary key,
    caption varchar(16) not null
)default charset=utf8;

INSERT INTO class VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');


create table student(
	 sid int not null auto_increment primary key,
    gender char(1) not null,
    class_id int not null,
    sname varchar(16) not null,
    constraint fk_student_class foreign key (class_id) references class(cid)
)default charset=utf8;

INSERT INTO student VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');


create table teacher(
	 tid int not null auto_increment primary key,
    tname varchar(16) not null
)default charset=utf8;

INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');


create table course(
	   cid int not null auto_increment primary key,
    cname varchar(16) not null,
    teacher_id int not null,
    constraint fk_course_teacher foreign key (teacher_id) references teacher(tid)
)default charset=utf8;

INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');


CREATE TABLE `score` (
  `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `student_id` int NOT NULL,
  `course_id` int NOT NULL,
  `num` int NOT NULL,
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) DEFAULT CHARSET=utf8;


INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');

创建用户 luffy 并赋予此数据库的所有权限

create user 'luffy'@'%' identified by 'root123';
grant all privileges on day27db.* TO 'luffy'@'%';
flush privileges;

2. 查询练习

1. 查询姓“李”的老师的个数。

   select * from teacher where tname like "李%";
   

2. 查询姓“张”的学生名单。

   select * from student where sname like "张%";
   

3. 查询男生、女生的人数。

   select gender,count(1) from student group by gender;
   

4. 查询同名同姓学生名单，并统计同名人数。

   select sname,count(1) from student group by sname;
   select sname,count(1) from student group by sname having count(1) > 1;
   

5. 查询 “三年二班” 的所有学生。

   select * from student left join class on student.class_id = class.cid where class.caption="三年二班";
   

6. 查询 每个 班级的 班级名称、班级人数。

   select class_id,count(1) from student group by class_id;
   
   select class.caption,count(1) from student left join class on student.class_id = class.cid group by class.caption;
   

7. 查询成绩小于60分的同学的学号、姓名、成绩、课程名称。

   select * from score where num <60;
   
   select 
   	student.sid,
   	student.sname,
   	score.num,
   	course.cname 
   from 
   	score 
   	left join student on score.student_id=student.sid 
   	left join course on score.course_id =course.cid 
   where num <60;
   

8. 查询选修了 “生物课” 的所有学生ID、学生姓名、成绩。

   select * from score left join course on score.course_id =course.cid where course.cname="生物";
   
   
   select 
   	student.sid,
   	student.sname,
   	score.num 
   from 
   	score 
   	left join course on score.course_id =course.cid 
   	left join student on score.student_id=student.sid 
   where course.cname="生物";
   

9. 查询选修了 “生物课” 且分数低于60的的所有学生ID、学生姓名、成绩。

   select student.sid,student.sname,score.num from score left join course on score.course_id =course.cid left join student on score.student_id=student.sid where course.cname="生物" and score.num < 60;
   

10. 查询所有同学的学号、姓名、选课数、总成绩。

    select student_id,count(1),sum(num) from score group by student_id;
    
    select student_id,student.sname,count(1),sum(num) from score left join student on score.student_id=student.sid group by student_id;
    

11. 查询各科被选修的学生数。

    select course_id,count(1) from score group by course_id;
    
    select course_id,course.cname,count(1) from score left join course on score.course_id =course.cid group by course_id;
    

12. 查询各科成绩的总分、最高分、最低分，显示：课程ID、课程名称、总分、最高分、最低分。

    select course_id,course.cname,sum(num), max(num), min(num) from score left join course on score.course_id =course.cid group by course_id;
    

13. 查询各科成绩的平均分，显示：课程ID、课程名称、平均分。

    select course_id,course.cname,avg(num) from score left join course on score.course_id =course.cid group by course_id;
    

14. 查询各科成绩的平均分，显示：课程ID、课程名称、平均分（按平均分从大到小排序）。

    select course_id,course.cname,avg(num) from score left join course on score.course_id =course.cid group by course_id order by avg(num) desc;
    
    
    select course_id,course.cname,avg(num) as A from score left join course on score.course_id =course.cid group by course_id order by A desc;
    

15. 查询各科成绩的平均分和及格率，显示：课程ID、课程名称、平均分、及格率。

    10/20 = 及格率
    
    select course_id,count(1) from score group by course_id;
    

    select 
    	sid,
    	course_id,
    	num,
    	case when score.num >= 60 then 1 else 0 end "是否及格" 
    from score;
    

    select sid,course_id,num,case when score.num > 60 then 1 else 0 end "是否及格" from score;
    

    select 
    	course_id,
    	course.cname,
    	avg(num),
    	count(1) as total,
    	sum(case when score.num > 60 then 1 else 0 end) 
    from 
    	score 
    	left join course on score.course_id =course.cid 
    group by 
    	course_id;
    

    select 
    	course_id,
    	course.cname,
    	avg(num),
    	sum(case when score.num > 60 then 1 else 0 end)/count(1) *100 as percent 
    from 
    	score 
    	left join course on score.course_id =course.cid 
    group by 
    	course_id;
    

16. 查询平均成绩大于60的所有学生的学号、平均成绩；

    select student_id,avg(num) from score group by student_id having avg(num) > 60;
    

17. 查询平均成绩大于85的所有学生的学号、平均成绩、姓名。

    select student_id,avg(num) from score group by student_id having avg(num) > 85;
    
    select student_id,avg(num),student.sname from score left join student on score.student_id=student.sid   group by student_id having avg(num) > 85;
    

18. 查询 “三年二班” 每个学生的 学号、姓名、总成绩、平均成绩。

    SELECT
    	* 
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid
    	LEFT JOIN class ON class.cid = student.class_id;
    

    SELECT
    	* 
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid
    	LEFT JOIN class ON class.cid = student.class_id 
    WHERE
    	class.caption = "三年二班";
    

    SELECT
    	student_id,
    	sname,
    	sum( num ),
    	avg( num ) 
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid
    	LEFT JOIN class ON class.cid = student.class_id 
    WHERE
    	class.caption = "三年二班" 
    GROUP BY
    	student_id
    

19. 查询各个班级的班级名称、总成绩、平均成绩、及格率（按平均成绩从大到小排序）。

    SELECT
    	class.cid,
    	class.caption,
    	sum( num ),
    	avg( num ) as av,
    	sum( CASE WHEN score.num > 60 THEN 1 ELSE 0 END ) / count( 1 ) * 100 as JG
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid
    	LEFT JOIN class ON class.cid = student.class_id 
    GROUP BY
    	class.cid
    ORDER BY
    	av desc
    	
    

20. 查询学过 “波多” 老师课的同学的学号、姓名。

    SELECT
    	student.sid,
    	student.sname
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid
    	LEFT JOIN course ON score.course_id = course.cid
    	LEFT JOIN teacher ON course.teacher_id = teacher.tid
    WHERE
    	teacher.tname = "波多" 
    

21. 查询没学过 “波多” 老师课的同学的学号、姓名。

    SELECT
    	student.sid,
    	student.sname
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid
    	LEFT JOIN course ON score.course_id = course.cid
    	LEFT JOIN teacher ON course.teacher_id = teacher.tid
    WHERE
    	teacher.tname != "波多" 
    

    select * from student where sid not in(
        SELECT
            student.sid
        FROM
            score
            LEFT JOIN student ON score.student_id = student.sid
            LEFT JOIN course ON score.course_id = course.cid
            LEFT JOIN teacher ON course.teacher_id = teacher.tid
        WHERE
            teacher.tname = "波多" 
    )
    

22. 查询选修 “苍空” 老师所授课程的学生中，成绩最高的学生姓名及其成绩（不考虑并列）。

    SELECT
    	student.sid,
    	student.sname 
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid
    	LEFT JOIN course ON score.course_id = course.cid
    	LEFT JOIN teacher ON course.teacher_id = teacher.tid 
    WHERE
    	teacher.tname = "苍空" 
    ORDER BY
    	score.num DESC 
    	LIMIT 1
    

23. 查询选修 “苍空” 老师所授课程的学生中，成绩最高的学生姓名及其成绩（考虑并列）。

    SELECT
    	student.sid,
    	student.sname 
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid
    	LEFT JOIN course ON score.course_id = course.cid
    	LEFT JOIN teacher ON course.teacher_id = teacher.tid 
    WHERE
    	teacher.tname = "苍空" 
    	AND score.num = (
            SELECT
                max( num ) 
            FROM
                score
                LEFT JOIN course ON score.course_id = course.cid
                LEFT JOIN teacher ON course.teacher_id = teacher.tid 
            WHERE
            teacher.tname = "苍空" 
    	)
    

24. 查询只选修了一门课程的全部学生的学号、姓名。

    SELECT
    	student.sid,
    	student.sname 
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid 
    GROUP BY
    	student_id 
    HAVING
    	count( 1 ) =1
    

25. 查询至少选修两门课程的学生学号、学生姓名、选修课程数量。

    SELECT
    	student.sid,
    	student.sname ,
    	count(1)
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid 
    GROUP BY
    	student_id 
    HAVING
    	count( 1 ) >= 2
    

26. 查询两门及以上不及格的同学的学号、学生姓名、选修课程数量。

    SELECT
    	student.sid,
    	student.sname ,
    	count(1)
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid 
    where 
    	num < 60
    GROUP BY
    	student_id 
    HAVING
    	count( 1 ) >= 2
    

27. 查询选修了所有课程的学生的学号、姓名。

    SELECT
    	student.sid,
    	student.sname
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid 
    GROUP BY
    	student_id 
    HAVING
    	count( 1 ) = ( SELECT count( 1 ) FROM course )
    

28. 查询未选修所有课程的学生的学号、姓名。

    SELECT
    	student.sid,
    	student.sname
    FROM
    	score
    	LEFT JOIN student ON score.student_id = student.sid 
    GROUP BY
    	student_id 
    HAVING
    	count( 1 ) != ( SELECT count( 1 ) FROM course )
    

29. 查询所有学生都选修了的课程的课程号、课程名。

    SELECT
    	course.cid,
    	course.cname
    FROM
    	score
    	LEFT JOIN course ON score.course_id = course.cid
    GROUP BY
    	course_id 
    HAVING
    	count( 1 ) = ( SELECT count( 1 ) FROM student )
    

30. 查询选修 “生物” 和 “物理” 课程的所有学生学号、姓名。

    SELECT
    	student.sid,
    	student.sname
    FROM
    	score
    	LEFT JOIN course ON score.course_id = course.cid
    	LEFT JOIN student ON score.student_id = student.sid
    WHERE
    	course.cname in ("生物","物理")
    GROUP BY
    	student_id
    having 
    	count(1) = 2;
    

31. 查询至少有一门课与学号为“1”的学生所选的课程相同的其他学生学号 和 姓名 。

    SELECT
    	student.sid,
    	student.sname
    FROM
    	score
    	LEFT JOIN course ON score.course_id = course.cid
    	LEFT JOIN student ON score.student_id = student.sid
    WHERE
    	score.course_id in ( select course_id from score where student_id=1)
    	and score.student_id != 1
    GROUP BY
    	student_id
    HAVING
    	count(1) > 1
    

32. 查询与学号为 “2” 的同学选修的课程完全相同的其他 学生学号 和 姓名 。

    SELECT
    	student.sid,
    	student.sname
    FROM
    	score
    	LEFT JOIN course ON score.course_id = course.cid
    	LEFT JOIN student ON score.student_id = student.sid
    WHERE
    	score.course_id in ( select course_id from score where student_id=2)
    	and score.student_id in (
        	select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2
        )
    GROUP BY
    	student_id
    HAVING
    	count(1) = ( select count(1) from score where student_id=2 )
    	
    	
    # 如果id=2学生他的课程数量和其他人的课程数量是一样。
    select student_id from score where student_id!=2 group by student having count(1) = select count(1) from score where student_id=2
    
    select 
    	student_id 
    from 
    	score 
    where 
    	student_id!=2 
    group by 
    	student_id 
    having 
    	count(1) = select count(1) from score where student_id=2
    

33. 查询“生物”课程比“物理”课程成绩高的所有学生的学号；

    SELECT
    	* 
    FROM
    	score
    	LEFT JOIN course ON score.course_id = course.cid
    where 
    	cname in ("生物","物理");
    

    SELECT
    	*,
    	case cname WHEN "生物" then num else -1 end sw,
    	case cname WHEN "物理" then num else -1 end wl
    FROM
    	score
    	LEFT JOIN course ON score.course_id = course.cid
    where 
    	cname in ("生物","物理");
    

    SELECT
    	student_id,
    	max(case cname WHEN "生物" then num else -1 end) as sw,
    	max(case cname WHEN "物理" then num else -1 end) as wl
    FROM
    	score
    	LEFT JOIN course ON score.course_id = course.cid
    where 
    	cname in ("生物","物理")
    GROUP BY
    	student_id;
    

    SELECT
    	student_id,
    	max( CASE cname WHEN "生物" THEN num ELSE 0 END ) AS sw,
    	max( CASE cname WHEN "物理" THEN num ELSE 0 END ) AS wl 
    FROM
    	score
    	LEFT JOIN course ON score.course_id = course.cid 
    WHERE
    	cname IN ( "生物", "物理" ) 
    GROUP BY
    	student_id 
    HAVING
    	sw > wl;
    

34. 查询每门课程成绩最好的前3名 (不考虑成绩并列情况) 。

    SELECT
    	cid,
    	cname,
    	( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 0) as "第1名",
    	( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 1) as "第2名",
    	( select student.sname from score left join student on student.sid = score.student_id where course_id = course.cid order by num desc limit 1 offset 2) as "第3名"
    FROM
    	course;
    

35. 查询每门课程成绩最好的前3名 (考虑成绩并列情况) 。

    SELECT
    	cid,
    	cname,
    	( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 0) as "最高分",
    	( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 1) as "第二高分",
    	( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as "第三高分"
    FROM
    	course;
    

    select 
    	* 
    from 
    	score 
    	
    	left join (
    		SELECT
    			cid,
    			cname,
    			( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 0) as "最高分",
    			( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 1) as "第二高分",
    			( select num from score  where course_id = course.cid GROUP BY num order by num desc limit 1 offset 2) as third
    		FROM
    			course ) as C on score.course_id = C.cid 
    WHERE
    	score.num >= C.third
    

36. 创建一个表 sc，然后将 score 表中所有数据插入到 sc 表中。

    CREATE TABLE `sc` (
      `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `student_id` int NOT NULL,
      `course_id` int NOT NULL,
      `num` int NOT NULL,
      CONSTRAINT `fk_sc_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
      CONSTRAINT `fk_sc_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
    ) DEFAULT CHARSET=utf8;
    

    INSERT INTO sc SELECT * from score;
    

37. 向 sc 表中插入一些记录，这些记录要求符合以下条件：

    • 学生ID为：没上过课程ID为 “2” 课程的学生的 学号；
    • 课程ID为：2
    • 成绩为：80

    -- 上过
    select student_id from score where course_id =2;
    
    -- 没上过
    SELECT
    	sid
    FROM
    	student 
    WHERE
    	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )
    
    
    -- 构造数据
    SELECT
    	sid,
    	2,
    	80
    FROM
    	student 
    WHERE
    	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 );
    

    INSERT INTO sc ( student_id, course_id, num ) SELECT
    sid,
    2,
    80 
    FROM
    	student 
    WHERE
    	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )
    

38. 向 sc 表中插入一些记录，这些记录要求符合以下条件：

    • 学生ID为：没上过课程ID为 “2” 课程的学生的 学号。
    • 课程ID为：2。
    • 成绩为：课程ID为3的最高分。

    SELECT
    sid,
    2,
    (select max(num) from score where course_id=3) as num
    FROM
    	student 
    WHERE
    	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 );
    

    INSERT INTO sc ( student_id, course_id, num ) SELECT
    sid,
    2,
    (select max(num) from score where course_id=3) as num
    FROM
    	student 
    WHERE
    	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )