一，MYSQL入门
1.数据库相关概念
DB：数据库：存储数据的仓库，保存了一系列有组织的数据。
DBMS：数据库管理系统：数据库是通过DBMS创建和操作的容器。
SQL：结构化查询语言：专门用来与数据库通信的语言。
2.数据库的好处
1.可以持久化数据到本地
2.可以实现结构化查询，方便管理
3.数据库存储数据特点
1.将数据放到表中，表放到库中。
2.一个数据库有多张表，每个表都有一个名字，用来标识自己。
表名具有唯一性。
3.表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中类的设计。
4.表有列组成，我们也称为字段。所有表都是由一个列或多个列组成的，
每一列类似Java中的属性。
5.表中的数据按照行来存储，每一行类似于Java中的对象。
4.mysql的安装与使用
参照mysql安装文档
5.Mysql常用命令
显示数据库----->show Databases;
使用数据库----->use 数据库名；
显示表---->show tables;
显式指定数据库的表---->show tables from 数据库名；
查看位于那个数据库---->select database();
显示表结构--->desc 表名；
查看数据库版本：--->select version();
查看数据库版本2:----->Dos:mysql --version;
查看数据库信息----->show CREATE DATABASE mydb1;
查看服务器中的数据库，并把mydb1的字符集修改为utf-8----->ALTER DATABASE mydb1character set utf8;
删除数据库----->drop database mydb1;
表中增加一栏信息----->alter table student add image blob;
删除表----->drop table student;
修改地址----->alter table student modify address varchar(100);
删除一个属性-----> alter table student drop image;
修改表名----->rename table student to students;
查看表的创建细节----->show create table students;
修改表的字符集为 gbk----->alter table students character set gbk;
列名name修改为studentname----->alter table students change name studentname varchar(100);
6.mysql语法规范
1.不区分大小写，建议关键字大写，表名列名小写。
2.每条命令最好用分号结尾。
3.每条语句可以缩进，换行。
4.注释
单行注释：#注释文字
-- 注释文字
多行注释：/* */
二，DQL查询语言
1.基础查询
**语法： select 查询列表 from 表名**
**查询列表：表中的字段，常量，表达式，函数**
**查询的结果是张虚拟的表格**
1.查询表中的单个字段
select last_name from employee;
2.查询表中的多个字段
select last_name,salary,email from employee;
3.查询表中的所有字段
select * from employee;
4.查询常量值
select 100;
select 'john';
5.查询表达式
select 100*98;
6.查询函数
select version();
7.起别名
select last_name as name from employee;
select last_name name from employee;
8.去重
查询员工表中涉及到的所有的部门编号
select distinct department_id from employee;
9.+的作用
#运算符：两个操作数都为数值型，则做加法运算；
#其中一方为字符型，试图将字符型数值转换成数值型，
#如果转换成功，继续做加法运算；否则，将字符型数值
#转换为0；
10.使用concat实现连接
#案例：查询员工名和性连接成一个字段
SELECT CONCAT(username,PASSWORD) FROM USER;
#任何数与null做运算结果都为null
2.条件查询
语法：
select 查询列表 from 表名 where 筛选条件
分类：
①按照条件表达式筛选
条件运算符：>,<,=,!=,>=,<=
查询员工工资>1w2的员工信息
select * from employee where salary >12000;
查询部门编号！=90号的员工名和部门编号
select name, dep_id from employee where dep_id 1=90；
②按照逻辑表达式筛选
逻辑运算符：&&,||,!,AND,OR,NOT
查询工资在一万到两万之见的员工名，工资以及奖金。
select name,salary ,jiangjin where salary between 10000 and 20000;
查询部门编号不在90-110之间，或者工资高于15000的员工信息。
select * from employee where department<90||department>110 ||salary >15000;
③模糊查询
like：一般和通配符搭配使用
通配符：
%任意多个字符，包含0个字符
_任意单个字符
BETWEEN AND:包含临界值
IN:判断某个字段的值是否属于in列表中的某一项
IS NULL,IS NOT NULL:=或者！=不能用来判断null
安全等于<=>可以判断null
查询员工名中包含a的员工信息
select * from emp where name like %a%;
查询员工名中第三个字符为e第五个字符为a的员工名和工资
select name ,salary from emp where name like %__e_a%;
员工名中第二个字符为_的员工名
select name from emp where name like %_\_%;
查询员工编号在100到120之间的所有员工信息
select * from emp where id between 100 and 120;
查询员工的工种编号是IT_PRIG,AD_PRES,AD_VP中的一个员
工名和工种编号；
select name , id from emp where id in(IT_PRIG,AD_PRES,AD_VP);
查询没有奖金的员工名和奖金率
select salary , jjl from emp where salary is Null;
查询有奖金的员工名和奖金率
select salary ,jjl from emp where salary is not null;
④IF null的使用：
查询员工号为176的员工的姓名和部门号和年薪
SELECT last_name ,department_id , salary*(1+IFNULL(commission_pct,0))*12 '年薪'
FROM employees WHERE employee_id =176;
3.排序查询
语法：
select 查询列表
from 表
where 筛选条件
order by 排序列表 asc 或desc （升序或者降序，默认为升序）
查询员工信息，要求工资从高到低排序
select * from emp order by salary desc;
查询部门编号大于等于90的员工信息，按照入职时间先后排序
select * from emp where dep_id >=90 order by createtime asc;
按照员工年薪的高低显示员工的信息和年薪
select * ,年薪 from emp order by salary*(1+if null(jjl,0))*12 as 年薪 desc;
按姓名长度显示员工的姓名和工资
select name ,salary from emp order by length(name) asc;
查询员工信息，先按照工资排序，再按照员工编号排序
select * from emp order by salary asc,id asc;
4.常见函数
功能：类似Java中的方法
分类：单行函数
分组函数
1.单行函数
1.字符函数
1.length 获取参数值的字节个数
select * from emp order by length(name);
2.concat 拼接字符串
select concat(last_name,first_name) as 姓名 from emp;
3.upper，lower 大小写转换函数
案例：将姓变大写，名字变小写，然后拼接
SELECT
CONCAT(UPPER(last_name),LOWER(first_name))
FROM employees;
4.substr,SUBSTRING 截取字符串
SELECT SUBSTR('李莫愁',2);
SELECT SUBSTR('李莫愁',2,3);
案例：姓名中首字符大写，其他的小写然后用_拼接显示出来
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
'_',
LOWER(SUBSTR(last_name, 2))
) output
FROM
employees ;
5.instr:返回字串第一次出现的索引，如果找不到返回0
SELECT INSTR('风急天高猿啸哀','天') AS out_put;
6.trim :去掉前后空格或前后指定字符
SELECT LENGTH(TRIM(' 张三丰 ')) AS out_put;
SELECT TRIM('a' FROM 'aaaa1aa2aaa3aaa') AS out_put;
7.lpad :用指定字符填满指定长度（左填充）
SELECT LPAD('苍老师',10,'*');
8.rpad:用指定字符填满指定长度（右填充）
SELECT RPAD('苍老师',10,'*');
9.replace 替换
SELECT REPLACE('千锋培训机构','千锋','尚硅谷');
2.数学函数
1.round:四舍五入
SELECT ROUND(1.666);
SELECT ROUND(1.567,2);
2.ceil 向上取整
SELECT CEIL(1.52);
3.floor 向下取整
SELECT FLOOR(1.52);
4.truncate:截断（小数点后保留几位）
SELECT TRUNCATE(1.65,2);
5.mod:取余
SELECT MOD(10,3);
3.日期函数
1.now:返回当前系统日期时间
SELECT NOW();
2.curdate:返回当前系统日期
SELECT CURDATE();
3.curtime:返回当前时间
SELECT CURTIME();
4.获取指定部分的年月日时分秒
SELECT YEAR(NOW());
SELECT YEAR(hiredate) FROM employees;
5.str_to_date将字符通过指定的格式转化成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
案例：查询入职时间为1992-4-3的员工信息
SELECT * FROM employees
WHERE hiredate=STR_TO_DATE('2016-3-3','%Y-%c-%d');
6.date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS 日期;
案例：查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT
last_name,
DATE_FORMAT(hiredate, '%c月/%d日 %y')
FROM
employees
WHERE commission_pct IS NOT NULL ;
4.其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
5.流程控制函数
1.if:IF else效果
SELECT IF(10>5,'true','false');
案例：查询如果有奖金就备注有，没有就备注没有。
SELECT
last_name,
commission_pct,
IF(
commission_pct IS NULL,
'没奖金',
'有奖金'
) AS 备注
FROM
employees ;
2.case函数
1)switch-CASE
语法:
CASE 要判断的字段或者表达式
WHEN 常量1 THEN 要显示的值1或者语句1
WHEN 常量2 THEN 要显示的值2或者语句2
...
ELSE 要显示的值n或者语句n；
案例：查询员工的工资，要求
部门号==30，显示的工资为1.1倍，
部门号==40，显示的工资为1.2倍，
部门号==50，显示的工资为1.3倍，
其他部门，显示原有工资。
SELECT
salary AS 原始工资,
department_id ,
CASE
department_id
WHEN 30
THEN salary * 1.1
WHEN 40
THEN salary * 1.2
WHEN 50
THEN salary * 1.3
ELSE salary
END AS 新工资
FROM
employees ;
2)CASE 使用2：
语法：
CASE
WHEN 条件1 THEN 要显示的值1或语句1
WHEN 条件2 THEN 要显示的值2或语句2
...
ELSE 要显示的值n或语句n
END
案例：查询员工的工资情况
如果>2w，显示A
如果>1.5w，显示B
如果>1w，显示C
否则，显示D
SELECT
salary,
CASE
WHEN salary > 20000
THEN 'A'
WHEN salary > 15000
THEN 'B'
WHEN salary > 10000
THEN 'C'
ELSE 'D'
END AS 工资等级 FROM employees ;
2.分组函数
功能：用作统计使用
1.sum :求和
SELECT SUM(salary) FROM employees;
2.avg：平均值
SELECT AVG(salary) FROM employees;
3.max：最大值
SELECT MAX(salary) FROM employees;
4.min：最小值
SELECT MIN(salary) FROM employees;
5.count：计算个数
SELECT COUNT(salary) FROM employees;
总结
①.sum,avg一般用于处理数值类型
②.max，min，count用来处理任何类型
③.以上分组函数都忽略null值
④.可以和distinct搭配
SELECT SUM(DISTINCT salary) 纯净,SUM(salary) FROM employees;
6.count的详细介绍
①select COUNT(*) FROM employees;
②select COUNT(1) FROM employees;
③和分组函数一同查询的字段要求是group by后的字段。
5.分组查询
GROUP BY 和分组函数对应
分组查询中分组条件分为两类
| 数据源 | 位置 | 关键字 | |
|---|---|---|---|
| 分组前筛选 | 原始表 | GROUP BY 子句的前面 | WHERE |
| 分组后筛选 | 分组后的结果集 | GROUP BY 子句的后面 | HAVING |
分组函数做条件肯定是放在having子句中。
group BY 子句支持单个字段分组，多个字段分组
（多个字段之间用逗号隔开没有顺序要求），表达式或函数。
也可以添加排序，放在整个分组查询的最后。
案例：查询每个工种的最高工资
SELECT
MAX(salary),
job_id
FROM
employees
GROUP BY job_id
ORDER BY MAX(salary) ASC ;
案例：查询邮箱中包含a字符的，每个部门的平均工资
SELECT
AVG(salary),
department_id
FROM
employees
WHERE email LIKE '%a%'
GROUP BY department_id ;
#select Avg(salary),dep_id from employee where email like %a% group by dep_id ;
案例：查询有奖金的每个领导手下员工的最高工资
SELECT
MAX(salary),
manager_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id ;
#select max(salary) ,manage_id from employees where commission_pct is not null group by manager_id;
案例：哪个部门的员工个数大于二？
SELECT
COUNT(*),
department_id
FROM
employees
GROUP BY department_id
HAVING COUNT(*) > 2 ;
#select dep_id from emp group by dep_id having count(*)>2;
案例：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT
MAX(salary),
job_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000 ;
#select job_id ,max(salary) from emp where commission_pct IS NOT NULL group by job_id having max(salary)>12000;
案例：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个？
SELECT manager_id ,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
#select manager_id from emp where manager_id>102 group by manager_id having min(salary)>5000;
#按照员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些？
SELECT COUNT(*) AS c
FROM employees
GROUP BY LENGTH(last_name)
HAVING c>5;
# select count(*) from emp group by length(name) having count(*)>5;
#查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY department_id,job_id;
#select avg(salary) from emp group by dep_id,job_id;
#查询每个部门每个工种的员工的平均工资并且按照平均工资的高低显示
SELECT AVG(salary),job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) ASC;
#select avg(salary) from emp group by dep_id,job_id order by avg(salary) asc;
6.连接查询
又称为多表查询，当查询的字段来自多个表时，就会用到连接查询。
笛卡尔乘积现象：表1有m行，表2有n行，结果：m*n行
发生原因：没有有效的连接条件
分类
①按年代分类
sql92:仅仅支持内连接
sql99：不支持全外连接
②按功能分类
| 内连接 | 外连接 | 交叉连接 |
|---|---|---|
| 等值连接 | 左外连接 | |
| 非等值连接 | 右外连接 | |
| 自连接 | 全外连接 |
1.等值连接
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序，分组，筛选
#案例一：查询女优名对应的男优名
SELECT
NAME,
boyName
FROM
beauty,
boys
WHERE beauty.boyfriend_id = boys.`id` ;
#select name, boyname from girl ,boy where girl.boyfriend_id=boy.id;
#案例：查询员工名和对应的部门名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE employees.`department_id` = departments.`department_id` ;
#select name ,dep_name from emp e,dep d where e.dep.id= d.id;
#案例：查询员工名，工种号，工种名。
SELECT
last_name,
emp.`job_id`,
job_title
FROM
employees emp,
jobs job
WHERE emp.`job_id` = job.`job_id` ;
#select name , e.job_id,job_title from emp e,job j where e.job_id=j.id;
#案例：查询有奖金的员工名和部门名
SELECT
last_name,
department_name
FROM
employees emp,
departments dep
WHERE commission_pct IS NOT NULL && emp.`department_id` = dep.`department_id` ;
#select name ,dep_name from emp e ,dep d where e.dep_id =d.id &&e.salary_pct is not null;
#案例：查询城市名第二个字符为o的部门
SELECT
department_name
FROM
locations l,
departments d
WHERE l.`location_id` = d.`location_id`
AND l.`city` LIKE '_o%' ;
#select dep_name from location l , dep d where l.city like %_o% && l.id =d.location_id;
#案例：查询每个城市的部门个数
SELECT
COUNT(*),
city
FROM
locations l,
departments d
WHERE l.`location_id` = d.`location_id`
GROUP BY l.`city` ;
#select count(*),city from loca l,dep d where l.loc_id=d.loc_id group by count(*) asc;
#案例：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT
d.`department_name`,
d.manager_id,
MIN(salary)
FROM
employees e,
departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY d.`department_id`,
d.`department_name` ;
#select dep_name ,d.manager_id ,min(salary) from emp e ,dep d where e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL GROUP BY d.`department_id`,d.`department_name` ;
#案例：查询每个工种的工种名和员工的个数，并且按照员工个数降序排序
SELECT
j.job_title,
COUNT(*)
FROM
jobs j,
employees e
WHERE j.`job_id` = e.`job_id`
GROUP BY e.`job_id`,
j.`job_title`
ORDER BY COUNT(*) DESC ;
#案例：查询员工名，部门名和所在城市
SELECT
last_name,
department_name,
city
FROM
employees e,
departments d,
locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id` ;
2.非等值连接
#案例：查询员工的工资和工资级别
SELECT DISTINCT
salary,
grade_level
FROM
employees e,
job_grades j
WHERE e.salary >= j.lowest_sal && e.salary <= j.highest_sal
ORDER BY salary ASC ;
3.自连接
#案例：查询员工名和上级的名称
SELECT
e.last_name,
m.last_name
FROM
employees e,
employees m
WHERE e.manager_id = m.employee_id ;
4.内连接
INNER 可以省略
#查询员工名，部门名
SELECT
last_name,
department_name
FROM
employees e
INNER JOIN departments d
ON e.department_id = d.department_id ;
#查询名字中包含e的员工名和工种名
SELECT
last_name,
job_title
FROM
employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE last_name LIKE '%e%' ;
#查询部门个数>3的城市名和部门个数
SELECT
city,
COUNT(*)
FROM
departments d
INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING COUNT(*) > 3 ;
#查询哪个部门的部门员工个数>3的部门名和员工个数，并按照个数降序排序
SELECT
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY e.department_id
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC ;
#查询员工名，部门名，工种名，并按照部门名降序排序
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
grade_level,
salary
FROM
job_grades j
INNER JOIN employees e
ON e.`salary` BETWEEN j.`lowest_sal`
AND j.`highest_sal` ;
#查询每个工资级别的个数，并且降序排序
SELECT grade_level,COUNT(*)
FROM employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal`
AND j.`highest_sal`
GROUP BY grade_level
ORDER BY COUNT(*) DESC;
#查询员工的名字和上级的名字
SELECT e1.last_name, e2.last_name
FROM employees e1
INNER JOIN employees e2
ON e1.`employee_id`=e2.`manager_id`;
5.左外连接
语法：
SELECT 查询列表
FROM 表1 【连接类型】
JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组
HAVING 筛选条件
ORDER BY 排序条件
连接类型：
内连接：inner
左外连接：left
右外连接：right
全外连接：full
交叉连接：cross
外连接
用于查询一个表中有，另一个表中没有的数据
左外连接，left左边是主表
右外连接，right右边是主表
Mysql不支持全外连接
#没有男朋友的女生
SELECT
g.`name`,b.`boyName`
FROM beauty g
LEFT JOIN boys b
ON g.`boyfriend_id`=b.`id`
WHERE b.`boyName` IS NULL;
6.交叉连接
笛卡尔乘积
7.子查询
出现在其它语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
①按照子查询出现的位置：
| select后面 | from后面 | where或having后面 | exists后面 |
|---|---|---|---|
| 仅仅支持标量子查询 | 支持表子查询 | 标量子查询，列子查询 | 表子查询 |
②按照结果集的行列数不同：
| 标量子查询 | 列子查询 | 行子查询 | 表子查询 |
|---|---|---|---|
| 结果只有一行一列 | 结果一列多行 | 一行多列 | 多行多列 |
1）where或having后面
特点：
子查询一般放在小括号内
子查询一般放在条件的右边
标量子查询，一般搭配着单行操作符
列子查询：一般搭配多行操作符使用
1.标量子查询
#谁的工资比Abel高
SELECT
last_name
FROM
employees
WHERE salary >
(SELECT
salary
FROM
employees
WHERE last_name = 'Abel') ;
#返回job_id于141号员工相同，salary比143号员工多的员工 姓名，job_id和工资
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)
#返回公司工资工资最少的员工的姓名，job_id,salary
SELECT
last_name,
job_id,
salary
FROM
employees
WHERE salary =
(SELECT
MIN(salary)
FROM
employees);
#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT
department_id,
MIN(salary)
FROM
employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT
MIN(salary)
FROM
employees
WHERE department_id = 50) ;
2.列子查询
多行操作符：
IN / NOT in：等于列表中的任意一个
ANY / SOME ：和子查询返回的某一个值比较
ALL ：和子查询返回的所有值比较
#返回location_id是1400或者1700的部门中的所有员工姓名
SELECT
last_name
FROM
employees
WHERE department_id IN
(SELECT DISTINCT
department_id
FROM
departments
WHERE location_id IN (1400, 1700)) ;
#返回其他工种中比job_id为IT_PROG部门任意工资低的员工
#工号，姓名，job_id以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE salary <
(SELECT
MAX(salary)
FROM
employees
WHERE job_id = 'IT_PROG')
AND job_id !='IT_PROG';
#返回其他工种中比job_id为IT_PROG部门所有工资低的员工
#工号，姓名，job_id以及salary
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE salary <
(SELECT
MIN(salary)
FROM
employees
WHERE job_id = 'IT_PROG')
AND job_id !='IT_PROG';
*********************************
3.行子查询
#查询员工编号最小并且工资最高的员工信息
SELECT
*
FROM
employees
WHERE employee_id =
(SELECT
MIN(employee_id)
FROM
employees)
AND salary =
(SELECT
MAX(salary)
FROM
employees)
2）SELECT 后面
#查询每个部门的员工个数
SELECT
d.*,
(SELECT
COUNT(*)
FROM
employees e
WHERE e.department_id = d.department_id)
FROM
departments d ;
#查询员工号等于102的部门名
SELECT
department_name
FROM
departments
WHERE department_id =
(SELECT
department_id
FROM
employees
WHERE employee_id = 102) ;
3）FROM 后面
#查询每个部门平均工资的工资等级
SELECT
grade_level ,aa.department_id
FROM
(SELECT
AVG(salary) ag,
department_id
FROM
employees
GROUP BY department_id) aa
INNER JOIN job_grades j
ON aa.ag BETWEEN lowest_sal
AND highest_sal ;
4）exists后面（相关子查询）
#查询有员工的部门名
SELECT department_name FROM departments d
WHERE EXISTS(
SELECT * FROM employees e WHERE d.department_id=e.department_id
);
#查询没有女朋友的男生信息
SELECT bo.* FROM boys bo WHERE
bo.`id` NOT IN(SELECT boyfriend_id FROM beauty);
5）子查询经典案例祥讲
1.查询工资最低的员工信息：last_name,salary
SELECT last_name,salary FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees);
2.查询平均工资最低的部门信息
SELECT * FROM departments WHERE department_id=
(SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary)
LIMIT 1)
3.查询平均工资最低的部门信息和该部门的平均工资
SELECT d.*,a1.ag FROM departments d JOIN
(SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ORDER BY AVG(salary)
LIMIT 1) a1
ON d.department_id=a1.department_id
4.查询平均工资最高的job信息
SELECT j.* FROM jobs j WHERE j.job_id=
(SELECT job_id FROM employees
GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1)
5.查询平均工资高于公司平均工资的部门有哪些
SELECT department_id FROM
(SELECT department_id ,AVG(salary) AS avg1 FROM employees GROUP BY department_id) e1
WHERE e1.avg1>(SELECT AVG(salary) AS avg2 FROM employees)
6.查询出公司中所有manager的详细信息
SELECT * FROM employees
WHERE employee_id IN(SELECT DISTINCT manager_id FROM employees);
7.各个部门中，最高工资中最低的那个部门的最低工资是多少
SELECT MIN(salary) FROM employees GROUP BY department_id
HAVING department_id=(
SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary)
LIMIT 1)
8.查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary
SELECT last_name,department_id,email,salary FROM employees WHERE employee_id=(
SELECT manager_id FROM departments WHERE department_id=(
SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary)
DESC LIMIT 1))
8.分页查询
*语法：limit(currentPage-1)size,size
#查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
#查询第11-25条员工信息
SELECT * FROM employees LIMIT 10,15;
#查询有奖金的员工，并且工资最高的前十名显示出来
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0 ,10;
9.联合查询
要查询的结果来自于多个表，且多个表没有直接的连接关系，单查询的信息一致时
特点：
1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好一致
3.union关键字默认去重，如果使用union all 可以不去除重复项
案例：查询员工部门编号大于90或邮箱包含a的员工信息
SELECT * FROM employees WHERE department_id>90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';
三，DML数据操作语言
插入insert
一：插入语句
#插入beauty一行数据
INSERT INTO beauty(NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES('波多野吉依','女','1998-11-11','13342969497',
NULL,10)
#可以为null的列如何不插入值
直接写null，或列名少写一列
INSERT INTO beauty(NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES('小泽玛利亚','女','1999-11-11','13342456497',
NULL,11)
INSERT INTO beauty VALUES(15,'马蓉','女','1989-11-11','13342456123',
NULL,12);
INSERT INTO beauty SET id=16,NAME='刘亦菲', sex='女',borndate='1989-10-01',
phone='15945231056',boyfriend_id=16;
#insert 嵌套子查询，将一个表的数据插入另一张表
INSERT INTO beauty (NAME,sex,borndate,phone,boyfriend_id)
SELECT '妲己','女','1111-11-11','13146587954',0;
修改update
二，修改
UPDATE beauty SET phone='110' WHERE id=16;
多表修改：sql99
UPDATE 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件
SET 列=值
WHERE 筛选条件
#修改张无忌的女朋友手机号为114
UPDATE beauty g
INNER JOIN boys b
ON g.boyfriend_id=b.id
SET g.phone='114'
WHERE b.boyName='张无忌';
#修改没有男朋友的女生的男朋友编号都为4号
UPDATE beauty g
LEFT JOIN boys b
ON g.`boyfriend_id`=b.id
SET g.`boyfriend_id`=4
WHERE b.id=NULL;
删除delete
三，删除
DELETE 和 TRUNCATE 的区别：
1.delete可以加where条件，truncate不行
2.truncate删除效率高
3.加入要删除的表中有自增列，
用delete删除整个表后在插入数据，从断点处开始插入
用truncate删除后在插入数据，从1开始。
4.truncate删除没有返回值，delete有返回值
5.truncate删除不能回滚，delete删除可以回滚
DELETE FROM beauty WHERE id=17;
语法：truncate TABLE 表名;
#删除张无忌的女朋友的信息
DELETE g FROM beauty g
INNER JOIN boys b
ON g.boyfriend_id=b.id
WHERE b.id=1;
#删除黄晓明以及他女朋友的信息
DELETE b,g FROM beauty g
INNER JOIN boys b
ON b.`id`=g.`boyfriend_id`
WHERE b.`boyName`='黄晓明';
多表删除 :TRUNCATE
TRUNCATE TABLE boys
四，DDL数据定义语言
1.库和表的管理
一，库的管理
创建 CREATE
CREATE DATABASE IF NOT EXISTS mydb1 ;
修改 ALTER
1.更改字符集
ALTER DATABASE mydb1 CHARACTER SET utf8;
删除 DROP
DROP DATABASE IF EXISTS school;
二，表的管理
创建 CREATE
CREATE TABLE book(
id INT PRIMARY KEY,
b_name VARCHAR(30),
price DOUBLE,
author_id INT ,
publishDate DATE
);
DESC book ;
CREATE TABLE author(
id INT PRIMARY KEY ,
au_name VARCHAR(20),
nation VARCHAR(10)
);
DESC author;
修改 ALTER
1.修改列名
ALTER TABLE book CHANGE COLUMN publishDate pub_date DATETIME;
2.修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pub_date DATE;
3.添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
4.删除新列
ALTER TABLE author DROP COLUMN annual;
5.修改表名
ALTER TABLE author RENAME TO book_author;
删除 DROP
DROP TABLE IF EXISTS my_employee;
SHOW TABLES;
复制
1.仅仅复制表的结构
CREATE TABLE copy LIKE book_author;
2.复制表的结构加数据
CREATE TABLE copy2
SELECT * FROM book_author;
3.复制部分结构
CREATE TABLE copy3
SELECT id,au_name
FROM book_author
WHERE id=0;
2.数据类型
数值型
1.整型
| TINYINT | SMALLINT | MEDIUMINT | INT/INTEGER | BIGINT |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 8 |
如何设置无符号和有符号(默认有符号)
DROP TABLE tab_int;
CREATE TABLE tab_int(t1 INT,t2 INT UNSIGNED);
INSERT INTO tab_int(t1,t2) VALUES(-1,1);
DESC tab_int;
1）如果插入的数值超出了整形的范围，会报out of range异常，并且插入
临界值。
2）如果不设置长度，会有默认的长度。
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配
zerofill使用。
2.小数
①定点数
dec（M,D）
②浮点数
float（4） ，double（8）
M，D的意思：M指定一共多少位，D指定小数几位，超出会四舍五入。
MD都可以省略，
如果是dec，则M默认为10，D默认为0
如果是浮点数，则会根据插入数值的精度改变精度
定点型精度相对较高。
3.字符型
①较短的文本
CHAR(M)默认为1,VARCHAR(M)
M:字符数
char：固定长度字符，比较耗费空间，但是效率高。
varchar：可变长度字符
ENUM 枚举类
CREATE TABLE tab_char(
t1 ENUM('a','c','b')
);
SET 集合
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set(s1) VALUES('a,b');
BINARY:保存较短的二进制。
②较长的文本
text（文本）,BLOB(较大的二进制)
4.日期型
DATE:日期
DATETIME:日期加时间，8字节
timestamp：跟时区有关系，建议使用，4字节
time：时间
year：年
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date(t1,t2)
VALUES(NOW(),NOW());
SELECT * FROM tab_date;
SET time_zone='+9:00';
#设置时区为东9区
3.常见约束
含义：一种限制，用于限制表中的数据，保证数据的一致性。
-
NOT NULL
-
DEFAULT
-
PRIMARY KEY 唯一，且不为空
-
UNIQUE 唯一，可以为空
-
CHECK Mysql不支持
-
FOREIGN KEY 外键约束，用于限制两个表的关系，
用于保证该字段的值必须来自于主表的关联列的值。
约束的分类：
列级约束：除外键约束
表级约束：除了非空，默认。
CREATE TABLE 表名(
字段1 字段类型 列级约束,
字段2 字段类型 列级约束,
表级约束
);
#创建表时添加列级约束
DROP TABLE tab_test;
CREATE TABLE tab_test(
id INT PRIMARY KEY,
stu_name VARCHAR(20) NOT NULL,
gender CHAR DEFAULT '男',
seat_id INT UNIQUE,
major_id INT REFERENCES tab_major(id)
);
CREATE TABLE tab_major(
id INT PRIMARY KEY ,
major_name VARCHAR(20) NOT NULL
);
DESC tab_test;
SHOW INDEX FROM tab_test;
#查看索引信息
#添加表级约束
CREATE TABLE tab_test(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(20) NOT NULL,
gender CHAR DEFAULT '男',
seat_id INT UNIQUE,
major_id INT ,
CONSTRAINT m_id FOREIGN KEY(major_id) REFERENCES tab_major(id)
);
CONSTRAINT m_id 可以省略
面试题：主键约束和唯一约束的区别：
都可以保证唯一性，
主键不能为空 ，unique 能为空，但是只能有一个null。
主键只能有1个，unique可以有多个。
都允许两个列组合成一个约束。
面试题：外键：
要求在从表设置外键关系
从表的外键列类型和主表的关联列类型一致，名称无要求
要求主表的关联列必须是主键或者唯一键
插入数据应该先插入主表再插入从表
删除数据应该先删除从表，在删除主表
二，修改表时添加约束
CREATE TABLE tab_test2(
id INT ,
stu_name VARCHAR(20) ,
gender CHAR ,
seat_id INT ,
major_id INT
);
ALTER TABLE tab_test2 MODIFY COLUMN stu_name VARCHAR(20) NOT NULL ;
ALTER TABLE tab_test2 MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#添加外键
ALTER TABLE tab_test2 ADD FOREIGN KEY(major_id) REFERENCES tab_major(id);
4.标识列
自增长列 AUTO_INCREMENT
特点：
1.表示必须和一个key搭配
2.一个表最多一个标识列
3.标识列类型只能是数值型
4.标识列可以通过set auto_increment_increment=3;设置步长
CREATE tab_auto(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL
);
五，TCL语言：事务控制语言
事务：一个或一组sql语句组成的执行单元，
要么全部执行,要么都不执行。
存储引擎:在MySQL中的数据用各种不同的技术存储在文件中。
通过show ENGINES;来查看mysql支持的存储引擎。
innodb引擎支持事务。
事务的ACID属性：
1.原子性:事务是一个不可分割的工作单位，要么都发生，要么都不发生。
2.一致性：事务必须使数据库从一个一致性状态变为另一个一致性状态。
3.隔离性：一个事务的执行不能被另一个事务干扰。
4.持久性：事务一旦被提交，对数据库事务的改变就是永久性的。
DELETE 和 TRUNCATE 在事务中的区别：
演示delete
SET autocommit=0;
START TRANSACTION;
DELETE FROM tab_teacher;
ROLLBACK;
演示 TRUNCATE
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE tab_teacher;
ROLLBACK;
DELETE 是直接删除表中数据，truncate是江表删除，创建一张与原来一样的空表。
六，视图
含义：虚拟表，和普通表格一样使用
通过表动态生成的数据
1.创建视图
语法：
CREATE VIEW 视图名
AS
查询语句 ;
# 案例：查询姓名中包含a字符的员工名，部门名和工种信息
create view view1 as
select e.last_name,d.department_name ,j.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
where e.last_name like '%a%';
select * from view1;
# 案例：查询各个部门的平均工资级别
create view view2 as
select j.grade_level ,aa.department_id
from job_grades j
inner join (select avg(salary) avg_s,department_id from employees group by department_id) aa
on aa.avg_s between j.lowest_sal and j.highest_sal;
select * from view2;
# 案例：查询平均工资最低的部门信息
create view view3 as
select avg(salary) avg_s ,department_id
from employees
group by department_id
order by avg_s asc
limit 1;
select * from view3;
2.视图修改
①create OR REPLACE VIEW 视图名 AS 查询语句;
②alter VIEW 视图名 AS 查询语句;
3.删除视图
DROP VIEW v1,v2;
4.查看视图
DESC v1;
#创建视图emp_v1，要求查询电话号码以011开头的员工姓名和工资，邮箱
CREATE VIEW emp_v1 AS
SELECT last_name ,salary,email FROM employees WHERE
phone_number LIKE '%011';
#创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
CREATE VIEW v4 AS
SELECT department_id FROM employees GROUP BY department_id
HAVING MAX(salary)> 12000;
CREATE VIEW emp_v2 AS
SELECT * FROM departments WHERE department_id IN(SELECT * FROM v4);
5.视图的更新
视图的可更新性和视图中查询的定义有关，以下类型的视图是不能更新的。
1.包含以下关键字的sql语句：分组函数，distinct，group by，having，union
2.常量视图
3.select中包含子查询的
4.join
5.from 一个不能更新的视图
6.where子句的子查询引用了from子句的表
6.视图和表的对比：
| 创建语法的关键字 | 是否实际占用物理空间 | 使用 | |
|---|---|---|---|
| 视图 | CREATE VIEW | 只是保存了sql逻辑 | 增删改查，一般不能增删改 |
| 表 | CREATE TABLE | 占用 | 增删改查 |
七，变量
系统变量 ：变量由系统提供，不是用户自定义，属于服务器层面。
查看系统所有变量：show GLOBAL VARIABLES;
查看满足条件的部分系统变量： SHOW GLOBAL VARIABLES LIKE ‘%char%’;
查看指定的某个系统变量的值： SELECT @@global.autocommit;
为某个系统变量赋值：set @@global.系统变量名=值;
全局变量:GLOBAL
作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话有效，但不能
跨重启。
会话变量:SESSION
作用域：针对当前的会话有效。
用户自定义变量
用户变量
声明： SET/SELECT @用户变量名 :=值;
赋值：通过 SELECT 字段 INTO 变量名;或 SET/SELECT @用户变量名 :=值;
使用：select @用户变量名;
应用在任何地方。
作用域：针对当前会话和连接有效。
局部变量
作用域：作用在定义它的begin END 块中。
声明： DECLARE 变量名 类型 （default 值）;
赋值：通过 SELECT 字段 INTO 变量名;或 SET/SELECT @变量名 :=值;
使用：select @变量名;
只能放在begin END 中的第一句话
八，存储过程和函数
存储过程：
一组预先定义好的sql语句集合，理解成批处理语句。
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。
1.创建语法：
CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
一组合法的sql语句;
END
参数列表：参数模式 参数名 参数类型
参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
OUT ：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出
如果存储过程只有一句话，begin END 可以省略
存储过程体中的每条sql语句的结尾需要必须加分号，
存储过程的结尾可以使用 DELIMITER 重新设置。
2.调用
CALL 存储过程名（实参列表）;
3.案例
#插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE my_a()
BEGIN
INSERT INTO admin(username,PASSWORD) VALUES('yin','666');
INSERT INTO admin(username,PASSWORD) VALUES('aa','123');
INSERT INTO admin(username,PASSWORD) VALUES('bb','666');
INSERT INTO admin(username,PASSWORD) VALUES('cc','123');
INSERT INTO admin(username,PASSWORD) VALUES('dd','666');
END $
#创建存储过程实现 根据女生名查询对应的男生信息
DELIMITER $
CREATE PROCEDURE my_b(IN beauty_name VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beauty_name;
END $
CALL my_b('热巴');
#根据女生名返回他的男朋友名
DELIMITER $
CREATE PROCEDURE my_d(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyName;
END $
CALL my_d('小昭',@b_name);
SELECT @b_name;
#传入两个值a，b，最终翻倍返回a和b
DELIMITER $
CREATE PROCEDURE my_e(INOUT a INT ,INOUT b INT )
BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @m=10;
SET @n=20;
CALL my_e(@m,@n);
SELECT @m,@n;
4.删除存储过程
DROP PROCEDURE 存储过程名
DROP PROCEDURE my_a;
5.查看存储过程的信息
SHOW CREATE PROCEDURE my_b;
函数
存储过程可以有0/n个返回值：适合批量增删改
函数有且仅有一个返回值：适合查询
1.创建
DELIMITER $
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
END
注意：
参数列表：参数名，参数类型
一定会有return语句
2.使用
SELECT 函数名(参数列表)
#返回公司员工个数
DELIMITER $
CREATE FUNCTION my_f1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0 ;
SELECT COUNT(*) INTO c FROM employees;
RETURN c;
END $
SELECT my_f1();
#根据员工名返回他的工资
DELIMITER $
CREATE FUNCTION my_f2(NAME VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE c DOUBLE;
SELECT salary INTO c FROM employees WHERE last_name=NAME;
RETURN c;
END $
SET @a='Hunold';
SELECT my_f2(@a);
3.查看
SHOW CREATE FUNCTION my_f2;
4.删除
DROP FUNCTION my_f2;
九，流程控制
分支结构
1.if （表达式1，表达式2，表达式3）
如果表达式1成立，就返回表达式2的值，否则返回表达式3的值。
应用在任何地方
2.case
1)switch-CASE
语法:
CASE 要判断的字段或者表达式
WHEN 常量1 THEN 要显示的值1或者语句1
WHEN 常量2 THEN 要显示的值2或者语句2
…
ELSE 要显示的值n或者语句n；
案例：查询员工的工资，要求
部门号==30，显示的工资为1.1倍，
部门号==40，显示的工资为1.2倍，
部门号==50，显示的工资为1.3倍，
其他部门，显示原有工资。
SELECT
salary AS 原始工资,
department_id ,
CASE
department_id
WHEN 30
THEN salary * 1.1
WHEN 40
THEN salary * 1.2
WHEN 50
THEN salary * 1.3
ELSE salary
END AS 新工资
FROM
employees ;
2)CASE 使用2：
语法：
CASE
WHEN 条件1 THEN 要显示的值1或语句1
WHEN 条件2 THEN 要显示的值2或语句2
…
ELSE 要显示的值n或语句n
END
案例：查询员工的工资情况
如果>2w，显示A
如果>1.5w，显示B
如果>1w，显示C
否则，显示D
SELECT
salary,
CASE
WHEN salary > 20000
THEN 'A'
WHEN salary > 15000
THEN 'B'
WHEN salary > 10000
THEN 'C'
ELSE 'D'
END AS 工资等级 FROM employees
可以放在任何地方
#创建存储过程，根据传入的成绩，显示等级，90A,80B，70C，60D ，F
DELIMITER $
CREATE PROCEDURE my_1(IN score INT)
BEGIN
CASE
WHEN score BETWEEN 90 AND 100
THEN SELECT 'A';
WHEN score BETWEEN 80 AND 90
THEN SELECT 'B';
WHEN score BETWEEN 70 AND 80
THEN SELECT 'C';
WHEN score BETWEEN 70 AND 60
THEN SELECT 'D';
ELSE SELECT 'E';
END CASE;
END $
CALL my_1(95);
3.if
语法：
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
…
ELSE 语句n;
END IF;
只能用在begin end中
#创建存储过程，根据传入的成绩，返回等级，90A,80B，70C，60D ，F
DELIMITER $
CREATE FUNCTION my_2( score INT) RETURNS CHAR
BEGIN
IF score >=90 THEN RETURN'A';
ELSEIF score >=80 THEN RETURN'B';
ELSEIF score >=70 THEN RETURN'C';
ELSEIF score >=60 THEN RETURN'D';
ELSE RETURN'E';
END IF;
END $
SELECT my_2(85);
循环结构
在存储过程或函数里面使用
1.while
语法：
标签:WHILE 循环条件 DO
循环体;
END WHILE 标签;
循环控制和标签搭配使用
2.loop
语法：
标签： LOOP
循环体;
END LOOP 标签;
3.repeat
语法：
标签： REPEAT
循环体;
UNTIL 结束循环的条件
END REPEAT 标签;
循环控制
ITERATE 类似continue
LEAVE 类似break
left join==left outer join
a left join b 就是取a和b的交集加a剩下的部分
inner join
a inner join b就是取交集
十，MySQL架构介绍
1.字符集，权限问题
出现乱码问题如何解决：
vim /etc/my.conf
最后加上一句话：
character_set_server=utf8
修改已经创建好的库和表的字符集
alter database xxx character set 'utf8'; alter table xxx convert to character set 'utf8';
远程访问权限问题：
1.创建新用户：
create user username identified by 'password';
此时默认支持所有访问，但是权限很低。
2.创建用户并授权:
grant all privileges on *.*to用户@'%' identified by 'password';
3.修改某个用户密码：
update mysql.user set password=password('123456')where user='lisi';
2.杂项配置
group by使用原则：select 后面只能放 函数 和group by后的字段
3.MySQL逻辑架构
1.逻辑架构图
2.sql语句执行步骤
1.去连接池获取连接
2.查询缓存，如果有直接返回，如果没有执行第3步。
3.sql接口分析
4.sql解析器解析复杂sql
5.sql优化器，不改变结果的前提下，生成优化计划
6.存储引擎按照计划分类型执行
7.查询结果存入缓存
8.返回结果
3.利用show profile查看sql的执行周期
1.开启查询缓存
修改配置文件/etc/my.cnf
新增一行query_cache_type=1
重启mysql
2.开启执行周期日志
先开启show variables like '%profiling%';
set prodiling=1;
3.查询数据
select * from mytbl2 where id =2;
4.查看计划
show profiles;
show profile cpu,block io for query 编号 #查看程序的执行步骤
5.查询同一个sql
6.命中缓存条件，sql一模一样
4.MySQL存储引擎
1.myISAM和InnoDB区别
| 对比项 | MiISAM | InnoDB |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，就算操作一条记录也会锁住整个表，不适合高并发。 | 行锁，操作时只锁住某一行，不对其他行有影响。（适合高并发，但可能发生死锁） |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求比较高。而且内存大小特别影响性能。 |
| 关注点 | 节省资源，消耗少，简单业务 | 并发写，事务，更大资源 |
| 默认安装 | Y | Y |
2.其他sql引擎
1、show engines；#查看MySQL所有的引擎，
2、show variables like "storage_engine%";查看当前正在使用的引擎
NDB 存储引擎
2003 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。
Memory 存储引擎
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。
Infobright 存储引擎
第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。
NTSE 存储引擎
网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。
BLACKHOLE
黑洞存储引擎，可以应用于主备复制中的分发主库。
5.SQL练习
1.练习脚本
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);
ALTER TABLE `t_dept`
add CEO INT(11) ;
update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;
2.两表关联查询
1.增加CEO字段前
#查询所有门派的人员信息
SELECT * FROM t_emp
WHERE deptId IS NOT NULL;
#列出所有人物，并显示其门派信息
SELECT a.* ,b.*
FROM t_emp a
LEFT JOIN
t_dept b
ON a.`deptId`=b.`id`
#所有没有门派的人员
SELECT * FROM t_emp WHERE deptId IS NULL;
#所有没人入的门派
SELECT a.*
FROM t_dept a
LEFT JOIN t_emp b
ON a.`id`=b.`deptId`
WHERE b.`id` IS NULL;
#列出所有人员和机构的对照关系
SELECT a.*,b.*
FROM t_emp a LEFT JOIN t_dept b
ON a.`deptId`=b.`id`
UNION ALL
SELECT d.*,c.* FROM t_dept c
LEFT JOIN t_emp d
ON c.id=d.`deptId`
WHERE d.id IS NULL;
#列出所有没入门派的人员和所有没人入的门派
SELECT a.*,b.* FROM t_emp a
LEFT JOIN t_dept b ON a.`deptId`=b.`id`
WHERE b.`id` IS NULL
UNION
SELECT d.*,c.* FROM t_dept c LEFT JOIN t_emp d
ON c.id=d.deptId
WHERE d.id IS NULL;
#求哥哥门派对应的掌门人名称
SELECT a.*,b.*
FROM t_emp a INNER JOIN t_dept b
ON a.`id`=b.`CEO`
#求各个门派掌门人的平均年龄
SELECT AVG(a.age)
FROM t_emp a
INNER JOIN t_dept b
ON a.`deptId`=b.`CEO`
#求所有人物对应的掌门名称
SELECT c.name , d.name
FROM t_emp c LEFT JOIN
(
SELECT a.name NAME,a.`id` id FROM t_emp a
INNER JOIN t_dept b
ON a.`id`=b.`CEO`
) d
ON c.id=d.id;
2.增加CEO字段后
#1、列出自己的掌门比自己年龄小的人员
SELECT a.* FROM t_emp a
INNER JOIN t_ceo b
ON a.`deptId`=b.deptId
WHERE a.`age`< b.age;
#2、列出所有年龄低于自己门派平均年龄的人员
SELECT c.* FROM t_emp c
INNER JOIN
(
SELECT AVG(a.age) aage,a.`deptId` aid FROM t_emp a
INNER JOIN t_dept b
ON a.deptId=b.`id`
GROUP BY a.`deptId`
) d
ON c.`deptId`=d.aid
WHERE c.`age`<d.aage;
#3、列出至少有2个年龄大于40岁的成员的门派
SELECT d.* FROM t_dept d
INNER JOIN
(
SELECT deptId,COUNT(*) cavg
FROM
(
SELECT a.*
FROM t_emp a
WHERE age>40
) b
GROUP BY b.deptId
) c
ON d.`id`=c.deptId
WHERE c.cavg>=2
#4、至少有2位非掌门人成员的门派
SELECT c.deptName,c.id,COUNT(*)
FROM t_emp a INNER JOIN t_dept c
ON a.deptId=c.id
LEFT JOIN t_dept b ON a.id=b.ceo
WHERE b.id IS NULL
GROUP BY c.deptName,c.id
HAVING COUNT(*)>=2;
#5、列出全部人员，并增加一列备注“是否为掌门”，
#如果是掌门人显示是，不是掌门人显示否
SELECT a.name ,(CASE WHEN b.id IS NULL THEN '否' ELSE '是' END) '是否为掌门'
FROM t_emp a
LEFT JOIN t_ceo b
ON a.id=b.id
#6、列出全部门派，并增加一列备注“老鸟or菜鸟”，若门派的平均值年龄>50显示“老鸟”，否则显示“菜鸟”
SELECT b.`deptName`, IF(AVG(a.age)>50,'老鸟','菜鸟') '老鸟or菜鸟'
FROM t_emp a INNER JOIN t_dept b
ON a.`deptId`=b.`id`
GROUP BY b.`deptName`,b.`id`
#7、显示每个门派年龄最大的人
SELECT a.* FROM t_emp a
INNER JOIN (
SELECT MAX(age) age,deptId FROM t_emp b GROUP BY deptId
) c
ON a.`deptId`=c.deptId
WHERE a.age=c.age;
#8、显示每个门派年龄第二大的人
SET r=0;
SET @l=0;
SELECT a.deptId,a.name,a.age
FROM (
SELECT t.*,
IF(@l=deptid,@r:=@r+1,@r:=1)AS rk,
@l:=deptid AS last_deptid
FROM t_emp t
ORDER BY deptid,age DESC
)
a WHERE a.rk=2;
十一，索引优化
1.索引简介
1.是什么？
索引是数据结构
索引的目的是提高查询效率，可以类比字典。
2.索引的特点
优点：查询，排序快。
缺点：写操作慢，占用磁盘空间。
3.索引结构
1.BTree
非叶子节点：数据，向下的指针，指向数据的指针（二叉树）
2.B+Tree
非叶子节点：数据，向下的指针（平衡二叉树）
3.mysql的选择
mysql选择B+Tree
4、聚簇索引与非聚簇索引
一张表聚簇索引只有一个：主键索引
其他，通过命令创建的索引都是非聚簇索引
2.索引分类
1.单值索引
create index idx_name on t_emp(name);
2.唯一索引
create unique index idx_empno on t_emp(empno);
3.主键索引
表的主键指定后，建表时自动创建。
4.复合索引
create index idx_age_deptId_name on t_emp(age,deptId,name);
5.查询
show index from t_emp;
6.删除
drop index indexName on 表名
7.哪些情况需要索引
1）主键自动建立唯一索引
2）频繁作为查询条件的字段应该创建索引
3）查询中与其他表关联的字段，外键关系建立索引
4）单键/组合索引的选择问题，组合索引性价比更高
5）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6）查询中统计或者分组字段
8.哪些情况不需要创建索引
1）表记录太少
2）经常增删改的表或字段
3）Where条件里用不到的字段不创建索引
4）过滤性不好的不适合建索引
3.性能分析
1.是什么
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
2.为什么要用
1）表的读取顺序
2）哪些索引可以使用
3）数据读取操作的操作类型
4）哪些索引被实际使用
5）表之间的引用
6）每张表有多少行被物理查询
3.怎么用
1）id
id相同，执行顺序由上到下
id不同，执行顺序从大到小
id既有相同又有不同，先从大到小，在从上到下
关注点：id每个号码，表示一趟独立的查询。一个sql的查询趟数越少越好。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oPXzBxAE-1601998234924)(img/mysql11111111.png)]
2）type
ALL，全表扫描，建索引优化
INDEX，使用了索引但是没用通过索引进行过滤
RANGE，使用了索引，但是过滤条件是范围查询
3）key_len
定义：where后面筛选字段命中索引长度
key_len规则：数值越大，查询越快
4）rows
定义：整个sql物理扫描的行数（预估）
Rows规则：数值越小，查询越快
5）Extra
Using filesort orderby没有用上索引
Using temporary groupby没有用上索引
Using join buffer join字段没有用上索引
4.批量数据脚本生成
1.创建表
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;
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;
2.开启自定义函数开关
创建函数，假如报错：This function has none of DETERMINISTIC......
# 由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
# 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1
3.创建两个函数
随机产生字符串
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;
4.创建存储过程
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表添加随机数据
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;
5.调用存储过程生成函数
#执行存储过程，往dept表添加1w条数据
#执行存储过程，往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000);
#执行存储过程，往emp表添加50w条数据
#执行存储过程，往emp表添加50万条数据
DELIMITER ;
CALL insert_emp(100000,500000);
6.创建批量删除索引工具
1）查询索引名
2）怎么从列表里取出一个个索引名
3）拼写的字符串转换成sql
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$$
4）调用工具
CALL proc_drop_index("dbname","tablename");
5.单表索引优化
1）全值匹配我最爱
SQL语句
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptId=4 AND emp.name='abcd';
#创建索引前：0.069s
#创建索引后：
创建索引前
CREATE INDEX idx_age_deptId_name ON emp(age,deptId,NAME);
2）最佳左前缀法则
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' #0.138s
#中了age
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptId=4 AND emp.name='abcd';
#完全没有使用上索引
结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
3）不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
CREATE INDEX idx_name ON emp(NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE 'abc%' #0.059
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = 'abc' #0.072
4）存储引擎不能使用索引中范围条件右边的列
CREATE INDEX idx_age_deptId_name ON emp(age,deptId,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ;
5）字段出现不等于索引失效
CREATE INDEX idx_name ON emp(NAME)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc'
6）出现is not索引失效
is not null 无法使用索引，但是is null可以使用
UPDATE emp SET age =NULL WHERE id=123456;
CREATE INDEX idx_age ON emp(age);
EXPLAIN SELECT * FROM emp WHERE age IS NULL
EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL
7）like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
CREATE INDEX idx_age_name ON emp(age,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name LIKE '%abc%';
8）字符串不加单引号索引失效
CREATE INDEX idx_name ON emp(NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name='123';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name=123;
总结
对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量吧这个字段放在索引次序的最后面
书写sql语句时，尽量避免造成索引失效的情况
6.关联查询索引优化
1.建表
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)));
2.案例
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
ALTER TABLE `book` ADD INDEX Y ( `card`);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#换成inner join
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;
#添加索引后
DELETE FROM class WHERE id<5;
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
#这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。
# 删除旧索引 + 新建 + 第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#可以看到左表的索引对优化没啥卵用
3.总结
1）保证被驱动表的join字段已经被索引
2）left join时，选择小表作为驱动表，大表作为被驱动表
3）inner join ，mysql会自己帮你吧结果集小的表选为驱动表
4）子查询尽量不要放在被驱动表，有可能使用不到索引
5）能够直接多表关联的尽量直接多表关联，不用子查询
7.子查询索引优化
尽量不要使用not in not exists
#取所有不为掌门人的员工，按年龄分组 ，每个年龄段多少人
CREATE INDEX idx_ceo ON dept(ceo);
EXPLAIN SELECT SQL_NO_CACHE age,COUNT(*) FROM emp a WHERE id NOT
IN(SELECT ceo FROM dept b2 WHERE ceo IS NOT NULL)
GROUP BY age HAVING COUNT(*)<10000;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp a LEFT JOIN dept b ON a.`id`=b.`CEO`
WHERE b.`id` IS NULL;
尽量不要使用not in 或者 not exists，用left outer join on xxx is null 替代
8.排序分组优化
1、排序优化
（1）无过滤，不索引
CREATE INDEX idx_age_deptid ON emp(age,deptid);
EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid LIMIT 10;
（2）顺序错，必排序
CREATE INDEX idx_deptid_age ON emp(deptid,age);
EXPLAIN SELECT id,NAME,age,deptid,empno FROM emp WHERE deptid=45 ORDER BY age;
有orderby的查询语句，必须将where后的字段和orderby后的字段都搞成一个索引或者只写where后面的字段，才会生效。
但是吧where和orderby一起写了效率高。
（3）方向反，必排序
2、索引选择
原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。 相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。
#查询 年龄为30岁的，且员工编号小于101000的用户，按用户名称排序
SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;
建立3个字段的索引
建立两个字段的索引，但是需要在范围和排序中选一个
3、文件排序
单路排序比双路排序要快
4、分组优化
group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引
5、覆盖索引
查询语句，不要写select * ，要写具体字段
十二，查询截取分析
1.是什么
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
2.怎么玩
1.说明
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
2.查看是否开启以及如何开启
默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，
可以通过设置slow_query_log的值来开启
使用set global slow_query_log=1;开启了慢查询日志只对当前数据库生效，
如果MySQL重启后则会失效。
全局变量设置，对当前连接不影响，因此
SET SESSION long_query_time =1;
SHOW VARIABLES LIKE '%long_query_time%';
如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
修改my.cnf文件，[mysqld]下增加或修改参数
slow_query_log和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件
slow_query_log =1 slow_query_log_file=/var/lib/mysql/atguigu-slow.log
关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）
3.什么样的sql会被记录到慢日志
这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，
命令：SHOW VARIABLES LIKE 'long_query_time%';
假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，
在mysql源码里是判断大于long_query_time，而非大于等于。
4.Case
1）记录慢sql并后续分析
vim /var/lib/mysql/izbp1ix6yixfm2zbnl72crz-slow.log
2）查询系统中当前有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
5.配置版
my.cnf
【mysqld】下配置：
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE
3.日志分析工具–mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
1.查看mysqldumpslow的帮助信息
2.工作常用参考
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more
4.SHOW PROCESSLIST
能干什么？
查询所有用户正在干什么
如果出现不顺眼的，kill [id]
4843
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
