MySQL ddl语句的使用(2)-木庄网络博客

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

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：标识自动增长

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、如果表数据被清空并重启数据库，自增列会从初始值开始。

删除表

drop table [if exists] tname;

修改表名、备注

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

复制表

仅复制架构

create table tname like from_tname;

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

复制架构+数据

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

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

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

管理字段

添加字段

alter table tname add column column_name column_type [constraints];

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

修改字段

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示例：

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

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

删除字段

alter table tname drop column col_name;

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语句的资料请关注其它相关文章！

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容