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;