Web developers notes,programming tutorials.

GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.

Syntax:

GROUP_CONCAT([DISTINCT] expr [,expr …]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name …]]
[SEPARATOR str_val])

Example:

SELECT student_name,GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;

SELECT student_name,
GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ‘ ‘)
FROM student
GROUP BY student_name;

In MySQL, you can get the concatenated values of expression combinations. You can eliminate duplicate values by using DISTINCT. If you want to sort values in the result, you should use ORDER BY clause.

To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword.

SEPARATOR is followed by the string value that should be inserted between values of result. The default is a comma (’,'). You can remove the separator altogether by specifying SEPARATOR ‘’.

You can set a maximum allowed length with the group_concat_max_len system variable.

If a maximum length has been set, the result is truncated to this maximum length.

Usage of DEFAULT Keyword in MySQL

Defalut keyword is used to update or set the columns default value.

Syntax:
DEFAULT(col_name)

It returns the default value for a table column.

It shows error when a given column doesn’t have a default value.

Example:

UPDATE tablename SET val = DEFAULT(val) WHERE id

Rename Database :
We can rename databases by using rename database query. For do this, we must have ALTER and DROP privileges for that database, and the CREATE privilege for the new database. This query create new database first and move tables and triggers from old database to new one.It does not change any account privileges listed in the system tables. It updates only Db column for objects such as stored routines and events.

Syntax :
RENAME DATABASE [db_name] TO [new_db_name];

Example :
RENAME DATABASE wordlisttemp TO wordlist;