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
How 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 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 MoreHow can we ENABLE AND DISABLE a particular MySQL event?
With the help of ALTER EVENT statement along with the ENABLE and DISABLE keyword, we can ENABLE and DISABLE the event. To illustrate it we are having the following example −Examplemysql> ALTER EVENT hello DISABLE; Query OK, 0 rows affected (0.00 sec)The above query will DISABLE the event named ‘Hello’ and the query below will enable it.mysql> ALTER EVENT hello ENABLE; Query OK, 0 rows affected (0.00 sec)
Read MoreHow can we find the employees from MySQL table whose age is greater than say 30 years, providing the only date of birth on the table?
To understand this concept, we are using the data from table ‘emp_tbl’ as follows −mysql> Select * from emp_tbl; +--------+------------+ | Name | DOB | +--------+------------+ | Gaurav | 1984-01-17 | | Gaurav | 1990-01-17 | | Rahul | 1980-05-22 | | Gurdas | 1981-05-25 | | Naveen | 1991-04-25 | | Sohan | 1987-12-26 | +--------+------------+ 6 rows in set (0.00 sec) mysql> SELECT Name, SYSDATE(), DOB, DATEDIFF(SYSDATE(), DOB)/365 AS AGE from emp_tbl WHERE(DATEDIFF(SYSDATE(), DOB)/365)>30; +--------+---------------------+------------+---------+ | Name | SYSDATE() | DOB | AGE ...
Read More