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