select * from 表 select * from 表 whereid > 1 select nid,name,gender as gg from 表 whereid > 1
4.5. 条件判断
4.5.1. where
1 2 3 4 5
select * from <table> whereid >1andname!='huwh'andnum =12; select * from <table> whereidbetween5and6; select * from <table> whereidin (11,22,33); select * from <table> whereidnotin (11,22,33); select * from <table> whereidin (select nid from <table>)
4.5.2. 通配符like
1 2
select * from <table> wherenamelike'hu%'; #hu开头 select * from <table> wherenamelike'hu_'#hu开头后接一个字符
4.5.3. 限制limit
1 2 3
select * from <table> limit5; #前5行 select * from <table> limit4,5#从第四行开始的5行 select * from <table> limit5offset4;#从第四行开始的5行
4.5.4. 排序asc,desc
1 2 3
select * from <table> orderby 列 asc; #跟据“列”从小到大排序(不指定默认为从小到大排序) select * from <table> orderby 列 desc; #根据“列”从大到小排序 select * from <table> orderby 列1desc,列2asc; #根据“列1”从大到小排序,如果相同则按“列2”从小到大排序
4.5.5. 分组group by
group by 必须在where之后,order by之前。
1 2 3 4 5 6
selectnum,from <table> groupbynum; selectnum,nid from <table> groupbynum,nid; selectnumfrom <table> where nid > 10groupbynum,nid order nid desc; selectnum,nid,count(*),sum(score),max(score) from <table> groupbynum; selectnumfrom <table> groupbynumhavingmax(id) > 10; selectnumfrom <table> groupbynum;