Oracle 数据库基础教程2007
第 14章 SQL语言基础
Oracle 数据库基础教程2007
本章内容
SQL语句分类
数据查询（ SELECT）
数据操纵（ INSERT,UPDATE,DELETE）
事务控制
SQL函数
Oracle 数据库基础教程2007
本章要求
掌握数据查询的各种应用
掌握数据操纵的各种应用
掌握事务处理
了解 SQL函数应用
Oracle 数据库基础教程2007
14.1SQL语言概述
SQL语言介绍
SQL语言的分类
SQL语言的特点
Oracle 数据库基础教程2007
14.1.1SQL语言介绍
SQL（ Structured Query Language）语言是
1974年由 Boyce 和 Chamberlin提出的。
SQL语言是关系数据库操作的基础语言，将数据查询、数据操纵、数据定义、事务控制、系统控制等功能集于一体，从而使得数据库应用开发人员、数据库管理员等都可以通过 SQL语言实现对数据库的访问和操作。
Oracle 数据库基础教程2007
14.1.2SQL语言的分类
据定义语言（ Data Definition Language,DDL）：用于定义、
修改、删除数据库对象，包括 CREATE,ALTER,DROP,GRANT，
REVOKE,AUDIT和 NOAUDIT等。
数据操纵语言（ Data Manipulation Language,DML）：用于改变数据库中的数据，包括数据插入（ INSERT）、数据修改
（ UPDATE）和数据删除（ DELETE）。
数据查询语言（ Data Query Language,DQL）：用于数据检索，
包括 SELECT。
事务控制（ Transaction Control）：用于将一组 DML操作组合起来，形成一个事务并进行事务控制。包括事务提交（ COMMIT）、
事务回滚（ ROLLBACK）、设置保存点（ SAVEPOINT）和设置事务状态（ SET TRANSACTION）。
系统控制（ System Control）：用于设置数据库系统参数，包括
ALTER SYSTEM。
会话控制（ Session Control）：用于设置用户会话相关参数，包括 ALTER SESSION。
Oracle 数据库基础教程2007
14.1.3SQL语言的特点
功能一体化：几乎涵盖了对数据库的所有操作，语言风格统一。
高度的非过程化：在使用 SQL语言操作数据库时，用户只需要说明
,做什么,,而不需要说明,怎样做,。用户任务的实现对用户而言是透明的，由系统自动完成。这大大减轻了用户的负担，同时降低了对用户的技术要求。
面向集合的操作方式,SQL语言采用集合操作方式，不仅查询结果可以是多条记录的集合，而且一次插入、删除、修改操作的对象也可以是多条记录的集合。面向集合的操作方式极大地提高了对数据操作效率。
多种使用方式,SQL语句既是自含式语言，又是嵌入式语言。 SQL语言可以直接以命令方式与数据库进行交互，也可以嵌入到其他的高级语言中使用。
简洁、易学,SQL语言命令数量有限，语法简单，接近于自然语言
（英语），因此容易学习和掌握。
Oracle 数据库基础教程2007
14.2数据查询
数据查询基础
基本查询
分组查询
连接查询
子查询
合并查询
Oracle 数据库基础教程2007
14.2.1数据查询基础
基本语法：
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_name2,… ]];
Oracle 数据库基础教程2007
14.2.2基本查询
无条件查询
有条件查询
查询排序
查询统计
Oracle 数据库基础教程2007
无条件查询
查询所有列
select * from dept;
查询指定列
select deptno,dname from dept;
select empno,sal*0.18 from emp;
使用算术表达式
Select empno,sal*0.8 from emp;
使用字符常量
Select empno,’ Name is:’,ename from emp;
使用函数
Select empno,UPPER（ ename） from emp;
Oracle 数据库基础教程2007
改变列标题
Select empno employeename,sal AS salary,job,dept
from emp;
使用连接字符串
Select ename || ‘,’ || ’ 1’ || ’ month salary=‘ ||
sal as monthy from emp
消除重复行
SQL> select [ALL] ename from emp;
SQL> select DISTINCT ename from emp;
Oracle 数据库基础教程2007
有条件查询
查询满足条件的元组可以通过 WHERE 子句实现。
WHERE条件中常用的运算符号运算符号 谓词比较大小 =,>,<,>=,<=,< >,!=
确定范围 BETWEEN AND,NOT BETWEEN AND
确定集合 IN,NOT IN
字符匹配 LIKE,NOT LIKE
空值 IS NULL,IS NOT NULL
多重条件 AND,OR
Oracle 数据库基础教程2007
关系运算
select empno,ename,sal from emp where
deptno = 10;
Select empno,ename,sal from emp where
sal>1500
确定范围
谓词 BETWEEN AND与 NOT BETWEEN AND。
select empno,ename,sal,deptno from emp
where deptno between 10 and 20
select empno,ename,sal,deptno from emp
where sal not between 1000 and 2000
Oracle 数据库基础教程2007
确定集合
谓词 IN可以用来查找属性值属于指定集合的元组。
select empno,ename,sal,deptno from
emp where deptno in (10,30);
字符匹配
%（百分号）代表任意长（长度为 0）字符串。
_（下划线）代表任意单个字符。
ESCAPE：转义字符
select empno,ename,sal,deptno from
emp where ename like 'S%';
Oracle 数据库基础教程2007
空值操作
涉及空值查询时使用 IS NULL或 IS NOT NULL，这里的 IS
不能用 =替代。
select empno,ename,sal,deptno from emp where
comm is not null
select empno,ename,sal,deptno from emp where
comm is null
Oracle 数据库基础教程2007
逻辑操作
用逻辑运算符 NOT,AND和 OR来联结多个查询条件。
优先级,NOT,AND,OR（用户可以用括号改变优先级）。
IN谓词实际上是多个 OR运算的缩写。
select empno,ename,sal,deptno from emp
where comm is not null and sal >1500;
select ename,job,sal from emp
where ( job='SALESMAN' or job=
‘ CLERK') and sal>1500;
Oracle 数据库基础教程2007
查询排序
升序、降序排序
多列排序
表达式排序
使用别名排序
使用列位置编号排序
Oracle 数据库基础教程2007
升序、降序排序
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;
Oracle 数据库基础教程2007
按表达式排序
可以按特定的表达式进行排序。
SELECT empno,ename,sal FROM emp ORDER BY
sal*12;
使用别名排序
可以使用目标列或表达式的别名进行排序。,
SELECT empno,sal*12 salary FROM emp ORDER BY
salary;
使用列位置编号排序
如果列名或表达式名称很长，那么使用位置排序可以缩短排序语句的长度。
SELECT empno,sal*12 salary FROM emp ORDER BY
2;
Oracle 数据库基础教程2007
查询统计函数 格式 功能
AVG AVG([DISTINCT|ALL] <列名 > ) 计算一列值的平均值（要求数值列）
COUNT COUNT([DISTINCT|ALL] * ) 统计元组个数
COUNT COUNT([DISTINCT|ALL] <列名
> )
统计一列中 非空值的个数
MAX MAX([DISTINCT|ALL] <列名 > ) 求一列值中的最大值
MIN MIN([DISTINCT|ALL] <列名 > ) 求一列值中的最小值
SUM SUM([DISTINCT|ALL] <列名 > ) 计算一列值的总和（要求数值列）
STDDEV STDDEV(<列名 >).,计算一列值的标准差
VARIANC
E
VARIANCE(<列名 >) 计算一列值的方差
Oracle 数据库基础教程2007
注意
除了 COUNT(*)函数外，其他的统计函数都不考虑返回值或表达式为 NULL的情况。
聚集函数只能出现在目标列表达式,ORDER BY子句、
HAVING子句中，不能出现在 WHERE子句和
GROUP BY子句中。
默认对所有的返回行进行统计，包括重复的行；如果要统计不重复的行信息，则可以使用 DISTINCT选项。
如果对查询结果进行了分组，则聚集函数的作用范围为各个组，否则聚集函数作用于整个查询结果。
Oracle 数据库基础教程2007
SELECT count(*),avg(sal),max(sal),min(sal)
FROM emp WHERE deptno=10;
SELECT avg(comm),sum(comm) FROM emp ;
SELECT count(DISTINCT deptno) FROM emp;
SELECT variance(sal),stddev(sal) FROM emp;
Oracle 数据库基础教程2007
14.2.3分组查询
基本语法
单列分组查询
多列分组查询
使用 HAVING子句限制返回组
使用 ROLLUP 和 CUBE
合并分组查询
Oracle 数据库基础教程2007
EMP
每个部门的平均薪水
2916.6667
2175
1566.6667
DEPTNO SAL
--------- ---------
10 2450
10 5000
10 1300
20 800
20 1100
20 3000
20 3000
20 2975
30 1600
30 2850
30 1250
30 950
30 1500
30 1250
DEPTNO AVG(SAL)
------- ---------
10 2916.6667
20 2175
30 1566.6667
将查询结果表按某一列或多列值分组，值相等的为一组。
分组查询
Oracle 数据库基础教程2007
语法
SELECT column,group_function,…
FROM table
[WHERE condition]
[GROUP [BY ROOLUP|CUBE|GROUPING SETS]
group_by_expression]
[HAVING group_condition]
[ORDER BY column[ASC|DESC]];
Oracle 数据库基础教程2007
GROUP BY子句用于指定分组列或分组表达式。
集合函数用于对分组进行统计。如果未对查询分组，则集合函数将作用于整个查询结果；如果对查询结果分组，则集合函数将作用于每一个组，即每一个分组都有一个集合函数。
HAVING子句用于限制分组的返回结果。
WHERE子句是对表中的记录进行过滤，而 HAVING子句是对分组后形成的组进行过滤。
在分组查询中,SELECT子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是集合函数。
Oracle 数据库基础教程2007
单列分组查询
将查询出来的记录按照某一个指定的列进行分组
SELECT deptno,count(*),avg(sal) FROM
emp GROUP BY deptno;
多列分组查询
在 GROUP BY子句中指定了两个或多个分组列
SELECT deptno,job,count(*),avg(sal)
FROM emp GROUP BY deptno,job;
Oracle 数据库基础教程2007
使用 HAVING子句限制返回组
可以使用 HAVING子句，只有满足条件的组才会返回。
SELECT deptno,count(*),avg(sal) FROM
emp GROUP BY deptno HAVING
avg(sal)>1500;
使用 ROLLUP 和 CUBE
如果在 GROUP BY子句中使用 ROLLUP选项，则还可以生成横向统计和不分组统计；
如果在 GROUP BY子句中使用 CUBE选项，则还可以生成横向统计、纵向统计和不分组统计。
Oracle 数据库基础教程2007
SELECT deptno,job,avg(sal) FROM emp
GROUP BY ROLLUP(deptno,job);
SELECT deptno,job,avg(sal) FROM emp
GROUP BY CUBE(deptno,job);
合并分组查询
使用 GROUPING SETS可以将几个单独的分组查询合并成一个分组查询
SELECT deptno,job,avg(sal) FROM emp
GROUP BY GROUPING SETS(deptno,job);
Oracle 数据库基础教程2007
14.2.4连接查询
交叉连接
内连接
等值连接
不等值连接
自身连接
外连接
左外连接
右外连接
全外连接
Oracle 数据库基础教程2007
交叉连接
以下情况可以出现交叉连接
连接条件省略
连接条件非法
一个表的所有行被连接到另一个表的所有行
为了避免笛卡尔积，一定要在 WHERE 从句中包含合法的连接条件。
SELECT ename,dname from emp,dept;
Oracle 数据库基础教程2007
内连接
执行过程首先在表 1中找到第一个元组，然后从头开始扫描表 2，逐一查找满足连接条件的元组，找到后就将表 1中的第 1个元组与该元组拼接形成结果表中的一个元组。表 2全部找完后，再找表 1中的第 2
个元组，然后再从头扫描表 2，逐一查找满足连接条件的元组，找到后就将表 1中的第 2个元组与该元组拼接形成结果表中的一个元组。重复执行，
直到表 1中的全部元组都处理完毕为止。
Oracle 数据库基础教程2007
内连接语法
SELECT … FROM tab1 [INNER] JOIN tab2 ON
condition
SELECT … FROM tab1,tab2 WHERE condition
Oracle 数据库基础教程2007
相等内连接
SELECT
empno,ename,sal,emp.deptno,dname
FROM emp JOIN dept ON emp.deptno=10
AND emp.deptno=dept.deptno;
SELECT
empno,ename,sal,emp.deptno,dname
FROM emp,dept WHERE emp.deptno=10
AND emp.deptno=dept.deptno;
Oracle 数据库基础教程2007
自身内连接
SELECT
work.empno,work.ename,manager.empno
,manager.enameFROM emp work JOIN
emp manager ON
work.mgr=manager.empno;
SELECT
work.empno,work.ename,manager.empno
,manager.ename FROM emp work,emp
manager WHERE
work.mgr=manager.empno;
Oracle 数据库基础教程2007
不相等内连接
SELECT empno,ename,sal,grade FROM
emp JOIN salgrade ON sal>losal AND
sal<hisal;
SELECT empno,ename,sal,grade FROM
emp,salgrade WHERE sal>losal AND
sal<hisal;
Oracle 数据库基础教程2007
自身内连接
SELECT worker.ename,manager.ename
FROM emp worker,emp manager
WHERE worker.mgr =manager.empno;
Oracle 数据库基础教程2007
外连接
左外连接
右外连接
全外连接
Oracle 数据库基础教程2007
左外连接
语法
SELECT table1.column,table2.column
FROM table1 LEFT JOIN table2 ON
table1.column1 = table2.column2;
SELECT table1.column,table2.column
FROM table1,table2 WHERE
table1.column = table2.column(+);
Oracle 数据库基础教程2007
SELECT dname,empno,ename FROM dept
LEFT JOIN emp
ON dept.deptno=emp.deptno AND
dept.deptno=10;
SELECT dname,empno,ename FROM
dept,emp
WHERE dept.deptno=emp.deptno(+) AND
emp.deptno(+)=10;
Oracle 数据库基础教程2007
右外连接
语法
SELECT table1.column,table2.column
FROM table1 RIGHT JOIN table2
ON table1.column1 = table2.column2;
SELECT table1.column,table2.column
FROM table1,table2
WHERE table1.column(+) = table2.column
Oracle 数据库基础教程2007
SELECT empno,ename,dname
FROM dept RIGHT JOIN emp
ON dept.deptno=emp.deptno AND
dept.deptno=20;
SELECT empno,ename,dname
FROM dept,emp
WHERE dept.deptno(+)=emp.deptno AND
dept.deptno(+)=20;
Oracle 数据库基础教程2007
全外连
语法
SELECT table1.column,table2.column
FROM table1 FULL JOIN table2
ON table1.column1 = table2.column2;
示例
SELECT EMPNO,ENAME,DNAME
FROM EMP FULL JOIN DEPT
ON EMP.DEPNTO=DEPT.DEPTNO;
Oracle 数据库基础教程2007
14.2.5子查询
概述
单行单列子查询
多行单列子查询
单行多列子查询
多行多列子查询
相关子查询
在 FROM子句中使用子查询
Oracle 数据库基础教程2007
子查询概述
子查询概念
子查询是指嵌套在其他 SQL语句中的 SELECT语句，
也称为嵌套查询 。
子查询作用
在 INSERT或 CREATE TABLE语句中使用子查询，
可以将子查询的结果写入到目标表中；
在 UPDATE语句中使用子查询可以修改一个或多个记录的数据；
在 DELETE语句中使用子查询可以删除一个或多个记录；
在 WHERE和 HAVING子句中使用子查询可以返回的一个或多个值。
Oracle 数据库基础教程2007
单行单列子查询
运算符
SELECT empno,ename,sal FROM emp
WHERE sal>(SELECT sal FROM emp WHERE
empno=7934)
多行单列子查询
运算符号
SELECT empno,ename,sal FROM emp
WHERE sal IN (SELECT sal FROM emp
WHERE deptno=10)
Oracle 数据库基础教程2007
运 算 符 含 义
IN 与子查询返回结果中任何一个值相等
NOT IN 与子查询返回结果中任何一个值都不等
>ANY 比子查询返回结果中某一个值大
=ANY 与子查询返回结果中某一个值相等
<ANY 比子查询返回结果中某一个值小
>ALL 比子查询返回结果中所有值都大
<ALL 比子查询返回结果中任何一个值都小
EXISTS 子查询至少返回一行时条件为 TRUE
NOT EXISTS 子查询不返回任何一行时条件为 TRUE
Oracle 数据库基础教程2007
单行多列子查询
SELECT empno,ename,sal,job FROM emp
WHERE (sal,job)= (SELECT sal,job FROM
emp WHERE empno=7844);
多行多列子查询
SELECT empno,ename,sal,job FROM emp
WHERE (sal,job) IN(SELECT sal,job FROM
emp WHERE deptno=10);
Oracle 数据库基础教程2007
相关子查询
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
Oracle 数据库基础教程2007
在 DDL语句中使用子查询
CREATE TABLE emp_subquery
AS
SELECT empno,ename,sal FROM emp;
Oracle 数据库基础教程2007
14.2.6 合并查询
语法
UNION
INTERSECT
MINUS
Oracle 数据库基础教程2007
语法：
SELECT query_statement1
[UNION|UNION ALL|INTERSECT|MINUS]
SELECT query_statement2;
注意：
当要合并几个查询的结果集时，这几个查询的结果集必须具有相同的列数与数据类型。
如果要对最终的结果集排序，只能在最后一个查询之后用 ORDER BY子句指明排序列。
Oracle 数据库基础教程2007
UNION
SELECT empno,ename,sal,deptno FROM
emp WHERE deptno=10
UNION
SELECT empno,ename,sal,deptno FROM
emp WHERE sal>2000ORDER BY deptno;
Oracle 数据库基础教程2007
INTERSECT
SELECT empno,ename,sal,deptno FROM emp
WHERE deptno=30
INTERSECT
SELECT empno,ename,sal,deptno FROM EMP
WHERE sal>2000;
MINUS
SELECT empno,ename,job FROM emp WHERE
deptno=30
MINUS
SELECT empno,ename,job FROM EMP WHERE
job='SALESMAN'
Oracle 数据库基础教程2007
14.3数据操纵
插入数据
修改数据
删除数据
Oracle 数据库基础教程2007
插入数据
插入 单行记录
利用子查询插入数据
向多个表中插入数据
Oracle 数据库基础教程2007
插入 单行记录
语法
INSERT INTO table_name|view_name
[(column1[,column2… ])] VALUES(value1[,values,… ])
注意
如果在 INTO子句中没有指明任何列名，则 VALUES子句中列值的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配。
如果在 INTO子句中指定了列名，则 VALUES子句中提供的列值的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对应。
向表或视图中插入的数据必须满足表的完整性约束。
字符型和日期型数据在插入时要加单引号。日期类型数据需要按系统默认格式输入，或使用 TO_DATE函数进行日期转换。
示例
INSERT INTO emp(empno,ename,sal,hiredate)
VALUES(1234,'JOAN',2500,'20-4月 -2007');
Oracle 数据库基础教程2007
利用子查询插入数据
语法
INSERT INTO
table_name|view_name[(column1[,column2,… ])
subquery;
示例
INSERT INTO emp_salary
SELECT deptno,max(sal),min(sal) FROM emp
GROUP BY deptno
Oracle 数据库基础教程2007
向多个表中插入数据
语法
INSERT ALL|FIRST
WHEN condition1 THEN INTO table1
WHEN condition2 THEN INTO table2
……
ELSE INTO tablen
subquery;
ALL表示一条记录可以同时插入到多个满足条件的表中
FIRST表示一条记录只会插入到第一个满足条件的表中
Oracle 数据库基础教程2007
示例
INSERT FIRST
WHEN deptno=10 THEN INTO emp10
WHEN deptno=20 THEN INTO emp20
WHEN deptno=30 THEN INTO emp30
ELSE INTO emp_other
SELECT * FROM emp;
Oracle 数据库基础教程2007
示例
INSERT ALL
WHEN deptno=10 THEN INTO emp10
WHEN deptno=20 THEN INTO emp20
WHEN deptno=30 THEN INTO emp30
WHEN deptno=40 THEN INTO emp_other
WHEN sal<2000 THEN INTO lowsal
WHEN sal>4000 THEN INTO highsal
ELSE INTO middlesal
SELECT * FROM emp;
Oracle 数据库基础教程2007
14.3.2修改数据
基本语法
修改单行记录
修改多行记录
带有子查询的修改
Oracle 数据库基础教程2007
基本语法
UPDATE table_name|view_name
SET column1=value1[,column2=value2… ]
[WHERE condition]
修改单条记录
UPDATE emp SET sal=sal+100,comm=200 WHERE
empno=7844;
修改多条记录
UPDATE emp SET sal=sal+150 WHERE deptno=20;
利用自查询修改记录
UPDATE emp SET sal=300+ (SELECT avg(sal)
FROM emp WHERE deptno=10)
WHERE deptno=30;
Oracle 数据库基础教程2007
14.3.3删除数据
基本语法
删除单条记录
删除多条记录
带有子查询的删除操作
利用 TRUNCATE删除数据
TRUNCATE与 DELETE区别
Oracle 数据库基础教程2007
基本语法
DELETE FROM table|view [WHERE
condition]
删除单条记录
DELETE FROM emp WHERE empno=7844;
删除多条记录
DELETE FROM emp WHERE deptno=10;
带有子查询的删除操作
DELETE FROM emp WHERE sal>(SELECT
sal FROM emp WHERE empno=7900);
Oracle 数据库基础教程2007
利用 TRUNCATE删除数据
语法
TRUNCATE TABLE table_name
TRUNCATE与 DELETE区别
释放存储空间
不写入日志文件，因此执行效率较高，但该操作不可回滚。
Oracle 数据库基础教程2007
14.4事务控制
事务概述
Oracle事务的隔离级别
Oracle事务处理
Oracle 数据库基础教程2007
14.4.1事务概述
原子性（ Atomicity）：事务是数据库的逻辑工作单位，
事务中的所有操作要么都做，要么都不做，不存在第三种情况。
一致性（ Consistency）：事务执行的结果必须是使数据库从一个一致性状态转变到另一个一致性状态，不存在中间的状态。
隔离性（ Isolation）：数据库中一个事务的执行不受其他事务干扰，每个事务都感觉不到还有其他事务在并发执行。
持久性（ Durability）：一个事务一旦提交，则对数据库中数据的改变是永久性的，以后的操作或故障不会对事务的操作结果产生任何影响。
Oracle 数据库基础教程2007
14.4.2Oracle事务的隔离级别
READ COMMITED（提交读）
SERIALIZABLE（串行化）
SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;
ALTER SESSION SET ISOLATION_LEVEL
=SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL
=READ COMMITTED
Oracle 数据库基础教程2007
14.4.3Oracle事务处理
事务提交
用户显式执行 COMMIT命令
执行特定操作时系统自动提交。
事务回滚
事务全部回滚,ROLLBACK
事务部分回滚
SAVEPOINT X
ROLLBACK TO X
Oracle 数据库基础教程2007
R O L L B A C K
R O L L B A C K T O A
R O L L B A C K T O B
事务
I N S E R T I N S E R T U P D A T E I N S E R T
S A V E P O I N T A S A V E P O I N T B
C O M M I T
Oracle 数据库基础教程2007
14.5SQL函数
SQL函数分类
数值函数
字符函数
日期函数
转换函数
其他函数
Oracle 数据库基础教程2007
数值函数函数 返回值
ABS(n) 返回 n的绝对值
CELL(n) 返回大于或等于 n的最小整数
EXP(n) 返回 e的 n次幂
FLOOR(n) 返回小于或等于 n的最大整数
LN(n) 返回以 E为底的 n的对数
LOG(m,n) 返回以 m为底的 n的对数
MOD(m,n) 返回 m除以 n的余数
PI( ) 返回常数 3.1415926535897931
POWER(m,n) 返回 m的 n次方
Oracle 数据库基础教程2007
ROUND(m[,n]) 对 m进行四舍五入（ n大于 0时，将 m四舍五入到小数点右边 n位,n等于零时，表示对 m进行取整,n小于 0时，则小数点左边的数字位置被圆整。
SIGN(n) 判断 n的正负（ n大于 0返回,n等于 0返回 0,n小于 0
返回 -1
SQRT(n) 返回 n的平方根
SQUARE(n) 返回 n的平方
TRUNC(m[,n]) 对 m进行截断操作（ n截断到小数点后第 n位，如果 n
未给出，则系统默认为 0,n也可以为负数，表示小数点左边的数字位置被删除成零。
Oracle 数据库基础教程2007
字符函数函数 返回值
ASCII(char) 字符串首字符的 ASC码值
CHR(n) ASC码值为 n的字符
CONCAT 把两个列值拼接起来；,||”操作符更通用
INITCAP(char) 将字符串中每个单词的首字母大写
INSTR（ char1,char2,a,b)
INSTRB
（ char1,char2,a,b)
返回指定字符的位置
LENGTH(char)
LENGTHB(char)
计算字符串的长度
SUBSTR(char,m[,n]) 求子串。 column中从起始位置 m开始长度为 n的子串
Replace(str1,str2,str3)
Oracle 数据库基础教程2007
LOWER(char)、
UPPER(char)
将字符串中所有的大写字母变为小 (大 )写
LPAD(char1,n[,char2]) 从左侧用字符串 char2补齐字符串 char1至长度 n（右对齐）
RPAD(char1,n[,char2]) 从右侧用 char2补齐 char1至长度 n.
LTRIM(char[,SET]) 把 char中最左侧的若干个字符去掉，以使其首字符不在 SET中
RTRIM(char[,SET]) 把 char中最右侧的若干个字符去掉，以使其尾字符不在 SET中
TRIM([leading|trailing|
both] FROM string)
Oracle 数据库基础教程2007
日期函数函数 返回值
ADD_MONTHS(d,n) 返回日期 d 添加 n 个月的日期
LAST_DAY(d) 返回 d所在月份最后一天的日期
MONTHS_BETWEEN(d1,d2) 日期 d1和 d2之间相隔的月数
NEXT_DAY(d,day) d后第一周指定的日期。
ROUND（ d,format） 日期 d按 format格式进行舍入
TRUNC（ d,format） 返回截尾到由 format指定单位的日期
CURRENT_DATE 返回系统当前日期和时间
SYSDATE 返回系统当前日期和时间。
Oracle 数据库基础教程2007
CURRENT_TIMESTAMP(P)
LOCALTIMESTAMP(P)
Oracle 数据库基础教程2007
转换函数函 数 说 明
CAST(expr AS datatype) 将表达式 expr按指定的类型返回
CHARTOROWID(char) 将字符串换转换为 ROWID类型
HEXTORAW(char) 将十六进制数转换为二进制数
RAWTOHEX(raw) 将二进制数转换为十六进制数
ROWIDTOCHAR(rowid) 将 ROWID类型转换为字符类型
TO_CHAR(d[,fmt]) 将日期 d按指定格式转换为字符串
TO_CHAR(num[,fmt]) 将数值按指定格式转换为字符串
TO_DATE(char[,fmt]) 将字符串按指定格式转换为日期
TO_CLOB(char) 将字符串转换为 CLOB类型数据
TO_NUMBER(char[,fmt]) 将字符串按指定格式转换为数值
Oracle 数据库基础教程2007
其他函数函 数 说 明
GREATEST(expr1,expr2,… ) 返回几个表达式中的最大值
LEAST(expr1,expr2,… ) 返回几个表达式中的最小值
NULLIF(expr1,expr2) 如果 expr1与 expr2相等，则函数返回
NULL，否则返回 exp1
NVL(expr1,expr2) 如果 expr1为 NULL，则返回 expr2，否则返回 expr1
NVL2(expr1,expr2,exp3) 如果 expr1为 NULL，则返回 expr3，否则返回 expr2
UID 返回当前会话的用户 ID
USER 返回当前会话的数据库用户名
DECODE(base_expr,expr1,
value1,expr2[,value2,… default])
如果 base_expr=exprn，则返回 valuen的值
Oracle 数据库基础教程2007
小结
数据查询操作：简单的单表查询、分组查询、连接查询、子查询、合并查询等
DML操作：数据的插入、更新和删除
Oracle数据库中的事务控制：事务的隔离级、事务的提交与回滚
内置的 SQL函数：数值函数、字符函数、日期函数、转换函数等。
第 14章 SQL语言基础
Oracle 数据库基础教程2007
本章内容
SQL语句分类
数据查询（ SELECT）
数据操纵（ INSERT,UPDATE,DELETE）
事务控制
SQL函数
Oracle 数据库基础教程2007
本章要求
掌握数据查询的各种应用
掌握数据操纵的各种应用
掌握事务处理
了解 SQL函数应用
Oracle 数据库基础教程2007
14.1SQL语言概述
SQL语言介绍
SQL语言的分类
SQL语言的特点
Oracle 数据库基础教程2007
14.1.1SQL语言介绍
SQL（ Structured Query Language）语言是
1974年由 Boyce 和 Chamberlin提出的。
SQL语言是关系数据库操作的基础语言，将数据查询、数据操纵、数据定义、事务控制、系统控制等功能集于一体，从而使得数据库应用开发人员、数据库管理员等都可以通过 SQL语言实现对数据库的访问和操作。
Oracle 数据库基础教程2007
14.1.2SQL语言的分类
据定义语言（ Data Definition Language,DDL）：用于定义、
修改、删除数据库对象，包括 CREATE,ALTER,DROP,GRANT，
REVOKE,AUDIT和 NOAUDIT等。
数据操纵语言（ Data Manipulation Language,DML）：用于改变数据库中的数据，包括数据插入（ INSERT）、数据修改
（ UPDATE）和数据删除（ DELETE）。
数据查询语言（ Data Query Language,DQL）：用于数据检索，
包括 SELECT。
事务控制（ Transaction Control）：用于将一组 DML操作组合起来，形成一个事务并进行事务控制。包括事务提交（ COMMIT）、
事务回滚（ ROLLBACK）、设置保存点（ SAVEPOINT）和设置事务状态（ SET TRANSACTION）。
系统控制（ System Control）：用于设置数据库系统参数，包括
ALTER SYSTEM。
会话控制（ Session Control）：用于设置用户会话相关参数，包括 ALTER SESSION。
Oracle 数据库基础教程2007
14.1.3SQL语言的特点
功能一体化：几乎涵盖了对数据库的所有操作，语言风格统一。
高度的非过程化：在使用 SQL语言操作数据库时，用户只需要说明
,做什么,,而不需要说明,怎样做,。用户任务的实现对用户而言是透明的，由系统自动完成。这大大减轻了用户的负担，同时降低了对用户的技术要求。
面向集合的操作方式,SQL语言采用集合操作方式，不仅查询结果可以是多条记录的集合，而且一次插入、删除、修改操作的对象也可以是多条记录的集合。面向集合的操作方式极大地提高了对数据操作效率。
多种使用方式,SQL语句既是自含式语言，又是嵌入式语言。 SQL语言可以直接以命令方式与数据库进行交互，也可以嵌入到其他的高级语言中使用。
简洁、易学,SQL语言命令数量有限，语法简单，接近于自然语言
（英语），因此容易学习和掌握。
Oracle 数据库基础教程2007
14.2数据查询
数据查询基础
基本查询
分组查询
连接查询
子查询
合并查询
Oracle 数据库基础教程2007
14.2.1数据查询基础
基本语法：
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_name2,… ]];
Oracle 数据库基础教程2007
14.2.2基本查询
无条件查询
有条件查询
查询排序
查询统计
Oracle 数据库基础教程2007
无条件查询
查询所有列
select * from dept;
查询指定列
select deptno,dname from dept;
select empno,sal*0.18 from emp;
使用算术表达式
Select empno,sal*0.8 from emp;
使用字符常量
Select empno,’ Name is:’,ename from emp;
使用函数
Select empno,UPPER（ ename） from emp;
Oracle 数据库基础教程2007
改变列标题
Select empno employeename,sal AS salary,job,dept
from emp;
使用连接字符串
Select ename || ‘,’ || ’ 1’ || ’ month salary=‘ ||
sal as monthy from emp
消除重复行
SQL> select [ALL] ename from emp;
SQL> select DISTINCT ename from emp;
Oracle 数据库基础教程2007
有条件查询
查询满足条件的元组可以通过 WHERE 子句实现。
WHERE条件中常用的运算符号运算符号 谓词比较大小 =,>,<,>=,<=,< >,!=
确定范围 BETWEEN AND,NOT BETWEEN AND
确定集合 IN,NOT IN
字符匹配 LIKE,NOT LIKE
空值 IS NULL,IS NOT NULL
多重条件 AND,OR
Oracle 数据库基础教程2007
关系运算
select empno,ename,sal from emp where
deptno = 10;
Select empno,ename,sal from emp where
sal>1500
确定范围
谓词 BETWEEN AND与 NOT BETWEEN AND。
select empno,ename,sal,deptno from emp
where deptno between 10 and 20
select empno,ename,sal,deptno from emp
where sal not between 1000 and 2000
Oracle 数据库基础教程2007
确定集合
谓词 IN可以用来查找属性值属于指定集合的元组。
select empno,ename,sal,deptno from
emp where deptno in (10,30);
字符匹配
%（百分号）代表任意长（长度为 0）字符串。
_（下划线）代表任意单个字符。
ESCAPE：转义字符
select empno,ename,sal,deptno from
emp where ename like 'S%';
Oracle 数据库基础教程2007
空值操作
涉及空值查询时使用 IS NULL或 IS NOT NULL，这里的 IS
不能用 =替代。
select empno,ename,sal,deptno from emp where
comm is not null
select empno,ename,sal,deptno from emp where
comm is null
Oracle 数据库基础教程2007
逻辑操作
用逻辑运算符 NOT,AND和 OR来联结多个查询条件。
优先级,NOT,AND,OR（用户可以用括号改变优先级）。
IN谓词实际上是多个 OR运算的缩写。
select empno,ename,sal,deptno from emp
where comm is not null and sal >1500;
select ename,job,sal from emp
where ( job='SALESMAN' or job=
‘ CLERK') and sal>1500;
Oracle 数据库基础教程2007
查询排序
升序、降序排序
多列排序
表达式排序
使用别名排序
使用列位置编号排序
Oracle 数据库基础教程2007
升序、降序排序
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;
Oracle 数据库基础教程2007
按表达式排序
可以按特定的表达式进行排序。
SELECT empno,ename,sal FROM emp ORDER BY
sal*12;
使用别名排序
可以使用目标列或表达式的别名进行排序。,
SELECT empno,sal*12 salary FROM emp ORDER BY
salary;
使用列位置编号排序
如果列名或表达式名称很长，那么使用位置排序可以缩短排序语句的长度。
SELECT empno,sal*12 salary FROM emp ORDER BY
2;
Oracle 数据库基础教程2007
查询统计函数 格式 功能
AVG AVG([DISTINCT|ALL] <列名 > ) 计算一列值的平均值（要求数值列）
COUNT COUNT([DISTINCT|ALL] * ) 统计元组个数
COUNT COUNT([DISTINCT|ALL] <列名
> )
统计一列中 非空值的个数
MAX MAX([DISTINCT|ALL] <列名 > ) 求一列值中的最大值
MIN MIN([DISTINCT|ALL] <列名 > ) 求一列值中的最小值
SUM SUM([DISTINCT|ALL] <列名 > ) 计算一列值的总和（要求数值列）
STDDEV STDDEV(<列名 >).,计算一列值的标准差
VARIANC
E
VARIANCE(<列名 >) 计算一列值的方差
Oracle 数据库基础教程2007
注意
除了 COUNT(*)函数外，其他的统计函数都不考虑返回值或表达式为 NULL的情况。
聚集函数只能出现在目标列表达式,ORDER BY子句、
HAVING子句中，不能出现在 WHERE子句和
GROUP BY子句中。
默认对所有的返回行进行统计，包括重复的行；如果要统计不重复的行信息，则可以使用 DISTINCT选项。
如果对查询结果进行了分组，则聚集函数的作用范围为各个组，否则聚集函数作用于整个查询结果。
Oracle 数据库基础教程2007
SELECT count(*),avg(sal),max(sal),min(sal)
FROM emp WHERE deptno=10;
SELECT avg(comm),sum(comm) FROM emp ;
SELECT count(DISTINCT deptno) FROM emp;
SELECT variance(sal),stddev(sal) FROM emp;
Oracle 数据库基础教程2007
14.2.3分组查询
基本语法
单列分组查询
多列分组查询
使用 HAVING子句限制返回组
使用 ROLLUP 和 CUBE
合并分组查询
Oracle 数据库基础教程2007
EMP
每个部门的平均薪水
2916.6667
2175
1566.6667
DEPTNO SAL
--------- ---------
10 2450
10 5000
10 1300
20 800
20 1100
20 3000
20 3000
20 2975
30 1600
30 2850
30 1250
30 950
30 1500
30 1250
DEPTNO AVG(SAL)
------- ---------
10 2916.6667
20 2175
30 1566.6667
将查询结果表按某一列或多列值分组，值相等的为一组。
分组查询
Oracle 数据库基础教程2007
语法
SELECT column,group_function,…
FROM table
[WHERE condition]
[GROUP [BY ROOLUP|CUBE|GROUPING SETS]
group_by_expression]
[HAVING group_condition]
[ORDER BY column[ASC|DESC]];
Oracle 数据库基础教程2007
GROUP BY子句用于指定分组列或分组表达式。
集合函数用于对分组进行统计。如果未对查询分组，则集合函数将作用于整个查询结果；如果对查询结果分组，则集合函数将作用于每一个组，即每一个分组都有一个集合函数。
HAVING子句用于限制分组的返回结果。
WHERE子句是对表中的记录进行过滤，而 HAVING子句是对分组后形成的组进行过滤。
在分组查询中,SELECT子句后面的所有目标列或目标表达式要么是分组列，要么是分组表达式，要么是集合函数。
Oracle 数据库基础教程2007
单列分组查询
将查询出来的记录按照某一个指定的列进行分组
SELECT deptno,count(*),avg(sal) FROM
emp GROUP BY deptno;
多列分组查询
在 GROUP BY子句中指定了两个或多个分组列
SELECT deptno,job,count(*),avg(sal)
FROM emp GROUP BY deptno,job;
Oracle 数据库基础教程2007
使用 HAVING子句限制返回组
可以使用 HAVING子句，只有满足条件的组才会返回。
SELECT deptno,count(*),avg(sal) FROM
emp GROUP BY deptno HAVING
avg(sal)>1500;
使用 ROLLUP 和 CUBE
如果在 GROUP BY子句中使用 ROLLUP选项，则还可以生成横向统计和不分组统计；
如果在 GROUP BY子句中使用 CUBE选项，则还可以生成横向统计、纵向统计和不分组统计。
Oracle 数据库基础教程2007
SELECT deptno,job,avg(sal) FROM emp
GROUP BY ROLLUP(deptno,job);
SELECT deptno,job,avg(sal) FROM emp
GROUP BY CUBE(deptno,job);
合并分组查询
使用 GROUPING SETS可以将几个单独的分组查询合并成一个分组查询
SELECT deptno,job,avg(sal) FROM emp
GROUP BY GROUPING SETS(deptno,job);
Oracle 数据库基础教程2007
14.2.4连接查询
交叉连接
内连接
等值连接
不等值连接
自身连接
外连接
左外连接
右外连接
全外连接
Oracle 数据库基础教程2007
交叉连接
以下情况可以出现交叉连接
连接条件省略
连接条件非法
一个表的所有行被连接到另一个表的所有行
为了避免笛卡尔积，一定要在 WHERE 从句中包含合法的连接条件。
SELECT ename,dname from emp,dept;
Oracle 数据库基础教程2007
内连接
执行过程首先在表 1中找到第一个元组，然后从头开始扫描表 2，逐一查找满足连接条件的元组，找到后就将表 1中的第 1个元组与该元组拼接形成结果表中的一个元组。表 2全部找完后，再找表 1中的第 2
个元组，然后再从头扫描表 2，逐一查找满足连接条件的元组，找到后就将表 1中的第 2个元组与该元组拼接形成结果表中的一个元组。重复执行，
直到表 1中的全部元组都处理完毕为止。
Oracle 数据库基础教程2007
内连接语法
SELECT … FROM tab1 [INNER] JOIN tab2 ON
condition
SELECT … FROM tab1,tab2 WHERE condition
Oracle 数据库基础教程2007
相等内连接
SELECT
empno,ename,sal,emp.deptno,dname
FROM emp JOIN dept ON emp.deptno=10
AND emp.deptno=dept.deptno;
SELECT
empno,ename,sal,emp.deptno,dname
FROM emp,dept WHERE emp.deptno=10
AND emp.deptno=dept.deptno;
Oracle 数据库基础教程2007
自身内连接
SELECT
work.empno,work.ename,manager.empno
,manager.enameFROM emp work JOIN
emp manager ON
work.mgr=manager.empno;
SELECT
work.empno,work.ename,manager.empno
,manager.ename FROM emp work,emp
manager WHERE
work.mgr=manager.empno;
Oracle 数据库基础教程2007
不相等内连接
SELECT empno,ename,sal,grade FROM
emp JOIN salgrade ON sal>losal AND
sal<hisal;
SELECT empno,ename,sal,grade FROM
emp,salgrade WHERE sal>losal AND
sal<hisal;
Oracle 数据库基础教程2007
自身内连接
SELECT worker.ename,manager.ename
FROM emp worker,emp manager
WHERE worker.mgr =manager.empno;
Oracle 数据库基础教程2007
外连接
左外连接
右外连接
全外连接
Oracle 数据库基础教程2007
左外连接
语法
SELECT table1.column,table2.column
FROM table1 LEFT JOIN table2 ON
table1.column1 = table2.column2;
SELECT table1.column,table2.column
FROM table1,table2 WHERE
table1.column = table2.column(+);
Oracle 数据库基础教程2007
SELECT dname,empno,ename FROM dept
LEFT JOIN emp
ON dept.deptno=emp.deptno AND
dept.deptno=10;
SELECT dname,empno,ename FROM
dept,emp
WHERE dept.deptno=emp.deptno(+) AND
emp.deptno(+)=10;
Oracle 数据库基础教程2007
右外连接
语法
SELECT table1.column,table2.column
FROM table1 RIGHT JOIN table2
ON table1.column1 = table2.column2;
SELECT table1.column,table2.column
FROM table1,table2
WHERE table1.column(+) = table2.column
Oracle 数据库基础教程2007
SELECT empno,ename,dname
FROM dept RIGHT JOIN emp
ON dept.deptno=emp.deptno AND
dept.deptno=20;
SELECT empno,ename,dname
FROM dept,emp
WHERE dept.deptno(+)=emp.deptno AND
dept.deptno(+)=20;
Oracle 数据库基础教程2007
全外连
语法
SELECT table1.column,table2.column
FROM table1 FULL JOIN table2
ON table1.column1 = table2.column2;
示例
SELECT EMPNO,ENAME,DNAME
FROM EMP FULL JOIN DEPT
ON EMP.DEPNTO=DEPT.DEPTNO;
Oracle 数据库基础教程2007
14.2.5子查询
概述
单行单列子查询
多行单列子查询
单行多列子查询
多行多列子查询
相关子查询
在 FROM子句中使用子查询
Oracle 数据库基础教程2007
子查询概述
子查询概念
子查询是指嵌套在其他 SQL语句中的 SELECT语句，
也称为嵌套查询 。
子查询作用
在 INSERT或 CREATE TABLE语句中使用子查询，
可以将子查询的结果写入到目标表中；
在 UPDATE语句中使用子查询可以修改一个或多个记录的数据；
在 DELETE语句中使用子查询可以删除一个或多个记录；
在 WHERE和 HAVING子句中使用子查询可以返回的一个或多个值。
Oracle 数据库基础教程2007
单行单列子查询
运算符
SELECT empno,ename,sal FROM emp
WHERE sal>(SELECT sal FROM emp WHERE
empno=7934)
多行单列子查询
运算符号
SELECT empno,ename,sal FROM emp
WHERE sal IN (SELECT sal FROM emp
WHERE deptno=10)
Oracle 数据库基础教程2007
运 算 符 含 义
IN 与子查询返回结果中任何一个值相等
NOT IN 与子查询返回结果中任何一个值都不等
>ANY 比子查询返回结果中某一个值大
=ANY 与子查询返回结果中某一个值相等
<ANY 比子查询返回结果中某一个值小
>ALL 比子查询返回结果中所有值都大
<ALL 比子查询返回结果中任何一个值都小
EXISTS 子查询至少返回一行时条件为 TRUE
NOT EXISTS 子查询不返回任何一行时条件为 TRUE
Oracle 数据库基础教程2007
单行多列子查询
SELECT empno,ename,sal,job FROM emp
WHERE (sal,job)= (SELECT sal,job FROM
emp WHERE empno=7844);
多行多列子查询
SELECT empno,ename,sal,job FROM emp
WHERE (sal,job) IN(SELECT sal,job FROM
emp WHERE deptno=10);
Oracle 数据库基础教程2007
相关子查询
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
Oracle 数据库基础教程2007
在 DDL语句中使用子查询
CREATE TABLE emp_subquery
AS
SELECT empno,ename,sal FROM emp;
Oracle 数据库基础教程2007
14.2.6 合并查询
语法
UNION
INTERSECT
MINUS
Oracle 数据库基础教程2007
语法：
SELECT query_statement1
[UNION|UNION ALL|INTERSECT|MINUS]
SELECT query_statement2;
注意：
当要合并几个查询的结果集时，这几个查询的结果集必须具有相同的列数与数据类型。
如果要对最终的结果集排序，只能在最后一个查询之后用 ORDER BY子句指明排序列。
Oracle 数据库基础教程2007
UNION
SELECT empno,ename,sal,deptno FROM
emp WHERE deptno=10
UNION
SELECT empno,ename,sal,deptno FROM
emp WHERE sal>2000ORDER BY deptno;
Oracle 数据库基础教程2007
INTERSECT
SELECT empno,ename,sal,deptno FROM emp
WHERE deptno=30
INTERSECT
SELECT empno,ename,sal,deptno FROM EMP
WHERE sal>2000;
MINUS
SELECT empno,ename,job FROM emp WHERE
deptno=30
MINUS
SELECT empno,ename,job FROM EMP WHERE
job='SALESMAN'
Oracle 数据库基础教程2007
14.3数据操纵
插入数据
修改数据
删除数据
Oracle 数据库基础教程2007
插入数据
插入 单行记录
利用子查询插入数据
向多个表中插入数据
Oracle 数据库基础教程2007
插入 单行记录
语法
INSERT INTO table_name|view_name
[(column1[,column2… ])] VALUES(value1[,values,… ])
注意
如果在 INTO子句中没有指明任何列名，则 VALUES子句中列值的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配。
如果在 INTO子句中指定了列名，则 VALUES子句中提供的列值的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对应。
向表或视图中插入的数据必须满足表的完整性约束。
字符型和日期型数据在插入时要加单引号。日期类型数据需要按系统默认格式输入，或使用 TO_DATE函数进行日期转换。
示例
INSERT INTO emp(empno,ename,sal,hiredate)
VALUES(1234,'JOAN',2500,'20-4月 -2007');
Oracle 数据库基础教程2007
利用子查询插入数据
语法
INSERT INTO
table_name|view_name[(column1[,column2,… ])
subquery;
示例
INSERT INTO emp_salary
SELECT deptno,max(sal),min(sal) FROM emp
GROUP BY deptno
Oracle 数据库基础教程2007
向多个表中插入数据
语法
INSERT ALL|FIRST
WHEN condition1 THEN INTO table1
WHEN condition2 THEN INTO table2
……
ELSE INTO tablen
subquery;
ALL表示一条记录可以同时插入到多个满足条件的表中
FIRST表示一条记录只会插入到第一个满足条件的表中
Oracle 数据库基础教程2007
示例
INSERT FIRST
WHEN deptno=10 THEN INTO emp10
WHEN deptno=20 THEN INTO emp20
WHEN deptno=30 THEN INTO emp30
ELSE INTO emp_other
SELECT * FROM emp;
Oracle 数据库基础教程2007
示例
INSERT ALL
WHEN deptno=10 THEN INTO emp10
WHEN deptno=20 THEN INTO emp20
WHEN deptno=30 THEN INTO emp30
WHEN deptno=40 THEN INTO emp_other
WHEN sal<2000 THEN INTO lowsal
WHEN sal>4000 THEN INTO highsal
ELSE INTO middlesal
SELECT * FROM emp;
Oracle 数据库基础教程2007
14.3.2修改数据
基本语法
修改单行记录
修改多行记录
带有子查询的修改
Oracle 数据库基础教程2007
基本语法
UPDATE table_name|view_name
SET column1=value1[,column2=value2… ]
[WHERE condition]
修改单条记录
UPDATE emp SET sal=sal+100,comm=200 WHERE
empno=7844;
修改多条记录
UPDATE emp SET sal=sal+150 WHERE deptno=20;
利用自查询修改记录
UPDATE emp SET sal=300+ (SELECT avg(sal)
FROM emp WHERE deptno=10)
WHERE deptno=30;
Oracle 数据库基础教程2007
14.3.3删除数据
基本语法
删除单条记录
删除多条记录
带有子查询的删除操作
利用 TRUNCATE删除数据
TRUNCATE与 DELETE区别
Oracle 数据库基础教程2007
基本语法
DELETE FROM table|view [WHERE
condition]
删除单条记录
DELETE FROM emp WHERE empno=7844;
删除多条记录
DELETE FROM emp WHERE deptno=10;
带有子查询的删除操作
DELETE FROM emp WHERE sal>(SELECT
sal FROM emp WHERE empno=7900);
Oracle 数据库基础教程2007
利用 TRUNCATE删除数据
语法
TRUNCATE TABLE table_name
TRUNCATE与 DELETE区别
释放存储空间
不写入日志文件，因此执行效率较高，但该操作不可回滚。
Oracle 数据库基础教程2007
14.4事务控制
事务概述
Oracle事务的隔离级别
Oracle事务处理
Oracle 数据库基础教程2007
14.4.1事务概述
原子性（ Atomicity）：事务是数据库的逻辑工作单位，
事务中的所有操作要么都做，要么都不做，不存在第三种情况。
一致性（ Consistency）：事务执行的结果必须是使数据库从一个一致性状态转变到另一个一致性状态，不存在中间的状态。
隔离性（ Isolation）：数据库中一个事务的执行不受其他事务干扰，每个事务都感觉不到还有其他事务在并发执行。
持久性（ Durability）：一个事务一旦提交，则对数据库中数据的改变是永久性的，以后的操作或故障不会对事务的操作结果产生任何影响。
Oracle 数据库基础教程2007
14.4.2Oracle事务的隔离级别
READ COMMITED（提交读）
SERIALIZABLE（串行化）
SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;
ALTER SESSION SET ISOLATION_LEVEL
=SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL
=READ COMMITTED
Oracle 数据库基础教程2007
14.4.3Oracle事务处理
事务提交
用户显式执行 COMMIT命令
执行特定操作时系统自动提交。
事务回滚
事务全部回滚,ROLLBACK
事务部分回滚
SAVEPOINT X
ROLLBACK TO X
Oracle 数据库基础教程2007
R O L L B A C K
R O L L B A C K T O A
R O L L B A C K T O B
事务
I N S E R T I N S E R T U P D A T E I N S E R T
S A V E P O I N T A S A V E P O I N T B
C O M M I T
Oracle 数据库基础教程2007
14.5SQL函数
SQL函数分类
数值函数
字符函数
日期函数
转换函数
其他函数
Oracle 数据库基础教程2007
数值函数函数 返回值
ABS(n) 返回 n的绝对值
CELL(n) 返回大于或等于 n的最小整数
EXP(n) 返回 e的 n次幂
FLOOR(n) 返回小于或等于 n的最大整数
LN(n) 返回以 E为底的 n的对数
LOG(m,n) 返回以 m为底的 n的对数
MOD(m,n) 返回 m除以 n的余数
PI( ) 返回常数 3.1415926535897931
POWER(m,n) 返回 m的 n次方
Oracle 数据库基础教程2007
ROUND(m[,n]) 对 m进行四舍五入（ n大于 0时，将 m四舍五入到小数点右边 n位,n等于零时，表示对 m进行取整,n小于 0时，则小数点左边的数字位置被圆整。
SIGN(n) 判断 n的正负（ n大于 0返回,n等于 0返回 0,n小于 0
返回 -1
SQRT(n) 返回 n的平方根
SQUARE(n) 返回 n的平方
TRUNC(m[,n]) 对 m进行截断操作（ n截断到小数点后第 n位，如果 n
未给出，则系统默认为 0,n也可以为负数，表示小数点左边的数字位置被删除成零。
Oracle 数据库基础教程2007
字符函数函数 返回值
ASCII(char) 字符串首字符的 ASC码值
CHR(n) ASC码值为 n的字符
CONCAT 把两个列值拼接起来；,||”操作符更通用
INITCAP(char) 将字符串中每个单词的首字母大写
INSTR（ char1,char2,a,b)
INSTRB
（ char1,char2,a,b)
返回指定字符的位置
LENGTH(char)
LENGTHB(char)
计算字符串的长度
SUBSTR(char,m[,n]) 求子串。 column中从起始位置 m开始长度为 n的子串
Replace(str1,str2,str3)
Oracle 数据库基础教程2007
LOWER(char)、
UPPER(char)
将字符串中所有的大写字母变为小 (大 )写
LPAD(char1,n[,char2]) 从左侧用字符串 char2补齐字符串 char1至长度 n（右对齐）
RPAD(char1,n[,char2]) 从右侧用 char2补齐 char1至长度 n.
LTRIM(char[,SET]) 把 char中最左侧的若干个字符去掉，以使其首字符不在 SET中
RTRIM(char[,SET]) 把 char中最右侧的若干个字符去掉，以使其尾字符不在 SET中
TRIM([leading|trailing|
both] FROM string)
Oracle 数据库基础教程2007
日期函数函数 返回值
ADD_MONTHS(d,n) 返回日期 d 添加 n 个月的日期
LAST_DAY(d) 返回 d所在月份最后一天的日期
MONTHS_BETWEEN(d1,d2) 日期 d1和 d2之间相隔的月数
NEXT_DAY(d,day) d后第一周指定的日期。
ROUND（ d,format） 日期 d按 format格式进行舍入
TRUNC（ d,format） 返回截尾到由 format指定单位的日期
CURRENT_DATE 返回系统当前日期和时间
SYSDATE 返回系统当前日期和时间。
Oracle 数据库基础教程2007
CURRENT_TIMESTAMP(P)
LOCALTIMESTAMP(P)
Oracle 数据库基础教程2007
转换函数函 数 说 明
CAST(expr AS datatype) 将表达式 expr按指定的类型返回
CHARTOROWID(char) 将字符串换转换为 ROWID类型
HEXTORAW(char) 将十六进制数转换为二进制数
RAWTOHEX(raw) 将二进制数转换为十六进制数
ROWIDTOCHAR(rowid) 将 ROWID类型转换为字符类型
TO_CHAR(d[,fmt]) 将日期 d按指定格式转换为字符串
TO_CHAR(num[,fmt]) 将数值按指定格式转换为字符串
TO_DATE(char[,fmt]) 将字符串按指定格式转换为日期
TO_CLOB(char) 将字符串转换为 CLOB类型数据
TO_NUMBER(char[,fmt]) 将字符串按指定格式转换为数值
Oracle 数据库基础教程2007
其他函数函 数 说 明
GREATEST(expr1,expr2,… ) 返回几个表达式中的最大值
LEAST(expr1,expr2,… ) 返回几个表达式中的最小值
NULLIF(expr1,expr2) 如果 expr1与 expr2相等，则函数返回
NULL，否则返回 exp1
NVL(expr1,expr2) 如果 expr1为 NULL，则返回 expr2，否则返回 expr1
NVL2(expr1,expr2,exp3) 如果 expr1为 NULL，则返回 expr3，否则返回 expr2
UID 返回当前会话的用户 ID
USER 返回当前会话的数据库用户名
DECODE(base_expr,expr1,
value1,expr2[,value2,… default])
如果 base_expr=exprn，则返回 valuen的值
Oracle 数据库基础教程2007
小结
数据查询操作：简单的单表查询、分组查询、连接查询、子查询、合并查询等
DML操作：数据的插入、更新和删除
Oracle数据库中的事务控制：事务的隔离级、事务的提交与回滚
内置的 SQL函数：数值函数、字符函数、日期函数、转换函数等。
