SQL 基础（二）

发表于   |   分类于

内建 SQL 函数的语法是：

1
SELECT function(列名称) FROM 表名称

在 SQL 中，基本的函数类型和种类有若干种。函数的基本类型是：

  • Aggregate （聚合）函数
  • Scalar 函数

注：以下为 MYSQL 数据库的语法，部分不适用于其他数据库。

字符函数

函数 描述
CONCAT(string1, string2,…) 字符连接
CONCAT_WS(seperator, string1, string2,…) 使用指定的分隔符进行字符连接
FORMAT(number, format) 数字格式化
LOWER(string) 转换成小写字母
UPPER(string) 转换成大写字母
MID(string, start [,length]) 从文本字段中提取字符
LEFT(string, length) 获取左侧字符
RIGHT(string, length) 获取右侧字符
LENGTH(string) 获取字符串长度
LTRIM([ characters FROM ] string) 删除前导空格
RTRIM([ characters FROM ] string) 删除后续空格
TRIM([ characters FROM ] string) 删除前导和后续空格
SUBSTRING(expression, start [,length]) 字符串截取
[NOT] LIKE 模式匹配
REPLACE(string_expression, string_pattern , string_replacement) 字符串替换

FORMAT()

返回结果是字符型

例如：

1
SELECT FORMAT(12560.75,1) // 12560.8

LEFT()

例如： 

1
SELECT LEFT('MySQL',2) // My

LENGTH()

LENGTH() 函数返回文本字段中值的长度。
UTF8 编码下，一个汉字是算三个字符，一个数字或字母算一个字符。其他编码下，一个汉字算两个字符，一个数字或字母算一个字符。

注：数字、文本和日期类型均可使用 MID() 函数

Scalar 函数的操作面向某个单一的值，并返回基于输入值的一个单一的值。

UCASE() / LCASE()

UCASE()、LCASE()函数分别把字段的值转换为大写和小写。

1
2
SELECT UCASE(列名称) FROM 表名称
SELECT LCASE(列名称) FROM 表名称

MID()

MID() 函数用于从文本字段中提取字符。

  • 起始下标的起始值是 1。
  • 长度可选。如果省略，则 MID() 函数返回剩余文本。
  • 数字、文本和日期类型均可使用 MID() 函数

ROUND()

  • “小数位数”可选，如果不写，默认值为0.
  • “小数位数”（d）可以是负数，这时是指定小数点左边的 d 位整数位为 0,同时小数位均为0；

例如：

1
2
select round(1123.26723); // 1123
select round(1123.26723,-1); // 1120

FORMAT()

FORMAT 函数用于对字段的显示进行格式化。

1
SELECT FORMAT(列名称, 格式) FROM 表名称

例如：

1
2
SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products

TRIM()

例如：

1
2
3
TRIM(LEADING '?' FROM '?MySQL???') // MySQL???
TRIM(TRAILING '?' FROM '?MySQL???') // ?MySQL
TRIM(BOTH '?' FROM '?MySQL???') // MySQL

REPLACE()

1
SELECT REPLACE('?MyS???QL???', '?', '') // MySQL

SUBSTRING()

注： MySQL 的编号从1开始

1
2
SUBSTRING('MySQL', 1, 3) // MyS
SUBSTRING('MySQL', -1) // L

start 可以为负数，表示倒数第几个，但长度不能为负。

数值运算符和函数

函数 描述
CEIL(number) 上取整
FLOOR(number) 下取整
ROUND(number) 四舍五入
DIV 整数除法
MOD或% 取余数
POWER(number ,y) 幂运算
TRUNCATE(number, decimal) 数字截取

DIV

1
3 DIV 4 // 0

TRUNCATE

1
2
TRUNCATE(125.89, 1) // 125.8
TRUNCATE(125.89, -1) // 120

日期函数

函数 描述
NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式的日期部分
EXTRACT() 返回日期/时间按的单独部分
DATE_ADD(time, INTERVAL expr type) 给日期添加指定的时间间隔
DATE_SUB(time, INTERVAL expr type) 从日期减去指定的时间间隔
DATEDIFF(date1, date2) 返回两个日期之间的天数
DATE_FORMAT() 用不同的格式显示日期/时间

数据格式:

  • DATE - YYYY-MM-DD
  • DATETIME - YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - YYYY-MM-DD HH:MM:SS
  • YEAR - YYYY 或 YY
  • NOW - YYYY-MM-DD HH:MM:SS AM/PM

注：只要数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间，情况就有点复杂了。

日期类型：

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

DATE_ADD()

1
2
DATE_ADD('2014-3-12', INTERVAL -365 DAY) // 2015-03-12 
DATE_ADD('2014-3-12', INTERVAL 3 WEEK) // 2015-04-02

返回值类型为 DATETIME

DATEDIFF()

1
DATE_ADD('2013-3-12', '2014-3-12') // -365

DATE_FORMAT()

1
DATE_FORMAT('2014-3-12', '%m/%d/%Y') // 03/02/14

信息函数

函数 描述
CONNECTION_ID() 连接 ID
DATEBASE() 当前数据库
LAST_INSERT_ID() 最后插入记录的 ID
USER() 当前用户
VERSION() 数据库的版本信息

聚合函数

Aggregate 函数的操作面向一系列的值，并返回一个单一的值。

函数 描述
AVG(column) 返回某列的平均值
COUNT(column) 返回某列的行数（不包括 NULL 值）
SUM(column) 返回某列的总和
MAX(column) 返回某列的最高值
MIN(column) 返回某列的最低值

AVG()

AVG() 函数返回数值列的平均值。NULL 值不包括在计算中。

1
SELECT AVG(列名称) FROM 表名称

COUNT()

COUNT() 函数返回匹配指定条件的行数。

1
2
3
4
5
// 返回匹配指定条件的行数
SELECT COUNT(列名称) FROM 表名称

// 返回匹配指定条件的唯一值的行数
SELECT COUNT(DISTINCT column_name) FROM table_name

例如：返回表的行数

1
SELECT COUNT(*) FROM 表名称

MAX() / MIN()

MAX()、MIN()函数返回一列中的最大／小值，NULL 值不包括在计算中。
注：MAX 和 MIN也可用于文本列，以获得按字母顺序排列的最高或最低值。

1
2
SELECT MAX(列名称) FROM 表名称
SELECT MIN(列名称) FROM 表名称

SUM()

SUM 函数返回数值列的总数（总额）。

1
SELECT SUM(列名称) FROM 表名称

加密函数

函数 描述
MD5() 信息摘要算法
PASSWORD() 密码算法，用于修改密码

PASSWORD()

用于修改客户端密码。

1
SET PASSWORD=PASSWORD('dilimitar')

与 NULL 相关

ISNULL()

1
ISNULL(表达式) FROM 表名称 WHERE ISNULL(列名称／表达式)

如表达式为 null，那么 ISNULL() 的返回值为 1，否则返回值为 0。

例如：

1
2
3
4
SELECT * FROM DATE WHERE ISNULL(dt);

// 等价于
SELECT * FROM DATE WHERE dt IS NULL

IFNULL()

1
SELECT IFNULL(列名称／表达式1, 表达式2) FROM 表名称

假如表达式 1 不为 NULL，则 IFNULL() 的返回值为表达式 1; 否则其返回值为表达式 2。IFNULL() 的返回值是数字或是字符串，具体情况取决于其所使用的语境。

例如:

1
2
3
4
5
SELECT IFNULL(dt, '9999-10-01') FROM date 
// 返回值包含 dt = NULL 的纪录，该纪录被替换为 '9999-10-01'

SELECT IFNULL(1/0, '9999-10-01') FROM date 
// 返回值为一列，值均为 '9999-10-01'

NULLIF()

1
SELECT NULLIF(表达式1, 表达式2) FROM 表名称

如果表达式 1 = 表达式 2 成立，那么返回值为 NULL，否则返回值为表达式 1。

例如：

1
2
3
4
5
SELECT NULLIF(1,1) FROM date;   
// 返回值为一列，值均为 NULL

SELECT NULLIF(1,2) FROM date
// 返回值为一列，值均为 1