创建表
创建表
CREATE TABLE
获取帮助
HELP CREATE TABLE;
创建表的方法
直接创建
语法
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(col_name1 type1 修饰符1,col_name2 type2 修饰符2,...)
-- 字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1,...)
UNIQUE KEY(col1,...)
-- 表选项
ENGINE [=] engine_name
ROW_FORMAT [=] { DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT }
注意：
- Storage Engine 是指表类型，也即在表创建时指明其使用的存储引擎
- 同一个库不同表可以使用不同的存储引擎
- 同一个库中表建议要使用同一种存储引擎类型
范例：创建表
-- 创建表，id的初始值为10
17:19:05 (root@(none)) [db1]> CREATE TABLE student (
-> 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 CHARACTER SET utf8;
Query OK, 0 rows affected
Time: 0.189s
-- 查看表结构
17:20:08 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| 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
Time: 0.009s
-- 插入一条为name为xiaoming age为20的数据
-- 由于id为主键，不能为NULL，并设置为自动增长，插入为NULL时， 会自动填充为自增长的数值，gender有个默认值，如果插入数据为空时，会自动填写默认值到数据中
17:21:10 (root@(none)) [db1]> insert into student (name,age)values('xiaoming',20);
Query OK, 1 row affected
Time: 0.024s
17:21:26 (root@(none)) [db1]> select * from student;
+----+----------+-----+--------+
| id | name | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20 | M |
+----+----------+-----+--------+
1 row in set
Time: 0.009s
-- 插入一条为name为xiaohong age为20 gender为F的数据
17:21:48 (root@(none)) [db1]> insert into student (name,age,gender)values('xiaoming',18,'f');
Query OK, 1 row affected
Time: 0.045s
17:21:50 (root@(none)) [db1]> select * from student;
+----+----------+-----+--------+
| id | name | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20 | M |
| 11 | xiaoming | 18 | F |
+----+----------+-----+--------+
2 rows in set
Time: 0.009s
范例2:时间类型
-- 创建一个时间戳的表，
17:36:30 (root@(none)) [db1]> CREATE TABLE testdate (
-> id int PRIMARY KEY AUTO_INCREMENT,
-> date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected
Time: 0.169s
17:36:47 (root@(none)) [db1]> desc testdate;
+-------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | <null> | auto_increment |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-----------+------+-----+-------------------+----------------+
2 rows in set
Time: 0.009s
-- 插入数据
17:40:23 (root@(none)) [db1]> insert into testdate () values();
Query OK, 1 row affected
Time: 0.003s
17:40:36 (root@(none)) [db1]> insert into testdate () values();
Query OK, 1 row affected
Time: 0.031s
17:40:38 (root@(none)) [db1]> insert into testdate () values();
Query OK, 1 row affected
Time: 0.008s
17:41:28 (root@(none)) [db1]> insert into testdate (id) values(5);
Query OK, 1 row affected
Time: 0.004s
-- 查看数据内容，时间戳就是插入数据时的时间
17:41:29 (root@(none)) [db1]> select * from testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2021-02-25 17:40:23 |
| 2 | 2021-02-25 17:40:36 |
| 3 | 2021-02-25 17:40:38 |
| 5 | 2021-02-25 17:41:28 |
+----+---------------------+
4 rows in set
Time: 0.008s
通过查询现存表创建新表，新表会被直接插入查询而来的数据
语法
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options]
[partition_options] select_statement
范例：
17:52:10 (root@(none)) [db1]> create table testdate_new select * from testdate;
Query OK, 4 rows affected
Time: 0.126s
-- 通过查询创建的新表，表结构跟之前的不一样
17:52:18 (root@(none)) [db1]> desc testdate;
+-------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | <null> | auto_increment |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-----------+------+-----+-------------------+----------------+
2 rows in set
Time: 0.009s
17:52:26 (root@(none)) [db1]> desc testdate_new;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-------+
| id | int(11) | NO | | 0 | |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-----------+------+-----+-------------------+-------+
2 rows in set
Time: 0.009s
17:52:40 (root@(none)) [db1]> select * from testdate_new;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2021-02-25 17:40:23 |
| 2 | 2021-02-25 17:40:36 |
| 3 | 2021-02-25 17:40:38 |
| 5 | 2021-02-25 17:41:28 |
+----+---------------------+
4 rows in set
Time: 0.009s
-- 复制指定的查询列的数据创建新表
17:56:32 (root@(none)) [db1]> create table testuser select user,host from mysql.user;
Query OK, 3 rows affected
Time: 0.168s
17:56:50 (root@(none)) [db1]> desc testuser;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| user | char(32) | NO | | | |
| host | char(60) | NO | | | |
+-------+----------+------+-----+---------+-------+
2 rows in set
Time: 0.009s
17:57:26 (root@(none)) [db1]> select * from testuser;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set
Time: 0.009s
通过复制现存表的表结构创建，但不复制数据
语法
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name |(LIKE old_tbl_name) }
范例:
18:00:42 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| 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
Time: 0.009s
-- 通过student表的表结构创建teacher新表，而没有student表的数据
18:01:33 (root@(none)) [db1]> create table teacher LIKE student;
Query OK, 0 rows affected
Time: 0.135s
18:01:45 (root@(none)) [db1]> desc teacher;
+--------+---------------------+------+-----+---------+----------------+
| 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
Time: 0.009s
18:01:56 (root@(none)) [db1]> select * from teacher;
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
0 rows in set
Time: 0.009s
18:03:19 (root@(none)) [db1]> select * from student;
+----+----------+-----+--------+
| id | name | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20 | M |
| 11 | xiaohong | 18 | F |
+----+----------+-----+--------+
2 rows in set
Time: 0.009s
表查看
查看支持的engine类型
SHOW ENGINES;
范例
09:58:41 (root@(none)) [db1]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | <null> | <null> | <null> |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
9 rows in set
Time: 0.009s
查看表
SHOW TABLES [FROM db_name];
范例:
09:59:24 (root@(none)) [db1]> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| student |
| t1 |
| teacher |
| testdate |
| testdate_new |
| testuser |
+---------------+
6 rows in set
Time: 0.009s
查看表结构
DESC [db_name].tb_name;
SHOW COLUMNS FROM [db_name].tb_name;
范例:
10:01:51 (root@(none)) [db1]> SHOW COLUMNS FROM student;
+--------+---------------------+------+-----+---------+----------------+
| 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
Time: 0.009s
10:02:34 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| 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
Time: 0.009s
查看表创建命令
SHOW CREATE TABLE tb_name;
范例:
10:04:24 (root@(none)) [db1]> SHOW CREATE TABLE student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `name` varchar(20) NOT NULL,\n `age` tinyint(3) unsigned DEFAULT NULL,\n `gender` enum('M','F') DEFAULT 'M',\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Time: 0.009s
查看表状态
SHOW TABLE STATUS LIKE 'tb_name';
范例:
10:06:21 (root@(none)) [db1]> SHOW TABLE STATUS LIKE 'student'\G
***************************[ 1. row ]***************************
Name | student
Engine | InnoDB
Version | 10
Row_format | Dynamic
Rows | 2
Avg_row_length | 8192
Data_length | 16384
Max_data_length | 0
Index_length | 0
Data_free | 0
Auto_increment | 12
Create_time | 2021-02-25 17:19:05
Update_time | 2021-02-25 17:30:30
Check_time | <null>
Collation | utf8_general_ci
Checksum | <null>
Create_options |
Comment |
1 row in set
Time: 0.002s
查看指定库中所有表的状态
SHOW TABLE STATUS FROM db_name;
范例:
10:13:42 (root@(none)) [db1]> SHOW TABLE STATUS FROM db1\G
***************************[ 1. row ]***************************
Name | student
Engine | InnoDB
Version | 10
Row_format | Dynamic
Rows | 2
Avg_row_length | 8192
Data_length | 16384
Max_data_length | 0
Index_length | 0
Data_free | 0
Auto_increment | 12
Create_time | 2021-02-25 17:19:05
Update_time | 2021-02-25 17:30:30
Check_time | <null>
Collation | utf8_general_ci
Checksum | <null>
Create_options |
Comment |
***************************[ 2. row ]***************************
Name | t1
Engine | InnoDB
Version | 10
Row_format | Dynamic
Rows | 2
Avg_row_length | 8192
Data_length | 16384
Max_data_length | 0
Index_length | 0
Data_free | 0
Auto_increment | 4294967295
Create_time | 2021-02-25 15:43:19
Update_time | 2021-02-25 15:43:37
Check_time | <null>
Collation | utf8mb4_general_ci
Checksum | <null>
Create_options |
Comment |
***************************[ 3. row ]***************************
Name | teacher
Engine | InnoDB
Version | 10
Row_format | Dynamic
Rows | 0
Avg_row_length | 0
Data_length | 16384
Max_data_length | 0
Index_length | 0
Data_free | 0
Auto_increment | 1
Create_time | 2021-02-25 18:01:33
Update_time | <null>
Check_time | <null>
Collation | utf8_general_ci
Checksum | <null>
Create_options |
Comment |
***************************[ 4. row ]***************************
Name | testdate
Engine | InnoDB
Version | 10
Row_format | Dynamic
Rows | 4
Avg_row_length | 4096
Data_length | 16384
Max_data_length | 0
Index_length | 0
Data_free | 0
Auto_increment | 6
Create_time | 2021-02-25 17:36:30
Update_time | 2021-02-25 17:41:28
Check_time | <null>
Collation | utf8mb4_general_ci
Checksum | <null>
Create_options |
Comment |
***************************[ 5. row ]***************************
Name | testdate_new
Engine | InnoDB
Version | 10
Row_format | Dynamic
Rows | 4
Create_options |
Comment |
***************************[ 5. row ]***************************
Name | testdate_new
Engine | InnoDB
Version | 10
Row_format | Dynamic
Rows | 4
Avg_row_length | 4096
Data_length | 16384
Max_data_length | 0
Index_length | 0
Data_free | 0
Auto_increment | <null>
Create_time | 2021-02-25 17:52:10
Update_time | 2021-02-25 17:52:10
Check_time | <null>
Collation | utf8mb4_general_ci
Checksum | <null>
Create_options |
Comment |
***************************[ 6. row ]***************************
Name | testuser
Engine | InnoDB
Version | 10
Row_format | Dynamic
Rows | 3
Avg_row_length | 5461
Data_length | 16384
Max_data_length | 0
Index_length | 0
Data_free | 0
Auto_increment | <null>
Create_time | 2021-02-25 17:56:32
Update_time | 2021-02-25 17:56:32
Check_time | <null>
Collation | utf8mb4_general_ci
Checksum | <null>
Create_options |
Comment |
修改和删除表
删除表
DROP TABLE [IF EXISTS] tbl_name;
修改表
ALTER TABLE tbl_name
-- 字段
-- 添加字段:add
ADD col1 data_type [FIRST | AFTER col_name]
-- 删除字段:drop
DROP col_name
-- 修改字段:alter
ALTER (默认值) 修改字段名使用change,修改字段属性使用modify
范例:在现有表中添加字段
10:36:37 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| 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
Time: 0.009s
10:36:44 (root@(none)) [db1]> select * from student;
+----+----------+-----+--------+
| id | name | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20 | M |
| 11 | xiaohong | 18 | F |
+----+----------+-----+--------+
2 rows in set
Time: 0.009s
-- 添加字段，在name字段的后面，添加phone字段
10:38:39 (root@(none)) [db1]> alter table student ADD phone char(11) not null after name;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 0 rows affected
Time: 0.336s
10:38:52 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| name | varchar(20) | NO | | <null> | |
| phone | char(11) | NO | | <null> | |
| age | tinyint(3) unsigned | YES | | <null> | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set
Time: 0.009s
10:39:02 (root@(none)) [db1]> select * from student;
+----+----------+-------+-----+--------+
| id | name | phone | age | gender |
+----+----------+-------+-----+--------+
| 10 | xiaoming | | 20 | M |
| 11 | xiaohong | | 18 | F |
+----+----------+-------+-----+--------+
2 rows in set
Time: 0.009s
范例:在现有表中修改相关字段
-- 修改phone字段的名称为mobile，修改字段名称使用change
16:08:35 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| name | varchar(20) | NO | | <null> | |
| phone | char(11) | NO | | <null> | |
| age | tinyint(3) unsigned | YES | | <null> | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set
Time: 0.009s
16:10:55 (root@(none)) [db1]> alter table student change phone mobile char(11);
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 0 rows affected
Time: 0.181s
16:11:04 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| name | varchar(20) | NO | | <null> | |
| mobile | char(11) | YES | | <null> | |
| age | tinyint(3) unsigned | YES | | <null> | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set
Time: 0.009s
-- 修改mobile字段的数据类型为varchar(20)，修改数据类型使用modify
16:19:59 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| name | varchar(20) | NO | | <null> | |
| mobile | char(11) | YES | | <null> | |
| age | tinyint(3) unsigned | YES | | <null> | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set
Time: 0.009s
16:20:52 (root@(none)) [db1]> alter table student modify mobile varchar(20);
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 2 rows affected
Time: 0.243s
16:21:01 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| name | varchar(20) | NO | | <null> | |
| mobile | varchar(20) | YES | | <null> | |
| age | tinyint(3) unsigned | YES | | <null> | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set
Time: 0.009s
-- 修改表名:将s1的表明重命名为student
16:25:36 (root@(none)) [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| s1 |
| t1 |
| teacher |
| testdate |
| testdate_new |
| testuser |
+---------------+
6 rows in set
Time: 0.009s
16:25:42 (root@(none)) [db1]> desc s1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| name | varchar(20) | NO | | <null> | |
| mobile | varchar(15) | YES | | <null> | |
| age | tinyint(3) unsigned | YES | | <null> | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set
Time: 0.010s
16:25:54 (root@(none)) [db1]> alter table s1 rename student;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 0 rows affected
Time: 0.032s
16:26:00 (root@(none)) [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student |
| t1 |
| teacher |
| testdate |
| testdate_new |
| testuser |
+---------------+
6 rows in set
Time: 0.008s
16:26:08 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| name | varchar(20) | NO | | <null> | |
| mobile | varchar(15) | YES | | <null> | |
| age | tinyint(3) unsigned | YES | | <null> | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set
Time: 0.009s
-- 删除字段: 删除mobile字段
16:28:43 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | <null> | auto_increment |
| name | varchar(20) | NO | | <null> | |
| mobile | varchar(15) | YES | | <null> | |
| age | tinyint(3) unsigned | YES | | <null> | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set
Time: 0.009s
16:28:46 (root@(none)) [db1]> select * from student;
+----+----------+-------------+-----+--------+
| id | name | mobile | age | gender |
+----+----------+-------------+-----+--------+
| 10 | xiaoming | 12345678976 | 20 | M |
| 11 | xiaohong | 12345678978 | 18 | F |
+----+----------+-------------+-----+--------+
2 rows in set
Time: 0.009s
16:29:40 (root@(none)) [db1]> alter table student drop mobile;
-- You're about to run a destructive command.
-- Do you want to proceed? (y/n): y
-- Your call!
Query OK, 0 rows affected
Time: 0.214s
16:29:48 (root@(none)) [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| 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
Time: 0.009s
16:29:54 (root@(none)) [db1]> select * from student;
+----+----------+-----+--------+
| id | name | age | gender |
+----+----------+-----+--------+
| 10 | xiaoming | 20 | M |
| 11 | xiaohong | 18 | F |
+----+----------+-----+--------+
2 rows in set
Time: 0.009s
扫一扫
1039
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
