交互式 SQL

实验目的

通过进行给定使用场景下的 SQL 语句编写和运行，理解 SQL 语句的执行过程。

实验环境

• 本次实验使用 MySQL Community 8.0.12.0 版。
• 本次实验使用了 MySQL Workbench 工具。
• 操作系统同 实验一。

实验内容和完成情况

本次使用的数据库内容

• Product
• PC
• Laptop

maker	model	type
A	1001	pc
A	1002	pc
A	2004	laptop
B	1003	pc
B	1004	pc
B	2005	laptop
C	1005	pc
C	2006	laptop
C	2007	laptop

model	speed	ram	hd	price
1001	2.66	1024	250	2114
1002	2.10	512	250	995
1003	1.42	512	80	478
1004	2.80	1024	250	649
1005	3.15	2048	500	4028

model	speed	ram	hd	screen	price
2004	2.00	512	60	13.3	1150
2005	2.16	1024	120	17.0	2500
2006	5.00	4096	500	17.0	6094
2007	3.58	4028	120	14.5	4000

数据定义

模式的创建与删除

1. 为当前用户创建一个名为 computer_products 的模式。

   CREATE SCHEMA `computer_products`;

   

   

   

   
   1. MySQL 创建模式时不需要 AUTHORIZATION 子句。
   2. 创建完模式后，需要双击 computer_products 或使用 use computer_products; 命令进入该模式。
   3. 应当进行已有数据库的检查，若已存在，则先删除再插入。其实采用以下语句会更加科学。

   DROP SCHEMA IF EXISTS `computer_products`;
   CREATE SCHEMA IF NOT EXISTS `computer_products`;

2. 本次实验已结束，本模式不再需要。请删除 computer_products 模式。

   DROP SCHEMA computer_products;

   

   

   

   执行成功后，SCHEMAS 栏中不再有此模式（见最右图）。

基本表的创建与删除

1. 创建计算机产品数据库的 Product 表、PC 表和 Laptop 表。

   • Product
   • PC
   • Laptop

   create table Product(
      maker char(2), 
      model int(4) primary key,
      type char(10)
      );

      create table PC(
     model int(4) primary key,
     speed float(3),
     ram int(5),
     hd int(4),
     price int(5),
     foreign key (model) references Product(model)
   );

      create table Laptop(
     model int(4) primary key,
     speed float(3),
     ram int(5),
     hd int(4),
     screen float(2),
     price int(5),
     foreign key (model) references Product(model)
   );

   

   

   

   

   

   
   
   1. MySQL 中引用外码的写法是 FOREIGN KEY (<外码列名>) REFERENCES <表名>(<引用列名>)。
   2. 后来，我将 screen 和 speed 的数据类型改为了 decimal（MySQL 中不支持 numeric 的写法），在后面的数据查找操作中会简单讲下为什么要这样做。

2. 为 PC 表中加入屏幕尺寸列，取值为一位小数。

   ALTER TABLE pc ADD COLUMN screen FLOAT(1);

   

   

3. 为统计屏幕尺寸创建了一个新表 screen，现在统计结束，不再需要此表。

   DROP TABLE screen;

   

   

   

索引的建立与删除

1. 按照降序序列建立 PC 表中价格的索引。

   CREATE INDEX pc_price ON pc(price DESC);

   

   

   

2. 1 中的索引不再需要，请删除 pc_price 索引。

   DROP INDEX pc_price ON pc;

   

   

   在 MySQL 中，drop index 语句中也有 on 子句，需要指定索引所在表。

数据操作

数据更新

1. 向计算机产品数据库中添加给定数据。

   

   

   

   

   

   

   在 MySQL 中，VALUES 子句可以一次插入多组值。

2. 将所有硬盘容量为 120GB 的笔记本的价格下调 200 元。

   UPDATE laptop
   SET price=price-200
   WHERE hd=120;

   

   

3. 型号为 2004 的电脑已经停产，请删除其相关记录。

   DELETE
   FROM laptop
   WHERE model=2004;

   

   

数据查询

1. （单表查询）查询制造商 A 的所有产品，按照产品序列号降序排列。

   SELECT * FROM product
   where maker='A'
   ORDER BY model DESC;

   

   

2. （连接查询 I）查询生产了速度为 2.80 的 PC 机制造商。

   SELECT maker FROM product, pc
   WHERE product.model=pc.model
   AND speed=2.8;

   

   

   由于浮点数比较问题，这里的 speed 若是 float 或 double 类型，则会查找失败！
   解决方案：将 speed 设为 decimal (dec) 类型。

3. （连接查询 II）计算制造商 C 生产的所有笔记本的总价格。

   SELECT SUM(price)
   FROM product, laptop
   WHERE maker IN('C')
   AND product.model=laptop.model;

   

   

4. （嵌套查询 I）查询生产了型号为 2007 号笔记本的厂商都生产了哪些产品和型号。

   SELECT model, type
   FROM product
   WHERE maker IN (SELECT maker
           FROM product, pc, laptop
           WHERE (product.model=pc.model OR product.model=laptop.model)
           AND product.model=2007);

   

   

5. （嵌套查询 II）查询硬盘容量比所有 PC 机都小的笔记本电脑的型号及其硬盘容量大小。

   SELECT model, hd
   FROM laptop
   WHERE hd<ALL(SELECT hd FROM pc);

   

   

6. （集合查询）查询所有硬盘容量为 500G 的电脑型号及其类型。

   SELECT product.model,type
   FROM laptop, product
   WHERE product,model=laptop.model AND hd=500
   UNION
   SELECT product.model, type
   FROM pc, product
   WHERE product.model=pc.model AND hd=500;

   

   

视图操作

在 MySQL 中，视图是一种虚拟表，它的数据并不存储在数据库中，而是通过查询语句动态生成。

1. （视图创建）建立笔记本电脑所有信息的视图。

   CREATE VIEW laptop_info AS
   	SELECT maker, speed, ram, hd, price
   	FROM product, laptop
   	WHERE 
   		type IN ('laptop') 
   		AND product.model=laptop.model;

   

   

   

2. （视图更新）将制造商 C 的所有笔记本价格上调 200 元。

   SET SQL_SAFE_UPDATES=0;
   UPDATE laptop_info
   SET price=price+200
   WHERE maker='C';

   

   

3. （视图查询）在笔记本中找出屏幕大小为 17 寸的所有产品信息。

   SELECT * FROM laptop_info
   WHERE screen=17;

   

   

   若 screen 的数据类型为 double 或 float，类似于数据查询，也会导致查询失败！

4. （视图删除）删除笔记本电脑信息视图。

   DROP VIEW laptop_info;

   

   

遇到的问题和解决方案

1. 使用 update 命令更新数据时出现 1175 错误。提示信息如下：

   Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

   MySQL 有个叫 SQL_SAFE_UPDATES 的变量，为了数据库更新操作的安全性，此值默认为 1，所以才会出现更新失败的情况。所以，出现 1175 错误的时候，可以先设置 SQL_SAFE_UPDATES 的值为 0，然后再执行更新。对于上例来说，直接在前面加上一句 SET SQL_SAFE_UPDATES = 0; 即可解决问题。

2. 在 MySQL 中，CREATE DATABASE 和 CREATE SCHEMA 的区别是什么？

   “CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE.”

   MySQL 官方英文文档 dev.mysql.com/doc/refman/5.6/en/create-database.html

   CREATE DATABASE 根据给定的名称创建数据库，你需要拥有数据库的 CREATE 权限来使用这个语句。CREATE SCHEMA 是 CREATE DATABASE 的一个代名词。由此可见，在 MySQL 的语法操作中（MySQL5.0.2 之后），可以使用 CREATE DATABASE 和 CREATE SCHEMA 来创建数据库，两者在功能上是一致的。在使用 MySQL 官方的 MySQL 管理工具 MySQL Workbench 5.2.47 创建数据库时，使用的是 CREATE SCHEMA 来创建数据库的。而这和 MS SQL 中的 SCHEMA 有很大差别。

3. 在 MySQL 修改基本表中列的数据类型时，使用的命令是 CHANGE COLUMN。