MySQL的笔记

 一、关于索引

MySQL中的索引官方定义是帮助Mysql高效的获取数据的数据结构。树结构类型，类似于图书的数目索引，可以提高数据检索的效率，降低数据库的IO成本
索引的分类：聚集索引与非聚集索引、唯一索引与非唯一索引
聚集索引的意思可以理解为顺序排列，比如一个主键自增的表即为聚集索引。一个表只能包含一个聚集索引。
唯一索引：如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
CREATE INDEX mycolumn_index ON mytable (myclumn)
建立索引
数据库查询优化方案：
一、对表字段的优化：
1、最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
2、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
3、尽可能的使用 varchar/nvarchar代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
二、对SQL语句的优化
对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
应尽量避免在 where子句中对字段进行表达式操作、函数操作，这将导致引擎放弃使用索引而进行全表扫描。
join 语法，尽量将小的表放在前面，在需要on的字段上，数据类型保持一致，并设置对应的索引，否则MySQL无法使用索引来join查询

in和 not in 也要慎用，否则会导致全表扫描
对于连续的数值，能用 between就不要用 in 了
很多时候可以使用 exist 和not exist代替in和not in。
LIKE操作符 例如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。
对于多张大数据量（这里几百条就算大了）的表JOIN，要 先分页 再JOIN，否则逻辑读会很高，性能很差。
select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
不要以字符格式声明数字，要以数字格式声明字符值。（日期同样）否则会使索引无效，产生全表扫描。
尽量避免使用游标，因为游标的效率较差（游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果）
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
三、  对数据库进行修改的优化处理
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个
(临时表 #本地临时表 ##全局临时表) 避免频繁创建和删除临时表，以减少系统表资源的消耗。在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
尽量避免大事务操作，提高系统并发能力。
Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志 ORM对象关系映射（性能差）

二、组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：
1. CREATE TABLE mytable( ID INT NOT NULL, username
VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：
1. ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。
如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

删除索引
alter table pay_info drop index orderId_index;

 

三、字符串拼接

select concat("update product_category set `subsidyLimit`='", `subsidyLimit` ,"' where id='", id, "';") from product_category WHERE id LIKE '%B%' AND `level` = 2

 

select concat("update u_permission set sequence='", sequence ,"',icon='",icon,"' where permissionId='", permissionId, "';") from u_permission WHERE sequence BETWEEN '41' AND '46'

 

select concat("INSERT INTO u_role_permission ( roleId,permissionId)VALUES('", roleId, "','", permissionId, "');") from u_role_permission WHERE permissionId='51e4a46bac1f4b3db853a27344f181ca'

 

select concat("update main_log set outResult='", a.comContractUrl,"' where functionName = '企业上传合作协议' AND  functionKey='", a.orderId, "';")

from (SELECT b.functionName,b.comContractUrl,b.orderId FROM

(SELECT t.comContractUrl,t.orderId,m.functionName FROM transaction_info t LEFT JOIN main_log m ON t.orderId = m.functionKey )b)a

 

concat(IFnULL(b.handOverFileUrl,""),",",IFNULL(b.bankUrl,""),",",IFNULL(b.receiptUrl,""))

 

 

四、添加字段

ALTER TABLE people ADD COLUMN name VARCHAR(100) DEFAULT NULL COMMENT '姓名'

   修改表 people  增加字段 name    长度100  默认为null   备注：姓名

 

五、有条件的修改字段

<update id="updateSecondRentalInThird" parameterType="com.rental.entity.SecondRental">

        UPDATE secondrental

        <set>

            <if test="fid              !=null">fid = #{ fid },</if>

            <if test="sname       !=null">sname = #{ sname },</if>

        </set>

        WHERE

        sid=#{sid}

    </update>

 

六、有条件的插入数据

insert into  tablename( 属性1，属性2) values ('值1'，‘值2’);

insert into  tablename(属性1，属性2) select '值1','值2' from dual where exists (select  值1 from tablename where 子句); 

例子：

<insert id="insertdemo" parameterType="com.略.entity.table_name">

  insert into table_name (id, name

      )

      select  #{id}, #{name}

      from dual where not exists

      (select 1 from table_name where id=#{id} and name=#{name})

  </insert>

 

<insert id="insertOrgInfo" parameterType="com.xql.ctv.entity.ctv.entity.organization_type">

   insert into organization_type( id, typeName, orglv, parentType, instruction, updateTime)

   select #{id,jdbcType=CHAR}, #{typeName,jdbcType=VARCHAR}, #{orglv,jdbcType=VARCHAR},

      #{parentType,jdbcType=BIT}, #{instruction,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP}

    FROM dual

    WHERE not EXISTS (SELECT typeName FROM organization_type WHERE typeName = #{typeName})

</insert>

 

 

七、拆出时间中的月份

SELECT count(1)as mount, DATE_FORMAT(payTime,'%Y')AS years,DATE_FORMAT(payTime,'%m')AS months  FROM pay_info

GROUP BY years,months

 

八、sum()带条件统计

SUM(if(a.comType=1,a.registeredCapital,0))AS foreignRegistered,  -- 求所有 类型为1 所有registeredCapital 的 总和

IFNULL(SUM(1111>status AND status>=50),0)                           -- 求 status<100的status 的个数

IFNULL(SUM(b.employedNum>=50),0)AS totalPerson2,            -- 求 数量在50-100之间的 个数

 

九、字符串去除某字符包含了某字符串

SELECT COUNT(1)amount,replace(replace(industry,"[\"",""),"\"]","")AS industryNew FROM com_basic_info

WHERE replace(replace(industry,"[\"",""),"\"]","") LIKE '%互联网%' OR  replace(replace(industry,"[\"",""),"\"]","") LIKE '%专用%' GROUP BY industry

 

十、分组后再分组

SELECT SUM( c.total) total,

             CASE WHEN c.type IN('1','2','3') THEN '1'

             WHEN c.type IN ('4') THEN '4'

             WHEN c.type IN ('5') THEN '5'

             WHEN c.type IN ('6') THEN '6'

             WHEN c.type IN ('7') THEN '7'

             ELSE c.type END as types, IFNULL(SUM(c.mount),0) AS mount

             FROM

            (

             SELECT   (SELECT COUNT(1) FROM com_basic_info)total,COUNT(1) AS mount,

             SUBSTRING(a.comType,1,1) AS type

             FROM com_basic_info a LEFT JOIN com_year_report b ON a.creditCode = b.creditCode  

             WHERE b.reportYear = (DATE_FORMAT(NOW(),'%Y')-1) GROUP BY type

            )c

             GROUP BY types

 

十一、时间取当今年 的往前3个月的 数据

SELECT COUNT(1) FROM com_basic_info WHERE DATE_FORMAT(foundingTime,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')

             OR DATE_FORMAT(foundingTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

             OR DATE_FORMAT(foundingTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m')

 

十二、截取字符串

SUBSTRING(b.payedDate,5,6)

 

 

十三、关联表更新

 UPDATE tdb_goods AS g JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name JOIN tdb_goods_cates AS c ON g.goods_cate = c.cate_name SET g.brand_name = b.brand_id, g.goods_cate = c.cate_id;

 

十四、left join on 条件

SELECT  count(1),a.level,a.minScore,a.maxScore,a.topLimit FROM(

(SELECT level,minScore,maxScore,topLimit

FROM bracket_info b LEFT JOIN credit_limit_by_bracket_item c ON b.id = c.bracketId LEFT JOIN credit_limit_by_bracket_record cr

ON c.recordId = cr.recordId

WHERE cr.recordId = '58a01f9d96174c25b70e7fa546cfa949' ORDER BY LEVEL asc)a

LEFT JOIN (SELECT score  FROM voucher_apply_info WHERE batchId = 'a1854d50ee484c04b744952ca90605e6' AND STATUS = 4  ORDER BY score)b ON (b.score BETWEEN a.minScore AND maxScore)

)GROUP BY level

 

十五、分组中时间最新的数据

SELECT a.regno,a.score FROM voucher_apply_info a

LEFT JOIN (SELECT MAX(updateTime)AS maxTime FROM voucher_apply_info GROUP BY regno )b ON a.regNo = b.regno

WHERE a.updateTime = b.maxTime  ORDER BY a.score DESC LIMIT 50

 

 

十六、表几种连接方式

1） 内连接   

  select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id       

  结果是     

  1   张3                   1     23     1   

  2   李四                  2     34     2   

 

  2）左连接   

  select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id       

  结果是     

  1   张3                   1     23     1   

  2   李四                  2     34     2   

  3   王武                  null   

 

3） 右连接   

  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id       

  结果是     

  1   张3                   1     23     1   

  2   李四                  2     34     2   

  null                       3     34     4   

 

4） 完全连接   

  select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id   

  结果是     

  1   张3                  1     23     1   

  2   李四                 2     34     2   

  null               　　  3     34     4   

  3   王武                 null

 

十七、修改sql中字段注释

alter table test1 modify column field_name int comment '修改后的字段注释';

alter table goods_info modify column status int comment '修改后的字段注释';

 

十八、一个企业多条数据多个标签，用|分隔 组装

-- 效果：国家高新技术企业 | 科技小巨人培育企业

select IFNULL(GROUP_CONCAT(b.tagName SEPARATOR ' | '),'')as tagNameNew,a.comId,a.tagId FROM com_tag_relevance a

LEFT JOIN com_tag b ON a.tagId=b.tagId

group by a.comId;

 

十九、随机排序

SELECT * FROM voucher_apply_info order by rand();

 

固定一条数据排在前面，其它随机排序

1、SELECT * FROM com_info order by field(regNo,340191000113172) DESC;

2、SELECT * FROM com_info order by IF(regNo='340191000113172',1,rand()) DESC;

 

注：

0<rand()<1

 

 

二十、 分组后组内排名

详细：https://www.cnblogs.com/niniya/p/9046449.html

一、分组普通排名：顺序排名
SELECT city,score,rank
FROM
(
SELECT cs.*,IF(@p=city,@r:=@r+1,@r:=1) AS rank,
　　　　@p:=city
FROM cs,(SELECT @p:=NULL,@r:=0)r
ORDER BY city,score
)s;

            

二、 分组后并列排名：组内相同数值排名相同
SELECT city,score,rank
FROM
(
SELECT *,
IF(@p=city,
    CASE
       WHEN @s=score THEN @r
       WHEN @s:=score THEN @r:=@r+1
    END,
   @r:=1 ) AS rank,
@p:=city,
@s:=score
FROM cs,(SELECT @p:=NULL,@s:=NULL,@r:=0)r
ORDER BY city,score
)s;

  　　

 

 

　　例子、SELCT typeNew,productId,productQuanSum,productType,rank FROM

        (SELECT a.*,IF(@p=a.typeNew,@r:=@r+1,@r:=1) AS rank,@p:=a.typeNew

        FROM (SELECT substring(productType,1,5)AS typeNew,productId,SUM(quantity)AS productQuanSum,productType FROM transaction_info GROUP BY productId)a,(SELECT @p:=NULL,@r:=0)r

        ORDER BY typeNew,a.productQuanSum)s

    WHERE productType is NOT null AND rank <4

 

 

二十一、拼接 操作时间+随机6位数

concat(DATE_FORMAT(operateTime,'%Y%m%d'),CEILING(RAND()*90000+100000))

结果：20190508123456