MySQL and Database Design

Data Definition Language

SHOW DATABASES;

CREATE DATABASE Test;

USE Test;

SHOW TABLES;

DROP DATABASE Test;

Data Types

• INT: Whole numbers
• FLOAT(M, D): Decimal numbers (approximate)
  • M: length
  • D: length after the decimal point
  • allow rounding, e.g. for FLOAT(3,1), number 6.25 will be rounded up to 6.3
• DECIMAL(M, D): Decimal numbers (percise)
• CHAR(N): Fixed length character
• VARCHAR(N): Varying length character
• ENUM(‘M’, ‘F’): Value form a defined list
• BOOLEAN: True or False values
• DATE: Date (YYYY-MM-DD)
• DATETIME: Date and time (YYYY-MM-DD HH-mm-SS)
• TIME: Time (HHH-mm-SS), can be larger than 24 hours
• YEAR: Year (YYYY)

Primary and Foreign keys

Primay Key

• A primay key is a column, or set of columns, which uniquely identifies a record within a table
• A primay key must be unique
• A primay key cannot be NULL
• A table can only have one primary key

Foreign Key

• A foreign key is used to link two tables together
• A foreign key is a column whose values match the values of another tables primay key column
• The table with the primary key is called the reference, or parent table and the table with the foreign key is called the child table
• A table can have multiple foreign keys

SQL Queries

Create Table

CREATE TABLE products (
	id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30),
  price DECIMAL(3, 2)
);

CREATE TABLE customers (
	id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  gender ENUM('F', 'M'),
  phone_number VARCHAR(11)
);

CREATE TABLE orders (
	id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  customer_id INT,
  order_time DATETIME,
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Alter Table

# 1. show details of a table
DESCRIBE <tablename>;

# 2. add column to table
ALTER TABLE products
ADD COLUMN coffee_origin VARCHAR(30);

# 3. delete column from table
ALTER TABLE prodcuts
DROP COLUMN coffee_origin;

# 4. add primary key to table
ALTER TABLE <tablename>
ADD PRIMARY KEY (columnname);

# 5. delete primary key from table
ALTER TABLE <tablename>
DROP PRIMARY KEY;

# 6. make up a constraint name when you add foreign key
ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname>
FOREIGN KEY (<columnname>) REFERENCES <tablename>(<columnname>);

# example
ALTER TABLE people
ADD CONSTRAINT FK_PeopleAddress
FOREIGN KEY (address_id) REFERENCES addresses(id);

# 7. delete foreign key from table
ALTER TABLE <tablename>
DROP FOREIGN KEY <constraintname>;

# example
ALTER TABLE people
DROP FOREIGN KEY FK_PeopleAddress;

# 8. add constraint to table
ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname> UNIQUE (<columnname>);

# example
ALTER TABLE pets
ADD CONSTRAINT u_species UNIQUE (species);

# 9. delete constraint from table
ALTER TABLE <tablename>
DROP INDEX <constraintname>;

# example
ALTER TABLE pets
DROP INDEX u_species;

# 10. change column name, using back ticks for column names, you can change the data type at the same time too.
ALTER TABLE <tablename>
CHANGE `old_column_name` `new_column_name` <data type>

# example
ALTER TABLE pets
CHANGE `species` `animal_type` VARCHAR(20);

# 11. change a column data type
ALTER TABLE <tablename>
MODIFY <columnname> <date type>

# example
ALTER TABLE addresses
MODIFY city VARCHAR(30);

Delete Table

DROP TABLE products;

Delete All Data from Table

TRUNCATE TABLE products;

Data Manipulation Language

Insert

INSERT INTO <tablename> (<column1>, <column2>, <column3>)
VALUES ('value1', 'value2', 'value3');

# example
INSERT INTO products (name, price, coffee_origin)
VALUES ('Espresso', 2.50, 'Brazil');

Update

UPDATE <tablename>
SET <columnname> = 'value'
WHERE <columnname> = 'value';

# example
UPDATE products
SET coffee_origin = 'Sri Lanka'
WHERE id = 7;

# set safe update to false so we can use other columns (not just primary key) in WHERE clause to update values
SET SQL_SAFE_UPDATES=0;

Delete

DELETE FROM people
WHERE name = 'John';

# delete all rows in a table, just don't add the WHERE clause
DELETE FROM people;

Selecting from a table

SELECT <columnname> FROM <tablename>
WHERE <columnname> = <value> 
AND <columnname> = <value>
OR <columnname> = <value>;

SELECT * FROM customers
WHERE last_name IN ('Taylor', 'Bluth', 'Armstrong');

SELECT * FROM orders
WHERE order_time BETWEEN '2017-01-01' AND '2017-01-07';

# select all customers whos last name starts with 'W'
SELECT * FROM customers
WHERE last_name LIKE 'W%';

SELECT * FROM products
ORDER BY price ASC;

SELECT * FROM products
ORDER BY price DESC;

SELECT DISTINCT * FROM products;

# list 5 rows start at 6th row
SELECT * FROM customers
LIMIT 5 OFFSET 5;

# name alias
SELECT name as coffee, price, coffee_origin as country
FROM products;

Select From Multiple Tables

What is a join

• Joins allow you to retrieve data from multiple tables in a single select statement
• To join two tables there needs to be a related column between them
• There are many different kinds of join

INNER JOIN

• Will retrieve data only when there is matching values in both values

LEFT JOIN

• Will retrieve all data from the left table and matching rows from the right table

RIGHT JOIN

• Will retrieve all data from the right table and matching rows from the left table

There is no FULL JOIN in MySQL

SELECT products.name, orders.order_time FROM orders
INNER JOIN products ON orders.product_id = products.id;

# with table alias
SELECT p.name, o.order_time FROM orders o
JOIN products p ON o.product_id = p.id;

SELECT o.id, o.order_time, c.phone_number, c.last_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
ORDER BY o.order_time
LIMIT 10;

# Join multiple tables
SELECT p.name, p.price, c.first_name, c.last_name, o.order_time
FROM products p
JOIN orders o ON p.id = o.product_id
JOIN customers c ON c.id = o.customer_id;

Database Design

Normalization

• Normalization is the process of efficiently organizing data in a database
• To eliminate redundant data
• To only store related data in a table
• Reduce storage space
• Reduce insert, update and deletion anomalies
• Improve query performance

First Normal Form

Tables are in first normal form if

• No repeated rows of data
• Columns only contain a single value
• Table has a primary key

Second Normal Form

Tables are in second normal form if

• They conform to first normal form
• Every column that is not a primary key of the table is dependent on the whole of the primary key

Third Normal Form

Tables are in third normal form if

• They conform to second normal form
• Every column that is not a primary key is only dependent on the whole of the primary key (they can’t dependent on other columns than primary key)

Relationships

Tables are related through primary and foreign keys

One to One

• Where a key to one table appears no more than once as the key in another table and vice versa

One to Many

• Where a primary key of one table can be in multiple rows of a foreign key column of another table

Many to Many

• Where two tables can have many instances of each other

Constraints

• NOT NULL: a column can’t contain any null values
• UNIQUE: a column can’t contain any duplicate values of data
• PRIMARY KEY: a column that uniquely identifies each row of data
• FOREIGN KEY: a column which is related to a primary key in another table
• CHECK: controls the values that can be inserted into a column
• DEFAULT: if no values is inserted into a column, you can set a default value

Aggregate Functions

• Perform a calculations on data within a column and returns one result row
• Can use GROUP BY clauses to group the results by one or more columns
• Can use a HAVING clause in a similar way to a WHERE clause in a SELECT statement to filter the resutls set.

Subqueries

• Can be used in a SELECT, INSERT, UPDATE or DELETE query
• The nested query can be in the WHERE clause or the FROM
• Two types of subquery
  • Non-correlated
  • Correlated

Non-correlated subquery

• The inner query can run independently of the outer query
• Inner query runs first and produces a result set, which is then used by the outer query

SELECT id, start_time FROM screenings
WHERE film_id IN
(
  SELECT id FROM films
  WHERE length_min > 120
);

Correlated subquery

• The inner query can’t run independently of the outer query
• The inner query runs for every row in the outer query

SELECT screening_id, customer_id, 
(
  SELECT COUNT(seat_id)
  FROM reserved_seat WHERE booking_id = b.id
)
FROM bookings b;

SQL Functions

• Functions are stored programs which can be passed parameters and return a value
• we have already seen some MySQL functions - aggregate functions