首页 » MySQL » 菜鸟谈——mysql之join

菜鸟谈——mysql之join

原文 http://blog.csdn.net/u010879420/article/details/79188873

2018-01-29 02:01:13阅读(188)

1. sql的执行顺序 1.1 手写
/* 手写sql语句 */
SELECT 
DISTINCT <select_list>
FROM <left_table> 
<join_type> JOIN <right_table> 
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
1.2 机读
/* 机读sql */
FROM <left_table>
ON   <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
2. join图

<a href=菜鸟谈——mysql之join" src="http://img.blog.csdn.net/20180128193603523?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMDg3OTQyMA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast" alt="这里写图片描述" title="">

3. 建表sql
CREATE TABLE tbl_dept(
    id INT(11) NOT NULL AUTO_INCREMENT,
    deptName VARCHAR(30) DEFAULT NULL,
    iocAdd VARCHAR(40) DEFAULT NULL,
    PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE tbl_emp(
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) DEFAULT NULL,
    deptId INT(11) DEFAULT NULL,
    PRIMARY KEY(id),
    KEY fk_dept_id (deptId)
    #CONSTRAINT fk_dept_id FOREIGN KEY(deptId)REFERENCES tbl_dept(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,iocAdd)VALUES("RD",11);
INSERT INTO tbl_dept(deptName,iocAdd)VALUES("HR",12);
INSERT INTO tbl_dept(deptName,iocAdd)VALUES("MK",13);
INSERT INTO tbl_dept(deptName,iocAdd)VALUES("MIS",14);
INSERT INTO tbl_dept(deptName,iocAdd)VALUES("FD",15);
INSERT INTO tbl_emp(name,deptId)VALUES("z3",1);
INSERT INTO tbl_emp(name,deptId)VALUES("z4",1);
INSERT INTO tbl_emp(name,deptId)VALUES("z5",1);
INSERT INTO tbl_emp(name,deptId)VALUES("w5",2);
INSERT INTO tbl_emp(name,deptId)VALUES("w6",2);
INSERT INTO tbl_emp(name,deptId)VALUES("s7",3);
INSERT INTO tbl_emp(name,deptId)VALUES("s8",4);
INSERT INTO tbl_emp(name,deptId)VALUES("s9",51);
4. 7中join 4.7.1 Inner join
select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;

菜鸟谈——mysql之join

4.7.2 Left join
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;

菜鸟谈——mysql之join

4.7.3 Right join
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;

菜鸟谈——mysql之join

4.7.4 Left join And null
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null;

菜鸟谈——mysql之join

4.7.5 Right join And null
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.deptId is null;

菜鸟谈——mysql之join

4.7.6 Full join
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id
union
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;

菜鸟谈——mysql之join

4.7.7
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null
union
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.deptId is null;

菜鸟谈——mysql之join

最新发布

CentOS专题

关于本站

5ibc.net旗下博客站精品博文小部分原创、大部分从互联网收集整理。尊重作者版权、传播精品博文,让更多编程爱好者知晓!

小提示

按 Ctrl+D 键,
把本文加入收藏夹