SQL基础 Data Difinition Language

来源：互联网发布：淄博网站排名优化编辑：程序博客网时间：2024/06/01 07:30

建表语句

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;

0 0