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
MySQLi Articles
Page 111 of 341
How can CONCAT() function be used with MySQL WHERE clause?
Suppose from the table ‘Student’ we want to concatenate the values of columns, ‘Name’, ‘Address’ and ‘Columns’, based on the condition that is also a concatenation of values from columns, ’Name’, ‘Subject’, provided in WHERE clause with the help of CONCAT() function. We can use the following query to give the output −mysql> Select CONCAT(Name, ' ', 'Resident of', ' ', Address, ' ', 'is', ' ', 'Studying', ' ', Subject)AS 'Detail of Student' from Student WHERE CONCAT(Name, Subject) = "AaravHistory"; +----------------------------------------------+ | Detail of Student | ...
Read MoreHow Can MySQL CAST handle overflow?
MySQL CAST can handle overflow occurs during numerical expression assessment. Suppose if numeric expression evaluation produces overflow then MySQL reflects an error message. Now to handle this overflow we can change that numeric value to UNSIGNED with the help of CAST.For example on adding 1 to BIGINT maximum value, MySQL produce an error due to overflow as follows −mysql> Select 9223372036854775807 + 1; ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807+1)'Now, with the help of CAST, MySQL handles this kind of overflow as follows:mysql> Select CAST(9223372036854775807 AS UNSIGNED) +1; +------------------------------------------+ | CAST(9223372036854775807 AS UNSIGNED) +1 | +------------------------------------------+ | 9223372036854775808 | +------------------------------------------+ 1 row in set (0.07 sec)
Read MoreWhat is the use of NO_UNSIGNED_SUBTRACT SQL mode in handling overflow?
In case of enabled SQL strict mode, subtraction between integers value in which one is of UNSIGNED type will produce an unsigned result by default. But MySQL produces an error if the result is a negative one. It can be observed with the following example −mysql> SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> Select CAST(0 AS UNSIGNED) -1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'The error after the query above shows that it is an overflow after numeric arithmetic expression.Now, it can be handled with ...
Read MoreHow can we force MySQL out of TRADITIONAL mode?
With the help of the following command we can force MySQL out of TRADITIONAL mode −mysql> Set SQL_MODE =''; Query OK, 0 rows affected (0.00 sec)
Read MoreHow do you find out which sequence number was assigned recently by MySQL AUTO_INCREMENT?
Last_Insert_Id() MySQL function is used to find out which sequence number was assigned recently by AUTO_INCREMENT.Examplemysql> Create table Employee(Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Name Varchar(5)); Query OK, 0 rows affected (0.13 sec) mysql> Insert into Employee(Name) Values('Harvinder'); Query OK, 1 row affected (0.06 sec) mysql> Insert into Employee(Name) Values('Suresh'); Query OK, 1 row affected (0.07 sec) mysql> Select* from Employee; +----+---------+ | Id | Name | +----+---------+ | 1 |Harvinder| | 2 | Suresh | +----+---------+ 2 rows in set (0.00 sec) mysql> Select Last_insert_id(); +------------------+ | Last_insert_id() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec)
Read MoreHow can we use BIN() function with MySQL WHERE clause?
When BIN() string function is used with WHERE clause, the output returns by it will depend upon the condition given in WHERE clause. In this case, we must have to use binary value in WHERE clause. For example, suppose we have a table named ‘Student’ and we want to get only those rows where the binary value of column ‘id’ is higher than 1010, then we can write following query −mysql> Select *, Bin(id) from student where BIN(id) > 1010 ; +------+---------+---------+-----------+---------+ | Id | Name | Address | Subject | Bin(id) | +------+---------+---------+-----------+---------+ | 15 ...
Read MoreHow can we say that in MySQL, AUTO_INCREMENT is taking precedence over PRIMARY KEY?
This can be understood with the help of an example in which NULL value has been inserted in an AUTO_INCREMENT column and MySQL deliver a new sequence number.mysql> Create table employeeinfo(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Name Varchar(10)); Query OK, 0 rows affected (0.16 sec) mysql> Insert into employeeinfo(id, Name) values(NULL, 'Saurabh'); Query OK, 1 row affected (0.07 sec) mysql> Select * from employeeinfo; +----+---------+ | id | Name | +----+---------+ | 1 | Saurabh | +----+---------+ 1 row in set (0.00 sec)As we can observe from the above example that the column ‘id’ has been ...
Read MoreHow does MySQL handle out of range numeric values?
Handling of MySQL numeric value that is out of allowed range of column data type depends upon the SQL mode in following ways −(A) Enabled SQL strict mode - When strict SQL mode is enabled, MySQL returns the error on entering the put-of-range value. In this case, the insertion of some or all the values got failed.For example, we have created a table with two columns having TINYINT and UNSIGNED TINYINT as their data types on columns.mysql> Create table counting(Range1 Tinyint, Range2 Tinyint Unsigned); Query OK, 0 rows affected (0.14 sec)Now with the help of the following command, we enabled the ...
Read MoreWhat MySQL ASCII() function returns if I will provide NULL to it?
In this case, the output of ASCII() function depends on the condition that whether we are providing NULL as a string or we are providing simply NULL to it. Following example will demonstrate the difference −mysql> SELECT ASCII(null); +-------------+ | ASCII(null) | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) mysql> SELECT ASCII('null'); +---------------+ | ASCII('null') | +---------------+ | 110 | +---------------+ 1 row in set (0.00 sec) mysql> Select ASCII(NULL); +-------------+ | ASCII(NULL) | +-------------+ | NULL | +-------------+ 1 row in set ...
Read MoreHow can we use ASCII() function with MySQL WHERE clause?
While using the ASCII() function with WHERE clause, the output returns by it will depend upon the condition given in WHERE clause. For example, suppose we have a table named ‘Student’ and we want to get the number code, higher than 65, of the first characters of the names of the students. The query for this can be written as follows −mysql> Select * from student; +------+---------+---------+-----------+ | Id | Name | Address | Subject | +------+---------+---------+-----------+ | 1 | Gaurav | Delhi | Computers | | 2 | Aarav | Mumbai | ...
Read More