oracle sql 知识小结
Oracle_sql ：
第一单元：select 语句：
①：字符串连接操作符： ||
②：去除重复行：distinct
第二单元：条件限制和排序
①：关键字：where
②：比较操作符：=，>,<,between...and...,in,like,is null
:使用like做模糊匹配：可使用%或者_作为通配符；%代表0个或多个字符。_代表一个单个字符。escape为标识转义字符 escape '/'。
③：排序：order by asc（升序默认）/desc（倒序）
第三单元：单行函数
①：大小写转换：lower（） A——a
upper（）a——A
initcap（）first——First
②：字符串操作函数
函数 结果
CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld', 'W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary, 10, '*') 24000*****
TRIM('H' FROM 'HelloWorld')elloWorld
TRIM(' HelloWorld') HelloWorld
TRIM('Hello World') Hello World
③：数字操作函数：
函数 结果
ROUND(45.926, 2) 45.93
TRUNC(45.926, 2) 45.92
MOD(1600, 300) 10
④：日期操作函数：
函数 结果
MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194
ADD_MONTHS ('11-JAN-94',6) 11-Jul-94
NEXT_DAY ('01-SEP-95','FRIDAY') 8-Sep-95
NEXT_DAY ('01-SEP-95',1) 3-Sep-95
NEXT_DAY ('1995-09-01',1) ORA01861:literal does not match format string
NEXT_DAY (to_date('1995-09-01','YYYY-MM-DD'),1) 3-Sep-95
LAST_DAY('01-FEB-95') 28-Feb-95
ROUND('25-JUL-95','MONTH') 1-Aug-95
ROUND('25-JUL-95' ,'YEAR') 1-Jan-96
TRUNC('25-JUL-95' ,'MONTH') 1-Jul-95
TRUNC('25-JUL-95','YEAR') 1-Jan-9
⑤：不同类型的数据转换函数：
Oracle数据类型的隐私转换规则：
对于赋值操作可以： 从到
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR
对于表达式比较操作仅可以：
从到 VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
TO_CHAR() 函数：日期到字符串的转换
TO_CHAR(date, 'format_model') ;
日期格式化元素意义
YYYY 4位数字表示的年份
YEAR 英文描述的年份
MM 2位数字表示的月份
MONTH 英文描述的月份
MON 三个字母的英文描述月份简称
DD 2位数字表示的日期
DAY 英文描述的星期几
DY 三个字母的英文描述的星期几简称
HH24:MI:SS AM 时分秒的格式化
DDspth 英文描述的月中第几天
fm 格式化关键字，可选
TO_CHAR() 函数：数字到字符串的转换
TO_CHAR(number, 'format_model') ;
数字格式化元素意义
9表示一个数字
0强制显示0
$放一个美元占位符
L使用浮点本地币种符号
.显示一个小数点占位符
,显示一个千分位占位符
TO_NUMBER() 函数：字符串到数字的转换
TO_NUMBER(char[, 'format_model']) ;
TO_DATE() 函数：字符串到日期的转换
TO_DATE(char[, 'format_model'])
其他常用单行函数
函数用途
NVL (expr1, expr2)如果expr1为空，这返回expr2
NVL2 (expr1, expr2, expr3)如果expr1为空，这返回expr3（第2个结果）否则返回expr2
NULLIF (expr1, expr2)如果expr1和expr2相等，则返回空
COALESCE (expr1, expr2, ..., exprn)
如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到 一个不为NULL的值或者如果全部为NULL，也只能返回NULL
条件表达式：decode函数：
DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default])
第四单元：多表关联查询：1，等于链结
2，不等链结。3，外链接（左链结，右链结）。4，自联结
第五单元：分组计算函数和group by句子
分组计算函数：相对于单行函数，也可称之为多行函数，它的输入是多个行构成得一个行集（这个行集可以是 一张表的所有行，也可以是按照某个维度进行分组后的某一组行），而输出都是一个值；
分组计算函数(常用)：包括
1、求和(SUM)
2、求平均值(AVG)
3、计数（COUNT）
4、求标准差(STDDEV)
5、求方差(VARIANCE)
6、求大值(MAX)
7、求小值(MIN)
8、COUNT 函数说明:
函数用法意义
COUNT(*) 返回满足选择条件的所有行的行数，包括值为空的行和重复的行
COUNT(expr)返回满足选择条件的且表达式不为空行数。
COUNT(DISTINCT expr)返回满足选择条件的且表达式不为空，且不重复的行数。
9、group by
SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时，其他字段必须出现在Group By子 句中，否则不合法。
不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句
第六单元：子查询
SELECTselect_list FROMtable WHEREexproperator
(SELECTselect_list FROM table);
第七单元：dml语句
DML: Data Manipulation Language , 数据操纵语言；简单的说就是SQL中的增、删、改等语句
INSERT INTO table [(column [, column...])] VALUES(value [, value...]);
UPDATE table SETcolumn= value[, column = value, ...] [WHERE condition];
DELETE [FROM]table [WHEREcondition];
第九章：数据库对象-表
规则要求：表的命名要求和表中列的命名要求：
1、必须以字母开头
2、长度不能超过30个字符
3、只能包含A–Z, a–z, 0–9, _, $, and #
4、不能与数据库中的已有对象重名
5、不能使用Oracle 数据库的保留
数据类型描述
VARCHAR2(size)可变长字符串
CHAR(size) 定长字符串
NUMBER(p,s) 可变长数值
DATE 日期时间
LONG 可变长大字符串，大可到2G
CLOB可变长大字符串数据，大可到4G
RAW and LONG RAW 二进制数据
BLOB大二进制数据，大可到4G BFILE存储于外部文件的二进制数据，大可到4G
ROWID64进制18位长度的数据，用以标识行的地址
TIMESTAMP精确到分秒级的日期类型（9i以后提供的增强数据类型）
INTERVAL YEAR TO MONTH表示几年几个月的间隔（9i以后提供的增强数据类型-极其少见）
INTERVAL DAY TO SECOND表示几天几小时几分几秒的间隔（9i以后提供的增强数据类型-极其 少见）
更改表的语法：
添加列：
ALTER TABLE table
ADD(column datatype[DEFAULT expr] [, column datatype]...);
更改列：
ALTER TABLE table
MODIFY(column datatype[DEFAULT expr] [, column datatype]...);
删除列：
ALTER TABLE table
DROP(column);
删除表：
DROP TABLE tableName;
更改表名：
RENAME oldtablenameto newtableName；
第十单元：数据库对象-约束
约束的概念：Oracle 数据库使用“约束”来阻止对数据库表中数据的不合法的“增删改”动作。
常用的约束有如下几种：
NOT NULL （非空约束）
UNIQUE （唯一性约束）
PRIMARY KEY （主键约束）
FOREIGN KEY （外键约束）
CHECK （自定义约束)
单独创建约束语法：
ALTER TABLE tablename
ADD CONSTRAINT constraintname constrainttype(column1,…);
删除约束：
ALTER TABLE tablename
DROP CONSTRAINT constraintname;
失效/生效约束：
ALTER TABLE tablename
DISABLE CONSTRAINT constraintname CASCADE;
ALTER TABLE tablename
ENABLE CONSTRAINT constraintname;
查询系统中存在哪些约束:
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
第十一单元：数据库对象-视图
视图的概念：
有的时候我们需要关联多张表获得一个查询结果集，有的时候我们需要写很复杂的条件得到一个想要的结果集， 我们不想每次要想这些数据的时候都重新去写很复杂的SQL语句，怎么办？
我们可以把这些结果集创建为视图-View
视图创建的语法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]]
删除视图：
drop view viewname;
第十二章：数据库对象-序列、索引、同义词
序列的概念：
有的时候我们定义某一张表中某一列为主键，当我们往表中插入数据的时候，对于主键字段的赋值要求唯一性， 我们希望能有个自增长类型的数据库对象，我们每获取一次，它自动增长，保证下次获取时肯定是不一样的值， 这样我们就方便了，Oracle 数据库提供“序列”这种对象来满足我们的要求
序列的创建：
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n| NOCACHE}];
更改序列定义：
ALTER SEQUENCE sequence
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
删除序列：
DROP SEQUENCE sequence;
索引创建举例：
CREATE INDEX emp_last_name_idx
ON employees(last_name);
同义词的概念：
当数据库用户A要访问数据库用户B中的一张表Table1的时候，需要加前缀
Select * from B.table1
但我们要通过DB-LINK访问另一个数据库中的某张表的时候我们需要加@后缀
Select * from table1@db-link-name
为了在程序中能够简化写法，Oracle 提供同义词，也就是可以在A用户下建立一个同义词指向B用户下的 Table1, 以后访问的时候可以直接访问这个同义词，而不用加前缀了
语法：CREATE SYNONYM Table1 for B.Table1
第十三单元：控制用户权限
语法：
GRANT object_priv [(columns)]
ON object T
O{user|role|PUBLIC}
[WITH GRANT OPTION] （传递权限）
收回权限：
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
UNION
UNION ALL 保留重复记录
INTERSECT 取交集
MINUS 取差集
SELECT employee_id,job_id
FROM employees
MINUS
SELECT employee_id,job_id FROM job_history;
第十四单元：group by 句子增强
①：在Group By 中使用Rollup 产生常规分组汇总行以及分组小计：
1、常规分组行；2，3 、分层小计行； Rollup 后面跟了n个字段，就将进行n+1次分组，从左到右每次减少一个字段进行分组；然后进行 union
②：在Group By 中使用Cube产生Rollup结果集+ 多维度的交叉表数据源：
