mysql学习-子查询和多表连接

2022-02-01 03:28:57

简单子查询

子查询可以完成SQL查询中比较复杂的情况

1. 简单子查询

子查询是SELECT 语句内的另外一条SELECT语句。通常，语句内可以出现表达式的地方都可以使用子查询，另外，子查询可以从任何表中提取，只要对该表有适当的访问权限即可，因此，通过在一个查询内或者在另一个查询内嵌套子查询，可以从两个或者多个表中组合信息而不必编写复杂的整个组合表，然后在过滤掉多余的或者不想管的联合行的JION的语句。

子查询的语法与普通的SELECT查询语法相同，子查询可以包括联合，WHERE子句，HAVING子句和GROUP BY 子句

1. 子查询的语法

(SELECT [ALL | DISTINCT ]<select item list>)
FROM <table list>
[WHERE <search conditon>]
[GROUP BY <group item list>]
[HAVING <group by searh conition>]])

语法规则：

  • 子查询的SELECT查询必须使用园括号括起来

  • 不能包括COMPUTE或者FOR BROWSE子句

  • 如果同时指定TOP子句，则可能只能包括ORDER BY 子句

  • 子查询最多可以嵌套32层

  • 任何可以使用表达式的地方都可以使用子查询，只要它返回的是单个值

  • 如果某个表只出现在子查询中而不是出现在外部查询中，那么该表的列就无法包含在输出中

2 子查询的常用的语法格式

--- 格式1
WHERE 查询表达式 [NOT] IN (子查询)

--- 格式2
WHERE 查询表达式 比较运算符 [ ANY | ALL ](子查询)

--- 格式3
WHERE [NOT] EXISTS (子查询)

3. 子查询和其他SELECT语句之间的区别

子查询除了必须在括号中出现以为，与其他的SELECT语句之间还有以下几点不同

  • SELECT 语句只能使用那些来自FROM子句中的表中的列，子查询不仅可以使用在该子查询的FROM子句的表，

而且还可以使用子查询的FROM子句的表的任何列

  • SELECT 语句的子查询必须返回单一列数据，另外根据其在查询中使用的方法（如将查询结果作用包括子查询的SELECT子句中的一个数据项），包括子查询的查询可能要求子查询返回单个值，（而不是来自单列的多个值）。

  • 子查询不能有ORDER BY子句,(因为用户看不到返回多个数据值的子查询的结果表，所以对隐藏的中间结果排序没有意义)

  • 子查询必须由一个SELECT 语句组成，也就是不能将多个SQL语句用UNION 组合起来为一个子查询

2. SELECTG 列表中的子查询

子查询是SELECT 查询内的返回一个值的表达式，就像返回值中的单一列一样，在一个表达式中，子查询必须只返回一条记录，这样的子查询被称为标量子查询（scalar subquery),也必须封闭在圆括号内

--- 根据名称获取最高价格
SELECT
    id,
    `name`,
    current_price,
    introduction,
    (
        SELECT
            MAX(current_price)
        FROM
            goods
    )
FROM
    goods

3. 比较子查询

1 . 使用比较运算符链接子查询

在WHERE子句中可以使用单行比较运算符来比较某个表达式与子查询的结果，可以使用的比较运算符包括=,>,>=，<>或者(!=)等。这些比较运算符都可以链接一个子查询，而且在使用ALL或者ANY修饰的比较运算符链接子查询的时候，必须保证子查询返回的结果集只有单行数据，否则会引起查询错误

--- 查询表id值大于其他名称的表
SELECT id ,subcat_id,`name`,introduction FROM goods
WHERE subcat_id >(
SELECT id
FROM subcat
WHERE cat_name=‘投资‘
);

2. 子查询的易错点

  • 子查询不能返回多个值，因此，如果子查询的结果不是返回单个值，那么系统就会发出错误信息

  • 子查询中不能包含ORDEY BY 子句，如果非要对数据进行排序查询，那么只能在外查询语句中使用ORDER BY子句。
SELECT id ,subcat_id,`name`,introduction FROM goods
WHERE subcat_id >(
SELECT id
FROM subcat
WHERE cat_name=‘投资‘
)
ORDER BY subcat_id

3. 在子查询中使用聚合函数

聚合函数SUM(),COUNT(),MAX(),MIN()和AVG()函数都返回单个数，在子查询中应用聚合函数，并将该函数返回的结果应用到WHERE子句的查询条件中。

SELECT id,`name`,current_price   FROM goods
WHERE current_price > (SELECT AVG(current_price) FROM goods);

子查询是SELECT 语句内的另外一条SELECT语句，也被称为SQL查询嵌套，在实际开发中，一条SQL语句中不要嵌套太多的子查询，否则降低系统效率，影响代码可读性

多行子查询

多行子查询通过多行比较操作符来实现，其返回值为多行。通常用的多行比较操作符也包括 IN，NOT IN ,ALL ANY/SOME,EXISTS 和NOT EXISTS

1. 使用IN ,NOT IN 操作多行子查询

1. 使用IN查询实现交集运算

IN子查询是值在外层查询和子查询之间使用IN进行连接，判断某个属性列是否在子查询的结果中，其返回的结果中可以包括零个或者多个值，在IN 子句中，子查询和输入多个运算符的数据的区别在于使用多个运算符输入时，一般都会输入两个或者两个以上的数值，而使用子查询的时候，不能确定其返回的结果的数量，但是即使子查询返回的结果为空，语句也能正常运行。

由于在子查询中，查询的结果往往是一个集合，所以IN子查询是子查询中最常用的，IN子查询语句的操作步骤可以分为两步，第一步执行内部子查询，第二步，根据子查询的结果再执行外层查询，IN子查询返回列表中的每个值，并显示任何相等的数据行。

--- 获取goods表中和subcat表中拒用相同ID的信息

SELECT * FROM goods
WHERE `name` IN (
SELECT cat_name 
FROM subcat
WHERE goods.subcat_id=subcat.id)
ORDER BY goods.current_price;

2. 使用NOT IN 子查询实现差集运算

子查询还可以用在外层的NOT IN 子句，以及NOT IN 的使用清单，如果外层查询中用来比较数据被查询出来与子查询产生的结果集中所有的值都不匹配，那么NOT IN 子句返回TRUE ，然后将该记录指定的列的值输入到最终结果集中。

--- 查询在tb_book图书中列表列出，而在tb_book_author作者表中没有列出的作者所在部门的图书信息
SELECT book_sort NOT IN (
    SELECT tb_author_department
    FROM tb_book_author);
)

使用NOT IN子查询的查询速度很慢，在对SQL语句的性能有所要求的时候，就要使用性能更好的语句来替代NOT IN。例如，可以使用外连接的方式替代NOT IN 以提高语句的执行速度。一般来说，使用NOT IN 和子查询的语句结构更加容易理解和编写。

2. EXISTS子查询与NO EXISTS子查询

3. EXISTS子查询实现两个表的交集

EXISTS子查询的功能是判断子查询的返回结果中是否有数据行，如果子查询返回的结果是空集，则判断为不存在，即EXISTS失败，NOT EXISTS成功。如果子查询返回至少一行的数据记录，则判断存在，即EXISTS成功，NOT EXISTS失败。由于EXISTS子查询中不需要返回具体值，所以该子查询的选择列表常用"SELECT *"格式，其外层的WHERE子句也不需要指定列名

EXISTS通常都和相关子查询一起使用。在使用相关子查询的时候，对外表中的每一行，子查询都要运行一遍，该行的值也要在子查询WHERE子句中被使用。这样通过EXISTS子句就能将外层表中各行数据依次与子查询处理的内层表中的数据进行存在比较，得到需要的结果。

关键字EXISTS引入子查询的语法：

  • 关键字EXISTS一般直接跟在外层查询的WHERE子句后面，它的前面没有列名，通常为表达式。

  • 关键字EXISTS引入的子查询的SELECT列表清单中可以而且通常都是由* 组成的。因为只是测试满足子查询的数据行的存在性，所以子查询的SELECT列表中清单加入列名没有实际意义

1 . EXISTS 和IN

以下两种查询结果是一样的

SELECT DISTINCT goods_name
FROM goods
WHERE EXISTS (
    SELECT * 
    FROM brand
    WHERE cat_id=goods.cat_id AND name=‘索尼‘);
)
SELECT DISTINCT goods_name
FROM goods
WHERE cat_id IN (
    SELECT cat_id
    FROM brand
    WHERE cat_id=goods.cat_id AND name= ‘索尼‘
)

2. 比较使用EXISTS和“=ANY"查询

-- 查找两个表中相同subcat_id,EXISTS的方法
SELECT `name`,subcat_id,introduction
FROM goods
WHERE EXISTS( 
SELECT id
FROM subcat
WHERE goods.subcat_id =subcat.id
)
-- 查找两个表中相同subcat_id,使用"=ANY"的方法
SELECT `name`,subcat_id,introduction
FROM goods
WHERE id= ANY(
SELECT id 
FROM subcat);

两种方法查询的结果一样

-- 获取goods表和subcat表中具有相同id的信息
SELECT * FROM goods
WHERE EXISTS (
select id 
FROM subcat
WHERE goods.subcat_id=subcat.id)
ORDER BY goods.current_price

说明：一些带有EXISTS或者NOT EXISTS的子查询不能被其他形式的子查询等价替代，但是所有带IN,ANY,ALL和比较运算符的子查询都能用带EXISTS的子查询等价替换

3. NOT EXISTS子查询实现两个表的差集

使用NOT EXISTS后，如果子查询的结果为空，则外层的WHERE子句返回TRUE

-- 获取tb_book图书表中与tb_book_author作者表中相同不问外的图书信息
SELECT *
FROM tb_book
WHERE NOT EXISTS(
    SELECT tb_author_department
    FROM tb_book_author
    WHERE tb_book.book_sort=tb_book_author.tb_author_department);
)

3. 通过量词实现多行子查询

1. 使用量词实现多行子查询

ALL,SOME,ANY是量词，允许比较运算符左边的单值与生成的单列但多行结果集的子查询（在比较运算符的右边）相比较，如果WHERE子句查询生效生成多个值的单列结果，将终止以下形式的查询

SELECT <column name list>
FROM <table>
WHERE <expr> {=|<>|>|>=|<|<=} <subquer>
-- goods表中查询售价高于平均售价的商品信息
SELECT id,`name`,current_price from goods
WHERE current_price < SOME (
SELECT AVG(current_price) FROM goods 
GROUP BY id)

如果子查询的单列结果表不只有一行数据，此时将不会终止查询

2. 使用ALL操作符的多行子查询

ALL操作符比较子查询返回列表中的每一个值，<ALL 为小于最小的，>ALL为大于最大的，而=ALL则没有返回值，因为在等于子查询的情况下，返回列表中的所有值是不符合逻辑的

ALL允许比较运算符前面的单值与比较运算符后面的子查询返回的集合中的每一个值相比较，另外，仅当所有的(ALL)的比较运算符左边的单值与子查询的返回值的集合中的没每一个值比较求值为TRUE的时候，比较判断以及WHERE才求值为TRUE

-- 获取所有售价低于平均价的商品信息
SELECT id,`name`,current_price  FROM goods
WHERE current_price < ALL(
SELECT AVG(current_price) 
FROM goods
GROUP BY id)

3. 使用ANY/SOME操作符的多行子查询

ANY操作符比较子查询返回列表中的每一个值，<ANY为小于最大的，>ANY为大于最小的，=ANY为等于IN。
ANY允许将比较运算符前面的单值与比较运算符后面的子查询返回的结果集中的每一个值相比较，另外当所有的ANY比较运算符左边的单值与子查询返回值的结果中的每一个值的比较为TRUE，比较判断的求值就为TRUE

-- 获取所有售价低于平均价的商品信息
SELECT id,`name`,current_price  FROM goods
WHERE current_price < ANY(
SELECT AVG(current_price) 
FROM goods
GROUP BY id);

量词SOME，和ANY是同意的，他们都允许将比较运算符前面的单值与比较运算符后面的子查询返回的结果集中的每个值进行比较，如果比较运算前面的单值与比较运算后面的查询返回结果集中的每一个值之间的任何比较求值为TRUE，那么WHRER求值九尾TRUE

多表连接

SQL最强大的功能之一就是在查询数据的时候能够连接多个表。连接时非常重要的操作，通过连接可以实现更多，更复杂的查询。

内连接

内连接就是使用比较运算符进行表和表之间的列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，内连接可以用来组合两个或者多个表中的数据。
在内连接中，根据使用的比较方式不同，可以经内连接分为以下三种。

  • 等值连接；在连接条件中使用等于运算符比较被连接的列。

  • 不等值连接；在连接条件中使用除了等于运算符以为的其他的比较运算符比较被连接的列。

  • 自然连接；它是等值连接的一种特殊情况，用来把目标中重复的属性去掉。

1. 等值连接

等值连接时值在连接条件中使用“=”运算符比较被连接的列，在连接条件中的各个连接列的类型必须时可比的，但不一定时相同的。例如，可以是字符型或者都是日期型；也可以是一个整数，因为他们都是数值型。

虽然连接条件中各列的类型可以不同，但是在应用中最好还是使用相同的类型，因为系统类型转换需要花费很多时间。

-- 通过等值连接查询id,name,inroname
SELECT subcat_id,`name`,introduction FROM goods,subcat
WHERE goods.subcat_id=subcat.id;

说明,WHERE子句作为过滤条件是非常重要的，在关系型数据库中，表就是一个联合，当从两个或者多个表中查询数据的时候，如果没有指定条件，那么这种查询结果就是笛卡尔乘积。笛卡尔乘积就是从多个表中提取数据的时候，在WHERE子句中灭有指定多个表的公共关系。

对于等值连接还可以使用一种特定的语法，明确指出连接的类型，具体如下：

SElECT fieldlist
FROM table1 [INNER] JOIN table2
ON table1.column=table2.column

参数说明：

  • fieldlist:要查询的列

  • table [INNER] JION table2; 将table1表与table2表进行内部连接

  • table1.column=table2.column:table1表中与table2表中被连接的列。

2. 不等值连接

在SQL中既支持等值连接，也支持不等值连接。不等值连接是指在连接条件中使用除了等于运算符以为的其他比较运算符比较被连接列值。可以使用的运算符包括:>,>=,<=，！>等

-- 通过不等值连接查询商品
SELECT a.goods_id,a.goods_name
FROM goods a INNER JOIN (SELECT * FROM goods_type WHERE name=‘平板电脑‘) b
ON a.goods_type <> b.id

3. 自然连接

自然连接是等值的一种特殊形式。如果是按照两个表中相同属性进行等值连接，且目标去除重复的列，保留所有不重复的列，则可以称之为自然连接。自然连接只有在两个表中有相同的列且列的含义相似的时候才能使用。

-- 在user用户信息表和user_address用户表地址中，通过自然连接查询用户的用户id等信息
SELECT a.user_id,b.address,CONVERT(VARCHAR(10),last_login,120) AS last_login
FROM users a ,user_address b
WHERE a.user_id=b.user_id

4. 使用带聚合函数的内连接

聚合函数用来汇总数据

-- 通过内连接查询商品分类id,商品分类名称（name）和对应商品的数量
SELECT a.id,a.name,COUNT(b.cat_id) num
FROM goods_category a INNER JION goods b
ON a.id=b.cat_id GROUP BY a.id,a.name

5 . 连接多个表

SQL不会限制一条SELECT 子查询语句中可以连接的表的数量。在创建多个表的连接查询的时候，首先定义要查询的列，然后通过各个表之间的关系定义连接条件

-- 通过内连接查询商品的商品ID等信息
SELECT a.subcat_id,a.`name`,b.cat_name,c.cat_name
FROM goods a,subcat b,supercat c
WHERE a.subcat_id=b.id AND a.supercat_id=c.id;

注意：

  • 在使用中如果为表格指定了别名，那么在该sql语句中对表的所有显示引用都必须使用别名，而不能使用表名

  • 如果连接中的多个表中有相同的名称的列存在，要求必须使用表表或者别名来限定列名

外连接

有时需要显示表中所有的记录，包括对奈雪儿不符合连接条件的记录，此时就需要使用外连接。使用外连接可以翻盖你的在连接结果中包含某个表的其他记录，外连接的查询结果是内连接查询结果的扩散

外连接一个显著的点是将某些不满足连接条件的数据也在连接结果中输出。外连接以指定的数据表为主体，将主体表中不满足连接条件的数据也一并输出。根据外连接保存下来的行的不同，可以将外连接分为下面三种：

  • 左外连接： 表示在结果中包括左表不满足条件的数据

  • 右外连接： 表示在结果中包括不满足的条件的数据

  • 全外连接： 表示在结果中包括左表和右表不满足条件的数据

在连接语句中，JOIN关键字左边的表示左表，右边的表示右表

1. 左外连接

左外连接保留了第一个表的所有行，但只包括第二个表与第一个表匹配的行。第二个表的相应的空行被放入NULL列

语法：

SELECT fieldlist
FROM table1 LEFT JOIN table2
ON table1.column=table2.column
-- 通过左外连接查询每个商品的id,name和类型

SELECT  subcat_id,`name`,introduction
FROM goods LEFT JOIN subcat
ON goods.subcat_id=subcat.id ORDER BY subcat_id DESC

2. 右外连接

右外连接保留了第二个表的所有行，但是只包含第一个表与第二个表匹配的行，第一个表的相应的空行被放入NULL值。

语法

SELECT goods_id,goods_name,name
FROM goods RIGHT JOIN table2
ON table1.column=table2.column
-- 通过外连接查询每个商品的id,名称
SELECT  subcat_id,`name`,introduction
FROM goods RIGHT  JOIN subcat
ON goods.subcat_id=subcat.id ORDER BY subcat_id DESC

3.全外连接

全外连接是将两个表所有的行都显示在结果中。返回的结果除内连接的数据外，还包括两个表中不符合条件的数据，并在左表或者右表中相应的列中放入NULL值。

语法

SELECT fieldlist
FROM table1 FULL JION table2
ON table1.coulumn=tabel2.column
-- 通过全连接查询
SELECT  subcat_id,`name`,introduction
FROM goods FULL JOIN subcat
ON goods.subcat_id=subcat.id ORDER BY subcat_id DESC

注意，mysql,access,sqlite不支持全外连接

其他连接

自连接

自连接就是指一个表同自身进行连接，为了更好地理解自连接，可以把一个表想象成两个独立的表，而在FROM子句中表被列出了两次，为了区别，必须给每个表提供一个别名来区分这两个副本。

-- 通过自连接查询
SELECT g1.id,g1.`name`,g1.subcat_id
FROM goods g1,goods g2
WHERE g1.`name`=g2.`name`
AND g2.subcat_id=35;

交叉连接

交叉连接会将第一个表的每一行与第二个表的每一行匹配，这导致了所有可能的合并。在交叉连接中列是原表列的数量的总和（相加），交叉连接中的行是原表中行数的积（相乘）。交叉连接通过CROSS JOIN关键字来完成，并忽略掉ON条件。

语法： 

SELECT  fieldlist
FROM table1
CORSS JOIN table2
-- 通过交叉连接查询
SELECT b.cat_name,b.super_cat_id,a.`name`
FROM subcat b CROSS goods a;

组合查询

组合查询通过UNION操作符来完成，使用UNION可以执行多个SELECT查询语句，并将多个查询结果作为一个查询结果集返回，以下几点需要注意

  • 使用UNION操作符进行组合拆查询必须由两个或者两个以上的SELECT语句组成，语句之间使用UNION关键字分隔

  • 要求每个SELECT语句中列的数目必须相同，而且对应位置上的列的数据类型必须相同或者兼容

  • 最后结果集中的列名是由第一个SELECT语句的列名决定的

在使用UNION进行组合查询的时候，可以对一个表进行多个查询，也可以对不同的表进行多个查询

通过UNION合并多个结果集

-- goods商品信息中，首先查询分类id(subcat_id) 为36和37的所有商品信息，然后查询商品名称（name)中包含“联想”的所有商品信息，并对两个查询结果进行合并
SELECT  subcat_id,`name`,current_price   FROM goods
WHERE subcat_id IN (36,37)
UNION 
SELECT subcat_id,`name`,current_price   FROM goods
WHERE `name` LIKE ‘%联想%‘;
-- 查询结果合并

SELECT BookName,Writer,Price
FROM bookinfo
WHERE Price=59.89
UNION
SELECT BookName,Writer,Price
FROM book_info_zerobasis
WHERE Price=69.80

通过UNION ALL返回重复的行

UNION 操作符会从最后的结果集中自动去除重复的行，如果希望返回重复的行，则要使用UNION ALL

-- 查询结果合并,返回重复的行

SELECT BookName,Writer,Price
FROM bookinfo
WHERE Price=59.89
UNION
SELECT BookName,Writer,Price
FROM book_info_zerobasis
WHERE Price=69.80

对组合查询结果进行排序

在使用UNION操作符进行组合查询的时候，查询结果将对SELECT列表中按照从左到右的顺序自动排序，在使用UNION组合查询的时候，只能使用一个ORDER BY 子句，而且该子句必须放在最后一个SELECT 语句之后，所使用的排序名必须是第一个SELECT语句的列名

-- 查询完后按照价格进行排序

SELECT  subcat_id,`name`,current_price   FROM goods
WHERE subcat_id IN (36,37)
UNION  ALL
SELECT subcat_id,`name`,current_price   FROM goods
WHERE `name` LIKE ‘%联想%‘
ORDER BY current_price;
上一篇：oracle文件属性改变导致实例无法mount磁盘


下一篇：[MySQL] 解决mysql导入大文件问题max_allowed_packet