玩转MYSQL数据库基本功

整理了一些入门sql语句，自定义玩转mysql数据库必备基本功；非常适合刚入门的小伙伴们学习！

学生表

create table student_info (
id int unsigned auto_increment primary key,
studentname varchar(10),
gender enum('男','女')
);


insert into student_info values
(null, '钱阳', 1),
(null, '胡玉', 2),
(null, '赵军', 1),
(null, '孙华', 2),
(null, '王龙', 1),
(null, '周亮', 1),
(null, '李华', 2),
(null, '刘光', 1),
(null, '张辉', 1),
(null, '杨英', 2);

课程表

create table class_info (
id int unsigned auto_increment primary key,
classname varchar(20)
);
insert into class_info values
(null, 'JS'),
(null, 'PHP'),
(null, 'MYSQL'),
(null, 'LINUX');

成绩表

create table score_info (
id int unsigned auto_increment primary key,
studentid int,
classid int,
score int
);


insert into score_info values
(null, 2, 3, 57),
(null, 4, 2, 93),
(null, 2, 1, 75),
(null, 3, 4, 59),
(null, 3, 1, 41),
(null, 7, 4, 83),
(null, 4, 4, 54),
(null, 1, 3, 88),
(null, 10, 4, 71),
(null, 5, 2, 66),
(null, 6, 1, 97),
(null, 3, 3, 80),
(null, 4, 3, 54),
(null, 6, 2, 100),
(null, 9, 1, 88),
(null, 10, 3, 73),
(null, 2, 2, 45),
(null, 6, 3, 89),
(null, 2, 4, 63),
(null, 3, 2, 48),
(null, 6, 4, 99);

1)写一个SQL语句，查询选修了 MYSQL 的学生详细资料

select s.*, c.classname from student_info as s
left join score_info as sc on s.id=sc.studentid
left join class_info as c on sc.classid=c.id
where c.classname='mysql' order by s.id;

2)写一个SQL语句，查询 杨英 同学的详细资料及其选修了的课程名称

select s.*, c.classname from student_info as s
left join score_info as sc on s.id=sc.studentid
left join class_info as c on sc.classid=c.id
where s.studentname='杨英';

3)写一个SQL语句，查询选修了>=2门课程的学生详细资料及门数

select s.*, count(*) as num from student_info as s
left join score_info as sc on s.id=sc.studentid
group by studentid having num>=2;

4)写出SQL语句,查询选修了所有课程的学生详细资料及门数

select s.*, count(*) as num from student_info as s
left join score_info as sc on s.id=sc.studentid
group by studentid having num=(
select count(*) from class_info);

5)查询被选修课程的详细资料及选修学生的人数

select c.*, count(*) as num from class_info as c
left join score_info as sc on c.id=sc.classid
group by c.classname ;
//这样不完全正确，

思考如果增加一门新课程ThinkPHP，没有学生选修，如何查询课程的详细资料及选修学生的人数？

测试完后，删除此数据，以免影响后面的查询

insert into class_info values(null, 'ThinkPHP');


select c.*, count(sc.id) as num from class_info as c
left join score_info as sc on c.id=sc.classid
group by c.classname;

delete from class_info where id=5;

6)检索所学课程包含学生 杨英 所选任何一门课程的学生详细资料及课程资料、分数

select s.*, sc.score, c.classname from student_info as s
left join score_info as sc on s.id = sc.studentid
left join class_info as c on c.id = sc.classid
where sc.classid in (
select classid from score_info where studentid = (
select id from student_info where studentname='杨英')
) order by s.id;


select s.*, sc.score, c.classname from student_info as s
left join score_info as sc on s.id = sc.studentid
left join class_info as c on c.id = sc.classid
where sc.classid in (
select classid from score_info as scc
left join student_info as ss on ss.id = scc.studentid
where ss.studentname = '杨英')order by s.id;


select s.*, sc.score, c.classname from student_info as s
left join score_info as sc on s.id = sc.studentid
left join class_info as c on c.id = sc.classid
where sc.classid in (
select classid from score_info where studentid = (
select id from student_info where studentname='杨英')
) order by s.id;

7)查询参加了所有学科考试且平均分为前两名的同学的详细资料及平均分

select s.*, avg(sc.score) as avg from student_info as s
left join score_info as sc on s.id = sc.studentid
group by s.id  having count(sc.classid) = (
select count(*) from class_info) order by avg desc limit 2;

select st.*,avg(sc.score) as avg,count(*) as num from score_info as sc
left join student_info as st on st.id=sc.studentid
group by sc.studentid
having num=(select count(*) from class_info)
order by avg desc
limit 2;

select s.*, avg(sc.score) as avg from student_info as s
left join score_info as sc on sc.studentid = s.id
LEFT JOIN class_info as c on c.id=sc.classid
GROUP BY studentid  //此处分完组之后两个count就已经定了，所以没意义，所以这个错
having COUNT(sc.score) = COUNT(c.id)
ORDER BY AVG DESC
LIMIT 2;

8)查询两门及两门以上不及格同学的详细资料和平均分

select s.* ,avg(sc.score) from student_info as s
left join score_info as sc on sc.studentid = s.id
group by studentid
having studentid in (
select studentid from score_info where score < 60
 GROUP by studentid having COUNT(*) > 2  
);