mysql三表关联查询练习

三张表：

1：公司表        

2、员工表

 3、中间关联表

-- 关联查询方式查询张飞在哪个公司
SELECT
  company.company_name AS '张飞公司' 
FROM
  company
  INNER JOIN com_emp ON com_emp.company_id = company.company_id
  INNER JOIN emp ON emp.emp_id = com_emp.emp_id 
WHERE
  emp.emp_name = '张飞';--   子查询查询方式查询张飞在哪个公司
SELECT
  company.company_name 
FROM
  company 
WHERE
  company.company_id = ( SELECT com_emp.company_id FROM emp INNER JOIN com_emp ON emp.emp_name = '张飞' WHERE emp.emp_id = com_emp.emp_id ) --  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--  查询建投公司的所有43岁以下员工的名字。
SELECT
  emp.emp_name 
FROM
  company
  INNER JOIN com_emp ON company.company_id = com_emp.company_id
  INNER JOIN emp ON com_emp.emp_id = emp.emp_id 
WHERE
  emp.`em_age` < '43' 
  AND company.company_name = '建投' ;
  
  -------------------------------------------------------------------------- 内连接等价于：
SELECT
  * 
FROM
  company,
  com_emp,
  emp 
WHERE
  company.company_id = com_emp.company_id 
  AND com_emp.emp_id = emp.emp_id 
  AND emp.`em_age` < '43' 
  AND company.company_name = '建投' 
  
  
  -- -------------------------------------------- 子查询实现查询建投公司的所有43岁以下员工的名字

SELECT emp.emp_name from emp where emp.emp_id=(
SELECT
  com_emp.emp_id
FROM
  company,
  com_emp 
WHERE
  company.company_id = com_emp.company_id 
  AND company.company_name = '建投')