SQL Syntax handbook

This article deals with the general syntax of relational databases. Limited by space, this article focuses on the usage, and will not expand to explain the characteristics and principles.

I. Basic concepts

Database terminology

Database– A container that holds organized data (usually a file or a group of files).
Data Table– A structured list of a particular type of data.
Schema– Information about the layout and features of databases and tables. A schema defines how data is stored in a table, including information about what data is stored, how the data is broken down, and how the pieces of information are named. Both databases and tables have schemas.
Column (column)– A field in the table. All tables are composed of one or more columns.
Row (row)– A record in the table.
Primary key– A column (or set of columns) whose values uniquely identify each row in a table.

SQL syntax

The Structured Query Language (SQL) is managed by the ANSI Standards Council. Each DBMS has its own implementation, such as PL/SQL, Transact-SQL, and so on.

SQL Syntax Structure

SQL syntax structures include:

Clauses – are components of statements and queries. (In some cases, these are optional.)
Expressions – Can produce any scalar value, or database table composed of columns and rows
Predicates – Specify conditions for SQL three-valued logic (3VL) (true/false/unknown) or Boolean truth values that need to be evaluated and limit the effects of statements and queries, or change the flow of the program.
Query – Retrieves data based on specific criteria. This is an important part of SQL.
Statements – can permanently affect profiles and data, and can control database transactions, program flow, connections, sessions, or diagnostics.

SQL Syntax Key points

SQL statements are case insensitive, but whether database table names, column names, and values are differentiated depends on the specific DBMS and configuration.

For example, SELECT is the same as SELECT and SELECT.

Multiple SQL statements must be semicolon (;). Space.
All whitespace is ignored when processing SQL statements. SQL statements can be written in one line or multiple lines.

-- A row of SQL statements
UPDATE user SET username='robot'.password='robot' WHERE username = 'root';

-- Multi-line SQL statements
UPDATE user
SET username='robot'.password='robot'
WHERE username = 'root';
Copy the code

SQL supports three types of annotations

## note 1 -- Note 2 /* Note 3 */Copy the code

Classification of SQL

Data Definition Language (DDL)

Data Definition Language (DDL) is the Language responsible for defining Data structures and database objects in the SQL Language set.

The primary function of DDL is to define database objects.

The core DDL instructions are CREATE, ALTER, and DROP.

Data Manipulation Language (DML)

Data Manipulation Language (DML) is a programming statement that is used to operate on a database and run access to objects and Data in the database.

The main function of DML is to access data, so its syntax is based on reading and writing databases.

The core instruction of DML is INSERT, UPDATE, DELETE and SELECT. These four instructions are called CRUD(Create, Read, Update, Delete), that is, add, Delete, modify and check.

Transaction Control Language (TCL)

Transaction Control Language (TCL) is used to manage transactions in the database. These are used to manage changes made by DML statements. It also allows statements to be grouped into logical transactions.

The core commands of TCL are COMMIT and ROLLBACK.

Data Control Language (DCL)

Data Control Language (DCL) is a kind of instruction that can Control the Data access right. It can Control the Control of database objects such as Data tables, view tables, prestored programs, and user-defined functions by specific user accounts.

The core directives of the DCL are GRANT and REVOKE.

DCL mainly controls the access permission of users, so its instruction method is not complicated. The permissions that can be controlled by DCL are CONNECT, SELECT, INSERT, UPDATE, DELETE, EXECUTE, USAGE, REFERENCES.

Depending on the DBMS and the security entity, the permissions that are supported vary.

(The following is the DML statement usage)

2. Adding, deleting, modifying and checking

Add, delete, modify and check, also known as CRUD, the basic operation of the basic database operations.

Insert data

INSERT INTOStatement is used to insert new records into a table.

Insert the full row

INSERT INTO user
VALUES (10.'root'.'root'.'[email protected]');
Copy the code

Insert part of a row

INSERT INTO user(username, password, email)
VALUES ('admin'.'admin'.'[email protected]');
Copy the code

Insert the queried data

INSERT INTO user(username)
SELECT name
FROM account;
Copy the code

Update the data

UPDATEStatement is used to update records in a table.

UPDATE user
SET username='robot'.password='robot'
WHERE username = 'root';
Copy the code

Delete the data

DELETEStatement is used to drop a record in a table.

TRUNCATE TABLEYou can clear the table, that is, delete all the rows.

Deletes the specified data from a table

DELETE FROM user
WHERE username = 'robot';
Copy the code

Clear the table

TRUNCATE TABLE user;
Copy the code

Query data

SELECTStatement is used to query data from a database.

DISTINCTUsed to return a uniquely different value. It applies to all the columns, which means all the columns have the same value.

LIMITLimits the number of rows returned. You can have two arguments. The first argument is the starting line, starting at 0. The second argument is the total number of rows returned.

ASC: ascending (default)

DESC: descending

Query a single

SELECT prod_name
FROM products;
Copy the code

Query multiple columns

SELECT prod_id, prod_name, prod_price
FROM products;
Copy the code

Query all columns

ELECT *
FROM products;
Copy the code

Query different values

SELECT DISTINCT
vend_id FROM products;
Copy the code

Restricting query Results

Return the first 5 rows
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0.5;
-- Returns lines 3 through 5
SELECT * FROM mytable LIMIT 2.3;
Copy the code

Subquery

A subquery is an SQL query nested within a larger query. Subqueries are also called internal queries or internal selections, and statements that contain subqueries are also called external queries or external selections.

Subqueries can be nested within a SELECT, INSERT, UPDATE, or DELETE statement or within another subquery.
Subqueries are typically added in the WHERE clause of another SELECT statement.
You can use comparison operators such as >, <, or =. Comparison operators can also be multi-line operators, such as IN, ANY, or ALL.
Subqueries must be surrounded by parentheses ().
The internal query is first executed before its parent query so that the results of the internal query can be passed to the external query. The execution process can be seen in the following figure:

Subqueries of subqueries

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'RGAN01'));
Copy the code

WHERE

WHEREClause is used to filter records, that is, to narrow the scope of the data to be accessed.
WHEREFollowed by a returntrue 或 falseConditions.
WHERECan work withSELECT.UPDATE 和 DELETEUse them together.
Can be found inWHEREClause

The operator	describe
=	Is equal to the
<>	Is not equal to. Note: In some versions of SQL, this operator can be written as! =
>	Is greater than
<	Less than
> =	Greater than or equal to
< =	Less than or equal to
BETWEEN	Within a certain range
LIKE	Search for a pattern
IN	Specifies multiple possible values for a column

SELECTIn the statementWHEREclause

SELECT * FROM Customers
WHERE cust_name = 'Kids Place';
Copy the code

UPDATEIn the statementWHEREclause

UPDATE Customers
SET cust_name = 'Jack Jones'
WHERE cust_name = 'Kids Place';
Copy the code

DELETEIn the statementWHEREclause

DELETE FROM Customers
WHERE cust_name = 'Kids Place';
Copy the code

And IN BETWEEN

INThe operator inWHEREClause to select any of several specified values.
BETWEENThe operator inWHEREClause to select a value within a range.

IN the sample

SELECT *
FROM products
WHERE vend_id IN ('DLL01'.'BRS01');
Copy the code

BETWEEN the sample

SELECT *
FROM products
WHERE prod_price BETWEEN 3 AND 5;
Copy the code

AND, OR, NOT

AND,OR,NOTIs a logical processing instruction for filtering conditions.
ANDPriority overORTo clarify the processing order, you can use(a).
ANDThe operator indicates that both left and right conditions must be satisfied.
ORThe operator indicates that either of the left and right conditions can be met.
NOTOperator is used to negate a condition.

AND the sample

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4;
Copy the code

OR the sample

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
Copy the code

NOT the sample

SELECT *
FROM products
WHERE prod_price NOT BETWEEN 3 AND 5;
Copy the code

LIKE

LIKEThe operator inWHEREClause to determine whether a string matches a pattern.
Used only if the field is a text valueLIKE.
LIKESupports two wildcard matching options:% 和 _.
Don’t abuse wildcards. Wildcards at the beginning of the match are very slow.
%Represents any number of occurrences of any character.
_Indicates that any character occurs once.

% sample

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '%bean bag%';
Copy the code

_ sample

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '__ inch teddy bear';
Copy the code

Connect and combine

JOIN

If aJOINIf there is at least one common field and a relationship exists between themJOINYou can work on two or more tables.

Joins are used to join multiple tables, usingJOINKeyword, and conditional statements are usedONRather thanWHERE.

JOINKeep the base tables (structure and data) unchanged.

JOINThere are two types of connections: inner and outer.

INNER joins, also known as equivalent joins, use INNERJOINThe keyword. Returns the Cartesian product without a conditional statement.

Self-join can be viewed as a kind of inner join, except that the joining table is itself.

The natural link is to connect the same name through = test, the same name can have more than one.

Inner join vs. natural join

An inner join provides a join column, while a natural join automatically joins all the same names.

An outer join returns all rows in a table and only those rows from the secondary table that meet the join condition, that is, the columns in both tables are equal. External connection is divided into left external connection, right external connection, full external connection (Mysql does not support).

A left outer join preserves rows that are not associated with the left table.

A right outer join preserves rows that are not associated with the right table.

Joins vs subqueries

Joins can replace subqueries and are generally more efficient than subqueries.

INNER JOIN (INNER JOIN)

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
Copy the code

Since the connection

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
Copy the code

NATURAL JOIN

SELECT *
FROM Products
NATURAL JOIN Customers;
Copy the code

LEFT JOIN

SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;
Copy the code

RIGHT JOIN

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;
Copy the code

UNION

UNIONOperator combines the results of two or more queries and generates a result set containing the data fromUNIONExtract rows that participate in the query in.
UNIONThe basic rule
- The number and order of columns must be the same for all queries.
- The data types of the columns involved in each query must be the same or compatible.
- Typically, the column names returned are taken from the first query.
The default is to remove the same line, if you want to keep the same line, useUNION ALL.
Can contain only oneORDER BYClause, and must be at the end of the statement.
Application scenarios
- Returns structured data from different tables in a query.
- Perform multiple queries on a table and return data as a single query.

Combination query

SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('IL'.'IN'.'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4All';
Copy the code

JOIN vs UNION

JOIN vs UNION
- JOINThe columns in the join table may differ, but inUNION, the number and order of columns must be the same for all queries.
- UNIONPut the rows after the query together (vertically), butJOINThe columns after the query are put together (horizontally) so that it forms a Cartesian product.

Five, the function

🔔 Note: functions are often different from database to database and are therefore not portable. This section uses Mysql functions as an example.

Text processing

function	instructions
`LEFT()`,`RIGHT()`	The left or right character
`LOWER()`,`UPPER()`	Convert to lowercase or uppercase
`LTRIM()`,`RTIM()`	Remove the left or right Spaces
`LENGTH()`	The length of the
`SOUNDEX()`	Convert to a speech value

Where, SOUNDEX() converts a string into an alphanumeric pattern that describes its speech representation.

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')
Copy the code

Date and time processing

Date format:YYYY-MM-DD
Time format:HH:MM:SS

Number of letter	Said Ming
`AddDate()`	Add a date (day, week, etc.)
`AddTime()`	Add a time (hour, grade)
`CurDate()`	Return current date
`CurTime()`	Return current time
`Date()`	Returns the date portion of the date time
`DateDiff()`	Calculate the difference between the two dates
`Date_Add()`	Highly flexible date operation function
`Date_Format()`	Returns a formatted date or time string
`Day()`	Returns the number of days portion of a date
`DayOfWeek()`	For a date, return the day of the week
`Hour()`	Returns the hourly portion of a time
`Minute()`	Returns the minute portion of a time
`Month()`	Returns the month portion of a date
`Now()`	Returns the current date and time
`Second()`	Returns the second portion of a time
`Time()`	Returns the time portion of a date time
`Year()`	Returns the year portion of a date

mysql> SELECT NOW();
Copy the code

The 2018-4-14 20:25:11Copy the code

Numerical processing

function	instructions
SIN()	sine
COS()	cosine
TAN()	tangent
ABS()	The absolute value
SQRT()	The square root
MOD()	remainder
EXP()	index
PI()	PI
RAND()	The random number

summary

Number of letter	Said Ming
`AVG()`	Returns the average value of a column
`COUNT()`	Returns the number of rows in a column
`MAX()`	Returns the maximum value of a column
`MIN()`	Returns the minimum value of a column
`SUM()`	Returns the sum of the values in a column

AVG() ignores NULL rows.

Using DISTINCT allows the summary function value to summarize different values.

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable
Copy the code

Sort and group

ORDER BY

ORDER BYUse to sort a result set.
- ASC: ascending (default)
- DESC: descending
You can sort multiple columns and specify a different sort for each column

Specifies the sort direction of multiple columns

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;
Copy the code

GROUP BY

GROUP BYClause groups records into summary rows.
GROUP BYOne record is returned for each group.
GROUP BYAggregation is also commonly involved: COUNT, MAX, SUM, AVG, etc.
GROUP BYGroups can be grouped by one or more columns.
GROUP BYAfter sorting by group fields,ORDER BYYou can sort by summary fields.

grouping

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;
Copy the code

Sorting after grouping

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;
Copy the code

HAVING

HAVINGUsed to aggregateGROUP BYThe results are filtered.
HAVINGRequires that there be aGROUP BYClause.
WHERE 和 HAVINGIt can be in the same query.
HAVING vs WHERE
- WHERE 和 HAVINGIt’s all for filtering.
- HAVINGGroup records suitable for summary; WHERE is for a single record.

Use WHERE and HAVING to filter data

SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) > =1;
Copy the code

(The following is DDL statement usage)

7. Data definition

The primary function of DDL is to define database objects (such as databases, tables, views, indexes, and so on).

DATABASE

Creating a database

CREATE DATABASE test;
Copy the code

Deleting a database

DROP DATABASE test;
Copy the code

Select database

USE test;
Copy the code

Data TABLE

Create data table

Common create

CREATE TABLE user (
  id int(10) unsigned NOT NULL COMMENT 'Id',
  username varchar(64) NOT NULL DEFAULT 'default' COMMENT 'Username'.password varchar(64) NOT NULL DEFAULT 'default' COMMENT 'password',
  email varchar(64) NOT NULL DEFAULT 'default' COMMENT 'email'
) COMMENT='User table';
Copy the code

Create a new table based on an existing table

CREATE TABLE vip_user AS
SELECT * FROM user;
Copy the code

Delete data table

DROP TABLE user;
Copy the code

Modify a data table

Add columns

ALTER TABLE user
ADD age int(3);
Copy the code

Delete the column

ALTER TABLE user
DROP COLUMN age;
Copy the code

Modify the column

ALTER TABLE `user`
MODIFY COLUMN age tinyint;
Copy the code

Add a primary key

ALTER TABLE user
ADD PRIMARY KEY (id);
Copy the code

Remove the primary key

ALTER TABLE user
DROP PRIMARY KEY;
Copy the code

VIEW

define

A view is a visual table based on the result set of an SQL statement.

A view is a virtual table that contains no data of its own and cannot be indexed. You do the same thing with a view as you would with a normal table.

role

Simplify complex SQL operations, such as complex joins;

Only part of the data from the actual table is used;

Ensure data security by only giving users access to views;

Change the data format and presentation.

Create a view

CREATE VIEW top_10_user_view AS
SELECT id, username
FROM user
WHERE id < 10;
Copy the code

Delete the view

DROP VIEW top_10_user_view;
Copy the code

INDEX

role

Data can be queried more quickly and efficiently through indexes.

Indexes are not visible to the user; they can only be used to speed up queries.

Pay attention to

Updating a table with indexes takes more time than updating a table without indexes because the indexes themselves need to be updated. Therefore, it is ideal to create indexes only on the columns (and tables) that are often searched.

The only index

Unique indexes indicate that each index value of this index corresponds to only one data record.

Create indexes

CREATE INDEX user_index
ON user (id);
Copy the code

Creating a unique index

CREATE UNIQUE INDEX user_index
ON user (id);
Copy the code

Remove the index

ALTER TABLE user
DROP INDEX user_index;
Copy the code

The constraint

SQL constraints are used to specify data rules in a table.

If there is data behavior that violates the constraint, the behavior will be terminated by the constraint.
Constraints can be specified when the TABLE is created (through the CREATE TABLE statement) or after the TABLE is created (through the ALTER TABLE statement).
Constraint type
- NOT NULL– Indicates that a column cannot store NULL values.
- UNIQUE– Ensure that each row of a column must have a unique value.
- PRIMARY KEY– NOT A combination of NULL and UNIQUE. Ensuring that a column (or a combination of two columns) has a unique identity makes it easier and faster to find a particular record in a table.
- FOREIGN KEY– Ensure referential integrity that data in one table matches values in another table.
- CHECK– Ensure that the values in the column meet the specified conditions.
- DEFAULT– Specifies the default value for columns that are not assigned.

Create tables using constraints:

CREATE TABLE Users (
  Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'on the Id',
  Username VARCHAR(64) NOT NULL UNIQUE DEFAULT 'default' COMMENT 'Username'.Password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT 'password',
  Email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT 'Email address',
  Enabled TINYINT(4) DEFAULT NULL COMMENT 'Valid or not',
  PRIMARY KEY (Id))ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='User table';
Copy the code

(The following is TCL statement usage)

Viii. Transaction handling

You cannot roll back the SELECT statement, and it is meaningless to roll back the SELECT statement. CREATE and DROP statements cannot be rolled back.

MySQL commits implicitly by defaultWhen a statement is executed, it is treated as a transaction and committed. When there is aSTART TRANSACTIONStatement, the implicit commit is closed whenCOMMIT 或 ROLLBACKAfter the statement is executed, the transaction is automatically closed and the implicit commit resumes.

throughset autocommit=0You can cancel automatic submission untilset autocommit=1Will be submitted; The AUTOCOMMIT tag is for each connection and not for the server.

instruction

START TRANSACTIONThe – directive is used to mark the starting point of a transaction.

SAVEPOINTThe – directive is used to create a reservation point.

ROLLBACK TOThe – directive is used to roll back to the specified reservation point. If no reservation point is set, it falls back toSTART TRANSACTIONStatements.

COMMIT– Commit the transaction.

-- Start a transaction
START TRANSACTION;

Insert operation A
INSERT INTO `user`
VALUES (1.'root1'.'root1'.'[email protected]');

-- Create the reservation point updateA
SAVEPOINT updateA;

Insert operation B
INSERT INTO `user`
VALUES (2.'root2'.'root2'.'[email protected]');

-- roll back to the reserved point updateA
ROLLBACK TO updateA;

-- Commit transaction, only operation A takes effect
COMMIT;
Copy the code

(The following is the DCL statement usage)

Ix. Permission control

Grants and revoks control access at several levels:

GRANT ALL and REVOKE ALL for the entire server.

* ON database.*;

For specific tables, use ON database.table;

A specific column;

Specific stored procedures.

The newly created account does not have any permissions.

An account is defined in the form of username@host, and username@% uses the default host name.
MySQL account information is stored in the MySQL database.
USE mysql;
SELECT user FROM user;
Copy the code

Create account

CREATE USER myuser IDENTIFIED BY 'mypassword';
Copy the code

Changing the Account name

UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;
Copy the code

Delete the account

DROP USER myuser;
Copy the code

Check the permissions

SHOW GRANTS FOR myuser;
Copy the code

Grant permissions

GRANT SELECT.INSERT ON*. *TO myuser;
Copy the code

Remove permissions

REVOKE SELECT.INSERT ON*. *FROM myuser;
Copy the code

Change password

SET PASSWORD FOR myuser = 'mypass';
Copy the code

Stored procedures

A stored procedure can be viewed as a batch processing of a series of SQL operations;

Benefits of using stored procedures

Code encapsulation, to ensure a certain degree of security;

Code reuse;

Because it is pre-compiled, it has high performance.

Creating a stored procedure

Custom delimiters are required to create stored procedures on the command line because the command line is;Is the terminator, and the stored procedure also contains a semicolon. Therefore, this part of the semicolon will be incorrectly regarded as the terminator, resulting in a syntax error.

Contains in, OUT, and inout parameters.

Assign a value to a variable using a SELECT into statement.

Only one variable can be assigned at a time. Operations on collections are not supported.

Creating a stored procedure

DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int.IN b int.OUT sum int)
BEGIN
    DECLARE c int;
    if a is null then set a = 0;
    end if;

    if b is null then set b = 0;
    end if;

    set sum  = a + b;
END
;;
DELIMITER ;
Copy the code

Using stored procedures

set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;
Copy the code

11. Cursors

A cursor, “cursor,” is a database query stored on a DBMS server. It is not a SELECT statement, but a result set retrieved by that statement.

Using a cursor in a stored procedure allows you to move through a result set.

Cursors are primarily used for interactive applications where the user needs to browse and modify any row in the data set.

Four steps to using a cursor:

Declare the cursor, this process does not actually retrieve data;

Open the cursor;

Extract data;

Close the cursor;

DELIMITER $
CREATE  PROCEDURE getTotal()
BEGIN
    DECLARE total INT;
    Create a variable that receives cursor data
    DECLARE sid INT;
    DECLARE sname VARCHAR(10);
    -- Create the total variable
    DECLARE sage INT;
    -- Create an end flag variable
    DECLARE done INT DEFAULT false;
    -- Create a cursor
    DECLARE cur CURSOR FOR SELECT id.name,age from cursor_table where age>30;
    -- Specifies the value returned at the end of the cursor loop
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    SET total = 0;
    OPEN cur;
    FETCH cur INTO sid, sname, sage;
    WHILE(NOT done)
    DO
        SET total = total + 1;
        FETCH cur INTO sid, sname, sage;
    END WHILE;

    CLOSE cur;
    SELECT total;
END $
DELIMITER ;

-- Call the stored procedure
call getTotal();
Copy the code

Xii. Flip-flop

A trigger is a database object associated with a table operation that is invoked when a specified event occurs on the table where the trigger resides, that is, the table’s operation event touches the execution of the trigger on the table.

You can use triggers to conduct an audit trail to record changes to another table.

MySQL does not allow the use of CALL statements in triggers, which means stored procedures cannot be called.

BEGIN 和 END

When the trigger condition is met, the trigger execution action between BEGIN and END is executed.

🔔 Note: in MySQL, semicolon; Is the identifier for the end of a statement. A semicolon is encountered to indicate that the statement has ended and MySQL can start executing. Therefore, the interpreter encounters a semicolon in the trigger’s execution action and starts executing, then reports an error because no END is found that matches BEGIN.

This is where the DELIMITER command comes in. It is a command that does not require end-of-statement identifiers and has the syntax of DELIMITER new_delemiter. New_delemiter can be set to one or more length symbols. The default is a semicolon. We can change it to something else like $- DELIMITER $. After that, statements end with a semicolon, and the interpreter doesn’t react until $is encountered. Note that we should remember to change it back after using it.

NEW 和 OLD

It’s defined in MySQLNEW 和 OLDKeyword that represents the row in the trigger table that triggered the trigger.
inINSERTType FLIP-flop,NEWUsed to indicate that (BEFORE) or have (AFTER) insert new data;
inUPDATEType FLIP-flop,OLDUsed to represent the original data that will or has been modified,NEWUsed to represent new data to be or have been modified to;
inDELETEType FLIP-flop,OLDUsed to represent the original data that will or has been deleted;
Usage:NEW.columnNameColumnName = columnName; columnName = columnName;

Creating a trigger

Tip: In order to understand the main points of triggers, it is important to look at the instructions for creating triggers.

The CREATE TRIGGER directive is used to CREATE a TRIGGER.

Grammar:

CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
  trigger_statements
END;
Copy the code

Description:

Trigger_name: indicates the name of the trigger
Trigger_time: the time when the trigger fires. Values forBEFORE 或 AFTER.
Trigger_event: The listener event for the trigger. Values forINSERT,UPDATE 或 DELETE.
Table_name: specifies the target of the trigger. Specifies the table on which the trigger is created.
FOR EACH ROW: ROW level monitoring, Mysql fixed writing, other DBMS different.
Trigger_statements: triggers to execute actions. Is a list of one or more SQL statements. Each statement in the list must use a semicolon;To the end.

Example:

DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO `user_history`(user_id, operate_type, operate_time)
    VALUES (NEW.id, 'add a user'.now());
END $
DELIMITER ;
Copy the code

View triggers

SHOW TRIGGERS;
Copy the code

Delete trigger

DROP TRIGGER IF EXISTS trigger_insert_user;
Copy the code

(after)

The resources

Benforta.sql must know must be [M]. Posts and Telecommunications Press, 2013.
“Shallow in, deep out” transactions in MySQL implementation
MySQL – Triggers
Wikipedia entry – SQL
www.sitesbay.com/sql/index
SQL Subqueries
Quick breakdown of the types of joins
SQL UNION
SQL database security
Stored procedure in Mysql