MySQL advanced statement (the ultimate)

Posted by Isityou on Sun, 23 Jan 2022 20:16:55 +0100

Preparation before learning: first create two tables for sentence experiment

mysql -uroot -p
show databases;

create database plane;
use plane;
create table destination (
region char(20),
place_name char(20));

insert into destination values('southwest','chengdu');
insert into destination values('north China','beijing');
insert into destination values('southwest','kunming');
insert into destination values('north China','tianjin');
select * from destination;

create table info (
place_name char(20),
sales int(10),
date char(10));

insert into info values('chengdu','350','2021-02-10');
insert into info values('beijing','294','2021-02-10');
insert into info values('kunming','330','2021-02-10');
insert into info values('beijing','392','2021-02-16');
select * from info;

1, MySQL advanced statement

1. SELECT ------ displays all data of one or more fields in the table

Syntax: SELECT "Field" FROM "Table name";
Example:
select place_name from destination;

2. DISTINCT ------ do not display duplicate data

Syntax: SELECT DISTINCT "Field" FROM "Table name";
Example:
select distinct place_name from info;

3. WHERE ------ conditional query

Syntax: SELECT "Field" FROM "Table name" WHERE "condition";
example:
select place_name from info where sales > 300;

4. AND|OR ------ AND|OR

Syntax: SELECT "Field" FROM "Table name" WHERE "Condition 1" {[AND|OR] "Condition 2"}+ ;
Example:
select place_name from info where sales > 350 or sales < 300;
select place_name from info where sales > 300 and sales < 350;

5. IN ------ display data of known values

Syntax: SELECT "Field" FROM "Table name" WHERE "Field" IN ('Value 1', 'Value 2', ...);
Example:
select * from info where place_name in ('beijing');
select * from info where place_name in ('beijing','kunming');

6. BETWEEN ------ displays the data within the range of two values

Syntax: SELECT "Field" FROM "Table name" WHERE "Field" BETWEEN 'Value 1' AND 'Value 2';
Example:
select * from info where sales between '300' and '350';

7. Wildcard

Wildcards are usually used with LIKE

% : The percent sign indicates zero, one or more characters
_ : The underscore indicates a single character
 Example:
'A_Z': All with 'A' Start with another character of any value, and start with 'Z' A string that ends with. For example,'ABZ' and 'A2Z' Are consistent with this model, and 'AKKZ' Does not meet (Because in A and Z There are two characters between, not one). 
'ABC%': All with 'ABC' Starting string. For example,'ABCD' and 'ABCABC' All conform to this model.
'%XYZ': All with 'XYZ' End string. For example,'WXYZ' and 'ZZXYZ' All conform to this model.
'%AN%': All contain 'AN'The string of this pattern. For example,'LOS ANGELES' and 'SAN FRANCISCO' All conform to this model.
'_AN%': All second letters are 'A' And the third letter is 'N' String of. For example,'SAN FRANCISCO' In line with this model, and 'LOS ANGELES' Does not conform to this pattern.

8. LIKE ------ match a pattern to find the data we want

Syntax: SELECT "Field" FROM "Table name" WHERE "Field" LIKE {pattern};
Example:
select * from info where place_name like '_ei%';

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, which is the default sorting method.
#DESC is sorted in descending order.
Example:
select place_name,sales,date from info order by sales;
select place_name,sales,date from info order by sales asc;
select place_name,sales,date from info order by sales desc;

10. Functions

(1) Mathematical function

abs(x)	          return x Absolute value of
rand()	          Returns a random number from 0 to 1
mod(x,y)	      return x divide y Remainder after
power(x,y)        return x of y Power
round(x)	      Return from x Nearest integer
round(x,y)        retain x of y Value rounded to decimal places
sqrt(x)	          return x Square root of
truncate(x,y)	  Return number x Truncate to y Decimal value
ceil(x)	          Returns greater than or equal to x Minimum integer of
floor(x)	      Returns less than or equal to x Maximum integer of
greatest(x1,x2...)  Returns the largest value in the collection
least(x1,x2...)	  Returns the smallest value in the collection

SELECT abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
SELECT round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);

(2) Aggregate function

avg()	Returns the average value of the specified column
count()	Returns the value of the non column in the specified column NULL Number of values
min()	Returns the minimum value of the specified column
max()	Returns the maximum value of the specified column
sum(x)	Returns the sum of all values for the specified column

#count(*) includes the number of rows of all columns. The value of NULL will not be ignored in the statistical results
#Count (column name) only includes the number of rows in the column name. Rows with NULL column value will be ignored in the statistical results

Example:
select avg(sales) from info;

select count(place_name) from info;
select count(distinct place_name) from info;

select max(sales) from info;
select min(sales) from info;

select sum(sales) from info;

(3) String function

trim()	Returns a value in the specified format
concat(x,y)	Parameters to be provided x and y Splice into a string
substr(x,y)	Get from string x Section in y A string starting at a position, followed by substring()The function has the same function
substr(x,y,z)	Get from string x Section in y The starting length of the first position is z String of
length(x)	Return string x Length of
replace(x,y,z)	String z Substitution string x String in y
upper(x)	String x All letters of the become capital letters
lower(x)	String x All letters of the become lowercase
left(x,y)	Return string x Before y Characters
right(x,y)	Return string x After y Characters
repeat(x,y)	String x repeat y second
space(x)	return x Spaces
strcmp(x,y)	compare x and y，The returned value can be-1,0,1
reverse(x)	String x reversal

Supplement: such as sql_mode enables PIPES_AS_CONCAT, "|" is regarded as a concatenation operator of a string rather than an or operator. It is similar to concat, a concatenation function of a string, which is the same as the Oracle database
trim() returns a value in the specified format

select TRIM ([ [position] [String to remove] from ] character string);
#[position]: the value of can be LEADING, TRAILING, BOTH. 
#[string to be removed]: a string removed from the beginning, end, or beginning and end of a string. The default is space.
Example:
select trim(leading '123' from '1234567');

concat(x,y) splices the supplied parameters X and Y into a string

select concat(region, place_name) from destination;

select region || ' ' || place_name from destination;
select region || ' ' || place_name from destination where place_name = 'beijing';

substr(x,y) gets the string starting from the Y position in string x, which is the same as the substring() function
substr(x,y,z) gets a string of length Z starting from the y-th position in string X

select substr(place_name,3) from destination where place_name = 'beijing';
select substr(place_name,2,4) from destination where place_name = 'beijing';

length(x) returns the length of the string X

select region,length(place_name) from destination;

replace(x,y,z) replaces string y in string x with string Z

select replace(region,'China',' ') from destination;

upper(x) turns all letters of string x into uppercase letters
lower(x) turns all letters of string x into lowercase letters

select upper(region) from destination;
select lower(region) from destination;

left(x,y) returns the first y characters of string X
right(x,y) returns the last y characters of string X

select left(region,5) from destination;
select right(region,5) from destination;

repeat(x,y) repeats the string x y times

select repeat(region,2) from destination;
select repeat(123,2);

space(x) returns x spaces
strcmp(x,y) compares X and y, and the returned value can be - 1,0,1
reverse(x) inverts the string X

11. GROUP BY ------ summarize and group the query results of the fields behind GROUP BY

It is usually used in conjunction with aggregate functions
GROUP BY has a principle that in all columns after SELECT, columns that do not use aggregate functions must appear after GROUP BY.

Syntax: SELECT "Field 1", SUM("Field 2") FROM "Table name" GROUP BY "Field 1";
Example:
select place_name,sum(sales) from info group by place_name order by sum(sales);

12. HAVING ------ used to filter the record table returned by the GROUP BY statement

Usually used in conjunction with the GROUP BY statement
The existence of HAVING statement makes up for the deficiency that WHERE keyword can not be used in combination with aggregate function. If only the function column is selected, the GROUP BY clause is not required.

Syntax: SELECT "Field 1", SUM("Field 2") FROM "Table name" GROUP BY "Field 1" HAVING (Function condition);
Example:
select place_name,sum(sales) from info group by place_name having sum(sales) > 400;

13. Alias ------ field alias, table alias

Syntax: SELECT "Table alias"."Field 1" [AS] "Field alias" FROM "Table name" [AS] "Table alias";
Example:
select A.place_name "name",sum(A.sales) "sum_sales" from info A group by name;

14. Subqueries: join tables

Insert another SQL statement into the child WHERE clause or HAVING clause

Syntax:
#External query
SELECT "Field 1" FROM "Table 1" WHERE "Field 2" [Comparison operator]
#Internal query
(SELECT "Field 1" FROM "Table 2" WHERE "condition");
#Operators that can be symbols, for example: =, >, <, > =, < =; It can also be a literal operator, such as LIKE, IN, BETWEEN

Example 1:

select sum(sales) from info where place_name in (select place_name from destination where region = 'southwest');
#The following sentence is the simplified version of the above
select sum(sales) from info where place_name in('chengdu','kunming');

Example 2:

select sum(a.sales) from info a where a.place_name in(select place_name from destination b where b.place_name = a.place_name);

15. EXISTS ------ used to test whether internal queries produce any results

Whether similar Boolean values are true
If so, the system will execute the SQL statements in the external query. If not, the entire SQL statement will not produce any results.

Syntax: SELECT "Field 1" FROM "Table 1" WHERE EXISTS (SELECT * FROM "Table 2" WHERE "condition");
Example:
select sum(sales) from info where exists (select * from destination where region = 'southwest');

2, Connection query

1. Inner join

Returns only rows with equal join fields in two tables

SELECT * FROM Table 1 aliases for table 1 INNER JOIN Table 2 aliases of Table 2 on Table 1 aliases.Field = Table 2 aliases.Field;  #The field names here are the same
 Example:
select * from destination a inner join info b on a.place_name = b.place_name;

2. Left join

Returns records that include all records in the left table and join fields in the right table

SELECT * FROM Table 1 aliases for table 1 LEFT JOIN Table 2 aliases for table 2 on Table 1 aliases.Field = Table 2 aliases.Field;  #The field names here are the same
 Example:
select * from destination a left join info b on a.place_name = b.place_name;

3. Right join

Returns records that include all records in the right table and the same join field in the left table

SELECT * FROM Table 1 aliases for table 1 RIGHT JOIN Table 2 aliases of Table 2 on Table 1 aliases.Field = Table 2 aliases.Field;  #The field names here are the same
 Example:
select * from destination a right join info b on a.place_name = b.place_name;

3, CREATE VIEW

The difference between the view and the table is that the table actually stores data, while the view is a structure based on the table, which itself does not actually store data.
The temporary table disappears automatically after the user exits or disconnects from the database, while the view does not disappear.
The view does not contain data, but only stores its definition. Its purpose is generally to simplify complex queries. For example, you need to join and query several tables, and also perform operations such as statistical sorting. It will be very troublesome to write SQL statements. Join several tables with a view, and then query this view, which is as convenient as querying a table.

Syntax: CREATE VIEW "View table name" AS "SELECT sentence";
Example:
create view new_info as select a.region region,a.place_name place_name,b.sales sales from destination a inner join info b on a.place_name = b.place_name;

select * from new_info;
drop view new_info;

4, MySQL associative set, intersection value, no intersection value, case

1. Associative set

Combine the results of the two SQL statements. The fields generated by the two SQL statements need to be of the same data type

(1)UNION

UNION : The data values of the generated results will not be repeated and will be sorted in the order of fields
 Syntax:[SELECT Statement 1] UNION [SELECT Statement 2];
example:
select place_name from destination union select place_name from info;

(2)UNION ALL

UNION ALL : The data values of the generated results are listed, whether there are duplicates or not
 Syntax:[SELECT Statement 1] UNION ALL [SELECT Statement 2];
Example:
select place_name from destination union all select place_name from info;

2. Intersection value

Take the intersection of the results of two SQL statements

select a.place_name from destination a inner join info b on a.place_name = b.place_name;

select a.place_name from destination a inner join info b using(place_name);

There are no duplicate rows in the two tables, and they are used when they do intersect

select a.place_name from (select place_name from destination union all select place_name from info) a group by a.place_name having count(*) > 1;

Take the intersection of the results of two SQL statements without repetition

select a.place_name from (select b.place_name from destination b inner join info c on b.place_name =c.place_name) a group by a.place_name;

select distinct a.place_name from destination a inner join info b using(place_name);

select distinct place_name from destination where (place_name) in (select place_name from info);

select distinct a.place_name from destination a left join info b using(place_name) where b.place_name is not null;

3. No intersection value

The results of the first SQL statement are displayed, and the results that do not intersect with the second SQL statement are not repeated

select distinct place_name from destination where (place_name) not in (select place_name from info);

select distinct a.place_name from destination a left join info b using(place_name) where b.place_name is null;

4,CASE

CASE is a keyword used by SQL as logic such as IF-THEN-ELSE

Syntax:
SELECT CASE ("Field name")
  WHEN "Condition 1" THEN "Result 1"
  WHEN "Condition 2" THEN "Result 2"
  ...
  [ELSE "result N"]
  END
FROM "Table name";

#The condition can be a numeric value or a formula. ELSE clause is not required.

For example:
select place_name, case place_name
when 'beijing' then sales - 50
when 'kunming' then sales - 20
else sales
end
"new sales",date
from info;
#"New Sales" is the field name for the CASE field.

5, Sort

Prepare the table for the experiment before sorting

create database school;
use school;
create table class(name varchar(20),scores int(5));
insert into class values ('aa1',80);
insert into class values ('bb2',100);
insert into class values ('cc3',78);
insert into class values ('dd4',94);
insert into class values ('ee5',66);
insert into class values ('ff6',53);
insert into class values ('gg7',77);
select * from class;

1. Calculate ranking

Self join the table, and then list the results in order to calculate the number of rows before each row (including the row itself)

select a1.name, a1.scores,count(a2.scores) rank from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name = a2.name) group by a1.name,a1.scores order by a1.scores desc;
#The value of the score field is smaller than its own value, and the number of the score field and the name field are the same. For example, ff6 is 6 + 1 = 7

2. Calculate the median

select scores middle from (select a1.name, a1.scores,count(a2.scores) rank from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name <= a2.name) group by a1.name,a1.scores order by a1.scores desc) a3 where a3.rank = (select (count(*)+1) div 2 from class);

#Each derived table must have its own alias, so alias A3 must have
#DIV is a way to calculate quotient in MySQL

3. Calculate cumulative total

Table self join, and then list the results in order to calculate the sum before each row (including the row itself)

select A1.*,sum(A2.scores) sum_socores from class A1,class A2 where A1.scores < A2.scores or(A1.scores = A2.scores and A1.name = A2.name) group by A1.name order by A1.scores desc;

4. Total percentage

select A1.*,A1.scores/(select sum(scores) from class) per_sum from class A1,class A2 where A1.scores < A2.scores or (A1.scores = A2.scores and A1.name=A2.name) group by A1.name order by A1.scores desc;

#SELECT SUM(Sales) FROM Total_ The sub query of sales is used to calculate the total
#After the total is calculated, we can divide each row by the total to find the total percentage of each row

5. Cumulative total percentage

Calculate the cumulative total percentage of each row by dividing the cumulative total SUM(a2.Sales) by the total

select A1.name,A1.scores,sum(A2.scores),sum(A2.scores)/(select sum(scores) from class) per_sum from class A1,class A2 where A1.scores < A2.scores or (A1.scores = A2.scores and A1.name=A2.name) group by A1.name order by A1.scores desc;

Take a few digits after the decimal point

select A1.name,A1.scores,sum(A2.scores),truncate(sum(A2.scores)/(select sum(scores) from class),2) ||'%' per_sum from class A1,class A2 where A1.scores < A2.scores or (A1.scores = A2.scores and A1.name=A2.name) group by A1.name order by A1.scores desc;

If you think the SQL statement is a little long, it is suggested that you can use the view method

6, The difference between null and no value

The length without value is 0 and does not occupy space; The length of NULL value is NULL, which takes up space.
IS NULL or IS NOT NULL is used to judge whether the field IS NULL or not. It is not possible to find out whether there is no value.
The judgment of no value is handled with = '' or < > '. < > Represents not equal to.
When you specify the number of rows in the field statistics through count(), if you encounter a NULL value, it will be automatically ignored, and if you encounter no value, it will be added to the record for calculation.

SELECT length(NULL), length(''), length('1');

test

#Build table
create table test(test varchar(20));
insert into test values(' ');
insert into test values('123123');
insert into test values();
insert into test values('1111');
insert into test values('');
select * from test;

Example:
select * from test where test is null;
select * from test where test is not null;
select * from test where test = '';
select * from test where test <> '';
select count(test) from test;

7, Regular expression of MySQL

Matching pattern			describe									    example
^ 				Matches the start character of the text 						'^bd' Match to bd String beginning with
$ 				Matches the end character of the text 						'qn$' Match to qn a null-terminated string 
. 				Match any single character							's.t' Match any s and t String with one character between
* 				Matches zero or more characters that precede it 				'fo*t' matching t Any one in front o
+ 				Matches the preceding character 1 or more times					'hom+' Match to ho At least one after the beginning m String of
 character string 			Match contains the specified string 						'clo' Match contains clo String of
p1|p2 			matching p1 or p2 							'bg|fg' matching bg perhaps fg
[...] 			Matches any character in the character set 				'[abc]' matching a perhaps b perhaps c
[^...] 			Matches any character that is not in parentheses 					'[^ab]' Match does not contain a perhaps b String of
{n} 			Match previous string n second 					    'g{2}' Matching contains 2 g String of
{n,m}			Match the previous string at least n Times, at most m second		    'f{1,3}' matching f Minimum 1 time, maximum 3 times

Syntax format:

Syntax: SELECT "Field" FROM "Table name" WHERE "Field" REGEXP {pattern};
Example: I use the above experimental library here
use plane
select * from info where place_name regexp '^b';
select * from info where place_name regexp 'ei|un';

8, Stored procedure

1. Concept of stored procedure

A stored procedure is a set of SQL statements to complete specific functions.
In the process of using stored procedures, common or complex work is written in SQL statements in advance and stored with a specified name. This process is compiled and optimized and stored in the database server. When you need to use the stored procedure, you just need to call it. Stored procedures are faster and more efficient than traditional SQL.

2. Advantages of stored procedures

After one execution, the generated binary code will reside in the buffer to improve the execution efficiency
SQL statements plus a collection of control statements are highly flexible
In the server-side storage, when the client calls, reduce the network load
It can be called repeatedly and can be modified at any time without affecting the client call
It can complete all database operations and control the information access permission of the database

3. Create stored procedure

Format:
DELIMITER $$							#Change the closing symbol of the statement from semicolon; Temporarily changed to two $$(can be customized)
CREATE PROCEDURE xxx()					#Create a stored procedure named xxx without parameters
 BEGIN                                  #The process body starts with the keyword BEGIN
   sql sentence;                             #Process style sentence
 END $$								    #The procedure body ends with the keyword END
DELIMITER ;                             #Returns the closing symbol of the statement to a semicolon

Example:
delimiter < s p a n c l a s s = " t o k e n k e y w o r d " > c r e a t e < / s p a n > < s p a n c l a s s = " t o k e n k e y w o r d " > p r o c e d u r e < / s p a n > c h a i n f o < s p a n c l a s s = " t o k e n p u n c t u a t i o n " > ( < / s p a n > < s p a n c l a s s = " t o k e n p u n c t u a t i o n " > ) < / s p a n > < s p a n c l a s s = " t o k e n k e y w o r d " > b e g i n < / s p a n > < s p a n c l a s s = " t o k e n k e y w o r d " > s e l e c t < / s p a n > < s p a n c l a s s = " t o k e n o p e r a t o r " > ∗ < / s p a n > < s p a n c l a s s = " t o k e n k e y w o r d " > f r o m < / s p a n > i n f o < s p a n c l a s s = " t o k e n p u n c t u a t i o n " > ; < / s p a n > < s p a n c l a s s = " t o k e n k e y w o r d " > e n d < / s p a n > create procedure cha_info() begin select * from info; end <spanclass="tokenkeyword">create<spanclass="tokenkeyword">procedurechainfo<spanclass="tokenpunctuation">(<spanclass="tokenpunctuation">)<spanclass="tokenkeyword">begin<spanclass="tokenkeyword">select<spanclass="tokenoperator">∗<spanclass="tokenkeyword">frominfo<spanclass="tokenpunctuation">;<spanclass="tokenkeyword">end
delimiter ;

4. Call stored procedure

CALL Process name;
Example:
call cha_info;

5. Viewing stored procedures

SHOW CREATE PROCEDURE [database.]Stored procedure name;		#View specific information about a stored procedure

Example:
show create procedure cha_info;
show procedure status like '%cha_info'\G

6. Parameters of stored procedure

IN input parameter: indicates that the caller passes IN a value to the procedure (the passed IN value can be literal or variable)
OUT output parameter: indicates that the procedure sends OUT a value to the caller (multiple values can be returned) (the outgoing value can only be a variable)
INOUT input / output parameter: it indicates that the caller passes in a value to the procedure and the procedure passes out a value to the caller (the value can only be a variable)

For example:

delimiter !!
create procedure test1(in inname char(20))
  begin
  select * from info where place_name = inname;
  end !!
delimiter ;

call test1('chengdu');

7. Delete stored procedure

The modification method of stored procedure content is to delete the original stored procedure, and then create a new stored procedure with the same name.

DROP PROCEDURE Process name;    #Delete only if it exists. If the specified procedure does not exist, an error will be generated
DROP PROCEDURE IF EXISTS Process name;   #Therefore, you can use if exists to determine whether it exists and delete it. If it does not exist, it will not be deleted.
Example:
drop procedure if exists test1;

8. Control statement of stored procedure

create table t (id int);
insert into t values(10);

(1) Conditional statement if then else end

delimiter $$
create procedure test2(in shu int)
 begin
 declare var int;
 set var=shu*2;
 if var>=10 then
 update t set id=id+1;
 else
 update t set id=id-1;
 end if;
 end $$

delimiter ;

call test2(6);

(2) Loop statement while ···· end while

delimiter $$
create procedure test3()
 begin
 declare var int;
 set var=0;
 while var<6 do
 insert into t values(var);
 set var=var+1;
 end while;
 end $$