MySQL个人数据库学习笔记(黑马课程学习)
目录
二、操作数据表（CRUD,Create创建、Retrieve查询、Update修改、Delete删除）
1.CASE WHEN判断函数：用于计算条件列表并返回多个结果表达式之一
1.基本使用：开发中通常每张表必须有一个主键字段（提高查询效率，和外键建立表关系）
2.主键自增：数据过多时自己手动在主键字段下插入数据时不确定是否重复，开发中主键列由MySQL管理(程序员不干预由MySQL自主完成插入)
1.作用：约束外键字段下的数据和主表主键下的数据保持一致（一致性、完整性）
2.多行单列：子查询结果是多行单列，可认为结果一个数组，父查询使用in、any、all关键字
3.多行多列：子查询结果是多行多列，查询结果可以当作一张虚拟表，可以使用表连接再次进行查询
声明：该篇文章作为笔记分享是个人在学习MySQL数据时做的笔记，用意是和大家一起共同进步和讨论，如果笔记出现错误大家可以指出、哪些地方有提升也希望大佬能指导，该笔记是从语雀重新写入到博客，部分格式和内容存在偷懒省略也请大家原谅(如果有好的办法从语雀导入博客方法，望请大佬分享)
SQL语言基础
>DDL数据库定义语言
作用：操作数据库和表
一、操作数据库
1.查询数据库
SHOW DATABAASES;//显示所有数据库
2.创建数据库
Ⅰ.直接创建：CREATE DATABASE 数据库名称;
Ⅱ.判断，不存在则创建
CREATE DATABASE IF NOT EXISTS 数据库名称;
3.删除数据库
Ⅰ.直接删除
DROP DATABASE 数据库名称
Ⅱ.判断，存在则删除
DROP DATABASE IF EXISTS 数据库名称;
4.使用数据库
Ⅰ.查看当前使用的数据库
SELECT DATABASE();
Ⅱ.使用/切换数据库
USE 数据库名称;
二、操作数据表（CRUD,Create创建、Retrieve查询、Update修改、Delete删除）
1.查询表
Ⅰ.查看当前数据库所有表:
SHOW TABLES;//可以看到当前操作的数据库所有表名字
Ⅱ.查看表结构：查看指定表的内容结构
DESC 表名称;
2.创建表
//扩展：将一个数据库1存在的表在另一个数据库2创建一个一样的数据表，根据查询的结果创建新表
CREATE TABLE 数据库2.表名2 AS SELECT * FROM 数据库1.表名1;
Ⅰ.直接创建表
-- 将bd1中的tb_stu表在db2中创建一个一样的
CREATE TABLE db2.student AS SELECT * FROM db1.tb_stu;
-- 创建一个新表
CREATE TABLE 表名(
字段名1 数据类型1,
字段名2 数据类型2,
...
字段名n 数据类型n -- 最后一行不要逗号
);基本数据类型关键字：
int<整型>、double<浮点型>、char(int num字符串长度)<固定长度的字符串>、varchar(int num字符串长度)<可变长度字符串>、date<日期类型，格式为yyyy-MM-dd，只有年月日没有时分秒，datetime有时分秒>
//字符串与时间类型都是用‘ ’单引号，例：‘这是一个字符串’‘2025-12-12’
//固定长度的意思是：当固定长度为2时只填一个字符时会自动把剩下空补上空格
《案例》
-- 创建一个学生信息表
CREATE TABLE student(
number int, -- 编号
name varchar(10), -- 姓名
gender char(2), -- 性别
birthday date, -- 生日
grade double, -- 成绩
email varchar(64), -- 邮件
phone varchar(20), -- 电话
status int -- 状态
);3.修改表
Ⅰ.修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
Ⅱ.新增一列
ALTER TABLE 表名 ADD 列名 数据类型;
Ⅲ.修改表中某列的数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
Ⅳ.修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
Ⅴ.删除列
ALTER TABLE 表名 DROP 列名;
《案例》
-- 1.修改student表名变为tb_stu
ALTER TABLE student RENAME TO tb_stu;
-- 2.为学生表添加一个新的字段remark,类型为varchar(20)
ALTER TABLE tb_stu ADD remark varchar(20);
-- 3.将student表中的remark字段的改成varchar(100)
ALTER TABLE tb_stu MODIFY remark varchar(100);
-- 4.将student表中的remark字段名改成intro,类型varchar(30)
ALTER TABLE tb_stu CHANGE remark intro varchar(30);
-- 5.删除student表中的字段intro
ALTER TABLE tb_stu DROP intro;4.删除表
Ⅰ.直接删除
DROP TABLE 表名;
Ⅱ.判断，存在则删除
DROP TABLE IF EXISTS 表名;
>DML数据操作语言
//DML:针对数据表中的记录进行增、删、改
一.insert操作(增)：向数据表中添加数据(行记录)
Ⅰ.给指定的列添加数据
INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...)//列名和值一一对应
Ⅱ.给全部列添加数据
INSERT INTO 表名(...,...,所有列名都写) VALUES(值1,...)
INSERT INTO 表名 VALUES(值1,...对应所有列的值)//开发中不适用
Ⅲ.批量添加数据
INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),...//后面每一个括号为一组
《案例》
-- 在指定列上添加数据
INSERT INTO tb_stu(name,gender,phone) VALUES ('hhh','男','181********');
-- 批量添加数据
INSERT INTO tb_stu(name,gender)
VALUES
('zj','女'),
('zzz','男性'); #该写法为开发常用写法风格二.update操作(改)
Ⅰ.修改表数据
UPDATE 表名 SET 列名1=值1,列名2=值2,... WHERE 条件;//不带条件则将表所有数据都修改
《案例》
-- 修改数据不带条件,开发中不能用
UPDATE tb_stu SET gender='男'; #会将所有行记录数据gender字段都改为男
-- 修改数据带条件
UPDATE tb_stu SET gender='男' WHERE name='zj'; #将name为zj的数据gender修改为男三.delete操作(删)
Ⅰ、删除表中部分数据数据
DELETE FROM 表名 WHERE 条件;//删除所有满足条件的数据
Ⅱ、删除表中所有数据
DELETE FROM 表名 WHERE 1=1;//给定一个必对的条件，开发中不适用
TRUNCATE TABLE 表所属数据库名.表名;//truncate语句属于DDL语言
两条语句区别：delete是逐行删除表中数据，truncate是将整个表摧毁然后创建一个相同结构的表
Ⅲ、存在多张表
DELETE 要删除数据所在表p1 FROM P1,P2 WHERE 条件;
>DQL数据查询语言
//DQL：专门针对表数据的查询
#在基础查询之上，如果有其它需求就可以加不同查询关键字
SELECT
字段列表
FROM
表名列表 -- 基础查询
WHERE
条件列表 -- 条件查询
GROUP BY
分组字段
HAVING
分组后条件 -- 分组查询
ORDER BY
排序字段 -- 排序查询
LIMIT
分页限定 -- 分页限定//执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
一.基础查询
Ⅰ.查询多个字段
SELECT 字段1,字段2,.... FROM 表名;//查询指定字段
SELECT * FROM 表名;//查询所有数据，开发中不让使用，*意思代表所有
Ⅱ.去除重读查询
SELECT DISTINCT 字段1,字段2,... FROM 表名;//当查询多个字段时，行记录查询字段全部一样时才会去重
Ⅲ.查询时给列、表指定别名:显示表的时候就会显示别名
//AS关键字可以省略、别名建议放在双引号里(虽然不用双引号是可以的)
SELECT 字段1 AS "别名",字段2 AS "别名"... FROM 表名;
SELECT 字段1 AS "别名",... FROM 表名 AS "表别名";
《案例》
-- 查询多个字段
SELECT name , gender FROM tb_stu; #查询name、gender字段数据
-- 去重查询
SELECT DISTINCT gender FROM tb_stu;
-- 查询时显示别名
SELECT name AS "姓名",gender "性别",grade "成绩" FROM tb_stu;二.条件查询
Ⅰ.语法：
SELECT 字段列表 FROM 表名列表 WHERE 条件列表;
Ⅱ.条件符号：
模糊查询语法：
SELECT 字段列表 FROM 表名 WHERE 字段名 LIKE 模糊查询的关键字//关键词通常和通配符号关联
//通配符号：% -> 任意个数据 ， _ -> 任意一个数据
《案例》
-- 查询分数大于60分的
SELECT name , grade FROM tb_stu WHERE grade >60;
-- 查询结果小于等于60分的
SELECT name , grade FROM tb_stu WHERE grade <=60; #条件筛选会忽略null值
-- 查询姓名为人机1的
SELECT name FROM tb_stu WHERE name = '人机1';
-- 查询姓名不为人机1的
SELECT name FROM tb_stu WHERE name != '人机1';
SELECT name FROM tb_stu WHERE name <> '人机1';
-- 查询性别为男的且分数为66.6分的
SELECT name , gender ,grade
FROM tb_stu
WHERE (gender='男性' OR gender='男')AND grade=66.6; #AND可改为&&,OR可改为||
-- 查询number为1、3、5的学生
SELECT number, name
FROM tb_stu
WHERE number IN (1,3,5); #可理解为 number=1 OR number=2 OR number=3
-- 查询number不为1、3、5的
SELECT number, name
FROM tb_stu
WHERE number NOT IN (1,3,5);
-- 查询成绩为60到100之间的
SELECT name, grade
FROM tb_stu
WHERE grade BETWEEN 60 AND 100 #相当于 grade >=60 AND grade <=100
-- 查询姓周的
SELECT name
FROM tb_stu
WHERE name LIkE '周%';
-- 查询姓名里含龙的
SELECT name
FROM tb_stu
WHERE name LIKE '%龙%';
-- 查询姓人且名字为三个字的
SELECT name
FROM tb_stu
WHERE name LIKE '人__'; #两个下划线,两个字则为 '人_'
-- 查询名字为两个字的
SELECT name
FROM tb_stu
WHERE name LIKE '__'; #两个下划线三.排序查询
Ⅰ.语法：排序方式有升序(默认可以不写):ASC和降序:DESC两种
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1[排序方式1],排序字段名2[排序方式2]...;
//要点1:如果有多个排序条件,当前一个排序有一样的值时,后一个排序才会进行
//要点2:当升序的时候null值排第一个,降序的时候null值排最后一个
《案例》
-- 按招number降序排序
SELECT number, name
FROM tb_stu
ORDER BY number DESC;
-- 按照成绩升序排序，如果成绩相同时按照number降序排序
-- 成绩没有一样的时候,number的DESC降序是不会执行的
SELECT number, name, grade
FROM tb_stu
ORDER BY grade ASC, number DESC; #可将升序省略为 grade,number DESC四.分组查询(统计查询)
1.聚合函数
Ⅰ.概念:将一列(一竖行)数据作为一个整体，进行纵向计算
Ⅱ.聚合函数分类:
//要点1：count统计某一列存在null，null是不统计的、一整列都是null统计结果为0
//要点2：函数名与括号之间不要有空格
//要点3：sum会忽略null值进行计算
//要点4：在MySQL中null与任何值相加结果还是null，解决方案->使用ifnull(列名,默认值)函数，当指定列名存在null值是会以指定的默认值代替
Ⅲ.聚合函数语法
SELECT 聚合函数名(列名) FROM 表;
-- 聚合函数求成绩总和
SELECT AVG(grade) FROM tb_stu;
-- 统计数量全为null值的生日列,结果为0
SELECT COUNT(birthday) FROM tb_stu;
-- 统计数量一列含null的成绩列,null不会别统计
SELECT COUNT(grade) FROM tb_stu;
-- 统计人的个数并且显示时名字为人数
SELECT COUNT(number) AS "人数" FROM tb_stu;
-- 统计所有列行数
SELECT COUNT(*) AS "行数" FROM tb_stu;
-- 代码补充:计算英语和数学成绩总分
SELECT SUM(english) + SUM(math) AS "总分" FROM tb_stu;
SELECT SUM(english + math) AS "总分" FROM tb_stu;
#两种写法查询结果有差别：在MySQL中null与任何值相加结果还是null
#第二种写法修改：用ifnull函数
SELECT SUM(ifnull(english,0) + ifnull(math,0)) AS "总分" FROM tb_stu;2.分组查询：如果数据没相同就不要用分组查询
Ⅰ.含义即语法：分组就是按照某一列或者某几列，把相同数据进行合并输出
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名1 , 分组字段2 [HAVING 分组后条件过滤]//先按字段1分组后的基础上按字段2分组，先分完组后进行HAVING条件过滤
//要点1：语法要求，在进行分组查询时，书写在SELECT关键字后的列名要么出现在GROUP BY关键字后、要么使用聚合函数包含->查询的字段必须分组，聚合的列会自动按分组计算（非聚合的列必须出现在GROUP BY后）
//WHERE条件与HAVING条件区别：第一WHERE条件在分组之前执行、而HAVING条件在分组之后执行，WHERE条件不可以使用聚合函数、HAVING条件可以使用聚合函数
Ⅱ.HAVING关键字使用：适用于分组后的条件过滤
《案例》
-- 查询不同性别的成绩总分
SELECT gender,sum(grade) AS "总分" FROM tb_stu GROUP BY gender;
-- 查询不同性别的成绩总分并且只显示总分上60的分组
SELECT gender,SUM(grade) AS "总分" FROM tb_stu GROUP BY gender HAVING SUM(grade)>60;五.分页查询：LIMIT是MySQL特有的
随着业务增长，数据表中的体量会越来越大，查询时就不能把所有数据全部查询出来(效率低)，解决方案->一部分一部分查询
Ⅰ.语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数//索引从0开始，0代表数据表第一行，条目数是查询多少行数据，起始索引=(当前页码数-1)*每页显示的条数
//要点1：如果第一个参数是0可以简写、如果查询行数不够条目数则有多少显示多少
《案例》
-- 将所有数据分为三页
SELECT number,name FROM tb_stu LIMIT 0,3; #第一页起始索引=(1-1)*3=0
SELECT number,name FROM tb_stu LIMIT 3,3; #第二页起始索引=(2-1)*3=3
SELECT number,name FROM tb_stu LIMIT 6,3; #第三页起始索引=(3-1)*3=6
-- 跳过第一行显示后面四行
SELECT number,name FROM tb_stu LIMIT 1,4;>DCL数据控制语言
//用来管理数据库用户、控制数据库访问权限
一.用户管理
1.查询用户
USE mysql;//用户信息存放在系统数据库mysql数据库中
SELECT * FROM user;
2.创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
//创建的用户没有访问其它数据库的权限
//主机名用通配符%代替，就代表任意主机可以访问
3.修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIDE WITH mysql_native_password BY '新密码';
4.删除用户
DROP USER '用户名'@'主机名';
二.权限控制
1.查询权限
SHOW GRANTS FOR '用户名'@'主机名';
2.授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
3.撤销权限
REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名';
要点：
//*.*代表所有数据库的所有表
//多个权限之间用逗号分割
>数据库备份与还原
备份
还原
1.控制台指令
mysql -u用户名 -p密码 数据库名 < 备份文件.sql
2.可视化工具
//此时db1数据库不小心别删除了可以进行以下操作将之前备份的数据还原
//还原时需要自己先创建数据库然后还原
MySQL进阶
>事务
简介：
》数据库事物是一种机制、一个操作序列，包含了一组数据库操作命令
》如果一个包含多个步骤的业务操作(多行SQL语句)，被事务管理，要么这些操作同时操作成功，要么同时失败
》事务是一个不可分割的工作逻辑单元
//事务使用机制：开启事务->执行多条SQL语句->提交事务或回滚事务
START TRANSACTION/start transaction:开启事务
COMMIT/commit:提交
ROLLBACK/rollback:回滚
一.事务基本使用
1.MySQL数据库的事务机制：
Ⅰ.自动提交(默认):每执行一行SQL语句，就开始一个事务，SQL提交完成，事务提交(一行SQL一个事务)
Ⅱ.手动提交(要写代码):先开启事务，再执行多行SQL语句(可多行可一行)，提交事务(提交事务则数据更改)或回滚(回滚则数据不变保持再开启事务时的原始数据)
2.手动提交事务SQL语句
开启事务：START TRANSACTION;
提交事务：COMMIT;
回滚事务：ROLLBACK;
-- 事务操作：
#1.开启事务
START TRANSACTION;
#2.执行转账操作
-- 出账
UPDATE account SET money = money-500 WHERE name='万元户';
-- 进账
UPDATE account SET money = money+500 WHERE name='暴发户';
#3.回滚事务
ROLLBACK;
#3.提交事务
COMMIT;3.查看和更改事务默认提交方式：
1代表自动提交，0代表手动提交
查看事务默认提交方式:SELETE @@autocommmit;
修改默认提交方式:SET @@autocommit=0;//修改为手动提交任务后，增删改之后需要执行commit提交事务
二.事务原理图
三.面试：事务四大特征(ACID/acid)
Atomicity(原子性):原子是不可分割的最小操作单位,事务要么同时成功,要么同时失败。
Consistensy(一致性):事务操作前后,数据总量不变
Isolation(隔离性):多个用户并发的访问数据库时,一个用户的事务不能被其他用户的事务干扰,多个并发的事务之间要相互隔离。
Durability(持久性):当事务提交或回滚后,数据库会持久化的保存数据。
四.事务隔离级别
1.(面试)事务并发访问引发的三个问题：
//事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题
Ⅰ.脏读：
//出现原因：cpu在两个或多个事务间快速切换，两个事务开启时事务B修改了数据但没提交事务，但线程切到了事务A,则事务A读取到了事务B未提交的修改数据
Ⅱ.不可重复读：
//出现原因：两个事务开启时事务A读取到了原始数据，但线程切到了事务B，事务B修改了数据并提交了事务,后面事务A再次读取到的数据则与开始原始数据不同
Ⅲ.幻读/虚读：
//出现原因：两个事务开启时事务A读取到了原始数据，但线程切到了事务B，事务B插入或删除了数据并提交了事务，后面事务A读取到的记录行数与开始不同
2.隔离级别：
说明：
》最严重的就是脏读(读取了错误数据),这个问题一定要避免MySQL默认隔离级别已经解决;
》不可重复读和虚读其实并不是逻辑上的错误,而是数据的时效性问题,所以这种问题并不属于很严重的错误;
》如果对于数据的时效性要求不是很高的情况下,我们是可以接受不可重复读和虚读的情况发生的;
安全:串行化>可重复读>读已提交>读未提交
性能:串行化<可重复读<读已提交<读未提交
Ⅰ.查看隔离级别
SHOW VARIABLES LIKE '%isolation%'; 或 SELECT @@tx_isolation;
>函数
//函数：就是MySQL数据库提供的一些方法（相当于java中的API方法）
一.日期函数
//获取生日字段的数据不要年份：SELECT MONTH(生日字段),DAY(生日字段) FROM 表名;
二.流程函数
1.CASE WHEN判断函数：用于计算条件列表并返回多个结果表达式之一
//以下代码不写ELSE并且都不满足则返回null
-- 格式1
CASE 列|表达式
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...
ELSE 结果n #以上条件都不满足就会执行else结果
END
-- 格式2
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...
ELSE 结果n
END-- 有人创建表时用int类型取带char类型来存储性别（char存储占位大于int）
-- 查询时使用判断函数识别性别
-- 格式1
SELECT id,name,
CASE sex
WHEN 1 THEN '男'
WHEN 2 THEN '女'
WHEN 0 THEN '保密'
ELSE '未知'
END AS gender
FROM test_table;
-- 格式2
SELECT id,name,
CASE WHEN sex=1 THEN '男'
WHEN sex=2 THEN '女'
WHEN sex=0 THEN '保密'
ELSE '未知'
END AS gender
FROM test_table;2.IF判断函数
IF(value<条件表达式>,T,F)//如果value为true则返回T否则返回F
-- IF函数
SELECT IF(name = '赵大宝','是赵大宝','不是赵大宝') FROM user;3.IFNULL判断函数
IFNULL(value1,value2)//如果value1不为null则返回value1，否则返回value2
三.字符函数
Ⅰ.获取字符串长度：char_length()
SELECT char_length('字符串'); 或 SELECT char_length(字符串类型字段) FROM 表名;
Ⅱ.拼接字符串：concat()
SELECT concat('字符串','字符串');//拼接字符串
SELECT concat(字符串字段1,字符串字段2);//拼接字段数据
SELECT concat(字符串字段1,'-',字符串字段2);//拼接字段和字符串
Ⅲ.转大小写：转小写lower()、转大写upper()
SELECT lower('Hello World');
SELECT upper('Hello World');
Ⅳ.截取字符串：substr()
SELECT substr('字符串',从哪个索引开始截取,截取长度);//起始索引从1开始与Java不同
Ⅴ.去除字符串前后空格：trim()
应用场景：char类型数据存储长度不足时会自动补空格，用于去除是char类型数据不带空格
SELECT trim(' ZIFU CHAUN ');//只去除前后空格，中间空格不会去除
四.数学函数
五.开窗函数
//对分组数据进行计算、 同时保留原始行的详细信息
//可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少
1.SUM<聚合函数> OVER
//可以实现同组内数据的 累加求和
SUM<其它聚合函数也可以>(计算字段名) OVER (PARTITION BY 分组字段名);
-- 希望计算每个客户的订单总金额，并显示每个订单的详细信息
SELECT
order_id,
customer_id,
order_date,
total_amount,
SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total_amount
FROM
orders;
//使用开窗函数 SUM 来计算每个客户的订单总金额（customer_total_amount），并使用 PARTITION BY 子句按照customer_id 进行分组。从前两行可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的订单总金额。
2.sum<聚合函数> OVER ORDER BY
SUM<聚合函数>(计算字段名) OVER(PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)
--计算每个客户的历史订单累计金额，并显示每个订单的详细信息
SELECT
order_id,
customer_id,
order_date,
total_amount,
SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS cumulative_total_amount
FROM
orders;
//使用开窗函数 SUM 来计算每个客户的历史订单累计金额（cumulative_total_amount），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 order_date 进行排序。从结果的前两行可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的历史订单累计金额；相比于只用 sum over，同组内的累加列名称
3.RANK
//用于对查询结果集中的行进行排名的开窗函数,可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名
-- 语法
RANK() OVER (
PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS 别名//PARTITION BY:子句可选，用于指定分组列，将结果集按照指定列进行分组
//ORDER BY:子句用于指定排序列及排序方式
//AS 别名：用于指定生成的 Rank 排名列的别名
SELECT
order_id,
customer_id,
order_date,
total_amount,
RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS customer_rank
FROM
orders;
4.ROW_NUMBER
//为查询结果集中的每一行 分配唯一连续排名 的开窗函数
//函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。每一行都有一个唯一的行号，从 1 开始连续递增。
ROW_NUMBER() OVER (
PARTITION BY column1, column2, ... -- 可选，用于指定分组列
ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS 别名SELECT
order_id,
customer_id,
order_date,
total_amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS row_number
FROM
orders;
5.LAG/LEAD
Ⅰ.Lag 函数
//用于获取当前行之前 的某一列的值。它可以帮助我们查看上一行的数据
LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
》column_name:要获取值的列名。
》offset:表示要向上偏移的行数。例如,offset为1表示获取上一行的值,offset为2表示获取上两行的值,以此类推。
》default_value:可选参数,用于指定当没有前一行时的默认值。
》PARTITION BY 和 ORDER BY 子句可选,用于分组和排序数据。
Ⅱ.Lead函数
//用于获取当前行之后的某一列的值，它可以帮助我们查看下一行的数据。
LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
》column_name：要获取值的列名
》offset：表示要向下偏移的行数。例如，offset为1表示获取下一行的值，offset为2表示获取下两行的值，以此类推
》default_value:可选参数,用于指定当没有后一行时的默认值。
》PARTITION BY 和 ORDER BY 子句可选,用于分组和排序数据。
SELECT
student_id,
exam_date,
score,
LAG(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_score,
LEAD(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_score
FROM
scores;
>约束
概念：约束是一种限制，用于修饰表中的列，通过这种限制保证表中数据的正确性、有效性和完整性
特点：一张表主键约束(关键字)只能有一个，其它约束(关键字)可以有多个
面试题：非空+唯一 约束与主键约束区别？
》主键约束在表中只能存在一个，但非空+唯一约束可以存在多个
》主键约束可以添加自增约束，但非空+唯一约束不能
》主键约束底层维护了一个主键索引，而唯一约束底层维护的是唯一索引
一.主键约束（特点：唯一+非空）<重点>
1.基本使用：开发中通常每张表必须有一个主键字段（提高查询效率，和外键建立表关系）
单列主键:CREATE TABLE 表名(字段名 数据类型 PRIMARY KEY,其他字段...);//创建表时对一个字段定义主键
复合主键(多列主键):CREATE TABLE 表名(字段名1 数据类型 ,字段名2 数据类型,其它字段...,PRIMARY KEY(字段1,字段2...));//创建表时对多个字段定义主键
//要点：一张表只能有一个主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段);//在已有表中指定主键
ALTER TABLE 表名 DORP PRIMARY KEY//删除主键约束
-- 给已有表中添加主键约束
ALTER TABLE student ADD PRIMARY KEY(number);
-- 创建表时指定主键约束
CREATE TABLE student2(
id int PRIMARY KEY,
name varchar(10)
);
-- 删除主键约束
ALTER TABLE db2.student2 DROP PRIMARY KEY;2.主键自增：数据过多时自己手动在主键字段下插入数据时不确定是否重复，开发中主键列由MySQL管理(程序员不干预由MySQL自主完成插入)
Ⅰ.主键自增基本使用：
主键添加语法上调整：字段名 字段类型 PRIMARY KEY AUTO_INCREMENT;//主键是整数类型才能自动增加
//要点1：主键自增后的数值删除后则不会再被使用了,信息插入失败主键值也会被使用
//要点2：当自己指定值之后，自增会从指定值开始，例：添加id为100的人，后面要求自增就从101开始
-- 创建student表添加主键约束同时加上主键自增
CREATE TABLE student3(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(10)
);
-- 往带有主键自增的表里添加数据
INSERT INTO db2.student3 (name) VALUES ('张三'); # 该表第一个数据
INSERT INTO db2.student3 (name) VALUES ('李四'); # 该表第二个数据
INSERT INTO db2.student3 (name) VALUES ('王五'); # 该表第三个数据
-- 在添加数据时包含主键自增字段，赋值时填null
INSERT INTO db2.student3 (id,name) VALUES (null,'王五');
-- 删数据
DELETE FROM db2.student3 WHERE id=4;
-- 此时赵六就不是id4而是5了
INSERT INTO db2.student3 (id,name) VALUES (null,'赵六');
-- 自己指定值后再要求自增则会从指定值开始自增
INSERT INTO db2.student3 (id,name) VALUES (100,'田老八');
INSERT INTO db2.student3 (id,name) VALUES (null,'百老六');Ⅱ.主键自增使用细节：
二.唯一约束（特点：唯一）
1.作用：被唯一约束的字段，本列数据不允许出现重复数据，null除外null可以出现多个
2.基本使用：
CREATE TABLE 表名(字段名 字段类型 UNIQUE,...);//创建表时指定
ALTER TABLE 表名 ADD UNIQUE(字段);//已有表给指定字段添加唯一约束
-- 创建表student4给name字段指定唯一约束
CREATE TABLE student4(
id int PRIMARY KEY,
name varchar(10) UNIQUE
);三.非空约束（特点：非空）
1.作用：被非空约束的字段，本列数据不允许出现null值数据
2.基本使用：
CREATE TABLE 表名(字段名 字段类型 NOT NULL,...);//创建时指定
ALTER TABLE 表名 MODIFY 字段 类型 NOT NULL;//已有表添加唯一约束
-- 创建表student5,name字段不能为null且不能重复
CREATE TABLE student5(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(10) NOT NULL UNIQUE
);四.默认值约束（特点：没有指定具体数据时给定默认值）
1.作用:被默认值约束的字段，相当于给字段添加默认值，插入数据时没有被赋值(插入null不算没赋值)则使用默认值
2.基本使用：
CREATE TABLE 表名(字段名 字段类型 DEFAULT 默认值,...);//创建表时指定
ALTER TABLE 表名 MODIFY 字段 类型 DEFAULT 默认值;//已有表添加默认值约束
-- 创建表student6,地址与生日指定默认值约束
CREATE TABLE student6(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(10) NOT NULL UNIQUE,
adress varchar(100) DEFAULT '长沙'
);
五.外键约束
//认知：先有外键字段再有外键约束，外键字段是建立表与表之间的关系，外键约束是约束外键字段值只能是主表主键值
1.作用：约束外键字段下的数据和主表主键下的数据保持一致（一致性、完整性）
2.语法：外键名取名一般取名方法：表名_外键字段名_fk
CREATE TABLE 表名(其它字段,外键字段名 int,CONSTRAINT 外键名 FOREIGN KEY(当前表外键字段名) REFERENCES 主表名(主表主键));//创建表时创建外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(当前表外键字段名) REFERENCES 主表名(主表主键);//修改字段为外键
ALTER TABLE 从表名 DROP FOREIGN KEY 外键名;//删除外键
《案例》
-- 部门表
CREATE TABLE 部门表(
id int PRIMARY KEY AUTO_INCREMENT,
部门名称 varchar(20),
部门地址 varchar(20)
);
-- 添加测试数据
INSERT INTO 部门表(部门名称, 部门地址) VALUES
('研发部','长沙'),
('销售部','深圳');
-- 员工表
CREATE TABLE 员工表(
id int PRIMARY KEY AUTO_INCREMENT,
员工姓名 varchar(20),
员工年龄 int,
部门id int, #外键字段数据类型与主表主键字段数据类型保持一致
CONSTRAINT 员工表_部门id_fk FOREIGN KEY(部门id) references 部门表(id)
);
-- 添加数据
INSERT INTO 员工表(员工姓名,员工年龄,部门id) VALUES
#当外键字段所给值，在主表主键字段下不存在则会报错
('李四',29,1), #属于研发部
('王老八',35,1), #属于研发部
('赵本汉',18,2), #属于销售部
('钱无敌',18,2); #属于销售部3.外键约束使用细节
Ⅰ.操作注意事项：
①添加数据时：先添加主表中数据，再添加从表中数据
②删除数据时：先删除从表中数据，再删除主表中数据
//直接先删除主表数据会报错(外键表中还有引用数据)，解决方案就是先删除从表中数据，或者用级联删除
③修改数据时：主表中的主键字段数据被引用则不能修改
//直接修改主表数据会报错，可以用级联更新
Ⅱ.(扩展)外键的级联：实现主表数据删除、更新操作，对应从表数据也同时删除、更新数据
语法：在创建从表时添加，需要哪个就写哪个两个可以一起写,必须与外键约束一起添加
ON UPDATE CASCADE//级联更新：主表进行数据更新操作，从表对应的数据也要进行更新操作
ON DELETE CASCADE//级联删除：主表进行主键删除后，从表外键对应数据也要删除
-- 员工表
CREATE TABLE 员工表(
id int PRIMARY KEY AUTO_INCREMENT,
员工姓名 varchar(20),
员工年龄 int,
部门id int, #外键字段数据类型与主表主键字段数据类型保持一致
CONSTRAINT 员工表_部门id_fk FOREIGN KEY(部门id) references 部门表(id)
ON UPDATE CASCADE #级联更新
ON DELETE CASCADE #级联删除
);
-- 在已有表添加约束和级联，如果表里已有外键约束必须先删除约束，然后再一起添加约束和级联
ALTER TABLE 员工表 ADD
CONSTRAINT 员工表_部门id_fk FOREIGN KEY(部门id) references 部门表(id)
ON UPDATE CASCADE #级联更新
ON DELETE CASCADE; #级联删除>表关系设计、多表查询、组合查询
表关系设计
一.一对一设计(了解)
Ⅰ.设计原则:
》方案一：两张表合并为一张表
》方案二：任选一张表作为从表创建外键字段
二.一对多表设计(开发使用场景多)
//案例场景：一个用户有多个订单，但一个订单只属于一个用户
Ⅰ.相关基础知识：
》约定：一的这一方叫主表或1表，多方叫从表或多表
》外键字段：设计表时，从表添加一个字段用于存放主表主键字段下的值，这个字段叫外键字段
Ⅱ.设计原则：在从表创建一个字段作为外键，从表外键值指向主表的主键
三.多对多设计（变为两个一对多）
Ⅰ.设计原则：需要创建第三张表作为中间表，中间表至少两个字段，这两个字段分别作为外键指向各自一方的主键
多表查询(高级查询)
多表查询通用技巧：
①明确要查询哪些数据
②明确所查询数据分别归属哪张表
③明确表与表的关系（寻找主键、外键）
//多表查询:在查询数据时，数据是从多张表中获取的
一.笛卡尔积：多张表查询时每张表的每条数据组合的数据结果集
1.多表查询时出现的笛卡尔积：
-- 多表查询
SELECT 员工表.id,员工姓名,员工年龄,部门id,
部门表.id,部门名称,部门地址
FROM 员工表,部门表;
2.消除笛卡尔积：
方法：查询时加上条件，例如：WHERE 主表.主键字段 = 从表.外键字段
-- 消除笛卡尔积
SELECT 员工表.id,员工姓名,员工年龄,部门id,
部门表.id,部门名称,部门地址
FROM 员工表,部门表
WHERE 部门表.id = 员工表.部门id; #消除笛卡尔积条件
二.内连接查询
1.目的、作用：把多张表中相互关联的数据查询出来
2.语法：
隐式内连接:SELECT 列名 FROM 表1,表2 WHERE 主表.主键=从表.外键;
显示内连接:SELECT 列名 FROM 表1[INNER] JOIN 表2 ON 从表.外键=主表.主键;//标准写法
//区别:结果一样,隐式内连接先进性笛卡尔积在筛选数据显式内连接在查询时就对数据进行过滤
-- 查询王老八信息和所在部门
-- 隐式内连接
SELECT 员工表.id,员工表.员工姓名,员工表.员工年龄,
部门表.部门名称,部门表.部门地址
FROM 员工表,部门表
WHERE 部门表.id = 员工表.部门id AND 员工表.员工姓名='王老八';
-- 显示内连接
SELECT 员工表.id,员工表.员工姓名,员工表.员工年龄,
部门表.部门名称,部门表.部门地址
FROM 员工表 INNER JOIN 部门表
ON 部门表.id = 员工表.部门id AND 员工表.员工姓名='王老八';
#ON的后面可以加WHERE,AND可以改为WHERE三.外连接查询
1.两种连接方式作用：
//理解:ON后面的语句就是匹配
Ⅰ.左外连接：左表中所有记录都出现在结果中，如果右表没匹配记录使用null填充
Ⅱ.右外连接：右表中所有记录都出现在结果中，如果左表没匹配记录使用null填充
2.语法：掌握一个就行，关键字不变交换两表位置就行
左外连接：SELECT 列名 FROM 左表 LEFT JOIN 右表 ON 主表.主键=从表.外键;
右外连接：SELECT 列名 FROM 左表 RIGHT JOIN 右表 ON 左表.主键=从表.外键;
-- 外连接查询
-- 左外连接查询,查询所有部门和部门内所有员工
SELECT 部门名称,部门地址,
员工表.id,员工姓名,员工年龄
FROM 部门表 LEFT JOIN 员工表
ON 部门表.id = 员工表.部门id;
-- 右外连接查询，查询所有员工和对应部门
SELECT 部门名称,部门地址,
员工表.id,员工姓名,员工年龄
FROM 部门表 RIGHT JOIN 员工表
ON 部门表.id = 员工表.部门id;
四.子查询：在一个查询语句中嵌套了另一个查询语法
//子查询结果分为三类：单行单列、多行单列、多行多列
//子查询分类：
》相关子查询(当前子查询不能独立执行，需依赖外部的查询结果)，先执行外部查询再执行内部查询
》非相关子查询(当前子查询可以独立执行)，先执行内部查询再执行外部查询
SELECT 字段1,字段2
FROM 表1,(SELECT 字段列表 FROM 表 WHERE ...) #这里的子查询出来的结果作为外层查询的数据来源
WHERE 条件1 条件2=(SELECT 字段列表 FROM 表 WHERE ...) #条件2满足等于子查询的查询结果
/*
子查询书写:
方式1:子查询作为条件（非相关子查询）
select 字段列表 from 表 where 字段=(select 字段 from 表 where .... )
方式2:子查询作为表（非相关子查询）
select 字段列表 from 表,(select 字段列表 from 表 where .... )as 别名 where 条件
方式3:子查询作为字段（非相关子查询）
select 字段1,(select 字段2 from 表 where 条件) from 表 where...
*/
1.单行单列：
-- 子查询结果单行单列，查询年龄最大的员工
SELECT id,员工姓名,员工年龄
FROM 员工表
WHERE 员工年龄 = (SELECT MAX(员工年龄) FROM 员工表); 
2.多行单列：子查询结果是多行单列，可认为结果一个数组，父查询使用in、any、all关键字
-- 查询年龄大于30的员工的部门
SELECT 部门名称
FROM 部门表
WHERE id IN (SELECT 部门id FROM 员工表 WHERE 员工年龄>30);
-- 查询年龄大于30的员工信息和其部门信息
-- 查询年龄都大于2号销售部门所有员工的员工信息
SELECT 员工表.id,员工姓名,员工年龄
FROM 员工表
WHERE 员工年龄 >ALL (SELECT 员工年龄 FROM 员工表 WHERE 部门id=2);
-- 查询年龄都大于2号销售部门任意员工的员工信息
SELECT 员工表.id,员工姓名,员工年龄
FROM 员工表
WHERE 员工年龄 >ANY (SELECT 员工年龄 FROM 员工表 WHERE 部门id=2);
3.多行多列：子查询结果是多行多列，查询结果可以当作一张虚拟表，可以使用表连接再次进行查询
//要点1：需要访问子查询结果表的字段，需要为字段取别名否则无法访问表中字段
-- 查询年龄大于30的员工信息和其部门信息
SELECT 虚拟表.id,虚拟表.员工姓名,虚拟表.员工年龄,
部门名称,部门地址
FROM 部门表
RIGHT JOIN (SELECT id,员工姓名,员工年龄,部门id FROM 员工表 WHERE 员工年龄>30) AS 虚拟表
ON 虚拟表.部门id = 部门表.id;
4.exists子查询
//用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据
-- 主查询
SELECT name, total_amount
FROM customers
WHERE EXISTS (
-- 子查询
SELECT 1
FROM orders
WHERE orders.customer_id = customers.customer_id
);
//先遍历客户信息表的每一行，获取到客户编号；然后执行子查询，从订单表中查找该客户编号是否存在，如果存在则返回结果
//先遍历客户信息表的每一行，获取到客户编号；然后执行子查询，从订单表中查找该客户编号是否存在，如果存在则返回结果
五.自连接查询技巧
//一张表当两张表使用
组合查询
//组合查询是一种将多个 SELECT 查询结果合并在一起的查询操作
- UNION 操作：它用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。
- UNION ALL 操作：它也用于将两个或多个查询的结果集合并， 但不去除重复的行 。即如果两个查询的结果有相同的行，则全部保留。
-- UNION 操作
SELECT name, age, department
FROM table1
UNION
SELECT name, age, department
FROM table2;
-- UNION ALL操作
SELECT name, age, department
FROM table1
UNION ALL
SELECT name, age, department
FROM table2;>索引、SQL优化
SQL性能分析
一.MySQL性能
1.两种优化性能方式：
》硬优化：软优化后性能还是很低，那就只能购买服务器在硬件上优化
》软优化：在操作和设计数据库方面进行优化（SQL语句、表结构）
2.sql语句优化：了解该数据表是查询密集型(DQL)还是修改密集型(DML)
Ⅰ.查询累计插入和返回数据条数
show global status like 'innodb_rows%';
二.SQL性能分析
1.SQL执行频次：
SHOW [GLOBAL|SESSION] STATUS LIKE 'Con___（七个下划线）____';//GLOBAL是全局，SESSION是当前会话
2.慢查询日志：
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位;秒,默认10秒)的所有SQL语句的日志。
查询慢查询日志是否开启：SHOW VARIABLES LIKE 'slow_query_log';
(MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息)
3.profile详情：show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
查看到当前MySQL是否支持：SELECT @@have_profiling;
查看profile是否开启:SELECT @@profiling//0是关闭、1是开启
打开profile：SET profiling = 1;
注意：
》query_id是数字代表第几次查询的语句，不是表名或字段名
》show profile [type] query query_id;//使用
//type参数：ALL, BLOCK, CONTEXT, CPU, IPC, MEMORY, PAGE, SOURCE 或 SWAPS
ALL - 显示所有性能分析信息
BLOCK - 显示块级别的 I/O 操作
CONTEXT - 显示上下文切换信息
CPU - 显示 CPU 使用情况
IPC - 显示进程间通信信息
MEMORY - 显示内存使用情况
PAGE - 显示页面错误和页面处理信息
SOURCE - 显示源代码级别的执行信息
SWAPS - 显示交换操作信息
4.explain执行计划：
EXPLAIN 或者DESC命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。
Ⅰ.语法：
EXPANIN/DESC SELECT 字段列表 FROM WHERE 条件;
Ⅱ.查询结果：
查询结果对应的每一列含义：
id：select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)
select_type：代表查询类型
type：表示连接类型,性能由好到差的连接类型为NULL(不访问任何表才会出现)、system(只访问系统表才会出现)、const、eq_ref、ref(使用非唯一性索引查询时出现)、range、index、all
possible_key：显示可能应用这张表上的索引
Key：实际使用的索引，如果为NULL,则没有使用索引
Key_len：使用索引的字节数，该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好。
rows：执行查询的行数，innodb中是一个预估值
filtered：表示返回结果的行数占需读取行数的百分比，filered的值越大越好
Extra：额外信息
索引基础
一.索引语法、底层原理
//本质：索引就是数据结构（B+Tree）
//主键索引、唯一索引：当添加主键约束、唯一约束的时候自动添加主键索引和唯一索引
1.创建索引
Ⅰ.创建索引：
创建普通索引:CREATE INDEX 索引名 ON 表名(字段 [ASC/DESC]);
创建唯一索引:CREATE UNIQUE INDEX 索引名 ON 表名(字段);
创建普通组合索引:CREATE INDEX 索引名 ON 表名(字段1,字段2,...);
创建唯一组合索引:CREATE UNIQUE INDEX 索引名 ON 表名(字段1,字段2,..);
//要点1:同一张表的多个索引的索引名不能重复
//要点2:主键索引无法通过上面方式创建
//要点3：创建组合(联合)索引时，第一个字段为最左前缀字段
//要点4：创建索引时可以指定按升序还是降序保存，默认升序，联合索引多个字段排序方式可以不同
Ⅱ.在已有表的字段上修改表时指定
添加一个主键:ALTER TABLE 表名 ADD PRIMARY KEY(字段)//默认索引名:primary
添加一个唯一索引:ALTER TABLE 表名 ADD UNIQUE(字段);//默认索引名:字段名
添加一个普通索引:ALTER TABLE 表名 ADD INDEX(字段);//默认索引名:字段名
-- 创建表时创建索引
CREATE TABLE student(
id int PRIMARY KEY AUTO_INCREMENT, -- 主键（主键索引）
name varchar(20),
telephone varchar(11) UNIQUE, -- 唯一约束+唯一索引
sex varchar(5),
birthday DATE,
INDEX(name) -- 普通索引
);
-- 给student表中birthday字段添加索引
CREATE INDEX idx_student_birthday ON student(birthday);2.查看索引
SHOW INDEX FROM 表名;
3.删除索引
DROP INDEX 索引名称 ON 表名称;
4.索引的数据结构：B+Tree
B+Tree结构特点：划分叶子节点和非叶子节点
》叶子节点：存储索引+指针+数据（数据占用存储空间最大）
》非叶子节点：只存索引+指针，不存数据
5.底层机制
二.索引使用规则
1.验证索引效率
在未建立索引之前，执行如下SQL语句，查看SQL的耗时
SELECT * FROM 表名称 WHERE 条件;
添加唯一索引后执行相同SQL语句
2.最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。（如果跳过最左索引则索引失效，跳过中间列则中间列索引和中间列后面索引都失效）（最左列索引只要存在则全部索引就生效与先后顺序无关）
//profession、age、status为联合索引
根据联合索引查询时书写顺序为：profession，age，status，如果不使用profession则会索引失效，不写age则age和status则失效
3.索引失效情况
3.1范围查询：联合索引中,出现范围查询>或<，范围查询右侧的列索引失效
//解决方法：尽可能使用>=或<=
3.2索引列运算：在索引列上进行运算操作,索引将失效。
3.3字符串不加引号：字符串类型字段使用时,不加引号,索引将失效。
3.4模糊查询：如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
3.5or连接的条件：用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到，只有or两侧都有索引才会生效
3.6数据分布影响：MySQL评估走索引比全表慢则索引失效
4.SQL提示
加入人为提示告诉SQL使用哪个索引
EXPLAIN SELECT * FROM 表名称 [USE/IGNORE/FORCE INDEX(索引名称)] WHERE 条件;
//use index()：建议使用哪个索引(mysql会评估如果速度快就接受建议)、ignore index()：不使用哪个索引、force index()：必须使用哪个索引
5.覆盖索引、回表查询
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少selecl*，如果查询列表中存在索引返回列中找不到的字段，会出现回表查询
6.前缀索引
当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
CREATE INDEX 索引名称 ON 表名称(字段名(n<取前n个字符构建索引>));
//前缀长度：可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高, 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
7.单列、联合索引
单列索引：一个索引只包含单个列、联合索引：一个索引包含多个列
三.面试：
1.索引创建原则，哪些字段适合添加索引
》字段内容可识别度不能低于70%,字段内数据唯一值的个数不能低于70%
//例如:一个表数据只有50行,那么性别和年龄哪个字段适合创建索引,明显是年龄,因为年龄的唯一值个数比较
多,性别只有两个选项。性别的识别度是50%。男 女
》 经常使用where条件搜索的字段
//例如user表的id name等字段。
》 经常使用表连接的字段(内连接、外连接),可以加快连接的速度。
》经常排序的字段 order by,因为索引已经是排过序的,这样一来可以利用索引的排序,加快排序查询速度。
//注意:那是不是在数据库表字段中尽量多建索引呢? 肯定是不是的。因为索引的建立和维护都是需要耗时的创建表时需要通过数据库去维护索引,添加记录、更新、修改时,也需要更新索引,会间接影响数据库的
2.避免索引失效
》全值匹配，对索引中所有列都指定具体值
//例如：名字的模糊查询就会变成走表查询
》最左前缀法则
》范围查询右边的列不能使用索引
》不要在索引列上进行运算操作
》字符串不加单引号会导致索引失效
》用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到
》如果MySQL评估使用索引比全表更慢,则不使用索引
》IN 走索引， NOT IN 索引失效
索引补充
//没有指定索引，默认是B+树索引
//hash索引只适用于等值匹配（=，in），不能使用范围匹配（between，>，<）
//hash无法利用索引完成排序
//hash索引查询效率高，通常只要一次检索就可以，效率通常高于b+tree索引
一、在InnoDB存储引擎中索引分类
聚集索引选取规则：
①如果存在主键索引，主键索引就是聚集索引
②如果不存在主键索引，将使用第一个唯一索引作为聚集索引
③如果没有主键和合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
//聚集索引，叶子节点索引下存储的就是行数据
//二级索引，叶子节点索引下存储的是对应的主键
例子：一张表id为主键字段，通过id创建聚集索引，再用name创建二级索引
回表查询：先走二级索引获取到对应主键值然后走聚集索引获取查询行数据结果
二、思考题
1.聚集索引查询和回表查询哪个效率高？
聚集索引查询效率高，回表查询需要先走二级索引查询到对应主键再走聚集索引查询
2.InnoDB主键索引的B+tree高度为多高？
高度为3：能够存储21939856个数据
SQL优化
1.insert插入数据优化
1.1批量插入：减少一个数据一个数据的插入，一次性批量插入最佳是500~1000条数据
1.2手动提交事务：默认自动提交，插入数据时频繁开启和提交事务会占时间，开启手动提交事务在插入完所有数据后统一提交事务
1.3主键顺序插入：按照主键顺序插入数据
1.4大批量数据插入：大批量数据insert语句插入性能低，使用MySQL数据库提供的load指令进行插入
//要点1：在可视化工具添加参数方法，在URL后加参数例：jdbc:mysql://127.0.0.1:3306/db3?allowLoadLocalInfile=true
//要点2：表名是用反引号（``）,文件路径和其它字符用单引号（''）
-- 新建一张表
CREATE TABLE 人物信息表(
name varchar(20),
sex varchar(2),
age int,
weight double
);
-- 插入本地文件数据
load data local infile 'D:/java_program/java_ide_program/test2_javaSEmax/day08/src/com/IOLastTest/WeightedRandomSelection/Student.txt'
into table `人物信息表` fields terminated by '-' lines terminated by '\n';
2.主键优化
2.1数据组织方式：
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)。
2.2页分裂：
当主键乱序插入(1,5,9,55,101,....)时，在插入一个序号当前存在的页已经满了，该序号会先找到所属大小区间然后放在小于该序号的前一个序号，如果也得空间不足就会将该页50%的数据放入到一个新的页然后将该序号数据放在新页。
//（页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据过大,会行溢出),根据主键排列）
2.3页合并：
当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
//（当删除一行记录时,实际上记录井没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。）
2.4主键设计原则
》尽量降低主键长度
》插入数据时，尽量选择顺序插入，选择使用主键自增
》尽量不要使用UUID做主键或者其它自然主键，如身份证号
》尽量避免对主键的修改
3.ORDER BY排序查询优化
3.1Using filesort：
通过表的索引或全表扫描，读取满足条件的行数据，然后在排序缓冲区sortbuffer中完成排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序
3.2Using index：
通过有序索引扫描直接返回有序数据，不需要额外排序操作效率高
//当创建联合索引时默认都是升序排列，使用联合索引一个字段升序另一个字段降序排序查询时，会出现using index也出现using filesort
（解决方案：在创建索引时指定一个升序一个降序CREAT INDEX 索引名 ON 表名(字段1 ASC<升序>,字段2 DESC<降序>);）
4.GROUP BY分组查询优化
在分组操作时，可以通过索引提高效率
分组操作时，索引使用也是满足最左前缀法则的
5.LIMIT分页查询优化
一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。
6.count聚合函数优化
6.1优化思路：
MylSAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;（不带where条件）
InnoDB 引擎就麻烦了,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
优化方式：自己计数
6.2count几种用法
》count(主键)：InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)。
》count(字段)：
没有not null约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加。
有not null约束;InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加。
》count(1)：InnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字1进去,直接按行进行累加。
》count(*)：InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。
效率对比：count(字段)<count(主键)<count(1)≈count(*)
7.update优化
修改的条件字段尽量为索引字段，避免行锁升级为表锁（在InnoDB引擎中，手动提交事务修改某行数据时，如果事务没提交会有锁存在，索引字段是行锁只锁住修改的那一行其他行还是可以被修改的，没有索引字段是表锁直接把修改行所在表锁住表里的数据都不可修改），锁表会导致并发性能降低
//InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。
>锁
全局锁
//全局锁是对整个数据库实例加锁，加锁后整个实例处于只读状态，后续的DML语句,DDL语句，都将被阻塞
//典型使用场景：做全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据的完整性
1.语法
Ⅰ.对当前操作数据库加全局锁
flush tables with read lock;
Ⅱ.解锁
unlock tables;
2.案例：一致性数据备份
//以命令行为演示
第一步：用终端控制台1登录mysql充当1号客户端，操作db3数据库并执行加全局锁指令
第二步：用终端控制台2登录mysql充当2号客户端，操作db3数据库执行DQL,DML语句
第三步：终端控制台3执行数据库备份语句，将db3数据库备份到指定路径下（该处为D盘下）
//保存到D盘需要以管理员身份运行控制台（不然会备份失败），然后转到MySQL的bin文件下执行指令
第四步：通过客户端释放全局锁
3.弊端
》 如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。
》如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟。
解决方法：
在InnoDB引擎中,我们可以在备份时加上参数 -- single-transaction参数来完成不加锁的一致性数据备份
mysqldump --single-transacation -uroot -p 数据库名称 > 备份文件名称.sql
表级锁
//锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度低，应用在MyISAM、InnoDB、BDB等存储引擎中
表级锁分类：表锁、元数据锁、意向锁
一.表锁
加锁：lock tables 表名 read/write;//read为读锁，write为写锁
释放锁：unlock tables; 或者 客户端断开连接
1.表共享读锁（read lock）
//客户端加了读锁，在释放锁之前，所有客户端都只能读不能写，会阻塞其它客户端执行写的语句
2.表独占写锁（write lock）
//客户端加了写锁，该客户端可以读和写，其它客户端不能读也不能写，会阻塞其它客户端的读写语句
二.元数据锁(meta data lock，MDL)
//MDL加锁过程是系统自动控制，无需显示使用，在访问一张表时自动加上
//作用：维护表元数据(元数据理解为表结构)的数据一致性，在表上有事务的时候，不可以对元数据进行写入操作（避免DML与DDL冲突，确保读写的正确性）
在MySQL5.5中引入了MDL,当对一张表进行增删改查的时候,加MDL读/写锁(共享);当对表结构进行变更操作的时候,加MDL写锁(排他)。
//SHEARED_REAF和SHEARED_WRITE为共享锁，EXCLUSIVE为排他锁
//两个客户端同时手动开启事务时访问表时会自动上MDL，其中一个客户端读时另一个客户端也可以读也可以修改数据因为共享锁之间是兼容的，如果其中一个客户端修改表结构添加一个字段时会被阻塞应为这是上的时排他锁会互斥需要另一个客户端提交事务才能执行成功
查询当前数据库表中元数据锁：
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks
三.意向锁
//为了避免DML在执行时,加的行锁与表锁的冲突,在InnoDB中引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意向锁来减 少表锁的检查。
//线程a开启事务在修改行数据时加上行锁同时加上意向锁，此时线程b加表锁时就先看意向锁是否兼容，兼容的话则直接加表锁，不兼容则会被阻塞直到线程a提交事务才结束阻塞
//意向锁之间不会互斥
1.意向共享锁(IS)
//由语句select...lock in share mode添加行锁时自动添加意向共享锁
兼容：表锁共享锁(read)
互斥：表锁排他锁(write)
2.意向排他锁(IX)
//由语句insert、update、delete、select...for update添加行锁自动添加意向排他锁
互斥：表锁共享锁(read)和表锁排他锁(write)
查看意向锁及行锁的加锁情况
select objęct_schema,object_name,index_name, lock_type,lock_mode,lock_data from performance_schema.data_locks;
行级锁
//每次操作锁住对应的行数据，锁定粒度小，发生锁冲突低，并发度高，应用在InnoDB存储引擎中
//InooDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁实现的，不是对行记录加的锁
//行级锁分类：行锁(Record Lcok)、间隙锁(Gap Lock)、临键锁(Next-Key Lock)
一.行锁
//锁定单个行记录，防止其它事务对此进行update和delete，在RC、RR隔离级别下都支持
行锁触发：
》针对唯一索引进行检索时,对已存在的记录进行等值匹配时,将会自动优化为行锁（确定不会有重复值就不需要加间隙锁）。
》InnoDB的行锁是针对于索引加的锁,不通过索引条件(索引失效、字段没有索引等)检索数据,那么InnoDB将对表中的所有记录加锁,此时就会升级为表锁。
1.共享锁(s)
//允许一个事务去读一行（即获取共享锁），阻止其它事务获得相同行数据的排他锁
2.排他锁(x)
//允许获取排他锁的事务更新数据，阻止其它事务获得相同数据集的共享锁和排他锁
查看意向锁及行锁的加锁情况
select objęct_schema,object_name,index_name, lock_type,lock_mode,lock_data from performance_schema.data_locks;
二.间隙锁
//锁定索引记录间隙(不含该记录)，确保索引间隙不变，防止其它事务在这个间隙进行insert，产生幻读，在RR隔离级别下都支持
//间隙锁的唯一目的是防止其它事务插入间隙，间隙锁之间不会互相阻止可以共存
间隙锁触发：
》索引上的等值查询(唯一索引)，给不存在的行记录加行锁时,优化为间隙锁(会锁这个不存在数据的主键后一个数据和前一个数据的间隙)。
//执行修改id=2的语句时，数据不存在，会给id=1和id=3的行记录之间上间隙锁但不会锁这两条数据
》索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询需求时,next-key lock退化为间隙锁
//临键锁的退化理解：本来是加临键锁(所有20和30要加行锁而且所有20前后间隙也要加间隙锁，20和30间隙也要加间隙锁)，但现在只要给所有20加行锁(因为重复20之间不会插入数据所以不用加间隙锁)和最后20与30之间加间隙锁，这就是临键锁退化(重复的20之间的间隙锁没了、30的行锁没了，导致临键锁缺失退化)
//退化目的：消除冗余的间隙锁和边界行锁来优化性能的机制
三.临键锁
//行锁与间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap，在RR隔离级别下支持
临键锁触发：
》索引上的范围查询(唯一索引) ，会访问到不满足条件的第一个值为止。
>视图、存储过程、触发器
视图
视图：
》虚拟表，数据在数据库中不存在，行和列数据来自自定义视图查询中使用的表(基表)，并且使用中视图是动态生成的
》只保存查询的SQL逻辑，不保存查询结果
一.视图基本使用
1.创建视图
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED|LOCAL]] CHECK OPTION]
-- 创建视图
CREATE OR REPLACE VIEW 人物信息_视图_1 AS SELECT name,sex,age FROM 人物信息表;2.查询视图
查看创建视图语句：SHOW CREATE VIEW 视图名称;
查看视图数据：SELECT * FROM 视图名称;//当作一张表查询，可以加查询条件
-- 查询
SHOW CREATE VIEW 人物信息_视图_1;
SELECT * FROM 人物信息_视图_1;3.修改视图
方式一：CREATE OR REPLACE VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED|LOCAL]] CHECK OPTION//OR REPLACE是替换的意思，创建可以不加但修改要加
方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED|LOCAL]] CHECK OPTION
-- 修改视图
-- 方式一
CREATE OR REPLACE VIEW 人物信息_视图_1 AS SELECT name,sex FROM 人物信息表;
-- 方式二
ALTER VIEW 人物信息_视图_1 AS SELECT name,sex FROM 人物信息表;4.删除视图
DROP VIEW [IF EXISITS] 视图名称;//IF EXISITS表示如果存在就删除
二.检查选项
//视图检查：
》检查更改每行时的数据是否符合视图的定义
》MySQL允许基于另一个视图创建视图，会检查依赖视图的规则与其保持一致
》MySQL中两个检查范围哦：CASCADED（级联）和LOCAL，默认值为CASCADED
1.插入数据
INSERT INTO 视图名称 VALUES(值,...);
向视图插入数据时，数据其实是插入到基表中，然后查询视图显示插入的数据，如果创建视图时存在条件，插入一条不满足条件的数据，数据会插入到基表但查询视图数据是看不到这条不满足条件的数据（解决方法：创建视图加上检查选项，当插入不满足条件数据时会报错阻止数据插入）
CREATE OR REPLACE VIEW 人物信息_视图_1
AS SELECT name,sex,age
FROM 人物信息表
WHERE age > 20 WITH CASCADED CHECK OPTION;2.CASCADED检查选项
CASCADED级联传递：如果一个视图依赖另一个视图创建同时加上了CASCADED检查选项，添加数据时会先检查是否满足该视图定义，然后会检查是否满足依赖的视图的定义(相当于给依赖的视图也加上CASCADED检查选项)
//如果视图创建没加检查选项，但依赖的视图加了检查选项，插入数据时该视图不会检查数据但依赖的视图会检查数据是否满足依赖视图的定义
//上图：向v3插入数据只要满足v2，v1的定义就会插入成功，v3不会检查
3.LOCAL检查选项
LOCAL：不会传递检查选项，先创建视图时加上LOCAL检查选项会进行数据检查，但如果依赖的视图没有加检查选项就不检查
三.更新及作用
1.视图的更新
视图中的行与基表中的行必须是一对一的关系(聚合函数、分组等不是一对一)，视图才可以插入数据和更新
2.作用
2.1简单：简化用户对数据的理解和操作
2.2安全：通过视图同户只能查询和修改他们能见到的数据
2.3数据独立：帮助用户屏蔽真实表结构变化带来的影响
四.案例
1.案例一：
/* 为了保证数据库表的安全性,开发人员在操作tb_user表时,只能看到的用户的基本字段,屏蔽手机号和邮箱两个
字段。*/
-- 创建视图时只包含基础字段
CREATE VIEW tb_user_view AS SELECT id,name,age,gender FROM tb_user;
-- 查询视图时就只能看见基本信息
SELECT * FROM tb_user_view;2.案例二：
-- 查询每个学生所选修的课程(三张表联查),这个功能在很多的业务中都有使用到,为了简化操作,定义一个视图
-- 学生表：id name
-- 课程表：id name
-- 学生课程关系表(中间表)：id studentid courseid
-- 三表联查sql语句
SELECT s.name,c.name
FROM sudent s,course c,student_course sc
WHERE s.id=sc.id AND c.id=sc.id;
-- 定义到一个视图中
CREATE VIEW tb_course_view AS
SELECT s.name student_name,c.name course_name -- 取别名防止报错：重复字段名name
FROM sudent s,course c,student_course sc
WHERE s.id=sc.id AND c.id=sc.id;
-- 以后只需要查视图就能查学生课程信息
SELECT * FROM tb_course_view;
存储过程
存储过程：事先经过编译并存储在数据库中的一段SQL语句的集合，可以直接调用
特点：封装、复用，可以接收参数也可以返回数据、减少网络交互效率提高
一.基本使用
1.创建
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
-- SQL语句(可一条也可多条)
END;
-- 命令行写法
-- 先修改sql语句结束符
DELIMITER !!
-- 然后执行创建存储过程语句
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
-- SQL语句(可一条也可多条)
END!! //在命令行操作此语句会出现报错，因为在BEGIN后的sql语句存在分号(命令行中见到分号代表sql语句结束)导致END关键字没被识别（解决方案：通过delimiter指定失去了语句结束符DELIMITER 自定义符号;//改了结束符没改回分号所有sql语句结束语句都会是自定义的符号）
2.调用
CALL 名称([参数列表]);
3.查看
查看指定数据库所有存储过程：SELECT* FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='数据库名称';
查看某个存储过程的定义：SHOW CREATE PROCEDURE 存储过程名称
4.删除
DORP PROCEDURE[IF EXISTS] 存储过程名称;
查看所有系统变量：SHOW [SESSION|GLOBAL] VARIABLES;
使用模糊匹配查找变量：SHOW [SESSION|GLOBAL] VARIABLES LIKE '模糊匹配格式';
查看指定变量的值：SELECT @@[SESSION|GLOBAL] 系统变量名;
Ⅱ.设置系统变量
SET [SESSION|GLOBAL] 系统变量名 = 值;
SET @@[SESSION|GLOBAL] 系统变量名 = 值;
二.变量
1.系统变量
//属于服务器层面，分为全局变量(GLOBAL)、会话变量(SESSION)，默认级别SESSION
Ⅰ.查看系统变量
查看所有系统变量：SHOW [SESSION|GLOBAL] VARIABLES;
使用模糊匹配查找变量：SHOW [SESSION|GLOBAL] VARIABLES LIKE '模糊匹配格式';
查看指定变量的值：SELECT @@[SESSION|GLOBAL] 系统变量名;
Ⅱ.设置系统变量
SET [SESSION|GLOBAL] 系统变量名 = 值;
SET @@[SESSION|GLOBAL] 系统变量名 = 值;
2.用户自定义变量
//根据需求用户自己定义的变量，不需要提前声明，在用的时候直接用“@变量名”使用即可，作用域为当前连接（当前会话）
Ⅰ.赋值：
SET @自定义变量名1 = 值 [,@自定义变量名2 = 值] ... ;
SET @自定义变量名1 := 值 [,@自定义变量名2 := expr] ... ;
SELECT @自定义变量名1 := 值 [,自定义变量名2 := expr] ... ;
SELECT 字段名 INTO @变量名 FROM 表名;//在赋值是推荐使用”:=“，因为在MySQL中”=“也是判断符
Ⅱ.使用
SELECT @变量名;//直接使用但没赋值时，获取到的值是NULL
3.局部变量
//局部生效的变量，访问之前需要DECLARE声明，可用作存储过程的局部变量和输入参数，局部变量作用范围是在其声明的BEGIN...AND块
Ⅰ.声明
DECLARE 变量名 变量类型[DEFAULT...];//有默认值就可以用DEFAULT关键字指定
Ⅱ.赋值
SET 变量名 = 值;
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名...;三.流程控制语句
1.if判断
Ⅰ.语法
IF 条件1 THEN
..满足条件1所需执行的SQL逻辑..
ELSEIF 条件2 THEN -- 可选
....
ELSE -- 可选
....
END IF; Ⅱ.需求练习
-- 创建存储过程
CREATE PROCEDURE p1()
BEGIN
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;
-- 查询成绩等级
SLECT result;
END;
-- 调用存储过程
CALL p1;2.参数
Ⅰ.语法
-- 用法
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
....
END;Ⅱ.案例
-- 创建存储过程
CREATE PROCEDURE p2(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 p2(88<传入参数>,@result<创建一个用户自定义变量接收结果>);
SELECT @result #查询结果 
-- 创建存储过程
CREATE PROCEDURE p3(INOUT score double)
BEGIN
SET score := score * 0.5;
END;
-- 调用存储过程
SET @score = 78;
CALL p3(@score);
SELECT @score;3.case
Ⅰ.语法
-- 语法一
CASE 表达式
WHEN 值1 THEN sql语句1
[WHEN 值2 THEN sql语句2]
...
[ELSE sql语句]
END CASE;
-- 语法二
CASE
WHEN 条件表达式1 THEN sql语句1
[WHEN 条件表达式2 THEN sql语句2]
...
[ELSE sql语句]
END CASE;Ⅱ.案例
-- 创建存储过程
CREATE PROCEDURE p4(IN month int)
BEGIN
DECLARE result varchar(10);
CASE
WHEN month >=1 AND month <=3 THEN SET result := '第一季度'
WHEN month >=4 AND month <=6 THEN SET result := '第二季度'
WHEN month >=7 AND month <=9 THEN SET result := '第三季度'
WHEN month >=10 AND month <=12 THEN SET result := '第四季度'
ESLE SET result := '非法参数';
END CASE;
SELECT concat('输入的月份为：',month,'所属为',result);
END;
-- 调用存储过程
CALL p4(1);4.循环
4.1.while
//有条件的循环控制语句，满足条件后在执行循环体中的sql语句
Ⅰ.语法
#先判定条件，如果为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑(循环体)...
END WHILE;Ⅱ.案例
-- 创建存储过程
CREATE PROCEDURE p5(IN n int)
BEGIN
DECLARE sum int DEFAULT 0;
WHILE n>0 DO
SET sum := sum + n;
SET n = n - 1;
END WHILE;
SELECT sum;
END;
-- 调用
CALL p5(10);4.2.repeat
//有条件的循环控制语句，当条件满足时退出循环,不管怎样都会先执行一次循环体
Ⅰ.语法
#先执行一次逻辑，然后判定条件是否满足，满足则退出，不满足则继续执行循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;Ⅱ.案例
-- 创建存储过程
CREATE PROCEDURE p6(IN n int)
BEGIN
DECLARE sum int DEFAULT 0;
REPEAT
SET sum := sum + n;
SET n := n - 1;
UNTIL n <= 0;
END REPEAT;
SELECT sum;
END;
-- 调用
CALL p6(10);4.3.loop
//需要增加退出循环条件，不然就是死循环
LEAVE:配合循环使用，退出循环
ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环
Ⅰ.语法
[BEGIN_LABEL:]LOOP
SQL逻辑...
END LOOP [END_LABEL];
#[BEGIN_LABEL][END_LABEL]分别为LOOP开始和结束的标记//要点：LEAVE和ITERATE必须结合标签使用
Ⅱ.案例
-- 创建存储过程
CREATE PROCEDURE p7(IN n int)
BEGIN
DECLARE sum int DEFAULT 0;
Evaluate:LOOP
IF N <= 0 THEN
LEAVE Evaluate -- 满足条件则退出循环
END IF;
SET sum := sum + n;
SET n := n - 1;
END LOOP Evaluate;
SELECT sum;
END;
-- 调用
CALL p7(10);
-- 创建存储过程
CREATE PROCEDURE p8(IN n int)
BEGIN
DECLARE sum int DEFAULT 0;
Evaluate:LOOP
IF n <= 0 THEN
LEAVE Evaluate -- 满足条件则退出循环
ELSE IF n % 2 = 1
SET n := n - 1;
ITERATE Evaluate; -- 满足条件跳过下面的循环体，执行下次循环
END IF;
SET sum := sum + n;
SET n := n - 1;
END LOOP Evaluate;
SELECT sum;
END;
-- 调用
CALL p8(10);5.游标cursor
//变量只能接收单行单列的数据，如果想接收结果集或多行多列数据则使用游标
游标：是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE,
Ⅰ.声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
Ⅱ.打开游标
OPEN 游标名称;
Ⅲ.获取游标记录(使用前必须打开游标)
FETCH 游标名称 INTO 变量[,变量];//通过循环获取
Ⅳ.关闭游标
CLOSE 游标名称;
Ⅴ.案例
-- 创建存储过程
CREATE PROCEDURE p9(IN uage int)
BEGIN
#创建局部变量、游标
DECLARE uname vachar(100);
DECLARE upro vachar(100);
DECLARE u_corsor CORSOR FOR SELECT name,profession FROM tb_user WHERE age <= uage;
#创建handler
DECLARE EXIT HANDLER FOR '02000' CLOSE u_corsor;
#创建新表
CREATE TABLE tb_user_pro(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(100),
profession varchar(100)
);
#打开游标
OPEN u_corsor;
#获取游标记录并插入到新表
WHILE ture DO -- 死循环，当游标所有信息都被保存没有记录时程序会出错，靠handler解决
FETCH u_corsor INTO uname,upro;
INSERT INTO tb_user_pro VALUES (null,uname,upro);
END WHILE;
#关闭游标
CLOSE u_corsor;
END;
-- 调用
CALL p9(40);//要点1：必须先声明局部变量再声明游标，顺序不能反不然会报错
//要点2：获取游标数据时最后会出错，靠handler解决
//要点3：获取游标数据时变量顺序与游标声明时查询语句字段列表顺序一致
6.条件处理程序handler
//用来定义在流控制结构执行过程中遇到的问题时相应的处理步骤
Ⅰ.语法
DECLARE 处理动作 HANDLER FOR 条件值1[,条件值2]... 满足条件时要处理的的语句;
处理动作：
CONTINUE:继续执行当前程序
EXIT:终止当前程序
条件值：
SQLSTATE 报错状态码:状态码,如02000
SQLWARNING:所有以01开头的SQLSTATE代码的简写
NOT FOUND:所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION:所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写 存储函数
//存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的
1.语法
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值数据类型 [存储函数特性声明] -- 8.0版本必须指定特性
BEGIN
-- SQL语句
RETURN ...;
END;
存储函数特性说明:
· DETERMINISTIC（确定性函数）:相同的输入参数总是产生相同的结果
· NO SQL:函数内不包含5QL语句。
· READS SQL DATA:函数内只包含读取数据的语句,不包含写入数据的语句。2.案例
-- 创建存储函数
CREATE PROCEDURE fun1(IN n int)
RETURNS int DERERMINISTIC
BEGIN
DECLARE sum int DEFAULT 0;
WHILE n>0 DO
SET sum := sum + n;
SET n = n - 1;
END WHILE;
RETURN sum;
END;
-- 调用存储函数
SELECT fun(10);触发器
//与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的sql语句集合
//可以协助应用在数据库端确保数据的完整性，日志记录，数据校验
//使用别名OLD(原来记录内容)和NEW(新的记录内容)来引发触发器中发生变化的记录内容，目前触发器只支持行级触发<操作几条行数据就触发几次>，不支持语句触发
1.语法
Ⅰ.创建
CREATE TRIGGER 触发器名称
BEFORE/AFTER INSERT/UPDATE/DELETE -- BEFORE:在语句执行前触发 AFTER:之后触发
ON 表名称 FOR EACH ROW -- 行级触发器
BEGIN
-- 触发器逻辑实现
END;Ⅱ.查看
SHOW TRIGGERS;
Ⅲ.删除
DROP TRIGGER [数据库名称.]触发器名称;//没指定数据库，默认当前数据库
2.案例需求
//COMMENT是MySQL中用于添加注释、说明和文档的关键字
Ⅰ.insert触发器
-- insert触发器
CREATE TRIGGER tb_user_insert_trigger
AFTER INSERT ON user FOR EACH ROW
BEGIN
INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
(null,'insert',NOW(),new.id,
CONCAT('插入数据内容为：id=',new.id,'name=',new.name,'password=',new.password));
END;
#new.字段代表新插入数据的指定字段值
Ⅱ.update触发器
-- update触发器
CREATE TRIGGER tb_user_update_trigger
AFTER UPDATE ON user FOR EACH ROW
BEGIN
INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
(null,'update',NOW(),new.id,
CONCAT('修改前的旧数据内容为：id=',old.id,'name=',old.name,'password=',old.password,
'修改后的新数据内容为：id=',new.id,'name=',new.name,'password=',new.password));
END;
Ⅲ.delete触发器
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
31
0- 0
已为社区贡献1条内容
所有评论(0)