第2章 数据查询

2.1 数据库查询语言SQL

2.1.1 SQL语言的特点和分类

SQL语言可以在Oracle数据库中创建、存储、更新、检索和维护数据，其中主要的功能是实现数据的查询和数据的插入、删除、修改等操作。
* SQL语言在书写上类似于英文，简洁清晰，易于理解。它由关键字、表名、字段名，表达式等部分构成。
SQL语言属于非过程化的4GL(第四代语言)。 SQL语言按功能可分为DDL语言、DML语言、DCL语言和数据库事务处理语言四个类别。
*
SQL语言的主要关键字有：ALTER、DROP、REVOKE、AUDIT、GRANT、ROLLBACK、COMMIT、INSERT、SELECT、COMMENT、LOCK、UPDATE、CREATE、NOAUDIT、VALIDATE、DELETE、RENAME等。

数据库控制语言(DCL) 控制对数据库的访问，启动和关闭等
数据库定义语言(DDL) 用来创建、删除及修改数据库对象
数据库操纵语言(DML) 用来操纵数据库的内容，包括查询

2.1.2 SQL的基本语法

QL语言的语法比较简单，类似于书写英文的语句。其语句一般由主句和若干个从句组成，主句和从句都由关键字引导。主句表示该语句的主要功能，从句表示一些条件或限定，
* 关键字、字段名、表名等之间都要用空格或逗号等进行必要的分隔。
* 语句的大小写不敏感(查询的内容除外)。
* 语句可以写在一行或多行。
* 语句中的关键字不能略写和分开写在两行
* *要在每条SQL语句的结束处添加“；”号。
* 为了提高可读性，可以使用缩进。
* 从句一般写在另一行的开始处。
*

2.2 基本查询和排序

2.2.1 查询的基本用法

在Oracle数据库中，对象是属于模式的，每个账户对应一个模式，模式的名称就是账户名称。在表名前面要添加模式的名字，在表的模式名和表名之间用“.”分隔。我们以不同的账户登录数据库时，就进入了不同的模式，比如登录到STUDENT 账户，就进入了STUDENT模式。而在STUDENT模式要查询属于SCOTT模式的表，就需要写成：

SELECT * FROM SCOTT.EMP;

每个表都有一个虚列ROWNUM，它用来显示结果中记录的行号。我们在查询中也可以显示这个列。

SELECT rownum,ename FROM emp;

显示的行号是查询结果的行号，数据在数据库中是没有行号的。
【训练3】 显示雇员工资上浮20%的结果。

SELECT ename,sal,sal*(1+20/100) FROM emp;

我们可以为表的列起一个别名，它的好处是，可以改变表头的显示。特别是对于计算列，可以为它起一个简单的列别名以代替计算表达式在表头的显示。

SELECT ename AS 名称, sal 工资 FROM emp;

我们也可以使用字符型的计算列，方法是在查询中使用连接运算。连接运算符是双竖线“||”。通过连接运算可以将两个字符串连接在一起。

SELECT	ename||job AS "雇员和职务表" FROM emp;

在查询中可以使用字符和日期的常量，表示固定的字符串或固定日期。字符和日期的常量需要用单引号引起。下一个训练是作为上一个训练的改进。

SELECT	ename|| ' IS '||job AS "雇员和职务表" FROM emp;

消除重复行

如果在显示结果中存在重复行，可以使用的关键字DISTINCT消除重复显示。

SELECT DISTINCT job FROM emp;

2.2.2 查询结果的排序

SELECT 字段列表 FROM 表名 WHERE 条件 ORDER BY 字段名1 [ASC|DESC][,字段名2 [ASC|DESC]…];
ORDER BY从句后跟要排序的列。ORDER BY 从句出现在SELECT语句的最后。

排序有升序和降序之分，ASC表示升序排序，DESC表示降序排序。如果不指明排序顺序，默认的排序顺序为升序。如果要降序，必须书写DESC关键字。

SELECT ename, sal FROM emp ORDER BY sal;

SELECT ename,hiredate FROM emp ORDER BY hiredate DESC;

多列排序
这里我们排列的依据有两个列，分别是deptno,hiredate，如果hiredate排在前面，则hiredate的排序优先级大于deptno的排序优先级

SELECT ename,hiredate,deptno FROM emp ORDER BY hiredate,deptno;

2.2.3 条件查询

显示1982年以后雇佣的雇员姓名和雇佣时间。
输入并执行查询：

SELECT ename,hiredate FROM emp WHERE hiredate>='1-1月-82';

2.3条件特殊表示法

1．BETWEEN的用法

	对于数值型或日期型数据，表示范围时可以用以下的特殊运算表示方法：
	显示工资在1000～2000之间的雇员信息。
	输入并执行查询：

SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;

	执行结果从略。
	注意：下限在前，上限在后，不能颠倒。查询范围中包含上下限的值，因此在本例中，
	查询工资包含1000和2000在内。请自行执行并察看结果。

2.IN的用法

	使用以下运算形式，可以显示值满足特定集合的结果：
	[NOT] IN (...)
  显示职务为“SALESMAN'，“CLERK”和“MANAGER”的雇员信息。
	输入并执行查询：

SELECT * FROM emp WHERE job IN ('SALESMAN','CLERK','MANAGER');

	执行结果从略。
	注意：如果在IN前面增加NOT，将显示职务不在集合列表中的雇员。以上用法同样适用于数值型集合
	，请自行执行并察看结果。

3．LIKE的用法

	使用LIKE操作符可完成按通配符查找字符串的查询操作，该操作符适合于对数据进行模糊查询。其语句法为：
	[NOT] LIKE 匹配模式
	匹配模式中除了可以包含固定的字符之外，还可以包含以下的通配符：
	%：代表0个或多个任意字符。
	_ ：代表一个任意字符。

显示姓名以“S”开头的雇员信息。
输入并执行查询：

SELECT * FROM emp WHERE ename LIKE 'S%';

4．判断空值NULL

	在表中，字段值可以是空，表示该字段没有内容。如果不填写，或设置为空则我们说该字段的内容为NULL。
	NULL没有数据类型，也没有具体的值，但是使用特定运算可以判断出来。这个运算就是：
	IS  [NOT] NULL

SELECT  ename, mgr FROM emp WHERE mgr **IS** NULL;

2.4 函数

2.4.1 数值型函数

abs 求绝对值的函数
mod 求除法余数
round 指定精度对十进制数四舍五入 round(45.923,1) =45.9
trunc 按指定精度截断十进制数 trunc(45.923,-1) =40

2.4.2 字符型函数

ascii 获得字符的ASCII码
chr 返回与ascii码相应的字符
substr 给出起始位置和长度，给出子字符串
lpad 用字符填充字符串左侧到指定长度
rpad 用字符填充字符串右侧到指定长度
length 求字符串的长度

如果不知道表的字段内容是大写还是小写，可以转换后比较。
输入并执行查询：

SELECT  empno, ename, deptno	FROM emp
		WHERE  lower(ename) ='blake';

2.4.3 日期型函数

	Oracle使用内部数字格式来保存时间和日期，包括世纪、年、月、日、小时、分、秒。缺省日期格式为 DD-
	MON-YY，如“08-05月-03”代表2003年5月8日。
	SYSDATE是返回系统日期和时间的虚列函数。
	使用日期的加减运算，可以实现如下功能：
	对日期的值加减一个天数，得到新的日期。
	对两个日期相减，得到相隔天数。
	通过加小时来增加天数，24小时为一天，如12小时可以写成12/24(或0.5)。

sysdate 系统当前的时间
last_day 次月的最后一天
round 对日期天数进行四舍五入

2.4.4 转换函数

	Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER

To_char 转换成字符串类型 To_char(1234.5, ‘$9999.9’) --> $1234.5
To_date 转换成日期类型 To_date(‘1980-01-01’, ‘yyyy-mm-dd’) --> 01-1月-80
To_number 转换成数值类型 To_number(‘1234.5’) --> 1234.5

Oracle可以自动根据具体情况进行如下的转换：

• 字符串到数值。
• 字符串到日期。
• 数值到字符串。
• 日期到字符串。

日期类型转换
将日期型转换成字符串时，可以按新的格式显示。
如格式YYYY-MM-DD HH24:MI:SS表示“年-月-日 小时:分钟:秒”。Oracle的日期类型是包含时间在内的。

2.4.5 其他函数

	Oracle还有一些函数，如decode和nvl，这些函数也很有用，归纳如表2-11所示。

nvl 空值转换函数 nvl(null, ‘空’) 空 decode 实现分支功能 decode(1,1, ‘男’, 2, ‘女’) 男
userenv 返回环境信息 userenv(‘LANGUAGE’) SIMPLIFIED CHINESE_CHINA.ZHS16GBK
greatest 返回参数的最大值 greatest(20,35,18,9) 35
least 返回参数的最小值 least(20,35,18,9) 9

1．空值的转换

	如果对空值NULL不能很好的处理，就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是，在查询雇员表时，将工资sal字段和津贴字段comm进行相加，如果津贴为空，则相加结果也为空，这样容易引起误解。
	使用nvl函数，可以转换NULL为实际值。该函数判断字段的内容，如果不为空，返回原值；为空，则返回给定的值。

SELECT	ename,nvl(job,'无'),nvl(hiredate,'01-1月-97'),nvl(comm,0) FROM	 emp;

2．decode函数

decode函数可以通过比较进行内容的转换，完成的功能相当于分支语句。该函数的第一个参数为要进行转换的表达式，以后的参数成对出现，最后一个参数可以单独出现。如果第一个参数的值与第二个表达式的值相等，则返回第三个表达式的值；如果不等则继续比较，如果它的值与第四个表达式的值相等，则返回第五个表达式的值，以此类推。在参数的最后位置上可以存在单独的参数，如果以上比较过程没有找到匹配值，则返回该参数的值，如果不存在该参数，则返回NULL。

SELECT	ename,decode(job, 'MANAGER', '经理', 'CLERK','职员', 'SALESMAN','推销员',
 'ANALYST','系统分析员','未知') FROM emp;

 select decode(dept.loc,'NEW YORK','纽约','DALLAS','达拉斯','CHICAGO','芝加哥','BOSTON','波士顿','LOS ANGELES','洛杉矶','未知')as 城市,decode(dept.dname,'ACCOUNTING','会计','RESEARCH'
 ,'研发','SALES','销售','OPERATIONS','运营','MARKETING','促销','未知') as 岗位 from dept;

3．userenv函数

	函数userenv返回用户环境信息字符串，该函数只有一个字符串类型的参数，参数的内容为如下之一的字符串，可以不区分大小写：
	* ISDBA：判断会话用户的角色是否为SYSDBA，是则返回TRUE。
	* INSTANCE：返回会话连接的INSTANCE标识符。
	* LANGUAGE：返回语言、地区、数据库字符集信息。
	* LANG：返回会话语言的ISO简称。
	* TERMINAL：返回正在会话的终端或计算机的标识符。

4．最大、最小值函数

	greatest返回参数列表中的最大值，least返回参数列表中的最小值。
	这两个函数的参数是一个表达式列表，按表达式列表中的第一个表达式的类型对求值后的表达式求得最大
	或最小值。对字符的比较按ASCII码的顺序进行。如果表达式中有NULL，则返回NULL。

2.5 高级查询

2.5.1 多表联合查询

通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在WHERE子句中用比较运算符指明连接的条件。两个表的连接有四种连接方式：
* 相等连接。
* 不等连接。
* 外连接。
* 自连接。

1．相等连接

	通过两个表具有相同意义的列，可以建立相等连接条件。使用相等连接进行两个表的查询时，只有连接列上
	在两个表中都出现且值相等的行才会出现在查询结果中。	

2．外连接

	在以上的例子中，相等连接有一个问题：如果某个雇员的部门还没有填写，即保留为空，那么该雇员在查询中
	就不会出现；或者某个部门还没有雇员，该部门在查询中也不会出现。
	为了解决这个问题可以用外连，即除了显示满足相等连接条件的记录外，还显示那些不满足连接条件的行
	，不满足连接条件的行将显示在最后。外连操作符为(+)，它可以出现在相等连接条件的左侧或右侧。

3．不等连接

	还可以进行不等的连接。
	显示雇员名称，工资和所属工资等级。
	执行以下查询：

SELECT e.ename, e.sal, s.grade FROM emp e,salgrade s
WHERE 	e.sal BETWEEN s.losal AND s.hisal；

4．自连接

	 最后是一个自连接的训练实例，自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同
	 的表(表和表的副本)，可以通过不同的别名区别两个相同的表。

2.5.2 统计查询

通常需要对数据进行统计，汇总出数据库的统计信息。比如，我们可能想了解公司的总人数和总工资额，或各个部门的人数和工资额，这个功能可以由统计查询完成。
Oracle提供了一些函数来完成统计工作，这些函数称为组函数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDER
BY子句中。组函数也可以称为统计函数。

AVG 求平均值
COUNT 求计数值，返回非空行数，*表示返回所有行
MAX 求最大值
MIN 求最小值
SUM 求和
STDDEV 求标准偏差，是根据差的平方根得到的
VARIANCE 求统计方差

分组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。
使用GROUP BY 从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。
如果不使用分组，将对整个表或满足条件的记录应用组函数。
在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT
表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。

2.5.3 子查询

我们可能会提出这样的问题，在雇员中谁的工资最高，或者谁的工资比SCOTT高。通过把一个查询的结果作为另一个查询的一部分，可以实现这样的查询功能。具体的讲：要查询工资高于SCOTT的雇员的名字和工资，必须通过两个步骤来完成，第一步查询雇员SCOTT的工资，第二步查询工资高于SCOTT的雇员。第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中，这就是子查询。出现在其他查询中的查询称为子查询，包含其他查询的查询称为主查询。

子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。子查询按照返回数据的类型可以分为单行子查询、多行子查询和多列子查询。

2.5.4 集合运算*

	多个查询语句的结果可以做集合运算，结果集的字段类型、数量和顺序应该一样。
	Oracle共有4个集合操作

UNION 并集，合并两个操作的结果，去掉重复的部分
UNION ALL 并集，合并两个操作的结果，保留重复的部分
MINUS 差集，从前面的操作结果中去掉与后面操作结果相同的部分 INTERSECT 交集，取两个操作结果中相同的部分