A basis,

Schemas define information about how data is stored, what data is stored, and how the data is decomposed. Both databases and tables have schemas.

Primary key values are not allowed to be modified or reused (deleted primary key values cannot be assigned to the primary key of a new row).

Structured Query Language (SQL), standard SQL is managed by the ANSI Standards Committee and is called ANSI SQL. Each DBMS has its own implementation, such as PL/SQL, Transact-SQL, etc.

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

SQL supports the following three types of annotations:

# # commentsSELECT *
FROM mytable; Comments -
/* Note 1 note 2 */
Copy the code

Database creation and use:

CREATE DATABASE test;
USE test;
Copy the code

Create table

CREATE TABLE mytable (
  # intType, not empty, automatically increment idINT NOT NULL AUTO_INCREMENT,
  # intThe value cannot be null. The default value is1, not null col1INT NOT NULL DEFAULT 1, # is a string of variable length45Can be empty col2VARCHAR(45) NULL, # date type, can be null col3DATE NULLSet primary key to idPRIMARY KEY (`id`));
Copy the code

3. Modify the table

Add columns

ALTER TABLE mytable
ADD col CHAR(20);
Copy the code

Delete the column

ALTER TABLE mytable
DROP COLUMN col;
Copy the code

Delete table

DROP TABLE mytable;
Copy the code

Four, insert,

Ordinary insert

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

Insert the retrieved data

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

Inserts the contents of a table into a new table

CREATE TABLE newtable AS
SELECT * FROM mytable;
Copy the code

Five, update,

UPDATE mytable
SET col = val
WHERE id = 1;
Copy the code

Six, delete,

DELETE FROM mytable
WHERE id = 1;
Copy the code

TRUNCATE TABLE can clear the TABLE, that is, delete all rows.

TRUNCATE TABLE mytable;
Copy the code

Use the WHERE clause when using update and delete operations, otherwise the entire table will be destroyed. You can test with a SELECT statement first to prevent false deletions.

Seven, query

DISTINCT

The same value only occurs once. It works on all columns, which means that all columns are equal if they have the same value.

SELECT DISTINCT col1, col2
FROM mytable;
Copy the code

LIMIT

Limit the number of rows returned. You can take two arguments. The first argument is the start row, starting at 0; The second argument is the total number of rows returned.

Return to the first 5 lines:

SELECT *
FROM mytable
LIMIT 5;
Copy the code
SELECT *
FROM mytable
LIMIT 0.5;
Copy the code

Return lines 3 to 5:

SELECT *
FROM mytable
LIMIT 2.3;
Copy the code

Eight, sorting,

  • ASC: Ascending (default)
  • DESC: descending

You can sort by more than one column and specify a different sort for each column:

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

Nine, filtering,

Unfiltered data is very large, resulting in excess data being transmitted over the network, wasting network bandwidth. Therefore, try to use SQL statements to filter unnecessary data, rather than transferring all data to the client and then filtering by the client.

SELECT *
FROM mytable
WHERE col IS NULL;
Copy the code

The following table shows the operators available for the WHERE clause

The operator instructions
= Is equal to the
< Less than
> Is greater than
< >! = Is not equal to
< =! > Less than or equal to
> =! < Greater than or equal to
BETWEEN Between the two values
IS NULL NULL values

Note that NULL is different from either a 0 or an empty string.

AND AND OR are used to join multiple filter conditions. Priority is given to AND. When a filter expression involves more than one AND AND OR, () can be used to determine the priority, making the priority relationship clearer.

The IN operator is used to match a set of values, which can also be followed by a SELECT clause to match a set of values from a subquery.

The NOT operator is used to negate a condition.

10. Wildcards

Wildcards are also used in filter statements, but only for text fields.

  • % matches >=0 arbitrary characters;

  • _ Matches ==1 arbitrary character;

  • [] can match characters in the set. For example, [ab] will match characters a or B. It can be negated with the off character ^, that is, characters in the set that do not match.

Use Like for wildcard matching.

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- Any text that does not begin with A and B
Copy the code

Don’t abuse wildcards, they match very slowly at the beginning.

11. Calculation fields

Converting and formatting data on a database server is often much faster than on a client, and less data can be converted and formatted to reduce network traffic.

A computed field usually needs to be aliased using AS, otherwise the field is printed AS a computed expression.

SELECT col1 * col2 AS alias
FROM mytable;
Copy the code

CONCAT() is used to join two fields. Many databases use Spaces to fill a value to a column width, so join results in unnecessary whitespace. TRIM() removes the leading and trailing whitespace.

SELECT CONCAT(TRIM(col1), '('.TRIM(col2), ') ') AS concat_col
FROM mytable;
Copy the code

Xii. Functions

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

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 of a column

AVG() ignores NULL lines.

Use DISTINCT to aggregate different values.

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

Text processing

function instructions
LEFT() Character on the left
RIGHT() Character on the right
LOWER() Convert to lowercase characters
UPPER() Converts to uppercase characters
LTRIM() Remove the Spaces on the left
RTRIM() Remove the Spaces on the right
LENGTH() The length of the
SOUNDEX() Convert to voice value

Among other things, SOUNDEX() converts a string into an alphanumeric pattern describing its phonetic 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 manipulation function
DATE_FORMAT() Returns a formatted date or time string
DAY() Returns the number of days of a date
DAYOFWEEK() For a date, return the 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();
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

13. Grouping

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

The same group of data can be processed using a summary function, such as finding the average of the group of data.

The specified group field can be grouped and sorted automatically by the field.

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

GROUP BY is automatically sorted BY GROUP fields, and ORDER BY can also be sorted BY summary fields.

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

WHERE to filter rows and HAVING to filter groups. Row filtering should precede group filtering.

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

Grouping requirements:

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

Subquery

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

The result of a subquery can be used as a filter for a WHRER statement:

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

The following statement retrieves the customer’s order quantity, and the subquery executes 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;
Copy the code

Xv. Connection

Joins are used to JOIN multiple tables, use the JOIN keyword, and condition statements use ON instead of WHERE.

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

Column names, computed fields, and table names can be aliased with AS to simplify SQL statements and join the same tables.

In the 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;
Copy the code

Instead of explicitly using an INNER JOIN, you can use a plain query and JOIN the columns to be joined in the two tables using an equivalent method in WHERE.

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

Since the connection

A self-join can be considered a type of inner join, except that the joined table is itself.

A list of employees, including the names of employees and their departments, to find 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");
Copy the code

Self-connecting version

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

A natural connection

Natural join is to join the same names through the equivalence test, there can be more than one.

The difference between an inner join and a natural join: an inner join provides joined columns, while a natural join automatically joins all the same columns.

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

Outer join

The outer join preserves those rows that are not associated. It is divided into left outer join, right outer join and full outer join. The left outer join is to keep the rows that are not associated with the left table.

Retrieves order information for all customers, including those who do not already have order information.

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

Customers table:

cust_id cust_name
1 a
2 b
3 c

The orders table:

order_id cust_id
1 1
2 1
3 3
4 3

Results:

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

16. Combination 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 default is to remove the same line. If you want to preserve the same line, use UNION ALL.

Only one ORDER BY clause can be contained and must be at the end of the statement.

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;
Copy the code

Xvii. View

A view is a virtual table that contains no data and therefore cannot be indexed.

The operation on the view is the same as the operation on the normal table.

Views have the following benefits:

  • Simplify complex SQL operations, such as complex joins;
  • Use only part of the data from the actual table;
  • Data security is ensured by only giving users the permission to access the view.
  • 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;
Copy the code

Stored Procedures

Stored procedures can be thought of 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 a stored procedure on the command line requires a custom delimiter because the command line starts with; Is the end character, and the stored procedure also contains a semicolon, so this part of the semicolon will be mistaken as the end character, resulting in syntax errors.

Contains in, out, and inout parameters.

Assigning values to variables requires the SELECT into statement.

Only one variable can be assigned at a time. Collection operations are 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 ;
Copy the code
call myprocedure(@ret);
select @ret;
Copy the code

19. Cursors

Cursors can be used in stored procedures to move through a result set.

Cursors are primarily used in interactive applications where users need to browse and modify arbitrary rows in a dataset.

Four steps to using a cursor:

  1. Declare a cursor, which does not actually retrieve the data;
  2. Open the cursor;
  3. Data retrieval;
  4. Close the cursor;
delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 frommytable; # define a continue handler whensqlstate '02000'When this condition occurs, it is executedset done = 1
        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 ;
Copy the code

20. Trigger

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

Triggers must specify whether to execute automatically BEFORE or AFTER the statement, using the BEFORE keyword for the previous execution and the AFTER keyword for the subsequent execution. BEFORE is used for data validation and cleansing, and AFTER is used for audit trails to log changes to a separate 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
Copy the code

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

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

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

21. Business management

Basic terms:

  • A transaction is a group of SQL statements;
  • Rollback refers to the process of revoking specified SQL statements.
  • Commit refers to writing the result of an unstored SQL statement to a database table.
  • Savepoints are temporary placeholders set up in a transaction to which you can publish rollback (as opposed to the entire transaction).

The SELECT statement cannot be rolled back, and there is no point in rolling back the SELECT statement. CREATE and DROP statements cannot be rolled back either.

MySQL commits transactions implicitly by default. Each statement executed is treated as a transaction and committed. When a START TRANSACTION statement occurs, implicit commit is turned off; When a COMMIT or ROLLBACK statement is executed, the transaction is automatically closed and implicit COMMIT resumes.

Setting autoCommit to 0 disables automatic commit. The AutoCOMMIT tag is per-connection, not per-server.

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

START TRANSACTION
//.SAVEPOINT delete1
//.ROLLBACK TO delete1
//.COMMIT
Copy the code

22. Character Sets

Basic terms:

  • A character set is a collection of letters and symbols;
  • Encoded as an internal representation of a character set member;
  • The collate character specifies how to compare and is used primarily for sorting and grouping.

In addition to specifying character set and calibration for tables, you can also specify columns:

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

Collation can be specified when sorting or grouping:

SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;
Copy the code

23. Permission Management

MySQL account information is stored in the MySQL database.

USE mysql;
SELECT user FROM user;
Copy the code

Create account

The newly created account does not have any permissions.

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

Changing the Account Name

RENAME USER myuser TO newuser;
Copy the code

Delete the account

DROP USER myuser;
Copy the code

Check the permissions

SHOW GRANTS FOR myuser;
Copy the code

Grant permissions

The account is defined as username@host and username@% uses the default hostname.

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

Remove permissions

GRANT and REVOKE control access at several levels:

  • GRANT ALL and REVOKE ALL;
  • For the entire database, use ON database.
  • For a specific table, use ON database.table;
  • Specific columns;
  • Specific stored procedures.
REVOKE SELECT.INSERT ON mydatabase.* FROM myuser;
Copy the code

Change password

Encryption must be done using the Password() function.

SET PASSWROD FOR myuser = Password('new_password');
Copy the code

The resources

  • Benforta.sql Must Know must know [M]. Posts and Telecommunications Press, 2013.

Resource portal

  • Pay attention to [be a gentle program ape] public account
  • In [do a tender program ape] public account background reply [Python information] [2020 autumn recruit] can get the corresponding surprise oh!
  • Build their own blog address: nightmare back to life blog

“❤️ thank you.”

  • Click “like” to support it, so that more people can see this content.
  • Share your thoughts with me in the comments section, and record your thought process in the comments section