15.ORACLE数据库
数据定义语言（DDL） : Date Definition Language
CREATE TABLE 、 ALTER TABLE(add modify drop) 、DROP TABLE、
TRUNCATE TABLE、 DESC 、 RENAME 、 default、 null 、not null、unique
数据操纵语言（DML） : Date Manipulation Language
INSERT INTO...VALUES()、UPDATE...SET、DELETE FROM
事务控制语言（TCL）：Transacation control Language
COMMIT ROLLBACK
数据查询语言（DQL）：Data Query Language
SELECT ..FROM..WHERE
数据控制语言（DCL） : Data Control Language
GRANT REVOKE CREATE USER
ORACLE不区分大小写，但字符串里面区分大小写，单引号自动大写，双引号区分大小写
从一个已知的表创建一个新表 ：create talbe newName
as(select 旧表)
ORACLE操作
NUMBER(P)，NUMBER（P，S）数字形式：P表示数字的最大总位数，S表示小数点后面的最大位数.最大38位
如：NUMBER(6,2)，整数位最大为4位，小数位最大位数是2位，也就是最大取值：9999.99
-
字符串操作：
-
CHAR和VARCHAR2类型
字符串在数据库中存储的默认单位是字节，也可显式指定为字符。CHAR(10)，等价于 CHAR(10 BYTE)，如果指定单位为字符：CHAR(10 CHAR)
英文字符占用一个字节，每个中文字符按编码不同，占用2-4个字节：
ZHS16GBK: 2个字节
UTF-8: 2-4个字节
CHAR存放定长字符，如果数据存不满定长长度，则补齐空格，最大取值为2000字节，如果不指定长度，默认为1个字节
VARCHAR2存放变长字符，实际数据有多少长度则占用多少，最大取值为4000字节，VARCHAR2必须指定长度
LONG类型可以认为是VARCHAR2的加长版，用来存储变长字符串，最多达2GB的字符串数据，但是LONG类型有诸多限制，CLOB用来存储定长或变长字符串，最多达4GB的字符串数据，ORACLE建议开发中使用CLOB替代LONG类型，
-
字符串函数
-
CONCAT和“||”
CONCAT(char1, char2)，返回两个字符串连接，如果任何一个参数是NULL，相当于连接了一个空格。
连接两个以上操作符时并不是很方便。concat的等价操作是连接操作符”||”
-
UPPER、LOWER和INITCAP
-
UPPER(char)用于将字符转换为大写形式
-
LOWER(char)用于将字符转换为小写形式
-
INITCAP(char)用于将字符串中每个单词的首字符大写，其它字符小写，单词之间用空格和非字母字符分隔
输入参数是NULL值，仍然返回NULL值
-
截去子字符串：TRIM、LTRIM、RTRIM
-
TRIM(c2 FROM c1) 表示从c1的前后截 去c2，没有c2表示截去前后的空格
-
LTRIM(c1,c2) 表示从c1的左边截去c2，没有参数c2，就去除空格
-
RTRIM(c1,c2) 表示从c1的右边截去c2，没有参数c2，就去除空格
-
补位函数：LPAD、RPAD
-
LPAD(char1, n, char2) 左补位函数。
-
RPAD(char1, n, char2) 右补位函数
-
在字符串参数char1的左端或右端用char2补足到n位
-
SUBSTR表示在一个字符串中截取子串
SUBSTR（char，m,n）:返回char中从m位开始取n个字符的子串，字符串的首位计数从1开始,如果m = 0，则从首字符开始，如果m取负数，则从尾部开始如果没有设置n，或者n的长度超过了char的长度，则取到字符串末尾为止
-
INSTR:返回在一个字符串中子串的位置
INSTR(char1, char2,n,m):返回子串char2在源字符串char1中的位置initcap
从n的位置开始搜索，没有指定n，从第1个字符开始搜索
m用于指定子串的第m次出现次数，如果不指定取值1
如果在char1中没有找到子串char2 ，返回0
-
数值操作
整数和NUMBER(P)和浮点数NUMBER(P,S)
数值函数
-
ROUND(n,m):用于将参数n按照m的数字要求四舍五入
-
参数中的n可以是任何数字，指要被处理的数字
-
m必须是整数
-
m取正数则四舍五入到小数点后第m位
-
m取0值则四舍五入到整数位
-
m取负数，则四舍五入到小数点前m位
-
m缺省，默认值是0
-
TRUNC(n,m)的功能是截取，其中n和m的定义和ROUND(n[, m])相同，不同的是功能上按照截取的方式处理数字n
-
MOD(m, n)是取模函数，返回m除以n后的余数，如果n为0则直接返回m。
-
CEIL(n)取>=n得最小整数 FLOOR(n)取<=n得最小整数
-
Oracle日期操作
常用日期格式：
-
DATE类型在数据库中的实际存储固定为7个字节，相减是天数，精确到秒，默认格式：DD-MON-RR，“11-APR-71”。如果是中文环境，是“11-4月-71”这种形式。
-
第1字节：世纪+100
-
第2字节：年
-
第3字节：月
-
第4字节：天
-
第5字节：小时+1
-
第6字节：分+1
-
第7字节：秒+1
-
TIMESTAMP(n):表示时间戳,可以保存日期和时间，精确到毫秒,相减是天时分秒，精度为0时，可指定为0-9位，默认6位,用7字节存储，与DATE00功能相同，精度大于0则用11字节存储。
第1字节-第7字节：和DATE相同
第8-11字节：纳秒，采用4个字节存储，内部运算类型为整型
日期关键字
SYSDATE：返回当前的date日期，精确到秒,默认显示日期格式是DD-MON-RR
SYSTIMESTAMP：返回当前系统日期和时间timestamp日期,精确到毫秒
日期转换函数:
TO_DATE(char,char pattern)：字符串 日期
TO_CHAR(date,char pattern): 日期 字符串
注意：转换格式字符串里有汉字或者非符号字符需要用双引号。
|
YY
|
2位的年份
|
DY
|
缩写的周几
|
|
YYYY
|
4位的年份
|
DAY
|
全称的周几
|
|
MM
|
2位的月份
|
HH24
|
24制小时
|
|
MON
|
简称的月份
|
HH12
|
12制小时
|
|
MONTH
|
全称的月份
|
MI
|
显示分钟
|
|
DD
|
2位数字的天
|
SS
|
显示秒数
|
RR和YY的区别：指定的年份是两位数，yy根据系统时间判定，RR根据给定时间的
|
|
0-49
|
50-99
|
|
0-49
|
本世纪
|
上世纪
|
|
50-99
|
下世纪
|
本世纪
|
日期常用函数
LAST_DAY(date)：返回日期date所在月的最后一天的date类型
ADD_MONTHS(date, i)：返回日期date加上i个月后的日期值，如果i是小数， 截取整数再运算，如果i是负数，则获得的是减去i个月后的日期值
MONTHS_BETWEEN(date1, date2)：计算（date1-date2）两个日期值之间间隔了 多少个月，有可能会得到小数，也有可能得到负值。
NEXT_DAY(date, char)：返回date日期数据的下一个周几。在中文环境下，直 接使用”星期三”这种形式，英文环境下，使用”WEDNESDAY”这种英文的 周几。可以直接用数字1-7表示周日-周六。
LEAST(e1,e2,e3...)和GREATEST(e1,e2,e3...) 返回最小最大值
在比较之前，在参数列表中第二个以后的参数会被隐含的转换为第一个参数 的数据类型，所以如果可以转换，则继续比较，如果不能转换将会报错。
EXTRACT(date FROM datetime)：从参数datetime中提取参数date指定的数据
-
NULL操作
插入NULL值：显示插入和隐士插入
更新成NULL。
NULL查询：用is null 和is not null查询
NVL(expr1, expr2)：将NULL转变为非NULL值。如果expr1为NULL，则取值expr2, expr2是非空值。
NVL2(expr1, expr2, expr3)：和NVL函数功能类似，都是将NULL转变为非空值。NVL2用来判断expr1是否为NULL，如果不是NULL，返回expr2，如果是NULL，返回expr3。
别名：一个查询语句中的字段不是表中纯粹的字段而是一个表达式，那么查询出来的结果就是表中的函数或者表达式，可以添加别名，若希望区分大小写，就可以用双引号将其括起。
SQL(基础查询)
基本查询语句
select ...from ...where...;
当查询条件中和数字比较，可以使用单引号引起，也可以不用，当和字符及日期类型的数据比较，则必须用单引号引起。
查询条件
使用>, <, >=, <=, !=, <>, =
AND，OR关键字
使用LIKE条件（模糊查询）
%：表示0到多个字符
_：标识单个字符
使用IN(list)和NOT IN(list)
BETWEEN…AND… 数字 字符 日期都适用
使用IS NULL和IS NOT NULL
ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。
-
> ANY : 大于最小
-
< ANY：小于最大
-
> ALL：大于最大
-
< ALL：小于最小
查询条件中使用表达式和函数
可以在数字列上使用算术表达式(+、-、*、/),算术运算主要是针对数字类型的数据，对日期类型的数据可以做加减操作，表示在一个日期值上加或减一个天数。
使用DISTINCT过滤重复，列可以组合查询，表示组合起来不重复。
排序
Order by[asc][desc] 默认是ASC, 当以多列作为排序标准时，首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推。(NULL值视作最大)
聚合函数
多行数据参与运算返回一行结果
GOURP BY 按什么分组
HAVING 进一步限制分组结果
max min avg sum count : 忽略NULL值。
注意：当一条查询语句中包含所有的子句，执行顺序依下列子句次序：
FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。
join on子句：
WHERE子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE 子句的最右。
GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。
HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。
SELECT子句：少用*号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。
ORDER BY子句：执行顺序为从左到右排序，消耗资源。
SQL（关联查询）
笛卡尔积：当两个表关联查询时，不写连接条件，得到的结果即是笛卡尔积
内连接：内连接返回两个表中所有满足连接条件的数据记录 ,或者 join on()
外连接：不仅返回满足连接条件的记录，还将返回不满足连接条件的记录。(左右全连接) 格式：LEFT/RIGHT/FULL OUTER JOIN...ON
自连接：通过将表用别名虚拟成两个表的方式实现
内、外连接有两种写法
Select aa from a,b where a.x(+)=b.xx 右外连接
Select aa from a,b where a.x=b.xx 等于下条
Select aa from a join b on(a.x=b.xx)
SQL(高级查询)
注：在子查询中函数要起别名
在SELECT查询条件中是来自于另外一个查询的结果。为了给查询提供数据而首先执行的查询语句叫做子查询。
子查询：嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。
也可以出现在having中。
子查询在FROM部分：子查询的结果作为表继续查询，这个子查询也称作行内视图或者匿名视图。
把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式，
也可以是其它类型的语句比如DML或DDL语句。
根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询。
如果子查询返回多行单列，主查询中要使用多行比较操作符，包括NOT IN、IN、ALL、ANY。其中ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。
在子查询中需要引用到主查询的字段数据，使用EXISTS关键字。EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE。如果子查询没有结果，则返回FALSE。
分页查询
定义：当查询的数据量过大时，会导致响应速度慢，资源消耗过大，为此，我们采用分页查询方式，分段将数据查询出来，
ROWNUM：被称作伪列，不存在任何表中，但是所有表都可以查询该字段，该字段的值是动态生成的，在查询表生成结果集的过程中产生值得，从1开始递增，用于返回标识行数据顺序的数字。
理解：select * from (select rownum rn,t.* from (select ename,sal,job from yang order by sal desc) t where rownum<=10) where rn>=5;
-
DECODE函数：DECODE (expr, search1, result1[, search2, result2…][, default])
比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值。default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。
Decode函数相当于case when then when then else end
DECODE函数可以按字段内容分组，DECODE函数也可以按字段内容排序
排序函数
row_number函数：生成组内连续且唯一的数字。
row_number() over(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序。
rank函数：rank有结果有重复值，而row_number没有
Rank() over(partition by col1 order by col2) : 表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名。
Dense_rank函数:
Dense_rank() over(partition by col1 order by col2)
根据col1分组，分组内部根据col2排名，相同的数据返回相同排名。特点是连续排序，如果有并列第二，下一个排序将是三。
高级分组函数
rollup、cube 和 grouping sets 可以生成与使用union all来组合单个分组查询时相同的结果集，
Group by rollup(a, b, c)
从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组(也就是全表分组)。对于n个参数的ROLLUP，有n+1次分组。
Group by cube(a, b, c)
对于n个参数的cube，有2^n次分组。如果GROUP BY CUBE(a,b,c)，首先对(a,b,c)进行group by，然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，最后对全表进行group by操作，
Group by GROUPING SETS ( (a), (b))
每个参数就是一种分组方式，然后会将这些分组统计的结果并到一个结果中显示。
集合操作：
集合操作符包括union、union all、intersect（交集）和minus(差集)
Union和union all区别：
Union操作符会自动去掉合并后的重复记录，并按照第一列进行排序。
Union all返回两个结果集中的所有行，包括重复的行。
Union操作符对查询结果排序，union all不排序。
使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。
一、sql执行顺序
(1)from
(3) join
(2) on
(4) where
(5)group by(使用select中的别名)
(6) avg,sum....
(7)having
(8) select
(9) distinct
(10) order by
16视图(VIEW)
视图(VIEW)也被称作虚表，,其本质是对应于一条SELECT语句。
定义：create [or replace] view view_name as （select语句）
[with check option][with read only]
简单视图
复杂视图
简单视图能够执行DML操作，下列情况除外：
在基表中定义了非空列，但简单视图对应的SELECT语句并没有包含这个非空列，导致这个非空列对视图不可见，这时无法对视图执行INSERT操作；
如果视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUM伪列，不允许执行DML操作。
DML操作不能违反基表的约束条件。
视图可以select操作、drop操作
序列(SEQUENCE)
序列(SEQUENCE)：一种用来生成唯一数字值的数据库对象，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值
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
序列中有两个伪列，不能回退：
NEXTVAL：获取序列的下个值
CURRVAL：获取序列的当前值
主键生成的另一种方式：UUID（32位不重复的字符串）
Select sys_guid from dual
索引(INDEX)
索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）中。索引记录中存有索引关键字和指向表中数据的指针（地址）。
-
基于单列或多列或函数：create [UNIQUE] INDEX index_name
ON table(column[, column…]);
ROWID: 伪列，唯一标识一条数据记录，可理解为行地址。
在索引列上执行DML操作，需要定期重建索引：alter index index_name rebuild
删除索引： drop index index_name;
为提升查询效率，创建和使用索引的原则：
- 为经常出现在WHERE子句中的列创建索引
-
为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
-
为经常作为表的连接条件的列上创建索引
-
不要在经常做DML操作的表上建立索引
-
不要在小表上建立索引
-
限制表上的索引数目，索引并不是越多越好
-
删除很少被使用的、不合理的索引
ORACLE完整性约束
一、维护数据的完整性
概述：数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在Oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。
1、约束
约束用于确保数据库数据满足特定的商业规则。在Oracle中，约束包括：not null、unique、primary key， foreign key和check五种。
A、not null（非空）
如果在列上定义了not null，那么当插入数据时，必须为列提供数据。
B、unique（唯一）
当定义了唯一约束后，该列值是不能重复的，但是可以为null。
C、primary key（主键）
用于唯一的标识表行的数据，当定义主键约束后，该列不但不能重复而且不能为NULL。一张表最多只能有一个主键，但是可以由多个unique约束。
D、foreign key（外键）
用于定义主表和从表之间的关系，外键约束要定义在从表上，主要则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为NULL。
E、check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000～2000之间，如果不在1000～2000之间就会提示出错。
综合案例：
商品售货系统表设计案例
现在有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：
商品表Goods（商品号GoodsId，商品名GoodName，单价UnitPrice，商品类别Categroy，供应商Provider）
客户表Customers（客户号CustomerId，姓名Name，住址Address，电邮Email，性别Gender，身份证CardId）
销售表Purchases（客户号CustomerId，商品号GoodsId，购买数量Num）
请用SQL语言完成下列功能：
建表，在定义中要求声明：
（1）每个表的主外键。
（2）客户的姓名不能为空值。
（3）单价必须大于0，购买数量必须在1～30之间。
（4）电邮不能够重复。
（5）客户的性别必须是男或女，默认是男。
sql>create table Goods(
GoodsId char(8) primary key ,--主键
GoodName varchar2(50),
UnitPrice number(10,2) check(UnitPrice>0),--单价必须大于0
Category varchar(30),
Provider varchar(100));
sql>create table Customers(
CustomerId char(8) primary key,
Name varchar2(30) not null, --姓名不能为空
Address varchar2(150),
Email varchar2(100) unique,--必须唯一
Gender char(2) default(‘男‘) check(Gender in(‘男‘,‘女‘)),
CardId char(18));
sql>create table Purchases(
CustomerId char(8) references Customers(CustomerId), --外键
GoodsId char(8) references Goods(GoodsId),
Num number(10) check(Num between 1 and 30)));
2、添加约束
如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束，但是要注意：增加not null约束时，需要使用modify选项，而增加其他四种约束使用add选项。
（1）增加商品名也不能为空
sql>alter table Goods modify GoodsId not null;
（2）增加身份证也不能重复
sql>alter table Customers add constraint UQ_CardId unique(CardId);
（3）增加客户的住址只能是海淀、朝阳、东城、西域
sql>alter table Customers add constraint CK_Address check(Address in (‘海淀‘,‘朝阳‘,‘东城‘,‘西域‘));
3、删除约束
当不再需要某个约束时，可以删除。
sql>alter table 表名 drop constraint 约束名称;
特别说明：
在删除主键约束的时候，可能有错误。比如
sql> alter table 表名 drop primary key;
这是因为如果在两表存在主从关系，那么在删除主表主键约束时，必须带上cascade选项。
sql> alter table 表名 drop primary key cascade;
4、显示约束信息
A、显示约束信息
通过查询数据字典视图user_constraints，可以显示当前用户所有的约束的信息。
sql>select constraint_name,constraint_type,status,validated from user_constraints where table_name=‘Goods‘;
B、显示列约束
通过查询数据字典视图user_cons_column，可以显示约束所对应的表列信息。
sql>select column_name,position from user_cons_columns where constraint_name=‘CK_Address‘;
6、表级定义和列级定义
A、表级定义
表级定义是指在定义了所有列后，再定义约束，这里需要注意，not null约束只能在列级上定义。
案例：
sql> create table Goods(
GoodsId char(8),GoodsName varchar2(50), Category varchar(30),
constraint PK_GoodsId primary key(GoodsId));
B、列级定义
列级定义是在定义列的同时定义约束。
案例：
sql>create table Goods(
GoodsId char(8) constraint PK_GoodsId primary key ,--主键
GoodName varchar2(50),
UnitPrice number(10,2) check(UnitPrice>0),--单价必须大于0
Category varchar(30),
Provider varchar(100));