MySQL - CREATE VIEW

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);

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.