Self Reference

SQL - Structured Query Language

A database is a collection of data that is organised in a manner that facilitates ease of access, as well as efficient managing and updating.
A table has a specified number of columns but can any number of rows.
A field are the columns and the records are rows.
A primary key is field in a table that uniquely identifies the table records.
- New unique value for each row.
- Cannot contain null values.

Uses of SQL

Insert, update or delete records in a database.
Create new databases, table, stored procedure, views.
Retrieve data from a database.
Execute queries against a database.
Set permissions on table, procedure and views.

SQL is an ANSI(American National Standards Institute) standard, but there are different versions.

SHOW displays information contained in the database and its tables.
SHOW DATABASES returns the list of all the databases on the server.
SHOW TABLES returns all the list of the tables in a database.
SHOW COLUMNS displays information about columns in a given table. It displays the following values:
- Field: Column name
- Type: Column data type
- Key: Indicates whether a column is indexed
- Default: Default value assigned to column
- Extra: May contain any additional information that is available about a given column.

SELECT is used to select data from a database. It is stored in a result table called result-set.

Syntax:

SELECT column_list
FROM table_name

SELECT column_list
FROM table_name

A query may retrieve information from selected or all columns in a table.

To select all columns in a table:

SELECT * FROM table_name

SELECT * FROM table_name

In SQL asterisk(*) means all.

DISTINCT keyword is used in conjunction with SELECT to eliminate all duplicate records and return only the unique ones.

Syntax:

SELECT DISTINCT column_1, column_2
FROM table_name

SELECT DISTINCT column_1, column_2
FROM table_name

LIMIT keyword is used to retrieve a subset of records.

Syntax:

SELECT column_list
FROM table_name
LIMIT [number_of_records]

SELECT column_list
FROM table_name
LIMIT [number_of_records]

Sample:

SELECT ID, FirstName, LastName, City
FROM customers
LIMIT 3, 4;

SELECT ID, FirstName, LastName, City
FROM customers
LIMIT 3, 4;

This query picks up 4 records, starting from the 3rd position.

The index of the first row is one.

ORDER BY is used with SELECT to sort the returned data.

Sample:

SELECT * FROM Customers
ORDER BY FirstName ;

SELECT * FROM Customers
ORDER BY FirstName ;

It is in ascending order by default.
To change the order type to ascending or decending, use ASC and DESC respectively.

It is used to extract only records that fulfill a specified criterion.

Syntax:

SELECT column_list FROM table_name
WHERE condition;

SELECT column_list FROM table_name
WHERE condition;

E.g. To select a specific record,

SELECT * from customers
WHERE ID = 7

SELECT * from customers
WHERE ID = 7

Use quotation marks when working with text values. E.g.

SELECT * FROM customers
WHERE city = 'New York';

SELECT * FROM customers
WHERE city = 'New York';

LIKE Operator

It is used to specify a search condition within a WHERE clause.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

pattern uses simple regex expressions for evaluations. E.g.
- _ matches any single character.
- % used to mark an arbituary number of characters (including zero characters).

Pattern Sample Usage:

SELECT * FROM employees
WHERE FName LIKE 'A%'

SELECT * FROM employees
WHERE FName LIKE 'A%'

'A%' means, beginning with 'A'
'%A' means, ending with 'A'

There are 2 types of operators:
- Conditional operators
- Logical opeerators

Conditional Operators

Operator	Description
=	equal to
!=	not equal to
>	greater than
<	less than
<=	less than or equal to
>=	greater than or equal to
BETWEEN	between an inclusive range

BETWEEN Operator

Sample usage:

SELECT column_name FROM table_name
WHERE column_ name BETWEEN value1 AND value2

SELECT column_name FROM table_name
WHERE column_ name BETWEEN value1 AND value2

Logical Operator

It combines two logical values and return a result of True, False or Null.

Operator	Description
AND	Returns True if both expressions are true
OR	Returns True if either expressions are true
IN	Returns True if one of the operand is equal to one of a list of expressions
NOT	Returns True if expression is not true
NOT IN	Allows you to exclude a list of specific values from the result-set.

IN Operator

Allows you to specify more values in the WHERE 'clause'.

Comes in use in replace of multiple OR
Sample:

SELECT * FROM customers
WHERE city IN ('N', 'L', 'C')

SELECT * FROM customers
WHERE city IN ('N', 'L', 'C')

Concat Keyword

Allows you to concatenate two or more text values and returns the concatenated string. E.g.

SELECT CONCAT(FName, ',', City)
FROM Customers

SELECT CONCAT(FName, ',', City)
FROM Customers

As Keyword

Concatenation results in a new column.
The default column name will be the concat function.

A custom name can be assigned using the AS keyword.
Sample:

SELECT CONCAT(FName, ',', City)
AS new_column_name FROM Customers

SELECT CONCAT(FName, ',', City)
AS new_column_name FROM Customers

It is a query in another query.

DESC sorts in descending order.
ASC sorts in ascending order.

Sample:

# # separated queries

# Getting the average
SELECT AVG(salary) FROM employees

# Using the average in next query
SELECT FName, salary
FROM employees
WHERE salary > 3100
ORDER BY salary DESC;

# # separated queries

# Getting the average
SELECT AVG(salary) FROM employees

# Using the average in next query
SELECT FName, salary
FROM employees
WHERE salary > 3100
ORDER BY salary DESC;

Always enclose subquery in parenthesis without using semi-colon.

## making same query as above using subquery
SELECT FName, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees
)
ORDER BY salary DESC;

## making same query as above using subquery
SELECT FName, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees
)
ORDER BY salary DESC;

Joined tables shows a temporary table showing data from the joined tables.
To join two or more tables, specify them as a comma separated list in the FROM clause; setting a condition that at least each must contain the same primary key.

Sample:

SELECT customers.ID, customers.Name, orders.name, orders.amount
FROM customers, orders
WHERE customers.ID = orders.ID
ORDER BY customers.ID

SELECT customers.ID, customers.Name, orders.name, orders.amount
FROM customers, orders
WHERE customers.ID = orders.ID
ORDER BY customers.ID

Custom names can be used for tables to shorten the join statement.

Sample:

SELECT ct.ID, ct.Name, ord.Name, ord.Amount
FROM customers AS ct, orders AS ord
WHERE ct.ID = ord.ID
ORDER BY ct.ID:

SELECT ct.ID, ct.Name, ord.Name, ord.Amount
FROM customers AS ct, orders AS ord
WHERE ct.ID = ord.ID
ORDER BY ct.ID:

Types Of Join

Inner Join
Left join
Right join

Inner Join

It is equivalent to join.
It returns rows where there's a match between tables.

Syntax:

SELECT column_name(s)
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;

SELECT column_name(s)
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;

ON specifies the INNER JOIN condition.
INNER JOIN is the intersection of tables.

Left Join

Returns all rows from the left table even if there are no matches in the right table.

Syntax:

SELECT column_name(s)
FROM table1 LEFT JOIN table2
ON table1.column_name = table2.column_name;

SELECT column_name(s)
FROM table1 LEFT JOIN table2
ON table1.column_name = table2.column_name;

Right Join

Returns all rows from the right table, even if there are no matches in the left table.

Syntax:

SELECT column_name(s)
FROM table1 RIGHT JOIN table2
ON table1.column_name = table2.column_name;

SELECT column_name(s)
FROM table1 RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Union

It combines the result set of multiple SELECT statements and removes any duplicates.
All SELECT statements within the UNION must have the same number of columns and the same datatypes. The columns must be in the same order.

Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2

Union All

It combines the result set of multiple SELECT statements and does not remove any duplicates.
It is faster than UNION.

Syntax:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2

The INSERT INTO statement is used to add new rows of data to a table in a database.

Syntax:

INSERT INTO table_name
VALUES(val1, val2, val3, ...)

INSERT INTO table_name
VALUES(val1, val2, val3, ...)

Make sure the order of the values is the same order as the columns in the table.
Alternate syntax:
```
INSERT INTO table_name(col1, col2, col3, ...)
VALUES(val1, val2, val3, ...)
```
```
INSERT INTO table_name(col1, col2, col3, ...)
VALUES(val1, val2, val3, ...)
```
Any order can be specified as long as the values goes into their respective columns.
Data can also be inserted into specific columns only.

Update keyword

Allows to alter data in a table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

If WHERE clause is omitted, all values in the table will be updated.

Delete Keyword

Used to remove a record from a table.

Syntax:

DELETE FROM table_name
WHERE condition;

DELETE FROM table_name
WHERE condition;

If WHERE clause is omitted, all records in the table will be deleted.

Sample:

DELETE FROM customers
WHERE customerName = 'Dave';

DELETE FROM customers
WHERE customerName = 'Dave';

Alter Table

Used to add, delete, or modify columns in an existing table.
Used to add or drop various constraints.

E.g.

ALTER TABLE People
ADD DateOfBirth DATE;

ALTER TABLE People
ADD DateOfBirth DATE;

Dropping

Syntax:

## Deleting a column
ALTER TABLE table_name
DROP COLUMN column_name

## Deleting a table
DROP TABLE table_name

## Deleting a column
ALTER TABLE table_name
DROP COLUMN column_name

## Deleting a table
DROP TABLE table_name

Renaming

Syntax:

## Renaming a table
RENAME TABLE table_name TO new_table_name

## Renaming a column
ALTER TABLE table_name
CHANGE column_name new_column_name DATA_TYPE

#OR
ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name

## Renaming a table
RENAME TABLE table_name TO new_table_name

## Renaming a column
ALTER TABLE table_name
CHANGE column_name new_column_name DATA_TYPE

#OR
ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name

CREATE TABLE is used to create a new table.
It involves naming the table and defining its columns and each column's data type.

Syntax:

CREATE TABLE table_name (
column1 data-type(size) constraint,
column2 data-type(size) constraint,
column3 data-type(size) constraint,
)

CREATE TABLE table_name (
column1 data-type(size) constraint,
column2 data-type(size) constraint,
column3 data-type(size) constraint,
)

size specifies the maximum length of table column.

Numeric Datatype

INT: a normal integer (signed/unsigned)
FLOAT(M, D): A floating point number that cannot be unsigned.
- M - Display length
- D - Number of decimal places
DOUBLE(M, D): A double-precision floating point number that cannot be unsigned.

Date & Type Datatype

DATE: A date in YYYY-MM-DD.
DATETIME: A date and time combination in YYYY-MM-DD HH:MM:SS format.
TIMESTAMP: A timestamp is calculated from midnight, January 1, 1970.
TIME: Stores time in HH:MM:SS format.

String Datatype

CHAR(M): Fixed length character string. Size is specified in (M). Max=255.
VARCHAR(M): Variable-length character.
BLOB: Binary Large Objects; is used to store large amounts of binary data such as images or other files.
TEXT: Large amount of text data.

They are used to specify rules for table data.

Some List Of Constraints

Constraint	Description
NOT NULL	Indicates that a column cannot contain a null value.
UNIQUE	Does not allow to insert a duplicate value in a column.
PRIMARY KEY	Enforces the table to accept unique data for a specific column. It creates a unique index for accessing the table faster.
CHECK	Determines whether a value is is valid or not from a logical expression.
DEFAULT	While inserting data, if no value is supplied then the column gets the value set as DEFAULT.
AUTO INCREAMENT	Allows a unique number to be generated when a new record is inserted into a table.

During table creation, specify the constraints after the data type of that column

Sample use of constraints:

CREATE TABLE test (
UserID NOT NULL AUTO INCREAMENT PRIMARY KEY(UserID)
    )

CREATE TABLE test (
UserID NOT NULL AUTO INCREAMENT PRIMARY KEY(UserID)
    )

A view is a virtual table that is based on the resilt-set of an SQL statement.
Uses:
- Structure data in a way that users or classes of users find natural or intuitive.
- Restrict access to the data in a way that a user can see and sometimes modify exactly what they need and no more.
- Summarize data from various tables and use it to generate reports.

Syntax:

CREATE VIEW view_name AS 
SELECT column_name(s) 
FROM table_name
WHERE condition

CREATE VIEW view_name AS 
SELECT column_name(s) 
FROM table_name
WHERE condition

A view always shows up-to-date data.

Updating Data

Syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Deleting Views

Syntax:

DROP VIEW view_name

DROP VIEW view_name

Always end SQL statements with a semi-colon(;)
SQL commands are case insensitive
Multiple arguements are separated by comma
The table name can be provided prior to the column name, by separating them with a dot(.). It is also called the fully qualified name. E.g.
```
SELECT City FROM Customers
#is the same as 
SELECT Customers.City FROM Customers
```
```
SELECT City FROM Customers
#is the same as 
SELECT Customers.City FROM Customers
```
It is normally used when multiple tables have the same column name.

Other Functions

UPPER(): converts all letters in specified string into uppercase.
LOWER(): converts string into lowercase.
SQRT(): returns the square root of a given value in the arguement.
AVG(): returns the average value of a numeric column.
SUM(): returns the sum of values in a numeric column.
MIN(): used to return the minimum value of an expression in the SELECT command.

MySQL Reference

Introduction

SQL - Structured Query Language

Uses of SQL

Show Keyword

Select Keyword

Distinct Keyword

Limit Keyword

Order By Keyword

Where Keyword

LIKE Operator

Operators

Conditional Operators

BETWEEN Operator

Logical Operator

IN Operator

Concat Function

Concat Keyword

As Keyword

Sub Queries

Joining Tables

Types Of Join

Inner Join

Left Join

Right Join

Set Operations

Union

Union All

Inserting Data

Updating Data

Update keyword

Delete Keyword

Alter Table

Dropping

Renaming

Creating Tables

Numeric Datatype

Date & Type Datatype

String Datatype

Constraints

Views

Updating Data

Deleting Views

Things To Note

Other Functions