Oracle 笔记02

数据查询

SELECT语句

SELECT [ALL|DISTINCT] column_name [,expression...]
FROM table1_name [,table2_name,view_name,...]
[WHERE condition]
[GROUP BY column_name1 [,column_name2,...]
[HAVING group_condition]]
[ORDER BY column_name2 [ASC|DESC] [,column_name3,...]];

基本查询

此处切换为 scott 账户，默认密码是 tiger，运行 Oracle 安装目录下的
/RDBMS/ADMIN/utlsampl.sql 文件即可创建接下来演示的表

表构成：

雇员表 emp

部门表 dept

无条件查询

SELECT * FROM emp;

查询指定列

SELECT deptno, dname FROM dept;

使用算数表达式

SELECT empno, sal*0.8 FROM emp;

使用字符常量：

SELECT empno,'Name is :',ename FROM emp;

使用函数：

-- 将 ename 列的输出字符置为大写
SELECT empno,UPPER(ename) FROM emp;

改变列标题

SELECT ename employeename,sal salary FROM emp;

使用连接字符串

SELECT '员工号：'||empno||'员工名：'||ename FROM emp;

显示所有行（可重复）

SELECT ALL deptno FROM emp;

显示所有行（不可重复）

SELECT DISTINCT deptno FROM emp;

有条件查询

查询不在10号部门的雇员的编号、姓名、工资

SELECT empno,ename,sal FROM emp
WHERE deptno != 10;

查询基本工资大于1500的雇员的编号、姓名、工资

SELECT empno,ename,sal FROM emp WHERE sal>1500;

查询部门编号在10-20间的雇员信息

SELECT * FROM emp WHERE deptno NOT BETWEEN 10 AND 20;

查询属于10和20号部门雇员的信息

-- 谓词 IN 可以属性值属于指定集合的元组
SELECT empno,ename,sal FROM emp WHERE deptno IN(10,30);

字符通配

 % 表示任意长(可为0)的字符串
 _ 表示任意单个字符

检索姓名中含有 'S' 字符的雇员

SELECT empno,ename FROM emp WHERE ename LIKE '%S%';

检索姓名第二个人字母为 'A' 的雇员

SELECT empno,ename FROM emp WHERE ename LIKE '_A%';

ESCAPE转义字符

在模式中，将转义字符置于通配符前，该通配符将被转义为普通字符

ESCAPE 'escape_character'

检索姓名包含 '_' 的雇员

SELECT * FROM emp WHERE ename LIKE '%x_%' ESCAPE 'x';

空值查询

涉及空值查询时使用 IS NULL 或 IS NOT NULL，这里的 IS 不能用 = 代替

查询奖金为空值的雇员

SELECT * FROM emp WHERE comm IS NULL;

逻辑运算

查询10号部门工资大于1500的雇员

SELECT * FROM emp WHERE deptno=10 AND sal>1500;

查询10号和20号部门工资大于1500的雇员

SELECT * FROM emp WHERE (deptno=10 OR deptno=20) AND sal>1500;

查询工资在1500到2000之间的雇员

SELECT * FROM emp WHERE sal>=1500 AND sal<=2000;

升序/降序 输出

--ASC 升序（默认）   DESC 降序

按工资递增的顺序输出雇员信息

SELECT empno,ename,sal FROM emp ORDER BY sal;

按工资递减的顺序输出雇员信息

SELECT empno,ename,sal FROM emp ORDER BY sal DESC;

多行排列规则

-- 首先按照第一个列或表达式进行排序
-- 第一个数据相同时，以第二个列或表达式进行排序，依此类推
-- 按照部门编号递增，工资递减的序列列出所有雇员
SELECT * FROM emp ORDER BY deptno, sal DESC;

使用列位置编号排序

SELECT empno,sal*12 salary FROM emp ORDER BY 2;

聚集函数

※ 除了COUNT(*)函数外，其他的统计函数都不考虑返回值或表达式为 NULL 的情况
※ 聚集函数只能出现在目标列表达式、ORDER BY子句和 HAVING 子句中
※ 聚集函数不能出现在 WHERE 子句和 GROUP BY 子句中
※ 默认对所有的返回行进行统计，包括重复的行
※ 如果要统计不重复的行信息，则可使用 DISTINCT 选项
※ 如果对查询结果进行了分组，则聚集函数的作用范围为各个组，否则聚集函数作用于整个查询结果

查询10号部门的人数、平均工资和最低工资

SELECT count(*),avg(sal),min(sal)
FROM emp
WHERE deptno = 10;

查询所有雇员的平均奖金和总奖金

SELECT avg(comm),sum(comm) FROM emp;

分组查询

查询不同部门的雇员人数和平均工资

SELECT deptno, count(*),avg(sal) FROM emp
GROUP BY deptno;

查询不同部门中不同工种的员工人数和平均工资

SELECT deptno, job,count(*),avg(sal) FROM emp
GROUP BY deptno, job;

查询部门平均工资高于2000的部门信息

-- 查询部门号、部门人数、部门平均工资
SELECT deptno,count(*),avg(sal)
FROM emp
GROUP BY deptno
HAVING avg(sal)>2000;

连接查询

交叉连接

emp 和 dept 表的交叉连接

-- 查询所有的雇员们，部门名
SELECT ename,dname FROM emp,dept;

内连接

查询10号部门雇员的编号、姓名等信息

SELECT
	empno,ename,sal,emp.deptno,dname
FROM emp,dept
WHERE emp.deptno = 10
AND emp.deptno = dept.deptno;

子查询

子查询的作用：
INSERT或CREATE TABLE语句 —— 将子查询的结果写到目标表中
UPDATE语句 —— 可以修改一个或多个记录的数据
DELETE语句 —— 删除一个或多个记录
WHERE和HAVING子句 —— 返回的一个或多个值

单行单列子查询

查询比7934号员工工资高的员工编号

SELECT empno
FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7934);

查询与10号部门某个员工工资相等的员工信息

SELECT empno,ename,sal
FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE deptno=10);

查询比10号部门某个员工工资高的员工信息

SELECT empno,ename,sal
FROM emp
WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=10);

查询比10号部门所有员工工资高的员工信息

SELECT empno,ename,sal
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=10);

单行多列子查询

查询与7844号员工的工资、工种都相同的员工信息

SELECT empno,ename,sal,job FROM emp
WHERE (sal,job)=(SELECT sal,job FROM emp WHERE empno = 7844);

多行多列子查询

查询与10号部门某个员工的工资和工种都相同的员工信息

SELECT empno,ename,sal,job FROM emp
WHERE (sal,job) IN
(SELECT sal,job FROM emp WHERE deptno = 10);

相关子查询

无关子查询：子查询在执行时不需要外部父查询的信息
相关子查询：
※ 子查询在执行时要引用外部父查询的信息
※ 经常使用EXISTS和NOT EXSISTS谓词来实现
※ 如果子查询返回结果，则条件为TRUE
※ 如果子查询没有返回结果，则条件为FALSE

查询没有任何员工的部门号、部门名

SELECT deptno,dname,loc
FROM dept
WHERE NOT EXISTS(
	SELECT * FROM emp WHERE emp.deptno = dept.deptno);

查询各个员工的员工号、员工名及其所在部门平均工资

-- 在FROM子句中使用子查询时，该子查询被作为视图对待，必须为该子查询指定别名
SELECT empno,ename,d.avgsal
FROM
	emp,
	(SELECT deptno,avg(sal) avgsal FROM emp
	 GROUP BY deptno)d
 WHERE emp.deptno = d.deptno;

查询各个部门号、部门名、部门人数和部门平均工资

SELECT dept.deptno,dname,d.amount,d.avgsal
FROM
	dept,
	(SELECT deptno,count(*) amount,avg(sal) avgsal
	 FROM emp GROUP BY deptno)d
WHERE dept.deptno = d.deptno;

合并查询

-- 当要合并几个查询的结果集时，这几个查询的结果集必须具有相同的列数与数据类型
-- 如果要对最终的结果集排序，只能在最后一个查询之后用ORDER BY子句指明排序类型
SELECT query_statement1
[UNION|UNION ALL|INTERSECT|MINUS]
SELECT query_statement2;

UNION

查询10号部门的员工信息以及工资大于2000的所有员工的信息

-- UNION 默认会消除重复记录
SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10
UNION
SELECT empno,ename,sal,deptno FROM emp WHERE sal>2000
ORDER BY deptno;

查询10号部门的员工信息以及工资大于2000的所有员工的信息

-- 问题和上面的一样，但这里要求保留重复的记录
-- 保留需要用到 UNION ALL
SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10
UNION ALL
SELECT empno,ename,sal,deptno FROM emp WHERE sal>2000
ORDER BY deptno;

INTERSECT 取交集

查询30号部门中工资大于2000的员工信息

-- INTERSECT 用于获取几个查询结果集的交集，返回结果默认按第一列进行排序
SELECT empno,ename,sal,deptno FROM emp WHERE deptno = 30
INTERSECT
SELECT empno,ename,sal,deptno FROM emp WHERE sal>2000;

MINUS 取差集

查询30号部门中工种不是“SALESMAN”的员工信息

-- MINUS 用于获取几个查询结果集的差集，返回结果默认按第一列进行排序
SELECT empno,ename,job FROM emp WHERE deptno=30
MINUS
SELECT empno,ename,job FROM emp WHERE job = 'SALESMAN'