My SQL cheat sheet

       209 words, 1 minutes

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;

Related posts: