MySQL基础（二）

最新推荐文章于 2025-05-28 01:55:06 发布

yfqh9588

最新推荐文章于 2025-05-28 01:55:06 发布

阅读量154

点赞数

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/qq_43635468/article/details/104677846

版权

数据完整性约束

数据完整性是指数据的正确性和相容性，是为了防止数据库中存在不符合语义的数据，即防止数据库中存在不正确的数据。在MySQL中提供了多种完整性约束。

主键约束

PRIMARY KEY
主键可以是表中的某一列，也可以是表中的多个列所构成的一个组合；其中，由多个列组合而成的主键也称为复合主键。
1、每一个表只能定义一个主键；
2、主键的值，也称键值，必须能够唯一表示表中的每一条记录，且不能为NULL；
3、复合主键不能包含不必要的多余列。当从一个复合主键中删除一列后，如果剩下的列构成的主键仍能满足唯一性原则，那么这个复合主键是不正确的；
4、一个列名在复合主键的列表中只能出现一次。

自增长约束

AUTO_INCREMENT
自增长必须为主键，设置为自增长后，默认从1开始，之后插入数据时若不指定，则该键值自动加一，也可以手动指定键值。

mysql> CREATE TABLE IF NOT EXISTS user1(
    -> id SMALLINT KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT user1 VALUES(1,'Tom');
Query OK, 1 row affected (0.29 sec)

#不指定时默认加一
mysql> INSERT user1(username) VALUE('Jerry');
Query OK, 1 row affected (0.05 sec)

#指定为NULL时默认加一
mysql> INSERT user1 VALUES(NULL,'King');
Query OK, 1 row affected (0.04 sec)

#指定为默认时也加一
mysql> INSERT user1 VALUES(DEFAULT,'ABC');
Query OK, 1 row affected (0.00 sec)

#可以指定为其他值
mysql> INSERT user1 VALUES(100,'BCD');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT user1(username) VALUE('CDE');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *FROM user1;
+-----+----------+
| id  | username |
+-----+----------+
|   1 | Tom      |
|   2 | Jerry    |
|   3 | King     |
|   4 | ABC      |
| 100 | BCD      |
| 101 | CDE      |
+-----+----------+
6 rows in set (0.30 sec)

在创建数据表时可以设置自增长起始值，也可以改变。

#设置自增长起始值为50
mysql> CREATE TABLE IF NOT EXISTS user2(
    -> id SMALLINT KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> )AUTO_INCREMENT=50;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT user2(username) VALUE('Jerry');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT *FROM user2;
+----+----------+
| id | username |
+----+----------+
| 50 | Jerry    |
+----+----------+
1 row in set (0.00 sec)

#更改自增长值为100
mysql> ALTER TABLE user2 AUTO_INCREMENT=100;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT user2(username) VALUE('Tom');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT *FROM user2;
+-----+----------+
| id  | username |
+-----+----------+
|  50 | Jerry    |
| 100 | Tom      |
+-----+----------+
2 rows in set (0.00 sec)

非空约束

NOT NULL
非空约约束就是限制必须为某个列提供值。空值（NULL）是不存在值，它既不是数字0，也不是空字符串，而是不存在、未知的情况。

mysql> CREATE TABLE IF NOT EXISTS user3(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password CHAR(32) NOT NULL,
    -> age TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> DESC user3;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| password | char(32)            | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
4 rows in set (0.08 sec)

mysql> INSERT user3(username,password) VALUES('KING','KING');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user3;
+----+----------+----------+------+
| id | username | password | age  |
+----+----------+----------+------+
|  1 | KING     | KING     | NULL |
+----+----------+----------+------+
1 row in set (0.00 sec)

mysql> INSERT user3(username,password) VALUES(NULL,NULL);
ERROR 1048 (23000): Column 'username' cannot be null

默认值约束

DEFAULT
设置默认值，在插入数据时若未赋值则采用该默认值，通常与非空配合使用。

mysql> CREATE TABLE IF NOT EXISTS user4(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password CHAR(32) NOT NULL,
    -> age TINYINT UNSIGNED DEFAULT 18,
    -> sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC user4;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| password | char(32)            | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | 18      |                |
| sex      | enum('              | NO   |     |         |                |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set, 3 warnings (0.05 sec)

mysql> INSERT user4(username,password) VALUES('KING','KING');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT user4 VALUES(2,'QUEEN','QUEEN',DEFAULT,'保密');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT user4 VALUES(3,'QUEEN','QUEEN',29,'保密');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user4;
+----+----------+----------+------+------+
| id | username | password | age  | sex  |
+----+----------+----------+------+------+
|  1 | KING     | KING     |   18 | 男   |
|  2 | QUEEN    | QUEEN    |   18 | 保密 |
|  3 | QUEEN    | QUEEN    |   29 | 保密 |
+----+----------+----------+------+------+
3 rows in set (0.00 sec)

唯一约束

UNIQUE KEY
唯一约束的值必须是唯一的，在MySQL中，唯一约束与主键之间存在以下两点区别。
1、一个表只能创建一个主键，但可以定义多个唯一约束；
2、定义主键约束时，系统会自动创建PRIMARY KEY索引，而定义候选键约束时，系统会自动创建UNIQUE索引。

mysql> CREATE TABLE IF NOT EXISTS user5(
    -> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> card CHAR(18) UNIQUE
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC user5;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | YES  | UNI | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> INSERT user5(username) VALUES('A');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT user5(username) VALUES('A1');
Query OK, 1 row affected (0.01 sec)

#当重复时会报错
mysql> INSERT user5(username) VALUES('A1');
ERROR 1062 (23000): Duplicate entry 'A1' for key 'username'
#NULL不算重复值
mysql> INSERT user5(username,card) VALUES('B1',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT user5(username,card) VALUES('B2',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user5;
+----+----------+------+
| id | username | card |
+----+----------+------+
|  1 | A        | NULL |
|  2 | A1       | NULL |
|  4 | B1       | NULL |
|  5 | B2       | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

修改表结构

首先创建一个数据表

mysql> CREATE TABLE IF NOT EXISTS user6(
    -> id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> password CHAR(32) NOT NULL,
    -> email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
    -> age TINYINT UNSIGNED DEFAULT 18,
    -> sex ENUM('男','女','保密') DEFAULT '保密',
    -> addre VARCHAR(200),
    -> salary FLOAT(6,2),
    -> regTime INT UNSIGNED,
    -> face CHAR(100) NOT NULL DEFAULT 'default.jpg'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC user6;
+----------+----------------------+------+-----+------------------+----------------+
| Field    | Type                 | Null | Key | Default          | Extra          |
+----------+----------------------+------+-----+------------------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL             |                |
| password | char(32)             | NO   |     | NULL             |                |
| email    | varchar(50)          | NO   |     | 382771946@qq.com |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum('               | YES  |     |                  |                |
| addre    | varchar(200)         | YES  |     | NULL             |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)            | NO   |     | default.jpg      |                |
+----------+----------------------+------+-----+------------------+----------------+
10 rows in set, 3 warnings (0.01 sec)

表名

修改表名有以下两种方法：
1、ALTER TABLE tbl_name RENAME [TO I AS] new_name
2、RENAME TABLE tbl_name TO new_name

mysql> ALTER TABLE user6 RENAME TO user7;
Query OK, 0 rows affected (0.01 sec)

mysql> RENAME TABLE user7 TO user6;
Query OK, 0 rows affected (0.06 sec)

字段

1、添加字段
ALTER TABLE tbl_name ADD 字段名称字段类型 [完整性约束条件] [FIRST I AFTER 字段名称]

#当前表结构
mysql> DESC user6;
+----------+----------------------+------+-----+------------------+----------------+
| Field    | Type                 | Null | Key | Default          | Extra          |
+----------+----------------------+------+-----+------------------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL             |                |
| password | char(32)             | NO   |     | NULL             |                |
| email    | varchar(50)          | NO   |     | 382771946@qq.com |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum(                | YES  |     |                  |                |
| addre    | varchar(200)         | YES  |     | NULL             |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)            | NO   |     | default.jpg      |                |
+----------+----------------------+------+-----+------------------+----------------+
11 rows in set, 3 warnings (0.01 sec)

#默认添加字段在最后
mysql> ALTER TABLE user6 ADD card CHAR(18);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#可添加到第一条
mysql> ALTER TABLE user6 ADD test1 VARCHAR(20) NOT NULL FIRST;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

#也可添加到指定位置
mysql> ALTER TABLE user6 ADD test2 INT NOT NULL DEFAULT 100 AFTER username;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#当前表结构
mysql> DESC user6;
+----------+----------------------+------+-----+------------------+----------------+
| Field    | Type                 | Null | Key | Default          | Extra          |
+----------+----------------------+------+-----+------------------+----------------+
| test1    | varchar(20)          | NO   |     | NULL             |                |
| id       | smallint(5) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL             |                |
| test2    | int(11)              | NO   |     | 100              |                |
| password | char(32)             | NO   |     | NULL             |                |
| email    | varchar(50)          | NO   |     | 382771946@qq.com |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum(                | YES  |     |                  |                |
| addre    | varchar(200)         | YES  |     | NULL             |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)            | NO   |     | default.jpg      |                |
| card     | char(18)             | YES  |     | NULL             |                |
+----------+----------------------+------+-----+------------------+----------------+
13 rows in set, 3 warnings (0.01 sec)

#批量添加
mysql> ALTER TABLE user6
    ->
    -> ADD test3 INT NOT NULL DEFAULT 123 AFTER password,
    ->
    -> ADD test4 FLOAT(6,2) FIRST,
    ->
    -> ADD test5 SET('A','B','C');
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#当前表结构
mysql> DESC user6;
+----------+----------------------+------+-----+------------------+----------------+
| Field    | Type                 | Null | Key | Default          | Extra          |
+----------+----------------------+------+-----+------------------+----------------+
| test4    | float(6,2)           | YES  |     | NULL             |                |
| test1    | varchar(20)          | NO   |     | NULL             |                |
| id       | smallint(5) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL             |                |
| test2    | int(11)              | NO   |     | 100              |                |
| password | char(32)             | NO   |     | NULL             |                |
| test3    | int(11)              | NO   |     | 123              |                |
| email    | varchar(50)          | NO   |     | 382771946@qq.com |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum(                | YES  |     |                  |                |
| addre    | varchar(200)         | YES  |     | NULL             |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)            | NO   |     | default.jpg      |                |
| card     | char(18)             | YES  |     | NULL             |                |
| test5    | set('A','B','C')     | YES  |     | NULL             |                |
+----------+----------------------+------+-----+------------------+----------------+
16 rows in set, 3 warnings (0.00 sec)

2、删除字段
ALTER TABLE tbl_name DROP 字段名称

mysql> ALTER TABLE user6
    ->
    -> DROP test1,
    ->
    -> DROP test2,
    ->
    -> DROP test3,
    ->
    -> DROP test4;
    ->
    -> DROP test5;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user6;
+----------+----------------------+------+-----+------------------+----------------+
| Field    | Type                 | Null | Key | Default          | Extra          |
+----------+----------------------+------+-----+------------------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL             |                |
| password | char(32)             | NO   |     | NULL             |                |
| email    | varchar(50)          | NO   |     | 382771946@qq.com |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum('               | YES  |     |                  |                |
| addre    | varchar(200)         | YES  |     | NULL             |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)            | NO   |     | default.jpg      |                |
| card     | char(18)             | YES  |     | NULL             |                |
+----------+----------------------+------+-----+------------------+----------------+
11 rows in set, 3 warnings (0.01 sec)

3、修改字段
ALTER TABLE tbl_name MODIFY 字段名称字段类型 [完整性约束条件][FIRST I AFTER]

-- 将email 修改为 VARCHAR(200)
mysql> ALTER TABLE user6 MODIFY email VARCHAR(200);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 将card字段移动到sex字段之后
mysql> ALTER TABLE user6 MODIFY card CHAR(18) AFTER sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user6;
+----------+----------------------+------+-----+-------------+----------------+
| Field    | Type                 | Null | Key | Default     | Extra          |
+----------+----------------------+------+-----+-------------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL        |                |
| password | char(32)             | NO   |     | NULL        |                |
| email    | varchar(200)         | YES  |     | NULL        |                |
| age      | tinyint(3) unsigned  | YES  |     | 18          |                |
| sex      | enum('               | YES  |     |             |                |
| card     | char(18)             | YES  |     | NULL        |                |
| addre    | varchar(200)         | YES  |     | NULL        |                |
| salary   | float(6,2)           | YES  |     | NULL        |                |
| regTime  | int(10) unsigned     | YES  |     | NULL        |                |
| face     | char(100)            | NO   |     | default.jpg |                |
+----------+----------------------+------+-----+-------------+----------------+
11 rows in set, 3 warnings (0.01 sec)

4、修改字段名称
ALTER TABLE tbl_name CHANGE 旧字段名称新字段名称字段类型 [完整性约束条件] [FIRST I AFTER 字段名称]

-- 将addre更改为addr
mysql> ALTER TABLE user6 CHANGE addre addr varchar(200);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user6;
+----------+----------------------+------+-----+------------------+----------------+
| Field    | Type                 | Null | Key | Default          | Extra          |
+----------+----------------------+------+-----+------------------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL             |                |
| password | char(32)             | NO   |     | NULL             |                |
| email    | varchar(50)          | NO   |     | 382771946@qq.com |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum('               | YES  |     |                  |                |
| card     | char(18)             | YES  |     | NULL             |                |
| addr     | varchar(200)         | YES  |     | NULL             |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)            | NO   |     | default.jpg      |                |
+----------+----------------------+------+-----+------------------+----------------+
11 rows in set, 3 warnings (0.01 sec)

约束

1、默认值
添加默认值：ALTER TABLE tbl_name ALTER 名称 SET DEFAULT 默认值

mysql> ALTER TABLE user6 ALTER addr SET DEFAULT 'Beijing';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user6;
+----------+----------------------+------+-----+------------------+----------------+
| Field    | Type                 | Null | Key | Default          | Extra          |
+----------+----------------------+------+-----+------------------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL             |                |
| password | char(32)             | NO   |     | NULL             |                |
| email    | varchar(50)          | NO   |     | 382771946@qq.com |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum('               | YES  |     |                  |                |
| card     | char(18)             | YES  |     | NULL             |                |
| addr     | varchar(200)         | YES  |     | Beijing          |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)            | NO   |     | default.jpg      |                |
+----------+----------------------+------+-----+------------------+----------------+
11 rows in set, 3 warnings (0.01 sec)

删除默认值：ALTER TABLE tbl_name ALTER 名称 DROP DEFAULT

mysql> ALTER TABLE user6 ALTER addr DROP DEFAULT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user6;
+----------+----------------------+------+-----+------------------+----------------+
| Field    | Type                 | Null | Key | Default          | Extra          |
+----------+----------------------+------+-----+------------------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL             |                |
| password | char(32)             | NO   |     | NULL             |                |
| email    | varchar(50)          | NO   |     | 382771946@qq.com |                |
| age      | tinyint(3) unsigned  | YES  |     | 18               |                |
| sex      | enum('               | YES  |     |                  |                |
| card     | char(18)             | YES  |     | NULL             |                |
| addr     | varchar(200)         | YES  |     | NULL             |                |
| salary   | float(6,2)           | YES  |     | NULL             |                |
| regTime  | int(10) unsigned     | YES  |     | NULL             |                |
| face     | char(100)            | NO   |     | default.jpg      |                |
+----------+----------------------+------+-----+------------------+----------------+
11 rows in set, 3 warnings (0.01 sec)

2、主键
添加主键：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol] PRIMARY KEY [index_type] (字段名称 )

mysql> CREATE TABLE IF NOT EXISTS test7(
    -> id INT
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC test12;
ERROR 1146 (42S02): Table 'test1.test12' doesn't exist
mysql> DESC test7;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> ALTER TABLE test7 ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test7;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | 0       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

删除主键：ALTER TABLE tbl_name DROP PRIMARY KEY

mysql> ALTER TABLE test7 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test7;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | 0       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

3、唯一
添加唯一：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEXIKEY] [索引名称] (字段名称,···)

mysql> CREATE TABLE IF NOT EXISTS user8(
    -> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> card CHAR(18) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC user8;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| card     | char(18)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> ALTER TABLE user8 ADD UNIQUE(username);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE user8 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user8;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| card     | char(18)            | NO   | UNI | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

删除唯一：ALTER TABLE tbl_name DROP {INDEX | KEY} index_name

mysql> ALTER TABLE user8 DROP INDEX username;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE user8 DROP KEY uni_card;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC user8;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   |     | NULL    |                |
| card     | char(18)            | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

4、自增长值
ALTER TABLE tbl_name AUTO INCREMNET=值

mysql> ALTER TABLE user8 AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE user8;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user8 | CREATE TABLE `user8` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

存储引擎

ALTER TABLE tbl_name ENGINE=存储引擎名称

-- 修改表的存储引擎为MyISAM
mysql> SHOW CREATE TABLE user8;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user8 | CREATE TABLE `user8` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE user8 ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE user8;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user8 | CREATE TABLE `user8` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据表

DROP TABLE [IF EXISTS] tbl_name [,tbl_name…]

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test7           |
| user            |
| user1           |
| user2           |
| user3           |
| user4           |
| user5           |
| user6           |
| user8           |
+-----------------+
9 rows in set (0.00 sec)

#删除一张表
mysql> DROP TABLE user8;
Query OK, 0 rows affected (0.00 sec)

#删除多张表
mysql> DROP TABLE IF EXISTS user1,user2,user3,user4,user5,user6,user7;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test7           |
| user            |
+-----------------+
2 rows in set (0.00 sec)