当前位置：网站首页>Summary of common SQL statements

Summary of common SQL statements

2022-04-23 17:30:00 【Xiaobaidian】

List of articles

SQL Introduce

SQL It is a structured database query and programming language , For data access and query 、 Updating and managing databases .SQL It is divided into 4 Categories: ： Data definition language (DDL)、 Data manipulation language (DML)、 Data query language (DQL) And data control language (DCL)

  • Data definition language (DDL) Is to define the database and table , Keywords are create/alter/drop/truncate

  • Data manipulation language (DML) It is the operation of adding, deleting and modifying records in the table , Keywords are insert/update/delete

  • Data query language (DQL) It is the operation of querying the records in the table , Keywords are selete

  • Data control language (DCL) Is for the users of the database 、 jurisdiction 、 Transactions, etc , Keywords are grant/revoke/commit/set/rollback etc.

This article USES ：MySQL-8.0.26

SQL Keywords in are not case sensitive

Log in to the database

Connecting data in the command window using , Use the following command

Log in to the local database mysql -u root -p, Enter the database password

Log in to the remote database ：mysql -h ip Address -u user name -p, Enter the database password

You can use remote tools to connect to the database , for example ：Navicat、PyCharm、IDEA、VSCode etc. , There are association tips , Write SQL The sentence is more convenient

Database operation

Database addition, deletion, modification and query operation 

SHOW DATABASES;			#  View all databases 
CREATE DATABASE dyd;	#  Use the default parameters to create a file named dyd The database of 
CREATE DATABASE dyd DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;	#  establish UTF-8 Character coded database 
ALTER DATABASE dyd DEFAULT CHARACTER SET gbk;	#  Modify the database character code to GBK
USE dyd;				#  Using a database 
SHOW TABLES;			#  View all tables under the current database 
DROP DATABASE dyd;		#  Delete database

The operation of adding, deleting, modifying and querying data table 

CREATE TABLE workers (	#  Create a workers Database table of , And constrain the database fields , Comment on the table fields 
	id INT PRIMARY KEY AUTO_INCREMENT,				 #  Primary key , Self growth constraints 
	sid INT UNIQUE COMMENT ' staff id',					#  Unique constraint 
	sname VARCHAR (20) NOT NULL COMMENT ' full name ',		#  Non empty constraint 
	gender VARCHAR (2) DEFAULT '-1' COMMENT '1 Male ,0 For a woman ,-1 It means unknown ',	#  Default constraint 
    IDNum VARCHAR (20) NOT NULL UNIQUE COMMENT ' Id card number ',	#  Non empty , Unique constraint 
	birthday DATE COMMENT ' Birthday ',
	email VARCHAR (20) COMMENT ' mailbox ',
	remark VARCHAR (50) COMMENT ' remarks '
) DEFAULT CHARSET = utf8 COMMENT = ' Employee information form ';		#  The default character encoding uses UTF-8

DESCRIBE workers;	#  View table structure 
ALTER TABLE workers ADD COLUMN age INT (20) COMMENT ' Age ';		#  Add a table field 
ALTER TABLE workers ADD a INT,ADD b CHAR(2),ADD c FLOAT(5,2);	 #  Add multiple table fields at a time 
ALTER TABLE workers MODIFY COLUMN gender INT(2);	#  Modify table fields gender The type is INT(2)
ALTER TABLE workers CHANGE COLUMN gender sex INT(2);#  Modify table field name gender by sex
ALTER TABLE workers DROP COLUMN c;					#  Delete a table field 
ALTER TABLE workers DROP COLUMN a,DROP COLUMN b;	#  Delete multiple table fields at a time 
ALTER TABLE workers RENAME TO employees;			#  Modify the name of the table workers by employees
DROP TABLE employees;								#  Delete table

Data manipulation

insert data 

#  When a single piece of full data is inserted , Assign values according to the field order of the table structure 
INSERT INTO workers VALUES(1,00001,' Little Bai Dian ',1,'410928200207196688','2002-07-19','[email protected]','#');
#  Insert data into the specified field , The order of inserting values needs to be assigned according to the order of the specified fields , Fields with non empty constraints are indispensable when adding specified fields , That is, the fields not inserted can be null
INSERT INTO workers (sid,sname,IDNum) VALUES(00002,' Short paragraph ','410928200407196688');
#  Bulk insert data , Just like inserting a single piece of data , Pay attention to the field order , Each group of data is separated by English commas 
INSERT INTO workers (sid,sname,IDNum) VALUES
(00003,' Small days ',410928200507196688),
(00004,' The small white ',410928200607196688),
(00005,' Little black ',410928200707196688);

Modifying data 

#  modify sid by 00003 Of sname by ‘ Xiao Cai ’
UPDATE workers SET sname = ' Xiao Cai ' WHERE sid = 000003;
#  modify sid by 00003 The name is ‘ Side dish ’, The remarks are ‘ On trial ’
UPDATE workers SET sname = ' Side dish ',remark = ' On trial ' WHERE sid = 000003;
#  modify birthday All data in the field is ‘2002-06-06’
UPDATE workers SET birthday = '2002-06-06';
#  modify birthday All data in the field is ‘2002-08-08’,email All data in the field is ‘[email protected]’
UPDATE workers SET birthday = '2002-08-08',email = '[email protected]';

Cross table update

It is similar to the multi table query described below , If you study for the first time SQL, It is recommended to skip this section first ,update to set Is the statement used in the joint query of two tables ,set After that is the value to be modified ,where After that is the judgment condition , It can be understood as merging two tables into one table first , Then modify this table 

#  to update a In the table totalSales Value of field , be equal to a The unit price in the table is multiplied by a The sales volume in the table , And then add b The grades in the table 
UPDATE sales a LEFT JOIN workers b USING(sid) SET a.totalSales=a.price*a.saleVolume+b.levels;
#  When satisfied ab The two tables id When equal , to update a Tabular productID be equal to b Tabular id
UPDATE sales a LEFT JOIN product b ON a.id=b.id SET a.productID=b.id WHERE a.id=b.id;
UPDATE sales a LEFT JOIN product b USING(id) SET a.productID=b.id WHERE a.id=b.id;	#  Or use using

Delete data 

#  Delete id by 4 The employees' 
DELETE FROM workers WHERE id = 4;
#  Delete all table data , Clean up data in this way , Does not affect self growth constraints , The database will still record where it is currently growing 
DELETE FROM workers;
#  Truncation table , Also known as reset table , This method empties the table and only retains the table structure , Self growth constraints will also restart , This is equivalent to creating a new table after deleting the table 
TRUNCATE TABLE workers;

Cross table deletion

Follow update…join similar , If you study for the first time SQL, It is recommended to skip this section first , In a sql Statement to delete records of multiple tables at the same time , You can also delete records in a table according to the relationship between multiple tables 

#  Delete ab In the two tables id be equal to 7 A row of data 
DELETE a.*,b.* FROM sales a LEFT JOIN workers b ON a.id=b.sid WHERE a.id=7 AND b.sid=7;
#  When a surface id be equal to b surface sid when , Delete a In the table id be equal to 7 A row of data 
DELETE a FROM sales a LEFT JOIN workers b ON a.id=b.sid WHERE a.id=7;

Query data

Simple query 

SELECT * FROM workers;	#  Inquire about workers All information in the table 
SELECT sid ' Number ',sname AS ' full name ' FROM workers;	#  Inquire about workers In the table sid and sname, The header uses an alias ,AS not essential 
SELECT CONCAT(sname,'-',sid) AS ' full name - Number ' FROM workers;	#  Using functions concat Merge Columns , Merge results into one string

Conditions of the query 

SELECT sid+id FROM workers;					#  Supports arithmetic operations , Add (+)、 reduce (-)、 ride (*)、 except (/)、 Remainder (%)
SELECT sname FROM workers WHERE age < 20;	#  The query age is less than 20 Of employees , Support comparison operation （<、>、<=、>=、（!=、<>））
SELECT sid,sname FROM workers WHERE age BETWEEN 18 AND 20;	#  The age of inquiry is 18~20 Employee number and name , contain 18 and 20 year 
SELECT sid,sname FROM workers WHERE age NOT BETWEEN 18 AND 20;	#  The inquiry is not in 18~20 Employee number and name between years of age 
SELECT sname FROM workers WHERE sname IN(' Short paragraph ',' The small white ',' Xiaohong ');	#  Query employee names in the collection , If not in the collection, use NOT IN
SELECT sid FROM workers WHERE sname LIKE ' Small %';	#  Fuzzy query , Query name in ‘ Small ’ Employees at the beginning id,%( Match multiple characters )
SELECT sid FROM workers WHERE sname LIKE '_ paragraph '; #  The query name does not start with ‘ paragraph ’ Employees at the end id,_( matching 1 Characters , Multiple can be used together )
SELECT sname FROM workers WHERE email IS NULL;	#  Query employee name with empty email ,is not null By contrast 
SELECT sname FROM workers WHERE email IS NOT NULL AND sname LIKE ' Small _';	#  The query mailbox is not empty and the name is with ‘ Small ’ The first two characters of the name 
SELECT sname FROM workers WHERE id = 1 OR sid = 00002;	#  Inquire about id by 1 perhaps sid by 00002 Of employees , If it is satisfied at the same time, the results will return 
SELECT id FROM workers WHERE (sid=1 AND age<20) OR age>=22;	#  Inquire about sid by 1 And less than 18, Or older than or equal to 20 The employees' id
SELECT DISTINCT sname FROM workers WHERE age>18;	#  Query age is greater than 18 Of employees , Carry out deduplication 
select c.atitle,d.atitle,e.atitle from china c,china d,china e	#  Query the urban area of Henan Province 
where c.aid=d.pid 
and d.aid=e.pid 
and c.atitle=' Henan province ';

Aggregate query 

SELECT MAX(saleVolume) FROM sales;	#  Maximum , Query the maximum sales volume 
SELECT MIN(saleVolume) FROM sales;	#  minimum value , Query the minimum sales volume 
SELECT AVG(saleVolume) FROM sales;	#  Average , Query the average sales volume 
SELECT SUM(saleVolume) FROM sales;	#  Sum up , Query the total sales volume 
SELECT COUNT(sid) FROM sales WHERE saleVolume IS NOT NULL;	#  Count , Query how many employees have sales 
SELECT SUM(price*saleVolume) ' Total sales ' FROM sales;	#  Calculate total sales , And set the header alias to ‘ Total sales ’
SELECT SUM(price*saleVolume)/COUNT(sid) FROM sales WHERE productID=1;	#  Check the average sales of computers 
SELECT SUM(price*saleVolume) FROM sales WHERE MONTH(date)=04;	#  Inquire about 4 Monthly sales

Group query

It is generally used in combination with aggregate query , Aggregate queries for groups , After grouping, you can use aggregate query to filter the results , The condition judgment after grouping uses having, Then follow the aggregation query criteria , Support grouping again after grouping 

SELECT SUM(price*saleVolume) FROM sales GROUP BY salesGroup;	#  Check the sales of each group 
SELECT salesGroup,AVG(saleVolume) FROM sales GROUP BY salesGroup;	#  Query the average sales volume of each sales team 
SELECT salesGroup,AVG(saleVolume) FROM sales GROUP BY sid,salesGroup;	#  Query results are grouped by employees first , Then divide into groups 
SELECT salesGroup,SUM(saleVolume) FROM sales WHERE price=5600 GROUP BY salesGroup;	#  Check the total sales volume of each group 
SELECT salesGroup,COUNT(sid) FROM sales GROUP BY salesGroup HAVING COUNT(sid)<3;	#  The number of sales is less than 3 Group

Sort query 

SELECT * FROM sales ORDER BY saleVolume DESC;	#  The query results are arranged in descending order according to the sales volume 
SELECT * FROM sales ORDER BY saleVolume ASC;	#  Query results are arranged in positive order according to sales volume , Positive order arrangement can save Columns ASC
SELECT * FROM sales ORDER BY price,saleVolume DESC;	#  First, arrange them in descending order according to the unit price , Then arrange them in descending order according to the sales volume 
SELECT * FROM sales ORDER BY price DESC,saleVolume ASC;	#  First, arrange them in descending order according to the unit price , Then arrange them in positive order according to the sales volume 
SELECT * FROM sales WHERE saleVolume>5 ORDER BY price;	#  According to the positive order of unit price , Yes where When the conditions , Sorting should be where after

Paging query

Intercept part of the query result set , The first number represents the offset , That is, the number of rows from the starting position of the query to the difference of the first row of data , First act 0, The second represents the step size , That is, how many results are displayed at a time . When there is where Conditions 、 grouping 、 When sorting and paging , First, query the result set according to the criteria , Group again , And then sort it , Finally, check the page by page 

SELECT * FROM sales LIMIT 0,2;	#  Query the first two data 
SELECT * FROM sales LIMIT 2,4;	#  The query result is from 3 After the start of data 4 Data ( Including the first 3 Data )
SELECT * FROM sales WHERE price=5600 ORDER BY saleVolume DESC LIMIT 1,3;	#  First judge, then sort, and finally page

If any in the query where、group by( contain having)、order by、limit, The order of use is where→group by→order by→limit, The following example 

#  Check the sales volume of each group , Output group name and sales volume , The results are arranged in descending order according to the unit price , And only view the second and third data in the query results 
SELECT salesGroup,SUM(saleVolume) FROM sales WHERE price=5600 GROUP BY salesGroup ORDER BY price DESC LIMIT 1,2;

Through the above practice operation, we can see where and having The difference between ：

  • where The clause is in group by Filtering data before grouping and data summary

  • having The clause is in group by Filtering data after grouping and data summary

Subquery 

#  Inquire about id by 2 Name of employee and sales volume 
SELECT sname ' full name ',(SELECT saleVolume FROM sales WHERE sid=2) ' Sales volume ' FROM workers WHERE sid=2;	
#  Inquire about id by 2 Of employees 、 Sales and goods sold 
SELECT sname ' full name ',(SELECT SUM(price*saleVolume) FROM sales WHERE sid=2) ' sales ',
	(SELECT productName FROM product WHERE productID IN(SELECT productID FROM sales WHERE sid=2)) ' Selling goods ' 
FROM workers WHERE sid=2;
#  Inquire about id by 2 The name of the employee and their share of sales in the sales department 
SELECT sname ' full name ',
	(SELECT SUM(price*saleVolume) FROM sales WHERE sid=2)/(SELECT SUM(price*saleVolume) FROM sales)*100 ' Proportion of sales (%)'
FROM workers WHERE sid=2;	
#  If the subquery has results, the product name in the product table will be returned ,exists() It returns a Boolean value ,ture or flase,not exists() By contrast 
SELECT productName FROM product WHERE EXISTS(SELECT productID FROM sales);
#  Query all sales below 15 The employees' ID, Then query the employees larger than the maximum employee ID Of employees , there all Get is the maximum value in the sub query 
SELECT sname ' full name ' FROM workers WHERE sid >ALL(SELECT sid FROM sales WHERE saleVolume<15);
#  Query all sales below 15 The employees' ID, Then the query is larger than any employee obtained in the sub query ID Of employees , there any Take all the values in the sub query 
SELECT sname ' full name ' FROM workers WHERE sid >ANY(SELECT sid FROM sales WHERE saleVolume<15);
#  The expression meaning is the same as the previous one ,any and some It is equivalent. , The meaning of expression is the same 
SELECT sname ' full name ' FROM workers WHERE sid >SOME(SELECT sid FROM sales WHERE saleVolume<15);

Multiple tables associated query

The connection methods commonly used to query multiple tables are ： Internal connection ：inner join; External connection ：left join,right join,union; Cross connect ：cross join

Inner connection is also called equivalent connection 、 Natural join , The function is based on the relationship between columns in two or more tables , Then query the data from these tables , Inner join matches only rows

The external connection is divided into left connection 、 Right connection and merge connection , At least one party of this connection method retains all data , No matching line will use null Instead of

Cross connection is also called Cartesian product connection , The query result will return all rows in the left table , The rows in the right table are combined with each row in the left table 

# inner join For internal connection , I could just write it as join, The query is the intersection of two tables , That is, get the data with connection matching relationship in the two tables 
SELECT * FROM workers a INNER JOIN sales b ON a.sid=b.sid;	#  Joint query of two tables 
SELECT * FROM workers a JOIN sales b ON a.sid=b.sid JOIN product c ON b.productID=c.productID;	#  Three tables associated query 
#  The query level is 2 The name and sales volume of the employee 
SELECT a.sname,b.saleVolume FROM workers a JOIN sales b ON a.sid=b.sid WHERE levels=2;
# left join Left connection , Also known as left outer connection , Get all the data in the left table , If there are data matching the conditions in the right table, it will match , Otherwise null
SELECT * FROM workers a LEFT JOIN sales b ON a.sid=b.sid;
SELECT * FROM workers a LEFT JOIN sales b ON a.sid=b.sid LEFT JOIN product c ON b.productID=c.productID;
#  Using the left connection, you can also query the level of 2 The name and sales volume of the employee 
SELECT a.sname,b.saleVolume FROM workers a LEFT JOIN sales b ON a.sid=b.sid WHERE levels=2;
# right join Right connection , The opposite of the left link , Based on the right table , according to on The following conditions match the data 
SELECT * FROM workers a RIGHT JOIN sales b ON a.sid=b.sid;
SELECT * FROM workers a RIGHT JOIN sales b ON a.sid=b.sid RIGHT JOIN product c ON b.productID=c.productID;
#  Using the right connection here, you can also query the level of 2 The name and sales volume of the employee , The specific data in the actual table shall prevail , Not used inner/left/right Can find the same data 
SELECT a.sname,b.saleVolume FROM workers a RIGHT JOIN sales b ON a.sid=b.sid WHERE levels=2;
# union Join for merge , Used to combine two or more select sentence , The query result derives a result set after de reprocessing , Be careful ： The number of columns of multiple tables to be queried must be consistent 
SELECT * FROM sales UNION SELECT * FROM sales_copy;
#  When all along with union When used together （ namely UNION ALL）, Query results will not be de reprocessed , Repeated lines will display normally 
SELECT * FROM sales UNION ALL SELECT * FROM sales_copy;
# CROSS JOIN Cross connect , Get all the data in the left table , The data in the right table will be combined with each row in the left table 
SELECT * FROM sales b CROSS JOIN workers a ON b.sid=a.sid;

If two judgment key values have the same name , You can use using Instead of on, Internal connection 、 Both external connection and cross connection can be used , The following example 

SELECT * FROM workers a LEFT JOIN sales b ON a.sid=b.sid;
SELECT * FROM workers LEFT JOIN sales USING(sid);	#  have access to using Replace the above on Judge 
SELECT * FROM sales a LEFT JOIN product b ON a.id=b.id AND a.productID=b.productID;
SELECT * FROM sales a LEFT JOIN product b USING(id,productID);	#  As long as the key value has the same name , Multiple judgments can also be used using
SELECT * FROM workers a LEFT JOIN sales b ON a.sid=b.id;	#  This article SQL Because the keys have different names , So it can't be used using

stored procedure

A stored procedure is a procedure with processing business logic SQL sentence , It's like using java、python Such as code to implement business logic and operate the database , The stored procedure only uses the syntax of the database to realize this operation , In short , It's a group of... To complete a specific function SQL Statements set

Because stored procedures are executed on the server side of the database , Therefore, it has the characteristics of fast execution efficiency , Only the first execution needs to go through the compilation and optimization steps , Subsequent calls can be executed directly , But the portability is too poor , Stored procedures of different databases cannot be transplanted , And consume the resources of the database server , Less used in work , So here's a brief summary

Create stored procedure

Examples are as follows 

delimiter $						#  Stored procedure start identifier , The following symbol uses $、//、$$ Fine 
CREATE PROCEDURE FindProduct()	#  Create a FindProduct Stored procedure 
BEGIN
SELECT * FROM product;			#  Executes SQL sentence 
END $;							#  End of stored procedure , The following symbols should be consistent with the symbols used at the beginning

Execute stored procedures

Use call Execute stored procedures 

CALL FindProduct();	#  Check later product Product table data , Execute this SQL that will do

View stored procedures 

SHOW PROCEDURE STATUS;			#  The stored procedures in all databases are displayed , Include mysql Systematic

Delete stored procedure 

DROP PROCEDURE test_loop;		#  Delete stored procedure test_loop

Stored procedure access parameters

  1. Stored procedure input parameters - in

    Define input parameters , Use “in Parameter name data type ” Define input parameters 

    delimiter $
CREATE PROCEDURE Findoneproduct(IN pname VARCHAR(10))	#  Use “in  Parameter name   data type ” Defining parameters 
BEGIN
SELECT * FROM product WHERE productName=pname;	#  Call parameters 
END $;

    Define multiple input parameters 

    delimiter $
CREATE PROCEDURE Find_id_pro(IN pid INT(10),uid INT(5))	#  Multiple parameters are separated by English commas 
BEGIN
SELECT						#  According to the incoming goods id And the employees id, Check the name of the employee 、 The name of the commodity sold and the corresponding sales amount 
	b.sname ' Employee name ',
	a.productName ' Name of commodity ',
	c.price * c.saleVolume ' sales '
FROM product a
LEFT JOIN workers b USING (id)
JOIN sales c USING (sid)
WHERE
	a.productID = pid AND b.sid = uid ;
END $;

    Execute the stored procedure created above 

    CALL Find_id_pro(0,2);	#  According to the defined parameters , Pass parameters in order

  2. Stored procedure output parameters - out

    Define output parameters , Similar to input , Use out Show parameters , Access parameters can be used together 

    delimiter $
CREATE PROCEDURE emp(IN emp_id INT,OUT emp_name VARCHAR(30))	#  Use “out  Parameter name   data type ” Define the parameters 
BEGIN
SELECT sname INTO emp_name FROM workers WHERE sid=emp_id;	#  Incoming employees id Return the employee's name , Assign a value to the result emp_name Parameters 
END $;

    Execute the stored procedure created above 

    CALL emp(2,@emp_name);	#  Pass parameters in order , Incoming employees id, Return employee name , Add... Before your name @ Symbol 
SELECT @emp_name;		#  Check out the parameter results

  3. Stored procedure access parameters - inout

    inout have in and out Dual function , You can use either the value of the passed in variable or the value of the modified variable 

    delimiter $	# #  Use “out  Parameter name   data type ” Define the parameters 
CREATE PROCEDURE saleinfo(INOUT uid INT,INOUT sal_price INT(2),INOUT sal_vol INT(2),INOUT sal_total INT(2))
BEGIN
SELECT price INTO sal_price FROM sales WHERE sid=uid;		#  Incoming users id Get the unit price of the goods they sell 
SELECT saleVolume INTO sal_vol FROM sales WHERE sid=uid;	#  Incoming users id Get the sales volume of the goods they sell 
SET sal_total = sal_price * sal_vol;						#  Calculate sales 
END $;

    Execute the stored procedure created above 

    SET @uid = 2;	#  staff id Not obtained by query , Use... When you need to assign values yourself set Give the initial value of the variable 
CALL saleinfo(@uid,@sal_price,@sal_vol,@sal_total);	#  Call variables ,uid Assigned value , Other parameters are obtained through query calculation 
SELECT @uid,@sal_price,@sal_vol,@sal_total;	#  View the results obtained
Process control

Use if…elseif…else Process control , Usage and python The code in is similar to , The following example 

delimiter $
CREATE PROCEDURE if_age( IN age INT)				#  Define input parameters 
BEGIN
DECLARE str VARCHAR(20) DEFAULT NULL;				#  Declare internal variables , Set data type , The default is empty. 
IF age<18 THEN SET str=' minors ';
ELSEIF age>=18 AND age<=65 THEN  SET str=' puber ';
ELSEIF age>65 AND age<=99 THEN  SET str=' aged ';
ELSE  SET str= ' wow ！ Longevity ！';
END IF;
SELECT str;			#  Print variable name 
END $;

CALL if_age(100);	#  Execute stored procedures , The incoming age returns the corresponding variable value , return ' wow ！ Longevity ！'
Three cycles
while loop 
drop procedure if exists test_while;				#  If it's called test_while Delete the stored procedure if it exists 
delimiter $
CREATE PROCEDURE test_while (IN i INT)				#  Create a test_while Stored procedure , Parameter is i
BEGIN
WHILE i < 10 DO										#  When i Less than 10 Jump out when while loop 
	INSERT INTO product (productID) VALUES(i + 4);	#  Go to product Table insert data 
SET i = i + 1 ;					#  Each cycle i+1
END WHILE ;						#  end while loop 
SELECT productID FROM product;	#  View the data of the inserted field 
END $; 

CALL test_while(6)		#  Execute stored procedures
repeat loop 
drop procedure if exists test_repeat;				#  If it's called test_repeat Delete the stored procedure if it exists 
delimiter $
CREATE PROCEDURE test_repeat()						#  Create a test_repeat Stored procedure , No parameter 
BEGIN
DECLARE i INT;										#  Declare variables , The parameter , You can also define parameters in this way 
SET i = 0;											#  Assign a value to a variable , The default value is 
REPEAT
	INSERT INTO product (productID) VALUES(i + 4);
SET i = i + 1; 
UNTIL i < 10 END REPEAT; 							#  Until when i Less than 10 Jump out when repeat loop 
SELECT productID FROM product;
END $;

CALL test_repeat();		#  Because the default value has been given , All calling stored procedure parameters can be null
loop loop 
drop procedure if exists test_loop;					#  If it's called test_loop Delete the stored procedure if it exists 
delimiter $
CREATE PROCEDURE test_loop()						#  Create a test_loop Stored procedure , No parameter 
BEGIN
DECLARE i INT;										#  Defining variables 
SET i = 0; 											#  Set the default value of the variable 
yd:LOOP												# yd For the custom loop body name , loop Is the key word 
	INSERT INTO product (productID) VALUES(i + 4);
SET i = i + 1;
IF i < 10 THEN LEAVE yd;			#  When i Less than 10 When you leave yd The loop body , Out of the loop ,leave Indicates leaving the loop body ,iterate Means to exit the current cycle and continue to the next cycle 
END IF;
END LOOP; 
SELECT productID FROM product;
END $;

CALL test_loop();		#  Because the default value has been given , The default value is used when all execution stored procedure parameters are empty

trigger

When a table is operated , Hope to trigger some other actions at the same time , You can use triggers to complete , Can guarantee the integrity of the data , Act as a constraint , For example, after adding data to the product table, add a piece of data to the log table 

CREATE TRIGGER tri_log 	#  Create a file called tri_log The trigger of 
AFTER INSERT 		#  Trigger after insertion , also after update/delete,before insert/update/delete 5 Kind of operation 
ON product 			#  The table name triggers the event , Indicates which table has insert data and executes this trigger 
FOR EACH ROW		#  Indicates that the trigger event will be triggered when the operation on any record meets the trigger event 
BEGIN
	INSERT INTO oper_log (content, time)VALUES(' Added a new product record ',SYSDATE());	#  Trigger execution SQL sentence 
END ;

When inserting data into the product table , A new record will also be added to the log table 

INSERT INTO product VALUES(6,8,' Tables and chairs ','https://www.dyd.com');	#  Add a new item to the product table 
SELECT * FROM oper_log;		#  Check the operation log , A new record will be added

Check triggers 

SHOW TRIGGERS;

Delete trigger 

DROP TRIGGER tri_log;	#  Delete the name tri_log The trigger of

function

Common built-in functions

  1. case function

    case Function can realize complex logical judgment , With... In the code if…else The function is similar to , The usage is as follows 

    SELECT	#  Query employees id, Name and gender 
	sid ' staff ID',sname ' full name ',
	( CASE gender
		WHEN 0 THEN ' Woman '	#  The query result is 0 Then return to ‘ Woman ’
		WHEN 1 THEN ' male '	#  The query result is 1 Then return to ‘ male ’
		ELSE ' Unknown '		#  Other results return ‘ Unknown ’
		END ) ' Gender '		#  Set the alias to gender in the header 
FROM workers;

    You can also use judgment conditions 

    SELECT	#  Query employees id, Name and age 
	sid ' staff ID',sname ' full name ',
	( CASE
		WHEN age >= 65 THEN ' aged '	#  Query age is greater than or equal to 65 Then return to ‘ aged ’
		WHEN age >= 18 THEN ' puber '	#  Query age is greater than or equal to 18 Then return to ‘ puber ’
 		ELSE age		  #  Otherwise, return to the actual age 
		END ) ' age group '		#  The header is set to alias ID number. 
FROM workers;

  2. IF() function

    if Function can be understood as case Simplified version of function , If it's just a simple logical judgment ,IF Function is more suitable for , The usage is as follows 

    #  Query employees id、 full name , Judge whether the gender is correct , The gender is 0 or 1 Then return to ‘ Normal sex ’, Otherwise return to ‘ Unknown Gender ’
SELECT sid,sname,IF(gender=0 OR gender=1,' Normal sex ',' Unknown Gender ') FROM workers;
#  Query employees id、 full name , Determine whether the mailbox has been filled in , If the mailbox is empty, a prompt message will be returned , Otherwise, return to the existing mailbox 
SELECT sid,sname,IF(email IS NULL,' Email is empty , Please improve it in time ',email) FROM workers;
#  The above sentence can be abbreviated as the following , Use ifnull Judge 
SELECT sid,sname,IFNULL(email,' Email is empty , Please improve it in time ') FROM workers;

  3. substring_index() function

    substring_index The function is used to intercept a string by delimiter , The following example 

    SELECT SUBSTRING_INDEX(salesURL,'/',-1) FROM product;	#  from http://www.dyd.com/d166 Medium intercept d166
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(salesURL,'/',3),'//',-1) FROM product;	#  Intercept www.dyd.com

  4. concat() function

    concat Function can connect one or more strings , The following example 

    SELECT CONCAT(price,'*',saleVolume,'=',price*saleVolume) FROM sales;	#  Calculate sales , The result is 5*20=100

  5. Case conversion function 

    #  Inquire about workers In the table email And name , And put email Turn to uppercase display , Turn full spelling to lowercase display 
SELECT UPPER(email),LOWER(fullpin) FROM workers;

  6. Time function

    There are many functions of processing time , For example, get the current time , Get date , Minutes and seconds , Calculate the number of weeks , Calculate the day of the week , What day of the week is from 0 At the beginning , That is, find out 0 It means Monday 

    #  Get the current time ( Mm / DD / yyyy HHM / S ), The current date ( Specific date ), current time ( Minutes and seconds ), Current system time ( Mm / DD / yyyy HHM / S )
SELECT NOW(),CURDATE(),CURTIME(),SYSDATE();
#  obtain workers In the table birthday The date of the field , And calculate the week of the year , What day of the week is it 
SELECT NOW(),YEAR(birthday),MONTH(birthday),DAY(birthday),WEEK(birthday),WEEKDAY(birthday) FROM workers;
#  obtain sales The date, hour, minute and second of the sales time in the table 
SELECT YEAR(saledate),MONTH(saledate),DAY(saledate),HOUR(saledate),MINUTE(saledate),SECOND(saledate) FROM sales;

  7. Aggregate functions

    The aggregation query above also uses the built-in aggregation function 

    sum(),avg(),max(),min(),count()

Custom function

You can also customize functions , The following example 

CREATE FUNCTION sel_totalsale(uid INT) RETURNS INT		#  Create a file called sel_totalsale Function of 
BEGIN
 DECLARE totalSale INT DEFAULT 0;	#  Declare the local name as totalSale The variable of , The default is 1
 SELECT SUM(price*saleVolume) INTO totalSale FROM sales WHERE sid=uid;	#  Query employee sales , Assign a value to a variable totalSale
 RETURN totalSale;					#  return totalSale The value of the variable 
END;

Call custom functions 

SELECT sel_totalsale(5);		#  Query the employee number as 5 Sales volume

Delete custom function 

DROP FUNCTION sel_totalsale;	#  Delete the name sel_totalsale The custom function of

Indexes

The establishment of index for MySQL The efficient operation of the system is essential , The index can be greatly improved MySQL Retrieval speed of , When creating a table, there will basically be one id A primary key , Will be used as a primary key index , You can also create indexes while creating tables , The following example 

CREATE TABLE tab_index (
	id INT PRIMARY KEY AUTO_INCREMENT,	#  As a primary key index 
	sid INT, 
    sname VARCHAR(10),
	content VARCHAR(20),
	ustatus VARCHAR(10),
	INDEX sid_index (sid),	#  Create a sid_index The index of 
	INDEX (content)			#  Create a content The index of , Because the index name is not set , Therefore, the field name will be automatically used as the index name 
) DEFAULT CHARSET = utf8 COMMENT = ' Create a table with indexes ';

Create indexes separately 

#  Use create Command to create an index 
CREATE INDEX sta_index ON tab_index(ustatus);	#  Use tab_index In the table ustatus The field creation name is sta_index Indexes 
CREATE INDEX sta_index ON tab_index(ustatus,sname);		#  Create composite index 
CREATE UNIQUE INDEX sta_index ON tab_index(ustatus);	#  Create unique index 
#  Use alter Command to create an index 
ALTER TABLE tab_index ADD INDEX name_index (sname);
ALTER TABLE tab_index ADD UNIQUE INDEX (sid);

Delete index 

DROP INDEX sname ON tab_index;				#  Delete tab_index The name in the table is sname The index of 
ALTER TABLE tab_index DROP INDEX sname;		#  Or use this method to delete 
ALTER TABLE sname DROP PRIMARY KEY;			#  Delete primary key index

Index failure

Under some conditions, the index will fail , Therefore, it should be avoided when querying , For example

  • Index itself is invalid

  • Use like Query with wildcard characters (%) start , The index will fail , Turn to full table scan

  • Query criteria with or, Unless all query criteria are indexed , Otherwise, the index will fail

  • The value of the index column participates in the calculation , The index will fail , You can calculate the values involved in the calculation before querying

  • Violate the leftmost matching principle , For example, creating a composite index (sid,sname), When the query criteria do not sid The index is invalidated when

  • A string without single quotation marks will also invalidate the index , If you have any other information, please know by yourself

Performance analysis

usage ：explain+SQL sentence , Used for interpretation SQL sentence , It's what we usually say SQL The basis of sentence optimization , General inspection type Field , The order is ：all<index<range<ref<eq_ref<const<system, In general, at least range Level , The worst is to reach index, With the following SQL For example 

EXPLAIN SELECT sname ' full name ',
	(SELECT SUM(price*saleVolume) FROM sales WHERE sid=2)/(SELECT SUM(price*saleVolume) FROM sales)*100 ' Proportion of sales (%)'
FROM workers WHERE sid=2;

After creating the following index type Type by ALL Turn into ref

CREATE INDEX idx_sid ON sales(sid);

User management

To view the user 

SELECT * FROM mysql.user;	#  View user permissions and related information

Create user 

CREATE USER 'dy'@'%' IDENTIFIED BY '123456';	#  establish dy user , The user has access to any IP database 
CREATE USER 'dyd'@'localhost' IDENTIFIED BY '123456';	#  establish dyd user , The password for 123456, Specify that the user can only access the local database 
CREATE USER 'yd'@'192.166.66.23' IDENTIFIED BY '123456';	#  establish yd user , This user can only be in IP The address is .23 Access the database on the host 
CREATE USER 'yd'@'192.166.66.23,192.166.66.24' IDENTIFIED BY '123456';	#  Multiple IP The addresses are separated by commas

Change user name 

UPDATE mysql.user SET USER='yadian' WHERE user='dy';	#  modify dy The user is called yadian
FLUSH PRIVILEGES;	#  Refresh the permissions , Otherwise, the modification may not take effect

Change user password 

ALTER USER 'dy'@'%' IDENTIFIED BY '654321';	#  hold host by %, The user is called dy The password of is changed to 654321
FLUSH PRIVILEGES;	#  Refresh the permissions , Otherwise, the modification may not take effect

Delete user 

DROP USER yadian;	#  Delete the name yadian Users of 
DELETE FROM mysql.user WHERE user = 'yd';	#  You can also use this method to delete , But not recommended , There will be residual information in the system

Rights management

After the user is created , What can be done to the database , It needs to be completed through permission management

View user permissions 

SHOW GRANTS FOR 'dyd'@'localhost';	#  see host by localhost, The user is called dyd Authority

to grant authorization 

GRANT SELECT ON *.* TO 'dyd'@'%' WITH GRANT OPTION;			#  To the user dyd Query authority , And you can grant current permissions to other users 
GRANT ALL PRIVILEGES ON *.* TO 'dyd'@'%' WITH GRANT OPTION;	#  To the user dyd All permissions , And you can grant permissions to other users 
GRANT SELECT,UPDATE,INSERT,DELETE ON *.* TO 'dyd'@'%';		#  to dyd The user has the authority to query addition, deletion and modification , But you can't authorize other users 
GRANT SELECT,INSERT ON dyd.workers TO 'duanyd'@'%';	#  Only to duanyd The user action dyd In the database workers Table query and insert data permissions 
GRANT SELECT ON dyd.* TO 'duanyd'@'%';				#  to duanyd User query dyd Permissions for all tables in the database 
FLUSH PRIVILEGES;	#  You need to refresh the permissions after each authorization , Otherwise, the permission may not take effect

Revoke authority 

REVOKE DELETE ON *.* FROM 'dyd'@'%';			#  revoke dyd User's delete permission 
REVOKE ALL PRIVILEGES ON *.* FROM 'dyd'@'%';	#  revoke dyd All rights of the user

stay SQL The semicolon after the statement is changed to \G, Indicates that the query results are printed by column , You can make each field print to a separate line , For more fields in the table , It is recommended to use... When the query results in the command line window are messy , The query results are more clear at a glance , As shown in the figure below

The pit of tread

  1. Create function report This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable), That's because it turns on bin-log, Must give first function Specify a parameter

    terms of settlement ：

    First execute the following statement , Then execute the... Of the creation function SQL sentence 

    set global log_bin_trust_function_creators=TRUE;

  2. Create user reports Your password does not satisfy the current policy requirements, This is because the default password policy is medium , The demand is higher , You can lower the password policy level and shorten the password length

    terms of settlement ：

    • see mysql The initial password strategy 

      SHOW VARIABLES LIKE 'validate_password%'

    • Set the password authentication level , Set up validate_password.policy The global parameter of is LOW

      SET GLOBAL validate_password.policy=LOW;

    • Change password length , Set up validate_password.length The global parameter of is 6

      SET GLOBAL validate_password.length=6;

      Then a simple password can be set

  3. To blame root User authorization report You are not allowed to create a user with GRANT, because root Lack of system permissions

    terms of settlement ：

    Grant... First root All permissions , Perform the following SQL

    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;	#  Grant full permissions

    Execute the following command again and you will succeed ！

    GRANT SELECT ON *.* TO 'dyd'@'%' WITH GRANT OPTION;	#  grant dyd User query authority

    Grant non root After the user permissions , You can revoke the grant again root The ownership of the , recovery root Default permissions 

    REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';

版权声明
本文为[Xiaobaidian]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204231721343102.html

边栏推荐

猜你喜欢

随机推荐