MYSQL必知必会学习笔记

8.1.1 百分号（ %）通配符
最常使用的通配符是百分号（ %）。在搜索串中， %表示任何字符出现
任意次数。例如，为了找出所有以词jet起头的产品，可使用以下SELECT
语句：
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'jet%';
区分大小写 根据MySQL的配置方式，搜索可以是区分大小
写的。如果区分大小写， 'jet%' 与 JetPack 1000将不匹配。

搜索模式'%anvil%' 表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '%anvil%';

通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面
的例子找出以s起头以e结尾的所有产品：

SELECT prod_name FROM products Where prod_name LIKE 's%e';

重要的是要注意到，除了一个或多个字符外， %还能匹配0个字符。 %
代表搜索模式中给定位置的0个、 1个或多个字符。

8.1.2 下划线（_）通配符
另一个有用的通配符是下划线（_）。下划线的用途与%一样，但下划
线只匹配单个字符而不是多个字符。

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
任意字符
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '% ton anvil';

基本字符匹配
我们从一个非常简单的例子开始。下面的语句检索列prod_name包含
文本1000的所有行：

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
REGEXP后所跟的东西作为正则表达式（与文字正文1000匹配的一个正则表达式）处理。

SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;

 

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

匹配几个字符之一
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
这里，使用了正则表达式[123] Ton。 [123] 定义一组字符，它
的意思是匹配1或2或3，因此， 1 ton和2 ton都匹配且返回（没
有3 ton）。

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;

要找出包含. 字符的值 为了匹配特殊字符，必须用\\为前导。 \\-表示查找-， \\. 表示查找. 。
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;

正则表达式\\([0-9] sticks?\\) 需要解说一下。 \\(匹配( ，
[0-9] 匹配任意数字（这个例子中为1和5）， sticks?匹配stick
和sticks（ s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出
现）， \\) 匹配) 。没有?，匹配stick和sticks会非常困难。
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9]sticks?\\)'

[:digit:] 匹配任意数字，因而它为数字的一个集
合。 {4}确切地要求它前面的字符（任意数字）出现4次，所以
[[:digit:]]{4}匹配连在一起的任意4位数字。
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;

上面的例子也可以如下编写：
SELECT prod_name FROM products WHERE prond_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;

^匹配串的开始。因此， ^[0-9\\.] 只在. 或任意数字为串中第
一个字符时才匹配它们。没有^， 则还要多检索出4个别的行（那
些中间有数字的行）。
SELECT prod_name FROM products WHERE prod_name REGEXP'^[0-9\\.]' ORDER BY prod_name;

简单的正则表达式测试 可以在不使用数据库表的情况下用
SELECT来测试正则表达式。 REGEXP检查总是返回0（没有匹配）
或1（匹配）。可以用带文字串的REGEXP来测试表达式，并试
验它们。相应的语法如下：
SELECT 'hello' REGEXP '[0-9]';
这个例子显然将返回0（因为文本hello中没有数字）。

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

通过删除数据右侧多余的空格来整理数据，这可以
使用MySQL的RTrim() 函数来完成

SELECT CONCAT(RTRIM(vend_name), ' (',RTRIM(vend_country), ')') FROM vendors ORDER BY vend_name;

SELECT CONCAT(RTRIM(vend_name), ' (',RTRIM(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

SELECT prod_id,quantity,item_price FROM orderitems WHERE order_num = 20005;

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

Upper()将文本转换为大写，因此本例子中每个供
应商都列出两次，第一次为vendors表中存储的值，第二次作
为列vend_name_upcase转换为大写。
SELECT vend_name,UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

使用 Soundex()函数进行搜索，它匹配所有发音类似于
Y.Lie的联系名：
SELECT cust_name,cust_contract FROM customers WHERE SOUNDEX(cust_contract) = SOUNDEX('Y Lie');

SELECT cust_id, order_num FROM orders WHERE DATE(order_date) = '2005-09-01';

SELECT cust_id, order_num FROM orders WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9;

下面的例子使用AVG() 返回products表中所有产品的平均价格：
SELECT AVG(prod_price) AS avg_price FROM products;

AVG() 也可以用来确定特定列或行的平均值。 下面的例子返回特定供
应商所提供产品的平均价格：
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

AVG() 只能用来确定特定数值列的平均值， 而
且列名必须作为函数参数给出。为了获得多个列的平均值，
必须使用多个AVG() 函数.

返回customers表中客户的总数：
SELECT COUNT(*) AS num_cust FROM customers;
下面的例子只对具有电子邮件地址的客户计数：
SELECT COUNT(count_email) AS num_cust FROM customers;
MAX() 返回products表中最贵的物品的价格。
SELECT MAX(prod_price) AS max_price FROM products;

SELECT SUM(quantity) AS item_ordered FROM orderitems WHERE order_num = 20005;

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

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

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 COUNT(*) AS num_prods FROM products WHERE vend_id=1003;

SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id;
GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前。

HAVING和WHERE的差别 这里有另一种理解方法， WHERE在数据
分组前进行过滤， HAVING在数据分组后进行过滤。这是一个重
要的区别， WHERE排除的行不包括在分组中。这可能会改变计
算值，从而影响HAVING子句中基于这些值过滤掉的分组。

列出具有2个（含）以上、价格
为10（含）以上的产品的供应商：
SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING COUNT(*)>=2;
COUNT(*) 函数返回在给定的选择中被选的行数。

检索包含物品TNT2的所有订单的编号。
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
检索具有前一步骤列出的订单编号的所有客户的ID。
SELECT cust_id FROM orders WHERE order_num IN(20005,20007);
现在，把第一个查询（返回订单号的那一个）变为子查询组合两个
查询。请看下面的SELECT语句：
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2');
现在得到了订购物品TNT2的所有客户的ID。下一步是检索这些客户
ID的客户信息。检索两列的SQL语句为：
SELECT cust_name,cust_contract FROM customers WHERE cust_id IN (10001,10004);
可以把其中的WHERE子句转换为子查询而不是硬编码这些客户 ID：
SELECT cust_name,cust_contract FROM customers WHERE cust_id IN(SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id='TNT2'));

下面的代码对客户 10001的订单进行计数
SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 10001;
为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查
询。请看下面的代码：
SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

创建联结
SELECT vend_name, prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name;
SELECT vend_name, prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

要检索所有客户及每个客户所
下的订单数，下面使用了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语句使用INNER JOIN将customers和orders表互相关联。
GROUP BY 子 句 按 客 户 分 组 数 据 ， 因 此 ， 函 数 调 用 COUNT
(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

有两种基本情况，其中需要使用组合查询：
 在单个查询中从不同的表返回类似结构的数据；
 对单个表执行多个查询，按单个查询返回数据。

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);

进行全文本搜索
在索引之后，使用两个函数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作为搜索文本。由于有
两行包含词rabbit，这两个行被返回。

SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';

客户 10005现在有了电子邮件地址，因此他的记录
需要更新，语句如下：
UPDATE customers SET count_email = 'elmer@fudd.com' WHERE cust_id = 10005;
UPDATE customers SET cust_name = 'The Fudds',count_email = 'elmer@fudd.com' WHERE cust_id = 10005;

CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2))
BEGIN
SELECT SUM(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;

CALL ordertotal (20005,@total);
SELECT @total;
为了得到另一个订单的合计显示，需要再次调用存储过程，然后重
新显示变量：
CALL ordertotal(20009, @total);
SELECT @total;

考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计
增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么，
你需要做下面几件事情：
 获得合计（与以前一样）；
 把营业税有条件地添加到合计；
 返回合计（带或不带税）。

存储过程不太牢靠

创建游标
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;

OPEN ordernumbers;
CLOSE ordernumbers;
END;
这个存储过程并没有做很多事情， DECLARE语句用来定义和命
名游标，这里为ordernumbers。 存储过程处理完成后，游标就
消失（因为它局限于存储过程）。
在定义游标之后，可以打开它。
OPEN ordernumbers;
游标处理完成后，应当使用如下语句关闭游标：
CLOSE ordernumbers;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

触发器用CREATE TRIGGER语句创建。下面是一个简单的例子：
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'product added';

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

下面的例子保证州名缩写总是大写（不管UPDATE语句中给出的是大
写还是小写）：
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);