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

To check whether a field is null or empty in MySQL, use the IF() function in MySQL. The syntax is as follows −SELECT IF(yourColumnName IS NULL or yourColumnName = '', 'NULLId', yourColumnName) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table NullAndEmptyDemo -1> ( -> Id varchar(200) -> ); Query OK, 0 rows affected (0.66 sec)Let us now insert records into the table with the help of insert command. The query to insert records in the table is as follows. We have added null ... Read More

To change the default charset of a MySQL table, you can use the below syntax. The syntax is as follows −alter table yourTableName convert to character set yourCharsetName;Let us create a table and apply the above syntax to change the default charset. The query to create a table −mysql> create table CharsetDemo -> ( -> Id int, -> Name varchar(200), -> Age int -> ); Query OK, 0 rows affected (0.73 sec)Now you can change the charset of a table. The following is the query ... Read More

If you will install MySQL on your system, then you will get the default MySQL server port number i.e. 3306.To know the MySQL server port number, you can use the following query. Here, we have used the SHOW VARIABLES command. The query is as follows −mysql> SHOW VARIABLES WHERE Variable_Name = 'port';The following is the output −+---------------+-------+ | Variable_Name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.04 sec)