I am using MySQL 5.1. As a rookie, here are some basic command I feel useful to remember.
Some basic mysql commands
Use mysql client to access the database.
% myslq -u database-user -p
Show databases
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mytest | | mysql | | phpmyadmin | +--------------------+ 4 rows in set (0.00 sec)
Change to a database
mysql> use mytest;
Show tables
mysql> show tables;
Show a table structure
mysql> describe table-name;
List procedures
mysql> select ROUTINE_NAME from information_schema.ROUTINES;
Show a view or procedure's definition
mysql> SHOW CREATE VIEW view-name;
mysql> SHOW CREATE PROCEDURE procedure-name;
or
mysql> select ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME='procedure-name';
Show errors or warnings of just executed command
mysql> SHOW WARNINGS;
mysql> SHOW ERRORS;
Kill a sql process
To kill a heavy sql process: find the id of that process and kill it
mysql> SHOW FULL PROCESSLIST;
mysql> KILL theprocessid;
Some useful SQL commands
Find Duplicates of a field set
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
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,
SELECT ... FROM ... INTO OUTFILE '/tmp/sqlresult.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';