To cast DATETIME as a DATE in MySQL, use the CAST() function. The syntax is as follows −select cast(yourColumnName as Date) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table −mysql> create table ConvertDatetimeToDate −> (    −> YourDatetime datetime −> ); Query OK, 0 rows affected (0.95 sec)Inserting datetime into the table with the help of insert command. The query is as follows −mysql> insert into ConvertDatetimeToDate values(date_add(now(), interval 1 day)); Query OK, 1 row affected (0.17 sec) mysql> insert into ConvertDatetimeToDate values(date_add(now(), interval -1 day)); Query OK, 1 row affected (0.15 sec) ... Read More

To list all triggers in a MySQL database, you can use the SHOW command. The query is as follows −mysql> show triggers;The following is the output −+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+ | Trigger        | Event  | Table                | Statement                                                          | Timing | Created                | sql_mode                             ... Read More

You can typecast NULL to 0 with the help of IFNULL() function. The syntax is as follows −select ifnull(yourColumnName) as anyVariableName from yourTableName;To understand the above concept, let us first create a table −mysql> create table TypecastDemo    −> (       −> AccountNumber int    −> ); Query OK, 0 rows affected (0.84 sec)Let us insert some records with NULL value. The query to insert records is as follows −mysql> insert into TypecastDemo values(NULL); Query OK, 1 row affected (0.13 sec) mysql> insert into TypecastDemo values(1234); Query OK, 1 row affected (0.14 sec) mysql> insert into ... Read More

The NOW() function gives current datetime as a timestamp while CURDATE() gives only current date, not time.Now let us work on both the functions with the help of select statement. The query is as follows −The following is a demo of NOW() function −mysql> select NOW();The following is the output −+---------------------+ |             now() | +---------------------+ | 2018-11-27 15:17:01 | +---------------------+ 1 row in set (0.00 sec)A demo of CURDATE().mysql> select CURDATE();The following is the output that displays only date, not time −+------------+ | curdate() | +------------+ | 2018-11-27 | +------------+ 1 row in set ... Read More

To get the random integer range, use the rand() function. The query to create a table −mysql> create table RandomIntegerDemo −> (    −> Number int −> ); Query OK, 0 rows affected (0.61 sec)Inserting records into table. The query is as follows −mysql> insert into RandomIntegerDemo values(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14); Query OK, 14 rows affected (0.14 sec) Records: 14 Duplicates: 0 Warnings: 0Now you can display all records with the help of select statement. The query is as follows −mysql> select *from RandomIntegerDemo;The following is the output displaying integers ... Read More

To add leading zero to some value, use the LPAD() function of MySQL. The syntax is as follows −select lpad(yourColumnName, lengthofColumnValue+1, 0) from yourTableName;Here is an example of LPAD().mysql> select lpad('98765432', 9, 0);The following is the output −+----------------------+ | lpad('98765432', 9, 0) | +----------------------+ | 098765432            | +----------------------+ 1 row in set (0.00 sec)To check it in a live example, let us first create a table −mysql> create table leadingZeroDemo −> (    −> Id varchar(200) −> ); Query OK, 0 rows affected (0.63 sec)Now insert some records into the table with the help of ... Read More

You can get the MySQL table columns data type with the help of "information_schema.columns".The syntax is as follows −SELECT DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where table_schema = 'yourDatabaseName' and table_name = 'yourTableName'To understand the above syntax, let us first create a table −mysql> create table DataTypeDemo −> (    −> Id int,    −> Address varchar(200),    −> Money decimal(10, 4) −> ); Query OK, 0 rows affected (0.60 sec)Apply the above syntax to get the MySQL columns data type. The query is as follows −mysql> select data_type from information_schema.columns where table_schema = 'business' and able_name = 'DataTypeDemo';The following is the output ... Read More

To add a day to datetime field, use the DATE_ADD() function. The syntax is as follows −SELECT DATE_ADD(yourColumnName, interval yourIntegerValue day) as anyVariableName from yourTableName;Let us first create a table −mysql> create table AddOneDayDemo −> (    −> YourDay datetime −> ); Query OK, 0 rows affected (1.37 sec)Insert current date with the help of curdate() and after that use date_add() function to add a day.To insert a day into the table, the following is the query −mysql> insert into AddOneDayDemo values(curdate()); Query OK, 1 row affected (0.17 sec)Display records with the help of select statement. The query is as ... Read More

To insert data into MySQL database, use INSERT command. The syntax is as follows −INSERT INTO yourTableName(yourColumnName1, ........yourColumnNameN)values(Value1, Value2, ......ValueN);Here, I am inserting records in a MySQL database with JAVA programming language. First, we need to create a table in MySQL. The query is as follows −mysql> create table InsertDemo    -> (    -> Id int,    -> Name varchar(200),    -> Age int    -> ); Query OK, 0 rows affected (0.97 sec)Now, here is the JAVA code to insert records into MySQL database with table InsertDemo. . Before that, we will establish a Java Connection to our ... Read More

To know where MySQL store database files, you can use the variable @@datadir. The query is as follows −mysql> select @@datadir;The following is the output that displays the path −+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Here is the snapshot where MySQL store database files i.e. the same path we got above −Now, let us verify whether ... Read More