MySQL之创建表

创建表

创建学生表 SQL

create table if not exists`students`  (
    `id` int not null auto_increment comment '学生id',
    `no` char(5) not null comment '学生学号',
    `name` varchar(128) not null default '' comment '学生姓名',
    `sex` tinyint not null default 0 comment '0 无 1 女 2 男',
    `age` tinyint null default null comment '年龄',
    `created_at` datetime not null default current_timestamp comment '创建时间',
    `updated_at` datetime default null on update current_timestamp comment '更新时间',
    primary key(`id`),
    key `created_at`(`created_at`),
    unique key `student_no`(`no`)
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_unicode_ci comment "学生信息表";

执行创建语句

mysql> use school;
Database changed
mysql> create table if not exists`students`  (
    -> `id` int not null auto_increment comment '学生id',
    -> `no` char(5) not null comment '学生学号',
    -> `name` varchar(128) not null default '' comment '学生姓名',
    -> `sex` tinyint not null default 0 comment '0 无 1 女 2 男',
    -> `age` tinyint null default null comment '年龄',
    -> `created_at` datetime not null default current_timestamp comment '创建时间',
    -> `updated_at` datetime default null on update current_timestamp comment '更新时间',
    -> primary key(`id`),
    -> key `created_at`(`created_at`),
    -> unique key `student_no`(`no`)
    -> ) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_unicode_ci comment "学生信息表";
Query OK, 0 rows affected (0.08 sec)

创建表中的字段 id created_at updated_at 和索引 primary key(id) 是创建任何表都必须的。表默认统一使用 InnoDB 引擎 和 utf8mb4 字符集、utf8mb4_unicode_ci 字符序。
查看所有表

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| students         |
+------------------+
1 row in set (0.00 sec)

查看表字段基本结构

mysql> desc students;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| no         | char(5)      | NO   | UNI | NULL              |                             |
| name       | varchar(128) | NO   |     |                   |                             |
| sex        | tinyint(4)   | NO   |     | 0                 |                             |
| age        | tinyint(4)   | YES  |     | NULL              |                             |
| created_at | datetime     | NO   | MUL | CURRENT_TIMESTAMP |                             |
| updated_at | datetime     | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.01 sec)

查看创建表语句

mysql> show create table students\G;
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `no` char(5) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学生学号',
  `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '学生姓名',
  `sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 无 1 女 2 男',
  `age` tinyint(4) DEFAULT NULL COMMENT '年龄',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `student_no` (`no`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生信息表'
1 row in set (0.00 sec)

ERROR:
No query specified

\G 用来格式化输出结果，更好看一点。

临时表

临时表只在当前会话中生效，退出重连就会消失，我们有时候需要一个临时表存放一些数据。比如保存下 id 小于 100 的学生到一个临时表

mysql> create temporary table students_temporary select * from students where id<100;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from students_temporary limit 1\G;
*************************** 1. row ***************************
        id: 1
        no: 00001
      name: 阿宝
       sex: 0
       age: NULL
created_at: 2019-11-20 01:34:04
updated_at: NULL
1 row in set (0.00 sec)

ERROR:
No query specified

查看库所有所有表时不展示临时表

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| students         |
+------------------+
1 row in set (0.00 sec)

创建一个相同的表

只创建表不要数据

mysql> create table students_new like students;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| students         |
| students_new     |
+------------------+
2 rows in set (0.00 sec)

mysql> desc students_new;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| no         | char(5)      | NO   | UNI | NULL              |                             |
| name       | varchar(128) | NO   |     |                   |                             |
| sex        | tinyint(4)   | NO   |     | 0                 |                             |
| age        | tinyint(4)   | YES  |     | NULL              |                             |
| created_at | datetime     | NO   | MUL | CURRENT_TIMESTAMP |                             |
| updated_at | datetime     | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.01 sec)

创建表并复制全部数据

mysql> create table students_new2 select * from students;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from students_new2 limit 1\G;
*************************** 1. row ***************************
        id: 1
        no: 00001
      name: 阿宝
       sex: 0
       age: NULL
created_at: 2019-11-20 01:34:04
updated_at: NULL
1 row in set (0.00 sec)

ERROR:
No query specified

使用 create talbe ... select 不会创建任何索引

mysql> show create table students_new2\G;
*************************** 1. row ***************************
       Table: students_new2
Create Table: CREATE TABLE `students_new2` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT '学生id',
  `no` char(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学生学号',
  `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '学生姓名',
  `sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 无 1 女 2 男',
  `age` tinyint(4) DEFAULT NULL COMMENT '年龄',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR:
No query specified