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
- 1, Foundation
- 2, Create table
- 3, Modify table
- 4, Insert
- 5, Renew
- 6, Delete
- 7, Inquiry
- 8, Sort
- 9, Filter
- 10, Wildcard
- 11, Calculation field
- 12, Function
- 13, Grouping
- 14, Subquery
- 15, Connect
- 16, Combined query
- 17, View
- 18, Stored procedure
- 19, Cursor
- 20, Trigger
- 21, Transaction management
- 22, Character set
- 23, Authority management
- reference material
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
ALTER TABLE mytable ADD col CHAR(20);
ALTER TABLE mytable DROP COLUMN col;
DROP TABLE mytable;
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;
UPDATE mytable SET col = val WHERE id = 1;
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.
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 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;
- 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;
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
|=||be equal to|
|<> !=||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.
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;
The functions of each DBMS are different, so they are not portable. The following are mainly MySQL functions.
|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;
|LOWER()||Convert to lowercase characters|
|UPPER()||Convert to uppercase characters|
|LTRIM()||Remove the space on the left|
|RTRIM()||Remove the space on the right|
|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
|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();
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;
- 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.
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;
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 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 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.
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 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;
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;
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;
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;
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:
- Declare a cursor, which does not actually retrieve data;
- Open cursor;
- Take out data;
- 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 ;
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
- 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
- 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;
The newly created account does not have any permissions.
CREATE USER myuser IDENTIFIED BY 'mypassword';
Modify account name
RENAME USER myuser TO newuser;
DROP USER myuser;
SHOW GRANTS FOR myuser;
For account username@host Username @% uses the default host name.
GRANT SELECT, INSERT ON mydatabase.* TO myuser;
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;
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