SQL --数据表操作

目录

1， 查询数据库表

2， 查询数据表结构(desc)

3， 查询表的创建语句(show create table)

4， 创建表

5，制定约束

5-1， 非空约束(not null)

5-1-1， 创建表时制定约束

5-1-2， 修改非空约束

5-2， 唯一约束(unique)

5-2-1， 创建表时制定约束

5-2-2， 修改唯一约束

5-2-3， 删除唯一约束

5-3， 主键约束(primary key)

5-3-1， 创建表时制定约束

5-3-2， 修改主键约束

5-3-3， 删除主键约束

5-3-4， 设置自增字段(auto_increment)

5-4， 外键约束(foreign key)

5-4-1， 创建表时制定约束

5-4-2， 修改外键约束

5-4-3， 删除外键约束

5-5， 检查约束(check)

5-5-1， 创建表时制定约束

5-5-2， 修改check约束

5-5-2， 删除check约束

5-6， 默认约束(default)

5-6-1， 创建表示制定约束

5-6-2， 修改默认约束

5-6-3， 删除默认约束

6， 修改数据表(alter)

6-1， 数据表列操作

6-1-1， 增加列(add column)

7-1-2， 修改列(change column)

7-1-3， 删除列(drop column)

7-2，数据表行操作

7-2-1， 删除行数据

7-2-2， 删除整表数据

8， 删除数据表

9， 快照

10， 查询结果集存放到表中

---

1， 查询数据库表

语法：show tables;

# 查询当前数据库的表
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
| snapshot_table  |
+-----------------+
4 rows in set (0.00 sec)

2， 查询数据表结构(desc)

语法：desc 表名;

# 查询dept表的结构
mysql> desc dept;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| dept_name    | varchar(255) | YES  |     | NULL    |       |
| dept_number  | int(11)      | YES  |     | NULL    |       |
| dept_address | varchar(255) | YES  |     | NULL    |       |
| dept_id      | int(11)      | NO   | PRI | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3， 查询表的创建语句(show create table)

语法： show create table 表名;

mysql> show create table dept;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dept  | CREATE TABLE `dept` (
  `dept_name` varchar(255) DEFAULT NULL,
  `dept_number` int(11) DEFAULT NULL,
  `dept_address` varchar(255) DEFAULT NULL,
  `dept_id` int(11) NOT NULL,
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4， 创建表

语法：

create table 表名(

字段1 字段1数据类型，

...

字段n 字段n数据类型);

mysql> create table result_table(
    -> id bigint,
    -> name varchar(30),
    -> job varchar(30));
Query OK, 0 rows affected (0.28 sec)

mysql> desc result_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| job   | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5，制定约束

5-1， 非空约束(not null)

若字段为not null，则在插入数据时必须有数据，否则会报错

5-1-1， 创建表时制定约束

语法：

create table 表名(

字段1 数据类型 not null

...

字段1 数据类型 not null));

# 创建表时指定字段id，name, job不能为空
mysql> create table result_table_1(
    -> id bigint not null,
    -> name varchar(20) not null,
    -> job varchar(20) not null);
Query OK, 0 rows affected (0.36 sec)

mysql> desc result_table_1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| job   | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5-1-2， 修改非空约束

语法：alter table 表名 modify 字段名 字段数据类型 not null

# 单独修改not null约束， 注意，这里需要写上：字段名，字段数据类型，约束条件
# 删除result_table_1 表中name_1字段的非空约束
mysql> alter table result_table_1 modify name_1 varchar(30) null;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc result_table_1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | bigint(20)  | NO   |     | NULL    |       |
| name_1 | varchar(30) | YES  |     | NULL    |       |
| job    | varchar(20) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 添加result_table_1表name_1字段非空约束
mysql> alter table result_table_1 modify name_1 varchar(30) not null;
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc result_table_1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | bigint(20)  | NO   |     | NULL    |       |
| name_1 | varchar(30) | NO   |     | NULL    |       |
| job    | varchar(20) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5-2， 唯一约束(unique)

指定了unique约束的列，不允许有重复的数据出现

5-2-1， 创建表时制定约束

语法：

create table 表名(

字段1 字段1数据类型 not null,

...

字段n 字段n数据类型 not null,

constraint 约束名称 unique (字段x,  字段y, ...));

# 创建表时， 给字段name制定unique约束，其中"aaa"为约束名称
mysql> create table r_table(
    -> id bigint not null,
    -> name varchar(10) not null,
    -> job varchar(20) not null,
    -> constraint aaa unique (name));
Query OK, 0 rows affected (0.55 sec)

mysql> desc r_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   |     | NULL    |       |
| name  | varchar(10) | NO   | PRI | NULL    |       |
| job   | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5-2-2， 修改唯一约束

alter table 表名 add constraint 约束名称 unique (字段x,  字段y, ...);

# 给字段的字段id, name添加unique约束， 约束名称为aa1 
mysql> alter table r_table add constraint aa1 unique (id, name);
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc r_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   | PRI | NULL    |       |
| job   | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5-2-3， 删除唯一约束

语法：alter table 表名 drop index 约束名称;

# 删除unique约束
mysql> alter table r_table drop index aaa;
Query OK, 0 rows affected (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc r_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   |     | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| job   | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5-3， 主键约束(primary key)

主键是记录的唯一标识，一张表中有且只能有一个主键，且不能为null

5-3-1， 创建表时制定约束

语法：

create table 表名(

字段1 字段1数据类型 not null，

...

字段n 字段n数据类型 not null，

constraint 约束名称 primary key (字段x, ...));

# 创建表，并指定id列为主键，约束名称为aaa_primary_key 
mysql> create table aaa(
    -> id bigint not null,
    -> name varchar(20) not null,
    -> job varchar(20) not null,
    -> constraint aaa_primary_key primary key (id));
Query OK, 0 rows affected (0.34 sec)

mysql> desc aaa;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| job   | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5-3-2， 修改主键约束

语法：alter table 表名 add constraint 约束名称 primary key (字段x, ...);

# 为表aaa的ID列条件主键约束，约束名称为aaa_pri 
mysql> alter table aaa add constraint aaa_pri primary key (id);
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc aaa;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| job   | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5-3-3， 删除主键约束

语法：alter table 表名 drop primary key

# 删除表的主键约束
mysql> alter table aaa drop primary key;
Query OK, 0 rows affected (0.85 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc aaa;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| job   | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5-3-4， 设置自增字段(auto_increment)

通过设置自增字段，在每次插入数据后，该字段自动加1;

默认从1开始自增

语法：

create table 表名(

字段1 字段1数据类型 not null auto_increment,

...

字段n 字段n数据类型 not null);

# 创建表bbb，并给主键id列设置自增
mysql> create table bbb(
    -> id bigint not null auto_increment,
    -> name varchar(50) not null,
    -> job varchar(50) not null,
    -> constraint bbb_pk primary key (id));
Query OK, 0 rows affected (0.34 sec)

mysql> insert into bbb (name, job) values ('zs', 'fsdafa');
Query OK, 1 row affected (0.15 sec)

mysql> select * from bbb;
+----+------+--------+
| id | name | job    |
+----+------+--------+
|  1 | zs   | fsdafa |
+----+------+--------+
1 row in set (0.00 sec)

修改自增字段的起始值

语法：alter table 表名 auto_increment = xxx

# 设置自增字段从100开始
mysql> alter table bbb auto_increment = 100;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into bbb (name, job) values ('zs', 'fsdafa');
Query OK, 1 row affected (0.05 sec)

mysql> select * from bbb;
+-----+------+--------+
| id  | name | job    |
+-----+------+--------+
|   1 | zs   | fsdafa |
| 100 | zs   | fsdafa |
+-----+------+--------+
2 rows in set (0.00 sec)

5-4， 外键约束(foreign key)

通过建立外键约束，可以建立表与表的联系，放置无效数据插入

一个表的外键通常来自另外一个表的主键

5-4-1， 创建表时制定约束

语法：

create table 表名(

字段1 字段1数据类型 not null,

...

字段n 字段n数据类型 not null,

constraint 约束名称 foreign key (字段x, ...) references 其他表(字段x, ...));

# 创建表bbb，并指定id列为外键，指向aaa表的id列
mysql> create table bbb(
    -> id bigint not null,
    -> gender varchar(20) not null,
    -> age int not null,
    -> constraint bbb_fk foreign key (id) references aaa(id));
Query OK, 0 rows affected (0.48 sec)

5-4-2， 修改外键约束

语法：

alter table 表名 add constraint 约束名称 foreign key (字段x, ...) references 其他表(字段x, ...)

mysql> alter table bbb add constraint bbb_fk foreign key (id) references aaa(id);
Query OK, 0 rows affected (0.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

5-4-3， 删除外键约束

语法：alter table 表名 drop foreign key 约束名称

# 删除表bbb的外键约束
mysql> alter table bbb drop foreign key bbb_fk;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

5-5， 检查约束(check)

check约束有用限制列的取值, 注意是否支持check约束需要看数据库的版本

5-5-1， 创建表时制定约束

语法：

create table 表名(

字段1 字段1数据类型 not null,

...

字段n 字段n数据类型 not null,

constraint 约束名称 check (条件表达式))；

mysql> create table bbb(
    -> id bigint not null,
    -> name varchar(20) not null,
    -> job varchar(50) not null,
    -> address varchar(50) not null,
    -> constraint bbb_ck check (id > 1 and address = 'zh'));
Query OK, 0 rows affected (0.41 sec)

5-5-2， 修改check约束

语法：alter table 表名 add constraint 约束名称 check (条件表达式);

5-5-2， 删除check约束

语法：alter table 表名 drop check 约束名称 

5-6， 默认约束(default)

用于向指定列插入默认值

5-6-1， 创建表示制定约束

语法：

create table 表名(

字段1 字段1数据类型 not null,

...

字段x 字段x数据类型 default 默认值);

# 创建表bbb，并指定address列的默认值为zh
mysql> create table bbb(
    -> id bigint not null,
    -> name varchar(50) not null,
    -> job varchar(50) not null,
    -> address varchar(10) default 'zh');
Query OK, 0 rows affected (0.26 sec)

mysql> desc bbb;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | bigint(20)  | NO   |     | NULL    |       |
| name    | varchar(50) | NO   |     | NULL    |       |
| job     | varchar(50) | NO   |     | NULL    |       |
| address | varchar(10) | YES  |     | zh      |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5-6-2， 修改默认约束

语法：alter table 表名 alter 字段x set default 默认值;

mysql> alter table bbb alter name set default '张三';
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc bbb;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | bigint(20)  | NO   |     | NULL    |       |
| name    | varchar(50) | NO   |     | 张三    |       |
| job     | varchar(50) | NO   |     | NULL    |       |
| address | varchar(10) | YES  |     | zh      |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5-6-3， 删除默认约束

语法：alter table 表名 alter 字段x drop default;

# 删除name列的默认值
mysql> alter table bbb alter name drop default;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc bbb;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | bigint(20)  | NO   |     | NULL    |       |
| name    | varchar(50) | NO   |     | NULL    |       |
| job     | varchar(50) | NO   |     | NULL    |       |
| address | varchar(10) | YES  |     | zh      |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

6， 修改数据表(alter)

6-1， 数据表列操作

6-1-1， 增加列(add column)

语法：alter table 表名 add column 列名 列名数据类型 not null；

# 给数据表dept添加birth列
mysql> alter table dept add column birth varchar(10) not null;
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from dept;
+--------+------------+----------+-------+
| deptno | dname      | loc      | birth |
+--------+------------+----------+-------+
|     10 | ACCOUNTING | NEW YORK |       |
|     20 | RESEARCH   | DALLAS   |       |
|     30 | SALES      | CHICAGO  |       |
|     40 | OPERATIONS | BOSTON   |       |
|     50 | fdsf       | fdsaf    |       |
|     60 | fdsf       | fdsaf    |       |
|     70 | fdsf       | fdsaf    |       |
+--------+------------+----------+-------+
7 rows in set (0.00 sec)

7-1-2， 修改列(change column)

语法：alter table 表名 change column 字段x 字段x别名 修改列数据类型 not null;

# 修改birth列，并将数据类型修改为varchar(20)
mysql> alter table dept change column birth birthday varchar(20) not null;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from dept;
+--------+------------+----------+----------+
| deptno | dname      | loc      | birthday |
+--------+------------+----------+----------+
|     10 | ACCOUNTING | NEW YORK |          |
|     20 | RESEARCH   | DALLAS   |          |
|     30 | SALES      | CHICAGO  |          |
|     40 | OPERATIONS | BOSTON   |          |
|     50 | fdsf       | fdsaf    |          |
|     60 | fdsf       | fdsaf    |          |
|     70 | fdsf       | fdsaf    |          |
+--------+------------+----------+----------+
7 rows in set (0.00 sec)

mysql> desc dept;
+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| deptno   | int(2) unsigned zerofill | NO   | PRI | NULL    |       |
| dname    | varchar(14)              | YES  |     | NULL    |       |
| loc      | varchar(13)              | YES  |     | NULL    |       |
| birthday | varchar(20)              | NO   |     | NULL    |       |
+----------+--------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

7-1-3， 删除列(drop column)

语法：alter table 表名 drop column 列名;

mysql> alter table dept drop column birthday;
Query OK, 0 rows affected (0.79 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | fdsf       | fdsaf    |
|     60 | fdsf       | fdsaf    |
|     70 | fdsf       | fdsaf    |
+--------+------------+----------+
7 rows in set (0.00 sec)

7-2，数据表行操作

7-2-1， 删除行数据

语法： delete from 表名 where 条件表达式

mysql> select * from dept;
+--------+------------+----------+------+
| deptno | dname      | loc      | sal  |
+--------+------------+----------+------+
|     10 | ACCOUNTING | NEW YORK |  900 |
|     20 | RESEARCH   | DALLAS   | 1100 |
|     30 | SALES      | CHICAGO  | 1000 |
|     40 | OPERATIONS | BOSTON   | 1000 |
|     50 | fdsf       | fdsaf    | 1000 |
|     60 | fdsf       | fdsaf    | 1000 |
|     70 | aaa        | bbb      | 1000 |
|     80 | fdabb      | bbb      | 1000 |
+--------+------------+----------+------+
8 rows in set (0.00 sec)

# 删除deptno >= 50 and deptno <= 80条件下的记录
mysql> delete from dept where deptno >= 50 and deptno <= 80;
Query OK, 4 rows affected (0.07 sec)

mysql> select * from dept;
+--------+------------+----------+------+
| deptno | dname      | loc      | sal  |
+--------+------------+----------+------+
|     10 | ACCOUNTING | NEW YORK |  900 |
|     20 | RESEARCH   | DALLAS   | 1100 |
|     30 | SALES      | CHICAGO  | 1000 |
|     40 | OPERATIONS | BOSTON   | 1000 |
+--------+------------+----------+------+
4 rows in set (0.00 sec)

7-2-2， 删除整表数据

语法：delete from 表名；

# 删除dept_copy表的所有数据
mysql> select * from dept_copy;
+--------+------------+----------+------+
| deptno | dname      | loc      | sal  |
+--------+------------+----------+------+
|     10 | ACCOUNTING | NEW YORK |  900 |
|     20 | RESEARCH   | DALLAS   | 1100 |
|     30 | SALES      | CHICAGO  | 1000 |
|     40 | OPERATIONS | BOSTON   | 1000 |
+--------+------------+----------+------+
4 rows in set (0.00 sec)

mysql> delete from dept_copy;
Query OK, 4 rows affected (0.04 sec)

mysql> select * from dept_copy;
Empty set (0.00 sec)

8， 删除数据表

语法：drop table 表名;

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| aaa             |
| dept            |
| emp             |
| result_table    |
| salgrade        |
+-----------------+
5 rows in set (0.00 sec)

mysql> drop table result_table;
Query OK, 0 rows affected (0.28 sec)

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| aaa             |
| dept            |
| emp             |
| salgrade        |
+-----------------+
4 rows in set (0.00 sec)

9， 快照

即复制一个查询结果到一个新表中

语法：create table 新表名 select * from 表名;

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| aaa             |
| dept            |
| emp             |
| salgrade        |
+-----------------+
4 rows in set (0.00 sec)

# 复制select * from dept查询结果到新表snapshot_table中
mysql> create table snapshot_table select * from dept;
Query OK, 8 rows affected (0.40 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| aaa             |
| dept            |
| emp             |
| salgrade        |
| snapshot_table  |
+-----------------+
5 rows in set (0.00 sec)

mysql> select * from snapshot_table;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | fdsf       | fdsaf    |
|     60 | fdsf       | fdsaf    |
|     70 | aaa        | bbb      |
|     80 | fdabb      | bbb      |
+--------+------------+----------+
8 rows in set (0.00 sec)

10， 查询结果集存放到表中

语法： insert into 表名 select * from 其他表;

mysql> select * from bbb;
+----+------+
| id | job  |
+----+------+
|  4 | fds1 |
|  5 | fds2 |
|  6 | fds3 |
+----+------+
3 rows in set (0.00 sec)

mysql> create table bbb_1(
    -> id bigint not null auto_increment,
    -> job varchar(50) not null,
    -> constraint bbb_1_pk primary key (id))
    -> ;
Query OK, 0 rows affected (0.38 sec)

# 将查询结果存放到表bbb_1
mysql> insert into bbb_1 select * from bbb;
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from bbb_1;
+----+------+
| id | job  |
+----+------+
|  4 | fds1 |
|  5 | fds2 |
|  6 | fds3 |
+----+------+
3 rows in set (0.00 sec)