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 59 of 341
What 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 db: query Command: Query Time: 0 State: ...
Read MoreHow can we match the values having backslashes, like ‘a\\\\b’, 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 MoreHow can we delete an existing MySQL event permanently?
We need to use the DROP statement to delete an existing MySQL event permanently. To illustrate it we are deleting the event named testing_event as follows −Examplemysql> DROP EVENT testing_event; Query OK, 0 rows affected (0.00 sec)
Read MoreHow can we enter numerical values as a BINARY number in MySQL statement?
Following are the two approaches with the help of which we can enter numeric values as a BINARY number −By prefix ‘B’In this approach we need to quote binary numbers within single quotes with a prefix of B. Then BINARY number string will be automatically converted into a numerical value based on the expression context.Examplemysql> Select B'1110'+0; +-----------+ | B'1110'+0 | +-----------+ | 14 | +-----------+ 1 row in set (0.00 sec)By prefix 0bIn this approach, we need to write BINARY numbers without any quotes with a prefix of 0b. Then BINARY number string will be automatically ...
Read More