学习笔记day64-----oracle-sql标准的表连接、集合运算、函数和分组、子查询、表操作

1、sql标准中的表连接
    1.1、内连接
        语法:
            select 字段列表 
            from 表1 [inner] join 表2
            on 关联条件;
        --三表
            select 字段列表
            from 表1 join 表2 on 关联条件1
                    join 表3 on 关联条件2;
            select e.id,e.first_name,d.name dname,r.name rname from s_emp e join s_dept d on e.dept_id=d.id join s_region r on d.region_id=r.id;
    1.2、外连接
        左外连接:内连接的结果集加上左表匹配不上的数据
            select 字段链表
            from 左表 left outer join 右表
            on 关联条件;
        右外连接:内连接的结果集加上右表匹配不上的数据
            select 字段列表
            from 左表 right [outer] join 右表
            on 关联条件;
        全外连接:内连接的结果集加上两表匹配不上的数据
            select 字段列表
            from 左表 full [outer] join 右表
            on 关联条件;
        --内连接
            select e.id,e.first_name,d.name from s_emp e join s_dept d on e.dept_id=d.id;
        --左外连接
            select e.id,e.first_name,d.name from s_emp e left join s_dept d on e.dept_id=d.id;
        --右外连接
            select e.id,e.first_name,d.name from s_emp e right join s_dept d on e.dept_id=d.id;
        --全外连接
            select e.id,e.first_name,d.name from s_emp e full join s_dept d on e.dept_id=d.id;
            select e.id,e.first_name,d.name from s_emp e full join s_dept d on e.dept_id=d.id where e.id is null;
2、集合运算
    union :返回两个结果集的并集，去掉重复行
    union all：返回两个结果集的并集，不去重复行
        select id from s_emp union select id from s_dept;
        select id from s_emp union all select id from s_dept;
    intersect：返回两个结果集中相同的部分
        select id from s_emp intersect select id from s_dept;
    minus:返回第一个结果集减去两个结果集的交集之后的部分
        select id from s_emp minus select id from s_dept;
    注意:两个结果集的字段列表的数量和数据类型要匹配
        还可以用null来填充
        select first_name,id from s_emp union select null,id from s_dept;

3、组函数和分组
    3.1、常用的组函数
        3.1.1、功能
            count   统计一组数据的个数
                select count(salary) from s_emp;
                参数可以是任意类型 可以使用*
            max     统计一组数据的最大值
            min     统计一组数据的最小值
                select max(salary),min(salary) from s_emp;
                select to_char(max(start_date),'yyyy-mm-dd'),to_char(min(start_date),'yyyy-mm-dd') from s_emp;
                参数可以是数值、数值、日期字符串
            sum     统计一组数据的和
            avg     统计一组数据的平均值
                select sum(salary),avg(salary) from s_emp;
        3.1.2、组函数对NULL的处理--忽略
            select count(manager_id) from s_emp;
        3.1.3、参数可以使用distinct
            select count(salary),count(distinct salary) from s_emp;
            select sum(salary),sum(distinct salary) from s_emp;
    3.2、分组
        3.2.1、分组语句的语法
                多列分组
            根据分组标准，把数据分成多个部分
                group by 分组标准 
                select 
                from
                where
                group by
                order by
        3.2.2、按照部门分组，统计每个部门的人数
            select dept_id,count(id) cnt from s_emp e group by dept_id;         
            --在分组语句中，字段要么是分组标准，要么是组函数的参数
        --列出每个部门的编号、名称以及人数
        select e.dept_id,d.name dname,count(e.id) from s_emp e join s_dept d on e.dept_id=d.id group by e.dept_id,d.name;
        --列出部门人数多余2人的部门    
            错误的，主函数不能在where子句中使用
        select e.dept_id,d.name dname,count(e.id) from s_emp e join s_dept d on e.dept_id=d.id where count(id)>2 group by e.dept_id,d.name;
        3.2.4、having子句
            语法:
                having 条件
            功能:在分组语句中，根据条件筛选符合条件的组
            select dept_id,count(id) cnt from s_emp where 1=1 group by dept_id having count(id)>2 order by cnt desc;

            语法顺序:
            select      列出要显示的字段列表
            from        从哪个表中检索数据
            where       从元表中根据条件筛选符合条件的行
            group by    根据分组标准，把数据分成多个部分
            having      根据条件，对分组结果进行筛选，检索出符合分组条件的部分
            order by    根据排序标准对结果集进行排序
            执行顺序:
            from        从哪个表中检索数据
            where       从元表中根据条件筛选符合条件的行
            group by    根据分组标准，把数据分成多个部分
            having      根据条件，对分组结果进行筛选，检索出符合分组条件的部分
            select      列出要显示的字段列表
            order by    根据排序标准对结果集进行排序

        --统计每个部门的平均工资，累出平均工资超过1400的部门信息
        select d.id,d.name,avg(salary) avgs from s_emp e join s_dept d on e.dept_id=d.id group by d.id,d.name having avg(salary)>1400 order by avgs desc;

4、子查询
    子查询就是一个select语句嵌入到另一条sql语句中，作为其中的一部分。
        其中，嵌入的select语句，称为子查询;外层的sql语句，称为父查询
    在执行时，先执行嵌入的子查询，再执行父查询。
    4.1 where子句中
        4.1.1、单值的子查询
            where子句中的条件 可以使用比较运算符(<>等)
            --列出工资高于‘Ben’的员工的信息
                select id,first_name,salary from s_emp where salary>(select salary from s_emp where first_name='Ben');
        4.1.2、多行单列的子查询
            当子查询的结果是多行单列时，需要使用能够处理多只的运算符，比如in、not in、any、all等。
            any、all需要和比较运算符配合使用，比如>any、<all等
            --使用子查询，列出s_emp表中的领导的信息
                select id,first_name from s_emp where id in(select distinct manager_id from s_emp);
            --列出所有普通员工的信息
                select id,first_name from s_emp where id not in(select distinct nvl(manager_id,0) from s_emp);
            注意:在not in中不能带有null值，
                原理:
                    in(null,1,2)==> x=null or x=1 or x=2
                    not in(null,1,2)==> x!=null and x!=1 and x!2 ==>永远为假
        4.1.3、子查询中需要引用父查询中的字段,使用exists关键字
            --列出有员工的部门
                select d.id,d.name from s_dept d where exists (select * from s_emp e where e.dept_id=d.id);
    4.2、having子句
        --列出平均工资高于43号部门平均工资高的部门信息
        select dept_id,avg(salary) avgs from s_emp group by dept_id having avg(salary)>(select avg(salary) from s_emp where dept_id=42);
    4.3、from子句中
        任何一个合法的select语句，都会在内存中构建一张临时表，可以称为内存表
        --列出工资高于公司的平均工资的员工信息
            select id,first_name,salary from s_emp where salary>(select avg(salary) from s_emp);
        --列出工资高于本部门平均工资的员工
            select e.id,e.first_name,e.salary,s.avgsa from s_emp e, (select dept_id did,avg(salary) avgsa from s_emp group by dept_id) s
            where e.dept_id=s.did and e.salary>s.avgsa;
    4.4、select子句中
        把子查询放到select子句中，可以认为是外连接的另一种实现方式
        --列出所有员工的编号名字和所在部门的名称
        select e.id,e.first_name,(select name from s_dept d where e.dept_id=d.id) dname from s_emp e;

5、表操作
    5.1、标识符的命名
        1) 首字母必须是英文字母
        2) 包含因为字母、数字、'_'、$、#
        3) 1~30未完、
        4) 不能和其他的数据库对象重名
        5) 不能是使用关键字
    5.2、创建表和删除表
        1) 语法:
            create table 表名(
                        字段名 数据类型，
                        字段名 数据类型，
                        ...
                        字段名 数据类型。
                );
        2) 示例
            create table emp_lx_42( id number(7), name varchar2(20), salary number(11,2), s_date date);
        5.2.2、删除表
            语法:
                drop table 表名
            --删除emp_lx_42
            drop table emp_lx_42;
            select table_name from user_tables where table_name like 'EMP%';

        5.3
            5.3.1、insert语句
                功能:向表中写入数据行
                1)语法
                    insert into 表名[(字段列表)] values(值列表);
                2)示例
                    insert into emp_lx_42(id,name,salary,s_Date) values(1,'test1',2500,to_date('2018-07-06','yyyy-mm-dd'));
                提交
                commit;
                    insert into emp_lx_42 values(2,'test2',2000,to_date('2018-07-05','yyyy-mm-dd'));
                    insert into emp_lx_42(id,name,s_date) values(3,'test3',sysdate);
            5.3.2、update语句
                功能:根据条件更改表中的数据
                语法:
                    update 表名 set 字段=新值[,字段=新值,...] [where 子句];
                --修改员工的工资，每人加1000,3000封顶
                    update emp_lx_42 set salary = 3000 where salary > 2000;
                    update emp_lx_42 set salary = salary + 1000 where salary <= 2000;
                --把salary为null的员工的salary设置为2500
                    update emp_lx_42 set salary = 2500 where id = 3;
                回滚
                rollback;
                update emp_lx_42 set name='Ben',salary=2500,s_date='01-JUL-18' where id=1;
            5.3.3、delete语句
                功能:根据条件删除表中的数据行
                语法:
                    delete [from] 表名 [where 子句];
                示例:
                    delete from emp_lx_42;
                    delete from emp_lx_42 where id = 3;
                --截断表
                truncate table 表名;--属于ddl语句
                功能:相当于没有where子句的delete语句，而且不能回滚