汇聚和分组数据,数据检索

汇集数据

                                 函数

                             说明

                               AVG()

                   返回某列的平均数

                               COUNT()

                   返回某列的行数

                               MAX()

                   返回某列的最大值

                               MIN()

                   返回某列的最小值

                               SUM()

                   返回某列值的和

汇聚和分组数据,数据检索。聚焦数据

                                 函数

                             说明

                               AVG()

                   返回某列的平均数

                               COUNT()

                   返回某列的行数

                               MAX()

                   返回某列的最大值

                               MIN()

                   返回某列的最小值

                               SUM()

                   返回某列值的和

mysql-陆 数据检索(四),mysql-6数据检索

壹、汇总的数量据

1、AVG函数

SELECT AVG(prod_price) AS avg_price FROM products;

997755.com澳门葡京 1

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

997755.com澳门葡京 2

1、AVG函数

SELECT AVG(prod_price) AS avg_price FROM products;

997755.com澳门葡京 3

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

997755.com澳门葡京 4

集聚数据

                                 函数

                             说明

                               AVG()

                   返回某列的平均数

                               COUNT()

                   返回某列的行数

                               MAX()

                   返回某列的最大值

                               MIN()

                   返回某列的最小值

                               SUM()

                   返回某列值的和

办事中时时索要集中数据而不是将它们整个找寻出来(实际多少作者:重临实际数据是对时间和拍卖财富的荒废),那体系型的查找有以下特征:

2、count()函数

SELECT COUNT(*) AS num_cust FROM customers;

997755.com澳门葡京 5

SELECT COUNT(cust_email) AS num_cust FROM customers;

997755.com澳门葡京 6

那条select语句使用count(cust_email)对cust_email列中有值的拓展计数,在此例子中,cust_email的总计式一遍,表示两个客户中唯有四个客户有电子邮件

2、count()函数

SELECT COUNT(*) AS num_cust FROM customers;

997755.com澳门葡京 7

SELECT COUNT(cust_email) AS num_cust FROM customers;

997755.com澳门葡京 8

那条select语句使用count(cust_email)对cust_email列中有值的拓展计数,在此例子中,cust_email的计算式一遍,表示多少个客户中只有多个客户有电子邮件

1、AVG函数

SELECT AVG(prod_price) AS avg_price FROM products;

997755.com澳门葡京 9

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

997755.com澳门葡京 10

明确表中的行数(或然满足某些条件或带有有个别特定值的行数)

3、max()函数

SELECT MAX(prod_price) AS max_price FROM products;

997755.com澳门葡京 11

3、max()函数

SELECT MAX(prod_price) AS max_price FROM products;

997755.com澳门葡京 12

2、count()函数

SELECT COUNT(*) AS num_cust FROM customers;

997755.com澳门葡京 13

SELECT COUNT(cust_email) AS num_cust FROM customers;

997755.com澳门葡京 14

那条select语句使用count(cust_email)对cust_email列中有值的张开计数,在此例子中,cust_email的总结式2遍,表示6个客户中只有多个客户有电子邮件

收获表中央银行组的和

4、min()函数

SELECT MIN(prod_price) AS min_price FROM products;

997755.com澳门葡京 15

4、min()函数

SELECT MIN(prod_price) AS min_price FROM products;

997755.com澳门葡京 16

3、max()函数

SELECT MAX(prod_price) AS max_price FROM products;

997755.com澳门葡京 17

搜索表列(或富有行或有个别特定的行)的最大值、最小值和平均值

5、sum()函数

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

997755.com澳门葡京 18

SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

997755.com澳门葡京 19

SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;

997755.com澳门葡京 20

 

5、sum()函数

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

997755.com澳门葡京 21

SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

997755.com澳门葡京 22

SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;

997755.com澳门葡京 23

 

4、min()函数

SELECT MIN(prod_price) AS min_price FROM products;

997755.com澳门葡京 24

晤面函数(aggregate
function):运维在行组上,计算和重临单个值的函数(MySQL还帮助部分列的规范不是聚集函数);

分组数据    group by 

SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;

997755.com澳门葡京 25

SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;

997755.com澳门葡京 26

SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;

997755.com澳门葡京 27

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;

997755.com澳门葡京 28

order by 一、排序产生的出口 二、肆意列都能够运用 三、不自然须要

group by 1、分组行,但输出大概不是分组的次第二、只恐怕行使选用列或表明式列,而且必须选取种种选用列表达式
三、如果与聚焦函数一同利用列,则必须利用

 where过滤行,having过滤组

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;

997755.com澳门葡京 29

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;

997755.com澳门葡京 30

 

分组数据    group by 

SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;

997755.com澳门葡京 31

SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;

997755.com澳门葡京 32

SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;

997755.com澳门葡京 33

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;

997755.com澳门葡京 34

order by 一、排序发生的输出 二、任性列都足以运用 3、不必然须求

group by 一、分组行,但输出可能不是分组的顺序
二、只大概采取选取列或表明式列,而且必须利用各类选择列表明式
叁、借使与集中函数一同行使列,则必须运用

 where过滤行,having过滤组

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;

997755.com澳门葡京 35

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;

997755.com澳门葡京 36

 

5、sum()函数

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

997755.com澳门葡京 37

SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

997755.com澳门葡京 38

SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;

997755.com澳门葡京 39

 

SQL聚集函数如下表:

 使用子查询

 

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

997755.com澳门葡京 40

 

 

SELECT AVG(prod_price) *AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
SELECT COUNT(*) AS num_cust FROM customers;
SELECT COUNT(cust_email) AS num_cust FROM customers;
SELECT MAX(prod_price) AS max_price FROM products;
SELECT MIN(prod_price) AS min_price FROM products;
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;
SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;
SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;
SELECT cust_id FROM orders ;
SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;
SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;
SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;
SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

997755.com澳门葡京, 

 使用子查询

 

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

997755.com澳门葡京 41

 

 

SELECT AVG(prod_price) *AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
SELECT COUNT(*) AS num_cust FROM customers;
SELECT COUNT(cust_email) AS num_cust FROM customers;
SELECT MAX(prod_price) AS max_price FROM products;
SELECT MIN(prod_price) AS min_price FROM products;
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;
SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;
SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;
SELECT cust_id FROM orders ;
SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;
SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;
SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;
SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

 

分组数据    group by 

SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;

997755.com澳门葡京 42

SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;

997755.com澳门葡京 43

SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;

997755.com澳门葡京 44

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;

997755.com澳门葡京 45

order by 1、排序产生的输出 二、放4列都能够利用 叁、不自然需求

group by 1、分组行,但输出大概不是分组的依次
二、只或许应用选拔列或表达式列,而且必须运用各个选择列表达式
叁、假如与聚集函数一齐利用列,则必须使用

 where过滤行,having过滤组

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;

997755.com澳门葡京 46

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;

997755.com澳门葡京 47

 

997755.com澳门葡京 48

 使用子查询

 

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

997755.com澳门葡京 49

 

 

SELECT AVG(prod_price) *AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
SELECT COUNT(*) AS num_cust FROM customers;
SELECT COUNT(cust_email) AS num_cust FROM customers;
SELECT MAX(prod_price) AS max_price FROM products;
SELECT MIN(prod_price) AS min_price FROM products;
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;
SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;
SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;
SELECT cust_id FROM orders ;
SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;
SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;
SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;
SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

 

数据检索(4),mysql-陆数据检索 汇总数据
函数 表明 AVG() 再次来到某列的平平均数量 COUNT() 再次来到某列的行数 MAX()
重临某列的最大值 MIN() 重返某…

 

1、avg()函数

avg()通过对表中央银行数计数并企图特定列值之和,求得该列的平均值;avg()可用来回到全部列平均值,也可用来回到特定列的平均值;

select
avg(prod_price) as avg_price from pfoducts;

此select语句重临值avg_price,它包蕴producs表中持有成品的平均价格,avg_price是多少个外号;

avg()也足以用来分明特定列或行的平均值,例子如下:

select
avg_(prod_price) as avg_price from products where vend_id =
1003;

那条SQL语句包罗了where子语句,仅过滤出vend_id为1003的产品,avg_price中回到该供应商的制品的平均值;

PS:avg()只好用来规定特定数值列的平均值,而且列名必须作为函数参数给出,为了博取八个列的平均值,必须采取三个avg()函数{avg()函数忽略列值为NULL的行};

 

2、count()函数

count()函数实行计数,可应用count()分明表中央银行的数据或符合特定条件的行的数额;

count()函数有三种选择办法:

使用count(*)对表中央银行的数额实行计数,不管表列中隐含的是空值(null)依旧非空值;

应用count(column)对一定列中具备值的行开始展览计数,忽略null值;

select
count(*) as num_cust from customers;

那条SQL语句利用count(*)对customers表中保有行计数,计数值在num_cust中返回;

select
count(cust_email) as cum_cust from customers;

这条SQL语句使用count(cust_email)对cust_email列中有值的行实行计数;

PS:借使钦命列名,则钦命列的值为空的行被count()函数忽略,但假如count()函数中用的是星号(*),则不忽视; 

 

3、max()函数

max()再次回到内定列中的最大值,max()须要钦定列名,例子如下:

select
max(prod_price) as max_price from products;

这条SQL语句中华夏族民共和国回到products表中price列的最大值;

PS:MySQL允许max()用来回到任意列中的最大值,包含再次回到文本列的最大值;但用于文书数据时,假如数量按对应的列排序,则max()重返最后一行(max()函数忽略列值为null的行)

 

4、min()函数

min()再次回到钦点列的小小值,min()也供给钦赐列名,例子如下:

select
min(prod_price) as min_price from products;

那条SQL语句中min()重回products表中price列最小值;

PS:MySQL允许min()用来回到率性列中的最小值,包蕴再次回到文本列的小小值;但用于文书数据时,要是数量按对应的列排序,则min()重返最前头的行(min()函数忽略列值为null的行)

 

5、sum()函数

sum()函数用来回到钦赐列值的和(计算);例子如下:

select
sum(quantity) as items_ordered from orderitems where order_num =
20005;

函数sum()重临orderitems中兼有quantity列的值之和,where子句保险只总括某些内定列的数值;

PS:运用标准的算数操作符,全部集中函数都可用来进行八个列上的测算(sum()函数忽略列值为null的行)

 

陆、distinct与聚焦函数

MySQL五.0.3以及未来的版本,聚集函数和distinct能够搭配使用,比方:

对富有的行实践计算,钦命all参数或不给参数(all是暗许全体行为举止,不要求钦命,如若不点名distinct,则只要为all);

只包罗差别的值,钦定distinct参数;

假定钦点列名,则distinct只可以用于count();distinct不能够用来count(*),因而不容许使用count(distinct);distinct必须运用列名,无法用来总结照旧表达式;

select
avg(distinct prod_price) as avg_price from products where vend_id =
1003;

这条SQL语句中,使用avg()函数重回vend列中vend_id=100三的相应的price平均价格,因为使用了distinct参数,由此平均值只思量分裂的值(唯1值)

 

七、组合聚集函数

select语句能够包罗多少个聚众函数,比方:

select
count(*) as num_items,

         
min(prod_price) as price_min,

         
max(prod_price) as price_max,

         
avg(prod_price) as price_avg

from
products;

那条SQL语句,使用了单条select语句实施三个聚众总结,重临几个值(products表中items的多寡、price的万丈、最低以及平均值)

PS:在钦赐外号以富含有个别聚焦函数的结果时,不应有使用表中实际的列名;那样方便使用SQL尤其轻易和清楚,以及清除方便排除错误。

 

2、分组数据

一、group
by创设分组

在MySQL中,分组是在select语句中的group
by子句中树立的,比方:

select
vend-id,count(*) as num_prods from products group by vend_id;

那条SQL语句钦点了一个列,group
by提示MySQL遵照vend_id排序并且分组(要是应用group
by,则无需内定要总结的各种组)

group
by子句提醒提示MySQL分组数据,然后都每一种组而不是漫天结果集举行联谊;关于group
by使用,请小心以下规则:

group
by子句能够包含自由数目标列(使得对分组实行嵌套,为数据分组提供更全面包车型大巴决定);

假定在group
by子句中嵌套分组,数据就要结尾明显的分组上开始展览汇总,即:建立分组时,内定的装有列都一同总结(所以无法从各自列取回数据);

group
by子句中列出的种种列都必须是搜索列或有效的说明式(但不能是聚众函数),假设在select中央银行使表明式,则必须在group
by子句中内定一样的表明式(不可能采取小名);

除去集合总结语句外,select中各个列都必须在group
by子句中提交;

如若分组列中具有null值,则null将用作三个分组再次回到(倘诺列中有多行null值,他们将分成壹组);

group
by子句必须出现在where子句之后,order by子句之前;

PS:运用with
rollup关键字,能够获取每种分组以及各种分组集中品级(针对各类分组)的值。

 

二、having过滤分组

where子句都得以用having代替,分化在于where过滤行,having过滤分组;having帮忙全数的where操作符,举例:

select
cust_id,count(*) as orders from orders group by cust_id having
count(*) >= 2;

那条SQL语句中的having子句过滤count(*)>=二(二个以上的分组)的这些分组;

having和where的区别:

where在数量分组前举办过滤,having在数码分组后开始展览过滤;where排除的行不包含在分组中(那只怕会退换总括值,从而影响having子句中基于那么些值过滤掉的分组)

having和where能够而且选拔,举例:

select
vend_id, count(*) as num_prods from products where prod_price>=10
group by vend_id having count(*)>= 2;

那条SQL语句中,where子句过滤掉全部prod_price至少为十的行,然后遵照vend_id分组数据;having子句过滤才能为二或二之上的分组;

 

三、分组和排序

group
by和order by的区别:

997755.com澳门葡京 50

order
by的第贰:
相似采取group by子句时,应该也给出order
by子句,这是保证数据精确性的唯1办法(千万不要借助group
by排序数据)。

 

4、select子句顺序

997755.com澳门葡京 51

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website