mysql单表-多表查询 内连接,外连接 练习

create database studentManage;
use studentManage;
-- 1.学生表 Student(SId,Sname,Sage,Ssex)
-- SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
create table student(
	sid int primary key auto_increment,
	sname char(16) not null,
	sage int ,
	ssex char(1) default '男'
);

-- 2.教师表 Teacher(TId,Tname) 
-- TId 教师编号,Tname 教师姓名
create table teacher(
	tid int primary key auto_increment,
	tname char(16) not null
);

-- 3.课程表 Course(CId,Cname,TId) 
-- CId 课程编号,Cname 课程名称,TId 教师编号
create table course(
	cid int primary key auto_increment,
	cname char(32),
	tid int 
);

-- 4.成绩表 SC(SId,CId,score) 
-- SId 学生编号,CId 课程编号,score 分数
create table sc(
	sid int ,
	cid int ,
	score double,
	PRIMARY key (sid,cid)
);

--   使用sql语句创建以上四张表 添加外键约束
-- （添加外键  alter table 表名 add foreign key (列名) references 表名(列名)）  
alter table course add foreign key(tid) REFERENCES teacher(tid);
alter table sc add foreign key(sid) REFERENCES student(sid);
alter table sc add foreign key(cid) REFERENCES course(cid);
	
-- 新增数据 主表   --- 从表
insert into	student(sname,sage,ssex) values 
('张三',20,'男'),
('李四',19,'男'),
('王五',18,'女'),
('赵六',19,'男');
	
insert into teacher(tname) values ('李林'),('张旭'),('林毅'),('张三');	
	
insert into course(cname,tid) values ('javaSE',1),('HTML5',2),('UI',3),('Python',4);
	
insert into sc(sid,cid,score) values 
	(1,1,80),(1,2,84),(1,3,70),(1,4,50),
	(2,1,86),(2,2,80),(2,3,60),(2,4,90),
	(3,1,60),(3,2,74),(3,3,71),(3,4,59);
	
select * from student;
select * from teacher;	
select * from course;	
select * from sc;

-- 三.完成以下查询功能

-- 1.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT AVG(score) FROM score
GROUP BY sid;

SELECT student.sid,sname,AVG(score) 
FROM sc JOIN student ON student.sid = sc.sid
GROUP BY sc.sid
HAVING AVG(score) >= 60;

-- 2.查询在 SC 表存在成绩的学生信息
SELECT DISTINCT student.* FROM sc LEFT JOIN student ON sc.sid = student.sid;

-- 子查询,先找出所有参加考试的学号

SELECT * from student
WHERE sid IN (SELECT DISTINCT sid from sc);


-- 3.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT student.sid, student.sname, COUNT(cid),SUM(score)
FROM sc RIGHT JOIN student ON sc.sid = student.sid
GROUP BY student.sid;

SELECT student.sid,sname 姓名 ,
COUNT(cid) 选课总数 ,SUM(score) 总成绩 
FROM student LEFT OUTER JOIN sc ON student.Sid = sc.sid 
GROUP BY sc.Sid ORDER BY student.sid;


-- 4.查有没有成绩的学生信息
SELECT student.*, score FROM student LEFT JOIN sc ON sc.sid = student.sid
WHERE sc.score is null;


SELECT * from student
WHERE sid NOT IN (SELECT DISTINCT sid from sc);

-- 5.查询「李」姓老师的数量
SELECT COUNT(*) FROM teacher WHERE teacher.tname LIKE '李%';

-- 6.查询学过「张三」老师授课的同学的信息
SELECT student.*, sc.score,course.cname,teacher.* 
FROM student 
JOIN sc ON sc.sid = student.sid
JOIN course ON sc.cid = course.cid
JOIN teacher ON teacher.tid = course.tid
WHERE teacher.tname = '张三';

-- 7.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩(所有科目)
-- 根据学号-分组,统计不及格课程总数,在筛选
SELECT sc.sid, COUNT(cid)
FROM sc
WHERE sc.score <= 60
GROUP BY sid
HAVING COUNT(cid) >= 2;

SELECT student.sid, sname, AVG(score) ,COUNT(cid)
FROM sc 
JOIN student ON student.sid = sc.sid
WHERE sc.score <= 60
GROUP BY student.sid
HAVING COUNT(sc.cid) >= 2;

SELECT student.sid, sname, AVG(score) ,COUNT(cid)
FROM sc 
JOIN student ON student.sid = sc.sid
WHERE sc.sid 
IN (SELECT sc.sid
FROM sc
WHERE sc.score <= 60
GROUP BY sid
HAVING COUNT(cid) >= 2)
GROUP BY student.sid;

-- 8.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT * FROM student JOIN sc ON student.sid = sc.sid
WHERE sc.cid = 1 AND sc.score <= 60
ORDER BY sc.score desc;

-- 9.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT score FROM sc;

SELECT sc.sid, AVG(sc.score) avgscore 
FROM sc JOIN student ON sc.sid = student.sid
GROUP BY student.sid
ORDER BY AVG(sc.score) DESC;

SELECT *
FROM sc s1,
(SELECT sc.sid, AVG(sc.score) avgscore 
FROM sc GROUP BY sc.sid) s2
WHERE s1.sid = s2.sid
ORDER BY avgscore DESC;

-- 10.查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT course.cid 课程号, course.cname 课程名称,
MAX(score) 最高分, MIN(score) 最低分, AVG(score) 平均分,COUNT(*), 
SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT(*) 及格率,
SUM(CASE WHEN score >= 70 AND score <= 80 THEN 1 ELSE 0 END) / COUNT(*) 中等率,
SUM(CASE WHEN score >= 80 AND score <= 90 THEN 1 ELSE 0 END) / COUNT(*) 优良率,
SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / COUNT(*) 优秀
FROM sc JOIN course ON sc.cid = course.cid
GROUP BY sc.cid 
ORDER BY COUNT(*) DESC, sc.cid ASC;

SELECT course.cid 课程号, course.cname 课程名称,
MAX(score) 最高分, MIN(score) 最低分, AVG(score) 平均分,COUNT(*), 
CONCAT(ROUND(SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT(*),2),'%') 及格率,
CONCAT(ROUND(SUM(CASE WHEN score >= 70 AND score <= 80 THEN 1 ELSE 0 END) / COUNT(*),2),'%') 中等率,
CONCAT(ROUND(SUM(CASE WHEN score >= 80 AND score <= 90 THEN 1 ELSE 0 END) / COUNT(*),2),'%') 优良率,
CONCAT(ROUND(SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / COUNT(*),2),'%') 优秀
FROM sc JOIN course ON sc.cid = course.cid
GROUP BY sc.cid 
ORDER BY COUNT(*) DESC, sc.cid ASC;

SELECT course.cid 课程号, course.cname 课程名称,
MAX(score) 最高分, MIN(score) 最低分, AVG(score) 平均分,COUNT(*), 
CONCAT(ROUND(SUM(score >= 60 ) / COUNT(*),2),'%') 及格率,
CONCAT(ROUND(SUM(score >= 70 AND score <= 80) / COUNT(*),2),'%') 中等率,
CONCAT(ROUND(SUM(score >= 80 AND score <= 90) / COUNT(*),2),'%') 优良率,
CONCAT(ROUND(SUM(score >= 90) / COUNT(*),2),'%') 优秀
FROM sc JOIN course ON sc.cid = course.cid
GROUP BY sc.cid 
ORDER BY COUNT(*) DESC, sc.cid ASC;

-- 11.要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.cid 课程号,COUNT(sc.sid) 选修人数
FROM sc
GROUP BY sc.cid
ORDER BY 选修人数 DESC, 课程号 ASC;