这是我学习的
MySQL必知必会的笔记
因本人才疏学浅,如有错误之处,还请见谅
在使用之前,需要先导入一个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 中使用表达式,不能使用别者.
④ 如果分组中具有NULL 值,那么这些NULL值将分为一组.
⑤ 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的作用就是
列出具有2个以上(包括两个) ,价格为10以上的产品的供应商
④ 分组和排序
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 显示版本信息.
还有就是查看日志文件.
结尾
如果这篇文章对你有张帮助的话,可以用你高贵的小手给我点一个免费的赞吗
相信我,你也能变成光.
如果你有任何建议,或者是发现了我的错误,欢迎评论留言指出.