MySQL系列（五）：MySQL进阶实验之数据库高级查询与更新

实验目的：掌握基本的SQL命令和语法，熟练掌握子查询、连接查询、嵌套查询、组函数等技术查询。

实验说明：本实验以《MySQL系列（四）：MySQL进阶实验之数据库查询》为先导实验。

实验内容：按照下文中的步骤，在MySQL中执行SQL语句，逐一学习体会各SQL语句的用法。

6.3 Subqueries

--6.3.1 Subqueries that Produce Scalar Values

• 查询电影“Star Wars”的出品方的名字：

这里使用了子查询，先在movies表中查找Star Wars的出品方的cert，再在movieexec表中查找与之匹配的cert对应的name。从结果上看，也可以通过将movies表与movieexec表联接实现查询。

--6.3.2 Conditions Involving Relations

--6.3.3 Conditions Involving Tuples

• 查询姓名为“Harrison Ford”的明星参演的电影的出品方的名字：

这里使用了子查询，并且嵌套了两层。先在starsin表中找到该明星参演电影的信息，然后在movies表中找到与之匹配的记录，从而得到出品方的cert，再在movieexec表中查询name。

• 通过联接查询姓名为“Harrison Ford”的明星参演的电影的出品方的名字：

这里没有使用子查询的方式，而是通过将三个表格联接。可以看到，查询结果为两行重复的记录，体现出子查询与联接查询的区别所在。如果不需要显示重复记录，需要加DISTINCT。

--6.3.4 Correlated Subqueries

• 查找有多个版本的电影中的老版本的名字与年代。

我们先来看一下movies表格：

First Blood电影有四个版本，其中年代为2008的版本为最新版本，查询结果为其余三个版本的名称与年代。SQL语句使用了子查询，对于一个记录，只要在movies表中找到与它同名但year比它大的记录即满足条件。关键字any代表“存在，任何一个”，也就是当前记录的year小于某一个与之同名的记录的year。

--6.3.5 Subqueries in from Clauses

• 查询姓名为“Harrison Ford”的明星出演的所有电影的出品方，如果有重复便输出多次：

prod表为该明星出演过的电影构成的表格，将movieexec表与prod作笛卡尔积，对于cert与prodecerc字段相同的记录即为可联接的记录。因为笛卡尔积包括所有联接的结果，因此重复的记录会输出多次。

SQL中各种联接：

• 交叉联接CROSS JOIN：如果不带WHERE条件子句，它将会返回被联接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积；如果有WHERE条件，先生成两个表行数乘积的行的数据表，然后根据WHERE条件从中选择，效率较低。
• 自然联接NATURAL JOIN：在两张表中查找数据类型和列名都相同的字段并联接。如果两个表中有多个字段满足数据类型与列名相同，那么它们都会被作为自然连接的条件。
• 内联接INNER JOIN：两边表同时符合条件的组合，通常情况下需要指定联接条件。
• 外联接OUTER JOIN：外连接返回的结果不仅包含符合连接条件的行，而且包括左表(左外联接时), 右表(右联接时)或者两边连接(全外联接时)的所有数据行。

1. 左外连接LEFT [OUTER] JOIN：显示符合条件的数据行，同时显示左边数据表不符合条件的数据行，右边没有对应的条目显示NULL。
2. 右外连接RIGHT [OUTER] JOIN：显示符合条件的数据行，同时显示右边数据表不符合条件的数据行，左边没有对应的条目显示NULL。
3. 全外连接FULL [OUTER] JOIN：显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL，即显示左连接、右连接和内连接的并集。

--6.3.6 SQL Join Expressions

• 查询movies表与starsin表的笛卡尔积：

…

未指定WHERE条件的交叉联接，即笛卡尔积。

• 查询movies表与starin表自然联接的结果：

通过on指定联接字段（外键约束），从而得到自然联接结果。

• 查询movies表与starin表自然联接的结果中的title、year、length、genre、studioName、producerc、starname字段：

通过on指定联接字段（外键约束），从而得到自然联接结果；通过select指定输出字段。

-6.3.7 Natural Joins

• 将movies表与starsin表自然联结：

由于不需要指定自然联接条件，因此按照列名与数据类型都相同的字段联接。由于两张表不存在符合联接条件的字段，因此得到笛卡尔积运算结果。

• 将moviestar表与movieexec表自然联结：

由于不需要指定自然联接条件，因此按照列名与数据类型都相同的字段联接。由于两张表中的name与address字段符合条件，因此根据这两个字段做自然联接。

-6.3.8 Outer joins

• 将movieexec表与moviestar表左联接的结果与右联接的结果合在一起输出，联接字段为name：

可以看到，左联接输出左表的每一行，右联接输出右表的每一行，若另一表中无记录对应，该表格的全部字段显示为NULL。

• 不指定联接字段，将movieexec表与moviestar表左联接，观察能否输出结果：

可以看到，输出错误信息，表明左联接不同于自然联结，一定要指定联接字段。

• 不指定联接字段，将movieexec表与moviestar表左联接，观察能否输出结果：

可以看到，输出错误信息，表明右联接不同于自然联结，一定要指定联接字段。

• 将movies表与starin表左联接的结果与右联接的结果合在一起输出，联接字段为左表的title、year分别对应右表的movietitle、movieyear：

由于starin表（10条记录）包含在movies表（11条记录）中，因此查询结果包括11条记录，一条记录中starin表对应字段为NULL。

• 将movies表与starin表左联接，联接字段为左表的titile、movietitle分别对应右表的year、movieyear：

• 将movies表与starin表右联接，联接字段为左表的titile、movietitle分别对应右表的year、movieyear：

-6.3.9 Exercises for Section 6.3

exercise 6.3.1

-a) find the makers of PC''s with a speed of at least 3.0

答：select distinct maker from product where model in(select model from pc where speed>3.0);

-b) find the printer with the highest price

答：select model from printer where price=(select max(price) from printer);

-c) find the laptops whose speed is slower than that of any pc

答：select model from laptop where speed<(select max(speed) from pc);

注：any为“任何，任何一个”。

-d) find the model number of the item(PC, laptop, or printer) with the highest price

答：select model,price

from ((select model,price from pc)

union (select model,price from laptop)

union (select model,price from printer))

where price=

(select max(price) from ((select model,price from pc)

union (select model,price from laptop)

union (select model,price from printer)));

Every derived table must have its own alias，也就是说进行嵌套查询的时候子查询出来的结果是作为一个派生表来进行上一级的查询的，所以子查询的结果必须要有一个别名。

改：select model,price

from ((select model,price from pc)

union (select model,price from laptop)

union (select model,price from printer)) as t1

where price=

(select max(price) from ((select model,price from pc) union

(select model,price from laptop)

union (select model,price from printer)) as t2);

-e) find the maker if the color printer with the lowest price

答：select maker

from product

where model in

(select model from printer where color= 'true'

and price<=(select price from printer where color= 'true'));

改为：select maker

from product

where model in

(select model from printer where color= 'true'

and price<=all(select price from printer where color= 'true'));

-f) find the maker(s) of the PC(s) with the fastest processor among all those PC"s that have the smallest amount of RAM.

答：select model,ram from pc as pc1

where pc1.ram=(select min(ram) from pc)

and pc1.speed>=all

(select pc2.speed from pc as pc2

where pc1.ram=pc2.ram);

6.4 Full-Relation Operations

--6.4.1 Eliminating Duplicates

• 查询movies表中的不同的标题：

如果去掉DISTINCT，那么First Blood将出现四次。

-6.4.2 Duplicates in Unions, Intersections, and Differences

1. UNION：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。
2. UNION ALL：对两个结果集进行并集操作，包括重复行，不进行排序。

• 查询movie表中全部记录的title、year，查询starin表中所有记录的movietitle、movieyear，对两个结果集进行不去重的并集操作：

由于UNION ALL结果包括重复行，因此得到21条记录(11+10，有重复)，按照在两个表格中的原顺序排序。

--6.4.4 Aggregation Operators

• 查询movieexec表中所有记录networth的平均值：

使用聚集函数avg实现平均值查询，输出结果为一行记录，可视为一个常量。

• 查询starin表中的记录数目：

使用聚集函数count实现统计，count(*)代表统计行数。

• 查询starin表中starname的数目：

使用聚集函数count(字段名称)实现统计，可以看到该统计不去重，在这里结果与count(*)一致。

• 查询starin表中不同的starname的数目：

可以看到，在count(字段名称)的基础上，使用distinct实现去重统计。

--6.4.5 Grouping

• 查询所有制片厂的制片总长度：

在movies表中，使用group by进行分组，按照studioname分组之后统计sum(length)。

• 在movies表中查询不同的制片厂的名字：

这里相当于SELECT DISTINCT studioname，使用GROUP BY studioname实现相同效果，体现了GROUP BY的原理。

• 在movies表中查询不同的制片厂的名字：

这里既使用了DISTINCT，又使用了GROUP BY，实现相同的效果。

6.4.6 Grouping, Aggregation, and Nulls

• 创建一个名为temp_a的表，有两个字段：a和b，数据类型都是varchar(8)：

• 向该表中插入一行记录，该记录全部字段为NULL。

• 查询tmp_a表中的全部记录：

可以看到刚刚插入的记录。

• 查询tmp_a表中b字段的记录数量：

可以看到COUNT(b)为0，表明COUNT不会将NULL统计在内。

--6.4.7 HAVING Clauses

• 查询在1985年之前发行过电影的制片厂的电影总时长：

要求制片厂发行过1986年之前的电影，也就是制片厂发行的所有电影的发行时间最小值小于1985，因此min(year)<1985放在HAVING之后。

• 查询制片厂1985年之前发行的所有电影的总时长：

要求电影为1985年之前，因此year<1985要放在WHERE之后。

6.4.8 Exercises for Section 6.4

exercise 6.4.6

-a) find the average speed of pc''s

答：select avg(speed) from pc;

-b) find the average speed of laptops costing over S1000.

答：select avg(speed) from laptop where price>1000;

-c) find the average price of Pc"s made by manufacturer "A"

答：select avg(price) from

(pc natural join product)

where maker= 'a';

-d) find the average price of pc"s and laptops made by maufacturer "D''.

答：select avg(price) from

 (((select model,price from pc)

union (select model,price from laptop)) as t1

natural join product)

where maker='a';

-e) find, for each diffrent speed, the average price of a pc.

答：select speed,avg(price) from pc group by speed;

-f) find for each maufacturer, the average screen size of its laptops.

答：select maker,avg(screen)

from laptop natural join product group by maker;

-g) find the maufacturer that make at least three diffrent models of PC.

答：select maker,count(*)

from pc natural join product

group by maker having count(*)>=3;

-h) find for each mamfacturer who sells PC"s the maximum price of a PC.

答：select maker,max(price)

from pc natural join product group by maker;

-i) find, for each speed of PC above 2.0, the average price.

答：select speed,avg(price) from pc group by speed;

-j) find the average hard disk size of a PC for all those manmufacturers that make printers.

答：select maker,avg(hd) from

pc natural join product where maker in

(select maker from product where type= 'printer')

group by maker;

6.5 Database Modifications

--6.5.1 Insertion

• 通过指定字段值插入记录：

1. 首先，再插入记录之前，我们查看一下starsin表：

1. 向starsin表中插入一条记录，movietitle为The maltese falcon，movieyear为1942，starname为Sydney Greenstreet：

1. 我们再次查看starsin表中的内容：

可以看到刚刚的记录已经插入。

• 通过select子查询插入记录：

1. 在插入记录之前，我们查看studio表中的内容：

1. 向studio表中插入movies表中存在但studio表中不存在的记录：

1. 插入记录之后，我们再次查看studio表中的内容：

--6.5.2 Deletion

• 删除starsin表中指定的记录：

1. 删除记录之前，我们查看starsin表中的记录：

1. 删除starsin表中指定的记录，指定要删除的记录的movietitle、movieyear、starname：

1. 删除记录之后，我们再次查看starsin表中的记录：

可以看到，starsin表中已不存在刚刚删除的记录。

--6.5.3 Updates

• 更新movieexec表中符合条件的字段：

1. 在更新之前，我们查看movieexec表中的记录：

1. 更新movieexec中的cert字段值存在于studio表中的记录，将对应记录的name修改为“Pres.”。

发现更新记录数目为0。于是，查看studio表中的presc字段：

--6.5.4 Exercises for Section 6.5

execise6.5.1

a) using two insert statement, store in the database the fact that PC model 1100 is made by manufacturerC, has speed 3.2, RAM 1024, hard disk 180, and sells for $2499.

答：insert into pc values(1100,3.2,1024,180,2499);

insert into product values('C',1100, 'pc');

b) insert the facts that for every PC there is a laptop with the same manufacturer, speed, RAM, and hard disk, a 17-inch screen,a model nummber 1100 greater, and a price $500 more.

答：insert into laptop(model,speed,ram,hd,screen,price)

select model+1100,speed,ram,hd,17,price+500 from pc;

insert into product(maker,model,type)

select maker,model+1100,'laptop' from pc natural join product;

c) delete all PC''s with less than 100 gigabytes of hard disk.

答：delete from pc where hd<100;

d) delete all laptops made by a manufacturer that doesn''t make printers.

答：delete from laptop where model not in

(select model from product as p1

where exists (select * from product as p2

where p1.maker=p2.maker and p2.type= 'printer') );

e) manufacturer A buys manufacturer B. change all products made by B so they are now made by A.

答：update product set maker='a' where maker='b';

f) for each PC, double the amount of RAM and add 60 gigabytes to the amount of hard disk.

答：update pc set ram=ram*2, hd=hd+60;

g) for each laptop made by manufacturer B, add one inche to the screen size and substract $100 from the price.

答：update laptop set price=price-100, screen=screen+1

where model in(select model from product where maker= 'b');

就在刚刚，manufacturer A buys manufacturer B，所以不存在laptop made by manufacturer B。

为检验SQL语句正确性，改为修改厂商E研发的笔记本电脑：

update laptop set price=price-100, screen=screen+1

where model in(select model from product where maker= 'e');