MySQL练习

-- 创建学生表
create table student (sno varchar(10) primary key comment '学号',sName varchar(20) comment '姓名',sAge numeric(2) comment '年龄',sSex varchar(5) comment '性别');
-- 插入学生数据
insert into student (sno, sName, sAge, sSex) VALUES ('s001','张三',23,'男');
insert into student (sno, sName, sAge, sSex) VALUES ('s002','李四',23,'男');
insert into student (sno, sName, sAge, sSex) VALUES ('s003','吴鹏',25,'男');
insert into student (sno, sName, sAge, sSex) VALUES ('s004','琴沁',20,'女');
insert into student (sno, sName, sAge, sSex) VALUES ('s005','王丽',20,'女');
insert into student (sno, sName, sAge, sSex) VALUES ('s006','李波',21,'男');
insert into student (sno, sName, sAge, sSex) VALUES ('s007','刘玉',21,'男');
insert into student (sno, sName, sAge, sSex) VALUES ('s008','萧蓉',21,'女');
insert into student (sno, sName, sAge, sSex) VALUES ('s009','陈萧晓',23,'女');
insert into student (sno, sName, sAge, sSex) VALUES ('s010','陈美',22,'女');

-- 创建教师表
create table teacher(tNO varchar(10) primary key comment '教师编号',tName varchar(20) comment '教师姓名');
-- 插入教师数据
insert into teacher(tNO, tName) VALUES ('t001','刘阳'),('t002','谌燕'),('t003','胡明星');

-- 创建课程表
create table course(cNo varchar(10) comment '课程号',cName varchar(20) comment '课程名',tNo varchar(20) comment '教师编号');
-- 插入数据
insert into course(cNo, cName, tNo) VALUES ('c001','J2SE','t002'),('c002','Java Web','t002'),('c003','SSH','t001'),('c004','Oracle','t001'),('c005','SQL SERVER 2005',''),('c006','C#','t003'),('c007','JavaScript','t002'),('c008','DIV+CSS','t001'),('c009','PHP','t003'),('c010','EJB3.0','t002');

-- 创建成绩表
create table sc (sNo varchar(10) comment '学号',cNo varchar(10) comment '课程号',score numeric(4,2) comment '学生成绩');
-- 插入数据
insert into sc (sNo, cNo, score) VALUES ('s001','c001',78.9),('s002','c001',80.9),('s003','c001',81.9),('s004','c001',60.9),('s001','c002',82.9),('s002','c002',72.9),('s003','c002',81.9),('s001','c003',59);

-- 1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
select sno from sc s1 where s1.cno='c001' and s1.score > (select s2.score from sc s2 where s2.cno='c002' and s1.sno=s2.sno );
-- 2、查询平均成绩大于60 分的同学的学号和平均成绩
select sno as'学号',avg(score) as'平均成绩' from sc group by sno having avg(score)>60;
-- 3、查询所有同学的学号、姓名、选课数、总成绩；
select st.sno as'学号',st.sName as'姓名',count(sc.sNo) as '选课数',sum(score)as'总成绩' from student st left join sc on st.sno=sc.sNo group by st.sno;
-- 4、查询姓“刘”的老师的个数；
select count(tName) from teacher where tName like '刘%';
-- 5、查询没学过“谌燕”老师课的同学的学号、姓名；
select st.sno as'学号',st.sName as'姓名' from student st where st.sno not in (select distinct sno from sc s join course c on s.cno = c.cno join teacher t on c.tno = t.tno where tname = '谌燕');
-- 6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
select s.sno as'学号',s.sName as'姓名' from sc a,sc b inner join student s on b.sNo = s.sno where a.sNo=b.sNo and a.cNo='c001'and b.cNo='c002';
-- 7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
select a.sno as '学号', a.sname as'姓名' from student a where a.sno in (select distinct s.sno from sc s,(select c.* from course c, (select tno from teacher t where tname = '谌燕') t where c.tno = t.tno) b where s.cno = b.cno);
-- 8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
select st.sno as'学号',st.sName as'姓名' from student st inner join sc s on st.sno = s.sNo inner join sc s2 on st.sno = s2.sNo where s.cNo='c001' && s2.cNo='c002' and s2.score>s.score;
-- 9、查询所有课程成绩小于60 分的同学的学号、姓名；
select st.sno as'学号',sName as'姓名' from student st left join sc s on st.sno = s.sNo where s.score<60;
-- 10、删除学习“谌燕”老师课的SC 表记录；
delete from sc where sc.cno in(select cno from course c left join teacher t on c.tno=t.tno where t.tname='谌燕');
-- 11、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cNo as'课程ID',max(score) as'最高分',min(score) as'最低分'from sc group by cNo;
-- 12、查询不同老师所教不同课程平均分从高到低显示
select s.cNo as '学号', t.tName as'姓名',avg(s.score)as '平均分'from course co inner join sc s on co.cNo = s.cNo inner join teacher t on co.tNo = t.tNO group by t.tName,s.cNo order by avg(s.score) desc ;
-- 13、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select *from (select sno,cno,score, row_number() over(partition by cno order by score desc) rn from sc) as s where rn <=3;
-- 14、查询每门课程被选修的学生数
select c.cName as'课程', count(s.cNo) as '学生数'from sc s inner join course c on s.cNo = c.cNo group by c.cName ;
-- 15、查询出只选修了一门课程的全部学生的学号和姓名
select st.sno as'学号',st.sName as'姓名' from student st inner join sc s on st.sno = s.sNo group by st.sno, st.sName having count(cNo)=1;
-- 16、查询男生、女生人数
select count(sSex='男')as'男生人数' ,count(sSex='女')as'女生人数'from student order by sSex;
-- 17、查询姓“张”的学生名单
select * from student where sName like '张%';
-- 18、查询同名同性学生名单，并统计同名人数
select s.sName as'姓名',count(s.sName) as'人数'from student s group by s.sName;
-- 19、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select avg(score) as'平均成绩' from sc group by sNo order by avg(score) , avg(score) desc ;
-- 20、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
select st.sno as'学号',st.sName as'姓名',AVG(score) as'平均成绩' from student st left join sc s on s.sno=st.sno group by st.sno having AVG(score)>85;
-- 21、查询所有学生的选课情况；、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数
select st.sName as'姓名',s.score as'分数' from student st left join sc s on st.sno = s.sNo where s.score<60;
-- 22、查询所有学生的选课情况；
select c.cName as'课程名',COUNT(c.cNo) as'选课数' from sc s left join course c on s.cNo=c.cNo group by cName;
-- 23、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
select st.sName as'姓名',c.cName as'课程名称',s.score as '分数' from student st left join sc s on st.sno = s.sNo left join course c on s.cNo = c.cNo where s.score>70;
-- 24、查询不及格的课程，并按课程号从大到小排列
select cNo as'课程号'from sc where score<60 order by cNo desc;
-- 25、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
select st.sno as'学号',st.sName as'姓名'from student st left join sc s on st.sno = s.sNo where s.cNo='c001' and s.score>80;
-- 26、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select s.* from sc s,sc s2 where s.cNo<>s2.cNo and s.score=s2.score;
-- 27、检索至少选修两门课程的学生学号
select s.sNo as'学号' from sc s group by s.sNo having count(s.cNo)>=2;
-- 28、删除“s002”同学的“c001”课程的成绩
delete from sc where sno='s002' and cno='c001';
-- 29、检索“c004”课程分数小于60，按分数降序排列的同学学号
select s.sNo as'学号'from sc s where s.score<60 and s.cNo = 'c004' order by s.sNo desc ;