1、数据准备
CREATE TABLE student
(
"NUMBER" varchar2(6) NOT NULL,
name varchar2(8) NULL,
sex number(1),
birthday timestamp NULL,
height number(3,2) NULL
);
insert into student ("NUMBER",name,sex,birthday,height) values ( '000001','黎明',1,TO_DATE('15-4月 -1980','dd-Mon-
yyyy'),1.78);
insert into student ("NUMBER",name,sex,birthday,height) values ( '000002','赵怡春',0,TO_DATE('17-12月 -1982','dd-Mon-
yyyy'),1.77);
insert into student ("NUMBER",name,sex,birthday,height) values ( '000003','张富平',1,TO_DATE('1-2月 -1981','dd-Mon-
yyyy'),1.80);
insert into student ("NUMBER",name,sex,birthday,height) values ( '000004','白丽',0,TO_DATE('20-11月 -1986','dd-Mon-
yyyy'),1.73);
insert into student ("NUMBER",name,sex,birthday,height) values ( '000005','牛玉德',1,TO_DATE('1-5月 -1983','dd-Mon-
yyyy'),1.74);
insert into student ("NUMBER",name,sex,birthday,height) values ( '000006','姚华',0,TO_DATE('12-5月 -1983','dd-Mon-
yyyy'),1.77);
CREATE TABLE grade
(
"NUMBER" varchar2(6) NULL,
course varchar2(50) NULL,
grade number(3) NULL
);
insert into grade ("NUMBER",course,grade) values ('000001','A03',56);
insert into grade ("NUMBER",course,grade) values ('000002','A02',90);
insert into grade ("NUMBER",course,grade) values ('000004','A02',77);
insert into grade ("NUMBER",course,grade) values ('000005','B01',91);
insert into grade ("NUMBER",course,grade) values ('000004','A03',76);
insert into grade ("NUMBER",course,grade) values ('000003','A03',67);
insert into grade ("NUMBER",course,grade) values ('000001','B01',78);
insert into grade ("NUMBER",course,grade) values ('000002','A03',74);
insert into grade ("NUMBER",course,grade) values ('000002','B01',86);
insert into grade ("NUMBER",course,grade) values ('000003','A02',80);
insert into grade ("NUMBER",course,grade) values ('000004','B01',90);
2、查询
并
select * from student where sex=1 union select * from student where height>1.75;
等价方法为：
select * from student where sex=1 or height>1.75;
交
select * from student where sex=1 intersect select * from student where height>1.75;
等价方法为：
select * from student where sex=1 and height>1.75;
差
select * from student where sex=1 minus select * from student where height>1.75;
等价方法为：
select * from student where sex=1 and height<=1.75;
笛卡尔连接
select * from student cross join grade;
自然连接
select * from student natural join grade;
使用一般连接实现的自然连接
select student."NUMBER",name,sex,birthday,height,course,grade from student inner join grade on student."NUMBER"=grade."NUMBER";