mysql数据库 三章_Mysql 数据库入门第三章

Mysql的多表查询简介Tips: 本章使用的s数据库文件

●单表查询和多表查询

单表查询:从一张表中查询数据

多表查询:从多张表中联合查询出数据

●为什么要有多张表?

Java中：一个类中描述一类事物的特征，不同的事物使用不同的类型(类)来表示.

数据库中：一张表中存储一类事物的数据,不同事物使用不同的表存储.

●为什么需要多表查询?

因为有的时候不同类别的数据存在于不同的表中，我们通过一张表不能查询出我们想要的完整的数据，那么就需要联合查询多张表以获得我们想要的数据

●语法单表查询:

SELECT 列名1,列名2,列名3…FROM 表名多表查询(最简单的): – 会有笛卡尔积

SELECT 列名1,列名2,列名3… FROM 表名A , 表名B – 会有笛卡尔积

需求:查询商品名称及其对应的分类名称1select productName,dirName from product,productdir;

思考为什么产生了88条数据:

笛卡尔积

●什么是笛卡尔积

假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。

笛卡尔积的现实意义:如果A表示学生的集合，B表示所有课程的集合，则A与B的笛卡尔积表示所有可能出现的选课情况。

A(张三,李四)

B(Java,PHP,C++)

AXB(张三,Java), (张三,PHP), (张三,C++), (李四,Java), (李四,PHP), (李四,C++),

●如何避免笛卡尔积

但是在进行多表查询时,没有连接条件的表关系返回的笛卡尔积大多数时候是无意义的。我们在开发中，应避免使用笛卡尔积，如何避免？

解决方案: 在WHERE加入有效的连接条件—->等值连接

注意:连接 n张表,至少需要 n-1个连接条件。

多表查询(最简单的):

SELECT 列名1,列名2,列名3… FROM 表名A , 表名B where 条件 – 使用where条件去除笛卡尔积

– 再次完成需求:查询商品名称及其对应的分类名称1select productName,dirName from product,productDir where product.dir_id = productDir.id;

出现了88条数据

外键约束

● 什么是外籍约束

主键约束(PRIMARY KEY): 约束在当前表中,指定列的值非空且唯一(主键列).

外键约束(FOREIGN KEY): A表中的外键列的值必须来源于于B表中的主键列.(理解)

● 注意:在MySQL中,InnoDB支持事务和外键.

● 如何修改表的存储引擎

1) 使用命令: ALTER TABLE 表名 ENGINE=’InnoDB’;

2) 使用图形化界面:

● 给表设置外键(先修改两张表的存储引擎为InnoDB)

(在实际开发中如果对效率要求比较高，那么一般不会给表加外键约束)

● tips:在开发中，外键列起名字一般是所引用表名_引用列名：productdir_id，有时候也运行简写： dir_id

● 增加外键 增加外键之前要确定是否满足约束条件,否则不能关联1ALTER TABLE `product` ADD CONSTRAINT `productdir_id` FOREIGN KEY (`dir_id`) REFERENCES `productdir` (`id`);

● 删除外键1ALTER TABLE `product` DROP FOREIGN KEY `productdir_id`;

隐式内连接

– 内连接查询

– 内连接：分为隐式内连接和显示内连接(高级部分讲)，它们的查询效果相同。有内连接肯定有外连接(高级部分讲)。

– ●隐式内连接：

– SELECT列名1,列名2… FROM 表A ,表B WHERE 表A.列名 = 表B.列名

– 需求: 查询商品的商品id,商品名称和分类名称:

– 需求: 查询零售价大于200的无线鼠标(使用表的别名)

– 需求: 查询每个货品对应的分类以及对应的库存(三张表)

– 需求: 如果库存货品都销售完成,按照利润从高到低查询货品名称,零售价,货品分类(三张表). 销售完成利润 : (售价-成本价)*库存

– 总结:

– 可以使用表名前缀区分多个表中的相同列。

– 可以使用别名可以简化查询。

– 如果使用了表的别名，则不能再使用表的真名

– 使用表名前缀可以提高执行效率。

答案在下面,请粘贴到sql客户端工具执行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-- 内连接查询

-- 内连接：分为隐式内连接和显示内连接(高级部分讲)，它们的查询效果相同。有内连接肯定有外连接(高级部分讲)。

-- -----------------------------------------------

-- ●隐式内连接：

-- SELECT列名1,列名2... FROM 表A ,表B WHERE 表A.列名 = 表B.列名

-- 需求: 查询商品的商品id,商品名称和分类名称:

select p.id,p.productName,pd.dirName from product p,productdir pd where p.dir_id = pd.id;

-- 需求: 查询零售价大于200的无线鼠标(使用表的别名)

select * from product,productdir where product.dir_id = productdir.id and product.salePrice > 200 and dirName='无线鼠标';

#使用内连接查询

select productName,dir_id,product.id from product JOIN productdir ON product.dir_id = productdir.id AND dirName = '无线鼠标' AND product.salePrice > 200;

-- 需求: 查询每个货品对应的分类以及对应的库存(三张表)

select product.id,product.productName,productdir.dirName,productstock.storeNum from product,

productdir,productstock where product.dir_id = productdir.id

AND product.id = productstock.id

#使用内连接查询

select product.id,product.productName,productdir.dirName,productstock.storeNum from product JOIN

productdir ON product.dir_id = productdir.id JOIN productstock ON productstock.id = product.id;

-- 需求: 如果库存货品都销售完成,按照利润从高到低查询货品名称,零售价,货品分类(三张表). 销售完成利润 : (售价-成本价)*库存

SELECTp.productName,p.salePrice,p.salePrice,(p.salePrice-costPrice) * ps.storeNum from product p,productdir pd,productstock ps where p.dir_id = pd.id and p.id = ps.id

# 内连接查询

SELECTp.productName,p.salePrice,p.salePrice,(p.salePrice-costPrice) * ps.storeNum from product p JOIN productdir pd ON p.dir_id = pd.id JOIN productstock ps ON p.id= ps.id;

-- ---------------------------------------------------------------------

-- 总结:

-- 可以使用表名前缀区分多个表中的相同列。

-- 可以使用别名可以简化查询。

-- 如果使用了表的别名，则不能再使用表的真名

-- 使用表名前缀可以提高执行效率。