MySQL 必知必会 -MySQL Crash Course [英]Ben Forta 刘晓霞 钟鸣 人民邮电出版社
原书 ISBN: 0672327120 中文版 ISBN: 9787115191120
分类:计算机/数据库/MySQL
配套网站:http://forta.com/books/0672327120/
11.使用数据处理函数
1.函数
函数可移植性不强
2.使用函数
1>.文本处理函数
SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
Left() 返回串左边的字符,Length() 返回串的长度,Locate() 找出串的一个子串,Lower() 转换为小写,
LTrim() 去掉左边的空格,Right() 返回串右边的字符,RTrim() 去掉右边的空格,Soundex() 返回串的 SOUNDEX 值,
SubString() 返回子串的字符,Upper() 转换为大写
这里 SOUNDEX 是一个将任何文本串转换为描述其语言表示字母数字模式的算法
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y Lie');
2>.日期和时间处理函数
AddDate() 增加一个日期,AddTime() 增加一个时间,CurDate() 返回当前日期,CurTime() 返回当前时间,
Date() 返回日期时间的日期部分,DateDiff() 计算两日期之差,Date_Add() 高度灵活的日期运算函数,
Date_Format() 返回一格式化日期或时间串,Day() 返回一日期的天数部分,DayOfWeek() 返回一日期 即星期几,
Hour() 返回一日期的小时部分,Minute() 返回一日期的分钟,Month() 返回一日期的月份,Now() 返回当前日期与时间,
Second() 返回一日期的秒,Time() 返回一日期时间的时间部分,Year() 返回一日期的年份部分
注意:使用的日期格式
SELECT cust_id, order_num FROM orders WHERE order_date = '2005-09-01';
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';
如果要的是日期,要使用 Date()
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
3>.数值处理函数
主要用于代数、三角和几何运算
Abs() ,Cos() ,Exp() ,Mod() ,Pi() ,Rand() ,Sin() ,Sqrt() ,Tan()
12.汇总数据
1.聚集函数
聚集函数 (aggregate function) 运行在行组上,计算和返回单个值的函数
AVG() 返回某列的平均值,COUNT() 返回某列的行数,MAX() 返回某列的最大值,
MIN() 返回某列的最小值,SUM() 返回某列之和
1>.AVG() 函数
SELECT AVG(prod_price) AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
若想获得多列的平均值,必须使用多个 AVG() 函数,AVG() 函数忽略列值为 NULL 的行
2>.COUNT() 函数
使用 COUNT(*) 对表中行的数目进行计数,不忽略 NULL
SELECT COUNT(*) AS num_cust FROM customers;
使用 COUNT(column) 对特定列中具有值得行进行计数,忽略 NULL 值
SELECT COUNT(cust_email) AS num_cust FROM customers;
3>.MAX() 函数
要求指定列名,忽略值为 NULL 的行
SELECT MAX(prod_price) AS max_price FROM products;
4>.MIN() 函数
SELECT MIN(prod_price) AS min_price FROM products;
5>.SUM() 函数
忽略值为 NULL 的行
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;
2.聚集不同值
以上的函数:1>.对所有行执行计算,即指定 ALL 参数 (默认) 2>.只包含不同的值,指定 DISTINCT 参数
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
3.组合聚集函数
SELECT 语句可根据需要包含多个聚集函数
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;
13.分组数据
1.数据分组
2.创建分组
分组是在 SELECT 语句中的 GROUP BY 子句中建立的
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
将 products 中所有的行按照 vend_id 分组,并依次计数
注意:对 GROUP BY 子句:
使用 ROLLUP 使用 WITH ROLLUP 关键字,可以得到每个分组以及每个分组汇总级别 (针对每个分组) 的值
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;
3.过滤分组
可以规定包括哪些分组,排除哪些分组
WHERE 过滤指定的是行而不是分组
可以使用 HAVING 子句来过滤分组,HAVING 支持所有 WHERE 操作符,可以直接套用
WHERE 在数据分组前进行过滤,HAVING 在数据分组后进行过滤
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
可以同时使用 WHERE 和 HAVING 子句,即先用 WHERE 过滤,再用 HAVING 过滤
SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >=10
GROUP BY vend_id HAVING COUNT(*) >= 2;
4.分组和排序
GROUP BY 和ORDER BY:
SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems
GROUP BY order_num HAVING SUM(quantity*item_price) >= 50;
按 order_num 排序分组
SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems
GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;
按 ordertotal 排序分组
5.SELECT 子句顺序
SELECT 要返回的列或表达式 必须使用
FROM 从行中检索数据的表 仅在从表中选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否
14.使用子查询
1.子查询
SELECT 是简单查询,即从单个数据库表中检索数据的单条语句
查询 (query) 任何 SQL 语句都是查询,但术语一般指 SELECT 语句
子查询,即嵌套在其他查询中的查询
2.使用子查询进行过滤
关系表
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
==>
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
在 SELECT 语句中,子查询总是从内向外处理
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001,10004);
==>
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'));
列必须匹配,在 WHERE 子句中使用子查询,应保证 SELECT 语句具有与 WHERE 子句中相同数目的列
子查询也可以用于测试等于 (=)、不等于 (<>)
3.作为计算字段使用子查询
使用子查询的另一方法是创建计算字段
SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 10001;
==>
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
相关子查询 (correlated subquery) 涉及外部查询的子查询,必须使用完全限定的列名
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE cust_id = cust_id) AS orders
FROM customers ORDER BY cust_name;
这时就会发生错误,无法得到正确的结果
15.联结表
1.联结
1>.关系表
关系表设计就是要保证把信息分解成多个表,一类数据一个表,各表通过某些常用的值互相关联
外键 (foreign key) 外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系
关系数据库的可伸缩性比非关系数据库好
可伸缩性 (scale) 能够适应不断增加的工作量而不失败,设计良好的数据库或应用程序称之为可伸缩性好 (scale well)
2>.为什么使用联结
分解数据为多个表能更有效地存储,更方便地处理,并具有更大的可伸缩性
这时,数据存储在多个表内,要用单条 SELECT 语句检索出数据,就得使用联结
联结不是实际存储在数据库表中的,而是根据需要建立并存在与查询的执行中
2.创建联结
创建联结,仅需规定要联结的所有表以及它们如何关联即可
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;
1>.WHERE 子句的重要性
联结两个表时,实际上做的是将第一个表中的每一行与第二个表中的每一行配对,WHERE 子句给出过滤条件
笛卡尔积 (cartesian product) 由没有联结条件的表关系返回的结果为笛卡尔积,检索出的行的数目将是第一个表中的行数乘以第二个表中的行数
如上面例子,去掉 WHERE 语句,会有不同的结果
SELECT vend_name, prod_name, prod_price FROM vendors, products ORDER BY vend_name, prod_name;
叉联结
2>.内部联结
以上的联结也被称为等值联结,其基于两个表之间的相等测试,即内部联结。可以使用稍微不同的语法指定联结的类型
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
这里 FROM 子句不同,两表关系由 INNER JOIN 指定,此时联结条件用特定的 ON 子句而不是 WHERE 子句,其传递的条件与传递给 WHERE 相同
3>.联结多个表
SQL 对联结的表的数目没有限制,首先列出所有表,然后定义表间关系即可
SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;
MySQL 在运行时关联指定的每个表以处理联结,这种处理非常耗费资源
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'));
嵌套子查询
==>
SELECT cust_name, cust_contact FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2';
使用两个联结
16.创建高级联结
1.使用表别名
别名除可用于列名和计算字段外,SQL 还允许给表名起别名,这样:可以缩短 SQL 语句,并允许在单条 SELECT 语句中多次使用相同的表
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';
注意:表别名只在查询执行中使用。且与列别名不同,表别名不返回到客户机
2.使用不同类型的联结
以上,所有使用的联结均是称为内部联结或等值联结 (equijoin) 的简单联结,此外还有其他三种联结:
1>.自联结
SELECT prod_id, prod_name FROM products WHERE vend_id =
(SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
此为子查询的方法
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';
使用自联结有时会比使用子查询快得多
2>.自然联结
无论何时对表进行联结,应该至少有一列出现在不止一个表中。标准的联结返回所有数据,甚至相同的列出现多次。自然联结排除出现多次,使每个列只返回一次。
自然联结,只能选择那些唯一的列,这一般通过对表使用通配符 (SELECT *),对所有其他表的列使用明确的子集来完成
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';
这样,没有重复的列被检索出来,其实几乎所有内部联结都是自然联结
3>.外部联结
将一个表的行与另一个表中的行相关联,包含了那些在相关表中没有关联行的行的联结
SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;
检索所有客户与其订单,但这样会得不到没有订单的客户
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
这样,使用外部联结,可使没有订单的客户也显示出来
关键字 OUTER JOIN 可以指定联结的类型,而非在 WHERE 子句中指定
使用 OUTER JOIN 语法时,必须使用 RIGHT 或 LEFT 关键字指定其所有行的表 (即右边的表还是左边的表),其不同仅在于所关联的表的顺序不同
注意:在 MySQL 中没有 *= 和 =* 的使用方法
3.使用带聚集函数的联结
检索所有客户及美国客户所下的订单数,使用 COUNT() 函数即可完成:
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
但这样会缺少没有订单的客户,同时可以看出聚集函数和联结可以方便地结合
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
可以使用外部联结显示没有订单的客户
4.使用联结和联结条件
17.组合查询
1.组合查询
执行多个查询 (多条 SELECT 语句),并将结果作为单个查询结果集返回,这些组合查询通常称为并 (union) 或复合查询 (compound query)
使用情况:1>.在单个查询中从不同的表返回类似结构的数据 2>.对单个表执行多个查询,按单个查询返回结果
多数情况下,组合相同表的结果和使用多个 WHERE 子句的结果相同
2.创建组合查询
1>.使用 UNION
给出每条 SELECT 语句,在各条语句间放上关键字 UNION
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5;
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);
==>
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);
作为对比:
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001,1002);
2>.UNION 规则
<1.UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字 UNION 分隔
<2.UNION 中的每个查询必须含有相同的列、表达式或聚集函数
<3.列数据类型必须兼容:类型不必完全相同,但必须是 DBMS 可以隐含的转换的类型
3>.包含或取消重复的行
默认去除重复的行,但可以使用 UNION ALL 取消去除,即返回所有匹配行,这是 WHERE 完成不了的
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION ALL
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);
4>.对组合查询结果排序
使用 UNION 组合查询时,只能用一条 ORDER BY 子句,并必须出现在最后一条 SELECT 语句后
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)
ORDER BY vend_id, prod_price;
注意:这里不能使用 UNION ALL
18.全文本搜索
1.理解全文本搜索
并非所有引擎都支持全文本搜索,MyISAM 支持,而 InnoDB 不支持
LIKE 和正则表达式可以匹配部分文本
这些搜索机制有很多限制:性能差、不能很明确地控制、不够智能
2.使用全文本搜索
在索引之后,SELECT 可与 Match() 和 Against() 一起使用以实际执行搜索
1>.启用全文本搜索支持
在 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;
不要在导入数据时使用 FULLTEXT,应该先导入所有数据,再修改表,定义 FULLTEXT,这样有助于更快地导入数据
2>.进行全文本搜索
在索引之后,使用两个函数 Match() 和 Against() 执行全文本搜索
Match() 指定被搜索的列,Against() 指定要使用的搜索表达式
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
注意:传递给 Match() 的值必须与 FULLTEXT() 定义中的相同,若指定多个列,必须列出它们,并次序要正确;搜索不区分大小写,除非使用 BINARY 方式
类似的,也可以使用 LIKE 子句完成以上功能:
SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';
其文本的次序不同,全文本搜索以词出现的次序排序
SELECT note_text, Match(note_text) Against('rabbit') AS rank1 FROM productnotes;
可以显示全文本搜索计算出来的等级值,其由 MySQL 根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含改词的数目计算出来
注意:rank 在 MySQL 8 版本后成为保留字
3>.使用查询扩展
查询扩展用来设法放宽所返回的全文本搜索结果的范围
使用查询扩展时,MySQL 对数据和索引进行两遍扫描完成搜索:<1.进行一个全文本搜索,找出与搜索条件匹配的所有行 <2. MySQL 检查这些匹配行并选择所有有用的词 <3.MySQL再次进行全文本搜索,这次不仅使用原来的条件,还使用所有有用的词
SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils');
不使用查询扩展
SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
使用查询扩展
4>.布尔文本搜索
布尔方式 (boolean mode) 是全文本搜索的一种形式,其可以提供一下内容的细节:要匹配的词、要排斥的词、排列提示、表达式分组。另外即使没有 FULLTEXT 索引也可以使用布尔方式,但这时非常缓慢
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
匹配包含 heavy 但不包括任意以 rope 开始的行
全文本布尔操作符:
+ 包含,- 排除,> 包含且增加等级值,< 包含且减少等级值,() 把词组成子表达式,
- 取消一个词的排序值,* 词尾的通配符,“” 定义一个短语 匹配时要匹配整个短语
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+heavy +bait' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy bait' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE Match(note_text) Against(' "heavy +bait" ' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE Match(note_text) Against('>heavy <bait' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
注意:在布尔方式中,不按等级值降序排序返回的行,即排列而不排序
5>.全文本搜索的使用说明
邻近操作符,即搜索相邻的词 (在相同句子中、相同段落中或在特定数目的词的部分中),MySQL 全文本搜索不支持邻近操作符
19.插入数据
1.数据插入 INSERT
用来插入行到数据库表,插入方法有一下几种:插入完整的行、插入行的一部分、插入多行、插入某些查询的结果
另外,可以针对每个表或用户,禁止使用 INSERT 语句
2.插入完整的行
指定表名和被插入到新行中的值
INSERT INTO Customers
VALUES(NULL, 'Pep E.LaPew', '100 Main Street', 'Los Angles', 'CA', '90046', 'USA', NULL, NULL);
对每个列必须提供一个值,如没有值即使用 NULL
更加安全的写法如下:
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('Pep E.LaPew', '100 Main Street', 'Los Angles', 'CA', '90046', 'USA', NULL, NULL);
此时不必再给出 cust_id 的值
省略列的条件:1>.该列定义为允许 NULL 值 2>.在表定义中给出默认值,这表示如果不给出值,将使用默认值
使用 INSERT 会降低数据检索的性能,可以使用 INSERT LOW_PRIORITY INTO,这样可以降低 INSERT 的优先级
3.插入多个行
使用多条 INSERT 语句,可以一次提交它们,每条语句间用一个分号隔离
或者如果每条 INSERT 语句中的列名相同:
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('Pep E.LaPew', '100 Main Street', 'Los Angles', 'CA', '90046', 'USA'),
('M.Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');
4.插入检索出的数据
可以将一条 SELECT 语句的结果插入表中
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
SELECT cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM custnew;
另外,这里 SELECT 语句中还可以 包含 WHERE 子句以过滤插入的数据
20.更新和删除数据
1.更新数据 UPDATE
更新表中特定行、更新表中所有行
注意:不要省略 WHERE 子句,稍不注意就会更新表中所有行
基本的 UPDATE 语句由三部分组成:要更新的表、列名和它们的新值、确定要更新行的过滤条件
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;
UPDATE 语句中也可以使用子查询
如果用 UPDATE 语句更新多行,如其中一行出现错误,则整个 UPDATE 操作即被取消,可以使用 IGNORE 关键字,使得发生错误,也会继续更新
UPDATE IGNORE customers
若想删除某个列的值,可设置它为 NULL ,当然表定义须运行 NULL 值
UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;
2.删除数据 DELETE
从表中删除特定的行、从表中删除所有行
注意:不要省略 WHERE 子句,稍不注意就会删除表中所有行
DELETE 不需要列名或通配符,其删除的是整行,若要删除指定列,应使用 UPDATE 语句
DELETE FROM customers WHERE cust_id = 10006;
若想从表中删除所有行,使用 TRUNCATE TABLE 语句,其速度更快,TRUNCATE 实际上是删除原来的表并重新创建一个表,而非逐行删除表中的数据
3.更新和删除的指导原则
注意:MySQL 没有撤销,使用 UPDATE 和 DELETE 要非常注意