MySQL基础知识（四)——子查询和连接

一.子查询

先创建一张商品表，后续对其进行操作。

# 创建一个商品表，商品id：goods_id 商品名称：goods_name 商品分类：goods_cate 
# 商品品牌：brand_name 商品价格：goods_price 是否上架：is_show  是否已售空：is_saleoff
CREATE TABLE IF NOT EXISTS tbl_goods(
    goods_id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_name  VARCHAR(150) NOT NULL,
    goods_cate  VARCHAR(40)  NOT NULL,
    brand_name  VARCHAR(40)  NOT NULL,
    goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
    is_show     BOOLEAN NOT NULL DEFAULT 1,
    is_saleoff  BOOLEAN NOT NULL DEFAULT 0
  );

1.子查询定义

   （1） 定义：

         子查询（Sbuquery）是指出现在其他SQL语句内的SELECT子句。

        语法： SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);

         其中SELECT * FROM t1，称为Outer Query/Outer Statement

         SELECT col2 FROM t2，称为SubQuery

  （2）要求：

         子查询指嵌套在查询内部，且必须始终出现在圆括号内；

         子查询可以包含多个关键字或条件，如DISTINCT，GROUP BY，ORDER BY，LIMIT，函数等。

         子查询的外层查询可以是：SELECT,INSERT,UPDATE,SET或DO。

  （3）返回值：

         子查询可以返回标量，一行，一列或子查询。

2.使用比较运算符的子查询

         使用比较运算符的子查询如=,>,<.>=,<=,<>,!=,<=>

        （1） 语法：operand comparison_operator subquery  

# 查商品表的平均价格
SELECT AVG(goods_price) FROM tbl_goods;
# 查商品表的平均价格,四舍五入保留两位小数
SELECT ROUND(AVG(goods_price),2) FROM tbl_goods;
# 上一步算出商品表商品平均价格为5845.10;查商品表价格大于平均水平的商品
SELECT * FROM tbl_goods WHERE goods_price >= 5845.10;
# 使用子查询查商品表价格大于平均水平的商品,比较运算可以产生子查询
SELECT * FROM tbl_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tbl_goods);

        （2）用ANY,SOME或ALL修饰的比较运算符

             语法：operand comparison_operator ANY (subquery)  

                        operand comparison_operator SOME (subquery)  

                       operand comparison_operator ALL (subquery)  

             ANY,SOME是符合任何一个就行，ALL是要符合所有，

            所以各个运算符和ANY，SOME，ALL关键字组合结果如下：

运算符/关键字	ANY	SOME	ALL
>,>=	最小值	最小值	最大值
<.<=	最大值	最大值	最小值
=	任意值	任意值
<>,!=			任意值

               如果使用运算符和子查询结果去比较，子查询结果不仅仅是一条会报错：

               ERROR 1242 (21000): Subquery returns more than 1 row

              这时候就要适当的使用ANY，SOME和ALL修饰比较运算符。

# 使用>ANY, 则大于最小值，即价格大于2899.000的结果集
SELECT * FROM tbl_goods WHERE goods_price > ANY(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');
# 使用>SOME, 和大于ANY一样，大于最小值，即价格大于2899.000的结果集
SELECT * FROM tbl_goods WHERE goods_price > SOME(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');
# 使用>ALL,要大于最大值，即价格大于9188.000的结果集
SELECT * FROM tbl_goods WHERE goods_price > ALL(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');

# 使用<ANY, 则小于最大值，即价格小于9188.000的结果集
SELECT * FROM tbl_goods WHERE goods_price < ANY(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');
# 使用<SOME, 和小于ANY一样，小于最大值，即价格小于9188.000的结果集
SELECT * FROM tbl_goods WHERE goods_price < SOME(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');
# 使用<ALL,要小于最小值，即价格小于2899.000的结果集
SELECT * FROM tbl_goods WHERE goods_price < ALL(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');

# 使用=ANY, 则等于3499.000   2899.000  9188.000  3699.000中的任意值
SELECT * FROM tbl_goods WHERE goods_price = ANY(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');
# 使用=SOME, 和等于ANY一样， 使用=ANY, 则等于3499.000   2899.000  9188.000  3699.000中的任意值
SELECT * FROM tbl_goods WHERE goods_price = SOME(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');
# 使用=ALL,是不会出现结果集的，因为不可能有一个价钱即等于3499.000，又等于 2899.000  9188.000  3699.000中的其他值
SELECT * FROM tbl_goods WHERE goods_price = ALL(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');

# 使用!=ALL,出现的结果集是不等于3499.000   2899.000  9188.000  3699.000这几个值外的其他值
SELECT * FROM tbl_goods WHERE goods_price != ALL(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');

3.使用[NOT] IN的子查询

          语法：operand comparison_operator [NOT] IN (subquery)

           =ANY运算符与IN等效

           !=ALL或<>ALL运算符与NOT IN等效

# IN,与使用=ANY结果等效, 则等于3499.000   2899.000  9188.000  3699.000中的任意值
SELECT * FROM tbl_goods WHERE goods_price IN (SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');
# NOT IN,与使用!=ALL结果等效，出现的结果集是不等于3499.000   2899.000  9188.000  3699.000这几个值外的其他值
SELECT * FROM tbl_goods WHERE goods_price NOT IN(SELECT goods_price FROM tbl_goods WHERE goods_cate = '台式机');

4.使用[NOT] EXISTS的子查询

          如果子查询返回任何行，EXISTS将返回TRUE；否则返回FALSE。

5.INSERT...SELECT   使用子查询将数据插入表中

            语法：INSERT [INTO] tbl_name[(col_name,...)] SELECT ...

# 将查询结果写入数据表
# 将原有表中的goods_cate值插入新的商品分类表
INSERT tbl_goods_cates (cate_name) SELECT goods_cate FROM tbl_goods GROUP BY goods_cate;

6.CREATE...SELECT 创建数据表同时将查询结果写入到数据表

            语法：CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement

# 创建一个商品品牌表,并将原有商品表中的品牌插入
CREATE TABLE tbl_goods_brands (
		brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
		brand_name VARCHAR(40) NOT NULL
		) 
SELECT brand_name FROM tbl_goods GROUP BY brand_name;
# 由于两个表的字段名称相同，所以要给两张表设置别名。使用内连接更新 tbl_goods的brand_name字段
UPDATE tbl_goods AS goods INNER JOIN tbl_goods_brands AS brands ON goods.brand_name = brands.brand_name SET goods.brand_name = brands.brand_id;
# 修改商品分类和商品品牌为数字型，因为数字型所占字符少
ALTER TABLE tbl_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL, CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

若是两个表有相同字段名，一定要给两张表设置别名，否则会报错：ERROR 1052 (23000): Column 'brand_name' in on clause is ambiguous 

二.连接 

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

            语法：

             table_reference {[INNER | CROSS] JOIN | {LIFE | RIGHT} [OUTER] JOIN} table_reference ON conditional_expr

 1.连接类型

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

         LEFT[OUTER] JOIN,左外连接。

         RIGHT[OUTER] JOIN,右外连接。

 2.连接条件

          使用ON关键字来设定连接条件，也可以使用WHERE来代替。

          通常使用ON关键字来设定连接条件，

          使用WHERE关键字进行结果集记录的过滤。          

 3.数据表参照

          table_reference 

          tbl_name [[AS] alias] | table_subquery [AS] alias 

          数据表可以使用tbl_name AS alias_name 或tbl_name alias_name 赋予别名

          table_subquery可以作为子查询使用在FROM语句中，这样的子查询必须为其赋予别名。

  4.内连接

          通常使用INNER JOIN。显示左表及右表符合连接条件的记录。

# 使用内连接查询商品表和商品分类表的交集
SELECT goods.goods_id,goods.goods_name,cates.cate_name FROM tbl_goods goods INNER JOIN tbl_goods_cates cates ON goods.cate_id = cates.cate_id;

         内连接补充：

         如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE字句中尝试以下操作：col_name IS NULL时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接条件的记录后停止搜做搜索更多的行。

 5.左外连接

         显示左表的全部记录和右表符合连接条件的记录。

# 使用左外连接查询商品表和商品分类表的交集，显示左表的全部信息和右表符合条件的信息，如果右表没有符合条件的记录，则返回空
SELECT goods.goods_id,goods.goods_name,cates.cate_name FROM tbl_goods goods LEFT JOIN tbl_goods_cates cates ON goods.cate_id = cates.cate_id;

 6.右外连接

         显示右表的全部记录和左表符合连接条件的记录。

# 使用右外连接查询商品表和商品分类表的交集，显示右表的全部信息和左表符合条件的信息，如果左表没有符合条件的记录，则返回空
SELECT goods.goods_id,goods.goods_name,cates.cate_name FROM tbl_goods goods RIGHT JOIN tbl_goods_cates cates ON goods.cate_id = cates.cate_id;

 7.多表连接 

# 多表连接要注意ON后面的连接条件
SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tbl_goods AS goods 
INNER JOIN tbl_goods_cates AS cates ON goods.cate_id = cates.cate_id 
INNER JOIN tbl_goods_brands AS brands ON goods.brand_id = brands.brand_id;

 8.外连接补充：

         A LEFT JOIN B join_condition

        数据表A中有的数据才能在结果集中显示出来，否则不能显示出来。

        数据表B的结果集依赖数据表A。

        数据表A的结果集根据左连接条件依赖所有数据表（B表除外）。

        左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）。

        如果数据表A的记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的记录。

 9.自身连接:

     同一个数据表对其自身进行连接

# 创建一张类别表，有三个字段 类型Id：type_id  类型名称：type_name 父类名称：parent_id
   CREATE TABLE tbl_goods_types(
     type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     type_name VARCHAR(20) NOT NULL,
     parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
  ); 

# 以子表作为左表，自身连接，查子类的父类名称
SELECT
	children.type_id,
	children.type_name,
	parent.type_name AS parent_type_name 
FROM
	tbl_goods_types AS children
	LEFT JOIN tbl_goods_types AS parent ON parent.type_id = children.parent_id;

# 以父表作为子表，自身连接，查子类的名称
SELECT
	parent.type_id,
	parent.type_name,
	children.type_name AS children_type_name 
FROM
	tbl_goods_types AS parent
	LEFT JOIN tbl_goods_types AS children ON parent.type_id = children.parent_id;

# 以父表作为子表，自身连接，查父类下子类的数目,并根据父表名称进行分类，根据父表id进行排序
SELECT
	parent.type_id,
	parent.type_name,
	count(children.type_name) AS children_type_count 
FROM
	tbl_goods_types AS parent
	LEFT JOIN tbl_goods_types AS children ON parent.type_id = children.parent_id 
GROUP BY
	parent.type_name 
ORDER BY
	parent.type_id;

 在使用GROUP BY想对结果集进行分组的时候,发生1055报错：

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'kimtest.parent.type_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我使用的数据库版本是：8.0.11.

解决办法如下：

# 查看sql_model参数命令
SELECT @@SESSION.sql_mode;
# 由于sql_model参数命令中有ONLY_FULL_GROUP_BY影响导致了异常，所以去掉ONLY_FULL_GROUP_BY这个值
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

这里有个小技巧，MySQL的所有报错都可以直接通过MySQL+编号来查找问题出现原因以及可解决办法.