MySQL中一些深入概念整理

最新推荐文章于 2024-11-08 17:49:58 发布

billfeller

最新推荐文章于 2024-11-08 17:49:58 发布

阅读量667

点赞数

分类专栏： mysql 文章标签： mysql insert 存储 delete sql 数据库

mysql 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

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个主要的好处：简单、安全、高性能。

执行：CALL procedure_name(parameter_list);
创建：CREATE PROCEDURE procedure_name(parameter_list) BEGIN ...... END;
删除：DROP PROCEDUREIF EXISTS procedure_name;
查看：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标志是针对每个连接而不是服务器的。