本文是个人学习笔记学习素材参考《SQL必知必会第5版》仅用于个人学习记录非商用。样例数据、练习思路来自该书。八.使用函数处理数据8.1函数SQL函数不可移植意味着为特定SQL实现编写的代码在其他实现中可能不能用DBMS函数的差异函数语法提取字符串的组成部分DB2,Oracle,PostgreSQL,和SQLite使用SUBSTR()MariaDB,MYSQL和SQL Server使用SUBSTRING()数据类型转换Oracle使用多个函数每种类型的转换有一种函数DB2和PostgreSQL使用CAST()MariaDB,MYSQL和SQL Server使用CONVERT()取当前日期DB2和PostgreSQL使用CURRENT_DATAMariaDB和MYSQL使用CURDATE()Oracle使用SYSDATASQL Server使用GETDATA()SQLite使用DATA()8.2使用函数8.2.1文本处理函数例UPPER()将文本转换为大写SELECT VEND_NAME,UPPER(VEND_NAME)AS VEND_NAME_UPCASEFROM VENDORSORDER BY VEND_NAME;常用的文本处理函数函数说明LEFT()返回字符串左边的字符LENGTH()返回字符串的长度LOWER()将字符串转换为小写LTRIM()去掉字符串左边的空格RIGHT()返回字符串右边的字符RTRIM()去掉字符串右边的空格SUBSTR()或SUBSTRING()提取字符串的组成部分SOUNDEX()返回字符串的SOUNDEX值UPPER()将字符串转换为大写SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法例有个顾客联系名输入错误成Michelle Green正确的是Michael Green在不知道错误输入的联系名的情况下如何找出SELECT CUST_NAME,CUST_CONTACTFROM CUSTOMERSWHERESOUNDEX(CUST_CONTACT)SOUNDEX(Michael Green);8.2.2日期和时间处理函数在SQL Server中用DATEPART(),这个函数有两个参数分别是返回的成分和从中返回成分的日期在PostgreSQL中用DATE_PART()在Oracle中用EXTRACT()、BETWEENto_date()函数用来将两个字符串转换为日期DB2MYSQLMariaDB用户可使用名为YEAR()的函数从日期中提取年份SELECT ORDER_NUMFROM ORDERSWHEREYEAR(ORDER_DATE) 2020;8.2.3数值处理函数常用数值处理函数函数说明ABS()返回一个数的绝对值COS()返回一个角度的函数EXP()返回一个数的指数值PI()返回圆周率Π的值SIN()返回一个角度的正弦SQRT()返回一个数的平方根TAN()返回一个角度的正切九.汇总数据9.1聚集函数聚集函数:对某些行运行的函数,计算并返回一个值SQL聚集函数函数说明AVG()返回某列的平均值COUNT()返回某列的行数MAX()返回某列的最大值MIN()返回某列的最小值SUM()返回某列值之和9.1.1 AVG()函数AVG()通过对表中行数计数并计算其列值之和,求得该列的平均值例:返回Product表中所有产品的平均值SELECTAVG(PROD_PRICE)AS AVG_PRICEFROM PRODUCTS;AVG()也可以用来确定特定列或行的平均值SELECTAVG(PROD_PRICE)AS AVG_PRICEFROM PRODUCTSWHERE VEND_ID DLL01;注意:AVG()只用于单个列,想获得多个列的平均值必须使用多个AVG()函数,AVG()函数忽略列值为null的行9.1.2 COUNT()函数COUNT()确定表中行的数目或符合特定条件的行的数目COUNT(*)对表中所有行的数目进行计数COUNT(column)对特定列中具有值的行进行计数,忽略null值例:返回customers表中顾客的总数SELECTCOUNT(*)AS NUM_CUSTFROM CUSTOMERS;例:只对具有电子邮件地址的客户计数SELECTCOUNT(CUST_EMAIL)AS NUM_CUSTFROM CUSTOMERS;如果指定列名,则COUNT()函数会忽略指定列的值为NULL的行如果用的是COUNT(*)则不忽略9.1.3 MAX()函数MAX()返回指定列中的最大值,MAX()要求指定列名SELECTMAX(PROD_PRICE)AS MAX_PRICEFROM PRODUCTS;提示:在用于非数值的文本数据时,MAX()返回按改列排序后的最后一行,MAX()函数忽略列值为NULL的行9.1.4 MIN()函数MIN()返回指定列的最小值,要求指定列名SELECTMIN(PROD_PRICE)AS MIN_PRICEFROM PRODUCTS;提示:在用于非数值的文本数据时,MIN()返回该列排序后最前面的行MIN()函数忽略列值为NULL的行9.1.5 SUN()函数SUM()用来返回指定列值的和SELECTSUM(QUANTITY)AS ITEM_ORDEREDFROM ORDERITEMSWHERE ORDER_NUM 20005;提示:所有聚集函数都可用来执行多个列上的计算SUM()函数忽略列值为NULL的行9.2 聚集不同值以上5个聚集函数都可以如下使用对所有行执行计算,指定ALL参数或不指定参数(因为ALL是默认行为)只包含不同值,指定DISTINCT参数例:使用AVG()函数返回特定供应商提供的产品的平均价格SELECT AVG(DISTINCTPROD_PRICE) AS AVG_PRICEFROM PRODUCTSWHERE VEND_ID DLL01;注意:DISTINCT不能用于COUNT(*),DISTINCT必须使用列名,不能用于计算或表达式9.3 组合聚集函数SELECT COUNT(*) AS NUM_ITEMS,MIN(PROD_PRICE) AS PRICE_MIN,MAX(PROD_PRICE) AS PRICE_MAX,AVG(PROD_PRICE) AS PRICE_AVGFROM PRODUCTS;#别忘了逗号,
本文是个人学习笔记学习素材参考《SQL必知必会第5版》仅用于个人学习记录非商用。样例数据、练习思路来自该书。八.使用函数处理数据8.1函数SQL函数不可移植意味着为特定SQL实现编写的代码在其他实现中可能不能用DBMS函数的差异函数语法提取字符串的组成部分DB2,Oracle,PostgreSQL,和SQLite使用SUBSTR()MariaDB,MYSQL和SQL Server使用SUBSTRING()数据类型转换Oracle使用多个函数每种类型的转换有一种函数DB2和PostgreSQL使用CAST()MariaDB,MYSQL和SQL Server使用CONVERT()取当前日期DB2和PostgreSQL使用CURRENT_DATAMariaDB和MYSQL使用CURDATE()Oracle使用SYSDATASQL Server使用GETDATA()SQLite使用DATA()8.2使用函数8.2.1文本处理函数例UPPER()将文本转换为大写SELECT VEND_NAME,UPPER(VEND_NAME)AS VEND_NAME_UPCASEFROM VENDORSORDER BY VEND_NAME;常用的文本处理函数函数说明LEFT()返回字符串左边的字符LENGTH()返回字符串的长度LOWER()将字符串转换为小写LTRIM()去掉字符串左边的空格RIGHT()返回字符串右边的字符RTRIM()去掉字符串右边的空格SUBSTR()或SUBSTRING()提取字符串的组成部分SOUNDEX()返回字符串的SOUNDEX值UPPER()将字符串转换为大写SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法例有个顾客联系名输入错误成Michelle Green正确的是Michael Green在不知道错误输入的联系名的情况下如何找出SELECT CUST_NAME,CUST_CONTACTFROM CUSTOMERSWHERESOUNDEX(CUST_CONTACT)SOUNDEX(Michael Green);8.2.2日期和时间处理函数在SQL Server中用DATEPART(),这个函数有两个参数分别是返回的成分和从中返回成分的日期在PostgreSQL中用DATE_PART()在Oracle中用EXTRACT()、BETWEENto_date()函数用来将两个字符串转换为日期DB2MYSQLMariaDB用户可使用名为YEAR()的函数从日期中提取年份SELECT ORDER_NUMFROM ORDERSWHEREYEAR(ORDER_DATE) 2020;8.2.3数值处理函数常用数值处理函数函数说明ABS()返回一个数的绝对值COS()返回一个角度的函数EXP()返回一个数的指数值PI()返回圆周率Π的值SIN()返回一个角度的正弦SQRT()返回一个数的平方根TAN()返回一个角度的正切九.汇总数据9.1聚集函数聚集函数:对某些行运行的函数,计算并返回一个值SQL聚集函数函数说明AVG()返回某列的平均值COUNT()返回某列的行数MAX()返回某列的最大值MIN()返回某列的最小值SUM()返回某列值之和9.1.1 AVG()函数AVG()通过对表中行数计数并计算其列值之和,求得该列的平均值例:返回Product表中所有产品的平均值SELECTAVG(PROD_PRICE)AS AVG_PRICEFROM PRODUCTS;AVG()也可以用来确定特定列或行的平均值SELECTAVG(PROD_PRICE)AS AVG_PRICEFROM PRODUCTSWHERE VEND_ID DLL01;注意:AVG()只用于单个列,想获得多个列的平均值必须使用多个AVG()函数,AVG()函数忽略列值为null的行9.1.2 COUNT()函数COUNT()确定表中行的数目或符合特定条件的行的数目COUNT(*)对表中所有行的数目进行计数COUNT(column)对特定列中具有值的行进行计数,忽略null值例:返回customers表中顾客的总数SELECTCOUNT(*)AS NUM_CUSTFROM CUSTOMERS;例:只对具有电子邮件地址的客户计数SELECTCOUNT(CUST_EMAIL)AS NUM_CUSTFROM CUSTOMERS;如果指定列名,则COUNT()函数会忽略指定列的值为NULL的行如果用的是COUNT(*)则不忽略9.1.3 MAX()函数MAX()返回指定列中的最大值,MAX()要求指定列名SELECTMAX(PROD_PRICE)AS MAX_PRICEFROM PRODUCTS;提示:在用于非数值的文本数据时,MAX()返回按改列排序后的最后一行,MAX()函数忽略列值为NULL的行9.1.4 MIN()函数MIN()返回指定列的最小值,要求指定列名SELECTMIN(PROD_PRICE)AS MIN_PRICEFROM PRODUCTS;提示:在用于非数值的文本数据时,MIN()返回该列排序后最前面的行MIN()函数忽略列值为NULL的行9.1.5 SUN()函数SUM()用来返回指定列值的和SELECTSUM(QUANTITY)AS ITEM_ORDEREDFROM ORDERITEMSWHERE ORDER_NUM 20005;提示:所有聚集函数都可用来执行多个列上的计算SUM()函数忽略列值为NULL的行9.2 聚集不同值以上5个聚集函数都可以如下使用对所有行执行计算,指定ALL参数或不指定参数(因为ALL是默认行为)只包含不同值,指定DISTINCT参数例:使用AVG()函数返回特定供应商提供的产品的平均价格SELECT AVG(DISTINCTPROD_PRICE) AS AVG_PRICEFROM PRODUCTSWHERE VEND_ID DLL01;注意:DISTINCT不能用于COUNT(*),DISTINCT必须使用列名,不能用于计算或表达式9.3 组合聚集函数SELECT COUNT(*) AS NUM_ITEMS,MIN(PROD_PRICE) AS PRICE_MIN,MAX(PROD_PRICE) AS PRICE_MAX,AVG(PROD_PRICE) AS PRICE_AVGFROM PRODUCTS;#别忘了逗号,
