MySQL

最新推荐文章于 2025-02-11 07:15:00 发布

LEewhITe2003

最新推荐文章于 2025-02-11 07:15:00 发布

阅读量2.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/LEewhITe2003/article/details/123969864

MySQL 专栏收录该内容

1 篇文章

订阅专栏

MySQL

findinSet函数

疑问：为什么要findinset呢？

答：可以使用查询变量，[FIND_IN_SET使用方法总结](https://blog.youkuaiyun.com/c_base_jin/article/details/74358235?ops_request_misc=&request_id=&biz_id=102&utm_term=find in set&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduweb~default-1-74358235.nonecase&spm=1018.2226.3001.4187)

mysql 如何在in 中放字符串

使用 FIND_IN_SET(id, ‘1,2,3,4’) 函数

也就是说select * from table where FIND_IN_SET(id, ‘1,2,3,4’) 即可

Max函数

MySQL max()函数

MySQL Max()函数的坑

所以需要转型再Max：Mysql使用max函数查询不到最大值

Mysql Max函数坑查询出来的是最大值，但不是最大值的那条记录

Round函数

[ mysql中的round函数](https://blog.youkuaiyun.com/chinabestchina/article/details/72799826?ops_request_misc=&request_id=&biz_id=102&utm_term=mysql round函数&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduweb~default-2-72799826.nonecase&spm=1018.2226.3001.4187)

mysql round函数隐藏问题:：精确值和近似值

字符串下标从1开始；

On 和 Where

先看好文：[MYSQL 表连接 ON AND 和ON WHERE 的区别](https://blog.youkuaiyun.com/QQQQQQ654/article/details/77991690?ops_request_misc=&request_id=&biz_id=102&utm_term=mysql 的 on&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduweb~default-4-77991690.142^v5control,143^v6register&spm=1018.2226.3001.4187)

补充：

MYSQL on和where的区别

小结：比如两个表进行left join连接查询结果，where会对查询的结果进行过滤； on会返回驱动表的所有数据。

left join按照条件关联，不管是否为真，都返回左边表的数据(没有数据用null填充）。

TRUNCATE

较全面的演示：[ MySQL数据库数值函数#round()与truncate()函数“舍入”的区别](https://blog.youkuaiyun.com/LINHONG_1994/article/details/98222176?ops_request_misc=&request_id=&biz_id=102&utm_term=mysql TRUNCATE(x,y)&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduweb~default-3-98222176.142^v5control,143^v6register&spm=1018.2226.3001.4187)

MySQL四舍五入函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)

TRUNCATE(56.78,-1)截去小数点左边第1位后面的值，并将整数部分的1位数字置0，结果为50。

Case When

MySQL 的CASE WHEN 语句使用说明

补充：MySQL中case when的基本用法总结

Group BY

select中出现的非组函数的字段必须声明在Group By中。反之Group By中的字段可以不出现在select中；

子查询

#题目：查询与141号员工的manager_id和department_id相同的其他员工
#的employee_id，manager_id，department_id。
#方式1：
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (
		    SELECT manager_id
		    FROM employees
		    WHERE employee_id = 141
		   )
AND department_id = (
		    SELECT department_id
		    FROM employees
		    WHERE employee_id = 141
		   )
AND employee_id <> 141;

#方式2：了解
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) = (
				    SELECT manager_id,department_id
			            FROM employees
				    WHERE employee_id = 141
				   )
AND employee_id <> 141;

Having

解决where不能有聚合函数的问题，和Group by一起使用；

单/多行子查询

in

[注意mysql的not in查询值中存在null值时返回的查询结果会一直为空](https://blog.youkuaiyun.com/eininotop/article/details/79371966?ops_request_misc=&request_id=&biz_id=102&utm_term=mysql in 空值&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduweb~default-1-79371966.142^v5control,143^v6register&spm=1018.2226.3001.4187)

但是 in 对null的操作正常；

RollUp

#说明：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
#错误的：
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;

别名的bug

失败：浙江大学用户题目回答情况牛客网 (nowcoder.com)

已解决：

不知道为什么这边在给表取别名的时候只能用 as，不能加"";

注意：group by 和order by 不能使用别名

exist和in

Sql 语句中 IN 和 EXISTS 的区别及应用

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

Order By

ORDER by 后接的字段如果是boolean属性，则false比ture ‘大’！！！！！

Union

字段的顺序

SQL UNION ALL 多个结果字段合并顺序

union的时候，上下两表的字段顺序一定要一样；

Union默认排序吗？

MySQL中union和order by一起使用的方法

order在union前没用，在union后才有用，因为union会自动排序，所以一般连完再排，而不是排完再连；

【SQL 学习】UNION 的排序方式！-阿里云开发者社区 (aliyun.com)

这篇文章说是“全量排序”，也就是逐字段一个个比较；

建库

Use

切换当前数据库；

DESC

描述数据库；

Show Create DATABASE

查看创建数据库的结构

Create DataBase

如果要创建的数据库已经存在，则创建不成功，不会报错。
CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET ‘utf8’;

SHOW TABLES

#查看当前数据库中保存的数据表

SELECT DATABASE()

#查看当前使用的数据库

ALTER DATABASE 表名 CHARACTER SET ‘utf8’

#更改数据库字符集

DROP DATABASE IF EXISTS mytest1;

#如果要删除的数据库存在，则删除成功。如果不存在，则默默结束，不会报错。

建表

#方式1

"白手起家"的方式

CREATE TABLE IF NOT EXISTS myemp1( #需要用户具备创建表的权限。
id INT,
emp_name VARCHAR(15), #使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。
hire_date DATE
);

SHOW CREATE TABLE

#查看创建表的语句结构
SHOW CREATE TABLE myemp1; #如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集。

#方式2

基于现有的表，同时导入数据(可以理解为表的复制、赋值)

CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;

添加一个字段 add

#默认添加到表中的最后一个字段的位置

ALTER TABLE myemp1
ADD salary DOUBLE(10,2);

#添加到表中的第一个字段的位置

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

#添加到表中的指定字段后

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;

修改一个字段的属性 modify

修改一个字段：数据类型、长度、默认值（可略）

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ;

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT ‘aaa’;

重命名一个字段 change

ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);

小改modify，大改change

删除一个字段 Drop

ALTER TABLE myemp1
DROP COLUMN my_email;

重命名表 Rename

#方式1：

RENAME TABLE myemp1
TO myemp11;

#方式2：

ALTER TABLE myemp2
RENAME TO myemp12;

删除表 Drop

不光将表结构删除掉，同时表中的数据也删除掉，释放表空间

DROP TABLE IF EXISTS myemp2;

DROP TABLE IF EXISTS myemp12;

清空表 TRUNCATE

清空表，表示清空表中的所有数据，但是表结构保留。

TRUNCATE TABLE employees_copy;

删除行/列 delete

DELETE FROM table_name where table_name.name=‘dell’

按照where的条件来删除单行，而如果没有where的话，我们就直接删除这一列；

DDL、DML、DCL

DDL:数据定义语言。CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE

DML:数据操作语言。INSERT \ DELETE \ UPDATE \ SELECT （重中之重）

DCL:数据控制语言。COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

运算规则和函数

在SQL中，+没有连接的作用，就表示加法运算。此时，会将字符串转换为数值（隐式转换）

SELECT 100 + ‘1’ # 在Java语言中，结果是：1001。
FROM DUAL;

SELECT 100 + ‘a’ #此时将’a’看做0处理
FROM DUAL;

SELECT 100 + NULL # null值参与运算，结果为null
FROM DUAL;

SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,
100 + 2 * 5 / 2,100 / 3, 100 DIV 0 # 分母如果为0，则结果为null
FROM DUAL;

= 的使用

SELECT 1 = 2,1 != 2,1 = ‘1’,1 = ‘a’,0 = ‘a’ #字符串存在隐式转换。如果转换数值不成功，则看做0
FROM DUAL;

<=> ：安全等于。记忆技巧：为NULL而生。

SELECT 1 <=> 2,1 <=> ‘1’,1 <=> ‘a’,0 <=> ‘a’
FROM DUAL;

Least、GREATEST

SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;

between

#BETWEEN 条件下界1 AND 条件上界2 （查询条件1和条件2范围内的数据，包含边界）
#查询工资在6000 到 8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
where salary between 6000 and 8000;

LIKE :模糊查询

% : 代表不确定个数的字符（0个，1个，或多个）

查询last_name中包含字符’a’的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE ‘%a%’;

_ ：代表一个不确定的字符

#练习：查询第3个字符是’a’的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE ‘__a%’;

看escape:

MySQL中ESCAPE关键字的用法

连接条件 Using

SQL99语法的新特性:USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

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

自然连接 NATURAL JOIN

它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

连接字段 Contact

查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT

SELECT CONCAT(employee_id,‘,’,last_name,‘,’,salary) “OUT_PUT”
FROM employees;

Contact可以拼接 “列”变量，不只是拼接常量；

DateDiff 和 To_DAYS

DATEDIFF(CURDATE(),hire_date) “worked_days”,
TO_DAYS(CURDATE()) - TO_DAYS(hire_date) “worked_days1”

这两个是一样的，datediff是 “前-后”

转化日期

查询员工姓名，hire_date , department_id，满足以下条件：

#雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN (80,90,110)
AND commission_pct IS NOT NULL
#and hire_date >= ‘1997-01-01’; #存在着隐式转换
#and date_format(hire_date,‘%Y-%m-%d’) >= ‘1997-01-01’; # 显式转换操作，格式化：日期—> 字符串
#and date_format(hire_date,‘%Y’) >= ‘1997’; # 显式转换操作，格式化
AND hire_date >= STR_TO_DATE(‘1997-01-01’,‘%Y-%m-%d’);# 显式转换操作，解析：字符串 ----> 日期

null

[mysql in existnull_解决mysql使用not in 包含null值的问题](https://blog.youkuaiyun.com/weixin_39606048/article/details/113288786?ops_request_misc=&request_id=&biz_id=102&utm_term=in exist null&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduweb~default-1-113288786.142^v5control,143^v6register&spm=1018.2226.3001.4187)

–如果null参与算术运算，则该算术表达式的值为null。(例如：+，-，*，/ 加减乘除)

–如果null参与比较运算，则结果可视为false。(例如：>=,<=,<> 大于，小于，不等于)

–如果null参与聚集运算，则聚集函数都置为null(使用isnull(字段,0)等方式可以避免这种情况)。除count(*), count(1), count(0)等之外(count(字段) 字段为null的行不参与计数)。

–如果在not in子查询中有null值的时候,则不会返回数据。

Case When

MySQL中Case When用法详解

CASE可能是 SQL 中被误用最多的关键字之一。虽然你可能以前用过这个关键字来创建字段，但是它还具有更多用法。例如，你可以在 WHERE、GROUP BY和Order By子句中使用CASE。

CASE WHEN 及 SELECT CASE WHEN的用法

多行比较操作符

in、all、any、some这些多行比较操作符，可以放在where里面，不是聚合函数；

查询

先找到目标的一个字段，在从主表中找到该字段，比对后返回一整行全部数据。

约束

如何查看表中的约束

SELECT * FROM information_schema.table_constraints
WHERE table_name = ‘test1’;

information_schema.table_constraints 专门记载各个表的约束；

Unique

Unique的null都是一样的，可以添加两个null，不会冲突；

如果原表数据已经有重复的数据了，那我们不能改变该字段的属性为unique；

#4.1 在CREATE TABLE时添加约束
CREATE TABLE test2(
id INT UNIQUE, #列级约束
last_name VARCHAR(15) ,
email VARCHAR(25),
salary DECIMAL(10,2),
#表级约束
CONSTRAINT uk_test2_email UNIQUE(email)
);

#方式1：有命名的约束
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);

#方式2：无命名约束
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

删除index索引，比较特殊

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

ALTER TABLE test2
DROP INDEX last_name;

ALTER TABLE test2
DROP INDEX uk_test2_sal;

表级约束和列级约束

sql中表级约束和列级约束

#4.3 复合的唯一性约束
CREATE TABLE USER(
id INT,
name VARCHAR(15),
password VARCHAR(25),

#表级约束
CONSTRAINT uk_user_name_pwd UNIQUE(name,password)
);

INSERT INTO USER
VALUES(1,‘Tom’,‘abc’);
#可以成功的：
INSERT INTO USER
VALUES(1,‘Tom1’,‘abc’);

虽然password在表约束那里设置不能重复，但是表约束设置的是“name,password"不能重复，也就是这两个是打包在一起的，虽然password一样但是name不同，所以还是不同，所以添加成功（两个只要有一个不同就是不同）；

not null (非空约束)

#3.1 在CREATE TABLE时添加约束

CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)

);

#3.2 在ALTER TABLE时添加约束

ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;

删除约束

#3.3 在ALTER TABLE时删除约束
ALTER TABLE test1
MODIFY email VARCHAR(25) NULL;

用的是modify，这样子就是允许null了；

主键 Primary Key

primary key (主键约束)

#5.1 在CREATE TABLE时添加约束

#一个表中最多只能有一个主键约束。

主键约束特征：非空且唯一，用于唯一的标识表中的一条记录。

CREATE TABLE test4(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);

表级约束

#MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
CREATE TABLE test5(
id INT ,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
#表级约束
CONSTRAINT pk_test5_id PRIMARY KEY(id) #没有必要起名字。
);

复合主键

#如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

CREATE TABLE user1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),

PRIMARY KEY (NAME,PASSWORD)

);

一般不会删除主键；

自增长列:AUTO_INCREMENT

6.1 在CREATE TABLE时添加

CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
#开发中，一旦主键作用的字段上声明有AUTO_INCREMENT，则我们在添加数据时，就不要给主键
#对应的字段去赋值了。

#6.2 在ALTER TABLE 时添加

ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;

#6.3 在ALTER TABLE 时删除

ALTER TABLE test8
MODIFY id INT ;

直接在modify不声明就行了；

foreign key (外键约束)

外键的意义：

外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。
FOREIGN KEY 约束的主要目的是控制存储在外键表中的数据，但它还可以控制对主键表中数据的修改。例如，如果在 publishers 表中删除一个出版商，而这个出版商的 ID 在 titles 表中记录书的信息时使用了，则这两个表之间关联的完整性将被破坏，titles 表中该出版商的书籍因为与 publishers 表中的数据没有链接而变得孤立了。FOREIGN KEY 约束防止这种情况的发生。如果主键表中数据的更改使之与外键表中数据的链接失效，则这种更改是不能实现的，从而确保了引用完整性。如果试图删除主键表中的行或更改主键值，而该主键值与另一个表的 FOREIGN KEY 约束值相关，则该操作不可实现。若要成功更改或删除 FOREIGN KEY 约束的行，可以先在外键表中删除外键数据或更改外键数据，然后将外键链接到不同的主键数据上去。
外键是用来控制数据库中数据的数据完整性的
就是当你对一个表的数据进行操作
和他有关联的一个或更多表的数据能够同时发生改变
这就是外键的作用

#7.1 在CREATE TABLE 时添加

#主表和从表；父表和子表

#①先创建主表
CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);
#②再创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT,

#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)

);

上述操作报错，因为主表中的dept_id上没有主键约束或唯一性约束。

所以要先建主表，将主表要用作主键的字段设置为主键或者 Unique(唯一约束)，然后才能在子表中的references中选择主键。

添加的时候，主表要先添加，从表才能添加；

删除的时候，从表要先删除，主表才能删除；

约束等级

– Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录

– Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null

– No action方式：如果**子表**中有匹配的记录，则不允许对父表对应候选键进行update/delete操作

– Restrict方式：同no action，都是立即检查外键约束

– Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别

example:

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等级
);

删除外键约束：

删除外键约束

ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;

#再手动的删除外键约束对应的普通索引

ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;

先删除约束，再删除普通索引；

check 约束

MySQL5.7 不支持CHECK约束，MySQL8.0支持CHECK约束。

CREATE TABLE test10(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK(salary > 2000)
);

DEFAULT约束

#9.1 在CREATE TABLE添加约束
CREATE TABLE test11(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) DEFAULT 2000
);

#9.2 在ALTER TABLE添加约束
ALTER TABLE test12
MODIFY salary DECIMAL(8,2) DEFAULT 2500;

#9.3 在ALTER TABLE删除约束
ALTER TABLE test12
MODIFY salary DECIMAL(8,2);

直接不声明就行了；

视图

针对于单表

#情况1：视图中的字段与基表的字段有对应关系
CREATE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary
FROM emps;

用的是as;

给视图的字段命名

#确定视图中字段名的方式1：
CREATE VIEW vu_emp2
AS
SELECT employee_id emp_id,last_name lname,salary #查询语句中字段的别名会作为视图中字段的名称出现
FROM emps

类似参数列表一样命名

#确定视图中字段名的方式2：
CREATE VIEW vu_emp3(emp_id,NAME,monthly_sal) #小括号内字段个数与SELECT中字段个数相同
AS
SELECT employee_id,last_name,salary
FROM emps

视图中的字段在基表中可能没有对应的字段

CREATE VIEW vu_emp_sal
AS
SELECT department_id,AVG(salary) avg_sal
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;

聚合函数不存在原表中，但是可以出现在视图中；

针对于多表

CREATE VIEW vu_emp_dept
AS
SELECT e.employee_id,e.department_id,d.department_name
FROM emps e JOIN depts d
ON e.department_id = d.department_id;

这里和单表一样，没有特别的操作；

#利用视图对数据进行格式化

CREATE VIEW vu_emp_dept1
AS
SELECT CONCAT(e.last_name,‘(’,d.department_name,‘)’) emp_info
FROM emps e JOIN depts d
ON e.department_id = d.department_id;

使用了contact将几个列的查询结果合并到一列中；

用视图建造视图

#2.3 基于视图创建视图

CREATE VIEW vu_emp4
AS
SELECT employee_id,last_name
FROM vu_emp1;

SELECT * FROM vu_emp4;

视图的增改

#更新视图的数据，会导致基表中数据的修改
UPDATE vu_emp1
SET salary = 20000
WHERE employee_id = 101;

#同理，更新表中的数据，也会导致视图中的数据的修改
UPDATE emps
SET salary = 10000
WHERE employee_id = 101;

#删除视图中的数据，也会导致表中的数据的删除
DELETE FROM vu_emp1
WHERE employee_id = 101;

SELECT employee_id,last_name,salary
FROM emps
WHERE employee_id = 101;

不能增改原表中没有的数据，比如聚合函数；

修改视图

#方式1
CREATE OR REPLACE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,email
FROM emps
WHERE salary > 7000;

#方式2 直接重新刷数据
ALTER VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,email,hire_date
FROM emps;

删除视图

SHOW TABLES;

DROP VIEW vu_emp4;

DROP VIEW IF EXISTS vu_emp2,vu_emp3;

存储过程和存储函数

存储过程

#举例1：创建存储过程select_all_data()，查看 employees 表的所有数据

DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM employees;
END $

DELIMITER ;

#2. 存储过程的调用

CALL select_all_data();

带out的输出

#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资
#通过OUT参数“ms”输出

DESC employees;

DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms
FROM employees;
END //

DELIMITER ;

#调用

CALL show_min_salary(@ms);

#查看变量值
SELECT @ms;

带 IN 的输入

#举例5：创建存储过程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 ;

#调用方式1
CALL show_someone_salary(‘Abel’);
#调用方式2
SET @empname := ‘Abel’;
CALL show_someone_salary(@empname);

带 IN 和 OUT

#举例6：创建存储过程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;

带 INOUT

#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，
#输出领导的姓名。

DESC employees;

DELIMITER $

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
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;

存储函数

创建存储函数

#该函数查询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 ;

#举例2：创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，
#并返回，数据类型为字符串型。

#创建函数前执行此语句，保证函数的创建会成功（这样子就可以不写修饰符了。）
SET GLOBAL log_bin_trust_function_creators = 1;

#声明函数

DELIMITER //

CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)

BEGIN
RETURN (SELECT email FROM employees WHERE employee_id = emp_id);

END //

DELIMITER ;

#调用
SELECT email_by_id(101);

//或者

SET @emp_id := 102;
SELECT email_by_id(@emp_id);

存储过程、存储函数的查看

#方式1. 使用SHOW CREATE语句查看存储过程和函数的创建信息

SHOW CREATE PROCEDURE show_mgr_name;

SHOW CREATE FUNCTION count_by_id;

#方式2. 使用SHOW STATUS语句查看存储过程和函数的状态信息

SHOW PROCEDURE STATUS;

SHOW PROCEDURE STATUS LIKE ‘show_max_salary’;

SHOW FUNCTION STATUS LIKE ‘email_by_id’;

#方式3.从information_schema.Routines表中查看存储过程和函数的信息

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME=‘email_by_id’ AND ROUTINE_TYPE = ‘FUNCTION’;

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME=‘show_min_salary’ AND ROUTINE_TYPE = ‘PROCEDURE’;

#4.存储过程、函数的修改
ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT ‘查询最高工资’;

#5. 存储过程、函数的删除

DROP FUNCTION IF EXISTS count_by_id;

DROP PROCEDURE IF EXISTS show_min_salary;

存储过程和存储函数的修改只能改修饰词；

变量

变量 = 系统变量 + 用户自定义变量

系统变量

系统变量 = 全局系统变量 + 会话系统变量

查看系统变量

#查询全局系统变量
SHOW GLOBAL VARIABLES; #617
#查询会话系统变量
SHOW SESSION VARIABLES; #640

SHOW VARIABLES; #默认查询的是会话系统变量

#查询部分系统变量

SHOW GLOBAL VARIABLES LIKE ‘admin_%’;

SHOW VARIABLES LIKE ‘character_%’;

#1.3 查看指定系统变量

SELECT @@global.max_connections;
SELECT @@global.character_set_client;

@@global 是全局， @@session 是会话；

修改系统变量的值

#全局系统变量：
#方式1：
SET @@global.max_connections = 161;
#方式2：
SET GLOBAL max_connections = 171;

#针对于当前的数据库实例是有效的，一旦重启mysql服务，就失效了。

#会话系统变量：
#方式1：
SET @@session.character_set_client = ‘gbk’;
#方式2：
SET SESSION character_set_client = ‘gbk’;

#针对于当前会话是有效的，一旦结束会话，重新建立起新的会话，就失效了。

用户变量

用户变量 = 会话用户变量 + 局部变量

会话用户变量

/*
① 变量的声明和赋值：
#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;

SET @m1 = 1;
SET @m2 := 2;
SET @sum := @m1 + @m2;

#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];

SELECT @count := COUNT(*) FROM employees;

SELECT AVG(salary) INTO @avg_sal FROM employees;

② 使用
SELECT @变量名

SELECT @count;

SELECT @avg_sal;

*/

局部变量

/*
1、局部变量必须满足：
① 使用**DECLARE**声明
② 声明并使用在BEGIN … END 中（使用在存储过程、函数中）
③ DECLARE的方式声明的局部变量必须声明在BEGIN中的首行的位置。

2、声明格式：
DECLARE 变量名类型 [default 值];

如果没有DEFAULT子句，初始值为NULL

3、赋值：
方式1：
SET 变量名=值;
SET 变量名:=值;

方式2：
SELECT 字段名或表达式 INTO 变量名 FROM 表;

4、使用
SELECT 局部变量名;
*/

#举例：
DELIMITER //

CREATE PROCEDURE test_var()

BEGIN
#1、声明局部变量
DECLARE a INT DEFAULT 0;
DECLARE b INT ;

//可以合在一起声明： DECLARE a,b INT DEFAULT 0;
DECLARE emp_name VARCHAR(25);

#2、赋值
SET a = 1;
SET b := 2;

SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101;

#3、使用
SELECT a,b,emp_name;

END //

DELIMITER ;

举例：声明两个变量，求和并打印（分别使用会话用户变量、局部变量的方式实现）

#方式1：使用会话用户变量
SET @v1 = 10;
SET @v2 := 20;
SET @result := @v1 + @v2;

#查看
SELECT @result;

#方式2：使用局部变量
DELIMITER //

CREATE PROCEDURE add_value()
BEGIN
#声明
DECLARE value1,value2,sum_val INT;

#赋值
SET value1 = 10;
SET value2 := 100;

SET sum_val = value1 + value2;
#使用
SELECT sum_val;

END //

DELIMITER ;

#调用存储过程
CALL add_value();

举例：创建存储过程“different_salary”查询某员工和他领导的薪资差距，并用IN参数emp_id接收员工id，
用OUT参数dif_salary输出薪资差距结果。

DELIMITER //

CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE)
BEGIN
#分析：查询出emp_id员工的工资;查询出emp_id员工的管理者的id;查询管理者id的工资;计算两个工资的差值

#声明变量
DECLARE emp_sal DOUBLE DEFAULT 0.0; #记录员工的工资
DECLARE mgr_sal DOUBLE DEFAULT 0.0; #记录管理者的工资

DECLARE mgr_id INT DEFAULT 0; #记录管理者的id

#赋值
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 employee_id = mgr_id;

SET dif_salary = mgr_sal - emp_sal;

END //
DELIMITER ;

定义条件与处理程序

类似于Java中的异常；

定义条件

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

举例：定义“Field_Not_Be_NULL”错误名

（与MySQL中违反非空约束的错误类型“ERROR 1048 (23000)”对应）

#方式1：使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

#方式2：使用sqlstate_value，要添上SQLSTATE关键字；
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE ‘23000’;

定义处理程序
#格式：DECLARE 处理方式 HANDLER FOR 错误类型处理语句

处理方式有： continue，exit，undo(撤销/还原），但是MySql不支持undo;

#举例：
#方法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’;

这里exit和continue是随意,没特别的意义；

流程控制

分支结构之 IF

#举例1

DELIMITER //

CREATE PROCEDURE test_if()

BEGIN
#情况1：
declare stu_name varchar(15);
if stu_name is null

then select 'stu_name is null;

end if;

#情况2：二选一
declare email varchar(25) default ‘aaa’;

if email is null

then select ‘email is null’;

else

select ‘email is not null’;

end if;

#情况3：多选一 ELSEIF连着写
DECLARE age INT DEFAULT 20;

IF age > 40
THEN SELECT ‘中老年’;
ELSEIF age > 18
THEN SELECT ‘青壮年’;
ELSEIF age > 8
THEN SELECT ‘青少年’;
ELSE
SELECT ‘婴幼儿’;
END IF;

END //

DELIMITER ;

#调用
CALL test_if();

举例：声明存储过程“update_salary_by_eid2”，定义IN参数emp_id，输入员工编号。
判断该员工薪资如果低于9000元并且入职时间超过5年，就涨薪500元；否则就涨薪100元。

DELIMITER //

CREATE PROCEDURE update_salary_by_eid2(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 DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year FROM employees WHERE employee_id = emp_id;
#判断
IF emp_sal < 9000 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 ;

分支结构之case

举例1:基本使用

DELIMITER //
CREATE PROCEDURE test_case()
BEGIN

#演示1：case … when …then …
declare var int default 2;

case var
when 1 then select ‘var = 1’;
when 2 then select ‘var = 2’;
when 3 then select ‘var = 3’;
else select ‘other value’;
end case;

#演示2：case when … then …
DECLARE var1 INT DEFAULT 10;
CASE
WHEN var1 >= 100 THEN SELECT ‘三位数’;
WHEN var1 >= 10 THEN SELECT ‘两位数’;
ELSE SELECT ‘个数位’;
END CASE;

END //

DELIMITER ;

一种是 case+变量+when ，另外一种是 case when条件；

循环结构之LOOP

举例：

DELIMITER
CREATE PROCEDURE test_loop()
BEGIN
#声明局部变量
DECLARE num INT DEFAULT 1;

loop_label:LOOP
#重新赋值
SET num = num + 1;

//在loop中使用if

IF num >= 10 THEN LEAVE loop_label;
END IF;
END LOOP loop_label;

#查看num
SELECT num;

END //

DELIMITER ;

通过loop_lable来声明loop的名字（可以不声明），Loop 和 End loop来标记开始和结束；

loop没有判断条件，所以得在loop体内手动设置判断条件，leave loop_lable来跳出循环

另注意：每次的条件都要更新；

#举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。
#声明存储过程“update_salary_loop()”，声明OUT参数num，输出循环次数。
#存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平
#均薪资达到12000结束。并统计循环次数。

DELIMITER //

CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
#声明变量
DECLARE avg_sal DOUBLE ; #记录员工的平均工资

DECLARE loop_count INT DEFAULT 0;#记录循环的次数

#① 初始化条件
#获取员工的平均工资
SELECT AVG(salary) INTO avg_sal FROM employees;

loop_lab:LOOP
#② 循环条件
#结束循环的条件
IF avg_sal >= 12000
THEN LEAVE loop_lab;
END IF;

#③ 循环体
#如果低于12000，更新员工的工资
UPDATE employees SET salary = salary * 1.1;

#④ 迭代条件
#更新avg_sal变量的值
SELECT AVG(salary) INTO avg_sal FROM employees;

#记录循环次数
SET loop_count = loop_count + 1;

END LOOP loop_lab;

#给num赋值
SET num = loop_count;

END //

DELIMITER ;

循环结构之WHILE

/*
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];

*/

同样也是上面的例子，大同小异，但是while有判断条件了，同样注意每次条件的更新；

DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)

BEGIN
#声明变量
DECLARE avg_sal DOUBLE ; #记录平均工资
DECLARE while_count INT DEFAULT 0; #记录循环次数

#赋值
SELECT AVG(salary) INTO avg_sal FROM employees;

WHILE avg_sal > 5000 DO
UPDATE employees SET salary = salary * 0.9 ;
SET while_count = while_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees;
END WHILE;

#给num赋值
SET num = while_count;

END //

DELIMITER ;

循环结构之REPEAT

repeat写的是结束的条件，写在until后（也就是repeat体之后）；

所以repeat怎么样都会做，所以相当于do-while；

/*
[repeat_label:] REPEAT
　　　　循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

*/

举例：

DELIMITER //

CREATE PROCEDURE test_repeat()
BEGIN
#声明变量
DECLARE num INT DEFAULT 1;

REPEAT
SET num = num + 1;
UNTIL num >= 10
END REPEAT;

#查看
SELECT num;

END //

LEAVE的使用

相当于break，搭配标签名使用，没有签名不能用；

ITERATE的使用

相当于continue，搭配标签名使用，没有签名不能用；

举例：定义局部变量num，初始值为0。循环结构中执行num + 1操作。

如果num < 10，则继续执行循环；
如果num > 15，则退出循环结构；

DELIMITER //

CREATE PROCEDURE test_iterate()

BEGIN
DECLARE num INT DEFAULT 0;

loop_label:LOOP
#赋值
SET num = num + 1;

IF num < 10
THEN ITERATE loop_label;
ELSEIF num > 15
THEN LEAVE loop_label;
END IF;

SELECT ‘尚硅谷：让天下没有难学的技术’;

END LOOP;

END //

DELIMITER ;

CALL test_iterate();

游标的使用

在局部变量声明之后再声明游标；

游标使用的步骤：
① 声明游标
② 打开游标
③ 使用游标（从游标中获取数据）
④ 关闭游标

举例：创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，
DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，
直到薪资总和达到limit_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_sal DOUBLE DEFAULT 0.0; #记录累加的工资总额
DECLARE emp_sal DOUBLE; #记录每一个员工的工资
DECLARE emp_count INT DEFAULT 0;#记录累加的人数

#1.声明游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;

#2.打开游标
OPEN emp_cursor;

REPEAT
#3.使用游标
FETCH emp_cursor INTO emp_sal;
SET sum_sal = sum_sal + emp_sal;
SET emp_count = emp_count + 1;
UNTIL sum_sal >= limit_total_salary
END REPEAT;

SET total_count = emp_count;

#4.关闭游标
CLOSE emp_cursor;

END //

DELIMITER ;

#调用
CALL get_count_by_limit_total_salary(200000,@total_count);
SELECT @total_count;

declare…cursor for select… （定义）-> open（打开） -> fetch（获取） -> close（关闭）；

SQL存储过程和函数区别

触发器

创建触发器

DELIMITER //

CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN

#查询到要添加的数据的manager的薪资
DECLARE mgr_sal DOUBLE;

SELECT salary INTO mgr_sal FROM employees
WHERE employee_id = **NEW.**manager_id;

IF NEW.salary > mgr_sal
THEN SIGNAL SQLSTATE ‘HY000’ SET MESSAGE_TEXT = ‘薪资高于领导薪资错误’;

END IF;

END //

DELIMITER ;

new就是表示要插入的信息；

before/after来控制在插入前还是插入后触发；

for each row表示对每行数据都执行；

查看/删除触发器

#① 查看当前数据库的所有触发器的定义
SHOW TRIGGERS;

#② 方式2：查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER salary_check_trigger;

#③ 方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;

#3. 删除触发器
DROP TRIGGER IF EXISTS after_insert_test_tri;

练习：

创建触发器emps_insert_trigger，每当向emps表中添加一条记录时，同步将这条记录
添加到emps_back表中

DELIMITER //

CREATE TRIGGER emps_insert_trigger
AFTER INSERT ON emps
FOR EACH ROW
BEGIN
#将新添加到emps表中的记录添加到emps_back表中
INSERT INTO emps_back(employee_id,last_name,salary)
VALUES(**NEW.**employee_id, **NEW.**last_name, **NEW.**salary);
END //

DELIMITER ;

创建触发器emps_del_trigger，每当向emps表中删除一条记录时，同步将删除的这条记录添加到emps_back1表中

DELIMITER //

CREATE TRIGGER emps_del_trigger
BEFORE DELETE ON emps
FOR EACH ROW
BEGIN
#将emps表中删除的记录，添加到emps_back1表中。
INSERT INTO emps_back1(employee_id,last_name,salary)
VALUES(**OLD.**employee_id,**OLD.**last_name,**OLD.**salary);
END //

DELIMITER ;

这边出现新的关键字：OLD，和new对应（因为原先的表中已经有了这条数据了）；

MySql8.0新特性1-窗口函数

序号函数

1．ROW_NUMBER()函数

ROW_NUMBER()函数能够对数据中的序号进行顺序显示。

举例：查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。

SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC)

AS row_num, id, category_id, category, NAME, price, stock

FROM goods;

举例：查询 goods 数据表中每个商品分类下价格最高的3种商品信息。

(类比子查询，在上一题的基础上截取表)

SELECT * FROM

( SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC)

AS row_num, id, category_id, category, NAME, price, stock

FROM goods) t

WHERE row_num <= 3;

2．RANK()函数

使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。

举例：使用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;

看看表中画线的地方，这里就说明了相同的排序会使用一样的id，之后会跳过某些序号；

3．DENSE_RANK()函数

函数 DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。

举例：使用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;

可以看到，使用DENSE_RANK()函数得出的行号为1、2、2、3，相同价格的商品序号相同，后面的商品序号是连续的，并且没有跳过重复的序号。

分布函数

1．PERCENT_RANK()函数

PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。

(rank - 1) / (rows - 1)

其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数。

举例：计算 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;

这边的PERCENT_RANK()是不断累积的，所以最后会等于1；

2．CUME_DIST()函数

CUME_DIST()函数主要用于查询小于或等于某个值的比例。

（小于等于<= 的条数) / (该类的总数) = 比例；

举例：查询goods数据表中小于或等于当前价格的比例。

SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC)

AS cd, id, category, NAME, price

FROM goods;

前后函数

1．LAG(expr,n)函数

LAG(expr,n)函数返回当前行的前n行的expr的值。

举例：查询goods数据表中前一个商品价格与当前商品价格的差值。

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;

LAG(price,1) 返回前一行price字段的记录；

over w 和 window w是对应的，这样子就拆开写窗口函数了，避免代码过于冗长；

2．LEAD(expr,n)函数

LEAD(expr,n)函数返回当前行的后n行的expr的值。（和LAG恰好相反）

举例：查询goods数据表中后一个商品价格与当前商品价格的差值。

SELECT id, category, NAME, behind_price, price,behind_price - price AS diff_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;

访问函数

1．FIRST_VALUE(expr)函数

FIRST_VALUE(expr)函数返回第一个expr的值。

举例：按照价格排序，查询第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);

2．LAST_VALUE(expr)函数

LAST_VALUE(expr)函数返回最后一个expr的值。

举例：按照价格排序，查询最后一个商品的价格信息。

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);

3．NTH_VALUE(expr,n)函数

NTH_VALUE(expr,n)函数返回第n个expr的值。

举例：查询goods数据表中排名第2和第3的价格信息。

SELECT id, category, NAME, price,

NTH_VALUE(price,2) OVER w AS second_price,

NTH_VALUE(price,3) OVER w AS third_price

FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

为什么会有null呢，因为这个排名不是“全排名”，这个排名的范围只包括当前条和当前条之前的数据，不包括之后的数据；

分桶函数

NTILE(n)函数

NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。

举例：将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);

分桶的逻辑暂且不明；

MySql8.0新特性2-公用表表达式

普通公用表表达式

普通公用表表达式的语法结构是：普通公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用。

（即：可以多次使用、复用的子查询。）

举例：查询员工所在的部门的详细信息。

WITH emp_dept_id

//AS(里面和子查询一样)

AS (SELECT DISTINCT department_id FROM employees)

SELECT *

FROM departments d JOIN emp_dept_id e

ON d.department_id = e.department_id;

例子说明，公用表表达式可以起到子查询的作用。以后如果遇到需要使用子查询的场景，你可以在查询之前，先定义公用表表达式，然后在查询中用它来代替子查询。而且，跟子查询相比，公用表表达式有一个优点，就是定义过公用表表达式之后的查询，可以像一个表一样多次引用公用表表达式，而子查询则不能。
递归公用表表达式