mysql 经典sql收集

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>