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

最新推荐文章于 2024-08-21 09:09:31 发布

爱学习的狮王

最新推荐文章于 2024-08-21 09:09:31 发布

阅读量1.8k

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/study_in/article/details/96370739

版权

mysql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

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;