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
Articles on Trending Technologies
Technical articles with clear explanations and examples
How 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 triggersnfor 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 ...
Read MoreHow to use MySQL DISTINCT clause on multiple columns?
We can use the DISTINCT clause on more than columns in MySQL. In this case, the uniqueness of rows in the result set would depend on the combination of all columns.ExampleConsider the following table ‘testing’ having 10 rows −mysql> select * from testing; +------+---------+---------+ | id | fname | Lname | +------+---------+---------+ | 200 | Raman | Kumar | | 201 | Sahil | Bhalla | | 202 | Gaurav | NULL | | 203 | Aarav | NULL | | 204 | Harshit | Khurana | | 205 | Rahul ...
Read MoreHow Can MySQL GROUP BY clause behave like DISTINCT clause?
When we use the GROUP BY clause in the SELECT statement without using aggregate functions then it would behave like the DISTINCT clause. For example, we have the following table −mysql> Select * from testing; +------+---------+---------+ | id | fname | Lname | +------+---------+---------+ | 200 | Raman | Kumar | | 201 | Sahil | Bhalla | | 202 | Gaurav | NULL | | 203 | Aarav | NULL | | 204 | Harshit | Khurana | | 205 | Rahul | NULL | | 206 | ...
Read MoreHow we can find all the triggers associated with a particular MySQL table?
We can find all the triggers associated with a particular table with the help of the following query −mysql> Select * from INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'query'AND EVENT_OBJECT_TABLE = 'Student_info'\G *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: query TRIGGER_NAME: studentinfo_after_delete EVENT_MANIPULATION: DELETE EVENT_OBJECT_CATALOG: def ...
Read More