2【sql语言进阶版】数据库函数以及多表查询

数据库函数

1.round(m,n);对m值保留n位小数，四舍五入

select round(3.145，2)from dual；

——dual伪表或者哑表，主要用来调用数据库内部函数的，凑格式用的

2.mod(a，b)；求模函数/求余数

select mod(3,2) from dual #余1

select mod（1，2）from dual #余1

3.to_char(data,'format');将数值型或者日期型数据转换为字符串型

select to_char(sysdata，'YYMMDD') from dual;

练习：查找出emp表中入职日期为1981年2月份的员工信息

select * from emp where  to_char(hire_data，’YYYY-MM')='1981-02';

常见日期格式：

'yyyymmdd hh24:mi:ss'

'yyyy/mm/dd hh12:mi:ss'  （如果hh后面不写数字，默认是12）

'yyyy-mm-dd hh24:mi:ss'

PS：日期格式不区分大小写。函数名不区分大小写。

4.to_date(char,'format')：将字符串数据类型的值转换成日期格式

   例：将一字符串改为时间格式：

select TO_DATE('20150405','YYYYmmDD') from dual;

select to_date('2018-06-06 11:13:11','yyyy-mm-dd hh:mi:ss') from dual;

select to_date('1980年12月17日','yyyy"年"mm"月"dd"日"') from dual;

PS：假如日期格式中含有中文，就用双引号引起来。

练习: 查找出emp表中入职日期为1980-12-17的员工信息

select * from emp e where e.hiredate = date'1980-12-17';

Ps: 直接用date’yyyy-mm-dd’的方式将具有日期格式的字符串转换为date类型

5.字符串拼接符：||

select 'Hello'||'World' from dual;

思考：如下两个查询语句的效果是否相同？

select e.empno||e.ename,e.* from emp e;

select 'e.empno'||e.ename,e.* from emp e;

6.字符串拼接函数CONCAT(字符串1,字符串2),参数可以是数值类型

select  CONCAT('Hello', 'World') from dual;

分组查询

分组函数/统计函数/聚合函数：

1. count(字段名)：计算指定字段为非空的行数

  例：统计emp表中有奖金的人数？

  select count(comm) 非空行数 from emp;

2、count(*)：计算表中的全部行数，包括重复行和空行

例：统计emp表中公司的总人数？

select count(*) 总人数 from emp;

select count(1) 总人数 from emp;

PS：使用count(1) 代替 count(*)，统计的结果是一样的，但是count(1)的统计效率高些。

练习：统计emp表中comm为空的人数？

3.sum(字段名)：计算指定字段的总和

  例：计算emp表中工资的总和？

select sum(sal) 总和 from emp;

4.avg(字段名)：计算指定字段的平均值

例：计算emp表中工资的平均值？

select round(avg(sal),2) 平均值 from emp;

5.min(字段名)：计算指定字段的最小值

例：计算emp表中工资的最小值？

select min(sal) 最小值 from emp;

6.max(字段名)：计算指定字段的最大值

 例：计算emp表中工资的最大值？

select max(sal) 最大值 from emp;

PS：分组函数也叫多行函数，对一组的多行数据同时进行处理，并返回一行。

函数分为多行函数（分组函数）和单行函数。

练习1：计算emp表中公司职工的人数、最低工资、最高工资、平均工资和总工资

练习2：计算emp表中公司职工的总人数及工种数

练习3：计算全部销售员的年工资之和。

分组查询：GROUP BY子句用于对查询的结果的分组统计

分组函数只能从一组值中计算出一个汇总信息，所以在以上给出的查询实例中，分组函数是将查询所获得满足条件的记录行为作为一组，并返回一个统计值。如果要想对多个组分别进行统计汇总。例如，分别计算emp表中每个部门职工的最低工资、最高工资、平均工资和总工资，则要在查询命令中使用"GROUP BY"子句，进行分组查询。Oracle会把查询到的行分成多个组，对于每一个组返回一个汇总信息。

分组查询命令的基本格式为：

select 字段名

from 表名

where 过滤条件

group by 分组字段名

order by 字段名 asc/desc ;

例：计算出公司支付给每个部门的总工资

select deptno,sum(sal)

from emp

group by deptno;

如果要将各个分组再进行划分出不同的小组，则需要在"group by"子句中指定多个列名：

group by 字段名1,字段名2....

例：计算每个部门中每种工种各有多少职工数，并按部门号升序排

select deptno,job,count(*) 人数

from emp

group by deptno,job

order by deptno;

练习1：统计各部门的人数。

练习2：如何显示每个部门的平均工资和最高工资

练习3：找每个部门中每种职位的最高和最低的工资，并按部门号升序排

注意： 在包括"group by"子句的查询中，select子句的列名(除分组函数外)均应包含在"group by"的子句中，即select的列名只能来源于group by后的列名，否则数据库报错！

思考：此语句格式上正确吗？ select e.deptno,count(*) from emp e;

注意：当select后面同时出现字段名和分组函数，就要用到group by子句进行分组，且select的字段名只能来源于group by后的字段名。

带条件的分组查询

利用"group by"子句将选择到的进行分组时，可以使用 having 子句用于限制选择的组，having子句的作用同where子句相似，都是指定查询条件。不同的是where子句对所有列进行选择，检查每条记录是否满足条件，而having子句是检查分组之后的各组是否满足条件。having子句是配合group by子句使用，在没有"group by"子句时不能用，并且group by和having子句在顺序中要出现在where子句之后。

带条件的分组查询语句基本格式为：

select 列1,列2,列3...列N 或 *

from 表名

where 过滤条件

group by 分组字段名

having 分组过滤条件

order by 字段名 asc/desc ;

例：统计各部门的人数,并显示3人以上的人数和部门号。

select deptno,count(*)

from emp

group by deptno

having count(*)>3;

练习1：查询各工种组的平均工资，要求每组至少在2人以上。

练习2：查询出所有经理和销售人员的年平均工资，并按年平均工资降序排序。

练习3：显示员工工资大于1000，且部门平均工资高于2000的部门号和它的平均工资，并按部门编号升序排序。

查询的最长语句：

select 列1, 列2, 列3... 列N 或 *

from 表名

[where 过滤条件]

[group by 分组字段名]

[having  分组过滤条件]

[order by 分组以后的排序字段名 asc/desc] ;

注意：

语句执行顺序是：1. from, where, group by, having

2. select

3. order by

1. 分组原则：select子句的列名、having子句及order by子句的列名，只能来源于group by

后的列名。（分组函数除外）

2、分组函数仅用于select子句，having子句，order by子句。

多表查询

内连接

等值连接：查找多个表中连接字段相等的记录
select 表别名1.列名1,表别名2.列名2....  或 表别名1.*,表别名2.*
from 表名1 表别名1,表名2 表别名2,...表名n,表别名n
where 表名1.列名=表名2.列名 and 表名(n-1).列名=表名n.列名; 

如果对多个表进行连接查询，但在where子句中没有给出连接条件(称无条件连接)，则oracle将构成一个笛卡尔积，对所有表的全部行进行组合，产生大量的行(m*n行)。
e.deptno=d.deptno的作用：两个表刚开始进行笛卡尔积连接（一张表的每一行和另外一张表的所有行都比对一次），然后对形成的笛卡尔积逐行筛选出部门号相等的记录。

---什么时候用到多表查询：
--1，当查询的数据来自于两张表或以上，就要用到多表查询
--2，确定用多表连查，找到多张表的相同字段，在where后面用等号进行连接，如果有还有条件用and进行追加

例：查找名字为"ALLEN"的职工所在的部门号、部门名和部门所在地
select e.ename,e.deptno,d.dname,d.loc 
from emp e,dept d
where d.deptno=e.deptno and e.ename='ALLEN';

练习1：查询部门号是20,30的职工的员工编号，姓名，部门号，部门所在位置。
练习2：显示部门号为10的部门名、员工号和工资
练习3：显示雇员名，雇员工资及所在的部门的名字，并按部门名排序
思考：三张表如何关联 ？（n张表还是看成两张表关联）

内连接的另一种写法：
select * from A1 ,B1 where A1.id=B1.id;     
等价于：select * from A1 inner join B1 on A1.id=B1.id; 
（inner可以省略，但不建议省略）

自连接

：就是和自己进行连接查询，给一张表取两个不同的别名，然后附上连接条件
自连接的基本格式：
select 列名
from 表名 表别名1,表名 表别名2
where 条件;

例：查出比"JONES"工资高的职工的工资、工种的情况
select b.sal,b.job from emp a,emp b
where a.ename= 'JONES' and a.sal < b.sal;

练习1：查找出职工的上级领导的信息，显示职工姓名和领导姓名。
select e1.ename ,e1.mgr,e2.ename,e2.empno from emp e1,emp e2 where  e1.mgr=e2.empno;
练习2：查找出与Smith在同一个部门工作的所有职工的姓名及工资
Select ename,sal from emp e,dept d where e.deptno=d.deptno and ename=’Smith’;
注意： 对表进行自身的连接时，必须使用表的别名，这才能在where子句中给出正确的连接条件。

外连接

外连接：左连接、右连接
左连接：左表为主表，左表每一行都显示(即使没有匹配到)。
select * from A1 left join B1 on A1.id=B1.id;
右连接：右表为主表，右表每一行都显示(即使没有匹配到)。
select * from A1 right join B1 on A1.id=B1.id;

例：显示出所有部门的编号、部门的名称和其职工的姓名与工种。(部门要全部显示出来)
select d.deptno,d.dname,e.ename,e.job
from dept d left join emp e on d.deptno=e.deptno;

练习：列出无雇员的部门的部门号，部门名称。
select d.deptno,dname from emp e right join dept d on e.deptno=d.deptno where ename is not null;

子查询

子查询：查询语句嵌套的查询语句就是子查询，子查询语句必须要用圆括号括起来，结尾不能有分号。子查询返回的数据池可看成在内存中的一张临时表。断电关机后就消失，语句运行时才有数据。子查询得到的数据也称为数据池或者临时表。
where子查询
单值子查询基本格式：
SELECT  <列名表>
FROM  表名
WHERE 字段 比较运算符 (SELECT 列名 FROM 表名WHERE 条件) ;

例：查找出与“SMITH”在同一个部门的所有职工姓名及工资
select ename,sal
from emp
where deptno=(SMITH所在的部门);
--SMITH所在的部门
select deptno from emp where ename='SMITH';
--将子查询带入外层查询
select ename,sal,deptno
from emp
where deptno=(select deptno from emp where ename='SMITH');
-----对于该子查询命令，系统执行时先执行子查询，返回一个值(即SMITH的部门号)作为主要查询(或外层查询)的条件，然后再根据该条件执行主查询选择出预期的结果，即与“SMITH”同一部门的所有职工。

练习1、查找出比"SCOTT"工资低的职工信息，并按工资升序排序。
Select * from emp where sal<(select sal from emp where ename=’SCOTT’);
练习2、查找出比"SCOTT"工资低，并且在"NEW YORK"工作的职工的信息。
Select * from emp e,dept d from e.deptno=d.deptno and loc=’NEW YORK’and sal<(select sal from emp where ename=’SCOTT’)；
练习3，查找出具有最高月工资的雇员的姓名、工种和工资。
Select ename,job,sal from emp where sal=(select max(sal) from emp);

多值子查询基本格式：
SELECT  <列名表>
FROM  表名
WHERE 字段 in/not in (SELECT 列名 FROM 表名WHERE 条件) ;

例：查找出工资在2500到3500元之间的职工所在部门的所有人员的有关信息。
select *
from emp
where deptno in (工资在2500到3500元之间的职工所在部门);
--工资在2500到3500元之间的职工所在部门号
select distinct deptno from emp where sal between 2500 and 3500;
--将多行子查询带入外层查询
select *
from emp
where deptno in (select distinct deptno from emp where sal between 2500 and 3500);

练习1：查找出工种在部门10号中没有的其它部门职工的姓名，工种和工资的信息 。
练习2：在部门10中，查找出与部门30中工种相同的职工的姓名和工种。
练习3：如何查询与SMITH的部门和岗位完全相同的所有雇员信息。

总结：1.等于号右边有一个值的时候用等于号。
2.等于号右边有多个值或者一个值的时候用in或者用=any()。

 From子查询

From子查询基本格式：
SELECT  <列名表>
FROM  (SELECT 列名 FROM 表名WHERE 条件) 
WHERE 条件;

例：查询工作是clerk的员工姓名和工作
select ename,job
from emp
where job='CLERK';
--上题的子查询做法
select e2.ename,e2.job
from (select * from emp where job='CLERK') e2;

练习1：显示高于自己公司平均工资的员工的信息。
练习2：显示高于自己部门平均工资的员工的信息。

Ps: from子查询的查询效率比where子查询要高