prepare

use kgc;
create table Localtion (Region char(20),Store_Name char(20));
insert into Localtion values('East','Boston');
insert into Localtion values('East','New York');
insert into Localtion values('West','Los Angeles');
insert into Localtion 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');

1, Advanced SQL statement

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

Syntax: SELECT "field" FROM "table name";
SELECT Store_Name FROM Store_Info;

2. DISTINCT -- do not display duplicate data

Syntax: SELECT DISTINCT "field" FROM "table name";
SELECT DISTINCT Store_Name FROM Store_Info;

3. WHERE -- conditional query

Syntax: SELECT "field" FROM "table name" WHERE "condition";
SELECT Store_Name FROM Store_Info WHERE Sales > 1000;

4. AND OR -- AND OR

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

5. IN -- data showing known values

Syntax: SELECT "field" FROM "table name" WHERE "field" IN ('value 1 ',' value 2 ',...);
SELECT * FROM Store_Info WHERE Store_Name IN ('Los Angeles', 'Houston');

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';
SELECT * FROM Store_Info WHERE Date BETWEEN '2020-12-06' AND '2020-12-10';

7. Wildcards -- wildcards are usually used with LIKE

%: the percent sign indicates zero, one or more characters
_ : The underscore indicates a single character

'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' All conform to 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 information we want

Syntax: SELECT "field" FROM "table name" WHERE "field" LIKE {mode};
SELECT * FROM Store_Info WHERE Store_Name like '%os%';

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.
SELECT Store_Name,Sales,Date FROM Store_Info ORDER BY Sales DESC;

10. GROUP BY -- summarize and group the query results of the fields behind GROUP BY, which is usually used in combination with aggregation functions

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

11. HAVING -- used to filter the record set returned by the GROUP BY statement. It is usually used in combination with the GROUP BY statement

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

12. EXISTS ---- used to test whether the internal query produces any results, such as whether the Boolean value is true

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

Syntax: SELECT field 1 FROM table 1 WHERE EXISTS (SELECT * FROM table 2 WHERE conditions);

2, Function

Mathematical function:

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 function:

SELECT avg(Sales) FROM Store_Info;

SELECT count(Store_Name) FROM Store_Info WHERE Store_Name IS NOT NULL;
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;

String function:

SELECT concat(Region, Store_Name) FROM Localtion WHERE Store_Name = 'Boston';

#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
SELECT Region || ' ' || Store_Name FROM Localtion WHERE Store_Name = 'Boston';

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

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.

SELECT TRIM(LEADING 'Ne' FROM 'New York');

SELECT Region,length(Store_Name) FROM Localtion;

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

III

1. Alias -- field alias table alias

Syntax: SELECT "table alias" "Field 1" [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;

2. Connection query - inner join, left join, right join

Inner join: returns only rows with equal join fields in two tables
Left join: returns records including all records in the left table and those with equal join fields in the right table
Right join: returns records including all records in the right table and those with equal join fields in the left table

SELECT * FROM Localtion A INNER JOIN Store_Info B on A.Store_Name = B.Store_Name ;
SELECT * FROM Localtion A LEFT JOIN Store_Info B on A.Store_Name = B.Store_Name ;
SELECT * FROM Localtion A RIGHT JOIN Store_Info B on A.Store_Name = B.Store_Name ;

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

3. CREATE VIEW ---- view, which can be regarded as virtual table or storage query

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. Writing sgL statements will be very troublesome. 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 statement";
CREATE VIEW V_REGION_SALES AS SELECT A.Region REGION, SUM(B.Sales)SALES FROM Localtion A INNEER JOIN Store_Info B ON A.Store_Name = B.store_Name GROUP BY REGION;
 
SELECT *FROM V_REGION_SALES;
DROP VIEW V_REGION_SALES;

4. UNION - UNION, which combines the results of two SQL statements. The fields generated by two SQL statements need to be of the same data type

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];
UNION ALL: list the data values of the generated results, whether there is repetition or not. Syntax: [SELECT statement 1] UNION ALL [SELECT statement 2];
SELECT Store_Name FROM Localtion UNION SELECT Store_Name FROM Store_Info;
SELECT Store_Name FROM Localtion UNION ALL SELECT Store_Name FROM Store_Info;

5. Intersection value

Take the intersection of the results of two SQL statements

SELECT A.Store_Name FROM Localtion A INNER JOIN Store_Info B ON A.Store_Name = B.Store_Name;
SELECT A.Store_Name FROM Localtion A INNER JOIN Store_Info B USING(Store_Name);

#The two tables do not use separate duplicate rows, and they do have intersections
SELECT A.Store_Name FROM(SELECT Store_Name FROM Localtion UNION ALL SELECT Store_Name FROM Store_Info) A GROUP BY A.store_Name HAVING COUNT(*) > 1;

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

SELECT A.Store_Name FROM(SELECT B.Store_Name FROM Localtion 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 Localtion A INNER JOIN Store_Info B USING(Store_Name);

SELECT DISTINCT Store_Name FROM Localtion WHERE (Store_Mame) IN (SBLECT Store_Name FROM Store_Info);

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

6. 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 Store_Name FROM Localtion WHERE(Store_Name) NOT IN (SELECT Store_Name FROM Store_Info) ;

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

7. CASE -- is the keyword syntax used by SQL as logic such as IF, THEN and ELSE

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 numeric value or a formula. 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 for the CASE field.

4, Mathematical calculation

prepare

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);

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.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,A1.Sales ORDER BY A1.Sales DESC;

#The value of the sales field is smaller than its own value, and the sales field and Name field are the same. For example, zhangsan is 6 + 1 = 7

2. 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 OR (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));
#Each derived table must have its own alias, so alias A3 must have
#DIV is the way to calculate quotient in MysQL

3. Calculate cumulative total

self join (self Join)，Then list the results in order and calculate the number before each line〈Include the line itself)Sum of
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;

4. Total percentage

SELECT A1.Name,A1.Sales,round(A1.Sales/(SELECT SUM(Sales)FROM Total_Sales)*100,2) || '%' Per_Total _Total FROM Total_Sales A1,Total_Sales A2 WHERE A1.Sales < A2.Sales OR (A1.Sales=A2.Sales AND A1.Namme = A2.Name) GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales 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

V

1. The difference between null and no value

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

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 <> '';

2. Regular expression (same as Shell part)

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: SELECT field FROM table name WHERE field REGEXP match pattern

select * from FARE where site regexp '^[n]';
select * from FARE where site regexp '[n]';
select * from FARE where site regexp 'jin|jing';

3. Stored procedure

=A stored procedure is a set of SQL statements to complete a specific function

In the process of using a stored procedure, 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 the stored procedure needs to be used, you only need to call it. The execution of the stored procedure is faster and more efficient than traditional SQL.
Advantages of stored procedures
1. After one execution, the generated binary code will reside in the buffer to improve the execution efficiency
2. SQL statements plus a collection of control statements are highly flexible
3. In the server-side storage, when the client calls, reduce the network load
4. It can be called repeatedly and can be modified at any time without affecting the client call
5. It can complete all database operations and control the information access permission of the database

DELIMITER !!    			#Change the closing symbol of the statement from semicolon; It can be modified temporarily to prevent problems and can be customized
CREATE PROCEDURE XXX()  	#Create a stored procedure. The procedure name is user-defined and () can take parameters
 	BEGIN   		    	#The process body starts with the keyword BEGIN
 	select * from xxx;  	#Process style sentence
 	END!!    				#The procedure body ends with the keyword END
DELIMITER ; 			    	#Returns the closing symbol of the statement to a semicolon

call XXX;	  				#Call stored procedure

====Viewing stored procedures====
show create procedure [database.]Stored procedure name; 	  #View specific information about a stored procedure
show create procedure XXX;
show procedure status [like '%XXX%'] \G

DELIMITER !!
CREATE PROCEDURE KIND1()
BEGIN
	select * from FARE;
END!!

DELIMITER ;
CALL KIND1;

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

Parameters of stored procedure
IN input parameter, indicating 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 process sends OUT a value to the caller (multiple values can be returned, and the outgoing value can only be a variable)

DELIMITER !!
CREATE PROCEDURE KIND2(IN place char(20))
BEGIN
	select * from FARE where site=place;                                                    
END!!         

DELIMITER ;
CALL KIND2('beijing');

4. Statement of stored procedure

Conditional statement

DELIMITER !!
CREATE PROCEDURE KIND3(IN num int(10))
BEGIN
declare var int;
set var=num*2;
if var>=10 then
	update FARE set money=money+1;
else 
	update FARE set money=money-1;
end if;
END!!
DELIMITER ;

CALL KIND3(5);
CALL KIND3(4);

Loop statement while

create table akg(id int);

DELIMITER !!
CREATE PROCEDURE KIND4()
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 KIND4;
select * from akg;