一、MySQL基础篇
一、MySQL基础操作
1.卸载
①停止mysql服务
②通过控制面板卸载mysql
③可以考虑删除数据库文件
④删除环境变量
2.MySQL密码
123456
3.导入表
1.使用命令行的方式source 文件的全路径名：source E:\源代码\hotel.sql
2.基于图形化界面的工具
二、DDL语言
1.说明
1.存储过程
从系统架构的层次上看，MySQL 数据库系统从大到小依次是数据库服务器 、 数据库 、 数据表 、数据表的行与列 。
2.数据库的命名规则
①数据库名、表名不得超过30个字符，变量名限制为29个
②只可以包含字母、数字、下划线
③同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
④必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使 用`（着重号）引起
来
⑤保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里也要为整形
2.数据库操作
create、drop、alter、show、use
1.创建数据库：create database [if not exists] 数据库名 [character set 字符集] [collate 排序 规则] ;
charset 字符集：不指定字符集则用默认的utf-8mb4（utf8）字符集。
if not exists：如果数据库已存在则不会创建，也不会报错；如果数据可已经存在则创建成功
2.删除数据库：drop database [ if exists ] 数据库名 ;
3.修改数据库（一般只更改字符集）：alter database 数据库名 character set 字符集；
4.查询所有数据库：show databases ;
5.查询当前数据库：show database() ;
查看当前使用的数据库：select database() [from dual] ;
6.查看创建数据库的结构：show create database 数据库名；
7.切换数据库：use 数据库名 ;
3.表操作
create、desc、show
1.创建表结构：如果创建表时没有指定字符集则默认使用数据库的字符集
方式一：
CREATE TABLE [if not exists] 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;
方式二：基于现有的表来创建新的表--属性以及类型和根据的表一致，同时数据也会被导入。可以将select中查出来的属性会作为新表的属性，如果有数据也会导入，如果select中查询出来的属性使用了别名则新表的属性别名
CREATE TABLE 表名
AS
SELECT employee_id e_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;
练习一：创建表table1，实现对employees表的赋值，包括表数据
create table1
as
select *
from employees;
练习二：不插入原来表的数据，写一个找不到的条件
create table1
as
select *
from employees
where 1=2;
2.查询当前(指定)数据库所有表： show tables [from 数据库]；
3.查看指定表结构：desc 表名;
4.查询指定表的建表语句：show create table 表名 ;
4.数据类型
1.数值型
| 类型 | 字节(byte) | 有符号数范围 | 无符号数范围 | 说明 |
|---|---|---|---|---|
| TINYINT | 1 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 | (-32768，32767) | (0，65535) | 大整数值 |
| MEDIUMINT | 3 | (-8388608，8388607) | (0，16777215) | 大整数值 |
| INT或INTEGER | 4 | (-2147483648，2147483647) | (0，4294967295) | 大整数值 |
| BIGINT | 8 | (-263，263-1) | (0，2^64-1) | 极大整数值 |
| FLOAT | 4 | (-3.402823466 E+38，3.402823466351 E+38) | 0 和 (1.175494351 E-38，3.402823466 E+38) | 单精度浮点数值 |
| DOUBLE | 8 | (-1.7976931348623157 E+308，1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308) | 双精度浮点数值 |
| DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) | |
| BIT | 位类型 |
1.整数
①UNSIGNED：可以在数据类型后面加上 UNSIGNED 关键字来使该数值型为无符号数
age tinyint unsigned comment '年龄;
②M : 表示显示宽度，M的取值范围是(0, 255)，如果数据宽度小于指定宽度则用zerofill填补，如果数据宽度大于M则原样输入。例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。 整型数据类型可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。
id INT(5) ZEROFILL
sno INT(5);
insert into table value(1,1);--00001 _____1（四个空格一个1）
③zerofill：0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。
④int(M)，必须和UNSIGNED ZEROFILL一起使用才有意义。如果整 数值超过M位，就按照实际位数存储。只是无须再用字符 0 进行填充。
⑤使用情景
TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证
券公司衍生产品持仓等。
2.浮点数
①real默认就是 DOUBLE。如果你把 SQL 模式设定为启用“ REAL_AS_FLOAT ”，那 么，MySQL 就认为 REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”
set sql_model='real_as_float';
②MySQL 存储浮点数的格式为： 符号(S) 、 尾数(M) 和 阶码(E) 。因此，无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此， 所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。
③MySQL允许使用非标准语法 （其他数据库未必支持）： FLOAT(M,D) 或 DOUBLE(M,D) 。这里，M称为 精度 ，D称为 标度 。(M,D)中 M=整数位+小数 位，D=小数位。 D<=M<=255，0<=D<=30。如：FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。
④如果存储时，整数部分超出了范围，MySQL就会报错，不允许存这样的值。
如果存储时，小数点部分若超出范围，就分以下情况： 若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。例如在FLOAT(5,2)列内插入999.009，近似结果是999.01。 若四舍五入后，整数部分超出范围，则MySQL报错，并拒绝处理。如FLOAT(5,2)列内插入 999.995和-999.995都会报错。
⑤float与double存在误差，所以我们要避免使用“=”来 判断两个数是否相等；同时，建议使用decimal
3.DECIMAL
①使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。如：定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。
②浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动 力学等） 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景 （比如涉 及金额计算的场景）。开发中常用decimal来保证精确度
4.BIT
①长度：1<=M<=64，不写长度时默认为1。若只有一位长度，而值为2则会报错因为2的二进制而10为两位，1为01可以写成1，因此不报错
CREATE TABLE test_decimal1(
f1 DECIMAL,
f2 DECIMAL(5,2)，
f1 BIT,
f2 BIT(5),
f3 BIT(64)
);
分别以2进制、16进制、10进制显示数据
select bin(num),hex(num),num+0
from 表;
2.字符串型
| 类型 | 范围 | 描述 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535（21845–一个字符占3个字节，所以最大为65535➗3=21845字符） bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16777215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16777215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4294967295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4294967295 bytes | 极大文本数据 |
| ENUM | 1~65535 | 枚举类型（占1或2个字节） |
| SET | 0~64 | 集合类型（占1，2，3，4或8个子杰） |
1.char
①CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
②如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长 度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。因此在用char_length（c）来计算字符个数时，末尾的几个空格不算，如a空格空格，长度为1，空格空格1长度为3
③定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
④仅在存储不大，速度要求高时使用char，否则使用varchar
2.varchar
①VARCHAR(M) 定义时， 必须指定长度M，否则报错。
②MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）；
MySQL5.0版本以上，varchar(20)：指的是20字符。
③检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。
3.TEXT
①存的是什么就是什么，计算长度是不会去掉尾部空格（尾部空格也会算入长度）
②TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR， VARCHAR来代替。
③TEXT类型不用加默认宽度，加了也没用。
④且text和blob类型的数据删除后容易导致 “空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用 一个表。
CREATE TABLE test_text(
tx TEXT
);
INSERT INTO test_text
VALUES('atguigu ');
SELECT CHAR_LENGTH(tx)
FROM test_text; #10
4.ENUM
#在给season添加值时需要从枚举的数据中进行选择（没有限制非空时可以选择null），且仅可以选择一个
CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);
INSERT INTO test_enum
VALUES('春'),('秋'),('春','秋');--最后一个为错误写法，一个枚举数据中不可以添加两个值
# 忽略大小写
INSERT INTO test_enum
VALUES('UNKNOW');
# 允许按照角标的方式获取指定索引位置的枚举值，下标从1开始（可以用字符或数字格式）
INSERT INTO test_enum
VALUES('1'),(3);
# Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('ab');
# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);
5.SET
CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);
INSERT INTO test_set (s) VALUES ('A'), ('A,B');
#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');--ABC
#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
INSERT INTO test_set (s) VALUES ('A,B,C,D');
SELECT *
FROM test_set;
6.BINARY与VATBINARY
①BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
②BINARY(M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M)，表示只能存储1个字节 ，如果字段值不足(M)个字 节，将在右边填充’\0’以补齐指定长度。
③VARBINARY(M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数。
④VARBINARY类型 必须指定(M) ，否则报错。
CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(3),
#f3 VARBINARY,报错，必须指定长度
f4 VARBINARY(10)
);
INSERT INTO test_binary1(f1,f2)
VALUES('a','a');
INSERT INTO test_binary1(f1,f2)
VALUES('尚','尚');#失败
INSERT INTO test_binary1(f2,f4)
VALUES('ab','ab');
7.BLOB
①BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。
②BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如图片 、 音频和视频等。
③在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到MySQL中。
8.JSON
create table test(
js json
);
向表中插入JSON数据
INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing","city":"beijing"}}');
当需要检索JSON类型的字段中数据的某个具体值时，可以使用“属性 -> key”和“->>”符号。其中key为键，如name，来获取他的值songhk。$.address.province为address下的provice
SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province'AS province, js -> '$.address.city' AS city
FROM test_json;
案例
CREATE TABLE temp_mul(
gender ENUM('男','女'),
hobby SET('吃饭','睡觉','打豆豆','写代码')
);
INSERT INTO temp_mul VALUES('男','睡觉,打豆豆'); #成功
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('男,女','睡觉,写代码'); #失败
# Data truncated for column 'gender' at row 1
INSERT INTO temp_mul VALUES('妖','睡觉,写代码');#失败
INSERT INTO temp_mul VALUES('男','睡觉,写代码,吃饭'); #成功
3.日期类型
| 类型 | 字节 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:01 UTC至 2038-01-19 03:14:07 UTC | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
1.YEAR
①以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
②以2位字符串格式表示YEAR类型，最小值为00，最大值为99。
当取值为01到69时，表示2001到2069；
当取值为70到99时，表示1970到1999；
当取值整数的0或00添加的话，那么是0000年；
当取值是日期/字符串的’0’添加的话，是2000年。
③从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”，没必要写成YEAR(4)
从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。
定义时加不加宽度都可以
create table text(
f1 year,
f1 year(4)
);
加不加单引号都可以，建议加上
insert into text(f1,f2)
values(2022,'2022'),('69','70'),(0,'00');--2022 2022 2069 1970 0000 2000
2.DATA
①以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值为1000-01-01，最大取值为 9999-12-03，YYYYMMDD格式会被转化为YYYY-MM-DD格式。
②以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足 YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99 时，会被转化为1970到1999。
③使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期。
CREATE TABLE test_date1(
f1 DATE
);
INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);
INSERT INTO test_date1
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'),
('99-01-01'), ('990101'),(now());
3.TIME
①可以使用带有冒号的字符串，比如’ D HH:MM:SS’ 、’ HH:MM:SS ‘、’ HH:MM ‘、’ D HH:MM （表示时和分）‘、’ D HH ‘或’ SS '（表示秒）格式，都能被正确地插入TIME类型的字段中。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10。
②可以使用不带有冒号的字符串或者数字，格式为’ HHMMSS '或者 HHMMSS 。如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示 00:12:10，而不是12:10:00。
③带’：‘不写全最左边表示小时；不带’：'不写全最右边表示秒；D HH表示小时；ss表示秒
CREATE TABLE test_time1(
f1 TIME
);
INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
60:30:29 12:35:29 12:40:00 60:40:00 29:00:00 00:00:45
INSERT INTO test_time1
VALUES ('123520'), (124011),(1210);
12:35:20 12:40:11 00:12:10
INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME());
SELECT * FROM test_time1;
4.DATATIME
①以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时， 最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
②以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式。
③使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME类型的字段插入系统的当前日期和时间。
CREATE TABLE test_datetime1(
dt DATETIME
);
INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');
INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'),
('200101000000');
INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000)
5.TIMESTAMP
①向TIMESTAMP类型的字段插入数据时，当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS 时，两位数值的年份同样符合YEAR类型的规则条件，只不过表示的时间范围要小很多。 如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围，则MySQL会抛出错误信息。
②存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。
③可以用@来代替：和中间的空格
CREATE TABLE test_timestamp1(
ts TIMESTAMP
);
INSERT INTO test_timestamp1
VALUES ('2020:01:01 00:00:00'), ('20:01:01 00:00:00');
INSERT INTO test_timestamp1
VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');
INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());
#Incorrect datetime value
INSERT INTO test_timestamp1
VALUES ('2038-01-20 03:14:07');--范围超了
6.DATATIEM和TIMESTAMP的对比
①TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
②底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
③两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
④TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。
CREATE TABLE temp_time(
d1 DATETIME,
d2 TIMESTAMP
);
INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52');
INSERT INTO temp_time VALUES(NOW(),NOW());
| d1 | d2 |
| 2021-09-02 14:45:52 | 2021-09-02 14:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 17:38:17 |
#修改当前的时区
SET time_zone = '+9:00';
SELECT * FROM temp_time
| d1 | d2 |
| 2021-09-02 14:45:52 | 2021-09-02 15:45:52 |
| 2021-11-03 17:38:17 | 2021-11-03 18:38:17 |
用得最多的日期时间类型，就是 DATETIME 。一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用 时间戳 ，因为 DATETIME虽然直观，但不便于计算。
#时间戳
SELECT UNIX_TIMESTAMP();
| UNIX_TIMESTAMP() |
| 1635932762 |
4.指定字符集character set 字符集
①创建数据可时指明字符集
②创建表的时候指明字符集
③创建表时可以指定字段的字符集
create table 表名(id int unsigned,
name varchar(50) character set 'gbk'
) character set 'utf8';
5.总结
①整形：int 小数：decimal（m，d） 日期与时间：datetime
②任何字段如果为非负数，必须是 UNSIGNED
③小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并 分开存储。
④如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
⑤VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
5.修改表结构的操作
1.添加字段：ALTER TABLE 表名 ADD 字段名 类型（长度） [first/after 指定属性] [comment ‘注释’] [约束]；
first/after 指定属性：将该属性放在第一个位置或指定属性后面
2.修改数据类型：ALTER TABLE 表名 MODIFY （新）字段名 新的数据类型（长度）；
一般只改长度
3.修改属性名和属性类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新字段数据类型（长度） [注释] [约束]；
4.删除字段：ALTER TABLE 表名 DROP 字段名；
5.修改表名：ALTER TABLE 表名 RENAME TO 新表名；
6.表删除操作
1.删除表：DROP TABLE [IF EXISTS] 表名 [CASCADE/RESTRICT]；
2.清空表：TRUNCATE TABLE 表名；删除指定表, 并重新创建表（表中没有数据了，但有表结构）
DELETE FROM 表名；
相同点：都可以实现对表中所有数据的删除，同时保留表结构
不同点：①TRUNCATE TABLE：一旦执行此操作，表数据会全部清除，数据不可以回滚
②TRUNCATE TABLE：一旦执行此操作，表数据会全部清除，也可以加where条件实现部分清除，数据可以回滚
7.DDL与DML
①DDL的操作一旦执行，就不可回滚，SET autocommit = FALSE对DDL无效，因为在执行完DDL之后会自动执行commit，且不收SET autocommit = FALSE影响
②DML的操作默认情况下一旦执行也是不可以回滚的，但是如果在执行DML之前执行了SET autocommit = FALSE，则执行的DML操作就可以实现回滚
演示：
commit;
set autocommit=false;
delete from emp3;
rollback;
commit;
set autocommit=false;
truncate table emp3;
rollback;
三、DML语言
（insert、delete、update ）
DML执行完默认提交数据，如果不想提交数据则可以使用SET autocommit = FALSE
1.添加操作
1.给指定字段添加数据：INSERT INTO 表名（字段名1，字段名2，…） VALUES（值1，值2，…）;
2.给全部字段添加数据：INSERT INTO 表名 VALUES（值1，值2，…）;
3.批量添加数：INSERT INTO 表名（字段名1，字段名2，…） VALUES（值1，值2，…），（值1，值2，…），…；
INSERT INTO 表名 VALUES（值1，值2，…），（值1，值2，…），…；
4.插入子查询结果：INSERT INTO 表名（字段名） 子查询；–其中子查询的返回结果与字段名对应且顺序一致
注：①在使用指定字段添加数据时未写值得列未空值
②主码必须有值，不能为空
③插入数据时，指定的字段顺序需要与值的顺序是一一对应的
④字符串和日期型数据应该包含在单引号中
⑤插入的数据大小，应该在字段的规定范围内
⑥被添加表中字段的长度要大于等于数据来源表中字段的长度，如果小了就有添加不成功的风险
举例：
insert into itcase.employee(id, workno, name, gender, age, idcard, entrydate)
VALUES (1, '1', 'itcast', 1, 9, '213123', '1999-01-01');
insert into itcase.employee value (2, '2', '张无忌', '0', '10', '123123', '1999-2-2');
insert into itcase.employee(id, workno, name, gender, age, idcard, entrydate)
VALUES (3, '3', 'itcast3', 1, 9, '213123', '1999-01-01'), (4, '4', 'itcast4', 1, 9, '213123', '1999-01-01');
2.修改操作
1.修改数据：UPDATA 表名 SET 字段1=值1，字段2=值2，…[WHERE 条件]；
2.带子查询的修改操作：
将计算机全体学生的成绩置为0,年龄加1
UPDATA SC SET grade=0 , age=age+1 WHERE sno IN(SELECT sno,from student where sdept='CS');
注：①如果不加条件，修改的则是表中所有的数据
②在修改时系统会自动检查是否破坏了表的完整性约束，如student表中sno发生了修改则sc表中得sno也需要进行更改
③删除某一个字段的值使用UPDATA把其值设为null即可
④由于约束、语法错误等问题可能会使数据添加、删除不成功
举例：
update itcase.employee set name='itheima' where id=1;
update itcase.employee set name='小昭',gender='0' where id=1;
update itcase.employee set entrydate='2008-1-1';
3.删除操作
1.删除数据：DELETE FROM 表名 [WHERE 条件]；
注：①WHERE条件可以有也可以没有，如果没有则删除整张表的所有数据
②DELETE不可以删除某一个字段的值，如果想删除某一个字段的值使用UPDATA把其值设为null即可
1.delete from itcase.employee where gender='0';
2.delete from itcase.employee;
3.delete from sc
where sno in(select sno from student where sdept='cs');
4.MySQL8新特性：计算列
1.定义
某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列
2.使用
CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。
举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。
CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT generated always AS (a + b) virtual
);
在使用updata更新时c会自动更新(如b=200)
UPDATE tb1 SET a = 500;
c=700
5.综合案例
#指定使用哪个数据库
USE test01_library;
#2、创建表 books
CREATE TABLE books(
id INT,
name VARCHAR(50),
`authors` VARCHAR(100) ,
price FLOAT,
pubdate YEAR ,
note VARCHAR(100),
num INT
);
#3、向books表中插入记录
# 1）不指定字段名称，插入第一条记录
INSERT INTO books
VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11);
# 2）指定所有字段名称，插入第二记录
INSERT INTO books (id,name,`authors`,price,pubdate,note,num)
VALUES(2,'EmmaT','Jane lura',35,1993,'Joke',22);
# 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books (id,name,`authors`,price,pubdate,note,num) VALUES
(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'Law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);
# 4、将小说类型(novel)的书的价格都增加5。
UPDATE books SET price=price+5 WHERE note = 'novel';
# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE books SET price=40,note='drama' WHERE name='EmmaT';
# 6、删除库存为0的记录。
DELETE FROM books WHERE num=0;
# 7、统计书名中包含a字母的书
SELECT * FROM books WHERE name LIKE '%a%';
# 8、统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*),SUM(num) FROM books WHERE name LIKE '%a%';
# 9、找出“novel”类型的书，按照价格降序排列
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC;
# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT * FROM books ORDER BY num DESC,note ASC;
# 11、按照note分类统计书的数量
SELECT note,COUNT(*) FROM books GROUP BY note;
# 12、按照note分类统计书的库存量，显示库存量超过30本的
北京宏福校区：010-56253825 深圳西部硅谷校区：0755-23060254 上海大江商厦校区：021-57652717
SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)>30;
# 13、查询所有图书，每页显示5本，显示第二页
SELECT * FROM books LIMIT 5,5;
# 14、按照note分类统计书的库存量，显示库存量最多的
SELECT note,SUM(num) sum_num FROM books GROUP BY note ORDER BY sum_num DESC LIMIT 0,1;
# 15、查询书名达到10个字符的书，不包括里面的空格
SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(name,' ',''))>=10;
/*
16、查询书名和类型，
其中note值为 novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
*/
SELECT name AS "书名" ,note, CASE note
WHEN 'novel' THEN '小说'
WHEN 'law' THEN '法律'
WHEN 'medicine' THEN '医药'
WHEN 'cartoon' THEN '卡通'
WHEN 'joke' THEN '笑话'
END AS "类型"
FROM books;
# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
SELECT name,num,CASE
WHEN num>30 THEN '滞销'
WHEN num>0 AND num<10 THEN '畅销'
WHEN num=0 THEN '无货'
ELSE '正常'
END AS "库存状态"
FROM books;
# 18、统计每一种note的库存量，并合计总量
SELECT IFNULL(note,'合计总库存量') AS note,SUM(num) FROM books GROUP BY note WITH
ROLLUP;
# 19、统计每一种note的数量，并合计总量
SELECT IFNULL(note,'合计总数') AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP;
# 20、统计库存量前三名的图书
SELECT * FROM books ORDER BY num DESC LIMIT 0,3;
# 21、找出最早出版的一本书
SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1;
# 22、找出novel中价格最高的一本书
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC LIMIT 0,1;
# 23、找出书名中字数最多的一本书，不含空格
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC LIMIT 0,1;
# 24、将userid为Bbiri的users表和my employees表的记录全部删除
delete m,u
from my_employees m
join users u
on m.userid = u.userid
where m.userid='Bbiri';
# 25、插入数据
insert into my_employees
select 1,'abc','abc','abc',895,union all
select 2,'abc2','abc2','abc2',8952
四、DQL语言
1.基本语法
sql92书写顺序:
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
sql99书写顺序:
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
执行顺序:
（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积,left/right join
（3）where：从表中筛选的条件
（4）group by：对where筛选出来的数据进行分组依据,因此先where效率高，条件全写在having中效率低
（5）having：在统计结果中再次筛选
（6）select--distinct
（7）order by：排序
（8）limit：分页
2.基础查询
1.字段别名设置：SELECT 字段1 AS 别名1，字段2 AS 别名2 FROM 表名；
AS可以省略
注：别名如果中间有空格要用双引号把关键字引起来
2.取出重复行：SELECT DISTINCE 字段名1，字段名2 FROM 表名；
select name,workno,age
from emp;
/*尽量不要写* */
select *
from emp;
select workno as '工作地址'
from emp;
select distinct workno '工作地址'
from emp;
3.练习
select name,workno,age
from emp;
/*尽量不要写* */
select *
from emp;
select workno as '工作地址'
from emp;
select distinct workno '工作地址'
from emp;
/*字符串：名字和属性会原样输出，起上别名别名会之影响名字那一行。算数表达式则：名字原样输出。属性为计算结果*/
select name,2014-age,'2014-age' birth,LOWER(sdept)
from student;
3.条件查询
| 运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| =、<=> | 等于、安全等于（用于null的比较，可以判断一个数据是不是null，是返回1不是返回0） |
| <>或！= | 不等于 |
| （NOT）BETWEEN…AND… | （不）在某个范围之内(含最小、最大值 |
| （NOT）IN(…) | （不）在in之后的列表中的值，多选一 |
| （NOT）LIKE | 模糊匹配(_匹配单个字符, %匹配任意个字符 |
| IS （NOT） NULL | 是NULL |
| AND或&& | 并且 (多个条件同时成立) and优先级高于or |
| OR或|| | 或者 (多个条件任意一个成立 |
| NOT或！ | 非 , 不是 |
| XOR | 异或(不同为1相同为0)–a xor b–满足a且不满足b或满足b且不满足a |
| +、- | 整数加减浮点数为浮点数、整数加减字符串–将字符串转为相应设置并相加、整数加非数字字符串把字符串当0，即用0和该类字符串比较时返回1（true）,0=‘a’–1 |
| *、/或div | 除法结果为浮点型、任何数除以0为null |
| %或mod | 正负号取决于被模数 |
| least(value1，value2…) | 返回value中最小的–SELECT LEAST(‘A’,‘C’,‘T’) ,LEAST(first+name,last+name)FROM employee;–B 每条数据中字符串比较小的 |
| greatest(value1,value2…) | 返回value中最大的SELECT GREATEST(‘A’,‘C’,‘T’) FROM DUAL;–T |
| regesp | 判断一个值是否符号正则表达式的规则 |
| rike | 判断一个值是否符号正则表达式的规则 |
1.语法：SELECT 字段列表 FROM 表名 WHERE 条件列表；
2.EXCAPE ‘字符’：用来告诉编译器该字符后的字符为普通字符，不是通配符。\本身表示转义，不用加excape，如果要用别的字符充当转移字符需要用excape来声明
3.null不等于0、‘ ’、‘null’；
4.null参与算数运算时结果一定也为null，可以用if null（属性，0）–如果属性是null则用0替换
select employee_id, last_name, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS "annual salary"
from employees;
null=null--null
5.着重号：`–如果某一个字段名或表名与关键字一样，可以用着重号括起来，普通字段也可以加不会报错。
select * from ` order`;
6.如果一个字符串与数字对应，则其在运算时相当于该数字；如果字符串不能转换为数字，则在算数运算时当作0；如果两个字符串之间比较按字符串的ASCII来比
1='1';--1
0='ab';--1
'a'='a'--1
'a'='b'-0
7.where删选的是条件为1的语句
8.正则表达式
^:匹配以该字符后面的字符开头的字符串
$:匹配以该字符前面的字符结尾的字符串
.:匹配任何一个单字符
[...]:匹配在方块内的任何字符。[abc]匹配a、b或c，[a-d]匹配a到b的任何字符，[0-9]匹配0到9的任何字符
*:匹配0个或多个在他前面的字符。如：a*--匹配任意数量的a;[0-9]*--匹配任意数量的数组
以a开头，m结尾，包含ml，包含m任意字符k,包含a或包含b，每一个都返回1
select 'amlkascm' regexp '^m','amlkascm' regexp 'c$','amlkascm' regexp 'ml'，amlkascm' regexp 'm.k'
amlkascm' regexp '[ab]'
from dual;
9.练习
A. 查询年龄等于 88 的员工
select * from emp where age=88;
B. 查询年龄小于 20 的员工信息
select * from emp where age < 20;
C. 查询年龄小于等于 20 的员工信息
select * from emp where age <= 20;
D. 查询没有身份证号的员工信息
select * from emp where idcard is null;
E. 查询有身份证号的员工信息
select * from emp where idcard is not null
F. 查询年龄不等于 88 的员工信息
select * from emp where age != 88;
select * from emp where age <> 88;
G. 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from emp where age between 15 and 20;
select * from emp where age > 15 and age < 20;
select * from emp where age > 15 && age < 20;
H. 查询性别为 女 且年龄小于 25 岁的员工信息
select * from emp where gender='女' and age < 25
I. 查询年龄等于18 或 20 或 40 的员工信息
select * from emp where age=18 or age = 20 or age=40;
select * from emp where age in (18,20,40);
J. 查询姓名为两个字的员工信息 _ %
select * from emp where name like '__';
K. 查询身份证号最后一位是X的员工信息
select * from emp where idcard like '_________________X';
L. 查询DB_Design课程的课程号和学分
select cno,credit from course where cname like 'DB\_Design' excape '\';
M. 查询"DB_"开头且倒数第三个字符为i的课程
select * from course where cname like 'DB\_%__' excape '\';
N.commission_pctb不为null的员工的数据(NOT加在where后，表示commission_pct <=> null是null返回1，然后not就是0即是null就是0不是null就是1，where删选是的条件为1的，所以为非空返回)
select *from employeeswhere not commission_pct <=> null;
1.选择工资不在5000到12000的员工的姓名和工资
select last_name, salary
from employees
where salary not between 5000 and 12000;
2.选择在20或50号部门工作的员工姓名和部门号
select last_name, department_id
from employees
where department_id in (20, 50);
3.选择公司中没有管理者的员工姓名和job_id
select last_name, job_id
from employees
where manager_id is null;
4.选择公司中有奖金的员工姓名，工资和奖金级别
select last_name, salary, commission_pct
from employees
where commission_pct is not null;
5.选择员工姓名的第三个字母是a的员工姓名
select last_name
from employees
where last_name like '__a%';
6.选择字母中有a和k的员工
select *
from employees
where last_name like '%a%k%'
or last_name like '%k%a%';
# where last_name like '%a%'and last_name like '%k%';
7.显示出表employees表中first_name以e结尾的员工信息
select *
from employees
where first_name regexp 'e$';
8.显示出表employees部门编号在80-100之间的姓名、工种
select *
from employees
where department_id between 80 and 100;
9.显示出表的manager_id是100，101，110的员工姓名、工资、管理者id
select last_name,salary,manager_id
from employees
where manager_id in (100,101,110);
4.排序与分页操作
1.排序
1.排序：SELECT * FROM 表 WHERE 条件 ORDER BY 字段1 排序方式1，字段2 排序方式2，别名 排序方式3；
注：①如果没有排序则查询出来的数据是按照添加的顺序显示的
②升序操作：ASC（默认）；降序操作：DESC
③可以使用别名来排序（列别名只能在ORDER BY中使用，不能在WHERE中使用）。因为sql语句不是顺序执行的，会先执行from、where在执行select、order by
④多级排序：如果有多组排序方式则会先按第一组排，相同的按第二组排，以此类推
SELECT * FROM emp ORDER BY salary DESC;
SELECT salary*12 yearsalary FROM emp ORDER BY yearsalary DESC;
2.分页
LIMIT使用在MySQL、PGSQL、MariaDB、SQLlite中
1.分页：SELECT * FROM 表 LIMIT 起始索引，查询记录数；
注：①想要显示第几页的数据：起始索引从0开始（想要显示第32条数据就写31–偏移量=要显示的数据-1），起始索引 = （查询页码 - 1）* 每页显示记录数。
②声明顺序如下：where order by limit
③偏移量从0开始可以省略
select employee_id,last_name,salary
from employees
where salary>6000
order by salary desc
limit 10;
#limit 0,10;
显示第31，32条数据
select *
from employees
limit 30,2;
2.分页8.0新特性：limit 查询记录条数 offset 起始索引
3.章节练习
1.查询员工的姓名、部门号和年薪，按年薪降序，姓名升序显示
select last_name, department_id, salary * 12 'annual salary'
from employees
order by `annual salary` desc, last_name;
2.选择工资不在8000到17000的员工的姓名和工资，按工资降序，显示第21到40位置的数据
select last_name, salary
from employees
where salary not between 8000 and 17000
order by salary desc
limit 20,20;
3.查询邮箱中包含e的员工信息，先按邮箱字节数降序，再按部门号升序
select *
from employees
where email like '%e%'
or email like 'e%'
or email like '%e'
order by length(email) desc, department_id;
select *
from employees
where email regexp '[e]'
order by length(email) desc, department_id;
5.聚合函数
1.聚合函数
1.聚合函数是作用域某一列的：如sum（某一列）–avg（grade）
2.语法：SELECT 聚合函数(字段列表) FROM 表名 WHERE 条件;
3.常见聚合函数
mysql中聚合函数不可以嵌套（聚合里套聚合）
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值（适用于数值类型、字符串类型、日期时间类型） |
| min | 最小值（适用于数值类型、字符串类型、日期时间类型） |
| avg | 平均值（只适用于数值型数据） |
| sum | 求和（只适用于数值型数据） |
4.所有的null值不参与聚合函数的运算
5.聚合函数只能用于：select、group by、having子句中
6.count（2*salary）–也是工资的数量，并不会收到×2的影响
count（n）–表中有多少条记录，n可为任何数字、*、具体字段。
innoDB引擎下的执行效率：count（*）=count（1）> count（字段）
7.avg=sum/count，如avg（salary）=sum（salary）/count（salary）
A. 统计该企业员工数量对于count聚合函数，统计符合条件的总记录数，还可以通过 count(数字/字符串)的形式进行统计
查询。
select count(*) from emp; -- 统计的是总记录数
select count(idcard) from emp; -- 统计的是idcard字段不为null的记录数
B. 统计该企业员工的平均年龄
1 select avg(age) from emp;
C. 统计该企业员工的最大年龄
select max(age) from emp;
D. 统计该企业员工的最小年龄
select min(age) from emp;
E. 统计西安地区员工的年龄之和
select sum(age) from emp where workaddress='西安';
F.查询公司的平均奖金率(不能只计算非null，要把null换成0算进去)
select sum(commission_pct)/count(ifnull(commission_pct,0))或avg(ifnull(commission_pct,0))
from dual;
2.分组查询
1.group by：按照某个字段分组，把这一组看作一个表，使用聚合函数时相当组在每组中进行统计
二级分组：先对一个元组进行分组，再对这个已经分好了的组中的某个属性进行再次分组
注：select中出现的非聚合函数的字段一定要写在group by中，group by中出现的字段不一定要写在select中
1.求各个课程号及相应的选课人数
select cno,count(sno)
from sc
group by cno;
输出
cno count(sno)
1 22
2 34
3 44
4 33
5 48
6 23
7 26
2.查询各个部门下各个工种的平均工资(先对部门进行分组，再对每一个部门内工种进行分组，并计算每一个工种的平均工资)
select department_id,job_id,avg(salary)
from employees
group by departmeng_id,job_id;
效果一样，知识先分工种，再分部门
select department_id,job_id,avg(salary)
from employees
group by job_id,departmeng_id;
2.新特性–with rollup：在group by中使用with rollup：使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
注意： 当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
select departmrnt_id,avg(salary)
from employees
WHERE department_id > 80
group by department_id with rollup
3.having的使用
1.having：用于筛选分组中满足条件的元组，如果过滤条件中出现了聚合函数则不使用where要使用having
2.where与having的对比：①在基本表或视图中筛选元组
②having使用范围更广
③如果过滤条件没有聚合函数，where的执行效率高
注：①having写在group by后边
②不使用group by也可以使用having，但是意义和含义就会发生改变；因此，要求having要和group by一起使用，不能单独使用
③过滤条件中有聚合函数时必须声明在having中；过滤条件中没有聚合函数时，建议声明在where中
1.查询选修了三门以上课程的学生学号
select sno
from sc
group by sno
having count(*)>3;
2.查询平均成绩大于等于90分的学生学号和平均成绩
select sno,avg(grade)
from sc
group by sno
having avg(grade)>=90;
3.查询各个部门中最高工资比10000高的部门信息
select department_id,max(salary)
from departments
group by department_id
having max(salary)>10000;
4.查询各个部门id为10，20，30，40四个部门中最高工资比10000高的部门信息
方式一:非聚合函数条件写在where中(推荐:执行效率高)
select department_id,max(salary)
from departments
where department_id in (10,20,30,40)
group by department_id
having max(salary)>10000;
方式二:条件全写在having中
select department_id,max(salary)
from departments
where department_id in (10,20,30,40)
group by department_id
having max(salary)>10000 and department_id in (10,20,30,40);
4.课后练习
1.查询公司员工工资的最大值，最小值，平均值，总和
select max(salary),min(salary),avg(salary),sum(salary)
from employees;
2.查询各 job_id 的员工工资的最大值，最小值，平均值，总和，并按 job_id 升序
select job_id,max(salary),min(salary),avg(salary),sum(salary)
from employees
group by job_id
order by job_id;
3.选择具有各个 job_id 的员工人数
select job_id,count(*)
from employees
group by job_id;
4.查询员工最高工资和最低工资的差距（DIFFERENCE）
select max(salary)-min(salary) 'DIFFERENCE'
from employees;
5.查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id;
having min(salary)>=6000;
6.查询所有部门名称，location_id,员工数量和工资平均值,并按平均工资降序
select d.department_name,d.location_id,count(enployee_id),avg(salary) 'as'
from departments d right join employees e
on d.department_id = e.department_id
group by department_name,location_id;
注：由于select中有的非聚合函数group by中也要有因此直接赋值过去，随后查询所有部门因此用到外连接
7.查询每个工种、每个部门的部门名、工种名和最低工资
select e.job_id,d.depatemnt_name,min(salary)
from employees e right join departments d
on j.department_id = e.department_id
group by job_id,depatemnt_name;
6.多表查询
1.多表查询的分类
①等值连接与非等值连接
②自然连接与非自然连接
③内连接与外连接
注：超过三个表，尽量不用join
2.内连接
内连接：把两个表中都存在（非空）的数据查询出来（只挑选满足匹配条件的）
1.等值连接
1.等值连接：在笛卡尔积中选择某些属性值相等的元组
2.笛卡尔积（交叉连接）：第一张表中的每一个数据与另一张表的的每个数据都匹配一次（多个表中的每条记录都相互匹配一遍）
3.笛卡尔积错误可能产生的原因：①省略或缺失多个表的连接条件
②连接条件失效
③所有表中的所有行相互连接
笛卡尔积
select employee_id,department_id
from employees,department;
交叉连接
select employee_id,department_id
from employees cross join department;
4.多表查询：需要有连接条件，两个表中有重复的属性且相等
select employee_id,department_name
from employees,departments
where employees.department_id = departments.department_id;
sql99中使用join on的方式来实现多表查询来实现外连接，支持mysql
//sql99内连接(inner可以省略)join
两张表的多表查询
select student,sno,sname,ssex,sage,sdept,cno,grade--没有重复的属性，学号列只有一个作为列显示
from student s (inner)join sc c
on s.sno=c.sno;
三张表的多表查询
select t1.student , t1.sno , t1,sname , t3.cname,t2.grade
from student t1 (inner)join sc t2
on t1.sno = t3.sno
join course t3
on st3.cno = t2.cno;
也可以这样写，但是不建议用
select t1.student , t1.sno , t1,sname , t3.cname,t2.grade
from student t1 (inner)join sc t2 join course t3
on t1.sno = t3.sno
adn st3.cno = t2.cno;
注：①如果其中一个表中的连接条件（department_id）为null，则该条数据不会出现在结果集中
②如果查询语句中出现了两个表中都存在的属性，则该属性需要指定属于哪个表
③可以在from中给表起别名，在select与where中使用表的别名,一旦起了别名后在select与where中使用就不可以用表名必须用别名
④如果有n个表实现多表连接至少需要n-1个连接条件
select t1.employee_id,t2.department_name,t1.department_id
from employees t1,departments t2
where t1.department_id = t2.department_id;
select t1.student , t1.sno , t1,sname , t3.cname,t2.grade
from student t1 , sc , t2,course t3
where t1.sno = t3.sno and t3.cno = t2.cno;
select student.*,sc.*--有重复的属性，如两个表中都有学号，因此有两个学号列
from student,sc
where student.sno=sc.sno;
2.非等值连接
非等值连接，用工资连接，工资在最低工资和最高工资之间，连接得到最高与最低范围内 相应的等级
select last_name, salary, grade_level
from employees e,
job_grades j
# where e.salary between lowest_sal and highest_sal;
where e.salary >= lowest_sal
and e.salary <= highest_sal;
3.自然连接
1.自然连接：将等值连接查询出来的结果中重复的列去掉
在sql99中的自然连接：自动把两张表中所有相同的属性进行查询，进行等值连接，但是不够灵活
select student,sno,sname,ssex,sage,sdept,cno,grade--没有重复的属性，学号列只有一个作为列显示
from student,sc
where student.sno=sc.sno;
如表中两张表中有共同的属性department_id与manager_id
sql99
select employee_id,last_name,department_name
from employees e natural join departments d;
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;
4.自身连接
1.自身连接：一张表当两张表来用，第二张表的某个属性是第一张表中的属性
查询员工id，员工姓名及其管理者的id和姓名。
因为本表中没有专门设置管理员姓名的属性，管理员姓名即为对应管理员id员工的id的姓名，因此要用自身连接
e1当作员工表，e2当作管理员表，其实都是一张表，连接条件：e1的管理员id肯定是d2员工id。
select e1.employee_id, e1.last_name, e2.employee_id, e2.last_name
from employees e1,
employees e2
where e1.manager_id = e2.employee_id;
3.外连接
把某一属性所有的数据差距来，就用外连接
外连接：除了把两个表中都存在（非空）的数据（只挑选满足匹配条件的）查询出来之外，还把左表或右表中不匹配的行查询出来，空数据用null填充
1.左外连接：查出左表中的全部数据，右表中没有值，在结果集中用null表示
sql92:在右侧加一个(+)，但是在mysql中不支持
select student,sno,sname,ssex,sage,sdept,cno,grade--没有重复的属性，学号列只有一个作为列显示
from student,sc
where student.sno=sc.sno(+);
sql99:左外连接left (outer) join
select student,sno,sname,ssex,sage,sdept,cno,grade--没有重复的属性，学号列只有一个作为列显示
from student s left (outer) join sc c
on s.sno=c.sno;
2.右外连接：查出右表中的全部数据，左表中没有值，在结果集中用null表示
sql92:在左侧加一个(+)，但是在mysql中不支持
select student,sno,sname,ssex,sage,sdept,cno,grade--没有重复的属性，学号列只有一个作为列显示
from student,sc
where student.sno(+)=sc.sno;
sql99右外连接:right (outer) join
select student,sno,sname,ssex,sage,sdept,cno,grade--没有重复的属性，学号列只有一个作为列显示
from student s right (outer) join sc c
on s.sno=c.sno;
3.满外连接：查出左、右表中的全部数据，左或右表中没有值，在结果集中用null表示
sql99满外连接:full (outer) join--mysql不支持
select student,sno,sname,ssex,sage,sdept,cno,grade--没有重复的属性，学号列只有一个作为列显示
from student s full (outer) join sc c
on s.sno=c.sno;
4.集合操作
列一样，列的数量也要一样
1.合并查询(union)：A和B种重复的数只算一份
union all：A和B种重复的再算一份
在这两个都可以用的情况下，尽量用union all，因为union all效率高
查询计算机系学生及年龄不大于19岁的学生
select *
from student
where sdept='cs'
union 使用的是union所以如果查询计算机系学生与年龄大于大19岁学生的两次结果种有重复的学生，只显示一个在表中
select *
from student
where sage<=19;
2.交集操作(intersect)：
查询计算机系学生与年龄不大于19岁学生的交集(既是计算机系学生年龄又小于19岁)
select *
from student
where sdept='cs'
intersect
select *
from student
where sage<=19;
3.差集操作(except)：
查询计算机系学生与年龄不大于19岁的学生的差集(是计算机系学生且年龄大于19岁)
select *
from student
where sdept='cs'
except
select *
from studnet
where sage<=19;
5.7种sql joins的实现
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XSx0D2yK-1660187025949)(E:\学习笔记\mysql\mysql-1.png)]
1.中图–内连接：
select employee_id,depatment_name
from employees e join departments d
on e.department_id = d.department_id;
2.左上图–左外连接：
select employee_id,depatment_name
from employees e left join departments d
on e.department_id = d.department_id;
3.右上图–右外连接
select employee_id,depatment_name
from employees e right join departments d
on e.department_id = d.department_id;
4.左中图–在左上图的基础上去掉重复数据
找右表的连接数据是null的
select employee_id,depatment_name
from employees e right join departments d
on e.department_id = d.department_id
where d.department_id is null;
5.右中图–在右上图的基础上去掉重复数据
找左表的连接数据是null的
select employee_id,depatment_name
from employees e right join departments d
on e.department_id = d.department_id
where e.department_id is null;
6.左下图–满外连接
方式一：左上图 union 右中图
select employee_id,depatment_name
from employees e left join departments d
on e.department_id = d.department_id
union all
select employee_id,depatment_name
from employees e right join departments d
on e.department_id = d.department_id
where e.department_id is null;
方式二：左中图 union 右上图
select employee_id,depatment_name
from employees e right join departments d
on e.department_id = d.department_id
where d.department_id is null
union all
select employee_id,depatment_name
from employees e right join departments d
on e.department_id = d.department_id;
7.右下图
左中图 union 右中图
select employee_id,depatment_name
from employees e right join departments d
on e.department_id = d.department_id
where d.department_id is null
union all
select employee_id,depatment_name
from employees e right join departments d
on e.department_id = d.department_id
where e.department_id is null;
6.using连接
1.using连接：连接时在using的括号中写入两各表中用来连接的相同的字段，代替连接条件。因为只能写入相同字段，因此不适用于自身连接
select employee_id,last_name,department_name
from employees e join departments d
using (department_id)l
7.练习
1.显示所有员工的姓名、部门号和部门名称(所有--外连接)
select e.last_name, e.department_id, d.department_name
from employees e
left join departments d
on e.department_id = d.department_id;
2.查询90号部门员工的job_id和90号员工的location_id
select e.job_id, d.location_id
from departments d
join employees e
on d.department_id = e.department_id
where d.department_id = 90;
3.选择所有有奖金的员工的last_name,department_name,location_id,city(所有--外连接)
select e.last_name, e.commission_pct, d.department_name, d.location_id, l.city
from departments d
right join employees e on d.department_id = e.department_id
left join locations l on l.location_id = d.location_id
where e.commission_pct is not null;
4.选择city在toronto工作的员工的last_name,job_id,department_id,department_name(虽然要查询的数据都在前面两张表中，但是筛选条件在第三张表中，因此要用三张表来进行多表查询)
select e.last_name, e.job_id, d.department_id, d.department_name
from employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.department_id = l.location_id
where l.city = 'Toronto';
5.查询员工所在部门名称，部门地址、姓名、工作、工资，其中员工所在部门名称为'Acc'
select d.department_name, l.street_address, e.last_name, e.job_id, e.salary
from employees e right join departments d on e.department_id = d.department_id
left join locations l on d.location_id = l.location_id
where d.department_name = 'Acc';
6.选择指定员工的姓名，员工号以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# Manager Mgr#
kochhar 101 king 100
select e1.last_name 'employees', e1.employee_id 'Emp#', e2.last_name 'Manager', e2.employee_id 'Mgr'
from employees e1 left join employees e2 on e1.manager_id = e2.employee_id;
7.查询哪些部门没有员工(先把所有的部门查出来(需要外连接)，然后加上员工为null的条件)
select d.department_id
from employees e right join departments d on e.department_id = d.department_id
where e.employee_id is null;
8.查询哪个城市没有部门
select location_id,city
from locations l left join departments d on l.location_id = d.location_id
where d.department_id is null;
9.查询部门名为sales或it的员工信息
select e.employee_id,e.last_name,d.department_id
from employees e join departments d on e.department_id = d.department_id
where d.department_name in ('Sales','IT');
#where d.department_name='IT' or d.department_name='Sales';
7.函数
1.基本函数
| 函数 | 用法 |
|---|---|
| ABS(X) | 返回X的绝对值 |
| SIGN(X) | 返回X的正负号，正1负-1，0返回0 |
| PI() | 返回圆周率的值 |
| CEIL(X)，CELING（X） | 返回大于或等于某个数的最下整数（无论正负取大的那个方向的） |
| FLOOR（X） | 返回小于或等于某个值得最大整数（无论正负取小的那个方向的） |
| LEAST（a,b,c） | 返回列表中最小的 |
| GREATEST（a,b,c） | 返回列表中最大的 |
| MOD（x,y） | 求x除以y后的余数 |
| RAND（） | 返回0~1的随机值 |
| RAND(x) | 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机 数 |
| ROUND(x) | 返回一个对x的值进行四舍五入后，最接近于X的整数 |
| ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位（Y可以为负的，往前省略，123负一为120，123负2为100） |
| TRUNCATE(x,y) | 返回数字x截断为y位小数的结果（Y可以为负的，往前省略，123负一为120，123负2为100，只取位数不四舍五入） |
| SQRT(x) | 返回x的平方根。当X的值为负数时，返回NULL |
| radians(x) | 将角度转化为弧度，其中，参数x为角度值 |
| DEGREES(x) | 将弧度转化为角度，其中，参数x为弧度值 |
数值函数
SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)，ROUND(123.5,-1)--120
FROM DUAL;
SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;
可以嵌套
SELECT TRUNCATE(ROUND(123.12，2),1)
FROM DUAL;
2.三角函数
| 函数 | 用法 |
|---|---|
| SIN(x) | 返回x的正弦值，其中，参数x为弧度值 |
| ASIN(x) | 返回x的反正弦值），即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| COS(x) | 返回x的余弦值，其中，参数x为弧度值 |
| ACOS(x) | 返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| TAN(x) | 返回x的正切值，其中，参数x为弧度值 |
| ATAN(x) | 返回x的反正切值，即返回正切值为x的值 |
| ATAN2(m,n) | 返回两个参数的反正切值 |
| COT(x) | 返回x的余切值，其中，X为弧度值 |
注：返回的都是弧度值，不方便看可以使用DEGREES（）将其转换为角度值
3.指数与对数函数
| 函数 | 用法 |
|---|---|
| POW(x,y)，POWER(X,Y) | 返回x的y次方 |
| EXP(X) | 返回e的X次方，其中e是一个常数，2.718281828459045 |
| LN(X)，LOG(X) | 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG10(X) | 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG2(X) | 返回以2为底的X的对数，当X <= 0 时，返回NULL |
SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)
FROM DUAL;
| POW(2,5) | POWER(2,4) | EXP(2) | LN(10) | LOG10(10) | LOG2(4) |
| 32 | 16 | 7.38905609893065 | 2.302585092994046 | 1 | 2 |
4.进制间的转换
| 函数 | 用法 |
|---|---|
| BIN(x) | 返回x的二进制编码 |
| OCT(x) | 返回x的八进制编码 |
| HEX(x) | 返回x的十六进制编码 |
| CONV(x,f1,f2) | 返回f1进制数变成f2进制数 |
SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
FROM DUAL;
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
| 1010 | A | 12 | 2 |
5.字符串函数
| 函数 | 用法 |
|---|---|
| ASCII(S) | 返回字符串S中的第一个字符的ASCII码值 |
| CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
| LENGTH(s) | 返回字符串s的字节数，和字符集有关 |
| CONCAT(s1,s2,…,sn) | 连接s1,s2,…,sn为一个字符串 |
| CONCAT_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个字符，作用与SUBSTRING(s,n,len)、 MID(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，否则返回 value1 |
注：在mysql中字符串的索引是从1开始的
select concat(e1.last_name,'work for',d1.department_name) 'details'
from employees e1 join department e2
on e1.department_id = e2.department_id
在oracle中字符串是区分大小写的，因此可以用大小写转换将字段全变为小写再与该字段比较
select *
from departments
where lower(department_name)='it';--先将IT变为小写，再与it比较
将hello重复4遍
select repeat('hello',4)
from dual;--hellohellohellohello
返回字符串首次出现的位置返回4
select field('ss','mnm','yy','tt','ss'，‘ss’)
from dual;
返回字符串首次出现的位置返回4，但是后面是一个整体的字符串，以逗号分隔
select field_in_set('ss','mm,yy,tt,ss,ss’)
from dual;
比较两个字符串，如果value1与value2相等，则返回NULL，否则返回 value1,该查询compare返回长度，若不加length返回字符串。
select employee_id,nullif(length(first_name),length(last_name)) 'compare'
from employees;
6.流程处理函数
| 函数 | 作用 |
|---|---|
| IF(value,value1,value2) | 如果value的值为true，返回value1， 否则返回value2 |
| IFNULL(value1, value2) | 如果value1不为null，返回value1，否 则返回value2 |
| case when 条件1 then 结果1 when 条件2 then 结果2 … [else 结果3] end | 相当于Java的if…else if…else… |
| case 表达式 when 常量值1 then 值1 when 常量值2 then 值2 …[else 值n]end | 相当于java中的switch case |
IF(value,value1,value2)
select last_name,commission_pct,salary*12*(1+if(commission_pct is not null ,commission_pct,0))
from employees;
select ifnull(commission_pct, 0)
from employees;
select last_name,
salary,
case when salary >= 20000 then '高新' when salary >= 10000 then '起薪' else '实习' end '状态',
department_id
from employees;
练习：查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数。
select last_name,case (department_id) when 10 then salary*(1+0.011) when 20 then salary*(1+0.012) when 30 then salary*(1+0.013) else salary*(1+0.014) end '加薪'
from employees
where department_id in(10,20,30);
7.加密与解密函数
| 函数 | 作用 |
|---|---|
| password(str) | 返回字符串str的加密版本，41位长的字符串。加密结果不可逆 ，常用于用户的密码加密（mysql8.0已弃用） |
| md5(str) | 返回字符串str的md5加密后的值，也是一种加密方式。若参数为 NULL，则会返回NULL |
| sha(str) | 从原明文密码str计算并返回加密后的密码字符串，参数为NULL时，返回NULL。 SHA加密算法比MD5更加安全 。 |
| encode(value，密码种子) | 返回使用密码种子作为加密密码加密value（mysql8.0已弃用） |
| decode（value，密码种子） | 返回使用password_seed作为加密密码解密value（mysql8.0已弃用） |
select md5('mysql'),sha('mysql'),md5(sha('mysql')),encode('value','mysql'),decode(encode('value','mysql'),'mysql')
from dual;
8.MySQL信息函数
| 函数 | 作用 |
|---|---|
| version（） | 返回当前MySQL的版本号 |
| connection_id（） | 返回当前MySQL服务器的连接数 |
| database（）、schema（） | 返回MySQL命令行当前所在的数据库 |
| USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER() | 返回当前连接MySQL的用户名，返回结果格式为 “主机名@用户名” |
| charset(value) | 返回字符串value自变量的字符集 |
| collation(value) | 返回字符串value的比较规则 |
select version(), connection_id(), database(), user(), charset('数据库'), collation('数据库')
from dual;
9.其他函数
| 函数 | 作用 |
|---|---|
| FORMAT(value,n) | 返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位,如果n的值小于或者等于0，则只保留整数部分 |
| 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 |
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
| 123.12 | 124 | 123 |
SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
10000 | 22B8 | NULL
SELECT INET_ATON('192.168.1.100');
3232235876
以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
select INET_NTOA(3232235876)
192.168.1.100
SELECT BENCHMARK(1, MD5('mysql'));
0
SELECT BENCHMARK(1000000, MD5('mysql'));
0
SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
utf8mb4 | utf8
8.时间和日期函数
注：data和time可以是系统获取的时间函数也可以是’yyyy-MM-dd’、‘hh:mm:ss’
1.获取日期、时间
| 函数 | 用法 |
|---|---|
| curdate() ，CURRENT_DATE() | 返回当前日期，只包含年、 月、日 |
| curtime() ， CURRENT_TIME() | 返回当前时间，只包含时、 分、秒 |
| now() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期和时间 |
| UTC_DATE() | 返回UTC（世界标准时间） 日期 |
| UTC_TIME() | 返回UTC（世界标准时间） 时间 |
2.日期与时间戳的转换
| 函数 | 用法 |
|---|---|
| unix_timestamp() | 以UNIX时间戳的形式返回当前时间。 |
| UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 |
| from_unixtime(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
select SELECT UNIX_TIMESTAMP(),unix_timestamp('2022-7-31 12::12:12'),from_unixtime(unix_timestamp())
from dual; - >1634348884 既可以转换当前的，也可以转换给定的
3.获取年份、日期、星期数、天数等
| 函数 | 用法 |
|---|---|
| YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 |
| HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时间值 |
| monthname(date) | 返回月份：January，… |
| dayname(date) | 返回星期几：MONDAY，TUESDAY…SUNDAY |
| weekday(date) | 返回周几，注意，周1是0，周2是1，。。。周日是6 |
| quarter(date) | 返回日期对应的季度，范围为1～4 |
| WEEK(date) ， WEEKOFYEAR(date) | 返回一年中的第几周 |
| dayofyear(date) | 返回日期是一年中的第几天 |
| dayofmonth(date) | 返回日期位于所在月份的第几天 |
| dayofweek(date) | 返回周几，注意：周日是1，周一是2，。。。周六是 7 |
| extract(type FROM date) | 返回指定日期中特定的部分，type指定返回的值 |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iOYMy1LW-1660187025951)(MySQL.assets/mysql-2.png)]
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;
4.时间和秒钟的转换
| 函数 | 作用 |
|---|---|
| time_to_sec(time) | 将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟 *60+秒 |
| sec_to_time(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 |
SELECT SEC_TO_TIME(78774),TIME_TO_SEC(CURTIME())
from dual;
5.计算日期和时间的函数
第一组
| 函数 | 作用 |
|---|---|
| date_add(datetime, INTERVAL n type)， ADDDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时 间段的日期时间 |
| date_sub(date,INTERVAL n type)， SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的日期 |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fr3IQG4J-1660187025951)(MySQL.assets/mysql-type值.png)]
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;
SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;
第二组
| 函数 | 作用 |
|---|---|
| ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时，代表的是 秒 ，可以为负数 |
| SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时，代表的 是 秒 ，可以为负数 |
| DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 |
| TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 |
| FROM_DAYS(N) | 返回从0000年1月1日起，N天以后的日期 |
| TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
| LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
| MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 |
| MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 |
| PERIOD_ADD(time,n) | 返回time加上n后的时间 |
SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3')--可以这样减去时分秒,DATEDIFF(NOW(),'2021-10-
01'),TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,
10)
FROM DUAL;
6.日期格式化与解析
| 函数 | 作用 |
|---|---|
| DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值，返回字符串–格式化(格式化时不需要格式匹配) |
| TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值，返回字符串–格式化(格式化时不需要格式匹配) |
| GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式（解析(字符串→时间)时也要格式相同，格式化时没要求） |
| STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析，解析为一个日期，两个参数格式要匹配，否则返回null–解析 |
| 格式符 | 说明 | 格式符 | 说明 |
|---|---|---|---|
| %Y | 4位数字的年份 | %y | 两位数字的年份 |
| %M | 月名表示月份（January,…） | %m | 两位数字表示月份 （01,02,03。。。） |
| %b | 缩写的月名（Jan.，Feb.，…） | %c | 数字表示月份（1,2,3,…） |
| %D | 英文后缀表示月中的天数 （1st,2nd,3rd,…） | %d | 两位数字表示月中的天数(01,02…) |
| %e | 数字形式表示月中的天数 （1,2,3,4,5…） | ||
| %H | 两位数字表示小数，24小时制 （01,02…） | %h和%l | 两位数字表示小时，12小时制 （01,02…） |
| %k | 数字形式的小时，24小时制(1,2,3) | ||
| %i | 两位数字表示分钟（00,01,02） | %S和%s | 两位数字表示秒(00,01,02…) |
| %W | 一周中的星期名称（Sunday…） | %a | 一周中的星期缩写（Sun.， Mon.,Tues.，…） |
| %w | 以数字表示周中的天数 (0=Sunday,1=Monday…) | ||
| %j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周， （1,2,3。。）其中Sunday为周中第一 天 |
| %u | 以数字表示年中的第几周， （1,2,3。。）其中Monday为周中第一 天 | ||
| %T | 24小时制 | %r | 12小时制 |
| %p | AM或PM | %% | 表示% |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BEXZTgVU-1660187025952)(MySQL.assets/mysql常用时间日期格式.png)]
补：DATE USA --%m.%d.%Y
select DATE_FORMAT(CURDATE(),'%Y-%M-%D'),date_format(now(),'%Y-%m-%d')
from dual;
select str_to_date(hiredate,'%Y-%m-%d %H:%i:%s')
from employees;
SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;
通过get_format(日期类型，格式)，用来设置更多操作函数作用DATE_FORMAT(date,fmt)与STR_TO_DATE(str, fmt)中的格式(fmt),但是在解析(字符串→时间)时也要格式相同。
SELECT GET_FORMAT(DATE, 'USA');
| GET_FORMAT(DATE, 'USA') |
| %m.%d.%Y |
SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;
1.查询系统时间（注：时间+日期）
select now() '系统时间'
from dual;
2.查询员工姓名、工资，以及工资提高20%后的结果（new salary）
select last_name, salary, salary * 1.2 'new salary'
from employees;
3.将员工姓名按首字母顺序排序，并写出姓名的长度(length)降序的二级排序
select last_name, length(last_name) 'length'
from employees
order by last_name, length desc;
4.查询员工id、last_name、salary，并作为一个列输出，别名out_put
select concat(employee_id, ',', last_name, ',', salary) 'out_put'
from employees;
5.查询公司个员工的工作年数，天数，并按工作年数降序排序
select employee_id,
last_name,
round(datediff(now(), hiredate)) '天数',
round(datediff(now(), hiredate) / 365) '年数',
round((to_days(now()) - to_days(hiredate)) / 365)
from employees
order by '年数' desc;
6.查询员工姓名、hire_date、部门id、，满足以下条件：启用时间在1997年之后，部门id为80、90或110，commisson_pct不为空
select last_name, department_id, hiredate
from employees
where department_id in (80, 90, 110)
and commission_pct is not null
# and hiredate >= '1997-01-01'
# and date_format('hiredate, '%Y-%m-%d') >= '1997-01-01';
# and date_format(hiredate, '%Y') >= '1997';
and str_to_date('1997-01-01', '%Y-%m-%d') >= hiredate;
7.查询公司中入职超过10000天的员工姓名、入职时间
select employee_id, last_name, hiredate, datediff(now(), hiredate)
from employees
where datediff(now(), hiredate) >= 10000;
8.做一个查询，产生下面的效果
select concat(last_name, 'earn', truncate(salary, 0), 'monthy but wangts', truncate(salary * 3, 0)) 'Dream salary'
from employees;
9.使用case-when
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
select last_name,
job_id,
case job_id
when 'AD_PRES' then 'A'
when 'ST_MAN' then 'B'
when 'IT_PROG' then 'C'
when 'SA_REP' then 'D'
when 'ST_CLERK' then 'E' end 'grade'
from employees;
9.子查询
1.子查询
1.引入
查询谁的工资比abel高
#方式一：两个select
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
2.注意
①主查询（外查询）、内查询（子查询）
②子查询在主查询之前执行完成
③子查询的结果被主查询使用
④子查询包含在一个括号内
⑤一般将子查询放在比较条件的右侧
⑥单行操作符对应单行子查询，多行操作符对应多行子查询
3.子查询的分类
（1）查询结果的条数–单行子查询与多行子查询
①单行子查询：内查询的查询结果是一条数据
②多行子查询：内查询查出来的是多条记录
（2）内查询是否被执行多次–相关子查询与不相关子查询
①不相关子查询：内查询与外查询没有直接关系。如：查询工资大于本公司平均工资的员工信息
②相关子查询：内查询依赖于外查询的条件。如：查询工资大于本部门平均工资的员工信息
4.哪些地方可以用子查询
除了在group by和limit中不可以声明子查询其他地方均可以
SELECT 可以
FROM 可以
WHERE 可以
AND 可以
GROUP BY 不可以
HAVING 可以
ORDER BY 可以 ASC/DESC
LIMIT 不可以
2.单行子查询
1.单行比较操作符
只可以运用于单行子查询
| 操作符 | 作用 |
|---|---|
| = | 等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| <> | 不等于 |
2.子查询编写思路
(1)从里往外写：①如果子查询相对简单
②不相关子查询
(2)从外往里写：①如果子查询相对复杂
②相关子查询
3.举例
1.查询工资大于149号员工工资的员工信息
select employee_id,last_name，salary
from employees
where salary > (select salary
from employees
where employee_id=149);
2.返回job_id与141号员工相同，工资比143号员工多的员工姓名、job_id和工资
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
select last_name,job_id,salary
from employees
where salary = (select min(salary)
from employees);
4.查询与141号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id
方式一：
select employee_id,manager_id,department_id
from employees
where department_id = (select department_id
from employees
where employee_id = 141)
and managet_id = (select manager_id
from employees
whereemployee_id = 141)
and employee_id <> 141;
方式二：多个外查询对应的内查询条件一样可以写一起加括号
select employee_id,manager_id,department_id
from employees
where （department_id,managet_id) = (select department_id
from employees
where employee_id = 141)
and employee_id <> 141;
3.having中的单行子查询
1.查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary)
from employees
where department_id is not null
group by department_id
having min(salary)>(select min(salary)
from departments
where department_id=50)
4.case中的单行子查询
1.显示员工的employee_id,last_name和location
select employee_id,last_name,case when department_id = (select department_id
from departments
where location_id=1800)
then 'canada'
else 'USA' end 'location'
from employees;
5.子查询中的空值问题
内查询的查询结果为空也不会报错，只不过显示的结果为空的
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
6.非法使用子查询
由于子查询的查询结果为多行，=为单行操作符，因此不符合逻辑；工资即等于1000又等于2000不知道具体是哪个，不符合逻辑
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
3.多行子查询
1.定义
内查询返回了多行的数据
2.多行比较操作符
可以运用于多行子查询，也可以用于当行子查询
| 操作符 | 含义 |
|---|---|
| in | 等于列表中的任意一个。通常可以改写为exists |
| any | 表示某一：需要和单行比较操作符一起使用，和子查询返回的某一个值比较 |
| all | 表示所有：需要和单行比较操作符一起使用，和子查询返回的所有值比较 |
| some | 实际上是ANY的别名，作用相同，一般常使用any |
1.in的举例
SELECT employee_id, last_name
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
2.返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
select employee_id,last_name,job_id,salary
from employees
where salary <any (select salary
(<all--所有----也可以< min(salary)) from employee
where job_id='IT_PROG')
and job_id <> 'IT_PROG';
3.查询平均工资最低的部门id
方式一：先查询平均工资最低的部门，然后让每个部门的平均工资等于它
select department_id
from employees
group by department_id
having avg(salart) = (select min(avg_s)
from (select avg(salary) avg_s
from employees
group by department_id
)min_s_table
);
方式二：利用小于等于最小的
select department_id
from employees
group by department_id
having avg(salart) <= (select avg(salary) avg_s
from employees
group by department_id
);
3.可以在from中使用子查询
①from中的子查询相当于是把查询结果看作是一张表
②可以在外层查询中获取该from表的属性，该属性如果又别名用别名来声明，可以对这个属性来使用函数
③需要给from中的子查询最后加一个别名，表示该表的名称
查询平均工资最低的部门
select min(avg_salary)
from (select avg(salary) avg_salary
from employees
group by department_id
) salary_avg_table;
4.多行子查询的空值问题
not in 会影响查询结果，一般用not in要去掉空值
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
where manager_id is not null//不加这句查询出来的是0条记录，因为不加的话not in是不能是这里面的任何一个
);
4.相关子查询
1.相关子查询
①如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件 关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询 。(内查询中出现了外查询的表)
②相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。
③子查询中使用主查询中的列。
2.举例
1.查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
相关子查询
select last_name,salary,department_id
from employees e1
where salary > (select avg(salary)
from employees e2
where e2.employees = e1. employees);
子查询声明在from中--想做一个查询但是这个表不是真实存在，可以考虑使用from子查询
select e1.last_name,e1.salary,e1.departmeng_ide1.
from employee e1,(select department_id,avg(salary) avgsal
from employee
group by department_id) avgsalary_tab
where e1.departmeng_id = avgsalary_tab.departmeng_id
and e1.salary > avgsalary_tab.avgsal
2.找出每个学生超过他自己选修课程平均成绩的课程号
select sno,cno
from sc s1
where grade > (select avg(grade)
from sc s2
where s1.sno = s2.sno);
3.查询员工的id,salary,按照department_name 排序(order by 中使用相关子查询)
外查询每查出一条记录，就会在内查询中进行部门名称的查询，每查一条进行一次排序。如果加上外部where则内部的where每次会筛选和外部部门id一样的部门id
select employee_id,salary
from employees e
#where department_id in (30,60,90)
order by (select department_name
from departments d
where e.department_id=d.department_id
);
4.若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
3.EXISTS 与NOT EXISTS关键字
1.说明
①带有exists关键字的子查询不返回任何数据，只返回true和false
2.练习
1.查询公司管理者的employee_id，last_name，job_id，department_id信息
方式一：exists
select employee_id,last_name,job_id,department_id
from employees e1
where exists (select *
from employees e2
where e1.employee_id = e2.manager_id);
方式二：自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
方式饭：子查询
select employee_id,last_name,job_id,department_id
from employees
where employee_id in (select distince manager_id
from employees);
2.查询所有选修了1好课程的学生姓名
select name
from student
where exists (select *
from sc
where student.sno=sc.sno and cno=1);
3.查询departments表中，不存在于employees表中的部门的department_id和department_name
方式一：找右连接去掉null的
方式二：not extsit
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id = d.department_id);
5.课后练习
1. 查询和Zlotkey相同部门的员工姓名和工资
select last_name, salary
from employees
where department_id in (select department_id
from employees
where last_name = 'Zlotkey');
2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
select employee_id, last_name, salary
from employees
where salary > (select avg(salary)
from employees);
3.选择工资大于所有job_id='SA_MAN'的员工的工资员工的last_name，job_id,salary
select last_name, job_id, salary
from employees
where salary > all (select salary
from employees
where job_id = 'SA_MAN');
4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
select employee_id, last_name
from employees
where employee_id in (select distinct employee_id
from employees
where last_name like '%u%'
or last_name like 'u%'
or last_name like '%u');
5. 查询在部门的 location_id 为 1700 的部门工作的员工的员工号
select employee_id
from employees
where department_id in (select department_id
from departments
where location_id = 1700);
6. 查询管理者是 King 的员工姓名和工资
select last_name, salary
from employees
where manager_id in (select employee_id
from employees
where last_name = 'King');
7. 查询工资最高的员工的姓名，要求 first_name 和 last_name
select last_name, employee_id
from employees
where salary = (select min(salary)
from employees);
8.查询平均工资最低的部门信息
方式一：
select *
from departments
where department_id = (select department_id
from employees
group by department_id
having avg(salary) = (select min(avg_salary)
from (select avg(salary) avg_salary
from employees
group by department_id) avg_salary_table));
方式二:找最小值就找小于等于最小的那个
select *
from departments
where department_id = (select department_id
from employees
group by department_id
having avg(salary) <= all (
select avg(salary) avg_salary
from employees
group by department_id));
方式三:求出每个部门的平均工资，然后按照升序排序，第一个就是最小的，再在外循环找平均工资等于这个的部门号，再在部门表里对该部门号进行搜索
select *
from departments
where department_id = (select department_id
from employees
group by department_id
having avg(salary) = (select avg(salary) avg_salary
from employees
group by department_id
order by avg_salary asc
limit 1));
方式四:利用自定义表和departments表的连接
select d.*
from departments d,
(select department_id, avg(salary) avg_salary
from employees
group by department_id
order by avg_salary
limit 1) min_salary_table
where d.department_id = min_salary_table.department_id;
9.查询平均工资最低部门信息和该部门的平均工资(相关子查询)
select d.*, (select avg(salary) from employees where d.department_id = department_id) avgsalary
from departments d,
(select department_id, avg(salary) avg_salary
from employees
group by department_id
order by avg_salary
limit 1) min_salary_table
where d.department_id = min_salary_table.department_id;
方式一：
select d.*, (select avg(salary) from employees where d.department_id = department_id) avgsalary
from departments d
where department_id = (select department_id
from employees
group by department_id
having avg(salary) = (select min(avg_salary)
from (select avg(salary) avg_salary
from employees
group by department_id) avg_salary_table));
方式二:找最小值就找小于等于最小的那个
select d.*, (select avg(salary) from employees where d.department_id = department_id) avgsalary
from departments d
where department_id = (select department_id
from employees
group by department_id
having avg(salary) <= all (
select avg(salary) avg_salary
from employees
group by department_id));
方式三:求出每个部门的平均工资，然后按照升序排序，第一个就是最小的，再在外循环找平均工资等于这个的部门号，再在部门表里对该部门号进行搜索
select d.*, (select avg(salary) from employees where d.department_id = department_id) avgsalary
from departments d
where department_id = (select department_id
from employees
group by department_id
having avg(salary) = (select avg(salary) avg_salary
from employees
group by department_id
order by avg_salary asc
limit 1));
方式四:利用自定义表和departments表的连接
select d.*, (select avg(salary) from employees where d.department_id = department_id) avgsalary
from departments d,
(select department_id, avg(salary) avg_salary
from employees
group by department_id
order by avg_salary
limit 1) min_salary_table
where d.department_id = min_salary_table.department_id;
10.查询平均工资最高的job_id
方式一：
select *
from jobs
where job_id = (select job_id
from employees
group by job_id
having avg(salary) = (select max(avg_salary)
from (select avg(salary) avg_salary
from employees
group by job_id) avg_salary_table
));
方式二:
select *
from jobs
where job_id = (select job_id
from employees
group by job_id
having avg(salary) >= all (select avg(salary)
from employees
group by job_id
)
);
方式三：
select *
from jobs
where job_id = (select job_id
from employees
group by job_id
having avg(salary) = (select avg(salary) avg_salary
from employees
group by job_id
order by avg_salary desc
limit 1)
);
方式四：j.*不加j.会多出max_salary_table表中的属性
select j.*
from jobs j,
(select job_id,avg(salary) avg_salary
from employees
group by job_id
order by avg_salary desc
limit 1) max_salary_table
where j.job_id = max_salary_table.job_id;
11.查询平均工资高于公司平均工资的部门有哪些
select department_id
from employees
where department_id is not null
group by department_id
having avg(salary) > (select avg(salary)
from employees
);
12.查询出公司中所有manager的详细信息
方式一：exists
select employee_id,last_name,job_id,department_id
from employees e1
where exists (select *
from employees e2
where e1.employee_id = e2.manager_id);
方式二：自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
方式饭：子查询
select employee_id,last_name,job_id,department_id
from employees
where employee_id in (select distince manager_id
from employees);
13.各个部门中最高工资中 最低的那个部门 的最低工资是多少
方式一：
select min(salary)
from employees
where department_id = (select department_id
from employees
group by department_id
having max(salary) = (select min(max_salary)
from (select max(salary) max_salary
from employees
group by department_id) max_salary_table));
方式二：
select min(salary)
from employees
where department_id = (select department_id
from employees
group by department_id
having max(salary) <= all (select max(salary) max_salary
from employees
group by department_id));
方式三：
select min(salary)
from employees
where department_id = (select department_id
from employees
group by department_id
having max(salary) = (select max(salary) max_salary
from employees
group by department_id
order by max_salary
limit 1
));
方式四：
select min(salary)
from employees e,
(select department_id, max(salary) max_salary
from employees
group by department_id
order by max_salary
limit 1
) max_salary_table
where e.department_id = max_salary_table.department_id;
14.查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary
select last_name, department_id, email, salary
from employees
where employee_id in (与=any可替换)(select manager_id
from employees
where department_id = (select department_id
from employees
group by department_id
having avg(salary) >= all (select avg(salary)
from employees
group by department_id
)));
15.查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
方式一：
select department_id
from departments
where department_id not in (select department_id
from employees
where job_id = 'ST_CLERK');
方式二：
select department_id
from departments d
where not exists(select *
from employees e
where e.department_id = d.department_id
and e.job_id != 'ST_CLERK');
16.选择所有没有管理者的员工的姓名
select last_name
from employees e1
where not exists(select *
from employees e2
where e1.manager_id = e2.employee_id);
17.查询员工号、姓名雇佣时间、工资，其中员工的管理者为'Da Haan'
select *
from employees
where manager_id in (select *
from employees
where last_name = 'De Haan');
方式二：
select *
from employees e1
where exists(select *
from employees e2
where e1.manager_id = e2.employee_id
and e2.last_name = 'De Haan');
18.查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资(相关子查询)
方式一：
select employee_id, last_name, salary
from employees e1
where salary > (select avg(salary)
from employees e2
where e2.department_id = e1.department_id);
方式二：
select e1.employee_id, e1.last_name, e1.salary
from employees e1,
(
select department_id, avg(salary) avg_salary
from employees
group by department_id
) new_table
where e1.department_id = new_table.department_id
and salary > avg_salary
19.查询每个部门下的部门人数大于5的部门名称(相关子查询)
将部门表的每条记录传进去，然后统计与传入的d的id相同的e的记录，因此不用在内部进行分组
select department_name
from departments d
where 5 < (select count(*)
from employees e
where e.department_id = d.department_id);
20.查询每个国家下的部门个数大于2的国家标号(相关子查询)
select country_id
from locations l
where 2 < (select count(*)
from departments d
where d.location_id = l.location_id);
五、DCL语言
1.commit和rollback
commit：提交数据，一旦执行commit，则数据就会被永久保存在了数据库中，意味着数据不可以回滚
rollback：回滚数据，一旦执行了rollbakc，则可以实现数据的回滚，回滚到最后的一次commit之后
六、约束
1.为什么需要约束
数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中 存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
①实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录
②域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女”
③引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门
④用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门 经理的工资不得高于本部门职工的平均工资的5倍。
⑤约束：约束是表级的强制规定。 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE语句规定 束。
2.约束的分类
(1)约束字段的个数角度：单列约束与多列约束
(2)约束的作用范围角度：列级约束与表级约束
①列级约束：将约束声明在对应字段的后面
②表级约束：在表中所有字段声明完以后，声明在所有字段后面
(3)约束的作用角度
(1)not null：非空约束
①默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
②非空约束只能使用列举约束去定义(每个属性之后声明)
③一个表可以有很多列都分别限定了非空
④空字符串’'不等于NULL，0也不等于NULL
#创建表时添加约束
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);
#插入数据
insert into student values(1,'张三','13710011002','110222198912032545'); #成功
insert into student values(2,'李四','13710011002',null);#身份证号为空
insert into student values(2,'李四',null,'110222198912032546');#成功，tel允许为空
insert into student values(3,null,null,'110222198912032547');#失败
#修改表示添加约束
alter table 表名称 modify 字段名 数据类型 not null;
#去除非空约束
alter table 表名称 modify 字段名 数据类型 null;
#举例
ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;
(2)unique：唯一性约束
①同一个表可以有多个唯一约束。
②唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
③唯一性约束允许列值为空，且可以多次添加null值不记作重复。
④在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
⑤MySQL会给唯一约束的列上默认创建一个唯一索引。
⑥符合唯一：当使用表及约束一次给多个属性设置唯一约束时，则只需要保证这多个属性不同时一样即可。如数据1值为abc，123，数据2值为abc，112，这两条数据可以添加成功。
#创建表时添加唯一约束
create table 表名称(
字段名 数据类型 unique,--列级约束
字段名 数据类型 unique,
字段名 数据类型,
[constraint 约束名] unique(字段名)--表级约束。在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
);
#举例
CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表级约束语法为多个列添加约束
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);
#修改表时添加约束(如果为某列添加唯一约束时，如果该列下已有重复的数据则会添加约束失败，要先将数据修改为不一样的才可以修改约束)
#方式一：
alter table 表名称 add [constraint 约束名] unique(字段列表1,字段列表2,...);--当使用表及约束一次给多个属性设置唯一约束时，则只需要保证这多个属性不同时一样即可。如数据1值为abc，123，数据2值为abc，112，这两条数据可以添加成功
#方式二：
alter table 表名称 modify 字段名 字段类型 [constraint 约束名] unique;
#符合唯一案例
#学生表
create table student(
sid int, #学号
sname varchar(20), #姓名
tel char(11) unique key, #电话
cardid char(18) unique key #身份证号
);
#课程表
create table course(
cid int, #课程编号
cname varchar(20) #课程名称
);
#选课表
create table student_course(
id int,
sid int,
cid int,
score int,
unique key(sid,cid) #复合唯一
);
insert into student values(1,'张三','13710011002','101223199012015623');#成功
insert into student values(2,'李四','13710011003','101223199012015624');#成功
insert into course values(1001,'Java'),(1002,'MySQL');#成功
insert into student_course values
(1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);#成功
insert into student_course values (5, 1, 1001, 88);#失败
#删除唯一性索引
①添加唯一性约束的列上也会自动创建唯一索引。
②删除唯一约束只能通过删除唯一索引的方式删除。
③删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
④如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
#查看都有哪些约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名';
#或用
desc 表名;
#删除唯一性约束
ALTER TABLE 表名
DROP INDEX 唯一性约束名;
(3)primary key：主键约束
(4)foreign key：外键约束
①从表的外键列，必须参考主表的主键或唯一约束的列
②在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），因此尽量在声明约束时来设置
③创建表时就指定外键约束的话，先创建主表，再创建从表
④删表时，先删从表（或先删除外键约束），再删除主表
⑤当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
⑥在“从表”中指定外键约束，并且一个表中可以建立多个外键约束
⑦从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误
⑧删除外键约束后，必须手动删除对应的索引
#建表时
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT 外键约束名称] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
#案例
create table dept( #主表
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(#从表
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);
说明：
（1）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
（2）删除表时，先删除从表emp，再删除主表dept
#建表后
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用
字段) [on update xx][on delete xx];
#案例
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int #员工所在的部门
);
#这两个表创建时，没有指定外键的话，那么创建顺序是随意
alter table emp add foreign key (deptid) references dept(did);
#删除外键约束
(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名(column name)
ALTER TABLE 从表名 DROP INDEX 索引名;
⑨阿里开发规范：在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的SQL操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢 。所以， MySQL允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。不得使用外键与级联，一切外键概念必须在应用层解决。 说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学 生表中的student_id，同时触发成绩表中的student_id更新，即为级联更新。外键与级联更新适用于单机低并发 ，不适合分布式 、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度 。
(5)约束等级
①Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
②Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子 表的外键列不能为not null
③Restrict(No action)方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
④Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置 成一个默认的值，但Innodb不能识别
对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
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等级(建议用ON UPDATE CASCADE ON DELETE RESTRICT)
);
insert into dept values(1001, '人事部');
insert into dept values(1002, '技术部');
insert into dept values(1003, '咨询部');
insert into emp values(1,'张三',1001); #在添加这条记录时，要求部门表有1001部门
insert into emp values(2,'李四',1001);
insert into emp values(3,'王五',1002);
#修改主表成功，从表也跟着修改，修改了主表被引用的字段1002为1004，从表的引用字段就跟着修改为1004了
update dept set did = 1004 where did = 1002;
#删除主表的记录成功，从表对应的字段的值被修改为null
delete from dept where did = 1001;
(6)check：检查约束
①检查某个字段的值是否符号xx要求，一般指的是值的范围
②MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告
create table text(
id int primary key,
name varchar(10),
gender char check ('男' or '女'),
salary int check(salary>2000)
#age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))
#CHECK(height>=0 AND height<3)
);
into employee values(1,'张三','妖',2000);#mysql5.0添加成功，mysql8.0添加失败
into employee values(2,'李四','男',2000);#mysql5.0添加成功，mysql8.0添加失败
into employee values(3,'gala','男',25000);#添加成功
(7)default：默认值约束
①：默认值约束一般不在唯一键和主键列上加
②一般用not null default ‘’或not null default 0
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table employee(
eid int primary key,
ename varchar(20) not null,
gender char default '男',
tel char(11) not null default '' #默认是空字符串
);
#建表后
alter table 表名称 modify 字段名 数据类型 default 默认值
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;
alter table employee modify gender char default '男'; #给gender字段增加默认值约束
alter table employee modify tel char(11) default ''; #给tel字段增加默认值约束
alter table employee modify tel char(11) default '' not null;#给tel字段增加默认值约束，并保留非空约束
#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 ;
#删除默认值约束，保留非空约束
alter table 表名称 modify 字段名 数据类型 not null;
alter table employee modify gender char; #删除gender字段默认值约束，如果有非空约束，也一并删除
alter table employee modify tel char(11) not null;#删除tel字段默认值约束，保留非空约束
(8)auto_increament：自增
①一个表最多只能有一个自增长列
②当需要产生唯一标识符或顺序值时，可设置自增长
③自增长列约束的列必须是键列（主键列，唯一键列）
④自增约束的列的数据类型必须是整数类型
⑤如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接 赋值为具体值。
#创建自增长列
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);
#常常这样使用
create table employee(
eid int primary key auto_increment,
ename varchar(20)
);
#建表后添加约束
alter table 表名称 modify 字段名 数据类型 auto_increment;
#删除自增长列：去掉auto_increment相当于删除
alter table 表名称 modify 字段名 数据类型;
⑥8.0新特性持久化量增：5.7中重启服务器后他会找到最后一个数进行自增，如到6了，把3和6删除1 2 4 5，重启后会从5自增，也就是添加的第一个数为6；但是在8.0中他仍会从6开始，也就是添加的第一个数据为7。
⑦如果不重启服务器则会从已经有的最大的值开始自增尽管这个值已经被删除
3.如何添加/删除约束
①在create table时添加约束
②在alter table时增加约束、删除约束
4.查看表约束
select *
from information_schema.TABLE_CONSTRAINTS
where TABLE_NAME = 'employees';
七、视图
1.视图的理解
①视图可以看作是一个虚拟表，本身是不存放数据的
②视图的本质就可以看作是存储起来的select语句
③视图中select语句中涉及到的表被称作基表
④针对视图做增删改操作会影响基表中的数据；对基表进行增删改操作也会影响视图
⑤视图本身的删除不会影响基表数据的删除
⑥小型项目不建议使用视图
⑦视图的优点：简化查询、空值数据访问
2.创建视图
①可以在字段列表出给每个属性来起名字，使用这种方法时字段列表要和select中的字段匹配(个数、顺序相同)
②可以在select中直接给属性起名字作为视图列的名字
③可以查询出表中不存在的字段，通过select的结果，如年薪、平均工资等
④可以基于多表查询来建立视图
⑤利用视图对数据进行格式化
⑥视图上可以再创建视图
CREATE VIEW 视图名称(字段列表)
AS 查询语句
#案例一：①②③
CREATE VIEW emp_year_salary (ename,year_salary)
AS
SELECT ename,salary*12*(1+IFNULL(commission_pct,0))
FROM t_employee
where salary>8000;
#案例二：基于多表查询建立视图
#例一
CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
#例二
CREATE VIEW emp_dept
AS
SELECT ename,dname
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did;
#例三
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
#利用视图对数据进行格式化
CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id
#视图上可以再创建视图(其中emp_dept与_emp_year_salary是两个视图)
CREATE VIEW emp_dept_ysalary
AS
SELECT emp_dept.ename,dname,year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;
3.查看视图
1.查看数据库的表对象、视图对象
show tables;
2.查看视图的结构
desc 视图名;
3.查看视图的属性信息
#查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
show tables status like '视图名';
执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。
4.查看视图的详细定义信息
show create view 视图名称;
4.更新视图
1.说明
MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。
UPDATE emp_tel SET tel = '13789091234' WHERE ename = '孙洪亮';
DELETE FROM emp_tel WHERE ename = '孙洪亮';
2.不可更新视图的情况
要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。另外当视图定义出现如下情况时，视图不支持更新操作。
①在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作
② 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作
③ 在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持INSERT和DELETE操作
④在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值
⑤在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、 UNION 等，视图将不支持INSERT、UPDATE、DELETE
⑥在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE
⑦视图定义基于一个 不可更新视图
⑧常量视图
注：虽然可以更新视图数据，但总的来说，视图作为虚拟表 ，主要用于方便查询 ，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。
5.修改、删除视图
1.修改视图
方式一：使用CREATE OR REPLACE VIEW 子句修改视图
方式二：ALTER VIEW
注：无论用那种方式都是将原有的视图覆盖掉了
#方式一：使用CREATE OR REPLACE VIEW 子句修改视图
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;
#方式二：ALTER VIEW
ALTER VIEW 视图名称
AS
查询语句
2.删除视图
①删除视图只是删除视图的定义，并不会删除基表的数据。
②基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这 样的视图c需要手动删除或修改，否则影响使用。
DROP VIEW IF EXISTS 视图名称1,视图名称2,...;
#案例
DROP VIEW empvu80;
八、存储过程与存储函数
1.存储过程
1.存储过程的理解
①是一组经过预先编译的SQL语句的封装。
②执行过程：存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。
③它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表 ， 通常不对底层数据表直接操作，而存储过程是程序化的SQL，可以直接操作底层数据表 ，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。
④一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。
⑤一般用于更新
2.存储过程的创建
#语法
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体;
END
1.参数前面的符号
①in：当前参数为输入参数，也就是表示入参；配合into将数据存入变量
存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN，表示输入参数。
②out：当前参数为输出参数，也就是表示出参；
执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
③inout：当前参数既可以为输入参数，也可以为输出参数。
2.形参类型
形参类型可以是MySQL数据库中的任意类型且使用原表的类型。
3.characteristics
表示创建存储过程时指定的对存储过程的约束条件，其类型如下：
①LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
②[NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的，相同的输入会得到相同的输出结果；NOT DETERMINISTIC表示结果是不确定的，相同的输入不一定会得到相同的输出
③{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使 用SQL语句的限制。
CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
默认情况下，系统会指定为CONTAINS SQL。
④SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
4.其他说明
①存储过程体中可以有多条SQL语句，如果仅仅一条SQL 语句，则可以省略BEGIN和END
1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进
行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
#需要设置新的结束标记，因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。常用$或//来作为新的结束标记。存储过程结束再用delimiter ;还原为;
DELIMITER 新的结束标记
#案例
DELIMITER //
CREATE PROCEDURE show_max_salary()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查看最高薪资'
BEGIN
SELECT MAX(salary) FROM emps;
END //
DELIMITER ;
3.调用存储过程
①存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。
CALL [其他数据库名.]存储过程名(实参列表)
#调用in模式的参数
CALL sp1('值');
#调用out模式的参数
SET @name;
CALL sp1(@name);
SELECT @name;
#调用inout模式的参数--:=或=给变量·
SET @name=值;
CALL sp1(@name);
SELECT @name;
4.举例
create database procedure_text;
use procedure_text;
create table employees
as
select *
from myemployees.employees;
create table departments
as
select *
from myemployees.departments;
select *
from employees;
select *
from departments;
#创建存储过程select_all_data()，查看employees表的所有数据
delimiter $
create procedure select_all_data()
begin
select * from employees;
end $
delimiter ;
#存储过程调用：CALL 存储过程名(实参列表)
call select_all_data();
#创建存储过程avg_employee_salary()，返回所有员工的平均工资
delimiter $
create procedure avg_employee_salary()
begin
select avg(salary)
from employees;
end $
delimiter ;
call avg_employee_salary();
#创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出
delimiter $
create procedure show_min_salary(out ms double)
begin
select min(salary)
into ms
from employees;
end $
delimiter ;
#调用存储过程--@表示用户自定义变量
call show_min_salary(@ms);
#查看ms变量值
select @ms;
#创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名。
delimiter $
create procedure show_someone_salary(in empname varchar(20))
begin
select salary
from employees
where last_name = empname;
end $
delimiter ;
#调用方式一：
call show_someone_salary('Abel');
#调用方式二：:=或=表示赋值
set @empname := 'Abel';
call show_someone_salary(@empname);
#创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。
delimiter $
create procedure show_someone_salary2(in empname varchar(20), out empsalary decimal(10, 2))
begin
select salary
into empsalary
from employees
where last_name = empname;
end $
delimiter ;
set @empname = 'Abel';
call show_someone_salary2(@empname, @empsalary);
select @empsalary;
#创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名。
delimiter $
create procedure show_mgr_name(inout empname varchar(20))
begin
select last_name
into empname
from employees
where employee_id = (select manager_id
from employees
where last_name = empname
);
end $
delimiter ;
set @empname = 'Abel';
call show_mgr_name(@empname);
select @empname;
#1.
delimiter $
create procedure insert_user(in username varchar(15),in pwd varchar(15))
bengin
insert into admin(username,paw)
values(username,paw);
end
delimiter ;
call insert_user('123','3456');
#2.
delimiter $
create procedure get_phone(in id int,out name varchar(15),out phone varchar(15))
bengin
select b.nane,b.phone into name phone
from beauty b
where b.id=id;
end
delimiter ;
call get_phone(3,@name,@phone);
select @name,@phone;
2.存储函数
1.存储函数的创建
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END
①参数列表：总是默认为IN参数
②RETURNS type 语句表示函数返回数据的类型。
函数体必须包含一个 RETURN value 语句。
③characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同
④函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略 BEGIN…END
④若在创建存储函数中报错“ you might want to use the less safe log_bin_trust_function_creators variable ”，有两种处理方法
#方式一：
加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA |MODIFIES SQL DATA}”
#方式二：
在创建函数前加上该语句
SET GLOBAL log_bin_trust_function_creators = 1;
2.存储函数的调用
SELECT 函数名(实参列表);
3.举例
①一般用于查询结果为一个值并返回
#创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型。
delimiter $
create function email_by_name()
returns varchar(25)
deterministic
contains sql
reads sql data
begin
return (select email from employees where last_name = 'Abel');
end $
delimiter ;
#调用
select email_by_name();
#创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型为字符串型。
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //
DELIMITER ;
SET @emp_id = 102;
SELECT email_by_id(102);
#创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。
DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT '查询部门平均工资'
BEGIN
RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //
DELIMITER ;
SET @dept_id = 50;
SELECT count_by_id(@dept_id);
3.存储过程和存储函数的查看、修改和删除
1.查看
#1.查看存储过程和函数的创建信息
存储过程的查看
SHOW CREATE PROCEDURE 存储过程名;
存储函数的查看
SHOW CREATE FUNCTION 存储函数名;
#2.查看存储过程和函数的状态信息，如果不加like则会查询数据库中所有的信息
存储过程的查看
SHOW PROCEDURE STATUS [LIKE '存储函数名'];
存储函数的查看
SHOW PROCEDURE STATUS [LIKE '存储函数名'];
#3.从information_schema.Routines表中查看存储过程和函数的信息
MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];--出现存储过程与存储函数同名的情况需要加上and的内容，红字要大写
2.修改
#修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。
ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...];
#案例：修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行
ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER ;
3.删除
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名;
DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;
九、变量、存储过程与游标
1.变量的分类
①变量分为系统变量(全局系统变量、会话系统变量)与用户变量(会话用户变量、局部变量)
2.系统变量
1.查看变量
①系统变量：以@@开头，@@global仅用于标记全局系统变量，@@session仅用于标记会话系统变量，@@标记的变量会先从会话系统变量里找，若找不到再在全局系统变量中找
#1.查看所有全局变量
show global variables;
#2.查看所有会话变量
show session variables;
show variables;--默认查询的使会话变量
#3.查看满足条件的会话(全局)变量
show global(session) variables like 'admin_%';
show variables like 'character_%';
#4.查看指定的系统变量的值
SELECT @@global.变量名;
select @@global.max_connections;
#5.查看指定的会话变量的值(session的会话名可以写既是全局的又是会话的也可以写只是会话的)
SELECT @@session.变量名;
select @@session.character_set_client;--既是系统又是全局变量
select @@session.pseudo_thread_id;--会话变量
#或者
SELECT @@变量名;
select @@session.character_set_client;--先查询会话变量再查询全局变量
2.修改系统变量的值
方式1：修改MySQL配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值
#1.修改系统变量的值--修改值仅针对当前数据可实例是有效的，一旦重启mysql服务就失效了
方式一：
set @@golbal.变量名=变量值;
方式二：
set global 变量名=变量值;
#2.修改会话变量的值--对于当前会话是有效的，一旦结束会话重新建立起新的会话，对于新的会话就失效了
方式1：
SET @@session.变量名=变量值;
方式2：
SET SESSION 变量名=变量值;
3.用户变量
1.说明
①用户变量分为会话用户变量和局部变量
②@主要用于修饰会话用户变量
③会话用户变量：作用域和会话变量一样，只对当前连接会话有效，使用@开头
④局部变量：只能使用在存储过程和存储函数中
2.会话用户变量的使用
#1.用户变量的定义
方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
案例：
set @num1=1;
set @num2=2;
方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
案例：
set @sum3:=@num1+@num2;
SELECT @num := COUNT(*) FROM employees;
SELECT AVG(salary) INTO @avgsalary FROM employees;
#2.查看用户变量的值 （查看、比较、运算等）
SELECT @用户变量
案例：
select @sum,@sum3;
3.局部用户变量的使用
①局部变量必须使用declear声明在begin…end中的首行
BEGIN
#声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];# 如果没有DEFAULT子句，初始值为NULL
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
#局部变量赋值
方式1：一般用于赋简单的值
SET 变量名 = 值;
SET 变量名:=值;
方式2：一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;
#3.使用变量（查看局部变量的值、比较、运算等）
SELECT 变量1,变量2,变量3;
END
案例1：
DELIMITER //
CREATE PROCEDURE set_value()
BEGIN
DECLARE emp_name VARCHAR(25);
DECLARE sal DOUBLE(10,2);
SELECT last_name,salary INTO emp_name,sal
FROM employees
WHERE employee_id = 102;
SELECT emp_name,sal;
END //
DELIMITER ;
案例2：声明两个变量，求和并打印 （分别使用会话用户变量、局部变量的方式实现）
#方式1：使用用户变量
set @num1=1;
set @num2=1;
set @num3:=@num1+@num2;
select @num3;
#方式2：使用局部变量
delimiter $
create procedute add_value()
begin
declear num1 int default 1;
declear num2 int default 2;
declear sum intl
select sun:=m+n;
select sum;
end $
delimiter ;
案例3：创建存储过程“different_salary”查询某员工和他领导的薪资差距，并用IN参数emp_id接收员工id，用OUT参数dif_salary输出薪资差距结果。
DELIMITER //
CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE)
BEGIN
#声明局部变量
DECLARE emp_sal,mgr_sal DOUBLE DEFAULT 0.0;
DECLARE mgr_id INT;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = emp_id;
SELECT salary INTO mgr_sal FROM employees WHERE employees_id=mgr_id;
SET dif_salary=mgr_sal-emp_sal
END //
DELIMITER ;
#调用
SET @emp_id = 102;
CALL different_salary(@emp_id,@diff_sal);
#查看
SELECT @diff_sal
4.程序出错处理机制
1.定义条件
1.定义条件的说明
①定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。
2.定义条件的创建
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）;
#案例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。
declear Field_Not_Be_NULL condition for 1048;
declear Field_Not_Be_NULL condition for SQLSTATE '23000';
#案例2：定义"ERROR 1148(42000)"错误，名称为command_not_allowed。
declear command_not_allowed condition for 1148;
declear command_not_allowed condition for SQLSTATE '42000';
①MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。其中，MySQL_error_code是数值类型错误代码，sqlstate_value是长度为5的字符串类型错误代码。
例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000'是sqlstate_value。
例如，在ERROR 1142（42000）中，1142是MySQL_error_code，'42000'是sqlstate_value。
2.定义处理程序
1.处理程序的定义
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;
#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
①处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。
CONTINUE ：表示遇到错误不处理，继续执行。
EXIT ：表示遇到错误马上退出。
UNDO ：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
②错误类型（即条件）可以有如下取值：
SQLSTATE ‘字符串错误码’ ：表示长度为5的sqlstate_value类型的错误代码；
MySQL_error_code ：匹配数值类型错误代码；
错误名称 ：表示DECLARE … CONDITION定义的错误条件名称。
SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码；
NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码；
SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
③处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“ SET 变量 = 值 ”这样的简单语句，也可以是使用 BEGIN … END 编写的复合语句。
2.案例
#案例：在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到MySQL_error_code值为1048时，执行CONTINUE操作，并且将@proc_value的值设置为-1。
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;
+------+-------------+
| @x | @proc_value |
+------+-------------+
| 3 | -1 |
+------+-------------+
#案例二：创建一个名称为“InsertDataWithCondition”的存储过程，代码如下。在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到sqlstate_value值为23000时，执行EXIT操作，并且将@proc_value的值设置为-1。
#准备工作
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);
DELIMITER //
CREATE PROCEDURE InsertDataWithCondition()
BEGIN
DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000' ;
DECLARE EXIT HANDLER FOR duplicate_entry SET @proc_value = -1;
SET @x = 1;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 2;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 3;
END //
DELIMITER ;
#调用存储过程
CALL InsertDataWithCondition();
SELECT @x,@proc_value;
+------+-------------+
| @x | @proc_value |
+------+-------------+
| 2 | -1 |
+------+-------------+
5.分支结构流程控制
1.流程控制分类
①流程控制分为顺序结构、选择结构、分支结构
②条件判断语句：if语句或case语句
③循环语句：loop、while和repeat语句
④跳转语句：iterate和leave语句
2.分支结构–if
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2];
[ELSE 操作N];
END IF;
#案例1：声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就不变。
delimiter $
create procedure update_salary_by_eid1(in emp_id int)
begin
declear emp_salary double;
declear hire_year double;
select salary into emp_salary from employees where employee_id=emp_id;
select datediff(now(),hrie_date) into hire_year from employees where employee_id=emp_id;
if emp_salary < 8000 and hire_year >5
then update employees set salary = salary + 500;
end if;
end
delimiter ;
#案例2:声明存储过程“update_salary_by_eid2”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于9000元并且入职时间超过5年，就涨薪500元；否则就涨薪100元。
CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT)
BEGIN
DECLARE emp_salary DOUBLE;
DECLARE hire_year DOUBLE;
SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year
FROM employees WHERE employee_id = emp_id;
IF emp_salary < 8000 AND hire_year > 5
THEN UPDATE employees SET salary = salary + 500 WHERE employee_id =
emp_id;
ELSE
UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
END IF;
END //
DELIMITER ;
#案例3：声明存储过程“update_salary_by_eid3”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于9000元，就更新薪资为9000元；薪资如果大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT)
BEGIN
DECLARE emp_salary DOUBLE;
DECLARE bonus DECIMAL(3,2);
SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
IF emp_salary < 9000
THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
ELSEIF emp_salary < 10000 AND bonus IS NULL
THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
ELSE
UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
END IF;
END //
DELIMITER ;
3.分支结构-case
#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
#情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
#案例1：声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于9000元，就更新薪资为9000元；薪资大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元。
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
DECLARE emp_sal DOUBLE;
DECLARE bonus DECIMAL(3,2);
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
CASE
WHEN emp_sal<9000
THEN UPDATE employees SET salary=9000 WHERE employee_id = emp_id;
WHEN emp_sal<10000 AND bonus IS NULL
THEN UPDATE employees SET commission_pct=0.01 WHERE employee_id = emp_id;
ELSE
UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
END CASE;
END //
#案例2：声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。判断该员工的入职年限，如果是0年，薪资涨50；如果是1年，薪资涨100；如果是2年，薪资涨200；如果是3年，薪资涨300；如果是4年，薪资涨400；其他的涨薪500。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
DECLARE emp_sal DOUBLE;
DECLARE hire_year DOUBLE;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT ROUND(DATEDIFF(CURDATE(),hire_date)/365) INTO hire_year FROM employees
WHERE employee_id = emp_id;
CASE hire_year
WHEN 0 THEN UPDATE employees SET salary=salary+50 WHERE employee_id = emp_id;
WHEN 1 THEN UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
WHEN 2 THEN UPDATE employees SET salary=salary+200 WHERE employee_id = emp_id;
WHEN 3 THEN UPDATE employees SET salary=salary+300 WHERE employee_id = emp_id;
WHEN 4 THEN UPDATE employees SET salary=salary+400 WHERE employee_id = emp_id;
ELSE UPDATE employees SET salary=salary+500 WHERE employee_id = emp_id;
END CASE;
END //
DELIMITER ;
6.三种循环结构
1.三种循环结构的说明
1.循环结构一定具备的四个要素
①初始化条件
②循环条件
③循环体
④迭代条件
2.三种循环结构的对比
①这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。
② LOOP：一般用于实现简单的"死"循环
③WHILE：先判断后执行
④REPEAT：先执行后判断，无条件至少执行一次
2.LOOP结构
①LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子 句），跳出循环过程。
[loop_label:] LOOP
循环执行的语句
if 退出条件 then leave loop_label;
end if;
END LOOP [loop_label]
#案例1：使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程。
declear id int default 0;
increase_loop : loop
if id >=10 then leave increast_loop;
end if;
set id = id + 1;
end loop increast_loop;
#案例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程“update_salary_loop()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。
delimiter $
create procedure update_salary_loop(out num int)
begin
declear countm int default 0;
declear avg_salary double;
loop_table : loop
#初始化条件
select avg(salary) into avg_salary from employees;
#循环条件
if avg_salary >= 12000 then leave loop loop_table;
end if;
#循环体
update employees set salary = salary * 1.1;
set countnum = countnum + 1;
#迭代条件
select avg(salary) into avg_salary from employees;--要对avg_salary进行修改，要不avg_salary还是原来的数
end loop loop_label;
set num = countnum;
end $
delimiter ;
3.while结构
①WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如 果为真，就执行循环内的语句，否则退出循环。
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];
#案例1：市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程“update_salary_while()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家降薪，薪资降为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。
delimiter $
create procedure update_salary_while(out mum int)
begin
declear avg_salary doublle;
declear countnum int default 0;
select avg(salary) into avg_salary from employees;
while_label : while avg_salary > 5000 do
update employees set salary =salary * 0.9;
set countnum = countnum + 1;
select avg(salary) into avg_salary from employees;
end while while_label;
set num = countnum;
end $
delimiter ;
4.repeat结构
①REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会 就继续执行循环，直到满足退出条件为止。
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label];
#案例：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程“update_salary_repeat()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。
delimiter $
create procedure update_salary_repeat(out num int)
begin
declear avg_salary double;
declear countnum int default 0;
select avg(salary) into avg_salary from employees;
repeat
update employees set salary = salary * 1.15;
set countnum = countnum + 1;
select avg(salary) into avg_salary from employees;
until avg_salary >=13000
end repeat;
end $
delimiter ;
7.跳转语句
1.跳转语句–leave
①LEAVE语句：可以用在循环语句内，或者以BEGIN和END包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。
LEAVE 标记名
#案例1：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN...END加标记名，并在BEGIN...END中使用IF语句判断num参数的值。如果num<=0，则使用LEAVE语句退出BEGIN...END；如果num=1，则查询“employees”表的平均薪资；如果num=2，则查询“employees”表的最低薪资；如果num>2，则查询“employees”表的最高薪资。
DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_label: BEGIN
IF num<=0 THEN LEAVE begin_label;
ELSEIF num=1 THEN SELECT AVG(salary) FROM employees;
ELSEIF num=2 THEN SELECT MIN(salary) FROM employees;
ELSE SELECT MAX(salary) FROM employees;
END IF;
SELECT COUNT(*) FROM employees;
END //
DELIMITER ;
#案例2：当市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程“leave_while()”，声明OUT参数num，输出循环次数，存储过程中使用WHILE循环给大家降低薪资为原来薪资的90%，直到全公司的平均薪资小于等于10000，并统计循环次数。
DELIMITER //
CREATE PROCEDURE leave_while(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE;#记录平均工资
DECLARE while_count INT DEFAULT 0; #记录循环次数
SELECT AVG(salary) INTO avg_sal FROM employees; #① 初始化条件
while_label:WHILE TRUE DO #② 循环条件
#③ 循环体
IF avg_sal <= 10000 THEN LEAVE while_label;
END IF;
UPDATE employees SET salary = salary * 0.9;
SET while_count = while_count + 1;
#④ 迭代条件
SELECT AVG(salary) INTO avg_sal FROM employees;
END WHILE;
#赋值
SET num = while_count;
END //
DELIMITER ;
2.跳转语句–interate
①ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把ITERATE理解为continue，意思为“再次循环”。
ITERATE label;
#案例：定义局部变量num，初始值为0。循环结构中执行num + 1操作。如果num < 10，则继续执行循环；如果num > 15，则退出循环结构
DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
my_loop:LOOP
SET num = num + 1;
IF num < 10 THEN ITERATE my_loop;
ELSEIF num > 15 THEN LEAVE my_loop;
END IF;
SELECT '尚硅谷：让天下没有难学的技术';--10<=num<=15时执行
END LOOP my_loop;
END //
DELIMITER ;
8.游标
①游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。
②游标的声明放在变量声明之后。
③游标的使用分为四个步骤：声明游标、打开游标、使用游标、关闭游标
④ 声明游标中要使用SELECT语句来获取数据结果集，而此时还没有开始遍历数据，这里select_statement代表的是 SELECT语句，返回一个用于创建游标的结果集。
⑤当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候SELECT语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。
⑥使用某个游标来读取当前行，并且将数据保存到变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。多与循环语句配合使用。
1.声明游标
#MySQL、SQL Server、DB2和MariaDB中声明游标
declear 游标名 cursor for select查询结果集;
#用Oracle或者PostgreSQL中声明游标
declear 游标名 cursor is select查询结果集;
DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;
DECLARE cursor_fruit CURSOR FOR
SELECT f_name, f_price FROM fruits ;
2.打开游标
open 游标名;
3.使用游标
fetch 游标名 into 变量1,变量2;
4.关闭游标
CLOSE 游标名;
#案例：创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到imit_total_salary参数的值，返回累加的人数给total_count。
DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
DECLARE emp_count INT DEFAULT 0; #记录循环个数
#定义游标--查询所有员工的工资并按从高到低排序
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
#打开游标
OPEN emp_cursor;
REPEAT
#使用游标（从游标中获取数据）--配合循环来移动游标，将每一条工资记录放入cursor_salary变量中，再后续操作工对该变量操作
FETCH emp_cursor INTO cursor_salary;
SET sum_salary = sum_salary + cursor_salary;
SET emp_count = emp_count + 1;
UNTIL sum_salary >= limit_total_salary
END REPEAT;
SET total_count = emp_count;
#关闭游标
CLOSE emp_cursor;
END //
DELIMITER ;
9.MySQL8.0的新特性—全局变量的持久化
在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。例如，设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现，使用SET GLOBAL语句设置的变量值只会临时生效 。 数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。 MySQL 8.0版本新增了SET PERSIST 命令。例如，设置服务器的最大连接数为1000，MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。
#原来
SET GLOBAL MAX_EXECUTION_TIME=2000;
#MySQL8.0
SET PERSIST global max_connections = 1000;
#案例：永久更改max_connections的值，将其改为1000
查看全局变量max_connections的值
show variables like '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+------------------------+-------
设置全局变量max_connections的值：
set persist max_connections=1000;
重启MySQL服务器 ，再次查询max_connections的值：
show variables like '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 1000 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
十、触发器
1.触发器的概述
①触发器是由事件来触发某个操作，这些事件包括INSERT 、UPDATE 、DELETE 事件。
②事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。
③当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。
2.触发器的使用
①表名 ：表示触发器监控的对象。
②BEFORE|AFTER ：表示触发的时间。BEFORE表示在事件之前触发；AFTER表示在事件之后触发。
③INSERT|UPDATE|DELETE ：表示触发的事件。
④FOR EACH ROW：每一行出发一次
⑤触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。
⑥new.属性：获取INSERT添加语句的新记录的某个属性。
⑦old.属性：获取DELETE删除语句的新记录的某个属性。
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
BEGIN
触发器执行的语句块;
END
#案例1：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中的t_log字段插入before_insert的日志信息。
delimiter $
create trigger before_insert
before insert on test_trigger
for each row
begin
insert into test_trigger_log(t_lob)
values ('before_insert');
end $
delimiter ;
#向test_triggre表中插入数据，会触发触发器向test_trigger_log表中插入数据
insert into test_trigger(t_note)
values ('Tom');
#查看表
select * from test_trigger;
select * from test_trigger_log;
#案例2：定义触发器“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;--new.属性：获取INSERT添加语句的新记录的某个属性。NEW.manager_id为下面添加记录的103
IF NEW.salary > mgrsalary THEN
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
END IF;
END //
DELIMITER ;
#添加记录1：添加成功--触发器出发了
insert into employees(employee_id,last_name,email,hire_date,job_id,salary,manager_id)
values(300,'TOM','tom126@.com',now(),'AD_DF',8000,103);
#添加记录2：添加失败--触发器出发了
insert into employees(employee_id,last_name,email,hire_date,job_id,salary,manager_id)
values(300,'TOM','tom126@.com',now(),'AD_DF',8000,103);
3.查看触发器
#查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。
#方式1：查看当前数据库的所有触发器的定义
SHOW TRIGGERS\G
#方式2：查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名;
#方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;
4.删除触发器
DROP TRIGGER IF EXISTS 触发器名称;
5.触发器的优点
①触发器可以确保数据的完整性。
每当我们录入、删除和修改一条进货单明细数据的时候，进货单明细表里的数据就会发生变动。这个时候，在进货单头表中的总计数量和总计金额就必须重新计算，否则，进货单头表中的总计数量和总计金 额就不等于进货单明细表中数量合计和金额合计了，这就是数据不一致。规定每当进货单明细表有数据插入、修改和删除的操作时，自动触发更新另一个表的操作
②触发器可以帮助我们记录操作日志。
利用触发器，可以具体记录什么时间发生了什么。比如，记录修改会员储值金额的触发器，就是一个很好的例子。这对我们还原操作执行时的具体场景，更好地定位问题原因很有帮助。
③触发器还可以用在操作数据前，对数据进行合法性检查。
比如，超市进货的时候，需要库管录入进货价格。但是，人为操作很容易犯错误，比如说在录入数量的时候，把条形码扫进去了；录入金额的时候，看串了行，录入的价格远超售价，导致账面上的巨亏…… 这些都可以通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止 错误数据进入系统。
6.触发器的缺点
①触发器最大的一个问题就是可读性差。
②相关数据的变更，可能会导致触发器出错。
7.注意点
如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。
8.案例
create database test17_trigger;
use test17_trigger;
create table emps
as
select employee_id, last_name, salary
from myemployees.employees;
select *
from emps;
#1.复制一张emps的空表emps_back
create table emps_back
as
select *
from emps
where 1 = 2;
#2.查询emps_back表中的数据
select *
from emps_back;
#3.创建触发器emps_insert_trigger，每当向emps表中添加一条记录时，同步将这条记录添加到emps_back表中
delimiter $
create trigger emps_insert_trigger
after insert
on emps
for each row
begin
insert into emps_back
value (new.employee_id, new.last_name, new.salary);
end $
delimiter ;
#4.验证触发器是否起作用
insert into emps
value (1, 'Toms', 18000);
select *
from emps_back;
#4.创建触发器emps_delete_trigger，每当删除emps表中一条记录时，同步将这条记录添加到emps_back表中
delimiter $
create trigger emps_delete_trigger
before delete
on emps
for each row
begin
insert into emps_back
value (old.employee_id, old.last_name, old.salary);
end $
delimiter ;
delete
from emps
where employee_id = 101;
delete
from emps;
select *
from emps_back;
十一、窗口函数
1.窗口函数的语法结构
方式一：
函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]） 别名
方式二：
函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
①OVER关键字：指定函数窗口的范围。
如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。
如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。
②窗口名：为窗口设置一个别名，用来标识窗口。
③parttion by子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。按某一个属性进行分类，相同属性值共用一组需要，属性值发生了序号则从1重新开始
④ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
⑤FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。
2.序号函数
#数据准备
CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT,
category VARCHAR(15),
NAME VARCHAR(30),
price DECIMAL(10,2),
stock INT,
upper_time DATETIME
);
INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
VALUES
(1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');
1.row_number()
①row_number()函数能够对数据中的序号进行顺序显示。分组后，窗口函数可以在每个分组中分别执行。按某一个属性进行分类，相同属性值共用一组需要，属性值发生了序号则从1重新开始
#1.查询goods数据表中每个商品分类下价格降序排列的各个商品信息。
select row_number() over(parttion by category_id order by price desc) as row_num
,id, category_id, category, NAME, price, stock
from goods;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 3 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
| 5 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |
| 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 |
| 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 |
| 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 |
| 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 |
| 5 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 |
| 6 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 |
+---------+----+-------------+---------------+------------+---------+-------+
#2.查询 goods 数据表中每个商品分类下价格最高的3种商品信息。
SELECT *
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) ASrow_num,
id, category_id, category, NAME, price, stock
FROM goods) t
WHERE row_num <= 3;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 3 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 |
| 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 |
| 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 |
+---------+----+-------------+---------------+------------+----------+-------+
2.rank()
①使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。
#1.使用RANK()函数获取goods数据表中各类别的价格从高到低排序的各商品信息。
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock
FROM goods;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |#两个价格相同的数据使用相同需要，下一条数据从4开始
| 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
| 5 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |
| 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 |
| 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 |
| 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 |
| 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 |
| 4 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 |
| 6 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 |
+---------+----+-------------+---------------+------------+---------+-------+
#2.使用RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。
SELECT *
FROM(SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock
FROM goods) t
WHERE category_id = 1 AND row_num <= 4;
+---------+----+-------------+---------------+----------+--------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+----------+--------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
+---------+----+-------------+---------------+----------+--------+-------+
3.dense_rank()
①DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。
#1.使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,id, category_id, category, NAME, price, stock
FROM goods;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+------------+---------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
| 4 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 |
| 5 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |
| 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 |
| 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 |
| 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 |
| 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 |
| 4 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 |
| 5 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 |
+---------+----+-------------+---------------+------------+---------+-------+
#2.使用DENSE_RANK()函数获取 goods 数据表中类别为“女装/女士精品”的价格最高的4款商品信息。
SELECT *
FROM(SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,id, category_id, category, NAME, price, stock
FROM goods) t
WHERE category_id = 1 AND row_num <= 3;
+---------+----+-------------+---------------+----------+--------+-------+
| row_num | id | category_id | category | NAME | price | stock |
+---------+----+-------------+---------------+----------+--------+-------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
+---------+----+-------------+---------------+----------+--------+-------+
3.分布函数
1.precent_rank()
①PERCENT_RANK()函数是等级值百分比函数。计算方式为：(rank - 1) / (rows - 1)
其中rank为该记录对应rank函数的序号；rows为查询出来的当前窗口的总记录条数
#1.计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。
写法一：
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;
写法二：将over后括号中的内容单独声明并使用别名表示，并使用别名进行引用
SELECT RANK() OVER w AS r,
PERCENT_RANK() OVER w AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);
+---+-----+----+-------------+---------------+----------+--------+-------+
| r | pr | id | category_id | category | NAME | price | stock |
+---+-----+----+-------------+---------------+----------+--------+-------+
| 1 | 0 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 0.2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 0.2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 4 | 0.6 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
| 5 | 0.8 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 1 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |
+---+-----+----+-------------+---------------+----------+--------+-------+
2.cume_dist()
①CUME_DIST()函数主要用于查询小于或等于某个值的比例。
#1.查询goods数据表中小于或等于当前价格的比例。
SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
id, category, NAME, price
FROM goods;
+---------------------+----+---------------+------------+---------+
| cd | id | category | NAME | price |
+---------------------+----+---------------+------------+---------+
| 0.16666666666666666 | 5 | 女装/女士精品 | 百褶裙 | 29.90 |
| 0.3333333333333333 | 1 | 女装/女士精品 | T恤 | 39.90 |
| 0.5 | 2 | 女装/女士精品 | 连衣裙 | 79.90 |
| 0.8333333333333334 | 3 | 女装/女士精品 | 卫衣 | 89.90 |
| 0.8333333333333334 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 |
| 1 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 |#同id中小于或等于该商品价格(399.90)的比例为100%
| 0.16666666666666666 | 9 | 户外运动 | 登山杖 | 59.90 |
| 0.5 | 7 | 户外运动 | 自行车 | 399.90 |
| 0.5 | 10 | 户外运动 | 骑行装备 | 399.90 |
| 0.6666666666666666 | 12 | 户外运动 | 滑板 | 499.90 |
| 0.8333333333333334 | 11 | 户外运动 | 运动外套 | 799.90 |
| 1 | 8 | 户外运动 | 山地自行车 | 1399.90 |
+---------------------+----+---------------+------------+---------+
3.lag(exp,n)
①LAG(expr,n)函数返回当前行的前n行的exp属性的值。
#1.查询goods数据表中前一个商品价格与当前商品价格的差值。from中看作一个新的表，差值为将from表中price减去pre_price
SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
FROM (SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;#完整写法为：where 1=1 WINDOW w AS (PARTITION BY category_id ORDER BY price)
+----+---------------+------------+---------+-----------+------------+
| id | category | NAME | price | pre_price | diff_price |
+----+---------------+------------+---------+-----------+------------+
| 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女装/女士精品 | T恤 | 39.90 | 29.90 | 10.00 |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 40.00 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 79.90 | 10.00 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 89.90 | 0.00 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 89.90 | 310.00 |
| 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL |
| 7 | 户外运动 | 自行车 | 399.90 | 59.90 | 340.00 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 0.00 |
| 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 100.00 |
| 11 | 户外运动 | 运动外套 | 799.90 | 499.90 | 300.00 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 799.90 | 600.00 |
+----+---------------+------------+---------+-----------+------------+
4.lead(exp,n)
①LEAD(expr,n)函数返回当前行的后n行的属性expr的值。
SELECT id, category, NAME, behind_price, price,behind_price - price ASdiff_price
FROM(SELECT id, category, NAME, price,LEAD(price, 1) OVER w AS behind_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
+----+---------------+------------+--------------+---------+------------+
| id | category | NAME | behind_price | price | diff_price |
+----+---------------+------------+--------------+---------+------------+
| 5 | 女装/女士精品 | 百褶裙 | 39.90 | 29.90 | 10.00 |
| 1 | 女装/女士精品 | T恤 | 79.90 | 39.90 | 40.00 |
| 2 | 女装/女士精品 | 连衣裙 | 89.90 | 79.90 | 10.00 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 89.90 | 0.00 |
| 4 | 女装/女士精品 | 牛仔裤 | 399.90 | 89.90 | 310.00 |
| 6 | 女装/女士精品 | 呢绒外套 | NULL | 399.90 | NULL |
| 9 | 户外运动 | 登山杖 | 399.90 | 59.90 | 340.00 |
| 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 0.00 |
| 10 | 户外运动 | 骑行装备 | 499.90 | 399.90 | 100.00 |
| 12 | 户外运动 | 滑板 | 799.90 | 499.90 | 300.00 |
| 11 | 户外运动 | 运动外套 | 1399.90 | 799.90 | 600.00 |
| 8 | 户外运动 | 山地自行车 | NULL | 1399.90 | NULL |
+----+---------------+------------+--------------+---------+------------+
4.首尾函数
1.FIRST_VALUE(expr)
①FIRST_VALUE(expr)函数返回每组第一个expr属性的值。如果没有分组则显示整体的第一个
#1.按照价格排序，查询第1个商品的价格信息。
SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS first_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+---------------+------------+---------+-------+-------------+
| id | category | NAME | price | stock | first_price |
+----+---------------+------------+---------+-------+-------------+
| 5 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 |
| 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 29.90 |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 29.90 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 29.90 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 29.90 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 29.90 |
| 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 |
| 7 | 户外运动 | 自行车 | 399.90 | 1000 | 59.90 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 59.90 |
| 12 | 户外运动 | 滑板 | 499.90 | 1200 | 59.90 |
| 11 | 户外运动 | 运动外套 | 799.90 | 500 | 59.90 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 59.90 |
+----+---------------+------------+---------+-------+-------------+
2.LAST_VALUE(expr)
①LAST_VALUE(expr)函数返回每组最后一个expr属性的值。如果没有分组则显示整体的最后一个
#2.按照价格排序，查询最后一个商品的价格信息。
SELECT id, category, NAME, price, stock,LAST_VALUE(price) OVER w AS last_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+---------------+------------+---------+-------+------------+
| id | category | NAME | price | stock | last_price |
+----+---------------+------------+---------+-------+------------+
| 5 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 |
| 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 39.90 |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 79.90 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 89.90 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 89.90 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 399.90 |
| 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 |
| 7 | 户外运动 | 自行车 | 399.90 | 1000 | 399.90 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 399.90 |
| 12 | 户外运动 | 滑板 | 499.90 | 1200 | 499.90 |
| 11 | 户外运动 | 运动外套 | 799.90 | 500 | 799.90 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 1399.90 |
+----+---------------+------------+---------+-------+------------+
5.其他函数
1.NTH_VALUE(expr,n)
①NTH_VALUE(expr,n)函数返回第n个expr的值。有分组按分组无分组按全体
#1.查询goods数据表中排名第2和第3的价格信息。
SELECT id, category, NAME, price,
NTH_VALUE(price,2) OVER w AS second_price,#按id分组，然后在该分组中找第二第三条，第一条时第二条数据还未加载因此未null
NTH_VALUE(price,3) OVER w AS third_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+---------------+------------+---------+--------------+-------------+
| id | category | NAME | price | second_price | third_price |
+----+---------------+------------+---------+--------------+-------------+
| 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女装/女士精品 | T恤 | 39.90 | 39.90 | NULL |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 79.90 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 39.90 | 79.90 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 39.90 | 79.90 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 39.90 | 79.90 |
| 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL |
| 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 399.90 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 399.90 |
| 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 399.90 |
| 11 | 户外运动 | 运动外套 | 799.90 | 399.90 | 399.90 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 399.90 | 399.90 |
+----+---------------+------------+---------+--------------+-------------+
2.NTILE(n)
①NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。先将数据标号为n，然后从头开始再分配序号
#1.将goods表中的商品按照价格分为3组。
SELECT NTILE(3) OVER w AS nt,id, category, NAME, price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+----+---------------+------------+---------+
| nt | id | category | NAME | price |
+----+----+---------------+------------+---------+
| 1 | 5 | 女装/女士精品 | 百褶裙 | 29.90 |
| 1 | 1 | 女装/女士精品 | T恤 | 39.90 |
| 2 | 2 | 女装/女士精品 | 连衣裙 | 79.90 |
| 2 | 3 | 女装/女士精品 | 卫衣 | 89.90 |
| 3 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 |
| 3 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 |
| 1 | 9 | 户外运动 | 登山杖 | 59.90 |
| 1 | 7 | 户外运动 | 自行车 | 399.90 |
| 2 | 10 | 户外运动 | 骑行装备 | 399.90 |
| 2 | 12 | 户外运动 | 滑板 | 499.90 |
| 3 | 11 | 户外运动 | 运动外套 | 799.90 |
| 3 | 8 | 户外运动 | 山地自行车 | 1399.90 |
+----+----+---------------+------------+---------+
6.小结
窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。
7.案例
#第18章_MySQL8.0的其它新特性的课后练习
#1. 创建students数据表，如下
CREATE DATABASE test18_mysql8;
USE test18_mysql8;
CREATE TABLE students(
id INT PRIMARY KEY AUTO_INCREMENT,
student VARCHAR(15),
points TINYINT
);
#2. 向表中添加数据如下
INSERT INTO students(student,points)
VALUES
('张三',89),
('李四',77),
('王五',88),
('赵六',90),
('孙七',90),
('周八',88);
SELECT * FROM students;
#3. 分别使用RANK()、DENSE_RANK() 和 ROW_NUMBER()函数对学生成绩降序排列情况进行显示
#方式1：
SELECT
ROW_NUMBER() OVER (ORDER BY points DESC) AS "排序1",
RANK() OVER (ORDER BY points DESC) AS "排序2",
DENSE_RANK() OVER (ORDER BY points DESC) AS "排序3",
student,points
FROM students;
#方式2：
SELECT
ROW_NUMBER() OVER w AS "排序1",
RANK() OVER w AS "排序2",
DENSE_RANK() OVER w AS "排序3",
student,points
FROM students WINDOW w AS (ORDER BY points DESC);
十二、公用表表达式
1.公用表表达式的说明
①公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）。CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的， CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询
②依据语法结构和执行方式的不同，公用表表达式分为普通公用表表达式和递归公用表表达式
2.普通公用表表达式
①普通公用表表达式类似于子查询，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用。
WITH CTE名称
AS （子查询）
SELECT|DELETE|UPDATE 语句;
#案例：查询员工所在的部门的详细信息。
WITH emp_dept_id
AS (SELECT DISTINCT department_id FROM employees)
SELECT *
FROM departments d JOIN emp_dept_id e
ON d.department_id = e.department_id;
3.递归公用表表达式
1.递归公用表表达式的说明
①递归公用表表达式也是一种公用表表达式，只不过，除了普通公用表表达式的特点以外，它还有自己的特点，就是可以调用自己。
2.递归公用表表达式的使用
①递归公用表表达式由 2 部分组成，分别是种子查询和递归查询，中间通过关键字 UNION [ALL]进行连接。 这里的种子查询，意思就是获得递归的初始值。这个查询只会运行一次，以创建初始数据集，之后递归查询会一直执行，直到没有任何新的查询数据产生，递归返回。
WITH RECURSIVE
CTE名称 AS （子查询）
SELECT|DELETE|UPDATE 语句
#案例：找出employees表中所有的下下属。针对于我们常用的employees表，包含employee_id，last_name和manager_id三个字段。如果a是b的管理者，那么，我们可以把b叫做a的下属，如果同时b又是c的管理者，那么c就是b的下属，是a的下下属。
用递归公用表表达式中的种子查询，找出初代管理者。字段 n 表示代次，初始值为 1，表示是第一代管理者。
用递归公用表表达式中的递归查询，查出以这个递归公用表表达式中的人为管理者的人，并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了，递归返回。
在最后的查询中，选出所有代次大于等于 3 的人，他们肯定是第三代及以上代次的下属了，也就是
下下属了。这样就得到了我们需要的结果集。
WITH RECURSIVE cte
AS
(
SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte
ON (a.manager_id = cte.employee_id) -- 递归查询，找出以递归公用表表达式的人为领导的人
)
SELECT employee_id,last_name FROM cte WHERE n >= 3
1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 39.90 |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 79.90 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 89.90 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 89.90 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 399.90 |
| 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 |
| 7 | 户外运动 | 自行车 | 399.90 | 1000 | 399.90 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 399.90 |
| 12 | 户外运动 | 滑板 | 499.90 | 1200 | 499.90 |
| 11 | 户外运动 | 运动外套 | 799.90 | 500 | 799.90 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 1399.90 |
±—±--------------±-----------±--------±------±-----------+
#### 5.其他函数
##### 1.NTH_VALUE(expr,n)
①NTH_VALUE(expr,n)函数返回第n个expr的值。有分组按分组无分组按全体
```mysql
#1.查询goods数据表中排名第2和第3的价格信息。
SELECT id, category, NAME, price,
NTH_VALUE(price,2) OVER w AS second_price,#按id分组，然后在该分组中找第二第三条，第一条时第二条数据还未加载因此未null
NTH_VALUE(price,3) OVER w AS third_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+---------------+------------+---------+--------------+-------------+
| id | category | NAME | price | second_price | third_price |
+----+---------------+------------+---------+--------------+-------------+
| 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女装/女士精品 | T恤 | 39.90 | 39.90 | NULL |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 79.90 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 39.90 | 79.90 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 39.90 | 79.90 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 39.90 | 79.90 |
| 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL |
| 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 399.90 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 399.90 |
| 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 399.90 |
| 11 | 户外运动 | 运动外套 | 799.90 | 399.90 | 399.90 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 399.90 | 399.90 |
+----+---------------+------------+---------+--------------+-------------+
2.NTILE(n)
①NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。先将数据标号为n，然后从头开始再分配序号
#1.将goods表中的商品按照价格分为3组。
SELECT NTILE(3) OVER w AS nt,id, category, NAME, price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+----+---------------+------------+---------+
| nt | id | category | NAME | price |
+----+----+---------------+------------+---------+
| 1 | 5 | 女装/女士精品 | 百褶裙 | 29.90 |
| 1 | 1 | 女装/女士精品 | T恤 | 39.90 |
| 2 | 2 | 女装/女士精品 | 连衣裙 | 79.90 |
| 2 | 3 | 女装/女士精品 | 卫衣 | 89.90 |
| 3 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 |
| 3 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 |
| 1 | 9 | 户外运动 | 登山杖 | 59.90 |
| 1 | 7 | 户外运动 | 自行车 | 399.90 |
| 2 | 10 | 户外运动 | 骑行装备 | 399.90 |
| 2 | 12 | 户外运动 | 滑板 | 499.90 |
| 3 | 11 | 户外运动 | 运动外套 | 799.90 |
| 3 | 8 | 户外运动 | 山地自行车 | 1399.90 |
+----+----+---------------+------------+---------+
6.小结
窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。
7.案例
#第18章_MySQL8.0的其它新特性的课后练习
#1. 创建students数据表，如下
CREATE DATABASE test18_mysql8;
USE test18_mysql8;
CREATE TABLE students(
id INT PRIMARY KEY AUTO_INCREMENT,
student VARCHAR(15),
points TINYINT
);
#2. 向表中添加数据如下
INSERT INTO students(student,points)
VALUES
('张三',89),
('李四',77),
('王五',88),
('赵六',90),
('孙七',90),
('周八',88);
SELECT * FROM students;
#3. 分别使用RANK()、DENSE_RANK() 和 ROW_NUMBER()函数对学生成绩降序排列情况进行显示
#方式1：
SELECT
ROW_NUMBER() OVER (ORDER BY points DESC) AS "排序1",
RANK() OVER (ORDER BY points DESC) AS "排序2",
DENSE_RANK() OVER (ORDER BY points DESC) AS "排序3",
student,points
FROM students;
#方式2：
SELECT
ROW_NUMBER() OVER w AS "排序1",
RANK() OVER w AS "排序2",
DENSE_RANK() OVER w AS "排序3",
student,points
FROM students WINDOW w AS (ORDER BY points DESC);
5741
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
