As we know that MySQL temporary table would be deleted if the current session is terminated. But of still in between the session we want to delete the temporary table than with the help of the DROP statement we can delete the temporary table. It can be understood with the help of the following example −ExampleIn this example, we are deleting the temporary table named ‘SalesSummary’ −mysql> DROP TABLE SalesSummary; Query OK, 0 rows affected (0.00 sec)The above query will delete the table and it can be confirmed from the query below −mysql> Select * from SalesSummary; ERROR 1146 (42S02): ... Read More

Temporary table would be deleted if MySQL session terminates. After login again, on issuing the SELECT command we will find no data available in the database. Even our temporary table will not exist.

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.ExampleThe following example will illustrate by creating the views with column list −mysql> Select * from student_detail; +-----------+-------------+------------+ | Studentid | StudentName | address    | +-----------+-------------+------------+ | 100       | Gaurav      | Delhi      | | 101       | Raman ... Read More

In MySQL, we can create a view that is based on another existing view. To make it understand we are having the view ‘Info’ with the following data −mysql> Create view info AS Select Id, Name, Subject FROM student_info; Query OK, 0 rows affected (0.11 sec) mysql> Select * from Info; +------+---------+------------+ | Id | Name | Subject | +------+---------+------------+ | 101 | YashPal | History | | 105 | Gaurav | Literature | | 125 | Raman | Computers | | NULL | Ram | Computers | +------+---------+------------+ 4 rows in set (0.00 sec)Now, with the help of ... Read More

As we know that we can see the list of tables in a database with the help of SHOW TABLES statement. But MySQL temporary tables are not stored in this list or in other words we can say that we cannot see the temporary tables with the help of SHOW TABLES statement. To illustrate it we are using the following example −ExampleIn this example, we are trying to get the temporary table named ‘SalesSummary’ from SHOW TABLES statement as follows −mysql> SHOW TABLES LIKE '%Sales%'; Empty set (0.00 sec) mysql> SHOW TABLES LIKE '%SalesSummary%'; Empty set (0.00 sec)The above ... Read More

While creating a view, providing the list of columns is optional. The following example will illustrate by creating the views without any column list −mysql> Select * from student_detail; +-----------+-------------+------------+ | Studentid | StudentName | address    | +-----------+-------------+------------+ |       100 | Gaurav      | Delhi      | |       101 | Raman       | Shimla     | |       103 | Rahul       | Jaipur     | |       104 | Ram         | Chandigarh | |     ... Read More

Following privileges are required for a different kind of CREATE, REPLACE, DROP, ACCESS, UPDATE etc. of usage of views − CREATE VIEW Privilege − CREATE VIEW privilege is required to create a view. Along with this we must have sufficient privileges, like SELECT, INSERT or UPDATE, for accessing the tables to which the view definition refers. DROP VIEW Privilege − We require DROP VIEW privileges for using OR REPLACE clause, DROP VIEW statement and also for using ALTER VIEW statement. SELECT Privilege − We must have SELECT privileges for selecting from a view. INSERT, DELETE or UPDATE Privileges − Actually for an updateable view ... Read More

MySQL VersionAs we know that MySQL 5 introduced views, hence, first of all, we need to check for the version of MySQL before starting writing and using stored procedures. It can be done with the following query −mysql> Select VERSION(); +-----------+ | VERSION() | +-----------+ | 5.7.20    | +-----------+ 1 row in set (0.10 sec)Privileges for current userActually CREATE VIEW statement requires the CREATE VIEW privilege. Privileges for the current user can be checked with the following query −mysql> SHOW PRIVILEGESSelecting a databaseBefore creating a view we must have to select a database from the available databases. It can ... Read More

In spite of various benefits of using views there are following limitations on using MySQL views − Can’t create an index of views − In MySQL, we cannot create an index on views. It is because indexes are not utilized when we query data against the views. MySQL invalidates the view − Suppose, if we drop or rename tables to which a view references, rather than issuing an error MySQL invalidate the view. We can use the CHECK TABLE statement to check whether the view is valid or not. MySQL views cannot be updateable in some situations − Actually, the simple view can ... Read More

As we know that views are definitions built on the top of other tables or views and stored in the database. Followings are benefits of using MySQL views as compared to selecting data directly from MySQL base tablesSimplify data accessThe use of views simplifies the data access because of the following reasons −A view can be used to perform a calculation and display its result. For example, a view definition that invokes aggregate functions can be used to display a summary.With the help of views, we can select a restricted set of rows by means of an appropriate WHERE clause ... Read More