慧记

主题

SQL练习 ​

建库、建表 ​

建库 ​

sql
-- 创建数据库
CREATE DATABASE mysqltest;
-- 数据库的使用
USE mysqltest;

建表 ​

sql
-- 学生表
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)
);

-- 课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);

-- 教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);

-- 成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
);

插入数据 INSERT ​

sql
-- 插入学生表测试数据
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);

查看基本数据 ​

表名 ​

sql
-- 查看表名
SHOW TABLES FROM mysqltest;

-- 查看表名
SHOW TABLES;
shell
+---------------------+
| Tables_in_mysqltest |
+---------------------+
| course              |
| score               |
| student             |
| teacher             |
+---------------------+
4 rows in set (0.00 sec)

表内容 ​

sql
-- 查看表内容
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;
shell
mysql> SELECT * FROM course;
+------+--------+------+
| c_id | c_name | t_id |
+------+--------+------+
| 01   | 语文   | 02   |
| 02   | 数学   | 01   |
| 03   | 英语   | 03   |
+------+--------+------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM score;
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01   | 01   |      80 |
| 01   | 02   |      90 |
| 01   | 03   |      99 |
| 02   | 01   |      70 |
| 02   | 02   |      60 |
| 02   | 03   |      80 |
| 03   | 01   |      80 |
| 03   | 02   |      80 |
| 03   | 03   |      80 |
| 04   | 01   |      50 |
| 04   | 02   |      30 |
| 04   | 03   |      20 |
| 05   | 01   |      76 |
| 05   | 02   |      87 |
| 06   | 01   |      31 |
| 06   | 03   |      34 |
| 07   | 02   |      89 |
| 07   | 03   |      98 |
+------+------+---------+
18 rows in set (0.00 sec)

mysql> SELECT * FROM student;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷   | 1990-01-01 | 男    |
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吴兰   | 1992-03-01 | 女    |
| 07   | 郑竹   | 1989-07-01 | 女    |
| 08   | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+
8 rows in set (0.00 sec)

实战50例 ​

单表查询 ​

多表查询 ​

LEFT JOIN ​

查询"01"课程比"02"课程成绩高的学生的信息及课程分数 ​
sql
SELECT
	st.*,
	sc.s_score AS '语文',
	sc2.s_score '数学' 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
	AND sc.c_id = '01'
	LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
	AND sc2.c_id = '02' 
WHERE
	sc.s_score > sc2.s_score
  1. 选择字段:
    • st.*: 选择 student 表中的所有字段（所有学生的基本信息）。
    • sc.s_score AS '语文': 从 score 表中选择与课程 "01" 相关的分数，并将其别名为 "语文"。
    • sc2.s_score AS '数学': 从 score 表中选择与课程 "02" 相关的分数，并将其别名为 "数学"。
  2. FROM 子句:
    • FROM student st: 从 student 表开始查询，并将其命名为 st。
  3. LEFT JOIN 操作:
    • 第一个 LEFT JOIN score sc ON sc.s_id = st.s_id AND sc.c_id = '01': 将 student 表与 score 表连接，查找课程 "01" 的成绩。由于使用了 LEFT JOIN，即使某个学生没有这门课的成绩，仍会返回该学生的信息，且课程 "01" 的分数将为 NULL。
    • 第二个 LEFT JOIN score sc2 ON sc2.s_id = st.s_id AND sc2.c_id = '02': 类似地，连接查找课程 "02" 的成绩。
  4. WHERE 子句:
    • WHERE sc.s_score > sc2.s_score: 过滤条件，确保只返回课程 "01" 的成绩高于课程 "02" 的学生。
查询"01"课程比"02"课程成绩低的学生的信息及课程分数 ​
sql
SELECT
	st.*,
	sc1.s_score,
	sc2.s_score 
FROM
	student st
	LEFT JOIN score sc1 ON sc1.s_id = st.s_id 
	AND sc1.c_id = '01'
	LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
	AND sc2.c_id = '02' 
WHERE
	sc1.s_score < sc2.s_score

GROUP BY ​

分组

sql
-- 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
	st.s_id,
	st.s_name,
	ROUND( AVG( sc.s_score ), 2 ) cjScore 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	st.s_id 
HAVING
	AVG( sc.s_score )>= 60
  1. GROUP BY 子句:
    • GROUP BY st.s_id, st.s_name: 将结果按照学生编号和姓名进行分组，以便计算每个学生的平均成绩。注意，所有选定的非聚合字段都必须包含在 GROUP BY 子句中。
  2. HAVING 子句:
    • HAVING AVG(sc.s_score) >= 60: 过滤条件，确保只返回平均成绩大于或等于 60 的学生。HAVING 子句用于对分组后的结果进行过滤。

HAVING ​

过滤

sql
-- 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
	st.s_id,
	st.s_name,
	ROUND( AVG( sc.s_score ), 2 ) cjScore 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	st.s_id 
HAVING
	AVG( sc.s_score )>= 60
sql
-- 	查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SELECT st.s_id,
       st.s_name,
       (CASE WHEN ROUND(AVG(sc.s_score), 2) is null then 0 else ROUND(AVG(sc.s_score)) end) cjScore
from student st
         left JOIN score sc ON sc.s_id = st.s_id
group by st.s_id
having AVG(sc.s_score) < 60
    or AVG(sc.s_score) is NULL

  1. 选择列:

    • st.s_id：学生的 ID。
    • st.s_name：学生的姓名。
    • cjScore：计算得出的平均分数，使用 AVG(sc.s_score) 计算。

  2. CASE 语句:

    • 这里使用了一个CASE语句来处理平均分数的显示：
      • 如果 AVG(sc.s_score) 的结果为 NULL（即学生没有任何分数记录），则返回 0。
      • 否则，返回 ROUND(AVG(sc.s_score), 2) 的结果，四舍五入到小数点后两位。

  3. 连接:

    • 使用 LEFT JOIN 将 student 表和 score 表连接在一起，这样即使某些学生没有任何分数记录，他们仍然会出现在结果中。

  4. 分组:

    • GROUP BY st.s_id：按照学生的 ID 进行分组，以便计算每个学生的平均分数。

    • 筛选条件:HAVING

      子句用于筛选分组后的结果：

      • AVG(sc.s_score) < 60：选择平均分数低于 60 分的学生。
      • OR AVG(sc.s_score) IS NULL：选择没有分数记录的学生（即平均分数为 NULL 的学生）。

ORDER BY ​

ASC正序，默认值，DESC倒序

sql
select st.s_id,
       st.s_name,
       count(sc.c_id)                                                                              '选课总数',
       (case when sum(sc.s_score) is null or sum(sc.s_score) = '' then 0 else sum(sc.s_score) end) '总成绩'
from student st
         left outer join score sc on st.s_id = sc.s_id
group by st.s_id
ORDER BY sum(sc.s_score) DESC ;
shell
+------+--------+----------+--------+
| s_id | s_name | 选课总数 | 总成绩 |
+------+--------+----------+--------+
| 01   | 赵雷   |        3 |    269 |
| 03   | 孙风   |        3 |    240 |
| 02   | 钱电   |        3 |    210 |
| 07   | 郑竹   |        2 |    187 |
| 05   | 周梅   |        2 |    163 |
| 04   | 李云   |        3 |    100 |
| 06   | 吴兰   |        2 |     65 |
| 08   | 王菊   |        0 |      0 |
+------+--------+----------+--------+

在 SQL 中，ORDER BY 子句中不能直接使用列别名（如 '总成绩'）进行排序。应当使用实际的表达式或计算结果。在这个查询中，总成绩 是一个由 CASE 语句计算得出的列，因此需要直接使用这个计算表达式来排序，而不是用别名。

sql
-- 查询学过"张三"老师授课的同学的信息
select st.*
from student st
         left join score sc on sc.s_id = st.s_id
         left join course c on c.c_id = sc.c_id
         left join teacher t on t.t_id = c.t_id
where t.t_name = "张三";
sql
-- 查询没学过"张三"老师授课的同学的信息
-- 张三老师教的课
select c.* from course c left join teacher t on t.t_id=c.t_id where  t.t_name="张三";
-- 有张三老师课成绩的st.s_id
select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where  t.t_name="张三");
-- 不在上面查到的st.s_id的学生信息,即没学过张三老师授课的同学信息
select st.* from student st where st.s_id not in(
    select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where  t.t_name="张三")
)

Released under the MIT License.

Copyright © 2024 lihuibear