mysql 基础知识

2022-12-02 22:58 由 slivermirror 发表于 #数据库

学习中的思考

在 mysql 学习和使用中，我遇到了不少的难题，我觉得我应该形成一套逻辑思考体系，可以让我在初识 mysql 的过程中加入理性思考，从一开始就探求原理，了解所学内容的核心和关键点，做到一叶知秋而不是只见树木不见森林。

mysql 几种数据类型的底层是如何存储的，不同的 sql 语句对其有何限制？
学习 sql 语句时，需要思考：这个 sql 语句的底层是如何实现的，对哪些数据类型起作用，有什么限制，如果这个 sql 语句对单字段可以使用，可否在多字段中使用，在多字段使用的过程中，可否做到只对一个字段起作用或者对多个字段同时起作用？
隔离级别是如何实现的？

`MySQL` 基础

一、为什么要学习数据库

二、数据库的相关概念：

DB：数据库(database)，存储数据的“仓库”，它保存了一系列有组织的数据。
DBMS：数据库管理系统(Database Management System)，数据库是通过 DBMS 创建和操作的容器。
SQL：结构化查询语言(Structure Query Language)，专门用来与数据库通信的语言。

SQL的优点：

不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持 SQL 。
简单易学。
强有力，灵活使用。

三、数据库存储结构的特点：

将数据放到表中，表再放到库中。
一个数据库可以有多个表，每个表都有一个名字，用来表示自己。表名具有唯一性。
表具有一些特定，这些特定定义了数据在表中如何存储，类似 java 中“类”的设计。
表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似 java 中的“属性”。
表中的数据都是按行存储的，每一行类似与 java 中的“对象”。

四、初识`MySQL`

`MySQL`产品的介绍

DBMS分为两类：

基于共享文件系统的DBMS(Access)
基于客户机-服务器的DBMS(MySQL、Oracle、SqlServer)

`MySQL`产品的安装

`MySQL`安装

`MySQL`卸载

控制面板卸载
安装路径删除 mysql 文件夹 + 删除 C 盘根目录下 ProgramData下 mysql 文件夹；
清理注册表：

A. HKEY_LOCAL_MAACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL 目录
B. HKEY_LOCAL_MAACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL 目录
C. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL 目录
D. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControl001\Services\MYSQL 目录
E. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControl002\Services\MYSQL 目录
F. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControlSet\Services\MYSQL 目录

删除 C：\Document and Settings\All Users\Application Data\MySQL 目录（隐藏的目录）

`MySQL`服务的启动和停止

方式一：计算机 -> 管理 -> 服务
方式二：命令行(管理员)

net start 服务名(启动服务)
net stop 服务名(停止服务)

`MySQL`服务端的登录和退出

方式一：通过 mysql 自带的客户端；只限于 root 用户
方式二：通过 windows 自带的客户端

登录：mysql [-h 主机名 -P 端口号] -u 用户名 -p密码

退出：exit 或者 ctrl + c

`MySQL`常见的命令

查看当前所有数据库 show databases;
打开指定的库 use 库名;
查看当前库的所有表 show tables;
查看其它库的所有表 show tables from 库名;
查看目前位于哪个库 select database();
创建表

create table 表名(
    列名     列类型，
    列名     列类型，
    ......
);

查看表结构 desc 表名;
查看服务器的版本

方式一：登录到mysql服务器 select version();
方式二：没有登录到mysql服务器 mysql --version

`MySQL`语法规范

不区分大小写，但建议关键字大写，表名、列名小写。
每条命名最好用分号或 \G、\g 结尾（会格式化输出数据）
每条命令根据需要，可以进行缩进或换行。
注释

单行注释：# 注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */

试说出查询语句中涉及到的所有关键字，以及执行先后顺序

SELECT 查询列表             7
FROM 表1 别名                第1步
连接类型 join 表2             2
ON 连接条件                 3
WHERE 筛选条件                 4
GROUP BY 分组列表             5
HAVING 分组后的筛选            6
ORDER BY 排序列表             8
LIMIT 偏移，条目数;            9

五、`DQL`(Data Query Language)语言的学习

基础查询

语法：

SELECT 查询列表 FROM 表名;

特点：

1.查询列表可以是：表中的字段、常量值、表达式、函数；
2.查询的结果是一个虚拟的表格；查询表中的某个字段

查询表中的所有字段

> 方式一：
    SELECT `employee_id`,
        `first_name`,
    FROM
        employees;
> 方式二：
    SELECT * FROM employees;

查询常量值

SELECT 100;
SELECT 'john';    //字符和日期型的常量值必须用单引号，数值型不需要；

查询表达式

SELECT 100*99;

查询函数

SELECT VERSION();
SELECT 函数名(实参列表);

起别名

便于理解；
如果要查询的字段有重名的情况，使用别名可以区分开来；
如果别名中含有关键词，用 单引号 或者 双引号 将别名括起来；

方式一：使用 AS
    SELECT 100%98 AS 结果;
    SELECT last_name AS 姓, first_name AS 名 FROM employees;

方式二：使用空格
    SELECT last_name 姓,first_name 名 FROM employees;

去重 distinct
DISTINCT 需要放到所有列名的前面。
DISTINCT 其实对后面所有列名的组合进行去重。

SELECT DISTINCT 字段名 FROM 表名;

案例：查询员工表中涉及到的所有部门编号
    SELECT DISTINCT department_id FROM employees;

concat 函数：和null拼接结果为null

案例：查询员工名和姓连接成一个字段，并显示为 姓名
  SELECT 
      CONCAT(last_name,first_name) AS 姓名
  FROM
      employees;

案例：显示出表employee的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
  SELECT 
      CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct，0)) AS out_put
  FROM
      employees;

ifnull 函数

功能：判断某字段或表达式是否为null，如果为null，返回指定值，否则返回原本的值
SELECT ifnull(commissiop_pct,0) from employees;

空值参与运算

所有运算符或列值遇到 null 值，运算的结果都为 null。
在 MySQL 里面，空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。

运算符

算数运算符

加法与减法运算符

mysql 中的加号，只有一个功能：运算符，做加法运算。

SELECT 100+90; 两个操作数都为数值型，则做加法运算

SELECT '123'+90;只要其中一方为字符型，试图将字符型数值转成数值型，
                如果转换成功，则继续做加法运算。
                如果转换失败，则将字符型数值转换成0

SELECT null+10;    只要其中一方为null，则结果肯定为null

乘法与除法运算符

一个数乘以整数1和除以整数1后仍得原数；
个数乘以浮点数1 和除以浮点数1后变成浮点数，数值与原数相等；
一个数除以整数后，不管是否能除尽，结果都为一个 浮点数；
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

求模（求余）运算符

结果的符号与被模数的符号一致。

比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回 NULL。

等号运算符

如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他返回结果与等于运算符相同。

不等于运算符

LEAST(值1，值2，...，值n)：在有两个或多个参数的情况下，返回最小值。当比较值列表中有NULL时，不能判断大小，返回值为NULL。
GREASTEST：最大值运算符。

逻辑运算符

逻辑非运算符逻辑非（NOT或!）运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。
逻辑与运算符逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。
逻辑或运算符逻辑或（OR或||）运算符是当给定的值都不为NULL，并且有任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。
逻辑异或运算符逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。
AND 的优先级高于OR。

位运算符

条件查询

语法：

SELECT 
    查询列表
FROM
    表名
WHERE
    筛选条件;

分类：
- 按条件表达式筛选
  
  条件运算符： > < = != <> >= <= <=>
- 按逻辑表达式筛选
  
  逻辑运算符: && || !
  
  and or not
  
  && 和 and：两个条件都为 true，结果为 true，反之为 false；
  
  || 和 or：只要有一个条件为 true，结果为 true；
- 模糊查询
  
  like
  between and
  in
  is null / is not null

按条件表达式筛选

#案例1：查询工资>12000的员工信息
    SELECT
        *
    FROM
        employees
    WHERE
        salary>120000;

#案例2：查询部分编号不等于90的员工名和部门编号
    SELECT
        last_name,
        department_id
    FROM
        employees;
    WHERE
        department_id<>90;

按逻辑表达式筛选

#案例一：查询工资在10000到20000之间的员工名、工资以及奖金
    SELECT
        last_name,
        salary,
        commission_pct
    FROM
        employees
    WHERE
        salary >= 10000 
    AND
        salary <= 20000

模糊查询

一般和通配符搭配使用，可以判断 字符型 或 数值型；

通配符：%s任意多个字符，_任意单个字符

#案例1：查询员工名中包含符a的员工信息
    SELECT
        *
    FROM
        employees
    WHERE
        last_name LIKE '%a%';

#案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
    SELECT
        last_name,
        salary,
    FROM
        employees
    WHERE
        last_name LIKE '__e_a%';

#案例3：查询员工名中第二个字符为_的员工名
    SELECT 
        last_name
    FROM 
        employees
    WHERE
        last_name LIKE '_\_%';    或者        last_name LIKE '_$_%' ESCAPE '$';

between and

使用 between and 可以提高语句的简介度；
包含临界值；
两个临街值不要调换顺序；

案例1：查询员工编号在100到120之间的员工信息
    SELECT
        *
    FROM
        employees
    WHERE
        employee_id BETWEEN 100 AND 120;

in

含义：判断某字段的值是否属于 in 列表中的某一项；

使用 in 提高语句简介度；
in 列表的值类型必须一致或兼容；
不支持通配符；

案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号。
    SELECT
        last_name,
        job_id
    FROM
        employees
    WHERE    
        job_id IN ('IT_PROT','AD_VP','AD_PRES');

is null

判断某字段或表达式是否为 null，如果是，则返回1，否则返回0。

= 或 <> 不能用于判断 null 值；
is null 或 is not null 可以判断null值；

案例1：查询没有奖金的员工名和奖金率
    SELECT
        last_name,
        commission_pct
    FROM
        employees
    WHERE
        commission_pct IS NULL;        IS NOT NULL

安全等于 <=>

案例1：查询没有奖金的员工名和奖金率
    SELECT
        last_name,
        commision_pct
    FROM
        employees
    WHERE
        commission_pct <=> NULL;

案例2：查询工资为12000的员工名和奖金率
    SELECT
        last_name,
        commision_pct
    FROM
        employees
    WHERE
        salary <=> 12000;

is null pk <=>
- IS NULL：仅仅可以判断NULL值，可读性较高，建议使用；
- <=>：既可以判断 NULL 值，又可以判断普通的数值，可读性较低。
经典面试题：

试问，select * from employees;和select * from employees where commission_pct like '%%' and last_name like '%%';结果是否一样？并说明原因。

答：不一样。如果判断的字段有null值。

排序查询

语法：

SELECT     查询列表
FROM     表
[WHERE     筛选条件]
order by 排序列表 [asc/desc]

特点：

asc 代表的是升序，desc 代表的是降序；如果不写，默认是升序。
order by 子句中可以支持单个字段、多个字段、表达式、函数、别名。
order by 后面的列必须是在 select 后面存在的。
order by 子句一般是放在查询语句的最后面，limit 子句除外。
可以使用列的别名进行排序，但别名只能在 order by 中使用，不能在 WHERE 中使用。

#案例1：查询愚弄信息，要求工资从高到低排序
 SELECT * FROM employees 
ORDER BY salary DESC;

#案例2：查询部门编号>=90的员工信息，按入职时间的先后顺序进行排序
 SELECT *
 FROM employees
 WHERE department_id>=90
 ORDER BY hiredate ASC;

#案例3：按年薪的高低显示员工的信息和年薪[按表达式排序]
 SELECT *,salary*12*(1+IFNULL(commission_prt,0)) 年薪
 FROM employees
 ORDER BY salary*12*(1+IFNULL(commission_prt,0)) DESC; 或 ORDER BY 年薪 DESC;

#案例5：按姓名的长度显示员工的姓名和工资[按函数排序]
 SELECT LENGTH(last_name) 字节长度,last_name,salary
 FROM employees
 ORDER BY LENGTH(last_name) DESC;

#案例6：查询员工信息，要求先按工资升序，再按员工编号降序[按多个字段排序]
 SELECT *
 FROM employees
 ORDER BY salary ASC,employee_id DESC;

常见函数

select 函数名(实参列表) [from 表]
(1) 单行函数
    concat、length、ifnull等
(2) 分组函数
    做统计使用，又称为统计函数、聚合函数、组函数

单行函数

操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以嵌套
参数可以是一列或一个值

字符函数

注意：MySQL中，字符串的位置是从1开始的。

函数	作用
ASCII(S)	返回字符串 S 中的第一个字符的 ASCII 码值
CHAR_LENGTH(s)	返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s)	返回字符串s的个数，和字符集有关
CONCAT(s1,s2,...,sn)	连接s1,s2,......sn 为一个字符串
CONACT_WS(x,s1,s2,......,sn)	同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x
INSERT(str,idx,len,replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s)	将字符串s的所有字母都转换成大写字母
LOWER(s) 或 LCASE(s)	将字符串s的所有字母都转成小写字母
LEFT(str,n)	返回字符串str最左边的n个字符
RIGHT(str,n)	返回字符串str最右边的n个字符
LPAD(str,len,pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str,len,pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)	去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str,n)	返回str重复n次的结果
SPACE(n)	返回n个空格
STRCMP(s1,s2)	比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)	返回从字符串s的index位置其len个字符，作用与SUBSTING(s,n,len)相同
LOCATE(substr,str)	返回字符串 substr 在字符串 str 中首次出现的位置，作用与 POSITION(substr IN str)、INSTR(str,substr) 相同。未找到，返回0
ELT(m,s1,s2,......,sn)	返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,...,sn)	返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)	返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s)	返回s反转后的字符串
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value

length()：获取参数值的字节个数

SELECT LENGTH('张三丰hahaha');

SHOW VARIVALE LIKE '%char%'; 查看字符集

concat()：拼接字符串

SELECTV CONCAT(last_name,'_',first_name) 姓名 FROM employees;

upper()、lower()

substr()、substring()：

注意：索引从1开始

# 截取从指定索引处后面所有字符
    SELECT SUBSTR('李莫愁爱上了路站元',7) out_put;

# 截取从指定所引处指定字符长度的字符
    SELECT SUBSTR('李莫愁爱上了路站元',1,3) out_put;

#案例：姓名中首字符大写，其他字符小写，然后用 _ 拼接，显示出来
SELECT CONTACT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name),2)) out_put;

instr()：返回字串第一次出现的索引，如果找不到返回0

trim()：

SELECT TRIM('a' FROM 'aaaaa张aa翠山aaaaaa') AS out_put;

lpad()：用指定的字符实现左填充指定长度

SELECT LPAD('殷素素',10,'*') AS out_put;     //10为总字符数
SELECT LPAD('殷素素',2,'*') AS out_put;     //殷素

rpad()：用指定的字符实现右填充指定长度
```
SELECT RPAD('殷素素',12,'ab') AS out_put;
```
replace()：替换

数学函数

round()：四舍五入

SELECT ROUND(1.65);
SELECT ROUND(1.567,2);         // 1.57

ceil()：向上取整，返回 >= 该参数的最小整数
floor()：向下取整，返回 <= 该参数的最大整数
truncate()：截断
```
SELECT TRUNCATE(1.6999,1);    //1.6
```
mod()：取余
```
mod(a,b) = a-a/b*b
```
rand()：获取随机数，返回 0-1 之间的小数

日期函数

now()：返回当前系统日期+时间
```
SELECT NOW();
```
curdate()：返回当前系统日期，不包含时间

curtime()：返回当前时间，不包含日期
可以获取指定的部分，年、月、日、小时、分钟、秒
```
SELECT YEAR(now());
SELECT YEAR('1998-1-1') 年;
```
str_to_date：将日期格式的字符转换成指定格式的日期
```
STR_TO_DATE('9-13-1999','%m-%d-%Y')        1999-09-13
```

date_format：将日期格式转换成字符

DATE_FORMAT('2018/6/6','%Y年%m月%d日')    2018年06月06日

year()、month()、day()、hour()、minute()、second()
datediff()：返回两个日期相差的天数
monthname()：以英文形式返回月

其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();
SELECT password('字符')：返回该字符的密码形式 自动加密
MD5('字符')：返回该字符的md5加密形式

流程控制函数

if 函数：if else 的效果

if(条件表达式,表达式1,表达式2)：如果条件表达式成立，返回表达式1;否则返回表达式2

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;

case函数的使用一：switch case 的效果

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

#案例：要查询员工的工资，要求    
部门号=30，显示的工资为1.3倍
部门号=40，显示的工资为1.4倍
部门号=50，显示的工资为1.5 倍
其他部门，显示的工资为原工资

SELECT salary 原始工资,department_id,
    CASE department_id
    WHEN 30 THEN salary*1.3
    WHEN 40 THEN salary*1.4
    WHEN 50 THEN salary*1.5
    ELSE salary
    END AS 新工资
    FROM employees;

case 函数的使用二：类似于多重if

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

#案例：查询员工的工资的情况    如果工资 > 20000，显示A级别
                        如果工资 > 15000，显示B级别
                        如果工资 > 10000，显示C级别
                        否则，显示D级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

加密与解密函数

函数	用法
PASSWORD(str)	返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用语用户的密码加密。在mysql8.0中被弃用。
MD5(str)	返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL。不可逆。
SHA(str)	从原明文密码str计算并返回加密后的密码字符串，当参数为 NULL 时，返回 NULL。不可逆。
ENCODE(value,password_seed)	返回使用 password_seed 作为加密密码加密value。mysql8.0后被弃用。
DECODE(value,password_seed)	返回使用password_seed作为加密密码解密value

mysql 信息函数

函数	用法
VERSION()	返回当前MySQL的版本号
CONNECTION_ID()	返回当前MySQL服务器的连接id
DATABASE()，SCHEMA()	返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()	返回当前连接MySQL的用户名，返回结果格式为 “主机名@用户名”
CHARSET(value)	返回字符串value自变量的字符集
COLLATION(value)	返回字符串value的比较规则

其他函数

函数	用法
FORMAT(value,n)	返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位
CONV(value,from,to)	将value的值进行不同进制之间的转换
INET_ATON(ipvalue)	将以点分隔的IP地址转化为一个数字
INET_NTOA(value)	将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr)	将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code)	将value所使用的字符编码修改为char_code

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数。输入的是一组数据的集合，输出的是单个值。
分类：sum 求和、 avg 平均值、 max 最大值、 min 最小值、 count 计算个数；
特点：
sum、avg一般用于处理数值型；max、min、count 可以处理任何类型；
以上分组函数都忽略 null 值；
可以和 distinct 搭配实现去重的运算；

```sql
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
```

count函数的详细介绍，一般使用 COUNT(*) 统计行数；

```sql
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;    //只要某一行有一个不为null，就相当于+1(不包含NULL值)
SELECT COUNT(1) FROM employees; //统计1的个数，相当于计算行数

效率：
    如果使用的是 MyISAM 存储引擎，则三者效率相同，都是O(1)
    如果使用的是 InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1) > COUNT(字段)
```

和分组函数一同查询的字段要求是 group by 后面的字段；

```
SELECT AVG(salary),employee_id FROM employees; 有问题，AVG(salary)只有一行，employee_id有很多行
```

分组查询

语法

                                                 #执行顺序
SELECT         column，group_function(column)           5
FROM         table                                    1
[WHERE         condition]                                2
GROUP BY     group_by_expression                     3
[HAVING     分组后的筛选]                                4
[ORDER BY     column];                                 6

明确：WHERE 一定放在 FROM 后面；
注意：查询列表必须特殊，要求是分组函数和 group by 后出现的字段；
当使用 ROLLUP 时，不能同时使用 ORDER BY 子句进行结果排序，即 ROLLUP 和 ORDER BY 是互相排斥的。

为了更好的理解 grouy by 多个列和聚合函数的应用，我们可以假设在group by执行之后，生成了一个虚拟的中间表。相同的group by字段合并成一行，其余的字段分别写到一个单元格里。
对于id和number里面的单元格有多个数据的情况，使用聚合函数。聚合函数就是用来输入多个数据，输出一个数据的。
#对name字段进行分组
id    name    number
1    aa        2
2            3

3            4
4    bb        5
8            6

特点：
分组查询中的筛选条件分为两类：

```
            数据源            位置                    关键字
分组前筛选    原始表            group by子句的前面        where
分组后筛选    分组后的结果集    group by子句的后面         having
```

group by 子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开，没有顺序要求)，表达式或函数(用的较少)；
也可以添加排序(排序放在整个分组查询的最后)；
从 mysql8.0 开始，group by不再支持隐式排序。
当 group by 这列有 null 值时，group 会把他们当成是同一个直接聚合。
如果过滤条件中使用了聚合函数，则必须使用 HAVING 来替换 WHERE。否则，报错。
案例：

#案例1：查询每个工种的最高工资
    SELECT MAX(salary),job_id
    FROM employees
    GROUP BY job_id;

#案例2：查询每个位置上的部门个数
    SELECT COUNT(*),location_id
    FROM employees
    GROUP BY location_id;

#案例3：查询邮箱中包含a字符的，每个部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    WHERE email LIKE '%a%'
    GROUP BY department_id;

#案例4：查询有奖金的每个领导手下员工的最高工资
    SELECT MAX(salary),manager_id
    FROM employees
    WHERE commission_pct IS NOT NULL
    GROUP BY manager_id;

#案例5：查询哪个部门的员工个数>2
(1) 查询每个部门员工个数
    SELECT COUNT(*),department_id
    FROM employees
    GROUP BY department_id;
(2) 根据(1)的结果进行筛选，查询哪个部门的员工个数>2
    SELECT COUNT(*),department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*)>2;

#案例：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
(1)查询每个工种有奖金的员工的最高工资
    SELECT MAX(salary),job_id
    FROM employees
    WHERE commission_pct IS NOT NULL
    GROUP BY job_id;

(2)根据(1)结果继续筛选，最高工资>12000
    SELECT MAX(salary),job_id
    FROM employees
    WHERE commission_pct IS NOT NULL
    GROUP BY job_id
    HAVING MAX(salary)>12000;

#案例：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资；
(1)查询每个领导手下的员工最低工资
    SELECT MIN(salary),manager_id
    FROM employees
    GROUP BY manager_id;
(2)添加筛选条件：编号>102
    SELECT MIN(salary),manager_id
    FROM employees
    WHERE manager_id > 102
    GROUP BY manager_id;
(3)添加筛选条件：最低工资>5000
    SELECT MIN(salary),manager_id
    FROM employees
    WHERE manager_id > 102
    GROUP BY manager_id
    HAVING MIN(salary)>5000;

     > 按表达式或函数分组
             > 案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的的有哪些
                 (1)查询每个长度的员工个数
                     SELECT COUNT(*),LENGTH(last_name)
                     FROM employees
                     GROUP BY LENGTH(last_name);
                 (2)添加筛选条件
                     SELECT COUNT(*),LENGTH(last_name)
                     FROM employees
                     GROUP BY LENGTH(last_name)
                     HAVING COUNT(*)>5;
     > 按多个字段分组
         #案例：查询每个部门每个工种的员工的平均工资
             SELECT AVG(salary),department_id,job_id
             FROM employees
             GROUP BY job_id,department_id;

         #添加排序
        #案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
            SELECT AVG(salary),department_id,job_id
             FROM employees
             WHERE department_id IS NOT NULL
             GROUP BY job_id,department_id
             ORDER BY AVG(salary) DESC;

连接查询

含义：又称多表查询，当查询的字段来自多个表时；
笛卡尔乘积现象：表1有 m 行，表2有 n 行，结果 m*n 行

发生原因：没有有效的连接条件；

如何避免：添加有效的连接条件；

分类：

按年代分类：

sql92标准：仅仅支持内连接
    等值
    非等值
    自连接
    也支持一部分外连接(用于 oracle、sqlserver，mysql 不支持)
     sql99标准(推荐)：支持内连接+外连接(左外和右外)+交叉连接

按功能分类：

内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全外连接(mysql不支持)
交叉连接

sql92 标准

等值连接

多表等值连接的结果为多表的交集部分；
n表的连接，至少需要n-1个连接条件；
多表的顺序没有要求；
一般需要为表起别名；
可以搭配前面介绍的所有子句使用，比如排序、筛选、分组；

语法：

SELECT 查询列表
FROM 表1 别名, 表2 别名
WHERE 表1.key = 表2.key
[AND 筛选条件]
[GROUP BY 分组字段]
[HAVING 分组后的筛选]
[ORDER BY 排序字段]

案例：

#案列1：查询女生名和对应的男生名
 SELECT NAME,boyName 
FROM boys,beauty
 WHERE beauty.boyfriend_id = boys.id;

#案例2：查询员工名和对应的部门名
 SELECT last_name,department_name
 FROM employees,departments
 WHERE employees.`department_id` = departments.`department_id`;

为表起别名
提高语句的简洁度；
区分多个重名的字段；
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定；

#案例：查询员工名、工种号、工种名
    SELECT e.last_name,e.job_id,j.job_title     #employees.last_name错误
    FROM employees AS e,jobs j
    WHERE e.`job_id` = j.`job_id`;

两个表的顺序可以调换
加筛选

#案例：查询有奖金的员工名、部门名
    SELECT last_name,department_name
    FROM employees e,departements d
    WHERE e.`department_id` = d.`department_id`
    AND e.`commission_pct` IS NOT NULL;

#案例2:查询城市名中第二个字符为o的部门名和城市名
    SELECT dapartment_name,city
    FROM departments d,location l
    WHERE d.`location_id` = l.`location_id`
    AND city LIKE `_o%`;

加分组

#案例1：查询每个城市的部门个数
    SELECT COUNT(*) 个数,city
    FROM departments d,locations l
    WHERE d.`location_id` = l.`location_id`
    GROUP BY city;

#案例2：查询有奖金的部门的部门名和部门的领导编号和该部门的最低工资
    SELECT department_name,d.`manager_id`,MIN(salary)
    FROM dapartment d,employees e
    WHERE d.`department_id` = e.`department_id`
    AND commission_pct IS NOT NULL
    GROUP BY department_name,d.mamager_id;

加排序

#案例：查询每个工种的工种名和员工的个数，并且按员工的个数降序
    SELECT job_title,COUNT(*)
    FROM employees e,jobs j
    WHERE e.`job_id` = j.`job_id`
    GROUP BY job_title
    ORDER BY COUNT(*) DESC;

三表连接

#案列：查询员工名、部门名和所在的城市
    SELECT last_name,department_name,city
    FROM employees e,department d,location l
    WHERE e.`department_id` = d.`department_id`
    AND d.`location_id` = l.`location_id`

    AND city LIKE 's%'
    ORDER BY department_name DESC;

非等值连接

SELECT     查询列表
FROM     表1 别名, 表2 别名
WHERE     非等值的连接条件
[AND     筛选条件]
[GROUP BY 分组字段]
[HAVING 分组后的筛选]
[ORDER BY 排序字段]

#案例1：查询员工的工资和工资级别
    SELECT salary,grade_level
    FROM employess e,job_grades g
    WHERE salary BETWEEN g.`lowest_sal` AND g.`hightest_sal`
    //AND g.`grade_level` = 'A';

自连接

#案例：查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;

sql99 语法

SELECT 查询列表
FROM 表1 别名 [连接类型]
JOIN 表2 别名
ON     连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表]

内连接：inner

SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件;

分类：等值、非等值、自连接

等值连接

特点：

添加排序、分组、筛选；
inner 可以省略；
筛选条件放在 where 后面，连接条件放在 on 后面，提高分离性，便于阅读；
inner join 连接和 sql92 语法中的等值连接效果是一样的，都是查询多表；

#案例1：查询员工名、部门名
    SELECT last_name,department_name
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id` = d.`department_id`;

#案例2：查询员工名字中包含e的员工名和工种名(添加筛选)
    SELECT last_name,job_title
    FROM employees e
    INNER JOIN jobs j
    ON e.`job_id` = j.`job_id`
    WHERE e.`last_name` LIKE '%e%';

#案例3：查询部门个数>3的城市名称和部门个数(添加分组+筛选)
#(1)查询每个城市部门的个数
#(2)在(1)结果上筛选满足条件的
    SELECT city,COUNT(*) 部门个数
    FROM departments d
    INNER JOIN locations l
    ON d.`location_id` = l.`location_id`
    GROUP BY city
    HAING COUNT(*)>3;

#案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序(添加排序)
#(1)查询每个部门的员工个数
    SELECT COUNT(*),department_name
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id` = d.`department_id`
    GROUP BY department_name;
#(2)在(1)结果上筛选员工个数>3的记录，并排序
    SELECT COUNT(*),department_name
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id` = d.`department_id`
    GROUP BY department_name
    HAVING COUNT(*)>3
    ORDER BY COUNT(*) DESC;

#案例5：查询员工名、部门名、工种名，并按部门名排序
    SELECT last_name,department_name,job_title
    FROM employees e
    INNER JOIN departments d ON e.`department_id` = d.`department_id`
    INNER JOIN jobs j ON e.`job_id`=j.`job_id`
    ODER BY department_name DESC;

非等值连接

#案例1：查询员工的工资级别
    SELECT salary,grade_level
    FROM employees e
    JOIN job_grade e
    ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#案例2：查询工资级别的个数>20的个数，并且按工资级别降序
    SELECT COUNT(*),grade_level
    FROM employees e
    JOIN job_grade e
    ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
    GROUP BY grade_level
    HAVING COUNT(*)>20
    ORDER BY grade_level DESC;

自连接

#案例：查询姓名中包含字符k的员工的名字、上级的名字
    SELECT e.last_name,m.last_name
    FROM employees e
    JOIN employees m
    ON e.`manager_id` = m.`manager_id`
    WHERE e.`last_name` LIKE '%k%';

外连接

左外: left [outer]
右外: right [outer]
全外: full [outer]

应用场景：用于查询一个表中有，另一个表中没有的记录；

特点：

外连接的查询结果为主表中的所有记录；

如果从表中有和它匹配的，则显示匹配的值；

如果从表中没有和它匹配的，则显示 null；

外连接查询结果=内连接结果 + 主表中有而从表中没有的记录；

左外连接，left join 左边的是主表；
右外连接，right join 右边的是主表；

全外连接，两边都是主表。

左外和右外交换两个表的顺序，可以实现同样的效果；
全外连接=内连接的结果+表1中有但表2没有的+表2中有的但表1没有的；

#案例：查询男朋友 不在男明星表的女明星名
    SELECT b.name,bo.*
    FROM beauty b
    LEFT OUTERF JOIN boys bo
    ON b.`boyfriend_id` = bo.`id`
    WHERE bo.`id` IS NULL;  //问题：为什么用boys.id为NULL就可以了呢，实际上的boys表里的id是主键，根本不可能为NULL啊？

#案例1：查询哪个部门没有员工
#左外
    SELECT d.*,e.employee_id
    FROM departments d
    LEFT OUTER JOIN employees e
    ON d.`department_id`=e.`department_id`
    WHERE e.`employee_id` IS NULL;

#案例2：查询哪个城市没有部门
    SELECT city
    FROM department d
    RIGHT OUTER JOIN locations l
    ON d.location_id = l.location_id
    WHERE d.department_id IS NULL;

#案例3：查询部门为SAL或IT的员工信息
SELECT e.*,d.department_name
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
WHERE d.department_name IN ('SAL','IT');

交叉连接：cross

SELECT 查询列表
FROM 表1 别名
CROSS JOIN 表2 别名;

#特点：类似于笛卡尔乘积

sql99 语法新特性

自然连接

NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

#sql92中：
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

#在sql99中可写成：
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING 连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

子查询

含义：出现在其他语句中的 select 语句，称为子查询或内查询；
外部的查询语句，称为主查询或外查询。
外面的语句可以是 insert、update、delete、select 等，一般 select 作为外面语句较多。

分类：

按子查询出现的位置：

select 后面：
- 仅仅支持标量子查询
from 后面：
- 支持表子查询
where 或 having 后面
- 标量子查询(单行)
- 列子查询 (多行)
- 行子查询
exists 后面(相关子查询)（返回的结果1或0，类似布尔操作）
- 表子查询

按结果集的行列数不同：

标量子查询(单行子查询)(结果集是一个数据：一行一列)
列子查询(多行子查询)(结果集是一列：一列多行)：一般搭配着多行操作符使用 in、any/some、 all
行子查询(结果集是一行：一行多列)
表子查询(结果集一般为多行多列)

where 或 haing 后面

标量子查询
列子查询
行子查询

特点：

子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用 > < >= <= = <>
子查询的执行顺序优于主查询执行，主查询的条件用到了子查询的结果

标量子查询

#案例1：谁的工资比Abel高？
#(1)查询Abel的工资
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
#(2)查询员工信息，满足 salary>(1)结果
    SELECT *
    FROM employees
    WHERE salary>(
        SELECT salary
        FROM employees
        WHERE last_name = 'Abel'
    );

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
#(1)查询141号员工的job_id
    SELECT job_id
    FROM employees
    WHERE employee_id = 141
#(2)查询143号员工的salary
    SELECT salary
    FROM employees
    WHERE employee_id = 143
#(3)查询员工的姓名、job_id和工资，要求job_id=(1)并且salary>(2)
    SELECT last_name,job_id,salary
    FROM employees
    WHERE job_id = (
        SELECT job_id
        FROM employees
        WHERE employee_id = 141
    ) AND salary > (
        SELECT salary
        FROM employees
        WHERE employee_id = 143
    );

#案例3：返回公司工资最少的员工的last_name，job_id和salary
#(1)查询公司的最低工资
    SELECT MIN(salary)
    FROM employees;
#(2)查询last_name，job_id和salary，要求salary=(1)
    SELECT last_name,job_id,salary
    FROM employees
    WHERE salary = (
        SELECT MIN(salary)
        FROM employees;
    );

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#(1)查询50号部门的最低工资
    SELECT MIN(salary)
    FROM employees
    WHERE department_id =50
#(2)查询每个部门的最低工资
    SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
#(3)在(2)的基础上，满足min(salary)>(1)
    SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING MIN(salary)>(
        SELECT MIN(salary)
        FROM employees
        WHERE department_id =50
    );

非法使用标量子查询：

    SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING MIN(salary)>(
        SELECT salary
        FROM employees
        WHERE department_id =50
    );

列子查询(多行子查询)

返回多行
使用多行比较操作符

操作符	含义
IN / NOT IN	等于列表中的任意一个
ANY \| SOME	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

#案例1：返回 location_id 是 1400 或 1700 的部门中所有员工姓名
#(1)查询location_id是1400或1700的部门编号
    SELECT DISTINCT department_id
    FROM departments
    where location_id IN(1400,1700)
#(2)查询员工姓名，要求部门号是(1)列表中的某一个
    SELECT last_name
    FROM employees
    WHERE deapartment_id IN(
        SELECT DISTINCT department_id
        FROM departments
        where location_id IN(1400,1700)
    );

#案例2：查询其它工种中比job_id为`IT_PROG`工种任一工资低的员工的员工号、姓名、job_id 和 salary
#(1)查询job_id为`IT_PROG`部门任一工资
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id='IT_PROG'
#(2)查询员工号、姓名、job_id以及salary，salary<(1)的任意一个
    SELECT last_name,employee_id,job_id,salary
    FROM employees
    WHERE salary < ANY(
        SELECT DISTINCT salary
        FROM employees
        WHERE job_id='IT_PROG'
    )AND job_id<>'IT_PROG';
    或
    SELECT last_name,employee_id,job_id,salary
    FROM employees
    WHERE salary < (
        SELECT MAX(salary)
        FROM employees
        WHERE job_id='IT_PROG'
    )AND job_id<>'IT_PROG';

行子查询(结果集一行多列或多行多列)

#案例：查询员工编号最小且工资最高的员工信息
#(1)查询最小的员工编号
    SELECT MIN(employee_id)
    FROM employees
#(2)查询最高工资
    SELECT MAX(salary)
    FROM employees
#(3)查询员工信息
    SELECT *
    FROM employees
    WHERE employee_id=(
        SELECT MIN(employee_id)
        FROM employees
    )AND salary=(
        SELECT MAX(salary)
        FROM employees
    );

    SELECT *
    FROM employees
    WHERE (employee_id,salary)=(
        SELECT MIN(employee_id),MAX(salary)
        FROM employees
    );

select后面：仅仅支持标量子查询

#案例1：查询每个部门的员工个数
    SELECT d.*,(
        SELECT COUNT(*)
        FROM employees
        WHERE e.department_id = d.`department_id`
        ) 个数
    FROM departments d;

#案例2：查询员工号=102的部门名
    SELECT (
        SELECT department_name
        FROM departments d
        INNER JOIN employees e
        ON d.department_id = e.department_id
        WHERE e.employee_id = 102
    ) 部门名;

from 后面

将子查询结果充当一张表，要求必须起别名；

#案例1：查询每个部门的平均工资的工资等级
#(1)查询每个部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
#(2)连接(1)的结果集和job_grade表，筛选条件平均工资between lowest_sal and highest_sal
    SELECT ag_dep.*,g.`grade_level`
    FROM(
        SELECT AVG(salary) ag,department_id
        FROM employees
        GROUP BY department_id
    ) ag_dep
    INNER JOIN job_grade g
    ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

#案例2：查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
#(1)查询各部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id;
#(2)连接(1)结果集和 employees 表，进行筛选
    SELECT employee_id,last_name,salary,e.department_id
    FROM employees e
    INNER JOIN (
        SELECT AVG(salary) ag,department_id
        FROM employees
        GROUP BY department_id;
    )ag_dep
    ON e.department_id = ag_dep.department_id
    WHERE salary > ag_dep.ag;

exists后面(相关子查询)

语法：
    exists(完整的查询语句)            问题：exists(NULL)？
结果：
    1或0

#案例1：查询有员工的部门名
    SELECT department_name
    FROM departments d
    WHERE EXISTS(
        SELECT *
        FROM employees e
        WHERE e.`department_id`= d.`department_id`
    );
#in
    SELECT department_name
    FROM departments d
    WHERE d.`deparment_id` IN (
        SELECT department_id
        FROM employees
    );


#案例2：查询没有女朋友的男生信息
#in
    SELECT bo.*
    FROM boys bo
    WHERE bo.id NOT IN(
        SELECT boyfriend_id
        FROM beauty
    );
#exists
    SELECT bo.*
    FROM boys bo
    WHERE NOT EXISTS(
        SELECT boyfriend_id
        FROM beauty b
        WHERE bo.id = b.boyfriend_id
    );

子查询经典案例

# 1.查询工资最低的员工信息：last_name，salary
#(1)查询最低的工资
    SELECT MIN(salary)
    FROM employees
#(2)查询last_name，salary，要求salary=(1)
    SELECT last_name，salary
    FROM employees
    WHERE salary = (
        SELECT MIN(salary)
        FROM employees
    )



# 2.查询平均工资最低的部门信息
#方法一：
    #(1)各部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    #(2)查询(1)结果上的最低平均工资
    SELECT MIN(ag)
    FROM (
        SELECT AVG(salary) ag,department_id
        FROM employees
        GROUP BY department_id
    ) ag_dep
    #(3)查询哪个部门的平均工资=(2)
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) = (
        SELECT MIN(ag)
        FROM (
            SELECT AVG(salary) ag,department_id
            FROM employees
            GROUP BY department_id
        ) ag_dep
    )
    #(4)查询部门信息
    SELECT d.*
    FROM departments d
    WHERE d,department = (
        SELECT department_id
        FROM employees
        GROUP BY department_id
        HAVING AVG(salary) = (
            SELECT MIN(ag)
            FROM (
                SELECT AVG(salary) ag,department_id
                FROM employees
                GROUP BY department_id
            ) ag_dep
        )
    )

#方法二：
    #(1)各部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    #(2)求出最低平均工资的部门编号
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 1;



# 3.查询平均工资最低的部门信息和该部门的平均工资
#(1)各部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
#(2)求出最低平均工资的部门编号和平均工资
    SELECT department_id,AVG(salary)
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 1;
#(3)查询部门信息（内连接）
    SELECT d.*,
    FROM departments d
    INNER JOIN (
        SELECT department_id,AVG(salary) ag
        FROM employees
        GROUP BY department_id
        ORDER BY AVG(salary)
        LIMIT 1;
    )ag_dep
    ON d.department_id = ag_dep.department_id;


# 4.查询平均工资最高的 job 信息
#(1)查询每个job的平均工资
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id
#(2)在(1)的结果上获取平均工资最高的job
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id
    ORDER BY AVG(salary) DESC
    LIMIT 1
#(3)查询job信息
    SELECT *
    FROM jobs
    WHERE job_id = (
        SELECT job_id
        FROM employees
        GROUP BY job_id
        ORDER BY AVG(salary) DESC   #order by后面存在的非聚合列必须在select后面出现
        LIMIT 1
    )


# 5.查询平均工资高于公司平均工资的部门有哪些
#(1)查询公司的平均工资
    SELECT AVG(salary)
    FROM employees
#(2)查询每个部门的平均工资
    SELECT AVG(salary)
    FROM employees
    GROUP BY department_id
#(3)筛选(2)结果集，满足平均工资>(1)
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) > (
        SELECT AVG(salary)
        FROM employees
    )


# 6.查询出公司中所有 manager 的详细信息
#(1)查询所有manager的员工编号
    SELECT DISTINCT manager_id
    FROM employees
#(2)查询详细信息，满足employee_id=(1)
    SELECT *
    FROM employees
    WHERE employee_id = ANY(
        SELECT DISTINCT manager_id
        FROM employees
    )


# 7.各个部门中，最高工资中最低的那个部门的最低工资是多少
#(1)查询各部门的最高工资中最低的
    SELECT MAX(salary),department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary) ASC
    LIMIT 1
#(2)查询(1)结果的那个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary) ASC
    LIMIT 1
)


# 8.查询平均工资最高的部门的 manager 的详细信息：last_name，department_id，email，salary
#(1)查询平均工资最高的部门编号
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) DESC
    LIMIT 1
#(2)将employees和departments表连接查询，筛选条件是(1)
    SELECT last_name，d.department_id，email，salary
    FROM employeese e
    INNER JOIN departments d
    ON d.department_id = e.department_id
    WHERE d.department_id = (
        SELECT department_id
        FROM employees
        GROUP BY department_id
        ORDER BY AVG(salary) DESC
        LIMIT 1
    )

分页查询

应用查询：当要显示的数据，一页显示不全，需要分页提交 sql 请求。

语法：

    SELECT 查询列表                    7
    FROM 表                          1
    [JOIN type join 表2              2
    ON 连接条件                        3
    WHERE 筛选条件                    4
    GROUP BY 分组字段                5
    HAVING 分组后的筛选               6
    ORDER BY 排序的字段]                8
    LIMIT offset,size;               9
    # offset 要显示条目的起始索引(起始索引从0开始)
    # size 要显示的条目个数

根据执行顺讯，可以得出：
1. order by 后面的列必须是在 select 后面存在的；
2. select、having 或 order by 后面存在的非聚合列必须全部在 group by 中存在。

特点：

limit 语句放在查询语句的最后
公式

#要显示的页数page    每页的条目数size
    SELECT 查询列表
    FROM 表
    LIMIET (page-1)*size,size;

#案例1：查询前五条员工信息
    SELECT * FROM employees LIMIT 0,5;
    或
    SELECT * FROM employees LIMIT 5;

#案例2：查询第11条——第25条
    SELECT * FROM employees LIMIT 10,15;

union 联合查询

union：将多条查询语句的结果合并成一个结果

语法：

查询语句1
union [all]
查询语句2
union [all]
......



#案例：查询部门编号>90或邮箱包含a的员工信息
    SELECT * FROM employees WHERE email LIKE '%a%'
    UNION
    SELECT * FROM employees WHERE department_id >90;

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点：

要求多条查询语句的查询列数是一致的；
要求多条查询语句的查询的每一列的类型和顺序最好一致；
union 关键字默认去重，如果使用 union all 可以包含重复项；

SELECT 的执行过程

查询的结构

SELECT .....
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...

SELECT 执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。

SQL 的执行原理

首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table） 1-1；
通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
添加外部行。如果我们使用的是左连接、右连接或者全连接，就会涉及到外部行。也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1。就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2.
......

六、`DML`语言的学习

插入语句

方式一：经典的插入

语法：

insert into 表名(列名,....) values(值1,...)

特点：

插入的值的类型要与列的类型一致或兼容；
不可以为 null 的列必须插入值，可以为 Null 的列如何插入值：

方式一：对应的 value 填 null；
方式二：字段和值都省略；

字段的个数和顺序不一定与原始表中的字段个数和顺序一致，但必须保证值和字段一一对应；
列数和值的个数必须一致；
可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致；

方式二：

insert into 表名 set 列名=值,列名=值,...

两种方式大pk

方式一支持插入多行，方式二不支持

INSERT INTO beauty VALUES(23,'唐艺昕'...),(24,'唐艺昕2'...);

方式一支持子查询，方式二不支持

INSERT INTO 表名 查询语句;

INSERT INTO beauty(id,name,phone)
SELECT 26,'刘亦菲','11908425';

修改语句

修改单表的记录

语法：

update 表名                1
set 列=新值,列=新值...      3
[where 筛选条件];           2

修改多表的记录(补充)

sql92 语法：

update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;

sql99 语法:

update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;

#案例：修改没有男朋友的女神的男朋友编号都为2号
    UPDATE boys bo
    RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
    SET b.`boyfriend_id`=2;
    WHERE bo.`id` IS NULL;

删除语句

方式一：delete

单表的删除

delete from 表名 [where 筛选条件] [limit 条目数]

#案例：删除手机号以9结尾的女神系信息
    DELETE FROM beauty WHERE phone LIKE '%9';

多表的删除

sql92 语法

delete [表1的别名,表2的别名] #如果要删除表1，就写表1的别名；要删表2，就写表2的别名；两个都删，写两个表的别名。
from 表1 别名,表2 别名
where 连接条件
and 筛选条件

sql99 语法

delete [表1的别名,表2的别名]
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件

#案例1：删除张无忌的女朋友的信息
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='张无忌'; 


#案例2：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.boyName = '黄晓明';

方式二：truncate (清空)

truncate table 表名;

delete PK truncate (重点)

delete 可以添加 where 条件，truncate 不能加；
truncate 删除，效率高一丢丢；
假如要删除的表中有自增长列，如果用 delete 删除后，再插入数据，自增长列的值从断点开始，而 truncate 删除后，再插入数据，自增长列的值从1开始；
truncate 删除没有返回值，delete 删除有返回值；
truncate 删除不能回滚，delete 删除可以回滚；

MySQL8 新特性：计算列

什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

CREATE TABLE tb1( id INT, a INT, b INT, c INT GENERATED ALWAYS AS (a + b) VIRTUAL );

七、`DDL`语言的学习

标识符命名规则

数据库名、表名不得超过30个字符，变量名限制为29个。
必须只能包含 A-Z，a-z，0-9，_ 共63个字符。
数据库名、表名、字段名等对象中间不要包含空格。
同一个 MySQL 软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名。
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在 SQL 语句中使用着重号引起来。
保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可别变成字符型了。

库的管理

创建：create
修改：alter
删除：drop

1. 库的创建

语法：
    create database [if not exists] 库名 [character set 字符集名];

#案例：创建库books
    CREATE DATABASE IF NOT EXISTS books;

2. 库的修改

RENAME DATABASE books TO 新库名;(现已不用)

更改库的字符集：
    ALTER DATABASE 库名 CHARCATER SET 字符集;

3. 库的删除

DROP DATABASE IF EXISTS 库名;

表的管理

创建：create
修改：alter
删除：drop

1. 表的创建

create table [if not exists] 表名(
    列名 列的类型[(长度) 约束],
    列名 列的类型[(长度) 约束],
    ...
    列名 列的类型[(长度) 约束]
);

#案例：创建表book
    CREATE TABLE IF NOT EXISTS book( #需要用户具备创建表的权限
        id INT,#编号
        bName VARCHAR(20),#图书名
        price DOUBLE,#价格
        authorID INT,#作者编号
        publishDate DATETIME #出版日期
    );

2. 表的修改

alter table 表名 add|drop|modify|change column 列名 [列类型 约束] [first|after 字段名];

(1)修改列名
    ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;

(2)修改列的类型或约束
    ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 [新约束];

(3)添加新列
    ALTER TABLE 表名 ADD COLUMN 列名 类型 [first|after 字段名];

(4)删除列
    ALTER TABLE 表名 DROP COLUMN 列名;

(5)修改表名
    ALTER TABLE 表名 RENAME [TO] 新表名;

3. 表的删除

DROP TABLE IF EXISTS 表名;

4. 表的复制

1.仅仅复制表的结构
    CREATE TABLE 表名 LIKE 旧表;

2.复制表的结构+数据
    CREATE TABLE 表名
    SELECT * FROM 旧表;

3.仅仅复制某些字段和数据 查询语句中字段的别名，可以作为新创建的表的字段的名称
    CREATE TABLE 表名
    SELECT 查询列表
    FROM 旧表
    WHERE 筛选;

常见数据类型介绍

数值型：
- 整型
- 小数：
  - 定点数
  - 浮点数
字符型：
- 较短的文本：char、varchar
- 较长的文本：text、blob(较长的二进制数据)
日期型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

其中，常用的几类类型介绍如下：

数据类型	描述
INT	从-2^31到231-1的整型数据。存储大小为 4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，占用4个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D)	双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D)	高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同
DATE	日期型数据，格式'YYYY-MM-DD
BLOB	二进制形式的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

整型

分类：

    tinyint、smallint、mediumint、int/integer、bigint
字节数  1        2        3            4            8

可设为有符号/无符号

特点：
如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加 unsigned 关键字；
如果插入的数值超出了整型的范围，会报 out of range 异常，并且插入临界值；
如果不设置长度，会有默认的长度；长度代表了显示的最大宽度，如果不够会用 0 在左边填充，但必须搭配 zerofill 使用，并且默认变为无符号整型；
案例

#案例1：如何设置无符号和有符号
    CREATE TABLE tab_int(
        t1 INT
        t2 INT UNSIGNED 
    );

小数

分类：

1.浮点型
    float(M,D)    4字节
    double(M,D)    8字节

2.定点型
    dec(M,D)    占用M+2字节的存储空间 最大范围与double相同，给定decimal的有效取值范围由M和D决定
    decimal(M,D) 定点数以字符串形式进行存储

特点：

(1) M：整数部位+小数部位
    D：小数部位
    如果超过范围，则报out of range，并插入临界值

(2)M和D都可以省略
    如果是decimal，则M默认为10，D默认为0
    如果是float和double，则会根据插入的数值的精度来决定精度

(3)定点型的精度较高，如果要求插入的数值精度较高，如货币运算则考虑使用。

原则：所选择的类型越简单越好，能保存数值的类型越小越好；

字符型

char、varchar、binary、varbinary、enum、set、text、blob

BLOB是一个二进制大对象，四种BLOB类型是：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOG
//todo    https://blog.csdn.net/wb1046329430/article/details/114783334
使用临时表处理的查询结果实例中的BLOB和TEXT列，会导致服务器在磁盘上而不是在内存中使用表


前缀索引

分类：

较短的文本：char、varchar

字符串类型	最多字符数	描述及存储需求	特点	占用的存储空间	效率
char(M)	M，可以省略，默认为1	M为 0~255 之间的整数	固定长度的字符	M个字节	高
varchar(M)	M，不可以省略	M为 0~65535 之间的整数	可变长度的字符	M+1个字节	低

较长的文本：text、blob(较大的二进制)
位类型

位类型字节范围

Bit(M) 1~8 Bit(1)~Bit(8)
binary 和 varbinary 类型

说明：用于保存较短的二进制，类似于char 和 varchar，不同的是它们包含二进制字符串而不包含非二进制字符串；
enum 类型

说明：枚举类型，要求插入的值必须属于列表中指定值之一；
set 类型：用于保存集合；

位类型	字节	范围
Bit(M)	1~8	Bit(1)~Bit(8)

日期型

分类：

date：只保存日期
time：只保存时间
datetime：保存日期+时间
timestamp：保存日期+时间

特点：

日期和时间类型	字节	最小值	最大值	受时区影响
date	4	1000-01-01	9999-12-32
datetime	8	1001-01-01 00:00:00	9999-12-31 23:59:59	不受
timestamp	4	19700101080001	2038年的某个时刻	受
time	3	-838:59:59	838:59:59
year	1	1901	2155

datetime 和 timestamp 的区别：

timestamp 支持的时间范围较小，取值范围：19700101080001 —— 2038年的某个时刻

datetime 的取值范围：1001-01-01 00:00:00 —— 9999-12-31 23:59:59

timestamp 和实际时区有关，更能反映实际的日期，而 datetime 则只能反映出插入时的当地时区；
timestamp 的属性受 mysql版本和 sqlmode 的影响很大；
timestamp 底层存储的是毫秒值。两个日期比较大小或日期计算时，timestamp 更方便、更快。

JSON 类型

当需要检索JSON类型的字段中数据的某个具体值时，可以使用“->”和“->>”符号。

常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性。
分类：六大约束

NOT NULL：非空，用于保证该字段的值不能为空。比如姓名、学号等
DEFAULT：默认，用于保证该字段有默认值。比如性别
PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空。比如学号、员工编号等。
UNIQUE：唯一，用于保证该字段的值具有唯一性，可以有多个为空。唯一约束可以是某个列的值唯一，也可以是多个列组合的值唯一。
CHECK：检查约束。比如年龄、性别。
FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号。

添加约束的时机：

创建表时
修改表时

约束的添加分类：

列级约束：六大约束语法上都支持，但外键约束没有效果。

表级约束：除了非空、默认，其他的都支持。

主键和唯一的区别

	保证唯一性	是否允许为空	一个表中可以有多个	是否允许组合(多个字段组合成一个)
主键	√	×	至多有1个	√，但不推荐
唯一	√	√	可以有多个	√，但不推荐

外键：
要求在从表设置外键关系；
从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求；
主表的关联列必须是一个key（一般是主键或唯一）；
插入数据时，先插入主表，再插入从表；
删除数据时，先删除从表，再删除主表；
可以通过以下两种方式来删除主表的记录

```sql
# 方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majofid) REFERENCES major(id) ON DELETE CASCADE;
DELETE FROM major WHERE id = 3;

# 方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majofid) REFERENCES major(id) ON DELETE SET NULL;
DELETE FROM major WHERE id = 3;
```

列级约束和表级约束的区别

	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持，但外键没有效果	不可以
表级约束	所有列的下面	默认和非空不支持，其他支持	可以(主键没有效果)

查看表中的约束

SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名';

一、创建表时添加约束

1.添加列级约束

语法：直接在字段名和类型后面追加约束类型即可。

只支持：默认、非空、主键、唯一；

CREATE TABLE stuinfo(
    id INT PRIMARY KEY,#主键
    stuName VARCHAR(20) NOT NULL,#非空
    gender CHAR(1) CHECK(gender='男' OR gneder='女'),#检查
    seat INT UNIQUE,#唯一
    age INT DEFAULT 18,#默认约束
    majorID INT FOREIGN KEY REFERENCES major(id) #外键
);

CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);

#查看stuinfo中所有索引，包括主键、外键、唯一
    SHOW INDEX FROM stuinfo;

2.添加表级约束

语法：在各个字段的最下面；

[constraint 约束名] 约束类型(字段名)

CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT,

    [CONSTRAINT pk] PRIMARY KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat),#唯一键
    CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查
    CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表(被引用列),#外键
);

#通用的写法
CREATE TABLE IF NOT EXISTS stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    gender CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

二、修改表时添加约束

1.添加列级约束
    alter table 表名 modify column 字段名 字段类型 新约束;

2.添加表级约束
    alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用]


#案例1：添加非空约束
    ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

#案例2：添加默认约束
    #(1)列级约束
    ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
    #(2)表级约束
    ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#案例3：添加外键
    ALTER TABLE 从表名 ADD [CONSTRAINT fk_stuinfo_major] FOREIGN KEY(从表外键) REFERENCE 主表名(主表主键);

三、修改表时删除约束

1.删除非空约束
    ALTER TABLE 表名 MODIFY COLUNM 字段名 字段类型;

2.删除主键
    ALTER TABLE 表名 DROP PRIMARY KEY;

PRIMARY KEY 约束

一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
主键约束对应着表中的一列或者多列（复合主键）。
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
MySQL 的主键名总是 PRIMARY，就算自己命名了主键约束名也没用。
当创建主键约束时，系统默认会在所在列或列组合上建立对应的主键索引。如果删除主键约束了，主键约束对应的索引就自动删除了。

唯一性约束

特点：

同一个表可以有多个唯一约束。
唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
唯一性约束允许列值为空。
在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
MySQL 会给唯一约束的列上默认创建一个唯一索引。

删除唯一约束：

添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和 () 中排在第一个的列名相同。也可以自定义索引名。

ALTER TABLE USER
DROP INDEX 索引名;

FOREIGN KEY 约束

在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名。
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。
删除外键后，必须手动删除对应的索引。

约束等级：

Cascasde 方式：在父表上 update/delete 记录时，同步 update/delete 掉子表的匹配记录。
Set null 方式：在父表上 update/delete 记录时，将子表上匹配记录的列设置为 null，但是要注意子表的外键列不能为 not null。
No action 方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行 update/delete 操作。
Restrict 方式：同 no action，都是立即检查外键约束。
Set default 方式：父表有变更时，子表将外键列设置成一个默认的值，但 Innodb 不能识别。

对于没有指定等级，就相当于 Restrict 方式。

对于外键约束，最好是采用：ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

create table emp(
eid int primary key, #员工编号 
ename varchar(5), #员工姓名 
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) on update cascade on delete set null
#把修改操作设置为级联修改等级，把删除操作设置为set null等级 
)

check 约束

作用：检查某个字段的值是否符合 xx 要求，一般指的是值的范围。
说明：MySQL 5.7 不支持，MySQL 8.0 支持。

标识列：又称为自增长列

含义：可以不用手动的插入值，系统提供默认的序列值。默认从1开始，步长为1。

特点：

标识列必须和主键搭配吗？不一定，但要求是一个key。
自增长列约束的列必须是键列（主键列，唯一键列）。
一个表可以有几个标识列？至多一个。
标识列的类型只能是数值型。
标识列可以通过 SET auto_increment_increment=3 设置步长；可以通过手动插入值，设置起始值。
如果自增列指定了 0 或 NULL，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

一、创建表时设置标识列

CREATE TABLE 表(
    字段名 字段类型 约束 AUTO_INCREMENT
);

二、修改表时设置标识列

ALTER TABLTE 表名 MODIFY COLUMN 字段名 字段类型 约束 AUTO_INCREMENT;

三、修改表时删除标识列

ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束;

八、`TCL`语言的学习

事务和事务处理

Transcation Control Language 事务控制语言 ;
事务：一个或一组 sql 语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

事务的ACID(acid)属性

原子性(Atomicity)：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性(Consistency)：事务必须使数据库从一个一致性状态变换到另一个一致性状态。
隔离性(Isolation)：是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性(Durability)：指一个事务一旦被提交，它对数据库中的数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的创建

隐式事务

隐式事务：事务没有明显的开启和结束的标记。

比如insert、update、delete语句

显式事务

显式事务：事务具有明显的开启和结束的标记。

前提：必须先设置自动提交功能为禁用。 set autocommit=0;

#步骤1：开启事务
    set autocommit=0;
    start transaction; 可以省略

#步骤2：编写事务中的sql语句(select、insert、update、delete)
    语句1;
    语句2;    
    ...

#步骤3：结束事务
    commit; 提交事务
    rollback; 回滚事务    rollback to 回滚点名;
    savapoint 节点名; 设置保存点

事务的隔离

并发事务

事务的并发问题如何发生的？

多个事务同时操作同一个数据库的相同数据时。

并发问题都有哪些？

脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了不一定最终存在的数据，就是脏读。

一个事务读取了其他事务还没有提交的数据，读取到的是其他事务“更新”的数据。

对于两个事务 T1、T2，T1 读取了已经被 T2 更新但还没被提交的字段，之后，若 T2 回滚，T1 读取的内容就是临时且无效的。

总结一句话：无论是脏写还是脏读，都是因为一个事务去更新或者查询了另一个还没有提交的事务更新过的数据。因为另一个事务还没有提交，它随时都可能回滚，那么必然导致你更新的数据就没了，或者你之前查询到的数据就没了，这就是脏写和脏读两种场景。
不可重复读：指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据 更新(update) 操作。

一个事务多次读取，结果不一样。

对于两个事务 T1、T2，T1 读取了一个字段，然后 T2 更新了该字段，之后，T1 再次读取同一个字段，值就不同了。
幻读：幻读是针对数据插入(INSERT)操作来说的。

一个事务读取了其他事务还没有提交的数据，只是读到的是其他事务“插入”的数据。

对于两个事务 T1、T2，T1 从一个表中读取了一个字段，然后 T2 在该表中插入了一些新的行，之后，如果 T1 再次读取同一个表，就会多出几行。

如何解决并发问题？通过设置隔离级别解决并发问题。

4 种隔离级别

read uncommitted（读未提交数据）

允许事务读取未被其他事务提交的变更，脏读、不可重复读和幻读的问题都会出现。
read committed（读已提交数据）

只允许事务读取已经被其它事务提交的变更，可以避免脏读，但不可重复读和幻读问题仍然可能出现。
repeatable read（可重复读）

确保事务可以从一个字段中读取相同的值，在这个事务持续期间，禁止其它事务对这个字段进行更新。可以避免脏读和不可重复度，但幻读的问题仍然存在。
serializable（串行化）

确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其它事务对该表执行插入，更新和删除操作。所有并发都可以避免，但性能低下。

注：Oracle 支持的 2 中事务隔离级别：READ COMMITED、SERIALIZABLE。Oracle 默认的事务隔离级别是：READ COMMITED；

Mysql 支持 4 种事务隔离级别。mysql 默认的事务隔离级别是：REPEATABLE READ。

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

设置隔离级别

每启动一个 mysql 程序，就会获得一个单独的数据库连接，每个数据库连接都有一个全局变量 @@tx_isolation，表示当前的事务隔离级别；

#查看隔离级别
    select @@tx_isolation;

#设置隔离级别
    set session|global transaction isolation level 隔离级别;

回滚点

#演示savepoint的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id = 25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id = 28;
ROLLBACK TO a;#回滚到保存点

delete和truncate区别

delete可以回滚，truncate不可以；

#演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

#演示truncate
SET autocommit=0;
START TRANSACTION;
TRUNCATE account;
ROLLBACK;

COMMIT 和 ROLLBACK

COMMIT：提交数据，一旦执行 COMMIT，则数据就被永久的保存在了数据库汇总，意味着数据不可以回滚。

ROLLBACK：回滚数据。一旦执行 ROLLBACK，则可以实现数据的回滚。回滚到最近的一次 COMMIT 之后。

DDL 的操作一旦执行，就不可以回滚。指令 SET autocommi = FALSE 对 DDL 操作失效。

DML 的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行 DML 之前，执行了 SET autocommit = FALSE，则执行的 DML 操作就可以实现回滚。

九、视图

含义：虚拟表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。

和普通表一样使用，是通过表动态生成的数据。

简化 sql 语句；
提高了 sql 的重要性；
保护基表的数据，提高了安全性；

创建视图

#语法
    CREATE [OR REPLACE]
    [ALGORITM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW 视图名称 [(字段列表]
    AS 查询语句
    [WITH [CASCADED|LOCAL] CHECK OPTION]
#精简版
    create view 视图名 [(字段名称)]
    as
    查询语句;

#案例1：查询员工姓名中包含a字符的员工名、部门名和工种信息
#(1)创建
    CREATE VIEW myv1
    AS
    SELECT last_name,department_name,job_title
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN jobs j ON j.job_id = e.job_id;
#(2)使用
    SELECT * FROM myv1 WHERE last_name LIKE '%a%';

#案例2：查询各部门的平均工资级别
#(1)创建视图查看某个部门的平均工资
    CREATE VIEW myv2
    AS
    SELECT AVG(salary) AS ag,department_id
    FROM employees
    GROUP BY department_id;
#(2)使用
    SELECT myv2.ag,g.grade_level
    FROM myv2
    JOIN job_grades g
    ON myv2.ag BETWEEN g.lowest_sal AND g.highest_sal;

修改视图

#方式一：
    create or replace view 视图名
    as
    查询语句;

#方式二：
    alter view 视图名
    as
    查询语句;

删除视图

drop view 视图名1,视图名2,...;

说明：基于视图 a、b 创建了新的视图 c，如果将视图 a 或者视图 b 删除，会导致视图 c 的查询失败。这样的视图 c 需要手动删除或修改，否则影响使用。

查看视图

desc 视图名;
show create view 视图名;

更新视图

#1.插入
    INSERT INTO 视图名 VALUES(...);

#2.修改
    UPDATE 视图名 SET last_name = '张无忌' WHERE last_name='张无忌';

#3.删除
    DELETE FROM 视图名 筛选条件;

视图一般用于查询的，而不是更新的，所以具备以下特点的视图不允许更新：

包含以下关键字的 sql 语句：分组函数、distinct、group by、having、union 或者 union all；
常量视图；
select 中包含子查询；
join；
from 一个不能更新的视图；
where 子句的子查询引用了 from 子句的表；

视图和表的对比

	创建语法的关键字	是否实际占用物理空间	使用
视图	create view	只是保存了sql逻辑	增删改查，一般不能增删改
表	create table	保存了数据	增删改查

十、变量

系统变量：变量是由系统提供的，不用自定义。
- 全局变量
  - 服务器层面上的，必须拥有 super 权限才能为系统变量赋值。
  - 作用域：服务器每次启动都将为所有的全局变量赋初始值，针对于所有的会话(连接)有效，但不能跨重启。
- 会话变量
  - 服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接(会话)；
  - 作用域：仅仅针对于当前会话有效。
自定义变量
- 用户变量
- 局部变量

系统变量

说明：变量由系统提供的，不是用户定义，属于服务器层面；

语法：

#1.查看所有的系统变量
    show global|session variables;

#2.查看满足条件的部分系统变量
    show global | [session] variables like '%char%';

#3.查看指定的某个系统变量的值
    select @@global | [@@session].系统变量名

4.为某个系统变量赋值
    方式一：修改配置文件
    方式二：set global | [session] 系统变量名 = 值;
    方式三：set @@global | [session].系统变量名 = 值;

注意：如果是全局级别，则需要加 global，如果是会话级别，则需要加 session，如果不写，则默认是 session

作为 MySQL 编码规范，MySQL 中的系统变量以 两个"@" 开头，其中 "@@global" 仅用于标记全局系统变量，"@@session" 仅用于标记会话系统变量。"@@" 首先会标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

全局变量持久化

MySQL 8.0 的新特性

在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。例如，设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现：

SET GLOBAL MAX_EXECUTION_TIME=2000;

使用SET GLOBAL语句设置的变量值只会 临时生效 。数据库重启后，服务器又会从MySQL配置文件中读取变量

的默认值。 MySQL 8.0版本新增了 SET PERSIST 命令。例如，设置服务器的最大连接数为1000：

SET PERSIST global max_connections = 1000;

MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用

其中的配置来覆盖默认的配置文件。

自定义变量

说明：变量是用户自定义的，不是由系统的。

使用步骤：
声明
赋值
使用(查看、比较、运算等)

用户变量

作用域：针对于当前会话(连接)有效，同于会话变量的作用域。

赋值的操作符： = 或 :=

(1)声明并初始化
方式一："=" 或 ":="
    set @用户变量名 = 值;
    set @用户变量名 := 值;
方式二：":=" 或 INTO 关键字
    select @用户变量名:= 表达式 [FROM 等子句];
    SELECT 表达式 INTO @用户变量 [FROM 等子句];

(2)赋值(更新用户变量的值)
    方式一：通过 set 和 select
        set @用户变量名=值;或
        set @用户变量名:=值;或
        select @用户变量名:=值;
    方式二：通过 select into
        select 字段 into @变量名
        from 表;

(3)使用(查看用户变量的值)
    select @用户变量名;

局部变量

作用域：仅仅是在定义它的 begin end 中有效。应用在 begin end 中的第一句话！

(1)声明：使用 DECLARE 语句定义一个局部变量
    DECLARE 变量名 类型 [DEFAULT 值]; #如果没有 DEFAULT子句，初始值为 NULL

(2)赋值
    方式一：通过 set 和 select
        set 局部变量名=值;或
        set 局部变量名:=值;或
        select @局部变量名:=值;
    方式二：通过 select into
        select 字段 into 局部变量名
        from 表;

(3)使用
    select 局部变量名;

对比用户变量和局部变量

	作用域	定义和使用的位置	语法
用户变量	当前会话	会话中的任何地方	必须加@符号，不用限定类型
局部变量	BEGIN END中	只能在 BEGIN END 中，且为第一句话	一般不用加@符号，需要限定类型

#案例：声明两个变量并赋初始值，求和，并打印
#1.用户变量
    SET @m=1;
    SET @m=2;
    SET @sum = @m + @n;
    SET @sum;

#2.局部变量
    BEGIN
        DECLARE m INT DEFAULT 1;
        DECLARE n INT DEFAULT 2;
        DECLARE SUM INT;
        SET SUM = m + n;
        SELECT SUM;
    END

十一、存储过程和函数

存储过程和函数：类似于 java 中的方法。

存储过程

含义：一组预先编译好的 SQL 语句的集合，理解成批处理语句；

优点：
1.提高代码的重用性；
2.简化操作；
3.减少了编译次数并且减少了和数据库的连接次数，提高了效率；

一、创建语法

CREATE PROCEDURE 存储过程名(参数列表)
[characteristics ...]
BEGIN
    存储过程体(一组合法的SQL语句)
END

注意：

参数列表包含三部分：参数模式参数名参数类型

举例：IN stuname VARCHAR(20)

参数模式：

IN：该参数可以作为输入，该参数需要调用方法传入值；
OUT：该参数可以作为输出，也就是该参数可以作为返回值；
INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值；

举例：
调用 IN 模式的参数：call sql('值');
调用 OUT 模式的参数：set @name; call sql(@name);
调用 INOUT 模式的参数：set @name; call sql(@name); select @name;

如果存储过程仅仅只有一句话，BEGIN END 可以省略；
存储过程体中的每条 SQL 语句的结尾要求必须加分号；
存储过程的结尾可以使用 DELIMITER 重新设置；

语法：DELIMITER 结束标记
案例：DELIMITER $

characteristics 表示创建存储过程时指定的对存储过程的约束条件。

二、调用语法

CALL 存储过程名(实参列表);

三、删除存储过程

DROP PROCEDURE 存储过程名;

四、查看存储过程的信息

SHOW CREATE PROCEDURE 存储过程名;

五、案例

#1.空参列表
#案例：插入到 admin 表中五条记录
    DELIMITER $
    CREATE PROCEDUER myp1()
    BEGIN
        INSERT INTO admin(username,`password`)  VALUES ('john1','0000'),('lily','0000');
    END $

    #调用
    CALL myp1() $


#2.创建带 in 模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName varchar(20))
BEGIN    
    SELECT bo.*
    FROM boys bo
    RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
    WEHRE b.name = beautyName;
END $
    #调用
    CALL myp2('小昭')$

#案例2：创建存储过程实现，用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp4(IN usernaame VARCHAR(20),IN password VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0; #声明并初始化

    SELECT COUNT(*) INTO result #赋值
    FROM admin
    WHERE admin.username = username
    AND admin.password = password;

    SELECT IF(result >0,'成功','失败'); #使用
END $

    #调用 
    CALL mp4('张飞','6666') $


#3.创建带 in 和 out 模式参数的存储过程        
#案例：根据女神名，返回对应的男神名和男神魅力值
DELIMITER $
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
    SELECT bo.boyName,bo.userCP INTO boyName,userCP
    FROM boys bo
    INNER JOIN beauty b ON bo.id = b.boyfriend_id
    WHERE b.name = beautyName;
END $

    #调用
    CALL myp6('小昭'，@bName,@usercp)$
    SELECT @bName,@usercp$


#4.创建带 inout 模式参数的存储过程
#案例1：传入 a 和 b 两个值，最终 a 和 b 都翻倍并返回
CREATE PROCEDURE myp8(INOUT a int,INOUT b int)
BEGIN
    SET a = a*2;
    SET b = b*2;
END $


    #调用
   SET @m=10$
   SET @n=20$
   CALL(@m,@n)$
   SELECT @m,@n$

存储函数

区别：

存储过程：可以有 0 个返回，也可以有多个返回，适合做批量插入，批量更新。
函数：有且仅有 1 个返回。适合做处理数据后返回一个结果。

一、创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
[characteristics]
BEGIN
    函数体
END

注意：

参数列表包含两部分：参数名参数类型。
函数体：肯定会有 return 语句，如果没有会报错

如果 return 语句没有放在函数体的最后也不会报错，但不建议。

函数体重仅有一句话，则可以省略 begin end。
使用 delimiter 语句设置结束标记。

二、调用语法

select 函数名(参数列表)

案例

#案例：根据员工名，返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20) RETURNS DOUBLE
BEGIN
    SET @sal=0; #定义用户变量
    SELECT salary INTO @sal #赋值
    FROM employees
    WHERE last_name = empName
    LIMIT 1;

    RETURN @sal;
END $

SELECT myf2('k_king') $

#案例：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    DECLARE sal DOUBLE;
    SELECT AVG(salary) INTO sal
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name = deptName;
    RETURN sal;
END $

SELECT myf3('IT') $


#创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_func1(num1 FLOAT,NUM2 FLOAT) RETURNS FLOAT
BEGIN
    DECLARE SUM FLOAT DEFAULT 0;
    SET SUM = num1 + num2;
    RETURN SUM;
END $

SELECT test_func(1,2) $

三、查看函数

SHOW CREATE FUNCTION 函数名;

四、删除函数

DROP FUNCTION 函数名;

对比存储过程和存储函数

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程名()	理解为有0个或多个	一般用于更新
存储函数	FUNCTION	SELECT 函数名()	只能是一个	一般用于查询结果为一个值并返回时

此外，存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

定义条件与处理程序

定义条件 是事先定义程序执行过程中可能遇到的问题， 处理程序 定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

说明：定义条件和处理程序在存储过程、存储函数中都是支持的。

定义条件

定义条件就是给 MySQL 中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件 关联起来。这个名字可以随后被用在定义处理程序的``DECLARE HANDLER` 语句中。

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

定义处理程序

可以为 SQL 执行过程中发生的某种错误类型的错误定义特殊的处理程序。

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。

案例解决

DELIMITER // 
CREATE PROCEDURE UpdateDataNoCondition() 
    BEGIN
        #定义处理程序 
        DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
        SET @x = 1;
        UPDATE employees SET email = NULL WHERE last_name = 'Abel'; 
        SET @x = 2; 
        UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel'; 
        SET @x = 3; 
    END // 

DELIMITER ;

#调用过程：
CALL UpdateDataWithCondition();
SELECT @x,@proc_value;

十二、流程控制结构

顺序结构
分支结构
循环结构

分支结构

if 函数

功能：实现简单的双分支
语法： IF(表达式1,表达式2,表达式3)
执行顺序：如果表达式1成立，则 if 函数返回表达式2的值，否则返回表达式3的值。
应用：任何地方。

case 结构

情况1：类似于 java 中的 switch 语句，一般用于实现等值判断。

语法：

CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1或语句1;
WHEN 要判断的值 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END CASE;

情况2：类以 java 中的多重 if 语句，一般用于实现区间判断。

语法：

CASE 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n；
END CASE;

特点：
(1)可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，BEGIN END 中或 BEGIN END 的外面。可以作为独立的语句去使用，只能放在BEGIN END 中。
(2) 如果 WHEN 中的值满足或条件成立，则执行对应对的 THEN 后面的语句，并且结束 CASE。如果不满足，则执行 ELSE 中的语句或值。
(3) ELSE 可以省略，如果 ELSE 省略了，并且所有的 WHEN 条件不满足，则返回 NULL。

 #案例：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，显示A；80-90，显示B；60-80，C；否则，显示D
CREATE PROCEDURE test_case(IN score INT)
BEGIN
    CASE
    WHEN score >=90 AND score <=100 THEN SELECT 'A';
    WHEN score>=80 THEN SELECT 'B';
    WHEN score>=60 TEHN SELECT 'C';
    ELSE SELECT 'D';
    END CASE
END $

CASE test_case(86) $

if 结构

功能：实现多重分支。

语法：

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
[ELSE 语句n;]
END IF;

应用场景：应用在 begin end 中。

循环结构

分类：while、loop、repeat

循环控制：
iterate 类似于 continue，继续，结束本次循环，继续下一次。只可以用在循环语句内。
leave 类似于 break，跳出；结束当前所在的循环。可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内。

1.while
语法：
    [标签:] while 循环条件 do
        循环体
    end while [标签];

2.loop
语法：
    [标签:] loop
        循环体
    end loop [标签];
# LOOP 内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。

3.repeat
语法：
    [标签:] repeat
        循环体
    until 结束循环的条件
    end repeat [标签];

#案例1：批量插入，根据次数插入到admin表中的多条记录。    
#1.没有添加循环控制
    DROP PROCEDURE pro_while1$
    CREATE PROCEDURE pro_while1(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
            WHILE 1<=insertCount DO
                INSERT INTO admin(username,`password`) VALUES(CONTACT('Rose',i),'666')
                SET i=i+1;
            END WHILE;
    END $

    CALL pro_while1(100)$


#2.添加 leave 语句
#案例：批量插入，根据次数插入到 admin 表中的多条记录。，如果次数 >20 则停止
    TRUNCATE TABEL admin5$
    DROP PROCEDURE test_while1$
    CREATE PROCEDURE test_while(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
            a:WHILE i <= insertCount DO
                INSERT INTO amdin5(username,`password`) VALUES(CONTACT('xiaohua',i),'0000');
                IF i>=20 TEHN LEAVE a;
                END IF;
                SET i=i+1;
            END WHILE a;
    END $

    CALL test_while1(100)$



#已知表stringcontent 其中字段：
#id 自增长
#content varchar(20)
#向该表插入指定个数的，随机的字符串

CREATE TABLE stringcontent(
    id INT PRIMARY KEY AUTO_INCREMENT;
    content VARCHAR(20);
);

DELIMITER $
CREATE PROCEDURE test_randstr_insert(in insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;#定义一个循环变量i，表示插入次数
    DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
    DECLARE startIndex INT DEFAULT 1;#代表起始索引
    DECLARE len INT DEFAULT 1;#代表截取的字符的长度
    WHILE i<=insertCount DO
        SET len = FLOOR(RAND()*(20-startIndex+1)+1);#产生一个随机的整数，代表截取长度
        SET startIndex = FLOOR(Rand()*26+1);#产生一个随机的整数,代表起始索引
        INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
        SET i=i+1;#循环变量更新
    END WHILE;

END $

对比

这三种循环都可以省略名称，但如果循环中添加了循环控制语句（leave 或 iterate），则必须添加名称。
loop 一般用于实现简单的死循环；

while 先判断，后执行；

repeat 先执行，后判断，无条件至少执行一次。

游标

游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结果。游标让 SQL 这种面相集合的语言有了面相过程的开发能力。

MySQL中游标可以在存储过程和函数中使用。

触发器

1.触发器概述

触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。

2.触发器的创建

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 
触发器执行的语句块;

说明：

表名 ：表示触发器监控的对象。
BEFORE | AFTER：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
INSERT | UPDATE | DELETE：表示触发的事件。
- INSERT 表示插入记录时触发。
- UPDATE 表示更新记录时触发。
- DELETE 表示删除记录时触发。
触发器执行的语句块 ：可以是单条SQL语句，也可以是由 BEGIN…END 结构组成的复合语句块。

#定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，
#在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，
#如果大于领导薪资，则报sqlstate_value为'HY000'的错误，从而使得添加失败。

DELIMITER // 

CREATE TRIGGER salary_check_trigger 
BEFORE INSERT ON employees FOR EACH ROW 
BEGIN
    DECLARE mgrsalary DOUBLE; 
    SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id; 
    IF NEW.salary > mgrsalary THEN 
        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误'; 
    END IF; 
END // 

DELIMITER ;

3.查看、删除触发器

3.1 查看触发器

方式一：查看当前数据库的所有触发器的定义

SHOW TRIGGERS\G

方式2：查看当前数据库中某个触发器的定义

SHOW CREATE TRIGGER 触发器名

方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息

SELECT * FROM information_schema.TRIGGERS;

3.2 删除触发器

DROP TRIGGER IF EXISTS 触发器名称;

4.注意点

注意，如果在子表中定义了外键约束，并且外键指定了 ON UPDATE/DELETE CASCADE/SET NULL 子句，此时修改父表引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的 UPDATE 和 DELETE 语句定于的触发器并不会被激活。

MySQL 8.0 其他新特性

MySQL 高级

新特定1：窗口函数

窗口函数分类

MySQL 从 8.0 版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条函数，而窗口函数是将结果置于每一条数据记录中。

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。

窗口函数可以分为 静态窗口函数 和 动态窗口函数。

静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同。
动态窗口函数的窗口大小会随着记录的不同而变化。

窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数，如下表：

函数分类	函数	函数说明
序号函数	ROW_NUMBER()	顺序排序
	RNAK()	并列排序，会跳过重复的序号，比如序号为1、1、3
	DENSE_RANK()	并列函数，不会跳过重复的序号，比如序号为1、1、2
分布函数	PRECENT_RANK()	等级值百分比
	CUME_DIST()	累计分布值
前后函数	LAG(expr,n)	返回当前行的前n行的expr的值
	LEAD(expr,n)	返回当前行的后n行的expr的值
首尾函数	FIRST_VALUE(expr)	返回第一个expr的值
	LAST_VALUE(expr)	返回最后一个expr的值
其他函数	NTH_VALUE(expr,n)	返回第n个expr的值
	NTILE(n)	将分区中的有序数据分为n个桶，记录桶编号

语法结构

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
或者是
函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

OVER 关键字指定函数窗口的范围。
- 如果省略后面括号中的内容，则窗口函数会包含满足 WHERE 条件的所有记录，窗口函数会基于所有满足 WHERE 条件的记录进行计算。
- 如果 OVER 关键字后面的括号不为空，则可以使用如下语法设置窗口。
窗口名：为窗口设置一个别名，用来标识窗口。
PARTITION BY 子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
ORDRE BY 子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
FRAME 子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。

新特性2：公用表达式

公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）。CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。

依据语法结构和执行方式的不同，公用表表达式分为 普通公用表表达式 和 递归公用表表达式 2 种。

# 普通公用表达式
WITH CTE名称 
AS （子查询） 
SELECT|DELETE|UPDATE 语句;


# 递归公用表表达式
WITH RECURSIVE CTE名称 
AS （子查询） 
SELECT|DELETE|UPDATE 语句;

mysql 的架构介绍

mysql 简介

概述

关系型数据库；

高级 mysql

mysql 内核
sql 优化工程师
mysql 服务器的优化
各种参数常量设置
查询语句优化
主从复制
软硬件升级
容灾备份
sql 编程

mysqlLinux 版的安装

检查当前系统是否安装过 mysql

查询命令：rpm -qa|grep -i mysql
删除命令：rpm -e RPM软件包名

安装 mysql 服务端

rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm

安装 mysql 客户端
查看 mysql 安装时创建的 mysql 用户和 mysql 组
```
cat /etc/passwd |grep mysql
cat /etc/group |grep mysql
```
mysql 服务的启+停
```
service mysql start
service mysql stop
```

mysql 服务启动后，开始连接

设置root密码：/usr/bin/mysqladmin -u root password 123456

自启动 mysql 服务

chkconfig mysql on  设置开机自动启mysql
chkconfig --list |grep mysql
ntsysv：看到 [*]mysql 这一行，表示开机后会自动启动mysql

修改配置文件位置

拷贝：cp /usr/share/mysql/my-default/cnf  /etc/my.cnf

修改字符集和数据存储路径

1.查看字符集
    show variables like 'character%';
    show variables like '%char%';

2.修改
    vim /etc/my.cnf        注：如何让mysql使用指定的/etc/my.cnf配置文件呢？
    [client]
    #passsword = your_password
    port = 3306
    socket = ...
    default-character-set = utf8    新增

    [mysqld]
    port = 3306
    character_set_server = uft8     新增
    character_set_client = uft8        新增
    collation-server = uft8_general_ci     新增

    [mysql]
    no-auto-rehash
    default-character-set=uft8        新增

3.重启mysq
    service mysql stop
    service mysql start

4.重新连接后重新create database并使用新建库，然后再重新建表试试

mysql 的安装位置

在linux下查看安装目录 ps -ef|grep mysql

路径	解释	备注
/var/lib/mysql	mysql数据库文件的存放路径	/var/lib/mysql/atguigu.cloud.pid
/usr/share/mysql	配置文件目录	mysql.service 命令及配置文件
/usr/bin	相关命令目录	mysqladmin mysqldump 等命令
/etc/init.d/mysql	启停相关脚本

mysql 配置文件

二进制日志 log-bin：主从复制
错误日志 log-error：默认是关闭的，记录严重的警告和错误信息
查询日志 log：默认关闭，记录查询的 sql 语句，如果开启
数据文件
如何配置

mysql 逻辑架构介绍

(1)连接层
(2)服务层
(3)引擎层
(4)存储层

mysql 存储引擎

mysql存储引擎
查看命令

    SHOW ENGINES;
    show variables like '%storage_engine%'; #查看mysql当前默认的存储引擎

> MyISAM和InnoDB
    对比项         MyISAM         InnoDB

    主外键         不支持         支持
    事务         不支持         支持
    行表锁         表锁         行锁
    缓存         只缓存索引， 索引和数据都缓存
                不缓存真实数据

    表空间         小             大
    关注点         性能          事务
    默认安装     Y             Y

索引优化分析

> 性能下降SQL慢，执行时间长，等待时间长
    > 查询语句写的烂
    > 索引失效
    >
    >     > 单值
    >     > 复合
    > 关联查询太多join(设计缺陷或不得已的需求)
    > 服务器调优及各个参数设置(缓存、线程等)

> 常见的join查询
    > SQL执行顺序
    > join图
    > 建表sql
    > 7种join

> 索引简介
    MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的 数据结构。可以简单理解为“排好序的快速查找数据结构”

    > mysql索引分类
        单值索引：即一个索引只包含单个列，一个表可以多个单列索引。
        唯一索引：索引列的值必须唯一，但允许有空值
        复合索引：即一个索引包含多个列。
        基本语法：
            > 创建
                create [unique] index indexName ON mytale(columnname(length));
                ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
            > 删除
                DROP INDEX [indexName] ON mytable;
            > 查看
                SHOW INDEX FROM table_name;
            > 使用ALTER命令：有四种方式来添加数据表的索引
                ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
                ALTER TALBE tbl_name ADD UNIQUE index_name (column_list)：这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)
                ALTER TABLE tbl_name ADD INDEX index_name (column_list)：添加普通索引，索引值啃出现多次
                ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)：该语句指定了索引为 FULLTEXT，用于全文索引。

        > mysql索引结构
            BTree索引
            Hash索引
            full-text全文索引
            R-Tree索引

> 性能分析
    > MySql Query Optimize
    > MySQL常见瓶颈
    > explain
        > 是什么(查看执行计划)：使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
        > 能干嘛
            表的读取顺序
            数据读取操作的操作类型
            哪些索引可以使用
            哪些索引被实际使用
            表之间的引用
            每张表有多少行被优化器查询
        > 怎么玩
            Explain + SQL语句
            执行计划包含的信息
                id select_type table type possible_keys key key_len ref rows Extra
        > 各字段解释
            id：
                select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
                三种情况
                    id相同，执行顺序由上至下
                    id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
                    id相同不同，同时存在。
            select_table
                > 有哪些
                    id  select_type
                    1     SIMPLE
                    2     PRIMARY
                    3     SUBQUERY
                    4     DERIVED
                    5     UNION
                    6     UNION RESULT
                > 查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
                    1.SIMPLE：简单的select查询，查询中不包含子查询或者UNION
                    2.PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
                    3.SUBQUERY：在select或where列表中包含了子查询
                    4.DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里。
                    5.UNION
                    6.UNION RESULT

            table
            type
                > ALL  index  range ref  eq_ref  const,system  NULL
                >访问类型排列
                    > 显示查询使用了何种类型，从最好到最差依次是：system > const > eq_ref > range > index > ALL
                    > system：表只有一行记录(等于系统表)，这是const类型的特例。
                    > const：表示通过索引一次就找到了，const用于比较primary key或者unique索引
                    > eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
                    > ref：非唯一性索引扫描，返回匹配某个单独值的所有行。

            possible_keys：
            key
            key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。
            ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。
            rows
            Extra
                1.Using filesort
                2.Using temporary
                3.Using index
                4.Using where
                5.using join buffer
                6.impossible where

> 索引优化
    > 索引分析
        单表
        两表
        三表
    > 索引失效(应该避免)
        1.全值匹配我最爱
        2.最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
        3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。
        4.存储引擎不能使用索引中范围条件右边的列。
        5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
        6.myslq在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描。
        7.is null，is not null也无法使用索引。
        8.like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作。
            问题：解决like '%字符串%' 时索引不被使用的方法？
            答：覆盖索引
        9.字符串不加单引号索引失效
        10.少用or，用它来连接时会索引失效
        11.小总结

    > 一般性建议

查询截取分析

查询优化

>     > 永远小表驱动大表
    > order by关键字优化
        > 为排序使用索引
            mysql两种排序方式：文件排序或扫描有序索引排序
            mysql能为排序与查询使用相同的索引

            key a_b_c(a,b,c)

            order by 能使用索引最左前缀
            - order by a
            - order by a,b
            - order by a,b,c
            - order by a DESC, b DESC, c DESC

            如果where使用索引的最左前缀定义为常量，则order by能使用索引
            - WHERE a = const ORDER BY b,c
            - WHERE a = const AND b = const ORDER BY c
            - WHERE a = const AND b > const ORDER BY b,c

            不能使用索引进行排序
            - ORDER BY a ASC,b DESC,c DESC  /*排序不一致*/
            - WHERE g = const ORDER BY b,c     /*丢失a索引*/
            - WHERE a = const ORDER BY c     /*丢失b索引*/
            - WHERE a = const ORDER BY a,d     /*d不是索引的一部分*/
            - WHERE a in(...) ODER BY b,c     /*对于排序来说，多个相等条件也是范围查询*/


    > group by关键字优化
        group by实质是先排序后进行分组，遵照索引建的最佳左前缀。
        当无法使用索引列，增大max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置
        where高于having，能写在where限定的条件就不要去having限定了。

慢查询日志

批量数据脚本

Show Profile

全局查询日志

> 慢查询日志
    默认情况下，mysql 数据库没有开启慢查询日志。
    SHOW VARIABLES LIKE ‘%slow_query_log%’
    开启：set global slow_query_log = 1;  只对当前数据库生效，MySql重启后会失效。如果要永久生效，就必须修改配置文件my.cnf

> 批量数据脚本

> Show Profile
    > 是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。
    > 默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

> 全局查询日志

mysql锁机制

>     读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。
        当前session可以查询该表记录；当前session不能查询其他没有锁定的表；当前session中插入或者更新锁定的表都会提示错误。
        其他session也可以查询该表的记录；其他session可以查询或者更新未锁定的表；其他session插入或者更新锁定表会一直等待获得锁；

    写锁(排他锁)：当前写操作没有完成前，它会阻断其他读锁和写锁。
        当前session对锁定表的查询+更新+插入操作都可以执行
        其他session对锁定表的查询被阻塞，需要等待锁被释放。

> 表锁(偏读)
    > 手动添加表锁：lock table 表名 read(write),表名2 read(write),其他;
    > 查看表上加过的锁：show open tables;
    > 释放表：unlock tables;

    MyISAM在执行查询语句(SELECT)前，会自动给所有涉及的表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
    MySQL的表级锁有两种模式：
        表共享读锁(Table Read Lock)
        表独占写锁(Table Write Lock)
    1.对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作。
    2.对MyISAM表的写操作(加写锁)，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程读写操作。
    简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

> 行锁(偏写)

    更新丢失：版本覆盖
    脏读：事务A读取到了事务B已修改但尚未提交的数据。
    不可重复读：事务A读到了事务B已经提交的修改数据，不符合隔离性。
    幻读：事务A读取到了事务B提交的新增数据。

    脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。 

    > 无索引行锁升级为表锁。

    > 如何锁定一行： select * from 表名 where 条件 for update;


InnoDB和MyISAM的最大不同有两点：一是支持事务，二是采用了行级锁。

主从复制

参考

https://www.bilibili.com/video/BV12b411K7Zu  //尚硅谷mysql教程
https://www.bilibili.com/video/BV1iq4y1u7vj   //尚硅谷 mysql8.0.26 有课件
https://www.cnblogs.com/kerrycode/p/11836647.html
https://zhuanlan.zhihu.com/p/150107974    //大白话讲解脏写、脏读、不可重复读和幻读
https://www.docs4dev.com/docs/zh/mysql/5.7/reference/    //mysql 5.7 中文文档
https://blog.csdn.net/wb1046329430/article/details/114783334    //BLOB 和 TEXT 类型
https://zhuanlan.zhihu.com/p/117476959    //MySQL事务隔离级别和实现原理

学习中的思考

MySQL 基础

一、为什么要学习数据库

二、数据库的相关概念：

三、数据库存储结构的特点：

四、初识MySQL

MySQL产品的介绍

MySQL产品的安装

MySQL安装

MySQL卸载

MySQL服务的启动和停止

MySQL服务端的登录和退出

MySQL常见的命令

MySQL语法规范

五、DQL(Data Query Language)语言的学习

基础查询

空值参与运算

运算符

算数运算符

比较运算符

逻辑运算符

位运算符

条件查询

排序查询

常见函数

单行函数

字符函数

数学函数

日期函数

其他函数

流程控制函数

加密与解密函数

mysql 信息函数

其他函数

分组函数

分组查询

连接查询

sql92 标准

等值连接

非等值连接

自连接

sql99 语法

内连接：inner

外连接

交叉连接：cross

sql99 语法新特性

自然连接

USING 连接

子查询

where 或 haing 后面

标量子查询

列子查询(多行子查询)

行子查询(结果集一行多列或多行多列)

select后面：仅仅支持标量子查询

from 后面

exists后面(相关子查询)

子查询经典案例

分页查询

union 联合查询

SELECT 的执行过程

查询的结构

SELECT 执行顺序

SQL 的执行原理

六、DML语言的学习

插入语句

方式一：经典的插入

方式二：

两种方式大pk

修改语句

修改单表的记录

修改多表的记录(补充)

删除语句

方式一：delete

单表的删除

多表的删除

方式二：truncate (清空)

delete PK truncate (重点)

MySQL8 新特性：计算列

七、DDL语言的学习

标识符命名规则

`MySQL` 基础

四、初识`MySQL`

`MySQL`产品的介绍

`MySQL`产品的安装

`MySQL`安装

`MySQL`卸载

`MySQL`服务的启动和停止

`MySQL`服务端的登录和退出

`MySQL`常见的命令

`MySQL`语法规范

五、`DQL`(Data Query Language)语言的学习

六、`DML`语言的学习

七、`DDL`语言的学习

八、`TCL`语言的学习

三、删除存储过程

四、查看存储过程的信息