First you need to reach the location of “my.cnf” with the help of below query for MySQL Utilities. The query is as follows −mysql> select @@datadir;The following is the output that display where “my.conf” is −+---------------------------------------------+ | @@datadir                                   | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Follow the above path in order to open the “my.cnf” file. The snapshot of “my.cnf” location is as follows −Now you can put the below MySQL Utilities in my.cnf which is as ... Read More

Use the STR_TO_DATE() function from MySQL to set a date format for displaying DD/MM/YYYY date. The syntax is as follows −SELECT STR_TO_DATE(yourColumnName, ’%d/%m/%Y) as anyVariableName from yourTableName.To understand the above syntax, let us create a table −mysql> create table DateFormatDemo    −> (       −> IssueDate varchar(100)    −> ); Query OK, 0 rows affected (0.54 sec)Inserting some string dates into the table. The query to insert date is as follows −mysql> insert into DateFormatDemo values('26/11/2018'); Query OK, 1 row affected (0.14 sec) mysql> insert into DateFormatDemo values('27/11/2018'); Query OK, 1 row affected (0.18 sec) mysql> ... Read More

To fetch fields with multiple values, use LIKE with OR in MySQL −select *from yourTableName where yourColumnName like ‘%AnyStringValue’ or yourColumnName like ‘%AnyStringValue’ or yourColumnName like ‘%AnyStringValue’ ……...N;You can understand with the help of a table −mysql> create table LikeDemo    −> (       −> Hobby varchar(200)    −> ); Query OK, 0 rows affected (1.71 sec)Insert some records in the table with the help of insert command. The query to insert records in the table is as follows −mysql> insert into LikeDemo values('Reading Book'); Query OK, 1 row affected (0.13 sec) mysql> insert into LikeDemo values('Playing ... Read More

You can get timestamp with the help of current_timestamp, now() and current_timestamp().Case 1 − Using current_timestamp()The query is as follows −mysql> SELECT CURRENT_TIMESTAMP();The following is the output displaying timestamp −+---------------------+ | CURRENT_TIMESTAMP() | +---------------------+ | 2018-11-29 16:09:31 | +---------------------+ 1 row in set (0.00 sec)Case 2 − Using now()The query is as follows −mysql> select now();The following is the output −+---------------------+ | now()               | +---------------------+ | 2018-11-29 16:09:38 | +---------------------+ 1 row in set (0.00 sec)You can get integer UNIX timestamp with the help of below query −mysql> select unix_timestamp();The following is the ... Read More

To skip first 10 results, use “limit offset”. The syntax is as follows −select *from yourTableName limit 10 offset lastValue;Let us create a table to understand the above syntax. The following is the query to create a table −mysql> create table SkipFirstTenRecords    −> (       −> StudentId int,       −> StudentName varchar(200)    −> ); Query OK, 0 rows affected (0.53 sec)Now you can insert some records in the table with the help of insert command. The query is as follows −mysql> insert into SkipFirstTenRecords values(100, 'John'); Query OK, 1 row affected (0.12 sec) ... Read More

To generate a range of numbers in MySQL, you can use stored procedure. Firstly, we need to create a table. After that, we will create a stored procedure that generates a range of number from 10 to 1.The following is the query to create a table −mysql> create table GeneratingNumbersDemo    −> (       −> Number int    −> ); Query OK, 0 rows affected (0.55 sec)Now you can create a stored procedure that stores a range of numbers in the table. The following is the query to create a stored procedure −mysql> delimiter // mysql> CREATE PROCEDURE ... Read More

You can add unique key to existing table with the help of alter command. The syntax is as follows −ALTER TABLE yourTableName ADD CONSTRAINT yourConstraintName UNIQUE(yourColumnName1, yourColumnName2, ............N);To understand the above concept, let us create a table with some columns. The query to create a table −mysql> create table MovieCollectionDemo    −> (       −> MovieId int,       −> MovieDirectorName varchar(200),       −> NumberOfSongs int unsigned    −> ); Query OK, 0 rows affected (0.62 sec)Now you can check the table does not have any unique constraint. The query to check unique constraint is ... Read More

You can change table engine with the help of alter command. The syntax is as follows −alter table yourTableName ENGINE = yourEngineName;To understand the above syntax let us create a table with engine MyISAM. Later you can change any other engine. The following is the query to create a table.mysql> create table ChangeEngineTableDemo −> (    −> MovieId int,    −> MovieName varchar(100),    −> IsPopular bool −> )ENGINE = 'MyISAM'; Query OK, 0 rows affected (0.37 sec)Look at the above query, the table engine is MyISAM, now you can change it to any other engine. Here, we will change ... Read More

MySQL DAYOFWEEK() function returns 1 for Sunday, 2 for Monday and so on for day of week. Let us see an example by first creating a table −mysql> create table DayOfWeekDemo −> (    −> Issuedate datetime −> ); Query OK, 0 rows affected (0.52 sec)Inserting date in the table with the help of insert command. The query is as follows −mysql> insert into DayOfWeekDemo values(date_add(curdate(), interval 5 day)); Query OK, 1 row affected (0.52 sec) mysql> insert into DayOfWeekDemo values(date_add(curdate(), interval 6 day)); Query OK, 1 row affected (0.13 sec) mysql> insert into DayOfWeekDemo values(date_add(curdate(), interval 7 ... Read More

To insert the date with date formats, use the str_to_date() function with date in single quotes. The following is the syntax −insert into yourTableName values(Value1, value2, ......ValueN, str_to_date(‘anyDate’, ’%Y-%m-%d’));Here are the Date Formats in MySQL −FormatDescription%aAbbreviated weekday name (Sun to Sat)%bAbbreviated month name (Jan to Dec)%cNumeric month name (0 to 12)%DDay of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)%dDay of the month as a numeric value (01 to 31)%eDay of the month as a numeric value (0 to 31)%fMicroseconds (000000 to 999999)%HHour (00 to 23)%hHour (00 to 12)%IHour (00 to 12)%iMinutes (00 to 59)%jDay ... Read More