尚学堂oracle笔记
来源：互联网 发布：网络信息系统管理员 编辑：程序博客网 时间：2024/06/10 15:06
文章分类:数据库
1 . 第一课: 客户端
2 . 1. Sql Plus(客户端), 命令行直接输入: sqlplus ,然后按提示输入用户名,密码。
3 . 2. 从开始程序运行:sqlplus,是图形版的sqlplus.
4 . 3. http://localhost:5560/isqlplus
5 .
6 . Toad : 管理, PlSql Developer:
7 .
8 . 第二课: 更改用户
9 . 1. sqlplus sys/bjsxt as sysdba
10. 2. alter user scott account unlock;( 解锁)
11. --创建用户
12. create user username identified by password;
13. grant dba to username;
14. drop user username cascade ;
15. 第三课: table structure
16. 1. 描述某一张表: desc 表名
17. 2. select * from 表名
18. 第四课: select 语句:
19. 1.计算数据可以用空表: 比如: . select 2*3 from dual
20. 2.select ename,sal*12 annual_sal from emp; 与select ename,sal*12 "annual sal" from emp; 区
别,加双引号保持原大小写。不加全变大写。
21.
22. 3. select ename || "abcd" 如果连接字符串中含有单引号,用两个单引号代替一个单引号。
23. 第五课: distinct
24. select deptno from emp;
25. select distinct deptno from emp;
26. select distinct deptno from emp;
27. select distinct deptno ,job from emp
28. 去掉deptno,job两者组合的重复。更多的项,就是这么多项的组合的不重复组合。
29. 第六课: Where
30. select * from emp where deptno =10;
31. select * from emp where deptno <>10;不等于10
32. select * from emp where ename ='bike';
33. select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)
34. 空值处理:
35. select ename,sal,comm from emp where comm is (not ) null;
36. select ename,sal,comm from emp where ename ( not ) in ('smith' , 'king', 'abc' );
37. select ename from emp where ename like '_A%' ;_代表一个字母,%代表0 个或多个字母. 如果查询%
38. 可用转义字符.\%. 还可以用escape '$' 比如: select ename from emp where ename like '%$a%' esc
ape '$' ;
39. 第七课: orderby
40.
41. select * from dept;
42. select * from dept order by dept desc;(默认: asc )
43. select ename,sal,deptno from emp order by deptno asc ,ename desc;
44. 第八课: sql function1:
45. select ename,sal*12 annual_sal from emp
46. where ename not like '_A%' and sal>800
47. order by sal desc;
48. select lower (ename) from emp;
49. select ename from emp
50. where lower (ename) like '_a%' ; 等同于
51. select ename from emp where ename like '_a%' or ename like '_A%' ;
52. select substr(ename,2,3) from emp; 从第二字符截, 一共截三个字符.
53. select chr(65) from dual 结果为:A
54. select ascii( 'a' ) from dual 结果为:65
技术网站
3445[2010/7/19 14:37:42]
55. select round(23.652,1) from dual; 结果为: 23.7
56. select round(23.652,-1) from dual; 20
57.
58. select to_char(sal,'$99_999_999') from emp;
59. select to_char(sal,'L99_999_999') from emp; 人民币符号,L: 代表本地符号
60. 这个需要掌握牢:
61. select birthdate from emp;
62. 显示为:
63. BIRTHDATE
64. ----------------
65. 17-12月-80
66. ----------------
67. 改为:
68. select to_char(birthdate,'YYYY-MM-DD HH:MI:SS' ) from emp;
69.
70. 显示:
71.
72. BIRTHDATE
73. -------------------
74. 1980-12-17 12:00:00
75. -------------------
76.
77. select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS' ) from dual; //也可以改为:HH12
78. TO_CHAR(SYSDATE,'YY
79. -------------------
80. 2007-02-25 14:46:14
81.
82. to_date函数:
83. select ename,birthdate from emp where birthdate > to_date( '1981-2-20 12:34:56', 'YYYY-MM
-DD HH24:MI:SS');
84. 如果直接写 birthdate>'1981-2-20 12:34:56'会出现格式不匹配, 因为表中的格式为: DD-MM月-YY.
85.
86.
87. select sal from emp where sal>888.88 无错. 但
88. select sal from emp where sal>$1,250,00;
89. 会出现无效字符错误.
90. 改为:
91. select sal from emp where sal>to_number('$1.250.00', '$9,999,99');
92.
93. 把空值改为0
94. select ename,sal*12+nvl(comm,0) from emp;
95. 这样可以防止comm为空时,sal*12 相加也为空的情况.
96.
97. 第九课: Group function 组函数
98. max , min , avg ,count , sum 函数
99.
100 . select to_char( avg (sal),'99999999,99') from emp;
101 .
102 .
103 . select round( avg (sal),2) from emp;
104 . 结果:2073.21
105 .
106 . select count (*) from emp where deptno=10;
107 . select count (ename) from emp where deptno=10; count 某个字段, 如果这个字段不为空就算一个.
108 . select count ( distinct deptno) from emp;
109 . select sum (sal) from emp;
110 . 第十课: Group by语句
111 .
112 . 需求: 现在想求, 求每个部门的平均薪水.
113 . select avg (sal) from emp group by deptno;
114 . select deptno avg (sal) from emp group by deptno;
115 .
116 . select deptno,job, max (sal) from emp group by deptno,job;
117 .
技术网站
3445[2010/7/19 14:37:42]
118 . 求薪水值最高的人的名字.
119 . select ename, max (sal) from emp; 出错, 因为max 只有一个值, 但等于max 值的人可能好几个, 不能匹配.
120 . 应如下求:
121 . select ename from emp where sal=(select max (sal) from emp);
122 . Group by语句应注意,
123 . 出现在select中的字段, 如果没出现在组函数中, 必须出现在Group by语句中.
124 .
125 .
126 . 第十一课: Having 对分组结果筛选
127 .
128 . Where 是对单条纪录进行筛选, Having是对分组结果进行筛选.
129 .
130 . select avg (sal),deptno from emp
131 . group by deptno
132 . having avg (sal)>2000;
133 .
134 . 查询工资大于1200雇员, 按部门编号进行分组, 分组后平均薪水大于1500, 按工薪倒充排列.
135 . select * from emp
136 . where sal>1200
137 . group by deptno
138 . having avg (sal)>1500
139 . order by avg (sal) desc;
140 .
141 . 第十二课: 字查询
142 .
143 . 谁挣的钱最多( 谁: 这个人的名字, 钱最多)
144 .
145 . select 语句中嵌套select 语句, 可以在where , from后.
146 .
147 .
148 . 问那些人工资, 在平均工资之上.
149 .
150 . select ename,sal from emp where sal>(select avg (sal) from emp);
151 .
152 . 查找每个部门挣钱最多的那个人的名字.
153 . select ename ,deptno from emp where sal in( select max (sal) from ename group by deptno)
查询会多值.
154 . 应该如下:
155 .
156 . select max (sal),deptno from emp group by deptno;当成一个表. 语句如下:
157 . select ename, sal from emp join( select max (sal) max_sal,deptno from emp group
158 . by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno);
159 .
160 . 每个部门的平均薪水的等级.
161 . 分析: 首先求平均薪水( 当成表),把平均薪水和另外一张表连接.
162 .
163 .
164 . 第十四课:self_table_connection
165 .
166 . 把某个人的名字以及他的经理人的名字求出来( 经理人及这个人在表中同处一行)
167 .
168 . 分析: 首先求出这个人的名字, 取他的编号, 然后从另一张表与其相对应编号, 然后找到经理的名字.
169 .
170 . select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.
171 .
172 . empno 编号和MGR 都是编号.
173 .
174 . 第十15课: SQL1999_table_connections
175 .
176 . select ename,dname,grade from emp e,dept d, sqlgrade s
177 . where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
178 . job<>'CLERK' ;
179 .
技术网站
3445[2010/7/19 14:37:42]
180 . 有没有办法把过滤条件和连接条件分开来? 出于这样考虑,Sql1999标准推出来了. 有许多人用的还是
181 . 旧的语法, 所以得看懂这种语句.
182 .
183 .
184 .
185 . select ename,dname from emp,dept;(旧标准).
186 . select ename,dname from emp cross join dept;(1999标准)
187 .
188 . select ename,dname from emp,dept where emp.deptno=dept.deptno ( 旧)
189 . select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999 标准. 没有Where 语句
.
190 . select ename,dname from emp join dept using(deptno);等同上句, 但不推荐使用.
191 .
192 . select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
193 . join 连接语句, on过滤条件。连接,条件一眼分开。如果用Where 语句较长时,连接语句和过滤语句混在一起。
194 .
195 . 三张表连接:
196 . slect ename,dname, grade from
197 . emp e join dept d on(e.deptno=d.deptno)
198 . join salgrade s on(e.sal between s.losal and s.hisal)
199 . where ename not like '_A%' ;
200 . 把每张表连接 条件不混在一起,然后数据过滤条件全部区分开来。读起来更清晰,更容易懂一点。
201 .
202 . select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);
203 . 左外连接: 会把左边这张表多余数据显示出来。
204 . select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno); left 后可加out
er
205 . 右外连接:
206 . select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer 可以取掉
。
207 .
208 . 即把左边多余数据,也把右边多余数据拿出来,全外连接。
209 . select ename,dname from emp e full join dept d on(e.deptno =d.deptno);
210 .
211 . 16-23 课: 求部门平均薪水的等级
212 . A.求部门平均薪水的等级。
213 . select deptno,avg_sal,grade from
214 . ( select deptno,avg (sal) avg_sal from emp group by deptno)t
215 . join salgrade s on(t.avg_sal between s.losal and s.hisal)
216 .
217 . B.求部门平均的薪水等级
218 . select deptno,avg (grade) from
219 . ( select deptno,ename, grade from emp join salgrade s on(emp.sal between s.losal and
220 . s.hisal)) t
221 . group by deptno
222 . C.那些人是经理
223 . select ename from emp where empno in( select mgr from emp);
224 . select ename from emp where empno in( select distinct mgr from emp);
225 .
226 . D.不准用组函数,求薪水的最高值(面试题)
227 .
228 . select distinct sal from emp where sal not in(
229 . select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));
230 .
231 . E.平均薪水最高的部门编号
232 .
233 . select deptno,avg_sal from
234 . ( select avg (sal)avg_sal,deptno from emp group by deptno)
235 . where avg_sal=
236 . ( select max (avg_sal) from
237 . ( select avg (sal) avg_sal,deptno from emp group by deptno)
238 . )
239 .
技术网站
3445[2010/7/19 14:37:42]
240 . F.平均薪水最高的部门名称
241 . select dname from dept where deptno=
242 . (
243 . select deptno from
244 . (select avg (sal)avg_sal,deptno from emp group by deptno)
245 . where avg_sal=
246 . (select max (avg_sal) from
247 . (select avg (sal) avg_sal,deptno from emp group by deptno)
248 . )
249 . )
250 .
251 . G. 求平均薪水的等级最低的部门的部门名称
252 .
253 . 组函数嵌套
254 . 如: 平均薪水最高的部门编号,可以E.更简单的方法如下:
255 . select deptno,avg_sal from
256 . (select avg (sal) avg_sal,deptno from emp group by deptno)
257 . where avg_sal =
258 . (select max ( avg (sal)) from emp group by deptno)
259 .
260 . 组函数最多嵌套两层
261 .
262 . 分析:
263 . 首先求
264 . 1. 平均薪水: select avg (sal) from group by deptno;
265 . 2. 平均薪水等级: 把平均薪水当做一张表,需要和另外一张表连接salgrade
266 . select deptno,grade avg_sal from
267 . ( select deptno,avg (sal) avg_sal from emp group by deptno) t
268 . join salgrade s on(t.avg_sal between s.losal and s.hisal)
269 .
270 . 上面结果又可当成一张表。
271 .
272 . DEPTNO GRADE AVG_SAL
273 . -------- ------- ----------
274 . 30 3 1566.66667
275 . 20 4 2175
276 . 10 4 2916.66667
277 . 3. 求上表平均等级最低值
278 .
279 . select min (grade) from
280 . (
281 . select deptno,grade,avg_sal from
282 . ( select deptno,avg (sal) avg_sal from emp group by deptno)t
283 . join salgrade s on(t.avg_sal between s.losal and s.hisa)
284 . )
285 . 4. 把最低值对应的2 结果的那张表的对应那张表的deptno, 然后把2 对应的表和另外一张表做连接。
286 .
287 . select dname ,deptno,grade,avg_sal from
288 . (
289 . select deptno,grade,avg_sal from
290 . ( select deptno,avg (sal) avg_sal from emp group by deptno)t
291 . join salgrade s on(t.avg_sal between s.losal and s.hisal)
292 . ) t1
293 . join dept on (t1.deptno = dept.deptno)
294 . where t1.grade =
295 . (
296 . select deptno,grade,avg_sal from
297 . (select deptno,avg (sal) avg_sal from emp group by deptno) t
298 . join salgrade s on(t.avg_sal between s.losal and s.hisal)
299 . )
300 . )
301 . 结果如下:
302 .
303 . DNAME DEPTNO GRADE AVG_SAL
技术网站
3445[2010/7/19 14:37:42]
304 . -------- ------- -------- --------
305 . SALES 30 3 1566.6667
306 .
307 .
308 . H: 视图(视图就是一张表,一个字查询)
309 .
310 . G 中语句有重复,可以用视图来简化。
311 . conn sys/bjsxt as sysdba;
312 . grant create table , create view to scott;
313 . conn scott/tiger
314 . 创建视图:
315 . create view v$_dept_avg-sal_info as
316 . select deptno,grade,avg_sal from
317 . ( select deptno,avg (sal) avg_sal from emp group by deptno)t
318 . join salgrade s on 9t.avg_sal between s.losal and s.hisal)
319 .
320 . 然后
321 . select * from v$_dept_avg-sal_info
322 .
323 . 结果如下:
324 . DEPTNO GRADE AVG_SAL
325 . -------- ------- ----------
326 . 30 3 1566.66667
327 . 20 4 2175
328 . 10 4 2916.66667
329 . 然后G 中查询可以简化成:
330 . select dname,t1.deptno,grade,avg_sal from
331 . v$_dept_avg-sal_info t1
332 . join dept on9t1.deptno =dept.deptno)
333 . where t1.grade=
334 . (
335 . select min (grade) from v$_dept_avg-sal_info t1
336 . )
337 .
338 . 求比普通员工最高薪水还要高的经理人的名称
339 . 先求普通员工的最高薪水
340 . select max (sal) from emp where empno not in
341 . (select distinct mgr from emp where mgr is not null);
342 .
343 . select ename from emp where empno in
344 . (select distinct mgr from emp where mgr is not null)
345 . and sal >
346 . (
347 . select max (sal) from emp where empno not in
348 . ( select distinct mgr from emp where mgr is not null);
349 . )
350 .
351 . 第二十四课
352 . 备份与恢复
353 . DOS 环境下:exp导出,imp导入
354 . --drop user yugang cascade;
355 . --cd \
356 . --cd temp
357 . =-delete *.*
358 . --exp
359 . --create user yugang indentified by yugang default tablespace users quota 10M on us
ers
360 . --grant create session,create table,create view to yugang
361 . --imp the data
362 . 第一次输入的用户名密码为:yugang/yugang
363 . 第二次输入的用户名密码为: 导出数据的用户的用户名和密码
364 . --rollback
365 . create table emp2 as select * from emp;
366 .
技术网站
3445[2010/7/19 14:37:42]
367 . 第二十五课:rownum
368 . rownum 是在Oracle中在表的后面加的一个尾字段, 并且只能使用诸如rownum<n或rownum<=n 这样的 形式,
不能与> 或>=相连接
369 .
370 . 1.求薪水值最高的前5 条记录.
371 . select ename,sal from emp order by sal desc where sal <= 5;
372 . 2.求薪水值最高的第6~10条记录.
373 . select ename,sal from
374 . (
375 . select ename,sal,rownum r from
376 . ( select ename,sal from emp order by sal desc)
377 . )
378 . where r >=6 and r <= 10;
379 .
380 .
381 . 第二十六课:homework_dml_transaction
382 . SQL 面试题
383 . 有三张表S 、C 、SC
384 . S(SNO、SNAME)代表(学号、姓名)
385 . C(CNO、CNAME 、CTEACHER) 代表(课号、课名、老师)
386 . SC(SNO 、CNO 、SCGRADE)代表(学号、课号、成绩)
387 . 1.求出没选过郭富城老师的所有学生姓名
388 . 2.列出2 门以上(含2 门)不及格的学生姓名及平均成绩
389 . 3.既学过1 号课程又学过2 号课程的所有学生姓名
390 . 1.select sname from s join sc on (s.sno = sc.sno) join c on (c.cno = sc.cno)
391 . where c.cteacher <> ' 郭富城' ;
392 . 2.select sname from s where sno in
393 . (
394 . select sno from sc where scgrade < 60 group by sno having count (*) >= 2
395 . )
396 . 3.select sname from s where sno in
397 . (
398 . select sno from sc where cno=1 and sno in
399 . ( select distinct sno from sc where cno=2)
400 . )
401 .
402 . 事务transaction代表一组不可分割的操作,要么全部执行,要么全部不执行,
403 . transaction起始于一条DML 语句,结束于commit; 语句,或者是DCL 、DDL 语句,在事务未提交前
404 . 可以通过rollback回滚事务,正常退出事务会自动提交,非正常退出事务会自动回滚。
405 .
406 . 第二十七课: create table
407 . create table student
408 . (
409 . id number(6),
410 . name varchar2(20),
411 . sex number(1),
412 . age number(3),
413 . sdate date,
414 . grade number(2) default 1,
415 . class number(4),
416 . email varchar2(50)
417 . );
418 .
419 . 第二十八~ 三十课 constraint
420 . not null, unique( 当某字段有unique约束时,可以插入空值,空值之间不重复) 、主键、外键、 check 约束
421 . create table student
422 . (
423 . id number(6),
424 . name varchar2(20) constraint stu_name_nn not null,
425 . sex number(1),
426 . age number(3),
427 . sdate date,
428 . grade number(2) default 1,
技术网站
3445[2010/7/19 14:37:42]
429 . class number(4),
430 . email varchar2(50) unique
431 . )
432 . /
433 . 行级约束(放在字段后面)与表级约束(加在表后面):
434 . create table student
435 . (
436 . id number(6),
437 . name varchar2(20) constraint stu_name_nn not null,
438 . sex number(1),
439 . age number(3),
440 . sdate date,
441 . grade number(2) default 1,
442 . class number(4),
443 . email varchar2(50),
444 . constraint stu_email_name_uni unique( name,email)
445 . )
446 . /
447 . 主键约束
448 . create table student
449 . (
450 . id number(6) primary key ,
451 . name varchar2(20) constraint stu_name_nn not null,
452 . sex number(1),
453 . age number(3),
454 . sdate date,
455 . grade number(2) default 1,
456 . class number(4),
457 . email varchar2(50),
458 . constraint stu_email_name_uni unique( name,email)
459 . )
460 . /
461 . create table student
462 . (
463 . id number(6),
464 . name varchar2(20) constraint stu_name_nn not null,
465 . sex number(1),
466 . age number(3),
467 . sdate date,
468 . grade number(2) default 1,
469 . class number(4),
470 . email varchar2(50),
471 . constraint stu_id_pk primary key (id),
472 . constraint stu_email_name_uni unique( name,email)
473 . )
474 . /
475 . 外键约束
476 . 外键约束被参考的字段必须是主键。
477 . create table class
478 . (
479 . id number(4) primary key ,
480 . name varchar2(20) not null
481 . )
482 . /
483 . create table student
484 . (
485 . id number(6),
486 . name varchar2(20) constraint stu_name_nn not null,
487 . sex number(1),
488 . age number(3),
489 . sdate date,
490 . grade number(2) default 1,
491 . class number(4) references class(id),
492 . email varchar2(50),
技术网站
3445[2010/7/19 14:37:42]
493 . constraint stu_id_pk primary key (id),
494 . constraint stu_email_name_uni unique( name,email)
495 . )
496 . /
497 .
498 . create table student
499 . (
500 . id number(6),
501 . name varchar2(20) constraint stu_name_nn not null,
502 . sex number(1),
503 . age number(3),
504 . sdate date,
505 . grade number(2) default 1,
506 . class number(4),
507 . email varchar2(50),
508 . constraint stu_id_pk primary key (id),
509 . constraint stu_class_fk foreign key (class) references class(id),
510 . constraint stu_email_name_uni unique( name,email)
511 . )
512 . /
513 .
514 . 第三十一课: alter table drop table
515 . alter table student add (addr varchar2(100));
516 . alter table student drop(addr);
517 . alter table student modify(addr varchar2(50));
518 . 若addr字段中有的记录长度大于50,则修改不成功。
519 . alter table student drop constraint stu_class_fk;
520 . alter table student add constraint stu_class_fk foreign key (class)
521 . references class (id);
522 . drop table class;
523 . 第32课: Oracle dictionaries
524 . desc user_tables;
525 . select table_name from user_tables;
526 . select view_name from user_views;
527 . select constraint_name from user_constraints;
528 . select constraint_name,table_name from user_constraints;
529 . desc dictionary;
530 .
531 . 第33课: indexes_and_views
532 . 索引可以提高读数据的效率,但会降低修改、写数据的效率, 索引还会占用一定的存储空间
533 . create index idx_stu_email on student(email);
534 . drop index idx_stu_email;
535 . 视图会带来一些维护的代价,比如表结构改了,那么视图也得跟着改变。视图还可以用来保
536 . 护私有数据。
537 . select index_name from user_indexes;
538 . select view_name from user_views;
539 .
540 . 第34课: sequences and review
541 . 序列可以自动增长,在sql server 中有identity,mysql 中有auto_increment
542 . create table article
543 . (
544 . id number,
545 . title varchar2(1024),
546 . cont long
547 . )
548 . /
549 . 如何保证插入数据时id不重复？
550 . select max (id) from article; 这样做也不行,如果有多个线程同时访问数据,则可能会出现
551 . 数据不一致的现象,比方说第一个用户查询出最大id值是100 ,第二个用户也查询出最大id是
552 . 100 ,然后第二个用户新插入一条记录: id是101 ,然后第一个用户也插入了一条记录: id也是
553 . 101
554 . 可以用Oracle的sequence
555 . create sequence seq_article_id;
556 . 多个表之问可以共用一个序列,但是一般情况下为每个字段分别建立一个序列
技术网站
3445[2010/7/19 14:37:42]
557 . sequence有两个属性CurrVal 、NextVal
558 . select seq.nextvalue from dual;
559 . insert into article values(seq.nextval,'y' , 'yy');
560 . insert into article values(seq.nextval,'x' , 'xx');
561 . insert into article values(seq.nextval,'z' , 'zz');
562 . insert into article values(seq.nextval,'q' , 'qq');
563 .
564 . 第35课: 三范式
565 . 第一范式:
566 . 1.每个表都要有主键
567 . 2.列不可分,比如:
568 . create table stu
569 . (
570 . id number,
571 . name varchar2(20),
572 . age number
573 . )
574 . /
575 . insert into stu values(1, 'yu',21);
576 . create table stu2
577 . (
578 . inf long
579 . )
580 . /
581 . insert into stu2 values( '1_yu_21' );
582 . 虽然表stu2可以字符串解析后可以达到和表stu 一样的效果,但是显然第一种方法更好,查询数
583 . 据更加方便,而表stu2违反了第一范式的列不可分原则。
584 . 第二范式:
585 . 当有两个以上字段作主键时,非主键字段不能部分依赖于主键字段,如有一个需求,一门老师教 多名学生,一名
学生可以选多个老师的课。然后设计了一张表,有以下字段(老师编号、学生编 号、老师姓名、学生姓名等), 其中
以老师编号和学生编号作联合主键,则些表就存在部分依赖 ,老师姓名部分依赖于老师编号,不满足第二范式,有数
据冗余。要解决这个问题可以设计三张 表。
586 . 第三范式:
587 . 不能存在传递依赖,如有一张表有以下字段:( 学号、姓名、班级号、班级名称、班级位置), 其中学号为主键,
则班级号依赖于学号,每个学生都有相应的班级号,但是班级名称、班级位置 是依赖于班级号,即它们通过班级号传
递依赖于学号,不满足第三范式。
588 .
589 . 第38课: PL_SQL
590 . set serveroutput on;
591 . begin
592 . dbms_output.put_line('Hello World!!');
593 . end ;
594 .
595 . declare
596 . v_name varchar2(20);
597 . begin
598 . v_name:='xiaoyu';
599 . dbms_output.put_line(v_name);
600 . end ;
601 .
602 . declare
603 . v_num number:=0;
604 . begin
605 . v_num:=2/v_num;
606 . dbms_output.put_line(v_num);
607 . exception
608 . when others then
609 . dbms_output.put_line( 'error' );
610 . end ;
611 . --变量声明的规则
612 . 1.变量名不能使用保留字
613 . 2.第一个字符必须是字母
614 . 3.变量名最多包含30个字母
615 . 4.不要与数据库或表名同名
技术网站
3445[2010/7/19 14:37:42]
616 . 5.每一行只能声明一个变量
617 . /*
618 . 这是多行注释
619 . */
620 . --常用变量类型
621 . 1.binary_integer: 整数,主要用来计数而不是用来表示字段类型
622 . 2.number 数字类型
623 . 3.char定长字符串
624 . 4.date日期
625 . 5.long 长字符串,最长2GG
626 . 6.boolean类型: 可以取值true、false 、null,默认为null,另外boolean 类型值不可直接打印
627 . --变量声明
628 . declare
629 . v_temp number(1);
630 . v_count binary_integer:=0;
631 . v_sal number(7,2):=8888.88;
632 . v_date date:=sysdate;
633 . v_pi constant number(3,2):=3.14;
634 . v_valid boolean:=false ;
635 . v_name varchar2(20) not null:='geniusxiaoyu' ;
636 . begin
637 . dbms_output.put_line('v_temp value:' || v_temp);
638 . end ;
639 . --变量声明: 使用%type 属性
640 . declare
641 . v_empno number(4);
642 . v_empno2 emp.empno%type;
643 . v_empno3 v_empno2%type;
644 . begin
645 . dbms_output.put_line('test for type');
646 . end ;
647 . --Table 变量类型, 相当于数组类型
648 . declare
649 . type type_table_emp_empno is table of emp.empno%type index by bina
ry_integer;
650 . v_empnos typa_table_emp_empno;
651 . begin
652 . v_empnos(0):=7777;
653 . v_empnos(1):=7778;
654 . v_empnos(-1):=6666;
655 . dbms_output.put_line(v_empnos(-1));
656 . end ;
657 . --Record变量类型,相当于类
658 . declare
659 . type type_record_dept is record
660 . (
661 . deptno dept.deptno%type,
662 . dname dept.dname%type,
663 . loc dept.loc%type
664 . );
665 . v_temp type_record_dept;
666 . begin
667 . v_temp.deptno:=50;
668 . v_temp.dname:='yugang';
669 . v_temp.loc:='beijing' ;
670 . dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname || ' ' || v_
temp.loc);
671 . end ;
672 . --使用%rowtype声明record变量
673 . declare
674 . v_temp dept%rowtype;
675 . begin
676 . v_temp.deptno:=50;
677 . v_temp.dname:='yugang';
技术网站
3445[2010/7/19 14:37:42]
678 . v_temp.loc:='beijing' ;
679 . dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname || ' ' || v_
temp.loc);
680 . end ;
681 .
682 . --SQL 语句的运用
683 . --select语句必须和into语句一块使用并且只能返回一条记录
684 . --sql%rowcount
685 . declare
686 . v_name emp.ename%type;
687 . v_sal emp.sal%type;
688 . begin
689 . select ename,sal into v_name,v_sal from emp where empno=7369;
690 . dbms_output.put_line(v_name || ' ' || v_sal);
691 . end ;
692 .
693 . declare
694 . v_temp emp%rowtype;
695 . begin
696 . select * into v_temp from emp where empno=7369;
697 . dbms_output.put_line(v_temp.ename || ' ' || v_temp.eno);
698 . end ;
699 .
700 . declare
701 . v_deptno dept.deptno%type:=50;
702 . v_dname dept.dname%type:='mm';
703 . v_loc dept.loc%type:='bj';
704 . begin
705 . insert into dept values(v_deptno,v_dname,v_loc);
706 . commit;
707 . end ;
708 .
709 . declare
710 . v_deptno dept.deptno%type:=50;
711 . v_count number;
712 . begin
713 . --update emp set sal:=sal/2 where deptno=v_deptno;
714 . --select deptno into v_deptno from emp where deptno=7369;
715 . select count (*) into v_count from emp;
716 . dbms_output.put_line(sql%rowcount || ' 条记录被影响!' );
717 . commit;
718 . end ;
719 . --DDL 语句,在PLSQL 中使用DDL 语句要加上execute immediate,两个单引号代表一个单引号
720 . begin
721 . execute immediate 'create table tt(name varchar2(20) default ' 'Army'')' ;
722 . end ;
723 .
724 . declare
725 . v_sal emp.sal%type;
726 . begin
727 . select sal into v_sal from emp where empno=7369;
728 . if(v_sal<1200) then
729 . dbms_output.put_line('low' );
730 . elsif(v_sal<2000) then
731 . dbms_output.put_line('middle');
732 . else
733 . dbms_output.put_line('high');
734 . end if;
735 . end ;
736 .
737 . --循环
738 . declare
739 . i binary_integer:=1;
740 . begin
技术网站
3445[2010/7/19 14:37:42]
741 . loop
742 . dbms_output.put_line(i);
743 . i:=i+1;
744 . exit when (i >= 11);
745 . end loop;
746 . end ;
747 .
748 . declare
749 . j binary_integer:=1;
750 . begin
751 . while j<11 loop
752 . dbms_output.put_line(j);
753 . j:=j+1;
754 . end loop;
755 . end ;
756 .
757 . begin
758 . for k in 1..10 loop
759 . dbms_output.put_line(k);
760 . end loop;
761 . for k in reverce 1..10 loop
762 . dbms_output.put_line(k);
763 . end loop;
764 . end ;
765 .
766 . --错误处理
767 . declare
768 . v_temp number;
769 . begin
770 . select empno into v_temp where deptno=10;
771 . exception
772 . when too_many_rows then
773 . dbms_output.put_line( ' 太多记录了' );
774 . when others then
775 . dbms_output.put_line( 'error' );
776 . end ;
777 .
778 . declare
779 . v_temp number;
780 . begin
781 . select deptno into v_temp from emp where empno=2222;
782 . exception
783 . when no_data_found then
784 . dbms_output.put_line( ' 没数据' );
785 . end ;
786 .
787 . create table errorlog
788 . (
789 . id number primary key ,
790 . errcode number,
791 . errmsg varchar2(1024),
792 . errdate date
793 . );
794 .
795 . create sequence seq_errorlog_id start with 1 increment by 1;
796 .
797 . declare
798 . v_deptno dept.deptno%type:=10;
799 . v_errcode number;
800 . v_errmsg varchar2(1024);
801 . begin
802 . delete from dept where deptno=v_deptno;
803 . commit;
804 . exception
805 . when others then
技术网站
3445[2010/7/19 14:37:42]
806 . rollback;
807 . v_errcode:=SQLCODE;
808 . v_errmsg:=SQLERRM;
809 . insert into errlog values(seq_errorlog_id.nextVal,v_errcode,v_errmsg,sysdate);
810 . commit;
811 . end ;
812 .
813 . 第47~48 课: cursor(重点)
814 . declare
815 . cursor c is
816 . select * from emp;
817 . v_emp c%rowtype;
818 . begin
819 . open c;
820 . fetch c into v_emp;
821 . dbms_output.put_line(v_emp.ename);
822 . close c;
823 . end ;
824 . --简单循环
825 . declare
826 . cursor c is
827 . select * from emp;
828 . v_emp c%rowtype;
829 . begin
830 . open c;
831 . loop
832 . fetch c into v_emp;
833 . exit when(c%notfound);
834 . dbms_output.put_line(v_emp.ename);
835 . end loop;
836 . end ;
837 .
838 . declare
839 . cursor c is
840 . select * from emp;
841 . v_emp c%rowtype;
842 . begin
843 . open c;
844 . loop
845 . fetch c into v_emp;
846 . --下面两行顺序改变后,将会把最后一条记录打印两遍
847 . dbms_output.put_line(v_emp.ename);
848 . exit when(c%notfound);
849 .
850 . end loop;
851 . end ;
852 . --while 循环
853 . declare
854 . cursor c is
855 . select * from emp;
856 . v_emp emp%rowtype;
857 . begin
858 . open c;
859 . fetch c into v_emp;
860 . while(c%found) loop
861 . dbms_output.put_line(v_emp.ename);
862 . fetch c into v_emp;
863 . end loop;
864 . close c;
865 . end ;
866 .
867 . --for 循环
868 . declare
869 . cursor c is
技术网站
3445[2010/7/19 14:37:42]
870 . select * from emp;
871 . begin
872 . open c;
873 . for v_emp in c loop
874 . dbms_output.put_line(v_emp.ename);
875 . end loop;
876 . close c;
877 . end ;
878 .
879 . --带参数的游标
880 . declare
881 . cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is
882 . select ename,sal from emp where deptno=v_deptno and job=v_job;
883 . --v_emp emp%rowtype;
884 . begin
885 . for v_emp in c(30,'JAY' ) loop
886 . dbms_output.put_line(v_emp.ename);
887 . end loop;
888 . end ;
889 .
890 . --可更新的游标
891 . declare
892 . cursor c is
893 . select * from emp for update;
894 . --v_temp c%rowtype;
895 . begin
896 . for v_temp in c loop
897 . if(v_temp.sal < 2000) then
898 . update emp set sal=sal*2 where current of c;
899 . elsif(v_temp.sal=5000) then
900 . delete from emp where current of c;
901 . end if;
902 . end loop;
903 . commit;
904 . end ;
905 .
906 . 第49~ 50课: procedure
907 . create or replace procedure p
908 . is
909 . cursor c is
910 . select * from emp for update;
911 . begin
912 . for v_temp in c loop
913 . if(v_temp.deptno=10) then
914 . update emp set sal=sal+10 where current of c;
915 . elsif(v_temp.deptno=20) then
916 . update emp set sal=sal+20 where current of c;
917 . else
918 . update emp set sal=sal+50 where current of c;
919 . end if;
920 . end loop;
921 . commit;
922 . end ;
923 .
924 . --带参数的存储过程
925 . create or replace procedure p
926 . (v_a in number,v_b number,v_ret out number,v_temp in out number)
927 . is
928 . begin
929 . if(v_a<v_b) then
930 . v_ret:=v_b;
931 . else
932 . v_ret:=v_a;
933 . end if;
934 . v_temp:=v_temp+1;
技术网站
3445[2010/7/19 14:37:42]
935 . end ;
936 .
937 . declare
938 . v_a number:=10;
939 . v_b number:=20;
940 . v_ret number;
941 . v_temp number:=99;
942 . begin
943 . p(v_a,v_b,v_ret,v_temp);
944 . dbms_output.put_line(v_ret);
945 . dbms_output.put_line(v_temp);
946 . end ;
947 .
948 . /*
949 . 存储过程在创建过程中如果出现错误,仍然会创建,并且不会提示错误在哪儿,可以使用
950 . show error 来查看错误出现在哪儿。
951 . */
952 .
953 . create or replace function tax_sal
954 . (v_sal number)
955 . return number
956 . is
957 . begin
958 . if(v_sal<2000) then
959 . return 0.10;
960 . elsif(v_sal<3000) then
961 . return 0.20;
962 . else
963 . return 0.30;
964 . end if;
965 . end ;
966 .
967 . --触发器
968 . create table emp_log
969 . (
970 . uname varchar2(20);
971 . action varchar2(10);
972 . atime date
973 . );
974 . create or replace trigger trig
975 . after /before insert or update or delete on emp2 for each row
976 . begin
977 . if inserting then
978 . insert into emp_log values( USER, 'insert',sysdate);
979 . elsif updating then
980 . insert into emp_log values( USER, 'update',sysdate);
981 . else
982 . insert into emp_log values( USER, 'delete',sysdate);
983 . end if;
984 . end ;
985 .
986 . update dept set deptno=99 where deptno=10;
987 . --违反约束条件
988 . create or replace trigger trig
989 . after update on dept for each row
990 . begin
991 . update emp set deptno=:NEW.deptno where deptno=:OLD.deptno;
992 . end ;
- 尚学堂oracle笔记
- 尚学堂 oracle笔记
- 尚学堂oracle笔记
- 尚学堂Oracle笔记整理
- 尚学堂oracle笔记1
- 尚学堂oracle笔记2
- 尚学堂Oracle学习笔记
- 尚学堂马士兵老师oracle笔记
- 尚学堂Oracle笔记（1）
- 尚学堂Oracle笔记（2）
- 尚学堂Oracle 课堂笔记(收藏)
- 尚学堂马士兵老师oracle笔记
- 尚学堂Oracle经验
- 尚学堂Oracle经验
- 尚学堂学习笔记。。。
- 尚学堂笔记
- 尚学堂笔记
- 自己学习尚学堂的Oracle笔记（1）
- 等待队列与工作队列实例
- JVM调优总结 -Xms -Xmx -Xmn -Xss
- C#里byte[] 怎么截取需要的字节;如何得到一个byte[]的值
- 基于OO的不间断滚动分享，支持滚动方向、是否自动滚动等自定义
- sql 索引
- 尚学堂oracle笔记
- LSIP 疑惑1
- 为什么三次握手
- 你若安好
- 自定义AlertDialog样式,根据屏幕大小来显示
- apache做反向代理服务器
- eclipse 将android项目打包成签名的apk文件
- EJB学习笔记 （待续）
- Android非常简单的使应用程序全屏显示
