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
Articles on Trending Technologies
Technical articles with clear explanations and examples
In MySQL, what is the difference between SERIAL and AUTO_INCREMENT?
In MySQL, both SERIAL and AUTO_INCREMENT are used to define a sequence as a default value for a field. But they are technically different from each other.The AUTO_INCREMENT attribute is supported by all numeric data types except for BIT and DECIMAL. There can only be one AUTO_INCREMENT field per table and the sequence generated by an AUTO_INCREMENT field in one table cannot be used in any other table. This attribute requires that a UNIQUE index exists on the field to ensure the sequence has no duplicates. The sequence would start by default from 1 and increment by 1 for every insert.Examplemysql> ...
Read MoreHow can I store the fixed length string as well as variable length string in the same MySQL table?
As we know that CHAR is used to store fixed length string and VARCHAR is used to store variable length strings. Hence we can store a fixed length as well as variable length string in the same table by declaring a column as CHAR and other as VARCHAR.Examplemysql> Create Table Employees(FirstName CHAR(10), LastName VARCHAR(10)); Query OK, 0 rows affected (0.64 sec) mysql> Desc Employees; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | FirstName | char(10) | YES ...
Read MoreWhat is the importance of the order of Columns in the SET clause of UPDATE statement? Will it make big difference in result set returned by MySQL?
The order of columns in the SET clause of UPDATE statement is important because MySQL provides us the updated value on columns names used in an expression. Yes, it will make big difference in the result set returned by MySQL. Following is an example to make it clear −ExampleIn this example, we are having a table ‘tender’. First, we will write UPDATE statement by using ‘tender_id’ as the first and ‘rate’ as the second column in SET clause and then we will write UPDATE statement by using ‘rate’ as the first and ‘tender_id’ as the second column on table ‘tender’.mysql> ...
Read MoreWhat MySQL returns if sub-query, used to assign new values in the SET clause of UPDATE statement, returns multiple rows?
In this case, MySQL will return an error message because we know that if sub-query is used to assign new values in the SET clause of UPDATE statement then it must return exactly one row for each row in the update table that matches the WHERE clause.Examplemysql> insert into info(id, remarks) values(5, 'average'); Query OK, 1 row affected (0.06 sec) mysql> select * from info; +------+-----------+ | id | remarks | +------+-----------+ | 1 | Good | | 2 | Good | | 3 | Excellent | | 4 ...
Read MoreWhat MySQL returns if sub-query, used to assign new values in the SET clause of UPDATE statement, returns no rows?
In this case, MySQL will provide a NULL value to the SET clause. Following example will demonstrate it −Examplemysql> Select * from student; +----+---------+-----------+ | Id | Name | grade | +----+---------+-----------+ | 1 | Rahul | Good | | 2 | Gaurav | Good | | 3 | Raman | Excellent | | 4 | Harshit | Average | | 5 | Aarav | Best | | 6 | Ram | average | +----+---------+-----------+ 6 rows in set (0.00 sec) ...
Read MoreWhat is the use of ALLOW_INVALID_DATES SQL mode?
As the name suggests, enabling ALLOW_INVALID_DATES SQL mode will allow us to store invalid dates in the table. The example is given below to understand it −Examplemysql> SET sql_mode = ALLOW_INVALID_DATES; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> Insert Into detail_bday(Name, Birth_Date) values('Sonia', '1993-11-31'); Query OK, 1 row affected (0.09 sec) mysql> Insert Into detail_bday(Name, Birth_Date) values('Ram', '0000-00-00'); Query OK, 1 row affected (0.10 sec) mysql> Select * from detail_bday; +----+---------+------------+ | Sr | Name | Birth_Date | +----+---------+------------+ | 1 | Saurabh | 1990-05-12 | | 2 | Raman | 1993-06-11 ...
Read MoreHow can we use existing values of the rows to provide new values in the SET clause of UPDATE statement?
Existing values of the row can be used to provide new values in the SET clause if that row matches the WHERE clause in an UPDATE statement. Following is the example to demonstrate it.ExampleSuppose we have a table named ‘tender’ as follows −mysql> Select * from tender; +-----------+---------+------+ | tender_id | company | rate | +-----------+---------+------+ | 200 | ABC | 1000 | | 300 | ABD | 6000 | | 301 | ABE | 7000 | | 302 | ABF ...
Read MoreHow MySQL evaluates if I store date along with time value in a column having DATE data type?
As we know that the default format for MySQL DATE data type is “YYYY-MM-DD” and in this format, there is no possibility to store the time value. But still, if we store date along with time value in a column having DATE data type then MySQL will show a warning and store only the date value in that column. Following example will exhibit it −Examplemysql> Create table date_time_test(date DATE); Query OK, 0 rows affected (0.44 sec) mysql> Insert into date_time_test(date) values(‘2017-09-09 09:34:21’); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> Select * from date_time_test; +------------+ | ...
Read MoreHow can we update columns values on multiple rows with a single MySQL UPDATE statement?
Column values on multiple rows can be updated in a single UPDATE statement if the condition specified in WHERE clause matches multiple rows. In this case, the SET clause will be applied to all the matched rows.ExampleSuppose we have a table ‘tender’ as follows −mysql> Select * from tender; +-----------+---------+------+ | tender_id | company | rate | +-----------+---------+------+ | 200 | ABC | 1000 | | 300 | ABD | 5000 | | 301 | ABE | 6000 | | 302 ...
Read MoreIn MySQL, how can we use FROM_UNIXTIME() function with format string?
Suppose if we want the output of FROM_UNIXIME() function in a particular format then we can use date format string or time format string or both in it. Following is the example of using the format string in FROM_UNIXTIME() function −mysql> Select FROM_UNIXTIME(1555033470 '%Y %M %D')AS 'Formatted Output'; +------------------+ | Formatted Output | +------------------+ | 2019 April 12th | +------------------+ 1 row in set (0.00 sec)In the query above, it is using only date format string.mysql> Select FROM_UNIXTIME(1555033470 '%h:%i:%s')AS 'Formatted Output'; +------------------+ | Formatted Output | +------------------+ | 07:14:30 | +------------------+ 1 row in set (0.00 ...
Read More