39.0/自联/嵌套查询/额外的内容

目录

39. 1回顾

39. 2正文

39.3 自联

39. 4嵌套查询

39.5 把查询的结果当作临时表看待

39.6 组合查询

---

39. 1回顾

1、 函数： 
   [1]字符串函数:
      substr(str,start,size): 截取字符串
      trim():去除左右空格
      char_length():字符串字符的个数
      lower():小写
      upper():大写
      concat(str1,str2,...):拼接
      replace(str,old,new):替换
      left(str,n):求字符串左边n个字符
   [2]算术函数: abs()  ceil()  floor()
   [3]日期时间函数: 
    curdate():当前日期
    curtime():当前时间
    now():当前日期时间
    year():年份
    month(): 月份
    day(): 日
    datediff(date1,date2): 两个日期相差的天数
    timestampdiff(unit,date1,date2): 
    
    [4]流程函数:
       if(条件,val1,val2)
       ifnull(字段,val2):
       case when 条件 then 值 when 条件 then 值 .... else 值 end;
       
 2. 联表查询。
   1.什么情况下需要联表查询? 
     当查询的字段不在一张表中或者查询的字段和条件不在一张表中。
     
   2.联表的方式:
     1.内联:  隐式内联. select * from 表名1,表名2.... where 条件
             显式内联: select * from 表名 join 表名2 on 条件  join 表2 on 条件
             
     2.外联: 左外联: select * from 表名 left join 表名2 on 联表条件
            右外联: select * from 表名 right join 表名2 on 联表条件

39. 2正文

1、 自联
2. 嵌套查询。
3. 额外的内容

39.3 自联

自己连接自己。必须为表起别名。

 

 要求: 查询员工名称以及对应的领导名。

分析: 查询的结果都在一张表中。但是设计员工表---领导表  

select e.e_name 员工姓名,l.e_name 领导名 from emp e join emp l on e.mgr=l.empno

 要求: 查询员工名称以及对应的领导名[并列出没有领导的员工名]

select e.e_name 员工姓名,l.e_name 领导名 from emp e left join emp l on e.mgr=l.empno

 要求: 查询员工名称以及对应的领导名[并列出没有领导的员工名和对应领导的默认值未知]

 select e.e_name 员工姓名,ifnull(l.e_name,'未知') 领导名 from emp e left join emp l on e.mgr=l.empno

 

39. 4嵌套查询

把一次查询的结果作为另一个查询的

 语法:

select * from 表名 where 字段 [in|=] (select 字段 from 表名 where 条件)

例子: 查询研发部具有的员工信息。

-- 根据部门名称查询到部门的编号--dept 
select * from emp where deptno=(
select deptno from dept where dname='研发部'
)

 例子: 查询比周春杰入职到的员工信息。

select * from emp where hiredate<(
 select hiredate from emp where e_name='周春杰'
)

例子: 查询比财务部所有人工资都高的员工信息。

-- 1. 查询财务部的最高信息。

select max(salary) from tb_emp e join tb_dept d  on e.dept_id=d.id where d.`name`='财务部'

-- 2. 查询员工表中薪水大于上面求得最高得薪水。

select * from tb_emp where salary>(select max(salary) from tb_emp e join tb_dept d  on e.dept_id=d.id where d.`name`='财务部')

 

39.5 把查询的结果当作临时表看待

查询每个部门中最高薪资的员工信息。

select * from tb_emp e join 
(select dept_id,max(salary) zg from tb_emp group by dept_id) t
on e.dept_id=t.dept_id and e.salary=t.zg

39.6 组合查询

把多个查询语句的结果组合在一起。union 和union all

 

多个查询的结果 组合到一起。
sql union sql --->把这两条sql查询的结果组合到一起。如果有重复记录则合并成一条。
sql union all sql--->把这两条sql查询的结果组合到一起。如果有重复记录，不合并。 

注意: 这两条sql查询的字段必须一样。

	
	select name from tb_emp where salary>8000
	UNION 
	select * from tb_emp where age>40;

-- 查询薪水大于8000 或2009入职。
select * from tb_emp where salary>8000 
union all
select * from tb_emp where entrydate>'2009-01-01'