=====================================day1====================================
ORACLE公司 ORACLE 数据库
a) 安装和删除 orace9i 9.0.1.1 版本 9.2.1.1
超级用户 sys --- 类似于SQLserver的sa （董事长） 密码: change_on_install
system --- 第2个超级用户(总经理) 密码: manager
服务中 ： OracleServiceACCP 核心系统服务 一定要启动它 ACCP 就是刚才定的数据库的名字SID OracleOraHome90TNSListener oracle监听器 也是要启动的 应用程序和ORACLE之间的连接
如果监听器无法启动 如何解决??a) D:/oracle/ora90/network/ADMIN listener.ora改HOST 为 HOST = localhost
b) 某个键值给优化大师优化没了 需要改注册表 <1>改注册表 ImagePath 值D:/oracle/ora90/BIN/TNSLSNR <2> 如果改不了注册表 命令行去启动 cmd TNSLSNR 窗口最小化 不要关闭
使用ORACLE oracle自带的工具 sql*plus window版 dos版
普通用户 用户名:scott 口令:tiger
select * from tab; 查询有哪些表 desc <表名> 例如desc dept 查询dept表的结构 exit 退出 工具 clear screen 清除屏幕
第三方的工具 pl/sql developer 图形界面 但是在图形界面中写sql语句不方便 Enterprise Management Console 改文件 D:/oracle/ora90/network/ADMIN tnsnames.ora HOST = localhost
三个工具中 选一个方便使用的来操作
ORACLE启动以后 启动Tomcat会出现问题?? ORACLE启动后 会占用8080端口 而Tomcat默认使用8080端口 修改Tomcat的启动端口 server.xml 8080 改为 4001
c) sql语句 (ANSI92) 查询 select * from dept; select deptno,dname from dept; select deptno,dname from dept where deptno = 10; select deptno,dname from dept order by deptno desc;
select deptno,dname from dept order by deptno desc, dname asc; --查询没有经理的员工 (is null) --不能用 =null select * from emp where mgr is null; 修改 --改员工的津贴为1000 update emp set comm = 1000; --使用edit修改上次执行的sql语句 --对多个字段进行修改 update emp set comm=1500, sal = 2500, job = '销售员' where empno =7782; --把某个字段改为空值 update emp set comm=null where mgr is null; --作条件判断的时候是 is null/is not null(非空) --修改的时候 只能是set comm=null delete 删除 --删除满足条件的记录 delete from emp where empno = 7782; --删除表中所有的记录 delete from emp; --删除多个条件的记录 delete from emp where deptno =10 and sal > 300; delete from emp where deptno =10 or job = 'CLERK'; d) ORACLE的数据类型
ORacle 最大长度 SQLserver
字符 varchar2 变长 (4000) varchar char 定长 (2000) char
数字 number(n) n位整数 int / numeric(n) number(n,m) 小数 decimal(n,m) number -- 38位有效数字 可以表示小数 也可以表示整数
日期 date 年月日时分秒 datetime
大对象 文本 clob (4G) text (2G) 二进制 blob (4G) image (2G)
--建立表student create table student(--学生表 stuid number(4), --学号 stuname varchar2(20), --姓名 sex char(2), --性别 birthday date, --生日 score number(5,2) --分数 );
--加入主键 alter table student add primary key (stuid) ;
--建立表的同时加主键 create table student(--学生表 stuid number(4), --学号 stuname varchar2(20), --姓名 sex char(2), --性别 birthday date, --生日 score number(5,2), --分数 primary key (stuid,stuname) ); 或 create table student(--学生表 stuid number(4) primary key, --学号 stuname varchar2(20), --姓名 sex char(2), --性别 birthday date, --生日 score number(5,2) --分数 ); --插入数据 默认格式'07-5月-05' 2005年5月7号 insert into student values (1000,'Mike','男','07-5月-05',89.5); 设置日期的格式 alter session set nls_date_format = 'yyyy-mm-dd'; insert into student values (1001,'Jack','男','1805-05-01',90); 恢复默认格式 alter session set nls_date_format = 'dd-mon-rr'; rr 世纪的年(100年) 如果需要格式永久生效 需要修改注册表 insert into student values (1002,'Rose','女','1805-05-01',90); 注册表 LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0 增加一个字符串 NLS_DATE_FORMAT 值 yyyy-mm-dd dd-mm-yyyy
insert into student(stuid,stuname) values (1004,'Marry');
insert into student(stuid,stuname,score) values (1005,'Mar',null); insert into student(stuid,stuname,score,birthday) values (1006,'Marley',90,sysdate);
数字类型 number 整数 number(n) 最大长度是n的整数 number(4) 范围 -9999 到 9999 小数 number(p,s) 有效数字是p 小数位数是s number(7,2) 7位有效数字 2位小数 范围 -99999.99 到 99999.99 还可以直接使用number 可以表示整数和小数都可以 精度很高 10^-38 到 10^38次方 字符类型 varchar2 变长 varchar2(20) 最大4000个字节 '张三' -> 实际存放就是4个字节 char 定长 char(20) 最大2000个字节 '张三' -> 实际存放 后填16空格 补齐到20个字节 char(2) 存放性别 '男' '女' 大对象 字符型大对象 4G 文本 clob (character Large of object)
二进制大对象 图像和声音 4G blob (binary Large of object)
BFile 超过4G binary File 在数据库中存放指针 指向硬盘的一个文件
日期类型 Date 大小7个字节 年月日 还带有时分秒
体系结构 sqlserver 分成若干个数据库 use pubs 选数据库
oracle 分成用户 就一个数据库 连接到一个用户 connect scott/tiger
connect sys/change_on_install as sysdba show user 察看当前所在的用户 自己建立用户: <1> 只有超级用户才能建立用户 create user zhangsan identified by h123; grant connect to zhangsan; --登陆 grant resource to zhangsan; --建立数据库对象
删除用户 <1> 只有超级用户才能删除用户 drop user zhangsan cascade;
修改用户的密码: <1> 在自己的用户下修改自己密码 alter user zhangsan identified by a456;
<2> 超级用户可以改所有用户的密码 connect system/manager alter user system identified by s123; alter user sys identified by sys123; alter user scott identified by t123;
建立一个超级用户 <-> system权限相当 connect system/manager create user zhangsan identified by h123; grant dba to zhangsan;
JDBC操作ORACLE数据库 <1> jdbc-odbc桥 <2> jar包驱动 D:/oracle/ora90/jdbc/lib/classes12.jar a) thin: 不需要安装Oracle的客户端 (****)
accp是数据库的名字 Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@127.0.0.1:1521:accp"; String user = "scott"; String pass = "tiger"; Connection conn = DriverManager.getConnection(url,user,pass);
b) fat驱动: 需要安装Oracle的客户端 效率高 myaccp是通过net configuration tools 配置的一个连接串 Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:oci8:@myaccp"; String user = "scott"; String pass = "tiger"; Connection conn = DriverManager.getConnection(url,user,pass);
sql语句中使用函数 Oracle中的函数 分2类 单行函数 分组函数
单行函数 日期的函数 Oracle: sysdate 系统时间 sqlserver: getdate()
select sysdate from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select sysdate+1 from dual; 数字和日期进行运算 数字就表示天数 select sysdate - 7 from dual;
两个日期类型的数据相减 得到天数 select (sysdate-hiredate) as 天数 from emp;
add_months(date,num) 得到的是date 经过 num个月后的日期
select add_months(sysdate,12) from dual; 1年后的今天 select add_months(sysdate,-12) from dual; 1年前的今天
months_between(date1,date2) 返回2个日期之间的月数 date1 - date2
select months_between(sysdate,hiredate) from emp;
select months_between(hiredate,sysdate) from emp;
所在月的最后一天 last_day(date) 返回日期所在月的最后一天 select last_day(sysdate) from dual;
select last_day(sysdate)-2 from dual; 倒数第三天 select add_months(last_day(sysdate)+1,-1)+2 from dual; 本月的第三天
NEXT_DAY( SYSDATE,'MONDAY') 下一个星期一是几号？？ select NEXT_DAY( SYSDATE,'星期一') from dual;
转换函数 to_char() 把日期或数字 转化为字符串
可以取出日期中的年月日 (转换的掩码) select to_char(sysdate,'yyyy') from dual; select to_char(sysdate,'mm') from dual; select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'d') from dual; select to_char(sysdate,'day') from dual; select to_char(sysdate,'ddd') from dual;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss d day ddd') from dual; 转换数字 成字符串 select to_char(234) from dual;
to_date 转换字符串 成为日期
alter session set nls_date_format='dd-mm-yyyy';不想修改默认格式 插入'dd-mm-yyyy'作为日期？？？
insert into student(stuid,birthday) values (1122,to_date('1972-08-05','yyyy-dd-mm'));
insert into student(stuid,birthday) values (1122,to_date('08-05-1972 09:40:22','dd-mm-yyyy hh24:mi:ss'));
to_number 把字符串转成数字 select to_number('56') from dual; select to_number('aa') from dual; --不能转的
数字函数 大于n的最小整数 select CEIL(5.6) from dual; select CEIL(-5.6) from dual; 小于n的最大整数 select floor(-5.6) from dual; select floor(5.6) from dual;
求余数 select mod(3,2) from dual; select mod(-3,2) from dual;
四舍五入 select round(4.5) from dual; select round(4.4) from dual; select round(4.567,2) from dual; 保留2位小数
截断数字(舍去尾数) select trunc(4.5) from dual; select trunc(4.567,2) from dual;
select to_char(trunc(sysdate),'hh24:mi:ss') from dual;
字符函数 连接字符串 || select 'Hello'||' '||'World' from dual;
select ename||'-'||job from emp;
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月'||to_char(sysdate,'dd')||'日' from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
INITCAP 首字母大写 select initcap(ename),ename from emp;
=====================================day2=====================================
字符函数 concat 连接 || <1>显示dname和loc中间用-分隔 select deptno,dname||'----'||loc from dept; dual哑元表 没有表需要查询的时候 可以用它 select 'Hello World' from dual; select 1+1 from dual; 查询系统时间 select sysdate from dual; <2> initcap 首字母大写 select ename,initcap(ename) from emp; <3> lower 转换为小写字符 select ename,lower(ename) from emp; <4> upper 转换为大写 update dept set loc=lower(loc); update dept set loc=upper(loc); <5> LPAD 左填充 select deptno,lpad(dname,10,' '),loc from dept; <6> RPAD 右填充 <7> LTRIM 去除左边的空格 RTRIM 去除右边的空格
ALLTRIM 去除两边的空格
<8>replace 替换 translate 转换 select ename,replace(ename,'S','s') from emp; 用's'去替换ename中的'S' select ename,translate(ename,'S','s') from emp; <9> ASCII 求ASC码 chr asc码变字符 select ascii('A') from dual; select chr(97) from dual; select 'Hello'||chr(9)||'World' from dual; '/t' ascii码是 9 '/n' ascii码是 10
select 'Hello'||'/t'||'World' from dual;
<10> substr 字符截取函数 select ename,substr(ename,1,3) from emp; 从第1个位置开始 显示3个字符 select ename,substr(ename,4) from emp; 从第4个位置开始显示后面所有的字符 <11> instr 测试字符串出现的位置 select ename,instr(ename,'S') from emp; 'S'第1次出现的位置 select ename,instr(ename,'T',1,2) from emp; 从第1个位置开始 测试'T'第2次出现的位置 <12> length 字符串的长度 select ename,length(ename) from emp;
混合函数 求最大值 select greatest(100,90,80,101,01,19) from dual; 求最小值 select least(100,0,-9,10) from dual;
空值转换函数 nvl(comm,0) 字段为空值 那么就返回0 否则返回本身 select comm,nvl(comm,0) from emp; comm 类型和 值的类型是 一致的 复杂的函数 decode 选择结构 (if ... elseif .... elesif ... else结构) 要求: sal=800 显示低工资 sal=3000 正常工资 sal=5000 高工资 只能做等值比较
select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断') from emp; 表示如下的if else 结构 if sal=800 then '低工资' else if sal =3000 then '正常工资' else if sal = 5000 then '高工资' else '没判断' end if
sal > 800 变换 sal -800 > 0
判断正负 sign(x) x是正 1 x是负 -1 x是0 0 select sign(-5) from dual;
sal>800 sign(sal-800) = -1 sal<800 sign(sal-800) = 0 sal=800 sign(sal-800) = 1 sal > 800
如何做大于小于的比较？？？？ sal<1000 显示低工资 sal-1000<0 sign(sal-1000) = -1 1000<=sal<=3000 正常工资 3000<sal<=5000 高工资
select sal,decode(sign(sal-1000),-1,'低工资', decode(sign(sal-3000),-1,'正常工资',0,'正常工资',1,decode(sign(sal-5000),-1,'高工资',0,'高工资',1,'没判断'))) from emp;
select sal,decode( sign(sal-1000),-1,'低工资', decode(sign(sal-3000),-1,'正常工资', 0,'正常工资',1, decode(sign(sal-5000),-1,'高工资','高工资') )) as 工资状态 from emp; 一般的情况 decode(x,y1,z1,y2,z2,z3) if x= y1 then z1 else if x = y2 then z2 else z3 end if
分组函数 返回值是多条记录 或计算后的结果 group by sum avg count
<1> 计算记录的条数 count
select count(*) from emp; select count(1) from emp;
select count(comm) from emp; 字段上count 会忽略空值 comm不为空值的记录的条数
统计emp表中不同工作的个数 ???? select count(distinct job) from emp;
select distinct job from emp; select distinct job,empno from emp; select job,empno from emp; 得到的效果是一样的,distinct 是消去重复行 不是消去重复的列 <2>group by 分组统计 --在没有分组函数的时候 --相当于distinct 的功能 select job from emp group by job;
select distinct job from emp;
--有分组函数的时候 --分组统计的功能 统计每种工作的工资总额是多少?? select job,sum(sal) from emp group by job; --行之间的数据相加
select sum(sal) from emp; --公司的工资总额
统计每种工作的平均工资是多少?? select job,avg(sal) from emp group by job;
select avg(saL) from emp; --整个公司的平均工资
显示平均工资>1000的工作?？？ <1>统计每种工作的平均工资是多少 <2>塞选出平均工资>1000的工作
从分组的结果中筛选 having select job,avg(sal) from emp group by job having avg(sal) > 1000; group by 经常和having搭配来筛选
显示平均工资>公司的平均工资 的工作?？？select job,avg(sal) from emp group by job having avg(sal) > (select avg(sal) from emp);
计算工资在1000以上的各种工作的平均工资 大于2000的工作？？？ select job,avg(sal) from emp where sal > 1000 group by job having avg(sal) > 2000 ; where / group by /having 出现的顺序不能变
一般group by 和 having搭配 表示对分组后的结果的筛选 where子句 --- 用于对表中数据的筛选 <3> max min select max(sal) from emp; 公司的最高工资 select min(sal) from emp ; 公司的最低工资
找每个部门的最高和最低的工资?? select deptno,max(sal),min(sal) from emp group by deptno;找每个工作的最高和最低的工资?? select job,max(sal),min(sal) from emp group by job;找每个部门中每种工作的最高和最低的工资?? select deptno,job,max(sal),min(sal) from emp group by deptno,job;
select max(sal),min(sal) from emp group by deptno,job;
单个字段如果没有被分组函数所包含， 而其他字段又是分组函数的话 一定要把这个字段放到group by中去
约束 主键约束 -- 每个表要有主键，唯一的标识一行数据 非空约束 唯一性约束 外键约束 检查约束
脚本(SCRIPT) create table cla( --班级表 id number(2) primary key, --班级编号 cname varchar2(20) not null --班级名字 );
create table stu( --学生表 xh number(4) primary key, --学号是主键 xm varchar2(20) not null, --姓名非空 age number(2) check (age between 10 and 90),--年龄在10到90之间(10<= age <=90 ) birthday date, shenfenzheng number(18) unique, --身份证唯一 classid number(2) references cla(id) -- 班级编号外键 --(引用的一定是主键或唯一性约束的字段) ); <1>建立表的同时使用约束 create table student( --学生表 xh number(4) primary key, --学号主键 xm varchar2(10) not null, --姓名不能为空 sex char(2) check (sex in ('男','女')), --性别 birthday date unique, --日期 sal number(7,2) check (sal between 500 and 1000),--奖学金 sal >=500 and sal <=1000 classid number(2) references cla(id) ); --必须要先有cla表才对 --一定先建立班级cla表 主键约束 primary key not null check unique 唯一约束
create table student( --学生表 xh number(4) constraint pk_stu primary key, --学号主键 xm varchar2(10) constraint nn_stu not null, --姓名不能为空 sex char(2) constraint ck_stu_sex check (sex in ('男','女')), --性别 birthday date constraint uq_bir unique, --日期 sal number(7,2) constraint ck_sal check (sal between 500 and 1000)--奖学金 sal >=500 and sal <=1000 ); <2>建立约束的同时给约束指定名字，便于删除 create table cla( --班级表 id number(2) constraint pk_cla primary key, --班级编号 cname varchar2(20) constraint nn_cla not null --班级名字 ); create table stu( --学生表 xh number(4) constraint pk_stu primary key, --学号是主键 xm varchar2(20) constraint nn_stu not null, --姓名非空 age number(2) constraint ck_stu check (age between 10 and 90),--年龄在10到90之间(10<= age <=90 ) birthday date, shenfenzheng number(18) constraint uq_stu unique, --身份证唯一 classid number(2) constraint fk_stu references cla(id) -- 班级编号外键 --(引用的一定是另外表的主键或唯一性约束的字段) ); <3>建完表后加约束 学生表student create table student( --学生表 xh number(4), --学号 xm varchar2(10), --姓名 sex char(2), --性别 birthday date, --日期 sal number(7,2) --奖学金 ); 加约束 加主键 alter table student add constraint pk_stu primary key (xh); 加非空 alter table student modify (xm not null); 检查约束 alter table student add check(sex in ('男','女')); alter table student add constraint ck_sal check(sal between 500 and 1000));
给student加班级字段 alter table student add (classid number(2));
班级表cla create table cla( --班级表 id number(2), --班级编号 cname varchar2(20) --班级名字 );
添加 主键 alter table cla add constraint pk_cla primary key (id);加 not null alter table cla modify (cname not null);
学生表student create table student( --学生表 xh number(4) , xm varchar2(20) , --姓名非空 age number(2),--年龄在10到90之间(10<= age <=90 ) birthday date, shenfenzheng number(18), --身份证唯一 classid number(2) -- 班级编号外键 --(引用的一定是另外表的主键或唯一性约束的字段) );
加外键约束alter table student add constraint fk_stu foreign key (classid) references cla(id);
加主键alter table student add constraint pk_stu primary key (xh);
加not nullalter table student modify(xm not null);
加检查约束alter table student add constraint cc_age check (age >= 10 and age <=90);
加唯一约束 alter table student add constraint uq_sfz unique(shenfenzheng);
加外键约束 alter table student add constraint fk_stu foreign key (classid) references cla(id);
如何删除约束 alter table student drop constraint fk_stu;可以用一个统一的格式来删除 alter table 表名 drop constraint 约束名
<4>如何查看约束?? 约束一定加在表上
一个表上到底有哪些约束??? select constraint_name,constraint_type from user_constraints where table_name = 'STUDENT'--查看表上有什么约束 select * from user_constraints;--查看约束作用在什么字段上 select * from user_cons_columns where CONSTRAINT_NAME='PK_STU';
user_constraints数据字典表
<5>约束是如何起作用的??
create table cla( --班级表 id number(2) constraint pk_cla primary key, --班级编号 cname varchar2(20) constraint nn_cla not null --班级名字 ); create table stu( --学生表 xh number(4) constraint pk_stu primary key, --学号是主键 xm varchar2(20) constraint nn_stu not null, --姓名非空 age number(2) constraint ck_stu check (age between 10 and 90),--年龄在10到90之间(10<= age <=90 ) birthday date, shenfenzheng number(18) constraint uq_stu unique, --身份证唯一 classid number(2) constraint fk_stu references cla(id) -- 班级编号外键 --(引用的一定是另外表的主键或唯一性约束unique的字段) ); 主键 = 非空 + 唯一 非空 唯一 = 有值的话 值要不同 null的话 都是可以的 外键 = 有值 一定要在被引用的表的数据中 null的话 是可以的
<3>关联查询 子查询 功能正确 - 〉sql语句能查出数据 性能要好 - 〉sql语句尽量使用上索引
关联查询: 多个表 放在from子句后面 一定要带连接的条件 不然的话 变成 笛卡尔积
连接条件个数 = 表的个数 - 1
参加连接的字段 只要存放的数据代表相同的含义 就可以连接在一起 （不一定非要是外键关系或名字一样）
内连接 (多个表 完全匹配的记录查询出来) select dname,ename from dept,emp where dept.deptno = emp.deptno and dept.deptno=10;
标准SQL的写法 select dname,ename from dept inner join emp on dept.deptno = emp.deptno where dept.deptno=10; 连接的条件要使用on 来表示 筛选条件使用where来表示 select dept.deptno,dname,ename from dept,emp where dept.deptno = emp.deptno
外连接 (多个表中部分匹配的记录查询出来) 左外连接 右外联结 全外联结 查询部门名，员工名，以及没有员工的部门名??? 外连接 select dept.deptno,dname,ename from dept,emp where dept.deptno = emp.deptno(+) (+)的对面侧的表(dept)中没有匹配的记录显示出来
select dept.deptno,dname,ename from dept,emp where dept.deptno(+) = emp.deptno 标准sql来写 --左外连接 --dept表中没有匹配的记录显示 select dept.deptno,dname,ename from dept left outer join emp on dept.deptno = emp.deptno; --右外联接 --emp表中没有匹配的记录显示 select dept.deptno,dname,ename from dept right outer join emp on dept.deptno = emp.deptno;
--全外联接 --2个表没有匹配的记录都显示 select dept.deptno,dname,ename from dept full outer join emp on dept.deptno = emp.deptno;
--自连接 --自己的表和自己连接到一起 --通过别名把一张表 变成2张表
显示员工 和其直接上级的名字？？？？ select a.ename as 员工,b.ename as 经理 from emp a,emp b where a.mgr = b.empno(+);
a -- 员工的信息 b -- 领导的信息
--Sqlserver中的top N --ORACLE rownum的伪列 (用于分页显示)
select rownum,emp.* from emp
查询emp表中的前3行记录
select * from emp where rownum <= 3
查询emp表中的第三行记录
select * from emp where rownum > 2 and rownum < 4;
<1>1个表的数据的分页显示 emp 分页的条件: 每页显示4条
第3页的内容: 1-4 page 1 5-8 page 2 9-12 page 3 select * from (select rownum lineno,emp.* from emp) where lineno >= 9 and lineno <= 12;
<2> 2个表的数据查询的分页显示
select dname,empno,ename from dept a,emp b where a.deptno = b.deptno
select * from (select rownum lineno, dname,empno,ename from dept a,emp b where a.deptno = b.deptno )where lineno >=9 and lineno <= 12;
--Oracle中的层次查询(connect by 语句) 使用伪列 level
select lpad('+',level*2,' ')||ename from emp connect by prior empno = mgr start with mgr is null;
select lpad('+',level*2,' ')||ename from emp connect by prior empno = mgr start with ename='BLAKE';
--子查询 a) 关联子查询 求工资最高的前2名员工??? select * from (select * from emp order by sal desc) where rownum <= 2; 名次比较 转化为 人数比较 select * from emp a where (select count(*) from emp where sal > a.sal) <=1
b) 非关联子查询
比部门30的平均工资高的所有员工 select * from emp where sal > (select avg(sal) from emp where deptno=30)
=====================================day3=====================================
-----集合运算SQL语句 集合概念 (1,2,3,4,5,6) A (4,5,6) B
A和B 交集 (4,5,6) 公有的元素 intersect
A和B 并集 所有的元素 (1,2,3,4,5,6) union 去重复值 (1,2,3,4,5,6,4,5,6) union all 不去重复值
A和B 差集 A-B A中去除B中的元素 （1,2,3) B-A B中去除A中的元素 空集 minus create table A( num number(2));insert into A values (1);insert into A values (2);insert into A values (3);insert into A values (4);insert into A values (5);insert into A values (6);
create table B( num number(2));
insert into B values (4);insert into B values (5);insert into B values (6); 集合运算的基本要求 : 对应的字段的数据类型是一样的 字段的个数要一样 A和B 交集 select num from a intersect select num from b; A和B 并集 select num from b union select num from a;
select num from b union all select num from a; A和B的差集 a-b select num from a minus select num from b; b-a select num from b minus select num from a;
create table grade( --选修课程 xm varchar2(20) , --姓名 subject varchar2(20), --课程 mark number(5,2) , --分数 primary key (xm,subject));
insert into grade values ('Mike','Java',90);insert into grade values ('Mike','C++',85);
insert into grade values ('Martin','C++',87);insert into grade values ('Martin','Java',80);insert into grade values ('Martin','C#',76);
insert into grade values ('Rose','Java',87);
查询包含了Mike所选课程的别的同学的姓名？？？
select distinct xm from grade where subject in (select subject from grade where xm='Mike') and xm <> 'Mike';
思路: 前提是Mike选了课程的
Mike所选课程的集合 - 别的同学选课的集合 = 空集
select xm from grade a where not exists ( (select subject from grade where xm='Mike')minus ( select subject from grade where xm=a.xm)) and a.xm <> 'Mike'group by xm;
-- 技巧性的查询语句 1. emp如何备份一张表??? 复制数据： create table emp_bak as select * from emp;
新的表 emp_bak数据和原表emp完全一样 emp表中的约束 不能复制过去
复制结构: create table emp_nbak as select * from emp where 1=2;
sqlserver的复制表: select * into aa from emp; 建立表aa 插入emp中的数据到aa中
select * into bb from emp where 1=2; 复制结构
2. emp表中的数据 一次性的插入到表emp_nbak中??
insert into emp_nbak select * from emp;
insert into emp_nbak(empno,ename) select empno, ename from emp;
3. 别名 列的别名 select ename "姓 名" from emp;
表的别名 select a.ename,b.dname from emp a ,dept b where a.deptno = b.deptno
总结: 数据库 中的sql语句 <1> DDL (DATA Define Language) 数据定义语言 create ... drop .. drop table emp; -- 删除表的结构和数据 delete from emp; -- 删除数据,可以恢复 delete from emp where deptno=10; truncate table emp; --删除数据,不能恢复被删除的数据 alter .. create table student( stuid number(4) primary key, stuname varchar2(20), mark number(5,2) ); --增加字段birthday alter table student add (birthday date); --修改字段stuname的长度 alter table student modify (stuname varchar2(10)); --删除字段mark alter table student drop column mark; --给表改名字 rename student to stu; rename stu to student;
truncate ... truncate table emp;
<2> DML (Data manupulate language) 数据操作语言
select 查询语句 insert insert into emp(empno,ename,deptno) values (9999,'Mike',20); insert into emp select * from emp2; update update emp set ename='Mike', job='MANAGER', sal=900 where empno=7369; --给每个人涨工资20% update emp set sal = sal * 1.2; --修改每个人的工资为其所在部门的平均工资 update emp a set sal = (select avg(sal) from emp where deptno=a.deptno);
delete delete from emp; delete from emp where deptno=10;
<3> TCL(事务控制语言) Tansaction Control Language commit rollback savepoint 事务: DML语句必须形成一个整体来执行 delete update insert 银行转帐: 帐户zhangsan 帐户lisi 2000 3000 张三转1000 到lisi的帐户上???? -1000 +1000
create table account( acc_name varchar2(20) primary key, num number(7,2) );
insert into account values ('zhangsan',1000); insert into account values ('lisi',3000);
转帐 update account set num=num-1000 where acc_name='zhangsan'; update account set num=num+1000 where acc_name='lisi'; Oracle: 写在一个存储过程中
create or replace procedure p1 as begin update account set num=num-1000 where acc_name='zhangsan'; update account set num=num+1000 where acc_name='lisi';
commit; when others then rollback;
end;
java语言编程实现事务:JDBC本身存在事务模型的(显式的事务)
Connection conn = DriverManager.getConnection(); conn.setAutoCommit(false); .... conn.commit(); /conn.rollback();
sqlserver中的事务: 显式事务 (自动提交) set implicit_transactions on 打开隐式事务 set implicit_transactions off 关闭隐式事务 明确的声明隐式事务 begin transaction
update account set num=num-1000 where acc_name='zhangsan'; update account set num=num+1000 where acc_name='lisi'; if @@ERROR<> 0 rollback transaction else commit transaction
在Oracle中 DML语句所形成的事务提交的方法: <1> commit 提交事务
<2> 未提交的事务 后紧跟一条DDL/DCL(grant revoke) 语句 都会导致事务的提交的
savpoint 分解事务(大的事务 分解为若干小的事务)
update emp set sal=560 where empno=7499;
savepoint c1;
update dept set dname='AA' where deptno=30;
savepoint c2;
delete from emp where empno=7499;
rollback to c2; --delete给回滚 rollback to c1; --回滚update dept rollback; --回滚update emp
<4> DCL (Data Control Language) 数据控制语言 grant --- 授予权限 revoke -- 回收权限
a) 建立用户的时候 grant connect,resource to <用户>
b) 用户scott 能够访问 用户B的某张表??? scott: emp dept zhangsan: student
connect system/manager grant connect,resource to zhangsan identified by h123; connect zhangsan/h123; create table student( stuid number(4) primary key, stuname varchar2(20) ); insert into student values (1000,'JACK');
grant select update on <对象> to <用户> delete insert
all(select/update/delete/insert)
revoke select on <对象> from <用户>
级联授权(with grant option) 用户A 用户B 用户C student -----> --> grant select on student to B with grant option
grant select on A.student to C
约束: 表上或字段上的约束
数据的完整性
主键 非空 检查 外键 唯一性 缺省值 default
create table bj( --班级 cid number(2) primary key, cname varchar2(20) not null ); create table student( --学生 stuid number(4) primary key, --主键 stuname varchar2(20) not null, --非空 age number(2) check (age between 10 and 50), sfz varchar2(18) unique, classid number(2) references bj(cid) on delete cascade, sex varchar2(2) default '男' check (sex in ('男','女')) );
外键中被引用的字段 一定是主键或者具有唯一性约束的字段
create table grade( stuid number(4), subid number(2), mark number(5,2), primary key (stuid,subid) ) 建完表以后在加约束
create table student( --学生 stuid number(4), stuname varchar2(20), age number(2), sfz varchar2(18), classid number(2), sex varchar2(2) );--加主键约束 alter table student add primary key (stuid);--删除主键约束 alter table student drop primary key;
--加非空约束 alter table student modify (stuname not null);
--加检查 alter table student add constraint ck_1 check (age >=20 and age<=60);--删除约束 alter table student drop constraint ck_1;
--加唯一性 alter table student add (sfz unique);
--加外键 alter table student add foreign key (classid) references bj(cid) on delete cascade
---其他数据库对象 <1> 序列 sqlserver : create table subject( subid int identity(1,2) primary key, ...); oracle中如何实现自增长的字段??? a)建立一个序列的对象 create sequence seq1; b) create table subject( subid number primary key, subname varchar2(20) ); c) insert into subject (subid,subname) values (seq1.nextval,'aa'); 伪列 nextval ---下一个值 currval --- 当前值
create sequence seq2 start with 1000 increment by 5 maxvalue 2000 minvalue 900 cycle nocache ---- 没有缓存 cache 20 ---- 默认的cache是20个
1000 1005 --- 1010 --- 缓冲了以后的19个号
用途: 产生一个唯一的编号， 但是编号的连续性是不能保证的
<2> 视图 view 表 存放数据 视图 存放的是一条查询语句
create or replace view v_emp as select ename,sal from emp where deptno=10;
select * from v_emp;
=====================================day4=====================================
<2> 视图 view 表 存放数据 视图 存放的是一条查询语句 (基表)
create or replace view v_emp as select ename from emp where deptno=10;
select * from v_emp; 作用: a) 隐藏信息 增加安全性
emp (sal)
create or replace view rs_emp as select ename,empno,job from emp ;
select * from rs_emp;
select * from user_views; --数据字典表(用户所拥有的视图)
b) 报表 : view select * from <VIEW>
视图可以 实时反应表中的变化的 视图是否可以修改??? <1> 简单的视图(单表查询,没有分组和关联查询) 是可以通过视图修改基表的 <2> 关联查询,分组函数的视图是不能修改的with read only 只读视图 create or replace view v_emp as select ename from emp where deptno=20 with read only; with check option 检查select语句的where条件是否被违背
create or replace view v_emp as select empno,ename,deptno from emp where deptno=20 with check option; 不能修改数据 使得修改后的结果违背where条件 插入: insert into v_emp values (9999,'kl',10); 无法插入 违背where条件 删除: delete from v_emp where deptno=10; 和where条件无关
原则: 都设计只读视图 -> 简化查询 尽量不要通过视图 修改基表
<3> 同义词synonym --- 提高系统的安全性 私有同义词 -- 属于建立它的用户 create synonym myemp for scott.emp; 为scott下的emp表建立同义词myemp
select * from scott.emp -> select * from myemp;
a) zhangsan/h123 select * from scott.emp; create synonym myemp for scott.emp; select * from myemp; update myemp set sal=500;
b) scott/tiger emp 使zhangsan能访问它 grant select on emp to zhangsan; grant update on emp to zhangsan; 公有的同义词 超级用户才能建立 create public synonym memp for scott.emp; 所有的用户 都能访问这个同义词 connect zhangsan/h123 select * from memp; 把scott下的emp表的查询权限授予所有的用户 connect scott/tiger grant select on emp to public;
设计时候的使用: 人事系统 grant connect,resource to hrms identified by hrms; employee dept grant select on employee to test;
销售系统 grant connect,resource to sales identified by sales; orders customers 财务系统 grant connect,resource to cw identified by cw; salary
公用的用户: test / test123 connect (insert / delete / update ) 不能执行DDL (drop table) 系统运行 resource 系统开发的时候 授予的权限
建立synonym create synonym emp for hrms.employee;
<4> 索引 作用: 加快查询速度（select） 功能正确.... 性能好......??? 索引一定是建立在表的字段上的 索引的维护是自动进行的
a) 都某些具有约束的字段 自动建立索引 primary key -> index unique -> index
b) 自己根据需要来建立索引 create index idx_ename on emp(ename); 在表emp的ename字段上建立索引idx_ename drop index idx_ename; 删除索引idx_ename
create index idx_a on emp(job,sal)
emp (empno主键) -> 索引
select * from emp where empno=7369; 表emp字段empno没有索引 对emp表中的数据逐行塞选 表emp字段empno有索引 先在索引中查找empno=7369 -> rowid(每条数据的物理地址) -> 直接 利用rowid到emp中去定位
select * from emp; -> 无法使用任何索引 全表扫描
select * from emp where ename like '张%'; 只有带有where条件的select语句才可以使用上索引 create index idx_en on emp(ename); Oracle中索引的使用 可以通过执行计划来看到 table access full 全表扫描 index range scan 索引的范围扫描 table access by index rowid 通过rowid来访问表的纪录
<1> 索引过字段是不能使用函数和参与运算的 a. 查询在1980年参加工作的所有员工???
create index idx_date on emp(hiredate);
不能使用hiredate上的索引 select * from emp where to_char(hiredate,'yyyy')='1980';
可以使用hiredate上的索引 select * from emp where hiredate >= to_date('1980-01-01','yyyy-mm-dd') and hiredate < to_date('1981-01-01','yyyy-mm-dd')
b. 查询在50天以前参加工作的员工？？？ 不能使用hiredate的索引 select * from emp where hiredate + 50 < sysdate;
可以使用hiredate上的索引 select * from emp where hiredate < sysdate -50;
功能正确 性能优良
数据字典 -> 纪录系统内部结构的表和视图 sys -> 拥有一套数据字典 system -〉察看数据字典
命名规则: user_xxxx 用户所拥有的对象的表
all_xxx 用户有权察看的对象的表
dba_xxx 整个系统所拥有的对象的表
xxxx --- 对象英文名字的复数形式
user_tables user_views user_indexes
所有的数据字典的列表 select * from dict; 用户下的所有表(拥有) select table_name from user_tables; 用户下有权访问的表(拥有+别人授权给你可以访问的) select table_name from all_tables;
Oracle系统中的所有用户?? select * from dba_users;
数据库设计??? <1> 表结构 需求分析: 名词 -〉 表
规范化设计 1NF -- 表都要有主关键字 2NF -- 除主键外的所有字段 都依赖于主键 3NF -- 违反2NF的设计如何解决?? emp empno(pk),deptno,[dname] dept deptno,dname
反规范化 -> 保证查询效率的 select ename,dname from emp,dept where emp.deptno = dept.deptno
把dname的字段 放在emp表中 select ename,dname from emp;
<2> 实现的功能： 视图 索引 存储过程 触发器
=====================================day5=====================================
ORACLE下的编程 为什么还需要在数据库中编程? <1>一条SQL语句完成不了功能 <2>可能完成 但SQL语句太复杂 效率低下
引入编程 SQL语句 -- 和数据库交互 ORACLE内部编程语言 PL/SQL
PL/SQL语言 (procedure language) SQL - 结构化查询语言 - (DDL/DML/TCL/DCL) - 4GL PL - 过程化的语言 - (if结构 循环结构等) - 3GL
问题:
程序的结构？？？ 定义变量？？？ 变量怎么赋值？？？ 数据如何显示？？？？ 逻辑控制语句？？？ 如何与数据库交互？？？
<1>PL/SQL程序的结构 PL/SQL块(block) declare --定义变量,先定义 后使用 强类型语言 --不区分大小写 begin --执行语句(SQL语句/逻辑结构if 循环) --可以写的SQL语句是DML 和TCL --DDL 和DCL是不能直接写在PL/SQL块中 exception --错误处理程序 --异常处理 end;
最简单的PL/SQL块 begin null; --空语句(什么事都不干 占位置的) end; <2> 定义变量定义变量？？？ 变量的类型 数字 number number(4) number(7,2) 字符 varchar2(20) char(20) 日期 date 布尔值 boolean -- 在数据库中是没有这种类型的 --只能用在编程中 <3> 变量怎么赋值？？？ 数据如何显示？？？？
赋值语句 :=
declare aa number; --30个字母以内 第一个字母不能是数字 bb number(5) default 1000; --cc赋初始值1000 cc number(7,2) := 12.45; --cc赋初始值12.45 c1 number(7,2) := -5.0; --c1赋初始值-5.0
dd varchar2(20) := 'Hello'; --字符 ee char(3);
birthday date; --日期 islogin boolean := false; --布尔值
pi constant number := 3.1415; --常量pi --不能改变它的值
--利用数据库的属性来定义变量 eno emp.empno%type;--%type定义变量的类型和字段一样 erow emp%rowtype; --%rowtype定义变量的类型和表中的行一样 --erow.empno/erow.ename --利用.的方法来访问行中的每个字段 enm emp.ename%type; begin --给变量赋值 --<1> aa := 120.24; dd := 'SMITH'; birthday := '01-4月-05'; --系统时间赋值 select sysdate into birthday from dual; select to_char(sysdate,'yyyymmdd') into dd from dual; --<2>数字类型 bb := &ggggggggggg; --字符类型 enm := '&输入值'; --<3> into子句 --使用列类型变量作为参数 select sal,ename into cc,dd from emp where ename = enm; --使用行类型变量 select * into erow from emp where empno = 7369; --打印变量的值 DBMS_OUTPUT.put_line('aa='||aa); DBMS_OUTPUT.put_line('bb='||bb); DBMS_OUTPUT.put_line('cc='||cc); DBMS_OUTPUT.put_line('dd='||dd); DBMS_OUTPUT.put_line(erow.empno||'-'||erow.ename||'-'||erow.sal); end;
使用DBMS_OUTPUT来输出结果时，必须先 执行set serverout on 命令
PL/SQL中SELECT语句的要求〈1〉在PL/SQL块中的select语句必须有into子句 <2> select语句只能返回一条记录,不能是多条，也不能是0条 <3> 字段个数和变量的个数要一样
<3> 逻辑控制语句？？？--循环语句 3种循环 (a) loop循环 格式:loop exit when <条件> --当条件成立的时候退出循环 end loop; declare i number; begin i := 0; loop exit when i = 10; dbms_output.put_line(i); i := I + 1; end loop; end;
(b) while循环 while <条件> loop --当条件不成立的时候退出循环 end loop;
declare i number; begin i := 6; while (i < 10 and i > 5) loop dbms_output.put_line(i); i := i + 1; end loop; end; (c) for 循环 for <循环变量> in [reverse] <下界>..<上界> loop end loop; declare i number; begin for i in 1..10 loop dbms_output.put_line(i); end loop; end;
declare i number; m number := -5; n number; begin n := 10; for i in m..n loop dbms_output.put_line(i); end loop; end;
declare i number; m number := -5; n number; begin n := 10; for i in reverse m..n loop dbms_output.put_line(i); end loop; end;
if ... else .... 结构 if <条件> then elsif <条件> then elsif <条件> then
elsif <条件> then else end if;
--给职工涨工资,输入一个员工号,判断员工所在的部门,如果部门 10 涨10% 20 涨20% 30 涨30%如果涨后的工资>5000那么就设为5000(涨后的工资不能超过5000) declare eno emp.empno%type; dno emp.deptno%type; v_sal number; begin --<1>输入员工号 eno := &员工号; /*<2>得到部门号 */ select deptno,sal into dno,v_sal from emp where empno = eno; /*<3>判断*/ if dno = 10 then if v_sal * 1.1 > 5000 then update emp set sal = 5000 where empno = eno; else update emp set sal = sal * 1.1 where empno = eno;
end if; elsif dno = 20 then if v_sal * 1.2 > 5000 then update emp set sal = 5000 where empno = eno;
else update emp set sal = sal * 1.2 where empno = eno;
end if;
elsif dno = 30 then if v_sal * 1.3 > 5000 then update emp set sal = 5000 where empno = eno;
else update emp set sal = sal * 1.3 where empno = eno;
end if; else null; --占位 end if; end;
end;
declare eno emp.empno%type; begin eno := &员工号; update emp set sal = least(sal + sal * deptno /100,5000) where empno = eno; commit; end;
<4> declare begin --到底能写哪些sql语句 --直接写的DML TCL --间接写的DDL DCL (execute immediate) end;
declare i number; begin --所有的DDL语句都不能直接写 create table test(a number(2)); --所有的DCL语句都不能直接写 grant select on emp to mike; end;
declare s1 varchar2(100); begin --这两类命令可以利用语句execute immediate间接的写 s1:='create table test1(a number(2))'; execute immediate s1; end;
declare s2 varchar2(100); begin --这两类命令可以利用语句execute immediate间接的写 s2:='drop table test1'; execute immediate s2; end;
<5>异常部分 运行时候的错误是异常 declare begin exception --这部分如何写 end;
declare erow emp%rowtype; begin select * into erow from emp where empno = 9000; exception --预定义的异常 --too_many_rows 关键字(返回多行) --no_data_found 关键字(没有返回行) when no_data_found then dbms_output.put_line('没有返回行'); end;
declare erow emp%rowtype; begin select * into erow from emp;
exception --预定义的异常 --too_many_rows 关键字(返回多行) --no_data_found 关键字(没有返回行) when too_many_rows then dbms_output.put_line('多于1行'); end;
-----简单的异常处理-- 系统全局变量 sqlcode sqlerrm declare erow emp%rowtype; begin select * into erow from emp;
exception when others then dbms_output.put_line('错误的编号'||sqlcode); dbms_output.put_line('错误的信息'||sqlerrm); end;
-- 自定义异常 declare eno number(2); myexp exception; --1。定义异常变量begin eno := &部门号; if eno > 80 then raise myexp; --2。引发异常end if;
insert into dept values ( eno,'&名字','&地址'); commit;exception when myexp then --3。捕获异常 dbms_output.put_line('部门号超过80'); when dup_val_on_index then --违反唯一性约束 dbms_output.put_line('部门号'||eno||'已经存在!!!'); rollback;
end;
--非预定异常 declare erow emp%rowtype; mexp exception; pragma exception_init(mexp,-1422); begin select * into erow from emp;
exception when mexp then dbms_output.put_line('太多行'); end; --raise 引发异常 (throw java中)
--raise_application_error(-20001,'太多行'); 编号范围 是从-20001 到 -29999 declare erow emp%rowtype; begin select * into erow from emp;
exception when others then raise_application_error(-20001,'太多行'); end;
<游标> 内存中的一块区域,存放的是SQL语句影响的结果 cursor 隐式游标 单条的SQL语句 所产生的结果 显式游标
1。 隐式游标 单条sql语句所产生的结果集合 用关键字SQL表示隐式游标 4个属性 %rowcount 影响的记录的行数 整数 %found 影响到了记录 true %notfound 没有影响到记录 true %isopen 是否打开 布尔值 永远是false
多条sql语句 隐式游标SQL永远指的是最后一条sql语句的结果 主要使用在update 和 delete语句上
2。 显式游标 select语句上 使用显式游标 明确能访问结果集 for循环游标 参数游标 解决多行记录的查询问题 fetch游标
显示游标 需要明确定义 (1)FOR循环游标 (常用的一种游标)
--<1>定义游标 --<2>定义游标变量 --<3>使用for循环来使用这个游标
--前向游标 只能往一个方向走 --效率很高
declare --类型定义 cursor cc is select empno,ename,job,sal from emp where job = 'MANAGER'; --定义一个游标变量 ccrec cc%rowtype;
begin --for循环 for ccrec in cc loop dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);
end loop; end; (2) fetch游标 --使用的时候 必须要明确的打开和关闭
declare --类型定义 cursor cc is select empno,ename,job,sal from emp where job = 'MANAGER'; --定义一个游标变量 ccrec cc%rowtype;
begin --打开游标 open cc; --loop循环 loop --提取一行数据到ccrec中 fetch cc into ccrec; --判断是否提取到值，没取到值就退出 --取到值cc%notfound 是false --取不到值cc%notfound 是true exit when cc%notfound; dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);
end loop; --关闭 close cc; end; 游标的属性4种 %notfound fetch是否提到数据 没有true 提到false %found fetch是否提到数据 有true 没提到false %rowcount 已经取出的记录的条数 %isopen 布尔值 游标是否打开
declare --类型定义 cursor cc is select empno,ename,job,sal from emp where job = 'MANAGER'; --定义一个游标变量 ccrec cc%rowtype;
begin --打开游标 open cc; --loop循环 loop --提取一行数据到ccrec中 fetch cc into ccrec; --判断是否提取到值，没取到值就退出 --取到值cc%notfound 是false --取不到值cc%notfound 是true exit when (cc%notfound or cc%rowcount =3);
dbms_output.put_line(cc%rowcount||'-'||ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);
end loop; --关闭 close cc; end;
<例子> declare cursor cc is select dept.dname, emp.ename,emp.sal from dept,emp where dept.deptno = emp.deptno; ccrec cc%rowtype; begin for ccrec in cc loop dbms_output.put_line(ccrec.dname||'-'||ccrec.ename||'-'||ccrec.sal); end loop;
end; (3)参数游标 按部门编号的顺序输出部门经理的名字 declare --部门 cursor c1 is select deptno from dept; --参数游标c2,定义参数的时候 --只能指定类型,不能指定长度 --参数只能出现在select语句=号的右侧 cursor c2(no number,pjob varchar2) is select emp.* from emp where deptno = no and job=pjob;
/* no = 10 pjob = 'MANAGER' select * from emp where deptno = 10 and job = 'MANAGER'; */ c1rec c1%rowtype; c2rec c2%rowtype; --定义变量的时候要指定长度 v_job varchar2(20); begin --部门 for c1rec in c1 loop --参数在游标中使用 for c2rec in c2(c1rec.deptno,'MANAGER') loop dbms_output.put_line(c1rec.deptno||'-'||c2rec.ename);
end loop; end loop; end; <综合例子> 求购买的商品包括了顾客"Dennis"所购买商品的顾客（姓名）；************** 思路: Dennis (A,B)
别的顾客 (A,B,C) (A,C) (B,C) C declare --Dennis所购买的商品 cursor cdennis is select productid from purcase where customerid=( select customerid from customer where name = 'Dennis'); --除Dennis以外的每个顾客 cursor ccust is select customerid from customer where name <> 'Dennis'; --每个顾客购买的商品 cursor cprod(id varchar2) is select productid from purcase where customerid = id;
j number ; i number; c1rec cdennis%rowtype; c2rec ccust%rowtype; c3rec cprod%rowtype; cname varchar2(10); begin --顾客循环 for c2rec in ccust loop i:=0; j:=0; for c1rec in cdennis loop i := i + 1; --每个顾客买的东西 for c3rec in cprod(c2rec.customerid) loop
if (c3rec.productid = c1rec.productid) then j := j + 1; end if;
end loop; end loop; if (i=j) then select name into cname from customer where customerid = c2rec.customerid; DBMS_output.put_line(cname); end if;
end loop;
end; (4)引用游标/动态游标 select语句是动态的 declare --定义一个类型(ref cursor)弱类型 type cur is ref cursor; --强类型(返回的结果集有要求) type cur1 is ref cursor return emp%rowtype; --定义一个ref cursor类型的变量 cura cur; -- c1rec emp%rowtype; c2rec dept%rowtype; begin DBMS_output.put_line('输出员工') ; open cura for select * from emp; loop fetch cura into c1rec; exit when cura%notfound; DBMS_output.put_line(c1rec.ename) ; end loop ; DBMS_output.put_line('输出部门') ; open cura for select * from dept; loop fetch cura into c2rec; exit when cura%notfound; DBMS_output.put_line(c2rec.dname) ;
end loop; close cura; end;
=====================================day6=====================================
使用PL/SQL块编程实现，注意必要的异常处理
set serverout on
1.输入一个员工号，输出该员工的姓名、薪金和大概的服务年限（按年月日显示）declare no emp.empno%type; v_enm emp.ename%type; v_sal emp.sal%type; v_nx varchar2(30); begin no := &员工号;
select ename,sal,to_char(to_date('00010101','yyyymmdd')+ (sysdate-hiredate) - 366 -31,'yyyy"年"mm"月"dd"日"') into v_enm,v_sal,v_nx from emp where empno = no;
dbms_output.put_line('员工姓名:'||v_enm||chr(10)||'工资:'||v_sal||chr(10)||'工作年限:'||v_nx); exception when others then dbms_output.put_line(sqlerrm);end;
2.接收一个员工号，输出该员工所在部门的名称declare no number; dno emp.deptno%type; dnm dept.dname%type;begin no := &员工号; select deptno into dno from emp where empno = no; select dname into dnm from dept where deptno = dno; dbms_output.put_line('部门名称是:'||dnm);exception when others then dbms_output.put_line(sqlerrm);end;
3.接收一个员工号，如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；如果该员工职位是CLERK，并且在NEW YORK工作那么就给他薪金扣除5％;其他情况不作处理。
declare no emp.empno%type; dno dept.deptno%type; v_job emp.job%type; dnm dept.dname%type;begin no := &员工号; select deptno,job into dno,v_job from emp where empno = no; select dname into dnm from dept where deptno = dno;
if v_job = 'MANAGER' and dnm = 'DALLAS' then update emp set sal = sal * 1.15 where empno = no; elsif v_job = 'CLERK' and dnm = 'NEW YORK' then update emp set sal = sal * 0.95 where empno = no; else null; end if;exception when others then dbms_output.put_line(sqlerrm);
end;
4.接收一个员工号，输出这个员工所在部门的平均工资 declare no emp.empno%type; v_sal number; begin no := &员工号;
select avg(sal) into v_sal from emp where deptno = (select deptno from emp where empno = no); dbms_output.put_line('平均工资:'||round(v_sal,2)); exception when others then dbms_output.put_line(sqlerrm); end;
5.以交互的方式给部门表插入一条记录，如果出现主键冲突的异常，请显示“部门号已被占用”的字样。 declare erow dept%rowtype; begin erow.deptno := '&部门号'; erow.dname := '&部门名'; erow.loc := '&地址'; insert into dept values (erow.deptno,erow.dname,erow.loc); exception when others then dbms_output.put_line(sqlerrm); end;
=====================================day7=====================================
1、建立一个存储过程用来接收一个员工号，返回他的工资和他所在部门的平均工资并作为传出参数传出。create or replace procedure p1(id number,psal out number,pavgsal out number)asbegin select sal into psal from emp where empno =id; select avg(sal) into pavgsal from emp where deptno = (select deptno from emp where empno = id);exception when others then dbms_output.put_line(sqlerrm);end;
declare s number; ps number(7,2); id number;begin id := &员工号; p1(id,s,ps); dbms_output.put_line('员工'||id||chr(10)||'工资'||s||chr(10)||'平均工资'||ps);
end;
2、建立一个存储过程用来接收一个部门号，找出其中的两位最老的员工的员工号，并打印。create or replace procedure p2(dno number)as cursor c1 is select * from emp where deptno = dno order by hiredate; c1rec c1%rowtype;
begin for c1rec in c1 loop dbms_output.put_line('员工号:'||c1rec.empno||chr(9)||'工作时间:'||to_char(c1rec.hiredate,'yyyy/mm/dd')); if (c1%rowcount = 2) then exit; end if; end loop;exception when others then dbms_output.put_line(sqlerrm);end;
--调用execute p2(10);
3、编写一个过程用来传入一个员工号，在emp表中删除一个员工，当该员工是该部门的最后一个员工时就在dept表中删除该员工所在的部门。
create or replace procedure p3(eno emp.empno%type) as dno dept.deptno%type; n_count number; begin select deptno into dno from emp where empno = eno; delete from emp where empno = eno; select count(*) into n_count from emp where deptno = dno; if n_count = 0 then delete from dept where deptno = dno; end if; exception when others then dbms_output.put_line(sqlerrm); end;
=====================================day8=====================================
触发器
1.编写一个触发器实现如下功能： 对修改职工薪金的操作进行合法性检查： a) 修改后的薪金要大于修改前的薪金 b) 工资增量不能超过原工资的10% c) 目前没有单位的职工不能涨工资--update emp set sal = 1000 where empno = 7369;
create or replace trigger tr1 after update of sal on empfor each rowbegin if :new.sal <= :old.sal then raise_application_error(-20001,'修改后的薪金要大于修改前的薪金'); elsif :new.sal > :old.sal * 1.1 then raise_application_error(-20002,'工资增量不能超过原工资的10%'); elsif :old.deptno is null then raise_application_error(-20003,'没有单位的职工不能涨工资'); end if;end;
2. 在emp表上编写一个触发器，实现如下功能： 当插入或删除的职工记录属于10号部门时，记录下操作时间,语句的种类(插入/删除),和涉及的员工号 --建立日志表 create table logtable( serial number primary key, --序号 dmltime date, --时间 dmltype varchar2(20), --DML种类 empno number --员工号); --建立序列(用于产生logtable的序号) create sequence seq1;
--建立触发器 create or replace trigger tr2 after insert or delete on emp for each row when (new.deptno = 10 or old.deptno = 10) --插入或删除的职工记录属于10号部门时触发 --when的条件成立的时候触发 --对新值和旧值引用的时候不要用: begin if inserting then --插入 insert into logtable values (seq1.nextval, sysdate, 'insert语句', :new.empno); elsif deleting then --删除 insert into logtable values (seq1.nextval, sysdate, 'delete语句', :old.empno); end if;--updating 修改 end;
