课程 › 第 6 章 › 实验一
第 6 章 · 实验一 · 在第二节之后

单表查询与条件查询

在一张表中查询：先选择要显示的列，去掉重复、限制行数，再用各种条件筛选行。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 1-1查看表和指定列
  2. 实验 1-2去重和限制行数
  3. 实验 1-3比较与多重条件
  4. 实验 1-4范围和集合
  5. 实验 1-5部分匹配和空值
第一步

数据准备（必做）

在 teaching 库中重建教材的学生表 s、课程表 c、教师表 t、选课表 sc、授课表 tc，并装入教材中的数据，查询结果与课件例题一致。四次实验共用这份数据。

整段复制，全部执行下载 .sql
-- 第 6 章实验数据：教材 teaching 库的五张表和教材中的数据
-- 四次实验共用这一份数据。整段执行一次即可，数据改乱了重新执行就能恢复。
CREATE DATABASE IF NOT EXISTS teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 删除旧的表。先暂时关闭外键检查，库里有别的表引用它们时也能删除
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sc, tc, avgsal, s, c, t;
SET FOREIGN_KEY_CHECKS = 1;

-- 课程表
CREATE TABLE c (
  cno CHAR(4) NOT NULL,
  cn VARCHAR(30) NOT NULL,
  ct INT,
  PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
  sno CHAR(4) NOT NULL,
  sn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  maj VARCHAR(30),
  dept VARCHAR(30),
  PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
  tno CHAR(4) NOT NULL,
  tn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  prof VARCHAR(20),
  maj VARCHAR(30),
  dept VARCHAR(30),
  sal DECIMAL(8,2),
  PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES s (sno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
  tno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  tcdate DATE,
  PRIMARY KEY (tno, cno),
  FOREIGN KEY (tno) REFERENCES t (tno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);

INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');

INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);

INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);

INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');
1-1

查看表和指定列

讲义 · 无条件查询讲义 · 列别名
目的
用 SELECT … FROM 查询全部列和指定的列，用 AS 给列起中文别名。
前提
已执行数据准备。
  1. 查询课程表 c 的全部数据
  2. 查询所有学生的学号、姓名和专业
  3. 查询课程表，把列名显示为 课程号、课程名、课时

参考答案

可以直接复制到 Workbench 执行
-- 查询课程表的全部列
SELECT * FROM c;
-- 只查三列，列的顺序按这里写的顺序
SELECT sno, sn, maj FROM s;
-- 用 AS 给每一列起中文别名
SELECT cno AS 课程号, cn AS 课程名, ct AS 课时 FROM c;

语句解释

SELECT *星号表示全部字段
FROM c从课程表 c 中查询
SELECT sno, sn, maj只显示这三列，结果中列的顺序和这里一致
cno AS 课程号结果中这一列显示为“课程号”，表中的字段名不变
执行后看：第一句返回 8 门课程。第三句结果的表头是中文。
1-2

去重和限制行数

讲义 · DISTINCT讲义 · LIMIT
目的
用 DISTINCT 去掉重复的行，用 LIMIT 限制返回的行数。
前提
已完成实验 1-1。
  1. 查询学生表中有哪些专业，每个专业只出现一次
  2. 查询授课表 tc 中讲授了课程的教师号，去掉重复
  3. 查询前 3 位学生的姓名、学号和专业

参考答案

可以直接复制到 Workbench 执行
-- 不加 DISTINCT，专业会重复出现
SELECT maj FROM s;
-- 加上 DISTINCT，每个专业只出现一次
SELECT DISTINCT maj FROM s;
SELECT DISTINCT tno FROM tc;
-- 只返回前 3 行
SELECT sn, sno, maj FROM s LIMIT 3;

语句解释

DISTINCT写在 SELECT 后面，结果中完全相同的行只保留一行
LIMIT 3只返回结果的前 3 行
执行后看：第一句 8 行，第二句 4 行（计算机、信息、自动化、数学）。讲授课程的教师有 5 位：t1 到 t5。
1-3

比较与多重条件

讲义 · 比较大小讲义 · 多重条件
目的
用 WHERE 写比较条件，用 AND、OR 组合多个条件。
前提
已完成实验 1-2。
  1. 查询成绩在 90 分及以上的选课记录
  2. 查询职称为 教授 的教师的教师号、姓名和专业
  3. 查询计算机专业或数学专业的学生
  4. 查询年龄在 30 到 40 岁之间（包括 30 和 40）的教师的教师号、姓名和职称

参考答案

可以直接复制到 Workbench 执行
SELECT * FROM sc WHERE score >= 90;
-- 字符串常量要加单引号
SELECT tno, tn, maj FROM t WHERE prof = '教授';
-- 两个条件满足一个即可，用 OR
SELECT * FROM s WHERE maj = '计算机' OR maj = '数学';
-- 两个条件都要满足，用 AND
SELECT tno, tn, prof FROM t WHERE age >= 30 AND age <= 40;

语句解释

score >= 90大于或等于 90，包括 90 分
prof = '教授'字符串要用单引号括起来
OR两个条件满足其中一个即可
AND两个条件同时满足
执行后看：90 分及以上 4 条。教授 2 位：刘杨、赵礼。计算机和数学专业共 4 人。30 到 40 岁的教师 4 位。
1-4

范围和集合

讲义 · BETWEEN讲义 · IN
目的
用 BETWEEN 查一个范围内的值，用 IN 查一组值。
前提
已完成实验 1-3。
  1. 用 BETWEEN 查询课时在 30 到 40 之间的课程
  2. 查询课时不在 30 到 40 之间的课程
  3. 用 IN 查询课程号为 c4 或 c6 的选课记录
  4. 查询除 c4、c6 以外的选课记录

参考答案

可以直接复制到 Workbench 执行
SELECT cno, cn, ct FROM c WHERE ct BETWEEN 30 AND 40;
SELECT cno, cn, ct FROM c WHERE ct NOT BETWEEN 30 AND 40;
SELECT sno, cno, score FROM sc WHERE cno IN ('c4', 'c6');
SELECT sno, cno, score FROM sc WHERE cno NOT IN ('c4', 'c6');

语句解释

BETWEEN 30 AND 40包括 30 和 40，等价于 ct >= 30 AND ct <= 40
NOT BETWEEN 30 AND 40范围以外，等价于 ct < 30 OR ct > 40
IN ('c4', 'c6')等于其中任意一个，等价于 cno = 'c4' OR cno = 'c6'
NOT IN ('c4', 'c6')和列表中每一个都不相等
执行后看：前两句的行数加起来是 8，正好是全部课程。后两句的行数加起来是 20，是全部选课记录。
1-5

部分匹配和空值

讲义 · LIKE讲义 · 空值
目的
用 LIKE 按部分内容查询，用 IS NULL 查询空值。
前提
已完成实验 1-4。
  1. 查询课程名中含有 程序 的课程
  2. 查询课程名第二个字是 据 的课程
  3. 查询没有考试成绩（成绩为空）的选课记录
  4. 把第 3 题的条件写成 score = NULL，看结果有什么不同

参考答案

可以直接复制到 Workbench 执行
-- % 代表任意多个字符
SELECT cno, cn, ct FROM c WHERE cn LIKE '%程序%';
-- _ 代表一个字符
SELECT * FROM c WHERE cn LIKE '_据%';
-- 判断空值只能用 IS NULL
SELECT sno, cno FROM sc WHERE score IS NULL;
-- 用等号判断空值，查不出任何行
SELECT sno, cno FROM sc WHERE score = NULL;

语句解释

LIKE '%程序%'课程名任意位置含有“程序”
LIKE '_据%'第一个字任意，第二个字是“据”，后面任意
IS NULL判断字段的值是否为空
score = NULLNULL 不能用等号比较，条件不成立，结果为空
执行后看：含“程序”的 2 门，第二个字是“据”的 3 门，成绩为空的 2 条（s2 的 c7、s4 的 c6）。最后一句不报错，但没有结果。
保山学院·人工智能教研室·曹鼎鼎