数据库的设计概要.docx

/* 数据库的设计概要： 1.每一个项不可分割 2.表中的每一条数据只描述一件事情，且具有为唯一的标示符 3.每个表中不存在其他表的非主键 思想：实际关系的转化说明： 1.当表和表之间的关系为1-1时，转化为独立模式，也可与任意一端对应得关系模式合并 2.当表和表的关系为1-n时可将将单端转化为独立模式，而后与n端所对应的融合 3.m:n 多对多，每个表转化为一个关系模式，同时将两个表之间的关系建成一个表数据库的多表查询1.笛卡尔积： 产生条件;查询条件没有，或查询条件无效 多表之间的连接关系 1.常见连接： Equijion-等值连接 Non-Equijion -非等值连接 Outer-join 外连接外连接符号; 1.外连接符号(+)，其中符号在那边，另一边附加不符合条件的记录（+）符号在那边，那边加空格） 2.如果有多个条件谓词，则每个条件谓词都必须带有外连接符 3.如果一个表使用了外连接符作为条件，那么每个都必须有该表参与，并且使用使用外连接符号 4.一个连接谓词只允许使用在条件谓词的一边，不能两端同时具有否则抛异常 注意：如果要实现全外连接，可以考虑使用“合并查询” sel-jion 自连接 -SQL 92标准 - SQL 92 标准的链接 -/*Cross joinsNatural joinsUsing clauseFull or two sided outer joinsArbitrary join conditions for outer joins*/ */-常见查询-等值连接 -查询每个员工所对应的部门信息select e.*,d.dname,d.locfrom emp e,dept dwhere e.deptno=d.deptno;-2 不等值连接不等连接阐述的是一种范围关系，即就是两个表中没有直接联系- 输出员工的薪资等级select e.ename,e.job,e.sal,s.grade gradefrom emp e,salgrade swhere e.sal between s.losal and s.hisal;-3 自连接【当需要跨取数据是进行自连接】-输出每个员工的信息及其管理者的姓名select e.*,e1.ename margenNamefrom emp e,emp e1where e.empno=e1.mgr;-外连接【显示除了符合条件的记录之外，还显示不符合条件的记录】/* 外连接符号; 1.外连接符号(+)，其中符号在那边，另一边附加不符合条件的记录（+）符号在那边，那边加空格） 2.如果有多个条件谓词，则每个条件谓词都必须带有外连接符 3.如果一个表使用了外连接符作为条件，那么每个都必须有该表参与，并且使用使用外连接符号 4.一个连接谓词只允许使用在条件谓词的一边，不能两端同时具有否则抛异常 注意：如果要实现全外连接，可以考虑使用“合并查询”*/-1，显示所有部门的信息，并显示部门10 的人员信息select e.empno,e.deptno edeptno,d.dname,d.deptnofrom emp e,dept dwhere e.deptno(+)=d.deptno and e.deptno(+)=10;- 2.显示所有人员的信息，并对应20号部门的信息select e.ename,e.deptno eno,d.deptno dno,d.dnamefrom emp e,dept dwhere e.deptno=d.deptno(+)and d.dname(+)=RESEARCH;-3显示所有人，所有部门，以及对应20号部门的人员信息/*抛出异常：同时具有了外连接符号select e.*,d.*from emp e,dept dwhere e.deptno(+)=d.deptno(+)and d.dname(+)=RESEARCH*/-合并集查询【机就是将两个查询的结果集合并】 -关键字 union 注意合并时必须为同结构【列个数，和相对应的集合类型都相同】的结构集 -显示 工作名为Manager,或工资在2500之上的员工信息 select e.* from emp e where job=MANAGER union select e1.* from emp e1 where e1.sal=2500; select e.* from emp e where e.job=MANAGER or e.sal2500; -SQL 92标准 - SQL 92 标准的链接 -/*Cross joinsNatural joinsUsing clauseFull or two sided outer joinsArbitrary join conditions for outer joins*/-Cross jions笛卡尔积select e.*,d.*from emp e cross join dept d;select e.*,d.*from emp e,dept d-2 Natural joins自然连接 同名列的的等值连接 -注意：同名列不能加表的限制 -注意在select 语句中不能使用e.deptno,或者d.deptnoselect e.empnoe,e.ename,d.dnamefrom emp e natural join dept d-3.Using calse 指定同名列的链接 select e.empno,ename,deptno,job,d.dname,d.loc from emp e join dept d using(deptno);-4 Join on 自定义链接select e.*,s.gradefrom emp e join salgrade s on(e.sal between s.losal and s.hisal);select e.*,d.*from emp e left outer join dept d on(e.deptno=d.deptno and e.deptno=10);select e.*,d.*from emp e full outer join dept d on(e.deptno=d.deptno and e.deptno=10);/* 查询原理 3.查询的结果均为矩阵 分组查询【注意：分组查询的结果都是集合】 分组函数 【sum avg count max min】 分组语句： group by关于分组条件的描述 注意;分组查询的要求： select 子句中，要显示的列必须为集合列【可代表集合】 即就是：它要么参与了分组函数，要么参加了分组列 如果不写group by 语句那么就默认为一列 在分组函数中会默认忽略null值【应用，如果在求平均值时，所给的数据中有空值那么默认不会计数】*/-1 sum 求和函数：输出每个部门的工资和select deptno,sum(sal)Sumfrom emp egroup by e.deptno;-2 关于求平均值 -求部门平均工资select deptno ,round(avg(sal),2)AVGSALfrom emp egroup by e.deptno;-3 求最大最小值- 每个部门的中工资的最大值select deptno,max(sal)-min(sal)from empgroup by emp.deptno-统计数量 -count（）统计每个部门的人数 select count(e.empno) from emp e group by e.deptno; -查询员工在所在部门的最高工资的人员信息 select empno,ename,e1.sal from emp e1, ( select deptno, max(sal) maxsal from emp e group by e.deptno)t where e1.sal=t.maxsal and e1.deptno=t.deptno; select empno,ename,e.deptno,salfrom emp e ,(select deptno,max(sal) maxsalfrom emp egroup by deptno) twhere e.deptno=t.deptno and e.sal=t.maxsal-5.在分组函数中会默认忽略null值【应用，如果在求平均值时，所给的数据中有空值那么默认不会计数】select deptno,round(avg(sal),2)from emp egroup by deptno;select deptno,round(avg(nvl(sal,0),2)from emp egroup by deptno;-6.可以同时进行多列分组 -求出同部门同工作的人员的工资和select deptno,job,sum(sal)from emp egroup by deptno,job-=-/* 分组筛选子句 having 说明：再将数据进行分组后，进行操作判断 从而筛选出所要求的组*/-求出 -总工资大于9000的部门号select deptno,sum(sal)from empgroup by deptnohaving sum(sal)=9000;select sum(sal),job from empwhere deptno=20group by job-select sum(sal),deptno from empwhere deptno=20group by deptno;select sum(sal),deptno from empgroup by deptnohaving deptno=20;/*关于使用子查询 1.子查询的分类 1.1单行单列子查询 1.2，单行多列子查询 1.3 多行单列子查询 1.4多行多列子查询 注意：多行多列子查询的关键字 in 匹配结果中的某一个人(取向等的结果) any 符合结果中的某一个（取不等的结果） all 符合结果中的任意一个（取不等的情况）子查询的意义：提高子查询的效率 */-1.1单行单列子查询- 求出SCOTT 所在部门的员工信息select e.*from emp ewhere deptno=(select deptno from emp where emp.ename=SCOTT);-1.2 单行多列的子查询 -查询scott 同部门，同工作的的其它员工信息 select emp.* from emp where(deptno,job)=(select deptno,job from emp where emp.ename=SCOTT); -多行单列子查询 -查询与20号部门同样工作的其他部门的员工信息 select distinct job from emp where deptno=20;-返回所有的工作名 select emp.* from emp where job in(select distinct job from emp where deptno=20)and deptno!=20; -多行多列子查询 -any:工资大于20号部门的某个员工工资的员工信息（大于最小的） select e.* from emp e where sal any(select sal from emp e1 where deptno=20)and e.deptno!=20; -all 工资大于20号部门所有员工的信息（大于最大的）的其他部门的员工信息 select emp.* from emp where salall(select max(sal) MaxSal from emp where deptno=20)and emp.empno!=20; -in 求出每个部门的工资最低的人 select e.* from emp e where(deptno,sal)in (select deptno,min(sal) from emp group by deptno); /* -相关子查询，要使用 关键字exists,not exists说明;相关子查询的子差询每查出一条结果就作为外查询条件进行匹配对比 而后进行结果返回 */ -输出所有员工的领导信息 select * from emp e1 where exists (select 1 from emp e2 where e2.mgr=e1.empno); /*注意：可以再什么位置进行子查询 1.可以再where 语句中【子查询作为条件】 2.可以再from语句中使用子查询【自查询作为资源】 select e.* from emp e ,(select max(sal),deptno from emp e1 group by deptno)e1 where e.deptno=e1.deptno and e.sal=e1.maxsal 3.在select语句中使用 求出每个员工的信息，及其工资所占所有员工的工资的百分比 select e.*,round(e.sal/(select sum(sal) from emp )*100,2)|% from emp e; - 4- DML语句中(delete insert update select) - 删除SCOTT所在部门的工资最高的人 delete emp where sal=( select max