Mysql docker images available at https://hub.docker.com/_/mysql/tags
# download version, 5.7.43
docker pull mysql:5.7.43
# run container exposing port 3306 inside the container
# on port 3309 on host machine. DB clients use 3309 to connect
docker run --name=mysql57-local -p 3309:3306 -e MYSQL_ROOT_PASSWORD=password -d mysql:5.7.43
## copy a backup.sql file into the container.
## The mysql image has a bug with docker volumes
docker cp /backup/file.sql container-name:/home
## drop into the container shell to restore the backup
docker exec -it container-name /bin/bash
## navigate to the folder where you copied your backup
cd /home
## login to mysql
mysql -u root -p
## restore the backup
use database_name;
source file.sql
## now you can play with the database from a mysql clientGet Mysql to update datetime fields automatically.
CREATE TABLE students (
firstname VARCHAR(150),
middlename VARCHAR(150),
lastname VARCHAR(150),
-- created_at is auto populated with current datetime
created_at TIMESTAMP default CURRENT_TIMESTAMP,
-- when record is updated, updated_at is automatically updated
-- with current timestamp
updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Formatting Dates
SELECT CURTIME();
SELECT CURDATE();
SELECT DAYOFWEEK(CURDATE());
SELECT DAYOFWEEK(NOW());
SELECT DATE_FORMAT(NOW(), '%w') + 1;
SELECT DAYNAME(NOW());
SELECT DATE_FORMAT(NOW(), '%W');
SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y');
SELECT DATE_FORMAT(NOW(), '%M %D at %h:%i');SELECT title, released_year FROM books
WHERE released_year <= 2015 AND released_year >= 2004;
SELECT title, released_year FROM books
WHERE released_year BETWEEN 2004 AND 2014;In most cases, mysql can translate a string datetime to a real date time object, and do some maths with it. It is not recommended to rely on this auto detection but use cast or other functions to be precise in calculations.
SELECT * FROM people WHERE birthtime
BETWEEN CAST('12:00:00' AS TIME) AND CAST('16:00:00' AS TIME);
SELECT * FROM people WHERE HOUR(birthtime)
BETWEEN 12 AND 16;Mysql stops evaluating further case statements as soon as it finds one that evaluates to true.
SELECT title, stock_quantity,
CASE
WHEN stock_quantity BETWEEN 0 AND 40 THEN '*'
WHEN stock_quantity BETWEEN 41 AND 70 THEN '**'
ELSE '*****'
END AS stock
FROM books;
-- can be refactored to the below since the first statement to be true is the only one that is used.
SELECT title, stock_quantity,
CASE
WHEN stock_quantity <= 40 THEN '*'
WHEN stock_quantity <= 70 THEN '**'
ELSE '*****'
END AS stock
FROM books;CREATE TABLE contacts (
firstname VARCHAR(100) NOT NULL,
lastname VARCHAR(100) NOT NULL,
-- UNIQUE constraint. No duplicates.
phone VARCHAR(15) NOT NULL UNIQUE,
-- check constraint, age cannot be negative.
age INT CHECK (age > 0),
-- use CONSTRAINT keyword to name a constraint
over18 INT,
CONSTRAINT age_over_18 CHECK (over18 > 18),
-- constraint that uses more than one field
-- firstname and last name must be unique
CONSTRAINT contacts_firstname_lastname UNIQUE (firstname, lastname)
);over() functions without parameters forces the same value to repeat.
SELECT emp_no, department, salary, MIN(salary) OVER(), MAX(salary) OVER() FROM employees;![[Pasted image 20230827083840.png]] See how Min and Max salaries repeat for every department with over() function. The minimum and maximum values are calculated for the entire organisation.
To get a minimum / max salary for each department, you need to specify a partition by department.
SELECT
emp_no,
department,
salary,
AVG(salary) OVER(PARTITION BY department) AS dept_avg,
AVG(salary) OVER() AS company_avg
FROM employees;![[Pasted image 20230827085105.png]]
This now compares a single employee's salary to their department's average and the company average salary.
Sources: