I am using MySQL 5.1. As a rookie, here are some basic commands I feel useful to know.
$ myslq -u database-user -p |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mytest | | mysql | | phpmyadmin | +--------------------+ 4 rows in set (0.00 sec) |
mysql> use mytest; |
mysql> show tables; |
mysql> describe table-name; |
mysql> select ROUTINE_NAME from information_schema.ROUTINES; |
mysql> SHOW CREATE VIEW view-name; |
or
mysql> select ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME='procedure-name'; |
mysql> SHOW WARNINGS; |
To kill a heavy sql process: find the id of that process and kill it
mysql> SHOW FULL PROCESSLIST; |
mysql> select *, count(*) as count from table-name group by field1, field2, ... having count(*) >1; |
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
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; |
SELECT ... FROM ... INTO OUTFILE '/tmp/sqlresults.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'; |