MySql进阶教程

全部的PDF笔记: https://qingling.icu/posts/50465.html

原视频地址: 黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括

一.Mysql入门

1.SQL

全称 Structured Query Language，结构化查询语言。操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准 。

1.1 SQL通用语法

1.2 SQL分类

1.3 DDL-数据定义语言

连接本地mysql的命令

mysql -u root -p

常用的DDL操作

-- ------------------------------------------------------数据库的操作------------------------------------------------------------------------
-- 展示所有的数据库
show databases ;

-- 查询当前的数据库
select database();

-- 创建数据库
create database if not exists  itcast;

-- 删除数据库
drop database if exists itcast;

-- 使用数据库
use itcast;


-- ----------------------------------------------------------表的操作--------------------------------------------------------------------

-- 创建用户表
create table tb_user(id int comment '编号', name varchar(50) comment '姓名', age int comment '年龄',gender varchar(1) comment '性别' ) comment '用户表';

-- 显示创建表的结构
desc tb_user;

-- 显示创建表的语句
show create table tb_user;

-- 删除表
drop table emp;

-- 修改表的操作
-- 举例：现有一张员工表
create table emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    entrydate date comment '入职时间'
) comment '员工表';
-- 查看表结构
desc emp;

-- 修改表结构(向emp表中添加昵称字段)
alter table emp add nickname varchar(20) comment '昵称';

-- 修改相应字段的数据类型(把age的数据类型从int改回tinyint)
alter table emp modify age tinyint;

-- 修改字段名和字段的类型(把nicknem字段改为username,并修改数据类型)
alter table emp change nickname username varchar(30) comment '用户名';

-- 删除字段(删除username字段)
alter table emp drop username;

-- 修改表名(将表名字从emo修改为employee)
alter table emp rename to employee;

-- 删除表
drop table if exists employee;
-- 删除指定表，并重新创建该表(相当于删除表中的全部数据)
truncate table employee;

Mysql的数据类型

数值数据类型

字符串数据类型

日期时间类型

1.4 DML-数据操作语言

-- ------------------------------------------------DML-增删改操作---------------------------------------------------
-- 添加数据
select database();
show tables;
-- 该指定的字段添加数据
insert into employee(id, workno, name, gender, age, idcard, entrydate) value (1, '1', '小华', '男', 20, '123456789123456789', '2001-01-01');
select *
from employee;
-- 给全部的字段添加数据
insert into employee value (2, '2', '小刚', '男', 23, '123456784124456789', '2008-01-01');
-- 批量添加数据
insert into employee
values (3, '3', '小李', '男', 23, '123456784124456789', '2008-01-01'),
       (4, '4', '小黄', '男', 23, '123456784124456789', '2008-01-01');

-- 修改数据（不带条件修改所有）
update employee set name = '张三' where id = 1;
update employee set name  = '李四', gender= '女' where id = 1;

-- 删除数据(删除id为4的数据，不带添加删除所有）
delete from employee where id = 4;

1.5 DQL-数据查询语言

• 基本查询（不带任何条件）
• 条件查询（WHERE）
• 聚合函数（count、max、min、avg、sum）
• 分组查询（group by）
• 排序查询（order by）
• 分页查询（limit

测试数据准备

-- 数据准备
create table emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    workaddress varchar(50) comment '工作地址',
    entrydate date comment '入职时间'
) comment '员工表';
--  插入数据
insert into emp(id, workno, name, gender, age, idcard, workaddress,entrydate)
values (1,'1','柳岩','女',20,'12345678912345678','北京','2001-01-01'),
       (2,'2','张无忌','男',18,'123456789012345670','北京','2005-09-01'),
       (3,'3','韦一笑','男',38,'12345678972345670','上海','2005-08-01'),
       (4,'4','赵敏','女',18,'12345675712345670','北京','2009-12-01'),
       (5,'5','小昭','女',16,'123456769012345678','上海','2007-07-01'),
       (6,'6','杨逍','男',28,'1234567893123456X','北京','2006-01-01'),
       (7,'7','范瑶','男',40,'123456789212345670','北京','2005-05-01'),
       (8,'8','黛绮丝','女',38,'123456157123645670','天津','2015-05-01'),
       (9,'9','范凉凉','女',45,'123156789012345678','北京','2010-04-01'),
       (10,'10','陈友谅','男',53,'123456789012345670','上海','2011-01-01'),
       (11,'11','张士诚','男',55,'12356789712345670','江苏','2015-05-01'),
       (12,'12','常遇春','男',32,'123446757152345670','北京','2004-02-01'),
       (13,'13','张三丰','男',88,'123656789012345678','江苏','2020-11-01'),
       (14,'14','灭绝','女',65,'123456719012345670','西安','2019-05-01'),
       (15,'15','胡青年','男',70,'12345674971234567X','西安','2018-04-01'),
       (16,'16','周芷若','女',18,null,'北京','2012-06-01');

1.5.1 基础查询

-- 查看插入的数据
select * from emp;

-- 查询指定字段 name workno age 返回
select name, workno, age
from emp;

-- 查询所有字段返回
select id,
       workno,
       name,
       gender,
       age,
       idcard,
       workaddress,
       entrydate
from emp;

-- distinct去重关键字
-- 查询所有员工的工作地址(不要重复的地址)
select distinct workaddress '工作地址'
from emp;

1.5.2 条件查询

语法: SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

举例

-- 条件查询
-- 查询年龄等于 88 的员工
select * from emp where age = 88;

-- 查询年龄小于20的员工信息
select * from emp where age < 20;

-- 查询年龄小于等于20的员工信息
select * from emp where age <= 20;

-- 查询没有身份证号的员工信息
select * from emp where idcard is null ;

-- 查询年龄不等于88的员工信息(<>也表示不等于)
select * from emp where age != 88;
select * from emp where age <> 88;

-- 查询年龄在15~20(包含)之间的员工信息（三种实现方式）
select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20; #包含了15和20

-- 查询性别为女 且年龄小于25岁的员工
select * from emp where gender = '女' and age < 25;

-- 查询年龄等于18 或 20 或 48 的员工
select * from emp where age = 18 or age = 20 or age = 48;
select * from emp where age in (18,20,48);

-- 查询姓名为两个字的员工(模糊匹配(_匹配单个字符, %匹配任意个字符))
select * from emp where name like '__';

-- 查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';

1.5.3 聚合函数

语法: SELECT 聚合函数(字段列表) FROM 表名 ;

注意 : NULL值是不参与所有聚合函数运算的

-- 聚合函数
-- 统计该企业员工数量(空值是不参与统计的)
select count(*) from emp;
select count(idcard) from emp;

-- 统计该企业员工的平均年龄
select avg(age) from emp;

-- 统计该企业员工的最大年龄
select max(age) from emp;

-- 统计该企业员工的最小年龄
select min(age) from emp;

-- 统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = '西安';

1.5.4 分组查询

语法: SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组 后过滤条件 ];

where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
• 判断条件不同：where不能对聚合函数进行判断，而having可以

注意事项:
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
• 执行顺序: where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB

-- 分组查询
-- 根据性别分组，统计男性员工和女性员工的数量
select gender, count(*) from emp group by gender;

-- 根据性别进行分组，统计男性员工和女性员工的平均年龄
select gender, avg(age) from emp group by gender;

-- 查询年龄小于45岁的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址(可以使用别名也可以不使用别名)
select workaddress,count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;

1.5.5 排序查询

语法: SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

注意事项：
• 如果是升序, 可以不指定排序方式ASC，因为升序ASC是默认值 ;
• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

-- 排序查询
-- 根据年龄对公司的员工进行升序排序(asc可以省略)
select * from emp order by age  ;
-- 降序排序
select * from emp order by age desc ;

-- 根据员工的入职时间降序排序
select * from emp order by entrydate desc ;

-- 根据员工的年龄升序排序，年龄相同，再按照入职时间进行降序排序
select * from emp order by age asc ,entrydate desc ;

1.5.6 分页查询

语法: SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

注意事项:
• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

-- 分页查询
-- 查询第一页的员工数据，每页显示10条记录
select * from emp limit 0,10;

-- 查询第二页数据，每页展示十条数据(起始索引 = （查询页码 - 1）* 每页显示记录数)
select * from emp limit 10,10;

1.5.7 DQL语句案例

-- DQL语句案例
-- 查询年龄在20,21,22,23岁的女性员工的信息
select * from emp where gender = '女' and age in (20,21,22,23);

-- 查询性别为男，并且年龄再20~40岁(含)以内的姓名为三个字的员工
select * from emp where gender = '男' and (age between 20 and 40) and name like '___';

-- 统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
select gender,count(*) from emp where age < 60 group by gender;

-- 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按照入职时间降序排序
select name,age from emp where age <= 35 order by age asc ,entrydate desc ;

-- 查询性别为男,且年龄在20·40岁(含)以内的前五个员工信息，并对查询结果按年龄升序排序，如果年龄相同按照入职时间升序排序
select * from emp where gender = '男' and age between 20 and 40 order by age,entrydate limit 5;

1.5.8 执行顺序

总结

1.6 DCL-数据控制语言

1.6.1 管理用户

查询用户: select * from mysql.user;

创建用户:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码: ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

删除用户:DROP USER '用户名'@'主机名' ;

注意事项:
• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户

• 主机名可以使用 % 通配

• 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库管理员）使用

-- 切换到系统中自带的mysql数据库
use mysql;

-- 查询所有的用户
select * from mysql.user;

-- 创建用户itcast, 只能够在当前主机localhost访问, 密码123456;
create user 'itcast'@'localhost' identified by '123456';

-- 创建用户heima, 可以在任意主机访问该数据库, 密码123456;
create user 'heima'@'%' identified by '123456';

-- 修改用户heima的访问密码为1234;
alter user 'heima'@'%' identified with mysql_native_password by '1234';

-- 删除 itcast@localhost 用户
drop user 'itcast'@'localhost';

1.6.2 权限控制

查询权限: SHOW GRANTS FOR '用户名'@'主机名' ;

授予权限: GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限: REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项：
• 多个权限之间，使用逗号分隔
• 授权时， 数据库名和表名可以使用 * 进行通配，代表所有

-- 权限控制
-- 查询权限(查询heima用户的所有权限)
show grants for 'heima'@'%';

-- 授予权限(授予heima用户对数据库itcast的所有权限)
grant all on itcast.* to 'heima'@'%';

-- 授予用户所有数据库所有表的所有权限(相当于超级管理员)
grant all on *.* to 'heima'@'%';

-- 撤销权限(撤销heima用户在itcast数据库上的所有权限)
revoke all on itcast.* from 'heima'@'%';

总结

全部的SQL

-- ------------------------------------------------------数据库的操作------------------------------------------------------------------------
-- 展示所有的数据库
show databases;

-- 查询当前的数据库
select database();

-- 创建数据库
create database if not exists itcast;

-- 删除数据库
drop database if exists itcast;

-- 使用数据库
use itcast;


-- ----------------------------------------------------------表的操作--------------------------------------------------------------------

-- 创建用户表
create table tb_user
(
    id     int comment '编号',
    name   varchar(50) comment '姓名',
    age    int comment '年龄',
    gender varchar(1) comment '性别'
) comment '用户表';

-- 显示创建表的结构
desc tb_user;

-- 显示创建表的语句
show create table tb_user;

-- 删除表
drop table emp;

-- 修改表的操作
-- 举例：现有一张员工表
create table emp
(
    id        int comment '编号',
    workno    varchar(10) comment '工号',
    name      varchar(10) comment '姓名',
    gender    char(1) comment '性别',
    age       tinyint unsigned comment '年龄',
    idcard    char(18) comment '身份证号',
    entrydate date comment '入职时间'
) comment '员工表';
-- 查看表结构
desc emp;

-- 修改表结构(向emp表中添加昵称字段)
alter table emp
    add nickname varchar(20) comment '昵称';

-- 修改相应字段的数据类型(把age的数据类型从int改回tinyint)
alter table emp
    modify age tinyint;

-- 修改字段名和字段的类型(把nickname字段改为username,并修改数据类型)
alter table emp
    change nickname username varchar(30) comment '用户名';

-- 删除字段(删除username字段)
alter table emp
    drop username;

-- 修改表名(将表名字从emo修改为employee)
alter table emp rename to employee;

-- 删除表
drop table if exists employee;
-- 删除指定表，并重新创建该表(相当于删除表中的全部数据)
truncate table employee;



-- ------------------------------------------------DML-增删改操作---------------------------------------------------
-- 添加数据
select database();
show tables;
-- 该指定的字段添加数据
insert into employee(id, workno, name, gender, age, idcard, entrydate) value (1, '1', '小华', '男', 20, '123456789123456789', '2001-01-01');

select *
from employee;
-- 给全部的字段添加数据
insert into employee value (2, '2', '小刚', '男', 23, '123456784124456789', '2008-01-01');
-- 批量添加数据
insert into employee
values (3, '3', '小李', '男', 23, '123456784124456789', '2008-01-01'),
       (4, '4', '小黄', '男', 23, '123456784124456789', '2008-01-01');

-- 修改数据（不带条件修改所有）
update employee
set name = '张三'
where id = 1;
update employee
set name  = '李四',
    gender= '女'
where id = 1;

-- 删除数据(删除id为4的数据，不带添加删除所有）
delete
from employee
where id = 4;


-- --------------------------------------------------------DQL数据查询语言--------------------------------------------------
-- 数据准备
create table emp
(
    id          int comment '编号',
    workno      varchar(10) comment '工号',
    name        varchar(10) comment '姓名',
    gender      char(1) comment '性别',
    age         tinyint unsigned comment '年龄',
    idcard      char(18) comment '身份证号',
    workaddress varchar(50) comment '工作地址',
    entrydate   date comment '入职时间'
) comment '员工表';

--  插入数据
insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate)
values (1, '1', '柳岩', '女', 20, '12345678912345678', '北京', '2001-01-01'),
       (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
       (3, '3', '韦一笑', '男', 38, '12345678972345670', '上海', '2005-08-01'),
       (4, '4', '赵敏', '女', 18, '12345675712345670', '北京', '2009-12-01'),
       (5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),
       (6, '6', '杨逍', '男', 28, '1234567893123456X', '北京', '2006-01-01'),
       (7, '7', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01'),
       (8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),
       (9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),
       (10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
       (11, '11', '张士诚', '男', 55, '12356789712345670', '江苏', '2015-05-01'),
       (12, '12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),
       (13, '13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),
       (14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),
       (15, '15', '胡青年', '男', 70, '12345674971234567X', '西安', '2018-04-01'),
       (16, '16', '周芷若', '女', 18, null, '北京', '2012-06-01');

-- 基础查询
-- 查看插入的数据
select * from emp;

-- 查询指定字段 name workno age 返回
select name, workno, age
from emp;

-- 查询所有字段返回
select id,
       workno,
       name,
       gender,
       age,
       idcard,
       workaddress,
       entrydate
from emp;

-- distinct去重关键字
-- 查询所有员工的工作地址(不要重复的地址)
select distinct workaddress '工作地址'
from emp;





-- 条件查询
-- 查询年龄等于 88 的员工
select * from emp where age = 88;

-- 查询年龄小于20的员工信息
select * from emp where age < 20;

-- 查询年龄小于等于20的员工信息
select * from emp where age <= 20;

-- 查询没有身份证号的员工信息
select * from emp where idcard is null ;

-- 查询年龄不等于88的员工信息(<>也表示不等于)
select * from emp where age != 88;
select * from emp where age <> 88;

-- 查询年龄在15~20(包含)之间的员工信息（三种实现方式）
select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20; #包含了15和20

-- 查询性别为女 且年龄小于25岁的员工
select * from emp where gender = '女' and age < 25;

-- 查询年龄等于18 或 20 或 48 的员工
select * from emp where age = 18 or age = 20 or age = 48;
select * from emp where age in (18,20,48);

-- 查询姓名为两个字的员工(模糊匹配(_匹配单个字符, %匹配任意个字符))
select * from emp where name like '__';

-- 查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';




-- 聚合函数
-- 统计该企业员工数量(空值是不参与统计的)
select count(*) from emp;
select count(idcard) from emp;

-- 统计该企业员工的平均年龄
select avg(age) from emp;

-- 统计该企业员工的最大年龄
select max(age) from emp;

-- 统计该企业员工的最小年龄
select min(age) from emp;

-- 统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = '西安';





-- 分组查询
-- 根据性别分组，统计男性员工和女性员工的数量
select gender, count(*) from emp group by gender;

-- 根据性别进行分组，统计男性员工和女性员工的平均年龄
select gender, avg(age) from emp group by gender;

-- 查询年龄小于45岁的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址(可以使用别名也可以不使用别名)
select workaddress,count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;




-- 排序查询
-- 根据年龄对公司的员工进行升序排序(asc可以省略)
select * from emp order by age  ;
-- 降序排序
select * from emp order by age desc ;

-- 根据员工的入职时间降序排序
select * from emp order by entrydate desc ;

-- 根据员工的年龄升序排序，年龄相同，再按照入职时间进行降序排序
select * from emp order by age asc ,entrydate desc ;




-- 分页查询
-- 查询第一页的员工数据，每页显示10条记录
select * from emp limit 0,10;

-- 查询第二页数据，每页展示十条数据(起始索引 = （查询页码 - 1）* 每页显示记录数)
select * from emp limit 10,10;


-- DQL语句案例
-- 查询年龄在20,21,22,23岁的女性员工的信息
select * from emp where gender = '女' and age in (20,21,22,23);

-- 查询性别为男，并且年龄再20~40岁(含)以内的姓名为三个字的员工
select * from emp where gender = '男' and (age between 20 and 40) and name like '___';

-- 统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
select gender,count(*) from emp where age < 60 group by gender;

-- 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按照入职时间降序排序
select name,age from emp where age <= 35 order by age asc ,entrydate desc ;

-- 查询性别为男,且年龄在20·40岁(含)以内的前五个员工信息，并对查询结果按年龄升序排序，如果年龄相同按照入职时间升序排序
select * from emp where gender = '男' and age between 20 and 40 order by age,entrydate limit 5;



-- ---------------------------------------------------DCL--------------------------------------------------
-- 用户管理
-- mysql数据库
use mysql;

-- 查询所有的用户
select * from mysql.user;

-- 创建用户itcast, 只能够在当前主机localhost访问, 密码123456;
create user 'itcast'@'localhost' identified by '123456';

-- 创建用户heima, 可以在任意主机访问该数据库, 密码123456;
create user 'heima'@'%' identified by '123456';

-- 修改用户heima的访问密码为1234;
alter user 'heima'@'%' identified with mysql_native_password by '1234';

-- 删除 itcast@localhost 用户
drop user 'itcast'@'localhost';




-- 权限控制
-- 查询权限(查询heima用户的所有权限)
show grants for 'heima'@'%';

-- 授予权限(授予heima用户对数据库itcast的所有权限)
grant all on itcast.* to 'heima'@'%';

-- 授予用户所有数据库所有表的所有权限
grant all on *.* to 'heima'@'%';

-- 撤销权限(撤销heima用户在itcast数据库上的所有权限)
revoke all on itcast.* from 'heima'@'%';

2.函数

2.1 字符串函数

-- concat函数(字符串拼接)
select concat('hello ','word'); # hello word

-- lower(全部转化成小写)
select lower('Hello'); # hello

-- upper(全部转化成大写)
select upper('Hello'); # HELLO

-- lpad(左侧填充)
select lpad('01',5,'-'); # ---01

-- rpad(右侧填充)
select rpad('01',5,'-') ;# 01---

-- trim 去除头部和尾部的空格
select trim(' Hello Mysql ');# Hello Mysql

-- substring 字符串截取
select substring('Hello Mysql',1,5);# Hello

案例

-- 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0 比如:1号员工的工号应该为00001
update emp set workno = lpad(workno,5,'0');

2.2 数值函数

-- 数值函数
-- ceil 向上取整
select ceil(1.5); # 2

-- floor 向下取整
select floor(1.1); # 1

-- mod 求模运算
select mod(7,4); # 3

-- rand 随机数(0~1)
select rand();

-- round 四舍五入
select round(2.345,2); # 2.35

案例

-- 通过数据库函数生成一个的随机的六位验证码
-- 思路是通过rand()*100生成xxxxxx.xxx的小数,然后通过round四舍五入掉小数位，然后可能存在小于六位数的情况，我们
-- 通过lpd函数补齐六位
select  lpad(round(rand()*1000000,0),6,'0');

2.3 日期函数

-- 日期函数
-- curdate：当前日期
select curdate();

-- curtime：当前时间
select curtime();

-- now：当前日期和时间
select now();

-- YEAR , MONTH , DAY：当前年、月、日
select year(now());
select month(now());
select day(now());

-- date_add：增加指定的时间间隔
select date_add(now(),INTERVAL 70 DAY ); # 当前的时间往后推70天
select date_add(now(),INTERVAL 2 MONTH ); #向后推2个月

-- datediff：获取两个日期相差的天数
select datediff('2021-12-01','2021-10-01'); # 查询两个时间之间的差值，第一个时间减去第二个时间

案例

-- 查询所有员工的入职天数，并根据入职天数倒叙排序
select name,datediff(curdate(),entrydate) 'entrydays' from emp order by entrydays desc ;

2.4 流程函数

-- 流程控制函数
-- if 相当java中的三元运算符
select if(true, 'OK', 'ERROR');

-- ifnull 第一个字符串不为空就返回第一个，第一个为空就返回第二个
select ifnull('OK', 'DEFAULT');
select ifnull(null, 'DEFAULT');

-- case when then else end
-- 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select name,
       (case workaddress
            when '北京' then '一线城市'
            when '上海' then '一线城市'
            else
                '二线城市' end) as '工作地址'
from emp;

案例

-- 案例：统计班级各个学员的成绩，展示规则如下:
-- >= 85 展示优秀
-- >= 60 展示及格
-- 否则展示不及格

-- 建表数据
create table score
(
    id      int comment 'ID',
    name    varchar(20) comment '姓名',
    math    int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese)
VALUES (1, 'Tom', 67, 88, 95),
       (2, 'Rose', 23, 66, 90),
       (3, 'Jack', 56, 98, 76);

select *
from score;

-- 案例sql实现
select id,
       name,
       (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end)
                '数学',
       (case
            when english >= 85 then '优秀'
            when english >= 60 then '及格'
            else '不及格'
           end) '英语',
       (case
            when chinese >= 85 then '优秀'
            when chinese >= 60 then '及格'
            else '不及格'
           end) '语文'
from score;

函数相关的全部sql

-- ----------------------------------------------------------函数--------------------------------------------------------
-- 字符串函数
-- concat函数(字符串拼接)
select concat('hello ', 'word');
# hello word

-- lower(全部转化成小写)
select lower('Hello');
# hello

-- upper(全部转化成大写)
select upper('Hello');
# HELLO

-- lpad(左侧填充)
select lpad('01', 5, '-');
# ---01

-- rpad(右侧填充)
select rpad('01', 5, '-');
# 01---

-- trim 去除头部和尾部的空格
select trim(' Hello Mysql ');
# Hello Mysql

-- substring 字符串截取
select substring('Hello Mysql', 1, 5);
# Hello

-- 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0 比如:1号员工的工号应该为00001
update emp
set workno = lpad(workno, 5, '0');



-- 数值函数
-- ceil 向上取整
select ceil(1.5);
# 2

-- floor 向下取整
select floor(1.1);
# 1

-- mod 求模运算
select mod(7, 4);
# 3

-- rand 随机数(0~1)
select rand();

-- round 四舍五入
select round(2.345, 2);
# 2.35

-- 通过数据库函数生成一个的随机的六位验证码
select lpad(round(rand() * 1000000, 0), 6, '0');


-- 日期函数
-- curdate：当前日期
select curdate();

-- curtime：当前时间
select curtime();

-- now：当前日期和时间
select now();

-- YEAR , MONTH , DAY：当前年、月、日
select year(now());
select month(now());
select day(now());

-- date_add：增加指定的时间间隔
select date_add(now(), INTERVAL 70 DAY); # 当前的时间往后推70天
select date_add(now(), INTERVAL 2 MONTH);
#向后推2个月

-- datediff：获取两个日期相差的天数
select datediff('2021-12-01', '2021-10-01');
# 查询两个时间之间的差值，第一个时间减去第二个时间

-- 查询所有员工的入职天数，并根据入职天数倒叙排序
select name, datediff(curdate(), entrydate) 'entrydays'
from emp
order by entrydays desc;



-- 流程控制函数
-- if 相当java中的三元运算符
select if(true, 'OK', 'ERROR');

-- ifnull 第一个字符串不为空就返回第一个，第一个为空就返回第二个
select ifnull('OK', 'DEFAULT');
select ifnull(null, 'DEFAULT');

-- case when then else end
-- 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select name,
       (case workaddress
            when '北京' then '一线城市'
            when '上海' then '一线城市'
            else
                '二线城市' end) as '工作地址'
from emp;

-- 案例：统计班级各个学员的成绩，展示规则如下:
-- >= 85 展示优秀
-- >= 60 展示及格
-- 否则展示不及格

-- 建表数据
create table score
(
    id      int comment 'ID',
    name    varchar(20) comment '姓名',
    math    int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese)
VALUES (1, 'Tom', 67, 88, 95),
       (2, 'Rose', 23, 66, 90),
       (3, 'Jack', 56, 98, 76);

select *
from score;

-- 案例sql实现
select id,
       name,
       (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end)
                '数学',
       (case
            when english >= 85 then '优秀'
            when english >= 60 then '及格'
            else '不及格'
           end) '英语',
       (case
            when chinese >= 85 then '优秀'
            when chinese >= 60 then '及格'
            else '不及格'
           end) '语文'
from score;

3.约束

3.1 概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

分类 :

3.2 约束演示

案例需求： 根据需求，完成表结构的创建，需求如下：

对应的建表语句为：

create table tb_user(
    id     int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
    name   varchar(10) NOT NULL UNIQUE COMMENT '姓名',
    age    int check (age > 0 && age <= 120) COMMENT '年龄',
    status char(1) default '1' COMMENT '状态',
    gender char(1) COMMENT '性别'
);

3.3 外键约束

3.3.1 介绍

3.3.2 语法

建立外键: ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;

删除外键: ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

举例

-- 外键约束
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';

INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办');

create table employee
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';

INSERT INTO employee (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

select *
from dept;
select *
from employee;

-- 添加外键 员工表employee的部门id字段关联到部门表的id字段以建立外键约束
alter table employee
    add constraint fk_emp_dept foreign key (dept_id) references dept (id);

-- 删除外键
alter table employee drop foreign key fk_emp_dept;

3.3.3 删除/更新行为

详细介绍见PDF文件 https://qingling.icu/posts/50465.html

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种:

具体语法为:

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

约束相关的sql

-- ---------------------------------------------------约束演示------------------------------------------------------------
-- id 主键，并且自动增长
-- name 不为空，并且唯一
-- age 大于零，并且小于等于120（8.0以上的数据库才支持）age    int check (age > 0 && age <= 120) COMMENT '年龄'
-- status 如果没有指定该值，默认为1
-- gender 无约束
drop table if exists user;
create table user
(
    id     int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
    name   varchar(10) NOT NULL UNIQUE COMMENT '姓名',
    age    int COMMENT '年龄',
    status char(1) default '1' COMMENT '状态',
    gender char(1) COMMENT '性别'
);
select *
from user;

-- 插入数据
-- name的值不能重复,status的值没有填写的话，将使用的是默认的值
insert into user(name, age, status, gender)
values ('Tom1', 19, '1', '男'),
       ('Tom2', 29, '1', '男'),
       ('Tom3', 14, '1', '男');


-- 外键约束
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';

INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办');

create table employee
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';

INSERT INTO employee (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

select *
from dept;
select *
from employee;

-- 添加外键 员工表employee的部门id字段关联到部门表的id字段以建立外键约束
alter table employee
    add constraint fk_emp_dept foreign key (dept_id) references dept (id);

-- 删除外键
alter table employee drop foreign key fk_emp_dept;

4.多表查询

4.1 多表关系

一对多

多对多

一对一

-- 多表关系
-- 演示多对多的关系
create table student
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no   varchar(10) comment '学号'
) comment '学生表';

insert into student
values (null, '黛绮丝', '2000100101'),
       (null, '谢逊',
        '2000100102'),
       (null, '殷天正', '2000100103'),
       (null, '韦一笑', '2000100104');

create table course
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';

insert into course
values (null, 'Java'),
       (null, 'PHP'),
       (null, 'MySQL'),
       (null, 'Hadoop');

create table student_course
(
    id        int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid  int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';

insert into student_course
values (null, 1, 1),
       (null, 1, 2),
       (null, 1, 3),
       (null, 2, 2),
       (null, 2, 3),
       (null, 3, 4);


-- 演示一对一的关系
create table tb_user
(
    id     int auto_increment primary key comment '主键ID',
    name   varchar(10) comment '姓名',
    age    int comment '年龄',
    gender char(1) comment '1: 男 , 2: 女',
    phone  char(11) comment '手机号'
) comment '用户基本信息表';
create table tb_user_edu
(
    id            int auto_increment primary key comment '主键ID',
    degree        varchar(20) comment '学历',
    major         varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool  varchar(50) comment '中学',
    university    varchar(50) comment '大学',
    userid        int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user (id)
) comment '用户教育信息表';
insert into tb_user(id, name, age, gender, phone)
values (null, '黄渤', 45, '1', '18800001111'),
       (null, '冰冰', 35, '2', '18800002222'),
       (null, '码云', 55, '1', '18800008888'),
       (null, '李彦宏', 50, '1', '18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
                        university, userid)
values (null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
       (null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
       (null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
       (null, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4);

4.2 多表查询概述

4.2.1 数据准备

-- 数据准备
-- 创建dept表，并插入数据
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4,
        '销售部'),
       (5, '总经办'),
       (6, '人事部');

-- 创建emp表，并插入数据
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';

-- 添加外键
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references
        dept (id);

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
       (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
       (8, '周芷若', 19, '会计', 4800, '2006-06-02', 7, 3),
       (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
       (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
       (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
       (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
       (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
       (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
       (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
       (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
       (17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);

4.2.2 概述

4.2.3 分类

4.3 内连接

-- 内连接演示
-- 案例
-- 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name, d.name from emp e, dept d where e.dept_id = d.id;

-- 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ...ON ...
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name ,d.name from emp e inner join dept d on e.dept_id = d.id;

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

4.4 外连接

-- 外连接
-- 查询emp表的所有数据, 和对应的部门信息
-- 由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id
select e.name,d.name from emp e left join dept d on e.dept_id = d.id;

-- 查询dept表的所有数据, 和对应的员工信息(右外连接)
-- 由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询
-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id
select e.name,d.name from emp e right join dept d on e.dept_id = d.id;

注意事项：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

4.5 自连接

-- 自连接
-- 查询员工 及其 所属领导的名字
-- 表结构: emp
-- 技巧:查询的时候看作是两张表
select e1.name '员工' ,e2.name '领导' from emp e1,emp e2 where e1.managerid = e2.id;

-- 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
-- 表结构: emp a , emp b
select e1.name '员工' ,e2.name '领导' from emp e1 left join  emp e2 on e1.managerid = e2.id;

4.6 联合查询

-- 联合查询
-- 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
-- 当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可,也可以通过union/union all来联合查询
-- 相当于把下面的两条sql的结果拼接在一起(去掉all就可以实现去重)
select * from emp e where e.salary < 5000
union
select * from emp e where e.age > 50;

union all查询出来的结果，仅仅进行简单的合并，并未去重

union 联合查询，会对查询出来的结果进行去重处理

4.7 子查询

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。常用的操作符：= <> > >= < <=

-- 标量子查询
-- 1.查询 "销售部" 的所有员工信息
-- 1.1 查询销售部的部门id
select * from emp  e where e.dept_id = (select id from dept d where d.name = '销售部');

-- 查询在 "方东白" 入职之后的员工信息
select * from emp e1 where e1.entrydate > (select e2.entrydate from emp e2 where e2.name = '方东白');

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

-- 列子查询
-- 查询 "销售部" 和 "市场部" 的所有员工信息
select * from emp e where e.dept_id in (select id from dept where dept.name = '销售部' or dept.name = '市场部');

-- 查询比财务部所有人工资都高的员工信息
select * from emp e where e.salary > all (select e2.salary from emp e2 where e2.dept_id = (select id from dept where dept.name = '财务部'));

-- 查询比研发部其中任意一人工资高的员工信息(使用any或者some均可)
select * from emp e where e.salary > any (select e2.salary from emp e2 where e2.dept_id = (select id from dept where dept.name = '研发部'));

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。常用的操作符：= 、<> 、IN 、NOT IN

-- 行子查询
-- 查询与"张无忌"的薪资及与其直属领导相同的员工信息
select * from emp e2 where (e2.salary,e2.managerid) = (select e.salary,e.managerid from emp e where e.name = '张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。常用的操作符：IN

-- 表子查询
-- 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp e2 where (e2.job,e2.salary) in (select e.job, e.salary from emp e where e.name = '鹿杖客' or name = '宋远桥');

-- 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select * from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

多表查询相关的sql语句

-- ---------------------------------------------多表查询--------------------------------------------------------
-- 多表关系
-- 演示多对多的关系
create table student
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no   varchar(10) comment '学号'
) comment '学生表';

insert into student
values (null, '黛绮丝', '2000100101'),
       (null, '谢逊',
        '2000100102'),
       (null, '殷天正', '2000100103'),
       (null, '韦一笑', '2000100104');

create table course
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';

insert into course
values (null, 'Java'),
       (null, 'PHP'),
       (null, 'MySQL'),
       (null, 'Hadoop');

create table student_course
(
    id        int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid  int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';

insert into student_course
values (null, 1, 1),
       (null, 1, 2),
       (null, 1, 3),
       (null, 2, 2),
       (null, 2, 3),
       (null, 3, 4);


-- 演示一对一的关系
create table tb_user
(
    id     int auto_increment primary key comment '主键ID',
    name   varchar(10) comment '姓名',
    age    int comment '年龄',
    gender char(1) comment '1: 男 , 2: 女',
    phone  char(11) comment '手机号'
) comment '用户基本信息表';
create table tb_user_edu
(
    id            int auto_increment primary key comment '主键ID',
    degree        varchar(20) comment '学历',
    major         varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool  varchar(50) comment '中学',
    university    varchar(50) comment '大学',
    userid        int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user (id)
) comment '用户教育信息表';
insert into tb_user(id, name, age, gender, phone)
values (null, '黄渤', 45, '1', '18800001111'),
       (null, '冰冰', 35, '2', '18800002222'),
       (null, '码云', 55, '1', '18800008888'),
       (null, '李彦宏', 50, '1', '18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
                        university, userid)
values (null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
       (null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
       (null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
       (null, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4);



-- 演示多表查询
-- 数据准备
-- 创建dept表，并插入数据
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4,
        '销售部'),
       (5, '总经办'),
       (6, '人事部');

-- 创建emp表，并插入数据
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';

-- 添加外键
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references
        dept (id);

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
       (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
       (8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),
       (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
       (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
       (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
       (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
       (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
       (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
       (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
       (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
       (17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);

-- 多表查询
select *
from emp,
     dept
where emp.dept_id = dept.id;

-- 内连接演示
-- 案例
-- 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name, d.name from emp e, dept d where e.dept_id = d.id;

-- 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ...ON ...
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name ,d.name from emp e inner join dept d on e.dept_id = d.id;


-- 外连接
-- 查询emp表的所有数据, 和对应的部门信息
-- 由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id
select e.name,d.name from emp e left join dept d on e.dept_id = d.id;

-- 查询dept表的所有数据, 和对应的员工信息(右外连接)
-- 由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询
-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id
select e.name,d.name from emp e right join dept d on e.dept_id = d.id;


-- 自连接
-- 查询员工 及其 所属领导的名字
-- 表结构: emp
-- 技巧:查询的时候看作是两张表
select e1.name '员工' ,e2.name '领导' from emp e1,emp e2 where e1.managerid = e2.id;

-- 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
-- 表结构: emp a , emp b
select e1.name '员工' ,e2.name '领导' from emp e1 left join  emp e2 on e1.managerid = e2.id;




-- 联合查询
-- 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
-- 当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可,也可以通过union/union all来联合查询
-- 相当于把下面的两条sql的结果拼接在一起(去掉all就可以实现去重)
select * from emp e where e.salary < 5000
union
select * from emp e where e.age > 50;



-- 子查询
-- 标量子查询
-- 1.查询 "销售部" 的所有员工信息
-- 1.1 查询销售部的部门id
select * from emp  e where e.dept_id = (select id from dept d where d.name = '销售部');

-- 查询在 "方东白" 入职之后的员工信息
select * from emp e1 where e1.entrydate > (select e2.entrydate from emp e2 where e2.name = '方东白');


-- 列子查询
-- 查询 "销售部" 和 "市场部" 的所有员工信息
select * from emp e where e.dept_id in (select id from dept where dept.name = '销售部' or dept.name = '市场部');

-- 查询比财务部所有人工资都高的员工信息
select * from emp e where e.salary > all (select e2.salary from emp e2 where e2.dept_id = (select id from dept where dept.name = '财务部'));

-- 查询比研发部其中任意一人工资高的员工信息
select * from emp e where e.salary > any (select e2.salary from emp e2 where e2.dept_id = (select id from dept where dept.name = '研发部'));



-- 行子查询
-- 查询与"张无忌"的薪资及与其直属领导相同的员工信息
select * from emp e2 where (e2.salary,e2.managerid) = (select e.salary,e.managerid from emp e where e.name = '张无忌');


-- 表子查询
-- 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp e2 where (e2.job,e2.salary) in (select e.job, e.salary from emp e where e.name = '鹿杖客' or name = '宋远桥');

-- 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select * from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

4.8 多表查询案例

题目

数据准备

-- 数据准备
-- 创建dept表，并插入数据
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4,
        '销售部'),
       (5, '总经办'),
       (6, '人事部');

-- 创建emp表，并插入数据
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';

-- 添加外键
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references
        dept (id);

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
       (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
       (8, '周芷若', 19, '会计', 4800, '2006-06-02', 7, 3),
       (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
       (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
       (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
       (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
       (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
       (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
       (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
       (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
       (17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);

-- 创建salgrade并插入数据
create table salgrade
(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';
insert into salgrade
values (1, 0, 3000);
insert into salgrade
values (2, 3001, 5000);
insert into salgrade
values (3, 5001, 8000);
insert into salgrade
values (4, 8001, 10000);
insert into salgrade
values (5, 10001, 15000);
insert into salgrade
values (6, 15001, 20000);
insert into salgrade
values (7, 20001, 25000);
insert into salgrade
values (8, 25001, 30000);

题解

-- 题解
-- 1.查询员工的姓名、年龄、职位、部门信息（隐式内连接）
select e.name, e.age, e.job, d.name
from emp e,
     dept d
where e.dept_id = d.id;

-- 2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name, e.age, e.job, d.name
from emp e
         inner join dept d on e.dept_id = d.id
where e.age < 30;

-- 3.查询拥有员工的部门id、部门名称
-- 方法一
select distinct d.id, d.name
from emp e,
     dept d
where e.dept_id = d.id;
-- 方法二
select *
from dept
where dept.id in (select e.dept_id from emp e);

-- 4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果没有员工分配部门，也需要展示出来
select e.*, d.name
from emp e
         left join dept d on e.dept_id = d.id
where age > 40;

-- 5.查询所有员工的工资等级
select e.name '姓名', s.grade '工资等级'
from emp e,
     salgrade s
where e.salary between s.losal and s.hisal;

-- 6.查询研发部所有员工的信息已经工资等级
-- 方法一
select e.*, s.grade
from emp e,
     dept d,
     salgrade s
where e.dept_id = d.id
  and (e.salary between s.losal and s.hisal)
  and d.name = '研发部';
-- 方法二
select e.*, s.grade
from (select emp.*
      from emp,
           dept
      where emp.dept_id = dept.id
        and dept.name = '研发部') e,
     salgrade s
where e.salary between s.losal and s.hisal;

-- 7.查询研发部的平均工资
select avg(e.salary)
from emp e,
     dept d
where e.dept_id = d.id
  and d.name = '研发部';

-- 8.查询工资比灭绝高的员工信息
select *
from emp
where salary > (select salary from emp where name = '灭绝');

-- 9.查询比平均工资高的员工信息
select *
from emp
where salary > (select avg(salary) from emp);

-- 10.查询低于本部门平均工资的员工
-- 方法一
select *
from emp e2
where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
-- 方法二
select *
from emp e,
     (select dept_id, avg(salary) 'salavg' from emp group by dept_id) eavg
where eavg.dept_id = e.dept_id
  and e.salary < eavg.salavg;

-- 11.查询所有的部门信息，并统计部门的员工人数
-- 方法一
select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) '人数'
from dept d;
-- 方法二
select *
from dept d,
     (select e.dept_id, count(*) '人数' from emp e group by e.dept_id) ecount
where d.id = ecount.dept_id;

-- 12.查询所有学生的选课情况,展示出学生名称,学号,课程名称
select s.name, s.no, c.name
from course c,
     student s,
     student_course sc
where c.id = sc.courseid
  and s.id = sc.studentid;

总结

5.事务

5.1 事务介绍

​ 事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。就比如: 张三给李四转账1000块钱，张三银行账户的钱减少1000，而李四银行账户的钱要增加1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。

注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。

5.2 事务操作

数据准备

-- 数据准备
drop table if exists account;
create table account
(
    id    int primary key AUTO_INCREMENT comment 'ID',
    name  varchar(10) comment '姓名',
    money double(10, 2) comment '余额'
) comment '账户表';
insert into account(name, money)
VALUES ('张三', 2000),
       ('李四', 2000);
select * from account;



-- 转账操作（张三转1000给李四）
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

控制事务一

控制事务二

-- 开启事务
start transaction;
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;

5.3 事务四大特性

5.4 并发事务问题

5.5 事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别，主要有以下几种：

​ “X” 表示不会出现

-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

-- 设置事务的隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

小结

基础篇全部的Sql

-- ------------------------------------------------------数据库的操作------------------------------------------------------------------------
-- 展示所有的数据库
show databases;

-- 查询当前的数据库
select database();

-- 创建数据库
create database if not exists itcast;

-- 删除数据库
drop database if exists itcast;

-- 使用数据库
use itcast;


-- ----------------------------------------------------------表的操作--------------------------------------------------------------------

-- 创建用户表
create table tb_user
(
    id     int comment '编号',
    name   varchar(50) comment '姓名',
    age    int comment '年龄',
    gender varchar(1) comment '性别'
) comment '用户表';

-- 显示创建表的结构
desc tb_user;

-- 显示创建表的语句
show create table tb_user;

-- 删除表
drop table emp;

-- 修改表的操作
-- 举例：现有一张员工表
create table emp
(
    id        int comment '编号',
    workno    varchar(10) comment '工号',
    name      varchar(10) comment '姓名',
    gender    char(1) comment '性别',
    age       tinyint unsigned comment '年龄',
    idcard    char(18) comment '身份证号',
    entrydate date comment '入职时间'
) comment '员工表';
-- 查看表结构
desc emp;

-- 修改表结构(向emp表中添加昵称字段)
alter table emp
    add nickname varchar(20) comment '昵称';

-- 修改相应字段的数据类型(把age的数据类型从int改回tinyint)
alter table emp
    modify age tinyint;

-- 修改字段名和字段的类型(把nickname字段改为username,并修改数据类型)
alter table emp
    change nickname username varchar(30) comment '用户名';

-- 删除字段(删除username字段)
alter table emp
    drop username;

-- 修改表名(将表名字从emo修改为employee)
alter table emp rename to employee;

-- 删除表
drop table if exists employee;
-- 删除指定表，并重新创建该表(相当于删除表中的全部数据)
truncate table employee;



-- ------------------------------------------------DML-增删改操作---------------------------------------------------
-- 添加数据
select database();
show tables;
-- 该指定的字段添加数据
insert into employee(id, workno, name, gender, age, idcard, entrydate) value (1, '1', '小华', '男', 20, '123456789123456789', '2001-01-01');

select *
from employee;
-- 给全部的字段添加数据
insert into employee value (2, '2', '小刚', '男', 23, '123456784124456789', '2008-01-01');
-- 批量添加数据
insert into employee
values (3, '3', '小李', '男', 23, '123456784124456789', '2008-01-01'),
       (4, '4', '小黄', '男', 23, '123456784124456789', '2008-01-01');

-- 修改数据（不带条件修改所有）
update employee
set name = '张三'
where id = 1;
update employee
set name  = '李四',
    gender= '女'
where id = 1;

-- 删除数据(删除id为4的数据，不带添加删除所有）
delete
from employee
where id = 4;


-- --------------------------------------------------------DQL数据查询语言--------------------------------------------------
-- 数据准备
create table emp
(
    id          int comment '编号',
    workno      varchar(10) comment '工号',
    name        varchar(10) comment '姓名',
    gender      char(1) comment '性别',
    age         tinyint unsigned comment '年龄',
    idcard      char(18) comment '身份证号',
    workaddress varchar(50) comment '工作地址',
    entrydate   date comment '入职时间'
) comment '员工表';

--  插入数据
insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate)
values (1, '1', '柳岩', '女', 20, '12345678912345678', '北京', '2001-01-01'),
       (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
       (3, '3', '韦一笑', '男', 38, '12345678972345670', '上海', '2005-08-01'),
       (4, '4', '赵敏', '女', 18, '12345675712345670', '北京', '2009-12-01'),
       (5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),
       (6, '6', '杨逍', '男', 28, '1234567893123456X', '北京', '2006-01-01'),
       (7, '7', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01'),
       (8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),
       (9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),
       (10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
       (11, '11', '张士诚', '男', 55, '12356789712345670', '江苏', '2015-05-01'),
       (12, '12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),
       (13, '13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),
       (14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),
       (15, '15', '胡青年', '男', 70, '12345674971234567X', '西安', '2018-04-01'),
       (16, '16', '周芷若', '女', 18, null, '北京', '2012-06-01');

-- 基础查询
-- 查看插入的数据
select *
from emp;

-- 查询指定字段 name workno age 返回
select name, workno, age
from emp;

-- 查询所有字段返回
select id,
       workno,
       name,
       gender,
       age,
       idcard,
       workaddress,
       entrydate
from emp;

-- distinct去重关键字
-- 查询所有员工的工作地址(不要重复的地址)
select distinct workaddress '工作地址'
from emp;



-- 条件查询
-- 查询年龄等于 88 的员工
select *
from emp
where age = 88;

-- 查询年龄小于20的员工信息
select *
from emp
where age < 20;

-- 查询年龄小于等于20的员工信息
select *
from emp
where age <= 20;

-- 查询没有身份证号的员工信息
select *
from emp
where idcard is null;

-- 查询年龄不等于88的员工信息(<>也表示不等于)
select *
from emp
where age != 88;
select *
from emp
where age <> 88;

-- 查询年龄在15~20(包含)之间的员工信息（三种实现方式）
select *
from emp
where age >= 15 && age <= 20;
select *
from emp
where age >= 15
  and age <= 20;
select *
from emp
where age between 15 and 20;
#包含了15和20

-- 查询性别为女 且年龄小于25岁的员工
select *
from emp
where gender = '女'
  and age < 25;

-- 查询年龄等于18 或 20 或 48 的员工
select *
from emp
where age = 18
   or age = 20
   or age = 48;
select *
from emp
where age in (18, 20, 48);

-- 查询姓名为两个字的员工(模糊匹配(_匹配单个字符, %匹配任意个字符))
select *
from emp
where name like '__';

-- 查询身份证号最后一位是X的员工信息
select *
from emp
where idcard like '%X';



-- 聚合函数
-- 统计该企业员工数量(空值是不参与统计的)
select count(*)
from emp;
select count(idcard)
from emp;

-- 统计该企业员工的平均年龄
select avg(age)
from emp;

-- 统计该企业员工的最大年龄
select max(age)
from emp;

-- 统计该企业员工的最小年龄
select min(age)
from emp;

-- 统计西安地区员工的年龄之和
select sum(age)
from emp
where workaddress = '西安';



-- 分组查询
-- 根据性别分组，统计男性员工和女性员工的数量
select gender, count(*)
from emp
group by gender;

-- 根据性别进行分组，统计男性员工和女性员工的平均年龄
select gender, avg(age)
from emp
group by gender;

-- 查询年龄小于45岁的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址(可以使用别名也可以不使用别名)
select workaddress, count(*) address_count
from emp
where age < 45
group by workaddress
having address_count >= 3;



-- 排序查询
-- 根据年龄对公司的员工进行升序排序(asc可以省略)
select *
from emp
order by age;
-- 降序排序
select *
from emp
order by age desc;

-- 根据员工的入职时间降序排序
select *
from emp
order by entrydate desc;

-- 根据员工的年龄升序排序，年龄相同，再按照入职时间进行降序排序
select *
from emp
order by age asc, entrydate desc;



-- 分页查询
-- 查询第一页的员工数据，每页显示10条记录
select *
from emp
limit 0,10;

-- 查询第二页数据，每页展示十条数据(起始索引 = （查询页码 - 1）* 每页显示记录数)
select *
from emp
limit 10,10;


-- DQL语句案例
-- 查询年龄在20,21,22,23岁的女性员工的信息
select *
from emp
where gender = '女'
  and age in (20, 21, 22, 23);

-- 查询性别为男，并且年龄再20~40岁(含)以内的姓名为三个字的员工
select *
from emp
where gender = '男'
  and (age between 20 and 40)
  and name like '___';

-- 统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
select gender, count(*)
from emp
where age < 60
group by gender;

-- 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按照入职时间降序排序
select name, age
from emp
where age <= 35
order by age asc, entrydate desc;

-- 查询性别为男,且年龄在20·40岁(含)以内的前五个员工信息，并对查询结果按年龄升序排序，如果年龄相同按照入职时间升序排序
select *
from emp
where gender = '男'
  and age between 20 and 40
order by age, entrydate
limit 5;



-- ---------------------------------------------------DCL--------------------------------------------------
-- 用户管理
-- mysql数据库
use mysql;

-- 查询所有的用户
select *
from mysql.user;

-- 创建用户itcast, 只能够在当前主机localhost访问, 密码123456;
create user 'itcast'@'localhost' identified by '123456';

-- 创建用户heima, 可以在任意主机访问该数据库, 密码123456;
create user 'heima'@'%' identified by '123456';

-- 修改用户heima的访问密码为1234;
alter user 'heima'@'%' identified with mysql_native_password by '1234';

-- 删除 itcast@localhost 用户
drop user 'itcast'@'localhost';



-- 权限控制
-- 查询权限(查询heima用户的所有权限)
show grants for 'heima'@'%';

-- 授予权限(授予heima用户对数据库itcast的所有权限)
grant all on itcast.* to 'heima'@'%';

-- 授予用户所有数据库所有表的所有权限
grant all on *.* to 'heima'@'%';

-- 撤销权限(撤销heima用户在itcast数据库上的所有权限)
revoke all on itcast.* from 'heima'@'%';



-- ----------------------------------------------------------函数--------------------------------------------------------
-- 字符串函数
-- concat函数(字符串拼接)
select concat('hello ', 'word');
# hello word

-- lower(全部转化成小写)
select lower('Hello');
# hello

-- upper(全部转化成大写)
select upper('Hello');
# HELLO

-- lpad(左侧填充)
select lpad('01', 5, '-');
# ---01

-- rpad(右侧填充)
select rpad('01', 5, '-');
# 01---

-- trim 去除头部和尾部的空格
select trim(' Hello Mysql ');
# Hello Mysql

-- substring 字符串截取
select substring('Hello Mysql', 1, 5);
# Hello

-- 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0 比如:1号员工的工号应该为00001
update emp
set workno = lpad(workno, 5, '0');



-- 数值函数
-- ceil 向上取整
select ceil(1.5);
# 2

-- floor 向下取整
select floor(1.1);
# 1

-- mod 求模运算
select mod(7, 4);
# 3

-- rand 随机数(0~1)
select rand();

-- round 四舍五入
select round(2.345, 2);
# 2.35

-- 通过数据库函数生成一个的随机的六位验证码
select lpad(round(rand() * 1000000, 0), 6, '0');


-- 日期函数
-- curdate：当前日期
select curdate();

-- curtime：当前时间
select curtime();

-- now：当前日期和时间
select now();

-- YEAR , MONTH , DAY：当前年、月、日
select year(now());
select month(now());
select day(now());

-- date_add：增加指定的时间间隔
select date_add(now(), INTERVAL 70 DAY); # 当前的时间往后推70天
select date_add(now(), INTERVAL 2 MONTH);
#向后推2个月

-- datediff：获取两个日期相差的天数
select datediff('2021-12-01', '2021-10-01');
# 查询两个时间之间的差值，第一个时间减去第二个时间

-- 查询所有员工的入职天数，并根据入职天数倒叙排序
select name, datediff(curdate(), entrydate) 'entrydays'
from emp
order by entrydays desc;



-- 流程控制函数
-- if 相当java中的三元运算符
select if(true, 'OK', 'ERROR');

-- ifnull 第一个字符串不为空就返回第一个，第一个为空就返回第二个
select ifnull('OK', 'DEFAULT');
select ifnull(null, 'DEFAULT');

-- case when then else end
-- 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select name,
       (case workaddress
            when '北京' then '一线城市'
            when '上海' then '一线城市'
            else
                '二线城市' end) as '工作地址'
from emp;

-- 案例：统计班级各个学员的成绩，展示规则如下:
-- >= 85 展示优秀
-- >= 60 展示及格
-- 否则展示不及格

-- 建表数据
create table score
(
    id      int comment 'ID',
    name    varchar(20) comment '姓名',
    math    int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese)
VALUES (1, 'Tom', 67, 88, 95),
       (2, 'Rose', 23, 66, 90),
       (3, 'Jack', 56, 98, 76);

select *
from score;

-- 案例sql实现
select id,
       name,
       (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end)
                '数学',
       (case
            when english >= 85 then '优秀'
            when english >= 60 then '及格'
            else '不及格'
           end) '英语',
       (case
            when chinese >= 85 then '优秀'
            when chinese >= 60 then '及格'
            else '不及格'
           end) '语文'
from score;



-- ---------------------------------------------------约束演示------------------------------------------------------------
-- id 主键，并且自动增长
-- name 不为空，并且唯一
-- age 大于零，并且小于等于120（8.0以上的数据库才支持）age    int check (age > 0 && age <= 120) COMMENT '年龄'
-- status 如果没有指定该值，默认为1
-- gender 无约束
drop table if exists user;
create table user
(
    id     int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
    name   varchar(10) NOT NULL UNIQUE COMMENT '姓名',
    age    int COMMENT '年龄',
    status char(1) default '1' COMMENT '状态',
    gender char(1) COMMENT '性别'
);
select *
from user;

-- 插入数据
-- name的值不能重复,status的值没有填写的话，将使用的是默认的值
insert into user(name, age, status, gender)
values ('Tom1', 19, '1', '男'),
       ('Tom2', 29, '1', '男'),
       ('Tom3', 14, '1', '男');


-- 外键约束
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';

INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办');

create table employee
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';

INSERT INTO employee (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

select *
from dept;
select *
from employee;

-- 添加外键 员工表employee的部门id字段关联到部门表的id字段以建立外键约束
alter table employee
    add constraint fk_emp_dept foreign key (dept_id) references dept (id);

-- 删除外键
alter table employee
    drop foreign key fk_emp_dept;



-- ---------------------------------------------多表查询--------------------------------------------------------
-- 多表关系
-- 演示多对多的关系
create table student
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no   varchar(10) comment '学号'
) comment '学生表';

insert into student
values (null, '黛绮丝', '2000100101'),
       (null, '谢逊',
        '2000100102'),
       (null, '殷天正', '2000100103'),
       (null, '韦一笑', '2000100104');

create table course
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';

insert into course
values (null, 'Java'),
       (null, 'PHP'),
       (null, 'MySQL'),
       (null, 'Hadoop');

create table student_course
(
    id        int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid  int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';

insert into student_course
values (null, 1, 1),
       (null, 1, 2),
       (null, 1, 3),
       (null, 2, 2),
       (null, 2, 3),
       (null, 3, 4);


-- 演示一对一的关系
create table tb_user
(
    id     int auto_increment primary key comment '主键ID',
    name   varchar(10) comment '姓名',
    age    int comment '年龄',
    gender char(1) comment '1: 男 , 2: 女',
    phone  char(11) comment '手机号'
) comment '用户基本信息表';
create table tb_user_edu
(
    id            int auto_increment primary key comment '主键ID',
    degree        varchar(20) comment '学历',
    major         varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool  varchar(50) comment '中学',
    university    varchar(50) comment '大学',
    userid        int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user (id)
) comment '用户教育信息表';
insert into tb_user(id, name, age, gender, phone)
values (null, '黄渤', 45, '1', '18800001111'),
       (null, '冰冰', 35, '2', '18800002222'),
       (null, '码云', 55, '1', '18800008888'),
       (null, '李彦宏', 50, '1', '18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
                        university, userid)
values (null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
       (null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
       (null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
       (null, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4);



-- 演示多表查询
-- 数据准备
-- 创建dept表，并插入数据
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4,
        '销售部'),
       (5, '总经办'),
       (6, '人事部');

-- 创建emp表，并插入数据
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';

-- 添加外键
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references
        dept (id);

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
       (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
       (8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),
       (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
       (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
       (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
       (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
       (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
       (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
       (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
       (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
       (17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);

-- 多表查询
select *
from emp,
     dept
where emp.dept_id = dept.id;

-- 内连接演示
-- 案例
-- 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name, d.name
from emp e,
     dept d
where e.dept_id = d.id;

-- 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ...ON ...
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name, d.name
from emp e
         inner join dept d on e.dept_id = d.id;


-- 外连接
-- 查询emp表的所有数据, 和对应的部门信息
-- 由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id
select e.name, d.name
from emp e
         left join dept d on e.dept_id = d.id;

-- 查询dept表的所有数据, 和对应的员工信息(右外连接)
-- 由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询
-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id
select e.name, d.name
from emp e
         right join dept d on e.dept_id = d.id;


-- 自连接
-- 查询员工 及其 所属领导的名字
-- 表结构: emp
-- 技巧:查询的时候看作是两张表
select e1.name '员工', e2.name '领导'
from emp e1,
     emp e2
where e1.managerid = e2.id;

-- 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
-- 表结构: emp a , emp b
select e1.name '员工', e2.name '领导'
from emp e1
         left join emp e2 on e1.managerid = e2.id;



-- 联合查询
-- 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
-- 当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可,也可以通过union/union all来联合查询
-- 相当于把下面的两条sql的结果拼接在一起(去掉all就可以实现去重)
select *
from emp e
where e.salary < 5000
union
select *
from emp e
where e.age > 50;



-- 子查询
-- 标量子查询
-- 1.查询 "销售部" 的所有员工信息
-- 1.1 查询销售部的部门id
select *
from emp e
where e.dept_id = (select id from dept d where d.name = '销售部');

-- 查询在 "方东白" 入职之后的员工信息
select *
from emp e1
where e1.entrydate > (select e2.entrydate from emp e2 where e2.name = '方东白');


-- 列子查询
-- 查询 "销售部" 和 "市场部" 的所有员工信息
select *
from emp e
where e.dept_id in (select id from dept where dept.name = '销售部' or dept.name = '市场部');

-- 查询比财务部所有人工资都高的员工信息
select *
from emp e
where e.salary > all (select e2.salary from emp e2 where e2.dept_id = (select id from dept where dept.name = '财务部'));

-- 查询比研发部其中任意一人工资高的员工信息
select *
from emp e
where e.salary > any (select e2.salary from emp e2 where e2.dept_id = (select id from dept where dept.name = '研发部'));



-- 行子查询
-- 查询与"张无忌"的薪资及与其直属领导相同的员工信息
select *
from emp e2
where (e2.salary, e2.managerid) = (select e.salary, e.managerid from emp e where e.name = '张无忌');


-- 表子查询
-- 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select *
from emp e2
where (e2.job, e2.salary) in (select e.job, e.salary from emp e where e.name = '鹿杖客' or name = '宋远桥');

-- 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select *
from (select * from emp where entrydate > '2006-01-01') e
         left join dept d on e.dept_id = d.id;


-- 多表查询案例
-- 数据准备
create table salgrade
(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';
insert into salgrade
values (1, 0, 3000);
insert into salgrade
values (2, 3001, 5000);
insert into salgrade
values (3, 5001, 8000);
insert into salgrade
values (4, 8001, 10000);
insert into salgrade
values (5, 10001, 15000);
insert into salgrade
values (6, 15001, 20000);
insert into salgrade
values (7, 20001, 25000);
insert into salgrade
values (8, 25001, 30000);

-- 题解
-- 1.查询员工的姓名、年龄、职位、部门信息（隐式内连接）
select e.name, e.age, e.job, d.name
from emp e,
     dept d
where e.dept_id = d.id;

-- 2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name, e.age, e.job, d.name
from emp e
         inner join dept d on e.dept_id = d.id
where e.age < 30;

-- 3.查询拥有员工的部门id、部门名称
-- 方法一
select distinct d.id, d.name
from emp e,
     dept d
where e.dept_id = d.id;
-- 方法二
select *
from dept
where dept.id in (select e.dept_id from emp e);

-- 4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果没有员工分配部门，也需要展示出来
select e.*, d.name
from emp e
         left join dept d on e.dept_id = d.id
where age > 40;

-- 5.查询所有员工的工资等级
select e.name '姓名', s.grade '工资等级'
from emp e,
     salgrade s
where e.salary between s.losal and s.hisal;

-- 6.查询研发部所有员工的信息已经工资等级
-- 方法一
select e.*, s.grade
from emp e,
     dept d,
     salgrade s
where e.dept_id = d.id
  and (e.salary between s.losal and s.hisal)
  and d.name = '研发部';
-- 方法二
select e.*, s.grade
from (select emp.*
      from emp,
           dept
      where emp.dept_id = dept.id
        and dept.name = '研发部') e,
     salgrade s
where e.salary between s.losal and s.hisal;

-- 7.查询研发部的平均工资
select avg(e.salary)
from emp e,
     dept d
where e.dept_id = d.id
  and d.name = '研发部';

-- 8.查询工资比灭绝高的员工信息
select *
from emp
where salary > (select salary from emp where name = '灭绝');

-- 9.查询比平均工资高的员工信息
select *
from emp
where salary > (select avg(salary) from emp);

-- 10.查询低于本部门平均工资的员工
-- 方法一
select *
from emp e2
where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
-- 方法二
select *
from emp e,
     (select dept_id, avg(salary) 'salavg' from emp group by dept_id) eavg
where eavg.dept_id = e.dept_id
  and e.salary < eavg.salavg;

-- 11.查询所有的部门信息，并统计部门的员工人数
-- 方法一
select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) '人数'
from dept d;
-- 方法二
select *
from dept d,
     (select e.dept_id, count(*) '人数' from emp e group by e.dept_id) ecount
where d.id = ecount.dept_id;

-- 12.查询所有学生的选课情况,展示出学生名称,学号,课程名称
select s.name, s.no, c.name
from course c,
     student s,
     student_course sc
where c.id = sc.courseid
  and s.id = sc.studentid;


-- ----------------------------------------------------------事务--------------------------------------------------------------------
-- 数据准备
drop table if exists account;
create table account
(
    id    int primary key AUTO_INCREMENT comment 'ID',
    name  varchar(10) comment '姓名',
    money double(10, 2) comment '余额'
) comment '账户表';
insert into account(name, money)
VALUES ('张三', 2000),
       ('李四', 2000);
select * from account;



-- 转账操作（张三转1000给李四）
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';



-- 开启事务
start transaction;
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;

二.MySql进阶

1.存储引擎

1.1 MySql的体系结构

1.2 存储引擎简介

简介

​ 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是 基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果 没有指定将自动选择默认的存储引擎。

查询一张数据库表的建表语句

-- 查询建表语句
show create table book_info;

建表的时候指定存储引擎的语句

1.3 存储引擎特点

存储引擎的区别

1.4 存储引擎的选择

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

• InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要 求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操 作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完 整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。(例如评论的数据、日志相关的数据、电商中足迹相关的数据)
• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是 对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

一句话：项目中绝大多数的时候使用的都是InnoDB

2.索引

2.1 索引概述

2.1.1 介绍

​ 索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2.1.2 演示

注：下面的二叉树并不是一个真实的索引结构，而是一个举例

2.1.3 优缺点

2.2 索引结构

2.2.1 索引的数据结构有哪些

默认的索引结构是B+树索引结构

不同类型的存储引擎对不同的索引结构的支持情况

2.2.2 B+树的索引数据结构

二叉树和红黑树的不足

B树

B+树

MySql中的B+树结构

2.2.3 hash的索引数据结构

2.3.4 思考题

2.3 索引分类

当我们要查询name值是Arm的数据的时候，会先通过二级索引查询，然后通过聚集索引查询这一行的信息（回表查询）

思考题

第一条的执行效率高，第二条要回表查询（先查询name所在行对应的主键id,然后通过主键id查询这一行的数据）

2.4 索引语法

创建|查看|删除语法

举例

2.5 SQL性能分析

2.5.1 SQL执行频率

-- 查看系统的状态信息
show global status like 'Com_______';

2.5.2 慢查询日志

-- 查询慢查询日志是否开启
show variables like 'slow_query_log';

2.5.3 profile详情

-- 是否支持profile详情功能
select @@have_profiling;

-- 查看是否开启该功能
select @@profiling;

-- 开启profile
set profiling = 1;

2.5.4 explain执行计划

2.6 索引使用

2.6.1 验证索引效率

现在有一个有着10000000数据的表（tb_sku），我们根据sn这一字段查询某一条具体的数据，没有建立索引之前的查询耗费的时间是20多秒，建立索引耗费大约90秒，为sn字段建立索引之后，查询耗费时间大概是零点几秒。

2.6.2 最左前缀法则

2.6.3 索引失效的情况

2.6.4 SQL提示

2.6.5 覆盖索引

2.6.6 前缀索引

2.6.7 单例索引与联合索引的选择问题

2.7 索引设计原则

3.SQL优化

3.1 插入数据的优化

100万的数据如果使用insert插入的话需要10分钟左右，如果使用文件的方式导入，只需要10多秒。

3.2 主键优化

3.3 order by优化

3.4 group by优化

3.5 limit优化

3.6 count优化

3.7 update优化

3.8 总结

4.视图/存储过程/触发器

4.1 视图

4.1.1 视图介绍

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

4.1.2 语法

创建

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ]

查询

查看创建视图语句：SHOW CREATE VIEW 视图名称;
查看视图数据：SELECT * FROM 视图名称 ...... ;

修改

方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

删除

DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

演示示例

-- 视图
-- 创建视图
create or replace view stu_v_1 as select id, name from student where id <= 10;

-- 查看创建视图的语句
show create view stu_v_1;

-- 查看视图数据
select * from stu_v_1;
-- 也可以加上条件
select * from stu_v_1 where id = 1;

-- 修改视图的方式(or replace可以实现修改效果)
-- 方式一
create or replace view stu_v_1 as select id, name, no from student where id <= 10;
-- 方式二
alter view stu_v_1 as select id, name from student where id <= 10;

-- 删除视图
drop view if exists  stu_v_1;

-- 通过视图实现插入操作
create or replace view stu_v_1 as select id, name from student where id <= 20;

-- 向视图中添加数据(实际上插入的数据插入到原table表中)
insert into stu_v_1 values(6,'Tom');

4.1.3 检查选项

​ 当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL，默认值为 CASCADED 。

1.CASCADED 级联

​ 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

2.LOCAL 本地

​ 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，知会检查v2，不会检查v2的关联视图v1。

-- 检查选项
-- 添加了检查选项，在插入数据的时候会检查插入的数据是否满足视图创建的条件(创建语句为create or replace view stu_v_1 as select id, name from student where id <= 20;此时插入的数据id要<=20,否则报错)
-- cascaded:插入时会检查是否满足当前视图和依赖的视图的条件
create or replace view stu_v_1 as select id, name from student where id <= 20 with cascaded check option ;

-- local:插入时会检查是否满足当前视图和依赖的视图(定义了检查选项的视图)的条件
create or replace view stu_v_1 as select id, name from student where id <= 20 with local check option ;

-- local和cascaded的区别:
-- local:v2依赖了v1,只有v1也加上了with local check option检查选项,在v2插入数据的时候才会检查v1的条件
-- cascaded:v2依赖了v1,不管v1有没有加上检查选项,在v2插入数据时都会检查v1的条件

-- 重点
-- 1.加不加检查选项的区别
-- 2.with cascaded check option和with local check option的区别

4.1.4 视图更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）

B. DISTINCT

C. GROUP BY

D. HAVING

E. UNION 或者 UNION ALL

4.1.5 视图作用

4.1.6 视图案例

tb_user表结构以及sql语句

create table tb_user(
	id int primary key auto_increment comment '主键',
	name varchar(50) not null comment '用户名',
	phone varchar(11) not null comment '手机号',
	email varchar(100) comment '邮箱',
	profession varchar(11) comment '专业',
	age tinyint unsigned comment '年龄',
	gender char(1) comment '性别 , 1: 男, 2: 女',
	status char(1) comment '状态',
	createtime datetime comment '创建时间'
) comment '系统用户表';


INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');

案例

-- 视图案例
-- 为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段
create view tb_user_view as select id, name, profession, age, gender, status, createtime from tb_user;
select * from tb_user_view;

-- 查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图
create view tb_stu_course_view as select s.name student_name,s.no,c.name course_name from student s, course c, student_course sc where s.id = sc.studentid and c.id = sc.courseid;

4.2 存储过程

4.2.1 介绍

4.2.2 基本语法

创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
	-- SQL语句
END ;

调用

CALL 名称 ([ 参数 ]);

查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指定数据库的存储过程及状态信息

SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ；

注意:
在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符

演示示例

-- 存储过程
-- 创建存储过程
create procedure p1()
begin
    select count(*) from student;
end;

-- 调用
call p1();

-- 查看
-- 查询指定数据库的存储过程及状态信息
-- 查询itcast数据库中定义的存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE p1 ;

-- 删除
drop procedure if exists p1;

4.2.3 变量

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量

4.2.3.1 系统变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话 变量（SESSION）

1.查看系统变量

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

2.设置系统变量

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;

注意:

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量

1 mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置

​ A. 全局变量(GLOBAL): 全局变量针对于所有的会话

​ B. 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了

演示示例

-- 系统变量
-- 查看所有系统变量
show  session variables ; #查看会话的系统变量
show global variables ; #查看全局的系统变量
-- 查看以auto开头的系统变量
show variables like 'auto%';
-- 查看某一个具体的环境变量值（以autocommit为例）
select @@autocommit; #会话的
select @@global.autocommit; #全局的

-- 设置系统变量（以autocommit为例）
set session autocommit = 1; #会话级别的
set global autocommit  = 1; #全局级别的
-- 或者
set @@autocommit = 1;
set @@global.autocommit = 1;

4.2.3.2 用户定义变量

用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量 名” 使用就可以。其作用域为当前连接

1.赋值

方式一：

SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;

赋值时，可以使用 = ，也可以使用 :=

方式二:

SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;

2.使用

SELECT @var_name ;

注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL

演示示例

-- 用户定义变量
-- 赋值
-- 方式一
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby = 'java';
-- 方式二
select @mycolor := 'red';
select count(*) into @mycount from tb_user; #将tb_user表中的总记录数赋值给@mycount

-- 使用
select @myname,@myage,@mygender,@myhobby,@mycolor,@mycount;

4.2.3.3 局部变量

局部变量 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的 局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块

1**.声明**

DECLARE 变量名 变量类型 [DEFAULT ... ] ;

变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等

2.赋值

SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;

演示示例

-- 局部变量
-- 创建存储过程
create procedure p2()
begin
    # 定义一个int类型的局部变量,默认值是0
    declare stu_count int default 0;
    # 给stu_count赋值
    -- set stu_count = 100;
    select count(*) into stu_count from tb_user;
    #输出
    select stu_count;
end;
-- 调用函数
call p2();

4.2.4 if判断

介绍

if 用于做条件判断，具体的语法结构为：

IF 条件1 THEN
	.....
ELSEIF 条件2 THEN -- 可选
	.....
ELSE -- 可选
	.....
END IF;

在if条件判断的结构中，ELSE IF 结构可以有多个，也可以没有. ELSE结构可以有，也可以没有.

案例

根据定义的分数score变量，判定当前分数对应的分数等级

• score >= 85分，等级为优秀

• score >= 60分 且 score < 85分，等级为及格

• score < 60分，等级为不及格

-- if判断
-- 根据定义的分数score变量，判定当前分数对应的分数等级
-- score >= 85分，等级为优秀
-- score >= 60分 且 score < 85分，等级为及格
-- score < 60分，等级为不及格
drop procedure p3;
create procedure p3()
begin
    # 先给一个默认值 58
    declare score int default 58;
    declare result varchar(10);
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
    #输出
    select result;
end ;
-- 调用
call p3();

4.2.5 参数

介绍

参数的类型，主要分为以下三种：IN、OUT、INOUT. 具体的含义如下：

语法

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
	-- SQL语句
END ;

案例

1.根据传入的分数score变量，判定当前分数对应的分数等级，并返回

• score >= 85分，等级为优秀

• score >= 60分 且 score < 85分，等级为及格

• score < 60分，等级为不及格

-- 参数
-- 1.根据传入的分数score变量，判定当前分数对应的分数等级，并返回
-- score >= 85分，等级为优秀
-- score >= 60分 且 score < 85分，等级为及格
-- score < 60分，等级为不及格
create procedure p4(in score int, out result varchar(10))
begin
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end ;
-- 调用
call p4(88,@result);
-- 查看结果
select @result;

2.将传入的200分制的分数，进行换算，换成百分制，然后返回

-- 2.将传入的200分制的分数，进行换算，换成百分制，然后返回
create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;
-- 调用(score既是输入又是输出)
set @score = 78;
call p5(@score);
select @score;

4.2.6 case