select
- 常见的查询语句
select * from tableName;
- 查询指定的字段
select `columnName1`,`columnName2`,... from tableName;
where
添加筛选条件筛选查询数据
select * from tableName where `columnName` = '筛选条件';
多条件筛选查询数据
select * from tableName where `columnName` = '筛选条件1' and `columnName2` = '筛选条件2';
//示例2
select * from tableName where `columnName` = '筛选条件1' and `columnName2` between 1 and 10;
<>(!=)
//查询字段不等于1的记录
select * from tableName where `columnName` <> 1;
select * from tableName where `columnName` != 1;
between
//查询字段1在范围1~10的记录
select * from tableName where `columnName1` between 1 and 10;
like
通过like进行模糊查询,使用%表示模糊部分
//查询字段1前2位数是12的记录
select * from tableName where `columnName1` like '12%';
//查询字段2前2位是12,结尾是56的记录
select * from tableName where `columnName2` like '12%56';
//查询字段3包含数字89的记录
select * from table Name where `columnName3` like '%89%';
as
- 修改查询出的数据的字段名称
select `columnName1` as `newColumnName` from tableName;
order by
order by的默认排序规则为ASC(小->大),可以设置DESC(大->小)
- 字段1从小到大查询数据
select * from tableName order by columnName1;
- 字段1从大到小查询数据
select * from tableName order by columnName1 desc;
- 字段1和字段2从小到大查询,先判定字段1,再判定字段2
select * from tableName order by columnName1,columnName2;
- 字段1从小到大,字段2从大到小
select * from tableName order by columnName1,columnName2 desc;
group by
通过group by可以对查询的数据分组,筛选条件相同的数据会合并显示第一条数据。
//使用字段1进行分组
select * from tableName group by `columnName1`;
//使用字段1和字段2进行分组,只有字段1和字段2完全相同才会合并为一组
select * from tableName group by `columnName1`,`columnName2`;
group_concat
使用group by进行分组时,分组的数据会合并并显示第一条数据,有时候需要对columnName1进行分组,当是需要显示合并的记录的不同的字段columnName2,这时候就可以通过group_concat实现
select `columnName1`,group_concat(`columnName2`) as `columnName2` from tableName group by `columnName1`;