多表查询
查询语法
1. 普通查询 select 列名列表 from 表名列表 where ...
CREATE TABLE dept( -- 创建部门表
id int PRIMARY KEY auto_increment,
NAME VARCHAR(20)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
INSERT INTO dept (name) values("开发部"), ("市场部"), ("财务部");
SELECT * FROM dept;
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) -- 外键 关联部门表的主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
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-24', 2);
INSERT into emp(name,gender, salary, join_date, dept_id) VALUES ("唐僧","男", 9000,'2008-08-24', 2);
INSERT into emp(name,gender, salary, join_date, dept_id) VALUES ("白骨精","女", 5000,'2015-10-24', 3);
INSERT into emp(name,gender, salary, join_date, dept_id) VALUES ("蜘蛛精","女", 4500,'2011-03-24', 1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT * FROM dept,emp; -- 笛卡尔积
内连接查询
1. 隐式内连接: 使用where条件消除无用数据
1. SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
2. SELECT
t1.name, t1.gender, t2.NAME
FROM
emp t1, dept t2
WHERE
t1.dept_id = t2.id;
2. 显式内连接
1. SELECT
emp.name,emp.gender,dept.NAME
FROM
emp
INNER JOIN
dept
ON
emp.dept_id = dept.id
3. 注意
1. 从哪些表中查询数据
2. 条件是什么
3. 查询哪些字段
外连接查询
1. 左外连接
2. 右外连接
* SELECT 字段列表 FROM 表1 left outer JOIN 表2 on 条件
子查询
* 概念: 查询中嵌套查询, 称嵌套查询为子查询
1. select name,id,gender from emp where emp.salary = ( select max(salary) from emp );