Some common advanced SQL statements in MySQL

use kgc;
create table location (Region char(20),store_name char(20));
insert into location values ​​('East','Boston');
insert into location values ​​('East','New York');
insert into location values ​​('west','Los Angeles');
insert into location values ​​('west','Houston'); 

create table Store_Info (Store_Name char(20),sales int(10),Date char(10));
insert into Store_Info values ​​('Los Angeles','1500','2020-12-05');
insert into Store_Info values ​​('Houston','250','2020-12-07');
insert into Store_Info values ​​('Los Angeles','300','2020-12-08');
insert into Store_Info values ​​('Boston','700','2020-12-08'); 

---- SELECT ---- Display all data of one or more fields in a table. Syntax: SELECT "field" FROM "table name";
SELECT Store_Name FROM Store_Info; 

---- DISTINCT ---- Do not display duplicate data Syntax: SELECT DISTINCT "field" FROM "table name";
SELECT DISTINCT Store_Name FROM Store_Info; 

---- WHERE ---- Conditional query syntax: SELECT "field" FROM "table name" WHERE "condition";
SELECT Store_Name FROM Store_Info WHERE Sales > 1000; 

---- AND OR ---- AND or syntax: SELECT "field" FROM "table name" WHERE "condition 1" {[ANDIOR] "condition 2"}+;
SELECT Store_Name FROM Store_Info WHERE Sales > 1000 OR (Sales < 500 AND Sales > 200); 

---- IN ---- Display data of known values ​​Syntax: SELECT "field" FROM "table name" WHERE "field" IN ('value1','value2', ...);
SELECT * FROM Store_Info WHERE Store_Name IN ('Los Angeles','Houston'); 

---- BETWEEN ---- Display data within two value ranges. Syntax: SELECT "field" FROM "table name" WHERE "field" BETWEEN 'value 1' AND 'value 2';
SELECT * FROM Store_Info WHERE Date BETWEEN '2020-12-06' AND '2020-12-10'; 

---- Wildcards ---- Wildcards are usually used with LIKE %: The percent sign represents zero, one or more characters _: The underscore represents a single character 'A_Z': All strings starting with 'A', another character of any value, and ending with 'Z'. For example, 'A.BZ' and 'A.22' both match this pattern, but 'AKK2' does not (because there are two characters between A and Z, not just one).
'ABC%': All strings starting with 'ABC'. For example, 'ABCD' and 'ABCABC' both match this pattern.
'%XYZ': All strings ending with 'XYZ'. For example, 'WXYZ' and 'ZZXYZ' both match this pattern.
'%AN%': All strings containing the pattern 'AN'. For example, 'LOS ANGELES' and 'SAN FRANCISCO' both fit this pattern.
'_AN%': All strings whose second letter is 'A' and the third letter is 'N'. For example, 'SAMN FRANCITSCO' fits this pattern, but 'LOS ANGELES' does not.
---- LIKE ---- Match a pattern to find the data we want. Syntax: SELECT "field" FROM "table name" WHERE "field" LIKE {pattern};
SELECT * FROM Store_Info WHERE Store_Name like '%os%';
---- 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 sorting method.
#DESC is sorting in descending order.
SELECT Store_Name,Sales,Date FROM Store_Info ORDER BY Sales DESC; 

abs(x) #Returns the absolute value of x rand() #Returns a random number between 0 and 1 mod(x,y) #Returns the remainder after dividing x by y power(x,y) #Returns x raised to the power of y round(x) #Returns the integer closest to x round(x,y) #Rounded to y decimal places in x sqrt(x) #Returns the square root of x truncate(x,y) #Returns the value of the number x truncated to y decimal places ceil(×) #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 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); 

Aggregate functions:
avg() #Returns the average value of the specified column count() #Returns the number of non-NULL values ​​in the specified column 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 ​​in the specified column SELECT avg(Sales) FROM Store_Info;

SELECT count(store_Name) FROM Store_Info;
SELECT count(DISTINCT store_Name) FROM Store_Info;

SELECT max(Sales) FROM Store_Info;
SELECT min(sales) FROM Store_Info;

SELECT sum(sales) FROM Store_Info;

SELECT count(DISTINCT store_Name) FROM Store_Info;
SELECT count(*) FROM Store_Info;
#count(*) includes the number of rows in all columns. When counting the results, NULL column values ​​will not be ignored.
#count(column name) only includes the number of rows in the column name. When counting the results, rows with NULL column values ​​will be ignored. 

String functions:
trim() #Returns the value without the specified format concat(x,y) #Concatenates the provided parameters x and y into a string substr(x,y) #Gets the string starting from the yth position in string x, which has the same effect as the substring() function substr(x,y,z) #Gets a string of length z starting from the yth position in string x length(x) #Returns the length of string x replace(x,y,z) #Replaces string y in string x with string z
upper(x) #Change all letters of string x to uppercase lower(x) #Change all letters of string x to lowercase left(x,y) #Return the first y characters of string x right(x,y) #Return the last y characters of string x repeat(x,y) #Repeat string x y times space(x) #Return x spaces strcmp (x,y) #Compare x and y, the returned value can be -1, 0, 1
reverse(x) #Reverse string x SELECT concat(Region,Store_Name) FROM location WHERE Store_Name = 'Boston';

#If sql_mode is turned on and PIPES_AS_CONCAT is turned on, "||" is regarded as a string concatenation operator instead of an OR operator, which is similar to the string concatenation function concat. This is the same as the method used in Oracle database. SELECT Region || ' ' || Store_Name FROM location WHERE Store_Name = 'Boston';

SELECT substr(Store_Name,3) FROM location WHERE Store_Name = 'Los Angeles';
SELECT substr(Store_Name,2,4) FROM location WHERE Store_Name = 'New York';

SELECT TRIM ([ [position] [string to remove] FROM ] string);
#[Position]: The value can be LEADING (starting), TRAILING (trailing), BOTH (starting and trailing).
#[String to be removed]: The string to be removed from the beginning, end, or both of the string. The default is space SELECT TRIM(LEADING 'Ne' FROM 'New York');

SELECT Region,length(Store_Name) FROM location;

SELECT REPLACE(Region,'ast','astern')FROM location; 

---- GROUP BY ---- Summarizes and groups the query results of the columns following GROUP BY, usually in combination with aggregate functions. There is a principle for GROUP BY, that is, among all the columns following SELECT, the columns that do not use aggregate functions must appear after GROUP BY.

Syntax: SELECT "column 1", SUM ("column 2") FROM "table name" GROUP BY "column 1";
SELECT Store_Name,SUM(Sales) FROM Store_Info GROUP BY Store_Name ORDER BY sales desc;

---- HAVING ---- is used to filter the record set returned by the GROUP BY statement and is usually used in conjunction with the GROUP BY statement. The existence of the HAVING statement makes up for the deficiency that the WHERE keyword cannot be used in conjunction with aggregate functions. If only function columns are SELECTed, then there is no need for a GROUP BY clause.

Syntax: SELECT "column 1", SUM ("column 2") FROM "table name" GROUP BY "column 1" HAVING (function condition);
SELECT Store_Name,SUM(Sales) FROM Store_Info GROUP BY Store_Name HAVING SUM (Sales) > 1500;

---- Alias---- Field alias Table alias Syntax: SELECT "Table alias"."Field1" [AS] "Field alias" FROM "Table name" [AS] "Table alias"
SELECT A.Store_Name Store，SUM(A.Sales) "Total Sales" FROM Store_Info A GROUP BY A.Store_Name;

---- Subquery ---- Connect tables and insert another SQL statement in the WHERE clause or HAVING clause. Syntax: SELECT "column1" FROM "table1" WHERE "column2" [comparison operator] #Outer query (SELECT "column1" FROM "table2" WHERE "condition"); #Inner query can be a symbolic operator, such as =, >, <, >=, <=; it can also be a literal operator, such as LIKE, IN, BETWEEN
SELECT SUM(Sales) FROM Store_Info WHERE Store_Name IN
(SELECT Store_Name FROM location WHERE Region = 'west');

SELECT SUM(A.Sales) FROM Store_Info A WHERE A.Store_Name IN
(SELECT Store_Name FROM location B WHERE B.Store_Name = A.Store_Name); 

---- EXISTS ---- is used to test whether the inner query produces any results, similar to whether a Boolean value is true. If so, the system will execute the SQL statement in the outer 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");
SELECT SUM(Sales) FROM Store_Info WHERE EXISTS (SELECT * FROM location WHERE Region = 'West'); 

UPDATE Store_Info SET store_name='washington' WHERE sales=300;

Inner join: returns only the rows with equal joining fields in two tables. Left join: returns all the records in the left table and the records with equal joining fields in the right table. Right join: returns all the records in the right table and the records with equal joining fields in the left table. SELECT * FROM location A INNER JOIN Store_Info B on A.Store_Name = B.store_Name;

SELECT * FROM location A RIGHT JOIN Store_Info B on A.Store_Name = B.Store_Name ;

SELECT * FROM location A,store_Info B WHERE A.Store_Name = B.Store_Name;

SELECT A.Region REGION,SUM(B.Sales) SALES FROM location A,Store_Info B
WHERE A.Store_Name = B.Store_Name GROUP BY REGION; 

---- CREATE VIEW ---- Views can be thought of as virtual tables or stored queries.
The difference between a view and a table is that a table actually stores data, while a view is a structure built on top of a table and does not actually store data itself.
Temporary tables disappear automatically when the user exits or disconnects from the database, but views do not disappear.
A view does not contain data, but only stores its definition. Its purpose is generally to simplify complex queries. For example, if you want to perform a join query on several tables and also perform operations such as statistics and sorting, it would be very troublesome to write SQT statements. Using a view to join several tables and then querying the view is the same as querying a table, which is very convenient.

Syntax: CREATE VIEW "view table name" AS "SELECT statement";
CREATE VIEW V_REGION_SALES AS SELECT A.Region REGION, SUM(B.Sales) SALES FROM location A
INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name GROUP BY REGION;

SELECT * FROM V_REGION_SALES;
DROP VIEW V_REGION_SALES; 

---- UNION ---- Union, merge the results of two SQL statements, the fields generated by the two SQL statements need to be of the same data type UNION: the data values ​​generated will not be repeated, and will be sorted in the order of the fields Syntax: [SELECT statement 1] UNION [SELECT statement 2];

UNION ALL: List all the data values ​​that generate the results, regardless of whether there are duplicates or not. Syntax: [SELECT statement 1] UNION ALL [SELECT statement 2];

SELECT Store_Name FROM location UNION SELECT Store_Name FROM Store_Info;

SELECT Store_Name FROM location UNION ALL SELECT Store_Name FROM Store_Info; 

----- Intersection value---- Take the intersection of the results of two SQL statements SELECT A.Store_Name FROM location A INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name;
SELECT A.Store_Name FROM location A INNER JOIN Store_Info B USING(Store_Name);

#When one of the two base tables does not have a specified row, and the other table has a duplicate row, and there is indeed an intersection, use SELECT A.Store_Name FROM
(SELECT Store_Name FROM location UNION ALL SELECT Store_Name FROM store_Info) A
GROUP BY A.Store_Name HAVING COUNT(*) > 1;

#Get the intersection of the results of two SQL statements without duplication SELECT A.Store_Name FRONM (SELECT B.Store_Name FROM location B INNER JOIN Store_Info C ON B.Store_Name = C.store_Name) A
GROUP BY A.Store_Name;

SELECT DISTINCT A.Store_Name FROM location A INNER JOIN Store_Info B USING(Store_Name);

SELECT DISTIMCT Store_Name FROM location WHERE (Store_Name) IN (SELECT Store_Name FROM Store_Info);

SELECT DISTINCT A.Store_Name FROM location A LEFT JOIN Store_Info B USING(Store_Name) WHERE B.Store_Name IS NOT NULL; 

---- No intersection values ​​---- Display the results of the first SQL statement, and there is no intersection with the results of the second SQL statement, and there is no duplication SELECT DISTINCT Store_Name FROM location WHERE (Store_Name) NOT IN (SELECT Store_Name FROM Store_Info);

SELECT DISTINCT A.Store_Name FROM location A LEFT JOIN Store_Info B USING(Store_Name) WHERE B.Store_Name IS NULL; 

---- CASE ---- is a keyword syntax used by SQL for IF-THEN-ELSE logic:
SELECT CASE ("field name")
  WHEN "Condition 1" THEN "Result 1"
  WHEN "Condition 2" THEN "Result 2"
  ...
  [ELSE "result N"]
  END
FROM "table name";

#"Condition" can be a value or a formula. The ELSE clause is not required.

SELECT store_Name, CASE Store_Name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'Boston' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",Date
FROM Store_Info;

#"New Sales" is the field name used for that column in CASE.

CREATE TABLE Total_Sales (Name char(10),sales int(5));
INSERT INTO Total_Sales VALUES ('zhangsan',10);
INSERT INTO Total_Sales VALUES ('lisi',15);
INSERT INTO Total_Sales VALUES ('wangwu',20);
INSERT INTO Total_Sales VALUES ('zhaoliu',40);
INSERT INTO Total_Sales VALUES ('sunqi',50);
INSERT INTO Total_Sales VALUES ('zhouba',20);
INSERT INTO Total_Sales VALUES ('wujiu',30); 

----Calculate ranking----Self-join the table, then list the results in order, and calculate the number of rows before each row (including the row itself) SELECT A1.Name, A1.sales, COUNT(A2.sales) Rank FROM Total_sales A1,Total_sales A2
WHERE A1.sales < A2.sales 0R (A1.sales = A2.sales AND A1.Name = A2.Name)
GROUP BY A1.Name, A1.sales ORDER BY A1.sales DESC;

For example:
select A1.Name,A1.sales,count(A2.sales) rank from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales OR (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc;

explain:
When the sales field value of A1 is less than the sales field value of A2, or the sales field values ​​of the two tables are equal and the Name field values ​​are equal,
Query the Name field value of A1, the sales field value of A1, and the non-null value of the sales field of A2 from tables A1 and A2. rank is an alias, and groups the Name field of A1. Sort the sales field of A1 in descending order. 

---- Calculate the median ----
SELECT Sales Middle FROM (SELECT A1.Name,A1.Sales,COUNT(A2.Sales) Rank FROM Total_Sales A1,Total_Sales A2
WHERE A1.Sales < A2.Sales 0R (A1.Sales = A2.Sales AND A1.Name >= A2.Name)
GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC) A3
WHERE A3.Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);

For example:
select * from (select A1.Name,A1.sales,count(A2.sales) rank from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales OR (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from Total_Sales);

select sales mid from (select A1.Name,A1.sales,count(A2.sales) rank from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales OR (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc) A3 where A3.rank = (select (count(*)+1) DIV 2 from Total_Sales);


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

---- Calculate cumulative totals ---- Self-join the table, then list the results in order, calculating the total before each row (including the row itself) SELECT A1.Name, A1.Sales, SUM(A2.Sales) Sum_Total FROM Total_Sales A1, Total_Sales A2
WHERE A1.Sales < A2.Sales OR (A1.Sales=A2.Sales AND A1.Name = A2.Name)
GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales DESC;

For example:
select A1.*,sum(A2.sales) sum_soales from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales or(A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc; 

For example:
select A1.*,A1.sales/(select sum(sales) from Total_Sales) z_sum from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales or (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name;

#select sum(sales) from Total_Sales is to calculate the total of the field values, and then divide each row by the total to calculate the percentage of the total for each row. 

For example:
select A1.Name,A1.sales,sum(A2.sales),sum(A2.sales)/(select sum(sales) from Total_Sales) Z from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales or (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc;

select A1.Name,A1.sales,sum(A2.sales),TRUNCATE(sum(A2.sales)/(select sum(sales) from Total_Sales),2) ||'%' Z from Total_Sales A1,Total_Sales A2 where A1.sales < A2.sales or (A1.sales=A2.sales and A1.Name=A2.Name) group by A1.Name order by A1.sales desc; 

For example:
create table SITE(site varchar(20));
insert into SITE values('nanjing');
insert into SITE values('beijing');
insert into SITE values('');
insert into SITE values('taijin');
insert into SITE values();
insert into SITE values('');
select * from SITE; 

select length(site) from SITE;
select * from SITE where site is NULL;
select * from SITE where site is not NULL;
select * from SITE where site = '';
select * from SITE where site <> ''; 

Matching pattern Description Example ^ Matches the beginning character of the text '^bd' Matches a string that starts with bd $ Matches the end character of the text 'qn$' Matches a string that ends with qn. Matches any single character 'st' Matches any string with one character between s and t * Matches zero or more of the preceding character 'fo*t' Matches t preceded by any number of o
+ matches the preceding character 1 or more times 'hom+' matches a string starting with ho followed by at least one m string matches a string containing the specified string 'clo' matches a string containing clo p1|p2 matches p1 or p2 'bg|fg' matches bg or fg
[...] matches any character in the character set '[abc]' matches a or b or c
[^...] matches any character not in the brackets '[^ab]' matches a string that does not contain a or b {n} matches the previous string n times 'g{2}' matches a string containing 2 g's {n,m} matches the previous string at least n times and at most m times 'f{1,3}' matches f at least 1 time and at most 3 times

For example:
select * from Total_Sales where Name regexp '^[n]';
select * from Total_Sales where Name regexp '[n]';
select * from Total_Sales where Name regexp 'Ho|Bo'; 

grammar:
DELIMITER !! #Change the end symbol of the statement from semicolon; temporarily to prevent problems, you can customize itCREATE PROCEDURE XXX() #Create a stored procedure, customize the procedure name, () can have parameters BEGIN #The procedure body starts with the keyword BEGIN select * from xxx; #Procedure body statement END!! #The procedure body ends with the keyword ENDDELIMITER ; #Restore the end symbol of the statement to a semicoloncall XXX; #Call a stored procedure====View the stored procedure====
show create procedure [database.]Stored procedure name; #View the detailed information of a stored procedure show create procedure XXX;
show procedure status [like '%XXX%'] \G

For example:
DELIMITER !!
CREATE PROCEDURE KIND1()
BEGIN
	select * from Total_Sales;
END!!

DELIMITER ;
CALL KIND1;

show create procedure KIND1\G
show procedure status like '%KIND1%'\G 

For example:
DELIMITER !!
CREATE PROCEDURE KIND2(IN people char(20))
BEGIN
	select * from Total_Sales where Name=people;                                                    
END!!         

DELIMITER ;
CALL KIND2('lisi'); 

For example:
DELIMITER !!
CREATE PROCEDURE KIND7(IN num int(10))
BEGIN
declare var int;
set var=num*2;
if var>=10 then
	update Total_Sales set sales=sales+1;
else 
	update Total_Sales set sales=sales-1;
end if;
END!!
DELIMITER ;

CALL KIND7(5);
CALL KIND7(4); 

For example:
create table akg(id int);

DELIMITER !!
CREATE PROCEDURE KIND6()
BEGIN
declare var int;
set var=0;
while var<5 do
	insert into akg values(var);
	set var=var+1;
end while;
END!!

DELIMITER ;
CALL KIND6;
select * from akg;