Mysql high-order sql statements

The following two tables are explained


SELECT

SELECT "field" FROM "table name";
SELECT Store_Name FROM Store_Info;
Display all data records for one or several fields in the table


DISTINCT

SELECT DISTINCT "field" FROM "table name";
select distinct store_name from store_info;
Do not display duplicate data records


WHERE

SELECT "field" FROM "table name" WHERE "condition";
Find the store_name field of the symbol sales=700 condition in the store_info table
Conditional query


AND OR

SELECT "field" FROM "table name" WHERE "condition 1" 
select store_name,sales,date from store_info where sales > 1000 or (sales < 500 and sales > 200);
and or

IN

SELECT "field" FROM "table name" WHERE "field" BETWEEN ('value 1' AND 'value 2',...);
select * from store_info where store_name in ('Houston','Los Angeles');
Show data records with known values


BETWEEN

SELECT "field" FROM "table name" WHERE "field" BETWEEN 'value 1' AND 'value 2';
select * from store_info where sales between 200 and 500;
Show two value ranges


wildcard

%; the percent sign represents zero, one or more characters
_ : Underscore represents a single character

'A_Z': All strings starting with 'A', another character of any value, and ending with 'Z'. For example, 'ABZ' and 'A2Z' both fit this pattern, but 'AKKZ' does not (because there are two characters between A and Z, not one).

'ABC%': All strings starting with 'ABC'. For example, 'ABCD' and 'ABCABC' both fit this pattern.
'%XYZ': All strings ending with 'XYZ'. For example, 'WXYZ' and 'ZZXYZ' both fit 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 third letter is 'N'. For example, 'SAN FRANCIScO' fits this pattern, while 'LoS ANGELES' does not.

LIke

SELECT "field" FROM "table name" WHERE "field" LIKE {mode};
select * from store_info where store_name like '%os%';
Match a pattern to find the data record we want


ORDER BY

SELECT "field" FROM "table name" [WHERE "condition"] ORDER BY "field" [ASC,DESC];
select * from store_info order by sales;
select * from store_info order by sales desc;
#ASC is sorted in ascending order and is the default sorting method.
#DESC is to sort in descending order


function

mathematical functions;

abs (x) returns the absolute value of x

rand () returns a random number from o to 1

mod (x,y) returns the remainder after dividing x by y

power (x,y) returns x raised to the yth power

round (x) returns the nearest integer to x

round (x,y) retains the value of x rounded to y decimal places

sqrt(x) returns the square root of x

truncate (x, y) returns the value of the number x truncated to y decimal places

ceil(x) 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 the set

least(x1,x2...) returns the smallest value in the set

Aggregation function:
avg () returns the average 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

city ​​table

#count(*) includes the number of rows in all columns. When calculating the results, NULL column values ​​will not be ignored.
#count (column name) only includes the number of rows in the column of the column name. When counting the results, rows with a column value of NULL will be ignored.

String functions

trim () returns the value with the specified format removed

concat (x, y) concatenates the provided parameters x and y into a string

substr(x,y) gets the string starting from the y-th position in the string x, which has the same effect as the substring() function

substr(x,y,z) Gets a string of length z starting from the y-th position in string x

length (x) returns the length of string x

replace (x,y,z) replaces string z with string y in string x

upper (x) turns all letters of string x into uppercase letters

lower (x) turns all letters of string x into lowercase letters

left (x, y) returns the first y characters of string x

right (x, y) returns the last y characters of string x

repeat (x,y) repeats string x y times

space (x) returns x spaces

strcmp(x,y) compares x and y, the returned value can be -1,0,1

reverse (x) reverses the string x

Commonly used function examples:

concat

If sql_mode turns on PIPES_AS_CONCAT, ‘||’ is regarded as a string connection operator instead of an OR operator. It is similar to the string concatenation function Concat. This is similar to Oracle. This is the same as the Oracle database.


substr

trim


region

replace


group by

select "field 1",sum("field 2") from "table name" group by "field 1";
Group by has a principle, that is, among all the columns after select, the columns that do not use aggregate functions must appear after group by.


having

Used to retrieve the record set returned by the group by statement. It is usually used in conjunction with the group by statement.
The existence of the having statement makes up for the shortcoming that the where keyword cannot be used in conjunction with aggregate functions. If only the function column is selected, there is no need for a group by clause.


Alias

select "Table Alias"."Field One" [AS] "Field Alias" from "Table Name" [AS] "Table Alias";
Field alias, table alias


subquery

select "Field 1" from "Table" where "Field 2" [Comparison operator] #External query
select "field 1" from "table 2" where "condition";
Join the table and insert another sql statement in the where clause or having clause
It can be a symbolic operator, such as = > < <= >= or a literal operator, such as LIKE IN BETWEEN.


exists

Used to test whether the inner query produces any results, similar to whether a Boolean value is true
#If there is, the system will execute the SQL statement in the outer query. If there is 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');


table link

Experiment with the following two tables

inner join: only returns rows with equal join fields in the two tables
left join: Returns all records in the left table that are equal to the join fields in the right table
right join: Returns all records in the right table that are equal to the join field in the left table

Use subqueries to implement multi-table queries


create view

Views, which can be treated as virtual tables or stored queries
The difference between a view and a table is that the table actually stores data records, while the view is a structure built on the table and does not actually store data records itself. The temporary table disappears automatically after the user exits or the connection with the database is disconnected, but the view will not disappear.
A view does not contain data, only its definition is stored, and its purpose is generally to simplify complex queries. For example, if you want to connect and query several tables, and also perform statistical sorting and other operations, it will be very troublesome to write SQL statements. Use a view to connect several tables, and then query the view, which is the same as querying a table. The query is the same, very convenient.
View tables will not disappear automatically after disconnecting like temporary tables.
The view table saves the results of the select statement query and does not store data itself.
When the structure of the view table is the same as the structure of the original table where the data is stored, the data can be modified or inserted. If it is different, the data cannot be modified or inserted (such as the results of a multi-table join query)


union

Union: The data record values ​​generated will have no duplicates and will be sorted according to the order of the fields.
Syntax: [select statement 1] union [select statement 2];

Union all: List all data record values ​​that generate results, no matter how many duplicates there are
Syntax: [select statement 1] union all [select statement 2];


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

One of the two tables does not have the specified row, and the other table has duplicate rows. This is not applicable. It is used when the two tables do have intersecting rows.

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


No intersection value

Display the results of the first SQL statement, and the results have no intersection with the second SQL statement, and there are no duplicates.


case

It is a keyword used by SQL for logic such as if-then-else.
Syntax: select casd("field name")
       when "Condition 1" then "Result 2"
       when "condition 2" then "result 2"
       ....
       [else "resultN"]
       end
from "table name";

#The condition can be a numerical value or a formula. The else clause is not required


The difference between null and no value (‘ ‘)

1. The length of no value is 0 and does not occupy space; the length of NULL value is NULL and it takes up space.
2.IS NULL or IS Not NULL is used to determine whether the field is NULL or not NULL.
											It cannot be found out whether it is worthless.
3. Use ='' or <>'' to handle the judgment of no value. <> means not equal to.
4. When counting the number of rows in a specified field through count(), if a NULL value is encountered, it will be automatically ignored. If a no value is encountered, it will be added to the record for calculation.


regular expression

match patterndescribeExample
^Matches the starting character of text‘^bd’ matches strings starting with bd
$Matches the ending character of the text‘qn$’ matches a string ending with qn
.Match any single character‘s.t’ matches any string with one character between s and t
*Matches zero or more characters preceding it‘fo*t’ matches any number of o’s before t
+Matches the preceding character 1 or more times‘hom+’ matches a string starting with ho and followed by at least one m
stringMatches the specified string‘clo’ matches strings containing clo
p1|p2Match p1 or p2‘bg | fg’ matches bg or fg
[…]Match any character in the character set‘[abc]’ matches a or b or c
[^…]Matches any character not enclosed in parentheses‘[^ab]’ matches a string that does not contain a or b
{n}Match the previous string n times‘g{2}’ matches a string containing 2 g’s
{n,m}Match 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
grammar:
select "field" from "table name" where "field" regexp {mode};


stored procedure

A stored procedure is a set of SQL statements designed to accomplish a specific function.
When using a stored procedure, common or complex work is written in advance using SQL statements 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. Stored procedures are faster and more efficient in execution than traditional SQL.

Advantages of stored procedures:
1. After executing once, the generated binary code will reside in the buffer to improve execution efficiency.
2. A collection of SQL statements plus control statements, high flexibility
3. Store on the server side and reduce network load when called by the client.
4. It can be called multiple times and can be modified at any time without affecting client calls.
5. Can complete all database operations and control database information access permissions

Create stored procedure

delimiter $$ ##Change the end symbol of the statement from semicolon; temporarily to two $$, which can be customized
create procedure proc() ##Create a stored procedure, the procedure is named proc, without parameters
           
-> begin ##The process body starts with the keyword begin

-> select * from Store_info; ##Process statement
-> end $$ ##The process body ends with the keyword end
delimiter; ##Restore the end symbol of the statement to a semicolon

call proc ##Call stored procedure

show create procedure [database.] stored procedure name;

Stored procedure parameters

in input parameter: Indicates calling the folding process to pass in a value (the passed in value can be a literal or a variable)
out output parameter: indicates that the process passes a value to the caller (can return multiple values) (the passed value can only be a variable)
inout input and output parameters: it means that the caller passes a value to the process, and it also means that the process passes a value to the caller (the value can only be a variable)

View stored procedures

show create procedure [database.] stored procedure name;

Delete stored procedure

The method to modify the contents of a stored procedure is to delete the original stored procedure and then create a new stored procedure with the same name.
drop procedure if exists Proc;
#Delete only if it exists. If if exists is not added, if the specified process does not exist, an error will be generated.
 drop procedure proc1;

Stored procedure control statements

(1) Conditional statement if-then-else…end if

 delimiter $$
 create procedure proc2(in num int) 
 begin
 declare var int;
 set var=num*2;
 if var >= 10 then 
 update t set id=id+1; 
 else
 update t set id=id-1; 
 end if; 
 end$$

(2) Loop statement while… end while

Related Posts

Summary of common redis commands and use of RedisDesktopManager

mysql8.0 installation tutorial and configuration (most detailed) simple operation

Huawei Cloud RDS database evaluation: performance exceeded expectations, Double 11 discounts continue

Detailed explanation of MySQL Cluster (1) – basic principles

The driver package required by Kettle to connect to the mysql database causes an error (with driver download method attached)

Alibaba Yu Jun: DingTalk should take the path of low-code practice

Java project: train ticket reservation system (java+JDBC+JSP+Servlet+html+mysql)

Detailed usage tutorial of SQL Server (including detailed operations of starting SQL server service, establishing database, and creating tables), very suitable for beginners

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*