快速网站优化服务,系统网站,网页制作三剑客是指什么,郑州网站建设rwxwl文章目录 8.1 函数8.2 使用函数8.2.1 文本处理函数8.2.2 日期和时间处理函数8.2.3 数值处理函数 表8-3 常用数值处理函数 这一课介绍什么是函数#xff0c;DBMS支持何种函数#xff0c;以及如何使用这些函数#xff1b;还将讲解为什么SQL函数的使用可能会带来问题。 8.1 函数… 文章目录 8.1 函数8.2 使用函数8.2.1 文本处理函数8.2.2 日期和时间处理函数8.2.3 数值处理函数 表8-3 常用数值处理函数 这一课介绍什么是函数DBMS支持何种函数以及如何使用这些函数还将讲解为什么SQL函数的使用可能会带来问题。 8.1 函数
与大多数其他计算机语言一样SQL也可以用函数来处理数据。函数一般是在数据上执行的为数据的转换和处理提供了方便。 前一课中用来去掉字符串尾的空格的RTRIM()就是一个函数。 函数带来的问题 在学习这一课并进行实践之前你应该了解使用SQL函数所存在的问题。 与几乎所有DBMS都等同地支持SQL语句如SELECT不同每一个DBMS都有特定的函数。事实上只有少数几个函数被所有主要的DBMS等同地支持。虽然所有类型的函数一般都可以在每个DBMS中使用但各个函数的名称和语法可能极其不同。为了说明可能存在的问题表8-1列出了3个常用的函数及其在各个DBMS中的语法 表8-1 DBMS函数的差异
函 数语 法提取字符串的组成部分Access使用MID()DB2、Oracle、PostgreSQL和SQLite使用SUBSTR()MySQL和SQL Server使用SUBSTRING()数据类型转换Access和Oracle使用多个函数每种类型的转换有一个函数DB2和PostgreSQL使用CAST()MariaDB、MySQL和SQL Server使用CONVERT()取当前日期Access使用NOW()DB2和PostgreSQL使用CURRENT_DATEMariaDB和MySQL使用CURDATE()Oracle使用SYSDATESQL Server使用GETDATE()SQLite使用DATE()
可以看到与SQL语句不一样SQL函数不是可移植的。这表示为特定SQL实现编写的代码在其他实现中可能不正常。 可移植portable 所编写的代码可以在多个系统上运行。 为了代码的可移植许多SQL程序员不赞成使用特定于实现的功能。虽然这样做很有好处但有的时候并不利于应用程序的性能。如果不使用这些函数编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS可以非常有效完成的工作。 提示是否应该使用函数 现在你面临是否应该使用函数的选择。决定权在你使用或是不使用也没有对错之分。如果你决定使用函数应该保证做好代码注释以便以后你或其他人能确切地知道所编写的SQL代码的含义。 8.2 使用函数
大多数SQL实现支持以下类型的函数。
用于处理文本字符串如删除或填充值转换值为大写或小写的文本函数。用于在数值数据上进行算术操作如返回绝对值进行代数运算的数值函数。用于处理日期和时间值并从这些值中提取特定成分如返回两个日期之差检查日期有效性的日期和时间函数。返回DBMS正使用的特殊信息如返回用户登录信息的系统函数。 我们在上一课看到函数用作SELECT语句的列表成分但函数的作用不仅于此。它还可以作为SELECT语句的其他成分如在WHERE子句中使用在其他SQL语句中使用等后面会做更多的介绍。
8.2.1 文本处理函数
在上一课我们已经看过一个文本处理函数的例子其中使用RTRIM()函数来去除列值右边的空格。下面是另一个例子这次使用的是UPPER()函数 输入▼
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name; 输出▼
vend_namevend_name_upcaseBear EmporiumBEAR EMPORIUMBears R UsBEARS R USDoll House Inc.DOLL HOUSE INC.Fun and GamesFUN AND GAMESFurball Inc.FURBALL INC.Jouets et oursJOUETS ET OURS
分析▼ 可以看到UPPER()将文本转换为大写因此本例子中每个供应商都列出两次第一次为Vendors表中存储的值第二次作为列vend_name_upcase转换为大写。 表8-2列出了一些常用的文本处理函数。 表8-2 常用的文本处理函数
函 数说 明LEFT()或使用子字符串函数返回字符串左边的字符LENGTH()也使用DATALENGTH()或LEN()返回字符串的长度LOWER()Access使用LCASE()将字符串转换为小写LTRIM()去掉字符串左边的空格RIGHT()或使用子字符串函数返回字符串右边的字符RTRIM()去掉字符串右边的空格SOUNDEX()返回字符串的SOUNDEX值UPPER()Access使用UCASE()将字符串转换为大写
表8-2中的SOUNDEX需要做进一步的解释。SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节使得能对字符串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念但多数DBMS都提供对SOUNDEX的支持。 说明SOUNDEX支持 Microsoft Access和PostgreSQL不支持SOUNDEX()因此以下的例子不适用于这些DBMS。 另外如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项那么SOUNDEX()在SQLite中就可用。因为SQLITE_SOUNDEX不是默认的编译时选项所以多数SQLite实现不支持SOUNDEX()。 下面给出一个使用SOUNDEX()函数的例子。Customers表中有一个顾客Kids Place其联系名为Michelle Green。但如果这是错误的输入此联系名实际上应该是Michael Green该怎么办呢显然按正确的联系名搜索不会返回数据如下所示 输入▼
SELECT cust_name, cust_contactFROM Customers WHERE cust_contact Michael Green; 输出▼
cust_namecust_contact
现在试一下使用SOUNDEX()函数进行搜索它匹配所有发音类似于Michael Green的联系名 输入▼
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) SOUNDEX(Michael Green); 输出▼
cust_namecust_contactKids PlaceMichelle Green
分析▼ 在这个例子中WHERE子句使用SOUNDEX()函数把cust_contact列值和搜索字符串转换为它们的SOUNDEX值。因为Michael Green和Michelle Green发音相似所以它们的SOUNDEX值匹配因此WHERE子句正确地过滤出了所需的数据。
8.2.2 日期和时间处理函数
日期和时间采用相应的数据类型存储在表中每种DBMS都有自己的特殊形式。日期和时间值以特殊的格式存储以便能快速和有效地排序或过滤并且节省物理存储空间。 应用程序一般不使用日期和时间的存储格式因此日期和时间函数总是用来读取、统计和处理这些值。由于这个原因日期和时间函数在SQL中具有重要的作用。遗憾的是它们很不一致可移植性最差。 我们举个简单的例子来说明日期处理函数的用法。Orders表中包含的订单都带有订单日期。为在SQL Server中检索2012年的所有订单可如下进行 输入▼
SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) 2012; 输出▼
order_num2000520006200072000820009
在Access中使用如下版本 输入▼
SELECT order_num
FROM Orders
WHERE DATEPART(yyyy, order_date) 2012; 分析▼ 这个例子SQL Server和Sybase版本以及Access版本使用了DATEPART()函数顾名思义此函数返回日期的某一部分。DATEPART()函数有两个参数它们分别是返回的成分和从中返回成分的日期。在此例子中DATEPART()只从order_date列中返回年份。通过与2012比较WHERE子句只过滤出此年份的订单。 下面是使用名为DATE_PART()的类似函数的PostgreSQL版本 输入▼ SELECT order_num FROM Orders WHERE DATE_PART(‘year’, order_date) 2012; Oracle没有DATEPART()函数不过有几个可用来完成相同检索的日期处理函数。例如 输入▼
SELECT order_num
FROM Orders
WHERE to_number(to_char(order_date, YYYY)) 2012; 分析▼ 在这个例子中to_char()函数用来提取日期的成分to_number()用来将提取出的成分转换为数值以便能与2012进行比较。 完成相同工作的另一方法是使用BETWEEN操作符 输入▼
SELECT order_num
FROM Orders
WHERE order_date BETWEEN to_date(01-01-2012)
AND to_date(12-31-2012); 分析▼ 在此例子中Oracle的to_date()函数用来将两个字符串转换为日期。一个包含2012年1月1日另一个包含2012年12月31日。BETWEEN操作符用来找出两个日期之间的所有订单。值得注意的是相同的代码在SQL Server中不起作用因为它不支持to_date()函数。但是如果用DATEPART()替换to_date()当然可以使用这种类型的语句。 MySQL和MariaDB具有各种日期处理函数但没有DATEPART()。MySQL和MariaDB用户可使用名为YEAR()的函数从日期中提取年份 输入▼
SELECT order_num
FROM Orders
WHERE YEAR(order_date) 2012; 在SQLite中有个小技巧 输入▼
SELECT order_num
FROM Orders
WHERE strftime(%Y, order_date) 2012; 这里给出的例子提取和使用日期的成分年。按月份过滤可以进行相同的处理指定AND操作符以及年和月份的比较。 DBMS提供的功能远不止简单的日期成分提取。大多数DBMS具有比较日期、执行基于日期的运算、选择日期格式等的函数。但是可以看到不同DBMS的日期-时间处理函数可能不同。关于具体DBMS支持的日期-时间处理函数请参阅相应的文档。
8.2.3 数值处理函数
数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算因此不像字符串或日期-时间处理函数使用那么频繁。 具有讽刺意味的是在主要DBMS的函数中数值函数是最一致、最统一的函数。表8-3列出一些常用的数值处理函数。
表8-3 常用数值处理函数
函 数说 明ABS()返回一个数的绝对值COS()返回一个角度的余弦EXP()返回一个数的指数值PI()返回圆周率SIN()返回一个角度的正弦SQRT()返回一个数的平方根TAN()返回一个角度的正切
关于具体DBMS所支持的算术处理函数请参阅相应的文档。 上一篇第7课 SQL入门之创建计算字段 下一篇第9课 SQL入门之汇总数据
