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 60 of 341
How can we create a MySQL one-time event that executes immediately?
As we know a one-time event means the events that will be executed only once on a particular schedule. To illustrate the creation of such kind of events we are using the following example in which we are creating an event which will execute at the current time −Examplemysql> Create table event_message(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, MESSAGE VARCHAR(255) NOT NULL, Generated_at DATETIMENOT NULL); Query OK, 0 rows affected (0.61 sec) mysql> CREATE EVENT testing_event ON SCHEDULE AT CURRENT_TIMESTAMP DO INSERT INTO event_message(message, generated_at) Values('Hello', NOW()); Query OK, 0 rows affected (0.00 sec) mysql> Select * from ...
Read MoreWhen a MySQL arithmetic expression returns NULL?
As we know that a NULL is not a value and it is also not the same as zero. MySQL arithmetic expression returns NULL if we will use NULL in it. It can be understood with the help of the following example −Examplemysql> Select 100*NULL; +----------+ | 100*NULL | +----------+ | NULL | +----------+ 1 row in set (0.00 sec) mysql> Select 100+NULL; +----------+ | 100+NULL | +----------+ | NULL | +----------+ 1 row in set (0.00 sec)From the above example, it can be observed that if we will use NULL in an arithmetic expression then the result would be NULL itself.
Read MoreHow Can we use MySQL DISTINCT clause with WHERE and LIMIT clause?
By using the WHERE clause with a DISTINCT clause in MySQL queries, we are putting a condition on the basis of which MySQL returns the unique rows of the result set. By using the LIMIT clause with a DISTINCT clause in MySQL queries, we are actually providing a perimeter to the server about a maximum number of unique rows of the result set to be returned.ExampleWe can use WHERE and LIMIT clause with DISTINCT as follows on the table named ‘testing’ −mysql> Select * from testing; +------+---------+---------+ | id | fname | Lname | +------+---------+---------+ | 200 ...
Read MoreHow can I get the records from MySQL table in result set in a particular way?
For getting the records from MySQL table in the result set in a particular way either ascending or descending, we need to use the ORDER BY clause along with ASC or DESC keywords. If we will not use any of the above-mentioned keywords then MySQL by default return the records in ascending order. The ORDER BY clause returned the result set based on a particular field (ascending or descending order) with which we will use the ORDER BY clause. Suppose we want to sort the rows of the following table −mysql> Select * from Student; +--------+--------+--------+ | Name | ...
Read MoreWhy do we need to change the delimiter for creating a trigger?
As we know that in MySQL we use the delimiter semicolon (;) to end each statement. The semicolon is the by default delimiter in MySQL. We need to change the delimiter, while creating a trigger, to tell MySQL that this is not the end of our trigger statement because we can use multiple statements in the trigger. We can change the delimiter temporarily by DELIMITER // statement to change the delimiter from Semicolon (;) to two back-slash (//). After this MySQL would know that the triggering statement only ends when it encounters a two back-slash (//). Following is an example ...
Read MoreHow can I check the tables of databases other than current database?
With the help of following MySQL command, we can check the tables of a database other than the database we are currently using −Show Tables from Database_name;For example, the following query would display the list of tables from a database named ‘gaurav’ when currently we are using a database named ‘new’ −mysql> use new; Database changed mysql> show tables from gaurav; +--------------------+ | Tables_in_tutorial | +--------------------+ | testing | | employee | | tender | | Ratelist | +--------------------+ 4 rows in set (0.00 sec)
Read MoreHow can we see the information on triggers order in case of multiple triggers\\nfor same event and action time?
It can be done with the help of the following query −mysql> SELECT trigger_name,action_order FROM INFORMATION_SCHEMA.triggers WHERE TRIGGER_SCHEMA = 'query' ORDER BY event_object_table,action_timing,event_manipulation; +------------------------------+--------------+ | trigger_name | action_order | +------------------------------+--------------+ | studentdetail_before_update | 1 | | studentdetail_before_update2 | 2 | +------------------------------+--------------+ 2 rows in set (0.10 sec)The above result set shows the order of multiple triggers created on the same event and action time in the database ‘query’.
Read MoreWhat are the privileges required to use triggers?
We must have SUPER privileges to create or destroy triggers with the help of a CREATE or DROP statement. Other than that if the triggered statements use OLD or NEW then there are some additional privileges as follows which we require −To assign the value of a column with SET NEW.column_name = value then we must have the UPDATE privilege for the column.To use NEW.column_name in an expression to refer to the new value of a column, we must have the SELECT privilege for the column.
Read MoreHow can we destroy a trigger?
We can destroy a trigger in two ways −Dropping a trigger explicitlyWith the help of the DROP statement, we can destroy a trigger explicitly. It can be understood with the help of the following example −mysql> DROP Trigger before_inser_studentage1; Query OK, 0 rows affected (0.05 sec)Dropping a trigger implicitlyA trigger will be destroyed implicitly if the table with which it is associated is destroyed or if the database which it is associated is destroyed.
Read MoreHow can we use INFORMATION_SCHEMA to get the details about triggers in a particular database?
It can be done with the help of the following statement −mysql> select * from information_schema.triggers where -> information_schema.triggers.trigger_schema like '%query%'\G *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: query TRIGGER_NAME: trigger_before_delete_sample EVENT_MANIPULATION: DELETE EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: query EVENT_OBJECT_TABLE: sample ACTION_ORDER: 1 ACTION_CONDITION: NULL ACTION_STATEMENT: BEGIN SET @count ...
Read More