第 8 章 · 实验一 · 在第二节之后

用户管理

查看服务器上的用户，创建用户并用新用户登录，再修改口令、修改用户名，最后删除用户。

环　境

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

执　行

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

注　意

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

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

实验 1-1查看已有的用户
实验 1-2创建用户
实验 1-3用新用户登录
实验 1-4修改口令和用户名
实验 1-5删除用户

第一步

数据准备（必做）

用 root 连接整段执行。删除本章会用到的用户和角色，并重建 teaching 库的五张表和教材数据。三次实验共用这份脚本，想从头再做时重新执行即可。

整段复制，全部执行下载 .sql

-- 第 8 章实验数据：删除本章用到的用户和角色，并重建教材 teaching 库的五张表
-- 用 root 连接整段执行。三次实验共用，想从头再做时重新执行即可。
DROP USER IF EXISTS 'student'@'localhost', 'teacher1'@'%', 'teacher2'@'localhost',
  'teacher3'@'localhost', 'teacher4'@'localhost', 'teacher10'@'localhost';
DROP ROLE IF EXISTS 'teacher', 'administrator', 'developer'@'localhost';

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');

1-1

查看已有的用户

讲义 · 查看用户

目的: 查询系统表 mysql.user，看服务器上有哪些用户，以及当前是哪个用户在连接。
前提: 已用 root 连接执行数据准备。

列出服务器上全部用户的用户名和主机
查看当前连接使用的用户

参考答案

可以直接复制到 Workbench 执行

SELECT User, Host FROM mysql.user;
SELECT CURRENT_USER();

语句解释

mysql.user	系统库 mysql 中的用户表，一行是一个用户
User, Host	用户名和主机，两列合起来确定一个用户
CURRENT_USER()	返回当前连接使用的用户

执行后看：能看到 root 和 mysql.sys 等几个系统用户。当前用户是 root@localhost。

1-2

创建用户

讲义 · CREATE USER 讲义 · 创建用户的例子

目的: 用 CREATE USER 创建用户，设置主机和口令。
前提: 已完成实验 1-1。

创建只能从本机登录的用户 student，口令为 student123
用一条语句创建两个用户：teacher1 可以从任何主机登录，口令为 teacher123，teacher2 只能从本机登录，使用随机口令
查询这三个用户

参考答案

可以直接复制到 Workbench 执行

CREATE USER IF NOT EXISTS 'student'@'localhost' IDENTIFIED BY 'student123';
-- 结果中会显示 teacher2 的随机口令
CREATE USER 'teacher1'@'%' IDENTIFIED BY 'teacher123',
            'teacher2'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
SELECT User, Host FROM mysql.user
WHERE User IN ('student', 'teacher1', 'teacher2');

语句解释

'student'@'localhost'	用户名 student，只能从本机登录
'teacher1'@'%'	% 表示可以从任何主机登录
IDENTIFIED BY RANDOM PASSWORD	由 MySQL 生成随机口令，只在这次的执行结果中显示
IF NOT EXISTS	用户已经存在时不报错，只给出警告

执行后看：第二句执行后，结果中 generated password 一列就是 teacher2 的口令。最后查出 3 个用户，teacher1 的 Host 是 %。

1-3

用新用户登录

讲义 · CREATE USER

目的: 用 student 登录，看一个刚创建、还没有授权的用户能做什么。
前提: 已完成实验 1-2。

用新用户登录的方法：Workbench 首页 MySQL Connections 右边点 ⊕，Connection Name 和 Username 都填这个用户名，点 Test Connection 输入口令，提示成功后点 OK，再双击这个连接打开。原来的 root 连接保持打开。

用 student 新建一个连接并打开
查看当前用户和能看到的数据库
进入 teaching 库

参考答案

在 student 的连接中执行

SELECT CURRENT_USER();
SHOW DATABASES;
-- 还没有授权，这一句会报错 1044
USE teaching;

语句解释

SHOW DATABASES	只列出当前用户有权限的数据库
USE teaching	进入 teaching 库。student 在这个库上没有任何权限，报错 1044
USAGE	新用户只有 USAGE 权限，也就是只能登录

执行后看：当前用户是 student@localhost，只看到 information_schema 和 performance_schema 两个库。USE teaching 报错 1044。

1-4

修改口令和用户名

讲义 · RENAME USER 讲义 · 修改口令

目的: 用 ALTER USER 修改口令，用 RENAME USER 修改用户名和主机。
前提: 已完成实验 1-3。

修改口令后，关掉 student 的连接再重新打开，Workbench 会要求输入口令，这时输入新口令 stu456。

在 root 的连接中，把 student 的口令改为 stu456
把 teacher1@% 改名为 teacher10@localhost
用新口令重新登录 student

参考答案

在 root 的连接中执行

ALTER USER 'student'@'localhost' IDENTIFIED BY 'stu456';
RENAME USER 'teacher1'@'%' TO 'teacher10'@'localhost';
SELECT User, Host FROM mysql.user WHERE User LIKE 'teacher%';

在 student 的连接中执行（用新口令重新连接）

SELECT CURRENT_USER();

语句解释

ALTER USER … IDENTIFIED BY 'stu456'	修改口令。也可以写成 SET PASSWORD FOR 用户 = 新口令
RENAME USER 'teacher1'@'%' TO 'teacher10'@'localhost'	用户名和主机一起修改，口令和权限不变
LIKE 'teacher%'	查询用户名以 teacher 开头的用户

执行后看：teacher1 不见了，多了 teacher10@localhost。student 用新口令能登录，用旧口令登录会报错 1045。

1-5

删除用户

讲义 · 删除用户

目的: 用 DROP USER 删除用户，比较加与不加 IF EXISTS 的区别。
前提: 已完成实验 1-4。

删除 teacher10
用一条语句删除 teacher2 和并不存在的 teacher9，加上 IF EXISTS
再删除一次 teacher10，不加 IF EXISTS

参考答案

可以直接复制到 Workbench 执行

DROP USER 'teacher10'@'localhost';
DROP USER IF EXISTS 'teacher2'@'localhost', 'teacher9'@'localhost';
SELECT User, Host FROM mysql.user WHERE User LIKE 'teacher%';
-- 用户已经不存在，这一句会报错 1396
DROP USER 'teacher10'@'localhost';

语句解释

DROP USER	删除用户，它在各个权限表中的记录一起删除
IF EXISTS	用户不存在时只给出警告，不报错
报错 1396	Operation DROP USER failed，要删除的用户不存在

执行后看：查询结果为空，teacher 开头的用户都已删除。第二句对 teacher9 只给出一条警告，最后一句报错 1396。

上一节第二节　用户管理下一节第三节　权限管理

保山学院·人工智能教研室·曹鼎鼎

用户管理

环 境

执 行

注 意

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

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

环　境

执　行

注　意

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