MYSQL 教程:§8, 在SELECT中使用内置函数

 

 

在SELECT中使用内置函数

       Mysql 有大量的用于查询的内置操作符和函数，多数使用于SELECT and WHERE。也有一些使用于GROUP BY中的组函数，比如count() and max()。

 

    本章主要内容：

＊     操作符

＊     控制流函数

＊     字符串函数

＊     数值函数

＊     时间日期函数

＊     类型转换函数

＊     其他函数

＊     组函数

 

 

可以执行数学运算,比如:

mysql> select 2+2;

+-----+

| 2+2 |

+-----+

|   4 |

+-----+

1 row in set (0.00 sec)

 

 

操作符

＊     算术操作符

addition (+), subtraction (-), multiplication (*), and division (/). 除以0产生一个安全的NULL值。

＊     比较运算符

注意一：一般情况下，与NULL比较返回NULL。

 

 

mysql> select NULL=NULL;

+-----------+

| NULL=NULL |

+-----------+

|      NULL |

+-----------+

1 row in set (0.00 sec)

 

mysql> select NULL IS NULL;

+--------------+

| NULL IS NULL |

+--------------+

|            1 |

+--------------+

1 row in set (0.00 sec)

       注意二：字符串比较，多数情况下不区分大小写。

比如：

select * from department where name='marketing';

+---------------+-----------+

| departmentID  | name      |

+---------------+-----------+

|           130 | Marketing |

+---------------+-----------+

1 row in set (0.41 sec)

通过以下方式可以区分大小写

select * from department where name = binary 'marketing';

Empty set (0.18 sec)

 

 

Table 8.1. Comparison Operators
Operator	Meaning
=	Equality
!= or <>	Inequality
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
n BETWEEN min AND max	Range testing
n IN (set)	Set membership. Can be used with a list of literal values or expressions or with a subquery as the set. An example of a set is (apple, orange, pear)
<=>	NULL safe equal. This will return 1 (true) if we compare two NULL values
n IS NULL	Use to test for a NULL value in n
ISNULL(n)	Use to test for a NULL value in n

 

 

 

＊     逻辑运算符

逻辑表达式可以返回1 (true), 0 (false), or NULL， 非0，非空值也被视为true

 

Table 8.2. Logical Operators
Operator	Example	Meaning
AND or &&	n && m	Logical AND. Here is the truth table: true&&true = true false&&anything = false All other expressions evaluate to NULL.
OR or ||	n || m	Logical OR. Here is the truth table: true||anything = true NULL||false = NULL NULL||NULL = NULL false||false = false
NOT or !	NOT n	Logical NOT. Here is the truth table: !true = false !false = true !NULL = NULL
XOR	n XOR m	Logical exclusive OR. Here is the truth table: true XOR true = false true XOR false = true false XOR true = true NULL XOR n = NULL n XOR NULL = NULL

控制流函数

最常用的是：IF and CASE

 

＊     IF：

        IF (e1, e2, e3)，如果e1成立，则返回e2，否则返回e3。

 

mysql> select name, if(job='Programmer', "nerd", "not a nerd")

    -> from employee;

+---------------+--------------------------------------------+

| name          | if(job='Programmer', "nerd", "not a nerd") |

+---------------+--------------------------------------------+

| Nora Edwards  | nerd                                       |

| Ben Smith     | not a nerd                                 |

| Ajay Patel    | nerd                                       |

| Candy Burnett | not a nerd                                 |

+---------------+--------------------------------------------+

4 rows in set (0.00 sec)

 

＊     CASE

语法如下

CASE value

WHEN [compare-value] THEN result

[WHEN [compare-value] THEN result ...]

[ELSE result]

END

or

CASE

WHEN [condition] THEN result

[WHEN [condition] THEN result ...]

[ELSE result]

END

 

mysql> select workdate, case          when workdate < 2000-01-01 then "archived"          when workdate < 2003-01-01 then "old"          else "current"          end from assignment;

+------------+----------------------------------------------------------------------------------------------------------------------------------------------+

| workdate   | case          when workdate < 2000-01-01 then "archived"          when workdate < 2003-01-01 then "old"          else "current"          end |

+------------+----------------------------------------------------------------------------------------------------------------------------------------------+

| 2003-01-20 | current                                                                                                                                      |

+------------+----------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set, 2 warnings (0.00 sec)

 

mysql>

 

CASE value

WHEN [compare-value] THEN result

[WHEN [compare-value] THEN result ...]

[ELSE result]

END

or

CASE

WHEN [condition] THEN result

[WHEN [condition] THEN result ...]

[ELSE result]

END

We can use this function to return one of a number of values. For example, consider the following query:

select workdate, case

         when workdate < 2000-01-01 then "archived"

         when workdate < 2003-01-01 then "old"

         else "current"

         end

from assignment;

 

字符串函数

分为字符串处理函数和比较函数，后者比前者有用。

 

＊     字符串处理函数

 

常用参数如下：

Table 8.3. String Processing Functions
Function	Purpose
concat(s1, s2, ...)	Concatenate the strings in s1, s2, ....
conv (n, original_base, new_base)	Convert the number n from original_base to new_base. (It may surprise you to see this as a string function, but some bases use letters in their notations, such as hexadecimal.)
length(s)	Returns the length in characters of the string s.
load_file(filename)	Returns the contents of the file stored at filename as a string.
locate(needle, haystack, position)	Returns the starting position of the needle string in the haystack string. The search will start from position.
lower(s) and upper(s)	Convert the string s to lowercase or uppercase.
quote(s)	Escapes a string s so that it is suitable for insertion into the database. This involves putting the string between single quotes and inserting a backslash.
replace(target, find, replace)	Returns a string based on target with all incidences of find replaced with replace.
soundex(s)	Returns a soundex string corresponding to s. A soundex string represents how the string sounds when pronounced. It can be easier to match soundex strings of names than names themselves, for example.
substring (s, position, length)	Returns length characters from s starting at position.
trim(s)	Removes leading and trailing whitespace from s. (You can also use ltrim() to just remove whitespace from the left or rtrim() for the right.)

 

 

＊     字符串比较函数

＊    LIKE: Performs string wildcard matching.

＊    RLIKE: Performs regular expression matching.

＊    STRCMP: String comparison, just like the strcmp() function in C.

＊    MATCH: Performs full-text searching.

本节讲述前3个，Full-text searching是MYISM独有的，将在第九章讲述。

 

－＊使用LIKE 进行通配符匹配

mysql> select * from department where name like '%research%';

+--------------+--------------------------+

| departmentID | name                     |

+--------------+--------------------------+

|          128 | Research and Development |

+--------------+--------------------------+

 

%匹配很多字符，_匹配单个字符。

－＊使用RLIKE 进行正则表达式匹配

       字符串匹配字符串，比如'cat'匹配 'cat'，'catacomb'。使用'^cat$'可以精确匹配'cat'.这点和shell不同，shell是匹配行首和行尾。这里却是针对字符串的首和尾。

       通配符.匹配一个字符。*匹配0次或者多次。'n*' matches '', 'n', 'nn', 'nnn'。可以使用括号，比如'(cat)*''(cat)*' matches '', 'cat', 'catcat', 'catcatcat', and so on. '.*'匹配任何东东。

    ＋匹配一次或多次。？匹配1次或者0次。还可以指定匹配次数：'(cat)(2,4)' matches 'catcat', 'catcatcat', and 'catcatcatcat'.

       区间匹配：'[a-z]'，'[a-z]*'。

    还有一些类的定义：比如[[:alnum:]]

    Mysql 使用的是POSIX-style正则表达式，语法和perl的不同。

      

举例：

mysql> select * from department where name rlike 'an';

+--------------+--------------------------+

| departmentID | name                     |

+--------------+--------------------------+

|           42 | Finance                  |

|          128 | Research and Development |

|          129 | Human Resources          |

|          131 | Asset Management         |

+--------------+--------------------------+

 

－＊使用STRCMP()进行字符串比较

和C,PHP中的语法相同

 

STRCMP(s1, s2)

and returns the following values:

·         0 if the strings are equal

·         -1 if s1 is less than s2— that is, if it comes before s2 in the sort order

·                     1 if s1 is greater than s2— that is, if it comes after s2 in the sort order

mysql> select strcmp('cat', 'cat');

+----------------------+

| strcmp('cat', 'cat') |

+----------------------+

|                    0 |

+----------------------+

1 row in set (0.42 sec)

mysql> select strcmp('cat', 'dog');

+----------------------+

| strcmp('cat', 'dog') |

+----------------------+

|                   -1 |

+----------------------+

1 row in set (0.00 sec)

mysql> select strcmp('cat', 'ant');

+----------------------+

| strcmp('cat', 'ant') |

+----------------------+

|                    1 |

+----------------------+

1 row in set (0.00 sec)

 

       注意排序和字符串集有关。

 

数值函数

常用的数值函数如下：

Table 8.4. Numeric Functions
Function	Purpose
abs(n)	Returns the absolute value of n—that is, the value without a sign in front of it.
ceiling(n)	Returns the value of n rounded up to the nearest integer.
floor(n)	Returns the value of n rounded down to the nearest integer.
mod(n,m) and div	These two functions divide n by m. div returns the integral quotient, and mod() returns the integral remainder.
power(n,m)	Returns n to the power of m.
rand(n)	Returns a random number between 0 and 1. The parameter n is optional, but if supplied, it is used as a seed for the pseudorandom number generation. (Giving the same n to rand will produce the same pseudorandom number.)
round(n[,d])	Returns n rounded to the nearest integer. If you supply d, n will be rounded to d decimal places.
sqrt(n)	Returns the square root of n.

 

 

比如：

mod(9,2)，9 mod 2，9 % 2

9 div 2 这是div仅有的格式。

日期和时间函数

   常用的日期和时间函数如下，由于mysql的数据仓库集成，有太多的日期和时间函数。

Table 8.5. Date and Time Functions
Function	Purpose
adddate(date, INTERVAL n type) and subdate(date, INTERVAL n type)	These functions are used to add and subtract dates. Both start from the date supplied in date and add or subtract the period specified after the keyword INTERVAL. You need to specify both a quantity n and the type of that quantity. The type can be SECOND, MINUTE, HOUR, DAY, MONTH, YEAR, MINUTE:SECOND (the format of n should be 'm:s'), HOUR:MINUTE ('h:m'), DAY_HOUR ('d h'), YEAR_MONTH ('y-m'), HOUR_SECOND ('h:m:s'), DAY_MINUTE ('d h:m'), DAY_SECOND ('d h:m:s'). These functions are really useful, but remembering the data formats is virtually impossible (because they are all different), so you will usually have to look them up.
curdate(), curtime(), now()	These return the current date, the current time, and the current date and time, respectively.
date_format(date, format) and time_format(time, format)	These are used to reformat dates and times to pretty much any format you like. You do this by supplying a format string, such as date_format(workdate, '%W %D of %M, %Y'). (This gives, for example, 'Monday 16th of June, 2003'). There is a massive list of formats, so consult the manual for details.
dayname(date)	This returns the name of the day in date (for example, 'Monday').
extract(type FROM date)	This returns the value of type in date. For example, if you specify YEAR, it will return the year from date. The types are the same as in adddate() and subdate().
unix_timestamp([date])	This returns the current Unix timestamp. (That's the number of seconds since the first of January 1970.) If called with a date, this returns the timestamp corresponding to that date.

 

本节暂不深入。

select adddate("1999-01-01", INTERVAL "1-6" YEAR_MONTH);

+--------------------------------------------------+

| adddate("1999-01-01", INTERVAL "1-6" YEAR_MONTH) |

+--------------------------------------------------+

| 2000-07-01                                       |

+--------------------------------------------------+

1 row in set (0.41 sec)

select unix_timestamp(adddate("1999-01-01", INTERVAL "1-6" YEAR_MONTH));

+------------------------------------------------------------------+

| unix_timestamp(adddate("1999-01-01", INTERVAL "1-6" YEAR_MONTH)) |

+------------------------------------------------------------------+

|                                                        962373600 |

+------------------------------------------------------------------+

1 row in set (0.01 sec)

mysql> select from_unixtime(add_date)  from schedule limit 10;

+-------------------------+

| from_unixtime(add_date) |

+-------------------------+

| 2008-02-18 11:26:02     |

| 2008-02-18 11:26:02     |

| 2008-02-18 11:26:02     |

| 2008-02-18 11:26:02     |

| 2008-02-18 11:26:03     |

| 2008-02-18 11:26:03     |

| 2008-02-18 11:26:03     |

| 2008-02-18 11:26:03     |

| 2008-02-18 11:26:03     |

| 2008-02-18 11:26:03     |

+-------------------------+

10 rows in set (0.04 sec)

 

 

§8.6  类型转换函数

convert results from one type (for example, signed integer) to another (for example, char).

cast(expression AS type)

convert(expression, type)

The cast() function is ANSI compliant, and convert() is ODBC compliant.

Valid types are BINARY, CHAR, DATE, DATETIME, SIGNED (INTEGER), and UNSIGNED (INTEGER).

多数类型转换在需要的时候由mysql自动进行。

 

§8.7  类型转换函数

如下，多数和hashing or encryption有关。

Table 8.6. Miscellaneous Functions
Function	Purpose
benchmark(count, expression)	Evaluates expression count times. Always returns zero—the point of this function is to time execution and look at the execution time at the bottom of the result set.
encrypt(s[,salt])	Encrypts s using a Unix crypt system call. The salt string is an optional two-character string. If crypt is not available on your system (for example, Windows), this function will return NULL.
found_rows()	Returns the number of rows that would have been returned by the last query if no limit clause was used. Works only if SQL_CALC_FOUND_ROWS was specified in the SELECT statement, as discussed in Chapter 7.
last_insert_id()	Returns the last automatically generated AUTO_INCREMENT value. This is useful if we have inserted a row into one table and now need that row's id to insert as a foreign key into another table.
md5(s)	Returns the 128-bit MD5 hash of string s. If you are writing an application to store usernames and passwords, this is the recommended method for storing passwords in your database.
	Encryption algorithms have a limited useful lifetime. As the power of computers increases, stronger algorithms are required. MD5 is currently regarded as fairly secure.
password(s)	Calculates a password string for the string s. This is the scheme that is used to represent MySQL user passwords, as we will discuss in Chapter 11, "Managing User Privileges." It is not recommended that you use password() to store passwords in your own applications.

 

§8.8  GROUP使用的函数

比如：

mysql> select job, count(job) from employee group by job;

+-----------------------+------------+

| job                   | count(job) |

+-----------------------+------------+

| DBA                   |          1 |

| Programmer            |          2 |

| Systems Administrator |          1 |

+-----------------------+------------+

3 rows in set (0.00 sec)

 

常用的函数如下：

Table 8.7. Grouping Functions
Function	Purpose
avg(column)	Returns the average value in column.
count(column)	Returns the number of values in column.
min(column)	Returns the smallest value in column.
max(column)	Returns the largest value in column.
std(column)	Returns the standard deviation of the values in column.
sum(column)	Returns the sum of values in column.

 

§8.9  小结

·                     MySQL has a full set of arithmetic, comparison, and logical operators. You need to be careful when using operators with NULL because this does not always produce the expected results.

·         MySQL provides a set of functions that can be used to perform string, numeric, date, casting, and miscellaneous functions.

·                     The grouping functions are performed over a set of column values. These sets are groups if a GROUP BY clause is specified, or they provide the complete set of returned values in a column if no GROUP BY clause is specified.