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 257 of 547
How 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() ...
Read MoreWhat is the use of ON COMPLETION PRESERVE clause while creating the event?
As we know that an event is automatically dropped when it is expired and we would not be able to see it from SHOW EVENTS statement. To change such kind of behavior we can use ON COMPLETION PRESERVE while creating the event. It can be understood from the following example −Examplemysql> Create table event_messages(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, MESSAGE VARCHAR(255) NOT NULL, Generated_at DATETIME NOT NULL); Query OK, 0 rows affected (0.61 sec)The below query will create an event without the use of ON COMPLETION PRESERVE hence it would not be seen in the output of SHOW EVENTS ...
Read MoreHow to delete the duplicate values stored in reverse order from MySQL table?
To understand this concept, we are using the data from table ‘Details_city’ as follows −mysql> Select * from details_city; +--------+--------+ | City1 | City2 | +--------+--------+ | Delhi | Nagpur | | Delhi | Mumbai | | Nagpur | Delhi | | Katak | Delhi | | Delhi | Katak | +--------+--------+ 5 rows in set (0.00 sec)Now, the following query will delete the reverse duplicate values from details_city table −mysql> Select a.city1,a.city2 from details_city a WHERE a.city1
Read MoreHow can we RENAME an existing MySQL event?
With the help of ALTER EVENT statement along with the RENAME keyword, we can RENAME an existing event. To illustrate it we are having the following example in which we are renaming the event ‘Hello’ to ‘Hello_renamed’ −Examplemysql> ALTER EVENT Hello RENAME TO Hello_renamed; Query OK, 0 rows affected (0.00 sec)To confirm that event has been renamed we can try to delete the event with the old name, MySQL will throw an error as follows −mysql> DROP EVENT hello; ERROR 1539 (HY000): Unknown event 'hello'
Read MoreHow can we put schedule for different kinds of MySQL events?
Basically, there are two kinds of events for which we need to specify the schedule −One-time eventOne-time event means it will be executed only once on a particular schedule. If we want to create a one-time event then we need to put the following syntax after ON SCHEDULE clause −AT Timestamp[+INTERVAL]Recurring eventsRecurring event means that it will be executed after regular time of interval. If we want to create a recurring event then we need to put the following syntax after ON SCHEDULE clause −EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]
Read MoreHow will GROUP BY clause perform without an aggregate function?
When we use GROUP BY clause in the SELECT statement without using aggregate functions then it would behave like DISTINCT clause. For example, we have the following table −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 125 | Raman | Shimla | Computers | | 130 | Ram | Jhansi | Computers | | 132 | Shyam | Chandigarh ...
Read MoreHow can we start MySQL event scheduler?
Actually, MySQL event scheduler is a process that runs in the background and constantly looks for the events to execute. But before we create or schedule an event we just have to start the scheduler. It can start with the help of the following statement −mysql> SET GLOBAL event_scheduler = ON; Query OK, 0 rows affected (0.07 sec)Now with the help of the following statement, we can check its status in MySQL process list −mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 3 User: root Host: localhost:49500 ...
Read MoreHow can we match the values having backslashes, like 'ab', from MySQL column?
With the help of an RLIKE operator, we can perform such kind of matching. The only concept is about to use a number of backslashes in MySQL query. The example below will make it clearer −We have the following table having values such as ‘a\b’ and ‘a\b’.mysql> select * from backslashes; +------+-------+ | Id | Value | +------+-------+ | 1 | 200 | | 2 | 300 | | 4 | a\b | | 3 | a\b | +------+-------+ 4 rows in set (0.10 sec)Now suppose if we want to match the ...
Read MoreHow can we fetch alternate even-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 even-numbered records from the above table ‘Information’ −mysql> Select id,Name from information group by id having mod(id,2) = 0; +----+---------+ | id | Name | +----+---------+ | 2 | Ram | | 4 | Aarav | | 6 | Krishan | +----+---------+ 3 rows in set (0.00 sec)
Read More