mysql的CUDR

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

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

3

4

select distinct house_title from tbl_house;

5

6

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

7

select ename sla*12 from tbl_xxx;

8

9

select ename, sla*12 as yearsalary from tbl_xxx;

10

11

3、设置显示数据的格式

12

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

13

   

4.2 条件数据记录查询

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 field1,field2 from tbl_xxx where condition

2

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

3

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

4

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

5

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

6

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

7

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

8

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

9

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

10

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

11

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

12

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

13

14

   

4.3 排序数据记录查询

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;
                      1

1、 单字段排序

2

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

3

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

4

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

5

6

2、 多字段排序

7

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

8

   

4.4 限制数据记录查询数量

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

select * from house_price_list2 limit 100;

2

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

3

   

4.5 统计函数和分组数据记录查询

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; 这种分组查询，可以看到每个组中的字段值
                      1

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

2

3

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

4

5

这里的*指的所有字段

6

一般形式如

7

select function(field1) from tbl_xxx where condition

8

9

2、简单分组查询

10

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

11

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

12

13

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

14

select GROUP_CONCAT(house_price) FROM house_price_list2 GROUP BY house_info;

15

一般形式如下

16

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

17

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

18

19

   

mysql的CUDR

标签：under   xtu   随机   from   silver   pos   简单的   strong   opera