DDL：CREATE，DROP，ALTER
- 表：二维关系
- 设计表：遵循规范
- 定义：字段，索引
- 字段：字段名，字段数据类型，修饰符
- 约束，索引：应该创建在经常用作查询条件的字段上
1、创建表
CREATE TABLE
帮助：HELP CREATE TABLE
# 直接创建表
CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符, ...)
# 字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
# 表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}- Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎
- 同一库中不同表可以使用不同的存储引擎
- 同一个库中表建议要使用同一种存储引擎类型
# 范例
mysql> create table test1 (
-> id int unsigned auto_increment primary key,
-> name varchar(20) not null,
-> age tinyint unsigned,
-> gender enum('M','F') default 'M'
-> )
-> engine=innodb auto_increment=10 default charset=utf8;
mysql> desc test1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> insert test1 (name,age)values('xiaohu',20);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test1;
+----+--------+------+--------+
| id | name | age | gender |
+----+--------+------+--------+
| 10 | xiaohu | 20 | M |
+----+--------+------+--------+
1 row in set (0.00 sec)# auto_increment 属性
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
mysql> set @@auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 3 |
+--------------------------+-------+
2 rows in set (0.00 sec)# 时间类型
mysql> create table datetime1 (id int auto_increment primary key,date timestamp default current_timestamp not null);
Query OK, 0 rows affected (0.00 sec)
mysql> desc datetime1;
+-------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-----------+------+-----+-------------------+----------------+
2 rows in set (0.00 sec)
mysql> insert datetime1 ()values(),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from datetime1;
+----+---------------------+
| id | date |
+----+---------------------+
| 3 | 2022-11-15 21:45:06 |
| 13 | 2022-11-15 21:45:06 |
| 23 | 2022-11-15 21:45:06 |
+----+---------------------+
3 rows in set (0.00 sec)# 通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options]
[partition_options] select_statement
# 范例
mysql> create table user1 select user,host from mysql.user;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc user1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| user | char(32) | NO | | | |
| host | char(60) | NO | | | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from user1;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)# 通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE
old_tbl_name) }
# 范例
mysql> desc test1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> create table test2 like test1;
Query OK, 0 rows affected (0.00 sec)
mysql> desc test2;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)# 创建外键表
mysql> create table test_for (id int primary key auto_increment,name varchar(10), test1_id int unsigned,foreign key(test1_id) referennces test1(id));
Query OK, 0 rows affected (0.01 sec)
mysql> desc test1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc test_for;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| test1_id | int(10) unsigned | YES | MUL | NULL | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show create table test_for;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_for | CREATE TABLE `test_for` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`test1_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `test1_id` (`test1_id`),
CONSTRAINT `test_for_ibfk_1` FOREIGN KEY (`test1_id`) REFERENCES `test1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test1;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 10 | xiaohu | 20 | M |
| 13 | xiaowang | 11 | F |
| 23 | xiaoming | 22 | F |
| 33 | xiaoniu | 24 | M |
+----+----------+------+--------+
4 rows in set (0.00 sec)
mysql> insert test_for (name,test1_id)values('wangning',10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_for;
+----+----------+----------+
| id | name | test1_id |
+----+----------+----------+
| 3 | wangning | 10 |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> insert test_for (name,test1_id)values('ailun',11);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`test_for`, CONSTRAINT `test_for_ibfk_1` FOREIGN KEY (`test1_id`) REFERENCES `test1` (`id`))2、表查看
# 查看表
SHOW TABLES [FROM db_name]
# 查看表创建命令
SHOW CREATE TABLE tbl_name
# 查看表结构
DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name
# 查看表状态
SHOW TABLE STATUS LIKE 'tbl_name'
# 查看支持的engine类型
SHOW ENGINES;
# 范例
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| auto_inc1 |
| datetime1 |
| test1 |
| test2 |
| test_for |
| user1 |
+---------------+
6 rows in set (0.00 sec)
mysql> show create table test1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc test1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show table status;
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| auto_inc1 | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 | 0 | 0 | 43 | 2022-11-15 21:40:39 | 2022-11-15 21:41:19 | NULL | utf8_bin | NULL | | |
| datetime1 | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 0 | 0 | 33 | 2022-11-15 21:43:30 | 2022-11-15 21:45:06 | NULL | utf8_bin | NULL | | |
| test1 | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 | 0 | 0 | 43 | 2022-11-15 21:34:07 | 2022-11-15 21:54:38 | NULL | utf8_general_ci | NULL | | |
| test2 | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2022-11-15 21:48:06 | NULL | NULL | utf8_general_ci | NULL | | |
| test_for | InnoDB | 10 | Dynamic | 1 | 16384 | 16384 | 0 | 16384 | 0 | 23 | 2022-11-15 21:50:56 | 2022-11-15 21:58:07 | NULL | utf8_bin | NULL | | |
| user1 | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 0 | 0 | NULL | 2022-11-15 21:46:52 | 2022-11-15 21:46:52 | NULL | utf8_bin | NULL | | |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
6 rows in set (0.00 sec)
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
3、修改和删除表
# 修改表
ALTER TABLE 'tbl_name'
# 字段：
# 添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
# 删除字段：drop
# 修改字段：
alter（默认值）, change（字段名）, modify（字段属性）# 删除表
DROP TABLE [IF EXISTS] 'tbl_name';
# 范例
#修改表名
ALTER TABLE test1 RENAME s1;
#添加字段
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
#修改字段类型
ALTER TABLE s1 MODIFY phone int;
#修改字段名称和类型
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
#删除字段
ALTER TABLE s1 DROP COLUMN mobile;
#修改字符集
ALTER TABLE s1 character set utf8;
#修改数据类型和字符集
ALTER TABLE s1 change name name varchar(20) character set utf8;
#添加字段
ALTER TABLE test1 ADD gender ENUM('m','f');
alter table test1 modify is_del bool default false;
#修改字段名和类型
ALETR TABLE test1 CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
#删除字段
ALTER TABLE test1 DROP age;
#查看表结构
DESC test1;
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;
#添加外键
ALTER TABLE test1 add foreign key(TeacherID) references teachers(tid);
#删除外键
SHOW CREATE TABLE test1 #查看外键名
ALTER TABLE test1 drop foreign key <外键名>;© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
