MySQL学习15：子查询(二)
二子查询 3由[NOT] IN/EXISTS引发的子查询 使用[NOT] IN引发的子查询的语法结构： operand comparsion_operator [NOT] IN (subquery)。其中， =ANY运算 符与IN等价； !=ALL或ALL运算符与NOT IN等价。 例子： 1)查询所有商品中价等于超级本价(任意一个)的商品
二子查询
3由[NOT] IN/EXISTS引发的子查询
使用[NOT] IN引发的子查询的语法结构：operand comparsion_operator [NOT] IN (subquery)。其中，=ANY运算
符与IN等价；!=ALL或ALL运算符与NOT IN等价。
例子：
1)查询所有商品中价格等于超级本价格(任意一个)的商品SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price IN (SELECT
goods_price FROM tdb_goods WHERE goods_cate = '超级本');
2)查询所有商品中价格不等于超级本价格(任意一个)的商品
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT
goods_price FROM tdb_goods WHERE goods_cate = '超级本');
使用[NOT] EXISTS引发的子查询的语法结构：operand comparsion_operator [NOT] EXISTS (subquery);如果子
查询返回任何行，EXISTS将返回TRUE；否则返回FALSE。这种情况我们使用的比较少。
三子查询应用
(1)INSERT SELECT命令
使用INSERT...SELECT插入记录的语法结构：INSERT [INTO] table_name [(col_name,...)] SELECT ...;
1)先来创建一个数据表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
);
2)再对数据表tdb_goods中的记录进行商品类型分类
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
3)将查询的结果写入到数据表tdb_goods_cates
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROm tdb_goods GROUP BY goods_cate;
SELECT * FROM tdb_goods_cates;
4)参照商品分类表tdb_goods_cates的商品类型cate_id去更新商品表tdb_goods的商品类型(这里用到多表更新和
连接)
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
SELECT goods_id,goods_cate FROM tdb_goods;
(2)多表更新
多表更新的语法结构：
UPDATE table_references SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}] ... [WHERE
where_condition];
上述的例子就是多表更新。
1多表更新一步到位
我们在上面进行多表更新需要几个步骤，我们可以将其几个步骤结合在一起使用，也就是CREATE SELECT命
令哎创建数据表的同时将查询结果直接写入到指定的数据表中。
创建数据表同时将查询结果写入到数据表的语法结构：
CREATE TABLE [IF NOT EXISTS] table_name [(create_definition,...)] select_statement;
例子：
1)查找数据表tdb_goods中的商品品牌(不属于步骤，只是一个简单的查询)
SELECT brand_name FROM tdb_goods GROUP BY brand_name;
2)创建数据表的同时写入记录：
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;
SHOW TABLES;
SELECT * FROM tdb_goods_brands;
3)参照数据表tdb_goods_brands中的brand_id去更新数据表tdb_goods中的brand_name的品牌类型
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;
SELECT goods_id,brand_name FROM tdb_goods;
(3)多表更新带来的问题
到这里我们还有一个问题，就是更新了tdb_goods数据表中的goods_cate字段和brand_name字段，更新后的都
是数字类型的，而原来的是字符串型的，因此需要修改tdb_goods数据表中的那两个字段名称以及数据类型。
1)查看tdb_goods数据表的表结构
DESC tdb_goods;
2)修改指定列的数据类型和字段名称
ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
DESC tdb_goods;
修改之后的检查操作
3)分别在tdb_goods_cates和tdb_goods_brands表插入记录
INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神州');
4)在tdb_goods数据表中写入任意记录
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES('LaserJet Pro P1606dn 黑白激光打印
机',12,4,1849);
我们看到写入记录的时候中的cate_id是12，而在数据表tdb_goods_cates中的cate_id不存在12，但是依然可以插
入记录，这是没有使用外键约束的结果。