Database System [2] - SQL
Structured Query Language (SQL)
The Language you talk with DBMS.
Some International standard:
- Database definition - Create new DB and tables, modify table definition
CREATE TABLE
,ALTER TABLE
- Manipulation - retrieval and modification of rows
Select
,insert
,update
,delete
- Control - integrity and security constraints
Grant
,revoke
Data Types in SQL
- boolean :
BOOLEAN
- character :
CHAR
,VARCHAR
- bit :
BIT
,BIT VARYING
- exact numeric :
NUMERIC
,DECIMAL
,INTEGER
,SMALLINT
- approximate numeric :
FLOAT
,REAL
,DOUBLE PRECISION
- datetime :
DATE
,TIME
,TIMESTAMP
- interval :
INTERVAL
- large objects :
CHARACTER LARGE OBJECT
,BINARY LARGE OBJECT
Creating Databases
When creating a database, it is important to understand the differences between database schemas and database instances.
Database Schemas (Tables)
- The entire configuration of the database, including all of its tables, relations, index, etc.
- is specified during database design
Database Instance (Datas of Table)
- The data in the database at any particular point in time
Creating / Deleting Databases
Syntax:
1 | CREATE {DATABASE || SCHEMA} [IF NOT EXISTS] db_name [create_specification]... |
1 | DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] |
InnoDB
InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.7, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.
1 | -- Default storage engine = InnoDB. |
Constratints on Foreign Keys
1 | ON DELETE NO ACTION |
The reference option can be one of the following:
- NO ACTION: Server rejects the delete or update operation for the parent table if there is a related foreign key value in the child table.
- RESTRICT: Rejects the delete or update operation for the parent table.
- CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table.
- SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.
Creating Tables
SELECT Statement: Retrieval
Syntax :
1 | SELECT <list of column expressions> |
Use of DISTINCT
Syntax :
1 | SELECT DISTINCT < Database Instance > FROM < Database Schemas >; |
Use of Alias
You can give a Alias (Second Name) to the objects.
Syntax :
1 | SELECT < Database Instance > AS < alias > FROM < Database Schemas >; |
Use of ORDER BY
The ORDER BY
keyword is used to sort the result-set in ascending or descending order.
The ORDER BY
keyword sorts the records in ascending order by default (ASC
) keyword. To sort the records in descending order, use the DESC
keyword.
Syntax :
1 | SELECT < Database Instance > FROM < Database Schemas > |
Note < Database Instance > can be multiple.
Data Manipulation Statements
INSERT
UPDATE
DELETE
Use of INSERT
The INSERT
keyword is used to adds one or more rows.
Syntax :
1 | INSERT INTO < Database Schemas > |
Use of UPDATE
The UPDATE
keyword is used to modify the existing records in a table.
use SET
to give the data a new value.
It can be used with a WHERE
clause
Syntax :
1 | UPDATE < Database Schemas > |
Use of DELETE
The DELETE
keyword is used to remove one or more rows.
It can be used with a WHERE
clause
Delete specific datas
Syntax :
1 | DELETE FROM < Database Schemas > |
Delete all rows in a table
Syntax :
1 | DELETE FROM < Database Schemas >; |
Advanced SQL
Joining Tables
Use of JOIN
A JOIN
clause is used to combine rows from two or more tables, based on a related column between them.
Example:
List the order number, the order date, the customer number, the customer name, the customer’s state, and the shipping state in which the customer’s state differs from the shipping’s state.
1 | SELECT OrderNo, OrderDate, CustNo, `Order`.CustName, Customer.State AS 'Customer State', `Order`.State AS 'Shipping State' |
Use of Cross Join
Example:
List the order number, the order date, the customer number, the customer name, the customer’s state, and the shipping state in which the customer’s state differs from the shipping’s state.
1 | SELECT OrderNo, OrderDate, CustNo, `Order`.CustName, Customer.State AS 'Customer State', `Order`.State AS 'Shipping State' |
Use of Multiple Join
Example:
It is also ok to do something like this:
1 | SELECT CustNo, FirstName, LastName, |
OR something like this:
1 | SELECT CustNo, FirstName, LastName, |
Different Types of SQL JOINs
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
In mysql, We use LEFT JOIN UNION RIGHT JOIN to get FULL JOIN.
E.g.
1
2
3
4
5
6
7 SELECT * FROM `Customer`
LEFT JOIN `Employee` ON (Employee.FirstName = Customer.FirstName
AND Employee.LastName = Customer.LastName)
UNION
SELECT * FROM `Customer`
RIGHT JOIN `Employee` ON (Employee.FirstName = Customer.FirstName
AND Employee.LastName = Customer.LastName);
Nested Queries
Syntax:
1 | SELECT <Database Instance> |
Example:
1 | SELECT CustNo, FirstName, LastName |
Nested Queries for custom table
You can also use nested queries for FROM
.
1 | SELECT MAX(Total) |
Example:
1 | SELECT SUM(CPD)/COUNT(orderNumber) |
Summarizing Tables
Use of COUNT
COUNT()
function returns the number of rows that matches a specified criteria.
Syntax :
1 | SELECT COUNT( < Database Instance > ) |
Use of GROUP BY
indicate columns to summarize on.
The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.
The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
1 | SELECT column_name(s) |
Use of HAVING
The HAVING
clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
Syntax :
1 | SELECT < Database Instance > |
Difference between WHERE and HAVING
More Functions
Use of BETWEEN
Syntax/Example:
1 | SELECT * |
Use of CONCAT
Add serval strings together.
Syntax/Example:
1 | SELECT CONCAT(firstName," ", lastName) AS 'Full Name'; -- Result: firstName lastName |
Use of LENGTH
Syntax/Example:
1 | SELECT LENGTH("SQL") AS LengthOfString; -- Result: 3 |
Use of MAX and MIN
Syntax/Example:
1 | SELECT MAX(Price) AS LargestPrice FROM Products; |
Use of SUBSTRING
Syntax/Example:
1 | SELECT (SUBSTRING(Price, 2) AS 'Price without $' FROM Products; |
Use of STR_TO_DATE
Syntax/Example:
1 | SELECT * FROM `Order` |
Use of LIMIT
Syntax/Example:
Find top 5 creditLimit
1 | SELECT creditLimit FROM customers |
LIMIT IS VERY USEFUL IN MANY SITUATION.
Use of DATEDIFF
Syntax/Example:
1 | SELECT DATEDIFF(largedate1, smallerdate2) |
Use of MONTH and DAY and Year
Syntax/Example:
1 | SELECT MONTH("2017-06-15"); -- Return the month part of a date |
View
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
1 | CREATE VIEW view_name AS |
Example
1 | CREATE VIEW `Q1a_View` AS |
More Advanced SQL
Programming in MySQL
Add procedural programming constructs to SQL.
- SQL/PSM (Persistent Stored Modules)
- Extension of SQL
- PL/SQL (Procedural Language/SQL)
- Oracle’s procedural extension to SQL
Defining Stored Procedure
PROCEDURE
Each stored program contains a body that consists of an SQL statement. This statement may be a compound statement made up of several statements separated by semicolon (;
) characters. For example, the following stored procedure has a body made up of a BEGIN ... END
block that contains a SET
statement and a REPEAT
loop that itself contains another SET
statement:
1 | CREATE PROCEDURE dorepeat(p1 INT) |
If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.
DELIMITER
To redefine the mysql delimiter, use the delimiter
command.
In mysql, ;
denote the end of sql statement.
Now with delimiter, you can change the end of sql statement to other things else.
In this example, we are going to change the the end of sql statement to //
.
After we finish writing the PROCEDURE,
We need to change back the delimiter to ;
to avoid confusion.
1 | mysql> delimiter // |
You can redefine the delimiter to a string other than //
, and the delimiter can consist of a single character or multiple characters. You should avoid the use of the backslash (\
) character because that is the escape character for MySQL.
The following is an example of a function that takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter
because the function definition contains no internal ;
statement delimiters:
1 | mysql> CREATE FUNCTION hello (s CHAR(20)) |
Declaration of Variables
Variables and constant variables must be declared before they can be referenced.
Syntax :
1 | DECLARE variable_name datatype(size) DEFAULT default_value; |
Example :
1 | DELIMITER $$ |
1 | -- To use the procedure (Call the function) |
Assign Values to Variables
Variables can be assigned in two ways:
- Using the
SET
statement - Using an SQL
SELECT
orFETCH
statement
Example Using the SET
statement :
1 | DELIMITER $$ |
What is the difference between procedure variable (y) and session variable (@y)?
- session variable (@y) does not require declaration, can be used in any SQL query or statement, not visible to other sessions, and exists until the end of the current session.
- procedure variable (y) is reinitialized to NULL each time the procedure is called, while the session-specific variable (@y) is not.
- procedure variable (y) would be locally scoped variable (in a stored procedure)
- while your connection with the database exists, session variable (@y) can still be used.
Example Using the SELECT
or FETCH
statement
In this case, We use INTO
to store the selected value.
1 | DROP PROCEDURE IF EXISTS compute_sale $$ |
Control Statements
Conditional IF statement
The IF()
function returns a value if a condition is TRUE, or another value if a condition is FALSE.
Example
1 | DELIMITER $$ |
Conditional CASE statement
The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result.
If no conditions are true, it will return the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
Syntax
1 | CASE case_expression |
Example
Double/half the price if the total stock is larger/smaller than 500
1 | DELIMITER $$ |
Return the number of days required for shipping out an order based on the recipient’s city
1 | DELIMITER $$ |
Iteration statement (WHILE)
Syntax :
1 | WHILE expression DO |
Example
1 | DELIMITER $$ |
Iteration statement (REPEAT)
It is basically DO…WHILE Loop.
The statements will run at least once.
Syntax :
1 | REPEAT |
Example:
1 | DELIMITER $$ |
Iteration statement (LOOP)
Example:
1 | BEGIN |
Exception Handling in MySQL
Good for handling error caused by SQL operations
Syntax :
1 | DECLARE {CONTINUE | EXIT} HANDLER FOR |
Examples :
1 | DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1; |
If an error occurs, set the value of the variable has_error to 1 and continue the execution.
1 | DECLARE CONTINUE HANDLER FOR 1062 |
Display an error message when ERROR 1062 occurs
Example:
1 | DELIMITER $$ |
Cursors in MySQL
Useful for accessing result sets one row at a time.
What is Cursor ? (Like a Pointer)
- Allows the rows of a query result to be accessed one at a time
- Must be declared and opened before use
- Must be closed to deactivate it after it is no longer required
- Read-only - you cannot update data in the underlying table through the cursor.
- Non-scrollable - you can only fetch rows in the order determined by the SELECT statement. You cannot fetch rows in the reversed order. In addition, you cannot skip rows or jump to a specific row in the result set.
Working with MySQL Cursors
Step 1: Declare a cursor (after any variable declaration):
1 | DECLARE cursor_name CURSOR FOR SELECT_statement; |
Step 2: Open a cursor
1 | OPEN cursor_name; |
Step 3: Use the FETCH statement to retrieve the next row pointed to by the cursor and move the cursor to the next row in the result set
1 | FETCH cursor_name INTO variables list; |
Step 4: call the CLOSE statement to deactivate the cursor and release the memory associated with it
1 | CLOSE cursor_name; |
Example:
To change the price of product depending on stock
1 | DELIMITER $$ |
Triggers
Very useful for checking data and for ensure data integrity.
Defines an action that the database should take when some events occur in the application.
- Triggers can be used for checking values to be inserted into a table or for calculating values involved in an update.
- A trigger is defined to activate when a statement inserts, updates, or deletes rows in the associated table. These row operations are called “trigger events”.
- Database trigger is a powerful tool for protecting the integrity of data
Working with Triggers
1 | CREATE |
BEFORE INSERT
- activated before data is inserted into the table.AFTER INSERT
- activated after data is inserted into the table.BEFORE UPDATE
- activated before data in the table is updated.AFTER UPDATE
- activated after data in the table is updated.BEFORE DELETE
- activated before data is removed from the table.AFTER DELETE
- activated after data is removed from the table.
Syntax
1 | CREATE TRIGGER trigger_name trigger_time trigger_event |
OLD
keyword refers to the existing record before you change the data
NEW
keyword refers to the new row after you change the data.
Example:
Create a table to store the old value on other table before update.
1 | CREATE TABLE employees_audit ( |
Create a BEFORE UPDATE trigger. It is triggered when a change made to employees
table.
1 | DELIMITER $$ |
NOW when you do a update, the old record is putted into employees_audit
.