SQL(Structured Query Language )被称为结构化查询语，SQL 是操作和检索关系型数据库的标准语言。SQL是在90年代开发出来的，最早使用SQL的数据库就是Oracle，而且随着时间的发展，基本上只要是数据库都支持了SQL语句，不同的数据库会存在在若干差异，但是基本的核心语法永远都是一样的。
Sql语句很多，在oracle数据库中，主要的sql语句分为5大类
- Select查询语句 DQL
- DML语句（数据操作语言）
Insert / Update / Delete/merge
- DDL语句（数据定义语言）
Create / Alter / Drop / Truncate(删除数据立即生效)
- DCL语句（数据控制语言）
Grant（赋于权限 ） / Revoke（回收权限 ）
- 事务控制语句
Commit / Rollback / Savepoint
模式和模式对象
一个模式(schema)为模式对象(scehma object)的一个集合，每一个数据库用户对应一个模式。模式对象为直接引用数据库数据的逻辑结构，模式对象包含如表、视图、索引、聚集、序列、同义词、数据库链、过程和包等结构。模式对象是逻辑数据存储结构，每一种模式对象在磁盘上没有一个相应文件存储其信息。一个模式对象逻辑地存储在数据库的一个表空间中，每一个对象的数据物理地包含在表空间的一个或多个数据文件中。
实例模式SCOTT
在Oracle12之前，存在一个典型的实例模式----SCOTT模式，该模式演示了一个简单的公司人力资源管理的数据结构。12之后取消，我们这先新建Scott用户,设置好默认的表空间后，创建相关表对象
雇员表（emp）
| emp | |||
| No. | 字段名称 | 字段类型 | 描述 |
| 1 | EMPNO | NUMBER(4) | 雇员的编号，编号的长度是四位数字； |
| 2 | ENAME | VARCHAR2(10) | 雇员姓名，只能有10个字符； |
| 3 | JOB | VARCHAR2(9) | 职位，只能是9个字符； |
| 4 | MGR | NUMBER(4) | 雇员对应的领导编号； |
| 5 | HIREDATE | DATE | 雇佣日期； |
| 6 | SAL | NUMBER(7,2) | 基本工资，其中5位整数，2位小数； |
| 7 | COMM | NUMBER(7,2) | 奖金（佣金），其中5位整数，2位小数； |
| 8 | DEPTNO | NUMBER(2) | 每个雇员所属的部门编号。 |
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('1980-12-17','yyyy-mm-dd'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('1981-2-20','yyyy-mm-dd'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('1981-2-22','yyyy-mm-dd'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('1981-4-2','yyyy-mm-dd'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('1981-9-28','yyyy-mm-dd'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1981-5-1','yyyy-mm-dd'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('1981-6-9','yyyy-mm-dd'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('87-7-13','yyyy-mm-dd'),3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('1981-11-17','yyyy-mm-dd'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('1981-9-8','yyyy-mm-dd'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('87-7-13','yyyy-mm-dd'),1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('1981-12-3','yyyy-mm-dd'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('1981-12-3','yyyy-mm-dd'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('1982-1-23','yyyy-mm-dd'),1300,NULL,10); | dept | |||
| No. | 字段名称 | 字段类型 | 描述 |
| 1 | DEPTNO | NUMBER(2) | 部门编号，编号由两位数字组成； |
| 2 | DNAME | VARCHAR2(14) | 部门名称 |
| 3 | LOC | VARCHAR2(13) | 部门位置 |
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');工资等级表（salgrade）
| salgrade | |||
| No. | 字段名称 | 字段类型 | 描述 |
| 1 | GRADE | NUMBER | 等级的编号 |
| 2 | LOSAL | NUMBER | 此等级的最低工资 |
| 3 | HISAL | NUMBER | 此等级的最高工资 |
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);工资表（bonus）
| bonus | |||
| No. | 字段名称 | 字段类型 | 描述 |
| 1 | ENAME | VARCHAR2(10) | 雇员姓名 |
| 2 | JOB | VARCHAR2(9) | 职位 |
| 3 | SAL | NUMBER | 基本工资 |
| 4 | COMM | NUMBER | 奖金 |
工资表使用相对较少，但是之前的三张表的表结构必须清楚的记下来。
检索数据（***）
查询语句的语法如下所示：
| SELECT [DISTINCT] * | 列 别名，列 别名，.... FROM {tables | views | other select} [别名] [WHERE conditions] [GROUP BY columns] [HAVING conditions] [ORDER BY columns]; |
以后为了方便，所有的固定语法部分使用大写字母，所有的可以更换的地方都使用小写字母区分。
简单查询
--范例：查询部门表的全部信息
SELECT * FROM dept ;
--范例：查询雇员表的全部信息
SELECT * FROM emp ;
--范例：查询出雇员的编号、姓名、职位、基本工资
SELECT empno,ename,job,sal FROM emp ;
--范例：要求查询出雇员的职位
SELECT job FROM emp
--此时的确查询出了结果，但是却存在了重复的内容，这样肯定不方便浏览，这个时候可以使用DISTINCT关键字去掉所有的重复数据。
SELECT DISTINCT job FROM emp ;
--但是，在使用DISTINCT的时候如果查询的是多个列的内容，则只有在每个字段的内容都一样的时候才能消除掉重复数据。
SELECT DISTINCT empno,job FROM emp ;
现在在简单查询语句之中，还可以使用四则运算：+、-、*、/。
范例：查询出雇员的编号，姓名，基本的年薪
SELECT empno,ename,sal*12 FROM emp ;
现在疫情期间，各行各业都不景气，老板要求工资发放60%，计算员工应发的工资。
SELECT empno,ename,sal*0.6 FROM emp ;
但是这个时候又一个问题出现了：有人不知道这些查询的含义。那么要想显示清楚则使用别名。
通过AS关键字
直接空格后面指定别名
SELECT empno as 雇员编号,ename 姓名,sal*12 年薪 FROM emp ;
这个时候又一个问题出现了，这些数据都是分列显示的，不好看，要求可以按照如下的格式显示：SMITH（编号：7369），基本工资：800，职位：CLERK
分析一下之后，可以发现有些数据是需要从emp表中求出来的，而有些数据确实固定显示的，那么肯定需要做一个连接的操作，所以在oracle之中专门提供了一个“||”表示连接，但是在进行连接的时候有一点必须注意，即：所有的字符串必须使用'括起来。
范例：验证||操作
SELECT ename || '，' || job FROM emp ;
范例：完成程序
SELECT ename || '（编号：' || empno || '）,基本工资：' || sal || ',职位：' || job 个人信息 FROM emp ;
简单查询显示的时候有一个特点，就是可以将一张表中的全部数据取出来。筛选查询语句（重点）
简单查询之中是将所有的内容一起进行显示，可是，在某些时候是需要进行数据筛选的，例如：查询工资范围1000~1500的，或者是查询雇佣时间在1980年的，此时就必须使用限定查询的语法，也只是增加了一个WHERE子句而已。
| SELECT [DISTINCT] * | 列 别名，列 别名，.... FROM 表名称 [别名] [WHERE 条件(s)] ; |
范例：要求查询出工资低于1000的雇员信息，sal<1000
| SELECT * FROM emp WHERE sal<1000 ; |
范例：要求查询出工资在1000~1500之间的所有雇员信息
· 现在肯定是两个条件：sal>=1000、san<=1500，应该属于与的关系，必须同时满足
| SELECT * FROM emp WHERE sal>=1000 AND sal<=1500 ; |
这些判断相等的符号，也可以用在字符串或者是日期上。
范例：要求查询出smith的完整信息
| SELECT * FROM emp WHERE ename='smith' ; |
但是，此时数据没有显示出来，因为oracle数据库中的数据是区分大小写的，所以这一点必须将smith大写。
| SELECT * FROM emp WHERE ename='SMITH' ; |
范例：要求查询出在“1980-12-17”日雇佣的雇员信息
· 由于现在使用的是hiredate，所以对于日期的格式按照已有的格式使用即可。
| SELECT * FROM emp WHERE hiredate='17-12月-80' ; |
之前已经完成了一个给定范围的查询，但是对于这种给定范围的查询，实际上在sql语句之中也给了一个明确的操作符：BETWEEN….AND…。
范例：要求查询出工资范围在1500 ~ 3000的雇员信息
| SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000; |
但是现在难度加大了，除了以上的条件之外，还要求满足这些信息的雇员的职位都是经理。
| SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000 AND job='MANAGER'; |
但是后来又改了，又要求查询的职位不是经理了。
| SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000 AND job!='MANAGER'; |
| SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000 AND job<>'MANAGER'; |
范例：要求查询出工资在3000~5000之间的全部雇员信息，或者其所属的职位是业务员，两者之条满足，OR
| SELECT * FROM emp WHERE sal BETWEEN 3000 AND 5000 OR job='CLERK'; |
如果现在想查询出工资不在3000~5000之间，而且也不是业务员，直接求反即可，使用NOT
| SELECT * FROM emp WHERE NOT (sal BETWEEN 3000 AND 5000 OR job='CLERK'); |
但是，对于BETWEEN…AND操作符除了可以使用在数字上之外，在日期上也是可以正常使用的。
范例：要求查询出在1981年雇佣的全部雇员信息
| SELECT * FROM emp WHERE hiredate BETWEEN '01-1月-1981' AND '31-12月-81' ; |
范例：要求显示出编号是7369、7566、7788的雇员信息
· 按照最早的做法，应该使用=完成，而且这些条件应该使用OR操作符
| SELECT * FROM emp WHERE empno=7369 OR empno=7566 OR empno=7788 ; |
可是除了这种做法之外，对于这种已经给定了限定数值的查询，可以直接使用IN语句完成。
| SELECT * FROM emp WHERE empno IN (7369,7566,7788,1234,6789) ; |
如果要对取的内容求反的话，直接使用NOT IN即可。
| SELECT * FROM emp WHERE empno NOT IN (7369,7566,7788,1234,6789) ; |
后来又有了一个要求，老板非常想知道那几个雇员是领取奖金的。
通过查询，可以发现comm有些数据是没有任何内容的，那么没有内容的地方都使用null（空）表示，所以此时判断的条件就应该是其是否为null，在sql中提供了IS NULL、IS NOT NULL两种操作判断。
| SELECT * FROM emp WHERE comm IS NOT NULL ; |
范例：查询所有不领取奖金，但是其基本工资大于2000的雇员信息
| SELECT * FROM emp WHERE comm IS NULL AND sal>2000 ; |
null主要是用于判断字段的，但是有一点必须注意的就是其与IN操作符的结合。
范例：将null应用在IN操作之中。
| SELECT * FROM emp WHERE ename IN ('SMITH','ALLEN','WARD',null) ; |
这个时候的查询没有任何的影响。
范例：将null应用在NOT IN操作之中
| SELECT * FROM emp WHERE ename NOT IN ('SMITH','ALLEN','WARD',null) ; |
此时，一条数据都查询不出来，这一点算是SQL自己的限制了。
在很多的时候模糊查询也是一个很重要的东西，就是关键字检索，就属于模糊查询，模糊查询使用LIKE字句即可，在LIKE字句之中，有两种占位符：
_：表示占一个字符位，是任意的字符；
%：表示占多个字符（0、1、*）。
范例：查询出姓名是以S开头的雇员信息
| SELECT * FROM emp WHERE ename LIKE 'S%' ; |
范例：查询出第二个字母是L的雇员信息
| SELECT * FROM emp WHERE ename LIKE '_L%' ; |
范例：要求查询出在姓名的任意位置上包含字母“S”的雇员信息
| SELECT * FROM emp WHERE ename LIKE '%S%' ; |
这种模糊查询除了可以应用在字符串上也可以应用在数字或者是日期上。
范例：查询工资中包含数字1的雇员信息
| SELECT * FROM emp WHERE sal LIKE '%1%' ; |
范例：查询出在1980年雇佣的雇员
| SELECT * FROM emp WHERE hiredate LIKE '%80%' ; |
注意：如果在查询的时候没有设置任何的查询关键字，则表示查询全部。
| SELECT * FROM emp WHERE ename LIKE '%%' OR hiredate LIKE '%%' OR sal LIKE '%%' ; |
这一点在以后的开发中100%要使用到。
对结果排序（重点）
在之前的查询中，有一定的顺序，但是这个顺序是由数据库自己指定的，如果现在希望可以按照某些字段排序，例如：基本工资或雇佣日期，则就必须使用ORDER BY子句完成排序操作。
| SELECT [DISTINCT] * | 列 别名，列 别名，.... FROM 表名称 [别名] [WHERE 条件(s)] [ORDER BY 排序字段 ASC | DESC，排序字段]; |
ASC表示的是升序（默认），而DESC表示的是降序（需要自己指定）。
范例：要求显示所有雇员的信息，按照工资由高到低进行排序
| SELECT * FROM emp ORDER BY sal DESC ; |
范例：要求显示所有雇员姓名中带有字母A的雇员信息，显示的结果按照雇佣日期排序，由早到晚
| SELECT * FROM emp WHERE ename LIKE '%A%' ORDER BY hiredate ASC ; |
范例：要求显示所有雇员的信息，按照工资由高到低排序，若工资相等，则按照雇佣日期由早到晚排序
| SELECT * FROM emp ORDER BY sal DESC,hiredate ; |
多表关联查询(连接查询)
之前的所有查询都是从emp一张表中取出的数据，如果现在要求从多张表中一起查询数据的话，那么就必须使用多表查询的概念，以下是多表查询的语法：
| SELECT [DISTINCT] * | 列 别名，列 别名 FROM 表名称1 别名，表名称2 别名，.... [WHERE 条件(s)] [ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ; |
但是在进行多表查询的时候也会存在一个问题，下面先进行一个简单的分析。
交叉连接：笛卡尔积，可以根据一定的条件去消除笛卡尔积 cross join
范例：查询emp表中的记录数
| SELECT COUNT(*) FROM emp ; |
范例：查询dept表中的记录数
| SELECT COUNT(*) FROM dept ; |
emp表中一共存在了14条记录，在dept表中一共存在了4条记录，下面开始将两张表一起进行查询。
范例：使用多表查询
| SELECT * FROM emp ,dept ; |
现在是从两张表中一起取出数据，但是现在却返回了56条记录，而且这56 = emp的14条 * dept的4条，这种问题在多表查询中实际上始终存在，被称为笛卡尔积，之所以会产生笛卡尔积主要是由于数据库自己本身的查询机制所造成的。
既然现在产生了笛卡尔积，那么该如何消除呢？
可以直接通过关联字段消除，在dept表中存在了deptno字段，而在emp表中存在了deptno字段，而且emp表中的deptno字段的取值范围有dept表中的deptno字段决定其范围，所以在查询的时候就可以消除掉笛卡尔积了。
| SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno ; |
此时已经消除掉了笛卡尔积，而且数据也已经全部的显示出来了，当然，此时也可以采用部分显示的情况。
范例：查询雇员的编号、姓名、部门名称、部门位置
雇员的编号和姓名在emp表中
部门的名称和位置在dept表中
| SELECT emp.empno,emp.ename,dept.dname,dept.loc FROM emp,dept WHERE emp.deptno=dept.deptno ; |
在使用多表查询时先分析所需要引入的表，之后再找到关联字段消除掉里面的笛卡尔积，但是这个程序本身也是存在一些问题的，此时是通过了“表名称.字段名称”访问的，如果假设表名称非常的长：diqiu_yazhou_zhongguo_beijing_ren，如果每次都使用如此之长的表名称的话，那么这种查询肯定非常的麻烦，所以在多表查询时往往会使用别名的形式。
| SELECT e.empno,e.ename,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno ; |
虽然多表查询可以同时从多张表中查询记录，但是其性能是很差的，尤其是在进行大数据量操作的时候性能更始无比的低，在oracle中存在着sh的大数据用户。
范例：查询sh.sales表的记录数
| SELECT COUNT(*) FROM sales ; |
此表中一共存在着：918843条记录。
范例：查询sh.costs表的记录数
| SELECT COUNT(*) FROM costs ; |
一共存在着：82112条记录。
范例：两张表关联，再次查询全部记录数
| SELECT COUNT(*) FROM costs,sales ; |
此时由于数据量属于相当大的情况，所以多表查询根本就无法使用，那么从而得出结论：不到万不得以，不要使用多表查询，即便使用了多表查询，也必须注意表中的记录不能太多，否则性能有严重的问题。
在emp表中的大部分的字段都已经使用过了，还差了一个mgr的字段，此字段表示的是一个雇员的上级领导的编号
范例：要求可以查询出每个雇员的编号，姓名，领导的姓名
首先分析出要使用的表：
雇员的编号、姓名肯定从emp表中可以查询到；
领导的姓名，从emp表中查到；
此时就相当于是emp表的自身关联，而且两张表的关联条件是：雇员的领导编号 = 领导的雇员编号
| SELECT e.empno,e.ename,m.ename FROM emp e,emp m WHERE e.mgr=m.empno ; |
思考题：
要求显示出每个雇员的编号、姓名、领导的编号、姓名，所在的部门名称，部门位置，工资所在公司的工资等级。
步骤一：雇员的编号、姓名，使用emp表
| SELECT e.empno,e.ename FROM emp e ; |
步骤二：引入自身关联，查询领导的信息，由于是两张表关联，可以使用mgr字段消除笛卡尔积
| SELECT e.empno,e.ename,m.empno,m.ename FROM emp e , emp m WHERE e.mgr=m.empno ; |
步骤三：部门名称及部门位置要使用dept表，那么也需要进行笛卡尔积的消除，依靠deptno字段
| SELECT e.empno,e.ename,m.empno,m.ename,d.dname,d.loc FROM emp e , emp m,dept d WHERE e.mgr=m.empno AND d.deptno=e.deptno ; |
步骤四：工资等级使用salgrade表，这张表没有明确的关联字段，需要指定一个查询范围，使用BETWEEN…AND即可
| SELECT e.empno,e.ename,m.empno,m.ename,d.dname,d.loc,s.grade FROM emp e , emp m,dept d,salgrade s WHERE e.mgr=m.empno AND d.deptno=e.deptno AND e.sal BETWEEN s.losal AND s.hisal ; |
进一步扩展：
如果现在有如下的进一步要求：将每一个工资等级替换成具体的文字信息，例如：
1替换成第五等工资、2替换成第四等工资、3替换成第三等工资，依次类推 à DECODE()函数解决。
| SELECT e.empno,e.ename,m.empno,m.ename,d.dname,d.loc, DECODE(s.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资') FROM emp e , emp m,dept d,salgrade s WHERE e.mgr=m.empno AND d.deptno=e.deptno AND e.sal BETWEEN s.losal AND s.hisal ; |
在使用多表查询的时候，需要那张表就引入那张表，只要是多引入了一张表就要增加一个条件以去掉笛卡尔积。
内连接 INNER JOIN(如果连接不上，会丢失连接条件不成立的数据)
外连接（左外连接，右外连接，全连接）
自然连接 natural join(连接条件是自然发生的，不需要我们指定，连接的依据：根据相同的字段名
单行函数（重点）
数据库从使用来讲，有很多种，但是对于程序人员最需要掌握的实际上是两块内容：SQL语句、单行函数，而所谓单行函数就是指可以完成某些固定操作的函数，由数据库提供，一般单行函数的语法如下：
| 函数名称(操作的列 | 具体的数值 [,若干参数]) |
在Oracle之中，单行函数主要分为以下的五种：字符串函数、数值函数、转换函数、日期函数、通用函数，只有掌握这些函数之后，才可以让开发变得更加的方便。
字符函数
字符函数主要是指的接收一个字符，同时将字符处理之后返回，常用的字符函数有：UPPER()、LOWER()、INITCAP()、REPLACE()、LENGTH()、SUBSTR()、CONTACT()。
但是在调用函数操作的时候，Oracle有一点不方便：所有的操作必须按照标准的SQL语句编写。
范例：进行大小写转换，UPPER()、LOWER()
| SELECT UPPER('Hello'),LOWER('Hello') FROM emp ; |
这个时候一查询就比较麻烦，因为有多少条数据则重复显示多少次，为了解决这种难看，所以在oracle之中提供了一个DUAL的虚拟表，以方便用户验证函数。
| SELECT UPPER('Hello'),LOWER('Hello') FROM dual ; |
至于dual表的由来以后再讲解。
范例：应用函数
| SELECT * FROM emp WHERE ename=UPPER('smith') ; |
范例：现在要求将所有雇员的姓名首字母大写
| SELECT INITCAP(ename) FROM emp ; |
范例：连接字符串
| SELECT CONCAT(ename,' HELLO') FROM emp ; |
这种连接的操作肯定没有使用“||”方便，所以这个函数掌握即可。
范例：要求显示出姓名长度正好是5个的雇员信息
| SELECT LENGTH(ename),ename FROM emp WHERE LENGTH(ename)=5 ; |
范例：要求将所有雇员姓名中的字母A，都替换成“*”
| SELECT ename,REPLACE(ename,'A','*') FROM emp ; |
范例：要求显示每个雇员姓名的前三个字母
| SELECT ename,SUBSTR(ename,0,3) FROM emp ; |
| SELECT ename,SUBSTR(ename,1,3) FROM emp ; |
在使用SUBSTR()函数的时候，无论从1开始还是从0开始其最终的效果都是一样的。
范例：要求截取每个雇员姓名的后三个字母
按照基本的程序思路：求出长度，之后确定一个开始点，一直截取到尾
| SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp ; |
SUBSTR()函数很智能，除了正着截取之外，也可以倒着截取。
| SELECT ename,SUBSTR(ename,-3) FROM emp ; |
数值函数
数值函数常用经常面试有三种：ROUND()、TRUNC()、MOD()
范例：验证ROUND()函数 —— 表示的是四舍五入，而且是带小数点的
| SELECT ROUND(789.63871) FROM dual ; |
直接使用ROUND()函数则意味着直接将小数点之后的内容进行四舍五入。
| SELECT ROUND(789.63871,2),ROUND(789.63871,-2) FROM dual ; |
范例：验证TRUNC()函数 —— 直接截取，不再进行四舍五入了（这里的+，-是指往小数点后还是往小数点前，表示一个方向）
| SELECT TRUNC(789.63871),TRUNC(789.63871,2),TRUNC(789.63871,-2) FROM dual ; |
范例：验证MOD()取余
| SELECT MOD(10,3) FROM dual ; |
数值函数的东西经过某些操作也可以用来计算日期。
日期函数
在讲解日期函数之前必须首先先确定一个问题，如何取得当前的系统时间，如果要取的话直接使用SYSDATE即可。
| SELECT SYSDATE FROM dual ; |
在日期操作中有如下的三个计算公式：（注意：这是在数据库中才成立）
1、 日期 – 数字 = 日期；
2、 日期 + 数字 = 日期；
3、 日期 – 日期 = 数字（天数）；
范例：求出三天后的日期
| SELECT SYSDATE+3 FROM dual ; |
范例：求出前三天的日期
| SELECT SYSDATE-3 FROM dual ; |
范例：求出每个雇员雇佣的天数
| SELECT empno,ename,SYSDATE-hiredate FROM emp ; |
除了以上的计算口诀之外，在Oracle中有以下的几个函数也是很重要的：
MONTHS_BETWEEN(日期1，日期2)：求出两个日期之间的月数；
ADD_MONTHS()：求出几个月之后的日期；
NEXT_DAY()：求出下一个的今天；
LAST_DAY()：求出给定日期的最后一天。
范例：求出每个雇员雇佣的月数
| SELECT empno,ename,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp ; |
范例：求出三个月之后的日期
| SELECT ADD_MONTHS(sysdate,3) FROM dual ; |
范例：求出下一个星期二
| SELECT NEXT_DAY(sysdate,'星期二') FROM dual ; |
范例：求出本月的最后一天
| SELECT LAST_DAY(sysdate) FROM dual ; |
日期操作的时候比较复杂，而且这些函数有可能会同时操作。
转换函数（重点）
在Oracle之中可以将字符串、数字、日期型数据进行互相的转换，使用TO_CHAR()、TO_DATE()、TO_NUMBER()三个函数完成。
范例：观察日期的转换，将日期变为字符串
| SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual ; |
此时月份将显示成07月，如果不希望加入前导0，可以使用fm去掉。
也可以进行分别的拆分。
| SELECT TO_CHAR(SYSDATE,'yyyy'),TO_CHAR(SYSDATE,'mm'),TO_CHAR(SYSDATE,'dd') FROM dual ; |
范例：查询1981年雇佣的雇员信息
| SELECT * FROM emp WHERE TO_CHAR(hiredate,'yyyy')='1981' ; |
TO_CHAR()函数除了对日期可以转换之外，也可以对数字进行格式化。
| SELECT TO_CHAR(123456789.89,'999,999,999.99') FROM dual ; |
但是，如果现在要想表示出本地货币的话，也可以在前面加上一个字母“L”。
| SELECT TO_CHAR(123456789.89,'L999,999,999.99') FROM dual ; |
TO_CHAR()函数的主要功能如果按照专业的术语来讲，属于格式化文字的显示功能。
范例：使用TO_DATE()函数，直接将字符串变为DATE型的数据
| SELECT '1989-09-19',TO_DATE('1989-09-19','yyyy-mm-dd') FROM dual ; |
范例：使用TO_NUMBER函数
| SELECT TO_NUMBER('123') * TO_NUMBER('234') FROM dual ; |
但是，以上的操作即便不使用TO_NUMBER()也可以完成。
| SELECT '123' * '234' FROM dual ; |
聚合函数
组函数（统计函数）
在SQL语法之中，定义了以下的几个常用的统计函数：
1、 COUNT()：统计数据量；
2、 SUM()：求和；
3、 AVG()：求平均；
4、 MAX()：最大值；
5、 MIN()：最小值；
范例：统计所有员工的数量，每个月的总工资
| SELECT COUNT(*),SUM(SAL) FROM EMP; |
范例：统计平均工资，最高及最低工资
| SELECT AVG(NVL(SAL,0)),MAX(SAL),MIN(SAL) FROM EMP; |
但是，一般的统计操作中肯定要对数据进行分组后再统计。
分组统计
什么情况下可能分组？
男、女生各一组、超过18岁的一组，不超过18岁的一组，按各个部门分组，可以发现，只要是有分组的情况下，基本上都会存在某一列的内容有重复的。
实际上，分组的不成文的规定：如果某一个列上存在了重复的内容，则就有可能分组，因为每个个体也可以分组。
如果要使用分组的话，在SQL语句之中直接编写GROUP BY子句即可，此时的SQL语法如下：
| SELECT 分组字段,统计函数 FROM 表名称1 别名，表名称2 别名，.... [WHERE 条件(s)] [GROUP BY 分组字段] [ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ; |
范例：求出每个部门的人数及平均工资
这个操作肯定是按照部门分组，从emp表中，可以发现，deptno字段重复，所以先按照deptno字段分组。
| SELECT deptno,COUNT(empno),AVG(sal) FROM emp GROUP BY deptno ; |
范例：求出每种工作的平均工资
| SELECT job,COUNT(empno),AVG(sal) FROM emp GROUP BY job ; |
此时完成了分组的基本操作，但是一旦SQL语句之中引入了分组操作的话，则就会出现许多的限制；
限制一：在分组之后的查询里，只能出现分组字段及分组函数，其他的字段不能出现
| SELECT job,COUNT(empno),AVG(sal),ename FROM emp GROUP BY job ;（注意：这里给的是一个错误的例子） |
限制二：在使用组函数的时候，可以单独使用，但是一旦要查询了其他字段，则此字段必须是分组条件
| SELECT job,COUNT(empno),AVG(sal) FROM emp ;（注意：这里给的是一个错误的例子） |
以上的分组全部是对着实体表完成的，所谓的实体表是指数据真实存在的表，例如：emp和dept，数据都存在了，除了对实体表分组之外，也可以对临时表分组。
范例：要求统计出每个部门的人数、平均工资，部门要求显示部门的名称
此时先抛开统计不谈，如果按照正常的思路，要查询的：dname、sal、empno
| SELECT d.dname,e.sal,e.empno FROM emp e,dept d WHERE e.deptno=d.deptno ; |
这个时候查询的显示结果是一张临时表的数据，但是分组这种操作并不关心分组的是具体表还是临时表，所以只要有重复那么就分组。
| SELECT d.dname,AVG(e.sal),COUNT(e.empno) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.dname ; |
但是，以上的代码有问题，因为部门是4个，而以上的只显示出了3个。
| SELECT d.dname,NVL(AVG(e.sal),0),COUNT(e.empno) FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.dname ; |
上面完成了单条件的分组，但是分组本身也可以编写多个条件。
范例：查询出每个部门的编号、名称、位置、雇员人数、平均工资、总工资、最高工资、最低工资
| SELECT d.deptno,d.dname,d.loc,NVL(AVG(e.sal),0),COUNT(e.empno), NVL(SUM(e.sal),0),NVL(MAX(e.sal),0),NVL(MIN(e.sal),0) FROM emp e,dept d WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc ; |
范例：要求查询出平均工资大于2000的部门的名称、雇员的人数、平均工资
按照最早的理解，现在肯定要使用条件的判断，最早的判断是在WHERE子句之中完成的。
| SELECT d.dname,COUNT(e.empno),AVG(e.sal) FROM emp e,dept d WHERE e.deptno=d.deptno AND AVG(e.sal)>2000 GROUP BY d.dname ; |
但是此时的代码出现错误，提示：
| WHERE e.deptno=d.deptno AND AVG(e.sal)>2000 * 第 3 行出现错误: ORA-00934: 此处不允许使用分组函数 |
实际上对于WHERE子句来讲主要的功能是对表的记录做出筛选。
例如：以服兵役为主，首先要求从所有的13亿人口中找出所有刚满18岁的青少年。这个属于从所有的数据之中筛选，但是如果这些人到了军营之后，可能按照出生的月份继续分组。第二次的筛选由于要使用了一些统计函数，所以只能通过HAVING子句完成，此语句是直接编写在GROUP BY子句之后的。
| SELECT 分字字段,统计函数 FROM 表名称1 别名，表名称2 别名，.... [WHERE 条件(s)] [GROUP BY 分组字段 [HAVING 分组过滤]] [ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ; |
范例：重新修改之前的查询
| SELECT d.dname,COUNT(e.empno),AVG(e.sal) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.dname HAVING AVG(e.sal)>2000 ; |
WHERE及HAVING的区别：
· 这两个都属于条件的过滤；
· HAVING可以使用统计函数，而WHERE不能使用；
· WHERE属于第一次的筛选，而HAVING属于对分组后的数据的第二次筛选；
思考题：显示所有非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要求满足同一工作的雇员的月工资的合计大于5000，显示的结果按照月工资的合计降序排列。
当题目拿到之后，采用逐步的分析方式，分步骤完成。
步骤一：显示所有非销售人员的信息
| SELECT * FROM emp WHERE job<>'SALESMAN' ; |
步骤二：按工作分组，之后使用SUM()函数求出总和
| SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job ; |
步骤三：要求满足同一工作的雇员的月工资的合计大于5000
这个条件属于分组后的过滤，所以要使用HAVING子句完成。
| SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000 ; |
步骤四：使用ORDER BY进行排序
| SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000 ORDER BY SUM(sal) DESC ; |
面对复杂的问题，肯定不能一次性的全部写出来，就采用逐步的方式慢慢分析。
通用函数（Oracle的特色函数）
在Oracle之中通用函数一共有两种：NVL()、DECODE()。
范例：要求查询出每个雇员的姓名及年收入（应该包含奖金的）
| SELECT empno,ename,(sal+comm)*12 FROM emp ; |
这个时候可以发现，很多的雇员非常的发扬风格，不要年工资了，因为可以领取奖金的雇员只有四个，所以，最终只有这四个有年工资，很明显这种做法是错误的，因为如果奖金为null，应该按照0进行处理，此时就要使用NVL()函数。
范例：验证NVL()函数(如果第一个参数为空，就取第二个参数的值，如果不为空就去第一个参数的值)
| SELECT NVL(null,0),NVL(1,0) FROM dual ; |
范例：使用NVL()函数重新计算
| SELECT empno,ename,(sal+NVL(comm,0))*12 FROM emp ; |
在Oracle中最大特点的函数就属DECODE()了，表示的是多条件判断，DECODE()函数语法如下：（和程序语言中的switch…case类似）
| DECODE(列 | 值，判断条件1，输出结果1，判断条件2，输出结果2，.....) |
此语句非常类似于程序中的if…else，但是此处判断条件，只能是具体的数值。
例如：现在要求查询出雇员的编号、姓名及职位
但是对于职位的显示有要求了，不能显示英文，要求显示成中文。
| SELECT empno,ename,DECODE(job,'MANAGER','经理','CLERK','办事员','ANALYST','分析员','PRESIDENT','CEO','SALESMAN','销售员') FROM emp ; |
开窗函数
子查询的用法
多表查询本身是存在着性能的缺陷，所以在开发之中多表查询不常见，可是有很多的时候又往往需要使用多表查询，那么在这种情况下，往往可以使用子查询进行替代，而所谓的子查询就是在一个大的查询中包含了若干个小的查询，子查询的语法如下：
| SELECT 查询的字段,( SELECT 查询的字段 FROM 表名称1 别名，表名称2 别名，.... [WHERE 条件(s)] [GROUP BY 分组字段] [ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ; ) FROM 表名称1 别名，表名称2 别名，.... ( SELECT 查询的字段 FROM 表名称1 别名，表名称2 别名，.... [WHERE 条件(s)] [GROUP BY 分组字段] [ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ; ) [WHERE 条件(s) ( SELECT 查询的字段 FROM 表名称1 别名，表名称2 别名，.... [WHERE 条件(s)] [GROUP BY 分组字段] [ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ; )] [GROUP BY 分组字段] [ORDER BY 排序字段 ASC | DESC，排序字段 ASC | DESC] ; |
子查询可以在任意的位置上编写，而且不止编写一个，可以编写多个，但是从实际来讲，在FROM和WHERE子句之后出现的子查询比较常见。
再给出一个简单的规定，关于何处使用子查询的个人说明：
· 如果子查询在FROM子句之后编写，往往查询出来的数据是多行多列的，作为临时表出现；
· 如果子查询在WHERE子句之后编写，往往查询出来的数据是单行单列，或者是单行多列的。
范例：要求查询出工资比7566还要高的雇员的信息00
按照此方式理解，则首先需要找到的是7566的工资，返回的内容是一行一列。
| SELECT sal FROM emp WHERE empno=7566 ; |
既然返回的是一行一列，基本上在WHERE子句之后出现较多，而且也应该以此为条件。
| SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE empno=7566) ; |
范例：查询出工资最低的雇员信息
先找到最低的工资的数额。
| SELECT MIN(sal) FROM emp ; |
将以上的查询作为过滤的条件使用。
| SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp) ; |
以上的子查询返回的都是单行单列的内容，子查询实际上一共分为三种：
1、 单行子查询：返回的结果是一行中的多个数据列
2、 单列子查询：返回的是一行一列的内容
3、 多列子查询：返回多行多列的数据
如果一个子查询返回的内容是一张临时表的话，基本上都将其在FROM子句之后编写。
范例：要求统计出每个部门的完整信息，部门的编号、名称、位置、人数、平均工资
按照最早的分组肯定可以实现，但是现在换种情况，使用子查询完成。
在使用分组的时候之前强调过：查询的结果只能是分组条件及分组函数，那么此处肯定无法直接满足，所以现在子查询中完成分组。
| SELECT deptno,COUNT(empno) count,AVG(sal) avg FROM emp GROUP BY deptno ; |
此时的结果返回的是多行多列的数据，那么按照之前的理解，这些数据如果是子查询，放在FROM子句之后比较好处理。
| SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg FROM dept d,( SELECT deptno,COUNT(empno) count,AVG(sal) avg FROM emp GROUP BY deptno) temp WHERE d.deptno=temp.deptno ; |
如果是临时表，则在FROM子句之后编写是最方便的，以上就属于多表查询 + 分组统计 + 子查询。
范例：要求显示出每个工资等级所对应的雇员人数、平均工资
考虑步骤：
1、 现在有统计的要求，肯定使用统计函数，但是统计函数在查询中有限制；
2、 考虑表的关联问题，要引入那几张表；
3、 要考虑笛卡尔积的消除问题。
工资等级只能与emp表的sal字段关联，使用BETWEEN…AND…
| SELECT s.grade,e.sal FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal ; |
再使用子查询包装一次（无意义。。。）
| SELECT temp.grade,AVG(temp.sal),COUNT(temp.eno) FROM ( SELECT s.grade grade,e.sal sal,e.empno eno FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal) temp GROUP BY temp.grade ; |
在子查询的操作之中，也有以下的三个操作的符号：IN、ANY、ALL
范例：要求查询出每个工作的最低工资
| SELECT MIN(sal) min FROM emp WHERE job NOT IN ('CLERK','PRESIDENT') GROUP BY job ; |
以上面的查询为条件验证三种操作符。
IN操作符：
IN本身是指定查询范围的，在子查询中，如果子查询返回的结果是一列多行的话，就相当于指定了一个范围，可以使用IN判断了。
| SELECT * FROM emp WHERE sal IN (SELECT MIN(sal) min FROM emp WHERE job NOT IN ('CLERK','PRESIDENT') GROUP BY job) ; |
ANY操作符：ANY操作符一共有三种使用情况：
=ANY：功能与IN的功能是一样的
| SELECT * FROM emp WHERE sal =ANY (SELECT MIN(sal) min FROM emp WHERE job NOT IN ('CLERK','PRESIDENT') GROUP BY job) ; |
>ANY：比子查询中返回的最小的值要大
| SELECT * FROM emp WHERE sal >ANY (SELECT MIN(sal) min FROM emp WHERE job NOT IN ('CLERK','PRESIDENT') GROUP BY job) ; |
<ANY：比子查询中返回的最大值要小
| SELECT * FROM emp WHERE sal <ANY (SELECT MIN(sal) min FROM emp WHERE job NOT IN ('CLERK','PRESIDENT') GROUP BY job) ; |
ALL操作符，一共有两种使用情况：
>ALL：比子查询中返回的最大值要大
| SELECT * FROM emp WHERE sal >ALL (SELECT MIN(sal) min FROM emp WHERE job NOT IN ('CLERK','PRESIDENT') GROUP BY job) ; |
<ALL：比子查询中返回的最小值还要小
| SELECT * FROM emp WHERE sal <ALL (SELECT MIN(sal) min FROM emp WHERE job NOT IN ('CLERK','PRESIDENT') GROUP BY job) ; |
子查询中也可以返回一行多列的数据，但是这种子查询一般比较少见，通过代码了解以下即可。
范例：查询工作与工资与7654相同的雇员信息
| SELECT * FROM emp WHERE (job,sal)=(SELECT job,sal FROM emp WHERE empno=7654) AND empno<>7654 ; |
但是这种子查询使用的较少，主要是返回多行多列、单行单列的子查询较多。
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
