MySQL必知必会2
使用数据处理函数
函数
与其他大多数计算机语言一样，SQL支持利用函数来处理数据。函数一般是在数据上执行的，他给数据的转换和处理提供了方便，在前一章中用来去掉尾空格的RTrim()就是一个函数的例子
文本处理函数
输入： SELECT vend_name,Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
分析： 将列 vend_name_upcase转换为大写
示例：
mysql> SELECT NAME,UPPER(NAME) AS name_upcase FROM account ORDER BY NAME;
+------+-------------+
| NAME | name_upcase |
+------+-------------+
| aaa | AAA |
| aest | AEST |
| bbb | BBB |
| ccc | CCC |
| ccc | CCC |
| ddd | DDD |
| ddd | DDD |
| t57L | T57L |
| test | TEST |
| tsdf | TSDF |
+------+-------------+
常用的文本处理函数
| 函数 | 说明 |
|---|---|
| Left() | 返回串左边的字符 |
| Length() | 返回串的长度 |
| Locate() | 找出串的一个子串 |
| Lower() | 将串转为小写 |
| LTrim() | 去掉串左边的空格 |
| Right() | 返回串右边的字符 |
| RTrim() | 去掉串右边的空格 |
| Soundex() | 返回串的SOUNDEX的值 |
| SubString() | 返回子串的字符 |
| Upper() | 将串转换为大写 |
日期和时间处理函数
日期和时间采用相应的数据类型和特殊的格式存储，以便于能快速和有效的排序或过滤，并且节省物理存储空间
常用日期和时间处理函数
| 函数 | 说明 |
|---|---|
| AddDate() | 增加一个日期(天、周等) |
| AddTime() | 增加一个时间(时、分等) |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Date() | 返回日期时间的日期部分 |
| DateDiff() | 计算两个日期之差 |
| Date_Add() | 高度灵活的日期运算函数 |
| Date_Format() | 返回一个格式化的日期或时间串 |
| Day() | 返回一个日期的天数部分 |
| DayOfWeek() | 对于一个日期、返回对应的星期几 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Month() | 返回一个日期的月份部分 |
| Now() | 返回当前日期和时间 |
| Second() | 返回一个时间的秒部分 |
| Time() | 返回一个日期时间的时间部分 |
| Year() | 返回一个日期的年份部分 |
汇总数据
聚集函数 运行在行组上，计算和返回单个值的函数
AVG函数
输入： SELECT AVG(prod_price) AS avg_price FROM products;
分析： 此SELECT语句返回值avg_price，它包含products表中所有产品的平均价格
只用于单个列 AVG()只能用来确定特定数值的平均值，而且列名必须作为函数参数给出
NULL值 AVG()函数忽略列值的NULL的行
COUNT函数
输入 SELECT COUNT(*) AS num_cust FROM customers;
分析 利用COUNT对所有行计数，不管行中各列有什么值。包含NULL值
输入 SELECT COUNT(cust_email) AS num_cust FROM customers;
分析 使用COUNT对cust_email列中有值的行进行计数，不包含NULL值
聚集不同的值
输入 SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
分析 排除掉相同的prod_price的值
分组数据
创建分组
输入 SELECT vend_id,COUNT(*) AS num_prods FROM proucts GROUP BY vend_id;
分析 上面的SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段。GROUP BY 子句指示MySQL按vendid排序并分组数据。
过滤分组
输入：
SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
分析 HAVING子句，他过滤 count(*)>=2的那些分组
HAVING和WHERE的差别：HAVING用于分组后过滤，WHERE用于分组前过滤
分组和排序
输入
SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal;
输出
mysql> SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20006 | 55.00 |
| 20008 | 125.00 |
| 20005 | 149.87 |
| 20007 | 1000.00 |
+-----------+------------+
使用子查询
利用子查询进行过滤
输入
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'
)
);
输出
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
分析
为了执行上述SELECT语句；MySQL实际上必须执行3条SELECT语句，最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。外面的子查询返回客户ID列表，此客户ID列表用于最外层的WHERE子句，最外层的查询确实返回所需的数据
连接表
外键
外键为某个表中的一列，他包含另一个表的主键值
创建联结
输入
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id=products.vend_id
ORDER BY vend_name,prod_name;
分析
最大的差别是在于所指定的两个列(prod_name和prod_price)在一个表中，二另一个列(vend_name)在另一个表中
现在来看FROM子句。与以前的SELECT语句不一样，这条语句的FROM子句列出了两个表，分别是vendors表和products表。他们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结，WHER子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id
*内部联结
输入
SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;
分析 此语句中的SELECT域前面的SELECT语句相同，但FROM子句不同，这里，两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定，在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出
创建高级联结
使用表别名
输入
SELECT cust_name,cust_contact
FROM customers AS c,orders AS o,orderitems AS oi
WHERE c.cust_id=o.cust_id
AND oi.order_num=o.order_num
AND prod_id='TNT2';
自联结
输入
SELECT p1.prod_id,p1.prod_name
FROM products AS p1,products AS p2
WHERE p1.vend_id=p2.vend_id
AND p2.prod_id='DTNTR'
自然联结
无论何时对表进行联结，应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次
输入
SELECT c.*,o.order_num,o.order_date,
oi.prod_id,oi.quantity,oi.item_price
FROM customers AS c,orders AS o,orderitems AS OI
WHERE c.cust_id=o.cust_id
AND oi.order_num=o.order_num
AND prod_id='FB';
外部联结
输入
SELECT customers.cust_id,orders.order_num
FORM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
组合查询
多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询(多条SELECT语句)，并将结果作为单个查询结果集返回
创建组合查询
使用UNION
UNION的使用很简单。所需做的只是给出每条SELECT语句，在各条语句之间加上关键字UNION
输入
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN(1001,1002)
分析 UNION指示MySQL执行两条语句，并把输出组合成单个查询结果集
全文本搜索
两个最常用的引擎为MyISAM和InnoDB,前者支持全文本搜索，后者不支持
使用全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断的重新索引
在对表列进行适当的设计后，MySQL会自动进行所有索引和重新索引
在索引之后，SELECT可与Match()和Against()一起使用以实际执行搜索
启用全文本搜索支持
一般在创建时启用全文本搜索，CREATE TABLE语句接收FULLTEXT子句，他给出被索引的一个逗号分隔的列表。
创建表
CREATE TABLE productnotes(
note_id INT NOT NULL AUTO_INCREMENT,
prod_id CHAR(10) NOT NULL,
note_date DATETIME NOT NULL,
note_text TEXT NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
)ENGINE=MYISAM;
分析 这些列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对他进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列
在定义之后MySQL自动维护该索引，在增加、更新、或删除行时，索引随之自动更新
进行全文本搜索
在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式
输入
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against("rabbit");
分析 此SELECT语句检索单个列note_text。由于WHERE子句，一个全文本搜索被执行。Match(note_text)指示MySQL针对指定的列进行搜索，Against('rabbit')指定词rabbit作文搜索文本。
使用查询扩展
查询扩展用来设法放宽所返回的全文本搜索结果的范围，考虑下面的情况。你想找出所有提到anvils的注释。只有一个注释包含词anvils，但你还想找出可能与你的搜索有关的其他行，即使他们不包含词anvils
这也是扩展的一项任务，在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：
首先，进行一个基于全文本的搜索，找出与搜索条件匹配的所有行
其次，MySQL检查这些匹配并选择所有有用的词
在其次，MySQL再次进行全文搜索，这次不仅使用原来的条件，而且还使用所有有用的词
利用查询扩展，能找出可能相关的结果，即使他们并不精确包含所查找的词
使用查询扩展
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
布尔文本搜索
MySQL支持全文本搜索的另一种形式，称为布尔方式，即使没有全文本搜索也可以使用，但这是一种非常缓慢的操作
输入
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
分析 此全文本搜索检索包含词heavy的所有行，其中使用了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同
为了匹配包含heavy但不包含任意以rope开始的词的行可以使用以下查询
输入
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
全文本布尔操作符
| 布尔操作符 | 说明 |
|---|---|
| + | 包含，词必须存在 |
| - | 排除，词必须不出现 |
| > | 包含，而且增加等级值 |
| < | 包含，且减少等级值 |
| () | 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等) |
| ~ | 取消一个词的排序值 |
| * | 词尾的通配符 |
| "" | 定义一个短语(与单个词的列表不一样，他匹配整个短语以便包含或排除这个短语) |
插入数据
插入完整的行
输入
INSERT INTO customers(cust_name,
cust_contact,
cust_email,
cust_address,
cust_city,
cust_state
)VALUES('pEP e.lAPew',
NULL,
NULL,
'100 Main Street',
'los Angeles',
'CA'
)
插入多个行
INSERT INTO customers(
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
)VALUES(
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA'
),
(
'M.Martian',
'42 Galaxy Way',
'New Your',
'NY',
'11213',
'USA'
);
更新和删除数据
输入
UPDATE customers
SET cust_email='elmer@fudd.com'
WHERE cust_id=10005;
更新多个列
UPDATE customers
SET cust_name='The Fudds',
cust_email='elmer@fudd.com'
WHERE cust_id=10005;
删除数据
DELETE FROM customers
WHERE cust_id = 10006;
