《MySQL入门学习》(p46~p48)

p46 多表连接_内连接

CREATE DATABASE db2;

-- 新建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
)

INSERT INTO dept(NAME) VALUES ('开发部'),('市场部'),('财务部');

-- 新建员工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1),
	salary DOUBLE,
	join_date DATE,
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id) 
)

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

-- 查询所有员工信息，和对应的部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

-- 查询所员工表的名称，性别。部门表的名称
SELECT emp.name,emp.`gender`,emp.`name` FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

-- 或者我们一般会使用别名
SELECT 
	t1.name,
	t1.gender,
	t2.name
FROM 
	emp t1, 
	dept t2
WHERE 
	t1.`dept_id` = t2.`id`;

p47 多表连接_外连接

p48 多表连接_子查询概述