常用SQL语句总结（二）

单表查询

简单查询

select * from t_emp;

select empno,ename,sal from t_emp;

列别名

select
empno,
sal * 12 as "income"
from t_emp;

数据分页(limit)

select ... from ... limit 起始位置，偏移量;

select empno,ename from t_emp limit 10,5; # 取从10开始5条数据

select empno,ename from t_emp limit 5; # 取从0开始5条数据，不写start默认为0

结果集排序(order by)

select ... from ... order by (列名 [ASC | DESC]) {n} ;

select empno,ename,sal,deptno from t_emp order by sal desc; 

select empno,ename,sal,deptno from t_emp order by sal;   # 默认ASC

select empno,ename,sal,hiredate
from t_emp
order by sal desc,hiredate desc;

select 
empno,ename,sal 
from t_emp
order by sal limit 0,5;

ps: 排序可以指定多个关键字字段，所有关键字如果都相同的话，最后会按照主键大小来排序

数据去重（distinct）

select distinct 字段 from ...;

select 
distinct job
from t_emp;

• distinct关键字仅支持查询一列数据，如果查询多列数据，该关键字就会失效
• distinct关键字仅能使用一次，且必须放到第一个查询字段前面

条件查询

select ... from ... where 条件 [and | or] 条件 ...;

select 
empno ,ename ,sal ,deptno 
from t_emp te 
where deptno = 10 and sal >= 2000;

WHERE语句中的条件运算会用到四种运算符：数学运算符，比较运算符，逻辑运算符，按位运算符

数学运算符

序号	表达式	意义	例子
1	+	加法	1 + 2
2	-	减法	3 - 1
3	*	乘法	2 * 8
4	/	除法	8 / 2
5	%	求模	10 % 3

比较运算符

序号	表达式	意义	例子
1	>	大于	age > 10
2	>=	大于等于	age >= 10
3	<	小于	age < 10
4	<=	小于等于	age <= 10
5	=	等于	age = 10
6	!=	不等于	age != 10
7	IN	包含	age IN (10,30)
8	IS NULL	为空	comm IS NULL
9	IS NOT NULL	不为空	comm IS NOT NULL
10	BETWEEN AND	范围	sal BETWEEN 2000 AND 3000
11	LIKE	模糊查询	ename LIKE “a%”
12	REGEXP	正则表达式	ename REGEXP “[a-zA-Z]{4}”

逻辑运算符

序号	表达式	意义	例子
1	AND	与	age > 12 AND sal < 2000
2	OR	或	age > 12 OR sal < 2000
3	NOT	非	NOT age = 12
4	XOR	异或	age > 12 XOR sal < 2000

按位运算符

序号	表达式	意义	例子
1	&	按位与	2 & 8
2	|	按位或	3 | 7
3	~	按位取反	~ 2
4	^	按位异或	2 ^ 5
5	<<	左移	1 << 2
6	>>	右移	55 >> 2

PS: WHERE子句中，条件执行的顺序是从左到右的，所以应该将索引条件，或者可以筛选记录最多的条件写在最左侧

聚合函数

SUM（求和）

select sum(sal) from t_emp;

MAX/MIN（最大值/最小值）

AVG（求平均值）

COUNT（统计数量）

数据分组

• 默认情况下，聚合函数是对全表范围的数据做统计
• GROUP BY 子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理
• 查询语句中如果包含group by 子句，那么select 子句中的内容就必须要遵守规定：select 子句中可以包含聚合函数，或者group by 子句的分组列，其余内容均不可以出现在select子句中

简单分组

select deptno, round(avg(sal)) from t_emp
group by deptno;

逐级分组

• 数据库支持多列分组条件，执行时候逐级分组

select deptno ,job ,count(*) ,avg(sal) 
from t_emp te 
group by deptno, job 
order by deptno;

分组结果再汇总

• 可以使用rollup 字段对分组结果再次汇总

select deptno, avg(sal), min(sal), max(sal)  
from t_emp te 
group by deptno with rollup;

HAVING子句

• 前面说到，where子句中不能使用聚合函数，所以提供了having子句来执行条件中的聚合函数，当然，having中的聚合函数不能和字段数据作比较，只能和常量数字作比较

select deptno 
from t_emp
where hiredate >= "1982-01-01"
group by deptno having count(*) >= 2;

• 除此之外，如果按照数字分组(这里的数字是select查询列的位置，从1开始)，Mysql也会依据SELECT子句中的列分组，having子句也可以照常使用

select deptno, count(*) 
from t_emp
group by 1 having deptno in (10,20);

联表查询

• 表连接分为两种：内连接和外连接
• 内连接是结果集中只保留符合连接条件的记录
• 外连接是不管符不符合条件，记录都要保留在结果集中

内连接语法

SELECT ... FROM 表1 JOIN 表2 ON 连接条件；

SELECT ... FROM 表1 JOIN 表2 WHERE 连接条件；

SELECT ... FROM 表1，表2 WHERE 连接条件；

## 其中，相同的表也可以做表连接

外连接语法

• 内连接只保存符合条件的记录，所以查询条件写在ON或者WHERE语句的结果中是相同的。但是在外连接里，条件写在WHERE子句里，不符合条件的记录是会被过滤掉的，而不是保留下来

SELECT ... FROM 表1 LEFT JOIN 表2 ON 连接条件；

SELECT ... FROM 表1 RIGHT JOIN 表2 ON 连接条件；

UNION关键字

UNION关键字可以将多个查询语句的结果集进行合并,UNION合并的前提是查询的字段要一致，不然无法合并

（查询语句）UNION（查询语句）UNION（查询语句）......

各种子句执行顺序

FORM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

子查询

• 子查询可以写在三个地方：WHERE子句，FROM子句，SELECT子句，但是仅建议写在FROM子句。因为写在WHERE和SELECT子句中的子查询，每条记录都会运行一遍，效率极其低下。

where子查询

select empno , ename , sal 
from t_emp
where sal >= (select avg(sal) from t_emp);

from子查询

select e.empno , e.ename , e.sal , t.avgsal
from t_emp e join
(select deptno,avg(sal) as avgsal from t_emp group by deptno) t
on e.deptno = t.deptno and e.sal >= t.avgsal;

select子查询

select 
	e.empno,
	e.ename,
	(select dname from t_dept where deptno = e.deptno)
from t_emp e;

多行子查询

• 单行子查询的结果集只有一条记录，多行子查询结果集有多行记录
• 多行子查询只能出现在WHERE子句和FROM子句中
• where子句中，可以使用IN，ALL，ANY，EXISTS关键字来处理多行表达式结果集的条件判断