mysql⾃连接临时表_【Mysql进阶技巧（1）】MySQL的多表
关联与⾃连接
⾃连接
测试数据准备
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`gid` char(1) DEFAULT NULL,
`col1` int(11) DEFAULT NULL,
`col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
insert into t2 values
(1,'A',31,6),
(2,'B',25,83),
(3,'C',76,21),
(4,'D',63,56),
(5,'E',3,17),
(6,'A',29,97),
(7,'B',88,63),
(8,'C',16,22),
(9,'D',25,43),
(10,'E',45,28),
(11,'A',2,78),
(12,'B',30,79),
(13,'C',96,73),
(14,'D',37,40),
(15,'E',14,86),
(16,'A',32,67),
(17,'B',84,38),
(18,'C',27,9),
(19,'D',31,21),
(20,'E',80,63),
(21,'A',89,9),
2021网页游戏
(22,'B',15,22),
(23,'C',46,84),
(24,'D',54,79),
(25,'E',85,64),
(26,'A',87,13),
(27,'B',40,45),
(28,'C',34,90),
(29,'D',63,8),
(30,'E',66,40),
(31,'A',83,49),
(32,'B',4,90),
(33,'C',81,7),
(34,'D',11,12),
(35,'E',85,10),
(36,'A',39,75),
(37,'B',22,39),
(38,'C',76,67),
(39,'D',20,11),
(40,'E',81,36);
通过⾃连接查询每组col2最⼤的值;
-- ⽅法1:
select * from t2 as a where not exists (select 1 from t2 where gid=a.gid and col2&l2);
-
- 1. select 1 from t2 where gid=a.gid and col2&l2 : select就进⼊了隐式迭代，同组中⽐当前col2⼤的就输出1;
-- 2. 然后not exists来判断是否存在⽐当前col2⼤的，如果不存在就返回true；返回true就输出当前col2这⼀列;
-- 3. 这⾥的exists与not exists是判断语句，返回的是true or false;
-- ⽅法2:
select * from (select * from t2 order by gid,col2 desc) as t group by gid;
-- t2按照gid和col2来降序排列，然后group分组，分组就取的是frist row,⽽frist row就是最⼤的值;
-- 乍看之下貌似不⽤⾃连接也可以搞定，但是group by分组是不能放在order by之后的，否则就会报错;
通过⾃连接查询每组col2最⼤的三个值;
select * from t2 as a where 3 > (select count(*) from t2 where gid=a.gid and col2&l2) order by a.l2 desc;
-- ⽐当前col2⼤的值如果⼩于三条就输出(注意必须是⼩于三条，如果等于三条就代表已经有了三条)，然后输出后排序;
上⾯两条⾃连接sql都⽐较难理解，但只要换个⾓度，其实理解起来也很容易,⾸先在mysql中要把select翻译为输出，并且要满⾜where以后才输出；输出以后再分组，分组以后才轮到排序，排序之后才轮到取⼏个
JOIN
[inner] join,left join,right join;
通过join,mysql可以做到集合中的求交集，并集，差集等需求，但⽐起类似redis等集合来说，效率差了不⽌⼀个级别了;
数据准备
-- 创建数据表
CREATE TABLE IF NOT EXISTS tdb_goods(
goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(150) NOT NULL,
mysql语句多表查询goods_cate VARCHAR(40) NOT NULL,
brand_name VARCHAR(40) NOT NULL,
goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
is_show BOOLEAN NOT NULL DEFAULT 1,
is_saleoff BOOLEAN NOT NULL DEFAULT 0
)ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COMMENT='测试商品表';
-- 写⼊记录
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英⼨笔记
本','笔记本','华硕','3399',DEFAULT,DEFAULT);
fortune的意思
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英⼨笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英⼨游戏
本','游戏本','雷神','8499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英⼨笔记
本','笔记本','华硕','2799',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英⼨超极本','超级本','联想','4999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英⼨超极
本','超级本','联想','4299',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英⼨触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英⼨平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英⼨平板电脑 (16G WiFi版)','平板电脑','苹果','3388',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显⽰屏 7.9英⼨平板电脑 (16G WiFi版)','平板电脑','苹果','2788',DEFAULT,DEFAULT);
m98调用子程序格式INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英⼨⼀体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);borderlands2中文调节
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英⼨⼀体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑(i5-3450四核 4G 500G 2G独显 DVD 键⿏ Linux )','台式机','宏碁','3699',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA⼯作站','服务器/⼯作站','惠普','4288',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/⼯作站','戴尔','5388',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/⼯作站','苹果','28888',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显⽰设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUE
S('商务双肩背包','笔记本配
件','索尼','99',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器2583i14','服务器/⼯作站','IBM','6888',DEFAULT,DEFAULT);
c语言简单动画代码INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('⽞龙精英版 笔记本散热
器','笔记本配件','九州风神','',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显⽰设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配
件','索尼','99',DEFAULT,DEFAULT);
语法
table1
{[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER]JOIN}
table2
ON conditional_expr
...
{[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER]JOIN}
tablen
ON conditional_expr
JOIN 按照功能⼤致分为如下三类：
INNER JOIN(内连接，或等值连接,)：取得两个表中存在连接匹配关系的记录。
LEFT JOIN(左连接)：取得左表(table1)完全记录，然后再去匹配(table2)，如果匹配不到以NULL的形式返回table2的字段值。
RIGHT JOIN(右连接)：与 LEFT JOIN 相反。
关联更新
-- ↓↓把单表更新的table换成了关联在⼀起的table
UPDATE tdb_goods AS g
JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name
JOIN tdb_goods_cates AS c ds_cate = c.cate_name
SET g.brand_name = b.brand_id,
-- ↓↓由于把原来的品牌名和分类名更换为了id，所以相应的字段名称和类型要有所改变
ALTER TABLE tdb_goods
CHANGE brand_name brand_id TINYINT NOT NULL DEFAULT 0,
CHANGE goods_cate cate_id TINYINT NOT NULL DEFAULT 0;
DESC tdb_goods;
关联查询
-- 内连接
SELECT * FROM tdb_goods AS g
JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;
-- 左连接
SELECT * FROM tdb_goods AS g
LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;
关联删除
-- 查出重复记录;
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;
-- 进⾏删除
DELETE t1 FROM tdb_goods AS t1 JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ds_name = t2.goods_name ds_id > t2.goods_id;
-- 注意，单表的删除在delete后⾯不⽤加上表名，但多表⼀定要加，否则会报语法错误;
-- 如何理解这条sql语句? ⾸先把t1和t2关联⼀起；然后把t1全删除；也就是说把符合另外⼀张表关联条件的本表给删掉;
Cross join [笛卡尔]
交叉连接，得到的结果是两个表的乘积
select * from tdb_goods,tdb_goods_brands; -- tdb_goods表23条数据,tdb_goods_brands有10条数据，笛卡尔以后就出现23*10条数据;
select * from tdb_goods as g join tdb_goods_brands as b; -- join在没有on条件的时候也是笛卡尔乘积;
Full join [union]
mysql⾥⾯没有Full join，只有union；如果要使⽤union的话，被union的表的结构要⼀样才能并在⼀起;
-- 先查出1101的评论，再查出1101所关注的⼈的评论;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论