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)

To do select into in MySQL, use CREATE TABLE SELECT command. The syntax is as follows −CREATE TABLE yourTableName SELECT *FROM yourOriginalTableName;To understand, let us first create a table −mysql> create table SelectIntoDemo -> ( -> Id int, -> Name varchar(200) -> ); Query OK, 0 rows affected (0.50 sec)Let us insert some records into the table with the help of insert command. The query is as follows −mysql> insert into SelectIntoDemo values(1, 'Bob'), (2, 'Carol'), (3, 'David'); Query OK, 3 rows affected (0.15 sec) Records: 3 Duplicates: 0 Warnings: 0Displaying all records with the help of select statement. The ... Read More

To select last few days, use DATE_ADD() function in MySQL. The syntax is as follows −select date_add(curdate(), interval - anyIntgegerValue day);Or you can DATE_SUB() from MySQL.select date_sub(curdate(), interval anyIntgegerValue day);Or you can use the following syntax −select curdate() - interval anyIntgegerValue day;Here is the example of all syntaxes shown above to select last few days.Case 1 − Use of DATE_ADD() functionThe query is as follows −mysql> select date_add(curdate(), interval -6 day);Here is the output −+-------------------------------------+ | date_add(curdate(), interval -6 day) | +-------------------------------------+ | 2018-11-20                          | +-------------------------------------+ 1 row ... Read More