921107810238 张茂源
数据库实验报告

[toc]

【第一堂】安装

安装过程确实一波三折，安装版本不同，然后第一次安装又没有完全弄完，导致的bug就是再进行安装时无法完成，启动mySQL失败。查找了新教程完美解决安装和环境配置

2023 年 MySQL 8.0 安装配置 最简易（保姆级）

卸载MySQL

MySQL安装 starting the server失败的解决办法

下载安装

基本没有问题，注意选用custom安装，安装需要的MySQL Server 8.0.33 - X^64$ 即可。安装路径选择D盘，便于后续添加功能


注意命名时可改为MySQL，方便后续使用。

环境变量配置

复制MySQL 的bin目录，添加到系统变量Path目录内即可

验证安装

管理员身份运行终端，输入mysql -uroot -p，再输入密码，

启动 MySQL 自带命令行，启动图形化界面均正常。

基本操作

查看数据库

创建数据库

创建表，显示表结构

修改字段数据类型

修改字段名

增加字段

删除字段

修改字段排列

删除表

相关代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 查看当前系统中有什么数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE MYSQLDATA;

-- 选择数据库
USE MYSQLDATA;

-- 查看当前的表情况
SHOW TABLES;

-- 创建MYTABLE表
CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

-- 描述表结构
DESCRIBE MYTABLE;

-- 修改字段数据类型
ALTER TABLE MYTABLE MODIFY sex int;

-- 修改字段名
ALTER TABLE MYTABLE CHANGE sex thissex char(1);

-- 增加字段
ALTER TABLE MYTABLE ADD num INT(8) PRIMARY KEY FIRST;

-- 调整字段排列
ALTER TABLE MYTABLE MODIFY name varchar(20) AFTER thissex;

-- 删除字段
ALTER TABLE MYTABLE DROP thissex;

-- 删除表
drop table mytable;

【第二、三堂】电子产品数据库

题目要求

下面的练习基于一个正在运行的关系数据库。该数据库模式由四个关系组成，它们的模式如下：

Product(maker,model, type)
PC(model,speed,ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)

关系Product给出了各种产品的制造商、型号和产品类型 (PC、laptop或者printer)。为了简单起见，假设型号对于所有的制造商和产品都是唯一的，尽这个假设在实际中不成立，实际的数据库中型号将包含一个代表制造商的代码。关系PC给出了每PC的速度 (处理器，以千兆赫兹为单位)、RAM的大小 (以MB为单位)、硬盘容量 （以GB为单位以及价格。关系Laptop与关系PC类似，它在PC的基础上增加了属性screen，即屏幕的尺寸(以寸为单位)。关系Printer记录了每种类型的打印机的型号，是否为彩色打印机 (如果是彩色，则true)、处理类型(激光或者喷墨打印)以及价格。

请写出下面的定义:
a) 合适的Product关系模式。
b) 合适的PC关系模式。
c) 合适的Laptop关系模式。
d) 合适的Printer关系模式。
e) 从(d)的关系模式中删除属性color。
f) 为©得到的模式增加一个属性od (光驱类型，比如CD、DVD)。如果某个笔记本电脑 （laptop）没有光驱，则该属性的默认值为“none’。

关系模式

a) Product关系模式是：
Product(maker: string, model: string, type: string)
其中，maker是产品的制造商，model是产品的型号，type是产品的类型（PC、laptop或者printer）。model是主键，因为它在所有的制造商和产品中都是唯一的。

b) PC关系模式是：
PC(model: string, speed: float, ram: int, hd: float, price: float)
其中，model是PC的型号，speed是PC的处理器速度（以千兆赫兹为单位），ram是PC的内存大小（以MB为单位），hd是PC的硬盘容量（以GB为单位），price是PC的价格。model是主键，也是Product关系的外键，因为它引用了Product关系中的model属性。

c) Laptop关系模式是：
Laptop(model: string, speed: float, ram: int, hd: float, screen: float, price: float)
其中，model是笔记本电脑的型号，speed是笔记本电脑的处理器速度（以千兆赫兹为单位），ram是笔记本电脑的内存大小（以MB为单位），hd是笔记本电脑的硬盘容量（以GB为单位），screen是笔记本电脑的屏幕尺寸（以寸为单位），price是笔记本电脑的价格。model是主键，也是Product关系的外键，因为它引用了Product关系中的model属性。

d) 合适的Printer关系模式是：
Printer(model: string, color: boolean, type: string, price: float)
其中，model是打印机的型号，color是打印机是否为彩色打印机（如果是彩色，则true），type是打印机的处理类型（激光或者喷墨打印），price是打印机的价格。model是主键，也是Product关系的外键，因为它引用了Product关系中的model属性。

e) 从(d)的关系模式中删除属性color，得到的关系模式是：
Printer(model: string, type: string, price: float)
其中，model是打印机的型号，type是打印机的处理类型（激光或者喷墨打印），price是打印机的价格。model是主键，也是Product关系的外键，因为它引用了Product关系中的model属性。

f) 为©得到的模式增加一个属性od (光驱类型，比如CD、DVD)，得到的关系模式是：
Laptop(model: string, speed: float, ram: int, hd: float, screen: float, price: float, od: string)
其中，model是笔记本电脑的型号，speed是笔记本电脑的处理器速度（以千兆赫兹为单位），ram是笔记本电脑的内存大小（以MB为单位），hd是笔记本电脑的硬盘容量（以GB为单位），screen是笔记本电脑的屏幕尺寸（以寸为单位），price是笔记本电脑的价格，od是笔记本电脑的光驱类型（比如CD、DVD）。如果某个笔记本电脑没有光驱，则该属性的默认值为“none”。model是主键，也是Product关系的外键，因为它引用了Product关系中的model属性。

数据库设计

我选择了Product和Laptop两个表，因为它们包含了不同类型的电子产品的信息。接下来是关于创建数据库、创建表、填充数据、做出查询的相关操作的代码和操作截图。

1.数据库的创建

分别创建Product、Laptop 两张表

2.常用的表操作

查询

创建表

填充表，观察表结构：是用不同方式，例如

  • 文件导入
  • 多行同时导入
  • 特定字段导入
  • …

直接添加

从文件导入

所有字段增加记录

将查询结果插入到表中

从文件中导入表数据可能会遇到问题，问题如下：

  1. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
    这是因为mySQL的权限设置问题，输入 SHOW VARIABLES LIKE "secure_file_priv"; 可以找到默认的安全导入导出路径secure-file-priv 。一般来说，把你需要导入的文件放进去即可。但把其他东西放进去的同时，会导致drop database ebase 删除数据库失败。一个可行的办法是：打开mySQL的 my.ini 文件，找到其中的 secure-file-priv 设置，修改为 secure-file-priv= 就可以。

  2. printer.txt中的布尔数据需要转换。
    由于mySQL中没有布尔类型，所以我的数据库里使用的是 tinyint(1) 来替代。因此当导入 printer.txt 文件中的数据，遇到此类型的数据，需要把 true 和 false 全部转换成 1 和 0，否则就会被识别为字符无法导入。
  3. printer.txt 内字符串不需要打引号。
  4. 导入文件使用绝对路径 LOAD DATA INFILE 'D:/printer.txt' INTO TABLE Printer FIELDS TERMINATED BY ', '; 其中的斜杆使用 /。

修改表结构
修改字段数据类型：将price字段的数据类型从FLOAT改为DECIMAL(10,2)

修改字段名：将color字段的名字改为is_color

增加字段：在表的末尾增加一个字段rating，表示打印机的评分，数据类型为INT

删除字段：从表中删除type字段

增加字段：在表的开头增加一个字段id，表示打印机的编号，数据类型为INT

修改字段排列位置：将rating字段移动到is_color字段之后

删除表

使用.sql命令：导入.sql命令，假设有一个名为ebase.sql的文件，包含了创建数据库、填入数据等操作的代码

3.完整代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
-- 创建EBASE数据库
CREATE DATABASE EBASE;

-- 使用ebase数据库
USE EBASE;

-- 创建Product表
CREATE TABLE Product (
    maker VARCHAR(40),
    model VARCHAR(40) PRIMARY KEY,
    type VARCHAR(10)
);

-- 创建Laptop表
CREATE TABLE Laptop (
    model VARCHAR(40) PRIMARY KEY,
    speed FLOAT,
    ram INT,
    hd FLOAT,
    screen FLOAT,
    price FLOAT,
    od VARCHAR(10),
    FOREIGN KEY (model) REFERENCES Product(model)
);

-- 填充Product表
INSERT INTO Product VALUES
    ('Apple', 'MacBook Air', 'laptop'),
    ('Apple', 'MacBook Pro', 'laptop'),
    ('Apple', 'iPad Pro', 'laptop'),
    ('Lenovo', 'ThinkPad X1', 'laptop'),
    ('Lenovo', 'Yoga Slim 7', 'laptop'),
    ('Lenovo', 'IdeaPad Flex 5', 'laptop'),
    ('Dell', 'XPS 13', 'laptop'),
    ('Dell', 'Inspiron 15', 'laptop'),
    ('Dell', 'Latitude 14', 'laptop'),
    ('HP', 'Spectre x360', 'laptop'),
    ('HP', 'Pavilion 14', 'laptop'),
    ('HP', 'EliteBook 840', 'laptop'),
    ('HP', 'HP LaserJet Pro', 'printer'),
    ('Canon', 'Canon PIXMA TS8320', 'printer'),
    ('Epson', 'Epson EcoTank ET-4760', 'printer'),
    ('Brother', 'Brother HL-L2350DW', 'printer'),
    ('Samsung', 'Samsung Xpress M2020W', 'printer'),
    ('HP', 'HP OfficeJet Pro 9015', 'printer');

-- 填充Laptop表
INSERT INTO Laptop VALUES
    ('MacBook Air', 1.6, 8, 256, 13.3, 999, 'none'),
    ('MacBook Pro', 2.3, 16, 512, 16, 2399, 'none'),
    ('iPad Pro', 2.5, 6, 128, 12.9, 999, 'none'),
    ('ThinkPad X1', 2.4, 16, 512, 14, 1499, 'none'),
    ('Yoga Slim 7', 2.8, 8, 256, 14, 799, 'none'),
    ('IdeaPad Flex 5', 2.1, 8, 256, 14, 599, 'none'),
    ('XPS 13', 2.6, 8, 256, 13.4, 999, 'none'),
    ('Inspiron 15', 2.1, 8, 512, 15.6, 699, 'DVD'),
    ('Latitude 14', 2.3, 8, 256, 14, 899, 'DVD'),
    ('Spectre x360', 2.4, 8, 256, 13.3, 1099, 'none'),
    ('Pavilion 14', 2.1, 8, 256, 14, 599, 'DVD'),
    ('EliteBook 840', 2.3, 8, 256, 14, 999, 'DVD');

-- 查询
-- 查询所有的产品型号和类型
SELECT model, type FROM Product;

-- 查询所有的笔记本电脑的型号、制造商和价格
SELECT L.model, P.maker, L.price 
FROM Laptop L, Product P 
WHERE L.model = P.model;

-- 查询所有没有光驱的笔记本电脑的型号和屏幕尺寸
SELECT model, screen 
FROM Laptop 
WHERE od = 'none';

-- 查询所有价格高于1000的笔记本电脑的型号、速度和内存大小
SELECT model, speed, ram 
FROM Laptop WHERE price > 1000;

-- 创建Printer表
CREATE TABLE Printer (
    model VARCHAR(40) PRIMARY KEY,
    color BOOLEAN,
    type VARCHAR(10),
    price FLOAT,
    FOREIGN KEY (model) REFERENCES Product(model)
);

-- 填充Printer表
-- 用文本方式将数据装入数据库表中
-- 先给出文本文件的内容，文件名为printer.txt，每行代表一条记录，字段之间用逗号分隔
-- 文件内容如下：
-- HP LaserJet Pro,TRUE,laser,199
-- 然后使用LOAD DATA命令将文本文件的数据装入Printer表中
LOAD DATA INFILE 'D:/printer.txt' INTO TABLE Printer FIELDS TERMINATED BY ',';

-- 使用所有字段增加记录
INSERT INTO Printer VALUES
    ('Canon PIXMA TS8320', TRUE, 'ink-jet', 179),
    ('Epson EcoTank ET-4760', TRUE, 'ink-jet', 499),
    ('Brother HL-L2350DW', FALSE, 'laser', 119),
    ('Samsung Xpress M2020W', FALSE, 'laser', 99),
    ('HP OfficeJet Pro 9015', TRUE, 'ink-jet', 229);

-- 使用同时增加多条数据
INSERT INTO Printer (model, color, type, price)
SELECT model, TRUE, 'ink-jet', 299 FROM Product WHERE maker = 'Apple';

-- 使用特定字段增加记录
INSERT INTO Printer (model, color, type)
VALUES
    ('Dell Color Laser Printer', TRUE, 'laser'),
    ('Dell Inkjet Printer', TRUE, 'ink-jet');

-- 使用查询结果插入到表中
INSERT INTO Printer (model, color, type, price)
SELECT model, FALSE, 'laser', 149 FROM Product WHERE maker = 'Lenovo';

-- 修改表结构
-- 修改字段数据类型，将price字段的类型从FLOAT改为DECIMAL(10,2)
ALTER TABLE Printer MODIFY price DECIMAL(10,2);

-- 修改字段名，将type字段改为print_type
ALTER TABLE Printer RENAME COLUMN type TO print_type;

-- 增加字段，增加一个名为resolution的字段，类型为VARCHAR(20)，放在print_type字段后面
ALTER TABLE Printer ADD resolution VARCHAR(20) AFTER print_type;

-- 删除字段，删除color字段
ALTER TABLE Printer DROP color;

-- 修改字段排列位置，将resolution字段移到model字段后面
ALTER TABLE Printer MODIFY resolution VARCHAR(20) AFTER model;

-- 删除表，删除Printer表
DROP TABLE Printer;

-- 导入.sql命令，假设有一个名为ebase.sql的文件，包含了创建数据库、填入数据等操作的代码
SOURCE D:/ebase.sql;

【第四堂、第五堂】基于课本数据的电子产品数据库

该数据库模式由四个关系组成，这四个关系的模式如下:
Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model,speed, ram, hd, screen, price)
Printer(model, color, type, price)

选择使用 product 和 pc 建立数据库

建立数据库

创建数据EPBASE数据库

创建相关表

输入数据，其中
product的数据如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
A,1001,pc
A,1002,pc
A,1003,pc
A,2004,laptop
A,2005,laptop
A,2006,laptop
B,1004,pc
B,1005,pc
B,1006,pc
B,2007,laptop
C,1007,pc
D,1008,pc
D,1009,pc
D,1010,pc
D,3004,printer
D,3005,printer
E,1011,pc
E,1012,pc
E,1013,pc
E,2001,laptop
E,2002,laptop
E,2003,laptop
E,3001,printer
E,3002,printer
E,3003,printer
F,2008,laptop
F,2009,laptop
G,2010,laptop
H,3006,printer
H,3007,printer

pc的数据如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
1001,2.66,1024,250,2114
1002,2.1,512,250,995
1003,1.42,512,80,478
1004,2.8,1024,250,649
1005,3.2,512,250,630
1006,3.2,1024,320,1049
1007,2.2,1024,200,510
1008,2.2,2048,250,770
1009,2,1024,250,650
1010,2.8,2048,300,770
1011,1.86,2048,160,959
1012,2.8,1024,160,649
1013,3.06,512,80,529

表的数据更新
将PC表中model为1001的price更新为1999

表的数据插入
向laptop直接插入product的model没有的数据时会失败，因为存在外键约束

避免laptop插入数据时被外键约束，先在product里插入一条新纪录，model为2011，maker为G，type为laptop

在Laptop表中插入一条新的记录，model为2011，speed为2.5，ram为4096，hd为500，screen为15.6，price为1299

表的数据删除
从Laptop表中删除model为2011的记录

列出表的所有字段
列出Product表的所有字段

列出表的某些字段
列出PC表的model，speed和price字段

查询指定记录
查询Product表中maker为A的记录

带BETWEEN AND的范围查询
查询pc表中speed在2.5和3之间的记录

带LIKE的字符匹配查询
查询Product表中type以p开头的记录

查询空值
查询Printer表中price为空的记录，结果是空

带AND的多条件查询
查询PC表中speed大于2.5且ram小于1024的记录

带OR的多条件查询
查询PC表中speed等于2.00或2.20的记录

查询结果不重复
查询Product表中不重复的maker

排序
查询PC表中的所有记录，并按price降序排序

GROUP BY with 聚集函数
查询Product表中每种type的数量

HAVING
查询Product表中每种type的数量，并只显示数量大于5的记录

使用到五个聚集函数的查询
查询PC表中speed的最大值，最小值，平均值，总和和数量

基于内连接的多表查询
查询Product表和PC表的内连接，显示maker，model，speed和price

基于左外连接的多表查询
查询Product表和Laptop表的左外连接，显示maker，model，speed和price

基于右外连接的多表查询
查询Product表和pc表的右外连接，显示maker，model，ram和price

子查询
查询PC表中price高于平均price的记录

完整代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
-- 创建EPBASE数据库
CREATE DATABASE EPBASE;

-- 选择数据库
USE EPBASE;

-- 创建Product表
CREATE TABLE Product (
    maker VARCHAR(1) NOT NULL,
    model VARCHAR(4) NOT NULL,
    type VARCHAR(20) NOT NULL,
    PRIMARY KEY (model)
);

-- 插入Product表的数据
INSERT INTO Product (maker, model, type) VALUES
('A', '1001', 'pc'),
('A', '1002', 'pc'),
('A', '1003', 'pc'),
('A', '2004', 'laptop'),
('A', '2005', 'laptop'),
('A', '2006', 'laptop'),
('B', '1004', 'pc'),
('B', '1005', 'pc'),
('B', '1006', 'pc'),
('B', '2007', 'laptop'),
('C', '1007', 'pc'),
('D', '1008', 'pc'),
('D', '1009', 'pc'),
('D', '1010', 'pc'),
('D', '3004', 'printer'),
('D', '3005', 'printer'),
('E', '1011', 'pc'),
('E', '1012', 'pc'),
('E', '1013', 'pc'),
('E', '2001', 'laptop'),
('E', '2002', 'laptop'),
('E', '2003', 'laptop'),
('E', '3001', 'printer'),
('E', '3002', 'printer'),
('E', '3003', 'printer'),
('F', '2008', 'laptop'),
('F', '2009', 'laptop'),
('G', '2010', 'laptop'),
('H', '3006', 'printer'),
('H', '3007', 'printer');

-- 创建PC表
CREATE TABLE PC (
    model VARCHAR(4) NOT NULL,
    speed DECIMAL(5,2) NOT NULL,
    ram INT NOT NULL,
    hd DECIMAL(5,1) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (model),
    FOREIGN KEY (model) REFERENCES Product(model)
);

-- 插入PC表的数据
INSERT INTO PC (model, speed, ram, hd, price) VALUES
('1001', 2.66, 1024, 250, 2114),
('1002', 2.1, 512, 250, 995),
('1003', 1.42, 512, 80, 478),
('1004', 2.8, 1024, 250, 649),
('1005', 3.2, 512, 250, 630),
('1006', 3.2, 1024, 320, 1049),
('1007', 2.2, 1024, 200, 510),
('1008', 2.2, 2048, 250, 770),
('1009', 2, 1024, 250, 650),
('1010', 2.8, 2048, 300, 770),
('1011', 1.86, 2048, 160, 959),
('1012', 2.8, 1024, 160, 649),
('1013', 3.06, 512, 80, 529);

-- 创建Laptop表
CREATE TABLE Laptop (
    model VARCHAR(4) NOT NULL,
    speed DECIMAL(5,2) NOT NULL,
    ram INT NOT NULL,
    hd DECIMAL(5,1) NOT NULL,
    screen DECIMAL(4,1) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (model),
    FOREIGN KEY (model) REFERENCES Product(model)
);

-- 插入Laptop表的数据
INSERT INTO Laptop (model, speed, ram, hd, screen, price) VALUES
('2001', 2, 2048, 240, 20.1, 3673),
('2002', 1.73, 1024, 80, 17, 949),
('2003', 1.8, 512, 60, 15.4, 549),
('2004', 2, 512, 60, 13.3, 1150),
('2005', 2.16, 1024, 120, 17, 2500),
('2006', 2, 2048, 80, 15.4, 1700),
('2007', 1.83, 1024, 120, 13.3, 1429),
('2008', 1.6, 1024, 0, 15.4, 900),
('2009', 1.6, 512, 80, 14.1, 680),
('2010', 2, 2048, 160, 15.4, 2300);

-- 创建Printer表
CREATE TABLE Printer (
    model VARCHAR(4) NOT NULL,
    color CHAR(1) NOT NULL,
    type VARCHAR(10) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (model),
    FOREIGN KEY (model) REFERENCES Product(model)
);

-- 插入Printer表的数据
INSERT INTO Printer (model, color, type, price) VALUES
('3001', TRUE, 'ink-jet', 99),
('3002', FALSE, 'laser', 239),
('3003', TRUE, 'laser', 899),
('3004', TRUE, 'ink-jet', 120),
('3005', FALSE, 'laser', 120),
('3006', TRUE, 'ink-jet', 100),
('3007', TRUE, 'laser', 200);

-- 表的数据更新
-- 将PC表中model为1001的price更新为1999
UPDATE PC SET price = 1999 WHERE model = '1001';

-- 表的数据插入
-- 在Product表中插入一条新的记录，model为2011，maker为G，type为laptop
INSERT INTO Product (model, maker, type) VALUES
('2011', 'G', 'laptop');

-- 在Laptop表中插入一条新的记录，model为2011，speed为2.5，ram为4096，hd为500，screen为15.6，price为1299
INSERT INTO Laptop (model, speed, ram, hd, screen, price) VALUES
('2011', 2.5, 4096, 500, 15.6, 1299);

-- 表的数据删除
-- 从Laptop表中删除model为2011的记录
DELETE FROM Laptop WHERE model = '2011';

-- 列出表的所有字段
-- 列出Product表的所有字段
SELECT * FROM Product;

-- 列出表的某些字段
-- 列出PC表的model，speed和price字段
SELECT model, speed, price FROM PC;

-- 查询指定记录
-- 查询Product表中maker为A的记录
SELECT * FROM Product WHERE maker = 'A';

-- 带BETWEEN AND的范围查询
-- 查询pc表中speed在2.5和3之间的记录
SELECT * FROM pc WHERE speed BETWEEN 2.5 AND 3;

-- 带LIKE的字符匹配查询
-- 查询Product表中type以p开头的记录
SELECT * FROM Product WHERE type LIKE 'p%';

-- 查询空值
-- 查询Printer表中price为空的记录
SELECT * FROM Printer WHERE price IS NULL;

-- 带AND的多条件查询
-- 查询PC表中speed大于2.5且ram小于1024的记录
SELECT * FROM PC WHERE speed > 2.5 AND ram < 1024;

-- 带OR的多条件查询
-- 查询PC表中speed等于2.00或2.20的记录
SELECT * FROM PC WHERE speed = 2.00 OR speed = 2.20;

-- 查询结果不重复
-- 查询Product表中不重复的maker
SELECT DISTINCT maker FROM Product;

-- 排序
-- 查询PC表中的所有记录，并按price降序排序
SELECT * FROM PC ORDER BY price DESC;

-- GROUP BY with 聚集函数
-- 查询Product表中每种type的数量
SELECT type, COUNT(*) AS count FROM Product GROUP BY type;

-- HAVING
-- 查询Product表中每种type的数量，并只显示数量大于5的记录
SELECT type, COUNT(*) AS count FROM Product GROUP BY type HAVING count > 5;

-- 使用到五个聚集函数的查询
-- 查询PC表中speed的最大值，最小值，平均值，总和和数量
SELECT MAX(speed) AS max_speed, 
    MIN(speed) AS min_speed, 
    AVG(speed) AS avg_speed, 
    SUM(speed) AS sum_speed, 
    COUNT(speed) AS count_speed 
FROM 
    PC;

-- 基于内连接的多表查询
-- 查询Product表和PC表的内连接，显示maker，model，speed和price
SELECT Product.maker, 
    Product.model, 
    PC.speed, 
    PC.price 
FROM Product 
    INNER JOIN PC ON Product.model = PC.model;

-- 基于左外连接的多表查询
-- 查询Product表和Laptop表的左外连接，显示maker，model，speed和price
SELECT 
    Product.maker, 
    Product.model, 
    Laptop.speed, 
    Laptop.price 
FROM Product 
    LEFT OUTER JOIN Laptop ON Product.model = Laptop.model;

-- 基于右外连接的多表查询
-- 查询Product表和pc表的右外连接，显示maker，model，ram和price
SELECT Product.maker, 
    Product.model, Pc.ram, Pc.price 
FROM Product
    RIGHT OUTER JOIN Pc ON Product.model = Pc.model;

-- 子查询
-- 查询PC表中price高于平均price的记录
SELECT * FROM PC WHERE price > (SELECT AVG(price) FROM PC);

【第六堂】基于电子数据库的查询

6.1.3

题目

基于习题2.4.1给出的数据库模式和数据写出后面的查询语句以及查询结果。
Product(maker, model, type)
PC(model,speed,ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model color, type, price)

结果

a) 找出所有价格低于$1000的个人计算机的型号、速度和硬盘大小

b) 要求同(a)，但要将列speed重命名为gigahertz，并将列hd重命名为gigabytes

c) 找出所有打印机制造厂商。

d) 找出价格高于$1500的笔记本电脑的型号、内存大小和屏幕尺寸。

e)找出关系Printer中所有彩色打印机元组。注意属性color是一个布尔类型。

f) 找出速度为3.2且价格低于$2000的个人计算机的型号和硬盘大小。

完整代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- a) 找出所有价格低于$1000的个人计算机的型号、速度和硬盘大小
SELECT model, speed, hd FROM PC WHERE price < 1000;

-- b) 要求同(a)，但要将列speed重命名为gigahertz，并将列hd重命名为gigabytes
SELECT model, speed AS gigahertz, hd AS gigabytes FROM PC WHERE price < 1000;

-- c) 找出所有打印机制造厂商
SELECT DISTINCT maker FROM Product WHERE type = 'printer';

-- d) 找出价格高于$1500的笔记本电脑的型号、内存大小和屏幕尺寸
SELECT model, ram, screen FROM Laptop WHERE price > 1500;

-- e)找出关系Printer中所有彩色打印机元组。注意属性color是一个布尔类型。
SELECT * FROM Printer WHERE color = TRUE;

-- f) 找出速度为3.2且价格低于$2000的个人计算机的型号和硬盘大小。
SELECT model, hd FROM PC WHERE speed = 3.2 AND price < 2000;

6.2.2

题目

根据习题2.4.1的数据库模式和数据写出下面的查询，并求出查询结果。
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)
a) 查询硬盘容量至少30G的笔记本电脑制造商及该电脑的速度
b) 查询制造商B生产的任意类型的所有产品的型号和价格。
c)查询只卖笔记本电脑不卖PC的厂商。
d)查询出现在两种或两种以 上PC中的硬盘的大小。
e)查询每对具有相同速度和RAM的PC的型号。每一对只能列出一次，例如，若 (i,j) 已被列出则 (i.i) 就不能再被列出。
f)查询生产至少两种速度至少为3.0的电脑 (PC或笔记本电脑) 的厂商。

结果

a) 查询硬盘容量至少30G的笔记本电脑制造商及该电脑的速度

b) 查询制造商B生产的任意类型的所有产品的型号和价格。

c)查询只卖笔记本电脑不卖PC的厂商。

d)查询出现在两种或两种以 上PC中的硬盘的大小。

e)查询每对具有相同速度和RAM的PC的型号。每一对只能列出一次，例如，若 (i,j) 已被列出则 (i.i) 就不能再被列出。

f)查询生产至少两种速度至少为3.0的电脑 (PC或笔记本电脑) 的厂商。

完整代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- a) 查询硬盘容量至少30G的笔记本电脑制造商及该电脑的速度
SELECT maker, speed FROM Product INNER JOIN Laptop ON Product.model = Laptop.model WHERE hd >= 30;

-- b) 查询制造商B生产的任意类型的所有产品的型号和价格。
SELECT model, price
from
    (
        select model, price 
            from pc
        union 
        select model, price 
            from laptop
    union
    select model, price 
        from printer
    ) a
where model in (
    select model from product where maker='B')

-- c)查询只卖笔记本电脑不卖PC的厂商。
SELECT maker 
FROM Product 
WHERE type = 'laptop' 
    AND maker NOT IN (SELECT maker FROM Product WHERE type = 'pc');

-- d)查询出现在两种或两种以 上PC中的硬盘的大小。
SELECT hd FROM PC GROUP BY hd HAVING COUNT(*) >= 2;

-- e)查询每对具有相同速度和RAM的PC的型号。每一对只能列出一次，例如，若 (i,j) 已被列出则 (i.i) 就不能再被列出。
SELECT 
    p1.model, 
    p2.model 
FROM 
    PC p1, 
    PC p2 
    WHERE p1.speed = p2.speed 
        AND p1.ram = p2.ram 
        AND p1.model < p2.model;

-- f)查询生产至少两种速度至少为3.0的电脑 (PC或笔记本电脑) 的厂商。
SELECT maker 
FROM
    (
        SELECT 
            a.maker,
            b.speed 
        FROM product a 
            JOIN pc b ON a.model=b.model
        GROUP BY b.speed,a.maker
    )a
GROUP BY 
    maker 
HAVING 
    COUNT(*)>=2

6.3.1

题目

基于习题2.4.1的数据库模式写出后面的查询。
Product(maker, model, type)
PC(model, speed, ram, hd, price)Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)

每题的答案应当至少使用一个子查询，并且要求使用两种不同的方法写出每个查询(例如，使用不同运算符EXISTS、IN、ALL和ANY)。
a)找出速度在3.0以上的PC制造商。
b) 找出价格最高的打印机。
c) 找出速度比任何一台PC都慢的笔记本电脑。
d)找出价格最高的产品 (PC、笔记本电脑或打印机)的型号
!e) 找出价格最低的彩色打印机的制造商。
!!f)找出RAM容量最小而PC中速度最快者的制造商。

结果

a)找出速度在3.0以上的PC制造商。

b) 找出价格最高的打印机。

c) 找出速度比任何一台PC都慢的笔记本电脑。


d)找出价格最高的产品 (PC、笔记本电脑或打印机)的型号

!e) 找出价格最低的彩色打印机的制造商。

!!f)找出RAM容量最小而PC中速度最快者的制造商。

完整代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
-- a)找出速度在3.0以上的PC制造商。
-- 方法一：使用IN运算符
SELECT DISTINCT maker 
FROM Product 
WHERE model IN (SELECT model FROM PC WHERE speed > 3);

-- 方法二：使用EXISTS运算符
SELECT DISTINCT maker 
FROM Product p 
WHERE EXISTS (SELECT * FROM PC WHERE speed > 3 AND model = p.model);

-- b) 找出价格最高的打印机。
-- 方法一：使用MAX函数
SELECT * FROM 
Printer 
WHERE price = (SELECT MAX(price) FROM Printer);

-- 方法二：使用ALL运算符
SELECT * 
FROM Printer 
WHERE price >= ALL (SELECT price FROM Printer);

-- c) 找出速度比任何一台PC都慢的笔记本电脑。
-- 方法一：使用MIN函数
SELECT * 
FROM Laptop 
WHERE speed < (SELECT MIN(speed) FROM PC);

-- 方法二：使用ANY运算符
SELECT * FROM Laptop WHERE speed < ANY (SELECT speed FROM PC);

-- d)找出价格最高的产品 (PC、笔记本电脑或打印机)的型号
-- 方法一：使用UNION和MAX函数
-- 使用UNION和MAX函数
SELECT
    model
FROM
    (
        SELECT model, price
        FROM PC
        UNION
        SELECT model, price
        FROM Laptop
        UNION
        SELECT model, price
        FROM Printer
    ) AS t
WHERE
    price = (
        SELECT
            MAX(price)
        FROM
            (
                SELECT price
                FROM PC
                UNION
                SELECT price
                FROM Laptop
                UNION
                SELECT price
                FROM Printer
            ) AS p
    );



-- 方法二：使用UNION和ALL运算符
SELECT model 
FROM 
    (
        SELECT model, price 
        FROM PC 
        UNION SELECT model, price 
        FROM Laptop 
        UNION SELECT model, price 
        FROM Printer
    ) AS t 
WHERE price >= ALL (SELECT price FROM t);

-- e) 找出价格最低的彩色打印机的制造商。
-- 方法一：使用JOIN和MIN函数
SELECT maker 
FROM Product JOIN Printer ON Product.model = Printer.model 
WHERE color = TRUE 
    AND price = (
        SELECT MIN(price) 
        FROM Printer 
        WHERE color = TRUE
    );

-- 方法二：使用IN运算符
SELECT maker 
FROM product,printer
WHERE product.model=printer.model 
AND color='1' 
AND price in (
    select min(price)
    from product,printer
    where product.model=printer.model
    and color='1'
);

-- f)找出RAM容量最小而PC中速度最快者的制造商。
-- 方法一：使用JOIN和MIN/MAX函数
SELECT maker 
FROM Product JOIN PC ON Product.model = PC.model 
WHERE ram = (SELECT MIN(ram) FROM PC) 
    AND speed = (SELECT MAX(speed) FROM PC);

-- 方法二：使用IN运算符
SELECT DISTINCT maker
FROM product
WHERE model IN 
		(
		SELECT model 
		FROM pc 
		WHERE speed>=ALL(
			SELECT speed
			 FROM pc
			WHERE ram <=ALL (
			SELECT ram 
			FROM pc
			)
		)
		AND
		ram <= ALL(
			SELECT ram 
			FROM pc
		)
);


6.4.6

题目

a) 查询PC的平均速度。
b) 查询价格高于$1000的笔记本电脑的平均速度。
c) 查询厂商“A”生产的PC的平均价格。
d)查询厂商“D”生产的PC和笔记本电脑的平均价格。
e) 查询每种不同速度的PC的平均价格。
! f) 查询每家厂商生产的笔记本电脑的屏幕尺寸的平均值。
!g) 查询至少生产三种不同型号PC的制造商。
! h) 查询每个销售PC的广商的PC的最高价格。
!i) 查询每种高于2.0速度的PC的平均价格。
!!) 查询所有生产打印机的厂商生产的PC的硬盘容量的平均大小。

结果

a) 查询PC的平均速度。

b) 查询价格高于$1000的笔记本电脑的平均速度。

c) 查询厂商“A”生产的PC的平均价格。

d)查询厂商“D”生产的PC和笔记本电脑的平均价格。

e) 查询每种不同速度的PC的平均价格。

! f) 查询每家厂商生产的笔记本电脑的屏幕尺寸的平均值。

!g) 查询至少生产三种不同型号PC的制造商。

! h) 查询每个销售PC的广商的PC的最高价格。

!i) 查询每种高于2.0速度的PC的平均价格。

!j) 查询所有生产打印机的厂商生产的PC的硬盘容量的平均大小。

完整代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- a) 查询PC的平均速度。
SELECT AVG(speed) AS avg_speed FROM PC;

-- b) 查询价格高于$1000的笔记本电脑的平均速度。
SELECT AVG(speed) AS avg_speed FROM Laptop WHERE price > 1000;

-- c) 查询厂商“A”生产的PC的平均价格。
SELECT AVG(price) AS avg_price FROM Product JOIN PC ON Product.model = PC.model WHERE maker = 'A';

-- d)查询厂商“D”生产的PC和笔记本电脑的平均价格。
SELECT round(AVG(price)) AS avg_price
    FROM (
    SELECT price
        FROM product,pc
        WHERE product.model = pc.model
        AND maker = 'D'
    UNION ALL
    SELECT price
        FROM product, laptop
        WHERE product.model = laptop.model   
        AND maker = 'D'
    ) temp;

-- e) 查询每种不同速度的PC的平均价格。
SELECT speed, AVG(price) AS avg_price 
FROM PC 
GROUP BY speed;

-- f) 查询每家厂商生产的笔记本电脑的屏幕尺寸的平均值。
SELECT maker, AVG(screen) AS avg_screen 
FROM Product JOIN Laptop ON Product.model = Laptop.model 
GROUP BY maker;

-- g) 查询至少生产三种不同型号PC的制造商。
SELECT maker 
FROM Product 
WHERE type = 'pc' 
GROUP BY maker 
    HAVING COUNT(DISTINCT model) >= 3;

-- h) 查询每个销售PC的广商的PC的最高价格。
SELECT maker, MAX(price) AS max_price 
FROM Product JOIN PC ON Product.model = PC.model 
GROUP BY maker;

-- i) 查询每种高于2.0速度的PC的平均价格。
SELECT speed, AVG(price) AS avg_price 
FROM PC 
WHERE speed > 2 
GROUP BY speed;

-- j) 查询所有生产打印机的厂商生产的PC的硬盘容量的平均大小。
SELECT maker, AVG(hd) AS avg_hd 
FROM Product JOIN PC ON Product.model = PC.model 
    WHERE maker IN (SELECT maker FROM Product WHERE type = 'printer') 
    GROUP BY maker;

【第七堂】数据库设计

自行定义一个数据库模型，完成以下任务：

  1. 对该数据库功能的文字描述，相关条件及约束
  2. 画E/R图
  3. 转化为关系表
  4. 输入该模型进入mysql
  5. 进行相关操作查询操作的展示
    要求：至少要包含三张关系表，每个表至少包含三个元组，进行至少五次不同的查询操作且要包括将三张表连接的查询操作

数据库功能描述、相关条件及约束

我定义了一个图书管理系统的数据库模型，用于存储和查询图书馆的图书、读者和借阅信息。该数据库模型的相关条件和约束如下：

  • 图书表（Book）包含图书的编号（Bno）、书名（Bname）、作者（Author）、出版社（Publisher）、出版日期（PubDate）、价格（Price）和库存量（Stock）等属性，其中图书编号是主键，不可为空，且唯一。
  • 读者表（Reader）包含读者的编号（Rno）、姓名（Rname）、性别（Gender）、年龄（Age）、电话（Phone）和借阅次数（BorrowTimes）等属性，其中读者编号是主键，不可为空，且唯一。
  • 借阅表（Borrow）包含借阅的编号（Lno）、图书编号（Bno）、读者编号（Rno）、借阅日期（BorrowDate）、归还日期（ReturnDate）和逾期罚款（Fine）等属性，其中借阅编号是主键，不可为空，且唯一，图书编号和读者编号是外键，分别参照图书表和读者表的主键。
  • 图书表和读者表之间是多对多的关系，通过借阅表实现。
  • 借阅表的归还日期必须大于或等于借阅日期，逾期罚款根据归还日期和借阅日期的差值计算，每超过一天罚款0.5元。
  • 读者表的借阅次数是根据借阅表的记录统计的，每借阅一本图书，借阅次数加一。
  • 图书表的库存量是根据借阅表的记录更新的，每借出一本图书，库存量减一，每归还一本图书，库存量加一。

ER图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
erDiagram
    Book ||--o{ Borrow : has
    Reader ||--o{ Borrow : borrows
    Book {
        Bno string
        Bname string
        Author string
        Publisher string
        PubDate date
        Price float
        Stock int
    }
    Reader {
        Rno string
        Rname string
        Gender string
        Age int
        Phone string
        BorrowTimes int
    }
    Borrow {
        Lno string
        Bno string
        Rno string
        BorrowDate date
        ReturnDate date
        Fine float
    }

ER图转化为关系表

Book(Bno, Bname, Author, Publisher, PubDate, Price, Stock)

Borrow(Lno, Bno, Rno, BorrowDate, ReturnDate, Fine)

Reader(Rno, Rname, Gender, Age, Phone, BorrowTimes)

输入数据进mySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- 创建数据库
CREATE DATABASE LIBRARY;

-- 创建图书表
CREATE TABLE Book (
    Bno VARCHAR(10) PRIMARY KEY, -- 图书编号
    Bname VARCHAR(50) NOT NULL, -- 书名
    Author VARCHAR(20) NOT NULL, -- 作者
    Publisher VARCHAR(50) NOT NULL, -- 出版社
    PubDate DATE NOT NULL, -- 出版日期
    Price DECIMAL(10, 2) NOT NULL, -- 价格
    Stock INT NOT NULL -- 库存量
);

-- 创建读者表
CREATE TABLE Reader (
    Rno VARCHAR(10) PRIMARY KEY, -- 读者编号
    Rname VARCHAR(20) NOT NULL, -- 姓名
    Gender VARCHAR(10) NOT NULL, -- 性别
    Age INT NOT NULL, -- 年龄
    Phone VARCHAR(20) NOT NULL, -- 电话
    BorrowTimes INT NOT NULL -- 借阅次数
);

-- 创建借阅表
CREATE TABLE Borrow (
    Lno VARCHAR(10) PRIMARY KEY, -- 借阅编号
    Bno VARCHAR(10) NOT NULL, -- 图书编号
    Rno VARCHAR(10) NOT NULL, -- 读者编号
    BorrowDate DATE NOT NULL, -- 借阅日期
    ReturnDate DATE NOT NULL, -- 归还日期
    Fine DECIMAL(10, 2) NOT NULL, -- 逾期罚款
    FOREIGN KEY (Bno) REFERENCES Book (Bno), -- 外键约束
    FOREIGN KEY (Rno) REFERENCES Reader (Rno) -- 外键约束
);

-- 插入图书数据
INSERT INTO Book VALUES
('B001', '数据库系统概念', 'Abraham Silberschatz', '机械工业出版社', '2019-01-01', 79.00, 10),
('B002', '计算机网络', 'Andrew S. Tanenbaum', '机械工业出版社', '2018-01-01', 89.00, 8),
('B003', '数据结构与算法分析', 'Mark Allen Weiss', '机械工业出版社', '2017-01-01', 69.00, 6),
('B004', '编译原理', 'Alfred V. Aho', '机械工业出版社', '2016-01-01', 99.00, 4);

-- 插入读者数据
INSERT INTO Reader VALUES
('R001', '张三', '男', 20, '13800000001', 0),
('R002', '李四', '女', 21, '13800000002', 0),
('R003', '王五', '男', 22, '13800000003', 0),
('R004', '赵六', '女', 23, '13800000004', 0);

-- 插入借阅数据
INSERT INTO Borrow VALUES
('L001', 'B001', 'R001', '2023-11-01', '2023-11-15', 0.00),
('L002', 'B002', 'R002', '2023-11-02', '2023-11-16', 0.00),
('L003', 'B003', 'R003', '2023-11-03', '2023-11-17', 0.00),
('L004', 'B004', 'R004', '2023-11-04', '2023-11-18', 0.00),
('L005', 'B001', 'R002', '2023-11-05', '2023-11-19', 0.00);

建立好数据库并且完成三张表的创建、数据的输入后，查看数据库如下：

至此，数据库创建成果，数据输入完成。

查询操作及结果展示

主要进行了以下五种查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询图书表中所有图书的信息
SELECT * FROM Book;

-- 查询读者表中年龄大于等于22的读者的姓名和电话
SELECT Rname, Phone FROM Reader WHERE Age >= 22;

-- 查询借阅表中逾期罚款大于0的借阅记录的编号和罚款金额
SELECT Lno, Fine FROM Borrow WHERE Fine > 0;

-- 查询图书表和借阅表连接后，借阅日期在2023年11月2日之后的图书的书名和借阅日期
SELECT Bname, BorrowDate FROM Book JOIN Borrow ON Book.Bno = Borrow.Bno WHERE BorrowDate > '2023-11-2';

-- 查询图书表、读者表和借阅表连接后，读者姓名为李四的图书的书名和作者
SELECT Bname, Author FROM Book JOIN Borrow ON Book.Bno = Borrow.Bno JOIN Reader ON Borrow.Rno = Reader.Rno WHERE Rname = '李四

  1. 查询图书表中所有图书的信息

  2. 查询读者表中年龄大于等于22的读者的姓名和电话

  3. 查询借阅表中逾期罚款大于0的借阅记录的编号和罚款金额

不存在逾期罚款大于0，所以结果为空。修改记录，让张三逾期罚款变成5，输入UPDATE Borrow set Fine = 5 WHERE Rno = 'R001'，可以看到有记录被修改。

使用查询检查，证实确实得到修改

再做一次查询3，可以得到结果如下：

  1. 查询图书表和借阅表连接后，借阅日期在2023年11月10日之后的图书的书名和借阅日期

  2. 查询图书表、读者表和借阅表连接后，读者姓名为李四的图书的书名和作者