一、MySQL常用命令

完整看完就能掌握MySQL中大部分的查询问题了

-- 查看所有数据库
show databases;

-- 选择使用哪个数据库
use <数据库名>;

-- 创建数据库
create database <数据库名>;

-- 查看所有数据表。（前提是要先选择使用哪个数据库）
show tables;

-- 查看表结构
desc <表名>;

-- 导入数据（*.sql文件）注意路径中不要有中文
source <.sql文件路径>

-- 查看MySQL版本号
select version();

-- 查看正在使用的数据库。
select database();

-- 中止sql语句的输入
\c

-- 退出MySQL
exit;

二、SQL分类

DQL：数据库查询语言

凡是带有select关键字的都是查询语句。

select 查

DML：数据库操作语言

凡是对表当中的数据进行增删改的都是DML。

insert 增

delete 删

update 改

DDL：数据定义语言

凡是带有create、drop、alter的都是DDL。DDL主要操作的是表的结构，不是表中的数据。

create 新建

drop 删除

alter 修改

TCL：事务控制语言

事务提交：commit

事务回滚：rollback

DCL：数据控制语言

授权：grant

撤销权限：revoke

三、查询语句

1.简单查询

select 和 from 都是关键字，字段名 和 表名 都是标识符。

对于SQL语句来说，是通用的。所有的SQL语句都以“;”结尾。SQL语句不区分大小写。

a>查询一个字段

select <字段名> from <表名>;

b>查询两个或多个字段

字段之间用“,”隔开。

select <字段名>,<字段名>,... from <表名>;

c>查询所有字段

1. 可以把每个字段都写上。
2. 可以使用“*”，代表全部。

在Java程序中不要写 "*" 号，因为它会先把星号转换为字段，这个过程也会占用一定的时间，所以效率比较低且可读性较差。

select * from <表名>;

d>给查询的列起别名

使用 as 关键字起别名。as 关键字可以省略。

注意：只是将显示的查询结果列名显示为别名，原表列名不变。记住：select语句是永远不会进行修改操作的。（只负责查询）

select deptno,dname as deptname from dept;
-- 或
select deptno,dname deptname from dept;

问：假设起别名的时候，别名里面想要加空格，怎么办？————用 单引号 将别名括起来。

select deptno,dname 'dept name' from dept;

2.列参和数学运算

-- 列可以参与运算
select ename, sal*12 from emp;
-- 也可以起别名，别名是中文就用单引号括起来
select ename, sal*12 as yearsal from emp;

3.条件查询

对表中数据进行条件筛选，将符合条件的数据取出来。

-- 语法格式
select <字段名> from <表名> where <条件>；

-- 查询工资等于800的数据
select empno, ename from emp where sql = 800;
-- 查询工资不等于800的数据
select empno, ename from emp where sql != 800;
-- 查询补助为null的数据（需要用is，不能用=）
select empno, ename sal, comm from emp where comm is null;
-- 查询工作岗位是manager和salesman的员工（in后面不是一个区间，而是具体的值）
select empno, ename, job from emp where job in ('manager', 'salesman');
-- 找出名字中含有“O”的
select ename from emp where ename like '%O%';
-- 找出名字中以“T”结尾的
select ename from emp where ename like '%T';
-- 找出名字中第二个字母是“A”的
select ename from emp where ename like '_A%';

4.排序

a>单个字段排序

-- 正序排序（从小到大排序）。加不加asc都行，因为默认是升序。
select * from car_information order by car_price (asc);
-- 倒序排序（从大到小排序）
select * from car_information order by car_price desc;

b>多个字段排序

查询所有车辆，要求按照车辆价格升序，如果价格一样的话，再按照品牌升序排序。

-- car_price在前，起主导作用。只有当car_price相等的时候，才会考虑car_brand。
select * from car_information order by car_price, car_brand;

5.综合案例

找出价格在5到10的车辆信息，要求按照价格降序排列。

select
	car_brand, car_price
from
	car_information
where
	car_price between 5 and 10
order by
	car_price desc;

四、数据处理函数

数据处理函数又被称为单行处理函数，对数据是一行一行处理的。

特点：一个输入对应一个输出

-- 例：转换小写
select lower(ename) as ename from emp;

五、分组函数

分组函数又被称为多行处理函数。

特点：输入多行，最终输出一行。

注意：分组函数在使用的时候必须先进行分组，然后才能用。如果没有对数据进行分组，默认整张表为一组。

-- 找出最高工资
select max(sal) from emp;
-- 找出最低工资
select min(sal) from emp;
-- 计算工资和
select sum(sal) from emp;
-- 计算平均工资
select avg(sal) from emp;
-- 计算员工数量
select count(ename) from emp;

分组函数需要注意的点

1. 分组函数自动忽略null，你不需要对null进行处理。（null不是一个值，是什么也没有）

2. 分组函数中的count(*)和count(具体字段)的区别：

   count(*)：统计表当中的总行数。（因为数据表中不存在全部字段都为null的数据）

   count(具体字段)：表示统计该字段下所有不为null的元素的总和。

3. 分组函数不能直接使用在where子句中。

   例：找出比最低工资高的员工信息。

   select ename, sal from emp where sal > min(sal);
   

   表面上看没问题，但是运行报错：错误使用分组函数。

   看完 《分组查询》 才能明白。

4. 所有的分组函数可以组合起来一起用。

   select sum(sal), min(sal), max(sal), avg(sal), count(*) from emp;
   

六、分组查询(⭐⭐⭐⭐⭐)

在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。

这时候就需要用到分组查询了

select
	...
from
	...
group by
	...

例：

​ 计算每个部门的工资和？

​ 计算每个工作岗位的平均薪资？

​ 找出每个工作岗位的最高薪资？

关键字执行顺序

将之前的关键字全部组合在一起，来看一下他们的执行顺序：

select
	...
from
	...
where
	...
group by
	...
order by
	...

顺序不能改变！！！

执行顺序是什么：

1. from
2. where
3. group by
4. select
5. order by

一个大问题

为什么分组函数不能直接使用在where后面？

-- 报错
select ename, sal from emp where sal > min(sal);

-- 成功
select sum(sal) from emp;

原因是：执行顺序。分组函数在使用的时候必须先进行分组，然后才能用。

从语法层面上看，where 的执行在 group by 前面，而 select 的执行在 group by 后面，select 的时候，group by 已经执行结束了。（group by没写不等于没有，没写表示整张表分为一组）

案例分析1

1. 找出每个岗位的工资和。

   select
   	job, sum(sal)
   from
   	emp
   group by
   	job;
   

   以上这个语句的执行顺序：

   1. 先从 emp 表中查询。
   2. 根据 job 字段进行分组
   3. 然后对每一组的数据进行 sum(sal)

a>提问：

下面的语句能否执行：

select ename, job, sum(sal) from emp group by job;

答：以上语句在MySQL中可以执行，但没有意义。sum(sal)按照job只能分出5组，可是ename有14个数据，ename对应不了sum(sal)。

在MySQL中可以执行，但是在Oracle中会报错。Oracle的语法比MySQL严格。

重要结论：

在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段、以及分组函数。其他的一律不能跟。

案例分析2

找出”每个部门，不同工作岗位“的最高薪资。

技巧：两个字段联合成一个字段看。（两个字段联合分组）

select
	deptno, job, max(sal)
from
	emp
group by
	deptno, job;

having子句

having可以对分完组之后的数据进一步过滤。

having 不能单独使用，having 不能代替 where，having 必须和 group by 联合使用。

案例分析

找出每个部门最高薪资，要求显示最高薪资大于3000的。

1. 找出每个部门最高薪资

   按照部门编号分组，求每一组最大值。

   select deptno, max(sal) from emp group by deptno;
   

2. 要求显示最高薪资大于3000的

   这题不能使用where，因为where的执行顺序在group by前面，所以只能使用having来过滤。

   select 
   	deptno, max(sal)
   from
   	emp
   group by
   	deptno
   having
   	max(sal) > 3000;
   

思考一个小问题

以上的sql语句执行效率是不是低？

答：是的，比较低，实际上可以这样考虑：先将大于3000的都找出来，然后再分组。

select
	deptno, max(sal)
from
	emp
where
	sal > 3000
group by
	deptno;

优化策略：

能够使用 where 过滤的，都尽量使用 where。

where 和 having 优先选择 where。

大总结

-- 关键字只能按照这个顺序来，不能颠倒
select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...

执行顺序：

1. from
2. where
3. group by
4. having
5. select
6. order by

最终案例

找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除manager岗位之外，要求按照平均薪资降序排列。

select
	job, avg(sal) as avgsal
from
	emp
where
	job != 'manager'
group by
	job
having
	avg(sal) > 1500
order by
	avgsal desc;

七、查询结果去重

把查询结果去除重复记录，原表数据不会被修改，只是查询结果去重。

需要使用一个关键字：distinct

select distinct job from emp;


-- 这样编写是错误的，语法错误。distinct只能出现在所有字段的最前方。
select ename, distinct job from emp;

-- distinct出现在job和deptno两个字段之前，表示两个字段联合起来去重。
select distinct job, deptno from emp;

案例分析

统计一下工作岗位的数量?

select count(distinct job) from emp;

八、连接查询(⭐⭐⭐⭐⭐)

什么是连接查询：

从一张表中单独查询，称为单表查询。

从emp表和dept表联合起来查询数据，从emp表中取员工姓名，从dept表中取部门名字。这种跨表查询，多张表联合起来查询数据，叫做连接查询。

连接查询的分类：

根据语法的年代分类：

• SQL92：1992年的时候出现的语法（几乎废了）
• SQL99：1999年的时候出现的语法

根据表连接的方式分类：

• 内连接：
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接：
  • 左外连接（左连接）
  • 右外连接（右连接）
• 全连接（很少使用）

笛卡尔积现象（连接查询底层）

查询每个员工所在部门名称？

1. 当两张表进行连接查询时，没有任何条件的限制会发生什么？

   select ename, dname from emp, dept;
   

   56 rows in set (0.00 sec)

   当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数是两张表条数的乘积，这种现象被称为：笛卡尔积现象

   怎么避免笛卡尔积现象呢？

   连接时加条件，满足这个条件的记录被筛选出来

   select
   	ename, dname
   from
   	emp, dept
   where
   	emp.deptno = dept.deptno;
   ================================
   -- 可以使用别名
   -- 为什么select里可以使用from里起的别名，因为select的执行顺序在from之后。
   select
   	e.ename, d.dname
   from
   	emp e, dept d
   where
   	e.deptno = d.deptno;
   

   思考：最终查询的结果条数是14条，但是匹配的过程中，匹配的次数减少了吗？
   答：没有，还是56次，只不过进行了四选一。匹配次数没有减少。

   表连接越多，效率就越低。因为笛卡尔积现象，匹配次数会很多。

内连接

内连接的特点：完成能够匹配上这个条件的数据查询出来。

等值连接

案例：查询每个员工所在部门名称，显示员工名和部门名？（使用SQL99语法）

-- 92语法
select
	e.ename, d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;

-- SQL92的缺点：结构不清晰，表的连接条件和后期进一步筛选的条件，都放在了where后面。 

===========================

-- 99语法，inner join = join
select
	e.ename, d.dname
from
	emp e
(inner) join
	dept d
on
	e.deptno = d.deptno;	-- 条件是等量关系，所以被称为等值连接。
	
-- SQL99的优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where条件。

条件是等量关系，所以被称为等值连接。

非等值连接

案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级。

select
	e.ename,e.sal,s.grade
from
	emp e
inner join
	salgrade s
on
	e.sal between s.losal and s.hisal;

条件不是一个等量关系，称为非等值连接。

自连接

案例：查询员工的上级领导，要求显示员工名和对应的领导名。

select 
	e.empno,e.ename,ee.ename mgrName
from
	emp e
inner join
	emp ee
on 
	e.mgr = ee.empno;

一张表看作两张表

外连接

右外连接

案例：显示员工和部门的关系

-- 内连接
select
	e.ename, d.dname
from
	emp e
join
	dept d
on 
	e.deptno = d.deptno;
===================================
-- 右外连接
select
	e.ename, d.dname
from
	emp e
right (outer) join
	dept d
on 
	e.deptno = d.deptno;

right表示什么：表示将join关键字右边的这张表看成是主表，然后去左边的表中查询匹配项。（left同理）

内连接是把所有能够匹配上的数据查询出来，而外连接会把主表中所有数据都查询出来，无论是否有匹配项。

思考：外连接的查询结果条数一定是 >= 内连接的查询结果条数？

​ 答：正确！

案例：查询每个员工的上级领导，要求显示所有员工的名字和领导名字

select 
	a.ename '员工名',b.ename '领导名'
from 
	emp a
left outer join
	emp b
on
	a.mgr = b.empno;

多表连接

语法：

select
	...
from
	a
join
	b
on
	a和b的连接条件
left join
	c
on
	a和c的连接条件
...

一条sql语句中，内连接和外连接可以混合。都可以出现。

案例：找出每个员工的部门名称以及薪资等级，要求显示员工名、部门名、薪资、薪资等级。

select
	e.ename '员工名',d.dname '部门名',e.sal '薪资', s.grade '薪资等级'
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

案例：找出每个员工的部门名称以及薪资等级，要求显示员工名、领导名、部门名、薪资、薪资等级。

select
	e.ename '员工名',ee.ename '领导名',d.dname '部门名',e.sal '薪资', s.grade '薪资等级'
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join
	emp ee
on
	e.mgr = ee.empno;

九、子查询

什么是子查询？

​ select语句中嵌套select语句，被嵌套的select语句称为子查询。

子查询都可以出现在哪里

select 
	..(select).
from
	..(select).
where
	..(select).

where中的子查询

案例：找出比最低工资高的员工姓名和工资？

1. 查出最低工资是多少？

   -- 第一步：查出最低工资是多少？
   select min(sal) from emp;
   

   --> 800

2. 找出工资 >800 的

   -- 第二步：找出 >800（第一步求出的最低工资）的
   select 
   	ename,sal 
   from 
   	emp 
   where 
   	sal > 800;
   

3. 合并

   -- 第三步：合并
   select 
   	ename, sal
   from
   	emp
   group by
   	empno
   having
   	sal > (select min(sal) from emp);
   

from中的子查询

注意：from后面的子查询，可以将子查询的查询结果当作一张临时表。

案例：找出每个岗位的平均工资的薪资等级。

1. 计算每个岗位的平均工资。

   select job, avg(sal) `avg` from emp group by job;
   

   --> 将结果看作一张真实存在的临时表 t。
   

2. 计算薪资等级，将t表和salgrade表进行连接，并且需要把子查询的结果起个别名。

   select
   	a.job, b.grade
   from
   	(select job, avg(sal) `avg` from emp group by job) a
   left join
   	salgrade b
   on
   	a.avg between losal and hisal;
   

select后的子查询

脱裤子放屁的感觉。可以用连接来实现。

十、union合并查询结果集

案例：查询工作岗位是MANAGER或SALESMAN的员工。

select * from emp where job = 'MANAGER' or job = 'SALESMAN';
-- 或
select * from emp where job in ('MANAGER','SALESMAN');

union：

select * from emp where job = 'MANAGER'
union
select * from emp where job = 'SALESMAN';

union的效率要高一些。对于表连接来说，每连接一次新表，匹配的次数满足笛卡尔积，成倍的翻。。。
但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。

a 连接 b 连接 c

a：10条

b：10条

c：10条

匹配的次数就是：10 * 10 * 10 = 1000

a 连接 b 一个结果：10 * 10 = 100

b 连接 c 一个结果：10 * 10 = 100

使用union的话是：100 + 100 = 200 次 （把乘法变成了加法）

十一、limit(⭐⭐⭐⭐⭐)

limit是将查询结果集的一部分取出来，通常使用在分页查询中。

百度默认：一页显示10条记录
分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差。可以一页一页翻页看。

案例：按照薪资降序，取出排名在前5名的员工。

select
	ename,sal
from 
	emp
order by
	sal desc
limit 
	0,5;

完整用法：limit startIndex,length
startIndex是起始下标，length是长度
缺省用法：limit length
length是长度，取前几条。

案例：取出工资排在[5-9]名的员工。

select
	ename, sal
from
	emp
order by
	sal desc
limit
	4,5;

分页

每页显示3条记录：
第1页：limit 0,3 [0 1 2]
第1页：limit 0,3 [3 4 5]
第1页：limit 0,3 [6 7 8]
第1页：limit 0,3 [9 10 11]

每页显示pageSize条记录：
第pageNo页：limit (pageNo-1)*pageSize,pageSize

再次总结执行顺序

1. from
2. where
3. group by
4. having
5. select
6. order by
7. limit