- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - CREATE VIEW
MySQL views are a type of virtual tables. They are stored in the database with an associated name. They allow users to do the following −
- Structure data in a way that users or classes of users find natural or intuitive.
- Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
- Summarize data from various tables which can be used to generate reports.
A view can be created from one or more tables, containing either all or selective rows from them. Unless indexed, a view does not exist in a database.
MySQL Create View Statement
Creating a view is simply creating a virtual table using a query. A view is an SQL statement that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query.
Syntax
Following is the syntax of the CREATE VIEW Statement −
CREATE VIEW view_name AS select_statements FROM table_name;
Example
Assume we have created a table using the SELECT statement as shown below −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(15) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(10, 2), PRIMARY KEY(ID) );
Let us insert 7 records in the above created table −
INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', '32', 'Ahmedabad', 2000), (2, 'Khilan', '25', 'Delhi', 1500), (3, 'Kaushik', '23', 'Kota', 2500), (4, 'Chaitali', '26', 'Mumbai', 6500), (5, 'Hardik','27', 'Bhopal', 8500), (6, 'Komal', '22', 'MP', 9000), (7, 'Muffy', '24', 'Indore', 5500);
Following query creates a view based on the above create table −
CREATE VIEW first_view AS SELECT * FROM CUSTOMERS;
Verification
You can verify the contents of a view using the select query as shown below −
SELECT * FROM first_view;
The view will be created as follows −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
With REPLACE and IF NOT EXISTS Clauses
Usually, if you try to create a view with the name same as an existing view an error will be generated as shown as −
CREATE VIEW first_view AS SELECT * FROM CUSTOMERS;
As the view already exists, following error is raised −
ERROR 1050 (42S01): Table 'first_view' already exists
So, you can use the REPLACE clause along with CREATE VIEW to replace the existing view.
CREATE OR REPLACE VIEW first_view AS SELECT * FROM CUSTOMERS;
With WHERE Clause
We can also create a view using the where clause as shown below −
CREATE VIEW test_view AS SELECT * FROM CUSTOMERS WHERE SALARY>3000;
Following are the contents of the above created view −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
The With Check Option
The WITH CHECK OPTION is an option used with CREATE VIEW statement. The purpose of this WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) in the query. If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.
Syntax
Following is the syntax −
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WITH CHECK OPTION;
Example
In the following example, we are creating a view using CREATE VIEW statement along with the WITH CHECK OPTION −
CREATE VIEW NEW_VIEW AS SELECT * FROM CUSTOMERS WHERE NAME IS NOT NULL WITH CHECK OPTION;
The view is created as follows −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Creating a MySQL View Using Client Program
In addition to creating a view in MySQL Database using the SQL queries, we can also do so using a client program.
Syntax
Following are the syntaxes of the Create View into MySQL in various programming languages −
The MySQL PHP connector mysqli provides a function named query() to execute a CREATE VIEW query in the MySQL database.
$sql="CREATE VIEW views_name AS SELECT col_1, col_2, col_3 FROM table_name"; $mysqli->query($sql);
The MySQL NodeJS connector mysql2 provides a function named query() to execute a CREATE VIEW query in the MySQL database.
sql="CREATE VIEW view_name AS Select_statements FROM table"; con.query(sql);
We can use the JDBC type 4 driver to communicate to MySQL using Java. It provides a function named execute() to execute a CREATE VIEW query in the MySQL database.
String sql="CREATE VIEW views_name AS SELECT col_1, col_2, col_3 FROM table_name"; statement.execute(sql);
The MySQL Connector/Python provides a function named execute() to execute a CREATE VIEW query in the MySQL database.
create_view_query = "CREATE VIEW view_name AS Select_statements FROM table" cursorObj.execute(create_view_query);
Example
Following are the implementations of this operation in various programming languages −
$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $dbname = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } // printf('Connected successfully.
'); // CREATING A VIEW; $sql = "CREATE VIEW first_view AS SELECT tutorial_id, tutorial_title, tutorial_author FROM clone_table"; if ($mysqli->query($sql)) { printf("View created successfully!.
"); } if ($mysqli->errno) { printf("View could not be created!.
", $mysqli->error); } $mysqli->close();
Output
The output obtained is as follows −
View created successfully!.
var mysql = require('mysql2'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); sql = "USE TUTORIALS" con.query(sql); sql = "CREATE TABLE dispatches_data(ProductName VARCHAR(255),CustomerName VARCHAR(255),DispatchTimeStamp timestamp,Price INT,Location VARCHAR(255));" con.query(sql); sql = "insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad'),('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam'),('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada'),('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai'),('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');" con.query(sql); sql = "Create view first_view AS SELECT * FROM dispatches_data;" con.query(sql); sql = "SELECT * FROM first_view;" con.query(sql, function(err, result){ if (err) throw err console.log(result); }); });
Output
The output produced is as follows −
Connected! -------------------------- [ { ProductName: 'Key-Board', CustomerName: 'Raja', DispatchTimeStamp: 2019-05-04T09:32:45.000Z, Price: 7000, Location: 'Hyderabad' }, { ProductName: 'Earphones', CustomerName: 'Roja', DispatchTimeStamp: 2019-06-26T08:43:12.000Z, Price: 2000, Location: 'Vishakhapatnam' }, { ProductName: 'Mouse', CustomerName: 'Puja', DispatchTimeStamp: 2019-12-07T02:20:37.000Z, Price: 3000, Location: 'Vijayawada' }, { ProductName: 'Mobile', CustomerName: 'Vanaja', DispatchTimeStamp: 2018-03-21T10:30:45.000Z, Price: 9000, Location: 'Chennai' }, { ProductName: 'Headset', CustomerName: 'Jalaja', DispatchTimeStamp: 2018-12-30T05:19:27.000Z, Price: 6000, Location: 'Goa' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class CreateView { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Create View..... String sql = "CREATE VIEW first_view AS SELECT tutorial_id, tutorial_title, tutorial_author FROM tutorials_tbl"; statement.execute(sql); System.out.println("View created Successfully...!"); ResultSet resultSet = statement.executeQuery("SELECT * FROM first_view"); while (resultSet.next()) { System.out.print(resultSet.getString(1)+ " " +resultSet.getString(2)); System.out.println(); } connection.close(); } catch (Exception e) { System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! View created Successfully...! 1 Learn PHP 2 Learn MySQL 3 JAVA Tutorial 4 Python Tutorial 5 Hadoop Tutorial
import mysql.connector #establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) cursorObj = connection.cursor() create_view_query = """ CREATE VIEW tutorial_view AS SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl WHERE submission_date >= '2023-01-01' """ cursorObj.execute(create_view_query) connection.commit() print("View created successfully.") cursorObj.close() connection.close()
Output
Following is the output of the above code −
View created successfully.
To Continue Learning Please Login
Login with Google