多表查询
1、准备sql
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 ) ;
2、笛卡尔积
有两个集合A,B 取这两个集合的所有组合情况。 要完成多表查询,需要消除无用的数据
3、多表查询分类
3.1、内连接查询
SELECT * FROM dept, emp WHERE emp. ` dept_id` = dept. ` id` ;
SELECT emp. name, emp. ` gender` , dept. ` name` FROM dept, emp WHERE emp. ` dept_id` = dept. ` id` ;
SELECT
emp. name,
emp. ` gender` ,
dept. ` name`
FROM
dept, emp
WHERE
emp. ` dept_id` = dept. ` id` ;
显示内连接:
语法:select 字段列表 from 表名1 [innner] join 表名2 on 条件
SELECT * FROM emp INNER JOIN dept ON emp. ` dept_id` = dept. ` id` ; mysql
SELECT * FROM emp JOIN dept ON emp. ` dept_id` = dept. ` id` ;
内连接查询的注意事项:
从哪些表中查询数据 条件是什么 查询哪些字段
3.2、外连接查询
左外连接:
语法:select 字段列表 from 表1 left [outer] join 表2 on 条件; 查询的是左表所有数据以及其交集部分 右外连接:
语法:select 字段列表 from 表1 right [outer] join 表2 on 条件; 查询的是右表所有数据以及其交集部分
SELECT t1. * , t2. name FROM emp t1 LEFT JOIN dept t2 ON t1. ` dept_id` = t2. ` id` ;
3.3、子查询
SELECT * FROM emp WHERE emp. salary = ( SELECT MAX ( salary) FROM emp) ;
子查询不同情况
子查询的结果是单行单列的:
子查询可以作为条件,使用运算符去判断。运算符:> >= < <= =
SELECT * FROM emp WHERE emp. ` salary` < ( SELECT AVG ( salary) FROM emp) ;
SELECT * FROM emp WHERE emp. ` dept_id` IN ( SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部' ) ;
SELECT t1. * , t2. ` name` FROM ( SELECT * FROM emp WHERE join_date > '2011-11-11' ) t1, dept t2 WHERE t1. dept_id = t2. ` id` ;
SELECT t1. * , t2. name FROM emp t1, dept t2 WHERE t1. ` dept_id` = t2. ` id` AND t1. ` join_date` > '2011-11-11' ;