SQL学习笔记3 通配符 计算字段 函数 汇总

LIKE操作符

在搜索子句中使用通配符来匹配字符串，必须使用LIKE操作符，且只能用于文本字段（字符串）的匹配

百分号（%）通配符

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'Fish%';

• %表示任意字符出现任意次数（可以是0次），其可以在搜索模式中任意位置使用，并可使用多个
• Access需要使用 * 而不是 %
• %不会匹配NULL值的行

下划线（_）通配符

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '_ inch teddy bear';

• DB2不支持该通配符，Access需要使用？而不是 _
• 匹配单个字符

方括号（[ ]）通配符

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '[JM]%';

• 用来指定一个字符集，必须匹配其中一个字符
• 只有Access 和 SQL server支持该通配符
• 可以用前缀 ^ 来否定，Access使用 ！来否定

创建计算字段

计算字段：在SELECT语句内创建的字段

拼接字段

SELECT vend_name + '(' + vend_country + ')'
FROM vendors;

• 使用 + 来拼接字段，Access 和 SQL server 支持

SELECT vend_name || '(' || vend_country || ')'
FROM vendors;

• 使用 || 来拼接字段，DB2 、Oracle、PostgreSQL、SQLite 和 Open Office Base 支持

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

• 使用Concat函数来拼接字段，MySQL 和 MariaDB 支持

使用别名

SELECT vend_name + '(' + vend_country + ')'
AS vend_title
FROM vendors;

• 别名是一个字段或值的替换名，用关键字AS赋予
• 当实际的表列名包含不合法的字符或容易混淆时，可以使用别名
• 别名有时也叫导出列（derived column）

执行算术计算

 SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price
 FROM orderitems
 WHERE order_num = 20008;

• 可以使用加(+)减(-)乘(*)除(/)来创建计算字段，圆括号可以用来区分优先顺序

函数

不同数据库软件的函数名称和语法可能极其不同

文本处理函数

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM vendors

常用的文本处理函数

• LEFT（ARG,N）、RIGHT（ARG,N） 返回字符串左边或右边N个字符
• LENGTH（） 返回字符串的长度，也使用DATALENGTH（）或LEN（）
• LOWER（）、UPPER（） 将字符串转换为小写和大写，Access使用LCASE（）和UCASE
• TRIM（）、RTRIM（）、LTRM（） 去掉字符串两边、右边、左边的空格
• SOUNDEX（） 描述其语音表示的字母数学模式算法，可用于模糊匹配，Access和PostgreSQL不支持

SELECT cust_name,cust_contact
FROM customers
WHERE SOUNDEX(cust_name) = SOUNDEX('Michael Green')；

日期和时间处理函数

SELECT order_num
FROM orders
WHERE DATEPART('yyyy'，order_date);

• SQL server 和 Access支持

SELECT order_num
FROM orders
WHERE YEAR(order_date) = 2012;

• MySQL 和 MariaDB支持

SELECT order_num
FROM orders
WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012')

• Oracle可以结合使用BETWEEN和to_date函数限制日期时间

数值处理函数
数值处理函数在不同的数据库软件中较为一致

汇总数据

AVG（）
求得列的平均值

SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 'DLL01';

• AVG()只能用于确定特定数值列的平均值，且列名必须作为函数参数
• 会忽略NULL值行

COUNT（）
确定表中行的数目或符合特定条件的行的数目

SELECT COUNT(*) AS num_cust
FROM customers；

• 统计所有行时，将包含NULL行，而统计特定条件下的行时，不会包含NULL行

MAX（）和 MIN（）
找出指定列中的最大值和最小值（数值或日期），当用于文本数据时，返回该列排序后的最后一行和最前面一行

SELECT MAX(prod_price) AS max_price
FROM products;

• 都会忽略NULL行

SUM（）
返回指定列值的和

SELECT SUM(quantity * item_price) AS total_price
FROM orderitems;
WHERE order_num = 20005;

• 利用算术操作符，所有聚集函数都可执行多个列上的计算

使用 DISTINCT 聚集不同的值（Access不支持）
主要用于AVG（）和SUM（）函数

SELECT AVG( DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 'DLL01';