Mysql Tips #3: Drop and CONCAT

Drop Index

One common mistake which is being made on dropping index is that we forget the index name and try to drop the column name.

drop index departments_name_unique on departments;

CONCAT()

Incase if you want to join the fields

SELECT  DATE, expiry, CONCAT( user.b_fname, " ", user.b_lname ) 
FROM user

Joins

Different types of joins: inner joins, left joins, right joins union/outer joins. This link explains in great detail.

 

Happy coding!!

….Sometime simple tip makes someone’s coding life easier…………

Mysql Tips #2 : Using Group

Using group in MySQL:

Group by is being used to group field into a single row to perform count, sum avg etc.

SELECT *
FROM `test_results` 
WHERE parent_code =11
GROUP BY username

Using Group by with MAX

SELECT MAX( points ) , username
FROM `test_results` 
WHERE parent_code =11
GROUP BY username

Similar can be used for min, avg, count…etc

Using Group by with Having

select gid
 from `gd`
 group by gid
 having count(*) > 10
order by lastupdated desc

Happy coding!!

….Sometime simple tip makes someone’s coding life easier…………