-- 获取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;
