Oracle基础

--创建 T_CLASS ID列的序列
CREATE SEQUENCE T_CLASS_ID_SEQUENCE
INCREMENT BY 1      -- 设置每次步长为1
START WITH 1        -- 设置从1开始计数
MINVALUE 1          -- 设置最小计数为 1
MAXVALUE 999999999  -- 设置最大计数为 999999999
NOCYCLE             -- 设置为一直累加，不循环，也可以设置为 CYCLE(循环)
CACHE 10;           -- 设置缓存 10 个序列，如果系统宕掉了或者其它情况将会导致序列不连续，也可以设置为 NOCACHE(不缓存)


SELECT T_CLASS_ID_SEQUENCE.NEXTVAL FROM DUAL;
SELECT T_CLASS_ID_SEQUENCE.Currval FROM DUAL;

SELECT T_STUDENT_ID_SEQUENCE1.Nextval FROM DUAL;
SELECT T_CLASS_ID_SEQUENCE.Currval FROM DUAL;


--创建 T_STUDENT ID列的序列
CREATE SEQUENCE T_STUDENT_ID_SEQUENCE
INCREMENT BY 1 -- 设置每次步长为1
START WITH 1   -- 设置从1开始计数
NOMAXVALUE     -- 不设置最大值 
NOCYCLE        -- 设置为一直累加，不循环，也可以设置为 CYCLE(循环)
CACHE 10;      -- 设置缓存 10 个序列，如果系统宕掉了或者其它情况将会导致序列不连续，也可以设置为 NOCACHE(不缓存)

--创建 T_CLASS1 表
CREATE TABLE T_CLASS11
(
       ID NUMBER(10),                                       --班级ID
       NAME VARCHAR2(100) 
            CONSTRAINT NN_TC11_NAME NOT NULL,                 --班级名称，并创建一个 NOT NULL 约束
                                                            /**(创建 NOT NULL 约束的格式：
                                                                列名 列的数据类型 
                                                                       CONSTRAINT 自定义的非空约束名称 NOT NULL
                                                             */
       CONSTRAINT PK_TC11_ID PRIMARY KEY (ID)                --创建一个主键约束，把“班级ID”设置为主键
                                                            /**(创建主键约束的格式：
                                                                CONSTRAINT 自定义的主键约束名称 
                                                                       PRIMARY KEY (表中的列名,...)
                                                             */
);

--创建 T_STUDENT 表
CREATE TABLE T_STUDENT
(
       ID NUMBER(6),                                           --学生ID
       NAME VARCHAR2(50) 
            CONSTRAINT NN_TS_NAME NOT NULL,                    --学生名称，并创建一个 NOT NULL 约束
                                                               /**
                                                                   (创建 NOT NULL 约束的格式：
                                                                   列名 列的数据类型 
                                                                          CONSTRAINT 自定义的非空约束名称 NOT NULL
                                                                */
       ID_NUMBER VARCHAR2(18) 
                 CONSTRAINT NN_TS_ID_NUMBER NOT NULL,          --身份证号码，并创建一个 NOT NULL 约束
       CLASS_ID NUMBER(10),                                    --班级ID
       SEX VARCHAR2(2) DEFAULT '男',                           --性别，并设置默认为'男'
                                                               /**
                                                                   (创建默认值约束的格式：
                                                                   列名 列的数据类型
                                                                             DEFAULT 默认值
                                                                */
       AGE NUMBER(3),                                          --年龄
       ADMISSION_TIME DATE,                                    --入学时间
       ADDRESS VARCHAR2(100),                                  --地址
       CHINESE_SCORE NUMBER(5, 2),                             --语文成绩
       MATH_SCORE NUMBER(5, 2),                                --数据成绩
       ENGLISH_SCORE NUMBER(5, 2),                             --英语成绩
       --列定义结束
       
       
       --定义约束
       CONSTRAINT PK_TS_ID PRIMARY KEY (ID),                   --创建一个主键约束，把“学生ID”设置为主键
                                                               /**
                                                                   (创建主键约束的格式：
                                                                   CONSTRAINT 自定义的主键约束名称 
                                                                                 PRIMARY KEY (表中的列名,...)
                                                                */
       CONSTRAINT FK_TS_CLASS_ID 
                  FOREIGN KEY (CLASS_ID) 
                  REFERENCES T_CLASS(ID),                      --创建一个外键约束，把 CLASS_ID 列指定引用 T_CLASS 表中 ID 列
                                                               /**
                                                                   (创建外键约束的格式：
                                                                   CONSTRAINT 自定义的外键约束名称 
                                                                              FOREIGN KEY 当前表中要设置成外键的列名 
                                                                              REFERENCES 被引用的表名(被引用的列名)
                                                                */
       CONSTRAINT U_TS_ID_NUMBER
                  UNIQUE (ID_NUMBER),                          --创建一个唯一约束，把“身份证号码”设置为唯一约束
                                                               /**
                                                                   (创建唯一约束的格式：
                                                                   CONSTRAINT 自定义的唯一约束名称 
                                                                                 UNIQUE (表中的列名)
                                                                */
       CONSTRAINT CK_TS_AGE 
                  CHECK (AGE > 10)                             --创建一个检查约束，把“年龄”设置为唯一约束
                                                               /**
                                                                   (创建唯一约束的格式：
                                                                   CONSTRAINT 自定义的唯一约束名称 
                                                                                 CHECK (表中的列名)
                                                                */
);

--修改表名，格式：ALTER TABLE <table name> RENAME TO 新表名
ALTER TABLE T_STUDENT11 RENAME TO T_STUDENT;

--用于修改现有列定义，格式：ALTER TABLE <table name> MODIFY (column definition…, CONSTRAINT definition…);
ALTER TABLE T_STUDENT MODIFY 
(
      ID NUMBER(7), 
      AGE NUMBER(6),
      CONSTRAINT CK_TS_AGE1 CHECK (AGE > 11)--,
      --CONSTRAINT PK_TS_ID PRIMARY KEY(ID)
);

--用于向现有表中添加新的列，格式：ALTER TABLE <table name> ADD (column definition…, CONSTRAINT definition…);
ALTER TABLE T_STUDENT ADD 
(
      TELEPHONE1 NUMBER(11), 
      TELEPHONE2 NUMBER(11), 
      CONSTRAINT CK_TS_TELEPHONE1 CHECK (TELEPHONE1 >= 10000000000),
      CONSTRAINT CK_TS_TELEPHONE2 CHECK (TELEPHONE2 >= 10000000000)
);

--用于从现有表中删除列
--格式1: ALTER TABLE <table name> DROP COLUMN 列名;
ALTER TABLE T_STUDENT DROP COLUMN TELEPHONE1;
ALTER TABLE T_STUDENT DROP COLUMN TELEPHONE2;
--格式2: ALTER TABLE <table name> DROP (列名1， 列名2，...);
ALTER TABLE T_STUDENT DROP (TELEPHONE1, TELEPHONE2);

--用于从现有表中删除约束，格式: ALTER TABLE <table name> DROP CONSTRAINT 约束名称;
ALTER TABLE T_STUDENT DROP CONSTRAINT CK_TS_TELEPHONE1;
ALTER TABLE T_STUDENT DROP CONSTRAINT PK_TS_ID;
ALTER TABLE T_STUDENT DROP CONSTRAINT CK_TS_AGE;

--用于将现有表中的列设置为不可用，格式：ALTER TABLE <table name> SET UNUSED(列名1, 列名2...);
ALTER TABLE T_STUDENT SET UNUSED(TELEPHONE1, TELEPHONE2);

--TRUNCATE TABLE 命令，格式：TRUNCATE TABLE <table  name> REUSE STORAGE;
TRUNCATE TABLE T_STUDENT REUSE STORAGE;

--DROP TABLE 命令，格式：DROP  TABLE  <table  name>;
DROP TABLE T_STUDENT;


--INSERT 语句，插入单个记录的格式：INSERT INTO <table name>(column[,column...]) VALUES(value[,value...]);
INSERT INTO T_CLASS(ID, NAME) VALUES(12, '.NET开发12');
INSERT INTO T_CLASS VALUES(14, '.NET开发14');
COMMIT;

--INSERT 语句，将一个查询结果的记录插入表中的格式：
/**
  INSERT INTO <table name>(column[,column...]) 
         SELECT column[,column...] FROM <table name>;
*/
INSERT INTO T_CLASS(ID, NAME) 
         SELECT ID, NAME FROM CLASS;-- ORDER BY ID ASC;

--INSERT 语句，插入多个记录的格式：INSERT INTO <table name> VALUES('&column'[, '&column'...]);
INSERT INTO T_CLASS VALUES('&ID', '&NAME');
COMMIT;

--SELECT 语句，格式：
/**
  SELECT  *|{[DISTINCT]  column  |expression  [alias], …}
	        FROM   <table  name>;
*/
--这里的“*”代表查询表中所有的列值
SELECT * FROM T_CLASS;
--指定查询的列名
SELECT ID, NAME FROM T_CLASS;
--对查询出来的列的值进行表达式运算
SELECT '班级编号：' || ID || '    班级名称：' || NAME 班级信息 FROM T_CLASS;
--查询部份的列
SELECT ID FROM T_CLASS;
--用指定的别名，来表示查询出来的列
SELECT ID 班级ID, NAME 班级名称 FROM T_CLASS;
--查询表中 NAME 列中的数据
SELECT NAME 班级名称 FROM T_CLASS;
--查询表中 NAME 列中的数据，并使用关键字 DISTINCT 过滤掉指定列重复的数据
SELECT DISTINCT NAME 班级名称 FROM T_CLASS;

--带有 WHERE 子句的 SELECT 命令的语法如下：
/**	
  SELECT  *|{[DISTINCT]  column  |expression  [alias], …}
	        FROM   <table  name>
	        [WHERE  condition]
          [ORDER  BY  {column | alias} [ ASC | DESC ],  {column | alias} [ ASC | DESC ], …];
*/
SELECT * FROM T_CLASS WHERE ID < 5;
SELECT * FROM T_CLASS  
       WHERE ID < 5   
       ORDER BY ID ASC;       
SELECT * FROM T_CLASS 
       WHERE ID < 5       
       ORDER BY ID DESC;
       
--UPDATE 命令的语法如下：
/**
  UPDATE  <table  name>
	SET  column  =  value [, column  =  value, …]
	[WHERE  condition];
*/
SELECT * FROM T_CLASS WHERE NAME = '嵌入式1';
UPDATE T_CLASS 
       SET NAME = '嵌入式1'
       WHERE NAME = 'UPDATE1-嵌入式1';
COMMIT WORK;
--DELETE 命令的语法如下：
/**
  DELETE  [FROM]  <table  name>
	        [WHERE  condition];
*/
DELETE FROM T_CLASS WHERE ID = 31;
--删除表中所有的行
DELETE FROM T_CLASS;


----COMMIT 命令的语法如下：
/**
  COMMIT;
  或
  COMMIT  WORK;
*/
UPDATE T_CLASS 
       SET NAME = '嵌入式1' 
       WHERE NAME = 'UPDATE-嵌入式1';
COMMIT WORK;


--SAVEPOINT 的语法：SAVEPOINT  savepoint_id;
--要回退整个事务处理的 ROLLBACK 命令的语法：ROLLBACK;  或  ROLLBACK work;
--要回退到事务处理中特定的阶段，即保存点的 ROLLBACK 命令的语法：ROLLBACK TO SAVEPOINT savepoint_id;
UPDATE T_CLASS 
    SET NAME = '1' || NAME 
    WHERE ID = 1;
SAVEPOINT UPDATE_POINT_1;

UPDATE T_CLASS 
    SET NAME = '2' || NAME 
    WHERE ID = 2;
SAVEPOINT UPDATE_POINT_2;

SELECT * FROM T_CLASS WHERE ID <= 2;

ROLLBACK TO SAVEPOINT UPDATE_POINT_1;
COMMIT;

 
UPDATE T_CLASS 
    SET NAME = '22' || NAME 
    WHERE ID = 2;
 
COMMIT; 
SELECT * FROM T_CLASS WHERE ID <= 2;
     
SELECT * FROM ANDROID.T_STUDENT;
UPDATE ANDROID.T_STUDENT 
    SET NAME = '22' || NAME 
    WHERE ID = 2;

--使用 SQL 命令 GRANT 可以将对象权限授予其它用户，语法：GRANT  privileges  ON  object-name  TO  username;
--将 T_CLASS 表的 SELECT、UPDATE 权限，分配给 SCOTT 用户
GRANT SELECT, UPDATE ON T_STUDENT TO SCOTT;
--将 T_CLASS 表中 NAME 列的 UPDATE 权限，分配给 SCOTT 用户
GRANT UPDATE(NAME) ON T_CLASS TO SCOTT;
--如果向用户授予权限时使用了“WITH  GRANT  OPTION”，则获取此权限的用户反过来又可以将此权限授予其他用户，其语法如下：
/**
  GRANT  privileges  ON  object-name  TO  username  
	       WITH  GRANT  OPTION;
*/
GRANT SELECT,UPDATE ON T_CLASS TO SCOTT
         WITH  GRANT  OPTION;

--回收权限，GRANT 语法：REVOKE  privilege  ON  object-name  FROM  username;
REVOKE SELECT, UPDATE ON T_STUDENT FROM SCOTT;