mysql之重用查询

其他常用查询 https://www.cnblogs.com/luxd/p/9916677.html

1、查询当前时间  年月日时分秒

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-03-19 23:12:52 |
+---------------------+

2、查询当前时间 前三小时 的时间点

mysql> select subdate(now(),interval 3 hour);
+--------------------------------+
| subdate(now(),interval 3 hour) |
+--------------------------------+
| 2018-03-19 20:13:11            |
+--------------------------------+

3、查询当前时间  前三天 的时间点

mysql> select subdate(now(),interval 3 day);
+-------------------------------+
| subdate(now(),interval 3 day) |
+-------------------------------+
| 2018-03-16 23:13:23           |
+-------------------------------+

4、查新当前时间 前三分钟 的时间点

mysql> select subdate(now(),interval 3 minute);
+----------------------------------+
| subdate(now(),interval 3 minute) |
+----------------------------------+
| 2018-03-19 23:10:32              |
+----------------------------------+

5、查询当前时间  时分秒

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 23:14:09       |
+----------------+

6、查询当前时间  年月日时分秒

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2018-03-19     |
+----------------+

 7、获取本月最后一天

mysql> select last_day(curdate());
+---------------------+
| last_day(curdate()) |
+---------------------+
| 2018-03-31          |
+---------------------+

8、获取本月第一天

mysql> select date_add(curdate(), interval - day(curdate()) + 1 day);
+--------------------------------------------------------+
| date_add(curdate(), interval - day(curdate()) + 1 day) |
+--------------------------------------------------------+
| 2018-03-01                                             |
+--------------------------------------------------------+

9、获取下个月第一天

mysql> select date_add(curdate() - day(curdate()) + 1, interval 1 month);
+------------------------------------------------------------+
| date_add(curdate() - day(curdate()) + 1, interval 1 month) |
+------------------------------------------------------------+
| 2018-04-01                                                 |
+------------------------------------------------------------+

10、获取本月天数

mysql> select day(last_day(curdate()));
+--------------------------+
| day(last_day(curdate())) |
+--------------------------+
|                       31 |
+--------------------------+

11-1、获取一个月前的今天【2018年的2月只有28天】

mysql> select date_sub(curdate(), interval 1 month);
+---------------------------------------+
| date_sub(curdate(), interval 1 month) |
+---------------------------------------+
| 2018-02-28                            |
+---------------------------------------+

11-1、获取两个月前的今天

mysql> select date_sub(curdate(), interval 2 month);
+---------------------------------------+
| date_sub(curdate(), interval 2 month) |
+---------------------------------------+
| 2018-01-29                            |
+---------------------------------------+

12、获取当前时间【当前月的第几天】

mysql> select datediff(curdate(), date_sub(curdate(), interval 1 month));
+------------------------------------------------------------+
| datediff(curdate(), date_sub(curdate(), interval 1 month)) |
+------------------------------------------------------------+
|                                                         29 |
+------------------------------------------------------------+

 13、以时间为条件查询数据时(例如查询某个月内或者固定时间段内的数据)

/*
           需要的依赖：
         <!-- https://mvnrepository.com/artifact/joda-time/joda-time -->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.9.9</version>
        </dependency>
         */
        //关于数据库查询数据以时间为条件的查询时
        /*
            思路：将 将时间格式的字符串转换为DateTime类型的时间，
            然后可以实现在该时间基础上  plus增加day  month  week  year等等;
            然后将增加固定时间后得到的DateTime类型的时间转换为字符串
            在sql语句中进行拼接
         */
        StringBuffer timeStr = new StringBuffer("2017-01");
        timeStr.append("-01 00:00:01");
        org.joda.time.format.DateTimeFormatter format = org.joda.time.format.DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
        //时间解析
        org.joda.time.DateTime startDate = DateTime.parse(timeStr.toString(), format);
        DateTime endDate = startDate.plusMonths(1);//在start表示的时间点基础上增加一个月

        String start = startDate.toString("yyyy-MM-dd HH:mm:ss");
        String end = endDate.toString("yyyy-MM-dd HH:mm:ss");
        System.out.println(start);
        System.out.println(end);
        // 单引号一定不能省略
        StringBuffer sql = new StringBuffer("select * from user where create_time >'");
        sql.append( start).append("'").append(" and create_time < '").append(end).append("'");
        String sqlQuery = sql.toString();
        //总之就是如下" create_time < '2017-01-01 00；00:00'" (这个是2017-01-01 00；00:00String类型的参数)