MySQL condition query if case usage detailed

Source: Internet
Author: User
Tags table name

Where Condition Query

The code is as follows Copy Code


SELECT * FROM news where DATE (adddate) between
' 2011-04-20 '-INTERVAL 5 day and ' 2011-04-20 ' + INTERVAL 5 day

SELECT * FROM news where DATE (adddate) in (' 2011-04-20 ', ' 2011-04-15 ', ' 2011-04-25 ')

Use of an IF condition statement

Use of the MySQL conditional judgment statement if: First, determine if there exists a specified value exists in the database, the modification does not exist to add.
I'll use it here. The IF statement of the MySQL stored procedure

The code is as follows Copy Code

DROP PROCEDURE IF EXISTS procedure name;
CREATE PROCEDURE Procedure Name (
In Truename VARCHAR (100),
In Phone VARCHAR (100),
In Qqmsn VARCHAR (100),
In Mail VARCHAR (100),
In Mac VARCHAR (100),
In Heartip VARCHAR (100)
)
Not deterministic
SQL Security Definer
COMMENT '
BEGIN
SELECT count (id) into @maccount the From ' table name ' where ' table name '. Mac=mac; --Is this record present
if (@maccount >0) THEN
Update ' table name ' Set ' Truename ' =truename, ' phone ' =phone, ' qqmsn ' =qqmsn, ' mail ' =mail, ' mac ' =mac,

' Heart ' = ' heart ' +1, ' Heartip ' =heartip, ' heartdatetime ' =current_timestamp WHERE table

Name. Mac=mac;
ELSE
Insert INTO ' table name ' (' Truename ', ' phone ', ' qqmsn ', ' Mail ', ' mac ', ' Heartip ', ' Heartdatetime ') VALUES (TRUENAME,PHONE,QQMSN, Mail,mac,heartip,current_timestamp);
End IF;
End

Case Condition Statement

The code is as follows Copy Code

Select substr (t1.area_id, 1, 1) type,
       substr (t1.area_id, 2) ID,
 & nbsp;     Case substr (t1.area_id, 1, 1)
         When ' C ' then
          (select T2.country
              from Countnumber.dbtable_countryid T2
             where t2.id = substr (t1.area_id, 2))
          Else
          (SELECT DISTINCT T3.province
             from countnumber.dbtable _provinceid T3
            where t3.id = substr (T1.area_ ID, 2)
       end name
  from T_ad_area T1

The

is value=compare-value in the return result of the first scenario. The return result of the second scheme is the true result of the first. If there is no matching result value, the result is returned, and if there is no else part, the return value is NULL.

  code is as follows copy code

mysql> SELECT Case 1 if 1 THEN ' one '

   ->     when 2 THEN ' two ' ELSE ' more ' end;

       -> ' one '

mysql> SELECT case 1>0 THEN ' true ' ELSE ' False ' end;

       -> ' true '

Mysql> SELECT case BINARY ' B '

   ->     when ' a ' THEN 1 while ' B ' THEN 2 end;

       -> NULL

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.