JOIN的使用 JOIN 理论 Mysql 七种 JOIN 的 sql 编写 环境搭建 # 创建部门表 CREATE TABLE tbl_dept ( id INT NOT NULL AUTO_INCREMENT, deptNa
# 创建部门表
CREATE TABLE tbl_dept (
id INT NOT NULL AUTO_INCREMENT,
deptName VARCHAR (30) DEFAULT NULL,
locAdd VARCHAR (40) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT chharset = utf8 ;
# 创建员工表
CREATE TABLE tbl_emp (
id INT 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 FORREIGN KEY (deptId) REFERENCES tbl_dept (id)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = uttf8 ;
# 插入部门信息
INSERT INTO tbl_dept(deptName,locAdd) VALUES("RD",11),("HR",12),("MK",13),("MIS",14),("FD",15);
# 插入员工信息
INSERT INTO tbl_emp(`name`,deptId) VALUES("z3",1),("z4",1),("z5",1),("w5",2),("w6",2),("s7",3),("s8",4),("s9",51);
SELECT * FROM tbl_dept a INNER JOIN tbl_emp b ON a.id = b.deptId;
SELECT * FROM tbl_dept a LEFT JOIN tbl_emp b ON a.id = b.deptId;
LEFT JOIN
返回左表的全部行和右表满足 ON
条件的行,如果左表的行在右表中没有匹配,那么这一行右表中对应数据用 NULL
代替SELECT * FROM tbl_dept a RIGHT JOIN tbl_emp b ON a.id = b.deptId;
RIGHT JOIN
返回右表的全部行和左表满足 ON
条件的行,如果右表的行在左表中没有匹配,那么这一行左表中对应数据用 NULL
代替。SELECT * FROM tbl_dept a LEFT JOIN tbl_emp b ON a.id = b.deptId WHERE b.deptId IS NULL;
SELECT * FROM tbl_dept a RIGHT JOIN tbl_emp b ON a.id = b.deptId WHERE a.id IS NULL;
SELECT * FROM tbl_dept a LEFT JOIN tbl_emp b ON a.id = b.deptId
UNION
SELECT * FROM tbl_dept a RIGHT JOIN tbl_emp b ON a.id = b.deptId;
SELECT * FROM tbl_dept a LEFT JOIN tbl_emp b ON a.id=b.deptId WHERE b.deptId IS NULL
UNION
SELECT * FROM tbl_dept a RIGHT JOIN tbl_emp b ON a.id=b.deptId WHERE a.id IS NULL;
--结束END--
本文标题: MySQL JOIN的使用
本文链接: https://www.lsjlt.com/news/8675.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-11
2024-05-11
2024-05-11
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0