MySQL学习笔记（九）多表更新

1. 将查询结果写入数据表

INSET[INTO] tbl_name [(col_name,...)] SELECT ...

示例：

//创建保存商品种类的数据表tdb_goods_cates

CREATE TABLE IF NOT EXISTS tdb_goods_cates(

                               cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

                               cate_name VARCHAR(40) NOT NULL);

//将数据表tdb_goods中的商品按种类分组，并将分组结果保存在表tdb_goods_cates

INSERT INTO  tdb_goods_cates(cate_name) SELECT  goods_cate FROM  tdb_goods GROUP BY goods_cate;

//查看数据表tdb_goods_cates的记录

SELECT * FROM  tdb_goods_cates；

2. 参照分类表tdb_goods_cates更新商品表tdb_goods，主要更新其中的goods_cate字段

参考点：

多表更新

UPDATE table_references SET col_name1 = {expr | DEFAULT} [,col_name = {expr2 | DEFAULT}] ... [WHERE where_condition]

//将参照分类表tdb_goods_cates更新商品表tdb_goods的语句如下

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;

//显示商品表的记录

SELECT * FROM tdb_goods;

3.创建数据表同时将查询结果写入到数据表中

CREATE TABLE[IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement。

因此，一步实现将商品表tdb_goods中的品牌brand_name进行分组，并且保存到新创建的品牌表中tdb_goods_brands

CREATE TABLE tdb_goods_brands(

                           brand_idSMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

                           brand_name VARCHAR(40) NOT NULL

                           ) SELECT brand_name FROM tdb_goods GROUP BY brand_name;

//显示品牌表的记录

SELECT * FROM tdb_goods_brands;

//再将商品表中的品牌字段更新为品牌表中的索引值

UPDATE tdb_goods INNER JOIN tdb_goods_brands ON tdb_goods.brand_name =tdb_goods_brands.brand_name SET tdb_goods.brand_name = brand_id;

注意：上述也可以通过起别名来实现，如下所示

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;

//再次将商品表中的goods_cate和brand_name的列名和定义修改为cate_id和brand_id

ALTER TABLE tdb_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

//在种类表和品牌表中添加记录

INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');

 
INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');

//在商品表中添加记录，因为种类表中没有12，因此有小错误，但是语法不会出错。

 INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

4. FROM子句中的查询

语法结构：SELECT... FROM (subquery) [AS] name;

说明：名称为必选项，且子查询的列名称必须唯一

5.连接：

MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。

语法结构：

table_references {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_references ON conditional_expr

6. 数据表参照：

table_references tbl_name [[AS]alias] | table_subquery [AS] alias;

数据表可使用tbl_name AS alias_name或tbl_name alias_name赋予别名。table_subquery可做为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。

7. 连接类型

INNER JOIN为内连接，在MySQL中，JOIN、CROSS JOIN 和 INNER JOIN是等价的。

LEFT[OUTER] JOIN为左外连接，RIGHT[OUTER] JOIN为右外连接。

左连接是以左表为准，即左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表中不足的地方均为NULL。右连接与左连接恰好相反

8. 连接条件

使用ON关键字来设定连接条件，也可以使用WHERE来代替。通常使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤。

SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;