1、读取不重复数据 distinct关键字

select distinct va1 val2,,,valn from tbl_xxx    # 这里面是值的val,val2...valn的并集不同就显示

select distinct house_title from tbl_house;

2、查询过程中实现四则运算

select ename sla*12 from tbl_xxx;

select ename, sla*12 as yearsalary from tbl_xxx;

3、设置显示数据的格式

select contact(ename,‘雇员年薪为:‘,sla*12) yearsalary from tbl_xxx;

select field1,field2 from tbl_xxx where condition

这里面的condition，可以有很多类型

1、带关系运算符和逻辑运算符的条件查询

select distinct title,location from house_price_list2 where location like ‘%麓山%‘ and field1>10;

2、带 between and 关键字的条件查询

select distinct title,location from house_price_list2 where location like ‘%麓山%‘ and field1>10 and field2 between 10 and 100;

3、带 is null 关键字的条件查询

select distinct title,location from house_price_list2 where location like ‘%麓山%‘ and field1>10 and field3 is null;

4、带 in 的关键字的条件查询

select distinct title,location from house_price_list2 where location like ‘%麓山%‘ and field1>10 and field4 in (12,12,3,4,5);

5、带 like 关键字的条件查询

select distinct title,location from house_price_list2 where location like ‘%麓山%‘;

1、 单字段排序

select distinct title,location from house_price_list2 where location like ‘%麓山%‘ order by location;

select distinct title,location from house_price_list2 where location like ‘%麓山%‘ order by location desc; 降序

select distinct title,location from house_price_list2 where location like ‘%麓山%‘ order by location asc; 升序

2、 多字段排序

select distinct title,location from house_price_list2 where location like ‘%麓山%‘ order by location desc,title desc;

select * from house_price_list2 limit 100;

select * from house_price_list2 limit 0,100;   # 初始位置0，到100行

1、函数类型： count() avg() sum() max() min()

select count(*) from house_price_list2 where location like ‘%马%‘;

这里的*指的所有字段

一般形式如

select function(field1) from tbl_xxx where condition

2、简单分组查询

select * from house_price_list2 group by house_info;   # 简单分组查询，对house_info分组，然后显示每个分组的一条记录

仅仅使用简单分组查询是没有什么实际意义，因为这个显示的一条数据是随机的

3、实现统计功能分组查询

select GROUP_CONCAT(house_price) FROM house_price_list2 GROUP BY house_info;

一般形式如下

select group_concat(field) from tbl_xxx where condition group by field;

这种分组查询，可以看到每个组中的字段值