Common MySQL Interview Questions


Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

-----------add comments to a column ---------

ALTER TABLE tbl_commety_members modify `member_type` TINYINT( 1 ) COMMENT 'id of user'


// to select a column with serial no which doesn't exist in mysql table
SET @rownum:=0; SELECT @rownum:=@rownum+1 AS id, names FROM table_name;


-----------to fromat date in dd/mm/yyyy format--------------------


SELECT remarks, date_FORMAT(payment_date,'%d/%m/%Y') FROM tbl_payment


--------------------------group by----------------------------


select count(tbl_property.city) as ctr,tbl_city.city from tbl_property left join tbl_city on (tbl_property.city=tbl_city.id) where tbl_property.status=1 group by tbl_property.city order by ctr desc limit 0,8


-------------------------count max id from union of two table---------------


select certificate_no from (select certificate_no from tbl_austfumigation order by certificate_no desc limit 0,1) as a
union
select certificate_no from (select certificate_no from tbl_fumigation order by certificate_no desc limit 0,1) as b
order by certificate_no desc limit 0,1



-----------------------Count co of columns in a table------------
SELECT count(*) FROM information_schema.`COLUMNS` C
WHERE table_name = 'tablename'
AND TABLE_SCHEMA = "databasename"

--------------------rearrange columns ---------------
ALTER table tbl_tour MODIFY COLUMN days tinyint(4) AFTER package;


-----------------replace a part of a str ------------


http://www.demourl.com/RTL.asp?Name=StringReplace


REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE()performs a case-sensitive match when searching for from_str.

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
    -> 'WwWwWw.mysql.com'
This function is multi-byte safe.

UPDATE MyDB.MyTable
SET MyDB.MyTable
= REPLACE(MyDB.MyTable.MyField,'OldString','NewString')
WHERE MyDB.MyTable.MyField like '%OldString%';

--------------------load data from csv-------------------------

load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)

----------- format date time datetime is the name of the field------------------

select tbl_order.*, DATE_FORMAT(datetime,'%d/%m/%Y %h:%m:%s %p') as tt from tbl_order

-------------rearrange column after cretaing table--------------------------

alter table `tbl_maintenance` modify column vehicle_id bigint(20) after id



No comments: