MySQL ddl语句的使用

前言

SQL的语言分类主要包含如下几种：

 

DDL	数据定义语言	create、drop、alter	数据定义语言 create、drop、alter 语句 。
DML	数据操纵语言	insert、delete、update	定义对数据库记录的增、删、改操作。
DQL	数据库查询语言	select	定义对数据库记录的查询操作。
DCL	数据库控制语言	grant、remove	定义对数据库、表、字段、用户的访问权限和安全级别。 （授权grant,收回权限revoke等）。
TCL	事务控制语言	set autocommit=0、 start transaction、 savepoint、commit、rollback	定义对数据库的事务操作。

 

这小节主要了解下数据定义语言DDL(Data Define Language)。我们用它对数据库、表进行一些管理操作（创建、删除、修改等），比如：建库、删库、建表、修改表、删除表、对字段的增删改等，库表结构的管理。

接下来我们逐一来说明（下文[]中的内容属于可选项）。

数据库管理

创建数据库

?

1	create database [if not exists] dbname;

删除数据库

?

1	drop databases [if exists] dbname;

完整的写法如下：

?

1 2	drop databases [if exists] o_dbname; create database n_dbname;

 o_dbname 代表旧的数据库名，n_dbname 代表新的数据库名。

测试一下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

mysql> show databases; + --------------------+ | Database   | + --------------------+ | information_schema | | buyerparty   | | buyerparty1  | | git_jeeshop  | | jz     | | kdmy    | | kdmygf    | | localsdk   | | mgrcentercontrol | | mysql    | | performance_schema | | stroke_data  | | test    | + --------------------+ 13 rows in set   mysql> drop database if exists test1; Query OK, 0 rows affected   mysql> create database test1; Query OK, 1 row affected   mysql> create database test1; 1007 - Can 't create database ' test1'; database exists

通过上面的测试可以知道：删除之前要先判断数据库是否存在，否则会报出异常；同时创建之前也要判断是否存在，如果存在则会提示已存在。

表管理

创建表

在数据库中一张表的基本语法格式如下：

?

1 2 3 4 5

create table tbname( column_name_1 column_type_1[(n)] [constraints] [comment 'comment1' ], column_name_2 column_type_2[(n)] [constraints] [comment 'comment2' ], column_name_3 column_type_3[(n)] [constraints] [comment 'comment3' ] )[table_options];

语法说明

1、column_name是指字段名；column_type指的是字段类型（CHAR、INT等）；n代表字段宽度，可选；constraints 约束，可选；comment 为字段备注，可以对字段详细描述。

2、同一个表里面，column_name不能相同 

3、字段名和类型为必选，其他均为可选参数

4、类型限制了 字段 的存储格式，必须以给定的数据类型来存储，并可以额外添加的约束

约束说明

not null：非空约束

?

1 2 3 4 5 6 7 8

mysql> use test; Database changed   mysql> create table if not exists `user1`(age int comment '年龄' , name char (5) comment '姓名' not null ); Query OK, 0 rows affected   mysql> insert into user1 values (8, null ); 1048 - Column 'name' cannot be null

建表的时候，对name字段做了非空约束，这时候传入的值为null，就会有错误提示。所以非空约束的目的是保证字段不为空。

default value：提供字段默认值

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> use test; Database changed   mysql> create table if not exists `user2`(age int not null default 0 comment '年龄' , name char (50) comment '姓名' not null ); Query OK, 0 rows affected   mysql> insert into user2( name ) values ( 'brand' ); Query OK, 1 row affected mysql> select * from user2; + -----+-------+ | age | name | + -----+-------+ | 0 | brand | + -----+-------+ 1 row in set

设置了默认值之后，如果在写入数据时，不指定值，他会自动取默认值0。

primary key：标识主键约束
设置该字段为表的主键，全局唯一标识录，插入重复时报错。

有两种表现方式：一种是直接在字段约束中跟上；一种是字段都声明完了之后，在结尾加上，与上一个字段之间用逗号隔开。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> use test; Database changed   mysql> create table if not exists `user3`(id int primary key ,age int not null default 0 comment '年龄' , name char (50) comment '姓名' not null ); Query OK, 0 rows affected   mysql> insert into user3 values (1,20, 'brand' ); Query OK, 1 row affected   mysql> insert into user3 values (1,22, 'sol' ); 1062 - Duplicate entry '1' for key 'PRIMARY'   mysql> insert into user3 values (2,22, 'sol' ); Query OK, 1 row affected   mysql> select * from user3; + ----+-----+-------+ | id | age | name | + ----+-----+-------+ | 1 | 20 | brand | | 2 | 22 | sol | + ----+-----+-------+ 2 rows in set

如上，主键必须保持值的唯一性，如果插入重复值，会提示违反主键约束

另外一种方式是在字段声明的尾部，可以支持多个主键，用逗号隔开并且不可重复，格式：primary key(字段1,字段2,字段n)，这种叫组合主键（或复合主键），举个栗子：

?

1	create table if not exists `user4`(id int ,age int not null default 0 comment '年龄' , name char (50) comment '姓名' not null , primary key (id, name ));

foreign key：标识外键约束
语法：foreign key(t1_columnname) references t2(columnname),t1 为当前表，t2为外键表，当前表和外键表有一个字段约束成外键。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> create table if not exists `class`(classid int primary key ,classname varchar (50)); Query OK, 0 rows affected   mysql> create table if not exists `user4`(id int primary key ,age int comment '年龄' , name char (50) comment '姓名' ,cid int not null , foreign key (cid) references class(classid)); Query OK, 0 rows affected   mysql> insert into `user4` values (1,20, 'brand' ,1); 1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`user4`, CONSTRAINT `user4_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `class` (`classid`))   mysql> insert into `class` values (1, 'grad 3' ); Query OK, 1 row affected   mysql> insert into `user4` values (1,20, 'brand' ,1); Query OK, 1 row affected   mysql> select a.age as '年龄' ,a. name as '学生姓名' ,b.classname as '班级' from user4 a left join class b on a.cid = b.classid; + ------+----------+--------+ | 年龄 | 学生姓名 | 班级 | + ------+----------+--------+ | 20 | brand | grad 3 | + ------+----------+--------+ 1 row in set

几点说明：

1、插入user4表的时候，会检查关联的外键classid的值是否存在，如果不存在就会报错误。如上述代码中第三段，classid=1的值在class表中不存在。

2、建立外键关系的两张表的对应字段，类型需要保持一致。

3、设置为外键的字段不能为本表的主键，而关联表的字段需要为主键。（所以外键cid关联到class表的classid字段为主键）。

unique key：标识唯一值约束
可以设置一个到多个字段，不允许重复值，重复会报违反唯一约束，导致插入失败。

同样的有两种定义方式，一种是直接在字段后设置，一种是定义完所有字段之后再设置。以下例子：

?

1 2 3 4 5 6 7 8 9 10

mysql> create table `user5` (id int primary key , name varchar (50),ident char (18) unique key ); Query OK, 0 rows affected   mysql> create table `user6` (id int primary key , name varchar (50),ident char (18) not null ,sex int not null , unique key (ident,sex)); Query OK, 0 rows affected   mysql> insert into `user5` values (1, 'brand' , '012345678901234567' ); Query OK, 1 row affected mysql> insert into `user5` values (2, 'sol' , '012345678901234567' ); 1062 - Duplicate entry '012345678901234567' for key 'ident'

第二段中演示了支持多字段，用逗号隔开，语法格式：unique key(字段1,字段2,字段n)；

第三段重复输入了ident的值，他就提示重复输入了。

auto_inc：标识自动增长

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> create table `user7` (id int auto_increment primary key , name varchar (50)); Query OK, 0 rows affected   mysql> insert into `user7`( name ) values ( 'brand' ),( 'sol' ),( 'helen' ); Query OK, 3 rows affected   Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from `user7`; + ----+-------+ | id | name | + ----+-------+ | 1 | brand | | 2 | sol | | 3 | helen | + ----+-------+ 3 rows in set

auto_increment 说明：

1、auto_increacement 的字段为自动增长，默认值从1开始，每次+1

2、自动增长字段的初始值、步长可以在mysql中进行设置，比如设置初始值为1万，步长每次增长10

3、自增列当前值存储在内存中，数据库重启后，会查询当前表中自增列max为当前值。

4、如果表数据被清空并重启数据库，自增列会从初始值开始。

删除表

?

1	drop table [if exists] tname;

修改表名、备注

?

1 2	alter table o_tname rename [ to ] n_tname; alter table tname comment 'memo' ;

复制表

仅复制架构

?

1	create table tname like from_tname;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> select * from `user7`; + ----+-------+ | id | name | + ----+-------+ | 1 | brand | | 2 | sol | | 3 | helen | + ----+-------+ 3 rows in set   mysql> create table `user8` like `user7`; Query OK, 0 rows affected   mysql> select * from `user8`; Empty set

复制架构+数据

?

1	create table tname [ as ] select column1,column2,... from from_tname [ where condition];

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> select * from `user7`; + ----+-------+ | id | name | + ----+-------+ | 1 | brand | | 2 | sol | | 3 | helen | + ----+-------+ 3 rows in set   mysql> create table `user9` select id, name from `user7`; Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0   mysql> select * from `user9`; + ----+-------+ | id | name | + ----+-------+ | 1 | brand | | 2 | sol | | 3 | helen | + ----+-------+ 3 rows in set

数据和架构都被复制过来了，这个超实用。

管理字段

添加字段

?

1	alter table tname add column column_name column_type [constraints];

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> select * from `user9`; + ----+-------+ | id | name | + ----+-------+ | 1 | brand | | 2 | sol | | 3 | helen | + ----+-------+ 3 rows in set   mysql> alter table `user9` add column newcolumn int not null default 0; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0   mysql> select * from `user9`; + ----+-------+-----------+ | id | name | newcolumn | + ----+-------+-----------+ | 1 | brand |   0 | | 2 | sol |   0 | | 3 | helen |   0 | + ----+-------+-----------+ 3 rows in set

修改字段

?

1 2	alter table tname modify column col_name new_col_type [constraints]; -- 修改类型、约束，不能修改字段名 alter table tname change column col_name new_col_name new_col_type [constraints]; -- 修改字段名、类型、约束

以下分别是modify和change示例：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> desc `user9`; + -----------+-------------+------+-----+---------+-------+ | Field  | Type  | Null | Key | Default | Extra | + -----------+-------------+------+-----+---------+-------+ | id  | int (11)  | NO |  | 0  |  | | name  | varchar (50) | YES |  | NULL |  | | newcolumn | int (11)  | NO |  | 0  |  | + -----------+-------------+------+-----+---------+-------+ 3 rows in set   mysql> alter table `user9` modify column name varchar (100); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0   mysql> desc `user9`; + -----------+--------------+------+-----+---------+-------+ | Field  | Type   | Null | Key | Default | Extra | + -----------+--------------+------+-----+---------+-------+ | id  | int (11)  | NO |  | 0  |  | | name  | varchar (100) | YES |  | NULL |  | | newcolumn | int (11)  | NO |  | 0  |  | + -----------+--------------+------+-----+---------+-------+ 3 rows in set

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> desc `user9`; + -----------+--------------+------+-----+---------+-------+ | Field  | Type   | Null | Key | Default | Extra | + -----------+--------------+------+-----+---------+-------+ | id  | int (11)  | NO |  | 0  |  | | name  | varchar (100) | YES |  | NULL |  | | newcolumn | int (11)  | NO |  | 0  |  | + -----------+--------------+------+-----+---------+-------+ 3 rows in set   mysql> alter table `user9` change column name name1 varchar (100); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0   mysql> desc `user9`; + -----------+--------------+------+-----+---------+-------+ | Field  | Type   | Null | Key | Default | Extra | + -----------+--------------+------+-----+---------+-------+ | id  | int (11)  | NO |  | 0  |  | | name1  | varchar (100) | YES |  | NULL |  | | newcolumn | int (11)  | NO |  | 0  |  | + -----------+--------------+------+-----+---------+-------+ 3 rows in set

删除字段

?

1	alter table tname drop column col_name;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> desc `user9`; + -----------+--------------+------+-----+---------+-------+ | Field  | Type   | Null | Key | Default | Extra | + -----------+--------------+------+-----+---------+-------+ | id  | int (11)  | NO |  | 0  |  | | name1  | varchar (100) | YES |  | NULL |  | | newcolumn | int (11)  | NO |  | 0  |  | + -----------+--------------+------+-----+---------+-------+ 3 rows in set   mysql> alter table `user9` drop column newcolumn; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0   mysql> desc `user9`; + -------+--------------+------+-----+---------+-------+ | Field | Type   | Null | Key | Default | Extra | + -------+--------------+------+-----+---------+-------+ | id | int (11)  | NO |  | 0  |  | | name1 | varchar (100) | YES |  | NULL |  | + -------+--------------+------+-----+---------+-------+ 2 rows in set

以上就是MySQL ddl语句的使用的详细内容，更多关于MySQL ddl语句的资料请关注服务器之家其它相关文章！

原文链接：https://www.cnblogs.com/wzh2010/p/13842993.html