查

select

基础查询

 # 查询 emp 表 所有的信息
 SELECT * FROM emp
 
 # 查询 emp 表的 deptno和dname字段的信息
 SELECT deptno，dname FROM emp
 

SELECT [distinct]{*|column|expression[alias]}
into 表名
FROM table
where 条件表达式
group by conditions
having conditions
order by conditions;		-- 对结果集进行排序

# distinct：消除重复行
# 字符型数值区分大小写

去重 distinct

 # 添加 distinct 或 DISTINCT  参数
 SELECT DISTINCT * FROM emp

别名 as

• 当列别名中包含空格、要求区分大小写、含有特殊字符时需要加双引号

 # 两种方式
 SELECT ENAME 姓名，SAL 工资 FROM emp
 SELECT ENAME as 姓名，SAL as 工资 FROM emp

排序 desc/asc

#   desc   降序  大 -> 小
#   asc    升序  小 -> 大

# 按 deptno降序，且 deptno降序 情况下 dname升序 排序
SELECT deptno，dname FROM emp 
order by deptno desc,dname asc;     

伪列

表中没有的一列，由sql拼接增加的一列

# 求年薪，SAL：月薪 ， SAL * 12：年薪
SELECT ENAME ， SAL ， SAL * 12  FROM emp

连接操作符 (拼接) ||

SELECT ename||job AS "Employees" 2  FROM emp;
# 将表emp中的ename列和job列连接起来显示

# 字符串拼接  符号： || 
SELECT ename ,ename || '-abc'  FROM emp 

# 拼接内容为空时，是空字符串，不是null
SELECT ename,comm,ename || comm FROM emp

分组 group by

1. 要把所有的非聚合字段加入group by中

• 非聚合指没有进行计算的，聚合指进行计算的，比如sum(某列名)等就不用加进去
• 在select中出现的列必须全部出现在group by的后面,但在group by里出现的列不必都出现在select 中

2. 在group by 中不能用别名

SELECT  deptno ,avg(sal) FROM emp group by  deptno; 

SELECT  deptno ,count(*) FROM emp group by  deptno;

过滤 having

• 作用于分组

# 部门平均工资>2000的部门编号
SELECT  deptno ,avg(sal) FROM emp 
group by  deptno
having avg(sal)>2000; 

# 部门员工数>3的部门的编号
SELECT  deptno ,count(*) FROM emp 
group by  deptno
having count(*)>3; 

FETCH

用来限制查询返回的行数

指定要返回的行数或百分比。

[ OFFSET offset ROWS]
 FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]
 
 
 

# 为了语义清晰的目的，您可以使用关键字 ROW 而不是 ROWS，FIRST 而不是 NEXT。 
# 例如
# 以下子句的行为和产生的结果相同
FETCH NEXT 1 ROWS
FETCH FIRST 1 ROW

# 返回库存量最高的前 5 个产品
SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH NEXT 5 ROWS ONLY;

ONLY | WITH TIES 选项

仅返回 FETCH NEXT (或 FIRST) 后的行数或行数的百分比。

WITH TIES 返回与最后一行相同的排序键。请注意，如果使用 WITH TIES，则必须在查询中指定一个 ORDER BY 子句。如果不这样做，查询将不会返回额外的行。

例子：查前十名成绩，用了with ties后，会查前十名，并且把剩余的与第十名有一样成绩的也排列在第十名后面

# 以下查询语句仅能在Oracle 12c以上版本执行
SELECT
 product_name,
 quantity
FROM
 inventories
INNER JOIN products
 USING(product_id)
ORDER BY
 quantity DESC 
FETCH NEXT 10 ROWS with ties;

exists/not exists

如果子查询不返回任何行，则NOT EXISTS运算符返回true。 否则，它返回false

请注意，如果子查询返回任何具有NULL值的行，则NOT EXISTS运算符将返回false

SELECT * FROM table_name WHERE NOT EXISTS (subquery);

多行运算符

in

# rowid = 1 或 rowid = 2 或 rowid = 3  都可以
select ... FROM ... WHERE rowid in (1,2,3)
# 括号()里面可以是值，也可以是查询等语句
select ... FROM ... WHERE rowid in (select... )
select ... FROM ... WHERE rowid not in (select... )

any/some

any和some的行为完全相同，因此它们完全可以互换

# rowid小于其中一个
select ... FROM ... WHERE rowid < any (1,2,3)

# 也可以是 > >= != 等运算符

# 括号()里面可以是值，也可以是查询等语句
select ... FROM ... WHERE rowid < any (select... )
select ... FROM ... WHERE rowid < not any (select... )

all

# rowid小于全部
select ... FROM ... WHERE rowid < all (1,2,3)
# 也可以是 > >= != 等运算符
# 括号()里面可以是值，也可以是查询等语句
select ... FROM ... WHERE rowid > all (select... )
select ... FROM ... WHERE rowid not > all (select... )

优先级

优先级	运算分类	运算符举例
1	算术运算符	+, -, *, /,%
2	连接运算符	||
3	比较运算符	=, <>, <, >, <=, >=
4	特殊比较运算符	between…and…，in，like，is，null
5	逻辑非	not
6	逻辑与	and
7	逻辑或	or

虚表 dual

不能保存任何数据，只有一个字段， 一行记录

不要删除这个表

当我们不希望从任何表中读取数据，同时又想 利用SQL引擎中的计算表达式的能力帮我们运算时，就可以使用DUAL表

SELECT 999*666 FROM dual;

条件查询

# sal字段数据大于10的,且ename等于FORD的
SELECT *  FROM emp where sal > 10 and ename = 'FORD'

# sal字段数据介于[2000,4000]的，闭区间
SELECT *  FROM emp where sal BETWEEN 2000 and 4000

>    # 大于
<    # 小于
>=   # 大于等于
<=   # 小于等于
!=   # 不等于 
<>   # 不等于
^=   # 不等于

in(2000,4000)   # 等于2000或4000
BETWEEN 2000 and 4000   # [2000,4000]之间，包含2000和4000

条件1 and 条件2  # 满足条件1 且 满足条件2
条件1 or  条件2  # 满足条件1 或 满足条件2
not 条件1       # 取反，取 不满足 条件1的

空处理

nvl()

# 计算时，设置空值返回的内容
nvl(表达式,空时返回的结果)

# 不处理空时，与空计算会为空
SELECT ename , sal, comm ,sal+comm  FROM emp 

# 处理空后，与空计算相当于与 空时返回的结果 计算
SELECT ename , sal, comm ,sal+ nvl(comm,0)  FROM emp 

nvl2( )

# 如果expr1不是null,返回expr2,否则返回expr3
nvl2(expr1,expr2,expr3)  

# str='na'
SELECT nvl2(str,0,1)  FROM dual; 
#　0

# str=null
SELECT nvl2(str,0,1)  FROM dual; 
#　1

nullif()

# 比较两个表达式,如果相等，返回null,否则，返回第 一个表达式
nullif(expr1,expr2) 	

# a='a'
# str='b'
SELECT nullif(a,str)  FROM dual;
# a

# a='a'
# str='a'
SELECT nullif(a,str)  FROM dual;
# null

coalesce()

# coalesce()函数是对nvl() 函数的扩展
# 返回第一个不为空的参数，参数个数不受限制
coalesce(表达式 1 , 表达式 2 ,… 表达式 n)

SELECT coalesce( null,0,1)  FROM dual;
# 0

SELECT coalesce( null,'','1')  FROM dual;
# 1

nulls first / nulls last

# 排序时，空的放前面
nulls first  

# 排序时，空的放后面
nulls last  

is null / is not null / not...is null

is null         # 值为空
is not null     # 值不为空
not...is null   # 值不为空

# comm为空时
SELECT ename , sal, comm   FROM emp  where comm is null;
# comm不为空时
SELECT ename , sal, comm   FROM emp  where comm is not null;
SELECT ename , sal, comm   FROM emp  where not comm is null;

模糊查询 like

%   # 任意字符
_   # 一个任意字符

# ename 包含 A 的
SELECT ename  FROM EMP where ename LIKE '%A%';

# ename 以 A 开头
SELECT ename  FROM EMP where ename LIKE 'A%';
# ename 以 A 结尾
SELECT ename  FROM EMP where ename LIKE '%A';

# ename 第二个为 A 的
SELECT ename  FROM EMP where ename LIKE '_A%';

# ename 倒数第二个为 A 的
SELECT ename  FROM EMP where ename LIKE '%A_';

使用符号 escape

escape('a') # 标识 a 后面的一个符号就是符号本身，没有其他功能

# 标识 a 后面的 % 就是 % 这个符号，不是任意字符了
SELECT *  FROM EMP where ename LIKE '%a%%' escape('a');

# 标识 B 后面的 % 就是 % 这个符号，不是任意字符了
SELECT *  FROM EMP where ename LIKE '%B%%' escape('B');

# 标识 a(第一个) 后面的 a(第二个) 就是 a 这个符号，不是标识字符了
# 标识 a(第三个) 后面的 _ 就是 _ 这个符号，不是一个任意字符了
SELECT *  FROM EMP where ename LIKE '%aaa_%' escape('a');

子查询

# 条件1 要 满足 子查询
SELECT  ... FROM .. where 条件1 = (子查询)
# 从 子查询 中 查询
SELECT  ... FROM (子查询) where ...


SELECT *  FROM EMP 
where DEPTNO = (
    SELECT DEPTNO FROM DEPT where dname  =  'SALES'
)

分页

真分页：获取全部数据，分页排好了，问题：占内存

假分页：只获取对应页的数据，问题：数据库操作频繁

rownum

• rownum不是表中的字段，rownum是个伪列

• 每次查询时，自动从1开始排序

• 可以直接用

• 不能用 大于 > 条件

# rownum不是表中的字段
SELECT deptno, ename,rownum FROM emp

# 子查询 能用 大于 > 条件
SELECT * FROM (SELECT deptno, ename,rownum rw FROM emp) WHERE rw > 5

rowid

• rowid不是表中的字段，rowid是个伪列
• 每个查询都有自己的rowid
• rowid具有唯一性

# 案例
# copy表中有name,age两个字段4条数据
# 现在每条数据复制两份，既12条数据
# 要求
# 去除重复的数据

delete FROM copy WHERE rowid not in (select rowid ri FROM copy);
commit；

连接查询

笛卡尔积

一个表中的所有行和另一个表的所有行都进行了连接（配对）

发生情况:

• 连接条件被省略
• 连接条件是无效的

# 笛卡尔积
select ... FROM table1,table2
select ... FROM emp,dept
# 别名
select ... FROM emp e,dept d

select ... FROM emp e,dept d

等值连接

select ... FROM emp e,dept d where e.deptno = d.deptno

非等值连接

select ... FROM emp e,dept d 
where e.deptno = d.deptno 
and e.sal between d.losal and d.hisal

# 查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
select e.empno 员工编号,e.ename 姓名,e.sal 工资,s.grade 工资等级,d.loc 工作城市
from emp e,dept d,salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal
order by s.grade;

select e.empno 员工编号,e.ename 姓名,e.sal 工资,s.grade 工资等级,d.loc 工作城市
from emp e 
join dept d on e.deptno = d.deptno
join salgrade s on e.sal between s.losal and s.hisal
order by s.grade;

内连接

• 99标准

# 语法格式 inner可以省略 
select ... from table1 inner join table2 on 连接条件;
# 省略 inner
select ... from table1 join table2 on 连接条件;

外连接 (+)

• 92标准

主表：主表的数据即使是空也会保留

+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。如果加号写在左表，右表就是全部显示，所以是右连接。

# 语法：
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+)=table2.column;

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column=table2.column(+);

# 查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来 
SELECT e.ename 姓名,d.deptno 部门编号,d.dname 部门名称
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno
ORDER BY e.deptno;

左连接

左外连接以FROM子句中的左边表为基表，该表所有行数据 按照连接条件无论是否与右边表能匹配上都会被显示出来。

# 左连接
SELECT e.ename,e.deptno,d.loc
FROM emp e LEFT JOIN dept d ON (e.deptno = d.deptno);
# 左外连接
SELECT e.ename,e.deptno,d.loc
FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno);

右连接

右外连接以FROM子句中的右边表为基表，该表所有行数据 按照连接条件无论是否与左边表能匹配上，都会被显示出 来。

# 右连接
SELECT e.ename,e.deptno,d.loc
FROM emp e RIGHT JOIN dept d ON (e.deptno = d.deptno);
# 右外连接
SELECT e.ename,e.deptno,d.loc
FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno);

全外连接

全外连接返回两个表等值连接结果，以及两个表中所有等值连接失败的记录

SELECT e.ename,e.deptno,d.loc
FROM emp e FULL OUTER JOIN dept d ON (e.deptno = d.deptno);

自连接

是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样

# 例子：员工表，有员工信息和上级信息
# 查员工都上级
# mgr:上级id
# empno:员工id
SELECT * FROM emp e,emp m where e.mgr = m.empno;

交叉连接 (笛卡尔积)

• 99标准

• 相当于笛卡尔积

# SELECT ... FROM 表1  CROSS JOIN 表2; 
SELECT ... FROM emp CROSS JOIN dept;

自然连接 (等值连接)

• 99标准

• 相当于等值连接

• 必须有同名字段或主外键关系

# SELECT ... FROM 表1 NATURAL JOIN 表2; 
SELECT ... FROM emp NATURAL JOIN dept; 

join using (等值连接)

• 99标准

• 相当于等值连接

• 必须要有同名字段

# SELECT ... FROM 表1 JOIN 表2 using(根据这个字段); 
SELECT ... FROM emp JOIN dept using(deptno); 

全能连接 on 多连接 (推荐)

• 99标准

• 推荐

# 同名字段deptno需要指定来自那张表
SELECT ...,e.deptno FROM emp e JOIN dept d on e.deptno = d.deptno; 

# 多连接
SELECT ...,e.deptno 
FROM emp e 
JOIN dept d 
on e.deptno = d.deptno
JOIN sa s 
on e.deptno = s.id
where ... ;

内连接和外连接的区别

内连接消除了与另一个表中的任何不匹配的行，而外连接扩展了内连接的结果集，除了返回所有匹配的行外，返回一部分或全部不匹配的行，这就要取决于外连接的种类

集合

Union、Union All、Intersect、Minus

• Union：并集(去重)，对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序;

• Union All：全集(不去重)，对两个结果集进行并集操作，包括重复行，不进行排序﹔

• Intersdct：交集(找出重复)，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序;

• Minus：差集(减去重复)，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

联合（union）

返回由任一查询结果集包含的行，并且去除重复行,并且按照查询结果集的第一列 升序排序

规则：

• 多个被联合的查询语句所选择的列数和列的数据类型必须一致， 列的名字不必相同 列的名字不必相同。
• 如果多个查询结果都有NULL值，整个结果中只包含一个NULL值。
• 每个查询不能包含自己的Order by子句，只能在联合之后使用 Order by子句。

查询1 UNION 查询2

# 查询编号为7839的员工当前工资、岗位及 历史工资、岗位。
SELECT sal,job FROM emp WHERE empno=7839
UNION
SELECT sal,job FROM emp_jobhistory WHERE empno=7839;
1234

完全联合运算（union all）

返回由任一查询结果集包含的行，并且包含重复行，默认情况下不对 结果集进行排序。

规则：

• 多个被联合的查询语句所选择的列数和列的数据类型必须一致 列的名字不必相同， 列的名字不必相同。
• 如果多个查询结果都有NULL值，不被去掉。
• 每个查询不能包含自己的Order by子句，只能在联合之后使用 Order by子句。

注意：

使用UNION ALL会比UNION的速度快，因为省去了去掉重复 记录和排序的时间。

查询1 union all  查询2

SELECT ... FROM ... WHERE ...
union all
SELECT ... FROM... WHERE ...;

相交（intersect）

返回多个查询结果集的公有行。

规则：

• 多个查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。
• 相交运算不忽略空值。

查询1 intersect  查询2

SELECT ... FROM ... WHERE ...
intersect
SELECT ... FROM... WHERE ...;

相减minus）

返回第一个查询中存在而第二个查询中不存在的行记录。

规则：

• 多个查询语句所选择的列数和列的数据类型必须一 致，列的名字不必相同。
• 相减运算不忽略空值

查询1 minus  查询2

SELECT ... FROM ... WHERE ...
minus
SELECT ... FROM... WHERE ...;

层次查询

SELECT [LEVEL], column, expr...
FROM table		# 只能从单独的一个表中选择
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];

# LEVEL：节点的层次，伪列，由查询的起点开始算起为1，依次类推。
# Condition：是一个比较式。
# START WITH（必须有）：指定层次的根行(起点)。
# CONNECT BY PRIOR（必须有）：指定存在父与子行的关系列。