数据库3
--当要查询的数据来自于不同的表,需要实现 多表联查
--92语法 99语法
--内连接 :有where ,满足条件才显示,不满足不显示
--92语法 select 数据 from 表1,表2,表3...;
--笛卡尔积 对乘效果
select * from emp,dept;
--查询所有员工的信息,以及所在的部门信息
--数据: 员工信息 部门信息
--来源: emp dept
--等值连接
select empno,ename,emp.deptno,dname from emp,dept where emp.deptno = dept.deptno;
--注意:当使用同名字段时候,需要指明字段出处
--非等值连接
--查询员工信息以及每个员工的薪资等级
select empno,ename,sal,grade from emp,salgrade where sal between losal and hisal;
--2500这个薪资在几等级
select grade from salgrade where 2500 between losal and hisal;
select * from salgrade;
--自连接 一张表，当多张表使用
--查询有上级的员工的员工信息,以及这个员工的上级信息
--数据：员工信息 上级信息
--来源： 员工表 emp e1 上级表 emp e2
select * from emp e1,emp e2 where e1.mgr = e2.empno;
--外连接
--左外连接|左连接 右外连接|右连接 :主表在逗号的左边叫做左连接,主表在逗号的右边叫做右连接
--主表: 主表中的数据无论是否满足连接条件都要显示
--查询所有员工信息,以及这个员工的上级信息
--员工表作为主表
select * from emp e2,emp e1 where e1.mgr = e2.empno(+);--右链接
select * from emp e1,emp e2 where e1.mgr = e2.empno(+);--左连接
select * from emp e1,emp e2 where e1.mgr = e2.empno(+) and e1.deptno in(10,20) order by e1.empno desc;
-- 找出30部门的员工名称及部门名称
select ename,dname from emp e,dept d where e.deptno = d.deptno and e.deptno= 30;
-- 找出 所有部门的员工数 及部门名称
--需求:所有部门都要,部门表中所有的数据都要 部门表作为主表存在
select dname,count(empno) from emp e,dept d where e.deptno = d.deptno group by dname;
-- 找出所有有员工的部门名称以及员工数
select dname,count(1) from emp e,dept d where e.deptno = d.deptno group by dname;
select dname,c 人数 from dept d,(select deptno,count(1) c from emp group by deptno) e where d.deptno = e.deptno;
select * from emp,dept where emp.deptno(+) = dept.deptno;
--先把每一个部门的部门编号和部门人数求出来
select deptno,count(1) from emp group by deptno;
--上面这个结果集与部门表进行连接
select dname,nvl(c,0) 人数 from dept d,(select deptno,count(1) c from emp group by deptno) e where d.deptno = e.deptno(+);
--查询员工信息 e1,部门信息 d,薪资等级 s,上级信息 e2
select dname 部门名称, e1.ename 员工名称, grade 薪资等级, e2.ename 上级名称
from emp e1, dept d, salgrade s, emp e2
where e1.deptno = d.deptno
and e1.sal between losal and hisal
and e1.mgr = e2.empno;
-- 非等值连接 > < != <>between and
--查询员工姓名，工资及等级
--900 属于哪个等级
select ename,sal,grade from emp e ,salgrade s where sal between losal and hisal;
select grade from salgrade where 900 between losal and hisal;
-- 自连接: 特殊的等值连接 (来自于同一张表)
----找出 存在上级的员工姓名 及上级名称
-- 数据来源： emp e, emp m
-- 字段： e.ename, m.ename
-- 条件： e.mgr=m.empno
select e.ename,m.ename from emp e, emp m where e.mgr = m.empno;
-- 外连接
--找出 所有部门的员工数 及部门名称
select dname,nu from dept d,(select count(1) nu,deptno from emp group by deptno ) e where d.deptno(+) = e.deptno;
--99语法
--select 数据 from 数据来源1 join 数据来源2;
--笛卡尔积 对乘 cross join --交叉连接
--交叉连接 cross join --->笛卡尔积
-- 自然连接(主外键、同名列) natural join -->等值连接
-- join using连接(同名列) -->等值连接
--[inner]join on 连接 -->等值连接 非等值 自连接 (解决一切) 关系列必须区分
--left|right [outer] join on|using -->外连接
--full join on|using -->全连接 满足直接匹配，不满足 相互补充null ，确保 所有表的记录 都至少出现一次
select * from emp,dept; --92
select * from emp e cross join dept d ; --99
select * from emp natural join dept; --自然连接
select empno,deptno from emp e natural join dept d;
--注意;如果使用相同名字段 不能指明出处
select empno,deptno from emp e join dept d using(deptno);--join..using
--注意;如果使用相同名字段 不能指明出处
--非等值连接
--A join B on 连接条件 join c on 连接条件 可以做等值连接 可以做非等值连接
select * from emp e join dept d on e.deptno = d.deptno where e.deptno =30;
--注意;如果使用相同名字段 不能指明出处
--查询员工信息，薪资等级
select grade,count(empno)
from emp e
join salgrade s
on e.sal between losal and hisal
join dept d
on e.deptno = d.deptno
where e.deptno in(10,30) group by grade
having grade != 1 order by grade;
--员工信息，上级信息
select * from emp e1 join emp e2 on e1.mgr = e2.empno;
--内连接（inner）join
--外连接 left join 左连接 right join右链接 全连接full join 两边的表都作为主表
--主表 主表在join的左边，叫做左连接 left join 主表在join的右边，叫做左连接 right join
select * from emp e1 left join emp e2 on e1.mgr = e2.empno;
--视图
--视图是虚拟表,操作视图其实数据还是在对应的表中,使用视图最大的好处可以简化select语句代码
--不是所有的账号都有权限创建视图
--需要授权 1)切换到管理员账号sys 2) grant dba to 账户名;授权 回收权限revoke dba from 账号名;
--创建视图
--create or replace view 视图名 as select语句 [with read only];
--drop view 视图名; 删除视图
--update 表名 set 字段=值 [,....] where 过滤行记录;
--索引
--是数据库的对象之一,是透明的,有没有索引,sql都一样
--大量的数据的查询,如果数据量比较小,如果不是大量做查询,二十大量的执行增删改的操作,反而会降低效率,因为需要维护索引
--字典的目录
--create index 索引名 on表名 (字段列表...)
--drop index 索引名
--表设计
--三范式的规范
--表的名称, 字段,类型,要求(约束)
--表和表之间的关系 一对一 一对多|多对一(主外键) 多对多(中间表)
--创建表 和添加约束
--先创建表,后续为这个表的什么字段添加约束
--创建表的同时添加约束
--创建表 字段后直接添加约束 1)默认的约束名 2)指定约束名
-- 字段定义完成之后,统一为不同的字段的添加约束
--DDL 定义语句 创建表 create 删除表drop 修改alter
--创建表
--create table 表名(
--字段名 类型 约束,
--字段名 类型 约束,
-- 字段名 类型 ,
--约束;
--约束....
--)
--追加约束
--当有主从表关系的两张表
--1.删除表时候
--1)默认先删除从表后删除主表 2)删除主表并同时删除主外键约束 cascade constraints
--2.删除主表中的数据的时候,
--这个主表数据如果没有被从表中的数据引用,可以直接删除
--这个主表数据如果已经被从表中的数据引用,需要处理
--1)先删除从表中引用了的数据 再删除主表的这个数据
--2) 添加外键约束的时候 on delete set null,当主表数据被删除时,从表引用这条数据的外键字段值为null
--3) on delete cascade 删除主表数据的时候级联删除从表中引用了的这些数据
