MySQL Interview Questions and Answers for Experienced

Question: Can I concatenate multiple MySQL rows into one field?
use GROUP_CONCAT function
For Example

SELECT group_concat( city, '--' ) AS all_city FROM `cities`WHERE 1 LIMIT 0 , 30

Output:
New York--,London--,Chicago--,Frankfurt--,Paris--

Question: How to output MySQL query results in csv format?
Its quite simple.

First get the queries with column which you want to write.
Seconds Use OUTFILE (filename with location), FIELDS TERMINATED BY (field separator), ENCLOSED BY and LINES TERMINATED (Line Separator) BY for format.

SELECT city from cities INTO OUTFILE 'city.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

Question: How do you set a default value for a MySQL Datetime column?
You can add default time, when data type is timestamp.
See Example:

CREATE TABLE `mytable` (`id` INT NULL ,`created_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ,PRIMARY KEY ( `id` ) ) ENGINE = InnoDB;

Question: Difference between utf8_general_ci and utf8_unicode_ci?
utf8_unicode_ci is more accurately as compare to utf8_general_ci.
utf8_unicode_ci is support more lanaguages as compare to utf8_general_ci.
utf8_general_ci is faster at comparisons and sorting as compare to utf8_general_ci.

Question: How to make multiple column as unique?
use UNIQUE Constraint for creating multiple column as unique.
For Example

ALTER TABLE `mytable` ADD UNIQUE `unique_index`(`user`, `email`, `address`);

Question: How to find duplicate email records in MySQL?

SELECT u1.first_name, u1.last_name, u1.email FROM users as u1
INNER JOIN (
    SELECT email FROM users GROUP BY email HAVING count(id) > 1
    ) u2 ON u1.email = u2.email;

Question: How can you reset AUTO_INCREMENT for user table?

ALTER TABLE users AUTO_INCREMENT = 1

Question: How to make duplicate table in MySQL?

CREATE TABLE users_new LIKE users; //Copy the structure
INSERT users_new SELECT * FROM users; //copy the table rows

Question: How to Group by multiple column(i.e email and ssn)?

select * from users group by concat(email,"-",ssn);

Question: MySQL: @variable vs. variable. Whats the difference?
@variable are user-defined variable used in statements which retain its values during the same session is active.
variable are procedure variable used in store procedure only. It never retain its value. Each time store procedure called its value set to null automatic.

Question: What is query to show list of Stored Procedures?

SHOW PROCEDURE STATUS;

Question: What is query to show list of functions?

SHOW FUNCTION STATUS;

Question: How to check last queries executed on MySQL?
check mysql.log

Question: How to get the sizes of the tables of a mysql database?

SELECT table_name AS "Table", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "geob2cdev" ORDER BY (data_length + index_length) DESC

Question: How to check specific table details?

SHOW TABLE STATUS WHERE Name = 'users';

Question: How to rename a table?

RENAME TABLE `mytable` TO `geob2cdev`.`mytable1` ;

Question: How to rename a column?

ALTER TABLE `mytable1` CHANGE `created_at` `created_at1` TIMESTAMP;

Question: How to insert current datetime in column?
Use Now() function

INSERT INTO `users` (`id`,`name`,create_time) VALUES (NULL,  'this is test\'s message',now());

Question: Can we have multiple order by in single Query?
Yes, We can have

select * from users where upper(name) = upper('Hello') order by name asc, id desc

Question: How to remove stored PROCEDURE?

DROP PROCEDURE storedProcedureName

Question: How to remove stored PROCEDURE if exist?

DROP PROCEDURE IF EXISTS storedProcedureName

Web Technology Experts Notes

Monday, 6 April 2015