三、MySQL多表查询和子查询

作者: AKyS佐毅 | 来源:发表于2018-03-20 08:18 被阅读0次

1、隐式连接

1、笛卡尔乘积
- 笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。
```
select * from employees,departments;
```
2、隐式等值连接
- 使用表连接从多个表中查询数据 SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;
```
select concat(employees.first_name,',',employees.last_name),departments.department_name 
from employees ,departments where employees.department_id=departments.department_id;
```
- 在 WHERE 子句中写入连接条件.当多个表中有重名列时，必须在列的名字前加上表名作为前缀 .
- 等值连接是连接操作中最常见的一种，通常是在存在主外键约束条件的多表上建立的，连接条件中的两个字段通过等号建立等值关系。
3、使用表的别名
- 使用表的别名简化了查询，提高了查询的性能

 select concat(e.first_name,',',e.last_name),d.department_name
 from employees e,departments d where e.department_id = d.department_id;

4、对多表做等值连接
- 为了连接n个表，至少需要n-1个连接条件。例如，为了连接三个表，至少需要两个连接条件

select e.employee_id,e.first_name,d.department_id,d.department_name,d.location_id,l.city
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id;

5、非等值连接

select e.first_name,e.salary,jg.grade_level
from employees e,job_grades jg
where e.salary between jg.lowest_sal and jg.higest_sal;

2、显式连接

隐式连接的问题在于：

1、需要在where条件中写连接条件，如果忘记写，代码不会出错，产生笛卡尔乘积；
2、隐式连接只能做内连接；

 select e1.first_name,ifnull(e2.first_name,'总经理') 
 from employees e1 left join employees e2 on e1.manager_id = e2.employee_id;

 select e2.first_name,e2.last_name,e2.department_id 
 from employees e1 join employees e2 on e1.department_id = e2.department_id and e1.last_name = 'Jones'
 where e2.last_name <> 'Jones';

1、内连接
- SELECT table1.column, table2.column FROM table1 JOIN table2 ON(table1.column_name = table2.column_name)
- 自然连接的条件是基于表中所有同名列的等值连接.为了设置任意的连接条件或者指定连接的列，需要使用ON子句.连接条件与其它的查询条件分开书写.使用ON 子句使查询语句更容易理解。
- on字句后面仅仅只是连接条件而已,JOIN的执行顺序:先执行JOIN ON中的子句,再执行WHERE过滤;
- 先执行join再执行过滤。
- 如果要先过滤再join，那么在join中可以通过join on ..and ..and来先过滤再连接。
- JOIN的优化原则:
  - 1 、让小的表在JOIN的前面。
  - 2、如果过滤条件能够过滤掉表中的大量数据,可以把过滤条件放在ON子句中,先执行过滤,再连接表。
```
 select concat(e.first_name,',',e.last_name),d.department_name
 from employees e join departments d on d.department_id = e.department_id;
 
 select e.employee_id,e.first_name,e.department_id,d.department_name,l.location_id,l.city
 from employees e join departments d on e.department_id = d.department_id
 join locations l on d.location_id = l.location_id;

 select e.first_name,e.salary,jg.grade_level
 from employees e join job_grades jg on e.salary between jg.lowest_sal and jg.higest_sal;

 select e.first_name,e.salary,jg.grade_level
 from employees e join job_grades jg on e.salary between jg.lowest_sal and jg.higest_sal whe re e.salary > 10000;
```
2、外连接
- 在LEFT OUTER JOIN中，会返回所有左边表中的行，即使在右边的表中没有可对应的列值。即对连接中左边的表中的记录不加限制
  - SELECT table1.column, table2.column FROM table1 LEFT [OUTER] JOIN table2 ON (table1.column_name = table2.column_name);
- RIGHT OUTER JOIN中会返回所有右边表中的行，即使在左边的表中没有可对应的列值。即对连接中右边的表中的记录不加限制
  - SELECT table1.column, table2.column FROM table1 RIGHT [OUTER] JOIN table2 ON (table1.column_name = table2.column_name);
- FULL OUTER JOIN中会返回所有右边表中的行和所有左边表中的行，即使在左边的表中没有可对应的列值或者右边的表中没有可对应的列
  - SELECT table1.column, table2.column FROM table1 FULL [OUTER] JOIN table2 ON (table1.column_name = table2.column_name);
- MYSQL中暂时不支持全连接,可以通过union +左右连接来完成；
3、自连接
- 在查询语句中，一张表可以重复使用多次，完成多次连接的需要；

-- 查询员工和其经理，如果没有经理，显示总经理,所有的员工必须显示
SELECT e.first_name, e.last_name, e.manager_id, IFNULL(m.first_name,'总经理')
from employees e LEFT JOIN employees m on e.manager_id = m.employee_id;

-- 查询last_name中有‘KI’的员工的经理的信息。
SELECT m.first_name ,m.last_name,e.last_name from employees e
 join employees m on e.last_name like '%KI%' and e.manager_id = m.employee_id;

-- 查询‘jones’部门同事姓名
SELECT e.last_name ,m.first_name ,m.last_name ,m.department_id,e.department_id 
from employees e JOIN employees m on e.department_id = m.department_id 
WHERE e.last_name = 'jones' and m.last_name <> 'jones';

3、子查询

在使用select语句查询数据时,有时候会遇到这样的情况，在where查询条件中的限制条件不是一个确定的值，而是一个来自于另一个查询的结果。
SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);
- 1、子查询在主查询前执行一次
- 2、主查询使用子查询的结果
- 3、子查询是在外层查询执行之前执行;
- 4、子查询要加上括号;
- 5、子查询不能随便写,子查询返回的内容要和外层查询比较的内容相匹配;
- 6、子查询一般单独成行(格式)

--查询工资大于公司平均工资的员工
select first_name,last_name,salary from employees where salary > (select avg(salary) from employees);

1、子查询种类

单行单列子查询：只包含一个字段的查询，返回的查询结果也只包含一行数据(看成一个值)

子查询返回一行一列记录
- 1、返回一行记录
- 2、使用单行记录比较运算符：=；>；>=；<；<=；<>

--  查询‘jones’的部门同事姓名
SELECT first_name ,last_name , department_id from employees
WHERE   department_id = (SELECT department_id from employees WHERE last_name = 'jones')  
and last_name <> 'jones';

-- 查询公司工资最低的员工信息
SELECT e.first_name ,e.last_name ,e.salary from employees e 
WHERE salary = (SELECT min(salary) FROM employees);

-- 查询工资比Jones高的员工姓名和工资
SELECT e.first_name,e.last_name ,e.salary FROM employees e 
WHERE e.salary > (SELECT b.salary from employees b WHERE b.last_name = 'jones');

-- 查询1999年入职公司的所有员工中的最高工资的员工
SELECT e.first_name ,e.last_name ,e.hire_date,e.salary from employees e 
WHERE salary >= (SELECT max(salary) from employees 
WHERE year(hire_date) = '1999')  and year(hire_date) = '1999' ;

 -- 查询last_name king 管理的员工
select first_name ,last_name ,manager_id ,employee_id from employees 
WHERE manager_id in (SELECT employee_id  from employees WHERE last_name = 'king');

 -- 查询平均工资高于公司平均工资的部门信息
 SELECT * from departments WHERE department_id in (
 SELECT department_id from employees
 GROUP BY department_id HAVING AVG(salary) >  (SELECT AVG(salary) from employees));

多行单列子查询：只包含了一个字段，但返回的查询结果可能多行或者零行(看成多个值)
- 使用多行比较运算符 ,对于多行单列子查询,我们可以使用 in;
- IN：与列表中的任意一个值相等
- ANY：与子查询返回的任意一个值比较.
多行多列：包含多个字段的返回，查询结果可能是单行或者多行。(看成一张表)
- 对于多行多列子查询,我们可以在这个结果集上继续查询,过滤和连接;
- 一般会把子查询返回的结果当成一个中间表，在这个中间表上继续查询或者链接查询；
  注意，多行多列的子查询返回的结果必须要设置一个中间表名；

SELECT  t.fullname ,t.salarys ,t.allsalary from
(SELECT CONCAT(first_name,',',last_name) fullname ,
salary*12 salarys ,salary*12*(1+IFNULL(commission_pct,0)) allsalary from employees) t;

UNION/UNION ALL
- JOIN是用于把表横向连接，UNION/UNION ALL是用于把表纵向连接
- UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
- 注意，
  - 1、UNION 内部的 SELECT 语句必须拥有相同数量的列。
  - 2、列也必须拥有相似的数据类型。
  - 3、每条 SELECT 语句中的列的顺序必须相同。
  - 4、UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名
  - 5、UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
- 语法：
SELECT column_name(s) FROM table_name1 UNION|UNION ALL SELECT column_name(s) FROM table_name2

 select * from(
 select e.first_name,e.last_name,d.department_name 
 from employees e left join departments d on e.department_id = d.department_id
 UNION
 select e.first_name,e.last_name,d.department_name
 from employees e right join departments d on e.department_id = d.department_id
 )t;

4、DML加强

CREATE TABLE employees_copy (column columntype 约束,....)
CREATE TABLE table_name as select子查询:把子查询中的内容作为表的内容;
- 注意,这种拷贝方式有一个问题,会丢失数据表的约束;
如果要保留数据表的约束:create table employees_copy2 like employees;
- 注意,这种方式没有内容,没有外键;

-- 1、备份employees表到employees_bck
CREATE TABLE employees_bak as SELECT * FROM employees;
-- 2、创建employees_bck2表，复制employees表结构
CREATE TABLE employees_bak2 as SELECT * FROM employees where 1=2;
-- 3、备份employees表中工资大于10000的员工信息;
CREATE TABLE employees_bak3 as SELECT * FROM employees where salary > 10000;
-- 4、使用employees表中的某几列创建employees_smallest表(id,fullname,hiredate,salary)。
CREATE TABLE employees_smallest as SELECT employee_id as id ,CONCAT(first_name,',',last_name) as fullname ,hire_date as hiredate, salary from employees;

UPDATE语句也可以使用表连接，子查询等多种方式执行；
- 1、update语句中使用子查询,set xxx= xxx;
- 2、update语句中使用连接(update语句不支持显示连接)

   --1，修改114号员工的工资和205号员工相同
   update employees set salary = (select salary from employee where employee_id = 205) where employee_id = 114;
   --不能这样使用,在mysql中,子查询的表不能和要更新的表来自同一张表;
   --只能使用隐式连接来修改数据:
   update employees e1,employees e2 set e1.salary = e2.salary where e1.employee_id = 114 and e2.employee_id = 205;
   -- 2，将在牛津工作的员工的工资涨$1000
   update employees e,departments d,locations l set e.salary = e.salary+1000  where e.department_id = d.department_id and d.location_id = l.location_id and l.city='Oxford'

DELETE
- 在delete语句中，where子句是可选的部分，如果使用了where子句，则删除的数据是符合where条件的所有记录；如果省略了where子句，则全表的数据都会被删除，delete语句的where条件也同样支持子查询，但是一定注意，删除语句中的where条件不能是要删除的数据表中的数据；
- 所以，在涉及到删除的数据是通过要删除的表中的数据查询出来的，一般会把查询结果临时保存到另一张表，再通过delete语句删除；

create table temp as select employee_id from ......;
delete from employees where employee_id in (select * from temp);

6、查询的SQL语句

-- 查询last_name中有‘KI’的员工的经理的信息
SELECT m.first_name ,m.last_name,e.last_name from employees e join employees m on e.last_name like '%KI%' and e.manager_id = m.employee_id;

-- 查询出公司员工的last_name,department_name,city 
SELECT e.last_name,d.department_name ,l.city from employees e LEFT JOIN departments d  on d.department_id = e.department_id LEFT JOIN locations  l  on d.location_id = l.location_id;


-- 查询员工和其经理，如果没有经理，显示总经理
SELECT e.first_name, e.last_name, e.manager_id, IFNULL(m.first_name,'总经理')from employees e LEFT JOIN employees m on e.manager_id = m.employee_id;

-- 查询公司员工的工资等级
select e.first_name,e.salary,jg.grade_level from employees e,job_grades jg
where e.salary between jg.lowest_sal and jg.higest_sal;

--  查询公司员工的姓名和所属部门
SELECT e.first_name ,e .last_name , d.department_name from employees e LEFT join departments d on e.department_id = d.department_id;

SELECT e.first_name ,e.last_name FROM employees e WHERE e.department_id = 50;

-- 查询‘jones’部门同事姓名
SELECT e.last_name ,m.first_name ,m.last_name ,m.department_id,e.department_id from employees e JOIN employees m on e.department_id = m.department_id WHERE e.last_name = 'jones' and m.last_name <> 'jones';

SELECT min(salary) FROM employees GROUP BY department_id ;

-- 返回多行是错误的 不要用 = ，可以使用in
SELECT e.employee_id,e.last_name from employees e WHERE salary = (SELECT min(salary) FROM employees GROUP BY department_id);

SELECT department_id from employees WHERE last_name = 'jones';

--  查询‘jones’的部门同事姓名
SELECT first_name ,last_name , department_id from employees WHERE department_id = (SELECT department_id from employees WHERE last_name = 'jones') 
and last_name <> 'jones';

-- 查询公司工资最低的员工信息
SELECT e.first_name ,e.last_name ,e.salary from employees e WHERE salary = (SELECT min(salary) FROM employees);

SELECT salary from employees WHERE last_name = 'jones';

-- 查询工资比Jones高的员工姓名和工资
SELECT e.first_name,e.last_name ,e.salary FROM employees e WHERE e.salary > (SELECT b.salary from employees b WHERE b.last_name = 'jones');

-- 查询1999年入职公司的所有员工中的最高工资的员工
SELECT e.first_name ,e.last_name ,e.hire_date,e.salary from employees e WHERE salary >= (SELECT max(salary) from employees WHERE year(hire_date) = '1999') and year(hire_date) = '1999' ;

SELECT salary from employees WHERE job_id = 'IT_PROG';

SELECT employee_id ,last_name ,job_id,salary from employees WHERE salary < ANY(SELECT salary from employees WHERE job_id = 'IT_PROG') and job_id <> 'IT_PROG';

-- 查询last_name king 管理的员工
select first_name ,last_name ,manager_id ,employee_id from employees WHERE manager_id in 
(SELECT employee_id  from employees WHERE last_name = 'king');

-- 查询曾经做过ST_CLERK的员工信息
select first_name ,last_name ,manager_id ,employee_id from employees WHERE employee_id in  (SELECT DISTINCT employee_id from job_history WHERE  job_id = 'ST_CLERK');

-- 查询平均工资高于公司平均工资的部门信息
SELECT * from departments WHERE department_id in (
SELECT department_id from employees GROUP BY department_id HAVING AVG(salary) >  (SELECT AVG(salary) from employees));

-- 
SELECT  t.fullname ,t.salarys ,t.allsalary from 
(SELECT CONCAT(first_name,',',last_name) fullname ,salary*12 salarys ,salary*12*(1+IFNULL(commission_pct,0)) allsalary from employees) t WHERE t.allsalary > 150000;

select * from(
select e.first_name,e.last_name,d.department_name 
from employees e left join departments d on e.department_id = d.department_id
UNION
select e.first_name,e.last_name,d.department_name
from employees e right join departments d on e.department_id = d.department_id
)t;


-- 1,备份employees表到employees_bck
CREATE TABLE employees_bak as SELECT * FROM employees;
-- 2,创建employees_bck2表，复制employees表结构
CREATE TABLE employees_bak2 as SELECT * FROM employees where 1=2;
-- 3,备份employees表中工资大于10000的员工信息;
CREATE TABLE employees_bak3 as SELECT * FROM employees where salary > 10000;
-- 3,使用employees表中的某几列创建employees_smallest表(id,fullname,hiredate,salary)。
CREATE TABLE employees_smallest as SELECT employee_id as id , CONCAT(first_name,',',last_name) as fullname ,hire_date as hiredate, salary from employees;

微信扫码关注java技术栈，每日更新面试题目和答案，并获取Java面试题和架构师相关题目和视频。

三、MySQL多表查询和子查询

1、隐式连接

2、显式连接

3、子查询

4、DML加强

6、查询的SQL语句

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

三、MySQL多表查询和子查询

1、隐式连接

2、显式连接

3、子查询

4、DML加强

6、 查询的SQL语句

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

6、查询的SQL语句