Oracle数据库学习

2016-07-16 本文已影响86人 tenlee

Select/条件和排序/单行函数（7.14）

条件查找

去重
SELECT DISTINCT department_id FROM employees; 使用DISTINCT 消除重复结果行
LIKE
- escape可以转义任意字符为转义字符,可以指定
- % >=个字符
- _ 一个字符

between...and...包含区间

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

in 在集合范围内
<> 不等于
not is not, not in, not like

排序

order by asc升序, desc倒序

单行函数

大小写转换函数
lower
upper
initcap
字符串操作函数

image
concat 字符串连接和||差不多
substr

length

instr

lpad 向左补齐

rpad

trim 去空格
lower, upper, initcap, replace
数字

round(45.923, 2)四舍五入,保留两位小数

trunc 全舍

mod(12, 2) 取模

保留几位小数, 正数和负数的作用
日期

函数	结果
`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)`	ORA-01861: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-95

to_char用法

Select to_char(sysdate,'ss') from dual取当前时间秒部分

Select to_char(sysdate,'mi') from dual取当前时间分钟部分

Select to_char(sysdate,'HH24') from dual取当前时间秒小时部分

Select to_char(sysdate,'DD') from dual取当前时间日期部分

Select to_char(sysdate,'MM') from dual取当前时间月部分

Select to_char(sysdate,'YYYY') from dual取当前时间年部分

Select to_char(sysdate,'w') from dual取当前时间是一个月中的第几周(从1日开始算)

Select to_char(sysdate,'ww') from dual取当前时间是一年中的第几周（从1.1开始算）

Select to_char(sysdate,'iw') from dual取当前时间是一年中的第几周（按实际日历的）

Select to_char(sysdate,'d') from dual取当前时间是一周的第几天，从星期天开始，周六结束
Select to_char(sysdate,'day') from dual　取当前日是星期几，和数据库设置的字符集有关，会输出'Tuesday'

Select to_char(sysdate,'ddd') from dual 当前日是一年中的第几天

其他

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,直到找到

条件表达式case

SELECT last_name, job_id, salary,
CASE job_id
WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary
END "REVISED_SALARY"
FROM employees;

或者

SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary) REVISED_SALARY
FROM employees;

一个加 as和不加, concat 和 ||

多表关联/分组计算和group by/子查询/DML语句/事务控制/锁（7.15）

2 、SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时，其他字段必须出现在Group By 子
句中，否则不合法
3 、不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。
单行比较必须对应单行子查询（返回单一结果值的查询）；比如= ， >
多行比较必须对应多行子查询（返回一个数据集合的查询）；比如 IN , > ANY, > ALL

使用子查询作为插入

INSERT INTO (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM employees
WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000);

WITH CHECK OPTION 可以检查要插入的内容，是否符合目标子查询的WhereWITH CHECK OPTION 可以检查要插入的内容，是否符合目标子查询的Where

如果遇到这种需求，也可以使用TRUNCATE 语句，，TRUNCATE TABLE copy_emp ,
但要注意，TRUNCATE 语句无法回滚，因此除非是单独执行，并非常确认，否则慎用

DML语句

insert into

INSERT INTO table [(column [, column...])] VALUES (value [, value...]);
update

UPDATE table SET column = value [, column = value, ...] [WHERE condition];
delete

DELETE [FROM] table [WHERE condition];

merge语句
MERGE 语句：比较整合语句，语法：

MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);

事务控制

当如下事件发生是，会隐式的执行Commit 动作：
1 、数据定义语句被执行的时候，比如新建一张表：Create Table …
2 、数据控制语句被执行的时候，比如赋权 GRANT …( 或者 DENY)
3 、正常退出 iSQL*Plus 或者PLSQL DEVELOPER, 而没有显式的执行 COMMIT 或者 ROLLBACK 语句
在在Commit 或者 Rollback 前后数据的状态：
1 、在数据已经被更改，但没有Commit 前，被更改记录处于被锁定状态，其他用户无法进行更改；
2、、在数据已经被更改，但没有Commit 前，只有当前Session 的用户可以看到这种变更，其他Session 的用户
。看不到数据的变化。
3、、在数据已经被更改，并且被Commit 后，被更改记录自动解锁，其他用户可以进行更改；；
4、、在数据已经被更改，被并且被Commit后后，其他Session的的用户再次访问这些数据时，看到的是变化后的数据
锁
更新一张表之前呢,先用select ... for update nowait查看此表是否已经被锁

表/约束/视图/序列&索引&同义词/集合操作/用户权限控制（7.16）

更改表结构

添加列

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 oldtablename to newtableName;

一次性清空表TRUNCATE TABLE tableName;

约束

添加约束

ALTER TABLE table ADD CONSTRAINT 约束名 primary key();
删除约束

alter table t_table drop constraint pri;

`TOP-N`

SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N;

索引

在什么样的情况下创建索引对加快查询有利呢：

答：查询条件中使用到这个列（或者这个列于其他列的组合），且这个列（或者与其他列的组合）上的数字
量范围跨度很大，而大多数情况下我们要获取的数据的量占整个表的数据总量小于4% ；
在什么样的情况下不适合创建索引呢：
答: 1 ）被查询的表本身就很小，即是是全表扫描也非常快；；或者基于这张表的查询，大多数情况下需要获取的数据量都超过了总量的4%；或者这张表需要频繁的被更新，建立索引的话会引起索引的频繁更新，从而反而
降低数据库的整体效率。
模糊查询的时候%放在前面,是不会用到索引的.
字段类型要匹配,不然不会用到索引
所有的索引字段使用的时候不能加任何函数,建议用函数索引.

集合操作

并集,union

去除重复项,union all保留重复项

select employee_id, job_id
from employees
union
select employee_id, job_id
from job_history;

交集, intersect
差集, minus

Group By 子句增强/子查询进阶/递归查询/INSERT增强(7.17)

group by增强

rollup
Rollup 后面跟了n 个字段，就将进行n+1 次分组，从右到左每次减少一个字段进行分组；然后进行
union
```
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);
```
第一次根据(department_id, job_id)进行分组,第二次根据(department_id)进行分组,第三次是总体的汇总
cube

cube像rollup一样,从右向左减少一个字段进行分组,还会从左往右减少一个字段分组,最后在汇总,即2^N次分组运算
grouping

GROUPING 函数：Rollup 和 Cube 有点抽象，他分别相当于n+1 和 2 的n 次方常规 Group by 运运
算；那么在Rollup 和 Cube 的结果集中如何很明确的看出哪些行是针对那些列或者列的组合进行
？分组运算的结果的？答案是可以使用Grouping 函数；没有被Grouping 到返回1
group sets

代替多次的union

子查询进阶

非相关子查询当做一张表使用
exists比in效率高

注意：Not In 里面只要有一个NULL ，就不成立了，这是很容易出错的地方；正确的方法请在后
面的子查询中加上where department_id is not null;
exists不返回数据,只返回True或者False
with

递归查询

SELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;

举例：查询从King 开始，从上往下的各级员工。

SELECT last_name||' reports to '||
PRIOR last_name "Walk Top Down"
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id ;

lpad 向左补齐, level是等级

insert增强

一个来源插入多个目标表（无条件）。
一个来源插入多个目标表（有条件）
一个来源插入多个目标表（有条件，首次匹配即跳到下一条）
列转行（一行变多行，交叉报表的反操作）

SQL进阶功能(7.19)

over

分析函数提供一系列比较高级的SQL 功能。分析函数时建立在数据窗口（over 在一定的数据库范
围进行数据分析），在一定的数据范围进行排序、汇总等
例如 over(parttion by .....)
rank, dense_rank, row_number

rank 1 2 2 4 相同的会跳过编号
dense_rank 1 2 2 3 相同的不跳过编号
row_number 1 2 3 4 一直编号
闪回功能

SELECT * FROM departments AS OF TIMESTAMP SYSDATE-5/(24*60)

全局临时表
- 基于会话
  CREATE GLOBAL TEMPORARY TABLE temp_table_session (...) ON COMMIT PRESERVE ROWS;
  基于会话的临时表，在会话断开之前，或者通过一个delete 或truncate 物理地删除行之前，
  这些行会一直存在于这个临时表中。只有我的会话能看到这些行，即使我已经提交，其他会话也无法看到我的行。
- 基于事务
  CREATE GLOBAL TEMPORARY TABLE temp_table_session (...) ON COMMIT DELETE ROWS;
  基于事务的临时表，我的会话提交时，临时表中的行就不见了。只需把分配给这个表的临时区段交回，
  这些行就会消失，在临时表的自动清除过程中不存在开销。
length 是字符个数，lengthb 指的是字节数。。
substr值是按字来取值，，substrb 是按字节来取值
汉字。字符对应多少字节，和数据库的字符集设置有关系