Row to Column

GROUP_CONCAT() function

MySQL GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group.

Returns NULL when there are no non-NULL values.

Syntax:

GROUP_CONCAT(expr);

Where expr is an expression.

MySQL Version: 5.6

Contents:

• Example : MySQL GROUP_CONCAT() function
• MySQL GROUP_CONCAT() with order by and example
• MySQL GROUP_CONCAT() with distinct example
• MySQL GROUP_CONCAT() with separator and example

Example : MySQL GROUP_CONCAT() function

The following MySQL statement will return a list of comma(,) separated 'cate_id's for each group of 'pub_id' from the book_mast table.

Sample table: book_mast

Code:

SELECT pub_id,GROUP_CONCAT(cate_id)
FROM book_mast
GROUP BY pub_id;

Copy

Sample Output:

mysql> SELECT pub_id,GROUP_CONCAT(CATE_ID)
    -> FROM book_mast
    -> GROUP BY pub_id;
+--------+-----------------------+
| pub_id | GROUP_CONCAT(CATE_ID) |
+--------+-----------------------+
| P001   | CA002,CA004           | 
| P002   | CA003,CA003           | 
| P003   | CA001,CA003           | 
| P004   | CA005,CA002           | 
| P005   | CA001,CA004           | 
| P006   | CA005,CA001           | 
| P007   | CA005,CA002           | 
| P008   | CA005,CA004           | 
+--------+-----------------------+
8 rows in set (0.02 sec)

Credit to https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

Exporting and Importing An Individual MySQL Table

Exporting the Table
To export the table run the following command from the command line:
“mysqldump -p – –user=username dbname tableName > tableName.sql”

This will export the tableName to the file tableName.sql.
[NOTE: there should be no space between the two dashes, but I have to write it that way so that it display properly].

Importing the TableTo import the table run the following command from the command line:
mysql -u username -p -D dbname < tableName.sql

The path to the tableName.sql needs to be prepended with the absolute path to that file. At this point the table will be imported into the DB and you are ready to go!

MySQL Database

1. Backup Database using mysqldump
2. Create Stored Procedure From Script (command prompt)
3. Dumping MySQL Stored Procedures, Functions and Triggers
4. Exporting and Importing An Individual MySQL Table
5. Restore MySQL Data using command line in Windows
6. Row to Column

Dumping MySQL Stored Procedures, Functions and Triggers

MySQL 5 has introduced some new interesting features, like stored procedures and triggers. I will show in this small post how we can backup and restore these components using mysqldump.

mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

• —routines – FALSE by default
• —triggers – TRUE by default

This means that if you want to include in an existing backup script also the triggers and stored procedures you only need to add the —routines command line parameter:

1	mysqldump <other mysqldump options> --routines outputfile.sql

Let’s assume we want to backup ONLY the stored procedures and triggers and not the mysql tables and data (this can be useful to import these in another db/server that has already the data but not the stored procedures and/or triggers), then we should run something like:

1	mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql

and this will save only the procedures/functions/triggers of the . If you need to import them to another db/server you will have to run something like:

1	mysql <database> < outputfile.sql

Credit to Marius Ducea Jul 25th, 2007  posted in: tips & tricks 

Create Stored Procedure from script (command prompt)

[root@db]# mysql -u dbuser -pdbpassword--delimiter="//" database_name < /home/admin/stored_procedure_script.sql

Backup Database

$ mysqldump -u root -p database_name > /home/admin/nasa-temp/db_backup.sql

Then, you need to enter db root (or db user) password

Use this for export:

mysqldump -u username -p databasename > filename.sql

Use this for import:

mysql -u username -p databasename < filename.sql

Restore MySQL Data using command line in Windows

The is the example:

E:\MySQL\bin>mysql -u [username] -p
Then, enter [username] password.

mysql> use [schema]
mysql> source [db_file]

*db_file = .dump / .sql file