【SQL.Cookbook读书笔记】——1检索记录

前言

SQL注释：
方法一：一行#…
方法二：多行/…/
方法三：一行代码后–…

1检索记录

SELECT语句的学习

1.1从表中检索所有行和列

查看表中所有数据

#方法一：“*”表示从指定表中返回每一列
select *
  from emp
#方法二：指定返回每一列（更加清楚）
select empno,ename,job,sal,mgr,hiredate,comm,deptno
  from emp

1.2从表中检索部分行（where）

从表中查看满足特定条件的行

#查找部门号码为10的所有员工
select *
  from emp
 where deptno = 10

1.3查找满足多个条件的行（where、and、or）

返回满足多个条件的行

#查询1：查找DEPTNO为10，或者COMM不为NULL（所有得到提成的员工），或者工资不超过2000且DEPTNO为20
select *
  from emp
 where deptno = 10
 	or comm is not null
 	or sal <= 2000 and deptno = 20
#查询2：以下用了括号，查询结果有所变动：DEPTNO为10，或者COMM不为NULL（所有得到提成的员工），或者工资不超过2000；且DEPTNO为20
select *
  from emp
 where (	deptno = 10
         or comm is not null
         or sal <= 2000
       )
 and deptno = 20 

注意：可以看出按照查询2这样筛选，结果并不会返回DEPTNO为10的员工，因为DEPTNO为10与DEPTNO为20的员工没有交集

1.4从表中检索部分列

查看表中感兴趣的列

select sal,comm
  from emp

1.5为列取有意义的名称

改变查询所返回的列名，使他们更具可读性，更容易理解

select sal as salary, comm as commission
  from emp

1.6在where子语句中引用取别名的列

想在where子语句中引用别名，但下列代码出错（因为SQL运行时是按step1、2、3进行的，而emp这个表中没有salary这一列）：

select sal as salary, comm as commission	--step3
  from emp	--step1
 where salary < 5000	--step2 

以下将取别名的查询作为内联视图，便可以在外部查询中引用其中的别名列

select *	--step6
  from (	--step1
select sal as salary, comm as commission	--step3
  from emp	--step2
  	   ) x	--step4：记内联视图的别名为X，并非所有数据库都需要内联视图显式给内联视图取别名
 where salary < 5000	--step5

以上使用内联视图的方法在下列情形的where字句中都可以使用：聚集函数；标量子查询；视窗函数；别名

1.7连接列值

问题
将多列作为一列返回。例如，要查询emp表，返回这样的结果集：（某人做…工作）
CLARK WORKS AS A MANAGER
KING WORKS AS A PRESIDENT
MILLER WORKS AS A CLERK（职员；书记；记账员）

可以看出CLARK KING MILLER都是人名，来自ename这一列；而MANAGER PRESIDENT CLERK都来自job这一列。
解决方案
查找和使用DBMS提供的内置函数，来连接来自不同列的值。
（1）DB2,Oracle,PostgreSQL这些数据库使用双竖线||作为连接符

select ename||' WORKS AS A '||job as msg
  from emp
 where deptno = 10

（2）MySQL支持CONCAT函数

select concat(ename, ' WORKS AS A ' ,job) as msg
  from emp
 where deptno = 10 

（3）SQL Sever使用“+”运算符进行连接操作

select ename + ' WORKS AS A ' + job as msg
  from emp
 where deptno = 10

注意：这三种方法中’ WORKS AS A '可以理解为字符串

1.8在select语句中使用条件逻辑

问题
产生结果集：如果一个员工工资小于等于2000美金，就返回消息“UNDERPAID”；如果大于等于4000美金，就返回消息“OVERPAID”，如果是在这两者之间，就返回OK

解决方案
使用CASE表达式直接在SELECT语句中执行条件逻辑。

select ename,sal,
	   case when sal <= 2000 then 'UNDERPAID'
	   		when sal >= 4000 then 'OVERPAID'
	   		else 'OK'	--如果没有使用else，对于不满足满段条件的行，CASE表达式会返回NULL。
	   end as status	--为了更加易读，给CASE表达式一个别名STATUS

1.9限制返回的行数

限制查询中返回的行数，这里不关心顺序，返回任何n行都可以。
（1）DB2

select *
  from emp fetch first 5 rows only

（2）MySQL和PostgreSQL

select *
  from emp limit 5

（3）SQL Sever

select top 5 *
  from emp

（4）Oracle

select *
  from emp
 where rownum <= 5

注意：方法（4）与其他方法不同，rownum的数值是获取了满足条件的行之后才赋予的。
下面说明使用rownum = 5来返回第五行是不可行的：

1. Orcale先获取表中的第一行，叫做第1行，1不满足rownum = 5，故Orcale丢弃这一行，不对这一行赋值。
2. Orcale再获取表中的第二行，仍然叫做第1行，1不满足rownum = 5，故Orcale丢弃这一行，不对这一行赋值。
3. …
4. 永远也不会有“到了第5行”的情况发生，因为每次获取的行都叫做第1行。

注意：使用rownum = 1来返回第一行是可行的

1.10从表中随机返回n条记录

使用DBMS支持的内置函数来生成随机数值：
（1）DB2
同时使用内置函数RAND与ORDER BY和FETCH

select ename,job
  from emp
 order by rand() fetch first 5 rows only

（2）MySQL
同时使用内置的RAND函数、LIMIT和ORDER BY：

select ename,job
  from emp
 order by rand() limit 5

（3）PostgreSQL
同时使用内置的RANDOM函数、LIMIT和ORDER BY：

select ename,job
  from emp
 order by random() limit 5

（4）SQL Sever
同时使用内置函数NEWID、TOP和ORDER BY，返回随机结果集：

select top 5 ename,job
  from emp
 order by newid()

（5）Oracle
同时使用DBMS_RANDOM包中的内置函数VALUE、ORDER BY和内置函数ROWNUM：

select *	    --step6
  from (		--step1
 select ename,job		--step4
   from emp		--step2
  order by dbms_random.value()	--step3：在order by子句中使用函数，则先执行函数生成随机数，然后order by子句接收函数的返回值，并使用它改变结果集的次序
  	   )
 where roownum <= 5		--step5

1.11查找空值

要查找某列值为空的所有行

select *
  from emp
 where comm is null

注意：NULL不能用等于或不等于跟任何值比较，包括它自身。也不能用 = 或 != 来测试一列是否为NULL，为了确定一行是否为空值，必须使用IS NULL；也可以使用IS NOT NULL来查找给定列的值不为空的行。

1.12将空值转换为实际值

使用COALESCE函数或CASE用实际值替换空值，语句如下：

#方法一：在此列中只要comm非空，就返回comm的值，否则返回0
select coalesce(comm,0)
#方法二：
select case
       when comm is null then 0
       else comm
  from emp 

1.13按模式搜索

在部门10和部门20中，需要返回名字中有一个“I”，或职务（job title）中有带有“ER”的员工。

#使用LIKE运算符和SQL通配符%，%可以表示任何字符序列，ER%表示查找以ER起始的职务
select ename,job
  from emp
 where deptno in (10,20)
   and (ename like '%I%' or job like '%ER%')