To create a MySQL table with MyISAM engine, we can use ENGINE command. Let us first create a table using CREATE command. mysql> create table StudentRecordWithMyISAM -> ( -> Id int, -> StudentName varchar(100), -> StudentAge int -> )ENGINE=MyISAM; Query OK, 0 rows affected (0.26 sec) Above, we have set the ENGINE as “MyISAM”. To check how many columns are present in the table, use DESC command. mysql> DESC StudentRecordWithMyISAM; The following is the output. +-------------+--------------+------+-----+---------+-------+ | Field ... Read More

To create a table with InnoDB engine, we can use the ENGINE command. Here is the query to create a table.mysql> create table EmployeeRecords - > ( - > EmpId int, - > EmpName varchar(100), - > EmpAge int, - > EmpSalary float - > )ENGINE=INNODB; Query OK, 0 rows affected (0.46 sec)We have set the ENGINE as INNODB above.Check the full description about the table using the DESC command.mysql> DESC EmployeeRecords;The following is the output.+-----------+--------------+------+-----+---------+-------+ | Field     | Type         | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | EmpId     ... Read More

We can enable the MySQL slow query log with the help of SET statement.The following is the syntax.SET GLOBAL slow_query_log = 'Value';In the above syntax, value can be filled with ON/OFF. To enable slow query log, let us see the query.mysql> SET GLOBAL slow_query_log = 'ON'; Query OK, 0 rows affected (0.00 sec)To check if the slow query is ON, implement the following query −mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%'; Here is the output.+---------------------+--------------------------+ | Variable_name       | Value                    | +---------------------+--------------------------+ | slow_launch_time    | 2       ... Read More

Function-based index wasn’t possible in MySQL versions below 5.6. Firstly, to create function-based index in MySQL, we will create a table. mysql> create table FunctionIndexDemo - > ( - > FirstName varchar(100) - > ); Query OK, 0 rows affected (0.70 sec) Let us see the syntax to create a function based index. create index index_name on yourTableName (column_name(IntegerSize)); Here is the query. mysql> create index indFirstName on FunctionIndexDemo (FirstName(6)); Query OK, 0 rows affected (0.56 sec) Records: 0 Duplicates: 0 Warnings: 0 ... Read More

We can escape apostrophe (‘) in MySQL in the following two ways − We can use backslash. We can use single quotes twice (double quoted) Using backslash Let us first create a table. mysql> create table SingleQuotesDemo - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (1.16 sec) Following direct usage does not give the desired result for name “John’s”. mysql> insert into SingleQuotesDemo values(1, 'John's'); '> Let us now use backslash. ... Read More

InnoDB is a general-purpose storage engine that balances high reliability and performance. Since MySQL 5.6, InnoDB is the default MySQL storage engine. InnoDB can be used for various purposes. Here are some of them − It can be used for transaction purpose i.e. all ACID properties. InnoDB can be used for row level locking, that means it gives higher performance as compared to MyISAM. InnoDB can be used for both data and index for a large buffer pool. InnoDB can be used when we need better performance than MyISAM. MyISAM is the default storage engine for the MySQL ... Read More

Key is synonymous to an index. If you want to create an index for a column, then use ‘Key’. As stated in the official docs: KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems. The key can be used with Primary Key: Let us first create a table. Here is the query to set primary key for a column “id”. mysql> create table KeyDemo -> ( ... Read More

We can convert MySQL date and time to Unix Timestamp with the help of function UNIX_TIMESTAMP(). The following is the query. mysql> SELECT UNIX_TIMESTAMP(STR_TO_DATE('Oct 19 2018 10:00PM', '%M %d %Y %h:%i%p')); After running the above query we will not get the output in date format as shown in the below output. The output shown here is a Unix Timestamp. +------------------------------------------------------------------------+ | UNIX_TIMESTAMP(STR_TO_DATE('Oct 19 2018 10:00PM', '%M %d %Y %h:%i%p')) | +------------------------------------------------------------------------+ | ... Read More

We can remove the leading and trailing whitespaces from MySQL with the help of trim() function.The following is the syntax.mysql> SELECT TRIM(' AnyStringWithWhitespaces ');Let us now implement the above syntax in the below query.mysql> SELECT TRIM(' Leading And Trailing whitespaces Demo '); Here is the output that removes the whitespaces.+---------------------------------------+ | TRIM(' Leading And Trailing whitespaces Demo ') | +---------------------------------------+ | Leading And Trailing whitespaces Demo | +---------------------------------------+ 1 row in set (0.00 sec)Let us now see another way to remove the leading and trailing whitespaces.Firstly, let us create a new table.mysql> create table TrimDemo2 -> ( -> name varchar(200) ... Read More

We can use DISTINCT and COUNT together in a single MySQL query. Firstly, let us create a table. The CREATE command is used to create a table. mysql> create table DistCountDemo - > ( - > id int, - > name varchar(100), - > age int - > ); Query OK, 0 rows affected (0.48 sec) Records are inserted with the help of INSERT command. mysql> insert into DistCountDemo values(1, 'John', 23); Query OK, 1 row affected (0.11 sec) mysql> insert ... Read More