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

Cast DECIMAL to INT with the help of FLOOR() function. The syntax is as follows −SELECT FLOOR(yourColumnName) from yourTableName where condition;Let us first create a table. The following is the query to create a table.mysql> create table DecimalToIntDemo -> ( -> Amount DECIMAL(3, 1) -> ); Query OK, 0 rows affected (0.88 sec)Now you can insert records into the table with the help of insert command. The query is as follows −mysql> insert into DecimalToIntDemo values(12.5); Query OK, 1 row affected (0.23 sec) mysql> insert into DecimalToIntDemo values(50.4); Query OK, 1 ... Read More

You can count all rows per table with the help of aggregate function count (TABLE_ROWS) from informatio_schema.tables. The syntax is as follows −SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'yourDatabaseName';Now you can apply the above syntax to get all rows per table. The query is as follows −mysql> SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'business';Here is the output −+------------------------------------------------------------------+------------+ | TABLE_NAME                                                       | TABLE_ROWS | +------------------------------------------------------------------+------------+ | accentsearchdemo       ... Read More

To get the creation date of MySQL table, use the information_schema. The syntax is as follows −SELECT create_time FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'yourDatabaseName’ AND table_name = 'yourTableName';Apply the above syntax for your database and the table name. Here I am using the database ‘business’ and table name is ‘student’. The query is as follows −mysql> SELECT create_time FROM INFORMATION_SCHEMA.TABLES -> WHERE table_schema = 'business' -> AND table_name = 'student';The following is the output displaying the creation time of a table −+---------------------+ | CREATE_TIME ... Read More

To convert string to date in MySQL, you can use STR_TO_DATE() function. The syntax is as follows −select str_to_date(‘StringValue’, '%d, %m, %Y') as anyVariableName;Apply the above syntax in the following query wherein, we have a string value −mysql> SELECT STR_TO_DATE('26, 11, 2018', '%d, %m, %Y');The following is the output −+--------------------------------------+ | STR_TO_DATE('26, 11, 2018', '%d, %m, %Y') | +--------------------------------------+ | 2018-11-26 | +--------------------------------------+ 1 row in set (0.00 sec)Let us see another example, to ... Read More

To convert the DateTime value into string in MySQL, you can use the DATE_FORMAT() function. The syntax is as follows −select date_format(yourColumnName, ‘%d %m %y’) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table DateAsStringDemo -> ( -> YourDateTime datetime -> ); Query OK, 0 rows affected (0.57 sec)Inserting the date with the help of curdate() method. The query to insert date is as follows −mysql> insert into DateAsStringDemo values(curdate()); Query OK, 1 row affected ... Read More