Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
It is very important to handle the errors by throwing a proper error message. MySQL provides a handler to handle the error. We can declare a handler with the help of the following syntax −Syntax of handlerDECLARE handler_action FOR condition_value statement;The above syntax shows that we need to use DECLARE HANDLER statement to declare a handler. If a condition whose value matches the condition_value then MySQL will execute the statement and continue or exit the current code block based on the action. Followings are the three major things in the above syntax −Handler_action is of two types and can accept one ... Read More
Whenever an exception occurred in MySQL stored procedure, it is very important to handle it by throwing a proper error message. Suppose, if we do not handle the exception, there would be a chance to fail application with that certain exception in a stored procedure. MySQL provides a handler to handle the exception in the stored procedure. Followings are the four kinds of MySQL handlers which can be used in a stored procedure −DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'got an error';The above handler will throw an error message and continues the execution.DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET got_error=1;The above ... Read More
As we know that WHERE clause is used to put condition/s in MySQL query and MySQL returns result set based on those conditions. Similarly when we use REPLACE() function with WHERE clause, the result set will depend upon the conditions provided. Following is an example by using data from the ‘Student’ table in which REPLACE() function replaces the records of a column ‘Name’ in which the value of column ‘Subject’ is ‘Computers’.Examplemysql> Select Name, REPLACE(Name, 'G', 'S') from student Where Subject = 'Computers'; +--------+------------------------+ | Name | REPLACE(Name, 'G', 'S') | +--------+------------------------+ | Gaurav | Saurav ... Read More
We can fetch the MySQL SET column values as a list of integer offset with the help of the MAKE_SET() function. To make it understand, we are creating a table named ‘set_testing’ as follows −mysql> Create table set_testing( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, table SET('ABC', 'ABD', 'GHF') NOT NULL); Query OK, 0 rows affected (0.08 sec) mysql> Insert into set_testing (table) values('1'); Query OK, 1 row affected (0.06 sec) mysql> Insert into set_testing (table) values('2'); Query OK, 1 row affected (0.06 sec) mysql> Insert into set_testing (table) values('3'); Query OK, 1 row affected (0.02 ... Read More
The binary equality operators compare their operands for strict equality or inequality. In MySQL, the equal-to-operator (=) returns 1 if both the operands have the same value otherwise returns 0. Following MySQL query show an equality condition −mysql> Select tender_value From estimated_cost WHERE id = 3;The above query shows an equality condition because the column id equates to the integer value.mysql> Select tender_value From estimated_cost1 WHERE Name_company = 'Chd Ltd.';The above query shows an equality condition because column Name_company equates to the string value.
As we know that in MySQL by default || operator is a logical OR operator but it depends upon PIPES_AS_CONCAT SQL mode. If PIPES_AS_CONCAT SQL mode is enabled, then || operator works as string concatenation. At that time its precedence would be between ^ and the unary operator. Following example will make it understand −mysql> Set @C='tutorials'; Query OK, 0 rows affected (0.00 sec) mysql> Set @D='point'; Query OK, 0 rows affected (0.00 sec) mysql> Select @C||@D; +--------+ | @C||@D | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)The result set of the ... Read More
In MySQL, basically the precedence of ! operator in comparison with NOT operator depends upon the enabling or disabling of HIGH_NOT_PRECEDENCE SQL mode as follows −Disabled HIGH_NOT_PRECEDENCE SQL − In this case,! the operator has higher precedence than NOT operator.Enabled HIGH_NOT_PRECEDENCE SQL − In this case,! the operator has the same precedence as NOT operator.
For combining values of two or more columns, we can use MySQL CONCAT() function. In this case, the arguments of the CONCAT() functions would be the name of the columns. For example, suppose we have a table named ‘Student’ and we want the name and address of the student collectively in one column then the following query can be written −mysql> Select Id, Name, Address, CONCAT(ID, ', ', Name, ', ', Address)AS 'ID, Name, Address' from Student; +------+---------+---------+--------------------+ | Id | Name | Address | ID, Name, Address | +------+---------+---------+--------------------+ | 1 | Gaurav | Delhi ... Read More
MySQL NULL-safe equal operator, equivalent to standard SQL IS NOT DISTINCT FROM operator, performs an equality comparison like = operator. Its symbol is . It performs differently from the comparison operators in the case when we have NULL as both the operands. Consider the following examples to understand NULL-safe operator along with its difference with comparison operator −mysql> Select 50 50, NULL NULL, 100 NULL; +-----------+---------------+--------------+ | 50 50 | NULL NULL | 100 NULL | +-----------+---------------+--------------+ | 1 | 1 | 0 | +-----------+---------------+--------------+ 1 row in set (0.00 sec) mysql> Select 50 = 50, NULL = NULL, 100 = NULL; +---------+-------------+------------+ | 50 = 50 | NULL = NULL | 100 = NULL | +---------+-------------+------------+ | 1 | NULL | NULL | +---------+-------------+------------+ 1 row in set (0.00 sec)
When we use NULL-safe operator with row comparisons like (A, B) (C, D) then its performance is equivalent to (A C) AND (B D). Following example will demonstrate it −mysql> Select (100,50) (50,100); +-----------------------+ | (100,50) (50,100) | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.00 sec) mysql> Select (100,50) (100,50); +-----------------------+ | (100,50) (100,50) | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.00 sec)The above result sets show how NULL-safe operators can be used with row comparison.