The MySQL Book

Notes on how to use the database

How to install and connect to the database via Docker

// (1) Download the latest version of MySQl from the Docker hub
debugme@desktop ~> docker pull mysql/mysql-server:latest

// (2) Start up a container based on the downloaded MySQL image
debugme@desktop ~> docker run --name=mysqlbox -d mysql/mysql-server:latest

// (3) Find out what the root password is for MySQL
debugme@desktop ~> docker logs mysqlbox 2>&1 | grep GENERATED

// (4) Log into MySQL on the container (Use root password when prompted)
debugme@desktop ~> docker exec -it mysqlbox mysql -u root -p

// (5) Change root password to one of your choice (e.g. 'password')
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

// (6) Exit the container
mysql> \q

// (7) Log into MySQL on the container (Use your new password when prompted)
debugme@desktop ~> docker exec -it mysqlbox mysql -u root -p

// (8) How to login with a shell onto the MySQL docker container and then connect to mysql
debugme@desktop ~> docker exec -it sh
sh-4.2# mysql -u root -p

// (9) How to install an editor in the MySQL docker container
debugme@desktop ~> docker exec -it sh
sh-4.2# yum install vim

How to clear mysql screen from within a tmux pane

ctrl l

How to see what databases are installed on your database server

msql> show databases;

How to create a database

mysql> create database sandbox;

How to delete a database

mysql> drop database sandbox;

How to specify the active database

mysql> use sandbox;

How to find out what the active database is

mysql> select database();

How to see what tables are in your database

mysql> show tables;

How to see what columns are in a table

mysql> show columns from myTable;

How to get a description of a table

mysql> describe myTable;
mysql> desc myTable;

How to delete a table

mysql> drop table if exists myTable

How to create a table

mysql> create table myTable(name varchar(50), age int);

How to insert rows into a table

mysql> insert into myTable(name, age) values('jack', 15), ('jill', 16);

How to get data from a table

mysql> select * from myTable;

How to see what warnings you get when running a command

mysql> show warnings;

How to see what errors you get when running a command

mysql> show errors;

How to make a column required

mysql> create table myTable(
mysql>   name varchar(50) not null, 
mysql>   age int not null
mysql> );

How to disallow null as a value for a column

mysql> create table myTable(
mysql>   name varchar(50) not null default '?', 
mysql>   age int not null default 1
mysql> );

How to create an auto-incrementing primary key on a table

mysql> create table myTable(
mysql>   id int not null auto increment primary key,
mysql>   name varchar(80) not null default 'xXx'
mysql> );

How to update a column value

mysql> update myTable
mysql> set name = "Jackson"
mysql> where name = "Jack";

How to use string functions

mysql> select concat(author_fname, ' ', author_lname) from books;
mysql> select concat_ws('.', title, author_fname, author_lname) from books;
mysql> select substring(author_lname, 1, 3) from books;
mysql> select replace(title, 'Norse', 'Viking') from books;
mysql> select reverse(title) from books;
mysql> select title, char_length(title) as length from books;
mysql> select upper(author_lname), lower(author_fname) from books;

How to filter selections

// return all distinct combinations of first name and last name
mysql> select distinct author_fname, author_lname from books;

// order by ascending author last name
mysql> select author_lname from books 
mysql> order by author_lname asc;

// order by descending author last name
mysql> select author_lname from books 
mysql> order by author_lname desc;

// order by column 2 i.e. author_fname
mysql> select title, author_fname, author_lname from books
mysql> order by 2;

// order by author lastname and then by author first name
mysql> select author_fname, author_lname from books 
mysql> order by author_lname, author_fname;

// return all queries that match title having the word stories in it
mysql> select title from books
mysql> where title like '%stories%';

// return all queries that match stock quantity having exactly 4 characters in it
mysql> select title, stock_quantity from books
mysql> where stock_quantity like '____';

How to use aggregate functions

// how many rows are in the books table
mysql> select count(*) from books;

// how many distinct authors are there (2 ways, same result)
mysql> select count(distinct author_lname, author_fname) from books;
mysql> select count(distinct(concat(author_fname,author_lname))) from books;

// group by concatenation of last name and first name
mysql> select author_fname, author_lname, count(title) from books 
mysql> group by author_lname, author_fname;

// find title of book with most pages using a sub-query
mysql> select title from books 
mysql> where pages = (select max(pages) from books);

// find the earliest year an author was published using min
mysql> select min(released_year), author_lname, author_fname from books 
mysql> group by author_lname, author_fname;

// find the latest year an author was published using max
mysql> select max(released_year), author_lname, author_fname from books 
mysql> group by author_lname, author_fname;

// how to find average number of pages written per author
mysql> select author_lname, author_fname, avg(pages) from books 
group by author_lname, author_fname;

// how to get just the top five books sorted by title
mysql> select * from books order by title limit 5;

How can I think about group by?

(1) If you group by one or more columns, then you can use those column names directly in the select clause, but you cannot refer to any other columns in that select clause, unless you use them in conjunction with an aggregation function.

(2) When you group by a column with possible duplicate values, imagine subtables are being created that allow you to perform aggregation functions on each subtable.

select author_lname, author_fname, pages from books group by author_lname, author_fname;

Using pages in the select clause violates the rule we just explained so this query is invalid 😖

select author_lname, author_fname, sum(pages) from books group by author_lname, author_fname;

Using pages within the sum aggregation function follows the rule so this query is valid 🥳

How to use different datatypes

mysql> create table people(
mysql>   name varchar(80),
mysql>   gender char(1),
mysql>   birthdate date,
mysql>   birthtime time,
mysql>   birthdatetime datetime,  
mysql>   created_at timestamp default now(),
mysql>   last_updated timestamp default now() on update current_timestamp
mysql> );

mysql> insert into people(name, gender, birthdate, birthtime, birthdatetime)
mysql> values('bob', 'm', '2020-05-21', '10:34:07', '2020-05-21 10:34:07'),
mysql>       ('sue', 'f', curdate(), curtime(), now());

mysql> select * from people;

How to source a file from within the mysql shell

// Put this in "/home/a.sql"
use books;
select * from books;

// Run the file above by sourcing it from within the mysql shell
mysql> source /home/a.sql

Different date and time functions

mysql> select curdate();        // returns the current date
mysql> select curtime();        // returns the current time
mysql> select now();            // returns the current datetime

How to model a 1:N relationship using primary and foreign keys

mysql> use shopping;

mysql> drop table if exists customers;
mysql> drop table if exists orders;

mysql> create table customers(
mysql>   id int auto_increment primary key,
mysql>   name varchar(100)
mysql> );

mysql> create table orders(
mysql>   id int auto_increment primary key,
mysql>   order_date date,
mysql>   payment decimal(8, 2),
mysql>   customer_id int,
mysql>   foreign key(customer_id) references customers(id)
mysql> );

How select records from both tables where join condition is met (i.e. inner join)

mysql> select first_name, last_name, order_date, amount 
mysql> from customers join orders
mysql> on customers.id = orders.customer_id;

How to select records from left table where join condition is met (i.e. left join)

// Get all records from left table (i.e. customers) and join them with their 
// matching records from right table (i.e. orders) and where no match found in 
// right table, replace nulls returned with a zero (i.e. sum(amount))

mysql> select
mysql>   first_name,
mysql>   last_name,
mysql>   ifnull(sum(amount), 0) as total_spent
mysql> from customers
mysql> left join orders
mysql>   on customers.id = orders.customer_id
mysql> group by customers.id
mysql> order by total_spent;

How to select records from right table where join condition is met (i.e. right join)

// Get all records from right table (i.e. orders) and join them with their 
// matching records from left table (i.e. customers) and where no match found in 
// left table, replace nulls returned with a string

mysql> select
mysql>   ifnull(first_name, 'missing'),
mysql>   ifnull(last_name, 'user'),
mysql>   sum(amount) as total_spent
mysql> from customers
mysql> right join orders
mysql>   on customers.id = orders.customer_id
mysql> group by customers.id
mysql> order by total_spent;

How to automatically delete record in child table when matching record in parent table is deleted

mysql> use shopping;

mysql> drop table if exists customers;
mysql> drop table if exists orders;

mysql> create table customers(
mysql>   id int auto_increment primary key,
mysql>   name varchar(100)
mysql> );

mysql> create table orders(
mysql>   id int auto_increment primary key,
mysql>   order_date date,
mysql>   payment decimal(8, 2),
mysql>   customer_id int,
mysql>   foreign key(customer_id) references customers(id) on delete cascade
mysql> );

How to create a composite primary key

// Disallow more than one like by a user for the same photo by creating a 
// composite primary key from the user id and photo id
mysql> create table likes(
mysql>   primary key(user_id, photo_id),
mysql>   user_id int not null,
mysql>   foreign key(user_id) references users(id) on delete cascade,
mysql>   photo_id int not null,
mysql>   foreign key(photo_id) references photos(id) on delete cascade,
mysql>   created_at timestamp default now()
mysql> );