第4章 管理数据.docx
《第4章 管理数据.docx》由会员分享，可在线阅读，更多相关《第4章 管理数据.docx（49页珍藏版）》请在冰豆网上搜索。
第4章管理数据
第4章管理数据
本章主要介绍SQL语言基础以及SQL*PLUS工具的使用。
4.1SQL语言
4.1.1概述
SQL（StructureQueryLanguage，结构化查询语言）于1975年由Boyce和Chamberlin提出，用来实现关系运算中查询、选择等操作，是一种功能极强又简单易学的语言。
作为关系数据库操作的标准语言，目前已被ANSI（美国国家标准化组织）正式批准为数据库的工业标准。
SQL语言功能极强，可以完成数据库整个生命周期的全部活动。
SQL是一种面向集合的操作语言，即操作对象和操作结果都是元组的集合。
SQL语句可以在Oracle提供的SQL*Plus或SQLDeveloper等工具中直接执行，并返回执行结果。
SQL还可以嵌入到其他高级程序设计语言中，进行前端程序的设计，实现对后台数据库的访问。
SQL语言按照功能可以分为3类：
DDL、DML和DCL。
分别介绍如下。
1．DDL
DDL（DataDefinitionLanguage，数据定义语言）：
用于新建、修改和删除数据库对象，如常用的一些SQL语句如下。
（1）CREATEDatabase：
创建数据库；
（2）CREATETablespace：
创建表空间；
（3）CREATETable：
创建表；
（4）CREATEView：
创建视图；
（5）CREATEIndex：
创建索引；
（6）ALTERDatabase：
修改数据库；
（7）ALTERTablespace：
修改表空间；
（8）ALTERTable：
修改表；
（9）DROPDatabase：
删除数据库；
（10）DROPTablespace：
删除表空间；
（11）DROPTable：
删除表；
（12）DROPView：
删除视图；
（13）DROPIndex：
删除索引。
2．DML
DML（DataManipulationLanguage，数据操作语言）：
用于对数据库中的数据进行操作，包含的SQL语句如下。
（1）SELECT：
查询数据库中的数据；
（2）INSERT：
把数据插入到数据库；
（3）UPDATE：
更新数据库中的数据；
（4）DELETE：
删除数据库中的数据。
3．DCL
DCL（DataControlLanguage，数据控制语言）：
用于控制对数据库的访问，包含的一些SQL语句如下。
（1）GRANT：
授权用户对数据库对象的操作权限；
（2）REVOKE：
撤销对用户的授权；
（3）COMMIT：
提交事务，用于事务处理；
（4）ROLLBACK：
回滚事务，用于事务处理；
（5）LOCK：
锁定数据库的某一部分，直到某一个事务完成，用于并发控制。
下面重点介绍DML语句，即SELECT语句、INSERT语句、UPDATE语句和DELETE语句。
关于DDL和DCL将在有关章节中介绍。
4.1.2使用SELECT语句查询数据
SQL语言使用SELECT语句实现对表的任何查询，包括选择符合条件的行或列及其他操作等。
常用的SELECT语法格式如下：
SELECT字段1,字段2,……
FROM表1[,表2]…
WHERE查询条件
GROUPBY分组字段1[,分组字段2]…HAVING分组条件
ORDERBY列1[,列2]……
其中，SELECT表示要选取的字段，FROM表示从哪个表查询，可以是多个表（或视图），WHERE指查询条件，GROUPBY用于分组查询，HAVING指分组条件，ORDERBY用于对查询结果进行排序。
1．单表查询
单表查询指从一个表中查询数据。
在SQL*Plus中执行下面的语句可查询DEPT表中的所有数据。
SQL>SELECT*FROMSCOTT.DEPT;
DEPTNODNAMELOC
-------------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
其中，“*”表示查询表中的所有字段。
下面的语句可查询EMP表的雇员编号、雇员姓名和工资信息。
SQL>SELECTEMPNO,ENAME,SALFROMSCOTT.EMP;
EMPNOENAMESAL
------------------------------
7100Mary
7369SMITH880
7499ALLEN1760
7521WARD1375
7566JONES3272.5
7654MARTIN1375
7698BLAKE3448.5
7782CLARK2695
7788SCOTT3300
7839KING5500
7844TURNER1650
7876ADAMS1210
7900JAMES1045
7902FORD3300
7934MILLER1430
已选择15行。
本例只对EMP表中的部分字段进行查询。
下面的语句可查询EMP表中的工作种类（Job），并且去掉重复的记录。
SQL>SELECTDISTINCTJOBFROMSCOTT.EMP;
JOB
------------------
ANALYS
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
已选择6行。
其中，DISTINCT关键字用于去掉重复记录，与之相对应的ALL关键字将保留全部记录，默认为ALL关键字。
下面的语句查询雇员姓名（ENAME）为“BLAKE”的雇员编号、姓名、工作和雇用日期。
SQL>SELECT*FROMSCOTT.EMPWHEREENAME='BLAKE';
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
-------------------------------------------------------------
7698BLAKEMANAGER783901-5月-81285030
其中，WHERE关键字用来指定查询条件。
表4-1列举了一些常用的查询条件运算符。
表4-1常用的查询条件运算符
名称
说明
=，>，<，!
=（<>），>=，<=
比较运算符。
分别是等于、大于、小于、不等于、大于等于、小于等于
in，notin
是否属于集合
betweenaandb，
notbetweenaandb
是否存在于a和b之间，包括a和b
like，notlike
是否与查询字段模式匹配，％表示任意长度的字符串，_下划线表示一个长度的字符串
isnull，isnotnull
是否为空
all
满足子查询中所有值的记录
any
满足任一查询条件为真的记录
exists
总存在一个值满足条件
some
满足集合中的某一个值
下面的语句可查询EMP表中雇员姓名以“S”开头的雇员姓名。
SQL>SELECTENAMEFROMSCOTT.EMPWHEREENAMELIKE'S％';
ENAME
----------
SMITH
SCOTT
下面的语句可查询EMP表中的雇员清单，并对姓名按字母升序排列。
SQL>SELECTENAMEFROMSCOTT.EMPORDERBYENAMEASC;
ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
已选择15行。
其中，ORDERBY关键字实现对查询结果排序，ASC选项按升序排列，DESC选项按降序排列，默认是升序排列。
下面的语句按照工作种类（JOB）分组统计EMP表中各部门的员工人数。
SQL>SELECTJOB,COUNT（*）FROMSCOTT.EMPGROUPBYJOB;
JOBCOUNT（*）
-----------------------
CLERK4
SALESMAN4
PRESIDENT1
MANAGER3
ANALYST1
已选择6行。
其中，COUNT函数用来统计符合条件的记录行数。
GROUPBY语句还可以使用HAVING子句来检查分组的各组记录是否满足条件。
HAVING只能配合GROUPBY语句使用筛选出满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，而WHERE 语句在对查询结果进行分组前，将不符合WHERE条件的行去掉，即在分组之前过滤数据，条件中不能包含聚合函数。
下面的语句带有HAVING子句，筛选出工资总额9000以上的部门编号及工资总额，可以看出HAVING条件中包含聚合函数SUM。
SQL>SELECTDEPTNO,SUM（SAL）FROMSCOTT.EMPGROUPBYDEPTNO
HAVINGSUM（SAL）>9000;
DEPTNOSUM（SAL）
----------------------------------------
309400
2010875
如在WHERE条件中包含SUM函数，执行时就会出错。
如下面的语句。
SQL>selectdeptno,sum（sal）fromscott.empwheresum（sal）>9000
第1行出现错误:
ORA-00934:
此处不允许使用分组函数
2．多表查询
多表查询是指从多个有关联的表中查询数据，其基本语法跟单表查询类似。
一般来说，多表查询的表要用等值连接联系起来，如果没有连接，则查询结果是这多个查询表的笛卡尔积。
下面的语句可查询雇员姓名和所在部门名称。
SQL>SELECTENAME,DNAMEFROMSCOTT.EMPA,SCOTT.DEPTB
WHEREA.DEPTNO=B.DEPTNO;
ENAMEDNAME
------------------------------
MILLERACCOUNTING
CLARKACCOUNTING
KINGACCOUNTING
JONESRESEARCH
SMITHRESEARCH
ADAMSRESEARCH
FORDRESEARCH
SCOTTRESEARCH
JAMESSALES
TURNERSALES
MARTINSALES
WARDSALES
ALLENSALES
BLAKESALES
已选择14行。
其中，上例为每个查询表指定了别名，便于SQL语句的书写。
下面的语句可查询SALES部门的雇员姓名。
SQL>SELECTENAMEFROMSCOTT.EMPA,SCOTT.DEPTB
WHEREA.DEPTNO=B.DEPTNOANDB.DNAME='SALES';
ENAME
--------------------
JAMES
TURNER
MARTIN
WARD
ALLEN
BLAKE
已选择6行。
3．嵌套查询
嵌套查询指一个SELECT查询中包含一个以上的子查询，所谓子查询指嵌套在另一个SELECT、INSERT、UPDATE或DELETE语句中的SELECT查询语句。
子查询的语法与SELECT语法类似，但有所限制，如子查询不能含有ORDERBY和INTO等关键字。
下面的语句也可以查询“SALES”部门的雇员姓名。
SQL>SELECTENAMEFROMSCOTT.EMPWHEREDEPTNOIN
（SELECTDEPTNOFROMSCOTT.DEPTWHEREDNAME='SALES'）;
ENAME
----------
JAMES
TURNER
MARTIN
WARD
ALLEN
BLAKE
已选择6行。
下面的语句可在EMP表中查询比平均工资高的雇员名单。
SQL>SELECTENAMEFROMSCOTT.EMPWHERESAL>
（SELECTAVG（SAL）FROMSCOTT.EMP）;
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
已选择6行。
上例中的AVG函数用来计算平均工资。
4．函数查询
SELECT查询语句可以使用函数，表4-2列出了SQL语句中常用的一些函数。
表4-2常用的函数
类别
名称
说明
集合函数
MIN
计算最小值
MAX
计算最大值
AVG
计算平均值
SUM
求和
COUNT
计算符合条件的记录总数
数值函数
ABS（X）
计算X的绝对值
MOD（X,Y）
计算X除以Y的余数
CEIL（X）
计算大于等于参数X的最小整数
FLOOR（X）
计算小于等于参数X的最大整数
POWER（X,Y）
计算以X为底的Y次幂
EXP（X）
计算e的X次幂（e=2.71828183…）
SQRT（X）
计算X的平方根
LN（X）
计算X的自然对数
ROUND（X）
四舍五入
SIN（X）
计算X的正弦值
COS（X）
计算X的余弦值
SIGN（X）
符号函数。
当X为正数，返回1；X为负数，返回-1；X为0，返回0
字符串函数
LENGTH（S）
获取字符串S的长度
CONCAT（S1,S2）
字符串连接，返回将S2添加到S1后面形成的字符串
LOWER（S）
将字符串S全部小写
UPPER（S）
将字符串S全部大写
SUBSTR（S,m,n）
截取子串，返回S中从m位置开始，长度为n的子串
REPLACE（S1，S2，S3）
将字符串S1中出现的S2都替换成S3，然后返回剩余的字符串，S3默认NULL，则所有S1中的S2字符都被删除。
INSTR（S,c）
获取字符c在字符串S中首次出现的位置
日期函数
SYSDATE
获取日期和时间
MONTHS_BETWEEN
获取两个日期之间的月份间隔
ADD_MONTHS
在指定日期上添加月份
NEXT_DAY
指定日期的下一天
LAST_DAY
每月的最后一天
类型转换函数
TO_NUMBER
转化为数值类型
TO_DATE
转化为日期类型
TO_CHAR
转化为字符类型
HEX_TO_RAW
十六进制转化为二进制
RAW_TO_HEX
二进制转化为十六进制
下面的语句可查询雇员总人数。
SQL>SELECTCOUNT（*）FROMSCOTT.EMP;
COUNT（*）
----------
15
上例使用COUNT函数计算雇员总人数。
下面的语句把查询出来的部门名称以小写字符显示出来。
SQL>SELECTLOWER（DNAME）AS"部门名称"FROMSCOTT.DEPT;
部门名称
--------------
accounting
research
sales
operations
上例使用LOWER函数把查询到的部分名城转化为小写英文字符，还使用AS关键字为查询字段指定了别名“部门名称”。
下面的语句查询“2010/01/01”和“2011/05/10”两个日期之间的月份间隔。
SQL>SELECTMONTHS_BETWEEN（TO_DATE（'2010/01/01','yyyy/mm/dd'）,
TO_DATE（'2011/05/10','yyyy/mm/dd'））AS"月份间隔"FROMDUAL;
月份间隔
----------------------
-16.290323
上例使用TO_DATE类型转换函数，将字符串转换为日期型数据。
下面的语句使用TO_NUMBER函数用于求和的示例。
SQL>SELECTTO_NUMBER（'100'）+TO_NUMBER（'200'）AS"计算结果"FROMDUAL;
计算结果
----------------------
300
4.1.3使用INSERT语句插入数据
SQL语言用INSERT语句在表中插入数据。
INSERT语句的使用语法一般有以下两种：
INSERTINTO表名[字段1,字段2,……]VALUES（值1,值2,……）
INSERTINTO表名[字段1,字段2,……]SELECT（字段1,字段2,……）FROM其他表名
其中，INSERTINTO指明要插入的表以及表中的字段，VALUES指明要插入相应字段的值。
第一条INSERT语句用于向表中插入单条记录，第二条INSERT语句用于把从其他表中查询出来的数据插入到当前表中，用于多条记录的插入。
无论是哪一种用法，都应该注意要插入的值与要插入的字段相互对应。
1．单行记录的插入
下面的语句在EMP表中插入一条记录。
SQL>INSERTINTOSCOTT.EMP
VALUES（7700,'John','ANALYS',7902,'08-9月-81',2500,'0',20）;
已创建1行。
上例在EMP表中插入一条记录，要注意VALUES子句中的插入数据与表中的字段顺序相对应。
因此例对EMP表中所有字段都插入数据，故可在EMP表名称后面省略字段列表。
如果只对EMP表中的部分字段插入数据，则需在表名称后面添加相应的字段，VALUES子句中的数据也要保持一致。
下面的语句为在EMP表中部分字段插入数据。
SQL>INSERTINTOSCOTT.EMP（EMPNO,ENAME,JOB）
VALUES（7100,'Mary','ANALYS'）
已创建1行。
2．多行记录的插入
下面的语句可新建NEWEMP表，使之与EMP表具有相同的结构，并将EMP表中全部数据插入NEWEMP表中。
SQL>CREATETABLEscott.NEWEMP
（
EMPNONUMBER（5,0）NOTNULL,
ENAMEVARCHAR2（10）,
JOBVARCHAR2（9）,
MGRNUMBER（5,0）,
HIREDATEDATE,
SALNUMBER（7,2）,
COMMNUMBER（7,2）,
DEPTNONUMBER（2,0）
）
表已创建。
然后执行下面的INSERT语句，将会把EMP表中的所有数据插入到新表NEWEMP。
SQL>INSERTINTOSCOTT.NEWEMPSELECT*FROMSCOTT.EMP;
已创建16行。
此时，如查看NEWEMP表中的记录，可以发现与EMP表中的记录完全相同。
下面的语句将EMP表中的部分字段的所有数据插入到新表NEWEMP中。
SQL>INSERTINTOSCOTT.NEWEMP（EMPNO）SELECTEMPNOFROMSCOTT.EMP;
已创建16行。
4.1.4使用UPDATE语句更新数据
SQL语言使用UPDATE语句对表中符合更新条件的记录进行更新。
UPDATE语句的一般语法如下：
UPDATE表名SET字段1=值1[,字段2=值2]……WHERE条件表达式;
其中，UPDATE后的表名指定要更新的表，SET指定要更新的字段及其相应的值，WHERE指定更新条件，如果没有指定更新条件，则对表中所有记录进行更新。
下面的语句表示雇员BLAKE加薪10％。
SQL>UPDATESCOTT.EMPSETSAL=SAL*1.1WHEREENAME='BLAKE'
已更新1行。
下面的语句表示EMP表中的所有雇员加薪10％。
SQL>UPDATESCOTT.EMPSETSAL=SAL*1.1;
已更新16行。
4.1.5使用DELETE语句删除数据
1．使用DELETE语句删除记录
SQL语言使用DELETE语句删除表中的记录，语法格式如下：
DELETEFROM表名[WHERE条件];
其中，FROM后面的表名指定要删除数据的表，WHERE子句指定要删除数据的条件。
如果没有WHERE子句，则删除表中的所有记录。
值得注意的是，使用DELETE语句删除表中数据时，并不能释放被占用的数据块空间，它只是把那些被删除的数据块标记为无效（Unused），将来还可以使用回退（Rollback）操作恢复过来。
下面的语句可删除NEWEMP表中的雇员姓名为Mary的记录。
SQL>DELETEFROMSCOTT.NEWEMPWHEREENAME='Mary';
已删除1行。
下面的语句可删除NEWEMP表中的所有记录。
SQL>DELETEFROMSCOTT.NEWEMP;
已删除16行。
2．使用TRUNCATE语句删除所有记录
SQL语言还可以使用TRUNCATE语句全部清空表中的数据但保留表结构。
使用TRUNCATE语句删除表中的数据可以释放掉那些占用的数据块，不能进行回退操作。
因此进行此操作时，一定要慎重。
TRUNCATE语句的语法格式如下：
TRUNCATETABLE表名;
下面的语句可删除NEWEMP表中的所有记录。
SQL>TRUNCATETABLESCOTT.NEWEMP;
表已截掉。
如再执行下面的语句，可以看到表中的数据已全部被清空。
SQL>SELECT*FROMSCOTT.NEWEMP;
未选定行。
4.1.6事务控制命令
1．事务概述
事务（TRANSACTION）是由一系列相关的SQL语句组成的最小逻辑工作单元。
Oracle系统以事务为单位来处理数据，用以保证数据的一致性。
对于事务中的每一个操作要么全部完成，要么全部不执行。
如果数据库发生例程故障造成正在执行事务的不一致，重新启动数据库服务器时，Oracle系统会自动恢复事务的一致性。
事务从执行一条SQL语句开始，当遇到以下情况时事务结束。
（1）执行COMMIT或ROLLBACK命令。
（2）执行DDL命令。
（3）LOGOFF。
（4）程序异常结束。
如果一个程序没有遇到以上几种情况，则在程序正常结束时将整个程序作为一个事务提交。
如在SQL*Plus中正常退出时，系统会自动提交最后一个事务。
事务可以是一组SQL命令，也可以是一条SQL语句。
这些SQL语句只能是DML语句，对于其他SQL语句，如CREATETABLE语句，一旦执行就立即提交给数据库，不可能回滚。
事务控制的命令有以下几种：
提交事务（COMMIT）、设置保留点（SAVEPOINT）、回滚事务（ROLLBACK）和设置事务（SETTRANSACTION）等。
在对数据库发出DML操作时，只有当事务提交后才确保操作完成。
在事务提交前所作的修改只有操作者本人可以查看操作结果，其他用户只有在事务提交后才能够看到。
2．提交事务
提交事务有以下3
升级会员 