![图片[1]-MySQL数据库知识总结-墨初小屋](https://s2.loli.net/2022/02/12/2PAxpkbSMvIdyfn.jpg)
一、基本操作
1. 连接数据库操作
/*连接mysql*/
mysql -h 地址 -P 端口 -u 用户名 -p 密码
例如: mysql -h 127.0.0.1 -P 3306 -u root -p ****
/*退出mysql*/
exit;2. 数据库操作
#数据库操作
/*关键字:create 创建数据库(增)*/
create database 数据库名 [数据库选项];
例如: create database test default charset utf8 collate utf8_bin;
/*数据库选项:字符集和校对规则*/
字符集:一般默认utf8;
校对规则常见: ⑴ci结尾的:不分区大小写 ⑵cs结尾的:区分大小写 ⑶bin结尾的：二进制编码进行比较
/*关键字:show 查看当前有哪些数据库(查)*/
show databases;
/*查看数据库的创建语句*/
show create database 数据库名;
/*关键字:alter 修改数据库的选项信息(改)*/
alter database 数据库名 [新的数据库选项];
例如: alter database test default charset gbk;
/*关键字:drop 删除数据库(删)*/
drop database 数据库名;
/*关键字:use 进入指定的数据库*/
use 数据库名;3. 表的操作
#表的操作
/*关键字:create 创建数据表(增)*/
create table 表名(
字段1 字段1类型 [字段选项],
字段2 字段2类型 [字段选项],
字段n 字段n类型 [字段选项]
)表选项信息;
例如: create table test(
id int(10) unsigned not null auto_increment comment 'id',
content varchar(100) not null default '' comment '内容',
time int(10) not null default 0 comment '时间',
primary key (id)
)engine=InnoDB default charset=utf8 comment='测试表';
--ENGINE 设置存储引擎,CHARSET 设置编码, comment 备注信息/*关键字:show 查询当前数据库下有哪些数据表(查)*/
show tables;
/*关键字:like 模糊查询*/
通配符：_可以代表任意的单个字符，%可以代表任意的字符
show tables like '模糊查询表名%';
/*查看表的创建语句*/
show create table 表名;
/*查看表的结构*/
desc 表名;
/*关键字:drop 删除数据表(删)*/
drop table [if exists] 表名
例如: drop table if exists test;
/*关键字:alter 修改表名(改)*/
alter table 旧表名 rename to 新表名;
/*修改列定义*/
/*关键字:add 增加一列*/
alter table 表名 add 新列名 字段类型 [字段选项];
例如: alter table test add name char(10) not null default '' comment '名字';
/*关键字:drop 删除一列*/
alter table 表名 drop 字段名;
例如: alter table test drop content;
/*关键字:modify 修改字段类型*/
alter table 表名 modify 字段名 新的字段类型 [新的字段选项];
例如: alter table test modify name varchar(100) not null default 'admin' comment '修改后名字';
/*关键字:first 修改字段排序,把某个字段放在最前面*/
alter table 表名 modify 字段名 字段类型 [字段选项] first;
例如: alter table test modify name varchar(100) not null default 'admin' comment '最前面' first;
/*关键字:after 修改字段排序,字段名1放在字段名2的后面*/
alter table 表名 modify 字段名1 字段类型 [字段选项] after 字段名2;
例如: alter table test modify name varchar(100) not null default 'admin' comment 'time字段后面' after time;
/*关键字:change 重命名字段*/
alter table 表名 change 原字段名 新字段名 新的字段类型 [新的字段选项];
例如: alter table test change name username varchar(50) not null default '' comment '用户名字';
/*修改表选项*/
alter table 表名 表选项信息;
例如: alter table test engine Myisam default charset gbk; --修改存储引擎和修改表的字符集4. 数据操作
增加 insert
删除 delete
修改 update
查询 select
#数据操作
/*关键字:insert 插入数据(增)*/
insert into 表名(字段列表) values(值列表);
--例子
insert into user(id,name,age) values(1,'admin_a',50);
insert into user(name) values('admin_b');
/*关键字:select 查询数据(查)*/
select *[字段列表] from 表名[查询条件];
/*关键字:delete 删除数据(删)*/
delete from 表名[删除条件];
例如: delete from user where age<1;--删除age小于1数据
/*关键字:update 修改数据(改)*/
update 表名 set 字段1=新值1,字段n=新值n [修改条件];
例如: update user set age=100 where name='admin_a';二、数据类型
大致分为三大类：数值，日期/时间，字符串
-- 常用数据类型
int, float, double
date, time, year, datetime
char, varchar, text1. 数值类型
![图片[2]-MySQL数据库知识总结-墨初小屋](https://s2.loli.net/2022/02/12/TKHoJDWyPLQk7G9.png)
2. 日期和时间类型
![图片[3]-MySQL数据库知识总结-墨初小屋](https://s2.loli.net/2022/02/12/1BCGNfcxrMzVw3i.png)
3. 字符串类型
![图片[4]-MySQL数据库知识总结-墨初小屋](https://s2.loli.net/2022/02/12/j9rUiNBSudWkmVX.png)
char是定长的，索引效率很高，char(10)，不论你存储的数据是否达到了10个字节，都要占去10个字节的空间，当你输入的字符小于10时会在后面补空值，而大于10时会截取超出的字符
varchar的存储效率没有CHAR高，但在存储的字符串是变长时，varchar更加节约空间
如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar。
因为一般情况下，英文字符占一个字节，汉字占两个字节，同时存在容易造成混乱。nchar和nvarchar表示存储Unicode数据类型的字符，其所有的字符都用两个字节表示，但在存储英文时数量上有些损失。
三、建表约束
- 建表的时候就添加约束
- alter … add …
- alter … modify …
- alter … drop …
1. 主键约束
每个表只能定义一个主键
唯一性原则：非空且不重复
/*单一主键*/
create table user(
id int primary key,
name varchar(20)
);
/*联合主键 - 只要联合的主键值加起来不重复就行，但都得非空*/
create table user2(
id int,
name varchar(20),
sex char,
primary key(id, name)
);
-- 建表后对主键约束的添加, 删除, 与修改
create table user3(
id int,
name varchar(20)
);
//alter 修改 modify 修改字段
alter table user4 add primary key(id);
alter table user4 drop primary key;
alter table user4 modify id int primary key;
alter table table1 change column1 column1 varchar(100) DEFAULT 1.2 COMMENT '注释';2. 自增约束
一个表只能有一个自增约束，因为一个表只有一个维护自增值的变量
自增约束的列只能是整数列，且必须是键列（主键，唯一键，外键），实际中一般是主键自增最多
create table user4(
id int primary key auto_increment,
name varchar(20)
);
insert into user4 (name) values('张三');
insert into user4 (name) values('张三');3. 唯一约束
可以有多个，约束修饰的字段的值不可以重复
唯一约束的列允许有空值，但是只能有一个空值
create table user5(
id int unique,
name varchar(20)
);
-- 组合在一起不重复，单个得分开加
create table user6(
id int,
name varchar(20),
unique(id, name)
);4. 非空约束
修饰的字段不能为空
create table user7(
id int,
name varchar(20) not NULL
);5. 默认约束
插入字段值的时候，未传值则使用默认值
create table user8(
id int primary key auto_increment,
name varchar(20),
age int default 10
);
insert into user8(name) values('111');
insert into user8(name) values('111');6. 外键约束
用来建立主表与从表的关联关系，约束两个表中数据的一致性和完整性
- 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
- 必须为主表定义主键。
- 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
- 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
- 外键中列的数目必须和主表的主键中列的数目相同。
- 外键中列的数据类型必须和主表主键中对应列的数据类型相同。
-- 班级表（主表）
create table classes(
id int primary key,
name varchar(20)
);
-- 学生表（从表/副表）
create table students(
id int primary key,
name varchar(20),
class_id int,
foreign key(class_id) references classes(id)
);
-- 主表 classes 中没有的数据值，在副表中是不可以使用的
-- 主表中的记录被副表引用时，是不可以被删除的四、设计范式
范式 ( NF )：Normal Format，是一种离散数学中的知识，是为了解决数据的存储与优化的问题，保存数据的存储之后，凡是能够通过关系寻找出来的数据，坚决不再重复存储，终极目标是为了减少数据的冗余
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。一般说来，数据库只需满足第三范式(3NF）就行了
1. 第一范式 – 1NF
所有属性都是不可分割的原子值
（确保每一列不可再分）
- 在设计表存储数据时候，如果表中设计的字段存储的数据，在取出来使用之前还需要额外的处理（拆分），那么表的设计不满足第一范式
- 第一范式要求字段具有原子性、不可再分割
-- 字段值还可以继续拆分的，不满足第一范式
create table student(
id int primary key,
name varchar(20),
address varchar(30)
);
insert into student values(1, '张三', '中国四川省成都市金牛区');
insert into student values(2, '李四', '中国四川省成都市武侯区');
insert into student values(3, '王五', '中国四川省成都市高新区');
-- 应该满足以下形式
create table student2(
id int primary key,
name varchar(20),
country varchar(30),
province varchar(30),
city varchar(30),
details varchar(30)
);
insert into student2 values(1, '张三', '中国', '四川省', '成都市', '金牛区');
insert into student2 values(2, '李四', '中国', '四川省', '成都市', '武侯区');
insert into student2 values(3, '王五', '中国', '四川省', '成都市', '高新区');2. 第二范式 – 2NF
在第一范式的基础上，要求非主属性都要和码有完全依赖关系
（确保表中每一列都和主键有关）
- 在数据表设计过程中，如果有复合主键（多字段主键），且表中有字段并不是由整个主键来确定，而是依赖于主键中的某个字段（主键的部分）
- 存在字段依赖主键的部分的问题，称之为部分依赖
- 第二范式就是解决表设计不允许出现部分依赖
-- 除主键以外的其他列，只依赖于主键的部分字段，部分依赖
create table myorder(
product_id int,
customer_id int,
product_name varchar(20),
customer_name varchar(20),
primary key(product_id, customer_id)
);
-- 拆表，分成多个表之后就满足第二范式
create table myorder(
order_id int primary key,
product_id int,
customer_id int
);
create table product(
id int primary key,
name varchar(20)
);
create table customer(
id int primary key,
name varchar(20)
);3. 第三范式 – 3NF
任何非主属性不依赖于其它非主属性
（确保每一列都和主键列直接相关，而不是间接相关）
- 要满足第三范式、必须满足第二范式
- 理论上讲，应该一张表中的所有字段都应该直接依赖于主键，如果表设计中存在一个字段，并不直接依赖主键，而是通过某个非关键字段依赖，最终实现依赖主键,把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖
- 第三范式就是解决传递依赖的问题
-- 除开主键列的其他列之间有传递依赖关系，不满足
create table myorder(
order_id int primary key,
product_id int,
customer_id int,
customer_phone varchar(15)
);
-- 应该把phone提出来
create table myorder(
order_id int primary key,
product_id int,
customer_id int
);
create table customer(
id int primary key,
name varchar(20),
phone varchar(15)
);4. BC范式 – BCNF
在 3NF 的基础上消除任何属性（主属性和非主属性）对关系键的部分函数依赖和传递函数依赖
不管这些A、B、C属性是主属性还是非主属性，不存在部分函数依赖 (AB)–>C , B–>C，也不存在传递函数依赖A–>B , B–>C，就是BC范式
![图片[5]-MySQL数据库知识总结-墨初小屋](https://s2.loli.net/2022/02/13/YJhLby3cprtkev2.png)
五、查询
1. 基本语句
SELECT column_name,column_name
FROM table_name [WHERE Clause][LIMIT N][ OFFSET M]在查询过程中的执行顺序
from > where > group（含聚合）> having > order > select完整书写顺序
SELECT column_name,column_name
FROM table_name [WHERE Clause][GROUP BY ][HAVING Clause][ORDER BY ][LIMIT N][ OFFSET M]2. 别名 – AS或空格加别名
SELECT id AS 学号, name 姓名 FROM exam;3. 去重 – DISTINCT
SELECT DISTINCT math FROM exam;4. 排序 – ORDER BY
ASC升序，DESC降序，默认ASC
NULL 数据排序，视为比任何值都小
可以对多个字段进行排序，排序优先级随书写顺序
-- 查询所有人的成绩，按照数学成绩排序，从高到低，如果数学成绩相等，按照语文成绩排序，从低到高，如果语文成绩相等，按照id排序，从高到底
select * from exam
order by math desc, chinese asc, id desc;5. 分组 – GROUP BY
根据一个或多个字段对查询结果进行分组
-- 统计学生出现的次数，并按次数的升序排列
select name, count(*) from exam group by name order by count(name);6. 条件查询 – WHERE
WHERE条件可以使用表达式，但不能使用别名，条件为true才返回
AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分
![图片[6]-MySQL数据库知识总结-墨初小屋](https://s2.loli.net/2022/02/12/AdaugGw5VYpZ4R3.png)
① BETWEEN … AND …
between语句范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
-- 查询语文成绩在 [80, 90] 分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
-- 使用 AND 也可以实现
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;② IN……OR……
范围查询 IN (option, …) 如果是 option 中的任意一个，返回 TRUE(1)
select * from exam_result where name in ('孙悟空', '唐三藏');
select * from exam_result where name = '孙悟空' or name = '唐三藏';③ NULL
不能使用 = 或者 != 判断，null值在where中被视为false，一般就以下三种表达形式
-- 查询已经登记过 qq 邮箱的同学
select * from student where qq_mail is not null;
select * from student where qq_mail is null;
select * from student where qq_mail <=> null;④ 模糊查询：LIKE
% 表示任意多个（包括 0 个）任意字符
_ 表示任意一个字符
-- % 匹配任意多个（包括 0 个）字符
SELECT name FROM exam_result WHERE name LIKE '孙%';-- 匹配到孙悟空、孙权
-- _ 匹配严格的一个任意字符
SELECT name FROM exam_result WHERE name LIKE '孙_';-- 匹配到孙权7. 分页查询 – LIMIT
偏移量是从0号开始的
-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;8. 聚合查询 – COUNT…
![图片[7]-MySQL数据库知识总结-墨初小屋](https://s2.loli.net/2022/02/12/3UDalQXAhGPLBFv.png)
9. 条件过滤 – HAVING子句
having是在分组后对数据进行过滤，where是在分组前对数据进行过滤
聚合语句在having之前，在where子句之后执行，所以having后面可以使用聚合函数，where后面不可以使用聚合函数
在查询过程中执行顺序：from > where > group（含聚合）> having > order > select
-- 通过学号筛选出总分大于400的学生
SELECT
id,
name,
SUM(score) AS total
FROM
exam
GROUP BY id
HAVING total > 400;10. 连接查询（联合查询）
没有条件的联表，结果是一个笛卡尔积， 有效数据，需要通过一些条件过滤，关联查询可以对关联表使用别名
-- 内连接
join (inner join)
-- 外连接
left join (left outer join) 左连接
right join (right outer join) 右连接
full join (full outer join) 完全外连接
![图片[8]-MySQL数据库知识总结-墨初小屋](https://s2.loli.net/2022/02/12/ekHCRIU51xmZwSf.png)
① 内连接
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;② 外连接
外连接分为左外连接和右外连接
联合查询，如果左侧的表完全显示我们就说是左外连接，右侧的表完全显示我们就说是右外连接
-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;③ 自连接
当您想将表中行与同一表中的其他行组合时，可以使用自连接
要执行自连接操作必须使用表别名来帮助MySQL在单个查询中区分左表与同一张表的右表
SELECT c1.cust_id ,c1.cust_name ,c1.cust_adress
FROM customers as c1 ,customers as c2
where c2.cust_company = '爱艺奇'
and c1.cust_name = c2.cust_name④ 子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询，常常跟在from、where后面
单行子查询：返回一行记录的子查询，子查询的结果只有一行一列，可以把这个结果当成具体数值来使用
多行子查询：返回多行记录的子查询，只有一列，但有很多行，可以当成一组数据，配合in、max、min等方法使用
子查询必须在小括号里，写在from里面子查询必须加上别名
-- 查询部门人数 < 3 人的部门
select depart_id, count(*) from employee group by depart_id having count(*) < 3;
--子查询：
select
depart_id, cnt
from (select depart_id, count(*) cnt from employee group by depart_id) t
where t.cnt < 3;子查询中的IN和EXISTS
EXISTS用于检查子查询是否至少会返回一行数据，返回值True就保留这行的结果，false不保留
查找表中所有重复的电子邮箱
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
-- 方法 1 利用聚合查询完成我们的操作
select Email from Person group by Email having count(*) > 1;
-- 方法 2 利用子查询的 in 完成
select distinct Email from Person p1 where Email in (select Email from Person p2 where p1.Id != p2.Id);
-- 方法 3 利用子查询的 exists 完成
select distinct Email from Person p1 where exists (
select Email from Person p2 where p1.Id != p2.Id and p1.Email = p2.Email
);
- IN:在范围内的值，只要有就true
- ALL: 与子查询返回的所有值比较为true 则返回true
- ANY:与子查询返回的任何值比较为true 则返回true
- SOME:是ANY的别称,很少用
/*
举个例子: 表1(table1)中 列r1有 1 2 8 表2(table2)中 列r2有 2 3
*/
-- 用IN举例子 返回的结果是 2
SELECT r2 FROM table2 WHERE r2 IN (SELECT r1 FROM table1);
-- ALL例子 没有返回结果 因为2 3没有都大于或等于1 2 8 所有的数值，也就是子查询中的所有值都成立则成立
SELECT r2 FROM table2 WHERE r2 >= ALL(SELECT r1 FROM table1);
-- ANY例子 返回结果是2 3 只要子查询中返回的值 有一个成立则成立
SELECT r2 FROM table2 WHERE r2 >= ANY(SELECT r1 FROM table1); 11. 控制语句 – CASE WHEN
简单函数：枚举这个字段所有可能的值
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
-- 例子
SELECT
course as '课程',
CASE course
WHEN '高数' THEN
'难'
WHEN '英语' THEN
'一般'
WHEN '物理' THEN
'简单'
ELSE
'一般'
END '难度'
FROM
class;搜索函数：搜索函数可以写判断，并且搜索函数同时满足时只会返回第一个符合条件的值，其他case被忽略
CASE WHEN [expr] THEN [result1]…ELSE [default] END
-- 例子1
SELECT
name '姓名',
age '年龄',
CASE
WHEN age < 18 THEN '少年'
WHEN age < 30 THEN '青年'
WHEN age >= 30 AND age < 50 THEN '中年'
ELSE '老年'
END '状态'
FROM
resident;
-- 例子2
SELECT
sno AS '学号',
sum(credit) AS Total,
CASE
WHEN sum(credit) > 30 THEN '优秀' -- 注意这里不能用Total，select过程在最后，但是having筛选可以用
WHEN sum(credit) BETWEEN 25 AND 30 THEN '良好' -- [25, 30]
WHEN sum(credit) > 20 AND sum(credit) < 25 THEN '正常'
ELSE '应努力'
END '等级'
FROM course, sc
WHERE sc.cno = course.cno
GROUP BY sno12. 合并查询 – UNION
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符UNION和UNION ALL，使用时前后查询的结果集中，字段需要一致
union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行
union all该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行
六、存储例程
![图片[9]-MySQL数据库知识总结-墨初小屋](https://img-blog.csdnimg.cn/20200314084903529.png)
1. 变量的定义
在调用存储过程时，以declare声明的变量都会被初始化为null，变量名不区分大小写
而会话变量（即@开头的变量）则不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量
定义后的变量采用set语句进行赋值
-- 用户变量，可以在一个会话的任何地方声明，作用域是整个会话
set @var=1;
-- 存储过程变量，只能在存储过程中使用，或者是给存储传参数
declare var int default 0;- 局部变量
- 用户变量
- 会话变量
- 全局变量
会话变量和全局变量叫系统变量
一、局部变量，只在当前begin/end代码块中有效
局部变量一般用在sql语句块中，比如存储过程的begin/end。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了。declare语句专门用于定义局部变量，可以使用default来说明默认值。set语句是设置不同类型的变量，包括会话变量和全局变量
declare var_name [, var_name]... data_type [ DEFAULT value ];二、用户变量，在客户端链接到数据库实例整个过程中用户变量都是有效的
-- 这里要使用set语句创建并初始化变量，直接使用@num变量
第一种用法：set @num=1; 或set @num:=1;
第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……，
-- select语句一般用来输出用户变量，比如select @变量名，用于输出数据源不是表格的数据三、会话变量
服务器为每个连接的客户端维护一系列会话变量。在客户端连接数据库实例时，使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样，仅限于当前连接。当当前连接断开后，其设置的所有会话变量均失效
set session var_name = value;
set @@session.var_name = value;
set var_name = value; #缺省session关键字默认认为是session
-- 查看所有的会话变量
show session variables;
-- 查看一个会话变量有如下三种方式
select @@var_name;
select @@session.var_name;
show session variables like "%var%";四、全局变量
全局变量影响服务器整体操作。当服务器启动时，它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。要想更改全局变量，必须具有super权限。全局变量作用于server的整个生命周期，但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效，需要更改相应的配置文件
-- 要设置一个全局变量，有如下两种方式：
set global var_name = value; //注意：此处的global不能省略。根据手册，set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION
set @@global.var_name = value; //同上
-- 查看所有的全局变量
show global variables;
-- 要想查看一个全局变量，有如下两种方式：
select @@global.var_name;
show global variables like “%var%”;2. 流程控制
-- 条件语句
if 布尔表达式 then
处理语句
elseif 布尔表达式 then
处理语句
else
处理语句
end if;
-- 循环语句
-- 方式1
while 布尔表达式 do
循环语句
end while;
-- 方式2
repeat
循环语句
until 布尔表达式 end repeat;
-- 方式3
循环标记:loop
循环语句
leave 循环标记; -- leave语句一般和判断语句一起用，用来退出循环
end loop 循环标记3. 存储过程 – PROCEDURE
由过程化SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可
CREATE PROCEDURE 存储过程名称([参数类型 参数名 数据类型]组成的参数列表)
-- 存储过程的参数类型有三种：in out inout
-- in：实参可为常量/变量；实参为变量时，只能向外赋值，不能被赋值(即in型参数的值不会被存储过程更改)
-- out:实参只能是变量；实参不能向外赋值，只能被赋值
-- inout：实参只能是变量；实参既可向外赋值，又可被赋值
BEGIN
需要执行的语句，可包含内容和存储函数是一样的。
END
-- 1.创建存储过程
delimiter ||
create procedure test(in in_count int)
begin
declare count int default 0;
declare sum int default 0;
while count < in_count do
set sum = sum + count;
set count = count + 1;
end while;
select sum;
end ||
delimiter ;
-- 2.调用存储过程
call test(10);
-- 3.查看储存过程
-- 查看定义的存储过程有哪些
show procedure status [like 需要匹配的函数名]
-- 查看某个存储过程的详细定义
show create procedure test
-- 4.删除存储过程
drop procedure test;
或者drop procedure if exists test;
-- 5.输入输出参数
delimiter ||
create procedure query_2(in in_ename varchar(100), out out_eamount int)
begin
select eamount into out_eamount from let where ename = in_ename;
end ||
delimiter ;
call query_2("广州美能达电器有限公司", @a);
select @a as eamount;
drop procedure query_2;
4. 存储函数 – FUNCTION
函数和存储过程都是持久性存储模块，但是函数必须指定返回的类型
CREATE FUNCTION 存储函数名称([参数名 数据类型]组成的参数列表) comment ‘函数的注释说明’
RETURNS 返回值类型
BEGIN
函数体内容
-- 函数体中，每条语句都要以分号;结尾
-- 函数体内容可以包含：
-- 1. SQL语句
-- 2. 变量定义(此时变量的定义用declare，不加@)和赋值
-- 3. 流程控制语句
END
-- 1.创建函数
delimiter $$
create function test(quantity int(10))returns int(10)
begin
while quantity mod 12 > 0 do
set quantity = quantity + 1;
end while;
return quantity;
end $$
delimiter ;
-- 注意：创建函数之前必须更改系统设置,否则无法创建函数
set global log_bin_trust_function_creators = TRUE;
-- 2.调用函数
select test(10);
select test(24);
select test(222);
-- 3.查看函数
-- 查看定义的函数有哪些
show function status [like 需要匹配的函数名]
-- 查看某个函数的具体定义
show create function 函数名
-- 4.删除函数
drop function test;5. 游标 – CURSOR
类似于迭代器，用来标记结果集中正在访问的某一行记录，初始状态下它标记结果集中的第一条记录，每次使用后自动移动到下一条记录的位置
游标可以用在存储函数中和存储过程中时，游标的创建、打开、调用和关闭，均在存储函数和存储过程中完成
-- 创建
DECLARE 游标名称 CURSOR FOR 查询语句;
-- 注意，创建游标的语句必须放在变量声明的后面
-- 打开
-- 打开游标意味着执行查询语句，让创建好的游标与该查询语句得到的结果集关联起来
OPEN 游标名称;
-- 调用
-- 把指定游标对应记录的各列的值依次赋值给INTO后边的各个变量。赋值后，游标自动移动到下一条记录的位置。
-- fetch语句一般用在循环语句中
FETCH 游标名 INTO 变量1, 变量2, ... 变量n
-- 关闭
-- 关闭游标意味着会释放该游标占用的内存。
CLOSE 游标名称;
-- 例子
delimiter //
create procedure query_2()
begin
declare done int default 0;
declare l_eno char(7);
declare the_eamount int;
declare cur cursor for select eno from let where eno in (select eno from loant);
declare continue handler for not found set done = 1;
create temporary table tmp_table(
eno char(7) not null,
eamount int
);
open cur;
repeat
fetch cur into l_eno;
call query_1(l_eno, the_eamount);
insert into tmp_table select l_eno, the_eamount;
update let set let.eamount = the_eamount where let.eno = l_eno;
until done=1
end repeat;
close cur;
select * from tmp_table;
drop table tmp_table;
end //
delimiter ;
call query_2();6. 触发器 – TRIGGER
触发器的作用就是，在对某个表格进行某项操作（增删改）之前或之后，自动地(即被服务器隐性调用)对操作所涉及的记录执行触发器操作
CREATE TRIGGER 触发器名
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN
触发器操作
END
-- 例子
delimiter //
create trigger maxInEamount before insert on
loant for each row
begin
declare msg varchar(200);
declare the_eamount int;
declare the_ecapital int;
select ecapital into the_ecapital from let where eno = new.eno;
call query_1(new.eno, the_eamount);
if the_eamount + new.lamount > 10 * the_ecapital then
set msg = "拒绝添加数据的请求!";
SIGNAL sqlstate 'HY000' set MESSAGE_TEXT = msg;
end if;
update let set let.eamount = the_eamount + new.lamount where let.eno = new.eno;
end //7. 事件 – EVENT
事件可以实现，自动地(即被服务器隐性调用)在某个时间点或者某隔一段时间执行一次某些命令
CREATE EVENT 事件名
ON SCHEDULE
{AT 某个确定的时间点 | EVERY 期望的时间间隔 [STARTS datetime][END datetime]}
DO
BEGIN
具体的语句
END
-- 例子
-- 指定某个时间点执行
CREATE EVENT insert_t1
ON SCHEDULE
AT '2018-03-10 15:48:54'
-- 也可以写成：AT DATE_ADD(NOW(), INTERVAL 2 DAY)
DO
BEGIN
INSERT INTO t1(m1, n1) VALUES(6, 'f');
END
-- 指定某个时间间隔执行
CREATE EVENT insert_t1
ON SCHEDULE
EVERY 1 HOUR STARTS '2018-03-10 15:48:54' ENDS '2018-03-12 15:48:54'
-- 也可以写成：EVERY 1 HOUR
DO
BEGIN
INSERT INTO t1(m1, n1) VALUES(6, 'f');
END七、事务
1. 什么是事务
事物是独立的工作单元，在这个独立工作单元中所有操作要么全部成功，要么全部失败
也就是说如果有任何一条语句因为崩溃或者其它原因导致执行失败，那么未执行的语句都不会再执行，已经执行的语句会进行回滚操作，这个过程被称之为事务
2. 事务四大特征 – ACID
① 原子性 (atomicity)
事务是一个不可拆分的工作单位，事务中的操作，要么全部完成，要么全部失败
基于回滚日志(undo log)实现，当事物需要回滚时就会调用回滚日志进行 SQL 语句回滚操作，实现数据还原
② 一致性 (Consistency)
事务的前后数据的完整性必须保持一致
比如A、B余额共计1000，那么不管两人怎么转账自始至终就只有1000
一致性是事物追求的最终目标，原子性、隔离性、持久性都是为了保证数据库一致性而存在
③ 隔离性 (Isolation)
一个事务的执行不能被其他的事务干扰
原子性和持久性是针对单个事务，而隔离性是针对事物与事物之间的关系
基于mvcc实现（幻读问题除外）
④ 持久性 (Durability)
一旦事务提交之后，他对数据库的操作数据改变是永久的，接下来的任何突发情况都不会放生影响
基于事务日志的重做日志（redo log）实现
3. 事务的创建
隐式事务与显式事务
-- 隐式的事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句
delete from 表 where id=1;
-- 显式事务：事物具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
set autocommit=0;创建事务
-- 1、关闭自动提交
SET autocommit=0;
-- 2、开启事务
START TRANSACTION;
-- 3、事务语句
ALTER TABLE girls MODIFY gname VARCHAR(20) NOT NULL;
INSERT INTO girls VALUES(0,'张三123');
-- 4、明显的结束标记
COMMIT; #提交
ROLLBACK; #回滚
SELECT * FROM girls; -- 查看4. 数据库的隔离级别
对于多个事务运行时，如果事务访问的是相同的数据，如果没有设置隔离级别，就会出现并发问题
read uncommitted; -- 读未提交的
read committed; -- 读已提交的
repeatable read; -- 可以重复读 (默认)
serializable; -- 串行化
-- 查看数据库的隔离级别
mysql 8.0
-- 系统级别的
select @@global.transaction_isolation;
-- 会话级别的
select @@transaction_isolation;
mysql 5.x
-- 系统级别的
select @@global.tx_isolation;
-- 会话级别的
select @@tx_isolation;
-- 修改隔离级别
set global transaction isolation level read uncommitted;![图片[10]-MySQL数据库知识总结-墨初小屋](https://s2.loli.net/2022/02/13/Uolm8hd32WLVGz6.png)
5. 事务的并发问题
![图片[11]-MySQL数据库知识总结-墨初小屋](https://s2.loli.net/2022/02/13/YfU5N6JjxvsV1AH.png)
① 脏读
读取了另一个事务没有提交的数据
② 不可重复读
事务读取同一个数据，返回结果先后不一致问题
脏读与不可重复读的区别：脏读读取的是另一个事物没有提交的数据，而不可重复读读取的是另一个事务已经提交的数据
③ 幻读
事务按照范围查询，俩次返回结果不同
④ 区别
脏读读取的是另一个事务没有提交的数据，而不可重复读读取的是另一个事物已经提交的数据。
幻读和不可重复读都是读取了另一条已经提交的事务（这点与脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）
6. 锁机制
锁在 MySQL 中是是非常重要的一部分，锁对 MySQL 数据访问并发有着举足轻重的作用
MySQL中有三类锁，分别为行锁、表锁、页锁。首先需要明确的是这三类锁是是归属于那种存储引擎的：
- 行锁：Innodb 存储引擎
- 表锁：Myisam、MEMORY 存储引擎
- 页锁：BDB 存储引擎
① 行锁
行锁又分为共享锁、排它锁，也被称之为读锁、写锁，Innodb 存储引擎的默认锁
共享锁（S）：假设一个事物对数据 A 加了共享锁（S），则这个事物只能读 A 的数据
其他事物只能再对数据 A 添加共享锁（S），而不能添加排它锁（X），直到这个事物释放了数据 A 的共享锁（S）
这就保证了其他事物也可以读取 A 的数据，但是在这个事物没有释放在 A 数据上的共享锁（S）之前不能对 A 做任何修改
排它锁（X）：假设一个事物对数据 A 添加了排它锁（X），则只允许这个事物读取和修改数据 A
其他任何事物都不能在对数据A添加任何类型的锁，直至这个事物释放了数据 A 上的锁
排它锁阻止其它事物获取相同数据的共享锁（S）、排它锁（X），直至释放排它锁（X）
特点如下：
- 只针对单一数据进行加锁
- 开销大
- 加锁慢
- 会出现死锁
- 锁粒度最小，发生锁冲突的概率越低，并发越高
② 表锁
表锁又分为表共享读锁、表独占写锁，也被称之为读锁、写锁，Myisam 存储引擎的默认锁
表共享读锁：针对同一个份数据，可以同时读取互不影响，但不允许写操作
表独占写锁：当写操作没有结束时，会阻塞所有读和写
特点如下：
- 对整张表加锁
- 开销小
- 加锁快
- 无死锁
- 锁粒度最大，发生锁冲突的概率越大，并发越小
③ 如何加锁
表锁：
- 隐式加锁：默认自动加锁释放锁，select 加读锁、update、insert、delete 加写锁
- 手动加锁：lock table tableName read；（添加读锁）、lock table tableName write（添加写锁）
- 手动解锁：unlock table tableName（释放单表）、unlock table（释放所有表）
行锁：
- 隐式加锁：默认自动加锁释放锁，只有 select 不会加锁，update、insert、delete 加排它锁
- 手动加共享锁：select id name from user lock in share mode
- 手动加排它锁：select id name form user for update
- 解锁：正常提交事物（commit）、事物回滚（rollback）、kill 进程
数据库系统概论pdf下载
https://pan.baidu.com/s/1QGeDEBWaP7-DdODoq7q_RQ?pwd=b40a
参考资料
https://www.cnblogs.com/cxx8181602/p/9525950.html
https://blog.csdn.net/qq_41207757/article/details/108132442
https://www.php.cn/mysql-tutorials-445537.html
https://blog.csdn.net/u013617791/article/details/104854495
https://blog.csdn.net/dianxinlaozong/article/details/108650368
