sql语句连接查询.
接触sql语句这么长时间了,牵涉到连接的时候,总是google别人的.
 
今天好好学学sql 的嵌套查询
 
嵌套查询 ,也不局限查询,也可以其他, 但本质是 一个sql语句中用到其他sql语句的结果了
 
sql执行顺序是, 由内向外执行, 
一、子查询的组成
　　1、包含标准选择列表组件的标准select查询。
　　2、包含一个或多个表或者视图名称的标准from子句。
　　3、可选的where子句。
　　4、可选的group by子句。
　　5、可选的having子句。

二、子查询的语法规则

　　1、子查询的select查询总是使用圆括号括起来。
　　2、不能包括compute或for.browse子句。
　　3、如果同时指定top子句，则可能只包括order
 by子句。
　　4、子查询最多可以嵌套到32层。个别查询可能会不支持32层嵌套。
　　5、任何可以使用表达式的地方都可以使用子查询，只要它返回的是单个值。
　　6、如果某个表只出现在子查询中而不出现在外部查询中，那么该表的列就无法包含在输出中。
建张表测试一下
测试环境 mysql5.6


例如 
select name,age from person where age > (select age from person where name ='孙权');
内外查询都使用同一张表(相互依赖的查询 称为相关子查询) 
运行update user set username ='lisi' where id =(select id from user where age=10);

报错 You can't specify target table 'user' for update in FROM clause (因为内外用了同一张表)
解决办法 
update user set username ='lisi' where id = (select t.id from (select id from user where age=10)t);
再用一次嵌套即可

三、简单子查询

　　示例：

select name,age from user 



where age > =



(




					select age from user




					where name =
									'lisi'
										


)


　　　输出结果为：
　　　
 
 
四、in嵌套查询
　　　in关键字用于where子句中用来判断查询的表达式是否在多个值的列表中。返回满足in列表中的满足条件的记录。
　　　示例：
select username,age from user

where id in(
select id from user
where age=10);
结果集

 

五、some或any嵌套查询

　　1、语法
　　some在sql中的逻辑运算符号，如果在一系列比较中，有些值为True，那么结果就为True。some的语法是：

　　<表达式>{ =|<>|!=|>|>=|!>|<|<=|!<}some(子查询)　　
												

　　示例：

select name from user



where id =
								some 　//用等号和以下查询到的值比较，如果与其中一个相等，就返回
											


(




					select id from user




					where age =
									'10'
										


)


　　输出结果为：


五、all嵌套查询

some
或any为 select ...from ... where a > result1 or a > result2 or a > result3
ALL关键字：
ALL关键字与any关键字类似，只不过上面的or改成and。

七、exists嵌套查询

　　　1、语法
　　　exists是sql中的逻辑运算符号。如果子查询有结果集返回，那么就为True。exists代表"存在"的意义，它只查找满足条件的那些记录。一旦找到第一个匹配的记录后，就马上停止查找。
select * from user where exists(select * from user where age=11);
 
 

 
 
后记:当然最主要的应用是不只是嵌套查询,还有根据而结果集筛选数据,更新数据等等
sql 语句是面向过程的,很恶心.

子查询：
 是嵌套在select ,insert ,update ,delete语句或者其他的子查询中的查询语句。 
 一、一般的使用方式：
 select * from 表1 where 列 运算符 （子查询）
 注意：1.先执行子查询，在执行外查询（主查询） 
 2.显示结果中只有主查询的内容
 3.子查询与比较运算符连用时，子查询只能返回单个值，不能是多个值
 二、子查询和连接查询
 1.子查询都可以代替连接查询 
 2.不是所有的连接查询都可以代替子查询
 3.子查询用于insert ,delete,update时筛选条件
 4.连接查询用于查看多个表的数据（结果集中包含多个表的信息的时候），
三、in或是not in 
 select * from 表名1 where 列 in（子查询）子查询可以返回某一列中的多个值，不能是多列
四、exists (子查询)
 select * from 表名1 where exists(子查询) 子查询有数据行的话返回true,否则返回false，一般都可以用in代替

SQL复杂查询
视图
为什么使用视图
视图的规则和限制
使用视图
更新视图
使用视图简化复杂联结
查看视图
用视图过滤不想要数据
使用视图与计算字段
子查询
使用子查询进行过滤
关联子查询
练习题一
SQL函数
算数函数
字符串函数
日期函数
谓词
LIKE - 字符串的部分一致查询
BETWEEN - 范围查询
IS NULL、IS NOT NULL - 判断是否为 NULL
IN - OR 的简便用法
EXIST
使用子查询作为 IN 谓词的参数
CASE 表达式
行转列
练习题二

视图
 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
为什么使用视图

重用SQL语句
简化复杂的SQL操作，编写查询后，可以方便地重用它而不必知道它的基本查询细节
使用表的组成部分而不是整张表
保护数据，可以给用户授予表的特定部分的访问权限而不是整张表的访问权限
更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据

 性能问题:

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降的很厉害，所以，在部署使用大量视图的应用前，先进行测试。
视图的规则和限制

与表一样，视图必须唯一命名(不能给视图取与其他视图和表相同的名字)
对于可以创建的视图数目没有限制
为了创建视图，必须具有足够的访问权限
视图可以嵌套，可以利用从其他视图中检索数据的查询来构造一个视图
ORDER BY 可以用在视图中，但如果从该视图中检索数据的SELECT语句中也包含ORDER BY，那么该视图的ORDER BY将被覆盖
视图不能索引，也不能有关联的触发器或默认值
视图可以和表一起使用

使用视图

创建视图

CREATE VIEW 视图名


使用SHOW CREATE VIEW viewname;查看创建视图的语句。
使用DROP删除视图

DROP VIEW viewname;


更新视图时，先使用DROP在使用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，第二条更新语句会创建一个视图，如果要更新视图存在，则第二条更新语句会替换掉原有视图。

更新视图

通常视图是可以更新的（即可以对它们使用INSERT,UPDATE和DELETE）更新一个视图将更新表（视图本身没有数据）如果对视图进行增加删除行，其实是对表进行增加删除行。

但并非所有的视图都可以更新，如果MySQL不能正确的确定更新的基数据，则不能进行更新，实际上，大多数视图都是不可更新的，因为视图主要用于数据检索。如果视图中以下操作，就不可以进行视图的更新：

分组(使用GROUP BY和HAVING)
联结
子查询
并
聚集函数(Min()，Count()，Sum()等)
DISTINCT
导出(计算)列

使用视图简化复杂联结
视图极大的简化了复杂SQL语句的使用，使用视图，可以一次性编写基础的SQL，然后按需多次使用。
CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num

创建了一个名为productcustomers的视图，联结了三个表，返回已订购了任意产品的所有客户的列表。

检索订购了产品TNT2的客户：
SELECT cust_name,cust_contact
FROM  productcustomers
WHERE prod_id ='TNT2';





cust_name
cust_contact




Coyote Inc
Y Lee


Yosemite Place
Y sam


查看视图
查看视图和查看表的语句相同
desc 视图名;
select * from 视图名

用视图过滤不想要数据
CREATE VIEW list AS 
SELECT id,username,price,date
FROM dataw
WHERE date IS NOT NULL;

将date为null的数据过滤掉，不在视图中显示。


使用视图与计算字段
CREATE VIEW data AS
SELECT
id,
username
date,
price*i AS iprice 
FROM dataw;


子查询
 任何sql语句都是查询，但是一般指的是SELECT语句。

sql允许创建子查询，即嵌套在其他查询中的查询。

子查询中最常见的使用是在WHERE子句的IN操作符中，以及用来填充计算列。
使用子查询进行过滤
SELECT product_type, cnt_product
FROM (SELECT *
        FROM (SELECT product_type, 
                      COUNT(*) AS cnt_product
                FROM product 
               GROUP BY product_type) AS productsum
       WHERE cnt_product = 4) AS productsum2;


其中最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。 虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。
关联子查询
SELECT product_type, product_name, sale_price
  FROM product AS p1
 WHERE sale_price > (SELECT AVG(sale_price)
                       FROM product AS p2
                      WHERE p1.product_type = p2.product_type
   GROUP BY product_type);


关联查询处理过程：

首先执行不带WHERE的主查询

根据主查询讯结果匹配product_type，获取子查询结果

将子查询结果再与主查询结合执行完整的SQL语句

练习题一

3.1

创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

条件 1：销售单价大于等于 1000 日元。

条件 2：登记日期是 2009 年 9 月 20 日。

条件 3：包含商品名称、销售单价和登记日期三列。

对该视图执行 SELECT 语句的结果如下所示。

SELECT * FROM ViewPractice5_1;

执行结果

product_name | sale_price | regist_date

--------------±-----------±-----------

T恤衫         | 　 1000    | 2009-09-20

菜刀          |    3000    | 2009-09-20

CREATE VIEW ViewPractice5_1 AS 
SELECT product_name, sale_price, regist_date
FROM product WHERE sale_price >= 1000 ADN regist_date = "2009-09-20";


3.2

向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');


对原始数据表不产生影响，只有对原始数据表进行操作，视图数据和原始数据表才会更新。


3.3

请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。


product_id | product_name | product_type | sale_price | sale_price_all

------------±------------±-------------±-----------±--------------------

0001       | T恤衫         | 衣服         | 1000       | 2097.5000000000000000

0002       | 打孔器        | 办公用品      | 500        | 2097.5000000000000000

0003       | 运动T恤       | 衣服          | 4000      | 2097.5000000000000000

0004       | 菜刀          | 厨房用具      | 3000       | 2097.5000000000000000

0005       | 高压锅        | 厨房用具      | 6800       | 2097.5000000000000000

0006       | 叉子          | 厨房用具      | 500        | 2097.5000000000000000

0007       | 擦菜板        | 厨房用具       | 880       | 2097.5000000000000000

0008       | 圆珠笔        | 办公用品       | 100       | 2097.5000000000000000

SELECT product_id, product_name, product_type, sale_price, 
(SELECT avg(sale_price) FROM product) AS sale_price_all
FROM product;



3.4

请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。


product_id | product_name | product_type | sale_price | avg_sale_price

------------±------------±-------------±-----------±--------------------

0001       | T恤衫         | 衣服         | 1000       |2500.0000000000000000

0002       | 打孔器         | 办公用品     | 500        | 300.0000000000000000

0003       | 运动T恤        | 衣服        | 4000        |2500.0000000000000000

0004       | 菜刀          | 厨房用具      | 3000        |2795.0000000000000000

0005       | 高压锅         | 厨房用具     | 6800        |2795.0000000000000000

0006       | 叉子          | 厨房用具      | 500         |2795.0000000000000000

0007       | 擦菜板         | 厨房用具     | 880         |2795.0000000000000000

0008       | 圆珠笔         | 办公用品     | 100         | 300.0000000000000000

提示：其中的关键是 avg_sale_price 列。与习题三不同，这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。
CREATE VIEW AvgPriceByType AS 
SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price, p2.avg_sale_price
FROM product AS p1, 
	(SELECT product_type,  avg(sale_price) AS avg_sale_price FROM product
    GROUP BY product_type) AS p2
WHERE p1.product_type = p2.product_type;


SQL函数
算数函数
创建samplemath表并插入数据。
-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT; -- 提交事务
-- 查询表内容
SELECT * FROM samplemath;


ABS – 绝对值

语法：ABS( 数值 )
MOD – 求余数

语法：MOD( 被除数，除数 )
ROUND – 四舍五入

语法：ROUND( 对象数值，保留小数的位数 )

ROUND 函数用来进行四舍五入操作。

SELECT m,
ABS(m)ASabs_col ,
n, p,
MOD(n, p) AS mod_col,
ROUND(m,1)ASround_colS
FROM samplemath;


注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。
字符串函数

CONCAT – 拼接

语法：CONCAT(str1, str2, str3)
LENGTH – 字符串长度

语法：LENGTH( 字符串 )
LOWER – 小写转换
UPPER 函数用于大写转换。

LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。
REPLACE – 字符串的替换

语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
SUBSTRING – 字符串的截取

语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）	

使用 SUBSTRING 函数 可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

日期函数

CURRENT_DATE – 获取当前日期


CURRENT_TIME – 当前时间

SELECT CURRENT_TIME



CURRENT_TIMESTAMP – 当前日期和时间

SELECT CURRENT_TIMESTAMP



EXTRACT – 截取日期元素

语法：EXTRACT(日期元素 FROM 日期)

【例子】

SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;


谓词


谓词：返回值为真值（TRUE/FALSE/UNKNOWN）的函数。
LIKE - 字符串的部分一致查询
SELECT *FROM  dataw WHERE username LIKE 'data%';



　%：0 字符以上的任意字符串。

_：任意 1 个字符。
SELECT *FROM dataw WHERE username LIKE 'datawh___';


SELECT *FROM dataw WHERE username LIKE 'datawh_____';


BETWEEN - 范围查询
SELECT * FROM dataw  WHERE price BETWEEN 4 AND 9;

re

BETWEEN会包含临界值，不需要临界值可以用>和<来处理
SELECT * FROM dataw  WHERE price > 4 AND price< 9;


IS NULL、IS NOT NULL - 判断是否为 NULL
为了选取部分值为 NULL 的列的数据，不能使用 =，只能使用 IS NULL
SELECT * FROM dataw WHERE date IS NULL;



取反（不为空的数据），请使用 IS NOT NULL。
SELECT * FROM dataw WHERE date IS NOT NULL;


IN - OR 的简便用法


用IN替换上面的SQL语句：



否定形式 NOT IN：



IN 和 NOT IN 是无法选取 NULL 数据的

EXIST
作用：判断是否存在满足某种条件的记录

　很多时候基本上可以使用 IN 或 NOT IN 来代替该谓词。





　NOT EXIST 与 EXIST 相反，不存在：
使用子查询作为 IN 谓词的参数
IN 和 NOT IN 谓词具有其它谓词没有的用法，它的参数可以是子查询。
SELECT price,username 
FROM dataw 
WHERE id IN(SELECT id FROM dataw WHERE username="datawhale");


SELECT price,username 
FROM dataw 
WHERE id NOT IN(SELECT id FROM dataw WHERE username="datawhale");


CASE 表达式
CASE 表达式：（条件）分歧。

语法：
CASE WHEN <判断表达式> THEN <表达式>
     WHEN <判断表达式> THEN <表达式>
     ...
     ELSE <表达式>
END

判断表达式类似“键 = 值”的形式，返回值为真值（TRUE/FALSE/UNKNOW）的表达式。如果结果为真，就会返回 THEN 子句中的表达式；如果不为真，就跳转到下一条 WHEN 子句的判断中；如果到最后的 WHEN 子句都不为真，就执行最后一条 ELSE 的表达式。



简化代码：


行转列



练习题二

3.5

运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）


会。


3.6

对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

①
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);

②
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);


3.7

按照销售单价（ sale_price）对练习 6.1 中的 product（商品）表中的商品进行如下分类。

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）

中档商品：销售单价在1001日元以上3000日元以下（菜刀）

高档商品：销售单价在3001日元以上（运动T恤、高压锅）

请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

执行结果

low_price | mid_price | high_price
----------+-----------+------------
        5 |         1 |         2

SELECT 
sum(CASE WHEN sale_price <= 1000 THEN 1 ELSE 0 END) AS low_price,
sum(CASE WHEN  sale_price BETWEEN 1001 AND 3000 THEN  1 ELSE 0 END ) AS mid_price,
sum(CASE WHEN sale_price  >3000 THEN 1 ELSE 0 END) AShigh_price
FROM product;



文章部分内容引用

SQL基础知识梳理：https://www.cnblogs.com/liqingwen/p/6572284.html

一 子查询
1 MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作
2 子查询虽然很灵活，但是执行效率并不高
3 执行子查询时，SQL语句中进行嵌套了SQL语句, 这就类似于循环了
4 例如有两张表, 我先查一张表,查一次, 然后拿这个结果去筛选查另一张表,查两次
二 连接查询(join)
1 可以使用连接查询(JOIN)代替子查询，连接查询需要建立临时表，,但因为联表操作不需要查询数据, 只需要在联表成新表之后查询一次, 因此其速度比子查询快
2 表关联是可以利用两个表的索引的，如果是用子查询，至少第二次查询是没有办法使用索引的
3 例如两张表, 我先把两张表拼在一起, 不查, 再查拼起来的新表, 查一次
总结：连接查询效率高于子查询！！！
1 扩展：多表联查性能优化
优化的本质就是(join on 和where的执行顺序)！！！
在使用left jion on时，on和where条件的区别如下：
1 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录
2 where条件是在临时表生成好后，再对临时表进行过滤的条件(这时已经和left join没关系了)，条件不为真的就全部过滤掉
2 left join on + where条件查询的索引优化实例分析
sql：select * from A left join B on A.c = B.c where A.employee_id = 3
解读： A表left join B表，并且指定A表中的employee_id为一个具体的值
1 假设A表，B表均有10000多条数据；
2 使用上面的sql查询时间达到16秒(在c字段不是任何索引，用explain分析得知，AB表都使用了全表查询，效率极低)
3 开始优化上面sql:
1 给AB表列c都加索引(仅用了0.1s，但是分析后显示表A依然进行了全表扫描)
思考：为什么全表扫描的不是表B.因为Mysql内部的优化，使用小表驱动大表，它在估算到必须有一个表要全表扫描的话，一定会选择那个数据量更小的表去全表扫描
也就是说，在这个查询中，因为on以后的where条件列并没有使用到索引，所以mysql的优化只用到了表B的c索引，没有用到表A的索引！
2 我们有where条件查询，不需要全表扫描，此时就需要where条件生效，操作及分析如下：
将A表中的索引改为employee_id+c(经验证两个所以都使用了，方案可行)
思考：sql执行 from中的on应该是优先于where语句的，为什么这里employee_id反而在c之前，有违常理
因为Mysql内部优化，这一句Sql在执行的时候首先是选择了使用表B的索引来进行优化，将表A单独放出来进行后续的操作，
然后，又发现了where语句中A.employee_id有一个聚合索引，并且employee_id处于索引头，所以这个聚合索引是可用的，所以自然使用了此索引
3 即使把聚合索引后面的列c删掉，与使用聚合索引的效果是一样的，之前全表查询，现在根据条件只查询了满足条件的，时间大幅缩短
3 扩展：mysql连接查询中索引的重要性
1 连接查询通过两张表中符合连接关系的字段来建立两张表的关联，通常包括内连接、左外连接、右外连接和全连接
2 内连接会保留两张表中共有的那部分记录，因此最后产生的连接表记录数最少；
3 全连接会保留两张表中所有的记录，因此最后产生的连接表记录数最多；
4 左外连接会保留左表的全部记录，右外连接会保留右表的全部记录，因此最后产生的连接表记录数处于内连接和外连接之间。
5 我们可以给关联表的字段添加索引来减少查询次数，提高查询效率
6 使用多表关联时，一般遵循以下规则：
6.1.左连接：一般给右边表的关联字段建立索引；
6.2.右关联：一般给左边表的关联字段建立索引；
6.3.内连接：一般给关联表的任意一边的关联字段建立索引即可
举例：学生表student(id，name)，课程表class(id,student_id,class),各有10000条数据
sql实例：SELECT a.id, name FROM student a LEFT JOIN class b ON a.id = b.student_id
这个查询的执行速度非常慢！！
1 首先用explain查看这个语句的查询执行计划，可以看到type都为ALL，即在student表和class表中都使用的全表扫描
2 改进：试着给class表的student_id字段添加索引alter table class add index class_index(student_id)
3 然后再次执行查询，发现速度非常快,这就是效率改进的关键点所在
4 当连接查询时产生的连接表过大时，为了防止查询次数过多，我们要经常使用索引来减少查询次数，提高查询效率