SQL commands are things I can’t remember since I don’t use them every day.
Here are (MySQL) commands that I keep looking for.
Connexion
Connect to MySQL as root:
# mysql -u root -p
Connect to MySQL as an applicative user:
# mysql -u admin -p myappdb
Database operations
List the available databases:
mysql> SHOW DATABASES;
Create a database:
mysql> CREATE DATABASE database;
Suppress a database:
mysql> DROP database;
Select a database:
mysql> USE database;
Authentication
Grant privileges:
mysql> GRANT ALL ON database.* TO ‘user‘@’client_hostname‘ IDENTIFIED BY ‘password‘;
mysql> FLUSH PRIVILEGES;
Revoke privileges:
mysql> REVOKE ALL PRIVILEGES ON database.* FROM ‘user‘@’client_hostname‘;
mysql> FLUSH PRIVILEGES;
Table operations
List the available tables of the selected database:
mysql> SHOW TABLES;
Have a look at a table:
mysql> DESCRIBE table;
Data operations
Finding something:
mysql> SELECT * FROM table;
mysql> SELECT * FROM table WHERE record = “value“;
mysql> SELECT field FROM table WHERE rec1 = “val1” AND rec2 = “val2“;
Updating something:
mysql> UPDATE table SET record = “new_value” WHERE record = “old_value“;
Deleting something:
mysql> DELETE FROM table WHERE record = “value“;
Information
Getting version details:
mysql> SHOW VARIABLES LIKE “%version%”;
General status of the DB:
mysql> STATUS;
Getting system parameters:
mysql> SHOW VARIABLES;
Getting system status:
mysql> SHOW GLOBAL STATUS;
Note: italic text should be adapted to your environment.
Source:
MySQL 5.1 Reference Manual :: 12 SQL Statement Syntax
Neal Parikh’s Cheat Sheet
Server System Variables
Server Status Variables