Oracle学习整理

用 startup 命令启动数据库，用 shutdown 命令关闭数据库

Oracle SQL 语句由如下命令组成： 

　　数据定义语言（DDL），包括 CREATE （创建）命令、ALTER （修改）命令、DROP（删除）命令等。 

　　数据操纵语言（DML），包括 INSERT（插入）命令、UPDATE（更新）命令、DELETE （删除）命令、SELECT … FOR UPDATE（查询）等。 

　　数据查询语言（DQL），包括基本查询语句、Order By 子句、Group By 子句等。 

　　事务控制语言（TCL），包括 COMMIT（提交）命令、SAVEPOINT（保存点）命令、 ROLLBACK（回滚）命令。 

　　数据控制语言（DCL），GRANT（授权）命令、REVOKE（撤销）命令。

1、创建用户

CREATE USER  用户名  
IDENTIFIED BY  口令  
[ACCOUNT LOCK|UNLOCK]

LOCK|UNLOCK 创建用户时是否锁定，默认为锁定状态。锁定的用户无法正常的登录进行数据库操作。

2、授权（CONNECT、RESOURCE、DBA）

GRANT角色|权限 TO 用户（角色）

GRANT CONNECT TO jerry; 授权成功。
GRANT RESOURCE TO jerry; 授权成功。

//回收权限

REVOKE 角色|权限 FROM 用户（角色）

//修改用户的密码

ALTER USER 用户名 IDENTIFIED BY 新密码

//修改用户处于锁定（非锁定）状态

ALTER USER 用户名 ACCOUNT LOCK|UNLOCK

3、创建约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名约束内容。

ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID) ；-- 主键约束
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女')；--check 约束
ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME) ；--唯一约束

根据结果集创建表： CREATE TABLE INFOS1 AS SELECT * FROM INFOS;

集合运算就是将两个或者多个结果集组合成为一个结果集。

集合运算包括： 

INTERSECT(交集)，返回两个查询共有的记录。 

UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。 

UNION(并集)，返回各个查询的所有记录，不包括重复记录。 

MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

表连接

外连接：不完全匹配

左连接： FROM e JOIN d ON e.id = d.id(+) 左边数据全部显示，右边匹配不上的部分用空值代替

右连接： FROM e JOIN d ON e.id(+) = d.id

select a.latn, a.guanz, nvl(b.bangd,0)
  from (select nvl(a.latn, '99999') latn, count(1) guanz
          from wx_run_user_state a
         group by a.latn) a
  left join (select nvl(a.latn, '99999') latn, count(1) bangd
               from wx_run_user_state a
              where a.bind_type = 2
              group by a.latn) b
    on a.latn = b.latn

函数：

1、DECODE DECODE(AA﹐V1﹐R1﹐V2﹐R2，。。。)函数

解释: IF AA=V1 THEN RETURN R1 IF AA=V2 THEN RETURN R2 ..… ELSE RETURN NULL

2. ROUND(X[,Y])，四舍五入。在缺省 y 时，默认 y=0；

比如：ROUND(3.56)=4。 y 是正整数，就是四舍五入到小数点后 y 位。

ROUND(5.654,2)=5.65。 y 是负整数，四舍五入到小数点左边|y|位。

ROUND(351.654,-2)=400。 3.

TRUNC(x[,y])，直接截取，不四舍五入。在缺省 y 时，默认 y=0；

比如：TRUNC (3.56)=3。 y 是正整数，就是四舍五入到小数点后 y 位。

TRUNC (5.654,2)=5.65。

TRUNC (351.654,-2)=300。y 是负整数，四舍五入到小数点左边|y|位。

4、截串 SUBSTR(表达式，位置，长度)

select substr('asdfghjkl',1,4) from dual; --asdf

select SUBSTR('abcdefg', LENGTH('abcdefg')-3+1, 3) from dual; --efg

SUM(DECODE(SEX, '男', 1, 0)) 筛选出行被为男的记录并加1

SUM(DECODE(SEX, '女', 1, 0)) 筛选出行被为女的记录并加1

5、日期函数

1. ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。 d 表示日期，n 表示要加的月数。

select sysdate,ADD_MONTHS(sysdate,2) from dual;

2、LAST_DAY(d)，返回指定日期当月的最后一天。

select sysdate,LAST_DAY(sysdate) from dual;

3、ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式模型。默认 fmt 为 DDD，即月中的某一天。

select sysdate,ROUND(sysdate),ROUND(SYSDATE,'YEAR'),ROUND(SYSDATE,'MONTH'),ROUND(SYSDATE,'DDD'),ROUND(SYSDATE,'DAY') from dual; 
select sysdate,TRUNC(sysdate),TRUNC(SYSDATE,'YEAR'),TRUNC(SYSDATE,'MONTH'),TRUNC(SYSDATE,'DDD'),TRUNC(SYSDATE,'DAY') from dual;

4. EXTRACT(fmt FROM d)，提取日期中的特定部分。

SELECT SYSDATE "DATE",
       EXTRACT(YEAR FROM SYSDATE) "YEAR",
       EXTRACT(MONTH FROM SYSDATE) "MONTH",
       EXTRACT(DAY FROM SYSDATE) "DAY",
       EXTRACT(HOUR FROM SYSTIMESTAMP) + 8 "HOUR",
       EXTRACT(MINUTE FROM SYSTIMESTAMP) "MINUTE",
       EXTRACT(SECOND FROM SYSTIMESTAMP) "SECOND"
  FROM DUAL;

5.NVL2

NVL2(x,value1,value2) 如果 x 非空，返回 value1，否则返回 value2

SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,NVL2(E.COMM,e.comm+100,200),E.DEPTNO FROM EMP E WHERE E.SAL<2000;

6、同义词（Synonym）

CREATE [OR REPLACE] [PUBLIC] SYSNONYM [schema.]synonym_name FOR [schema.]object_name

GRANT CREATE SYNONYM TO XiaoMei;

CREATE SYNONYM MyEmp FOR SCOTT.EMP;

7、序列序列(Sequence)是用来生成连续的整数数据的对象

CREATE SEQUENCE sequence_name
[START WITH num]
[INCREMENT BY increment]
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]

解释：

START WITH：从某一个整数开始，升序默认值是 1，降序默认值是-1。

INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默认值是 1，降序默认值是-1。

MAXVALUE：指最大值。

NOMAXVALUE：这是最大值的默认选项，升序的最大值是：10^27，降序默认值是-1。

MINVALUE：指最小值。

NOMINVALUE：这是默认值选项，升序默认值是 1，降序默认值是-10^26。

CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始。

NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认 NOCYCLE。

CACHE：使用 CACHE 选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。 Oracle 默认会生产 20 个序列号。

NOCACHE：不预先在内存中生成序列号。

8、视图：

视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的 SELECT…FROM 即可。

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查询
[WITH READ ONLY CONSTRAINT]