MySQL

DQL（Data Query Language）：数据查询语言
select
DML(Data Manipulate Language):数据操作语言
insert 、update、delete
DDL（Data Define Languge）：数据定义语言
create、drop、alter
TCL（Transaction Control Language）：事务控制语言
commit、rollback

一、基本知识

DDL

对库的操作

创建数据库

CREATE DATABASE IF NOT EXISTS book;

修改数据库

#修改字符集
ALTER DATABASE books CHARACTER SET gbk;

展示数据库

SHOW DATABASES;

删除指定数据库

DROP database_xx;

使用指定数据库

USE database_xx;

对表的操作

创建表

CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
);

展示表

DESCRIBE table_xx;
DESC table_xx;

修改表

alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

#①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE; 

#④删除列
ALTER TABLE book_author DROP COLUMN  annual;

#⑤修改表名
ALTER TABLE author RENAME TO book_author;

删除表

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE  表名();

复制表

#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;

#2.复制表的结构+数据
CREATE TABLE copy2 
SELECT * FROM author;

#只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='中国';

#仅仅复制某些字段
CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;

查看版本

SELECT version();

DQL

基础查询

#从表中查询所有
SELECT * FROM table_name; 

#从表中指定查询
SELECT `last_name`,
			 `first_name` 
FROM table_name;

#起别名
SELECT last_name AS "别名1",
       first_name AS "别名2"
FROM employees;

#或者用空格
SELECT last_name 别名1,
       first_name 别名2
FROM employees;

#去重DISTINCT查询
SELECT DISTINCT department_id FROM employees;

#拼接
SELECT CONCAT(first_name,last_name ) oo
FROM employees;

#如果为NULL，返回0或者指定的数字
SELECT IFNULL(last_name,0) AS xx FROM employees;

#字母字符统一转为0
SELECT 0 = ‘a’; ---1
SELECT 0 = ‘abc’; ---1

#数字字符统一转为数字
SELECT 1 = ‘1’; ---1
SELECT 2 = ‘2’; ---1

进阶查询

select
要查询的字段|表达式|常量值|函数
from
表
where
条件 ;

分类：

条件表达式
- > < = <= <>是不等于
- 安全等于<=>既可以判断NULL，又可以判断普通值
逻辑表达式
示例：salary>10000 && salary<20000

AND（&&）:两个条件如果同时成立，结果为true，否则为false
OR(||)：两个条件只要有一个成立，结果为true，否则为false
NOT(!)：如果条件成立，则not后为false，否则为true
IS NULL：不能用 xx=NULL
模糊查询
- %任意多个字符，如包含a的信息：last_name LIKE '%a%'
- _任意单个字符，如第三个e，第五个为a： last_name LIKE '___e_a%'
- 在100-200之间，包含：BETWEEN 100 AND 200
- 在包含某些条件的信息：last_name IN ('Lucifer' , 'Melrose' , 'Xiaowei')等价于last_name = lucifer OR last_name = melrose OR ....

排序查询

select
要查询的东西
from
表
where
条件

order by 排序的字段|表达式|函数|别名【asc|desc】

#降序
SELECT * FROM employees ORDER BY salary DESC;
#升序
SELECT * FROM employees ORDER BY salary ASC;

常见函数✨

SELECT 函数名(实参列表) FROM 表

单行函数

字符函数
- concat拼接
- substr截取子串(MySQL中索引从1开始)
- upper转换成大写
- lower转换成小写
- trim去前后指定的空格和字符
- ltrim去左边空格
- rtrim去右边空格
- replace替换
- lpad左填充
- rpad右填充
- instr返回子串第一次出现的索引
- length 获取字节个数
数学函数
- round 四舍五入
- rand 随机数
- floor向下取整
- ceil向上取整
- mod取余
- truncate截断
日期函数
- now当前系统日期+时间
- curdate当前系统日期
- curtime当前系统时间
- str_to_date 将字符转换成日期
- date_format将日期转换成字符
- datedief两个日期相隔的天数
流程控制函数
- if 处理双分支
- case语句处理多分支
  - 情况1：处理等值判断
  - 情况2：处理条件判断
其他函数
- version版本
- database当前库
- user当前连接用户

分组函数

sum 求和
max 最大值
min 最小值
avg 平均值
count 计数
特点：
- 以上五个分组函数都忽略null值，除了count()
- sum和avg一般用于处理数值型
- max、min、count可以处理任何数据类型
- 都可以搭配distinct使用，用于统计去重后的结果
- count的参数可以支持：字段、常量值，一般放1，建议使用 count(*)，效果都一样

分组查询

select
查询的字段，分组函数
from
表

where 筛选条件

group by
分组的字段

order by 子句

1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选

针对的表位置关键字
分组前筛选：原始表 group by的前面 where
分组后筛选：分组后的结果集 group by的后面 having

4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名

#查询邮箱中包含a字符，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id

#查询那个部门的员工个数>2
#首先查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#然后根据结果筛选，查询那个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

#每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

多表连接查询

笛卡尔乘积：如果连接条件省略或无效则会出现
解决办法：添加上连接条件

一、传统模式下的连接：等值连接——非等值连接

等值连接的结果 = 多个表的交集
n表连接，至少需要n-1个连接条件
多个表不分主次，没有顺序要求
一般为表起别名，提高阅读性和性能

#1.显示所有员工的姓名，部门号和部门名称。
USE myemployees;

SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;


#2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;



#3.	选择所有有奖金的员工的
last_name , department_name , location_id , city

SELECT last_name , department_name , l.location_id , city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;


#4.选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name 

SELECT last_name , job_id , d.department_id , department_name 
FROM employees e,departments d ,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND city = 'Toronto';


#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_title,MIN(salary) 最低工资
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;


#6.查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING 部门个数>2;


#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees	Emp#	manager	Mgr#
kochhar		101	king	100

SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name='kochhar';

二、sql99语法：通过join关键字实现连接

内连接（inner）
- 等值连接
- 非等值连接
- 自连接
外连接
- 左外连接（left 【outer】)
- 右外连接（right 【outer】）
- 全外连接（full【outer】）
交叉连接 cross

语法

select 字段，…
from 表1
【inner|left outer|right outer|cross】join 表2
【on 连接条件】
【inner|left outer|right outer|cross】join 表3
【on 连接条件】
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了

没有创建外键时，可以使用内外查询将两个表的内容合并在一起查询

内连接

内联查询：两张表通过某个字段合并起来，查询出相关数据

等值连接

#案例1.查询员工名、部门名
SELECT last_name,department_name
FROM departments d
INNER JOIN  employees e
ON e.`department_id` = d.`department_id`;

#案例2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=  j.`job_id`
WHERE e.`last_name` LIKE '%e%';

#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
#①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name

#② 在①结果上筛选员工个数>3的记录，并排序
SELECT COUNT(*) 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;

非等值连接

#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
 
 
#查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

自连接

#查询员工名和直接上级的名称，从自己的表中查询两次
#sql99
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;
#sql92
SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;

#查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`;

#查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

外连接

应用场景：用于查询一个表中有，另一个表没有的记录

特点：
外连接的查询结果为主表中的所有记录
- 如果从表中有和它匹配的，则显示匹配的值
- 如果从表中没有和它匹配的，则显示null
- 外连接查询结果=内连接结果+主表中有而从表没有的记录
左外连接，left join左边的是主表
右外连接，right join右边的是主表
左外和右外交换两个表的顺序，可以实现同样的效果
全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

左外连接

LEFT JOIN ... ON ...
或
LEFT OUTER JOIN ... ON ...

举例：

#引入：查询男朋友 不在男神表的的女神名
SELECT * FROM beauty;
SELECT * FROM boys;

#bo=boys b=beauty
#左外连接
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

#案例1：查询哪个部门没有员工
#左外
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

右外连接

RIGHT JOIN ... ON ...
或
RIGHT OUTER JOIN ... ON ...

举例：

#右外
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;](MySQL.assets/20200810195526.png)](https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200810195526.png)

PS：全外连接FULL是两者的并集，全外连接CROSS是所有的排列组合，是一个笛卡尔乘积

三种连接的图解

内连接

左连接

右连接

额外情况

子查询

含义：

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询

特点：

子查询都放在小括号内
子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
子查询优先于主查询执行，主查询使用了子查询的执行结果
子查询根据查询结果的行数不同分为以下两类：
- 标量子查询
  - 结果集只有一行
  - 一般搭配单行操作符使用：> < = <> >= <=
  - 非法使用子查询的情况：
```
a、子查询的结果为一组值
b、子查询的结果为空
```
- 列子查询
  - 结果集有多行
  - 一般搭配多行操作符使用：any、all、in、not in
  - in：属于子查询结果中的任意一个就行
  - any：和子查询的某一个比较（可代替）
  - all：和子查询的所有比较（可代替）
按结果集的行列数不同：
- 标量子查询（结果集只有一行一列）
- 列子查询（结果集只有一列多行）
- 行子查询（结果集有一行多列）
- 表子查询（结果集一般为多行多列）

select后面：
仅仅支持标量子查询

from后面：
支持表子查询
where或having后面：★
标量子查询（单行） √
列子查询（多行） √
行子查询

exists后面（相关子查询）
表子查询

标量子查询举例：

#案例1：谁的工资比 Abel 高?
#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'

#②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(

	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143

#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

#案例3：返回公司工资最少的员工的last_name,job_id和salary
#①查询公司的最低工资
SELECT MIN(salary)
FROM employees

#②查询last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#①查询50号部门的最低工资
SELECT  MIN(salary)
FROM employees
WHERE department_id = 50

#②查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

#③ 在②基础上筛选，满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50
);

列子查询举例：

#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

#②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN( #<>ALL
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
#①查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

行子查询距离

#案例：查询员工编号最小并且工资最高的员工信息
SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);
#等同于
#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees

#②查询最高工资
SELECT MAX(salary)
FROM employees

#③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);

select后面的标量子查询

#案例：查询每个部门的员工个数
SELECT d.*,(

	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;
 
 #案例2：查询员工号=102的部门名
SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
) 部门名;

from后面将子查询结果充当一张表，要求必须起别名

#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id


SELECT * FROM job_grades;


#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

SELECT  ag_dep.*,g.`grade_level`
FROM (
   SELECT AVG(salary) ag,department_id
   FROM employees
   GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

exists后面（相关子查询）

exists(完整的查询语句)，结果：1或0

#案例1：查询有员工的部门名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
   SELECT department_id
   FROM employees

);

#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
   SELECT *
   FROM employees e
   WHERE d.`department_id`=e.`department_id`
);

#案例2：查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
);

#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.`id`=b.`boyfriend_id`

);

分页查询

应用场景：

实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

语法：

select 字段|表达式,…
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引offset】条目数size;

特点：

起始条目索引从0开始
limit子句放在查询语句的最后
公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
- 假如:
  每页显示条目数sizePerPage
  要显示的页数 page

#案例1：查询前五条员工信息
SELECT * FROM  employees LIMIT 0,5;
SELECT * FROM  employees LIMIT 5;

#案例2：查询第11条——第25条
SELECT *
FROM employees
LIMIT 10,15;

#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

联合查询

引入
union 联合、合并

语法

select 字段|常量|表达式|函数【from 表】【where 条件】 union 【all】
select 字段|常量|表达式|函数【from 表】【where 条件】 union 【all】
select 字段|常量|表达式|函数【from 表】【where 条件】 union 【all】
…..
select 字段|常量|表达式|函数【from 表】【where 条件】

特点

多条查询语句的查询的列数必须是一致的
多条查询语句的查询的列的类型几乎相同
union代表去重，union all代表不去重

#引入的案例：查询部门编号>90或邮箱包含a的员工信息
SELECT *
FROM employees
WHERE email LIKE '%a%'
   OR department_id > 90;;

SELECT *
FROM employees
WHERE email LIKE '%a%'
UNION
SELECT *
FROM employees
WHERE department_id > 90;

#案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

练习

建表语句及插入语句

CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
);

CREATE TABLE teacher(
    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
    t_birthday DATETIME COMMENT'教师生日',
    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
);

CREATE TABLE course(
    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
    t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表',
    FOREIGN KEY(t_no) references teacher(t_no)
);

CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',
        c_no VARCHAR(20)  NOT NULL COMMENT'课程号 依赖于课程表中的c_id',
    sc_degree decimal,
    foreign key(s_no) references student(s_no),
    foreign key(c_no) references course(c_no),
    PRIMARY KEY(s_no,c_no)
); 
--学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

--教师表数据
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

--添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

--添加成绩表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');

INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

查询student表中所有记录的s_name,s_sex和s_class列

SELECT s_no,s_name,s_class FROM  student;

查询教师所有的单位但是不重复的t_depart列

SELECT DISTINCT(t_depart) FROM teacher;

查询score表中成绩在60-80之间所有的记录(sc_degree)

SELECT s_no, sc_degree FROM score WHERE sc_degree BETWEEN 60 AND 80;
SELECT s_no, sc_degree FROM score WHERE sc_degree > 60 AND sc_degree < 80;

查询score表中成绩为85, 86, 或者88的记录(sc_degree)

SELECT s_no, sc_degree FROM score WHERE sc_degree IN(85, 86, 88);

查询student表中’95031’班或者性别为’女’的同学记录

SELECT *FROM student WHERE s_class = '95031' OR s_sex = '女';

以sc_degree降序查询score表中所有的记录

SELECT s_no, sc_degree FROM score ORDER BY sc_degree DESC;

查询’95031’班的学生人数

SELECT COUNT(*) FROM student WHERE s_class = '95031';

查询score表中的最高分数的学生号和课程号

SELECT s_no, c_no FROM score WHERE sc_degree = (SELECT MAX(sc_degree) FROM score);

查询每门课的平均成绩

SELECT c_no, AVG(sc_degree) FROM score GROUP BY c_no;

查询score表中至少有2名学生选修的,并且以3开头的课程的平均分

SELECT c_no, AVG(sc_degree) FROM score GROUP BY c_no HAVING COUNT(c_no) >=2 AND c_no LIKE '3%';

查询所有的学生 s_name , c_no, sc_degree列

SELECT s_name, c_no, sc_degree FROM student, score WHERE student.s_no = score.s_no;

查询所有学生的s_no, c_name, sc_degree列

SELECT s_no, c_name, sc_degree FROM student, score, course WHERE student.s_no = score.s_no AND score.c_no = course.c_no;

查询所有的学生 s_name , c_name, sc_degree列

SELECT s_name, c_name, sc_degree FROM student, score, course WHERE student.s_no = score.s_no AND score.c_no = course.c_no;

查询班级是’95031’班学生每门课的平均分

SELECT c_no, AVG(sc_degree) FROM score WHERE s_no IN (SELECT s_no FROM student WHERE s_class = '95031') GROUP BY(c_no);

SELECT c_no, AVG(sc_degree) FROM student AS s LEFT JOIN score AS sc ON s.s_no = sc.s_no WHERE s_class = '95031' GROUP BY c_no;

--查询内容包含课程名（c_name）
SELECT c.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, course AS c, score AS sc WHERE s.s_no = sc.s_no AND sc.c_no = c.c_no AND s.s_class = '95031'  GROUP BY c.c_no;

查询选修”3-105”课程的成绩高于’109’号同学’3-105’成绩的所有同学的记录

SELECT *FROM student AS s, score AS sc WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND sc.c_no = '3-105' AND s.s_no = sc.s_no;

查询所有与学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday

SELECT *FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN ('101', '108'));

查询张旭教师任课的学生的成绩

SELECT s.s_name, sc.sc_degree FROM score AS sc, student AS s WHERE c_no = (SELECT c_no FROM course WHERE t_no = (SELECT t_no FROM teacher WHERE t_name = '张旭')) AND s.s_no = sc.s_no;

查询95033班和95031班全体学生的记录

SELECT * FROM student WHERE s_class IN ('95033', '95031') ORDER BY s_class;

查询存在85分以上成绩的c_name和对应的老师

SELECT t_name, c_name FROM course AS c, teacher AS t WHERE c_no IN (SELECT c_no FROM score WHERE sc_degree > 85) AND c.t_no = t.t_no;

查出所有’计算机系’ 教师所教课程的教师信息、课程信息及学生信息

SELECT  * FROM teacher AS t, course AS c, student AS s, score AS sc 
WHERE s.s_no = sc.s_no 
AND c.t_no = t.t_no 
AND sc.c_no = c.c_no 
AND t.t_no IN (SELECT t_no FROM teacher WHERE t_depart = '计算机系');

查询’计算机系’与’电子工程系’ 不同职称的教师的name和rof

SELECT * FROM teacher WHERE t_depart = '计算机系' 
AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '电子工程系')
UNION 
SELECT * FROM teacher WHERE t_depart = '电子工程系' 
AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '计算机系');

查询选修编号为”3-105”课程且成绩至少高于选修编号为’3-245’同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序

SELECT * FROM score WHERE c_no = '3-105' AND sc_degree > ANY(SELECT sc_degree FROM score WHERE c_no = '3-245') ORDER BY sc_degree DESC;

查询选修编号为”3-105”且成绩高于选修编号为”3-245”课程的同学c_no.s_no和sc_degree

SELECT * FROM score WHERE c_no = '3-105' 
AND sc_degree > ALL(SELECT sc_degree FROM score WHERE c_no = '3-245')
ORDER BY sc_degree DESC;

查出学生的信息,课程名称,分数(s_name c_name,sc_degree)

SELECT s.s_name, c.c_name, sc.sc_degree FROM student AS s, course AS c, score AS sc
WHERE s.s_no = sc.s_no
AND c.c_no = sc.c_no
AND sc.sc_degree > ALL(SELECT sc_degree FROM score WHERE c_no = '3-245')
ORDER BY sc_degree DESC;

总结:

ANY 和 ALL
- ANY:表示任何一个就行了,如;数组A中的值比数组B中任何一个都要大,那么只要A和B中最小的比较就行了
- ALL:表示所有都要比较,如:数组A中的值比数组B中所有的数都要大,那么A要和B中最大的值比较才行
查询所有教师和同学的 name ,sex, birthday

SELECT s_name, s_sex, s_birthday FROM student
UNION
SELECT t_name, t_sex, t_birthday FROM teacher;

查询所有’女’教师和’女’学生的name,sex,birthday

SELECT s_name, s_sex, s_birthday FROM student WHERE s_sex = '女'
UNION
SELECT t_name, t_sex, t_birthday FROM teacher WHERE t_sex = '女';

查询成绩比该课程平均成绩低的同学的成绩表

SELECT * FROM score AS a WHERE sc_degree < (SELECT AVG(sc_degree) FROM score AS b WHERE a.c_no = b.c_no);

查询成绩比该课程平均成绩低的同学的成绩表，并显示出学生name,课程name以及分数

SELECT s.s_name AS 学生姓名, c.c_name AS 课程名, a.sc_degree AS 成绩  FROM score AS a, student AS s, course AS c 
WHERE sc_degree < (SELECT AVG(sc_degree) FROM score AS b WHERE a.c_no = b.c_no)
AND s.s_no = a.s_no
AND c.c_no = a.c_no;

查询所有任课教师的t_name 和 t_depart(要在分数表中可以查得到)

SELECT * FROM teacher WHERE t_no IN (SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score));

查出至少有2名男生的班号

SELECT s_class FROM student WHERE s_sex = '男' 
GROUP BY s_class HAVING COUNT(s_no) > 1;

查询student 表中不姓”王”的同学的记录

SELECT * FROM student WHERE s_name NOT LIKE '王%';

查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)

SELECT s_name AS 姓名, (YEAR(NOW())-YEAR(s_birthday)) AS 年龄 FROM student;

查询student中最大和最小的 s_birthday的值

SELECT MAX(s_birthday), MIN(s_birthday) FROM student;

以班级号和年龄从大到小的顺序查询student表中的全部记录

SELECT * FROM student ORDER BY s_class DESC, (YEAR(NOW()) - YEAR(s_birthday)) DESC;

查询”男”教师及其所上的课

SELECT t.t_name, c.c_name FROM teacher AS t, course AS c WHERE t.t_sex = '男'  AND t.t_no = c.t_no;

查询最高分同学的s_no c_no 和 sc_degree;

SELECT * FROM student WHERE s_no = (SELECT s_no FROM score WHERE sc_degree  = (SELECT MAX(sc_degree) FROM score));

查询和”李军”同性别的所有同学的s_name

SELECT s_name FROM student WHERE s_sex =  (SELECT s_sex FROM student WHERE s_name = '李军');

查询和”李军”同性别并且同班的所有同学的s_name

SELECT s_name FROM student WHERE s_sex =  (SELECT s_sex FROM student WHERE s_name = '李军')
AND s_class = (SELECT s_class FROM student WHERE s_name = '李军');

查询所有选修’计算机导论’课程的’男’同学的成绩表，并显示出s_name,c_name

SELECT s.s_name, c.c_name, sc.sc_degree FROM student AS s, score AS sc, course AS c 
WHERE s.s_no = sc.s_no AND c.c_no = sc.c_no 
AND c.c_name = '计算机导论'
AND s.s_sex = '男';

DML

插入操作

方式一

INSERT INTO table
VALUES (‘’, ‘’…);

举例

#1.插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);

#2.不可以为null的列必须插入值。可以为null的列如何插入值？
#方式一：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
#方式二：
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','1388888888');

#4.列数和值的个数必须一致
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');

#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);

方式二

insert into 表名
set 列名=值,列名=值,…

举例

INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';

两种方式的比较

#1、方式一支持插入多行,方式二不支持

INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);

#2、方式一支持子查询，方式二不支持

INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';

INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1234567'
FROM boys WHERE id<3;

练习

#方式一：
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
DELETE FROM my_employees;

#方式二：
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION
SELECT 4,'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;

删除操作

方式一：DELETE

单表的删除★

DELETE FROM table
WHERE cow = ‘’;

举例

#案例：删除手机号以9结尾的女神信息

DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;

多表的删除

#sql92语法：
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

#sql99语法：

delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

举例

#案例：删除张无忌的女朋友的信息

DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';


#案例：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';

方式二：truncate语句

#案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys ;

比较

delete 可以加where 条件，truncate不能加
truncate删除，效率高一丢丢
假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始。
truncate删除没有返回值，delete删除有返回值
truncate删除不能回滚，delete删除可以回滚.

修改数据

修改单表的记录★

update 表名
set 列=新值,列=新值,…
where 筛选条件;

举例

#案例1：修改beauty表中姓唐的女神的电话为13899888899
UPDATE beauty SET phone = '13899888899'
WHERE NAME LIKE '唐%';

#案例2：修改boys表中id好为2的名称为张飞，魅力值 10
UPDATE boys SET boyname='张飞',usercp=10
WHERE id=2;

修改多表的记录

#sql92语法：
update 表1 别名,表2 别名
set 列=值,…
where 连接条件
and 筛选条件;

#sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,…
where 筛选条件;

举例

#案例 1：修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';

#案例2：修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;

SELECT * FROM boys;

变量

系统变量

说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
使用步骤：
1、查看所有系统变量
show global|【session】variables;
2、查看满足条件的部分系统变量
show global|【session】 variables like ‘%char%’;
3、查看指定的系统变量的值
select @@global|【session】系统变量名;
4、为某个系统变量赋值
方式一：
set global|【session】系统变量名=值;
方式二：
set @@global|【session】系统变量名=值;

全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启

#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
#④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

会话变量

作用域：针对于当前会话（连接）有效

#①查看所有会话变量
SHOW SESSION VARIABLES;
#②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#④为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

自定义变量

说明：变量由用户自定义，而不是系统提供的
使用步骤：
1、声明
2、赋值
3、使用（查看、比较、运算等）

用户变量

作用域：针对于当前会话（连接）有效，作用域同于会话变量

#①声明并初始化,=或:=
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

#②赋值（更新变量的值）
#方式一：
   SET @变量名=值;
   SET @变量名:=值;
   SELECT @变量名:=值;
#方式二：
   SELECT 字段 INTO @变量名
   FROM 表;
#③使用（查看变量的值）
SELECT @变量名;

局部变量

作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话

#①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;

#②赋值（更新变量的值）
#方式一：
   SET 局部变量名=值;
   SET 局部变量名:=值;
   SELECT @局部变量名:=值;
#方式二：
   SELECT 字段 INTO 具备变量名
   FROM 表;
   
#③使用（查看变量的值）
SELECT 局部变量名;

举例

#案例：声明两个变量，求和并打印

#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

			       作用域    	      		定义位置     							 语法
用户变量   当前会话      						会话的任何地方      		 加@符号，不用指定类型
局部变量   定义它的BEGIN END中     BEGIN END的第一句话 		一般不用加@,需要指定类型

存储过程和函数

提高代码的重用性，简化操作

存储过程

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN

   存储过程体（一组合法的SQL语句）
END

参数列表包含三部分

参数模式
参数名
参数类型

例如：in stuname varchar(20)

参数模式：

in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

特别需要注意的是：

如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $

调用过程

CALL 存储过程名(实参列表);

空参列表

#案例：插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
   INSERT INTO admin(username,`password`) 
   VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $

CALL myp1()$

创建带in模式参数的存储过程

#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
   SELECT bo.*
   FROM boys bo
   RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
   WHERE b.name=beautyName;
   
END $

#调用
CALL myp2('柳岩')$

#案例2 ：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	
	SELECT COUNT(*) INTO result#赋值
	FROM admin
	WHERE admin.username = username
	AND admin.password = PASSWORD;
	
	SELECT IF(result>0,'成功','失败');#使用
END $

#调用
CALL myp3('张飞','8888')$

创建out模式参数的存储过程

#案例1：根据输入的女神名，返回对应的男神名
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
   SELECT bo.boyname INTO boyname
   FROM boys bo
   RIGHT JOIN
   beauty b ON b.boyfriend_id = bo.id
   WHERE b.name=beautyName ;
   
END $

CALL myp6('Luci',@bname)$
SELECT @bname$

#案例2：根据输入的女神名，返回对应的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
	SELECT boys.boyname ,boys.usercp INTO boyname,usercp
	FROM boys 
	RIGHT JOIN
	beauty b ON b.boyfriend_id = boys.id
	WHERE b.name=beautyName ;
	
END $


#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$

创建带inout模式参数的存储过程

#案例1：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
   SET a=a*2;
   SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

删除存储过程

DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#不支持删除多个

查看存储过程的信息

SHOW CREATE PROCEDURE  myp2;

函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果

语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
   函数体
END

注意

1.参数列表包含两部分：参数名参数类型

2.函数体：肯定会有return语句，如果没有会报错如果return语句没有放在函数体的最后也不报错，但不建议

3.函数体中仅有一句话，则可以省略begin end 4.使用 delimiter语句设置结束标记

调用函数

SELECT 函数名(参数列表)

无参有返回

#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN

   DECLARE c INT DEFAULT 0;#定义局部变量
   SELECT COUNT(*) INTO c#赋值
   FROM employees;
   RETURN c;
   
END $

SELECT myf1()$

有参有返回

#案例1：根据员工名，返回它的工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
   SET @sal=0;#定义用户变量 
   SELECT salary INTO @sal   #赋值
   FROM employees
   WHERE last_name = empName;
   
   RETURN @sal;
END $

SELECT myf2('k_ing') $

#案例2：根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
   DECLARE sal DOUBLE ;
   SELECT AVG(salary) INTO sal
   FROM employees e
   JOIN departments d ON e.department_id = d.department_id
   WHERE d.department_name=deptName;
   RETURN sal;
END $

SELECT myf3('IT')$

查看函数

SHOW CREATE FUNCTION myf3;

删除函数

DROP FUNCTION myf3;

案例

#一、创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
   DECLARE SUM FLOAT DEFAULT 0;
   SET SUM=num1+num2;
   RETURN SUM;
END $

SELECT test_fun1(1,2)$

流程控制结构

if函数

语法：if(条件,值1，值2)
功能：实现双分支
应用在begin end中或外面

case函数

情况1：类似于switch
case 变量或表达式
when 判断的值1 then 返回的值1或语句;
when 判断的值2 then 返回的值2或语句;
…
else 返回的值n或语句;
end

情况2：
case
when 判断的值1 then 返回的值1或语句;
when 判断的值2 then 返回的值2或语句;
…
else 返回的值n或语句;
end

应用在begin end 中或外面

ps:如果是语句，只能放在begin end中

if结构

if 条件1 then 语句1;
elseif 条件2 then 语句2;
….
else 语句n;
end if;
功能：类似于多重if

只能应用在begin end 中

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
   DECLARE ch CHAR DEFAULT 'A';
   IF score>90 THEN SET ch='A';
   ELSEIF score>80 THEN SET ch='B';
   ELSEIF score>60 THEN SET ch='C';
   ELSE SET ch='D';
   END IF;
   RETURN ch;
   
   
END $

SELECT test_if(87)$


#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
   IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
   ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
   ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
   END IF;
   
END $

CALL test_if_pro(2100)$

#案例3：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
   DECLARE ch CHAR DEFAULT 'A';
   
   CASE 
   WHEN score>90 THEN SET ch='A';
   WHEN score>80 THEN SET ch='B';
   WHEN score>60 THEN SET ch='C';
   ELSE SET ch='D';
   END CASE;
   
   RETURN ch;
END $

SELECT test_case(56)$

while

【标签:】while 循环条件 do
循环体;
end while【标签】;

联想：

while(循环条件){

循环体;
}

loop

【标签:】loop
循环体;
end loop 【标签】;

可以用来模拟简单的死循环

repeat

【标签：】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;

没有添加循环控制语句

#案例：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
   DECLARE i INT DEFAULT 1;
   WHILE i<=insertCount DO
      INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
      SET i=i+1;
   END WHILE;
   
END $

CALL pro_while1(100)$

iterate类似于 continue，继续，结束本次循环，继续下一次
leave 类似于 break，跳出，结束当前所在的循环

添加leave语句（此时必须加名称）

#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
   DECLARE i INT DEFAULT 1;
   a:WHILE i<=insertCount DO
      INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
      IF i>=20 THEN LEAVE a;
      END IF;
      SET i=i+1;
   END WHILE a;
END $


CALL test_while1(100)$

添加iterate语句（此时必须加名称）

#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
   DECLARE i INT DEFAULT 0;
   a:WHILE i<=insertCount DO
      SET i=i+1;
      IF MOD(i,2)!=0 THEN ITERATE a;
      END IF;
      
      INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
      
   END WHILE a;
END $


CALL test_while1(100)$

案例

/*已知表stringcontent
其中字段：
id 自增长
content varchar(20)

向该表插入指定个数的，随机的字符串
*/
DROP TABLE IF EXISTS stringcontent;
CREATE TABLE stringcontent(
   id INT PRIMARY KEY AUTO_INCREMENT,
   content VARCHAR(20)
   
);
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
   DECLARE i INT DEFAULT 1;
   DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
   DECLARE startIndex INT;#代表初始索引
   DECLARE len INT;#代表截取的字符长度
   WHILE i<=insertcount DO
      SET startIndex=FLOOR(RAND()*26+1);#代表初始索引，随机范围1-26
      SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取长度，随机范围1-（20-startIndex+1）,content-20
      INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
      SET i=i+1;
   END WHILE;

END $

CALL test_randstr_insert(10)$

二、约束

数据类型

数值型：
整型
小数：
  定点数
  浮点数
字符型：
较短的文本：char、varchar
较长的文本：text、blob（较长的二进制数据）

日期型：

整型

分类

tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8 (字节)

特点

① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

如何设置无符号和有符号

DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
   t1 INT(7) unsigned,
   t2 INT(7) ZEROFILL 

);

DESC tab_int;


INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(2147483648,4294967296);

INSERT INTO tab_int VALUES(123,123);


SELECT * FROM tab_int;

小数

分类

浮点型
float(M,D)
double(M,D)

定点型
dec(M，D)
decimal(M,D)

特点

M：整数部位+小数部位，D：小数部位
- 如果超过范围，则插入临界值
M和D都可以省略
- 如果是decimal，则M默认为10，D默认为0
- 如果是float和double，则会根据插入的数值的精度来决定精度
定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

原则

所选择的类型越简单越好，能保存数值的类型越小越好

字符型

分类

较短的文本：

char

varchar

其他：

binary和varbinary用于保存较短的二进制

enum用于保存枚举

set用于保存集合

较长的文本：

text

blob(较大的二进制)

特点

写法 M的意思特点空间的耗费效率
char char(M) 最大的字符数，可以省略，默认为1 固定长度的字符比较耗费高

varchar varchar(M) 最大的字符数，不可以省略可变长度的字符比较节省低

日期型

分类

SHOW VARIABLES LIKE 'time_zone';

SET time_zone='+9:00';

date只保存日期

time 只保存时间

year只保存年

datetime保存日期+时间

timestamp保存日期+时间（用得多）

特点

                         字节                范围                            时区等的影响
datetime 8 1000——9999 不受

timestamp 4 1970-2038 受

更改时区

SHOW VARIABLES LIKE 'time_zone';

SET time_zone='+9:00';

主键约束

PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
能够唯一确定表中的一条记录。我们通过给某个字段添加该约束，就可以使得该字段不重复且不为空。

CREATE TABLE user (
	id INT PRIMARY KEY,
	name VARCHAR(20)
);

联合主键

CREATE TABLE user2 (
	id INT,
	name VARCHAR(20),
    PRIMARY KEY(id, name)
);

此处字段id和name一同作为主键，联合主键要求每个字段加起来不同即可（无需每个字段都不同）

建表后添加主键

ALERT TABLE user2 ADD PRIMARY KEY (id);

或者通过修改字段的方式来添加主键

ALERT TABLE user2 MODIFY id INT PRIMARY KEY;

建表后删除主键

ALERT TABLE user2 DROP  PRIMARY KEY (id);

自增约束

1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过手动插入值，设置起始值

CREATE TABLE user3 (
	id INT PRIMARY KEY AUTO_INCREAMENT,
	name VARCHAR(20),
);

INSERT INTO user3 (name) VALUES ('name'); //插入成功，自动生成了id 

#设置自增
SET auto_increment_increment=3;

自增约束一般与主键搭配使用

唯一约束

UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
约束修饰的字段不可以重复

CREATE TABLE user4 (
	id INT PRIMARY KEY AUTO_INCREAMENT UNIQUE,
	name VARCHAR(20)
);

或者

CREATE TABLE user4 (
	id INT PRIMARY KEY AUTO_INCREAMENT,
	name VARCHAR(20),
	UNIQUE(id, name)
);

非空约束

NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等

CREATE TABLE user4 (
	id INT NOT NULL,
	name VARCHAR(20),
);

默认约束

DEFAULT:默认，用于保证该字段有默认值
比如性别

CREATE TABLE user4 (
	id INT DEFAULT 10,
	name VARCHAR(20),
);

如果我们插入字段时没有传值，就会使用默认值

外键约束

FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号

主表

CREATE TABLE master(
	id INT PRIMARY KEY,
	name VARCHAR(20)
);

从表

CREATE TABLE pet(
	id INT PRIMARY KEY,
	name VARCHAR(20),
	master_id int,
	FOREIGN KEY(master_id) REFERENCES master(id)
);

主表中没有的数据，在副表中是不可以使用的
主表中的数据被副标引用时，是不可以删除的

总结

CREATE TABLE 表名(
   字段名 字段类型 列级约束,
   字段名 字段类型,
   表级约束
)

约束的添加分类：

列级约束：
- 六大约束语法上都支持，但外键约束没有效果
表级约束：
- 除了非空、默认，其他的都支持

列级约束

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL UNIQUE,#非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查,mysql中不支持
	seat INT UNIQUE,#唯一
	age INT DEFAULT  18,#默认约束
	majorId INT REFERENCES major(id)#外键
);

#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;

表级约束

CREATE TABLE stuinfo(
   id INT,
   stuname VARCHAR(20),
   gender CHAR(1),
   seat INT,
   age INT,
   majorid INT,
   
  #【constraint 约束名】 约束类型(字段名) .
   CONSTRAINT pk PRIMARY KEY(id),#主键
   CONSTRAINT uq UNIQUE(seat),#唯一键
   CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查,mysql中不支持
   CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键

位置支持的约束类型是否可以起约束名

列级约束：列的后面语法都支持，但外键没有效果不可以
表级约束：所有列的下面默认和非空不支持，其他支持可以（主键没有效果）

主键和唯一的大对比

保证唯一性是否允许为空一个表中可以有多少个是否允许组合(有一个满足就成立)

主键 √ × 至多有1个 √，但不推荐
唯一 √ √ 可以有多个 √，但不推荐

外键

1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表

删除数据时，先删除从表，再删除主表

可以通过以下两种方法删除主表中的记录

级联删除

ALTER TABLE stu ADD CONSTRAINT fk_stu_major FOREIGN KEY (majorid) REFERENCES major(id) ON DELETE CASCADE;

级联置空

ALTER TABLE stu ADD CONSTRAINT fk_stu_major FOREIGN KEY (majorid) REFERENCES major(id) ON DELETE SET NULL;

修改表的约束

#添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

#添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

举例

#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 


#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

三、数据库的三大范式

第一范式

字段还可以拆分的，就不满足第一范式

比如地址如果写为

地址：四川省成都市高新区天府一街

就是可以被拆分的

如果字段写为

省份：四川省
城市：成都市
区域：高新区
街名：天府一街

就是不可拆分的

建表如下

CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCRAEMENT,
	name VARCHAR(20),
	province VARCHAR(20),
	city VARCHAR(20),
	area VARCHAR(20),
	street: VARCHAR(20)
);

就是符合第一范式的，但并不是拆分的越详细越好

第二范式

满足第一范式的条件下，第二范式要求：除主键外的每一列，都必须完全依赖于主键
如果出现不完全依赖，则只可能发生在联合主键的情况下

不满足第二范式的例子

CREATE TABLE order(
	product_id INT,
	customer_id INT,
	product_name VARCHAR(20),
	customer_name VARCHAR(20),
    PRIMARY KEY(product_id, customer_id)
);

此处product_name只依赖于product_id，customer_name只依赖于customer_id，是完全依赖

满足第二范式的例子

CREATE TABLE 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)
);

第三范式

满足第二范式，除主键外的其他列之间不能有传递依赖关系

不满足第三范式的例子

CREATE TABLE order(
	id INT PRIMARY KEY,
	product_id INT,
	customer_id INT,
	customer_phone INT
);

此处customer_phone又依赖于customer_id，存在传递依赖关系，不满足第三范式

满足第三范式的例子

CREATE TABLE order(
	id INT PRIMARY KEY,
	product_id INT,
	customer_id INT
);

CREATE TABLE customer(
	id INT PRIMARY KEY,
    name VARCHAR(20),
    phone INT
);

四、视图

是一种虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

创建语法的关键字是否实际占用物理空间使用

视图 create view 只是保存了sql逻辑增删改查，只是一般不能增删改

表 create table 保存了数据增删改查

创建视图

利用CREATE VIEW xx AS封装

#案例：查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`
WHERE s.`stuname` LIKE '张%';

CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`;

SELECT * FROM v1 WHERE stuname LIKE '张%';

#2.查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

#②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

修改视图

create or replace view 视图名
as

查询语句;

或者

alter view 视图名
as
查询语句;

#1
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#2
ALTER VIEW myv3
AS
SELECT * FROM employees;

删除视图

DROP VIEW emp_v1,emp_v2,myv3;

查看视图

DESC myv3;

SHOW CREATE VIEW myv3;

更新视图

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;


#1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');

#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

#3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';

具备以下特点的视图不允许更新

#①包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;

#更新失败
UPDATE myv1 SET m=9000 WHERE department_id=10;

#②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;

#更新失败
UPDATE myv2 SET NAME='lucy';

#③Select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;

#更新失败
UPDATE myv3 SET 最高工资=100000;

#④join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id  = d.department_id;

#更新成功，插入失败
UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');

#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;

#更新失败
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;

#⑥where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
	SELECT  manager_id
	FROM employees
	WHERE manager_id IS NOT NULL
);

#更新失败
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';

五、事务

事务是一个最小的不可分割的单元，事务能够保证一个业务的完整性

多条sql语句要么同时成功，要么同时失败，这时就要用到事务，即TCL

自动提交、手动提交和回滚

通过ROLLBACK; 指令可以回滚，但需要关闭自动提交

#关闭一次，也等于开启事务
SET autocommit = 0
#查看自动提交状态
SELECT @@autocommit;

但如果在进行操作之后，执行COMMIT; 则无法再进行回滚（持久化）

同时delete可以回滚，而truncate不能回滚

SAVEPOINT设置保存点

SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

手动开启事务

通过

BEGIN;
或者
START TRANSACTION;

可以手动开启事务

插入数据前

插入数据后

BEGIN;
INSERT INTO person VALUES (4, '老六', 7);
INSERT INTO person VALUES (5, '老七', 9);
或者
START TRANSACTION;
INSERT INTO person VALUES (4, '老六', 7);
INSERT INTO person VALUES (5, '老七', 9);

回滚后

ROLLBACK;

即

步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
…

步骤3：结束事务
commit;提交事务
rollback;回滚事务

四大特征 ACID

原子性：事务是最小的单位，不可再分，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：事物一旦结束（commit），就不可返回（rollback）

隔离性

事务的隔离级别
- 读未提交 read uncommitted
- 读已提交 read committed
- 可以重复读 repeatable read
- 串行化 serializable

事务的并发问题
- 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
- 不可重复读：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致
- 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读

总结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除

解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

查看数据库的隔离级别

版本 5.x
SELECT @@global.tx_isolation;

版本 8.0
SELECT @@global.transaction_isolation;

修改数据库的隔离级别

set global transaction isolation level read committed;

六、MySQL逻辑架构

整体架构

连接层

最上层是一些客服端和连接服务，包括socket通信和大多数基于客服端/服务端工具实现的类似于tcp/ip的通信，主要完成一些类似于连接处理、授权认证及相关安全的方案，在该层上引入了线程池的概念，为通过认证安全接入的客服端提供线程，同样在该层上可以实现基于SSL的安全的连接，服务器也会为安全接入的每个客户端验证它所具有的操作权限

服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析以及优化部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如过程、函数等，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询的顺序是否利用索引，最后生成相应的执行操作

Management Serveices & Utilities	系统管理和控制工具
SQL Interface	SQL 接口。接受用户的 SQL 命令，并且返回用户需要查询的结果。比如 select from 就是调用 SQL Interface
Parser	解析器。 SQL 命令传递到解析器的时候会被解析器验证和解析
Optimizer	查询优化器。 SQL 语句在查询之前会使用查询优化器对查询进行优化，比如有 where 条件时，优化器来决定先投影还是先过滤。
Cache 和 Buffer	查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存，权限缓存等

引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有功能不同

对比项	MylSAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	[表锁](# 表锁)（不适合高并发）	[行锁](# 行锁)（适合高并发操作）
缓存	只缓存索引，不缓存真实数据	不仅缓存索引，还缓存真实数据。对内存要求较高
表空间	小	大
关注点	性能	事务
默认安装	是	是

存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互

七、性能与JOIN

性能下降原因

索引失效

单值索引

创建语句

CREATE INDEX idx_表名_字段名 ON 表名(字段名);

复合索引

CREATE INDEX idx_表名_字段名1字段名2... ON 表名(字段名1, 字段名2 ...);

关联太多JOIN

内连接、外连接的表不要过多

服务器调优及参数设置

SQL执行加载顺序

手写顺序

随着 Mysql 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序

下面是经常出现的查询顺序：

7种JOIN

建表语句

CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT, 
  `deptName` VARCHAR(30) DEFAULT NULL, 
  `address` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT, 
  `name` VARCHAR(20) DEFAULT NULL, 
  `age` INT(3) DEFAULT NULL, 
  `deptId` INT(11) DEFAULT NULL, 
  `empno` INT NOT NULL, 
  PRIMARY KEY (`id`), KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,NULL,100010);

JOIN查询

笛卡尔积

SELECT * FROM t_dept, t_emp;

t_dept共20条记录，t_emp共6条记录。两表共同查询后共120条记录

内连接

SELECT * FROM t_emp a INNER JOIN t_dept b ON  a.deptId = b.id;

左外连接

SELECT * FROM t_emp a LEFT JOIN t_dept b ON  a.deptId = b.id;

右外连接

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON  a.deptId = b.id;

左外连接取左表的独有部分

SELECT * FROM t_emp a LEFT JOIN t_dept b ON  a.deptId = b.id WHERE a.deptId IS NULL;

右外连接取右表的独有部分

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;

注意：判断字段是否为NULL时，不能使用’=’

因为= NULL 的结果不会报错，但是结果永远为false。所以必须使用IS NULL 来进行判空

全外连接

MySQL不支持全外连接，要查询两个表的全集，需要合并两个查询结果，所以要使用 UNION 关键字

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id
UNION
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id;

查询两表独有内容

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id WHERE b.id IS NULL
UNION
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;

八、索引优化

什么是索引

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质： 索引是数据结构。

可以简单理解为：排好序的快速查找数据结构
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

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

索引的优缺点

优点

提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

索引的分类

基本语法

创建

CREATE [UNIQUE] INDEX [indexName] ON table_name(column);

删除
```
DROP INDEX [indexName] ON table_name;
```
查看
```
SHOW INDEX FROM table_name;
```

分类

单值索引

定义：即一个索引只包含单个列，一个表可以有多个单列索引

语法：

--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id), 
KEY (customer_name) --单值索引
);

--单独创建单值索引
CREATE INDEX idx_customer_name ON customer(customer_name);

唯一索引

定义：索引列的值必须唯一，但允许有空值

语法：

--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id), 
KEY (customer_name), --单值索引
UNIQUE (customer_no) --唯一索引
);

--单独创建唯一索引
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

主键索引

定义：设定为主键后数据库会自动建立索引，innodb为聚簇索引

语法：

--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id) --主键索引
);

--单独创建主键索引
ALTER TABLE customer ADD PRIMARY KEY customer(customer_no);

--删除主键索引
ALTER TABLE customer DROP PRIMARY KEY;

--修改建主键索引
必须先删除掉(drop)原索引，再新建(add)索引

复合索引

定义：即一个索引包含多个列

语法：

--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200), 
PRIMARY KEY(id), 
KEY (customer_name), --单值索引
UNIQUE (customer_no), --唯一索引
KEY (customer_no,customer_name) --复合索引
);

--单独创建复合索引
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

MySQL的索引

B树

B树的阶：节点的最多子节点个数。比如 2-3树的阶是3，2-3-4树的阶是4
B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点
关键字集合分布在整颗树中, 即叶子节点和非叶子节点都存放数据
搜索有可能在非叶子结点结束
其搜索性能等价于在关键字全集内做一次二分查找

B+树

B+树是B树的变体，也是一种多路搜索树
B+树的搜索与 B树也基本相同，区别是 B+树只有达到叶子结点才命中（B树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找
所有关键字都出现在叶子结点的链表中（即数据只能在叶子节点【也叫稠密索引】），且链表中的关键字(数据)恰好是有序的
不可能在非叶子结点命中
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层
更适合文件索引系统
B树和 B+树各有自己的应用场景，不能说 B+树完全比 B树好，反之亦然

区别

B树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中
在B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B树的性能好像要比 B+树好，而在实际应用中却是 B+树的性能要好些。因为 B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B树多，树高比B树小，这样带来的好处是减少磁盘访问次数。尽管 B+树找到一个记录所需的比较次数要比 B树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B+树的性能可能还会好些，而且 B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用 B+树的缘故

为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？

B+树的磁盘读写代价更低
- B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了
B+树的查询效率更加稳定
- 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

MySQL中的B+树

业的概念

主键索引

MySQL在创建表时，会根据主键来创建主键索引（如果没有主键，会用一个隐藏值来作为主键）。主键索引所构建的B+树，表中所有的记录都存放在了树的最后一层。且与一般的B+树不同的是：叶子节点间的指针是双向的

复合索引

创建复合索引时，会将作为复合索引字段的值进行排序并放在B+树的最后一层中，同时还会将其对应的主键值放在其后。如：

a（主键）	b	c	d	e
2	1	1	1	a

其中字段a为主键，字段bcd共同作为复合索引，此时存放在最后一层的数据就是：111（复合索引） 2（主键索引）

根据这个特点，可以看出复合索引具有以下使用方法

最佳左前缀：使用复合索引的顺序必须和创建的顺序一致
覆盖索引的同时，可以带上主键字段，如
```
SELECT a, b, c, d FROM t_emp;
```
因为主键字段和复合索引一起存放在了复合索引说产生的B+树的最后一层。如果需要a字段，无需进行全表扫描
如果进行范围查找，可能会进行全表扫描，这取决于处在范围内记录的多少
- 记录多，从复合索引映射到主键索引的次数过多，成本过高，会直接进行全表扫描
```
EXPLAIN SELECT * FROM t_emp WHERE age > 1;
```
- 记录少，先使用复合索引，然后映射到全表中的对应记录上
```
EXPLAIN SELECT * FROM t_emp WHERE age > 80;
```
- 但是使用覆盖索引，无论记录多少，都会用到索引
```
EXPLAIN SELECT age, name FROM t_emp WHERE age > 1;
```
- 不带WHERE也可以通过复合索引查找到主键+复合索引的记录
```
EXPLAIN SELECT id, age, name, deptId FROM t_emp ;
```

索引的使用场景

适合索引的场景

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

不适合索引的场景

表记录太少（有无索引差别不大）
经常增删改的表或者字段
Where 条件里用不到的字段不创建索引
过滤性不好的不适合建索引（重复性较高，比如国籍、性别之类的字段）

九、Explain 性能分析

概念

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈

用法

--EXPLAIN + SQL语句，如：
EXPLAIN SELECT * FROM person;

Explain 执行后返回的信息：

表头字段介绍

准备工作

CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

id：表的读取顺序

id是select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同：执行顺序为 从上至下执行
```
EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;
```
查询时，表的加载顺序为t1, t2, t3

id不同：执行顺序为 id大的先执行

EXPLAIN SELECT t2.id FROM t2 WHERE t2.id = 
(SELECT t1.id FROM t1 WHERE t1.id = 
(SELECT t3.id FROM t3)
);

查询时，表的加载顺序为t3, t1, t2

id相同又不同：执行顺序为
- id不同时，值较大的先执行
- id相同时，从上至下执行
```
EXPLAIN SELECT t2.* FROM (
    SELECT t3.id
    FROM t3
    WHERE t3.content = '') s1, t2
WHERE s1.id = t2.id;
```
查询时，表的加载顺序为t3, t2, 虚表dervied2
- 其中dervied2 的 2，为 id = 2

select_type：查询操作类型

select_type代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

select_type 属性	含义
SIMPLE	简单的 select 查询,查询中不包含子查询或者 UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary
DERIVED	在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DEPEDENT SUBQUERY	在SELECT或WHERE列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUERY	无法使用缓存的子查询
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

SUBQUERY 和 DEPEDENT SUBQUERY
都是 WHERE 后面的条件，SUBQUERY 是单个值（=），DEPEDENT SUBQUERY 是一组值（IN）
UNCACHEABLE SUBQUERY
当使用了**@@来引用系统变量**的时候，不会使用缓存

UNION 和 UNION RESULT

EXPLAIN SELECT * FROM t_emp a LEFT JOIN  t_dept b ON a.deptId = b.id WHERE b.id IS NULL
UNION
SELECT * FROM t_emp a RIGHT JOIN  t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;

table：表的来源

table表示这个数据是基于哪张表的

type：访问类型

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

--常见的顺序为
system > const > eq_ref > ref > range > index > all

一般来说，得保证查询至少达到 range 级别，最好能达到 ref

类型名	含义
SYSTEM	表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计
CONST	表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量
EQ_REF	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
REF	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
RANGE	只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引
INDEX	出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组
ALL	Full Table Scan，将遍历全表以找到匹配的行

REF

--其中deptId为索引，且用到了' = '
EXPLAIN SELECT * FROM t_emp WHERE deptId = 3;

RANGE

--其中deptId为索引，用到了 BETWEEN...AND... , IN , > , < 等范围查询
EXPLAIN SELECT * FROM t_emp WHERE deptId > 3;

INDEX

--其中deptId为索引，查找了整张表时，用到了索引
EXPLAIN SELECT deptId FROM t_emp;

ALL

--其中name为非索引
EXPLAIN SELECT name FROM t_emp;

possible_key：可能用到的索引

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key：实际使用的索引

实际使用的索引。如果为NULL，则没有使用索引

EXPLAIN SELECT * FROM t_emp WHERE id = 1 AND deptId = 1;

MySQL推测可能用到主键索引和idx_dept_id索引，实际上用到的是主键索引

覆盖索引

当查找的字段与建立的索引的匹配（查询的字段都是索引，但不需要全是索引）时，会发生覆盖索引。MySQL推测使用的索引为NULL，而实际上会使用索引

有以下两种解释

select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖
索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引

注意：要使用覆盖索引，则只取出需要的列（被令为索引），不要使用 SELECT *

--其中id和deptId都为索引
EXPLAIN SELECT id, deptId FROM t_emp;

key_len：索引使用字节数

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引

ken_len 越长，说明索引使用的越充分

ref：显示被使用的索引的具体信息

ref显示索引的哪一列被使用了，如果可能的话，可以是一个常数。哪些列或常量被用于查找索引列上的值

EXPLAIN SELECT * FROM t_dept, t_emp WHERE t_emp.deptId = t_dept.id;

ref中如果有const就代表在where条件中加上了t_emp.xx = 'xxx'匹配了常量

rows：被查询的行数

rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

验证

--先删除索引
DROP INDEX idx_dept_id ON t_emp;

--查找
EXPLAIN SELECT * FROM t_dept, t_emp WHERE t_emp.deptId = t_dept.id;

--再创建索引
CREATE INDEX idx_dept_id ON t_emp(deptId);

--查找
EXPLAIN SELECT * FROM t_dept, t_emp WHERE t_emp.deptId = t_dept.id;

结果如下

未使用索引时，一共需要查询26行
使用索引后，一共需要查询6行

Extra：额外重要信息

其他的额外重要的信息

Using filesort

使用外部索引排序（未使用用户创建的索引）
说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”
出现 Using filesort 说明SQL语句设计的不好，没有按照创建的索引进行排序，或者未按照索引指定的顺序进行排序

演示

--创建符合索引
CREATE INDEX idx_emp_empno_age ON t_emp(empno, age);

--进行查询操作，通过 age 字段进行排序（未按照复合索引顺序进行排序查询）
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 ORDER BY age;

--进行查询操作，通过 empno 或者 empno + age 字段进行排序（按照复合索引顺序进行排序查询）
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 ORDER BY empno;
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 ORDER BY empno, age;

结果

Using temporary

使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
出现 Using temporary 说明SQL语句设计的非常不好，可能是因为没有按照顺序使用复合索引

演示

--进行查询操作， 通过 age 字段进行分组（未按照复合索引顺序进行排序查询）
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 GROUP BY age;

----进行查询操作，通过 empno 或者 empno + age 字段进行分组（按照复合索引顺序进行排序查询）
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 GROUP BY empno;
EXPLAIN SELECT empno FROM t_emp WHERE empno >100002 GROUP BY empno, age;

结果

重要结论

如果创建了复合索引，一定要按照复合索引的顺序来使用，否则会使得性能大幅下降。

filesort只能应用在单个表上，如果有多个表的数据需要排序，那么MySQL会先使用using temporary保存临时数据，然后再在临时表上使用filesort进行排序，最后输出结果。

Using index

Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，详见[key：实际用到的索引——覆盖索引](# 覆盖索引)，避免访问了表的数据行，效率不错！
如果同时出现 using where，表明索引被用来执行索引键值的查找
如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。

演示

--查询 age 字段，使用了WHERE
EXPLAIN SELECT age FROM t_emp WHERE age >100000;

--查询 empno 和 age 字段，未使用WHERE
EXPLAIN SELECT empno, age FROM t_emp;

--查询 empno 和 name 字段 （name字段不是索引）
EXPLAIN SELECT empno, name FROM t_emp;

结果

Using where

表明使用了 where 过滤

Using join buffer

使用了连接缓存

impossible where

where 子句的值总是 false，不能用来获取任何元组

select tables optimized away

在没有 GROUP BY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

十、单表查询优化

全值匹配我最爱

SQL语句

--建立符合索引（age, deptId, name）
CREATE INDEX idx_emp_ade ON t_emp(age, deptId, NAME);

--查找
EXPLAIN SELECT empno FROM t_emp WHERE age = 90;
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1;
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND name = '风清扬';

--和上一条SQL语句中WHERE后字段的顺序不同，但是不影响查询结果
EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1 AND name = '风清扬' AND age = 90;

对应结果

可以看到，复合索引都被用到了，并且SQL中查询字段的顺序，跟使用索引中字段的顺序，没有关系。优化器会在不影响 SQL 执行结果的前提下，自动地优化

结论：全职匹配我最爱指的是，查询的字段按照顺序在索引中都可以匹配到

最佳左前缀法则

SQL语句

--先删除之前创建的单值索引
DROP INDEX idx_dept_id ON t_emp; 

--查询，未按照最佳左前缀法则
EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1;
EXPLAIN SELECT empno FROM t_emp WHERE deptId = 1 AND name = '风清扬';

--查询，部分按照最佳左前缀法则（age字段和复合索引匹配，但name没有）
EXPLAIN SELECT empno FROM t_emp WHERE  age = 90 AND name = '风清扬';

--查询，完全按照最佳左前缀法则
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1;
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND name = '风清扬';

对应结果

可以看到，查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效

原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列，俗称中间兄弟不能断

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用

索引列上不计算

不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），可能会导致索引失效而转向全表扫描

SQL语句

--直接查询
EXPLAIN SELECT empno FROM t_emp WHERE age = 90 AND deptId = 1 AND NAME = '风清扬';

--使用MySQL函数查询
EXPLAIN SELECT empno FROM t_emp WHERE LEFT(age,2) = 90 AND deptId = 1 AND name = '风清扬';

对应结果

可以看出，当age字段使用了left函数以后，导致索引完全失效

结论：等号左边无计算

范围之后全失效

SQL语句

--范围查询
EXPLAIN SELECT empno FROM t_emp WHERE age > 50 AND deptId = 1 AND name = '风清扬';
EXPLAIN SELECT empno FROM t_emp WHERE age = 50 AND deptId > 1 AND NAME = '风清扬';

--未使用范围查询
EXPLAIN SELECT empno FROM t_emp WHERE age = 50 AND deptId = 1 AND name = '风清扬';

对应结果

可以看出，当对age字段使用范围查询后，使得范围后面的索引失效了

建议：将可能做范围查询的字段的索引顺序放在最后

结论：使用范围查询后，如果范围内的记录过多，会导致索引失效，因为从自定义索引映射到主键索引需要耗费太多的时间，反而不如全表扫描来得快

覆盖索引多使用

SQL语句

--查询所有字段
EXPLAIN SELECT * FROM t_dept WHERE id = 1;

--查询索引字段
EXPLAIN SELECT id FROM t_dept WHERE id = 1;

对应结果

结论：使用覆盖索引（Using index）会提高检索效率

使用不等会失效

在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描

SQL语句

--SQL语句中有不等于
EXPLAIN SELECT * FROM t_emp WHERE age != 90;
EXPLAIN SELECT * FROM t_emp WHERE age <> 90;

--SQL语句中没有不等于
EXPLAIN SELECT * FROM t_emp WHERE age = 90;

对应结果

结论：尽量不要使用不等于

使用NULL值要小心

在使用

IS NULL
或者
IS NOT NULL

时，可能会导致索引失效

但是如果允许字段为空，则

IS NULL 不会导致索引失效
IS NOT NULL 会导致索引失效

SQL语句

EXPLAIN SELECT * FROM t_emp WHERE age IS NULL;

EXPLAIN SELECT * FROM t_emp WHERE age IS NOT NULL;

对应结果

模糊查询加右边

要使用模糊查询时，百分号最好加在右边，而且进行模糊查询的字段必须是单值索引

SQL语句

--创建单值索引
CREATE INDEX idx_emp_name ON t_emp(NAME);

--进行模糊查询
EXPLAIN SELECT * FROM t_emp WHERE name LIKE '%风';
EXPLAIN SELECT * FROM t_emp WHERE name LIKE '风%';
EXPLAIN SELECT * FROM t_emp WHERE name LIKE '%风%';

对应结果

可以看出，对索引使用模糊查询时，只有当百分号在右边，索引为单值索引且模糊查询语句在最右边时，索引才会生效

其他情况均失效了

但是有时必须使用其他类型的模糊查询，这时就需要用覆盖索引来解决索引失效的问题

SQL语句

EXPLAIN SELECT name FROM t_emp WHERE name LIKE '%风';
EXPLAIN SELECT name FROM t_emp WHERE name LIKE '风%';

EXPLAIN SELECT NAME FROM t_emp WHERE name LIKE '%风%';

对应结果

结论：对索引进行模糊查询时，最好在右边加百分号。必须在左边或左右加百分号时，需要用到覆盖索引来提升查询效率

字符串加单引号

当字段为字符串时，查询时必须带上单引号。否则会发生自动的类型转换，从而发生全表扫描

用于查询的表

其中card_id字段为varchar类型，且设置了单值索引

SQL语句

--使用了单引号
EXPLAIN SELECT card_id FROM person WHERE card_id = '1';

--未使用单引号，发生自动类型转换
EXPLAIN SELECT card_id FROM person WHERE card_id = 1;

对应结果

尽量不用or查询

如果使用or，可能导致索引失效。所以要减少or的使用，可以使用 union all 或者 union 来替代：

SQL语句

--使用or进行查询
EXPLAIN SELECT * FROM t_emp WHERE age = 90 OR NAME = '风清扬';

对应结果

口诀

全职匹配我最爱，最左前缀要遵守

带头大哥不能死，中间兄弟不能断

索引列上少计算，范围之后全失效

LIKE 百分写最右，覆盖索引不写

不等空值还有 OR，索引影响要注意

VARCHAR 引号不可丢，SQL 优化有诀窍

十一、关联查询优化

建表语句

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`bookid`)
);
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

LEFT JOIN优化

SQL语句

--未建立索引时的左外连接查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

--左表（class）建立索引
CREATE INDEX idx_class_card ON class(card);

--再次执行查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

--去掉左表索引
DROP INDEX idx_class_card ON class;

--右表建立索引
CREATE INDEX idx_book_card ON book(card);

--再次执行查询
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

对应结果

结论

在优化关联查询时，只有在被驱动表上建立索引才有效
left join 时，左侧的为驱动表，右侧为被驱动表

INNER JOIN优化

SQL语句

--查询操作，目前索引在book表的card上，class表和book表的位置不会改变查询结果
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;
EXPLAIN SELECT * FROM book INNER JOIN class ON book.card = class.card;

--删除book表中的几条记录
DELETE FROM book WHERE bookid<10;

--再次查询
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

--删除book表card字段索引，给class表的card字段添加索引
DROP INDEX idx_book_card ON book;
CREATE INDEX idx_class_card ON class(card);

--再次查询
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

对应结果

结论：inner join 时，mysql 会把小结果集的表选为驱动表（小表驱动大表）

所以最好把索引建立在大表（数据较多的表）上

RIGHT JOIN优化

优化类型和LEFT JOIN类似，只不过被驱动表变成了左表

IN与EXISTS

十二、排序分组优化

在查询中难免会对查询结果进行排序操作。进行排序操作时要避免出现 Using filesort，应使用索引给排序带来的方便

索引信息

ORDER BY 优化

以下查询都是在索引覆盖的条件下进行的

SQL语句

--不满足索引覆盖时进行排序查询
EXPLAIN SELECT empno FROM t_emp  WHERE age > 50 ORDER BY age, deptId;

--按照复合索引顺序进行排序
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age;
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age, deptId;
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age, deptId, name;

--不按照复合索引顺序进行排序（无 age 字段），发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY deptId, name;

--不按照复合索引顺序进行排序（索引顺序打乱），发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY deptId, name, age;

--排序时部分(age)升序，部分(deptId)降序，发生Using filesort
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age ASC, deptId DESC;

--排序时都为降序
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age > 50 ORDER BY age DESC, deptId DESC;

--排序时，在前面的字段为常量时（非范围）
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age = 50 ORDER BY deptId, name;
EXPLAIN SELECT age, deptId FROM t_emp  WHERE age = 50 AND deptId>10000 ORDER BY deptId, name;

对应结果

结论：

要想在排序时使用索引，避免 Using filesort，首先需要发生索引覆盖，其次

ORDER BY 后面字段的顺序要和复合索引的顺序完全一致
ORDER BY 后面的索引必须按照顺序出现，排在后面的可以不出现
要进行升序或者降序时，字段的排序顺序必须一致。不能一部分升序，一部分降序，可以都升序或者都降序
如果复合索引前面的字段作为常量出现在过滤条件中，排序字段可以为紧跟其后的字段

MySQL的排序算法

当发生 Using filesort 时，MySQL会根据自己的算法对查询结果进行排序

双路排序
- MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
- 从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段
- 简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序
单路排序
- 从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了
- 存在的问题：在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，因为方法 B 是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取取 sort_buffer 容量大小，再排……从而多次 I/O。也就是本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失
优化Using filesort
- 增大 sort_butter_size 参数的设置
  - 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M 之间调整
- 增大 max_length_for_sort_data 参数的设置
  - mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data
  - 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。（1024-8192 之间调整）
- 减少 select 后面的查询的字段
  - 查询的字段减少了，缓冲里就能容纳更多的内容了，间接增大了sort_buffer_size

总结

GROUP BY 优化

优化方式和 ORDER BY 类似，参考ORDER BY 的优化方式即可

十三、截取查询分析

慢日志查询

概念

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中
具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上的语句
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析

使用

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

SQL 语句	描述	备注
SHOW VARIABLES LIKE ‘%slow_query_log%’	查看慢查询日志是否开启	默认情况下 slow_query_log 的值为 OFF
set global slow_query_log=1	开启慢查询日志
SHOW VARIABLES LIKE ‘long_query_time%’	查看慢查询设定阈值	单位：秒
set long_query_time=1	设定慢查询阈值	单位：秒

运行查询时间长的 sql，可以打开慢查询日志查看

批量数据脚本

建表语句

--dept 部门表
CREATE TABLE `dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, ceo INT NULL , PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

--emp 员工表
CREATE TABLE `emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT, `empno` INT NOT NULL , `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

设置参数

在执行创建函数之前，首先请保证 log_bin_trust_function_creators 参数为 1，即 on 开启状态。否则会报错

--查询
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

--设置
SET GLOBAL log_bin_trust_function_creators=1;

编写随机函数

随机产生字符串

--DELIMITER 是用于改变结束的标志的，一般以分号结尾，但这里改为了以 $$ 结尾
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$

如果要删除函数，则执行：

DROP FUNCTION rand_string;

随机产生部门编号

DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$

如果要删除函数，则执行：

drop function rand_num;

创建存储过程

创建往 emp 表中插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE insert_emp( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把 autocommit 设置成 0
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) , rand_num(30,50),rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$

--删除
-- DELIMITER ;
-- drop PROCEDURE insert_emp;

创建往 dept 表中插入数据的存储过程

--执行存储过程，往 dept 表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$

--删除
-- DELIMITER ;
-- drop PROCEDURE insert_dept;

调用存储过程

添加数据到部门表

--执行存储过程，往 dept 表添加 1 万条数据
DELIMITER ;
CALL insert_dept(10000);

添加数据到员工表

--执行存储过程，往 emp 表添加 50 万条数据
DELIMITER ;
CALL insert_emp(100000,500000);

批量删除某个表上的所有索引

删除索引的存储过程

DELIMITER $$
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE
table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index ",_index," on ",tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END$$

执行存储过程

CALL proc_drop_index("dbname","tablename");

十四、Profiles分析

查看是否开启

SHOW VARIABLES LIKE 'profiling';

开启

SET profiling = ON ;

查看结果

SHOW PROFILES ;

查看具体语句的分析

SHOW PROFILE cpu,block io FOR QUERY id;

出现以下结果危险

十五、MySQL锁机制

表锁

[MylSAM](# 引擎层 )引擎使用表锁，并且不支持事务

SQL语句

--展示表是否加锁
SHOW OPEN TABLES;

--加锁 read (读锁) write (写锁)
LOCK TABLE table1 read(write), table2 read(write)...

--全部解锁
UNLOCK TABLES;

读锁

主机A给表加上表锁（读锁）以后
- 主机A和其他主机都可以读取该表的信息
- 主机A不能读取库中其他表的信息，但其他主机可以读取库中所有表的信息
- 如果要修改被锁表的信息
  - 主机A如果对表进行修改，会修改失败
  - 其他主机对表进行修改，会被阻塞，直到锁被释放

演示

给dept表加锁并查询状态

LOCK TABLE dept READ;

SHOW OPEN TABLES;

读取

两个客户端分别读取dept表的信息，都能读出来
```
SELECT id FROM dept WHERE id = 1;
```
客户端A（加锁端）A读取其他表信息，读取失败
```
SELECT * FROM t_emp;
```
其他客户端读取度其他表信息，读取成功
```
SELECT * FROM t_emp;
```

修改

客户端A对表中内容进行修改，修改失败
```
DELETE FROM dept WHERE id = 1;
```
客户端B对表中内容进行修改，进入阻塞状态
```
DELETE FROM dept WHERE id = 1;
```

从客户端A解锁后，客户端B修改成功
```
UNLOCK TABLES;
```

写锁

主机A给表加上表锁（写锁）以后

主机A可以读取该表信息，但其他主机读取时，会进入阻塞状态，直到锁被释放
主机A不能读取库中其他表的信息，但其他主机可以读取库中除该表以外所有表的信息
如果要修改被锁表的信息
- 主机A如果对表进行修改，修改成功
- 其他主机对表进行修改，会被阻塞，直到锁被释放

演示

给dept表加上写锁并查看

LOCK TABLE dept WRITE;

SHOW OPEN TABLES;

读取

客户端A查询该表内容，查询成功；读取其他表，读取失败
```
SELECT * FROM dept;

SELECT * FROM t_emp;
```

其他表读取该表信息，进入阻塞状态
```
SELECT * FROM dept;
```
释放后，读取成功
```
UNLOCK TABLES;
```

修改

客户端A修改该表内容，修改成功
```
DELETE dept WHERE id = 2;
```
客户端A修改其他表内容，修改失败
```
DELETE FROM t_emp WHERE id = 2;
```
其他客户端修改该表内容，进入阻塞状态
```
DELETE FROM t_emp WHERE id = 2;
```

总结

读锁不会阻塞读，只会阻塞写。但是写锁会阻塞读和写。

行锁

InnoDB使用行锁，并且支持事务，事务相关可参考 MySQL基础事务

特点

如果两个客户端对同一条记录进行修改

客户端A修改后，未提交（未commit），此时客户端B修改，则会阻塞
客户端A修改后，提交后，客户端B再修改，则不会阻塞

如果两个客户端分别对不同的记录进行修改，则不会被阻塞

修改同一条记录

--关闭自动提交
SET autocommit = 0;

--客户端A、B查询id=2的记录
SELECT * FROM t_emp WHERE id = 2;

--客户端A进行修改操作（将年龄改为了80），但未提交
UPDATE t_emp SET age = 80 WHERE id = 2;

--客户端A进行查询
SELECT * FROM t_emp WHERE id = 2;

--客户端B进行查询
SELECT * FROM t_emp WHERE id = 2;

--客户端B进行修改（客户端A未提交）
UPDATE t_emp SET age = 90 WHERE id = 2;

--客户端A提交
COMMIT;

--客户端B提交
COMMIT;

对应结果

客户端A查询结果

客户端B查询结果

客户端A修改后A查询

客户端A修改后B查询

客户端A修改，未提交，此时B进行修改，被阻塞

客户端A提交后，B修改成功

修改不同记录

--客户端A对id=2的年龄进行修改
UPDATE t_emp SET age = 90 WHERE id = 2;

--客户端B对id=3的年龄进行修改
UPDATE t_emp SET age = 30 WHERE id = 3;

--客户端A，B分别提交
COMMIT;
COMMIT;

因为InnoDB使用行锁，对于不同行的操作，不会出现阻塞现象

索引失效

索引失效，行锁变表锁

当索引失效后，即使多个客户端操作的不是同一条记录，如果未提交，其他客户端也会进入阻塞状态

所以要避免索引失效

间隙锁危害

概念

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁

对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)” ，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

危害

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

演示

--查询表记录，此处没有id=2的记录
SELECT * FROM t_emp;

--客户端A进行范围查询，但是范围内没有id=2的记录
UPDATE t_emp SET deptId = 1 WHERE id>1 AND id < 6;

--客户端B进行插入数据，插入一条id=2的记录
INSERT t_emp VALUES(2, '岳不群', 11, 2, 100002); 

--客户端A提交
COMMIT;

--客户端B提交
COMMIT;

客户端B进入阻塞状态

提交后，插入成功

结论：可以看到表中本来没有id=2的记录，但是在客户端A进行范围修改时，客户端B对在范围内但不存在的数据进行插入时，客户端B进入了阻塞状态

查看行锁的争夺情况

SHOW STATUS LIKE 'innodb_row_lock%';

1表示正在等待锁定的数量
2表示等待总时长
3表示等待平均时长
5表示系统启动后到现在总共等待的次数

锁住指定的一行

BEGIN;

--锁住指定的一行，如果进行更新操作就是 ... FOR UPDATE，删除操作就是 ... FOR DELETE 以此类推
SELECT * FROM t_emp WHERE id = 1 FOR UPDATE;

--进行修改操作
UPDATE t_emp SET NAME = '风车车' WHERE id = 1;

--提交
COMMIT;

如果当某一行被锁住后，其他客户端对改行进行操作，会被阻塞

总结

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MylSAM高，甚至可能会更差。

十六、复制

主从复制

12.mysql中主从复制架构

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

在Linux下实施

# 0.架构规划
	192.168.202.201    master  主节点
	192.168.202.202    slave   从节点

# 1.修改mysql的配置文件
  vim /etc/my.cnf

# 2.分别在配置文件中加入如下配置
	mysql(master):
		server-id=1
		log-bin=mysql-bin
		log-slave-updates
		slave-skip-errors=all
	
	msyql(slave):
		server-id=2
		log-bin=mysql-bin
		log-slave-updates
		slave-skip-errors=all
		
注意:两个机器的server-id不能一致

# 3.重启mysql服务
	systemctl restart mysqld

# 4.登录mysql执行如下命令检测配置是否生效
	SHOW VARIABLES like 'server_id';

# 5.登录master节点执行如下命令
		show master status;
		从图中可以发现从120行后同步

# 6.登录slave节点执行如下命令:
		change master to 
		master_host='192.168.202.201',
		master_user='root',
		master_password='root',
		master_log_file='mysql-bin.000001',
		master_log_pos=120;

# 7.开启slave节点
		start slave; 
		stop  slave;

# 8.查看从节点状态
		show slave status\G;

		************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.15.0.9
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
   	
    注意:
    		1.出现 Slave_IO_Running: Yes`和 Slave_SQL_Running: Yes 说明成功
    		2.如果在搭建过程出现错误,可以查看查看错误日志文件 cat /var/log/mysqld.log
# 9.通过客户端工具进行测试
	自行选择客户端，在master节点新建，可以发现slave节点也会同步
# 10.关闭主从复制(在从节点执行)
	stop slave;

注意:如果出现Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593错误
关闭systemctl stop mysqld后执行如下命令rm -rf /var/lib/mysql/auto.cnf删除这个文件,之所以出现会出现这样的问题，是因为我的从库主机是克隆的主库所在的主机，所以auto.cnf文件中保存的UUID会出现重复.

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作

13.mysql中读写分离架构

读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，极大程度缓解了锁的争用
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销
增加冗余，提高可用性

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器

数据库

SQL

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！

Redis 上一篇

Java开发网络相关知识下一篇