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
MySQLi Articles
Page 58 of 341
What 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. It can ...
Read MoreWhat 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 simple view can ...
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 (HY000): Unknown ...
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 counts\\nof 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 EVENT_BODY: SQL EVENT_DEFINITION: INSERT INTO event_message(message, generated_at) values('EVENT ALTERED', NOW()) EVENT_TYPE: ONE TIME EXECUTE_AT: 2017-11-22 20:03:52 ...
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 More