I've been learning SQL this week. Here's a cheat sheet of basic SQL commands.
CRUD
CRUD stands for CREATE, READ, UPDATE, DELETE - the basic SQL commands for working with relational databases. Here's the syntax for the most common commands:
CREATE
This is the command to create a new database from scratch:
CREATE DATABASE database_name;
--An example:
CREATE DATABASE hogwarts_library;
Here's the basic syntax to create a new table in a database:
CREATE TABLE table_name (
column1_name data_type,
column2_name data_type,
column3_name data_type
);
--An example:
CREATE TABLE books (
id INT,
title VARCHAR(100),
author VARCHAR(100),
genre VARCHAR(50)
);
The above example will give us a table with the following headings (we haven't put any data in it yet so the rows are empty):
| id | title | author | genre |
|----------|----------|---------|---------|
| | | | |
| | | | |
DATA TYPES
Here's a list of a few common data types:
| Data type | Meaning |
| INT | Integer. A number |
| VARCHAR(size) | A string of variable length. Can contain letters, numbers, and special characters. Enter a number between 0 and 65535 in place of (size) |
| BOOLEAN | A zero in this column will be considered false, any non-zero value will be true |
| DATE | A date, in YYYY-MM-DD format |
n.b these are MySQL data types, they may be slightly different for other database software such as PostgreSQL
Click here for a full list of data types from W3Schools.
READ
To read data from a database, we use the SELECT command:
SELECT column_name
FROM table_name;
--An example:
--This query will return all the values in the title column:
SELECT title
FROM books;
--Another example:
--This will return all the values in the books table:
SELECT *
FROM books;
UPDATE
To update data in a database we can use the INSERT, UPDATE and ALTER TABLE commands:
INSERT
This command inserts a new row into a table:
INSERT INTO table_name (column1_name, column2_name, column3_name)
VALUES (value1, value2, value3);
--An example:
INSERT INTO books (id, title, author, genre)
VALUES (1, 'The Hobbit', 'J.R.R. Tolkein', 'Fantasy');
--Will result in:
| id | title | author | genre |
|-----|------------|----------------|----------|
| 1 | The Hobbit | J.R.R. Tolkein | Fantasy |
| | | | |
n.b. if you are entering values for all the columns, you can omit the first set of brackets after table_name.
UPDATE
This command edits a row in a table:
UPDATE table_name
SET column_name = value
WHERE condition;
--An example:
--This will change the title in our example table above from 'The Hobbit' to 'The Fellowship of the Ring'
UPDATE books
SET title = 'The Fellowship of the Ring'
WHERE id = 1;
ALTER TABLE
This command adds a new column to a table:
ALTER TABLE books
ADD COLUMN year INT;
--Will give us:
| id | title | author | genre | year |
|-----|----------|---------|---------|--------|
| | | | | |
DELETE
DELETE FROM
This is the command to delete one or more rows from a table:
DELETE FROM books
WHERE author = 'J.R.R. Tolkein';
DROP TABLE
This command deletes a table from a database:
DROP TABLE books;
WHERE
The WHERE command is used to filter records. Here is an example using SELECT:
SELECT * FROM books
WHERE author = 'J.R.R. Tolkein';
You can use operators with WHERE. Here's a list of a few operators:
| Operator | Meaning |
| = | Equal |
| <> | Not equal (sometimes written as !=) |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| BETWEEN | Between a certain range. Use with AND |
| LIKE | Search for a pattern e.g LIKE '%man% will find any string with man in it |
| OR | Use to check for one of two/multiple conditions |
Click here for a full list of operators from W3Schools.
IS NULL and IS NOT NULL are also useful:
SELECT *
FROM books
WHERE genre IS NULL;
So there you have it, the basic SQL commands in a handy cheatsheet. Some other helpful commands to look up next include:
- Constraints e.g
PRIMARY KEY,UNIQUE,DEFAULT,NOT NULL MAX()&MIN()ORDER BYLIMITCOUNT()DISTINCTCASESUM()AVG()ROUND()JOIN