mysql学习篇-3
韩琳*
MySQL, 数据库,
项目描述
mysql学习篇-3
上传时间
2022.06.28
浏览人数
747人
韩琳*
天津市宁河县
Hot:14318
-- 表结构:
-- 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');
SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM teacher;
SELECT * FROM score;
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT a.*, b.s_score AS 01_score, c.s_score AS 02_score
FROM student a
JOIN
Score b ON a.s_id = b.s_id AND b.c_id = '01'
LEFT JOIN
Score c ON a.s_id = c.s_id AND c.c_id = '02'
OR c.c_id = NULL
WHERE
b.s_score > c.s_score;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT
a.*, b.s_score AS 01_score, c.s_score AS 02_score
FROM student a
LEFT JOIN
score b ON a.s_id = b.s_id AND b.c_id = '01'
OR b.c_id = NULL
JOIN
score c ON a.s_id = c.s_id AND c.c_id = '02'
WHERE
b.s_score < c.s_score;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score
FROM student b
JOIN
score a ON b.s_id = a.s_id
GROUP BY b.s_id , b.s_name
HAVING ROUND(AVG(a.s_score), 2) >= 60;
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT
b.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score
FROM student b
LEFT JOIN
score a ON b.s_id = a.s_id
GROUP BY b.s_id , b.s_name
HAVING ROUND(AVG(a.s_score), 2) < 60
UNION SELECT
a.s_id, a.s_name, 0 AS avg_score
FROM student a
WHERE
a.s_id NOT IN (SELECT DISTINCT s_id
FROM score);
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.s_id,a.s_name,
COUNT(b.c_id) AS sum_course,
SUM(b.s_score) AS sum_score
FROM student a
LEFT JOIN
score b ON a.s_id = b.s_id
GROUP BY a.s_id , a.s_name;
-- 6、查询"李"姓老师的数量
SELECT COUNT(t_id)
FROM teacher
WHERE
t_name LIKE '李%';
-- 7、查询学过"张三"老师授课的同学的信息
SELECT a.*
FROM student a
JOIN
score b ON a.s_id = b.s_id
WHERE
b.c_id IN (SELECT c_id
FROM course
WHERE
t_id = (SELECT t_id FROM teacher WHERE t_name = '张三'));
-- 8、查询没学过"张三"老师授课的同学的信息
SELECT * FROM student c
WHERE
c.s_id NOT IN (SELECT a.s_id FROM student a
JOIN
score b ON a.s_id = b.s_id
WHERE
b.c_id IN (SELECT c_id FROM course WHERE
t_id = (SELECT t_id FROM teacher WHERE t_name = '张三')));
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT a.* FROM
student a,
score b,
score c
WHERE
a.s_id = b.s_id AND a.s_id = c.s_id
AND b.c_id = '01'
AND c.c_id = '02';
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT a.* FROM student a
WHERE
a.s_id IN
(SELECT s_id FROM score WHERE c_id = '01') AND
a.s_id NOT IN (SELECT s_id FROM score WHERE c_id = '02');
-- 11、查询没有学全所有课程的同学的信息
SELECT s.*
FROM student s
WHERE
s.s_id IN
(SELECT s_id FROM score
WHERE s_id NOT IN
(SELECT a.s_id FROM score a
JOIN
score b ON a.s_id = b.s_id AND b.c_id = '02'
JOIN
score c ON a.s_id = c.s_id AND c.c_id = '03'
WHERE
a.c_id = '01'));
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT * FROM student
WHERE
s_id IN (SELECT DISTINCT a.s_id
FROM score a
WHERE
a.c_id IN
(SELECT a.c_id FROM score a WHERE a.s_id = '01'));
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT a.s_name
FROM student a
WHERE a.s_id NOT IN
(SELECT s_id FROM score
WHERE
c_id = (SELECT c_id FROM course
WHERE
t_id = (SELECT t_id FROM teacher
WHERE
t_name = '张三'))
GROUP BY s_id);
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT a.*, b.c_id, b.s_score
FROM student a, score b
WHERE
a.s_id = b.s_id AND b.c_id = '01'
AND b.s_score < 60
ORDER BY b.s_score DESC;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT a.s_id,
(SELECT
s_score
FROM score
WHERE
s_id = a.s_id AND c_id = '01') AS 语文,
(SELECT s_score FROM score
WHERE
s_id = a.s_id AND c_id = '02') AS 数学,
(SELECT s_score FROM score
WHERE
s_id = a.s_id AND c_id = '03') AS 英语,
ROUND(AVG(s_score), 2) AS 平均分
FROM score a
GROUP BY a.s_id
ORDER BY 平均分 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;
-- 26、查询每门课程被选修的学生数
SELECT
c_id, COUNT(s_id)
FROM
score a
GROUP BY c_id;
-- 27、查询出只有两门课程的全部学生的学号和姓名
SELECT s_id, s_name
FROM student
WHERE
s_id IN (SELECT
s_id FROM
score
GROUP BY s_id
HAVING COUNT(c_id) = 2);
-- 28、查询男生、女生人数
SELECT
s_sex, COUNT(s_sex) AS 人数
FROM student
GROUP BY s_sex;
-- 30、查询同名同性学生名单，并统计同名人数
SELECT
a.s_name, a.s_sex, COUNT(*)
FROM student a
JOIN
student b ON a.s_id != b.s_id AND a.s_name = b.s_name
AND a.s_sex = b.s_sex
GROUP BY a.s_name , a.s_sex;
-- 31、查询1990年出生的学生名单
SELECT
s_name
FROM
student
WHERE
s_birth LIKE '1990%';
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
c_id, ROUND(AVG(s_score), 2) AS avg_score
FROM score
GROUP BY c_id
ORDER BY avg_score DESC , c_id ASC;
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT
a.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score
FROM score a
LEFT JOIN
student b ON a.s_id = b.s_id
GROUP BY s_id
HAVING avg_score >= 85;
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT
a.s_name, b.s_score
FROM score b
LEFT JOIN
student a ON a.s_id = b.s_id
WHERE
b.c_id = (SELECT c_id FROM course
WHERE
AND b.s_score < 60;
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT a.s_name, b.c_name, c.s_score
FROM course b
LEFT JOIN
score c ON b.c_id = c.c_id
LEFT JOIN
student a ON a.s_id = c.s_id
WHERE
c.s_score >= 70;
-- 37、查询不及格的课程
SELECT
a.s_id, a.c_id, b.c_name, a.s_score
FROM score a
LEFT JOIN
course b ON a.c_id = b.c_id
WHERE
a.s_score < 60;
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select * from score as se left join student as st on se.s_id = st.s_id
left join course as ce on se.c_id = ce.c_id where ce.c_id = 01 and se.s_score > 80 ;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
-- t_id c_id s_id(s) max()
SELECT a.*, b.s_score, b.c_id, c.c_name
FROM student a
LEFT JOIN
Score b ON a.s_id = b.s_id
LEFT JOIN
course c ON b.c_id = c.c_id
WHERE
b.c_id = (SELECT c_id FROM course c,teacher d
WHERE
c.t_id = d.t_id AND d.t_name = '张三')
AND b.s_score IN (SELECT
MAX(s_score)
FROM Score
WHERE
c_id = (SELECT c_id FROM
course c, teacher d
WHERE
c.t_id = d.t_id AND d.t_name = '张三'));
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）
select se.c_id, count(1) as n from score se group by se.c_id having n>5;
-- 44、检索至少选修两门课程的学生学号
select * from student st left join score se on st.s_id = se.s_id
group by st.s_id having count(se.c_id) >= 2;
-- 45、查询选修了全部课程的学生信息
select * 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
where se.s_score is not null and se1.s_score is not null and se2.s_score is not null;
select * from student st left join score se on st.s_id = se.s_id
group by st.s_id having count(se.c_id) = 3;
-- –46、查询各学生的年龄
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
-- 时间格式的统一表示: %Y-%m-%d %H:%i:%s:代表: 年年年年-月月-日日 时时:分分:秒秒
-- NOW(): 获取当前时间
-- DATE_FORMAT:转化时间为指定格式
-- DATE_FORMAT(NOW(),’%Y’):获取年,其他的以此类推
-- WEEK('2019-07-11',1): 返回'2019-07-11'是2019年的第几周: 结果是28
-- YEARWEEK('2019-07-11',1);返回整数型的6位, 结果为:201928
-- MONTH('2018-06-08'): 获取月, 结果为6
-- select WEEK('2019-07-11',1) from student;
-- select YEARWEEK('2019-07-11',1) from student;
SELECT s_birth,
(DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(s_birth, '%Y')
- (CASE WHEN DATE_FORMAT(NOW(), '%m%d')>
DATE_FORMAT(s_birth, '%m%d')
THEN 0 ELSE 1 END)) AS age
FROM student;
-- 47、查询本周过生日的学生
select * from student where week (concat(year(curdate()),'-',mid(s_birth,6,5)),1) = week (now(),1);
-- 48、查询下周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'),1)+1 =WEEK(s_birth);
SELECT * FROM student AS s WHERE WEEK(CONCAT(YEAR(CURDATE()),"-",MID(s_birth,6,5)),1) = WEEK(now(),1)+1;
SELECT * FROM student AS s WHERE WEEK(CONCAT(YEAR(CURDATE()),"-",MID(s_birth,6,5)),1) = WEEK(curdate(),1)+1;
-- CURDATE() 取的是年月日，CURTIME()取的是时分秒
-- 50、查询下月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth);
韩琳*
