You can strip time component from datetime with the help of DATE() function. The syntax is as follows −SELECT DATE(yourColumnName) from yourTableName;To understand the above concept, let us first create a table −mysql> create table StripComponentDatetimeDemo -> ( -> YourDateTime datetime -> ); Query OK, 0 rows affected (0.60 sec)Let us now insert some records in the table. The query is as follows −mysql> insert into StripComponentDatetimeDemo values(date_add(now(), interval 1 day)); Query OK, 1 row affected (0.13 sec)Display records with the help of select statement. The query is as follows displaying ... Read More

To round down to nearest integer, use FLOOR() function from MySQL. The syntax is as follows −SELECT FLOOR(yourColumnName) from yourTableName;Let us first create a table −mysql> create table FloorDemo -> ( -> Price float -> ); Query OK, 0 rows affected (0.57 sec)Insert records to column Price. The query to insert records is as follows −mysql> insert into FloorDemo values(5.75); Query OK, 1 row affected (0.21 sec) mysql> insert into FloorDemo values(5.23); Query OK, 1 row affected (0.31 sec) mysql> insert into FloorDemo values(5.50); Query OK, 1 row affected (0.12 sec)Display ... Read More

To part DATE and TIME from DATETIME, you can use the DATE_FORMAT() method from MySQL. The syntax is as follows −SELECT DATE_FORMAT(yourColumnName, '%Y-%m-%d') VariableName, DATE_FORMAT(yourColumnName, '%H:%i:%s') VariableName from yourTableName;To understand the above method DATE_FORMAT(), let us create a table with data type “datetime”.Creating a table −mysql> create table DateAndTimePartDemo -> ( -> YourDateandtime datetime -> ); Query OK, 0 rows affected (0.56 sec)Now, I am inserting current date and time using now(). The query is as follows −mysql> insert into DateAndTimePartDemo values(now()); Query OK, 1 row affected (0.37 sec)Display the record ... Read More

To know a MySQL table is using MyISAM or InnoDB engine, you can use the command show status table. The syntax is as follows −SHOW TABLE STATUS from yourDatabaseName LIKE ‘yourTableName’.The above syntax tells about the specific table engine. Now you can apply the above syntax to know whether the MySQL table engine is using MyISAM or InnoDB.Here, I have database ‘business’ and table ‘student’. The query is as follows −mysql> show table status from business like 'student';The following displays the engine our table ‘student’ is using −+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | Name   | Engine | Version | Row_format | Rows | ... Read More

To find my.cnf on Windows system, first open the command prompt with the help of shortcut key Windows + R (run). The snapshot is as follows −Type “services.msc” on command prompt and press ENTER as shown in the following screenshot −Now, a new wizard will open. The snapshot is as follows −Now, search for MySQL. The snapshot is as follows −Right click on “MySQL80” and select “Properties” as in the following screenshot −As you can see in the above screenshot, “Path to executable” tells about the location of my.cnf on Windows.

You can create user if it does not exist with the help of “create user” command. The command will work on MySQL version 5.7.6 and above. The syntax is as follows −mysql> CREATE USER IF NOT EXISTS 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';Apply the above syntax to create a user if it does not exist. The query is as follows −mysql> CREATE USER IF NOT EXISTS 'Smith'@'localhost' IDENTIFIED BY 'Smith123456'; Query OK, 0 rows affected (0.29 sec)To check the new user is created or not, use the below query −mysql> SELECT User FROM mysql.user;The following is the output −+------------------+ | User ... Read More

To display MySQL host via SQL command, use system variable "hostname".The following is the query to display the host −mysql> select @@hostname;Here is the output −+-----------------+ | @@hostname | +-----------------+ | DESKTOP-QN2RB3H | +-----------------+ 1 row in set (0.00 sec)Or you can use "show variables" command to show MySQL host via SQL command.show variables where Variable_name like '%host%';The following is the output −+-------------------------------+-----------------+ | Variable_name                 | Value           | +-------------------------------+-----------------+ | host_cache_size               | 279             | | hostname                      | DESKTOP-QN2RB3H | | performance_schema_hosts_size | -1 | | report_host                 | | +-------------------------------+-----------------+ 4 rows in set (0.07 sec)

Suppose the LIMIT is 4 and OFFSET is 6 then it will return the rows from 7 to 10 i.e. will end with row 10. The LIMIT 4 and OFFSET 6 returns row 7, 8, 9, 10.You can understand the above concept by implementing LIMIT and OFFSET. Let us create a table.mysql> create table LimitOffsettable -> ( -> Id int -> ); Query OK, 0 rows affected (0.60 sec)Let us insert some records in the table. The query is as follows −Mysql> insert into LimitOffsettable values(1); Query OK, 1 row affected ... Read More

Yes, you can use the LOWER() or LCASE() from MySQL to convert a string to lowercase. Both methods can be used to convert the string into lowercase.Here is the syntax of LOWER() −lower(‘yourStringValue);Or you can use LCASE().The syntax is as follows −lcase(‘yourStringValue);Let us see an example of LOWER(). The query is as follows −mysql> select lower('JOhN');Here is the output −+---------------+ | lower('JOhN') | +---------------+ | john | +---------------+ 1 row in set (0.00 sec)Let us see an example of LCASE(). The query is as follows −mysql> select lcase('JOhN');The following is the ... Read More

To insert only date value, use curdate() in MySQL. With that, if you want to get the entire datetime, then you can use now() method.Let us first create a table −mysql> create table CurDateDemo -> ( -> ArrivalDate datetime -> ); Query OK, 0 rows affected (0.74 sec)Now you can insert only date with the help of curdate() method −mysql> insert into CurDateDemo values(curdate()); Query OK, 1 row affected (0.15 sec)Display the inserted date with the help of select statement. The query is as follows −mysql> select *from CurDateDemo;Here is the ... Read More