第8章数据查询

第8章 数 据 查 询主要内容SELECT语句介绍简单查询分组查询多表查询子查询合并查询SQL内置函数8.1 SELECT语句介绍SEL
ECT [ALL|DISTINCT]column_name[,expression…]FROM table1_name[,tab
le2_name,view_name,…][WHERE condition][GROUP BY column_name1[,col
umn_name2,…] [HAVING group_condition]][ORDER BY column_name2 [ASC
|DESC][,column_name2,…]];8.2 简 单 查 询无条件查询有条件查询查询排序查询统计　8.2.1 无条
件查询查询所有列SELECT FROM employees; 查询指定列SELECT department_id,depart
ment_name FROM departments; 使用算术表达式SELECT employee_id,salary0.8
FROM .employees;使用字符常量SELECT employee_id, ''salary is: '', salary F
ROM employees; 使用函数SELECT employee_id,UPPER(first_name) FROM empl
oyees; 改变列标题SELECT employee_id empno,salary sal FROM employees;使用
连接字符串SELECT ''员工名:''||first_name||last_name FROM employees; 消除重复行SE
LECT ALL department_id FROM employees; SELECT DISTINCT department
_id FROM employees; 8.2.2 有条件查询查询满足条件的元组可以通过WHERE 子句实现。WHERE条件中常
用的运算符号关系运算 SELECT employee_id,salary FROM employees WHERE depa
rtment_id!= 10; SELECT employee_id,salary FROM employees WHERE sa
lary>1000;确定范围谓词BETWEEN AND与NOT BETWEEN AND。SELECT FROM employe
es WHERE department_id BETWEEN 10 AND 20;SELECT FROM employees
WHERE salary NOT BETWEEN 1000 AND 2000; 确定集合谓词IN可以用来查找属性值属于指定集合的元
组。SELECT employee_id,first_name,last_name,salary FROM employee
s WHERE department_id IN(10,20,30,50);字符匹配%（百分号）代表任意长（长度为0）字符串
。_（下划线）代表任意单个字符。ESCAPE：转义字符SELECT FROM employees WHERE last
_name LIKE ''%S%''; SELECT FROM employees WHERE first_name L
IKE ''_a%'';SELECT FROM employees WHERE first_name LIKE ''%x_%
'' ESCAPE ''x'';空值操作涉及空值查询时使用IS NULL或 IS NOT NULL，这里的IS不能用=替代。SELECT
FROM employees WHERE department_id IS NULL;SELECT FROM em
ployees WHERE commission_pct IS NOT NULL;逻辑操作用逻辑运算符NOT、AND和OR来
联结多个查询条件。优先级：NOT、AND、OR（用户可以用括号改变优先级）。 IN谓词实际上是多个OR运算的缩写。 SELECT
FROM employees WHERE department_id=10 AND salary >1500; SELE
CT FROM employees WHERE (department_id=10 OR department_id=
20) AND salary>1500;注意：使用BETWEEN…AND，NOT BETWEEN…AND，IN，NOT IN运算符
的查询条件都可以转换为NOT，AND，OR的逻辑运算。例如，下面两个语句是等价的：SELECT FROM employees
WHERE salary>3000 AND salary<4000;SELECT FROM employees W
HERE salary BETWEEN 3000 AND 4000;升序、降序排序ASC: 升序 （缺省）；DESC: 降序S
ELECT employee_id,salary FROM employees ORDER BY salary;SELECT
employee_id,salary FROM employees ORDER BY salary DESC;多列排序
首先按照第一个列或表达式进行排序；当第一个列或表达式的数据相同时，以第二个列或表达式进行排序，以此类推 。SELECT FRO
M employees ORDER BY department_id,salary DESC; 8.2.3 查询排序按表达
式排序 可以按特定的表达式进行排序。 SELECT employee_id,salary FROM employees OR
DER BY salary12; 使用别名排序 可以使用目标列或表达式的别名进行排序。SELECT employee_id,sa
lary12 year_salary FROM employees ORDER BY year_salary; 使用列位
置编号排序 如果列名或表达式名称很长，那么使用位置排序可以缩短排序语句的长度。SELECT employee_id,salary
12 yearsal FROM employees ORDER BY 2; 8.2.4 查询统计注意除了COUNT()函
数外，其他的统计函数都不考虑返回值或表达式为NULL的情况。聚集函数只能出现在目标列表达式、ORDER BY子句、HAVING子句
中，不能出现在WHERE子句和GROUP BY子句中。默认对所有的返回行进行统计，包括重复的行；如果要统计不重复的行信息，则可以使
用DISTINCT选项。如果对查询结果进行了分组，则聚集函数的作用范围为各个组，否则聚集函数作用于整个查询结果。统计50号部门员工
的人数、平均工资、最高工资、最低工资。 SELECT count(),avg(salary),max(salary),min(s
alary) FROM employees WHERE department_id=50;统计所有员工的平均工资和工资总额。SEL
ECT avg(salary),sum(salary) FROM employees;统计有员工的部门的个数。SELECT cou
nt(DISTINCT department_id) FROM employees;统计员工工资的方差和标准差。SELECT va
riance(salary),stddev(salary) FROM employees;8.3 分组查询基本语法单列分组查询多列
分组查询使用HAVING子句限制返回组SELECT column, group_function, …FROM table[WHE
RE condition]GROUP BY group_by_expression[HAVING group_condition]
[ORDER BY column[ASC|DESC]];（1）基本语法注意：GROUP BY子句用于指定分组列或分组表达式。集合函
数用于对分组进行统计。如果未对查询分组，则集合函数将作用于整个查询结果；如果对查询结果分组，则集合函数将作用于每一个组，即每一个分
组都有一个集合函数。HAVING子句用于限制分组的返回结果。WHERE子句对表中的记录进行过滤，而HAVING子句对分组后形成的组
进行过滤。在分组查询中，SELECT子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是集合函数。单列分组查询将
查询出来的记录按照某一个指定的列进行分组SELECT department_id,count(),avg(salary) FRO
M employees GROUP BY department_id ORDER BY department_id; 多列分组查询
在GROUP BY子句中指定了两个或多个分组列SELECT department_id,job_id,count(),avg(s
alary) FROM employees GROUP BY department_id,job_id;使用HAVING子句限制
返回组可以使用HAVING子句，只有满足条件的组才会返回。SELECT department_id,count(),avg(sa
lary) FROM employees GROUP BY department_id HAVING avg(salary)>80
00; 8.4 多表查询交叉连接内连接等值连接不等值连接自身连接外连接左外连接右外连接全外连接8.4.1 交叉连接概念两个或多个
表之间的无条件连接。一个表中所有记录分别与其他表中所有记录进行连接。如果进行连接的表中分别有n1，n2，n3…条记录，那么交叉连接
的结果集中将有n1×n2×n3×…条记录。 标准SQL语句的连接方式SELECT table1.column,talbe2.col
umn[,…] FROM table1 CROSS JOIN table2;Oracle扩展的连接方式SELECT table1.
column,talbe2.column[,…] FROM table1,table2;employees表中有107条记录，de
pt表中有28条记录，那么两个表交叉连接后有2996条记录。SELECT employee_id,first_name,salar
y,department_nameFROM employees CROSS JOIN departments;或者：SELECT
employee_id,first_name,salary,department_name FROM employees, dep
artments;8.4.2内连接执行过程内连接语法等值内连接非等值内连接自身内连接执行过程首先在表1中找到第一个元组，然后从头开
始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第1个元组与该元组拼接形成结果表中的一个元组。表2全部找完后，再找表1中
的第2个元组，然后再从头扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第2个元组与该元组拼接形成结果表中的一个元组。重复
执行，直到表1中的全部元组都处理完毕为止。内连接语法：标准SQL语句的连接方式SELECT table1.column,talbe
2.column[,…] FROM table1 [INNER] JOIN table2 [JOIN …] ON conditio
n;内连接语法：Oracle扩展的连接方式SELECT table1.column,talbe2.column[,…] FROM
table1,table2[,…] WHERE condition;等值内连接 SELECT employee_id,salary
,e.department_id, department_name FROM employees e JOIN departme
nts dON e.department_id=d.department_id WHERE e.department_id=10
;SELECT employee_id,salary,e.department_id,department_name FROM e
mployees e,departments dWHERE e.department_id=d.department_id AND
e.department_id=10 ; 非等值内连接SELECT employee_id,first_name,last_n
ame,salary,grade FROM hr.employees JOIN hr.sal_grades ON salary>m
in_salary AND salary ast_name,salary,grade FROM hr.employees,hr.sal_gradesWHERE salary
>min_salary AND salary work.first_name,manager.employee_id,manager.first_name FROM emplo
yees work JOIN employees manager ON work.manager_id=manager.empl
oyee_id;SELECT work.employee_id,work.first_name,manager.employee_
id,manager.first_name FROM employees work, employees manager WHE
RE work.manager_id=manager.employee_id;8.4.3 外连接左外连接右外连接全外连接左外连接
语法：标准SQL语句的连接方式SELECT table1.column, table2.column[,…] FROM table
1 LEFT JOIN table2[,] ON table1.column table2.column[,
…];左外连接语法：Oracle扩展的连接方式SELECT table1.column, table2.column[,…] FR
OM table1, table2[,…]WHERE table1.column
table2.column(+)[…];查询10号部门的部门名、员工号、员工名和所有其他部门的名称，语句为SELECT depar
tment_name,employee_id,first_name,last_name FROM departments d L
EFT JOIN employees eON d.department_id=e.department_id AND d.dep
artment_id=10;或SELECT department_name,employee_id,first_name,last
_name FROM departments d, employees eWHERE d.department_id=e.dep
artment_id(+) AND e.department_id(+)=100; 右外连接语法：标准SQL语句的连接方式SELE
CT table1.column, table2.column[,…] FROM table1 RIGHT JOIN table2
[,…] ON table1.column table2.column[…];右外连接语法：Oracle扩展
的连接方式SELECT table1.column, table2.column[,…] FROM table1, table2[
,…]WHERE table1.column (+) table2.column[…]
; 查询20号部门的部门名称及其员工号、员工名，和所有其他部门的员工名、员工号，语句为SELECT employee_id,fir
st_name,department_name FROM departments d RIGHT JOIN employees
eON d.department_id=e.department_id AND d.department_id=20;或SELE
CT employee_id,first_name,department_nameFROM departments d, emp
loyees eWHERE d.department_id(+)=e.department_id AND d.department
_id(+)=20;全外连接是指在内连接的基础上，将连接操作符两侧表中不符合连接条件的记录加入结果集中。在Oracle数据库中，全
外连接的表示方式为SELECT table1.column, table2.column[,…] FROM table1 FULL
JOIN table2[,…] ON table1.column1 = table2.column2[…];查询所有的部门名和
员工名，语句为SELECT department_name,first_name,last_name FROM employee
s e FULL JOIN departments d ON e.department_id=d.department_id;
8.5 子 查 询子查询概述无关子查询单行单列子查询多行单列子查询单行多列子查询多行多列子查询相关子查询在FROM子句中使用
子查询 在DDL语句中使用子查询使用WITH子句的子查询子查询的概念子查询是指嵌套在其他SQL语句中的SELECT语句，也称为嵌套
查询 。在执行时，由里向外，先处理子查询，再将子查询的返回结果用于其父语句（外部语句）的执行。 子查询作用在INSERT或CREA
TE TABLE语句中使用子查询在UPDATE语句中使用子查询可以修改一个或多个记录的数据；在DELETE语句中使用子查询可以删除
一个或多个记录在WHERE和HAVING子句中使用子查询可以返回一个或多个值；在DDL语句中使用子查询子查询概述8.5.1 无关
子查询单行单列子查询多行单列子查询单行多列子查询多行多列子查询单行单列子查询是指子查询只返回一行数据，而且只返回一列的数据。 运算
符=，>，<，>=，<=，!= 查询比105号员工工资高的员工的员工号、员工名、员工工资信息，语句为 SELECT employe
e_id,first_name,last_name,salary FROM employeesWHERE salary>(SELE
CT salary FROM employees WHERE employee_id=105); （1）单行单列子查询（2）多行单
列子查询多行单列子查询是指返回多行数据，且只返回一列的数据。 运算符号查询与50号部门某个员工工资相等的员工信息。SELECT e
mployee_id,first_name,last_name,salary FROM employees WHERE sala
ry IN (SELECT salary FROM employees WHERE department_id=50); 查询比
50号部门某个员工工资高的员工信息。SELECT employee_id,first_name,last_name,salary
FROM employees WHERE salary>ANY(SELECT salary FROM employeesWHERE
department_id=50);查询比50号部门所有员工工资高的员工信息。SELECT employee_id,first_
name,last_name,salary FROM employees WHERE salary>ALL(SELECT sal
ary FROM employees WHERE department_id=50);（3）单行多列子查询单行多列子查询是指子查询
返回一行数据，但是包含多列数据。多列数据进行比较时，可以成对比较，也可以非成对比较。成对比较要求多个列的数据必须同时匹配，而非成对
比较则不要求多个列的数据同时匹配。查询与169号员工的工资、工种都相同的员工的信息。SELECT employee_id,firs
t_name,last_name,salary,job_idFROM employees WHERE (salary,job_id
)=(SELECT salary,job_id FROMemployees WHERE employee_id=169);查询与5
0号部门某个员工工资相同，工种也与10号部门的某个员工相同的员工的信息。SELECT employee_id,first_name
,last_name,salary,job_id FROM employees WHERE salary IN (SELECT s
alary FROM employees WHERE department_id=50)AND job_id IN (SELECT
job_id FROM employees WHERE department_id=50);（4）多行多列子查询多行多列子查询是
指子查询返回多行数据，并且是多列数据。例如，查询与50号部门某个员工的工资和工种都相同的员工的信息，语句为SELECT emplo
yee_id,first_name,last_name,salary,job_idFROM employees WHERE (sa
lary,job_id) IN(SELECT salary,job_id FROM employees WHERE departm
ent_id=50); 8.5.2相关子查询子查询在执行时并不需要外部父查询的信息，这种子查询称为无关子查询。如果子查询在执行时需
要引用外部父查询的信息，那么这种子查询就称为相关子查询。在相关子查询中经常使用EXISTS或NOT EXISTS谓词来实现。如果子
查询返回结果，则条件为TRUE，如果子查询没有返回结果，则条件为FALSE。查询没有任何员工的部门号、部门名。SELECT F
ROM departments d WHERE NOT EXISTS(SELECT FROM employees e WHER
E e.department_id=d.department_id); 查询比本部门平均工资高的员工信息。SELECT emplo
yee_id,first_name,last_name,salary FROM employees e WHERE salary>
(SELECT avg(salary) FROM employees WHERE department_id=e.departme
nt_id);8.5.3在FROM子句中使用子查询当在FROM子句中使用子查询时，该子查询被作为视图对待，成为内嵌视图。查询各个员
工的员工号、员工名及其所在部门平均工资。SELECT employee_id,first_name,last_name,d.avg
sal FROM employees,(SELECT department_id,avg(salary) avgsal FROM
employees GROUP BY department_id) d WHERE employees.department_id
=d.department_id;查询各个部门号、部门名、部门人数及部门平均工资。SELECT d.department_id,d
epartment_name,ds.amount,ds.avgsalFROM departments d, (SELECT dep
artment_id, count()amount,avg(salary)avgsal FROM employees GROU
P BY department_id)ds WHERE d.department_id=ds.department_id;内嵌视图
有一种特殊的应用，称为Top-N-Analysis查询，通过使用伪列ROWNUM，为查询结果集排序，并返回符合条件的记录。Top-
N视图的基本定义形式为：SELECT[column_list],ROWNUM FROM (SELECT [column_list]
FROM table ORDER BY Top-N_column) WHERE ROWNUM <=N;查询工资排序在前1～5名的员
工号、员工名、工资及其工资排序号。SELECT FROM(SELECT employee_id,first_name,last
_name,salary FROM employees ORDER BY salary DESC) nested_orde WHE
RE ROWNUM<=5;查询工资排序在前5～10名的员工号、员工名、工资及其工资排序号。SELECT FROM (SELEC
T ROWNUM num,employee_id,first_name,last_name,salary FROM (SELECT
employee_id,first_name,last_name,salary FROM employees ORDER BY
salary DESC) nested_order1)nested_order2 WHERE num BETWEEN 5 AND
10;8.5.4 DDL语句中的子查询可以在CREATE TABLE和CREATE VIEW语句中使用子查询来创建表和视图。CR
EATE TABLE emp_subquery AS SELECT employee_id,first_name,last_nam
e,salaryFROM employees;CREATE VIEW emp_view_subqueryASSELECT FR
OM employees WHERE salary>2000;8.5.5 使用WITH子句的子查询如果在一个SQL语句中多次使用
同一个子查询，可以通过WITH子句给子查询指定一个名字，从而可以实现通过名字引用该子查询，而不必每次都完整写出该子查询。查询人数最
多的部门的信息。SELECT FROM departments WHERE department_id IN (SELECT
department_id FROM employees GROUP BY department_id HAVING count(
)>=ALL(SELECT count() FROM employees GROUP BY department_id));
相同的子查询连续出现了两次，因此可以按下列方式编写查询语句。WITH deptinfo AS(SELECT department_
id,count() num FROM employees GROUP BY department_id)SELECT FR
OM departments WHERE department_id IN(SELECT department_id FROM d
eptinfo WHERE num=(SELECT max(num) FROM deptinfo)) 8.6 合 并 操 作语法
UNIONINTERSECTMINUS语法： SELECT query_statement1 [UNION|UNION A
LL|INTERSECT|MINUS] SELECT query_statement2;注意：当要合并几个查询的结果集时，这几
个查询的结果集必须具有相同的列数与数据类型。如果要对最终的结果集排序，只能在最后一个查询之后用ORDER BY子句指明排序列。 8
.6.1 并集运算UNION运算符用于获取几个查询结果集的并集，将重复的记录只保留一个，并且默认按第一列进行排序。查询50号部门
的员工号、员工名、工资和部门号以及工资大于8000的所有员工的员工号、员工名、工资和部门号，语句为SELECT employee_
id,salary,department_id FROM employees WHERE department_id=50UNIO
NSELECT employee_id,salary,department_id FROM employees WHERE sal
ary>8000 ORDER BY department_id; 如果要保留所有的重复记录，则需要使用UNION ALL运算符。S
ELECT employee_id,salary,department_id FROM employees WHERE depar
tment_id=50UNION ALLSELECT employee_id,salary,department_id FROM
employees WHERE salary>8000 ORDER BY department_id;8.6.2 交集运算INT
ERSECT用于获取几个查询结果集的交集，只返回同时存在于几个查询结果集中的记录。同时，返回的最终结果集默认按第一列进行排序。查询
50号部门中工资大于6000的员工号、员工名、工资和部门号，语句为：SELECT employee_id,first_name,l
ast_name,salary,department_idFROM employees WHERE department_id=
50INTERSECTSELECT employee_id,first_name,last_name,salary,departm
ent_idFROM employees WHERE salary>6000; 8.6.3 差集运算MINUS用于获取几个查询
结果集的差集，即返回在第一个结果集中存在，而在第二个结果集中不存在的记录。同时，返回的最终结果集默认按第一列进行排序。查询50号部
门中工种不是“ST_CLERK”的员工号、员工名和工种名称，语句为： SELECT employee_id,first_name,
last_name,job_id FROM employees WHERE department_id=50MINUSSELECT
employee_id,first_name,last_name,job_idFROM employees WHERE job_
id=''ST_CLERK'';8.7 SQL内置函数SQL函数分类数值函数字符函数日期函数转换函数其他函数SQL函数分类根据参数作
用行数的不同，可以分为：单行函数多行函数根据参数类型不同，可以分为：数值函数字符函数日期函数转换函数聚集函数 8.7.1 数值
函数SELECT salary/22 daysal,round(salary/22,1),trunc(salary/22,1),
round(salary/22,-1),trunc(salary/22,-1) FROM employees;SELECT sal
ary,width_bucket(salary,1000,10000,10) FROM employees WHERE depar
tment_id=30; SELECT floor(3.5),ceil(3.5),mod(5,3), remainder(5
,3), mod(4,3),remainder(4,3) FROM dual;FLOOR(3.5) CEIL(3.5)
MOD(5,3) REMAINDER(5,3)
MOD(4,3) REMAINDER(4,3) ------------------
------------------------------------- 3 4
2 -1 1 18.7.2 字符函数SELECT lpad(''abc'',5,
''#'') leftpad,rpad(''abc'',5, ''#'') rightpad, ltrim(''abcd'', ''a'')
lefttrim,rtrim(''abcde'', ''e'') righttrim, substr(''abcd'',2,3
) substring FROM dual;LEFTPAD RIGHTPAD LEFTTRIM RIGHTTRIM S
UBSTRING ------- ----------- ----------- ------------ ---------
------------ ##abc abc## bcd abcd
bcd SELECT employee_id,concat(concat(first_name,'' ''),last_name)
employee_name FROM employees WHERE employee_id=108;SELECT instr(
''abcde'',''b'') position,replace(''oracle9i'',''9i'',''10g'') newstring,so
undex(''hello'') soundFROM dual;POSITION NEWSTRING SOUND ---
------------------------------------ 2 oracle10g
H4008.7.3 日期函数日期函数是指对日期进行处理的函数，函数输入为DATE或TIMESTAMP类型的数据，
输出为DATE类型的数据（除MONTH_BETWEEN函数返回整数以外）。Oracle数据库中日期的默认格式为DD-MON-YY。
可以通过设置NLS_DATE_FORMAT参数设置当前会话的日期格式，通过NLS_LANGUAGE参数设置表示日期的字符集。例如：
ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS'';ALTER S
ESSION SET NLS_LANGUAGE=''AMERICAN'';SELECT SYSDATE,add_months(sysd
ate,2) ADDM, next_day(sysdate,2) NEXTD,Last_day(sysdate) LAST
D, round(sysdate, ''MONTH'') ROUNDM, trunc(sysdate, ''MONTH''
?) TRUNCM FROM DUAL;SYSDATE ADDM NEXTD LA
STD ROUNDM TRUNCM ---------- ---------- ----
------ ---------- ---------- ---------- 2009-03-27 2009-05-27 2
009-03-30 2009-03-31 2009-04-01 2009-03-01 SELECT extract(YEAR FR
OM SYSDATE) YEAR, extract(DAY FROM SYSDATE) DAY , extract
(HOUR FROM SYSTIMESTAMP) HOUR,extract(MINUTE FROM SYSTIMESTAM
P) MINUTE FROM DUAL;YEAR DAY HOUR MINUTE ------------------------------------ 2009 28 14 44 SELECT dbtimezone,localtimestamp, numtoyminterval(20,''MONTH'') DAY_SECOND FROM dual;DBTIMEZONE LOCALTIMESTAMP YEAR_MONTH ---------------------------------------------------------- +00:00 28-3月-09 10.56.04.882000 下午 +000000001-08 8.7.4 转换函数SELECT to_date(''09-3-28'', ''yy-mm-dd'') CHARTODATE, to_char(sysdate, ''yyyy-mm-dd hh:mi:ss'') DATETOCHAR, to_char(123, ''$9999.99'') NUMTOCHAR, to_number(''$1234.56'',''$9999.99'') CHARTONUMBER FROM DUAL;CHARTODATE DATETOCHAR NUMTOCHAR CHARTONUMBER ---------------------------------------------------------- 28-3月 -09 2009-03-29 12:53:54 $123.00 1234.56 SELECT timestamp_to_scn(systimestamp) SCN, scn_to_timestamp(2790424) TIMESTAMP FROM dual;SCN TIMESTAMP -------------------------------------------- 2790484 29-3月 -09 12.58.20.000000000 上午8.7.5 其他函数SELECT employee_id, salary+nvl(commission_pctsalary,0) totalsalaryFROM .employees WHERE department_id=30; 总结简单的单表查询分组查询连接查询子查询合并查询SQL内置函数