MYSQL

docker-compose

version: "3.8"
services:
  mysql:
    image: mysql:8.0
    cap_add:
      - SYS_NICE
    restart: always
    command:
      [
        "--default-authentication-plugin=mysql_native_password",
        "--character-set-server=utf8mb4",
        "--collation-server=utf8mb4_unicode_ci",
        "--transaction-isolation=READ-COMMITTED",
        "--binlog-format=ROW",
      ]
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
      interval: 3s
      retries: 5
      start_period: 30s
    environment:
      - MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD
      - MYSQL_DATABASE=$MYSQL_DATABASE
      - MYSQL_USER=$MYSQL_USER
      - MYSQL_PASSWORD=$MYSQL_PASSWORD
    ports:
      - "3306:3306"
    volumes:
      - /path/to/db:/var/lib/mysql
      - ./db/init.sql:/docker-entrypoint-initdb.d/init.sql
docker-compose up -d mysql
docker exec -it mysql /bin/bash
#once inside the container
mysql -uroot -p
#put/paste the password, and once inside MySQL CLI run
show databases;

MyISAM vs InnoDB

Attribute MyISAM InnoDB
Locking Table-level Row-level
designed for need of speed high volume of data
foreign keys × (DBMS) ✓ (RDBMS)
transaction ×
fulltext search ×
scene lots of select lots of insert/update
count rows fast slow
auto_increment fast slow

JDBC timezones

Database dumps

docker exec mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql

Database restore

mysql> create database mydb;
mysql> use mydb;
mysql> source db_backup.dump;

DB Operater

mysql --user=username --password=password dbname

mysql -u username -p database_name < file.sql

MySQL Constraints

Date

MySQL Data Types

MySQL Functions

GENERAL OPERATIONS

  1. SELECT - extracts data from a database | SELECT column-name | FROM table-name

  2. UPDATE - updates data in a database | UPDATE tablename | SET some-column = some-value | WHERE some-column = some-value

  3. DELETE deletes data from a database | DELETE FROM table-name | WHERE some-column = some-value

  4. INSERT INTO -inserts new data into a database | INSERT INTO tablename (column 1, column 2, column 3) | VALUES (value 1, value 2, value 3)

  5. CREATE DATABASE - creates a database | CREATE DATABASE databasename;

  6. ALTER DATABASE - modifies a database | ALTER DATABASE databasename | [COLLATE collation-name]

  7. CREATE TABLE - creates a new table | CREATE TABLE tablename ( | column 1 datatype, | column 2 datatype, | column 3 datatype, | );

  8. ALTER TABLE -modifies a table | ALTER TABLE tablename | ADD columnname datatype;

  9. DROP TABLE - deletes a table | DROP TABLE tablename

  10. CREATE INDEX - creates an index | CREATE INDEX indexname | ON tablename (column name 1, column name 2,...);

  11. DROP INDEX - deletes an index | ALTER TABLE tablename | DROP INDEX indexname

Examples

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

The COUNT() function returns the number of rows that matches a specified criterion The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column.

The percent sign (%) represents zero, one, or multiple characters The underscore sign () represents one, single character_

INNER JOIN: Returns records that have matching values in both tables LEFT JOIN: Returns all records from the left table, and the matched records from the right table RIGHT JOIN: Returns all records from the right table, and the matched records from the left table CROSS JOIN: Returns all records from both tables

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

INSERT INTO SELECT statement copies data from one table and inserts it into another table

Real example

[
  {
    "Date": "2010-06-04",
    "Time (UTC)": "18:45:00",
    "Booster_Version": "F9 v1.0  B0003",
    "Launch_Site": "CCAFS LC-40",
    "Payload": "Dragon Spacecraft Qualification Unit",
    "PAYLOAD_MASS__KG_": 0,
    "Orbit": "LEO",
    "Customer": "SpaceX",
    "Mission_Outcome": "Success",
    "Landing_Outcome": "Failure (parachute)"
  },
  {
    "Date": "2010-12-08",
    "Time (UTC)": "15:43:00",
    "Booster_Version": "F9 v1.0  B0004",
    "Launch_Site": "CCAFS LC-40",
    "Payload": "Dragon demo flight C1, two CubeSats, barrel of Brouere cheese",
    "PAYLOAD_MASS__KG_": 0,
    "Orbit": "LEO (ISS)",
    "Customer": "NASA (COTS) NRO",
    "Mission_Outcome": "Success",
    "Landing_Outcome": "Failure (parachute)"
  },
  {
    "Date": "2012-05-22",
    "Time (UTC)": "7:44:00",
    "Booster_Version": "F9 v1.0  B0005",
    "Launch_Site": "CCAFS LC-40",
    "Payload": "Dragon demo flight C2",
    "PAYLOAD_MASS__KG_": 525,
    "Orbit": "LEO (ISS)",
    "Customer": "NASA (COTS)",
    "Mission_Outcome": "Success",
    "Landing_Outcome": "No attempt"
  }
]

QUESTIONS

  1. Display the names of the unique launch sites in the space mission

select "Launch_Site" from SPACEXTBL GROUP BY "Launch_Site"

  1. Display 5 records where launch sites begin with the string 'CCA'

select * from SPACEXTBL WHERE "Launch_Site" LIKE "%CCA%" LIMIT 5

  1. Display the total payload mass carried by boosters launched by NASA (CRS)

select SUM("PAYLOAD_MASS__KG_") from SPACEXTBL where "Customer" = "NASA (CRS)"

  1. Display average payload mass carried by booster version F9 v1.1

select AVG("PAYLOAD_MASS__KG_") from SPACEXTBL where "Booster_Version" LIKE "%F9 v1.1%"

  1. List the date when the first succesful landing outcome in ground pad was acheived

select MIN("Date") from SPACEXTBL where "Landing_Outcome" = "Success (ground pad)" and "Mission_Outcome" = "Success"

or

select "Date" from SPACEXTBL where "Landing_Outcome" = "Success (ground pad)" and "Mission_Outcome" = "Success" order by "Date" limit 1

  1. List the names of the boosters which have success in drone ship and have payload mass greater than 4000 but less than 6000

select "Booster_Version" from SPACEXTBL where "Landing_Outcome" is "Success (drone ship)" and "PAYLOAD_MASS__KG_" between 4000 and 6000

  1. List the total number of successful and failure mission outcomes

select COUNT(*) as "Mission", "Mission_Outcome" FROM SPACEXTBL GROUP BY "Mission_Outcome"

  1. List the names of the booster_versions which have carried the maximum payload mass. Use a subquery

select "Booster_Version" FROM SPACEXTBL where "PAYLOAD_MASS__KG_" = (select MAX("PAYLOAD_MASS__KG_") from SPACEXTBL)

  1. List the records which will display the month names, failure landing_outcomes in drone ship ,booster versions, launch_site for the months in year 2015

select substr(Date, 6,2), "Landing_Outcome", "Booster_Version", "Launch_Site" as "Month" FROM SPACEXTBL where substr(Date,0,5)='2015' and "Landing_Outcome" = "Failure (drone ship)"

  1. Rank the count of landing outcomes (such as Failure (drone ship) or Success (ground pad)) between the date 2010-06-04 and 2017-03-20, in descending order.

select COUNT(*) as "Mission", "Landing_Outcome" FROM SPACEXTBL where "Date" between "2010-06-04" and "2017-03-20" group by "Landing_Outcome" order by "Mission" DESC

SQL Aggregate Functions

Wildcard Characters

Symbol Description
% Represents zero or more characters
_ Represents a single character
[] Represents any single character within the brackets *
^ Represents any character not in the brackets *
- Represents any single character within the specified range *
{} Represents any escaped character **

** Supported only in Oracle databases.

DB transaction

connection.beginTransaction((error) => {
  if (error) throw error

  const sql = "INSERT INTO users (name, age) VALUES ?"
  const values = [['John Doe', 30]]

  connection.query(sql, [values], (error) => {
    if (error) {
      connection.rollback()
      throw error
    }

    connection.commit((error) => {
      if (error) {
        connection.rollback()
        throw error
      }
    })
  })
})

Page Source