CREATE VIEW using MySQL Workbench
Step 1: Open the MySQL Workbench
Open the MySQL workbench which you would have installed on your system.
Open MySQL Workbench
Step 2: Connect to Local Database
Add a new connection to connect to local db on your system. Click the “+” icon to create new connection. Then give the connection a name, here we gave it as “Local DB”, then enter your MySQL password by clicking “Store in Keychain”, then click “OK” to save and close the dialog boxes.
Add New DB Connection
Now, by following above steps, you will get connected to Local DB and then you will see a GUI-based MySQL editor.
Opened Local DB using MySQL workbench
Step 3: Create Database and Table
Create a database named “ViewsInMySQL”, then create table “StudentDetails” inside this database. Insert some rows in this StudentDetails table.
CREATE DATABASE ViewsInMySQL;
USE ViewsInMySQL;
CREATE TABLE StudentDetails(
sid int not null,
sname varchar(255) not null,
age int not null,
university varchar(255) not null
);
Create Schema & Tables in Workbench
INSERT INTO StudentDetails VALUES(1, "Girish", 24, "IIT Hyderabad");
INSERT INTO StudentDetails VALUES(2, "Aaditya", 24, "SRM University");
INSERT INTO StudentDetails VALUES(3, "Aashish", 23, "IIT Hyderabad");
INSERT INTO StudentDetails VALUES(4, "John", 25, "Mumbai University");
INSERT INTO StudentDetails VALUES(5, "Shruti", 24, "IIT Hyderabad");
INSERT INTO StudentDetails VALUES(6, "Leena", 25, "Mumbai University");
SELECT * FROM StudentDetails;
Data from StudentDetails Table
Step 4: Create the View
Create a view that selects the students from StudentDetails table which are from “IIT Hyderabad” university.
CREATE VIEW IITHyderabadStudentsView AS
SELECT sid, sname, age
FROM StudentDetails
WHERE university = "IIT Hyderabad";
Output: Once you created the view, you can see the view in the left panel. See the (2) in the image below.
CREATE VIEW using MySQL Workbench
Step 5: Query the View using SELECT Statement
Now you can query the data from the newly created view using SELECT statement.
SELECT * FROM IITHyderabadStudentsView;
SELECT * from View
MYSQL View
MySQL is an open-source RDBMS, i.e. Relational Database Management System which is maintained by Oracle. MySQL has support for major operating systems like Windows, MacOS, Linux, etc. MySQL makes it easy for users to interact with your relational databases, which store data in the form of tables. You can write SQL queries to get the required data from the databases using MySQL.
In this article, we will look at “Views in MySQL”, which act as virtual tables, and understand its advantages, as well as the syntax of Views for creation, updation, and deletion, with the help of awesome examples.
Contact Us