Skip to end of metadata
Go to start of metadata

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.

$ 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/sqlresults.csv'
    FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        ESCAPED BY '\\'
    LINES TERMINATED BY '\n';
  • No labels