oracle习题_动视_懂你更懂生活

oracle习题

2025-09-24 00:20:22 责编:小OO

点击下载本文 文档为doc格式

1. /*1、选择在部门30中员工的所有信息*/

2. select * from scott.emp where deptno = '30'

3. /*2、列出职位为（MANAGER）的员工的编号，姓名 */

4. select empno, ename from scott.emp where job = 'MANAGER'

5. /*3、找出奖金高于工资的员工*/

6. select * from scott.emp where comm > sal

7. /*4、找出每个员工奖金和工资的总和 */

8. select ename, sal + nvl(comm, 0) from scott.emp

9. /*5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) */

10. select *

11. from scott.emp

12. where deptno = '10'

13. and job = 'MANAGER'

14. union

15. select *

16. from scott.emp

17. where job = 'CLERK'

18. and deptno = '20'

19. /*6、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工 */

20. select *

21. from scott.emp

22. where job != 'MANAGER'

23. and job != 'CLERK'

24. and sal > 2000

25. /*7、找出有奖金的员工的不同工作 */

26. select distinct(job) from scott.emp where comm is not null

27. /*8、找出没有奖金或者奖金低于500的员工*/

28. select *

29. from scott.emp

30. where comm is not null

31. and comm > 500

32. /*9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 */

33. select ename

34. from scott.emp

35. order by (months_between(sysdate, hiredate) / 12) desc

36.

37. select ename,hiredate from scott.emp order by hiredate

38. /*10、找出每个月倒数第三天受雇的员工*/

39. select * from scott.emp where hiredate = last_day(hiredate) - 2

40. /*11、分别用case和decode函数列出员工所在的部门，deptno=10显示'部门10',

41. deptno=20显示'部门20'

42. deptno=30显示'部门30'

43. deptno=40显示'部门40'

44. 否则为'其他部门'*/

45. select ename,

46. case deptno

47. when 10 then

48. '部门10'

49. when 20 then

50. '部门20'

51. when 30 then

52. '部门30'

53. when 40 then

54. '部门40'

55. else

56. '其他部门'

57. end 工资情况

58. from scott.emp

59.

60. select ename,

61. decode(deptno,

62. 10,

63. '部门10',

. 20,

65. '部门20',

66. 30,

67. '部门30',

68. 40,

69. '部门40',

70. '其他部门') 工资情况

71. from scott.emp

72. /*12、分组统计各部门下工资>500的员工的平均工资*/

73. select avg(sal) from scott.emp where sal > 500 group by deptno

74. /*13、统计各部门下平均工资大于500的部门*/

75. select deptno from scott.emp group by deptno having avg(sal) > 500

76. /*14、算出部门30中得到最多奖金的员工奖金 */

77. select max(comm) from scott.emp where deptno = 30

78. /*15、算出部门30中得到最多奖金的员工姓名*/

79. select ename

80. from scott.emp

81. where deptno = 30

82. and comm = (select max(comm) from scott.emp where deptno = 30)

83. /*16、算出每个职位的员工数和最低工资*/

84. select count(ename), min(sal), job from scott.emp group by job

85. /*17、列出员工表中每个部门的员工数，和部门no */

86. select count(ename), deptno from scott.emp group by deptno

87. /*18、得到工资大于自己部门平均工资的员工信息*/

88. select *

. from scott.emp e

90. where sal > (select avg(sal) from scott.emp where e.deptno = deptno)

91.

92. select *

93. from scott.emp e1,

94. (select avg(sal) sals, deptno from scott.emp group by deptno) e2

95. where sal > sals

96. and e1.deptno = e2.deptno

97. /*19、分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金) */

98. select avg(nvl(comm,0)), sum(sal + nvl(comm, 0))

99. from scott.emp

100. group by deptno,job

101. /*20、笛卡尔集*/

102. select * from scott.emp, scott.dept

103. /*21、显示员工ID,名字，直属主管ID，名字*/

104. select empno,

105. ename,

106. mgr,

107. (select ename from scott.emp e1 where e1.empno = e2.mgr) 直属主管名字

108. from scott.emp e2

109. /*22、DEPT表按照部门跟EMP表左关联*/

110. select *

111. from scott.dept, scott.emp

112. where scott.dept.deptno = scott.emp.deptno(+)

113. /*23、使用此语句重复的内容不再显示了*/

114. select distinct (job) from scott.emp

115. /*24、重复的内容依然显示 */

116. select *

117. from scott.emp

118. UNION ALL

119. select * from scott.emp

120. /*23和24题和22题是一样的 */

121.

122. /*25、只显示了两个表中彼此重复的记录。*/

123. select *

124. from scott.dept, scott.emp

125. where scott.dept.deptno(+) = scott.emp.deptno

126. /*26、只显示了两张表中的不同记录*/

127. select * from scott.emp union select * from scott.emp

128. minus

129. (select * from scott.emp intersect select * from scott.emp)

130.

131. (select * from scott.emp minus select * from scott.emp)

132. union

133. (select * from scott.emp minus select * from scott.emp)

134. 表结构相同先union 只能有 -

135. /*27、列出员工表中每个部门的员工数，和部门no */

136. select count(ename), deptno from scott.emp group by deptno

137. /*28、列出员工表中每个部门的员工数（员工数必须大于3），和部门名称*/

138. select count(deptno),

139. deptno,

140. (select dname from scott.dept where scott.dept.deptno = e1.deptno)

141. from scott.emp e1

142. group by deptno having count(deptno)>3

143. /*29、找出工资比jones多的员工*/

144. select *

145. from scott.emp

146. where sal > (select sal from scott.emp where ename = 'JONES')

147. /*30、列出所有员工的姓名和其上级的姓名 */

148. select ename,

149. (select ename from scott.emp e1 where e1.empno = e2.mgr) 上级的姓名

150. from scott.emp e2

151. /*31、以职位分组，找出平均工资最高的两种职位 */

152. select job

153. from scott.emp

154. group by job

155. having avg(sal) in (select max(sal) from scott.emp group by job )

156.

157. select job

158. from (select job, avg(sal)

159. from scott.emp

160. group by job

161. order by avg(sal) desc)

162. where rownum <= 2

163.

1. 最大的：

165. select max(max_sal)

166. from (select job, avg(sal) max_sal from scott.emp group by job)

167. /*32、查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称*/

168.

169. select ename, dname

170. from scott.emp e1, scott.dept e2

171. where e1.deptno = e2.deptno

172. and e1.deptno <> 20

173. and sal > (select max(sal) from scott.emp where deptno = '20')

174.

175. /*33、得到平均工资大于2000的工作职种 */

176. select job from scott.emp group by job having avg(sal) > 2000

177. /*34、分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500 */

178. select avg(sal)

179. from scott.emp

180. where sal > 2000

181. group by deptno

182. having avg(sal) > 2500

183. /*35、得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 */

184. select deptno, dname, loc

185. from scott.dept

186. where deptno in (select deptno

187. from scott.emp

188. group by deptno

1. having sum(sal) = (select min(sum(sal))

190. from scott.emp

191. group by deptno))

192.

193. select * from scott.dept

194. /*36、分部门得到平均工资等级为2级（等级表）的部门编号 */

195. select deptno

196. from scott.emp

197. group by deptno

198. having avg(sal) between (select losal from scott.salgrade where grade = 2) and (select hisal

199. from scott.salgrade

200. where grade = 2)

201.

202. select avg(sal) from scott.emp group by deptno

203. select * from scott.salgrade

204. /*37、查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置*/

205. select a.ename, dname, loc

206. from (select *

207. from (select rownum rn, deptno, empno, sal, ename

208. from (select deptno, empno, sal, ename

209. from scott.emp

210. where deptno in (10, 20)

211. and rownum <= 5

212. order by sal desc))

213. where rn between 3 and 5) a,

214. scott.dept b

215. where a.deptno = b.deptno

216.

217.

218. select deptno, ename

219. from (select empno, deptno, ename

220. from (select rownum rn, deptno, empno, sal, ename

221. from (select deptno, empno, sal, ename

222. from scott.emp

223. where deptno in (10, 20)

224. order by sal desc))

225. where rn between 3 and 5)

226.

227.

228. /*38、查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入*/

229. select empno, ename, sal + nvl(comm, 0)

230. from scott.emp e1

231. where sal + nvl(comm, 0) >

232. (select sal + nvl(comm, 0) from scott.emp where empno = e1.mgr)

233.

234. select * from scott.emp

235. select ename, sal + nvl(comm, 0) from scott.emp

236. /*39、查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资 */

237. select avg(sal)

238. from scott.emp

239. where job in (select job

240. from scott.emp

241. where ename = 'MARTIN'

242. or ename = 'SMITH')

243. /*40、查找出不属于任何部门的员工 */

244. select * from scott.emp where deptno is null

245. select * from scott.emp where deptno not in (select deptno from scott.emp)

246. /*41、按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）*/

247. select dname, loc

248. from (select *

249. from (select rownum rn, deptno

250. from (select deptno, count(*)

251. from scott.emp

252. group by deptno

253. order by count(*) desc))

254. where rn between 2 and 5) a,

255. scott.dept b

256. where a.deptno = b.deptno

257.

258. select count(*) from scott.emp group by deptno

259. /*42、查询出king所在部门的部门号\部门名称\部门人数（多种方法）*/

260. select sc.deptno, dname, count(*)

261. from scott.emp sc, scott.dept de

262. where sc.deptno = ((select deptno from scott.emp where ename = 'KING'))

263. and de.deptno = sc.deptno

2. group by sc.deptno, dname

265.

266.

267. /*43、查询出king所在部门的工作年限最大的员工名字*/

268. select *

269. from scott.emp

270. where hiredate =

271. (select min(hiredate)

272. from scott.emp

273. where deptno in (select deptno from scott.emp where ename = 'KING'))

274. and deptno = (select deptno from scott.emp where ename = 'KING')

275. /*44、查询出工资成本最高的部门的部门号和部门名称 */

276. select deptno, dname

277. from scott.dept

278. where deptno = (select deptno

279. from scott.emp

280. group by deptno

281. having sum(sal) = (select max(sum(sal))

282. from scott.emp

283. group by deptno))

284.

285. select * from scott.emp for update

使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

工资＝薪金＋佣金

1．列出至少有一个员工的所有部门。

select deptno from emp group by deptno having count(empno)>1 ;

2．列出薪金比“SMITH”多的所有员工。

select empno from emp where sal> (select sal from emp where ename ='SMITH');

3．列出所有员工的姓名及其直接上级的姓名。

select a.ename,

(select ename from emp where empno= a.mgr) mgrname from emp a;

自连接

select a.ename,b.ename mgrname from emp a,emp b

where a.mgr = b.empno;

4．列出受雇日期早于其直接上级的所有员工。

select a.empno ,a.ename from emp a,emp b

where a.mgr = b.empno and (b.hiredate-a.hiredate)>0;

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

select dname,empno,ename,job,mgr,hiredate,sal,comm from emp , dept where emp.deptno(+) = dept.deptno;

6．列出所有“CLERK”（办事员）的姓名及其部门名称。

select ename,dname from emp,dept where emp.deptno=dept.deptno and job='CLERK';

7．列出最金大于1500的各种工作。

select distinct job from emp group by job having min(sal)>1500;

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

select ename from emp,dept where emp.deptno(+)=dept.deptno and dname='SALES';

9．列出薪金高于公司平均薪金的所有员工。

select empno,ename from emp where sal> (select avg(sal) from emp);

10．列出与“SCOTT”从事相同工作的所有员工。

select empno,ename from emp where job=(select job from emp where ename='SCOTT') and ename !='SCOTT';

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

select ename,sal from emp where sal in (select sal from emp where deptno='30') and deptno !='30';

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

select ename,sal from emp where sal>(select sum(sal) from emp where deptno='30');

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

select count(empno),avg(sal) ,deptno ,avg((sysdate - hiredate)/365) from emp group by deptno;

14．列出所有员工的姓名、部门名称和工资。

select ename,dname ,sal+nvl(emp.comm,0) from emp,dept where emp.deptno=dept.deptno;

15．列出从事同一种工作但属于不同部门的员工的一种组合。

select a.ename,b.ename

from emp a,emp b

where a.job=b.job and a.deptno!=b.deptno and a.ename!=b.ename;

////////16．列出所有部门的详细信息和部门人数。

select a.*,(select count(*) from emp where deptno = a.deptno) rs from dept a;

17．列出各种工作的最低工资。

select min(nvl(sal+comm,sal)) ,job from emp group by job;

18．列出各个部门的MANAGER（经理）的最金。

select min(sal) ,deptno from emp where job='MANAGER' group by deptno

19．列出所有员工的年工资,按年薪从低到高排序。

select 12* nvl(sal+comm,sal) from emp order by sal asc;

-----orcle的等连接

SELECT * FROM EMP E ,DEPT D WHERE E.DEPTNO=D.DEPTNO;

-----orcla的外连接

SELECT * FROM EMP E ,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;

+放在没有匹配行的表一侧，所以dept表的记录完全显示

一.Oracle数据库中常用的数据类型

varchar2(长度)可变长字符串

char(长度) 定长

number()表示整数或者浮点数number(8) number(8,2)

clog 字符的大对象

blog 二进制的大对象

二.数据库查询

1）SELECT语句

从表中提取查询数据.语法为SELECT [DISTINCT] {column1,column2,…} FROM tablename WHERE {conditions} GROUP BY {conditions} ORDER BY {expressions} [ASC/DESC];

说明：SELECT子句用于指定检索数据库的中哪些列，FROM子句用于指定从哪一个表或视图中检索数据。

2）WHERE子句。

WHERE子句用来选择符合条件的的记录.

like '...' 通配查询 _,%

between ... and ... ,表示结果在这之间，between and是一个闭区间。

!=，<>，^=，这三个都可以表示不等于。

in (va1,val2,...) 判断结果是否在这个集合中存在。

like '...' 字符串通配查询，'%'表示0或多个字符，'_'表示一个字符。

... and ... 表示只有两个条件同时满足

... or ... 表示条件只要满足其中之一就可以

all ... 是要求都满足条件。

not .....，则是可以与以上的条件产生反效果。

... is null 使用来判断值是不是空。

3) ORDER BY子句

ORDER BY 子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。

ASC（默认，升序） DESC（降序）

order by 目标列名（别名）排序顺序（不写排序顺序，会默认为升序排序）

例：select first_name from s_emp order by first_name;

select first_name from s_emp order by first_name desc;

三.SQL常用的命令分类及例子

数据定义语言：create（创建）、alter（更改）和drop（删除）命令。

数据操纵语言：insert（插入）、select（选择）、delete（删除）和update（更新）命令。

事务控制语言：commit（提交）、savepoint（保存点）和rollback（回滚）命令。

数据控制语言：grant（授予）和revoke（回收）。

1.数据定义语言举例：

SQL> create table myTab(no number(4),name varchar2(20));创建一个名为myTab的表，包含两列no和name；

SQL> alter table myTab modify (name varchar2(25));修改myTab中的name列，使此列能容纳25个字符；

SQL> alter table myTab add (tel_no varchar2(20));给表myTab增加一列tel_no；

SQL> alter table myTab drop column tel_no;删除表myTab的tel_no列;

SQL> drop table myTab;删除表myTab；

SQL> truncate table myTab;删除表myTab中的所有行（截断表）,注意:不可以回滚。

2.数据操纵语言举例：

SQL> insert into myTab values(‘001’,’John’);

向表myTab中插入一行数据；

SQL> select distinct salary “薪水” from s_emp where salary>1500 order by sal desc;

选择表中salary大于1500的数据，以别名“薪水”显示并按照salary的降序进行排列输出；

SQL> create table empa as select empno,ename,job,sal from emp;

从emp表中选择“empno,ename,job,sal”四列的数据建立新表empa;

SQL> create table empa as select * from emp where 1=2;

使用一个假条件根据现有表emp创建一个只包含结构的空表empa；

SQL> delete from empa where sal<1500;

删除表empa中sal小于1500的行；

SQL> update empa set sal=1500 where sal<1500;更新，将表empa中sal小于1500的行的sal值全部改为1500。

3.事务控制语言举例：

SQL> commit;用于提交并结束事务处理；

SQL> savepoint mark1;保存点类似于标记，用来标记事务中可以应用回滚的点；

SQL> rollback to savepoint mark1;回滚到保存点mark1。

四.Oracle数据库函数

注意:dual表(虚表)是专门用于函数测试和运算的.

1.字符函数

字符是大小写敏感的

转小写 lower(字段名)

转大写 upper(字段名)

首字母大写 initcap(字段名)

字符串拼接 concat(字段1, 字段2)

截取子串 substr(字段名, 起始位置，取字符个数)

例: select first_name,substr(first_name,2,2) sub from s_emp;（从名字的第二个字符开始取两个字符）

select first_name,substr(first_name,-2,2) sub from s_emp;（从名字的倒数第二个字符开始取两个字符）

2.数值函数

四舍五入函数 round(数据,保留到小数点后几位)

1表示保留到小数点后一位，-1表示保留到小数点前一位。

例：select round(15.36,1) from dual;

截取数值函数 trunc(数据，保留到小数点后几位)

例：select trunc(123.456,1) from dual;

截取到小数点后一位,注意:与round函数不同,不会四舍五入。

3.日期函数

缺省日期格式，日-月-年 dd-mon-rr

修改当前会话的日期格式，会按照指定的格式输出日期

alter session set nls_date_format='yyyy mm dd hh24:mi:ss';

返回当前日期 sysdate

例：select sysdate from dual;

4.不同数据类型间转换函数

将日期转成字符 tochar(date,'日期格式')

日期格式要用有效格式，格式大小写敏感 'yyyy mm dd hh24:mi:ss'(标准日期格式),'year'(年的全拼),'month'(月的全拼)，'day'(星期的全拼)，'ddspth' (日期的全拼)

例：select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;

select to_char(sysdate,'year month day ddspth')from dual;

将字符串转成日期 to_date('...','日期格式')

例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；

五.表连接（关联查询）

等值连接

select table1.column1，table2.column2

from table1 t1，table2 t2

where t1.column3=t2.column4;

表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用表名.字段名或表别名.字段名（列名）。当表的字段名是唯一时，可以不用加上表名或表的别名。

注意：当为表起了别名，就不能再使用表名.字段名了。

例如：select e.first_name ||’ ’|| e.last_name name,

d.name dept_name

from s_emp e, s_dept d

where e.dept_id=d.id;

非等值连接

select [表别名1.字段名1]，[表别名2.字段名2],...

from 表1 表别名1 ，表2 表别名2

where 表别名1.字段名3 ..... 表别名2.字段名4

....可以使比较运算符，也可以使其他的除了'='的运算符

例：select first_name, salary

from s_emp

where salary between 1000 and 2000;

自连接

把一个表的两个字段关系转换成两个表字段之间的关系.

select [表别名1.字段名1]，[表别名2.字段名2],...

from 表1 表别名1 ，表1 表别名2

where 表别名1.字段名3=表别名2.字段名4;

例：select a.first_name ename,b.first_name cname

from s_emp a,s_emp b

where a.manager_id=b.id;

外连接

使用一张表中的所有记录去和另一张表中的记录按条件匹配(空值也会匹配)这个表中的所有记录都会显示。

//想在哪边模拟记录就在哪边加上(+)

1. LEFT OUTER JOIN：左外连接

SELECT e.last_name, e.dept_id, d.name

FROM s_emp e

LEFT OUTER JOIN s_dept d

ON (e.dept_id = d.id);

等价于

SELECT e.last_name, e.dept_id, d.name

FROM s_emp e, s_dept d

WHERE e.dept_id=d.id(+);

结果为：所有员工及对应部门的记录，包括没有对应部门编号dept_id的员工记录。

2. RIGHT OUTER JOIN：右外连接

SELECT e.last_name, d.name

FROM s_emp e

RIGHT OUTER JOIN s_dept d

ON (e.dept_id = d.id);

等价于

SELECT e.last_name,d.name

FROM s_emp e, s_dept d

WHERE e.dept_id(+)=d.id;

结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。

3. FULL OUTER JOIN：全外关联

SELECT e.dept_id,d.id

FROM s_emp e

FULL OUTER JOIN s_dept d

ON (e.dept_id = d.id);

结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。

六.组函数

group by把 select 的结果集分成几个小组，这个group by 子句可以跟在 select 语句后或是 having前面。group by子句也会触发排序操作，会按分组字段排序。

select [组函数或分组的字段名]... from 表名 group by [字段名1],[字段名2],.....；

例：select avg(salary) from s_emp group by dept_id;

注意：组函数会忽略空值，但是count(*)除外，他会把空记录也记录在内。avg和sum这两个函数的参数只能是number型的。

以下所提到的函数可以使用任意类型做参数。

max(..),min(..)求最大值和最小值，

count(*)统计表中记录数。

例：select max(b.name),avg(a.salary), max(c.name)

from s_emp a,s_dept b,s_region c

where a.dept_id=b.id

and b.region_id=c.id

group by b.id;

注意：只要写了group by子句，select后就只能用group by之后的字段或者是组函数。having子句可以过滤组函数结果或是分组的信息，并且写在group by子句后。

七.子查询

可以嵌在sql语句中的select语句。

在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。

注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值）。配合使用子查询返回的结果必须符合运算符的用法。

例:

select first_name||' '||last_name name

from s_emp

where title in (select title from s_emp

where dept_id=42);

查询和42部门员工职位相同的所有员工的姓名

八.约束

针对表中的字段进行定义的。

primary key（主键约束 PK）保证实体的完整性，保证记录的唯一

主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键时，将两个字段放在一起唯一标识记录，叫做联合主键。

主键约束的定义：

第一种定义形式：

create table test(c number primary key ); 列级约束

第二种定义形式：

create table test(c number , primary key(c) ) ; 表级约束

create table test(c1 number constraints pk_c1 primary key ); 此约束有名字: pk_c1

create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键

foreign key（外键约束 FK）保证引用的完整性，外键约束，外键的取值是受另外一张表中的主键或唯一键的约束，不能够取其他值，只能够引用主键或唯一键的值，被引用的表，叫做 parent table（父表），引用方的表叫做child table（子表），要想创建子表，就要先创建父表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。(可以通过使用cascade constraints 选项来删除父表)

carete table parent(c1 number primary key );

create table child (c number primary key , c2 number references parent(c1));

或表级约束定义:

create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));

非空约束（not null）这是一个列级约束,在建表时,在数据类型的后面加上 not null ，也就是在插入时不允许插入空值。

例：create table student(id number primary key,name varchar2(32) not null,address varchar2(32));

unique 唯一约束

唯一约束，允许为空，要求插入的记录中的值是唯一的。

例：create table student(id number，name varchar2(32),address varchar2(32),primary key (id),unique (address));

check约束

检查约束，可以按照指定条件，检查记录的插入。check中不能使用伪列，不能使用函数，不能引用其他字段。

例：create table sal (a1 number , check(a1>1000));

九.数据字典

数据字典是由系统维护的，包含数据库的信息

数据字典示图

user_XXXXX 用户示图

all_XXXXX 所有示图

dba_XXXXX 数据库中所有示图

v$_XXXXX 动态性能示图

dict或 dictionary 表示数据字典的数据字典。

user_constraints 用户的表中约束的表

其中有constraints_name字段存放的是约束名，constraint_type字段存放的是约束的类型,r_constraints_name字段表示外键引用自何处.

user_cons_column表，是用户的列级约束表,column_name字段存放的是约束字段的名字,position字段存放的是约束在联合键中的位置.

十.事务transaction

原子操作，也就是不可分割的操作，必须一起成功一起失败。

事务的结束动作就是commit，DDL,DCL语句执行会自动提交commit。

sqlplus正常退出是会做提交动作的commit;，当系统异常推出是，会执行回滚操作rollback;。

一个没有结束的事务，叫做活动的事务 (active transaction),活动的事务中修改的数据，只有本会话才能看见。

十一.Oracle中的伪列

伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新或删除它们的值。常用的伪列：rowid和rownum。

rowid：数据库中的每一行都有一个行地址，rowid伪列返回该行地址。可以使用rowid值来定位表中的一行。通常情况下，rowid值可以唯一地标识数据库中的一行。

rowid伪列有以下重要用途：

1）能以最快的方式访问表中的一行；

2）能显示表的行是如何存储的。

3）可以作为表中行的唯一标识。

如：SQL> select rowid,ename from emp;

rownum：对于一个查询返回的每一行，rownum伪列返回一个数值代表的次序。

rownum伪列特点：

1）有个特点要么等于1 要么小于某个值，不能直接等于某个值, 不能大于某个值。

2）常用于分页显示。

返回的第一行的rownum值为1，第二行的rownum值为2，依此类推。通过使用rownum伪列，用户可以查询返回的行数。

如：SQL>select * from emp where rownum<11; 从emp表中提取10条记录。

十二.序列(sequence)

create sequence 序列名;

(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率)

sequence 的参数：

increment by n 起始值

start with n 递增量

maxvalue n 最大值

minvalue n 最小值

cycle|no cycle 循环

cache n 缓存(第一次取时会一次取多少个id存起来)

查看sequence 示图：

desc user_sequences ;

select sequence_name , cache_size , last_number from user_sequences where sequence_name like 's_';

select 序列名.currval from dual 查看当前的序列数

select 序列名.nextval from dual 查看下一个序列数，它会自动给当前的序列加１

删除序列sequence

drop sequence 序列名;

十三. 视图(View)

视图就相当于一条select 语句,定义了一个视图就是定义了一个sql语句, 视图不占空间,使用视图不会提高性能，但是能简化sql语句

创建视图：

creating views视图名;

如：

create or replace views test as select * from test1 where c1=1;

create or replace:如果view存在就覆盖，不存在才创建。

force|no force:基表存在时使用，不存在时则创建该表。

注意:向视图中插入数据时，会直接插进基表中，查看视图中的数据时，相当于就是执行创建时的select语句。

删除视图:

drop views视图名;

试图的约束:

with read only视图只读约束

with check option 不允许插入与where条件不符的记录，类似于check约束的功能.

create view test_cc

as select * from test

where c1>10

with check option;

十四.索引（index）

建立索引的目的就是为了加快查询速度,建立索引后会使DML操作效率慢，但是对用户查询会提高效率。删除一个表时，相对应的索引也会删除。另外,索引是会进行排序。

创建一个索引：

create index 索引名 on 表名 (字段名);

create index test_index on test(c1);

删除索引:

drop index test_index;

注意：创建索引就是为了减少物理读，索引会减少扫描的时间。在经常要用到where的子句的字段，应该使用索引，另外还要看所查询的数据与全部数据的百分比，表越大，查询的记录越少，索引的效率就越高.下载本文

显示全文

全部频道