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

Best Related Posts are Following:

1. MySQL Interview questions and answers for 2 year experience.
2. Advanced MySQL Interview Questions and Answers for Experienced.
3. How can I make SQL Query case sensitive string comparison on MySQL?.
4. PHP Mysql Interview Questions and Answers for fresher and experienced.
5. Differences between Stored Procedures and Functions in MYSQL.
6. PHP MySQL interview questions and answers for experienced.
7. MySQL Interview Questions and Answers for Experienced.
8. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY.
9. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL.
10. Calculate a Sum of Time using MySQL.
11. Mysql Interview Questions And Answers - Atomicity, Dedlock, Client Program, Mysql Mode, Mysql Sensitivity Of Identifiers And Mysql Errors.
12. Difference Between Primary Key And Unique Key And Foreign Key And Composite Key.
13. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation - Multilingual Website.
14. MySQL Interview Questions and Answers.
15. MySQL Functions - MySQL tutorial for beginners.
16. mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries.
17. Stored Procedure - Advantage of Stored Procedure - Disadvantage of Stored Procedure.
18. Pending Interview Questions and Answers.
19. MySQL Indexing.
20. Mysql Update Statement with Join.
21. MySQL Constraint Example.
22. MySQL Cascade Example.
23. MySQL Expressions.
24. MySQL Delete Table Example.
25. Replace Table - Update Field, Move Records to another table MySQL.
26. Mysql Update Syntax - Update Multiple Records.
27. What is Mysql.
28. MySQL interview Questions and Answers For Experienced.