DDL常用操作
DDL常用操作
DDL（Data Define Languge）：数据定义语言 create、drop、alter 语句，主要对数据库、表进行一些管理操作。比如：建库、删库、建表、修改表、删除表、对列的增删改等等。
数据库的操作
创建库
create database [if not exists] 库名;
删除库
drop databases [if exists] 库名;
表操作
创建表
#语法：
CREATE TABLE 表名(
字段名称1 数据类型[(宽度)] [约束类型] [comment '字段说明'],
字段名称2 数据类型[(宽度)] [约束类型] [comment '字段说明'],
字段名称3 数据类型[(宽度)] [约束类型] [comment '字段说明']
) [表的设置]
注意：
- 在同一张表中，字段名不能重复
- 宽度、约束、字段描述均为可选参数，字段名称和数据类型必须指定
- 最后一个字段不用加逗号
- 数据类型限制字段必须用什么数据类型存储记录
- 约束是为了对表中的数据进行限定，保证数据的正确和完整
- 表的设置是设置ENGINEL类型、自动增长、字符集、排序等等
约束说明
not null: 标识字段不为空
mysql> create table test1(a int not null comment '字段a');
Query OK, 0 rows affected (0.28 sec)
mysql> insert into test1 values (null);
ERROR 1048 (23000): Column 'a' cannot be null
mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
default value: 为字段设置默认值，默认值为value
mysql> drop table IF EXISTS test2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table test2(
-> a int not null,
-> b int not null default 0
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test2(a) values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test2;
+---+---+
| a | b |
+---+---+
| 1 | 0 |
+---+---+
1 row in set (0.00 sec)
#b取了默认值
primary key: 标识该字段为该表的主键，可以唯一的标识记录，插入重复的会报错
有两种写法，写在列后面，或者是在所有列之后再定义主键
1.在列后面插入
mysql> create table test3(
-> a int not null primary key
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test3 (a) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test3 (a) values (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
2.在列定义之后定义主键
mysql> create table test4(
-> a int not null,
-> b int not null default 0,
-> primary key(a)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test4(a,b) values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test4(a,b) values(1,1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
#也可以primary key(a,b)这样，设置多个字段为主键
#primary key(字段1，字段2，字段3)
foreign key: 为表中的字段设置外键
语法：foreign key(当前表的列名) references 引用的外键表(外键表中字段名)
mysql> create table test5(
-> a int not null primary key
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> create table test6(
-> b int not null,
-> fore_a int not null,
-> foreign key(fore_a) references test5(a)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test5 (a) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test6(b,fore_a) values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test6(b,fore_a) values(2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`test6`, CONSTRAINT `test6_ibfk_1` FOREIGN KEY (`fore_a`) REFERENCES `test5` (`a`))
#也就是说，test6中的字段fore_a来自test5的字段b
注意：
- 一个表可以有一到多个外键
- 建立外键时，数据类型要一致
- 外键可以不是这个表的主键，但必须和另外一个表的主键相对应
- 已经设置外键的字段，不能再设为主键
- 被引用的字段需要为主键
- 带有主键的那张表称为父表，含外键的是子表，必须先删除外键约束才能删除父表
- 在外键表插入值时，对应的主键表中的值必须存在。上面再test6插入fore为2的时候报错，是因为对应的主键表test5中不存在2
unique key(uq): 标识字段唯一，不能重复
支持一到多个字段，插入重复的值会违反约束，导致插入失败
一样有两种定义方式：
方式1：在字段后面定义：
mysql> create table test7(
-> a int not null unique key
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test7(a) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7(a) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'
方式2; 在所有列之后定义
mysql> create table test8(
-> a int not null ,
-> unique key(a)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test8(a) values(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test8(a) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'a'
#方式2支持多字段，多个之间用逗号隔开，语法：primary key(字段1,字段2,字段n)
auto_increment: 标识字段自动增长（整数类型，且为主键）
mysql> create table test9(
-> a int not null auto_increment primary key,
-> b int not null
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test9(b) values(10);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test9(b) values(20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test9;
+---+----+
| a | b |
+---+----+
| 1 | 10 |
| 2 | 20 |
+---+----+
2 rows in set (0.00 sec)
#字段a自动增长，默认从1开始，每次+1
#show variables like 'auto_inc%'; 查看自动增长的初始值和递增值
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.44 sec)
#调整自动增长的属性
mysql> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
mysql> set @@auto_increment_offset=2;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 2 |
+--------------------------+-------+
2 rows in set (0.00 sec)
#这种方式修改的是全局变量，整个数据库都会受到影响，不是某个库或表
#auto_increment_increment=10为每次增长的值为10，就是步长为10
#auto_increment_offset=2为自动增长的初始值
#mysql8可以在set后加persist来永久保存变量
#如set persist auto_increment_increment=1;
注意：
自增长列当前值存储在内存中，数据库每次重启之后，会查询当前表中自增列的最大值作为当前值，如果表数据被清空之后，数据库重启之后（重新从磁盘读取配置到内存，磁盘中保存的是初始值），自增列的值将从初始值开始
删除表
drop table [if exists] 表名;
修改表名
alter table 表名 rename [to] 新表名;
表加备注
alter table 表名 comment '备注信息';
复制表
只复制表结构
create table 表名 like 被复制的表名;
复制表结构和数据
create table 表名 [as] select 字段,... from 被复制的表 [where 条件];
示例：
#这里有一张叫aaa的表
mysql> select * from aaa;
+------+------+
| a | b |
+------+------+
| 1000 | 1 |
| 1001 | 2 |
| 1002 | 3 |
+------+------+
3 rows in set (0.01 sec)
#复制aaa中的a字段，且a字段中大于1000的记录，作为表bbb的数据
mysql> create table bbb as select a from aaa where a>1000;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from aaa;
+------+------+
| a | b |
+------+------+
| 1000 | 1 |
| 1001 | 2 |
| 1002 | 3 |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from bbb;
+------+
| a |
+------+
| 1001 |
| 1002 |
+------+
2 rows in set (0.00 sec)
列的管理
添加列
alter table 表名 add column 列名 类型 [列约束];
示例：
mysql> create table test10(
-> a int not null auto_increment primary key
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> alter table test10 add column b int not null default 0;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test10 add column c int not null default 0;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into test10(b) values(100);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test10;
+---+-----+---+
| a | b | c |
+---+-----+---+
| 1 | 100 | 0 |
+---+-----+---+
1 row in set (0.00 sec)
修改列
alter table 表名 modify column 列名 新类型 [约束];
或者
alter table 表名 change column 列名 新列名 新类型 [约束];
2种方式区别：modify不能修改列名，change可以修改列名
#查看test10的表结构
mysql> desc test10;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| a | int(11) | NO | PRI | NULL | auto_increment |
| b | int(11) | NO | | 0 | |
| c | int(11) | NO | | 0 | |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
modify修改字段b
mysql> alter table test10 modify b int not null default 100;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test10;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| a | int(11) | NO | PRI | NULL | auto_increment |
| b | int(11) | NO | | 100 | |
| c | int(11) | NO | | 0 | |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
change修改字段c
mysql> alter table test10 change c C int not null default 0;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test10;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| a | int(11) | NO | PRI | NULL | auto_increment |
| b | int(11) | NO | | 100 | |
| C | int(11) | NO | | 0 | |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
删除列
alter table 表名 drop column 列名;
示例;
mysql> desc test10;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| a | int(11) | NO | PRI | NULL | auto_increment |
| b | int(11) | NO | | 100 | |
| C | int(11) | NO | | 0 | |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
#删除C字段
mysql> alter table test10 drop column C;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test10;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| a | int(11) | NO | PRI | NULL | auto_increment |
| b | int(11) | NO | | 100 | |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select * from test10;
+---+-----+
| a | b |
+---+-----+
| 1 | 100 |
+---+-----+
1 row in set (0.00 sec)
