从表中查询数据

[size=medium][align=center][b]Selecting Data from Tables[/b][/align][/size]
[size=small][b]1.Combining Columns to Construct Composite Values[/b][/size]

select date_format(monthname(t),'',dayofmonth(t),',',year(t)) as date_sent,concat(srcuser,'@',srchost) as sender from mail;
CONCAT(列名1,列名2) 函数

[size=small][b]2.WHERE Clauses and Column Aliases[/b][/size]

select size/1024 as kilobytes from mail where size/1024 > 600;

[size=small][b]3.Working with NULL Values[/b][/size]

select name,if(id is null,'Unknown',id) as 'id' from taxpayer;
如果id列中null表示“unknown(未定义),那么在查询语句可以使用函数if(),将id列中的 null 值在查询结果中显示为Unknown”

select name,ifnull(id,'Unknown') as 'id' from taxpayer;
ifnull()函数检测传给它的第一个参数是否为null，如果不是null则返回该值，否则将第二个参数作为返回值

[size=small][b]4.Using Views to Simplify Table Access[/b][/size]

create view mail_view as select aaa as a,bbb as b,ccc as c,size from mail;
select a,b,c from mail_view where size> 5000 order by size;

[size=small][b]5.Selecting Rows from the Middle of a Result Set[/b][/size]

select * from profile order by birth limit 2,1;
limit的两个参数分别指定了从查询结果的第几行开始返回，以及一共返回多少行。

select SQL_CALC_FOUND_ROWS * from profile order by name limit 5;
select FOUND_ROWS();
关键字SQL_CALC_FOUND_ROWS指定服务器计算整个查询结果的行数。