老高-Oracle学习笔记
01_sqlplus_introduction.avi
三种不同的命令行的客户端
Oracle需要了解的第一个:sqlplus，当启动sqlplus需要让你输入，用户名口令字符串 Sqlplus是Oracle的一个客户端，Oracle大多数的东西都是在命令行执行， 输入用户名:scott 口令:tiger，进入oracle命令行
3个界面:图形版界面，命令行界面，
网页界面(isqlplus):在浏览器地址栏输入::5560/isqlplus/ 输入用户名:scott 口令:tiger进入
有一个著名的oracle客户端叫toad
Oracle还有一个客户端plsql develement
02_unlock_user.avi
使用超级管理员登录到数据库上:sqlplus sys/bjsxt as sysdba , 当成DBA登录到服务器上
连上之后
更改user :alter user scott account unlock; 更改用户 解除锁定
03_table_structures.avi
第二章SQL语言
Sql语言是在数据库地下进行操作的专门的语言，sql语言本身是一种
标准
excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载
语言，它是一个国际标准，它定义了套标准SQL1922， SQL1999， SQL在大多数数据库上通用，或许有轻微的改变
包含四大语句:
1. 查询语句
查询语句只有一种就是select语句
2. DML语句
DML语句包含Insert,Update,Delete等常用语句
3. DDL语句
DDL语句包含Create, Alter， Drop等常用语句
4. 事务控制语句
包含Commit， Rollback等常用语句
还有一大类语句:叫DCL语句(Data Control Language)，主要用于权限的分配与回收，由于与开发关系不是十分密切，不做重点讲解
最重要的就是select语句，任何select语句全部要背过，select语句必考，不会考其它的
Select语句就是从表里把数据选出来
首先熟悉试验中的数据
第一条语句:desc emp;, desc 表名; 列出 表头 == 字段 == 列
Varchar2 , 可变字符串,支持国际化
NUMBER(7,2), 7位的数字，2位的小数
每行显示的宽度set linesize 200;
显示的页数 set pagesize 30;
emp,雇员信息表
EMPNO 雇员编号 NOT NULL NUMBER(4)
ENAME 雇员姓名 VARCHAR2(10)
JOB 工作岗位 VARCHAR2(9)
MGR 该雇员经理人的编号 NUMBER(4)
HIREDATE 入职时间 DATE
SAL 薪水 NUMBER(7,2)
COMM 津贴 NUMBER(7,2)
DEPTNO 雇员所在部门编号 NUMBER(2)
表内容:
EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
------ -------------------- ------------------ ---------- --------------
---------- ---------- -
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
------ -------------------- ------------------ ---------- --------------
---------- ---------- -
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81
3000 20
7934 MILLER CLERK 7782 23-1月 -82
1300 10
dept,部门信息表
DEPTNO 部门编号 NOT NULL NUMBER(2) DNAME 部门名字 VARCHAR2(14) LOC 部门所在地 VARCHAR2(13) 内容:
DEPTNO DNAME LOC
------ ---------------------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
salgrade薪水等级表
GRADE 薪水等级 NUMBER LOSAL 该等级的最低薪水值 NUMBER HISAL 该等级的最高薪水值 NUMBER 内容:
GRADE LOSAL HISAL
----- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
熟悉表里面的数据:
第一个select语句:select * from 表名;
Select * from emp;
04_select_1.avi
1.简单select语句:
例子1:取出一张表中所有的数据
Select * from emp;
例子2:取出某些字段的值
Select empno, ename, deptno from emp; 2.包含算术表达式的sql语句
:取出emp中所有人的年薪及名字 例子1
Select ename , sal*12 from emp; 例子2:在emp表中取出2*3的结果
Select 2*3 from emp;
例子3:取出2*3的一条记录
Select 2*3 form dual;--当我们需要显示一个结果的时候就用系统提供的dual虚表 例子4:取出当前系统时间
Select sysdate from dual;--sysdate在Oracle中表示当前系统时间 3.含有别名的sql语句
例子1:select ename, sal*12 annual_sal from emp; 例子2:如果想让别名强制大小写混合可以使用双引号
Select ename, sal*12 “Annual_sal” from emp;
例子3:如果想让别名中有空格也使用双引号
Select ename, sal*12 “Annual sal” from emp;
5.处理含有空值的字符串
例子1:算某人一年的年薪,含有任何null值的数学表达式最后的结果都为null
Select ename, sal*12+comm from emp 例子2:含有任何null值的字符串表达式中，null被当作空字符串处理
select empno, ename ||'and his manager number is '|| mgr from emp;
字符串连接符:||
Select语句中用单引号表示字符串
6.在select中使用单引号
例子1:用两个单引号表示一个单引号
select empno, ename || ' ''s manager is ' || mgr from emp;
05_distinct.avi
7.消除重复值
例子1:select distinct deptno from emp;--用distinct消除结果集中的重复信息 例子2:用distinct修饰多个字段的时候，指的是消除后面所有字段的组合重复结果
Select distinct job, deptno from emp;--指job和deptno都相同的删除
06_where.avi
使用where对数据库设定条件限制，where,过滤条件，过滤相关的数据 1. 等值判断
例子1:显示部门编号为10的所有员工的详细信息
select * from emp where deptno = 10; 例子2:显示名字为KING的员工的详细信息
判断字符串是否相等时，字符串要用单引号引起来，单引号中字符串内容是要区分大小写的
Select * from emp where ename = ‘KING’;
2. 非等值判断(> < >= <= <>)
例子1:取出薪水大于2000的所有员工的名字和薪水
Select ename, sal from emp where sal > 2000; 例子2:字符串大小比较:是比较字符串ASCII码值的比较，先比较第一字母，依次„„
Select ename, sal from emp where ename > ‘DBA’;
例子3:取出所有部门号不是10的雇员的名字和薪水
Select ename, sal from emp where deptno <> 10;
例子4:取出薪水位于800和1500之间的雇员名字和薪水
Select ename, sal from emp where sal >= 800 and sal =< 1500;
Select ename, sal from emp where sal between 800 and 1500;
注意:使用between and 的时候，包含了最大值和最小值
3. 空值的处理
例子1:取出津贴值为空的所有雇员的名字
Select ename from emp where comm is null; 例子2:取出津贴不为空的所有雇员的名字
Select ename from emp where comm is not null; 4. In语句:用于塞选某一个值
例子1:把薪水是800，1250， 1500， 2000 的雇员信息取出来
Select ename, sal from emp where sal in (800, 1250, 1500, 2000);
也可以用于字符串操作
例子2:把名字为SMITH, ALLEN, KING 的雇员取出来
Select ename, sal from emp where ename in (‘SMITH’, ‘ALLEN’, ‘KING’); 5. 日期处理
Oracle默认的日期格式为:DD-MON-RR
例子1:查询在81年2月20号以后入职的员工
Select ename, hiredate from emp where hiredate > ’20-2月-81’;
Select ename, hiredate from emp where hiredate > ’20-2月-1981’; 如果想用自己定义的日期格式，可以使用to_char or to_date函数，
6. AND， OR， NOT
例子1:查询部门标号为10 并且薪水>1000 的员工
Select ename, deptno, sal from emp where deptno = 10 and sal > 1000;
例子2:查询部门编号为10或者工作岗位为CLERK 的员工
Select ename, deptno, job from emp where deptno = 10 or job = ‘CLEARK’; 例子3:查询薪水没有位于800, 1500, 2000 之中的员工
Select ename, sal from emp where sal not in (800, 1500, 2000);
7(模糊查询
使用like关键字，和通配符 % , 表示0个或多个字符，_ 表示1个字符
例子1:查询名字中含有ALL的人员
Select ename from emp where ename like ‘%ALL%’;
例子2:查询第二字母中含有 A 的雇员
Select ename from emp where ename like ‘_A%’;
例子3:查询名字中含有 % 等通配符的数据时，使用转义字符 \
Escape , 自定义转移字符, 系统默认的转义字符是 ‘\’
Select ename from emp where ename like ‘%\%%’escape ‘\’;
07_order_by.avi
使用它order by 对数据进行排序
例子1:按照名字的升序进行排序
Select ename, sal from emp order by ename; Select ename, sal from emp order by ename asc; , 用asc关键字指出按升序排列 例子2:按照雇员编号的升序排序
Select ename , deptno from emp order by deptno;
Select ename, deptno from emp order by deptno asc;
例子3:按照名字降序排列
Select ename, deptno from emp order by ename desc;
例子4:按照部门编号降序排列
Select ename, deptno from emp order by deptno desc;
08_sql_function_1.avi
09_sql_function_2.avi
常用SQL函数
1.Lower()函数, 将字符串全部转换成小写
例子1:将雇员中名字含有’A’或’a’的人员全部显示出来
Select ename from emp where ename like ‘%A%’or ename like‘%a%’;
Select ename from emp where lower(ename) like ‘%a%’; 2.Upper()函数将字符串全部转换为大写
例子2:将雇员中名字含有’A’或’a’的人员全部显示出来
Select ename from emp where upper(ename) like ‘%A%’; 3.substr()函数
例子1:从第一字符开始共截3个子串
Select substr(‘Hello’, 1, 3) from dual; , Hel Substr , 可以省略第三个参数
例子2:从第二个字符开始，截取到整个字符串结束
Select sbustr(ename, 2) from emp; 4.Chr()函数
例子1:求一个与某个ASCII码值对应的字符
Select chr(65) from dual; 5. Ascii()函数
例子:求一个字符的ASCII码值
Select ascii(‘A’) from dual;
6. round()函数 , 对参数值进行四舍五入的操作
例子1:对23.652进行四舍五入操作
Select round(23.652) from dual; , 24
可以指定四舍五入到小数点后几位
例子2:对23.652四舍五入到小数点后2位
Select round(23.652, 2) from dual; , 23.65 可以用负数指定小数点前面几位
例子3:对23.652四舍五入到小数点前1位
Select round(23.652, -1) from dual; , 20
*7.to_char()
用于将数字或日期转换成特定的字符串，
To_char()有两个参数:
第一个参数:需要进行转换的日期或数字
第二个参数:特定的转换格式，对于数字有一下几个格式可以指定: 9 , 代表数字，若果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示
0 , 代表一位数字，如果该位没有数字则强制显示0
$ , 显示美元符号
L , 显示本地货币符号
. , 显示小数点
, ,显示千分位符号
例子1:select to_char(sal, ‘$99,999.9999’) salary from emp where ename =
‘ALLEN’;, $1,600.0000
例子2:select to_char(sal, ‘$00,000.0000’) salary from emp where ename =
‘ALLEN’;, $01,600.0000
对于日期:to_char()可以指定为下面的常用格式:
格式控制符 含义
YYYY、YY -----------------------------, 代表4位，2位数字的年份 MM -----------------------------, 用数字表示的月份
MON -----------------------------, 月份的缩写对中文月份来说就是全称 DD -----------------------------, 数字表示的日
DY -----------------------------, 星期的缩写，对中文的星期来说就是全称 HH24、HH12 ---------------------------, 12小时或者24小时进制下的时间 MI -----------------------------, 分钟数
SS -----------------------------, 秒数
有了这些格式，就可以把日期自定义为任何格式
例子1:select to_char(sysdate, ‘YYYY-MM-DD-HH24:MI:SS’) from dual; , 2008-04-04
*8.to_date()函数
将特定的字符串转换成日期格式，这个函数有两个参数
第一参数:自定义的日期字符串
第二参数:指定这个字符串的格式
例子1:将1981年3月2日中午以后入职的雇员信息取出:
Select * from emp where hiredate > to_date(‘1981-03-02 12:00:00’, ‘YYYY-MM-DD
HH12:MI:SS’);
*9.to_number()函数
讲指定的字符串转换成数字格式，这个函数有两个参数
第一参数:自定义的数字字符串
第二参数:指定这个字符串的格式
例子1:求薪水大于1200的员工信息
Select * from emp where sal > to_number(‘$1,200.00’, ‘$9,999.99’); *10.nvl()函数
用来处理空值，这个函数有两个参数:
第一参数:字段名或表达式，如果这个参数值为null，就返回第二参数值，否则返回第一参数值
例子:求每个员工每年的年收入(12个月的薪水+津贴)
因为comm的值为null，想要得到正确的结果，必须讲null值转换为0 Select ename, sal*12+nvl(comm, 0) from emp; *11.组函数包括5个函数:
(1).avg(): 求平均值
(2).max():求最大值
(3).min():求最小值
(4).sum():求总和
(5).count():求记录的数量
例子1:求薪水的总和、平均值、最大值和最小值
Select sum(sal), avg(sal), max(sal), min(sal) from emp;
例子2:求emp表中记录的数量
Select count(*) from emp;
Count()可以对单独字段使用，得到的是所有非空记录的数量
例子3:求comm字段中所有非空记录的数量
Select count(comm) from emp;
Count()可以和distinct一起使用，得到所有唯一值记录的数量
例子4:求emp表中deptno唯一的数量
Select count(distinct, deptno) from emp; 注意:函数名不是在所有数据库中通用
10_group_function.avi
11_group_by.avi
对表中的数据进行分组
例子1:计算每个部门的平均工资
首先将现有数据按照部门进行分组，然后再计算每个组员工的平均薪水。
Select deptno, avg(sal) from emp group by deptno; 例子2:计算每个部门的最大工资
Select deptno, max(sal) from emp group by deptno; 例子3:按照部门，和职位 的最大薪水进行分组
Select deptno, job, max(sal) from emp group by deptno, job;
使用group by 的规律:出现在select列表中的字段，如果没有出现在组函数中，则必须出现在group by子句中
典型错误:select ename, deptno, max(sal) from emp group by deptno;
例子4:求出每一个部门里赚钱最多的那个人的名字
Select ename, deptno, sal from emp where sal in (
Select sal from (
Select deptno, max(sal) sal from emp group by deptno
)
)
例子4:选出所用部门里工资最高的人的名字
Select ename, sal from emp where sal = (
Select max(sal) from emp
);
12_having.avi
使用Having对分组进行限制
如果我们要从分组数据中把某些特定的剔除去的时候，使用Having关键字
例子1:将平均薪水大于1000的组的平均薪水从emp这张表中选出来
Select avg(sal), deptno group by deptno having avg(sal) > 1000;
例子2:求薪水大于1200的雇员，按照部门进行分组，而且这些分分组后组内平均薪水必须大于1500，要查询分组的平均工资
Select avg(sal) from emp where sal > 1200 group by deptno having avg(sal) > 1500
Order by avg(sal);
13_表连接.avi
在where子句中进行多表连接(SQL1992)
实际需要在很多表中取数据，
例子:把员工姓名及员工所在部门的名字同时显示出来
Select ename, dname from emp, dept;
第二天上午:
13_表连接--21:41
为什么启动慢,因为每次启动Oracle的服务都会启动(解决办法，把Oracle服务设置成“手动”启动)
scott/tiger --> 用户名密码可以直接这样输入
把第一天学的内容用一句话
总结
初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf
出来
问:从emp这张表里把平均工资和部门编号列出来，并且过滤掉大写是A的名字 ，把剩下的数据按照部门编号进行分组，分组之后的平均薪水必须大于2000，按照部门编号的倒序排列
select avg(sal), deptno from emp
where ename not like '_A%'
group by deptno
having avg(sal) > 2000
order by deptno
这是一个单条的select语句，(只是从一张表里取数据)第一天已经讲完
今天讲:多表的连接
例子1:请你选出雇员的名字，和雇员在部门的名字,
错误写法:select ename, deptno from emp;//这里选的是部门的编号，问题里是让选
部门的名字
select dname, deptno from dept where deptno = 20;//选出了编号是20的这个人所
在部门的名字
正确写法:select ename, dname from emp, dept where emp.deptno = dept.deptno;
必须明确的指出重复字段是哪个表的 例如:
select ename, dname, dept.deptno from emp, dept where emp.deptno = dept.deptno;
指定哪张表的deptno实际上对它有一个效率上的影响
例子2:求每个人的薪水值包括他的名字
select ename, sal from emp;
例子3:求每个人的薪水值，名字和他的薪水处于第几个级别(非等值连接)
select ename, sal, grade from emp, salgrade
where sal between losal and hisal;
select ename, sal, grade from emp, salgrade
where sal >= losal and sal <= hisal;
例子4:求出他的名字，求出他所在部门的名称，求出他的薪水等级
首先
分析
定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析
这几个数据在3张表里
Select ename, dname, grade from emp e, dept d, salgrade s Where e.deptno = d.deptno and e.sal between s.losal and s.hisal;
例子5:求出职位除’PRESIDENT‘以外的所有雇员的名字，部门名称，薪水等级
select ename, dname, grade from emp e, dept d, salgrade s
where e.deptno = d.deptno and e.sal >= s.losal and e.sal <= s.hisal and job <>
'PRESIDENT';
13_子查询
例子1:求谁挣的钱最多,
错误的写法:select ename, max(sal) from emp;
正确的写法:select ename, sal from emp
where sal = (select max(sal) from emp);
例子2:求出来有哪些工资位于所有人平均工资之上
select ename, sal from emp
where sal > (select avg(sal) from emp);
例子3:按照部门进行分组之后挣钱最多的那个人的名字，部门编号,
select ename, sal, t.deptno from emp
join(select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
****理解子查询的关键-->把它当成一张表
例子4:(练习)求每个部门的平均薪水等级是多少,
Select t.avg_sal, grade, t.deptno from salgrade s
Join( select avg(sal) avg_sal, deptno from emp group by deptno) t
On ( t.avg_sal between s.losal and s.hisal);
14_self_table_connection.avi
例子1:求这个人的名字和他经理人的名字(自连接)
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;
自连接:为同一张表起不同的别名，然后当成两张表来用
15_SQL1999_table_connections.avi
SQL1992是在where语句里直接写表连接的条件
有一个小小的问题:
select ename, dname, grade from emp e, dept d, salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal and -->两个表的连接条件
job <> 'CLERK'; -->过滤条件
连接条件和过滤条件混在一起让人读起来SQL语句会困难一些，不太容易清楚
怎么把连接条件和过滤条件分开来呢,
原来的交叉连接:
select ename, dname from emp, dept; -->笛卡尔乘积56行
1999年标准的语法:(写法)
select ename, dename from emp cross join dept; cross join --> 叫做交叉连接(新语法定义的比较明确)
原来的等值连接:
select ename, dname from emp, dept where emp.deptno = dept.deptno;
新语法的等值连接:
select ename, dname from emp join dept on ( emp.deptno = dept.deptno);
等值连接的简单的写法:
select ename, dname from emp join dept using (deptno)
using (deptno) --> 是说我这个表的deptno等于你这个表的deptno
* using 的用法不推荐使用 --> 两张表中要有相同的字段，类型必须相同
非等值连接:
例子1:取出雇员名称和薪水等级
select ename, grade from emp e
join salgrade s
on (e.sal between s.losal and s.hisal);
三个表连接在一起的:
例子2:取出雇员名字，他的部门名称，和薪水等级其中名字第二个字母包含A的不要取出
select ename, dname, grade from
emp e join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where ename not like '_A%';
例子3:自连接新语法求这个人的名字，他经理人的名字
select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);
King如果想显示出来该怎么办呢,
外连接:
左外连接:可以把左边这张表的多余的数据(不能产生连接的数据给拿出来)
选出雇员名字和它经理人的名字(可以将没有经理人的那个人取出来)
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
例子4:求:每个雇员的名字，他所在部门的名称，全部选出来，并且把多余的部门也
选出来,
select ename, dname from emp e right outer join dept d on (e.deptno = d.deptno);
全外连接:即把左边的多余的数据拿出来，又把右边的多余的数据拿出来，
例:select ename, dname from emp e full join dept d on (e.deptno = d.deptno);
16_部门平均薪水的等级.avi
1.求部门平均薪水等级,
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
2.求部门中哪些人的薪水最高
select ename, sal from emp
join (select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
17_部门平均的薪水等级.avi
例子1:求出每个人的薪水等级，然后再平均求出的就是平均薪水等级
select deptno, ename, grade from emp
join salgrade s on (emp.sal between s.losal and s.hisal);
select deptno, avg(grade) from
(select deptno, ename, grade from emp
join salgrade s
on (emp.sal between s.losal and s.hisal)) t
group by deptno;
18_哪些人是经理.avi
select ename from emp where empno in (select mgr from emp);
select ename from mep where empno in (select distinct mgr from emp);
19_不用组函数求最高薪水.avi
(面
试题
中考模拟试题doc幼小衔接 数学试题 下载云南高中历年会考数学试题下载N4真题下载党史题库下载
)，考虑使用“自连接”:
select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1
join emp e2
on (e1.sal < e2.sal)
);
20_平均薪水最高的部门编号与名称.avi
1.先求每个部门的平均薪水
select avg(sal), deptno from emp group by deptno;
2.拿出最高的值
select max(avg_sal) from
(select avg(sal) avg_sal, deptno from emp group by deptno);
结果:
select deptno, avg_sal from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal, deptno from emp group by deptno)
)
21_embedded_group_functions.avi
select deptno, avg_sal from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno) -->组函数可以嵌套，但最多只
能嵌套两层。
22_平均薪水的等级最低的部门名称.avi
1.先求平均薪水
select avg(sal) from emp group by deptno;
2.求平均薪水的等级
把上面看成是一张表，另外一张表与它做连接
select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
3.取出最低等级
select min(grade) from
(
select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
);
最后结果:
select dname, t1.deptno, avg_sal, grade from
(
select avg_sal, grade, deptno from
(
(select avg(sal) avg_sal, deptno from emp group by deptno) t
join salgrade s
on (t.avg_sal between s.losal and s.hisal)
)
) t1
join dept d on (t1.deptno = d.deptno) where t1.grade =
(
select min(grade) from
(
select avg_sal, grade, deptno from
(
(select avg(sal) avg_sal, deptno from emp group by deptno) t
join salgrade s
on (t.avg_sal between s.losal and s.hisal)
)
)
);
23_view.avi
权限问题:
conn sys/bjsxt as sysdba;
grant create table, create view to scott;
conn scott/tiger
创建视图:
create view v$_dept_avg_sal_info as
select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
视图是什么东西,
就是一个子查询或者就是一张表,视图中的表叫虚表，实际数据依然在实际当中的表里面
从视图里面取数据:
select * from v$_dept_vag_sal_info;
视图的作用:首先建一个虚表，给一个别名，然后在虚表的基础上取数据就方便多了
第二天下午:
24_比普通员工的最高薪水还要高的经理人名称.avi
例子1:求比普通员工的最高薪水还要高的经理人名称
先求普通员工的最高薪水,
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null);
求比这个值还要大而且他本身必须是经理人
select ename from emp
where empno in (select mgr from emp where mgr is not null)
and
sal >
(
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null);
)
25_create_new_user_and_insert.avi
之前学的select语句全部掌握住
--面试题:比较效率
select * from emp where deptno = 10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno = 10;
先比较数字比较快一些，只要数字不对，后面就不用看了，只有数字相同的
情况下才比较后面的字符串这其实有点像短路的那个意思
类似前面学的短路与和短路或。这两条语句放到Oracle里面执行，Oracle很可能
对他进行优化，很可能把后面的语句放到前面去了。
DML语句--数据操作语言
常用的有三条 + select语句合称为--数据库的4大语句
* select
insert -- 把一条数据插入的数据表里面
update -- 修改现有的已经放在我们数据库里面的数据
delete -- 删除数据
这四句话应该牢牢记住
简单介绍Oracle的逻辑结构
大家还记得我们装Oracle的时候安装了一个新的数据库database,database的名字全局数据库名叫:
-->sxt.com然后为这个数据库创建了一系列的管理数据的进程叫做-->sid 它的名字叫做-->SXT。
在这个大数据库里面，逻辑把他们划分成一个一个的“表空间”，我们现在的表其实是放在不同的表空间里面，我们现在用的表emp, salgrade, dept,放在表空间，Users里，这是Oracle自己帮你建好的一个表空间，这个表空间放置scott里面所有的表，当你登陆上来之后，实际上是访问你这个表
空间里面所有的表，如果现在两个人同时登录同一台服务器，就是两个人同时访问当前表空间里的表
这时候会产生数据不一致的现象(你正在改呢，我给删了，产生数据不一致问题)，解决办法:在服务
器上创建一个新的用户，叫xxx，然后在users表空间里，为xxx分配表空间，再把xxx要访问的表导入
xxx这个表空间里面，这就是说明Oracle为什么是支持多用户的，每个用户之间的访问不会产生影响
综述:一个是DATABASE DATABASE分配不同的表空间，现在用的表空间是Users，然后Scott所有的相关
资源全部都放在表空间里面了，现在我在表空间里面开辟同样的资源，把这些资源全部Copy过来，然后
另外一个人登录进来访问的是他自己的资源，不再访问其他的资源
第一步:用超级管理员登录，只有超级管理员才能创建用户
conn sys/bjsxt as sysdba;
第二步:删除用户
drop user XXX cascade; 当你的数据库里面没有xxx这个名就不需要删除它
1-- backup scott
exp
把资源导入c:\temp
cd \
cd temp
del *.*;
y
exp
scott/tiger
u
yes
yes
yes
成功导出之后C:\temp下面会多一个文件EXPDAT.DMP ，这个文件里包含我们导入其他人相关的资源
scott 里面所有的东西全部就包含进来了。
2-- create user
create user xxx identified by xxx default tablespace users quota 10M on users;
分配权限:
grant create session, create table, create view to xxx;
3-- import the data
imp
c:\temp>imp
xxx/xxx
scott
连接新数据库
conn xxx/xxx
开始学习DML语句
1. desc dept;
insert into dept values (50, 'game', 'bj');
对数据进行备份的简单办法:
1.首先写rollback 刚才插入了一段数据，后悔了，不想插了。
2.备份整张表
create table emp2 as select * from emp;
create table dept2 as select * from dept;
create table salgrade2 as select * from salgrade;
create table emp3 as select * from emp;
第一种形式:不写字段的名字，直接按照字段的顺序挨着排的往里插
insert into dept2 values (50, 'game', 'bj');
第二种形式: 指定某些字段往里插，其他不插的字段默认都是空值
insert into dept2 (deptno, dname) values (60, 'game2');
第三种形式:可以用一个子查询，把子查询拿出来的数据，挨着排的都插入到表里，
前提是:子查询拿出来的东西和这张表的结构必须完全一样
select * 是选出4个值，这4个值会挨着排的插入的dept2里面
select into dept2 select * from dept;
25_rownum.avi
敲数据库代码:要按照思路，从里敲到外，不是背过
求:薪水最高的前5名雇员
select empno, ename from emp;
当你没有进行排序的时候，它默认的顺序是先导进去的先选出来，后导进去的后显示出来
在Oracle里面有一个尾字段叫-->rownum 它是按照1，2，3...进行排列的，它不显示
select empno, ename from emp where rownum <= 5;//取前5行
一共有14行，求10行以后的后4行怎么求,
select empno, ename from emp where rownum > 10; //这样的写法不成立。
正确的写法:
select empno, ename from
(
select rownum r, ename from emp
)
where r > 10;
*记住:rownum只能和< or <= 一块用，不能>，也不能=
选第10行这个人的名字,
1--> select rownum r, ename from emp;
2--> select ename from
( select rownum r, ename from emp ) where r = 10;
求薪水最高的前5个人，首先要对薪水最高的人进行倒序排列
select ename, sal from emp order by sal desc;
错误写法:
select ename, sal from emp where rownum <= 5 order by sal desc;//先取出数，后排序
正确写法:
select ename, sal from
(
select ename, sal from emp order by sal desc
)
where rownum <= 5;//首先按倒序排好，然后取前5个
求:取薪水最高的第6个人---第10个人(重点掌握)--》不论是哪种数据库，这个应用非常广泛
select ename, sal from
(
select ename, sal, rownum r from
(
select ename, sal from emp order by sal desc
)
)
where r >= 6 and r <= 10;
以上写法是在Oracle里面效率最高的写法
第三天上午:
26_homework_dml_and_transaction.avi
面试题:
有3个表S, C, SC
S(SNO, SNAME) 代表 (学号, 姓名)
C(CNO, CNAME, CTEACHER) 代表 (课号, 课名, 教师)
SC(SNO, CNO, SCGRADE) 代表 (学号, 课号, 成绩)
问题:
1，找出没有选过"黎明"老师的所有学生姓名。
select sname from s join sc on (s.sno = sc.sno) join c (c.cno = sc.cno) where
c.cteacher <> 'liming';
2，列出2门以上(含2门)不及格学生姓名及平均成绩
select sname where sno in
( select sno from sc where scgrade < 60 group by sno having count(*) >= 2 );
3， 即学过1号课程也学过2号课程所有学生的姓名。
select sname from s where sno in
( select sno from sc where cno = 1 and cno in
( select distinct sno from sc where cno = 2)
);
请用标准SQL语言写出答案，方言也行(请说明用什么方言)
update 更新表中的数据
把emp2这张表里，编号为10的，所有人的薪水提1倍
update emp2 set sal = sal * 2, ename = ename||'-' where deptno = 10;
delete 删除表中的数据
四条语句讲完了:背过语法格式
DDL-- 数据定义语言，建表，建视图，建其他一些东西。
1.创建表-- create table
create table t (a varchar2(10));
2.删除表 drop table
drop table xx;
跳过此阶段，先讲事务控制语句
事务transaction--> 就是一系列的操作要么同时完成，要么不完成。有点像同步synchronized
注意:与程序员交流的时候要说Transaction不要说“事务”
两个线程同时访问资源为什么会产生冲突的现象,
是因为你应该把他们的操作当成一个Transaction，每个线程自己单独一系列的动作都当成一个Transaction
要不同时完成，要么不完成。
从一个帐户把钱转到另一个帐户，需要2条Update语句，两条语句必须同时完成
Oracle认为我在其中的一系列的操作就是一个事务Transaction
所以，对于Oracle来说，一个Transaction起始于一条DML语句
那么它什么时候结束呢,
第一，敲rollback的时候，它回到最原始的状态算是结束，除此之外它还有几个会结
束，书P23
27_create_table_1.avi
第三章 数据库常用对象
为什么有变长字符串Varchar2还要有定长字符串Char呢,
因为存在效率问题，用Char效率高，但浪费空间，这就是拿空间换时间Hashtable也
是拿空间换时间
创建一张表，并往里插入一条数据
create table stu
(
id number(6),
name varchar2(20),
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50) -->最后没有“,”
)
28_constraint_1.avi
1.非空约束:
create table stu
(
id number(6),
name varchar2(20) not null,--> 加上约束条件非空
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50) -->最后没有“,”
)
约束条件本身也是一个对象，本身约束条件你个可以为它起一个名字
用constraint stu_name_nn not null -->为约束条件not null起名字为:stu_name_nn
如果不给约束条件起名字，系统会默认的给约束条件起一个名字
2.唯一约束-->在这个字段里面所有的记录不能取重复的值，每个值必须是唯一的
唯一约束后面 + unique ，当然也可以用constraint + 名字
字段级的约束:把约束条件+在字段名的后面
表级的约束:+在所有字段名的最后面
constraint stu_name_email_uni unique(email, name) 表示的是:这两个字段的组合不能重复
29_constraint_2.avi
主键:PRIMARY KEY 叫做:可以唯一标识整条记录的这样的一个东西
从语法上来说主键的约束可以理解为: 非空 ，唯一 两个的组合
主键约束在逻辑意义上代表着单独的，每一条记录，看到主键就知道你是一条单独的不同的记录
是唯一的代表的记录
我们可以用两个字段的组合作为主键
外键(最麻烦的约束):建立于一张表的两个字段，或者两张表的两个字段
constraint stu_class_fk foreign key (class) references class (id)
外键关系:是建立在两个字段上，某一个字段会参考另外一个字段里面的值，如果另外一个字段里面
没有这个值，你不能够把这个字段里面的值，设置成为其他的值。
被参考的字段必须是主键
被其他人参考的字段不能作为删除条件
主键约束和外键约束非常重要，老牢牢掌握其概念
30_questions.avi
面试题
31_alter_table_drop_table.avi
check约束:本身用的非常少，大多数对于数据的检验在java中已经校验过了
修改表结构:已经有一张表了，但是往了某一条字段，使用alter修改现有表的结构
添加addr字段---add()
alter table stu add(addr varchar2(100));
删除某一个字段---drop()
alter table stu drop(addr);
修改某个字段---modify()
alter table stu modify(addr varchar2(50));
*修改后的新的精度必须能够容纳原来有的数据
删除或者增加约束条件
去掉约束条件---drop constraint xxx
alter table stu drop constraint stu_class_fk;
修改约束条件:一般很少去修改约束条件，修改的方法是把原来的删掉，然后再添加个新的。
添加:
alter table stu add constraint stu_class_fk foreign key (class) references class
(id);
怎么去修改非空约束:自己看P31
怎么去删除一张表:drop table xxx;
32_oracle_dictionaries.avi
Oracle默认的一张表-->数据字典;
desc user_tables; --> user_tables 装着当前用户下面有多少张表
查当前用户里有哪些表,
select table_name from user_tables;
查询当前用户下面有哪些视图,
select view_name from user_views;
查询当前用户下面有哪些约束,
select constraint_name from user_constraints;
查询当前用户下面的约束在哪些表上面,
select constraint_name, table_name from user_constraints;
像上面这样的表被称作数据字典表
在Oracle中一共有多少个数据字典表呢,全部存储在另外一张表里面，相当于数据字典表的表
desc dictionary
33_indexes_and_views.avi
索引:也是一个新的数据库对象，索引就相当于我们字典里那个索引
创建索引:create index idx_stu_email on stu (email);
删除索引:drop index idx_stu_email;
忘记索引怎么查,
select index_name from user_indexes;
注意:当你给表里某个字段加约束的时候，比如主键约束，或者唯一约束，这个时候Oracle会帮你建立
对应的这个字段的一个索引，如果你的主键是两个字段的组合，它会帮你建立两个字段组合的一个索引
索引的作用:你为某个字段建立索引，别人访问这个字段的时候效率会更高。注意:是读的时候效率会
更高。索引的建立是读起来更快，修改起来更慢了(附加修改索引表)。
什么时候建立索引呢,
你访问这个字段，访问量特别大的时候，而且觉得效率比较低的时候，这个时候可以考虑建立索引，但是，
记住一点:不要轻易的建立索引。
另外的常用对象-->视图 : 它就是一张虚表，严格意义来讲:视图就是一个子查询
create view v$
建立学生表的视图
create view v$_stu as select id, name, age from stu;
要确定视图必须有用的时候才考虑建视图，视图是可以更新数据的，但是我们很少这么去用它
34_sequence_and_review.avi
序列:sequence 这是Oracle里面独特的东西，即特有的东西
sequence 一般用来做主键
建立一个帖子的数据库:
create table article
(
id number,
title varchar2(1024),
cont long
)
把帖子的数据库插入表里，插的时候得给单独的帖子起一个id号，
select max(id) from article;
insert in
一般的数据库都会提供这样的一种东西:专门用来产生一个独一无二的数，然后每次
自己往上递增一个1，或递增一个几，Oracle里面的这个东西叫-->Sequence
create sequence seq;
select seq.nextval from dual; -->sequence是一个对象，可以使用里面的属性nextval
往表里面插数据:
insert into article values (seq.nextval, 'a', 'b'); //这时候不用关心表里到底产生多少个了
怎么样删除一个序列呢,
drop sequence xxx;
一般一个sequence对应一个字段
35_三范式.avi
数据库设计的三范式:
范式:数据库设计时的一些规则。而这些规则是由一个姓范的人规定的，所以叫范式
三范式设计的规则:
三范式所追寻的
原则
组织架构调整原则组织架构设计原则组织架构设置原则财政预算编制原则问卷调查设计原则
是:不存在冗余数据(同样的数据我不存第二遍)
第一范式的要求:1.要有主键(设计任何表都要有主键)
2.列不可分
第二范式的要求:当一张表里面有多个字段作为主键的时候，非主键的这些字段，不能依赖于部分主键
(只能依赖整个组合的主键，不能依赖部分)
叫做:不能存在部分依赖
第三范式的要求:不能存在传递依赖(除了主键之外的任何其他字段必须直接依赖于主键)
36_BBS_1.avi
1.论坛是要分板块的，板块里面有不同的帖子
2.帖子是有回复的，第2个人回复第1个人，第3个人回复第2个人，第4个人回复第3个人，第5个人回复
第2个人，这是一种树状结构
3.只有注册的用户才能发表帖子
4.每一个板块有自己的版主，版主可以删帖子
按照上面需求，设计表，来支撑我们整个BBS项目
37_BBS_2.avi
老师与学生探讨表的设计
第四天上午:
38_PL_SQL_1.avi
PL/SQL 是在Oracle里面的一种编程语言，是一种新的编程语言，在Oracle内部使用的编程语言。
每一种数据库都有这样的一种语言
一种语言最重要的是:数据类型，语法。
PL语言是为了补充SQL语言的，是带有了分支和循环的语言
第一个简单的程序:
begin
dbms_output.put_line('HelloWorld');
end;
/
必须先执行 set serveroutput on; 再执行上面的程序就能打印出结果
39_PL_SQL_2.avi
第二个小例子:
declare
v_name varchar2(20);
begin
v_name := 'myname';
dbms_output.put_line(v_name);
end;
/
再来认识一下整个语句块的构成:
declare
v_num number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then //其他的情况固定格式必须这样写
dbms_output.put_line('error');
end;
/
40_PL_SQL_3.avi
在PL/SQL里面boolean类型变量在定义的时候一定要给初始值
一个数据类型的例子:
declare
v_temp number(1);
v_count binary_integer := 0;
v_sal number(7,2) := 4000.00;
v_date date := sysdate;
v_pi constant number(3,2) := 3.14;
v_valid boolean := false;
v_name varchar2(20) not null := 'MyName';
begin
dbms_output.put_line('v_temp value:' || v_count);
end;
/
*查看当前用户的语句:show user
Oracle 里面的put_line()不能打印boolean类型的值
PL/SQL 里面--表示注释掉一行，
使用%type属性
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('Test');
end;
/
41_PL_SQL_4.avi
复杂的变量:
复合变量有两种:
第一种:table 相当于java里面的数组Array[]
在PL/SQL里面要声明数组，也要指定它的类型，PL/SQL里比较复杂，
必须重新定义新的类型，这里先声明类型，再用类型声明变量，下标允许有负
值
例子:
declare
type type_table_e_table_emp_empno;
begin
v_empnos(0) := 7369;
v_empnos(2) := 7839;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
/
第二种:record 相当于java里面的类
42_PL_SQL_3.avi
Record变量类型
例子:
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
--使用%rowtype声明record变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
43_PL_SQL_5.avi
PL/SQL里面--SQL语句的运用
用select语句，必须返回一条记录，并且只能返回一条记录。//如果返回太多，我变量装不了
PL/SQL里面的select语句必须和into语句一块用并且有且只有一条记录，没有不行，多了也不行。
例子1:
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_ename, v_sal from emp where empno = 7369;
dbms_output.put_line(v_ename || ' ' || v_sal);
end;
/
例子2:
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno = 7369;
dbms_output.put_line(v_emp.ename);
end;
/
其他语句的运用:
例子3:
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type := 'aaaa';
v_loc dept.loc%type := 'bj';
begin
insert into dept2 values (v_deptno, v_dname, v_loc);
commit;
end;
例子4:
declare
v_deptno emp2.deptno%type := 10;
v_count number;
begin
--update emp2 set sal = sal/2 where deptno = v_deptno;
--select deptno into v_deptno from emp2 where empno = 7369;
select count(*) into v_count from emp2;
dbms_output.put_line (sql%rowcount || '条记录被影响');
commit;
end;
44_PL_SQL_6.avi
PL/SQL 里面的DML语句
例子1:
begin
execute immediate 'create table T (nnn varchar2(20) default ''aaa'')';
end;
if语句:
取出7369的薪水，如果<1200, 则输出'low',如果 <2000 则输出 'middle', 否则'high'
declare
v_sal emp.sal%type;
select sal into v_sal from emp
where empno = 7369;
if(v_sal < 1200) then
dbms_output.put_line('low');
if(v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
45_PL_SQL_7.avi
--循环 1
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i+1;
exit when ( i>=11);
end loop;
end;
上面的循环相当于java里的 do-while 循环
--循环 2
declare
j binary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j + 1;
end loop;
end;
以上循环相当于 java 里的 while 循环
--循环 3
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
以上循环相当于 java 里的 增强 for 循环
46_PL_SQL_8.avi
--错误处理
例子1:
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10; --这条记录会返回很
多值，所以产生异常
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;
没有找着数据的异常:
例子:
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没数据');
end;
DBA经常使用的一种用来记录错误的做法:
例子:
创建表
create table errorlog
(
id number primary key,
errcode number;
errmsg varchar2(1024),
errdate date
);
创建序列sequence
create sequence seq_errorlog_id start with 1 increment by 1;
写你的程序
declare
v_deptno dept.deptno%type := 10;
v_errcode number;
v_errmsg varchar2(1024);
begin
delete from dept where deptno = v_deptno;
commit;
exception
when others then
rollback;
v_errcode := SQLCODE;
v_errmsg := SQLERRM;
insert into errorlog values (seq_errorlog_id.nextval, v_errcode, v_errmsg,
sysdate);
commit;
end;
执行
select * from errorlog;
报错误;
delete from dept where detpno = 10;
报错误
查看具体的出错信息:
select to_char (errdate, 'YYYY-MM-DD HH24:MI:SS') from errorlog;
书:P44--P45页
47_cursor1.avi
PL/SQL里面的重点-->游标
游标是什么意思呢,
请你挨着排的把emp那张表的每个人他的一些相关信息拿出来(注意:每个人都拿出来),拿出来之后
根据他不同的薪水值，来设定他新的薪水值 <1200 的 *2 , >1200 的 /2 等等
select 语句可以产生一个结果集，游标是指在结果集脑袋顶上的一个指针，游标跟java里面的
迭代器(interator)差不多,所以游标指的就是一个指针，它指在一个结果集上，所以有了这个
游标之后，你就可以对这个结果进行遍历，循环，先拿第一条，拿出来之后接下来next一下，
再拿第二条，游标是我们的重点，一定要理解。
例子1:
declare
cursor c is --cursor 是游标的意思，相当于一个指针，cursor的本意就是我们鼠标上的小指针
select * from emp;
v_emp c%rowtype; --v_emp用来存储我们这个游标所指向的结果集的记录
begin
open c; --打开游标 注意:cursor c 叫做声明游标 声明游标的时候PL/SQL不会真正的取数据
--只有打开游标的时候PL/SQL才会真真正正的取数据执行select语句
fetch c into v_temp; --把当前游标指向的内容拿出来放到v_emp变量里面去
--fetch拿出来之后游标自动的往下移一格
dbms_output.put_line(v_emp.ename);
close c; --用完游标之后要close c，把内存清掉
end;
没有游标的话，甚至我说让你把emp那张表里面，每一个记录都取出来，恐怕你都不知道怎么
样取,
如果取好多条怎么办,
应该和循环一起用，用一个东西取探测这个游标什么时候到底了。
下面来看看用do-while循环，也就是简单循环来遍历游标
例子2:
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp; --这个时候就把第一条记录放进去了
exit when (c%notfound); --用notfound这个属性和简单循环配合来遍历这个游标指向最后 一条的时候能fetch到记录
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
多问一句:
我把dbms_output.put_line(v_emp.ename);放到
exit when (c%notfound); 上面，最后输出的结果会是什么样子,
当你到最后一条记录的时候fetch 它，没有找到，那你还打印它的值，就把上一条记录的值再打印一遍，
所以这三条语句顺序是不能颠倒的。
48_cursor2.avi
另外两种循环，一种是while循环，一种是for循环
例子1:
declare
cursor c is
select * from emp;
v_emp emp%rowtype;
begin
open c;
fetch c into v_emp; --首先fetch一下
while(c%found) loop --如果找到了 loop循环
dbms_output.put_line(v_emp.ename);
fetch c into v_emp; --接下来再fetch
end loop;
close c;
end;
while循环的写法:如果找到了，循环，打印一行,然后再fetch，这是while循环的写法
问大家一句:如果把fetch c into v_emp;放到
dbms_output.put_line(v_emp.ename)前面 什么效果,
---->第一句没打，最后一句打了两遍
为什么打印两遍，最后一条找到了，找到了之后你还找，最下面的没了，找就再找一次上面的
所以，最后一条记录打了两遍
for循环:
declare
cursor c is
select * from emp;
begin
for v_emp in c loop --上面不用声明变量v_emp，for开始的时候自动声明v_emp
-- c不需要打开，for开始的时候自动打开，结束的时候自动关闭
-- 不需要每次都fetch，它自动帮你fetch完了进入下一条记录
-- 它挨着排的循环每条记录，然后挨着排的放到v_emp里面
dbms_output.put_line(v_emp.ename);
end loop;
end;
--看这三种循环，for循环恐怕是最简单，并且它很不容易出错，前后语句不容易颠倒，你也不需要根据cursor
的属性来判断cursor是不是结束，所以for循环是我们平常用的最多的循环
--带参数的游标
例子:
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type)
is --这个游标的语句是什么 --> is
select ename, sal from emp where deptno = v_deptno and job = v_job;
--v_temp c%rowtype; --v_temp 不需要声明，用的for循环
begin
for v_temp in c (30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
--可更新的游标 -->不是重点，大多数游标是用来做遍历的
declare
cursor c
is
select * from emp2 for update; --为了更新才使用的游标
--v_temp c%type;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c; --当前游标指到哪条记录上就更新
--哪条游标
--这个时候不需要知道游标到哪条记录了
--游标到哪条记录就更新哪条记录
elsif (v_temp.sal = 5000) then --这个要小心,这里用 = 来判断
delete from emp2 where current of c;
end if;
end loop;
49_produre_1.avi
这个时候我们可以随便写PL/SQL程序了
例子:
declare
cursor c
is
select * from emp2 from update;
--v_temp c%rowtype;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif(v_temp.sal = 5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;
/
如果你下次登录进来还想执行这个程序你怎么办,有没有一个办法，比如给这个程序起一个名字
以后调用的时候直接调用这个名字就行了,
-> 有，这个办法就是创建存储过程，每种数据库都会提供编写数据库存储语言，实际上它就是带有
名字的PL/SQL程序块
最简单的存储过程的例子:
create or replace procedure p --如果有就把它替换，没有就创建
is
--除了以上语句，剩下的语句跟PL/SQL语句一模一样
cursor c is
select * from emp2 from update;
begin
for v_emp in c loop
if(v_emp.deptno = 10) then
update emp2 set sal = sal + 10 where current of c;
elsif(v_emp.deptno = 20) then
update emp2 set sal = sal + 20 where current of c;
else
update emp2 set sal = sal + 50 where current of c;
end if;
end loop;
commit;
end;
/ --执行后，说这个存储过程已经创建，不等于这个存储过程已经执行了
你要执行这个存储过程有两个办法，
第一个:exec p;
select sal from emp2; --执行这个语句会发现，sal已经改变
第二个:
begin
p;
end;
/ --执行这个块，它说PL/SQL过程成功完成。
select sal from emp2; --测试执行后的结果
总结:存储过程就是一个块，只是这个块带有名字，执行的时候只要执行名字就可以了
--带参数的存储过程(这个稍微复杂一些)
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
--在存储过程里面，参数可以分为不同的类型
-- in 叫做传入参数->谁调用这个存储过程谁负责给v_a赋值,从调用这个环境往我们
这个存储过程里头传的参数
-- out 叫做传出参数:存储过程是没有返回值的,它就借助于传出参数,由存储过程把这个值传出到调用过程(调用的环
-- 境)里面去
-- out是负责往外传的,in是负责接收的
-- v_b 中间什么都没写，默认是in，是接收参数用的
-- v_temp 你还可以既可以接收，又可以传出
is
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b; --给v_ret赋了值
end if;
v_temp := v_temp + a; --既用了原来的值，又给它了新的值
end;
/ --创建好这个过程怎么用这个过程,
调用过程:
declare
v_a number := 3;
v_b number := 4;
v_ret number; -- 传一个空篮子，让它往里面装东西，装完了给我用
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp); -- 调用过程,传入参数进去
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
注意:存储过程语法错误的时候，它只告诉你:创建的过程带有编译错误。但是不告诉
你哪错了
如果你想知道哪里错了,
show error
接下来会告诉你哪里错误，
即便是语法错误，过程依然会创建
50_procedure_2.avi
怎么样删除一个存储过程(procedure)呢,
讲另外一种对象:Function-->函数，不是重点，用的不是十分多，要了解就可以了
例子:用来计算薪水的个人所得税
create or replace function sal_tax
(v_sal number)
return number --返回值是number类型
is
begin
if(v_sal < 2000) then
return 0.10;
elsif(v_sal < 2750) then
return 0.15;
else
return 0.20;
end if;
end;
/
调用时候的用法:
select lower(ename), sal_tax(sal) from emp;
比较重要的概念——>触发器
要求:概念牢牢掌握住，具体的写法可以忘了，无所谓
触发器:就是当你做一件事的时候,什么叫触发呢,你一碰发生了这种情况，它就发生
了一件事，这叫触发,
你拿火柴一点炮捻儿，它就着了，这叫触发,它着了就长生另外一个事件，又触发另外
一个事件,-->炮响了。
例子:
create table emp2_log -- emp这张表的操作记录
(
uname varchar2(20), -- 谁,
action varchar2(10), -- 做了什么样的操作,
atime date -- 在什么时间做的,
);
它是用来记录的,比如:有一张非常重要的表，谁对这张表进行了操作,都应该记录下来,
这个时候触发器就有用了。
比如:银行里面的某张转账表,谁要对这张表操作，一定记录谁在哪天转了哪个帐,怎么记录呢?
可以使用触发器来记录它.
记录这样一个触发器:
create or replace trigger trig --触发器叫trigger,不能直接执行，必须依附在某张表上面
after insert or delete or update on emp2 --for each row
--指定在什么样的条件下会触发,1.当你插入完成之后,或者是删除完成之后,或者是更新
--完成之后,才会触发这个触发器，2.after可以换成before,一个在之前，一个在之后,
begin
if inserting then --如果正在插入,那么...
insert into emp2_log values(USER, 'insert', sysdate);--USER关键字,代表当前用户是谁
elsif updating then
insert into emp2_log values(USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values(USER, 'delete', sysdate);--记录到log文件中
end if;
end;
/
下面更新它:
update emp2 set_sal = sal*2 where deptno = 30;
下面查看操作有没有记录:
select * from emp2_log; --这样只触发了一次触发器.
加上for each row 之后,
发现 -> 每更新一行就会触发一次操作。这就是 for each row 的作用
51_trigger.avi
讲一点触发器的副作用:一般触发器很少这么用。但是了解一下没坏处，有这么回事.
update dept set deptno = 99 where deptno = 10;
来告诉我这么update可以么,
-> 不行，现在参考的deptno=10,你把它改成99，参考的人就成了,没娘的孩儿了.
假如说你就是想这么做,怎么办呢,
比如在你把这个值改成99的同时,也把哪些参考了10这个值，把这些参考的值也改成99,就可以了
两边的值,同步的变。
白板:这是我们dept这张表的记录,这里面原来的值是10,你现在改成一个新的值叫99,可以认为10
是旧的那条记录,99可以认为是新的那条记录,所以,一条update语句,会产生一个旧记录,一个新记录.
旧的记录是原来的,update之后的是新记录.虽然说的是同一条记录,同一条记录的两种状态,一个旧的
一个新的.
首先吧trigger给drop掉
drop trigger trig;
再创建一个新的触发器:
create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;
/
触发器创建成功之后，下面再执行上面的话
update dept set deptno = 99 where deptno = 10;
发现这行可以更新了.
查询一下:
select deptno from emp;
可以看到原来是10的，自己跟着变成99了，就是这个触发器的作用.这是个副作用,一般很少这么用.
问题:当你update的时候是先检查 约束条件,还是先触发 触发器呢?
--> 先触发触发器。
52_recursion.avi
讲项目中经常使用的树状结构的存储和展示.
create table article
(
id number primary key, -- 存储我们的帖子的id号number类型
cont varchar2(4000), --它的内容4000
pid number, --它的回复的父节点
--接下来这两个是冗余字段
isleaf number(1), --0 代表非叶子节点,1代表叶子节点(叶子节点:这个节点下面没有其他的子结点了)
alevel number(2) --它的级别是哪个级别
);
insert into article values(1, '蚂蚁大战大象', 0, 0, 0); --这个id号不一定是连续的
insert into article values(2, '大象被蚂蚁打趴下了', 1, 0, 1);
insert into article values(3, '蚂蚁也不好过', 2, 1, 2);
insert into article values(4, '瞎说', 2, 0, 2);
insert into article values(5, '没有瞎说', 4, 1, 3);
insert into article values(6, '怎么可能', 1, 0, 1);
insert into article values(7, '怎么没有可能', 6, 1, 2);
insert into article values(8, '可能性是很大的', 6, 1, 2);
insert into article values(9, '大象进医院了', 2, 0, 2);
insert into article values(10, '护士是蚂蚁', 9, 1, 3);
commit;
蚂蚁大战大象
大象被打趴下了
蚂蚁也不好过
瞎说
没有瞎说
大象进医院了
护士是蚂蚁
怎么可能
怎么没有可能
可能性是很大的
问题:使用一个存储过程把这些数据插到表里面，再使用一个存储过程把它展现成上面的形式,
递归:函数里面调用函数自身
对于存储过程:存储过程里面调用存储过程本身。
53_recursion_2.avi
写递归最关键的要定义出来 递归函数 ,递归函数 最关键的要定义出来它的 参数 .和它的 返回值
咱么做展现，不用返回值，直接做展现就行了,参数最重要,那就分析一下参数怎么去定义?这时候要
分析递归的过程,递归过程什么样呢?根据它的 id 找它的 父id,根据它的 父id 找到
它的孩子,根据它
的孩子再找到它的孩子.那么分析这个参数肯定是一个id，因为只有传进来id，才知道怎么去找这个id
下面的孩子. 所以最关键定义出存储过程的参数
create or replace procedure p (v_pid article.pid%type, v_level binary_integer)
is
cursor c is select * from article where pid = v_pid;
v_preStr varchar2(1024) := '';
begin
for i in 1..v_level loop
v_preStr := v_preStr || '****';
end loop;
for v_article in c loop
dbms_output.put_line(v_preStr || v_article.cont);
if(v_article.isleaf = 0) then
p (v_article.id, v_level + 1);
end if;
end loop;
end;
--展现emp表的树状结构
create or replace procedure p
(v_empno emp.empno%type, v_grade binary_integer)
is
cursor c is
select * from emp where mgr = v_empno;
v_preStr varchar2(4000) := '';
v_i binary_integer := 0;
begin
while v_i < v_grade loop
v_preStr := v_preStr || '****';
v_i := v_i + 1;
end loop;
for v_emp in c loop
dbms_output.put_line(v_preStr || v_emp.ename);
p(v_emp.empno, v_grade + 1);
end loop;
end;
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where mgr is null;
dbms_output.put_line(v_emp.ename);
p(v_emp.empno, 1);
end;
浙公网安备 33021202002483