正文

MySQL全面瓦解4：数据定义-DDL

IP云
IP云V管理员/0 阅读
11/28
小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

前言

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;

删除数据库 

drop databases [if exists] dbname;

完整的写法如下： 

1 drop databases [if exists] o_dbname;

2 create database n_dbname;

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

测试一下： 

 1 mysql> show databases;

 2 +--------------------+

 3 | Database           |

 4 +--------------------+

 5 | information_schema |

 6 | buyerparty         |

 7 | buyerparty1        |

 8 | git_jeeshop        |

 9 | jz                 |

10 | kdmy               |

11 | kdmygf             |

12 | localsdk           |

13 | mgrcentercontrol   |

14 | mysql              |

15 | performance_schema |

16 | stroke_data        |

17 | test               |

18 +--------------------+

19 13 rows in set

20

21 mysql> drop database if exists test1;

22 Query OK, 0 rows affected

23

24 mysql> create database test1;

25 Query OK, 1 row affected

26

27 mysql> create database test1;

28 1007 - Can't create database 'test1'; database exists

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

表管理

创建表

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

1 create table tbname(

2 column_name_1 column_type_1[(n)] [constraints] [comment 'comment1'],

3 column_name_2 column_type_2[(n)] [constraints] [comment 'comment2'],

4 column_name_3 column_type_3[(n)] [constraints] [comment 'comment3']

5 )[table_options];
语法说明

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

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

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

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

约束说明
not null ：非空约束 
1 mysql> use test;

2 Database changed

3

4 mysql> create table if not exists `user1`(age int comment '年龄',name char(5) comment '姓名' not null);

5 Query OK, 0 rows affected

6

7 mysql> insert into user1 values(8,null);

8 1048 - Column 'name' cannot be null

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

default value：提供 字段默认值 
 1 mysql> use test;

 2 Database changed

 3

 4 mysql> create table if not exists `user2`(age int not null default 0 comment '年龄',name char(50) comment '姓名' not null);

 5 Query OK, 0 rows affected

 6

 7 mysql> insert into user2(name) values('brand');

 8 Query OK, 1 row affected

 9 mysql> select * from user2;

10 +-----+-------+

11 | age | name  |

12 +-----+-------+

13 |   0 | brand |

14 +-----+-------+

15 1 row in set

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

primary key：标识主键约束

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

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

 1 mysql> use test;

 2 Database changed

 3

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

 5 Query OK, 0 rows affected

 6

 7 mysql> insert into user3 values(1,20,'brand');

 8 Query OK, 1 row affected

 9

10 mysql> insert into user3 values(1,22,'sol');

11 1062 - Duplicate entry '1' for key 'PRIMARY'

12

13 mysql> insert into user3 values(2,22,'sol');

14 Query OK, 1 row affected

15

16 mysql> select * from user3;

17 +----+-----+-------+

18 | id | age | name  |

19 +----+-----+-------+

20 |  1 |  20 | brand |

21 |  2 |  22 | sol   |

22 +----+-----+-------+

23 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 mysql> create table if not exists `class`(classid int primary key,classname varchar(50));

 2 Query OK, 0 rows affected

 3

 4 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));

 5 Query OK, 0 rows affected

 6

 7 mysql> insert into `user4` values(1,20,'brand',1);

 8 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`))

 9

10 mysql> insert into `class` values(1,'grad 3');

11 Query OK, 1 row affected

12

13 mysql> insert into `user4` values(1,20,'brand',1);

14 Query OK, 1 row affected

15

16 mysql> select a.age as '年龄',a.name as '学生姓名',b.classname as '班级' from user4 a left join class b on a.cid = b.classid;

17 +------+----------+--------+

18 | 年龄 | 学生姓名 | 班级   |

19 +------+----------+--------+

20 |   20 | brand    | grad 3 |

21 +------+----------+--------+

22 1 row in set

几点说明：

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

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

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

unique key：标识唯一值约束

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

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

 1 mysql> create table `user5` (id int primary key,name varchar(50),ident char(18) unique key);

 2 Query OK, 0 rows affected

 3

 4 mysql> create table `user6` (id int primary key,name varchar(50),ident char(18) not null,sex int not null,unique key(ident,sex));

 5 Query OK, 0 rows affected

 6

 7 mysql> insert into `user5` values(1,'brand','012345678901234567');

 8 Query OK, 1 row affected

 9 mysql> insert into `user5` values(2,'sol','012345678901234567');

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

删除表 

1 drop table [if exists] tname;

修改表名、备注 

1 alter table o_tname rename [to] n_tname;

2 alter table tname comment 'memo'; 

复制表

仅复制架构 
1 create table tname like from_tname; 
 1 mysql> select * from `user7`;

 2 +----+-------+

 3 | id | name  |

 4 +----+-------+

 5 |  1 | brand |

 6 |  2 | sol   |

 7 |  3 | helen |

 8 +----+-------+

 9 3 rows in set

10

11 mysql> create table `user8` like `user7`;

12 Query OK, 0 rows affected

13

14 mysql> select *  from `user8`;

15 Empty set
复制架构+数据 
1 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

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

管理字段

添加字段 

1 alter table tname add column column_name column_type [constraints];
 1 mysql> select * from `user9`;

 2 +----+-------+

 3 | id | name  |

 4 +----+-------+

 5 |  1 | brand |

 6 |  2 | sol   |

 7 |  3 | helen |

 8 +----+-------+

 9 3 rows in set

10

11 mysql> alter table `user9` add column newcolumn int not null default 0;

12 Query OK, 0 rows affected

13 Records: 0  Duplicates: 0  Warnings: 0

14

15 mysql> select * from `user9`;

16 +----+-------+-----------+

17 | id | name  | newcolumn |

18 +----+-------+-----------+

19 |  1 | brand |         0 |

20 |  2 | sol   |         0 |

21 |  3 | helen |         0 |

22 +----+-------+-----------+

23 3 rows in set 

修改字段 

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

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

以下分别是modify和change示例： 

 1 mysql> desc `user9`;

 2 +-----------+-------------+------+-----+---------+-------+

 3 | Field     | Type        | Null | Key | Default | Extra |

 4 +-----------+-------------+------+-----+---------+-------+

 5 | id        | int(11)     | NO   |     | 0       |       |

 6 | name      | varchar(50) | YES  |     | NULL    |       |

 7 | newcolumn | int(11)     | NO   |     | 0       |       |

 8 +-----------+-------------+------+-----+---------+-------+

 9 3 rows in set

10

11 mysql> alter table `user9` modify column name varchar(100);

12 Query OK, 3 rows affected

13 Records: 3  Duplicates: 0  Warnings: 0

14

15 mysql> desc `user9`;

16 +-----------+--------------+------+-----+---------+-------+

17 | Field     | Type         | Null | Key | Default | Extra |

18 +-----------+--------------+------+-----+---------+-------+

19 | id        | int(11)      | NO   |     | 0       |       |

20 | name      | varchar(100) | YES  |     | NULL    |       |

21 | newcolumn | int(11)      | NO   |     | 0       |       |

22 +-----------+--------------+------+-----+---------+-------+

23 3 rows in set
 1 mysql> desc `user9`;

 2 +-----------+--------------+------+-----+---------+-------+

 3 | Field     | Type         | Null | Key | Default | Extra |

 4 +-----------+--------------+------+-----+---------+-------+

 5 | id        | int(11)      | NO   |     | 0       |       |

 6 | name      | varchar(100) | YES  |     | NULL    |       |

 7 | newcolumn | int(11)      | NO   |     | 0       |       |

 8 +-----------+--------------+------+-----+---------+-------+

 9 3 rows in set

10

11 mysql> alter table `user9` change column name name1 varchar(100);

12 Query OK, 0 rows affected

13 Records: 0  Duplicates: 0  Warnings: 0

14

15 mysql> desc `user9`;

16 +-----------+--------------+------+-----+---------+-------+

17 | Field     | Type         | Null | Key | Default | Extra |

18 +-----------+--------------+------+-----+---------+-------+

19 | id        | int(11)      | NO   |     | 0       |       |

20 | name1     | varchar(100) | YES  |     | NULL    |       |

21 | newcolumn | int(11)      | NO   |     | 0       |       |

22 +-----------+--------------+------+-----+---------+-------+

23 3 rows in set

删除字段 

1 alter table tname drop column col_name; 
 1 mysql> desc `user9`;

 2 +-----------+--------------+------+-----+---------+-------+

 3 | Field     | Type         | Null | Key | Default | Extra |

 4 +-----------+--------------+------+-----+---------+-------+

 5 | id        | int(11)      | NO   |     | 0       |       |

 6 | name1     | varchar(100) | YES  |     | NULL    |       |

 7 | newcolumn | int(11)      | NO   |     | 0       |       |

 8 +-----------+--------------+------+-----+---------+-------+

 9 3 rows in set

10

11 mysql> alter table `user9` drop column newcolumn;

12 Query OK, 0 rows affected

13 Records: 0  Duplicates: 0  Warnings: 0

14

15 mysql> desc `user9`;

16 +-------+--------------+------+-----+---------+-------+

17 | Field | Type         | Null | Key | Default | Extra |

18 +-------+--------------+------+-----+---------+-------+

19 | id    | int(11)      | NO   |     | 0       |       |

20 | name1 | varchar(100) | YES  |     | NULL    |       |

21 +-------+--------------+------+-----+---------+-------+

22 2 rows in set

为了表示严谨性，每一个都尝试一遍，确实很耗时，写到现在。

相关阅读

目录[+]