SQL第六周学习笔记---view视图、stored procedure存储过程、transaction事务、join

这是我参与8月更文挑战的第6天，活动详情查看：8月更文挑战

大家好，我是【豆干花生】，这次我带来了一整套sql、mysql学习笔记，后续还会更新~

今天我要分享第六周笔记，在本模块中，您将学习一些高级的SQL语句，比如view、stored procedure、transaction（即视图、存储过程和事务。）以及join操作。

这是我从头开始学习sql/mysql的学习笔记，后续还会更新，欢迎关注~

注：本笔记为coursera网站课程《Databases and SQL for Data Science with Python》的学习笔记

学习目标

描述视图提供的好处
创建和查询视图
描述使用存储过程的利弊
创建存储过程并从其他代码中调用它们
描述ACID交易的重要性
在您的SQL代码中使用事务
说明如何使用不同类型的JOIN运算符
使用联接从多个表中查询数据

一.view、stored procedure、transaction

即视图、存储过程和事务。

1.views

在SQL中，视图是表示存在于一个或多个表中的数据的另一种方法。

就像真实的表一样，它包含行和列。视图中的字段是数据库中一个或多个实际表中的字段。

尽管可以像表一样查询视图，但是视图是动态的。

仅存储视图的定义，而不存储数据。

CREATE VIEW语句的语法看起来如何？

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

REPLACE VIEW语句的语法看起来如何？

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

DROP VIEW语句的语法看起来如何？

DROP VIEW view_name;

练习1：创建一个视图

CREATE VIEW EMPSALARY AS 
SELECT cust_id, cust_name, cust_city, cust_country, cust_email, cust_state
FROM customers;

SELECT * FROM EMPSALARY;

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aPFpeRfW-1628408556155)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513114526795.png)]$

练习2：更新视图

把两张图拼为一个

CREATE OR REPLACE VIEW EMPSALARY  AS 
SELECT cust_id, cust_name, cust_city, cust_country, cust_email, cust_state, order_num,order_date
FROM customers,orders
WHERE customers.cust_id = orders.cust_id2;

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rXnDJ9Dt-1628408556158)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513114549637.png)]$

练习3：删除视图

DROP VIEW EMPSALARY;

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-avJKqIrZ-1628408556161)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513114707718.png)]$

2.stored procedures

存储过程是在数据库上存储和执行的一组SQL语句。

因此，您封装了而不是从客户端向服务器发送多个SQL语句。将它们存储在服务器上的存储过程中，并从客户端发送一条语句以执行他们。

如果您有一次又一次编写的SQL查询，则存储过程可能会很有用。您可以将其保存为存储过程，然后调用它来执行它。

您可以用许多不同的语言编写存储过程。例如，对于Cloud和DB2上的Db2，您可以使用SQL PL，PL / SQL，Java，C或其他语言编写语言。

总的来说，存储过程就是一个封装的函数，之后直接调用。

他们可以接受信息作为参数，执行创建，读取，更新和删除（CRUD）操作，并将结果返回到客户端应用程序。

存储过程的好处包括：

减少网络流量，因为只需要一个调用即可执行多个语句。由于处理是在存储数据的服务器上进行的，因此提高了性能最终结果只传回给客户。

重用代码，因为多个应用程序可以将相同的存储过程用于同样的工作。

提高安全性，因为a）您不需要公开所有表和列提供给客户端开发人员的信息； b）您可以使用服务器端逻辑进行验证数据，然后再将其接受到系统中。

不过请记住，SQL不是完全成熟的编程语言，因此您不应该尝试在存储过程中编写所有业务逻辑。

练习1 创建存储过程

blog.csdn.net/qpzkobe/art… 这篇博客写的非常好，提供了两种方式：一种是打开navicat选择要创建存储过程的数据库，利用工具栏打开【函数】--【新建函数】，另一种是利用工具栏打开【查询】--【新建查询】窗口编写sql。

这里选择第一种：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WY6esjmo-1628408556166)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513144303476.png)]$

运行一下：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9OGfLaMV-1628408556167)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513144324819.png)]$

当然也可以使用sql语句来调用存储过程：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4eyG0qZY-1628408556170)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513150921931.png)]$

blog.csdn.net/qq_41573234…

这里注意两个问题：

1.保存存储过程的时候，

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J5lIVeSK-1628408556171)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513151137573.png)]$

要这样写：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EZgajGp3-1628408556173)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513151207338.png)]$

才能保存。

2.在sql文件里调用存储过程，要在输出变量前写上@：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KQp9jnFP-1628408556174)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513151310148.png)]$

如果要删除存储过程，请复制下面的代码，单击全部运行。

DROP PROCEDURE RETRIEVE_ALL;

练习2 执行存储过程

我们还可以通过存储过程，输入变量值，来对数据进行更新。

例程将包含SQL查询，以根据动物的健康状况BAD或WORSE来更新PETSALE表中动物的售价。
该程序例程将采用动物ID和健康状况作为参数，这些参数将根据动物的健康状况用于更新PETSALE表中动物的售价。认为 -
- 对于具有不良健康状况的ID XX的动物，销售价格将进一步降低25％。
- 对于健康状况为WORSE的ID为YY的动物，销售价格将进一步降低50％。
- 对于具有其他健康状况的ID ZZ的动物，销售价格不会改变。

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc2`(IN `Animal_ID` integer,IN `Animal_Health` varchar(5))
BEGIN
	#Routine body goes here...
	IF Animal_Health = 'BAD' THEN                           -- Start of conditional statement
        UPDATE PETSALE
        SET SALEPRICE = SALEPRICE - (SALEPRICE * 0.25)
        WHERE ID = Animal_ID;
    
    ELSEIF Animal_Health = 'WORSE' THEN
        UPDATE PETSALE
        SET SALEPRICE = SALEPRICE - (SALEPRICE * 0.5)
        WHERE ID = Animal_ID;
        
    ELSE
        UPDATE PETSALE
        SET SALEPRICE = SALEPRICE
        WHERE ID = Animal_ID;

    END IF;   
	
		SELECT * FROM petsale;
		
END

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WkH1xUVJ-1628408556176)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513151942231.png)]$

输入值：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BAuyKgJ5-1628408556177)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513152006619.png)]$

之前的结果和之后的结果：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PtReu4ks-1628408556178)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513152039155.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kz0VP84Y-1628408556180)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513152107544.png)]$

当然也可以在sql文件里进行输入：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bKmbG2Lq-1628408556181)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513152219462.png)]$

当然每次新输入bad，会导致价格越来越低。。。。

3.acid transactions

ACID事务是所有SQL语句必须成功完成或全部不完成的事务完全没有。这样可以确保数据库始终处于一致状态。 ACID代表“原子”，“一致”，“隔离”，“耐用”。 SQL命令BEGIN，COMMIT和ROLLBACK用于管理ACID事务。可以从C，R和Python等语言中调用SQL命令。

数据库事务必须是ACID（原子的，一致的，隔离的和持久的）。事务中所有SQL语句的效果都可以使用COMMIT命令应用于数据库，也可以使用ROLLBACK命令从数据库撤消。

COMMIT（用于将事务中所做的更改永久保存在表中），以及ROLLBACK（用于撤消尚未保存在表中的事务）。ROLLBACK只能用于撤消当前工作单元中的更改。

练习1 新建数据集

-- Drop the table in case it exists-- DROP TABLE ShoeShop;-- Create the tableCREATE TABLE ShoeShop (    Product VARCHAR(25) NOT NULL,    Stock INTEGER NOT NULL,    Price DECIMAL(8,2) CHECK(Price>0) NOT NULL,    PRIMARY KEY (Product)    );-- Insert sample data into the table    INSERT INTO ShoeShop VALUES('Boots',11,200),('High heels',8,600),('Brogues',10,150),('Trainers',14,300);-- Retrieve all records from the tableSELECT * FROM ShoeShop;

-- Drop the table in case it exists-- DROP TABLE BankAccounts;-- Create the tableCREATE TABLE BankAccounts (    AccountNumber VARCHAR(5) NOT NULL,    AccountName VARCHAR(25) NOT NULL,    Balance DECIMAL(8,2) CHECK(Balance>=0) NOT NULL,    PRIMARY KEY (AccountNumber)    );-- Insert sample data into the table    INSERT INTO BankAccounts VALUES('B001','Rose',300),('B002','James',1345),('B003','Shoe Shop',124200),('B004','Corner Shop',76000);-- Retrieve all records from the tableSELECT * FROM BankAccounts;

练习2 更新数据集

您将创建一个名为TRANSACTION_ROSE的存储过程例程，该例程将包含TCL命令（例如COMMIT和ROLLBACK）。
现在，根据给定场景开发例程以执行事务。
**场景：**让我们从ShoeShop购买Rose一双靴子。因此，我们必须在BankAccounts表中更新Rose余额和ShoeShop余额。然后，我们还必须更新ShoeShop表中的Boots库存。Boots之后，我们还尝试购买Rose一双运动鞋。
要在Db2上创建存储过程例程，请复制以下代码并将其粘贴到“运行SQL”页面的文本框中。单击全部运行。

新建存储过程：

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc3`()BEGIN	#Routine body goes here...	        DECLARE SQLCODE INTEGER DEFAULT 0;                  -- Host variable SQLCODE declared and assigned 0        DECLARE retcode INTEGER DEFAULT 0;                  -- Local variable retcode with declared and assigned 0        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION           -- Handler tell the routine what to do when an error or warning occurs        SET retcode = SQLCODE;                              -- Value of SQLCODE assigned to local variable retcode                UPDATE BankAccounts        SET Balance = Balance-200        WHERE AccountName = 'Rose';                UPDATE BankAccounts        SET Balance = Balance+200        WHERE AccountName = 'Shoe Shop';                UPDATE ShoeShop        SET Stock = Stock-1        WHERE Product = 'Boots';                UPDATE BankAccounts        SET Balance = Balance-300        WHERE AccountName = 'Rose';                IF retcode < 0 THEN                                  --  SQLCODE returns negative value for error, zero for success, positive value for warning            ROLLBACK WORK;                ELSE            COMMIT WORK;                END IF;END

SELECT * FROM BankAccounts;SELECT * FROM ShoeShop;

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9NUJ7V3f-1628408556182)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513154028059.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YsyKci7h-1628408556184)(C:\Users\YUANMU\AppData\Roaming\Typora\typora-user-images\image-20210513154037729.png)]$

我们可以观察到事务已被执行。但是，当我们观察这些表时，没有任何更改已通过COMMIT永久保存。发生的所有可能更改可能已通过ROLLBACK撤消，因为整个事务由于SQL语句失败或更多而失败。让我们看一下事务失败背后的可能原因，以及COMMIT-ROLLBACK在存储过程中的工作方式：

前三个UPDATE应该成功运行。Rose和ShoeShop的余额都应该在BankAccounts表中进行了更新。Rose的当前余额应为300-200（一双靴子的价格）=100。ShoeShop的当前余额应为124200 + 200 = 124400。成功购买Rose，11-1 = 10。
最后一条UPDATE语句尝试购买Rose一对运动鞋，但是在购买了一双Boots之后，她的余额不足（Rose的当前余额：100 <Trainer价格：300）。因此，最后一个UPDATE语句失败。由于如果任何SQL语句失败，整个事务都会失败，因此不会提交事务。
作为独立主机变量的SQLCODE包含每个SQL语句执行的成功/失败/警告信息。现在，由于SQLCODE变量在下一条SQL语句运行时被重置，因此retcode是我们的本地变量，用于捕获此SQLCODE的返回值。如果未成功执行，则SQLCODE为每个SQL语句返回负值。因此，在发生任何错误时，所有更改都会回滚。提交仅在事务成功执行且没有任何错误之后进行。

4.summary and highlights

视图是一种动态机制，用于显示一个或多个表中的数据。事务表示一个完整的工作单元，可以是一个或多个SQL语句。
ACID事务是所有SQL语句必须成功完成的事务，或根本不完成的事务。
存储过程是在数据库服务器上存储并执行的一组SQL语句，允许您发送一个语句作为发送多个语句的替代方法。
您可以使用许多不同的语言（例如SQL PL，PL / SQL，Java和C）编写存储过程。

二.join statements

1.知识点

可以使用JOIN运算符来执行以下操作：合并两个或多个表中的行被联接的表由一个公共列关联，通常是一个表的主键，在另一个表中作为外键显示。有两种类型的联接：内部联接和外部联接。

**内部联接仅返回表中在公共列中具有匹配值的行，**通常，一个表的主键作为第二个表中的外键存在。来自联接表的不具有匹配值的行不会出现在结果中。

**可以使用多种外部联接来完善结果集。左外部联接返回左表中的所有行，**所有行形成右表与内部联接将返回的表和第一个表中的所有行在第二张表中没有匹配项。 右外部联接返回内部联接将返回的所有行以及所有行 第二个表中的第一个表中没有匹配项。完全外部联接返回两个表中的所有匹配行以及两个表中的所有行没有匹配项的表格。

2.sql语句

CROSS JOIN（也称为笛卡尔联接）语句语法如何显示？

SELECT column_name(s)FROM table1CROSS JOIN table2;

INNER JOIN语句的语法看起来如何？

SELECT column_name(s)FROM table1INNER JOIN table2ON table1.column_name = table2.column_name;WHERE condition;

LEFT OUTER JOIN语句的语法看起来如何？

SELECT column_name(s)FROM table1LEFT OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition;

RIGHT OUTER JOIN语句的语法看起来如何？

SELECT column_name(s)FROM table1RIGHT OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition;

FULL OUTER JOIN语句的语法看起来如何？

SELECT column_name(s)FROM table1FULL OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition;

SELF JOIN语句的语法看起来如何？

SELECT column_name(s)FROM table1 T1, table1 T2WHERE condition;

3.练习

--- Query1A ---select E.F_NAME,E.L_NAME, JH.START_DATE 	from EMPLOYEES as E 	INNER JOIN JOB_HISTORY as JH on E.EMP_ID=JH.EMPL_ID 	where E.DEP_ID ='5';	--- Query1B ---	select E.F_NAME,E.L_NAME, JH.START_DATE, J.JOB_TITLE 	from EMPLOYEES as E 	INNER JOIN JOB_HISTORY as JH on E.EMP_ID=JH.EMPL_ID 	INNER JOIN JOBS as J on E.JOB_ID=J.JOB_IDENT	where E.DEP_ID ='5';--- Query 2A ---select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME	from EMPLOYEES AS E 	LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;	--- Query 2B ---select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME	from EMPLOYEES AS E 	LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP 	where YEAR(E.B_DATE) < 1980;--- alt Query 2B ---select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME	from EMPLOYEES AS E 	INNER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP 	where YEAR(E.B_DATE) < 1980;--- Query 2C ---select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME	from EMPLOYEES AS E 	LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP 	AND YEAR(E.B_DATE) < 1980;--- Query 3A ---select E.F_NAME,E.L_NAME,D.DEP_NAME	from EMPLOYEES AS E 	FULL OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;--- Query 3B ---select E.F_NAME,E.L_NAME,D.DEPT_ID_DEP, D.DEP_NAME	from EMPLOYEES AS E 	FULL OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP AND E.SEX = 'M';--- alt Query 3B ---select E.F_NAME,E.L_NAME,D.DEPT_ID_DEP, D.DEP_NAME	from EMPLOYEES AS E 	LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP AND E.SEX = 'M';

4.Summary & Highlights

联接根据这些表中某些列之间的关系来合并两个或多个表中的行。
要合并来自三个或更多不同表的数据，只需将新联接添加到SQL语句中。
表联接有两种类型：内部联接和外部联接；以及三种外部联接：左外部联接，右外部联接和完全外部联接。
联接的最常见类型是内部联接，它与两个表中的结果匹配，并且仅返回匹配的行。
您可以使用别名作为表或列名称的简写。
您可以使用自联接比较同一表中的行。

都看到这里了，不如点个赞哦~

在这里插入图片描述