This article will give you an overview of MySQL questions and answers that may be helpful for you in an interview or online test.
Question: Define REGEXP?
REGEXP is a pattern match in which matches pattern anywhere in the search value.
// Query to find all the names starting with 'na'
mysql> SELECT name FROM users WHERE name REGEXP '^na';
// Query to find all the names ending with 'na'
mysql> SELECT name FROM users WHERE name REGEXP 'na$';
// Query to find all the names, which contain 'na'
mysql> SELECT name FROM users WHERE name REGEXP 'na';
// Query to find all the names starting with a vowel and ending with 'na'
mysql> SELECT name FROM users WHERE name REGEXP '^[aeiou]|na$';
Question: What is the difference between the LIKE and REGEXP operators.
mysql> SELECT name FROM users WHERE name LIKE 'na';
mysql> SELECT name FROM users WHERE name REGEXP 'na';
If you were to try them both you'd discover that the first returns no data and the second returns one row. Why is this?
LIKE matches an entire column. If the text to be matched existed in the middle of a column value, LIKE would not find it and the row would not be returned (unless wildcard characters were used). REGEXP, on the other hand, looks for matches within column values, and so if the text to be matched existed in the middle of a column value, REGEXP would find it and the row would be returned. This is a very important distinction.
Question: Difference between CHAR and VARCHAR?
A CHAR field is a fixed length, and VARCHAR is a variable length field.
This means that the storage requirements are different - a CHAR always takes the same amount of space regardless of what you store, whereas the storage requirements for a VARCHAR vary depending on the specific string stored.
Question: What are the drivers in MySQL
Following are the drivers available in MySQL
- PHP Driver
- PERL Driver
- PYTHON Driver
- RUBY Driver
- C++ Wrapper
- JDBC Driver
- ODBC Driver
Question: What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?
TIMESTAMP column is updated with Zero when the table is created. UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever there is a change in other fields of the table.
Question: What is the difference between primary key and candidate key?
Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.
Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.
Question: What does myisamchk do
It compress the MyISAM tables, which reduces their disk or memory usage.
Question: How do you control the max size of a HEAP table?
Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size
Question: What is the difference between MyISAM Static and MyISAM Dynamic.
In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.
Question: What are federated tables?
Federated tables which allow access to the tables located on other databases on other servers.
Question: What, if a table has one column defined as TIMESTAMP
Timestamp field gets the current timestamp whenever the row gets altered.
Question: What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?
It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.
Question: How can we find out which auto increment was assigned on Last insert?
mysql_insert_id() will return the last value assigned by Auto_increment and it is not required to specify the table name.
Question: What is the difference between Unix timestamps and MySQL timestamps?
Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.
Question: How can we convert between Unix & MySQL timestamps?
UNIX_TIMESTAMP() is the command which converts from MySQL timestamp to Unix timestamp.
mysql> SELECT UNIX_TIMESTAMP('table_date_column') FROM 'table_name'
FROM_UNIXTIME() is the command which converts from Unix timestamp to MySQL timestamp.
mysql> SELECT FROM_UNIXTIME('table_date_column') FROM 'table_name'
Question: What are the column comparisons operators?
The =, <=, >=, <, >, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.
Question: How can we get the number of rows affected by query?
Number of rows can be obtained by
mysql> SELECT COUNT ('table_column_name') FROM 'table_name';
Question: Is Mysql query is case sensitive?
It is not case sensitive.
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> SeLect version(), current_date;
Question: What is the difference between BLOB AND TEXT?
A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB.
- MEDIUMBLOB and
A TEXT is a case-insensitive BLOB. The four TEXT types.
- MEDIUMTEXT and
The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.
Question: Where MyISAM table will be stored and also give their formats of storage?
Each MyISAM table is stored on disk in three formats:
The .frm file stores the table definition.
The data file has a .MYD (MYData) extension.
The index file has a .MYI (MYIndex) extension.