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等价。

例子：

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，但是依然可以插

入记录，这是没有使用外键约束的结果。