drop table stuInfo;
drop table course;
drop table score;
drop sequence seq_stuid;
drop sequence seq_cid;
drop sequence seq_scid;
create table stuinfo(
--学生
stuid int primary key,
sname varchar2(50) unique,
age int not null
constraint CK_sage check(age>=18 and age<=30),
addr varchar2(50),
sex char(2) default 'M'
constraint CK_sexs check(sex in('M','F'))
);
create sequence seq_stuid start with 10001 increment by 1;
create table course(
--科目
cid int primary key,
cname varchar2(50)
);
create sequence seq_cid start with 10001 increment by 1;
create table score(
--成绩
scid int primary key,
stuid int
constraint FK_stuid references stuinfo(stuid),
courseid int
constraint FK_courseid references course(cid),
score int
);
create sequence seq_scid start with 10001 increment by 1;
insert into course values(seq_cid.nextval,'j2se精讲');
insert into course values(seq_cid.nextval,'Oracle');
insert into course values(seq_cid.nextval,'html网页设计');
insert into course values(seq_cid.nextval,'Java基础');
insert into stuinfo values(seq_stuid.nextval,'张果老',22,null,'M');
insert into stuinfo values(seq_stuid.nextval,'李豹',22,null,'M');
insert into stuinfo values(seq_stuid.nextval,'老胡',22,'北京','M');
insert into stuinfo values(seq_stuid.nextval,'*',24,'湖南','M');
insert into stuinfo values(seq_stuid.nextval,'张无忌',26,'衡阳','M');
insert into stuinfo values(seq_stuid.nextval,'二师兄',28,'长沙','M');
insert into stuinfo values(seq_stuid.nextval,'凤姐',24,'上海','F');
insert into stuinfo values(seq_stuid.nextval,'元芳',28,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'离歌',23,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'八戒',24,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'赵柳',22,'北京','F');
commit;
insert into score values(seq_scid.nextval,10005,10001,95);
insert into score values(seq_scid.nextval,10005,10002,58);
insert into score values(seq_scid.nextval,10005,10003,56);
insert into score values(seq_scid.nextval,10005,10004,81);
insert into score values(seq_scid.nextval,10005,10002,80);
insert into score values(seq_scid.nextval,10001,10001,78);
insert into score values(seq_scid.nextval,10001,10003,81);
insert into score values(seq_scid.nextval,10001,10004,92);
insert into score values(seq_scid.nextval,10002,10003,30);
insert into score values(seq_scid.nextval,10002,10003,78);
insert into score values(seq_scid.nextval,10003,10003,66);
insert into score values(seq_scid.nextval,10004,10003,66);
insert into score values(seq_scid.nextval,10006,10003,78);
insert into score values(seq_scid.nextval,10007,10003,76);
insert into score values(seq_scid.nextval,10008,10002,81);
insert into score values(seq_scid.nextval,10009,10002,90);
insert into score values(seq_scid.nextval,10010,10003,42);
insert into score values(seq_scid.nextval,10002,10001,80);
insert into score values(seq_scid.nextval,10004,10002,78);
insert into score values(seq_scid.nextval,10008,10003,69);
insert into score values(seq_scid.nextval,10008,10001,69);
select * from stuinfo;
select * from course;
select * from score;
commit;
--查出所有姓'张'的学员信息
select * from stuinfo where sname like '张%';
--查出所有addr字段为'NULL'值的学员的信息
select * from stuinfo where addr is null;
--查出成绩在60到70分之间的学员的stuid号
select stuid from score where score between 60 and 70;
--查出地址是北京和上海的学生的信息
select * from stuinfo where addr in ('北京' , '上海') ;
--多表的连接查询
--种类1.内连接 (inner join ) :仅当至少有一个同属于两表的行符合连接条件时，内连接才返回行，内连接消除了与另一张表中任何不匹配的行
--2.外连接: 外连接会返回from子句中提到的至少一个表中的行，只要这些行符合where条件或having条件
--左外连接(left join )： 返回左表中所有的行，右表中没有出现的字段用null代替
--右外连接(right join )： 返回右表中所有的行，左表中没有出现的字段用null代替
--完整连接 (full join)：两表中的数据都会返回，没有的用null代替
--3.交叉连接(cross join )： 笛卡尔集
--内连接语法： select 字段 from 表名1 inner join 表名2 on 连接条件;
--左外连接： select 字段 from 表名1 left join 表名2 on 连接条件
--显示学生姓名，成绩 stuinfo s score sc
内连接： select s.sname,sc.score from stuinfo s inner join score sc on s.stuid =sc.stuid ;
左外连接: select s.sname,sc.score from score sc left join stuinfo s on s.stuid =sc.stuid where sc.score is not null ;
右外连接： select s.sname ,sc.score from stuinfo s right join score sc on s.stuid = sc.stuid where sc.score is not null ;
完整连接： select s.sname ,sc.score from stuinfo s full join score sc on s.stuid =sc.stuid;
--
select * from stuinfo,score where stuinfo.stuid = score.stuid;
--显示学生姓名，成绩 及课程名
select s.sname,sc.score ,c.cname from stuinfo s inner join score sc on s.stuid =sc.stuid inner join course c on sc.courseid =c.cid ;
--查出学员在'html网页设计'这门课程中的总成绩和平均成绩,最高分数，最低分数
select sum(score),avg(score),max(score),min(score) from score inner join course on score.courseid =course.cid and course.cname='html网页设计';
--查出学员在'html网页设计'这门课程中不及格的学生人数
distinct() --去重
select count(distinct(stuid)) from score inner join course on score.courseid =course.cid and score.score<60 and course.cname='html网页设计';
--统计出参加“html网页设计”考试的人数
select count(distinct(stuid)) 参加考试人数 from score inner join course on score.courseid =course.cid and course.cname='html网页设计';
select count(distinct(stuid)) 参加考试人数 from score,course where score.courseid =course.cid and course.cname='html网页设计';
--查出没有及格的学生姓名和课程名以及分数
select stuinfo.sname,course.cname,score.score from stuinfo,score,course
where stuinfo.stuid =score.stuid and course.cid =score.courseid and score.score<60;
select stuinfo.sname,score.score,course.cname from stuinfo inner join score
on stuinfo.stuid =score.stuid inner join course on score.courseid = course.cid
and score.score<60;