Oracle学习笔记

1.SQL语言本身可以分为四类，即：
a.DDL：定义要在数据库存储哪些信息的数据定义语言，包括对数据库中对象的创建、修改、删除的操作；
b.DML：对数据库中的表进行操作的数据操纵语言，包括对数据库中的数据进行增加、删除、修改的操作；
c.DQL：对数据库的表进行检索的数据查询语言；
d.DCL：对数据库中对象进行权限管理的数据控制语言，进行权限设置和取消。

2.常用数据类型，主要分为四类：字符型、数据型、日期类型和其他类型。
a.字符型：

数据类型             取值范围（字节）           说明
varchar2              0-4000                可变长度的字符串
nvarchar2             0-1000                用来存储Unicode字符集的变长字符串数据
char                  0-2000                用来描述定长的字符型数据
nchar                 0-1000                用来存储Unicode字符集的定长字符型数据
long                  0-2GB                 用来存储变长的字符串

b.数字型：
number(p,s)： p最大精度是38位(十进制) p代表的是精度，s代表保留小数的位数
float ： 用来存储126位数据(二进制)

c.日期类型：
date 用来存储日期和时间
timestamp 与上面相比，更加精确，且可以显示上下午
d.其他数据类型

3.数据定义语言DDL：

a.CREATE:

CREATE TABLE table_name
     (
       column-name datatype  [null|not null]，//允许该列为空或不允许该列为空，在创建表时默认为不允许该列为空
       column-name datatype  [null|not null]，
       ......
       [constraint]//约束设置
      )
例如: 

CREATE TABLE productinfo
          (ProductId  varchar2(10),
           ProductName varchar2(20),
           ProductPrice number(8,2)
           );

b.ALTER:

ALTER TABLE table_name
      ADD column_name |MODIFY column_name |DROP column_name;



  例如1：ALTER TABLE scott.productinfo//这里登陆SQL*PLUS使用的用户是sys，所以在表名前面添加scott，如果使用scott用户登录就不需要了
          add remark varchar2(20);//向表productinfo添加一列remark，数据类型为varchar2
    例如2：ALTER TABLE scott.productinfo
           MODIFY remark number(8,2)|DROP COLUMN remark;

c.DROP

 DROP TABLE table_name;
    例如：DROP TABLE productinfo;

4.约束的使用：
约束包括主键约束、外键约束、唯一约束、检查约束、非空约束。
a.主键约束:一个表中只能有一个。
①创建表时使用：

 CREATE TABLE productinfo
  (productinfo varchar2(10),
   primary key(productinfo));
②使用ALTER TABLE语句为表添加主键约束：


ALTER TABLE table_name
  ADD CONSTRAINTS cnstraint_name PRIMARY KEY(column_name);
例如：
    ALTER TABLE productinfo
    ADD CONSTRAINTS pk_productinfo PRIMARY KEY(ProductId);

③移除主键约束：

 ALTER TABLE table_name
   DROP CONSTRAINT constraint_name;

b.外键约束：是两个表之间两列间建立连接并进行约束，保证数据的完整性。
书上定义为:外键约束可以保证使用外键约束的数据库列与所引用的主键约束的数据列一致。
①创建表时使用外键约束：
在创建表的语句后面加上如下语句，

CONSTRAINT constraint_name FOREIGN KEY(column_name)
     REFERENCE table_name(column_name)   //要引用的表名、列名
     ON DELETE CASCADE;//设置级联删除，当主键的字段被删除时，外键所对应的字段也被同时删除



例如：CREATE TABLE PRODUCTINFO1
         (productid varchar2(100）,
          productname varchar2(200),
          showtime number(8,2),
          catagory varchar2(10),
          PRIMARY KEY(productinfo),
          CONSTRAINT pk_pro FOREIGN KEY(catagory)
          REFERENCE productinfo(productid)
          ON DELETE CASCADE);

②使用ALTER添加外键约束：

ALTER TABLE table-name
     ADD CONSTRAINT constraint_name FORGIGN KEY(column_name)
     REFERENCES table_name(column_name)
     ON DELETE CASCADE;

③移除外键约束：

  ALTER TABLE productinfo 
      DROP CONSTRAINT fk_pro;

c.检查约束(CHECK)：能够规定每一列能够输入的值，以保证数据的正确性。
①创建表时添加CHECK约束：
在创建表的语句后面加上如下语句:

CONSTRAINT constraint_name CHECK(condition);//condition为约束条件
    例如：CREATE TABLE customerinfo
          (CustomerId varchar2(10),
           Name varchar2(10),
           Age number(2),
           Gender varchar2(2),
           CONSTRAINT CHE_AGE CHECK(AGE>=18 AND AGE<=50));

②使用ALTER语句添加CHECK：

 ALTER TABLE table_name 
     ADD CONSTRAINT constraint_name CHECK(condition);
    例如:设置性别输入只能为“男”或“女”
        ALTER TABLE customerinfo
        ADD CONSTRAINT chk_gender CHECK(Gender=‘男’or Gender=‘女’);

③移除CHECK约束：

 ALTER TABLE customerinfo
      DROP CONSTRAINT chk_gender;

d.唯一性约束(UNIQUE)：可以设置表中输入的字段值都是唯一的，和主键约束很像，但唯一性约束一张表中可以有多个。
①在创建表时添加UNIQUE约束：
在创建表的语句后面加上下面的语句：

 CONSTRAINT constraint_name UNIQUE(column_name);

②在创建表时添加UNIQUE约束：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE(column_name);

③移除UNIQUE约束：

 ALTER TABLE table_name
 DROP CONSTRAINT unq_customid;

e.非空约束（NOT NULL）：
①创建非空约束
即：在创建表的时候，在列名后面添加 NOT NULL即可
②修改表示设置NOT NULL约束：

ALTER TABLE table_name
MODIFY column NOT NULL;

对于非空约束不需要删除，如果要取消非空约束，直接使用MODIFY语句把该列非空约束改成NULL即可。

5.数据操纵语言DML和数据查询语言DQL：
a.INSERT语句：
①直接添加数据：

  INSERT INTO table_name(column_name1,column_name2......) VALUES(data1,data2......);

②通过其他数据库向表中添加数据：

INSERT INTO table_name1 (column_name1,column_name2......) SELECT column_name1,column_name2......FROM table_name2;

注意：在使用来源表向目标表中插入数据时，一定要确保两个表的列的个数和列的数据类型都一致，否则会出现错误。
③上述进行复制的前提是目标表已经存在，若想不创建表就直接通过源数据表在添加数据的同时创建表也是可以的，具体语法如下：

CREATE TABLE table_name AS SELECT column_name1,column_name2......FROM source_table；

b.UPDATE修改数据：

  UPDATE table_name SET column_name1=data1,column_name2=data2......[WHERE condition];

①修改表中指定字段的全部值：

   UPDATE productinfo SET productid =ID;

②根据条件修改表中的指定字段值：

   UPDATE productinfo SET productid='123456' WHEN productname='XIAOMING';

c.DELETE删除语句：

 DELETE FROM table_name [WHERE condition];
 [WHERE condition]子句可以省略，如果省略意味着删除全部数据。

d.SELECT查询语句：

SELECT column_name1,column_name2......FROM table_name [WHERE condition]

①查询表中全部数据：

   SELECT *FROM productinfo;

②查询表中某一字段数据：

   SELECT productid FROM productinfo;

③根据条件查询数据：

SELECT *FROM productinfo WHERE productid='123456';

6.其他数据操纵语言：
a.TRUNCATE语句：删除语句，与DELETE类似

 TRUNCATE TABLE table_name；//删除表中所有的数据，但是速度比DELETE快。

b.MERGE语句：修改数据表中的数据，与UPDATE类似，但也有区别。使用MERGE可以同时进行增加和修改操作。

  MERGE [INTO] table_name1  //要修改或添加的表
         USING table_name2     //参照更新的表
         ON (condition)        //两表之间的关系，或其他一些条件
        WHEN MATCHED THEN merge_update_clause  //如果和表2中的条件匹配，就执行该更新语句
        WHEN NOT MATCHED THEN merge_insert_clause;//如果条件不匹配，就执行该增加操作语句

注：两个WHEN可以删除，但是一次只能删除一个，如果两个都删除，那MERGE语句将失去意义。

7.数据控制语句DCL:
主要对数据库使用者赋予和撤销访问数据库的权限设置，包括授予权限使用的GRANT语句和收回权限的REVOKE语句。

8.数据库用户管理：
在ORACLE中用户登录数据库的方式主要有三种：①密码验证、②外部验证（其验证密码通常与数据库所在的操作系统的密码一致）、③全局验证。
a.创建用户：
①使用企业管理器创建用户；
②使用SQL*PLUS创建用户，一般创建用户语法如下：

CREATE USER username 
   IDENTIFIED BY PASSWORD |	EXTERANALLY AS CERTIFICATE_DN|GLOBALLY AS DIRECTORY_DN  //分别为三种验证方式：口令验证、外部验证、全局验证
   [DEFAULT TABLESPACE tablesapce]  //设置默认表空间	
   [TEMPORARY TABLESPACE tablespace|tablespace_group_name]  //设置临时表空间或临时表空间组
   [QUOTA size|UNLIMITED ON tablespace]  //设置当前用户使用表空间的最大值
   [PROFILE profile]  //设置当前用户使用的概要文件的名称
   [PASSWORD EXPIRE]  //设置当前用户密码立即处于过期状态，如果用户想再登陆数据库必须要改密码
   [ACCOUNT LOCK|UNLOCK]  //设置用户的锁定状态。默认为锁定状态
   
例如：CREATE USER USER2
      IDENTIFIED BY abcd
	  DEFAULT TABLESPACE test
	  QUOTA 10M ON test
	  TEMPORARY TABLESPACE temp
	  PROFILE pro_test
	  PASSWORD EXPIRE;

b.修改用户：
①使用企业管理器修改；
②在SQL*PLUS中修改：与创建用户的语法非常相似，具体如下：

 ALTER USER user IDENTIFIED
	   BY password [REPLACE old_password]|EXTERANALLY [AS 'certificate_ON']|GLOBALLY [AS '[directory_ON]']
	   [DEFAULT TABLESAPCE tablesapce|tablespace_group_name]
	   [QUOTA (size_clause|UNLIMITED) ON tablespace]
	   [PROFILE profile]
	   [PASSWORD EXPIRE]
	   [ACCOUNT LOCK|UNLOCK]
	   
	   例1：修改用户USER2的密码
	   ALTER USER USER2
	   IDENTIFIED BY newabcd
	   DEFAULT TABLESAPCE test;
	   
	   例2：为用户USER2添加临时表空间
	   ALTER USER USER2
	   TEMPORARY TABLESAPCE newspace1;
	   
	   例3：为用户USER2设置密码立即过期
	   ALTER USER USER2
	   PASSWORD EXPIRE;

c.删除用户：删除一些废弃不用的用户，在这同时需要把该用户所使用的数据库对象一并删除。
①使用企业管理器删除用户；
②在SQL*PLUS中删除用户：

   DROP USER username CASCADE;  //如果要删除的用户中没有任何数据库对象，那么就可以省略CASCADE关键字

9.权限管理
在ORACLE数据库中，权限分为系统权限和对象权限两类。系统权限主要指SESSION权限、USER权限等，也就是说对数据库的系统级操作都可以成为系统权限；
对象权限主要指表对象、序列、触发器等操作的权限。
a.授予权限：
①使用企业管理器授予系统权限；
②使用企业管理器添加对象权限；
③在SQL*PLUS中授予系统权限：一般由数据库管理员来处理，只有足够的权限才可以。

GRANT system_privilege //创建的系统权限名称
	  |ALL PRIVILEGES TO (user IDENTIFIED BY password |role|)  //分别是：可以设置除SELECT ANY DIRECTIONARY权限之外的所有系统权限；设置指定用户的权限；role代表设置角色的权限。
	  [with ADMIN OPTION]  //表示当前给予授权的用户还可以给其他用户进行系统权限的授权

例1：授权一个用户系统权限session(也可以加上WITH ADMIN OPTION)

  GRANT create session to USER1 (WITH ADMIN OPTION)

④在SQL*PLUS中授予用户对象权限：与上面类似，具体语法如下

 GRANT object_privilege|ALL  //object_privilege:对象权限的名称
  ON schema.object  //为用户授予的对象权限所使用的对象
  TO user|role      //user代表用户，role代表角色
  [WITH ADMIN OPTION]    //代表当前用户可以给予其他用户系统权限
  [WITH THE GRANT ANY OBJECT]  //代表当前用户可以给予其他用户对象权限
  例1：为用户USER1授予表对象AA删除的权限。
  GRANT DELETE ON AA TO USER1

b.撤销权限：
①撤销系统权限：前提是已经存在系统权限，且只能由管理员操作，具体语法如下:

 REVOKE system_privilege 
	   FROM user|role
	   例：撤销USER1的create session权限
	    REVOKE create session FROM USER1

②撤销对象权限，具体语法如下：

REVOKE object_privilege|ALL
	ON schema.object
	FROM user|role
	[CASCADE CONSTRAINTS]  //表示该用户授权其他用户的权限也一并撤销
    例：撤销用户USER1的DELETE权限
	REVOKE DELETE ON AA FROM USER1;

注：在撤销用户权限时，撤销系统权限和撤销对象权限是不同的。如果撤销用户的系统权限，那么该用户授予其他用户的系统权限仍然存在；
而撤销了用户的对象权限后，用户授予其他用户的对象权限也同时被撤销了。
c.查询用户权限：
①在企业管理器中查询用户权限；
②使用SQL*PLUS中查询用户权限：用户权限存储在数据库的数据字典中。
系统权限：DBA_SYS_PRIVS; 对象权限：DBA_TAB_PRIVS;
例：查询ANONYMOUS的系统权限

SELECT *FROM DBA_SYS_PRIVS WHERE GRANTEE='ANONYMOUS';//罗列用户名和权限

查询ANONYMOUS的对象权限

 SELECT PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE='ANONYMOUS'; //只罗列权限

注：除了在上述两个数据字典中查询权限外，还可以直接在USER_SYS_PRIVS查询当前登录用户的系统权限；
在ALL_TAB_PRIVS中查询当前登录用户的对象权限。