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 the system variable hostname. Query 1 The following is the query to display the host − mysql > select @@hostname; Output Here is the output − +-----------------+ | @@hostname | +-----------------+ | DESKTOP-QN2RB3H | +-----------------+ 1 row in set (0.00 sec) Query 2 Or you can use "show variables" command to show MySQL host via SQL command. show variables where Variable_name like '%host%'; Output 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

To show a MySQL warning, you can use the below syntax −SHOW WARNINGS;The above syntax only displays the immediate warning from MySQL prompt. Suppose you run another query between them or you have lost the MySQL connection, then SHOW WARNINGS will not work.Here is the query to display warnings −mysql> SHOW WARNINGS;Here is the output that displays immediate warning −+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message ... Read More

To subtract 30 days from current datetime, first we need to get the information about current date time, then use the now() method from MySQL. The now() gives the current date time.The method to be used for this is DATE_SUB() from MySQL. Here is the syntax to subtract 30 days from current datetime.The syntax is as follows −DATE_SUB(NOW(), INTERVAL 30 DAY);The above syntax calculates the current datetime first and in the next step, subtracts 30 days. Let us first seethe query to get the current datetime −mysql> select now();Here is the output −+---------------------+ | now() ... Read More