2021-8-4 数据库课堂笔记Day02

1 单行函数

字符函数 数值函数 日期函数 转换函数 通用函数

1.1 数值函数

重点函数：

• ROUND 四舍五入

SELECT ROUND(x) FROM daul; -- 整数四舍五入
SELECT ROUND(x,y) FROM daul; -- y>0,四舍五入y位小数;
							--y<0,四舍五入整数第y位;y为小数,对y取整

• TRUNC 截取

SELECT TRUNC(x) FROM daul;
SELECT TRUNC(x,y) FROM daul; -- 规则与ROUND相同,不需要四舍五入

• MOD 求余

SELECT MOD(x,y) FROM dual;

1.2 转换函数

隐性(自动类型转换) 显性(强制转化)

DATE、CHARACTER、NUMBER 可通过转换函数互相转换

to_char 将日期转化为字符

to_char (date,'format_model')

必须包含单引号,其中的 format_model 大小写敏感

可以包含任意的日期格式

日期和格式之间用逗号分割

to_date 将字符转化为日期

to_date ('date','format_model')

to_char 将数值转化为字符

9 表示数字，0 表示 0， $ 表示美元，L 表示本地货币 . 表示小数点

9和0都表示位数，位数不足会数据乱码，0在高位时，需要补0，$和L只会添加符号，不会改变数值

to_char(number,'format_model')
SELECT to_char(123,'$000999,999.00') FROM dual; -- $000000,123.00

to_number 将字符转化为数值

to_number('number','format_model') -- 规则与to_char相似

1.3 日期函数

日期的运算

-- 计算员工从入职到现在所度过的周数
select  ename,round((sysdate - hiredate)/7,2) weeks from emp;

日期只能做减法不能做加法

-- 计算员工从入职到现在所度过的周数
select  ename,round((sysdate - hiredate)/7,2) weeks from emp;
--返回指定月数后的日期
select add_months(sysdate,2) from dual;
--计算员工入职至今的月数
select months_between(sysdate,hiredate) from emp;
--next_day
select next_day(to_date('2020-11-11','yyyy-mm-dd'),'星期一') from dual;

1.4 通用函数

这些函数可以使用任何数据类型

1.4.1 NVL 函数

NVL (expr1, expr2)
-- 若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。注意两者的类型要一致 

NVL2 (expr1, expr2, expr3) 
-- expr1不为NULL，返回expr2；expr2为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型 

1.4.2 NULLIF 函数

NULLIF (expr1, expr2)
-- expr1和expr2相等返回NULL，不相等返回expr1

1.4.3 COALESCE 函数

COALESCE(c1, c2, ...,cn)
-- 返回列表中的第一个非空表达式，如果都为空则返回空值

2 条件表达式

在 SQL 中可使用的判断逻辑 (if – then – else)

可使用两种方法

case 表达式

case expr when 值1 then 结果1
		  when 值2 then 结果2
		  when 值3 then 结果3
		  else 结果N
		end 别名

decode 函数

格式：
DECODE(条件，值1，结果1，值2，结果2，值3，结果3，...结果N)
示例：
DECODE( deptno，10，sal * 1.1，20，sal * 1.2，30，sal * 1.3，sal * 1.5 )salary

3 多表查询

直接查询多张表而不加附加条件时会产生笛卡尔集（多张表进行相互映射，输出数据个数为表中行数的积）

笛卡尔积的产生条件：

• 省略链接条件
• 链接条件无效
• 所有表中的所有行相互链接

为两张表设置关联条件 where 来避免笛卡尔积

3.1 Oracle的内链接查询

内链接：合并具有相同列的表的行，结果集中不包含不满足条件的行

3.1.1 等值链接

使用两张表的相同列进行关联，只有值相等的时候才进行链接

对于每张表中都有的字段，使用表名作为前缀进行区分

链接查询的技巧

• 给表起别名，从而简化书写
• 区分不同表的同名列，可用表的全名或别名作为前缀进行区分
• 多张表的链接，需要多个条件，条件之间可使用逻辑运算符 and or

3.1.2 非等值链接

链接条件为一个范围

-- 查询所有员工的信息，并查询他们的工资的等级
SELECT ename,job,sal, grade from emp e,salgrade s 
where sal between s.losal AND s.hisal;

3.2 Oracle的外连接查询

外链接：两个表再连接过程中除了返回满足条件的行以外，还返回左（右）表中不满足条件的行，这种链接称为左（右）外链接。如果没有匹配的行则表中相应列用 null 填充。外链接where子句类似内链接，但链接条件中没有匹配行的列的后面要加上外链接符（+），该语法为Oracle 特有

格式：

-- 右外链接
SELECT column1, column2，……columnN From  table1 t1，table2  t2 where  t1.column（+） = t2.column
-- 左外链接
SELECT column1, column2，……columnN From  table1 t1，table2  t2 where  t1.column = t2.column（+）

右外链接中，右表为主表，左表为从表；左外链接同理

结果集中，主表的数据会全部显示，如果从表有关联数据则以 null 填充

3.3 SQL1999链接语法

3.3.1 叉积 （CROSS JOIN）

效果等同于产生笛卡尔积

3.3.2 自然链接 （NATURA JOIN）

效果等同内链接

以两个表中具有相同名字的列作为条件创建等值链接

返回结果集仅包含两张表中有关联的数据

3.3.3 使用 using 子句创建链接

using 经常和 join 搭配使用

使用时不需要给表创建别名

用于区分相同列名的不同表，放在最后

3.3.4 on 子句创建链接

自然链接中以相同名字的字段作为链接条件

可以使用 on 子句添加额外条件，放在最后

3.3.5 内链接和外链接

左（右）外链接的 join 表达方式：

-- 左外链接
select ename,job,sal,d.deptno ,dname 
from emp e  
left outer join dept d
on e.deptno = d.deptno;
-- 右外链接
select ename,job,sal,d.deptno ,dname 
from emp e  
right outer join dept d
on e.deptno = d.deptno;

满外链接：除了返回满足条件的行以外，还返回其他不满足条件的行

表达方式：

select ename,job,sal,d.deptno ,dname 
from emp e  
full outer join dept d
on e.deptno = d.deptno;

SQL1999语法适用所有关系型数据库

Oracle语法只适用于Oracle数据库

4 分组函数（多行函数）

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

4.1 分组函数的类型

• AVG 均值
• COUNT 统计函数
• MAX 求最大值
• MIN 求最小值
• SUM 求和

4.2 组函数的使用

-- 求所有雇员的平均薪资  最高薪资  最低薪资
SELECT AVG(sal) ,MAX(sal),MIN (sal) from emp;
-- 查询入职最早的员工和入职最晚的员工
SELECT MIN(hiredate) , MAX(hiredate) from emp;
-- 统计员工的数量
select count(*)  from emp;
select count(empno) from emp;--效率高
-- 统计员工中有奖金的人  对空值不统计
select count(comm) from emp;
-- 查询奖金的平均值  总的奖金数 
-- 不计算空值,使用nvl函数使空值为0进行计算
select sum(comm),avg(nvl(comm,0)) from emp; 
-- 在emp表中查询部门的数量
select count(distinct deptno) from emp;

5 分组查询 （GROUP BY）

在 SELECT 列表中能出现的字段只有GROUP BY 的条件字段和组函数（不限字段）

5.1 过滤分组 having 子句

在 WHERE 子句中不能出现分组函数，因此对于同时有分组和用组函数过滤的要求，只能先分组，在分组后使用 having 子句进行过滤

-- 查询部门的平均薪资大于2000的部门及其平均薪资
select  deptno ,avg(sal) from emp
group by deptno
having avg(sal) > 2000;

6 子查询

将一个查询语句作为另一个查询语句的一部分，该查询语句称为子查询

• 子查询在主查询之前执行完成
• 子查询的结果被主查询所使用
• 子查询必须在括号内
• 子查询在条件右侧
• 单行操作符对应单行子查询
• 多行操作符对应多行子查询

子查询

-- 查询比雇员7566薪资高  且从事的是和7788 相同职业的雇员信息
select  * from emp where  sal > (select sal from emp where empno = 7566) and job = (select job  from emp where empno = 7788)

子查询中使用组函数

-- 查询每个部门的最低工资和最低工资的雇员和部门名称
select dname,ename, a.minsal
from dept d,(select deptno ,min(sal) minsal from emp group by deptno) a, emp e
where d.deptno = a.deptno
and e.sal = a.minsal;

子查询中使用 having 子句

-- 查询最低工资大于20号部门的最低工资的部门id和其最低工资
select deptno,min(sal) from emp  group by deptno
having min(sal) > (select min(sal) from emp where deptno=20)

多行子查询

in 等于列表中的任意一个

any 与子查询的任意一个返回值比较

all 与子查询的全部返回值比较

-- 返回其他部门中比job为‘CLERK’部门任一工资低的员工的工号  姓名 职位和薪资
select  empno,ename,job,sal
from emp
where sal < ANY(select sal from emp where job='CLERK')
AND job<> 'CLER'