5 others
5.1 view
View: MySQL provides view function from version 5.0.1. Virtual tables (used as ordinary tables) are dynamically generated when using views. Only sql logic is saved, and query results are not saved.
Application scenario:
- Multiple places use the same query results
- The sql statements used in the query results are complex
Benefits of view:
- Reuse sql statements
- Simplify complex sql operations without knowing its query details
- Protect the data of the base table and improve security
Keywords for creating syntax | Does it actually occupy physical space | use | |
---|---|---|---|
view | create view | It takes less time and only saves sql logic | Generally used for query |
surface | create table | Save actual data | Add, delete, modify and query |
#Case: query the student name and major name surnamed Zhang (packaged into a view) CREATE VIEW v1 AS SELECT stuname, majorname FROM stuinfo s INNER JOIN major m ON s.`majorid` = m.`id`; SELECT * FROM v1 WHERE stuname LIKE 'Zhang%';
5.1.1 creating views
/* Syntax: create view View name as Query statement; */ #1. Query the employee name, department name and type of work information with a character in the name #① Create view CREATE VIEW test1 AS SELECT e.last_Name, d.department_name, j.job_title FROM employees e INNER JOIN departments d ON e.`department_id` = d.`department_id` INNER JOIN jobs j ON e.`job_id` = j.`job_id`; #② Use SELECT * FROM test1 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 test2 AS SELECT department_id dId, AVG(salary) ag FROM employees GROUP BY department_id; #② Use SELECT test2.ag, g.grade_level FROM test2 INNER JOIN job_grades g ON test2.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`; #3. Query the information of the Department with the lowest average wage #Use view SELECT * FROM test2 ORDER BY ag LIMIT 1; #4. Query the Department name and salary with the lowest average wage #Use view SELECT test2.*, d.department_name FROM test2 INNER JOIN departments d ON test2.dId = d.`department_id` ORDER BY test2.ag ASC LIMIT 1; #Solution ② CREATE VIEW test3 AS SELECT * FROM test2 ORDER BY ag LIMIT 1; SELECT d.*, t.ag FROM departments d INNER JOIN test3 t ON d.`department_id` = t.dId;
5.1.2 view modification
#Mode 1: /* Syntax: (modify if it exists, create if it does not exist) create or replace view View name as Query statement; */ SELECT * FROM test3; CREATE OR REPLACE VIEW test3 AS SELECT AVG(salary), job_id FROM employees GROUP BY job_id; #Mode 2: /* Syntax: alter view View name as Query statement; */ ALTER VIEW test3 AS SELECT * FROM employees;
5.1.3 deleting and viewing views
#3, Delete view #Syntax: drop view view name 1, view name 2; DROP VIEW test1, test2, test3; #4, View view DESC test3; SHOW CREATE VIEW test3;
5.1.4 view update
CREATE OR REPLACE VIEW myv1 AS SELECT last_name, email, salary*12*(1+IFNULL(commission_pct,0)) Annual salary FROM employees; CREATE OR REPLACE VIEW myv1 AS SELECT last_name, email FROM employees; SELECT * FROM myv1; SELECT * FROM employees; #1. Insert data (it will be inserted into the original table) INSERT INTO myv1 VALUES('Fei Zhang','zf@qq.com'); #2. Modify (the original table is also updated) UPDATE myv1 SET last_name='zhang wuji' WHERE last_name='Fei Zhang'; #3. Delete (the original table is also deleted) DELETE FROM myv1 WHERE last_name='zhang wuji';
- Views with the following characteristics are not allowed to be updated
#1. sql statements containing the following keywords: grouping function, distinct, group by, having, union or union all CREATE OR REPLACE VIEW myv1 AS SELECT MAX(salary) m, department_id FROM employees GROUP BY department_id; SELECT * FROM myv1; #Update (not feasible, not allowed) UPDATE myv1 SET m=9000 WHERE department_id = 10; #2. Constant view CREATE OR REPLACE VIEW myv2 AS SELECT 'john' NAME; SELECT * FROM myv2; #Update (not feasible, not allowed) UPDATE myv2 SET NAME='lucy'; #3. Sub query included in select CREATE OR REPLACE VIEW myv3 AS SELECT ( SELECT MAX(salary) FROM employees ) Maximum wage; SELECT * FROM myv3; #Update (not feasible) UPDATE myv3 SET Maximum wage=10000; #4. Join (including all connections) CREATE OR REPLACE VIEW myv4 AS SELECT last_name, department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; SELECT * FROM myv4; #Update (feasible), insert, etc. not feasible UPDATE myv4 SET last_name='Fei Zhang' WHERE last_name='whalen'; INSERT INTO myv4 VALUES('Chen Zhen','xxx'); #5.FROM a view that cannot be updated CREATE OR REPLACE VIEW myv5 AS SELECT * FROM myv1; SELECT * FROM myv5; #to update UPDATE myv5 SET m=100000 WHERE department_id=60; #6. The subquery of the where clause references the table in the FROM clause CREATE OR REPLACE VIEW myv6 AS SELECT last_name, email, salary FROM employees WHERE employee_id IN( SELECT manager_id FROM employees WHERE manager_id IS NOT NULL ); SELECT * FROM myv6; #Update (not feasible) UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
5.2 variables
Classification:
- System variables: global variables, session variables
- User defined variables: user variables, local variables
5.2.1 system variables
- Note: variables are provided by the system, not user-defined, and belong to the server level
Syntax used:
#1. View all system variables show global|[session] variables; #Global global, session session #2. View some system variables that meet the conditions show global|[session]variables like '%char%'; #3. View the value of a specified system variable select @@global|[session].System variable name; #4. Assign a value to a system variable Mode 1: set global|[session] System variable name = value; Mode 2: set @@global|[session].System variable name = value;
be careful:
- If it is a global level, you need to add global
- If it is a session level, you need to add a session (if you don't write, the default session)
1. Global variables
Scope: each time the server starts, it will assign initial values to all variables, which is valid for all sessions (connections), but cannot be restarted across.
#① View all global variables SHOW GLOBAL VARIABLES; #② View partial global variables SHOW GLOBAL VARIABLES LIKE '%char%'; #③ Finds the value of the specified global variable SELECT @@global.autocommit; #Non 0 table true SELECT @@global.tx_isolation; #④ Assigns a value to a specified global variable #Mode 1: SET @@global.autocommit=0; #Mode 2: SET GLOBAL autocommit=1;
2. Session variables
Scope: valid only for the current session (connection)
#When used, session can be omitted #① View all session variables SHOW VARIABLES; SHOW SESSION VARIABLES; #② View session variables for section SHOW VARIABLES LIKE '%char%'; SHOW SESSION VARIABLES LIKE '%char%'; #③ Finds the value of the specified session variable SELECT @@tx_isolation; SELECT @@session.tx_isolation; #④ Assign a value to a specified session variable #Mode 1: SET @@session.tx_isolation='read-uncommitted'; SET @@tx_isolation='read-uncommitted'; #Mode 2: SET SESSION tx_isolation='read-committed'; SET tx_isolation='read-committed';
5.2.2 user defined variables
- Note: variables are user-defined, not provided by the system
- Use steps: declaration, assignment, use (view, comparison, operation, etc.)
1. User variables
-
Scope: valid for the current session (connection), the same as the scope of the session variable
-
Apply anywhere
Syntax:
Assignment operator: = or := #① Declare and initialize SET @User variable name=value; SET @User variable name:=value; SELECT @User variable name:=value; #② Assignment (updating the value of the user variable) Method 1: through SET or SELECT SET @User variable name=value; SET @User variable name:=value; SELECT @User variable name:=value; Mode 2: Pass SELECT INTO SELECT field INTO @Variable name FROM surface #③ Use (view the value of the user variable) SELECT @User variable name;
Case:
#Declare and initialize SET @name='john'; SET @name=100; #Feasible weak type #assignment SET @count=1; SELECT COUNT(*) INTO @count FROM employees; #see SELECT @count;
2. Local variables
- Scope: valid only in the begin end where it is defined
- The first sentence applied in begin end!!!
#① Declaration (can not be initialized) DECLARE Variable name type; DECLARE Variable name type DEFAULT value; #② Assignment Method 1: through SET or SELECT SET Local variable name=value; SET Local variable name:=value; SELECT @Local variable name:=value; Mode 2: Pass SELECT INTO SELECT field INTO Local variable name FROM surface; #③ Use SELECT Local variable name;
Case:
#Case: declare two variables and assign initial values, sum and print #1. User variables SET @m=1; SET @n=2; SET @sum = @m + @n; SELECT @sum; #2. Local variables (local variables are not allowed to be written outside) DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 2; DECLARE SUM INT; SET SUM = m+n; SELECT SUM;
3. Comparison of user variables and local variables
Scope | Defined and used locations | grammar | |
---|---|---|---|
User variable | Current session | Anywhere in the conversation | Must add@ |
local variable | In BEGIN END | Only the first sentence in BEGIN END | Generally @, the type shall be limited |