SQL Learning to create, modify, and delete views using the command method

1. Create a View

(1) General format:

Create VIEW View name

[WITH Encryption]

As

SELECT statement

[WITH CHECK option]

(2) Define a single source table view:

Establish a view of the student's number, name, gender and age in the Department of Information Management

Create View is_student (Studentid,studentname,sex,birth)

As

Select Studentid,studentname,sex,getdate ()-birth

From student

where sdept = ' Information Management Department '

(3) Define a multi-source table view:

To establish a view of the student's number, name, and result of the C001 course in the Department of Information Management

Create View v_is_s1 (Studentid,studentname,grade)

As

Select S.studentid,studentname,grade

From student S

Join grade G on G.studentid = S.studentid

where sdept = ' Information Management Department ' and G.courseid = ' C001 '

(4) Define a new view on an existing view:

Create a view of the student's number, name, and age in the information management department on the view set up on (2) with age less than 20

Create View Is_student_sage (Studentid,studentname,birth)

As

Select Studentid,studentname,getdate ()-birth

From Is_student

where GETDATE ()-birth >20

(5) A view with an expression

Defines a view of a student's year of birth, including number, name, and year of birth

Create View bt_s (Studentid,studentname,birth)

As

Select Studentid,studentname,getdate ()-birth

From student

(6) Views with grouped statistics:

Define a view of each student's academic number and average score

Create View S_g (Studentid,grade)

As

Select Studentid,avg (Grade)

From grade

GROUP BY StudentID

2. Modify the View:

Alter VIEW view name

As

Query statements

Revised to count each student's test scores and the total number of elective courses.

Alter View S_g (Studentid,grade,count_coursename)

As

Select Studentid,avg (Grade), COUNT (*)

From grade

GROUP BY StudentID

3. Delete the view:

Drop View Name

This article from "A Growing small Tree" blog, reproduced please contact the author!

SQL Learning to create, modify, and delete views using the command method