一、DDL是什么?
DDL:Data De[ine Language数据定义语⾔,主要⽤来对数据库、表进⾏⼀些管理操作。如:建库、删库、建表、修改表、删除表、对列的增删改等等
二、库的管理
1.创建库
create database [if not exists] 库名;
建库通⽤的写法
drop database if exists 旧库名;
create database 新库名;
2.删除库
drop databases [if exists] 库名;
3.表管理
3.1 创建表
create table 表名(
字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的⼀些设置];
注意:
- 在同⼀张表中,字段名不能相同
- 宽度和约束条件为可选参数,字段名和类型是必须的
- 最后⼀个字段后不能加逗号
- 类型是⽤来限制 字段 必须以何种数据类型来存储记录
- 类型其实也是对字段的约束(约束字段下的记录必须为XX类型)
- 类型后写的 约束条件 是在类型之外的 额外添加的约束
约束说明
not null:标识该字段不能为空
default value:为该字段设置默认值,默认值为value
primary key:标识该字段为该表的主键,可以唯⼀的标识记录,插⼊重复的会报错
这三个约束比较容易理解,另外两个约束用的比较少,需要特别说明一下。
foreign key:为表中的字段设置外键
语法:foreign key(当前表的列名) references 引⽤的外键表(外键表中字段名称
实操记录:
mysql> drop table IF EXISTS test1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop table IF EXISTS test2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table test1(
-> a int not null comment '字段a' primary key
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table test2(
-> b int not null comment '字段b',
-> ts1_a int not null,
-> foreign key(ts1_a) references test1(a)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 (a) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 (b,test2.ts1_a) values (1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+---+-------+
| b | ts1_a |
+---+-------+
| 1 | 1 |
+---+-------+
1 row in set (0.00 sec)
mysql> select * from test1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> insert into test2 (b,test2.ts1_a) values (2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bm2`.`test2`, CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`ts1_a`) REFERENCES `test1` (`a`))
mysql> insert into test2 (b,test2.ts1_a) values (2,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select * from test2;
+---+-------+
| b | ts1_a |
+---+-------+
| 1 | 1 |
| 2 | 1 |
+---+-------+
2 rows in set (0.00 sec)
mysql> insert into test1 (a) values (2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test2 (b,test2.ts1_a) values (2,2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test2;
+---+-------+
| b | ts1_a |
+---+-------+
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
+---+-------+
3 rows in set (0.00 sec)
mysql> select * from test1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
mysql>
说明:表示test2中ts1_a字段的值来源于表test5中的字段a。
注意⼏点:
• 两张表中需要建⽴外键关系的字段类型需要⼀致
• 要设置外键的字段不能为主键
• 被引⽤的字段需要为主键
• 被插⼊的值在外键表必须存在,如上⾯向test6中插⼊ts1_a为2的时候报错了,原因:2的值在test1表中不存在
unique key(uq):标识该字段的值是唯⼀的⽀持⼀个到多个字段,插⼊重复的值会报违反唯⼀约束,会插⼊失败。
mysql> create table test2(
b int not null comment '字段b',
c int not null comment '字段c' unique key);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test2 (b,c) values (1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 (b,c) values (2,1);
ERROR 1062 (23000): Duplicate entry '1' for key 'test2.c'
mysql>
支持多字段唯一,在插入数据时,多个字段的值跟上一次插入的完全一致,就会报错,只要有一个不一致就不会报错。
mysql> create table test2(
-> a int not null comment '字段a',
-> b int not null comment '字段b',
-> c int not null comment '字段c',
-> unique key(a,b,c)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert test2(a,b,c) values(1,2,3);
Query OK, 1 row affected (0.00 sec)
mysql> insert test2(a,b,c) values(1,2,3);
ERROR 1062 (23000): Duplicate entry '1-2-3' for key 'test2.a'
mysql> insert test2(a,b,c) values(1,3,3);
Query OK, 1 row affected (0.00 sec)
mysql> insert test2(a,b,c) values(1,3,4);
Query OK, 1 row affected (0.00 sec)
mysql> insert test2(a,b,c) values(2,3,4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 1 | 3 | 3 |
| 1 | 3 | 4 |
| 2 | 3 | 4 |
+---+---+---+
4 rows in set (0.00 sec)
mysql>
auto_increment:标识该字段的值⾃动增长(整数类型,⽽且为主键)
实操记录:
mysql> create table test2 (
-> a int not null AUTO_INCREMENT PRIMARY KEY comment '字段a',
-> b int not null comment '字段c',
-> c int not null comment '字段b'
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert test2(b,c) values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert test2(b,c) values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert test2(b,c) values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert test2(b,c) values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 2 |
| 2 | 1 | 2 |
| 3 | 1 | 2 |
| 4 | 1 | 2 |
| 5 | 1 | 2 |
+---+---+---+
5 rows in set (0.00 sec)
字段a为⾃动增长,默认值从1开始,每次+1 关于⾃动增长字段的初始值、步长可以在mysql中进⾏设置,⽐如设置初始值为1万,每次增长10
注意:
⾃增长列当前值存储在内存中,数据库每次重启之后,会查询当前表中⾃增列的最⼤值作
为当前值。
3.2 删除表
删除表
drop table [if exists] 表名;
3.3 修改表名
alter table 表名 rename [to] 新表名;
3.4 表设置备注
alter table 表名 comment '备注信息';
3.5 复制表(只复制表结构)
create table 表名 like 被复制的表名;
3.6 复制表结构+数据
create table 表名 [as] select 字段,... from 被复制的表 [where 条件];
实操记录:
mysql> create table books1 as select * from books;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table books;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table
|+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| books | CREATE TABLE `books` (
`b_id` int NOT NULL AUTO_INCREMENT COMMENT '图书类别ID',
`bt_id` int NOT NULL COMMENT '图书类别id',
`number` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图书的编号',
`b_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图书名称',
`author` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '作者',
`publisher` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '出版社',
`total` int NOT NULL COMMENT '总数量',
`rental_unit` int NOT NULL COMMENT '租金/天 多少分钱',
`over_due_unit` int NOT NULL COMMENT '逾期租金/天 多少分钱',
`status` tinyint NOT NULL DEFAULT '1' COMMENT '图书的状态(true/false)',
PRIMARY KEY (`b_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> show create table books1;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table
|+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| books1 | CREATE TABLE `books1` (
`b_id` int NOT NULL DEFAULT '0' COMMENT '图书类别ID',
`bt_id` int NOT NULL COMMENT '图书类别id',
`number` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图书的编号',
`b_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图书名称',
`author` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '作者',
`publisher` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '出版社',
`total` int NOT NULL COMMENT '总数量',
`rental_unit` int NOT NULL COMMENT '租金/天 多少分钱',
`over_due_unit` int NOT NULL COMMENT '逾期租金/天 多少分钱',
`status` tinyint NOT NULL DEFAULT '1' COMMENT '图书的状态(true/false)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> drop table books1;
Query OK, 0 rows affected (0.00 sec)
mysql> create table books2 like books;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into books2 select * from books;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
3.7 表中列的管理
添加列
alter table 表名 add column 列名 类型 [列约束];
修改列
alter table 表名 modify column 列名 新类型 [约束];
或者
alter table 表名 change column 列名 新列名 新类型 [约束];
2种⽅式区别:modify不能修改列名,change可以修改列名
删除列
alter table 表名 drop column 列名;
总结
文章总体上介绍了Mysql的DDL操作,这些操作在生产工作中是比较常用的,需要熟练掌握。