This is the sixth day of my participation in the August More text Challenge. For details, see: August More Text Challenge
Hello, I’m [Bean Dried peanut], this time I brought a full set of SQL, mysql study notes, will be updated later ~
Today I’m going to share my week 6 notes. In this module, you will learn some advanced SQL statements such as view, Stored Procedure, and Transaction. And join operations.
This is my study notes to learn SQL /mysql from scratch, and will be updated later, welcome to follow ~
Note: This is a study note for coursera course Databases and SQL for Data Science with Python
Learning goals
- Describe the benefits that views provide
- Create and query views
- Describes the pros and cons of using stored procedures
- Create stored procedures and call them from other code
- Describe the importance of ACID trading
- Use transactions in your SQL code
- How to use different types of JOIN operators
- Use joins to query data from multiple tables
View, Stored procedure and Transaction
That is, views, stored procedures, and transactions.
1.views
In SQL, a view is another way to represent data that exists in one or more tables.
Just like a real table, it contains rows and columns. The fields in the view are the fields in one or more actual tables in the database.
Although views can be queried just like tables, they are dynamic.
Store only the definition of the view, not the data.
How does the syntax of the CREATE VIEW statement look?
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Copy the codeHow does the syntax of the REPLACE VIEW statement look?
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Copy the codeHow does the syntax of the DROP VIEW statement look?
DROP VIEW view_name;
Copy the codeExercise 1: Create a view
CREATE VIEW EMPSALARY AS
SELECT cust_id, cust_name, cust_city, cust_country, cust_email, cust_state
FROM customers;
SELECT * FROM EMPSALARY;
Copy the code
Exercise 2: Update views
Make one of the two pictures
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;
Copy the code
Exercise 3: Delete views
DROP VIEW EMPSALARY;
Copy the code
2.stored procedures
A stored procedure is a set of SQL statements that are stored and executed on a database.
Therefore, you encapsulate multiple SQL statements instead of sending them from the client to the server. Store them in a stored procedure on the server and send a statement from the client to execute them.
Stored procedures can be useful if you have SQL queries written over and over again. You can save it as a stored procedure and then call it to execute it.
You can write stored procedures in many different languages. For example, for DB2 on Cloud and DB2, you can write the language using SQL PL, PL/SQL, Java, C, or other languages.
In general, a stored procedure is a wrapped function that is then called directly.
They can take information as a parameter, perform create, Read, Update, and Delete (CRUD) operations, and return the results to the client application.
The benefits of stored procedures include:
Reduce network traffic because multiple statements can be executed with a single call. Because the processing takes place on the server where the data is stored, the performance is improved and the end result is passed back only to the customer.
Reuse code because multiple applications can use the same stored procedure for the same job.
Improved security because a) you don’t need to expose all the information that tables and columns provide to client developers; B) You can use server-side logic to validate data before accepting it into the system.
Keep in mind, however, that SQL is not a fully fledged programming language, so you should not attempt to write all your business logic in a stored procedure.
Exercise 1 Creating a stored procedure
Blog.csdn.net/qpzkobe/art… This blog post is very well written and provides two ways to write SQL: one is to open navicat to select the database where you want to create the stored procedure, and the other is to use the toolbar to open the query and new query window.
Here’s the first option:
Run it:
Of course, you can also use SQL statements to call stored procedures:
Blog.csdn.net/qq_41573234…
Note two questions here:
1. When saving the stored procedure,
Write like this:
To save.
2. Call a stored procedure in a SQL file and write @ before the output variable:
If you want to delete the stored procedure, copy the code below and click Run All.
DROP PROCEDURE RETRIEVE_ALL;
Copy the codeExercise 2 Executing a stored procedure
We can also update the data with stored procedures that input variable values.
- The routine will contain SQL queries to update the sale price of the animal in the PETSALE table based on the animal’s health condition, BAD or WORSE.
- The program routine takes the animal ID and health status as parameters, which are used to update the sale price of the animal in the PETSALE table based on the animal’s health status. Think –
- For animals with ID XX with poor health conditions, the sale price will be further reduced by 25%.
- For animals with a WORSE health condition and ID YY, the sale price will be further reduced by 50%.
- For animals with ID ZZ with other health conditions, the sale price does not change.
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
Copy the code
The input values:
Previous and subsequent results:
Of course, you can also type in the SQL file:
Of course, every time you enter bad, the price will get lower and lower…
3.acid transactions
An ACID transaction is one in which all SQL statements must complete successfully or none at all. This ensures that the database is always in a consistent state. ACID stands for atomic, consistent, isolated, durable. SQL commands BEGIN, COMMIT, and ROLLBACK are used to manage ACID transactions. SQL commands can be invoked from languages such as C, R, and Python.
Database transactions must be ACID (atomic, consistent, isolated, and persistent). The effects of all SQL statements in a transaction can be applied to the database using the COMMIT command or undone from the database using the ROLLBACK command.
COMMIT (to permanently store changes made in a transaction in the table), and ROLLBACK (to undo transactions that have not been saved in the table). ROLLBACK can only be used to undo changes in the current unit of work.
Exercise 1 Create a data set
-- 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 ' Up ', 8600), (' Brogues, 10150), (' Trainers', 14300); -- Retrieve all records from the tableSELECT * FROM ShoeShop;Copy the code-- 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;Copy the codeExercise 2 Update the data set
- You will create a stored procedure routine named TRANSACTION_ROSE that will contain TCL commands such as COMMIT and ROLLBACK.
- Now, develop routines to execute transactions based on a given scenario.
- ** Scene: ** Let’s buy Rose a pair of boots from the ShoeShop. Therefore, we must update the Rose balance and ShoeShop balance in the BankAccounts table. Then, we must also update the Boots inventory in the ShoeShop table. After Boots, we tried to buy a pair of sneakers from Rose.
- To create a stored procedure routine on Db2, copy the following code and paste it into the text box on the Run SQL page. Click Run All.
Create a stored procedure:
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; ENDCopy the codeSELECT * FROM BankAccounts; SELECT * FROM ShoeShop;Copy the code
We can observe that the transaction has been executed. However, when we look at these tables, none of the changes have been permanently saved by COMMIT. All possible changes that occurred may have been undone by ROLLBACK because the entire transaction failed due to a FAILED SQL statement or more. Let’s take a look at the possible reasons behind transaction failures and how COMMIT-ROLLBACK works in stored procedures:
- The first three updates should run successfully. The balance for both Rose and ShoeShop should have been updated in the BankAccounts table. Rose’s current balance should be 300-200 (the price of a pair of boots) =100. The current balance of ShoeShop should be 124200 + 200 = 124400. Successful purchase of Rose, 11-1 = 10.
- The last UPDATE statement tried to buy Rose a pair of sneakers, but after buying a pair of Boots, she had insufficient balance (Rose’s current balance: 100
- The SQLCODE, as a separate host variable, contains success/failure/warning information for each SQL statement execution. Now, since the SQLCODE variable is reset when the next SQL statement is run, retCode is our local variable to capture the return value of this SQLCODE. If the execution is unsuccessful, the SQLCODE returns a negative value for each SQL statement. Therefore, all changes are rolled back in the event of any errors. Commit only after the transaction has successfully executed without any errors.
4.summary and highlights
- A view is a dynamic mechanism for displaying data in one or more tables. A transaction represents a complete unit of work, which can be one or more SQL statements.
- An ACID transaction is one that all SQL statements must complete successfully, or not complete at all.
- A stored procedure is a set of SQL statements that are stored and executed on a database server, allowing you to send one statement as an alternative to sending multiple statements.
- You can write stored procedures in many different languages, such as SQL PL, PL/SQL, Java, and C.
2. Join statements
1. Knowledge
You can use the JOIN operator to do the following: Merge rows from two or more tables. The joined table is associated with a common column, usually the primary key of one table, which is displayed as a foreign key in the other table. There are two types of joins: internal joins and external joins.
** Inner joins only return rows of the table that have matching values in the common column. ** In general, the primary key of one table exists as a foreign key in the second table. Rows from the join table that do not have matching values do not appear in the result.
** Multiple outer joins can be used to complete the result set. The left outer join returns all rows in the left table, ** all rows forming the right table with the inner join will return the table and all rows in the first table have no match in the second table. The right outer join returns all rows that the inner join will return and all rows that have no match in the first table in the second table. A full outer join returns all matching rows in both tables and all rows in both tables with no matching entries.
2. The SQL statement
How is the CROSS JOIN (also known as a Cartesian JOIN) statement syntax displayed?
SELECT column_name(s)FROM table1CROSS JOIN table2;
Copy the codeWhat does the syntax of the INNER JOIN statement look like?
SELECT column_name(s)FROM table1INNER JOIN table2ON table1.column_name = table2.column_name;WHERE condition;
Copy the codeHow does the syntax of the LEFT OUTER JOIN statement look?
SELECT column_name(s)FROM table1LEFT OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition;
Copy the codeHow does the syntax of the RIGHT OUTER JOIN statement look?
SELECT column_name(s)FROM table1RIGHT OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition;
Copy the codeHow does the syntax of the FULL OUTER JOIN statement look?
SELECT column_name(s)FROM table1FULL OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition;
Copy the codeWhat does the syntax of the SELF JOIN statement look like?
SELECT column_name(s)FROM table1 T1, table1 T2WHERE condition;
Copy the codePractice 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';Copy the code4.Summary & Highlights
- A join combines rows from two or more tables based on the relationship between some of the columns in those tables.
- To merge data from three or more different tables, simply add a new join to the SQL statement.
- There are two types of table joins: internal joins and external joins; And three outer joins: left outer join, right outer join and full outer join.
- The most common type of join is an inner join, which matches the results in two tables and returns only the matching rows.
- You can use aliases as shorthand for table or column names.
- You can use a self-join to compare rows in the same table.
All see here, not as good as a like oh ~
