MySQL Views: Definition, Syntax, and Examples

Tutorial

MYSQL

MySQL Tutorial

MySQL Introduction

MySQL COUNT, AVG, SUM

MySQL Database

❮ Previous

Next ❯

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;

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.