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
MySQL Articles
Page 67 of 355
How can we see the metadata of a view(s) stored in a particular MySQL database?
The INFORMATION_SCHEMA database has a VIEWS table that contains view metadata i.e. data about views. To illustrate it we are taking the example of a view named ‘Info’.ExampleThe following query will show the metadata of a view named ‘Info’ −mysql> SELECT * from INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'Info' AND TABLE_SCHEMA = 'query'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: query TABLE_NAME: info VIEW_DEFINITION:select`query`.`student_info`.`id`AS`ID`, `query`.`student_info`.`Name` AS `NAME`, `query`.`student_info`.`Subject` AS `SUBJECT`, `query`.` student_info`.`Address` AS `ADDRESS` from `query`.`student_info` CHECK_OPTION: NONE IS_UPDATABLE: YES ...
Read MoreHow can we get the structure of a MySQL view as we can get the structure of a MySQL table?
As we know that views are a type of virtual tables and are a composition of tables too hence we can use the same query to get the structure of a view which we use to get the structure of a table. In other words, we can use DESCRIBE statement to get the structure of a MySQL view. Its syntax would be as follows −SyntaxDESCRIBE view_name;Here, view_name is the name of the view of which we want to get the structure.ExampleSuppose we want to get the structure of a view named ‘Info’ then it can be done with the help, of the following query ...
Read MoreHow can we list all the columns of a MySQL view as we can list the columns of a MySQL table?
As we know that views are a type of virtual tables and are the composition of tables too hence we can use the same query to list all the columns of a MySQL view as we can list the columns of a MySQL table. In other words, we can use SHOW FULL COLUMNS statement to get the structure of a MySQL view. Its syntax would be as follows −SyntaxSHOW FULL COLUMNS FROM View_name;Here view_name is the name of the view from which we want to get the list of columns.ExampleSuppose if we want to get a list of columns of ...
Read MoreHow can we update any value in MySQL view as we can update the values in MySQL table?
As we know that with the help of UPDATE statement we can update the values in MySQL table and in the similar way we can update the values in MySQL views. The syntax of UPDATE statement would be the same except at the place of table name we have to provide the name of the view. We are taking the data as follows from a view named ‘Info’ to illustrate the above concept −mysql> Select * from Info; +------+---------+------------+ | Id | Name | Subject | +------+---------+------------+ | 101 | YashPal ...
Read MoreAfter updating any value in a particular view, will MySQL updates the same in the base table and its associated views (if any)?
Yes, MySQL will update the value, if it is updated in a view, in the base table as well as in its associated views. To illustrate it we are taking the example of table Student_info having the following data −mysql> Select * from student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 125 | Raman | Shimla | Computers | | NULL | Ram | Jhansi ...
Read MoreHow can we get the total number of rows affected by MySQL query?
MySQL ROW_COUNT() can be used to get the total number of rows affected by MySQL query. To illustrate it we are creating a procedure with the help of which we can insert records in a table and it will show us how many rows have been affected.Examplemysql> Delimiter // mysql> CREATE PROCEDURE `query`.`row_cnt` (IN command VarChar(60000)) -> BEGIN -> SET @query = command; -> PREPARE stmt FROM @query; -> EXECUTE stmt; -> SELECT ROW_COUNT() AS 'Affected rows'; -> END // Query OK, 0 rows ...
Read MoreHow can we use MySQL REPLACE statement to prevent of insertion of duplicate data?
We can use the REPLACE statement while inserting the data to prevent the insertion of duplicate data. If we will use the REPLACE command rather than the INSERT command then if the record is new, it is inserted just as with INSERT else if it is a duplicate, the new record replaces the old one.SyntaxREPLACE INTO table_name(…)Here, table_name is the name of the table in which we want to insert the values.ExampleIn this example we will insert the data with the help of REPLACE statement as follows −mysql> REPLACE INTO person_tbl (last_name, first_name) -> VALUES( 'Ajay', 'Kumar'); Query OK, ...
Read MoreWhat 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 More