Mysql useful queries

On July 11, 2009, in Mysql, Tips, Tricks, by admin

Age:
Select DATE_FORMAT (NOW(), ‘%Y’) – DATE_FORMAT(BirthDate, ‘%Y’) – (DATE_FORMAT(NOW(), ’00-%m-%d’) < DATE_FORMAT(BirthDate, ’00-%m-%d’)) AS Age From tbl_member where 1;

Change collation:

Alter table `tbl_student` convert to character set utf8 collate utf8_swedish_ci;

Shift mysql column:

ALTER TABLE tableName MODIFY column column_2 int AFTER column_1;

Get video info with user info:
SELECT `video_id` , `video_title` ,
( SELECT userid FROM tbl_members
WHERE `member_id` = v.member_id
) AS owner
FROM `tbl_video` AS v
WHERE 1;

Concat:
Select concat(a.Day,’_’,a.Hour) as IDX From tbl_time;

Switch Case:
SELECT a.id, a.group_name ,
CASE
WHEN a.is_feature= ‘t’ THEN ‘Featured’
ELSE ‘Unfeatured’
END as Featured
FROM `tbl_member_groups` a;

2 Way Friendship:
a)
SELECT CASE WHEN mf.member_id = ‘”78″
THEN mf.friend_id
ELSE mf.member_id
END AS f_id,
(
SELECT name
FROM tbl_member
WHERE mid = f_id
) AS uname
FROM tbl_member_friend AS mfWHERE ( member_id = ’78’ OR friend_id = ’78’ ) AND is_accepted = ‘t';

b)
SELECT b.ID as us_id,c.ID as ctu,c.Nationality,e.Name as cname, b.Username, d.Name FROM user b
LEFT JOIN teacher c ON b.ID = c.UserID
LEFT JOIN teacher_photo d ON c.ID = d.TeacherID
LEFT JOIN country e on e.ID=c.Nationality
WHERE b.ID
IN ( SELECT CASE WHEN tf.user_id = ‘$ID’
THEN tf.friend_id
ELSE tf.user_id
END AS f_id
FROM friend tf
WHERE (
user_id = ‘$ID’
OR friend_id = ‘$ID’
)
AND tf.`is_accept` = ‘t’ order by tf.date_added desc
);

Join:
SELECT a. * , c.Nationality, e.Name AS cname, b.Username, d.Name
FROM forum_reply a
LEFT JOIN user b ON a.poster_thread_id = b.ID
LEFT JOIN teacher c ON a.poster_thread_id = c.UserID
AND b.ID = c.UserID
LEFT JOIN teacher_photo d ON c.ID = d.TeacherID
LEFT JOIN country e ON e.ID = c.Nationality
WHERE a.thread_id = ‘1’;

Remove duplicate records:
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

Stored Porcedures:
CREATE PROCEDURE simpleproc (OUT param1 INT)
SELECT * FROM testimonial;
CALL simpleproc(@a);

SQL Injection:
Select * from tbl_user where name = ‘pavan’ OR ‘1’ = ‘1’;

Insert Duplicate:
INSERT INTO `teacher_photo` SET TeacherID =7, Name = ‘7-teacher.jpeg’,
FileType = ‘image/jpeg’ ON DUPLICATE KEY UPDATE Name = ‘7-teacher.jpeg’, FileType = ‘image/jpeg';

Reporting month year wise:
Select count(hits), month(add_date) m, year(add_date) y From ‘sales’ where 1 group by m,y;

Interchange columns values:
Update userinfo set sex = case
when sex = ‘f’ then m
when sex = ‘m’ then f
end;

Case sensitive data:
Select * From member where BINARY uname = “pavan”;

Get third max mark holder:
Select * From ‘marks’ where 1 order by mark desc limit 2,1;

Get duplicate in table:
Select title From ‘books’ group by title having count(*) > 1;

Set Character set to UTF-8:
SET CHARACTER SET utf8;
SET NAMES ‘utf8′;

Check for Online:
SELECT *
FROM tbl_member_groups
WHERE DATE_SUB( CURDATE( ) , INTERVAL 300 MINUTE ) <= date_add;

2 Responses to Mysql useful queries

  1. Anonymous says:

    I will be really thankful for the author on this post to make this lovely and informative article live to put us. We actually appreciate ur effort. Carry on the great work. . . .

  2. Anonymous says:

    Wow, that’s an incredibly nice read!

Leave a Reply