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

To delete all the records from a table in MySQL, use the TRUNCATE command. Let us fir see the syntax −TRUNCATE TABLE yourTableName.The above syntax will delete all the records from a table. Let us create a table to understand the above syntax −mysql> create table TruncateTableDemo −> (    −> BookId int    −> ,    −> BookName varchar(200) −> ); Query OK, 0 rows affected (0.54 sec)Inserting records in the table with the help of insert command. The query to insert records in the table are as follows −mysql> insert into TruncateTableDemo values(1001, 'C in Dept'); Query OK, ... Read More

To achieve this with LIKE operator, the following is the syntax −CREATE TABLE yourTableName2 LIKE yourTableName1;To understand the syntax, let us create a table and insert some records into it. The following is the query to create a table −mysql> create table Employee −> (    −> EmployeeId int    −> ,    −> EmployeeName varchar(100) −> ); Query OK, 0 rows affected (0.54 sec)Inserting records into the table with the help of insert command. The query is as follows −mysql> insert into Employee values(1, 'Carol'); Query OK, 1 row affected (0.18 sec) mysql> insert into Employee values(2, 'John'); ... Read More

You can move rows from one table to another with the help of INSERT INTO SELECT statement.The syntax is as follows −insert into yourDestinationTableName select *from yourOriginalTable where someConditionTo understand the above syntax. let us create a table. The following is the query to create a table −mysql> create table StudentTable    −> (       −> Id int,       −> Name varchar(100)    −> ); Query OK, 0 rows affected (0.65 sec)Now, I will create second table. The query is as follows −mysql> create table Employee    −> (       −> EmployeeId int   ... Read More

To extract year from date format, you can use in-built function YEAR() from MySQL. The query is as follows −mysql> SELECT YEAR(curdate()) as OnlyYearFromCurrentDate;The following is the output −+-------------------------+ | OnlyYearFromCurrentDate | +-------------------------+ | 2018 | +-------------------------+ 1 row in set (0.00 sec)Or you can do in the following way −mysql> SELECT YEAR(date(now())) as OnlyYearFromCurrentDate;The following is the output −+-------------------------+ | OnlyYearFromCurrentDate | +-------------------------+ | 2018 ... Read More

You can set primary key on an existing column in MySQL with the help of alter command.The syntax is as follows to add primary key to an existing column.ALTER TABLE yourTableName ADD PRIMARY KEY(yourColumnName);To set existing column as primary key, let us first create a table. The query to create a table −mysql> create table AddingPrimaryKeyDemo    −> (       −> UniversityId int,       −> UniversityName varchar(200)    −> ); Query OK, 0 rows affected (1.16 sec)Look at the above query, I haven’t added primary key. Let us check the same with the help of DESC ... Read More

To get the number of columns, use the aggregate function count(*) with information_schema table from MySQL. The syntax is as follows to find the number of columns −SELECT COUNT(*) as anyVariableName from INFORMATION_SCHEMA.COLUMNS where table_schema = ’yourDatabaseName’ and table_name = ’yourTableName’;To understand the above syntax, let us create a table with some columns. The following is the query to create a table −mysql> create table CountColumns −> (    −> Bookid int,    −> BookName varchar(200),    −> BookAuthorName varchar(200),    −> BookPublishedDate datetime −> ); Query OK, 0 rows affected (0.69 sec)Now, we have total 4 columns in my ... Read More

You can use SHOW VARIABLES command to display current configuration variables. The syntax is as follows −SHOW VARIABLES;If you want any specific information, then implement the LIKE operator. The syntax is as follows −SHOW VARIABLES LIKE ‘%AnySpecificInformation%’;Now we will implement the above syntax −mysql> show variables like '%variable%';The following is the output −+--------------------------------+------------------------------------------------------------------------------------------+ | Variable_name                  | Value                                                                       ... Read More