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