I am using MySQL 5.1. As a rookie, here are some basic commands I feel useful to know.
Some basic mysql commands
Use mysql client to access the database.
Panel |
---|
$ myslq -u database-user -p |
Show databases
No Format |
---|
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mytest | | mysql | | phpmyadmin | +--------------------+ 4 rows in set (0.00 sec) |
Change to a database
Panel |
---|
mysql> use mytest; |
Show tables
Panel |
---|
mysql> show tables; |
Show a table structure
Panel |
---|
mysql> describe table-name; |
List procedures
Panel |
---|
mysql> select ROUTINE_NAME from information_schema.ROUTINES; |
Show a view or procedure's definition
Panel |
---|
mysql> SHOW CREATE VIEW view-name; |
or
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'; |