Tuesday 24 May 2016

MySQL Interview Questions and Answers for 3 year experience

MySQL Interview Questions and Answers for 3 year experience

Question: How can I enable Slow MySQL's query log?
Just set the global variable as following:
SET GLOBAL slow_query_log = 'ON';



Question: How to trim the white space from values?
 UPDATE tablename set name = TRIM(name);



Question: How to add more values in enum field?
ALTER TABLE tablename MODIFY COLUMN country ENUM('india','usa','uk','china');



Question: How do I check if an index exists on a table field in MySQL?
SHOW INDEX FROM tablename



Question: What is the maximum length of data I can put in a BLOB column in MySQL?
A BLOB - 65535 bytes maximum
A MEDIUMBLOB - 16777215 bytes maximum
A LONGBLOB - 4294967295 bytes maximum


Question: How to check empty field in Mysql
select * from where 1 AND (email != "" AND email IS NOT NULL);



Question: How to display current connection info?
SELECT USER();



Question: How to display List of database with current connection?
SELECT DATABASE();



Question: How to get count of distinct record?
 select count(distinct email) from users



Question: How to get count of distinct record?
 UPDATE users SET   email = NULL WHERE  id = 1000;



Question: How to copy data from one table to another table?
INSERT INTO table2 (id,uid,changed,status,assign_status) SELECT id,uid,now(),'Pending','Assigned' FROM table1



Question: How to get a list of MySQL views?
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';



Question: How to Copy table without copying data?
CREATE TABLE users_bck SELECT * FROM users WHERE 1=0;



Question: How to Repair all tables?
mysqlcheck -A --auto-repair;



Question: How to increase MySQL connection timeout?
Open MySQL file and increase the following
ini_set('mysql.connect_timeout', 600);//20 min
ini_set('default_socket_timeout', 600); //20 MIn



Question: Which MySQL datatype for an IP address?
As per me, It should be Int(11) unsigned.


Question: How to get last 30 days record?
SELECT  * FROM    users WHERE   created_date < DATE_ADD(NOW(), INTERVAL +1 MONTH);

Here created_date id datetime field in which we store the insertion date.


Question: Differences between utf8 and latin1?
In latin1 each character is exactly one byte long.
In utf8 a character can consist of more than one byte.



Question: How to convert the data type from one to another?
Convert String to DateTime
SELECT CONVERT('2017-04-06 08:25:57', DATETIME); //'2017-04-06 08:25:57'

Convert String to Time
SELECT CONVERT('06:07:58', TIME); //06:07:58

Convert Number to String
SELECT CONVERT(125, CHAR);  //125




Question: How to convert the Convert Character Sets from one to another?
SELECT CONVERT('hello user, how are you?' USING utf8); //



Question: How to get datetime when it was in another format?
SELECT STR_TO_DATE('18,05,2017','%d,%m,%Y'); //2017-05-18