Mysql condition query ifcase usage

The where condition query code is as follows: select * fromnewswhereDATE (adddate) between2011-04-20

The where condition query code is as follows: select * from news where DATE (adddate) between '2017-04-20 '-INTERVAL 5 DAY and '2017-04-20' + INTERVAL 5 DAY select * from news where DATE (adddate) in ('2017-04-20 ', '2014-04-15 ', '2014-04-25') IF Condition Statement using mysql

Where condition Query

The Code is as follows:

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:

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:

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:

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