In addition to the where clause, we can use the if case Method to Determine the query result. This is more advanced than the where clause. I will introduce several conditional query instances.
Where condition Query
The Code is as follows: |
Copy code |
Select * from news where DATE (adddate) '2017-04-20 '-INTERVAL 5 DAY and '2017-04-20' + INTERVAL 5 DAY
Select * from news where DATE (adddate) in ('2017-04-20 ', '2017-04-15', '2017-04-25 ') |
Use of IF condition statements
Use of the mysql condition judgment statement if: first, determine whether a specified value exists in the Database and add it if the modification does not exist.
Here I will use the if statement of the mysql stored procedure.
The Code is as follows: |
Copy code |
Drop procedure if exists process name; Create procedure process 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 from 'table name 'where' table name'. mac = mac; -- whether this record exists 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, 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 |
In the returned results of the first scheme, value = compare-value. The returned results of the second solution are the real results of the first case. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, the return value is NULL.
The Code is as follows: |
Copy code |
Mysql> select case 1 WHEN 1 THEN 'one' -> WHEN 2 THEN 'two' ELSE 'more' END; -> 'One' Mysql> select case when 1> 0 THEN 'true' ELSE 'false' END; -> 'True' Mysql> select case binary 'B' -> WHEN 'a 'then 1 WHEN 'B' THEN 2 END; -> NULL |