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

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;

 CREATE TABLE table_name (
  column1 datatype constraint,
  column2 datatype constraint,
  column3 datatype constraint,
  ....
 );

CREATE TABLE new_table_name AS
   SELECT column1, column2,...
   FROM existing_table_name
   WHERE ....;

DROP TABLE table_name;
TRUNCATE TABLE table_name;

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

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

SELECT - extracts data from a database | SELECT column-name | FROM table-name
UPDATE - updates data in a database | UPDATE tablename | SET some-column = some-value | WHERE some-column = some-value
DELETE deletes data from a database | DELETE FROM table-name | WHERE some-column = some-value
INSERT INTO -inserts new data into a database | INSERT INTO tablename (column 1, column 2, column 3) | VALUES (value 1, value 2, value 3)
CREATE DATABASE - creates a database | CREATE DATABASE databasename;
ALTER DATABASE - modifies a database | ALTER DATABASE databasename | [COLLATE collation-name]
CREATE TABLE - creates a new table | CREATE TABLE tablename ( | column 1 datatype, | column 2 datatype, | column 3 datatype, | );
ALTER TABLE -modifies a table | ALTER TABLE tablename | ADD columnname datatype;
DROP TABLE - deletes a table | DROP TABLE tablename
CREATE INDEX - creates an index | CREATE INDEX indexname | ON tablename (column name 1, column name 2,…);
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 | +————–+———-+———+

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 |
  +--------------------+

- ```sql
   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;`

- ```sql
   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`

- ```sql
   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;
  ```

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

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

select "Launch_Site" from SPACEXTBL GROUP BY "Launch_Site"

Display 5 records where launch sites begin with the string ‘CCA’

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

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

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

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%"

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"

select "Date" from SPACEXTBL where "Landing_Outcome" = "Success (ground pad)" and "Mission_Outcome" = "Success" order by "Date" limit 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

List the total number of successful and failure mission outcomes

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

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)

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)"

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

docs

a slatepencil documentail site