MySQL 创建数据表
用 CREATE TABLE 建表，定义列、类型、约束与引擎。
Key Points建表时定好主键、NOT NULL、默认值与引擎，省去日后 ALTER 的麻烦。
基本语法
SQL
USE shop;
CREATE TABLE users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(120) NOT NULL,
age TINYINT UNSIGNED DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Output
Query OK, 0 rows affected (0.04 sec)约束一览
| 约束 | 作用 |
|---|---|
| PRIMARY KEY | 唯一标识一行，非空且唯一 |
| NOT NULL | 该列必须有值 |
| UNIQUE | 值不重复（可多列组合） |
| DEFAULT | 未指定时填充默认值 |
| AUTO_INCREMENT | 自增，常用于主键 |
| FOREIGN KEY | 关联另一张表的主键 |
查看表结构
SQL
DESC users;
SHOW CREATE TABLE users;Output
+------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | | NULL | |
| email | varchar(120) | NO | UNI | NULL | |
| age | tinyint unsigned | YES | | 0 | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | |
+------------+------------------+------+-----+-------------------+----------------+Always set
ENGINE=InnoDB — it is the only engine with transactions and foreign keys. MySQL 8.0 defaults to it, but being explicit is clearer.基本语法
SQL
USE shop;
CREATE TABLE users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(120) NOT NULL,
age TINYINT UNSIGNED DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Output
Query OK, 0 rows affected (0.04 sec)约束一览
| 约束 | 作用 |
|---|---|
| PRIMARY KEY | 唯一标识一行，非空且唯一 |
| NOT NULL | 该列必须有值 |
| UNIQUE | 值不重复（可多列组合） |
| DEFAULT | 未指定时填充默认值 |
| AUTO_INCREMENT | 自增，常用于主键 |
| FOREIGN KEY | 关联另一张表的主键 |
查看表结构
SQL
DESC users;
SHOW CREATE TABLE users;Output
+------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | | NULL | |
| email | varchar(120) | NO | UNI | NULL | |
| age | tinyint unsigned | YES | | 0 | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | |
+------------+------------------+------+-----+-------------------+----------------+ENGINE=InnoDB 是必选项——只有它支持事务和外键。忘了写时 MySQL 8.0 默认就是 InnoDB，但显式写上更清晰。