《李兴华Oracle数据库课堂笔记》
例：查询出所有不领取佣金的雇员。
SELECT*FROM emp WHERE comm IS NULL;
例：查询出所有领取佣金的雇员。
SELECT*FROM emp WHERE comm IS NOT NULL;
SELECT*FROM emp WHERE NOT comm IS NULL;
范例：查询出雇员编号为7369、7566、7839、8899（不存在）的雇员信息。
SELECT*FROM emp WHERE empno=7369 OR empno= 7839 OR empno=7566 OR empno=8899;
SELECT*FROM emp WHERE empno IN(7369,7566,7839,8899);
2021-12-13 20:15:51
3.63MB
1
Connect scott/****@myoracle AS SYSDBA
create USER goupiaoXT
IDENTIFIED BY passward
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
grant connect to goupiaoXT;
grant resource to goupiaoXT;
connect goupiaoXT/passward;
CREATE TABLE YINGPIAN (
YPID NUMBER NOT NULL Primary Key,
YPNAME VARCHAR(50)
);
CREATE TABLE YINGTING (
YTID NUMBER NOT NULL Primary Key,
YTNAME VARCHAR(8),
HANG NUMBER,
LIE NUMBER
);
CREATE TABLE HUIYUAN (
HYID NUMBER NOT NULL Primary Key,
HYNAME VARCHAR(8),
JINE VARCHAR(15),
PASSWORD VARCHAR(20)
);
CREATE TABLE PIAO (
PID NUMBER NOT NULL Primary Key,
LEIXING VARCHAR(8),
PRICE VARCHAR(4)
);
CREATE TABLE FANGYING (
FYID NUMBER NOT NULL,
YPIDO NUMBER NOT NULL REFERENCES YINGPIAN(YPID),
YTIDO NUMBER NOT NULL REFERENCES YINGTING(YTID),
FYDATE DATE NOT NULL,
FYTIME DATE NOT NULL,
Primary Key(FYID)
);
CREATE TABLE ZWYD (
ZWYDID NUMBER NOT NULL PRIMARY KEY,
FYIDO NUMBER NOT NULL REFERENCES FANGYING (FYID),
ZWZHT VARCHAR(20) ,
HYIDO NUMBER REFERENCES HUIYUAN (HYID),
PIDO NUMBER REFERENCES PIAO (PID),
HANG NUMBER,
LIE NUMBER,
ZT NUMBER
);
create sequence seqZWYDId
increment by 1
start with 1
cache 10
;
create or replace trigger ZWYDTRI
after insert
on FANGYING
for each row
declare
TMPHANG NUMBER;
TMPLIE NUMBER;
i NUMBER :=1;
j NUMBER :=1;
begin
select HANG,LIE into TMPHANG,TMPLIE from YINGTING where YTID =:NEW.YTIDO;
for i in 1..TMPHANG
loop
for j in 1..TMPLIE
loop
insert into ZWYD(ZWYDID,FYIDO,HANG,LIE,ZT) values(seqZWYDId.nextval,:new.FYID,i,j,0);
end loop;
end loop;
end ZWYDTRI;
/
insert into HUIYUAN values(2004322888,'123456','500');
insert into HUIYUAN values(2004322889,'123456','400');
insert into HUIYUAN values(2004322898,'123456','300');
insert into YINGTING values(100,'大厅',10,10);
insert into YINGTING values(101,'一号厅',4,5);
insert into YINGTING values(102,'二号厅',6,8);
insert into YINGPIAN values(10001,'《梅兰芳》');
insert into YINGPIAN values(10002,'《拯救飓风》');
insert into YINGPIAN values(10003,'《画皮》');
insert into HUIYUAN values(1,'he','2000','123456');
insert into HUIYUAN values(2,'lei','50','12345');
insert into HUIYUAN values(3,'helei','405','1234567');
insert into PIAO values(0,'普通票','65');
insert into PIAO values(1,'团体票','35');
insert into PIAO values(2,'学生票','30');
insert into FANGYING values(100001,10001,100,to_date('08-10-10','yy-mm-dd'),to_date('08-10-10 8:20','yy-mm-dd hh24:mi'));
insert into FANGYING values(100002,10001,101,to_date('08-10-10','yy-mm-dd'),to_date('08-10-12 9:20','yy-mm-dd hh24:mi'));
insert into FANGYING values(100003,10001,102,to_date('08-10-12','yy-mm-dd'),to_date('08-10-13 12:20','yy-mm-dd hh24:mi'));
insert into FANGYING values(100004,10001,101,to_date('08-10-12','yy-mm-dd'),to_date('08-10-12 6:20','yy-mm-dd hh24:mi'));
insert into FANGYING values(100005,10002,102,to_date('08-10-13','yy-mm-dd'),to_date('08-10-13 20:20','yy-mm-dd hh24:mi'));
insert into FANGYING values(100006,10003,101,to_date('08-10-12','yy-mm-dd'),to_date('08-10-12 9:20','yy-mm-dd hh24:mi'));
2019-12-21 19:51:39
8.99MB
1