当前位置:Gxlcms > mysql > MySQL学习16:多表连接

MySQL学习16:多表连接

时间:2021-07-01 10:21:17 帮助过:23人阅读

一连接概述 (1)连接 MySQL数据库在SELECT语句,多表更新以及多表删除中都支持JOIN操作。多表连接的语法结构为: table_reference {[INNER | CROSS] JOIN} | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON condtional_expr; (2)数据表参照 table_reference

一连接概述

(1)连接

MySQL数据库在SELECT语句,多表更新以及多表删除中都支持JOIN操作。多表连接的语法结构为:

table_reference {[INNER | CROSS] JOIN} | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON

condtional_expr;

(2)数据表参照

table_reference table_name [[AS] alias] | table_subquery [AS] alias

数据表可以使用table_name AS alias_name或table_name alias_name赋予别名。table_subquery可以作为子查

询使用在FROM子句中,这样的子查询必须为其赋予其别名。

我们在两张数据表中的可能会有相同名称的字段,为了区分各个表中的字段我们给其数据表名称起了别名,用别

名加以区分。

(3)连接类型

INNER JOIN,内连接;在MySQL中,JOIN,CROSS JOIN和INNER JOIN是等价的。

LEFT [OUTER] JOIN,左外连接。

RIGHT [OUTER] JOIN,右外连接。

(4)连接条件

使用ON关键字来设定连接条件,也可以使用WHERE来代替。

通常使用ON关键字来设定连接条件,使用WHERE关键字进行结果集记录的过滤。

二连接方式

(1)内连接(INNER JOIN)

显示左表及右表符合连接条件的记录:

实例:

使用内连接将数据表tdb_goods和数据表tdb_goods_cates两个表连接起来进行联合查询

SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON

tdb_goods.cate_id = tdb_goods_cates.cate_id;


我们看到查询的结果只是查找到22条记录,我们新添加的第23条记录并没有被查询到,因为不符合查询的连接的

条件。

(2)左外连接(LEFT [OUTER] JOIN)

显示左表的全部记录及右表符合连接条件的记录


实例:

显示tdb_goods数据表中全部的记录以及tdb_goods_cates数据表中符合条件的记录

SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON

tdb_goods.cate_id = tdb_goods_cates.cate_id;


(3)右外连接(RIGHT [OUTER] JOIN)

显示左表的全部记录及右表符合连接条件的记录


实例:

显示tdb_goods_cates数据表中的所有记录以及tdb_goods数据表中符合条件的记录

SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON

tdb_goods.cate_id = tdb_goods_cates.cate_id\G;


三多表连接

(1)多表连接示例

我们在这里使用三张数据表的内连接作为说明:

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g INNER JOIN

tdb_goods_cates AS c ON g.cate_id = c.cate_id INNER JOIN tdb_goods_brands AS b ON

g.brand_id =b.brand_id\G;


我们得条到了最初的添加的22记录。

(2)关于连接的几点说明

外连接:A LEFT JOIN B join_condition

1)数据表B的结果集依赖数据表A

2)数据报A的结果集根据左连接条件依赖所有数据表(B表除外)

3)左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)

4)如果数据表A的某条记录符合WHERE条件,但是在数据表B不存在符合连接条件的记录,将生成一个所有列为

的额外的B行。

也就是下面显示的结果:

SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON

tdb_goods.cate_id = tdb_goods_cates.cate_id;

这个结果我们在上面的例子中已经知道。

如果使用内连接查找的记录在连接数据表中不存在,并且在WHERE子句中尝试以下操作:col_name IS NULL

时,如果col_name被定义为NOT NULL,MySQL将在找到符合连接条件的记录后停止搜索更多的行。

四无限级分类表设计

(1)设计步骤

1)首先创建数据表

CREATE TABLE tdb_goods_types(

type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

type_name VARCHAR(20) NOT NULL,

parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0

);

2)插入记录

INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);

INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑办公',DEFAULT);

INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);

INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);

INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);

INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);

INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);

INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);

INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);

INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);

INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);

INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);

INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);

INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);

INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

3)查看数据表中的记录

SELECT * FROM tdb_goods_types;


上面的显示结果的最后一列表示的意思是:0代表顶级分类,没有父亲节点;1到10代表子类。

(2)自身连接

自身连接指的是同一个数据表对其自身进行连接。

实例:

1)查看所有分类及其父类

SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p

ON s.parent_id = p.type_id;


2)查找所有分类及其子类

SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS

s ON s.parent_id = p.type_id;


3)查找所有分类及其子类的数目

SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT

JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;


五多表删除

多表删除的语法结构为:

DELETE tabke_name[.*] [,table_name[.*]] ... FROM table_references [WHERE where_condition];

SELECT * FROM tdb_goods\G;

我们查找到有重复的记录。那么下面所做的事情就是将重复的记录删除,保留id值较小的记录。

(1)查找重复记录

SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >=

2;


(2)删除重复记录

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP

BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE

t1.goods_id > t2.goods_id;


(3)再次查看数据表中的所有记录是否存在重复记录

SELECT * FROM tdb_goods\G;

SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >=

2;


从上面的结果可以看出数据表中已经没有重复的记录,说明我们成功删除了重复的记录,并且保留了goods_id值

较小的记录。

人气教程排行