SQL/PostgreSQL/MySQL Epic Reference

Reference

November 20, 2022


Here is a reference for the SQL & database commands I’ve learned, in its own post for easy reference. This is a mix of class notes and random notes I’ve kept in different places.

TABLE OF CONTENTS

Basic PostgreSQL Commands

top

From Main Command Line

  • brew info postgresql gives some information about the postgres installation
  • psql opens the postgres command line as the super user

User Management

  • \du lists all users
  • CREATE ROLE username WITH LOGIN PASSWORD 'password' CREATEDB; creates a new user, requires a password, and gives permission to create databases.
  • ALTER ROLE username CREATEDB; gives/removes permissions for the user
  • DROP USER username; deletes the user and their permissions

Database Management

  • \l lists all databases
  • \c dbname username moves you into using the database to access its tables as the specified user (username is optional)

Table Management

  • \dt lists all tables
  • \d tablename lists columns in a table
  • TABLE tablename; displays table data contents
  • NOTE: if tablename includes capital letters, it needs to go in quotes (\d "Tablename" or TABLE "Tablename";)

Other

  • \? lists all the available postgres command line commands
  • \q quits the postgres command line

Basic MySQL Commands

top

Server 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

  • 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>; permanently deletes the table and its contents
  • 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.

UUIDs

  • Universally Unique Identifiers (UUID) are generated by an algorithm chosen to make it very unlikely that the same identifier will be generated by anyone else in the known universe using the same algorithm. Therefore, for distributed systems, these identifiers provide a better uniqueness guarantee than sequence generators, which are only unique within a single database.
  • By default Postgres uses UUID v4

INTEGER

  • A whole number
  • INTEGER, 4 bytes, Range -2147483648 to +2147483647
  • SMALLINT, 2 bytes, Range -32768 to +32767
  • BIGINT, 8 bytes, Range -9223372036854775808 to +9223372036854775807

SERIAL

  • Auto-incrementing integers, always used for primary keys.
    • SMALLSERIAL : 1 to 32,767
    • SERIAL : 1 to 2147483647
    • BIGSERIAL : 1 to 9223372036854775807

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

Booleans

  • TRUE - True, 1, t, Y, yes, on
  • FALSE - False, 0, f, N, no, off
  • NULL

Dates & Times

  • DATE stores only a date in the format YYYY-MM-DD
  • TIME stores only a time in the format HH:MM:SS
  • TIMEWITHOUTTIMEZONE is a Postgres-specific date format
  • 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

LEARNING SOURCES & REFERENCE

top

Tags icon Stacked price tag looking things Tags: