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]