SQL in 10 Minutes

A hackable cheatsheet to get you up and running with SQL

What is SQL?

Image for post
Image for post
Source
SELECT * FROM customers;
Image for post
Image for post
Source: Database Guide
SELECT FirstName, LastName FROM customers;
SELECT * FROM customers WHERE age >= 30 ORDER BY age ASC;
INSERT INTO customers(FirstName, LastName, address, email)VALUES (‘Jason’, ‘Dsouza’, ‘McLaren Vale, South Australia’, ‘test@fakeGmail.com’);

Why Learn SQL?

SQL vs MySQL

Installing MySQL

Windows

macOS

Using MySQL

The Icing on the Cake — the Cheatsheet!

Keywords

Comments

Single Line Comments

-- This part is ignoredSELECT * FROM customers;

Multiline Comments

/*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

CREATE TABLE customers(id int,FirstName varchar(255));

1. String Data Types

2. Numeric Data Types

3. Date/Time Data Types

Operators

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

Functions

1. String Functions

2. Numeric Functions

3. Date Functions

4. Miscellaneous Functions

Wildcard Characters

% -- 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]%';

Keys

1. Primary Key

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

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

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;

Joins

Image for post
Image for post
Source: Website Setup
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;

View

Creating Views

CREATE VIEW priority_users ASSELECT * FROM usersWHERE country = ‘United Kingdom’;
SELECT * FROM [priority_users];

Replacing Views

CREATE OR REPLACE VIEW [priority_users] ASSELECT * FROM usersWHERE country = ‘United Kingdom’ OR country=’USA’;

Deleting Views

DROP VIEW priority_users;

Conclusion

Written by

I train ConvNets. Currently building Caer, a lightweight Computer Vision library in Python.

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store