Oracle01-课程背景-基本概念-DQL查询语句

在互联网时代，我们最初是通过IO流来进行数据的持久化存储，但是随着数据量的增多，仅仅通过IO流处理数据，已经不足以满足我们的需求了。通过IO流存储的数据都是保存在内存当中的，在内存中保存的数据是散乱的，并不是分门别类的存储的。当数据较少的时候，我们直接通过IO流进行操作即可，但是数据越来越多的今天，我们就需要一个专门的仓库来帮我们有条理处理这些数据。
就像超市一样，货少的时候，只需要超市的一个小房间即可；但是货多的时候，一个简单的小房间就不可以满足需求了，需要一个专门的仓库来帮助我们分门别类的管理数据。

1.2.数据库的简单介绍

数据库就是按照我们规定好的数据结构来存储和管理数据的仓库。数据库帮助我们分门别类的管理数据，帮助我们更加高效的对数据进行持久化的存储。

1.3.数据库的优点

数据库具有数据结构化，最低冗余度、较高的程序与数据独立性，易于扩展、易于编制应用程序等优点

1.4.数据库的分类

分类	产品	特点
小型	access、foxbase	负载量笑，用户大概100人以内(留言板、信息管理系统)；成本在千元之内，对安全性要求不高时可以使用
中型	sqlservler、mysql	负载量，日访问量在5000-10000；成本在万元之内(商务网站)；满足日常安全需求
大型	sybase、db2、oracle	海量的负载，可以处理海量数据(sybase<oracle<db2)；安全性高，相对很贵

1.4.1.关系型数据库

        所谓关系型数据库，就是指采用了关系模型来组织数据的存储的数据库。
        那么什么是关系模型呢？
        一个关系模型就是一张二维表格。多个关系模型形成了一张张二维表格，一张张二维表格之间又通过表之间的联系形成了一个项目的整个数据库的存储。
        关联到Java中，我们编写的每个Java类在数据库中就是以一张表的形式存储的，类与类之间的联系就是不同表之间的联系。

优点
        1.易于维护：格式固定，都是表结构，因此维护起来较为简单
        2.使用方便：因为其是支持SQL语言的，因此，不论是普通的查询还是复杂查询，都是可以通过SQL语言来实现，使用起来较为简单。
缺点
        １.读写性能差：对于少量数据而言，读写的效率影响不大，但是对于海量数据而言，读写性能所带来的体验就会相差较远。
        ２.数据格式固定的表结构，不够灵活
        ３.高并发读写需求：传统的关系型数据库是存储在硬盘中的，因此其在高并发读写需求的应用上并不占优。
        4.成本略高：非关系型数据库大多都是免费开源的，关系型数据库则

1.4.2.非关系型数据库

非关系型数据库，一般来说就是NoSQL，在非关系型数据库中，数据的存储方式变得多样化，无论是文档还是键值对等形式，都可以存储。
这样看来，非关系型数据库是不是变得与我们最初的数据库是为了分门别类的存储管理数据的需求不符。

优点
        1.格式灵活：存储数据的格式可以是文档、图片、键值对等等，数据格式并不固定
        2.速度快：相较于关系数据库受限于硬盘的传输速率而言，非关系型数据库则没有这个限制
        3.扩展性好
        4.成本低：非关系型数据库大多都是开源免费的
缺点
        1.不支持SQL
        2.不支持事务
        3.数据结构复杂

1.4.3.关系型数据库与关系型数据库的区别

        1.格式
        对于关系型数据库而言，所有的数据都是以一张二维表的形式存储的，而非关系型数据库则不同，非关系型数据库的存储方式是多种多样的。
        关系型数据库是二维表，格式固定；非关系型数据库则不是固定的某一种，格式灵活。
        2.SQL的支持
        关系型数据库的数据格式较为确定，因此，其是支持SQL语言的数据库类型；对于非关系型数据库而言，其数据格式多种多样，SQL语言依据每种不同的数据格式来专门对其进行实现，无疑是不够合理的。
        关系型数据库支持SQL；非关系型数据库不支持SQL
        ３.使用难易程度
        关系型数据库可以直接通过SQL语言来进行一些查询，使用起来也会较为简单；非关系型数据库的数据格式多种多样，没有一个像SQL的语言来操作这种类型数据库，所以其使用起来不够简单。
        关系型数据库使用简单，非关系型数据库使用较为困难
        ４.存储速度
        关系型数据库的存储需要通过硬盘来存储，非关系型数据库的存储则更为宽泛一些，硬盘或随机存储器都可。
        5.成本
        关系型数据库有很多都不是开源免费的；非关系型数据库大多都是开源的，因此成本较低。

二、SQL语言

2.1.什么是SQL语言？

SQL语言就是数据库语言，是一种结构化查询语言。
通俗的讲，SQL语言就是用来操作关系型数据库的语言。至于数据库都需要哪些操作？无外乎就是我们传统意义上的增删改查等。

2.2.SQL语言的组成

分类	命令
DDL	create：创建；drop：删除；alter：修改；rename: 重命名; truncate：截断
DML	insert：插入；delete：删除；update：更新；select：查询
DCL	grant：授权；revoke：回收权利；commit:提交事务；rollback：回滚事务

三、基本概念

3.1.表

        表，是关系型数据库的基本存储单位，在关系型数据库中，所有的关系都是由一张张的表来存储的，也就是说，一张表就是一个关系。
        既然说，数据库中的数据都是由一张张的表组成的，那么我们的数据库中的表真的就是一张张地表吗？
        事实上，并不是，在数据库中保存的表，只是逻辑意义上地表，他存储地位置并不是跟表所表示地相同，而是通过存储地逻辑，来将其看作是一张表。并不表示真正物理意义上的表。

3.1.1.表结构

既然关系型数据库中保存的是一张张的二维表，那么表的组成是什么呢？
表的组成：表名、列名、表记录

3.1.2.表名

见名知意，表名就是表的名字，我们每张表都需要一张对应地名字。
就像生活中，我们如果创建地表格没有名字，那么，当我们地表越来越多地时候，我们可能并不能够记得，每张表中记录的内容是关于什么的，而，一个优秀的表明则可以帮助我们做到这点。

3.1.3.字段

        字段，就是表中每一列的列名，大概含义和表名类似
        当我们为每一列都设置了对应的列名的时候，那么我们就可以在看到列的时候通过列名知道该列所存储的数据的类型。
        不仅如此，我们对每一列中的数据可能不仅仅是数据类型的要求，我们也许需要有其他的要求，比如说，当我们想要编写一张身份登记表的时候，年龄一定不是负数，因为一个人的年龄为负的时候，则说明，这个人还没有出生，还没出生，当然是不需要进行身份登记的。
        那么，我们是否可以对该列所填写的值，进行一个判断，当判断通过的时候，才允许存储。但是，这优惠产生一个新的问题，那就是，每一个个数据都需要设置的话，我们就会进行了大量的重复性的行为，因此，我们完全可以通过列名，来直接限定某一列的值，此时，就只需要设置一次即可。

3.1.4.记录

记录，就是表中存储的数据，每一行记录就被称为是一个数据。
在数据库中的每个表中，每个记录都是独立的存在，不能互相干扰。

3.2.注释

3.2.1.单行注释

单行注释，只会注释符号后面的本行数据，一旦换行，下面那行不会被注释

-- 注释内容

3.2.2.多行注释

多行注释，在开始符号/*和结束符号*/之间的所有内容均被注释。

/*
注释内容
*/

3.3.约束

        所谓的约束，就是我们前面所说的，对于列的规定。
        为什么我们需要约束?
        对于我们每个列而言，每一列的数据并不是随意存储的，想输入什么就输入什么，所以，我们需要对每个列设置一定的条件，而这些条件大致都分为以下几类。

3.3.1.主键约束

唯一+非空
主键是唯一可以独立确定一行数据的约束。也就是说，当我们为一个字段设置了主键约束，那么，我们就可以根据表中该列的不同值，来找到对应的确定的某一行记录。

3.3.2.非空约束

要求当前字段值不能为null
通俗的讲，就是在这个表中的某一列设置了非空约束，那么这张表的该列的值，都不可为空，必须要有一个确定的值。

3.3.3.唯一约束

要求表中所有数据的该列的字段的值不可重复
当该列重复的时候会报错。

3.3.4.默认约束

为该列的字段值设置一个默认值

3.3.5.检查约束

检查表中所有数据的该列的字段值是否符合要求

3.3.6.外键约束

当表中该列是该表与另外一张表的联系列的时候，该列就需要设置为外键约束。

三、DQL查询语句

DQL是SQL语言中规定的对数据库中保存的记录进行查询的语句。通过DQL查询语句，我们就能够随心所欲的从数据库中拿出我们想要的数据。

3.1.前期准备

3.1.1.dept部门表

3.1.2.emp雇员信息表

3.1.3.salgrade工资等级表

3.2.DQL查询语句的结构

select 数据(要查询的列)... from 数据源 where 行过滤条件 group by 分组字段 having 组过滤条件order by 排序字段 [desc|asc];

select 数据(要查询的列)  
from 数据源
where 行过滤条件
group by 分组字段
having 组过滤条件
order by 排序字段 [desc|asc];

执行流程为： from --> where --> group by --> having --> select --> order by
实际上我们只需要SELECT和FROM就可以进行一些比较简单的查询

3.3.从简到繁[不分组]

3.3.1.SELECT...FROM...

select 数据(*|字段|字段1,字段2..) from 数据源

select * from emp;--查询emp表中的每一条记录的所有列
select ename from emp;--查询emp表中的每一条记录的ename列

执行流程：from > select

解析：执行的先后顺序是满足现实规律的，from后面的是数据源，我们查询数据，出发点是定时数据源，因为数据源中才有我们想要的数据；我们出发点如果不是数据源也就并无意义了。
select：当我们从数据源中获取到数据的时候，我们并不是将所有的数据都拿出来，而是需要进行选择，只需要将我们需要的数据拿出来即可，也就是说，根据select后面的信息，来从查询到的记录中对应的拿出我们需要的数据即可。

3.3.2.SELECT...FROM...WHERE...

select 数据(*|字段|字段..) from 数据源 where 行过滤条件

select ename from emp where deptno = 20;--查询20部门的所有员工的员工姓名

执行流程：from > where > select

解析：where之后跟随的是行过滤条件，所谓的行过滤条件，就是对数据源中的数据进行筛选，将符合要求的记录挑选出来。

问题：为什么where在select之前执行？
        解释一：假设where在select之后执行，当通过select将from后面的数据源中的数据进行了挑选之后，只会留下来select所选定的列，这些列中并不一定有where后面的行过滤条件中所需要的列。此时就会导致一些本不会被通过的记录也被查询出来，不符合需求。
        举例：就像上面的员工信息表，如果先经过select语句，我们就会得到emp表中所有的员工的ename，之后，我们再进行where之后的语句过滤时，就会发现，此时的记录中已经没有了deptno这条依据，因此，无法再进行判断。
        解释二：依据现实中的逻辑，当我们从from后面的数据源中获取到数据，再经过where后面的行过滤条件之后，此时留下来的都是我们已经提前筛选过的，我们肯定需要的数据；最后，再依据select后面的查询来挑选出我们需要的列。
        举例：就像上面的语句，经过where的过滤之后，得到的就全部都是deptno=20的记录了，此时，再由select去除其他我们不需要的列，就能得到最终我们想要得到的数据了。

3.3.3.SELECT...FROM...WHERE...ORDER BY... [asc|desc]

select *|字段... from 数据源 where 行过滤条件 order by 排序字段 desc降序|asc升序,排序字段...

--查询10,30部门的员工信息,要求根据薪资做降序排序
select * from emp where deptno in(10,30) order by sal desc;

执行流程：from > where > select > order by

解析：order by是对查询出来的结果进行排序的，也就是说，它的执行是需要有一个查询结果，才可以进行查询的。所以，他的执行流程排在最后。

3.4.具体使用

3.4.1.查询列select

select distinct *\|字段\|表达式 as 别名 from 表表别名
SELECT * FROM 表名;	查询某个表中所有的记录的所有字段信息
SELECT 列名 FROM 表名;	查询某个表中所有的记录的指定字段信息
SELECT 列名1,列名2 FROM 表名;	查询某个表中所有的记录的字段1 字段2
SELECT distinct 列名 FROM 表名;	去除重复记录
SELECT 表达式 FROM 表名;	查询表达式
SELECT xxx as 别名 FROM 表名表别名;	使用别名

查询所有列*

        查询所有的字段通配符 * ( 书写方便、可以检索未知列;但是降低检索的性能) ,数据的顺序跟定义表结构的顺序一致
        解释：简单来说，查询所有列，共有两种方式：一种是使用通配符；另一种是将表中的所有列都在select后面标出。
        区别：使用通配符的方式较为简单，因为，不管表中有多少列，我们都只需要编写一个*即可查询出所有的列。但是缺点是使用通配符会略微降低效率；使用全部写到select后的方式则是效率略高。编写起来较为麻烦。

select * from dept;--编写简单，效率低
select deptno,dname,loc from dept;--编写麻烦，效率高

查询部分列[字段...]

查询部分字段，指定的字段名
我们大多数的查询都并不是要拿出每一条记录中保存的所有列信息，有时候我们只需要查询某一列或者某几列的信息即可，其他列信息则是我们不需要。此时，我们就需要查询部分列。

单列：

select ename from emp;

多列：

select deptno,dname from dept;

注意：
在select语句的后面书写的字段顺序就我们我们查询出来的最终结果的顺序。

--两者虽然查询的列都相同，但是最终结果的顺序却不一致
select loc,dname,deptno from dept;
select deptno,dname,loc from dept;

去重distinct

        去重就是去除重复数据。
        从字面意思理解，我们也很容易知道去重的作用，但是我们什么时候需要使用到去重呢？
        当我们查询到的结果中有重复的数据，虽然他们归属于不同的记录，但是，我们相同的数据只需要一条的时候，我们就需要对查询结果进行去重

select distinct deptno from emp;

别名as

别名就像是小名，与表名同样表示某一张表，但是当我们为某个表取了别名之后，我们就可以像使用表名一样使用别名，一般来说，我们为表定义的别名都很简略，这也会极大方便我们编写代码。

字段别名：

select ename as "雇员姓名" from emp;
select ename "雇员姓名" from emp;
select ename 雇员姓名 from emp;
select ename as 雇员姓名 from emp;
select ename as " Ename" from emp;

表别名：

select e.ename as "雇员姓名" from emp e;
select e.ename "雇员姓名" from emp e;
select e.ename 雇员姓名 from emp e;
select e.ename as 雇员姓名 from emp e;
select e.ename as " Ename" from emp e;

注意：
1. as 字段别名可以使用 as [as可以省略]；表别名不能使用as
2.""：原样输出，可以存在空格与区分大小写

字符串||

在SQL中，我们的字符串是用一对单引号''包裹起来表示的
使用''表示字符串(注意区分""，上面我们也说过了，SQL语言中的""中的内容是原样输出的) ，拼接字符串则是使用 || 符号来拼接字符串

select ename,sal,comm,comm||'100' from emp;

注意：
1.null值与字符串拼接结果为原串

伪列

不存在的列，构建虚拟的列
所谓不存在的列，就是在实际的表中是不存在的，但是我们却可以查询，查询结果记录数与查询结果一致，且该列的值，全为自己查询的伪列的列名。

select empno, 1*2 as count,'cmj' as name,deptno from emp;

虚表dual

所谓的虚表，就是用于计算表达式，显示单条记录的值

select 1+1 from dual;

null

null 遇到数字参与运算的结果为 null,遇到字符串为空串
解释：与数字参与运算：结果直接为null；与字符串参与运算：null值作为空串参与

select 1+null from dual;
select '1'||null from dual;
select 1||'2'||to_char(null) from dual;

3.4.2.查询行where

比较条件=、>、<、>=、<=、!=、<>、between and

=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
!=	不等于
<>	除什么以外
between and	介于A和B之间

-- 查询20部门的所有员工的信息
--数据 : 员工信息 *
--来源 : 员工表 emp
--条件 : deptno = 20
select * from emp where deptno = 20;

-- 查询30部门的员工的名称和工种
select ename,job from emp where deptno = 30;

-- 查询 员工名称 为SMITH 的员工信息
select * from emp where ename = 'SMITH';

-- 查询工资为800的员工信息
select * from emp where sal = 800;
select e.* from emp e where e.sal = 800;

-- 查询除了'SMITH'之外的所有人信息 
select * from emp where ename != 'SMITH';
select * from emp where ename <> 'SMITH';
select * from emp where not ename = 'SMITH';


-- 查询工资大于1500的员工的工种和姓名
select ename,job,sal from emp where sal>1500;

且或非and、or、not

and	且
or	或
not	非

-- 查询工资在1500~2500之间的员工工种和姓名和工资
select ename,job,sal from emp where sal>1500 and sal<2500;
select ename,job,sal from emp where sal>=1500 and sal<=2500;
select ename,job,sal from emp where sal between 1500 and 2500;

-- 查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资,工种
select ename,deptno,sal,job from emp where job = 'CLERK' and deptno = 20;

-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资,工种
select ename,deptno,sal,job from emp where job = 'CLERK' or deptno = 20;

-- 查询 岗位 不是 CLERK 并且也不在20部门的 员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job! = 'CLERK' and deptno != 20;
select ename,deptno,sal,job from emp where not( job = 'CLERK' or deptno = 20);

null

对于null值的判断则是需要通过is关键字来进行判断

-- 查询工资在1500~2500之间的员工工种和姓名和工资
select ename,job,sal from emp where sal>1500 and sal<2500;
select ename,job,sal from emp where sal>=1500 and sal<=2500;
select ename,job,sal from emp where sal between 1500 and 2500;

-- 查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资,工种
select ename,deptno,sal,job from emp where job = 'CLERK' and deptno = 20;

-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资,工种
select ename,deptno,sal,job from emp where job = 'CLERK' or deptno = 20;

-- 查询 岗位 不是 CLERK 员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job != 'CLERK';

-- 查询 岗位 不是 CLERK 并且也不在20部门的 员工名称 部门编号，工资
select ename,deptno,sal,job from emp where job! = 'CLERK' and deptno != 20;
select ename,deptno,sal,job from emp where not( job = 'CLERK' or deptno = 20);

集合操作union、union all、intersect、minus

Union 并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序

--查询工资大于1500 或 含有佣金的人员姓名[该命令会将重复数据进行去重]
SELECT * FROM EMP WHERE SAL>1500
Union
SELECT * FROM EMP WHERE COMM IS NOT NULL;

Union All 全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序

--查询工资大于1500 或 含有奖金的人员姓名[该命令不会将重复数据进行去重]
SELECT * FROM EMP WHERE SAL>1500
Union All
SELECT * FROM EMP WHERE COMM IS NOT NULL;

Intersect
交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序

--查询工资大于1500 且 含有奖金的人员姓名
SELECT * FROM EMP WHERE SAL>1500
Intersect
SELECT * FROM EMP WHERE COMM IS NOT NULL;

Minus
差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

--查询所有没有员工存在你的部门编号
SELECT DEPTNO FROM DEPT
Minus
SELECT DISTINCT DEPTNO FROM EMP;

like：模糊查询[escape]

精确匹配,但是效率低 [所谓的精确匹配，其实就是like后面的值是一个固定的值]

--查询员工姓名为SMITH的员工信息
select * from emp where ename like 'SMITH';

% 任意个任意字符 [代表的是任意长度的任意字符，也就是说，在%的位置，无论字符个数还是字符种类，都可以通过判定]

--查询中以A开头的员工信息
select * from emp where ename like 'A%';

_ 一个任意字符[可以代表任意的一个字符，但是一个_只能代表一个一个字符]

--查询员工姓名中除第一个字符外，其余字符为MITH的员工信息
select * from emp where ename like '_MITH';

联合使用：

--姓名中倒数第二个字符为E的员工信息
select * from emp where ename like '%E_';

escape：指定转义字符

可以人为的指定任意字符为转义字符
当我们定义了转义字符时，那么在这个like模糊查询中，我们定义的字符此时就不是原本的字符含义，而是作为转义字符使用。

--查询姓名中包含%的
select * from emp where ename like '%A%%' escape('A');

in与exists

in (值列表) 判断前面的某个内容的值是否在in()的值列表中,如果在就是满足,如果不在就是不满足
简单来说，这里的in后面的值列表就是一个个用来对比的值，当判断条件中的记录匹配到值列表中的任何一个值，立即判断通过。即，该条记录满足查询条件

-- 查询工资为 1500， 2000， 2500， 5000的员工的信息
select * from emp where sal = 1500 or sal = 2000 or sal = 2500 or sal = 5000;
select * from emp where sal in (1500， 2000， 2500， 5000); 

-- 查询SMITH,KING员工的信息
select * from emp where ename = 'SMITH' or ename = 'KING';
select * from emp where ename in ('SMITH' ,'KING');

-- 查询10,30部门的员工信息
select * from emp where deptno in (10,30);

exists 存在即保留,存在即合法
从from后的数据源中拿每一条数据,判断where是否满足where条件,where中exists语法,到exists(值列表)值列表中是否存在结果数据,
如果存在；当前从数据源中判断的数据就满足条件,如果不存在；当前这个判断的数据就不满足条件

select * from emp where exists (select * from dept where deptno = 40);
select * from emp where exists (select * from emp where deptno = 40);
select * from emp where exists (select * from emp where comm is not null);

结果：
        1.第一条查询语句查询结果为全部的员工信息
        2.第二条查询语句查询结果为空
        3.第三条查询语句查询结果为全部的员工信息
分析：
        在第一条语句和第三条语句中，exists中的查询语句是有查询结果的，且结果固定，此时，每次从员工表中拿出来的记录进行exists的判定都会被通过，因此查询结果为员工表的全部员工信息
        在第二条语句中，exists中的查询语句的查询结果恒为空，此时，每次从员工表中拿出来的记录进行exists的判定都不会被通过，因此查询结果为员工表的全部员工信息
解决方式：
        可以发现，前面的几行语句要么都通过，要么都不通过，是因为exists后面的查询语句的查询结果是固定的，那么，如果我们可以让exists后面的语句的结果依据前面的每条记录显示不同的情况，不就可以达成我们的需求了？

--查询销售部与会计部的员工信息
select *
  from emp
 where exists
 (select * from dept where dname in('SALES','ACCOUNTING') and dept.deptno = emp.deptno);

注意：
1.在这里可以使用别名

3.4.3.排序

order by 对结果集中数据做显示排序

--查询10,30部门的员工信息,要求根据薪资做降序排序
select * from emp where deptno in(10,30) order by sal desc;
--查询员工信息根据薪资做升序如果薪资相等,根据奖金做降序排序
select * from emp order by sal,comm desc;

问题：
我们发现，查询结果中可能会出现null值的情况，那么，我们的null值是排在数据的前面还是后面呢？
解决：
SQL语言中提供了两个关键词来帮助我们解决这个问题：nulls first 和 nulls last

select * from emp order by comm nulls last;

注意：
1.在根据某个字段做排序的时候,默认升序排序
2.我们同时使用多个字段进行排序时，我们的排序规则是，先根据第一个排序字段排序，在排序过后，对记录中依据第一条排序字段排序后无法排出先后顺序的记录再依据第二个排序字段进行排序。

四、函数

函数是用来对查询出来的数据结果进行一系列的处理并得到一个返回结果。

4.1.单行函数

一条记录返回一个结果

4.1.1.日期函数

在oracle中，我们的日期[年月日时分秒]是以一个数字的格式存储的。

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

当前时间：sysdate和current_date
        两者都返回的是当前时间
        current_date：指的是当前会话时间
        sysdate：指的是当前服务器的时间

select distinct sysdate from emp;
select current_date from emp;

修改日期[天数的+-]
我们对于日期的修改可以直接在select后通过+-来进行

select sysdate,sysdate+2 from dual;

修改月份add_months
参数一：被修改的日期
参数二：要修改的时间，是添加月份还是减少月份，以及减少或增加月份的多少

select ename,sal,hiredate,add_months(hiredate,3) from emp;

月份之差months_between
共两个参数，查询结果为第一个参数减去第二个参数，所得到的月份数

select ename,sal,hiredate,months_between(sysdate,hiredate) from emp;

最后一天last_day
返回所传入日期的当前月的最后一天

--返回雇佣日期 当月最后一天的时间
select ename, last_day(hiredate) d from emp;

下一个星期的时间next_day
        参数一：依据时间
        参数二：星期？
        获取从依据时间开始，往后的第一个星期？的时间

--下一个星期二
select next_day(sysdate, '星期二') from dual;

4.1.2.转换函数

to_date(日期字符串,'模板')    字符串以指定格式转换为日期
        参数一：选定日期相关的字符串
        参数二：设定日期格式[格式要与提供的字符串对应]

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

to_char(日期对象,'模板')    日期以指定格式转换为字符串
        参数一：提供日期对象
        参数二：设定日期格式[保证存储的格式依照自己指定的格式]

select to_date('1888-11-22','yyyy-mm-dd') from dual;

4.1.3.转换函数

decode
        decode(condition,case1,express1,case2 , express2,….casen , expressn,expressionm)
        decode(判定字段,值1,结果1,值2,结果2....,默认值)
        如果字段的值为值1,decode函数结果为结果1,如果为值2,函数结果为结果2....如果以上的值都不相等,最终函数的结果可以为默认值

--给所有的员工 涨薪,10-->10% 20-->8% 30 -->15% 其他-->20% --decode
select ename,
sal,
deptno,
decode(deptno,
10,
sal * 1.1,
20,
sal * 1.08,
30,
sal * 1.15,
sal * 1.2) raisesal
from emp;

case when then else end
case 判定字段 when 值1 then 结果1 when 值2 then 结果2 else 结果n end
如果字段的值为值1,case函数结果为结果1,如果为值2,函数结果为结果2....如果以上的值都不相等,最终函数的结果可以为结果n，即默认值

--给所有的员工 涨薪,10-->10% 20-->8% 30 -->15% 其他-->20% --decode
--case when then else end
select ename,
sal,
deptno,
(case deptno
when 10 then
sal * 1.1
when 20 then
sal * 1.08
when 30 then
sal * 1.15
else
sal * 1.2
end) raisesal
from emp;

4.1.4.其他函数

trunc() 参数取整
对于参数进行取整操作

--trunc() 参数取整
select ename,sal,hiredate,months_between(sysdate,hiredate),trunc(months_between(sysdate,hiredate)) from emp;

nvl nvl(string1,string2) 如果string1为null,则结果为string2的值
简单来说，就是先判断String1的值，值为空，结果为string2的值；值不为空，则还是原值

--为没有奖金的员工设置100奖金
select ename,sal,comm,nvl(comm,0)+100 from emp;

分析：
在comm列中，没有奖金的员工此列的值为null，但是，null值与其他值进行预算值为null；此时，我们就需要讲null值进行修改。
具体使用方式如上

4.2.多行函数

多条记录返回一个结果

注意：
      1.null值不参与组函数计算
        2.如果select后面一旦出现了组函数，那么就只能与其他组函数或者分组字段一起使用
      3.组函数不能使用在where的后面
分析：
        1.null值不参与组函数计算：这里的不参与的意思是，组函数所计算的列的值中有null值的情况下，就会自动跳过该行记录，并不计入计算中
        2.select：对于组函数而言，多行记录得到一个结果，也就是说，此时有多行记录变成了一条记录，很容易我们就可以想到，使用了组函数，那么对于查询结果而言，再使用其他独立字段与分组函数就会进行冲突，所以，我们只能使用其他的组函数或是分组字段
        3.where：通过执行流程来看，where是行过滤条件，组函数并不是行过滤条件，组函数是对查询到的记录来进行运算的。
        也就是说，行过滤条件执行在查询结果出现之前，组函数则是执行在查询结果出现之后，有冲突。

4.2.1.count计数

-- 统计一下一共有多少个员工
select count(*) from emp;
select * from emp;
select count(empno) from emp;
select count(deptno) from emp;
select count(1) from emp;

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

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

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

4.2.2.max最大值、min最小值

-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;

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

4.2.3.sum求和

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

-- 计算20部门每个月的工资花销
select sum(sal) from emp where deptno =20;

4.2.4.avg求平均值

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

4.3.分组group by

通过某一字段，将查询出来的结果通过某一字段进行分组。
举例说明，就比如员工表，每个员工都有其所在的部门的部门编号，而一个部门不会仅有一个员工，那么，我们就可以依据部门编号来进行分组。

select deptno,avg(sal) from emp group by deptno;

注意：
        分组之后，我们查询的记录就不再是每条单独的记录了，而是以组为单位，展示的是组级单位的各种信息。
        比如，依据部门编号分组之后，我们查询结果就是以部门为单位的各种信息，包括部门平均工资等。当然了，我们既然是依据部门编号分组的，那么部门编号也是班级为单位的信息之一。
结论：
        我们发现，当我们使用了分组之后，我们的select关键字之后就不能再查询除分组字段以及组函数之外的其他信息。

4.3.1.过滤组having

当我们将查询结果依据某个字段分组之后，我们还需要对分好组的记录进行过滤，此时，对于组过滤条件在SQL中是编写在having关键字后的。

-- 找出20部门和30部门的最高工资 
--1)先过滤后分组 : 找到所有20,30部门的员工信息,然后根据部门分组,然后计算组函数
select deptno,max(sal) from emp where deptno in (20,30) group by deptno;
--2)先分组后过滤 : 先按照部门分组,10,30,20,然后以组为单位过滤,只要20,30两组
select deptno,max(sal) from emp group by deptno having deptno in (20,30);

4.4.行转列

4.4.1.为什么要使用行转列？

在数据表的设计阶段，我们存入数据库中的数据并不一定是最合适的。有时候，我们发现如果将行列信息转换一下，那么会大大减少数据地冗余，通过，也方便了记录之间的解耦和结构的扩展。
减少数据地冗余；方便了记录之间的解耦和结构的扩展

4.4.2.前提准备

id	name	course	score
1	张三	语文	81
2	张三	数学	75
3	李四	语文	81
4	李四	数学	90
5	王五	语文	81
6	王五	数学	100
7	王五	英语	90

4.4.3.具体实现

--找出课程名(表头)
select distinct course from tb_student;
--数据(行记录) 分组（学生+行转列 decode）
select * from tb_student;
--1、行转列 decode
select name,
decode(course, '语文', score) 语文,
decode(course, '数学', score) 数学,
decode(course, '英语', score) 英语
from tb_student;
--2、分组
select name,
min(decode(course, '语文', score)) 语文,
min(decode(course, '数学', score)) 数学,
min(decode(course, '英语', score)) 英语
from tb_student
group by name;

4.5.rowid与rownum

前提准备

drop table tb_student;
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;

4.5.1.rowid

        rowid是oracle中的一个重要的概念。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。
        rowid是一个伪列，它并不实际存在于表中，他是oracle 在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。
        既然是依据物理地址编码，那么，我们依据rowid就可以快速定位到其所指向的数据行。所以，我们可以用它来删除重复数据。

删除重复数据的执行逻辑：
        1.我们先把重复的数据都查询出来
        2.将重复数据的字段进行分组，因为数据相同，所以，仅保留一项即可
        3.在重复数据中找出每个重复数据的最小或最大的rowid
        4.开始删除，只要rowid在之后没有匹配的时候，删除掉

--1、找出重复数据 :哪个学生 哪门课重复了
select name,course,count(1) from tb_student group by name,course; 
select name,course,count(1) from tb_student group by name,course having count(1)>1;
--2、删除重复数据 :删除重复记录
--每条记录的唯一标识
select s.* , rowid from tb_student s;
--找出保留的rowid
select min(rowid) from tb_student group by name,course;
--删除
delete from tb_student where rowid not in (select min(rowid) from tb_student
group by name,course);

4.5.2.rownum

rownum也是一种伪列，他会根据记录返回一个序列化的数字，排序的结果集。
简单来说，就是他会给所有的查询结果进行排序，从1开始，每次加1

select empno,ename,rownum n1 from emp;

分析：
查询语句的查询结果中有一列为N1，且从第一条记录开始，每条数据的序号加一；
问题：
我们在查询的时候，如果我们对查询结果进行了排序，那么，rownum会不会被干扰所以变成乱序呢？

--如果结果集存在排序,rownum出现乱序情况-->解决方案: 在select后面的嵌套的select,使用外层select结果集的rownum     
select empno,ename ,rownum n2 from (select empno,ename,rownum n1 from emp order by sal desc) ;

分析：
当我们拿到查询结果之后，就需要对结果进行分页操作了，但是这又会产生一个问题，那就是分页后，会对我们再次拍好的rownum又产生干扰。

--要对一个已经确定的结果集的满足条件的rownum进行再一步的判断实现分页--> 嵌套select,内部select确定rownum,外部的select用来对已经确定rownum进行判断的
select * from (select empno,ename ,rownum n2 from (select empno,ename,rownum n1 from emp order by sal desc)) where n2>5 and n2<10;

4.6.分页

4.6.1.为什么要使用分页？

        用户在请求数据的时候，我们是将所有的数据都查询出来，再向用户展示数据。
        假设，我们的数据库中的数据很多，那么，我们每次查询数据的时间就会很长，用户的体验感也不会很好。
        不仅如此，实际上，查询结果中对用户有实际效用的记录可能仅仅只有几条，这么看来，我们一次性将所有的记录都查询出来的方式是不是略微有些许不合理？
        所以，我们要使用分页

4.6.2.什么是分页？

        所谓的分页，实际上就是，增加用户的操作次数来实现对同一种类数据的多次查询，每次查询结果不同。
        什么意思呢？
        简单来说，用户所需要的数据共100条，我们一共分10页，当用户第一次查询的时候，我们仅仅给用户查询并展示前面10条记录。
        如果用户在当前记录中没有找到其想要的数据，就点击下一页或第几页：
        下一页：查询并展示后面十条数据
        第几页：查询并展示第几页的十条数据