Advertisement
Google Ad Slot: content-top
MySQL Views
MySQL VIEW
A VIEW in MySQL is a virtual table based on the result of a SQL query.
It does not store data itself but presents data from one or more tables using a query.
Syntax to Create a View
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
Example: Create a Simple View
Consider a students table:
student_id |
student_name |
age |
city |
score |
|---|---|---|---|---|
1 |
John |
20 |
New York |
85 |
2 |
Alice |
22 |
Chicago |
90 |
3 |
Bob |
19 |
Boston |
75 |
Create a View to Show Students Above Age 20:
Example
CREATE VIEW view_adult_students AS
SELECT student_id, student_name, age
FROM students
WHERE age > 20;
Select Data from the View:
Example
SELECT * FROM view_adult_students;
Output:
student_id |
name |
age |
|---|---|---|
2 |
Alice |
22 |
Example: Create a View with JOIN
Consider two tables:
students: Stores student data.courses: Stores course details.
student_id |
student_name |
course_id |
|---|---|---|
1 |
John |
101 |
2 |
Alice |
102 |
course_id |
course_name |
|---|---|
101 |
Math |
102 |
Science |
Create a View to Show Student Names with Their Course Names:
Example
CREATE VIEW view_student_courses AS
SELECT students.student_name, courses.course_name
FROM students
JOIN courses ON students.course_id = courses.course_id;
Query the View:
Example
SELECT * FROM view_student_courses;
Output:
student_name |
course_name |
|---|---|
John |
Math |
Alice |
Science |
Updating a View
To update an existing view, use the CREATE OR REPLACE statement:
The following SQL adds the "City" column to the "view_adult_students" view and to show students above Age 18
Example
CREATE OR REPLACE VIEW view_adult_students AS
SELECT student_id, student_name, age, city
FROM students
WHERE age >= 18;
MySQL Dropping a View
To remove a view, use the DROP VIEW statement:
Example
DROP VIEW IF EXISTS view_adult_students;