To insert current date/ time in MySQL, use the now() function. Let us now see an example.At first, we will create a table. The CREATE command is used to create a table.mysql > create table CurrentDateTime -> ( -> CurrentTime datetime -> ); Query OK, 0 rows affected (1.14 sec)Syntax to insert the current date/time with the help of insert command and now()mysql> insert into CurrentDateTime values(now()); Query OK, 1 row affected (0.11 sec)To check that the current date/ time is inserted in the table or not, use the select command.mysql> select *from CurrentDateTime; The following is the output that ... Read More

Let us see an example to get the count of each distinct value in a column. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table DistinctDemo1 - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (0.43 sec) Inserting records mysql> insert into DistinctDemo1 values(1, 'John'); Query OK, 1 row affected (0.34 sec) mysql> insert into DistinctDemo1 values(2, 'John'); Query OK, 1 row affected (0.20 sec) ... Read More

To return Sum as ‘0’ if no values are found, use IFNULL or COALESCE commands. The following is the syntax for IFNULL. SELECT IFNULL(SUM(NULL), 0) AS aliasName; Let us now implement the above syntax in the following query. mysql> SELECT IFNULL(SUM(NULL), 0) AS SUMOFTWO; The following is the output of the above query, which returns 0. +----------+ | SUMOFTWO | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) Here is the syntax for COALESCE. mysql> SELECT COALESCE(SUM(NULL),0) as SUMOFTWO; The following is the output that returns 0 using the SUM() function. +----------+ | SUMOFTWO | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)

Let us see some of the ways to check if a table exist in MySQL or not.Using SHOWThe SHOW command displays all the tables.SHOW tables;Here is the output.+--------------------------+ | Tables_in_business       | +--------------------------+ | addcheckconstraintdemo   | | addcolumntable           | | addconstraintdemo        | | alphademo                | | autoincrement            | | autoincrementtable       | | backticksymbol           | | bookindexes              | | chardemo         ... Read More

The composite index can be used when we are using sub queries. The advantages of using composite index are in case of. Joining Filtering Selecting The following is the syntax of index. index(column_name1, column_name2, column_name3, column_name4, ............................N) Let us create a table first and within that we have set index. mysql> create table MultipleIndexDemo - > ( - > id int, - > FirstName varchar(100), - > LastName varchar(100), - > Address varchar(200), - > index(id, LastName, ... Read More

To do a batch insert, we need to use all column names with parenthesis, separated by ‘, ’. Let us see an example. First, we will create a table. The following is the CREATE command to create a table. mysql> CREATE table MultipleRecordWithValues - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (0.88 sec) The following is the syntax of batch insert. INSERT into yourTableName values(column1, column2, ....N), (column1, column2, ....N), (column1, column2, ....N), ...........N; ... Read More

We can show the result as a comma separated list with the help of the ‘concat()’ function with the parameter ‘, ’. Let us see an example now. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table CommaSeperateDemo - > ( - > Id int, - > FirstName varchar(100), - > LastName varchar(100) - > ); Query OK, 0 rows affected (0.93 sec) Inserting records mysql> insert into CommaSeperateDemo values(1, 'John', 'Taylor'); Query ... Read More

Let us now see the following methods to calculate the time difference between two timestamps in seconds. Method The following is the query to calculate the difference between two timestamps. mysql> SELECT TIMESTAMPDIFF(SECOND, '2018-10-17 11:51:55', '2018-10-17 11:51:58'); The following is the output in seconds. +---------------------------------------------------------------------+ | TIMESTAMPDIFF(SECOND, '2018-10-17 11:51:55', '2018-10-17 11:51:58') | +---------------------------------------------------------------------+ | ... Read More

The Trim() function is used to remove new line characters from data rows in MySQL. Let us see an example. First, we will create a table. The CREATE command is used to create a table.mysql> create table tblDemotrail - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (0.57 sec)Let us now insert some records.mysql> insert into tblDemotrail values(1, 'John '); Query OK, 1 row affected (0.15 sec) mysql> insert into tblDemotrail values(2, ' Carol'); Query OK, 1 row affected (0.32 sec) mysql> insert into tblDemotrail values(3, ' Sam ... Read More

To store username and passwords safely in MySQL database, we can use MD5().Let us see an example. First, we will create a table. The CREATE command is used to create a table.mysql> create table UserNameAndPasswordDemo - > ( - > U_Id int(10) unsigned NOT NULL AUTO_INCREMENT, - > UserId varchar(255) DEFAULT NULL, - > UserPassword varchar(255) DEFAULT NULL, - > primary key(U_Id), - > UNIQUE KEY `UserId` (`UserId`) - > ); Query OK, 0 rows affected (0.61 sec)Inserting records and safely storing passwords with the help of MD5().mysql> INSERT INTO UserNameAndPasswordDemo(UserId, UserPassword) VALUES ('John@gg.com', MD5('john123')); Query OK, 1 row affected ... Read More