学习mysql的游标，存储过程，存储函数，视图，触发器

MySQL游标

MySQL游标是一种在MySQL存储过程中使用的数据结构，它允许开发人员对结果集进行迭代，类似于指针或迭代器。

以下是一个简单的示例，演示如何使用MySQL游标来遍历查询结果集：

DECLARE cur CURSOR FOR SELECT column1, column2 FROM table;//创建游标
//创建局部变量
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE var1 VARCHAR(50);
DECLARE var2 VARCHAR(50);
​
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
​
OPEN cur;//使用游标
​
read_loop: LOOP
  FETCH cur INTO var1, var2;//将游标中获取的值放入局部变量var1，var2
  IF done THEN//终止循环条件
    LEAVE read_loop;
  END IF;
  
  -- 在此处执行任何处理逻辑，例如打印或更新变量等等
END LOOP;
​
CLOSE cur;//关闭游标(一定要记得关闭，否则非常消耗MySQL性能)

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE 是一个处理程序，用于处理游标操作时出现的 "no more rows" 异常。在 MySQL 中，当使用游标操作数据集时，如果游标没有更多的行可供处理，就会触发这个异常。在这种情况下，处理程序将设置变量 done 的值为 TRUE，以便后续代码可以检查该变量并退出游标处理循环。

具体来说，当游标操作无法找到更多的行时，MySQL 会引发一个名为 NOT FOUND 的异常。在声明游标时，您可以使用 DECLARE CONTINUE HANDLER FOR NOT FOUND 语句指定一个处理程序。然后，在处理程序中，您可以设置一个变量，以便在异常发生时更新该变量的值。这使得程序可以处理异常，而不是直接退出或崩溃。

在练习题5的存储过程中，处理程序的目的是在没有找到匹配行时设置变量 done 的值为 TRUE，以便在后续代码中检查该值并退出游标处理循环。

MySQL存储过程和存储函数

MySQL存储过程和存储函数是一种在数据库中定义的可重用代码块，可以接受参数，并返回结果。存储过程和存储函数在很多情况下比直接编写SQL查询更有用，例如：

• 执行复杂的业务逻辑

• 提供可重复使用的数据处理逻辑

• 保护数据库安全性，只允许对特定表进行访问

以下是一个简单的示例，展示如何创建一个接受参数并返回结果的MySQL存储函数：

CREATE FUNCTION myFunction(param1 INT, param2 VARCHAR(50))//创建存储函数
RETURNS VARCHAR(50)//设置返回值
BEGIN
  DECLARE result VARCHAR(50);//局部变量
  
  -- 在此处编写任何所需的处理逻辑，例如查询、更新等等
  
  SET result = CONCAT(param1, '-', param2);//将两个传入的参数连接并且赋值给result
  
  RETURN result;//将result返回
END;//方法结束标志

MySQL视图

MySQL视图是虚拟表，其内容来自一个或多个查询。视图可以简化查询操作，并在多个查询之间共享逻辑。

以下是一个简单的示例，演示如何创建一个MySQL视图：

CREATE VIEW myView AS
SELECT column1, column2
FROM table
WHERE column3 > 10;

创建视图后，您可以像使用实际表一样使用它：

SELECT *
FROM myView
WHERE column1 = 'someValue';

视图的实际用途

视图是一种虚拟表，其内容是基于 SQL 查询而定义的。与实际的物理表不同，视图并不在数据库中占用存储空间，它只是一种逻辑结构。当使用视图时，您可以像使用表一样查询它，但是您实际上是查询的查询结果，而不是物理表本身。

视图的实际用途是在许多方面，包括：

1. 简化复杂查询：当您需要从多个表中检索数据时，使用视图可以简化查询语句，并且可以将常用的查询保存为视图以便以后使用。

2. 简化数据访问权限：使用视图，您可以限制用户访问表中的特定列或行。您可以创建只显示必要数据的视图，并将其分配给特定的用户，从而简化数据访问权限管理。

3. 简化数据聚合：使用视图，您可以聚合多个表的数据，而无需实际创建物理表。例如，您可以创建一个视图来计算每个部门的销售总额，而不必实际存储该数据。

4. 简化数据导出：视图可以用作导出数据的一种方式，因为您可以将视图的查询结果导出到文件或其他数据源中，而无需导出整个物理表。

5. 简化数据更新：视图可以用作更新数据的一种方式，因为您可以通过视图更新多个表中的数据。例如，如果您有一个视图，显示了两个表中的数据，并且您想要更新其中一个表中的数据，您只需要更新视图中的相关行即可。

综上所述，视图是一种强大的工具，可以简化查询、限制访问、聚合数据和简化数据更新等任务，从而提高数据库管理和数据分析的效率。

视图是否可以理解为写好的查询语句

视图可以被理解为一种预先定义好的查询语句，其查询结果被封装成了一个虚拟表，而不需要再次编写相同的查询语句。

当您创建一个视图时，您实际上在定义一个查询，该查询可以涉及一个或多个表，并且可以包含聚合、过滤和其他 SQL 操作。然后，这个查询结果就被封装成了一个虚拟表，您可以像使用任何其他表一样对其进行查询。

当您查询一个视图时，您实际上是查询了一个预先定义好的查询结果，而不是实际查询底层表。这样，您可以将常用的查询保存为视图，并在需要时使用它们，而不必编写相同的查询语句。

因此，视图可以被理解为一种预定义的查询语句，它可以重用查询结果，从而简化数据库操作和提高查询效率。

那么改变视图的数据是怎样影响到物理表中存储的数据

视图本身并不存储数据，它只是一个基于查询结果的虚拟表。当您查询视图时，实际上是查询视图的查询结果而不是底层表的实际数据。因此，对视图数据的更改不会直接影响物理表中存储的数据。

如果您更新了视图中的数据，则实际上是在更新视图查询结果中的数据。这意味着，如果您查询视图之后再次查询底层表，您会看到更新后的数据，但是如果您直接查询底层表而没有查询视图，则不会看到更新后的数据。

当您更新视图时，如果更新操作符合视图定义中的限制条件，则更改将反映在底层表中。例如，如果您使用视图更新表中的一些行，并且在视图定义中使用了 WHERE 子句来限制行的范围，则只有在满足 WHERE 子句条件的行才会被更新。

需要注意的是，视图更新操作的可行性取决于视图定义中的各种限制条件。如果更新操作不符合这些限制条件，则更新将被拒绝，并且不会影响底层表的数据。因此，视图是一种方便和有效的工具，但是在更新视图数据时需要格外小心。

例子1

要更新视图中的数据，您可以使用类似于更新表中数据的 SQL 语句。下面是一个使用视图更新数据的简单示例：

假设有一个包含以下数据的表 students：

+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  20 |
|  2 | Bob   |  21 |
|  3 | Carol |  22 |
+----+-------+-----+

现在，我们可以创建一个视图，只包含年龄在 21 岁以下的学生：

CREATE VIEW young_students AS
SELECT * FROM students WHERE age < 21;

使用此视图，我们可以通过以下语句更新 young_students 中 Bob 的年龄：

UPDATE young_students SET age = 22 WHERE name = 'Bob';

这个更新操作将反映在底层表 students 中，Bob 的年龄从 21 岁变为 22 岁：

+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  20 |
|  2 | Bob   |  22 |  -- Updated age
|  3 | Carol |  22 |
+----+-------+-----+

需要注意的是，视图更新操作的可行性取决于视图定义中的各种限制条件。如果更新操作不符合这些限制条件，则更新将被拒绝，并且不会影响底层表的数据。因此，在更新视图数据时需要格外小心，确保更新操作符合视图定义中的限制条件。

例子2

下面是一个稍微复杂一点的例子，假设有两个表 students 和 courses，以及一个连接这两个表的中间表 student_courses。这些表的结构如下：

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);
​
CREATE TABLE courses (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  credits INT
);
​
CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  grade INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

假设现在我们需要创建一个视图，包含每个学生及其所选课程的名称和学分。我们可以使用以下 SQL 语句创建这个视图：

CREATE VIEW student_course_info AS
SELECT students.name AS student_name, courses.name AS course_name, courses.credits
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.id;

使用这个视图，我们可以查看每个学生所选课程的名称和学分：

SELECT * FROM student_course_info;

输出结果类似于以下内容：

+--------------+-------------------+---------+
| student_name | course_name       | credits |
+--------------+-------------------+---------+
| Alice        | Biology           |       4 |
| Alice        | Chemistry         |       3 |
| Bob          | History           |       3 |
| Bob          | Mathematics       |       4 |
| Carol        | English           |       3 |
| Carol        | Chemistry         |       3 |
| Carol        | Computer Science  |       4 |
+--------------+-------------------+---------+

现在，假设我们需要将 Bob 在历史课程中的成绩更新为 A，并将这个更新操作反映在 student_courses 表中。我们可以使用以下 SQL 语句更新视图中 Bob 在历史课程中的成绩：

UPDATE student_course_info SET grade = 'A' WHERE student_name = 'Bob' AND course_name = 'History';

这个更新操作将反映在底层表 student_courses 中，Bob 在历史课程中的成绩将从之前的值更新为 A：

+------------+-----------+-------+
| student_id | course_id | grade |
+------------+-----------+-------+
|          1 |         1 |     B |
|          1 |         2 |     A |
|          2 |         1 |     A |  -- Updated grade
|          2 |         2 |     B |
|          3 |         3 |     C |
|          3 |         2 |     B |
|          3 |         4 |     A |
+------------+-----------+-------+

需要注意的是，视图更新操作的可行性取决于视图定义中的各种限制条件。如果更新操作不符合这些限制条件，则更新将被拒绝，并且不会影响底层表的数据。因此，在更新视图数据时需要格外小心，确保更新操作符合

视图更改不符合条件的例子

假设我们有以下三个表：

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2)
);
​
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_name VARCHAR(50)
);
​
CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT,//订单id（外键）
  product_id INT,//产品id（外键）
  quantity INT,//单价
);

现在，我们想要创建一个视图，显示每个订单的产品名称、数量和总价。我们可以使用以下 SQL 语句创建这个视图：

CREATE VIEW order_details AS
SELECT orders.id AS order_id, products.name AS product_name, order_items.quantity, (order_items.quantity * products.price) AS total_price
FROM orders
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id;

现在，假设我们尝试在视图中更新某个订单的产品数量，但是更新操作会导致订单总价超出预算，此时视图更新操作将被拒绝。以下是一个例子：

UPDATE order_details SET quantity = 10 WHERE order_id = 1;

在这个例子中，我们尝试将订单 #1 中的产品数量更新为 10。然而，根据 order_details 视图的定义，订单 #1 中的产品总价为 200，而我们的预算限制为 150，因此更新操作会被拒绝，且不会影响底层表的数据。

需要注意的是，视图的限制条件可以非常复杂，包括表之间的关系、数据类型、约束条件等等。因此，在更新视图数据时需要格外小心，确保更新操作符合视图定义中的各种限制条件。

MySQL触发器

MySQL触发器是一种数据库对象，它在数据库表上执行特定的操作，例如插入、更新或删除数据时自动运行。触发器可以用于实现数据约束、数据验证或自动执行其他业务逻辑。

以下是一个简单的示例，展示如何创建一个MySQL触发器，以便在插入新行时自动设置一个默认值：

CREATE TRIGGER myTrigger
BEFORE INSERT ON myTable
FOR EACH ROW
BEGIN
  SET NEW.column1 = 'defaultValue';
END;

这将在每次向myTable表中插入新行之前自动运行，并将column1设置为defaultValue。

下面是一些涉及MySQL游标、存储过程、存储函数、视图和触发器的练习题和答案，以及相应的创建表语句。

练习题1：

创建一个存储过程，该存储过程接受一个参数，并将该参数与表中的所有记录进行比较。如果找到匹配项，则返回第一个匹配项的ID，否则返回-1。

创建表的语句：

CREATE TABLE myTable (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);
INSERT INTO myTable (id, name, age) VALUES
(1, 'Alice', 25),
(2, 'Bob', 30),
(3, 'Charlie', 20),
(4, 'David', 22),
(5, 'Eva', 28);

存储过程的答案：

CREATE PROCEDURE myProcedure(IN searchName VARCHAR(50))
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE varId INT;
  DECLARE varName VARCHAR(50);
  DECLARE cur CURSOR FOR SELECT id, name FROM myTable;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  
  read_loop: LOOP
    FETCH cur INTO varId, varName;
    IF done THEN
      SET varId = -1;
      LEAVE read_loop;
    END IF;
    
    IF varName = searchName THEN
      LEAVE read_loop;
    END IF;
  END LOOP;
  
  CLOSE cur;
  
  SELECT varId;
END;

练习题2：

创建一个存储函数，该函数接受两个参数并返回它们的和。

创建表的语句：

drop table if EXISTS myTable;
CREATE TABLE myTable (
  id INT PRIMARY KEY,
  value INT
);
INSERT INTO myTable (id, value) VALUES
(1, 5),
(2, 10),
(3, 15),
(4, 20),
(5, 25);

存储函数的答案：

CREATE FUNCTION myFunction(param1 INT, param2 INT) RETURNS INT
BEGIN
  DECLARE result INT;
  
  SET result = param1 + param2;
  
  RETURN result;
END;

练习题3：

创建一个视图，该视图列出myTable表中年龄大于20岁的人的姓名和年龄。

创建表的语句：

drop table if EXISTS myTable;
CREATE TABLE myTable (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);
INSERT INTO myTable (id, name, age) VALUES
(1, 'Alice', 25),
(2, 'Bob', 30),
(3, 'Charlie', 20),
(4, 'David', 22),
(5, 'Eva', 28);

视图的答案：

CREATE VIEW myView AS
SELECT name, age
FROM myTable
WHERE age > 20;

练习题4：

创建一个触发器，该触发器在向myTable表中插入新行时自动设置ID列的值。

创建表的语句：

drop table if EXISTS myTable;
CREATE TABLE myTable (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);
INSERT INTO myTable (id, name, age) VALUES
(1, 'Alice', 25),
(2, 'Bob', 30),
(3, 'Charlie', 20),
(4, 'David', 22),
(5, 'Eva', 28);

触发器的答案：

CREATE TRIGGER myTrigger
BEFORE INSERT ON myTable
FOR EACH ROW
BEGIN
  SET NEW.id = (SELECT COALESCE(MAX(id), 0) + 1 FROM myTable);
END;

COALESCE函数：函数需要许多参数，并返回第一个非NULL参数。

练习题5：

创建一个存储过程，该存储过程接受一个参数，并将该参数与表中的所有记录进行比较。如果找到匹配项，则返回该匹配项的名称，否则返回'Not Found'。

创建表的语句：

drop table if EXISTS myTable;
CREATE TABLE myTable (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

测试数据：

INSERT INTO myTable (id, name, age) VALUES
(1, 'Alice', 25),
(2, 'Bob', 30),
(3, 'Charlie', 20),
(4, 'David', 22),
(5, 'Eva', 28);

存储过程的答案：

drop PROCEDURE if EXISTS myProcedure;
CREATE PROCEDURE myProcedure(IN searchId INT)
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE varId INT;
  DECLARE varName VARCHAR(50);
  DECLARE cur CURSOR FOR SELECT id, name FROM myTable;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  
  read_loop: LOOP
    FETCH cur INTO varId, varName;
    IF done THEN
      SET varName = "NOT FOUND";
      LEAVE read_loop;
    END IF;
    
    IF varId = searchId THEN
      LEAVE read_loop;
    END IF;
  END LOOP;
  
  CLOSE cur;
  
  SELECT varName;
END;

练习题6：

创建一个存储函数，该函数接受一个字符串参数并返回该字符串的长度。

创建表的语句：

drop table if EXISTS myTable;
CREATE TABLE myTable (
  id INT PRIMARY KEY,
  value VARCHAR(50)
);

测试数据：

INSERT INTO myTable (id, value) VALUES
(1, 'Hello world'),
(2, 'World so beautiful'),
(3, 'MySQL is Concurrency');

存储函数的答案：

drop FUNCTION if EXISTS myFunction;
CREATE FUNCTION myFunction(param VARCHAR(50)) RETURNS INT
BEGIN
  DECLARE result INT;
  
  SET result = LENGTH(param);
  
  RETURN result;
END;
​
-- 测试案例
SELECT value INTO @values FROM myTable where id = 3;
SELECT myFunction(@values);

练习题7：

创建一个视图，该视图列出myTable表中年龄大于25岁的人的姓名和年龄，并按照年龄从大到小排序。

创建表的语句：

drop table if EXISTS myTable;
CREATE TABLE myTable (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);
​
-- 测试数据：
INSERT INTO myTable (id, name, age) VALUES
(1, 'Alice', 25),
(2, 'Bob', 30),
(3, 'Charlie', 20),
(4, 'David', 22),
(5, 'Eva', 28);

视图的答案：

CREATE VIEW myView AS
SELECT name, age
FROM myTable
WHERE age > 25
ORDER BY age DESC;
​
-- 测试案例
select * FROM myView;

练习题8：

创建一个触发器，该触发器在向myTable表中插入新行时自动设置ID列的值为随机数。

创建表的语句：

CREATE TABLE myTable (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);
​
​
​
-- 触发器的答案：
CREATE TRIGGER myTrigger BEFORE INSERT ON myTable
FOR EACH ROW
BEGIN
  SET NEW.id = FLOOR(RAND() * 1000);
END;
​
-- 测试案例：
INSERT INTO myTable (name, age) VALUES
('Frank', 35),
('Grace', 27),
('Henry', 18);
SELECT * FROM myTable

这些练习题旨在帮助您深入理解MySQL的游标、存储过程、存储函数、视图和触发器的使用。您可以使用上面的创建表语句和测试数据来测试您的解决方案，以确保它们能够正确地运行。