MySQL之连接
连接查询就是求出多个表的乘积。连接查询会产生笛卡尔积,假设集合A={a,b},集合B = {0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)},所以需要通过连接关系去除笛卡尔积。
MySQL在select语句、多表更新、多表删除中支持join操作。
-
内连接
JOIN 、CROSS JOIN和INNER JOIN是等价的
连接条件通常会使用ON关键字,where关键字用来对结果集记录进行过滤
内连接的特点是:查询结果必须满足条件,MySQL默认的连接方式就是内连接例子:
查找两张表中cate_id相同的记录:
select goods_id,goods_name,cate_name from tdb_goods inner join tab_goods_cates on tdb_goods.cate_id = tab_goods_cates.cate_id; -
左外链接
显示左表的全部记录及右表符合条件的记录
左连接先是查询出左表(即以左表为主),然后查询右表,右表中满足条件的显示出来,不满足条件的显示为null。 -
右外连接
右连接先是查询出右表(即以左表为主),然后查询左表,左表中满足条件的显示出来,不满足条件的显示为null。 -
多表连接
连接不限于两张表,连接查询也可以是多张表。通常连接查询不可能需要全部的笛卡尔积,而只是需要部分。呢么这是就需要使用条件来去除不需要的记录。这个条件大多数情况下为主外键关系。
例子:
select goods_id,goods_name,cate_name,brand_name,goods_price from tdb_goods as g
inner join tab_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; -
关于连接的说明
A Left join B join_condition
数据表B的结果依赖于数据表A
如果A的某条记录符合where条件,但在数据表B中不存在符合连接条件的记录,则会生成一个所有列为空的额外的B行。 -
无限级分类表设计
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 ); //插入记录 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); //查找记录 //查找所有分类及其父类 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; //查找所有分类及其子类的数目,from——group by ——select —— order by 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; //为tdb_goods_types添加child_count字段 ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0; // 将刚才查询到的子类数量更新到tdb_goods_types数据表 UPDATE tdb_goods_types AS t1 INNER JOIN ( 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 ) AS t2 ON t1.type_id = t2.type_id SET t1.child_count = t2.children_count;
-
多表删除
//查找重复记录 SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 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;