Coursera 笔记

Module 06 · Bonus Module: Advanced SQL for Data Engineers

SQL Cheat Sheet: Views, Stored Procedures and TransactionsSQL 速查表：视图、存储过程和事务

Coursera ↗

Views 视图

Topic   主题 Syntax   语法 Description   描述 Example   示例
Create View   创建视图 CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; A CREATE VIEW is an alternative way of representing data that exists in one or more tables.
视图（ CREATE VIEW ）是表示存在于一个或多个表中的数据的一种替代方法。		 CREATE VIEW EMPSALARY AS SELECT EMP_ID, F_NAME, L_NAME, B_DATE, SEX, SALARY FROM EMPLOYEES;
Update a View   更新视图 CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; The CREATE OR REPLACE VIEW command updates a view.
CREATE OR REPLACE VIEW 命令用于更新视图。		 CREATE OR REPLACE VIEW EMPSALARY AS SELECT EMP_ID, F_NAME, L_NAME, B_DATE, SEX, JOB_TITLE, MIN_SALARY, MAX_SALARY FROM EMPLOYEES, JOBS WHERE EMPLOYEES.JOB_ID = JOBS.JOB_IDENT;
Drop a View   删除视图 DROP VIEW view_name; Use the DROP VIEW statement to remove a view from the database.
使用 DROP VIEW 语句从数据库中删除视图。		 DROP VIEW EMPSALARY;

Stored Procedures in IBM Db2 using SQL IBM Db2 中使用 SQL 的存储过程

Topic   主题 Syntax   语法 Description   描述 Example   示例
Stored Procedures   存储过程 --#SET TERMINATOR @ CREATE PROCEDURE PROCEDURE_NAME
LANGUAGE
BEGIN
END
@
 A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
一个 stored procedure 是一段预编译的 SQL 代码，可以保存下来，以便反复使用。

The default terminator for a stored procedure is semicolon(;). To set a different terminator we use SET TERMINATOR clause followed by the terminator such as ‘@’.
存储过程的默认终止符是分号（;）。要设置不同的终止符，我们使用 SET TERMINATOR 子句，后跟终止符，例如“@”。		 --#SET TERMINATOR @ CREATE PROCEDURE RETRIEVE_ALL
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR
WITH RETURN FOR
SELECT * FROM PETSALE;
OPEN C1;
END
@

Stored Procedures in MySQL using phpMyAdmin 使用 phpMyAdmin 的 MySQL 存储过程

Topic   主题 Syntax   语法 Description   描述 Example   示例
Stored Procedures   存储过程 DELIMITER //
CREATE PROCEDURE PROCEDURE_NAME
BEGIN
END //
DELIMITER ;
 A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
一个 stored procedure 是一段预编译的 SQL 代码，可以保存下来，以便反复使用。

The default terminator for a stored procedure is semicolon (;). To set a different terminator we use DELIMITER clause followed by the terminator such as

or//.<br>存储过程的默认终止符是分号（;）。要设置不同的终止符，我们使用‘DELIMITER‘子句，后跟终止符，如 or //.<br>存储过程的默认终止符是分号（;）。要设置不同的终止符，我们使用 `DELIMITER` 子句，后跟终止符，如

或 //。		 DELIMITER //
CREATE PROCEDURE RETRIEVE_ALL()
BEGIN
SELECT * FROM PETSALE;
END //
DELIMITER ;

Transactions with Db2 与 Db2 的交易

Topic   主题 Syntax   语法 Description   描述 Example   示例
Commit command   提交命令 COMMIT;
 A COMMIT command is used to persist the changes in the database.
一个 COMMIT command 用于持久化数据库中的更改。

The default terminator for a COMMIT command is semicolon (;).
COMMIT 命令的默认终止符是分号（;）。		 CREATE TABLE employee(ID INT, Name VARCHAR(20), City VARCHAR(20), Salary INT, Age INT);
INSERT INTO employee( ID, Name, City, Salary, Age) VALUES( 1, ‘Priyanka pal’, ‘Nasik’, 36000, 21), (2, ‘Riya chowdary’, ‘Bangalor’, 82000, 29);
SELECT *FROM employee;
COMMIT;
Rollback command   回滚命令 ROLLBACK;
 A ROLLBACK command is used to rollback the transactions which are not saved in the database.
一个 ROLLBACK command 用于回滚未保存到数据库中的事务。

The default terminator for a ROLLBACK command is semicolon (;).
ROLLBACK 命令的默认终止符是分号（;）。		 As auto-commit is enabled by default, all transactions will be committed. We need to disable this option to see how rollback works.
For db2, we have to disable auto-commit manually. Click the gear icon located on the right side of the SQL Assistant window. Next, select the “On Success” drop-down and choose “commit after the last statement in the script” Remember to save your changes!


INSERT INTO employee VALUES (3, ‘Swetha Tiwari’, ‘Kanpur’, 38000, 38);
SELECT *FROM employee;
ROLLBACK;
SELECT *FROM employee;

Transactions with MySQL 与 MySQL 的交易

Topic   主题 Syntax   语法 Description   描述 Example   示例
Commit command   提交命令 COMMIT;
 A COMMIT command is used to persist the changes in the database.
A COMMIT command 用于在数据库中持久化更改。

The default terminator for a COMMIT command is semicolon (;).
COMMIT 命令的默认终止符是分号（；）。		 CREATE TABLE employee(ID INT, Name VARCHAR(20), City VARCHAR(20), Salary INT, Age INT);
START TRANSACTION;
INSERT INTO employee( ID, Name, City, Salary, Age) VALUES( 1, ‘Priyanka pal’, ‘Nasik’, 36000, 21), (2, ‘Riya chowdary’, ‘Bangalor’, 82000, 29);
SELECT *FROM employee;
COMMIT;
Rollback command   回滚命令 ROLLBACK;
 A ROLLBACK command is used to rollback the transactions which are not saved in the database.
A ROLLBACK command 用于回滚未保存到数据库中的事务。

The default terminator for a ROLLBACK command is semicolon (;).
ROLLBACK 命令的默认终止符是分号（；）。		 As auto-commit is enabled by default, all transactions will be committed. We need to disable this option to see how rollback works. For MySQL use the command “SET autocommit = 0;”
INSERT INTO employee VALUES (3, ‘Swetha Tiwari’, ‘Kanpur’, 38000, 38);
SELECT *FROM employee;
ROLLBACK;
SELECT *FROM employee;

Db2 Transactions using Stored Procedure 使用存储过程进行 Db2 事务

Topic   主题 Syntax   语法 Description   描述 Example   示例
Commit command   提交命令 –#SET TERMINATOR @
CREATE PROCEDURE PROCEDURE_NAME
BEGIN
COMMIT;
END
@
 A COMMIT command is used to persist the changes in the database.
COMMIT command 用于将数据库中的更改持久化。

The default terminator for a COMMIT command is semicolon (;).
提交命令的默认终止符是分号（;）。		 --#SET TERMINATOR @ CREATE PROCEDURE TRANSACTION_ROSE LANGUAGE SQL MODIFIES SQL DATA
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE retcode INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET retcode = SQLCODE;
UPDATE BankAccounts
SET Balance = Balance-200
WHERE AccountName = ‘Rose’;
UPDATE BankAccounts
SET Balance = Balance-300
WHERE AccountName = ‘Rose’;
IF retcode < 0 THEN
ROLLBACK WORK;
ELSE
COMMIT WORK;
END IF;
END
@
Rollback command   回滚命令 --#SET TERMINATOR @
CREATE PROCEDURE PROCEDURE_NAME
BEGIN
ROLLBACK;
COMMIT;
END
@
 A ROLLBACK command is used to rollback the transactions which are not saved in the database.
一个 ROLLBACK command 用于回滚未保存到数据库中的事务。

The default terminator for a ROLLBACK command is semicolon (;).
ROLLBACK 命令的默认终止符是分号 (;)。		 --#SET TERMINATOR @ CREATE PROCEDURE TRANSACTION_ROSE LANGUAGE SQL MODIFIES SQL DATA
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE retcode INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET retcode = SQLCODE;
UPDATE BankAccounts
SET Balance = Balance-200
WHERE AccountName = ‘Rose’;
UPDATE BankAccounts
SET Balance = Balance-300
WHERE AccountName = ‘Rose’;
IF retcode < 0 THEN
ROLLBACK WORK;
ELSE
COMMIT WORK;
END IF;
END
@

MySQL Transactions using Stored Procedure 使用存储过程进行 MySQL 事务

Topic   主题 Syntax   语法 Description   描述 Example   示例
Commit command   提交命令 DELIMITER //
CREATE PROCEDURE PROCEDURE_NAME
BEGIN
COMMIT;
END //
DELIMITER ;
 A COMMIT command is used to persist the changes in the database.
A COMMIT command 用于持久化数据库中的更改。

The default terminator for a COMMIT command is semicolon (;).
提交命令的默认终止符是分号（;）。		 DELIMITER //
CREATE PROCEDURE TRANSACTION_ROSE()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
UPDATE BankAccounts
SET Balance = Balance-200
WHERE AccountName = ‘Rose’;
UPDATE BankAccounts
SET Balance = Balance-300
WHERE AccountName = ‘Rose’;
COMMIT;
END //
DELIMITER ;
Rollback command   回滚命令 DELIMITER //
CREATE PROCEDURE PROCEDURE_NAME
BEGIN
ROLLBACK;
COMMIT;
END //
DELIMITER ;
 A ROLLBACK command is used to rollback the transactions which are not saved in the database.
一个 ROLLBACK command 用于回滚未保存到数据库中的事务。

The default terminator for a ROLLBACK command is semicolon (;).
ROLLBACK 命令的默认终止符是分号 (;)。		 DELIMITER //
CREATE PROCEDURE TRANSACTION_ROSE()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
UPDATE BankAccounts
SET Balance = Balance-200
WHERE AccountName = ‘Rose’;
UPDATE BankAccounts
SET Balance = Balance-300
WHERE AccountName = ‘Rose’;
COMMIT;
END //
DELIMITER ;