MySQL Articles - Page 384 of 402

If we pass an invalid string as an argument to STR_TO_DATE() function then MySQL will return NULL as output along with a warning. Following is an example to understand the same −mysql> Select STR_TO_DATE('20173210', '%Y%d%m'); +-----------------------------------+ | STR_TO_DATE('20173210', '%Y%d%m') | +-----------------------------------+ | NULL                              | +-----------------------------------+ 1 row in set, 1 warning (0.00 sec)In the query above the string value is invalid because of wrong (32) day value. Hence it returns NULL values and a warning which is given below.mysql> Show warnings\G *************************** 1. row ***************************   ... Read More

At the time of creation of a table, if any column is defined with default values then by using the keyword ‘DEFAULT’ in the INSERT statement, we can take default value for that column. For example, we have created a table ‘employee’ with a default value of column ‘DOJ’ as follows −mysql> Create table employee(id int, name varchar(20), doj date DEFAULT '2005-01-01'); Query OK, 0 rows affected (0.09 sec) mysql> Insert into employee(id, name, doj) values(1, ’Aarav’, DEFAULT); Query OK, 1 row affected (0.03 sec) mysql> select * from employee; +------+------------+---------------+ | id   | name     ... Read More

As we know that we can convert a time of datetime value to an integer by adding 0(+0) to them. In a similar way, we can convert the datetime value returned by FROM_UNIXTIME() function in numeric format. The following example will clarify it more −mysql> Select FROM_UNIXTIME(1555033470)+0 AS 'Date in Numeric Format'; +------------------------+ | Date in Numeric Format | +------------------------+ | 20190412071430.000000  | +------------------------+ 1 row in set (0.00 sec)After adding 0 (+0) to datetime value MySQL returns the numeric value up to 6 digits microseconds.

With the help of UPDATE statement and WHERE clause, we can update the values in single or multiple rows of the table. MySQL updates the values on the basis of condition specified in WHERE clause. For example, suppose in the ‘employee’ table we want to change the ‘name’ and ‘doj’ of the employee whose id is 1 then it can be done with the following query −mysql> UPDATE employee SET name = 'Gaurav', doj = '2010-02-01' WHERE id = 1; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employee ... Read More

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 More

By specifying multiple columns in GROUP BY clause we can split the result set into smaller groups. The more columns specified in GROUP BY clause, the smaller the groups will be.Examplemysql> Select designation, YEAR(Doj), count(*) from employees GROUP BY designation, YEAR(DoJ); +-------------+-----------+----------+ | designation | YEAR(Doj) | count(*) | +-------------+-----------+----------+ | Asso.Prof   | 2013      | 1        | | Asst.Prof   | 2015      | 1        | | Asst.Prof   | 2016      | 1        | | Prof        | 2009      | 2        | | Prof        | 2010      | 1        | +-------------+-----------+----------+ 5 rows in set (0.00 sec)

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

It can be done by using GROUP BY clause in the SELECT statement. We can specify a column as grouping criteria with the help of GROUP BY clause. Due to the specified grouping criteria, rows with the same value in a particular column are considered as a single group. In this way, the result set returned by MySQL SELECT statement will be divided into groups.ExampleFollowing is a good example to understand it −We have a table named ‘employees’ as follows −mysql> Select * from employees; +------+-------------+--------+------------+ | id   | designation | Salary | DoJ        | +------+-------------+--------+------------+ ... Read More

As we know that GROUP BY clause in a SELECT statement can divide the result set, returned by MySQL, in groups. Now if we want to return only some specific groups then need to apply filtering criteria at the group level. It can be done by using HAVING clause inside the GROUP BY clause. The example below will demonstrate it −ExampleSuppose we want to return only the group which is having an average salary of 55000 then we need to use filtering criteria as follows in HAVING clause −mysql> Select count(*), AVG(salary), Designation from employees GROUP BY designation having AVG(salary) ... Read More

We can use COUNT(*) and GROUP BY clause to find out the repetition of a value in the column. Following is the example, using COUNT(*) and GROUP BY clause on ‘Name’ column of table ‘Student’, to demonstrate it −mysql> select count(*), name from student group by name; +----------+---------+ | count(*) | name    | +----------+---------+ | 1        | Aarav   | | 2        | Gaurav  | | 1        | Harshit | +----------+---------+ 3 rows in set (0.00 sec)The result set of above query shows that which value is repeated ... Read More