数据库基础(四)——结构化查询语言(二)
用通配符进行过滤(LIKE操作符)
前面提到的过滤都是对已知条件非常熟悉,再利用WHERE子句来对条件进行过滤,但是在很多情况下,我们手上的过滤条件不是非常精确,这时候就可以使用通配符来对条件进行过滤,如果觉得描述比较抽象,可以看看下面的例子(通配符只能用于文本字段的搜索,非文本数据类型字段不能使用通配符进行搜索)
- 百分号(%)通配符
百分号(%)是最常使用的通配符,在搜索串中,%表示任何字符出现任意次数,举个例子,为了找出所有Fish起头的产品,可以用一下代码
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';
在上面的例子中,%告诉DBMS接收Fish之后的任意字符,不管后面是啥,也不管后面有多少字符。
当然,如果Fish在中间可以这样写
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%Fish%';
如果只知道开头和结尾,也可以这样
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'F%h';
特别要注意,%除了可以匹配一个或多个字符,也可以匹配零个字符。
还有一点要注意,%可以匹配除了NULL的任何字符。
- 下划线(_)通配符
下划线(_)通配符的用途和百分号(%)类似,但它只能匹配单个字符,不能匹配多个字符。
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';
- 方括号([])通配符
方括号([])通配符用来指定一个字符集,它必须匹配指定位置的一个字符,比如,要找出所有名字以J或M开头的联系人
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
上面这个例子中用来两个通配符(’[JM]%’)来指定搜索。
- 使用通配符的注意事项
- 不要过度使用通配符,如果其他操作能达到相同的目的,应该使用其他操作,因为通配符比其他搜索要耗费更多的处理时间;
- 在确定需要使用通配符时,也尽量不要把它们用在搜索模式的开始处。因为把通配符置于开始处,搜索起来是最慢的;
- 仔细注意通配符的位置。
创建计算字段
其实字段和列的意思基本相同,创建计算字段就是创建一个新的列,二列中数据是由数据库中原有数据所计算出来的。
- 拼接字段
简单来说,就是由两列的数据组合起来,拼成一列,比如下面这个例子,可以将vend_name和vend_country这两列的数据拼起来,放到一列(以MySQL为例,用Concat实现拼接功能)
SELECT Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;
(删除多余的空格,可以用RTRIM(),LTRIM()和TRIM()三个函数)
2.使用别名
比如上面这个例子,我们将两列数据成功地拼接在一起,但是如果在其他地方要用到这些数据给怎么办呢,这时就可以给这个字段取一个别名,别名用AS关键字赋予,如下
SELECT Concat(vend_name, ' (', vend_country, ')')
AS vend_title
FROM Vendors
ORDER BY vend_name;
几点需要注意的:在上面的例子中,AS其实是可选的,但是最好使用它;别名可以是一个单词,也可以是一个字符串,如果是字符串,则需要放在引号中;别名也叫导出列。
- 执行算术计算
举个例子,给出商品单价和数量,算出总价,并且放在另一列
SELECT prod_id
quantity
item_price
quantity*item_price AS expended_price
FROM OrderItems
WHERE order_num = 20008;
使用函数处理数据
这一章就介绍一些常用的函数吧,太多就不举例了(这里不针对某一个DBMS)
- 常用的文本处理函数
函数 | 说明 |
---|---|
LEFT() | 返回字符串左边的字符 |
LENGTH() | 返回字符串的长度 |
LOWER() | 将字符串转换为小写 |
LTRIM() | 去掉字符串左边的空格 |
RIGHT() | 返回字符串右边的空格 |
RTRIM() | 去掉字符串右边的空格 |
SOUNDEX() | 返回字符串的·SOUNDEX值 |
UPPER() | 将字符串转换为大写 |
对SOUNDEX()做一个说明,SOUNDEX是一个将任何文本串转换为其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符的音节,使得能对字符串进行发音比较而不是字符比较。
-
日期和时间处理函数
-
数值处理函数
函数 | 说明 |
---|---|
ABS() | 返回一个数的绝对值 |
COS() | 返回一个角度的余弦 |
EXP() | 返回一个数的指数值 |
PI() | 返回圆周率 |
SIN() | 返回一个角度的正弦 |
SQRT() | 返回一个数的平方根 |
TAN() | 返回一个角度的正切 |
汇总数据
- 聚集函数
很多时候,我们不需要检索出数据本身,只需要这些数据的汇总信息,这时,就可以使用SQL提供的一些聚集函数,如下
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列之和 |
- AVG()函数
举例如下,求Products表中所有产品的平均价格,并取别名为avg_price
SELECT AVG(prod_price) AS avg_price
FROM Products;
- COUNT()函数
COUNT()函数有两种用法,第一种(COUNT(*))是对表中行的数目进行统计,不管表列中是否包含空值(NULL),如下
SELECT COUNT(*) AS num_cust
FROM Customers;
第二种用法,对特定列中具有值得行进行统计,此时会忽略NULL值,如下
SELECT COUNT(cust_mail) AS num_cust
FROM Customers;
- MAX,MIN,SUM等函数的用法也是一样的,这里不举例了,但注意这三个函数都忽略NULL值
- 聚集不同值
使用上面的聚集函数时,可以指定计算全部数据或者是不同的数据(可以理解为不重复的数据)
- 对所有数据都执行计算,可以用ALL参数,也可以不写,因为默认是计算所有数据
- 计算不重复数据,可以用DISTINCT参数
举例如下
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
注意,在COUNT(*)中不能用DISTINCT,以及,在MAX和MIN中用DISTINCT是没有意义的。
除了ALL和DISTINCT这两个参数,DBMS还指出其他参数,可以查阅相关文档。
- 组合聚集函数
简单来说,就是在一条语句中国利用多个聚集函数对数据进行汇总,如下
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg,
FROM Products;
小结,利用数据库聚集函数比在客户端应用程序中计算要快得多。
分组数据
这里主要涉及SELECT中的两个子句:GROUP BY和HAVING。
- 数据分组
啥叫数据分组呢?概念是这样说的:分组就是将数据分成多个逻辑组。
看懂了吗,哈哈看概念我也不懂。我们还是来看一个例子吧。
我们已经知道了如何使用聚集函数来统计,如果我们要知道供应商DLL01提供的产品数目可以这样搜索
SELECT COUNT(*) AS num_prods
FROM Products
WHERE vend_id = 'DLL01';
很简单是吧,那如果要知道另一个供应商BRS01的统计数据呢,将DLL01改成BRS01不就好了嘛,那如果要同时统计,并且分组显示呢,这时就可以用到数据分组了,如下
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
关于GROUP BY的一些重要规定(还有一些没理解透,先不写)
- 如果分组列中包含具有NULL的行,则将NULL作为一个分组返回;
- GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
- 过滤分组
前面我们在过滤数据时常常会用到WHERE子句,其实还有一个过滤子句时HAVING,在分组之前,所有的WHERE子句都可以用HAVING子句来代替,而且WHERE和HAVING的用法都是一样的,而在有数据分组之后,HAVING会比WHERE多了一个作用,就是HAVING可以过滤分组,而WHERE只能过滤行。
举个例子
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
可以看到,最后一行的HAVING子句过滤COUNT(*) >= 2的那些分组,如果将这里的HAVING换成WHERE是不起作用的,因为这里的过滤是基于分组聚集值,而不是特定行的值。
关于WHERE和HAVING的另一种理解方式:WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。还有一点,因为WHERE的执行是先于数据过滤的,所以WHERE排除的行不包括在分组中,也就是是WHERE会影响计算值。
举个WHERE和HAVING一起使用的例子吧
SELECT cust_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
上面的例子是WHERE子句先将prod_price至少为4的行过滤出来,利用GROUP BY分组后,再利用HAVING将计数为2或2以上的分组过滤出来。
关于WHERE和HAVING,总结起来就是使用HAVING时应结合GROUP BY子句,而WHERE子句可以用于标准的行级过滤。
- 分组和排序
虽然在用GROUP BY时得到的分组结果常常是顺序输出的,但并不总是这样,所以最好在分组之后用ORDER BY进行排序,如下
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
- SELECT的子句使用顺序
这里将之前所涉及的所有子句的顺序总结一下,如下表
子句 | 说明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
(以上内容大多总结自《SQL必知必会》)