课程 › 第 5 章 › 实验二
第 5 章 · 实验二 · 在第六节之后

修改表结构与操作数据

接着实验一的三张表做：先看清表结构，再修改字段和约束，然后插入、修改、删除数据，最后按顺序把表删掉。

环　境

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

执　行

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

注　意

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

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

  1. 实验 2-1查看表结构
  2. 实验 2-2添加和修改字段
  3. 实验 2-3删字段，改约束
  4. 实验 2-4插入数据
  5. 实验 2-5修改数据
  6. 实验 2-6删除数据和表
第一步

数据准备（必做）

重建实验一的 s、c、sc 三张表，并装入 8 名学生、6 门课程和 16 条选课记录。每个人从同一个起点开始，实验一没做完也能做。

整段复制，全部执行下载 .sql
-- 实验二的起点：实验一建好的三张表，再装入一些数据
DROP DATABASE IF EXISTS ch05_lab;
CREATE DATABASE ch05_lab DEFAULT CHARACTER SET utf8mb4;
USE ch05_lab;

-- 关闭 Workbench 的安全更新模式，否则实验 2-5 的 UPDATE 会报 1175
SET SQL_SAFE_UPDATES = 0;

CREATE TABLE s (
  sno  CHAR(10)    NOT NULL,                -- 学号，固定 10 个字符
  sn   VARCHAR(45) NOT NULL,                -- 姓名，最多 45 个字符
  sex  ENUM('男','女') NOT NULL DEFAULT '男',  -- 性别，只能取男或女，不填时为男
  age  INT         NOT NULL,                -- 年龄，整数
  maj  VARCHAR(45) NOT NULL,                -- 专业
  dept VARCHAR(45) NOT NULL,                -- 院系
  PRIMARY KEY (sno)                         -- 学号做主键
);

CREATE TABLE c (
  cno CHAR(10)    NOT NULL,           -- 课程号
  cn  VARCHAR(45) NOT NULL UNIQUE,    -- 课程名，不能为空，也不能重复
  ct  INT,                            -- 课时，可以为空
  PRIMARY KEY (cno)                   -- 课程号做主键
);

CREATE TABLE sc (
  sno   CHAR(10)     NOT NULL,        -- 学号
  cno   CHAR(10)     NOT NULL,        -- 课程号
  score DECIMAL(5,2),                 -- 成绩，共 5 位数字，其中 2 位小数
  PRIMARY KEY (sno, cno),             -- 学号和课程号合起来做主键
  FOREIGN KEY (sno) REFERENCES s (sno),    -- 学号必须在学生表中存在
  FOREIGN KEY (cno) REFERENCES c (cno),    -- 课程号必须在课程表中存在
  CONSTRAINT ck_score CHECK (score >= 0 AND score <= 100)  -- 成绩在 0 到 100 之间
);

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

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32);

INSERT INTO sc VALUES
('s1','c1',90.5), ('s1','c2',88), ('s2','c4',70), ('s2','c5',57),
('s2','c6',81.5), ('s3','c1',75), ('s3','c2',76.5), ('s3','c4',85),
('s4','c1',93), ('s4','c2',82), ('s4','c6',NULL), ('s5','c2',79),
('s7','c3',77), ('s7','c5',62), ('s8','c2',66), ('s8','c3',96);
2-1

查看表结构

讲义 · 查看表
目的
会查看库里有哪些表、表的结构和完整的建表语句。
前提
已执行数据准备。
  1. 列出当前数据库中的表
  2. 查看学生表 s 的结构
  3. 查看选课表 sc 的建表语句，找出成绩检查约束的名字（实验 2-3 要用）

参考答案

可以直接复制到 Workbench 执行
-- 列出当前数据库中所有的表
SHOW TABLES;
-- 查看学生表的字段、类型、能否为空、主键和默认值
DESC s;
-- 查看选课表完整的建表语句，里面能看到约束名
SHOW CREATE TABLE sc;

语句解释

SHOW TABLES;列出当前数据库中的表，应看到 c、s、sc 三张
DESC s;查看表结构，每个字段一行
SHOW CREATE TABLE sc;查看建表语句。结果在 Create Table 那一格中，右键选 Open Value in Viewer 可以看全文
执行后看：建表语句中有 CONSTRAINT `ck_score` CHECK …，约束名就是 ck_score。
2-2

添加和修改字段

讲义 · 添加字段讲义 · CHANGE 与 MODIFY
目的
用 ALTER TABLE 的 ADD、MODIFY、CHANGE 修改学生表的结构。
前提
已完成实验 2-1。
  1. 给 s 表添加手机号字段 phone，类型 CHAR(11)，放在 sn 后面
  2. 把 phone 的类型改为 VARCHAR(20)
  3. 把字段 sn 改名为 sname，类型不变，仍然不能为空

参考答案

可以直接复制到 Workbench 执行
-- 添加字段 phone，放在 sn 后面
ALTER TABLE s ADD phone CHAR(11) AFTER sn;
-- 只改类型，用 MODIFY
ALTER TABLE s MODIFY phone VARCHAR(20);
-- 改字段名，用 CHANGE，旧名后面写新名和完整的定义
ALTER TABLE s CHANGE sn sname VARCHAR(45) NOT NULL;
-- 查看修改后的结构
DESC s;

语句解释

ADD phone CHAR(11) AFTER sn添加字段 phone，AFTER sn 表示放在 sn 后面，不写就放在最后
MODIFY phone VARCHAR(20)修改字段类型，字段名不变
CHANGE sn sname VARCHAR(45) NOT NULL把 sn 改名为 sname。新名后面要写出完整的定义，漏写 NOT NULL 就会变成可以为空
执行后看：DESC 的结果中，phone 排在 sname 后面，类型是 varchar(20)，sname 的 Null 一栏是 NO。
2-3

删除字段，修改约束

讲义 · 删除字段和约束讲义 · 检查约束
目的
用 DROP 删除字段和约束，再重新添加约束。
前提
已完成实验 2-2，知道成绩检查约束的名字是 ck_score。
  1. 删除字段 phone
  2. 学校允许加分，成绩上限改为 120：先删除约束 ck_score，再添加一个同名的约束，范围 0 到 120

参考答案

可以直接复制到 Workbench 执行
-- 删除字段 phone，这一列的数据一起删除
ALTER TABLE s DROP phone;
-- 删除名为 ck_score 的约束
ALTER TABLE sc DROP CONSTRAINT ck_score;
-- 重新添加检查约束，成绩范围改为 0 到 120
ALTER TABLE sc ADD CONSTRAINT ck_score CHECK (score >= 0 AND score <= 120);
-- 查看建表语句，确认约束已经改好
SHOW CREATE TABLE sc;

语句解释

DROP phone删除字段 phone
DROP CONSTRAINT ck_score按名字删除约束，所以删除前要先查到约束名
ADD CONSTRAINT ck_score CHECK (…)添加检查约束，并给它起名 ck_score
执行后看：建表语句中 ck_score 那一行变为 score <= 120。
2-4

插入数据

讲义 · 插入一行讲义 · 部分字段与多行
目的
用 INSERT 插入整行、部分字段和多行记录。
前提
已完成实验 2-3。s 表现在有 6 个字段：sno、sname、sex、age、maj、dept。
  1. 插入一名学生：s9，郑冬，女，21 岁，计算机专业，信息学院
  2. 为 s9 插入一条选课记录，只有学号和课程号：c1
  3. 用一条语句为 s9 再插入两条选课记录：c2 85 分，c3 110 分

参考答案

可以直接复制到 Workbench 执行
-- 插入一行，按字段顺序给出全部 6 个值
INSERT INTO s VALUES ('s9', '郑冬', '女', 21, '计算机', '信息学院');
-- 只给学号和课程号两个字段，成绩为空
INSERT INTO sc (sno, cno) VALUES ('s9', 'c1');
-- 一次插入两行，每行一对括号，中间用逗号隔开
INSERT INTO sc VALUES ('s9', 'c2', 85), ('s9', 'c3', 110);
-- 查看 s9 的选课记录
SELECT * FROM sc WHERE sno = 's9';

语句解释

INSERT INTO s VALUES (…)不写字段名时，要按表中字段的顺序给出每个字段的值
INSERT INTO sc (sno, cno) VALUES (…)只给括号里列出的字段赋值，其余字段为 NULL
's9'　85字符串要加单引号，数字不加
WHERE sno = 's9'只显示学号为 s9 的行
执行后看：s9 有 3 条选课记录，c1 的成绩为 NULL。110 分能插入，是因为实验 2-3 把上限改成了 120。
2-5

修改数据

讲义 · 修改数据
目的
用 UPDATE 修改一行和全部行。
前提
已完成实验 2-4。
  1. 把 s9 的专业改为 人工智能
  2. 把 s9 的 c1 成绩改为 88
  3. 所有学生的年龄加 1

参考答案

可以直接复制到 Workbench 执行
-- 只修改学号为 s9 的那一行
UPDATE s SET maj = '人工智能' WHERE sno = 's9';
-- 两个条件同时满足才修改，用 AND 连接
UPDATE sc SET score = 88 WHERE sno = 's9' AND cno = 'c1';
-- 没有 WHERE，表中每一行都修改
UPDATE s SET age = age + 1;
-- 查看修改结果
SELECT * FROM s;

语句解释

SET maj = '人工智能'SET 后面写要改的字段和新值
WHERE sno = 's9'只修改满足条件的行
SET age = age + 1新值可以用原来的值计算，这里是在原年龄上加 1
没有 WHERE表中所有行都会被修改，执行前要想清楚
执行后看：所有学生的年龄都比原来大 1，s9 的专业是人工智能。如果报 1175，先执行 SET SQL_SAFE_UPDATES = 0; 再执行。
2-6

删除数据和表

讲义 · 删除数据讲义 · 删除表
目的
用 DELETE 删除行，用 DROP TABLE 删除表，掌握有外键时的删除顺序。
前提
已完成实验 2-5。
  1. 删除学生 s9。s9 在选课表中有记录，要按正确的顺序删除
  2. 删除 s、c、sc 三张表

参考答案

可以直接复制到 Workbench 执行
-- 先删从表 sc 中 s9 的选课记录
DELETE FROM sc WHERE sno = 's9';
-- 再删主表 s 中的学生 s9
DELETE FROM s WHERE sno = 's9';
-- 删表也是先删从表，再删主表
DROP TABLE sc;
DROP TABLE s, c;
-- 确认已经删完
SHOW TABLES;

语句解释

DELETE FROM sc WHERE sno = 's9'删除满足条件的行。不写 WHERE 会删除全部行
先删 sc，再删 ss9 还被选课记录引用时直接删除，会报 1451
DROP TABLE s, c一次删除多张表，表的结构和数据都删除。先删 s 表而不删 sc，会报 3730
执行后看：SHOW TABLES 的结果为空。想从头再做，重新执行数据准备脚本即可。
保山学院·人工智能教研室·曹鼎鼎