Mysql Tips #3: Drop and CONCAT

Posted on by admin

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

Posted on by admin

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…………

Mysql Tips #1: Change field order and insert

Posted on by admin

Change the field order 

Sometimes we want to change the field order without deleting and re-inserting.
ALTER TABLE `table_name` MODIFY `column_you_want_to_move` DATATYPE AFTER `column`

ALTER TABLE `investment_security` MODIFY `location` text AFTER `serial_number`;

If you want to move it to the first place then:

ALTER TABLE `investment_security` MODIFY `location` text FIRST;

 

Insert from one table into another

INSERT INTO new_table SELECT columns-in-new-order FROM old_table;

Happy coding!!!!!