1. SQL Advanced Statements

1, SELECT

Displays all data for one or more columns in a table

Syntax: SELECT "field" FROM "table name";
Example:

select * from infol;

select name from infol;

2,DISTINCT

Do not show duplicate content

Syntax: SELECT DISTINCT "field" FROM "table name";
Example:

select distinct sex from infol;

3,WHERE

Conditional Query

Syntax: SELECT "field" FROM "table name" WHERE "condition";
Example:

mysql> select * from infol where id=2;

4 ,AND OR

Meaning that stands for and, or, and

Syntax: SELECT "Column" FROM "Table Name" WHERE "Conditional 1" {[AND|OR] "Conditional 2"};
Example:

select * from infol where sex='female' and age > 22;

Example:

select * from infol where sex='male' or age > 22;

Example:

select * from infol where (sex='male' and age >22) or age > 23;

5,IN

Content showing known values

Syntax: SELECT "field" FROM "table name" WHERE "field" IN ('value 1','value 2',...);

select * from infol where age in (22,23,24);

6,BETWEEN

Display data in two value ranges

Syntax: SELECT "Column" FROM "Table Name" WHERE "Column" BETWEEN "Value 1'AND"Value 2";
Example:

select * from infol where age between 21 and 23;

7. Wildcards

Usually wildcards are used with LIKE

wildcard	Meaning of representation
%	Percentage signs represent zero, one or more characters (equivalent to'*'in linux)
_	Underlines represent a single character (equivalent to "?" in linux).No.)
–'A_Z'	All strings that start with'A', end with'Z', and have any other value.For example,'ABZ'and'A2Z' both fit this pattern, while'AKKZ'does not (because there are two characters between A and Z, not one).
'ABC%'	All strings starting with'ABC'.For example,'ABCD'and'ABCABC' both fit this pattern
'%XYZ'	All strings ending in'XYZ'.For example,'WXYZ'and'ZZXYZ' both fit this pattern
-'%AN%'	All strings containing the pattern'AN'.For example,'LOS ANGELES'and'SAN FRANCISCO' both fit this pattern
'_AN%'	All strings with a second letter of'A'and a third letter of'N'.For example,'SAN FRANCISCO'fits this pattern, whereas'LOS ANGELES' does not.

8, LIKE

Match a pattern to find out what we want

Syntax: SELECT Column FROM Table Name WHERE Column LIKE {Mode};
Example:

select * from infol where name like '%n';

Data content ending in n

select * from infol where name like '%s%';

Data content containing intermediate n

9 ORDER BY

Sort by keyword

Syntax: SELECT "field" FROM "table name" [WHERE "condition"] ORDER BY "field" [ASC, DESC];

#ASC is sorted in ascending order and is the default.

#DESC is sorted in descending order.
Example:

select * from infol order by age;

Example:

select * from infol order by age desc;

2. SQL Functions

1. Mathematical Functions

Mathematical Functions	Return Results
abs(x)	Returns the absolute value of x
rand()	Returns a random number from 0 to 1
power(x,y)	Returns the y-th power of x
mod(x,y)	Returns the remainder after x divided by y
round(x,y)	Keep x's y-bit decimal rounded value
sqrt(x)	Returns the square root of x
truncate(x,y)	Returns a value where the number x is truncated to a y-bit decimal
ceil(x)	Returns the smallest integer greater than or equal to x
floor(x)	Returns the largest integer less than or equal to x
greatest(x1,x2...)	Returns the largest value in a set
least(x1,x2...)	Returns the smallest value in a set

Example:

select abs(-10),rand(),mod(10,3),power(2,10),round(3.1415);

Example:

select round(3.1415926,3),sqrt(9),truncate(3.1415926,3),ceil(3.5),floor(1.11),greatest(1,2,3,4),least(1,2,3,4);

2. Aggregation function

Mathematical Functions	Return Results
avg()	Returns the average value of the specified column
count()	Returns the number of non-NULL values in a specified column
min()	Returns the minimum value of the specified column
max()	Returns the maximum value of a specified column
sum(x)	Returns the sum of all values of the specified column

select * from infol;

Example:

select avg(age) from infol;

select min(age) from infol;

select max(age) from infol;

select sum(age) from infol;

select count(age) from infol;

count

count(*) denotes the number of rows that include all columns and does not ignore null values

Count (column name) means that only this column is included, and rows with null values are ignored in Statistics

3. String Functions

Mathematical Functions	Return Results
trim()	Returns a value that removes the specified format
concat(x,y)	Split the provided parameters x and y into a string
substr(x,y,z)	Gets a string of length z starting from the YTH position in string x
substr(x,y)	Gets a string starting at the YTH position in the string x, the same as the substring() function
length(x)	Returns the length of the string x
replace(x,y,z)	Replace string z with string y in string x
upper(x)	Turn all letters of string x into uppercase letters
lower(x)	Change all letters of string x to lowercase
left(x,y)	Returns the first y characters of string x
right(x,y)	Returns the last y character of string x
repeat(x,y)	Repeat string x y times
space(x)	Returns x spaces
strcmp(x,y)	Comparing x with y returns a value of -1,0,1
reverse(x)	Reverse string x

Example:
Returns a value that removes the specified format

mysql> select trim(' qaq ');

Stitch into a string

 select concat(name,hobby) from infol where id=1;

substr

select substr(hobby,2) from infol where id=1;   Display from second character

select substr(hobby,2,1) from infol where id=1; Display a character starting with the second character

Returns the length of the string x

select length(name) from infol where id=1;

Replace string z with string y in string x

select replace(name,'zhangsan','zhangwei') from infol where id=1;

Turn all letters of string x into uppercase letters

select upper(name) from infol where id=1;

Change all letters of string x to lowercase

select lower(name) from infol where id=5;

select left(name,3) from infol where id=1;
select right(name,3) from infol where id=1;
select repeat(name,2) from infol where id=1;
select space(2) from infol where id=1;

select strcmp(5,7);
select reverse(56789);

SELECT TRIM ([location] [string to remove] FROM string);

#[Location]: Values can be LEADING (start), TRAILING (end), BOTH (start and end)

#[String to remove]: String removed from the beginning, end, or beginning and end of a string, with spaces by default

select trim(leading 'zh' from 'zhangsan');
select trim(trailing 'an' from 'zhangsan');
select trim(both 'n' from 'zhangsan');

4, GROUP BY

Grouping query results after GROUP BY, usually in conjunction with aggregation functions

GROUP BY has a principle that all columns following select have no columns using aggregate functions and must appear after GROUP BY

Syntax: SELECT "Field 1", SUM("Field 2") FROM "Table Name" GROUP BY "Field 1";

select name,sum(age) from infol group by name;
select name,sum(age) from infol group by name order by age;

select name,age from infol group by name order by age;
select name,age from infol group by name,age order by age;

5 ,HAVING

Used to filter the set of records returned by a GROUP BY statement, usually in conjunction with a GROUP BY statement

The existence of HAVING makes up for the inability of WHERE keywords to be used in conjunction with aggregate functions. If only function columns are select ed, the GROUP BY clause is not required.

Syntax: SELECT "Field 1", SUM("Field 2") FROM "Table Name" GROUP BY "Field 1" HAVING (Functional Conditions);　
Example:

select name,age from infol group by name having sum(age) > 22;

HAVING is only conditional filtering to filter the results of previous GROUP BY

6 Aliases

Column Alias Table Alias

Syntax: SELECT "Table Alias", "Field 1" [AS] "Field Alias" FROM "Table Name" [AS] "Table Alias";
Example:

select name,age,aaa.sex xb from infol aaa group by age;

7. Join Query

1. inner join (contiguous)
Only returns rows with the same join field in two tables

select * from infol a inner join infol1 b on a.age = b.age;

2. left join (left join)
Returns records that include all records in the left table and join fields in the right table

select * from infol a left join infol1 b on a.age = b.age;

3. right join (right join)
Returns records that include all records in the right table and join fields in the left table

select * from infol a right join infol1 b on a.age = b.age;

8. Subqueries

Join tables to insert another SQL statement into a WHERE or HAVING clause
Syntax: SELECT "Field 1" FROM "Table 1" WHERE "Field 2" [Comparison Operator] #Outer Query
(SELECT "Field 1" FROM "Table 2" WHERE "Conditions");
First inquire, then give the results to the outward query for further filtering

select sum(age) from infol where name in (select name from infol where age=22);

9,EXISTS

Used to test whether an internal query produces any results, such as whether a Boolean value is true

If so, the system will execute the SQL statement of the external query, if not, the whole SQL statement will not produce any results

Syntax: SELECT "Column" FROM "Table 1" WHERE EXISTS (SELECT * FROM "Table 2" WHERE "Conditions");

select sum(age) from infol where exists (select name from infol where age=26);