SQL语句

LincZero大约 7 分钟

SQL语句

DQL数据查询语言

select * from demotable;，查看表数据

总概

SELECT标准示例

SELECT customer_id, last_name		-- 可为*
FROM customers  					-- 从表选择
WHERE customer_id = 1  				-- 筛选。布尔：()>NOT>AND>OR
ORDER BY first_name  				-- 按某列排序
LIMIT 10  							-- 仅显示前10条数据
 
--SELECT子句扩展（选择）
SELECT points + 10 AS points_factor	-- 构建新列。可输入表达式，若新列名有空格或中文可加引号
SELECT DISTINCT state  				-- 获得唯一值列表

DQL语句及子句顺序

执行顺序补充

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

SELECT扩展（跟在SELECT后的）

DISTINCT（去重）

select后增加关键字distinct即可
- 例如：SELECT DISTINCT state，获得唯一值列表
- 补充：DISTINCT只能出现在所有字段的最前面，后面接多个字段时为联合去重

单行处理函数

使用

select (points+10) as points_factor，可输入表达式，若列名有空格可加引号
select ifnull(comm,0) as comm from emp;，空处理函数

补充

SQL中有NULL参与运算则结果一定为NULL，但空处理函数除外

分组函数（聚合函数 / 多行处理函数）

使用

select sum(sal) from mytable;，求总和
select max(sal) from mytable;，找最高
select min(sal) from mytable;，找最低
select avg(sal) from mytable;，找平均
select count(*) from mytable;，找总数

补充

分组函数，别名聚合函数，或多行处理函数（输入多行，输出一行）
SQL中有NULL参与运算则结果一定为NULL，但分组函数会自动忽略NULL
分组函数不可直接在where子句中使用
- 举例：即select * from mytable where sal > avg(sal);错误。若要使用该功能，要使用嵌套查询
- 原因分析：执行顺序where > group by > select，在分组查询之前无法执行分组函数
分组函数一般都会和GROUP BY分组查询联合使用，这也是其被称为分组函数的原因。若无group by则整个表为一组

分组类子语句（结合分组函数使用）

GROUP BY（分组查询）

使用

group by，按照某个字段或某些字段分组
- select max(sal) from emp group by job;，案例：找出每个工作岗位的最高薪资

补充

==【大坑】==当有group by时，select后面只能跟 “分组的字段” 以及 “分组函数”，其他不能跟！！！
- 举例：不能select ename,max(sal),job from emp group by job;
- 第一个字段并不是最大值所对应的那一个！
- 该语句在MySQL中能执行但结果无意义，在Oracle中甚至会报语法错误
多字段分组查询
- 如group by deptno,job（注意：先后顺序分重要）

HAVING（分组过滤）

使用

having，对分组之后的数据进行再次过滤

补充

和where差不多，但执行顺序不同，WHERE > GROUP BY > HAVING
WHERE和HAVING的选用：优先使用WHERE，除非WHERE搞不掂

筛选类子语句

WHERE（条件筛选）

除了查询以外，修改数据也能用

运算符

()					-- 括号运算符
>,<,=,>=,<=,><,!=	-- 比较运算符
NOT,AND,OR			-- 逻辑运算符
IS NULL		-- 匹配空值
IN			-- 列表包含，配合()使用
BETWEEN		-- 等同于：>= and <=
LIKE		-- 模糊匹配，配合%和_使用。若匹配%或_则使用转义符'\'
REGEXP		-- 正则匹配

常用条件

WHERE birth_date >= '1990-01-01'  			 			-- 日期区间1
WHERE birth_date BETWEEN '1990-01-01' AND '2020-01-01' 	-- 日期区间2
WHERE phone IS NULL  									-- 匹配空值
WHERE state IN ('VA', 'FL', 'GA')						-- 列表包含
WHERE last_name LIKE 'b%'  								-- 模糊匹配，匹配b开头的对象，大小写无关
WHERE last_name LIKE 'b____'  							-- 模糊匹配，%任意匹配长度字符，下划线匹配单个字符

正则匹配

WHERE last_name REGEXP 'field'  						-- 正则匹配，可组合特殊字符建立复杂筛选
'field'		包含
'^field'	以开头
'field$'	以结尾
'aa|bb'		并行
'[abcd]e'	单字符并行
'[a-d]e'	并行优化

嵌套子查询

可以在where、from、select语句中使用子查询

where后：应用举例：找出薪资高于平均值的员工

WHERE client_id =(
	SELECT client_id
	FROM clients
	WHERE name = 'Myworks'
)

from后：应用情况：查询结构作为一张新表

select后：这个不太好理解，大概可以代替下连接查询

连接类子语句（多表关联查询）

连接查询

概念
- 实际开发中，大部分情况都不是从单表中查询数据，都是多表联合查询（考试也一般是多表联合查询）
- 实际开发中，一般一个业务会对应多个表
分类
- 根据年代划分
  - SQL92（旧），一些老的DBA可能还在使用这种语法
  - SQL99（新）
- 根据连接方式划分
  - 内连接（两张表没有主副之分，两张表平等）
    - 等值连接（连接条件是等量关系）
    - 非等值连接（连接条件是非等量关系）
    - 自连接（一张表看作两张表，自己连接自己）
  - 外连接（分主表副表，主要查询主表数据，捎带查询副表。但副表没有与主表匹配的数据时，自动模拟NULL与之匹配）
    - 左外连接（左连接）（表示左边的表是主表，左右连接的写法可互转换）
    - 右外连接（右连接）（表示右边的表是主表，左右连接的写法可互转换）
  - 全连接（不讲，很少用）
一些使用注意项
- 连接查询原理、以及笛卡尔积现象（笛卡尔乘积现象）
  - 例如：sekect ename,dname from emp,dept;
  - 说明：无条件限制时，则新表的数据量是两张表的记录的乘积。该现象为笛卡尔积现象
  - 避免：加条件过滤
  - 思考：避免笛卡尔积现象并不会减少记录的匹配次数，只不过现实的是有效记录，即不会提高查询效率
- 使用表的别名
  - 例如：sekect e.ename,d.dname from emp e,dept d;
  - 说明：表的别名的好处：执行效率高、可读性好

分类

内连接之等值连接

SQL92（太老，不用）

select
	e.ename,d.dname
from
	emp e,dept d
where
	e.deptno = d.deptno

SQL99（常用）

select
	e.ename,d.dname
from
	emp e
join			-- 这里可写成inner join，inner可省略，带inner目的是提高可读性
	dept d
on
	e.deptno = d.deptno

特点：连接条件是等量关系
案例：很常见，信息扩展
比较
- 看起来SQL92简单，但SQL99耦合度更低更清晰
- SQL99可以使用WHERE进行过滤，而SQL92需要在WHERE使用AND（即表的连接条件和WHERE条件分离）

内连接之非等值连接

特点：连接条件是非等量关系
案例：比如找出每个员工的工资等级（高中低）

内连接之自连接

特点：一张表看作两张表，自己连接自己
案例：找出每个员工的上级领导的名字

外连接

特点：全量查询，不会丢数据

内连接

select
	a.ename '员工', b.ename '领导'
from
	emp a
join			-- 也可写成inner join，inner可省略
	emp b
on
	a.mgr = b.empno;

左外连接

select
	a.ename '员工', b.ename '领导'
from
	emp a
left join		-- 表示左边是主表，也可写成left outer join，outer可省略
	emp b
on
	a.mgr = b.empno;

右外连接

select
	a.ename '员工', b.ename '领导'
from
	emp b
right join		-- 表示右边是主表，也可写成right outer join，outer可省略
	emp a
on
	a.mgr = b.empno;

三表联合查询

举例

select
	e.ename, d.name, s.grade
from
	emp e
join
	dept d
on
	e.deptno = de.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

结果编辑子语句

ORDER BY（排序）

ORDER BY state, first_name  		-- 主次关键词
ORDER BY 1, 2  						-- 可填列序，但应尽量避免
ORDER BY first_name ASC  			-- 升序排列（默认）
ORDER BY first_name DESC  			-- 降序排列，MySQL以外数据库对此支持不好
ORDER BY state DESC, first_name ASC	-- 组合

补充：order by的执行顺序在select之后，故可以对新列别名进行排序

UNION（查询结果相加）

应用举例：找出工作岗位是SALESMAN和MANAGER的员工

select ename,job from emp where job='MANAGER'
union
select ename,job from emp where job='SALESMAN';

LIMIT（限制）【MySQL特有】

注意：mysql中特有的，其他数据库没有，不通用。Oracle中有一个相同的机制叫rownum

用途：最大的用途就是分页查询，例如百度搜索每页显示10条数据

语法：limit [startIndex=0,] length，startIndex表示起始位置，length表示取几个

LIMIT 10  							-- 仅显示前10条
LIMIT 6, 3  						-- 跳过前6条/从序列6开始，显示后3条