MYSQL2_mysql 查询数组当成结果集-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_44763047/article/details/142063323

一、子查询

1.介绍

子查询在我们查询方法中是比较常用的，通过子查询可以实现多表查询
子查询是指：将一个查询语句嵌套在另一个查询语句中
子查询可以在select、update、delete语句中使用，还可以进行多层嵌套

2.只出现在子查询中而没有出现在父查询中的表不能包含在输出列中

多层嵌套子查询的最终结果集只包含父查询（最外层的查询）的select 语句中出现的字段
子查询的结果集通常会作为其外层查询的数据源或用于条件判断

3.分类

按返回结果集分类
- 标量子查询（结果集只有一行一列）
  
  可以指定一个标量表达式的任何地方，都可以用一个标量子查询。
- 列子查询（结果集只有一列多行）
- 行子查询（结果集只有一行多列）
  
  行子查询可以用于父查询的FROM子句和WHERE子句中。
- 表子查询（结果集一般为多行多列）
  
  表子查询经常用于父查询的FROM子句中。
从定义上讲，每个标量子查询也是一个行子查询和一个列子查询，反之则不是；每个行子查询和列子查询也是一个表子查询，反之也不是。
按子查询出现的位置：
- select后面：
  
  仅仅支持标量子查询
- from后面：
  
  支持表子查询
- where或having后面：★
  
  标量子查询（单行子查询） √
  
  列子查询（多行子查询） √
  
  行子查询（用的较少）
- exists后面（相关子查询）
  表子查询

4.使用子查询原则

一个子查询必须放在圆括号中。
将子查询放在比较条件的右边以增加可读性。
子查询不包含 oreder by 子句。对一个 select 语句只能用一个 oreder by 子句，并且如果指定了它就必须放在主 select 语句的最后。
在子查询中可以使用两种比较条件：单行运算符(>, =, >=, <, <>, <=) 和多行运算符(IN, ANY, ALL)。

5.子查询可以嵌套在什么位置？

可以嵌套在where或having后面、from后面、select后面

select <字段> from <表名> where <子查询>
select <字段> from <表名> group by <字段> having 聚合函数 <子查询> 
select <字段> from <子查询> as <别名> where <查询条件>
select <子查询> from <表名> where <查询条件>

6.注意

常见错误写法

select * from (select * from emp);

这样写是会报错的，因为没有给子查询指定别名

正确写法

select * from (select * from emp) as t;

注意点

如果**<表名>嵌套的是子查询**，必须给表指定别名，一般会返回多行多列的结果集，当做一张新的临时表

7.where或having后面

特点

子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用

大于：>
小于：<
等于：= 
不等于：!=  <>
大于等于：>= 
小于等于：<=

列子查询，一般搭配着多行操作符使用

in：在指定项内，同 IN(项1,项2,…)。

any：与比较操作符联合使用，any关键字必须接在一个比较操作符的后面，表示与子查询返回的任何值比较为 TRUE ，则返回 TRUE 。

some：any 的别名，较少使用。

all：与比较操作符联合使用，ALL关键字必须接在一个比较操作符的后面，表示与子查询返回的所有值比较都为 TRUE ，则返回 TRUE 。

子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

语法

select <字段> from <表名> where <子查询>
select <字段> from <表名> group by <字段> having 聚合函数 <子查询>

标量子查询（单行）

一般搭配着单行操作符使用

大于：>
小于：<
等于：= 
不等于：!=  <>
大于等于：>= 
小于等于：<=

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

②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);


#返回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
);

#返回公司工资最少的员工的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
);

#查询最低工资大于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
);

非法使用标量子查询
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  salary #结果是1列多行，不能使用单行操作符 >
	FROM employees
	WHERE department_id = 50
);

列子查询（多行）

一般搭配着多行操作符使用

1.in：在指定项内，同 in(项1,项2,…)。
--括号里面放的是子查询语句
--in和=any结果一样

2.any：与比较操作符联合使用，any关键字必须接在一个比较操作符的后面。表示与子查询返回的任意一个值比较，只要符合条件，就返回True
例：a = 15，b=9
a >any(10,15,20)           可以用a >min(10,15,20)代替
b <any(10,15,20)		   可以用a <max(10,15,20)代替

3.some：any 的别名，较少使用。

4.all：与比较操作符联合使用，ALL关键字必须接在一个比较操作符的后面，表示与子查询返回的所有值比较，必须全部符合条件，才能返回True
例：a = 20, b=4
a >all(5,10,15)             可以用a >max(1,5,10)代替
a <all(5,10,15)				可以用a <min(1,5,10)代替

#返回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 (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);


#返回其它工种中比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<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';
或
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';


#返回其它工种中比job_id为‘IT_PROG’工种所有工资都低的员工的员工号、姓名、job_id 以及salary

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';
或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MIN( 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
);

8.from后面

特点

如果**<表名>嵌套的是子查询**，将子查询结果充当一张表，必须给表指定别名，一般会返回多行多列的结果集，当做一张新的临时表

语法

select <字段> from <子查询> as <别名> where <查询条件>

用法

常见错误写法

select * from (select * from emp);

这样写是会报错的，因为没有给子查询指定别名

正确写法

select * from (select * from emp) as t;

1.查询每个部门平均工资的工资等级
①查询每个部门平均工资
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;

9.select后面

特点

仅仅支持标量子查询

语法

select <子查询> from <表名> where <查询条件>

标量子查询

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

9.in / exists 子查询

in 和 exists的区别

in	exists
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；	用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；
适合外表大而内表小的情况	适合内表大而外表小的情况
无论哪个表大，用 not exists 都比 not in 速度快
1、A是表达式，B是子查询结果集2、若A在B里面，则返回True
方便理解，画个图

例子

先看表

dept表

emp表

比较运算符的栗子

查询部门是销售部的员工信息

select * from emp where dept_id = (select id from dept where name = "销售部")

查询部门不是销售部的员工信息

select * from emp where dept_id <> (select id from dept where name = "销售部")

in 的栗子

SQL分析
- 从 dept 表查询部门名字为销售部or财务部的部门 id
- 然后从 emp 表查询 depte_id 在上面 id 结果集的记录

select * from emp where dept_id in (select id from dept where name = "财务部" or name ="销售部")

可以看看子查询 sql 的查询结果

select id from dept where name = "财务部" or name ="销售部"

最终的 sql 其实是这样的

select * from emp where dept_id in (1,3)

not in 的栗子

select * from emp where dept_id not in (select id from dept where name = "财务部" or name ="销售部")

其实就是上面栗子结果集的取反

exists 栗子

SQL分析
- 从 dept 表中查询 id = 1 的记录，若有，exists 表达式则返回True
- 外层查询语句接收到 True 之后，对 emp 表进行查询，返回所有记录

select * from emp where exists(select * from dept where id = 1)

可以看看 exists 表达式里的子查询结果集

select * from dept where id = 1

可以看到，查询结果集不为空，所以 exists() 返回 true

最终的 sql 其实是这样的

select * from emp where true

exists + 其他查询条件的栗子

select * from emp where exists (select * from dept where id = 1) and dept_id = 2

10.子查询和多表查询对比

子查询的功能其实通过表连接（join）也可以完成
一般来说，表连接（内连接、外连接等）都可以用子查询查询，但反过来却不一定，有的子查询不能用表连接来替换
子查询比较灵活，适合作为查询的筛选条件
表连接更适合查看连接表之后的数据集

练习--------

1.查询和Zlotkey相同部门的员工姓名和工资
①查询Zlotkey的部门
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'

②查询部门号=①的姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name = 'Zlotkey'
)

2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

①查询平均工资
SELECT AVG(salary)
FROM employees

②查询工资>①的员工号，姓名和工资。
SELECT last_name,employee_id,salary
FROM employees
WHERE salary>(
	SELECT AVG(salary)
	FROM employees
);


3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
①查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

②连接①结果集和employees表，进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;



4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
①查询姓名中包含字母u的员工的部门
SELECT  DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'

②查询部门号=①中的任意一个的员工号和姓名
SELECT last_name,employee_id
FROM employees
WHERE department_id IN(
	SELECT  DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
);

5.查询在部门的location_id为1700的部门工作的员工的员工号
①查询location_id为1700的部门
SELECT DISTINCT department_id
FROM departments 
WHERE location_id  = 1700


②查询部门号=①中的任意一个的员工号
SELECT employee_id
FROM employees
WHERE department_id =ANY(
	SELECT DISTINCT department_id
	FROM departments 
	WHERE location_id  = 1700
);
-- =any和in效果一样

6.查询管理者是King的员工姓名和工资
①查询姓名为king的员工编号
SELECT employee_id
FROM employees
WHERE last_name  = 'K_ing'

②查询哪个员工的manager_id = ①
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
	SELECT employee_id
	FROM employees
	WHERE last_name  = 'K_ing'
);


7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
①查询最高工资
SELECT MAX(salary)
FROM employees

②查询工资=①的姓.名
SELECT CONCAT(first_name,last_name) "姓.名"
FROM employees
WHERE salary=(
	SELECT MAX(salary)
	FROM employees
);

11.分页查询（limit）

应用场景

实际工作中，我们的数据表数据肯定都是万级别的，如果每次都把所有数据查出来，不仅会增加查询的时间成本，还会给数据库服务器造成很大的压力
通过limit限制每次返回的数据量，可以有效减少查询时间和数据库压力

语法

初始位置从0开始
初始位置和记录数都必须为正整数

select 字段名 from 表 【join type】 join 表2 
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit 【offset】,size;

offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数

特点

①limit语句放在查询语句的最后

②公式

要显示的页数 page，每页的条目数size

select 查询列表
from 表
limit (page-1)*size,size;

size=10
page  
1	0
2  	10
3	20

用法

limit的三种用法
- 指定初始位置
- 不指定初始位置
- 结合offset使用

指定初始位置

limit 初始位置,记录数

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

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

不指定初始位置

limit 记录数

#查询前五条员工信息
SELECT * FROM  employees LIMIT 5;

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

结合offset使用

limit 记录数 offset 初始位置

和 limit 初始位置, 记录数用法一样，只是多了个offset，参数位置换了下而已

#查询前五条员工信息
SELECT * FROM  employees LIMIT 5 offset 1;

练习--------

1. 查询工资最低的员工信息: last_name, salary
①查询最低的工资
SELECT MIN(salary)
FROM employees

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

2.查询平均工资最低的部门信息
方式一：
①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

②查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep

③查询哪个部门的平均工资=②
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	) ag_dep
);

④查询部门信息
SELECT d.*
FROM departments d
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep

	)

);

#方式二：
①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

②求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

③查询部门信息
SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
);



3.查询平均工资最低的部门信息和该部门的平均工资
①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

②求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

③查询部门信息
SELECT d.*,ag
FROM departments d
JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1

) ag_dep
ON d.`department_id`=ag_dep.department_id;

4.查询平均工资最高的 job 信息
①查询最高的job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1

②查询job信息
SELECT * 
FROM jobs
WHERE job_id=(
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);


5.查询平均工资高于公司平均工资的部门有哪些?
①查询平均工资
SELECT AVG(salary)
FROM employees

②查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

③筛选②结果集，满足平均工资>①
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
	SELECT AVG(salary)
	FROM employees
);


6. 查询出公司中所有 manager 的详细信息.
①查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees

②查询详细信息，满足employee_id=①
SELECT *
FROM employees
WHERE employee_id =ANY(
	SELECT DISTINCT manager_id
	FROM employees
);

7.各个部门中 最高工资中最低的那个部门的 最低工资是多少
①查询各部门的最高工资中最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1

②查询①结果的那个部门的最低工资
SELECT MIN(salary) ,department_id
FROM employees
WHERE 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 
    department_id 
FROM
    employees 
GROUP BY department_id 
ORDER BY AVG(salary) DESC 
LIMIT 1 

②将employees和departments连接查询，筛选条件是①
SELECT last_name, d.department_id, email, salary 
FROM employees e INNER JOIN departments d 
ON d.manager_id = e.employee_id 
WHERE d.department_id = 
        (
        SELECT department_id 
        FROM employees 
        GROUP BY department_id 
        ORDER BY AVG(salary) DESC 
        LIMIT 1
        ) ;

作业-----------

一、查询每个专业的学生人数
SELECT majorid,COUNT(*)
FROM student
GROUP BY majorid;

二、查询参加考试的学生中，每个学生的平均分、最高分
SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;

三、查询姓张的每个学生的最低分大于60的学号、姓名
SELECT s.studentno,s.`studentname`,MIN(score)
FROM student s
JOIN result r
ON s.`studentno`=r.`studentno`
WHERE s.`studentname` LIKE '张%'
GROUP BY s.`studentno`
HAVING MIN(score)>60;

四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称
SELECT m.`majorname`,s.`studentname`
FROM student s
JOIN major m
ON m.`majorid`=s.`majorid`
WHERE DATEDIFF(borndate,'1988-1-1')>0
GROUP BY m.`majorid`;

五、查询每个专业的男生人数和女生人数分别是多少
SELECT COUNT(*),sex,majorid
FROM student
GROUP BY sex,majorid;

六、查询专业和张翠山一样的学生的最低分
①查询张翠山的专业编号
SELECT majorid
FROM student
WHERE studentname = '张翠山'

②查询编号=①的所有学生编号
SELECT studentno
FROM student
WHERE majorid=(
	SELECT majorid
	FROM student
	WHERE studentname = '张翠山'

)
③查询最低分
SELECT MIN(score)
FROM result
WHERE studentno IN(

	SELECT studentno
	FROM student
	WHERE majorid=(
		SELECT majorid
		FROM student
		WHERE studentname = '张翠山'

	)
)

七、查询大于60分的学生的姓名、密码、专业名
SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m ON s.majorid=  m.majorid
JOIN result r ON s.studentno=r.studentno
WHERE r.score>60;

八、按邮箱位数分组，查询每组的学生个数
SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email);

九、查询学生名、专业名、分数
SELECT studentname,score,majorname
FROM student s
JOIN major m ON s.majorid=  m.majorid
LEFT JOIN result r ON s.studentno=r.studentno


十、查询哪个专业没有学生，分别用左连接和右连接实现
#左
SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM major m
LEFT JOIN student s ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

#右
SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM student s
RIGHT JOIN  major m ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

十一、查询没有成绩的学生人数
SELECT COUNT(*)
FROM student s
LEFT JOIN result r ON s.`studentno` = r.`studentno`
WHERE r.`id` IS NULL

二、DCL 数据控制

创建用户

语法：
CREATE USER 用户名@地址 IDENTIFIED BY '密码';
例：
create user testuser1@localhost identified by '111';

授权(grant)

选择授权

语法：
GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP地址;
例：
grant create,alter,drop,insert,update,delete,select on test999.* to testuser1@localhost;

#刷新权限，否则不会生效
flush privileges;

授予所有权限

grant all privileges on *.* to '用户名'@'%' identified by '密码' with grant option;

撤销授权(revoke)

语法：
REVOKE权限1, … , 权限n ON 数据库.* FROM 用户名@IP地址;
例:
revoke create,alter,drop on test999.* to testuser1@localhost;

查看用户权限

语法：
SHOW GRANTS FOR 用户名;
例:
show grants for testuser1@locst;

删除用户

语法：
DROP USER 用户名;
例:
drop user testuser1@localhost;

三、常见函数

汇总

Mysql常用函数有哪几类

数值型函数
字符串型函数
日期时间函数
聚合函数
流程控制函数

数值型函数

某些简单的数学函数就不详细说了哦！

函数名称	作用
ABS	求绝对值
SQRT	返回非负数的二次方根
POW 和 POWER	两个函数的功能相同，返回X 的Y次方的结果值。
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号

字符串函数

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
SUBSTRING_INDEX	根据分隔符分割字符串，可以指定分隔符匹配次数
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
STRCMP	比较两个表达式的顺序
REGEXP	字符串是否匹配正则表达式
LOCATE	返回第一次出现子串的位置
INSTR	返回第一次出现子串的位置

日期时间函数

函数名称	作用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW	返回当前系统的日期和时间值
SYSDATE	返回当前系统的日期和时间值
DATE	获取指定日期时间的日期部分
TIME	获取指定日期时间的时间部分
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
YEAR	获取年份，返回值范围是 1970〜2069
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH 和 DAY	两个函数作用相同，获取指定日期是一个月中是第几天，返回值范围是1~31
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATEDIFF	返回两个日期之间的相差天数
SUBSTRING_INDEX	按照指定的分隔符划分字符串，可指定分隔符出现的次数

聚合函数

聚合函数比较简单，就不单独展开讲了哦，跟group by 结合的最多，可以看看这篇博客：https://www.cnblogs.com/poloyy/p/12868773.html

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

流程控制函数

函数名称	作用
IF	判断，流程控制
IFNULL	判断是否为空
CASE	搜索语句

1.流程控制函数

1.if

作用

根据表达式的某个条件或值结果来执行一组 sql 语句

Mysql 中怎么才算表达式？

结合文字，变量，运算符，甚至函数来组合
表达式可以返回 true、false、null

语法

if(expr,v1,v2)

expr：表达式，返回 true、false、null
v1：当expr = true时返回 v1
v2：当expr = false、null 时返回v2

例子

先看看emp表有什么数据

emp表

SQL分析

查询员工的name、在职状态
在职状态通过 if 函数去判断，返回【在职】or【离职】

select name,if(is_enable = 1,"在职","离职") "在职状态" from emp;

2.ifnull

作用

可以判断某个字段的值是否为 null，为 null 则返回预先设定的值；有点像编程里面的三元表达式（只可意会不可言传，不懂三元表达式就忽略吧~）

语法

ifnull(v1,v2)

如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2
v1、v2 均可以是值或表达式

例子

先看看emp表有什么数据

emp表

SQL分析

若员工的 dept_id 为 null ，则默认显示【无部门】

select id,name,ifnull(dept_id,"无部门") "部门" from emp;

3.case

作用

if 的高级版，类似Java 里面的 switch … case
通过条件表达式匹配 case 对应的值，然后执行对应的操作

语法

只有一个条件表达式

CASE
    WHEN <条件1> THEN <操作>
    WHEN <条件2> THEN <操作>
    ...
    ELSE <操作>
END;

将 <表达式> 的值逐一和每个 when 跟的 <值> 进行比较
如果跟某个<值>想等，则执行它后面的 <操作> ，如果所有 when 的值都不匹配，则执行 else 的操作
如果 when 的值都不匹配，且没写 else，则会报错

例子

先看看emp表有什么数据

根据部门id进行判断部门名称

SELECT name,dept_id,
CASE
        dept_id 
        WHEN 0 THEN
        "实习生" 
        WHEN 1 THEN
        "销售部" 
        WHEN 2 THEN
        "信息部" 
        WHEN 2 THEN
        "财务部" ELSE "没有部门" 
    END AS "部门" 
FROM
    emp;

可搜索 case 的语法

CASE
    WHEN <条件1> THEN <操作>
    WHEN <条件2> THEN <操作>
    ...
    ELSE <操作>
END;

每个 <条件> 都是独立的，可以用 and 来连接多个查询条件
不同 <条件> 是互不关联且互不影响的

可搜索 case例子

每个条件都是不一样的，可以任意指定查询条件，可简单可复杂

select name,dept_id,
case 
when dept_id=1 and leader != 0 and is_enable !=0 then "销售部在职员工"
when dept_id =2 and leader = 0 then "信息部大佬"
when leader = 8 then "任我行大佬"
else "啥都不是"
end
from emp;

重点

记得结尾要加 end ！！！！

2.字符串函数

1.substring

作用

截取指定范围的字符串，学过Java的同学，对这个应该不陌生，字符串函数也有一个substring，作用一样哈

语法

SUBSTRING(s，n，len)

s：指定字符串
n：起始位置，从1开始
len：截取的长度
**注意：**非字符串类型也是可以截取的，譬如 int整数、datetime日期等

例子

#从1开始。长度为2
select substring(1234,1,2);
结果：12

select substring("帅哥啊",1,2);
结果：帅哥

2.substring_index

作用

按照指定的分隔符划分字符串，可指定分隔符出现的次数

语法

SUBSTRING_INDEX(str, delim, count)

**str：**需要操作的字符串
**delim：**分隔符
**count：**匹配 delim 出现的次数，可正数可负数

例子一

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1);   # www
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -1);   # com

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);   # www.mysql
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);   # mysql.com

如果是负数则从右边开始寻找 delim，正数则是左边
如果是1、-1，则返回第一个 delim 之前的字符串
如果>1、< -1，则还会返回前面匹配到的 delim

例子二

假设有三个 IP：127.0.0.1、192.128.0.15、255.255.255.255，要分别取每一个号段的值并返回

sql 语句

SELECT ip, 
　　SUBSTRING_INDEX(ip,'.',1) AS part1, 
　　SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',2),'.',-1) AS part2, 
　　SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-2),'.',1) AS part3, 
　　SUBSTRING_INDEX(ip,'.',-1) AS part4  
FROM IPS;

执行结果

+-----------------+-------+-------+-------+-------+
| ip              | part1 | part2 | part3 | part4 |
+-----------------+-------+-------+-------+-------+
| 127.0.0.1       | 127   | 0     | 0     | 1     |
| 192.128.0.15    | 192   | 128   | 0     | 15    |
| 255.255.255.255 | 255   | 255   | 255   | 255   |
+-----------------+-------+-------+-------+-------+

中间的号段需要重复调用 SUBSTRING_INDEX，因为第一次调用的时候还是会返回两个号段

例子三

可以说是一个面试题，解法有多种，那么如果用 SUBSTRING_INDEX 要如何编写呢？

sql 语句

SELECT YEAR,
    SUBSTRING_INDEX( GROUP_CONCAT( amount ), ",", 1 ) AS m1,
    SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT( amount ), ",", 2 ),",", -1 ) AS m2,
    SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT( amount ), ",", - 2 ),",", 1 ) AS m3,
    SUBSTRING_INDEX( GROUP_CONCAT( amount ), ",", - 1 ) AS m4 
FROM
    tests 
GROUP BY
YEAR

执行结果

2.replace

作用

将某些字符串替换成新的字符串，学过Python的同学，对这个应该不陌生，字符串函数也有一个replace，作用一样哈

语法

REPLACE(s，s1，s2)

s：指定字符串
s1：需要替换掉的字符串
s2：新的字符串
**注意：**非字符串类型也是替换的，譬如 int整数、datetime日期等

例子

select replace("abcd","ab","啊啊"); # 啊啊cd

select replace(12341,1,"--"); # --234--

select replace("帅哥啊啊啊啊","啊",111); # 帅哥111111111111

3.length

作用

返回字符串的字节长度
**注意：**使用 uft8 编码字符集时，一个汉字是 3 个字节，一个数字或字母是一个字节

语法

length(str)

例子

select length("帅哥"); # 6

select length(12345); # 5

select length("2020-05"); # 7

4.concat

作用

连接多个字符串

语法

CONCAT(sl，s2，...)

可以连接多个哦
若任何一个参数为NULL，则最终也返回NULL

例子

SELECT CONCAT('MySQL','5.7'); # MySQL5.7

SELECT CONCAT('MySQL',NULL); # NULL

5.reverse

作用

将字符串反转，即顺序取反

语法

reverse(s)

例子

SELECT REVERSE('hello'); # olleh

SELECT REVERSE(123456); # 654321

6.strcmp

作用

比较两个字符串的顺序是否完全一致
**注意：**完全一致的意思是字符串里的每个字符、顺序都一致

语法

STRCMP(expr1,expr2)

expr 就是两个需要比较的字符串
若expr1 小于 expr2 ，则返回 -1
若expr1 大于 expr2 ，则返回 1
其他情况则返回 0（如相等）

例子

SELECT STRCMP('text', 'text2'); # -1
SELECT STRCMP('text2', 'text'); # 1
SELECT STRCMP('text', 'Text');  # 0

SELECT strcmp(123, 123);        # 0
SELECT strcmp(123, 122);        # 1
SELECT strcmp(123, 124);        # -1

SELECT strcmp('abc', 'Abc');    # 0
SELECT strcmp('abc', 'abb');    # 1
SELECT strcmp('abc', 'abd');    # -1

7.trim

作用

删除字符串左右两侧的空格

语法

TRIM(s)

例子

SELECT TRIM('  bar   '); # bar

拓展

用过 Python 的 trim 函数的小伙伴应该都知道，其实我们可以指定删除的符号
同样 Mysql 的 trim 函数也可以指定删除的符号，只是默认是空格

trim 的高级语法

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

{BOTH | LEADING | TRAILING} ：指删除的范围， BOTH 是前后都删除， LEADING 是只删除前面， TRAILING 是只删除后面
remstr ：指定删除的符号，默认是空格，不指定就是空格

高级语法例子

SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); # barxxx

SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); # bar

SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); # barx

最后

当然啦，平时用的最多还是删除空格，所以简单格式的 trim 一定要会用，然后掌握高级格式的 trim 即可！

8.left

作用

返回字符串 str 中最左边的 len 个字符；如果任何参数为NULL，则返回NULL。

语法

LEFT(str,len)

例子

SELECT LEFT('foobarbar', 5); # fooba

SELECT LEFT(1234567890, 5); # 12345

SELECT LEFT(NULL, 5); # NULL

9.right

作用

返回字符串 str 中最右边的 len 个字符；如果任何参数为NULL，则返回NULL。

语法

RIGHT(str,len)

例子

SELECT RIGHT('foobarbar', 5); # arbar

SELECT RIGHT(1234567890, 5); # 67890

SELECT RIGHT(NULL, 5); # NULL

10.lower

作用

将所有字符串更改为小写，然后返回

语法

LOWER(str)

例子

SELECT LOWER('QUADRATICALLY'); # quadratically

SELECT LOWER('ABc123asdBBB');  # abc123asdbbb

11.upper

作用

将所有字符串更改为大写，然后返回

语法

UPPER(str)

例子

SELECT UPPER('Hey'); # HEY

SELECT UPPER('hello world'); # HELLO WORLD

SELECT UPPER(NULL); # NULL

12.locate

作用

返回第一次出现子串的位置，有两种语法格式

语法一

LOCATE(substr,str)

返回 substr 在 str 中第一次出现的位置

例子

SELECT LOCATE('bar', 'foobarbar'); # 4
        
SELECT LOCATE('xbar', 'foobar'); # 0

语法二

LOCATE(substr,str,pos)

返回 substr 在 str 中第一次出现的位置，从 pos 位置开始搜索

例子

SELECT LOCATE('bar', 'foobarbar', 5); # 7

SELECT LOCATE('bar', 'foobarbar', 7); # 7

SELECT LOCATE('bar', 'foobarbar', 8); # 0

13.instr

作用

返回第一次出现子串的位置，和 locate 一样的作用，只是函数内部参数顺序调过来了而已~哈哈哈

语法

INSTR(str,substr)

是不是…似曾相识…对！没错！和 LOCATE(substr,str) 一样的参数，只是参数顺序反过来了！
返回 substr 在 str 中第一次出现的位置

例子

SELECT INSTR('foobarbar', 'bar');  # 4 

SELECT INSTR('xbar', 'foobar'); # 0

14.insert

作用

也是用来替换字符串的

语法

INSERT(str,pos,len,newstr)

**str：**指定字符串
**pos：**开始被替换的位置
**len：**被替换的字符串长度
**newstr：**新的字符串
**总结：**替换掉 str 范围为 [ pos, pos+len ] 的字符串
**注意：**如果 pos > str 长度以内，则返回 str 不会被替换；如果 len > str 剩余字符串的长度，则将 str 剩下所有字符都替换成 newstr

例子

SELECT INSERT('Quadratic',3,4,'What');#QuWhattic

SELECT INSERT('Quadratic',-1,4,'What');#Quadratic

SELECT INSERT('Quadratic',3,100,'What');#QuWhat

3.数值函数

1.mod

作用

求余数，和%一样

语法

mod(n,m)
n mod m
n % m

返回n除以m的余数，当然推荐直接%，方便快捷

例子

SELECT MOD(234, 10); # 4

SELECT 253 % 7; # 1

SELECT MOD(29,9); # 2

SELECT 29 MOD 9; #2

2.ceiling

作用

向上取整，ceil 函数一样效果哦

语法

CEILING(X)
CEIL(X)

返回不小于X的最小整数值

例子

SELECT CEILING(1.23); # 2

SELECT CEIL(-1.23); # -1

3.floor

作用

向下取整，返回整数

语法

FLOOR(X)

返回不大于X的最大整数值

例子

SELECT FLOOR(1.23); # 1

SELECT FLOOR(-1.23); # -2

4.round

作用

四舍五入，有两种格式哦

语法一

ROUND(X)

四舍五入返回整数

例子

SELECT ROUND(-1.23); # -1

SELECT ROUND(-1.58); # -2

SELECT ROUND(1.58); # 2

语法二

ROUND(X,D)

将 X 四舍五入到第 D 小数位
D 可取最大值为 30
若 D = 0，则小数点左一位变成 0

例子

SELECT ROUND(1.298, 1); # 1.3

SELECT ROUND(1.298, 0); # 1

SELECT ROUND(23.298, -1); # 20

SELECT ROUND(.12345678901234567890123456789012345, 35); # 0.123456789012345678901234567890

5.sign

作用

返回参数的符号

语法

SIGN(X)

返回 X 的符号标志，负数 = -1，整数 = 1，零 = 0``
**注意：**也可以传字符串，中文 = 0，负数开头 = -1，正数开头 = 1，字母开头 = 0

例子

SELECT SIGN(-32); # -1

SELECT SIGN(0); # 0

SELECT SIGN(234); # 1

SELECT SIGN("-1s"); # -1

SELECT SIGN("1s1"); # 1

SELECT SIGN("中文"); # 0

6.rand

作用

生成随机数

语法

RAND()

随机生成 0 - 1的浮点数
如果要指定指定范围的随机整数的话，需要用这个公式 FLOOR(i + RAND() * j)

例子

select rand();

# 生成 7 - 12的随机数
SELECT FLOOR(7 + (RAND() * 5));

7.abs

作用

求绝对值

语法

ABS(X)

x：指定一个数值

例子

SELECT ABS(2);#2

8.sqrt

作用

返回非负数的二次方根

语法

SQRT(X)

x：指定一个数值，如果数值负数，则返回null

例子

SELECT SQRT(4);#2

SELECT SQRT(20);#4.47213595499958

9.pow

作用

返回X 的Y次方的结果值。power和pow结果一样的

语法

POW(X,Y)
POWER(X,Y)

X和Y：指定数值

例子

 SELECT POW(2,2);#4
 
  SELECT POW(2,-2);#0.25

4.日期函数

1.curdate

作用

以字符串或数字形式使用该函数，以 ‘YYYY-MM-DD’ 或 YYYYMMDD 格式返回当前日期

**特殊注意，**以下几种写法的返回值是一样的

curdate()
current_date()
current_date

语法

CURDATE()

例子

SELECT CURDATE(); # '2008-06-13'

SELECT CURDATE() + 0; # 20080613

SELECT CURRENT_DATE(); # '2008-06-13'

SELECT CURRENT_DATE; # '2008-06-13'

2.curtime

作用

以字符串或数字形式使用该函数，以 ‘hh:mm:ss’ 或 Yhhmmss格式返回当前时分秒

**特殊注意，**以下几种写法的返回值是一样的

curtime()
current_time()
current_time

语法

CURTIME([fsp])

fsp：可以指定 0 - 6 位的秒精度；不写就只返回时分秒，不会精确到毫秒

例子

SELECT CURTIME(); # 11:51:57

SELECT CURTIME() + 0; # 115157

SELECT CURRENT_TIME; # 11:51:57

SELECT CURRENT_TIME(); # 11:51:57

SELECT CURTIME(6); # 11:51:57.429206

3.now

作用

以字符串或数字形式使用该函数，以 ‘YYYY-MM-DD hh:mm:ss’ 或 YYYYMMDDhhmmss 格式返回当前日期和时间

注意：要和 sysdate 函数区分开来，后面介绍时讲区别

**特殊注意，**以下几种写法的返回值是一样的

now()
current_timestamp()
current_timestamp

语法

NOW([fsp])

fsp：可以指定 0 - 6 位的秒精度；不写就只返回时分秒，不会精确到毫秒

例子

SELECT now(); # 2020-05-16 12:02:06

SELECT now() + 0; # 20200516120206

SELECT CURRENT_TIMESTAMP; # 2020-05-16 12:02:06

SELECT CURRENT_TIMESTAMP(); # 2020-05-16 12:02:06

SELECT now(6); # 2020-05-16 12:02:06.126904

4.datediff

作用

返回两个指定的日期之间相差的天数

语法

DATEDIFF(expr1,expr2)

返回 expr1 - expr2 的相差天数
expr 可以是具体的日期，也可以是日期表达式（即日期函数）
计算仅使用 expr 的日期部分，不会管时分秒部分

例子

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); # 1

SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31'); # 31

5.sysdate

作用

和 now 函数作用大同小异
以字符串或数字形式使用该函数，以 ‘YYYY-MM-DD hh:mm:ss’ 或 YYYYMMDDhhmmss 格式返回当前日期和时间

语法

SYSDATE([fsp])

fsp：可以指定 0 - 6 位的秒精度；不写就只返回时分秒，不会精确到毫秒

sysdate 和 now 的区别

sysdate() 返回执行时间
now() 返回 sql 语句开始执行的时间

啥意思？看下面例子

例子

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |

可以看到两个 now，和两个 sysdate 之间都有 sleep 两秒

但是两个 now 返回的时间都是一样的，因为它们在同一条 sql，返回的是 sql 执行的时间

而两个 sysdate 返回的时间刚好相差两秒，因为它们返回的是函数执行的时间

6.month

作用

返回指定日期的月份，1月至12月的范围为1到12

语法

MONTH(date)

date：可以是指定的具体日期，也可以是日期表达式

例子

SELECT MONTH('2008-02-03'); # 2

SELECT MONTH(now()); # 5

SELECT MONTH(sysdate()); # 5

7.dayofweek

作用

返回日期在本周的星期几索引，星期天 = 1，星期一 = 2，星期六 = 7

语法

DAYOFWEEK(date)

date：可以是指定的具体日期，也可以是日期表达式

例子

SELECT DAYOFWEEK('2008-02-03'); # 1

SELECT DAYOFWEEK(now()); # 7

SELECT DAYOFWEEK(sysdate()); # 7

8.dayofyear

作用

返回日期在本年的第几天，范围为1到366。

语法

DAYOFYEAR(date)

date：可以是指定的具体日期，也可以是日期表达式

例子

SELECT DAYOFYEAR('2008-02-03'); # 34

SELECT DAYOFYEAR(now()); # 137

SELECT DAYOFYEAR(sysdate()); # 137

9.dayofmonth

dayofmonth 的作用

返回日期在本月的第几天，范围为1到31

语法

DAYOFMONTH(date)

date：可以是指定的具体日期，也可以是日期表达式

例子

SELECT DAYOFMONTH('2008-02-03'); # 3

SELECT DAYOFMONTH(now()); # 16

SELECT DAYOFMONTH(sysdate()); # 16

10.week

作用

返回指定日期是一年中的第几周，返回值的范围是否为 0~53 或 1~53

语法

WEEK(date)

date：可以是指定的具体日期，也可以是日期表达式

例子

SELECT WEEK('2008-02-03'); # 5

SELECT WEEK(now()); # 19

SELECT WEEK(sysdate()); # 19

11.year

作用

返回指定日期的年份，在1000到9999之间

语法

YEAR(date)

date：可以是指定的具体日期，也可以是日期表达式

例子

SELECT YEAR('1987-01-01'); # 1987

SELECT YEAR(sysdate()); # 2020

SELECT YEAR(now()); # 2020

12.monthname

作用

返回指定日期的月份的全名**（默认：英文）**
名称使用的语言由lc_time_names系统变量的值控制

语法

MONTHNAME(date)

date：可以是指定的具体日期，也可以是日期表达式

例子

SELECT MONTHNAME('1987-01-01'); # January

SELECT MONTHNAME(sysdate()); # May

SELECT MONTHNAME(now()); # May

13.dayname

作用

返回指定日期在本周周几的全名**（默认：英文）**
名称使用的语言由lc_time_names系统变量的值控制

语法

DAYNAME(date)

date：可以是指定的具体日期，也可以是日期表达式

例子

SELECT DAYNAME('1987-01-01'); # Thursday

SELECT DAYNAME(sysdate()); # Sunday

SELECT DAYNAME(now()); # Sunday

14.date

作用

返回指定日期时间的日期部分

语法

DATE(expr)

例子

SELECT DATE('1987-01-01 16:00:00'); # 1987-01-01

SELECT DATE(CURTIME()); # 2020-05-17

SELECT DATE(CURRENT_TIMESTAMP); # 2020-05-17

15.time

作用

返回指定日期时间的时间部分

语法

TIME(expr)

例子

SELECT TIME('1987-01-01 16:00:00'); # 16:00:00

SELECT TIME(CURTIME()); # 11:45:39

SELECT TIME(CURRENT_TIMESTAMP); # 11:45:39

16.time_to_sec

作用

将指定时间转换为秒

语法

TIME_TO_SEC(time)

time：传入时间，如果传入了日期部分，也不会管，只将时间部分转换成秒
**重点：**是指将传入的时间转换成距离当天00:00:00的秒数，00:00:00为基数，等于 0 秒

例子

SELECT TIME_TO_SEC('1987-01-01 00:39:38'); # 2378

SELECT TIME_TO_SEC(CURRENT_TIME); # 42564

SELECT TIME_TO_SEC(CURTIME()); # 42564

17.sec_to_time

作用

和 time_to_sec 作用相反，将秒转换成时间（时、分、秒）

语法

SEC_TO_TIME(seconds)

seconds：传入秒数
**重点：**是指将传入的时间转换成距离当天00:00:00的秒数，00:00:00为基数，等于 0 秒

例子

SELECT SEC_TO_TIME(2378); # 00:39:38

SELECT SEC_TO_TIME(2378) + 0; # 3938

四、MySQL正则表达式

1.正则查询的语法格式

<列名> regexp '正则表达式'

2.常用的正则表达式

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头的字符串	book、big、banana、bike
$	匹配文本的结束字符	‘st$’ 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	‘b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配零个或多个在它前面的字符	‘f*n’ 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
<字符串>	匹配包含指定字符的文本	‘fa’ 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]’ 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串{n,}	匹配前面的字符串至少 n 次	‘b{2}’ 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串 {n,m}	匹配前面的字符串至少 n 次，至多 m 次	‘b{2,4}’ 匹配最少 2 个，最多 4 个 b	bbb、bbbb

先看看product表有什么数据

这里没有截全哈，因为数据比较多

栗子一：^

select * from product where product_name regexp '^2018';

栗子二：$

select * from product where product_name regexp '潮$';

先看看emp表有什么数据

后面再解释下为啥又换表了

栗子三： *、+

**po*：**查询 name 字段包含字母 p ，且 p 后面出现字母 o 的记录，而 * 可以表示0个字符，代表不出现

select * from emp where name regexp 'po*';

**po+：**查询 name 字段包含字母 p ，且 p 后面出现字母 o 的记录，但 + 表示至少出现1个字符

select * from emp where name regexp 'po+';

栗子四：[]

下面两种写法是一样的，用 , 隔开每个字符，可能可读性更高

select * from emp where name regexp '[p,s]';
select * from emp where name regexp '[ps]';

栗子五：[^]

**注意：**这里的^是取反，不是开头的意思哦！不要混淆

查询 id >=10 且开头非字母 p 的记录

select * from emp where id >=10 and  name regexp '^[^p]';

为啥中途换表

因为，我发现正则表达式并不是对所有中文都生效，举个下面的栗子

select * from emp where name regexp '[小]';

可以看到，name 字段需要匹配到一个【小】才应该被返回，但是除了红框以外的数据都被返回了，有问题有问题…

那为什么会这样呢？

原来，是因为 regexp 不支持多字节匹配，说白了，就是不支持中文编码
要想查询中文，最好通过 like 关键字进行模糊匹配啦

当然啦，也不是没有解决办法

只需要用小括号()把中文括起来就行了

select * from emp where name regexp '^(小)';

但，这种写法在 [ ] 里面还是不起作用

select * from emp where name regexp '[(小)]';

所以啊，还是推荐用 like 模糊匹配中文字符吧！而且日常工作中也完全够用啦！

五、存储引擎

引擎依赖的因素

MySQL 的版本
服务器在开发时如何被配置
启动选项

查询当前服务器引擎

show engines \G

Mysql默认使用的是InnoDB引擎

mysql> show engines \G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

常用的存储引擎

MyISAM

MyISAM 存储引擎是 MySQL 最常用的引擎。

特征：

使用三个文件表示每个表：
- 格式文件存储表结构的定义（ mytable.frm）
- 数据文件存储表行的内容（ mytable.MYD）
- 索引文件存储表上索引（ mytable.MYI）
灵活的 AUTO_INCRE MENT 字段处理
可被转换为压缩、只读表来节省空间

InnoDB

InnoDB 存储引擎是 MySQL 的缺省引擎。

特征

每个 InnoDB 表在数据库目录中以 .frm 格式文件表示
InnoDB 表空间 tablespace 被用于存储表的内容
提供一组用来记录事务性活动的日志文件
用 COMMIT 提交、 SAVEPOINT 及 ROLLBACK 回滚支持事务处理
提供全 ACID 兼容
在 MySQL 服务器 崩溃后提供自动恢复
多版本（ MVCC ）和行级锁定
支持外键及引用的完整性，包括 级联删除和更新

MEMORY

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。
MEMORY 存储引擎以前被称为 HEAP 引擎。

特征

在数据库目录内，每个表均以 .frm 格式的文件表示。
表数据及索引 被存储在内存中。
表级锁机制。
不能包含 TEXT 或 BLOB 字段。

存储引擎的使用

创建表时指定

语法

CREATE TABLE 表名称(
	字段名称 数据类型……
) ENGINE = 引擎名称;

例子

CREATE TABLE user_content(
	id INT,
	str VARCHAR(50)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

查看当前表的引擎

show create table 表名;

mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                               |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `Sno` int(255) NOT NULL COMMENT '学号',
  `Sname` varchar(255) DEFAULT NULL COMMENT '姓名',
  `Sage` int(255) DEFAULT NULL COMMENT '年龄',
  `Ssex` varchar(255) DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`Sno`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT         |

修改引擎

语法

ALTER TABLE 表名称 ENGINE = 引擎名称;

例子

ALTER TABLE user_info ENGINE = MyISAM;

选择合适的存储引擎

MyISAM 表最适合于大量的数据读而少量数据更新的混合操作。 MyISAM 表的另一种适用情形是使用压缩的只读表。
如果查询中包含较多的数据更新操作，应使用 InnoDB 。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
可使用 MEMORY 存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

六、事务

什么是事务

一个事务就是一个完整的业务逻辑（业务流程），是一个最小的工作单元，不可再分。
说白了一个事务就是完成一件事。

例子

假设转账，从A账户向B账户中转账10000。将A账户的钱减去10000，B账户的钱加上10000

这就是一个完整的事务逻辑。

以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。

和事务有关的语句

只有DML语句中的insert、delete、update和事务有关，其他都无关系。

因为只有以上的三个语句是数据库表中数据进行增、删、改的。

只要你的操作一旦涉及到数据的增、删、改，那么就一定要考虑事务。

思考题

假设所有的业务，只要一条DML语句就能完成，还有必要存在事务机制吗？

正是因为做某件事的时候，需要多条DML语句共同联合起来才能完成，所以需要事务的存在，如果任何意见负载的事都能一条DML语句搞定，那么事务则没有存在的价值了。

到底什么是事务呢？

说到底，一个事务其实就是多条DML语句同时成功或者同时失败
事务就是批量的DML语句同时成功或者同时失败。

事务如何实现的？

事务是怎么做到多条DML语句同时成功或同时失败的呢？

InnoDB 存储引擎：提供一组用来记录事务性活动的日志文件
```
事务开启了:

insert

insert

insert

delete

update

update

update
事务结束了!
```
在事务的执行过程中，每一条DML的操作都会记录到"事务性活动的日志文件”中。
在事务执行过程中，我们可以提交事务，也可以回滚事务。
- 提交事务
  
  清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中
  
  提交事务标志着事务的结束，并且是一种全部成功的结束
- 回滚事务
  
  将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件
  
  回滚事务标志着,事务的结束。并且是一种全部失败的结束

怎么提交事务？怎么回滚事务？

提交事务

commit

回滚事务

rollback

回滚永远都是只能回滚到上一次的提交点(commit)。因为做了commit后表就定型了，就回滚不回去了。

事务自动提交模式

在Mysql中事务是自动提交的。每执行一条DML语句，则提交一次
如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。
如果语句执行失败，事务将自动回滚，并取消该语句的结果。
在自动提交模式下，仍可使用 START TRANSACTION 语句来显式地启动事务。这时，一个事务仍可包含
多条语句，直到这些语句被统一提交或回滚。

自动提交可以关闭

mysql> SET AUTOCOMMIT = OFF
mysql> SET AUTOCOMMIT = ON
或
mysql> SET SESSION AUTOCOMMIT = OFF
mysql> SET SESSION AUTOCOMMIT = ON

创建表

create
table user(
id int (11) primary key not null auto_increment ,
username varchar(30),
password varchar(30)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

insert into user (username,password) values ('zhangsan','123');

查看数据

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
+----+----------+----------+
1 row in set (0.00 sec)

回滚事务演示

回滚成功

创建表

create
table user2(
id int (11) primary key not null auto_increment ,
username varchar(30),
password varchar(30)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看

mysql> select * from user2;
Empty set (0.00 sec)

开启提交事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

添加数据

mysql> insert into user2 (username,password) values ('zhangsan','123');
Query OK, 1 row affected (0.00 sec)

查看数据

mysql> select * from user2;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
+----+----------+----------+
1 row in set (0.00 sec)

回滚

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

再次查看，会发现没有刚才新增的数

mysql> select * from user2;
Empty set (0.00 sec)

提交事务演示

做了commit后如果回滚的话是回不到commit之前的。

回滚失败

创建表

create
table user3(
id int (11) primary key not null auto_increment ,
username varchar(30),
password varchar(30)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看

mysql> select * from user3;
Empty set (0.00 sec)

开启提交事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

添加数据

mysql> insert into user3 (username,password) values ('zhangsan','123');
Query OK, 1 row affected (0.00 sec)

提交

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查看数据

mysql> select * from user3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
|  2 | lisi     | 123      |
+----+----------+----------+

删除

mysql> delete from user3 where id=1;
Query OK, 1 row affected (0.00 sec)

回滚

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

再次查看，是回滚不到commit之前的

mysql> select * from user3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | lisi     | 123      |
+----+----------+----------+
1 row in set (0.00 sec)

总结

这种自动提交实际上是不符合我们的开发习惯，因为一个业务通常是需要多条DML语句共同执行才能完成的,为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条。
```
事务开启了(start transaction;):

insert

insert

insert

delete

update

update

update

事务结束了!(commit;)
```

事务的4个特性

原子性

说明事务是最小的工作单元。不可再分。

一致性

所有事务要求，在同一个事务当中，所有操作必须同时成功；或者同时失败，以保证数据的一致性。

隔离性

A事务和B事务之间具有一定的隔离。

A事务在操作一张表的时候，另一个事务B也操作这张表会怎样？

持久性

事务最终结束的一个保障。事务提交，就相当于把没有保存到硬盘上的数据保存到了硬盘上。

事务的隔离性

A教室和B教室中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的隔离级别。这道墙越厚,表示福离级别就越高。

隔离级别

读未提交 read uncommitted（最低级别）

事务A可以读取到事务B未提交的数据。这种现象称为脏读现象。

没有提交就读到数据
这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步!（读已提交）

读已提交 read committed

事务A只能读取到事务B提交的数据。

提交之后才能读到数据
解决了脏读现象。
这种级别存在不可重复读取数据问题

在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。在事务开启之后,第一次读到的数据是3条，当前事务还没有结束，可能第三次再读取的时候，读到的数据是4条，3不等于4称为不可重复读取。
这种隔离级别是比较真实的数据,每一次读到的数据是绝对的真实。
oracle数据库默认的隔离级别是: read comaitted

可重复读 repeatable read

事务A开启之后,不管是多久，每一次在事务A中读取到的数据都是一致的，即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变,这就是可重复读。

提交之后也读不到，永远读取的都是开启事务时的数据

早上9点开启的事务，到了晚上9点读取的数据还是早上9点的数据
解决了不可重复读取的问题
这种级别存在的问题是出现幻象读

每一次读取的数据都是幻象，不够真实。
Mysql默认隔离级别就是可重复读

序列化/串行化 serializable（最高级别）

这是最高隔离级别,效率最低。解决了所有的问题。

这种隔离级别表示事务排队。不能并发!

不能并发：对一个事务不能同时处理
每一次读取到的数据都是最真实的，并且效率是最低的。

隔离级别演示

查看事务隔离级别

查看会话级的当前隔离级别

只对当前会话有效

mysql> select @@tx_isolation;
或：
mysql> select @@session.tx_isolation;

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

查看全局级的当前隔离级别

对所有会话有效

mysql> select @@global.tx_isolation;

设置隔离级别

全局级

#设置为读为提交
mysql> set global transaction isolation level read uncommitted;

会话级

mysql> set transaction isolation level read uncommitted;
或：
mysql> set session transaction isolation level read uncommitted;

读未提交演示

新建表

create table t_user(
id int(10),
name varchar(30)
);

查看隔离级别

Mysql默认级别是可重复读

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

设置全局隔离级别

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+

退出mysql

exit;

演示

事务A和事务B各自开启一个cmd窗口
事务A先执行。事务A先登录，事务B再登录，以此类推

事务A	事务B
mysql -u root -p	mysql -u root -p
输入密码	输入密码
use student;	use student;
start transaction;	start transaction;
insert into t_user values(1,‘大君’);	select * from t_user;

#事务B查询结果：这边会显示没有commit的数据
mysql> select * from t_user;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 大君   |
+------+--------+
1 row in set (0.00 sec)

读已提交演示

新建表

create table t_user2(
id int(10),
name varchar(30)
);

设置全局隔离级别

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+

退出msyql

exit;

演示

事务A和事务B各自开启一个cmd窗口
事务A先执行。事务A先登录，事务B再登录，以此类推

未提交操作，事务B查看

事务A	事务B
mysql -u root -p	mysql -u root -p
输入密码	输入密码
use student;	use student;
start transaction;	start transaction;
insert into t_user2 values(1,‘大君’);	select * from t_user2;

#此时未提交，事务B是查不到记录的

mysql> select * from t_user2;
Empty set (0.00 sec)

做提交操作，事务B进行查看

事务A	事务B
commit;	select * from t_user2;

#提交后是能查看到结果的
mysql> select * from t_user2;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 大君   |
+------+--------+
1 row in set (0.00 sec)

可重复读演示

新建表

create table t_user3(
id int(10),
name varchar(30)
);

先插入一条数据

insert into t_user3 values(1,'大君');

设置全局隔离级别

mysql> set global transaction isolation level  repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

退出msyql

exit;

演示

事务A和事务B各自开启一个cmd窗口
事务A先执行。事务A先登录，事务B再登录，以此类推

先查看当前数据

事务A	事务B
mysql -u root -p	mysql -u root -p
输入密码	输入密码
use student;	use student;
start transaction;	start transaction;
	select * from t_user3;

#事务B查询结果
mysql> select * from t_user3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 大君   |
+------+--------+
1 row in set (0.00 sec)

现在事务A再添加几条数据，commit后看事务B能不能看到新增的数据。预期结果是看不到的

事务A	事务B
insert into t_user3 values(2,‘迪丽热巴’),(3,‘古力娜扎’),(4,‘李英爱’);
commit;	select * from t_user3;

#事务B查询结果，并没有事务A新增的那几条数据
mysql> select * from t_user3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 大君   |
+------+--------+
1 row in set (0.00 sec)

序列化演示

新建表

create table t_user4(
id int(10),
name varchar(30)
);

设置全局隔离级别

mysql> set global transaction isolation level  serializable;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| SERIALIZABLE          |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

退出msyql

exit;

演示

事务A和事务B各自开启一个cmd窗口
事务A先执行。事务A先登录，事务B再登录，以此类推

事务A	事务B
mysql -u root -p	mysql -u root -p
输入密码	输入密码
use student;	use student;
start transaction;	start transaction;
insert into t_user4 values(1,‘大君’)	select * from t_user4;

此时事务A没有错commit操作，如果事务B查询的会主语卡顿状态。等会就会报错

QQ截图20210403232948

QQ截图20210403233058

当事务A做了提交操作后，事务B会立马查询到结果

事务A	事务B
commit	select * from t_user4;

#查询结果
mysql> select * from t_user4;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 大君   |
+------+--------+
1 row in set (3.87 sec)

3.87 sec：表示事务B查询结果所花费的时间。之前事务A没提交，所以有延迟。

六、索引

什么是索引

索引是在数据库的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

例子

表名：t_user

id	name	email	address
1	zhangsan
2	lisi
3	wangwu
4	dajun
5	dilireba

select * from t_user where name='dajun';

以上的这条SQL语句会去name字段上扫描。如果name字段上没有索引或者没有给name字段创建索引，MySQL会进行全扫描，会将name字段上的每一个值都比对一遍。效率较低。

MySQL查询方式

全表扫描
根据索引检索

索引的实现原理

索引的实现原理就是缩小扫描的范围，避免全表扫描。

例子

id(PK)	name
100	a
120	b
99	c
55	d
101	e

提醒1：在任何数据库当中主键上都会自动添加索引对象。另外在mysql中，一个字段上如果有unique约束的话，也会自动创建索引对象。

提醒2：在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。

提醒3：在mysql中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。在MyISAM存储引擎中，索引存储在一个.MYI文件中，在InnoDB存储引擎中，索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）

select * from t_user where id=101;

mysql发现id字段上有索引对象，所以会通过索引对象idindex进行查找。

索引原理

注意

索引也是需要排序的，因为只有排序了才会有区间查找这一说（缩小扫描范围其实就是扫描某个区间）。
并且这个索引的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层就是一个自平衡的二叉树！在mysql当中索引是一个B-Tree数据结构。
遵循左小右大原则存放。采用中序遍历方式遍历数据。
在mysql当中，主键、unique字段都会自动添加索引的

什么时候添加索引？

数据量庞大

数据量庞大需要测试，因为每一个硬件环境不同。
该字段经常出现在where后面，以条件形式存在，经常被扫描
该字段很少DML操作（insert、delete、update）。因为DML操作后，索引需要重新排序

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

建议通过主键查询，通过unique约束的字段进行查询，效率较高。

索引的应用

创建

语法

create index 索引名 on 表名(字段名);

删除

语法

drop index 索引名 on 表名;

查看sql语句是否使用了索引进行检索

mysql> explain select * from jobs where job_title = 'Accountant';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | jobs  | ALL  | NULL          | NULL | NULL    | NULL |   19 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

rows字段显示19条记录，说明是全表扫描。type为ALL也表示全表扫描。

type类型	解释
null	–
system	const的特例，仅返回一条数据的时候。
const	查找主键索引，返回的数据至多一条（0或者1条）。属于精确查找
eq_ref	查找唯一性索引，返回的数据至多一条。属于精确查找
ref	查找非唯一性索引，返回匹配某一条件的多条数据。属于精确查找、数据返回可能是多条
range	查找某个索引的部分索引，一般在where子句中使用 < 、>、in、between等关键词。只检索给定范围的行，属于范围查找
index	查找所有的索引树，比ALL要快的多，因为索引文件要比数据文件小的多。
ALL	不使用任何索引，进行全表扫描，性能最差。

从下到上逐渐变好，使用的索引至少要达到range 级别。

给job_title字段添加索引，然后查询看

mysql> create index title_index on jobs(job_title);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from jobs where job_title = 'Accountant';
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | jobs  | ref  | title_index   | title_index | 73      | const |    1 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

查看后显示为1条记录，type为ref

索引的失效

模糊查询

什么时候会失效？

select * from emp where enmae like "%T";

因为模糊匹配中以“%”开头了，所以尽量避免模糊查询的时候以“%”开头

使用or

使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一边的索引也会失效。可以使用union代替or，效率较高。

select* from jobs where job_title = 'President' or job_id = 'AC_ACCOUNT';

select* from jobs where job_title = 'President' union select * from jobs where job_id = 'AC_ACCOUNT';

使用复合索引，没有使用左侧的列查找

复合索引

两个字段或更多的字段联合起来添加一个索引，叫做复合索引。
```
create index titlemin_index on jobs(job_title,min_salary);
```

查询左侧列

mysql> explain select * from jobs where job_title = 'President';
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys              | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | jobs  | ref  | title_index,titlemin_index | title_index | 73      | const |    1 | Using where |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

可以看出使用的索引

查询右侧列

mysql> explain select * from jobs where min_salary = 20000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | jobs  | ALL  | NULL          | NULL | NULL    | NULL |   19 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

可以看出没有使用索引

where中索引列参加了运算

mysql> explain select * from jobs where max_salary + 1 = 12000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | jobs  | ALL  | NULL          | NULL | NULL    | NULL |   19 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

在where中所有列使用了函数

mysql> explain select * from jobs where lower(job_title) = 'President';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | jobs  | ALL  | NULL          | NULL | NULL    | NULL |   19 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

索引的分类

单一索引

一个字段上添加索引

复合索引

两个或多个字段添加索引

主键索引

主键上添加索引。主键会自动添加索引

唯一索引

具有unique约束的字段上添加索引。唯一会自动添加索引

注意

唯一比较弱的字段上添加索引用处不大

七、视图

什么视图？

视图是一种根据查询（也就是 SELECT 表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。
视图有时也被成为“虚拟表”。
视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。
相对于从基表中直接获取数据，视图有以下好处：
访问数据变得简单
可被用来对不同用户显示不同的表的内容

用来协助适配表的结构以适应前端现有的应用程序

视图作用

提高检索效率
隐藏表的实现细节【面向视图检索】

应用

创建

语法

create view 视图名 as DQL语句

例子

create view locations_view as select * from locations;

删除

语法

drop view 视图名 as select语句

例子

drop view locations_view;

注意

只有DQL语句才能以view的形式创建。

视图可以做什么

可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作

实际中的作用

假设有一条非常复杂的sql语句，而这条sql语句需要在不同的位置上反复使用。每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办?

可以把这条复杂的sql语句以视图对象的形式新建。

在需要编写这条sql语句的位置直按使用视图对象，可以大大简化开发。并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的sql语句。

八、数据库设计三范式

什么是数据库设计范式

数据库的设计依据。教你怎么进行数据库表的设计。

有哪些范式？

设计数据库表的时候，按照以下的范式进行，可以避免表中数据的冗余，空间的浪费。

第一范式

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

不符合第一范式的示例

学生编号	学生姓名	联系方式
1001	张三	zs@gmail.com,1359999999
1002	李四	ls@gmail.com,13699999999
1001	王五	ww@163.net,13488888888

存在问题

最后一条记录和第一条重复（不唯一，没有主键）
联系方式字段可以再分，不是原子性的

设计满足第一范式的表

学生编号(pk)	学生姓名	联系方式
1001	张三	zs@gmail.com	1359999999
1002	李四	ls@gmail.com	1359999999
1003	王五	ww@163.net,1	13488888888

pk代表主键
关于第一范式，每一行必须唯一，也就是每个表必须有主键。这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。

第二范式（多对多）

建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

符合了第一范式，但不符合第二范式

学生编号(pk)	教师编号(pk)	学生姓名	教师姓名
1001	001	张三	王老师
1002	002	李四	赵老师
1003	001	王五	王老师
1001	002	张三	赵老师

学生编号教师编号，两个字段联合做主键，复合主键(PK:学生编号+教师编号)
虽然的表满足了第一范式。但是满足第二范式吗?
- 不满足，"张三"依赖1001，"王老师依赖001，显然产生了部分依赖。
- 产生部分依赖有什么缺点?
  
  数据冗余了。空间浪费了。"张三重复了，"王老师"重复了。

设计满足第二范式的表

使用三张表来表示多对多的关系。多对多三张表，关系表有两个外键

学生表

学生编号(pk) 学生姓名
1001 张三
1002 李四
1003 王五
教师表

教师编号(pk) 教师姓名
001 王老师
002 赵老师
学生教师关系表

id(pk) 学生编号(fk) 教师编号(fk)
1 1001 001
2 1002 002
3 1003 001
4 1001 002
- fk代表外键
- 这张表现在是一个学生对多个老师，一个老师对多个学生

学生编号(pk)	学生姓名
1001	张三
1002	李四
1003	王五

教师编号(pk)	教师姓名
001	王老师
002	赵老师

id(pk)	学生编号(fk)	教师编号(fk)
1	1001	001
2	1002	002
3	1003	001
4	1001	002

第三范式（一对多）

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

分析下表是否满足第一范式、第二范式

学生编号(pk)	学生姓名	班级编号	班级名称
1001	张三	01	一年一班
1002	李四	02	一年二班
1003	王五	03	一年三班
1004	马六	03	一年三班

以上表的设计是描述:班级和学生的关系。很显然是1对多关系! 一个教室中有多个学生-
分析以上表是否满足第一范式?

满足第一范式,有主键
分析以上表是否满足第二范式?

满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。
分析以上表是否满足第三范式?

第三范式要求：不要产生传递依赖!

一年一班依赖01,01依赖1001，产生了传递依赖

不符合第三范式的要求。产生了数据的冗余。一年三班和一年三班重复了。

设计满足第三范式的表

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

班级信息表：一

班级编号(pk) 班级名称
01 一年一班
02 一年二班
03 一年三班
学生表：多

学生编号(pk) 学生姓名班级编号(fk)
1001 张三 01
1002 李四 02
1003 王五 03
1004 马六 03

班级编号(pk)	班级名称
01	一年一班
02	一年二班
03	一年三班

学生编号(pk)	学生姓名	班级编号(fk)
1001	张三	01
1002	李四	02
1003	王五	03
1004	马六	03

总结表的设计

一对多

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

多对多

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

一对一

一对一放到一张表中不就行了吗?为啥还要拆分表?

在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。

id	账号	密码	姓名	邮箱	地址
1	zhngsan	123	张三	zhngsan@163.com	西安
2	lisi	123	李四	lisi@164.com	杭州

一对一怎么设计?

一对一，外键唯一。

登录信息表

id(pk) 账号密码
1 zhngsan 123
2 lisi 123
用户信息表

id(pk) 姓名邮箱地址 login_id(fk+unique)
100 张三 zhngsan@163.com 西安 1
200 李四 lisi@164.com 杭州 2

id(pk)	账号	密码
1	zhngsan	123
2	lisi	123

id(pk)	姓名	邮箱	地址	login_id(fk+unique)
100	张三	zhngsan@163.com	西安	1
200	李四	lisi@164.com	杭州	2

嘱咐

数据库设计三范式是理论上的。实践和理论有的时候有偏差

有的时候会拿冗余换执行速度，最终的目的都是为了满足客户的需求。因为在sql当中，表和表之间连接次数越多，效率越低。(笛卡尔积)

有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说,sql语句的编写难度也会降低。

面试的时候把这句话说上:他就不会认为你是初级程序员了!

九、实战练习

实战一

创建表、插入数据

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `id` int(11) NOT NULL COMMENT '员工编号',
  `name` varchar(255) CHARSET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `dept_id` int(11) NULL DEFAULT NULL COMMENT '部门编号',
  `leader` int(11) NULL DEFAULT NULL COMMENT '直属领导id',
  `is_enable` int(11) NULL DEFAULT NULL COMMENT '是否在职 1在职 0离职',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARSET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


INSERT INTO `emp` VALUES (1, '张三丰', 1, 0, 1);
INSERT INTO `emp` VALUES (2, '张无忌', 1, 1, 1);
INSERT INTO `emp` VALUES (3, '小龙女', 1, 1, 1);
INSERT INTO `emp` VALUES (4, '小白菜', 1, 3, 1);
INSERT INTO `emp` VALUES (5, '韦小宝', 2, 0, 1);
INSERT INTO `emp` VALUES (6, '令狐冲', 2, 0, 1);
INSERT INTO `emp` VALUES (7, '东方不败', 0, 8, 1);
INSERT INTO `emp` VALUES (8, '任我行', 3, 0, 1);
INSERT INTO `emp` VALUES (9, '李寻欢', 0, 8, 1);


DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` int(11) NOT NULL COMMENT '部门id',
  `name` varchar(255) CHARSET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARSET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `dept` VALUES (1, '销售部');
INSERT INTO `dept` VALUES (2, '信息技术部');
INSERT INTO `dept` VALUES (3, '财务部');
INSERT INTO `dept` VALUES (4, '有关部门');

题目列表

1、查询张姓员工的员工信息和所在部门信息。

2、查询张三丰管理了几个员工

3、查询出所有实习员工（实习员工无部门信息）

4、查询每个部门有多少个员工，并打印部门名字、部门里的所有员工名字

答案列表

-- 1、查询张姓员工的员工信息和所在部门信息。
select * from emp e left join dept d on e.dept_id=d.id where e.`name` like "%张%";

-- 2、查询张三丰管理了几个员工
select e1.name,count(1) as "手下数量" from emp e1 inner join emp e2 on e1. id = e2.leader where e1.`name` = "张三丰";

-- 3、查询出所有实习员工（实习员工无部门信息）
select * from emp e where e.dept_id not in (select id from dept)
select * from emp e left join dept d on e.dept_id = d.id where d.name is null

-- 4、查询每个部门有多少个员工，并打印部门名字、部门里的所有员工名字
select d.id,d.name,count(1),group_concat(e.name) from emp e right join dept d on e.dept_id = d.id group by d.id

实战二

创建表、插入数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `Cno` int(11) NOT NULL COMMENT '课程编号',
  `Cname` varchar(255) charset utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程名称',
  `Tno` int(11) NULL DEFAULT NULL COMMENT '老师编号',
  PRIMARY KEY (`Cno`) USING BTREE
) ENGINE = InnoDB charset = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '数学', 1);
INSERT INTO `course` VALUES (2, '语文', 2);
INSERT INTO `course` VALUES (3, '英文', 1);

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `Sno` int(11) NOT NULL COMMENT '学号',
  `Cno` int(11) NOT NULL COMMENT '课程编号',
  `score` int(255) NULL DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`Sno`, `Cno`) USING BTREE
) ENGINE = InnoDB charset = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (1, 1, 99);
INSERT INTO `sc` VALUES (1, 2, 80);
INSERT INTO `sc` VALUES (1, 3, 50);
INSERT INTO `sc` VALUES (2, 1, 70);
INSERT INTO `sc` VALUES (2, 2, 90);
INSERT INTO `sc` VALUES (3, 1, 90);
INSERT INTO `sc` VALUES (4, 1, 60);
INSERT INTO `sc` VALUES (4, 2, 50);
INSERT INTO `sc` VALUES (4, 3, 40);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `Sno` int(255) NOT NULL COMMENT '学号',
  `Sname` varchar(255) charset utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `Sage` int(255) NULL DEFAULT NULL COMMENT '年龄',
  `Ssex` varchar(255) charset utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`Sno`) USING BTREE
) ENGINE = InnoDB charset = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三丰', 108, '男');
INSERT INTO `student` VALUES (2, '李小龙', 20, '男');
INSERT INTO `student` VALUES (3, '小龙女', 17, '女');
INSERT INTO `student` VALUES (4, '白发魔女', 18, '女');
INSERT INTO `student` VALUES (5, '韦小宝', 19, '男');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `Tno` int(11) NOT NULL COMMENT '老师编号',
  `Tname` varchar(255) charset utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '老师名称',
  PRIMARY KEY (`Tno`) USING BTREE
) ENGINE = InnoDB charset = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '无崖子');
INSERT INTO `teacher` VALUES (2, '孤独求败');
INSERT INTO `teacher` VALUES (3, '洪七公');

SET FOREIGN_KEY_CHECKS = 1;

题目列表

1、查询年龄小于18岁的学员信息

2、查询无崖子授课信息

3、查询没有参与任意课程的学生信息

4、查询无崖子每个授课课程的学员人数统计 + 分组

5、查询张三丰数学成绩

6、查询出语文最高分【待完成】

7、查询没有参与语文考试的学生信息

8、查询语数外三门成绩的平均分

9、查询报名孤独求败老师课程的学生信息

10、没有报名孤独求败老师课程的学生信息

答案列表

#1 查询年龄小于18岁的学员信息
select * from student where Sage<18;

#2 查询无崖子授课信息
select * from teacher t join course c on c.Tno = t.Tno where t.Tname="无崖子";

#3 查询没有参与任意课程的学生信息
select * from student s left join sc  on s.Sno = sc.Sno where sc.score is null;

#4 查询无崖子每个授课课程的学员人数 统计 + 分组 
select t.Tname,c.Cname,count(1)  as "学员人数" from teacher t join course c on t.Tno = c.Tno join sc on sc.Cno = c.Cno group by sc.Cno HAVING t.Tname="无崖子";

#5 查询张三丰数学成绩
select s.Sname,c.Cname,sc.score from  student s join sc on s.Sno = sc.Sno join course c on c.Cno = sc.Cno where s.Sname="张三丰" and c.Cname="数学";

#6 查询出语文最高分【待完成】
select s.Sname,max(sc.score) as "分数" from sc join course c on sc.Cno = c.Cno join student s on s.Sno = sc.Sno where c.Cname="语文";

#7 查询没有参与语文考试的学生信息
select * from student s join sc on sc.Sno = s.Sno right join course c on c.Cno = sc.Cno and c.Cname = "语文" and sc.score is null;
select * from course  c join sc on c.Cno = sc.Cno and c.Cname="语文" right join student s on s.Sno = sc.Sno where sc.score is null;

#8 查询语数外三门成绩的平均分 
select c.Cname,avg(sc.score) from course c join sc on c.Cno = sc.Cno GROUP BY c.Cno;


#9 查询报名孤独求败老师课程的学生信息
select * from student s join sc on sc.Sno = s.Sno join  course c on c.Cno= sc.Cno join teacher t on t.Tno = c.Tno where t.Tname="孤独求败" ;
select * from teacher t join course c on c.Tno = t.Tno and t.Tname = "孤独求败" join sc on c.Cno = sc.Cno right join student s on s.Sno = sc.Sno where sc.score is not null;

#10 没有报名孤独求败老师课程的学生信息
select * from teacher t join course c on c.Tno = t.Tno and t.Tname = "孤独求败" join sc on c.Cno = sc.Cno right join student s on s.Sno = sc.Sno where sc.score is null;

实战三

创建表、插入数据

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE
IF
    EXISTS dept_;-- 部门表
DROP TABLE
IF
    EXISTS emp_;-- 部门表
SET FOREIGN_KEY_CHECKS = 1;

SELECT  @@FOREIGN_KEY_CHECKS;

CREATE TABLE dept_ ( DEPTNO INT PRIMARY KEY, -- 部门编号
DNAME VARCHAR ( 14 ), -- 部门名称
LOC VARCHAR ( 13 ) -- 部门地址
);
INSERT INTO dept_
VALUES
    ( 10, 'ACCOUNTING', 'NEW YORK' );
INSERT INTO dept_
VALUES
    ( 20, 'RESEARCH', 'DALLAS' );
INSERT INTO dept_
VALUES
    ( 30, 'SALES', 'CHICAGO' );
INSERT INTO dept_
VALUES
    ( 40, 'OPERATIONS', 'BOSTON' );
    
    
    
    
    
CREATE TABLE emp_ (
emp_NO INT PRIMARY KEY,-- 员工编号
ENAME VARCHAR ( 10 ),-- 员工名称
JOB VARCHAR ( 9 ),-- 工作
MGR DOUBLE,-- 直属领导编号
HIREDATE DATE,-- 入职时间
SAL DOUBLE,-- 工资
COMM DOUBLE,-- 奖金
DEPTNO INT,-- 部门号
FOREIGN KEY ( DEPTNO ) REFERENCES dept_ ( DEPTNO ) 
);


INSERT INTO emp_
VALUES
    ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20 );
INSERT INTO emp_
VALUES
    ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30 );
INSERT INTO emp_
VALUES
    ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30 );
INSERT INTO emp_
VALUES
    ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20 );
INSERT INTO emp_
VALUES
    ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30 );
INSERT INTO emp_
VALUES
    ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30 );
INSERT INTO emp_
VALUES
    ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10 );
INSERT INTO emp_
VALUES
    ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000, NULL, 20 );
INSERT INTO emp_
VALUES
    ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10 );
INSERT INTO emp_
VALUES
    ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30 );
INSERT INTO emp_
VALUES
    ( 7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100, NULL, 20 );
INSERT INTO emp_
VALUES
    ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30 );
INSERT INTO emp_
VALUES
    ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20 );
INSERT INTO emp_
VALUES
    ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10 );

问题列表

1、列出至少有三个员工的所有部门和部门信息。

2、列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称

3、列出职位为“CLERK”的姓名和部门名称，部门人数：

答案列表

1.列出至少有三个员工的所有部门和部门信息。

select * from dept_ where DEPTNO in (select DEPTNO from emp_ group by DEPTNO having count(1) >= 3)

2.列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称
select a.emp_NO,a.ENAME,(select DNAME from dept_ c where a.DEPTNO = c.DEPTNO) "部门名称" from emp_ a join emp_ b on a.MGR = b.emp_NO where a.HIREDATE < b.HIREDATE

3.列出职位为“CLERK”的姓名和部门名称，部门人数：
select group_concat(a.ENAME),b.DNAME,count(1) from emp_ a join dept_ b on a.DEPTNO = b.DEPTNO  where a.JOB = "CLERK" group by b.DEPTNO