mysql数据库编程精华案例347个（知此347个例子，便是高手矣）

1.现在有一个表如下
id name sign_date class
1 张三 2012-11-13 1
2 李四 2012-11-13 1
3 王五 null 2
4 无名氏 null 2
现在想用一条sql语句查询,把学生按照class进行分组,求出学生的签到数跟签到率.

比如这个表,得出的结果应该是两个结果集,第一个是class为1的,里面包含3个信息分别是班级 签到人数 签到率{1,2,100%}第二个结果集是class为2的,{2,0,0%}

select class,count(sign_date) as 签到数,count(sign_date)/count(name) as 签到率 from table 
group by class;

2.给你的mysql加上自增列号，注意：mysql可没有那个row_number, 也没有rownum怎么办呢？看看吧，这是我在美线海运查询时的视图usipi中遇到的。

SELECT @rowNum:=@rowNum+1 rowNum,t.* FROM (SELECT @rowNum:=0,uswc.* FROM uswc) t;

但是不能用在视图中ERROR 1351 (HY000): View's SELECT contains a variable or parameter

最终语句：

SELECT @rowNum:=@rowNum+1 rowNum,t.* FROM (SELECT @rowNum:=0, I.baseport AS IBASEPORT,I.port AS IPORT,I.dest AS IDEST,I.commodity AS ICOMMODITY,I.p20gp AS IP20GP,I.p40gp AS IP40GP,I.p40hq AS IP40HQ,I.p45hq AS IP45HQ,I.etd AS IETD,I.note AS INOTE,I.tt AS ITT,D.single AS DSINGLE,D.dest AS DDEST,D.via AS DVIA,D.p20gp AS DP20GP,D.p40gp AS DP40GP,D.p40hq AS DP40HQ,D.p45hq AS DP45HQ,D.svc AS DSVC,O.carrier AS OCARRIER,O.effective AS OEFFECTIVE,O.endDate AS OENDDATE FROM usipi I LEFT JOIN usdestaddon D ON I.port=D.via LEFT JOIN usothers O ON D.single = O.single WHERE D.single = (SELECT MAX(single) FROM usothers) AND I.baseport="SHANGHAI" AND D.dest="EL PASO,TX") t

3.mysql正则表达式

匹配汉字：

SELECT * FROM news WHERE HEX(`title`) REGEXP '[[:<:]](e[4-9][0-9a-f]{4}|3[0-9]|4[0-9A-F]|5[0-9A]|6[0-9A-F]|7[0-9A]|5F)+[[:>:]]'

匹配英文：

SELECT * FROM news WHERE `title` REGEXP '[a-zA-Z]+'

匹配数字：

SELECT * FROM news WHERE `title` REGEXP '[0-9]+'