一、将查询结果插入其他表中
若将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的字段名