What is the difference between the LIKE and REGEXP operators?
LIKE and REGEXP operators are used to express with ^ and %.
SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";
1
2
SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";
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 –
TINYBLOB
BLOB
MEDIUMBLOB and
LONGBLOB
They all differ only in the maximum length of the values they can hold.
A TEXT is a case-insensitive BLOB. The four TEXT types
TINYTEXT
TEXT
MEDIUMTEXT and
LONGTEXT
They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.
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.
How MySQL Optimizes DISTINCT?
DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
1
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
How to enter Characters as HEX Numbers?
If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).
A HEX number string will be automatically converted into a character string, if the expression context is a string.
What is BLOB?
- BLOB stands for binary large object.
- It that can hold a variable amount of data.
There are four types of BLOB based on the maximum length of values they can hold:
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
What is TEXT?
TEXT is case-insensitive BLOB. The four types of TEXT are:
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
What is the difference between BLOB and TEXT?
- In BLOB sorting and comparison is performed in case-sensitive for BLOB values.
- In TEXT types sorting and comparison is performed case-insensitive.
How is MyISAM table stored?
MyISAM table is stored on disk in three formats.
- ‘.frm’ file – storing the table definition
- ‘.MYD’ (MYData) - data file
- ‘.MYI’ (MYIndex) – index file
Explain advantages of MyISAM over InnoDB?
- MyISAM follows a much more conservative approach to disk space management – storing each MyISAM table in a separate file, which can be further compresses, if required.
- InnoDB stores the tables in tablespace. Further optimization is difficult with them.
How would concatenate strings in MySQL?
With the use of - CONCAT (string1, string2, string3)
How would you get the current date in Mysql?
By using
How would you enter Characters as HEX Numbers?
- To enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X).
- Alternatively, just prefix HEX numbers with (Ox).
How are MySQL timestamps seen to a user?
- MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS.
How will you export tables as an XML file in MySQL?
MYSQL’s query browser has a provision called “Export Result Set” which allows the tables to be exported as XML.
What is the use of i-am-a-dummy flag in MySQL?
Using the i-am-dummy flag makes the SQL engine refuse any Updates or deletes to execute if the WHERE clause is not present. It is very useful when using delete statements. Using i-am-dummy flag will not allow the following statement to execute:
LIKE and REGEXP operators are used to express with ^ and %.
SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";
1
2
SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";
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 –
TINYBLOB
BLOB
MEDIUMBLOB and
LONGBLOB
They all differ only in the maximum length of the values they can hold.
A TEXT is a case-insensitive BLOB. The four TEXT types
TINYTEXT
TEXT
MEDIUMTEXT and
LONGTEXT
They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.
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.
 |
| AppDynamics Most Frequently Asked Latest MySQL Interview Questions Answers |
How MySQL Optimizes DISTINCT?
DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
1
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
How to enter Characters as HEX Numbers?
If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).
A HEX number string will be automatically converted into a character string, if the expression context is a string.
What does myisamchk do?
- It compresses the MyISAM tables, which reduces their disk or memory usage
How can we convert between Unix & MySQL timestamps?
- MySQL timestamp can be converted into Unix timestamp using the command UNIX_TIMESTAMP.
- Unix timestamp can be converted into MySQL timestamp using the command FROM_UNIXTIME.
- It compresses the MyISAM tables, which reduces their disk or memory usage
How can we convert between Unix & MySQL timestamps?
- MySQL timestamp can be converted into Unix timestamp using the command UNIX_TIMESTAMP.
- Unix timestamp can be converted into MySQL timestamp using the command FROM_UNIXTIME.
What is BLOB?
- BLOB stands for binary large object.
- It that can hold a variable amount of data.
There are four types of BLOB based on the maximum length of values they can hold:
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
What is TEXT?
TEXT is case-insensitive BLOB. The four types of TEXT are:
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
What is the difference between BLOB and TEXT?
- In BLOB sorting and comparison is performed in case-sensitive for BLOB values.
- In TEXT types sorting and comparison is performed case-insensitive.
How is MyISAM table stored?
MyISAM table is stored on disk in three formats.
- ‘.frm’ file – storing the table definition
- ‘.MYD’ (MYData) - data file
- ‘.MYI’ (MYIndex) – index file
Explain advantages of MyISAM over InnoDB?
- MyISAM follows a much more conservative approach to disk space management – storing each MyISAM table in a separate file, which can be further compresses, if required.
- InnoDB stores the tables in tablespace. Further optimization is difficult with them.
How would concatenate strings in MySQL?
With the use of - CONCAT (string1, string2, string3)
How would you get the current date in Mysql?
By using
SELECT CURRENT_DATE();
How would you enter Characters as HEX Numbers?
- To enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X).
- Alternatively, just prefix HEX numbers with (Ox).
How are MySQL timestamps seen to a user?
- MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS.
How will you export tables as an XML file in MySQL?
MYSQL’s query browser has a provision called “Export Result Set” which allows the tables to be exported as XML.
What is the use of i-am-a-dummy flag in MySQL?
Using the i-am-dummy flag makes the SQL engine refuse any Updates or deletes to execute if the WHERE clause is not present. It is very useful when using delete statements. Using i-am-dummy flag will not allow the following statement to execute:
Delete from employee;
https://mytecbooks.blogspot.com/2019/05/appdynamics-mysql-interview-questions.html