当前位置: 澳门新濠3559 > 数据库 > 正文

这一系列的文章是《SQL必知必会》第四版的读书

时间:2019-12-27 19:58来源:数据库
聚集函数 1.AVG()函数 输入: SELECT AVG( prod_price ) AS avg_price FROM Products 输出: 警告:只用于单个列 AVG()只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出。为了获得多个

聚集函数

澳门新濠3559 1

1.AVG()函数

输入:SELECT AVG(prod_price) AS avg_price FROM Products

输出:

澳门新濠3559 2

警告:只用于单个列
AVG()只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出。为了获得多个列的平均值,必须使用多个AVG()函数。
说明:NULL值
AVG()函数忽略列值为NULL的行。

2.**COUNT()**函数

输入:SELECT COUNT(*) AS num_cust FROM Customers

输出:

澳门新濠3559 3

说明:NULL值
如果指定列名,则COUNT()函数会忽略指定列的值为空的行,但如果COUNT()函数中用的是星号(*),则不忽略。

3.**MAX()**函数

MAX()返回指定列中的最大值。 

输入:SELECT MAX(prod_price) AS max_price FROM Products

输出:

澳门新濠3559 4

提示:对非数值数据使用MAX()
虽然MAX()一般用来找出最大的数值或日期值,但许多(并非所有)DBMS允许将它用来返回任意列中的最大值,包括返回文本列中的最大值。在用于文本数据时,MAX()返回按该列排序后的最后一行。
说明:NULL值
MAX()函数忽略列值为NULL的行。

4.MIN()函数

MIN()的功能正好与MAX()功能相反,它返回指定列的最小值。 

5.**SUM()**函数

SUM()用来返回指定列值的和(总计)。 

注:这一系列的文章是《SQL必知必会》第四版的读书笔记。

组合聚集函数

输入:

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;

输出:

澳门新濠3559 5

7.创建计算字段

什么是计算字段,怎么创建计算字段,以及如何从应用程序中使用别名引用它们。

分组数据

1.数据分组

输入:

SELECT COUNT(*) AS num_prods
FROM Products
WHERE vend_id = 'DLL01';

输出:

澳门新濠3559 6

2.创建分组

利用GROUP BY建立。

输入:

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

输出:

澳门新濠3559 7

在使用GROUP BY子句前,需要知道一些重要的规定

  • GROUP BY子句可以包含任意数目的列,因而可以对分组进行嵌套,更细致地进行数据分组。
  • 如果在GROUP BY子句中嵌套了分组,数据将在最后指定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
  • GROUP BY子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  • 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型(如文本或备注型字段)。
  • 除聚集计算语句外,SELECT语句中的每一列都必须在GROUP BY子句中给出。
  • 如果分组列中包含具有NULL值的行,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。

3.过滤分组

输入:

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

输出:

澳门新濠3559 8

这条SELECT语句的前三行类似于上面的语句。最后一行增加了HAVING子句,它过滤COUNT(*) >= 2(两个以上订单)的那些分组。

为了更好地理解,来看下面的例子,它列出具有两个以上产品且其价格大于等于4的供应商: 

澳门新濠3559 9

4.分组和排序

GROUP BY和ORDER BY经常完成相同的工作,但它们非常不同,理解这一点很重要。表10-1汇总了它们之间的差别。

澳门新濠3559 10

实例:

输入:

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3;

输出:

澳门新濠3559 11

对比:

输入:

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

输出:

澳门新濠3559 12

在这个例子中,使用GROUP BY子句按订单号(order_num列)分组数据,以便COUNT(*)函数能够返回每个订单中的物品数目。HAVING子句过滤数据,使得只返回包含三个或更多物品的订单。最后,用ORDER BY子句排序输出。

作者:今孝
出处:
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接。

觉得好就点个推荐把!

7.1 计算字段

字段:基本上与列的意思相同,经常互换使用。术语字段通常与计算字段一起使用。

计算字段是运行时在SELECT语句内创建的,并不实际存在于数据库表中。

只有数据库直到SELECT语句中哪些列时实际列,哪些列时计算字段。从客户端来看,实际列和计算字段返回的方式相同。

7.2 拼接字段

拼接:将值联结到一起构成单个值。

输入:

SELECT Concat(vend_name,' (',vend_country,')')

FROM Vendors

ORDER BY vend_name;

上面输出的列没有名字,不能用于客户端应用中。为了解决这个问题,SQL支持列别名。

别名:一个字段或值得替换名。别名使用AS关键字赋予。

输入:

SELECT Concat(vend_name,' (',vend_country,')')

AS vend_title

FROM Vendors

ORDER BY vend_name;

7.3 执行算术计算

计算字段另一常见用途是对检索出的数据进行算术计算。

输入:

SELECT prod_id,

quantity,

item_price,

quantity*item_price AS expanded_price

FROM OrderItems

WHERE order_num=20008;

8.使用数据处理函数

介绍什么是函数,DBMS支持何种函数,如何使用这些函数,以及SQL函数的使用可能带来的问题。

8.1 函数

每一个DBMS都有特定的函数,即使功能一样的函数,但在不同的DBMS中名称和语法可能极其不同。

SQL函数不是可移植的。

8.2 使用函数

8.2.1 文本处理函数

UPPER()函数的使用。

输入:

SELECT vend_name,UPPER(vend_name) AS vend_name_upcase

FROM Vendors

ORDER BY vend_name;

SOUNDEX()函数,将任何文本串转换为其语音表示的字母数字模式的算法。对字符串进行发音比较而不是字母比较。

输入:

SELECT cust_name,cust_contact

FROM Customers

WHERE SOUNDEX(cust_contact)=SOUNDEX('Michael Green');

8.2.2 日期和时间处理函数

以MySQL中的YEAR()函数为例。

输入:

SELECT order_num

FROM Orders

WHERE YEAR(order_date)=2012;

8.2.3 数值处理函数

在主要的DBMS中,数值处理函数时最一致、最统一的函数。

常用的数值处理函数:

ABS(),COS(),EXP(),PI(),SIN(),SQRT(),TAN()

9.汇总数据

介绍什么是SQL的聚集函数,利用它们汇总表的数据。

9.1 聚集函数

聚集函数:对某些行运行的函数,计算并返回一个值。

SQL聚集函数:AVG(),COUNT(),MAX(),MIN(),SUM()

9.1.1 AVG()函数

AVG()函数通过对表中行数计数并计算其列值之和,求得该列的平均值。

输入:

SELECT AVG(prod_price) AS avg_price

FROM Products;

输入:

SELECT AVG(prod_price) AS avg_price

FROM Products

WHERE vend_id='DLL01';

注意:AVG()只用于单个列。AVG()函数忽略值为NULL的行。

9.1.2 COUNT()函数

COUNT()函数进行计数,有两种使用方式:

(1)COUNT(*):对表中行的数目进行计数,不管包含的是NULL值还是非NULL值。

(2)COUNT(column):对特定列中具有值得行进行计数,忽略NULL值。

输入:

SELECT COUNT(*) AS num_cust

FROM Customers;

输入:

SELECT COUNT(cust_email) AS num_cust

FROM Customers;

9.1.3 MAX()函数

MAX()函数要求指定列名,并且忽略列值为NULL的行。

输入:

SELECT MAX(prod_price) AS max_price

FROM Products;

9.1.4 MIN()函数

与MAX()函数类似。

9.1.5 SUM()函数

SUM()函数用来返回指定列值的和。忽略值为NULL的行。

输入:

SELECT SUM(quantity) AS item_ordered

FROM OrderItems

WHERE order_num=20005;

SUM()函数也能用来合计计算值。

输入:

SELECT SUM(item_price*quantity) AS total_price

FROM OrderItems

WHERE order_num=20005;

9.2 聚集不同值

以上五个聚集函数可以如下使用:

(1)对所有行执行计算,指定ALL参数或不指定参数(因为默认是ALL参数)。

(2)只包含不同的值,指定DISTINCT参数。

输入:

SELECT AVG(DISTINCT prod_price) AS avg_price

FROM Products

WHERE vend_id='DLL01';

DISTINCT只能用于COUNT(),不能用于COUNT(*),必须使用列名,不能用于计算或表达式。

9.3 组合聚集函数

聚集函数能够组合起来使用。

输入:

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;

10.分组数据

介绍如何分组数据,以便汇总表内容的子集。涉及的新SELECT子句:GROUP BY子句,HAVING子句

10.1 数据分组

使用分组可以将数据分为多个逻辑组,对每个组进行聚集计算。

10.2 创建分组

分组是使用GROUP BY子句建立的。

输入:

SELECT vend_id,COUNT(*) AS num_prods

FROM Products

GROUP BY vend_id;

使用GROUP BY子句需要知道的一些重要规定:

(1)GROUP BY子句可以包含任意数目的列,因而可以对分组进行嵌套,更加细致的进行数据分组。

(2)如果GROUP BY子句嵌套了分组,数据将在最后指定的分组上进行汇总。即,在建立分组时,指定的所有列都一起计算。

(3)GROUP BY子句中列出的每一列都必须是检索列或有效的表达式(不能是聚集函数)。如果在SELECT子句中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

(4)大多数SQL实现不允许GROUP BY子句列带有长度可变的数据类型。

(5)除聚集计算语句外,SELECT语句中的每一列都必须在GROUP BY子句中给出。

(6)如果分组列中包含具有NULL值的行,则NULL将作为一个分组返回。如果列中有多个行是NULL值,它们将分为一组。

(7)GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前

10.3 过滤分组

WHERE子句:过滤指定的是行。WHERE子句没有分组的概念。

HAVING子句:过滤的是分组。

输入:

SELECT cust_id,COUNT(*) AS orders

FROM Orders

GROUP BY cust_id

HAVING COUNT(*)>=2;

另一种理解方法:WHERE子句在数据分组前过滤,HAVING子句在数据分组后进行过滤。

同时使用WHERE子句和HAVING子句:

输入:

SELECT vend_id,COUNT(*) AS num_prods

FROM Products

WHERE prod_price>=4

GROUP BY vend_id

HAVING COUNT(*)>=2;

10.4 分组和排序

GROUP BY:对行分组,但输出可能不是分组的顺序;只可能使用选择列或表达式列,而且必须使用每个选择列表达式;如果与聚集函数一起使用列(或表达式),则必须使用。

ORDER BY:对产生的输出排序;任意列都可以使用;不一定需要。

输入:

SELECT order_num,COUNT(*) AS items

FROM OrderItems

GROUP BY order_num

HAVING COUNT(*)>=3

ORDER BY items,order_num;

10.5 SELECT子句的顺序

SELECT:必须使用

FROM:仅在从表选择数据时使用

WHERE:非必须

GROUP BY:仅在按组计算聚集时使用

HAVING:非必须

ORDER BY:非必须

11.使用子查询

介绍什么是子查询,使用子查询。子查询常用于WHERE子句的IN操作符中,以及用来填充计算列。

11.1 子查询

子查询:嵌套在其他查询中的查询。

MySQL对子查询是从4.1版本开始的。

11.2 利用子查询进行过滤

子查询总是从内向外处理。对能嵌套的子查询的数目没有限制,但在实际使用时,出于性能的限制,不能嵌套太多的子查询。

作为子查询的SELECT语句只能查询单个列。

列出订购物品RGAN01的所有顾客。

输入:

SELECT cust_name,cust_contact

FROM Customers

WHERE cust_id IN (SELECT cust_id

                                 FROM Orders

                                 WHERE order_澳门新濠3559,num IN (SELECT order_num

                                                                        FROM OrderItems

                                                                        WHERE prod_id='RGAN01'));

11.3 作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。

需要显示Customers表中每个顾客的订单总数。

输入:

SELECT cust_name,

               cust_state,

               (SELECT COUNT(*)

                FROM Orders

                WHERE Orders.cust_id=Customers.cust_id) AS orders

FROM Customers

ORDER BY cust_name;

注意使用完全限定列名来避免歧义。

12.联结表

介绍什么是联结,为什么使用联结,如何编写使用联结的SELECT语句。

12.1 联结

SQL最强大的功能之一就是能在数据查询的执行中联结表。

12.1.1 关系表

关系表的设计就是将信息分解成多个表,一类数据一个表。各表通过某些共同的值互相关联,这样可以有效的存储,方便的管理。关系数据库的可伸缩性远比非关系数据库要好。

12.1.2 为什么使用联结

联结是一种机制,用来在一条SELECT语句中关联表。使用特殊的语法,可以联结多个表返回一组输出。

12.2 创建联结

创建联结非常简单,指定要联结的所有表和关联它们的方式即可。

用WHERE子句联结。

输入:

SELECT vend_name,prod_name,prod_price

FROM Vendors,Products

WHERE Vendors.vend_id=Products.vend_id;

12.2.1 WHERE子句的重要性

联结两个表时,实际要做的是将第一个表的每一行与第二个表的每一行配对。WHERE子句作为过滤条件,只包含满足给定条件的行。没有WHERE子句的话,两个表联结的结果将是一个笛卡尔积

12.2.2 内联结

等值联结:基于两个表之间的相等测试,也称为内联结。

关键字:INNER JOIN,ON

输入:

SELECT vend_name,prod_name,prod_price

FROM Vendors INNER JOIN Products

 ON Vendors.vend_id=Products.vend_id;

12.2.3 联结多个表

显示订单20007中的物品。

输入:

SELECT prod_name,vend_name,prod_price,quantity

FROM OrderItems,Products,Vendors

WHERE Products.vend_id=Vendors.vend_id

 AND OrderItems.prod_id=Products.prod_id

 AND order_num=20007;

13.创建高级联结

讲解另一些联结,介绍如何使用表别名,如何对被联结的表使用聚集函数。

13.1 使用表别名

使用表别名的两个理由:

(1)缩短SQL语句

(2)允许在一条SELECT语句中多次使用相同的表

输入:

SELECT cust_name,cust_contact

FROM Customers AS C,Orders AS O,OrderItems AS OI

WHERE C.cust_id=O.cust_id

 AND OI.order_num=O.order_num

 AND prod_id='RGAN01';

13.2 使用不同类型的联结

其他的三种联结:自联结,自然联结,外联结。

13.2.1 自联结

要给与Jim Jones同一公司的所有顾客发送一封邮件。

输入:

(1)使用子查询

SELECT cust_id,cust_name,cust_contact

FROM Customers

WHERE cust_name=(SELECT cust_name

FROM Customers

WHERE cust_contact='Jim Jones');

(2)使用自联结

SELECT c1.cust_id,c1.cust_name,c1.cust_contact

FROM Customers AS c1,Customers AS c2

WHERE c1.cust_name=c2.cust_name

 AND c2.cust_contact='Jim Jones';

用自联结而不用子查询:许多DBMS处理联结远比处理子查询快的多,应该试一下两种不同的方法,以确定哪一种性能更好。

13.2.2 自然联结

自然联结排除多次出现,使每一列只返回一次。系统不完成这项工作,由自己完成。自然联结要求只能选择那些唯一的列,一般通过对一个表使用通配符(SELECT *),而对其他表的列使用明确的子集来完成。

输入:

SELECT C.*,O.order_num,O.order_date,OI.prod_id,OI.quantity,OI.item_price

FROM Customers AS C,Orders AS O,OrderItems AS OI

WHERE C.cust_id=O.cust_id

 AND OI.order_num=O.order_num

 AND prod_id='RGAN01';

13.2.3 外联结

外联结:包含了那些在相关表中没有关联的行。

关键字:OUTER JOIN,ON。使用RIGHT或LEFT关键字指定包括其所有行的表。

输入:

SELECT Customers.cust_id,Orders.order_num

FROM Customers LEFT OUTER JOIN Orders

ON Customers.cust_id=Orders.cust_id;

注意:MySQL不支持FULL OUTER JOIN 的语法。

13.3 使用带聚集函数的联结

检索所有顾客及每个顾客所下的订单数。

输入:

SELECT Customers.cust_id,COUNT(Orders.order_num) AS num_ord

FROM Customers INNER JOIN Orders

ON Customers.cust_id=Orders.cust_id

GROUP BY Customers.cust_id;

13.4 使用联结和联结条件

(1)注意所使用的联结类型。

(2)关于确切的联结语法,应当查看具体的文档。

(3)保证使用正确的联结条件。

(4)应该总是提供联结条件。

(5)在一个联结中可以包含多个表,甚至对每个联结都可以采用不同的联结类型。在一起测试它们之前,先分别测试每个联结。

编辑:数据库 本文来源:这一系列的文章是《SQL必知必会》第四版的读书

关键词: