数据库高级查询

1.多表查询

 

• 需求:查询每个员工的姓名,工资和对应部门的名称;涉及到两个表
  • 基本语法:select 内容     from 表一 join 表二       on 连接条件   join 是表和表之间的运算
    • join的运算方式

  • 
  • select `name`,gender,birth,depname FROM employees JOIN departments ON employees.deptid = departments.depid  //join通过笛卡尔集连接两个表的数据
  • 
  • 

  多表查询的四种方式

• 内连接 INNER; 外连接   LEFT

• LEFT join叫左外连接  将左边表数据全部出现在查询结果中,右表没有填null
• RIGHT join 右外连接  保证右表数据都出现
• select `name`,gender,birth,depname FROM employees LEFT JOIN departments ON employees.deptid = departments.depid

        内联接要求,完全符合联接条件才会出现在结果集  不符合笛卡尔集条件的数据被丢掉

 JOIN 对应 ON    ON采用99标准将联接的筛选条件通过on进行单独指定 和查询本身进行了区分        逗号对应where

全外联接  UNION 将左外和右外连接结合起来,自动去重

 联接的7种状态图 

1.左外left join      右外right join

2.A  B 的公有部分  : inner join

3.A的独有部分;查询没有员工的部门

 B的独有部分;查询没有部门的员工  通过id判断

4.全外连接 UNION 

      4.  A的独有和B的独有

三表联查

SELECT * FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id
JOIN jobs j
ON e.job_id = j.job_id
WHERE salary>=1000
ORDER BY salary DESC

 自联接

2.子查询Subquery

 方法一:以查询语句为条件

方法二:以自连接的方式

SELECT * 
FROM employees a JOIN employees b
ON b.last_name='Abel'
WHERE a.salary>=b.salary

查询平均工资高于100号部门的部门

 

 

NOT IN 如果有null 值就会失效

 

 SELECT  * 
FROM(
SELECT department_id ,AVG(salary) as avg 
FROM employees 
WHERE department_id IS NOT NULL
GROUP BY department_id 
)AS TEP JOIN job_grades
ON avg BETWEEN lowest_sal AND highest_sal

不相关子查询: 子查询可以独立运行

子查询时,子查询只会运行一次

相关子查询 :子查询不能独立执行,依赖外部数据;执行多次,效率低

-- 子查询还可以出现在 select 短语的后面

SELECT * , ( SELECT COUNT ( 1 ) FROM employees

WHERE department_id = d .department_id ) cnt

FROM departments d

 EXISTS() 

select  (SELECT * FROM employees where  employee_id=-1)

//只要子查询有结果记录行返回 1 不存在返回 -1

相关子查询方式:

SELECT * from  departments d   

WHERE EXISTS(SELECT * FROM employees WHERE department_id=d.department_id)