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 = ‘sandeep’ 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 = “sandeep”;
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;
