How to use the MySQL select statement

select statements can be separated by carriage returns

$sql="select * from article where id=1"和  $sql="select * from article where id=1"都可以得到正确的结果，但有时分开写或许能 更明了一点，特别是当sql语句比较长时。

Batch query data

可以用in 来实现$sql="select * from article where id  ;in(1,3,5)"

Use concat to connect the query results

$sql="select concat(id,"-",con)  as res from article where id=1"返回 "1-article content"

Use locate

用法： select locate("hello","hello baby");返回1
不存在返回 0

Use group by

以前一直没怎么搞明group by  和 order by，其实也满简单的，group by 是把相同的结 果编为一组
exam：$sql="select city ,count(*)  from customer group by city";
这句话的意 思就是从customer表里列出所有不重复的城市，及其数量（有点类似distinct）
group by 经常与AVG(),MIN(),MAX(),SUM(),COUNT()一起使用

Use having

having 允许有条件地聚合数据为组$sql="select city,count(*),min(birth_day)  from customer
group by city having c ount(*)>10";
这句话是先按city归组，然后找出city地数量大于10的城 市
btw：使用group by + having 速度有点慢
同 时having子句包含的表达式必须在之前出现过

Combining clauses

where、 group by、having、order by（如果这四个都要使用的话，一般按这 个顺序排列）

Use distinct

distinct是去掉重复值用的$sql="select distinct city from customer  order by id desc";
这句话的意思就是从customer表中查 询所有的不重复的city

Use limit

如果要显示某条记录之后的所 有记录$sql="select * from article limit 100,- 1";

Multiple table query

$sql="select user_name from user  u,membe r  m
where u.id=m.id and
m.reg_date> ;=2006-12-28
order by u.id desc"

Note: If both user and member have user_name fields at the same time, a mysql error will occur (because mysql does not know which table you want to query for user_name ), you must specify which table it is from.

The above is the detailed content of How to use the MySQL select statement. For more information, please follow other related articles on the PHP Chinese website!