Mysql Basics: 06. View, variable, stored procedure, function and process control

1, View

View: a virtual table, which is used as an ordinary table. The new feature in MySQL version 5.1 is the data generated dynamically through tables

View application scenario:

The same query results are used in multiple places
The sql statements used in the query results are complex

(a bit of a function)

Advantages of view:

Reuse sql statements
Simplify complex sql operations without knowing its query details
Protect data and improve security

1. View creation

Syntax: create view view name as query statement

Case:

USE myemployees;
#1. Query the employee name, department name and type of work information with a character in the name
#① Create view
CREATE VIEW myv1 AS
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
JOIN `departments` d ON e.`department_id` = d.`department_id`
JOIN `jobs` j ON e.`job_id` = j.`job_id`;
#② Use view
SELECT * FROM myv1 WHERE `last_name` LIKE '%a%';

#2. Query the average salary level of each department
#① Create a view to view the average salary of each department
CREATE VIEW myv2 AS
SELECT AVG(`salary`) ag, `department_id`
FROM `employees` GROUP BY `department_id` 
#② Use view
SELECT myv2.ag,g.`grade_level`
FROM myv2 JOIN `job_grades` g 
ON myv2.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#3. Query the information of the Department with the lowest average wage
#Use the created view 2
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

#4. Query the Department name and average wage with the lowest average wage
#① Create a view to view information about the Department with the lowest average wage
CREATE VIEW myv3 AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1; # Dolls
#② Using view 3
SELECT d.`department_name`,m.ag
FROM myv3 m JOIN `departments` d
ON m.`department_id` = d.`department_id`;

2. View modification

Syntax:

#Mode 1:
create or replace view View name as Query statement;
#Mode 2:
alter view View name as Query statement;

3. Delete view

Users can delete one or more views at a time, provided they have drop permission for the view

Syntax:

drop view View 1, view 2,...;

4. View

Syntax:

desc View name;
show create view View name;

5. View update

A view is a virtual table. You can update (add, delete, modify) its results, but it is generally only used for query

Update operation: Insert: insert modify: update delete: delete

It is worth noting that the addition, deletion and modification of the view will change the original table of the view from

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;

SELECT * FROM myv1;
SELECT * FROM employees;

#1. Insert
INSERT INTO myv1 VALUES('Fei Zhang','zf@qq.com');

#2. Modification
UPDATE myv1 SET last_name = 'zhang wuji' WHERE last_name='Fei Zhang';

#3. Delete
DELETE FROM myv1 WHERE last_name = 'zhang wuji';

Views are generally used for queries rather than updates, because there are many situations that cannot be updated, which cover most statements,

sql statements containing the following keywords: grouping function, distinct, group by, having, union, or union all
Constant view
Select contains subqueries
join
from a view that cannot be updated
The subquery of the where clause references the table in the from clause

6. View vs table

	keyword	Does it occupy physical space	Usage scenario
view	view	It takes less time and only saves sql logic	Generally used for query
surface	table	Save actual data	Add, delete, modify and query

2, Variable

1. System variables

System variables are defined by the system, not by users, and belong to the server level. System variables are divided into global variables and session variables.

Global variable: the global keyword needs to be added. The scope target is valid for all sessions (connections), but cannot be restarted across multiple sessions

Session variable: the session keyword needs to be added. The scope target is valid for the current session (connection). If it is not written, the default session level is used.

System variable usage syntax:

View all system variables
```
show global|[session] variables;
```
View some system variables that meet the criteria
```
show global|[session] variables like '%char%';
```
View the value of the specified system variable (only dot structure can be used)
```
select @@global.|[session.]System variable name;
```

Assign a value to a system variable (two methods, which can unify the dotted structure if it is convenient for memory)

#Mode 1:
set @@global|[session].System variable name=value;
#Mode 2:
set global|[session]System variable name=value;

Case:

#Global variable Demo:
#① View all global variables
SHOW GLOBAL VARIABLES;
#② View some system variables that meet the criteria
SHOW GLOBAL VARIABLES LIKE '%char%';
#③ View the value of the specified system variable
SELECT @@global.autocommit;
#④ Assign a value to a system variable
SET @@global.autocommit = 0;
SET GLOBAL autocommit = 0;


#Session variable presentation:
#① View all session variables
SHOW VARIABLES;
#② View some session variables that meet the criteria
SHOW VARIABLES LIKE '%char%';
#③ View the value of the specified session variable
SELECT @@autocommit;
SELECT @@session.autocommit;
#④ Assign a value to a session variable
SET @@autocommit = 0;
SET @@session.autocommit = 1;
SET SESSION autocommit = 0;

2. User defined variables

User defined variables are divided into user variables and local variables

2.1 user variables

User variables are user-defined rather than provided by the system. Scope: valid for the current session (connection). The scope is the same as the session variables, that is, they can be applied anywhere in the current session, both inside and outside begin end

Syntax:

#Assignment operator: = or:=
#① Declare and initialize without specifying the data type. The data type will be automatically allocated according to the initialized value. You must initialize, but you can't just declare!!!
SET @Variable name=value;
SET @Variable name:=value;
SELECT @Variable name:=value;  #select assignment is generally not used. If it is to be used, it must only be matched with: = symbol!!!

#② Assignment (updating the value of a variable)
#Mode 1:
SET @Variable name=value;
SET @Variable name:=value;
SELECT @Variable name:=value;
#Method 2: use the select statement to assign the information of the table to a value
SELECT Field 1,Field 2,... INTO @Variable name 1,@Variable name 2,...
FROM surface;

#③ Use (view, compare, calculate, etc.)
SELECT @Variable name;

Note: to use with the @ symbol, initialize when the user variable must be declared

example:

#Declare and initialize
SET @name = 'john';
SET @num := 100;
SELECT @count := 1;

#Assignment (update value)
#Assign the statistics of the major table to count
SELECT COUNT(*) INTO @count
FROM major;

#use
SELECT @name;
SELECT @num;
SELECT @count;

2.2 local variables

The scope of a local variable is only valid in the begin end block that defines it. It is applied to the first sentence in begin end. It is different from user variables. When declaring, you need to specify the data type without adding the @ symbol

Syntax:

#① Declaration. select declaration is not supported
DECLARE Variable name type;
DECLARE Variable name type[ DEFAULT Value];

#② Assignment (updating the value of a variable)
#Mode 1:
SET Local variable name=value;
SET Local variable name:=value;
SELECT Local variable name:=value;
#Mode 2:
SELECT Field 1,Field 2,... INTO Local variable name 1,Local variable name 2,...
FROM surface;
	
#③ Use (view the value of the variable)
SELECT Local variable name;

example:

#Local variable declaration
DECLARE name1 VARCHAR;
DECLARE num INT DEFAULT 1;
DECLARE count1 INT;

#Assignment (update variable value)
SET name1 = 'tom';
SET num := 2;
SELECT COUNT(*) INTO count1
FROM major;

#use
SELECT name1;
SELECT num;
SELECT count1;

2.3 comparison of user variables and local variables

	Scope	Define location	grammar
User variable	Current session	Anywhere in the conversation	Add the @ symbol without specifying the type
local variable	Define it in BEGIN END	First sentence of BEGIN END	Generally, you do not need to add @, and you need to specify the type

#Case: declare two variables, sum and print
#User variable
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

#local variable
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

3, Stored procedure

Similar to the method in Java, it packages a group of logical statements that complete specific functions, exposes names, and understands batch statements

benefit

1. Improve code reusability
2. Simplify operation
3. The compilation times are reduced, the connection times with the database server are reduced, and the efficiency is improved

1. Creation of stored procedure

1.1 create syntax

CREATE PROCEDURE Stored procedure name(Parameter mode parameter name parameter type)
BEGIN
	Stored procedure body(A combination method SQL sentence)
END

If the stored procedure body has only one sentence, begin end can be omitted. The end of each sql statement in the stored procedure body must be added with a semicolon.

1.2 parameter mode

In: this parameter can be used as input, that is, the value needs to be passed in when the parameter is called
out: this parameter can be used as output, that is, it can be used as return value (the stored procedure has no return, so it can declare the variable to receive when calling)
inout: this parameter can be used as both input and output, that is, this parameter needs both incoming value and return value

1.3 end mark

MySQL ends with a semicolon by default; However, sometimes you don't want the interpreter to end when it encounters a semicolon. At this time, you can use the keyword delimiter to specify the end tag, so that the interpreter can start executing all statements when it encounters the specified tag

Syntax: delimiter end tag

Example: delimiter $, that is, tell the interpreter to execute the statement only when it encounters $

Note: do not add a semicolon, otherwise you will think you encounter $; Just execute

2. Call of stored procedure

Call syntax:

CALL Stored procedure name(Argument list);

give an example:

#Parameters calling in mode
CALL p1('value')

#Parameters for calling out mode
SET @name;
CALL p2(@name);
SELECT @name;

#Parameters for invoking inout mode
SET @name = value;
CALL p3(@name);
SELECT @name;

Create and invoke procedure instances: need to be used at the command prompt

#Set end tag
DELIMITER $

#1. Empty parameter list: insert five records into the major table
#Switch database
USE students
#Create stored procedure
CREATE PROCEDURE myp1()
BEGIN 
	INSERT INTO major VALUES(1,'java'),(2,'c++'),(3,'python'),(4,'golang'),(5,'sql');	
END $
#Call stored procedure
CALL myp1()$


#2. Create a stored procedure with in mode parameters: query the corresponding department name information according to the employee name
#Switch database
USE myemployees
#Create stored procedure
CREATE PROCEDURE myp2(IN e_name VARCHAR(20))
BEGIN
	SELECT e.`last_name`,d.`department_name`
	FROM `employees` e
	LEFT JOIN `departments` d ON e.`department_id`=d.`department_id`
	WHERE e.`last_name` = e_name;#e_name is a local variable and can be used directly in the current scope
END$
#Call stored procedure
CALL myp2('K_ing')$


#3. Create a stored procedure with multiple in mode parameters: judge whether the entered department name and location ID match
#Create stored procedure
CREATE PROCEDURE myp3(IN d_name VARCHAR(20),IN locat_id INT)
BEGIN
	DECLARE result INT DEFAULT 0;#Create a variable to store the judgment results, or create a user variable to store
	
	SELECT COUNT(*) INTO result
	FROM `departments` d
	WHERE d.`department_name` = d_name
	AND d.`location_id` = locat_id;
	
	SELECT IF(result>0,'success','fail');
END$
#Call stored procedure
CALL myp3('Adm','1700')$ #success
CALL myp3('Adm','1800')$ #fail


#4. Create a stored procedure for out mode parameters: return the corresponding department name and location according to the entered employee name_ id
#Create stored procedure
CREATE PROCEDURE myp4(IN e_name VARCHAR(20),OUT d_name VARCHAR(20),OUT locat_id INT)
BEGIN
	SELECT d.`department_name`,d.`location_id` INTO d_name,locat_id
	FROM `employees` e
	LEFT JOIN `departments` d
	ON e.`department_id`=d.`department_id`
	WHERE e.`last_name` = e_name;
END$
#Call stored procedure
SET @d_name='';#Define user variables, this line can be omitted
SET @locat_id=0;#Define user variables, this line can be omitted
CALL myp4('Abel',@d_name,@locat_id);#Call stored procedure
SELECT @d_name,@locat_id$#View results: Sal,2500


Leave a question. When you call the following one, you return two results. What variable should you use to receive it
CALL myp4('K_ing',@d_name,@locat_id);


#5. Create a stored procedure with inout mode parameters: pass in two values a and b, and finally double A and b and return
CREATE PROCEDURE myp5(INOUT a INT,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2; 
END$

#Call stored procedure
SET @m=10;
SET @n=20;
CALL myp5(@m,@n);
SELECT @m,@n$ #20,40

3. Viewing stored procedures

Syntax:

show create procedure Stored procedure name;

desc cannot be used

4. Deletion of stored procedure

Syntax:

drop procedure Stored procedure name;

Only one can be deleted at a time, such as DROP PROCEDURE p1; Yes, DROP PROCEDURE p2,p3; may not

The stored procedure has not been modified. If you want to change it, delete it first and then add a new one

4, Functions

A set of precompiled SQL statements to understand batch statements. There is only one return

1. Difference from stored procedure

Stored procedure: it can have 0 returns or multiple returns. It is suitable for batch insertion and batch update;
Function: there is only one return, which is suitable for returning a result after processing data.

2. Function creation

MySQL has a parameter SET GLOBAL log_bin_trust_function_creators=TRUE, if set to 0 (the default), users cannot create or modify storage functions unless they have SUPER permissions other than create route or alter route privileges. Setting to 0 also forces the use of the DETERMINISTIC attribute or the read SQL data or NO SQL attribute to declare restrictions on functions. If the variable is set to 1, MySQL does not impose these restrictions on the creation of storage functions.

Therefore, you need to set this parameter to 1 when creating the function

SET GLOBAL log_bin_trust_function_creators=TRUE;

Syntax:

CREATE FUNCTION Function name(Parameter name parameter type) RETURNS Return type
BEGIN
	Function body
END

Note:

Note the keywords function and returns
The return statement and return value need to be added to the function body. Although no error is reported, it is meaningless
If there is only one sentence in the function body, begin end can be omitted
Use the delimiter statement to set the end tag

3. Function call

Syntax:

SELECT Function name(parameter list);

Case:

SET GLOBAL log_bin_trust_function_creators=TRUE;
#1. Return without parameter: returns the number of employees in the company
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0; #Define local variables
	SELECT COUNT(*) INTO c
	FROM employees;
	RETURN c;
END$

SELECT myf1()$


#2. Return with participation: returns the salary of an employee according to his / her name
CREATE FUNCTION myf2(e_name VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0; #Define user variables
	SELECT e.`salary` INTO @sal
	FROM `employees` e
	WHERE e.`last_name` = e_name;
	RETURN @sal;
END$

SELECT myf2('Abel')$

The return value can be defined as both local variables and user variables, and local variables are more commonly used

4. View function

Syntax:

show create function Function name;

5. Function deletion

Syntax:

drop function Function name;

5, Process control

Sequential structure: the program is executed from top to bottom
Branch structure: the program selects and executes according to conditions, and selects one of two or more paths for execution
Loop structure: when the program meets certain conditions, it repeatedly executes a group of statements

1. Branch structure

1.1 if function

Used to judge simple situations

Syntax:

if(Expression 1,Expression 2,Expression 3)

Judgment expression 1 is true, execution expression 2 is false, and execution expression 3 is similar to ternary expression

SELECT IF(10<5,'large','Small');

SELECT last_name,commission_pct,IF(`commission_pct` IS NULL,'No bonus','Bonus')AS remarks FROM employees;

1.2 if structure

Used to judge complex situations

Syntax:

if Condition 1 then Statement 1;
elseif Condition 2 then Statement 2;
....
else sentence n;
end if;

This structure can only be used in begin end

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	IF score >90 THEN RETURN 'A';
	ELSEIF score>= 80 THEN RETURN 'B';
	ELSEIF score >= 60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END$

SELECT test_if(86)$

1.3 case structure

The case structure has two uses:

The first is similar to the effect of switch case in Java
The second effect is similar to multiple if

The case structure can be placed anywhere,

If it is placed outside begin end, it can be used as an expression in combination with other statements; (the first case below can reflect)
If it is placed in begin end, it is generally used as an independent statement

1.3.1 add a constant after when

Syntax:

case Variable or expression
when Constant 1 then Value 1 or statement 1 to display
when Constant 2 then Value 2 or statement 2 to display
...
else Value to display n Or statement n
end

/*
Case: query employee's salary. Requirements:
Department number = 30, the displayed salary is 1.1 times
 Department number = 40, the displayed salary is 1.2 times
 Department number = 50, the displayed salary is 1.3 times
 For other departments, the displayed salary is the original salary, which is calculated by department_id ascending order
*/
SELECT `salary` Original salary,`department_id`,  # The comma at the end of this line is easy to be omitted, because the case structure is placed outside begin end. As a separate expression, it needs to be separated by commas
CASE `department_id`
WHEN 30 THEN `salary`*1.1
WHEN 40 THEN `salary`*1.2
WHEN 50 THEN `salary`*1.3
ELSE `salary`
END AS New salary
FROM employees
ORDER BY `department_id` ASC;

1.3.2 add condition after when

Syntax:

case 
when Condition 1 then Value 1 or statement 1 to display
when Condition 2 then Value 2 or statement 2 to display
...
else Value to display n Or statement n
end

Compared with the previous usage, there are fewer statements after case, and constants are replaced with conditions after when

#case 
#Create A stored procedure to display the grade according to the incoming grade. For example, the incoming grade: 90-100, A, 80-90, B, 60-80, c, otherwise D

CREATE PROCEDURE test_case (IN score INT) 
BEGIN 
	CASE 
	WHEN score>=90 AND score<=100 THEN SELECT 'A'; 
	WHEN score>=80 THEN SELECT 'B';
	WHEN score>=60 THEN SELECT 'C'; 
	ELSE SELECT 'D';
	END CASE; 
END $

CALL test_case(95)$

2. Circulation structure

Position: can only be placed in begin end

All three loops can omit the name, but if a loop control statement (leave or iterate) is added to the loop, the name must be added

2.1 while

Similar to while in Java

Syntax:

[name:]while Cycle condition do
		Circulatory body
end while [[name];

2.2 repeat

It is similar to do while in Java

Syntax:

[name:]repeat
		Circulatory body
until End condition 
end repeat [[name];

2.3 loop

Dead cycle

Syntax:

[Name:] loop
		Circulatory body
end loop [[name];

2.4 leave/iterate

leave: similar to break, it is used to jump out of the loop
iterate: similar to continue, it is used to end this cycle and continue the next cycle

#1. No loop control statement was added
#Case: batch insert. Insert multiple records into the major table according to the number of times
USE students;

CREATE PROCEDURE while_test(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO major VALUES(i,'mysql');
		SET i = i+1;
	END WHILE;
END$

CALL while_test(100)$


#2. Add leave statement
#Case: batch insert. Insert multiple records into the major table according to the number of times. If the number of times is > 20, stop
TRUNCATE TABLE major$

CREATE PROCEDURE while_test1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO major VALUES(i,'mysql');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i = i+1;
	END WHILE a;
END$

CALL while_test1(100)$


#3. Add iterate statement
#Case: batch insert, insert multiple records in the major table according to the number of times, and insert only an even number of times
TRUNCATE TABLE major$

CREATE PROCEDURE while_test2(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		SET i = i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		INSERT INTO major VALUES(i,'mysql');
	END WHILE a;
END$

CALL while_test2(100)$

Tags: Database

Posted on Thu, 11 Nov 2021 20:56:33 -0500 by waol

Programmer Help

Mysql Basics: 06. View, variable, stored procedure, function and process control

1, View

1. View creation

2. View modification

3. Delete view

4. View

5. View update

6. View vs table

2, Variable

1. System variables

2. User defined variables

2.1 user variables

2.2 local variables

2.3 comparison of user variables and local variables

3, Stored procedure

1. Creation of stored procedure

1.1 create syntax

1.2 parameter mode

1.3 end mark

2. Call of stored procedure

3. Viewing stored procedures

4. Deletion of stored procedure

4, Functions

1. Difference from stored procedure

2. Function creation

3. Function call

4. View function

5. Function deletion

5, Process control

1. Branch structure

1.1 if function

1.2 if structure

1.3 case structure

1.3.1 add a constant after when

1.3.2 add condition after when

2. Circulation structure

2.1 while

2.2 repeat

2.3 loop

2.4 leave/iterate

Hot Tags