MySQL Course Notes


Cloud 9 MySQL Commands
  • Start MySQL, this will create an empty database on first start
    mysql-ctl start
  • Stop MySQL
    mysql-ctl stop
  • Run MySQL interactive shell
    mysql-ctl cli
To create a database
CREATE DATABASE <name>;
To delete a database
DROP DATABASE <name>;
To use a database
USE <name>;
To show which database is in use
SELECT DATABASE();
MySQL Numeric Data Types
TINYINT( )
-128 to 127 normal 0 to 255 UNSIGNED.
SMALLINT( )
-32768 to 32767 normal 0 to 65535 UNSIGNED.
MEDIUMINT( )
-8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
INT( )
-2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT( )
-9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
FLOAT
A small approximate number with a floating decimal point.
DOUBLE( , )
A large number with a floating decimal point.
DECIMAL( , )
A DOUBLE stored as a string , allowing for a fixed decimal point. Choice for storing currency values.
MySQL Text Data Types
CHAR( )
A fixed section from 0 to 255 characters long.
VARCHAR( )
A variable section from 0 to 255 characters long.
TINYTEXT
A string with a maximum length of 255 characters.
TEXT
A string with a maximum length of 65535 characters.
BLOB
A string with a maximum length of 65535 characters.
MEDIUMTEXT
A string with a maximum length of 16777215 characters.
MEDIUMBLOB
A string with a maximum length of 16777215 characters.
LONGTEXT
A string with a maximum length of 4294967295 characters.
LONGBLOB
A string with a maximum length of 4294967295 characters.
MySQL Date/Time Data Types
DATE
YYYY-MM-DD
DATETIME
YYYY-MM-DD HH:MM:SS
TIMESTAMP
YYYYMMDDHHMMSS
TIME
HH:MM:SS
MySQL Other Data Types
ENUM
To store text value chosen from a list of predefined text values
SET
This is also used for storing text values chosen from a list of predefined text values. It can have multiple values.
BOOL
Synonym for TINYINT(1), used to store Boolean values
BINARY
Similar to CHAR, difference is texts are stored in binary format.
VARBINARY
Similar to VARCHAR, difference is texts are stored in binary format.
To create a table
CREATE TABLE tablename
(
<column_name> <data_type>,
<column_name> <data_type>
);
Example:
CREATE TABLE cats
(
name VARCHAR(100),
age INT
);
To show tables within a database
SHOW TABLES;
To show columns of a table
SHOW COLUMNS FROM <tablename>;
Short version:
DESC <tablename>;
To delete a table
DROP TABLE <tablename>;
To insert into a table
INSERT INTO <table_name>(<column_name>, ...) VALUES (<data>, ...);
Example:
INSERT INTO cats(name, age) VALUES ('kitty', 3);
* NOTE you can insert out of order, example:
INSERT INTO cats(age, name) VALUES (3, 'kitty');
To check your insert > get all records in the table
SELECT * FROM <tablename>;
To insert multiple records into table
INSERT INTO <table_name>(<column_name>, ...)
VALUES
(<data>, ...),
(<data>, ...),
(<data>, ...);
To show what happened when you get a warning use
SHOW WARNINGS;
NULL Column
KEY Column
DEFAULT Column
EXTRA Column
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(20) | YES  |     | NULL    |       |
| last_name  | varchar(20) | YES  |     | NULL    |       |
| age        | int(11)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
							
NULL Column: To prevent a table from having null specify NOT NULL
CREATE TABLE tablename
(
<column_name_1> <data_type> NOT NULL,
<column_name_2> <data_type> NOT NULL
);
Example:
CREATE TABLE cats
(
name VARCHAR(100) NOT NULL,
age INT NOT NULL
);
DEFAULT Column: To make sure a table inserts default values when one isn't given use DEFAULT
CREATE TABLE tablename
(
<column_name_1> <type> DEFAULT <value_of_type>,
<column_name_2> <type> DEFAULT <value_of_type>
);
Example:
CREATE TABLE cats
(
name VARCHAR(100) DEFAULT 'unnamed',
age INT DEFAULT 99
);
PRIMARY KEY: To create a table with a PRIMARY KEY
CREATE TABLE tablename
(
<column_name_1> <data_type> NOT NULL,
<column_name_2> <data_type>,
<column_name_3> <data_type>,
PRIMARY KEY (column_name_1)
);
Example:
CREATE TABLE cats
(
cat_id INT NOT NULL
name VARCHAR(100),
age INT,
PRIMARY KEY (cat_id)
);
EXTRA: To auto increment column
CREATE TABLE tablename
(
<column_name_1> <data_type> NOT NULL AUTO_INCREMENT,
<column_name_2> <data_type>,
<column_name_3> <data_type>,
PRIMARY KEY (column_name_1)
);
Example:
CREATE TABLE cats
(
cat_id INT NOT NULL AUTO_INCREMENT
name VARCHAR(100),
age INT,
PRIMARY KEY (cat_id)
);
CRUD Commands
Four basic actions how we want to manipulte our data
Create
Read
Update
Delete
To select or update a specific record or range of records use the WHERE clause
SELECT * FROM <table_name> WHERE <column_name>=<value>;
Example:
SELECT * FROM cats WHERE age=4;
To display aliases use AS keyword
SELECT <column_name> AS <new_name> FROM <table_name>;
Example
SELECT name AS 'cat name', breed AS 'kitty breed' FROM cats;
To update records in a table use UPDATE and SET command
A good rule is before you UPDATE run a corresponding SELECT commands to target the records you are going to update
UPDATE <table_name> SET <column_name>=<'new_value'>
WHERE <column_name>=<'current_value'>;
Example
UPDATE cats SET breed='Shorthair' WHERE breed='Tabby';
To DELETE records use DELETE
DELETE FROM <table_name> WHERE <column_name>=<value>;
Example
DELETE FROM cats WHERE name='egg';
To input MySQL Commands from a file run SOURCE command
SOURCE /<path_to_file>/<file_name>
CONCAT Command, to combine two strings together
SELECT CONCAT(column01, column02, ...) FROM <table_name>;
Example
SELECT CONCAT(author_fname, ' ', author_lname) FROM books;
This will output each column plus add a space between them
CONCAT_WS Command, Same as CONCAT, where you can predefine the separator passed as first argument
SELECT CONCAT_WS(<separator>, column01, column02, ...) FROM <table_name>;
Example
SELECT CONCAT_WS(' - ', author_fname, author_lname, book_id) FROM books;
SUBSTRING Command, to print a portion of string with a staring index of 1 to length of string
NOTE you can pass 1 value or 2
If you pass 1 value it will start at that index and print from that point forward
If you pass 2 values it print that portion of the string from index01 to index02
You can also pass a negative value but ONLY one negative value and it print from the start index backwards
SELECT SUBSTRING(<column>, value1, value2) FROM <table_name>;
Example
SELECT CONCAT(SUBSTRING(title,1,10),'...') AS 'Short Title' FROM books;
Sample Output
+---------------+
| Short Title   |
+---------------+
| The Namesa... |
| Norse Myth... |
| American G... |
...
								
REPLACE Command, to replace a portion of the string
This will replace all found instances
This is a case sensitive function
SELECT REPLACE(<column>, <string_to_replace>, <string_to_replace_with>) FROM <table_name>;
Example
SELECT REPLACE(title, 'o', 0) FROM books;
+-----------------------------------------------------+
| REPLACE(title, 'o', 0)                              |
+-----------------------------------------------------+
| The Namesake                                        |
| N0rse Myth0l0gy                                     |
| American G0ds                                       |
								
REVERSE Command, to reverse a string
SELECT REVERSE(<string_to_reverse>) FROM <table_name>;
Example
SELECT REVERSE(author_fname) FROM books;
+-----------------------+
| REVERSE(author_fname) |
+-----------------------+
| apmuhJ                |
| lieN                  |
								
CHAR_LENGTH to count number of characters in string
SELECT CHAR_LENGTH(<string_to_count>) FROM <table_name>;
Example
SELECT author_lname, CHAR_LENGTH(author_lname) FROM books;
+----------------+---------------------------+
| author_lname   | CHAR_LENGTH(author_lname) |
+----------------+---------------------------+
| Lahiri         |                         6 |
| Gaiman         |                         6 |
| Gaiman         |                         6 |
								
To send text to UPPER or LOWER case user UPPER() or LOWER()
SELECT UPPER(author_lname), LOWER(author_lname) FROM books;
To only get unique records use DISTINCT
SELECT DISTINCT <column> FROM <table>;
Example:
SELECT DISTINCT author_lname FROM books;
To get DISTINCT from mutiple columns, example lets say you want DISTINCT from first_name and last_name columns
SELECT DISTINCT CONCAT(author_fname,' ', author_lname) AS 'full name' FROM books;
More Simple way:
SELECT DISTINCT author_fname, author_lname FROM books;
To sort a query use ORDER BY
Note ORDER BY is in ascending, which you can state explicitly with ASC, order by default alphanumeric starting with numbers
SELECT <column> FROM <table> ORDER BY <column>;
Example
SELECT author_lname FROM books ORDER BY author_lname;
To reverse ascending order us DESC
Example descending
SELECT author_lname FROM books ORDER BY author_lname DESC;
ORDER BY shortcut to specify which column you want to sort by use number at end of ORDER BY
SELECT <column1>, <column2>, <column3> FROM <table> ORDER BY 2;
Example
SELECT title, author_fname, author_lname FROM books ORDER BY 2 DESC;
To sort by more than one column specify multiple columns when using ORDER BY
SELECT <column1>, <column2>, <column3> FROM <table> ORDER BY <column2>, <column1>;
Example
SELECT title, author_fname, author_lname FROM books ORDER BY author_lname, author_fname;
To limit the number of our result use LIMIT
Example you could use LIMIT to get top 3 results
SELECT <column1>, <column2> FROM <table> ORDER BY <column2> DESC LIMIT 5;
Example
SELECT title, released_year FROM books ORDER BY 2 DESC LIMIT 5;
You can also use limit by specifying a start point for example you want top 5 but start at 5 entry, which really is saying I want lower 5 results of top 10
SELECT <column1>, <column2> FROM <table> ORDER BY <column2> DESC LIMIT 5,5;
Example
SELECT title, released_year FROM books ORDER BY 2 DESC LIMIT 5,5;
To search for item based on a few characters use LIKE and percent sign %, the percent sign is a wild card attribute
Example to search for author that Da, like David, Dan, Dave, Darryl, etc
SELECT <column1>, <column2> FROM <table> WHERE author_fname LIKE '<value>%'
Example
SELECT title, author_fname FROM books WHERE author_fname LIKE 'da%';
Other wild cards for LIKE '_' underscore
Example to get values with only 4 digits
SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '____';
+----------------------+----------------+
| title                | stock_quantity |
+----------------------+----------------+
| Lincoln In The Bardo |           1000 |
+----------------------+----------------+
							
Example to get values with only 3 or digits
SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '___%';
+-------------------------------------------+----------------+
| title                                     | stock_quantity |
+-------------------------------------------+----------------+
| A Hologram for the King: A Novel          |            154 |
| A Heartbreaking Work of Staggering Genius |            104 |
| Coraline                                  |            100 |
| Oblivion: Stories                         |            172 |
| fake_book                                 |            287 |
| Lincoln In The Bardo                      |           1000 |
+-------------------------------------------+----------------+
							
In case you want to search for % or _ in your data you can escape the wild cards by using backslash \ '%\%%'
Example to get title with % in title
SELECT title FROM books WHERE LIKE '\%%';
+-------------+
| title       |
+-------------+
| 10% Happier |
+-------------+			
							
Example to get title with _ in title
SELECT title FROM books WHERE LIKE '\_%';
+-----------+
| title     |
+-----------+
| fake_book |
+-----------+			
							
What is an Aggregate Function(s)
Aggregate Function will take more than one data element and combine into whole result
To count number of records in a table use COUNT()
SELECT COUNT(*) FROM <table>;
Example
SELECT COUNT(*) FROM books;
To show a unique count records based on a column we can use DISTINCT with COUNT
Example this shows all the first names but there are duplicate first names not necessarily records
This can be problematic because this does not show unique authors see DISTINCT author_lname
We can add both author_fname and author_lname to COUNT with DISTINT
SELECT COUNT(author_fname) AS author_fname FROM books;
+--------------+
| author_fname |
+--------------+
|           19 |
+--------------+
							
SELECT COUNT(DISTINCT author_fname) AS author_fname FROM books;
+--------------+
| author_fname |
+--------------+
|           12 |
+--------------+
							
SELECT COUNT(DISTINCT author_lname) AS author_lname FROM books;
+--------------+
| author_lname |
+--------------+
|           11 |
+--------------+
							
SELECT COUNT(DISTINCT author_fname, author_lname) AS author_fname FROM books;
+--------------+
| author_fname |
+--------------+
|           12 |
+--------------+
							
GROUP BY - Summarizes or aggregates identical data into single rows.
It groupls a set of rows into a set of summary rows.
It will return only one row per group
You can put more than one value for GROUP BY

SELECT <column>, COUNT(*) FROM <table> GROUP BY <column>;
Example
SELECT author_fname, COUNT(*) FROM books GROUP BY author_fname;
Because we only grouping by author_lname in our example we could have multiple authors with same last name which would give incorrect count
Here is a better example where we specify more than one value for GROUP BY and we are sorting:
SELECT CONCAT(author_fname, ' ', author_lname) AS author, COUNT(CONCAT(author_fname, ' ', author_lname)) AS number_books FROM books GROUP BY author_fname, author_lname ORDER BY author_lname;
To get maximum or minimum items from a table use MAX or MIN
SELECT MIN(<column>) FROM books;
Or
SELECT MAX(<column>) FROM books;
Concept of Subqueries, we can embeded a subquery in a given query in order to get the most correct result.
Example if we try the following from a books table we will likely not get the correct result:
SELECT title, MAX(pages) FROM books;
+--------------+------------+
| title        | MAX(pages) |
+--------------+------------+
| The Namesake |        634 |
+--------------+------------+
							
Example of getting just title the namesake with pages
SELECT title, pages FROM books WHERE title LIKE '%the namesake%';
+--------------+-------+
| title        | pages |
+--------------+-------+
| The Namesake |   291 |
+--------------+-------+
							
Notice pages for the namesake is 291
SELECT tile, pages FROM books WHERE pages=634;
+-------------------------------------------+-------+
| title                                     | pages |
+-------------------------------------------+-------+
| The Amazing Adventures of Kavalier & Clay |   634 |
+-------------------------------------------+-------+
							
We can fix this by using a subquery, Example:
SELECT title, pages FROM books WHERE pages=(SELECT MAX(pages) FROM books);
+-------------------------------------------+-------+
| title                                     | pages |
+-------------------------------------------+-------+
| The Amazing Adventures of Kavalier & Clay |   634 |
+-------------------------------------------+-------+
							

Unfortunately this is an expensive operation because we are using two SELECT statement for one query.
We can try the following to get same result and should be much faster:
SELECT title, pages FROM books ORDER BY pages DESC LIMIT 1;
Here is an example of where we can find each authors first released book
SELECT CONCAT(author_fname, ' ', author_lname) AS author, MIN(released_year) AS year FROM books GROUP BY author_fname, author_lname ORDER BY author_lname;
To add up a number of columns use SUM
SELECT SUM(<column>) FROM <table>;
Example
SELECT SUM(pages) FROM books;
Example more advanced list how many pages has the most pages written
SELECT CONCAT(author_fname, ' ', author_lname) AS author, SUM(pages) AS 'total pages written' FROM books GROUP BY author_fname, author_lname ORDER BY author_lname;
To find the average of values use AVG
SELECT AVG(<column>) FROM <table>;
Example
SELECT AVG(released_year) FROM books;
Example more advanced showing average number of pages each author wrote
SELECT CONCAT(author_fname, ' ', author_lname) AS author, AVG(pages) AS 'total pages written' FROM books GROUP BY author_fname, author_lname ORDER BY author_lname;
CHAR datatype, CHAR is a fixed data type it right pads with space when stored
Example if you declare CHAR(10) but insert value of 'abc' it will store as 'abc       '
When retrieving a CHAR column MySQL will remove trailing spaces unless PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled
CHAR is faster for fixed length text, example use would be State abbreviations, Yes No flags, Sex M F, etc
Decimal data type, to create a column with decimal use DECIMAL(#,#)
DECIMAL data type takes two arguments, the first argument is TOTAL number of digits the decimal can have BEFORE and AFTER decimal and the second argument is number of digits to the right of the DECIMAL
NOTE: when you attempt to insert a number larger that you specified MySQL will only insert the larget number allowed, Example:
ColumnaA is DECIMAL(5,2) and you insert value of 7987654, you select ColumnA back you will get 9999.99 which is the maximum allowed for 4 digits of whole and 2 digits of precision
For date and time you can use the following DATE, TIME, and DATETIME
DATE is format 'YYYY-MM-DD'
Time is format 'HH:MM:SS'
DATETIME is format 'YYYY-MM-DD HH:MM:SS'
To get current time date and date time use CURTIME(), CURDATE(), and NOW()
Examples:
SELECT CURTIME(); > 04:27:05
SELECT CURDATE(); > 2018-08-19
SELECT NOW(); > 2018-08-19 04:27:10
Date and Time formatting reference
Various logical operators
AND can be used interchangeably with &&
OR can be used interchangeably with ||
= is equals
> is greater than
< is less than
You can combine >= to make comparision inclusive same for <=
!= is not equal to
NOT negates the statement
BETWEEN to compare two ranges inclusive
BETWEEN is equivalent to comparing two item ranges, Example:
100 >= 5 AND 100 <= 200
IN operator checks if in a list
Long way:
SELECT <column1>, <column2>
FROM <table>
WHERE <column1>='<value1>' OR
<column1>='<value2>' OR
<column1>='<value3>;
Using IN operator you can compare to a list
SELECT <column1>, <column2>
FROM <table>
WHERE <column1> IN ('<value1>', '<value2>', '<value3>');
CASE statements, basically if else statements
SELECT <column1>, <column2>,
    CASE
      WHEN <column1> >= <value1> THEN 'OtherValue1'
      ELSE 'OtherValue2'
    END AS <NewColumnName>
FROM <table>;
Example:
SELECT title, released_year,
    CASE
      WHEN released_year >= 2000 THEN 'Modern Lit'
      ELSE 'OtherValue2'
    END AS '20th Century Lit'
FROM Genre;
Foreign Keys
A foreign key is basically a unique key from another table that ties current table to a Primary key from another table
INNER JOIN - this allows you to SELECT data from two tables based upon a primary and foreign key
Implicit Inner Join
SELECT * FROM <table01>, <table02> WHERE <table01>.<column01> = <table02>.<column02>
Example
SELECT * FROM customers, orders WHERE customers.id = orders.customer_id;
Explicit Inner Join
SELECT * FROM <table01>
    JOIN <table02>
    ON <table01>.<column01> = <table02>.<column02>
Example
SELECT * FROM customers
    JOIN orders
    ON customers.id = orders.customer_id;
LEFT JOIN - this allows you to take the intersection of two tables plus all of the left table based upon a primary and foreign key
In the example on the right NOTE that ALL of the table01 left of "LEFT JOIN" statement will be included plus the intersection of table01 and table02
SELECT * FROM <table01>
    LEFT JOIN <table02>
    ON <table01>.<column01> = <table02>.<column02>
Example
SELECT * FROM customers
    LEFT JOIN orders
    ON customers.id = orders.customer_id;
RIGHT JOIN - this allows you to take the intersection of two tables plus all of the left table based upon a primary and foreign key
In the example on the right NOTE that ALL of the table01 right of "RIGHT JOIN" statement will be included plus the intersection of table01 and table02
***NOTE: that if you swap table01 with table02 to the left and right of the RIGHT JOIN you are essentially mimicking the output of the LEFT JOIN above. Its important to understand the table placement to the left and right of the join call.
SELECT * FROM <table01>
    RIGHT JOIN <table02>
    ON <table01>.<column01> = <table02>.<column02>
Example
SELECT * FROM customers
    RIGHT JOIN orders
    ON customers.id = orders.customer_id;
To format the SELECT state in a list of field=value use \G at end of statement minus semicolon ;
SELECT * from table\G