数据库操作实验

1. [实验名称] 数据库操作实验

2. [实验目的]

2.1. 实验1.1

掌握SQL 程序设计基本规范，熟练运用SQL 语言实现数据基本查询，包括单表查询、
分组统计查询和连接查询。

2.2. 实验1.2

熟悉SQL 语言有关视图的操作，能够熟练使用SQL 语句来创建需要的视图，定义数据
库外模式，并能使用所创建的视图实现数据管理。

2.3. 实验1.3

掌握SQL 嵌套查询和集合查询等各种高级查询的设计方法。

2.4. 实验1.4

掌握索引设计原则和技巧，能够创建合适的索引以提高数据库查询、统计分析效率。

2.5. 实验1.5

熟悉数据库的数据更新操作，能够使用SQL 语句对数据库进行数据的插入、修改、删除操作。

2.6. 实验1.6

掌握自主存取控制(MAC)权限的定义和维护方法。

3. [实验要求]

3.1. 实验1.1

针对NorthWind 数据库设计各种单表查询SQL 语句、分组统计查询语句；设计单个表针对自身的连接查询，设计多个表的连接查询。理解和掌握SQL 查询语句各个子句的特点和作用，按照SQL 程序设计规范写出具体的SQL 查询语句，并调试通过。
说明：简单地说，SQL 程序设计规范包含SQL 关键字大写、表名、属性名、存储过程名等大小写混合、SQL 程序书写缩进排列等编程规范。

3.2. 实验1.2

针对给定的数据库模式，以及相应的应用需求，创建视图和带WITH CHECK OPTION的视图，并验证视图WITH CHECK OPTION 选项的有效性。理解和掌握视图消解执行原理，
掌握可更新视图和不可更新视图的区别。

3.3. 实验1.3

针对NorthWind 数据库，正确分析用户查询要求，设计各种嵌套查询和集合查询。

3.4. 实验1.4

针对给定的数据库模式和具体应用需求，创建数据库索引。设计相应的SQL 查询验证索引的有效性。学习利用EXPLAIN 命令分析SQL 查询是否使用了所创建的索引，并能够分析其原因，执行SQL 查询并估算索引提高查询效率的百分比。要求实验数据集达到10 万条记录以上的数据量，以便验证索引效果。

3.5. 实验1.5

针对NorthWind 数据库设计单个记录插入、批量数据插入、修改数据和删除数据等SQL语句。理解和掌握INSERT、UPDATE 和DELETE 语法结构的各个组成成分，结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句，并调试成功。

3.6. 实验1.6

 定义用户、角色；
 分配权限给用户、角色；
 回收权限；
 以相应的用户名登录数据库验证权限分配是否正确。

4. [实验过程及内容]

4.1. 实验1.1 基本查询实验

mysql> use NorthWind; use 命令打开指定的数据库模式
Database changed
mysql> show Tables;  显示库中的数据表
+---------------------+
| Tables_in_northwind |
+---------------------+
| v_product_16        |
| v_product_康富食品  |
| 产品                |
| 供应商              |
| 客户                |
| 类别                |
| 订单                |
| 订单明细            |
| 运货商              |
| 雇员                |
+---------------------+
10 rows in set (0.00 sec)

4.1.1. 1.1.1列出所有供应商的名称、联系人姓名、城市、地址和电话。

SELECT 公司名称,联系人姓名,城市,地址,电话 from 供应商;

4.1.2. 1.1.2查询订购日期为 1997 年第 1 季度，货主城市为天津或上海的订单的所有信息。

SELECT * from 订单 
WHERE  (订购日期<'1997-03-31' && 订购日期>'1997-01-01') 
&& (货主城市 ='天津' || 货主城市 ='上海' );

4.1.3. 1.1.3不带分组过滤条件的分组统计查询,按照地区统计客户的数目，并按数目从低到高排序

SELECT `地区`, COUNT(*) FROM `客户` GROUP BY `地区` ORDER BY COUNT(*) DESC;

4.1.4. 1.1.4列出总金额(即订单中每个产品的金额之和)在 3800 到 4000 之间(含)的每个订单及其总金额，计算结果取小数点后两位。要求输出结果按照总金额从高到低排序。附：每个产品的金额 = 单价 数量 (1 - 折扣)。

SELECT 订单ID, ROUND(SUM(单价 * 数量 * (1 - 折扣)), 2) AS 总金额
FROM 订单明细
GROUP BY 订单ID
HAVING 总金额 BETWEEN 3800 AND 4000
ORDER BY 总金额 DESC;

4.1.5. 1.1.5两表连接查询 列出雇员“张颖”所销售的所有订单的货主信息（包括货主名称、国家、地区、城市、地址）以及到货日期。供应商“福满多”能提供哪些产品？请列出产品的详细信息。

SELECT 客户.联系人姓名, 客户.国家, 客户.地区, 客户.城市, 客户.地址, 订单.到货日期
FROM 雇员,订单,客户
WHERE 雇员.姓名 = '张颖' && 订单.雇员ID = 雇员.雇员ID && 订单.客户ID = 客户.客户ID; 

SELECT 产品.产品名称
FROM 供应商,产品
WHERE 供应商.公司名称 = '福满多' && 供应商.供应商ID = 产品.供应商ID;

4.1.6. 1.1.6单表自身连接查询 找出客户“赐芳股份”所在的城市，同时列出除它外也位于这个城市的客户名称。

SELECT c1.城市, c2.公司名称 
FROM 客户 c1,客户 c2
WHERE c1.公司名称 = '赐芳股份' AND c2.公司名称 <> '赐芳股份' &&  c1.城市 =c2.城市;

4.1.7. 1.1.7三表及以上连接查询列出公司名称中含有“人寿”的所有客户的公司名称及其订单总金额，计算结果取小数点后两位。列出客户“建国科技”在 1997 年订购的所有产品的名称及其数量。

SELECT 客户.公司名称, ROUND(SUM(订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)), 2) AS 订单总额
FROM 客户,订单,订单明细
where 客户.客户ID = 订单.客户ID && 订单.订单ID = 订单明细.订单ID && 客户.公司名称 LIKE '%人寿%'
GROUP BY 客户.公司名称;

SELECT 产品.产品名称, 订单明细.数量
FROM 客户,订单,订单明细,产品
WHERE 客户.公司名称 = '建国科技' && YEAR(订单.订购日期) = 1997 && 客户.客户ID = 订单.客户ID 
&&订单.订单ID = 订单明细.订单ID && 订单明细.产品ID = 产品.产品ID ;

4.2. 实验1.2 视图实验

4.2.1. 1.2.1创建行列子集视图在产品表上创建视图“ V_PRODUCT_16”，列出 ID 为 16 的供应商所供应产品的 ID、产品名称、供应商 ID、类别 ID、单位数量、单价、库存量；查询此视图的所有记录。

create view V_PRODUCT_16 as select 产品.产品ID, 产品.产品名称, 产品.类别ID,
                                   产品.单位数量, 产品.单价, 产品.库存量,产品.供应商ID
from 产品
where 产品.供应商ID=16;
SELECT * FROM V_PRODUCT_16;

4.2.2. 1.2.2从多个表中创建视图为供应商“康富食品” 创建一个它能供应的产品的视图“ VPRODUCT康富食品”，列出产品 ID、产品名称、类别名称、单位数量、单价、库存量；查询此视图的所有记录。

CREATE VIEW V_PRODUCT_康富食品 AS SELECT 产品.产品ID, 产品.产品名称, 产品.类别ID,
                                   产品.单位数量, 产品.单价, 产品.库存量, 产品.供应商ID 
FROM 产品,供应商
WHERE 供应商.公司名称='康富食品' && 供应商.供应商ID = 产品.供应商ID;

SELECT * FROM V_PRODUCT_康富食品;

4.2.3. 1.2.3创建来源于单个表的统计视图创建视图“ V_产品类别库存”，统计不同类别产品的总库存，只需列出类别 ID 和总库存量；在视图中查询类别 ID 为 8 的产品的总库存量。

CREATE VIEW V_产品类别库存 AS 
SELECT 类别ID, SUM(库存量) AS 总库存量 
FROM 产品 
GROUP BY 类别ID;
SELECT * FROM V_产品类别库存;

SELECT * FROM V_产品类别库存
WHERE 类别ID = 8;

4.2.4. 1.2.4创建来源于多个表的统计视图创建视图“ V_雇员产品销售额”，统计每个雇员所销售的每种产品的总数量和总金额，要求视图中包括雇员ID、雇员姓名、产品名称、销售总数量、销售总金额（计算结果取小数点后两位）；在视图中查询蛋糕的总销售额。

CREATE VIEW V_雇员产品销售额 AS 
SELECT 雇员.雇员ID, 雇员.姓名, 产品.产品名称, SUM(订单明细.数量) AS 销售总数量, ROUND(SUM(订单明细.单价 * 订单明细.数量 * (1 - 订单明细.折扣)), 2) AS 销售总金额
FROM 雇员,订单,订单明细,产品
where 雇员.雇员ID = 订单.雇员ID && 订单.订单ID = 订单明细.订单ID && 订单明细.产品ID = 产品.产品ID
GROUP BY 雇员.雇员ID, 产品.产品名称;

SELECT * FROM V_雇员产品销售额;

SELECT * FROM V_雇员产品销售额
WHERE 产品名称 = '蛋糕';

4.2.5. 1.2.5验证视图的可更新性和进行更新操作 视图“ V_产品类别库存”是可更新的吗？通过 SQL 语句加以验证，并说明原因。向视图“ V_PRODUCT_16”中分别插入两条记录： (101, ‘可口可乐’, 16, 1, ‘每箱 12瓶’, 20.00, 100) 和 (102, ‘百事可乐’, 15, 1, ‘每箱 24 瓶’, 30.00, 100)；看能否从视图中查询到这两条记录，再看能否从基本表中查询到这两条记录？通过视图删除刚才插入的两条记录，并验证之。

SELECT * FROM v_产品类别库存;
INSERT INTO v_产品类别库存(类别ID, 库存量)
VALUES (11,111);


INSERT INTO V_PRODUCT_16(产品ID, 产品名称, 供应商ID, 类别ID,
                                   单位数量, 单价, 库存量)
VALUES (101, '可口可乐', 16, 1, '每箱 12瓶', 20.00, 100);

INSERT INTO V_PRODUCT_16(产品ID, 产品名称, 供应商ID, 类别ID,
                                   单位数量, 单价, 库存量)
VALUES (102, '百事可乐', 15, 1, '每箱 24 瓶', 30.00, 100);

SELECT * FROM V_PRODUCT_16;

SELECT * FROM 产品
WHERE (产品ID=101||产品ID=102);

4.2.6. 1.2.6验证 WITH CHECK OPTIONPage 8 仿照视图“ V_PRODUCT_16”，创建视图“ V_PRODUCT_16_WCO”，但增加 WITHCHECK OPTION；向视图“ V_PRODUCT_16_WCO”中插入记录： (103, ‘百事可乐’, 15, 1, ‘每箱 24瓶’, 30.00, 100)，能否成功？

CREATE VIEW V_PRODUCT_16_WCO AS 
SELECT 产品.产品ID, 产品.产品名称,产品.供应商ID, 产品.类别ID, 产品.单位数量, 产品.单价, 产品.库存量
FROM 产品
WHERE 产品.供应商ID=16
WITH CHECK OPTION;

INSERT INTO V_PRODUCT_16_WCO(产品ID, 产品名称, 供应商ID, 类别ID,单位数量, 单价, 库存量)
VALUES (103, '百事可乐', 15, 1, '每箱 24瓶', 30.00, 100);

4.2.7. 1.2.7验证 RESTRICT 和 CASCADE,基于视图“ V雇员产品销售额”创建视图“ V雇员销售总额”，要求列出雇员姓名、销售总金额; 分别使RESTRICT 和 CASCADE 选项删除视图“ V_雇员产品销售额”，观察发生的现象并做出结论。

CREATE VIEW V_雇员销售总额 AS
SELECT 姓名, SUM(销售总金额) AS 总销售金额
FROM V_雇员产品销售额
GROUP BY 姓名; 

SELECT * FROM V_雇员销售总额;

DROP VIEW RESTRICT V_雇员产品销售额;
DROP VIEW CASCADE V_雇员产品销售额;

4.3. 实验1.3 数据高级查询实验

4.3.1. 1.3.1列出购买了“小坊”供应的“干贝”的客户的公司名称。（要求分别使用连接查询和嵌套查询两种方式，并在 MySQL WorkBench 中比较这两种方式的执行计划：点击结果窗口右侧的“Execution Plan”按钮）。

--连接查询
SELECT 客户.公司名称
FROM 订单
JOIN 客户 ON 订单.客户ID = 客户.客户ID
JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID
JOIN 产品 ON 订单明细.产品ID = 产品.产品ID
JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID
WHERE 供应商.公司名称 = '小坊' AND 产品.产品名称 = '干贝';

嵌套查询：
SELECT 客户.公司名称
FROM 客户
WHERE EXISTS (
    SELECT 1
    FROM 订单
    JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID
    JOIN 产品 ON 订单明细.产品ID = 产品.产品ID
    JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID
    WHERE 供应商.公司名称 = '小坊' AND 产品.产品名称 = '干贝' AND 订单.客户ID = 客户.客户ID
);

4.3.2. 1.3.2使用单层 EXISTS 嵌套查询没有购买过“小坊”供应的“干贝”的客户数目。

SELECT COUNT(*)
FROM 客户
WHERE NOT EXISTS (
    SELECT 1
    FROM 订单
    JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID
    JOIN 产品 ON 订单明细.产品ID = 产品.产品ID
    JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID
    WHERE 供应商.公司名称 = '小坊' AND 产品.产品名称 = '干贝' AND 订单.客户ID = 客户.客户ID
);

4.3.3. 1.3.3双层 EXISTS 嵌套查询 列出购买过客户“森通”购买过的类别 ID 为 3 的全部产品的客户名称。（成功写出语句后，在 MySQLWorkBench 中分析其执行计划）。

SELECT 客户.公司名称
FROM 客户
WHERE EXISTS (
    SELECT 1
    FROM 订单
    JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID
    JOIN 产品 ON 订单明细.产品ID = 产品.产品ID
    WHERE 订单.客户ID = 客户.客户ID AND EXISTS (
        SELECT 1
        FROM 订单 AS 订单2
        JOIN 订单明细 AS 订单明细2 ON 订单2.订单ID = 订单明细2.订单ID
        WHERE 订单2.客户ID = (SELECT 客户ID FROM 客户 WHERE 公司名称 = '森通') AND 订单明细2.产品ID = 产品.产品ID AND 产品.类别ID = 3
    )
);

4.3.4. 1.3.4采用 FROM 子句中的嵌套查询方式列出订单平均金额超过 800 元的华北地区客户的公司名称及订单平均金额，要求输出结果按照平均金额从低到高排序。

SELECT 公司名称, 平均订单金额
FROM (
    SELECT 客户.公司名称, AVG(订单总金额) AS 平均订单金额
    FROM (
        SELECT 订单.客户ID, SUM(订单明细.数量 * 产品.单价) AS 订单总金额
        FROM 订单
        JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID
        JOIN 产品 ON 订单明细.产品ID = 产品.产品ID
        GROUP BY 订单.客户ID
    ) AS 订单总金额
    JOIN 客户 ON 订单总金额.客户ID = 客户.客户ID
    WHERE 客户.地区 = '华北'
    GROUP BY 客户.公司名称
) AS 订单平均金额
WHERE 平均订单金额 > 800
ORDER BY 平均订单金额;

+------------------------+---------------+
| 公司名称               | 平均订单金额  |
+------------------------+---------------+
| 光远商贸               | 1640.800000   |
| 德化食品               | 1653.580000   |
| 山泰企业               | 1987.400000   |
| 志远有限公司           | 3161.350000   |
| 森通                   | 3239.800000   |
| 利合材料               | 3540.000000   |
| 汉典电机               | 3646.700000   |
| 正太实业               | 4437.100000   |
| 同恒                   | 4451.900000   |
| 就业广兑               | 4677.640000   |
| 仲堂企业               | 5345.000000   |
| 康毅系统               | 5864.100000   |
| 亚太公司               | 7585.580000   |
| 坦森行贸易             | 7616.150000   |
| 建国科技               | 7762.200000   |
| 兰格英语               | 7789.530000   |
| 千固                   | 10823.300000  |
| 池春建设               | 11266.450000  |
| 协昌妮绒有限公司       | 11272.000000  |
| 凯旋科技               | 11623.550000  |
| 建资                   | 11895.500000  |
| 嘉业                   | 12263.700000  |
| 浩天旅行社             | 13336.100000  |
| 宇欣实业               | 13848.250000  |
| 中通                   | 15777.770000  |
| 升格企业               | 18343.500000  |
| 仪和贸易               | 18500.620000  |
| 艾德高科技             | 18929.150000  |
| 百达电子               | 19744.750000  |
| 富泰人寿               | 19819.660000  |
| 福星制衣厂股份有限公司 | 27918.400000  |
| 友恒信托               | 31621.110000  |
| 留学服务中心           | 32989.900000  |
| 师大贸易               | 58540.910000  |
| 高上补习班             | 119333.040000 |
+------------------------+---------------+

4.4. 实验1.4 索引实验

4.4.1. 1.4.1执行程序 tpch-MySQL8.sql，建立一个新的数据库模式 tpch。成功执行后，各表中的记录数为：customer(661296)，nation(150)，continent(6)。

4.4.2. 1.4.2创建普通索引 列出 customer 表中姓“欧阳”的所有客户，并记录下其执行时间（点击结果窗口右侧的“Query Stat”按钮）；在 customer 表的 name 字段上创建普通索引 IDX_Name（可能需要较长时间）；再次执行上述查询，并记录下其执行时间，与前次时间进行比较。

-- 查询姓“欧阳”的所有客户并记录查询时间
SELECT * FROM customer WHERE name LIKE '欧阳%';

> OK
> 查询时间: 0.27s

-- 在customer表的name字段上创建普通索引IDX_Name
CREATE INDEX IDX_Name ON customer(name);


-- 再次查询姓“欧阳”的所有客户并记录查询时间
SELECT * FROM customer WHERE name LIKE '欧阳%'; 
> OK
> 查询时间: 0.003s

4.4.3. 1.4.3用 EXPLAIN 命令分析某个 SQL 查询语句执行时是否使用了索引。

4.4.4. 1.4.4删除 customer 表的普通索引 IDX_Name。

-- 使用EXPLAIN命令分析SQL查询语句
EXPLAIN SELECT * FROM customer WHERE name LIKE '欧阳%';

-- 删除customer表的普通索引IDX_Name
DROP INDEX IDX_Name ON customer;

4.5. 实验1.5 数据更新实验

编写 SQL 语句，实现以下数据更新操作。

4.5.1. 1.5.1INSERT 基本语句（插入全部列的数据），在雇员表中添加第 10 号雇员“张三”，要求为每列都给出一个合理的值。

-- INSERT 基本语句（插入全部列的数据），在雇员表中添加第 10 号雇员“张三”，要求为每列都给出一个合理的值。
INSERT INTO 雇员(雇员ID,姓名,职务,尊称,出生日期,雇用日期,地址,城市,地区,邮政编码,国家,家庭电话,分机)
VALUES (10, '张三', '安全员', '华科第一深情', '2001-08-15', '2021-08-15', '复兴门 245 号', '北京', '华北', '100098', '中国', '(010) 65559857', '5467');

4.5.2. 1.5.2INSERT 基本语句（插入部分列的数据）在订单表中插入一条 ID 为 99999 的订单：雇员 ID 为 10，客户 ID 为“BLONP”。并补足订单表的非空属性（订购日期, 到货日期,运货商, 运货费, 货主名称）的值。

-- INSERT 基本语句（插入部分列的数据）在订单表中插入一条 ID 为 99999 的订单：雇员 ID 为 10，客户 ID 为“BLONP”。并补足订单表的非空属性（订购日期, 到货日期,运货商, 运货费, 货主名称）的值。
INSERT INTO 订单(订单ID, 雇员ID, 客户ID, 订购日期, 到货日期, 运货商, 运货费, 货主名称)
VALUES (99999, 10, 'BLONP', '2022-01-01', '2022-01-10', 1, 100, '李四');

SELECT * FROM 订单 WHERE (订单ID=99999);

4.5.3. 1.5.3批量数据 INSERT 语句，创建一个新的客户表“河北客户”，把所有石家庄客户加入到此表中；继续把秦皇岛客户加入到“河北客户”表中；创建一个“购物统计”表，记录每个客户的公司名称及其订单总价（计算结果取小数点后两位）。

-- 创建新的客户表“河北客户”
CREATE TABLE 河北客户 AS SELECT * FROM 客户 WHERE 城市 = '石家庄';

-- 把秦皇岛客户加入到“河北客户”表中
INSERT INTO 河北客户 SELECT * FROM 客户 WHERE 城市 = '秦皇岛';
SELECT * FROM 河北客户;

-- 创建一个“购物统计”表，记录每个客户的公司名称及其订单总价
CREATE TABLE 购物统计 AS
SELECT 客户.公司名称, ROUND(SUM(订单明细.单价*订单明细.数量*(1-订单明细.折扣)), 2) AS 订单总价
FROM 订单,客户,订单明细
WHERE 客户.客户ID = 订单.客户ID && 订单.订单ID = 订单明细.订单ID
GROUP BY 公司名称;

SELECT * FROM 购物统计;

4.5.4. 1.5.4UPDATE 语句（修改部分记录的部分列值）将所有单价在 100 元以上（含）的产品的单价下降 10%。

UPDATE 产品
SET 单价 = 单价 * 0.9
WHERE 单价 >= 100;

4.5.5. 1.5.5UPDATE 语句（利用一个表中的数据修改另外一个表中的数据）在订单表中增加一个类型为 Decimal(8, 2)的属性“总金额”；利用订单明细表中的属性修改订单表中的总金额：总金额 = 单价数量(1-折扣)。

-- 在订单表中增加一个类型为 Decimal(8, 2)的属性“总金额”
ALTER TABLE 订单
ADD 总金额 decimal(8,2);

-- 利用订单明细表中的属性修改订单表中的总金额：总金额 = 单价*数量*(1-折扣)
UPDATE 订单
SET 总金额 = (SELECT SUM(单价*数量*(1-折扣)) 
FROM 订单明细 
WHERE 订单.订单ID = 订单明细.订单ID);

4.5.6. 1.5.6DELETE 语句（删除给定条件的所有记录）删除客户“东南实业”的所有订单（先删订单明细，再删订单）。只删除客户“三川实业有限公司”的所有订单，而不先删除其明细，看看结果如何？将“订单明细”的外码约束“订单明细_ibfk_1”修改为级联删除（使用 ALTER Table命令对约束先删再增）；现在直接删除客户“三川实业有限公司”的所有订单，看看结果如何？

DELETE FROM 订单明细
WHERE 订单ID IN (
    SELECT 订单ID FROM 订单
    WHERE 客户ID = (SELECT 客户ID FROM 客户 WHERE 公司名称 = '东南实业')
);

DELETE FROM 订单
WHERE 客户ID = (SELECT 客户ID FROM 客户 WHERE 公司名称 = '东南实业');

DELETE FROM 订单
WHERE 客户ID = (SELECT 客户ID FROM 客户 WHERE 公司名称 = '三川实业有限公司');

ALTER TABLE 订单明细
DROP FOREIGN KEY 订单明细_ibfk_1;

ALTER TABLE 订单明细
ADD CONSTRAINT 订单明细_ibfk_1 FOREIGN KEY (订单ID) REFERENCES 订单(订单ID) ON DELETE CASCADE;

DELETE FROM 订单
WHERE 客户ID = (SELECT 客户ID FROM 客户 WHERE 公司名称 = '三川实业有限公司');

4.6. 实验1.6 安全性实验

本节实验适宜于在两个或以上命令行窗口中进行：在窗口 1 中始终以 root 身份进行操作，在窗口 2 中以不同的用户身份登录数据库。

4.6.1. 1.6.1在窗口 1 中创建一个数据库用户“赵军”，初始密码为 123456；

SQL> CREATE USER 赵军 IDENTIFIED BY '123456';

4.6.2. 1.6.2在窗口 2 中以“赵军”登录，看能否创建一个新的数据库 test；

SQL> CREATE Database test;         
#ERROR 1044 (42000): Access denied...

4.6.3. 1.6.3在窗口 1 中创建一个角色“销售经理”，并授予其创建新数据库和查询 northwind库中所有表的权限；

SQL> CREATE ROLE 销售经理;                 #创建角色

SQL> GRANT create ON *.* TO 销售经理;             #为角色赋创建数据库的权限 
SQL> GRANT select ON northwind.* TO 销售经理; #为角色赋表的查询权限 
SQL> SHOW GRANTS for 销售经理;                                                       #查看角色的权限

4.6.4. 1.6.4在窗口 1 为用户“赵军”赋予角色“销售经理”，并查看其权限；

SQL> GRANT 销售经理 TO 赵军;        #将角色赋给用户

SQL> set default role all to 赵军; #赵军在登录时使用默认角色“销售经理”

4.6.5. 1.6.5在窗口 2 中以“赵军”重新登录，并尝试创建一个新的数据库，以及从 northwind数据库的表中查询数据；

SQL> CREATE Database test; 
SQL> USE NorthWind;

SQL> SELECT 姓名,职务 FROM 雇员;

4.6.6. 1.6.6在窗口 1 中创建“销售视图”，其作用是使每个销售代表只能看到自己的销售订单；

SQL> CREATE View 销售视图 AS SELECT 订单.*

FROM 订单 JOIN 雇员 ON (订单.雇员ID = 雇员.雇员ID)

WHERE INSTR(USER(),雇员.姓名) = 1;     #INSTR 和 USER 是内置函数

4.6.7. 1.6.7在窗口 1 中为雇员表中的 6 个销售代表（张颖等）各创建一个用户；然后创建一个新的角色“销售代表”，为其赋“销售视图”的选择权限，将此角色授予上述 6 个用户；

SQL> CREATE USER 张颖 IDENTIFIED BY '123456'; 
SQL> CREATE ROLE 销售代表;
SQL> GRANT select ON northwind.销售视图 TO 销售代表; 
SQL> GRANT 销售代表 TO 张颖;

SQL> set default role all to 张颖;

4.6.8. 1.6.8在窗口 2 中以不同身份登录，验证以上视图；

--以张颖为例
SQL> SELECT Distinct 雇员ID FROM 销售视图;

4.6.9. 1.6.9收回用户“张颖”的“销售代表”角色权限，并验证之；

SQL> revoke 销售代表 from 张颖;

SELECT Distinct 雇员ID FROM 销售视图;

4.6.10. 1.6.10自己设计关于数据库和表的其他权限操作实验，并验证。

回收除root用户外其他所有用户的权限

-- 查询除 root 外的其他用户的列表
SELECT User, Host FROM mysql.user WHERE User != 'root';

mysql.infoschema，mysql.session 和 mysql.sys 是 MySQL 系统账户，不应该修改这些账户的权限。

--收回权限
REVOKE ALL PRIVILEGES ON *.* FROM '张颖'@'%';
REVOKE ALL PRIVILEGES ON *.* FROM '赵军'@'%';
REVOKE ALL PRIVILEGES ON *.* FROM '销售代表'@'%';
REVOKE ALL PRIVILEGES ON *.* FROM '销售经理'@'%';

SELECT 查询显示这些用户仍然存在于 mysql.user 表中。这是因为 REVOKE 语句只收回用户的权限，而不删除用户。

--删除用户
DROP USER '张颖'@'%';
DROP USER '赵军'@'%';
DROP USER '销售代表'@'%';
DROP USER '销售经理'@'%';

5. [实验总结]

本次实验总体来说难度不大，但是有点费时间。我主要遇到了以下几点问题：

1.工具选择。最开始因为不熟悉那个可视化界面MySQL Workbench，我选择了那个黑窗口去编写运行SQL语句，主要这个界面不太方便修改自己的SQL语句，所以后面我安装了Navicat来进行实验，效率起飞。
2.对少量SQL语句不太熟悉，实验时需要翻看PPT和课本
3.特别是连接查询时，需要理清楚各个便之间的关系，最初我没有注意到实验指导书上面有一个northwind数据库的结构图，一直在Navicat中查看各个表之间的关系，很不方便，后面发现前面有结构图之后，理清关系时简单了许多，不需要耗费太多时间

总体来说这次实验收获很大，让我基本掌握了SQL语句的使用、MySQL数据库以及相关工具的使用。