970_90

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.


No comments:

Post a Comment