sql经典实例_读书笔记

温故SQL以及数据库相关知识

1、检索记录

select * from emp  //检索所有列
where dep = 10  //选择出指定行
or comm is not null
or sal <= 2000 and dep=20//满足多个条件

select ename,dep,sal from emp //筛选出列
select sal as sale,com as commission from emp //缩写，创建别名

select * from (select sal sa salary,com as commission from emp
              ) x where salary < 5000 //在where子句中引用别名列，无法直接引用

将含有别名列的查询放入内嵌视图，就可以在外层查询中引用别名列。为什么要这么做
呢？ WHERE 子句会比 SELECT 子句先执行，就最初那个失败的查询例子而言，当 WHERE 子句
被执行时，SALARY 和 COMMISSION 尚不存在。直到 WHERE 子句执行完毕，那些别名列才会生
效。然而，FROM 子句会先于 WHERE 子句执行。如果把最初的那个查询放入一个 FROM 子句，其查询结果会在最外层的 WHERE 子句开始之前产生，这样一来，最外层的 WHERE 子句就能
“看见”别名列了。当表里的某些列没有被恰当命名的时候，这个技巧尤其有用

在本例中，内嵌视图的别名为 X。并非所有数据库都需要给内嵌视图取别名，
但对于某些数据库而言，确实必须如此。不过，所有的数据库都支持这一
点。
串联多列

使用 CONCAT 函数可以串联多列的值。在 DB2、Oracle 和 PostgreSQL 中，“||”是 CONCAT 函数的快捷方式，在 SQL Server 中则为“+”。

//在SELECT语句里使用条件逻辑

select ename,sal,
	case when sal <= 2000 then 'UNDERPAID'
 		when sal >= 4000 then 'OVERPAID'
 		else 'OK'
 	end as status
 from emp

CASE 表达式能对查询结果执行条件逻辑判断。你可以为 CASE 表达式的执行结果取一个别名，使结果集更有可读性。

限定返回行数
//MySQL 和 PostgreSQL使用 LIMIT 子句。
1 select *
2 from emp limit 5

//Oracle
//对于 Oracle 而言，通过在 WHERE 子句中限制 ROWNUM 的值来获得指定行数的结果集。
1 select *
2 from emp
3 where rownum <= 5

//SQL Server  使用 TOP 关键字限定返回行数。
1 select top 5 *
2 from emp
随机返回若干行记录
//MySQL 把内置函数 RAND 和 LIMIT、ORDER BY 结合使用。
1 select ename,job
2 from emp
3 order by rand() limit 5
//Oracle 在内置包 DBMS_RANDOM 里可以找到 VALUE 函数，把该内置函数和 ORDER BY、内置函数 ROWNUM 结合使用。
1 select *
2 from (
3 select ename, job
4 from emp
6 order by dbms_random.value()
7 )
8 where rownum <= 5
//SQL Server 同时使用内置函数 NEWID 和 TOP、ORDER BY 来返回一个随机结果集。
1 select top 5 ename,job
2 from emp
3 order by newid()
查找Null值
//要判断一个值是否为 Null，必须使用 IS Null。
1 select *
2 from emp
3 where comm is null
把Null值转换为实际值
//使用 COALESCE 函数将 Null 值替代为实际值。
1 select coalesce(comm,0)
2 from emp
查找匹配项
//你想从编号为 10 和 20 的两个部门中找到名字中含有字母 I 或职位以 ER 结尾的人。
//结合使用 LIKE 运算符和 SQL 通配符 %。
1 select ename, job
2 from emp
3 where deptno in (10,20)
4 and (ename like '%I%' or job like '%ER')

2、查询结果排序 order by

以指定顺序返回查询结果
使用 ORDER BY 子句。
1 select ename,job,sal
2 from emp
3 where deptno = 10
4 order by sal asc
//默认情况下，ORDER BY会做升序排列，因此 ASC 子句是可选项。相应地，也可以通过指定 DESC 执行降序排列。
// order by sal desc
多字段排序
ORDER BY 子句中列出不同的排序列，以逗号分隔。
1 select empno,deptno,sal,ename,job
2 from emp
3 order by deptno, sal desc
依据子串排序
//望从 EMP 表检索员工的名字和职位，并且按照职位字段的最后两个字符对检索结果进行排序
//DB2、MySQL、Oracle 和 PostgreSQL 在 ORDER BY 子句里使用 SUBSTR 函数。
select ename,job
 from emp
 order by substr(job,length(job)-2)
//SQL Server 在 ORDER BY 子句里使用 SUBSTRING 函数。
select ename,job
 from emp
 order by substring(job,len(job)-2,2)
对含有字母和数字的列排序
//有混合了字母和数字的数据，希望按照字母部分或者数字部分来排序
排序时对Null值的处理
//对可能为 Null 的列进行升序排列或者降序排列。
1 select ename,sal,comm
2 from emp
3 order by 3
1 select ename,sal,comm
2 from emp
3 order by 3 desc
//但是，如果你希望采用与非 Null 值列不同的方式来排列 Null 值，例如，你可能想把非 Null 值以升序排列或降序排列，而把全部 Null 值都放到最后面，那么你就要使用 CASE 表达式来动态调整排序项。
依据条件逻辑动态调整排序项
//例如，如果 JOB 等于 SALESMAN，就要按照 COMM 来排序；否则，按照 SAL 排序
1 select ename,sal,job,comm
2 from emp
3 order by case when job = 'SALESMAN' then comm else sal end
//可以利用 CASE 表达式来动态调整结果的排序方式。
select ename,sal,job,comm,
 case when job = 'SALESMAN' then comm else sal end as ordered
 from emp
 order by 5

3、多表查询 (连接查询和集合运算)

叠加两个行集
使用集合运算 UNION ALL 合并多个表中的行。
1 select ename as ename_and_dname, deptno
2 	from emp
3  where deptno = 10
4  union all
5 select '----------', null
6 	from t1
7  union all
8 select dname, deptno
9 	from dept
合并相关行
//一个连接查询的例子。更准确地说，它是内连接中的相等连接。连接查询是一种把来自两个表的合并起来的操作。对于相等连接而言，其连接条件依赖于某个相等条件（例如，一个表的部门编号和另一个表的部门编号相等）。
1 select e.ename, d.loc
2 from emp e, dept d
3 where e.deptno = d.deptno
4 and e.deptno = 10
查找两个表中相同的行
//可以把多个表中所有必要的列都连接起来，以获得正确的结果。也可以使用集合运算INTERSECT 来替代连接查询，并返回两个表的交集（相同的行）。
MySQL 和 SQL Server 
使用多个条件把 EMP 表和视图 V 连接起来。
1 select e.empno,e.ename,e.job,e.sal,e.deptno
2 	from emp e, V
3 	where e.ename = v.ename
4 		and e.job = v.job
5 		and e.sal = v.sal
除此之外，也可以使用 JOIN 子句执行同样的连接查询。
1 select e.empno,e.ename,e.job,e.sal,e.deptno
2 	from emp e join V
3 	  on ( e.ename = v.ename
4 		  and e.job = v.job
5 		  and e.sal = v.sal)
查找只存在于一个表中的数据
从一个表检索与另一个表不相关的行
两个表有相同的键，你想在一个表里查找与另一个表不相匹配的行。例如，你想找出哪些部门没有员工
新增连接查询而不影响其他连接查询
你已经有了一个查询语句，它可以返回你想要的数据。你需要一些额外信息，但当你试图获取这些信息的时候，却丢失了原有的查询结果集中的数据。例如，你想查找所有员工的信息，包括他们所在部门的位置，以及他们收到奖金的日期
确定两个表是否有相同的数据
你想知道两个表或两个视图里是否有相同的数据（行数和值）
识别并消除笛卡儿积
你想执行一个聚合操作，但查询语句涉及多个表。你希望确保表之间的连接查询不会干扰聚合操作。例如，你希望计算部门编号为 10 的员工的工资总额以及奖金总和。因为有部分员工多次获得奖金，所以在 EMP 表和 EMP_BONUS 表连接之后再执行聚合函数 SUM，就会得出错误的计算结果
组合使用连接查询与聚合函数
你想从多个表中返回缺失值。找到存在于 DEPT 表而不存在于 EMP 表的数据（即没有员工的部门）需要使用外连接
解决方案
使用全外连接（full outer join），基于一个共同值从两个表中返回缺失值。
从多个表中返回缺失值
在运算和比较中使用Null
1. 问题
Null 不会等于或不等于任何值，甚至不能与其自身进行比较，但是你希望对从 Null 列返回的数据进行评估，就像评估具体的值一样。例如，你想找出 EMP 表里业务提成（COMM列）比员工 WARD 低的所有员工。检索结果应该包含业务提成为 Null 的员工。
2. 解决方案
使用如 COALESCE 这样的函数把 Null 转换为一个具体的、可以用于标准评估的值。
1 select ename,comm
2 from emp
3 where coalesce(comm,0) < ( select comm
4 from emp
5 where ename = 'WARD' )