文章目录
SQL基本操作
1 设计表操作
1.1 关系表字段
- 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 |
---|---|---|
学生编号 | 课程编号 | 课程分数 |
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知识,希望大家多多指教,我是小白,我在努力学习!!!