mysql常用操作记录

1.常用语句

1.1 concat格式化语句

SELECT
    concat(
        '失败笔数: ',
        count(*),
        '笔'
    ),
    concat(
        '失败总金额: ',
        ifnull(
            round(sum(F_repay_amount) / 100, 2),
            0
        ),
        '元'
    )
FROM
    drepay.t_card_repay_order
WHERE
    F_create_time > '2018-05-29'
AND F_state = 2;

1.2 case when then语句

    
SELECT
    b.f_mch_id AS '金柜商户号',
    CASE b.f_status
WHEN 0 THEN
    '已认证'
ELSE
    '待认证'
END AS '是否实名认证',
 b.f_owner_name AS '联系人',
 b.f_owner_mobile AS '联系人手机',
 b.f_create_time AS '注册日期'
FROM
    trade.t_mch b
WHERE
    b.f_mch_id IN (
        SELECT
            f_mch_id
        FROM
            trade.t_card_device_bind
        WHERE
            substr(
                f_device_name,
                LENGTH(f_device_name) - 7
            ) >= '1234'
        AND substr(
            f_device_name,
            LENGTH(f_device_name) - 7
        ) <= '5678'
    )
    

1.3 if语句

eval $($mysql_exec -N -B -e "select round(sum(F_amount)/100, 2),
        round(sum(F_amount-F_pay_amount)/100,2) 
    FROM trade.t_t0_order 
    WHERE date(F_create_time)='${day}' and F_state=31;" 
        | awk '{printf("amount=%.2f;fee=%.2f", $1, $2)}')
    //其中eval用法表示后续的amount，fee变量的定义

1.4 临时表

SELECT
    count(*)
FROM
    (
        SELECT
            min(f_create_time),
            f_user_id
        FROM
            t_card_repay_order
        WHERE
            F_fee_rate = 70
        AND F_state IN (3, 4)
        GROUP BY
            f_user_id
        HAVING
            MIN(f_create_time) > '2018-05-20'
    ) AS e

1.5 添加一列

alter table trade.t_quickpay_channel_pay_order  add column F_sign_sn varchar(32) 
    COMMENT '渠道返回签约号' AFTER F_out_channel_order_id ; 

1.6 添加用户及配置权限

create user 'youqi@192.168.0.%' identified by '12345678'
grant select on trade.* to 'youqi@192.168.0.%'  identified by '12345678'
flush privileges; //刷新权限

1.7 小数位数

select cast(19.16558 as decimal(9,2))精确到几位
select round(123.5); 四舍五入
select floor(123.5);取整数部分
select ceil(123.5);四舍五入

1.8 索引添加修改删除

alter table table_name ADD PRIMARY KEY(F_order_id) ;
alter table table_name ADD UNIQUE (F_channel_order_id);
alter table table_name ADD KEY idx_create_time (F_create_time);

create index index_test2 on table_test(age);

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

1.9 数据库修改操作

alter table trade.t_quickpay_channel_pay_order  add column F_sign_sn varchar(32)  COMMENT '渠道返回签约号' AFTER F_out_channel_order_id ;

alter table trade.t_mch_log change column F_memo F_memo VARCHAR(512)  default '' COMMENT '备注' ;

1.10 统计满足条件百分比

SELECT
    (a.time) AS '交易时间',
    a.total AS '总笔数',
    a.total_succ AS '成功笔数',
    ifnull(
        round(a.total_succ / a.total * 100, 2),
        0
    ) AS '成功率',
    a.gt5000 AS '>5千笔数',
    a.gt5000_succ AS '>5千成功笔数',
    ifnull(
        round(
            a.gt5000_succ / a.gt5000 * 100,
            2
        ),
        0
    ) AS '>5千成功率',
    a.gt10000 AS '>1万笔数',
    a.gt10000_succ AS '>1万成功笔数',
    ifnull(
        round(
            a.gt10000_succ / a.gt10000 * 100,
            2
        ),
        0
    ) AS '>1万成功率'
FROM
    (
        (
            SELECT
                DATE(f_create_Time) AS time,
                sum(IF(f_trade_id, 1, 0)) AS total,
                sum(

                    IF (
                        f_trade_id
                        AND f_status = 2,
                        1,
                        0
                    )
                ) AS total_succ,
                sum(IF(f_amount > 500000, 1, 0)) AS gt5000,
                sum(

                    IF (
                        f_amount > 500000
                        AND f_status = 2,
                        1,
                        0
                    )
                ) AS gt5000_succ,
                sum(IF(f_amount > 1000000, 1, 0)) AS gt10000,
                sum(

                    IF (
                        f_amount > 1000000
                        AND f_status = 2,
                        1,
                        0
                    )
                ) AS gt10000_succ
            FROM
                trade.t_trade
            WHERE
                f_pay_type = 4
            AND f_create_time >= '2018-07-25 00:00:00'
            AND f_create_time <= '2018-07-31 23:59:59'
            GROUP BY
                DATE(f_create_Time)
        ) AS a
    );

2.常见优化

2.1 in子句调整为join. exits子句在业务上也不适合用

select f_mch_name from trade.t_mch where f_mch_id in 
    (select f_mch_Id from trade.t_trade where f_trade_id = '123456')

可以修改为 join

select a.f_mch_name from trade.t_mch a  join trade.t_trade b on
    a.f_mch_id = b.f_mch_id where b.f_trade_id = '123456';
    

2.2 字段属性不一样 可以转换成一样利用索引

f_trade_id 的属性是varchar.如果直接用int 查找利用不了索引

不可以利用索引

desc  select * from t_t0_order  where f_trade_id = 20160612200858; 

可以利用索引

desc select * from t_t0_order where f_trade_id = cast(20160612200858 as char);  
as 的属性可以是下面的一个 
    二进制，同带binary前缀的效果 : BINARY    
    字符型，可带参数 : CHAR()     
    日期 : DATE     
    时间: TIME     
    日期时间型 : DATETIME     
    浮点数 : DECIMAL      
    整数 : SIGNED     
    无符号整数 : UNSIGNED 
    

2.3 运用缓存

查询缓存不开启

$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");  
  

开启查询缓存

$today = date("Y-m-d");  
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'"); 

上面两条SQL语句的差别就是CURDATE(),MySQL的查询缓存对这个函数不起作用。
所以像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，
因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。

3. 收集优化

• 当只要一行数据时使用 LIMIT 1
• 千万不要 ORDER BY RAND() 应用程序来选择随机,先查询有多少条记录在得到一个随机数去查询 limit random 1 ;
• 避免 SELECT *. 尽量可以用上覆盖索引
• 尽可能的使用 NOT NULL
• 永远为每张表设置一个ID 我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。
• 把IP地址存成 UNSIGNED INT 。 INET_ATON INET_NTOA 来转换
• 拆分大的 DELETE 或 INSERT 语句
• 表引擎最好选择 innodb
• text和blob 建议不要与业务表放在一起。可以存在文件中，数据库里面就保存一个链接地址就可以了
• 性别 sex字段，状态status 基本上选择tinyint就可以(最小够用原则)
• 很长的字符串列可以考虑前缀索引，提高索引利用率
• 慢查询日志在生产环境都可以不用打开 备份日志要在备库上面执行

4.注意事项

4.1 sql执行顺序

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

4.2 避免死锁的方法：

1) 并发存取多表，按照相同的顺序访问表
2) 业务尽量采用小事务，及时提交或者回滚
3) 在同一个事务中，尽量做到一次锁定所要的所有资源
4) 对于非常容易产生死锁的业务，可以升级锁粒度

4.3 加锁注意是否有索引

innodb的行锁是加在索引上的加锁的字段建立索引，否则锁表

4.4 innodeb的行记录锁

Inondb 默认的事务隔离级别是RR，在参数innodb_locks_unsafe_for_binlog=0的模式下，行锁类型有3种
1) 单个行记录的锁 record lock (主键和唯一索引都是行记录的锁模式，Rc隔离级别下只有行记录锁模式)
2) 间隙锁 gap-lock
3) 记录锁和间隙锁的组合叫做next-key lock（普通索引默认就是这个模式）

5.特别关注

5.1 mysql常用设置

lower_case_table_names参数来区分表名的大小写 默认为0 表示大小写敏感

5.2 mysql 5.7版本增强

1.新增索引重命名 in place 方式，不需要table copy

 alter table table_name rename index old_index_name to new_index_name ;   

2.支持在线调整varchar列的大小

alter table t1  algorithm=inplace, change column c1 c1 varchar(255); //不支持从小于255 改成大于255长度。

6.附录

1.MySQL查询缓存打开、设置、参数查询、性能变量 http://www.cnblogs.com/gimin/p/5459689.html