drop user 'along'@'127.0.0.1';
select host, user
from mysql.user;
-- 创建用户 along 并赋予此数据库的所有权限。
create user 'along'@'%' identified by "20020115";
grant all privileges on mysql01.* to 'along'@'%';
show grants for 'along'@'%';
-- 查询姓“李”的老师的个数。
select count(tid)
from teacher
where tname like '李%';
-- 查询姓“张”的学生名单。
select count(id)
from student
where sname like '张%';
-- 查询男生、女生的人数。
select gender, count(id) as num
from mysql01.student
group by student.gender;
-- 查询同名同姓学生名单，并统计同名人数
select sname, count(id)
from student
group by sname;
-- 查询 “三年1班” 的所有学生。
select sname, class_id
from student
where class_id = (select cid from class where caption = '二年3班');
-- 查询 每个 班级的 班级名称、班级人数
select class.caption, count(sname)
from class
left join mysql01.student s on class.cid = s.class_id
group by class.caption;
-- 查询成绩小于70分的同学的学号、姓名、成绩、课程名称
select sid, sname, num, cname
from score
left join mysql01.course c on c.cid = score.course_id
left join mysql01.student s on score.student_id = s.id
where num < 70;
-- 查询选修了 “python” 的所有学生ID、学生姓名、成绩
select s.id, sname, num
from score
join mysql01.course c on c.cid = score.course_id
left join mysql01.student s on s.id = score.student_id
where course_id = (select cid from course where cname = 'python');
-- 查询选修了 “php” 且分数低于70的的所有学生ID、学生姓名、成绩。
select s.id, sname, num
from score
left join mysql01.student s on s.id = score.student_id
where course_id = (select cid from course where cname = 'php')
and num < 70;
-- 查询所有同学的学号、姓名、选课数、总成绩。
select id, sname, count(score.student_id)
from score
left join mysql01.student s on s.id = score.student_id
group by id, sname;
-- 查询各科被选修的学生数。
select cname, count(score.student_id)
from score
left join mysql01.course c on score.course_id = c.cid
group by course_id;
-- 查询各科成绩的总分、最高分、最低分，显示：课程ID、课程名称、总分、最高分、最低分。
select cid, cname, sum(num), max(num), min(num)
from score
left join mysql01.course c on c.cid = score.course_id
group by course_id;
-- 查询各科成绩的平均分，显示：课程ID、课程名称、平均分。
select c.cid, cname, avg(num) as avg
from score
left join mysql01.course c on c.cid = score.course_id
group by c.cid, cname;
-- 查询各科成绩的平均分，显示：课程ID、课程名称、平均分（按平均分从大到小排序）。
select cid, cname, avg(num) as avg
from course
join mysql01.score s on course.cid = s.course_id
group by cid, cname
order by avg desc;
-- 查询各科成绩的平均分和及格率，显示：课程ID、课程名称、平均分、及格率。
select course_id, count(1)
from score
group by course_id;
select sid,
course_id,
num,
case when score.num >= 60 then 1 else 0 end "是否及格"
from score;
select sid, course_id, num, case when score.num > 60 then 1 else 0 end "是否及格"
from score;
select course_id,
course.cname,
avg(num),
sum(case when score.num > 70 then 1 else 0 end) / count(1) * 100 as percent
from score
left join course on score.course_id = course.cid
group by course_id;
select course_id,
course.cname,
avg(num),
sum(case when score.num > 70 then 1 else 0 end) / count(1) * 100 as jg
from score
left join course on score.course_id = course.cid
group by course_id;
-- 查询平均成绩大于60的所有学生的学号、平均成绩；
select score.sid, avg(num)
from score
group by score.sid;
-- 查询平均成绩大于85的所有学生的学号、平均成绩、姓名。
select sid, avg(num) as avg, s.sname
from score
left join mysql01.student s on s.id = score.student_id
GROUP BY sid, s.sname
HAVING avg > 85;
-- 查询 “二年3班” 每个学生的 学号、姓名、总成绩、平均成绩。
select id, sname, sum(num), avg(num)
from score
left join mysql01.student s on s.id = score.student_id
group by id, sname;
-- 查询各个班级的班级名称、总成绩、平均成绩、及格率（按平均成绩从大到小排序）。
select caption, sum(num), avg(num), sum(case when score.num > 70 then 1 else 0 end) / count(1) * 100 as jg
from score
left join mysql01.student s on s.id = score.student_id
left join mysql01.class c on s.class_id = c.cid
group by caption
order by avg(num) desc;
-- 查询学过 “波多” 老师课的同学的学号、姓名。
select id, sname
from student
left join mysql01.score s on student.id = s.student_id
left join mysql01.course c on student.id = c.tearch_id
left join mysql01.teacher t on c.tearch_id = t.tid
where tname = '苏怡丹';
-- 查询没学过 “波多” 老师课的同学的学号、姓名。
select id, sname
from student
left join mysql01.score s on student.id = s.student_id
left join mysql01.course c on student.id = c.tearch_id
left join mysql01.teacher t on c.tearch_id = t.tid
where tname != '苏怡丹'
group by sname, id;
-- 查询选修 “苏怡丹” 老师所授课程的学生中，成绩最高的学生姓名及其成绩（不考虑并列）。
select sname, max(score.num) as max
from score
left join mysql01.student s on s.id = score.student_id
left join course c on score.course_id = c.cid
left join mysql01.teacher t on tid = c.tearch_id
where tname = '苏怡丹'
group by sname
having max;
-- 查询选修 '苏怡丹' 老师所授课程的学生中，成绩最高的学生姓名及其成绩（考虑并列）。
SELECT
student.sid,
student.sname
FROM
score
LEFT JOIN student ON score.student_id = student.sid
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '苏怡丹'
AND score.num = (
SELECT
max( num )
FROM
score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '苏怡丹'
)
-- 查询只选修了一门课程的全部学生的学号、姓名。
select count(case when s.student_id is not null then s.student_id end) as count, sname
from student
left join mysql01.score s on student.id = s.student_id
where sid is not null
group by sname
having count > 2;
-- 查询至少选修两门课程的学生学号、学生姓名、选修课程数量。
select count(case when s.student_id is not null then s.student_id end) as count, sname
from student
left join mysql01.score s on student.id = s.student_id
where sid is not null
group by sname
having count >= 2;
-- 查询两门及以上不及格的同学的学号、学生姓名、选修课程数量。
# case when s.sid is not null then s.sid end as sid
-- 查询选修了所有课程的学生的学号、姓名。
select case when s.student_id is not null then s.student_id end as ssid, sname
from student
left join mysql01.score s on student.id = s.student_id
where sid is not null
group by sname, case when s.student_id is not null then s.student_id end;
-- 查询未选修所有课程的学生的学号、姓名。
select *
from student
left join mysql01.score s on student.id = s.student_id;
-- 查询所有学生都选修了的课程的课程号、课程名。
select c.cid, c.cname
from score
left join mysql01.course c on c.cid = score.course_id
group by score.course_id
HAVING COUNT(1) = (select count(1) from student);
-- 查询选修 “生物” 和 “物理” 课程的所有学生学号、姓名。
select id, student.sname
from student
left join mysql01.score s on student.id = s.student_id
left join mysql01.course c on s.course_id = c.cid
where c.cname in ('python', 'php')
group by student.sname, id
having count(1) = 2;
SELECT student.id,
student.sname
FROM score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.id
WHERE course.cname in ('python', 'php')
GROUP BY student_id
having count(1) = 2;
-- 查询至少有一门课与学号为“1”的学生所选的课程相同的其他学生学号 和 姓名 。
select s.id, s.sname
from score
left join mysql01.course c on c.cid = score.course_id
left join mysql01.student s on s.id = score.student_id
where score.course_id in (select course_id from score where student_id = 1)
and score.student_id != 1
group by s.id, s.sname
having count(1) > 1;
-- 查询“python”课程比“php”课程成绩高的所有学生的学号；
select student_id,
max(case cname when 'python' then num else -1 end) as py,
max(case cname when 'php' then num else -1 end) as php
from score
LEFT JOIN course ON score.course_id = course.cid
where cname in ('python', 'php')
group by student_id
having py > php;
SELECT student_id,
max(CASE cname WHEN 'python' THEN num ELSE 0 END) AS sw,
max(CASE cname WHEN 'php' THEN num ELSE 0 END) AS wl
FROM score
LEFT JOIN course ON score.course_id = course.cid
WHERE cname IN ('python', 'php')
GROUP BY student_id
HAVING sw > wl;
-- 查询每门课程成绩最好的前3名 (不考虑成绩并列情况) 。
select cid,
cname,
(select s.sname
from score
left join mysql01.student s on s.id = score.student_id
where course_id = course.cid
order by num desc
limit 1 offset 0) as '第一名',
(select s2.sname
from score
left join mysql01.student s2 on s2.id = score.student_id
where course_id = course.cid
order by num desc
limit 1 offset 1) as '第二名',
(select s3.sname
from score
left join mysql01.student s3 on score.student_id = s3.id
where course_id = course.cid
limit 1 offset 2) as '第三名'
from course;
-- 查询每门课程成绩最好的前3名 (考虑成绩并列情况) 。
select cid,
cname,
(select num from score where course_id = course.cid group by num order by num desc limit 1 offset 0) as "最高分",
(select num
from score
where course_id = course.cid
group by num
order by num desc
limit 1 offset 1) as "第二高分",
(select num from score where course_id = course.cid group by num order by num desc limit 1 offset 2) as "第三高分"
from course;
-- 创建一个表 `sc`，然后将 score 表中所有数据插入到 sc 表中。
create table sc
(
sid int auto_increment primary key not null,
student_id int not null,
course_id int not null,
num int(11) not null
)default charset=utf8;
insert into sc select * from score;
表结构设计（博客系统）
-- 创建数据库
create database blog default charset utf8 collate utf8_general_ci;
use blog;
-- 创建用户表
create table user
(
id int(11) not null auto_increment primary key,
username varchar(16) not null,
password varchar(32) not null,
number char(11) not null,
name varchar(16) not null,
email varchar(32) not null,
c_time datetime not null
) default charset = utf8;
-- 文章表
create table article
(
id int(11) not null auto_increment primary key,
reader int(11) not null,
comment_num int(11) not null,
like_n int(11) not null,
author_id int(11) not null,
content text not null,
c_time datetime not null
) default charset = utf8;
-- 评论表
create table comment
(
id int(11) not null primary key,
content text not null,
user_id int(11) not null,
c_time datetime
) default charset = utf8;
-- 推荐表
create table up_down
(
id int(11) not null auto_increment primary key,
choice int(11) not null,
user_id int(11) not null,
article_id int
) default charset = utf8;
