1, Processing of tabular data
1.1 ranking
#The table connects itself, and then lists the results in turn to calculate the number of rows before each row (including which row itself) select A1.Name, A1.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); #The value of the ranking field is smaller than its own value, and the number of ranking fields is the same as that of other fields. For example, aaa is 6 + 1 = 7
1.2 calculate the median
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
1.3 calculate cumulative total
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 count(A2.Sales); #As long as you add a sum function, you can add up to each line
1.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 l,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
1.5 calculate the percentage of each row's share in the total cumulative share of the current row
select A1.Name, A1.Sales, ROUND(A1.Sales/SUM(A2.Sales)*100,2) || '%' Per_Sales l,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)
2, NULL and no values (')
2.1 differences
1. The length of null value is 0 and does not occupy space
2. The length of null value is null, occupying space
3.is null cannot determine null value
4. Null values are processed with "=" or "< >"
5. During count() calculation, NULL will be ignored and NULL value will be added to the calculation
2.2 examples
3, Regular expression
3.1 common rules
| regular expression | explain |
|---|---|
| ^ | Matches the start character of the text |
| $ | Matches the end character of the text |
| . | Match any single character |
| * | Matches zero or more characters that precede it |
| + | Match 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 |
3.2 grammar
select field from form where field regexp 'regular expression ';
4, Stored procedure
4.1 introduction
1.MysQL database stored procedure is a set of SQL statements to complete specific functions
2. The function of stored procedure has been supported since version 5.0. It can speed up the processing speed of the database and enhance the flexibility of the database in practical application
3. 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 this stored procedure, you just need to call it
4. 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
4.2 advantages
1. After one execution, the generated binary code will reside in the buffer to improve the execution efficiency
2. The set of SQL statements and control statements has high flexibility
3. Store on the server side and reduce the network load when called by the client
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 authority of the database
4.3 format of stored procedure
4.3.1 without parameters
1.Create stored procedure delimiter $$ #Change the ending 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 of a stored procedure show create procedure proc\G show procedure status [like '%proc%'] \G
4.3.2 with parameters
1. Input parameter: in indicates that the caller passes in a value to the procedure (the passed in value can be literal or variable)
2. Output parameter: out indicates that the process sends out a value to the caller (multiple values can be returned) (the outgoing value can only be a variable)
3. Input / output parameter: inout, which means that the caller passes in a value to the process, and that the process 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
4.3.3 deleting stored procedures
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
4.4 control statement of stored procedure
4.4.1 conditional statement if... end if
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);
4.4.2 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;
