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?

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?

Question: How to display List of database with current connection?

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?

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

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