Oracle_11g常用SQL語句（高級工程師必備）

參賽學員：吳賢志

獲獎獎項：三等獎

  -- 退出 SQLPLUS
exit;

-- 修改 system(sys) 賬號密碼
SQLPLUS /NOLOG
CONN /AS SYSDBA
ALTER USER SYSTEM IDENTIFIED BY tarring;

-- 清除 SQLPLUS 屏幕
CLEAR SCREEN;
CL SCR;

-- 查看數(shù)據(jù)文件位置
SELECT NAME FROM v$datafile;

-- 查看控制文件位置
SELECT NAME FROM v$controlfile;

-- 查看日志文件位置
SELECT MEMBER FROM v$logfile;

-- 建立表空間
CREATE TABLESPACE ts01 
DATAFILE 'D:\DataBase\Oracle11g\oradata\orcl\test_db01.dbf'SIZE 100M
AUTOEXTEND ON NEXT 100M MAXSIZE 1024M
DEFAULT STORAGE(INITIAL 10m NEXT 1M)
PERMANENT
ONLINE
LOGGING;

-- 修改表空間
ALTER TABLESPACE ts01
NOLOGGING;

-- 表空間增加數(shù)據(jù)文件
ALTER TABLESPACE ts01 
ADD DATAFILE 'D:\DataBase\Oracle11g\oradata\orcl\test_db02.dbf'SIZE 100M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

-- 刪除表空間
DROP TABLESPACE ts01;

-- 刪除表空間同時刪除數(shù)據(jù)文件
DROP TABLESPACE ts01 INCLUDING CONTENTS AND DATAFILES;

-- 表空間中建表
CREATE TABLE student(
student_id VARCHAR2(10),
student_name VARCHAR2(20)
)TABLESPACE ts01;

-- 查看表所屬表空間
SELECT TABLE_NAME, TABLESPACE_NAME FROM tabs WHERE TABLE_NAME = 'STUDENT';

-- 查看表結(jié)構(gòu)
DESCRIBE student;
DESC student;

-- 增加表注釋
COMMENT ON TABLE student IS '學生信息表';

-- 查看表注釋
SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME = 'STUDENT';
SELECT * FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = 'STUDENT';

-- 表字段增加注釋
COMMENT ON COLUMN STUDENT.STUDENT_ID IS '學生編號';

-- 查看表字段注釋
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'STUDENT';
SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'STUDENT';

-- 查看用戶所有表
SELECT * FROM User_Tables;

-- 查看用戶擁有的所有對象
SELECT * FROM User_Objects;

-- 查看用戶擁有的 表 試圖 序列
SELECT * FROM User_Catalog;

-- 表字段修改
ALTER TABLE student MODIFY(student_id CHAR(15));

-- 表字段增加
ALTER TABLE STUDENT ADD(AGE NUMBER(2));

-- 刪除表字段
ALTER TABLE STUDENT DROP COLUMN student_name;

-- 修改表名稱
RENAME STUDENT TO STU;

-- 刪除表
DROP TABLE STUDENT;

CREATE TABLE student(
s_id Varchar2(10),
s_name varchar2(20),
s_age Number(3),
s_birthday DATE
)TABLESPACE ts01;

-- 增加一條記錄
INSERT INTO
student (s_id, s_name, s_age, s_birthday) 
VALUES ('S000000001', 'Tarring01', 10, to_date('1982-10-06','yyyy-mm-dd'));

INSERT INTO
student (s_id, s_name, s_age, s_birthday) 
VALUES ('S000000002', 'Tarring02', 10, Sysdate);

-- 使用替代變量時，輸入字符串字段時一樣要寫上引號
INSERT INTO
student (s_id, s_name, s_age, s_birthday) 
VALUES (&s_id, &s_name, 10, Sysdate);

-- 修改記錄
UPDATE student SET s_name = '陶川', s_age = 20 WHERE s_id = 'S000000002';

-- 刪除記錄
DELETE FROM student WHERE s_id = 'S000000002';

-- 截斷表
TRUNCATE TABLE student;

-- 事務(wù)處理
COMMIT; -- 提交事務(wù)
INSERT INTO student (s_id, s_name) VALUES ('S001', 'tarring1');
ROLLBACK; -- 回滾，回滾到上一次提交過后的點

-- 帶恢復點的事務(wù)
COMMIT;
INSERT INTO student (s_id, s_name) VALUES ('S001', 'tarring1');
SAVEPOINT firstdate;
INSERT INTO student (s_id, s_name) VALUES ('S002', 'tarring2');
SAVEPOINT seconddate;
DELETE FROM student;
ROLLBACK TO firstdate;
SELECT * FROM student;

--  約束條件            說明
--------------------------------------------------------------------
--  UNIQUE              指定字段的值，必須是唯一的
--  PRIMARY KEY         主鍵，會為指定的字段作索引，并且也是唯一的值
--  NOT NULL            不可以是空值【'' NULL】或0（零）
--  CHECK               檢查，必須符合指定的條件
--  FOREIGN KEY         外鍵，用來創(chuàng)建一個參考表之間的關(guān)系

-- 建表同時建立唯一約束
CREATE TABLE student(
s_id Varchar2(10),
s_name varchar2(20),
s_age Number(3),
s_birthday DATE,
CONSTRAINT s_name_uk UNIQUE(s_name)
)TABLESPACE ts01;

-- 查看唯一約束
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHERE table_name = 'STUDENT';

-- 作業(yè)：數(shù)據(jù)字典【分類 常用】

-- 建表同時建立主鍵
CREATE TABLE student(
s_id Varchar2(10),
s_name varchar2(20),
s_age Number(3),
s_birthday DATE,
CONSTRAINT s_id_pk PRIMARY KEY (s_id)
)TABLESPACE ts01;

-- 查看主鍵約束
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHERE table_name = 'STUDENT';

-- 建表同時建立非空字段
CREATE TABLE student(
s_id Varchar2(10),
s_name varchar2(20) NOT NULL,
s_age Number(3),
s_birthday DATE,
CONSTRAINT s_id_pk PRIMARY KEY (s_id)
)TABLESPACE ts01;

INSERT INTO student (s_id, s_name) VALUES ('S001', NULL); -- 插入一個null

-- 查看非空約束
SELECT table_name, constraint_name, constraint_type, search_condition FROM User_Constraints WHERE table_name = 'STUDENT';

-- 建表同時建立檢查
CREATE TABLE student(
s_id Varchar2(10),
s_name varchar2(20),
s_age Number(3),
s_birthday DATE,
CONSTRAINT s_age_ck CHECK (s_age BETWEEN 1 AND 100) -- 端點值可以使用
)TABLESPACE ts01;

-- 查看檢查約束
SELECT table_name, constraint_name, constraint_type, search_condition FROM User_Constraints WHERE table_name = 'STUDENT';


-- 外鍵的使用
CREATE TABLE team(
t_id Varchar2(10),
t_name Varchar2(20),
CONSTRAINT t_id_pk PRIMARY KEY (t_id)
)TABLESPACE ts01;

CREATE TABLE student(
s_id Varchar2(10),
team_id VARCHAR2(10),
s_name varchar2(20),
CONSTRAINT s_id_pk PRIMARY KEY (s_id),
CONSTRAINT s_team_id_fk FOREIGN KEY (team_id) REFERENCES team(t_id)
)TABLESPACE ts01;

-- 查看表的外鍵約束
SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHERE table_name = 'STUDENT';

drop table team;  -- 被引用表是不能刪除的

insert into team (t_id, t_name) values ('t001', 'lansene');
insert into student(s_id, s_name, team_id) values ('s001','tarring', 't001');
delete from team; -- 被引用的記錄是不能刪除的

-- 關(guān)閉一個約束
ALTER TABLE student DISABLE CONSTRAINT s_team_id_fk;

-- 啟用一個約束
ALTER TABLE student ENABLE CONSTRAINT s_team_id_fk;

-- 刪除一個約束
ALTER TABLE student DROP CONSTRAINT s_team_id_fk;

-- 已創(chuàng)建的表增加一個約束
ALTER TABLE student ADD CONSTRAINT s_team_id_fk FOREIGN KEY (team_id) REFERENCES team(t_id);

/********************************************************************************************|
|             SQL語句5大類型                    |  命令     | 說明                           |
|********************************************************************************************|
| Data Retrieval數(shù)據(jù)檢索                        | select    | 查詢記錄                       |
|********************************************************************************************|
| Date Manipulation Language【DML】數(shù)據(jù)操縱語言 | insert    | 添加記錄                       |
|                                               | update    | 修改記錄                       |
|                                               | delete    | 刪除記錄                       |
|********************************************************************************************|
| Data Definition Language【DDL】數(shù)據(jù)定義語言   | create    | 創(chuàng)建                           |
|                                               | alter     | 修改                           |
|                                               | drop      | 丟棄【刪除】                   |
|                                               | rename    | 重命名                         |
|                                               | truncate  | 截斷                           |
|********************************************************************************************|
| Transaction Control事務(wù)控制                   | commit    | 確認命令                       |
|                                               | rollback  | 回退至前一次確認的命令或保存點 |
|                                               | savepoint | 設(shè)置保存點                     |
|********************************************************************************************|
| Data Control Language【DCL】數(shù)據(jù)控制語言      | grant     | 授予權(quán)限                       |
|                                               | revoke    | 撤消權(quán)限                       |
|********************************************************************************************/


/*************************************|
|       系統(tǒng)權(quán)限       |     說明     |
|*************************************|
|   create session     |  連接數(shù)據(jù)庫  |
|*************************************|
|   create table       |  創(chuàng)建表      |
|*************************************|
|   create sequence    |  創(chuàng)建序列    |
|*************************************|
|   create view        |  創(chuàng)建視圖    |
|*************************************|
|   create proceduer   |  創(chuàng)建程序    |
|*************************************/


/*******************************************************************|
|      \  對象 |  表【table】 |  視圖【view】 |  程序【procedure】  |
| 權(quán)限  \      |              |               |                     |
|*******************************************************************|
|    insert    |      Y       |       Y       |                     |
|*******************************************************************|
|    alter     |      Y       |               |                     |
|*******************************************************************|
|    update    |      Y       |       Y       |                     |
|*******************************************************************|
|    delete    |      Y       |       Y       |                     |
|*******************************************************************|
|    select    |      Y       |       Y       |                     |
|*******************************************************************|
|    index     |      Y       |               |                     |
|*******************************************************************|
|    execute   |              |               |          Y          |
|*******************************************************************/

-- 創(chuàng)建用戶
CREATE USER u01 IDENTIFIED BY p01;

-- 創(chuàng)建用戶并制定默認表空間
CREATE USER u01 IDENTIFIED BY p01 DEFAULT TABLESPACE QUOTA 2M ON ts01; --quota 表空間中可使用的配額

-- 修改用戶密碼
ALTER USER u01 IDENTIFIED BY p001;

-- 修改用戶表空間配額
ALTER USER u01 QUOTA 20M ON ts01;
ALTER USER u01 QUOTA UNLIMITED ON ts01; -- 用戶對表空間沒有配額限制

-- 回收unlimited tablespace權(quán)限
REVOKE UNLIMITED TABLESPACE FROM ts01; 

-- 刪除用戶
DROP USER u01;

-- 切換連接數(shù)據(jù)庫的用戶
CONNECT u01/p01;
conn u01/p01;

-- 授權(quán)用戶連接數(shù)據(jù)庫的權(quán)限
GRANT CREATE SESSION TO u01;

-- 授權(quán)用戶創(chuàng)建序列(sequence)的權(quán)限
GRANT CREATE sequence TO u01;

-- 授權(quán)用戶創(chuàng)建表的權(quán)限
GRANT CREATE TABLE TO u01;
--授權(quán)用戶查表的權(quán)限
grant select on ts01.user1 to wangkai；
--授權(quán)用戶修改表的權(quán)限
grant alter on user1 to  wangkai；
--授權(quán)用戶刪除表的權(quán)限(沒有此權(quán)限)
grant drop on user1 to  wangkai；(錯誤)
--授權(quán)用戶對一個表的所有權(quán)限
grant all on user1 to wangkai; 
--授權(quán)所有用戶對一個表的所有權(quán)限
grant all on user1 to public;

-- 收回用戶創(chuàng)建表的權(quán)限
REVOKE CREATE TABLE FROM u01;
-- 收回用戶查表的權(quán)限
REVOKE select on user1 FROM u01;
-- 收回用戶修改的權(quán)限
REVOKE select on user1 FROM u01;
-- 收回用戶對一個表的所有權(quán)限
revoke all on user1 from wangkai;
--收回所有用戶對一個表的所有權(quán)限
revoke all on user1 to public; 

-- 創(chuàng)建角色
CREATE ROLE r01;

-- 角色授權(quán)
GRANT CREATE SESSION, CREATE TABLE TO r01;

-- 收回角色權(quán)限
REVOKE CREATE TABLE FROM r01;

-- 查看角色權(quán)限
SELECT ROLE,PRIVILEGE FROM role_sys_privs WHERE ROLE='R01'

-- 角色賦給用戶
GRANT r01 TO u01;

-- 查看當前用戶角色
SELECT * FROM user_role_privs;

-- 刪除角色
DROP ROLE r01;