小黄的MySQL学习之路-03

这是我学习的

MySQL必知必会的笔记

因本人才疏学浅，如有错误之处，还请见谅

01的地址

02的地址

在使用之前,需要先导入一个sql文件.

文件如下:

https://www.jianguoyun.com/p/DbapeNsQh_PWCBijurED

准备工作的表

汇总数据

① 聚集函数

定义: 聚集函数运行在组上,计算和返回单个值的函数

MySQL中常见的聚集函数

函数	说明
AVG()	返回某列的平均数
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

① AVG函数

栗子如下:

我们查询产品的平均价格

SELECT AVG(prod_price) AS avg_price
FROM products;

结果如下:

avg会自动的忽视NULL
值

② COUNT() 函数

使用方式有两种：

COUNT(*) 对表中所有的行的数目进行计数,不管表列中包含的是空值NULL ,还是非空值

使用COUNT(column)对特定列中具有值的行进行比较,忽略NULL值.

SELECT COUNT(*) AS num_cust
FROM customers;

SELECT COUNT(cust_email) AS num_cust
FROM customers;

上一个是对所有人的,这个就是对顾客中有电子邮件的人的.

③ Max函数

Max 函数返回指定列中的最大值.如果是非数值的话,返回文本列中的最大值.

如果文本都相同的话,那么就返回最后一行

举个例子

SELECT MAX(prod_price) AS max_price
FROM products;

④ Min函数

Min所有的东西都和Max 相反

这里不做过多的解释

举个例子

SELECT MIN(prod_price) AS min_price
FROM products;

⑤ SUM函数

Sum函数用来返回指定列值的总和.

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

Sum也可以用来计算合体计算值

栗子如下:

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

结果如下:

② 聚集不同值

就是一个参数

DISTINCT

PS: DISTINCT不能和 COUNT(*) 一起使用.

DISTINCT必须是用列名

DISTINCT和MIN和MAX一起使用无意义.

下面是一个栗子.

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 vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

我们这里的分组就是vend_id

然后内容就是vend_id有多少个产品

结果如下表:

PS：

GROUP BY 之前:

① GROUP BY 子句可以包含任意数目的列，

②　GROUP BY 子句中可以嵌套分组

如果嵌套，数据将在最后规定的分组进行汇总。

③　GROUP BY

子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数），如果在 SELECT 中使用表达式，不能使用别者．

④　如果分组中具有ＮＵＬＬ　值，那么这些ＮＵＬＬ值将分为一组．

⑤　GROUP BY 子句必须在　WHERE子句之后在ORDER BY 子句之前

③过滤分组

Having 可以实现部分WHERE的功能，

而且Having可以使用全部的WHERE的操作符

下面是栗子：

SELECT cust_id,COUNT(*) AS orders 
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >=2 ;

结果如下：

一个比较好理解的解释

Having发生在数据分组后WHERE发生在数据分组前．

注意，Having和WHERE是可以一起使用的。

栗子如下：

SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >=2;

结果如下：

这个mysql的作用就是

列出具有２个以上（包括两个）　，价格为１０以上的产品的供应商

④ 分组和排序

ORDER BY	GROUP BY
排序产生的输出	分组行,但输出可能不是分组的顺序
任意列都可以使用(甚至非选择的列也可以使用)	只可能使用选择列或表达式列,而且必须使用每个选择的列表达式
不一定需要	如果与聚集函数一起使用列(或表达式),则必须使用

PS　在使用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;

可以看到,这里ordertotal的排序不是我们想要的排序.我们改变一哈

加一个order by 看看

SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

结果如下:

⑤ SELECT　子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

使用子查询

①子查询

查询：任何SQL语句都是查询，但此术语一般指SELECT 语句．

子查询：嵌套在其他查询中的查询．

②利用子查询进行过滤

如果我们现在需要列出订购物品TNT2的所有客户,应该是怎样检索.

​ 检索包含物品TNT2的所有订单的编号.

​ 检索具有前一步骤列出的订单编号的所有客户的ID

​ 检索前一步骤返回的所有客户ID的客户信息

​ 检索包含物品TNT2的所有订单的编号.

SELECT order_num
FROM orderitems
WHERE prod_id='TNT2';

​ 检索具有前一步骤列出的订单编号的所有客户的ID

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

然后我们已经得到了订购TNT2的所有客户的ID,下一步就是检索这些客户ID的客户信息.

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

③ 作为计算字段使用子查询

需要显示customers表中每个客户的订单总数

从customers表中检索客户列表

对于检索出的每个客户,统计其在orders表中的订单数目

语句如下:

SELECT cust_name,
			 cust_state,
			 (SELECT COUNT(*)
			 FROM orders
			 WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

PS:如果当遇到可能有二义性的列名时,需要加上

表名.列名

比如 orders.cust_id

联结表

联结:SQL最强大的功能之一就是在数据检索查询的执行中联结(join)表

联结是利用SQL的SELECT能执行的最重要的操作.

① 创建联结

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id=products.vend_id
ORDER BY vend_name,prod_name;

结果如下:

如果没有WHERE语句,就直接来联结的话,那么结果就是笛卡尔积.

就是表A的行数*表B的行数

栗子如下:

SELECT vend_name,prod_name,prod_price
FROM vendors,products
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;

结果如下:

③ 联结多个表

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;

结果如下：

创建高级联结

①使用表别名

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

就是一个相同的表,取两个别名,在联结在一起.

结果如下:

②-②外部联结

将一个表中的行和另一个表中的行相关联

举个栗子：

这个SQL的功能是 :检索所有客户及其订单.

SELECT customers.cust_id,orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id=orders.cust_id;

截图

INNER JOIN 就是左边的表和右边的表都需要存在的意思．

然后如果我们要查询的是：

检索所有的客户，不管是有没有订单

那么我们就需要改成

SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER  orders
ON customers.cust_id=orders.cust_id;

LEFT 就代表着，左边的customers这张表，我们是用这张表来匹配orders的。

如果是Right的话．

这段代码的意识就是

检索所有的订单，不管订单是否为空。

③使用带聚集函数的联结

举个栗子：

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;

这段代码的意思是

寻找customer里面所有的订单数量．

并按照customres　cust_id来排序

组合查询

组合查询用的就是UNION 关键字

然后作用,就是把两个SELECT 语句联合起来.

举个栗子;

我们要找 价格小于等于5 的所有物品,还有找供应商1001和1002生产的所有物品.

如果按照单个查询是这样的.

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

我们使用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 ALL

就像这样.

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

UNION 规则

① UNION必须有两条以上的SELECT语句组成,语句之间用关键字UNIION分隔(因此,如果组合4条SELECT语句,将要使用3个UNION关键字)

② UNION的每个查询必须包含相同的列,表达式或聚集函数(不过各个列不需要以相同的次序列出)

③ 列数据类型必须兼容.类型不必完全相同,但必须是DBMS可以隐含转换的类型

④ 对组合查询排序一定要在最后一个SELECT　上进行，而且只能有一个排序．

不允许使用多条ORDER BY 子句．

使用全文本搜索

在使用全文本搜索前,我们要为搜索的列设置一个索引.

栗子如下:

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 为 note_text设置了索引.

这样,我们就可以在note_text上进行全文本搜索了.

然后这是插入的语句(这里不推荐这样做,等下会讲):

INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (101, 'TNT2', '2005-08-17 00:00:00', 'Customer complaint:\r\nSticks not individually wrapped, too easy to mistakenly detonate all at once.\r\nRecommend individual wrapping.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (102, 'OL1', '2005-08-18 00:00:00', 'Can shipped full, refills not available.\r\nNeed to order new can if refill needed.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (103, 'SAFE', '2005-08-18 00:00:00', 'Safe is combination locked, combination not provided with safe.\r\nThis is rarely a problem as safes are typically blown up or dropped by customers.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (104, 'FC', '2005-08-19 00:00:00', 'Quantity varies, sold by the sack load.\r\nAll guaranteed to be bright and orange, and suitable for use as rabbit bait.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (105, 'TNT2', '2005-08-20 00:00:00', 'Included fuses are short and have been known to detonate too quickly for some customers.\r\nLonger fuses are available (item FU1) and should be recommended.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (106, 'TNT2', '2005-08-22 00:00:00', 'Matches not included, recommend purchase of matches or detonator (item DTNTR).');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (107, 'SAFE', '2005-08-23 00:00:00', 'Please note that no returns will be accepted if safe opened using explosives.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (108, 'ANV01', '2005-08-25 00:00:00', 'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (109, 'ANV03', '2005-09-01 00:00:00', 'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (110, 'FC', '2005-09-01 00:00:00', 'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (111, 'SLING', '2005-09-02 00:00:00', 'Shipped unassembled, requires common tools (including oversized hammer).');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (112, 'SAFE', '2005-09-02 00:00:00', 'Customer complaint:\r\nCircular hole in safe floor can apparently be easily cut with handsaw.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (113, 'ANV01', '2005-09-05 00:00:00', 'Customer complaint:\r\nNot heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.');
INSERT INTO `class`.`productnotes`(`note_id`, `prod_id`, `note_date`, `note_text`) VALUES (114, 'SAFE', '2005-09-07 00:00:00', 'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.\r\nComment forwarded to vendor.');

全文本搜索用的函数:

① Match()

② Against()

举个栗子:

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

Match()里面就是我们全文本搜索的索引

PS 这里三索引一定要和我们设置的索引(FULLTEXT)一样,而且次序也要一样.

Against就是我们要搜索的东西.

而且,全文本搜索默认是不区分大小写的.

结果如下:

全文本搜索的等级:

全文本搜索的等级就是,看我们搜索的词在行中出现的早晚

举个栗子,

rabbit 有两个结果,第一个结果在行的第三个单词初出现

第二个结果在行的第13个单词处出现

那么第一个的等级就比第二个高,下面我们来看个sql.验证哈.

SELECT note_text,
			Match(note_text) Against('rabbit') AS rank1
FROM productnotes;

书上给的栗子是rank ,但是现在rank已经是MySQL的关键字了,所以,我们不能使用rank做为别名了,这里我们使用的是rank1

结果如下:

使用查询扩展

查询扩展就是把可能的结果也放出来.

举个栗子

在没有使用扩展前:

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

布尔文本搜索

这个搜索是不需要一定建立索引,代价就是当数据量比较大的时候,运行的会很慢.

举个栗子:

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('+rabbit +bait' IN BOOLEAN MODE);

搜索匹配包含词rabbit和bait的行

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

没有指定操作符,这个搜索匹配包含rabbit和bait中的至少一个词的行

SELECT note_text 
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait

SELECT note_text 
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot'IN BOOLEAN MODE);

匹配rabbit和carrot,增加前者的等级,降低后者的等级

SELECT note_text 
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)'IN BOOLEAN MODE);

这个搜索匹配词safe和combination,降低后者的等级.

全文本搜索的使用说明

① 在索引全文本数据时,短词被忽略且从索引中排除.短词定义为那些具有3个或3个以下字符的词(如果需要,这个数目可以更改)

② MySQL带有一个内建的非用词(stopword)列表,这些词在索引全文本数据总是被忽略,如果需要,可以覆盖这个列表(请参考MySQL文档以了解如何完成此工作)

③ 许多词的出现的频率很高,搜索他们没有用处.MySQL有一条50%的规则.如果一个词出现50%以上,那么则将它作为一个非用词忽略,50%规则不适用 IN BOOLEAN MODE

④ 如果表中的行数少于三行,则全文本搜索不返回结果(因为每个词或则不出现,或者至少出现在50%的行中)

⑤ 忽略词中的单引号 例如 don’t 索引为dont

⑥ 不具有词分隔符(包括日语和汉语) 的语言不能合适的返回全文本搜索 结果

⑦ 仅在MyISAM数据库引擎中支持全文本搜索.

使用视图

视图是虚拟的表,视图只包含使用时,动态检索数据的查询.

① 为什么使用视图

①-①

重用SQL语句

①-②

简化复杂的SQL操作,在编写查询后,可以方便的重用它而不必知道它的基本查询细节

①-③

使用表的组成部分而不是整个表

①-④

保护数据,可以给用户授予表的特定部分的访问权限而不是整个表的访问权限

①-⑤

更改数据格式和表示.视图可返回与底层表的表示和格式不同的数据

PS:但是使用视图,可能会降低性能,这点一定要注意

② 视图的规则和限制

②-①

与表一样,视图必须唯一命名

②-②

对于可以创建的视图数目没有限制

②-③

为了创建视图,必须具有足够的访问权限,这些限制通常由数据库管理人员授予.

②-④

视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图

②-⑤

ORDER BY 可以用在视图中,但如果改视图从检索数据的SELECT语句中也含有ORDER BY,那么改视图中的ORDER BY将被覆盖

②-⑥

视图不能索引,也不能有关联的触发器或默认值

②-⑦

视图可以和表一起使用,例如,编写一条联结表和视图的SELECT语句.

③ 使用视图

视图用CREATE VIEW 语句来创建

使用SHOW CREATE VIEW viewname 来创建视图

用DROP来删除视图,语法 DROP VIEW viewname

更新视图时,可以先用DROP 在用CREATE ,也可以直接用CREATE OR REPLACE VIEW

查看我们建立好的视图

SHOW TABLE STATUS

这个既可以看到视图也可以看到表

③-① 利用视图来简化复杂的联结

例如

CREATE VIEW productcustomers AS 
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
	AND orderitems.order_num=orders.order_num;

这样我们就建立了一个名叫productcustomers的视图

然后我们在使用看看

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id='TNT2';

结果如下:

③-② 重新格式化检索出来的数据

没有任何修改之前的如下:

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

然后我们使用视图:

CREATE VIEW vendorlocations AS 
SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')')
			 AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT *
FROM vendorlocations;

结果如下:

③-③ 用视图过滤不想要的数据

下面的SQL语句过滤没有电子邮件的客户

使用下面的语句

CREATE VIEW customermaillist AS 
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL;

使用的SQL

SELECT *
FROM customermaillist;

③-④使用视图和计算字段

CREATE VIEW orderitemsexpanded AS 
SELECT order_num,
			 prod_id,
			 quantity,
			 item_price,
			 quantity*item_price  AS expanded_price
FROM orderitems

然后我们在来使用

SELECT *
FROM orderitemsexpanded
WHERE order_num=20005;

③-⑤更新视图

PS 如果视图中有以下操作,那么改视图就不能更新

分组:GROUP BY HAVBING

联结:

子查询:

并:

聚集函数:Min() : Count() ,Sum()等

DISTINCT:

导出(计算)列:

使用存储过程

① 创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
			SELECT Avg(prod_price) AS priceaverage
			FROM products;
END;

使用这个存储过程

CALL productpricing();

结果如下:

①-①创建一个有参数的过程

CREATE PROCEDURE productpricing(
		OUT pin DECIMAL(8,2),
		OUT pax DECIMAL(8,2),
		OUT pvg DECIMAL(8,2)
)
BEGIN
			SELECT Min(prod_price)
			INTO pin
			FROM products;
			SELECT Max(prod_price)
			INTO pax
			FROM products;
			SELECT Avg(prod_price)
			INTO pvg
			FROM products;
END;

这个存储过程有三个变量

pin,pax,pvg ,分别存储最小值,最大值,平均值.

关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者).

INTO 是用来保存到相应的变量里面的.

调用过程如下:

CALL productpricing(@pricelow,
										@pricehigh,
										@priceaverage);

在查看

SELECT @priceaverage;

截图如下:

② 删除存储过程

一个存储过程一旦创建,就会一直保存下来.

如果我们确定不想再用这个存储过程了,我们可以删除这个存储过程

DROP PROCEDURE productpricing;

③ 一个稍微复杂的栗子

-- Name：ordertotal
-- Parameters: onumber= order number
-- 						 taxable=0 if not taxable ,l if taxable
-- 						 ototal=order total variable
CREATE PROCEDURE ordertotal(
		IN onumber INT,
		IN taxable BOOLEAN,
		OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total, optionally adding tax'
BEGIN
		-- Declare variable for total
		DECLARE total DECIMAL(8,2);
		-- Declare tax percentage
		DECLARE taxrate INT DEFAULT 6;
		-- Get the order total
		SELECT Sum(item_price*quantity)
		FROM orderitems
		WHERE order_num=onumber
		
		INTO total;
		-- IS this taxable?就是检查taxable是不是真,那个taxable我们在程序已经给出
		IF taxable THEN
				-- Yes,so add taxrate to the total
				SELECT total+(total/100*taxrate) INTO total;
		END IF;
		-- ADD finally,save to out variable
		SELECT total INTO ototal;
END;

就是一个查询,输入的是order_num

然后有一个类似于bool的值,

为0的话,就直接把order_num的价格和质量相乘

然后输出他们的和

如果为1 的话,就还要加一个tarate就是税率的

默认为6-最后算的时候是0.06

下面是代码

CALL ordertotal(20005,0,@total);
SELECT @total;

然后一个有税率的:

CALL ordertotal(20005,1,@total);
SELECT @total;

截图

④ 检查存储过程

显示所有存储

SHOW PROCEDURE STATUS

结果如下:

如果要看一个存储空间的话

SHOW CREATE PROCEDURE ordertotal;

结果:

使用游标

定义:游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集,在存储了游标之后应用程序可以根据滚动其中的数据.

① 创建游标

CREATE PROCEDURE processorders()
BEGIN 
	DECLARE ordernumbers CURSOR 
	FOR 
	SELECT order_num FROM orders;
END;

这里我们创建了一个ordernumbers的游标,

② 关闭和打开游标

OPEN ordernumbers;

打开游标

CLOSE ordernumbers;	

关闭游标

CLOSE ordernumbers;

整合在一起

CREATE PROCEDURE processorders()
BEGIN 
	DECLARE ordernumbers CURSOR 
	FOR 
	SELECT order_num FROM ordres;
	
	OPEN ordernumbers;
	
	CLOSE ordernumbers;
END;

③ 一个复杂的栗子

CREATE PROCEDURE processorders()

BEGIN
		-- 定义哈本地的变量
		DECLARE done BOOLEAN DEFAULT 0;
		DECLARE o INT;
		DECLARE t DECIMAL(8,2);
		
		-- 定义游标
		DECLARE ordernumbers CURSOR 
		FOR 
		SELECT order_num FROM orders;
		
		-- 定义条件出现时,执行的代码
		-- 当 SQLSTATE '02000'出现的时候,设置 done=1
		DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
		SET done=1;
		
		-- 在重新建立一个表
		CREATE TABLE IF NOT EXISTS ordertotals
			(order_num INT,total DECIMAL(8,2));
		
		OPEN ordernumbers;
		
		-- 循环开始
		
		REPEAT 
				
				FETCH ordernumbers INTO o;
				
				CALL ordertotal(o,1,t);
				
				INSERT INTO ordertotals(order_num,total)
				VALUES(o,t);
				
				UNTIL done END REPEAT;
				
				CLOSE ordernumbers;

END;

使用这个过程

CALL processorders;
SELECT *
FROM ordertotals;

结果如下:

触发器

定义:触发其是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END之间的一组语句)

DELETE

INSERT

UPDATE

① 创建触发器

唯一的触发器名

触发器关联的表

触发器应该响应的活动(DELETE ,INSERT, UPDATE)

触发器何时执行

下面是一个简单的创建触发器的栗子:

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' INTO @ee;

然后我们在插入数据,在看返回的东西

INSERT INTO products
VALUES(777,1002,'Money',9.99,'Rich Man');
SELECT @ee;

结果如下:

② 删除触发器

DROP TRIGGER newproduct;

③ 使用触发器

③-① INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行.

在INSERT 触发器代码内,可引用一个名为NEW的虚拟表访问被插入的行

在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值)

对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值.

一个栗子：

CREATE TRIGGER newworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num INTO @aa;

INSERT INTO orders(order_date,cust_id)
VALUES(NOW(),10001);
SELECT @aa;

截图如下:

③-② DELETE触发器

DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,访问被删除的行.

OLD 中的值全都是只读的,不能更新

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW 
BEGIN
	INSERT INTO archive_orders(order_num,order_date,cust_id)
	VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;

作用是在任意订单被删除前执行此触发器,它使用一条INSERT语句将OLD中的值.

③-③UPDATE触发器

在UPDATEA触发器代码中,你可以引用一个名为OLD的虚拟表访问以前的(UPDATE)的值,引用一个NEW的虚拟表访问新更新的值.

在BEFORE UPDATE的触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值)

OLD中的值全都是只读,不能更新

下面是一个栗子:

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);

控制事务处理

① 一些基本概念

事务:指一组SQL语句

回退:指撤销指定SQL语句的过程

提交:指将未存储的SQL语句结果写入数据库表

保留点:指事务处理中设置的临时占位符,你可以对它发布回退(与回退整个事务处理不同)

② 控制事务处理

START TRANSACTION;

这个就是标识事务的开始

ROLLBACK;	

这个就是回退的意思

只有INSERT,UPDATE,DELETE语句.

你不能回退SELETE语句.

③ 使用COMMIT

COMMIT

这个是提交语句

使用保留点:

SAVEPOINT deletel;

更改默认的提交行为

SET autocommit=0;

autocommit标志决定是否自动提交更改.不管有没有COMMIT语句,设置autocommit为0,指示MySQL不自动提交更改.

全球化和本地化

字符集和校对顺序

字符集:字母和符号的集合

编码:某个字符集的内部表示

校对:规定字符如何比较的指令

使用字符集和校对顺序

-- 显示所有可用的字符集
SHOW CHARACTER SET;

-- 查看所有支持的校对的完整列表
SHOW COLLATION;

设置一个表的字符集,和单个列的字符集

CREATE TABLE mytable(
		columnn1 INT,
		columnn2 VARCHAR(10),
		column3  VARCHAR(10) CHARACTER SET latin1 COLLATE
		latin1_general_ci)
		DEFAULT CHARACTER SET hebrew
		COLLATE hebrew_general_ci;

安全管理

管理用户

USE mysql;
SELECT user FROM user;

截图

创建用户账号

-- 创建了一个新的用户,账号叫ben ,这里用了口令
-- 口令是p@$$Ord
CREATE USER ben IDENTIFIED BY 'p@$$Ord';

给用户改名

-- 我们给创建的用户改个名
RENAME USER ben TO bforta;

删除用户账号

DROP USER bforta;

设置访问权限

SHOW GRANTS FOR bforta;

结果如下:

USAGE 表示根本没有权限

我们现在给它加上权限

GRANT SELECT ON crashcourse .* TO bforta;

这个就是表示,允许用户在crashcourse数据库的所有表上使用SELECT

拥有了访问权限.

然后我们在看以下.

然后是移除权限

REVOKE SELECT ON crashcourse .* FROM  bforta;

改密码

SET PASSWORD FOR bforta = Password('n3w p@sswOrd');

数据库维护

就几个函数

ANALYZE

CHECK

–help

–safe-mode 服务器相关

–verbose 显示全文本消息

version 显示版本信息.

还有就是查看日志文件.

结尾

如果这篇文章对你有张帮助的话,可以用你高贵的小手给我点一个免费的赞吗

相信我，你也能变成光.

如果你有任何建议,或者是发现了我的错误,欢迎评论留言指出.