基础篇

通用语法及其分类

SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾
• SQL语句可以使用空格/缩进来增强语句的可读性
• MYSQL数据库的SQL语句不区分大小写，关键字建议使用大写
• 注释：
  • 单行注释：-- 注释内容 或 # 注释内(MYSQL特有)
  • 多行注释：/ 注释内容 /

SQL分类

• DDL: 数据定义语言，用来定义数据库对象（数据库、表、字段）
• DML: 数据操作语言，用来对数据库表中的数据进行增删改
• DQL: 数据查询语言，用来查询数据库中表的记录
• DCL: 数据控制语言，用来创建数据库用户、控制数据库的控制权限

DDL(数据定义语言)

数据库的操作

查询所有数据库：

SHOW DATABASES;

查询当前数据库：

SELECT DATABASE();

创建数据库：

CREATE DATABASES[IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

删除数据库：

DROP DATABASE[IF EXISTS] 数据库名;

使用数据库：

USE 数据库名;

注意事项

• UTF8字符集长度为3字节，有些符号占4字节，所以推荐用utf8mb4字符集

表操作

查询当前数据库所有表：

SHOW TABLES;

查询表结构：

DESC 表名

查询指定表的建表语句：

SHOW CREATE TABLE 表名;

复制另一个表的结构：

CREATE TABLE 表名 LIKE 另一个表;

创建表

CREATE TABLE 表名（
	字段1 字段1类型[COMMENT 字段1注释],
	字段2 字段2类型[COMMENT 字段2注释],
	字段3 字段3类型[COMMENT 字段3注释],
	.....
	字段n 字段n类型[COMMENT 字段n注释] 
)[COMMENT 表注释];

最后一个字段没有,号

MySQL字符串是 varchar

修改表

添加字段：

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

例：ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

修改数据类型：

ALTER TABLE 表名 MODIFY字段名 新数据类型(长度);

修改字段名和字段类型：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释]{约束};

例：将emp表的nickname字段修改为username，类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

删除字段：

ALTER TBALE 表名 DROP字段名；

修改表名：

ALTER TABLE 表名 RENAME TO 新表名;

删除表

删除表：

DROP TABLE [IF EXISTS] 表名；

删除指定表，并重新创建该表：

TRUNCATE TABLE 表名;

数值类型

整数型

• TINYINT[(M)] [UNSIGNED] [ZEROFILL] 范围非常小的整数，有符号的范围是 -128到127，无符号的范围是0到 255
• SMALLINT[(M)] [UNSIGNED] [ZEROFILL] 范围较小的整数，有符号的范围是 -32768到32767，无符号的范围是0到 65535
• MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 中等大小的整数，有符号的范围是 -8388608到8388607，无符号的范围是0到 16777215。
• INT[(M)] [UNSIGNED] [ZEROFILL] 正常大小的整数，有符号的范围是 -2147483648到 2147483647。无符号的范围是 0到4294967295。
• BIGINT[(M)] [UNSIGNED] [ZEROFILL] 大整数，有符号的范围是 -9223372036854775808到 9223372036854775807，无符号的范围是0到 18446744073709551615。

浮点型

• FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]一个小的（单精度）浮点数。允许值是-3.402823466E+38 到-1.175494351E-38， 0以及1.175494351E-38 到3.402823466E+38,M是总位数，D是小数点后面的位数。
• DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]正常大小（双精度）浮点数。允许值是 -1.7976931348623157E+308到-2.2250738585072014E-308，0以及 2.2250738585072014E-308到 1.7976931348623157E+308。M是总位数，D是小数点后面的位数

字符串型

• CHAR[(M) 一个固定长度的字符串，在存储时始终用空格填充指定长度。 M表示以字符为单位的列长度。M范围为0到255.如果M省略，则长度为1，存储时占用M个字节
• VARCHAR(M)可变长度的字符串，M 表示字符的最大列长度，M的范围是0到65,535，存储时占用**L+1（L<=M,L为实际字符的长度）**个字节
• TINYTEXT[(M)] 不能有默认值，占用L+1个字节，L<2^8
• TEXT[(M)] 不能有默认值，占用L+2个字节，L<2^16
• MEDIUMTEXT[(M)] 不能有默认值，占用L+3个字节，L<2^24
• LONGTEXT[(M)] 不能有默认值，占用L+4个字节，L<2^32
• ENUM('value1','value2',...) ENUM是一个字符串对象，其值从允许值列表中选择，它只能有一个值，从值列表中选择,最多可包含65,535个不同的元素
• SET('value1','value2',...) 字符串对象，该对象可以有零个或多个值，最多可包含64个不同的成员

char() ---> 性能好 区别 char 定长 varchar 不定长

varchar() ---> 性能较差

日期时间类型

• TIME 范围是’-838:59:59.000000’ 到’838:59:59.000000’ 时间值或持续时间
• DATE 支持的范围是 '1000-01-01’到 ‘9999-12-31’ 日期值
• year 年份值
• DATETIME 日期和时间组合。支持的范围是 '1000-01-01 00:00:00.000000’到 ‘9999-12-31 23:59:59.999999’
• TIMESTAMP 时间戳。范围是’1970-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。
  YEAR 范围是 1901到2155 混合日期和时间值

DML(数据操作语言)

添加数据

给指定字段添加数据

INSERT INTO 表名(字段名1，字段名2...) VALUES(值1，值2...);

给全部字段添加数据

INSERT INTO 表名 VALUES(值1，值2...);

批量添加数据

INSERT INTO 表名(字段名1，字段名2...) VALUES(值1，值2...),(值1，值2...)(值1，值2...);

INSERT INTO 表名 VALUES(值1，值2...)(值1，值2...)(值1，值2...)...;

将同结构表中数据 插入另一个表：

INSET INTO 表名 SELECT *FROM 另一个表名;

例子：

insert into game(name,adress,juese,id) values
('王者荣耀','腾讯','李白',1),
('刺激战场','腾讯','嘻嘻嘻',2),
('英雄联盟','石头','疾风剑豪',3);

注意事项

• 插入数据时，指定的字段顺序需要与值的顺序时一一对应的
• 字符串和日期型类型应该包含在引号中
• 插入的数据大小，应该在字段的规定范围内

更新和删除数据

修改数据：

UPDATE 表名 SET 字段名1 = 值,字段名2 = 值2,....[WHERE 条件];

例：
UPDATE emp SET name = 'Jack' WHERE id = 1;

删除数据：

DELETE FROM 表名 [WHERE 条件];

注意事项

• DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的数据。
• DELETE语句不能删除某一个值字段的值(可以使用UPDATE)

DQL(数据查询语言)

​ 语法： 编写顺序

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

基本查询

查询多个字段

SELECT 字段1,字段2,字段3,...FROM 表名;

SELECT *FROM 表名;

设置别名

SELECT 字段1[AS 别名1],字段2[AS 别名2]...FROM 表名;

去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

转义：
SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'
/ 之后的_不作为通配符

条件查询(WHERE)

语法：

SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件：

例子：

select * from tb_user;
-- 年龄小于30
select *from tb_user where age < 30;	
-- 年龄大玉30
select *from tb_user where age > 30;
-- 年龄小于等于12
select *from tb_user where age <= 12;
-- 没有地址
select *from tb_user where adress is null or age = '';
-- 性别为男
select *from tb_user where gender = '男';
-- 不等于
select *from tb_user where age != 30;
-- 年龄在30到100之间
select *from tb_user where age between 30 and 100;
-- 年龄等于41或12或112
select *from tb_user where age = 41 or age = 12 or age = 112;
select *from tb_user where age in (41,12,112);
-- 姓名为一个字
select *from tb_user where name like '_';
-- 年龄最后为2
select *from tb_user where age like '%2';

聚合查询(聚合函数)

将一列数组作为一个这个题，进行纵向计算

语法：

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

常见聚合函数：

例子：

-- 统计该企业的员工数量
select count(name) from game;
-- 统计平均 id
select avg(id) from game;
-- 求取最大年龄
select max(age) from emp;
-- 求取最小值
select min(age) from emp;
-- 求取id的和
select sum(id) from game;

分组查询(GROUP BY)

语法：

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where 和 having 区别：

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

例子：

-- 根据性别分组，统计男性员工和女性员工的数量
select gender,count(*)from emp group by gender;
-- 根据性别分组，统计男性员和女性员工的平均年龄
select gender,avg(age)from emp group by gender;
-- 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workadress,count(*) from emp where age < 45 group by workadress having  count(*)>= 3;

注意事项

• 执行顺序：where > 聚合函数 > having
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

语法：

select 字段列表 from 表名 order by 字段1 排序方式1，字段2，排序方式2;

排序方式：

• ASC：升序(默认值)
• DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

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

分页查询(LIMIT)

语法：

select 字段列表 from 表名 limit 起始索引,查询记录数

注意事项

• 其实索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数
• 分页查询时数据库的方言，不同数据库有不同的体现，mysql中时limit
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10

例子：

-- 查询第一页员工数据，每页显示10条记录
select *from emp limit 0,5;
-- 查询第二页员工数据，每页显示10条记录  -> (页码-1)*页码展示记录数
select *from emp limit 10,10;

DQL执行语序

==FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT==

DCL(数据控制语言)

管理用户

USE mysql;
SELECT * FROM user;

语法：

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

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

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

例子：

-- 创建用户test，只能在当前主机localhost访问
create user 'test'@'localhost' identified by '123456';
-- 创建用户test，能在任意主机访问
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密码
alter user 'test'@'localhost' identified with mysql_native_password by '1234';
-- 删除用户
drop user 'test'@'localhost';

注意事项

• 主机名可以使用 % 通配

权限控制

常用权限：

更多权限请看权限一览表

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

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

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

注意事项

• 多个权限用逗号分隔
• 授权时，数据库名和表名可以用 * 进行通配，代表所有-

例子：

-- 查询权限
show grants for 'xxq'@'%';
show grants for 'xjh'@'localhost';

-- 授予权限
grant all on itcast.* to 'xjh'@'localhost';

-- 撤销权限
revoke all on itcast.* from 'xjh'@'localhost';

函数

• 字符串函数
• 数值函数
• 日期函数
• 流程函数

字符串函数

例子:

-- 字符串的拼接
select concat('Hello','World');
-- 小写
select lower('HELLO WORLD');
-- 大写
select upper("hello world");
-- 左填充
select lpad('01',5,'-'); # 结果 ---01
-- 右填充
select rpad('01',5,'-'); # 结果 01---
-- 去除空格
select trim('  Hello World   ');
-- 切片(起始索引为1) 划取字符串 从第一位到第五位
select substring('Hello World',1,5);

数值函数

常见函数：

例子：

-- 向上取整
select ceil(2.7);
-- 向下取整
select floor(2.1);
-- 返回x/y的模
select mod(1,2);
-- 返回0~1之间随机数
select rand();
-- 求参数x的四舍五入值，保留y位小数
select round(3.1415926,2);

日期函数

常用函数：

例子：

-- 返回当前日期
select curdate();
-- date_add() 返回一个紫气/时间值加上一个时间建个exprtype
select date_add(now(),interval 30 year);

流程函数

常用函数：

例子：

-- if
select if(true,'ok','error');
-- ifnull
select ifnull('ok','default');
select ifnull(null,'ok');
-- case then else end
-- 需求：查询emp表中的员工姓名和工作地址()
select 
	name,
	(case workadress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;
-- case when 
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;

约束

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

分类：

注意：约束时作用于表中字段上的，可以在创建表/修改表的时候添加约束。

常用约束

唯一约束：

• 列级约束

字段名 数据类型 UNIQUE;

• 表级约束

UNIQUE(字段名1, 字段名2, …);

删除默认约束:

ALTER TABLE 表名 MODIFY 字段名 数据类型;

添加默认约束:

ALTER TABLE 表名 MODIFY字段名 数据类型 约束;

例子：

create table user(
	id int PRIMARY key auto_increment comment '编号',  # 主键，并且自动增长
	name varchar(10) NOT NULL unique comment '姓名', # 姓名唯一约束 并且不为空
	age int check(age > 0 && age <= 120) comment '年龄',
	status char(1) default '1' comment '状态', # 如果没有指定值，默认为1
	gender char(1) comment '性别'
) comment '用户表';

# 列级约束
mysql> CREATE TABLE my_unique_1 (
    ->   id INT UNSIGNED UNIQUE,
    ->   username VARCHAR(10) UNIQUE
    -> );
Query OK, 0 rows affected (0.01 sec)

# 表级约束
mysql> CREATE TABLE my_unique_2 (
    ->   id INT UNSIGNED,
    ->   username VARCHAR(10),
    ->   UNIQUE(id),
    ->   UNIQUE(username)
    -> );
Query OK, 0 rows affected (0.01 sec)

# ① 删除默认约束
mysql> ALTER TABLE my_default MODIFY age INT UNSIGNED;

# ② 添加默认约束
mysql> ALTER TABLE my_default MODIFY age INT UNSIGNED DEFAULT 18;

外键约束

概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

注意：目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性。

语法：

alter table 从表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名)

create table 表名(
	字段名 数据类型，
	[comment][外键名称]foreign key (外键字段名) references 主表(主表列名)
);

删除外键：

alter table 表名 drop foreign key 外键名称;

例子：

-- 添加外键
alter table tb_emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
-- 删除外键
alter table tb_emp drop foreign key fk_emp_dept_id;

删除/更新行为

更改删除/更新行为：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;

alter table tb_emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;
alter table tb_emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set NULL on delete set null;

多表查询

多表关系

一对多(多对一)

案例：部门和员工关系

关系：一个部门对应多个员工，一个员工对应一个部门

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

多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可提供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

-- 多对多
create table student(
	id int auto_increment primary key comment '主键ID',
	name varchar(10) comment '姓名',
	no varchar(10) comment '学号'
)comment '学生表';
-- 为student表中添加数据
insert into student values
(null,'黛绮丝','20000101'),
(null,'谢逊  ','20000102'),
(null,'韦一笑','20000103'),
(null,'殷天正','20000104');
-- 为course中添加数据
create table course(
	id int auto_increment primary key comment '主键ID',
	name varchar(10) comment '课程名称'
)comment '课程表';
-- 为course中添加数据
insert into course values
(null,'Java'),
(null,'PHP'),
(null,'Hadoop');
-- 创建第三个表 实现duoduiduo
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 '学生课程中间表';

一对一

案例：用户和用户详情的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(unique)

-- 一对一
create table tb_user2(
		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_user2_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_user2(id) # 实现一对一
	)comment '用户教育信息表';

查询

合并查询（笛卡尔积，会展示所有组合结果）：
select * from employee, dept;

笛卡尔积：两个集合A集合和B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

消除无效笛卡尔积：

where 主键 = 外键

select * from employee, dept where employee.dept = dept.id;

交叉连接： 笛卡尔积

select * from 表1 cross join 表2;

内连接查询

内连接：相当于查询A、B交集部分数据

隐式内连接：

selcet 字段列表 from 表1,表2 where 条件...;

显式内连接：

select 字段列表 from 表1 [inner] join 表2 on 连接条件;

例子：

-- 查询每一个员工的姓名及所属的部门名称
-- 隐式内连接
select tb_emp.name,dept.name from tb_emp,dept where tb_emp.dept_id = dept.id;
select e.name, d.name from tb_emp e,dept d where e.dept_id = d.id;

-- 查询每一个员工的姓名及所属的部门名称
-- 显式内连接
select e.name,d.name from tb_emp e inner join dept d on e.dept_id = d.id ; 

外连接查询

左外连接：

select 字段列表 from 表1 left[outer] join 表2 on 条件...;

右外连接：

select 字段列表 from 表2 right[outer] join 表2 on 条件...;

-- 查询emp表中的所有数据和对应的部门连接(左外连接)
select e.*,d.name from tb_emp e left outer join dept d on e.dept_id = d.id;
-- 查询dept表的所有数据和对应的员工信息(右外连接)
select d.*,e.*  from tb_emp e right outer join dept d on e.dept_id = d.id;

自连接查询

自连接查询语法：

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询。

-- 查询员工信息及领导所属信息
select a.name ,b.name from tb_emp a ,tb_emp b where a.managerid = b.id;

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

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

语法：

select 字段列表 from 表A...
union[all]
select 字段列表 from 表B...;

注意：对于联合查询的多张表的列数必须保持一致，字段类型也需要一致

union all 会将全部的数据直接合并到一起，union会对合并后的数据去重。

-- 查询薪资低于5000的员工和年龄大于50的员工全部查询出来
select * from tb_emp where salary > 5000
union  all # 删掉all 可以去除重复记录
select * from tb_emp where age > 50;

子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

select * from t1 where column1 = (select column1 from t2);

子查询的外部语句可以是 insert / update / delete / select 的任何一个。

根据子查询结果不同，分为：

• 标量子查询(子查询结果为单个值)
• 列子查询(子查询结果为一列)
• 行子查询(子查询结果为一行)
• 表子查询(子查询结果为多行多列)

根据子查询位置，分为：where之后 、 from之后 、 select 之后 。

标量子查询

子查询返回的结果是单个值(数字，字符串，日期) ， 最简单的形式，之中成为标量子查询。

常用的操作符：- < > > >= < <=

-- 查询销售部的所有员工信息
-- a 查询销售部的id
select id from dept where name = '销售部';
-- b 根据销售部门的ID，查询员工信息
select * from tb_emp where dept_id = 4;
-- 标量子查询
select *from tb_emp where dept_id = (select id from dept where name = '销售部');

-- 查询在韦一笑入职后的员工信息
-- a 查询韦一笑的入职信息
select entrydate from tb_emp where name = '韦一笑';
-- b 查询指定入职日期之后的入职的员工信息
select * from tb_emp where entrydate > '2007-01-01';
-- 标量子查询
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '韦一笑');

列子查询

子查询返回的结果是一列(可以是多行)，这种查询成为列子查询。

常用的操作符：

-- 1.查询销售部和市场部的所有员工
-- a 查询销售部和市场部的部门id
select id from dept where name = '销售部' or name = '市场部';
-- b 根据部门id 查询员工信息
select * from tb_emp where dept_id in (2,6);
-- 列子查询
select * from tb_emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

-- 2.查询比财务部所有员工都高的工资
-- a 查询所有财务部的人员工资
select id from dept where name = '财务部';
select salary from tb_emp where dept_id = 3;
select salary from tb_emp where dept_id = (select id from dept where name = '财务部');
-- b 查询所有财务部人员工资
select * from tb_emp where salary > all(select salary from tb_emp where dept_id = (select id from dept where name = '财务部'));

-- 3.查询比市场部其中任意一人工资高的员工信息
-- a 查询市场部所有人的研发工资
select salary from tb_emp where dept_id = (select id from dept where name = '市场部');
-- b 查询比市场部任意一人工资高的员工信息
select * from tb_emp where salary > any (select salary from tb_emp where dept_id = (select id from dept where name = '市场部'));

行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。

常用操作符：=, <, >, IN, NOT IN

-- 1.查询张无忌的薪资及直属领导相同的员工信息-
-- a 查询张无忌的薪资和直属领导
select salary ,managerid from tb_emp where name = '张无忌';
-- b 查询与张无忌的薪资及直属领导相同的员工信息
select * from tb_emp where salary = 12000 and managerid = 1;
select * from tb_emp where (salary , managerid ) = (12000,1);
-- 行子查询 
select * from tb_emp where (salary , managerid) = (select salary ,managerid from tb_emp where name = '张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用运算符：in

-- 1. 查询与韦一笑 杨逍的职位和薪资相同的员工信息
-- a 查询 韦一笑 和 杨逍 的职位和薪资
select job , salary from tb_emp where name = '杨逍' or name = '韦一笑';
-- b 查询与 韦一笑 和 杨逍 的职位和薪资相同的员工信息
select * from tb_emp where (job , salary) in (select job , salary from tb_emp where name = '杨逍' or name = '韦一笑');
-- 2. 查询入职十七是 2006-01-01 之后的员工信息，及其部门是
-- a 查询入职十七是 2006-01-01 之后的员工信息
select * from tb_emp where entrydate > '2006-01-01';
-- b 查询这部分员工 对应的部门信息是：
select e.*,d.* from (select * from tb_emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

事务

事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向提醒哦那个提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

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

事务操作

方法一：

• 查看 / 设置事务提交方式

select @@autocommit;

set @@autocommit = 0;

• 提交事务

commit;

• 回滚事务

rollback;

方法二：

• 开启事务

start transaction 或 begin;

• 提交事务

commit;

• 回滚事务

rollback;

例子：

-- 转账操作(张三给李四转账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 = '李四';

-- 查看事务提交方式
select @@autocommit;
-- 设置为手动提交
set @@autocommit = 1;

-- 方法1
-- 提交事务
commit;
-- 回滚事务
rollback;

-- 方法2
-- 开启事务
start TRANSACTION;  # 手动控制事务
-- 提交事务
commit;
-- 回滚事务
rollback;

事务的四大特性ACID

• 原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败
• 一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态
• 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

并发事务问题

事务的隔离级别

• √表示在当前隔离级别下该问题会出现

• Serializable 性能最低；Read uncommitted 性能最高，数据安全性最差

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

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

-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level repeatable read;

SESSION 是会话级别，表示只针对当前会话有效，GLOBAL 表示对所有会话有效

进阶篇

存储引擎

MySQL体系结构：

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

相关操作：

-- 查询建表语句
show create table account;
-- 建表时指定存储引擎
CREATE TABLE 表名(
	...
) ENGINE=INNODB;
-- 查看当前数据库支持的存储引擎
show engines;

-- 创建表 my_myisam 并指定用MyISAM存储引擎
create table my_mysiam(
	id int,
	name varchar(20)
)engine = MyISAM;

create table my_memory(
	id int,
	name varchar(20)
)engine = MEMORY;

InnoDB

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

特点：

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

文件：

• xxx.ibd: xxx代表表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

参数：innodb_file_per_table，决定多张表共享一个表空间还是每张表对应一个表空间

知识点：

查看 Mysql 变量：
show variables like 'innodb_file_per_table';

从idb文件提取表结构数据：
（在cmd运行）
ibd2sdi xxx.ibd

InnoDB 逻辑存储结构：

MyISAM

MyISAM 是 MySQL 早期的默认存储引擎。

特点：

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

文件：

• xxx.sdi: 存储表结构信息
• xxx.MYD: 存储数据
• xxx.MYI: 存储索引

Memory

Memory 引擎的表数据是存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

• 存放在内存中，速度快
• hash索引（默认）

文件：

• xxx.sdi: 存储表结构信息

存储引擎特点

存储引擎的选择

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

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

电商中的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎。

性能分析

查看执行频次

查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次：
SHOW GLOBAL STATUS LIKE 'Com_______'; 或者 SHOW SESSION STATUS LIKE 'Com_______';
例：show global status like 'Com_______'

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 开启慢查询日志开关
slow_query_log=1
# 设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
更改后记得重启MySQL服务，日志文件位置：/var/lib/mysql/localhost-slow.log

查看慢查询日志开关状态：
show variables like 'slow_query_log';

profile

show profile 能在做SQL优化时帮我们了解时间都耗费在哪里。通过 have_profiling 参数，能看到当前 MySQL 是否支持 profile 操作：
SELECT @@have_profiling;
profiling 默认关闭，可以通过set语句在session/global级别开启 profiling：
SET profiling = 1;
查看所有语句的耗时：
show profiles;
查看指定query_id的SQL语句各个阶段的耗时：
show profile for query query_id;
查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：
# 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;

EXPLAIN 各字段含义：

• id：select 查询的序列号，表示查询中执行 select 子句或者操作表的顺序（id相同，执行顺序从上到下；id不同，值越大越先执行）
• select_type：表示 SELECT 的类型，常见取值有 SIMPLE（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
• type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all
• possible_key：可能应用在这张表上的索引，一个或多个
• Key：实际使用的索引，如果为 NULL，则没有使用索引
• Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
• rows：MySQL认为必须要执行的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的
• filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

视图

介绍：

视图(VIEW)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中是同的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不存在查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

基础语法

创建视图

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 exits] 视图名称 [,视图名称]...;

例子：

-- 创建视图
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 < 3;

-- 修改视图
-- 方式一
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;

检查选项

视图检查选项：

当使用with check option字句创建视图时，mysql会通过视图检查正在更改的每个行，例如插入、更新、删除以使其符号视图的定义。

mysql语句基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：cascaded 和 local，默认值为 cascaded。

cascaded

会将检查选项级联到 上一层

例子；

-- cascaded
create or replace view stu_v_1 as select id , name from student where id < 10 ;

insert into stu_v_1 values(5,'Tom');

insert into stu_v_1 values(20,'s');

create or replace view stu_v_2 as select id , name from stu_v_1 where id >= 6 with cascaded check option;

insert into stu_v_2 values(8,'z');
insert into stu_v_2 values(11,'r');

-- 没有加 检查 选项 直接 去 stu_v_ 2 级联条件
create or replace view stu_v_3 as select id , name from stu_v_2 where id >= 8;

insert into stu_v_3 values(7,'m');
insert into stu_v_3 values(9,'n');
insert into stu_v_3 values(5,'j');

local

不会为上一层生成检查选项

例子：

-- local
create or replace view stu_v_4 as select id , name from student where id < 10 ;

insert into stu_v_4 values(5,'Tom');

insert into stu_v_4 values(20,'s');
-- v4 没有定义 with check option 所以 不检查
create or replace view stu_v_5 as select id , name from stu_v_4 where id >= 6 with local check option;

insert into stu_v_5 values(8,'z');
insert into stu_v_5 values(11,'r');

-- 没有加 检查 选项 直接 去 stu_v_5 
-- 检查v5 v5 you 检查选项
create or replace view stu_v_6 as select id , name from stu_v_5 where id >= 8;

insert into stu_v_6 values(7,'m');
insert into stu_v_6 values(9,'n');
insert into stu_v_6 values(5,'j');

更新及作用

视图的更新

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

• 聚合函数或窗口函数( sum() , min() , max( ) ,count() 等)
• distinct
• group by
• having
• union 或者 union all

作用

简单：

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

安全：

数据库可以授权，但不能授权到数据库指定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。

数据独立：

视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

• 介绍

存储过程时事先经过编译并存储在数据库的一段SQL语句的结合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率时有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

• 特点

1.封装、复用

2.可以接受参数，也可也返回数据

3.减少数据交互，效率提升

基本语法

• 创建

create procedure 存储过程名称[参数列表]
begin
	-- SQL语句
end;

• 调用

call 名称([参数]);

• 查看

-- 查询指定数据库的存储过程及状态信息

select * from information_schema routines where routine_schema = 'xxx';

-- 查询某个存储过程的定义

show create procedure 存储过程名称;

• 删除

drop procedure [if exists] 存储过程名称;

例子：

-- 创建
create procedure p1()
begin
	select count(*) from student;
end;
-- 调用
call p1();
-- 查看
select * from information_schema.ROUTINES where routine_schema = 'itcast';
show create procedure p1; 
-- 删除
drop procedure p1;

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

例子：

delimiter $$
create prodecure p1()
begin
  select count(*) from student;
end;$$

变量

• 系统变量
• 用于自定义变量
• 局部变量

系统变量

系统变量时MYSQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)，会话变量(SESSION) 默认时 SESSION。

• 查看系统变量

-- 查看所有系统变量

show [session | gloabal] variables;

-- 可以通过like模糊匹配模式查找变量

show [seession | global] variables like '...';

-- 查看指定变量的值

select @@[session | global] 系统变量名;

• 设置系统变量

set [session | global] 系统变量名 = 值;

set @@[session | global] 系统变量名 = 值;

例子：

-- 变量：系统变量
-- 查看系统变量
show session VARIABLES;
show session variables like 'auto%';
show global variables like 'auto%';
select @@session.autocommit;
-- 设置系统变量
set session autocommit = 0; 
set @@autocommit = 1;

注意：

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

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

用户定义变量

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

• 赋值

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 表名;

• 使用

select @var_name;

例子：

-- 变量：用户变量
-- 赋值
-- set
set @myname = 'Heima';
set @myage := 10;
set @mygender := '男' , @myhobby := 'C++';
-- select
select @mycolor := 'red';
select count(*) into @mycount  from tb_user;
-- 使用
select @myname;
select @myage,@mygender,@myhobby;
select @mycount;
-- 没有赋值 结果为null
set @myeee;
select @myeee;

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

局部变量

局部变量需要定义的局部生效的变量，访问之前，需要declare声明。可以用作存储过程内的局部变量和输入参数，局部变量的范围是在其声明的begin..end块。

• 声明

declare 变量名 数据类型 [default];

变量类型就是数据库字段类型：int , bigint , char , varchar , date , time等。

• 赋值

set 变量名 = 值;

set 变量名 := 值;

select 字段名 into 变量名 from 表名;

例子：

-- 变量：局部变量
-- 声明
-- 赋值
create procedure p2()
begin
	declare stu_count int default 0 ;
-- 	set stu_count := 100
	select count(*) into stu_count from student;
	select stu_count;
end;
-- 使用p2
call p2;

if判断

语法：

if 条件1 then           -- 可选
	...
elself 条件2 then       -- 可选
	...
else
	...
end if;

例子：

# 根据定义的分数score变量，判定当前分数对应的分数等级
-- 1.score >= 85 等级为优秀
-- 2.score >= 60 且 score < 85 等级为及格
-- 3.score < 60 等级为不合格

create procedure p3()
begin
	declare score int default 58;
	declare result varchar(10);
	-- if 判断
	if score >= 85 then 
		set result := '优秀';
	elseif score >= 60 then 
		set result := '及格';
	else
		set result := '不合格';
	end if;
	
	select result;
end;

call p3;

参数(in,out,inout)

• 参数

用法：

create procedure 存储过程名称([in / out / inout ] 参数名 参数类型)
begin
	-- SQL语句
end;

例子：

# 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out).
-- in  out
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(95, @result);
select @result;

-- 将传入的200分制的分数，进行换算，换算成百分制，然后返回分数
-- inout
create procedure p5(inout score double)
begin
	set score := score / 2;
end;
set @score = 78; # 先赋值
call p5(@score); # 后调用
select @score;

case

语法一：

case case_value
	when when_value1 then statement_list1
	[when when_value2 then statement_list2]...
	[else statement_list]
end case;

语法二：

case
	when search_condition1 then statement_list1
	[when search_conditiin2 then statement_list2]
	[else statement_list]
end case;

例子：

-- case
# 根据传入的月份，判定月份所属的季节(要求使用case结构)
-- 1.1-3月份，为第一季度
-- 2.4-6月份，为第二季度
-- 3.7-9月份，为第三季度
-- 4.10-12月份，为第四季度

create procedure p6(in month int)
begin
	declare result varchar(10);
	case 
		when month between 1 and 3 then 
			set result := '第一季度';
		when month between 4 and 6 then
		  set result := '第二季度';
		when month between 7 and 9 then
			set result := '第三季度';
		when month between 10 and 12 then
			set result := '第四季度';
		else
			set result := '非法参数';
	end case;		
	select concat('您输入的月份为:',month,',所属的季度为:',result);
end;

call p6(7);

循环-while

while循环是有条件的循环控制语句，满足条件后，再执行循环体中的SQL语句。

语法：

# 先判断条件，如果条件为true,则执行逻辑，否则，不执行逻辑
while 条件 do
	SQL逻辑...
end while;

例子：

-- while循环
# 计算从1累加到100的值，n为传入的参数值
create procedure p7(in n int)
begin

	declare count int default 0;
	while n > 0 do
		set count := count + n;
		set n := n - 1;
	end while;
	
	select count;
end;

call p7(100);

循环-repeat

repeat 是有条件的循环控制语句，当满足条件的时候退出循环。

语法：

# 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出，如果不满足，则继续下一次循环
repeat
	SQL逻辑...
	until 条件
end repeat;

例子：

-- repeat 循环
# 计算从1累加到100的值，n为传入的参数值

create procedure p8(in n int )
begin

	declare result int default 0;
	repeat 
		set result := result + n;
		set n := n -1;
		until n <= 0
	end repeat;
	
	select result;
	
end;

call p8(100);

循环-loop

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用来实现简单的死循环。loop可以配合以下两个语句使用：

• leave：配合循环使用，退出循环
• iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进行下一次循环。

语法：

[begin_label] loop
	SQL逻辑
end loop [end_label];
leave label; -- 退出指定标记的循环体
iterate label; -- 直接进入下一次循环

例子：

-- loop 循环
# 计算从1累加到n的值，n为传入的参数值
create procedure p11(in n int )
begin
	declare result int default 0;	
	sum:loop 
		set result := result + n;
		set n := n - 1;		
		if n <= 0 then
			leave sum;
		end if;		
	end loop sum;	
	select result;
end;
call p11(100);

# 计算从1到n之间偶数累加的值，n为传入的参数
create procedure p12(in n int )
begin
	declare result int default 0;	
	count:loop
		if n <= 0 then
			leave count;			
		elseif n % 2 = 1 then
			   set n := n - 1;
			   iterate count;
		end if;
		set result := result + n;
		set n := n - 1;
	end loop count;
	select result;
end;
call p12(100);

游标

游标(cursor)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的传力。游标的使用包括游标的声明、open、fetch 和 close 。

语法：

• 声明游标

declare 游标名称 cursor for 查询语句;

• 打开游标

open 游标名称

• 获取游标记录

fetch 游标名称 into 变量[,变量];

• 关闭游标

close 游标名称

例子：

# 根据传入的参数uage，用来查询用户表tb_user中，所有的用户年龄小于等于usage的用户姓名(name)和专业(profession),并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中

-- 逻辑：
-- A.声明游标 存储查询的 结果集
-- B.准备创建 表结构
-- C.开启游标
-- D.获取游标中的记录
-- E.插入数据到新表中
-- F.关闭游标

create procedure p1(in uage int)
begin
	declare u_name varchar(10);
	declare u_pro varchar(100);
	DECLARE	u_cursor cursor for select name,profession from tb_user where uage >= age;
    -- 先声明 变量 后 声明 游标
	 declare exit handler for sqlstate '02000' close u_cursor;
	 -- 条件处理程序 看下部分内容讲解
	create table if not exists tb_user_pro(
		id int primary key auto_increment,
		name varchar(10),
		profession varchar(100)
	);	
	open u_cursor;	
	
	while true do
		fetch u_cursor into u_name,u_pro;	
		insert into tb_user_pro values(null,u_name,u_pro);
	end while;	
	
	close u_cursor;
end;

call p11(40);

条件处理程序

条件处理程序(handler)可以用来定义在流程控制结构执行过程中遇到问题时遇到时对应的处理步骤

语法：

declare handler_action handler for condition_value [,condition_value]..statement;

`handler_action`	
	continue:继续执行当前程序
	exit:终止执行当前程序
`condition_value`
	sqlstate sqlstate_value:状态码，如0200
	sqlwarning:所有以01开头的sqlstate代码的简写
	not found:所有以02开头的sqlstate代码的简写
	sqlexception:所有没有被sqlwarning 或 not found 捕获的 sqlstate代码的简写

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能时 in 类型。

语法：

create function 存储函数名称([参数列表])
returns type [characteric ...]
begin
	--SQL语句
	return ...;
end;

characteristic说明：

• deterministic ：相同的输入参数总是产生相同的结果
• no sql ：不包含 sql 语句
• reads sql data ：包含读取数据的语句，但不包含写入数据的语句

例子：

# 从1到累加到n的值，n为传入的参数值

create function func1(n int)
returns int deterministic
begin
	declare result int default 0;	
	while n > 0 do
		set result := result + n;
		set n := n - 1;
	end while;
	
	return result;
end;
select func1(100);

注意：必须要有返回值

触发器

介绍

触发器是与表有关的数据库对象，指在 insert / update / delete 之前或之后，触发并执行触发器定义的SQL语句集合。触发器的这种特性可以协助应用再数据库端确保数据的完整性，日志记录，数据校验等操作，一致性。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其它的数据可以是相似的。现在触发器还只支持行级触发，不支持语句级触发。

类型：

语法

• 创建

create trigger trigger_name
before/after insert/update/delete
on tbl_name for each row -- 行级触发器
begin
	trigger_stmt;
end;

• 查看

show triggers;

• 删除

drop trigger[schema_name.] trigger_name; # 如果没有指定schema_name , 默认为当前数据库

例子：

# 需求：通过触发器记录user 表的数据变更之日(user_logs) , 包含增加、删除、修改
-- 准备工作：日志表user_logs
create table user_logs(
	id int(11) not null auto_increment,
	operation varchar(20) not null comment '操作类型, insert / update / delete',
	operate_time datetime not null comment '操作时间',
	operate_id int(11) not null comment '操作的ID',
	operate_params varchar(500) comment '操作参数',
	primary key(`id`)
)engine=innodb default charset=utf8;

-- 插入数据触发器
create trigger tb_user_insert_trigger
	after insert on tb_emp 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,',job=',new.job,',salary=',new.salary));
end;
-- 查看
show triggers;
-- 删除
drop trigger tb_user_insert_trigger;
insert into tb_emp(id,name,job,salary) values
(12,'小徐','经理','14000');

-- 修改数据触发器
create trigger tb_emp_update
	after update on tb_emp 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,',job=',old.job,',salary=',old.salary,
																		 '更新之后的内容为:id=',new.id,',name=',new.name,',job=',new.job,',salary=',new.salary));
end;
update tb_emp set name = '徐' where id = 12;
-- 满足条件几行 触发器发生几次
update tb_emp set job = '员工' where id > 10;

-- 删除数据的触发器
create trigger tb_emp_delete
	after delete on tb_emp for each row
begin
	insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
	(null,'delete',now(),old.id,concat('删除之前的内容为:id=',old.id,',name=',old.name,',job=',old.job,',salary=',old.salary));
end;
show triggers;
drop trigger tb_emp_delete;
delete from tb_emp where name = '小徐';

关系规范化

函数依赖

什么是函数依赖？比如学生管理系统数据库，有学生姓名（Sname)、学生系名（Sdept)、学生学号（Sno）等等。一个学号只能唯一确定一个学生，一个学生只在一个系学习。所以，当“学号”确定后，学生姓名和该学生所在系也被唯一确定了。

这时我们可以说

Sno函数决定Sname和Sdept，或者说Sname，Sdept函数依赖于Sno

可记作：Sno->Sname,Sno->Sdept

完全函数依赖

在R(U)中，若X->Y，并且对于X的任意一个真子集X’，都有Y不依赖于X’，那么称Y对X完全函数依赖。

比如一个学生的学号和课程号才能共同决定一个学生该门课的成绩，即（Sno,Cno)->Grade，但X的真子集为Sno或者Cno，他们无法单独决定该门课的成绩，所以学号和课程号完全函数依赖成绩

部分函数依赖

在R(U)中，若X->Y，对于X的任意一个真子集X’，有Y依赖于X’ ，那么称Y对X部分函数依赖。

比如（Sno,Cno)->Sname,X的真子集Sno和Cn都能单独决定学生姓名，所以学号和课程号部分函数依赖学生姓名。

传递函数依赖

在R(U)中，如果Y依赖于X，X不依赖于Y，Z依赖于Y，则称Z对X传递函数依赖。

比如：Sno->Sdept,Sno不依赖于Sdept，Sdept->Manager,则Sno传递函数依赖于Manager

直接函数依赖

在R(U)中，如果Y依赖于X，X依赖于Y，Z依赖于Y，则称Z对X直接函数依赖。

比如：Sno->Sdept,Sdept->Sno，Sdept->Manager,则Sno直接函数依赖于Manager

范式

范式是衡量关系的规范化程度。

满足最低要求的是第一范式，简称1NF。在第一范式中满足进一步要求为第二范式，简称2NF，以此类推。
1NF<2NF <3NF <BCNF <4NF <5NF
一个低一级的范式关系模式，通过模式分解可转换为更高一级的范式关系模式集合，这个过程就叫规范化。

第一范式 (1NF)

要求任何一张表必须有主键，每一个字段原子性不可再分。

学号 姓名 班级 正班长 副班长
2018029 成小白 5班 龙龙 豫豫
2018030 晨小美 5班 龙龙 豫豫

在上面这个表格中，所有属性都是不可再分项，所以该关系为1NF。

第二范式(2NF)

建立在第一范式的基础之上，要求

所有非主键字段完全依赖主键

若关系模式R属于1NF，且R中每个非主属性都完全函数依赖于主关键字，则称R是第二范式(2NF)

也就是说当1NF消除了非主属性对关键字的部分函数依赖，则称为2NF。

案例:

学生关系S(Sno,Sname,Sdept,Manager,Cno,Grade)

• 关系S属于第一范式

• 关系S不属于第二范式，存在非主属性对(Sno,Cno)的部分函数依赖

• 如果将该学生关系S分解成以下两个关系S1和S2，

  • S1(Sno,Cno,Grade),这时Grade完全依赖于主码
    没有部分函数依赖，所以属于第二范式

  • S2(Sno,Sname,Sdept,Manager)
    主码是Sno，其他非主关键字都是完全函数依赖于主码
    属于第二范式

多对多，三张表，关系表两个外键！

第三范式(3NF)`

建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

举例：

• 关系S1(Sno,Cno,Grade)，不存在非主属性对主属性的部分函数依赖个传递函数依赖，所以S1属于第三范式。

• 关系S2(Sno,Sname,Sdept,Manager)，存在非主属性Manager对码Sno的传递函数依赖，即Sno->Sdept,Sdept不依赖于Sno，Sdept->Manager，Sno传递函数依赖于Manager。所以S2不属于第三范式。

• 若将S2分解成下述两个关系S3和S4，即：

  • S3(Sno,Sname,Sdept)，消除了非主属性对主属性的部分函数依赖和传递函数依赖，属于第三范式。

  • S4(Sdept,Manager),消除了非主属性对主属性的部分函数依赖和传递函数依赖，属于第三范式。
    解决了删除异常、更新异常和冗余度大等问题

一对多，两张表，多的表加外键！

BC范式(BCNF)

当第三范式消除了主属性对码的部分和传递函数依赖，称为BCNF

• 如果关系R是BCNF，那么R一定是3NF

• 如果R是第三范式，并且R只有一个候选码，则R必属于BCNF

• 二元关系模式R必定是BCNF

• 都是主属性的关系并非一定属于BCNF

• 从数据库设计的角度看，在函数依赖的基础上，分解最高范式BCNF的模式中仍然勋

注：原文链接：https://blog.csdn.net/m0_48834053/article/details/10920、

数据库设计

MySQL数据库作为数据存储的介质为应用系统提供数据存储的服务，我们如何设计出合理的数据库，数据表以满足应用系统的数据存储需求呢

• 车库：是用来存放车辆的，车库都需要划分车位，如果不划分车位，车子杂乱无章存放可能会导致车辆堵塞，同时也可能造成场地的浪费--有限的场地能够停放最多的车辆，同时方便每一辆车的出入
• 数据库：是用来存储数据的，我们需要设计合理的数据表--能够完成数据的存储，同时能够方便的提取应用系统所需的数据

设计流程

数据库是为应用系统提供的，数据存储什么样的数据也是有应用系统来决定的

当我们进行应用系统开发时，我们首先要明确应用系统的功能需求--软件系统的需求分析

1. 根据应用系统的功能，分析数据实体(实体，就是要存储的数据对象)

​ 电商系统：商品、用户、订单...

​ 教务管理系统：学生、课程、成绩

2. 提取实体的数据项(数据项，就是实体的属性)

   商品(商品名称，商品图片，商品描述...)

   用户(姓名，登录名，登录密码...)

3. 根据数据库设计三范式规范来检查数据项的提取是否合理(检查实体的数据项是否满足数据库设计三范式)

   如果实体的数据项不满足三范式，可能会导致数据的冗余，从而引起数据维护困难、破坏数据一致性等问题。

4. 绘制 E- R 图 (实体关系图，直观的展示 实体与实体之间的关系 )

5. 数据库建模

   • 三相表进行数据库设计

   • PowerDesigner

   • PDMan

   6. 建库建表 编写SQL指令创建数据库、数据表
   7. 添加测试数据 ，SQL测试

数据库设计案例

学校图书馆图书管理系统(借书)

数据实体

• 学生
• 类别
• 图书
• 借书记录
• 管理员

提取数据项

• 学生 (学号、姓名、性别、年龄、院系编号、院系名称、院系说明...)
• 类别 (类别ID、类别名称、类别描述...)
• 图书 (图书ID、图书名称、图书作者、图书封面、图书价格、图书库存...)
• 借书记录 (记录ID、学号、图书编号、数量、是否归还、借书日期、还书日期...)
• 管理员 (管理员ID、登录名、登录密码、员工编号)
• 员工 (员工编号、员工姓名、员工联系方式 )

数据库设计三范式

• 第一范式：要求数据表中的字段(列)不可再分

• 第二范式：不存在非关键字段对关键字段的部分依赖

• 第三范式：不存在非关键字段之间的传递依赖

绘制 E-R 图

E-R (Entity - Relation) 实体管辖图，用于直观的体现实体与实体之间的联系 (一对一、一对多、多对多、多对一、多对多)

画图地址：processon.com

Python连接数据库

连接数据库

import pymysql
try:
    db = pymysql.connect(host='localhost', port=3306, user='root', password='leizhe', db='test', charset='utf8')
    print("数据库成功连接")
except pymysql.Error as e:
    print("数据库连接失败："+ str(e))

索引

索引结构

B+树：

相对于B-Tree区别：

• 所有的数据都会出现在叶子节点上
• 叶子节点形成一个单向链表

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

Hash

Hash索引特点

• Hash索引智能用于对等比较(=, in)，不支持范围查询(between,>,>,...)
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次检索就可以了，效率通常高于B+Tree索引

存储引擎支持

在MYSQL中，支持hash索引的是Memory引擎，InnoDB中具有适应hash功能，hash索引是存储引擎根据B+Tree在指定条件下自动构建的。

思考

为什么InnoDB存储引擎选择使用B+Tree索引结构？

• 相对于二叉树，层级更少，搜索效率高
• 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。

索引分类

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

演示图：

• 回表查询

先根据二级索引找到主键值，然后聚集索引拿到行数据。

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
• 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

思考题

1. 以下 SQL 语句，哪个执行效率高？为什么？

select * from user where id = 10;
select * from user where name = 'Arm';
-- 备注：id为主键，name字段创建的有索引

答：第一条语句，因为第二条需要回表查询，相当于两个步骤。

2. InnoDB 主键索引的 B+Tree 高度为多少？

答：假设一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB 的指针占用6个字节的空间，主键假设为bigint，占用字节数为8.
可得公式：n * 8 + (n + 1) * 6 = 16 * 1024，其中 8 表示 bigint 占用的字节数，n 表示当前节点存储的key的数量，(n + 1) 表示指针数量（比key多一个）。算出n约为1170。

如果树的高度为2，那么他能存储的数据量大概为：1171 * 16 = 18736；
如果树的高度为3，那么他能存储的数据量大概为：1171 * 1171 * 16 = 21939856。

另外，如果有成千上万的数据，那么就要考虑分表，涉及运维篇知识。

索引语法

创建索引：
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);
如果不加 CREATE 后面不加索引类型参数，则创建的是常规索引

查看索引：
SHOW INDEX FROM table_name;

删除索引：
DROP INDEX index_name ON table_name;

案例：

-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on tb_user(name);
-- phone手机号字段的值非空，且唯一，为该字段创建唯一索引
create unique index idx_user_phone on tb_user (phone);
-- 为profession, age, status创建联合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);
-- 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);
-- 查询索引
show index from tb_user;
-- 删除索引
drop index idx_user_email on tb_user;

SQL性能分析

• SQL执行频率

Mysql客户端连接成功后，通过show [session | global] status 命令可以提供服务器状态信息。可以通过如下指令，可以查看当前数据库的insert,update,delete,select 的访问频次：

show global status like 'Com_______';

• 慢查询日志

慢查询日志记录了所有执行时间超过指定参数( long_query_time ,单位:秒 ， 默认 10 秒 )的所有SQL语句的日志。

MYSQL的慢查询日志默认没有开启，需要在MYSQL的配置文件( /etc/my.cnf )中配置如下信息：

查询慢查询日志状态：

show variables like 'slow_query_log';

# 开启mysql慢日志查询开关
slow_query_log = 1
# 设置慢日志的时间为2秒，sql语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2
# mysql
set global slow_query_log = 'ON';
set long_query_time = 2;

• profile详情

show profiles 能够在做sql优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MYSQL是否支持profile操作：

select @@have_profiling;

默认profiling时关闭的，可以通过set语句在session/global级别开启profiling：

set profiling = 1

执行一系列的业务sql的操作，然后通过如下指令查看指令的执行耗时：

# 查看每一条sql的耗时情况
show profiles;
# 查看指定query_id的sql语句各个阶段的耗时情况
show profiles for query query_id;
# 查看指定query_id 的sql语句CPU使用情况
show profile cpu for query query_id;

• explain执行计划

explain或者desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。

语法：

# 直接在select语句之前加上关键字explain / desc
explain select 字段列表 from 表明 where 条件

EXPLAIN执行计划各字段含义：

• id

select 查询的序列号，表示查询中执行select子句或者是操作表的顺序( id相同，执行顺序从上到下;id不同，值越大，越先执行 )。

• select_type

表示select的类型，常见的取值有SIMPLE ( 简单表，即不使用表连接或者子查询 )、PRIMARY（ 主查询，即外城的从查询 ）、UNION( UNION中的第二个或者后面的查询语句 )、SUBQUERY( SELECT / WHERE 周包含了子查询等。 )

• type

表示连接类型，性能由好到差的连接类型为NULL、system、const( 主键 唯一索引 )、eq_ref( 非唯一 )、ref( 非唯一和主键类型 -》 常规索引 )、range、index、all。

• possible_key

显示可能应用在这张表上的索引，一个或多个。

• key

实际用到的索引

• key_len

表示索引中使用的字节数

• rows

mysql认为必须要查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的。

• filtered

表示赶回结果行数占需读取数的百分比，filtered的值越大越好。

索引使用

最左前缀法则

如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

联合索引中，出现范围查询（>, <），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。

索引失效情况

substing() 截取字符串 substring(phone,10,2) 从第十位截取2位号码

1. 在索引列上进行运算操作，索引将失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15';
2. 字符串类型字段使用时，不加引号，索引将失效。如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号
3. 模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。
4. 用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。
5. 如果 MySQL 评估使用索引比全表更慢，则不使用索引。

SQL 提示

是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

例如，使用索引：
explain select * from tb_user use index(idx_user_pro) where profession="软件工程";
不使用哪个索引：
explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";
必须使用哪个索引：
explain select * from tb_user force index(idx_user_pro) where profession="软件工程";

use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改，force就是无论如何都强制使用该索引。

覆盖索引&回表查询

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少 select *。

explain 中 extra 字段含义：
using index condition：查找使用了索引，但是需要回表查询数据
using where; using index;：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

null:可能出现了回表查询

如果在聚集索引中直接能找到对应的行，则直接返回行数据，只需要一次查询，哪怕是select *；如果在辅助索引中找聚集索引，如select id, name from xxx where name='xxx';，也只需要通过辅助索引(name)查找到对应的id，返回name和name索引对应的id即可，只需要一次查询；如果是通过辅助索引查找其他字段，则需要回表查询，如select id, name, gender from xxx where name='xxx';

所以尽量不要用select *，容易出现回表查询，降低效率，除非有联合索引包含了所有字段

面试题：一张表，有四个字段（id, username, password, status），由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案：
select id, username, password from tb_user where username='itcast';

解：给username和password字段建立联合索引，则不需要回表查询，直接覆盖索引

前缀索引

当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只降字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：create index idx_xxxx on table_name(columnn(n));
前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
求选择性公式：

select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

show index 里面的sub_part可以看到接取的长度

单列索引&联合索引

单列索引：即一个索引只包含单个列
联合索引：即一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

单列索引情况：
explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';
这句只会用到phone索引字段

注意事项

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4. 如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

锁

全局锁

• 介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例

就处于只读状态，后续的DML的写语句，DDL语句，已经个更新操作的事务提交语句都将被阻塞。

其典型的使用是数据库的逻辑备份，对所有的表进行锁定，从而获一致性视图，保证数据的完整性。

• 操作备份

-- 添加锁
flush tables with read lock;
-- 备份数据库
mysqldump -uroot -p123456 itcast > itcast.sql
-- 关闭锁
unlock tables;

• 特点

数据库中全局锁，是一个比较重的操作，存在一下问题：

1. 如果在主库上备份，那么在备份期间都不能执行更新,业务基本就得停摆
2. 如果在从库上备份，那么在 备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 -- single-transaction 参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -uroot -p123456 itcast >itcast.sql

备份数据库

1.备份全部数据库的数据和结构

mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql

2.备份全部数据库的结构（加 -d 参数）

mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql

3.备份全部数据库的数据(加 -t 参数)

mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql

4.备份单个数据库的数据和结构(,数据库名mydb)

mysqldump -uroot-p123456 mydb > D:/data/mysqlDump/mydb.sql

5.备份单个数据库的结构

mysqldump -uroot -p123456 mydb -d > D:mydbs.sql

6.备份单个数据库的数据

mysqldump -uroot -p123456 mydb -t > D:/mydbdata.sql

7.备份多个表的数据和结构（数据，结构的单独备份方法与上同）

mysqldump -uroot -p123456 mydb t1 t2 > D:/mydb.sql

8.一次备份多个数据库

mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

9.备份全部数据库

mysqldump -uroot -p --all-databases > D:/all.sql

10.直接将MySQL数据库压缩备份

mysqldump -uroot -p123456 -database databasename | gzip > D:/backupfile.sql.gz

还原mysql备份内容

• 还原全部数据库

有两种方式还原，第一种是在MySQL命令行中，第二种是使用SHELL行完成还原

1.在系统命令行中，输入如下实现还原：

mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

2.在登录进入mysql系统中,通过source指令找到对应系统中的文件进行还原：

mysql> source /data/mysqlDump/mydb.sql

• 还原单个数据库(需指定数据库)

mysql>use mydb   
mysql>source f:\mydb.sql

mysql -uroot -p123456 mydb <f:\mydb.sql

• 还原单个数据库的多个表(需指定数据库)

mysql>use mydb
mysql>source f:\multables.sql

mysql -uroot -p123456 mydb<f:\multables.sql

表级锁

• 介绍

表级锁，每次操作锁住整张表。锁定力度大，发生锁冲突的概率最高，并发度最低。应用于MyISAM、InnoDB，BDB等存储引擎中。

表锁

表锁分为两类：

1. 表共享读锁( read lock )

   1. 自己只能读，不能写 其它客户端不能读，不能写 并且处于阻塞状态

2. 表独占写锁( write lock )

   1. 自己可以DQL DDL DML 其它客户端 不能DQL DDL DML

语法：

1. 加锁：lock tables 表名... read / write
2. 释放锁：unlock tables/ 客户端断开连接

读锁不会阻塞其它客户端的读，当时会阻塞写。写锁既会阻塞其客户端的读，又会阻塞其它客户端的写。

元数据锁( meta data lock , MDL)

• 介绍

MDL加锁过程时系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL主要作用时维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。

为了避免DML与DDL冲突，保证数据的正确性。

当对一张表进行增删改查的时候，加MDL读锁( 共享 );对表结构进行变更操作的时候，加MDL写锁( 排他 )。

• 查看元数据锁

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

意向锁

• 介绍

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，是的表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

1. 意向共享锁( IS )：由语句 select ... lock in share mode 添加。
   1. 与表锁共享锁( read )兼容，与表锁排它锁( write )互斥。
2. 意向排他锁( IX )：由insert、update、delete、select ... for update 添加。
   1. 与表锁共享锁( read )及排它锁( write )都互斥。意向锁之间不会互斥。

查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

• 测试

-- 添加共享 意向锁
select * from book where bookid = 1 lock in share mode;

行级锁

• 介绍

行级锁，每次操作所著对应的行数据。锁定力度小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录家的锁。对于行级锁，主要分为以下三类：

1. 行锁( Record Lock)：锁定单个记录的锁，防止其它事务对此进行update和delete，在RC，RR隔离级别下都支持
2. 间隙锁( Gao Lock) ：锁定索引间隙( 不含该记录 )，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
3. 临键锁( Next_Key_Lock )：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

行锁

InnoDB实现了以下两种类型的行锁：

1. 共享锁( S )：允许一个事务去读一行，组织其它事务获得相同数据集的排他锁
2. 排他锁( X )：允许获取排他锁的事务更新数据，组织其他食物获得相同数据集的共享锁和排他锁

• 行锁类型

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

1. 针对唯一索引进行检索式，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2. InnoDB的行锁时针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁

数据完整性

• 定义实体完整性
• 定义实体完整性
• 用户定义的完整性

运维

分库分表

介绍

分库分表的中心思想都是将数据分散存储，使得代议数据库 / 表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

拆分策略：

垂直拆分 -> 垂直分库 垂直分表

水平拆分 -> 水平分库 水平分表

详细介绍

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。

特点：

1. 每个库的表结构不一样
2. 每个库的数据也不一样
3. 所有库的并集是全集数据

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

特点：

1. 每个表的结构不一样
2. 每个表的数据也不一样，一般通过一列(主键 / 外键)关联
3. 所有表的并集是全量数据

水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。

特点：

1. 每个库的表结构都一样
2. 每个库的数据都不一样
3. 所有库的并集是全量数据

水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。

特点：

1. 每个表的表结构一样
2. 每个表的数据不一样
3. 所有表的并集是全量数据

实现技术

• shardingjDBC：基于AOP原理，在应用程序对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。
• MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表分表，支持多种语言，性能不及前者。

数据类型

整型

无符号在数据类型后加 unsigned 关键字。

浮点型

日期和时间

字符串

二进制类型

权限一览表

具体权限的作用详见官方文档

GRANT 和 REVOKE 允许的静态权限

GRANT 和 REVOKE 允许的动态权限

Linux安装数据库

1. 创建目录并解压

mkdir mysql

tar -xvf mysql-8.0.30-1.e17.x86_64.rpm-bundle.tar -C mysql

2. 安装MySQL的安装包

cd mysql

rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm 

rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm 

rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm 

rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm

yum install openssl-devel

rpm -ivh  mysql-community-devel-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm

rpm -ivh  mysql-community-server-8.0.26-1.el7.x86_64.rpm

3. 启动MySQL服务

systemctl start mysqld

systemctl restart mysqld

systemctl stop mysqlds

4. 安装注意事项

遇到的问题
1、linux终端报错——>错误：can’t create 事务 lock on /var/lib/rpm/.rpm.lock (权限不够)
解决办法：在终端输入su后运行，输入密码后前缀由[bll@Bailulu mysql]$变成了[root@Bailulu mysql]#
之后就可以继续解压了

2、运行第三条mysql-community-libs报错说
错误：依赖检测失败：mariadb-libs被mysql-community-libs-compat-8.0.30-1.el7.x86_64取代
解决办法：输入并运行 rpm -e mariadb-libs --nodeps

3、运行最后一行报错
错误：依赖检测失败：
mysql-community-icu-data-files = 8.0.30-1.el7 被 mysql-community-server-8.0.30-1.el7.x86_64 需要
先看看有没有icu-data
输入并执行
ls -l mysql-community-icu-data-file*
出现了一串红字mysql-community-icu-data-files-8.0.30-1.el7.x86_64.rpm
说明有，可以直接安装
rpm -ivh mysql-community-icu-data-files-8.0.29-1.el8.x86_64.rpm
完成即可解决

5. 查看密码

另起一个窗口查看默认密码：cat /var/log/mysqld.log

6. 登录MySQL

 ALTER  USER  'root'@'localhost'  IDENTIFIED BY '1234';
 
执行上述的SQL会报错，原因是因为设置的密码太简单，密码复杂度不够。我们可以设置密码的复杂度为简单类型，密码长度为4。

set global validate_password.policy = 0;

set global validate_password.length = 4;

降低密码的校验规则之后，再次执行上述修改密码的指令。

7. 创建可以远程访问的root用户

默认的root用户只能当前节点localhost访问，是无法远程访问的，我们还需要创建一个root账户，用户远程访问

create user ‘root’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘1234’;

给这个root用户分配所有权限

grant all on . to ‘root’@‘%’;

8. 重新连接MySQL

mysql -u root -p

9. 通过navicate远程连接MySQL

左上角新增数据源选择mysql，输入正确的linux主机ip，用户名和密码
测试连接，如果失败就在Linux终端输入 
`systemctl stop firewalld（关闭防火墙）`
测试连接成功后点击确定
开启数据库服务
service mysqld start

es的智能分词和倒排索引