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

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