SQL基礎 Data Difinition Language

標籤：des   io   ar   for   資料   sp   on   c   ad   

建表語句

CREATE TABLE TABLE_NAME
(
         COLUMN_NAME  DATATYPE [NULL|NOT NULL],
        COLUMN_NAME  DATATYPE [NULL|NOT NULL],
        …
        [CONSTRAINT]
);    

例子：

CREATE TABLETEST
(
ID VARCHAR(20) NOT NULL,
T_DATE DATE NOT NULL,
T_NUMBER NUMBER NOT NULL             注意結尾不要加逗號
);

查看錶結構語句

DESC TABLE_NAME;

修改表語句

ALTER TABLE TABLE_NAME
ADD COLUMN_NAME DATATYPE [NULL|NOT NULL] | MODIFY COLUMN_NAME DATATYPE [NULL|NOTNULL] | DROP CLOUMN_NAME [CASCADE CONSTRAINT]

例如

ALTER TABLE TEST
ADD T_ADD VACHAR(20) NOT
MODIFY T_NUMBER NUMBER(2,2)
DROP T_DATE;

刪除表語句                   

DROP TABLE TABLE_NAME;

建表語句中添加主鍵約束

PRIMARY KEY (COLUMN_NAME1, COLUMN_NAME1…)

例如：

CREATE TABLETEST
(
ID VARCHAR(20) NOT NULL,
T_DATE DATE NOT NULL,
T_NUMBER NUMBER NOT NULL,
PRIMARY KEY (ID)
);

修改表語句中添加主鍵約束

ALTER TABLE TABLE_NAME
ADD CONSTRAINTS CONSTRAINTS_NAME PRIMARY KEY (COLUMN_NAME1, COLUMN_NAME1…);

移除主鍵約束

ALTER TABLE TABLE_NAME
DROP CONSTRAINTS CONSTRAINTS_NAME;

建表語句中添加外鍵約束

CONSTRAINTS CONSTRAINTS_NAME FOREIGN KEY(COLUMN_NUME)//此表哪個是外鍵
PEFERENCE TABLE_NAME (COLUMN_NUME) //外鍵在哪個表哪個欄位
ON DELETE CASCADE;

修改表語句中添加外鍵約束

ALTER TABLE TABLE_NAME

CONSTRAINTS CONSTRAINTS_NAME FOREIGN KEY(COLUMN_NUME)
PEFERENCE TABLE_NAME (COLUMN_NUME)
ON DELETE CASCADE;

移除外鍵約束

ALTER TABLE TABLE_NAME
DROP CONSTRAINTS CONSTRAINTS_NAME;

建表語句中添加條件約束

CONSTRAINTS CONSTRAINTS_NAMECHECK(COLUMN_CONDITION)

修改語句中添加條件約束

ALTER TABLE TABLE_NAME

ADD CONSTRAINTS CONSTRAINTS_NAMECHECK(COLUMN_CONDITION);

刪除條件約束

ALTER TABLE TABLE_NAME

DROP CONSTRAINTS CONSTRAINTS_NAME;

 

DML

資料添加

INSERT INTO TABLE_NAME(COLUMN_NAME1,COLUMN_NAME2…) VALUES(DATA1,DATA2…);

通過其他的資料表向表中添加資料

INSERT INTO TABLE_NAME(COLUMN_NAME1,COLUMN_NAME2…) VALUES(SELECT COLUMN_NAME3 FROM TABLE_NAME1, SELECT COLUMN_NAME4 FROMTABLE_NAME2…);

建表的同時將需要的資料直接從別的資料表中提取出來

CREATE TABLE TABLE_NAME AS SELECTCOLUMN_NAME1, COLUMN_NAME2,…COLUMN_NAMEN FORM SOURCE_TABLE;

資料修改

UPDATE TABLE_NAME SET COLUMN_NAME1=DATA1,…COLUMN_NAME2=DATA2[WHERECONDITION];

資料刪除

DELETE FROM TABLE_NAME [WHERE COMDITION];

資料查詢

SELECT COLUMN_NUME1,…COLUMN_NUME2 FROMTABLE_NAME [WHERW CONDITON];

查詢表的全部欄位

SELECT * FROM TABLE-NAME [WHERE CONDITION];

其他資料庫操縱語句

TRUNCATE語句 是用於刪除資料表的全部資料，速度比DELETE快

TRUNCATE TABLE TABLE_NAME;

SQL基礎 Data Difinition Language