mysql练习02-1
苏*
mysql
项目描述
mysql练习02-1
上传时间
2022.06.27
浏览人数
410人
苏*
天津市河东区
Hot:14502
-- 表结构:
-- 1.学生表
-- student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
-- 2.课程表
-- course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
-- 3.教师表
-- Teacher(t_id,t_name) –教师编号,教师姓名
-- 4.成绩表
-- Score(s_id,c_id,s_score) –学生编号,课程编号,分数
CREATE TABLE student (
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY (s_id)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
-- 课程表
CREATE TABLE course (
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY (c_id)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
-- 教师表
CREATE TABLE teacher (
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY (t_id)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
-- 成绩表
CREATE TABLE score (
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY (s_id , c_id)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
-- 插入学生表测试数据
insert into student values('01' , '赵雷' , '1990-01-01' , '男');
insert into student values('02' , '钱电' , '1990-12-21' , '男');
insert into student values('03' , '孙风' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吴兰' , '1992-03-01' , '女');
insert into student values('07' , '郑竹' , '1989-07-01' , '女');
insert into student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');
-- 教师表测试数据
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');
-- 成绩表测试数据
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);
insert into course values('01' , '语文' , '02');
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT
s.*,
s1.s_score AS course_01_score,
s2.s_score AS course_02_score
FROM
student s
LEFT JOIN
score s1 ON s.s_id = s1.s_id AND s1.c_id = '01'
LEFT JOIN
score s2 ON s.s_id = s2.s_id AND s2.c_id = '02'
WHERE
s1.s_score > s2.s_score
;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT
s.*,
s1.s_score AS course_01_score,
s2.s_score AS course_02_score
FROM
student s
LEFT JOIN
score s1 ON s.s_id = s1.s_id AND s1.c_id = '01'
LEFT JOIN
score s2 ON s.s_id = s2.s_id AND s2.c_id = '02'
WHERE
s1.s_score < s2.s_score
;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
s.s_id, s.s_name, AVG(s1.s_score) AS avg_more_than_60
FROM
student s
LEFT JOIN
score s1 ON s.s_id = s1.s_id
GROUP BY s.s_id
HAVING avg_more_than_60 >= 60;
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT
s.s_id, s.s_name, AVG(s1.s_score) AS avg_more_than_60
FROM
student s
LEFT JOIN
score s1 ON s.s_id = s1.s_id
GROUP BY s.s_id
HAVING avg_more_than_60 < 60;
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT
-- *
s.s_id, s.s_name,
count(s1.c_id) as sum_course,
sum(s1.s_score) as sum_score
FROM
student s
LEFT JOIN
score s1 ON s.s_id = s1.s_id
GROUP BY s.s_id;
-- 6、查询"李"姓老师的数量
select count(1) from teacher t where t.t_name like ("李%");
-- 7、查询学过"张三"老师授课的同学的信息
/*
1.找到老师ID
2.找到课程ID
3.找到学员ID
4...
*/
select * from student s left join score s1 on s.s_id = s1.s_id where c_id = (
select c_id from course where t_id = (
select t_id from teacher where t_name = '张三' )
);
-- 8、查询没学过"张三"老师授课的同学的信息
-- 先查谁学了张三的课!
select s222.* from student s222
where
s222.s_id
NOT IN
(
select s.s_id from student s left join score s1 on s.s_id = s1.s_id where c_id = (
select c_id from course where t_id = (
select t_id from teacher where t_name = '张三' )
)
);
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT
s.*, s1.s_score as course_01_score, s2.s_score as course_02_score
FROM
student s
LEFT JOIN
score s1 ON s.s_id = s1.s_id AND s1.c_id = '01'
LEFT JOIN
score s2 ON s.s_id = s2.s_id AND s2.c_id = '02'
WHERE
s1.s_score is not null
AND
s2.s_score is not null
;
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student s left join score s1 on s.s_id = s1.s_id
where s.s_id not in (
select se.s_id from score se left join student st on se.s_id = st.s_id where se.c_id = 02) and s1.c_id = 01;
-- 11、查询没有学全所有课程的同学的信息
-- 先找学全的
SELECT
*
FROM
score s1
JOIN
score s2 ON s1.s_id = s2.s_id
JOIN
score s3 ON s1.s_id = s3.s_id
where s1.c_id = '01' and s2.c_id = '02' and s3.c_id = '03';
;
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from student s3 where s3.s_id in (
select distinct s2.s_id from score s2 where s2.c_id in
(
select s1.c_id from score s1 where s1.s_id = '01'
)
and
s2.s_id != '01');
;
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student s where s.s_id not in
(select se.s_id from score se left join student st on se.s_id = st.s_id
left join course ce on se.c_id = ce.c_id
where ce.c_id = (
select t.t_id from teacher t where t.t_name = "张三"));
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT
*
FROM
student s,
score s1
WHERE
s.s_id = s1.s_id AND s1.c_id = '01'
AND s1.s_score < 60;
--
SELECT *
FROM student s, score s1
WHERE s.s_id = s1.s_id AND s1.c_id = '01' AND s1.s_score < 60;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select st.s_id,s_name,se.s_score as '01' , se1.s_score as '02', se2.s_score as '03',avg(se3.s_score) avg_s
from student st left join score se on st.s_id = se.s_id and se.c_id = 01
left join score se1 on st.s_id = se1.s_id and se1.c_id = 02
left join score se2 on st.s_id = se2.s_id and se2.c_id =03
left join score se3 on st.s_id = se3.s_id
group by st.s_id order by avg_s desc;
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- –及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT
a.c_id,
b.c_name,
MAX(s_score),
MIN(s_score),
ROUND(AVG(s_score), 2),
ROUND(100 * (SUM(CASE
WHEN a.s_score >= 60 THEN 1
ELSE 0
END) / SUM(CASE
WHEN a.s_score THEN 1
ELSE 0
END)),
2) AS 及格率,
ROUND(100 * (SUM(CASE
WHEN a.s_score >= 70 AND a.s_score <= 80 THEN 1
ELSE 0
END) / SUM(CASE
WHEN a.s_score THEN 1
ELSE 0
END)),
2) AS 中等率,
ROUND(100 * (SUM(CASE
WHEN a.s_score >= 80 AND a.s_score <= 90 THEN 1
ELSE 0
END) / SUM(CASE
WHEN a.s_score THEN 1
ELSE 0
END)),
2) AS 优良率,
ROUND(100 * (SUM(CASE
WHEN a.s_score >= 90 THEN 1
ELSE 0
END) / SUM(CASE
WHEN a.s_score THEN 1
ELSE 0
END)),
2) AS 优秀率
FROM
score a
LEFT JOIN
course b ON a.c_id = b.c_id
GROUP BY a.c_id , b.c_name;
苏*
