Oracle初级——续续篇
时间：2021-07-01 10:21:17
帮助过：4人阅读
2 --约束
3
4 与表一起使用
5 6 约束不合理的数据，使其不能进入表中?
7
8 insert into stu
values (
‘13‘,
‘李小龙‘,
‘一班‘,
‘该学生成天练武，不爱学习‘,
‘不知道念什么中学‘);
9
10 约束的类型
11
12 约束的种类（重要）?
13 1、主键约束?
14 表中能够体现一行记录的唯一标识，这样的列就是主键?
15 特性：?
16 一张表中只能有一个主键约束?
17 主键约束可能作用在不止一个列上（联合主键）
18
19 主键约束列中的值不能重复?
20 主键约束列中的值不允许出现nul
21
22 如何创建主键?
23 关键字?
primary?
key?
24 创建表的时候直接创建主键?
25 直接在列上指定主键
26
27
28 create table haha(
29 hahaid
number primary key,
30 hahaname
varchar2(
16),
31 hahadate date
32 );
33
34 select * from haha ;
35 --–注意：不能指定联合主键
36 建表语句后在指定主键
37
38 drop table haha;
39 create table haha(
40 hahaid
number,
41 hahaname
varchar2(
16),
42 hahadate date,
43 constraint pk_haha_id
primary key(hahaid)
44 );
45 --做联合主键
46
47 create table haha(
48 hahaid
number,
49 hahaname
varchar2(
16),
50 hahadate date,
51 constraint pk_haha_id
primary key(hahaid,hahaname)
52 );
53
54 创建表之后追加主键
55
56 主键创建的规范：?
57 一般来说，做为主键的列，应该没有任何的实际意义。?
58 一个列做为主键后，不应该有任何其他的意义。
59
60
61
62
63 外键约束?
64 关键字：
foreign?
key?
65 一个表的某一个列关联另一个表的主键或者唯一键，?
66 这个列就是一个外键?emp中deptno?
67 创建一个外键
68
69 create table haha(
70 hid
number,
71 hname
varchar2(
32),
72 deptno
number,
73 constraint fk_haha_deptno
foreign key(deptno)
74 references dept(deptno)
75 );
76 --追加主键
77
78 create table haha(
79 hid
number,
80 hname
varchar2(
32),
81 deptno
number
82 );
83 alter table haha
84 add constraint fk_haha_deptno
foreign key(deptno)
85 references dept(deptno);
86
87 --3 唯一约束
88 表中的某一个类，其录入的值不能互相重复
89
90 --unique
91 alter table haha
92 add constraint un_haha_hname
unique(hname);
93
94
95 主键约束和唯一约束的区别（重要）?
96 1）一张表中只能有一个主键，可以有多个唯一约束
97
98
99 create table xixi(
100 xxid
number,
101 xname1
varchar2(
16),
102 xname2
varchar2(
16),
103 xname3
varchar2(
16),
104 constraint pk_xx_id
primary key(xxid),
105 constraint un_xx_x1
unique(xname1),
106 constraint un_xx_x2
unique(xname2)
107 );
108
109 2)主键的值必须是非空，唯一约束可以为空且可以不止一个为空?
110 3）主键创建的索引是聚集索引，?
111 唯一约束创建索引是非聚集索引（可以不提）
112
113 --4、检查性约束?
114 –关键字：
check
115
116 alter table xixi
117 add(salary
number);
118 alter table xixi
119 add constraint ck_xixi_sal
check(salary
> 0
120 and salary
< 2000);
121 create table xixi(
122 xxid
number,
123 xname1
varchar2(
16),
124 xname2
varchar2(
16),
125 xname3
varchar2(
16),
126 salary
number,
127 constraint pk_xx_id
primary key(xxid),
128 constraint un_xx_x1
unique(xname1),
129 constraint un_xx_x2
unique(xname2),
130 constraint ck_xixi_sal
check(salary
> 0
131 and salary
< 2000)
132 );
133
134 –
5、非空约束?
135 –只能在列上定义，不能利用constraint关键字声明?
136 –
not?
null
137
138 create table xixi(
139 xxid
number,
140 xname1
varchar2(
16),
141 xname2
varchar2(
16)
not null,
142 xname3
varchar2(
16)
not null,
143 salary
number,
144 constraint pk_xx_id
primary key(xxid),
145 constraint un_xx_x1
unique(xname1),
146 constraint un_xx_x2
unique(xname2),
147 constraint ck_xixi_sal
check(salary
> 0
148 and salary
< 2000)
149 );
150
151
152 一个列上可以作用多个约束
153 启用和禁用约束?
154 默认的情况是约束启用状态?
155 禁用约束
156
157 alter table xixi disable
constraint ck_xixi_sal;
158
159 alter table xixi enable
constraint ck_xixi_sal;
160
161 –启用约束时，表中存在违反约束条件的数据时，约束无法启用
162
163
164 --rownum
165 伪劣
166
167 select rownum , e.
* from emp e ;
168
169
170 查询工资前三的人
171
172 select rownum, e.
* from emp e
where rownum
<= 3 order by sal
desc;
173
174 错误?
175 查询排在第3到第5的人?根据月薪
176 1）rownum表示的是排序之前的序号?
177 2）rownum在where中可以进行筛选，但是只能用
<?
<=?，?
178 其他的得不到结果
179 排前三
180
181 select rownum,t1.
* from (
select * from emp
order by sal
desc)t1
where rownum
<=3;
182
183
184 --–查询排在第3到第5的人?根据月薪
185
186
187 select rownum,t1.
* from (
select * from emp
order by sal
desc)t1
where rownum
<=5 minus
select rownum,t1.
*from
188 (
select * from emp
order by sal
desc)t1
where rownum
<=2;
189
190 --分页显示
191 select t2.
* from (
select rownum
as haha, t1.
*
192 from (
select * from emp
order by sal
desc) t1
--排序
193 ) t2
--将rownum变成一个实际的列
194 where haha
between 3 and 5;
195
196 --rownum是oracle下的伪列?MySQL：limit ，SQL server top
197
198
199
200
201 子查询：
202
203 什么是子查询：
204
205 单行单列子查询
206 在一个查询中嵌套的查询被称为子查询
207
208 -- 问题一：
209
210 查询和scott同一个部门的其他员工
211
212 select *from emp
where deptno
= (
select deptno
from emp
where ename
= ‘SCOTT‘)
and ename
<> ‘SCOTT‘;
213
214 单行多列子查询（了解）
215 查询与scott同部门，同经理的其他员工
216
217 select * from emp e1
where (e1.deptno, e1.mgr)
= (
select e2.deptno, e2.mgr
from emp e2
where e2.ename
= ‘SCOTT‘)
218 and e1.ename
<> ‘SCOTT‘;
219
220
221 多行子查询（多行单列子查询）
222 多行多列基本看不到
223 当子查询结果返回的不止一条记录，外部查询不能使用
=，而是
224 使用in进行条件关联
225 练习：查询scott或ADAMS同一个部门的员工信息
226 注意：在多行子查询下
= 效率高还是in效率
227 当确定子查询结果只有一条时，外部使用
=，效率高
228 当无法确定子查询结果的条目数，外部推荐使用in，
229 为了保证正确
230 牺牲了效率保证了正确率
231 --查询与scott奖金数不同的人
232
233 select * from emp
where comm
not in (
select comm
from emp
where ename
= ‘SCOTT‘ or ename
= ‘ALLEN‘);
234
235
236 重点
237 not in
238 如果not in中的值出现null，则不会查询出任何结果
239 确保子查询结果中一定没有null
240
241
242 select * from emp
243 where comm
not in (
select nvl(comm,
0)
from emp
244 where ename
= ‘SCOTT‘
245 or ename
= ‘ALLEN‘);
246
247
248 –子查询可以使用再哪些语句中（嵌套子查询）
249 1、
where（重要）
250 查询比scott月薪高的人
251
252 select * from emp
253 where sal
> (
select sal
from emp
where ename
= ‘SCOTT‘);
254
255 --查询与scott不在同一个部门的人
256
257 select * from emp
258 where deptno
<> (
select deptno
from emp
where ename
= ‘SCOTT‘);
259
260 from 重要
261
262 select * from emp e,(
select deptno,dname
from dept) d
263 where e.deptno
= d.deptno;
264
265
266 多表连接
267
268 select * from emp, dept;
269
270 笛卡尔积
271 两个集合“相乘”（结合），等于两个集合中的每个元素互相组成
272 一个新的元素。
273
274 --1等值连接
275
276 select * from emp, dept
277 where emp.deptno
= dept.deptno;
278
279
280 --2、不等值连接
281 不使用等号进行表的连接
<= >= 也是不等值连接
282 查询员工的姓名，月薪和工资级别
283 姓名和月薪（EMP） 工资级别（salgrade）
284
285 select e.ename, e.sal, s.grade
286 from emp e, salgrade s
287 where e.sal
>= s.losal
and e.sal
<= s.hisal;
288
289 --3、外连接
290 查询员工姓名和其所在部门的名称，包括没部门的员工
291
292 select * from emp e, dept d
293 where e.deptno
= d.deptno
or e.deptno
is null;
294
295
296 此方法不行，因为所有的null都出来
297 外连接（左外连接
/右外连接）
298 简称左连接 右连接
299 查询员工姓名和其所在部门的名称，包括没部门的员工
300 左连接 oracle 下的连接
301
302
303 select * from emp e, dept d
304 where e.deptno
= d.deptno(
+);
305
306
307 右连接
308 查询没有员工的部门
309
310 select * from emp e, dept d
311 where e.deptno(
+)
= d.deptno
312
313
314 多表外连接
315 练习：查询员工的姓名(empolyees)，部门名称(departments)，
316 工作城市(locations)，没部门的员工也要显示出来
317 (
+)在多表连接中的传递
318
319 select * from employees e,departments d, locations l
320 where e.department_id
= d.department_id(
+)
321 and d.location_id
= l.location_id(
+);
322
323
324 --4、自连接(难点)
325 通常用于模拟层次关系的数据（行政体系，组织结构）
326 查询员工姓名e1.ename和其负责人的名字e2.ename
327
328
329 select e1.ename
as 员工姓名 , e2.ename
as 负责人姓名
330 from emp e1, emp e2
331 where e1.mgr
= e2.empno ;
332
333
334 --SQL99标准 （重点）
335 join…
on(…)
336 表A
join 表B
on(连接条件)
where 筛选条件
337
338
339 等值连接
340 查询员工姓名和其部门的名称
341
342 select e.ename, d.dname
343 from emp e
join dept d
on(e.deptno
= d.deptno);
344
345 --查询月薪高于1000的员工姓名和其部门的名称
346
347 select e.ename, d.dname
348 from emp e
join dept d
on(e.deptno
= d.deptno)
349 where e.sal
> 1000;
350
351
352 不等值连接
353 查询员工的姓名，月薪和工资级别
354 姓名和月薪（EMP） 工资级别（salgrade）
355
356
357 select e.ename, e.sal, s.grade
358 from emp e
join salgrade s
359 on(e.sal
between s.losal
and s.hisal);
360
361
362 自连接
363 查询员工姓名和其经理的姓名
364
365 select e1.ename, e2.ename
366 from emp e1
join emp e2
on(e1.mgr
= e2.empno );
367
368
369
370 外连接
371 左连接(常用)
372 hr环境下，查询员工的姓名，部门名称，包含没有部门的员工
373
374 select e.first_name, d.department_name
375 from employees e
left join departments d
376 on(e.department_id
= d.department_id);
377
378 右连接（意义不大，少用）
379
380 select e.first_name, d.department_name
381 from employees e
right join departments d
382 on(e.department_id
= d.department_id);
383
384 全连接
385
386 select *
387 from employees e, departments d
388 where e.department_id(
+)
= d.department_id(
+);
389
390 --错误的语句，oracle做不了全连接
391 SQL99标准写法
392
393 select *
394 from employees e
full join departments d
395 on(e.department_id
= d.department_id);
396
397
398
399 --集合
400
401 union与union all关键字
402 union 去掉重复的记录
403 union 默认按照第一列进行排序
404
405 select ename, sal
from emp
where sal
> 1000
406 union select ename, sal
from emp
where sal
< 1500;
407
408 union all
409 不排序任何列，包含重复的记录
410
411 select ename, sal
from emp
where sal
> 1000
412 union all select ename, sal
from emp
where sal
< 1500;
413
414 交集
intersect 取交集
415
416 select ename, sal
from emp
where sal
> 1000 intersect select ename, sal
from emp
where sal
< 1500;
417
418 相减
419
420 select ename, sal
from emp
where sal
> 1000
421 minus
select ename, sal
from emp
where sal
> 1500;
422
423
424 交并集的操作重要条件：（重要）
425 组成集合的多个SQL语句，其查询结果必须类型一致，
426 而且应该意义相同，这些SQL语句应该有相同的结构
427 类型是一致，但是没有意义
428
429
430 select ename, sal
from emp
where deptno
= 10
431 union select ename, comm
from emp
432 where deptno
= 10;
433
434 类型不一致的，语法错误
435
436 select ename, sal
from emp
where deptno
= 10
437 union select ename, hiredate
from emp
where deptno
= 10;
438
439 --不相同的结构，语法错误
440 select ename, sal
441 from emp
where deptno
= 10
442 union select ename, sal, comm
from emp
where deptno
= 10;
后续，会有视图和索引，以及存储过程的文章，客官们不要着急，耐心等待.........
Oracle初级——续续篇
标签：推荐 学习 prim 排序 分页 中学 dep ima create
