mysql的强化练习

mysql的强化练习

sql 语句练习

• 创建语句

-- 课程表
create table course
(
    cid       int auto_increment
        primary key,
    cname     varchar(16) not null,
    tearch_id int         not null,
    constraint fk_course_teacher_id
        foreign key (tearch_id) references teacher (tid)
) default charset = utf8;
-- 班级表
create table class
(
    cid     int auto_increment
        primary key,
    caption varchar(16) not null
) default charset = utf8;
-- 学生表
create table student
(
    id       int auto_increment
        primary key,
    sname    varchar(11) not null,
    gender   char        not null,
    class_id int         not null,
    constraint fk_student_class_id
        foreign key (class_id) references class (cid)
) default charset = utf8;
-- 教师表
create table teacher
(
    tid   int auto_increment
        primary key,
    tname varchar(11) not null
) default charset = utf8;
-- 成绩表
create table score
(
    sid        int auto_increment primary key not null,
    student_id int                            not null,
    course_id  int                            not null,
    num        int(11)                        not null,
    constraint fk_score_student_id foreign key score (student_id) references student (id),
    constraint fk_score_course_id foreign key score (course_id) references course (cid)
) default charset = utf8;

• 插入信息

-- 班级信息
insert into class(caption)
values ('一年1班'),
       ('一年2班'),
       ('一年3班'),
       ('二年1班'),
       ('二年2班'),
       ('二年3班'),
       ('三年1班'),
       ('三年3班');
       
-- 教师信息
insert into teacher(tname)
values ('苏怡丹'),
       ('张梅'),
       ('李蕴芮'),
       ('郑英霞'),
       ('王秀荣'),
       ('张天文'),
       ('王春'),
       ('李四');
       
-- 学生信息
insert into student(sname, gender, class_id)
values ('张地里', '男', 1),
       ('李铁锤', '女', 1),
       ('张山炮', '男', 1),
       ('范伟', '女', 2),
       ('田颖', '女', 2),
       ('江鹏娜', '女', 2),
       ('姚俊宇', '男', 3),
       ('张睿', '男', 3),
       ('张梅', '女', 3),
       ('高青云', '男', 4),
       ('刘静霞', '女', 4),
       ('李亚丽', '女', 4),
       ('张龙', '男', 5),
       ('谷慧勇', '男', 5),
       ('郝林文', '男', 5),
       ('卜光辉', '男', 6),
       ('刘鲜茵', '女', 6),
       ('徐佳晨', '男', 6);
       
-- 课程信息
insert into course(cname, tearch_id)
values ('python', 1),
       ('java', 2),
       ('php', 3),
       ('html', 4),
       ('js', 5),
       ('vue', 6);
       
-- 成绩信息
insert into score(student_id, course_id, num)
VALUES (1, 1, 100),
       (1, 2, 95),
       (2, 2, 85),
       (2, 3, 65),
       (3, 3, 75),
       (3, 4, 80),
       (4, 4, 61),
       (4, 5, 81),
       (5, 5, 71),
       (5, 6, 88),
       (6, 5, 99),
       (6, 6, 63);

• sql语句 练习

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;