mysql多个连接执行顺序 |
您所在的位置:网站首页 › mysql关联查询执行顺序 › mysql多个连接执行顺序 |
mysql加载顺序 手写顺序 SELECT DISTINCT FROM join JOIN ON WHERE GROUP BY HAVING ORDER BY LIMIT 机读顺序 1. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. HAVING 7. SELECT 8. DISTINCT 9. ORDER BY 10. LIMIT sql语句的执行顺序可以用这张鱼骨图来表示 join连表 mysql中的连表基本可以分为以下几种。 接下来对这几种写出相应的sql语句。 首先是创建相应的表来进行实践。 create table if not exists tbl_dept( id int not null auto_increment primary key, deptName varchar(30), locAdd varchar(40) ); create table if not exists tbl_emp( id int auto_increment primary key, name varchar(20), depid int ); insert into tbl_dept(deptName, locAdd) values('RD', 11); insert into tbl_dept(deptName, locAdd) values('HR', 12); insert into tbl_dept(deptName, locAdd) values('MK', 13); insert into tbl_dept(deptName, locAdd) values('MIS', 14); insert into tbl_dept(deptName, locAdd) values('FD', 15); insert into tbl_emp(name, depid) values('z3', 1); insert into tbl_emp(name, depid) values('z4', 1); insert into tbl_emp(name, depid) values('z5', 1); insert into tbl_emp(name, depid) values('w5', 2); insert into tbl_emp(name, depid) values('w6', 2); insert into tbl_emp(name, depid) values('s7', 3); insert into tbl_emp(name, depid) values('s8', 4); insert into tbl_emp(name, depid) values('s9', 51); 内连接(等值连接) mysql> select * from tbl_emp as e inner join tbl_dept as d on e.depid=d.id; +----+------+-------+----+----------+--------+ | id | name | depid | id | deptName | locAdd | +----+------+-------+----+----------+--------+ | 1 | z3 | 1 | 1 | RD | 11 | | 2 | z4 | 1 | 1 | RD | 11 | | 3 | z5 | 1 | 1 | RD | 11 | | 4 | w5 | 2 | 2 | HR | 12 | | 5 | w6 | 2 | 2 | HR | 12 | | 6 | s7 | 3 | 3 | MK | 13 | | 7 | s8 | 4 | 4 | MIS | 14 | +----+------+-------+----+----------+--------+ 7 rows in set (0.01 sec) 左连接(连接左表的全部,右表缺失的字段以null补齐) mysql> select * from tbl_emp as e left join tbl_dept as d on e.depid=d.id; +----+------+-------+------+----------+--------+ | id | name | depid | id | deptName | locAdd | +----+------+-------+------+----------+--------+ | 1 | z3 | 1 | 1 | RD | 11 | | 2 | z4 | 1 | 1 | RD | 11 | | 3 | z5 | 1 | 1 | RD | 11 | | 4 | w5 | 2 | 2 | HR | 12 | | 5 | w6 | 2 | 2 | HR | 12 | | 6 | s7 | 3 | 3 | MK | 13 | | 7 | s8 | 4 | 4 | MIS | 14 | | 8 | s9 | 51 | NULL | NULL | NULL | +----+------+-------+------+----------+--------+ 8 rows in set (0.03 sec) 右连接(连接右表的全部,左表缺失的字段以null补齐) mysql> select * from tbl_emp as e right join tbl_dept as d on e.depid=d.id; +------+------+-------+----+----------+--------+ | id | name | depid | id | deptName | locAdd | +------+------+-------+----+----------+--------+ | 1 | z3 | 1 | 1 | RD | 11 | | 2 | z4 | 1 | 1 | RD | 11 | | 3 | z5 | 1 | 1 | RD | 11 | | 4 | w5 | 2 | 2 | HR | 12 | | 5 | w6 | 2 | 2 | HR | 12 | | 6 | s7 | 3 | 3 | MK | 13 | | 7 | s8 | 4 | 4 | MIS | 14 | | NULL | NULL | NULL | 5 | FD | 15 | +------+------+-------+----+----------+--------+ 8 rows in set (0.03 sec) 左独占连接 mysql> select * from tbl_emp as e left join tbl_dept as d on e.depid=d.id where d.id is null; +----+------+-------+------+----------+--------+ | id | name | depid | id | deptName | locAdd | +----+------+-------+------+----------+--------+ | 8 | s9 | 51 | NULL | NULL | NULL | +----+------+-------+------+----------+--------+ 1 row in set (0.04 sec) 右独占连接 mysql> select * from tbl_emp as e right join tbl_dept as d on e.depid=d.id where e.id is null; +------+------+-------+----+----------+--------+ | id | name | depid | id | deptName | locAdd | +------+------+-------+----+----------+--------+ | NULL | NULL | NULL | 5 | FD | 15 | +------+------+-------+----+----------+--------+ 1 row in set (0.04 sec) 全连接 由于 mysql中不支持全连接,所以需要使用union来进行模拟。 mysql> select * from tbl_emp as e left join tbl_dept as d on e.depid=d.id union select * from tbl_emp as e right join tbl_dept as d on e.depid=d.id; +------+------+-------+------+----------+--------+ | id | name | depid | id | deptName | locAdd | +------+------+-------+------+----------+--------+ | 1 | z3 | 1 | 1 | RD | 11 | | 2 | z4 | 1 | 1 | RD | 11 | | 3 | z5 | 1 | 1 | RD | 11 | | 4 | w5 | 2 | 2 | HR | 12 | | 5 | w6 | 2 | 2 | HR | 12 | | 6 | s7 | 3 | 3 | MK | 13 | | 7 | s8 | 4 | 4 | MIS | 14 | | 8 | s9 | 51 | NULL | NULL | NULL | | NULL | NULL | NULL | 5 | FD | 15 | +------+------+-------+------+----------+--------+ 9 rows in set (0.04 sec) 左独占连接+右独占连接 同理使用union连接来进行模拟 mysql> select * from tbl_emp as e left join tbl_dept as d on e.depid=d.id where d.id is null union select * from tbl_emp as e right join tbl_dept as d on e.depid=d.id where e.id is null; +------+------+-------+------+----------+--------+ | id | name | depid | id | deptName | locAdd | +------+------+-------+------+----------+--------+ | 8 | s9 | 51 | NULL | NULL | NULL | | NULL | NULL | NULL | 5 | FD | 15 | +------+------+-------+------+----------+--------+ 2 rows in set (0.04 sec) 原文:https://www.cnblogs.com/yscl/p/12043598.html |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |