一些练习题目
1.只买过一种水果的人 sg
select name from sg
group by name
having count(distinct shuiguo)=1
select distinct t1.name from sg t1
left join sg t2
on t1.name=t2.name
and t1.shuiguo!=t2.shuiguo
where t2.name is null
2.哪些女生没有考过80分以上的科目 score
找出女生 这些女生所有科目都没有考过80分
重点是女生不是科目…
学生表与成绩表进行连接 找出性别为女并且成绩在80分之下的 作为表1
然后对成绩表找出成绩为80分以上的女生 作为表2
表2左关联表1 然后找出为null的记录就是符合条件的记录
这种方式只会筛选出已经考过试的
select distinct t1.s_id,s_name from student t1
left join score t2
on t1.s_id = t2.s_id
where s_sex = '女' and s_score <80
and t1.s_id
not in
(select s_id from score where s_score > 80)
或者找出性别为女 成绩是80分以上的
然后用所有女生去左关联
这种方式会筛选出有些科目没有考过试的学生
select distinct s_name from student
where
s_sex = '女'
and
s_id not in
(
(select distinct t1.s_id from student t1
left join score t2
on t1.s_id = t2.s_id
where s_sex = '女'
and s_score > 80)
)
数据库对象：触发器 trigger
触发器的一些详细介绍
触发器：当满足制定的触发条件后，自动执行处理语句
触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。
定义触发器
create or replace trigger tr_test--触发器名称
before insert or update or delete on test3 --触发事件insert update delete test 是表名
for each row--行级触发器
begin
dbms_output.put_line('正在对test3表进行修改');
end;
-- 只有成功运行了才会打印出信息出来，如果没有的话就不会触发到触发器
update test3 set name = '王五' where name = '张三'
delete from test3 where name = '王五'
删除触发器
drop trigger tr_test（触发器的名字）;
带条件的触发器
create or replace trigger tr_test2
before insert or update or delete on test3
for each row
when (new.id >10)
begin
dbms_output.put_line('正在修改或者插入大于10的id');
end;
insert into test3 values (11,'张十一')
insert into test3 values (8,'张十一')
insert into test3 values (6,'张')
update test3 set id=12 where id = 2
这里的触发器并不会改变插入 删除这些操作，无论如何都会执行，但是不满足条件不会执行触发器里面的语句，比如插入id为6的张，可以插入，但是不会输出语句，插入id为11的张十一就会触发到trigger，然后输出’正在修改或者插入大于10的id’。
在允许的时间内才能修改数据的触发器
create or replace trigger tr_test3
before insert or update or delete on test3
for each row
begin
if (to_char(sysdate,'d')) in(4,5,6,7) then
raise_application_error(-20001,'当前时间不能修改数据');
end if;
end;
insert into test3 values(1,'张一')
补充
select to_char(sysdate,'d') from dual ---一周中的第几天
select to_char(sysdate,'dd') from dual --一个月中的第几天
select to_char(sysdate,'ddd') from dual --一年中的第几天
创建特定的用户才能修改数据的触发器
create or replace trigger tr_test4
before insert or update or delete on test3
for each row
begin
if
ora_login_user!='SYSTEM' then
raise_application_error(-20001,'只有system才能修改当前表');
end if;
end;
insert into test3 values(88,'周周')
因为目前是管理员账户，所以执行的话不会报错
如果把里面的条件改掉，再去执行，会有如下结果
drop trigger tr_test4
create or replace trigger tr_test4
before insert or update or delete on test3
for each row
begin
if
--这里条件改变
ora_login_user ='SYSTEM' then
raise_application_error(-20001,'只有system才能修改当前表');
end if;
end;
insert into test3 values(89,'喵酱')
insert into test3 values(89,'喵酱')
> ORA-20001: 只有system才能修改当前表
ORA-06512: at "HR.TR_TEST4", line 4
ORA-04088: error during execution of trigger 'HR.TR_TEST4'
> 时间: 0.002s
通过触发器去自增序列
首先创建一个序列
create sequence test_seq
increment by 1
start with 10
maxvalue 99999
nocache
nocycle
select test_seq.currval from dual --查看当前序列
select test_seq.nextval from dual --查看序列的下一个值
将序列的值赋值给id
create or replace trigger tr_seq
before insert on test3
for each row
begin
select test_seq.nextval into :new.id from dual;
end;
只插入名字会自动从序列的值获取到id
并且只会根据序列值去更新id
即便你插入的时候规定了id也不会起作用
insert into test3(name) values ('万江')
insert into test3(name) values ('软爪')
通过触发器往日志表插入数据
--复制一个表结构，记得占位的那个空值那里要留大一点空，否则后面插入的时候会出现错误
create table log as select t1.*,sysdate as dates,' ' as type1 from test3 t1 where 1=2
创建一个日志表，如果是插入的话，就把这条记录放到日志表里面
如果是更新的话，首先改变类型，作为’NU’（new update），然后将这条新的记录插入到日志表里面；
然后再去改变类型，作为’OU’（old update），然后将老的记录插入到日志表里面；
其他的更改类型将其类型记录为’D’（delete），然后将删除的记录也插入到日志表里面
create or replace trigger tr_log
before insert or update or delete on test3
for each row
declare
type1 VARCHAR2(5);
begin
if inserting then
type1 :='I';
insert into log values (:new.id,:new.name,sysdate,type1);
elsif updating then
type1 :='NU';
insert into log values (:new.id,:new.name,sysdate,type1);
type1 :='OU';
insert into log values(:old.id,:old.name,sysdate,type1);
else type1 :='D';
insert into log values(:old.id,:old.name,sysdate,type1);
end if;
end;
--test
insert into test3 values(15,'张八')
delete from test3 where id>10
select * from log
通过数据字典查找表的触发器
select * from all_triggers
where table_owner = 'HR' --指定用户
AND TABLE_NAME = 'TEST3' ---指定表名
可以选择一些字段进行查找
记录一道题目
考过试的学生中，谁没有考过王五老师教的课程
可能用到的表，学生表可以不用
思路：
首先找出考过试的学生 作为表1
找出考过王五教过的课的这些人 作为表2
用表1左关联表2 找出表2 中为null的人，那么就是考过试但是没有考过王五教过的课程的人
select distinct t5.s_id from
(select t1.s_id from score t1
inner join student t2
on t1.s_id = t2.s_id) t5
left join
(select s_id from score
where c_id = (
select c_id from teacher t3
inner join course t4
on t3.t_id =t4.t_id
where t3.t_name = '王五')) t6
on t5.s_id = t6.s_id
where t6.s_id is null
-- 不用关联那么多表，直接使用score表就行了，
-- 因为score表天然可以筛选出来已经考过试的人
--如果需要名字的话就再去关联一下学生表get学生名字
select distinct t5.s_id from
(select t1.s_id from score t1
) t5
left join
(select s_id from score
where c_id = (
select c_id from teacher t3
inner join course t4
on t3.t_id =t4.t_id
where t3.t_name = '王五')) t6
on t5.s_id = t6.s_id
where t6.s_id is null
考过试的学生中，谁没有考过王五老师教的课程 student score course teacher
select distinct t5.s_id,t6.s_name from student t1
inner join score t2
on t1.s_id=t2.s_id
inner join course t3
on t3.c_id=t2.c_id
inner join teacher t4
on t3.t_id=t4.t_id
and t4.t_name='王五'
right join score t5
on t1.s_id=t5.s_id
inner join student t6
on t5.s_id=t6.s_id
where t1.s_id is null
1001
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
