My SQL cheat sheet

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

No Comments

Leave a Reply

%d bloggers like this: