学习笔记-单行函数和多表查询

SQL函数
SQL函数有输入参数，并且总有一个返回值。
根据函数操作数据行数的不同，可分为单行函数和多行函数。
单行函数根据操作对象的不同有可分为：字符函数，数字函数，日期函数，转换函数和通用函数。
多行函数操作的是成组的多个行，每个行组返回一个结果，这些函数又称为组函数。

字符函数
    接受字符数据作为输入，既可以是字段名也可以是表达式。
    返回的既可以是字符值也可以是数字值。

    常见函数：
    lower(in)               将输入的字符值全部转换为小写
    upper(in)               将输入的字符值全部转换为大写
    intcap(in)              将输入的字符值的首字母变成大写其他变为小写
    concat(in1, in2)        将输入的in2连接到in1上
    substr(in, m [,n])      将输入的in从m位置截取到n位置，n不写表示末尾
    length(in)              返回输入的in的字符长度
    replace(in, c1, c2)     在in中将找到的c1替换为c2
    lpad(in, n, c)          从in左边填充c，知道满足长度n
    rpad(in, n, c)          从in右边填充c，知道满足长度n
    instr(in,char[,m][,n])  
        返回char在in中的位置。从m位置值开始查找，n表示第几次发现。m，n默认都是1

    演示
    concat('Oracle','DB')                   OracleDB
    substr('OracleDB', 6)                   DB
    length('Oracle')                        6
    instr('OracleDB', 'DB')                 7
    replace('OracleDB','DB','Database')     OracleDatabase
    lpad('Oracle', 12, '*')                 ******Oracle

    例子
    select id, first_name || ' ' || last_name as 'Name', Email, length(Email), 
    instr(Email, '@') from employees where substr(job_id, 4) = 'REF';

数字函数

    常用函数：
    round(in [,n])      将数字四舍五入，n表示小数位数，不写为0
    trunc(in [,n])      将数字截断，n表示截断到的小数位置，不写为0
    mod(in1, in2)       返回in1除以in2的余数

    演示
    select round(123.456) from dual;        123
    注意：dual表示属于sys用户的，可以被所有用户访问。这个表没有实际意义，
    当目标表不存在时，为了达到select语法的完整，而使用的一个不需要从表中取出数据的表。

    round(123.456, 2)       123.46
    round(123.456, -1)      120
    trunc(123.456)          123
    trunc(123.456, 2)       123.45
    trunc(123.456, -1)      12
    trunc(123.456, -3)      0
    mod(21, 5)              1
    mod(-21, 5)             -1

日期函数
    在oracle中，日期是以数字格式存储的，显示和输入的格式是DD-MON-RR(日-月-年),但是在其他客户端中可能会以不同的格式显示。在pl/sql dev中，显示格式为yyyy/mm/dd

    函数
    sysdate                         获取当前服务器的日期和时间
    months_between(date1, date2)    返回两个日期之间的月数
    add_months(date, n)             在date日期上加n月
    next_day(date, char)            date日期的下个星期的星期几，char代表周几
    last_day(date)                  返回date日期中本月的最后一天的日期
    round(date[,'fmt'])             格式化四舍五入date日期
    trunc(date[,'fmt'])             格式化截取日期

    演示
    months_between('17-JUN-13', '21-JAN-11')        28.87
        月份为1到12月英文单词的前三个字母的缩写，
    add_months('17-JUN-13', 10)                     '17-APR-14'
    next_day('17-JUN-13', 'SUNDAY')                 '24-JUN-13'
        周几对应的就是英语的周一到周日
    last_day('17-JUN-13')                           '30-JUN-13'

    假定系统当前日期为'17-JUN-13'。 JUN:6月, JUL:7月, JAN:1月
    round(sysdate, 'MONTH')                 '01-JUL-13' 
    round(sysdate, 'YEAR')                  '01-JAN-13'     
    trunc(sysdate, 'MONTH')                 '01-JUN-13'
    trunc(sysdate, 'YEAR')                  '01-JAN-13'

    注意：在对月份进行四舍五入时，1日到15日舍弃返回1日，16-31日返回下月1日。在对年份进行四舍五入时，1-6月返回当前年数的1月1日，7-12月返回下年的1月1日。

转换函数
    函数
    to_char(date [,'fmt'])
    to_number(char [,'fmt'])
    to_date(char [,'fmt'])

    to_char函数
        对日期使用
            日期格式元素
            YYYY、 YYY、 YY、 Y、 Y,YYY         2013、013、13、3、2,013
            YEAR                                拼写年份
            MM                                  两位数字表示月份
            MONTH                               月份的全拼,右端补齐空格，总长度为9个字符
            Mon                                 月份的前三个字母，首字母大写
            WW, W                               在本年或本月的第几周
            DAY                                 周几的全拼，右端补齐空格，总长度为9
            DDD、DD、D                            本年，本月，本周的第几天
            ......

            时间格式元素
            AM, PM                              根据时间自动设置为AM, PM
            HH, HH12, HH24                      12,12,24格式的时间
            MI                                  分钟0-59
            SS                                  秒


            例子
            select to_char(sysdate, 'YYYY/MM/DD,HH24-MI-SS') from dual;
                显示：2013/06/17,15-44-26


            fm元素的作用：删除填补的空或者前导0
            例子
            select to_char(sysdate, 'fmYYYY/MM/DD,HH24-MI-SS') from dual;
                显示：2013/6/17,15-44-26

            th，sp元素，th加在数字后面表示序号，sp加在数字后面表示让数字以英文的形式显示。

        对数字使用
            格式元素
                9               9的个数决定数字的宽度
                0               显示前导0
                $              美元符号
                L               本地符号货币
                .               指定小数点位置
                ，               指定逗号位置
                ......

            演示
                to_char(2468, '999999')         2468，前面有两个空格
                to_char(2468, '099999')         002468
                to_char(2468, '$999999')		$2468，前面有两个空格
                to_char(2468, 'L999999')        ￥2468，前面有两个空格
                to_char(2468, '999999.99')      2468.00,前面有两个空格
                to_char(2468, '999,999')        2,468 前面有两个空格

    to_number函数
        将字符串转换为数字

        演示
            to_number('2468', '9999')           2468
            to_number('2468', '999')            报错，因为超过范围
            to_number('2468', '999999')         2468
            to_number('2468s', '99999')         报错，因为该字符串不能转换为数字
            to_number(to_char(sysdate, 'YYYY'), '9999')     2013

    to_date函数
        例子
            select to_date('17-JUN-13') from dual;      2013/6/17

        fx元素，进行精确匹配
            演示
                select to_date('Jan 03, 2006', 'fxMon DD, YYYY') from dual;
                    显示2006/1/3，匹配成功就显示结果
                select to_date('Jan 3, 2006', 'fxMon DD, YYYY') from dual;
                    报错，DD显示为本月的第几天，为两位数
                select to_date('Jan 03,2006', 'fxMon DD, YYYY') from dual;
                    报错，精确匹配的年份前面有空格，但是目标字符上没有空格

通用函数
    3个函数都是用来替换null空值的
    nvl(expr, val)
        用val替换为空的expr，expr可以为表达式，也可以为字段名
        nvl(commission_pct, 0)，将佣金百分比列为null的替换为0

    nvl2(expr, val1, val2)
        nvl函数的升级版，如果expr为空，返回val1的值，如果val1为空，返回val2的值

    coalesce(expr, val1, val2, .... valn)
        nvl2的升级版，如果expr为空，返回val1的值，如果val1为空，返回val2的值，依次类推
        coalesce(commission_pct, manager_id, '老板'), 如果佣金百分比为空，返回经理id，若经理id为空，返回 老板
        注意：但是，这里面的列表都要为同一类型，不然会报错的

多表查询
表别名
字段别名使用as关键字，表别名不用
select * from employees e, jobs j where e.id = j.id;
为什么使用表别名？
当跨表查询数据时，一般使用 表名.字段名 来区分不同的表，这是为了提高效率，如果不写表名，
直接进行查询，也行但是效率低。但是如果两个表中有相同的字段名时，就必须要使用 表名.字段名。
但是当要夸多表查询时，使用 表名.字段名 的方式，就会显得冗长。所以要采用表别名。
使用字段别名是为了方便显示的阅读，使用表别名是为了使代码简便，易懂。
一般表别名的长度不超过30个字符。

等值连接
    select * from employees e, jobs j where e.id = j.id;

自然连接
    select * from employees natural join jobs; 
    根据两表中 字段名和数据类型 都相同的列，进行匹配，返回两者都有的记录
    自然连接属于等值连接的一种

using子句
    select job_id, e.first_name from employees e join jobs j using(job_id);
    根自然连接的流程一样，返回两表中job_id相匹配的记录。
    值得注意的是，如果select读取的有两表进行匹配的字段名，那么就不用写具体的表别名了。
    因为进行配的字段名是属于连接字段，不在属于某个表了。

on子句
    select .... from employees e join jobs j on(e.id = j.id);

内连接
    在两个表中，仅返回匹配条件的行的连接 称为 内连接。
    等值连接就是内连接的一种。还有一种就是非等值连接。

外连接
    在两个表中，不仅返回匹配条件的行的连接，还返回不匹配的行的连接

    左外连接
        select * from employees e left outer join jobs j on(j.id = e.id);
        employees表为左表，返回的是e.id 中的所有记录，有和j.id匹配的，也有不匹配的。

        select * from employees e, jobs j where e.id = j.id(+);
        另一种写法，+号 放在不需要增加未匹配的行的所属表一侧。
        employees 表还是左表。

    右外连接
        select * from employees e right outer join jobs j using(id);
        select * from employees e, jobs j where j.id = e.id(+);
        这个中 jobs 表是右表，进行右外连接，在employees表一侧加 + 号。

    全连接
        select * from employees e full outer join jobs j using(id);
        +号，是不能再在 等号 两端加的。所以只有一种写法。

    笛卡尔连接
        没有什么实际意义，不经常使用。
        一个表中有10个符合匹配的行，另一个表中有20个符合匹配的行，则返回200的记录。