ORACLE day02

目录：
单行函数与组函数
substr函数
数字处理函数
to_char
如何修改本地的语言环境
函数嵌套
多表查询
等值连接与非等值连接
自连接
外连接
1 单行函数与组函数
1.1 问题
单行函数是针对sql影响每一行数据都做处理并且每一行都返回一个结果。

组函数是针对一组数据做处理，最终返回一个结果。

1.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：upper函数

代码如下所示：

select  upper(first_name)  from s_emp
    where id=1;

上述代码中，以下代码：

upper(first_name)    

将first_name字段的查询结果处理成全部是大写字母显示。因为是单行函数，所以只能将id=1这条查询出来的记录进行转化。

步骤二：count函数

代码如下所示：

select  count(first_name)  from s_emp
    where id>1; 

上述代码中，以下代码：

count(first_name)
计算当满足where id>1这个判断条件的时候查询出来的first_name有多少条记录。count函数是组函数，是针对查询出来的一组first_name来计算总数量。

2 substr函数
2.1 问题
substr(par1,par2,par3)函数介绍：

par1要处理的字符串 或者字段

par2是从什么位置开始处理 编号从1开始,也可以是负数 -1代表最后一个字符

par3截取多少个字符

2.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：把S_开头的表名的前三个字符

代码如下所示：

select table_name,substr(table_name,1,3)
from user_tables
where table_name like ‘S_%’
escape ‘’;
以上代码中，以下代码：

substr(table_name,1,3)
substr函数是将从表中查询出来的table_name的字符串，然后根据第二个参数即1来判定从第几个字符开始截取，根据第三个参数3来判断截取的字符的数量。

所以上面代码表示的是把table_name查询的结果从第一个字符开始截取，一共截取三个字符。

以上代码中，以下代码：

where table_name like ‘S_%’
escape ‘’;
利用模糊查询的方式把匹配的字符串拼接出来，因为””为特殊字符，也代表一个占位字符，所以需要将””代表的占位字符的意义去掉，仅表示一个下划线。

步骤一：把S_开头的表名的后三个字符

代码如下所示：

select table_name,substr(table_name,-3,3)
from user_tables
where table_name like ‘S_%’
escape ‘’;
以上代码中，以下代码：

substr(table_name,-3,3)
substr函数是将从表中查询出来的table_name的字符串，然后根据第二个参数即-3来判定从第几个字符开始截取，根据第三个参数3来判断截取的字符的数量。

所以上面代码表示的是把table_name查询的结果从倒数第三个字符开始截取，一共截取三个字符。

以上代码中，以下代码：

where table_name like ‘S_%’
escape ‘’;
利用模糊查询的方式把匹配的字符串拼接出来，因为””为特殊字符，也代表一个占位字符，所以需要将””代表的占位字符的意义去掉，仅表示一个下划线。

3 数字处理函数
3.1 问题
在查询的过程中有一些数字我们不需要特别的精确，只需要保留一部分有效数字就可以，所以我们需要对查询所得的数字进行处理。常用的有两个数字处理函数，一个是round函数，一个是trunc函数。

round(par1,par2)

par1:代表需要进行处理的函数

par2:代表精确度，大于0 的时候表示精确到小数点后第几位，小于 0 的时候表示精确到小数点前第(par2+1)位，不写的时候和写0是相同的，直接对par1按照正常四舍五入的数学计算，只保留小数点前的数据。

trunk(par1,par2)

par1:代表需要进行处理的函数

par2:代表从哪一位开始截取，大于0 的时候表示保留到小数点后第几位，后面的数据全部截取掉，小于0 的时候表示从小数点前第几位开始将本位和以后的数据变为0，前面的保持不变，不写的时候和等于0 的时候是相同的，表示只保留整数部分，。

3.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：round 四舍五入

代码如下所示：

 select  round(9.57) from dual;
 select  round(9.49) from dual;   
 select  round(9.57,1) from dual;
 select  round(9.494,2) from dual;  
 select  round(19.57,-1) from dual;
 select  round(12.88,-1) from dual; 
 select  round(12.88,-2) from dual;

以上代码中，以下代码：

select round(9.57) from dual;
select round(9.49) from dual;
这两句分别是把9.57和9.49进行四舍五入，只保留整数部分，因为小数点后第一位一个是5一个是4，因此两句的执行结果分别是10和9。

以上代码中，以下代码：

select round(9.57,1) from dual;
select round(9.494,2) from dual;
这两句分别是把数据精确到小数点后第一位和第二位，精确到哪一位就是看哪一位的下一位数据的值，遵循四舍五入的方式，因此两句的结果分别是9.6和9.49。

以上代码中，以下代码：

 select  round(19.57,-1) from dual;
 select  round(12.88,-1) from dual; 
 select  round(12.88,-2) from dual;

这三句相同的是要精确到小数点前，前两个是精确到小数点前第二位，因此看小数点前第一位的值，遵循四舍五入的原则，前两句的结果是20和10，因为第三句是精确到小数点前第三位，因此看小数点前第二位的值，遵循四舍五入的原则，所以结果为0。

步骤一：trunc 截取

代码如下所示：

 select  trunc(9.57) from dual;
 select  trunc(9.49) from dual;
    
 select  trunc(9.57,1) from dual;
 select  trunc(9.494,2) from dual;  
 
 select  trunc(19.57,-1) from dual;
 select  trunc(12.88,-1) from dual; 
 select  trunc(12.88,-2) from dual;

以上代码中，以下代码：

 select  trunc(9.57) from dual;
 select  trunc(9.49) from dual;

上面两句中par2的值没有写，因此只保留整数，后面的数据全部截取掉，所以上面两句的执行结果都是9。

以上代码中，以下代码：

 select  trunc(9.57,1) from dual;
 select  trunc(9.494,2) from dual;  

以上两句的par2都是大于0 ，因此从小数点后第par2位开始截取，par2位后面的数据全部舍去。所以上面的执行结果分别是9.5和9.49。

以上代码中，以下代码：

 select  trunc(19.57,-1) from dual;
 select  trunc(12.88,-1) from dual; 
 select  trunc(12.88,-2) from dual;

par2小于0， 因此表示从小数点前第几位开始将本位和以后的数据变为0，前面的保持不变。因此按照截取的方式，三句的执行结果分别是：10,10,0.

4 to_char
4.1 问题
to_char(par1,par2)

par1是要处理的数字或者字段

par2是格式 可以省略 代表把一个数字类型数据转换成字符串类型

格式以fm开头

9 小数点前代表0-9任意数字 小数点后

代表1-9的任意数字

0 0 用在小数点前代表强制显示前导零

12345 012,345

0 用在小数点后代表0-9的任意数字

$ 美元符号

L 本地货币符号 ￥ RMB

, 分隔符号

. 小数点。

4.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：显示 s_emp 表员工的工资 按照格式 ‘fm$099,999.99’

代码如下所示：

select to_char(salary,‘fm$099,999.99’)
from s_emp;
5 如何修改本地的语言环境
5.1 问题
数据库安装后默认的语言一般是英文的，需要手动修改一下本地的语言环境，以下介绍了一下修改语言的步骤。

5.2 步骤
1.切换shell

bash

2.打开配置文件

vi .bash_profile

3.写入配置数据

export NLS_LANG=‘SIMPLIFIED CHINESE_CHINA.ZHS16GBK’

export NLS_LANG=‘AMERICAN_AMERICA.ZHS16GBK’

4.保存退出文件

shift+zz

5.让环境变量生效

source .bash_profile

6.重新进入sqlplus

6 函数嵌套
6.1 问题
把一个函数的返回值作为另一个函数的参数

6.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：显示s_emp 表中 id,first_name,manager_id

manager_id 是NULL的显示成BOSS

代码如下所示：

select id,first_name,
nvl(to_char(manager_id),‘BOSS’)
from s_emp;
上述代码中，以下代码：

to_char(manager_id)
to_char函数是格式转换函数，可以将数字类型数据转换成字符串类型。因此本局是把查询出来的manager_id转换成字符串类型。

上述代码中，以下代码：

nvl(to_char(manager_id),‘BOSS’)
nvl是判空函数，把函数to_char转换成的字符串和NULL相比较，如果为NULL则显示’BOSS’，否则则原样显示查询出来的内容。

7 多表查询
7.1 问题
一个数据库是由很多张数据表而构成，不同的数据表存储的不同的信息，有时为了便于查询，我们会需要将不同的数据表中各拿出一部分数据显示出来，不再只是从一个数据表读取数据，因此就需要多表的联合查询。

7.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：把每个部门的名字 和 对应的地区名列出来

代码如下所示：

select s_dept.id,s_dept.name,s_region.name
from s_dept,s_region
where s_dept.region_id=s_region.id;
以上代码中，以下代码：

from s_dept,s_region
部门信息在部门表s_dept中，地区名在地区表s_region中。所以查询部门名字和地区名需要从这两张表中各读取一部分数据，所以from后跟两张表的表名，中间用逗号分隔。

以上代码中，以下代码：

where s_dept.region_id=s_region.id;
判断哪个部门在哪个地区的条件就是部门信息表的region_id字段和地区表s_region的id字段相同，所以where的判定条件写这两个字段相等。。

8 等值连接与非等值连接
8.1 问题
连接两张表的条件的符号都是等号所以这种连接称之为等值连接。

连接两张表的条件的符号不是等号则这种连接称之为非等值连接。。

8.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：查询每个员工的first_name salar以及工资对应的工资级别

代码如下所示：

 select first_name,salary,grade   
    from  s_emp e,salgrade s
      where e.salary between losal and hisal;

以上代码中，以下代码：

from s_emp e,salgrade s
员工的基本信息和工资级别分别在表s_emp和salgrade中，因此需要多表联合查询，为了书写方便，可以给表起别名，表名和别名中间用空格来分隔。

在查询中我们给s_emp表起别名叫e，salgrade表起别名叫s。

以上代码中，以下代码：

where e.salary between losal and hisal;
因为工资级别表中是划分一个数据范围内的为一个等级，所以只能看个人的工资在哪个工资范围内来得出是什么级别，所以在比较的时候不能使用等号。

between…and… 是数据库的关键字，用于对比数据是否在某个范围内。

查询工资的级别，所以把e.salary和between…and…给定的范围去对比。

9 自连接
9.1 问题
自连接是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

9.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：找出所有的领导

代码如下所示：

select id,first_name,manager_id
from s_emp
where id=manager_id;
以上代码中，以下代码：

where id=manager_id;
员工信息表中有员工id和领导id两个字段，如果员工本身就是领导，则员工的id和自己记录那条的manager_id必定相同。

所以我们抓住这一特性，在where字句判定中，以id=manager_id作为判断是否是领导。

10 外连接
10.1 问题
外连接有三种方式：左连接，右连接和全连接。

左连接：根据左表的记录，在被连接的右表中找出符合条件的记录与之匹配。

右连接：根据右表的记录，在被连接的左表中找出符合条件的记录与之匹配。

全连接：返回符合条件的所有表的记录，没有与之匹配的用null表示(结果是左连接和右连接的并集)

10.2 步骤
实现此案例需要按照如下步骤进行。

步骤一：找出所有的领导

代码如下所示：

select distinct m.id,m.first_name
from s_emp e,s_emp m
where m.id=e.manager_id(+)
and e.manager_id is not null;
以上代码中，以下代码：

from s_emp e,s_emp m
查询是不是领导只需要s_emp表中就可以得到结果，但是需要对比相等，所以给s_emp表起两个别名，一个叫e一个叫m。

以上代码中，以下代码：

where m.id=e.manager_id(+)
因为我们要查询的是领导，所以以领导表为准，采用右连接的方式，对比和领导表中有相同id的员工。

以上代码中，以下代码：

  and  e.manager_id is not null;

在员工信息表中manager_id没有限制不能为null，因此在比较的时候可能出现null的比较，所以我们需要限定.manager_id不为null。