三、MySQL多表查询和子查询

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面试题和架构师相关题目和视频。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 156,757评论 4赞 359
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 66,478评论 1赞 289
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 106,540评论 0赞 237
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,593评论 0赞 203
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 51,903评论 3赞 285
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,329评论 1赞 210
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,659评论 2赞 309
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,383评论 0赞 195
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,055评论 1赞 238
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,337评论 2赞 241
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,864评论 1赞 256
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,227评论 2赞 251
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 32,820评论 3赞 231
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,999评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,750评论 0赞 192
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,365评论 2赞 269
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,260评论 2赞 258

推荐阅读更多精彩内容