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 256 of 547
What are the limitations of using MySQL views?
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 ...
Read MoreHow can I move an existing MySQL event to another database?
It can be done with the help of ALTER EVENT statement too. We need to use the combination of database name and event name along with the RENAME keyword. To illustrate it we are having the following example in which we are moving the event named ‘hello_renamed’ from ‘query’ database to ‘tutorial’ database −Examplemysql> ALTER EVENT query.hello_renamed RENAME to tutorials.hello_renamed; Query OK, 0 rows affected (0.00 sec)To confirm that event has been moved to database ‘tutorials’ we can try to delete the event with an old name, MySQL will throw an error as follows −mysql> DROP event hello_renamed; ERROR 1539 ...
Read MoreHow can we simulate the MySQL INTERSECT query?
Since we cannot use INTERSECT query in MySQL, we will use IN operator to simulate the INTERSECT query. It can be understood with the help of the following example −ExampleIn this example, we are two tables namely Student_detail and Student_info having the following data −mysql> Select * from Student_detail; +-----------+---------+------------+------------+ | studentid | Name | Address | Subject | +-----------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 130 | Ram | Jhansi ...
Read MoreWhat are the different status variables in MySQL which provide us the countsnof event-related operations?
Followings are the status variables in MYSQL which provide us the counts of event-related operations −Com_create_event It provides us the number of CREATE EVENT statements executed since the last server restart.Com_alter_event − It provides us the number of ALTER EVENT statements executed since the last server restart.Com_drop_event − It provides us the number of DROP EVENT statements executed since the last server restart.Com_show_create_event − It provides us the number of SHOW CREATE EVENT statements executed since the last server restart.Com_show_events − It provides us the number of SHOW EVENTS statements executed since the last server restart.
Read MoreHow can we simulate the MySQL MINUS query?
Since we cannot use the MINUS query in MySQL, we will use JOIN to simulate the MINUS query. It can be understood with the help of the following example −ExampleIn this example, we are two tables namely Student_detail and Student_info having the following data −mysql> Select * from Student_detail; +-----------+---------+------------+------------+ | studentid | Name | Address | Subject | +-----------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 130 | Ram | Jhansi ...
Read MoreHow can we get the count of all MySQL event-related operations collectively?
With the help of SHOW STATUS statement, we can get the count of MySQL event-related operations. It can be used as follows −mysql> SHOW STATUS LIKE '%event%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Com_alter_event | 16 | | Com_create_event | 6 | | Com_drop_event | 4 | | Com_show_binlog_events | 0 | | Com_show_create_event | 0 | | Com_show_events | 4 | | Com_show_relaylog_events | 0 | +--------------------------+-------+ 7 rows in set (0.17 sec)
Read MoreHow can I drop an existing column from a MySQL table?
We can delete a particular existing column from a MySQL table by using the DROP statement along with an ALTER statement. Its syntax would be as follows −SyntaxALTER TABLE table_name DROP column_name;Here, table_name is the name of the table from which we want to delete the column.Column_name is the name of the column which is to be deleted from the table.ExampleIn this example, we are deleting the column ‘address’ from table ‘student_detail’ as follows −mysql> select * from student_detail; +-----------+-------------+----------+ | Studentid | StudentName | address | +-----------+-------------+----------+ | 100 | Gaurav | Delhi ...
Read MoreHow can we get the metadata of MySQL events?
It can be done with the help of the INFORMATION_SCHEMA database. The following statement will give us the metadata of events −mysql> SELECT * from INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME LIKE '%event%' A ND EVENT_SCHEMA = 'query'\G *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: query EVENT_NAME: testing_event6 DEFINER: root@localhost TIME_ZONE: SYSTEM ...
Read MoreHow can we fetch alternate odd numbered records from MySQL table?
To understand this concept, we are using the data from table ‘Information’ as follows −mysql> Select * from Information; +----+---------+ | id | Name | +----+---------+ | 1 | Gaurav | | 2 | Ram | | 3 | Rahul | | 4 | Aarav | | 5 | Aryan | | 6 | Krishan | +----+---------+ 6 rows in set (0.00 sec)Now, the query below will fetch the alternate odd-numbered records from the above table ‘Information’ −mysql> Select id,Name from information group by id having mod(id,2) = 1; +----+--------+ | id | Name | +----+--------+ | 1 | Gaurav | | 3 | Rahul | | 5 | Aryan | +----+--------+ 3 rows in set (0.09 sec)
Read MoreHow can we fetch a second highest salary of an employee from a MySQL table?
To understand this concept, we are using the data from table ‘Salary’ as follows −mysql> Select * from Salary; +--------+--------+ | Name | Salary | +--------+--------+ | Gaurav | 50000 | | Rahul | 40000 | | Ram | 45000 | | Raman | 45000 | +--------+--------+ 4 rows in set (0.00 sec) mysql> Select * from salary12345 order by salary DESC limit 1 offset 1; +-------+--------+ | name | Salary | +-------+--------+ | Raman | 45000 | +-------+--------+ 1 row in set (0.00 sec)
Read More