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 66 of 355
What are the different types of MySQL GENERATED COLUMNS?
We have two types of MYSQL generated columns as follows −VIRTUAL GENERATED COLUMNAs the name suggests, this kind of generated column will not take any disk space. It can be generated with or without using the keyword ‘virtual’. To understand we are illustrating it in the following example −Examplemysql> Create table triangle(SideA DOUBLE, SideB DOUBLE, SideC DOUBLE AS (SQRT(SideA * SideB + SideB * SideB))); Query OK, 0 rows affected (0.44 sec) mysql> Describe Triangle; +-------+--------+------+-----+---------+-------------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------+------+-----+---------+-------------------+ | SideA ...
Read MoreHow can we create MySQL view by selecting data based on pattern matching from base table?
MySQL LIKE operator is used to select data based on pattern matching. Similarly, we can use LIKE operator with views to select particular data based on pattern matching from the base table. To understand this concept we are using the base table ‘student_info’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | ...
Read MoreHow can we use logical operators while creating MySQL views?
MySQL views can be created by using logical operators like AND, OR, and NOT. It can be illustrated with the help of following examples −Views with AND operatorAs we know that logical AND operator compares two expressions and returns true if both the expressions are true. In the following example, we are creating a view which has the conditions based on ‘AND’ operator.ExampleThe base table is Student_info having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History ...
Read MoreHow can we create a MySQL view by selecting some range of values from a base table?
As we know that MySQL BETWEEN operator can be used to select values from some range of values. We can use BETWEEN operator along with views to select some range of values from the base table. To understand this concept we are using the base table ‘student_info’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | ...
Read MoreHow can we create a MySQL view with a subquery?
To illustrate the making of MySQL view with subquery we are using the following data from the table ‘Cars’ −mysql> select * from cars; +------+--------------+---------+ | ID | Name | Price | +------+--------------+---------+ | 1 | Nexa | 750000 | | 2 | Maruti Swift | 450000 | | 3 | BMW | 4450000 | | 4 | VOLVO | 2250000 | | 5 | Alto | 250000 | | 6 | Skoda ...
Read MoreWhere MySQL views can be inconsistent and how can we ensure their consistency?
In case of updateable views, it is quite possible that we update the data that is not visible through the view because we create a view to revealing only the partial data of a table. Such kind of updates makes the view inconsistent. We can ensure the consistency of views by using WITH CHECK OPTION while creating or modifying the views. Although WITH CHECK OPTION clause is an optional part of CREATE VIEW statement but it is very useful to make views consistent.Basically, the WITH CHECK OPTION clause prevents us from updating or inserting the rows which are not visible ...
Read MoreHow can I create a MySQL view that takes the values from a table based on some condition(s)?
If we want to create a view that takes the values from a table based on some particular condition(s) then we have to use WHERE clause while creating the view. The values depending upon the WHERE clause will be stored in view. The syntax of creating a MySQL view with WHERE clause can be as follows −SyntaxCreate View view_name AS Select_statements FROM table WHERE condition(s);ExampleTo illustrate the above concept, we are using the following data from table ‘Student_info’ −mysql> Select * from student_info; +------+---------+------------+------------+ | id | Name | Address | Subject ...
Read MoreHow 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 More