SQL 基礎--&gt建立和管理表

--=================================

--SQL 基礎--&gt建立和管理表

--=================================

一、建立表：create table

1.語法：CREATE TABLE [ 使用者名稱. ] 表名

（列名資料型別 [ default 預設值] [ 約束條件] [ , ......] )

TABLESPACE 表空間名

2.表名和列名命名規則:

必須以字母開頭

必須在1–個字元之間

必須只能包含A–Z, a–z, 0–, _, $, 和#

必須不能和使用者定義的其他物件重名

必須不能是Oracle 的保留字

3.建立前必須具備的條件:

–CREATE TABLE許可權

–儲存空間

4.必須指定:

–表名

–列名, 資料型別, 尺寸

5.資料型別：

varchar2(size) 變長字元型（最大字元）

nvarchar2(size) 變長unicode字元型（最大字元）

char(size) 字長字元型（最大字元）

number(p,s) 數值型（p為長度最大，s為小數點後的位數－～）

data 日期型

LOB ( Large Object )：

clob 字元型，用於在資料庫中儲存單位元組的大資料物件，最大G

nclob 可存放大量unicode文字資訊，最大４G

blob 用於在資料庫中儲存二進位制資料，如照片，最大G

clob和blob許多操作是不能直接使用oracle的資料庫命令來完成的，

因此，oracle 提供了一個叫DBMS_LOB 的PL/SQL 軟體包來維護LOB資料型別的列。

bfile 外部二進位制檔案，用於在資料庫外的作業系統檔案中儲存大的二進位制

物件，如電影，最大G。bfile 資料型別是外部資料型別，因此定義為bfile資料

型別的列是不能透過oracle 的資料庫命令來操作的，

這些列只能透過作業系統命令或第三方軟體來維護。

raw 裸二進位制資料，此種型別的資料佔用的儲存空間小，操作效率也高，但在網路環境

中不同的計算機上傳輸資料時，oracle伺服器不進行任何字符集轉換，

raw 1-2000 字元。

long 、long raw 為和以前的oracle版本相容，oracle 繼續支援

long 和long raw 資料型別

long 可變大字元型資料，最大G，long raw 裸二進位制資料，最大2 G，

主要用在8i 以前的資料庫中儲存無結構的資料。

rowid 行地址

在oracle 8 以後的版本，LOB 資料型別可以完全取代LONG 資料型別，

而且oracle 伺服器操作LOB 資料型別比操作LONG 資料型別效率更高。

另外，在一個表中只能定義一個LONG資料型別的列，但可定義多個LOB資料

型別的列。LONG資料型別的列最多可以儲存GB資料，而LOB資料型別的列最多可以儲存GB的資料。

注意：

number(p,s)

p:1~38 精度位，precision，是總有效資料位數，預設是，可以用字元*表示。

s:-84~127 小數位，scale，是小數點右邊的位數，取值範圍是-84~127，

預設值取決於p，如果沒有指定p，那麼s是最大範圍，如果指定了p，那麼s=0。

p>0，對s分種情況：

1. s>0

精確到小數點右邊s位，並四捨五入。然後檢驗有效數位是否<=p；如果s>p，

小數點右邊至少有s-p個填充。

2. s<0

精確到小數點左邊s位，並四捨五入。然後檢驗有效數位是否<=p+|s|

eg:

123.2564 NUMBER 123.2564

1234.9876 NUMBER(6,2) 1234.99

12345.12345 NUMBER(6,2) Error

1234.9876 NUMBER(6) 1235

12345.345 NUMBER(5,-2) 12300

1234567 NUMBER(5,-2) 1234600

6.引用其他使用者的表

其他使用者定義的表不在當前使用者的方案中

應該使用使用者名稱作為字首，引用其他使用者定義的物件

7.DEFAULT 選項

插入時為一個列指定預設值

字串, 表示式, 或SQL 函式都是合法的

其它列的列名和偽列是非法的

預設值必須滿足列的資料型別定義

8.使用預設值：

插入記錄時，可省略那個欄位，也可顯示的加default

--演示建立表

SQL> CREATE TABLE orders

2 (

3 orderid int,

4 orderstatus varchar2(20),

5 orderdate date,

6 ordernum number(10)

7 );

Table created.

--確認表結構

SQL> DESC orders;

Name Null? Type

----------------------------------------- -------- ----------------------------

ORDERID NUMBER(38)

ORDERSTATUS VARCHAR2(20)

ORDERDATE DATE

ORDERNUM NUMBER(10)

Oracle 資料庫中的表

使用者定義的表:

–使用者自己建立並維護的一組表

–包含了使用者所需的資訊

資料字典:

–由Oracle Server自動建立的一組表

–包含資料庫資訊

9.查詢資料字典

檢視使用者定義的表,

SELECT table_name FROM user_tables ;

檢視使用者定義的各種資料庫物件

SELECT DISTINCT object_type FROM user_objects ;

檢視使用者定義的表, 檢視, 同義詞和序列

SELECT * FROM user_catalog ;

10.利用子查詢建立表：

CREATE TABLE table

[(column, column...)]

AS subquery;

--演示利用子查詢建立表

SQL> CREATE TABLE emp

2 AS

3 SELECT ename,job,hiredate,mgr,sal

4 FROM scott.emp;

Table created.

11.利用子查詢建立一個空表（克隆表結構）：

create table xx as select * from yy where 1=0; 欄位可以指定的

SQL> CREATE TABLE emp2

2 AS

3 SELECT *

4 FROM scott.emp

5 WHERE 1=2;

Table created.

SQL> SELECT * FROM emp2;

no rows selected

12.使用子查詢建立表時候用

AS subquery 選項，將建立表和插入資料結合起來

指定的列和子查詢中的列要一一對應

透過列名和預設值定義列

13.使用者檢視一個表佔用的磁碟空間：

user_extents,user_segments

14.DBA如何檢視一個表佔用的磁碟空間：

dba_extents,dba_segments

--使用者檢視自身表的使用情況(user_extents)

SQL> SELECT segment_name,segment_type,tablespace_name,bytes/1024/1024 "size",

2 blocks from user_extents;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME size BLOCKS

-------------------- ------------------ ------------------------------ ---------- ----------

ORDERS TABLE USERS .0625 8

EMP TABLE USERS .0625 8

EMP2 TABLE USERS .0625 8

--使用者檢視自身表的使用情況(user_segments)

SQL> SELECT segment_name,segment_type,tablespace_name,

2 bytes/1024/1024 byt,buffer_pool

3 FROM user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYT BUFFER_

---------------------------------------- ------------------ ------------------- ---------- -------

EMP2 TABLE USERS .0625 DEFAULT

EMP TABLE USERS .0625 DEFAULT

ORDERS TABLE USERS .0625 DEFAULT

BIN$iZfhhmba19rgQKjAZQETvA==$0 TABLE USERS .0625 DEFAULT

BIN$iZfhhmbZ19rgQKjAZQETvA==$0 TABLE USERS .0625 DEFAULT

BIN$iZfhhmbY19rgQKjAZQETvA==$0 TABLE USERS .0625 DEFAULT

SQL> INSERT INTO emp2

2 SELECT * FROM scott.emp;

15 rows created.

SQL> /

--DBA檢視佔用的磁碟空間情況

SQL> SELECT owner,segment_name,segment_type,tablespace_name,bytes/1024/1024

2 FROM dba_extents WHERE owner='SCOTT';

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024

--------------- ---------------------------- ------------------ -------------------- ---------------

SCOTT DEPT TABLE USERS .0625

SCOTT EMP TABLE USERS .0625

SCOTT BONUS TABLE USERS .0625

SCOTT SALGRADE TABLE USERS .0625

SCOTT TB TABLE USERS .0625

SCOTT DIGITS TABLE USERS .0625

SCOTT TB2 TABLE USERS .0625

SCOTT PK_DEPT INDEX USERS .0625

SCOTT PK_EMP INDEX USERS .0625

SCOTT CUST_ID_PK INDEX USERS .0625

SQL> SELECT SUM(BYTES)/1024/1024 FROM dba_extents WHERE owner='ROBINSON'

2 AND segment_name = 'EMP2';

SUM(BYTES)/1024/1024

--------------------

5

SQL> SELECT owner,segment_name,segment_type,tablespace_name,bytes/1024/1024

2 FROM dba_segments WHERE owner='SCOTT';

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024

--------------- ---------------------------------- --------------- ----------------- ---------------

SCOTT PK_DEPT INDEX USERS .0625

SCOTT DEPT TABLE USERS .0625

SCOTT EMP TABLE USERS .0625

SCOTT PK_EMP INDEX USERS .0625

SCOTT BONUS TABLE USERS .0625

SCOTT SALGRADE TABLE USERS .0625

SCOTT TB TABLE USERS .0625

SCOTT DIGITS TABLE USERS .0625

SCOTT TB2 TABLE USERS .0625

SCOTT CUST_ID_PK INDEX USERS .0625

SCOTT BIN$h5Qj5nIfmqrgQKjAZQESMA==$0 TABLE TBS1 .0625

15.DBA如何檢視一個表的行數：

先分析表，更新系統資料

exec dbms_stats.gather_table_stats('屬主' , ' 表名')

從dba_tables資料字典中查詢

select table_name,num_rows from dba_tables;

--使用dbms_stats.gather_table_stats包檢視

SQL> exec dbms_stats.gather_table_stats('robinson','emp2');

PL/SQL procedure successfully completed.

SQL> /

TABLE_NAME NUM_ROWS

------------------------------ ----------

EMP

ORDERS

EMP2 93544

--從dba_tables檢視

SQL> SELECT table_name,num_rows FROM dba_tables

2 WHERE table_name = 'EMP2' AND owner = 'ROBINSON';

TABLE_NAME NUM_ROWS

------------------------------ ----------

EMP2 93544

16.使用者自身檢視錶及列的相關資訊

user_tables user_tab_columns

--檢視錶的相關資訊

SQL> SELECT table_name,tablespace_name,temporary,num_rows FROM user_tables;

TABLE_NAME TABLESPACE_NAME T NUM_ROWS

------------------------------ ------------------------------ - ----------

EMP USERS N

ORDERS USERS N

EMP2 USERS N 93544

--檢視列的相關資訊

SQL> SELECT table_name,column_name,data_type,data_length,data_precision

2 FROM user_tab_columns

3 WHERE table_name = 'EMP2';

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION

------------------------------ ---------------------- -------------- ----------- --------------

EMP2 EMPNO NUMBER 22 4

EMP2 ENAME VARCHAR2 10

EMP2 JOB VARCHAR2 9

EMP2 MGR NUMBER 22 4

EMP2 HIREDATE DATE 7

EMP2 SAL NUMBER 22 7

EMP2 DEPTNO NUMBER 22 2

17.建立臨時表：

臨時表分為LOCAL(本地)臨時表和GLOBAL (全域性)臨時表，兩者的區別在於資料可見性。

LOCAL臨時表中的資料只在填充它的事務可見，GLOBAL臨時表可以被會話中的任何程式或模組訪問。

臨時表的資料在退出時自動清除，但臨時表的定義是永久的。當建立臨表的會話登出後，

表依然會存在，只不過是空的罷了。

CREATE GLOBAL TEMPRORARY TABLE TEMP

ON COMMIT PRESERVE ROWS

AS

SELECT * FROM SCOTT.EMP WHERE SAL>2000

ON COMMIT DELETE ROWS 資料行只有在事務中可見（預設值）。

ON COMMIT PRESERVE ROWS 資料行在整個會話中可見。

刪除臨時表：DROP TABLE

有時刪除時需要TRUNCATE TABLE才行。

二、修改表：

1.新增新列( 新增的列在最後，沒辦法調整其位置)

ALTER TABLE tablename

ADD (column datatype [DEFAULT expr] [ constraint ] [, column datatype]...);

SQL> ALTER TABLE scott.emp

2 ADD (jobid varchar2(20) DEFAULT 'Eng');

2.修改現有的列

ALTER TABLE tablename

MODIFY (column datatype [DEFAULT expr] [ constraint ]

[, column datatype]...);

可以被修改的內容：

列的長度

數字列的精度

列的資料型別

修改列的預設值

a.修改列的長度

--修改列的長度，當修改的長度比不能容納現有資料長度，提示錯誤，如下：

SQL> ALTER TABLE scott.emp MODIFY(ename varchar2(3));

ALTER TABLE scott.emp MODIFY(ename varchar2(3))

*

ERROR at line 1:

ORA-01441: cannot decrease column length because some value is too big

--以下修改長度正確執行

SQL> ALTER TABLE scott.emp MODIFY(ename varchar2(30));

Table altered.

b.修改列的精度

--只有當表中還沒有任何行或列值為空值才可以降低數字的精度，如下第一條語句修改出錯

SQL> ALTER TABLE scott.emp MODIFY(sal number(6,2));

ALTER TABLE scott.emp MODIFY(sal number(6,2))

*

ERROR at line 1:

ORA-01440: column to be modified must be empty to decrease precision or scale

SQL> ALTER TABLE scott.emp MODIFY(sal number(8,2));

Table altered.

c.修改列的資料型別

--將jobid列VARCHAR2型別改為CHAR型別

SQL> ALTER TABLE scott.emp

2 MODIFY (jobid CHAR(20));

Table altered.

d.修改列的預設值

--將jobid列的預設值Eng改為Engn

SQL> ALTER TABLE scott.emp MODIFY (jobid varchar2(20) DEFAULT 'Engn');

Table altered.

3.刪除列（一次只能刪除一個列，無法刪除屬於SYS 的表中的列）：

ALTER TABLE tablename DROP COLUMN columnname;

--刪除jobid列

SQL> ALTER TABLE scott.emp DROP COLUMN jobid;

Table altered.

4.將一列設定成無用（UNUSED）：

ALTER TABLE tablename SET UNUSED (列名）或

ALTER TABLE tablename SET UNUSED COLUMN 列名

--將robinson.emp表中deptno列設定為無用列

SQL> ALTER TABLE robinson.emp SET UNUSED COLUMN deptno;

Table altered.

5.刪除無用的列：

ALTER TABLE tablename DROP UNUSED COLUMNS

SQL> ALTER TABLE robinson.emp DROP UNUSED COLUMN;

Table altered.

SQL> ALTER TABLE robinson.emp DROP UNUSED COLUMNS;

Table altered.

三、物件改名：

列改名：

ALTER TABLE tablename RENAME COLUMN oldname TO newname

--將scott.emp的列sal改為salary

SQL> ALTER TABLE scott.emp RENAME COLUMN sal TO salary;

Table altered.

表改名：

ALTER TABLE tablename RENAME TO newname

SQL> CONN robinson/lion

Connected.

--將表名emp 改為employees

SQL> ALTER TABLE emp RENAME TO employees;

Table altered.

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

EMPLOYEES

ORDERS

EMP2

--使用下面的方法也能夠實現表的改名

SQL> RENAME employees TO emp;

Table renamed.

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

EMP

ORDERS

EMP2

對於表、索引、檢視、序列、同義詞等物件可直接用RENAME 改

格式：RENAME oldname TO newname

如：RENAME test2 TO test;

四、移動表空間：

ALTER TABLE tablename MOVE TABLESPACE tablespacename;

檢視錶在哪個表空間：

dba_tables

--首先檢視錶位於哪個表空間

SQL> SELECT owner,table_name,tablespace_name FROM dba_tables

2 WHERE owner = 'ROBINSON';

OWNER TABLE_NAME TABLESPACE_NAME

--------------- ------------------------------ ------------------------------

ROBINSON ORDERS USERS

ROBINSON EMP USERS

ROBINSON EMP2 USERS

--將emp2表移動到tbs1表空間

SQL> ALTER TABLE robinson.emp2 MOVE TABLESPACE TBS1;

Table altered.

--再次檢視emp2已位於tbs1表空間

SQL> SELECT owner,table_name,tablespace_name FROM dba_tables

2 WHERE owner = 'ROBINSON';

OWNER TABLE_NAME TABLESPACE_NAME

--------------- ------------------------------ ------------------------------

ROBINSON EMP2 TBS1

ROBINSON ORDERS USERS

ROBINSON EMP USERS

五、表和列的註釋

使用COMMENT 語句給表或列新增註釋

表加註釋：

COMMENT ON TABLE tablename IS '.....'

SQL> COMMENT ON TABLE emp IS 'Emp is table contain all employees.';

Comment created.

列加註釋：

COMMENT ON COLUMN tablename.columnname IS '......';

SQL> COMMENT ON COLUMN emp.mgr IS 'MGR column is manager ID';

Comment created.

可以透過下列資料字典檢視檢視所新增的註釋:

–ALL_COL_COMMENTS

–USER_COL_COMMENTS

–ALL_TAB_COMMENTS

–USER_TAB_COMMENTS

SQL> SELECT * FROM user_tab_comments;

TABLE_NAME TABLE_TYPE COMMENTS

------------------------------ ----------- -----------------------------------------

EMP TABLE Emp is table contain all employees.

ORDERS TABLE

EMP2 TABLE

SQL> SELECT * FROM user_col_comments WHERE table_name = 'EMP';

TABLE_NAME COLUMN_NAME COMMENTS

------------------------------ ------------------------------ -------------------------

EMP ENAME

EMP JOB

EMP HIREDATE

EMP MGR MGR column is manager ID

六、截斷（清空）表

TRUNCATE TABLE tablename

TRUNCATE TABLE 語句:

刪除表中所有的資料，但保留結構

釋放表的儲存空間

不觸發表的刪除觸發器

TRUNCATE語句不能回滾

可以使用DELETE 語句刪除資料

SQL> SELECT COUNT(1) FROM emp;

COUNT(1)

----------

15

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL> SELECT COUNT(1) FROM emp;

COUNT(1)

----------

0

七、刪除表：DROP TABLE tablename

DROP TABLE dept80;

資料和結構都被刪除

所有正在執行的相關事物被提交

所有相關索引被刪除

DROP TABLE 語句不能回滾

所有基於該表扣檢視和別名依然保留但已無效

SQL> DROP TABLE emp;

Table dropped.

SQL> SELECT * FROM emp;

SELECT * FROM emp

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL 基礎-->建立和管理表

相關文章