mysql常用操作记录

本篇文章给大家分享《mysql常用操作记录》，覆盖了数据库的常见基础知识，其实一个语言的全部知识点一篇文章是不可能说完的，但希望通过这些问题，让读者对自己的掌握程度有一定的认识(B 数)，从而弥补自己的不足，更好的掌握它。

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
        ) 

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 

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函数都不会开启查询缓存

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 
(1)     FROM 
(3)      JOIN 
(2)     ON 
(4)     WHERE 
(5)     GROUP BY 
(6)     HAVING 
(9)     ORDER BY 
(10)    LIMIT 

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

以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。