### 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;
mysql学习笔记第三天
最新推荐文章于 2024-08-25 23:22:10 发布