当前位置:Gxlcms > mysql > MySQL中一些深入概念整理

MySQL中一些深入概念整理

时间:2021-07-01 10:21:17 帮助过:37人阅读

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. 索引

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

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

人气教程排行