Contents of articles

Environmental preparation :
use gcc; 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'); select * from location; location The table is as follows : +--------+
-------------+ | Region | Store_Name | +--------+-------------+ | East | Boston
| | East | New York | | West | Los Angeles | | West | Houston | +--------+
-------------+ 4 rows in set (0.00 sec) 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 * from Store_Info; +
-------------+-------+------------+ | Store_Name | Sales | Date | +-------------
+-------+------------+ | Los Angeles | 1500 | 2020-12-05 | | Houston | 250 |
2020-12-07 | | Los Angeles | 300 | 2020-12-08 | | Boston | 700 | 2020-12-08 | +
-------------+-------+------------+ 4 rows in set (0.00 sec) -----SELECT
------- Displays all data in one or more fields of the table grammar : SELECT " Field " FROM " Table name "; SELECT Store_Name FROM
Store_Info; ---- DISTINCT ---- Do not display duplicate data grammar : SELECT DISTINCT " Field " FROM " Table name ";
SELECT DISTINCT Store_Name FROM Store_Info; -----WHERE------- Conditional query grammar : SELECT
" Field " FROM " Table name " WHERE " condition "; SELECT Store_Name FROM Store_Info WHERE Sales > 1000;
-----AND OR ----- And or grammar : 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); ----IN----------- Displays information about known values grammar : SELECT " Field " FROM " Table name " WHERE " Field " IN (' value 1',
' value 2',...); SELECT * FROM Store_Info WHERE Store_Name IN ('Los Angeles','Houston'
); ----BETWEEN------ Displays data in two ranges grammar : 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'; ------ wildcard ------- Usually, wildcards are used with LIKE Used together % : A percent sign means zero , One or more characters _ :
The underline represents a single character 'A_Z': All with 'A' Start , Another character of any value , And with 'Z' String ending with . for example ,'ABZ' and 'A2Z' All conform to this pattern , and 'AKKZ'
It doesn't match 'ABC%': All with 'ABC' The string at the beginning of the . for example ,'ABCD' and 'ABCABC' They all fit the pattern . '%XYZ': All with 'XYZ'
a null-terminated string . for example ,'WXYZ' and 'ZZXYZ' They all fit the pattern . '%AN%': All contain 'AN' The string of this pattern . for example ,'LOS ANGELES' and
'SAN FRANCISCO' They all fit the pattern . '_AN%': All the second letters are 'A' And the third letter is 'N' String of . for example ,'SAN FRANCISCO'
In line with this model , and 'LOS ANGELES' It does not conform to this pattern . ------LIKE---- Match a pattern to find out what we want grammar : SELECT " Field ”
FROM " Table name " WHERE " Field " LIKE { pattern }; SELECT * FROM Store_Info WHERE Store_Name like
'%os%'; ---- ORDER BY ----- Sort by keyword grammar : SELECT " Field " FROM " Table name " [WHERE " condition "] ORDER
BY " Field " [ASC,DESC]; #ASC Is sorted in ascending order , Is the default sort method . #DESC Is sorted in descending order . SELECT Store_Name,
Sales,Date FROM Store_Info ORDER BY Sales DESC;
------------------------ function -------------------------
1, Mathematical function :

Function action
abs(x) return x Absolute value of
rand() return 0 reach 1 Random number of
mod(x,y) return x divide y Remainder after
power(x,y) return x Of y Power
round(x) Back away x Nearest integer
round(x,y) retain x Of y The rounded value of a decimal place
sqrt(x) return x The square root of
truncate(x,y) Return number x Truncate to y Decimal value
ceil(x) Return greater than or equal to x The smallest integer of
floor(x) Return 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
give an example :
SELECT abs(-1),rand(),mod(5,3),power(2,3),round(1.89); SELECT round(1.8937,3),
truncate(1.253,2),ceil(5.2),floor(2.1),least(1.89,3,6.1,3.3);
2, Aggregate function :

Function action
avg() Returns the average value of the specified column
count() Returns the non-zero value 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 of the specified column
give an example :
SELECT avg(Sales) FROM Store_Info; SELECT count(Store_Name) FROM Store_Info;
SELECT count(DISTINCT Store_Name) FROM Store_Info; #DISTINCT Do not display duplicate information #count(*)
Includes the number of rows for all columns , At the time of statistical results , Column values of NULL( empty ) My line ; #count( Listing )
The number of rows that only include the column name , At the time of statistical results , The column value is ignored NULL( empty ) My line . SELECT max(Sales) FROM Store_Info; SELECT
min(Sales) FROM Store_Info; SELECT sum(Sales) FROM Store_Info;
3, String function :

Function action
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 No.1 in y String starting at position , With substring() The function works the same
substr(x,y,z) Get from string x No.1 in y Position starting length z String of
length(x) Return string x The length of
replace(x,y,z) Use string z Substitution string x String in y
upper(x) String x All the letters in the alphabet are capitalized
lower(x) String x All the letters in the alphabet 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 value returned can be -1,0,1
reverse(x) String x reversal
give an example :
SELECT concat(Region,Store_Name) FROM location WHERE Store_Name ='Boston';
give the result as follows : +---------------------------+ | concat(Region,Store_Name) | +
---------------------------+ | EastBoston | +---------------------------+ 1 row
in set (0.00 sec) # as sql_mode open It's on PIPES_AS_CONCAT( The module is in the /etc/my.cnf Check to see if it is on ),"||"
A join operator that is treated as a string instead of the or operator , And string splicing function Concat Similar , It's not the same Oracle The database is used in the same way SELECT Region || ' ' ||
Store_Name FROM location WHERE Store_Name = 'Boston'; give the result as follows : +
-----------------------------+ | Region || ' ' || Store_Name | +
-----------------------------+ | East Boston | +-----------------------------+ 1
row in set (0.00 sec) 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 character string ); #[ position ]: The value can be LEADING ( Start )
,TRAILING ( ending ),BOTH ( Beginning and end ). #[ String to remove ]: From the beginning of a string , End or start and end removed string . The default is space . SELECT
TRIM(LEADING 'Ne' FROM 'New York'); SELECT Region,length(Store_Name) FROM
location; mysql> select Region,length(Store_name) from location; +--------+
--------------------+ | Region | length(Store_name) | +--------+
--------------------+ | East | 6 | | East | 8 | | West | 11 | | West | 7 | +
--------+--------------------+ 4 rows in set (0.00 sec) SELECT REPLACE (Region,
'ast','astern') FROM location; ------GROUP BY------ yes GROUP
BY Summarize and group the query results in the following fields , It is usually used in conjunction with aggregate functions GROUP BY There is a principle , namely SELECT
In all subsequent columns , Columns that do not use aggregate functions , Must appear in GROUPBY behind . grammar : 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; mysql> select Store_Name,sum(Sales) from Store_Info group by
Store_Name order by sales desc; +-------------+------------+ | Store_Name | sum(
Sales) | +-------------+------------+ | Houston | 250 | | Washington | 300 | |
Boston | 700 | | Los Angeles | 1500 | +-------------+------------+ 4 rows in set
(0.00 sec) ------- HAVING ---- Used to filter GROUP BY Statement to return the recordset , Usually with GROUP BY Statement combination HAVING
The existence of the sentence makes up for it WHERE Keyword cannot be combined with aggregate function . If it is SELECT Only the function bar is available , Then there's no need GROUP BY clause . grammar : 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; ----
---- alias --------- Field alias Table alias grammar : SELECT " Table alias "." Field 1" [AS] " Field alias " FROM " Form name " [AS]
" Table alias "; SELECT A.Store_Name Store,SUM(A.Sales) "Total Sales" FROM Store_Info A
GROUP BY A.Store_Name; --------- Subquery -------- Linking tables , stay WHERE Clause or HAVING Clause SQL sentence
grammar : SELECT " Field 1" FROM " form 1" WHERE " Field 2” [ Comparison operator ] # External query (SELECT " Field 1" FROM " form 2"
WHERE " condition "); # Internal query # An operator that can be a symbol , for example =,>,<,>=,<= ; It can also be a literal operator , for example 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); SELECT
Store_Name,SUM(Sales),COUNT(Sales) FROM Store_Info GROUP BY Store_Name ORDER BY
Sales; +-------------+------------+--------------+ | Store_Name | SUM(Sales) |
COUNT(Sales) | +-------------+------------+--------------+ | Houston | 250 | 1 |
| Boston | 700 | 1 | | Los Angeles | 1800 | 2 | +-------------+------------+
--------------+ 3 rows in set (0.01 sec) ------- EXISTS
------ Used to test whether the inner query produces any results , Is Boolean like value true #
If there is one , The system will execute the SQL sentence . If not , That's the whole thing SQL Statement will not produce any results . grammar : SELECT " Field 1" FROM " form 1"
WHERE EXISTS (SELECT * FROM " form 2" WHERE " condition "); SELECT SUM(Sales) FROM Store_Info
WHERE EXISTS (SELECT * FROM location WHERE Region = 'West'); SELECT SUM(Sales)
FROM Store_Info WHERE Store_Name IN ('Los Angeles','Houston'); +------------+ |
SUM(Sales) | +------------+ | 2050 | +------------+ 1 row in set (0.00 sec)
-------------- join query ---------------- location The table is as follows : +--------+-------------+ |
Region | Store_Name | +--------+-------------+ | East | Boston | | East | New
York | | West | Los Angeles | | West | Houston | +--------+-------------+ 4 rows
in set (0.00 sec) UPDATE Store_Info SET store_name='Washington' WHERE sales=300;
Store_Info The table is as follows : mysql> select * from Store_Info; +-------------+-------+
------------+ | Store_Name | Sales | Date | +-------------+-------+------------+
| Los Angeles | 1500 | 2020-12-05 | | Houston | 250 | 2020-12-07 | | Washington
| 300 | 2020-12-08 | | Boston | 700 | 2020-12-08 | +-------------+-------+
------------+ 4 rows in set (0.00 sec) inner join( Equivalent connection ): Returns only rows with equal join fields in two tables left
join( Left join ): Returns all records in the left table and records with the same join fields in the right table right join( Right join ): Returns records that include all records in the right table and those whose join fields are equal in the left table
SELECT * FROM location A INNER JOIN Store_Info B on A.Store_Name = B.Store_Name;
mysql> SELECT * FROM location A INNER JOIN Store_Info B on A.Store_Name = B.
Store_Name; +--------+-------------+-------------+-------+------------+ | Region
| Store_Name | Store_Name | Sales | Date | +--------+-------------+-------------
+-------+------------+ | West | Los Angeles | Los Angeles | 1500 | 2020-12-05 |
| West | Houston | Houston | 250 | 2020-12-07 | | East | Boston | Boston | 700 |
2020-12-08 | +--------+-------------+-------------+-------+------------+ 3 rows
in set (0.00 sec) 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--------- view , It can be regarded as a virtual table or a storage query
What's the difference between a view and a table , The form contains the actual stored data , A view is a structure built on a table , It does not actually store data itself .
The temporary table disappears automatically when the user exits or loses the connection to the database , And the view doesn't disappear .
The view does not contain data , Only store its definition , It can be used to simplify complex queries . For example, you need to query several tables , But also for statistical sorting and other operations , Written SQL Statements can be cumbersome , Join several tables with views , Then query the view , It's like querying a table , Very convenient .
grammar : CREATE VIEW " View table name " AS "SELECT sentence "; 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; # Delete view
------------UNION------- combine , Put the two together SQL Statement , Two SQL The fields generated by the statement need to be of the same data type UNION:
There will be no duplicate data values for the generated results , And sort according to the order of the fields grammar : [SELECT sentence 1] UNION [SELECT sentence 2]; UNION ALL:
List the data values of the generated results , With or without repetition grammar : [SELECT sentence 1] UNION ALL [SELECT sentence 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 two SQL Intersection of statement results 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); 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; # Take two SQL Intersection of statement results , And there is no repetition SELECT A.Store_Name FROM (SELECT A.
Store_Name FROM location A INNER JOIN Store_Info B ON A.Store_Name = B.
Store_Name) A GROUP BY A.Store_Name HAVING COUNT(*) >= 1; SELECT DISTINCT A.
Store_Name FROM location A INNER JOIN Store_Info B USING(Store_Name); _Name);
-------------- No intersection value ------- Show first SQL The result of the statement , And with the second one SQL Statement has no result of intersection , And there is no repetition 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 -------- yes SQL Used as a
IF-THEN-ELSE Key words of logic grammar : SELECT CASE (" Field name ") WHEN " condition 1" THEN " result 1" WHEN " condition 2"
THEN " result 2" ...... [ELSE " result N"] END FROM " Table name "; #" condition " It can be a numerical 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 for CASE The field name of that field # Create a new table : 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); Total_Sales The table is as follows : mysql> select * from
Total_Sales; +----------+-------+ | Name | Sales | +----------+-------+ |
zhangsan | 10 | | lisi | 15 | | wangwu | 20 | | zhaoliu | 40 | | sunqi | 50 | |
zhouba | 20 | | wujiu | 30 | +----------+-------+ 7 rows in set (0.00 sec)
------- Ranking ------ self join (self Join), Then list the results in order , Before calculating each line ( Include the line itself ) How many lines are there 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; #
Statistics sales The value of a field is smaller than its own value, and sales Fields and Name The number of fields is the same , such as zhangsan by 5+1=6 +----------+-------
+------+ | Name | Sales | Rank | +----------+-------+------+ | sunqi | 50 | 1 |
| zhaoliu | 40 | 2 | | wujiu | 30 | 3 | | wangwu | 20 | 4 | | zhouba | 20 | 4 |
| lisi | 15 | 6 | | zhangsan | 10 | 7 | +----------+-------+------+ 7 rows in
set (0.00 sec) ---------- 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 It has to be #DIV It's in MySQL How to calculate quotient +--------+ |
Middle | +--------+ | 20 | +--------+ 1 row in set (0.00 sec) --------
Cumulative total ------- self join (Self Join), Then list the results in order , Before calculating each line ( Include the line itself ) The 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; +----------+-------+-----------+ | Name | Sales |
Sum_Total | +----------+-------+-----------+ | sunqi | 50 | 50 | | zhaoliu | 40
| 90 | | wujiu | 30 | 120 | | zhouba | 20 | 140 | | wangwu | 20 | 140 | | lisi |
15 | 175 | | zhangsan | 10 | 185 | +----------+-------+-----------+ 7 rows in
set (0.00 sec) --------- Total percentage -------------- SELECT A1.Name,A1.Sales,A1.Sales/(
SELECT SUM(Sales) FROM Total_Sales) Per_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; #SELECT SUM(Sales) FROM Total_sales
This query is used to calculate the total # After total calculation , We can then divide each row by the sum one by one to find the sum percentage of each row +----------+-------+-----------
+ | Name | Sales | Per_Total | +----------+-------+-----------+ | sunqi | 50 |
0.2703 | | zhaoliu | 40 | 0.2162 | | wujiu | 30 | 0.1622 | | zhouba | 20 |
0.1081 | | wangwu | 20 | 0.1081 | | lisi | 15 | 0.0811 | | zhangsan | 10 |
0.0541 | +----------+-------+-----------+ 7 rows in set (0.00 sec)
-------- Cumulative total percentage -------------- SELECT A1.Name,A1.Sales,SUM(A2.Sales)/(SELECT SUM
(Sales) FROM Total_Sales) Per_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; +----------+-------+-----------+ | Name | Sales
| Per_Total | +----------+-------+-----------+ | sunqi | 50 | 0.2703 | | zhaoliu
| 40 | 0.4865 | | wujiu | 30 | 0.6486 | | zhouba | 20 | 0.7568 | | wangwu | 20 |
0.7568 | | lisi | 15 | 0.9459 | | zhangsan | 10 | 1.0000 | +----------+-------+
-----------+ 7 rows in set (0.00 sec) # Accumulated total SUM(a2.sales) Divide by the total to find the cumulative total percentage of each row .
SELECT A1.Name,A1.sales,TRUNCATE(ROUND(SUM(A2.Sales)/(SELECT SUM(Sales) FROM
Total_Sales),4)*100,2) || '%' Per_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; +----------+-------+-----------+ | Name |
sales | Per_Total | +----------+-------+-----------+ | sunqi | 50 | 27.03% | |
zhaoliu | 40 | 48.65% | | wujiu | 30 | 64.86% | | zhouba | 20 | 75.68% | |
wangwu | 20 | 75.68% | | lisi | 15 | 94.59% | | zhangsan | 10 | 100.00% | +
----------+-------+-----------+ 7 rows in set (0.00 sec) --------- Null value (NULL) And no value (
'') The difference between --------- 1. The length of no value is 0, Space free ; and NULL The length of the value is NULL, It takes up space . 2. Is NULL perhaps Is NOT
NULL, It is used to determine whether a field is NULL Or not NULL, We can't find out if it's worthless . 3. Judgment and use of no value ='' perhaps <>'' To deal with it . <> Representative is not equal to . 4.
Through count() When specifying how many rows the field counts , If you encounter NULL Values are automatically ignored , If there is no value, it will be added to the record for calculation . establish City form : use gcc;
create table city (name char(10)); insert into city values('beijing'); insert
into city values('nanjing'); insert into city values('shanghai'); insert into
city values(); insert into city values(); insert into city values(); insert into
city values(''); insert into city values(''); mysql> select * from city; +
----------+ | name | +----------+ | beijing | | nanjing | | shanghai | | NULL |
| NULL | | NULL | | | | | +----------+ SELECT length (NULL),length(''),length(
'1'); +---------------+------------+-------------+ | length (NULL) | length('')
| length('1') | +---------------+------------+-------------+ | NULL | 0 | 1 | +
---------------+------------+-------------+ 1 row in set (0.00 sec) SELECT *
FROM city WHERE name IS NULL; # You don't count in the non value SELECT * FROM city WHERE name IS NOT
NULL; # It's going to count in the non value SELECT * FROM city WHERE name = ''; SELECT * FROM city WHERE
name <> ''; SELECT COUNT(*) FROM city; # Null and no values are calculated SELECT COUNT(name) FROM city
; # Ignore null values , But it's going to count in the non value ------------- regular expression ------------------ Matching pattern describe example ^ Matches the start character of the text ‘
^bd’ Match to bd The string at the beginning of the $ Matches the end character of the text ‘qn$’ Match to qn a null-terminated string . Matches any single character ‘s.t’ Match any s and t
A string with one character between * Matches zero or more characters before it ‘fo*t’ matching t There's any one in front o + Match previous characters 1 Times or times ‘hom+’ Match to ho
start , At least one in the back 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 characters that are not in brackets ‘[^ab]’
Match does not contain a perhaps b String of {n} Match previous string n second ‘g{2}’ Match contains 2 individual g String of {n,m} Match previous string at least n
second , at most m second ‘f{1,3}’ matching f least 1 second , most 3 second grammar : SELECT " Field ” FROM " Table name " WHERE " Field " REGEXP
{ pattern }; SELECT * FROM Store_Info WHERE Store_Name REGEXP 'os'; # Match contains os String of SELECT
* FROM Store_Info WHERE Store_Name REGEXP '^[A-G]'; # Match to A-G The string at the beginning of the SELECT * FROM
Store_Info WHERE Store_Name REGEXP 'Ho|Bo'; # Matching has Ho or Bo String of
------------- stored procedure ---------------- 1, A stored procedure is a set of procedures designed to perform specific functions SQL Statement collection . 2
, Stored procedure is used to pre use common or complex work SQL The statement is written 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 , Just call it . Stored procedure is better than traditional procedure in execution SQL Faster , More efficient execution .
3, Advantages of stored procedures : (1) After one execution , The generated binary code resides in the buffer , Improve execution efficiency (2)SQL Statement plus a collection of control statements , High flexibility (3
) Storage on the server side , When called by client , Reduce network load (4) It can be called repeatedly , It can be modified at any time , Does not affect client calls (5) Can complete all database operations , It can also control the information access rights of the database
## Create stored procedure ## DELIMITER $$ # Change the ending symbol of a statement from a semicolon ; Temporarily changed to two $$( It can be custom ) CREATE PROCEDURE Proc() #
Create stored procedure , The procedure is called Proc, Without parameters -> BEGIN # The process body is represented by keywords BEGIN start -> select * from Store_Info; #
Process body sentences -> END $$ # The process body is represented by keywords END end DELIMITER ; # Returns the ending symbol of a statement to a semicolon ## Calling stored procedure ## CALL Proc;
## View stored procedures ## SHOW CREATE PROCEDURE [ database .] Stored procedure name ; # View the specific information of a stored procedure SHOW CREATE
PROCEDURE Proc; SHOW PROCEDURE STATUS [LIKE '%Proc%'] \G ## Parameters of stored procedure ## IN
input parameter : Indicates that the caller passes in a value to the procedure ( The incoming value can be a literal quantity or a variable ) OUT Output parameters : Represents the value passed out by the procedure to the caller ( Multiple values can be returned )( The outgoing value can only be a variable ) INOUT
Input and output parameters : Indicates that the caller passes in a value to the procedure , It also means that the procedure sends out a value to the caller ( The value can only be a variable ) give an example : DELIMITER $$ CREATE PROCEDURE Proc1
(IN inname CHAR(16)) -> BEGIN -> SELECT * FROM Store_Info WHERE Store_Name =
inname; -> END $$ DELIMITER ; CALL Proc1('Boston'); ## Delete stored procedure ##
The method of modifying stored procedure content is to delete the original stored procedure , Then create a new stored procedure with the same name . DROP PROCEDURE IF EXISTS Proc; ##
Control statement of stored procedure ## create table t (id int(10)); insert into t values(10); (1)
Conditional statement if-then-else-end if DELIMITER $$ CREATE PROCEDURE proc2(IN parameter int) ->
begin -> declare var int; -> set var=parameter*2; -> if var>=10 then -> update t
set id=id+1; -> else -> update t set id=id-1; -> end if; -> end $$ DELIMITER ;
CALL Proc2(6); (2) Loop statement while ···· end while DELIMITER $$ CREATE PROCEDURE proc3()
-> begin -> declare var int(10); -> set var=0; -> while var<6 do -> insert into
t values(var); -> set var=var+1; -> end while; -> end $$ DELIMITER ; CALL Proc3;

Technology
©2019-2020 Toolsou All rights reserved,
Hikvision - Embedded software written test questions C Language application 0 The length of array in memory and structure is 0 In depth analysis data structure --- The preorder of binary tree , Middle order , Subsequent traversal How to do it ipad Transfer of medium and super large files to computer elementui Shuttle box el-transfer Display list content text too long 2019 The 10th Blue Bridge Cup C/C++ A Summary after the National Games ( Beijing Tourism summary )unity Shooting games , Implementation of first person camera python of numpy Module detailed explanation and application case Study notes 【STM32】 Digital steering gear Horizontal and vertical linkage pan tilt Vue Used in Element Open for the first time el-dialog Solution for not getting element