[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 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;