/*
1.子查询的主要优势为：
·   子查询允许结构化的查询，这样就可以把一个语句的每个部分隔离开。
·   有些操作需要复杂的联合和关联。子查询提供了其它的方法来执行这些操作。
·   在许多人看来，子查询是可读的。实际上，子查询给予人们调用早期SQL“结构化查询语言”的原本的想法，这是子查询的创新。
2.子查询的限定条件：
    1.有一个限定是，一个子查询的外部语句必须是以下语句之一：SELECT, INSERT, UPDATE, DELETE, SET或DO。
    2.还有一个限定是，目前，您不能在一个子查询中修改一个表，又在同一个表中选择。这适用于DELETE, INSERT, REPLACE和UPDATE语句
3.子查询的小结：
    1.子查询作为标量操作数
    2.比较子查询：
      =  >  <  >=  <=  <>
    3.使用ANY, IN和SOME进行子查询
    4.使用ALL进行子查询
    5.行子查询:
      通常用于与对能返回两个或两个以上列的子查询进行比较，如：查询两张表中完全相同的记录
    6.含有EXISTS和NOT EXISTS的子查询
*/
CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
CREATE TABLE t2 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(1, 'fff'),(2,'lll');
INSERT INTO t2 VALUES(1, 'fff'),(2,'lll');
/*1.子查询作为标量操作数：
 在有些情况下，标量子查询不能使用。如果一个语句只允许一个文字值，您不能使用子查询。
 例如，LIMIT要求文字整数自变量，LOAD DATA要求一个文字字符串文件名。您不能使用子查询来提供这些值。
*/
SELECT (SELECT s1 FROM t2) FROM t1;
/*一个标量子查询可以为一个表达式的一部分。不要忘记圆括号。即使是子查询是一个为函数提供自变量的操作数时，也不要忘记圆括号。举例说明：*/
SELECT UPPER((SELECT s1 FROM t2)) FROM t1;
/*2:比较子查询：
 比较子查询中的运算符：=  >  <  >=  <=  <>
 有时，子查询的合法位置只能在比较式的右侧,为了正确性我们尽量把子查询放在比较运算符的右侧
*/
/*使用比较子查询和相关子查询查询出重的记录的id*/
SELECT DISTINCT (s1)
FROM t1
WHERE 1 < (
SELECT COUNT(s1) FROM t1 t WHERE t.s1 = t1.s1
);
/*3.使用ANY, IN和SOME进行子查询
 in 是 =any的别名，二者的意思一样
 not in 不是 <>any的别名，而是<>all的别名
 some是any的别名
*/
SELECT s1,s2
FROM t1
WHERE t1.s1 = ANY (
SELECT s1 FROM t2
);
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);
/*使用词语SOME的机会很少，但是本例显示了为什么SOME是有用的。对于多数人来说，
英语短语“a is not equal to any b”的意思是“没有一个b与a相等”，但是在SQL语法中不是这个意思。
该语法的意思是“有部分b与a不相等”。使用<> SOME有助于确认每个人都理解该查询的真正含义。
*/
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
/*4.使用ALL进行子查询
 词语ALL必须接在一个比较操作符的后面
*/
/*用一条SQL语句 查询出每门课都大于80分的学生姓名*/
CREATE TABLE score(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),SUBJECT VARCHAR(20),score INT);
INSERT INTO score VALUES
(NULL,'张三','语文',81),
(NULL,'张三','数学',75),
(NULL,'李四','语文',76),
(NULL,'李四','数学',90),
(NULL,'王五','语文',81),
(NULL,'王五','数学',100),
(NULL,'王五 ','英语',90);
SELECT DISTINCT ss.name
FROM score ss
WHERE 80 < ALL(
SELECT s.score FROM score s WHERE s.NAME = ss.name
);
SELECT DISTINCT ss.name
FROM score ss
WHERE ss.NAME NOT IN (
SELECT DISTINCT s.name FROM score s WHERE s.score < 80
);
SELECT DISTINCT ss.name
FROM score ss
WHERE ss.NAME <> ALL (
SELECT DISTINCT s.name FROM score s WHERE s.score < 80
);
/*5:行子查询
 行子查询是一个能返回一个单一行的子查询变量，因此可以返回一个以上的列值。
 以下是两个例子：
 SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
 SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);
 如果在表t2的一个行中，column1=1并且column2=2，则查询结果均为TRUE。

 表达式（1，2）和ROW（1，2）有时被称为行构造符。两者是等同的，在其它的语境中，也是合法的。例如，以下两个语句在语义上
 是等同的（但是目前只有第二个语句可以被优化）：
   SELECT * FROM t1 WHERE (column1,column2) = (1,1);
   SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
 行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。例如，以下查询可以答复请求，“在表t1中查找同时也存在于表t2中的所有的行”:
 SELECT column1,column2,column3
 FROM t1
 WHERE (column1,column2,column3) IN
 (SELECT column1,column2,column3 FROM t2);
*/
SELECT s1,s2
FROM t1
WHERE (s1,s2) IN (
SELECT s1,s2 FROM t2
);
SELECT s1,s2
FROM t1
WHERE ROW(s1,s2) = ANY(
SELECT s1,s2 FROM t2
);
/*6.EXISTS和NOT EXISTS
   哪些种类的商店出现在一个或多个城市里？

·                SELECT DISTINCT store_type FROM stores
·                  WHERE EXISTS (SELECT * FROM cities_stores
·                                WHERE cities_stores.store_type = stores.store_type);
·         哪些种类的商店没有出现在任何城市里？

·                SELECT DISTINCT store_type FROM stores
·                  WHERE NOT EXISTS (SELECT * FROM cities_stores
·                                    WHERE cities_stores.store_type = stores.store_type);
·         哪些种类的商店出现在所有城市里？

·                SELECT DISTINCT store_type FROM stores s1
·                  WHERE NOT EXISTS (
·                    SELECT * FROM cities WHERE NOT EXISTS (
·                      SELECT * FROM cities_stores
·                       WHERE cities_stores.city = cities.city
·                       AND cities_stores.store_type = stores.store_type));
*/
CREATE TABLE stores(
store_type INT(10) PRIMARY KEY,
NAME VARCHAR(32)
);
INSERT INTO stores(store_type,NAME) VALUES(1,'汰渍洗衣粉'),(2,'双汇火腿肠'),
(3,'格力空调'),(4,'苹果手机'),
(5,'小米手机'),(6,'三星手机');
CREATE TABLE cities_stores(
city_id INT(10),
store_type INT(10),
CONSTRAINT s_id FOREIGN KEY (store_type) REFERENCES stores(store_type)
);
CREATE TABLE cities(
id INT(4)
);
INSERT INTO cities_stores(city_id,store_type) VALUES(1,1),(1,2),(1,3),
(1,4),(1,5),(1,6),
(2,1),(2,2),(3,6);
/*哪些种类的商店出现在一个或多个城市里？*/
SELECT store_type
FROM stores
WHERE EXISTS (
SELECT city_id FROM cities_stores WHERE cities_stores.store_type = stores.store_type
);
/*哪些种类的商店没有出现在任何城市里？*/
SELECT store_type
FROM stores
WHERE NOT EXISTS (
SELECT city_id FROM cities_stores WHERE cities_stores.store_type = stores.store_type
);
/*哪些种类的商店出现在所有城市里？*/
SELECT store_type
FROM stores
WHERE NOT EXISTS (
SELECT store_type
FROM stores
WHERE NOT EXISTS (
SELECT city_id FROM cities_stores WHERE cities_stores.store_type = stores.store_type
)
);