where條件查詢
代碼如下
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')
IF條件語句的使用
mysql條件判斷語句if的使用:先判斷數據庫中是否存在是否存在指定值存在就修改不存在就添加。
這裡我就用啦mysql存儲過程的if語句
代碼如下
DROP PROCEDURE IF EXISTS 過程名;
CREATE PROCEDURE 過程名(
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 `表名` where `表名`.mac=mac; --是否存在此記錄
if (@maccount>0) THEN
update `表名` set `trueName`=trueName,`phone`=phone,`qqmsn`=qqmsn,`mail`=mail,`mac`=mac,
`heart`=`heart`+1,`heartip`=heartip,`heartdatetime`=CURRENT_TIMESTAMP WHERE 表
名.mac=mac;
ELSE
insert into `表名` (`trueName`,`phone`,`qqmsn`,`mail`,`mac`,`heartip`,`heartdatetime`)VALUES(trueName,phone,qqmsn,
mail,mac,heartip,CURRENT_TIMESTAMP);
end IF;
END
case條件語句
代碼如下
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
在第一個方案的返回結果中， value=compare-value。而第二個方案的返回結果是第一種情況的真實結果。如果沒有匹配的結果值，則返回結果為ELSE後的結果，如果沒有ELSE 部分，則返回值為 NULL。
代碼如下
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
