mysql 分组 查询 表中 字段 最大的 记录 mysql的分组查询语句

mysql-查询、聚合、排序

• 1 查询

• 1.1 select-选取数据
• 1.2 Where-从表中选取满足条件的数据
• 1.3 (and、or、not)逻辑运算符
• 1.4 练习题

• 2 聚合函数
• 3 group by-分组

• 3.1 group by的书写规则
• 3.2 order by-排序
• 3.3 练习题

1 查询

首先导入需要用到的数据

create table product
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

1.1 select-选取数据

select的语法形式为：

SELECT <列名>, 
  FROM <表名>;

例如：选取product中的商品的类别信息

select product_type
	from product;

注：如果要选取所有的列的语句为：

select *
	from product;

1.2 Where-从表中选取满足条件的数据

where的语法为：

SELECT <列名>, ……
  FROM <表名>
 WHERE <条件表达式>;

例如，查询商品类别为厨房用具的商品信息

select *
	from product
	where product_type = '厨房用具';

注：这里select 后面选择的列可以是不同与where子句(product_type)的列

例如：

SELECT product_name
  FROM product
 WHERE product_type = '厨具用品';

1.3 (and、or、not)逻辑运算符

例如：选取销售单价(sale_price)超过1000元的商品记录

select product_name,product_type,sale_price
	from product
	where not sale_price <= 1000;
/*
另一种正常的方法
select product_name,product_type,sale_price
	from product
	where  sale_price > 1000;
*/

先要找一种商品：商品种类为办公用品”并且“登记日期是 2009 年 9 月 11 日或者 2009 年 9 月 20 日

select product_name, product_type, regist_date
	from product
	where product_type = '办公用品'
 	and (regist_date = '2009-09-11'
    or regist_date = '2009-09-20');

注：特别注意一定要加括号
如果不给or的语句加括号形如：

select product_name, product_type, regist_date
	from product
	where product_type = '办公用品'
 	and regist_date = '2009-09-11'
    or regist_date = '2009-09-20';

结果为：

一般and比or的优先级高，所以先计算1.是办公用品且日期为2009-9-11的。2.再与日期为2009-9-20的取并集

1.4 练习题

1.编写一条SQL语句，从product（商品）表中选取出“登记日期（regist在2009年4月28日之后”的商品，查询结果要包含product name和regist_date两列

select product_name,regist_date
	from product
	where regist_date>'2009-04-28' ;

2.请说出对product 表执行如下3条SELECT语句时的返回结果

select *
    from product
    where purchase_price = NULL;

结果：NULL，因为任何值都不等于NULL
应该改为：

select *
    from product
    where purchase_price is NULL;

select *
  from product
  where purchase_price <> NULL;

结果：NULL，<>是不等于的意思，应该改为：

select *
  from product
  where purchase_price is not NULL;

3.代码清单2-22（2-2节）中的SELECT语句能够从product表中取出“销售单价（saleprice）比进货单价（purchase price）高出500日元以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示

product_name | sale_price | purchase_price 
-------------+------------+------------
T恤衫         | 　 1000    | 500
运动T恤       |    4000    | 2800
高压锅        |    6800    | 5000

-- 1
select product_name,sale_price,purchase_price
	from product
	where sale_price-purchase_price > 500;
/* 2
select product_name,sale_price,purchase_price
	from product
	where not sale_price-purchase_price <= 500;
*/

4.请写出一条SELECT语句，从product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润（别名设定为profit）。

提示：销售单价打九折，可以通过saleprice列的值乘以0.9获得，利润可以通过该值减去purchase_price列的值获得。

select *
from(
	select product_name,product_type,(sale_price*0.9-purchase_price) as profit
	from product) as t
    where profit > 100
	and (product_type = '办公用品'
    or product_type = '厨房用具');

注：第一次selcet不能使用别名，第二次才可以使用所以这里嵌套了一个select，也可以用下面这种办法，输出结果一样。

select product_name,product_type,(sale_price*0.9-purchase_price) as profit
	from product
    where sale_price*0.9-purchase_price > 100
	and (product_type = '办公用品'
    or product_type = '厨房用具');

2 聚合函数

• count-统计个数(可以用于任何类型)
• sum-求和(只能用于数值型)
• max-求最大值(可以用于字符等类型)
• min-求最小值(同max)
• avg-求平均(同sum)
  例如：以下是聚合函数的应用

-- 计算全部数据的行数（包含NULL）
SELECT COUNT(*)
  FROM product;
-- 计算NULL以外数据的行数
SELECT COUNT(purchase_price)
  FROM product;
-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price) 
  FROM product;
-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
  FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
  FROM product;

3 group by-分组

3.1 group by的书写规则

group by的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句顺序为：

1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

其中前三项用于筛选数据，group by对筛选出的数据进行处理

group by的语法为：

SELECT <列名1>,<列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

例如：选中商品类别为衣服，商品价格的分组个数的信息

select purchase_price, COUNT(*)
  from product
  where product_type = '衣服'
  group by purchase_price;

将表使用group by分组后，怎样才能只取出其中两组？

注：group by后的条件过滤用having用法类似于where

3.2 order by-排序

order by的语法为：

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1>, <排序基准列2>, ……

例如：1.对sale_price升序排列2.对purchase_price降序

select *
	from product
	order by sale_price;

select *
	from product
	order by purchase_price desc;

注：order by默认为升序，如果降序后面加desc即可

3.3 练习题

一、请指出下述SELECT语句中所有的语法错误。

SELECT product_id, SUM（product_name）
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

错误：1.sum不可以对字符类型使用 2.group by不可以使用where，应使用having

二、请编写一条SELECT语句，求出销售单价（ sale_price 列）合计值大于进货单价（ purchase_price 列）合计值1.5倍的商品种类。执行结果如下所示。

product_type | sum  | sum 
-------------+------+------
衣服         | 5000 | 3300
办公用品      |  600 | 320

select product_type,sum(sale_price),sum(purchase_price)
	from product
	group by product_type
	having sum(sale_price) > 1.5*sum(purchase_price);

三、此前我们曾经使用SELECT语句选取出了product（商品）表中的全部记录。当时我们使用了ORDERBY子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考ORDERBY子句的内容。

select *
	from product
    order by regist_date desc,sale_price ;

注：观察到regist_date是降序排列，且2009-9-20相等处又对sale_price升序排列，所以进行两次排序，先对regist_date,后对sale_price