一. Oracle字符串操作
字符串类型
1.CHAR和VARCHAR2类型
表示字符串数据类型，用来在表中存放字符串信息；
CHAR存放定长字符，即存不满补空格（浪费空间，节省时间）；VARCHAR2存放变长字符，存多少占用多少（浪费时间，节省空间）；
按照字符的自然顺序排序。
2.CHAR和VARCHAR2的存储编码
默认单位是字节，可指定为字符
— CHAR(10)，等价于CHAR(10 BYTE)
— 指定单位为字符：CHAR(10 CHAR)，20个字节
— VARCHAR2(10)，等价于VARCHAR2(10 BYTE)
— 指定单位为字符：VARCHAR2(10 CHAR)，20个字节
每个英文字符占用一个字节，每个中文字符按编码不同，占用2-4个字节
— ZHS16GBK：2个字节
— UTF-8：2－4个字节
3.CHAR和VARCHAR2的最大长度
CHAR最大取值为2000字节，最多保存2000个英文字符
VARCHAR2最大取值为4000字节
CHAR可以不指定长度，默认为1，VARCHAR2必须指定长度
4.LONG和CLOB类型
LONG：VARCHAR2加长版，存储变长字符串，最多达2GB的字符串数据
LONG有诸多限制：每个表只能有一个LONG类型列；不能作为主键；不能建立索引；不能出现在查询条件中
CLOB：存储定长或变长字符串，最多达4GB的字符串数据
ORACLE建议开发中使用CLOB替代LONG类型
字符串函数
1.CONCAT和“||”
CONCAT(char1, char2);
— 返回两个字符串连接后的结果，两个参数char1，char2是要连接的两个字符串
等价操作：连接操作符“||”
如过char1和char2任何一个为NULL，相当于连接了一个空格
“||”在数据库中是连接字符串，相当于java中的“+”，注意和java“||”区分
eg：
java中：”hello” +“world” ==> “helloworld"
DB中：’hello’||’world’==> ‘hello world'
oracle中：CONCAT(‘hello’,’world’)==> ‘hello world'
2.FROM DUAL(虚表)
DUAL：虚表，没有这么一个表，只为了满足SELECT的语法要求。
— 我们常用虚表来测试表达式的结果。
— 在数据库中，我们想测试某个表达式的结果只能使用SELECT语句来实现
— 什么时候使用虚表：当SELECT语句中没有任何表中的字段参与时
— eg: 假设表emp中name和sal两个字段存储的内容分别是名字和薪资
SELECT name||’:’||salFROM emp
则所得结果为：boss:5000
3.LENGTH
LENGTH(char)：用于返回字符串的长度
如果字符类型是VARCHAR2，返回字符的实际长度，如果字符类型是CHAR，长度还要包括后补的空格
eg: SELECT name,LENGTH(name) FROM emp;
所得结果：boss 4(name为CHAR)
4.UPPER，LOWER和INITCAP
大小写转换函数，用来转换字符的大小写
UPPER(char)用于将字符转换为大写形式
LOWER(char)用于将字符转换为小写形式
INITCAP(char)用于将字符串中每个单词的首字符大写，其它字符小写，单词之间用空格和非字母字符分隔
如果熟人的参数是NULL值，仁返回NULL值
5.TRIM，LTRIM，RTRIM
作用：截去子串
语法形式：
— TRIM(c2 FROM c1)从c1的前后截去c2
— LTRIM(c1[, c2])从c1的左边(Left)截去c2
— RTRIM(c1[, c2])从c1的右边(Right)截去c2
如果没有c2就去除空格
eg：SELECT TRIM(‘e’from ‘eeeeliteeee’) FROM DUAL;
参数中from前面只能是单一字符
若没有from以及前面的字符，则是去除空白
eg：SELECTLTRIM(‘eeeeliteeee’,’e’) FROM DUAL;
SELECTRTRIM(‘eeeliteee’,’e’) FROM DUAL;
不指定第二个参数，默认是去除空白
6.LPAD, RPAD
补位函数，用于在字符串char1的左端或右端用char2补足到n位，char2可重复多次
— LPAD(char1, n, char2)左补位函数
— RPAD(char1, n, char2)右补位函数
在emp表中使用左补位，将sal用$补齐20位
eg：SELECT name,LPAD(sal, 20, ‘$’) as “salary” FROM emp;
作用：要求显示20个字符，若sal的值不足长度，则补充若干个’$’，以达到20个字符
eg：SELECTRPAD(‘aaaaAAAAA’) FROM DUAL;
得到结果为aaaaA
7.SUBSTR
SUBSTR(char, [m[, n]])
— 用于获取字符串的子串，返回char中从m位开始取n个字符
如果m=0，则从首字符开始，如果m取负数，则从尾部开始
如果没有设置n，或者n的长度超过了char的长度，则取到字符串末尾为止
字符串的首位计数从1开始
8.INSTR
INSTR(char1, char2[, n [, m]]);
返回子串char2在原字符串char1中的位置
参数：
— 从n的位置开始搜索，没有指定n，从第一个字符开始搜索
— m用于指定子串的第m次出现次数，如果不指定取值1
— 如果在char1中没有找到子串char2，返回0
二. Oracle数值操作
数值类型
1.NUMBER(P)表示整数
完整语法：NUMBER(precision, scale)
— 如果没有设置scale，则默认取值0，即NUMBER(p)表示整数
— P表示数字的总位数，取值为1-38
用来在表中存放如编码，年龄，次数等用整数记录的数据
2.NUMBER(P, S)表示浮点数
NUMBER(precision, scale)
— precision：NUMBER可以存储的最大数字长度（不包括左右两边的0）
— scale：在小数点右边的最大数字长度（包括左侧0）
指定了s但是没有指定p，则p默认为38
经常用于表中存放金额，成绩等有小数的数据。
NUMBER的变种数据类型：内部实现是NUMBER，可以将其理解为NUMBER的别名，目的是多种数据库及编程语言兼容
— NUMERIC(p, s)：完全映射至NUMBER(p, s)
— DECIMAL(p, s)或DEC(p, s)：完全映射至NUMBER(p, s)类型
— INTEGER或INT：完全映射至NUMBER(38)类型
— SMALLINT：完全映射至NUMBER(38)类型
— FLOAT(b)：映射至NUMBER类型
— DOUBLE PRECISION：映射至NUMBER类型
— REAL：映射至NUMBER类型
数值函数
1.ROUND
ROUND(n[, m])：用于四舍五入
— 参数中的n可以是任何数字，指要被处理的数字
— m必须是整数
— m取正数则四舍五入到小数点后第m位
— m取0值则四舍五入到整数位
— m取负数，则四舍五入到小数点前m位
— m缺省，默认是0
eg：SELECTROUND(45.678, -1) FROM DUAL; —50
2.TRUNC
TRUNC(n[, m])：用于截取
— n和m的定义和ROUND(n[, m])相同，不同的是功能上按照截取的方式处理数字n
eg：SELECTTRUNC(45.678, -1) FROM DUAL; —40
3.MOD
MOD(m, n)：返回m除以n后的余数
—n为0则直接返回m
4.CEIL和FLOOR
CEIL(n)，FLOOR(n)这两个函数，一个是取大于或等于n的最小整数值，另一个是取小于或等于n的最大整数值
eg：SELECT CEIL(45.678) FROM DUAL; —46
SELECT FLOOR(45.678) FROMDUAL; —45
三. Oracle日期操作
日期类型
1.DATE
ORACLE中最常用的日期类型，用来保存日期和时间
DATE表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日
DATE类型在数据库中的存储固定为7个字节，格式为：
— 第一个字节：世纪+100
— 第二个字节：年
— 第三个字节：月
— 第四个字节：天
— 第五个字节：小时+1
— 第六个字节：分+1
— 第七个字节：秒+1
2.TIMESTAMP
ORACLE常用的日期类型
与DATE的区别是不仅可以保存日期和时间，还能保存小数秒，最高精度可以到ns（纳秒）
数据库内部用7或者11字节存储，精度为0，用7字节存储，与DATE功能相同，精度大于0则用11字节存储
格式为：
— 第1字节-第7字节：和DATE相同
— 第8-11字节：纳秒，采用4个字节存储，内部运算类型为整型
日期关键字
1.SYSDATE
其本质是一个ORACLE的内部函数，返回当前的系统时间，精确到秒
默认显示格式是DD-MON-RR
2.SYSTIMESTAMP
内部函数，返回当前系统日期和时间，精确到毫秒
日期转换函数
1.TO_DATE
TO_DATE(char[, fmt[, nlsparams]])：将字符串按照定制格式转换为日期类型
— char：要转换的字符串
— fmt：格式
— nlsparams：指定日期语言
— 常用的日期格式见表
YY | 2位数字的年份 |
YYYY | 4位数字的年份 |
MM | 2位数字的月份 |
MON | 简拼的月份 |
MONTH | 全拼的月份 |
DD | 2位数字的天 |
DY | 周几的缩写 |
DAY | 周几的全拼 |
HH24 | 24小时制的小时 |
HH12 | 12小时制的小时 |
MI | 显示分钟 |
SS | 显示秒 |
2.TO_CHAR
将其它类型的书籍转换为字符类型
TO_CHAR(date[, fmt[, nlsparams]])：将日期类型数据date按照fmt的格式输出字符串。nlsparams用于指定日期语言
需要注意的是：在日期格式字符串中，出现的非关键字符或符号的其它字符时，需要使用双引号
eg：SELECT TO_CHAR(SYSDATE,“yyyy”year”mm”month”dd”day”hh:mi:ss”) FROM DUAL;
两个日期可以进行减法操作，差为相差的天数
日期常用函数
1.LAST_DAY
LAST_DAY(date)：返回日期date所在月的最后一天
在按照自然月计算某些业务逻辑，或者安排月末周期性活动时很有用处
eg：SELECT LAST_DAY(SYSDATE) FROM DUAL; —30-9月－17
SELECT LAST_DAY(’20-2月-09’) FROM DUAL; —28-2月－09
2.ADD_MONTHS
ADD_MONTHS(date, i)：返回日期date加上i个月后的日期值
— 参数i可以是任何数字，大部分时候取正值整数
— 如果i是小数，将会被截取整数后再参与运算
— 如果i负数，则获得的是减去i个月后的日期值
eg：计算职员入职20周年纪念日
SELECT name,ADD_MONTHS(hiredate, 20 * 12) as ‘20周年’ FROM emp;
3.MONTHS_BETWEEN
MONTHS_BETWEEN(date1, date2)：计算date1和date2两个日期值之间间隔了多少个月
实际运算是date1-date2，如果date2时间比date1晚，会得到负值
除非两个日期间隔是整数月，否则会得到带小数位的结果。
— 此时可以使用FLOOR得到整月
eg：SELECT name, FLOOR(MONTHS_BETWEEN(SYSDATE, hiredate))FROM emp;
4.NEXT_DAY
NEXT_DAY(date, char)：返回date日期数据的下一个周几，周几是由参数char来决定的
在中文环境下，直接使用“星期三”这种形式，英文环境下，需要使用“WEDNESDAY”这种英文的周几。位避免麻烦，可以直接使用1-7表示周日－周六
NEXT_DAY不是明天
SELECT NEXT_DAY(SYSDATE, 3) FROM DUAL; —离现在最近的周2（不包含今天）
5.LEAST, GREATEST
GREAGEST(expr1[, expr2[, expr3]]...)
LEAST(expr1[, expr2[,expr3]]...)
也被称作比较函数，可以有多个参数值，返回结果是参数列表中最大或最小的值
参数类型必须一致
在比较之前，在参数列表中第二个以后的参数会被隐含的转换为第一个参数的数据类型，所以如果可以转换，则继续比较，如果不能转换将会报错。
6.EXTRACT
EXTRACT(date FROM datetime)：从参数date time中提取参数date指定的数据，比如提取年，月，日
eg：SELECT EXTRACT(HOUR FROM TIMESTAMP‘2008-01-01 10:10:10’) FROM DUAL;
四. 空值操作
NULL的含义
数据库里的重要概念：NULL，即空值
有时表中的某些字段值，数据未知或暂时不存在，取值NULL
任何数据类型均可取值NULL
NULL的操作
1.插入NULL值和更新成NULL时只有在非空约束时才可操作
查询条件：WHERE name IS NULL/ WHERE name IS NOTNULL
任何数据和NULL相加都是NULL
2.非空约束
非空（NOT NULL）约束用于确保字段值不为空
默认情况下，任何列都允许有空值
当某个字段被设置了非空约束条件，这个字段中必须存在有效值。
空值函数
1.NVL
NVL(expr1, expr2)：将NULL转变为非NULL值
— 如果expr1为NULL，则取值expr2，expr2是实际值
— expr1和expr2可以是任何数据类型，但两个参数的数据类型必须是一致的
2.NVL2
NVL2(expr1, expr2, expr3)：和NULL函数功能类似，都是将NULL转变为实际值
NVL2用来判断expr1是否为NULL，如果不是NULL，返回expr2，如果是NULL，返回expr3
五. SQL基础查询
基本查询语句
1. FROM子句
SELECT * FROM TABLE;
2. 使用别名
3. WHERE子句
WHERE子句中使用比较操作符限制查询结果
4. SELECT子句
查询条件
1. 使用>,<,>=,<=,!=,<>,=
数据库中标准的不等于：<>
2. 使用AND，OR关键字
SELECT * FROM table WHERE sal >1000 AND job = ‘clerk’;
SELECT * FROM table WHERE sal >1000 OR job = ‘clerk’;
同时使用AND和OR时，AND的优先级高于OR
3. 使用LIKE条件(模糊查询)
LIKE需要借助两个通配符：
— %：表示0到多个字符
— _：标识单个字符
SELECT name, job FROM amp WHERE name LIKE‘_A%’;—单词的第二个字符是A后面未知
4. 使用IN和NOT IN
比较操作符IN(list)用来取出符合列表范围中的数据
List表示值列表，当列或表达式匹配于列表中的任何一个值时，条件为TRUE，该条记录则被显示出来
IN可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表
NOT IN(list)取出不符合此列表中的数据记录
5. BETWEEN…AND...
BETWEEN…(小的值)AND...(大的值)：操作符用来查询符合某个值域范围条件的数据
最常见的是使用在数字类型的数据范围上，但对字符类型和日期类型数据也同样适用
SELECT name, sal FROM amp WHERE sal BETWEEN1000 AND 2000;
6. 使用IS NULL和IS NOT NULL
NULL比较时不能使用”=“，必须使用IS
7. 使用ANY和ALL条件
ALL和ANY不能单独使用，需要配合单行比较操作符>,<=,<,<=一起使用
> ANY：大于最小
< ANY：小于最大
> ALL：大于最大
< ALL：小于最小
eg：SELECT name, job sal FROM amp WHERE sal > ANY(2000,3400,4000);
与IN的相同之处：给定一组数据进行比较
区别：IN是与给定的数据进行等值或不等值比较
ANY和ALL是与给定数据进行范围比较
8. 查询条件中使用表达式和函数
查询条件中可以使用算术表达式：+, -, * ,/。优先级符合四则运算默认优先级，可使用括号改变优先级
9. 使用DISTINCT过滤重复
对多列去重，可以达到的效果是，这几列的组合是不重复的。
SELECT DISTINCT deptno from table ;—去掉重复值
排序
1. 使用ORDER BY子句
对数据按一定规则进行排序操作，使用ORDER BY子句
必须出现在SELECT中的最后一个子句
2. ASC和DESC
ASC：升序
DESC：降序
3. 多列排序
使用多列进行排序时，左边的列排序优先级高于右面的列
eg：SELECTname,sal,deptno FROM emp ORDER BY sal,deptno DESC;
首先按照sal的升序排列，当sal的值相同时，按照deptno的降序排列。若sal的值全表没有重复值，则第二列的排序会被忽略。
聚合函数
1. 聚合函数
多行数据参与运算返回一行结果，也称作分组函数，多行函数，集合函数。
2. MAX和MIN
用来取得列或表达式的最大，最小值
可用于统计任何数据类型，包括数字，字符和日期
SELECT MAX(sal) max_sal, MIN(sal) min_salFROM emp;
MAX和MIN是不能出现在WHERE语句中的。
3. AVG和SUM
聚合函数忽略空值。所以得到的平均值会少数据。
eg：SELECT AVG(NVL(comm, 0)) FROM amp;—若为空值则使其值为0
4. COUNT
用于统计记录条数
SELECT COUNT(comm) FROM amp;—忽略空值，若字段comm为kong则不记录在内
5. 聚合函数对空值的处理
分组
1. GROUP BY子句
SELECT MAX(sal), MIN(sal), SUM(sal), AVG(sal) FROM emp GROUP BYdeptno; —根据部分分组，得到每组的最高薪资，最低薪资，平均薪资，总薪资
根据值相同的记录作为一组，进行统计；
一般对部分数据重复的值进行分组，没有重复的数据分组没有意义；
只要在SELECT中使用了分组函数，那么，SELECT中其它非分组函数的列若出现，则必须同时出现在GROUP BY子句中，反过来没有限制。
若GROUP BY中出现了多列，那么就按照这几列组合值相同的记录看作一组。
2. 分组查询
3. HAVING字句
WHERE是用于整张表逐行过滤用的；
HAVING用于在进行分组查询后，二次过滤数据的；
HAVING中可以使用分组函数的结果进行过滤；
HAVING不能独立存在，必须跟在GROUP BY之后。
eg：筛选出部门平均工资高于1800的部门：SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 1800
SQL顺序：SELECT—> FROM —> WHERE —> GROUP BY —> HAVING —> ORDER BY
查询语句执行顺序
1. 查询语句执行顺序（可以改善代码执行效率）
查询语句的执行顺序依下列子句次序：
1.1 from子句：执行顺序为从后往前，从右到左
数量较少的表尽量放在后面
1.2 where子句：执行顺序为自下而上，从右到左
将能过滤掉最大数量记录的条件写在where子句的最右
1.3 group by 执行顺序从左往右分组
最好在GROUP BY前使用WHERE将不需要的记录在GROUPBY之前过滤掉
1.4 having子句：消耗资源
尽量避免使用，HAVING会在检索出所有记录之后才对结果进行过滤，需要排序等操作
1.5 select子句：少用*号，尽量取字段名称
ORACLE在解析的过程中，通过查询数据字典将*号依次转换成所有的列名，消耗时间
1.6 order by子句：执行顺序为从左到右排序，消耗资源
六. SQL关联查询
关联基础
1. 关联的概念
查询两个或两个以上数据表或视图的查询叫做连接查询
连接查询通常建立在存在相互关心的父子表之间
2. 笛卡尔积
笛卡尔积指做关联操作的每个表的每一行都和其它表的每一行做组合，假设两个表的记录条数分别是X和Y，笛卡尔积将返回X*Y条记录。
两张表不加连接条件关联时会产生笛卡尔积。
3. 等值连接
SELECT table1.column, table2.column FROM table1, table2where table1.column = table2.column;（column分别为table1和table2的主键和外键）
NATURAL JOIN：自动寻找两张表列名相同的字段（且这两个字段互为主外键关系时）做等值连接。注意：两张表中应当只有一列名字相同才可以使用自然连接
关联查询
1. 内连接
2. 外连接
应用场景：A表中某个字段的值在B表中相应该字段的值找不到时使用
SELECT table1.column, table2.column FROM table1 LEFT | RIGHT |FULL | OUTER JOIN table2 ON table1.column = table2.column2;
LEFT：左边表为准，右边表不足补齐；
RIGHT：右边表为准，左边表不足补齐
3. 全外连接
左边表不足补齐，右边表不足补齐
4. 自连接
最常用于员工上下级关系
将一张表当成两张表
eg：SELECTe.name||’的领导是’||m.nameFROM amp e, emp m WHERE e.mgr = m.empno;
七. SQL高级查询
子查询
当前查询需要建立在另一个查询的结果基础之上，这里就要利用到子查询
1. 子查询在WHERE子句中
在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另一个查询的结果
为了给查询提供数据而首先执行的查询语句叫做子查询
子查询是嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中
子查询嵌入的语句称作主查询或父查询
主查询可以是SELECT语句，也可以是其它类型的语句比如：DML或DDL
根据返回结果不同，子查询可以分为单行子查询，多行子查询及多列子查询
单行单列和多行单列子查询通常用在WHERE子句中作为条件
多行多列子查询通常用在FROM子句中
>, <, >=, <=, =, <>这些比较都只能使用单行单列子查询
比较多行单例子查询时可以使用ANY或ALL或IN
eg：SELECT name, sal FROM amp WHERE sal > ANY(SELECT sal FROM ampWHERE job = ’SALESMAN’);--大于其中之一
SELECT name, salFROM amp WHERE sal > ALL(SELECT sal FROM amp WHERE job = ’SALESMAN’);--大于最大
SELECT name, salFROM amp WHERE sal > IN(SELECT sal FROM amp WHERE job = ’SALESMAN’);--等于其中之一
在子查询中需要引用到主查询的字段数据，使用EXISTS关键字
EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE
eg：查询比本部门平均工资高的那些员工
SELECTe.1deptno, e1.name, e1.sal FROM emp e1
WHERE e1.sal > (SELECT AVG(sal) FROM emp e2WHERE e1.deptno = e2.deptno);
2. 子查询在HAVING子句中
查询列出最低薪水高于部门30的最低薪水的部门信息：
SELECT deptno, MIN(sal) min_salFROM emp
GROUP BY deptno
HAVING MIN(sal) > (SELECTMIN(sal) FROM emp WHERE deptno = 30);
3. 子查询在FROM部分
FROM中出现子查询，则需要将子查询结果当做一张表看待，再从中查询想要的结果。此时需注意：子查询的SELECT语句中，出现了非字段名的字段，通常是表达式或者函数，那么一定要给他们加上别名。
eg：查询出薪水比本部门薪水高的员工信息：
SELECT e.deptno, e.name, e.sal FROMemp e, (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x
WHERE e.deptno = x.deptno and e.sal> x.avg_sal ORDER BY e.deptno;
4. 子查询在SELECT部分
外连接的另一种写法，不常用
eg：查询姓名，薪资，部门名
SELECT name, sal, (SELECTname FROM deptno d WHERE d.deptno = e.deptno) FROM amp e;
分页查询
1. ROWNUM
被称作伪列，用于返回标识行数据顺序的数字；
只能从1计数，不能从结果集中直接截取
ROWNUM是当oracle进行select进行select当查询表数据时，确定一条数据需要时，才会对其进行编号（伪劣的值），这就导致，没有数据被查出前，rownum永远没有值。所以rownum不能在第一次查询时作为where条件。
rownum：有数据了才有值
where里出现：有值才有数据
eg：SELECT ROWUM,empno,name,sal FROM emp WHERE rownum > 3;--查询不到结果
2. 使用子查询进行分页
分页三步：
a. 排序
b. 编号
c. 取范围
eg：
a. SELECTempno, name, sal FROM emp ORDER BY empno;
b. SELECT ROWNUM rw, e.* FROM() e;
c. SELECT * FROM () WHERE rwBETWEEN 1 AND 3;
SELECT * FROM (SELECTROWNUM rw, e.* FROM (SELECT empno, name, sal FROM emp ORDER BY empno) e) WHERE rw BETWEEN 1 AND 3;
分页算法：page页数，pagesize一页的条数
起始位置：(page-1)*pagesize+1
结束位置：page*pagesize
MYSQL：limit(1,3)
3. 分页与ORDER BY
DECODE函数
1. DECODE函数基本语法
DECODE(expr, search1, result1[, search2, result2…][,default])
DECODE用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果
可以有多组search和result对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值
default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL
2. DECODE函数在分组查询中的应用
eg：查询职员表，根据职员的职位计算奖金金额，当职位为MANAGER，ANALYST，SALESMAN时，奖励金额分别是薪水的1.2倍，1.1倍，1.05倍，如果不是这三个职位，则奖励金额取薪水值
SELECT name, job, sal, DECODE(job, ‘MANAGER’, sal * 1.2,
‘ANALYST’,sal * 1.1,
’SALESMAN’,sal * 1.05,
sal)bonus
FROM emp;
和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作：
SELECT name, job, sal, CASE job WHEN ‘MANAGER’ THEN sal * 1.2
WHEN ‘ANALYST’ THEN sal * 1.1
WHEN ’SALESMAN’ THEN sal * 1.05
ELSE sal END bonus
FROM emp;
DECODE用在ORDER BY时，可以自定义排序规则：
SELECT * FROM dept ORDER BY DECODE(name, ‘OPERATIONS’, 0,‘ACCOUNTING’, 1, ’SALES’, 2, 3);
排序函数
1. ROW_NUMBER
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
表示根据col1分组，在分组内部根据col2排序
此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一
Rownum是伪列，ROW_NUMBER功能更强，可以直接从结果集中取出子集
eg：根据部门分组，部门内薪资倒序，部门内生成连续且唯一编号
SELECT name, sal, ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;
2. RANK
生成组内不连续且不唯一的编号，排序的列若值相同，会得到相同的编号
不连续排序，如果有并列第二，下一个排序将是四
eg：根据部门分组，部门内薪资倒序，部门内生成不连续且不唯一编号
SELECT name,sal,deptno RANK() OVER(PARTITIONBY deptno ORDER BY sal DESC) rank FROM emp;
3. DENSE_RANK
生成组内连续但不唯一的编号，排序的列若值相同，会得到相同的编号
连续排序，如果有并列第二，下一个排序将是三，这一点和RANK的不同，RANK是跳跃排序
eg：根据部门分组，部门内薪资倒序，部门内生成连续但不唯一编号
SELECT name,sal,deptno DENSE_RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;
集合操作
1. UNION，UNION ALL
为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并，交，差
集合操作符包括UNION，UNION ALL，INTERSECT和MINUS
多条作集合的SELECT语句的列的个数和数据类型必须匹配
ORDER BY子句只能放在最后的一个查询语句中
集合操作的语法：
SELECT statement1 [UNION | UNION ALL|INTERSECT | MINUS] SELECT statement2;
UNION:statement1和statement2重复的结果合并，重复的结果出现一次
UNION:statement1和statement2重复的结果不合并，重复的结果有几个就出现几次
2. INTERSECT
获得两个结果的交集，只有同时存在于两个结果集中的数据才被显示输出
使用INTERSECT操作符后的结果集会以第一列的数据作升序排序
3. MINUS
获取两个结果集的差集
只有在第一个结果集中存在，在第二结果集中不存在的数据才会被显示出来，也就是结果集一减去结果集二的结果
高级分组函数
1. ROLLUP
eg：计算出每月总和，每年总和
SELECT year_id, month_id, SUM(sales_value) AS sales_value FROMsales_tab GROUP BY ROLLUP(year_id, month_id) ORDER BY year_id, month_id;
2. CUBE
GROUP BY CUBE(a,b,c)
对CUBE的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合
对于n个参数的CUBE，组合形式有2^n次，如果是a，b，c则是2^3种组合
GROUP BY CUBE(a, b, c)，首先是对(a,b,c)进行GROUP BY，然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，最后对全表进行GROUP BY操作，一共是2^3=8次分组
eg：SELECTa,b,c,SUM(D) FROM test GROUP BY CUBE(a,b,c)等价于：(等价于，但是内部运行机制不同于UNION ALL，其效率远高于UNION ALL)
SELECT a,b,c,SUM(D) FROM testGROUP BY a,b,c
UNION ALL
SELECT a,b,NULL,SUM(D) FROM testGROUP BY a,b
UNION ALL
SELECT a,NULL,c,SUM(D) FROM testGROUP BY a,c
UNION ALL
SELECT a,NULL,NULL,SUM(D) FROMtest GROUP BY a
UNION ALL
SELECT NULL,b,c,SUM(D) FROM testGROUP BY b,c
UNION ALL
SELECT NULL,b,NULL,SUM(D) FROMtest GROUP BY b
UNION ALL
SELECT NULL,NULL,c,SUM(D) FROMtest GROUP BY c
UNION ALL
SELECT NULL,NULL,NULL,SUM(D) FROMtest ;
3. GROUPING SETS
GROUPING SETS运算符可以生成与使用单个GROUP BY，ROLLUP或CUBE运算符所生成的结果集相同的结果集
如果不需要获得由完备的ROLLUP或CUBE运算符生成的全部分组，则可以使用GROUPINGSETS仅指定所需的分组
GROUPING SETS列表可以包含重复的分组，即允许参数重复
eg：GROUP BYGROUPING SETS(a,a)，则对a进行2次GROUP BY
eg：计算出每年每月，每年每月每日的统计
SELECT year_id, month_id,SUM(sales_value) AS sales_value FROM sales_tab GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id)) ORDER BY year_id, month_id;
八. 视图，序列，索引
视图
1.什么是视图
视图（VIEW）也被称作虚表，是一组数据的逻辑表示
视图对应于一条SELECT语句，结果集被赋予一个名字，即视图名字
视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基于数据发生变化，视图数据页随之变化
CREATE [OR REPLACE] VIEW view_name[alias[,alias…]] ASsubquery;
视图创建后，可以像操作表一样操作视图，主要是查询
SUBQUERY是SELECT查询语句，对应的表被称为基表
— SELECT语句是基于单表建立的，且不包含任何函数运算，表达式或分组函数，叫简单视图，这时视图是基表的子集
— SELECT语句基于单表，但包含了单行函数，表达式，分组函数或GROUP BY子句，叫做复杂视图
— SELECT语句基于多个表，叫做连接视图
2.视图的作用
简化复杂查询：若经常需要执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询次视图即可
限制数据访问：当访问视图时只能访问到对应的SELECT语句中涉及的列，对基表中其它列起到安全和保密的作用
3.授权创建简单视图（单表）
创建视图的语句是：CREATE VIEWER
用户必须有CREATE VIEWER系统权限，才能创建视图
管理员通过DCL语句授予用户创建视图的权限：GRANT CREATEVIEWER TO user;
eg： CREATE VIEWER v_emp_10 AS SELECTempno,name,sal,deptno FROM emp WHERE deptno = 10;
4.查询视图
desc v_emp_10;
5.对视图进行INSERT操作
视图本身并不包含数据，只是基表数据的逻辑映射
当对视图执行DML操作时，实际上是对基表的DML操作
对视图执行DML操作的基本原则：
— 简单视图能够执行DML操作，下列情况除外：在基表中定义了非空列，但简单视图对应的SELECT语句并没有包含这个非空列，导致这个非空列队视图不可见，这时无法对视图执行INSERT操作
— 如果视图定义中包含了函数，表达式，分组语句，DISTINCT关键字或ROWNUM伪列，不允许执行DML操作
— DML操作不能违反基表的约束条件
— 简单视图可以通过DML操作影响到基表数据
— 视图进行DELETE操作时只能删除基表中视图中看得到的数据，不能基表中存在而视图中看不到的数据
6.创建具有CHECK OPTION约束的视图
CREATE [OR REPLACE] VIEW view_name[(alias[, alias...])] ASsubquery [WITH CHECK OPTION];
WITH CHECK OPTION短语表示，通过视图所做的修改，必须在视图的可见范围
— 假设INSERT，新增的记录在视图仍可查看
— 假设UPDATE，修改后的结果必须能通过视图查看到
7.创建具有READ ONLY约束的视图
CREATE [OR REPLACE] VIEW view_name[(alias[, alias...])] ASsubquery [WITH READ ONLY];
如果没有在视图上执行DML操作的必要，在建立视图时声明为只读来避免这种情况，保证视图对应的基表数据不会被非法修改
8.通过查询user_viewers获取相关信息
和视图相关的数据字典：
— USER_OBJECTS
— USER_VIEWS
— USER_UPDATE_COLUMNS (查看哪些列允许增删改)
eg：在数据字典USER_OBJECTS中查询所有视图名称
SELECTobject_name FROM user_objects WHERE object_type = ‘VIEW’;
9.创建复杂视图（多表关联）
复杂视图：在子查询中包含了表达式，单行函数或分组函数的视图
必须为子查询中的表达式或函数定义别名
复杂视图不允许DML操作（即INSERT，UPDATE，DELETE）
10.删除视图
当不再需要视图的定义，可以使用DROP VIEW语句删除视图
eg：DROP VIEWview_name;
视图虽然时存放在数据字典中的独立对象，但视图仅仅是基于表的一个查询定义，所以对视图的删除不会导致基表数据的丢失，不影响基表数据
SELECTsys_guid() FROM DUAL;//可以得到32位的uuid
JAVA中：
String uuid = UUID.randomUUID().toString();
序列
1.什么是序列
序列（SEQUENCE）是一种用来生成唯一数字值的数据库对象
序列的值由Oracle程序按递增或递减顺序自动生成，通常用来自动生成表的主键值，是一种高效率获得唯一键值的途径
序列是独立的数据库对象，和表是对立的对象，序列并不依附于表
通常一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键
2.创建序列
CREATE SEQUENCE [schema.]sequence_name
[START WITH i] [INCREMENT BY j ]
[MAXVALUE m | NOMAXVALUE ]
[MINVALUE n | NOMINVALUE ]
[CYCLE | NOCYCLE ][ CACHE p | NOCACHE]
sequence_name是序列名，将创建在schema方案下，schema是用户名，给自己用户创建时可不写
序列的第一个序列值是i，步进（即步长）是j
如果j是整数，表示递增，如果是负数，表示递减
序列可生成的最大值是m，最小值n
如果没有设置任何可选参数，序列的第一个值是1，步进是1
CYCLE表示在递增至最大值或递减至最小值之后是否重用序列。若是递减并有最大值，从最大值开始。若是递增并有最小值，从最小值开始。若没有从START WITH指定的值开始。默认是NOCYCLE
CACHE用来指定先预取p个数据在缓存中，以提高序列值的生成效率，默认是20
3.使用序列
eg：序列起始数据：100，步进10，则序列号分别是：100，110，120，130...
CREATE SEQUENCE emp_seq START WITH 100INCREMENT BY 10;
序列中有2个伪列：
— NEXTVAL：获取序列的下个值
— CURRENT：获取序列的当前值
当序列创建以后，必须先执行一次NEXTVAL，之后才能使用CURRENT
获取序列的第一个值，并使用序列值为EMP表插入新的记录
SELECT emp_seq.NEXTVAL FROM DUAL;//第一次执行：100；第二次执行：110
INSERT INTO emp(epmno, ename) VALUES(emp_seq.NEXTVAL,‘DONNA’);
4.删除序列
DROP SEQUENCE sequence_name;
索引
1.索引的原理
索引是一种允许值接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）中
索引记录中存有索引关键字和指向表中数据的指针（地址）
对索引进行的I/O操作比对表进行操作要少很多
索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引
索引是一种提高查询效率的机制
2.创建索引
CREATE [UNIQUE] INDEX index_name ON table(column[,column…]);
— index_name表示索引名称
— table表示表明
— column表示列名，可以建立单列索引或复合索引
— UNIQUE表示唯一索引
— 索引指定的列是在查询时WHERE子句中经常出现的列
复合索引也叫多列索引，是基于多个列的索引
如果经常在ORDER BY子句中使用job，sal作为排序依据，则可以建立复合索引：
CREATE INDEX idx_emp_job_sal ONemp(job, sal);
当做下面查询时会自动应用索引[idx_emp_job_sal]
SELECT empno, name, sal, job FROM ampORDER BY job,sal;
3.创建基于函数的索引
可以在列上建立一个基于UPPER函数的索引：
CREATE INDEX emp_name_upper_idx ONemp(UPPER(name));
当做下面查询时会自动应用索引[emp_name_upper_idx]
SELECT empno FROM emp WHEREUPPER(ename) = ‘KING'
4.修改和删除索引
如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率：ALTER INDEX index_name REBUILD;
当一个表上有不合理的索引，会导致操作性能下降，删除索引：DROP INDEXindex_name;
5.合理使用索引提升查询效率
为经常出现在WHERE子句中的列创建索引
为经常出现在ORDER BY，DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
为经常作为表的连接条件的列上创建索引
不要在经常做DML操作的表上建立索引
不要在小表上建立索引
限制表上的索引数目，索引并不是越多越好
删除很少被使用的，不合理的索引
九. 约束
约束概述
1.约束的作用
约束（CONSTRAINT）的全称是约束条件，也称为完整性约束条件
约束条件可以保证表中数据的完整性，保证数据间的商业逻辑
2.约束的类型
— 非空约束（Not Null），简称NN
— 唯一性约束（Unique），简称UK
— 主键约束（Primary Key），简称PK
— 外键约束（Foreign Key），简称FK
— 检查约束（Check），简称CK
非空约束
1.建表时添加非空约束
列级约束：在创建表指定列的同时声明的约束
CREATE TABLE employees(id NUMBER(6),
name VARCHAR2(30) NOT NULL,//列级约束1
salary NUMBER(7,2),
hiredate DATE CONSTRAINT employees_hiredate_nn NOTNULL//列级约束2
);
NOT NULL只能是列级约束
2.修改表时添加非空约束
ALTER TABLE employees MODIFY (id NUMBER(6) NOT NULL);
3.取消非空约束
ALTER TABLE employees MODIFY (id NUMBER(6) null);
唯一性约束
1.什么是唯一性约束
唯一性（Unique）约束条件用于保证字段或者字段的组合不出现重复值
当给表的某个列定义了唯一约束条件，该列的值不允许重复，但允许是NULL
2.添加唯一性约束
CREATE TABLE employees(id NUMBER(6) UNIQUE,//列级约束
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE,
CONSTRAINT employees_email_uk UNIQUE(email)//表级约束
);
列级约束是在建表声明某一列的同时指定约束条件，表级约束是所有列都声明完毕后单独加约束，括号里指定约束对象是哪一列。
唯一性约束即可以是列级约束也可以是表级约束
在建表之后增加唯一性约束条件：ALTER TABLEemployees ADD CONSTRAINT employees_name_uk UNIQUE(name);//新增的唯一性约束条件只会对该增加之后的数据起作用
主键约束
1.主键的意义
主键（Primary Key）约束条件从功能上看相当于非空且唯一的组合
主键字段可以是单字段或多字段组合
主键可用来确定表中唯一一行数据
一个表中只允许建立一个主键，而其它约束条件则没有明确的个数限制
2.主键选取的原则
主键应是对系统无意义的数据
永远不要更新主键，让主键除了唯一标识一行之外，再无其它用处
主键不应包含动态变化的数据，如时间戳
主键应自动生成，不要人为干预，以免它带有了唯一标识以外的意义
主键尽量建立在单列上
3.添加主键约束
在建表时添加主键约束条件：
CREATE TABLE employees (
idNUMBER(6) PRIMARY KEY,
nameVARCHAR2(30),
emailVARCHAR2(50),
salaryNUMBER(7,2),
hiredateDATE
);
建表后创建主键约束条件，并自定义约束条件名称：
CREATE TABLE employees (
idNUMBER(6),
nameVARCHAR2(30),
emailVARCHAR2(50),
salaryNUMBER(7,2),
hiredateDATE
);
ALTER TABLE employees ADD CONSTRAINTemployees ADD CONSTRAINT employees_id_pk PRIMARY KEY (id);
外键约束
1.外键约束的意义
外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系
dept表：主表或父表
emp表：从表或子表
deptno（PK） | name | loc |
10 | 研发部 | 北京 |
20 | 销售部 | 上海 |
empno（PK） | name | deptno（FK） |
1001 | 刘心 | 10 |
1002 | 李苏海 | 10 |
1.添加外键约束
先建表，在建表后建立外键约束条件
CREATE TABLE employees (
idNUMBER(6),
nameVARCHAR2(30),
emailVARCHAR2(50),
salaryNUMBER(7,2),
deptno NUMBER(4)
);
ALTER TABLE employees ADD CONSTRAINT employees_deptno_fkFOREIGN KEY (deptno) REFERENCES dept(deptno);
2.外键约束对一致性的维护
外键约束条件包含两个方面的数据约束：
— 从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL
— 当主表参照列的值被从表参照时，主表的该行记录不允许被删除
3.外键约束对性能的降低
若在一个频繁DML操作的表上建立外键，每次DML操作都将导致数据库自动对外键所关联的对应表做检查，产生开销，如果已在程序中控制逻辑，这些判断将增加额外负担，可以省去
外键确定了主从表的先后生成关系，有时会影响业务逻辑
4.关联不一定需要外键约束
保证数据完整行可由程序或触发器控制
简化开发，维护数据时不用考虑外键约束
大量数据DML操作时不需要考虑外键耗费时间
检查约束
1.什么是检查约束
检查（Check）约束条件用来强制在字段上的每个值都要满足Check中定义的条件
当定义了Check约束的列新增或修改数据时，数据必须符合Check约束中定义的条件
2.添加检查约束
eg：员工薪水必须大于2000
ALTER TABLE employees4 ADD CONSTRAINTemployees4_salary_check CHECK (salary > 2000);
3. 删除一个约束
ALTER TABLE employees DROP CONSTRAINTemployees_salary_check;
查看约束的数据字典
SELECT constraint_name,constraint_type FROMuser_constraints WHERE table_name = ‘EMPLOYEES’;
— constraint_type：约束类型
— constraint_name：约束名
SELECT * FROMuser_constraints WHERE table_name = ‘EMPLOYEES’;
2768
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
