Linux下Mysql安装

1.yum仓库下载MySQL

yum localinstall https://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm

2.yum安装MySQL

yum install mysql-community-server -y

3.启动MySQL服务

service mysqld start

4.查看初始密码

grep 'temporary password' /var/log/mysqld.log

5.本地MySQL客户端登录

mysql -uroot -p[初始密码]

6.修改密码

use mysql;    #如果报错了则无需执行
ALTER USER 'root'@'localhost' IDENTIFIED BY '密码';
update user set password=password('new password') where user = 'root';
flush privileges;

7.设置全部权限

grant all privileges on *.* to root@localhost identified by '000000';
flush privileges;

配置允许远程连接

use mysql;        #进入数据库
select host from user where user='root';    #查询表
update user set host = '%' where user ='root';    #将Host设置为通配符%
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '你的root密码' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1。这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。 user=root Host=localhost，表示只能通过本机客户端去访问。而%是个通配符，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果Host=%，表示所有IP都有连接权限。

注意：在生产环境下不能为了省事将host设置为%，这样做会存在安全问题，具体的设置可以根据生产环境的IP进行设置；

Mysql数据库基本操作

DDL-数据库操作

1.DDL解释

DDL（Data Definition Language），数据自定义语言，该语言部分包括以下内容：

• 对数据库的常用操作
• 对表结构的常用操作
• 修改表结构

2.对数据库的常用操作-数据库操作

关于注释：

#
--
Ctrl + /

• SQL语句不区分大小写

创建数据库

create database mydb1;    #直接创建数据库
create database if not exists mydb1;        #如果不存在了才创建

选择使用/进入数据库

use mydb1;    #进入数据库

删除数据库

drop database mydb1;        #直接删除数据库
drop database if exists mydb1;        #如果存在了就删除,不存在mydb1就报错

修改数据库编码

我们常用utf8编码，有些时候数据库并不是默认utf8编码，所以我们需要手动修改

alter database mydb1 character set utf8;
#修改 数据库 mydb1的 格式 设置为 utf8

3.对表结构的常用操作-创建表

• 创建表格式
  • 用 [] 括起来的代表可以选择着来写。

create table [if not exists]表名(
  字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
  字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
  字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];

例如：

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

4.数据类型

数字类型

name tinyint unsigned    #设置无符号格式
decimal(M,D)——decimal(5,2)        #123.45

字符串类型

VARCHAR 变长字符串，会随着字符的长度变换内存大小

日期类型

DATE：年月日
TIME：时分秒
YEAR：只描述年
DATETIME：年月日时分秒

5.对表结构的其他操作

6.修改表结构

在表中添加新列

语法格式：

alter table 表名 add 列名 类型(长度) [约束];

例子：

给student添加一个新的字段

alter table student add dept varchar(20);

修改表中某一列的列名

语法格式：

alter table 表名 change 旧列名 新列名 类型(长度) [约束];

例子：

将dept修改为depts

alter table student change dept depts varchar(20);

删除表中的某一列

语法格式：

alter table 表名 drop 列名;

例子：

删除student表中depts列

alter table student drop depts;

修改表的名称

语法格式：

rename table 表名 to 新表名;

例子：

将student表改为students

rename table student to students;

DML-数据操作

1.DML的解释

DML是指数据操作语言，英文全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新。

2.数据的插入

前面咱们的操作只是创建了个表，就类似在excel中创建了一个第一行的文字分类。

语法格式：

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； #向表中插入某些数据，可以不按照顺序或者少写，但是不能不写。
insert into 表 values (值1,值2,值3...);     #向表中插入所有列

例子：

insert into students(sid,name,gender,age,birth,address)VALUES(001,"张三","男",18,"2022-1-30","郑州");    #插入数据到表students，分别以该顺序，该字符插入。
insert into students values(002,"里奥","男",19,"2022-1-29","郑州");    #直接插入，顺序是按照表中列的顺序

3.数据修改

语法格式：

update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

例子：

#设置所有的address为河南省
update students set address = "河南省";
#设置sid为001的数据的address改为北京市
update students set address = "北京市" where sid = 001;
#将name为里奥的数据的sid改为21，age改为20
update students set sid = 021,age = 20 WHERE name = "里奥";

4.数据删除

语法格式：

delete from 表名 [where 条件];
truncate table  表名 或者 truncate 表名

例子：

#删除表中age的值为20的数
delete from students WHERE age=19;
#删除表中所有数据
delete from students;
#清空表数据
truncate table students;
truncate student;

注意：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table ，可以理解为是将整个表删除，然后再创建该表；

DQL-基本查询

1.DQL的解释

• 数据库管理系统一个重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示。
• MySQL提供了功能强大、灵活的语句来实现这些操作。
• MySQL数据库使用select语句来查询数据。

语法格式：

select 
  [all|distinct]
  <目标列的表达式1> [别名],
  <目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名> 
[having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

简化语法：

select *| 列名 from 表 where 条件

数据准备

2.简单查询

as：可以理解为赋值到别名中。

select * from product;    #简单查询
select pname,price from product;    #查询商品名和商品价格
select * from product as s;        #别名查询，表别名
select pname as pn from product;    #列别名
select distinct price from product;        #去掉重复值
select pname,price+10 from product;        #查询结果是表达式

3.运算符

数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。

例如，学生表中存在一个birth字段，这个字段表示学生的出生年份。而运用MySQL的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。

MySQL支持4种运算符

算术运算符

比较运算符

逻辑运算符

位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

4.运算符操作

条件查询

#查询商品名称为“海尔洗衣机”的商品所有信息：
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);
#查询商品价格大于600元的所有商品信息
select * from product where price > 600;
#查询商品价格在200到1000之间所有商品
select * from product where price >=200 and price <=1000;
select * from product where price BETWEEN 100 and 1000;

算数运算符查询

#将每件商品的价格加10
select pname,price+10 as new_price from product;
#将商品价格下调5%
select pname,price * 0.95 new_price from product;
#查询商品价格'是'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;

最值运算

select least(10,20,30,-1);        #-1
select least(10,null,203,132,52454,3,-1);        #null
select GREATEST(12312,123123,12312312);            #12312312
select GREATEST(10,null,343);                    #null

位运算符

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

5.排序查询

如果我们需要对读取的数据进行排序，我们就可以使用 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子句除外

操作：

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

6.聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

操作：

#查询商品的总条数
select count(*) from product;
#查询价格大于200的总条数
select count(*) from product where price > 200;
#查询price总和
select sum(price) from product;
#查询price最大值
select max(price) from product;
#查询price最小值，并且区间在2000到5000
select min(price) from product where price BETWEEN 2000 and 5000;
#查询price中，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值的存在。

7.group by

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

格式：

select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

操作：

以category_id进行分组，显示category_id和count的数据。

select category_id ,count(*) from product group by category_id;

分组条件筛选-having

• 分组之后对统计结果进行筛选的话必须使用having，不能使用where
• where子句用来筛选 FROM 子句中指定的操作所产生的行
• group by 子句用来分组 WHERE 子句的输出。
• having 子句用来从分组的结果中筛选行

格式：

select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

操作：

统计各个分类商品的个数，且只显示个数大于4的信息

select category_id , count(*) from product group by category_id having count(*) > 4;

8.分页查询-limit

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有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;

9.特殊语句

insert into select

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

将select到的内容插入到另一张表中，并且类型和顺序必须一致。

格式：

insert into Table2(field1,field2,…) select value1,value2,… from Table1 或者：
insert into Table2 select * from Table1

例子：

将从product表中查询到的pname和price转移到pp表中的name和prives列中。

insert into pp(name,prives) select pname,price from product;

select into from

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

格式：

SELECT vale1, value2 into Table2 from Table1

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

Mysql的执行顺序

from —->where —-> group by ——>count(pid) ——>having —->select ——>order by ——>limit

10.正则表达式

正则表达式(regular expression)描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。

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

#^ 在字符串开始处进行匹配
select 'abc' REGEXP '^a';
#$ 在字符串末尾开始匹配
select 'abc' REGEXP 'a$';
select 'abc' REGEXP 'c$';
#. 匹配任意字符
select 'abc' REGEXP '.b';
select 'abc' REGEXP 'a.';
#[...] 匹配括号内的任意单个字符
select 'abc' REGEXP '[xyz]';
select 'abc' REGEXP '[aty]';
select 'abc' REGEXP '[ab]';
#[^...] 注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
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';
#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 'a' REGEXP '^(a|b)';
select 'bccb' REGEXP '^(a|b|c)';
select 'c' REGEXP '^(a|b)';
#a{m} 匹配m个a
select 'auuuuuc' REGEXP '^au{5}c';
select 'auuuuuc' REGEXP '^au{3}c';
#a{m,n} 匹配m到n个a,包含m和n
select 'auuuuuc' REGEXP '^au{2,5}c';
select 'auuuuuuc' REGEXP '^au{1,4}c';
select 'auuuuuuc' REGEXP '^au{9,10}c';
#(abc) abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号，所以括号适合上面的所有情况。
select 'xababy' REGEXP 'x(abab)y';
select 'xababy' REGEXP 'x(ab)*y';
select 'xababy' REGEXP 'x(ab){1,2}y';

Mysql约束

• 概念

约束英文：constraint

约束实际上就是表中数据的限制条件

• 作用

表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。

• 分类
  • 主键约束(primary key) PK
  • 自增长约束(auto_increment)
  • 非空约束(not null)
  • 唯一性约束(unique)
  • 默认约束(default)
  • 零填充约束(zerofill)
  • 外键约束(foreign key) FK

1.主键约束(primary key)

• MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行，提高查询效率。
• 主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
• 每个表最多只允许一个主键
• 主键约束的关键字是：primary key
• 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

1.1添加单列主键

创建单列主键有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键。

方式1，语法：

-- 在 create table 语句中，通过 PRIMARY KEY 关键字来指定主键。
-- 在定义字段的同时指定主键，语法格式如下：
create table 表名(
   ...
   <字段名> <数据类型> primary key 
   ...
)

方式1，例子：

create table emp2(
    eid int PRIMARY KEY,
    name varchar(20),
    depted int,
    salaey double
);

方式2，语法：

-- 在定义字段之后再指定主键，语法格式如下：
create table 表名(
   ...
   [constraint <约束名>] primary key [字段名]
);

constraint <约束名>：可以省略

方式2，例子：

create table emp2(
    eid INT,
    name VARCHAR(20),
    deptId INT,
    salary double,
    constraint pk1 primary key(eid)
 );

1.2添加多列主键

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。不允许两个数据是完全一模一样的，比如姓名+身份证的组合

注意：

1. 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。
2. 一张表只能有一个主键，联合主键也是一个主键

语法：

create table 表名(
   ...
   primary key （字段1，字段2，…,字段n)
);

现实：

create table emp3( 
  name varchar(20), 
  deptId int, 
  salary double, 
  primary key(name,deptId) 
);

1.3修改表结构添加主键

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。

语法：

create table 表名(
   ...
);
alter table <表名> add primary key（字段列表);

例子：

#前面的students表中没有主键
#在students表中添加主键，将sid添加为主键
alter table students add PRIMARY key (sid);

1.4删除主键

一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

因为主键只能有一个，所以这里只需要填写表就能删除主键。并且单列和联合的主键并没有区别。

格式：

alter table <数据表名> drop primary key;

例子：

alter table students drop primary key;

2.自增长约束(auto_increment)

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

比如一个班级表，从1到50，给id设置为自增长，我们都不用管了，他默认情况下会自动+1。比如我们就添加姓名里奥，不给id添加值，此时的第一行就是1，里奥。

通过给字段添加 auto_increment 属性来实现主键自增长

特点：

• 默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
• auto_increment约束的字段必须具备 NOT NULL 属性。
• auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT) 等。
• auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

2.1设置自增字段

语法：

字段名 数据类型 auto_increment

操作：

create table t_user1( 
  id int primary key auto_increment, 
  name varchar(20) 
);

2.2指定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加

方式1，创建表时指定：

create table t_user2 ( 
  id int primary key auto_increment, 
  name varchar(20)
)auto_increment=100;
#此时第一个数据就是100，然后依次往上+1

方式2，创建表之后指定：

create table t_user3 ( 
  id int primary key auto_increment, 
  name varchar(20)
);
alter table t_user2 auto_increment=100;

注意：

• delete数据之后自动增长从断点开始
• truncate数据之后自动增长从默认起始值开始

3.非空约束(not null)

MySQL 非空约束（not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

语法：

方式1：<字段名><数据类型> not null;
方式2：alter table 表名 modify 字段 类型 not null;

方式1，例子：

create table users3(
    id int,
    name varchar(20) not null,
    address VARCHAR(20) not null
);

方式2，例子：

其实只是将表中的id字段，重新设置属性设置为非空。

alter table users3 MODIFY id int not null;

同理，删除非空字段也可以使用该方法：

alter table users3 MODIFY name varchar(20);

4.唯一约束(unique)

唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。

语法：

方式1：<字段名> <数据类型> unique
方式2： alter table 表名 add constraint 约束名 unique(列);

添加唯一约束，方式1：

默认的约束名就是列的名。

create table users4(
    id int,
    name varchar(20),
    number VARCHAR(20) UNIQUE
);

添加唯一约束，方式2：

如果再次添加，需要设置约束名，删除时这个约束名会被用到。

alter table users4 add CONSTRAINT id_num unique(id);

删除唯一约束：

alter table users4 drop index id_num;

5.默认约束(default)

MySQL 默认值约束用来指定某列的默认值。

语法：

方式1： <字段名> <数据类型> default <默认值>;
方式2: alter table 表名 modify 列名 类型 default 默认值;

添加默认约束方式1：

create table users5(
    id int,
    name varchar(20),
    address varchar(20) default '北京'
);

添加默认约束方式2：

create table users5(
    id int,
    name varchar(20),
    address varchar(30)
);
alter table users5 modify address varchar(20) default '北京';

删除默认约束

alter tabke users5 modify column address varchar(20) default null;

6.领填充约束(zerofile)

概念：

1、插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
2、zerofill默认为int(10)
3、当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128~+127，无符号为0~256。

其实就是设置完位数后，你添加的数字不够他的位数时，会在前面添加0进行填充。

操作：

create talbe users6(
    id int zerofill,
    name varchar(20)
);

删除：

alter table users6 modify id int;

Mysql多表操作

多表关系

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

1.一对一

• 一个学生只有一张身份证；一张身份证只能对应一学生。
• 在任一表中添加唯一外键，指向另一方主键，确保一对一关系。
• 一般一对一关系很少见，遇到一对一关系的表最好是合并表。

比如说：

一张表写了学生的学号，名称。但是另一张表又写了学生的身份证号。学生对于身份证都是一对一，所以学生表的一个数据对应身份证表的一条数据。

2.一对多/多对一关系

部门和员工

分析：一个部门有多个员工，一个员工只能对应一个部门

实现原则：在多的一方建立外键，指向一的一方的主键

比如：

一个表写了公司的部门名称，另一个表写了公司的员工。员工表中有一栏会对应部门表，多个员工是相同的部门，所以是部门表的数据对应多个员工表数据。

3.多对多关系

学生和课程

分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

原则：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键

比如：

学生表和课程表，课程表中写了许多选修课。学生可以随便选课程表中的课，并且可以选择多个。多个学生选择多个课并且还不一样。

外键约束

介绍

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

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果、桃子、李子、西瓜等 4 种水果，那么，你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜，其它的水果都是不能购买的。

特点

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

• 主表必须已经存在于数据库中，或者是当前正在创建的表。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这 个外键的内容就是正确的。
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

1.创建外键约束（一对多）

方式1：

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

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

例子：

#创建主表:
create database mydb3;
use mydb3;
create table if not exists dept(
    depton 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 (depton)
    #创建外键约束
);

CONSTRAINT emp_fk： 给外键取名

foreign key(dept_id)： 设置外键列

references dept(depton)： 设置依赖列

方式2：

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

alter table <数据表名> add constraint <外键名> foreign key(<列名>) references <主表名> (<列名>);

例子：

create table if not exists dept2(
    depton 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 CONSISTENT 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.删除外键约束

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

格式：

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

实现：

alter table emp2 drop foreign key dept_id_fk;

4.创建外键约束（多对多）

在多对多关系中，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,78),(1,2,85),(2,1,97),(2,3,79),(3,2,82),(3,3,76);

多表联合查询

多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候,需要显示的数据来自多张表。

多表查询有以下分类：

• 交叉连接查询 [产生笛卡尔积，了解]
  语法：select * from A,B;
• 内连接查询(使用的关键字inner join — inner可以省略)
  隐式内连接（SQL92标准）：select from A,B where 条件;
  显示内连接（SQL99标准）：select from A inner join B on 条件;

• 外连接查询(使用的关键字 outer join — outer可以省略)

  左外连接：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 条件;

• 子查询

  select的嵌套

• 表自关联：

  将一张表当成多张表来用

1.交叉连接查询

• 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
• 笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
• 假如A表有m行数据，B表有n行数据，则返回m*n行数据
• 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

格式：

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

实现：

-- 交叉连接查询
select * from dept3,emp3;

2.内连接查询

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

格式：

隐式内连接（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 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
-- 查询每个部门的员工数,并升序排序
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
-- 查询人数大于等于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;

3.外连接查询

外连接分为左外连接（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     #unino all 不去重显示
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

4.子查询

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

特点：

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

1. 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；
2. 单行多列：返回一行数据中多个列的内容；
3. 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；
4. 多行多列：查询返回的结果是一张临时表

操作：

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

5.子查询关键字

在子查询中，有一些常用的逻辑关键字，这些关键字可以给我们提供更丰富的查询功能，主要关键字如下:

• 1.ALL关键字
• 2.ANY关键字
• 3.SOME关键字
• 4.IN关键字
• 5.EXISTS关键字

5.1.ALL

前面比较的数值都是1个，但是当需要比较的数值出现多个的时候，就需要用到ALL了。

格式：

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

5.2.ANY和SOME

ALL是一堆数值全部比较，而ANY也是一堆数值全部比较，但是只需要有一个为true，where的判定就是true；比如要大于40,50。ALL则是需要同时大于40和50，但是ANY大于40也行，大于50也行。

格式：

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

特点：

• ANY:与子查询返回的任何值比较为true 则返回true
• ANY可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。
• 表示制定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
• SOME和ANY的作用一样，SOME可以理解为ANY的别名

操作：

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

5.3.IN

格式：

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

特点：

• IN关键字，用于判断某个记录的值，是否在指定的集合中
• 在IN关键字前边加上not可以将条件反过来

操作：

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

5.4.EXISTS

有数据就输出，没数据则不输出。当没有别名时，则是以表为单位，有别名时以列为单位。

格式：

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);
select * from emp3 a where eid in(select eid from emp3 b where a.age > 60);
-- 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);
select * from emp3 a where dept_id in(select deptno from dept3 b where a.dept_id = b.deptno);

6.自关联查询

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

格式：

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;
-- 2.查询所有人物的上级
select a.eid,a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
-- 3.查询所有人物，上级，上上级。
select * from t_sanguo a left join t_sanguo b on a.manager_id = b.eid left join t_sanguo c on b.manager_id = c.eid;

Mysql的函数

在MySQL中，为了提高代码重用性和隐藏实现细节，MySQL提供了很多函数。函数可以理解为别人封装好的模板代码。

分类：

• 聚合函数
• 数学函数
• 字符串函数
• 日期函数
• 控制流函数
• 窗口函数

聚合函数

• 在MySQL中，聚合函数主要由：count,sum,min,max,avg,这些聚合函数我们之前都学过，不再重复。这里我们学习另外一个函数:group_concat(），该函数用户实现行的合并
• group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。

格式：

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

说明：

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

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

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

操作：

-- 将所有员工的名字合并成一行 
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;

数学函数

#取绝对值
select abs(-10);
#向上取整
select ceil(1.1);
select ceil(1.8);
#向下取整
select floor(1.9);
#求最大值
select greatest(10,2,54,13454,67,6,4);
#求最小值
select least(1,4,32,23,4,3,4,54,3,6334);
#取模
select mod(100,33);
#计算次方
select pow(2,6);
#计算π
select pi();
#0-100随机数
select round(rand() * 10);
#四舍五入
select round(5.55);
#四舍五入并保留小数
select round(1.2353654,3);
#保留小数但不四舍五入
select truncate(1.123213,5);

字符串函数

#计算字符长度
select CHAR_LENGTH("test");
#以字节数计算字符长按
select length("213213");
select length("芜湖~");
#合并字符串
select concat('wuhu！','太牛了');
#设置分隔符并合并字符串
select concat_ws("-","123123","qwqweqwe");
#寻找第1个字符的位置
select FIELD('c','acdasd',"as","c");
#去除前面的空格
select LTRIM("   123213             ");
#去除后面的空格
select RTRIM("      qweqwe   ");
#去除全部的空格
select TRIM("       qweqw        ");
#切割字符某个位置的后几行
select mid("qwerty",3,2);
#查看某个字符在该字符中的位置
select position('q'in'werq');
#替换字符
select REPLACE('qweqwe','q','w');
#反转字符串内容
select REVERSE('zxcvbnm');
#取出字符串后几个字符
select right('asdfgh',5);
#比较字符串，左边大1，右边大-1，相等0
select STRCMP("qwe","qwer");
#字符串截取
select SUBSTR("runiib",2,3);
select SUBSTRING("qweqwe",2,3);
#将小写转为大写
select ucase("qwewqe");
#将大写转为小写
select lower("QWEQWE");

日期函数

#返回从1970-01-01 00:00:00到当前毫秒值
select UNIX_TIMESTAMP();
#将制定日期转为毫秒值时间戳
select UNIX_TIMESTAMP('2002-10-10 19:16:20');
#将毫秒值时间戳转为指定格式日期
select FROM_UNIXTIME(1034248580,'%y-%m-%d %H:%i:%s');
#返回当前日期
select CURDATE();
select CURRENT_DATE();
#从日期或日期时间表达式中提取日期值
select date("2017-12-04");
#计算日期 d1->d2 之间相隔的天数
select DATEDIFF('2011-12-5','2022-2-7');
#计算时间差值
select TIMEDIFF('13:20:13','15:40:10');
#按表达式 f的要求显示日期 d
select DATE_FORMAT("2011-11-11 11:11:11","%Y-%m-%d %r");
#将字符串转变为日期
select STR_TO_DATE("August 10 2017",'%M %d %Y');
#函数从日期减去指定的时间间隔
select DATE_SUB('2021-10-1',INTERVAL 2 day);
#计算起始日期 d 加上一个时间段后的日期
select DATE_ADD('2021-10-1',INTERVAL 2 year);
#从日期 d 中获取指定的值
select EXTRACT(hour from '2021-12-13 11:12:13');
#返回给给定日期的那一月份的最后一天
select LAST_DAY("2012-2-10");
#基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期
select MAKEDATE(2019,150);
#返回年份 还能返回更多 月 日 时 分 秒
select YEAR("2021-10-19");
#返回日期d是第几季节，返回 1 到 4
select QUARTER("2014-6-10");
#返回日期当中的月份名称，如 November
select MONTHNAME("2013-4-6");
#返回日期d中的月份值，1 到 12
select month("2012-10-3");
#返回日期 d 是星期几，如 Monday,Tuesday
select DAYNAME("2011-12-1");
#计算今天是本月的第几天
select DAYOFMONTH("2012-6-9");
#计算今天是第几周
select DAYOFWEEK("2018-9-28");
#计算今天是今年的第几天
select DAYOFYEAR("2019-6-15");
#计算今天是本周的第几周
select week("1980-7-10");
#计算今天是星期几
select WEEKDAY("2013-10-17");
#计算今天是今年的第几个星期
select WEEKOFYEAR("2020-8-19");
#计算年份+今年的第几周
select YEARWEEK("2015-10-3");
#展示现在的时间
select now();

流程控制函数

-- IF 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。
select *,if(score >= 85,'优秀','及格') flag from score;
-- IFNULL 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
use mydb2;
select *,ifnull(comm,'无奖金') '奖金' from emp; 
-- ISNULL 判断表达式是否为 NULL
select isnull(123);
-- CASE WHEN CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。
    select * ,
    case
        when payType=1 then '微信支付'
            when payType=2 then '支付宝支付'
            when payType=3 then '银行卡支付'
            else '其他支付'
        end as payTypeStr
        from orders;
use mydb3;
select * ,
case
    when score >= 95 then '优秀'
    when score >= 80 then '良'
    when score >= 60 then '中等'
    else '太差了'
    end as '成绩评级'
    from score;

窗口函数

• MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点.
• 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

分类

语法结构：

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

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

分区（PARTITION BY）

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

排序（ORDER BY）

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

以及窗口大小（frame_clause）

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

1.序号函数

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

格式：

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

操作

-- 对每个部门的员工按照薪资排序，并给出排名
select dname,ename,salary,
ROW_NUMBER() over(PARTITION by dname order by salary desc) as rn1,
RANK() over(PARTITION by dname order by salary) as rn2,
DENSE_RANK() over(PARTITION by dname order by salary) as rn3
from employee;

ROW_NUMBER() 没有重复的排名并且顺序挨着

rank() 有重复的排名，并且顺序会被跳过

dense_rank() 有重复的排名，顺序不被跳过

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

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

2.开窗聚合函数

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

操作：

SUM()

select *, sum(salary) over(PARTITION by dname order by hiredate) as s1 from employee;
-- 查询工资总和
select dname,ename,hiredate,salary,sum(salary) over(PARTITION by dname) as c1 from employee;-- 如果没有order  by排序语句  默认把分组内的所有数据进行sum操作
select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate  rows between unbounded preceding and current row) as c1 
from employee;
-- row 行 
-- unbounded preceding 从首行到尾行的当前行
-- current row    到最后一行
select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and current row) as c1 
from employee;
-- 开头第3行 到尾行
select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and 1 following) as c1 
from employee;
-- 开头第3行到倒数第1行
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_DUST()

• 用途：分组内小于、等于当前rank值的行数 / 分组内总行数
• 应用场景：查询小于等于当前薪资（salary）的比例

操作：

select dname,ename,salary,
cume_dist() over(order by salary) as rn1,
cume_dist() over(partition by dname order by salary) as rn2 from employee;

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

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将选择列的上n条数值作为当前数值，lead则是将下n条作为当前数值

操作：

select dname,ename,hiredate,salary,
lag(hiredate,1,'2000-01-01') over(PARTITION by dname order by hiredate) as last_1,
lag(hiredate,2) over(PARTITION by dname order by hiredate) as last_2 from employee;

5.窗口函数

• 用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
• 应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

操作：

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;

FIRST_VALUE：该列第一个数值

LAST_VALUE：该行第一个数值

其他函数

nth_value： 排序后，该列的数值第n个值

• 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
• 应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资