mysql 经典sql收集

最新推荐文章于 2024-04-28 10:15:00 发布

陈安志

最新推荐文章于 2024-04-28 10:15:00 发布

阅读量333

点赞数

分类专栏： sql

sql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1、按条件进行sql数据存储

普通的 INSERT INTO 插入：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> card(cardno, cardnum) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1111'</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'100'</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> card(cardno, cardnum) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'2222'</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'200'</span>);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>

加入条件的插入sql

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> card(cardno, cardnum) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'111'</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'100'</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FROM</span> DUAL <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NOT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">EXISTS</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> cardno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FROM</span> card <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> cardno = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'111'</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> card(cardno, cardnum) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'222'</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'200'</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FROM</span> DUAL <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NOT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">EXISTS</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> cardno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FROM</span> card <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> cardno = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'222'</span>);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>

2、条件控制

IF 语句
CASE 语句
IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF

<code class="hljs vhdl has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;">DECLARE
  x VARCHAR2(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>);
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BEGIN</span>
  x:='&x';
  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">IF</span> LOWER(x)=<span class="hljs-attribute" style="box-sizing: border-box;">'y</span>' <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">THEN</span>
    DBMS_OUTPUT.PUT_LINE(<span class="hljs-attribute" style="box-sizing: border-box;">'Yes</span>!');
  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ELSIF</span> LOWER(x)=<span class="hljs-attribute" style="box-sizing: border-box;">'n</span>' <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">THEN</span>
    DBMS_OUTPUT.PUT_LINE(<span class="hljs-attribute" style="box-sizing: border-box;">'No</span>!');
  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ELSE</span>
    DBMS_OUTPUT.PUT_LINE(<span class="hljs-attribute" style="box-sizing: border-box;">'Error</span>');
  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">IF</span>;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span>;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li></ul>

CASE 语句用于根据单个变量或表达式与多个值进行比较
执行 CASE 语句前，先计算选择器的值

<code class="hljs vhdl has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BEGIN</span>
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CASE</span> ‘&grade’
      <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHEN</span> ’A’ <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">THEN</span> DBMS_OUTPUT.PUT_LINE (’优异’);
      <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHEN</span> ’B’ <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">THEN</span> DBMS_OUTPUT.PUT_LINE (优秀’);
      <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHEN</span> ’C’ <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">THEN</span> DBMS_OUTPUT.PUT_LINE (’良好’);
      <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHEN</span> ’D’ <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">THEN</span> DBMS_OUTPUT.PUT_LINE (’一般’);
      <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHEN</span> ’F’ <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">THEN</span> DBMS_OUTPUT.PUT_LINE (’较差’);
      <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ELSE</span> DBMS_OUTPUT.PUT_LINE (’没有此成绩’);
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CASE</span>;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span>;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li></ul>

3、循环控制用于重复执行一系列语句
循环控制语句包括：
LOOP、EXIT 和 EXIT WHEN
循环控制的三种类型：
LOOP - 无条件循环
WHILE - 根据条件循环
FOR - 循环固定的次数

<code class="hljs oxygene has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">LOOP</span> 
  sequence_of_statements
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">LOOP</span>;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHILE</span> condition <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">LOOP</span> 
  sequence_of_statements
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">LOOP</span>;
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FOR</span> counter <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">IN</span> [<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">REVERSE</span>] value1..value2
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">LOOP</span> 
  sequence_of_statements
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">END</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">LOOP</span>;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li></ul>

or 语句的使用满足某条条件就能显示

<code class="hljs avrasm has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"> select  t2<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.content</span>,t2<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.notes</span>_id,t1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.title</span>,t1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.create</span>_date,t1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.id</span> from travel_notes t1,travel_notes_agenda t2,travel_notes_agenda_items t3 where t1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.id</span> = t2<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.notes</span>_id  
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> t2<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.id</span> = t3<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.notes</span>_agenda_id 
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span>(
            t2<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.content</span> LIKE CONCAT(CONCAT(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span>, <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{content}), '%')</span>
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">or</span> 
            t1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.title</span> LIKE CONCAT(CONCAT(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span>, <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{content}), '%')</span>
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">or</span> 
            t1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.author</span> LIKE CONCAT(CONCAT(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span>, <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{content}), '%')</span>
            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">or</span>
            t3<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.name</span> LIKE CONCAT(CONCAT(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%'</span>, <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{content}), '%'))</span>
        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span>
    <![CDATA[   t1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.create</span>_date < <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{createDate}]]></span>
        order by t1<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">.create</span>_date desc
        limit <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>,<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">#{page}</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li></ul>

update

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">UPDATE</span> travel_specialty t <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SET</span> t.read_num  = (t.read_num +<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> id = <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">19</span></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

Date 计算
获取指定月份的前一个月的时间

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> DATE_SUB(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1998-01-30 15:20:30'</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">Interval</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">month</span>);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

获取指定月份后一个月的时间

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> DATE_ADD(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1998-01-30 15:20:30'</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">Interval</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">month</span>);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

获取当前月份的月初时间

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> DATE_ADD(curdate(),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> -<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span>(curdate())+<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span>)</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

获取指定月份上个月的月初

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> DATE_ADD((<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> DATE_SUB(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1998-01-20 15:20:30'</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">Interval</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">month</span>)),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> -<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span>((<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> DATE_SUB(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1998-01-20 15:20:30'</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">Interval</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">month</span>)))+<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span>)</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

获取指定月份下个月的月初

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> DATE_ADD((<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> DATE_ADD(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1998-01-30 15:20:30'</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">Interval</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">month</span>)),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> -<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span>((<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> DATE_ADD(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1998-01-30 15:20:30'</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">Interval</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">month</span>)))+<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span>) </span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

获取指定月份下个月的月末
select last_day((select DATE_ADD(‘1998-01-30 15:20:30’, Interval 1 month)))

获取上个月的第一天

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> date_sub(date_sub(date_format(now(),<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%y-%m-%d'</span>),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> extract(    
   <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> now())-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span>),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">month</span>) </span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>

获取上个月的月末

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> date_sub(date_sub(date_format(now(),<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%y-%m-%d'</span>),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> extract(    
   <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> now()) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span>),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">month</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">date</span>  </span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>

获取上个月月初的时间格式为long （带秒数）

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> UNIX_TIMESTAMP(date_sub(date_sub(date_format(now(),<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%y-%m-%d'</span>),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> extract(    
   <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> now())-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span>),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">month</span>))* <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1000</span> </span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>

获取上个月月末的时间格式为long

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> UNIX_TIMESTAMP(date_sub(date_sub(date_format(now(),<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%y-%m-%d'</span>),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> extract(    
   <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> now()) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span>),<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">interval</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">month</span>))*<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1000</span></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>

获取当前日期在本周的周一

<code class="hljs scss has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-tag" style="color: rgb(0, 102, 102); box-sizing: border-box;">select</span> <span class="hljs-function" style="box-sizing: border-box;">subdate(<span class="hljs-function" style="box-sizing: border-box;">curdate()</span>,<span class="hljs-function" style="box-sizing: border-box;">date_format(<span class="hljs-function" style="box-sizing: border-box;">curdate()</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%w'</span>)</span>-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>)</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

获取当前日期在本周的周日

<code class="hljs scss has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-tag" style="color: rgb(0, 102, 102); box-sizing: border-box;">select</span> <span class="hljs-function" style="box-sizing: border-box;">subdate(<span class="hljs-function" style="box-sizing: border-box;">curdate()</span>,<span class="hljs-function" style="box-sizing: border-box;">date_format(<span class="hljs-function" style="box-sizing: border-box;">curdate()</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%w'</span>)</span>-<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7</span>)</span></code>