mysql fundamentals

Creating Database Tables

Data Definition Language (DDL) is a SQL subset for creating databases and tables
most databases will have a GUI to create
still good to know how to do it manually, especially for scripting

CREATE DATABASE

not part of SQL standard, but is supported by most implementations
USE DATABASE to scope future queries to a particular database
can also fully qualify table name to database
- can lead to performance enhancement
CREATE DATABASE Contact;
USE DATABASE Contact; SELECT * FROM person p;
- all future queries will be in this database
SELECT * FROM Contact.person p;
- fully qualified table name

CREATE TABLE

part of SQL standard
followed by table name, then list column definitions
- at minimum column name and type
CREATE TABLE email_address (email_address_id INTEGER, email_address_person_id INTEGER, email_address VARCHAR(55);
Standard datatypes:

Data Type	Value Space
CHARACTER	can hold N character values, set N statically
CHARACTER VARYING	can hold N character values, set N dynamically, can be less than N
BINARY	hexadecimal data
SMALLINT	-2E15 to 2E15-1
INTEGER	-2E31 to 2E31-1
BINGINT	-2E63 to 2E62-1
BOOLEAN	true / false
DATE	year, month, and day in format YYYY-MM-DD
TIME	hour, minute, and second in format HH:MM:SS[.sF]
TIMESTAMP	both date and time

NULL values

NULL is a special value
indicates a lack of a value
columns can be required
if required, then is NOT NULL
if optional, then is NULL

NULL	NOT NULL
default	must be specified
inserting NULL value ok	inserting NULL value results in error

CREATE TABLE email_address (email_address_id INTEGER NOT NULL, email_address_person_id INTEGER, email_address VARCHAR(55) NOT NULL;

PRIMARY KEY

must have a unique value per row
cannot be NULL
can be multiple columns (compound key)
- query one or more columns
- not using an auto-incremented value
- each individual column is not unique, but when combined is
CREATE TABLE email_address (email_address_id INTEGER PRIMARY KEY, email_address_person_id INTEGER, email_address VARCHAR(55) NOT NULL;

CONSTRAINT

way to add keys in one grouping
primary or foreign keys

CREATE TABLE phone_number
(
phone_number_id
INTEGER NOT NULL,
phone_number_person_id
INTEGER NOT NULL,
phone_number
VARCHAR(55) NOT NULL,
CONSTRAINT
Pk_phone_number // can name whatever you want, convention to have Pk for primary key and Fk for foreign key
PRIMARY KEY
(phone_number_id)
);

ALTER TABLE

used to change an existing table
add/remove column
change column data types or constraints
must work with current data in the table
- cannot change from NULL to NOT NULL if there are null values

ALTER TABLE
email_address
ADD CONSTRAINT
FK_email_address_person
FOREIGN KEY
(email_address_person_id)
REFERENCES // requires a REFERENCES statement to point back to the location of the key
person
(person_id);

DROP TABLE

removes a table and all data from database
CAREFUL
will throw an error if table is a foreign key to another table
DROP TABLE person

Adding, Changing, and Removing Data

INSERT

INSERT INTO is actual command
table name after command
only one table at a time
list of columns in parens, followed by VALUES keyword with a list of values in parens
numbers of values and columns must be same
`INSERT INTO person (person_id, first_name, last_name, contacted_number, date_last_contacted, date_added) VALUES (1, 'Jon', 'Flanders', 0, NULL, '2016-05-14 11:43:31');

BULK INSERT

INSERT allows only one table and column list
but you can insert multiple rows with one statement
eithe rmultiple values lists or
SELECT statement following table name
- inserting into one table by selecting one or more other tables
- useful for creating a JOIN table for fast querying
INSERT INTO person p SELECT * FROM old_person op WHERE op.person_id > 300'
- the SELECT statement column list will turn into the column list for the INSERT INTO statement
- values from the SELECT will turn into the VALUES for the INSERT INTO, not needed to state

UPDATE

modifies one or more columns in a single table
WHERE dictates which rows
SET keyword follows table name
UPDATE email_address e SET e.email_address = 'aaron@mail.com' WHERE e.email_address_id = 5;
- without a WHERE clause, you would set all these rows to that value

DELETE

deletes one or more rows in a table, permanent
DELETE FROMis actual full command
WHERE clause is critical, can end up deleting entire table
- some databases have protections against and require WHERE clause
DELETE FROM person p WHERE p.id = 5;

matching data tables with JOIN

merge multiple tables into one result set
FROM clause includes all tables
seprated by commas
WHERE is typically included
expression with columns from each table
different kinds of joins

CROSS JOIN

simplest join
retrieves all rows of both tables
no WHERE clause, least useful, inefficient
creates a Cartesian Product
CROSS JOIN is implied, do not have to type
SELECT p.first_name, e.email_address FROM person p email_address e;

INNER JOIN

most typical JOIN
emphasizes relatinoal nature of database
matches column in first table to second
Primary key to foreign key is most common
will only get data that is a match between the two columns where ON is specified
SELECT p.first_name, p.last_name, e.email_address FROM person p INNER JOIN email_address e ON p.person_id = e.email_address_person_id;
- between these two tables, relate ON these two columns

OUTER JOIN

INNER JOIN does not deal with NULL values
OUTER JOIN works even when no match
NULL columns if no match in second table
FULL OUTER JOIN returns all joined rows but will show NULL when there is no match between the tables selected rows
OUTER keyword is optional

LEFT OUTER JOIN

NULL related JOIN
all the rows from the left side will be returned
NULL for non-matching right side table rows
SELECT p.first_name, p.last_name, e.email_address FROM person p LEFT OUTER JOIN email_address e ON p.person_id = e.email_address_person_id;
- if there is not a match in the right hand table, please still return the rows

RIGHT OUTER JOIN

opposite of LEFT OUTER JOIN
all rows from the right side will be returned
NULL from non-matching left side of table
SELECT p.first_name, p.last_name, e.email_address FROM person p RIGHT OUTER JOIN email_address e ON p.person_id = e.email_address_person_id;
- very unusual to use, as most databases should not get into a state where there are email addresses without people

FULL OUTER JOIN

essentially a merge of the RIGHT and LEFT OUTER JOINS
mySQL does not support
SELECT p.first_name, p.last_name, e.email_address FROM person p FULL OUTER JOIN email_address e ON p.person_id = e.email_address_person_id;

SELF JOIN

can JOIN a table to itself
odd but useful, no special syntax
same table on left and right side of JOIN
useful when table contains hierarchical data
works in a recursive fashion on itself

Shaping results with ORDER BY and GROUP BY

ORDER BY

allows sorting of result set
after WHERE clause (if it exists)
specify one or more columns
ASC (default) or DESC

set functions

compute new values from column values
use in place of columns in SELECT clause
passes column name to function
help with more complex logic
often used with DISTINCT

function	what does
COUNT	count of column specified (includes NULL if * is used)
MAX	max value of column (no NULL)
MIN	min value of column (no NULL)
AVG	average value of column (no NULL, only numeric)
SUM	sum of all the values of column (no NULL, only numeric)

Set functions + qualifiers

often used together
added inside the function
run against DISTINCT column values
SELECT COUNT(DISTINCT p.first_name) FROM person p;

GROUP BY

allows multiple columns with a set function
break result set into subsets
runs set function against each subset
result set retruns 1 row per subset
subset is dictated by column in GROUP BY
column being grouped must appear in the SELECT LIST
appears after FROM and / or WHERE clases
SELECT COUNT(p.first_name), p.first_name FROM person p GROUP BY p.first_name;

HAVING

works like WHERE works against SELECT
restrict the subset
SELECT COUNT(DISTINCT p.first_name), p.first_name FROM person p GROUP BY p.first_name HAVING COUNT(DISTINCT p.first_name) >= 5;

WHERE clause filtering

how to constrain the result set
comes after the FROM clause
contains boolean expressions
only matching rows are in the result set
SELECT last_name FROM person p WHERE p.first_name = 'jon';

Boolean Operators

op	meaning
<>	! equal to
>	greater than
<	less than
>=	greater or equal
<=	less or equal
=	equal

AND keyword

chains multiple expressions together
if both expressions are true, the row is included
if either are false, row is excluded
SELECT last_name FROM person p WHERE p.first_name = 'jon' AND p.birthdate > '12/31/1965';

OR keyword

combines two expressions
if either are true, row is included
if both are false, row is excluded
SELECT last_name FROM person p WHERE p.first_name = 'jon' OR p.last_name = 'Flanders';

BETWEEN

acts on a column and two values
true if column value is between two values
inclusive of stated values
SELECT last_name FROM person p WHERE p.contacted BETWEEN 1 AND 20;

LIKE

more fuzzy version of equals
spring with special characters inside for matching
- % is wildcard
if match is true, returns row
SELECT last_name FROM person p WHERE p.first_name LIKE 'J%';

like a multi-value equals operator
lists potential values
true if any of the values in the list "hit"
SELECT last_name FROM person p WHERE p.first_name IN p.first_name IN ('Jon', 'Fritz');

a special operator
like equals, but just for values that might be NULL
SELECT p.first_name, p.last_name FROM person p WHERE p.last_name IS NULL;

IS NOT

also just for NULL
like a not equals

SELECT statement querying

is a question about the data
simplest query
- Select + select clause
- SELECT 'Hello', 'World' selects the lists 'Hello' and 'World'

SELECT list

most of the time it contains a list of columns from a table you want to query
a FROM clause is then required
every column is separated by a comma
no comma after the last column name
SELECT <COLUMN_NAME>, <COLUMN_NAME> FROM <TABLE_NAME>;
* wildcard select list gives all columns from table
- bad practice, better to be explicit

FROM clause

defines a table you want to query
instead of SELECT first_name, last_name FROM person; do SELECT person.first_name, person.last_name FROM person;
- table qualify the names of columns (some DB will query faster)
you can alias a table name: SELECT p.first_name, p.last_name FROM person p

Constrain Results

add a WHERE clause
use DISTINCT qualifier
- selects only unique first names: SELECT DISTINCT p.first_name FROM person p;
- NOT DISTINCT is default
- will refer to unique in all columns added, not just the first column named after the DISTINCT statement

Basic SQL syntax

sql statement is an expression that tells a database what you want it to do
statements must end in ; semicolon

SELECT

retrieves one or more row from one or more tables
- can work against multiple tables
SELECT person_name, person_last_name FROM person;