课程 › 第 15 章 › 实验二
第 15 章 · 实验二 · 在第四节之后

运算符与流程控制

先用 SELECT 计算几个表达式，再把 IF、CASE、WHILE、REPEAT、LOOP 各写一遍。

环　境

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

执　行

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

注　意

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

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

  1. 实验 2-1算术与比较运算
  2. 实验 2-2逻辑运算与优先级
  3. 实验 2-3IF 语句
  4. 实验 2-4CASE
  5. 实验 2-5WHILE 与 REPEAT
  6. 实验 2-6LOOP 与 ITERATE
第一步

数据准备（必做）

重建 teaching 库的五张表和教材中的数据，并删除本章实验建立的存储过程和函数。

整段复制，全部执行下载 .sql
-- 第 15 章实验数据：教材 teaching 库的五张表和教材中的数据
-- 整段执行一次即可。五次实验共用，想从头再做时重新执行。
CREATE DATABASE IF NOT EXISTS teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 删除本章实验建立的存储过程和函数
DROP PROCEDURE IF EXISTS s_count;
DROP PROCEDURE IF EXISTS info_count;
DROP PROCEDURE IF EXISTS pass_or_fail;
DROP PROCEDURE IF EXISTS sum100;
DROP PROCEDURE IF EXISTS max_i;
DROP PROCEDURE IF EXISTS odd_sum;
DROP PROCEDURE IF EXISTS showstudentsnums;
DROP PROCEDURE IF EXISTS cal_courses;
DROP PROCEDURE IF EXISTS dept_students;
DROP PROCEDURE IF EXISTS cal_students;
DROP PROCEDURE IF EXISTS double_it;
DROP PROCEDURE IF EXISTS fetch3;
DROP PROCEDURE IF EXISTS dept_names;
DROP FUNCTION IF EXISTS cal_depts;
DROP FUNCTION IF EXISTS isprime;
DROP FUNCTION IF EXISTS get_level;

-- 删除旧的表。先暂时关闭外键检查，库里有别的表引用它们时也能删除
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sc, tc, avgsal, classroom, 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');
2-1

算术与比较运算

讲义 · 算术运算符与算术表达式讲义 · 比较运算符讲义 · 正则表达式
目的
计算几个算术表达式和关系表达式，注意除法、取余和空值参加比较时的结果。
前提
已执行数据准备。
  1. 计算 2/4、2 DIV 4、5 % 3
  2. 比较 NULL = NULL 与 NULL <=> NULL
  3. 用 IN、LIKE、REGEXP 各写一个表达式

参考答案

可以直接复制到 Workbench 执行
SELECT 2 / 4, 2 DIV 4, 5 % 3, 5 MOD 3;
SELECT 5 > 3, NULL = NULL, NULL <=> NULL, 5 IS NOT NULL;
SELECT 5 IN (10, 20, 30), '张玉涛' LIKE '张%', 's12' REGEXP '^s[0-9]+$';

语句解释

2 / 4结果是 0.5000，除法的商是实数
2 DIV 4整数除，商为 0
NULL = NULL有 NULL 参加比较，结果是 NULL
<=>安全等于，两边都是 NULL 时结果为 1
REGEXP^ 表示开头，[0-9]+ 表示一个或多个数字，$ 表示结尾
执行后看：第一行 0.5000、0、2、2。第二行 1、NULL、1、1。第三行 0、1、1。
2-2

逻辑运算与优先级

讲义 · 逻辑运算符与位运算符讲义 · 运算符的优先级
目的
计算几个表达式，体会逻辑运算的结果和运算符的优先级。
前提
已完成实验 2-1。
  1. 计算 5 AND NULL、0 OR 5、5 XOR 1
  2. 计算判断 2021 年是否闰年的表达式
  3. 计算 1<<2+8-3*9 MOD 4^3

参考答案

可以直接复制到 Workbench 执行
SELECT 5 AND NULL, 0 OR 5, 5 XOR 1, NOT 0;
-- 能被 4 整除又不能被 100 整除的是闰年
SELECT (2021 MOD 4) = 0 AND (2021 MOD 100) != 0;
SELECT '陈佳' LIKE '陈%' AND 19 BETWEEN 17 AND 21 AND 89 >= 85;
SELECT 1<<2+8-3*9 MOD 4^3;

语句解释

5 AND NULL一个是非零，一个是 NULL，结果为 NULL
XOR两个都非零，结果为 0
4^3^ 是按位异或，优先级最高，4^3 等于 7
计算顺序3*9 MOD 7 得 6，2+8-6 得 4，最后 1<<4 得 16
执行后看：第一行 NULL、1、0、1。闰年表达式为 0。第三句为 1。最后一句为 16。
2-3

IF 语句

讲义 · IF 语句
目的
把例 15-1 写成存储过程：查学号为 s1 的学生的平均成绩，不低于 60 分输出 Pass!，否则输出 Fail!。
前提
已完成实验 2-2。
  1. 建立存储过程 pass_or_fail，学号作为参数
  2. 分别查 s1 和 s6

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE PROCEDURE pass_or_fail(IN stu CHAR(4))
BEGIN
  IF (SELECT AVG(score) FROM sc WHERE sno = stu) >= 60 THEN
    SELECT stu, 'Pass!';
  ELSE
    SELECT stu, 'Fail!';
  END IF;
END $$
DELIMITER ;
CALL pass_or_fail('s1');
CALL pass_or_fail('s6');

语句解释

为什么要建过程IF、CASE、循环和 BEGIN … END 只能用在存储过程、函数等存储程序中，不能单独执行
IN stu CHAR(4)输入参数，调用时传入学号
IF … THEN … ELSE … END IF条件成立执行 THEN 后的语句，否则执行 ELSE 后的语句
AVG(score)只对非空的成绩求平均
执行后看：s1 的平均成绩 89.25，输出 Pass!。s6 没有选课，平均成绩为 NULL，条件不成立，输出 Fail!。
2-4

CASE

讲义 · CASE 语句
目的
按例 15-2、例 15-3，在查询中用 CASE 把性别换成 M、F，把成绩换成等级。
前提
已完成实验 2-3。
  1. 查询学号和性别，男输出 M，女输出 F
  2. 查询 s2 和 s4 的成绩，按分数段输出等级，空成绩输出未考

参考答案

可以直接复制到 Workbench 执行
SELECT sno,
  CASE sex
    WHEN '男' THEN 'M'
    WHEN '女' THEN 'F'
  END AS sex
FROM s;
SELECT sno, cno, score,
  CASE
    WHEN score IS NULL THEN '未考'
    WHEN score < 60 THEN '不及格'
    WHEN score < 70 THEN '及格'
    WHEN score < 90 THEN '良好'
    ELSE '优秀'
  END AS 等级
FROM sc WHERE sno IN ('s2', 's4');

语句解释

CASE sex WHEN格式 1，把 sex 的值与 WHEN 后的值逐个比较
CASE WHEN score < 60格式 2，逐个判断条件，取第一个成立的
次序条件从小到大写，score < 70 时已经排除了 score < 60 的情况
END 与 END CASE查询中的 CASE 是表达式，以 END 结束。在存储过程中单独使用的 CASE 语句以 END CASE 结束
执行后看：第一个查询 8 行，性别变为 M 或 F。第二个查询 8 行，s2 的 c7 和 s4 的 c6 为未考，s2 的 c5 为不及格。
2-5

WHILE 与 REPEAT

讲义 · WHILE 与 REPEAT
目的
用 WHILE 求 1+2+…+100（例 15-4），用 REPEAT 求满足 1²+2²+…+i² < 1000 的最大 i（例 15-6）。
前提
已完成实验 2-4。
  1. 建立 sum100，用 WHILE 求和
  2. 建立 max_i，用 REPEAT 求最大的 i
  3. 调用两个过程

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE PROCEDURE sum100()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE total INT DEFAULT 0;
  WHILE i <= 100 DO
    SET total = total + i;
    SET i = i + 1;
  END WHILE;
  SELECT total;
END $$
CREATE PROCEDURE max_i()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE total INT DEFAULT 0;
  REPEAT
    SET i = i + 1;
    SET total = total + i * i;
  UNTIL total >= 1000
  END REPEAT;
  -- 循环结束时 total 刚好不小于 1000，所以答案是 i - 1
  SELECT i - 1 AS 最大的i;
END $$
DELIMITER ;
CALL sum100();
CALL max_i();

语句解释

WHILE 条件 DO先判断，条件成立才执行循环体
REPEAT … UNTIL 条件先执行一次，再判断，条件成立时结束
UNTIL 后面没有分号
i - 1i 为 14 时平方和为 1015，第一次不小于 1000，所以最大的 i 是 13
执行后看：sum100 得到 5050，max_i 得到 13。
2-6

LOOP 与 ITERATE

讲义 · LOOP、LEAVE 与 ITERATE
目的
用 LOOP 求 1 到 100 中奇数的和：遇到偶数用 ITERATE 跳过，超过 100 用 LEAVE 结束。
前提
已完成实验 2-5。
  1. 建立 odd_sum，给 LOOP 加标号 lp
  2. 偶数时 ITERATE lp，i 大于 100 时 LEAVE lp

参考答案

可以直接复制到 Workbench 执行
DELIMITER $$
CREATE PROCEDURE odd_sum()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE total INT DEFAULT 0;
  lp: LOOP
    SET i = i + 1;
    IF i > 100 THEN
      LEAVE lp;
    END IF;
    IF i MOD 2 = 0 THEN
      ITERATE lp;
    END IF;
    SET total = total + i;
  END LOOP lp;
  SELECT total;
END $$
DELIMITER ;
CALL odd_sum();

语句解释

lp: LOOPLOOP 本身没有结束条件，要加标号，供 LEAVE 和 ITERATE 使用
LEAVE lp跳出标号为 lp 的循环
ITERATE lp结束本次循环，直接进入下一次
先加 i如果把 SET i = i + 1 放在 ITERATE 之后，遇到偶数 i 不再增加，循环不会结束
执行后看：得到 2500。
保山学院·人工智能教研室·曹鼎鼎