CREATE TABLE J_TEACHER (
tno int NOT NULL PRIMARY KEY,
tname varchar(20) NOT NULL
);
INSERT INTO J_TEACHER(tno,tname)VALUES(1,‘张老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(2,‘王老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(3,‘李老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(4,‘赵老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(5,‘刘老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(6,‘向老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(7,‘李文静’);
INSERT INTO J_TEACHER(tno,tname)VALUES(8,‘叶平’);
-------------------------------------------------------------------------------------------------------------
CREATE TABLE J_STUDENT(
sno int NOT NULL PRIMARY KEY,
sname varchar(20) NOT NULL,
sage datetime NOT NULL,
ssex char(2) NOT NULL
);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(1,‘张三’,‘1980-1-23’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(2,‘李四’,‘1982-12-12’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(3,‘张飒’,‘1981-9-9’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(4,‘莉莉’,‘1983-3-23’,‘女’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(5,‘王弼’,‘1982-6-21’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(6,‘王丽’,‘1984-10-10’,‘女’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(7,‘刘香’,‘1980-12-22’,‘女’);
-------------------------------------------------------------------------------------------------------------
CREATE TABLE J_COURSE(
cno int NOT NULL PRIMARY KEY,
cname varchar(20) NOT NULL,
tno int NOT NULL
);
insert into J_COURSE(cno,cname,tno) values(1,‘企业管理’,3);
insert into J_COURSE(cno,cname,tno) values(2,‘马克思’,1);
insert into J_COURSE(cno,cname,tno) values(3,‘UML’,2);
insert into J_COURSE(cno,cname,tno) values(4,‘数据库’,5);
insert into J_COURSE(cno,cname,tno) values(5,‘物理’,8);
-------------------------------------------------------------------------------------------------------------
CREATE TABLE J_SCORE(
sno int NOT NULL,
cno int NOT NULL,
score int NOT NULL
);
ALTER TABLE J_SCORE ADD CONSTRAINT FK_SCORE_course FOREIGN KEY(cno)
REFERENCES J_COURSE (cno);
ALTER TABLE J_SCORE ADD CONSTRAINT FK_score_student FOREIGN KEY(sno)
REFERENCES J_STUDENT (sno);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,1,80);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,2,86);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,3,83);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,4,89);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,1,50);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,2,36);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,3,43);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,4,59);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,1,50);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,2,96);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,3,73);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,4,69);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,1,90);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,2,36);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,3,88);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,4,99);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,1,90);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,2,96);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,3,98);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,4,99);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,1,70);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,2,66);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,3,58);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,4,79);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,1,80);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,2,76);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,3,68);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,4,59);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,5,89);
select distinct a.sno,a.sname
from j_student a ,j_score b
where a.sno=b.sno and b.score is not null
-------------------------------------------------------
sno sname
1 张三
2 李四
3 张飒
4 莉莉
5 王弼
6 王丽
7 刘香
-------------------------------------------------------------------------------------------------------------
select a.sno,a.sname
from j_student a ,j_score b
where a.sno=b.sno and b.score is not null
group by a.sno
-------------------------------------------------------
1 张三
2 李四
3 张飒
4 莉莉
5 王弼
6 王丽
7 刘香
2.4 两个not in 等同 两个in
select a.sname
from j_student a
where a.sno not in (
select b.sno
from j_score b
where b.cno not in (
select c.cno
from j_course c
where c.tno not in (
select d.tno
from j_teacher d
where d.tname='李老师')))
-------------------------------------------------------------------------------------------------------------
select a.sname
from j_student a
where a.sno not in (
select b.sno
from j_score b
where b.cno in (
select c.cno
from j_course c
where c.tno in (
select d.tno
from j_teacher d
where d.tname='李老师')))
2.5 limit m,n 易错点
limit m,n 表示从第m+1条记录开始，共选n条记录，如limit 1,2 选的是第2、3条记录（很容易误以为是选1、2条记录）
limit n 表示选择前n条记录，是limit 0,n 的省略
select a.cno, a.sno, a.score
from j_score a
group by a.cno
-------------------------------------------------------
1055 - Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test01.b.cno'
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by
SQL查询中使用了GROUP BY子句进行分组，但SELECT列表中包含了一列（在这里是cno），它不是GROUP BY子句中列的一部分，
并且该列没有进行聚合操作。这与MySQL的SQL模式 only_full_group_by 不允许不在GROUP BY子句中的列出现在SELECT列表中。
select中除了group by这列外，其他列不能出现，但可以以【聚合函数】的方式出现
select 数字A group by 数字A
SELECT 列名, 聚合函数(列名) FROM 表名 WHERE 列 运算符 值 GROUP BY 列名
2 3 1
-------------------------------------------------------
这个错误是由于在使用 GROUP BY 子句时，SELECT 列中包含了非聚合的列，而且这些非聚合的列不在 GROUP BY 子句中。
select count(a.sno), min(a.score), max(a.score), avg(a.score), sum(a.score)
from j_score a
-------------------------------------------------------
count(a.sno), min(a.score), max(a.score), avg(a.score), sum(a.score)
29 36 99 74.3103 2155
查询出只选修两门课程的学生学号和姓名
select sno, sname from student
where sno in (select sno from sc group by sno having count(cno)=2);
查询没有学全所有课程的同学的信息
select * from student
where sno not in (select sno from sc group by sno having count(cno)=(select count(*) from course));
查询学全了所有课程的学生信息
select * from student
where sno in (select sno from sc group by sno having count(cno)=(select count(*) from course));
查询所有课程成绩均小于60分的学号、姓名
select sno,sname
from student where sno in (select sno from sc group by sno having max(score)<60);
2.10 having 筛选结果
select a.sno
from j_score a
GROUP BY a.sno
having count(*)>=5
-------------------------------------------------------
sno
7
2.11 count(1), count(任意列)
select count(*), count(1), count(a.sno), count(a.cno), count(a.score)
from j_score a
-------------------------------------------------------
count(*) count(1) count(a.sno) count(a.cno) count(a.score)
29 29 29 29 29
2.12 order by 列名，select 列名
先 select 列名，后 order by 列名
select cno, avg(score) as avg_score from sc
group by cno
order by avg_score desc, cno asc;
-------------------------------------------------------
cno avg_score
02 72.66667
03 68.50000
01 64.50000
3 单表、多表
3.1 查询姓“李”的学生的个数
第1种：like
-------------------------------------------------------------------------------------------------------------
select a.*
from j_student as a
where a.sname like '李%'
-------------------------------------------------------
sno sname sage ssex
2 李四 1982-12-12 00:00:00 男
-------------------------------------------------------------------------------------------------------------
select COUNT(a.sname) as '个数'
from j_student as a
where a.sname like '李%'
-------------------------------------------------------
个数
1
-------------------------------------------------------------------------------------------------------------
select '李姓', COUNT(a.sname) as '个数'
from j_student as a
where a.sname like '李%'
-------------------------------------------------------
李姓 个数
李姓 1
第2种：正则表达式
SELECT COUNT(sname) as '个数'
FROM j_student
WHERE sname REGEXP '^李';
第3种：LEFT函数
SELECT COUNT(sname) as '个数'
FROM j_student
WHERE LEFT(sname, 1) = '李';
第4种：使用SUBSTRING函数
SELECT COUNT(sname) as '个数'
FROM j_student
WHERE SUBSTRING(sname, 1, 1) = '李';
3.2 查询各科成绩最高分和最低分，显示：课程号，最高分，最低分
select a.cno, a.sno, a.score
from j_score a
group by a.cno
-------------------------------------------------------
1055 - Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test01.b.cno'
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by
SQL查询中使用了GROUP BY子句进行分组，但SELECT列表中包含了一列（在这里是cno），它不是GROUP BY子句中列的一部分，
并且该列没有进行聚合操作。这与MySQL的SQL模式 only_full_group_by 不允许不在GROUP BY子句中的列出现在SELECT列表中。
select中除了group by这列外，其他列不能出现，但可以以【聚合函数】的方式出现
select 数字A group by 数字A
SELECT 列名, 聚合函数(列名) FROM 表名 WHERE 列 运算符 值 GROUP BY 列名
2 3 1
-------------------------------------------------------------------------------------------------------------
select count(a.sno), min(a.score), max(a.score), avg(a.score), sum(a.score)
from j_score a
-------------------------------------------------------
count(a.sno), min(a.score), max(a.score), avg(a.score), sum(a.score)
29 36 99 74.3103 2155
-------------------------------------------------------------------------------------------------------------
select a.cno, count(a.cno), count(a.sno), count(a.score)
from j_score a
group by a.cno
-------------------------------------------------------
cno count(a.cno) count(a.sno) count(a.score)
1 7 7 7
2 7 7 7
3 7 7 7
4 7 7 7
5 1 1 1
-------------------------------------------------------------------------------------------------------------
select a.cno, count(a.cno), count(a.sno), count(a.score), sum(a.score), avg(a.score), min(a.score), max(a.score)
from j_score a
group by a.cno
-------------------------------------------------------
cno count(a.cno) count(a.sno) count(a.score) sum(a.score) avg(a.score) min(a.score) max(a.score)
1 7 7 7 510 72.8571 50 90
2 7 7 7 492 70.2857 36 96
3 7 7 7 511 73.0000 43 98
4 7 7 7 553 79.0000 59 99
5 1 1 1 89 89.0000 89 89
-------------------------------------------------------------------------------------------------------------
select a.cno,max(a.score) as '最高分', min(a.score) as '最低分'
from j_score a
group by a.cno
-------------------------------------------------------
cno 最高分 最低分
1 90 50
2 96 36
3 98 43
4 99 59
5 89 89
3.3 查询 每门课程被选修 的 学生数
每门课程被选修
select a.cname
from j_course a INNER JOIN j_score b
on a.cno=b.cno
-------------------------------------------------------
cname
企业管理
企业管理
企业管理
企业管理
企业管理
企业管理
企业管理
马克思
马克思
马克思
马克思
马克思
马克思
马克思
UML
UML
UML
UML
UML
UML
UML
数据库
数据库
数据库
数据库
数据库
数据库
数据库
物理
-------------------------------------------------------------------------------------------------------------`
select a.cname, count(b.sno)
from j_course a,j_score b
where b.score is not NULL and a.cno=b.cno
group by a.cno
-------------------------------------------------------
cname count(b.sno)
企业管理 7
马克思 7
UML 7
数据库 7
物理 1
3.4 查询出 只选修了一门课程 的 全部学生的学号和姓名
select a.sno,a.sname
from j_student a ,j_score b
where a.sno=b.sno and b.score is not null
-------------------------------------------------------
sno sname
1 张三
1 张三
1 张三
1 张三
2 李四
2 李四
2 李四
2 李四
3 张飒
3 张飒
3 张飒
3 张飒
4 莉莉
4 莉莉
4 莉莉
4 莉莉
5 王弼
5 王弼
5 王弼
5 王弼
6 王丽
6 王丽
6 王丽
6 王丽
7 刘香
7 刘香
7 刘香
7 刘香
7 刘香
-------------------------------------------------------------------------------------------------------------
select a.sno,a.sname
from j_student a ,j_score b
where a.sno=b.sno and b.score is not null
group by a.sno
-------------------------------------------------------
sno sname
1 张三
2 李四
3 张飒
4 莉莉
5 王弼
6 王丽
7 刘香
-------------------------------------------------------------------------------------------------------------
select a.sno,a.sname
from j_student a ,j_score b
where a.sno=b.sno and b.score is not null
group by a.sno
having count(b.cno)=1
-------------------------------------------------------
sno sname
空 空
3.5 查询不同课程成绩相同的学生的学号、课程号、学生成绩
select a.sno,a.cno,a.score
from j_score a join j_score b
where a.score=b.score and a.cno!=b.cno
-------------------------------------------------------
sno cno score
7 5 89
1 4 89
3.6 查询 每门课程成绩最好的前两名 的学生ID
select a.sno,a.cno,a.score
from j_score a
where (
select count(*)
from j_score b
where a.cno=b.cno and a.score<=b.score)<=2
-------------------------------------------------------
sno cno score
1 2 86
1 3 83
1 4 89
3 2 96
4 1 90
4 3 88
4 4 99
5 1 90
5 2 96
5 3 98
5 4 99
7 5 89
3.7 检索至少选修了5门课程的学生信息
select a.sno
from j_score a
GROUP BY a.sno
having count(*)>=5
-------------------------------------------------------
sno
7
-------------------------------------------------------------------------------------------------------------
select *
from j_student b
where b.sno in (
select a.sno
from j_score a
GROUP BY a.sno
having count(*)>=5
)
-------------------------------------------------------
sno sname sage ssex
7 刘香 1980-12-22 00:00:00 女
3.8 查询两门以上不及格课程的同学的学号及其平均成绩
select a.sno, a.cno, a.score
from j_score a
where a.score<60
-------------------------------------------------------
sno cno score
2 1 50
2 2 36
2 3 43
2 4 59
3 1 50
4 2 36
6 3 58
7 4 59
-------------------------------------------------------------------------------------------------------------
select a.sno, count(*)
from j_score a
where a.score<60
group by a.sno
-------------------------------------------------------
sno count(*)
2 4
3 1
4 1
6 1
7 1
-------------------------------------------------------------------------------------------------------------
select a.sno,round(avg(a.score),3) as'平均成绩'
from j_score a
where a.score<60
group by sno
having count(*)>2
-------------------------------------------------------
sno 平均成绩
2 47.000
3.9 查询选修学生最多的老师
select count(cno) max
from j_score
group by cno
order by count(cno) desc
-------------------------------------------------------
max
7
7
7
7
1
-------------------------------------------------------------------------------------------------------------
select d.max from (
select count(cno) max
from j_score
group by cno
order by count(cno) desc
limit 0,1) d
-------------------------------------------------------
max
7
-------------------------------------------------------------------------------------------------------------
select a.cno,c.tname,count(a.cno) as '选修人数'
from j_course a,j_score b,j_teacher c
where a.cno=b.cno and a.tno=c.tno
group by a.cno
having count(a.cno) =
(
select d.max from (
select count(cno) max
from j_score
group by cno
order by count(cno) desc
limit 0,1)d
)
order by count(a.cno) desc
-------------------------------------------------------
cno tname 选修人数
3 王老师 7
1 李老师 7
4 刘老师 7
2 张老师 7
4 内连接
4.1 查询所有同学的学号、姓名、选课数、总成绩
第1种：根据a.sno分组，再对分组结果使用count(b.cno)as'选课数', sum(b.score)as'总成绩'
-------------------------------------------------------------------------------------------------------------
select a.*, b.*
from j_student as a join j_score as b
on a.sno=b.sno
-------------------------------------------------------
sno sname sage ssex sno(1) cno score
1 张三 1980-01-23 00:00:00 男 1 1 80
1 张三 1980-01-23 00:00:00 男 1 2 86
1 张三 1980-01-23 00:00:00 男 1 3 83
1 张三 1980-01-23 00:00:00 男 1 4 89
2 李四 1982-12-12 00:00:00 男 2 1 50
2 李四 1982-12-12 00:00:00 男 2 2 36
2 李四 1982-12-12 00:00:00 男 2 3 43
2 李四 1982-12-12 00:00:00 男 2 4 59
3 张飒 1981-09-09 00:00:00 男 3 1 50
3 张飒 1981-09-09 00:00:00 男 3 2 96
3 张飒 1981-09-09 00:00:00 男 3 3 73
3 张飒 1981-09-09 00:00:00 男 3 4 69
4 莉莉 1983-03-23 00:00:00 女 4 1 90
4 莉莉 1983-03-23 00:00:00 女 4 2 36
4 莉莉 1983-03-23 00:00:00 女 4 3 88
4 莉莉 1983-03-23 00:00:00 女 4 4 99
5 王弼 1982-06-21 00:00:00 男 5 1 90
5 王弼 1982-06-21 00:00:00 男 5 2 96
5 王弼 1982-06-21 00:00:00 男 5 3 98
5 王弼 1982-06-21 00:00:00 男 5 4 99
6 王丽 1984-10-10 00:00:00 女 6 1 70
6 王丽 1984-10-10 00:00:00 女 6 2 66
6 王丽 1984-10-10 00:00:00 女 6 3 58
6 王丽 1984-10-10 00:00:00 女 6 4 79
7 刘香 1980-12-22 00:00:00 女 7 1 80
7 刘香 1980-12-22 00:00:00 女 7 2 76
7 刘香 1980-12-22 00:00:00 女 7 3 68
7 刘香 1980-12-22 00:00:00 女 7 4 59
7 刘香 1980-12-22 00:00:00 女 7 5 89
-------------------------------------------------------------------------------------------------------------
select a.*, b.*
from j_student as a join j_score as b
on a.sno=b.sno
group by a.sno
-------------------------------------------------------
1055 - Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test01.b.cno'
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by
SQL查询中使用了GROUP BY子句进行分组，但SELECT列表中包含了一列（在这里是cno），它不是GROUP BY子句中列的一部分，
并且该列没有进行聚合操作。这与MySQL的SQL模式 only_full_group_by 不允许不在GROUP BY子句中的列出现在SELECT列表中。
select中除了group by这列外，其他列不能出现，但可以以【聚合函数】的方式出现
select 数字A group by 数字A
SELECT 列名, 聚合函数(列名) FROM 表名 WHERE 列 运算符 值 GROUP BY 列名
2 3 1
-------------------------------------------------------------------------------------------------------------
select a.sno, a.sname, count(b.cno)as'选课数', sum(b.score)as'总成绩'
from j_student as a join j_score as b
on a.sno=b.sno
group by a.sno
-------------------------------------------------------
sno sname 选课数 总成绩
1 张三 4 338
2 李四 4 188
3 张飒 4 288
4 莉莉 4 313
5 王弼 4 383
6 王丽 4 273
7 刘香 5 372
第2种：子查询
-------------------------------------------------------------------------------------------------------------
SELECT a.sno, a.sname,
(SELECT COUNT(cno) FROM j_score b WHERE b.sno = a.sno) AS '选课数',
(SELECT SUM(score) FROM j_score b WHERE b.sno = a.sno) AS '总成绩'
FROM j_student a;
-------------------------------------------------------
sno sname 选课数 总成绩
1 张三 4 338
2 李四 4 188
3 张飒 4 288
4 莉莉 4 313
5 王弼 4 383
6 王丽 4 273
7 刘香 5 372
第2种：EXISTS子查询
SELECT sno
FROM j_score a
WHERE cno = 1 AND EXISTS (SELECT 1 FROM j_score b WHERE a.sno = b.sno AND b.cno = 2 AND a.score > b.score);
-------------------------------------------------------------------------------------------------------------
sno
2
4
6
7
第3种：子查询
SELECT DISTINCT a.sno
FROM j_score a
WHERE a.cno = 1 AND a.score > (SELECT MAX(b.score) FROM j_score b WHERE b.cno = 2 AND a.sno = b.sno);
-------------------------------------------------------------------------------------------------------------
sno
2
4
6
7
5.2 查询同名同性学生名单，并统计同名人数
同名同性学生名单
select a.sno,a.sname,a.sage,a.ssex,b.sno,b.sname,b.sage,b.ssex
from j_student a join j_student b
on a.sno=b.sno
where a.sname=b.sname and a.ssex=b.ssex
group by a.sno
-------------------------------------------------------
sno sname sage sno(1) sname(1) sage(1)
1 张三 1980-01-23 00:00:00 男 1 张三 1980-01-23 00:00:00 男
2 李四 1982-12-12 00:00:00 男 2 李四 1982-12-12 00:00:00 男
3 张飒 1981-09-09 00:00:00 男 3 张飒 1981-09-09 00:00:00 男
4 莉莉 1983-03-23 00:00:00 女 4 莉莉 1983-03-23 00:00:00 女
5 王弼 1982-06-21 00:00:00 男 5 王弼 1982-06-21 00:00:00 男
6 王丽 1984-10-10 00:00:00 女 6 王丽 1984-10-10 00:00:00 女
7 刘香 1980-12-22 00:00:00 女 7 刘香 1980-12-22 00:00:00 女
8 张三 2023-10-23 00:00:00 男 8 张三 2023-10-23 00:00:00 男
-------------------------------------------------------------------------------------------------------------
select t.sname, t.ssex, count(t.sname)
from (select a.sno,a.sname,a.sage,a.ssex
from j_student a join j_student b
on a.sno=b.sno
where a.sname=b.sname and a.ssex=b.ssex
group by a.sno)t
group by t.sname, t.ssex
having count(t.sname) > 1
-------------------------------------------------------
sname ssex count(t.sname)
张三 男 2
6 分组+having+聚合函数
6.1 查询平均成绩大于60分的同学的学号和平均成绩
第1种：根据a.sno分组，再对分组结果avg(a.score)>60
-------------------------------------------------------------------------------------------------------------
SELECT a.sno,AVG(a.score)as '平均成绩'
from j_score as a
group by a.sno
-------------------------------------------------------
sno 平均成绩
1 84.5000
2 47.0000
3 72.0000
4 78.2500
5 95.7500
6 68.2500
7 74.4000
-------------------------------------------------------------------------------------------------------------
SELECT a.sno, AVG(a.score) as '平均成绩'
from j_score as a
group by a.sno
having avg(a.score)>60
-------------------------------------------------------
sno 平均成绩
1 84.5000
3 72.0000
4 78.2500
5 95.7500
6 68.2500
7 74.4000
第2种：子查询
-------------------------------------------------------------------------------------------------------------
SELECT sno, AVG(score) as avg_score
FROM j_score
GROUP BY sno
-------------------------------------------------------
sno avg_score
1 84.5000
2 47.0000
3 72.0000
4 78.2500
5 95.7500
6 68.2500
7 74.4000
-------------------------------------------------------------------------------------------------------------
SELECT sno, avg_score as '平均成绩'
FROM (
SELECT sno, AVG(score) as avg_score
FROM j_score
GROUP BY sno
) AS subquery
WHERE avg_score > 60;
-------------------------------------------------------
sno 平均成绩
1 84.5000
3 72.0000
4 78.2500
5 95.7500
6 68.2500
7 74.4000
6.2 查询平均成绩大于80分的课程的编号和平均成绩
第1种：根据a.sno分组，再对分组结果avg(a.score)>60
-------------------------------------------------------------------------------------------------------------
SELECT a.cno,AVG(a.score)as '平均成绩'
from j_score as a
group by a.cno
--------------------------------------------------------
cno 平均成绩
1 72.8571
2 70.2857
3 73.0000
4 79.0000
5 89.0000
-------------------------------------------------------------------------------------------------------------
SELECT a.cno,AVG(a.score)as '平均成绩'
from j_score as a
group by a.cno
having avg(a.score)>80
--------------------------------------------------------
cno 平均成绩
5 89.0000
第2种：子查询
-------------------------------------------------------------------------------------------------------------
SELECT cno, AVG(score) as avg_score
FROM j_score
GROUP BY cno
-------------------------------------------------------
cno avg_score
1 72.8571
2 70.2857
3 73.0000
4 79.0000
5 89.0000
-------------------------------------------------------------------------------------------------------------
SELECT cno, avg_score as '平均成绩'
FROM (
SELECT cno, AVG(score) as avg_score
FROM j_score
GROUP BY cno
) AS subquery
WHERE avg_score > 80;
-------------------------------------------------------
sno 平均成绩
5 89.0000
7 子查询
7.1 not in：查询没学过“李老师”老师课的同学的学号、姓名
select s.*, c.*, t.*
from j_score as s,j_course as c,j_teacher as t
where s.cno=c.cno and c.tno=t.tno
-------------------------------------------------------
sno cno score cname tname
1 1 80 企业管理 李老师
2 1 50 企业管理 李老师
3 1 50 企业管理 李老师
4 1 90 企业管理 李老师
5 1 90 企业管理 李老师
6 1 70 企业管理 李老师
7 1 80 企业管理 李老师
1 2 86 马克思 张老师
2 2 36 马克思 张老师
3 2 96 马克思 张老师
4 2 36 马克思 张老师
5 2 96 马克思 张老师
6 2 66 马克思 张老师
7 2 76 马克思 张老师
1 3 83 UML 王老师
2 3 43 UML 王老师
3 3 73 UML 王老师
4 3 88 UML 王老师
5 3 98 UML 王老师
6 3 58 UML 王老师
7 3 68 UML 王老师
1 4 89 数据库 刘老师
2 4 59 数据库 刘老师
3 4 69 数据库 刘老师
4 4 99 数据库 刘老师
5 4 99 数据库 刘老师
6 4 79 数据库 刘老师
7 4 59 数据库 刘老师
-------------------------------------------------------------------------------------------------------------
select s.*, c.cname, t.tname
from j_score as s,j_course as c,j_teacher as t
where s.cno=c.cno and c.tno=t.tno and t.tname = '赵老师'
-------------------------------------------------------
sno cno score cname tname
空 空 空
-------------------------------------------------------------------------------------------------------------
SELECT a.sno,a.sname
from j_student as a
where a.sno not in (
select s.sno
from j_score as s,j_course as c,j_teacher as t
where s.cno=c.cno and c.tno=t.tno and t.tname='赵老师')
-------------------------------------------------------
sno sname
1 张三
2 李四
3 张飒
4 莉莉
5 王弼
6 王丽
7 刘香
7.2 not in：查询没学过“李”老师讲授的任一门课程的学生姓名
select d.tno
from j_teacher d
where d.tname='李老师'
-------------------------------------------------------
tno
3
-------------------------------------------------------------------------------------------------------------
select c.cno
from j_course c
where c.tno in (
select d.tno
from j_teacher d
where d.tname='李老师')
-------------------------------------------------------
cno
1
-------------------------------------------------------------------------------------------------------------
select b.sno
from j_score b
where b.cno in (
select c.cno
from j_course c
where c.tno in (
select d.tno
from j_teacher d
where d.tname='李老师'))
-------------------------------------------------------
sno
1
2
3
4
5
6
7
-------------------------------------------------------------------------------------------------------------
select a.sname
from j_student a
where a.sno not in (
select b.sno
from j_score b
where b.cno in (
select c.cno
from j_course c
where c.tno in (
select d.tno
from j_teacher d
where d.tname='李老师')))
-------------------------------------------------------
sname
空
select d.tno
from j_teacher as d
where d.tname='李老师'
-------------------------------------------------------
tno
3
-------------------------------------------------------------------------------------------------------------
SELECT c.cno
from j_course as c
where c.tno in (
select d.tno
from j_teacher as d
where d.tname='李老师')
-------------------------------------------------------
cno
1
-------------------------------------------------------------------------------------------------------------
SELECT b.sno
from j_score as b
where b.cno in (
SELECT c.cno
from j_course as c
where c.tno in (
select d.tno
from j_teacher as d
where d.tname='李老师'))
-------------------------------------------------------
sno
1
-------------------------------------------------------------------------------------------------------------
select a.sno,a.sname
from j_student as a
where a.sno in (
SELECT b.sno
from j_score as b
where b.cno in (
SELECT c.cno
from j_course as c
where c.tno in (
select d.tno
from j_teacher as d
where d.tname='李老师')))
-------------------------------------------------------
sno sname
1 张三
2 李四
3 张飒
4 莉莉
5 王弼
6 王丽
7 刘香
select b.sno, b.cno, b.score
from j_score as b
-------------------------------------------------------
sno cno score
1 1 80
1 2 86
1 3 83
1 4 89
2 1 50
2 2 36
2 3 43
2 4 59
3 1 50
3 2 96
3 3 73
3 4 69
4 1 90
4 2 36
4 3 88
4 4 99
5 1 90
5 2 96
5 3 98
5 4 99
6 1 70
6 2 66
6 3 58
6 4 79
7 1 80
7 2 76
7 3 68
7 4 59
7 5 89
-------------------------------------------------------------------------------------------------------------
select b.sno
from j_score as b
group by b.sno
-------------------------------------------------------
sno
1
2
3
4
5
6
7
-------------------------------------------------------------------------------------------------------------
select b.sno
from j_score as b
group by b.sno
having max(b.score)<60
-------------------------------------------------------
sno
2
-------------------------------------------------------------------------------------------------------------
select a.sno,a.sname
from j_student as a
where a.sno in (
select b.sno
from j_score as b
group by b.sno
having max(b.score)<60)
-------------------------------------------------------
sno sname
2 李四
7.7 in：查询所有课程成绩大于60分的同学的学号、姓名
select b.sno, b.cno, b.score
from j_score as b
-------------------------------------------------------
sno cno score
1 1 80
1 2 86
1 3 83
1 4 89
2 1 50
2 2 36
2 3 43
2 4 59
3 1 50
3 2 96
3 3 73
3 4 69
4 1 90
4 2 36
4 3 88
4 4 99
5 1 90
5 2 96
5 3 98
5 4 99
6 1 70
6 2 66
6 3 58
6 4 79
7 1 80
7 2 76
7 3 68
7 4 59
7 5 89
-------------------------------------------------------------------------------------------------------------
select b.sno
from j_score as b
group by b.sno
-------------------------------------------------------
sno
1
2
3
4
5
6
7
-------------------------------------------------------------------------------------------------------------
select b.sno
from j_score as b
group by b.sno
having min(b.score)>60
-------------------------------------------------------
sno
1
5
-------------------------------------------------------------------------------------------------------------
select a.sno,a.sname
from j_student as a
where a.sno in (
select b.sno
from j_score as b
group by b.sno
having min(b.score)>60)
-------------------------------------------------------
sno sname
1 张三
5 王弼
7.8 in：查询至少有一门课程 与 学号为1的同学所学课程 相同的同学的学号和姓名
学号为1的同学所学课程
select c.cno from j_score c where c.sno =1
-------------------------------------------------------
cno
1
2
3
4
-------------------------------------------------------------------------------------------------------------
select a.sno ,a.sname
from j_student a ,j_score b
where a.sno=b.sno and a.sno!=1 and b.cno in
(select c.cno from j_score c where c.sno =1)
-------------------------------------------------------
sno sname
2 李四
2 李四
2 李四
2 李四
3 张飒
3 张飒
3 张飒
3 张飒
4 莉莉
4 莉莉
4 莉莉
4 莉莉
5 王弼
5 王弼
5 王弼
5 王弼
6 王丽
6 王丽
6 王丽
6 王丽
7 刘香
7 刘香
7 刘香
7 刘香
-------------------------------------------------------------------------------------------------------------
select a.sno ,a.sname
from j_student a ,j_score b
where a.sno=b.sno and a.sno!=1 and b.cno in
(select c.cno from j_score c where c.sno =1)
group by a.sno
-------------------------------------------------------
sno sname
2 李四
3 张飒
4 莉莉
5 王弼
6 王丽
7 刘香
7.9 in：查询和 2号同学学习的课程 完全相同的其他同学学号和姓名
2号同学学习的课程
select d.cno from j_score d where d.sno=2
-------------------------------------------------------
cno
1
2
3
4
-------------------------------------------------------------------------------------------------------------
2号同学学习的课程 学生学号
select c.sno from j_score c where c.cno in (
select d.cno from j_score d where d.sno=2)
-------------------------------------------------------
sno
1
1
1
1
2
2
2
2
3
3
3
3
4
4
4
4
5
5
5
5
6
6
6
6
7
7
7
7
-------------------------------------------------------------------------------------------------------------
查询和 2号同学学习的课程 有重复的其他同学学号和姓名
select a.sno, count(a.sno), a.sname
from j_student a ,j_score b
where a.sno=b.sno and a.sno!=2 and b.sno in (
select c.sno from j_score c where c.cno in (
select d.cno from j_score d where d.sno=2))
group by a.sno
-------------------------------------------------------
sno count(a.sno) sname
1 4 张三
3 4 张飒
4 4 莉莉
5 4 王弼
6 4 王丽
7 5 刘香
-------------------------------------------------------------------------------------------------------------
查询和 2号同学学习的课程 完全相同的其他同学学号和姓名
select a.sno,a.sname
from j_student a ,j_score b
where a.sno=b.sno and a.sno<>2 and b.sno in (
select c.sno from j_score c where c.cno in (
select d.cno from j_score d where d.sno=2))
group by a.sno
having count(a.sno)=(select count(d.cno) from j_score d where d.sno=2)
-------------------------------------------------------
sno sname
1 张三
3 张飒
4 莉莉
5 王弼
6 王丽
7.10 in：查询选修“张”老师所授课程的学生中，成绩最高的学生姓名及其成绩
选修“张”老师
select d.tno
from j_teacher d
where d.tname='张老师'
-------------------------------------------------------
tno
1
-------------------------------------------------------------------------------------------------------------
select c.cno
from j_course c
where c.tno in (
select d.tno
from j_teacher d
where d.tname='张老师')
-------------------------------------------------------
cno
2
-------------------------------------------------------------------------------------------------------------
select a.sname, b.cno, b.score
from j_student a,j_score b
where a.sno=b.sno and b.cno in (
select c.cno
from j_course c
where c.tno in (
select d.tno
from j_teacher d
where d.tname='张老师'))
-------------------------------------------------------
sname cno score
张三 2 86
李四 2 36
张飒 2 96
莉莉 2 36
王弼 2 96
王丽 2 66
刘香 2 76
-------------------------------------------------------------------------------------------------------------
select max(b.score)
from j_student a,j_score b
where a.sno=b.sno and b.cno in (
select c.cno
from j_course c
where c.tno in (
select d.tno
from j_teacher d
where d.tname='张老师'))
group by b.cno
-------------------------------------------------------
max(b.score)
96
-------------------------------------------------------------------------------------------------------------
select e.sname, f.score
from j_student e, j_score f
where e.sno = f.sno and f.score = (
select max(b.score)
from j_student a,j_score b
where a.sno=b.sno and b.cno in (
select c.cno
from j_course c
where c.tno in (
select d.tno
from j_teacher d
where d.tname='张老师'))
group by b.cno)
-------------------------------------------------------
sname score
张飒 96
王弼 96