SQL, namely structured query language, is a database query and programming language, which is used to access data and query, update and manage relational database system; SQL language is the widely used standard language of relational database and the basis of various database interaction methods. The following will show how to use SQL to access and process data in the database.

SQL syntax

• SQL syntax
  • 1, Foundation
  • 2, Create table
  • 3, Modify table
  • 4, Insert
  • 5, Renew
  • 6, Delete
  • 7, Inquiry
    • DISTINCT
    • LIMIT
  • 8, Sort
  • 9, Filter
  • 10, Wildcard
  • 11, Calculation field
  • 12, Function
    • Summary
    • text processing
    • Date and time processing
    • Numerical processing
  • 13, Grouping
  • 14, Subquery
  • 15, Connect
    • Inner connection
    • Self connection
    • Natural connection
    • External connection
  • 16, Combined query
  • 17, View
  • 18, Stored procedure
  • 19, Cursor
  • 20, Trigger
  • 21, Transaction management
  • 22, Character set
  • 23, Authority management
  • reference material

1, Foundation

Schema defines how data is stored, what kind of data is stored, and how data is decomposed. Databases and tables have schemas.

The primary key value cannot be modified or reused (the deleted primary key value cannot be assigned to the primary key of the new data row).

SQL statements are not case sensitive, but whether the database table name, column name and value are distinguished depends on the specific DBMS and configuration.

SQL supports the following three annotations:

## notes
SELECT *
FROM mytable; -- notes
/* Note 1
   Note 2 */

Database creation and use:

CREATE DATABASE test;
USE test;

2, Create table

CREATE TABLE mytable (
  # int type, not null, self increment
  id INT NOT NULL AUTO_INCREMENT,
  # int type, which cannot be empty. The default value is 1, which cannot be empty
  col1 INT NOT NULL DEFAULT 1,
  # Variable length string type, up to 45 characters long, can be empty
  col2 VARCHAR(45) NULL,
  # Date type; can be blank
  col3 DATE NULL,
  # Set primary key to id
  PRIMARY KEY (`id`));

3, Modify table

Add column

ALTER TABLE mytable
ADD col CHAR(20);

Delete column

ALTER TABLE mytable
DROP COLUMN col;

Delete table

DROP TABLE mytable;

4, Insert

Normal insertion

INSERT INTO mytable(col1, col2)
VALUES(val1, val2);

Insert retrieved data

INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;

Inserts the contents of a table into a new table

CREATE TABLE newtable AS
SELECT * FROM mytable;

5, Renew

UPDATE mytable
SET col = val
WHERE id = 1;

6, Delete

DELETE FROM mytable
WHERE id = 1;

TRUNCATE TABLE can empty the table, that is, delete all rows.

TRUNCATE TABLE mytable;

When using update and delete operations, you must use the WHERE clause, otherwise the data of the whole table will be destroyed. You can first test with the SELECT statement to prevent erroneous deletion.

7, Inquiry

DISTINCT

The same value occurs only once. It works on all columns, that is, all columns are the same only if their values are the same.

SELECT DISTINCT col1, col2
FROM mytable;

LIMIT

Limit the number of rows returned. There can be two parameters. The first parameter is the starting line, starting from 0; The second parameter is the total number of rows returned.

Return to the first 5 lines:

SELECT *
FROM mytable
LIMIT 5;

SELECT *
FROM mytable
LIMIT 0, 5;

Return to lines 3 to 5:

SELECT *
FROM mytable
LIMIT 2, 3;

8, Sort

• ASC: ascending (default)
• DESC: descending order

You can sort by multiple columns and specify different sorting methods for each column:

SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;

9, Filter

The data that is not filtered is very large, which leads to the transmission of redundant data through the network, thus wasting the network bandwidth. Therefore, try to use SQL statements to filter unnecessary data, instead of transmitting all data to the client and then filtering by the client.

SELECT *
FROM mytable
WHERE col IS NULL;

The following table shows the operators available in the WHERE clause

Operator	explain
=	be equal to
<	less than
>	greater than
<> !=	Not equal to
<= !>	Less than or equal to
>= !<	Greater than or equal to
BETWEEN	Between two values
IS NULL	NULL value

It should be noted that NULL is different from 0 and empty string.

AND and OR are used to connect multiple filter conditions. Give priority to AND. When a filter expression involves multiple AND and AND OR, you can use () to determine the priority, making the priority relationship clearer.

A set of matching values of the SELECT clause can also be used to match a set of values of the SELECT clause.

The NOT operator is used to negate a condition.

10, Wildcard

Wildcards are also used in filtering statements, but they can only be used in text fields.

• %Match > = 0 arbitrary characters;

• _ Match = = 1 arbitrary character;

• [] can match characters in the set, for example, [ab] will match characters a or b. The caret ^ can be used to negate it, that is, it does not match the characters in the set.

Use Like for wildcard matching.

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- Not to A and B Any text at the beginning

Don't abuse wildcards. Wildcards at the beginning will match very slowly.

11, Calculation field

The conversion and formatting of data on the database server is often much faster than that on the client, and if the amount of converted and formatted data is less, the network traffic can be reduced.

Calculated fields usually need to be aliased with AS, otherwise the field name is calculated expression when outputting.

SELECT col1 * col2 AS alias
FROM mytable;

CONCAT() is used to connect two fields. Many databases use spaces to fill a value into the column width, so there will be some unnecessary spaces in the result of the connection. Using TRIM() can remove the leading and trailing spaces.

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

12, Function

The functions of each DBMS are different, so they are not portable. The following are mainly MySQL functions.

Summary

Function	explain
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.

Use DISTINCT to summarize different values.

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

text processing

function	explain
LEFT()	Left character
RIGHT()	Right character
LOWER()	Convert to lowercase characters
UPPER()	Convert to uppercase characters
LTRIM()	Remove the space on the left
RTRIM()	Remove the space on the right
LENGTH()	length
SOUNDEX()	Convert to voice value

Among them, SOUNDEX() can convert a string into an alphanumeric mode describing its voice representation.

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

Date and time processing

• Date format: YYYY-MM-DD
• Time format: HH: < zero width space > mm: SS

Function	explain
ADDDATE()	Add a date (day, week, etc.)
ADDTIME()	Add a time (hour, minute, etc.)
CURDATE()	Returns the current date
CURTIME()	Returns the current time
DATE()	Return date of time part
DATEDIFF()	Calculate the difference between two dates
DATE_ADD()	Highly flexible date operation function
DATE_FORMAT()	Returns a formatted date or time string
DAY()	Returns the days portion of a date
DAYOFWEEK()	For a date, return the corresponding day of the week
HOUR()	Returns the hour 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();

2018-4-14 20:25:11

Numerical processing

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

13, Grouping

Put rows with the same data values in the same group.

You can use the summary function to process the same grouped data, such as averaging the grouped data.

In addition to grouping by this field, the specified grouping field will also be sorted automatically by this field.

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col;

GROUP BY automatically sorts by grouping field, and ORDER BY can also sort by summary field.

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

WHERE filters rows and HAVING filters groups. Row filtering should precede group filtering.

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

Grouping provisions:

• The GROUP BY clause appears after the WHERE clause and before the ORDER BY clause;
• Except for summary fields, each field in the SELECT statement must be given in the GROUP BY clause;
• NULL guilds are grouped separately;
• Most SQL implementations do not support GROUP BY columns with variable length data types.

14, Subquery

Only one field of data can be returned in a subquery.

The results of the subquery can be used as the filter criteria of the WHRER statement:

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);

The following statement can retrieve the order quantity of customers, and the sub query statement will be executed once for each customer retrieved by the first query:

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

15, Connect

JOIN is used to connect multiple tables, use the JOIN keyword, and the conditional statement uses ON instead of WHERE.

Joins can replace subqueries and are generally faster than subqueries.

You can use AS to alias column names, calculated fields and table names. Aliasing table names is to simplify SQL statements and connect the same tables.

Inner connection

INNER JOIN, also known as equivalent join, uses the INNER JOIN keyword.

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

You can use ordinary query without explicitly using INNER JOIN, and connect the columns to be connected in the two tables with equivalent method in WHERE.

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

Self connection

Self join can be regarded as a kind of inner join, but the connected table is itself.

An employee table, including the name of the employee and the Department to which the employee belongs. Find out the names of all employees in the same department as Jim.

Subquery version

SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

Self connected version

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

Natural connection

Natural connection is to connect the columns with the same name through equivalence test. There can be multiple columns with the same name.

The difference between inner connection and natural connection: inner connection provides connected columns, while natural connection automatically connects all columns with the same name.

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

External connection

The outer join retains those rows that are not associated. It is divided into left external connection, right external connection and all external connection. Left external connection is to keep the rows not associated with the left table.

Retrieve the order information of all customers, including customers who have no order information yet.

SELECT Customers.cust_id, Customer.cust_name, Orders.order_id
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

customers table:

cust_id	cust_name
1	a
2	b
3	c

orders table:

order_id	cust_id
1	1
2	1
3	3
4	3

result:

cust_id	cust_name	order_id
1	a	1
1	a	2
3	c	3
3	c	4
2	b	Null

16, Combined query

Use UNION to combine two queries. If the first query returns M rows and the second query returns N rows, the result of the combined query is generally M+N rows.

Each query must contain the same columns, expressions, and aggregation functions.

The same row will be removed by default. If you need to keep the same row, use UNION ALL.

Can contain only one ORDER BY clause and must be at the end of the statement.

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

17, View

A view is a virtual table. It does not contain data, so it cannot be indexed.

The operation of view is the same as that of ordinary table.

Views have the following benefits:

• Simplify complex SQL operations, such as complex connections;
• Use only part of the data of the actual table;
• Ensure the security of data by giving users access to the view only;
• Change the data format and presentation.

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

18, Stored procedure

Stored procedures can be regarded as batch processing of a series of SQL operations.

Benefits of using stored procedures:

• Code encapsulation ensures certain security;
• Code reuse;
• Because it is pre compiled, it has high performance.

Creating stored procedures on the command line requires custom delimiters, because the command line is based on; It is a terminator, and the stored procedure also contains a semicolon. Therefore, this semicolon will be mistakenly regarded as a terminator, resulting in syntax errors.

It contains three parameters: in, out and inout.

You need to use the select into statement to assign values to variables.

Only one variable can be assigned a value at a time. Collection operation is not supported.

delimiter //

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //

delimiter ;

call myprocedure(@ret);
select @ret;

19, Cursor

Using cursors in stored procedures can move and traverse a result set.

Cursors are mainly used in interactive applications, where users need to browse and modify any row in the dataset.

Four steps for using cursors:

1. Declare a cursor, which does not actually retrieve data;
2. Open cursor;
3. Take out data;
4. Close the cursor;

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 from mytable;
        # A continue handler is defined. When the condition sqlstate '02000' appears, set done = 1 will be executed
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end //
 delimiter ;

20, Trigger

Triggers are automatically executed when a table executes the following statements: DELETE, INSERT, UPDATE.

The trigger must specify whether to execute automatically BEFORE or AFTER the statement is executed. BEFORE execution uses the BEFORE keyword and AFTER execution uses the AFTER keyword. BEFORE is used for data verification and purification, AFTER is used for audit tracking, and changes are recorded in another table.

The INSERT trigger contains a virtual table named NEW.

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- Get results

The DELETE trigger contains a virtual table named OLD and is read-only.

The UPDATE trigger contains a virtual table named NEW and OLD, where NEW can be modified and OLD is read-only.

MySQL does not allow CALL statements in triggers, that is, stored procedures cannot be called.

21, Transaction management

Basic terms:

• transaction refers to a set of SQL statements;
• rollback refers to the process of revoking a specified SQL statement;
• commit refers to writing the results of SQL statements that are not stored into the database table;
• A savepoint is a temporary placeholder set in a transaction that you can post a fallback to (different from fallback the entire transaction).

You cannot fallback the SELECT statement, and it is meaningless to fallback the SELECT statement; You cannot fallback CREATE and DROP statements.

MySQL's transaction COMMIT is implicit by default. Each statement is executed as a transaction and then committed. When the START TRANSACTION statement appears, the implicit submission will be closed; After the COMMIT or ROLLBACK statement is executed, the transaction will be automatically closed and the implicit COMMIT will be resumed.

Set autocommit to 0 to cancel automatic submission; The autocommit tag is for each connection, not for the server.

If no reservation point is set, ROLLBACK will fall back to the START TRANSACTION statement; If a reservation point is set and specified in ROLLBACK, it will fall back to the reservation point.

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

22, Character set

Basic terms:

• Character set is a set of letters and symbols;
• Encoding is the internal representation of a character set member;
• Proofing characters specify how to compare, mainly for sorting and grouping.

In addition to specifying the character set and correction for the table, you can also specify the following for the column:

CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

You can specify proofreading when sorting and grouping:

SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;

23, Authority management

The account information of mysql is saved in the mysql database.

USE mysql;
SELECT user FROM user;

Create account

The newly created account does not have any permissions.

CREATE USER myuser IDENTIFIED BY 'mypassword';

Modify account name

RENAME USER myuser TO newuser;

Delete account

DROP USER myuser;

View permissions

SHOW GRANTS FOR myuser;

Grant permission

For account username@host Username @% uses the default host name.

GRANT SELECT, INSERT ON mydatabase.* TO myuser;

Delete permissions

GRANT and REVOKE control access at several levels:

• The whole server uses GRANT ALL and REVOKE ALL;
• The entire database, using ON database. *;
• Specific tables, using on database table；
• Specific columns;
• Specific stored procedures.

REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

Change password

The Password() function must be used for encryption.

SET PASSWROD FOR myuser = Password('new_password');

Thank you for your patience. If you have any suggestions, please send a private letter or comment