首页 / MYSQL / mysql执行顺序与join连接
mysql执行顺序与join连接
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql执行顺序与join连接,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6123字,纯文字阅读大概需要9分钟。
内容图文
mysql加载顺序
手写顺序
SELECT DISTINCT
<select list>
FROM
<left_table> join <join_type> JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT <limit_number>
机读顺序
1. FROM <left_table>
2. ON <join_condition>
3. <join_type> JOIN <right_table>
4. WHERE <where_condition>
5. GROUP BY <group_by_list>
6. HAVING <having_condition>
7. SELECT
8. DISTINCT <select list>
9. ORDER BY <order_by_condition>
10. LIMIT <limit_number>
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)
mysql执行顺序与join连接
标签:create 创建 http 加载 set 连接 varchar order HERE
本文系统来源:https://www.cnblogs.com/yscl/p/12043598.html
内容总结
以上是互联网集市为您收集整理的mysql执行顺序与join连接全部内容,希望文章能够帮你解决mysql执行顺序与join连接所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。