第4章 管理数据Word下载.docx
- 文档编号:16201653
- 上传时间:2022-11-21
- 格式:DOCX
- 页数:49
- 大小:1,004.23KB
第4章 管理数据Word下载.docx
《第4章 管理数据Word下载.docx》由会员分享,可在线阅读,更多相关《第4章 管理数据Word下载.docx(49页珍藏版)》请在冰豆网上搜索。
(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表的雇员编号、雇员姓名和工资信息。
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),并且去掉重复的记录。
SELECTDISTINCTJOBFROMSCOTT.EMP;
JOB
------------------
ANALYS
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
已选择6行。
其中,DISTINCT关键字用于去掉重复记录,与之相对应的ALL关键字将保留全部记录,默认为ALL关键字。
下面的语句查询雇员姓名(ENAME)为“BLAKE”的雇员编号、姓名、工作和雇用日期。
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”开头的雇员姓名。
SELECTENAMEFROMSCOTT.EMPWHEREENAMELIKE'
S%'
ENAME
----------
SMITH
SCOTT
下面的语句可查询EMP表中的雇员清单,并对姓名按字母升序排列。
SELECTENAMEFROMSCOTT.EMPORDERBYENAMEASC;
ENAME
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
TURNER
WARD
其中,ORDERBY关键字实现对查询结果排序,ASC选项按升序排列,DESC选项按降序排列,默认是升序排列。
下面的语句按照工作种类(JOB)分组统计EMP表中各部门的员工人数。
SELECTJOB,COUNT(*)FROMSCOTT.EMPGROUPBYJOB;
JOBCOUNT(*)
-----------------------
CLERK4
SALESMAN4
PRESIDENT1
MANAGER3
ANALYST1
其中,COUNT函数用来统计符合条件的记录行数。
GROUPBY语句还可以使用HAVING子句来检查分组的各组记录是否满足条件。
HAVING只能配合GROUPBY语句使用筛选出满足条件的组,即在分组之后过滤数据,条件中经常包含聚合函数,而WHERE
语句在对查询结果进行分组前,将不符合WHERE条件的行去掉,即在分组之前过滤数据,条件中不能包含聚合函数。
下面的语句带有HAVING子句,筛选出工资总额9000以上的部门编号及工资总额,可以看出HAVING条件中包含聚合函数SUM。
SELECTDEPTNO,SUM(SAL)FROMSCOTT.EMPGROUPBYDEPTNO
HAVINGSUM(SAL)>
9000;
DEPTNOSUM(SAL)
----------------------------------------
309400
2010875
如在WHERE条件中包含SUM函数,执行时就会出错。
如下面的语句。
selectdeptno,sum(sal)fromscott.empwheresum(sal)>
9000
第1行出现错误:
ORA-00934:
此处不允许使用分组函数
2.多表查询
多表查询是指从多个有关联的表中查询数据,其基本语法跟单表查询类似。
一般来说,多表查询的表要用等值连接联系起来,如果没有连接,则查询结果是这多个查询表的笛卡尔积。
下面的语句可查询雇员姓名和所在部门名称。
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部门的雇员姓名。
SELECTENAMEFROMSCOTT.EMPA,SCOTT.DEPTB
WHEREA.DEPTNO=B.DEPTNOANDB.DNAME='
SALES'
ENAME
--------------------
JAMES
TURNER
MARTIN
WARD
ALLEN
BLAKE
3.嵌套查询
嵌套查询指一个SELECT查询中包含一个以上的子查询,所谓子查询指嵌套在另一个SELECT、INSERT、UPDATE或DELETE语句中的SELECT查询语句。
子查询的语法与SELECT语法类似,但有所限制,如子查询不能含有ORDERBY和INTO等关键字。
下面的语句也可以查询“SALES”部门的雇员姓名。
SELECTENAMEFROMSCOTT.EMPWHEREDEPTNOIN
(SELECTDEPTNOFROMSCOTT.DEPTWHEREDNAME='
);
下面的语句可在EMP表中查询比平均工资高的雇员名单。
SELECTENAMEFROMSCOTT.EMPWHERESAL>
(SELECTAVG(SAL)FROMSCOTT.EMP);
JONES
CLARK
SCOTT
KING
FORD
上例中的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
二进制转化为十六进制
下面的语句可查询雇员总人数。
SELECTCOUNT(*)FROMSCOTT.EMP;
COUNT(*)
----------
15
上例使用COUNT函数计算雇员总人数。
下面的语句把查询出来的部门名称以小写字符显示出来。
SELECTLOWER(DNAME)AS"
部门名称"
FROMSCOTT.DEPT;
部门名称
--------------
accounting
research
sales
operations
上例使用LOWER函数把查询到的部分名城转化为小写英文字符,还使用AS关键字为查询字段指定了别名“部门名称”。
下面的语句查询“2010/01/01”和“2011/05/10”两个日期之间的月份间隔。
SELECTMONTHS_BETWEEN(TO_DATE('
2010/01/01'
'
yyyy/mm/dd'
),
TO_DATE('
2011/05/10'
'
))AS"
月份间隔"
FROMDUAL;
月份间隔
----------------------
-16.290323
上例使用TO_DATE类型转换函数,将字符串转换为日期型数据。
下面的语句使用TO_NUMBER函数用于求和的示例。
SELECTTO_NUMBER('
100'
)+TO_NUMBER('
200'
)AS"
计算结果"
计算结果
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表中插入一条记录。
INSERTINTOSCOTT.EMP
VALUES(7700,'
John'
'
ANALYS'
7902,'
08-9月-81'
2500,'
0'
20);
已创建1行。
上例在EMP表中插入一条记录,要注意VALUES子句中的插入数据与表中的字段顺序相对应。
因此例对EMP表中所有字段都插入数据,故可在EMP表名称后面省略字段列表。
如果只对EMP表中的部分字段插入数据,则需在表名称后面添加相应的字段,VALUES子句中的数据也要保持一致。
下面的语句为在EMP表中部分字段插入数据。
INSERTINTOSCOTT.EMP(EMPNO,ENAME,JOB)
VALUES(7100,'
Mary'
)
2.多行记录的插入
下面的语句可新建NEWEMP表,使之与EMP表具有相同的结构,并将EMP表中全部数据插入NEWEMP表中。
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。
INSERTINTOSCOTT.NEWEMPSELECT*FROMSCOTT.EMP;
已创建16行。
此时,如查看NEWEMP表中的记录,可以发现与EMP表中的记录完全相同。
下面的语句将EMP表中的部分字段的所有数据插入到新表NEWEMP中。
INSERTINTOSCOTT.NEWEMP(EMPNO)SELECTEMPNOFROMSCOTT.EMP;
4.1.4使用UPDATE语句更新数据
SQL语言使用UPDATE语句对表中符合更新条件的记录进行更新。
UPDATE语句的一般语法如下:
UPDATE表名SET字段1=值1[,字段2=值2]……WHERE条件表达式;
其中,UPDATE后的表名指定要更新的表,SET指定要更新的字段及其相应的值,WHERE指定更新条件,如果没有指定更新条件,则对表中所有记录进行更新。
下面的语句表示雇员BLAKE加薪10%。
UPDATESCOTT.EMPSETSAL=SAL*1.1WHEREENAME='
已更新1行。
下面的语句表示EMP表中的所有雇员加薪10%。
UPDATESCOTT.EMPSETSAL=SAL*1.1;
已更新16行。
4.1.5使用DELETE语句删除数据
1.使用DELETE语句删除记录
SQL语言使用DELETE语句删除表中的记录,语法格式如下:
DELETEFROM表名[WHERE条件];
其中,FROM后面的表名指定要删除数据的表,WHERE子句指定要删除数据的条件。
如果没有WHERE子句,则删除表中的所有记录。
值得注意的是,使用DELETE语句删除表中数据时,并不能释放被占用的数据块空间,它只是把那些被删除的数据块标记为无效(Unused),将来还可以使用回退(Rollback)操作恢复过来。
下面的语句可删除NEWEMP表中的雇员姓名为Mary的记录。
DELETEFROMSCOTT.NEWEMPWHEREENAME='
已删除1行。
下面的语句可删除NEWEMP表中的所有记录。
DELETEFROMSCOTT.NEWEMP;
已删除16行。
2.使用TRUNCATE语句删除所有记录
SQL语言还可以使用TRUNCATE语句全部清空表中的数据但保留表结构。
使用TRUNCATE语句删除表中的数据可以释放掉那些占用的数据块,不能进行回退操作。
因此进行此操作时,一定要慎重。
TRUNCATE语句的语法格式如下:
TRUNCATETABLE表名;
TRUNCATETABLESCOTT.NEWEMP;
表已截掉。
如再执行下面的语句,可以看到表中的数据已全部被清空。
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第4章 管理数据 管理 数据