1 SELECT

The SELECT statement consists of several clauses as described in the following list:

• SELECTThis is followed by a comma-separated column or asterisk (*) to return all columns.
• FROMSpecifies the table or view for which data is to be queried.
• JOINGet data from other tables based on some join condition.
• WHEREFilter rows in the result set.
• GROUP BYGroup a set of rows into small groups and apply an aggregate function to each small group.
• HAVINGFilter based onGROUP BYSmall grouping of clause definitions.
• ORDER BYSpecifies the list of columns used for sorting.
• LIMITLimit the number of rows returned.

SELECT
<Field names>
FROM <table1>.<table2>...WHERE <expression>
GROUP BY <group by definition>
HAVING <expression> [{<operator> <expression>}... ] ]ORDER BY <order by definition>
LIMIT[<offset>,] <row count>
Copy the code

1.1 Column selection and designation

1.1.1 Select the specified column

select 列 fromThe name of the tableCopy the code

1.1.2 Define and use column aliases

selectAlias of column A, alias of column B, alias of column BfromThe name of the tableCopy the code

1.1.3 Replacing Data in the Query Result Set

select 

case 
 whereconditions1 thenexpression1
 whereconditions2 thenexpression2

elseexpressionendColumn namefromThe name of the tableCopy the code

1.1.4 Calculate column values

selectAs computingfromThe name of the tableCopy the code

1.1.5 Aggregate function

The name of the function	role
MAX(column)	The lowest value of a column (NULL is not returned if there is one, NULL if there is none)
MIN(column)	The highest value of a column (NULL if none)
COUNT(column)	The number of rows in a column (excluding NULL values)
COUNT(*)	Number of rows of selected columns (including NULL)
SUM(column)	and
AVG(column)	The average

2 FROM

2.1 Clause and multi-table join query

Join query: query by joining multiple tables together (changing the number of rows and columns)

T_student student table: SCID, SNAME, CID

T_clazz class table: CID, Cname

2.1.1 Cross Connection

Cross join: To cross the data of two tables with another table

The principle of

1. Fetch each record in turn from the first table
2. After fetching each record, it is matched against all the records in the other table
3. There are no matching conditions, and all results are retained
4. Number of records = number of records in first table * number of records in second table; Number of fields = number of fields in the first table + Number of fields in the second table (Cartesian product)

Table 1 cross join table 2;

Applying cross joins produces cartesian products with no practical application.

The cross connection results in a Cartesian product

Each record in the left table is joined to each record in the right table

SELECT * FROM t_student CROSS JOIN t_clazz ;
Copy the code

Results: 15 records

2.1.2 in connection

Inner join: fetch all the records from one table and match them in another table: use the matching conditions to match, keep them if successful, abandon them if failed.

Principle 1: Take a record from the first table and match it in another table

2. Use matching conditions to match:

2.1 Match: Reserved and continue to match

2.2 Matching Failure: Continue further. If all tables fail to match, the end

Table 1 [inner] join table 2 on

1. If the inner join is not conditional (allowed), then it is actually cross join (avoided)
2. Matches using matching conditions
3. Because table design tends to produce fields with the same name, especially ids, it is common to use table names to avoid errors with duplicate names. Field name to ensure uniqueness
4. In general, if the corresponding table name is used in the condition, and the table name is usually long, you can simplify this by using the table alias
5. When an inner connection is matched, it is saved only when it is matched
6. Inner join because there is no requirement to use a match condition (ON), you can use a WHERE condition after the data is matched, which has the same effect as on (ON is recommended).

In-application joins are usually used where there are precise requirements for data: data matching must be guaranteed in both types of tables.

Query the common part of the two tables

(The part of the Cartesian product that satisfies the on condition)

SELECT * FROM t_student s INNER JOIN t_clazz c ON s.`cid`=c.`cid`;
Copy the code

Result: 4 records

2.1.3 outer join

Select * from outer join; select * from outer join; select * from outer join; select * from outer join;

There are two types of outer join: left join and right join.

Left join: The left table is the primary table

Join right: the right table is the primary table

The principle of

1. Join primary table: left join is left join primary table; A right join is the right primary table
2. Take each record in the master table and match each record in the slave table
3. If matching conditions are met: Reserved; No satisfaction means no reservation
4. If the primary table record is not matched in any of the secondary tables, the record is also retained: the corresponding field values of the secondary table are not NULL

Syntax Basic syntax:

Left join: primary join secondary join condition;

Join table on join condition;

The main table data corresponding to the left join is on the left; Join the corresponding main table data on the right:

The characteristics of

1. The data record of the primary table in the external join must be saved: after the join, there will not be less records than the primary table.
2. Left joins and right joins can actually be converted to each other, but the position of the data (table order) will change

A very common method of retrieving data: retrieving the corresponding master table and other data as data (association)

2.1.4 The left outer join

Select part + from left table and part + from right table

SELECT * FROM t_student s LEFT JOIN t_clazz c ON s.`cid`=c.`cid`;
Copy the code

Results: 5 records

2.1.5 Right Outer Connection

Query common part of two tables + belong to right table but not belong to left table part

SELECT * FROM t_student s RIGHT JOIN t_clazz c ON s.`cid`=c.`cid`;
Copy the code

Results: 5 records

 

3 where

We know how to use SQL SELECT statements to read data from MySQL tables.

To conditionally SELECT data from a table, add a WHERE clause to the SELECT statement.

grammar

Here is the general syntax for SQL SELECT statements that use the WHERE clause to read data from a data table:

SELECTfield1, field2,... fieldNFROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
Copy the code

3.1 Comparison Operation

symbol	In the form of	role
=	X1=X2	Determine equality
< > and! =	< > the X1 X2 or X1! =X2	Determine if it is unequal
< = >	X1<=>X2	Check whether equal, can check whether equal NULL
>, > =	The X1 > X2, X1 > = X2	Check whether it is greater than or equal to
<, < =	X1, X2, X1 < = X2	Check whether it is less than or equal to

 

SELECT * FROM mytable
WHERE col > 4
Copy the code

3.2 Range of Judgment

symbol	In the form of	role
BETWEEN AND or NOT BETWEEN	X1 BETWEEN mAND n	Determine if it is in range
IN or NOT	The X1 (the value 1 IN…).	And I want to know if it’s within a certain range

 

SELECT * FROM mytable
WHERE col = 2 and col = 3
Copy the code

3.3 Determining null values

symbol	In the form of	role
IS NULL or IS NOT NULL	X1 IS NULLX1 IS NOT NULL	Check whether the value is NULL

 

SELECT * FROM mytable
WHERE col is null
Copy the code

3.4 the subquery

3.4.1 IN Range Query

The subquery used with the keyword IN is mainly used to determine whether a given value exists IN the result set of the subquery. The syntax format is:

SELECT * FROM mytable
WHERE col IN(2.3.4)

SELECT * FROM mytable
WHERE col NOT IN(2.3.4)

<expression> [NOT] IN <The subquery>
Copy the code

The syntax is described as follows.

• < expression >: Specifies an expression. Returns TRUE if the expression is equal to a value in the result set returned by the subquery, FALSE otherwise; If the keyword NOT is used, the opposite value is returned.
• < subquery >: Used to specify subqueries. The subquery here can return only one column of data. For more complex query requirements, SELECT statements can be used to achieve multi-layer nesting of subqueries.

3.4.2 ALL | SOME | ANY comparison operators subqueries

The subquery used by the comparison operator is mainly used to compare the value of an expression with the value returned by the subquery. The syntax format is:

SELECT * FROM mytable 
WHERE col  > = ALL(SELECT col  FROM mytableWHERE  WHERE type = 4)


<expression> {= | < | > | > = | < = | < = > | < > | ! = }
{ ALL | SOME | ANY} <The subquery>
Copy the code

The syntax is described as follows.

• < subquery > : Specifies a subquery.

• < expression > : Used to specify the expression to compare.

• ALL, SOME, and ANY: Optional. Used to specify restrictions on comparison operations. Among them,

  • ALL specifies that the expression needs to be compared with every value in the result set of the subquery, and returns TRUE if the expression is compared with every value, and FALSE otherwise
  • SOME and ANY are synonyms, meaning that the expression returns TRUE as long as it is compared to a value in the result set of the subquery, and FALSE otherwise.

3.4.3 EXIST sub-query

The subquery used by EXIST is mainly used to judge whether the result set of the subquery is empty. Its syntax format is: EXIST < subquery >

Return TRUE if the result set of the subquery is not empty. Otherwise return FALSE.

4 Create, modify, insert, update, and delete tables

4.1 create a 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

4.2 modify the table

Add 2 columns

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

4.2.2 delete columns

ALTER TABLE mytable
DROP COLUMN col;
Copy the code

Holdings delete table

DROP TABLE mytable;
Copy the code

4.3 insert

4.3.1 Common Insert

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

4.3.2 Insert the retrieved data

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

4.3.3 Inserting the contents of a table into a new table

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

The 4.4 update

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

4.5 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

5 Grouped Data

5.1 GROUP BY Field or expression GROUP

Group by: Groups the result set of the select query by a certain field or expression, obtains a set of groups, and retrieves the value of a specified field or expression from each group.

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

5.2 WHVING Line filter grouping

Having: Used to filter rows of the groups queried by WHERE and Group by to find the group results that meet the conditions. It is a filter statement that filters the results of a query after the query returns a result set.

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

5.3 ORDER BY grouping

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

Grouping rules

• 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.

6 Query Restrictions

6.1 the 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

6.2 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;

SELECT *
FROM mytable
LIMIT 0.5;
Copy the code

Return lines 3 to 5:Copy the code

SELECT *
FROM mytable
LIMIT 2.3;
Copy the code

7 a wildcard

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

7.1% 

Matches >=0 arbitrary characters

Find the data that contains an “A”

SELECT * FROM mytable col like '%a%'
Copy the code

Find the data that begins with “A”

SELECT * FROM mytable col like 'a%'
Copy the code

Find find the data ending in “AA”

SELECT * FROM mytable col like '%aa'
Copy the code

7.2 _

Matches ==1 arbitrary character;

Find the one that contains exactly 5 characters

SELECT * FROM mytable col like '_____'
Copy the code

7.3 [] 

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.

Any text that does not begin with A and B

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'
Copy the code

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

8 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.

 8.1 The fields queried by AS are renamed

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.

 8.2 CONCAT()Join two fieldsTRIM()Remove leading and trailing Spaces

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

9 function

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

10 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

SELECT NOW();

20184 -- 14 20:25:11
Copy the code

11 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

12 UNIONCombination 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

13 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

14 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

15 the cursor

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:

• Declare a cursor, which does not actually retrieve the data;
• Open the cursor;
• Data retrieval;
• 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

16 the trigger

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

Triggers must specify whether to execute automatically before or after a statement is executed

• Previous executions use the BEFORE keyword
• Subsequent executions use the AFTER keyword

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.

17 Transaction 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

18 character set

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, mainly 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

19 Rights 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

 

We continue to refine each chapter