数据库学习--mySQL

此文针对数据库database:crashbase，包含如下表

1. 表customers：客户信息
   cust_id cust_name cust_address cust_city cust_state cust_country cust_contact cust_email
2. 表orderitems:每种商品的订单信息
   order_num order_item prod_id quantity item_price
3. 表orders，每个客户的订单
   order_num order_date cust_id
4. 表productnotes，每种商品的备注
   note_id prod_id note_date prod_text
5. 表products，每种产品的进售信息
   prod_id vend_id prod_name prod_price prod_desc
6. 表vendors，所有的供应商
   vend_id vend_name vend_address vend_city vend_state vend_zip vend_country

orders存储实际的订单，而orderitems存储订购的各项物品。
这两个表使用称为主键（参阅第1章）的唯一ID互相关联。
这两个表又与包含客户和产品信息的其他表相关联。

一般关键字用大写，表名，行列名用小写 

show tables;#显示当前数据库中所有的表
use world;#启用另一个数据库
show columns from customers;#显示列
describe customers;#
show status;#用于显示广泛的服务器状态信息；
show grants;#用来显示授予用户（所有用户或特定用户）的安全权限
show errors;#
show warnings;#用来显示服务器错误或警告消息

#第五章：排序检索数据，SELECT，ORDER BY，DESC， LIMIT(限制行数)
select prod_name from products;
SELECT prod_id,prod_name,prod_price FROM products;#选择多列并显示
SELECT * FROM products;#*代表所有
SELECT DISTINCT vend_id from products;#DISTINCT:不重复
SELECT prod_name FROM products ORDER BY prod_name;#ORDER BY排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;#降序
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;#最高价格,LIMIT 1限制第一行
SELECT prod_id, prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
#note:ORDER BY必须是SELECT语句中的最后一条子句，可根据需要对一个或多个列进行排序

#第六章：过滤数据 SELECT + WHERE
SELECT prod_id, prod_price FROM products WHERE prod_price = 2.5;
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';#匹配时不区分大小写
SELECT prod_name, prod_price FROM products WHERE prod_price<=10;
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;#范围筛选·
SELECT prod_name,prod_price FROM products WHERE prod_price = NULL;#NULL检索空值

#第七章：过滤数据 WHERE子句的组合:AND OR
SELECT vend_id, prod_id, prod_name,prod_price FROM products 
WHERE vend_id = 1003 AND prod_price <=10 
ORDER BY prod_price DESC;#并且排了序，OR类似
#注意OR优先级大于AND,一块儿使用时要加小括号
SELECT prod_name, vend_id, prod_price FROM products
WHERE (vend_id = 1002 OR vend_id  = 1003) AND prod_price <= 10
ORDER BY prod_price DESC;
#IN代替WHERE(推荐,速度更快，更简洁，最大优点：可以包含其他SELECT语句，动态的创建WHERE子句)：指定条件范围
SELECT vend_id, prod_id, prod_name, prod_price FROM products
WHERE vend_id IN(1002,1003)
ORDER BY prod_price DESC;
SELECT vend_id, prod_id, prod_name, prod_price FROM products
WHERE vend_id NOT IN(1002,1003)
ORDER BY prod_price DESC;#NOT否定任何条件

#第八章：用通配符（用来匹配值的一部分的特殊字符）过滤 LIKE：% _
#搜索模式：由字面值、通配符或者两者组合构成的搜索条件，和正则表达式不同：LIKE匹配整个列
SELECT prod_id,prod_name FROM products
WHERE prod_name LIKE 'jet%'; #以jet开头
select prod_id,prod_name,prod_price FROM products
WHERE prod_price LIKE 10;#这样匹配数字是错误的
SELECT prod_id, prod_name FROM products
WHERE prod_name LIKE '%anvil%';#匹配anvil
#note:%不能匹配NULL,_类似LIKE但是_只能匹配一个字符

#第九章：用正则表达式进行搜索（匹配文本的特殊的串，字符集合）  REGEXP
SELECT prod_name FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name DESC;

SELECT prod_name FROM products
WHERE prod_name REGEXP '.000'# .表示匹配任意一个字符，将返回1000，2000
ORDER BY prod_name;

#正则表达式重复元字符：
/*
* 0个或多个匹配
+ 1个或多个匹配（等于{1,}）
? 0个或1个匹配（等于{0,1}）
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围（m不超过255）
*/

#OR匹配,利用|，不能加空格
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'#返回1 ton和2 ton和3 ton；[^123]匹配除这些字符外的任何东西
ORDER BY prod_name;
#匹配范围:[0-9]，[a-z]
SELECT prod_name FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;
#匹配特殊字符：.、[]|等，前面加\\转义，用两个反斜杠的原因：MySQL自己解释一个，正则表达式库解释另一个
SELECT vend_name FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;
#匹配多个实例
SELECT prod_name FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'#?使s可选,\\(转义(
ORDER BY prod_name;

SELECT prod_name FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'#[:digit:]匹配任意数字，{4}要求前面的字符出现4次 等价于 [0-9][0-9][0-9][0-9]
ORDER BY prod_name;
#定位符：匹配特定位置的文本 ^ $ [[:<:]]  [[:>:]]分别对应文本开始、结束、词开始、结束
#不使用数据库表进行正则表达式测试：（很有用）
SELECT 'hello' REGEXP '[0-9]';#不配返回0，匹配返回1

#第十章：创建计算字段（fieled）：运行时在SELECT语句内创建
#拼接字段，两列，如打印：name(location)，利用Concat()函数，而其他数据库一般用+或||
SELECT Concat(vend_name,'(',vend_country,')') FROM vendors ORDER BY vend_name;
#删除右侧多余空格来整理数据，利用RTrim()函数，删除左侧空格：LTrim()
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') FROM vendors ORDER BY vend_name;
#使用列别名显示，AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
#执行算术运算
SELECT prod_id,quantity,item_price, quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
#测试函数,利用SELETC+函数
SELECT 3*2;
SELECT TRim('abc ');
SELECT Now();#返回当前日期和时间

#第十一章：使用数据处理函数，函数不便于移植，因此要做好注释
#Upper() Lower() Left()返回串左边的字符 Length() Locate()找出串的一个字串 Soundex() SubString() 

#日期和时间处理函数
/*
日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和
有效地排序或过滤，并且节省物理存储空间。
一般，应用程序不使用用来存储日期和时间的格式，因此日期和时
间函数总是被用来读取、统计和处理这些值。由于这个原因，日期和时
间函数在MySQL语言中具有重要的作用。日期格式：yyyy-mm-dd
AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分
*/
SELECT cust_id, order_num,order_date FROM orders
WHERE order_date = '2005-09-01';#不可靠
SELECT cust_id,order_num,order_date FROM orders
WHERE Date(order_date) = '2005-09-01';#提取出日期来比较
#检索2005-09
SELECT cust_id,order_num,order_date FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
#数值处理函数
/*
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切
*/

#第十二章：汇总数据
/*
AVG() 返回某列的平均值,忽略NULL的行
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
*/
SELECT AVG(prod_price) AS avg_price FROM products
WHERE vend_id = 1003;

SELECT COUNT(cust_email) AS num_cust FROM customers;#对有电子邮件的客户计数，忽略NULL行

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
FROM products;

#第十三章：数据分组GROUP BY、HAVING
#创建分组
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id #通过vend_id进行逻辑分组
ORDER BY num_prods;#根据计数结果排序
#过滤分组：HAVING子句，不能用WHERE，因为WHERE基于分组聚集值而不是特定行值
SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;#HAVING基于行值

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

#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 BY ordertotal;

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


#第十四章：子查询，即嵌套在其他查询中的查询
#获取订购物品TNT2的所有客户，并检索客户信息，从内向外
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_state,
(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id)#涉及外部查询的子查询
AS orders 
FROM customers
ORDER BY cust_name;

#第十五章：联结表 ***重要！，一个表中存储另外一个表的主键（外键）即可形成联结，保证所有联结都有WHERE子句
#等值联结
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_name
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
#联结多个表
SELECT order_num, vend_name, prod_price, quantity
FROM vendors, products, orderitems
WHERE vendors.vend_id = products.vend_id
AND products.prod_id = orderitems.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 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';

#自然联结：使每个列只返回一次：对表使用通配符
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';

#外部联结：许多联结将一个表中的行与另一个表中的行相关联。但有时候会需
#要包含没有关联行的那些行
#检索所有客户及其订单
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders##LEFT:选择customers（左边）的所有行
ON customers.cust_id = orders.cust_id;

#使用带聚集函数的联结
#检索所有客户及其每个客户所下的订单数
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_prod
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

#第十七章：组合查询：UNION:任何具有多个WHERE子句的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);#将结果放在一个表中，这里可以用OR代替
#note：使用UNION ALL，MySQL不取消重复的行。

#第十八章：全文本搜索，对结果分等级，排序，出现的顺序
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
#等同于但是不排序
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

SELECT note_text,
Match(note_text) Against('rabbit') AS rank#每一行返回一个等级，没有的话等级为0，词靠前的等级更高
FROM productnotes;#此句语法有问题

#使用查询扩展p125,极大地增加了返回的行数
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION);

#布尔文本搜索：IN BOOLEAN MODE：p128


#第十九章：插入数据
#必须要指定列名
INSERT INTO customers(
#列名
)
VALUES(
),
(
);#可以同时插入多行

#还可以插入检索出的数据，INSERT 与 SELECT组合
INSERT INTO customers()
SELECT cust_id,cust_contact,...
FROM custnew;#两个表的列名不必一致


#第二十章：更新和删除数据，UPDATE DELETE
UPDATE customers
SET cust_name = 'The Fudds',
	cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;#如果没有WHERE将更新所有的行，一定要慎重！！！

#可以用UPDATE IGNORE  customers...复原
UPDATE IGNORE customers
SET cust_name = 'The Fudds',
	cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

#删除DELETE，  TRUNCATE TABLE

#更新和删除使用原则
/*
    除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE
子句的UPDATE或DELETE语句。
 保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能
像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进
行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不
正确。
 使用强制实施引用完整性的数据库（关于这个内容，请参阅第15
章），这样MySQL将不允许删除具有与其他表相关联的数据的行。
*/
#MySQL没有undo撤销功能

#第二十一章：创建和操纵表，看的不仔细
CREATE TABLE people
(
pId int NOT NULL auto_increment,
pName char(50) NOT NULL,
pGender char(10) NOT NULL,
pAge int NOT NULL,
PRIMARY KEY(pId)
)ENGINE=InnoDB;

INSERT INTO people
(
pId,pName,pGender,pAge
)
VALUES(1,'wjj','male','22');

SELECT pId,pName,pGender,pAge FROM people WHERE people.pName = 'wjj';

#第二十二章：使用视图：包含的是一个SQL查询，视图是一种虚拟的表
/*
创建一个名为productcustomers的视图，它联结三个
表，以返回已订购了任意产品的所有客户的列表。如果执行
SELECT * FROM productcustomers，将列出订购了任意产品的客户。
*/
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;

select * FROM productcustomers;

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

#用视图重新格式化检索出的数据
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
#可重用
SELECT * FROM vendorlocations;

#用视图过滤掉不想要的数据
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

SELECT * FROM customeremaillist;

#使用视图简化计算字段
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
ORDER BY expanded_price;

#更新视图：一般来说视图只用于检索不用于更新

#第二十三章：使用存储过程（可以视为批文件）：为以后的使用而保存的一条或多条MySQL语句的集合
#执行存储过程（调用）

#创建：一个返回产品平均价格的存储过程
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverge
    FROM products;
END // #没有参数，没有返回数据
#DELIMITER //告诉命令行实用程序使用//作为新的语
#句结束分隔符，可以看到标志存储过程结束的END定义为END
#//而不是END;
DELIMITER ;#除\符号外，任何字符都可以用作语句分隔符

#使用
CALL productpricing();#执行刚创建的存储过程并显示返回的结果
#删除
DROP PROCEDURE productpricing;


#使用参数：所有变量都必须以@开始
/*
关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。
MySQL支持IN（传递给存储过程）、
OUT（从存储过程传出，如这里所用）
和INOUT（对存储过程传入和传出）类型的参数。
*/
DELIMITER //
CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8,2),
	OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
	SELECT Min(prod_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT Avg(prod_price)
    INTO pa
    FROM products;
END //

DELIMITER ;
DROP PROCEDURE productpricing;
CALL productpricing(@pricelow,@pricehigh,@priceaverage);

#显示参数值
SELECT @pricelow, @pricehigh, @priceaverage;

DELIMITER //
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 //

DROP PROCEDURE ordertotal//

DELIMITER ;
CALL ordertotal(20005, @total);
SELECT @total;
CALL ordertotal(20009,@total);#可重复调用
SELECT @total;

 #建立智能存储过程
 DELIMITER //
 -- Name: ordertotal
 -- Parameters: onumber = order number
 --      		taxable = 0 if not taxable, 1 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 total DECIMAL(8,2);
    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
    IF taxable THEN
		SELECT total + (total/100*taxrate) INTO total;
	END IF;
		SELECT total INTO ototal;
 END//
  DELIMITER ;
 DROP PROCEDURE ordertotal;
 CALL ordertotal(20005,0,@total);
 SELECT @total;
  CALL ordertotal(20005,1,@total);
 SELECT @total;
 
 #检查存储过程
 SHOW CREATE PROCEDURE ordertotal;
 
#第二十四章：使用游标：前进后退一行或多行，用于交互式应用

#游标（cursor）是一个存储在MySQL服务器上的数据库查询，
#它不是一条SELECT语句，而是被该语句检索出来的结果集。

DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
	-- 声明定义游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- 打开游标
    OPEN ordernumbers;
    -- 关闭,释放资源
    CLOSE ordernumbers;
END//

DELIMiTER ;

DELIMITER //
#使用游标数据
CREATE PROCEDURE processorders1()
BEGIN
	DECLARE o INT;    
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;    
    OPEN ordernumbers;    
    -- Get order number
    FETCH ordernumbers INTO o;    #指定检索什么数据(所需的列)
    CLOSE ordernumbers;
END//#此例对检索出的数据未作处理

#循环检索数据
CREATE PROCEDURE processorders2()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;#句柄，必须在游标之后定义
    
    OPEN ordernumbers;    
	-- Loop through all rows
    REPEAT
		-- Get order number
        FETCH ordernumbers INTO o;
        #循环内可以放入任意需要的处理
	-- End of loop
    UNTIL done END REPEAT;    
    CLOSE ordernumbers;
END//

#循环检索数据并对索引出的数据处理
CREATE PROCEDURE processorders3()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;#句柄，必须在游标之后定义
    
    -- Create a table to store the results
    CREATE TABLE IF NOT EXISTS `ordertotals`(
		`order_num` INT,
        `total` DECIMAL(8,2),
        PRIMARY KEY(`order_num`)
        )ENGINE = InnoDB;
    
    OPEN ordernumbers;    
	-- Loop through all rows
    REPEAT
		-- Get order number
        FETCH ordernumbers INTO o;
        #循环内可以放入任意需要的处理
        -- Get the total for this order
        CALL ordertotal(o,1,t);
        INSERT INTO ordertotals(order_num,total)
        VALUES(o,t);
	-- End of loop
	UNTIL done END REPEAT;    	
    CLOSE ordernumbers;
END//#note:不知道为什么创建不成功

DELIMITER ;
DROP PROCEDURE processorders3;
SELECT * FROM ordertotals;

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB;

DROP TABLE runoob_tbl
;
    -- Create a table to store the results
    CREATE TABLE IF NOT EXISTS `ordertotals`(
		`order_num` INT,
        `total` DECIMAL(8,2),
        PRIMARY KEY(`order_num`)
        )ENGINE = InnoDB;

#第二十五章，使用触发器：某条语句在事件发生时自动执行，针对DELETE、INSERT、UPDATE
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' into @ee;

select * from products;

DROP TRIGGER newproduct;


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

INSERT INTO orders(order_date, cust_id)
VALUE(Now(),10001);
SELECT @ee;

#DELETE触发器
DELIMITER //
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//

#UPDATE触发器

#第二十六章：管理事务处理(ROLLBACK、COMMIT、保留点、更改默认的提交行为)
DELIMITER ;
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;


#第二十七章：使用字符集和校对顺序
#第二十八章：安全管理
/*
MySQL用户账号和信息存储在名为mysql的MySQL数据库中。一般
不需要直接访问mysql数据库和表（你稍后会明白这一点），但有时需要
直接访问。需要直接访问它的时机之一是在需要获得所有用户账号列表
时
*/
USE mysql;
SELECT user FROM user;
#获取数据库所有的表名称
SELECT table_name FROM information_schema.Tables ORDER BY table_name;#结果中包含user
#创建用户账号
CREATE USER wjj IDENTIFIED BY 'aaaaaa';
RENAME USER wjj TO wangjinju;
DROP USER wangjinju;

SHOW GRANTS FOR wangjinju;#显示权限
#赋权限：GRANT
GRANT SELECT ON crashcourse.* TO wangjinju;#只读权限SELECT
REVOKE SELECT ON crashcourse.* FROM wangjinju;#撤销权限
#更改密码（口令）
SET PASSWORD FOR wangjinju = Password('aaaaaab');

#第二十九章：数据库维护

#备份数据
/*
使用命令行实用程序mysqldump转储所有数据库内容到某个外部
文件。在进行常规备份前这个实用程序应该正常运行，以便能正
确地备份转储文件。
 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据
（并非所有数据库引擎都支持这个实用程序）。
 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所
有数据到某个外部文件。这两条语句都接受将要创建的系统文件
名，此系统文件必须不存在，否则会出错。数据可以用RESTORE
TABLE来复原。
*/

#数据库维护、检查
ANALYZE TABLE orders;#检查表键是否正确
CHECK TABLE orders,orderitems;

#如果从一个表中删除大量数据，
#应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性
OPTIMIZE TABLE orders;