mysql基础(2)--查询语句中 分组 聚合 过滤 排序 限制条数 详解

1. select语句

1. 检索单个列
从 products表中检索一个名为prod_name的列

    SELECT prod_name FROM products;

2. 检索people表多个列

    SELECT name, gender, age FROM people;

3. 检索products表所有列(使用*通配符)

    SELECT * FROM products;

4. 检索products表中产品的所有供应商ID(并去重)

    SELECT DISTINCT vend_id FROM products;
    去重使用DISTINCT关键字,直接放在列名的前面(注意:DISTINCT关键字应用于所有的列而不仅是前置它的列)

5. 限制返回行数

    从products表查询prod_name显示前10条
    SELECT prod_name FROM products LIMIT 10;(如果结果小于10条,则有多少返回多少)

2. LIMIT(永远放在SQL语句的最后写)

1. 作用：限制显示查询记录的条数
2. 用法
　　1、limit n–>显示n条记录
　　2、limit m,n
　　　m–>表示从第m+1(因为计算机计数从0开始，所以m开始显示为m+1)条记录开始显示，显示n条
　　　limit 2,3：第3、4、5条记录
3. LIMIT还可以当做 分页 来使用
　每页显示5条记录，显示第四页的内容
　第一页：limit 0,5 #1,2,3,4,5
　第二页：limit 5,5 #6,7,8,9,10
　第三页：limit 10,5 #11,12,13,14,15
　第四页：limit 15,5 #16,17,18,19,20
　每页显示n条记录，显示第m页：limit (m-1)*n,n

3. ORDER BY排序

在不使用ORDER BY的情况下,数据一般将以它在底层表中出现的顺序显示,一般为数据最终添加进表的顺序但是,如果数据后来进行过更新或删除,则此顺序将会受到MySQL重用回收存储空间的影响.因此,如果不明确控制的话,不能(也不应该)依赖该排序顺序
关系数据库设计理论认为,如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义。
为了明确地排序用 SELECT 语句检索出的数据,可使用 ORDER BY 子句。ORDER BY 子句取一个或多个列的名字,据此对输出进行排序.
1. 查询products表中的prod_name字段并按照prod_name排序

    SELECT prod_name from products ORDER BY prod_name;

2. 按多个列排序

    SELECT * FROM products ORDER BY prod_price, prod_name;

说明:仅在多个行具有相同的 prod_price 值时才对产品按 prod_name 进行排序。如果 prod_price 列中所有的值都是唯一的,则不会按 prod_name 排序。

说明:当不指定排序方式的时候,会默认按照升序排列ASC(ASCENDING),当我们想要倒叙排列时,只需要在要排列的列名后面加上DESC即可**

3. 按照prod_price倒序排列,prod_name升序排列

SELECT * FROM products ORDER BY prod_price DESC, prod_name;

4. WHERE子句(在表名之后给出)

1. 查询prod_price值为6的行
SELECT prod_name from products WHERE prod_pjrice = 6;
2. 数值比较：= != > >= < <=
3. 字符比较：= !=
4. 范围内比较
　　1. between 值1 and 值2
　　2. where 字段名 in(值1,值2…)
　　3. where 字段名 not in(值1,值2…)
5. 匹配空、非空
　　1. 空：where name is null
　　2. 非空：where name is not null
判断null只能用is,不能用==
6. 逻辑比较 and(两个或多个条件同时成立) or(任意一个条件成立即可)
这里and和or有一个有趣的问题
我们想要列出价格10元(含)以上且由1002或1003制作的所有产品
我们这样写sql语句(***错误 ***)

        SELECT prod_name, prod_price FROM produces WHERE vend_id=1002 OR wend_id=1003 AND prod_price>=10;

事实证明返回的结果并不是我们想要的(这里可以自己去试),这是因为在SQL处理OR操作符之前,会优先处理AND操作符,上述语句在sql中会被解释为由供应商 1003 制造的任何价格为10美元(含)以上的产品,或者由供应商 1002 制造的任何产品,而不管其价格如何
(正确):

        SELECT prod_name, prod_price FROM produces WHERE (vend_id=1002 OR wend_id=1003) AND prod_price>=10;

建议:任何时候使用具有 AND 和 OR 操作符的 WHERE 子句,都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序,即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处,它能消除歧义
7. 模糊比较
　　1. where 字段名 like 表达式
　　2. _:匹配单个字符
　　3. %：匹配0到多个字符
　　例: SELECT prod_id, prod_name from products WHERE prod_name LIKE ‘%san%’;
　　　　　匹配prod_name中包含san的值
注意: % 通配符可以匹配任何东西,但有一个例外,即 NULL 。即使是 WHERE prod_name LIKE ‘%’；也不能匹配用值 NULL 作为产品名的行
通配符搜索的处理一般要比其他搜索所花时间更长
通配符使用技巧

• 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符
• 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处,那样将全表扫描,搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据

5. 聚合函数

• avg(字段名)：求该字段平均值
  SELECT AVG(prod_price) AS avg_price FROM products;
  返回products表中的所有产品的平均价格,avg_price是一个别名
• sum(字段名)：求和
• max(字段名)：最大值
• min(字段名)：最小值
• count(字段名)：统计该字段记录的个数
  SELECT COUNT(cust_email) AS num_cust FROM customers;
  返回具有电子邮件地址的用户数
  注意: 如果指定列名,则指定列的值为空的行被 COUNT()函数忽略,但如果 COUNT() 函数中用的是星号( * ),则不忽略。

6. 数据分组(GROUP BY)

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

SELECT 语句指定了两个列, vend_id 包含产品供应商的ID,num_prods 为计算字段(用 COUNT(*) 函数建立)。 GROUP BY 子句指示MySQL按 vend_id 排序并分组数据。这导致对每个 vend_id 而不是整个表计算 num_prods 一次
GROUP BY的一些重要的规定

• GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制。
• 如果在 GROUP BY 子句中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
• GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在 SELECT 中使用表达式,则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外, SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
• 如果分组列中具有 NULL 值,则 NULL 将作为一个分组返回。如果列中有多行 NULL 值,它们将分为一组。
• GROUP BY 子句必须出现在 WHERE 子句之后, ORDER BY 子句之前。

7. 过滤分组(HAVING)

• WHERE 过滤行,而 HAVING 过滤分组

• WHERE 在数据分组前进行过滤, HAVING 在数据分组后进行过滤

  列出至少有两个订单的所有顾客

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

总结

sql查询各语法顺序

SELECT ...聚合函数 FROM 表名 WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...;

先使用WHERE查询,然后分组GROUP BY,然后聚合, 然后过滤HAVING ,然后排序ORDER BY,最后限制查询条数LIMIT;