对select的一些总结和理解

SELECT基础

查询的书写顺序：

SELECT [ALL|DISTINCT]查询字段  FROM -> WHERE -> GROUP BY-> HAVING -> ORDER BY -> LIMIT（offset）

执行顺序 ：

from--where--group by--having--select--order by--limit

from:需要从哪个数据表检索数据  
where:过滤表中数据的条件 
group by:如何将上面过滤出的数据分组 
having:对上面已经分组的数据进行过滤的条件 
select:查看结果集中的哪个列，或列的计算结果 
order by :按照什么样的顺序来查看返回的数据 
limit：限制返回的数据量

下面分别剖析下每个关键字

 a. 查询表达式
    -- 可以使用表达式（计算公式、函数调用、字段也是个表达式）
        select stu, 29+25, now() from tb;
    -- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。
        - 使用 as 关键字，也可省略 as.
        select stu+10 as add10 from tb;
b. FROM 子句
    用于标识查询来源。
    -- 可以为表起别名。使用as关键字。
        SELECT * FROM tb1 AS tt, tb2 AS bb;
    -- from子句后，可以同时出现多个表。SELECT * FROM tb1, tb2;
        -- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积。
        
    -- 向优化符提示如何选择索引
        USE INDEX、IGNORE INDEX、FORCE INDEX
        SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
        SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
c. WHERE 子句
    -- 从from获得的数据源中进行筛选。
    -- 整型1表示真，0表示假。
    -- 表达式由运算符和运算数组成。
        -- 运算数：变量（字段）、值、函数返回值
        -- 运算符：
            =, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
            (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
            is/is not 加上ture/false/unknown，检验某个值的真假
            <=>与<>功能相同，<=>可用于null比较
d. GROUP BY 子句, 分组子句
    GROUP BY 字段/别名
    以下[合计函数]需配合 GROUP BY 使用：
    count计算行数 count(*)、count(字段)
    sum 求和
    max 求最大值
    min 求最小值
    avg 求平均值

e. HAVING 子句，条件子句

f. ORDER BY
    order by 排序字段/别名 排序方式(默认升序)
    升序：ASC，降序：DESC
    支持多个字段的排序。

g. LIMIT + offset
1. 当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量,
select* from article LIMIT 1,3 就是跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据
2. 如果省略第一个值，则默认为0，即表示从0开始要取的数据的数量
例如 select * from article LIMIT 3  表示取前三条数据，
3. 当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量
例如select * from article LIMIT 3 OFFSET 1 表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

h. DISTINCT, ALL 选项
    distinct 去除重复记录
    默认为 all, 全部记录
去除多列的重复结果
SELECT DISTINCT 列名1, 列名2, ... 列名n  FROM 表名;

where和having对比

• where和having可以同时使用， having筛选的字段必须是select后面查询出来的，where筛选的字段必须是数据表存在的。所以只要select后查询数据库中存在的字段，where和having就可以同时使用。

• where+group by是先过滤然后分组，group by+having是先分组然后筛选

• where 不可以使用聚合函数，因为where在group by之间，而聚合函数是针对group by得到的分组的。
  having在group by之后，所以需用聚合函数时可以用 having，比如having count(*)>1(常用聚合函数有：sum、count、avg、max和min)

• where可以用于增删改查中， having只能用于select语句中

• having 可以使用字段的别名，但where 不可以，因为执行WHERE代码时，可能尚未确定列值。

1、只可以用where，不可以用having的情况

  select goods_name from sw_goods where goods_price > 100 //ok
 select goods_name,from sw_goods having goods_price > 100  //报错！！！因为前面并没有筛选出goods_price 字段
 //改成下面这样是对的了
 select goods_name, goods_price from sw_goods having goods_price > 100 

2、只可以用having，不可以用where情况
查询每种goods_category_id商品的价格平均值，获取平均价格大于1000元的商品信息

select avg(goods_price) as ag from sw_goods   having ag>1000 //ok
select avg(goods_price) as ag from sw_goods   where ag>1000 //报错,不能使用聚合函数

3、二者一起使用

select a,sum(b) from table where a=1 group by b having b>1

执行流程：WHERE ——GROUP BY——HAVING

offset过大优化

limit分页查询时，如果设置了offset值， mysql是从头将数据都读出来，然后丢弃前面的offset条数据，当offset过大时，前面丢弃的数据过多，导致性能很差。

优化思路：子查询
比如先用limit查出起始的id(只查id走性能开销小)，然后再一次用limit从起始id开始查，查出完整的数据。

举例:
经过我的多次验证，下面这条sql语句运行时间保持在0.0187左右

select * from user limit 10000,10; 

子查询优化之后，时间保持在0.0061左右，只有前者的1/3。

select * from user where uid >=( select uid from user order by uid limit 10000,1 ) limit 10; 

sql注入问题

mybatis中的三种模糊查询写法：

推荐：
 @Select("select * from user_dynamic WHERE NAME LIKE concat('%',#{name},'%');")
List<UserModel> getList3(String name);
不推荐：
 @Select("select * from user_dynamic WHERE NAME LIKE concat('%','${value}','%');")
@Select("select * from user_dynamic WHERE NAME LIKE '%${value}%';")
#{}名字随便 ${} 必须为value

${}存在sql注入问题
#{}不存在
解析：
默认情况下,使用#{}语法,MyBatis会产生PreparedStatement语句中，并且安全的设置PreparedStatement参数，这个过程中MyBatis会进行必要的安全检查和转义。

示例1：
执行SQL：Select * from emp where name = #{employeeName}
参数：employeeName=>Smith
解析后执行的SQL：Select * from emp where name = ?
执行SQL：Select * from emp where name = ${employeeName}
参数：employeeName传入值为：Smith
解析后执行的SQL：Select * from emp where name =Smith

综上所述 $ { }方式会引发SQL注入的问题、同时也会影响SQL语句的预编译，所以从安全性和性能的角度出发，能使用#{}的情况下就不要使用${}

但是${}在什么情况下使用呢？

有时候可能需要直接插入一个不做任何修改的字符串到SQL语句中。这时候应该使用${}语法。

比如，动态SQL中的字段名，如：ORDER BY ${columnName}

注意：当使用${}参数作为字段名或表名时、需指定statementType为“STATEMENT”，如：

<select id="queryMetaList" resultType="Map" statementType="STATEMENT">Select * from emp 
where name = ${employeeName} ORDER BY ${columnName}</select> 

连接查询join

能不能使用 join

都说join操作消耗性能，尽量不要使用，那在开发中到底能不能使用 join呢？
我的理解是：
如果可以使用被驱动表的索引，join 语句还是有其优势的；

如果不能使用被驱动表的索引，就尽量不要使用；

所以判断要不要使用 join 语句时，就是看 explain 的Extra 字段里面有没有出现“Block Nested Loop”字样。如果出现说明没有索引，就不要用join了，改为子查询比较好。

join优化

当t1 join t2 时，mysql 会选择一个表作为驱动表，另一个就是被驱动表，至于选哪一个由mysql根据性能做判断，实际是选择小表作为驱动表。

join最基本的查询方式就是嵌套循环连接

用伪代码表示一下这个过程就是这样：

for each row in t1 {   #此处表示遍历满足对t1单表查询结果集中的每一条记录
    
    for each row in t2 {   #此处表示对于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录
    
        for each row in t3 {   #此处表示对于某条t1和t2表的记录组合来说，对t3表进行单表查询
            if row satisfies join conditions, send to client
        }
    }
}

这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问的连接执行方式称之为嵌套循环连接，这是最简单，也是最笨拙的一种连接查询算法。我们可以为被驱动表上的被查询字段建立索引来优化查询效率

为什么建立了索引就可以加快查询呢？为被驱动表的那个字段建立索引呢？
下面举例分析：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

查询流程如下：

查询驱动表t1后的结果集中有两条满足条件的记录，嵌套循环连接算法需要对被驱动表查询2次：

当t1.m1 = 2时，去查询一遍t2表，对t2表的查询语句相当于：

SELECT * FROM t2 WHERE t2.m2 = 2 AND t2.n2 < 'd';

当t1.m1 = 3时，再去查询一遍t2表，此时对t2表的查询语句相当于：

SELECT * FROM t2 WHERE t2.m2 = 3 AND t2.n2 < 'd';

可以看到，原来的t1.m1 = t2.m2这个涉及两个表的过滤条件在针对t2表做查询时，关于t1表的条件就已经确定了，所以我们只需要优化对t2表的查询即可。

上述两个对t2表的查询语句中利用到的列是m2和n2列，我们可以：

• 在m2列上建立索引，因为对m2列的条件是等值查找，所以可能使用到ref的访问方法

• 在n2列上建立索引，涉及到的条件是t2.n2 < 'd'，可能用到range的访问方法

以上两种加索引的方式都能在一定程度上加快对t2表的查询速度。

内连接 vs 外连接

对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集

对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。
在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：
左外连接：选取左侧的表为驱动表。
右外连接：选取右侧的表为驱动表。

可是这样仍然存在问题，即使对于外连接来说，有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。这就犯难了，有时候匹配失败要加入结果集，有时候又不要加入结果集，这咋办？

把过滤条件分为两种不就解决了这个问题了么，所以放在不同地方的过滤条件是有不同语义的：

• WHERE子句中的过滤条件
  WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

• ON子句中的过滤条件
  对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

所以，我们都把只涉及单表的过滤条件放到WHERE子句中
把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

内链接

mysql> select * from t1 [inner]join t2 on t1.id = t2.id;

如果像下面这样没有on，就是笛卡尔积

select * from t1 join t2
或者
select * from t1 ,t2

左链接

mysql> select * from t1 left join t2 on t1.id = t2.id;

右链接

mysql> select * from t1 right join t2 on t1.id =t2.id;

左独有

mysql> select * from t1 left join t2 on t1.id = t2.id where t2.id is null;

右独有

mysql> select * from t1 right join t2 on t1.id = t2.id where t1.id is  null;

全连接

mysql> select * from t1 left join t2 on t1.id = t2.id
    -> union 
    -> select * from t1 right join t2 on t1.id = t2.id;

并集去交集

mysql> select * from t1 left join t2 on t1.id = t2.id where t2.id is null
    -> union 
    -> select * from t1 right join t2 on t1.id = t2.id where t1.id is null;

group by

1、执行顺序：from--where--group by--having--select--order by--[LIMIT 开始行, 限制条数],

2、group by的工作原理

 SELECT subject, AVG(score) FROM student_score GROUP BY subject;

先分组，再对每个分组分别执行avg

3、在使用分组的时候必须要意识到，分组的存在仅仅是为了方便我们分别统计各个分组中的信息，所以我们只把分组列和聚集函数放到查询列表处就好！如果非分组列出现在查询列表中会报错，如下：

为什么报错？
因为现在一组有多个number，改选那个呢，冲突所以报错。

使用group by的注意事项

• 如果分组列中含有NULL值，那么NULL也会作为一个独立的分组存在。

• 如果存在多个分组列，也就是嵌套分组，聚集函数将作用在最后的那个分组列上。

• 非分组列不能单独出现在检索列表中(可以被放到聚集函数中)。

• GROUP BY子句后也可以跟随表达式(但不能是聚集函数)。

子查询

子查询可以存在的位置：

• SELECT子句中
• FROM子句中
• WHERE或ON子句中（最常用的一种使用子查询的方式）
• ORDER BY子句中 无意义
• GROUP BY子句中 无意义

组合查询Union

我们看下边这两个查询：

很显然，t1表里有3条记录，t2表里有3条记录，我们使用UNION把它们合并起来看一下：

为什么合并后的结果只剩下了4条记录呢？因为使用UNION来合并多个查询的记录会默认过滤掉重复的记录。如果我们想要保留重复记录，可以使用UNION ALL来连接多个查询：