I Common database objects

object	describe
TABLE	A logical unit that stores data in the form of rows and columns
data dictionary	System table, a table that stores database related information. The data of the system table is usually maintained by the database system. Programmers should not modify it, but can only view it
CONSTRAINT	Rules for data verification, which are used to ensure data integrity
VIEW (VIEW)	A logical display of data in one or more datasheets. Views do not store data
INDEX (INDEX)	Used to improve query performance, equivalent to the directory of books
Stored PROCEDURE	It is used to complete a complete business process. There is no return value, but multiple values can be passed to the calling environment through outgoing parameters
Storage FUNCTION	It is used to complete a specific calculation and has a return value
TRIGGER	It is equivalent to an event listener. When a specific event occurs in the database, the trigger is triggered to complete the corresponding processing

II VIEW (VIEW)

I Why use views?

1. Help us use some tables instead of all tables
2. Make different query views for different users

II Understanding of views

1. View is a kind of virtual table, which has no data and occupies little memory space.
2. Views are built on the basis of existing tables. These tables on which views are built are called base tables.
3. View creation and deletion only affect the view itself, not the corresponding base table. However, when you add, delete or modify the data in the view, the data in the base table will change the same, and vice versa.
4. The statement that provides data content to the view is a SELECT statement, which can be understood as a stored SELECT statement

III Create view

Syntax:

CREATE VIEW view name [field list] AS query statement

1. Embed subquery in create view statement

CREATE VIEW view name
AS query statement

2. How to create a view

2.1 for single meter

CREATE VIEW vu_emp1 
		AS
		SELECT employee_id,last_name,salary
		FROM emps;

Case 1: query the contents in the base table
The alias in the query statement will be used as the field name in the view
Mode 1

CREATE VIEW vu_emp2
		AS
		SELECT employee_id emp_id,last_name lname,salary
		FROM emps
		WHERE salary>8000;

Mode 2

CREATE VIEW vu_emp3(emp_id,name,monthly_sal)
		AS
		SELECT employee_id,last_name,salary
		FROM emps
		WHERE salary>8000;
		

Case 2: query the content that does not exist in the base table (calculated from the content in the base table)
Query the average salary as a new view (the element 'average salary' that does not exist in the original table)

CREATE VIEW vu_emp_sal
		AS 
		SELECT department_id,AVG(salary) avf_sal
		FROM emps
		WHERE department_id IS NOT NULL
		GROUP BY department_id;

2.2 needle for multi meter

CREATE VIEW vu_emp_dept 
		AS
		SELECT e.employee_id,e.department_id,d.department_name
		FORM emps e JOIN depts d ON e.`department_id`=d.`department_id`;

Formatting data using views

CREATE VIEW vu_emp_dept1
		AS
		SELECT CONCAT(e.last_name,'(',d.department_name,')') emp_info
		FROM emps e JOIN depts d ON e.`department_id`=d.`department_id`;

2.3
Create a view from a view

CREATE VIEW emp4
		AS
		SELECT employee_id,last_name
FROM vu_emp1;

IV view a chart

one.View the table object and view object of the database

SHOW TABLES;

II View the structure of the view

DESC vu_emp1;

III View the attribute information of the view

SHOW TABLE STATUS LIKE 'vu_emp1';

IV View the detailed definition information of the view

SHOW CREATE VIEW vu_emp1;

V Update data in view

5.1 data in the view that can be modified
Updating the view data will cause the data in the base table to be modified
Updating the data of the base table will also cause the data in the view to be modified

5.2 the situation in the view that cannot be modified is
There is no one-to-one relationship in the base table and cannot be modified (that is, the new content generated by the calculation of the base table when creating the view)

Vi Modify and delete views

I Modify view

Mode 1:

CREATE OR REPLACE VIEW vu_emp1
	AS
	SELECT employee_id,last_name,salary,email
	FROM emps
	WHERE salary>7000;

Mode 2:

ALTER VIEW vu_emp1
	AS
	SELECT employee_id,last_name,salary,email
	FROM emps
	WHERE salary>7000;

II Delete view

DROP VIEW IF EXISTS vu_emp4;

VII summary

I View advantages

1. Simple operation
2. Reduce data redundancy
3. Data security
4. Adapt to flexible needs
5. Be able to decompose complex query logic

II View disadvantages

1. If the structure of the actual data table changes, the relevant views need to be maintained in time, which is complex.
2. In the actual project, if there are too many views, it will lead to the cost of database maintenance.