MySQL Quick Reference

mysql-ref.md

MySQL Reference

Notes from a course on MySQL

• Basic MySQL Commands
• Basic CRUD Commands
  • Creating Databases
  • Dropping Databases
  • Using Databases
  • Creating Tables
  • See What's In Tables
  • Dropping Tables
  • Adding Data to Tables
  • NULL / NOT NULL & Default Values
  • Primary Key & Auto Increment
  • Reading Data In Tables
  • WHERE Keyword
  • HAVING Keyword
  • Aliases
  • Updating Data In Tables
  • Deleting Data From Tables
• String Functions
  • CONCAT (x, y, z) & CONCAT_WS
  • SUBSTRING
  • TRIM
  • REPLACE
  • REVERSE
  • CHAR_LENGTH
  • Changing String Case
• Refining Selections
  • DISTINCT
  • ORDER BY
  • LIMIT
  • LIKE With Wildcards
• Aggregate Functions
  • COUNT
  • GROUP BY
  • MIN and MAX
  • SUM
  • AVG
• Data Types
  • CHAR and VARCHAR
  • DECIMAL
  • FLOAT and DOUBLE
  • Number Types: which to use?
  • Dates & Times
  • Formatting Dates
  • Date Math
  • Timestamps and TIMESTAMP
• Logical Operators
  • Common Comparison Operators
  • More Search Refiners
  • Case Statements
• Relationships
  • One To Many
    • Selecting Data From Tables - Inner Joins
    • Selecting Data From Tables - Left & Right Joins
  • On Delete Cascade
  • Many To Many
  • Displaying Data
• Schema Design
• Database Triggers
  • Definition
  • How & When To Use Triggers

BASIC MYSQL & CLOUD9 COMMANDS

top

Cloud 9 Commands

• mysql-ctl start: Start the MySql server
• mysql-ctl stop: Stop the MySQL server (rarely do this)
• mysql-ctl cli: Start the MySQL command line interface
• exit; or quit; or \q; or ctrl-c: Leave the MySQL CLI

MySQL Commands Everywhere

• help;: Get a list of commands
• SHOW DATABASES;: Show all of your databases on this server
• select @@hostname;: See your own host name

BASIC CRUD COMMANDS

top

Creating Databases

1. Start the CLI: mysql-ctl cli;
2. List available databases: SHOW DATABASES;
3. The general command for creating a database: CREATE DATABASE database_name;
   • A specific example: CREATE DATABASE soap_store;

Dropping Databases

• DROP DATABASE database_name;
• For Example: DROP DATABASE hello_world_db;

Using Databases

• USE <database name>;
• For Example: USE dog_walking_app;
• SELECT database();: Find out what database you're using now

Creating Tables

• The format:

CREATE TABLE tablename
  (
    column_name data_type,
    column_name data_type
  );

• For example:

CREATE TABLE cats
  (
    name VARCHAR(100),
    age INT NOT NULL
  );

• Include NOT NULL if the column is not allowed to be empty.

See What's In Tables

• SHOW TABLES;: See what tables are available in this database
• SHOW COLUMNS FROM tablename;: Show how the table has been set up
• DESC tablename;: Same as SHOW COLUMNS FROM or also DESCRIBE
• Example output:

mysql> DESCRIBE tweets;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| username        | varchar(15)  | YES  |     | NULL    |       |
| content         | varchar(140) | YES  |     | NULL    |       |
| favorites_count | int(11)      | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

Dropping Tables

• DROP TABLE <tablename>;
• A specific example: DROP TABLE cats;

Adding Data to Tables

• The format: INSERT INTO table_name(column_name) VALUES (data);
• For example: INSERT INTO cats(name, age) VALUES ('Jetson', 7);
• NOTE: The order you add the data doesn't matter, but it must be consistent between how it's initiated and what the values are.
• Multiple insert just extends the previous:

INSERT INTO table_name
            (column_1_name, column_2_name)
VALUES      (value_col_1, value_col_2),
            (value_col_1, value_col_2),
            (value_col_1, value_col_2);

• SHOW WARNINGS;: If the most recent insert gives a warning, this is how you see it. But you must do this right when the warning is given; it won't work later (although app server should have error handling)

NULL / NOT NULL & Default Values

• NOT NULL means this column is not allowed to have no value
• Unless specified the default value for an INT will be 0
• Unless specified the default value for a VARCHAR will be an empty string ''
• You can insert data into a table as NULL unless that column is specifically marked NOT NULL
• To set a default value, add this when creating the table (can be combined with NOT NULL):

CREATE TABLE cats4
  (
    name VARCHAR(20) NOT NULL DEFAULT ‘unnamed’,
    age INT NOT NULL DEFAULT 99
  );

Primary Key & Auto Increment

• Primary key is a unique value assigned to each row for identification
• This can be set up as an auto-incrementing column when creating a table:

CREATE TABLE unique_cats2 (
    cat_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (cat_id)
);

• Primary key can also be set along with the actual column:

CREATE TABLE employees (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    first_name VARCHAR(40) NOT NULL,
    last_name VARCHAR(40) NOT NULL,
    middle_name VARCHAR(40),
    age INT NOT NULL,
    current_status VARCHAR(40) NOT NULL DEFAULT 'employed'
);

• Result:

mysql> DESC employees;
+----------------+-------------+------+-----+----------+----------------+
| Field          | Type        | Null | Key | Default  | Extra          |
+----------------+-------------+------+-----+----------+----------------+
| id             | int(11)     | NO   | PRI | NULL     | auto_increment |
| last_name      | varchar(40) | NO   |     | NULL     |                |
| first_name     | varchar(40) | NO   |     | NULL     |                |
| middle_name    | varchar(40) | YES  |     | NULL     |                |
| age            | int(11)     | NO   |     | NULL     |                |
| current_status | varchar(40) | NO   |     | employed |                |
+----------------+-------------+------+-----+----------+----------------+

Reading Data In Tables

• SELECT * FROM tablename: Read all data in the table, in the default order / how the table was created.
• SELECT name FROM tablename: Show one column.
• Can also be combined: SELECT name, age FROM tablename
• Columns will be displayed in the order that the SELECT command is written; but again if it's SELECT * it will display in the default order.

WHERE Keyword

• Select by age: SELECT * FROM cats WHERE age=4; (INTs don't require quotes)
• Select by name: SELECT * FROM cats WHERE name='Egg'; (VARCHARs require quotes)
• Queries are case-insensitive. Produces same result as previous: SELECT * FROM cats WHERE name='egG';
• Can also compare columns to each other: SELECT cat_id, age FROM cats WHERE cat_id=age;

HAVING Keyword

• Works similarly to WHERE in cases where WHERE can't be used.
• Can be used with subqueries.

Aliases

• Change the display of column names. Useful when joining tables which may have columns of the same name.
• SELECT cat_id AS id, name FROM cats;: Renames cat_id column display to id
• SELECT name AS 'cat name', breed AS 'kitty breed' FROM cats;: Can do multiple columns at once.

Updating Data In Tables

• The format: UPDATE tablename SET column_name='new value' WHERE column_name='select value'
• Change tabby cats to shorthair: UPDATE cats SET breed='Shorthair' WHERE breed='Tabby';
• Update Misty's age: UPDATE cats SET age=14 WHERE name='Misty';

Deleting Data From Tables

• The format: DELETE FROM table_name WHERE column_name="data"
• Best to select first to ensure you have the right data: SELECT * FROM cats WHERE name='egg';
• ...THEN run the delete command: DELETE FROM cats WHERE name='egg';
• DELETE FROM cats;: DELETES ALL ROWS IN THE TABLE!!!!!

STRING FUNCTIONS

top

CONCAT (x, y, z) & CONCAT_WS

• Concatenates x y and z together.
• If not part of a table: SELECT CONCAT('Hello', 'World'); // HelloWorld
• Or...SELECT CONCAT('Hello', '...', 'World'); // Hello...World
• ...notice that spaces have to be included as an argument of CONCAT
• Examples to create new column (and optionally rename it):

SELECT
  CONCAT(author_fname, ' ', author_lname)
FROM books;

SELECT
  CONCAT(author_fname, ' ', author_lname)
  AS 'full name'
FROM books;

SELECT author_fname AS first, author_lname AS last,
  CONCAT(author_fname, ' ', author_lname) AS full
FROM books;

• CONCAT_WS concatenates with a separator between each field:

SELECT
    CONCAT_WS(' - ', title, author_fname, author_lname)
FROM books;

SUBSTRING

• Substring gives you a portion of the string you identify
• Note, indexing starts at 1 not 0!
• Two numbers goes from 1st index to 2nd: SELECT SUBSTRING('Hello World', 1, 4); // Hell
• One number goes from this index to end: SELECT SUBSTRING('Hello World', 7); // World
• Negative number goes from end, that many indices: SELECT SUBSTRING('Hello World', -3); // rld
• Use with column names to output new column of substrings (alias optional): SELECT SUBSTRING(title, 1, 10) AS 'short title' FROM books;
• SUBSTR() is a valid alternate: SELECT SUBSTR(title, 1, 10) AS 'short title' FROM books;
• String functions can be combined. For example:

SELECT CONCAT
    (
        SUBSTRING(title, 1, 10),
        '...'
    ) AS 'short title'
FROM books;

• ...result:

+---------------+
| short title   |
+---------------+
| The Namesa... |
| Norse Myth... |
| American G... |
| Interprete... |
| A Hologram... |
| The Circle... |
| The Amazin... |
| Just Kids...  |
| A Heartbre... |
| Coraline...   |
| What We Ta... |
| Where I'm ... |
| White Nois... |
| Cannery Ro... |
| Oblivion: ... |
| Consider t... |
+---------------+

TRIM

• Trims whitespace from a string.
• TRIM(field_name)

REPLACE

• The format: SELECT REPLACE('original string', 'what you want to replace', what you want to replace it with);
• Example: SELECT REPLACE('Hello World', 'Hell', '%$#@'); // %$#@o World
• Example: SELECT REPLACE('Hello World', 'o', '0'); // Hell0 W0rld
• It's case-sensitive: SELECT REPLACE('HellO World', 'o', '#'); //HellO W#rld
• Also apples to whole columns: SELECT REPLACE(title, 'e ', '3') FROM books;
• Can be combined with other string functions (and optionally aliased):

SELECT
    SUBSTRING(REPLACE(title, 'e', '3'), 1, 10) AS 'weird string'
FROM books;

• Result:

+--------------+
| weird string |
+--------------+
| Th3 Nam3sa   |
| Nors3 Myth   |
| Am3rican G   |
| Int3rpr3t3   |
| A Hologram   |
| Th3 Circl3   |
| Th3 Amazin   |
| Just Kids    |
| A H3artbr3   |
| Coralin3     |
| What W3 Ta   |
| Wh3r3 I'm    |
| Whit3 Nois   |
| Cann3ry Ro   |
| Oblivion:    |
| Consid3r t   |
+--------------+

REVERSE

• Does what it says on the tin.
• SELECT REVERSE('Hello World'); // dlroW olleH
• Can be combined with other functions: SELECT CONCAT('woof', REVERSE('woof')); // wooffoow
• Can be called on table columns: SELECT CONCAT(author_fname, REVERSE(author_fname)) FROM books;

CHAR_LENGTH

• Returns character length of what you ask for.
• SELECT CHAR_LENGTH('Hello World'); // 11
• Can be combined with other functions: SELECT CONCAT(author_lname, ' is ', CHAR_LENGTH(author_lname), ' characters long') FROM books;

Changing String Case

• SELECT UPPER converts everything to upper case
• SELECT LOWERconverts everything to lower case
• SELECT UPPER('Hello World'); // HELLO WORLD
• SELECT CONCAT('MY FAVORITE BOOK IS ', LOWER(title)) FROM books; returns column of titles with all of the data in lower case

REFINING SELECTIONS

top

DISTINCT

• DISTINCT allows you to see entries in a column without duplicates
• Example: SELECT DISTINCT author_lname FROM books;
• If there are columns which have relevant data in adjacent columns, you can either concatenate the columns first (1), or use DISTINCT to evaluate data within all of the relevant columns (2)
  • 1: SELECT DISTINCT CONCAT(author_fname,' ', author_lname) FROM books;
  • 2: SELECT DISTINCT author_fname, author_lname FROM books;

ORDER BY

• Sort the data, default is ascending order ASC.
• Basic usage: SELECT author_lname FROM books ORDER BY author_lname;
• To sort by descending order, add DESC to the end of the command
• You can use index shorthand to define which column to order by : SELECT title, author_fname, author_lname FROM books ORDER BY 2; (Sorts by author_fname)
• You can sort by one column, and then a second with two arguments: SELECT author_fname, author_lname FROM books ORDER BY author_lname, author_fname;

LIMIT

• Limit the results you're querying to a specific number of results
• Example: SELECT title FROM books LIMIT 10;
• Often used in combination with ORDER BY: SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 5;
• You can use two numbers to specify a starting row (from index 0) and a number of rows: SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 10,1;
• To select through the end of the table, you can put any giant number: SELECT title FROM books LIMIT 5, 123219476457;

LIKE With Wildcards

• Allows you to search for similar items, fuzzy search
• Uses % % wildcards to indicate where fuzzy data is allowed. Examples:
• SELECT title, author_fname FROM books WHERE author_fname LIKE '%da%';. This would return Dave, David, Cressida, etc.
• SELECT title, author_fname FROM books WHERE author_fname LIKE '%da';. This would only return Cressida.
• Wildcard search is case-insensitive: %da% would return David or DANIEL or dArReN
• Using LIKE without wildcards looks for exactly the search term: SELECT title FROM books WHERE title LIKE 'the'; is likely to return nothing (unless you have a book titled 'The')
• Underscore _ is used as a wildcard to denote one character place.
• So _ looks for a field with one character while __ looks for a field with 2 characters, and so on.
• Example: (235)234-0987 LIKE '(___)___-____'
• To search for data with these special characters, escape them with \: SELECT title FROM books WHERE title LIKE '%\%%'

AGGREGATE FUNCTIONS

top

COUNT

• SELECT COUNT(*) FROM books;: Count the number of entries in the database
• SELECT COUNT(author_fname) FROM books;: Counts the number of first_name entries in the database, including duplicates.
• SELECT COUNT(DISTINCT author_fname) FROM books;: Returns count of unique entries
• Counted columns can be combined if more than one field is necessary: SELECT COUNT(DISTINCT author_lname, author_fname) FROM books;
• To search for number of fields containing fuzzy match: SELECT COUNT(*) FROM books WHERE title LIKE '%the%';

GROUP BY

• GROUP BY summarizes or aggregates identical data into single rows
• Can't be used on its own, will always be combined with other things. For example: group films by genre and tell me how many films are in each genre; or group teas by color and tell me the average sales price of green tea vs red tea, etc.
• SELECT author_lname, COUNT(*) FROM books GROUP BY author_lname;: Counts the number of books per author and prints their name and the count. Result:

+----------------+----------+
| author_lname   | COUNT(*) |
+----------------+----------+
| Carver         |        2 |
| Chabon         |        1 |
| DeLillo        |        1 |
| Eggers         |        3 |
| Foster Wallace |        2 |
| Gaiman         |        3 |
| Harris         |        2 |
| Lahiri         |        2 |
| Saunders       |        1 |
| Smith          |        1 |
| Steinbeck      |        1 |
+----------------+----------+

• Multiple columns can be included in a row if needed: SELECT author_fname, author_lname, COUNT(*) FROM books GROUP BY author_lname, author_fname;. Notice Harris is now split as it should be:

+--------------+----------------+----------+
| author_fname | author_lname   | COUNT(*) |
+--------------+----------------+----------+
| Raymond      | Carver         |        2 |
| Michael      | Chabon         |        1 |
| Don          | DeLillo        |        1 |
| Dave         | Eggers         |        3 |
| David        | Foster Wallace |        2 |
| Neil         | Gaiman         |        3 |
| Dan          | Harris         |        1 |
| Freida       | Harris         |        1 |
| Jhumpa       | Lahiri         |        2 |
| George       | Saunders       |        1 |
| Patti        | Smith          |        1 |
| John         | Steinbeck      |        1 |
+--------------+----------------+----------+

• Counts can be concatenated with their values: SELECT CONCAT('In ', released_year, ' ', COUNT(*), ' book(s) released') AS year FROM books GROUP BY released_year ORDER BY COUNT(*) DESC LIMIT 5;:

+----------------------------+
| year                       |
+----------------------------+
| In 2001 3 book(s) released |
| In 2003 2 book(s) released |
| In 1981 1 book(s) released |
| In 2016 1 book(s) released |
| In 1989 1 book(s) released |
+----------------------------+

MIN and MAX

• Used to find minimum and maximum values in the data. Can be combined with GROUP BY.
• SELECT MIN(released_year) FROM books; returns the smallest year of all the books.
• THIS IS WRONG: SELECT MAX(pages), title FROM books;. It will result in the highest page number with the first title.
  • Instead you could use a sub-query: SELECT * FROM books WHERE pages = (SELECT Min(pages) FROM books); Inside the parens is evaluated first, then applied to the outer part.
  • Faster search solution since there's only one query: SELECT title, pages FROM books ORDER BY pages ASC LIMIT 1;
• Combine with GROUP BY to return the min/max of a field for that author:

SELECT author_fname,
       author_lname,
       Min(released_year)
FROM   books
GROUP  BY author_lname,
          author_fname
LIMIT 5;

RESULT:
+--------------+----------------+--------------------+
| author_fname | author_lname   | Min(released_year) |
+--------------+----------------+--------------------+
| Raymond      | Carver         |               1981 |
| Michael      | Chabon         |               2000 |
| Don          | DeLillo        |               1985 |
| Dave         | Eggers         |               2001 |
| David        | Foster Wallace |               2004 |
+--------------+----------------+--------------------+

SUM

• Add all of the values of a field together: SELECT SUM(pages) FROM books;
• Can be used in combination with GROUP BY to provide useful data, like the total number of pages written by each author:

SELECT author_fname,
       author_lname,
       Sum(pages)
FROM books
GROUP BY
    author_lname,
    author_fname;

AVG

• Find average of data from multiple rows: SELECT AVG(pages) FROM books;
• AVG by default returns 4 decimal places
• Like previous functions, it can be combined with GROUP BY for more utility

DATA TYPES

top

CHAR & VARCHAR

• CHAR is fixed to the length you declare when you create the column.
• VARCHAR is variable length, up to the length you declare when you create the column.
• Length value can be from 0 to 255.
• For CHAR, spaces are added to the right side and then removed when you display. A value with the pre-determined length is always stored in the database though.
• CHAR is faster when you're certain lengths will be fixed like US state abbreviations, Y/N flags, etc.

DECIMAL

• Takes two arguments: DECIMAL(total_number_of_digits, max_number_of_digits_after_decimal_point)
• Example: DECIMAL(5,2) can accept 382.34, 11.00, 23.87, etc.
• If you have a whole number it will add .00 to include the trailing decimals
• If you add a number bigger than the maximum constraint, it will give you the highest max number; for example 235498 will only be able to insert 999.99 as the highest within the constraints given
• Numbers will be rounded if they are entered with more decimal places than allowed.
• Calculations are exact

FLOAT and DOUBLE

• With these you can use larger numbers and they will take up less space in memory.
• BUT calculations are not exact: you start to see imprecision around 7 digit-long numbers for FLOAT and around 15 digits for DOUBLE.

Number Types: which to use?

• If precision is really important, like in calculating money, use DECIMAL
• If you can get away with less precision use DOUBLE as a first choice, since you get more precision.
• Or if you're certain you don't need precision and numbers will never be longer than 7 characters, use FLOAT to use less memory

Dates & Times

• DATE stores only a date in the format YYYY-MM-DD
• TIME stores only a time in the format HH:MM:SS
• DATETIME stores a date and time together in the format YYYY-MM-DD HH:MM:SS
• CURDATE() gives the current date
• CURTIME() gives the current time
• NOW() gives the current datetime

Formatting Dates

• Helper functions (see docs) can be applied to columns to display more meaningful information about dates.
• Example, if you have a date in the column birthday as 2012-03-22:
  • DAY(birthday) returns 22
  • DAYNAME(birthday) returns Thursday
  • DAYOFWEEK(birthday) returns 5 (5th day of the week with Sunday being 1)
  • DAYOFYEAR(birthday) returns 81
  • MONTH(birthday) returns 3
  • MONTHNAME(birthday) returns March
• To format dates nicely you can put these methods together and concatenate a nice display: SELECT CONCAT(MONTHNAME(birthdate), ' ', DAY(birthdate), ' ', YEAR(birthdate)) FROM people; would give March 22 2012
• OR you can use DATE_FORMAT with specifiers to do this more cleanly (see docs):
  • SELECT DATE_FORMAT(birthdt, '%m/%d/%Y at %h:%i') FROM people; returns 03/22/2012 at 07:16
  • Note that the days using DATE_FORMAT may be different, i.e. Sunday is 0 instead of 1

Date Math

• DATEDIFF(date1, date2) takes two dates and tells you the number of days between them
• DATE_ADD and DATE_SUB use INTERVAL to add/subtract a determinate amount of time to the date or datetime.
  • Example: SELECT birthdt, DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people; returns 2012-04-22
• + and - can accomplish the same thing as DATE_ADD/DATE_SUB: SELECT birthdt, birthdt + INTERVAL 1 MONTH FROM people; returns 2012-04-22
  • These can be chained into multiple operations: SELECT birthdt, birthdt + INTERVAL 15 MONTH + INTERVAL 10 HOUR FROM people;

Timestamps and TIMESTAMP

• TIMESTAMP is a data type in MySQL. It works the same a DATETIME, except its range is limited to dates between 1970-01-01 and 2038-01-19.
• TIMESTAMP uses less bytes than DATETIME so it's a preference if you're guaranteed to stay within that range.
• To set a timestamp for an addition to the database, you can set that column's default value:

CREATE TABLE comments (
    content VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

• You can also set the timestamp to automatically update if that row received a change:

CREATE TABLE comments2 (
    content VARCHAR(100),
    changed_at TIMESTAMP DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP
);

• Note CURRENT_TIMESTAMP has the same effect as using NOW()
• MySQL converts TIMESTAMP to UTC time for storage and then converts it back to the computer's timezone on retrieval
• To compare dates of different type, best to use CAST() to cast all values to the same type: SELECT CAST('2017-05-02' AS DATETIME);

LOGICAL OPERATORS

top

Common Comparison Operators

• Not equals != is the opposite of equals =
• NOT LIKE looks for the opposite of a LIKE statement with wildcards
• Greater than (or equal to) > (>=) and less than (or equal to) < (<=) work as expected
• Note when comparing letters: MySQL is case-insensitive: 'A' = 'a'
• Other letter comparisons work as expected: 'h' < 'p'
• For dual conditions where both must be true, use AND or &&:

SELECT *
FROM books
WHERE author_lname='Eggers'
    AND released_year > 2010
    && title LIKE '%novel%';

• For dual conditions where either must be true, use OR or ||:

SELECT title,
       author_lname,
       released_year,
       stock_quantity
FROM   books
WHERE  author_lname = 'Eggers'
     || released_year > 2010
OR     stock_quantity > 100;

• BETWEEN...AND looks for values within a range: SELECT title, released_year FROM books WHERE released_year BETWEEN 2004 AND 2015;
• NOT BETWEEN...AND does the opposite: SELECT title, released_year FROM books WHERE released_year NOT BETWEEN 2004 AND 2015;
• BETWEEN and NOT BETWEEN are inclusive, i.e. equivalent to >= ... <=
• To compare dates, it's best to CAST them all to be the same type before comparison:

SELECT name, birthdt
FROM people
WHERE birthdt
  BETWEEN CAST('1980-01-01' AS DATETIME)
  AND CAST('2000-01-01' AS DATETIME);

More Search Refiners

• IN and NOT IN let you provide a list of things to look for in a column. For example:

-- Long way, without IN
SELECT title, author_lname FROM books
WHERE author_lname='Carver' OR
      author_lname='Lahiri' OR
      author_lname='Smith';

-- Shorter way, with IN
SELECT title, author_lname FROM books
WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');

Case Statements

• Case statements allow you to add logic when working with the data.
• For example to add a 'GENRE' based on the year of release:

SELECT title, released_year,
       CASE
         WHEN released_year >= 2000 THEN 'Modern Lit'
         ELSE '20th Century Lit'
       END AS GENRE
FROM books;

• Case statements start with the CASE keyword, followed by WHEN to initiate a case and THEN to define the result. ELSE captures all other possibilities, and the statement must end with END. Also best to name it with AS for better display.
• Case statements can be chained with many conditions:

SELECT title, stock_quantity,
  CASE
    WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
    WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
    WHEN stock_quantity BETWEEN 101 AND 150 THEN '***'
    ELSE '****'
  END AS STOCK
FROM books LIMIT 5;

-- Returns:
+----------------------------------+----------------+-------+
| title                            | stock_quantity | STOCK |
+----------------------------------+----------------+-------+
| The Namesake                     |             32 | *     |
| Norse Mythology                  |             43 | *     |
| American Gods                    |             12 | *     |
| Interpreter of Maladies          |             97 | **    |
| A Hologram for the King: A Novel |            154 | ****  |
+----------------------------------+----------------+-------+

• Note that case statements cannot have commas , between cases

RELATIONSHIPS

top

One To Many

• One to many relationships connect tables of data together.
• Each table has a primary key, which is used to reference the relationship. In the related table, the primary key is referenced as a foreign key. Example:

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);
CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id) REFERENCES customers(id)
);

• Note the convention for naming foreign keys is tableName_columnName
• Once a foreign key is set and correctly references another table, it will be impossible to add data if that id does not exist in the foreign table.

Selecting Data From Tables - Inner Joins

• Without joins, finding orders placed by Boy George would either be a 2-step process, or you would use a subquery:

-- 2-Step Process:
SELECT id FROM customers WHERE last_name='George';
SELECT * FROM orders WHERE customer_id = 1;

-- Subquery:
SELECT * FROM orders WHERE customer_id =
  (
    SELECT id FROM customers
    WHERE last_name='George'
  );

• Cross joins are useless, and print out all of the data in a non-meaningful way: SELECT * FROM customers, orders;
• To narrow down the data and show meaningful information, use WHERE with an implicit inner join:

SELECT first_name, last_name, order_date, amount
FROM customers, orders
  WHERE customers.id = orders.customer_id;

• Inner joins only display data where there is overlap for both tables
• Best practice is to use an explicit inner join instead with the JOIN keyword:

SELECT first_name, last_name, order_date, amount
FROM customers
JOIN orders
    ON customers.id = orders.customer_id;

• The order you list the tables determines the display order.
• Joined tables can be manipulated any way an individual table can. Example:

SELECT
    first_name,
    last_name,
    SUM(amount) AS total_spent
FROM customers
JOIN orders
    ON customers.id = orders.customer_id
GROUP BY orders.customer_id
ORDER BY total_spent DESC;

-- Result:
+------------+-----------+-------------+
| first_name | last_name | total_spent |
+------------+-----------+-------------+
| George     | Michael   |      813.17 |
| Bette      | Davis     |      450.25 |
| Boy        | George    |      135.49 |
+------------+-----------+-------------+

Selecting Data From Tables - Left & Right Joins

• Left joins take all of the data from one table (on the left) and append data from another table to the right (where there is data). If there's no matching data for a particular row, it will print NULL
• Example:

SELECT * FROM customers
LEFT JOIN orders
    ON customers.id = orders.customer_id;

• When joining tables it may not be ideal to display NULL. You can use IFNULL to handle these instances: IFNULL(what_field_may_be_null, what_you_want_to_put_instead). Example:

SELECT
  first_name,
  last_name,
  IFNULL(SUM(amount), 0) AS total_spent
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY total_spent;

-- Result:
+------------+-----------+-------------+
| first_name | last_name | total_spent |
+------------+-----------+-------------+
| Blue       | Steele    |        0.00 |
| David      | Bowie     |        0.00 |
| Boy        | George    |      135.49 |
| Bette      | Davis     |      450.25 |
| George     | Michael   |      813.17 |
+------------+-----------+-------------+

• Right joins work the same as left joins, just on the other side. They can be useful to check your data, and see whether data in the right table are missing any associations, where you would expect to find them on the left.
• Right and left joins are the same and can be used in either direction by flipping which table you list first.

On Delete Cascade

• If you delete data from one table, this is how you can automatically delete any data from other tables that depend on what you're deleting.
• Example: if Amazon deletes a book from its database, this would also automatically delete all of that book's customer reviews
• To use this, add ON DELETE CASCADE as part of the foreign key definition. This says delete data in this table when the foreign key is deleted from its table:

CREATE TABLE orders(
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE,
  amount DECIMAL(8,2),
  customer_id INT,
  FOREIGN KEY(customer_id)
    REFERENCES customers(id)
    ON DELETE CASCADE
);

Many to Many

• Many-to-many relationships exist when data can be linked in both ways to multiple other pieces of data; examples: tags & posts, books & authors, students & classes.
• Each piece of data that exists on its own is in its own table; they are connected with a join or union table, which will contain its own data, and references to both of the original data tables.
• References are built the same way they are for one-to-main joins:

-- Independent table
CREATE TABLE reviewers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
);

--Independent table
CREATE TABLE series(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    released_year YEAR(4),
    genre VARCHAR (100)
);

-- Join table (aka union table)
CREATE TABLE reviews(
    id INT AUTO_INCREMENT PRIMARY KEY,
    rating DECIMAL(2,1),
    series_id INT,
    reviewer_id INT,
    FOREIGN KEY(series_id) REFERENCES series(id),
    FOREIGN KEY(reviewer_id) REFERENCES reviewers(id)
);

• When grouping data, best to GROUP BY their reference to primary key (rather than other content like title, name, etc.): it's not guaranteed the content will be forced unique while ids should always be unique.
• To join more than one table, add additional JOIN statements:

SELECT
    title,
    rating,
    CONCAT(first_name, " ", last_name) AS reviewer
FROM series
JOIN reviews
    ON series.id = reviews.series_id
JOIN reviewers
    ON reviews.reviewer_id = reviewers.id
    ORDER BY title;

Displaying Data

• ROUND() can be used to limit the number of decimal places printed: ROUND(AVG(scores), 2) AS avg_score. This would round the averages to two decimal places.

• IF statements: IF(condition, result_if_true, result_if_else): IF(Count(rating) > 0, 'ACTIVE', 'INACTIVE') AS STATUS

SCHEMA DESIGN

top

• If you're not going to reference data from somewhere else, you do not need to give that data an id.

  • For example: likes in an Instagram clone

• Two columns can be set to primary key and the result is that the combination of the two becomes the primary key. This is useful if you want to limit the number of times data can be associated with each other.

  • For example: one like per user, per photo in an Instagram clone

DATABASE TRIGGERS

top

Definition

• Database triggers are events that happen automatically when a specific table is changed.

• Usage examples:

  • Validating data (although you can and should do this on the app side as well)
  • Manipulating other tables based on what happens in this table...useful for logging history

• Syntax:

CREATE TRIGGER trigger_name
  trigger_time trigger_event ON table_name FOR EACH ROW
  BEGIN
    -- do something
  END;

• Components:

  • trigger_time: BEFORE or AFTER
  • trigger_event: INSERT, UPDATE, or DELETE
  • Between BEGIN and END there will be an IF...THEN...END IF statement.
  • Within this statement, the NEW and OLD keywords serve as placeholders for the data that need to be validated.
  • Since the conditional statement requires semi-colons ; to close each line, temporarily change the DELIMITER to $$ (this can be any symbols that won't be used in the trigger statement). At the end, change the delimiter back to ;.
  • MySQL Errors & SQLSTATE: Errors have numeric codes and are MySQL-specific. SQLSTATE codes are standardized across SQL databases. The message is preset and available in the docs. Except SQLSTATE '45000' which is a generic catch-all for user-defined errors; in this case you set the error message with SET MESSAGE_TEXT.

• Example: don't allow users under age 18 to register / add to database:

DELIMITER $$

CREATE TRIGGER must_be_adult
   BEFORE INSERT ON users FOR EACH ROW
   BEGIN
      IF NEW.age < 18
      THEN
        SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Must be an adult!';
      END IF;
   END;
$$

DELIMITER ;

How & When To Use Triggers

• To view the triggers that already exist in the database run SHOW TRIGGERS;
• To delete a trigger run DROP TRIGGER trigger_name;
• Note that triggers can cause problems during debugging: if there is unexpected behavior, if it's caused by a trigger, you won't see it in the normal application or database code.
• Be wary of chaining triggers

top