3月25日 条件函数
条件函数
select if(1<0,'正确','错误');
select ifnull(null,'hello world')
select case
when 1>0
then '对的'
when 2>0
then '2确实大于0'
else '3>0'
end;
以上学习了条件判断函数,在MySQL检索数据,我们能否把数据的返回值赋值给某个制定的变量呢,当然可以,当我们学会了如何赋值后又可以做很多操作,是不是很有趣呢!!!
MySQL的赋值语法
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');
select * from `user_submit`;
select @sum := 1
select @sum
select @girl := (select count(id) from user_submit where profile like '%female')
select @boy := (select count(id) from `user_sumbit`) - @girl;
select if(profile like '%female','女','男') as xb,
count(id) as number
from user_submit group by xb;
select substring_index(profile,',','-1')as gender,count(device_id) from user_submit
group by gender;
加密函数
select md5('123456')