ASPIRE

MySQL中的常用函数/语句记录

=Start=

缘由：

学习需要、提高需要

正文：

MySQL中如何获取上个月的第一天和最后一天
-- http://stackoverflow.com/questions/1138190/mysql-query-to-calculate-the-previous-month

> select DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01');
> select DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d');

> select DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00'), DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59');
> select DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01'), DATE_FORMAT(NOW() ,'%Y-%m-01');
MySQL中如何获取当前时间、日期、月份、年份、星期几
-- http://stackoverflow.com/questions/2090221/mysql-query-to-get-all-rows-from-previous-month
-- http://stackoverflow.com/questions/9166334/timespan-check-for-weekday-and-time-of-day-in-mysql
-- http://www.manongjc.com/article/109.html

> select now();
> select CURRENT_DATE;
> select MONTH(CURRENT_DATE);
> select YEAR(CURRENT_DATE);
> select DATE_FORMAT(NOW(), '%w'); -- 今天是星期几
MySQL中如何获取一天之前、一个月之前的日期
-- http://stackoverflow.com/questions/7146828/mysql-selecting-yesterdays-date
-- https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

> select subdate(current_date, 1); -- 获取昨天的日期
> select CURDATE() - INTERVAL 1 DAY; -- 获取昨天的日期

> select date_sub(now(), interval 1 month); -- 获取一个月之前的日期
> select date_sub(current_date, interval 1 month); -- 获取一个月之前的日期
待添加

……

=END=

admin

, ,
,

《 “MySQL中的常用函数/语句记录” 》 有 4 条评论

  1. MySQL查询中的OR
    `
    select foo from bar where baz in (1,2,3);

    select foo from bar where baz in (select baz from bar2);

    select foo from bar where baz = ‘1’ or baz = ‘2’ or baz = ‘3’;

    select foo from bar where baz between 1 and 3;
    `
    http://stackoverflow.com/questions/17870/select-where-or
    http://stackoverflow.com/questions/2804881/mysql-question-about-select-where-and-or
    http://stackoverflow.com/questions/6561311/mysql-where-and-or-statement
    https://www.techonthenet.com/mysql/and_or.php
    https://www.techonthenet.com/mysql/or.php

    回复

  4. MySQL中是否有类似于Hive SQL中的split函数用于字符串切分？
    Can you split/explode a field in a MySQL query?
    https://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query
    `
    In short, there is a reason why there is no native SPLIT() function in MySQL.
    ==

    MySQL’s only string-splitting function is SUBSTRING_INDEX(str, delim, count). You can use this, to, for example:

    1. Return the item before the first separator in a string: #取第一个分隔符之前的子串
    mysql> SELECT SUBSTRING_INDEX(‘foo#bar#baz#qux’, ‘#’, 1);
    foo

    2. Return the item after the last separator in a string: #取最后一个分隔符之前的子串
    mysql> SELECT SUBSTRING_INDEX(‘foo#bar#baz#qux’, ‘#’, -1);
    qux

    3. Return everything before the third separator in a string: #取第3个分隔符之前的子串
    mysql> SELECT SUBSTRING_INDEX(‘foo#bar#baz#qux’, ‘#’, 3);
    foo#bar#baz

    4. Return the second item in a string, by chaining two calls: #取第2个子串，通过链接2个调用来实现
    mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘foo#bar#baz#qux’, ‘#’, 2), ‘#’, -1);
    bar
    ==

    综上，在MySQL里面现在是没有现成的 split 函数的，但可以通过 SUBSTRING_INDEX 函数实现类似的效果。
    `

    回复

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注