Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 255 of 547
What are the different privileges required for using views?
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 ...
Read MoreHow can we see MySQL temporary tables in the list of tables?
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 MoreHow can we create a MySQL view based on another existing view?
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 MoreHow can I delete MySQL temporary table?
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 MoreHow can we create MySQL views with column list?
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 MoreHow can we use SIGNAL statement with MySQL triggers?
Actually, MySQL SIGNAL statement is an error handling mechanism for handling unexpected occurrences and a graceful exit from the application if need to be. Basically, it provides error information to the handler. Its basic syntax would be as follows −SIGNAL SQLSTATE | condition_value [SET signal_information_item = value_1, [, signal_information_item] = value_2, etc;]Here, the SIGNAL keyword is an SQLSTATE value or a condition name declared by a DECLARE CONDITION statement. The SIGNAL statement must always specify an SQLSTATE value or a named condition that defined with an SQLSTATE value. The SQLSTATE value for a The SIGNAL statement consists of a five-character ...
Read MoreHow can MySQL handle the errors during trigger execution?
Suppose if an error occurs during trigger execution then MySQL can handle it as follows −If a BEFORE trigger fails, the operation on the corresponding row is not performed.A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.An AFTER trigger is executed only if any BEFORE triggers and the row operation execute successfully.An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.For transactional tables, failure of a statement should cause a rollback of all changes performed by the statement. ...
Read MoreHow BEFORE UPDATE triggers can be used to emulate CHECK CONSTRAINTnfor updating values in the table?
As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car (number char(9)); Query OK, 0 rows affected (0.32 sec)Creating BEFORE UPDATE trigger to emulate CHECK CONSTRAINT for updating the values −Now, suppose if we will try to update the table ...
Read MoreWhat are the benefits of using MySQL views as compared to selecting data directly from MySQL base tables?
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 MoreWhat are the prerequisites before starting writing and using MySQL views?
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. ...
Read More