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:
- 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
- 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
- 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
- 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
- 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
- First kind
select Query list from A left join B on A.key=B.key;
- Second
select Query list from A right join B on A.key=B.key;
- Third
select Query list from A join B on A.key=B.key;
- 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