Oracle_11g常用SQL語(yǔ)句（高級(jí)工程師必備）
我們提供的服務(wù)有：做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、納溪ssl等。為上千多家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的納溪網(wǎng)站制作公司
Oracle_11g常用SQL語(yǔ)句（高級(jí)工程師必備）
--退出 SQLPLUS
exit;
--修改 system(sys)賬號(hào)密碼
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í)刪除數(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 '學(xué)生信息表';
--查看表注釋
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 '學(xué)生編號(hào)';
--查看表字段注釋
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'STUDENT';
SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'STUDENT';
--查看用戶所有表
SELECT * FROM User_Tables;
--查看用戶擁有的所有對(duì)象
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;
--修改表名稱(chēng)
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);
--使用替代變量時(shí)，輸入字符串字段時(shí)一樣要寫(xiě)上引號(hào)
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';
--截?cái)啾?br />TRUNCATE TABLE student;
--事務(wù)處理
COMMIT; --提交事務(wù)
INSERT INTO student (s_id, s_name) VALUES ('S001', 'tarring1');
ROLLBACK; --回滾，回滾到上一次提交過(guò)后的點(diǎn)
--帶恢復(fù)點(diǎn)的事務(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;
-- 約束條件 說(shuō)明
--------------------------------------------------------------------
-- UNIQUE 指定字段的值，必須是唯一的
-- PRIMARY KEY 主鍵，會(huì)為指定的字段作索引，并且也是唯一的值
-- NOTNULL 不可以是空值【'' NULL】或0（零）
-- CHECK 檢查，必須符合指定的條件
-- FOREIGN KEY 外鍵，用來(lái)創(chuàng)建一個(gè)參考表之間的關(guān)系
--建表同時(shí)建立唯一約束
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 WHEREtable_name = 'STUDENT';
--作業(yè)：數(shù)據(jù)字典【分類(lèi)常用】
--建表同時(shí)建立主鍵
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 WHEREtable_name = 'STUDENT';
--建表同時(shí)建立非空字段
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); --插入一個(gè)null
--查看非空約束
SELECT table_name, constraint_name, constraint_type, search_condition FROMUser_Constraints WHERE table_name = 'STUDENT';
--建表同時(shí)建立檢查
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) --端點(diǎn)值可以使用
)TABLESPACE ts01;
--查看檢查約束
SELECT table_name, constraint_name, constraint_type, search_condition FROMUser_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 WHEREtable_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)閉一個(gè)約束
ALTER TABLE student DISABLE CONSTRAINT s_team_id_fk;
--啟用一個(gè)約束
ALTER TABLE student ENABLE CONSTRAINT s_team_id_fk;
--刪除一個(gè)約束
ALTER TABLE student DROP CONSTRAINT s_team_id_fk;
--已創(chuàng)建的表增加一個(gè)約束
ALTER TABLE student ADD CONSTRAINT s_team_id_fk FOREIGN KEY (team_id)REFERENCES team(t_id);
/********************************************************************************************|
| SQL語(yǔ)句5大類(lèi)型 | 命令 |說(shuō)明 |
|********************************************************************************************|
| Data Retrieval數(shù)據(jù)檢索 | select |查詢記錄 |
|********************************************************************************************|
| Date Manipulation Language【DML】數(shù)據(jù)操縱語(yǔ)言 | insert |添加記錄 |
| | update |修改記錄 |
| | delete |刪除記錄 |
|********************************************************************************************|
| Data Definition Language【DDL】數(shù)據(jù)定義語(yǔ)言 | create |創(chuàng)建 |
| | alter |修改 |
| | drop |丟棄【刪除】 |
| | rename |重命名 |
| | truncate |截?cái)?nbsp; |
|********************************************************************************************|
| Transaction Control事務(wù)控制 | commit |確認(rèn)命令 |
| | rollback |回退至前一次確認(rèn)的命令或保存點(diǎn) |
| | savepoint |設(shè)置保存點(diǎn) |
|********************************************************************************************|
| Data Control Language【DCL】數(shù)據(jù)控制語(yǔ)言 |grant |授予權(quán)限 |
| | revoke |撤消權(quán)限 |
|********************************************************************************************/
/*************************************|
| 系統(tǒng)權(quán)限 | 說(shuō)明 |
|*************************************|
| create session | 連接數(shù)據(jù)庫(kù) |
|*************************************|
| create table | 創(chuàng)建表 |
|*************************************|
| create sequence | 創(chuàng)建序列 |
|*************************************|
| create view | 創(chuàng)建視圖 |
|*************************************|
| create proceduer | 創(chuàng)建程序 |
|*************************************/
/*******************************************************************|
| \ 對(duì)象 | 表【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)建用戶并制定默認(rèn)表空間
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; --用戶對(duì)表空間沒(méi)有配額限制
--回收unlimited tablespace權(quán)限
REVOKE UNLIMITED TABLESPACE FROM ts01;
--刪除用戶
DROP USER u01;
--切換連接數(shù)據(jù)庫(kù)的用戶
CONNECT u01/p01;
conn u01/p01;
--授權(quán)用戶連接數(shù)據(jù)庫(kù)的權(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)限(沒(méi)有此權(quán)限)
grant drop on user1 to wangkai；(錯(cuò)誤)
--授權(quán)用戶對(duì)一個(gè)表的所有權(quán)限
grant all on user1 to wangkai;
--授權(quán)所有用戶對(duì)一個(gè)表的所有權(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;
--收回用戶對(duì)一個(gè)表的所有權(quán)限
revoke all on user1 from wangkai;
--收回所有用戶對(duì)一個(gè)表的所有權(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;
--查看當(dāng)前用戶角色
SELECT * FROM user_role_privs;
--刪除角色
DROP ROLE r01;
重慶分公司
重慶分公司
