Oracle learning-------DDL, DML, TCL, DCL

1.DDL

Data Definition Language:

DDL语句包括:create、alter、drop 、truncate 

1.1 CREATE

Oracle commonly used data types:

Insert picture description here

1.1 Table-level operations

1.1.1 Create Table

---创建学生表
CREATE TABLE STUDENT(
       STU_ID NUMBER PRIMARY KEY,     ---主键约束
       STU_NAME VARCHAR(10) NOT NULL, ---非空约束
       STU_SEX CHAR(1) DEFAULT 'F',   ---默认值
       STU_AGE NUMBER(3),
       STU_IDCARD VARCHAR2(18) UNIQUE ---唯一键约束
);
---创建课程表
CREATE TABLE COURSE(
       C_ID NUMBER PRIMARY KEY,
       C_NAME VARCHAR2(20) NOT NULL
);
---创建分数表
CREATE TABLE SCORE(
       C_ID NUMBER,
       STU_ID NUMBER,
       SCORE NUMBER(4,1),
       PRIMARY KEY(C_ID,STU_ID),      ---主键约束
       FOREIGN KEY (C_ID) REFERENCES COURSE(C_ID),  ---外键约束
       FOREIGN KEY (STU_ID) REFERENCES STUDENT(STU_ID)
);
---根据查询的数据创建表
CREATE TABLE A 
AS 子查询;

---复制某个表结构
CREATE TABLE A
AS SELECT * FROM B WHERE 1=2;

---复制某个表的结构和数据
CREATE TABLE A
AS SELECT * FROM B WHERE 1=1;

---复制某个表的部分数据
CREATE TABLE A
AS SELECT 列1,列2.... FROM B WHERE 条件;

1.1.2 Modify the table structure

---添加一列
ALTER TABLE STUDENT ADD COL1 VARCHAR2(10);

---添加多列
ALTER TABLE STUDENT ADD(COL2 NUMBER,COL3 NUMBER);

---修改某个字段的类型
ALTER TABLE STUDENT MODIFY(COL1 NUMBER);

---修改多个字段的类型
ALTER TABLE STUDENT MODIFY(COL2 VARCHAR2(1),COL3 VARCHAR2(1));

---增加或者修改字段的约束
ALTER TABLE STUDENT MODIFY(COL1 UNIQUE);

---修改列名
ALTER TABLE STUDENT RENAME COLUMN COL1 TO COL4;

---删除某列数据
ALTER TABLE STUDENT DROP COLUMN COL2;

---删除多列数据
ALTER TABLE STUDENT DROP(COL3,COL4);

1.1.3 Delete table

---删除整个表数据
DELETE FROM STUDENT;
TRUNCATE TABLE STUDENT;

---删除表
DROP TABLE STUDENT;

1.2 View operation

---创建视图
CREATE VIEW <视图名> [(<列名>[,<列名>,....])]
AS <子查询>
[WITH CHECK OPTION];

WITH CHECK OPTION: Indicates that the UPDATE, INSERT, and DELETE operations on the view must be updated, and the inserted or deleted rows meet the predicate conditions in the view definition (that is, the conditional expressions in the subquery).

CREATE VIEW STU_SEX_VIEW
AS SELECT * FROM STUDENT WHERE STU_SEX = 'F'
WITH CHECK OPTION;

Because there is a WITH CHECK OPTION, STU_SEX ='F' will be judged when updating data.
Delete view

DROP VIEW 视图名

1.3 Index operation

Create index

CREATE [UNIQUE][CLUSTER] INDEX <索引名>
ON <表名>(<列名>[<次序>][,<列名>[次序],.....])

UNIQUE indicates that each index value of this index only corresponds to a unique data record.
CLUSTER indicates that the created index is a clustered index.

Modify index

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

Delete index

DROP INDEX <索引名>

2.DML

Basically the same as MySQL syntax, refer to my previous MySQL MySQL basic syntax

3.TCL

Transaction Control Language (Transaction Control Language):
TCL statements include: commit, rollback, savepoint

4. DCL

DCL mainly manages permissions

4.1 GRANT

GRANT <权限>[,<权限>]...
ON <对象类型><对象名>[,<对象类型><对象名>]....
TO <用户>[,<用户>]....
[WITH GRANT OPTION]

---把student表和course表的全部操作权限授予用户U2和U3
GRANT ALL PRIVILEGES
ON TABLE STUDENT,COURSE
TO U2,U3

---把sc表的查询权限授予给所有用户
GRANT SELECT 
ON TABLE SC
TO PUBLIC;

---把查询student表和修改学生学号的权限授给u4
GRANT SELECT,UPDATE(SNO)
ON TABLE STUDENT
TO U4
WITH GRANT OPTION;

4.2 REVOKE

REVOKE <权限>[,<权限>]...
ON <对象类型><对象名>[,<对象类型><对象名>]....
FROM <用户>[,<用户>]...[CASCADE|RESTRICT]

---收回用户U4修改学生学号的权限
REVOKE UPDATE(SNO)
ON TABLE STUDENT
FORM U4;

---收回多有用户会表sc发查询权限
REVOKE SELECT 
ON TABLE SC
FROM PUBLIC;
---吧用户U5对sc表的insert权限收回
REVOKE INSERT 
ON TABLE SC
FROM U5 CASCADE