oracle概述与总结3

Oracle总结与概述

组合函数

分组函数作用于一组数据，并对一组数据返回一个值。

l       AVG  平均值

l       COUNT 总记录

l       MAX   最大值

l       MIN   最小值

l       STDDEV(标准方差)

l       SUM   求和

举例：

SQL> select max(sal) from emp;//取出薪资的最大值

SQL> select count(*) from emp;//取出此表总的总记录

SQL> select count(comm) from emp;//取出此表中奖金不为空的总记录

SQL> select min(sal) from emp;//取出薪资最小的值

SQL> select avg(sal) from emp;//取出薪资不为空的员工的平均薪资  //avg忽略空值

备注：那怎么计算平均工资呢?

SQL> select sum(sal)/count(*) 平均工资 from emp;

或者

SQL> select avg(nvl(sal,0)) 平均工资 from emp;

SQL> select sum(sal) from emp;//取出薪资总额

嵌套查询

     所谓嵌套查询指的是在一个select查询内再嵌入一个select查询。外层的select语句叫外部查询,内层的select语句叫子查询。

 

使用子查询注意事项：

         子查询可以嵌套多层

         子查询需要圆括号()括起来

高级查询

前面介绍的查询还局限在数据库的一张表内。但在实际应用中，我们经常需要在多张表中查询数据或者需要对表中的数据进行分类、汇总等。这就需要较为复杂的高级查询

 

前提掌握部分{

多个表之间关系：一对多|(多对一)  多对多 一对一 3种

关系的完整性约束：实体完整性、参照完整性、用于定义的完整性。 必须满足实体完整性和参照完整性.

实体完整性：规定了字段|属性的约束

参照完整性：关系与关系之间的引用 某个字段的约束  外键

备注：实体完整性及参照完整性是任何关系数据库必须满足的条件。

用户定义完整性：举例：在学生表中 学生的年龄不能够大于60（用户自定义的条件）

连接查询

  在emp表与dept表之间存在着多对一的关联关系（现实中还有其他的关联）,往往我们希望查询出更多信息,这时候我们就要用到连接查询。

//查询员工及部门的详细信息  但是会产生一个笛卡尔积的效果

SQL> select * from emp,dept;

//怎么避免笛卡尔积呢?加入where查询条件 引用关系的比较

SQL> select * from emp ,dept where emp.deptno = dept.deptno;

//别名查询 为表起别名 采用别名查询

SQL> select * from emp e,dept d  where e.deptno=d.deptno;

//注意 以下写法是有问题的：ORA-00918: 未明确定义列

SQL> select e.empno,e.ename,deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

备注说明：deptno在两个表中都存在，所以一定要使用前缀区分。

SQL> select e.empno,e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

综上所述 创建连接查询时应遵循如下规则：

1、  from子句应当包括所有的表名

2、  where子句应定义连接条件 两个表1一个等值条件 三个表 2个等值条件…依次类推。

l       备注：连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。

3、  当列名为多个表共有时,列名必须被限制。

   

使用join连接查询

      语法：

        From join_table join_type join_table on join_condition

       备注： Join_table连接的表名 join_type连接的类型

Join_type连接类型分类如下：

1、  内连接：

内连接根据所使用的比较方式不同,把内连接分为了：

1）  等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

SQL> select * from emp e inner join dept d on e.deptno = d.deptno;

2）  不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

SQL> select * from emp e inner join dept d on e.deptno>d.deptno;

3）  自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

SQL> select * from emp natural join dept;

SQL> select  e.*,d.dname,d.loc  from emp e inner join dept d on e.deptno = d.deptno;

SQL> select d.*,e.ename,e.empno,e.job,e.mgr,e.hiredate,e.sal,e.comm from emp e inner join dept d on e.deptno=d.deptno;

 

备注：Distinct是去掉重复的行,而自然连接是去掉重复的列。

 

2、  外连接

内连接的查询结果都是满足连接条件的记录。但是，有时我们也希望输出那些不满足连接条件的记录的信息。比如，我们想知道这个部门中所有员工的情况,也包括没有员工的部门，这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式。3种外连接：

　　1) 左外连接（LEFT OUTER JOIN）

　　如果在连接查询中，连接表左端的表中所有的记录都列出来，并且能在右端的表中找到匹配的记录，那么连接成功。如果在右端的表中，没能找到匹配的记录，那么对应的记录是空值（NULL）。这时，查询语句使用关键字 LEFT OUTER JOIN，也就是说，左外连接的含义是限制连接关键字右端的表中的数据必须满足连接条件，而不关左端的表中的数据是否满足连接条件，均输出左端表中的内容。

　　例如：要查询所有部门的员工信息查询语句为

SQL> select * from dept d left outer join emp e on e.deptno=d.deptno order by d.deptno;　　左外连接查询中左端表中的所有记录的信息都得到了保留。

   备注：部门表中记录保留,如果部门中没有员工,部门显示 员工记录用null补充。

　　2）右外连接（RIGHT OUTER JOIN）

　　右外连接与左外连接类似，只是右端表中的所有元组都列出，限制左端表的数据必须满足连接条件，而不管右端表中的数据是否满足连接条件，均输出表中的内容。

　　例如：同上例内容，查询语句为

SQL> select * from emp e right outer join dept d on e.deptno=d.deptno order by d.deptno;

　　右外连接查询中右端表中的所有元组的信息都得到了保留。

　　3）全外连接（FULL OUTER JOIN）

　　全外连接查询的特点是左、右两端表中的记录都输出，如果没能找到匹配的记录，就使用NULL来代替。

　　例如：同左外连接例子内容，查询语句为

　　SQL> select * from emp e full outer join dept d on e.deptno=d.deptno order by d.deptno;

　　全外连接查询中所有表中的元组信息都得到了保留。

备注：一定分清：左与右   在join后边的是右

3、  交叉联接

　交叉连接即笛卡儿乘积，是指两个关系中所有记录的任意组合。一般情况下，交叉查询是没有实际意义的。

SQL> select * from emp e cross join dept d;

备注：可以添加where子句筛选出有意义的数据。建议不使用。

 

 

 

4、  自连接查询

如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。同一张表在FROM字句中多次出现，为了区别该表的每一次出现，需要为表定义一个别名。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

　　例如：要求检索出员工编号为7369的上司的详细信息，查询语句为

　SQL> select e1.* from emp e inner join emp e1 on e.mgr=e1.empno where e.empno=7369;

注意：对于连接查询中使用到的 inner outer是可以省略的。但为规范最好不要省略。

提高部分：

SQL> select * from emp e inner join dept d on e.deptno(+)=d.deptno;的理解?

l  使用外连接可以查询不满足连接条件的数据。

l  外连接的符号是 (+)。

 

2、

l  在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。

l  使用 USING 可以在有多个列满足条件时进行选择。

l  不要给选中的列中加上表名前缀或别名。

l  NATURAL JOIN 和 USING 子句经常同时使用。

//你要理解呀?

SQL> select * from emp e  join dept d  using(deptno);

分组查询

分组函数的介绍

分组函数作用于一组数据，并对一组数据返回一个值.

常见的分组函数有：

函数名称

函数描述

Count

返回找到的记录数

Min

返回一个数字列或计算列的最小值

Max

返回一个数字列或计算列的最大值

Sum

返回一个数字列或计算列总和

avg

返回一个数字列或计算列的平均值

分组函数的语法：

SELECT [column,] group_function(column), ...

FROM           table

[WHERE       condition]

[GROUP BY  column]

[ORDER BY  column];

//返回总记录数   //* 代表的是：一条记录

SQL> select count(*) from emp;

//返回comm不为空的总记录数

SQL> select count(comm) from emp;

//COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数

SQL> select count(distinct(sal)) from emp;

注意：组函数忽略空值。

//返回所有员工的平均工资  

SQL> select avg(nvl(sal,0)) from emp;

注意：NVL函数使分组函数无法忽略空值

//返回员工编号最小值  

SQL> select min(empno) from emp;

//返回员工工资最大值

SQL> select max(sal) from emp;

//求该月本公司发出的工资总额

SQL> select sum(comm)+sum(sal) from emp;

SQL> select sum(nvl(sal,0)+nvl(comm,0)) from emp;

 

Group by子句

Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。 如果在查询的过程中需要按某一列的值进行分组,以统计该组内数据的信息时,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句。

 

注意：group by子句一定要与分组函数结合使用,否则没有意义。

//求出每个部门的员工人数

SQL> select deptno,count(*) as "人数" from emp group by deptno;

//求出每个部门的员工的平均工资

SQL> select deptno,avg(nvl(sal,0)) from emp group by deptno;

//注意：group by 子句中的列不必包含在SELECT 列表中

SQL> select avg(nvl(sal,0)) from emp group by deptno;

 

//求出某个部门中相同职位的员工人数   group by 后可以跟多个分组的字段

SQL> select deptno,job,count(*) from emp group by deptno,job order by deptno;

 

非法使用组函数

1、    所用包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。

举例：

SQL> select empno,count(job) from emp;

正确写法如下：

SQL> select empno,count(job) from emp group by empno;

2、    不能在 WHERE 子句中使用组函数（注意）。

SQL> select deptno from emp where count(job)>0 group by deptno;

备注：ORA-00934: 此处不允许使用分组函数

 

 

Having 子句

    HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。

备注：having子句通常与group by子句结合使用

 

语法：

SELECT column, group_function

FROM           table

[WHERE       condition]

[GROUP BY  group_by_expression]

[HAVING      group_condition]

[ORDER BY  column];

//查询部门的员工人数大于五部门编号

SQL> select deptno,count(*) from emp group by deptno having count(*)>5;

           子查询

子查询概念 ：当一个查询的结果是另一个查询的条件时，称之为子查询。

使用子查询注意事项：

         子查询可以嵌套多层

         子查询需要圆括号()括起来

子查询语法：

SELECT select_list

FROM   table

WHERE expr operator

                    (SELECT      select_list

                     FROM            table);

l       子查询 (内查询) 在主查询之前一次执行完成。

l       子查询的结果被主查询使用 (外查询)。

举例：查询员工的工资大于JONES的员工信息

分析过程如下：

首先：查询JONES的员工工资是多少：结果2975

SQL> select sal from emp where ename='JONES';

实际上我们要查询的是：薪资大于2975的员工的信息写法如下：

SQL> select * from emp where sal>2975;

//综合以上写出子查询的结果如下：

SQL> select * from emp where sal>(select sal from emp where ename='JONES');

注意：

l        子查询要包含在括号内。

l        将子查询放在比较条件的右侧。

 

根据查询的结果(内部嵌套查询的结果)把子查询的类型分为单行子查询与多行子查询,

   注意：

l        单行操作符对应单行子查询，多行操作符对应多行子查询。

单行操作符

>、>=、 <、 <= 、<>、=

举例：

//查询编号7876相同职位的员工信息 并且薪资大于编号为7521的薪资的员工信息

SQL> select * from emp where job=( select job from emp where empno=7876) and sal>( select sal from emp where empno=7521);

//子查询含有组函数

SQL> select * from emp where sal>(select avg(nvl(sal,0)) from emp);

//子查询含有having子句 查询部门的最小工资大于20号部门最小工资的部门号及最小工资数

SQL> select deptno,min(sal) from emp group by deptno having min(sal)>( select min(sal) from emp where deptno=20);

 

备注：子查询可以返回空行没要查询到结果是可以的。

 

多行子查询

l         返回多行。

l        使用多行比较操作符。

操作符如下图：

操作符

描述

In

等于列表中的任何一个

Any

子查询返回的任意一个值比较 相同还有some

All

和子查询返回的所有值比较 

Exists

 

 

//查询薪水小于工作岗位CLERK的任何一个薪资的员工信息 并且不包含工作岗位为CLERK的员工信息

SQL> select * from emp where sal < any (select sal from emp where job='CLERK') and job<>'CLERK';

//all与所有值比较 >all 代表的是大于查询结果的最大值

SQL> select * from emp where sal > all (select sal from emp where job='CLERK') and job<>'CLERK';

//查询岗位与部门编号为10相同的员工信息 不包含自己。

SQL> select * from emp where job in(select job from emp where deptno=10) and deptno<>10;

 

只做思考?

 

SQL> select * from emp where exists(select * from dept);

SQL> select * from emp where exists(select * from dept where deptno=80);