I am using MySQL 5.1. As a rookie, here are some basic command commands I feel useful to rememberknow.
Some basic mysql commands
Use mysql client to access the database.
| Panel |
|---|
% $ myslq -u database-user -p |
Show databases
...
| Panel |
|---|
mysql> select ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME='procedure-name'; |
...
Show errors or warnings of just executed command
...
| Panel |
|---|
mysql> SHOW WARNINGS; |
...
Kill a sql process
...
To kill a heavy sql process: find the id of that process and kill it
| Panel |
|---|
mysql> SHOW FULL PROCESSLIST; |
Some useful SQL commands
...
Find Duplicates of a field set
...
| Panel |
|---|
mysql> select *, count(*) as count from table-name group by field1, field2, ... having count(*) >1; |
...
Get median generally
...
There are some useful group by functions in MySQL, like avg(), std(), max() and so on, but there is not a median function. Following SQL statement is a general way to achieve this purpose
| No Format |
|---|
SELECT m.name, avg(m.medians) medians from (
SELECT x.name, x.val medians
FROM A x, A y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING SUM(y.val <= x.val) >= COUNT(*)/2
AND SUM(y.val >= x.val) >= COUNT(*)/2
)AS m group by m.name;
|
...
Save SQL results in a csv file,
...
| No Format |
|---|
SELECT ...
FROM ...
INTO OUTFILE '/tmp/sqlresults.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';
|