Original article was published by Jason Dsouza on Artificial Intelligence on Medium
The Icing on the Cake — the Cheatsheet!
A collection of keywords used in SQL statements, a description, and where appropriate an example. Some of the more advanced keywords have their own dedicated section.
Where MySQL is mentioned next to an example, this means this example is only applicable to MySQL databases (as opposed to any other database system).
Comments allow you to explain sections of your SQL statements, without being executed directly.
In SQL, there are 2 types of comments, single line and multiline.
Single Line Comments
Single line comments start with ‘- -’. Any text after these 2 characters to the end of the line will be ignored.
-- This part is ignoredSELECT * FROM customers;
Multiline comments start with /* and end with */. They stretch across multiple lines until the closing characters have been found.
/*This is a multiline comment
It can span across multiple lines*/SELECT * FROM customers;/*This is another comment. You can even put code within a comment to prevent its executionSELECT * FROM icecreams;*/
Data Types in MySQL
When creating a new table or editing an existing one, you must specify the type of data that each column accepts.
In the example below, data passed to the id column must be an int (integer), while the FirstName column has a VARCHAR data type with a maximum of 255 characters.
CREATE TABLE customers(id int,FirstName varchar(255));
1. String Data Types
2. Numeric Data Types
3. Date/Time Data Types
1. Arithmetic Operators
+ -- Add
– -- Subtract
* -- Multiply
/ -- Divide
% -- Modulus
2. Bitwise Operators
& -- Bitwise AND
| -- Bitwise OR
^ -- Bitwise exclusive OR
3. Comparison Operators
= -- Equal to
> -- Greater than
< -- Less than
>= -- Greater than or equal to
<= -- Less than or equal to
<> -- Not equal to
4. Compound Operators
+= -- Add equals
-= -- Subtract equals
*= -- Multiply equals
/= -- Divide equals
%= -- Modulo equals
&= -- Bitwise AND equals
^-= -- Bitwise exclusive equals
|*= -- Bitwise OR equals
1. String Functions
2. Numeric Functions
3. Date Functions
4. Miscellaneous Functions
In SQL, Wildcards are special characters used with the LIKE and NOT LIKE keywords. This allows us to search for data with sophisticated patterns rather efficiently
% -- Equates to zero or more characters.Example: Find all customers with surnames ending in ‘ory’.SELECT * FROM customersWHERE surname LIKE '%ory';_ -- Equates to any single character.Example: Find all customers living in cities beginning with any 3 characters, followed by ‘vale’.SELECT * FROM customersWHERE city LIKE '_ _ _vale';[charlist]Equates to any single character in the list.Example: Find all customers with first names beginning with J, K or T.SELECT * FROM customersWHERE first_name LIKE '[jkt]%';
In relational databases, there is a concept of primary and foreign keys. In SQL tables, these are included as constraints, where a table can have a primary key, a foreign key, or both.
1. Primary Key
A primary key allows each record in a table to be uniquely identified. There can only be one primary key per table, and you can assign this constraint to any single or combination of columns. However, this means each value within this column(s) must be unique.
Typically in a table, the ID column is a primary key, and is usually paired with the AUTO_INCREMENT keyword. This means the value increases automatically as and when new records are created.
Create a new table and setting the primary key to the ID column.
CREATE TABLE customers (id int NOT NULL AUTO_INCREMENT,FirstName varchar(255),Last Name varchar(255) NOT NULL,address varchar(255),email varchar(255),PRIMARY KEY (id));
2. Foreign Key
A foreign key can be applied to one column or many and is used to link 2 tables together in a relational database.
The table containing the foreign key is called the child key,
The table containing the referenced (or candidate) key is called the parent table.
This essentially means that the column data is shared between 2 tables, as a foreign key also prevents invalid data from being inserted which isn’t also present in the parent table.
Create a new table and turn any column that references IDs in other tables into foreign keys.
CREATE TABLE orders (id int NOT NULL,user_id int,product_id int,PRIMARY KEY (id),FOREIGN KEY (user_id) REFERENCES users(id),FOREIGN KEY (product_id) REFERENCES products(id));
Indexes are attributes that can be assigned to columns that are frequently searched against to make data retrieval a quicker and more efficient process.
CREATE INDEX -- Creates an index named ‘idx_test’ on the first_name and surname columns of the users table. In this instance, duplicate values are allowed.CREATE INDEX idx_testON users (first_name, surname);CREATE UNIQUE INDEX -- The same as the above, but no duplicate values.CREATE UNIQUE INDEX idx_testON users (first_name, surname);DROP INDEX -- Removes an index.ALTER TABLE usersDROP INDEX idx_test;
In SQL, a JOIN clause is used to return a result which combines data from multiple tables, based on a common column which is featured in both of them
There are a number of different joins available for you to use:-
- Inner Join (Default): Returns any records which have matching values in both tables.
- Left Join: Returns all of the records from the first table, along with any matching records from the second table.
- Right Join: Returns all of the records from the second table, along with any matching records from the first.
- Full Join: Returns all records from both tables when there is a match.
A common way of visualising how joins work is like this:
SELECT orders.id, users.FirstName, users.Surname, products.name as ‘product name’FROM ordersINNER JOIN users on orders.user_id = users.idINNER JOIN products on orders.product_id = products.id;
A view is essentially an SQL results set that gets stored in the database under a label, so you can return to it later without having to rerun the query. These are especially useful when you have a costly SQL query which may be needed a number of times, so instead of running it over and over to generate the same results set, you can just do it once and save it as a view.
To create a view, you can do so like this:
CREATE VIEW priority_users ASSELECT * FROM usersWHERE country = ‘United Kingdom’;
Then in future, if you need to access the stored result set, you can do so like this:
SELECT * FROM [priority_users];
With the CREATE OR REPLACE command, a view can be updated.
CREATE OR REPLACE VIEW [priority_users] ASSELECT * FROM usersWHERE country = ‘United Kingdom’ OR country=’USA’;
To delete a view, simply use the DROP VIEW command.
DROP VIEW priority_users;