MySQL简单使用

NORMAL	普通索引
UNIQUE	唯一索引
FULLTEXT	全文索引
SPATIAL	空间索引

显示某张表有哪些索引

SHOW INDEX FROM 表名

最左前缀匹配原则

### 当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，
### 比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；
### 但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
### 比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性

使用索引的原则

/**
1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
*/

查询优化的7个步骤

## 0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE

## 1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
                                    select count(*),sync_status from stage_poi  group by sync_status

## 2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）,

3.order by limit 形式的sql语句让排序的表优先查

4.了解业务方使用场景

## 5.加索引时参照建索引的几大原则

必须要掌握的SQL语句

查询总的条数

<!-- long countDemandNumber(); -->
    <select id="countDemandNumber" resultType="long">
        select count(*) from demand_order where status = 4
    </select>

将某个数字类型的字段，加起来，求和

 <!-- public long CountDemandProcurement(); -->
    <select id="countDemandProcurement" resultType="long">
        select sum(coalNumber) from demand_order where status = 4
    </select>

<!-- 
    需要特别注意的是，假如没有一条数据符合条件，
    那么返回的将会是一个NULL，
    与要求返回的Long类型不服
    报错

    解决方案：一般实在Service层来处理
    使用该SQL语句之前
    前去执行 select count(*) from demand_order where status = 4
    假如返回的是0，代表没有一条数据符合条件，就不执行该SQL语句
    假如返回的是非0，代表至少有一条数据符合条件，就可以执行该SQL语句
-->

多表连接查询，通过公共列（尽管在不同表中名字可能不同，但是类型和代表的意思是相同的）

<!-- public long regionalCoalDistribution(String producingArea,String coalType); -->
    <select id="regionalCoalDistribution" resultType="long">
        select sum(q.supplyQuantity) from quoted_price_info as q inner join demand_order as d on q.dId=d.id
        where q.producingArea = #{param1} and d.coalType=#{param2}
    </select>

<!-- 
    这里使用的连接方式是： inner join
    建议使用： inner join
-->

计算查询出来的某一列的平均值

<!-- 平均值 -->
    <select id="***" resultType="***">
        select avg(id) from quoted_price_info
    </select>

<!-- 
    注意计算平均值的列的字段类型，是数字类型 
-->

查询某列的最大/小值


<!-- 查询某列的最大值 -->
    <select id="***" resultType="***">
        select max(id) from quoted
    </select>

<!-- 查询某列的最小值 -->
    <select id="***" resultType="***">
        select max(id) from quoted
    </select>

<!-- 
    注意计算平均值的列的字段类型，是数字类型 
-->

在SQL语句中也可以进行分页

<!-- 分页 -->
    <select id="***" resultType="***">
        select * from quoted limit 0,10
    </select>

<!-- 
     limit说明：
    有两个参数，
    参数1：代表第几页，从0开始
    参数2：代表
-->

group by进行分组，可以搭配having使用

<!-- 雷达图public List<RadarMapVO> attention(); -->
    <select id="attention" resultType="team1.deal.model.vo.RadarMapVO">
        select coalType,producingArea,sum(supplyQuantity) as count 
        from (
        select d.coalType,q.producingArea,q.supplyQuantity
        from quoted_price_info as q inner join demand_order as d on q.did = d.id
        where q.producingArea = '沿海城市' or q.producingArea = '内陆城市'
        group by q.producingArea,q.id
        ) as table1 
        group by producingArea,coalType
    </select>
<!-- 搭配having使用 -->
    <select id="***" resultType="***">
        select *,count(*) from quoted group by producingArea having port = '广东'
    </select>

group by
    //:对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值
    
    注意：
    如果分组后，使用select 字段1，字段2，...查询出来的只要有一个字段的值是不同的，MySQL就会迷惑，因为MySQL需要向用户返回展示分组中的一条数据，所以现在MySQL就不知道返回那一条数据，
    如果分组后，使用select 字段1，字段2，...查询出来的所有字段的值都是相同的，MySQL就会立马返回一条数据，因为所有的字段都是一样的

使用ORDER BY根据某个字段进行升序或者降序

<!-- 根据id降序排列 -->
    <select id="***" resultType="***">
        select * from quoted_price_info order by id DESC
    </select>

<!-- 
    特别说明：
    1.降序排列：order by 字段名 DESC
    2.升序排列：order by 字段名 ASC，MySQL默认是升序排列，所以一般情况下是不使用的

    字段类型一般是数字类型
-->

当涉及到时间时，比如说只查询某个时间段的数据时

<!-- public List<String> kindsOfCoallist(LocalDateTime beginYear,LocalDateTime endYear); -->
    <select id="kindsOfCoallist" resultType="String">
        select coalType from demand_order where deliveryTime <![CDATA[>=]]> #{beginYear}
        and deliveryTime <![CDATA[<]]> #{endYear} group by coalType
    </select>

<!-- 
    时间在MySQL数据库中是以timestamp时间戳的方式存储，只不过在navicat中是以yyyy-MM-dd HH:mm:ss格式展示
    
    时间的比较不能像普通的数字比较那样直接使用>  <   <=  >=...
    必须使用<![CDATA[>]]>   <![CDATA[<]]>   <![CDATA[<=]]>  <![CDATA[>=]]>....
-->

条件操作符

<>, !=
    不等于，检测两个值是否相等，如果不相等返回true
    
<=>
    : 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

NULL 值的处理

/**NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符*/

正则表达式

说明

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式
[...]	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'
[^...]	负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'
P1\|P2\|P3	匹配 p1 或 p2 或 p3。例如，'z\|food'，能够匹配"z" 或 "food"。'(z\|f)ood'则匹配 "zood" 或 "food"
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}
+	匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

示例

<!-- 查找name字段中以'st'为开头的所有数据 -->
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st'

<!-- 查找name字段中以'ok'为结尾的所有数据 -->
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$'

<!-- 查找name字段中包含'mar'字符串的所有数据 -->
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar'

<!-- 查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据 -->
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$'

MySQL的执行顺序

<!--  -->

    from →join →on →where →group by→having→select→order by→limit

<!--  -->