Oracle数据库编程——数据查询

数据查询

数据准备——建表、插入数据

	CREATE TABLE TEST.STU(
	SNO VARCHAR2(10) PRIMARY KEY,
	SNAME VARCHAR2(10),
	SGENTLE VARCHAR2(2),
	SAGE NUMBER(2),
	SDEPT VARCHAR2(20));
	
INSERT INTO TEST.STU VALUES('0001','张三','男',18,'计算机');
INSERT INTO TEST.STU VALUES('0002','李四','男',19,'艺术');
INSERT INTO TEST.STU VALUES('0003','王五','男',20,'外语');
INSERT INTO TEST.STU VALUES('0004','刘六','男',18,'大数据');
INSERT INTO TEST.STU VALUES('0005','王八','男',17,'工商');
INSERT INTO TEST.STU VALUES('0006','李七','男',19,'信息管理');

1.根据查询结果创建和复制表

条件：要求该表创建完成后包含了STU表中所有年龄为19的学生基本信息。

-- 根据查询结果创建和复制表
CREATE TABLE STU1
AS
SELECT
*
FROM TEST.STU
WHERE SAGE=19;

SELECT * FROM STU1;
SNO        SNAME      SG       SAGE SDEPT
---------- ---------- -- ---------- --------------------
0002       李四       男         19 艺术
0006       李七       男         19 信息管理

如果将上述语句中的WHERE子句删除，则写入到STU1表中的数据就是STU表的全部记录，这就实现了数据表的复制（包括表结构和表数据）

oracle PL/SQL提供了一个游标ROWNUM用于获取记录数据，如果只要求复制表结构而不复制表数据，可以使用该游标实现，语句如下：

CREATE TABLE STU1
AS
SELECT * FROM STU WHERE ROWNUM<1;

2.返回表中的前N行记录

在MS T-SQL中，定义了TOP N关键字来实现，TOP参数的作用是用来限制返回到结果集中记录的数目，而Oracle PL/SQL不支持该关键字。

SELECT * FROM Test.Stu
WHERE Rownum<4

本实例中同样使用了游标ROWNUM来实现获取表的前N行记录。事实上，ROWNUM是对查询结果集加的一个伪列，其需要先查询到结果集。简单地说，ROWNUM是符合条件的结果集的序号，其从1开始。

注意：ROWNUM 的使用只能用<、<=和!=等比较运算符，不能用>、>=等运算符，这是因为ROWNUM从自然数1开始，Oracle认为ROWNUM>n(n>1)这种条件是不成立的。

ROWNUM=n(n>1)不能直接作为条件直接写在where子句中，否则无法返回正确的结果。

3.消除重复行

SELECT DISTINCT sage
FROM Test.Stu

关键字distinct 用于消除查询中的重复值，只需要将其加在需要消除重复的列名前即可。

注意：distinct 关键字并不是消除数据表中的重复记录，而是消除查询结果中的重复记录，其不会对数据表中的数据造成影响。

4.NULL值的判定

空值在SQL中的表示为NULL。一般来说，下列情况可导致数据表的一个字段值为NULL。

• 该字段值未知
• 该字段值不存在
• 该字段对表的记录不可用

INSERT INTO TEST.STU VALUES('0008','钱十','女',20,'');
-- 此处插入仅为下面举例
-- NULL表示该字段的值是未知的，不确定的，null不是一个值
-- ''：表示空值，是一个数值。

在数据表中STU中插入一条SDEPT字段缺失的数据，查询如下：

SELECT * FROM TEST.STU
WHERE SDEPT IS NULL;

使用比较运算符不能处理NULL值，因为空值代表未知的空值，所以并不是所有的空值都相等。Oracle PL/SQL中使用特殊操作符IS来检测特殊值之间的等价性，其返回值为一个布尔型数值。

注意：空值并不包括0、一个或多个空格组成的字符串，以及零长度的字符串，它是一个未知的值。

5.查询字段为某个范围内的记录

要求：选出学生表STU中年龄在18-20岁之间的所有学生信息

SELECT
*
FROM Test.stu
WHERE sage BETWEEN 18 AND 20

相同的，如果在BETWEEN...AND...前加上NOT谓词，则对整个表达式的值取反。

注意：BETWEEN value1 AND value2 运算符在比较值的范围时，其取值范围含value1和value2两个值本身。

6.返回字段值为列表中某个值的记录

从STU表中取出SDEPT为”计算机“和”大数据“的学生信息

SELECT *
FROM test.stu
WHERE SDEPT IN ('计算机','大数据')

列表运算符IN允许WHERE子句后的字段从值列表中任取一个都为真。在IN前面加上NOT谓词进行取反。

注意：NULL 值不参与IN和NOT IN的比较。

7.条件组合查询

返回输出SDEPT为”大数据“且年龄在18岁的学生记录

SELECT
*
FROM test.stu
WHERE (sage=18 AND SDEPT='大数据')

WHERE子句后包含了两个条件，用圆括号将其括起来，表示一组条件。

多条件的组合是通过逻辑运算符来实现的。Oracle PL/SQL支持的逻辑运算符有三种：AND、OR、NOT。

8.复合条件查询

从STU表中查询并返回”外语“的男生和”大数据“的女生

SELECT *
FROM test.stu
WHERE (SDEPT='外语' AND SGENTLE='男') OR (SDEPT='大数据' AND SGENTLE='女')

在一条语句中含有两个以上的逻辑运算符时，就需要考虑其运算的优先级：先AND 后OR ，如果需要改变优先级，就需要用圆括号()来实现。

注意：如果WHERE子句后的条件使用了NOT运算符，其优先级是最高的，先进行NOT运算，再进行AND或OR运算。

9.实现模糊查询

在学生表STU中选取出所有姓”李“的学生的基本信息

SELECT * FROM test.stu
WHERE sname LIKE '李%'

LIKE运算符是模糊查询专用的运算符，%叫通配符，常用的通配符：

_:与任意单字符匹配

%:与包含一个或多个字符的字符串匹配

[]:与特定范围(例如：[a-f])或特定集(例如：[abcdef])中任意单字符匹配

注意：LIKE运算符后的字段值可以是完整的值，此时不需要使用通配符，LIKE的功能与比较运算符=相同。

10.使用ESCAPE和转义符

某一数据表存在含百分号(%)的数值，若要搜索%作为字符而不是通配符就需要使用ESCAPE子句和转义符来实现

SELECT *
FROM 表名
WHERE 列名 LIKE '%0!%' ESCAPE '!'

如70%，百分号前的那位数字为0，因此，%0!%中第一个%是通配符，允许前面有多个字符。后一个%是普通字符百分号，在前面加上了转义字符!，用ESCAPE子句定义转义符!

注意：转义符不仅可以为本实例中的”！”，也可以为“\”、“a”等用户使用ESCAPE关键字定义的符号和字符。