[MySQL database] Chapter 5: views and variables

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 syntaxDoes it actually occupy physical spaceuse
viewcreate viewIt takes less time and only saves sql logicGenerally used for query
surfacecreate tableSave actual dataAdd, 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

ScopeDefined and used locationsgrammar
User variableCurrent sessionAnywhere in the conversationMust add@
local variableIn BEGIN ENDOnly the first sentence in BEGIN ENDGenerally @, the type shall be limited

Tags: Database MySQL SQL

Posted on Wed, 27 Oct 2021 20:02:58 -0400 by Mohit_Prog