Oracle_SQL 数据操作和查询

1、SQL简介

SQL 是结构化查询语言（Structured Query Language），专门用于数据存取、数据更新及数据库管理等操作。

在 Oracle 开发中，客户端把 SQL 语句发送给服务器，服务器对 SQL 语句进行编译、执行，把执行的结果返回给客户端。Oracle SQL 语句由如下命令组成：

• 数据定义语言（DDL），包括 CREATE（创建）命令、ALTER（修改）命令、DROP（删除）命令等。
• 数据操纵语言（DML），包括 INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT … FOR UPDATE（查询）等。
• 数据查询语言（DQL），包括基本查询语句、Order By 子句、Group By 子句等。
• 事务控制语言（TCL），包括 COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
• 数据控制语言（DCL），GRANT（授权）命令、REVOKE（撤销）命令。

目前主流的数据库产品（比如：SQL Server、Oracle）都支持标准的 SQL 语句。数据定义语言，表的增删改操作，数据的简单查询，事务的提交和回滚，权限的授权和撤销等，Oracle与 SQL Server 在操作上基本一致。

2、Oracle数据类型

Oracle 数据库的核心是表，表中的列使用到的常见数据类型如下：

类型	含义
CHAR(length)	存储固定长度的字符串。参数 length 指定了长度，如果存储的字符串长度小于 length，用空格填充。默认长度是 1，最长不超过 2000 字节。
VARCHAR2(length)	存储可变长度的字符串。length 指定了该字符串的最大长度。默认长度是 1，最长不超过 4000 字符。
NUMBER(p，s)	既可以存储浮点数，也可以存储整数，p 表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p 默认是 38 为），s 是指小数位数。
DATE	存储日期和时间，存储纪元、4 位年、月、日、时、分、秒，存储时间从公元前 4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。
TIMESTAMP	不但存储日期的年月日，时分秒，以及秒后 6 位，同时包含时区。
CLOB	存储大的文本，比如存储非结构化的 XML 文档
BLOB	存储二进制对象，如图形、视频、声音等。

对应的NUMBER类型的实例：

格式	输入的数字	实际的存储
NUMBER	1234.567	1234.567
NUMBER（6，2）	123.4567	123.46
NUMBER（4，2）	12345.67	输入的数字超过了所指定的精度，数据库不能存储

对于日期类型，可以使用 sysdate 内置函数可以获取当前的系统日期和时间，返回 DATE类型，用 systimestamp 函数可以返回当前日期、时间和时区。

Oracle 的查询中，必须使用“select 列… from 表”的完整语法，当查询单行函数的时候，from 后面使用 DUAL 表，dual 表在系统中只有一行一列，该表在输出单行函数时为了select…from 的语法完整性而使用。

3、创建表和约束

 Oracle 创建表使用 CREATE TABLE 命令来完成。创建约束则使用如下命令：

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容

案例1：创建一个学生信息（INFOS）表和约束：

CREATE TABLE INFOS
(
		STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号
		STUNAME VARCHAR2(10) NOT NULL, --姓名
		GENDER VARCHAR2(4) NOT NULL, --性别
		AGE NUMBER(2) NOT NULL, --年龄
		SEAT NUMBER(2) NOT NULL, --座号
		ENROLLDATE DATE, --入学时间
		STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址
		CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号
)  -- 1

ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID);  --2

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女'); --3

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50); --4

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100);  --5

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO 
		CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR
				 (CLASSNO >='2001' AND CLASSNO<='2999'));  --6
				 
ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME); --7 

代码解析：

• 1 在 Oracle 代码中，“/”执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”。
• 2 创建一个主键约束。
• 3 与 4 5 6 7一起创建各种 check 约束。其中⑦是唯一约束，表示该列值是唯一的，列中的值不能重复。

案例2：创建一个成绩表（SCORES）表和约束：

CREATE TABLE SCORES
(
ID NUMBER ,    --ID  1
TERM VARCHAR2(2),  --学期 S1或S2
STUID VARCHAR2(7) NOT NULL,  --学号
EXAMNO VARCHAR2(7) NOT NULL,  --考号 E+班号+序号
WRITTENSCORE NUMBER(4,1) NOT NULL,  --笔试成绩
LABSCORE NUMBER(4,1) NOT NULL  --机试成绩
)

ALTER TABLE SCORES ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2');

ALTER TABLE SCORES ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID);  --2

代码解析：

• 1 SQL Server 中可以使用 identify 创建自动增长列，但是 Oracle 中的自动增长需要借助序列（Sequence）完成，在后面章节中讲解。
• 2 Oracle 中的外键约束定义。

4、数据操纵语言（DML）

据操纵语言（DML）用于对数据库的表中数据进行添加、修改、删除和 SELECT…For UPDATE(后面专门学习该查询)操作。

（1）简单查询

数据查询是用 SELECT 命令从数据库的表中提取信息。SELECT 语句的语法是：

SELECT *|列名|表达式 FROM 表名 WHERE 条件 ORDER BY 列名

语法解析：

1.  *表示表中的所有列。
2. 列名可以选择若干个表中的列名，各个列表中间用逗号分隔。
3. 表达式可以是列名、函数、常数等组成的表达式。
4. WHERE 子句是查询的条件。
5. ORDER BY 要求在查询的结果中排序，默认是升序。

Oracle 中可以把查询的结果根据结果集中的表结构和数据形成一张新表。

语法结构：根据结果集创建表

CREATE TABLE 表名 AS SELECT 语句

代码演示：根据结果集创建表

CREATE TABLE INFOS1 AS SELECT * FROM INFOS;

使用上面命令创建的新表中，不存在任何约束，并且把查询的数据一起插入到新表中。如果只复制表结构，只需使查询的条件不成立（比如 where 1=2），就不会查询从出任何数据，从而复制一个表结构。

代码演示：复制表结构

CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2;

（2）数据插入

语法结构：根据结果集创建表

INSERT INTO 表名(列名 1，列名 2……) VALUES (值 1，值 2……)

语法解析：

1.  列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。
2.  值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。

INSERT INTO INFOS VALUES ('s100102','林冲','男',22,2,TO_DATE('2009-8-9 06:30:10','YYYY-MM-DD HH24:MI:SS'),'西安','1001');

INSERT INTO INFOS VALUES ('s100104','阮小二','男',26,3,SYSDATE,default,'1001');

INSERT INTO INFOS VALUES ('s100105','卢俊义','男',24,3,SYSDATE,'青龙寺','1001');

INSERT INTO INFOS VALUES ('s100106','宋江','男',30,3,SYSDATE,DEFAULT,'1001');

alter table INFOS modify (GENDER varchar2(4));

commit;

代码解析：

• 表名后面缺省了列名，默认是表 Infos 中的所有列名，values 中的值要与表中列一一对应，包括顺序和数据类型的对应。在 SQL*Plus 中一条语句可以写在多行，那么从第二行开始，sqlplus 会为每一行前面给出行号。
• 在 Oracle 中，日期是国际化的，不同的区域安装的数据库，默认的日期格式不同，因此为了程序便于移植，日期的输入要使用 TO_DATE 函数对日期格式化后输入，采用格式化字符串对日期进行格式化时，格式化字符串中字符不区分大小写，常见的格式化字符如下：

1. yyyy 表示四位年份
2. mm 表示两位月份，比如 3 月表示为 03
3. dd 表示两位日期
4. hh24 表示小时从 0-23，hh12 也表示小时从 0-11。
5. mi 表示分钟
6. ss 表示秒

• 在遇到存在默认值的列时，可以使用 default 值代替。
• commit 是把用户操作（添加、删除、修改操作）提交，只有提交操作后，数据才能真正更新到表中，否则其他用户无法查询到当前用户操作的结果。

在 Oracle 中，一个 INSERT 命令可以把一个结果集一次性插入到一张表中。使用的语句是：INSERT INTO 表 SELECT 子句，如下示例：

代码演示：INSERT 向表中插入一个结果集

INSERT INTO INFOS2 SELECT * FROM INFOS;

在这种语法下，要求结果集中每一列的数据类型必须与表中的每一列的数据类型一致，结果集中的列的数量与表中的列的数量一致。比如表 INFOS2，该表的结构与 INFO 表一样，那么可以把 INFO 表中的所有记录一次性插入到 INFOS2 表中。

代码演示：INSERT 向表中插入一个常量结果集

INSERT INTO INFOS 
		SELECT 's100103','李逵','男',24,3,TO_DATE('2009-8-9 08:00:10','YYYY-MM-DD HH24:MI:SS'),
			'青龙寺','1001' 
		FROM DUAL;

（3）更新数据

语法结构：UPDATE 操作

UPDATE 表名 SET 列名 1=值，列名 2=值…… WHERE 条件

代码演示：UPDATE 操作

UPDATE INFOS SET CLASSNO='1002',STUADDRESS='山东莱芜' WHERE STUNAME='阮小二';

（4）删除数据

语法结构：DELETE 操作

DELETE FROM 表名 WHERE 条件

代码演示：DELETE 操作

DELETE FROM INFOS WHERE STUID='s100103';

（5）TRUNCATE

 在数据库操作中， TRUNCATE 命令（是一个 DDL 命令）可以把表中的所有数据一次性全部删除，语法是：

TRUNCATE TABLE 表名

TRUNCATE 和 DELETE 都能把表中的数据全部删除，他们的区别是：

1. TRUNCATE 是 DDL 命令，删除的数据不能恢复；DELETE 命令是 DML 命令，删除后的数据可以通过日志文件恢复。
2. 如果一个表中数据记录很多，TRUNCATE 相对 DELETE 速度快。由于 TRUNCATE 命令比较危险，因此在实际开发中，TRUNCATE 命令慎用。

PS：Oracle 默认安装中，已经创建了一个 SCOTT 用户，默认密码是：tiger，该用户下有四张表分别是：雇员表（EMP），部门表（DEPT），工资登记表和奖金表，请参考本章后面的附表。接下来很多操作都是在该用户下完成的。

5、操作符

（1）算术运算

Oracle 中的算术运算符，没有 C#中的算术运算符丰富，只有+、-、*、/四个，其中除号(/)的结果是浮点数。求余运算只能借助函数：MOD(x,y)：返回 x 除以 y 的余数。

案例3：每名员工年终奖是 2000 元，请显示基本工资在 2000 元以上的员工的月工资，年总工资。

代码演示：查询中的算术运算

（2） 关系运算和逻辑运算

Oracle 中 Where 子句经中经常见到关系运算和逻辑运算，常见的关系运算有：

运算符	说明	运算符	说明
=	等于	>	大于
<>或!=	不等于	<=	小于或者等于
<	小于	>=	大于或者等于

逻辑运算符有三个：AND、OR、NOT

（3）字符串连接操作符（||）

在 Oracle 中，字符串的连接用双竖线（||）表示。比如，在 EMP 表中，查询工资在 2000元以上的姓名以及工作。

代码解析：

• Oracle 中字符串可以用单引号，也可以用双引号，在别名中存在空格时，必须用双引号。在表名、列名时用双引号。

6、高级查询

（1）消除重复行

 在 Oracle 查询中结果中，可能出现若干行相同的情况，那么可以使用 DISTINCT 消除重复行。具体的用法如示例：

（2）NULL操作

如果某条记录中有缺少的数据值，就是空值（NULL 值）。空值不等于 0 或者空格，空值是指未赋值、未知或不可用的值。任何数据类型的列都可以包括 NULL 值，除非该列被定义为非空或者主键。

代码演示：EMP 中的 NULL 值

在查询条件中 NULL 值用 IS NULL 作条件，非 NULL 值用 NOT IS NULL 做条件。

案例 4：查询 EMP 表中没有发奖金的员工。

代码演示：NULL 值查询

（3）IN操作

在 Where 子句中可以使用 IN 操作符来查询其列值在指定的列表中的行。比如：查询出工作职责是 SALESMAN、PRESIDENT 或者 ANALYST 的员工。条件有两种表示方法：

1. WHERE job = 'SALESMAN ' OR job = 'PRESIDENT ' OR job = 'ANALYST '
2. WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST')

代码演示：IN 操作

对应 IN 操作的还有 NOT IN，用法一样，结果相反。

（4） BETWEEN...AND...

在 WHERE 子句中，可以使用 BETWEEN 操作符来查询列值包含在指定区间内的行。比如，查询工资从 1000 到 2000 之间的员工。可以使用传统方法：WHERE SAL>=1000 AND SAL<=2000；也可以使用：WHERE SAL BETWEEN 1000 AND 2000
BWTWEEN 操作所指定的范围也包括边界。

代码演示：BETWEEN 操作

（5）LIKE 模糊查询

在一些查询时，可能把握不准需要查询的确切值，比如百度搜索时输入关键字即可查询出相关的结果，这种查询称为模糊查询。模糊查询使用 LIKE 关键字通过字符匹配检索出所需要的数据行。字符匹配操作可以使用通配符“%”和“_ ”:

• %：表示零个或者多个任意字符。
• ：代表一个任意字符。

语法是：LIKE '字符串'[ESCAPE '字符']。匹配的字符串中，ESCAPE 后面的“字符”作为转义字符。与一期 SQLServer 中 ESCAPE 用法相同。

通配符表达式	说明
'S%'	以 S 开头的字符串。
'_S%'	第二个字符时 S 的字符串。
'%30\%%' escape '\'	包含“30%”的字符串，“\”指转义字符，“\%”在字符串中表示一个字符“%”。

案例 5：显示员工名称以 J 开头以 S 结尾的员工的姓名、工资和工资。

代码演示：LIKE 操作

（6）集合运算

集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

• INTERSECT(交集)，返回两个查询共有的记录。
• UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
• UNION(并集)，返回各个查询的所有记录，不包括重复记录。
• MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

  当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以不同。

案例 6：查询出 dept 表中哪个部门下没有员工。只需求出 dept 表中的部门号和 emp表中的部门号的补集即可。

前面学习过可以通过 insert into …select 把一个结果集插入到另一张结构相同的表中，因此可以使用 union 把若干条记录一次性插入到一张表中。

（7）连接查询

Oracle 中对两个表或者若干表之间的外联接用（+）表示。

案例 7：请查询出工资大于 2000 元的，员工姓名，部门，工作，工资。

由于部门名称在 dept 中，其他的信息在 emp 表中，需要内联接才能完成。

代码演示：内联接

也可以使用 SQL/92 标准中的内联接：

代码演示：内联接

这里 INNER JOIN 中，关键字 INNER 可以省略。

案例 8：请查询出每个部门下的员工姓名，工资。
案例分析：Emp 表用外键 deptno 引用 Dept 表中的 deptno，在 Dept 表中如果有某些部门没有员工，那么用内联接，没有员工的部门将无法显示，因此必须以 Dept 表为基准的外联接。

代码演示：外联接

代码解析：

• (+)：Oracle 专用的联接符，在条件中出现在左边指右外联接，出现在右边指左外联接。
• 也可以使用 SQL/92 标准的写法：

代码演示：外联接