欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

MySQL中一些深入概念整理

程序员文章站 2024-01-01 11:22:16
...

1. 视图 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询（即：包含一个SQL查询），仅仅是用来查看存储在别处的数据的一种设施。 视图基本操作：（1）创建：CREATE VIEW；（2）查看：SHOW CREATE VIEW viewname；（3）删除：DROP

1. 视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询（即：包含一个SQL查询），仅仅是用来查看存储在别处的数据的一种设施。

视图基本操作：（1）创建：CREATE VIEW；（2）查看：SHOW CREATE VIEW viewname；（3）删除：DROP VIEW viewname；（4）更新：CREATE OR REPLACE VIEW。

视图可以嵌套，但不能索引，也不能有关联的触发器或默认值。并非所有视图都是可更新的，如果MySQL不能正确确定被更新的基数据，则不允许更新（包括插入和删除）。

视图不能更新的情况：（1）分组，使用GROUP BY和HAVING；（2）联接；（3）子查询；（4）并；（5）聚集函数，Min/Count/Sum等；（6）DISTINCT；（7）导出列。

视图常见应用：（1）隐藏复杂的SQL，这通常都会涉及联接；（2） 重新格式化检索出的数据；（3）过滤不想要的数据；（4）简化计算字段的使用。

			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 cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';

2. 存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合（实际上是一种函数），可将其视为批处理文件，虽然它们的作用不仅限于批处理。使用存储过程有3个主要的好处：简单、安全、高性能。

  1. 执行：CALL procedure_name(parameter_list);
  2. 创建：CREATE PROCEDURE procedure_name(parameter_list) BEGIN ...... END;
  3. 删除：DROP PROCEDUREIF EXISTS procedure_name;
  4. 查看：SHOW CREATE PRODECURE procedure_name; SHOW PROCEDURE STATUR LIKE '***';

变量：内存中一个特定的位置，用于临时存储数据，所有MySQL变量都必须以@开头。

			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;

调用：CALL productpricing(@pricelow, @pricehigh, @priceaverage);

3. 游标

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集，主要用于交互式应用（定位结果集的行，通过判断全局变量@@FETCH_STATUS可判断其是否到达了最后，通常此变量不等于0时表示出错或到了最后），其中用户需要滚动屏幕上的数据，并对数据进行浏览或作出更改。MySQL游标只能用于存储过程（和函数）。

使用游标的步骤：（1）在能够使用游标前，必须声明/定义它，这个过程实际上没有检索数据，只是定义要使用的SELECT语句；（2）一旦声明后，必须打开游标以供使用，这个过程用前面定义的SELECT语句把数据实际检索出来；（3）对于填有数据的游标，根据需要取出/检索各行；（4）在结束游标使用时，必须关闭游标。

游标用DECLARE语句创建，DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。用DECLARE定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。

			CREATE PROCEDURE processorders()
            BEGIN
                DECLARE ordernumbers CURSOR
                FOR
                SELECT order_num FROM orders;
            END;
  • 打开游标：OPEN ordernumbers; #在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动
  • 关闭游标：CLOSE ordernumbers； #CLOSE释放游标使用的所有内部内存和资源
  • 使用游标数据：在一个游标被打开后，可使用FETCH语句分别访问它的每一行。

4. 触发器

触发器是MySQL响应DELETE/INSERT/UPDATE语句而自动执行的一条MySQL语句（或位于BEGIN/END间的一组语句）。只有表才支持触发器，视图不支持，临时表也不支持。触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，所以每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联。

创建触发器时，需要给出4条信息：（1） 唯一的触发器名；（2）触发器关联的表；（3）触发器应该响应的活动；（4）触发器何时执行（处理之前或之后）。如果BEFORE触发器失败，MySQL将不执行请求的操作；如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器。触发器不能更新或覆盖。MySQL触发器不支持CALL语句，即不能从触发器调用存储过程。

  • 创建：CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';
  • 删除：DROP TRIGGER newproduct;

INSERT触发器：在INSERT语句执行之前或之后执行。（1）在INSERT触发器代码内，可饮用一个名为NEW的虚拟表，访问被插入的行；（2）在BEFORE INSERT触发器中，NEW中的值也可以被更新，即允许更改被插入的值；（3）对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

DELETE触发器：在DELETE语句执行之前或之后执行。（1）在DELETE触发器代码内，可引用一个名为OLD的虚拟表，访问被删除的行；（2）OLD中的值全都是只读的，不能更新。

UPDATE触发器：在UPDATE语句执行之前或之后执行。（1）在UPDATE触发器代码中，可引用一个名为OLD的虚拟表访问以前的值，引用一个名为NEW的虚拟表访问新更新的值；（2）在BEFORE UPDATE触发器中，NEW中的值可能也被更新，即允许更改将要用于UPDATE语句中的值；（3）OLD中的值全都是只读的，不能更新。

应该用触发器来保证数据的一致性（大小写、格式等）：在触发器中执行这种类型处理的优点是它总是进行这种处理，且是透明地进行，与客户机应用无关。

5. 事务处理

事务处理（transaction processing）用于维护数据库的完整性，保证成批的MySQL操作要么完全执行，要么完全不执行。并非所有引擎都支持事务处理，MyISAM不支持明确的事务处理管理，而InnoDB支持。管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

事务（transaction）：指一组SQL语句，具有原子性、一致性、独立性及持久性等特点。

回退（rollback）：指撤销指定SQL语句的过程；ROLLBACK只能在一个事务处理内使用，即在执行一条START TRANSACTION命令之后。事务处理用来管理INSERT、UPDATE和DELETE语句，你不能回退SELECT语句，也不能回退CREATE或DROP操作，事务处理中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

提交（commit）：指将未存储的SQL语句结构哦写入数据库表；在事务处理块中，提交不会隐含地进行，须使用COMMIT明确提交。当COMMIT或ROLLBACK执行后，事务会自动关闭，将来的更改会隐含提交。

保留点（savepoint）：指事务处理中设置的临时占位符，可对它发布回退（与回退整个事务处理不同）；保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放，也可使用RELEASE SAVEPOINT明确释放保留点。

更改默认提交行为：SET autocommit=0; autocommit标志决定是否自动提交更改，不管有没有COMMIT语句；autocommit标志是针对每个连接而不是服务器的。

6. 索引

聚集索引确定数据在表中的物理存储顺序，一个表只能包含一个聚集索引，但该索引可包含多个列（组合索引）。聚集索引对那些经常要搜索范围值的列特别有效，使用聚集索引找到包含第一个值的行后，便可确定包含后续索引值的行在物理上相邻。

非聚集索引顺序与数据物理排列顺序无关，索引存储在一个地方，数据存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储(可由聚集索引规定)。如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。

相关标签： MySQL 一些 深入 概念 整理 视图 虚拟 包含 数据

上一篇：

下一篇：

推荐阅读