You can achieve this with the help of INFORMATION_SCHEMA.COLUMNS. The syntax is as follows −SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `', LOWER(COLUMN_NAME), '` ', COLUMN_TYPE, ';') AS anyAliasName FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘yourDatabaseName’;Now use the database which has two tables. The database name is as follows “bothinnodbandmyisam”. This database is having the following tables −employeestudentThe description of the employee table is as follows −mysql> desc employee;The following is the output. Let’s say we have the following columns in the employee table which are not in lowercase −+--------------+-------------+------+-----+---------+-------+ | Field        | Type   ... Read More

Use the DATE_FORMAT(), not FORMATDATE() in MySQL to format date. The correct syntax is as follows −SE LECT *, DATE_FORMAT(yourDateCoumnName, ’yourFormat’) as anyAliasName FROM yourTableNameTo understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table DateFormatDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(10),    -> UserLoginDate date    -> ); Query OK, 0 rows affected (0.94 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DateFormatDemo(UserName, UserLoginDate) values('Mike', curdate()); Query OK, 1 ... Read More

The max_heap_table_size is a system variable that has both read/write property.Initially, max_heap_table_size has size 16 MB. First, check the value of max_heap_table_size, which is in bytes.The query is as follows −mysql> select @@max_heap_table_size;The following is the output −+-----------------------+ | @@max_heap_table_size | +-----------------------+ | 16777216 | +-----------------------+ 1 row in set (0.00 sec)Now let us how the value 16777216 byte = 16 MB −1 MB = 1024KB 1KB = 1024 Bytes 1MB = 1024*1024 bytes. To convert 16777216 byte to MB you need to divide 1024*1024. =16777216/(1024*1024) =16777216/1048576 ... Read More

You can achieve this with the help of CONCAT() along with REPLACE() function. To find the first occurrences you need to use INSTR() function.The syntax is as follows −UPDATE yourTableName SET UserPost = CONCAT(REPLACE(LEFT(yourColumnName, INSTR(yourColumnName, 'k')), 'k', 'i'), SUBSTRING(yourColumnName, INSTR(yourColumnName, 'k') + 1));To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UserInformation -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(10), -> UserPost text -> ); Query OK, ... Read More

You can achieve this with the help of ORDER BY CASE statement. The syntax is as follows −SELECT *FROM yourTableName ORDER BY CASE yourIntegerColumnName1 WHEN 2 THEN 1 ELSE 0 END DESC ,yourDateColumnName ASC;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table OrderByCaseDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> GroupId int,    -> ArrivalDate date,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.57 sec)Insert some records in the table using insert command. The query is as ... Read More

To extract filenames from a path MySQL, you can use SUBSTRING_INDEX(). The syntax is as follows −SELECT SUBSTRING_INDEX(ypurColumnName, '\', -1) as anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ExtractFileNameDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> AllProgrammingFilePath varchar(100) -> ); Query OK, 0 rows affected (0.50 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into ExtractFileNameDemo(AllProgrammingFilePath) ... Read More

There is no standard function to get week of month in MySQL. You need to use the following syntax −SELECT WEEK(yourDateColumnName, 5) - WEEK(DATE_SUB(yourDateColumnName, INTERVAL DAYOFMONTH(yourDateColumnName) - 1 DAY), 5) + 1 AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table FirstWeekOfMonth -> ( -> Id int NOT NULL AUTO_INCREMENT primary key, -> yourdate date -> ); Query OK, 0 rows affected (2.50 sec)Now you can insert some records in the table ... Read More

You need to use ORDER BY clause to sort. The syntax is as follows −SELECT *FROM yourTableName ORDER BY yourColumnName='yourValue' DESC, yourIdColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SortCertainValues    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> CountryName varchar(10),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.36 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SortCertainValues(Name, CountryName) values('Adam', 'US'); Query OK, 1 row ... Read More

Yes, you can use InnoDB and MyISAM tables in one database or combine both of them in a single database. This is the recommended way.Here is the demo of both MyISAM and InnoDB in a one database. The following is the database and both the table types InnoDB and MyISAM. The query to create a database is as follows −mysql> create database BothInnoDBandMyISAM; Query OK, 1 row affected (0.20 sec) mysql> use BothInnoDBandMyISAM; Database changedI have a database with name ‘BothInnoDBandMyISAM’.First the table has engine type InnoDB. The query to create a table with engine InnoDB is as follows −mysql> ... Read More

You need to use the CAST operator along with CONV() function. The CONV() function can be used to convert one base number system to another base system.For Example, The 16 is one base system and 10 is another base system. The 16 base system is hexadecimal and 10 is a decimal.The syntax is as follows −SELECT CAST(CONV('yourColumnName', 16, 10) AS UNSIGNED INTEGER) as anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table castTypeToBigIntDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,     ... Read More