多表

错题1：找出销售部门中年纪最大的员工姓名
查询内容：员工姓名
查询条件：销售部门，年纪最大 max(emp_age)
select emp_name from dept,emp where dept.dept_id=emp.dept_id and emp_age=(select max(emp_age) from dept,emp where dept.dept_id=emp.dept_id and dept_name='财务'）and
dept_name='财务'
谨慎使用容易漏掉一些情况
错题2：求收入最低的员工姓名以及所属部门名称
查询内容：员工姓名 所属部门名称
查询条件：收入最低 min(emp_income)
select emp_name,dept_name from dept,emp where dept.dept_id=emp.dept_id and emp_income=(select min(emp_income) from emp)
错题3：列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序。
查询内容：员工姓名，部门名称，收入
查询条件：每个部门中收入最高，按照收入降序
两表连接
题目里面有 group by 谨慎使用查询的字段，容易出错
第一步：得到每个部门最高的收入
select dept_id,max(emp_income) from emp group by dept_id
第二步：查询
select emp_name,dept_name,emp_income,dept.dept_id
from deptt, emp where dept.dept_id=emp.dept_id
第三步：把第一步的结果和第二步的结果当成两个临时表，进行多表连接
Select emp_name,dept_name,emp_income from (select dept_id,max(emp_income)as max_income from emp group by dept_id)a,
(select emp_name ,dept_name,emp_income,dept.dept_id
from dept,emp
where dept.dept_id=emp.dept_id) b
where a.dept_id=b.dept_id and a.max_income=b.emp_income
order by emp_income desc

union 联合，并且去掉重复的数据
union all 联合，与union区别是不会去掉重复的数据
显示所有部分但是不重复 union
select * from dept left join emp on dept.dept_id=emp.dept_id
union select * from dept right  join emp on dept.dept_id=emp.dept_id
显示所有部分还重复了
select * from dept left join emp on dept.dept_id=emp.dept_id
union all select * from dept right  join emp on dept.dept_id=emp.dept_id
显示各自单独的部分
select *
from dept left join emp
on dept.dept_id=emp.dept_id where emp_name is null
union select * from dept right join emp
on dept.dept_id=emp.dept_id where dept_name is null

使用union 和union all 注意点
1.两个联合表的字段数量相同
2.两个联合的表字段数据类型相同

约束 创建表的时候，对定义的字段的取值进行约束
1.primary key 主键约束
特点
①列的取值位置 id=1 id=2
②列的取值不能为空 id is not null

2.default +默认值  默认值约束
在字段定义的时候，给字段提供一个默认值

3.not null  非空约束
给字段赋值时不能为空

4.foreign key  外键约束
外键约束的作用关联两个表
constraint student_classid_fk foreign key(classid) reference student3(id)
student3是主表，student2是次表，
student2里面的clssid字段必须参考student3里面id字段的值，
比如student3 id 是(1234),那么student2里面的classid就不可以是1234以外的值
而且删除时不能删除主表里和次表连接的字段，否则次表就没用和它连接的表
constraint 约束
student_classid_fk 外键约束自定义名称 可以用aa,yyy ，只是取的一个名字
foreign key(classid) 外键
classid 是外键的列名，可以理解为给哪个字段创建外键
references        参考 引用
student3(id)  主表的主键
主键和外键共存亡

视图 view
视图是一个虚拟的表，由行和列组成的一系列的数据
它不在数据库中保存，在使用的时候引用基础表直接得到
特点
1.视图是基于基础表产生的虚表
2.视图可以由多个基础表组合得到
3.视图内容修改删除会改变基础表的内容
4.创建视图，删除视图不会改变基础表
5.当视图内容来源于多个基础表，不能添加和删除内容
(简单的多表一对一是可以update ,如果多表中包含聚合函数，例如count,sum就无法更新)

为什么要使用视图
1.在日常工作中，作为测试可能每天都要对表中的数据进行多表，复杂查询，为了提高工作效率，减少对sql语句输入的错误，可以使用视图，直接查询
2.创建一个视图引用复杂的多表查询，可以提供给除了技术部门以外的同事使用
3.安全 数据库中表的权限没有控制列，通过创建视图，可以提供给开发，测试使用，但不是修改整个基础表，某些重要的字段也可以通过视图控制不让开发，测试看到

创建一个视图
create view +视图名称 +引用的sql语句
create view view_dept_emp as
(select emp_id,emp_name,emp_age,emp_date,dept_id from emp)
select * from view_dept_emp;
C:/Users/wo/AppData/Local/YNote/data/qianqian9298@163.com/3f412bf2753d46d1a04776230e6693c2/clipboard.png
使用视图
select * from +视图名称

查看当前有哪些已存在的视图(视图是一个假表)
show tables;

删除视图
drop view +视图名称

使用视图的注意点
1.一般是以视图不会有这种写法，as select * from table
视图的使用是针对表中的 某些列
2.创建视图时，谨慎使用 *     例如  as   select  * from dept
原因是，如果原表新增字段，那么通过查看视图，是不会显示新添加的列名
原表删除字段，查看视图会报错
使用 * 之后，删除基础表中字段，会影响视图，使用视图会报错（添加没有影响）
C:/Users/wo/AppData/Local/YNote/data/qianqian9298@163.com/a972ed9c05464d93acbf24918fd3fa91/lhm%7E6%5B%7Dsn69ke5z7d1%7D7zc8.png