Running Noob

Do Something Meaningful

0%

mysql数据函数

发表于 更新于 分类于
本文字数： 8.3k 阅读时长 ≈ 8 分钟
  • 使用数据处理函数

★第10章 创建计算字段

10.1 计算字段

  • 存储在数据库表中的数据一般不是应用程序所需要的格式。下面举几个例子。

    • 如果想在一个字段中既显示公司名，又显示公司的地址，但这两个信息一般包含在不同的表列中。
    • 城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序却需要把它们作为一个恰当格式的字段检索出来。
    • 列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。
    • 物品订单表存储物品的价格和数量，但不需要存储每个物品的总价格（用价格乘以数量即可）。为打印发票，需要物品的总价格。
    • 需要根据表数据进行总数、平均数计算或其他计算。

  • 在上述每个例子中，存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。这就是计算字段发挥作用的所在了。与前面各章介绍过的列不同，计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

  • 字段（field）：基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

  • 注意：只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户机（如应用程序）的角度来看，计算字段的数据是以与其他列的数据相同的方式返回的。

★10.2 拼接字段Concat

  • 为了说明如何使用计算字段，举一个创建由两列组成的标题的简单例子。

    vendors表包含供应商名和位置信息。假如要生成一个供应商报表，需要在供应商的名字中按照 name (location) 这样的格式列出供应商的位置。此报表需要单个值，而表中数据存储在两个列vend_name和vend_ country中。此外，需要用括号将vend_country括起来，这些东西都没有明确存储在数据库表中。我们来看看怎样编写返回供应商名和位置的SELECT语句。

    解决办法是把两个列拼接起来。在MySQL的SELECT语句中，可使用 Concat() 函数来拼接两个列。

    SELECT Concat(vend_name, ' (', vend_country, ')') FROM vendors ORDER BY vend_name;

    ★Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。上面的SELECT语句连接以下4个元素：

    • 存储在vend_name列中的名字；
    • 包含一个空格和一个左圆括号的串；
    • 存储在vend_country列中的国家；
    • 包含一个右圆括号的串。

  • 拼接（concatenate）：将值联结到一起构成单个值。

  • 在第8章中曾提到通过删除数据右侧多余的空格来整理数据，这可以使用MySQL的 RTrim() 函数来完成，如下所示：

    SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') FROM vendors ORDER BY vend_name;

  • ★Trim函数：MySQL除了支持RTrim()（正如刚才所见，它去掉串右边的空格），还支持LTrim()（去掉串左边的空格）以及Trim()（去掉串左右两边的空格）。

★10.2.1 使用别名AS

  • 从前面的输出中可以看到，SELECT语句拼接地址字段工作得很好。但此新计算列的名字是什么呢？实际上它没有名字，它只是一个值。如果仅在SQL查询工具中查看一下结果，这样没有什么不好。但是，一个未命名的列不能用于客户机应用中，因为客户机没有办法引用它。

    为了解决这个问题，SQL支持列别名。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。请看下面的SELECT语句：

    SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

    SELECT语句本身与之前使用的相同，只不过这里的语句中计算字段之后跟了文本AS vend_title。它指示SQL创建一个包含指定计算的名为vend_title的计算字段。从输出中可以看到，结果与以前的相同，但现在列名为vend_title，任何客户机应用都可以按名引用这个列，就像它是一个实际的表列一样。

  • 别名的其他用途：别名还有其他用途。常见的用途包括在实际的表列名包含不符合规定的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它，等等。

  • 注意：别名关键字 AS 是可以用空格替代的，即

    • SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

    • SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') vend_title FROM vendors ORDER BY vend_name;

    的效果是一样的。

10.3 执行算术计算

  • 计算字段的另一常见用途是对检索出的数据进行算术计算。举一个例子，orders表包含收到的所有订单，orderitems表包含每个订单中的各项物品。下面的SQL语句检索订单号20005中的所有物品：

    SELECT prod_id, quantity, item_price FROM orderitems WHERE order_num = 20005;

    item_price列包含订单中每项物品的单价。如下汇总物品的价格（单价乘以订购数量）：

    SELECT prod_id, quantity, item_price, quantity*item_price AS total_price FROM orderitems WHERE order_num = 20005;

    输出中显示的total_price列为一个计算字段，此计算为quantity*item_price。客户机应用现在可以使用这个新计算列，就像使用其他列一样。

  • MySQL支持下表中列出的基本算术操作符。此外，圆括号可用来区分优先顺序。

操作符说明
+加
-减
*乘
/除
  • 如何测试计算：SELECT提供了测试和试验函数与计算的一个很好的办法。虽然SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单地访问和处理表达式。例如，SELECT 3*2; 将返回6，SELECT Trim('abc');将返回abc，而SELECT Now();利用Now()函数返回当前日期和时间。通过这些例子，可以明白如何根据需要使用SELECT进行试验。

第11章 使用数据处理函数

11.1 函数

  • 与其他大多数计算机语言一样，SQL支持利用函数来处理数据。函数一般是在数据上执行的，它给数据的转换和处理提供了方便。

    在前一章中用来去掉串尾空格的RTrim()就是一个函数的例子。

  • 函数没有SQL的可移植性强：能运行在多个系统上的代码称为可移植的（portable）。相对来说，多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。 为了代码的可移植，许多SQL程序员不赞成使用特殊实现的功能。虽然这样做很有好处，但不总是利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS非常有效地完成的工作。 如果你决定使用函数，应该保证做好代码注释，以便以后你（或其他人）能确切地知道所编写SQL代码的含义。

11.2 使用函数

  • 大多数SQL实现支持以下类型的函数。
    • 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。
    • 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
    • 用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。
    • 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

11.2.1 文本处理函数

  • 下表列出了某些常用的文本处理函数。

    函数说明
    Charset(str)返回字符串字符集
    Concat(str, [, …])连接字符串
    Instr(str, substr)返回 substr 在 str 中出现的位置，没有则返回 0
    Left(str, length)从 str 的左边起取 length 个字符
    Right(str, length)从 str 的右边起取 length 个字符
    Length(str)返回串的长度 [按照字节长度]
    Lower(str)将串转换为小写
    Upper(str)将串转换为大写
    LTrim(str)去掉串左边的空格
    RTrim(str)去掉串右边的空格
    Trim(str)去掉串两边的空格
    Soundex()返回串的SOUNDEX值
    SubString(str, position [, length])从 str 的 position 开始（从 1 开始计算），取 length 个字符，如果没有指定 length 则取完
    Replace(str, search_str, replace_str)在 str 中用 replace_str 替换 search_str

  • 表中的SOUNDEX需要做进一步的解释。SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但MySQL（就像多数DBMS一样）都提供对SOUNDEX的支持。

    下面给出一个使用Soundex()函数的例子。customers表中有一个顾客Coyote Inc.，其联系名为Y.Lee。但如果这是输入错误，此联系名实际应该是Y.Lie，怎么办？显然，按正确的联系名搜索不会返回数据，如下所示：

    SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Y.Lie';

    现在试一下使用Soundex()函数进行搜索，它匹配所有发音类似于Y.Lie的联系名：

    SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y.Lie');

    在这个例子中，WHERE子句使用Soundex()函数来转换cust_ contact列值和搜索串为它们的SOUNDEX值。因为Y.Lee和Y.Lie发音相似，所以它们的SOUNDEX值匹配，因此WHERE子句正确地过滤出了所需的数据。

  • SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

    正如所见，Upper()将文本转换为大写，因此本例子中每个供应商都列出两次，第一次为vendors表中存储的值，第二次作为列vend_name_upcase转换为大写。

★11.2.2 日期和时间处理函数

  • 日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

    一般，应用程序不用来存储日期和时间的格式，因此日期和时间函数总是被用来读取、统计和处理这些值。由于这个原因，日期和时间函数在MySQL语言中具有重要的作用。

  • 下表列出了某些常用的日期和时间处理函数。

    函数说明
    AddDate()增加一个日期（天、周等）
    AddTime()增加一个时间（时、分等）
    CurDate()、Current_Date()返回当前日期
    CurTime()、Current_Time()返回当前时间
    Current_TimeStamp()返回当前时间戳
    Date(datetime)返回日期时间的日期部分
    DateDiff(date1, date2)计算两个日期之差（结果是xx天）
    TimeDiff(time1, time2)计算两个时间之差（结果是xx小时xx分钟xx秒）
    Date_Add(date, INTERVAL data_value data_type) （INTERVAL 是关键字）在 date 中加上日期或时间
    Date_Sub(date, INTERVAL data_value data_type)在 date 上减去一个时间
    Date_Format()返回一个格式化的日期或时间串
    Year(datetime)返回一个日期的年份部分
    Month(datetime)返回一个日期的月份部分
    Day(datetime)返回一个日期的天数部分
    DayOfWeek()对于一个日期，返回对应的星期几
    Hour(datetime)返回一个时间的小时部分
    Minute(datetime)返回一个时间的分钟部分
    Second(datetime)返回一个时间的秒部分
    Now()返回当前日期和时间
    Time()返回一个日期时间的时间部分
    Unix_TimeStamp()返回的是 1970-1-1 到现在的秒数
    From_UnixTime()可以把一个 Unix_TimeStamp 的秒数，转成指定格式的日期
    LAST_DAY(date)返回该日期所在月份的最后一天

    • 注意：

      1. Date_Add(date, INTERVAL data_value data_type) 中的 INTERVAL 后面的 date_type 可以是 YEAR、MONTH、DAY、MINUTE等。

      2. Date_Sub(date, INTERVAL data_value data_type) 中的 INTERVAL 后面的 date_type 可以是 YEAR、MONTH、DAY、MINUTE等。

      3. DateDiff(date1,date2) 得到的是天数，而且是 date1 - date2 的天数，因此可以取负数。

      4. 在 From_UnixTime() 函数中，可以通过 %y-%m-%d %h:%i:%s 将 Unix_TimeStamp 转换为指定格式的日期。

        意义：在实际开发中，我们也经常使用 int 来保存一个 Unix 时间戳，然后通过 From_UnixTime() 进行转换。

  • 这是重新复习用WHERE进行数据过滤的一个好时机。迄今为止，我们都是用比较数值和文本的WHERE子句过滤数据，但数据经常需要用日期进行过滤。用日期进行过滤需要注意一些别的问题和使用特殊的MySQL函数。

    首先需要注意的是MySQL使用的日期格式。无论你什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，★日期必须为格式yyyy-mm-dd。因此，2005年9月1日，给出为2005-09-01。

    SELECT cust_id, order_num, order_date FROM orders WHERE order_date = '2005-09-01'; 此SELECT语句正常运行。它检索出一个订单记录，该订单记录的order_date为2005-09-01。

    但是，使用WHERE order_date = ‘2005-09-01’可靠吗？order_ date的数据类型为datetime。这种类型存储日期及时间值。样例表中的值全都具有时间值00:00:00，但实际中很可能并不总是这样。如果用当前日期和时间存储订单日期（因此你不仅知道订单日期，还知道下订单当天的时间），怎 么 办 ？ 比 如 ， 存 储 的 order_date 值 为2005-09-01 11:30:05，则WHERE order_date = ‘2005-09-01’失败。即使给出具有该日期的一行，也不会把它检索出来，因为WHERE匹配失败。

    解决办法是指示MySQL仅将给出的日期与列中的日期部分进行比较，而不是将给出的日期与整个列值进行比较。为此，必须使用Date()函数。Date(order_date)指示MySQL仅提取列的日期部分，更可靠的SELECT语句为：

    SELECT cust_id, order_num, order_date FROM orders WHERE Date(order_date) = '2005-09-01';

  • **如果要的是日期，请使用Date()**：如果你想要的仅是日期，则使用Date()是一个良好的习惯，即使你知道相应的列只包含日期也是如此。这样，如果由于某种原因表中以后有日期和时间值，你的SQL代码也不用改变。当然，也存在一个Time()函数，在你只想要时间时应该使用它。 Date()和Time()都是在MySQL 4.1.1中第一次引入的。

  • 如果你想检索出2005年9月下的所有订单，怎么办？简单的相等测试不行，因为它也要匹配月份中的天数。有几种解决办法，如下所示：

    • SELECT cust_id, order_num, order_date FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

      其中，BETWEEN操作符用来把2005-09-01和2005-09-30定义为一个要匹配的日期范围。

    • 还有另外一种办法（一种不需要记住每个月中有多少天或不需要操心闰年2月的办法）：

      SELECT cust_id, order_num, order_date FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

      Year()是一个从日期（或日期时间）中返回年份的函数。类似，Month()从日期中返回月份。因此，WHERE Year(order_date) = 2005 AND Month(order_date) = 9检索出order_date为2005年9月的所有行。

11.2.3 数值处理函数

  • 数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此没有串或日期—时间处理函数的使用那么频繁。
  • 下表列出一些常用的数值处理函数。
函数说明
Abs(num)返回一个数的绝对值
Bin(decimal_number)十进制转二进制
Ceiling(num)向上取整，得到比 num 大的最小整数
Floor(num)向下取整，得到比 num 小的最大整数
Exp()返回一个数的指数值
Mod(numerator, denominator)返回除操作的余数（numerator是分子，denominator是分母）
Pi()返回圆周率
Rand([Seed])返回一个随机数，其值范围为 0 <= v <= 1.0
Sin()返回一个角度的正弦
Cos()返回一个角度的余弦
Tan()返回一个角度的正切
Sqrt(num)返回一个数的平方根
CONV(num, from_base, to_base)进制转换
FORMAT(num, decimal_places)保留小数位数

11.2.4 加密函数

函数说明
User()查询用户 - 可以查看登录到mysql的有哪些用户，以及“用户@IP地址”
DataBase()查看当前使用的数据库名称
MD5(str)为字符串算出一个 MD5 32 的字符串 -> 用于加密
PassWord(str)为原密码 str 计算并返回一个字符串，通常用于对用户密码进行加密。 MySQL 数据库的用户密码就是 PassWord 函数加密的。

11.2.5 流程控制函数

函数说明
IF(expr1, expr2, expr3)如果 expr1 为 TRUE, 则返回 expr2, 否则返回 expr3
IFNULL(expr1, expr2)如果 expr1 不为 Null, 则返回 expr1，否则返回 expr2
SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 … ELSE exprn END如果 expr1 为 TRUE，则语句返回 expr2；否则如果 expr3 为 TRUE，则语句返回 expr4 … 如果都不满足，则返回 exprn

  • 例如在下面的例子中，如果 job 为 CLERK，则替换为 职员；如果 job 为 MANAGER，则替换为 经理；如果 job 为 BOSS，则替换为 老板；如果都不满足，则保持不变。

    1
    2
    3
    4
    5
    6
    SELECT id, (SELECT CASE
    	WHEN job = 'CLERK' THEN '职员'
    	WHEN job = 'MANAGER' THEN '经理'
    	WHEN job = 'BOSS' THEN '老板'
    	ELSE job 
    	END) FROM employee;
---------------The End---------------