How can we create MySQL views with column list?

MySQLMySQLi Database

As we know that while creating a view, providing the list of columns is optional. But if we are providing the name of the columns while creating the view then the number of names in the list of columns must be the same as the number of columns retrieved by the SELECT statement.

Example

The following example will illustrate by creating the views with column list −

mysql> Select * from student_detail;
+-----------+-------------+------------+
| Studentid | StudentName | address    |
+-----------+-------------+------------+
| 100       | Gaurav      | Delhi      |
| 101       | Raman       | Shimla     |
| 103       | Rahul       | Jaipur     |
| 104       | Ram         | Chandigarh |
| 105       | Mohan       | Chandigarh |
+-----------+-------------+------------+
5 rows in set (0.17 sec)

mysql> Create view View_student_detail_columns AS SELECT Studentid,
StudentName FROM Student_Detail;
Query OK, 0 rows affected (0.10 sec)

In the above, we have given two columns and after running the query to get data from view it will show only the column names we have given while creating it.

mysql> Select * from View_Student_detail_columns;
+-----------+-------------+
| Studentid | StudentName |
+-----------+-------------+
| 100       | Gaurav      |
| 101       | Raman       |
| 103       | Rahul       |
| 104       | Ram         |
| 105       | Mohan       |
+-----------+-------------+
5 rows in set (0.08 sec)
raja
Published on 22-Feb-2018 14:12:36
Advertisements