select*from表orderby列asc-根据“列”从小到大排列 select*from表orderby列desc-根据“列”从大到小排列
select*from表orderby列1 desc,列2 asc-根据“列1”从大到小排列,如果相同则按列2从小到大排序
4.3、模糊查询
通配符(模糊查询)
select*from表where name like'ale%'- ale开头的所有(多个字符串) select*from表where name like'ale_'- ale开头的所有(一个字符)
4.4、聚集函数查询
聚集函数 4.5、分组查询
分组
select num from表groupby num selectnum,nidfrom表groupby num,nid
selectnum,nidfrom表where nid >10groupby num,nid order nid desc
selectnum,nid,count(*),sum(score),max(score),min(score) from表groupby num,nid
select num from表groupby num havingmax(id) >10
特别的:groupby必须在where之后,order by之前
4.6多表查询
a、连表
无对应关系则不显示
select A.num, A.name, B.name fromA,B
Where A.nid = B.nid
无对应关系则不显示
select A.num, A.name, B.name from A innerjoin B on A.nid = B.nid
A表所有显示,如果B中无对应关系,则值为null select A.num, A.name, B.name
from A leftjoin B on A.nid = B.nid
B表所有显示,如果B中无对应关系,则值为null select A.num, A.name, B.name from A rightjoin B on A.nid = B.nid b、组合
组合,自动处理重合 select nickname from A union
select name from B
组合,不处理重合 select nickname from A unionall select name from B