4, Some data processing methods

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

4.1 ranking

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

4.2. Calculate the median

#Each derived table must have its own alias, so A3 must have
select Name,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);
#div is the way to calculate quotient in mysql

1.Let's create a view of the ranking table first
create view v_1 as (select A1.Name, A1.Sales,count(A2.Salees) Rank from Total_Sales A1, Total_Sales  A2 where A2.Sales > A1.Sales or (A2.Sales = A1.Sales and A2.Name >= A1.Name) GROUP BY  A1.Name,A1.Sales ORDER BY count(A1.Sales));
2.Then query the median row of data, which we will use here where Judgment statement combination count(*)Number of trips+1 Then divide by two
select name,sales 'middle sales' from v_1 where rank = (select (count(*)+1) div 2 from v_1);
#div is the way to calculate quotient in mysql

4.3. Calculate cumulative total

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

select A1.Name, A1.Sales, SUM(A2.Sales) Total_Sales ,count(A2.Sales) Rank from Total_Sales A1,Total_Sales A2 where A2.Sales > A1.Sales or (A2.Sales = A1.Sales and A2.Name >= A1.Name) GROUP BY A1.Name,A1.Sales ORDER BY A1.Sales desc;
#count(A2.Sales) or A1.Sales desc are the same results
#Just add a sum function and you can add up to each line

4.4 calculate the percentage of each line's share in the total

select A1.Name, A1.Sales, ROUND(A1.Sales/(select sum(Sales) from Total_Sales)*100,2) || '%'  Per_Sales,count(A2.Sales) Rank from Total_Sales A1, Total_Sales A2 where A2.Sales > A1.Sales or (A2.Sales = A1.Sales and A2.Name >= A1.Name) GROUP BY A1.Name,A1.Sales ORDER BY count(A2.Sales);
#Add a round function to divide the share of the current row in the function by the total share

4.5 calculate the percentage of each row's share in the current row's cumulative total share

select A1.Name, A1.Sales, ROUND(A1.Sales/SUM(A2.Sales)*100,2) || '%'  Per_Sales ,count(A2.Sales) Rank from Total_Sales A1, Total_Sales A2 where A2.Sales > A1.Sales or (A2.Sales = A1.Sales and A2.Name >= A1.Name) GROUP BY A1.Name,A1.Sales ORDER BY count(A2.Sales);
#Change the denominator to SUM(A2.Sales)

5: Supplement: the difference between NULL and no value

difference:

• The length of no 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 value is null or not. It is impossible to judge whether there is no value
• No value is processed with "=" or "< >". < > means not equal to
• During count() calculation, NULL will be ignored and no value will be added to the calculation

5.1 experimental verification

select length(null),length(''),length('1');

① View the space occupied by no value and null value

② Determine null and null values

select * from City;
select * from City where name is null;
select * from City where name is not null;

③ Judge no value

select * from City where name = '';
select * from City where name <> '';

④ View null and no values by viewing the number of rows

#View the number of rows, including null and no values
select count(*) from City ;
#View the number of rows, including no value, but excluding null value
select count(name) from City ;

6, Regular expression

6.1. Common regular expressions:

regular expression	explain	example
^	Matches the start character of the text
$	Matches the end character of the text
.	Match any single character
*	Matches zero or more characters before it
+	Matches the preceding character 1 or more times
character string	Match contains the specified string
p1|p2	Match p1 or p2
[...]	Matches any character in the character set
[^...]	Matches any character that is not in parentheses
{n}	Match the previous string n times
{n,m}	Match the previous string at least n times and at most m times

6.2 grammar

Syntax description: regexp

select field from form where field regexp {pattern}; 

select * from Store_Info where Store_Name regexp 'os';

select * from Store_Info where Store_Name regexp '^[A-G]';

select * from Store_Info where Store_Name regexp 'Ho|Bo';

7, Stored procedure

7.1 introduction to stored procedures

• MysQL database stored procedure is a set of SQL statements to complete specific functions
• The function of stored procedure has been supported since version 5.0. It can speed up the processing speed of database and enhance the flexibility of database in practical application
• 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
• The traditional SQL statements that operate the database need to be compiled before execution. Compared with stored procedures, it is obvious that stored procedures are faster and more efficient in execution

7.2 advantages of stored procedure

• 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

7.3 application of stored process

7.3.1 without parameters

1.Create stored procedure
delimiter $$    								#Change the closing symbol of the statement from semicolon; It can be modified temporarily to prevent problems and can be customized
create procedure proc()  	                    #Create a stored procedure, procedure name proc, without parameters
begin   		    						    #The process body starts with the keyword BEGIN
select * from form where Conditional statement;  	            #Process style sentence
end $$    									    #The procedure body ends with the keyword END
delimiter ; 			    					#Returns the closing symbol of the statement to a semicolon

2.Call stored procedure
call proc;

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

① Instance operation: create a stored procedure and call the stored procedure

mysql> select * from test;
+---------+------+----------+
| name    | age  | addr     |
+---------+------+----------+
| zs      |   10 | jiangsu  |
| zs      |   20 | nanjing  |
| lisi    |   30 | shanghai |
| wangwu  |   40 | shanghai |
| zhaoliu |   40 | huaian   |
+---------+------+----------+
mysql>delimiter $$
mysql> create procedure proc1()
    -> begin
    -> select * from test where name='zs';
    -> end 
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc1;
+------+------+---------+
| name | age  | addr    |
+------+------+---------+
| zs   |   10 | jiangsu |
| zs   |   20 | nanjing |
+------+------+---------+

② Viewing stored procedures

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

③ Delete stored procedure

drop procedure if exists Stored procedure name;   #Delete only if it exists without adding if exists. If it does not exist, an error will be reported

7.3.2 with parameters

• . input parameter: in indicates that the caller passes in a value to the procedure (the passed in value can be literal or variable)
• Output parameter: out indicates that the procedure sends out a value to the caller (multiple values can be returned) (the outgoing value can only be a variable)
• Input / output parameter: inout, which means that the caller passes in a value to the procedure and the procedure passes in a value to the caller (it can only be a variable)

delimiter $$  
create procedure proc(in Parameter field type)   #Formal parameter
begin
select * from form where Conditional statement;          #Process style sentence
end $$
delimiter ; 

call proc('data');                         #Argument

mysql> delimiter $$
mysql> create procedure proc2(in new_age int)
    -> begin
    -> select * from test where age=new_age;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc2(40);
+---------+------+----------+
| name    | age  | addr     |
+---------+------+----------+
| wangwu  |   40 | shanghai |
| zhaoliu |   40 | huaian   |
+---------+------+----------+

7.4. Storage control statement

① Conditional statement if... end if

#Conditional statement
delimiter $$ 
create procedure proc3(in a int)     #Parameter a
begin
declare b int;                  #Parameter b is the same as parameter a above
set b=a*2;                      #Conditional statement between parameter b and parameter a
if b>=25 then
update form set Modify field data  where Conditional statement;
else
update form set Modify field data  where Conditional statement;
end if ;
end $$
delimiter ;

call proc3 (parameter a Specific value);

Operation:

create table test2 (id int(10));
insert into test2 values (10);
#Conditional statement
delimiter $$ 
create procedure proc3(in pro int)    
begin
declare var int;                  
set var=pro*2;                  
if var>=10 then
update test2 set id=id+1 ;
else
update test2 set id=id-1;
end if ;
end $$
delimiter ;

call proc3 (6);

② Loop statement while... end while

delimiter $$
create procedure proc4()
begin
declare a int;
set a=0;
while a<6 do
update form set Modify field data  where Conditional statement;
set a=a+1;
end while;
end $$
delimiter ;

call proc4;

Operation:

delimiter $$
create procedure proc4()
begin
declare var int(10);
set var=0;
while var<6 do
insert into test2 value(var);
set var=var+1;
end while;
end $$
delimiter ;

call proc4;