SQL之DDL

 

一、DDL（Data Definition Language）数据库定义语言statements are used to define the database structure or schema.

DDL是SQL语言的四大功能之一。
用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束
DDL不需要commit.
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

1、Create

 

CREATE DATABASE 语句

SQL CREATE DATABASE 语法

CREATE DATABASE database_name

 

SQL CREATE DATABASE 实例

现在我们希望创建一个名为 "my_db" 的数据库。

我们使用下面的 CREATE DATABASE 语句：

CREATE DATABASE my_db

 

CREATE TABLE 语句

CREATE TABLE 语句用于创建数据库中的表。

SQL CREATE TABLE 语法

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

 

数据类型（data_type）规定了列可容纳何种数据类型。下面的表格包含了SQL中最常用的数据类型：

数据类型描述
  • integer(size)
  • int(size)
  • smallint(size)
  • tinyint(size)
 仅容纳整数。在括号内规定数字的最大位数。
  • decimal(size,d)
  • numeric(size,d)

容纳带有小数的数字。

"size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。
char(size)

容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。

在括号中规定字符串的长度。
varchar(size)

容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。

在括号中规定字符串的最大长度。
date(yyyymmdd) 容纳日期。

SQL CREATE TABLE 实例

本例演示如何创建名为 "Person" 的表。

该表包含 5 个列，列名分别是："Id_P"、"LastName"、"FirstName"、"Address" 以及 "City"：

CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

 

Id_P 列的数据类型是 int，包含整数。其余 4 列的数据类型是 varchar，最大长度为 255 个字符。

 

 

创建表CREATE TABLE语句总结  

一般有两种创建表的方法：一种是使用具有交互式创建和管理表的工具；另一种是可以直接用SQL语句操纵。

为利用CREATE TABLE创建表，必须给出下列信息：新表的名字，在关键字CREATE TABLE之后给出；表列的名字和定义，用逗号分隔。

 

[sql] 
  1. CREATE TABLE 表名  
  2. (列名1 数据类型1 列级完整性约束条件1 列级完整性约束条件2...,  
  3.  列名2  数据类型2 列级完整性约束条件3...,  
  4.  ...  
  5.  表级完整性约束条件);  

常用的完整性约束条件如下：

主码约束：PRIMARY KEY;

唯一值约束：UNIQUE;

非空值约束：NOT NULL;

限制取值范围：CHECK;

指定默认值：DEFAULT;

参照完整性约束：FOREIGN KEY

例：

CREATE TABLE Products  
(  
prod_id   CHAR(10)   NOT NULL,  
vend_id   CHAR(10)  NOT NULL,  
prod_price DECIMAL(8,2)   NOT NULL,  
prod_desc  VARCHAR(1000)  NULL  
);

 

MySQL语句忽略空格。语句可以在一个长行上输入，也可以分成许多行。强烈推荐使用某种缩进格式编写。

如果你仅仅想在一个表不存在时创建它，应该在表名后面给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配，它只是查看表名是否存在，并且仅在表名不存在时创建它。

 

  • PRIMARY KEY介绍

主键值必须唯一。即，表中的每个行必须有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

 

主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

例：

CREATE TABLE orderitems  
(  
order_num int NOT NULL,  
order_item int NOT NULL,  
prod_id char(10) NOT NULL,  
quantity int NOT NULL,  
item_price decimal(8,2) NOT NULL,  
PRIMARY KEY(order_num, order_item)  
);

 

 

  • AUTO_INCREMENT介绍

cust_id int NOT NULL AUTO_INCREMENT

 

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量，每次执行一个INSERT操作时，MySQL自动对该列增量(从而才有这个关键字AUTO_INCREMENT)，给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id，从而可以用作主键值。

每个表只允许一个AUTO_INCREMENT列，而且她必须被索引(如，通过使它成为主键)。

让MySQL生成(通过自动增量)主键的一个缺点是你不知道这些值都是谁。

如何在使用AUTO_INCREMENT列时获得这个值呢？可以用last_insert_id()函数获得这个值。如下所示：

SELECT last_insert_id() ;

此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句。

 

 

  • DEFAULT介绍

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。

 

例：

CREATE TABLE orderitems  
(  
order_num int NOT NULL,  
order_item int NOT NULL,  
prod_id char(10) NOT NULL,  
quantity int NOT NULL DEFAULT 1,  
item_price decimal(8,2) NOT NULL ,  
PRIMARY KEY(order_num, order_item)  
);

 

在此例子中，给该列的描述添加文本DEFAULT 1 指示MySQL，在未给出数量的情况下使用数量1。

许多数据库开发人员使用默认值而不是NULL值，特别是对用于计算或者数据分组的列更是如此。

 

CREATE INDEX 语句用于在表中创建索引。

在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

索引

您可以在表中创建索引，以便更加快速高效地查询数据。

用户无法看到索引，它们只能被用来加速搜索/查询。

注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

SQL CREATE INDEX 语法

在表上创建一个简单的索引。允许使用重复的值：

CREATE INDEX index_name
ON table_name (column_name)

 

注释："column_name" 规定需要索引的列。

SQL CREATE UNIQUE INDEX 语法

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

 

CREATE INDEX 实例

本例会创建一个简单的索引，名为 "PersonIndex"，在 Person 表的 LastName 列：

CREATE INDEX PersonIndex
ON Person (LastName)

 

如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：

CREATE INDEX PersonIndex
ON Person (LastName DESC)

 

假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

 

 

 

2、ALTER TABLE 语句

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

 

SQL ALTER TABLE 语法

如需在表中添加列，请使用下列语法:

ALTER TABLE table_name
ADD column_name datatype

 

要删除表中的列，请使用下列语法：

ALTER TABLE table_name 
DROP COLUMN column_name

 

注释：某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。

要改变表中列的数据类型，请使用下列语法：

ALTER TABLE table_name
ALTER COLUMN column_name datatype

 

改变数据类型实例

现在我们希望改变 "Persons" 表中 "Birthday" 列的数据类型。

我们使用下列 SQL 语句：

ALTER TABLE Persons
ALTER COLUMN Birthday year

 

请注意，"Birthday" 列的数据类型是 year，可以存放 2 位或 4 位格式的年份。

DROP COLUMN 实例

接下来，我们删除 "Person" 表中的 "Birthday" 列：

ALTER TABLE Person
DROP COLUMN Birthday

 

改变数据类型长度

 

 

标准SQL修改字段类型和长度语句：

ALTER TABLE tableName modify column columnName 类型;

 

例如Mysql的修改字段类型语句：

alter table test modify column name varchar(255);

 

Oracle修改字段类型和长度语句：

ALTER TABLE tableName modify(columnName 类型);

 

例如

alter table test modify(name varchar(255));

 

 

 

3、SQL 撤销索引、表以及数据库

通过使用 DROP 语句，可以轻松地删除索引、表和数据库。

SQL DROP INDEX 语句

我们可以使用 DROP INDEX 命令删除表格中的索引。

用于 Microsoft SQLJet (以及 Microsoft Access) 的语法:

DROP INDEX index_name ON table_name

用于 MS SQL Server 的语法:

DROP INDEX table_name.index_name

用于 IBM DB2 和 Oracle 语法:

DROP INDEX index_name

用于 MySQL 的语法:

ALTER TABLE table_name DROP INDEX index_name

 

SQL DROP TABLE 语句

DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）：

DROP TABLE 表名称

SQL DROP DATABASE 语句

DROP DATABASE 语句用于删除数据库：

DROP DATABASE 数据库名称

SQL TRUNCATE TABLE 语句

如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？

请使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）：

TRUNCATE TABLE 表名称

 

4、comment

 

　Oracle数据库中 comment （注释） 修改方法：分两种情况，一种是表注释，一种是字段注释。对表或表字段增加或修改注释内容的方法都是一样的。

　　一、表注释修改语法：comment on table 表名 is '注释内容';
　　二、字段注释修改语法：comment on column 表名.字段名 is '注释内容'。

mysql

创建表的时候写注释 create table test1 ( 
    field_name int comment '字段的注释' )comment='表的注释';   
2 修改表的注释 
alter table test1 comment '修改后的表的注释';   
3 修改字段的注释 
alter table test1 modify column field_name int comment '修改后的字段注释'; --注意：字段名和字段类型照写就行   
4 查看表注释的方法 --在生成的SQL语句中看 show  create  table  test1; --在元数据的表里面看

 

5、rename table语法
rename table tbl_name to new_tbl_name    [, tbl_name2 to new_tbl_name2] ...本语句用于对一个或多个表进行重命名。

 

总结，最后列出常用的sql语句

 

1、创建数据库

CREATE DATABASE my_db；

2、创建数据表

CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)；

3、创建索引  ---索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)；


4、ALTER TABLE 语句
如需在表中添加列，请使用下面的语法:

ALTER TABLE table_name
ADD column_name datatype

如需删除表中的列，请使用下面的语法

ALTER TABLE table_name
DROP COLUMN column_name

要改变表中列的数据类型，请使用下面的语法：

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

5、DROP
SQL DROP INDEX 语句

DROP INDEX index_name （oracle）

ALTER TABLE table_name DROP INDEX index_name (mysql)

 DROP TABLE 语句

DROP TABLE 表名称

DROP DATABASE 语句

DROP DATABASE 数据库名称

 TRUNCATE TABLE 语句(仅仅删除表格中的数据)

TRUNCATE TABLE 表名称

6、COMMENT
Oracle数据库中 comment （注释） 修改方法：分两种情况，一种是表注释，一种是字段注释。对表或表字段增加或修改注释内容的方法都是一样的。

　　一、表注释修改语法：comment on table 表名 is '注释内容';
　　二、字段注释修改语法：comment on column 表名.字段名 is '注释内容'。

7、rename table语法
rename table tbl_name to new_tbl_name    [, tbl_name2 to new_tbl_name2] ...本语句用于对一个或多个表进行重命名。

 

posted @ 2017-03-07 12:59  Blue●Sky  阅读(1213)  评论(0)  编辑  收藏  举报