数据库—10(表数据操作的SQL语句，高级使用)

一、将查询结果插入其他表中

若将personinfo 表中 人员最基本信息(名字、年龄、性别，身份证号)  统计结果  插入进 一个表中
语法格式:

insert into 表1(字段名) select SQL语句

即将select SQL 语句执行的结果插入 表1.字段名 下

创建基本信息表basicPersonInfo

create table basicPersonInfo(id int unsigned primary key auto_increment not null,name varchar(20),age tinyint unsigned default 0,gender enum('男','女'), personId varchar(18),proabb varchar(20));

mysql> desc basicPersonInfo;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)         | YES  |     | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | 0       |                |
| gender   | enum('男','女')     | YES  |     | NULL    |                |
| personId | varchar(18)         | YES  |     | NULL    |                |
| proabb   | varchar(20)         | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

将personinfo 表中所有 省份简写为 '川'的 人员基本信息插入至表basicPersonInfo中

mysql> insert into basicPersonInfo(name,age,gender,personId,proabb) select name,age,gender,personId,proabb from personinfo where proabb = '川';

mysql> select * from basicPersonInfo;
+----+-----------+------+--------+--------------------+--------+
| id | name      | age  | gender | personId           | proabb |
+----+-----------+------+--------+--------------------+--------+
|  1 | xiaozhang |   20 | 男     | 510131198605136412 | 川     |
|  2 | xiaozhang |   20 | 男     | 510131198605136413 | 川     |
|  3 | xiaowang  |   22 | 男     | 510131198605136414 | 川     |
|  4 | xiaowang  |   30 | 男     | 510131198605136415 | 川     |
+----+-----------+------+--------+--------------------+--------+
4 rows in set (0.00 sec)

二、使用连接更新表中某个字段数据

现在将personinfo 表中 省简称字段‘proabb’ 换成 province 表中 proabb 字段相对应的ID。

首先通过连表查询-内查询  去查看 personinfo 表中 proabb 等于 province 表中 proabb 字段 

mysql> select * from personinfo as per inner join province as pro  on  per.proabb = pro.proabb;
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+----+--------+--------+
| id | name      | age  | gender | height | city   | addr         | personId           | proabb | id | name   | proabb |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+----+--------+--------+
|  1 | xiaozhang |   20 | 男     | 172.00 | 成都   | 成都高新     | 510131198605136412 | 川     |  3 | 四川   | 川     |
|  2 | xiaozhang |   20 | 男     | 172.00 | 成都   | 成都高新     | 510131198605136413 | 川     |  3 | 四川   | 川     |
|  3 | xiaowang  |   22 | 男     | 172.00 | 成都   | 成都高新     | 510131198605136414 | 川     |  3 | 四川   | 川     |
|  4 | xiaowang  |   30 | 男     | 170.00 | 成都   | 成都高新     | 510131198605136415 | 川     |  3 | 四川   | 川     |
|  5 | xiaozhao  |   25 | 女     | 165.00 | 重庆   | 重庆高新     | 510131198605136416 | 渝     |  4 | 重庆   | 渝     |
|  6 | xiaozhao  |   26 | 女     | 165.00 | 重庆   | 重庆高新     | 510131198605136417 | 渝     |  4 | 重庆   | 渝     |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+----+--------+--------+
6 rows in set (0.00 sec)

将 上面语句 from 后面的语句看成一张 虚表.

#看成虚表
personinfo as per inner join province as pro  on  per.proabb = pro.proabb

然后将此虚表 per.proabb 字段 替换成 pro.id

mysql>insert into province(name,proabb) values('贵州','贵'),('云南','云');

mysql>update personinfo as per inner join province as pro  on  per.proabb = pro.proabb set per.proabb = pro.id;


mysql> select * from personinfo;
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
| id | name      | age  | gender | height | city   | addr         | personId           | proabb |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
|  1 | xiaozhang |   20 | 男     | 172.00 | 成都   | 成都高新     | 510131198605136412 | 3      |
|  2 | xiaozhang |   20 | 男     | 172.00 | 成都   | 成都高新     | 510131198605136413 | 3      |
|  3 | xiaowang  |   22 | 男     | 172.00 | 成都   | 成都高新     | 510131198605136414 | 3      |
|  4 | xiaowang  |   30 | 男     | 170.00 | 成都   | 成都高新     | 510131198605136415 | 3      |
|  5 | xiaozhao  |   25 | 女     | 165.00 | 重庆   | 重庆高新     | 510131198605136416 | 4      |
|  6 | xiaozhao  |   26 | 女     | 165.00 | 重庆   | 重庆高新     | 510131198605136417 | 4      |
|  7 | xiaoxu    |   28 | 男     | 155.00 | 贵阳   | 贵阳南城     | 510131199204105870 | 5      |
|  9 | xiaozhou  |   29 | 女     | 160.00 | 桂林   | 桂林北城     | 510131199204105815 | 6      |
+----+-----------+------+--------+--------+--------+--------------+--------------------+--------+
8 rows in set (0.00 sec)

 

三、创建表并给某个字段添加数据

语法格式:
           create table ..  select 列名 .. 表示创建表并插入数据

比如建立一个表,表里面只有 省 简写

​​​​​​​create table proabbPrefix(id int unsigned primary key auto_increment not null,proabb varchar(20) ) select proabb  from personinfo group by proabb;

mysql> select * from proabbPrefix;
+----+--------+
| id | proabb |
+----+--------+
|  1 | 云     |
|  2 | 川     |
|  3 | 渝     |
|  4 | 贵     |
+----+--------+
4 rows in set (0.00 sec)

注意 如果 proabbPrefix proabb字段名 和personinfo  proabb 字段名不一样的话，必须将 select proabb as  proabbPrefix的字段名
​​​​​​​