Where Condition Query
The code is as follows
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
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
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 return result of the first scenario, Value=compare-value. 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.
The code is as follows
mysql> SELECT Case 1 when 1 THEN ' one '
-> when 2 THEN ' two ' ELSE ' more ' end;
-> ' One '
Mysql> SELECT case is 1>0 THEN ' true ' ELSE ' false ' end;
-> ' true '
mysql> SELECT case BINARY ' B '
-> when ' a ' THEN 1 when ' B ' THEN 2 end;
-> NULL