4.1 DDL语句
表：二维关系
设计表：遵循规范
定义：字段，索引
- 字段：字段名，字段数据类型，修饰符
- 约束，索引：应该创建在经常用作查询条件的字段上
4.1.1 创建表
创建表：
CREANTE TABILE获取帮助：
HELP CREATE TABLE创建表的方法
（1）直接创建
CREATE TABLE [IF NOT EXISTS] tal_name (col1 type1 修饰符,col2 type2 修饰符,...)
#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1,...)
UNIQUE KEY(col1,...)
#表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXD|COMPRESSED|REDUNDANT|COMPACT}注意：
- Storag Engine是指定表类型，也即在表创建时指明其使用的存储引擎
- 同一库中不同表可以使用不同的存储引擎
- 同一库中表建议要使用同一种存储引擎类型
范例：创建表
mysql> CREATE TABLE student (id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,gendder ENUM('M','F') default 'M')ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
#id字段以10初始值
mysql> 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 (0.00 sec)
mysql> insert student (name,age)values('xianming',20);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 10 | xianming | 20 | M |
+----+----------+------+--------+
1 row in set (0.00 sec)
mysql> insert student (name,age,gender)values('xiaohong',19,'F');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 10 | xianming | 20 | M |
| 11 | xiaohong | 19 | F |
+----+----------+------+--------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE employee (id int UNSIGNED NOT NULL,name varchar(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));
Query OK, 0 rows affected (0.01 sec)
mysql> DESC employee;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows 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.01 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 autoinc1 (col INT NOT NULL AUTO_INCCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO autoinc1 VALUES (NULL),(NULL),(NULL),(NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 3 |
| 13 |
| 23 |
| 33 |
+-----+
4 rows in set (0.00 sec)
范例：timestamp
mysql> CREATE TABLE testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> 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 (0.00 sec)
mysql> INSERT INTO testdate (id) values (NULL);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2021-10-01 07:40:30 |
+----+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2021-10-01 07:40:30 |
| 2 | 2021-10-01 07:41:06 |
+----+---------------------+
2 rows in set (0.00 sec)
（2）通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_optiones] [partition_options] select_statement范例：
MariaDB [db1]> CREATE TABLE user SELECT user,host,password FROM mysql.user;
Query OK, 4 rows affected (0.009 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| user |
+---------------+
1 row in set (0.000 sec)
MariaDB [db1]> desc user;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| user | char(80) | NO | | | |
| host | char(60) | NO | | | |
| password | char(41) | NO | | | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.001 sec)
（3）通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tal_name | (LIKE old_tbl_name) }范例：
MariaDB [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 (0.001 sec)
MariaDB [db1]> CREATE TABLE teacher LIKE student;
Query OK, 0 rows affected (0.012 sec)
MariaDB [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 (0.002 sec)4.1.2 表查看
查看支持的engine类型
SHOW ENGINES;查看表：
SHOW TABLES [FROM db_name]范例：
MariaDB [db1]> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| student |
| teacher |
| user |
+---------------+
3 rows in set (0.001 sec)查看表结构：
DESC [db_name.]tbl_name
SHOW COLUMNS FROM [db_name.]tbl_name范例：
MariaDB [db1]> DESC db1.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 (0.001 sec)
MariaDB [db1]> SHOW COLUMNS FROM db1.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 (0.001 sec)查看表创建命令：
SHOW CREATE TABLE tbl_nameMariaDB [db1]> SHOW CREATE TABLE student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`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=10 DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)查看表状态：
SHOW TABLE STATUS LIKE 'tbl_name'范例：
MariaDB [db1]> SHOW TABLE STATUS LIKE 'student'\G;
*************************** 1. row ***************************
Name: student
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: 10
Create_time: 2021-10-01 16:07:38
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.001 sec)查看库中所有表状态
SHOW TABLE STATUS FROM db_name范例：
MariaDB [db1]> SHOW TABLE STATUS FROM db1\G;
*************************** 1. row ***************************
Name: student
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: 10
Create_time: 2021-10-01 16:07:38
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
*************************** 2. 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-10-01 16:08:20
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
*************************** 3. row ***************************
Name: user
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-10-01 15:58:41
Update_time: 2021-10-01 15:58:41
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
3 rows in set (0.001 sec)4.1.3 修改和删除表
删除表
DROP TABLE [IF EXISTS] 'tbl_name';修改表
ALTER TABLE 'tbl_name'
#字段
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
#删除字段：drop
drop col
#修改字段：
alter(默认值)，change(字段名)，modify(字段属性)查看修改表帮助
HELP ALTER TABLE修改表范例：
#修改表名
ALTER TABLE student RENAME s1;
MariaDB [db1]> ALTER TABLE student RENAME s1;
Query OK, 0 rows affected (0.02 sec)
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| s1 |
+---------------+
1 row in set (0.01 sec)
#添加字段
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
MariaDB [db1]> ALTER TABLE s1 ADD phone varchar(11) AFTER name;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db1]> SELECT * FROM s1;
+----+----------+-------+------+--------+
| id | name | phone | age | gender |
+----+----------+-------+------+--------+
| 1 | xiaoming | NULL | 18 | M |
+----+----------+-------+------+--------+
1 row in set (0.00 sec)
#修改字段类型
ALTER TABLE s1 MODIFY phine int;
MariaDB [db1]> ALTER TABLE s1 MODIFY phone int;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [db1]> DESC s1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| phone | int(11) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
#修改字段名称和类型
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
MariaDB [db1]> ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
Query OK, 1 row affected (0.24 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [db1]> DESC s1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
#删除字段
ALTER TABLE s1 DROP COLUMN mobile;
MariaDB [db1]> ALTER TABLE s1 DROP mobile;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db1]> DESC s1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
#修改字符集
ALTER TABLE s1 character set utf8;
MariaDB [db1]> SHOW TABLE STATUS LIKE 's1'\G;
*************************** 1. row ***************************
Name: s1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 2
Create_time: 2021-10-01 18:35:48
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
MariaDB [db1]> ALTER TABLE s1 character set utf16;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db1]> SHOW TABLE STATUS LIKE 's1'\G;
*************************** 1. row ***************************
Name: s1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 2
Create_time: 2021-10-01 18:39:57
Update_time: NULL
Check_time: NULL
Collation: utf16_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
#修改数据类型和字符集
ALTER TABLE s1 CHANGE name name varchar(20) character set utf8;
mysql> SHOW full fields FROM s1;
+--------+---------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+---------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(20) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| age | tinyint(3) unsigned | NULL | YES | | NULL | | select,insert,update,references | |
| genter | enum('M','F') | utf8_general_ci | YES | | M | | select,insert,update,references | |
+--------+---------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE s1 CHANGE name name varchar(20) character set utf8;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW full fields FROM s1;
+--------+---------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+---------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| age | tinyint(3) unsigned | NULL | YES | | NULL | | select,insert,update,references | |
| genter | enum('M','F') | utf8_general_ci | YES | | M | | select,insert,update,references | |
+--------+---------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.00 sec)
#添加字段
ALTER TABLE s1 ADD gender ENUM('M','F');
ALTER TABLE s1 modify is_del bool default false;
mysql> ALTER TABLE s1 ADD is_del ENUM('m','f');
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW full fields FROM s1;
+--------+---------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+---------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| age | tinyint(3) unsigned | NULL | YES | | NULL | | select,insert,update,references | |
| genter | enum('M','F') | utf8_general_ci | YES | | M | | select,insert,update,references | |
| is_del | enum('m','f') | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
+--------+---------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE s1 modify is_del bool default false;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW full fields FROM s1;
+--------+---------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+---------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| age | tinyint(3) unsigned | NULL | YES | | NULL | | select,insert,update,references | |
| genter | enum('M','F') | utf8_general_ci | YES | | M | | select,insert,update,references | |
| is_del | tinyint(1) | NULL | YES | | 0 | | select,insert,update,references | |
+--------+---------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
5 rows in set (0.01 sec)
mysql> INSERT INTO s1 (name,age)values('xiaoming',18);
Query OK, 1 row affected (0.00 sec)
mysql> select * from s1;
+----+----------+------+--------+--------+
| id | name | age | genter | is_del |
+----+----------+------+--------+--------+
| 1 | xiaoming | 18 | M | 0 |
+----+----------+------+--------+--------+
1 row in set (0.00 sec)
#修改字段名和类型
ALTER TABLE s1 CHANGE si sid int UNSIGNED NOT NULL PRIMARY KEY;
mysql> SHOW full fields FROM s1;
+--------+---------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+---------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| name | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| age | tinyint(3) unsigned | NULL | YES | | NULL | | select,insert,update,references | |
| genter | enum('M','F') | utf8_general_ci | YES | | M | | select,insert,update,references | |
| is_del | tinyint(1) | NULL | YES | | 0 | | select,insert,update,references | |
+--------+---------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE s1 CHANGE id sid int UNSIGNED NOT NULL;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SHOW full fields FROM s1;
+--------+---------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+---------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| sid | int(10) unsigned | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| age | tinyint(3) unsigned | NULL | YES | | NULL | | select,insert,update,references | |
| genter | enum('M','F') | utf8_general_ci | YES | | M | | select,insert,update,references | |
| is_del | tinyint(1) | NULL | YES | | 0 | | select,insert,update,references | |
+--------+---------------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
5 rows in set (0.00 sec)
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM s1;
ALTER TABLE t1 ADD PRIMARY KEY (stuid);
ALTER TABLE t1 DROP PRIMARY KEY;
MariaDB [db1]> SHOW full fields FROM s1;
+-------+------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(20) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| age | tinyint(4) | NULL | YES | | NULL | | select,insert,update,references | |
+-------+------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.001 sec)
MariaDB [db1]> ALTER TABLE s1 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.018 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db1]> SHOW full fields FROM s1;
+-------+------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | | NULL | | select,insert,update,references | |
| name | varchar(20) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| age | tinyint(4) | NULL | YES | | NULL | | select,insert,update,references | |
+-------+------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.001 sec)
MariaDB [db1]> ALTER TABLE s1 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.021 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [db1]> SHOW full fields FROM s1;
+-------+------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(20) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| age | tinyint(4) | NULL | YES | | NULL | | select,insert,update,references | |
+-------+------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.002 sec)