MySQL之创建表
MySQL 刘宇帅 5年前 阅读量: 1498
创建表
创建学生表 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