数据库原理与应用
1 / 7
0

准备

情景　今天要在两个地方来回切换：SQL 语句在 Workbench 里执行；mysqldump、mysql 这些程序在 命令行 cmd 里执行。先把两边都准备好。
命令行 cmd以 mysqldump、mysql 开头的命令。按 Win+R，输入 cmd 回车打开。不要用 PowerShell。
Workbench以 SELECT、SHOW、CREATE、DROP 等开头的 SQL 语句，在 root 连接的编辑区执行。

  1. Workbench 打开 root 的连接，复制下面整段，按 Ctrl+Shift+Enter 执行，把 teaching 库恢复成标准数据。

    Workbench 中整段执行下载 .sql
    -- 第 10 章上机练习：准备数据（在 Workbench 中用 root 整段执行，可以反复执行）
-- 重建教材的 teaching 库：c 课程、s 学生、t 教师、sc 选课、tc 授课

CREATE DATABASE IF NOT EXISTS teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 删除第 7 章实验建的视图
DROP VIEW IF EXISTS s_view, s_sc_c_view, s_maj_view, s_update_view, c_count_view, maj_view, s_sc_view, s_lxy_view;

-- 删除旧的表。先暂时关闭外键检查，库里有别的表引用它们时也能删除
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');
DROP TABLE IF EXISTS c_copy;

-- 检查数据
SELECT COUNT(*) AS 学生数 FROM s;
SELECT COUNT(*) AS 选课数 FROM sc;

    应看到最后两个结果：学生数 8，选课数 20。

  2. 命令行 cmd 打开 cmd，进入 MySQL 的 bin 文件夹，确认能找到 mysqldump。

    命令行 cmd 中执行
    cd /d "C:\Program Files\MySQL\MySQL Server 8.0\bin"
mysqldump --version

    注意如果装的是别的版本，把路径里的 8.0 换成实际的文件夹名。这个 cmd 窗口整节课都别关。

    应看到一行版本信息，例如 mysqldump Ver 8.0.xx for Win64。

  3. 命令行 cmd 建一个专门放备份的文件夹。

    命令行 cmd 中执行
    mkdir D:\db_backup

    注意电脑没有 D 盘的，把本页所有 D:/db_backup 都换成 C:/db_backup。

    应看到没有任何提示就是成功了。提示「已经存在」也没关系。

1

备份整个库

情景　期末成绩录完了，教务处要求先把 teaching 库完整备份一份，再做别的事。

  1. 命令行 cmd 把整个 teaching 库备份到 D:/db_backup/teaching_full.sql。

    格式mysqldump -u 用户 -p 库名 > 文件路径　回车后输入 root 的口令（输入时不显示）

    看答案
    命令行 cmd 中执行
    mysqldump -u root -p teaching > D:/db_backup/teaching_full.sql

    应看到输完口令后没有任何输出，又回到了命令提示符。

  2. 命令行 cmd 用记事本打开备份文件，看看里面是什么。

    命令行 cmd 中执行
    notepad D:\db_backup\teaching_full.sql

    应看到全是 SQL 语句：每张表一段 DROP TABLE IF EXISTS、CREATE TABLE、INSERT INTO。往上翻找一找——文件里没有 CREATE DATABASE。看完关掉记事本。

2

备份部分表

情景　学工处只要学生表；另一个部门要学生表和课程表。不用每次都备份整个库。

  1. 命令行 cmd 只备份学生表 s，文件名 teaching_s.sql。

    格式mysqldump -u 用户 -p 库名 表名 > 文件路径

    看答案
    命令行 cmd 中执行
    mysqldump -u root -p teaching s > D:/db_backup/teaching_s.sql

    应看到输完口令，没有输出。

  2. 命令行 cmd 同时备份学生表 s 和课程表 c，文件名 teaching_s_c.sql。

    提示多个表名之间用空格隔开。

    看答案
    命令行 cmd 中执行
    mysqldump -u root -p teaching s c > D:/db_backup/teaching_s_c.sql

    应看到输完口令，没有输出。

  3. 命令行 cmd 列出备份文件夹里的文件，比一比大小。

    命令行 cmd 中执行
    dir D:\db_backup

    应看到3 个 .sql 文件。teaching_full.sql 最大，teaching_s.sql 最小。

3

删库，再还原

情景　有人误执行了 DROP DATABASE，整个 teaching 库没了。幸好第 1 项做过备份——现在把它救回来。

  1. Workbench 制造事故：删掉 teaching 库，再看看库还在不在。

    Workbench 中执行
    DROP DATABASE teaching;
SHOW DATABASES;

    注意执行前确认第 2 项里看到了 teaching_full.sql，不然就真的找不回来了。

    应看到库列表里没有 teaching 了。

  2. Workbench 备份文件里没有 CREATE DATABASE，所以先建一个空库。

    Workbench 中执行
    CREATE DATABASE teaching DEFAULT CHARACTER SET utf8mb4;

    应看到1 row(s) affected。

  3. 命令行 cmd 换回 cmd，用 teaching_full.sql 还原。

    提示和备份命令比，只改两处：mysqldump 换成 mysql，> 换成 <。

    看答案
    命令行 cmd 中执行
    mysql -u root -p teaching < D:/db_backup/teaching_full.sql

    应看到输完口令，等一两秒，没有输出就是成功。

  4. Workbench 检查数据回来没有。

    Workbench 中执行
    USE teaching;
SHOW TABLES;
SELECT COUNT(*) AS 学生数 FROM s;
SELECT COUNT(*) AS 选课数 FROM sc;

    应看到c、s、t、sc、tc 都在；学生数 8，选课数 20，和删库前一样。

4

导出与导入

情景　把课程表导出成一个逗号分隔的文本文件，再把这个文件导入一张新表，看看数据是不是原样回来。

  1. Workbench 查一下 MySQL 允许读写文件的文件夹。

    Workbench 中执行
    SHOW VARIABLES LIKE 'secure_file_priv';

    应看到一般是 C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\。下面两步的路径要和它一致，并把 \ 写成 /。

  2. Workbench 把课程表 c 导出到 Uploads 文件夹的 c.txt，字段之间用逗号隔开。

    Workbench 中执行
    SELECT * FROM teaching.c
  INTO OUTFILE
  'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/c.txt'
  FIELDS TERMINATED BY ',';

    应看到8 row(s) affected。

  3. Workbench 建一张结构和 c 一样的空表 c_copy，把 c.txt 导进去，再查。

    Workbench 中执行
    CREATE TABLE teaching.c_copy LIKE teaching.c;

LOAD DATA INFILE
  'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/c.txt'
  INTO TABLE teaching.c_copy
  CHARACTER SET utf8mb4
  FIELDS TERMINATED BY ',';

SELECT * FROM teaching.c_copy;

    应看到c_copy 里有 8 行，和 c 表一模一样，中文也正常。

5

查看日志

情景　打开 MySQL 的「黑匣子」：看看它最近记了哪些错误信息，再找找第 3 项里那次删库有没有被记下来。

  1. Workbench 查看错误日志的位置和最近几条内容。

    Workbench 中执行
    SHOW VARIABLES LIKE 'log_error';

SELECT LOGGED, PRIO, DATA
FROM performance_schema.error_log
ORDER BY LOGGED DESC LIMIT 5;

    应看到第一句给出错误日志文件的路径；第二句列出最近 5 条记录：时间、级别（System、Warning、Error）、内容。

  2. Workbench 看看二进制日志开没开，有哪些文件。

    Workbench 中执行
    SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;

    应看到log_bin 是 ON；下面列出若干个文件，名字形如 xxx-bin.000012。记下最后一个的名字。

  3. Workbench 打开最后一个二进制日志，找一找删库的记录。

    Workbench 中执行
    SHOW BINLOG EVENTS IN 'xxx-bin.000012';

    注意把引号里的文件名换成上一步记下的那个。

    应看到在 Info 列里能找到 DROP DATABASE teaching，后面紧跟着还原时的 CREATE TABLE 和 INSERT。修改数据的操作都被记下来了，这正是它能用来恢复数据的原因。

6

捉一条慢查询

情景　用户说系统越来越慢。打开慢查询日志，故意执行一条要等 2 秒的语句，看它会不会被抓到。

  1. Workbench 看看慢查询日志现在的状态。

    Workbench 中执行
    SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

    应看到slow_query_log 是 OFF，long_query_time 是 10.000000（秒）。

  2. Workbench 打开慢查询日志，阈值改成 1 秒；并让它同时记到一张表里，方便用 SQL 查看。

    Workbench 中执行
    SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE,TABLE';

    应看到三句都执行成功。

  3. 把 root 的标签页关掉，再双击打开（新阈值只对新连接有效），然后在 Workbench 执行一条要等 2 秒的语句。

    Workbench 中执行
    SELECT SLEEP(2);

    应看到大约 2 秒后返回 0。

  4. Workbench 看看慢查询日志记下了什么。

    Workbench 中执行
    SHOW VARIABLES LIKE 'slow_query_log_file';

SELECT start_time, query_time,
       CONVERT(sql_text USING utf8mb4) AS 语句
FROM mysql.slow_log;

    应看到第一句是日志文件的位置；第二句里有一行：query_time 约 00:00:02，语句是 SELECT SLEEP(2)——被抓到了。

  5. Workbench 收尾：把三个设置都改回默认值。

    Workbench 中执行
    SET GLOBAL slow_query_log = OFF;
SET GLOBAL long_query_time = 10;
SET GLOBAL log_output = 'FILE';

    应看到三句都执行成功。

练习结束

今天用到的命令，记住它们在哪里执行：

做什么命令在哪执行
备份mysqldump -u root -p 库 [表] > 文件命令行 cmd
还原mysql -u root -p 库 < 文件命令行 cmd
导出SELECT … INTO OUTFILE '文件'Workbench
导入LOAD DATA INFILE '文件' INTO TABLE 表Workbench
看日志设置SHOW VARIABLES LIKE '…'Workbench
Workbench 中执行（可选）：删掉练习用的 c_copy 表
DROP TABLE IF EXISTS teaching.c_copy;
回看理论讲义返回第 10 章