学习笔记day63-----oracle-单行函数、表连接

1、单行函数
    1.1、单行函数和组函数的概念
        单行函数：针对sql语句影响的数据，每一行都作处理，每一行产生一个结果
            select upper(first_name) from s_emp;
        组函数:针对sql语句影响的数据，分组进行处理，每组进行处理，每组产生一个结果
            select count(first_name) from s_emp;
    1.2、测试表:dual
        desc dual
        select count(*) from dual;
    1.3、字符串函数
        upper(s):返回s的全大写形式
        lower(s):返回s的全小写形式
        initcap(s):返回s的每个单词首字母大写，其余小些的形式
            select upper('hello') from dual;
        concat(x,y):字符串连接  ||
        substr(s,start[,length]):从字符串s的satrt开始，向右截取length个字符
            s:源字符串
            start:开始截取的位置，从1开始
                start > 0 表示从字符串的左边开始数
                start < 0 表示从字符串的右边开始数
                start = 0 按照1来处理。
            length:截取的字符串的长度
                缺省时表示截取到字符串的结尾
        length(s):获取s字符串的长度
            --截取s_emp表中first_name的后三位
                select substr(first_name,-3) from s_emp;
                select substr(first_name,length(first_name)-2) from s_emp;
    1.4、数字函数
        round(x[,y]):四舍五入
            y:
                缺省时，默认y=0;例如:round(3.56) = 4;
                y为正整数，四舍五入到小数点后y位;例如:round(3.567,2) = 3.57;
                y为副整数，四舍五入到小数点前|y|位;例如:round(356.7,-2) = 400;
        trunc(x[,y]):截取
            y:
                缺省时，默认y=0;例如:round(3.56) = 3;
                y为正整数，截取到小数点后y位;例如:round(3.567,2) = 3.56;
                y为副整数，截取到小数点前|y|位;例如:round(356.7,-2) = 300;
        ceil(x):向上取整
        floor(x):向下取整
    1.5、日期类型和常用的日期函数
        1.5.1、日期类型
            日期类型:date
            默认格式:dd-MON-yy
            date类型的各部分的表示方式:
            cc      世纪信息

            yy      2位数字的年     18
            yyyy    4位数字的年     2018
            year    英文年份的全拼  twenty eighteen

            mm          2位数字的月             07
            mon(Mon)    月份单词的前三个字母    jul(JUL)
            month(Mon)  月份单词的全拼          july(JULY)

            dd          2位数字的日             05
            dy          星期几单词的前三个字母  thu
            day         星期几单词的全拼        thursday

            hh          12小时制                11
            hh24        24小时制                23

            mi          2位数字的分钟           19

            ss          2位数字的秒             09

    当前系统时间:sysdate
        select  sysdate from dual;
        1.5.2、日期的算术运算
        --日期 + 数字
            select sysdate + 90 from dual;
        --日期 - 数字
            select sysdate - 128 from dual;
        --日期1 - 日期2
            select sysdate - to_date('01-JAN-00') from dual;
        注释:上述计算都是以 天 为单位，如果需要计算小时，分钟则需要 /24/60
            select to_char(sysdate - 1/24,'yyyy-mm-dd hh24:mi:ss') from dual;
        1.5.3、常用的函数
            add_months(date1,n):在日期date1加上n个月
            months_between(date1,date2):date1和date2相差的月数
            next_day(date1,dy):日期date1的下一个dy(星期几)
                select next_day(sysdate,'FRIDAY') from dual;
                //第二个参数，可以使用数字1-7，1表示星期天，7表示星期六
                select next_day(sysdate,6) from dual; 
            last_day(date1):日期date1所在月份的最后一天
                select last_day('05-FEB-18') from dual;
    1.6、类型转换函数
        1.6.1、to_char({date1|n},[,fmt])
                1)日期-->字符串
                    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
                2)数字-->字符串
                    fmt以fm开头
                        格式的构成:
                            9           小数点前代表0-9，小数点后代表1-9
                            0           小数点前代表前导0,小数点后代表0-9
                            .           小数点
                            $           美元符号
                            L           本地货币符号
                            ，          分隔符

                        select to_char(1234.56,'fm099,999.00') from dual;
                        select to_char(salary,'fm$099,999.00') from s_emp;
                        select to_char(salary,'fm$099,999.99') from s_emp;
        1.6.2、to_date(s[,fmt])
                字符串--->日期
                insert into

        1.6.3、to_number(s[,fmt])
                字符串-->数字
                --隐式转换
                    select id,first_name,salary from s_emp where id='1';
                --显示转换
                    select to_number('$001,123.56','fm$099,999.00') from dual;

    1.7、函数的嵌套
        一个函数的返回值，作为另一个函数的参数
        --查询员工的id,first_name,manager_id,如果manager_id为NULL，显示成BOSS
        select id,first_name,nvl(to_char(manager_id),'BOSS') from s_emp;
2、表连接
    2.1、查询员工的编号、名字和所在部门的名称
        2.1.1、查询员工的编号、名字和所在部门的编号
            select id,first_name,dept_id from s_emp;
        2.1.2、查询的字段来自于多张表
            id,first_name:s_emp
            name:s_dept
            select s_emp.id,s_emp.first_name,s_dept.name 
            from s_emp,s_dept 
            where s_emp.dept_id=s_dept.id;
            语法:
                select 字段列表
                from 表名
                where 限制条件
    2.2、笛卡尔积
        如果数据来自于两张表，没有任何限制(关联条件)则返回两表的笛卡尔积。
        select s_emp.id,s_emp.first_name,s_dept.name from s_emp,s_dept;

    2.3、表的别名
        语法:表名 别名 
        select e.id,e.first_name,d.name 
        from s_emp e,s_dept d 
        where e.dept_id=d.id;
        --错误:表一旦命名别名，在当前语句中只能使用别名，不能使用原名
        select e.id,s_emp.first_name,d.name 
        from s_emp e,s_dept d 
        where e.dept_id=d.id;
        --在表连接中，如果字段名在关联的多张表中没有重名则可以省略字段名前的表名或别名(效率低)
        select e.id,first_name,name 
        from s_emp e,s_dept d 
        where dept_id=d.id;
    2.4、表连接
                等值连接 非等值连接 自连接
        内连接:
        外连接:
    2.5、内连接
        符合关联条件的数据被选中，不符合的被过滤掉的
        2.5.1、等值连接
            关联条件使用=号的
            --列出部门及其所在地区，显示部门编号、部门名称、和地区名称
                select d.id,d.name,r.name 
                from s_dept d,s_region r 
                where d.region_id=r.id; 
        2.5.2、非等值连接
            关联条件不使用=的
            --创建表
                create table salgrade(
                        grade number(3) primary key,
                        losal number(11,2)
                        hisal number(11,2)
                        );
            --插入测试数据
                insert into salgrade values(1,700,1200);
                ...
            --需求:列出员工及其
            select e.id,e.first_name,e.salary,s.grade from s_emp e,salgrade s where e.salary between s.losal and s.hisal;
        2.5.3、自连接
            逻辑上，把一张表当成两张表使用，采用表连接的语法执行的查询操作.
            --列出员工表中的所有领导信息
                列出员工表中的id，first_name，manager_id
                select id,first_name,manager_id from s_emp;
                列出员工表中，员工的信息，和员工领导的信息
                select distinct e.id,e.first_name from s_emp m,s_emp e where m.manager_id=e.id order by e.id;
        2.6、外连接
            外连接的结果集=内连接的结果集+匹配不上的数据行
            外链接的语法:(+) --oracle
                表1.字段(+) 运算符 表2.字段
                    内连接的结果集+表2中匹配不上的数据行
                表1.字段 运算符 表2.字段(+)
                    内连接的结果集+表1中匹配不上的数据行
            2.6.1、自连接
                --列出普通员工的信息
                select e.id,e.first_name from s_emp m,s_emp e where m.manager_id(+)=e.id and e.id is null;

set linesize 200
set pagesize 40