SQL基础 Data Difinition Language

建表语句

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

例子：

CREATE TABLE TEST
(
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|NOT NULL] | 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 TABLE TEST
(
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_NAME CHECK(COLUMN_CONDITION)

修改语句中添加条件约束

ALTER TABLE TABLE_NAME

ADD CONSTRAINTS CONSTRAINTS_NAME CHECK(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 FROM TABLE_NAME2…);

建表的同时将需要的数据直接从别的数据表中提取出来

CREATE TABLE TABLE_NAME AS SELECT COLUMN_NAME1, COLUMN_NAME2,…COLUMN_NAMEN FORM SOURCE_TABLE;

数据修改

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

数据删除

DELETE FROM TABLE_NAME [WHERE COMDITION];

数据查询

SELECT COLUMN_NUME1,…COLUMN_NUME2 FROM TABLE_NAME [WHERW CONDITON];

查询表的全部字段

SELECT * FROM TABLE-NAME [WHERE CONDITION];

其他数据库操纵语句

TRUNCATE语句 是用于删除数据表的全部数据，速度比DELETE 快

TRUNCATE TABLE TABLE_NAME;