第4章 管理数据孟德欣 主编 许勇 谢二莲 贺师君 副主编.docx
《第4章 管理数据孟德欣 主编 许勇 谢二莲 贺师君 副主编.docx》由会员分享，可在线阅读，更多相关《第4章 管理数据孟德欣 主编 许勇 谢二莲 贺师君 副主编.docx（30页珍藏版）》请在冰豆网上搜索。
第4章管理数据孟德欣主编许勇谢二莲贺师君副主编
第4章管理数据
4.1SQL语言
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语句中常用的一些函数。
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;
未选定行。
SQL>SETTRANSACTIOnREADONLY;
4.2SQL*Plus工具
4.2.2登录和退出SQL*Plus
登录SQL*PLUS后，输入Help命令，获取SQL*Plus命令的使用方法。
如查看SQL*Plus的命令列表，可以使用下面的语句：
SQL>helpindex
EnterHelp[topic]forhelp.
@COPYPAUSESHUTDOWN
@@DEFINEPRINTSPOOL
/DELPROMPTSQLPLUS
ACCEPTDESCRIBEQUITSTART
APPENDDISCONNECTRECOVERSTARTUP
ARCHIVELOGEDITREMARKSTORE
ATTRIBUTEEXECUTEREPFOOTERTIMING
BREAKEXITREPHEADERTTITLE
BTITLEGETRESERVEDWORDS（SQL）UNDEFINE
CHANGEHELPRESERVEDWORDS（PL/SQL）VARIABLE
CLEARHOSTRUNWHENEVEROSERROR
COLUMNINPUTSAVEWHENEVERSQLERROR
COMPUTELISTSET
CONNECTPASSWORDSHOW
如想获取具体某一个命令用法的语法格式为“HELP命令名称”，如执行下面的语句可用来查看CLEAR命令的用法。
SQL>helpclear
CLEAR
-----
Resetsorerasesthecurrentvalueorsettingforthespecifiedoption.
CL[EAR]option...
whereoptionrepresentsoneofthefollowingclauses:
BRE[AKS]
BUFF[ER]
COL[UMNS]
COMP[UTES]
SCR[EEN]
SQL
TIMI[NG]
CLEARSCREENisnotavailableiniSQL*Plus
类似的也可以使用HELP命令获取其他命令的用法。
当不再使用SQL*Plus时，可以在SQL>命令提示符后面输入quit或exit命令，然后输入回车键即可退出SQL*Plus工具。
如下图4-3所示。
图4-3退出SQL*Plus
4.2.3交互式命令
SQL*Plus命令大致可以分为交互式命令、编辑命令和报表命令。
下面分别介绍这几类命令。
SQL*Plus工具常用的交互命令请见表4-3。
表4-3SQL*Plus常用交互式命令
命令（缩写）
说明
Accept（Acc）
读取整行数据并存储在给定的一个变量中
Clear（Cl）
重置或清除当前值或某些特定选项，如清除缓冲区或屏幕内容等
Connect（Conn）
连接到数据库
Disconnect（Disc）
断开到数据库的连接
Describe（Desc）
列出表、视图或同义词的列信息以及函数和过程的定义信息
Edit（Ed）
打开文本编辑器
Host（Hos）
在SQL*Plus环境中执行主机命令
Pause（Pau）
输出一行信息，并等待用户输入回车
Prompt（Pro）
输出提示信息或空行
Sqlplus
在命令提示符下启动SQL*Plus工具
Set
修改SQL*Plus工具的系统变量值和环境变量值
Show（Sho）
显示SQL*Plus工具的系统变量或当前环境参数值
Startup
启动Oracle数据库命令
Spool
将查询结果存储到一个文件中
Shutdown
关闭数据库
Exit或Quit
退出SQL*Plus
下面介绍几种常用的交互式命令。
如下面的语句用来连接到数据库。
SQL>CONNsystem/******@orcl;
已连接。
其中，system为用户名，“******”为要输入的口令，orcl为数据库实例名。
这里使用CONN为CONNECT命令的缩写，用来连接数据库服务器，该命令的格式是:
<用户名>/<口令>@服务名
下面的语句用来显示当前的登录用户。
SQL>SHOWUSER;
USER为"SYSTEM"
下面的语句可以用来显示系统的当前时间。
SQL>SELECTSYSDATEFROMDUAL;
SYSDATE
----------
05-1月-12
其中，DUAL是Oracle系统的一个特殊表，任何用户均可读取，常用在没有目标表的SELECT语句块中。
下面的语句用来查看当前系统中的所有用户。
SQL>SELECT*FROMALL_USERS;
USERNAMEUSER_IDCREATED
--------------------------------
BI9005-9月-13
PM8905-9月-13
SH8805-9月-13
IX8705-9月-13
OE8605-9月-13
HR8505-9月-13
SCOTT8402-4月-10
................
其中，ALL_USERS是Oracle的数据字典表，存放Oracle数据库系统当前所有的用户。
下面的语句用来显示SGA（系统全局区）的大小及分配情况。
SQL>SHOWSGA
TotalSystemGlobalArea778387456bytes
FixedSize1374808bytes
VariableSize486540712bytes
DatabaseBuffers285212672bytes
RedoBuffers5259264bytes
其中，SGA指系统全局区。
下面的语句可将查询结果发送到一个文件中，同时在屏幕上显示，即假脱机输出。
SQL>SPOOLD:
\file.txt--开始假脱机输出
SQL>SHOWS
升级会员 