多表查询，子查询

多表查询
创建部门表

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;

SELECT * FROM dept;

SELECT * FROM emp,dept;

– 当查询两张表的所有信息是会产生大量的冗余信息 只有当在where后面写上条件：emp.dept_id=dept.id; 冗余信息才会被过滤掉。

– 消除笛卡尔积的办法：附表的外键=主表的主键

– 过滤掉多余的数据：

SELECT* FROM emp,dept WHERE emp.dept_id=dept.id;

– 查询员工和部门的名字

– 当查询表中的部分信息时，先找到表名然后找到对应的字段

SELECT emp.name,dept.nameFROM emp,dept WHERE emp.dept_id=dept.id;

– 内连接：
– 隐式内连接：SELECT 字段名 FROM 左表, 右表 WHERE 条件
– 显示内连接：SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

– 查询张三的信息，显示员工 id，姓名，性别，工资和所在的部门名称，我们发现需要联合 2 张表同时才能查询出需要的数据，使用内连接

– 隐式内连接

SELECT * FROM emp,dept WHERE emp.dept_id=dept.id;

SELECT emp.id,emp.name,emp.gender,emp.salary,dept.name FROM dept,emp WHERE emp.dept_id=dept.idAND emp.name=”张三”;

– 显示内连接：

SELECT * FROM emp INNER JOIN dept ON emp.dept_id=dept.id;

SELECT emp.id,emp.name,emp.gender,emp.salary,dept.name FROM emp INNER JOIN dept ON emp.dept_id=dept.id AND emp.name=”张三”;

– 显示内联和隐式内联的区别：隐式内连接是先查到两个表(要求字段)的笛卡尔积然后把符合要求的挑选出来。

– 显示内连接是用： 表一 inner join 表二 on 条件1，条件2。。。

– 内连接的一般步骤：1) 确定查询哪些表 2) 确定表连接的条件 3) 确定查询的条件 4) 确定查询的字段

– 外连接：

– 左外连接：SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.dept_id=dept.id;

SELECT emp.id,emp.name,emp.gender,emp.salary,dept.name FROM dept LEFT OUTER JOIN emp ON emp.dept_id=dept.id;

– 在后面加上 ‘name’=’张三’ 得不到需要的结果？ 而两种内连接都可以得到需要的结果。 原因： ？？？？？？？

– 用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL

– 可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

– 右外连接：SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.dept_id= dept.id;

SELECT emp.id,emp.name,emp.gender,emp.salary,dept.name FROM dept RIGHT OUTER JOIN emp ON emp.dept_id=dept.id;

– 用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证右表的数据全部显示。

– 子查询的概念

– 1) 一个查询的结果做为另一个查询的条件

– 2) 有查询的嵌套，内部的查询称为子查询

– 3) 子查询要使用括号

– – 需求：查询开发部中有哪些员工

– 使用内连接才查询

SELECT emp.name,dept.nameFROM emp, dept WHERE emp.dept_id=dept.idAND dept.name=”开发部”;

– 使用外连接来查询

SELECT emp.name,dept.name FROM emp LEFT OUTER JOIN dept ON dept.id=emp.dept_idAND dept.name=”开发部”;

– 使用子查询：

SELECT dept.id FROM dept WHERE dept.name=”开发部”; – 查出dept表中开发部的编号。

SELECT * FROM emp WHERE emp.dept_id=1; – 更具编号找到emp表中外键为1的员工名字。

– 合在一起即为子查询

SELECT * FROM emp WHERE emp.dept_id=(SELECT dept.id FROM dept WHERE dept.name=’开发部’);

– 子查询的三种情况：

– 1) 子查询的结果是单行单列

– 子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、=等

– SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

– 查询工资大于 9000 的员工，来自于哪些部门的名字

SELECT * FROM emp WHERE emp.salary>=9000;

SELECT dept.name FROM dept WHERE dept.id=(SELECT emp.id FROM emp WHERE emp.salary>=5000);

– 2) 子查询的结果是多行单列

– 子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算

– SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

– 查询工资大于 5000 的员工，来自于哪些部门的名字

SELECT dept.name FROM dept WHERE dept.id IN (SELECT emp.id FROM emp WHERE emp.salary>=5000);

– 3) 子查询的结果是多行多列

– 子查询结果只要是多列，肯定在 FROM 后面作为表

– SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

– 查询出 2011 年以后入职的员工信息，包括部门名称

– 先查出2011 年入职的员工：

SELECT * FROM emp WHERE emp.join_date>”2011-1-1”;

– 内连接隐士

SELECT * FROM emp,dept WHERE emp.dept_id=dept.idAND emp.join_date>”2011-1-1”;

SELECT * FROM dept AS d,(SELECT * FROM emp WHERE emp.join_date>’2011-1-1’) e WHERE d.id=e.dept_id;

– 当查询的结果是一个表的时候，对这个表取别名 然后找到新表 和其他一个表的联系。

– 4.6 子查询小结

 – 子查询结果只要是单列，则在 WHERE 后面作为条件

 – 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询