mysql

Khóa học miễn phí MySQL – Create Views nhận dự án làm có lương

Posted on by user

MySQL – CREATE VIEW

Table of content

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.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user