多表查询
创建部门表
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.name
FROM 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.id
AND 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.name
FROM emp, dept WHERE emp.dept_id
=dept.id
AND dept.name
=”开发部”;
– 使用外连接来查询
SELECT emp.name
,dept.name
FROM emp LEFT OUTER JOIN dept ON dept.id
=emp.dept_id
AND 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.id
AND 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 后面作为表进行二次查询