Oracle day03 火推

 Oracle 03



函数

Oracle在编写时 内置了一些函数
单行函数

返回的结果. 针对每一行数据, 都会计算出一个结果

单行函数的案例: 
    查询员工的last_name , 并将last_name转换为全大写字母 !

    select upper(last_name) from s_emp;
    select last_name from s_emp;
组函数

返回的结果, 无论存在多少行数据, 都只返回一个结果

计算s_emp表格存在多少行数据 !


组函数存在一些使用上的限制 , 要求返回的结果要一一对应!

select count(id),last_name from s_emp;(错误案例)
select count(id) from s_emp;
常用单行函数

dual 系统提供用来测试学习单行函数的!
字符串的单行函数

1.  字符串变大写
    upper(文本/字段)
    案例: select upper('zhiqiang') from dual;

2.  字符串变小写
    lower(文本/字段)
    案例: select lower('ZHIQIANG') from dual;

3.  单词首字母大写
    initcap(文本/字段)
    案例: select initcap('zhi qiang') from dual;

4.  获取字符串的长度 *
    length(文本/字段)
    案例: select length('zhi qiang') from dual;
5.  截取字符串   *
    substr(参数1,参数2,参数3)

    参数1.    要被截取的原字符串/字段
    参数2.    截取开始的下标,从1开始,可以输入负数
    参数3.    截取的长度


    案例: 

        select substr('123456789',1,5) from dual;   \
                                                        结果完全一致
        select substr('123456789',0,5) from dual;   /


    开始截取的下标 ,输入负数

        select substr('123456789',-3,3) from dual;

6.  拼接字符串

    concat(参数1,参数2)

    参数1. 拼接字符串元素1
    参数2. 拼接字符串元素2

    select concat('zhiqiang','shuaibi') haha from dual;

7.  替换字符串中的指定内容 *

    replace(参数1,参数2,参数3)

    参数1.    要被替换内容的原字符串
    参数2.    在参数1中存在的, 要被替换的字符串片段
    参数3.    替换的新的字符串片段

    select replace('123456789','123','abc') from dual;


8.  判断字符串是否为空 *
    nvl(参数1,参数2)

    参数1如果为空则返回参数2,  不为空则返回参数1

    参数1. 被判断是否为空的参数
    参数2. 如果参数1为空, 则返回此替代参数  
number类型的操作

1.  round(参数1,参数2)  四舍五入 *
    参数1.    要四舍五入的数字
    参数2.    保留的小数点后的位数 ,可以省略 默认为0

    案例: 
        select round(15.5555) from dual;
        select round(15.5555,3) from dual;

2.  trunc(参数1,参数2) 截取数字 *

    参数1.    要截取的数字
    参数2.    保留的小数点后的位数 ,可以省略 默认为0

    案例: 
        select trunc(15.5555) from dual;
        select trunc(15.5555,3) from dual;


3.  格式化显示数字

    ￥020,000.00
    to_char(参数1,参数2)

    参数1. 格式化的数字/列名
    参数2. 格式化模版 , 是一个字符串类型的参数, 需要添加单引号

        FM  :   格式的开头 ,可以忽略

        L   :   本地货币单位, Oracle的语言环境

        $   :   美元符号

        9   :   两种情况:
                    1.  数字9出现在小数点后面:表示1-9的任意数字
                    2.  数字9出现在小数点前面:表示0-9的任意数字

        0   :   两种情况:
                    1.  数字0出现在小数点后面:表示0-9的任意数字
                    2.  数字0出现在小数点前面,且在数字最前面:表示强制显示前导零
        .   :   小数点

        ,   :   分隔符


    案例: 查询s_emp表格中的所有员工的月薪(salary), 并将其格式化 , 
    格式:FM$099,999.00   |  FM$099,999.99
    //建议在小数点后面描述数字时, 使用0

    select to_char(salary,'FM$099,999.00'),id from s_emp;
函数的嵌套

    把一个函数返回的结果, 当作另一个函数所需要的参数 !

    需求: 查询s_emp表格中的last_name , 并且将其最后三位字母以大写方式显示出来 !

    select upper(substr(last_name,-3,3)) from s_emp;

    select upper(substr(last_name,length(last_name)-3,3)) from s_emp;
多表查询
当我们要查询的数据 存在多个表格中时, 需要用到多表查询技术
内连接

s_emp 员工表

s_dept 部门表

    -   id      部门编号
    -   name    部门名称
    -   region_id   部门所在地区编号

s_region 地区表
    -   id      地区编号
    -   name    地区名称

查询部门表格: 

    select id from s_dept;


多表查询基本语法: 

    select 字段 from 表名1,表名2...表名n;

查询部门表和员工表
    select name,last_name from s_emp,s_dept;
笛卡尔积

笛卡尔积是同一种关系代数概念(X*Y)

在SQL中的实现方式时交叉连接, 表示两个表中的每一行数据任意组合, 自然匹配 !
如何解决笛卡尔积

    在实际的SQL的应用中, 笛卡尔积本身没有实际用处.

    我们可以手动指定多张表格之间的关联关系, 来进行限制迪卡尔积的产生 !

    例如: 查询员工表格与部门表格,通过s_emp表格中的dept_id与s_dept表格中的id 关联 ,进行查询

    select last_name,name from s_emp,s_dept where dept_id=s_dept.id;
如何解决错误(未明确定义列)

一般是我们在进行多表查询时, 字段在多表中有重复, oracle无法区分字段所属的表格 .

可以将字段修改为:   表格名.字段 , 来明确字段所属的表格
等值连接

使用等号进行连接多表查询的条件, 就是等值连接
给表格起别名, 简化sql语句, 提高sql语句的可阅读性

查询员工表格与部门表格,通过s_emp表格中的dept_id与s_dept表格中的id 关联 ,进行查询

格式: from 表名1 别名1,表名2 别名2;
select e.id,d.id,e.last_name,d.name from s_emp e,s_dept d where e.dept_id=d.id;
练习

查询如下两张表, 要求消除笛卡尔积

格式: select 字段 from 表名1,表名2...表名n where 多表关联条件;

s_dept 部门表

    -   id      部门编号
    -   name    部门名称
    -   region_id   部门所在地区编号

s_region 地区表
    -   id      地区编号
    -   name    地区名称

    select d.name,r.name from s_dept d,s_region r where d.region_id=r.id;
非等值连接

引入脚本: demobld.sql

不使用等号进行连接多表查询的条件, 就是非等值连接

salgrade    :   工资级别表格

    -   grade工资级别 1-5
    -   losal这个级别最低工资
    -   hisal这个级别最高工资

--------------------------------------------------------

查询每个员工的salary和对应的工资级别

1.  between

    select salary,last_name,grade from s_emp,salgrade where salary between losal and hisal;

2.  and

    select salary,last_name,grade from s_emp,salgrade where salary>=losal and salary<=hisal;
自连接

一张表中, 有多层的业务含义的数据 ,要把某一层的含义数据 取出来时, 使用自连接 ! 

把一张表, 看作多张表进行查询

s_emp表:   

     员工的id   

     领导的编号

------------------------------------------------------------------------

    我们可以去找到哪些人是领导!

    领导和员工的关联关系:  领导的id 是 普通员工的manager_id

    将员工表格 看作两个表格 : 员工表e 和 管理员表m ,这两个表的数据是完全相同的.

    我们通过员工的manager_id与领导的id进行关联, 来完成多表查询! 匹配到的m表格中的数据 就是领导的数据: 

    select distinct m.id,m.last_name from s_emp e,s_emp m where e.manager_id=m.id;
    //因为有些领导管理着多个员工, 会被匹配多次 ,需要加入排重字段distinct
    //查询所有的领导id , 来对比观察是否有问题
    select manager_id from s_emp;

    查询结果: 

               ID LAST_NAME
    ---------- -----------
             9 Catchpole
             1 Velasquez
             8 Biri
             2 Ngao
             7 Menchu
             3 Nagayama
             6 Urguhart
            10 Havel
外连接(+)

外连接的结果集, 等于内连接的结果集 , 加上 匹配不上的记录!

通过在字段后面添加(+) , 来完成外连接操作

格式:     把(+)加载where条件的字段后面 ,  (+)号修饰的字段所在的表的对面表格的数据全部被选中 !

    特点: 内连接匹配不上的数据, 因为外连接要取出, 外连接会通过补足null行来生成结果集

查询s_emp表格, 获取普通员工的信息

select distinct m.last_name,m.id from s_emp e,s_emp m where e.manager_id(+)=m.id and e.manager_id is null;


        e                           m
e.id    e.name  e.m_id      m.id    m.name  m.m_id

2       b       1           1       a       null

//3     c       1           1       a       null

4       d       2           2       b       1

//5     e       2           2       b       1

null    null    null        3       c       1

null    null    null        4       d       2

null    null    null        5       e       2


select m.id,m.name from e,m where e.manager_id is null;
SQL99标准连接 了解

SQL99内连接

    格式
    select 字段 from 表1 inner join 表2 on 连接条件 where 过滤条件;

    -   inner 与 where过滤条件可以忽略不写!

    查询员工last_name与部门的名称:
    oracle方式:
        select last_name,name from s_emp,s_dept where s_emp.dept_id=s_dept.id;
    sql99方式:
        select last_name,name from s_emp inner join s_dept on s_emp.dept_id=s_dept.id;

    查询部门名称 , id 和对应的地区名称 , 并且部门编号大于20

    oracle方式:
        select d.name,d.id,r.name from s_dept d,s_region r where d.region_id=r.id and d.id>20;
     sql99方式:
        select d.name,d.id,r.name from s_dept d inner join s_region r on d.region_id=r.id where d.id>20;
SQL99中的外连接

左外连接
    左边数据一个都不能少 , 就是oralce中把(+)放在了右边!
    格式: select 字段 from 表1 left [outer] join 表2 on 连接条件 where 过滤条件;


右外连接
    右边数据一个都不能少 , 就是oralce中把(+)放在了左边!
    格式: select 字段 from 表1 right [outer] join 表2 on 连接条件 where 过滤条件;


查询不是领导的id与last_name
    oracle:
    select distinct m.id,m.last_name from s_emp e,s_emp m where e.manager_id(+)=m.id and e.manager_id is null;

    sql99:
    select distinct m.id,m.last_name from s_emp e right outer join s_emp m on e.manager_id=m.id where e.manager_id is null;

全外连接
练习:

1.  显示员工的last_name和对应的部门名称, 要求, 把不存在部门编号的员工也给查询到 :

    题1的前置条件: 
        公司的业务需要 , 把id为25的员工 分配到一个新的项目组中, 部门名称待定!

        update s_emp set dept_id=null where id=25;
        commit;


2.  显示每个部门的名称 和 对应的地区的名字 , 没有地区编号的部门也要显示

    1.  s_dept表中的 name
    2.  s_region表中的name

    题2的前置条件: 

        公司业务需要, 增加了新的部门, 但是还没确定办公地点 !

        insert into s_dept values(250,'zhiqiang1',null);
        insert into s_dept values(251,'zhiqiang2',null);
        commit;

3.  显示每个员工的工资 和 对应的工资级别 , 超出工资级别范围的 也要显示出来

    工资表: salgrade 


    老板觉得自己工资太低了,  都不够每天泡个脚的 , 给自己涨了工资 涨到了66666, 
    给自己的小秘书涨到了8888

    update s_emp set salary=66666 where id=1;
    update s_emp set salary=8888 where id=24;
    commit;


    在进行外连接时, 如果进行了between (区间)进行比较 , 区间的两边都要添加(+)


    between losal(+) and hisal(+);