When MySQL SUM() function used with SELECT statement that returns no matching rows then there is nothing to evaluate and it returns NULL as output. Sometimes, we thought it must return 0 as output but 0 is a number itself and for no matching rows it not significant to return 0 hence it returns NULL. To understand the above concept, consider an ‘employee_tbl’ table, which is having the following records −mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id   | name | work_date  | daily_typing_pages | +------+------+------------+--------------------+ | 1    | John | 2007-01-24 | 250         ... Read More

We can use FIELD() function to find the index position of a string stored as a record in MySQL table’s column. To demonstrate it we are using the table named ‘websites’ having the following dataExamplemysql> Select * from websites; +----+---------------+------------------------+ | Id | Purpose       | Webaddress             | +----+---------------+------------------------+ | 1  | For tutorials | www.tutorialspoint.com | | 2  | For searching | www.google.co.in       | | 3  | For email     | www.gmail.com          | +----+---------------+------------------------+ 3 rows in set (0.00 sec)Now, suppose if we ... Read More

For calculating the sum of only dissimilar values of the column we can use ‘DISTINCT’ keyword along with the name of the column. To understand SUM() function for dissimilar values, consider an ‘employee_tbl’ table, which is having the following records −mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id   | name | work_date  | daily_typing_pages | +------+------+------------+--------------------+ | 1    | John | 2007-01-24 | 250                | | 2    | Ram  | 2007-05-27 | 220                | | 3    | Jack | 2007-05-06 | 170 ... Read More

With the help of SHOW TABLES command, we can get only the name having no other information about the tables. For example, we can see the list of tables in a database named tutorial as follows −mysql> show tables; +--------------------+ | Tables_in_tutorial | +--------------------+ | student            | +--------------------+ 1 row in set (0.00 sec)

When we use MySQL COUNT() function to count the values stored in a column which also stored some NULL values then MySQL ignores the NULL and returns the result for only non-NULL values. To understand it, we are using the data, as follows, from table ‘Employee’ −mysql> Select * from Employee; +----+--------+--------+ | ID | Name   | Salary | +----+--------+--------+ | 1  | Gaurav | 50000  | | 2  | Rahul  | 20000  | | 3  | Advik  | 25000  | | 4  | Aarav  | 65000  | | 5  | Ram    | 20000  | | 6  | ... Read More

MySQL ADDTIME() function can be used to add a time interval in the data stored in a column of the table. The syntax of this function is ADDTIME(date, ‘expression unit’). It can be demonstrated by following the example which uses the data from table ‘collegedetail’. mysql> Select estb, ADDTIME(estb, '05:04:25')AS 'Date with time' From collegedetail WHERE ID = 111; +------------+---------------------+ | estb | Date with time | +------------+---------------------+ | 2010-05-01 | 2010-05-01 05:04:25 | +------------+---------------------+ 1 row in set (0.02 sec) Here, ‘estb’ is a column of table ‘collegedetail’ having the date as values.

Two functions can be used for this purpose and in both the functions we need to provide column name as an argument along with INTERVAL keyword. The functions are as follows −DATE_ADD() functionThe syntax of this function is DATE_ADD(date, INTERVAL expression unit). It can be demonstrated by following the example which uses the data from table ‘collegedetail’ −mysql> Select estb, DATE_ADD(estb, INTERVAL 10 DAY) from collegedetail; +------------+---------------------------------+ | estb | DATE_ADD(estb, INTERVAL 10 DAY)       | +------------+---------------------------------+ | 2010-05-01 | 2010-05-11                      | | 1995-10-25 | 1995-11-04     ... Read More

We can check the status of tables in a database with the help of show table status statement. For example, in the database named tutorial, by executing this statement we can get the status of tables as follows −mysql> show table status \G*************************** 1. row ***************************            Name: student          Engine: InnoDB         Version: 10      Row_format: Compact            Rows: 0  Avg_row_length: 0     Data_length: 16384 Max_data_length: 0    Index_length: 0       Data_free: 7340032  Auto_increment: NULL     Create_time: 2017-10-24 09:34:29   ... Read More

By using –v option in batch mode, the MySQL statements can be printed along with output. For example, after running the same query in batch mode with –v option we will get the statement printed along with output.C:\Program Files\MySQL\bin>mysql -u root -p gaurav < hh.sql -v Enter password: ***** -------------- select * from hh -------------- id 1 2It is showing the statement select * from hh which is written in the file hh.sql.

We can get the MySQL output format in batch mode with the help of –t option. For example, after running the same query in batch mode with –t option we will get the output like interactive format.ExampleC:\Program Files\MySQL\bin>mysql -u root -p gaurav < hh.sql -t Enter password: *****Output+------+ | id   | +------+ | 1    | | 2    | +------+