You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Command substitution to list all containers
$(docker container ls -a -q)
# Stop all containers
docker container stop $(docker container ls -a -q)
# Remove all containers
docker container rm $(docker container ls -a -q)
# Remove all images
docker image rm $(docker image ls -a -q)
# Same for volumes and networks
# Remove everything (everything)
docker system prune -af --volumes
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
docker exec -it mysql bash
> mysql --user=root -p
# or
docker run -it \
--link my-local-mysql:mysql \
--rm mysql sh \
-c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'
Remote/Client Connection
Backup/Restore
From docker
# Copies the backup to the host
docker exec -it \
mysql /usr/bin/mysqldump \
-u root --password \
some_application | grep -v "Using a password" > backup.sql
To Docker
# Copies file from host to docker
cat backup.sql | docker exec -i mysql /usr/bin/mysql -u root --password=passwd some_application
mysql -u root -p
set global net_buffer_length=1000000; --Set network buffer length to a large byte number
set global max_allowed_packet=1000000000; --Set maximum allowed packet size to a large byte number
SET foreign_key_checks = 0; --Disable foreign key checking to avoid delays,errors and unwanted behaviour
source somefilename.sql --Import your sql dump file
SET foreign_key_checks = 1; --Remember to enable foreign key checks when procedure is complete!
# Create a named volume container for the data
docker create \
-v /var/lib/postgresql/data \
--name postgres-data \
busybox
# The postgres container using the volume, exposing the port to the host
docker run \
--name postgres \
--restart unless-stopped \
-p 5432:5432 \
-e POSTGRES_PASSWORD=passwd \
-d --volumes-from postgres-data \
postgres:9.6
-- hello queries
SELECT * FROM customers;
-- select columns to display
SELECT
categoryname, description
FROM categories;
-- Distinct values
SELECT DISTINCT region FROM suppliers;
-- count
SELECT COUNT(*) from orders;
SELECT COUNT(DISTINCT productid) from order_details;
-- Combining fields in SELECT
SELECT customerid, shippeddate - orderdate as shiptime from orders;
SELECT unitprice * quantity as total, orderid from order_details;
SELECT with WHERE
-- WHERE with text fields
SELECT * FROM suppliers WHERE city='Berlin';
-- WHERE with numeric fields
SELECT COUNT(*) FROM orders WHERE employeeid = 3;
SELECT COUNT(*) FROM order_details WHERE freight > 250;
-- WHERE with DATE fields
SELECT COUNT(*) FROM orders WHERE shippeddate < '1997-06-05';
-- WHERE with AND
SELECT COUNT(*) FROM orders WHERE shipcountry='Germany' AND freight > 100;
SELECT DISTINCT customerid FROM orders WHERE shipvia = 2 AND shipcountry = 'Brazil';
-- WHERE with OR
SELECT COUNT(*) FROM customers WHERE country = 'USA' OR country='Canada';
SELECT COUNT(*) FROM suppliers WHERE country='Germany' OR country='Spain';
SELECT COUNT(*) FROM orders WHERE shipcountry='USA' OR shipcountry='Brazil' OR shipcountry='Argentina';
-- WHERE with NOT
SELECT COUNT(*) FROM suppliers WHERE NOT country = 'USA';
-- COMBINE WHERE AND OR NOT
SELECT COUNT(*)
FROM orders
WHERE shipcountry = 'Germany' AND (freight < 50 OR freight > 175);
SELECT COUNT(*) FROM orders WHERE
(shipcountry = 'Canada' OR shipcountry='Spain') AND shippeddate > '1997-05-01';
-- WHERE with BETWEEN
SELECT COUNT(*) from order_details WHERE unitprice BETWEEN 10 AND 20;
SELECT COUNT(*) FROM orders WHERE shippeddate BETWEEN '1996-06-01' AND '1996-09-30';
-- WHERE IN
SELECT COUNT(*) FROM suppliers WHERE country IN ('Germany', 'France', 'Spain', 'Italy');
SELECT COUNT(*) FROM products WHERE categoryid IN (1, 4, 6, 7);
SELECT with ORDERBY
SELECT DISTINCT companyname from shippers ORDER BY companyname ASC;
SELECT DISTINCT country, city FROM suppliers ORDER BY country ASC, city ASC;
SELECT productname, unitprice FROM products ORDER BY price DESC, productname ASC;
SELECT with MIN and MAX
SELECT MIN(orderdate) from orders WHERE shipcountry = 'Italy';
SELECT MAX(shippeddate) from orders WHERE shipcountry = 'France';
SELECT MAX(shippeddate - orderdate) FROM orders WHERE shipcountry = 'France';
SELECT with AVG and SUM
SELECT AVG(freight) FROM orders WHERE shipcountry = 'Brazil';
SELECT SUM(quantity) from order_details WHERE productid = 14;
SELECT AVG(quantity) FROM order_details WHERE productid = 35;
SELECT with LIKE
-- % is wildcard for zero or more of any characters
SELECT companyname, contactname from customers WHERE contactname LIKE 'D%';
-- _ is wildcard for one character
SELECT companyname from suppliers WHERE companyname LIKE '_or%';
SELECT companyname from customers where companyname LIKE '%er';
SELECT with aliAS-ed column names
SELECT unitprice * quantity AS TotalSpent FROM order_details ORDER BY TotalSpent DESC;
SELECT unitprice * unitsinstock AS InventoryOnHand, productname FROM products ORDER BY InventoryOnHand DESC;
SELECT with LIMIT
SELECT orderid, unitprice * quantity AS Total FROM order_details ORDER BY Total DESC LIMIT 3;
SELECT productname, unitprice * unitsinstock AS Total FROM products ORDER BY Total ASC LIMIT 2;
SELECT with NULL values
SELECT COUNT(*) FROM customers where region is NULL;
SELECT COUNT(*) FROM suppliers where region is NOT NULL;
SELECT COUNT(*) FROM orders WHERE shipregion IS NULL;
INNER JOIN, a.k.a. JOIN
SELECT companyname, orderdate, shipcountry
FROM customers
JOIN orders ON orders.customerid = customers.customerid;
SELECT CONCAT(firstname, ' ', lastname) as name, orderdate
FROM orders
JOIN employees ON orders.employeeid = employees.employeeid;
SELECT productid, companyname, unitprice, unitsinstock
FROM products
JOIN suppliers ON products.supplierid = suppliers.supplierid;
INNER JOIN for more than 2 tables
SELECT companyname, orderdate, productname, order_details.unitprice, quantity, categoryname
FROM customers
INNER JOIN orders ON orders.customerid = customers.customerid
INNER JOIN order_details ON orders.orderid = order_details.orderid
INNER JOIN products ON products.productid = order_details.productid
INNER JOIN categories ON categories.categoryid = products.categoryid
WHERE categoryname = 'Seafood' AND (order_details.unitprice * quantity) >= 500;
LEFT JOIN
-- All records in the first (left) table and any matching records in the second right table
SELECT companyname, orderid
FROM customers
LEFT JOIN orders ON orders.customerid = customers.customerid
WHERE orderid IS null;
SELECT productname, orderid
FROM products
LEFT JOIN order_details ON order_details.productid = products.productid;
RIGHT JOIN
-- All records in the second (right) table and any matching records in the first(left) table
SELECT companyname, orderid
FROM orders
RIGHT JOIN customers ON orders.customerid = customers.customerid
WHERE orderid IS null;
SELECT customercustomerdemo.customerid, customertypeid, companyname
FROM customercustomerdemo
RIGHT JOIN customers ON customers.customerid = customercustomerdemo.customerid;
FULL JOIN
-- All records from both tables
SELECT companyname, orderid
FROM orders
FULL JOIN customers ON customers.customerid = orders.customerid
WHERE companyname IS NULL or orderID is null;
SELECT productname, categoryname
FROM products
FULL JOIN categories ON categories.categoryid = products.categoryid;
SELF JOIN
-- connect a table back to itself
SELECT C1.companyname, C2.companyname, C1.city
FROM customers C1, customers C2
WHERE C1.city=C2.city AND C1.customerid > C2.customerid;
SELECT s1.companyname, s2.companyname, s1.country
FROM suppliers s1, suppliers s2
WHERE s1.country = s2.country AND s1.supplierid > s2.supplierid;
GROUP BY
-- customers in a country
SELECT country, COUNT(*) as customercount
FROM customers
GROUP BY country
ORDER BY customercount DESC;
-- with JOIN, products in a category
SELECT categoryname, COUNT(*) as categorycount
FROM categories
JOIN products ON products.categoryid=categories.categoryid
GROUP BY categoryname
ORDER BY categorycount DESC;
-- with other aggregate functions
-- Average quantity of a product ordered in order details
SELECT productname, ROUND(AVG(quantity)) as productquantity
FROM products
JOIN order_details ON order_details.productid = products.productid
GROUP BY productname
ORDER BY productquantity DESC;
-- suppliers in each country
SELECT country, COUNT(*) as suppliercount
FROM suppliers
GROUP BY country
ORDER BY suppliercount DESC;
-- total value of each product sold in 1997
SELECT productname, ROUND(SUM(quantity * order_details.unitprice)) as orderdeettotal
FROM order_details
JOIN products ON products.productid = order_details.productid
JOIN orders ON orders.orderid = order_details.orderid
WHERE orderdate BETWEEN '1997-01-01' AND '1997-12-31'
GROUP BY productname
ORDER BY orderdeettotal DESC;
GROUP BY with HAVING
WHERE filters records before grouping
HAVING filters records after grouping
-- products selling less than 2000
SELECT productname, ROUND(SUM(quantity * order_details.unitprice)) as orderdeettotal
FROM order_details
JOIN products ON products.productid = order_details.productid
JOIN orders ON orders.orderid = order_details.orderid
GROUP BY productname
HAVING ROUND(SUM(quantity * order_details.unitprice)) > 2000
ORDER BY orderdeettotal DESC;
-- customers bought more than 5000
SELECT companyname, ROUND(SUM(quantity * order_details.unitprice)) as total
FROM customers
JOIN orders ON orders.customerid = customers.customerid
JOIN order_details ON order_details.orderid = orders.orderid
GROUP BY companyname
HAVING ROUND(SUM(quantity * order_details.unitprice)) > 5000
ORDER BY total DESC;
ORDER BY orderdeettotal DESC;
-- customers bought more than 5000 in the first 6 months
SELECT companyname, ROUND(SUM(quantity * order_details.unitprice)) as total
FROM customers
JOIN orders ON orders.customerid = customers.customerid
JOIN order_details ON order_details.orderid = orders.orderid
WHERE orders.orderdate BETWEEN '1997-01-01' AND '1997-06-30'
GROUP BY companyname
HAVING ROUND(SUM(quantity * order_details.unitprice)) > 5000
ORDER BY total DESC;
UNION
Combines the results of two or more queries
Must have the same number of columns
Types of columns must match
-- List of all supplier and company names
SELECT companyname
FROM customers
UNION
SELECT companyname
FROM suppliers;
SELECT country
FROM customers
UNION
SELECT country
FROM suppliers
ORDER BY country ASC
-- cities of customers and suppliers
SELECT city
FROM customers
UNION
SELECT city
FROM suppliers
ORDER BY city ASC;
-- VS with UNION ALL
SELECT city
FROM customers
UNION ALL
SELECT city
FROM suppliers
ORDER BY city ASC;
Subquery with EXISTS
SELECT records from a table WHERE conditions based on records in a different table
-- get customers with orders placed within a certain date
SELECT companyname
FROM customers
WHERE EXISTS (
SELECT customerid
FROM orders
WHERE orders.customerid=customers.customerid
AND orderdate BETWEEN '1997-04-01' AND '1997-04-30'
);
-- get customers that DID NOT have an order in a certain date
SELECT companyname
FROM customers
WHERE NOT EXISTS (
SELECT customerid
FROM orders
WHERE orders.customerid=customers.customerid
AND orderdate BETWEEN '1997-04-01' AND '1997-04-30'
);
-- What products did not have an order in april 1997
SELECT productname
FROM products
WHERE NOT EXISTS (
SELECT productid
FROM order_details
JOIN orders ON orders.orderid = order_details.orderid
WHERE order_details.productid = products.productid
AND orderdate BETWEEN '1997-04-01' AND '1997-04-30'
);
-- All suppliers with a product that costs more than $200
SELECT companyname
FROM suppliers
WHERE EXISTS (
SELECT supplierid
FROM products
WHERE products.supplierid = suppliers.supplierid AND unitprice > 200
);
Subquery with ANY
-- Find customers with order detail with more than 50 items of a single product
SELECT companyname
FROM customers
WHERE customerid = ANY (
SELECT customerid
FROM orders
JOIN order_details ON order_details.orderid = orders.orderid
WHERE quantity > 50
);
-- Find suppliers that had an order with 1 item
SELECT companyname
FROM suppliers
WHERE supplierid = ANY (
SELECT supplierid
FROM products
JOIN order_details ON order_details.productid = products.productid
WHERE quantity = 1
);
Subquery with ALL
-- products that had order amounts that were higher than the average of all products
SELECT DISTINCT productname
FROM products
JOIN order_details ON order_details.productid = products.productid
WHERE (order_details.unitprice * order_details.quantity) > ALL (
SELECT AVG(order_details.unitprice * order_details.quantity) FROM order_details GROUP BY productid
);
-- DISTINCT customers that ordered more of one item than the average order amount per item of all customers
SELECT DISTINCT companyname
FROM customers
JOIN orders ON orders.customerid = customers.customerid
JOIN order_details ON order_details.orderid = orders.orderid
WHERE (order_details.unitprice * order_details.quantity) > ALL (
SELECT AVG(order_details.unitprice * quantity)
FROM order_details
JOIN orders on orders.orderid = order_details.orderid
GROUP BY customerid
);
Subquery with IN
-- customers in the same countries as suppliers
SELECT companyname
FROM customers
WHERE country IN (
SELECT country from suppliers
);
-- suppliers in the same city as a customer
SELECT companyname
FROM suppliers
WHERE city IN (
SELECT city from customers
);
INSERT
INSERT INTO orders
(customerid, employeeid, orderdate, requireddate, shipvia, freight,
shipname, shipaddress, shipcity, shippostalcode, shipcountry)
VALUES ('VINET', 4, '2017-09-05', '2017-09-25', 3, 43.4,
'Vins et alcools Chevalier', '59 rue de l'' Abbaye', 'Reims', '51100', 'France');
INSERT INTO order_details
(orderid, productid, unitprice, quantity, discount)
VALUES (
11078, 11, 14, 20, 0
);
UPDATE
UPDATE orders
SET requireddate = '2017-09-20', freight=50
WHERE orderid = 11078;
UPDATE order_details
SET quantity=40, discount=.05
WHERE orderid=11078 AND productid=11;
DELETE
DELETE FROM order_details
WHERE orderid=11078 AND productid=11;
DELETE FROM orders WHERE orderid=11078;
SELECT INTO
SELECT * INTO suppliers_northamerica
FROM suppliers
WHERE country IN ('USA', 'CANADA');
SELECT * INTO orders_1997
FROM orders
WHERE orderdate BETWEEN '1997-01-01' AND '1997-12-31';
INSERT INTO SELECT
INSERT INTO suppliers_northamerica
SELECT * FROM suppliers
WHERE country IN ('Argentina', 'Brazil');
INSERT INTO orders_1997
SELECT * FROM orders
WHERE orderdate BETWEEN '1996-12-01' AND '1996-12-31';
ALTER TABLE subscribers
RENAME firstname TO first_name;
ALTER TABLE returns
RENAME datereturned to date_returned;
ALTER TABLE, rename table
ALTER TABLE subscribers
RENAME TO email_subscribers;
ALTER TABLE, change field type
ALTER TABLE email_subscribers
ALTER COLUMN email SET DATA TYPE varchar(255);
ALTER TABLE returns
ALTER COLUMN quantity SET DATA TYPE int;
CREATE Index
CREATE INDEX email_email_subscribers
ON email_subscribers (email);
CREATE INDEX customerid_orderid_returns
ON returns(customerid, orderid);
DROP INDEX
DROP INDEX email_email_subscribers;
DROP INDEX customerid_orderid_returns;
DROP TABLE
DROP TABLE returns;
Constraints
NOT NULL - Field must have a value
UNIQUE - Field value must not be the same in a different record
PRIMARY KEY - Must have value and be unique, identifier for the record
FOREIGN KEY - All values must exist in another table
CHECK - Checks that all values meet condition
DEFAULT - If no value is provided, value is set to the default
NOT NULL
DROP TABLE IF EXISTS practices;
CREATE TABLE practices (
practiceid integer NOT NULL,
practice_field VARCHAR(50) NOT NULL
);
-- ALTER TABLE syntax
ALTER TABLE products
ALTER unitprice set NOT NULL;
UNIQUE
DROP TABLE IF EXISTS practices;
CREATE TABLE practices (
practiceid integer UNIQUE,
practice_field VARCHAR(50) NOT NULL
);
CREATE TABLE pets (
petid integer UNIQUE,
name VARCHAR(25) NOT NULL
);
ALTER TABLE region
ADD CONSTRAINT regiondescription_region UNIQUE (regiondescription);
ALTER TABLE shippers
ADD CONSTRAINT companyname_shippers UNIQUE (companyname);
PRIMARY KEY
DROP TABLE IF EXISTS practices;
CREATE TABLE practices (
practiceid integer PRIMARY KEY,
name varchar(25) NOT NULL
);
ALTER TABLE practices
DROP CONSTRAINT practices_pkey -- called [table name]_pkey by default
ALTER TABLE practices
ADD PRIMARY KEY (practiceid);
FOREIGN KEY
DROP TABLE IF EXISTS practices;
CREATE TABLE practices (
practiceid integer PRIMARY KEY,
practicefield VARCHAR(48) NOT NULL,
employeeid integer NOT NULL,
FOREIGN KEY (employeeid) REFERENCES employees (employeeid)
);
ALTER TABLE practices
DROP CONSTRAINT practices_employeeid_fkey;
ALTER TABLE practices
ADD CONSTRAINT practices_employeeid_fkey
FOREIGN KEY (employeeid) REFERENCES employees (employeeid);
CHECK
DROP TABLE IF EXISTS practices;
CREATE TABLE practices (
practiceid integer PRIMARY KEY,
practicefield VARCHAR(48) NOT NULL,
employeeid integer NOT NULL,
cost INTEGER CONSTRAINT practices_cost CHECK (cost >= 0 AND cost <= 1000),
FOREIGN KEY (employeeid) REFERENCES employees (employeeid)
);
ALTER TABLE orders
ADD CONSTRAINT orders_freight CHECK (freight > 0);
DEFAULT
DROP TABLE IF EXISTS practices;
CREATE TABLE practices (
practiceid integer PRIMARY KEY,
practicefield VARCHAR(48) NOT NULL,
employeeid integer NOT NULL,
cost INTEGER DEFAULT 50 CONSTRAINT practices_cost CHECK (cost >= 0 AND cost <= 1000),
FOREIGN KEY (employeeid) REFERENCES employees (employeeid)
);
ALTER TABLE products
ALTER COLUMN reorderlevel
SET DEFAULT 5;
docker exec postgres /bin/bash \
-c "export PGPASSWORD=passwd \
&& /usr/bin/pg_dump -U postgres northwind" \
| gzip -9 > northwind.backup.sql.gz
gunzip < northwind.backup.sql.gz | \
docker exec -i postgres /bin/bash \
-c "export PGPASSWORD=passwd && \
/usr/bin/psql -U postgres northwind2"
-- list users
SELECT u.usename AS "User Name" FROM pg_catalog.pg_user u;
-- READONLY
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE ims_api_prod TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
-- READ/WRITE
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE ims_api_prod TO readwrite;
GRANT USAGE ON SCHEMA public TO readwrite;
GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO readwrite;
-- READ/WRITE USER
CREATE USER arumery WITH PASSWORD 'somepass';
GRANT readwrite TO arumery;
Output table info
SELECT
c.relname, a.attname AS column_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
case
when a.attnotnull
then 'NOT NULL'
else 'NULL'
END as not_null
FROM pg_class c,
pg_attribute a,
pg_type t
WHERE c.relname = 'prototype_vps_sync_archive'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
ORDER BY a.attnum
Table Sizes
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;
DISTINCT with COUNT()
SELECT
name,
COUNT(name) AS name_count
FROM
public.dealer_option
GROUP BY
name;
{
"parser": "@typescript-eslint/parser",
"parserOptions": {
"ecmaVersion": 2020,
"sourceType": "module"
},
"extends": [
"plugin:@typescript-eslint/recommended",
"prettier/@typescript-eslint",
"plugin:prettier/recommended"
],
"rules": {
// Place to specify ESLint rules. Can be used to overwrite rules specified from the extended configs// e.g. "@typescript-eslint/explicit-function-return-type": "off",
}
}
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
