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

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

• method 1: docker exec -i mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /some/path/on/your/host/all-databases.sql

• method 2:

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

• SQL keywords are NOT case sensitive
• CREATE DATABASE databasename;
• DROP DATABASE databasename;
• sql CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, .... ); - sql CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_name WHERE ....;
• DROP TABLE table_name;
• TRUNCATE TABLE table_name;
• sql ALTER TABLE table_name ADD column_name datatype; - sql ALTER TABLE table_name DROP COLUMN column_name;
• sql ALTER TABLE table_name MODIFY COLUMN column_name datatype;

MySQL Constraints

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly

Date

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
• YEAR - format YYYY or YY

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

• SELECT * FROM Customers;
• SELECT COUNT(id) AS NumberOfRecords FROM Ambient; +-----------------+ | NumberOfRecords | +-----------------+ | 617493 | +-----------------+
• SELECT DISTINCT type,hardware,unit FROM Ambient; +--------------+----------+---------+ | type | hardware | unit | +--------------+----------+---------+ | temperature | DHT11 | celsius | | humidity | DHT11 | percent | | pressure | BMP180 | pascal | | altitude | BMP180 | meter | | sealevel | BMP180 | pascal | | temperature2 | BMP180 | celsius | +--------------+----------+---------+
• sql SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;

• sql SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;

• sql SELECT column1, column2, ... FROM table_name WHERE NOT condition;

• sql SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

• sql INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

• sql SELECT column_names FROM table_name WHERE column_name IS NULL;

• sql UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

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

• sql DELETE FROM table_name WHERE condition; DELETE FROM table_name; // removes all records

• sql SELECT column_name(s) FROM table_name WHERE condition LIMIT number;

• SELECT MIN(value) FROM Ambient WHERE type = 'temperature'; +--------------------+ | MIN(value) | +--------------------+ | 14.100000381469727 | +--------------------+

• SELECT MAX(column_name)
  FROM table_name
  WHERE condition;
  

• sql SELECT COUNT(column_name) FROM table_name WHERE condition;

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.

• sql SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;

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

• sql SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

• sql SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

• sql SELECT column_name AS alias_name FROM table_name;

• SELECT CustomerName, CONCAT_WS(', ', Address, PostalCode, City, Country) AS Address FROM Customers;

• SELECT o.OrderID, o.OrderDate, c.CustomerName
  FROM Customers AS c, Orders AS o
  WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;
  

• sql SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

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

• sql SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

• The UNION operator is used to combine the result-set of two or more SELECT statements.

  • Every SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in every SELECT statement must also be in the same order
  • The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL

• SELECT column_name(s) FROM table1
  UNION
  SELECT column_name(s) FROM table2;
  

• sql SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

• sql SELECT column_name(s) COUNT(columan_name.type) AS NumberOfTyeps FROM table_name WHERE condition GROUP BY column_name(s)

• sql SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

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

• sql SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

• sql SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);

• sql SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);

• sql INSERT INTO table2 SELECT * FROM table1 WHERE condition;

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

• sql SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN 'The quantity is greater than 30' WHEN Quantity = 30 THEN 'The quantity is 30' ELSE 'The quantity is under 30' END AS QuantityText FROM OrderDetails;

• sql SELECT CustomerName, City, Country FROM Customers ORDER BY (CASE WHEN City IS NULL THEN Country ELSE City END);

• sql SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;

• sql SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;

• sql ALTER TABLE tablename MODIFY fieldname date not null;

• sql ALTER TABLE tablename CHANGE field_before field_after datetime not null;

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

• MIN() - returns the smallest value within the selected column
• MAX() - returns the largest value within the selected column
• COUNT() - returns the number of rows in a set
• SUM() - returns the total sum of a numerical column
• AVG() - returns the average value of a numerical column

Wildcard Characters

• Not supported in PostgreSQL and MySQL databases.

** 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
      }
    })
  })
})