MySQL Query for a Programmer

I am writing some useful queries collected in past specially when I was working with Ruby & Java projects.
They are more than SIMPLE and lesser than COMPLEX and often used by a programmer. This post is more for my self reference. Good if somebody else is benefited…
To make it simple, I have wrote them with examples only rather than having a complete syntax.
You can do a browser search(Ctrl+F) within this post based on few keywords:
mysqldump, alter, add index, add column, csv to database, group concat, rename etc.

1. Copy a table : Copies a table along with data structure and data into another table:
create table trackedi_duplicate as select * from trackedi

2. Insert multiple rows using single query
INSERT INTO “table1” (“column1”, “column2”)
SELECT “column1”, “column2” FROM table2

3. Repeating the same character:
SELECT REPEAT(‘a’,5)
Outputs aaaaa

4. Trimming the \n from a long description like column
select trim(both ‘\n’ from description) from base;

5. Position of a char or string in a column :  Give me all the rows containing the word “pro” in status column
select * from trackedi where LOCATE(‘pro’,status) > 0

6. Size of each table
SELECT TABLE_SCHEMA AS ‘Database’, TABLE_NAME AS ‘Table’,
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH – DATA_FREE) / 1024 / 1024),15),” Mb”) AS Size FROM INFORMATION_SCHEMA.TABLES;

7. Loading data from CSV to a table
LOAD DATA LOCAL INFILE ‘C:\\dbs_dump\\mytable1.csv’ INTO TABLE myTable1 FIELDS  TERMINATED BY ‘,’ LINES TERMINATED BY ‘\r\n’;

8. Dump a database
mysqldump -u root -p testDb  > c:\dbs_dump\testDb.sql

9. Dump few tables only
mysqldump -u root -p testDb table1 table2 > c:\dbs_dump\fewTables.sql // table structure along with data
mysqldump -u root -p -d testDb table1  table2 > c:\fewTablesStructure.sql // table structure without data

 10. Storing/Running a sql scriptMake sure that you are at the prompt of mysql
mysql> source fewTables.sql

11. Renaming a table
rename table analytical_charts to data_charts;

 12. Dropping/Adding a foreign key
alter table data_analysis drop foreign key chart_id;
alter table data_analysis add foreign key(`chart_id`) REFERENCES `data_charts` (`id`);

13. Adding a new column
alter table data_charts add column `chart_type` varchar(80);

 14. Adding a new index
alter table data_charts add index `chart_type` (`chart_type`);

15. Updating a column by string replacement
update [table_name] set [field_name] = replace([field_name],'[string_to_find]’,'[string_to_replace]’);
update myTable set myColm1 = replace(address,’\”,”);

16. Group Concat – Give me the list of all names grouped by status – MySQL specific, you don’t have in SQLServer
SELECT status, GROUP_CONCAT(name) FROM trackedi group by status

Advertisements