Introduction concept

• DB: database
• DBMS: Database Management System
• SQL: Structured Query Language
• database
• data sheet
• field

After mysql is successfully installed, common commands:

• mysql [-h host ip -p port] - u user name - p enter enter password to log in
• shwo databases; Display all databases after successful login
• use database name; Open database
• show tables; Show all data sheets
• . . .

DQL language

Basic query

The format is:

SELECT Query list FROM Data table name;

Query list:

• constant
• field
• function
• expression

Examples of basic query statements are as follows:

-- Query constant
select 12;

-- Query field
select username,age from tab_user;

-- Query function
select now();

-- Query expression
select 100*2;

Condition query

The where condition is added to the basic query. The format is:

select Query field from Data table name where query criteria;

Query criteria can be divided into:

• Conditional expression query < > > = < = < > or=
• Logical expression query and or not
• Like, between and, in, is null, is not null

Conditional expressions and logical expressions are too simple to write cases. The following cases mainly illustrate the fuzzy query
The first is like

-- wildcard %Represents any number of characters  _Represents any field
-- For example, I want to query the information that the second word of the user table name is still
select * from tab_user where username like '_still%';

-- If there are special symbols in the query criteria that need to be escaped, there are the following two ways. For example, I want to query the information whose second character is underlined
select Query list from Data table name where Field name like '_\_%'; -- use \ Escape symbols
select Query list from Data table name where Field name like '_$_%' escape $; -- You don't have to \ Symbol customize an escape symbol

Then between and

-- between and The interval value of is[n,m]  It contains critical values

-- I want to query the information of employees whose salary is between 100 and 200
SELECT * FROM empl WHERE sal>=100 AND sal<=200;
-- The above is a query using conditional expressions. You can also use fuzzy queries
SELECT * FROM empl WHERE sal BETWEEN 100 AND 200;

Next is in

-- It should be noted that in() Values of the same type are required in parentheses, or automatic type conversion is supported, such as '123'---> 123

-- Query employee information with department No. 10 20
SELECT * FROM empl WHERE Department number field name IN(10,20);

The last two are the judgment of null.

-- We can't write that, = Judgment is not supported null
SELECT * FROM Table name WHERE Field name = NULL;

-- The correct wording is:
SELECT * FROM Table name WHERE Field name IS NULL;

One additional point - Safety equals
It is written as < = >. It can be used to judge null values or ordinary types of values

-- For example, a field above me is null Information about
SELECT * FROM Table name WHERE Field name IS NULL;
-- It can also be done by using safety null Judgment of
SELECT * FROM Table name WHERE Field name <=>NULL;

-- Query common types of values, such as employee information with a salary of 12000
SELECT * FROM empl WHERE sal <=> 12000;

Sort query

The basic syntax is as follows:

select Query field from Data table name where Screening conditions order by Sort list [ASC or DESC];

The sorting list can be fields, aliases, expressions, and functions

-- Sort list as expression
SELECT 
	Query list 
FROM 
	Data table name 
[WHERE 
 	query criteria] 
ORDER BY 
	Salary field*12 [ASC|DESC]
	
-- The sort list is the alias specified in the query list
SELECT 
	emp_name, sal*12 AS 'Annual salary' 
FROM 
	Data table name 
[WHERE 
 	query criteria] 
ORDER BY 
	Annual salary [ASC|DESC]

-- Sort list as function
SELECT 
	Query list
FROM
	Data table name
[WHERE
	query criteria]
ORDER BY
	length(Field name) [ASC|DESC]

The sorting list can also be multiple fields. First, sort according to sorting condition 1. If this condition is the same, then sort according to sorting condition 2

SELECT 
	Query list
FROM
	Data table name
[WHERE
	query criteria]
ORDER BY
	Sort field name 1 [ASC|DESC], Sort field name 1 [ASC|DESC]

Common functions

There are generally two mysql functions:

• Single line function: pass a value to the formal parameter of the method, and finally get a result
• Grouping function: pass some values to the formal parameters of the method, and finally get a result

Character function

• Length (string) gets the length of the character
• Concat (string, string, string...) concatenation of multiple strings
• Upper (string) to uppercase
• Lower (string) to lowercase
• substr() / substring() string interception

-- mysql The index subscript starts from 1. For example, I want to intercept it cc Two characters
select substr('aabbcc',5);

-- substr()The first parameter of the function is the string to be intercepted, the second is the start index parameter, and the third is the number of bits to be intercepted. For example, I still intercept cc Two characters
select substr('aabbccdd',5,2);

• Instr (string, substring) returns the position where the substring first appears in the string. If it does not appear, it returns 0
• Trim (string) to remove leading and trailing spaces

-- If I want to remove a specific character in the first place,You need to specify what characters to remove in the method parameters
SELECT trim('a' FROM 'aaaaaa result aaaa');

• Lpad (string, length, character) implements left padding with the specified characters

SELECT LPAD('aaa',10,'*');
-- The result is *******aaa    Parameter 1 is the original string, parameter 2 is the filled character of the filled total length parameter 3

-- If the specified length is less than the original string, it will be intercepted
SELECT LPAD('aaabbbccc',5,'*');  -- The result is aaabb

• Rpad (string, length, character) is filled with specified characters
• Replace (string 1, string 2, string 2) replaces all string 2 in string 1 with string 3

SELECT replace('aaabbbccc','a','b');  -- The result is bbbbbbccc

Mathematical function

• Round (numeric value)

-- If the value is a negative number, you can first round the absolute value and then add a minus sign
SELECT round(-1.56);  -- The result is -2

-- The function also has an overload`round(Number of digits)；`   Indicates how many decimal places are reserved 
SELECT round(1.567,2);  -- The result is 1.57

• Ceil (value) rounded up
• The floor is rounded down
• Truncate (number of digits) truncate. How many digits are reserved after the decimal point

SELECT truncate(1.567,2);  -- The result is 1.56

• Mod (numeric, numeric) remainder

SELECT mod(10,3); -- The result is 1, which is 10 to 3.

• rand() the interval for obtaining random number results is [0,1)

Date function

• now() returns the current date and time
• Current date() returns the current date
• curtime() returns the current time

You can also get the specified part of the year, month, day, hour, minute and second

-- These methods need to pass a date object, date format string and date field in the data table
SELECT year(now()); -- Acquisition year
SELECT year('2021-8-16');
SELECT year(Field name) FROM data sheet;

• str_to_date() converts a string in date format to a date in the specified format

str_to_date('8-16-2021','%m-%d-%Y'); -- It means to give you a string. You should parse the string in the format of parameter 2
-- After parsing, the result is a date type: 2021-8-16

The date symbol is as follows:

Formatter	function
%Y	Four digit year
%y	Two digit year
%M	Month (01,02,03...)
%m	Month (1,2,3...)
%d	Day (01,02,03...)
%H	24-hour system
%h	12 hour system
%i	Minutes (01,02,03...)
%s	Seconds (01,02,03...)

• date_format() converts a date to a character

date_fromat('2018/6/6',"%Y year%m month%d day")  -- The result is June 6, 2018

Grouping function

Grouping functions are also called aggregation functions. Common grouping functions are as follows:

• sum()
• Max (max)
• Min (min)
• avg() average
• count() count

matters needing attention:

1. sum() and avg() are generally applicable to the processing of numeric data, while the maximum value, minimum value and number are applicable to any type such as numeric, character, date, etc
2. The above functions will automatically ignore the null value during operation, so do not use the count() function to calculate a possible null field when judging the total row number of the table
3. The grouping function can be used with the distinct keyword to realize the operation of de duplication

-- distinct Keywords can be added or not added. Other grouping functions can also prefix the field name with this keyword
SELECT sum([distinct] Field name) FROM Table name

1. Specific use of count() function

-- More commonly, a field is used in the parameters of a function
SELECT count(Field name) FROM Table name

-- The second way is to use*  Indicates that only one field in a row of data is not null Add 1 to the quantity
SELECT count(*) FROM Table name

-- The third method uses a constant and count(*) It is to add a column to the table, the values are this constant, and then calculate the number of rows 
SELECT count(1) FROM Table name

1. In the select query list, the fields queried together with the grouping function must be the fields grouped by group.

Other functions

• version() gets the current database version
• database() get the current database
• user() gets the current user
• password('character ') returns the password form of the changed string
  +md5('character ') another encryption method

Grouping query

The basic syntax of group by and having is added on the basis of the previous one, as follows:

select 
	Grouping function,Field name of the group
from
	Table name
where
	query criteria
group by
	Grouping field
having
	Filter criteria after grouping
order by
	Sort list

The grouping field can also be a function, and there can be multiple grouping fields

-- For example, group employees according to their name length, query the number of employees in each group, and filter the number of employees>5 What are the
SELECT
	length(Employee name), count(*)
FROM
	Employee table
GROUP BY 
	length(Employee name)
HAVING
	count(*)>5


-- For example, query the average type of work of employees in each department and type of work
SELECT
	avg(wages),Department number field, type of work field
FROM
	Employee table
GROUP BY 
	Department number field, type of work field;

You can also sort the results after grouping query

join query

When querying n tables, it should be noted that the query criteria must be at least n-1, so as to avoid the occurrence of Cartesian product

Connection query can be divided into sql92 and sql99 according to age
Connection query can be divided into internal connection, external connection and cross connection. Internal connection can be divided into equivalent connection, non equivalent connection and self connection; External connection is divided into left external connection, right external connection and full connection

sql92

The first is the equivalent connection, which is also the most original writing method. Multiple tables to be queried are separated by commas, and then the connection conditions are added in the where clause

SELECT
	Fields to query in multiple tables
FROM
	Table name 1,Table name 2...
WHERE
	Table 1.A field = Table 2.A field and ...

You can also alias a table in the form clause, but it should be noted that if you alias a table, you need to use the alias. Field name after the where clause. You can't use the original table name anymore, and an error will be reported

The non equivalent connection is the condition in the where clause, not the equal sign used
Self connection is to connect and query by yourself. The most common case is an employee table, where a field is your superior id, and then query accordingly

sql99

The basic syntax is as follows:

select
	Query list
from
	Data sheet 1 AS Alias 1
[line type] join 
	Data sheet 2 AS Alias 2
on
	Connection conditions
where
	Screening conditions
group by
	Grouping field
having
	Query criteria after grouping
order by
	Sort list;

Connection query classification:

• Inner join: [inner] join
• External connection: left external connection, right external connection and full external connection
• cross connect: cross
  External connection is to query the intersection of two tables and display the data of the main table at the same time
  Total outer connection is equivalent to Cartesian product
  Cross connection is: inner connection + left outer connection + right outer connection

Common theoretical usage of join

1. First kind
   

select Query list 
from A 
left join B 
on A.key=B.key;

1. Second
   

select Query list 
from A 
right join B 
on A.key=B.key;

1. Third
   

select Query list 
from A 
join B 
on A.key=B.key;

1. Fourth
   

select Query list 
from A 
left join B on A.key=B.key 
where B.key is null;

5. The fifth

select Query list 
from A 
right join B 
on A.key=B.key
where A.key is null;

6. Sixth

select Query list
from A
full outer join B
on A.key = B.key

The global external connection MySql does not support, but you can use the first and second cases above to use the union connection query, which will be de duplicated anyway

7. The seventh

select Query list
from A
full outer join B
on A.key = B.key
where A.key is null or B.key is null;

Subquery

The sub query actually takes the query result of the first sql as the condition of the second query
Where sub queries can appear:

• In the select sentence: only the results of sub queries are supported as one row and one column
• In the from sentence: the results of supporting sub queries are one row and one column, one row and multiple columns, multiple rows and one column, and multiple rows and multiple columns
• In where sentence or having sentence: the results of supporting sub query are one row and one column, one row and one column, and one row and multiple columns (less used)
• In the exists sentence: multiple rows and columns are supported

The most common sub query appears in the where clause. You can use <, < =, >, > =, =, for example

SELECT 
	*
FROM
	Data table name
WHERE
	Field name > (Results of sub query row by column)

If the subquery is multiple rows and one column, it needs to be used together with the multi row operator

Operator	explain
in	A field is equal to one in the result set of the sub query
any	A field is larger than one in the result set of the sub query
all	A field is larger than all of the result sets of the sub query

Generally, in is used more than the other two multiline operators, because the maximum or minimum function can be used to achieve the same effect in sub query

SELECT 
	*
FROM
	Data table name
WHERE
	Field name in (Results of multiple rows and one column of subquery)

The following points should be noted about sub query:

• Subqueries need to be enclosed in parentheses
• The number of rows and columns that can be allowed for sub queries in each location
• The subquery appears in the form clause. Alias the table

Paging query

The basic syntax is as follows:

select 
	Query list
from
	Data table name
join
	Data sheet 2
on
	Connection conditions
where
	query criteria
group by
	Group list
having
	Query criteria after grouping
order by
	Sort list
limit To display the starting index of the data, the number of entries per page is displayed

For paged queries, the most common formula is start index = (current page - 1) * number of entries displayed per page
Several cases are as follows:

-- Query page 1~5 Data bar
SELECT
	Query field
FORM
	Data table name
LIMIT 0,5;

-- If only the first few pieces of data are queried, the first parameter can be omitted
SELECT
	Query field
FORM
	Data table name
LIMIT 5;

-- If you want to query 11~25 Data bar
SELECT
	Query field
FORM
	Data table name
LIMIT 10,15;

The execution flow is as follows:

Joint query

The function of joint query is to combine the results of multiple sql queries into one result
The basic syntax is:

Query statement 1
union
 Query statement 2
......
Query statement n;

Joint query is actually a split, which splits multiple query criteria into multiple query statements, for example:

-- The query department number is greater than 90 or the mailbox contains a Employee information for
-- Common writing
SELECT * FROM Employee table WHERE Department number field>90 OR Mailbox field LIKE '%a%';

-- Joint query writing method
SELECT * FROM Employee table WHERE Department number field>90
UNION
SELECT * FROM Employee table WHERE Mailbox field LIKE '%a%';

There are only two query conditions here, so you can't see much advantages. If there are many and complex query conditions, the advantages of joint query will appear
The application scenario of joint query is that the results to be queried come from multiple tables, and there is no direct connection relationship between multiple tables, but the query information is consistent
Precautions for joint query:

• If multiple query statements are used for joint query, the number of fields to be queried must be the same, otherwise an error will be reported
• Multiple query statements are used for joint query, and the field type and order of query should be consistent
• Union Union query will be de duplicated by default. If you don't want to de duplicate, you can use union all for query