MySQL笔记（基本操作）

参考教程：

黑马程序员MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程

DDL

列出现有数据库:

1	show databases;

创建数据库：

1 2	create database mydb1; # 数据库存在时会报错 create database if not EXISTS mydb1 ;

选择使用数据库：

1	use mydb1;

删除数据库：

1 2	drop database mydb1; # 数据库不存在时会报错 drop database if exists mydb1;

修改数据库编码：

1	alter database mysql1 character set utf8;

创建表：

create table if not exists student(
  sid int,
  `name` varchar(20),
  gender varchar(10),
  age int,
  birth date,
  address varchar(20)
);

对表结构的常用操作：

-- 查看当前数据库所有的表
show tables; 
-- 查看指定表的创建语句
show create table student;
-- 查看表结构
desc student;
-- 删除表
drop table student;

查看表结构：

修改表结构:

-- 添加列（添加一个字段）
alter table student add dept varchar(20);
-- 修改列名和类型
alter table student change dept department varchar(30);
-- 删除列
alter table student drop department;
-- 修改表名
rename table student to stu;

DML

数据插入

-- 数据插入（指定对应的列）
insert into stu (sid,name,gender,age,birth,address) values(1001,'张三','男',18,'2001-11-01','广州');
-- 一次添加多行
insert into stu (sid,name,gender,age,birth,address) 
  values(1002,'王五','男',18,'2001-11-01','广州'),			 (1004,'lee','男',18,'2001-11-01','广州');
-- 向表中插入所有列 
insert into stu values(1005,'张华','男',21,'1999-01-01','广州');

数据修改

-- 修改所有学生的地址
update stu set address='深圳';
-- 修改特定id的学生的地址
update stu set address='中国香港' where sid = 1001;
update stu set address='中国香港' where sid > 1001;
-- 修改特定id的学生的名字和地址（同时修改多列）
update stu set address='中国香港',name='wangwu' where sid = 1002;

数据删除

delete只删除内容，而truncate类似于drop table，可以理解为把整个表删除，然后创建了一个新表。

-- 删除sid为1004的学生的数据
delete from stu where sid = 1004;
-- 删除表所有数据
delete from stu;
-- 清空表数据
truncate table stu;

总结

约束

主键约束

添加单列主键（主键约束的列非空且唯一）：

-- 创建表时指定主键
create table emp1(
  eid int primary key
);
-- 定义字段后再指定主键，其中pk1为主键名，可省略
create table emp2(
  eid int,
  CONSTRAINT pk1 primary key(eid)
);

添加联合主键：

参与联合主键的各个字段每一个字段都不能为空NULL

-- 创建表时添加多列主键
create table emp5(
  eid int, 
  name varchar(20), 
  deptId int, 
  salary double,
  CONSTRAINT pk PRIMARY KEY(name,deptId)
);

通过修改表结构添加主键：

-- 创建表后添加单列主键
create table emp4(
  eid int, 
  name varchar(20), 
  deptId int, 
  salary double
);
alter table emp4 add primary key(eid);
-- 创建表后添加多列主键
create table emp5(
  eid int, 
  name varchar(20), 
  deptId int, 
  salary double
);
alter table emp5 add primary key(name, deptId);

删除主键约束：

-- 删除主键
-- 1.删除单列主键
alter table emp1 drop primary key;
-- 2.删除多列主键
alter table emp5 drop primary key;

自增长约束

-- 自增正约束
create table t_user1 ( 
  id int primary key auto_increment, 
  name varchar(20)
);
insert into t_user1 values(NULL,'张三');
insert into t_user1(name) values('李四');

指定自增字段初始值：

-- 写法一：创建表时指定
create table t_user2 ( 
  id int primary key auto_increment, 
  name varchar(20)
)auto_increment=100;
insert into t_user2 values(NULL,'张三');
-- 写法二：创建表之后指定
create table t_user3 ( 
  id int primary key auto_increment, 
  name varchar(20)
);
alter table t_user3 auto_increment=100;
insert into t_user3 values(NULL,'张三');

delete from t_user1;  -- delete删除数据之后，自增长还是在最后一个值基础上加1
insert into t_user1 values(NULL,'张三');
insert into t_user1(name) values('李四');
#--------------------------------------------------
truncate t_user1; -- truncate删除之后，自增长从1开始
insert into t_user1 values(NULL,'张三');
insert into t_user1(name) values('李四');

非空约束

-- 非空约束
use mydb1;
-- 1. 创建非空约束-方式1，创建表时指定
create table mydb1.t_user6 ( 
  id int , 
  name varchar(20)  not null,   -- 指定非空约束
  address varchar(20) not null  -- 指定非空约束
);
insert into t_user6(id) values(1001); -- 不可以
insert into t_user6(id,name,address) values(1001,NULL,NULL); -- 不可以
insert into t_user6(id,name,address) values(1001,'NULL','NULL'); -- 可以（字符串：NULL）
insert into t_user6(id,name,address) values(1001,'',''); -- 可以（空串） 
-- 2.创建非空约束-方式2，创建表之后指定
create table t_user7 ( 
  id int , 
  name varchar(20) ,   -- 指定非空约束
  address varchar(20)  -- 指定非空约束
);
alter table t_user7 modify name varchar(20) not null;
alter table t_user7 modify address varchar(20) not null;
desc t_user7;
-- 3.删除非空约束
-- alter table 表名 modify 字段 类型 
alter table t_user7 modify name varchar(20) ;
alter table t_user7 modify address varchar(20) ;

唯一约束

use mydb1;
-- 1. 添加唯一约束-方式1-创建表时指定
create table t_user8 ( 
 id int , 
 name varchar(20) , 
 phone_number varchar(20) unique  -- 指定唯一约束 
);
insert into t_user8 values(1001,'张三',138);
insert into t_user8 values(1002,'张三2',139);
insert into t_user8 values(1003,'张三3',NULL);
insert into t_user8 values(1004,'张三4',NULL);  -- 在MySQL中NULL和任何值都不相同 甚至和自己都不相同
-- 2. 添加唯一约束-方式1-创建表之后指定
-- 格式：alter table 表名 add constraint 约束名 unique(列);
create table t_user9 ( 
  id int , 
  name varchar(20) , 
  phone_number varchar(20) -- 指定唯一约束 
); 
alter table t_user9 add constraint unique_pn unique(phone_number);
insert into t_user9 values(1001,'张三',138);
insert into t_user9 values(1002,'张三2',138);
-- 3. 删除唯一约束
-- 格式：alter table <表名> drop index <唯一约束名>;
alter table t_user9 drop index unique_pn;

默认约束

use mydb1;
-- 方式1-创建表时指定
create table t_user10 ( 
  id int , 
  name varchar(20) ,
  address varchar(20) default '北京' -- 指定默认约束 
);
insert into t_user10(id,name,address) values(1001,'张三','上海');
insert into t_user10 values(1002,'李四',NULL);
-- 方式2-创建表之后指定
-- alter table 表名 modify 列名 类型 default 默认值; 
create table t_user11 ( 
  id int , 
  name varchar(20) , 
  address varchar(20)  
);
alter table t_user11 modify address varchar(20) default '深圳';
insert into t_user11(id,name) values(1001,'张三');
-- 2.删除默认约束
-- alter table <表名> change column <字段名> <类型> default null; 
alter table t_user11 modify address varchar(20) default null;
insert into t_user11(id,name) values(1002,'李四');

零填充约束

-- 1. 添加约束
create table t_user12 ( 
  id int zerofill  , -- 零填充约束
  name varchar(20)   
);
insert into t_user12 values(123, '张三');
insert into t_user12 values(1, '李四');
insert into t_user12 values(2, '王五');
-- 2.删除约束
alter table t_user12 modify id int;

总结

DQL

数据准备

-- 创建数据库表
create database if not exists mydb2;
use mydb2;
-- 创建商品表
create table product(
  pid int primary key auto_increment,
  pname varchar(20) not NULL,
  price double,
  category_id varchar(20)
);
-- 添加数据
insert into product values(null,'海尔洗衣机',5000,'c001');
insert into product values(null,'美的冰箱',3000,'c001');
insert into product values(null,'格力空调',5000,'c001');
insert into product values(null,'九阳电饭煲',5000,'c001');
insert into product values(null,'啄木鸟衬衣',300,'c002');
insert into product values(null,'恒源祥西裤',800,'c002');
insert into product values(null,'花花公子夹克',440,'c002');
insert into product values(null,'劲霸休闲裤',266,'c002');
insert into product values(null,'海澜之家卫衣',180,'c002');
insert into product values(null,'杰克琼斯运动裤',430,'c002');
insert into product values(null,'兰蔻面霜',300,'c003');
insert into product values(null,'雅诗兰黛精华水',200,'c003');
insert into product values(null,'香奈儿香水',350,'c003');
insert into product values(null,'SK-II神仙水',350,'c003');
insert into product values(null,'资生堂粉底液',180,'c003');
insert into product values(null,'老北京方便面',56,'c004');
insert into product values(null,'良品铺子海带丝',17,'c004');
insert into product values(null,'三只松鼠坚果',88,null);

简单查询

-- 1.查询所有的商品.  
select *  from product;
-- 2.查询商品名和商品价格. 
select pname,price from product;
-- 3.别名查询.使用的关键字是as（as可以省略的）.  
-- 3.1表别名: 
select * from product as p;
-- 3.2列别名：
select pname as pn from product; 
-- 4.去掉重复值.  
select distinct price from product;
select distinct * from product;-- 当一张表中某一行的数据与另一行的数据全部相同则把重复的去掉
-- 5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname,price+10 from product;

运算符

算术运算符：

算术运算符	说明
+	加法运算
-	减法运算
*****	乘法运算
/ 或 DIV	除法运算，返回商
% 或 MOD	求余运算，返回余数

比较运算符：

比较运算符	说明
=	等于
< 和 <=	小于和小于等于
> 和 >=	大于和大于等于
<=>	安全的等于，两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
<> 或 !=	不等于
IS NULL 或 ISNULL	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

逻辑运算符：

逻辑运算符	说明
NOT 或者 !	逻辑非
AND 或者 &&	逻辑与
OR 或者 \|\|	逻辑或
XOR	逻辑异或

位运算符：

位运算符	说明
\|	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反，反转所有比特

运算符操作

select 6 + 2;
select 6 - 2;
select 6 * 2;
select 6 / 2;
select 6 % 2;
-- 将每件商品的价格加10
select pname,price + 10 as new_price from product;
-- 将所有商品的价格上调10%
select pname,price * 1.1 as new_price from product;

位运算：

select 3&5; -- 位与
select 3|5; -- 位或
select 3^5; -- 位异或
select 3>>1; -- 位左移
select 3<<1; -- 位右移
select ~3;   -- 位取反

条件查询

-- 查询商品名称为“海尔洗衣机”的商品所有信息：
select * from product where pname = '海尔洗衣机';
-- 查询价格为800商品
select * from product where price = 800;
-- 查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where not(price = 800);
-- 查询商品价格大于60元的所有商品信息
select * from product where price > 60;
-- 查询商品价格在200到1000之间所有商品
select * from product where price >= 200 and price <=1000;
select * from product where price between 200 and 1000;

-- 查询商品价格是200或800的所有商品
select * from product where price = 200 or price = 800;
select * from product where price in (200,800);
-- 查询含有‘裤'字的所有商品，%用来匹配任意字符
select * from product where pname like ‘%裤%';

 -- 查询以‘海’开头的所有商品
select * from product where pname like '海%';

-- 查询第二个字为‘蔻’的所有商品，一个下划线匹配单个字符
select * from product where pname like '_蔻%';

-- 查询category_id为null的商品
select * from product where category_id is null;

-- 查询category_id不为null分类的商品
select * from product where category_id is not null;

-- 使用least求最小值
select least(10, 20, 30); -- 10
select least(10, null , 30); -- null
-- 使用greatest求最大值
select greatest(10, 20, 30);
select greatest(10, null, 30); -- null

求最值时，若数值中存在null则不会进行比较，函数结果为null

排序查询

使用 MySQL 的 order by 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

格式：

select 
 字段名1，字段名2，……
from 表名
order by 字段名1 [asc|desc]，字段名2[asc|desc]……

1.asc代表升序，desc代表降序，如果不写默认升序

2.order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名

3.order by子句，放在查询语句的最后面。LIMIT子句除外

-- 1.使用价格排序(降序)
select * from product order by price desc;
-- 2.在价格排序(降序)的基础上，以分类排序(降序)
select * from product order by price desc,category_id asc;
-- 3.显示商品的价格(去重复)，并排序(降序)
select distinct price from product order by price desc;

聚合查询

使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

聚合函数	作用
count()	统计指定列不为NULL的记录行数；
sum()	计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max()	计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min()	计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg()	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

-- 1 查询商品的总条数
select count(*) from product;
-- 2 查询价格大于200商品的总条数
select count(*) from product where price > 200;
-- 3 查询分类为' c001 '的所有商品的总和
select sum(price) from product where category_id = 'c001';
-- 4 查询商品的最大价格
select max(price) from product;
-- 5 查询商品的最小价格
select min(price) from product;
-- 6 查询分类为' c002 '所有商品的平均价格
select avg(price) from product where category_id = 'c002';

对NULL值的处理：

1、count函数对null值的处理

如果count函数的参数为星号（*），则统计所有记录的个数。而如果参数为某字段，不统计含null值的记录个数。

2、sum和avg函数对null值的处理

这两个函数忽略null值的存在，就好象该条记录不存在一样。

3、max和min函数null值的处理

max和min两个函数同样忽略null值的存在。

-- 创建表
create table test_null( 
 c1 varchar(20), 
 c2 int 
);

-- 插入数据
insert into test_null values('aaa',3);
insert into test_null values('bbb',3);
insert into test_null values('ccc',null);
insert into test_null values('ddd',6);
 
-- 测试
select count(*), count(1), count(c2) from test_null;-- count(1)等价于count(*)，结果为 4 4 3
select sum(c2),max(c2),min(c2),avg(c2) from test_null;-- 结果为 12	6 3	4.0000

分组查询

分组查询是指使用group by字句对查询信息进行分组。

格式：

1 2	select 字段1,字段2… from 表名 group by 分组字段 having 分组条件; -- 执行顺序：from -> group by -> count(id) -> select -> having -> order by

如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现。

分组之后的条件筛选-having：

1、分组之后对统计结果进行筛选的话必须使用having，不能使用where

2、where子句用来筛选 FROM 子句中指定的操作所产生的行

3、group by 子句用来分组 WHERE 子句的输出,group by后面可以跟多个字段，意思是当这些字段都相同时才分到同一组

4、having 子句用来从分组的结果中筛选行

-- 1 统计各个分类商品的个数
select category_id ,count(*) from product group by category_id ;
-- 2.统计各个分类商品的个数,且只显示个数大于4的信息
select category_id ,count(*) from product group by category_id having count(*) > 1;

分页查询

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

格式：

-- 方式1-显示前n条
select 字段1，字段2... from 表明 limit n
-- 方式2-分页显示
select 字段1，字段2... from 表明 limit m,n
-- m: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
-- n: 整数，表示查询多少条数据

操作：

-- 查询product表的前5条记录 
select * from product limit 5 
-- 从第4条开始显示，显示5条 
select * from product limit 3,5 -- 第一条索引为0，因此3表示第四条

INSERT INTO SELECT 语句

将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句。

insert into Table2(field1,field2,…) select value1,value2,… from Table1 
-- 或者：
insert into Table2 select * from Table1
select * from product2;
#--------------------------------------------------------------
create table product3(
  category_id VARCHAR(20),
  product_count int
);
insert into product3 select category_id,count(*) from product GROUP BY category_id; 
select * from product3;

SELECT INTO FROM 语句

将一张表的数据导入到另一张表中，有两种选择 SELECT INTO 和 INSERT INTO SELECT 。

格式：

1	SELECT vale1, value2 into Table2 from Table1

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。

正则表达式

MySQL通过REGEXP关键字支持正则表达式进行字符串匹配。

模式	描述
^	匹配输入字符串的开始位置。
$	匹配输入字符串的结束位置。
.	匹配除 “\n” 之外的任何单个字符。
[…]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]	负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1\|p2\|p3	匹配 p1 或 p2 或 p3。例如，’z\|food’ 能匹配 “z” 或 “food”。’(z\|f)ood’ 则匹配 “zood” 或 “food”。

模式	描述
*****	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，’zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，’o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

-- ^ 在字符串开始处进行匹配
SELECT  'abc' REGEXP '^a';
 
-- . 匹配任意字符
SELECT  'abc' REGEXP '.b';
SELECT  'abc' REGEXP '.c';
SELECT  'abc' REGEXP 'a.';
 
-- [...] 匹配括号内的任意单个字符
SELECT  'abc' REGEXP '[xyz]';
SELECT  'abc' REGEXP '[xaz]';

-- [^...] 注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
SELECT  'a' REGEXP '[^abc]';
SELECT  'x' REGEXP '[^abc]';
SELECT  'abc' REGEXP '[^a]';
 
-- a* 匹配0个或多个a,包括空字符串。 可以作为占位符使用.有没有指定字符都可以匹配到数据
 
SELECT 'stab' REGEXP '.ta*b';
SELECT 'stb' REGEXP '.ta*b';
SELECT '' REGEXP 'a*';
 
-- a+  匹配1个或者多个a,但是不包括空字符
SELECT 'stab' REGEXP '.ta+b';
SELECT 'stb' REGEXP '.ta+b';

-- a?  匹配0个或者1个a
SELECT 'stb' REGEXP '.ta?b';
SELECT 'stab' REGEXP '.ta?b';
SELECT 'staab' REGEXP '.ta?b';
 
-- a1|a2  匹配a1或者a2，
SELECT 'a' REGEXP 'a|b';
SELECT 'b' REGEXP 'a|b';
SELECT 'b' REGEXP '^(a|b)';
SELECT 'a' REGEXP '^(a|b)';
SELECT 'c' REGEXP '^(a|b)';
 
-- a{m} 匹配m个a
 
SELECT 'auuuuc' REGEXP 'au{4}c';
SELECT 'auuuuc' REGEXP 'au{3}c';

 -- a{m,n} 匹配m到n个a,包含m和n
 
SELECT 'auuuuc' REGEXP 'au{3,5}c';
SELECT 'auuuuc' REGEXP 'au{4,5}c';
SELECT 'auuuuc' REGEXP 'au{5,10}c';
 
-- (abc) abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号，所以括号适合上面的所有情况。
SELECT 'xababy' REGEXP 'x(abab)y';
SELECT 'xababy' REGEXP 'x(ab)*y';
SELECT 'xababy' REGEXP 'x(ab){1,2}y';

-- $ 在字符串末尾开始匹配
SELECT  'abc' REGEXP 'a$';
SELECT  'abc' REGEXP 'c$’;

总结

多表操作

MySQL多表之间的关系可以概括为：一对一、一对多/多对一关系、多对多。

外键约束

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

定义一个外键时，需要遵守下列规则：

•主表必须已经存在于数据库中，或者是当前正在创建的表。

• 必须为主表定义主键。

• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。

• 外键中列的数目必须和主表的主键中列的数目相同。

• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

一对多关系

（1）设置外键约束：

在 create table 语句中，通过 foreign key 关键字来指定外键，具体的语法格式如下：

1	[constraint <外键名>] foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…]

实现：

1、创建表时设置外键约束

create database mydb3; 
use mydb3;
-- 创建部门表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);

create table if not exists emp(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20),  -- 员工所属部门
  constraint emp_fk foreign key (dept_id) references dept (deptno) -- 外键约束
);

2、创建表后设置外键约束

-- 创建部门表
create table if not exists dept2(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp2(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
);
-- 创建外键约束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);

（2）验证外键约束

 -- 1、添加主表数据
 -- 注意必须先给主表添加数据
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部');

-- 2、添加从表数据
-- 注意给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列
insert into emp values('1','乔峰',20, '1001');
insert into emp values('2','段誉',21, '1001');
insert into emp values('3','虚竹',23, '1001');
insert into emp values('4','阿紫',18, '1002');
insert into emp values('5','扫地僧',35, '1002');
insert into emp values('6','李秋水',33, '1003');
insert into emp values('7','鸠摩智',50, '1003'); 
insert into emp values('8','天山童姥',60, '1005');  -- 不可以

-- 3、删除数据
 /*
   注意：
       1：主表的数据被从表依赖时，不能删除，否则可以删除
       2: 从表的数据可以随便删除
 */
delete from dept where deptno = '1001'; -- 不可以删除
delete from dept where deptno = '1004'; -- 可以删除
delete from emp where eid = '7'; -- 可以删除

（3）删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系

格式：

1	alter table <表名> drop foreign key <外键约束名>;

实现：

1	alter table emp2 drop foreign key dept_id_fk;

多对多关系

在多对多关系中，A表的一行对应B的多行，B表的一行对应A表的多行，我们要新增加一个中间表，来建立多对多关系。

实现：

-- 学生表和课程表(多对多)
  -- 1 创建学生表student(左侧主表)
   create table if not exists student(
    sid int primary key auto_increment,
    name varchar(20),
    age int,
    gender varchar(20)
   );
  -- 2 创建课程表course(右侧主表)
  create table course(
   cid  int primary key auto_increment,
   cidname varchar(20)
  );
  

-- 3创建中间表student_course/score(从表)
  create table score(
    sid int,
    cid int,
    score double
  );
    
-- 4建立外键约束(2次)
 
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);
 
-- 5给学生表添加数据
insert into student values(1,'小龙女',18,'女'),(2,'阿紫',19,'女'),(3,'周芷若',20,'男');
-- 6给课程表添加数据
insert into course values(1,'语文'),(2,'数学'),(3,'英语');
-- 7给中间表添加数据
insert into score values(1,1,100),(1,2,100),(2,1,100),(2,3,100),(3,2,100),(3,3,100);

多表联合查询

数据准备

use mydb3;

-- 创建部门表
create table if not exists dept3(
  deptno varchar(20) primary key ,  -- 部门号
  dname varchar(20) -- 部门名字
);
 
-- 创建员工表
create table if not exists emp3(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
);
-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
-- 给emp表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002'); 
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

交叉连接查询

•交叉连接查询返回被连接的两个表所有数据行的笛卡尔积

•笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配

•假如A表有m行数据，B表有n行数据，则返回m*n行数据

•笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

格式：

1	select * from 表1,表2,表3….;

实现：

1	select * from dept3,emp3;

内连接查询

内连接查询求多张表的交集

格式：

-- 隐式内连接（SQL92标准）：
select * from A,B where 条件; 
-- 显示内连接（SQL99标准）：
select * from A inner join B on 条件;

操作：

-- 查询每个部门的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;

-- 查询研发部和销售部的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
select * from dept3 inner join emp3 on dept3.deptno=emp3.dept_id and name in ('研发部','销售部');
 
-- 查询每个部门的员工数,并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
 
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;

select deptno,count(*) as cnt from dept3 inner join emp3 on dept3.deptno = emp3.dept_id group by deptno ORDER BY cnt asc;

-- 查询人数大于等于3的部门，并按照人数降序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
 
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;

select deptno,count(1) as cnt from dept3 inner join emp3 on dept3.deptno=emp3.dept_id group by deptno having cnt>=3 order by cnt desc;

外连接查询

外连接分为左外连接（left outer join）、右外连接(right outer join)，满外连接(full outer join)。

注意：oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用union来达到目的。

格式：

左外连接：left outer join

select * from A left outer join B on 条件;

右外连接：right outer join

select * from A right outer join B on 条件;

满外连接: full outer join

select * from A full outer join B on 条件;

-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工
use mydb3;
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
 
-- 查询哪些员工有对应的部门，哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
 
 
-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union 
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

子查询

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。

子查询可以返回的数据类型一共分为四种：

1.单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；

2.单行多列：返回一行数据中多个列的内容；

3.多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；

4.多行多列：查询返回的结果是一张临时表

-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select eid,ename,age from emp3 where age = (select max(age) from emp3);

select * from emp3 where age=(select max(age) from emp3);
 
-- 查询年研发部和销售部的员工信息，包含员工号、员工名字
select * from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;
 
-- 查询研发部20岁以下的员工信息,包括员工号、员工名字，部门名字
select eid,ename,age,name from (select * from dept3 where name = '研发部') t1 join (select * from emp3 where age <20) t2 on t1.deptno=t2.dept_id;

select eid,ename,name from dept3 a join emp3 b on a.deptno=b.dept_id and a.name='研发部' and b.age<20;

子查询关键字：

1.ALL关键字

格式：

1
2
3

select …from …where c > all(查询语句)
-- 等价于：
select ...from ... where c > result1 and c > result2 and c > result3

特点：

•ALL: 与子查询返回的所有值比较为true 则返回true

•ALL可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

•ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

操作：

-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');

-- 查询不属于任何一个部门的员工信息 
select * from emp3 where dept_id != all(select deptno from dept3);

2.ANY和SOME关键字

格式：

1
2
3

select …from …where c > any(查询语句)
-- 等价于：
select ...from ... where c > result1 or c > result2 or c > result3

•ANY:与子查询返回的任何值比较为true 则返回true

•ANY可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。

•表示制定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。

SOME和ANY的作用一样，SOME可以理解为ANY的别名

操作：

1
2
3

-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > ANY(select age from emp3 where dept_id = '1003');
select * from emp3 where age > some(select age from emp3 where dept_id = '1003');

3.IN关键字

格式：

1
2
3

select …from …where c in(查询语句)
-- 等价于：
select ...from ... where c = result1 or c = result2 or c = result3

•IN关键字，用于判断某个记录的值，是否在指定的集合中

•在IN关键字前边加上not可以将条件反过来

操作：

-- 查询研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;

-- 查询不在研发部和不在销售部的员工信息，包含员工号、员工名字
select eid,ename from emp3 where dept_id not in (select deptno from dept3 where name = '研发部' or name = '销售部') ;

2.EXISTS关键字

格式：

1	select … from … where exists(查询语句)

•该子查询如果“有数据结果”(至少返回一行数据)，则该EXISTS() 的结果为“true”，外层查询执行

•该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()的结果为“false”，外层查询不执行

•EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立

•注意，EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字

操作：

-- 查询公司是否有大于60岁的员工，有则输出
select * from emp3 a where exists(select * from emp3 b where a.age > 60); -- 注意这里是a.age而不是b.age，否则会输出emp3表中的全部员工
 
-- 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

自关联查询

MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时表必须给表起别名。

格式：

1
2
3

select 字段列表 from 表1 a , 表1 b where 条件;
-- 或者 
select 字段列表 from 表1 a [left] join 表1 b on 条件;

操作：

-- 创建表,并建立自关联约束
create table t_sanguo(
    eid int primary key ,
    ename varchar(20),
    manager_id int,
 foreign key (manager_id) references t_sanguo (eid)  -- 添加自关联约束
);

-- 添加数据 
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);
 
-- 进行关联查询
-- 1.查询每个三国人物及他的上级信息，如:  关羽  刘备 
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;

总结

函数

聚合函数

group_concat()，该函数用户实现行的合并

group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。

格式：

1	group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])

（1）使用distinct可以排除重复值；

（2）如果需要对结果中的值进行排序（即对需要合并的各行的值进行排序），可以使用order by子句；

（3）separator是一个字符串值，默认为逗号。

操作：

create database mydb4;
use mydb4;
 
create table emp(
    emp_id int primary key auto_increment comment '编号',
    emp_name char(20) not null default '' comment '姓名',
    salary decimal(10,2) not null default 0 comment '工资',
    department char(20) not null default '' comment '部门'
);
 
insert into emp(emp_name,salary,department) 
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),
('刘云鹏',7800,'销售部');

-- 将所有员工的名字合并成一行 
select group_concat(emp_name) from emp;

-- 指定分隔符合并 
select department,group_concat(emp_name separator '、' ) from emp group by department; 

-- 指定排序方式和分隔符
select department,group_concat(emp_name order by salary desc separator ';' ) from emp group by department;

数学函数

函数名	描述	实例
ABS(x)	返回 x 的绝对值	返回 -1 的绝对值： SELECT ABS(-1) – 返回1
CEIL(x)	返回大于或等于 x 的最小整数	SELECT CEIL(1.5) – 返回2
FLOOR(x)	返回小于或等于 x 的最大整数	小于或等于 1.5 的整数： SELECT FLOOR(1.5) – 返回1
GREATEST(expr1, expr2, expr3, …)	返回列表中的最大值	返回以下数字列表中的最大值： SELECT GREATEST(3, 12, 34, 8, 25); – 34 返回以下字符串列表中的最大值： SELECT GREATEST(“Google”, “Runoob”, “Apple”); – Runoob
LEAST(expr1, expr2, expr3, …)	返回列表中的最小值	返回以下数字列表中的最小值： SELECT LEAST(3, 12, 34, 8, 25); – 3 返回以下字符串列表中的最小值： SELECT LEAST(“Google”, “Runoob”, “Apple”); – Apple

函数名	描述	实例
MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值： SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值： SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y)	返回 x 除以 y 以后的余数	5 除于 2 的余数： SELECT MOD(5,2) – 1
PI()	返回圆周率(3.141593）	SELECT PI() –3.141593
POW(x,y)	返回 x 的 y 次方	2 的 3 次方： SELECT POW(2,3) – 8

函数名	描述	实例
RAND()	返回 0 到 1 的随机数	SELECT RAND() –0.93099315644334
ROUND(x)	返回离 x 最近的整数（遵循四舍五入）	SELECT ROUND(1.23456) –1
ROUND(x,y)	返回指定位数的小数（遵循四舍五入）	SELECT ROUND(1.23456,3) –1.235
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) – 1.234

字符串函数

函数	描述	实例
CHAR_LENGTH(s)	返回字符串 s 的字符数	返回字符串 RUNOOB 的字符数 SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString;
CHARACTER_LENGTH(s)	返回字符串 s 的字符数	返回字符串 RUNOOB 的字符数 SELECT CHARACTER_LENGTH(“RUNOOB”) AS LengthOfString;
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串 SELECT CONCAT(“SQL “, “Runoob “, “Gooogle “, “Facebook”) AS ConcatenatedString;
CONCAT_WS(x, s1,s2…sn)	同 CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上 x，x 可以是分隔符	合并多个字符串，并添加分隔符： SELECT CONCAT_WS(“-“, “SQL”, “Tutorial”, “is”, “fun!”)AS ConcatenatedString;
FIELD(s,s1,s2…)	返回第一个字符串 s 在字符串列表(s1,s2…)中的位置	返回字符串 c 在列表值中的位置： SELECT FIELD(“c”, “a”, “b”, “c”, “d”, “e”);

函数	描述	实例
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 RUNOOB开始处的空格： SELECT LTRIM(“ RUNOOB”) AS LeftTrimmedString;– RUNOOB
MID(s,n,len)	从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)	从字符串 RUNOOB 中的第 2 个位置截取 3个字符： SELECT MID(“RUNOOB”, 2, 3) AS ExtractString; – UNO
POSITION(s1 IN s)	从字符串 s 中获取 s1 的开始位置	返回字符串 abc 中 b 的位置： SELECT POSITION(‘b’ in ‘abc’) – 2
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 abc 中的字符 a 替换为字符 x： SELECT REPLACE(‘abc’,’a’,’x’) –xbc
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来： SELECT REVERSE(‘abc’) – cba

函数	描述	实例
RIGHT(s,n)	返回字符串 s 的后 n 个字符	返回字符串 runoob 的后两个字符： SELECT RIGHT(‘runoob’,2) – ob
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 RUNOOB 的末尾空格： SELECT RTRIM(“RUNOOB “) AS RightTrimmedString; – RUNOOB
STRCMP(s1,s2)	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1	比较字符串： SELECT STRCMP(“runoob”, “runoob”); – 0
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 RUNOOB 中的第 2 个位置截取 3个字符： SELECT SUBSTR(“RUNOOB”, 2, 3) AS ExtractString; – UNO
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 RUNOOB 中的第 2 个位置截取 3个字符： SELECT SUBSTRING(“RUNOOB”, 2, 3) AS ExtractString; – UNO

函数	描述	实例
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 RUNOOB 的首尾空格： SELECT TRIM(‘ RUNOOB ‘) AS TrimmedString;
UCASE(s)	将字符串转换为大写	将字符串 runoob 转换为大写： SELECT UCASE(“runoob”); – RUNOOB
UPPER(s)	将字符串转换为大写	将字符串 runoob 转换为大写： SELECT UPPER(“runoob”); – RUNOOB
LCASE(s)	将字符串 s 的所有字母变成小写字母	字符串 RUNOOB 转换为小写： SELECT LCASE(‘RUNOOB’) – runoob
LOWER(s)	将字符串 s 的所有字母变成小写字母	字符串 RUNOOB 转换为小写： SELECT LOWER(‘RUNOOB’) – runoob

日期函数

最全总结—36种MySQL时间函数-阿里云开发者社区

控制流函数

if逻辑判断语句：

格式	解释	案例
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	SELECT IF(1 > 0,’正确’,’错误’) ->正确
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。	SELECT IFNULL(null,’Hello Word’) ->Hello Word
ISNULL(expression)	判断表达式是否为 NULL	SELECT ISNULL(NULL); ->1
NULLIF(expr1, expr2)	比较两个字符串，如果字符串 expr1 与 expr2 相等返回 NULL，否则返回 expr1	SELECT NULLIF(25, 25); -> NULL

case when 语句：

格式	解释	操作
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 … WHEN conditionN THEN resultN ELSE result END	CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。	select case 100 when 50 then ‘tom’ when 100 then ‘mary’else ‘tim’ end ; select case when 1=2 then ‘tom’ when 2=2 then ‘mary’ else’tim’ end ;

操作：

use mydb4; 
-- 创建订单表
create table orders(
 oid int primary key, -- 订单id
 price double, -- 订单价格
 payType int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4：其他)
);
 
insert into orders values(1,1200,1);
insert into orders values(2,1000,2);
insert into orders values(3,200,3);
insert into orders values(4,3000,1);
insert into orders values(5,1500,2);

-- 方式1
select 
*  ,
case 
  when payType=1 then '微信支付' 
    when payType=2 then '支付宝支付' 
    when payType=3 then '银行卡支付' 
    else '其他支付方式' 
end  as payTypeStr
from orders;
-- 方式2
select 
*  ,
case payType
  when 1 then '微信支付' 
    when 2 then '支付宝支付' 
    when 3 then '银行卡支付' 
    else '其他支付方式' 
end  as payTypeStr
from orders;

结果：

🌟窗口函数

语法结构：

window_function ( expr ) OVER ( 
  PARTITION BY ... 
  ORDER BY ... 
  frame_clause 
)

其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；OVER子句包含三个选项：

（1）分区（PARTITION BY）

PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算

（2）排序（ORDER BY）

OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似

（3）以及窗口大小（frame_clause）

frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

1.序号函数

序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。

格式：

row_number()|rank()|dense_rank() over ( 
  partition by ... 
  order by ... 
)

操作：

use mydb4; 
create table employee( 
   dname varchar(20), -- 部门名 
   eid varchar(20), 
   ename varchar(20), 
   hiredate date, -- 入职日期 
   salary double -- 薪资
); 

insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

-- 对每个部门的员工按照薪资排序，并给出排名（实际上不是排名，而是每组中的行的序号）
select 
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn 
from employee;

-- 对每个部门的员工按照薪资排序，并给出排名 rank
select 
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn 
from employee;

-- 对每个部门的员工按照薪资排序，并给出排名 dense-rank 
select 
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn 
from employee;

-- 求出每个部门薪资排在前三名的员工- 分组求TOPN
select 
* 
from 
(
    select 
     dname,
     ename,
     salary,
     dense_rank() over(partition by dname order by salary desc)  as rn
    from employee
)t
where t.rn <= 3;

-- 对所有员工进行全局排序（不分组）
-- 不加partition by表示全局排序
select 
     dname,
     ename,
     salary,
     dense_rank() over( order by salary desc)  as rn
from employee;

2.开窗聚合函数

在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），可以动态计算在指定的窗口内的各种聚合函数值。

操作：

-- 如果没有order by排序语句,默认把分组内的所有数据进行sum操作，结果表中的sum值的每一行的值为前几行的累加值再加上本行的值的和
select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate) as pv1 
from employee;
-- rows between unbounded preceding and current row 表示求和时从开头加到当前行（默认就是这样）
select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate  rows between unbounded preceding and current row) as c1 
from employee;
-- rows between 3 preceding and current row 表示当前行的求和结果为过去的三行的salary值求和再加上当前行的salary值的和
select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and current row) as c1 
from employee;
-- 从过去的第三行到接下来的一行的salary值的求和
select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and 1 following) as c1 
from employee;
-- 从后往前累加，其实就是默认的累加顺序反过来
select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between current row and unbounded following) as c1 
from employee;

3.分布函数

CUME_DIST

• 用途：分组内小于、等于当前rank值的行数 / 分组内总行数

• 应用场景：查询小于等于当前薪资（salary）的比例

操作：

/*
rn1: 没有partition,所有数据均为1组，总行数为12，
     第一行：小于等于3000的行数为3，因此，3/12=0.25
     第二行：小于等于4000的行数为5，因此，5/12=0.4166666666666667
rn2: 按照部门分组，dname='研发部'的行数为6,
     第一行：研发部小于等于3000的行数为1，因此，1/6=0.16666666666666666
*/
select  
 dname,
 ename,
 salary,
 cume_dist() over(order by salary) as rn1, -- 没有partition语句 所有的数据位于一组
 cume_dist() over(partition by dname order by salary) as rn2 
from employee;

percent_rank

•用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数

•应用场景：不常用

操作：

select 
 dname,
 ename,
 salary,
 rank() over(partition by dname order by salary desc ) as rn,
 percent_rank() over(partition by dname order by salary desc ) as rn2
from employee;
/*
 rn2:
  第一行: (1 - 1) / (6 - 1) = 0
  第二行: (1 - 1) / (6 - 1) = 0
  第三行: (3 - 1) / (6 - 1) = 0.4
*/

4.前后函数

•用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值

•应用场景：查询前1名同学的成绩和当前同学成绩的差值

-- lag的用
/*
last_1_time: 指定了往上第1行的值，default为'2000-01-01'  
                         第一行，往上1行为null,因此取默认值 '2000-01-01'
                         第二行，往上1行值为第一行值，2021-11-01 
                         第三行，往上1行值为第二行值，2021-11-02 
last_2_time: 指定了往上第2行的值，为指定默认值
                         第一行，往上2行为null
                         第二行，往上2行为null
                         第四行，往上2行为第二行值，2021-11-01 
                         第七行，往上2行为第五行值，2021-11-02 
*/
select 
 dname,
 ename,
 hiredate,
 salary,
 lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
 lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;

-- lead的用法
select 
 dname,
 ename,
 hiredate,
 salary,
 lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
 lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;

5.头尾函数

•用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值

•应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

操作：

-- 注意,如果不指定ORDER BY，则进行排序混乱，会出现错误的结果
-- 这里的每一个first都是分组中的第一个值，而由于是从上往下累计的并且是升序排列，所以last是当前员工的salary的值
select
  dname,
  ename,
  hiredate,
  salary,
  first_value(salary) over(partition by dname order by hiredate) as first,
  last_value(salary) over(partition by dname order by  hiredate) as last 
from  employee;

6.其他函数

NTH_VALUE(expr,n)

•用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名

•应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

-- 查询每个部门截止目前薪资排在第二和第三的员工信息
select 
  dname,
  ename,
  hiredate,
  salary,
  nth_value(salary,2) over(partition by dname order by salary DESC) as second_score,
  nth_value(salary,3) over(partition by dname order by salary DESC) as third_score
from employee;

NTILE

•用途：将分区中的有序数据分为n个等级，记录等级数

•应用场景：将每个部门员工按照入职日期分成3组

-- 根据入职日期将每个部门的员工分成3组
select 
  dname,
  ename,
  hiredate,
  salary,
ntile(3) over(partition by dname order by  hiredate  ) as rn 
from employee;

-- 取出每个部门的第一组员工
select
*
from
(
    SELECT 
        dname,
        ename,
        hiredate,
        salary,
    NTILE(3) OVER(PARTITION BY dname ORDER BY  hiredate  ) AS rn 
    FROM employee
)t
where t.rn = 1;