1.表格结构
学生表 student（sid,sname,ssex,sbirth,sclass）
sid 学生编号，sname 学生姓名，ssex 学生性别，sbirth 出生年月，sclass班级
教师表 teacher (tid,tname,tsex,tbirth,tprof,tdepart)
tid 教师编号，tname 教师姓名，tsex性别，tbirth出生年月，tprof职称，tdepart所在部门
课程表 course (cid,cname,tid) cid 课程编号，cname 课程名称，tid 教师编号
成绩表 score (sid,cid,degree) sid 学生编号，cid 课程编号，degree 分数
2.创建表格（数据类型、主键、外键）
创建表格 [学生表：student] (sid,sname,ssex,sbirth,sclass)
create 
创建表格 [教师表：teacher] (tid,tname,tsex,tprof,tdepart)
create 
创建表格 [课程表：course] (cid,cname,tid)
create 
创建表格 [成绩表：score] (sid,cid,degree)
create 
3.添加数据
[学生表：student] 中添加数据
insert 
更改数据内容：
update student set sbirth = '1991-12-21' where sid ='102';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[教师表：teacher] 中添加数据
insert 
mysql> update teacher set tname = '张旭' where tid = '856';
update teacher set tprof = '助教' where tname = '王萍';
update teacher set tbirth='1992-05-05' where tname = '王萍';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[课程表：course] 中添加数据
insert 
[成绩表：score] 中添加数据
insert 
删除表格、更改数据内容
mysql> drop table score;
Query OK, 0 rows affected (0.02 sec)
mysql> update score set degree = 79 where sid=108;
Query OK, 1 row affected (0.01 sec)
练习视频：https://www.bilibili.com/video/BV1Vt411z7wy
数据的内容，与原题目略有不同。
4.题目练习：
1、查询学生表中所有记录。
先
想
一
下
答案：
1、查询学生表中所有记录。
select 
2、查询学生表中所有学生姓名、性别、班级。
先
想
一
下
答案：
2、查询学生表中所有学生姓名、性别、班级。
select 
3、查询教师所有的单位（即不重复的所在部门）。
先
想
一
下
答案：
3、查询教师所有的单位（即不重复的所在部门）。
select 
4、查询成绩表中成绩在60到80之间的所有记录。
先
想
一
下
答案：
4、查询成绩表中成绩在60到80之间的所有记录。
select 
或
select 
5、查询成绩表中成绩为81，86或88的记录。
先
想
一
下
答案：
5、查询成绩表中成绩为81，86或88的记录。
select 
或
select 
6、学生表中‘95031’班或性别为‘女’的同学记录。
先
想
一
下
答案：
6、学生表中‘95031’班或性别为‘女’的同学记录。
select 
7、以班级降序查询学生表所有记录。
先
想
一
下
答案：
7、以班级降序查询学生表所有记录。
select 
8、查询成绩表中以课程升序，成绩降序的所有记录。
先
想
一
下
答案：
8、查询成绩表中成绩降序，课程号升序的所有记录。
select 
9、查询‘95031’班的学生人数。
先
想
一
下
答案：
9、查询‘95031’班的学生人数。
select 
10、查询成绩表中最高分同学的学号和课程号（子查询）
先
想
一
下
答案：【基础入门第一个难点：子查询】
10、查询成绩表中最高分同学的学号和课程号（子查询）
select 
分析：
1.找到最高分成绩
select 
2.通过已找到的最高分成绩行，查询对应的学生编号和课程编号
select 
一开始写的不标准（如下），出现了错误。
select ERROR 1140 （42000）： In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column '50sqltest.score.sid'; this is incompatible with sql_mode=only_full_group_by 翻译： 在没有分组条件 GROUP BY 情况下，聚集函数的查询，如 SELECT 的列表中包含了非聚集函数的列，这是与 sql_mode=only_full_group_by 不兼容的。
「MySQL必知必会 第12章 汇总数据」
聚集函数：计算和返回个单个值的函数：avg()、count()、max()、min()、sum()。
分析1：
在没有使用分组条件 Group By 的情况下，
select 只能直接接聚集函数（单个/组合聚集函数），select不能同时接字段和聚集函数。
大概是因为(组合)聚集函数的「(每个)聚集函数会被筛选成为一列，每列汇总为单一值」。
- 查到网上有人也遇到的情况说明：关于MySQL中only_full_group_by模式的一点个人理解
select 那「MySQL必知必会中 第16章 创建高级联结」不知道能不能解决呢？
11、查询每门课的平均成绩。
先
想
一
下
答案：【基础入门第二个难点：分组 GROUP BY 】
11、查询每门课的平均成绩。
select 
分析1：
1.查询每门课，先要知道有多少门课。
select 
2.查询每门课平均分。
select 3.查询每门课平均分，合并成一条语句。
即：将每门课按照课程号进行分组，求成绩的平均分。
select 
4.完善查询
select 
分析2：
1.查询成绩表
select 
2.按照课程编号排序：
select 
3.同一个课程编号分组，查看每门课各有几个人的成绩。
select 
4.根据分组计算各科成绩平均值。
select 
验证了1.中：有多少门课cid和每门课的成绩的列表可自行count(cid)。
一开始写的不标准（如下），出现了第10题的错误。
select ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column '50sqltest.score.sid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
分析2：（补充第10题的分析1）
分组的列cid (group by cid) ：cid 对每个科目下的成绩 degree 先分组，后取平均值avg()，select 只能对已分组的列 或 聚集函数进行查询。
学生编号 sid 无法通过分组在一列中呈现（一个cid对应sid有多个），
一个科目中有多个学生成绩和学号，成绩可以聚集计算，但学号sid不能，select 不能查询。
【补充：MySQL 8.0 关于 GROUP BY 的知识点理解 】
12、查询成绩表中，至少有2名同学选修，并以3开头的，课程的平均数。
先
想
一
下
答案：【基础入门第三个难点：分组 GROUP BY 和 聚集函数 组合使用】
12、查询成绩表中，至少有2名同学选修，并以3开头的课程的平均数。
select 
分析：
1.查看成绩表中所有列项
select 
2.至少有2名同学选修的课程，即，学生号对应的课程号个数大于等于2
group 3.条件增加‘并以3开头的课程’：
group 4.课程的平均数：avg()
select 
6.再补全信息：
select 
7.补完信息：
select 13、查询分数大于70小于90的学生号。
先
想
一
下
答案：
13、查询分数大于70小于90的学生号。
select 
14、查询所有学生的学生姓名、课程编号和成绩。
先
想
一
下
答案：【基础入门第四个难点：多表查询】
14、查询所有学生的学生姓名、课程编号和成绩。
select 
分析：多表关联查询
学生姓名，在学生表中；课程编号和成绩，在成绩表中。
共同的列：学生编号。
1.从学生表中查询学生编号和学生姓名
select 2.从成绩表中查询学生编号，课程编号和成绩
select 3.两个表中，学生编号是共同的列，两表联结查询
select 
join
15、查询所有学生的学生编号、课程名和成绩。
先
想
一
下
答案：【基础入门第四个难点：多表查询】（同上一题）
15、查询所有学生的学生编号、课程名和成绩。
select 
16、查询所有学生的学生姓名、课程名和成绩。
先
想
一
下
答案：【基础入门第四个难点：三表关联查询 】
16、查询所有学生的学生姓名、课程名和成绩。
select 
分析：
公共列：学生编号和课程编号，同时查询
select 
对比查询：
学生表中的学生编号、课程表中的课程编号，
与成绩表中的学生编号和课程编号，同时查询：
select 
17、查询95031班同学每门科目的平均分。
先
想
一
下
答案：
17、查询95031班同学每门科目的平均分。
select 
分析：
1.在学生表中，筛选班级95031的学生编号
select 
2.在成绩表中，筛选班级95031的学生的学生编号对应的成绩
select 
3.以成绩分组，求平均值
select 
报错1：
select ERROR 1242 (21000): Subquery returns more than 1 row
子查询中返回结果不止一个。
错误原因：
sid = 只能接一个值，sid IN 可以接多个非连续范围的值。
重点分析：
必须先进行95031班级号的筛选 sid 后，才能通过 sid 去 score 表进行分组。先分组后，sid 由于分组，无法操作唯一对应的主键功能。
错误做法2：
select 
错误思路：
1.求每门科目平均分：以科目的课程编号进行分组，并对课程编号的成绩进行平均分计算。
group 2.查询条件为：班级是95031的同学，所在表格是学生表
where 3.组合以上条件
select 
4.补全查询信息
select 
18、查询选修‘3-105’课程的成绩高于‘109’号同学‘3-105’成绩的所有同学记录。(题目有点意义不明)
先
想
一
下
答案：
18、查询选修‘3-105’课程的成绩高于‘109’号同学‘3-105’成绩的所有同学记录。
方法1: （如果数据中，109号同学3-105科目下没有成绩）
select 
分析：
1.查询‘3-105’课程的同学成绩列表
select 
2.查询109号同学的成绩列表
select 
3.查询选修‘3-105’课程的成绩高于‘109’号同学的学生列表
select 方法2: （如果数据中，109号同学3-105科目下有成绩）
即问题是：查询所有选修‘3-105’课程的同学，其中哪些人是成绩高于109号同学的‘3-105’科目成绩，的学生记录。
select 
1.查询学号109号的同学3-105科目的成绩
select 
2.查询选择3-105科目的同学中，成绩大于109号（已选‘3-105’科目）同学的成绩的列表。
select 19、查询成绩高于学号为‘109’、课程号为‘3-105’的同学成绩的所有记录。
先
想
一
下
答案：
19、查询成绩高于学号为‘109’、课程号为‘3-105’的同学成绩的所有记录。
select 
20、查询和学号为108、101的同学同年出生的所有同学的学号、姓名和出生日期。
先
想
一
下
答案：
20、查询和学号为108、101的同学同年出生的所有同学的学号、姓名和出生日期。
select 
分析：
1.查询学号为108、101同学的出生年份。
select 
2.查询与108、101学号同年份的同学的学号、姓名和出生日期。
select 21、查询‘张旭’老师任课的学生成绩。
先
想
一
下
答案：
21、查询‘张旭’老师任课的学生成绩。
select 分析：子查询
1.查询张旭老师的老师编号。
select 
2.根据老师编号查询任课的课程编号。
select 
3.根据老师任课的课程编号，查询对应课程编号的学生成绩。
select 
22、查询选修某课程的同学人数多于5人的老师姓名。
先
想
一
下
答案：
22、查询选修某课程的同学人数多于5人的老师姓名。
select 
分析：子查询
1.查询选修某课程人数多于5人的课程号。
select 
2.查询选修该课程的老师编号。
select 
3.查询该老师编号的老师姓名。
select 23、查询95033班和95031班全体学生的记录。
先
想
一
下
答案：
23、查询95033班和95031班全体学生的记录。
select 
分析：
select 
错误写法：
and 表示交集，同时存在在两个班级内。既在95031班，又在95033班的同学，不存在。
in 表示并集，或者关系，在95031 或 在95033的班级内的同学，存在这样的数据。
select 
24、查询存在有85分以上成绩的课程编号。
先
想
一
下
答案：
24、查询存在有85分以上成绩的课程编号。
select 
分析：
select 
select 25、查询计算机系老师所教课程的成绩表。
先
想
一
下
答案：
25、查询计算机系老师所教课程的成绩表。
select 
分析：
1、在教师表中，查询计算机系老师编号。
select 
2、通过老师编号，查询课程表中，老师所教的对应课程科目编号。
select 
3、通过对应课程编号，查询成绩表的对应成绩。
select 
26、查询‘计算机系’与‘电子工程系’不同职称的老师的姓名和职称。
先
想
一
下
答案：【入门难点：not in 和 union 求并集 联合 】
26、查询计算机系与电子工程系，不同职称的老师的姓名和职称。
select 
分析：
- 查看老师表中数据。
select 
2.查询计算机系的老师的职称。
select 
3.查询电子工程系的老师的职称。
select 
4.查询计算机系老师的职称，且不与电子工程系老师的职称相同。
select 
5.查询电子工程系老师的职称，且不与计算机系老师的职称相同。
select 
27、查询选修课程号为‘3-103’课程且成绩至少高于选修课程号‘3-245’的同学的学号、课程号、成绩，并按照成绩从高到低次序排序。
先
想
一
下
答案：【入门：any()】
27、查询选修课程号为‘3-105’课程且成绩至少高于选修课程号‘3-245’的同学，的学号、课程号、成绩，并按照成绩从高到低次序排序。
select 
分析：
1.查询课程‘3-105’的数据
select 
2.查询课程‘3-245’的数据
select 
3. 至少，大于其中至少任意一个数据，使用any
select 错误写法：
select 
28、查询选修课程号为‘3-105’且成绩高于选修课程号‘3-245’课程的同学的学号、课程号和成绩。
先
想
一
下
答案：【入门：all()】
28、查询选修课程号为‘3-105’且成绩高于选修课程号‘3-245’课程的同学的学号、课程号和成绩。
select 
29、查询所有老师和同学的姓名、性别和出生日期。
先
想
一
下
答案：
29、查询所有老师和同学的姓名、性别和出生日期。
select 
分析：
select 
使用as作为别名。
30、查询所有女教师女学生的姓名、性别、出生日期。
先
想
一
下
答案：
30、查询所有女教师女学生的姓名、性别、出生日期。
select 
31、查询成绩比课程平均成绩低的同学的成绩表。
先
想
一
下
答案：
31、查询成绩比课程平均成绩低的同学的成绩表。
select 
分析：
1.查看各科成绩平均分（按照科目分组）
select 
2. 表格他本身的数据，无法和他本身求平均值同时进行比较。所以，通过表格本身，命名为 a ，复制另一个一摸一样的表格，命名为 b。（保证两个表格一样，条件有 a.cid = b.cid，就可以进行比较了 ）
select 
32、查询所有任课老师的姓名和部门。
先
想
一
下
答案：
32、查询所有任课老师的姓名和部门。
select 
分析：
1.查询任课老师（指的是有课程表中安排课程的老师。）
select 
2.根据课程中查询的老师编号，找到任课老师姓名和部门。
select 
3.如果题目改为查询任课老师姓名和部门，同时安排了考试成绩。
select 
33、查询至少有2名男生的班级。
先
想
一
下
答案：
33、查询至少有2名男生的班级。
select 
分析：
1.查询学生表
select 
2. 先查询表中的男学生，再将其按班级分组（为了查询班级号），计数（性别）
select 
至少有2名 = 计数 count() > 1！不是大于2！
至少有2名 = 计数 count() > 1！不是大于2！
至少有2名 = 计数 count() > 1！不是大于2！
错误写法：计数count() > 2，数据为空。
select 
34、查询学生表中不姓王的同学。
先
想
一
下
答案：
34、查询学生表中不姓王的同学。
select 
分析：
1.查询所有学生姓名
select 
2.不姓王的同学。
select 
35、查询学生表中每个同学的姓名和年龄。
先
想
一
下
答案：
35、查询学生表中每个同学的姓名和年龄。
select 
分析：
1.年龄 = 当前年份 - 学生出生年份
当前年份
select 
2.学生出生年份
select 
36、查询学生表最大和最小的出生日期的日期值。
先
想
一
下
答案：
36、查询学生表最大和最小的出生日期的日期值。
select 
分析：
1.查询学生出生日期
select 
2.查询最大和最小出生日期（最大是指数值大，而不是年龄的大小；例1996 大，1988 小）
select 
3.查询日期，去除时间值，起别名。
select 
37、以班号和年龄从大到小的顺讯查询学生表记录。
先
想
一
下
答案：
37、以班号和年龄从大到小的顺序查询学生表记录。
select 
写法2：算年龄
select 
38、查询男老师及其所上的课程。
先
想
一
下
答案：
38、查询男老师及其所上的课程。
select 
39、查询最高分同学的学号、课程号、成绩。
先
想
一
下
答案：
39、查询最高分同学的学号、课程号、成绩。
select 
分析：
select 
思考，如果最高分有多个呢？语句用 等于= 会报错吗？
修改数据：
update score set degree = 92 where cid ='6-166'and sid='109';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
现在有两个最高分，都是92分，再来看语句：
select 
无变化。
再次补充完整语句后，依然用等于号 = ，max()只返回一个数值。
select * from score where degree = (select max(degree) from score);
输出了两条相等的max(degree)的数据。
错误写法：
select sid,cid,max(degree) from score;
一条包含 aggregate 聚集函数的查询语句但是没有使用 group by 分组。
当select语句中，除了聚合函数外还选择了多个字段，只用多个字段中的一个字段作为分组的依据，那么剩下的字段任然无法与聚集函数之间构成有明确合理的关联。
40、查询与李云同性别的所有的同学姓名。
先
想
一
下
答案：
40、查询与李云同性别的所有的同学姓名。
select 
41、查询与李云同性别并且同班的同学姓名。
先
想
一
下
答案：【双子查询】
41、查询与李云同性别并且同班的同学姓名。
select 
错误写法：
select sname from student where ssex in (select ssex from student where sname ='李云')
union
select sname from student where sclass in (select sclass from student where sname ='李云');
这是或和李云同性别，或和李云同班级的同学姓名。
42、查询所有选修计算机导论课程的男同学的成绩表。
先
想
一
下
答案：
42、查询所有选修计算机导论课程的男同学的成绩表。
select 
分析：
1.查询男同学
select 
2.查询计算机导论的课程号
select 
3.查询选修计算机导论，且，选择学号在男同学范围里的同学
select 
错误写法：
select 
sid，有多个男学生，所以 = 只能选取一个值，要用 in 表示在这个a范围内。
cid，计算机导论，对应只有一个课程号，所以用 = 。
43、查询所有同学的学号、课程号和grade等级列。
建立如下grade等级表
create 输入数据：
insert 先
想
一
下
答案：
43、查询所有同学的学号、课程号和grade等级列。
select 
SQL的四种连接
内连接：
inner join 或 join
外连接：
1.左连接：Left join 或 left outer join
2.右连接：Right join 或 right outer join
3.完全外连接：full join 或 full outer join
1.创建数据库：
create 
2.选中数据库：
use 
3.创建person表和card表：
create 和
create 4.查看表
show 
5.添加表中数据：
card表：
insert 
查看card表数据：
select 
person表：
insert 查看person表数据：
select 
6.两个表的关系：
没有创建外键，但是原则上有外键关系。
7.查询内连接inner join：两张表的数据，通过某个字段相等，查询出相关记录数据。
select * from person inner join card on person.cardId = card.id;
8.左外连接left join / left outer join：
把左边表内所有数据取出来，
右边表中的数据，若有相等值，则显示；若没有，则NULL。
select 
9.右外连接 right join：
把右边表内所有数据取出来，
左边表中的数据，若有相等值，则显示；若没有，则NULL。
select * from person right join card on person.cardId = card.id;
10.全外连接full join：
select * from person full join card on person.cardId = card.id;
mysql不支持全外连接full join 。
select * from person left join card on person.cardId = card.id
union
select * from person right join card on person.cardId = card.id;
- 查询“01”课程比“02”课程成绩高的所有学生的学号；
- 查询平均成绩大于60分的同学的学号和平均成绩；
- 查询所有同学的学号、姓名、选课数、总成绩
- 查询姓“李”的老师的个数；
- 查询没学过“张三”老师课的同学的学号、姓名；
- 查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；
- 查询学过“张三”老师所教的课的同学的学号、姓名；
- 查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
- 查询所有课程成绩小于60分的同学的学号、姓名；
- 查询没有学全所有课的同学的学号、姓名；
- 查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
- 查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
- 把“score”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
- 查询没学过"张三"老师讲授的任一门课程的学生姓名；
- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩；
- 检索"01"课程分数小于60，按分数降序排列的学生信息；
- 按平均成绩从高到低显示所有学生的平均成绩；
- 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率；
- 按各科平均成绩从低到高和及格率的百分数从高到低顺序；
- 查询学生的总成绩并进行排名；
- 查询不同老师所教不同课程平均分从高到低显示；
- 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩；
- 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比；
- 查询学生平均成绩及其名次；
- 查询各科成绩前三名的记录；
- 查询每门课程被选修的学生数；
- 查询出只选修了一门课程的全部学生的学号和姓名；
- 查询男生、女生人数；
- 查询名字中含有"风"字的学生信息；
- 查询同名同性学生名单，并统计同名人数；
- 查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)；
- 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
- 查询不及格的课程，并按课程号从大到小排列；
- 查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；
- 查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
- 查询每门功课成绩最好的前两名；
- 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列；
- 检索至少选修两门课程的学生学号；
- 查询选修了全部课程的学生信息；
- 查询各学生的年龄；
- 查询本周过生日的学生；
- 查询下周过生日的学生；
- 查询本月过生日的学生；
- 查询下月过生日的学生；
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
