mysql 常用命令之函数

站mysql distinct函数对某个字段去重复,(在某些时候group by也可以做到)例:select distinct(area) from user; 对地区进行去重复select area,count(*) from user group by area;　对地区进行聚合并统计出数量 mysql replace函数查找某个字符串并进行替换例:select replace(icon,"myeducs.cn/","img.rjkfw.com") from software; 把icon中的myeducs.cn/替换成替换成img.rjkfw.com显示update software set icon=replace(icon,"myeducs.cn/","img.rjkfw.com") ; 把数据库中icon的域名批量进行查找替换 myeducs.cn/mysql in函数可批量指定几个值作为查询条件例:select * from user where user_id in(1,2,3,4,5,100,200,333)select * from user where user_name in("a","b","d")mysql like函数可对某个字段进行模糊查询,"%"号用于匹配任意字符例:select * from user where name like "%王%"; 查询所有用户名中带"王"字符的用户select * from user where name like "%王";  查询所有用户名第一个字符为"王"字的用户 mysql is null函数匹配某个字符为null值的记录,注：null不代表空符串""例:select * from user where a is null ; 查询a字段为null的用户select a.* from user as a left join add_user as b on a.user_id=b.user_id where b.user_id is null; 连表查询附加表add_user中没有附加用户信息数据的用户 mysql is not null函数和is null用法一样，匹配某个字符不为空的记录 mysql MATCH ... AGAINST　全文匹配函数mysql的全文匹配函数，要使用此函数查询的字符必须增加了全文索引，另外mysql不支持中文全文索引，所以国人在开发中估计很少用到此函数。match中包含要进行全文匹配的字段，多个字段用","号分割 against为匹配的字符串例:select * from software where match(title,body) against("php"); 全文匹配title和body字段中包含"php"的记录select * from software where match(title) against("php mysql"); 全文匹配title字段中包含"php mysql"的记录。