MYSQL-习题掌握

已于 2023-08-23 11:50:01 修改
阅读量417 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
于 2023-08-14 17:03:45 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/allurelover/article/details/132277969
版权

文章目录

SQL基本操作

1 设计表操作

1.1 关系表字段

  • 1 学生表 student
s_ids_names_births_sex
学生编号学生姓名学生年月学生性别
  • 2 课程表 course
c_idc_namet_id
课程编号课程名称教师标号
  • 3 教师表 teacher
t_idt_name
教师编号教师姓名
  • 4 成绩表 score
s_idc_ids_score
学生编号课程编号课程分数

1.2 关系表创建

  • 1 创建 student 表
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;
  • 2 创建 course 表
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;
  • 3 创建 teacher 表
CREATE TABLE teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
  • 4 创建score表
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;

1.3 关系表数据

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('01' , '语文' , '02');
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);

1.4 关系表关系

在这里插入图片描述

2 SQL操作

2.1 SQL 1-10

  • 1、查询 “01” 课程比 “02” 课程成绩高的学生的信息及课程分数
方式1：三表关联查询
SELECT s1.* ,s2.s_score AS 01_score,s3.s_score AS 02_score 
FROM student s1
JOIN score s2 
ON s1.s_id=s2.s_id AND s2.c_id='01'
LEFT JOIN score s3
ON s1.s_id=s3.s_id AND s3.c_id='02' 
WHERE s2.s_score>s3.s_score;

方式2：关联查询+子查询
SELECT s1.*,s2.02_score
FROM 
(SELECT a.* ,b.s_score AS 01_score 
FROM student a
JOIN score b 
ON a.s_id=b.s_id AND b.c_id='01')s1,
(SELECT a.* ,c.s_score AS 02_score 
FROM student a
JOIN score c
ON a.s_id=c.s_id AND c.c_id='02')s2
WHERE s1.s_id = s2.s_id AND s1.01_score > s2.02_score

在这里插入图片描述`

步骤
第一步：先查询student和score.课程号是01的学生信息以及01成绩
第二步：再查询student和score.课程号是02的学生信息以及02成绩
第三步:关联查询第一步和第二步的表，帅选处01分数>02分数的信息

  • 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
三表关联查询：
SELECT s1.* ,s2.s_score AS 01_score,s3.s_score AS 02_score 
FROM student s1 JOIN score s2 
ON s1.s_id=s2.s_id AND s2.c_id='01'
LEFT JOIN Score s3 ON s1.s_id=s3.s_id AND s3.c_id='02' 
WHERE s2.s_score<s3.s_score;

在这里插入图片描述

  • 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s1.s_id,s1.s_name,ROUND(AVG(s2.s_score),2)AS avg_score 
FROM student s1 JOIN score s2 ON s1.s_id = s2.s_id 
GROUP BY s2.s_id HAVING avg_score>=60;

在这里插入图片描述

步骤：
第一步：先查询每位同学的平均成绩显示学生编号和学生姓名和平均成绩
第二步：再对平均成绩进行筛选

  • 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)
SELECT s1.s_id,s1.s_name, IFNULL(ROUND(AVG(s2.s_score),2),0) AS avg_score 
FROM student s1 LEFT JOIN score s2 ON s1.s_id = s2.s_id 
GROUP BY s2.s_id HAVING avg_score<60;

在这里插入图片描述

步骤：
第一步：先查询每位同学的平均成绩显示学生编号和学生姓名和平均成绩,包括无成绩的学生的信息
第二步：再对平均成绩进行筛选

  • 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT s1.s_id,s1.s_name,COUNT(s2.c_id) AS 选课总数,IFNULL(SUM(s2.s_score),0) AS 总成绩 
FROM student s1 LEFT JOIN score s2 ON s1.s_id = s2.s_id
GROUP BY s1.s_id;

在这里插入图片描述

步骤：
第一步：关联学生表和成绩表，注意需要所有学生
第二步：在关联表的数据上查询学生编号、学生姓名、选课总数、所有课程的总成绩，
第三步：将为null的数据，设置为0

  • 6、查询"李"姓老师的数量
SELECT COUNT(t_id),t_name FROM teacher WHERE t_name LIKE '李%';

在这里插入图片描述

  • 7、查询学过"张雪峰"老师授课的同学的信息
SELECT s1.* FROM student s1 JOIN score s2 ON s1.`s_id` = s2.`s_id` 
WHERE s2.`c_id` IN
(SELECT c1.c_id FROM teacher t1 JOIN course c1 ON t1.t_id = c1.t_id 
WHERE t1.t_name = '张雪峰');

在这里插入图片描述步骤：
第一步：查询张雪峰老师所带的课程的编号

SELECT c1.c_id FROM teacher t1 JOIN course c1 ON t1.t_id = c1.t_id 
WHERE t1.t_name = '张雪峰';

在这里插入图片描述

第二步：查询选择了02课程的学生的信息

SELECT s1.* FROM student s1 JOIN score s2 ON s1.`s_id` = s2.`s_id` 
WHERE s2.`c_id` IN
(SELECT c1.c_id FROM teacher t1 JOIN course c1 ON t1.t_id = c1.t_id 
WHERE t1.t_name = '张雪峰');

在这里插入图片描述

  • 8、查询没学过"张雪峰"老师授课的同学的信息
方式1：
SELECT * FROM student WHERE s_id NOT IN(
SELECT s1.s_id FROM student s1 JOIN score s2 ON s1.`s_id` = s2.`s_id` 
WHERE s2.`c_id` IN
(SELECT c1.c_id FROM teacher t1 JOIN course c1 ON t1.t_id = c1.t_id 
WHERE t1.t_name = '张雪峰'));

方式2:
SELECT c.* 
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 = '张雪峰')));

在这里插入图片描述
步骤:
第一步：查询张雪峰的课程编号

SELECT c1.c_id FROM teacher t1 JOIN course c1 ON t1.t_id = c1.t_id 
WHERE t1.t_name = '张雪峰';

第二步：查询选择过张雪峰老师课程的学生的编号

SELECT s1.s_id FROM student s1 JOIN score s2 ON s1.`s_id` = s2.`s_id` 
WHERE s2.`c_id` IN
(SELECT c1.c_id FROM teacher t1 JOIN course c1 ON t1.t_id = c1.t_id 
WHERE t1.t_name = '张雪峰')

在这里插入图片描述

第三步：查询所有学生信息去除掉选择过张雪峰课程的学生编号

SELECT * FROM student WHERE s_id NOT IN(
SELECT s1.s_id 
FROM student s1 
JOIN score s2 
ON s1.`s_id` = s2.`s_id` 
WHERE s2.`c_id` IN
(SELECT c1.c_id 
FROM teacher t1 
JOIN course c1 
ON t1.t_id = c1.t_id 
WHERE t1.t_name = '张雪峰'));

在这里插入图片描述

  • 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
方式1：三表关联查询
SELECT s1.* FROM student s1 
JOIN score s2 
ON s1.s_id = s2.s_id 
JOIN score s3 
ON s1.s_id = s3.s_id 
WHERE s2.c_id = '01' AND s3.c_id = '02';

方式2
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 s1.* 
FROM student s1
WHERE s1.s_id IN 
(SELECT s_id FROM score WHERE c_id='01' ) 
AND s_id NOT IN
(SELECT s_id FROM score WHERE c_id='02')

在这里插入图片描述

步骤：
第一步：先查询选择课程是02的学生编号

SELECT s_id FROM score WHERE c_id='02'

在这里插入图片描述

第二步：再查询选择课程是01的学生编号，并且此编号不在选择课程02的学生编号中

SELECT s_id FROM score WHERE c_id='01'
AND s_id NOT IN
(SELECT s_id FROM score WHERE c_id='02')

在这里插入图片描述

第三步：将第二步的结果作为查询学生信息的条件

SELECT s1.* 
FROM student s1
WHERE s1.s_id IN 
(SELECT s_id FROM score WHERE c_id='01' ) 
AND s_id NOT IN
(SELECT s_id FROM score WHERE c_id='02')

在这里插入图片描述

2.2 SQL 11-20

  • 11、查询没有学全所有课程的同学的信息

第一步：先查询同时学习了01 02 03 课程的学生编号

SELECT s1.s_id 
FROM student s1 
JOIN score s2 ON s1.s_id = s2.s_id 
JOIN score s3 ON s1.s_id = s3.s_id 
JOIN score s4 ON s1.s_id = s4.s_id 
WHERE s2.c_id = '01'
AND s3.c_id = '02' 
AND s4.c_id = '03';

第二步：对第一步的操作进行取反

SELECT * FROM student WHERE s_id NOT IN(
SELECT s1.s_id 
FROM student s1 
JOIN score s2 ON s1.s_id = s2.s_id 
JOIN score s3 ON s1.s_id = s3.s_id 
JOIN score s4 ON s1.s_id = s4.s_id 
WHERE s2.c_id = '01'
AND s3.c_id = '02' 
AND s4.c_id = '03'
);

在这里插入图片描述

  • 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

第一步：先查询01号学生的课程编号

SELECT s2.c_id FROM student s1 JOIN score s2
ON s1.s_id = s2.s_id 
WHERE s1.s_id = '01';

第二步：查询其他学生的课程编号在01学生的课程编号里

SELECT DISTINCT s1.* 
FROM student s1,score s2 
WHERE s1.`s_id` = s2.`s_id` 
AND s2.`c_id`IN (
SELECT s2.c_id FROM student s1 JOIN score s2
ON s1.s_id = s2.s_id 
WHERE s1.s_id = '01')

在这里插入图片描述

  • 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

第一步：先查询s_id是01的学生的课程号和课程号的个数

SELECT c_id FROM score WHERE s_id = '01';
SELECT COUNT(c_id) FROM score WHERE s_id = '01';

第二步：查询每位学生的课程总数

SELECT s1.s_id,COUNT(s2.`c_id`) c_num 
FROM student s1 
JOIN score s2 
ON s1.s_id = s2.s_id
GROUP BY s_id;

第三步：筛选第二步中学生的课程总数为3的s_id 和 课程数

SELECT s1.*,COUNT(s2.`c_id`) c_num  FROM student s1 
JOIN score s2 ON s1.s_id = s2.s_id
GROUP BY s_id HAVING c_num = (
SELECT COUNT(c_id) FROM score WHERE s_id = '01')

第四步：对第三步查询结果的学生的课程号与01号学生的课程号进行比对
将第三步的表与score关联起来

SELECT a.s_id,b.c_id FROM (
SELECT s1.*,COUNT(s2.c_id) c_num 
FROM student s1 JOIN score s2 
ON s1.s_id = s2.s_id
GROUP BY s_id HAVING c_num = 
(SELECT COUNT(c_id) FROM score WHERE s_id = '01'))a 
JOIN score b 
ON a.s_id = b.s_id
WHERE b.c_id IN (SELECT c_id FROM score WHERE s_id = '01');

第五步：去除掉01号学生的信息

SELECT DISTINCT student.* FROM student 
JOIN 
(
SELECT a.s_id,b.c_id FROM (
SELECT s1.*,COUNT(s2.c_id) c_num FROM student s1 JOIN score s2 ON s1.s_id = s2.s_id
GROUP BY s_id HAVING c_num = (SELECT COUNT(c_id) FROM score WHERE s_id = '01'))a 
JOIN score b 
ON a.s_id = b.s_id
WHERE b.c_id IN (SELECT c_id FROM score WHERE s_id = '01')
) c 
ON student.s_id = c.s_id
WHERE student.s_id !='01';

在这里插入图片描述

  • 14、查询没学过"张雪峰"老师讲授的任一门课程的学生姓名

第一步：查询张雪峰老师的课程号

SELECT c.c_id FROM teacher t JOIN course c 
ON t.t_id = c.t_id
WHERE t.t_name = '张雪峰'

第二步：查询所有学生中学过张雪峰课程的学生编号

SELECT s1.s_id FROM student s1 JOIN score s2 
ON s1.s_id = s2.s_id
WHERE s2.c_id IN (
SELECT c.c_id FROM teacher t JOIN course c 
ON t.t_id = c.t_id
WHERE t.t_name = '张雪峰'
)

第三步：取反操作

SELECT s_name FROM student WHERE s_id NOT IN(
SELECT s1.s_id FROM student s1 JOIN score s2 
ON s1.s_id = s2.s_id
WHERE s2.c_id IN (
SELECT c.c_id FROM teacher t JOIN course c 
ON t.t_id = c.t_id
WHERE t.t_name = '张雪峰'))

在这里插入图片描述

  • 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

第一步：查询除成绩小于60的学生信息

SELECT s2.* FROM student s1 
JOIN score s2 ON s1.s_id = s2.s_id 
GROUP BY s1.s_id,s2.c_id 
HAVING s2.s_score<60;

第二步：查询小于60分数的s_id和课程个数

SELECT	s3.s_id, COUNT(s3.c_id) AS cou_cid FROM (
SELECT s2.* FROM student s1 
JOIN score s2 ON s1.s_id = s2.s_id 
GROUP BY s1.s_id,s2.c_id 
HAVING s2.s_score<60) s3
GROUP BY s3.s_id HAVING cou_cid>=2

第三步：查询第二步的s_id

SELECT s6.s_id FROM (
SELECT	s3.s_id, COUNT(s3.c_id) AS cou_cid FROM (
SELECT s2.* FROM student s1 JOIN score s2 
ON s1.s_id = s2.s_id GROUP BY s1.s_id,s2.c_id HAVING s2.s_score<60) s3
GROUP BY s3.s_id HAVING cou_cid>=2)s6

第四步：查询在第三步的s_id的学生的学号 姓名 平均成绩

SELECT s4.s_id,s4.s_name,ROUND(AVG(s5.s_score),0) AS avg_score 
FROM student s4 LEFT JOIN score s5 ON s4.s_id = s5.s_id 
GROUP BY s4.s_id HAVING s4.s_id IN(
SELECT s6.s_id FROM (
SELECT	s3.s_id, COUNT(s3.c_id) AS cou_cid FROM (
SELECT s2.* FROM student s1 JOIN score s2 ON s1.s_id = s2.s_id
GROUP BY s1.s_id,s2.c_id HAVING s2.s_score<60) s3
GROUP BY s3.s_id HAVING cou_cid>=2)s6)

简化写法

SELECT a.s_id,a.s_name,ROUND(AVG(b.s_score)) FROMstudent a
LEFT JOIN score b ON a.s_id = b.s_id
WHERE a.s_id IN(
SELECT s_id FROM score WHERE s_score<60 
GROUP BY s_id HAVING COUNT(1)>=2)
GROUP BY a.s_id,a.s_name

在这里插入图片描述

  • 16、检索"01"课程分数小于60，按分数降序排列的学生信息

第一步：查询01课程小于60的分数

SELECT s_id,s_score FROM score WHERE c_id = '01' AND s_score<60;

第二步：降序排序

SELECT s1.*,s2.s_score FROM student s1 JOIN (
SELECT s_id,s_score FROM score WHERE c_id = '01' AND s_score<60
)s2 ON s1.s_id=s2.s_id ORDER BY s2.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、查询1990年出生的学生名单
  SELECT s_name FROM student WHERE s_birth LIKE '1990%'

在这里插入图片描述

  • 19、查询不同老师所教不同课程平均分从高到低显示
SELECT a.t_id,c.t_name,a.c_id,ROUND(AVG(s_score),2) AS avg_score 
FROM course a
LEFT JOIN score b ON a.c_id=b.c_id 
LEFT JOIN teacher c ON a.t_id=c.t_id
GROUP BY a.c_id,a.t_id,c.t_name
ORDER BY avg_score DESC;

在这里插入图片描述

  • 20、查询各科成绩前三名的记录
SELECT a.s_id,a.c_id,a.s_score 
FROM score a 
LEFT JOIN score b 
ON a.c_id = b.c_id AND a.s_score<b.s_score 
GROUP BY a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3 
ORDER BY a.c_id,a.s_score DESC

在这里插入图片描述

步骤：
1.选出b表比a表成绩大的所有组
2.选出比当前id成绩大的 小于三个的

2.3 SQL 21-30

  • 21、查询每门课程被选修的学生数
思路：根据c_id分组，求s_id的个数

select c_id,count(s_id) from score a GROUP BY c_id

在这里插入图片描述

  • 22、查询出只有两门课程的全部学生的学号和姓名

第一步：分组查询课程数=2的学生id

SELECT s_id, COUNT(c_id) AS cou_cid 
FROM score 
GROUP BY s_id 
HAVING cou_cid = 2;

第二步：根据学生id、查询学生信息

SELECT s1.s_id,s1.s_name 
FROM student s1 JOIN
(SELECT s_id, COUNT(c_id) AS cou_cid FROM score GROUP BY s_id HAVING cou_cid = 2)s2
ON s1.s_id = s2.s_id;

在这里插入图片描述

  • 23、查询男生、女生人数
SELECT s_sex,COUNT(s_sex) AS 人数  FROM student GROUP BY s_sex;

在这里插入图片描述

  • 24、查询名字中含有"风"字的学生信息
SELECT * FROM student WHERE s_name LIKE '%风%';

在这里插入图片描述

  • 25、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
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

在这里插入图片描述

  • 26、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

第一步：通过sid分组查询学生的>=85的平均成绩

SELECT s_id ,ROUND(AVG(s_score),0) AS avg_score 
FROM score 
GROUP BY s_id 
HAVING avg_score>=85;

第二步：关联student表查询学生姓名

SELECT s1.s_id ,s2.s_name ,ROUND(AVG(s1.s_score),0) AS avg_score 
FROM score s1 
JOIN student s2 
ON s1.s_id = s2.s_id 
GROUP BY s_id 
HAVING avg_score>=85;

在这里插入图片描述

  • 27、查询课程名称为"数学"，且分数低于60的学生姓名和分数

第一步：查询所有学生的数学成绩，筛选<60分的s_id和分数:course score

SELECT s.s_id,s.s_score 
FROM score s 
JOIN course c 
ON s.c_id = c.c_id 
WHERE c.c_name = '数学' AND s.s_score < 60;

第二步：关联student表查询学生姓名

SELECT s.s_id,s2.s_name,s.s_score
FROM score s 
JOIN course c 
ON s.c_id = c.c_id 
JOIN student s2
ON s.s_id = s2.s_id
WHERE c.c_name = '数学' 
AND s.s_score < 60;

在这里插入图片描述

  • 28、查询所有学生的课程及分数情况；
SELECT s.s_id,
(SELECT s_score FROM score WHERE s_id=s.s_id AND c_id='01') AS 语文,
(SELECT s_score FROM score WHERE s_id=s.s_id AND c_id='02') AS 数学,
(SELECT s_score FROM score WHERE s_id=s.s_id AND c_id='03') AS 英语
FROM score s GROUP BY s.s_id ;

在这里插入图片描述

  • 29、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

第一步：先查询70以上的课程名称和分数

SELECT s.s_id,c.c_name,s.s_score 
FROM score s 
JOIN course c 
ON s.c_id = c.c_id 
HAVING s.s_score>=70;

第二步：关联student查询学生姓名

SELECT s.s_id,s2.s_name,c.c_name,s.s_score
FROM score s 
JOIN course c 
ON s.c_id = c.c_id 
JOIN student s2
ON s.s_id = s2.s_id
WHERE s.s_score>=70;

在这里插入图片描述

  • 30、查询不及格的课程的学生id、姓名，课程号，课程名，分数

第一步：先查询不集合的s_id c_id

SELECT * FROM score WHERE s_score<60;

第二步：关联course，查询课程名

SELECT s.s_id,c.c_id,c.c_name,s.s_score 
FROM score s ,course c 
WHERE s.c_id = c.c_id AND s.s_score<60;

第三步：关联student，查询学生姓名

SELECT s.s_id,s2.s_name,c.c_id,c.c_name,s.s_score 
FROM score s ,course c,student s2 
WHERE s.c_id = c.c_id 
AND s.s_id = s2.s_id
AND s.s_score<60;

在这里插入图片描述

2.4 SQL 31-40

  • 31、查询课程编号为01且课程成绩在80分以上(包括80分)的学生的学号、姓名、课程号、分数

第一步：查询score的01课程，且分数>=80以上

SELECT * FROM score WHERE c_id = '01' AND s_score>=80;

第二步：关联student查询学生姓名

SELECT s1.s_id,s2.s_name,s1.c_id,s1.s_score FROM score s1,student s2 
WHERE s1.s_id = s2.s_id 
AND c_id = '01' 
AND s_score>=80;

在这里插入图片描述

  • 32、查询选修"张雪峰"老师所授课程的学生中，成绩最高的学生信息及其成绩
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='张雪峰'))

在这里插入图片描述

  • 33、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
思路：关联查询两次score表，排除课程号相同的信息,并帅选成绩相同的内容

SELECT DISTINCT s2.* 
FROM score s1,score s2
WHERE s1.c_id != s2.c_id
AND s1.s_score = s2.s_score;

在这里插入图片描述

  • 34、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT c_id,COUNT(*) AS total 
FROM score GROUP BY c_id HAVING total>5
ORDER BY total,c_id ASC

在这里插入图片描述

  • 35、检索至少选修两门课程的学生学号
SELECT s_id,COUNT(*) AS sel FROM score GROUP BY s_id HAVING sel>=2

在这里插入图片描述

  • 36、查询选修了全部课程的学生信息

第一步：计数查询课程表的课程数

SELECT COUNT(c_id) FROM course;

第二步：查询学生的课程数=第一步的计数

SELECT s_id FROM score 
GROUP BY s_id 
HAVING COUNT(c_id) = (SELECT COUNT(c_id) FROM course)

第三步：关联学生表查询学生信息

SELECT * FROM student 
WHERE s_id IN(
SELECT s_id FROM score 
GROUP BY s_id HAVING COUNT(c_id) = (SELECT COUNT(c_id) FROM course))

在这里插入图片描述

  • 37、查询本周过生日的学生
SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)

在这里插入图片描述

  • 38、查询下周过生日的学生
SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 = WEEK(s_birth)

在这里插入图片描述

  • 39、查询本月过生日的学生
SELECT * FROM student 
WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) = MONTH(s_birth)

在这里插入图片描述

  • 40、查询下月过生日的学生
SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)

在这里插入图片描述

重在分享一些基础的SQL知识，希望大家多多指教，我是小白，我在努力学习！！！

AlluzeYu
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql高级查询练习题_mysql 高级查询
weixin_29688227的博客
01-19 467
高级查询1.in的用法 select * from member where id in(1,2,3);2.模糊查询like的用法3.统计count()的用法4.去重distinct的用法5.between的用法6.分组group by的用法7.mysql分页-- 1：掌握in的用法 -- 在。。。里面-- 字段 in(数据集)-- 查询用户id为1，或者为2，或者为3的用户-- select ...
经典SQL练习题(MySQL版)
热门推荐
廖致君的博客
09-08 21万+
原文首发于简书于[2018.07.30] 网上有一篇关于SQL的经典文章，超经典SQL练习题，做完这些你的SQL就过关了，引用和分析它的人很多，于是今天复习SQL的时候找来练了练手。原作者用的是SQL Server 2008，我在这里用的是MySQL 8.0.11（二者语法差别不大），文本编辑器用的是Atom 1.28.2（不知道大家用什么，反正用Atom写SQL确实丝质顺滑）。 题目顺序...
数据库MySQL学习——内含34道MySQL练习题及答案
risroy的博客
09-25 6682
数据库MySQL 1 MySQL数据库简介 1.1 sql、DB、DBMS分别是什么，关系？ DB：DataBase 数据库 DBMS：DateBase Management System 数据库管理系统 SQL：结构化查询语言、sql语句的编译有dbms完成 DBMS负责执行sql语句，通过之心sql语句来操作DB当中的数据 1.2 什么是表？ table 是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强 行：被称为数据/记录（data） 列：被称为字段（column） 学号（
走进MySQL---练习学会如何建表，实现增查改删
wuyomhchang的博客
04-11 1042
下面我将为大家分享一个简单的小练习，对初学者很有帮助，分别包括如何建表，添加主键，插入数据，以及实现增删改查操作。 一、建表 根据要求创建一个product表，要求如下。 列的定义 列的名称 数据类型 约束 商品id product_id...
MySQL数据库操作练习题-各种操作掌握MySQL查询操作
weixin_46589442的博客
12-02 1万+
（文末有数据库代码） 1、 查询全部图书的图书号、作者、出版社和单价； select bno as ‘图书号’,author as ‘作者’,publish as ‘出版社’,price as ‘单价’ from book; 2、 查询全体图书的信息，其中单价打8折，并设置该列的别名为“打折价”； select ,price0.8 as ‘打折价’ from book; 3、 显示所有借过书的借阅者的读者号、并去掉重复行； select distinct rno from borrow ; 4、 查询所
mysql-进阶篇
weixin_59633478的博客
01-23 1379
用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。赋值# 推荐:=# 因为在mysql中它的比较运算符也是=，没有==，所以=既可以作为赋值运算符，又可以作为比较运算符。为了以示区分，推荐使用:=# 在使用的时候，如果是赋值，使用:=select 字段名 into @var_name from 表名;# 从这张表当中所查询出来的这一块的数据赋值给这个变量使用。
MySQL-老杜学习笔记
weixin_45581692的博客
03-02 2497
MySQL1. 数据库概述1.1 数据库、数据库管理系统、SQL之间的关系1.2 表的理解1.3 SQL语句的分类1.4 导入数据1.5 查看表结构1.6 MySQL常用命令2. 查询语句DQL2.1 简单查询2.2 条件查询2.3 排序2.4 分组查询2.5 总结（单表查询）2.6 查询结果去重3. 函数3.1 单行处理函数3.2 分组函数4. 连接查询笛卡尔积现象内连接外连接三张表，四张表连接子查询union合并查询结果集limit5. DDL语句5.1 建表5.2 删表5.3 快速创建表（表的复制）5
sql语句练习题（mysql版）
pikcacho_pkq的博客
01-04 4298
众所周知，学习完sql的基本语法之后，是需要一定量的练习才能更好的掌握sql语句的书写。因此，这里整理了几十道sql语句的练习题用于巩固sql语句的书写；当然这里只考虑能得到查询结果，不考虑sql语句的性能优化之类的问题。有需要的小伙伴们可以练习一下，一定会有收获的。本文所有的题目都是整理自互联网。
MySQL练习题
D2961953033的博客
11-18 95
MySQL数据库运算符小作业
七、MySQL 多表查询详解(附练习题及答案----超详细)
Amo Xiang的博客
01-12 3149
MySQL多表查询详解及练习(附答案)
（009）《第五部分-练习题2：MySQL两表联合查询》
07-16
1. MySQL 联合查询：在题目中，我们看到了两种查询方法，一种是基于子查询的方法，另一种是使用 JOIN 的方法。在 SQL 中，联接查询（JOIN）是用于从两个或更多个表中检索数据的主要方式。例如，方法一通过嵌套子查询...
MySQL常用练手题目集合 MySQL查询练习题（50题） 共18页.pdf
08-21
MySQL查询练习题旨在帮助用户熟悉和提升SQL查询技巧，涵盖了多方面的数据库操作，如联接查询、...通过解决这些练习题，用户可以深入了解和掌握SQL查询语言的核心功能，为实际数据库管理与数据分析工作打下坚实基础。
mysql练习题-sql_practice.pdf
03-12
在进行具体的IT知识点阐述之前，需要先明确一下，本文件的内容实际上并不是直接的知识点，而是有关于“mysql练习题-sql_practice.pdf”文档的简介。此简介中包含了练习题相关的数据库表描述，以及对每个表进行操作的...
（008）《第五部分-练习题1：MySQL单表查询》
07-16
在本练习中，我们主要探讨了如何在MySQL中进行单表查询，这涵盖了多个基本的SQL操作，如选择特定行、过滤数据、排序、聚合函数以及更新数据等。以下是各个练习涉及的知识点详解： 1. **创建表和插入数据**： 使用`...
57710-数据库原理及应用教程(MySQL版）-习题答案及解析.docx(1).rar
05-27
在MySQL数据库中，学习者首先需要掌握的是SQL（Structured Query Language），它是用于管理和处理关系数据库的标准语言。SQL的基本操作包括数据查询、数据插入、数据更新和数据删除，以及创建和管理数据库对象如表、...
MySQL对JSON数据类型的处理
cesske的博客
08-21 378
MySQL从5.7版本开始提供了对JSON数据类型的支持，‌使得MySQL能够直接存储和管理JSON格式的数据。‌这使得在数据库中处理JSON数据变得更为方便和高效。‌以下是一些常用的处理JSON数据的函数和操作：‌。通过这些操作和函数，‌MySQL允许你灵活地处理JSON数据，‌使得在数据库层面进行复杂的数据操作成为可能。4.‌使用JSON_EXTRACT提取JSON字段。9.‌删除JSON中的键值对。8.‌替换JSON中的值。10.‌搜索JSON数据。2.‌插入JSON数据。6.‌更新JSON数据。
MySQL创建与删除表
weixin_69053029的博客
08-21 308
表名(列名 类型,列名 类型......);
MySQL——多表操作（一）外键（3）添加外键约束的参数说明
最新发布
PAP
08-23 218
MySQL——多表操作（一）外键（3）添加外键约束的参数说明
[mysql][sql]mysql查询表大小
一名不太专业的程序员
08-21 464
表中选择特定数据库（在这个例子中是名为 'crawleroocllisten' 的数据库）的表信息。查询结果包括数据库名、表名、表中的记录数以及数据和索引所占用的空间容量（单位为MB）。这个查询通常用于数据库管理，帮助管理员了解数据库中各个表的大小和记录数，以便进行优化和维护。函数来将数据和索引长度除以1024两次（将字节转换为MB），并且保留两位小数。SQL 查询语句，用于从。
MySQL50-13-习题答案汇总1：多表连接及条件筛选技巧
MySQL50-13-习题及答案汇总1包含了多道涉及MySQL数据库的练习题目和对应的答案，旨在帮助学习者加深对MySQL数据库操作的理解和掌握。其中一道题目涉及了使用SQL语句进行数据查询和筛选的部分，通过联合查询和左连接...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值