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
Database Articles
Page 314 of 547
What 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 MoreHow can we search a record from MySQL table having a date as a value in it?
It can be understood with the help of following example in which we are using the following data from the table named ‘detail_bday’ −mysql> Select * from detail_bday; +----+---------+------------+ | Sr | Name | Birth_Date | +----+---------+------------+ | 1 | Saurabh | 1990-05-12 | | 2 | Raman | 1993-06-11 | | 3 | Gaurav | 1984-01-17 | | 4 | Rahul | 1993-06-11 | +----+---------+------------+ 4 rows in set (0.00 sec)Now, in the following two ways we can search records using the date −mysql> Select * from detail_bday Where Birth_Date = '1993-06-11'; +----+-------+------------+ | Sr | ...
Read MoreHow can we check the default character sets of all the MySQL databases we have on the server?
The query below will return the name of the database along with the default character set −mysql> SELECT SCHEMA_NAME 'Database', default_character_set_name 'charset' FROM information_schema.SCHEMATA; +--------------------+---------+ | Database | Charset | +--------------------+---------+ | information_schema | utf8 | | gaurav | latin1 | | menagerie | latin1 | | mysql | latin1 | | performance_schema | utf8 | | sample | latin1 | | test | latin1 | | tutorial | latin1 | +--------------------+---------+ 8 rows in set (0.00 sec)
Read More