mysql学习笔记第三天

### day3
-- 两表联查
select * from dept, emp
where emp.dept_id = dept.id;


-- 当表中出现相同名称的字段时，为了区分字段来自于哪张表，我们可以在字段名称前面用“表名."来表示
-- 连接条件最常见的就是等值连接。
select emp.id 用户id,dept.name 部门名称 from dept, emp
where emp.dept_id = dept.id;

select e.id 用户id,d.name 部门名称 from dept d, emp e
where e.dept_id = d.id;

# 查看每个员工的名字，工资，部门编号以及所在的部门名称和所在地区
-- 不重复的字段，是可以不使用表名进行限定的
select emp.name, sal, dept.id, dept.name, loc
from dept, emp
where emp.dept_id = dept.id;

# 注意：emp表上的dept_id保存的值是dept表中的主键字段值！因此emp表中的dept_id 与 dept表的id值一样的记录才会被查询出来，作为一条记录显示在结果集中。

# 当一张表上的某个字段保存的是另外一张表中的主键字段的值时，这个字段就被称为外键！
# 关联关系中经常用 A.主键 = B.外键 作为连接条件.
# 关联查询语法：
# select 字段
# from 表A，表B [,表C, 表D，...]
# where 过滤条件 and 连接条件

# 查看在天庭工作的人都有谁?
select emp.id, emp.name, dept.name,dept.loc
from dept, emp
where dept.loc = '天庭' and dept.id = emp.dept_id;

# 名字里含“飞”的人来自哪里?
select emp.name,dept.NAME,dept.loc
from emp,dept
where emp.name like '%飞%' and dept.id =emp.dept_id;

# 查看每个地区的平均工资?
select dept.loc 地区, avg(emp.sal) 平均工资 from emp,dept
where dept.id = emp.dept_id
group by dept.loc;

#  内连接查询： join子句

# 内连接语法：
# select  字段
# from A表 a
# join B表 b on a.xxx = b.xxx
# join C表 c on a.xxx = c.xxx 或者 b.xxx = c.xxx
# join ... on ...
# where 过滤条件

# 查看每以个员工信息及其对应的部分信息
select e.name, e.sal, d.NAME, d.loc
from emp e
         join dept d
              on e.dept_id = d.id;

# 在内连接中，过滤条件还是写在where子句中

# 查看工资高于1300的员工信息和所在的部门信息
select *
from emp e
         join dept d
              on e.dept_id = d.id
where e.sal > 1300;


# 在关联查询中不满足连接条件的记录是会被排出在外的！

# 如果需要在结果集中列出不满足连接条件的记录时，我们需要使用：外连接

# 外连接有：
# 左外连接： 以left join 左表作为主表，其中的记录都会被展示出来！ 不满足连接条件时，来自右表中的记录字符值 全部为 null
# 右外连接： 以right join 右表作为主表，其中的记录都会被展示出来！ 不满足连接条件时，来自左表表中的记录字符值 全部为 null

select e.name,e.job, d.NAME, d.loc
from emp e left join dept d
                     on  e.dept_id = d.id;


select  e.name,e.job, d.NAME, d.loc
from dept d right join emp e
                       on e.dept_id = d.id;

select e.name,e.job, d.NAME, d.loc
from emp e right join dept d
                      on e.dept_id = d.id;

## 全连接效果， 结果集包含满足连接条件的和左连接、右链接的所有数据

select e.name,e.job, d.NAME, d.loc
from emp e
         left join dept d
                   on e.dept_id = d.id
union
select e.name,e.job, d.NAME, d.loc
from emp e
         right join dept d
                    on e.dept_id = d.id

# 查看比本部门平均工资高的员工信息?
# 1. 查询每个部门的平均工资
select dept_id, avg(sal) from emp
group by dept_id;

# 2. 将子查询结果作为表进行关联查询
select e.*
from emp e, (select dept_id as id, avg(sal) as salary from emp group by dept_id) a
where e.dept_id = a.id and e.sal > a.salary;


# 查看比所在地区平均工资高的员工?
# 1. 查询每个员工的工资和所在地区
select e.sal, d.loc
from emp e, dept d
where e.dept_id = d.id;
# 2. 每个地区的平均工资(按地区分组)
select d.loc, avg(sal)
from emp e, dept d
where e.dept_id = d.id
group by d.loc;
# 3. 关联三张表进行查询： 员工表、部门表、（第二步查询的结果集当做中间表）
select e.name, e.sal, a.avg_sal, d.loc
from emp e, dept d ,
     (select d.loc, avg(sal) as avg_sal
      from emp e, dept d
      where e.dept_id = d.id
      group by d.loc) a
where e.dept_id = d.id and d.loc = a.loc -- 三张表的连接条件
  and e.sal > a.avg_sal; -- 筛选条件

## 内连接完成：查看比所在地区平均工资高的员工?
select  e.name, e.sal, a.avg_sal, d.loc
from emp e
         join dept d
              on e.dept_id = d.id
         join (select d.loc, avg(sal) as avg_sal
               from emp e, dept d
               where e.dept_id = d.id
               group by d.loc) as a
              on d.loc = a.loc
where e.sal > a.avg_sal;
##自连接
-- 1.查看每个员工的上司是谁
-- 正常写法
select e1.name,e2.name from emp e1,emp e2
where e1.manager=e2.id;

-- 内连接写法
select e1.name,e2.name from emp e1
join emp e2 on e1.manager=e2.id;

-- 2.刘备的手下都有谁
select * from emp e1
join emp e2 on e1.manager = e2.id
where e2.name='刘备';

-- 3.谁的工资高于沙僧领导的工资
select * from emp e1
where e1.sal > (select e2.sal from emp e1
           join emp e2 on e1.manager = e2.id
           where e2.id=4);

-- count 其参数有三种形式：
-- 1 count(字段名称)
/*函数返回指定字段的值的数目(NULL不计入)
也就是说,传入某个列的名称，函数返回的是该列中存在值的行数*/
SELECT count(job) FROM emp;

-- 2 count(*)
-- 函数返回表中所有的记录数
SELECT count(*) from emp;

-- 3 count(distinct 字段名称)
-- 函数返回指定列含不同值的行数
SELECT count(distinct job) FROM emp;