目录
数据准备
一、单表写入，insert…select
案例：获取goods的分类，写入到新的数据表
二、多表更新、表关系join
案例一：我们使用内链接，链接goods数据表和goods_two数据表，然后修改goods_cate
三、案例：多表更新之一步到位（create …select）
数据准备
//创建一个新的数据库
mysql> create table if not exists goods_two(
-> cate_id smallint unsigned primary key auto_increment,
-> cate_name varchar(20) not null);
Query OK, 0 rows affected (0.35 sec)
//查看数据表结构：
mysql> desc goods_two;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| cate_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| cate_name | varchar(20) | NO | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)一、单表写入，insert…select
- 注意，前面博客我们也简单的介绍过insert…select
- 地址：
- 位置：文章最后一个案例
案例：获取goods的分类，写入到新的数据表
//第一步，查看我们goods数据发现我们数据表分为了7类。注意这里还是用到的上篇博客的goods数据表
mysql> select goods_cate from goods group by goods_cate;
+---------------+
| goods_cate |
+---------------+
| 台式机 |
| 平板电脑 |
| 服务器/工作站 |
| 游戏本 |
| 笔记本 |
| 笔记本配件 |
| 超级本 |
+---------------+
7 rows in set (0.00 sec)
//第二步，把我们这7类写入我们创建好的新数据表，这里用到的就是我们今天学的知识，insert...select
语法：insert [into] tbl_name[(col_name,...)] select...
//第三部：把goods数据库的所有分类，写入写的数据表中。
mysql> insert goods_two(cate_name) select goods_cate from goods group by goods_c
ate;
Query OK, 7 rows affected (0.06 sec)
Records: 7 Duplicates: 0 Warnings: 0
//查看插入到新数据表中的记录
mysql> select * from goods_two;
+---------+---------------+
| cate_id | cate_name |
+---------+---------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 服务器/工作站 |
| 4 | 游戏本 |
| 5 | 笔记本 |
| 6 | 笔记本配件 |
| 7 | 超级本 |
+---------+---------------+
7 rows in set (0.00 sec)二、多表更新、表关系join
注意：我们需要多表更新，就需要给这些表添加关系
- 语法结构
- 链接类型有三种 ：
案例一：我们使用内链接，链接goods数据表和goods_two数据表，然后修改goods_cate
//第一步，查看插入到新数据表中的记录，添加内连接到goods，用cate_id来代替总表中的goods_cate参数。
mysql> select * from goods_two;
+---------+---------------+
| cate_id | cate_name |
+---------+---------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 服务器/工作站 |
| 4 | 游戏本 |
| 5 | 笔记本 |
| 6 | 笔记本配件 |
| 7 | 超级本 |
+---------+---------------+
//第二步、我们给这两张数据表添加内连接，这样我们更新数据表goods_two中的数据，就会改变goods中的数据，这样我们就不用操作总表去改变数据，只需要通过子表来改变数据即可，方便我们操作。
mysql> update goods inner join goods_two on goods_cate=cate_name
-> set goods_cate = cate_id;
Query OK, 22 rows affected (0.07 sec)
Rows matched: 22 Changed: 22 Warnings: 0
//建立关系前.总表中的goods_cate，还是笔记本配件
*************************** 22. row ***************************
goods_id: 22
goods_name: 商务双肩背包
goods_cate: 笔记本配件
brand_name: 索尼
goods_price: 99.000
is_show: 1
is_saleoff: 0
22 rows in set (0.00 sec)
//建立关系后、后面的值，就会改变成goods_two中与之对应的cate_id值，而cate_id多代表的意思就是笔记本配件
*************************** 22. row ***************************
goods_id: 22
goods_name: 商务双肩背包
goods_cate: 6
brand_name: 索尼
goods_price: 99.000
is_show: 1
is_saleoff: 0
22 rows in set (0.00 sec)三、案例：多表更新之一步到位（create …select）
- 回忆上部分，单表更新的时候，我们一共用到了三步，
1、创建goods_two数据表。
2、把分类写入到新数据表中，
3、添加inner join关联数据表，然后修改数据表。
- 那能不能减少和优化这些操作呢，这里就要用到create …select
语法：create table [if not exists] tbl_name [(create_definition,…)] select_statement
//案例：这里我们操作我们的品牌 brand_name.
//查看我们数据表，会发现我们有9组不同类型的品牌；
mysql> select brand_name from goods group by brand_name;
+------------+
| brand_name |
+------------+
| IBM |
| 华硕 |
| 宏碁 |
| 惠普 |
| 戴尔 |
| 索尼 |
| 联想 |
| 苹果 |
| 雷神 |
+------------+
9 rows in set (0.00 sec)
//第二步：我们把品牌也写入到一张新的数据表，这里我们用到create...select，添加成后，系统提示我们有9条记录被写入
mysql> create table goods_brand(
-> brand_id smallint unsigned primary key auto_increment,
-> brand_name varchar(40) not null
-> )
-> select brand_name from goods group by brand_name;
Query OK, 9 rows affected (0.14 sec)
Records: 9 Duplicates: 0 Warnings: 0
//第三步，验证是否添加成功goods_brand数据表和goods_brand数据表是否存在刚才添加的数据。
//存在goods_brand数据表
mysql> show tables;
+--------------+
| Tables_in_t1 |
+--------------+
| city_1 |
| goods |
| goods_brand |
| goods_two |
| sheng |
| tdb_goods |
| two |
| users |
+--------------+
8 rows in set (0.00 sec)
//并且goods_brand数据表存在刚才我们的9条数据
mysql> select * from goods_brand;
+----------+------------+
| brand_id | brand_name |
+----------+------------+
| 1 | IBM |
| 2 | 华硕 |
| 3 | 宏碁 |
| 4 | 惠普 |
| 5 | 戴尔 |
| 6 | 索尼 |
| 7 | 联想 |
| 8 | 苹果 |
| 9 | 雷神 |
+----------+------------+
9 rows in set (0.00 sec)
//第四步，添加关系，根据品牌表goods_brand，来更新我们的goods总表，但是注意，我们两张数据表中的brand_name是一样的参数名称，如果还按照上面那种添加链接，会爆如下错误：
//因为，他不知道brand_name到底属于哪一张表
mysql> update goods inner join goods_brand on brand_name= brand_name
-> set brand_name=brand_id;
ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous
第五步，这里我们就要给数据表起别名或者在参数前面加上表名（goods as g ）。
mysql> update goods as g inner join goods_brand as b on g.brand_name=
-> b.brand_name
-> set g.brand_name = b.brand_id;
Query OK, 22 rows affected (0.06 sec)
Rows matched: 22 Changed: 22 Warnings: 0具体案例步骤
- 具体案例
//第一步创建数据表tdb_goods
CREATE TABLE IF NOT EXISTS tdb_goods(
goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(150) NOT NULL,
goods_cate VARCHAR(40) NOT NULL,
brand_name VARCHAR(40) NOT NULL,
goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
is_show BOOLEAN NOT NULL DEFAULT 1,
is_saleoff BOOLEAN NOT NULL DEFAULT 0
);
//INSERTtdb_goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VAL//UES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
//INSERT tdb_goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
//VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
//INSERT tdb_goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
//VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
//INSERT tdb_goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
//VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
//第三步：插入成功后，查看数据库
mysql> select * from tdb_goods\G;
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
goods_cate: 笔记本
brand_name: 华硕
goods_price: 3399.000
is_show: 1
is_saleoff: 0
*************************** 2. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
goods_cate: 笔记本
brand_name: 联想
goods_price: 4899.000
is_show: 1
is_saleoff: 0
*************************** 3. row ***************************
goods_id: 3
goods_name: G150TH 15.6英寸游戏本
goods_cate: 游戏本
brand_name: 雷神
goods_price: 8499.000
is_show: 1
is_saleoff: 0
*************************** 4. row ***************************
goods_id: 4
goods_name: X550CC 15.6英寸笔记本
goods_cate: 笔记本
brand_name: 华硕
goods_price: 2799.000
is_show: 1
is_saleoff: 0
4 rows in set (0.00 sec)
//第四步、在创建两个数据表，分别存储goods_cate和brand_name；
//创建tdb_goods_cates数据表
mysql> CREATE TABLE IF NOT EXISTS tdb_goods_cates(
-> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> cate_name VARCHAR(40)
-> );
Query OK, 0 rows affected (0.10 sec)
//写入数据
mysql> INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY
-> goods_cate;
Query OK, 2 rows affected (0.05 sec)
mysql> select * from tdb_goods_cates;
+---------+-----------+
| cate_id | cate_name |
+---------+-----------+
| 1 | 游戏本 |
| 2 | 笔记本 |
+---------+-----------+
2 rows in set (0.00 sec)
//创建tdb_goods_brands 数据表，并写入数据
mysql> CREATE TABLE tdb_goods_brands (
->
-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
->
-> brand_name VARCHAR(40) NOT NULL
->
-> ) SELECT brand_name FROM tdb_goods GROUP BY brand_name;
Query OK, 3 rows affected (0.25 sec)
Records: 3 Duplicates: 0 Warnings: 0
//第五步：通过tdb_goods_cates数据表来更新tdb_goods表
//更新goods_cate的值
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name
-> SET goods_cate = cate_id ;
Query OK, 4 rows affected (0.17 sec)
Rows matched: 4 Changed: 4 Warnings: 0
//更新g.brand_name 的值
mysql> UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name =
b.brand_name
-> SET g.brand_name = b.brand_id;
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4 Changed: 4 Warnings: 0
//第六步：更新后
mysql> select * from tdb_goods\G;
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
goods_cate: 2
brand_name: 1
goods_price: 3399.000
is_show: 1
is_saleoff: 0
*************************** 2. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
goods_cate: 2
brand_name: 2
goods_price: 4899.000
is_show: 1
is_saleoff: 0
*************************** 3. row ***************************
goods_id: 3
goods_name: G150TH 15.6英寸游戏本
goods_cate: 1
brand_name: 3
goods_price: 8499.000
is_show: 1
is_saleoff: 0
*************************** 4. row ***************************
goods_id: 4
goods_name: X550CC 15.6英寸笔记本
goods_cate: 2
brand_name: 1
goods_price: 2799.000
is_show: 1
is_saleoff: 0
4 rows in set (0.00 sec)
第七步：、修改goods_cate、brand_name的名称和类型；
mysql> ALTER TABLE tdb_goods
-> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
-> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
Query OK, 4 rows affected (0.44 sec)
Records: 4 Duplicates: 0 Warnings: 0
//修改成功后
mysql> select * from tdb_goods\G;
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
cate_id: 2
brand_id: 1
goods_price: 3399.000
is_show: 1
is_saleoff: 0
*************************** 2. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
cate_id: 2
brand_id: 2
goods_price: 4899.000
is_show: 1
is_saleoff: 0
*************************** 3. row ***************************
goods_id: 3
goods_name: G150TH 15.6英寸游戏本
cate_id: 1
brand_id: 3
goods_price: 8499.000
is_show: 1
is_saleoff: 0
*************************** 4. row ***************************
goods_id: 4
goods_name: X550CC 15.6英寸笔记本
cate_id: 2
brand_id: 1
goods_price: 2799.000
is_show: 1
is_saleoff: 0
4 rows in set (0.00 sec)
//分别为这三个数据表，插入数据，为了之后区分三种连接的含义做准备。
//插入成功
//mysql> INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
//Query OK, 3 rows affected (0.11 sec)
//插入成功
//mysql> INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');
//Query OK, 3 rows affected (0.04 sec)
//插入成功
mysql> INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro'>'> P1606dn 黑白激光打印机','12','4','1849');
Query OK, 1 row affected (0.07 sec)
//第八步，查看三个数据表。
//数据表tdb_goods;
mysql> select * from tdb_goods\G;
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
cate_id: 2
brand_id: 1
goods_price: 3399.000
is_show: 1
is_saleoff: 0
*************************** 2. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
cate_id: 2
brand_id: 2
goods_price: 4899.000
is_show: 1
is_saleoff: 0
*************************** 3. row ***************************
goods_id: 3
goods_name: G150TH 15.6英寸游戏本
cate_id: 1
brand_id: 3
goods_price: 8499.000
is_show: 1
is_saleoff: 0
*************************** 4. row ***************************
goods_id: 4
goods_name: X550CC 15.6英寸笔记本
cate_id: 2
brand_id: 1
goods_price: 2799.000
is_show: 1
is_saleoff: 0
*************************** 5. row ***************************
goods_id: 5
goods_name: LaserJet Pro
P1606dn 黑白激光打印机
cate_id: 12
brand_id: 4
goods_price: 1849.000
is_show: 1
is_saleoff: 0
5 rows in set (0.00 sec)
//数据表tdb_goods_cates
mysql> select * from tdb_goods_cates;
+---------+-----------+
| cate_id | cate_name |
+---------+-----------+
| 1 | 游戏本 |
| 2 | 笔记本 |
| 4 | 路由器 |
| 5 | 交换机 |
| 6 | 网卡 |
+---------+-----------+
5 rows in set (0.00 sec)
//数据表tdb_goods_brands
mysql> select * from tdb_goods_brands;
+----------+------------+
| brand_id | brand_name |
+----------+------------+
| 1 | 华硕 |
| 2 | 联想 |
| 3 | 雷神 |
| 4 | 海尔 |
| 5 | 清华同方 |
| 6 | 神舟 |
+----------+------------+
6 rows in set (0.00 sec)
//第九步：查询数据，查询出来后，是默认的数据，千万切记，这里不是更改数据表
mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
->
-> INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
->
-> INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;
*************************** 1. row ***************************
goods_id: 3
goods_name: G150TH 15.6英寸游戏本
cate_name: 游戏本
brand_name: 雷神
goods_price: 8499.000
*************************** 2. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
cate_name: 笔记本
brand_name: 华硕
goods_price: 3399.000
*************************** 3. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
cate_name: 笔记本
brand_name: 联想
goods_price: 4899.000
*************************** 4. row ***************************
goods_id: 4
goods_name: X550CC 15.6英寸笔记本
cate_name: 笔记本
brand_name: 华硕
goods_price: 2799.000
4 rows in set (0.00 sec)
//第十步：查询最终数据库存储的数据如下：
mysql> select * from tdb_goods\G;
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
cate_id: 2
brand_id: 1
goods_price: 3399.000
is_show: 1
is_saleoff: 0
*************************** 2. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
cate_id: 2
brand_id: 2
goods_price: 4899.000
is_show: 1
is_saleoff: 0
*************************** 3. row ***************************
goods_id: 3
goods_name: G150TH 15.6英寸游戏本
cate_id: 1
brand_id: 3
goods_price: 8499.000
is_show: 1
is_saleoff: 0
*************************** 4. row ***************************
goods_id: 4
goods_name: X550CC 15.6英寸笔记本
cate_id: 2
brand_id: 1
goods_price: 2799.000
is_show: 1
is_saleoff: 0
*************************** 5. row ***************************
goods_id: 5
goods_name: LaserJet Pro
P1606dn 黑白激光打印机
cate_id: 12
brand_id: 4
goods_price: 1849.000
is_show: 1
is_saleoff: 0
5 rows in set (0.00 sec)
ERROR:
No query specified总结，很可能有人看我博客看不懂，原因是我的博客是跟上一遍关联在一起的。所以这里给出大家视频的地址，而这里只是我自己的一个笔记。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
