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 72 of 341
How can I get the output based on comparison done with column’s name using MySQL IN() function?
In this scenario, we need to use the name of the column as ‘Expression’ which will then be compared with the values in the list. If a column has value/s matched within the list, the output would be produced. For understanding it, consider the example from employee table having the following data −mysql> Select * from Employee; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 1 | Gaurav | 50000 | | 2 | Rahul | 20000 | | 3 | Advik | 25000 | | 4 | Aarav | 65000 | | 5 | Ram | 20000 | ...
Read MoreHow can I use MySQL IN() function to compare row constructors?
We can also use IN() function to compare row constructors. Consider the following example to make it clearer −mysql> Select (10,2) IN ((5,10),(10,2),(2,10),(100,100)); +--------------------------------------------+ | (10,2) IN ((5,10),(10,2),(2,10),(100,100)) | +--------------------------------------------+ | 1 | +--------------------------------------------+ 1 row in set (0.00 sec) mysql> Select (10,2) IN ((5,10),(2,10),(100,100)); +-------------------------------------+ | (10,2) IN ((5,10),(2,10),(100,100)) | +-------------------------------------+ | 0 | +-------------------------------------+ 1 row in set (0.00 sec)
Read MoreHow can we handle a result set inside MySQL stored procedure?
We can use a cursor to handle a result set inside a stored procedure. Basically a cursor allows us to iterate a set of rows returned by a query and process each row accordingly.To demonstrate the use of CURSOR within MySQL stored procedure, we are creating the following stored procedure which is based on the values, as shown below, of the table named ‘student_info’ −mysql> Select * from student_info; +-----+---------+----------+------------+ | id | Name | Address | Subject | +-----+---------+----------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Jaipur | Literature ...
Read MoreWhen MySQL IN() function returns NULL?
Following are the two cases when MySQL IN() function returns NULL as result −Case-1 − When expression on left side is NULL IN() function will return NULL if the expression on the left side is NULL. Following example will demonstrate it −mysql> Select NULL IN (1, 2, 3, 4, 10); +----------------------+ | NULL IN (1, 2, 3, 4, 10) | +----------------------+ | NULL | +----------------------+ 1 row in set (0.00 sec)Case-2 − When one of expression in the list is NULL and no match is foundIN() function will return NULL if it does ...
Read MoreIn MySQL, how can we declare a handler while handling errors?
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 MoreHow can MySQL REPLACE() function be used with WHERE clause?
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 MoreHow can we fetch a MySQL SET column as a list of integer offset?
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 MoreWrite down the MySQL query which shows equality condition?
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.
Read MoreHow does the precedence of || operator depend on PIPES_AS_CONCAT SQL mode?
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 MoreIn MySQL, how does the precedence of ! operator in comparison with NOT operator depends upon HIGH_NOT_PRECEDENCE SQL mode?
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.
Read More