SQL Cheat Sheet
SQL databases come installed with all the commands you need to add, modify, delete, and query your data. This cheat sheet-style guide provides a quick reference to some of the most commonly-used SQL commands.
('). In SQL, it is necessary to wrap any data values that consist of strings in apostrophes. This isn't required for numeric data, but it also won't cause any issues if you do include apostrophes.
Please note that, while SQL is recognized as a standard, most SQL database programs have their own proprietary extensions. This guide uses MySQL as the example relational database management system (RDBMS), but the commands given will work with other relational database programs, including PostgreSQL, MariaDB, and SQLite. Where there are significant differences between RDBMSs, we have included the alternative commands.
Opening up the Database Prompt (using Socket/Trust Authentication)
By default on Ubuntu 18.04, the root MySQL user can authenticate without a password using the following command:
sudo mysql
To open up a PostgreSQL prompt, use the following command. This example will log you in as the postgres user, which is the included superuser role, but you can replace that with any already-created role:
sudo -u postgres psql
Opening up the Database Prompt (using Password Authentication)
If your root MySQL user is set to authenticate with a password, you can do so with the following command:
mysql -u root -p
If you've already set up a non-root user account for your database, you can also use this method to log in as that user:
mysql -u user -p
The above command will prompt you for your password after you run it. If you'd like to supply your password as part of the command, immediately follow the -p option with your password, with no space between them:
mysql -u root -ppassword
Creating a Database
The following command creates a database with default settings.
CREATE DATABASE database_name;
If you want your database to use a character set and collation different than the defaults, you can specify those using this syntax:
CREATE DATABASE database_name CHARACTER SET character_set COLLATE collation;
Listing Databases
To see what databases exist in your MySQL or MariaDB installation, run the following command:
SHOW DATABASES;
In PostgreSQL, you can see what databases have been created with the following command:
\list
Deleting a Database
To delete a database, including any tables and data held within it, run a command that follows this structure:
DROP DATABASE IF EXISTS database;
Creating a User
To create a user profile for your database without specifying any privileges for it, run the following command:
CREATE USER username IDENTIFIED BY 'password';
PostgreSQL uses a similar, but slightly different, syntax:
CREATE USER user WITH PASSWORD 'password';
If you want to create a new user and grant them privileges in one command, you can do so by issuing a GRANT statement. The following command creates a new user and grants them full privileges to every database and table in the RDBMS:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
Deleting a User
Use the following syntax to delete a database user profile:
DROP USER IF EXISTS username;
Note that this command will not by default delete any tables created by the deleted user, and attempts to access such tables may result in errors.
Selecting a Database
Before you can create a table, you first have to tell the RDBMS the database in which you'd like to create it. In MySQL and MariaDB, do so with the following syntax:
USE database;
In PostgreSQL, you must use the following command to select your desired database:
\connect database
Creating a Table
The following command structure creates a new table with the name table, and includes two columns, each with their own specific data type:
CREATE TABLE table ( column_1 column_1_data_type, column_2 column_2_data_taype );
Deleting a Table
To delete a table entirely, including all its data, run the following:
DROP TABLE IF EXISTS table
Inserting Data into a Table
Use the following syntax to populate a table with one row of data:
INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_A', 'data_B', 'data_C' );
You can also populate a table with multiple rows of data using a single command, like this:
INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_1A', 'data_1B', 'data_1C' ), ( 'data_2A', 'data_2B', 'data_2C' ), ( 'data_3A', 'data_3B', 'data_3C' );
Deleting Data from a Table
To delete a row of data from a table, use the following command structure. Note that value should be the value held in the specified column in the row that you want to delete:
DELETE FROM table WHERE column='value';
Note: If you do not include a WHERE clause in a DELETE statement, as in the following example, it will delete all the data held in a table, but not the columns or the table itself:
DELETE FROM table;
Changing Data in a Table
Use the following syntax to update the data held in a given row. Note that the WHERE clause at the end of the command tells SQL which row to update. value is the value held in column_A that aligns with the row you want to change.
Note: If you fail to include a WHERE clause in an UPDATE statement, the command will replace the data held in every row of the table.
UPDATE table SET column_1 = value_1, column_2 = value_2 WHERE column_A=value;
Inserting a Column
The following command syntax will add a new column to a table:
ALTER TABLE table ADD COLUMN column data_type;
Deleting a Column
A command following this structure will delete a column from a table:
ALTER TABLE table DROP COLUMN column;
Performing Basic Queries
To view all the data from a single column in a table, use the following syntax:
SELECT column FROM table;
To query multiple columns from the same table, separate the column names with a comma:
SELECT column_1, column_2 FROM table;
You can also query every column in a table by replacing the names of the columns with an asterisk (*). In SQL, asterisks act as placeholders to represent “all”:
SELECT * FROM table;
Using WHERE Clauses
You can narrow down the results of a query by appending the SELECT statement with a WHERE clause, like this:
SELECT column FROM table WHERE conditions_that_apply;
For example, you can query all the data from a single row with a syntax like the following. Note that value should be a value held in both the specified column and the row you want to query: