mysql 高级功能_mysql高级特性

## 视图

视图是虚拟的表，本身不存储数据，而是按照指定的方式进行查询，只包含使用时动态索引数据的查询。

### 作用

1. 重用sql语句

2. 简化复杂的sql操作

3. 使用表的组成部分而不是整个表

4. 保护数据

5. 更改数据格式和表示

### 原则

1. 与表名一样视图必须唯一命名

2. 数目没有限制视图可以嵌套

3. ORDER BY可能会被selcect中的覆盖

4. 不能索引不能关联触发器和默认值

5. 可以和表一起使用

### 使用流程

1. CREATE VIEW 创建

2. DROP VIEW viewname 删除

3. SHOW CREATE VIEWW viewname 查看创建语句

4. CREATE OR REPLACE VIEW 删除并且创建

```

//创建视图

CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems

WHERE customers.cust_id = irders.cust_id AND orderitems.order_num = orders.order_num;

//从视图索引数据

SELECT cust_name FROM productcustomers WHERE prod_id = 'TNT2';

//使用视图格式化数据

CREATE VIEW fmtlocation AS SELECT Concat(RTrim(vend_name), '(', RTim(vend_country) ,')') AS vend_tittle FROM vendors;

SELECT * FROM fmtlocation;

//使用视图过滤数据

CREATE VIEW customemil AS SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;

//使用视图作为计算字段

CREATE VIEW expanded AS SELECT order_num,id,name, count, price, count*price AS expand FROM orders;

SELECT * FROM expanded WHERE order_num = 2005;

```

### 视图更新

视图中有分组GROUP BY HAVING、链接、子查询、并、集聚函数、DISTINCT、导出列则不能更新视图，视图主要用于数据检索。

## 存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

通过把处理封装在容易使用的单元中，简化复杂的操作。提高性能。因为使用存储过程比使用单独的SQL语句要快。

OUT指定参数有存储存储过程的查询输出，IN指定存储过程的输入，BEGIN END包含多条查询语句，CALL执行存储过程。

INTO 将结果放入OUT参数中返回。

```

//创建存储过程

CREATE PROCEDURE productpro(

IN number INT,

OUT p1 DECIMAL(8, 2),

OUT ph DECIMAL(8, 2),

OUT pa DECIMAL(8, 2),

OUT total DECIMAL(8, 2),

)

BEGIN

SELECT Min(prod_price) INTO p1 FROM products;

SELECT Max(prod_price) INTO ph FROM products;

SELECT Avg(prod_price) INTO p1 FROM products;

SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = number INTO total;

END;

//调用存储过程

CALL productpro(20, @pricemin, @pricemax, @priceavg, @total)

//检索结果

SELECT @pricemin, @pricemax, @priceavg, @total;

//显示创建详情

SHOE CREATE PROCEDURE productpro;

//删除存储过程

DROP PROCEDURE productpro;

```

## 游标

游标(cursor)是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

MySQL游标只能用于存储过程(和函数)

```

//DECLARE CURSOR 创建游标, 存储过程处理完后有效小时

CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

DECLARE o INT;

//在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动

OPEN ordernumbers;

//在一个游标被打开后，可以使用FETCH语句分别访问它的每一行

//它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行

FETCH ordernumbers INTO o;

//游标处理完成后，应当使用如下语句关闭游标

CLOSE ordernumbers;

END;

```

## 触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句)：DELETE 、INSERT、UPDATE。

只有表才支持触发器，视图不支持

```

//创建触发器

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'product added';// 触发器将在INSERT语句执行成功后 对每行插入都显示一条文本消息

//在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;//触发器返回插入行的order_num

//在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；

//删除之前将删除的值放入另外一个表

CREATE TRIGGER delleteorder BEFORE DELETE ON orders FOR EACH ROW

BEGIN

INSERT INTO archive_orders(order_num, order_date, cust_id) VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);

END

//在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值，引用一个名为NEW的虚拟表访问新更新的值,old是readonly

CREATE TRIGGER uodateorder BEFORE UPDATE ON orders FOR EACH ROW SET NEW.order_state = Upper(NEW.order_state);//触发器返回插入行的order_num

//删除触发器

DROP TRIGGER newproduct;

```

## 事务

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行(除非明确示)。如果没有错误发生，整组语句提交给(写到)数据库表。如果发生错误，则进行回退(撤销)以恢复数据库到某个已知且安全的状态。

默认的MySQL行为是自动提交所有更改。换句话说，任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效

```

SET autocommit = 0; //不自动提交

START TRANSACTION;

DELECT FROM orders;

SELECT * FROM orders;

ROLLBACK; //当COMMIT或ROLLBACK语句执行后，事务会自动关闭

START TRANSACTION;

DELECT FROM orders;

SELECT * FROM orders;

COMMIT;

SAVEPOINT delete1; //保留点保留点在事务处理完成(执行一条ROLLBACK或COMMIT)后自动释放

DELECT FROM orders;

ROLLBACK TO delete1;

```

有疑问加站长微信联系(非本文作者))