《MySQL必知必会》知识汇总二
六、用通配符进行过滤
本章介绍什么是通配符、如何使用通配符以及怎样使用LIKE操作符进行通配搜索
LIKE操作符
- 百分号（%）通配符
select prod_id,prod_name
from products
where prod_name like 'jet%';
%表示任何字符出现任意次数,也就是0个、1个或者多个字符
%可以在匹配字符的任意位置使用，例如 '%jet'、'%jet%'、'j%t'
- 下划线（_）通配符
select prod_id,prod_name
from products
where prod_name like '_ ton anvil';
_只匹配单个字符而不是多个字符
- 使用通配符的技巧
通配符处理比其他搜索花费时间更长，因此不建议过度使用通配符。
不建议将通配符置于搜索模式的开始处，这样搜索起来是最慢的
格外注意通配符的位置
七、用正则表达式进行搜索
本章将学习如何在where子句内使用正则表达式更好的控制数据过滤
- 基本字符匹配
select prod_name
from products
where prod_name regexp '1000'
order by prod_name;
select prod_name
from products
where prod_name like'1000'
order by prod_name;
注意：LIKE与REGEXP
like不会返回1000这个值，而regexp会返回1000这个值
select prod_name
from products
where prod_name regexp '.000'
order by prod_name;
其中 . 表示匹配任意一个字符
注意：正则表达式匹配不区分大小写，如果想要区分，使用关键字BINARY位于regexp之后
- 进行OR匹配
select prod_name
from products
where prod_name regexp '1000|2000'
order by prod_name;
其中 | 表示匹配其中一个，相当于使用or操作符
- 匹配几个字符之一
select prod_name
from products
where prod_name regexp '[123]Ton'
order by prod_name;
其中[123]表示匹配1或2或3
注意：如果不想匹配123，可以使用[^123]来匹配字符1、2、3以外的值
- 匹配范围
当想要匹配[0123456789]时，可以写成[0-9]，就表示匹配0到9中的任意一个
- 匹配特殊字符
上述使用了 . [] | - 等进行匹配，那么如果想要匹配这些字符呢？
select prod_name
from products
where prod_name regexp '\\.'
order by prod_name;
其中 \\. 匹配 . ，这种处理就是所谓的转义
注意：
为了匹配反斜杠（\）需要使用\\\
- 匹配字符类
- 匹配多个实例
*： 0个或者多个匹配
+： 1个或者多个匹配
?： 0个或者1个匹配
{n}: 指定数目的匹配
{n, }: 不少于指定数目的匹配
{n,m}: 匹配数目的范围（m不超过255）
select prod_name
from products
where prod_name regexp '\\([0-9] sticks?\\)'
order by prod_name;
将匹配到 TNT (1 stick) 、 TNT(5 sticks)
- 定位符
^ : 文本的开始
$ : 文本的结尾
[[:: 词的开始
[[:>:]] 词的结尾
select prod_name
from products
where prod_name regexp '^[0-9\\.]'
order by prod_name;
注意：
^不仅可以指定串的开始，还可以否定该集合
当通过^与$配合使用，可以是regexp与like达到相同的效果，因为like匹配整个串而regexp匹配子串
八、创建计算字段
本章介绍什么是计算字段以及如何创建计算字段
- 计算字段
与前面各章介绍的列不同，计算字段并不实际存在于数据库表中，而是运行时在select语句内创建的，也就是通过各种函数生产的字段
- 拼接字段
select Concat(vend_name,'(',vend_country,')')
from vendors
order by vend_name;
于是显示出了数据库中没有的，有Concat()函数生成的新字段，这就是计算字段！
Concat()函数就是将会多个字段拼接起来
select Concat(vend_name,'(',RTrim(vend_country),')')
from vendors
order by vend_name;
还可以使用RTrim()函数进一步处理该计算字段
注意：
RTrim()表示去掉右边的空格
LTrim()表示去掉左边的空格
Trim()表示去掉两边的空格
使用别名
通过as 在表名、列名后即可赋予别名
- 执行计算字段
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
其中quantity*item_price AS expanded_price将计算产品的数量×产品的单价并使用别名显示出该产品的总价
注意：
除了*乘法运算，字段之间还可以使用+、-、/等运算
还可以通过Now()函数返回当前日期
九、使用数据处理函数
Upper()、Soundex()、文本处理函数、日期和时间处理函数、数值处理函数
十、汇总数据
聚集函数：AVG()、COUNT()、MAX()、MIN()、SUM()、
注意：
count(*)返回表中行的数目，不管表列中包含的是空值还是非空值
count(column)对指定的列进行计数并返回，会忽略null值
这两章都是介绍一些处理字段的函数，有相关需求时，直接去查表即可
十一、分组数据
本章将介绍如何分组，并介绍GROUP BY 和HAVING如何使用
- 创建分组
select vend_id,count(*) as num_prods
from products
group by vend_id;
通过vend_id进行分组，并统计每组中有多少条数据
注意：
除聚集函数计算语句外，select语句中的每个列都必须在group by 子句中给出
group by 必须出现在 where 之后，order by 之前
- 过滤分组
select cust_id,count(*) as orders
from orders
group by cust_id
having count(*)>=2;
通过having子句，可以对分组后的数据进行过滤，一般都是作用于select中的分组函数上
注意：HAVING和WHERE的差别
where在数据分组前进行过滤，having在数据分组后进行过滤
- 分组和排序
只需要知道order by在group by 之后使用
- select子句顺序
select->from->where->group by->having->order by->limit
十二、使用子查询
本章介绍什么是子查询以及如何使用它们
- 利用子查询进行过滤
需求：列出订购物品TNT的所有客户信息
此时有张订单信息表、订货id-客户id表、客户信息表
可以分三步进行查询，先通过订单信息表查询TNT商品的订单id，在通过订单id查询客户id，再通过客户id查询客户信息
将上述三个步骤合为一步：
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'));
注意：
实际在使用过程中由于性能限制，不建议嵌套太多的子查询
- 作为计算字段使用子查询
需求：需要显示customers表中每个客户的订单总数
分两步进行查询，从customers表中检索出客户id，根据客户id去订单表中查询订单并统计数目
select cust_name,cust_state,(select count(*)
from orders
where orders.cust_id=customers.cust_id) as orders
from customers
order by cust_name;
其中orders是一个计算字段，对于从customers表中查询的每个客户都执行依次
也就是在customers表中查询的customers.cust_id去order表中做查询
注意：
where子句中必须使用全限定列名
