mysql 的高级使用
一：将查询结果插入到其他表（即复制表）
1：继上张表（https://www.cnblogs.com/1164xiepei-qi/p/13783331.html 【分组和聚合的使用】）的goods表中，添加一个分类信息
- 创建一个商品分类表，把goods表中的商品分类信息添加到该表中。
- 将goods表中的分类名称更改成商品分类表中对应的分类id
2. 创建商品分类表
-- 创建商品分类表 create table good_cates( id int not null primary key auto_increment, name varchar(50) not null );
3. 把goods表中的商品分类添加到商品分类表
-- 查询goods表中商品的分类信息
mysql> select cate_name from goods group by cate_name; +---------------+ | cate_name | +---------------+ | 台式机 | | 平板电脑 | | 服务器/工作站 | | 游戏本 | | 笔记本 | | 笔记本配件 | | 超级本 | +---------------+ 7 rows in set (0.00 sec)
将查询结果插入到good_cates表中mysql> insert into good_cates(name) select cate_name from goods group by cate_name; Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0
-- 添加移动设备分类信息
mysql> insert into good_cates(name) values("移动设备"); Query OK, 1 row affected (0.00 sec) mysql>
说明:
- insert into .. select .. 表示: 把查询结果插入到指定表中，也就是表复制。
二：使用连接更新表中的某个字段
1. 更新goods表中的商品分类信息
上面我们已经创建了一个商品分类表(good_cates)，并完成了商品分类信息的插入，现在需要更新goods表中的商品分类信息，把商品分类名称改成商量分类id。
-- 查看goods表中的商品分类名称对应的 商品分类表中的商品分类id mysql> select * from goods inner join good_cates on goods.cate_name=good_cates.name; +----+---------------------------------+---------------+------------+-----------+---------+------------+----+---------------+ | id | name | cate_name | brand_name | price | is_show | is_saleoff | id | name | +----+---------------------------------+---------------+------------+-----------+---------+------------+----+---------------+ | 1 | r510vc 15.6英寸笔记本 | 笔记本 | 华硕 | 3399.000 | | | 5 | 笔记本 | | 2 | y400n 14.0英寸笔记本电脑 | 笔记本 | 联想 | 4999.000 | | | 5 | 笔记本 | | 3 | g150th 15.6英寸游戏本 | 游戏本 | 雷神 | 8499.000 | | | 4 | 游戏本 | | 4 | x550cc 15.6英寸笔记本 | 笔记本 | 华硕 | 2799.000 | | | 5 | 笔记本 | | 5 | x240 超极本 | 超级本 | 联想 | 4880.000 | | | 7 | 超级本 | | 6 | u330p 13.3英寸超极本 | 超级本 | 联想 | 4299.000 | | | 7 | 超级本 | | 7 | svp13226scb 触控超极本 | 超级本 | 索尼 | 7999.000 | | | 7 | 超级本 | | 8 | ipad mini 7.9英寸平板电脑 | 平板电脑 | 苹果 | 1998.000 | | | 2 | 平板电脑 | | 9 | ipad air 9.7英寸平板电脑 | 平板电脑 | 苹果 | 3388.000 | | | 2 | 平板电脑 | | 10 | ipad mini 配备 retina 显示屏 | 平板电脑 | 苹果 | 2788.000 | | | 2 | 平板电脑 | | 11 | ideacentre c340 20英寸一体电脑 | 台式机 | 联想 | 3499.000 | | | 1 | 台式机 | | 12 | vostro 3800-r1206 台式电脑 | 台式机 | 戴尔 | 2899.000 | | | 1 | 台式机 | | 13 | imac me086ch/a 21.5英寸一体电脑 | 台式机 | 苹果 | 9188.000 | | | 1 | 台式机 | | 14 | at7-7414lp 台式电脑 linux ） | 台式机 | 宏碁 | 3699.000 | | | 1 | 台式机 | | 15 | z220sff f4f06pa工作站 | 服务器/工作站 | 惠普 | 4288.000 | | | 3 | 服务器/工作站 | | 16 | poweredge ii服务器 | 服务器/工作站 | 戴尔 | 5388.000 | | | 3 | 服务器/工作站 | | 17 | mac pro专业级台式电脑 | 服务器/工作站 | 苹果 | 28888.000 | | | 3 | 服务器/工作站 | | 18 | hmz-t3w 头戴显示设备 | 笔记本配件 | 索尼 | 6999.000 | | | 6 | 笔记本配件 | | 19 | 商务双肩背包 | 笔记本配件 | 索尼 | 99.000 | | | 6 | 笔记本配件 | | 20 | x3250 m4机架式服务器 | 服务器/工作站 | ibm | 6888.000 | | | 3 | 服务器/工作站 | | 21 | 商务双肩背包 | 笔记本配件 | 索尼 | 99.000 | | | 6 | 笔记本配件 | +----+---------------------------------+---------------+------------+-----------+---------+------------+----+---------------+ 21 rows in set (0.00 sec) --把goods表中的商品分类的名称改成 商品分类表中对应的id mysql> update goods as g inner join good_cates gc on g.cate_name=gc.name set g.cate_name=gc.id; Query OK, 21 rows affected (0.01 sec) Rows matched: 21 Changed: 21 Warnings: 0 --查询是否更改成功 mysql> select * from goods; +----+---------------------------------+-----------+------------+-----------+---------+------------+ | id | name | cate_name | brand_name | price | is_show | is_saleoff | +----+---------------------------------+-----------+------------+-----------+---------+------------+ | 1 | r510vc 15.6英寸笔记本 | 5 | 华硕 | 3399.000 | | | | 2 | y400n 14.0英寸笔记本电脑 | 5 | 联想 | 4999.000 | | | | 3 | g150th 15.6英寸游戏本 | 4 | 雷神 | 8499.000 | | | | 4 | x550cc 15.6英寸笔记本 | 5 | 华硕 | 2799.000 | | | | 5 | x240 超极本 | 7 | 联想 | 4880.000 | | | | 6 | u330p 13.3英寸超极本 | 7 | 联想 | 4299.000 | | | | 7 | svp13226scb 触控超极本 | 7 | 索尼 | 7999.000 | | | | 8 | ipad mini 7.9英寸平板电脑 | 2 | 苹果 | 1998.000 | | | | 9 | ipad air 9.7英寸平板电脑 | 2 | 苹果 | 3388.000 | | | | 10 | ipad mini 配备 retina 显示屏 | 2 | 苹果 | 2788.000 | | | | 11 | ideacentre c340 20英寸一体电脑 | 1 | 联想 | 3499.000 | | | | 12 | vostro 3800-r1206 台式电脑 | 1 | 戴尔 | 2899.000 | | | | 13 | imac me086ch/a 21.5英寸一体电脑 | 1 | 苹果 | 9188.000 | | | | 14 | at7-7414lp 台式电脑 linux ） | 1 | 宏碁 | 3699.000 | | | | 15 | z220sff f4f06pa工作站 | 3 | 惠普 | 4288.000 | | | | 16 | poweredge ii服务器 | 3 | 戴尔 | 5388.000 | | | | 17 | mac pro专业级台式电脑 | 3 | 苹果 | 28888.000 | | | | 18 | hmz-t3w 头戴显示设备 | 6 | 索尼 | 6999.000 | | | | 19 | 商务双肩背包 | 6 | 索尼 | 99.000 | | | | 20 | x3250 m4机架式服务器 | 3 | ibm | 6888.000 | | | | 21 | 商务双肩背包 | 6 | 索尼 | 99.000 | | | +----+---------------------------------+-----------+------------+-----------+---------+------------+ 21 rows in set (0.00 sec) mysql>
- 连接更新表中数据使用: update .. join .. 语句
三：创建表并且给字段添加数据
- 创建一个品牌表，把goods表中的品牌信息添加到该表中。
- 将goods表中的品牌名称更改成品牌表中对应的品牌id
-- 查询品牌信息 mysql> select brand_name from goods group by brand_name; +------------+ | brand_name | +------------+ | ibm | | 华硕 | | 宏碁 | | 惠普 | | 戴尔 | | 索尼 | | 联想 | | 苹果 | | 雷神 | +------------+ 9 rows in set (0.00 sec) -- 通过create table ...select来创建数据表并且同时插入数据 -- 创建商品分类表，注意: 需要对brand_name 用as起别名，否则name字段就没有值 mysql> create table good_brands( -> id int primary key auto_increment not null, -> name varchar(40) not null) select brand_name as name from goods group by brand_name; Query OK, 9 rows affected (0.01 sec) Records: 9 Duplicates: 0 Warnings: 0 -- 将goods表中的品牌名称更改成品牌表中对应的品牌id mysql> update goods as g inner join good_brands gb on g.brand_name = gb.name set g.brand_name = gb.id; Query OK, 21 rows affected (0.00 sec) Rows matched: 21 Changed: 21 Warnings: 0 mysql> select * from good_brands; +----+------+ | id | name | +----+------+ | 1 | ibm | | 2 | 华硕 | | 3 | 宏碁 | | 4 | 惠普 | | 5 | 戴尔 | | 6 | 索尼 | | 7 | 联想 | | 8 | 苹果 | | 9 | 雷神 | +----+------+ 9 rows in set (0.00 sec) mysql> select * from goods; +----+---------------------------------+-----------+------------+-----------+---------+------------+ | id | name | cate_name | brand_name | price | is_show | is_saleoff | +----+---------------------------------+-----------+------------+-----------+---------+------------+ | 1 | r510vc 15.6英寸笔记本 | 5 | 2 | 3399.000 | | | | 2 | y400n 14.0英寸笔记本电脑 | 5 | 7 | 4999.000 | | | | 3 | g150th 15.6英寸游戏本 | 4 | 9 | 8499.000 | | | | 4 | x550cc 15.6英寸笔记本 | 5 | 2 | 2799.000 | | | | 5 | x240 超极本 | 7 | 7 | 4880.000 | | | | 6 | u330p 13.3英寸超极本 | 7 | 7 | 4299.000 | | | | 7 | svp13226scb 触控超极本 | 7 | 6 | 7999.000 | | | | 8 | ipad mini 7.9英寸平板电脑 | 2 | 8 | 1998.000 | | | | 9 | ipad air 9.7英寸平板电脑 | 2 | 8 | 3388.000 | | | | 10 | ipad mini 配备 retina 显示屏 | 2 | 8 | 2788.000 | | | | 11 | ideacentre c340 20英寸一体电脑 | 1 | 7 | 3499.000 | | | | 12 | vostro 3800-r1206 台式电脑 | 1 | 5 | 2899.000 | | | | 13 | imac me086ch/a 21.5英寸一体电脑 | 1 | 8 | 9188.000 | | | | 14 | at7-7414lp 台式电脑 linux ） | 1 | 3 | 3699.000 | | | | 15 | z220sff f4f06pa工作站 | 3 | 4 | 4288.000 | | | | 16 | poweredge ii服务器 | 3 | 5 | 5388.000 | | | | 17 | mac pro专业级台式电脑 | 3 | 8 | 28888.000 | | | | 18 | hmz-t3w 头戴显示设备 | 6 | 6 | 6999.000 | | | | 19 | 商务双肩背包 | 6 | 6 | 99.000 | | | | 20 | x3250 m4机架式服务器 | 3 | 1 | 6888.000 | | | | 21 | 商务双肩背包 | 6 | 6 | 99.000 | | | +----+---------------------------------+-----------+------------+-----------+---------+------------+ 21 rows in set (0.00 sec) mysql>
说明:
- create table .. select 列名 .. 表示创建表并插入数据
四：修改表结构
目前我们已经把good表中的商品分类和品牌信息已经更改成了商品分类id和品牌id，接下来需要把 cate_name 和 brand_name 字段分别改成 cate_id和 brand_id 字段，类型都改成int类型
mysql> describe goods; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(150) | NO | | NULL | | | cate_name | varchar(150) | NO | | NULL | | | brand_name | varchar(40) | NO | | NULL | | | price | decimal(10,3) | NO | | 0.000 | | | is_show | bit(1) | NO | | b'1' | | | is_saleoff | bit(1) | NO | | b'0' | | +------------+------------------+------+-----+---------+----------------+ 7 rows in set (0.01 sec) mysql> alter table goods change cate_name cate_id int not null ,change brand_name brand_id int not null; Query OK, 21 rows affected (0.02 sec) Records: 21 Duplicates: 0 Warnings: 0 mysql> describe goods; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(150) | NO | | NULL | | | cate_id | int(11) | NO | | NULL | | | brand_id | int(11) | NO | | NULL | | | price | decimal(10,3) | NO | | 0.000 | | | is_show | bit(1) | NO | | b'1' | | | is_saleoff | bit(1) | NO | | b'0' | | +------------+------------------+------+-----+---------+----------------+ 7 rows in set (0.01 sec) mysql>
