数据库查询语句

一.基本查询

1.查询所有数据

select * from table;

2.查询部分字段

select field1,field2 from table;

二.条件查询

`1.单个条件查询

select * from table where field=x;

2.多个条件查询

select * from table where field1=x and field2=y;

三.模糊查询

select * from table where field like "%x%";

四.范围查询

1.in/not in

select * from table where field in (x,y);

2.between and

select * from table where field between a and b;

3.大于小于

select * from table where field<a and field>b;

五.聚合函数

1.聚合函数

select count(*),max(a),min(a),avg(a) from table;

2.分组

select count(*) from table group by field1;

3.筛选

select count(*) from table having field>a;

4.聚合函数组合使用

select count(*) from table where field1>a group by field2 having count(field3)>b;

六.左右连接

1.左连接

select * from table1 left join table2 on table1.a=table2.b;

2.右连接

select * from table1 right join table2 on table1.a=table.b;

3.内连接

slect * from table1 inner join table2 on table1.a=table2.b;

七.排序

1.升序

select * from table order by field asc;

2.降序

select * from table order by field desc;

八.分页

select * from table limit 1,3;     #从第一行开始的三行