DDL:数据库对象:表、视图、索引、序列等的增删改
DML:数据库数据(记录)的增删改,通常伴随事务
TCL:用于维护数据一致性,有提交、回滚、保存点.回滚是回到到上一次事务开始或保存点处.
DQL:数据库数据的查询
DCL:用于执行权限授予、收回、创建用户等.
--------------------------------------------------------------------------------
Oracle数据类型:
NUMBER:表示数字类型,定义格式:NUMBER(P[,S]) P表示数字总位数,S表示小数部分位数(非必须).
CHAR:表示固定长度字符串类型,定义格式:CHAR(N) N表示占用的字节数,N最大值为2000字节.
VARCHAR2:表示变长的字符串类型,定义格式:VARCHAR2(N) N表示最多占用字节数,N最大值为4000字节.
--------------------------------------------------------------------------------
注意:
CHAR可不指定长度(默认为1),VARCHAR2必须指定
在数据库中用'字符串'表示一个字符串类型值,且区分大小写,而SQL语句是不区分大小写的,数据
库中字段默认值:无论该字段什么类型,默认值都为NULL,但可在建表时用DEFAULT关键字指定默认值
--------------------------------------------------------------------------------
DATE:表示日期格式数据类型,定义格式:DD-MM-RR 长度为7个字节
第1字节：世纪+100
第2字节：年
第3字节：月
第4字节：天
第5字节：小时+1
第6字节：分+1
第7字节：秒+1
当前系统年份的后两位范围
0-49 50-99
指定年份后两位范围(RR)
0-49 本世纪 下世纪
50-99 上世纪 本世纪
取得的具体年份值:
分别将当前系统年份的后两位与指定年份的后两位(RR的值)和表中范围对照,以取得的具体年份值.
TIMESTAMP:时间戳与DATE的区别是不仅可以保存日期和时间，还能保存小数秒，可指定为0-9位，默认6位，最高精度可以到ns(纳秒)级别。
------------------------------------------------------------------------------------------
日期关键字:
SYSDATE本质是一个Oracle的内部函数，用来返回当前的系统时间，精确到秒，默认显示格式是DD-MON-RR，只有年月日并不显示时间
SYSTIMESTAMP也是Oracle的内部日期函数，返回当前系统日期和时间，精确到毫秒。
------------------------------------------------------------------------------------------
LONG:表示VARCHAR2类型的加长版,最多存储达2GB的字节,一个表中只能有一个LONG类型,且
不能作为主键、不能建立索引、不能出现在查询条件中.
CLOB:表示定长或变长字符串,最多存储达4GB的字节,建议用其替代LONG类型.
--------------------------------------------------------------------------------
删除表:
DROP TABLE table_name
例如:
DROP TABLE employee;
创建表的同时为字段指定默认值:
CREATE TABLE table_name(
字段名 字段类型 DEFAULT 默认值,
...
);
NOT NULL约束
当表中的字段被NOT NULL修饰约束后,该字段在插入数据时必须给值,否则会抛违反非空约束的错误.
建立表时设置name字段为非空字段:
CREATE TABLE table_name(
字段名 字段类型 NOT NULL,
...
);
--------------------------------------------------------------------------------
更改表名:
RENAME old_name TO new_name
用于将old_name表的表名更改为new_name
--------------------------------------------------------------------------------
向表追加新的列:
ALTER TABLE table_name ADD(column_name type,...)
需要注意的是,追加的列只能追加到表的末尾
例如,为表myemp追加列hiredate
ALTER TABLE myemp ADD(hiredate DATE DEFAULT sysdate)
--------------------------------------------------------------------------------
删除表中的字段:
ALTER TABLE table_name DROP(column_name)
例如删除myemp表中的字段hiredate
ALTER TABLE myemp DROP (hiredate);
--------------------------------------------------------------------------------
修改表中字段:
ALTER TABLE table_name MODIFY (column_name data_type [DEFAULT expr] [,column_name data_type...])
例如:
ALTER TABLE myemp MODIFY(job VARCHAR2(40) DEFAULT 'CLERK');
修改字段,尽量避免修改字段类型.长度可以修改但尽量不要缩短.
--------------------------------------------------------------------------------
向表中插入数据:
INSERT INTO table_name[(column[, column…])] VALUES(value[, value…])
例如:
向myemp表中插入数据,需要注意的是,VALUES后面指定的值的顺序必须与前面给定的字段顺序一致.
并且给定的值的类型必须与前面字段的类型一致.
如果table_name后面的字段忽略了,那就是对表中所有字段插入值,那么VALUES后面每一列的值必须都被指定,
并且顺序与表中字段的顺序一致.
--------------------------------------------------------------------------------
插入日期时应使用TO_DATE()函数,该函数会自动转换亚欧不同地区的日期表示形式.
例如:
INSERT INTO myemp(id,name,job,birth) VALUES(1003,'donna','MANAGER',TO_DATE('2009-09-01','YYYY-MM-DD'))
--------------------------------------------------------------------------------
更新表中数据:
UPDATE table_name SET column=value[,column=value…] [WHERE condition];
需要注意的是若不写WHERE语句则是将表中所有数据都修改,一般很少出现这样的操作.
例如:
1.UPDATE myemp SET salary=8500 WHERE name='rose';
将name字段值为rose的这条记录的salary字段值改为8500
2.UPDATE myemp SET salary=8500;
将所有记录的salary字段值改为8500
--------------------------------------------------------------------------------
删除表中记录:
1.DELETE [FROM] table_name [WHERE condition];
删除表中数据时需要注意,不添加WHERE条件则是删除表中所有记录!
例如:
DELETE FROM myemp WHERE name='rose'
删除name字段值为'rose'的这条记录
2.TRUNCATE TABLE table_name;
TRUNCATE是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段，做计数重置归零重新计算的作用。
DELETE和TRUNCATE TABLE区别:TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，
且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，
并且只在事务日志中记录页的释放。TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。
如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。对用TRUNCATE TABLE删除数据的表上增加数据时，要使用UPDATE STATISTICS来维护索引信息。
如果有ROLLBACK语句，DELETE操作将被撤销，但TRUNCATE不会撤销。
字符串函数CONCAT和||连接符(相当于JAVA中的+号)
1.CONCAT是字符串连接函数，语法是：
CONCAT(char1, char2)
用于返回两个字符串连接后的结果，两个参数char1、char2是要连接的两个字符串。concat只能有两个参数，所以如果连接三个字符串时，需要两个concat函数。
比如连接emp表中的ename列和sal列，中间用“：”隔开：
SELECT CONCAT(CONCAT(ename,' : '),sal) FROM emp;
2.在连接两个以上操作符时并不是很方便。concat的等价操作是连接操作符”||”。当多个字串连接时，用||符号更直观。
下述SQL语句实现相同的效果：
SELECT ename || ' : ' || sal FROM emp;
需要注意的是:在连接时，如果任何一个参数是NULL，相当于连接了一个空格!
--------------------------------------------------------------------------------
LENGTH函数
LENGTH(字符串类型 c)用于返回参数字符串的长度。
如果字符串类型是VARCHAR2，返回字符的实际长度，如果字符串类型是CHAR，长度还包括后补的空格。
例如：
SELECT ename, LENGTH(ename) FROM emp;
将显示ename列和ename列值的字符串长度.
--------------------------------------------------------------------------------
UPPER、LOWER和INITCAP函数
这三个函数全部是英文的大小写转换函数，用来转换字符的大小写：
UPPER(字符串类型 c)用于将字符转换为大写形式
LOWER(R(字符串类型 c)用于将字符转换为小写形式
INITCAP(R(字符串类型 c)用于将字符串中每个单词的首字符大写，其它字符小写，单词之间用空格和非字母字符分隔
如果这三个函数的输入参数是NULL值，仍然返回NULL值。例如：
SELECT UPPER('hello world'),LOWER('HELLO WORLD'),INITCAP('hello world') FROM DUAL;
------------------------------------------------------------------------------
注意:这里的DUAL表是Oracle为我们提供的一张伪表,其本不存在,只是为了满足语法要求
------------------------------------------------------------------------------
将列出参数“hello world”的大写、小写和首字符大写的形式。一般用来查询数据表中不确定大小写的情况。
--------------------------------------------------------------------------------
TRIM、LTRIM、RTRIM函数
这三个TRIM函数的作用都是截去子字符串。语法形式及解释：
TRIM(c1 FROM c2) 表示从c2的前后截去所有c1
LTRIM(c1[, c2]) 表示从c1的左边（Left）截去所有c2
RTRIM(c1[, c2]) 表示从c1的右边（Right）截去所有c2
在后两个函数中，如果没有参数c2，就去除空格。例如：
SELECT TRIM('e' from 'elite') AS t1, //结果为:lit
LTRIM('elite', 'e') AS t2, //结果为:lite
RTRIM('elite', 'e') AS t3 //结果为:elit
FROM DUAL;
需要注意的是,可以连续取出所有给定字符,但是被截取的原字符中的重复字符,在给定字符中只能出现一个.
TRIM('ee' FROM 'elite')
这样是不允许的,只能写一个e.
--------------------------------------------------------------------------------
LPAD、RPAD函数
PAD意为补丁，LPAD和RPAD两个函数都叫做补位函数，LPAD表示LEFT PAD，在左边打补丁，RPAD表示RIGHT PAD，在右边打补丁。语法如下：
LPAD(char1, n, char2) 左补位函数
RPAD(char1, n, char2) 右补位函数
参数的含义： 若char1字符串本身长度不满足n位,则在字符串参数char1的左端或右端用char2补足到n位，其中参数char2可重复多次;
若字符串本身超过n位,则从首字符开始截取,以满足n位
例如:
SELECT ename,LPAD(sal,6,'$') FROM emp;
将工资显示为6位,若不够则在sal左侧补充若干个$以达到6位
800-->$$$800
1500-->$$1500
--------------------------------------------------------------------------------
SUBSTR函数
------------------------------------------------------
注意!:在数据库中字符串的首位计数从1开始。
------------------------------------------------------
SUBSTR函数表示在一个字符串中截取子串，语法是：
SUBSTR(char, [m[, n]])
用于返回char中从m位开始取n个字符的子串，参数含义如下：
如果m = 0，则从首字符开始，如果m取负数，则从尾部倒数m个位置开始
如果没有设置n，或者n的长度超过了char的长度，则取到字符串末尾为止
例如：
SELECT
SUBSTR('Doctor Who travels in TARDIS', -6, 2)
FROM DUAL;
从倒数第6个字符开始,连续获取2个字符,连续获取的字符量若超过当前字符串能截取的字符量时,就表示截取到字符串末尾.
m的位置可正可负,正数就是从前面m个位置开始取,负数就是从倒数m个位置开始取
--------------------------------------------------------------------------------
INSTR函数
用来返回在一个字符串中子串的位置。语法是：
INSTR(char1, char2[, n [, m]])
参数的含义：
返回子串char2在源字符串char1中的位置(需完全匹配)
从n的位置开始搜索，没有指定n，默认从第1个字符开始搜索
m用于指定子串的第m次出现次数，如果不指定取值1
如果在char1中没有找到子串char2 ，返回0
例如：
SELECT INSTR('Doctor Who Who Who', 'Who',1,3) FROM DUAL;//从原字符串的第1个位置开始查找子串的第3次出现的位置,结果将返回16.
SELECT INSTR('Doctor Who Who Who', 'Who',1,2) FROM DUAL;//从原字符串的第1个位置开始查找子串的第2次出现的位置,结果将返回12.
--------------------------------------------------------------------------------
数值函数
ROUND函数
数值函数指参数是数值类型的函数。常用的有ROUND、TRUNC、MOD、CEIL和FLOOR。其中ROUND用来四舍五入，语法如下：
ROUND(n[, m]) 用于将参数n按照m的数字要求四舍五入。其中：
参数中的n可以是任何数字，指要被处理的数字
m必须是整数
m取正数则四舍五入到小数点后第m位
m取0值则四舍五入到整数位
m取负数，则四舍五入到小数点前m位(即整数部分),-1表示保留到十位
m缺省，默认值是0
例如：
SELECT ROUND(45.678, 2) FROM DUAL; //结果为:45.68
SELECT ROUND(45.678, 0) FROM DUAL;//结果为:46
SELECT ROUND(45.678, -1) FROM DUAL;//结果为:50
--------------------------------------------------------------------------------
TRUNC函数
TRUNC(n[, m])的功能是截取，其中n和m的定义和ROUND(n[, m])相同，不同的是该方式按照截取的方式处理数字n,而不会四舍五入。例如：
SELECT TRUNC(45.678, 2) FROM DUAL; //结果为:45.67
SELECT TRUNC(45.678, 0) FROM DUAL;//结果为:45
SELECT TRUNC(45.678, -1) FROM DUAL;//结果为:40
--------------------------------------------------------------------------------
MOD函数
MOD(m, n)是取模函数，返回m除以n后的余数，如果n为0则直接返回m。
例如：
--薪水值按1000取余数
SELECT ename, sal, MOD(sal, 1000) FROM emp;
结果为:
ename sal MOD(sal,1000)
SMITH 800 800
ALLEN 1600 600
WARD 1250 250
--------------------------------------------------------------------------------
CEIL和FLOOR函数
CEIL(n)、FLOOR(n)这两个函数顾名思义，一个是天花板(向上取整)，就是取大于或等于n的最小整数值，一个是地板(向下取整)，就是取小于或等于n的最大整数值。比如数字n = 4.5，那么它的CEIL是5.0，它的FLOOR是4.0。在SQL语句中的例子如下：
SELECT CEIL(45.678) FROM DUAL; //结果为:46
SELECT FLOOR(45.678) FROM DUAL;//结果为:45
--------------------------------------------------------------------------------
日期类型(日期越靠后,其值越大)
SYSDATE关键字表示Date类型的一个值.这个值为当前系统时间.
TIMESTAMP(n) 可指定为0-9位，默认6位，最高精度可以到ns(纳秒)级别。
DATE与TIMESTAMP区别:
DATE表示到秒.TIMESTAMP表示到纳秒
所以TIMESTAMP在精度上更准确
DATE7个字节,TIMESTAMP可以到11个字节
--------------------------------------------------------------------------------
SYSDATE关键字
Oracle内部是靠一个函数实现.返回当前系统时间,返回的是DATE类型
查看当前系统时间,显示格式为:DD-MON-RR,结果显示的是日月年,但实际上是能精确到秒的.
SELECT SYSDATE FROM DUAL;
--------------------------------------------------------------------------------
获取当前系统时间的时间戳
SYSTIMESTAMP也是一个内部函数.
SELECT SYSTIMESTAMP FROM DUAL;
--------------------------------------------------------------------------------
TO_DATE函数(字符串转日期)
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 显示秒
例子：
查询2002年以后入职的员工：
SELECT ename, hiredate
FROM emp
WHERE hiredate >
TO_DATE('2008-08-08','YYYY-MM-DD');
--------------------------------------------------------------------------------
获取本周、本月、本季度、本年的一天和最后一天
本周
select trunc(sysdate,'d')+1 from dual;
select trunc(sysdate,'d')+7 from dual;
本月
select trunc(sysdate,'mm') from dual;
select last_day(trunc(sysdate)) from dual;
本季
select trunc(sysdate,'Q') from dual;
select add_months(trunc(sysdate,'Q'),3)-1 from dual;
本年
select trunc(sysdate,'yyyy') from dual;
select add_months(trunc(sysdate,'yyyy'),12)-1 from dual;
--------------------------------------------------------------------------------
TO_CHAR函数(日期转字符串)
TO_CHAR的作用是将日期类型（日期，数值）的数据转换为字符类型。语法格式：
TO_CHAR(date[, fmt[, nlsparams]])
其中fmt是格式，将日期类型数据date按照fmt格式输出字符串，nlsparams用于指定日期语言。例如：
SELECT ename, TO_CHAR(hiredate, 'YYYY"年"MM"月"DD"日"')
FROM emp;
需要注意的是,时间格式字符串中若出现了中文,则需要使用双引号(若不加则会报错):
例如:'YYYY"年"MM"月"DD"日"'
--------------------------------------------------------------------------------
日期常用函数
LAST_DAY函数
LAST_DAY(date)：返回日期date所在月的最后一天，一般是在按照自然月计算某些业务逻辑，或者安排月末周期性活动时很有用处。例子：
SELECT LAST_DAY(SYSDATE) FROM DUAL;--查询当月的最后一天
SELECT LAST_DAY('20-2月-09') FROM DUAL;--查询09年2月的最后一天
ADD_MONTHS函数
ADD_MONTHS(date, num)：返回日期date加上num个月后的日期值.
其中：
参数num可以是任何数字，大部分时候取正值整数
如果num是小数，将会被截取整数后再参与运算
如果num是负数，则获得的是减去num个月后的日期值
例如计算职员入职20周年纪念日：
SELECT ename, ADD_MONTHS(hiredate, 20 * 12) FROM emp;
MONTHS_BETWEEN函数
MONTH_BETWEEN(date1, date2)：计算date1和date2两个日期值之间间隔了多少个月，实际运算是date1-date2，如果date2时间比date1晚，会得到负值。
除非两个日期间隔是整数月，否则会得到带小数位的结果，比如计算2009年9月1日到2009年10月10日之间间隔多少个月，会得到1.29个月。例如计算职员入职多少个月：
SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate) FROM emp;
另外,在数据库中,两个日期之间是可以做减法操作的.结果就是两个日期之间间隔的天数.
NEXT_DAY函数
NEXT_DAY(date, char或1-7数字)：
返回离给定date日期最近的的下一个周几，周几是由参数char来决定的。在中文环境下，直接使用”星期三”这种形式，英文环境下，需要使用”WEDNESDAY”这种英文的周几。
为避免麻烦，可以直接用数字1-7表示周日-周六。
查询离SYSDATE最近的下个周三是几号：
SELECT NEXT_DAY(SYSDATE, 4) next_wedn FROM DUAL;
LEAST、GREATEST函数
比较函数LEAST和GREATEST语法如下：
GREATEST(expr1[, expr2[, expr3]]…)//返回最大值
LEAST(expr1[, expr2[, expr3]]…)//返回最小值
两个函数都可以有多个参数值，但参数类型必须一致，返回结果是参数列表中最大或最小的值。
在比较之前，在参数列表中第二个以后的参数会被隐含的转换为第一个参数的数据类型，所以如果可以转换，则继续比较，如果不能转换将会报错。
SELECT LEAST(SYSDATE, '10-10月 -08') FROM DUAL;//比较最小值,结果为:10-10月 -08
SELECT GREATEST(SYSDATE, '10-10月 -08') FROM DUAL;//比较最大值,结果为:SYSDATE日期
EXTRACT函数
EXTRACT(date FROM datetime)：从参数datetime中提取参数date指定的数据，
1.若datetime为DATE类型,则只能提取到YEAR、MONTH、DAY.例如:
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;//取出当前日期的年
2.若datetime为TIMESTAMP类型,则能提取到YEAR、MONTH、DAY、HOUR、MINUTE、SECOND.例如:
SELECT EXTRACT(HOUR FROM TIMESTAMP '2008-10-10 10:10:10') FROM DUAL;//取出当前日期的小时
需要注意的是:提取TIMESTAMP表示的时间戳分量时,没有考虑时区偏差值
--------------------------------------------------------------------------------
更新成NULL值
把数据表的某个字段更新为NULL值，和更新为其他数据的语法是相同的。比如更新
UPDATE student SET gender = NULL; //所有记录的gender字段值都被更新为NULL
注意这种更新只有在此列没有非空约束的情况下才可操作。如果gender列有非空约束，则无法更新为NULL值，上述语句会报错。
NULL条件查询
在条件查询中，因为NULL不等于任何值，所以不能用“列名=NULL”这种形式查询。必须用“列名 IS NULL”来判断，或者用“列名 IS NOT NULL”来查询非空数据。
SELECT * FROM student WHERE gender IS NULL;//查询gender字段值为NULL的记录
---------------------------------------------------------------
注意!:判断一个字段的值是否为null不能用=号,而是使用IS NULL来判断
---------------------------------------------------------------
--------------------------------------------------------------------------------
空值函数
NVL函数
NVL(expr1, expr2)：将NULL转变为非NULL值。如果expr1为NULL， expr2是非空值,则取值expr2。
--------------------------------------------------------------------------------------
其中expr1和expr2可以是任何数据类型,但两个参数的数据类型必须一致!
--------------------------------------------------------------------------------------
SELECT ename,sal+comm FROM emp;
计算员工月收入，如果comm列为空值的话，最终计算结果将是NULL，不符合逻辑，所以先将取NULL值的comm列转换为0，再相加。
例如:
SELECT ename, sal, comm, sal + nvl(comm, 0) salary
FROM emp;
NVL2函数
NVL2(expr1, expr2, expr3)：与NVL函数功能类似，都是将NULL转变为非空值。
NVL2用来判断expr1是否为NULL，如果不是NULL，返回expr2，如果是NULL，返回expr3。
----------------------------------------------------------------
注意!:expr2、expr3类型应当一致,但不必与expr1一致!
----------------------------------------------------------------
SELECT ename, sal, comm,
nvl2(comm, sal + comm, sal) salary
FROM emp;
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
字段别名
格式:字段 [as]别名
起别名可用来增加可读性.
别名本身不区分大小写,若希望区分或别名中含有空格,则别名需要使用双引号包含.
注意:只要查询的列含有表达式,就应当为其加上别名!
--------------------------------------------------------------------------------
WHERE子句
用于在查询数据的过程中过滤记录的.只有满足WHERE子句的条件的记录才会被查询出来.
数据库在查询表的时候,每一条记录都要经过一次WHERE的过滤,要注意的是:WHERE子句只针对
非聚合函数字段过滤(即普通字段)
--------------------------------------------------------------------------------
查询条件
使用>, <, >=, <=, !=, <>, =
------------------------------------------
注意:!=等价于<>,都是表示不等于,通常建议用<>
------------------------------------------
在WHERE子句中的查询条件，可以使用比较运算符来做查询。
查询职员表中属于不部门10的员工信息：
SELECT ename, sal, job FROM emp WHERE deptno <> 10;
查询职员表中在2002年1月1号以后入职的职员信息，比较日期类型数据：
SELECT ename, sal, hiredate FROM emp
WHERE hiredate > to_date('2002-1-1','YYYY-MM-DD');
--------------------------------------------------------------------------------
使用AND，OR关键字
---------------------------------------------
注意!:AND的优先级是高于OR的,可用括号改变优先级
---------------------------------------------
在SQL操作中，如果希望返回的结果必须满足多个条件，应该使用AND逻辑操作符连接这些条件，如果希望返回的结果满足多个条件之一即可，应该使用OR逻辑操作符连接这些条件。
例如：
查看工资大于1250的CLERK,或者不限制工资的所有SALESMAN:
SLECT ename,sal,job FROM emp WHERE sal>1250 AND job='CLERK' OR job='SALESMAN';
用括号提高优先级后的意思:查看工资大于1250的CLEARK和SALESMAN:
SLECT ename,sal,job FROM emp WHERE sal>1250 AND (job='CLERK' OR job='SALESMAN');
--------------------------------------------------------------------------------
使用LIKE条件（模糊查询,区分大小写）
当用户在执行查询时，不能完全确定某些信息的查询条件，或者只知道信息的一部分，可以借助LIKE来实现模糊查询。LIKE需要借助两个通配符：
%：表示0到多个字符
_：表示1个字符
这两个通配符可以配合使用，构造灵活的匹配条件。
例如:查询职员姓名中第二个字符是‘A’的员工信息：
SELECT ename, job FROM emp WHERE ename LIKE '_A%';
--------------------------------------------------------------------------------
使用IN和NOT IN
用比较操作符IN(list)用来取出符合列表范围中的数据。其中的参数list表示值列表，
当字段值或表达式匹配于列表中的任何一个值时，条件为TRUE，该条记录则被显示出来。
IN页可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表，
NOT IN(list) ,与IN(list)相反,即取出不符合此列表中的数据记录。
例如查询职位是MANAGER或者CLERK的员工：
SELECT ename, job FROM emp WHERE job IN ('MANAGER', 'CLERK');
查询不是部门10或20的员工：
SELECT ename, job FROM emp WHERE deptno NOT IN (10, 20);
--------------------------------------------------------------------------------
BETWEEN…AND…
BETWEEN…AND…操作符用来查询符合某个值域范围条件的数据，小值在AND左边,大值在AND右边.
最常见的是使用在数字类型的数据范围上，但对字符类型和日期类型数据也同样适用。
例如:查询薪水在1500-3000之间的职员信息：
SELECT ename, sal FROM emp WHERE sal BETWEEN 1500 AND 3000;
--------------------------------------------------------------------------------
使用ANY和ALL条件
在比较运算符中，可以出现ALL和ANY，表示“全部”和“任一”，但是ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。其中：
若是ANY则是大于列表中其中之一即可,所以是大于最小的,若是ALL则是大于最大的.
ANY和ALL常用在子查询,而非直接给值
> ANY: 所有大于最小的记录值
< ANY：所有小于最大的记录值
> ALL：所有大于最大的记录值
< ALL：所有小于最小的记录值
例如，查询薪水比职位是“SALESMAN”的人高的员工信息，比任意一个SALESMAN高都行：
SELECT empno, ename, job, sal, deptno
FROM emp
WHERE sal > ANY (
SELECT sal FROM emp WHERE job = 'SALESMAN');
--------------------------------------------------------------------------------
查询条件中使用表达式和函数
当查询需要对选出出的字段进行进一步计算，可以在数字列上使用算术表达式(+、-、*、/)。表达式符合四则运算的默认优先级，
如果要改变优先级可以使用括号.
算术运算主要是针对数字类型的数据，对日期类型的数据可以做加减操作，表示在一个日期值上加或减一个天数。
查询条件中使用字符串函数UPPER，将条件中的字符串变大写后再参与比较：
SELECT ename, sal, job FROM emp WHERE ename = UPPER('rose');
--------------------------------------------------------------------------------
DISTINCT关键字
格式:DISTINCT 字段1[,字段2...]
用于去除给定列的重复数据
当我们对多个列进行去重时,表示这几个列的值组合起来没有重复的
例如:
SELEXT DISTINCT deptno,job FROM emp;
--------------------------------------------------------------------------------
单列排序
使用ORDER BY子句,默认为升序
对查询出的数据按一定规则进行排序操作，使用ORDER BY子句。语法如下：
SELECT <*, column [alias], …>
FROM table
[WHERE condition(s)]
[ORDER BY column[,column [ASC | DESC]]...] ;
ASC:默认排序规则,可以不写,表示升序
DESC:表示降序
-----------------------------------------------------------------------------------------
注意!:ORDER BY必须出现在SELECT中的最后一个子句。后面可以指定若干字段,排序优先级为从左到右
另外在排序一个含有NULL值的字段时,NULL值被认为是无限大,所以在降序排序时,NULL值会在最前.
-----------------------------------------------------------------------------------------
例如:对职员表按薪水排序(升序)：
SELECT ename, sal FROM emp WHERE deptno=10 ORDER BY sal;
多列排序
当以多列作为排序标准时，首先按照第一列进行排序，如果第一列字段值相同，再以第二列排序，以此类推。
多列排序时，不管正序还是倒序，每个列需要单独设置排序方式。
下例对职员表中的职员排序，先按照部门编码正序排列，再按照薪水降序排列：
SELECT ename, deptno, sal FROM emp
ORDER BY deptno ASC, sal DESC;
-----------------------------------------------------------------------------------------------------
聚合函数
也称作分组函数、多行函数、集合函数。
----------------------------------------------------------------------------
注意!:聚合函数都是忽略NULL值的,若不想忽略NULL值,可以用NVL函数转换
例如:SELECT AVG(NVL(sal,0)) avg_sal FROM emp;
聚合函数不能出现在 WHERE 子句中,可以在HAVING 子句或SELECT列表所包含的子查询里
----------------------------------------------------------------------------
MAX和MIN函数
用来取得列或表达式的最大、最小值，可以用来统计任何数据类型，包括数字、字符和日期。
例如获取机构下的最高薪水和最低薪水，参数是数字：
SELECT MAX(sal) max_sal, MIN(sal) min_sal FROM emp;
计算最早和最晚的入职时间，参数是日期：
SELECT MAX(hiredate) max_hire, MIN(hiredate) min_hire FROM emp;
AVG和SUM函数
AVG和SUM函数用来统计列或表达式的平均值和和值，这两个函数只能操作数字类型。
例如获得机构下全部职员的平均薪水和薪水总和(忽略NULL值的)：
SELECT AVG(sal) avg_sal, SUM(sal) sum_sal FROM emp;
COUNT函数
COUNT函数用来计算表中的记录条数.
例如获取职员表中一共有多少名职员记录：
SELECT COUNT(*) total_num FROM emp;
获得职员表中有多少人是有职位的（忽略没有职位的员工记录）
SELECT COUNT(job) total_job FROM emp;
--------------------------------------------------------------------------------
分组
GROUP BY子句
语法如下：
SELECT <*, column [alias], …>
FROM table [WHERE condition(s)]
[GROUP BY group_column[,group_column...]]
[HAVING group_condition]
[ORDER BY column [ASC | DESC]] ;
用于将表中的数据进行分组,分组原则为:
GROUP BY后面给定的字段的值相同的记录被看做一组.
当以多列分组时，首先按照第一列进行分组，如果第一列分组后字段值相同，再以第二列分组，以此类推。
查看每个部门的最大工资,最小工资:
SELECT MAX(sal),MIN(sal),AVG(sal),SUM(sal), deptno FROM emp GROUP BY deptno;
用了GROUP BY子句时:
SQL有一个要求:在SELECT子句中若出现了聚合函数,那么不在聚合函数中的其他字段,就必须出现在GROUP BY子句中.但是反过来不是必须的.
也就是说,在SELECT子句中若出现了聚合函数,那么在SELECT后面的字段要么在聚合函数里,要么在GROUP BY子句里.
若字段中每条记录的值不重复,那么该字段通常不应在GROUP BY子句中作为分组的参照.
--------------------------------------------------------------------------------
HAVING子句
HAVING子句用来对分组后的结果进一步限制,必须跟在GROUP BY后面，不能单独存在,
所以HAVING用在分组统计结果之后再进行的过滤.
也是用作过滤条件,只不过HAVING是第二次过滤的.
----------------------------------------------------------------------
特别要注意的是:HAVING子句后过滤的字段不能是非聚合函数字段(即普通字段)
----------------------------------------------------------------------
WHERE与HAVING的区别:
WHERE是用在第一次查询表的时候过滤条件,会从第一条记录开始逐行过滤.
而HAVING是在第一次分组查询之后,得到结果的基础上再次进行过滤.
WHERE是针对普通字段进行过滤,HAVING是针对聚合函数进行过滤
--------------------------------------------------------------------------------
查询语句的执行顺序(重点)
当一条查询语句中包含所有的子句，执行顺序依下列子句次序：
FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。
WHERE子句：执行顺序为自下而上、从右到左。将过滤记录数量最大的条件写在WHERE 子句的最右。
GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。
HAVING 子句：消耗资源。尽量避免使用，HAVING会在分组查询所有记录之后才对结果集进行过滤,需要排序等操作。
SELECT子句：少用*号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。
ORDER BY子句：执行顺序为从左到右排序，消耗资源。
--------------------------------------------------------------------------------
关连查询
N张表联合查询,至少要有N-1个连接条件,否则会产生笛卡尔积:A表的记录总数 * B表的记录总数,数量过大会急剧消耗性能且查询出的结果无意义.
因为查询时难免可能会出现两张表字段名相同的情况,为了解决这个问题,可以用格式:
表名.字段名
若表名比较长,可以给表名取别名来简化,可以用格式:
表别名.字段名
例如:
SQL89标准查询格式:
SELECT <*, column [alias], …> FROM 表1,表2 WHERE 表1.连接字段=表2.连接字段 AND 过滤条件
select e.ename,e.sal,d.dname,e.deptno from emp e,dept d where e.deptno=d.deptno;
内连接(内连接返回两个关联表中所有满足连接条件的记录):
也可以实现多表关联查询
SQL92标准查询格式:
SELECT 表1.字段,表2.字段 FROM 表1 JOIN 表2 ON 表1.连接字段=表2.连接字段 WHERE 过滤条件
例如:
SELECT e.ename,e.sal,d.dname,e.deptno FROM emp e JOIN dept d ON e.deptno=d.deptno;
SQL89标准弊端在于,连接条件与过滤条件都要写在WHERE子句中,可读性相对较差.尤其多表查询加上过滤条件多的时候.
SQL92标准建议我们在进行多表连接查询时使用内连接形式,在于连接条件单独定义在ON子句中,而过滤条件写在WHERE子句中,可读性较强.
外连接
外连接不仅返回满足连接条件的记录，还将返回不满足连接条件的记录。
外连接的语法如下：
SELECT 表1.字段, 表2.字段
FROM 表1 <LEFT | RIGHT | FULL> [OUTER] JOIN 表2
ON 表1.字段 = 表2.字段 WHERE 过滤条件;
外连接分为左外连接,右外连接,全外连接.
左外连接:以左表为驱动表,即左表记录全显示,对应的右表有不满足连接条件的,全部改为NULL进行显示.
右外连接:跟左外连接相反
全外连接:左表、右表记录全显示,哪边不满足连接条件,则哪边改NULL进行显示.
自连接
自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自于单表中的多个列。表中的列参照同一个表中的其它列的情况称作自参照表。
自连接是通过将表用别名虚拟成两个表的方式实现，可以是等值或不等值连接。
通常表被设计成自连接是为了表示同类型数据有存在上下级关系(树状结构的数据通常设计为自连接)
例如查出每个职员的经理名字，以及他们的职员编码：
SELECT worker.empno w_empno, worker.ename w_ename, manager.empno m_empno, manager.ename m_ename
FROM emp worker join emp manager
ON worker.mgr = manager.empno;
--------------------------------------------------------------------------------
主键:通常每张表的第一个字段就是主键,主键保存的值只是用来表示每条数据的唯一性,所以主键的要求是:存储的值非空且唯一.
外键:用来保存关系表中记录的主键的值.
在关联关系一对多中,保存外键的表通常是"多"的一方.
而我们在编写多表查询的SQL语句时,连接条件通常也是写的主键=外键.
--------------------------------------------------------------------------------
子查询:
当我们的一个操作需要基于另一个查询结果,那么先行执行的这个查询就是子查询.
子查询分为:
单行单列子查询:查询出的结果只有一行,且只有一个字段(单个结果值).
多行单列子查询:查询出来的结果有多行,但只有一列(多个结果值).
多行多列子查询:查询出多行和多列(一张结果表,通常给该表起别名便于访问).不能用在WHERE中,一般用在FROM子句后.
通常单行单列与多行单列子查询用于WHERE子句中,而多行多列子查询用于FROM子句中.
例如查找和SCOTT员工的同职位的员工：
SELECT e.ename, e.job FROM emp e WHERE e.job =(SELECT job FROM emp WHERE ename = 'SCOTT')；
当子查询返回的是一个多行单列的结果,我们就不能使用=号了,因为是个列表结果,可以用IN:
SELECT empno, ename, job, sal, deptno
FROM emp
WHERE deptno IN
(SELECT deptno FROM emp WHERE job = 'SALESMAN')
AND job <> 'SALESMAN';
--------------------------------------------------------------------------------
子查询在HAVING部分
子查询不仅可以出现在WHERE子句中，还可以出现在HAVING部分。
例如查询列出各部门中最低薪水高于部门30的最低薪水的部门信息：
SELECT deptno, MIN(sal) min_sal
FROM emp
GROUP BY deptno
HAVING MIN(sal) >
(SELECT MIN(sal) FROM emp WHERE deptno = 30);
查询比本部门平均薪水高的员工信息:
先统计每个部门的平均工资,其返回结果为一个多行多列的表,然后使用EMP表与其关联查询即可.
SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT deptno,AVG(sal) a_sal FROM emp GROUP BY deptno) x
WHERE e.deptno=x.deptno AND e.sal>x.a_sal;
--------------------------------------------------------------------------------
子查询在SELECT部分
把子查询放在SELECT子句部分，可以认为是左外连接的另一种表现形式(emp与dept),使用更灵活：
SELECT e.ename, e.sal, e.deptno,
(SELECT d.deptno FROM dept d
WHERE d.deptno = e.deptno) deptno1
FROM emp e;
--------------------------------------------------------------------------------
EXISTS的作用
当子查询中可以至少返回一条记录,那么表达式返回true
例如:
SELECT deptno,dname FROM dept d WHERE EXISTS (SELECT * FROM emp e WHERE d.deptno=e.deptno);
查看含有员工的部门
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Oracle分页(重点掌握)
将结果集分批获取.
目的:加快查询,减小系统资源消耗.
分页至少需要为记录编号以及排序
编号:
在Oracle中可以使用ROWNUM字段(伪列),ROWNUM本身不在表中,该字段值是源自表中查询出来的记录进行的号.
Oracle数据库自动生成该列的值.
ROWNUM字段后面不能直接跟*，必须指定表名.例如:select rownum,表名.* from 表名;
注意:ROWNUM不能以任何表的名称作为前缀.
示例:
SELECT ROWNUM, empno, ename, sal
FROM emp
WHERE ROWNUM>3;
该查询将会出错,原因在于:
--------------------------------------------------------------------------------
ROWNUM的生成时机:
ROWNUM刚开始会自动生成且初始默认值为1,之后只有每取出一条记录(如有过滤需过滤成功)
ROWNUM才会每次累加1,当第一次查询表时,ROWNUM为1,是不满足大于10的,所以WHERE
条件永远不成立,也就不会取出记录,因此ROWNUM就不会累加.所以没有数据被查询出来
--------------------------------------------------------------------------------
通常若想获取部分记录,则需要用到子查询,第一次先将所有数据进行编号,并给该列取个别名,其结果会作为一张结果表返回,然后再根据这个结果表进行二次查询,来获取部分数据.
-------------------------------------------------------------------------------------------
注意:子查询中的ROWNUM必须要有别名，否则还是不会查出记录来，
因为ROWNUM不是某个表的列，如果不起别名的话，无法知道ROWNUM是子查询的列还是主查询的列。
非子查询则不行,要直接用ROWNUM.
-------------------------------------------------------------------------------------------
在排序的时候需要注意:
1.要么通过子查询先排序,再去主查询查 ROWNUM字段,即用select嵌套:内层排序外层选
2.要么就直接给排序的字段加上主键或索引
-------------------------------------------------------------------------------------------
标准分页通常分为三步:1排序,2编号,3取部分数据
方式一:SELECT * FROM(
SELECT ROWNUM RN,E.* FROM (
SELECT * FROM 表名 ORDER BY 排序字段
) E
)
WHERE RN BETWEEN 开始 AND 结束;
方式二(推荐):SELECT * FROM(
SELECT ROWNUM RN,E.* FROM (
SELECT * FROM 表名 ORDER BY 排序字段
) E
WHERE ROWNUM<=结束
)
WHERE RN >=开始;
以上两种方式中,方式二性能更佳,推荐使用.
根据要查看的页数，计算起点值（(n - 1) * pageSize + 1）和终点值（n * pageSize），替换掉BETWEEN和AND的参数，即得到当前页的记录。
---------------------------------
|每页中记录头条与尾条公式: |
|适用于当页号从1开始时: |
|pageSize:页面大小 |
|n:页号 |
|尾条:n*pageSize |
|头条:(n-1)*pageSize+1 |
---------------------------------
--------------------------------------------------------------------------------
DECODE函数
DECODE函数基本语法
DECODE函数的语法如下：
DECODE (expr, search1, result1[, search2, result2…][, default])
用于比较参数expr和search的值,匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，
如果任何一个search条件都没有匹配到，则返回最后default的值。
default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。
用例:
SELECT ename,job,sal,
DECODE(job, //对应expr,比较的值
'MANAGER',sal*1.2, //对应search1,result1
'ANALYST',sal*1.1, //对应search2,result2
'SALESMAN',sal*1.05, //对应search3,result3
sal //给出的默认值
) bonus
FROM emp;
和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。
用例:
SELECT ename, 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函数在分组查询中的应用(可自定义分组规则)
DECODE函数可以按字段内容分组，当自带的分组无法满足我们需要时,可以采用DECODE方式
将其转换为一个其他的值,从而重新定义分组规则.
例如：计算职位的人数，analyst/manager职位属于vip，其余是普通员工operation,
这种功能无法用GROUP BY简单实现。用decode的实现方式：
将MANAGER和ANALYST这两个职位看做一组,其他职位的看做另一组,统计这两组的总人数.
思路:将需要被看做一组,但值不相同的那些数据,我们使用DECODE将他们改为相同的值即可.
SELECT DECODE(
job,
'MANAGER','VIP',
'ANALYST','VIP',
'OPERATIONS'
) JOB_NAME,COUNT(*)
FROM emp
GROUP BY DECODE(
job,
'MANAGER','VIP', //job值为MANAGER时,返回VIP
'ANALYST','VIP', //job值为ANALYST时,返回也VIP
'OPERATIONS' //未匹配时,默认都返回OPERATIONS
);
DECODE函数在排序中的应用(可自定义排序规则)
当我们按照某个字段的值进行排序,但是字段值的比较大小规则不满足我们排序需求时,可以使用DECODE方式
将其转换为一个其他的值,从而重新定义排序规则.
例如:Dept表中按”研发部”、“市场部”、“销售部”排序，用普通的select语句，无法按照字面数据排序：
SELECT deptno, dname, loc
FROM dept
ORDER BY
DECODE(dname, '研发部',1,'市场部',2,'销售部',3), loc;
--------------------------------------------------------------------------------
排序函数
ROW_NUMBER函数(生成的编号组内连续且唯一)
ROW_NUMBER语法如下：
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
表示先根据col1分组，然后在分组内部根据col2排序。此函数计算的值就表示每组内部排序后的顺序编号-----组内连续且唯一。
ROW_NUMBER可以直接从结果集中取出子集。
按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码
SELECT deptno, ename, empno,
ROW_NUMBER()
OVER (PARTITION BY deptno ORDER BY empno) emp_id
FROM emp;
RANK函数(生成的编号组内既不连续也不唯一)
RANK函数的语法如下：
RANK() OVER(PARTITION BY col1 ORDER BY col2)
功能与ROW_NUMBER大致相同,区别在于:在按照col2排序时若字段若值相同并且在同一组时,那么它们的顺序编号值一样,但在之后的编号会有跳跃,
RANK会在组内排序时生成既不连续也不唯一的数字.
DENSE_RANK函数(生成的编号组内连续但不唯一)
DENSE_RANK函数的语法如下：
DENSE_RANK() OVER(
PARTITION BY col1 ORDER BY col2)
与RANK的区别在于在按照col2排序时若字段若值相同并且在同一组时,那么它们的顺序编号值一样,在之后的编号是连续的不会跳跃
DENSE_RANK会生成组内连续但不唯一的数字
--------------------------------------------------------------------------------
集合操作中:
并集(UNION):将两个集合中的所有元素合成一个集合,合并后新的集合不含重复元素
全并集(UNION ALL):将两个集合中的所有元素合成一个集合,两个集合都有的元素会在合并后新的集合中重复出现
交集(INTERSECT):合并后新的集合中只保留共有的元素.
差集(MINUS):合并后新的集合中只保存第一个集合有,第二个集合无的元素(ORACLE中就是前表有,后表无).
Oracle与之对应也有相关的表操作
也分为:UNION|UNION ALL|INTERSECT|MINUS
--------------------------------------------------------------------------------
高级分组函数:
需求1:
统计销售额,统计分为:
1:按照年月日(每天的销售额)
2:按照年月(每月的销售额)
3:按照年(每年的销售额)
4:整张表(所有的销售额)
依次统计
ROLLUP函数
ROLLUP函数的语法形式：
GROUP BY ROLLUP(a, b, c)
ROLLUP函数会有ROLLUP参数的个数+1次分组,
如:
ROLLUP(a,b,c)
分组4次
a,b,c
a,b
a
全表
之后将所有统计结果UNION ALL在一起
示例:
SELECT year_id, month_id, day_id, SUM(sales_value) sales_value
FROM sales_tab
GROUP BY ROLLUP (year_id, month_id, day_id)
ORDER BY year_id, month_id, day_id;
CUBE函数
CUBE函数的语法形式：
GROUP BY CUBE(a, b, c)
对cube的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合。
对于n个参数的cube，有2的n次方个分组。
如:
CUBE(a,b,c)
a,b,c
a,b
a,c
b,c
a
b
c
全表
之后将所有统计结果UNION ALL在一起
示例:
SELECT year_id, month_id,SUM(sales_value) sales_value
FROM sales_tab
GROUP BY CUBE (year_id, month_id)
ORDER BY year_id, month_id;
GROUPING SETS函数
GROUPING SETS函数可以生成与使用单个 GROUP BY、ROLLUP 或 CUBE 运算符所生成的结果集相同的结果集，但是使用更灵活。
如果不需要获得由完备的 ROLLUP 或 CUBE 运算符生成的全部分组，则可以使用 GROUPING SETS 仅指定所需的分组。
GROUPING SETS 列表可以包含重复的分组。
GROUPING SETS函数的语法形式:
1.GROUP BY GROUPING SETS(a, b, c)
此方式对于参数的GROUPING SETS,分组如下:
a
b
c
之后将所有统计结果UNION ALL在一起
2.GROUPING SETS((ab),(ac))
此方式对于参数的GROUPING SETS,分组如下:
ab
ac
之后将所有统计结果UNION ALL在一起
示例：
SELECT year_id, month_id, SUM(sales_value)
FROM sales_tab
GROUP BY CUBE (year_id,month_id)
order by 1, 2;
SELECT year_id, month_id, SUM(sales_value)
FROM sales_tab
GROUP BY GROUPING SETS ((year_id), (month_id))
order by 1, 2
视图:
视图并非一张表,视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，
结果集被赋予一个名字，即视图名字。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，
-----------------------------------------------------------------------------
当基表数据发生变化，视图数据也会关联变化。
视图可以插入视图范围外的数据.但这样的操作会对原表数据有破坏效果.逾越了视图查看范围.
UPDATE语句也是如此,可能更新后,数据不会在视图中再次找到
--------------------------------------------------------------------------------------------------
根据视图所对应的子查询种类分为几种类型:
SELECT语句是基于单表建立的，且不包含任何函数运算、表达式或分组函数，叫做简单视图，此时视图是基表的子集；
SELECT语句同样是基于单表，但包含了单行函数、表达式、分组函数或GROUP BY子句，叫做复杂视图；
SELECT语句是基于多个表的，叫做连接视图。
视图通常用来:
1.简化查询
2.限制数据访问
创建视图的语法：
CREATE [OR REPLACE] VIEW view_name[(alias[, alias…])]
AS
SELECT (column[,column…])
[WITH CHECK OPTION]|[WITH READ ONLY];
要注意的是:
alias是与SELECT语句后的column是对应的,用于查询后在视图中的别名
CREATE OR REPLACE 的意义在于若视图不存在就创建,否则就替换.
这里不适用ALTER来修改视图,因为视图没有实际的字段以及对应的类型与长度,只是一条SELECT语句而已.
--------------------------------------------------------------------
WITH CHECK OPTION短语表示:通过视图所做的修改，必须在视图的可见范围内：
INSERT:新增的记录在视图仍可查看
UPDATE:修改后的结果必须能通过视图查看到
DELETE:只能删除现有视图里能查到的记录;
WITH READ ONLY短语表示:当视图被该短语修饰后,那么该视图不允许任何DML操作.
对简单视图进行DML操作是合法的,但是是不安全的.如果没有在视图上执行 DML 操作的必要,
在建立视图时声明为只读来避免这种情况,保证视图对应的基表数据不会被非法修改.
--------------------------------------------------------------------
视图创建后，可以跟操作表一样操作视图:(RUD)主要是查询操作。
语法中的subquery是SELECT查询语句，对应的表被称作基表。
------------------------------------------------------
若想隐藏基表列名,则只需要在Subquery中给对应字段起别名即可
只允许对简单视图进行DML操作,而复杂视图则不支持DML操作.
------------------------------------------------------
例如:
CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal salary,deptno dno
FROM emp
WHERE dno=10;
数据字段:
ORACLE中内建的一些表,用来维护用户创建的数据库对象,和其他信息.
user_tables表:该表中记录了用户创建过的所有表:
SELECT * FROM user_tables;
user_views表:该表中记录了用户创建过的所有视图:
SELECT * FROM user_views;
user_objects表:该表记录了用户建立过的所有数据库对象.
SELECT object_name
FROM user_objects
WHERE object_type='VIEW';//所有视图对象
user_update_columns：在数据字典中USER_UPDATE_COLUMNS查询视图：
SELECT column_name, insertable, updatable, deletable
FROM user_updatable_columns
WHERE table_name = 'V_EMP_10';
当不再需要视图的定义,可以使用DROP VIEW语句删除视图,语法如下:
DROP VIEW view_name;
视图虽然是存放在数据字典中的独立对象，但视图仅仅是基于表的一个查询定义，所以对视图的删除不会导致基表数据的丢失.
--------------------------------------------------------------------------------------------
序列
序列(SEQUENCE)是一种用来生成唯一数字值的数据库对象。序列的值由Oracle程序按递增或递减顺序自动生成，
通常用来自动产生表的主键值，是一种高效率获得唯一键值的途径。
序列是独立的数据库对象，和表是独立的对象，序列并不依附于表。
通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值,但一般不注意使用,会造成生成的数字不连续.
序列中有两个伪列：
序列对象.NEXTVAL:该位列会生成序列的下一个值,这个操作会消费序列的一个值,注意!:属于不可逆操作.
序列对象.CURRVAL:获取序列最后生成的数字,可重复调用,注意!:在第一次使用序列时,要先执行一次NEXTVAL后才可使用.
创建序列
创建序列的语法：
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方案下
序列的第一个序列值是i，步长是j
如果j是正数，表示递增，如果是负数，表示递减
序列可生成的最大值是m，最小值是n
如果没有设置任何可选参数，序列的第一个值是1，步进是1
CYCLE表示在递增至最大值或递减至最小值之后是否继续生成序列号，默认是NOCYCLE
CACHE用来指定先预取p个数据在缓存中，以提高序列值的生成效率，默认是20
主键生成另一机制:UUID:32位不重复字符串(字母加数字)
Oracle中提供了对应的函数:sys_guid()来生成一个UUID数
SELECT sys_guid() FROM DUAL;
-------------------------------------------------------------------------------------------------
使用序列
例如:
1.创建一个序列,起始数据是100,步长是10:
CREATE SEQUENCE emp_seq
START WITH 100
INCREMENT BY 10;
当序列被创建后，第一个序列值将是100，将要生成的序列号分别是110、120、130等。
2.当序列创建以后,必须先执行一次NEXTVAL,之后才能使用CURRVAL.
获取序列的第一个值，并且使用序列值为EMP表插入新的记录：
SELECT emp_seq.NEXTVAL FROM DUAL;
INSERT INTO emp(empno, ename) VALUES(emp_seq.NEXTVAL, 'donna');
-------------------------------------------------------------------------------------------------------------------
删除序列
删除序列的语法如下：
DROP SEQUENCE sequence_name;
例如:删除序列emp_seq:
DROP SEQUENCE emp_seq;
-------------------------------------------------------------------------------------------------------------------
索引
索引的原理:
索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，
可以存放在与表不同的表空间（TABLESPACE）中。索引记录中存有索引关键字和指向表中数据的指针（地址）。
索引一旦被建立就将被Oracle系统自动应用维护,查询语句中不用指定使用哪个索引,是一种提高查询效率的机制。
创建索引
创建索引的语法：
CREATE [UNIQUE] INDEX index_name ON table_name(column[, column…]);
其中：
index_name表示索引名称
table_name表示表名
column表示列名，可以建立单列索引(单列)或复合索引(多列)
UNIQUE表示唯一索引
例如:在EMP表的ENAME列上建立索引：
CREATE INDEX idx_emp_ename ON emp(ename);
-----------------------------------------------------------------------------------------
复合索引也叫多列索引，是基于多个列的索引。如果经常在ORDER BY子句中使用多个列作排序时，
可以建立复合索引来加快查询排序速度,需要注意的是ORDER BY中的顺序要与建立索引时的顺序一致.
-----------------------------------------------------------------------------------------
例如:
CREATE INDEX idx_emp_job_sal ON emp(job, sal);
当做下面的查询时，会自动应用索引idx_emp_job_sal
SELECT empno, ename, sal, job FROM emp
ORDER BY job, sal;
创建基于函数的索引
1.当我们经常需要将一个函数的结果作为过滤条件时,我们也可以为该函数添加一个索引:
例如:
CREATE INDEX idx_emp_ename_upper ON emp(UPPER(ename));
2.下面的sql语句会自动套用上面创建的索引,以提高检索效率:
SELECT * FROM emp WHERE UPPER(ename)='KING';
修改和删除索引
如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率，语法如下：
ALTER INDEX index_name REBUILD;
例如:重建索引idx_emp_ename：
ALTER INDEX idx_emp_ename REBUILD;
当一个表上有不合理的索引，会导致操作性能下降，删除索引的语法：
DROP INDEX index_name;
例如:删除索引idx_emp_ename
DROP INDEX idx_emp_ename;
-------------------------------------------------------------------------------------------------------------------
合理使用索引提升查询效率
为提升查询效率，创建和使用索引的原则：
为经常出现在WHERE子句中的列创建索引
为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
为经常作为表的连接条件的列上创建索引
不要在经常做DML操作的表上建立索引
不要在小表上建立索引(第一原则)
限制表上的索引数目，索引并不是越多越好
删除很少被使用的、不合理的索引
-------------------------------------------------------------------------------------------------------------------
约束
约束的作用
约束（CONSTRAINT）的全称是约束条件，也称作完整性约束条件。约束是在数据表上强制执行的一些数据校验规则，当执行DML操作时，数据必须符合这些规则，如果不符合则无法执行。
约束条件可以保证表中数据的完整性，保证数据间的商业逻辑。
约束的类型
约束类型包括：
非空约束(Not Null)，简称NN
唯一性约束(Unique)，简称UK
主键约束(Primary Key)，简称PK
外键约束(Foreign Key)，简称FK
检查约束(Check)，简称CK
约束分为表级约束与列级约束:
表级约束:在创建表时,所有列都声明完毕后,单独再为某个列指定约束.
列级约束:定义列的同时声明对应的约束.
非空约束,指定定义为列级.
在修改表时,为某个字段添加约束时,应当注意的是该字段的所有记录应当满足要添加的约束,否则可能添加约束失败.
建表时添加非空约束：
CREATE TABLE employees (
eid NUMBER(6),
name VARCHAR2(30) NOT NULL,
salary NUMBER(7, 2),
hiredate DATE
CONSTRAINT employees_hiredate_nn NOT NULL
);
通过修改表的定义,添加非空约束：
ALTER TABLE employees
MODIFY (eid NUMBER(6) NOT NULL);
通过修改表的定义,取消非空约束
如果要求取消某列的非空约束,可以采用重建表或者修改表的方式：
ALTER TABLE employees
MODIFY (eid NUMBER(6) NULL);
---------------------------------------------------
重点
1.定义字段时创建(列级):
字段名 字段类型 约束类型
2.作为建表时的尾定义语句(表级)
CONSTRAINT 约束名 约束类型(被约束字段)
3.在建表之后增加唯一性约束条件(表级):
ALTER TABLE 表名
ADD CONSTRAINT 约束名 约束类型(被约束字段);
删除约束:
将给定的表中的约束删除,语法格式:
ALTER TABLE 表名
DROP CONSTRAINT 约束名;
---------------------------------------------------
唯一性约束
当某个表声明了唯一性约束后,gia字段的值在整张表中不能有重复值.NULL值除外.
唯一性约束可以声明为表级约束和列级约束.
建表时添加唯一约束:
CREATE TABLE employees (
eid NUMBER(6) UNIQUE,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE,
CONSTRAINT employees_email_uk UNIQUE(email)
);
建表之后增加唯一性约束条件：
ALTER TABLE employees
ADD CONSTRAINT employees_name_uk UNIQUE(name);
主键约束
主键(Primary Key)约束条件从功能上看相当于非空（NUT NULL）且唯一（UNIQUE）的组合。主键字段可以是单字段或多字段组合，
即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值。
主键可以用来在表中唯一的确定一行数据。一个表上只允许建立一个主键，而其它约束条件则没有明确的个数限制。
主键选取的原则:
主键应是对系统无意义的数据
永远也不要更新主键，让主键除了唯一标识一行之外，再无其他的用途
主键不应包含动态变化的数据，如时间戳
主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义
主键尽量建立在单列上
主键约束可以声明为表级约束和列级约束.
声明表时创建主键:
CREATE TABLE employees3 (
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE,
CONSTRAINT employee3_eid_pk PRIMARY KEY(eid)
);
建表之后增加主键约束:
ALTER TABLE employees3
ADD CONSTRAINT
employees3_eid_pk PRIMARY KEY (eid);
外键约束
外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系。
例如:
ALTER TABLE employees4
ADD CONSTRAINT employees4_deptno_fk
FOREIGN KEY (deptno) REFERENCES dept(deptno);
外键约束对一致性的维护
外键约束条件包括两个方面的数据约束：
从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL；
当主表参照列的值被从表参照时，主表的该行记录不允许被删除。
外键约束对性能的降低
如果在一个频繁DML操作的表上建立外键，每次DML操作，都将导致数据库自动对外键所关联的对应表做检查，产生开销，
如果已在程序中控制逻辑，这些判断将增加额外负担，可以省去。
另外外键确定了主从表的先后生成关系，有时会影响业务逻辑。
关联不一定需要外键约束
如果业务逻辑要求保证数据完整性，可由程序或触发器控制，不一定需要外键约束。
另外为了简化开发，维护数据时不用考虑外键约束，以及大量数据DML操作时不需考虑外键耗费时间。
检查约束
检查(Check)约束条件用来强制在字段上的每个值都要满足Check中定义的条件。当定义了Check约束的列新增或修改数据时，
数据必须符合Check约束中定义的条件。
建表时增加检查约束:
CREATE TABLE employee4(
name VARCHAR2(30),
salary NUMBER(7,2),
hiredate DATE,
CONSTRAINT sal_check CHECK(salary > 2000)
);
建表后增加检查约束:
ALTER TABLE employees4
ADD CONSTRAINT employees4_salary_check
CHECK (salary > 2000);
305
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
