您的位置:澳门新葡8455最新网站 > 数据库管理 > 读书笔记4,存储过程和用户权限

读书笔记4,存储过程和用户权限

发布时间:2019-10-13 09:12编辑:数据库管理浏览(64)

    一、联结表

      数据仍使用前文中的数据。

    第二十二章 使用视图

    • 视图是一张虚拟表
    • 为什么使用视图
      • 重用SQL语句
      • 简化复杂的SQL操作
      • 使用表的组成部分而不是整张表
      • 保护数据,只允许访问表的部分而不是整张表
      • 更改数据格式和表示,视图可以返回与底层表格式不同的数据
    • 视图的使用规则

      • 唯一命名
      • 视图数目没有限制
      • 创建视图需要权限
      • 视图可以嵌套,可以利用其它视图来构造视图
      • 视图可以使用 ORDER BY,但是 SELECT 中的 ORDER BY 会覆盖视图中的 ORDER BY
      • 视图不能索引,也不能有关联的触发器或默认值
      • 视图可以和表一起使用,可以联结表和视图
    • 使用视图

      • CREATE VIEW 创建视图
      • SHOW CREATE VIEW viewname; 查看所创建的视图
      • DROP 删除视图
      • 更新视图,可以先删除再创建,也可以使用 CREATE OR PEPLACE VIEW
    • 利用视图简化复杂的联结:创建临时表,增加灵活性

    • 用视图重新格式化检索出的数据:视图可以根据需要创建列名

    • 利用视图过滤不想要的数据:通过视图过滤数据后代替表使用

    • 利用视图简化计算字段

    • 视图一般用于检索,不用于更新

      1、子查询

      作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

    -- 作为查询条件使用
    -- 查看TNT2订单对应的客户ip(order表)
    SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2');
    -- 根据TNT2订单的客户ip查看客户信息(cust表)
    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;
    

    第二十三章 使用存储过程

    • 存储过程是一条或者多条SQL语句的集合,类似于批文件

    • 使用存储过程的理由(简单、安全、高性能)

      • 简化复杂操作
      • 防止错误
      • 简化对变动的管理
      • 提高性能
    • 创建和使用存储过程都需要权限

    • 创建存储过程

      //创建一个名为productpricing的存储过程
      CREATE PROCECURE productpricing()
      BEGIN
          SELECT Avg(prod_price) AS priceaverage
          FROM products;
      END
      
    • 执行存储过程

      CALL productpricing (
          @pricelow,
          @pricehigh,
          @priceaverage
      );
      
    • 使用存储过程

      CALL productpricing();  
      
    • 删除存储过程

      DROP PROCEDURE productpricing IF EXISTS;
      

      2、创建链接

     图片示例  链接方式 关键字 语句示例
      内连接 INNER JOIN ... ON SELECT <select_list> FROM A INNER JOIN B ON A.key = B.key
    左外连接 LEFT JOIN ... ON SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key
    左外连接 LEFT JOIN ... ON ... WHERE B.key IS NULL SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key WHERE B.key IS NULL
    右外连接 RIGHT JOIN ... ON SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key
    右外连接 RIGHT JOIN ... ON ... WHERE A.key IS NULL SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key WHERE A.key IS NULL
    全外连接 UNION

    SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key

    UNION

    SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key

    全外连接 (WHER IS NULL) UNION ... (WHER IS NULL)

    SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key WHERE B.key IS NULL

    UNION

    SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key WHERE A.key IS NULL

       用例:

    -- 笛卡尔积:n * m
    -- SELECT * FROM vendors, products;
    SELECT orders.cust_id, cust_name FROM orders CROSS JOIN customers;-- 没有WHERE子句
    -- 内连接
    -- SELECT * FROM vendors, products WHERE vendors.vend_id = products.vend_id;
    SELECT * FROM vendors INNER JOIN products on vendors.vend_id = products.vend_id;
    -- 左外连接(a),根据A表相应字段取列
    SELECT orders.cust_id, cust_name FROM orders LEFT JOIN customers on customers.cust_id = orders.cust_id;
    -- 左外连接b,只取A表中无B表相应字段的列
    SELECT vendors.vend_id, vend_name, vend_country  FROM vendors LEFT JOIN products on vendors.vend_id = products.vend_id where products.vend_id IS NULL ORDER BY vend_id;
    -- 右外连接(a),同左外连接(a)
    SELECT orders.cust_id, cust_name FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id;
    -- 右外连接(b),同左外连接(b)
    SELECT customers.cust_id, cust_name FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL;
    
    -- 全外连接,两者id相等的行和不相等的行都保留
    SELECT customers.cust_id, orders.order_num FROM orders LEFT JOIN customers on customers.cust_id = orders.cust_id
    UNION
    SELECT customers.cust_id, orders.order_num FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id;
    -- 全外连接,只保留两者id不相等的行,这里两个WHERE子句一样是因为customers的id字段完全包含orders中的字段
    SELECT customers.cust_id, orders.order_num FROM orders LEFT JOIN customers on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL
    UNION
    SELECT customers.cust_id, orders.order_num FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL
    

    第二十四章 使用游标

    • 游标(cursor)是一个存储在MySQL服务器上面的数据库查询,可以在检索的结果集中滚动到某一行进行操作

    • 使用游标

      • 声明游标
      • 打开游标
      • 使用游标取出需要的行并处理
      • 关闭游标

      3、视图

      视图是虚拟的表。它只包含使用时动态检索数据的查询,换言之,视图存储查询语句。保存查询语句可以使用视图。视图的一些规则:

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

      - 视图数目没有限制。

      - 为了创建视图,必须具有足够的访问权限。

      - 视图可以嵌套。

      - 视图查询中不要使用GROUP BY。

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

    -- 创建视图
    -- CREATE VIEW viewname AS SELECT *
    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;
    
    CREATE VIEW orderitemsexpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems;
    
    -- 执行视图查询时会首先执行视图
    SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id ='FB';
    SELECT * FROM orderitemsexpanded WHERE order_num = 20009;
    -- 删除视图
    -- DROP VIEW viewname;
    DROP VIEW productcustomers;
    

    第二十五章 使用触发器

    • 触发器是在事件发生时自动执行的一条MySQL语句

      • DELETE
      • INSERT
      • UPDATE
    • 创建触发器

      • 唯一的触发器名
      • 关联的表
      • 响应的活动(DELETE、INSERT或UPDATE)
      • 何时执行(处理之前还是之后)
      • 只有表才支持触发器
      • 触发器按每个表每个事件每次地定义
      • 每个表每个事件每次只允许一个触发器
      • 每个表最多支持6个触发器(INSERT UPDATE 和 DELETE 的之前和之后)
    // 创建一个名为 newproduct 的触发器,在 products 表每次 INSERT 之后输出 'Product added'
    CREATE TRIGGER newproduct AFTER INSERT ON products 
    FOR EACH ROW SELECT 'Product added';
    
    • 删除触发器
      • DELETE TRIGGER newproduct;
    • 使用触发器
      • INSERT 触发器
        • 可以引用一个名为 NEW 的虚拟表,访问被插入的行
        • 通常 BEFORE 触发器用于数据验证,AFTER 触发器用于查询数据
      • DELETE 触发器
        • 可以引用一个名为 OLD 的虚拟表,访问被删除的行
      • UPDATE 触发器
        • 可以引用一个名为 OLD 的虚拟表,访问被更新以前的值
        • 可以引用一个名为 NEW 的虚拟表,访问被更新以后的值

    二、存储过程

    第二十六章 管理事务处理

    • 事务处理用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行

    • 不是所有的数据库引擎都支持事务处理

      • MyISAM 不支持
      • InnoDB 支持
    • 几个术语

      • 事务 一组SQL语句
      • 回退 撤销指定SQL语句
      • 提交 将未存储的SQL语句结果写入数据库表
      • 保留点 指事务处理中设置的临时占位符
    • 控制事务处理

      • 关键在于将SQL语句组分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退
    • 事务开始 START TRANSACTION

    • 回退 ROLLBACK

      • ROLLBACK 只能在一个事务处理内使用,在执行一条 START TRANSACTION 命令之后
      • INSERT UPDATE DELETE 可以回退
      • SELECT CREATE DROP 不能回退
    • 提交 COMMIT

      • 为了保证 SQL 操作不是被部分处理,可以放在事务块中处理
      • 在事务处理块中,提交不会隐含的提交,需要使用 COMMIT 明确的提交
      • 只有事务块中全部语句都正确执行,才会执行 COMMIT
    • 当 ROLLBACK 和 COMMIT 执行后,事务会自动关闭

    • 使用保留点

      • 简单的 ROLLBACK 和 COMMIT 可以写入或者撤销整个事务处理,更复杂的可能要部分提交或者回退
      • 添加占位符,如果需要回退,可以回退到某个占位符(保留点)
      • 创建保留点 SAVEPOINT delete;
      • 回退到保留点 ROLLBACK TO delete;

      1、存储过程

    第二十七章 全球化和本地化

    • 字符集 字母和符号的集合
    • 编码 字符集的内部表示
    • 校对 为规定的字符如何比较的指令
    • 可以给特定的表或者列定义字符集和校对,不指定的话就使用数据库默认

      1.简介

      存储过程是存储在数据库目录中的一段声明性SQL语句。它像是编程语言中的函数或者可执行代码块。通过声明一段可执行的SQL语句,既可以避免一行行的输入SQL语句,又可以将这段SQL语句重复使用。

      存储过程的优点:

      - 通常存储过程有助于提高应用程序的性能。

      - 存储过程有助于减少应用程序和数据库服务器之间的流量,因为应用程序不必发送多个冗长的SQL语句,而只能发送存储过程的名称和参数。

      - 存储的程序对任何应用程序都是可重用的和透明的。

      - 存储的程序是安全的。

      存储过程的缺点:

      - 如果使用大量存储过程,那么使用这些存储过程的每个连接的内存使用量将会大大增加。

      - 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。

      - 很难调试存储过程。

      - 开发和维护存储过程并不容易。

    第二十八章 安全管理

    • 访问控制
      • 用户对需要的数据具有适当的访问权
      • root是超级管理员,应该创建一系列的账号,开放不同的权限,给相关的人员使用
    • 用户管理
      • MySQL的用户账号和信息存储在mysql表中
      • 创建用户
        • CREATE USER ben IDENTIFIED BY 'p@$$wOrd';
        • REMANE USER ben TO mark;
      • 删除用户
        • DROP USER mark;
      • 设置访问权限
        • 查看用户权限 SHOW GRANTS FOR mark;
        • 使用 GRANT 设置权限
          • 要授予的权限
          • 被授予权限的数据库或者表
          • 用户名
        • 使用 REVOKE 撤销权限
        • GRANT 和 REVOKE 可以在几个层次上控制访问权限
          • 整个服务器
          • 整个数据库
          • 特定的表
          • 特定的列
          • 特定的存储过程
        • 更改密码 SET PASSWORD FOR mark = PASSWORD('123456');
          • 不指定用户名时,修改的是当前登录用户的密码

      2.使用

    -- 创建存储过程
    DELIMITER // -- DELIMITER // 和DELIMITER;用于划分一块范围来声明存储过程
    CREATE PROCEDURE GetAllProducts()-- CRAET PROCEDURE 创建一个存储过程
       BEGIN-- 存储过程的主体的开始
       DECLARE id INT(20) DEFAULT 1003;-- DECLARE variable datatype(size) DEFAULT value,声明局部变量;它只能在BEGIN和END之间生效
        SET id = 1001;-- 变量赋值
       SELECT * FROM products WHERE vend_id = id;-- SQL语句
       END //-- 存储过程的主体的结束,结尾用//
    DELIMITER ;
    
    -- 调用存储过程
    CALL GetAllProducts();
    -- 删除存储过程
    DROP PROCEDURE IF EXISTS `GetAllProducts`;
    
    -- 设置参数
    DELIMITER //
    CREATE PROCEDURE GetProductsByVendId(IN id INT(20), OUT outcome VARCHAR(20))-- 参数默认前缀是IN,即只允许调用函数时给参数传递值;OUT则表示将存储过程的运行结果传递出去,它是个单值参数;
        BEGIN
        SELECT count(*) INTO outcome FROM products WHERE vend_id = id;
        END //
    DELIMITER ;
    CALL GetProductsByVendId(1003, @outcome);-- 不直接打印结果,而是把结果传递给了全局变量@outcome
    SELECT @outcome;-- 设置全局变量可以用SET variable = value;
    
    -- 条件语句
    /* IF condition THEN 
        sql
    ELSEIF condition THEN
        sql
    ...
    ELSE 
        sql
    END IF */
    
    DELIMITER //
    CREATE PROCEDURE GetPriceLevel(IN prod_id CHAR(10), OUT price_level VARCHAR(20))
        BEGIN
        DECLARE price DECIMAL(8,2);
        SELECT prod_price INTO price FROM products WHERE products.prod_id = prod_id;
    
        IF price <= 5 THEN
            SET price_level = 'cheap';
        ELSEIF (price > 5 AND price <= 10) THEN
            SET price_level = 'ordinary';
        ELSE
            SET price_level = 'expensive';
        END IF;
        END //
    DELIMITER ;
    CALL GetPriceLevel('ANV01', @price_list);
    SELECT @price_list;
    
    -- 循环语句
    /* WHILE condition DO
       statements
    END WHILE */
    -- 函数 略
    -- 游标 https://www.yiibai.com/mysql/cursor.htm
    

    第二十九章 数据库维护

    • 备份数据
      • mysqldump
      • mysqlhotcopy
      • BACKUO TABLE 或者 SELECT INTO OUTFILE
    • 数据库维护
      • ANALYZE TABLE 检查表键是否正常
      • CHECK TABLE
      • OPTIMIZE TABLE
    • 诊断启动问题
      • --help 显示帮助
      • --safe-mode 装载减去某些最佳配置的服务器
      • --verbose 显示全文消息
      • --version 显示版本信息后退出
    • 查看日志文件
      • 错误日志 hostname.err
      • 查询日志 hostname.log
      • 二进制日志 hostname-bin
      • 缓慢查询日志 hostname-show.log 记录执行缓慢的任何查询,优化数据库时很有用

      2、事务处理

    第三十章 改善性能

    • 硬件
    • 内存分配、缓冲区大小
    • 关注执行缓慢的进程
    • 试验找出最佳的SQL语句
    • 存储过程一般比一条条执行sql要快
    • 总是使用正确的数据类型
    • 不要检索比需求还多的数据
    • 合理使用索引
    • 使用SELECT + UNION 代替一系列复杂的 OR 条件
    • 每条规则在某些条件下都会被打破

      1.概念

      事务处理(transaction processing)用于保证SQL操作的完整性。它提供一种处理机制来应对SQL或者其它环境因素可能造成的异常结果。事务处理中的几个术语:

      - 事务(transaction),指一组SQL语句。

      - 回退(rollback),指撤销指定SQL语句的过程。

      - 提交(commit),指将未存储的SQL语句结果些人数据库表。

      - 保留点(savepoint),指事务处理中设置的临时占位符(placeholder),它可以回退。

      事务处理用来管理(可以回退)INSERT、UPDATE和DELETE语句,不能回退SELECT语句,也不能回退CREATE或者DROP操作。

      在MySQL中,事务开始使用COMMIT或ROLLBACK语句开始工作和结束。开始和结束语句的SQL命令之间形成了大量的事务。

      2.ACID特性

      事务有以下四个标准属性的缩写ACID,通常被称为:

      - 原子性: 指事务是一个不可再分割的工作单元,事务中的操作要么都发生,要么都不发生。

      - 一致性: 在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

      - 隔离性: 多个事务并发访问时,事务之间是隔离的,一个事务不应该影响其它事务运行效果。

      - 持久性: 在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。

      3.简单用例

    -- MySQL默认出错自动回滚,没出错自动提交
    BEGIN;
    SAVEPOINT place;
    INSERT INTO orders VALUES(20010, '2005-09-08 00:00:00', 10001);
    ROLLBACK;-- ROLLBACK place
    COMMIT
    

      在COMMIT提交之前,ROLLBACK语句来撤销事务中所做的每一项工作,即便工作是成功的也可以撤销,它可以一直撤销到事务的开始。在COMMIT提交之后,只能使用DELETE,INSERT或者UPDATE语句,ROLLBACK是不行的。

    三、访问控制和用户权限管理

      内容摘自MySQL教程()

      1、访问控制

      当客户端连接到服务器时,MySQL访问控制有两个阶段:

      - 连接验证:连接到MySQL数据库服务器的客户端需要有一个有效的用户名和密码。此外,客户端连接的主机必须与MySQL授权表中的主机相匹配。

      - 请求验证:当连接成功建立后,对于客户端发出的每个语句,MySQL会检查客户端是否具有足够的权限来执行该特定语句。 MySQL能够检查数据库,表和字段级别的权限。

      MySQL安装程序自动创建一个名为mysql的数据库。 mysql数据库包含五个主要的授权表(user、db、host、tables_priv和columns_priv),它们存储了不同级别上对用户权限的设置。并可通过GRANT和REVOKE等语句间接操作这些表。

    表名 权限
    mysql.user  包含用户帐户和全局权限列。MySQL使用user表来接受或拒绝来自主机的连接。 在user表中授予的权限对MySQL服务器上的所有数据库都有效
    mysql.db  包含数据库级权限。MySQL使用数据库表来确定用户可以访问哪个数据库以及哪个主机。在db表中的数据库级授予的特权适用于数据库,所有对象属于该数据库,例如表,触发器,视图,存储过程等
    mysql.table_priv  包含表级别权限,权限适用于表中所有列
    mysql.columns_priv  授予的权限只适用于字段
    mysql.procs_priv  包含存储函数和存储过程的权限

      2、用户权限管理

      MySQL8以上提供了role对象进行多用户权限管理。

    /* GRANT
        priv_type [(column_list)]
          [, priv_type [(column_list)]] ...
        ON [object_type] priv_level
        TO user_specification [, user_specification] ...
        [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
        [WITH with_option ...]
    */
    
    -- 创建单个用户账户
    -- CREATE USER username@localhost IDENTIFIED BY passwd
    CREATE USER dbadmin@localhost IDENTIFIED BY 'pwd123';
    -- 查看该用户权限
    SHOW GRANTS FOR dbadmin@localhost;
    -- 赋予该用户操作权限
    GRANT ALL PRIVILEGES ON test.customer TO dbadmin@localhost;-- 可以到mysql.table_priv授权表中查看设置
    -- 更新
    FLUSH PRIVILEGES;
    SHOW GRANTS FOR dbadmin@localhost;
    SELECT * FROM mysql.user;
    -- 删除权限
    REVOKE ALL PRIVILEGES ON test.customer FROM dbadmin@localhost;
    

      dbadmin@localhost用来指定账户名和主机地址。

      GRANT子句设置用户权限,包括对数据库级、表级、字段、查询语句等的设置。

    权限 含义 全局 数据库 过程 代理
    ALL [PRIVILEGES] 授予除了GRANT OPTION之外的指定访问级别的所有权限            
    ALTER 允许用户使用ALTER TABLE语句 x x x      
    ALTER ROUTINE 允许用户更改或删除存储程序 x x     x  
    CREATE 允许用户创建数据库和表 x x x      
    CREATE ROUTINE x x          
    CREATE TABLESPACE 允许用户创建,更改或删除表空间和日志文件组 x          
    CREATE TEMPORARY TABLES 允许用户使用CREATE TEMPORARY TABLE创建临时表 x x        
    CREATE USER 允许用户使用CREATE USERDROP USERRENAME USERREVOKE ALL PRIVILEGES语句。 x          
    CREATE VIEW 允许用户创建或修改视图 x x x      
    DELETE 允许用户使用DELETE x x x      
    DROP 允许用户删除数据库,表和视图 x x x      
    EVENT 能够使用事件计划的事件 x x        
    EXECUTE 允许用户执行存储过程/存储函数 x x        
    FILE 允许用户读取数据库目录中的任何文件 x          
    GRANT OPTION 允许用户有权授予或撤销其他帐户的权限 x x x   x x
    INDEX 允许用户创建或删除索引 x x x      
    INSERT 允许用户使用INSERT语句 x x x x    
    LOCK TABLES 允许用户在具有SELECT权限的表上使用LOCK TABLES x x        
    PROCESS 允许用户使用SHOW PROCESSLIST语句查看所有进程 x          
    PROXY 启用用户代理            
    REFERENCES 允许用户创建外键 x x x x    
    RELOAD 允许用户使用FLUSH操作 x          
    REPLICATION CLIENT 允许用户查询主服务器或从服务器的位置 x          
    REPLICATION SLAVE 允许用户使用复制从站从主机读取二进制日志事件 x          
    SELECT 允许用户使用SELECT语句 x x x x    
    SHOW DATABASES 允许用户显示所有数据库 x          
    SHOW VIEW 允许用户使用SHOW CREATE VIEW语句 x x x      
    SHUTDOWN 允许用户使用mysqladmin shutdown命令 x          
    SUPER 允许用户使用其他管理操作,如CHANGE MASTER TOKILLPURGE BINARY LOGSSET GLOBALmysqladmin命令 x          
    TRIGGER 允许用户使用TRIGGER操作 x x x      
    UPDATE 允许用户使用UPDATE语句 x x x x    
    USAGE 相当于“无权限”          

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:读书笔记4,存储过程和用户权限

    关键词: