Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
With the help of the SHOW TRIGGERS statement, we can list all the triggers in a particular database. It can be illustrated with the help of the following example −Examplemysql> Show Triggers\G *************************** 1. row *************************** Trigger: trigger_before_delete_sample Event: DELETE Table: sample Statement: BEGIN SET @count = if (@count IS NULL, 1, (@count+1)); INSERT INTO sample_rowaffected values (@count); END Timing: BEFORE Created: 2017-11-21 12:31:58.70 sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERR OR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: latin1_swedish_ci *************************** 2. ... Read More
With the help of \c command, we can discard a MySQL statement in the middle of its processing. Consider the following example in which in the middle of the statement we want to discard it then we use \c option for it −mysql> Select * -> from\c mysql>The above query shows that after using \c option MySQL discards the statement and returns to the prompt.
We must have to understand the advantages, disadvantages, and restrictions of using MySQL triggers so that we can use it effectively.AdvantagesFollowings are the advantages of using MySQL triggers −Integrity of data − With the help of MySQL trigger we can check the integrity of data in the table. In other words, MySQL triggers are the alternative way to check the integrity of data.Useful for catching errors − MySQL triggers can catch errors in business logic in the database layer.Alternative way to run scheduled tasks − Actually by using MySQL triggers we do not have to wait to run the scheduled tasks because ... Read More
With the help of following MySQL statement, we can use SELECT to calculate the expression −SELECT expression;The above statement is having no reference to any table. Followings are some examples −mysql> Select 10 DIV 5; +----------+ | 10 DIV 5 | +----------+ | 2 | +----------+ 1 row in set (0.06 sec) mysql> Select 10*5; +------+ | 10*5 | +------+ | 50 | +------+ 1 row in set (0.00 sec) mysql> Set @var1 = 100, @var2 = 200; Query OK, 0 rows affected (0.00 sec) mysql> Select @Var1+@Var2; +-------------+ | @Var1+@Var2 | +-------------+ | 300 | +-------------+ 1 row in set (0.00 sec)
We can get the summary output of a column in MySQL result set by using the “WITH ROLLUP” modifier. This modifier is used with GROUP BY CLAUSE. It gives the summary output to include extra rows that represent higher-level summary operations.ExampleIn this example, the WITH ROLLUP modifier gave the summary output with total cost value in the extra row.mysql> Select Item_name, SUM(Cost) AS Total_cost from Item_list GROUP BY Item_name WITH ROLLUP; +-----------+------------+ | Item_name | Total_cost | +-----------+------------+ | Notebook | 45.00 | | Pen | 31.70 | | Pencilbox | 125.20 | | NULL | 201.90 | +-----------+------------+ 4 rows in set (0.00 sec)
It can be done with the help of the INFORMATION_SCHEMA database. Following statement will give us the metadata of triggers −mysql> Select trigger_schema, trigger_name, action_statement -> from information_schema.triggers\G *************************** 1. row *************************** trigger_schema: query trigger_name: trigger_before_delete_sample action_statement: BEGIN SET @count = if (@count IS NULL, 1, (@count+1)); INSERT INTO sample_rowaffected values (@count); END *************************** 2. row *************************** trigger_schema: query trigger_name: before_inser_studentage action_statement: IF NEW.age < 0 THEN SET NEW.age = 0; END IF *************************** 3. row *************************** trigger_schema: sys trigger_name: sys_config_insert_set_user action_statement: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by ... Read More
Declaring a column ‘NOT NULL’ means that this column would not accept NULL values but zero (0) and an empty string is itself a value. Hence there would be no issue if we want to insert zero or an empty string into a MySQL column which is defined as NOT NULL. Following comparisons of 0 and empty string with NULL would make it clear −mysql> Select 0 IS NULL, 0 IS NOT NULL; +-----------+---------------+ | 0 IS NULL | 0 IS NOT NULL | +-----------+---------------+ | 0 | 1 | ... Read More
By executing SELECT @@TX_ISOLATION command we can check the current MySQL transaction isolation level.Examplemysql> SELECT @@TX_ISOLATION; +-----------------+ | @@TX_ISOLATION | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)
The representation of an empty string in result set depends on data type when we insert an empty string into a MySQL column which is declared as NOT NULL. As we know that on inserting empty string we are providing value to MySQL that has integer representation as INT 0.Now, if that column is having INTEGER data type then MySQL would show 0 in the result set as that empty string has been mapped to zero as an integer.Examplemysql> create table test(id int NOT NULL, Name Varchar(10)); Query OK, 0 rows affected (0.19 sec) mysql> Insert into test(id, name) ... Read More
FROM clause after SELECT shows the references to a table. But if there is no reference to any table then we can use SELECT without the FROM clause. In other words, we can say that SELECT can be used to retrieve rows computed without reference to any table. Consider the following statements −mysql> Select concat_ws(" ","Hello", "World"); +---------------------------------+ | concat_ws(" ","Hello", "World") | +---------------------------------+ | Hello World | +---------------------------------+ 1 row in set (0.00 sec) mysql> Set @var1=100; Query OK, 0 rows affected (0.00 sec) mysql> Select @var1; +-------+ | @var1 | +-------+ | 100 | +-------+ 1 row in set (0.00 sec)