【MYSQL】

学习记录

MYSQL基础篇（尚学堂视频学习）

SQL语言

1、SQL语言简介

结构化查询语言(Structured Query Language)简称 SQL(发音：sequal['si:kwəl])，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

2、SQL 能做什么？

SQL 面向数据库执行查询
SQL 可在数据库中插入新的记录
SQL 可更新数据库中的数据
SQL 可从数据库删除记录
SQL 可创建新数据库
SQL 可在数据库中创建新表
SQL 可在数据库中创建存储过程
SQL 可在数据库中创建视图
SQL 可以设置表、存储过程和视图的权限

3、SQL 标准

SQL 是 1986 年 10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了 SQL 正式国际标准。1989 年 4 月，ISO 提出了具有完整性特征的 SQL89 标准，1992 年 11 月又公布了 SQL92 标准，在此标准中，把数据库分为三个级别：基本集、标准集和完全集。在 1999 年推出 99 版标准。最新版本为 SQL2016 版。比较有代表性的几个版本：SQL86、SQL92、SQL99。

4、SQL语言分类

4.1、DQL数据查询语言

数据查询语言（DQL：Data Query Language）其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。关键字 SELECT 是 DQL（也是所有 SQL）用得最多的动词。
SELECT
FROM
WHERE
ORDER BY
HAVING

4.2、DML数据操作语言

数据操作语言（DML：Data Manipulation Language）其语句包括动词 INSERT，UPDATE 和 DELETE。它们分别用于添加，修改和删除表中的行。
INSERT：添加数据
UPDATE：更新数据
DELETE：删除数据

4.3、DDL数据定义语言

数据定义语言（DDL：Data Definition Language）定义数据库对象语言，其语句包括动词 CREATE 和 DROP 等。
CREATE：创建数据库对象
ALTER：修改数据库对象
DROP：删除数据库对象

4.4、DCL数据控制语言

数据控制语言（DCL：Data Control Language）它的语句通过GRANT 或 REVOKE 获得许可，确定用户对数据库对象的访问。
GRANT：授予用户某种权限
REVOKE：回收授予的某种权限

4.5、TCL事务控制语言

事务控制语言（TCL ：Transaction Control Language）它的语句能确保被 DML 语句影响的表的所有行及时得以更新。
COMMIT：提交事务
ROLLBACK：回滚事务
SAVEPOINT：设置回滚点

注意：
数据操纵语言DML（insert、update、delete）针对表中的数据 ；
而数据定义语言DDL（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、存储过程procedure、触发器trigger；

4.6、SQL语言语法

SQL语言语法
1 SQL语句不区分大小写，关键字建议大写。
2 SQL语句可以单行或多行书写，以分号结尾。

创建、选择、删除数据库

1、创建数据库

1.1、使用DDL语句创建数据库：

语法：
CREATE DATABASE 数据库名 DEFAULT CHARACTER SET 字符编码;

CREATE DATABASE----------创建数据库关键字
test----------数据库名
DEFAULT CHARACTER SET----------指定默认编码类型关键字
utf8----------指定字符编码

示例：创建一个test 的数据库，并查看该数据库，以及该数据库的编码。
创建数据库：create database test default character set utf8;

查看数据库：show databases;

查看数据库编码：
select schema_name,default_character_set_name from information_schema.schemata where schema_name = ‘test’;

1.2、使用Navicat创建数据库：

示例：创建一个test2 的数据库。

2、选择数据库

数据库一旦创建之后，我们要对这个数据库进行其他操作，mysql也不知道，因为有很多数据库，要先选择这个数据库。

2.1、在创建表时，选择数据库：

语法：
USE 数据库名;
示例：创建一个名称为bjsxt的数据库编码为 utf8，选择该数据库
创建一个名称为bjsxt的数据库编码为 utf8：
create database bjsxt default character set utf8;

选择该数据库：USE bjsxt;

3、删除数据库

3.1、使用DDL语言删除数据库：

语法：
DROP DATABASE 数据库名称;

示例：删除 test 数据库
drop database test;

3.2、使用Navicat删除数据库：

示例：删除test2数据库
操作：选中test2数据库-右键-删除数据库

MySQL中的数据类型

1、整数类型

MySQL数据类型	含义（有符号）
tinyint(m) 1个字节	范围(-128~127)
smallint(m) 2个字节	范围(-32768~32767)
mediumint(m) 3个字节	范围(-8388608~8388607)
int(m) 4个字节	范围(-2147483648~2147483647)
bigint(m) 8个字节	范围(±9.22*10的18次方)

数值类型中的长度 m 是指显示长度，并不表示存储长度，只有字段指定 zerofill 时有用。
例如： int(3) ，如果实际值是 2 ，如果列指定了 zerofill ，查询结果就是 002 ，左边用 0 来填充。

2、浮点类型

MySQL数据类型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

3、字符类型

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
tinytext	可变长度，最多255个字符
varchar(n)	可变长度，最多65535个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

3.1、char和varchar：

1. char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定长。
2. varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
3. text不设置长度， 当不知道属性的最大长度时，适合用text。

3.2、查询速度

按照查询速度： char最快， varchar次之，text最慢。

3.3、字符串型使用建议：

1 经常变化的字段用varchar
2 知道固定长度的用char
3 尽量用varchar
4 超过255字符的只能用varchar或者text
5 能用varchar的地方不用text

4、日期类型

MySQL数据类型	含义
date	日期 YYYY-MM-DD
time	时间 HH:MM:SS
datetime	日期时间 YYYY-MM-DD HH:MM:SS
timestamp	时间戳YYYYMMDD HHMMSS

5、二进制数据(BLOB)

BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式。

1. 存储，不分大小写。
2. BLOB存储的数据只能整体读出。
3. TEXT可以指定字符集，BLOB不用指定字符集。

Tables表

1、创建表

1.1、使用DDL语句创建表

语法：
CREATE TABLE 表名(列名 类型,列名 类型…);

CREATE TABLE-------创建表关键字
表名(列名 类型,列名 类型…);-------表名、列名、类型

示例：创建一个 employees 表包含雇员ID、雇员名字、雇员薪水
create table employees(employee_id int,employee_name varchar(10),salary float(8,2));

查看已创建的表。

语法：
show tables;

1.2、使用Navicat创建表

示例：创建employees2表。

2、删除表

2.1、使用DDL语句删除表

语法：
DROP TABLE 表名;

DROP TABLE--------删除表关键字
表名;--------表名

示例：删除 employees 表。
drop table employees;

2.2、使用Navicat删除表

示例：
删除employees2表

3、修改表

3.1、修改表名

3.1.1、使用DDL语句修改表

语法：
ALTER TABLE 旧表名 RENAME 新表名;

ALTER TABLE----------修改表关键字
旧表名----------旧表名
RENAME----------修改表名关键字
新表名;----------新表名

示例一：创建一个 employees 表包含雇员 ID ，雇员名字，雇员薪水。
create table employees(employee_id int,employee_name varchar(10),salary float(8,2));

示例二：将 employees 表名修改为 emp。
alter table employees rename emp;

3.1.2、使用Navicat修改表名

选择表按F2。

3.2、修改列名

3.2.1、使用DDL语句修改列名

语法：
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;

ALTER TABLE------------修改表关键字
表名------------表名
CHANGE COLUMN------------修改列名关键字
旧列名------------旧列名
新列名 类型;------------新列名与类型

示例：将 emp 表中的 employee_name 修改为 name。
alter table emp change column employee_name name varchar(20);

3.2.2、使用Navicat修改列名

navicat-选择表-设计表-直接修改列名-ctrl+s保存

3.3、修改列类型

3.3.1、使用DDL语句修改列类型

语法：
ALTER TABLE 表名 MODIFY 列名 新类型;

ALTER TABLE----------修改表关键字
表名----------表名
MODIFY----------修改列类型关键字
列名 新类型;----------列名 新类型

示例：将 emp 表中的 name 的长度指定为 40。
alter table emp modify name varchar(40);

3.3.2、使用Navicat修改列类型

navicat-选择表-设计表-直接修改列类型-ctrl+s保存

3.4、添加新列

3.4.1、使用DDL语句添加新列

语法：
ALTER TABLE 表名 ADD COLUMN 新列名 类型;

ALTER TABLE----------修改表关键字
表名----------表名
ADD COLUMN----------添加新列关键字
列名 新类型;----------列名 新类型

示例：在 emp 表中添加佣金列，列名为 commission_pct。
alter table emp add column commission_pct float(4,2);

3.4.2、使用Navicat添加新列

navicat-选择表-设计表-直接添加栏位-添加新列-ctrl+s保存

3.5、删除指定列

3.5.1、使用DDL语句删除指定的列

语法：
ALTER TABLE 表名 DROP COLUMN 列名;

ALTER TABLE----------修改表关键字
表名----------表名
DROP COLUMN----------删除列关键字
列名;----------列名

示例：删除 emp 表中的 commission_pct。
alter table emp drop column commission_pct;

3.5.2、使用Navicat删除指定的列

navicat-选择表-设计表-直接选择要删除的那行，删除栏位-ctrl+s保存

MySQL中的约束

1、约束概述

数据库约束是对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。(约束可以理解为，是数据库提供的一种数据校验方式)

1. 主键约束(Primary Key) PK
   主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。
   主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。
2. 外键约束(Foreign Key) FK
   外键约束经常和主键约束一起使用，用来确保数据的一致性。
3. 唯一性约束(Unique)
   唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的。
4. 非空约束(Not Null)
   非空约束用来约束表中的字段不能为空。
5. 检查约束(Check)
   检查约束也叫用户自定义约束，是用来检查数据表中，字段值是否有效的一个手段，但目前 MySQL 数据库不支持检查约束。

2、主键约束(Primary Key)

2.1、什么是主键约束(Primary Key) PK

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。
主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

2.2、主键约束的类型

单一主键

使用一个列作为主键列，当该列的值有重复时，则违反唯一约束。

联合主键

使用多个列作为主键列，当多个列的值都相同时，则违反唯一约束。

2.3、添加主键约束

2.3.1、使用DDL语句修改表添加主键约束

语法：
ALTER TABLE 表名 ADD PRIMARY KEY(列名);

示例：将 emp 表中的 employee_id 修改为主键。
alter table emp add primary key(employee_id);

2.4、主键自增长

2.4.1、MySQL 中的自动增长类型要求：

（1）一个表中只能有一个列为自动增长。
（2）自动增长的列的类型必须是int整数类型。
（3）自动增长只能添加到具备主键约束与唯一性约束的列上。
（4）删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然 后在删除约束。
（5）注意：只有mysql数据库有主键自动增长的能力。oracle数据库没有主键自增长能力需要依靠oracle数据库中的序列才能达到自增长效果。

2.4.2、mysql主键自增长语法：

语法：
alter table 表名 modify 主键 类型 auto_increment;

示例：将 emp 表中的 employee_id 主键修改为自增。
alter table emp modify employee_id int auto_increment;
自动增长的列employee_id的类型必须是整数类型int

2.4.3、使用Navicat添加主键约束

navicat如果创建表写入数据，没有添加过主键，双击表之后会报错：这张表没有主键。

navicat添加主键约束操作(navicat在底层会生成ddl语句)：

1. 双击表-类型为整数int类型的列
2. 因为主键不允许有空值-默认是允许有空值，所以：不勾选允许空值-在后面点击一下：有个钥匙，表示这一列是一个主键列
3. 联合主键列：再选另一列-最后点一下出现钥匙即可。
4. 这样就完成了主键的添加，ctrl+s保存。
5. 如何让主键这一列有自动增长的能力：选中这一列-下面点击自动递增(就是主键自增长)，ctrl+s保存。
   
6. 表中插入数据，employee_id列主键列自动增长，employee_id列，不允许重复
   
7. 表中插入数据，employee_id列主键列自动增长，employee_id列，不允许为空
   

2.5、删除主键约束

2.5.1、使用DDL语句删除主键

语法：
ALTER TABLE 表名 DROP PRIMARY KEY;
注意：删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除主键

示例：删除emp表中的 employee_id 主键约束。

1. 如果主键有自动增长，先去掉自动增长：
   alter table emp modify employee_id int;
   
2. 删除主键：
   alter table emp drop primary key;
   

3、外键约束(Foreign Key)

3.1、什么是外键约束(Foreign Key)

外键约束经常和主键约束一起使用，用来确保数据的一致性。
1 外键约束允许有重复
2 外键约束允许有空值
3 外键约束的这个值必须得是它所参照的那个表中的主键列所包含的值。

3.2、添加外键约束

3.2.1、使用DDL语句修改表添加外键约束

语法：
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY( 列 名 ) REFERENCES 参照的表名 (参照的列名);

示例一：创建 departments 表包含 department_id 、department_name ，location_id。
create table departments(department_id int,department_name varchar(30),location_id int);

示例二：修改departments表，向department_id列添加主键约束与自动递增。
alter table departments add primary key(department_id);
alter table departments modify department_id int auto_increment;

示例三：修改 emp 表，添加 dept_id 列。
alter table emp add column dept_id int;

示例四：向 emp 表中的 dept_id 列添加外键约束。
alter table emp add constraint emp_fk foreign key(dept_id) references departments(department_id);

示例：唯一性约束的效果
如果要在emp表中插入数据，部门列如果在部门表中没有，数据是插入不了的，就必须要departments的这个表的department_id列里有这个值，emp表才可以插入数据。

3.2.2、使用Navicat添加外键约束

Navicat添加外键约束-设计表-添加外键约束

3.3、删除外键约束

3.3.1、使用DDL语句删除外键约束

语法：
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

示例：删除 dept_id 的外键约束
alter table emp drop foreign key emp_fk;

3.3.2、使用Navicat删除外键约束

Navicat-设计表-外键-选中要删除的外键-删除外键

4、唯一性约束(Unique)

4.1、什么是唯一性约束(Unique)

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的。

4.2、添加唯一性约束(Unique)

4.2.1、使用DDL语句修改表添加唯一性约束

语法：
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名);

示例：向 emp 表中的 name 添加唯一约束
alter table emp add constraint emp_uk unique(name);

示例：唯一性约束的效果：不允许重复、允许有空值
emp表的name列添加了唯一性约束，如果这列的值，数据插入时有重复，ctrl+s保存时报错。

唯一性约束的列允许有空值

4.2.2、使用Navicat添加唯一性约束

Navicat-emp表-设计表-索引-索引类型UNIQUE-ctrl+s保存

5、非空约束(Not Null)

5.1、什么是非空约束(Not Null)

非空约束用来约束表中的字段不能为空。

5.2、添加非空约束(Not Null)

5.2.1、使用DDL语句修改表添加非空约束(Not Null)

语法：
ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL;

示例：向 emp 表中的 salary 添加非空约束
alter table emp modify salary float(8,2) not NULL;

示例：非空约束的效果：不允许有空值
emp表salary列添加非空约束后，插入数据时salary列为空保存报错

5.2.2、使用Navicat添加非空约束(Not Null)

Navicat-找到表-设计表-字段名找到列-不是null-对勾

5.3、删除非空约束(Not Null)

5.3.1、使用DDL语句修改表删除非空约束

语法：
ALTER TABLE 表名 MODIFY 列名 类型 NULL;

示例：示例：删除emp表中salary 的非空约束
alter table emp modify salary float(8,2) NULL;

5.3.2、使用Navicat删除非空约束

Navicat-找到表-设计表-字段名找到列-不是null-不勾选，插入数据，该列可以有空值

6、创建表时添加约束

6.1、查询表中的约束信息：

SHOW KEYS FROM 表名;
示例：查询emp、deptments表中的约束信息

6.2、创建表时添加约束

示例：创建 depts 表包含 department_id 该列为主键且自动增长，department_name 列不 允许重复，location_id 列不允含有空值。
create table depts(department_id int primary key auto_increment,department_name
varchar(30) unique,location_id int not null);

MySQL中DML操作：

DML操作：insert添加数据、update更新数据、delete删除数据

1、insert添加数据

1.1、insert选择插入数据（表明后面需要指定列名非空）

语法：
INSERT INTO 表名(列名 1 ，列名 2 ，列名 3…) VALUES(值 1 ，值 2 ，值 3…);

示例：向 departments 表中添加一条数据，部门名称为 market ，工作地点 ID 为 1。
insert into departments(department_name,location_id) values(“market”, 1);

1.2、insert完全插入数据（表明后面不需要指定列名）

语法：
INSERT INTO 表名 VALUES(值 1 ，值 2 ，值 3…);

注意：如果主键是自动增长，需要使用 default 或者 null 或者 0 占位。

示例一：向 departments 表中添加一条数据，部门名称为 development ，工作地点 ID 为 2 。使用 default 占位。
insert into departments values(default,“development”,2);

示例二：向 departments 表中添加一条数据，部门名称为human ，工作地点 ID 为 3 ，使用 null 占位。
insert into departments values(null,“human”,3);

示例三：向 departments 表中添加一条数据，部门名称为 teaching ，工作地点 ID 为4 。使用 0 占位。
insert into departments values(0,“teaching”,4);

2、update更新数据（更新语句一定要给定更新条件）

语法：
UPDATE 表名 SET 列名=值，列名=值 WHERE 条件;

注意：更新语句中一定要给定更新条件，否则表中的所有数据都会被更新。

2.1、示例：更新 emp3 表中的 id 为 1 的数据，添加 address 为 BeiJing。

（更新不了表因为没有创建表，先看4节默认值处理）
update emp3 set address = “BeiJing” where emp_id = 1;

2.2、示例：不动产报表开发项目

update reo_property_info SET update_time=NULL where id=‘d619eaa181314818a80946650b566326’;

3、delete删除数据（删除语句一定要给定删除条件）

语法：
DELETE FROM 表名 WHERE 条件;

3.1、注意：在DELETE语句中，如果没有给定删除条件则会删除表中的所有数据。

示例：删除 emp3 表中 emp_id 为 1 的雇员信息。
delete from emp3 where emp_id = 1;

3.2、DELETE 清空表

语法：
DELETE FROM 表名;
（此语法一执行，表就会被清空）

3.3、TRUNCATE 清空表

语法：
TRUNCATE TABLE 表名;

示例：删除 emp3 表中的所有数据。
truncate table emp3;

3.4、清空表时 DELETE 与 TRUNCATE 区别

1. truncate 是整体删除(速度较快)， delete 是逐条删除(速度较慢)；
2. truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因；
3. truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而 不是接着原来的值。而 delete 删除以后， 自增值仍然会继续累加。

4、默认值处理（DEFAULT）

在 MySQL 中可以使用 DEFAULT 为列设定一个默认值。如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。

4.1、创建表时指定列的默认值

语法：
CREATE TABLE 表名(列名 类型 default 默认值,…);

示例：创建 emp3 表，该表包含 emp_id 主键且自动增长，包含name，包含 address 该列默认值为”未知”。
create table emp3(emp_id int primary key auto_increment,name varchar(10),address varchar(50) default ‘Unknown’);

4.2、修改表添加新列并指定默认值

语法：
ALTER TABLE 表名 ADD COLUMN 列名 类型 DEFAULT 默认值;

示例：修改 emp3 表，添加job_id 该列默认值为 0。
alter table emp3 add column job_id int default 0;

4.3、插入数据时的默认值处理

如果在插入数据时并未指定该列的值，那么MySQL 会将默认值添加到该列中。如果是 完全项插入需要使用 default 来占位。

示例：向 emp3 表中添加数据，要求 address 列与job_id 列使用默认值作为该列的值。
insert into emp3(name) values(“admin”);
insert into emp3 values(default,“oldlu”,default,default);

MySQL查询数据

1、SELECT基本查询

1.1、SELECT语句的功能

SELECT 语句从数据库中返回信息。使用一个 SELECT 语句，可以做下面的事：

1. 列选择：能够使用 SELECT 语句的列选择功能选择表中的列，这些列是想
   要用查询返回的。当查询时，能够返回列中的数据。
2. 行选择：能够使用 SELECT 语句的行选择功能选择表中的行，这些行是想
   要用查询返回的。能够使用不同的标准限制看见的行。
3. 连接：能够使用 SELECT 语句的连接功能来集合数据，这些数据被存储在不同的表中，在它们之间可以创建连接，查询出我们所关心的数据。

1.2、SELECT基本语法

基本 SELECT 语句，在最简单的形式中，SELECT 语句必须包含下面的内容：

1. 一个 SELECT 子句，指定被显示的列
2. 一个 FROM 子句，指定表，该表包含 SELECT 子句中的字段列表

在语法中：

1.3、添加测试数据

将data.sql文件通过Navicat导入到MySQL中itbz数据库中。该文件包含了课程中所使用的案例表。

2、SELECT查询中的列选择

2.1、选择所有列

用跟在 SELECT 关键字后面的星号 (*)，你能够显示表中数据的所有列。

示例：查询 departments 表中的所有数据。
select * from departments;

2.2、选择指定列

能够用 SELECT 语句来显示表的指定列，指定列名之间用逗号分隔。

示例：查询 departments 表中所有部门名称(只需要部门名称这一列)。
select department_name from departments;

查看表结构：desc departments;

3、查询中的算术表达式：加+减-乘*除/

3.1、算术表达式：加减乘除使用条件（什么情况下使用）

需要修改数据显示方式，如执行计算，或者作假定推测，这些都可能用到算术表达式。一个算术表达式可以包含列名、固定的数字值和算术运算符。
对查询到的结果做算数表达式处理时，只是对查询的结果做了算数表达式的处理，并不会影响到我们原表当中的数据。
也就是说，根据原表中的数据，写出的select查询语句，想要展示什么，怎么展示数据格式，对原表数据不会有影响，不会改动原表数据

示例：查询雇员的年薪，并显示他们的雇员ID，名字。
（年薪需要计算，月薪salary字段乘以12）
select employee_id,last_name, salary*12 from employees;

3.2、算术表达式：加减乘除运算符的优先级

（1）乘法和除法比加法和减法的优先级高
（2）相同优先级的运算符从左到右计算
（3）圆括号用于强制优先计算，并且使语句更清晰

如果算术表达式包含有一个以上的运算，乘法和除法先计算。如果在一个表达式中的运算符优先级相同，计算从左到右进行。可以用圆括号强制其中的表达式先计算。

示例一：
计算 employees 表中的员工全年薪水加 100 以后的薪水是多少，并显示他们的员工ID与名字。
select employees_id,last_name, 12salary+100 from employees;

示例二：
计算 employees 表中的员工薪水加 100 以后的全年薪水是多少，并显示他们的员工ID与名字。
select employees_id,last_name, 12 (salary+100) from employees;

4、MySQL中定义空值

4.1、定义空值null

空值null是什么？
（1）null 是一个未分配的、未知的，或不适用的值。
（2）null 不是 0，也不是空格。
（3）如果一行中的某个列缺少数据值，该值被置为 null，或者说包含一个空。
（4）空是一个难以获得的、未分配的、未知的，或不适用的值。空 和 0 或者 空格 不相同。 0 是一个数字，而 空格 是一个字符。

4.2、包含空值的算术表达式计算结果为空

算术表达式中的空值：包含空值的算术表达式计算结果为空。

示例：计算年薪包含佣金commission_pct列。
select commission_pct,12salarycommission_pct from employees;

如何解决？后面讲

5、MySQL中的别名

5.1、使用列别名

语法：
SELECT 列名 AS 列别名 FROM 表名 WHERE 条件;

示例：查询 employees 表将雇员 last_name 列定义别名为 name。
select last_name as name from employees;
select last_name name from employees;

5.2、使用表别名

语法：
SELECT 表别名.列名 FROM 表名 as 表别名 WHERE 条件;

示例：查询 employees 表为表定义别名为emp，将雇员 last_name 列定义别名为 name。
select emp.last_name name from employees emp;

6、MySQL中去除重复 (DISTINCT 关键字除去相同的行)

在 SELECT 子句中用 DISTINCT 关键字除去相同的行。
DISTNCT 只能写在SELECT后，要查询的字段之前，只有一个。
DISTINCT 将查询结果中重复的数据去掉，只保留一个数据。
多列去重时，当且仅当多列数据完全重复时才会去重。

6.1、单列去重

语法：
SELECT DISTINCT 列名 FROM 表名;

示例：查询 employees 表，显示唯一的部门 ID。
select distinct department_id from employees;

6.2、多列去重

DISTINCT是对查到的所有的结果集中的这一条数据去重，不是对某一个列的数据去重。多列去重时，当且仅当多列数据完全重复时才会去重。

语法：
SELECT DISTINCT 列名1, 列名2 … FROM 表名;

示例：查询 employees 表，显示部门 ID，月薪并去重。
select distinct department_id,salary from employees;

在这个结果集当中，只是department_id这一列的数据重复了，但是salary没有重复，所以这条数据不是重复数据，所以DISTINCT不会把他剔除掉。

7、MySQL中查询中的行选择（用where条件限制从查询返回的行）

用 WHERE 子句限制从查询返回的行。一个 WHERE 子句包含一个必须满足的条件，WHERE 子句紧跟着 FROM 子句。如果条件是true，返回满足条件的行。

语法：
SELECT * | 投影列 FROM 表名 WHERE 选择条件;

在语法中：
WHERE 限制查询满足条件的行
condition 由列名、表达式、常数和比较操作组成

示例：查询 departments 表中部门 ID 为 90 的部门名称与工作地点 ID。
select department_name,location_id from departments where department_id=90;

8、MySQL中的比较条件：=、>、<、>=、<=、<>或!=

示例一：查询 employees 表中员工薪水大于等于 3000 的员工的姓名与薪水。
select last_name,salary from employees where salary >= 3000;

示例二：查询 employees 表中员工薪水不等于 5000 的员工的姓名与薪水。
select last_name,salary from employees where salary<>5000;
select last_name,salary from employees where salary!=5000;

9、MySQL中的其他比较条件：

BETWEEN … AND …：在两个值之间 (包含)
IN(set)：匹配一个任意值列表
LIKE：匹配一个字符模板
IS NULL：是一个空值
case when：条件分支

9.1、between … and …：范围(包含)查询

BETWEEN … AND …：在两个值之间 (包含)
可以用 BETWEEN 范围条件显示基于一个值范围的行。指定的范围包含一个下限和一个上限。数学中的闭区间。

示例：查询 employees 表，薪水在 3000-8000 之间的雇员ID、名字与薪水。
select employee_id,last_name,salary from employees where salary between 3000 and 8000;

9.2、in：单点列表属性值查询

IN(set)：匹配一个任意值列表
in的作用是给定一个数值列表，判断是否符合数值列表的数据，如果满足这个列表当中所定义的值，那么他就满足这个条件查询出来。

示例：查询 employees 表，找出薪水是 5000,6000,8000 的雇员ID、名字与薪水。
select employee_id,last_name,salary from employees where salary in(5000,6000,8000);

9.3、模糊查询：LIKE、%百分号、_下划线

模糊查询是一种特殊的条件查询，模糊查询使用条件：在需求不明确或者给定的条件不具体时，根据模式匹配来查找符合特定条件的数据，可以使用 LIKE 关键字实现模糊查询。

（1）LIKE：通配符_下划线

通配符：下划线 _ 表示任意单个字符。

（2）LIKE：通配符%百分号

通配符：百分号（%）表示任意长度的任意字符序列。

示例：查询employees表中正数第二个字母是 e 倒数第二个字母也是 e 的雇员名字。
select last_name from employees where last_name like ‘e%e’;

9.4、IS NULL空值、IS NOT NULL非空值：空值查询

在数据库中，有时候数据表的某些字段可能没有值，即为空值（NULL）。

空值表示该字段的值是未知的、不存在的或者没有被填写的。因此，不能用 = ，因为 null 不能等于或不等于任何值。

在SQL查询中，空值null我们可以使用 “IS NULL” 和 “IS NOT NULL” 来判断字段是否为空值或非空值。

空值的应用场景：假设你是一名考试老师，而数据表中的数据就像是你学生们的考试成绩。当某个学生没有参加考试或者成绩尚未出来时，他的考试成绩就是空值。你可以使用 “IS NULL” 来找出没有参加考试的学生，使用 “IS NOT NULL” 来找出已经有成绩的学生。
注意：
1).空值参与条件比较时，条件恒不成立
2).空值参与运算，结果恒为空(去空值函数)

示例一：找出 emloyees 表中那些没有佣金的雇员雇员ID、名字与佣金。
select employee_id,last_name,commission_pct from employees where commission_pct is null;

示例二：找出 employees 表中那些有佣金的雇员ID、名字与佣金。
select employee_id,last_name,commission_pct from employees where commission_pct is not null;

10、MySQL中的逻辑运算：and、or、not、all、any

逻辑运算是一种在条件查询中使用的运算符，它允许我们结合多个条件来过滤出符合特定条件的数据。可以在 WHERE 子句中用 AND 和 OR 运算符使用多个条件。

10.1、and

AND：表示逻辑与，要求必须同时满足多个条件为真，才返回 true。

示例一and：查询 employees 表中雇员薪水是 8000 的并且名字中含有e 的雇员名字与薪水。
select last_name,salary from employees where salary = 8000 and last_name like ‘%e%’;

10.2、or

OR：表示逻辑或，要求满足其中任意一个条件为真，就返回 true。

示例二or：查询 employees 表中雇员薪水是 8000 的或者名字中含有e 的雇员名字与薪水。
select last_name,salary from employees where salary = 8000 or last_name like ‘%e%’;

10.3、not

NOT：表示逻辑非，用于否定一个条件（本来是 true，用了 not 后转为 false）

示例三not：查询 employees 表中雇员名字中不包含 u 的雇员的名字。
select last_name from employees where last_name not like ‘%u%’;

10.4、all（大于all大于集合中的最大值，小于all小于集合中的最小值）

ALL运算符：ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询。

如下，对于ALL运算符的条件和描述说明：

1、数据表以oracle数据库scott用户下的emp表的姓名列ename、薪资列sal、部门编号列deptno为例测试。

2、all>示例：查找工资大于部门ID为20的员工最高工资的所有员工。
select ename,sal from emp
where sal>all(select sal from emp where deptno=20)
order by sal;
分析：由上面列表可知，ALL运算符的大于符号，既是查询结果要大于ALL后面子查询的结果集中的最大值，结果集中的最大值是3000，所以我们上面的查询结果值都大于了3000。

2、all<>示例：查找工资不等于每个部门平均工资的员工。
select ename,sal from emp
where sal<>all(select AVG(sal) from emp group by deptno)
order by sal desc;
分析：由上面列表可知，ALL运算符的不等于符号，既是查询结果要不等于ALL后面子查询的结果集中的任何值，我们上面的查询结果都不属于子查询的结果集中的任一值。

10.5、any（大于any大于集合中的最小值，小于any小于集合中的最大值）

ANY运算符是一个逻辑运算符，它将值与子查询返回的一组值进行比较。 ANY运算符必须以比较运算符：>，>=，<，<=，=，<>开头，后跟子查询。
如下，对于ANY运算符的条件和描述说明：

1、any<示例：查找薪水低于每个部门平均薪水的所有员工。
select ename,sal,deptno from emp
where sal<any(select AVG(sal) from emp group by deptno)
order by deptno,sal desc;
分析：由上面列表可知，ANY运算符的小于符号，既是查询结果要小于ANY后面子查询的结果集中的最大，上面的查询结果小于子查询的结果集中的最大值。

11、MySQL中的优先规则

排序查询 order by

语法:
select column1,column2,column3 FROM table_name (where condition)
order by column1 asc/desc;

说明：
1.ORDER BY + COLUMN1 按照指定的列名进行排序
2.ASC 升序排列，DESC 降序排列
3.order by 子句，它必须位于 select 语句的最后。
4.简单查询+(条件)+排序关键字 + 排序列
5.排序时值相同的排列在一起
6.ASC升序排列时可以省略不写，默认升序排列
7.排序列和排序方式为一组
8.SELECT 语句的执行顺序如下：
执行顺序：FROM ->WHERE ->SELECT->ORDER BY
书写顺序：SELECT -> FROM ->WHERE ->ORDER BY

应用场景：
在查询数据时，我们有时希望对查询结果中返回的行按照某个字段的值进行排序，使用 ORDER BY 子句用于排序，以便更好地查看数据。

1、order by后：单列排序

示例一：查询 employees 表中的所有雇员，显示他们的ID、名字与薪水，并按薪水升序排序。
select employee_id,last_name,salary from employees order by salary;
select employee_id,last_name,salary from employees order by salary asc;

示例二：查询 employees 表中的所有雇员，显示他们的ID与名字，并按雇员名字降序排序。
select employee_id,last_name from employees order by last_name desc;

2、order by后：多列排序

示例：以升叙排序显示 DEPARTMENT_ID 列，同时以降序排序显示 SALARY 列。
select department_id,salary from employees order by department_id asc ,salary desc;

3、order by后：使用别名排序

示例：显示雇员ID，名字。计算雇员的年薪，年薪列别名为annsal，并对该列进行升序排序，
select employee_id,last_name ,12*salary annsal from employees order by annsal;

4、order by后：排序列可以是SELECT后未被查询的字段

order by后：排序列可以是SELECT后未被查询的字段

示例：employees表只查询展示入职日期 HIRE_DATE 列，部门编号为50的部门，按薪资salary升序。
SELECT HIRE_DATE FROM employees WHERE DEPARTMENT_ID=50 ORDER BY salary;

5、order by后：对某列的函数运算排序

order by后：对某列的函数运算排序

示例：查询employees表中的员工编号EMPLOYEE_ID，姓名LAST_NAME，工资SALARY，以及日薪(按30天保留两位小数)，并将表中的数据按照日薪升序排序。
SELECT EMPLOYEE_ID,LAST_NAME,SALARY,ROUND(SALARY/30,2) AS ‘日薪’ FROM employees ORDER BY ROUND(SALARY/30,2);

6、order by后：常量排序是按照SELECT后的字段位置排序

1. 普通的常量在排序列中，语句能够执行，不排序
2. 数字在排序列中，按照该数字对应的SELECT后字段的位置按照该字段排序,数字的值不能超过SELECT后表字段的个数。

示例1：查询employees表，并将数据按照一个常量1升序排列。
（查询结果会按表中的第一列EMPLOYEE_ID升序）
SELECT * FROM employees ORDER BY 1;

示例2：查询employees表，并将数据按照一个常量5降序排列。
（查询结果会按表中的第五列PHONE_NUMBER降序）
SELECT * FROM employees ORDER BY 5 DESC;

示例3：查询employees表，并将数据按照一个常量单引号 ‘5’ 降序排列。
（查询结果不会按表中的第五列PHONE_NUMBER降序）
SELECT * FROM employees ORDER BY ’5‘ DESC;

示例4：查询employees表共11列，并将数据按照一个常量12升序排列。
（查询结果不会按表中的第五列PHONE_NUMBER升序，报错）
SELECT * FROM employees ORDER BY 12;

SQL函数

1、函数介绍

函数是 SQL 的一个非常强有力的特性，函数能够用于下面的目的：

1. 执行数据计算
2. 修改单个数据项
3. 操纵输出进行行分组
4. 格式化显示的日期和数字
5. 转换列数据类型

SQL 函数有输入参数，并且总有一个返回值。

2、函数分类：单行函数、多行函数

SQL函数的两种类型：单行函数、多行函数

2.1、单行函数

单行函数仅对单个行进行运算，并且每行返回一个结果。

常见的函数类型：

• 字符
• 数字
• 日期
• 转换

2.1、多行函数

多行函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。

3、单行函数

3.1、单行函数作用

1. 操纵数据项
2. 接受多个参数并返回一个值
3. 作用于每一个返回行
4. 每行返回一个结果
5. 可以修改数据类型
6. 可以嵌套
7. 接受多个参数，参数可以是一个列或者一个表达式

3.2、单行函数分类

字符函数、数字函数、日期函数、转换函数、通用函数

4、字符函数

函数调用：
在mysql中调用函数，函数中的参数值并不是来源于某个表的某个列时，是自己给的自定义参数，在mysql中用select关键字来调用这个函数。在不同的关系型数据库中调用函数方式是不一样的，而在oracle中用select 函数 from dual;dual伪表

UPPER(s) 或 UCASE(s)：大写处理函数

1. 小写处理函数：UPPER(s)或UCASE(s)
2. 函数作用：将小写字符串 s 转换为大写
3. 实例：将小写字符串 abcd 转换为小写： SELECT UPPER(“abcd”); – ABCD
   

LOWER(s) 或 LCASE(s)：小写处理函数

1. 小写处理函数：LOWER(s)或LCASE(s)
2. 函数作用：将大写字符串 S 转换为小写
3. 实例：将字符串 ABCD 转换为小写： SELECT LOWER(“ABCD”); – abcd
   
4. 示例：显示雇员 Davies 的雇员号、姓名和部门号，将姓名转换为大写。
   select employee_id,UPPER(last_name),department_id from employees where last_name = ‘davies’;
   
   davies查询的是小写，查询出来是大写
   mysql在帮我们做数据查询时，默认自动做了大小写忽略，能不能让这个表的列严格区分大小写呢？在创建表定义列时加一个binnry关键字这一列就会严格区分大小写。

LENGTH(s) ：求字符串长度函数：返回字符串 s 的长度

返回字符串oldlu的字符数：
SELECT LENGTH(“oldlu”);
–5;

CONCAT(s1,s2…sn)：连接函数：字符串 s1,s2 等多个字符串合并为一个字符串

合并多个字符串：
SELECT CONCAT(“【”,EMPLOYEE_ID, “】”,“【”,LAST_NAME, “】”) AS 编号姓名 from employees;

经常用：比如在做财开报表项目查询的时候：把两张表中的数据取到一个视图中的sql：
SELECT busi_lend_apply.id AS id,concat( ‘[’, busi_lend_apply.property_no, ‘]’,busi_lend_apply.property_name ) AS cjspd FROM busi_lend_apply WHERE ( busi_lend_apply.Approve = ‘1’)

LPAD(s1,len,s2)：左填充函数：在字符串 s1 的左侧开始填充字符串s2，使字符串长度达到len

将字符串 x 左填充到 oldlu 字符串的开始处，总长度8，左侧填充x：
SELECT LPAD(‘oldlu’,8,‘x’); – xxxoldlu

RPAD(s1,len,s2)：右填充函数：在字符串 s1 的右侧开始填充字符串s2，使字符串长度达到len

将字符串 x 右填充到 oldlu 字符串的开始处，总长度8，右侧填充x：
SELECT RPAD(‘oldlu’,8,‘x’); --oldluxxx

LTRIM(s)：左去除函数：去掉字符串 s 开始处的空格

去掉字符串 oldlu开始处的空格：
SELECT LTRIM(" oldlu") ;-- oldlu

RTRIM(s)：右去除函数：去掉字符串 s 结尾处的空格

去掉字符串 oldlu 的末尾空格：
SELECT RTRIM("oldlu "); – oldlu

TRIM(s)：双侧去除函数：去掉字符串 s 开始和结尾处的空格

去掉字符串 oldlu 的首尾空格：
SELECT TRIM(’ oldlu ');–oldlu

REPLACE(s,s1,s2)：替换函数：将字符串 s处的 s1 替换成字符串 s2

将字符串 oldlu 中的字符 o 替换为字符 O：
SELECT REPLACE(‘oldlu’,‘o’,‘O’); --Oldlu

REVERSE(s)：字符串逆序函数：将字符串s的顺序反过来

将字符串 abc 的顺序反过来：
SELECT REVERSE(‘abc’); – cba

SUBSTR(s, start, length)：字符串截取函数：从字符串 s 的 start 位置截取长度为 length 的子字符串

从字符串 OLDLU中的第 2 个位置截取 3个字符：
SELECT SUBSTR(“OLDLU”, 2, 3); --LDL

SUBSTRING(s, start, length)：字符串截取函数：从字符串 s 的 start 位置截取长度为 length 的子字符串

从字符串 OLDLU中的第 2 个位置截取 3个字符：
SELECT SUBSTRING(“OLDLU”, 2, 3); --LDL

示例：

显示所有工作岗位名称从第 4 个字符位置开始，包含字符串 REP的雇员的ID信息，将雇员的姓和名连接显示在一起，还显示雇员名的的长度，以及名字中字母 a 的位置。
SELECT employee_id, CONCAT(last_name，first_name) NAME,job_id, LENGTH(last_name),INSTR(last_name,‘a’) “Contains ‘a’?” FROM employees WHERE SUBSTR(job_id, 4) = ‘REP’;

5、数字函数

ROUND(column|expression, n) 四舍五入函数

ROUND( )函数四舍五入：列、表达式或者 n 位小数的值。如果第二个参数是 0 或者缺少，值被四舍五入为整数。如果第二个参数是 2值被四舍五入为两位小数。如果第二个参数是–2，值被四舍五入到小数点左边两位。

示例：SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1);

TRUNCATE(column|expression,n) 截断函数

TRUNCATE函数的作用类似于 ROUND 函数。如果第二个参数是 0或者缺少，值被截断为整数。如果第二个参数是 2，值被截断为两位小数。如果第二个参数是–2，值被截断到小数点左边两位。与ROUND 最大的区别是不会进行四舍五入。

示例：SELECT TRUNCATE(45.923,2);

MOD(m,n) 取余函数

MOD 函数找出m 除以n的余数。

示例：所有job_id是SA_REP的雇员的名字，薪水以及薪水被5000除后的余数。

RAND()：返回 0 到 1 的随机数

SELECT RAND()

ROUND(x)：返回离 x 最近的整数

SELECT ROUND(1.23456)

ABS(x)：返回 x 的绝对值

返回 -1 的绝对值：
SELECT ABS(-1);-- 返回1

ACOS(x)：求 x 的反余弦值(参数是弧度)

SELECT ACOS(0.25);

ASIN(x)：求 x 的反余弦值(参数是弧度)

SELECT ACOS(0.25);

ATAN(x)：求反正切值(参数是弧度)

SELECT ATAN(2.5);

ATAN2(n, m)：求反正切值(参数是弧度)

SELECT ATAN2(-0.8, 2);

AVG(expression)：返回一个表达式的平均值，expression 是一个字段

返回 employees 表中SALARY字段的平均值：
SELECT AVG(SALARY) AS AverageSalary FROM employees;

CEIL(x)：返回大于或等于 x 的最小整数

SELECT CEIL(1.5); – 返回2

CEILING(x)：返回大于或等于 x 的最小整数

SELECT CEILING(1.5); – 返回2

SIGN(x)：返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1

SELECT SIGN(-10)

SIN(x)：求正弦值(参数是弧度)

SELECT SIN(RADIANS(30))

COS(x)：求余弦值(参数是弧度)

SELECT COS(2);

COT(x)：求余切值(参数是弧度)

SELECT COT(6);

TAN(x)：求正切值(参数是弧度)

SELECT TAN(1.75); – -5.52037992250933

COUNT(expression)：返回查询的记录总数，expression 参数是一个字段或者 * 号

返回 employees 表中 SALARY 字段总共有多少条记录：
SELECT COUNT(SALARY) AS NumberOfSALARY FROM employees;

DEGREES(x)：将弧度转换为角度

SELECT DEGREES(3.1415926535898);-- 180

RADIANS(x)：将角度转换为弧度

n DIV m：整除，n 为被除数，m 为除数

计算 10 除于 5：
SELECT 10 DIV 5; – 2

EXP(x)：返回 e 的 x 次方

计算 e 的三次方： SELECT EXP(3);-- 20.085536923188

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

LN：返回数字的自然对数，以 e 为底。

返回 2 的自然对数：
SELECT LN(2); – 0.6931471805599453

LOG(x) 或 LOG(base, x)：返回自然对数(以 e 为底的对数)，如果带有 base 参数，则base 为指定带底数。

LOG(x) 或 LOG(base, x)：返回自然对数(以 e 为底的对数)，如果带有 base 参数，则base 为指定带底数。
SELECT LOG(20.085536923188) – 3

SELECT LOG(2, 4); – 2

LOG10(x)：返回以 10 为底的对数

SELECT LOG10(100) – 2

PI()：返回圆周率(3.141593)

SELECT PI() --3.141593

POW(x,y)：返回 x 的 y 次方

2 的 3 次方： SELECT POW(2,3) – 8

POWER(x,y)：返回 x 的 y 次方

2 的 3 次方： SELECT POWER(2,3) – 8

SQRT(x)：返回x的平方根

25 的平方根： SELECT SQRT(25) – 5

6、日期函数

在MySQL中允许直接使用字符串表示日期，但是要求字符串的日期格式必须为：‘YYYY-MM-DD HH:MI:SS’ 或者‘YYYY/MM/DD HH:MI:SS’;

CURDATE()：返回当前日期

SELECT CURDATE(); -> 2018-09-19

CURTIME()：返回当前时间

SELECT CURTIME(); -> 19:59:02

CURRENT_DATE()：返回当前日期

SELECT CURRENT_DATE(); -> 2018-09-19

CURRENT_TIME()：返回当前时间

SELECT CURRENT_TIME(); -> 19:59:02

DATE()：从日期或日期时间表达式中提取日期值

SELECT DATE(“2017-06-15”); -> 2017-06-15

DATEDIFF(d1,d2)：计算日期 d1->d2 之间相隔的天数

SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’) ->-32

DAY(d)：返回日期值 d 的日期部分

SELECT DAY(“2017-06-15”);

DAYNAME(d)：返回日期 d 是星期几，如Monday,Tuesday

SELECT DAYNAME(‘2011-11-11 11:11:11’) ->Friday

DAYOFMONTH(d)：计算日期 d 是本月的第几天

SELECT DAYOFMONTH(‘2011-11-11 11:11:11’) ->11

DAYOFWEEK(d)：日期 d 今天是星期几，1 星期日，2 星期一，以此类推

SELECT DAYOFWEEK(‘2011-11-11 11:11:11’) ->6

DAYOFYEAR(d)：计算日期 d 是本年的第几天

SELECT DAYOFYEAR(‘2011-11-11 11:11:11’) ->315

HOUR(t)：返回 t 中的小时值

SELECT HOUR(‘1:2:3’) -> 1

LAST_DAY(d)：返回给给定日期的那一月份的最后一天

SELECT LAST_DAY(“2017-06-20”); -> 2017-06-30

MONTHNAME(d)：返回日期当中的月份名称，如 November

SELECT MONTHNAME(‘2011-11-11 11:11:11’) -> November

MONTH(d)：返回日期d中的月份值，1 到 12

SELECT MONTH(‘2011-11-11 11:11:11’) ->11

NOW()：返回当前日期和时间

SELECT NOW() -> 2018-09-19 20:57:43

SECOND(t)：返回 t 中的秒钟值

SELECT SECOND(‘1:2:3’) -> 3

SYSDATE()：返回当前日期和时间

SELECT SYSDATE() -> 2018-09-19 20:57:43

TIMEDIFF(time1,time2)：计算时间差值

SELECT TIMEDIFF(“13:10:11”, “13:10:10”); ->00:00:01

TO_DAYS(d)：计算日期 d 距离 0000 年 1 月 1 日的天数

SELECT TO_DAYS(‘0001-01-01 01:01:01’) -> 366

WEEK(d)：计算日期 d 是本年的第几个星期，范围是 0 到 53

SELECT WEEK(‘2011-11-11 11:11:11’) -> 45

WEEKDAY(d)：日期 d 是星期几，0 表示星期一，1 表示星期二

SELECT WEEKDAY(“2017-06-15”); -> 3

WEEKOFYEAR(d)：计算日期 d 是本年的第几个星期，范围是 0 到 53

SELECT WEEKOFYEAR(‘2011-11-11 11:11:11’)

YEAR(d)：返回年份

SELECT YEAR(“2017-06-15”); -> 2017

示例： (SYSDATE()-hire_date)/7

准备：向 employees 表中添加一条数据，雇员ID：300，名字：kevin ，email：kevin@sxt.cn，入职时间：2049-5-1 8:30:30，工作部门：‘IT_PROG’。
insert into employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID)
values(300,‘kevin’,‘kevin@sxt.cn’,‘2049-5-18:30:30’,‘IT_PROG’);
示例：
显示所有在部门 90 中的雇员的名字和从业的周数。雇员的总工作时间以周计算，用当前日期 (SYSDATE) 减去雇员的受顾日期，再除以 7。
SELECT last_name, (SYSDATE()-hire_date)/7 AS WEEKS FROM employees WHERE department_id =90;

sql145题：示例21、常见日期函数

求解以下日期：
本月的第一天，最后一天
上月的第一天，最后一天
本周的第一天，最后一天
本年的第一天，最后一天
要求使用系统日期，例如：
当前是2019-11-16
每月的第一天应该为 2019-11-01，最后一天应该为2019-11-30
上月的第一天应该为 2019-10-01，最后一天应该为2019-10-31
本周的第一天应该为 2019-11-11，最后一天应该为2019-11-17
本年的第一天应该为 2019-01-01，最后一天应该为2019-12-31

7、转换函数

7.1、隐式数据类型转换

隐式数据类型转换是指MySQL服务器能够自动地进行类型转换。

如：可以将标准格式的字串日期自动转换为日期类型。
MySQL字符串日期格式为：‘YYYY-MM-DD HH:MI:SS’ 或 ‘YYYY/MM/DD HH:MI:SS’;

7.2、显示数据类型转换

显示数据类型转换是指需要依赖转换函数来完成相关类型的转换。

如：
DATE_FORMAT(date,format) 将日期转换成字符串;
STR_TO_DATE(str,format) 将字符串转换成日期;

示例一：

示例一：向 employees 表中添加一条数据，雇员ID：400，名字：oldlu，email：oldlu@sxt.cn，入职时间：2049 年 5 月 5 日，工作部门：‘IT_PROG’。
insert into employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID)
values(400,‘oldlu’,‘oldlu@sxt.cn’,STR_TO_DATE(‘2049 年 5 月 5 日’,‘%Y 年%m 月%d
日’),‘IT_PROG’);

示例二：

示例二：查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM 月 dd 日。
select DATE_FORMAT(hire_date,‘%Y 年%m 月%d日’) from employees where last_name = ‘King’;

8、通用函数

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(column)判断是否null函数：判断列是否为空null，是空null返回值1，不是空null返回值0。继续用case when函数判断。

ISNULL(expression)：判断表达式是否为 NULL，是空NULL返回值1，不是空NULL返回值0。

SELECT ISNULL(NULL); ->1

示例：isnull(column)判断是否null函数：判断列是否为空null，是空null返回值1，不是空null返回值0。继续用case when函数判断。

1. isnull(column)判断是否null函数：判断列是否为空null，是空null返回值1，不是空null返回值0。
2. 根据该列是否为空，继续用case when函数判断：如果为空返回999，不为空返回表中某个字段的值来走工作流，case when 函数起别名为jgzt结果状态。

示例：真实项目：查询员工编号，佣金，判断佣金为空返回999不为空返回员工姓名，判断列的别名为jgzt结果状态。
isnull(column)判断是否null函数：判断列是否为空null，是空null返回值1，不是空null返回值0。
根据comm佣金列是否为空，继续用case when函数判断：如果comm佣金列为空返回999，不为空返回emp表中ename员工姓名列的值来走工作流，case when 函数起别名为jgzt结果状态。
SELECT
empno,
comm,
isnull(comm),
CASE WHEN isnull(comm) THEN 999
ELSE emp.ename
END AS jgzt
FROM emp;

NULLIF(expr1, expr2)：比较两个参数是否相同，如果参数 expr1 与 expr2 相等 返回 NULL，否则返回 expr1

SELECT NULLIF(25,25); ->

COALESCE(expr1, expr2, …, expr_n)：返回参数中的第一个非空表达式（从左向右）

SELECT COALESCE(NULL, NULL, NULL, ‘bjsxt.com’, NULL, ‘google.com’); -> bjsxt.com

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，而当有一个成立之后，后面的就不执行了。

条件分支 case when 是 SQL 中用于根据条件进行分支处理的语法。它类似于其他编程语言中的 if else 条件判断语句，允许我们根据不同的条件选择不同的结果返回。

使用 case when 可以在查询结果中根据特定的条件动态生成新的列或对现有的列进行转换。

举个例子：假设你是一位餐厅的服务员，客人点了不同的菜品，而你需要根据客人点的菜来确定服务的方式。如果客人点了鱼，你会用餐具和服务方式适合吃鱼的方式来招待他们；如果客人点了牛排，你会用适合牛排的餐具和服务方式。case when 就像你根据客人点的菜品来选择不同服务方式的过程。

（1）单分支条件判断：

语法：
CASE WHEN 条件1 THEN 值1 END;
判断当符合条件1时，该函数返回值1 结束

示例：将30号部门的员工标记为销售人员，case when这一列别名为员工分类。
SELECT DEPARTMENT_ID,CASE WHEN DEPARTMENT_ID=30 THEN ‘销售人员’ END ‘员工分类’ FROM employees;

（2）双分支条件判断：

语法：
CASE WHEN 条件1 THEN 值1 ELSE 值2 END;
判断当符合条件时，该函数返回值1 不符合条件时返回2

示例：将30号部门的员工标记为销售人员，其他部门的员工标记为普通员工。
SELECT DEPARTMENT_ID,CASE WHEN DEPARTMENT_ID=30 THEN ‘销售人员’ ELSE ‘普通员工’ END FROM employees;

（3）多分支条件判断：

语法：
**
CASE WHEN 条件1 THEN 值1
WHEN 条件2 THEN 值2
WHEN 条件3 THEN 值3
…
END; **
判断当符合条件1时，该函数返回值1，不符合条件1符合条件2时返回值2，不符合条件1、2符合条件3时返回值3，结束。

示例：将30号部门的员工标记为销售人员，20部门的员工标记为研究员工，其他部门的员工标记为普通员工。判断函数起别名员工分类。
SELECT DEPTNO,CASE WHEN DEPTNO = 30 THEN ‘销售人员’ WHEN DEPTNO=20 THEN ‘研究人员’ ELSE ‘普通员工’ END “员工分类” FROM EMP;

sql145题：示例11、case when 行转列的用法

#创建表：
create table case_when_line_column
(
Lyear varchar(10),
Lmonth varchar(10),
Amount decimal(18,2)
);
#输入数据：
insert into case_when_line_column values (‘2017’,‘1’,1.1);
insert into case_when_line_column values (‘2017’,‘2’,1.2);
insert into case_when_line_column values (‘2017’,‘3’,1.3);
insert into case_when_line_column values (‘2017’,‘4’,1.4);
insert into case_when_line_column values (‘2018’,‘1’,2.1);
insert into case_when_line_column values (‘2018’,‘2’,2.2);
insert into case_when_line_column values (‘2018’,‘3’,2.3);
insert into case_when_line_column values (‘2018’,‘4’,2.4);
#查询表数据：
select * from case_when_line_column;

#需求：将表查出这样的结果，将月份列和数量列由原来的行显示转换为列显示。

#需求SQL：
select Lyear,
min(case when Lmonth=‘1’ then Amount else null end) as m1,
min(case when Lmonth=‘2’ then Amount else null end) as m2,
min(case when Lmonth=‘3’ then Amount else null end) as m3,
min(case when Lmonth=‘4’ then Amount else null end) as m4
from case_when_line_column
group by Lyear;

示例一：if(ISNULL(commission_pct),‘SAL’,‘SAL+COMM’)

示例一：查询部门编号是50或者80的员工信息，包含他们的名字、薪水、佣金。在income列中，如果有佣金则显示‘SAL+COMM’，无佣金则显示’SAL’。
SELECT last_name, salary, commission_pct,
if(ISNULL(commission_pct),‘SAL’,‘SAL+COMM’) income
FROM employees
WHERE department_id IN (50, 80);

示例二：IFNULL(commission_pct, 0)

示例二：计算雇员的年报酬，你需要用 12 乘以月薪，再加上它的佣金 (等于年薪乘以佣金百分比)。
SELECT last_name, salary,
IFNULL(commission_pct, 0), (salary12) +
(salary12*IFNULL(commission_pct, 0)) AN_SAL
FROM employees;

示例三：NULLIF(LENGTH(first_name)

示例三：查询员工表，显示他们的名字、名字的长度该列名为expr1，姓氏、姓氏的长度该列名为expr2。在result列中，如果名字与姓氏的长度相同则显示空，如果不相同则显示名字长度。
SELECT first_name, LENGTH(first_name) “expr1”,
last_name, LENGTH(last_name) “expr2”,
NULLIF(LENGTH(first_name),
LENGTH(last_name)) result FROM employees;

示例四：COALESCE(commission_pct, salary, 10) comm

示例四：查询员工表，显示他们的名字，如果 COMMISSION_PCT 值是非空，显示它。如果COMMISSION_PCT 值是空，则显示 SALARY 。如果 COMMISSION_PCT 和SALARY 值都是空，那么显示 10。在结果中对佣金列升序排序。
SELECT last_name,COALESCE(commission_pct, salary, 10) comm
FROM employees ORDER BY commission_pct;

示例五：CASE WHEN THEN ELSE END

示例五：查询员工表，如果 JOB_ID 是 IT_PROG，薪水增加 10%；如果 JOB_ID 是 ST_CLERK，薪水增加 15%；如果 JOB_ID 是 SA_REP，薪水增加 20%。对于所有其他的工作角色，不增加薪水。
SELECT last_name, job_id, salary,CASE job_id WHEN ‘IT_PROG’ THEN 1.10salary
WHEN ‘ST_CLERK’ THEN 1.15salary
WHEN ‘SA_REP’ THEN 1.20*salary
ELSE salary END “REVISED_SALARY” FROM employees;

示例六：不动产开发报表待发起页面取数。三个页面：待发起、已办结、已中止。confirm确认状态0-未确认1-已确认。delete_flag删除标记0-未删除1-已删除。

1. #取出划入申请文件reo_document表的申请id_apply已确认的数据。
   SELECT
   belong_type,
   id_apply,
   #delete_flag删除标记0-未删除1-已删除
   #如果删除标记delete_flag不为空返回delete_flag的值，如果删除标记delete_flag的值为空返回0，取删除标记为1已删除的。
   ifnull(delete_flag, 0)= 1,
   #confirm确认状态0-未确认1-已确认
   #如果确认状态confirm不为空返回confirm的值，如果确认状态confirm的值为空返回0，取确认状态为1已确认的。
   ifnull(confirm, 0)= 1,
   #CASE WHEN 条件判断：如果删除标记是已删除的返回值1，如果确认状态是已确认的返回值3，其他情况返回值2
   #max取条件判断的最大值已确认的数据，起别名为WfRunResult结果状态
   max(CASE WHEN ifnull( delete_flag, 0)=1 THEN 1 WHEN ifnull(confirm, 0)=1 THEN 3 ELSE 2 END ) WfRunResult
   FROM
   reo_document
   WHERE
   type=1 AND belong_type=0
   GROUP BY
   belong_type,
   id_apply,
   ifnull(delete_flag, 0)= 1,
   ifnull(confirm, 0)=1;
2. #划入申请文件reo_document表的待发起页面：要求：从reo_property_apply划入申请基本信息表取得数据是type='0’划入、这条数据流程的结果状态为WfRunResult=1、没有删除的delete_flag=0、
   并且这个划入申请文件reo_document表的申请id不能是reo_document表已经确认的的数据。
   select * from reo_property_apply
   where
   type=‘0’ and WfRunResult=1 and delete_flag=0 and id not in(
   select id_apply from(
   select
   belong_type,
   id_apply,
   max(case when ifnull(delete_flag,0)=1 then 1 when ifnull(confirm,0)=1 then 3 else 2 end) WfRunResult
   from reo_document where type=1 and belong_type=0 group by belong_type,id_apply) x
   where x.WfRunResult <>0 and x.WfRunResult <>1
   )
   and sclc<=2 and sclc>=1;

多表查询（联合查询、表连接）

1、多表查询简介

1.1、什么是多表查询（联合查询）？

多表查询又称联合查询，只写一条sql语句并且从多张表中获取数据。

1.2、笛卡尔乘积（交叉连接：CROSS JOIN）

笛卡尔乘积 ：
当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积(Cartesian product)，其中所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的行，其结果没有什么用。你应该在 WHERE 子句中始终包含一个有效的连接条件，除非你有特殊的需求，需要从所有表中组合所有的行。

1.3、多表查询分类

sql92标准：内连接(等值连接 、非等值连接 、 自连接)。
sql99标准：内连接、外连接(左外、右外、全外(MySQL不支持全外连接))、交叉连接。

2、SQL92标准中的查询

2.1、等值连接（相当于inner join：内连接）（常用）

语法：
（不写inner jion只用where条件的等值连接）
SELECT colum1, colum2, column3 FROM table1, table2, table3
WHERE table1.colum = table2.colum AND table2.colum = table3.colum;

（等值连接：inner join）（inner join：内连接，后面讲）
SELECT colum1, colum2, column3 FROM table1
INNER JOIN table2 on table1.colum = table2.colum
INNER JOIN table3 on table2.colum = table3.colum;

（1）等值连接：WHERE

为了保证查询数据时当两个表连接列值的相等性作为连接条件查询出的数据非常准确，两张表中这两个列分别做一个主外键约束的定义，一个表该列为主键列，另一个表中该列为外键列。（主外键不是必须的，不具备也可以查询）

注意：
为了确定一个雇员的部门名，需要比较 EMPLOYEES 表中的 DEPARTMENT_ID 列与 DEPARTMENTS 表中的 DEPARTMENT_ID 列的值。在 EMPLOYEES 和DEPARTMENTS 表之间的关系是一个相等 (equal join) 关系，即，两个表中DEPARTMENT_ID 列的值必须相等。

（2）等值连接特点：使用值的相等性作为连接条件查询出多表的交集部分

（1）多表等值连接的结果为多表的交集部分；
（2）n表连接，至少需要n-1个连接条件；
（3）多表不分主次，没有顺序要求；
（4）一般为表起别名，提高阅读性和性能；
（5）可以搭配排序、分组、筛选….等子句使用；

（3）等值连接的使用：

1. SELECT 子句指定要返回的列名：
   − employee last name、employee number 和 department number，这些是 EMPLOYEES 表中的列
   − department number、department name 和 location ID，这些是 DEPARTMENTS 表中的列
2. FROM 子句指定数据库必须访问的两个表：
   − EMPLOYEES 表
   − DEPARTMENTS 表
3. WHERE 子句指定表怎样被连接：
   EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID，因为 DEPARTMENT_ID 列是两个表的同名列，它必须用表名做前缀以避免混淆。

（4）增加搜索条件：and

（5）添加查询条件：WHERE限制不能缺的列

除连接之外，可能还要求用 WHERE 子句在连接中限制一个或多个表中的行。
限制不能缺的列：

（6）限制不明确的列名：WHERE 子句中用表的名字限制列的名字

需要在 WHERE 子句中用表的名字限制列的名字以避免含糊不清。没有表前缀，DEPARTMENT_ID
列可能来自 DEPARTMENTS 表，也可能来自 EMPLOYEES 表，这种情况下需要添加表前缀来执行查询。
如果列名在两个表之间不相同，就不需要限定列。但是，使用表前缀可以改善性能，因为MySQL服务器可以根据表前缀找到对应的列。
必须限定不明确的列名也适用于在其它子句中可能引起混淆的那些列，例如 SELECT子句或 ORDERBY 子句。

（7）使用表别名：表别名定义原则

表别名定义原则：
（1）表别名不易过长，短一些更好。
（2）表别名应该是有意义的。
（3）表别名只对当前的 SELECT 语句有效。

（8）多表连接：n表连接，至少需要个连接条件

（9）示例（inner join）：

示例一：查询雇员 King 所在的部门名称。
select d.department_name from employees e,departments d where e.dept_id =
d.department_id and e.last_name = ‘King’;

示例二：显示每个雇员的 last name、departmentname 和 city（连接三张表）
SELECT e.last_name, d.department_name, l.city FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id;
SELECT e.last_name, d.department_name, l.city FROM employees e
INNER JOIN departments d on e.department_id = d.department_id
INNER JOIN locations l on d.location_id = l.location_id;

2.2、非等值连接

（1）非等值连接条件：

1. 一个非等值连接是一种使用大于作为连接条件。
2. 一个非等值连接是一种使用小于作为连接条件。
3. 一个非等值连接是一种使用不等于作为连接条件。
   
   一个非等值连接是一种不同于等值操作的连接条件。 EMPLOYEES 表 和JOB_GRADES A 表之间的关系有一个非等值连接例子。在两个表之间的关系是EMPLOYEES 表中的 SALARY 列必须是 JOB_GRADES 表的 LOWEST_SALARY 和HIGHEST_SALARY 列之间的值。使用不同于等于 (=) 的操作符获得关系。
   

（2）示例：创建JOB_GRADES表，根据薪水级别表查询所有雇员的薪水级别

示例一：创建 job_grades 表，包含 lowest_sal ，highest_sal ，grade_level。
create table job_grades(lowest_sal int,highest_sal int ,grade_level varchar(30));

示例二：
插入数据
1000 2999 A
2000 4999 B
5000 7999 C
8000 12000 D
insert into job_grades values(1000,2999,‘A’);
insert into job_grades values(2000,4999,‘B’);
insert into job_grades values(5000,7999,‘C’);
insert into job_grades values(8000,12000,‘D’);

示例三：查询所有雇员的薪水级别。
select e.last_name,j.grade_level from employees e ,job_grades j where e.salary
between j.lowest_sal and j.highest_sal;

2.3、自连接

（1）自连接：自己连接自己，表别名的起名非常重要

连接一个表到它自己。有时需要连接一个表到它自己。在employees表中，有employees_id员工id列和manager_id经理id列，员工和经理都在employees表，通过manager_id区分谁是谁的经理，现在要查询员工以及员工连同他们的经理是谁？为了找到每个雇员的经理的名字，则需要连接 EMPLOYEES 表到它自己，或执行一个自连接。


图片中的例子连接 EMPLOYEES 表到它自己。为了在 FROM 子句中模拟两个表，对于相同的表 EMPLOYEES，用两个别名，分别为worker 作为员工表和 manager作为经理表。在该例中，WHERE 子句包含的连接意味着“一个工人的经理号匹配该经理的雇员号”，员工的经理id=经理的员工id。

（2）示例：自连接

示例一：查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为W，经理列别名为M。
分析：worker雇员和manager经理在同一张employees表，经理也是员工，
相当于从两张表employees起别名worker员工表和employees起别名manager经理表取数据，
条件是worker表的经理id等于manager的雇员id。
SELECT worker.LAST_NAME W,manager.LAST_NAME M from employees worker,employees manager where worker.MANAGER_ID = manager.EMPLOYEE_ID;

示例二：查询Fox的经理是谁？显示他的名字。
分析：worker雇员和manager经理在同一张employees表，经理也是员工，
相当于从两张表employees起别名worker员工表和employees起别名manager经理表取数据，
条件是worker表的经理id等于manager的雇员id，并且worker表雇员名字是Fox。
SELECT worker.LAST_NAME,manager.LAST_NAME from employees worker,employees manager where worker.MANAGER_ID = manager.EMPLOYEE_ID AND worker.LAST_NAME = ‘Fox’;

3、SQL99标准中的查询

MySQL5.7 支持部分的 SQL99 标准。

3.1、交叉连接（CROSS JOIN：笛卡尔乘积）

（1）什么是交叉连接（笛卡尔乘积）

CROSS JOIN 是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的 每一行 与右表的 每一行 进行组合，返回的结果是两个表的笛卡尔乘积。
（sql92当中，from两个表，不给连接条件，就是一个笛卡尔乘积）
我们写sql肯定不写笛卡尔乘积，避免笛卡尔乘积。

（2）示例：写一个交叉连接笛卡尔乘积

示例：使用交叉连接查询 employees 表与 departments 表。
select * from employees cross join departments;
（这条sql执行之后查询出了2000多条数据）

3.2、自然连接（NATURAL JOIN）

（1）什么是自然连接？

连接只能发生在两个表中有相同。名字和数据类型的列上。如果列有相同的名字，但数据类型不同，NATURAL JOIN 语法会引起错误。

（2）自然连接查询

在图片例子中，LOCATIONS 表被用 LOCATION_ID 列连接到 DEPARTMENT表，这是在两个表中唯一名字相同的列。如果存在其它的同名同类型的列，自然连接会使用等值连接的方式连接他们，连接条件的关系为and。

自然连接也可以被写为等值连接：
SELECT d.department_id, d.department_name,
d.location_id , l.city
FROM
departments d , locations l
WHERE
d.location_id = l.location_id;

（3）示例：自然连接natural join

示例：使用自然连接查询所有有部门的雇员的名字以及部门名称。
select e.last_name,d.department_name from employees e natural join departments d;
（这两张表的e.department_id和d.department_id列名相同类型相同才可以使用natural join自然连接）

3.3、内连接（等值连接INNER JOIN）

语法：（等值连接：inner join）
SELECT colum1, colum2, column3 FROM table1
INNER JOIN table2 on table1.colum = table2.colum
INNER JOIN table3 on table2.colum = table3.colum;
SELECT 查询列表;
FROM 表1 别名;
INNER JOIN 连接表(INNER关键字可省略);
ON 连接条件;

（1）等值连接：INNER JOIN

INNER JOIN 根据两个表之间的关联条件，只返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行。使用 INNER_JOIN 后，只有两个表之间存在对应关系的数据才会被放到查询结果中。

（2）用ON子句指定连接条件

（3）用ON子句指定更多的连接条件

（4）示例：INNER JOIN

示例：查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。
select e.employee_id,e.salary,d.department_name from employees e inner JOIN departments d on e.department_id = d.department_id where e.last_name = ‘Fox’;

（5）示例：不动产报表开发项目

不动产信息表的id，要在出售申请表、资金支付表、不动产信息表三表关联内
select * from reo_property_info where id in (
SELECT c.id FROM reo_fund_appro a
JOIN asset_property_sale b ON a.id_apply=b.id
#资金支付表的申请id等于申请表申请id
JOIN reo_property_info c ON b.id_property=c.id
#不动产信息表的不动产id等于申请表的不动产id
WHERE a.belong_type=11
#立项类型为11出售
)

3.3、外连接查询（OUTER JOIN）

（1）什么是外连接？

1. 内连接：在 SQL: 1999 中，连接两个表，仅返回匹配的行的连接，称为内连接
2. 左外（右外）连接：在两个表之间的连接，返回内连接的结果，同时还返回不匹配行的左(或右) 表的连接，称为左(或右)外连接
3. 全外连接：在两个表之间的连接，返回内连接的结果， 同时还返回左和右连接，称为全外连接

（2）孤儿数据（Orphan Data）

孤儿数据是指被连接的列的值为空的数据。

（3）左外连接（LEFT OUTER JOIN）

语法：
SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名 ON 连接条件;

左外连接（LEFT OUTER JOIN）

左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的行，该查询也会取回 EMPLOYEES 表中所有的行。

（4）左外连接示例：

示例：查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。
分析：注意点：我们要的是雇员。
从员工表取雇员名字last_name，部门表取部门名称department_name，
两张表以部门id作为关联条件，这样取出的数据只是员工有部门的数据，
取出的员工表员工数据不全，因为员工表的员工还有没有部门的没有取出来，
所以用左连接 LEFT OUTER JOIN 左边的employees员工表中的员工即使没有与
departments部门表中匹配的行，该查询也会取回员工表中所有的员工行。
select e.last_name,d.department_name from employees e LEFT OUTER JOIN departments d on e.department_id = d.department_id;

（5）右外连接（RIGTH OUTER JOIN）

语法：
SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件;


右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的行，该查询
也会取回 DEPARTMENTS 表中所有的行。

（6）右外连接示例：

示例：查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。
分析：注意点：我们要的是部门。
从员工表取雇员名字last_name，部门表取部门名称department_name，
两张表以部门id作为关联条件，这样取出的数据只是部门有员工的数据，
取出的部门表部门数据不全，因为部门表的部门还有没有员工的没有取出来，
所以用右连接 RIGHT OUTER JOIN 右边的departments部门表中的部门即使没有与
员工表中匹配的行，该查询也会取回部门表中所有的部门行。
select e.last_name,d.department_name from employees e RIGHT OUTER JOIN departments d on e.department_id=d.department_id;

（5）全外连接（FULL OUTER JOIN）

3.4、MySQL 中使用 union 实现全完连接

MySQL 中不支持 FULL OUTER JOIN 全外连接，可以使用 union 实现全完连接。

（1）UNION （all）的作用

组合两个或更多SELECT语句的结果集

（2）UNION （all）使用前提

1、UNION中的每个SELECT语句必须具有相同的列数
2、这些列也必须具有相似的数据类型
3、每个SELECT语句中的列也必须以相同的顺序排列

（3）UNION（all）的语法：

注意: UNION（ALL）结果集中的列名总是等于UNION中第一个SELECT语句中的列名。
语法：
(SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名 ON 连接条件)
UNION（all）
(SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件);

SELECT column name(s) FROM tablel
UNION（all）
SELECT column name(s) FROM table2;

（4）UNION

UNION：可以将两个查询结果集合并，返回的行都是唯一的，没有重复数据（去重排序），如同对整个结果集合使用了DISTINCT。

（5）UNION ALL

UNION ALL：只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。

（6）示例一：全外连接UNION

从mysql8.0的scott数据库emp表：查询工资等于1600或者等于3000或者等于1250的员工信息。
方法一：
SELECT * FROM EMP WHERE SAL =1600 OR SAL=1250 OR SAL=3000;
SELECT * FROM EMP WHERE SAL IN(1600 ,1250 ,3000);
SELECT * FROM EMP WHERE SAL =ANY(1600 ,1250 ,3000);

方法二：
SELECT * FROM EMP WHERE SAL=1600
UNION
SELECT * FROM EMP WHERE SAL=3000
UNION
SELECT * FROM EMP WHERE SAL=1250;

（6）示例二：全外连接UNION

示例1：查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇员。
(select e.last_name,d.department_name from employees e LEFT OUTER JOIN departments d on e.department_id = d.department_id)
UNION
(select e1.last_name,d1.department_name from employees e1 RIGHT OUTER JOIN
departments d1 on d1.department_id = e1.department_id)

sql145题：示例15、union all 的巧妙解法

#建表
create table union_all
(
requester_id int,
accepter_id int,
accept_date date
);

#插入数据
insert into union_all values (1,2,‘2016-06-03’);
insert into union_all values (1,3,‘2016-06-08’);
insert into union_all values (2,3,‘2016-06-08’);
insert into union_all values (3,4,‘2016-06-09’);

#查询表
select * from union_all;

示例15：在 微信 或者 QQ 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。 表 union_all 存 储 了 所 有 好 友 申 请 通 过 的 数 据 记 录 ， 其 中 ，requester_id 和 accepter_id 都是用户的编号。

需求：写一个查询语句，求出谁拥有最多的好友和他拥有的好友数目。对于上面的样例数据，结果为下表所示。
注意：保证拥有最多好友数目的只有 1 个人。好友申请只会被接受一次，所以不会有 requester_id 和 accepter_id 值都相同的重复记录。
解释：编号为 ‘3’ 的人是编号为 ‘1’，‘2’ 和 ‘4’ 的好友，所以他总共有 3 个好友，比其他人都多。

需求SQL：
select ids id,count() num
from
(
select requester_id ids from union_all
union all
select accepter_id ids from union_all
) s
group by ids
order by count() desc
limit 1;

sql145题：示例1、111111111111111111111111111

聚合函数（多行函数、分组函数）

为什么先学聚合函数再学group by分组查询？
聚合函数通常在 SELECT 语句中配合 GROUP BY 子句使用，用于对分组后的数据进行汇总分析。

1、聚合函数介绍

1.1、什么是聚合函数？

聚合函数也称之为多行函数，组函数或分组函数。聚合函数不象单行函数，聚合函数对行的分组进行操作，对每组给出一个结果。如果在查询中没有指定分组，那么聚合函数则将查询到的结果集视为一组。

1.2、聚合函数类型

MIN/MAX/COUNT 能够处理字符型 数值型 日期型数据
SUM/AVG 只能处理数值型数据

2、使用聚合函数的原则

（1）DISTINCT 使得函数只考虑不重复的值；
（2）所有聚合函数忽略空值。为了用一个值代替空值，用 IFNULL 或 COALESCE 函数。

3、AVG(arg)平均值函数

AVG(arg)平均值函数：对分组数据做平均值运算。计算指定列的数值平均值。
arg：参数类型只能是数字类型。

4、SUM(arg)求和函数

SUM(arg)求和函数：对分组数据求和。计算指定列的数值之和。
arg：参数类型只能是数字类型。

示例：
计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。
SELECT AVG(salary),SUM(salary) FROM employees WHERE job_id LIKE ‘%REP%’;

5、MIN(arg)最小值函数

MIN(arg)最小值函数：求分组中最小数据。找出指定列的最小值。
arg：参数类型可以是字符、数字、日期。

6、MAX(arg)最大值函数

MAX(arg)最大值函数：求分组中最大数据。找出指定列的最大值。
arg：参数类型可以是字符、数字、日期。

示例：查询员工表中入职时间最短与最长的员工，并显示他们的入职时间。hire_date字段是个日期类型
SELECT MIN(hire_date), MAX(hire_date) FROM employees;

7、COUNT( )计数函数

COUNT( )计数函数：返回分组中的总行数。计算指定列的行数或非空值的数量。
COUNT 函数有三种格式：真正在做业务需求COUNT( )计数的时候，要考虑是用*，还是具体的列，考虑有没有空值，考虑有没有重复的问题，这样才能满足一个业务需求的正常查询，否则你查到的数据未必是业务中所需要的数据

7.1、COUNT(*)

COUNT(*)：返回表中满足 SELECT 语句的所有列的行数，包括重复行，包括有空值列
的行。返回查询的记录总数，统计的表中全部的数据，扫描表中所有的列的行数。计算空值。

7.2、COUNT(列)

COUNT(expr)：返回在列中的由 expr 指定的非空值的数。也就是不计算空值列。

7.3、COUNT(DISTINCT 列)

使用 DISTINCT 关键字
COUNT(DISTINCT expr)：返回在列中的由 expr 指定的唯一的非空值的数。 (返回expr列非空并且值不相同的行数)

示例一：显示员工表中部门编号是80中有佣金的雇员人数。
SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

示例二：显示员工表中的部门数。
SELECT COUNT(DISTINCT department_id) FROM employees;

7.4、COUNT(1)

COUNT(1)只扫描当前的常量列。计算一共有多少符合条件的行，计算空值。

COUNT(*):统计表中全部的数据条数，计算空值。

COUNT(COLUMN列):统计表中该列的数据条数，不计算空值。

示例：查询emp表中的所有数据。
SELECT * FROM emp;
comm列，一共14条数据14行，10行此列为空值

示例：统计EMP表中COMM列数据的行数（COMM字段总共有多少条记录）。
SELECT MIN(COMM),MAX(COMM),SUM(COMM),AVG(COMM),COUNT(*),COUNT(COMM)
FROM EMP;

示例：统计EMP表中列数据的行数。
SELECT COUNT(*),COUNT(1),COUNT(COMM),COUNT(DISTINCT comm)FROM EMP;

8、组函数和 Null 值处理：IFNULL(参数列, 0)

在组函数中使用 IFNULL 函数

示例：计算所有员工的佣金平均值，包括那些没用佣金的员工。
SELECT AVG(IFNULL(commission_pct, 0)) FROM employees;

IFNULL(commission_pct, 0)：IFNULL函数，如果第一个参数commission_pct不为空返回commission_pct的值，如果第一个参数commission_pct为空返回第二个参数的值0。

sql145题：示例2、聚合函数的巧妙用法

#建表
create table sum_count_min_casewhen
(A varchar(20),B int,C varchar(20));
#插入数据
insert into sum_count_min_casewhen values (‘aaa’,1,‘X’);
insert into sum_count_min_casewhen values (‘aaa’,2,‘Y’);
insert into sum_count_min_casewhen values (‘bbb’,3,‘X’);
insert into sum_count_min_casewhen values (‘bbb’,4,‘X’);
insert into sum_count_min_casewhen values (‘ccc’,5,‘Y’);
insert into sum_count_min_casewhen values (‘ccc’,6,‘Y’);

需求：有如下 ABC 三列和几组数据

想得到如下结果：也就是ABC三列，根据A列分组、B列求每列分组后的和、C列如果没有重复数据返回1求

该如何写查询？

分组查询（GROUP BY）

1、创建数据分组

在没有进行数据分组之前，所有聚合函数是将结果集作为一个大的信息组进行处理。但是，有时，则需要将表的信息划分为较小的组，可以用 GROUP BY 子句实现。

2、单列数据分组 GROUP BY 子句语法

原则：
（1）使用 WHERE 子句，可以在划分行成组以前过滤行。
（2）如果有WHERE子句，那么GROUP BY 子句必须在WHERE的子句后面。
（3）在 GROUP BY 子句中必须包含列。

使用 GROUP BY 子句条件：
在 SELECT 列表中的不在组函数中的所有列必须在GROUP BY 子句中

下面是包含一个 GROUP BY 子句 SELECT 语句的求值过程：
（1）SELECT 子句指定要返回的列：
（2）在 EMPLOYEES 表中的部门号
− GROUP BY 子句中指定分组的所有薪水的平均值
− FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
（3）WHERE 子句指定被返回的行。因为无 WHERE 子句默认情况下所有行被返回。
（4）GROUP BY 子句指定行怎样被分组。行用部门号分组，所以 AVG 函数被应用于薪水列，以计算每个部门的平均薪水。

示例：
计算每个部门的员工总数。
SELECT DEPARTMENT_ID, COUNT(*) FROM employees GROUP BY DEPARTMENT_ID;

3、多列数据分组 GROUP BY 子句语法

在 SELECT 列表中的不在组函数中的所有列必须在GROUP BY 子句中

在分组列中继续分组：
可以列出多个 GROUP BY 列返回组和子组的摘要结果。可以用GROUP BY子句中的列的顺序确定结果的默认排序顺序。下面是图片中的 SELECT 语句中包含一个 GROUP BY 子句时的求值过程：
（1）SELECT 子句指定被返回的列：
− 部门号在 EMPLOYEES 表中
− Job ID 在 EMPLOYEES 表中
− 在 GROUP BY 子句中指定的组中所有薪水的合计
（2）FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
（3）GROUP BY 子句指定你怎样分组行：
− 首先，用部门号分组行。
− 第二，在部门号的分组中再用 job ID 分组行。
（4）如此 SUM 函数被用于每个部门号分组中的所有 job ID 的 salary列。

示例：
计算每个部门的不同工作岗位的员工总数。
在 SELECT 列表中的不在组函数中的所有列必须在GROUP BY 子句中
SELECT e.DEPARTMENT_ID, e.JOB_ID,COUNT(*)
FROM employees e
GROUP BY e.DEPARTMENT_ID,e.JOB_ID;

分组聚合（约束分组结果 HAVING）

在 SQL 中，HAVING 子句用于在分组聚合后对分组进行过滤。它允许我们对分组后的结果进行条件筛选，只保留满足特定条件的分组。

HAVING 子句与条件查询 WHERE 子句的区别在于：
（1）WHERE 子句用于在分组之前约束选择的行进行过滤，
（2）而 HAVING 子句用于在 分组之后 进行过滤。

1、HAVING 子句：约束分组结果

HAVING 子句是对查询出结果集分组后的结果进行过滤。

约束分组结果：
用 WHERE 子句约束选择的行，用 HAVING 子句约束组。为了找到每个部门中的最高薪水，而且只显示最高薪水大于 $10,000 的那些部门，可以象下面这样做：
（1）用部门号分组，在每个部门中找最大薪水。
（2）返回那些有最高薪水大于 $10,000 的雇员的部门。
SELECT department_id, MAX(salary) FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;

2、HAVING 子句语法

用 HAVING 子句约束分组：
（1）行被分组
（2）应用组函数

示例：
显示那些合计薪水超过 13,000 的每个工作岗位的合计薪水。排除那些JOB_ID中含有REP的工作岗位，并且用合计月薪排序列表。
SELECT job_id, SUM(salary) PAYROLL FROM employees
WHERE job_id NOT LIKE ‘%REP%’
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);

子查询

1、子查询介绍

子查询是指在一个查询语句内部 嵌套 另一个完整的查询语句，内层查询被称为子查询。子查询可以用于获取更复杂的查询结果或者用于过滤数据。

当执行包含子查询的查询语句时，数据库引擎会首先执行子查询，然后将其结果作为条件或数据源来执行外层查询。

打个比方，子查询就像是在一个盒子中的盒子，外层查询是大盒子，内层查询是小盒子。执行查询时，我们首先打开小盒子获取结果，然后将小盒子的结果放到大盒子中继续处理。

用子查询解决问题：
假如要写一个查询来找出挣钱比 Abel 的薪水还多的人。为了解决这个问题，需要两个查询：一个找出 Abel 的收入，第二个查询找出收入高于 Abel 的人。可以用组合两个查询的方法解决这个问题。内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。

2、子查询语法

（1）子查询 (内查询) 在主查询之前执行一次。
（2）子查询的结果被用于主查询 (外查询)。

3、子查询使用

子查询是一个 SELECT 语句，它是嵌在另一个 SELECT 语句中的子句。使用子查询可以用简单的语句构建功能强大的语句。

可以将子查询放在许多的 SQL 子句中，包括：
（1）WHERE 子句
（2）HAVING 子句
（3）FROM 子句

4、使用子查询的原则

（1）子查询放在圆括号中。
（2）将子查询放在比较条件的右边。
（3）在单行子查询中用单行运算符，在多行子查询中用多行运算符。

5、子查询类型：单行子查询、多行子查询

（1）单行子查询
（2）多行子查询

示例：
查询与Fox同一部门的同事，并显示他们的名字与部门ID。
select e.LAST_NAME,e.DEPARTMENT_ID
FROM employees e
where e.DEPARTMENT_ID =
(select e1.DEPARTMENT_ID from employees e1
where e1.last_name = ‘Fox’);

6、单行子查询

单行子查询：单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。
（1）仅返回一行
（2）使用单行比较符

示例：
查询 Fox的同事，但是不包含他自己。
select empl.last_name from employees empl
where empl.department_id =
(select e.department_id from employees e
where e.last_name = ‘Fox’)
and empl.last_name <> ‘Fox’;

7、多行子查询

7.1、多行子查询使用

多行子查询：子查询返回多行被称为多行子查询。对多行子查询要使用多行运算符而不是单行运
算符。
（1）返回多于一行
（2）使用多行比较符

7.2、使用ANY运算符

使用ANY运算符：ANY 运算符比较一个值与一个子查询返回的每一个值。
< ANY 意思是小于最大值。
> ANY 意思是大于最小值。
= ANY 等同于 IN。

7.3、使用ALL运算符

使用ALL运算符：ALL 运算符比较一个值与子查询返回的每个值。
< ALL 意思是小于最小值。
> ALL 意思是大于最大值，
NOT 运算符可以与 IN运算符一起使用。

7.4、子查询中的空值

子查询中的空值：内查询返回的值含有空值，并因此整个查询无返回行，原因是用大于、小于或不等于比较Null值，都返回null。所以，只要空值可能是子查询结果集的一部分，就不能用 NOT IN 运算符。NOT IN 运算符相当于 <> ALL。
注意，空值作为一个子查询结果集的一部分，如果使用 IN 操作符的话，不是一个问题。IN 操作符相当于 =ANY。

示例：多行子查询中的 IN
SELECT emp.last_name FROM employees emp
WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr);

示例:
查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID。
select em.last_name,em.salary,em.department_id from employees em
where em.salary in(select min(e.salary) from employees e group by e.department_id);

子查询 exists、not exists

1、子查询 exists、not exists

1.1、exists：

子查询是一种强大的查询工具，它可以嵌套在主查询中，帮助我们进行更复杂的条件过滤和数据检索。
其中，子查询中的一种特殊类型是 “exists” 子查询，用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。exists用于WHERE后，当其后子查询语句有数据返回，就执行主查询语句，无结果返回，就不执行主查询语句(返回结果为空)

1.2、not exists：

和 exists 相对的是 not exists，用于查找不满足存在条件的记录。

1.3、不相关子查询：

子查询语句的执行不受主查询语句的影响，子查询语句能够独立运行。

1.4、相关子查询：

子查询语句的执行受主查询语句的影响，子查询语句不能独立运行。

2、子查询 exists 语法：

SELECT COLUMN1,COLUMN2… FROM TABLE_NAME WHERE EXISTS (SELECT 语句);
SELECT COLUMN1,COLUMN2… FROM TABLE_NAME WHERE NOT EXISTS (SELECT 语句);

3、子查询 exists 示例：

示例一：如果emp表能够查询出部门为20的员工编号，就继续从emp表中查询员工编号，姓名，薪资。
SELECT EMPNO, ENAME, SAL FROM EMP
WHERE EXISTS (SELECT EMPNO FROM EMP WHERE DEPTNO = 20);–(章程执行)正常执行

示例二：如果emp表不能够查询出部门为20的员工编号，就不要从emp表中查询出员工编号，姓名，薪资，让查询数据为空。
SELECT EMPNO, ENAME, SAL FROM EMP
WHERE EXISTS (SELECT EMPNO FROM EMP WHERE DEPTNO = 40); --返回为空

示例三：EXISTS作为相关子查询的使用：查询smith所在部门的员工信息。
SELECT * FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME = ‘SMITH’);

SELECT * FROM EMP E WHERE EXISTS
(SELECT DEPTNO FROM EMP WHERE ENAME = ‘SMITH’ AND E.DEPTNO = DEPTNO);

示例四：查询工资大于部门内平均薪资的员工信息。
SELECT * FROM EMP E WHERE
EXISTS
(SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING E.DEPTNO=DEPTNO AND E.SAL>AVG(SAL));

示例五：查询在纽约工作的员工的编号，姓名，以及职位。
SELECT EMPNO,ENAME,JOB FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘NEW YORK’);

SELECT EMPNO,ENAME,JOB FROM EMP
WHERE EXISTS (SELECT DEPTNO FROM DEPT WHERE LOC = ‘NEW YORK’ AND EMP.DEPTNO=DEPTNO);

4、使用子查询 exists 优点：查询数据量比较大时，相关子查询效率比较高

查询步骤：

1. 要明确比较的列/联系外表和子查询内表的列
2. 按照结构把题目中描述的大体用代码写出：主查询语句、子查询语句
3. 把比较的列引入子查询中（在子查询条件后直接加AND/OR让内外两表的关系列=/>/<）
4. 将两个条件联系以后，根据EXISTS独有的规则判断该条数据符不符合子查询的条件，有无结果返回，决定该条数据是否返回到结果集中。

重点：
5. 明确在哪个位置添加两个表的关联条件！！！！！！
6. 非相关子查询可以用在多个位置，相关子查询一般用在WHERE或HAVING子句中作为条件，两者可以发生转换。

exists优点：
7. IN和EXISTS互换的方式
8. 相关子查询之所以存在，是因为在很多情况下使用相关子查询效率比较高！！！
9. 当子查询中的数据量比较大时，用EXISTS，当子查询中的数据量比较小时用 IN
10. 在哪些位置可以添加哪中类型的子查询语句
11. 掌握两个查询步骤：子查询步骤 相关子查询步骤

开窗函数（窗口函数、分析函数、OLAP(Online Anallytical Processing 联机分析处理)函数）

1、开窗函数

开窗函数：为将要被操作的行的集合定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

在 SQL 中，开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息。
开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。
打个比方，可以将开窗函数想象成一种 “透视镜”，它能够将我们聚焦在某个特定的分组，同时还能看到整体的全景。

开窗函数又称OLAP函数（Online Analytical Processing），MySql在8.0的版本(2016年)增加了对开窗函数的支持。（mysql8.0之后才有开窗函数，先下载8.0版本的mysql）

注：在Oracle中称为分析函数。在MySQL中称为开窗函数。

2、开窗函数与聚合函数的区别

聚合函数：将数据按照一定的规则分组，统一分析各组的某项情况，每个分组返回一行结果。将多条记录聚合为一条。

开窗函数：将数据按照一定的规则分组，统一分析各组的某项情况，每行数据返回一行结果。有几条记录执行完还是几条。

3、开窗函数语法结构：

3.1、不加范围限制

函数名() OVER (PARTITION BY 分组列 ORDER BY 排序列 )
F_NAME() OVER (PARTITION BY COL1,COL2… ORDER BY COL1,COL2… )

3.2、加范围限制

函数名() OVER (PARTITION BY 分组列 ORDER BY 排序列 分析范围 )
F_NAME() OVER (PARTITION BY COL1,COL2… ORDER BY COL1,COL2… 分析范围 )

3.3、语法分析：

F_NAME：指明函数的功能，函数是 求和、求平均值、求最大最小值…
OVER：窗口函数的标志
PARTITION BY COL1: 按照 COL1 列分组
ORDER BY COL1,COL2：按照COL1 COL2列排序

1. 示例：聚合函数GROUP BY分组
   示例：查询员工表中各部门的薪资和，以部门编号的降序排列。
   使用SUM( )聚合函数GROUP BY分组，每个分组返回一行结果。
   SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO DESC;
   
2. 示例：开窗函数sum( ) over
   示例：查询员工表中各部门的薪资和，以部门编号的降序排列。
   使用开窗函数sum( ) over，PARTITION BY分组，每行数据返回一行结果。
   SELECT DEPTNO, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO DESC) as sum_over FROM EMP;
   

4、开窗函数分类

4.1、聚合开窗函数

（sum,avg,count,max,min）聚合函数( )+over() ，聚合之后返回多行。
注意：聚合开窗函数能使用PARTITION BY子句分组，也能使用ORDER BY子句分组排序。

（1）sum+over( )

语法：
SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序列字段名) + 别名

示例：查询emp表中部门编号deptno，员工编号empno，员工薪资sal，按部门编号分组统计每个部门薪资和的累加值(部门薪资和累加值别名为sum_over)，按员工薪资的降序排序。
SELECT DEPTNO,EMPNO,SAL,SUM(SAL) OVER(PARTITION BY DEPTNO order by SAL DESC) AS sum_over FROM EMP;

sql145题：示例10、sum 开窗函数

（2）avg+over( )

（3）count+over( )

（4）max+over( )

（5）min+over( )

4.2、专用开窗函数（按功能划分）：序号函数/排名函数

（1）ROW_NUMBER()：排序：1，2，3

（2）RANK()：排序：1，1，3

（3）DENSE_RANK()：排序：1，1，2

sql145题：示例39、排序开窗函数 rank 和 dense_rank

(1)、按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺。
(2)、按各科成绩进行排序，并显示排名，Score 重复时合并名次。
(3)、查询学生的总成绩，并进行排名，总分重复时保留名次空缺。
(4)、查询学生的总成绩，并进行排名，总分重复时不保留名次空缺。

#创建Student学生表
create table Student
(
stuid varchar(10),
sname varchar(20),
sage date,
ssex varchar(10)
)
#插入数据
insert into Student values(‘01’,‘赵雷’,‘1990-01-01’,‘男’);
insert into Student values(‘02’,‘钱电’,‘1990-12-21’,‘男’);
insert into Student values(‘03’,‘孙风’,‘1990-05-20’,‘男’);
insert into Student values(‘04’,‘李云’,‘1990-08-06’,‘男’);
insert into Student values(‘05’,‘周梅’,‘1991-12-01’,‘女’);
insert into Student values(‘06’,‘吴兰’,‘1992-03-01’,‘女’);
insert into Student values(‘07’,‘郑竹’,‘1989-07-01’,‘女’);
insert into Student values(‘08’,‘王菊’,‘1990-01-20’,‘女’);
#查询学生表
select * from student;

#创建Course课程表
create table Course
(
cid varchar(10),
cname varchar(20),
tid varchar(10)
)
#插入数据
insert into Course values(‘01’,‘语文’,‘02’);
insert into Course values(‘02’,‘数学’,‘01’);
insert into Course values(‘03’,‘英语’,‘03’);
#查询课程表
select * from course;

#创建Teacher教师表
create table Teacher
(
tid varchar(10),
tname varchar(20)
)
#插入数据
insert into Teacher values(‘01’,‘张三’);
insert into Teacher values(‘02’,‘李四’);
insert into Teacher values(‘03’,‘王五’);
#查询教师表
select * from teacher;

#创建SC学生分数表
create table SC
(
stuid varchar(10),
cid varchar(10),
score int
)
#插入数据
insert into SC values(‘01’,‘01’,80);
insert into SC values(‘01’,‘02’,90);
insert into SC values(‘01’,‘03’,99);
insert into SC values(‘02’,‘01’,70);
insert into SC values(‘02’,‘02’,60);
insert into SC values(‘02’,‘03’,80);
insert into SC values(‘03’,‘01’,80);
insert into SC values(‘03’,‘02’,80);
insert into SC values(‘03’,‘03’,80);
insert into SC values(‘04’,‘01’,50);
insert into SC values(‘04’,‘02’,30);
insert into SC values(‘04’,‘03’,20);
insert into SC values(‘05’,‘01’,76);
insert into SC values(‘05’,‘02’,87);
insert into SC values(‘06’,‘01’,31);
insert into SC values(‘06’,‘03’,34);
insert into SC values(‘07’,‘02’,89);
insert into SC values(‘07’,‘03’,98);
#查询学生分数表
select * from SC;

需求SQL
(1)、按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺。
select *,
RANK()over(order by score desc) 排名
from SC;

(2)、按各科成绩进行排序，并显示排名，Score 重复时合并名次。
select *,
DENSE_RANK()over(order by score desc) 排名
from SC;

(3)、查询学生的总成绩，并进行排名，总分重复时保留名次空缺。
分析：先根据每个stuid学生id，对分数SUM(score)求和别名总成绩降序，查出每个学生总成绩，以学生id分组。查出的每个stuid学生id，总成绩作为一张表A，并对A表进行rank排名。
select *,
RANK()over(order by 总成绩 desc) 排名
from(
select stuid,SUM(score) 总成绩
from SC group by stuid
) A;

(4)、查询学生的总成绩，并进行排名，总分重复时不保留名次空缺。
分析：先根据每个stuid学生id，对分数SUM(score)求和别名总成绩降序，查出每个学生总成绩，以学生id分组。查出的每个stuid学生id，总成绩作为一张表A，并进行dense_rank排名。
select *,
dense_rank()over(order by 总成绩 desc) 排名
from(
select stuid,SUM(score) 总成绩
from SC group by stuid
) A;

sql145题：示例40、 前 N 名问题

应用示例39的表结构和数据。
需求：查询各科成绩前三名的记录（要求至少想出两种解法）预计结果如下：

#解法一：使用排序函数：查询各科成绩前三名的记录。
分析：需求是查询各科成绩，用rank函数以分数表的cid课程id分组，以score分数降序，rank排序别名A，查出的数据作为一个子查询表B，从子查询B中表中取出rank排序A列小于等于3的数据。
#需求SQL
select * from
(
select ,
rank()over (partition by cid order by score desc) A
from SC
) B
where B.A<=3;

#解法二：使用表关联查询进行匹配：查询各科成绩前三名的记录。
分析：SC分数表，一张表当作两张表来用，两张表进行关联，第一张表别名a左连接第二张表别名b，关联条件为a表课程cid等于b表课程cid，a表分数小于b表分数，此时计算的是每门课程学生id小于3的记录数，以学生id，课程id，分数进行分组，课程id，分数进行降序。
#需求SQL
select a.stuid,a.cid,a.score
from SC a
left join SC b on a.cid=b.cid and a.score<b.score
group by a.stuid,a.cid,a.score
having COUNT(b.stuid)< 3
order by a.cid,a.score desc;

#解法三：使用排序函数：查询各科成绩前三名的记录。其实也是关联查询，只是形式不一样。
分析：SC分数表，一张表当作两张表来用，第一张表作为主查询表别名表a，where条件之后查询的是第二张SC分数表有多少数据记录条数，数据记录条数的取数条件为课程cid等于主表课程cid，分数大于主表分数。数据记录条数小于3。
#需求SQL
select * from SC a
where (select COUNT() from SC where cid=a.cid and score>a.score)< 3
order by a.cid,a.score desc;

sql145题：示例14、RANK() OVER(ORDER BY (COUNT(order_id))DESC) AS 排名。

表 rank_customer 定义如下：order_id(订单编号)，customer_id(客户编号)，order_date(下单日期) 有如下几条记录：

需求：在表 rank_customer 中找到订单数最多客户对应的 customer_id。预计的输出结果：

#建表：
CREATE TABLE rank_customer ( order_id INT, customer_id INT, order_date date );
#插入数据：
insert into rank_customer values (1,1,‘2019-06-24’);
insert into rank_customer values (2,2,‘2019-04-23’);
insert into rank_customer values (3,3,‘2019-03-21’);
insert into rank_customer values (4,3,‘2019-04-29’);
insert into rank_customer values (5,4,‘2019-08-12’);
insert into rank_customer values (6,4,‘2019-09-14’);

方法一：RANK() OVER ( ORDER BY ( COUNT( order_id)) DESC )
#分析：根据每笔订单编号用count函数计算出订单数，这个订单数用rank开窗函数排序并且DESC降序排列起别名订单数排序，这个订单数是以客户编号分组，因为每笔订单编号对应的客户编号是唯一的且客户编号是重复的，查询出的数据作为一张子查询排序表t。我们只要排序表中订单排序列为排名第一的客户编号。
SELECT customer_id FROM
(SELECT customer_id,COUNT( order_id ) AS 订单数,RANK() OVER (ORDER BY ( COUNT( order_id )) DESC ) AS 订单数排名 FROM rank_customer GROUP BY customer_id) t
WHERE t.订单数排名 =1;

方法二：GROUP BY customer_id HAVING COUNT (customer_id)
#分析：
SELECT customer_id
FROM rank_customer
GROUP BY customer_id
HAVING COUNT (customer_id) = (
SELECT TOP 1 COUNT (customer_id)
FROM rank_customer
GROUP BY customer_id
ORDER BY COUNT (customer_id) DESC)

sql145题：示例1、DENSE_RANK 开窗函数–MySQL 8.0和SQL Server

需求：编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

#创建表
CREATE TABLE dense_rank_1 ( id INT, Score INT );
#插入数据
insert into dense_rank_1 values (1,80);
insert into dense_rank_1 values (2,75);
insert into dense_rank_1 values (3,89);
insert into dense_rank_1 values (4,80);
insert into dense_rank_1 values (5,71);
#查询dense_rank_1表数据
SELECT * from dense_rank_1;

#需求SQL
SELECT
ID,
Score,
DENSE_RANK() over ( ORDER BY score DESC ) RK
FROM
dense_rank_1;

4.3、专用开窗函数（按功能划分）：分布函数

（1）PERCENT_RANK()：(rank-1)/(rows-1)。

（2）CUME_DIST()：<=当前rank值的函数/总函数。

4.4、专用开窗函数（按功能划分）：前后函数

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

LEAD(expr,n)：返回当前行的后n行的expr值。

4.5、专用开窗函数（按功能划分）：头尾函数

FIRST_VALUE(expr)：返回第一个expr值。

LAST_VALUE(expr)：返回最后个expr值。

4.6、专用开窗函数（按功能划分）：其他函数

NTH_VALU(expr,n)：返回第n个expr值。

NTILE(n)：将有序数据分为n个桶，记录等级数。返回每一行在第几桶。

MySQL中的索引

1、索引介绍

索引是对数据库表中的一列或多列值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。索引是一种特殊的文件，它们包含着对数据表里所有记录的位置信息。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。MySQL索引的建立对于MySQL 的高效运行是很重要的，索引可以大大提高MySQL 的检索速度。

1.1、索引的作用（优点、缺点）

索引相当于图书上的目录，可以根据目录上的页码快速找到所需的内容，提高性能（查询速度）。

（1）索引优点：
1 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性；
2 可以加快数据的检索速度；
3 可以加速表与表之间的连接；
4 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间；

（2）索引缺点：
1 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
2 索引需要占用物理空间，数据量越大，占用空间越大；
3 会降低表的增删改的效率，因为每次增删改索引都需要进行动态维护；

1.2、什么时候需要创建索引

1 频繁作为查询条件的字段应该创建索引；
2 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）；
3 查询中统计或者分组的字段；

1.3、什么时候不需要创建索引

1 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件；
2 where条件里用不到的字段，不创建索引；
3 表记录太少，不需要创建索引；
4 经常增删改的表；
5 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的果，例如性别字段，只有男女，不适合建立索引；

1.4、MySQL中的索引类型

• 普通索引：
  最基本的索引，它没有任何限制。
• 唯一索引：
  索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。
• 主键索引：
  特殊的索引，唯一的标识一条记录，不能为空，一般用primarykey来约束。
• 联合索引：
  在多个字段上建立索引，能够加速查询到速度。

2、普通索引

是最基本的索引，它没有任何限制。在创建索引时，可以指定索引长度。length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定length。

创建索引时需要注意：
如果指定单列索引长度，length 必须小于这个字段所允许的最大字符个数。

2.1、查询索引

查询索引：
SHOW INDEX FROM table_name;

2.2、直接创建索引

直接创建索引：
CREATE INDEX index_name ON table(column(length));
示例：
为 emp3 表中的 name 创建一个索引，索引名为 emp3_name_index；
create index emp3_name_index ON emp3(name);

2.3、修改表添加索引

修改表添加索引：
ALTER TABLE table_name ADD INDEX index_name (column(length));
示例：
修改 emp3 表，为 addrees 列添加索引，索引名 emp3_address_index；
alter table emp3 add index emp3_address_index(address);

2.4、创建表时指定索引列

创建表时指定索引列：
CREATE TABLE `table` (
COLUMN TYPE ,
PRIMARY KEY (`id`),
INDEX index_name (column(length))
);
示例：
创建 emp4 表，包含 emp_id,name,address 列， 同时为 name 列
创建索引 ，索引名为 emp4_name_index。
create table emp4(emp_id int primary key auto_increment,
name varchar(30),address varchar(50),
index emp4_name_index(name));

2.5、删除索引

删除索引：
DROP INDEX indexname ON tablename;
示例：
删除 mep3 表中索引名为 emp3_address_index 的索引。
drop index emp3_address_index on emp3;

3、唯一索引

唯一索引与普通索引类似，不同的就是： 索引列的值必须唯一，但允许有空值。

3.1、创建唯一索引

创建唯一索引：
CREATE UNIQUE INDEX indexName ON table(column(length));
示例：
为 emp 表中的 name 创建一个唯一索引，索引名为 emp_name_index。
create unique index emp_name_index on emp(name);

3.2、修改表添加唯一索引

修改表添加唯一索引：
ALTER TABLE table_name ADD UNIQUE indexName (column(length));
示例：
修改 emp 表，为 salary 列添加唯一索引，索引名为 emp_salary_index。
alter table emp add unique emp_salary_index(salary);

3.3、创建表时指定唯一索引

创建表时指定唯一索引：
CREATE TABLE `table` (
COLUMN TYPE ,
PRIMARY KEY (`id`),
UNIQUE index_name (column(length))
);
示例：
创建 emp5 表，包含 emp_id,name,address 列，同时为 name 列 创建唯一索引。索引名为 emp5_name_index。
create table emp5(emp_id int primary key,name varchar(30),address varchar(30),unique emp5_name_index(name));

4、主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

4.1、修改表添加主键索引

修改表添加主键索引：
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
示例：
修改 emp 表为 employee_id 添加主键索引。
alter table emp add primary key(employee_id);

4.2、创建表时指定主键索引

创建表时指定主键索引：
CREATE TABLE `table` (
COLUMN TYPE ,
PRIMARY KEY(column)
);
示例：
创建 emp6 表，包含 emp_id,name,address 列，同时为 emp_id 列创建主键索引。
create table emp6(employee_id int primary key auto_increment,
name varchar(20),address varchar(50));

5、组合索引

组合索引是指使用多个字段创建的索引，想要组合索引生效，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用(最左前缀原则)。

最左前缀原则
就是最左优先。
如： 我们使用表中的 name ，address ，salary 创建组合索引，那么想要组合索引生效， 我们只能使用如下组合：
name/address/salary
name/address
name/
如果使用 addrees/salary 或者是 salary 则索引不会生效。

5.1、添加组合索引

添加组合索引：
ALTER TABLE table_name ADD INDEX index_name (column(length),column(length));
示例：
修改 emp6 表，为 name ，address 列创建组合索引。
alter table emp6 add index emp6_index_n_a(name,address);

5.2、创建表时创建组合索引

创建表时创建组合索引：
CREATE TABLE `table` (
COLUMN TYPE ,
INDEX index_name
(column(length),column(length))
);
示例：
创建 emp7 表，包含 emp_id,name,address 列，同时为 name,address 列创建组合索引。
create table emp7(emp_id int primary key auto_increment,
name varchar(20),address varchar(30),
index emp7_index_n_a(name,address));

MySQL中的事务

1、事务简介

事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

• 事务定义(Transaction)：
• • 事务是一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
• • 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

2、事务四大特征(ACID)

• 原子性(ATOMICITY)
  事务中的操作要么都不做，要么就全做。
• 一致性(CONSISTENCY)
  一个事务应该保护所有定义在数据上的不变的属性(例如完整性
  约束)。在完成了一个成功的事务时，数据应处于一致的状态。
• 隔离性(ISOLATION)
  一个事务的执行不能被其他事务干扰。
• 持久性(DURABILITY)
  一个事务一旦提交，它对数据库中数据的改变就应该是永久性
  的。

3、事务类型

• 显式事务
  需要我们手动的提交或回滚。
  DML 语言中的所有操作都是显示事务操作。
• 隐式事务
  数据库自动提交不需要我们做任何处理，同时也不具备回滚性。
  DDL、DCL 语言都是隐式事务操作

4、使用事务

TCL语句	描述
start transaction	事务开启
commit	事物提交
rollback	事物回滚

示例一：创建account账户表，包含id、卡号、用户名、余额。

创建account账户表，包含id、卡号、用户名、余额。
create table account(
id int primary key auto_increment,
cardnum varchar(20) not null,
username varchar(30) not null,
balance double(10,2)
);

示例二：向account表中插入两条数据。

向account表中插入两条数据。
insert into account(cardnum,username,balance)
VALUES(‘123456789’,‘张三’,2000);
insert into account(cardnum,username,balance)
VALUES(‘987654321’,‘李四’,2000);

示例三：在一个事务中完成转账业务。

在一个事务中完成转账业务。
START TRANSACTION
update account set balance = balance-200
where cardnum = ‘123456789’;
update account set balance = balance+200
where cardnum = ‘987654321’;
select * from account;
– 当我们关闭数据库重新打开后，张三和李四的账户余额并没发生任何变化。
– 这是因为当我们使用“START TRANSACTION”开启一个事务后，该事务的提交方式不再是自动的，
– 而是需要手动提交，而在这里，我们并没有使用事务提交语句COMMIT，
– 所以对account表中数据的修改并没有永久的保存到数据库中，也就是说我们的转账事务并没有执行成功
– 提交转账事务
commit;
– 事务的回滚让数据库恢复到了执行事务操作前的状态。
– 需要注意的是事务的回滚必须在事务提交之前，因为事务一旦提交就不能再进行回滚操作。
rollback;

5、事务的并发问题

脏读（读取未提交数据）

指一个事务读取了另外一个事务未提交的数据。
A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。

时间顺序	转账事务	取款事务
1		开始事务
2	开始事务
3		查询账户余额为2000元
4		取款1000元，余额被更改为1000元
5	查询账户余额为1000元(产生脏读)
6		取款操作发生未知错误，事务回滚，余额变更为2000元
7	转入2000元，余额被更改为3000元(脏读的1000+2000)
8	提交事务
备注：按照正确逻辑，此时账户余额应该为4000元

不可重复读（前后多次读取，数据内容不一致）

在一个事务内读取表中的某一行数据，多次读取结果不同。
事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

时间顺序	事务A	事务B
1		开始事务
2	第一次查询，小明的年龄为20岁
3		开始事务
4	其他操作
5		更改小明的年龄为30岁
6		提交事务
7	第二次查询，小明的年龄为30岁
备注：按照正确逻辑，事务A前后两次读取到的数据应该一致

幻读（前后多次读取，数据总量不一致）

是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。

时间顺序	事务A	事务B
1	开始事务
2	第一次查询，数据总量为100条
3		开始事务
4	其他操作
5		新增100条数据
6		提交事务
7	第二次查询，数据总量为200条
备注：按照正确逻辑，事务A前后两次读取到的数据总量应该一致

6、事务的隔离级别

事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。

• 事务的隔离级别从低到高依次为：
• • READ UNCOMMITTED
• • READ COMMITTED
• • REPEATABLE READ
• • SERIALIZABLE

隔离级别越低，越能支持高并发的数据库操作。

隔离级别/读异常	脏读	不可重复读	幻读
SERIALIZABLE	Y	Y	Y
REPEATABLE READ	Y	Y	N
READ COMMITTED	Y	N	N
READ UNCOMMITTED	N	N	N
提示：Y表示解决N表示未解决

查看MySQL默认事务隔离级别

SELECT @@transaction_isolation;

设置事务隔离级别

对当前session有效。
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

MySQL的用户、权限、远程登录、密码

MySQL 是一个多用户的数据库系统，按权限，用户可以分为两种：
（1）root 用户，超级管理员。
（2）由 root 用户创建的普通用户。

1、用户管理

1.1、创建用户：

语法：
CREATE USER username IDENTIFIED BY ‘password’;
CREATE USER 用户名 IDENTIFIED BY ‘密码’;

1.2、查看用户：

语法：
SELECT USER,HOST FROM mysql.user;
安装mysql的mysql库的user表里面查看所有的用户。user列是用户名，host列是用户权限。root拥护包含所有权限。

1.3、创建用户示例：

示例：创建一个 rfr 的用户，密码是@QqWw123rfr。
CREATE USER rfr IDENTIFIED BY ‘@QqWw123rfr’;
查看mysql用户表是否创建成功。
select user,host from mysql.user;
（创建新用户如果没有指定权限，默认权限host是%，%代表这个用户可以在任何ip地址的设备中连接MYSQL服务端）

2、权限管理

新用户创建完后是无法登陆的，需要分配权限。

2.1、创建用户并授予用户权限：

想让创建的用户有什么权限，就授予什么权限。
语法：
GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIED BY “密码”;
GRANT ALL PRIVILEGES ON . TO ‘username’@‘localhost’ IDENTIFIED BY ‘password’

2.2、登陆主机：

2.3、权限列表：

2.4、刷新权限：

每当调整权限后，通常需要执行以下语句刷新权限。
FLUSH PRIVILEGES;

2.5、授予用户权限示例：

示例一(单个权限)：为 rfr 用户分配只能查询 scott 库中的 emp 表，并且只能在本机登陆的权限。

grant select ON scott.emp to ‘rfr’@‘localhost’ IDENTIFIED by ‘@QqWw123rfr’;
(此语句既创建了rfr用户，又分配了scott库emp表的查询权限)

超出权限就会报错：
navicat新建连接，输入主机localhost，用户名rfr，密码@QqWw123rfr。测试连接成功。

示例二(多个权限)：为 rfr 用户分配增删改查 所有库中的 所有表的权限，并且能在所有ip设备远程登录的权限。

grant insert,delete,update,select ON . to ‘rfr’@‘%’ IDENTIFIED by ‘@QqWw123rfr’;
(此语句既创建了rfr用户，又分配了rfr用户的权限)

查看rf用户所拥有的权限：
select * from mysql.user;

2.6、删除用户：

语法：
DROP USER username@localhost;

示例：删除能在所有ip设备远程登录的rfr用户。

drop user ‘rfr’@‘%’;
FLUSH PRIVILEGES;
查看用户权限表：
select user,host from mysql.user;

此时，创建的那个能在所有ip设备远程登录的rfr用户已经被删除了。

3、MySQL设置允许远程登录（本机windows的navicat连接登录linux系统下的mysql）

3.1、连接不上

此时我们用本机windows的navicat连接登录linux系统下的mysql，发现是连接不上的。

linux系统下 ip addr 查看linux下的ip地址：192.168.164.128，windows系统ping下linux系统，看是否能ping通？？？

windows系统ping下linux系统，能ping通说明网络没问题ip地址是通的。

3.2、为什么连不上？

因为mysql默认不支持远程登录，所以需要设置开启mysql远程登录。远程登录方式在mysql数据库的user表中体现。

3.3、如何开启mysql的远程连接？？？

步骤：
（1）登录数据库：mysql -uroot -p

（2）切换到mysql数据库：use mysql

（3）查看mysql数据库的user表中的主机和用户对应情况信息：
select user,host from user;

（4）针对远程用户授权root给所有ip：
GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ IDENTIFIED BY ‘rootlinux’ WITH GRANT OPTION;

FLUSH PRIVILEGES;

针对远程用户授权root给某一个ip(你想让哪个ip能远程连接linux)：GRANT ALL PRIVILEGES ON . TO ‘root’@‘your ip’ IDENTIFIED BY ‘youpassword’ WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;

补充：如果开启远程报错
报错：1045-access denied for user ‘root’@‘localhost’以下命令也可以开启远程
#删除之前配置
drop user ‘root’@’%‘;
#使用root能在任何host访问
update user set user.Host=’%‘where user.User=‘root’;
#配置远程登录
CREATE USER ‘root’@’%’ IDENTIFIED BY ‘rootlinux’;
GRANT ALL ON . TO ‘root’@‘%’;
ALTER USER ‘root’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘rootlinux’;
flush privileges;
（5）再次查看mysql数据库的user表中的主机和用户对应情况信息：
select user,host from user;

（6）本机windows的navicat远程连接登录linux系统下的mysql
打开navicat，连接MySQL，输入主机，端口，用户名，密码，连接测试，远程连接成功

4、修改用户密码

mysql修改密码
https://zhuanlan.zhihu.com/p/616953225
https://blog.youkuaiyun.com/qq_38002337/article/details/79553100

4.1、

4.2、

4.3、

4.4、

Navicat工具

补充

MySQL分页查询

补充

mysql进阶篇（b站黑马视频学习）

一、进阶_存储引擎

1、MySQL体系结构

1.1、MySQL体系结构：结构图

1.2、整个 MySQL Server 由以下组成

整个MySQL Server由以下组成：
Connection Pool : 连接池组件
Management Services & Utilities : 管理服务和工具组件
SQL Interface : SQL接口组件
Parser : 查询分析器组件
Optimizer : 优化器组件
Caches & Buffers : 缓冲池组件
Pluggable Storage Engines : 存储引擎
File System : 文件系统

1.3、MySQL体系结构层级

MySQL体系结构层级：

1. 连接层
   最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2. 服务层
   第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SOL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。
3. 引擎层
   存储引擎真正的负责了MySOL中数据的存储和提取，服务器通过AP和存储引擎进行通信。不同的存储擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
4. 存储层
   主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

2、存储引擎

2.1、存储引擎概述

和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。
存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

可以通过指定 show engines ， 来查询当前数据库支持的存储引擎 ：
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。
默认存储引擎是InnoDB。

2.2、如何查看存储引擎？

（1）查询emp表的建表语句：

（mysql5.5之后默认存储引擎是InnoDB）
show create table emp;

（2）建表时指定存储引擎，查看创建表的存储引擎。

CREATE TABLE 表名(
…
) ENGINE=INNODB;

示例1：创建my_myisam表指定存储引擎为MyISAM
CREATE TABLE my_myisam ( id INT, NAME VARCHAR ( 10 ) ) ENGINE = MyISAM;

查看创建表的存储引擎：
show create table my_myisam;

（3）查看当前数据库支持的存储引擎

可以通过指定 show engines ， 来查询当前数据库支持的存储引擎 ：
show engines;

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

（4）查看Mysql数据库默认的存储引擎指令：

show variables like ‘%storage_engine%’;

3、各种存储引擎特性

下面重点介绍几种常用的存储引擎， 并对比各个存储引擎之间的区别，如下表所示：

下面我们将重点介绍最长使用的两种存储引擎： InnoDB、MyISAM ， 另外两种 MEMORY、MERGE ， 了解即可。

3.1、InnoDB存储引擎

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎。在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎。

（1）InnoDB特点：

（1）DML 操作遵循 ACID 模型，支持事务
（2）行级锁，提高并发访问性能
（3）支持外键约束，保证数据的完整性和正确性

（2）InnoDB文件：

xxx.ibd：xxx代表表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
如何查看.ibd文件？
找到安装mysql8.0版本的data数据目录下的库，找到创建的以表名为emp后缀为.ibd的表空间文件

（3）InnoDB参数(如何打开emp.ibd表空间文件？)

InnoDB参数：innodb_file_per_table(mysql8.0版本是打开的)，决定多张表共享一个表空间还是每张表对应一个表空间
怎么查看是否每张表对应一个表空间？
查看系统的变量：show VARIABLES like “innodb_file_per_table”;

每一个InnoDB引擎的表，都会有这样一个表空间文件，在这个表空间文件里面，记录了：
1）当前数据库的表结构。
2）当前数据库所存放的数据。
3）索引
如何打开emp.ibd表空间文件？
因为.ibd是二进制文件，用记事本打开看不了，可以用指令ibd2sdi来查看，打开D:\mysql\MySQL Server 5.7 Data\Data\bjsxt目录，输入cmd，
输入：ibd2sdi emp.ibd
(意思是从idb文件当中提取sdi表结构数据，后面跟emp.ibd表结构文件)
报错：‘ibd2sdi’ 不是内部或外部命令，也不是可运行的程序或批处理文件。

报错解决：
1、你可能没有安装 MySQL 8.0 或更高的版本。‘ibd2sdi’ 是在 MySQL 8.0 中引入的，如果你正在使用的是一个较旧的版本，你需要更新你的 MySQL。
2、配置环境变量：系统变量和path变量。

（4）InnoDB逻辑存储结构：

• TableSpece：表空间
• Segment：段
• Extent：区（区大小固定：1M。一个区中包含64个页）
• Page：页（磁盘操作的最小单元，页大小固定：16K 。）
• Row：行

3.2、MyISAM存储引擎

（1）MyISAM特点：

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。有以下两个比较重要的特点：

（2）MyISAM不支持事务：

create table goods_myisam(
id int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;
在MyISAM存储引擎中，是没有事务控制的；

（3）MyISAM文件存储方式：

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：
.sdi (存储表结构定义)；
.MYD(MYData , 存储数据)；
.MYI(MYIndex , 存储索引)；

这个文件存储的是json格式数据，如何打开这个goods_myisam_369.sdi文件？？？
浏览器打开：json.cn，将用记事本打开的goods_myisam_369.sdi文件数据复制粘贴到此。

3.3、MEMORY存储引擎

Memory存储引擎将表的数据存放在内存中。
每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。
MEMORY类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引 ，但是服务一旦关闭，表中的数据就会丢失。

3.4、MERGE存储引擎

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST 或LAST 值使得插入操作被相应地作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。
可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。

4、存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

4.1、选择InnoDB存储引擎

InnoDB：是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。

4.2、选择MyISAM存储引擎

MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
在实际开发业务当中，已被mongodb替代

4.3、选择MEMORY存储引擎

MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。
MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
在实际开发业务当中，已被rediis替代

4.4、选择MERGE存储引擎

MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

一、进阶_索引

1、索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
如下面的示意图所示：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

2、索引优势劣势

2.1、优势

1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
2） 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

2.2、劣势

1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。磁盘现在是很便宜的
2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
对于一个正常的业务系统来说，增删改的比例非常少，主要是select查询

3、索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。
MySQL目前提供了以下4种索引：

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
  
  我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

3.1、B TREE B树结构

（1）B TREE B树特性

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：
树中每个节点最多包含m个孩子。
除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。
若根节点不是叶子节点，则至少有两个孩子。
所有的叶子节点都在同一层。
每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1
以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n>4时，中间节点向上分裂到父节点，两边节点分裂。

（2）5叉BTree B树分裂示例：

1. 数据结构可视化网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
   
2. 选择B TREE，选择5阶，插入数据：234、345、23、899、1200、1234、1500、1000、123、245、12、1567、1800、1980、2000、1888、2456
   

3.2、B+TREE B+树结构

（1）B+TREE B+树特性

B+Tree为BTree的变种，B+Tree与BTree的区别为：
1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
2). B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
3). 所有的非叶子节点都可以看作是key的索引部分。
由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

（2）5叉B+Tree B+树分裂示例：

1. 数据结构可视化网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
2. 选择B+TREE，选择5阶，插入数据：1000、567、234、232、1234、2345
   

3.3、 MySQL中的B+Tree B+树

（1）MySQL中的B+Tree B+树

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

（2）MySQL中的 B+Tree 索引结构示意图：

4、索引分类

5、索引语法

6、索引设计原则

一、进阶_SQL优化

1、插入数据优化

一、进阶_视图

1、视图是什么？

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

2、视图优点

视图相对于普通的表的优势主要包括：
（1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
（2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
（3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

3、创建视图

创建视图的语法为：
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS
select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

以oracle数据库scott用户下的表为示例。
示例：创建视图，视图展示deptno=30部门编号为30的员工所有信息。
CREATE OR replace view v_emp as select * from emp WHERE deptno=30;

3、查看视图

从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。

3.1、查看视图创建语句：

SHOW CREATE VIEW 视图名称;

示例：查看创建v_emp视图的创建语句。
SHOW CREATE VIEW v_emp;

navicat直接找到视图-设计视图-SQL预览

3.2、查看视图数据：

SELECT * from 视图名称;
表怎么查，视图就怎么查

示例：查看v_emp视图中所有数据。
SELECT * from v_emp;

4、修改视图

修改视图的语法为：
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

或者
CREATE OR REPLACE
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS
select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

选项 :
WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。
**LOCAL：**只要满足本视图的条件就可以更新。
**CASCADED：**必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.

示例1：CREATE OR REPLACE VIEW修改v_emp视图，只查询deptno=30的empno，deptno，sal字段。
CREATE OR REPLACE VIEW v_emp AS
select empno,deptno,sal from emp WHERE deptno=30;


示例2：ALTER VIEW修改v_emp视图，查询deptno=30的empno，deptno，sal，comm字段。

5、删除视图

语法 :
DROP VIEW [IF EXISTS] view_name [, view_name] …[RESTRICT | CASCADE]

示例 , 删除v_emp视图
DROP VIEW IF EXISTS v_emp;

这些语句只是学习面试用，知道会写就好。在公司做数据分析项目，真正的项目上打开navicat视图直接写一条sql就是一个视图，报表中去视图中取数。偶尔写，不会一直这样去写，有病，等这样写出来黄花菜都凉了

6、视图添加数据（对视图的增删改就是对原表的增删改）

语法：
insert into 视图名称 values(值1，值2 …);

示例：创建v_emp_copy1视图，视图中展示部门编号为20deptno=20员工的empno,sal,comm,deptno字段信息。
CREATE OR replace view v_emp_copy1 as select empno,sal,comm,deptno from emp_copy1 WHERE deptno=20;

v_emp_copy1视图中添加数据7777，888，666，20
insert into v_emp_copy1 values(7777,888,666,20);

v_emp_copy1视图所对应的emp_copy1基表中数据为：

也就是说，对视图的增删改就是对原表的增删改。

7、创建视图的检查选项：[WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE OR REPLACE
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS
select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

选项 :
WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。
**LOCAL：**只要满足本视图的条件就可以更新。
**CASCADED：**必须满足所有针对该视图的所有视图的条件才可以更新。 默认值。

7.1、视图-检查选项：LOCAL

创建视图同时给检查选项，当插入数据不符合检查选项的条件，插入失败。
DROP VIEW IF EXISTS v_emp_copy1;
CREATE OR replace view v_emp_copy1 as select empno,sal,comm,deptno from emp_copy1 WHERE deptno=20 WITH LOCAL CHECK OPTION;
插入数据：
deptno<=20可以插入数据
v_emp_copy1视图中添加数据7777，888，666，20
insert into v_emp_copy1 values(7777,888,666,20);

插入数据：
deptno=30不可以插入数据
v_emp_copy1视图中添加数据7777，888，666，30
insert into v_emp_copy1 values(7777,888,666,30);

7.2、视图-检查选项：CASCADED

创建视图同时给检查选项，当插入数据不符合检查选项的条件，插入失败。
DROP VIEW IF EXISTS v_emp_copy1;
CREATE OR replace view v_emp_copy1 as select empno,sal,comm,deptno from emp_copy1 WHERE deptno<=20 WITH CASCADED CHECK OPTION;

插入数据：
deptno<=30不可以插入数据
v_emp_copy1视图中添加数据7777，888，666，30
insert into v_emp_copy1 values(7777,888,666,30);

从测试结果可以看出，payment_view1 是 WITH LOCAL CHECK OPTION 的，所以只要满足本视图的条件就可以更新，但是 payment_view2 是 WITH CASCADED CHECK OPTION 的，必须满足针对该视图的所有视图才可以更新，因为更新后记录不再满足 payment_view 的条件，所以更新操作提示错误退出。

一、进阶_存储过程和函数

1、存储过程和函数概述

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程特点：
封装、复用，可以接收参数，也可以返回数据，减少网络交互，效率提升。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。
函数：是一个有返回值的过程；
过程：是一个没有返回值的函数；

2、创建存储过程

语法：
CREATE PROCEDURE procedure_name ([proc_parameter[,…]])
begin
– SQL语句
end;

2.1、示例：写 pr0_1() 存储过程统计emp表的总记录数。

delimiter $
create procedure pr0_1()
begin
select count(*) from emp;
end$
delimiter;

2.2、DELIMITER 关键字：

DELIMITER
该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

3、call调用存储过程

语法：
call procedure_name();

示例：call调用创建的 pro_test1() 存储过程
call pro_test1();

4、查看存储过程的三种方式：

4.1、查询指定db_name数据库中的所有的存储过程及信息状态

查询数据库中的所有的存储过程，这个存储过程在mysql的information_schema库的routines系统表，加where条件限制数据库的名字， where routine_schema=‘db_name’;
语法：
select * from information_schema.routines where routine_schema=‘db_name’;

示例：查询mysql的scott数据库中的所有的存储过程。
这个存储过程在mysql的information_schema库的routines系统表，加where条件限制数据库的名字， where routine_schema=‘scott’;
select * from information_schema.routines where routine_schema=‘scott’;

4.2、查询存储过程的状态信息

查询存储过程的状态信息
语法：
show procedure status;

示例：查看存储过程的状态信息

4.3、查询某个存储过程的定义

查询某个存储过程的定义
语法：
show create procedure test.pro_test1 \G;

示例：查看scott库下创建的pro_test1存储过程的定义
show create procedure scott.pro_test1;

内容为：CREATE DEFINER=root@localhost PROCEDURE pro_test1()
begin
select count(*) from emp;
end

DEFINER=root@localhost指定是创建用户是哪个当前登录的root用户。创建时没有指定会自动加上默认参数。

navicat双击函数查看存储过程：

5、删除存储过程

语法：
DROP PROCEDURE [IF EXISTS] sp_name;
[IF EXISTS]为可选参数，意思是如果存在。

示例：删除scott数据库下的pro_test1存储过程
DROP PROCEDURE IF EXISTS scott.pro_test1;

删除后再次查看存储过程报错不存在说明已经删除了。
show create procedure scott.pro_test1;

6、存储过程的语法

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能。

6.1、系统变量

系统变量是mysql服务器提供的，不是用户定义的，属于服务器层面，分为全局变量global、会话变量session。

（1）查看系统变量

1. 查看所有系统变量
   SHOW [SESSION | GLOBAL] VARIABLES;
   示例：默认会话级别系统变量
   show variables;
   
2. 可以通过LIKE模糊匹配方式查找变量
   SHOW [SESSION | GLOBAL] VARIABLES LIKE '…’;
   示例：show session variables like ‘auto%’;
   
3. 查看指定变量的值
   SELECT @@[SESSION|GLOBAL] 系统变量名;
   示例：准确查看自动提交事务autocommit系统环境变量的值。
   select @@autocommit;
   

（2）设置系统变量

方式一：SET [SESSION|GLOBAL] 系统变量名=值;

示例：将会话变量的autocommit关掉。准确查看autocommit系统环境变量的值。
set session autocommit=0;
select @@session.autocommit;

当前会话，不会影响到其他会话。

方式二：SET @@[SESSION| GLOBAL] 系统变量名 =值;

SET global autocommit=0;
设置了自动提交事务autocommit系统环境全局变量的会话关闭为0，会影响到其他会话。

当mysql服务器重启systemctl restart mysqld之后，这个参数又会初始化为默认值autocommit=1;

注意：
如果没有指定SESSION/GLOBAL，默认是SESSION会话变量。
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

6.2、用户自定义变量

（1）DECLARE：

DECLARE：通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。
语法：
DECLARE var_name[,…] type [DEFAULT value]

示例：
delimiter $
create procedure pro_test2()
begin
declare num int default 5;
select num+ 10;
end$
delimiter ;

（2）SET：

SET：直接赋值使用 SET，可以赋常量或者赋表达式。
具体语法如下：
SET var_name = expr [, var_name = expr] …

示例：
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE NAME VARCHAR(20);
SET NAME = ‘MYSQL’;
SELECT NAME ;
END$
DELIMITER;

一、进阶_触发器

1、触发器介绍

1.1、什么是触发器？

触发器是与表有关的数据库对象，指在 insert/update/delete 插入/更新/删除数据 之前或之后，触发并执行触发器中定义的SQL语句集合。mysql只支持行级触发器。不支持语句级触发器。

1.2、优点（为什么要用）

触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。

1.3、引用触发器：

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

1.4、触发器类型：

2、创建触发器

语法结构：
**
create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] ##行级触发器
begin
trigger_stmt;
end;
**
trigger_name：触发器名称。
before/after：指定是增删改之前还是之后触发。
insert/update/delete：指定什么类型的触发器，插入/更新/删除。
tbl_name：表名，当哪一张表增删改的时候触发。
[ for each row ]：行级触发器，mysql只支持行级触发器，for each row 必写。
begin 和 end 之间写 trigger_stmt; 触发器里面的具体逻辑实现。

3、查看触发器：SHOW TRIGGERS;

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。
语法结构：
show triggers;

4、删除触发器

语法结构：
删除指定数据库下的触发器
drop trigger [schema_name.]trigger_name
[schema_name.] 哪个数据库名称，如果没有指定 schema_name，默认为当前数据库。
trigger_name：触发器名称。

5、触发器案例：

通过触发器记录 emp 表的数据变更日志，包含增加，修改，删除。
定义创建触发器时要定义三个触发器。因为增删改都需要触发。

5.1、准备工作：

（1）创建 emp 表，插入数据

#创建emp表
create table emp(
id int(11) not null auto_increment ,
name varchar(50) not null comment ‘姓名’,
age int(11) comment ‘年龄’,
salary int(11) comment ‘薪水’,
primary key(id)
)engine=innodb default charset=utf8;

#emp表插入数据
insert into emp(id,name,age,salary) values
(null,‘金毛狮王’,55,3800),
(null,‘白眉鹰王’,60,4000),
(null,‘青翼蝠王’,38,2800),
(null,‘紫衫龙王’,42,1800);

#查询emp表
select * from emp;

（2）创建 emp_logs 日志表，表中记录触发器的操作日志

#2.创建一张emp_logs日志表：
create table emp_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment ‘操作类型, insert/update/delete’,
operate_time datetime not null comment ‘操作时间’,
operate_id int(11) not null comment ‘操作表的ID’,
operate_params varchar(500) comment ‘操作参数’,
primary key(id)
)engine=innodb default charset=utf8;

5.2、示例：创建 insert 型触发器，完成 insert插入 数据时的日志记录

1、我们要的是在增删改之后记录日志，所以选的是after insert：插入数据后型触发器。（实际项目中如果增删改之前记录日志用before）。
2、begin和end之间编写的触发器的具体逻辑：往日志表中记录日志数据就可以了。
3、使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容。
4、往日志表emp_logs中insert into插入数据，id是自增的，所以传入null就可以了、operation操作类型是’insert’、operate_time操作时间是当前时间通过now函数获取当前时间、operate_id当前操作插入数据的id是用new.id来获取、operate_params操作的参数用字符串拼接concat(‘插入后(id:’,new.id,‘, name:’,new.name, ‘, age:’,new.age,‘, salary:’,new.salary,‘)’)来实现。
5、执行创建触发器没有报错就说明触发器执行成功了。

（1）创建 insert 型触发器

DELIMITER $
create trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,‘insert’,now(),new.id,concat(‘插入后(id:’,new.id,‘, name:’,new.name,
‘, age:’,new.age,‘, salary:’,new.salary,‘)’));
end $
DELIMITER;

（2）查看创建的触发器

show triggers;

（3）测试：插入两条数据到emp表

insert into emp(id,name,age,salary) values(null, ‘光明左使’,30,3500);
insert into emp(id,name,age,salary) values(null, ‘光明右使’,33,3200);

（4）查看 emp_logs 日志表：多了两条日志数据

select * from emp_logs;

5.2、示例：创建 update 型触发器，完成 update更新 修改数据时的日志记录：

operate_params操作的参数用字符串concat函数将用old把更新之前的数据和用new把更新之后的数据拼接起来。

（1）创建触发器

DELIMITER $
create trigger emp_logs_update_trigger
after update
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,‘update’,now(),new.id,concat(‘修改前(id:’,old.id,‘, name:’,old.name,‘,
age:’,old.age,‘, salary:’,old.salary,‘) , 修改后(id’,new.id, ‘name:’,new.name,‘,
age:’,new.age,‘, salary:’,new.salary,‘)’));
end $
DELIMITER ;

（2）查看创建的触发器

show triggers;

（3）测试：update更新数据到emp表

update emp set salary=1111 where id=6; #日志表中触发一条数据
update emp set salary=2222 where id<=2; #日志表中触发两条数据

（4）查看 emp_logs 日志表：多了三条update日志数据

select * from emp_logs;

5.4、示例：创建 delete 型触发器，完成 delete删除 数据时的日志记录：

对于删除操作，删除之前是有数据的，删除之后没有数据了。用old

（1）创建触发器

DELIMITER $
create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,‘delete’,now(),old.id,concat(‘删除前(id:’,old.id,‘, name:’,old.name,‘,
age:’,old.age,‘, salary:’,old.salary,‘)’));
end $
DELIMITER;

（2）查看创建的触发器

show triggers;

（3）测试：emp表删除两条数据

delete from emp where id = 6; #日志表中触发一条数据

（4）查看 emp_logs 日志表：多了三条update日志数据

select * from emp_logs;

MYSQL运维篇（b站黑马视频学习）

一、MYSQL中常用工具

1、mysql

该mysql不是指mysql服务，而是指mysql的客户端工具。

语法：
mysql [options] [database]

1.1、连接选项

参数：
-u，–user=name 指定用户名
-p，–password[=name] 指定密码
-h，–host=name 指定服务器IP或域名
-P，–port=# 指定连接端口
示例 ：
mysql -h 127.0.0.1 -P 3306 -u root -p
mysql -h127.0.0.1 -P3306 -uroot -p2143

1.2、执行选项

参数：
-e, --execute=name 执行SQL语句并退出
此选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。

示例：
mysql -uroot -p2143 db01 -e “select * from tb_book”;

2、mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

可以通过 ： mysqladmin --help 指令查看帮助文档

示例 ：
mysqladmin -uroot -p2143 create ‘test01’;
mysqladmin -uroot -p2143 drop ‘test01’;
mysqladmin -uroot -p2143 version;

3、 mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。

语法：
mysqlbinlog [options] log-files1 log-files2 …

选项：
-d, --database=name : 指定数据库名称，只列出指定的数据库相关操作。
-o, --offset=# : 忽略掉日志中的前n行命令。
-r,–result-file=name : 将输出的文本格式日志输出到指定文件。
-s, --short-form : 显示简单格式， 省略掉一些信息。
–start-datatime=date1 --stop-datetime=date2 : 指定日期间隔内的所有日志。
–start-position=pos1 --stop-position=pos2 : 指定位置间隔内的所有日志。

4、mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。

语法：
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3…]
mysqldump [options] --all-databases/-A

4.1、连接选项

参数 ：
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器IP或域名
-P, --port=# 指定连接端口

4.2、输出内容选项

参数：
–add-drop-database 在每个数据库创建语句前加上 Drop database 语句
–add-drop-table 在每个表创建语句前加上 Drop table 语句 , 默认开启 ; 不开启 (–
skip-add-drop-table)
-n, --no-create-db 不包含数据库的创建语句
-t, --no-create-info 不包含数据表的创建语句
-d --no-data 不包含数据
-T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；
一个.txt文件，数据文件，相当于select into outfile

示例 ：
mysqldump -uroot -p2143 db01 tb_book --add-drop-database --add-drop-table > a
mysqldump -uroot -p2143 -T /tmp test city

5、mysqlimport/source

mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。

语法：
mysqlimport [options] db_name textfile1 [textfile2…]

示例：
mysqlimport -uroot -p2143 test /tmp/city.txt

如果需要导入sql文件,可以使用mysql中的source 指令：
source /root/tb_book.sql

6、mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

语法：
mysqlshow [options] [db_name [table_name [col_name]]]

参数：
–count 显示数据库及表的统计信息（数据库，表 均可以不指定）
-i 显示指定数据库或者指定表的状态信息

示例：
#查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p2143 --count
#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count
#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count

二、MYSQL日志

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件。MySQL 也不例外，在 MySQL 中，有 4 种不同的日志，分别是错误日志、二进制日志（BINLOG 日志）、查询日志和慢查询日志，这些日志记录着数据库在不同方面的踪迹。

1、错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。
该日志是默认开启的 ， 默认存放目录为 mysql 的数据目录（var/lib/mysql）, 默认的日志文件名为：hostname.err（hostname是主机名）。

1.1、查看错误日志位置指令：

show variables like ‘log_error%’;

1.2、查看错误日志内容：

tail -f /var/lib/mysql/xaxh-server.err

2、二进制日志

2.1、概述

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。
二进制日志，默认情况下是没有开启的，需要到MySQL的配置文件中开启，并配置MySQL日志的格式。

配置文件位置 : /usr/my.cnf

日志存放位置 : 配置时，给定了文件名但是没有指定路径，日志默认写入Mysql的数据目录。

#配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如 :
mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin
#配置二进制日志的格式
binlog_format=STATEMENT

2.2、二进制日志格式

STATEMENT

该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件
中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会
将日志解析为原文本，并在从库重新执行一次。

ROW

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句 ： update
tb_book set status=‘1’ , 如果是STATEMENT 日志格式，在日志中会记录一行SQL文件； 如果是ROW，由于是对全
表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更。

MIXED

这是目前MySQL默认的日志格式，即混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT，但是在
一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点。

2.3、二进制日志读取

由于日志以二进制方式存储，不能直接读取，需要用mysqlbinlog工具来查看。
语法如下 ：
mysqlbinlog log-file;

读取查看STATEMENT格式日志

执行插入语句：
insert into tb_book values(null,‘Lucene’,‘2088-05-01’,‘0’);
查看日志文件：
mysqlbin.index : 该文件是日志索引文件 ， 记录日志的文件名；
mysqlbing.000001 ：日志文件
查看日志内容：
mysqlbinlog mysqlbing.000001；

读取查看ROW格式日志

配置：
#配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如 :
mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin
#配置二进制日志的格式
binlog_format=ROW

插入数据：
insert into tb_book values(null,‘SpringCloud实战’,‘2088-05-05’,‘0’);

如果日志格式是 ROW , 直接查看数据 , 是查看不懂的 ; 可以在mysqlbinlog 后面加上参数 -vv
mysqlbinlog -vv mysqlbin.000002

2.4、日志删除

对于比较繁忙的系统，由于每天生成日志量大 ，这些日志如果长时间不清楚，将会占用大量的磁盘空间。下面我们将会讲解几种删除日志的常见方法：

方式一
通过 Reset Master 指令删除全部 binlog 日志，删除之后，日志编号，将从 xxxx.000001重新开始 。
查询之前 ，先查询下日志文件

执行删除日志指令：
Reset Master

执行之后， 查看日志文件：

方式二
执行指令 purge master logs to ‘mysqlbin.******’ ，该命令将删除 ****** 编号之前的所有日志。

方式三
执行指令 purge master logs before ‘yyyy-mm-dd hh24:mi:ss’ ，该命令将删除日志为 “yyyy-mm-dd
hh24:mi:ss” 之前产生的所有日志 。
方式四
设置参数 --expire_logs_days=# ，此参数的含义是设置日志的过期天数， 过了指定的天数后日志将会被自动删除，这样将有利于减少DBA 管理日志的工作量。
配置如下 ：

3、查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。

默认情况下， 查询日志是未开启的。如果需要开启查询日志，可以设置以下配置 ：

4、慢查询日志

三、MYSQL主从复制

1、Mysql

四、综合案例

五、MYSQL分库分表

六、MYSQL读写分离