MySQL中的多表联查

笛卡儿积的形式

内连接的形式

外连接的形式

-- 创建新数据库
CREATE DATABASE IF NOT EXISTS test4 DEFAULT CHARACTER SET 'UTF8';
USE test4;

-- 插入emp表
-- depid代表部门编号，
CREATE TABLE emp(
    id INT UNSIGNED AUTO_INCREMENT KEY COMMENT 'id',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
    sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
    addr VARCHAR(20) NOT NULL DEFAULT '北京' COMMENT '地址',
    depid TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号'
)ENGINE=INNODB CHARSET=UTF8;

-- 部门表
CREATE TABLE dep(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT 'id',
    depName VARCHAR(50) NOT NULL UNIQUE COMMENT '部门名称',
    depDesc VARCHAR(100) NOT NULL DEFAULT '' COMMENT '部门描述'
)ENGINE=INNODB CHARSET=UTF8;

-- 插入部门数据
INSERT dep(depName,depDesc) VALUES('PHP教学部','研发PHP课件'),
('Java教学部','研发Java课件'),
('Python教学部','研发Python课件'),
('C++教学部','研发C++课件');

INSERT emp(username,age,depId) VALUES('King',24,1),
('Queen',25,2),
('imooc',26,1),
('lily',27,1),
('rose',28,3),
('john',29,3);

 

-- 查询emp id username age 部门名称 dep depName
-- 通过内连接的形式，查询两个表中符合连接条件的记录
-- SELECT 字段名称,... FROM tbl_name1
-- INNER JOIN tbl_name2
-- ON 连接条件 

SELECT e.id,e.username,e.age,d.depName
FROM emp AS e
INNER JOIN dep AS d
ON e.depid=d.id;

 

 

-- 插入一个错误的用户
INSERT emp(username,age,depId) VALUES('测试用户',39,6);

 

SELECT e.id,e.username,e.age,d.depName
FROM emp AS e
INNER JOIN dep AS d
ON e.depid=d.id;

发现并没有查找到"测试用户"，它只会查找到符合两个表中符合连接的数据，类似交集部分

 

-- 外连接的形式
-- 外连接分为左外连接和右外连接

-- 左外连接
SELECT 字段名称,... FROM tbl_name
LEFT [OUTER] JOIN tbl_name2
ON 条件;
-- 先显示左表中的全部记录，再去右表中查询复合条件的记录，不符合的以NULL代替

-- 右外连接
SELECT 字段名称,... FROM tbl_name
RIGHT [OUTER] JOIN tbl_name2
ON 条件;
-- 先显示右表中的全部记录，再去左表中查询复合条件的记录，不符合的以NULL代替

 

-- 测试左外连接
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.depid=d.id;

 LEFT以上一个表为基准，也就是emp表，此时会把所有的用户打印出来，但是测试用户的depid是错误，匹配不到对应的数据，所有为NULL

-- 测试右外连接
SELECT e.id,e.username,e.age,d.depName,d.depdesc
FROM emp AS e
RIGHT JOIN dep AS d
ON e.depId=d.id;

 用dep作为基准，没有对应数据的将以NULL显示