The purpose of this SQL cheat sheet is to help developpers and beginners become better at SQL. It will reference some basic concepts of the Structured Query Language. For example, it will explain the language elements, operators and other various topics. As a result, you can use this cheat sheet as a reference or as a learning material.
What is SQL
SQL stands for Structured Query Language. Relational database management system (RDBMS) use it in conjunction with programming functions. Because of this, It is particularly useful with structured data that is using relations between entities. In layman terms, SQL is a language to store and retrieve data in a relational database. Lets take, for example, someone buying a car from the dealership. Since this is a concrete example, we can envision the following minimal database. It will have 3 tables. 1 for the car listings, 1 for the buyers and 1 for the sales (orders). Since in each table you could have duplicate informations, mostly in the orders table, how do you represent this without duplicating the data ? The answer to this question is : relations. Now, lets look at the image below:
In this database minimal diagram, the table with the red header is the orders table. Everytime a sale occurs, the salesmen add a row in the database. In this example it is compose of an ORDER_ID as a PRIMARY KEY (PK). CUSTOMER_ID and the CAR_ID are also represented in the table. These fields represents the FOREIGN_KEY (FK). In addition, some unique information about the orders are also added to the database such as the PRICE and DATE. Relations are represented by not duplicating the data. By creating a relation between tables keys’s, the dba will optimise the database. Therefore, it will take less diskspace on the server for web hosting.
Many types of databases engines exists. And because of this, a reader should not confuse it with the SQL language itself. Databases engines, also known as DBMS, are implementing the langugae to deal with the data. MySQL is one of them, while PostgreSQL is another. They are known as relational database management system (RDBMS). MySQL is open source, therefore free to use and very popular. However, other popular SQL database systems are:
- Oracle Database
- Microsoft SQL Server
How to Use MySQL
With Mysql, a user will query the database by using the Command Line Interface of a *unix distribution. However, Graphical user interface exists and they are making the task easier for the user because they are verifying the syntax as your write your queries. The application will let you know if your query is syntaxically wrong before you execute it, therefore removing a lot of trial and error attempts for beginners. Some popular GUI for MySQL includes:
- MySQL Workbench
- LibreOffice Base
A popular web version is also known as PHPMyAdmin, and as you guest it, it runs on PHP to interface with mysql databases. Here is a screen of the main interface:
SQL Cheat Sheet
Now that we have touched a few topics related to SQL, lets acutally look at an SQL cheat sheet. For instance, what is actually composing this structure query language (SQL) syntax ?
SQL Language Elements
The sql syntax is actually very detailled. It is composed of many elements, which we will look into:
Keywords are words that have a special meaning in the language. In other words, these keywords have functions attached to them in the SQL language. Because of this, they are interpreted differently then non keywords words. The most populars one would be the “SELECT”, “FROM” and “WHERE”. As a result, we will demonstrate a very basic sql query:
SELECT * FROM dealer.cars WHERE car_color = "red";
After running this query, the code would return a table of results. Therefore, all the cars from the listing would be red cars. In the example diagram above, we would have the following data per red car: CAR_ID, CAR_MAKE, CAR_COLOR and SUGGESTED_PRICE.
This is because the SQL engine understands those keywords in the example above. However, there are multiple other keywords in the language. Because of this, a massive total of 914 reserved keywords currently exists in SQL. Some are also special to certain RDBMS.
SQL has also a defined sets of operators that will let a user compare values and insert logic in its sql statement.
|=||Used when comparing a column value with an exact match|
|<> or !=||To be Used when comparing a column value is NOT an exact match|
|>||Used to compared a Value is greater then another value|
|<||Used to compared a Value is smaller then another value|
|>=||To compare a Value is greater or equal to another value|
|<=||Used to compared a Value is smaller or equal to another value|
|BETWEEN||Used to verify if a value is between an interval of values|
|LIKE||Used when comparing a column value is similar to a value in conjucntion wiht wildcard % character|
|[NOT] IN||Used to verify if a value is in a List provided in query or not|
|IS [NOT] NULL||Used to verify a column is NULL or not|
|IS [NOT] TRUE or IS [NOT] FALSE||Boolean test on a value|
|IS NOT DISTINCT FROM||Used to check if it Is equal to value or both are nulls|
|AS||Used to rename a column in the results|
SQL syntax allows for two types of comments. A single line version and a Multine version of the comments are permitted. The single line comment should be in the form of 2 dashes and then the comment text. It is very easy to use, however it limits the number of text you can insert in the comment.
-- this is a comment
However, the multiline or block comment is done like this, which permits to add more text:
/* This is
line or block
So far, we have looked at queries that are used to search and find content. But the SQL syntax also allows for managing that data. Hence, We will look at the most common data manipulation keywords.
As you guest it, this statement is to INSERT data into a database. It would then add a row to a table in a sql database schema. To further illustrate, lets look at a simplistic INSERT for the “cars” table represented in the previous diagram.
INSERT INTO dealer.cars
(CAR_ID, CAR_MAKE, CAR_COLOR, SUGGESTED_PRICE)
(123, "FORD", "RED", 30000);
This query would add a row for a FORD red vehicule that as a suggested price of $30000 and has the car_id: 123.
Let say that the suggested price by the manufacturer of the above inserted RED Ford car as dropped. Because of this, we would need to adjust the database row to reflect the change. This is where we would use an UPDATE query to do so. For instance, we could use the following query:
SET SUGGESTED_PRICE = 25000
WHERE CAR_ID = 123;
This would change the value in the cells for that exact car with id 123 to $25000 instead of $30000.
Now, if you would want to remove this car from the inventory because it was wrongfully inserted, you would need to delete the row. You would do so with this SQL statement:
DELETE FROM dealer.cars WHERE CAR_ID = 123;
The previosuly data manipulation statement are useful to work with the data itself, but what about the container of the data. The database schema and tables used to contain data. This is why SQL syntax also has a portion of it to do Data definition.
The CREATE keyword in SQL is used to create an entity such as table and you have to specify certain minimal elements to be able to run a CREATE statement in SQL. Because of this we will look at the structure a minimal query:
CREATE TABLE cars(
PRIMARY KEY (CAR_ID)
Continuing with the cars dealership, here in the above CREATE example, we would create a table named “cars”. That way, we are specifying each of the 4 columns and their respective data type int he statement. INTEGER are numbers while VARCHAR(50) represents a variable string of characters of a maximum lenght of 50 chars. Lastly we specify that the primary key for this table will be the CAR_ID column.
The ALTER keyword is used in the SQL language to modify an existing table. For instance, if we wanted to ADD a column to our cars table to mention if it is sold or available, we could do the following SQL statement:
ALTER TABLE cars ADD SOLD VARCHAR(1);
With this query/statement, we would add a column name SOLD with a varchar(1) type. That way, when a car is sold, you could a “Y” as value for YES it sold.
RENAME as the name implies, is to change the name of an element such as an SQL table.
Truncate is equivalent to deleting the content but not the container. It will delete in a fast way all rows, but keep the table and data structure intact.
Drop will delete the content of the table and the table structure from the database schema. Because of that, you must be careful when using it. You should check your query twice in order to prevent the deletion of the wrong table by accident.
Other SQL Topics
Here, id like to cover a few random SQL topics, that are good to know.
It was all good in our car dealer diagram but what if I want to see all the data combined into a single table. This relational database thing is splitting data into multiple table, hence we cant see everything at once. Here comes the JOIN keyword. It is used in select queries to join data on keys that match. For this reason, 4 types of JOIN statement exists in the SQL language. These are:
- INNER JOIN (Default join statement) (Will return any rows that have a matching value in both tables)
- LEFT JOIN (Will Return all results from first table with only the matching results from the second table)
- RIGHT JOIN (Will Return all results from second table with only the matching results from the first table)
- FULL JOIN (Returns all records from both table wich match the condition)
A better representation in this graphic can be perceived, where blue represent the data that is returned:
An example JOIN statement for our cars dealer could look like this:
Select customer.CUSTOMER_NAMES as "Customer Name", customer.EMAIL as "Email", cars.CAR_MAKE as "Car Make", cars.CAR_COLOR as "Color", orders.ORDER_PRICE as "PRICE", orders.DATE as "DATE"
INNER JOIN customer ON order.CUSTOMER_ID = customer.CUSTOMER_ID
INNER JOIN cars ON order.CAR_ID = cars.CAR_ID;
As a result of this query, the table columns would look something like this:
|Customer Name||Car make||Color||Price||Date|
In conclusion, I Hope this was a good opportunity to learn SQL thru this cheat sheet. If you are an advanced user, hope you at least learn 1 thing in the sql cheat sheet. Do bookmark the page and most importantly, check it if you need it, because learning SQL is a valuable skills nowadays. Almost every business will use a database of some sort to run their activities. Therefore, by knowing SQL you will become a valuable asset.