Oracle学习笔记21

        数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。数据库现在已经成为数据管理的重要技术，也是计算机的重要分支。由于数据库具有数据结构化，最低冗余度、较高的程序与数据独立性，易于扩展、易于编制应用程序等优点，较大的信息系统都是建立在数据库设计之上的。

        数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称 DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。

   大部分DBMS 提供数据定 义语言 DDL（Data Definition Language）和数据操作语言DML ，供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。 

        

1.SQL语言

       下面执行的语句都是基于以下三张表：dept 部门表、emp 雇员信息表、salgrade 工资等级表

                 

                                                （1）emp 雇员信息表       

                ​   

                                                 （2）dept 部门表  

                        

                                                  （3）salgrade 工资等级表

 （1）SELECT

      主外键约束关系:
       主表|父表 : 具有主键字段
       从表|子表 : 添加一个外键字段
          外键字段关联主表的主键字段
          外键字段中能够出现的值,必须在主表的主键字段中已有的值

注：命令不区分大小写,字符串内容区分大小写

        查询语句：
        select *|数据,数据... from 数据源

（1-1）语法：select *(全部列) from 数据源
             select * from emp; --查询emp表中的全部数据的所有列

 （1-2）查询指定列
        语法：select 字段名1,字段名2,... from 数据源
        select empno,ename,sal from emp;  --查询emp表中所有数据的指定列empno,ename,sal

（1-3）伪列 (整数,表达式,字符串): 表中不存在的字段,但是可查询的内容
                整数、表达式、字符串连接符 |
                select empno,ename,sal,1 from emp;

（1-4）字段别名: 为查询的结果集中的列起别名(名字默认为字段名或者伪列的值)
        语法：select 数据 (as) 别名,数据 别名,数据 别名... from 数据源
        select ename as 员工姓名, sal 员工薪资, 202 "教室    编号" from emp;
        当别名名字内容比较特别,存在特殊符号,空格,小写名称...  可以在名字的前后添加一对""--->""中的内容原封不动显示

（1-5）表的别名: select 数据 from 表名 别名  
            注：表的别名不能添加as
            select e.ename,e.deptno from emp e;

（1-6）去重: distinct 对结果集中完全相同的数据进行去重

             select distinct deptno from emp;

（1-7）虚表 dual（用于计算表达式，显示单条记录的值）
        select distinct 123*456 from emp;
        select sysdate from dual;

（1-8）null（null 遇到数字参与运算的结果为 null,遇到字符串为空串）

                null与数字运算结果为null
                select empno,ename,comm,comm+100 from emp;
                null值与字符串拼接,结果字符串
                select empno,ename,comm,comm||100 from emp;

（1-9）nvl内置函数，判断是否为null,如果为空，取默认值0，否则取字段实际值

        nvl(值1,值2) 当值1为null,结果为值2,当 值1不为null,结果为值1
        select empno,ename,comm,nvl(comm,0)+100 from emp;

（2）条件查询

        条件查询: select 数据字段 from 数据源 where 行过滤条件    --> 满足行过滤条件where的数据被保留在结果集中,只保留指定字段的值select。

执行过程: from -->where-->select

 a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
 b)、and 、or、 not 
 c)、集合函数 union、  union all、    intersect 、minus
 d)、null  :is null、  is not null、  --not is null        
 e)、like :模糊查询 % _ escape('单个字符')

(2-1)between and

        工资在2000到3000之间的员工信息
        select * from emp where sal>=2000 and sal<=3000;

(2-2) and

        检索 工资 2000和3000员工名称 岗位 工资
        select * from emp where sal=1500 and sal=3000;

(2-3) 集合函数 union(两个语句求并集结果去重)、 union all（结果不去重）、    intersect （两个结果求交集）、minus（差集）

        集合函数 union(去重)、 union all、    intersect 、minus

        查询工资大于1500 或 含有佣金的人员姓名
        select * from emp where sal>1500
        union
        select * from emp where comm is not null;

        查询显示不存在雇员的所有部门号。
        select deptno from dept
        minus
        select distinct deptno from emp;

（2-4）null :is null、  is not null、  --not is null

                存在佣金的员工名称

                select * from emp where comm is null;

                不存在佣金的员工名称

                select * from emp where comm is not null;

                select * from emp where not comm is null;

（2-5）like:模糊查询

        模糊查询，使用通配符:

                %:零个及以上(任意个数的)的字符

                 _:一个字符

                遇到内容中包含 % _ 使用escape('单个字符')指定转义符

         查询员工姓名中包含字符A的员工信息
        select * from emp where ename like '%A%';
        查询员工姓名中第二个字母为A的员工信息
        select * from emp where ename like '_A%';

（2-6）in 与 exists

       判断指定的字段的值是否与in后面的值列表中的某个值相等,相等就满足

        查询到部门名称为 SALES 或 ACCOUNTING的部门编号：
        select deptno from dept where dname in('SALES','ACCOUNTING');
        在以上两个部门编号的员工信息
        select * from emp where deptno in(10,30);

exists(结果集) 存在即合理,存在即合法  --> 看得懂
语法：select 数据 from 数据源 where exists(结果集) 
从from的数据源中拿出一条数据,执行where后的判断,判断是否满足条件,看到exists,就观察后面()中的结果集中是否存在数据,存在数据当前数据就满足条件,被保留,不存在不合法

        查询销售部的员工信息：
        select * from emp where exists(select deptno from dept where dname in('SALES') and dept.deptno = emp.deptno);
（2-7）排序

        使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的顺序。

•         顺序 :asc（默认） desc
•         多字段: 在前面字段相等时，使用后面的字段排序
•         空排序: 降序为 desc，注意 null 为最后

        按工资降序

        select * from emp order by sal desc;

        null问题

        select * from emp order by nvl(comm,0),comm desc; select * from emp order by comm nulls first;

        查询雇员姓名，年薪 按佣金排序 默认为升序(asc)，降序为desc，注意null为最后

        select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm desc;

（3）函数

        函数分为系统内置函数 自定义函数（后期学习的 plsql 中定义）；了解系统内置函数（方法），重点掌 握 to_date，to_char（字符和日期的转换）根据函数的返回结果，我们将函数分为单行函数和多行函数。

(3-1)单行函数

1.日期函数

注意区分 db数据库时间 ，java应用服务器的时间。以一方为准

oracle以内部数字格式存储日期年月日小时分钟秒

• sysdate/current_date 以date类型返回当前的日期
• add_months(d,x) 返回加上x月后的日期d的值
• LAST_DAY(d) 返回的所在月份的最后一天
• months_between(date1,date2) 返回date1和date2之间月的数目
• next_day(sysdate,星期一) 下一个星期一

--当前时间
select distinct sysdate from emp;
select sysdate from dual;
select current_date from dual;

-- 2天以后日期
-- 日期可以进行+-
select sysdate,sysdate+2 from dual;

-- 所有员工入职的3天后是几号
select empno,ename,sal,hiredate,hiredate-3 from emp;

-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select empno,ename,sal,hiredate,hiredate+90 from emp;
--add_months(日期,月数)
select empno,ename,sal,hiredate,add_months(hiredate,3) from emp;

-- 查询所有员工到目前为止一共工作了几个月
select empno,ename,hiredate,months_between(sysdate,hiredate) from emp;

-- 查询当前月的最后一天
select last_day(sysdate) from emp;

-- 下一个星期三是几号
select next_day(sysdate,'星期四') from dual;

2.转换函数（重点）

-- 日期对象 与 字符串之间转换问题
--to_char(日期对象,'模板')
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
--to_date(日期字符串,'模板')
select to_date('2021-7-29 15:37:47','yyyy-mm-dd hh24:mi:ss') from dual;

(3-2)多行|聚合|组函数

组函数|多行函数|聚合函数 即多条记录 返回一个结果

• count :统计记录数 count() -->* 或一个列名
• max min: 最大值 最小值
• sum：求和
• avg:平均值

-- 统计一共有几个部门 
select count(deptno) from dept;

-- 统计有员工存在的部门总数
select count(distinct deptno) from emp;

-- 统计20部门一共有多少人
select count(1) from emp where deptno  = 20;

-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

--查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp where deptno = 30;

-- 请查询出 20部门的平均工资， 部门编号
select avg(sal),deptno from emp where deptno = 20 group by deptno;

-- 计算出所有员工的奖金总和
select sum(comm) from emp;

(3-3)分组

        分组: group by , 将符合条件的记录 进一步的分组，过滤组:having, 过滤组信息 ，表达式同where一致。

步骤顺序：1)、from 2)、where 3)、group 4)、having 5)、select 6)、order by

group by : 分组

        1)、select出现分组函数,就不能使用 非分组信息，可以使用group by 字段

        2)、group by字段 可以不出现 select 中 ，反之select 除组函数外的，其他字段必须出现在group by 中

having : 过滤组

        1. where : 过滤行记录，不能使用组函数

        2. having : 过滤组 可以使用组函数

注：

•     一旦分组,分组之后的行为操作都是以组为单位,只能看到有多少组,每组的标签(分组字段的值),但是看不到组中的数据
•     一旦分组,select后面只能查询分组字段或者组函数
•     where中不能使用组函数

-- 找出每个部门的最高工资 
select deptno,max(sal),min(sal),sum(sal),avg(sal),count(1) from emp group by deptno;

-- 找出20部门和30部门的每个部门最高工资 
--1)先过滤后分组
select max(sal),deptno from emp where deptno in(20,30) group by deptno order by max(sal) desc;

--2)先分组后过滤
select max(sal),deptno from emp group by deptno having deptno in(20,30);

-- 求出每个部门员工工资高于1000的的平均工资
select avg(sal),deptno from emp where sal>1000 group by deptno;

-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select avg(sal),deptno from emp where sal>1000 and deptno in(10,20) group by deptno;
select avg(sal),deptno from emp where sal>1000  group by deptno having deptno in(10,20);

-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;