27 关联查询、联合查询、子查询

lix_uan
• 阅读 703

SQL JOINS

27 关联查询、联合查询、子查询

关联查询

内连接 inner join

27 关联查询、联合查询、子查询

select 字段名 from 表1,表2 on 关联条件;

左外连接 left join

27 关联查询、联合查询、子查询

  • where b. is null 27 关联查询、联合查询、子查询

  • 返回左表中的所有行,如果左表中的行在右表中没有匹配行,则右表中的列返回空值

联合查询 union

27 关联查询、联合查询、子查询

  • where a. is null, where b.is null 27 关联查询、联合查询、子查询

自连接

  • table1,table2本质上是同一张表,只是用取别名的方式虚拟成两张表,然后两张表再进行连接查询

select的五个子句

where 条件查询

group by + 聚合函数

  • avg() #返回平均值
  • count()
  • min()
  • max()
  • sum()

having 筛选

  • having 和 where 类似,可以筛选数据

  • having 只用于 group by 分组统计语句

order by 排序

limit 分页

#显示前3条
limit 0,3

子查询

where 型子查询

  • in:等于任何一个

  • all:和子查询返回的所有值比较,如 sal > all(1,2,3)

  • any:和子查询返回的任意一个值比较,如 sal > any(1,2,3)

  • exists:判断子查询是否有返回结果(不关心具体行数和内容)

    #查询比“孙红雷”的工资高的员工编号
    SELECT * FROM t_salary
    WHERE basic_salary > (SELECT basic_salary FROM t_employee INNER JOIN t_salary ON t_employee.eid=t_salary.eid WHERE t_employee.ename='孙红雷');
    
    #查询和孙红雷,李晨在同一个部门的员工
    SELECT * FROM t_employee
    WHERE dept_id IN(SELECT dept_id FROM t_employee WHERE ename='孙红雷' OR ename = '李晨');
    
    #查询全公司工资最高的员工编号,基本工资
    SELECT eid,basic_salary FROM t_salary
    WHERE basic_salary = (SELECT MAX(basic_salary) FROM t_salary);
    
    SELECT eid,basic_salary FROM t_salary
    WHERE basic_salary >= ALL(SELECT basic_salary FROM t_salary);

from 型子查询

#找出比部门平均工资高的员工编号,基本工资
SELECT t_employee.eid,basic_salary 
FROM t_salary INNER JOIN t_employee INNER JOIN (
    SELECT emp.dept_id AS did,AVG(s.basic_salary) AS avg_salary
    FROM t_employee AS emp,t_salary AS s
    WHERE emp.eid = s.eid
    GROUP BY emp.dept_id) AS temp
ON t_salary.eid = t_employee.eid AND t_employee.dept_id = temp.did
WHERE t_salary.basic_salary > temp.avg_salary;

exists 型子查询

#查询部门信息,该部门必须有员工
SELECT * FROM t_department 
WHERE EXISTS (SELECT * FROM t_employee WHERE t_employee.dept_id = t_department.did);
评论区
推荐文章

暂无数据

lix_uan
lix_uan
Lv1
学无止境,即刻前行
7
文章
3
粉丝
0
获赞