MySQL入门学习笔记之五

存储过程

存储过程是为以后的使用而保存的一条或多条MySQL语句的集合。可以视为批文件。

为什么使用

1 封装子容易使用的单元中,简化复杂的操作
2 不需要反复建立操作,保证数据的完整性
3 安全性—业务修改时只需修改存储过程的代码
4 提高性能—运行比基本SQL语句快

缺陷:
1 存储过程的编写比基本SQL语句复杂
2 可能没有创建存储过程的安全访问权限。

使用存储过程

执行存储过程—CALL(调用)

CALL 接受存储过程的名字和参数

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

创建存储过程

返回平均价格的存储过程

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

【临时更改命令行实用程序的分隔符】

DELIMITER //

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

DELIMITER ;

命令行实用程序使用//作为语句结束符。

CALL productpricing();

删除存储过程

DROP PROCEDURE productpricing;

只给出存储过程名即可。

存在才能删除

DROP PROCEDURE IF EXISTS

使用参数

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
    SELECT min(prod_pice)
    INTO pl
    FROM products;
    SELECT max(prod_price)
    INTO ph
    FROM products;
    SELECT avg(prod_price)
    INTO pa
    FROM products;
END;

MySQL支持3中类型参数:
IN(传递给存储过程)
OUT(从存储过程中传出)
INOUT(对存储过程传入传出)

调用

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

所有MySQL变量都必须以@开始。

上面的存储过程中的变量是OUT类型(输出)

SELECT @priceaverage;

输入输出例子

接受订单,返回订单合计

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

调用

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

智能存储过程

1 获取合计
2 把营业税有条件地添加到合计
3 返回合计

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;

    SELECT sum(item_price * quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total;

    IF taxable THEN
        SELECT total + (total/100*taxrate) INTO total;
    END IF;

    SELECT total INTO total;
END;

调用

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

BOOLEAN 1表示真,0表示假

检查存储过程

SHOW CREATE PROCEDURE ordertotal;

获取详细信息

SHOW PROCEDURE STATUS LIKE 'ordertotal';

游标

游标是存储在MySQL服务器上的数据库查询,不是select语句,而是被该语句,而是被语句检索出来的结果集。

使用游标

创建游标

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

打开关闭游标

打开

OPEN odernumbers;

关闭

CLOSE ordernumbers;

结束游标使用,必须关闭游标。

使用游标数据

游标被打开,可以使用FETCH语句访问每一行。

例:从游标中检索单个行

CREATE PROCEDURE processorders()
BEGIN
    DECLARE o INT;

    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    OPEN ordernumbers;

    FETCH ordernumbers INTO o;

    CLOSE ordernumbers;
END;

循环检索每一行

CREATE PROCEDURE processorders()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;

    // 游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    // 将结束标志绑定到游标
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    // 打开游标
    OPEN ordernumbers;
    // 循环所有列
    REPEAT
        // 获取行数据
        FETCH ordernumbers INTO o;
    // 循环结束
    UNTIL done END REPEAT;

    // 关闭游标
    CLOSE ordernumbers;
END;

SQLSTATE ‘02000’ 是未找到条件

触发器

触发器是MySQL响应以下任意语句而自动执行的MySQL语句(或者BEGIN和END之间的一组语句)。
DELETE
INSERT
UPDATE
其它语句不支持触发器

创建触发器

需要给出的信息:
1 唯一的触发器名
2 关联的表
3 响应的活动(DELETE/INSERT/UPDATE)
4 何时执行–之前或之后

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

AFTER INSERT 触发器在INSERT语句成功之后执行
FOR EACH ROW 对每个插入行执行

只有表才支持触发器,视图和临时表不支持

删除触发器

DROP TRIGGER newproduct;

使用触发器

INSERT触发器

这里写图片描述

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

【BEFORE】BEFORE用于数据验证(保证插入表的数据是需要的数据)

DELETE

UPDATE

事务

使用InnoDB数据库引擎

事务处理

维护数据库的完整性,操作要么完全执行,要么完全不执行

术语:
1 事务(transaction)——一组SQL语句
2 回滚(rollback)
3 提交(commit)
4 保留点(savepoint)——事务处理中设置的临时占位符,可以对其发布回滚。

控制事务处理

使用ROLLBACK–回滚(撤销)MySQL语句

SELECT * FROM ordertotals;  // 显示表非空
START TRANSACTION;   // 标识事务开始
DELETE FROM ordertotals;  // 删除表中数据
SELECT * FROM ordertotals; // 确定表为空
ROLLBACK;   // 回滚
SELECT * FROM ordertotals; // 显示表不为空

【回滚】
事务处理用来管理INSERT、UPDATE、DELETE语句,不能回退SELECT语句、不能回退CREATE、DROP操作(可以使用,但不被撤销)。

COMMIT

隐含提交(implicit commit),提交(写、保存)操作自动进行。

事务中,提交不会隐含进行。需使用COMMIT.

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
COMMIT;

【隐含事务关闭】
当COMMIT或ROLLBACK语句执行后,事务自动关闭。

使用保留点、占位符

SAVEPOINT delete1;  // 创建占位符
ROLLBACK TO delete1;  // 回退到保留点
RELEASE SAVEPOINT  // 释放保留点

更改默认的提交行为

使得MySQL不自动提交更改

SET autocommit = 0;

针对每个连接,而不是服务器。

全球化和本地化

字符集和校对顺序

查看所支持的字符集完整列表

SHOW CHARACTER SET;

查看所支持校对的完整列表

SHOW COLLATION;
SHOW VARIABLES LIKE 'character%'
SHOW VARIABLES LIKE 'collation%'

创建表时,指定字符集和校对

CREATE TABLE mytable
(
    column1 INT,
    column2 VARCHAR(20)
    column3 VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_general_ci   // 可以给列单独设置字符集和校对
) DEFAULT CHARACTER SET utf8
  COLLATE utf8_general_ci;

安全管理

访问控制

安全基础:用户对他们需要的数据具有适当的访问权。

管理用户

USE mysql;
SELECT user FROM user;

创建用户账号

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';  // 口令

重命名用户账号

RENAME USER ben TO newname;

删除用户账号

DROP USER newname;

设置访问权限

查看用户账号权限

SHOW GRANTS FOR newname;

授予用户newname在crashcourse.*(所有表)上使用SELECT

GRANT SELECT ON crashcourse.* TO newname;

撤销某特定权限

REVOKE SELECT ON crashcourse.* FROM newname;

各层次上权限

  • 整个服务器,使用GRANT ALL, REVOKE ALL
  • 数据库,使用ON database.*
  • 特定表,使用ON database.table
  • 特定列
  • 特定存储过程

另一种创建用户的方法

创建一个数据库用户z1,具有对sakila数据库中所有表的SELECT/INSERT权限
grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';

更改用户口令

SET PASSWORD FOR newname = password('n3w p@$$w0rd');

更改当前用户口令

SET PASSWORD = password('n3w p@$$w0rd');

数据库维护

备份数据

备份前使用FLUSH TABLES,将所有数据写入到磁盘。

mysqldump,可将所有数据库内容存储到某个外部文件
mysqlhotcopy
MySQL的BACKUP TABLE、SELECT INTO OUTFILE转存所有数据到外部文件。通过RESTORE TABLE复原。

维护

检查表键是否正确

ANALYZE TABLE orders;

检查问题

CHECK TABLE orders, orderitems;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值