MySQL MyBatis

1. MySQL从表中随机查一条数据

SELECT * FROM address ORDER BY RAND() LIMIT 1

2. MySQL查询表是否存在

select count(*) from information_schema.TABLES where table_name = #{tableName}

3. 插入数据插入随机的uuid

<insert id="insertComment" parameterType="com.deyi.govaffair.pojo.po.WelfareComment">
    <selectKey keyProperty="commentId" order="BEFORE" resultType="String">
      select replace(uuid(),'-','') from dual
    </selectKey>
    INSERT INTO welfare_comment
    (comment_id, project_id, user_id, comment_content, create_time, update_time)
    VALUES(#{commentId}, #{projectId}, #{userId}, #{commentContent}, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
  </insert>

4. 时间格式化

SELECT DATE_FORMAT(donation_deadline,'%Y-%m-%d %H:%i:%s') AS donationDeadline FROM sport_config

%S, %s 两位数字形式的秒( 00,01, . . ., 59)
%i 两位数字形式的分( 00,01, . . ., 59)
%H 两位数字形式的小时，24 小时(00,01, . . ., 23)
%h, %I 两位数字形式的小时，12 小时(01,02, . . ., 12)
%k 数字形式的小时，24 小时(0,1, . . ., 23)
%l 数字形式的小时，12 小时(1, 2, . . ., 12)
%T 24 小时的时间形式(h h : m m : s s)
%r 12 小时的时间形式(hh:mm:ss AM 或hh:mm:ss PM)
%p AM 或P M
%W 一周中每一天的名称( S u n d a y, Monday, . . ., Saturday)
%a 一周中每一天名称的缩写( Sun, Mon, . . ., Sat)
%d 两位数字表示月中的天数( 00, 01, . . ., 31)
%e 数字形式表示月中的天数( 1, 2， . . ., 31)
%D 英文后缀表示月中的天数( 1st, 2nd, 3rd, . . .)
%w 以数字形式表示周中的天数( 0 = S u n d a y, 1=Monday, . . ., 6=Saturday)
%j 以三位数字表示年中的天数( 001, 002, . . ., 366)
% U 周(0, 1, 52)，其中Sunday 为周中的第一天
%u 周(0, 1, 52)，其中Monday 为周中的第一天
%M 月名(J a n u a r y, February, . . ., December)
%b 缩写的月名( J a n u a r y, February, . . ., December)
%m 两位数字表示的月份( 01, 02, . . ., 12)
%c 数字表示的月份( 1, 2, . . ., 12)
%Y 四位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%”
 select date_format(日期字段,’%Y-%m-%d’) as ‘日期’ from test

5. mybatis插入当前时间

insert into test (userId,stepNum,currentTime) values (#{userId},#{stepNum},CURRENT_TIME)

6. mysql加一个排序字段

SELECT b.user_id,
       b.photo_url,
       b.user_name,
       a.step_num,
       (@rowno := @rowno + 1) AS rowno
FROM sport_steps_day_record a,
     sport_user b,
     (SELECT (@rowno := 0)) c

7. mybatis List入参

<select id="getFriendRank" resultType="com.deyi.govaffair.pojo.dto.UserIdAndRowNoDto">
        select
        e.user_id as userId,
        (@rowno:=@rowno+1) as rowno,
        d.step_num as stepNum
        from sport_steps_day_record d,
             sport_user e,
             (select (@rowno:=0)) f
        where d.user_id = e.user_id
        AND e.user_id IN
    <foreach collection="ids" index="index" item="id" open="(" separator="," close=")">
      #{id}
    </foreach>
        and d.record_date = #{today}
        order by d.step_num desc
  </select>

8. linux内远程连接外部MySQL

mysql -h [ip地址] -P[端口号] -u[用户名] -p[密码]

9. 开放MySQL的一个用户可被所有地址访问

use mysql ;
select user,host from user;
update user set host = '%' where user='[用户名]';

10. 让root用户使用密码123456从任何主机连接到本机mysql服务器的

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH   PRIVILEGES;

11. 如果你想允许用户muser从ip为192.168.1.116的主机连接到本机mysql服务器，并使用123456作为密码

GRANT ALL PRIVILEGES ON *.* TO 'muser'@'192.168.1.116' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH   PRIVILEGES;

12. 如果你想允许用户muser从ip为192.168.1.116的主机连接到mysql服务器的test数据库，并使用123456作为密码

GRANT ALL PRIVILEGES ON dk.* TO 'muser'@'192.168.1.116' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH   PRIVILEGES;

13. SQL两日期相差的天数

SELECT TIMESTAMPDIFF(DAY,DATE_FORMAT(create_time,'%Y-%m-%d'),CURDATE()) FROM sport_contro_audit

14. 创建数据库指定字符集

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

15. 删除数据库中所有表

select concat('drop table ',table_name,';') from information_schema.TABLES where table_schema='test';

16. 查询2017-03-09有多少注册人数

select count(*) from ucenter_member uc where DATE(uc.gmt_create)='2017-03-09';

17.查询日期间隔的时分秒并左填充0

SELECT
            CONCAT(
                LPAD( TIMESTAMPDIFF( HOUR, create_time, close_time )- TIMESTAMPDIFF( DAY, create_time, close_time )* 24, 2, '0' ),
                ':',
                LPAD( TIMESTAMPDIFF( MINUTE, create_time, close_time )- TIMESTAMPDIFF( HOUR, create_time, close_time )* 60, 2, '0' ),
                ':',
                LPAD( TIMESTAMPDIFF( SECOND, create_time, close_time )- TIMESTAMPDIFF( MINUTE, create_time, close_time )* 60, 2, '0' )) AS liveTime,
            viewers,
            message_sum,
            add_follow,
            floor(live_streaming_income) as live_streaming_income
        FROM
            jy_live_streaming_room
        WHERE
            id = #{id};

mysql的时间查询

mysql查询今天、昨天、7天、近30天、本月、上一月 数据
今天
select * from 表名 where to_days(时间字段名) = to_days(now());
昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上一月 
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
 
#查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
#查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
#查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
#查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));