mysql数据检索和输出_MySql必知必会实战练习（二）数据检索

在上篇博客

1. Select子句使用顺序

select--->DISTINCT--->from--->where--->GROUP BY--->HAVING--->ORDER BY--->LIMIT

(1)DISTINCT

select verd_id from products;

使用DISTINCT检索出不同值的列表

select DISTINCT verd_id from products;

(2)Group By

首先看下下面3个查询语句的结果：

select count(*) from products;

select * from products where verd_id = 1003;

select count(*) from products where verd_id = 1003;

(1)

(2)

(3)

(1)表示products表的总项数14

(2)列出了verd_id为1003的所有项

(3)显示verd_id为1003的总项数7

再看下面语句的输出结果：

select verd_id, count(*) as num_prods from products GROUP BY verd_id;

结果一目了然，分别对verd_id进行分组，并显示各组的总项数。

注：如果再select中使用表达式，则必须再GROUP BY字句中指定相同的表达式，不能使用别名。

(3)HAVING

HAVING语句主要是对分组语句进行过滤，WHERE过滤指定的是行而不是分组，事实上，WHERE没有分组的概念

HAVING与WHERE的唯一差别就是WHERE过滤行，HAVING过滤分组

select verd_id, count(*) as num_prods from products GROUP BY verd_id HAVING count(*)>2;

select verd_id, count(*) as num_prods from products GROUP BY verd_id HAVING verd_id = 1003;

(4)ORDER BY

select cust_name,cust_address,cust_zip from customers;

对cust_zip排序

select cust_name,cust_address,cust_zip from customers ORDER BY cust_zip;

对多列进行排序

select cust_name,cust_address,cust_zip from customers ORDER BY cust_address,cust_zip;

指定排序方向：默认升序(ASC)，为了进行降序排序，必须指定DESC关键字

select cust_name,cust_address,cust_zip from customers ORDER BY cust_zip DESC;

(5)LIMIT

LIMIT关键子对输出的行数限制，指定其实行和行数

select cust_name,cust_address,cust_zip from customers ORDER BY cust_zip DESC LIMIT 2,4;

(6)综合使用

select verd_id, count(*) as num_prods from products GROUP BY verd_id HAVING count(*) > 0 ORDER BY verd_id DESC LIMIT 1,3;