MySQL Tutorial 8th Chapter View

Purpose of creating a view

Simple Hidden Data complexity

Security can be authorized on view

Data independence can block the impact of table structure changes on users, such as adding columns, changing column names

Create a View

1. Create a single-table view

The following views show the student name, social security number, and class for the Java class.

CREATE VIEW vjavastudent

As

SELECT sname name, CardID ID number, class class from ' Tstudent ' where class= ' JAVA '

Query view

SELECT * from Vjavastudent;

Select name, ID number from vjavastudent where name = ' Baek ampoule '

2. Create a view on multiple tables

Create a view showing student number name ID number class score

Create VIEW Vstudentinfo

As

Select A.studentid number, sname name, CardID ID number, class class, Subjectname subject, Mark score from ' Tstudent ' a join ' Tscore ' B on a. ' Student ID ' =b. ' StudentID ' join ' Tsubject ' C on B. ' Subjectid ' =c. ' Subjectid '

Query view

SELECT * from ' Vstudentinfo '

Find top scores for each section

Select Account, max (score) highest score from ' Vstudentinfo ' GROUP by account

3. Create a View

The view shows the student's name score and subject

CREATE VIEW ' Studentmark '

As

(

SELECT a.studentid number, sname name, Mark Score, subjectname discipline

From Tstudent a joins Tscore B on A.studentid=b.studentid

JOIN tsubject C on B.subjectid=c.subjectid

);

Query the View

SELECT * FROM Studentmark

4. Row and column conversion view

Create a row and column transformation view with the above view as a table

CREATE VIEW Vmark

As

(

SELECT study number, name,

AVG (case discipline when ' computer network ' then fractional END) computer network,

AVG (case discipline when ' Java development ' then fractional END) Java development,

AVG (case discipline when ' data structure ' then fractional END) data structure

From ' Studentmark '

GROUP by study number

);

Query Vmark View

SELECT * FROM Vmark

View View

5. View basic information

DESCRIBE ' Vstudentinfo '

, double-click the Vstudentinfo view to view the created view

6. View details in the Views table

Connect to the INFORMATION_SCHEMA database using SQL admin

Enter select * from Information_schema.views;

Modify a View

1. Modify the view with Create or replace

Add a list of emails to an existing view

Create OR REPLACE VIEW vstudentinfo

As

Select A.studentid, sname name, CardID ID number, email address, class class, Subjectname subject, Mark score from ' Tstudent ' a join ' Tscore ' B on a . ' StudentID ' =b. ' StudentID ' join ' Tsubject ' C on B. ' Subjectid ' =c. ' Subjectid '

2. Modify the view with alter

Remove the ID number column for an existing view

ALTER VIEW Vstudentinfo

As

Select A.studentid number, sname name, email address, class class, Subjectname subject, Mark score from

' Tstudent ' A join ' Tscore ' B on a. ' StudentID ' =b. ' StudentID ' joins ' Tsubject ' C on B. ' Subjectid ' =c. ' Subjectid '

Changing data through a view

Inserting, updating, and deleting data in a table with a view is updated on the modified base table

3. Changing data through a view

SELECT * from ' Vstudentinfo '

Add 10 points to the cloning data structure

Update ' Vstudentinfo ' Set score = score +10 where name = ' cloning ' and account = ' data structure '

View Results

SELECT * from ' Vstudentinfo '

4. Inserting data through a view

The following view can insert data

CREATE VIEW Vjava

As

Select StudentID number, sname name, sex sex, email address, class class from ' Tstudent ' where class= ' JAVA '

Inserting records through a view

INSERT Vjava values (' 00025 ', ' Yao ', ' Male ', ' [email protected] ', ' JAVA ')

SELECT * FROM Vjava

5. Deleting data from a view

Delete from Vjava where study number = ' 00025 '

SELECT * FROM Vjava

Delete the created view

6. View the Created view

7. Delete a view

Drop View ' Vjava '

Press F5 to see that the Vjava view has been deleted

8. View the syntax of the view

Views that cannot be updated

The view does not contain columns that are defined as non-empty in the base table

A mathematical expression is used in the field list after defining the view's SELECT statement

Use aggregate functions in fields after defining the view's SELECT statement

The distinct union top group by or having is used in the SELECT statement that defines the view

Work under Class

Create a View Studentmark

Select Sname name, Subjectname subject, Mark score from Tstudent a join Tscore b

On A.studentid=b.studentid joins Tsubject C on B.subjectid=c.subjectid

Create a view Vmark and convert the Studentmark to a row and column

Name computer network Java development data structure

Han Ligang 89 78 89

Wang Ruis 90 78 76

MySQL Tutorial 8th Chapter View