| comment block | | |
| comment line | | backspace after -- |
| comment line | | |
| database create | CREATE DATABASE tutorial;
| |
| database select | | |
| datatype date dayOfWeek | SELECT NOW() - INTERVAL '10 YEAR';
SELECT EXTRACT(DOW FROM NOW());
| |
| datatype date year | SELECT NOW() - INTERVAL '10 YEAR';
SELECT EXTRACT(YEAR FROM NOW());
| |
| datatype decimal limitation, precision | produkt_preis decimal(15,2)
| |
| datatype primary key | id BIGSERIAL NOT NULL PRIMARY KEY
| |
| datatype primary key | id int PRIMARY KEY AUTO_INCREMENT,
| |
| datatype primary key check last | SELECT * FROM person_id_seq;
| |
| datatype primary key check next available | SELECT nextval('person_id_seq'::regclass);
| |
| datatype primary key reset | ALTER SEQUENCE person_id_seq RESTART WITH 1;
| Conflict, existing will not be overwritten |
| datatype String lengthLimitation | | |
| operation if null show | SELECT COALESCE(email, '***NoData***') FROM person;
| |
| operation math | SELECT 10 + 9;
SELECT 10 - 9;
SELECT 10 * 9;
SELECT 10 / 9;
| |
| operation math | | integer calculation |
| operation math | | residual value calculation |
| operation math avg | SELECT AVG(price) FROM Car;
| |
| operation math max | SELECT MAX(price) FROM Car;
| |
| operation math min | SELECT MIN(price) FROM Car;
| |
| operation math range | SELECT * FROM person
WHERE last_name BETWEEN 'A%' AND 'B%';
| |
| operation math round | SELECT ROUND(price) FROM Car;
| |
| operations column create | SELECT *, verkaufspreis - einkaufspreis
as Gewinn FROM produkte;
| temporary column |
| query add | INSERT INTO user (id, firstname, lastname)
VALUES (1, "John" , "Doe")
| if id is not AUTO_INCREMENT |
| query add multiple | INSERT INTO user (firstname, lastname)
VALUES ("John" , "Doe"),("Jane", "Doe")
| |
| query conflicts | ...ON CONFLICT (id) Do NOTHING;
| result = 0
(DEFAULT: SELECT 10 / 0; -> ERROR |
| query conflicts if/else | SELECT COALESCE(10 / NULLIF(0, 0), 0);
| |
| query constraints | ALTER TABLE person ADD CONSTRAINT gender_constraint
CHECK (gender = 'Female' OR gender = 'Male' OR gender = 'Agender');
| |
| query delete by id | DELETE FROM user WHERE id=9;
| |
| query select | SELECT * FROM Customers
WHERE (Country="USA" AND PostalCode Like "9%")
OR (Country="Mexico" AND PostalCode LIKE "0%");
| |
| query select | SELECT * FROM user WHERE id=2;
| |
| query select all | | |
| query select all condition | SELECT * FROM customer WHERE firstname="John";
| |
| query select all condition | SELECT * FROM kunden WHERE land in ("DE", "ES");
| |
| query select case insensitive | SELECT * FROM person WHERE email iLIKE 'H%';
| |
| query select case sensitive | SELECT * FROM person WHERE email iLIKE 'h%';
| |
| query select certain range | SELECT * FROM person FETCH FIRST 10 ROW ONLY;
| |
| query select group | SELECT land FROM kunden group by land;
| |
| query select group agregate | SELECT land, min(lebensalter) FROM kunden
group by land;
| min, max, avg, sum... |
| query select group by | SELECT country_of_birth, COUNT(*)
FROM person GROUP BY country_of_birth;
| |
| query select group by condition | SELECT country_of_birth, COUNT(*)
FROM person
GROUP BY country_of_birth HAVING COUNT(*) > 5;
| |
| query select group distinct | SELECT DISTINCT country_of_birth FROM person;
| |
| query select multiple condition | SELECT * FROM kunden
WHERE nachname like "m%" AND land="ES";
| |
| query select offset | SELECT * FROM person OFFSET 5;
| show > 5 |
| query select order desc | SELECT * FROM kunden
order by nachname DESC;
| asc=default |
| query select String length | SELECT * FROM person
WHERE email LIKE '___h%';
| |
| query select String like | SELECT * FROM kunden
WHERE nachname like "H%";
| any char after H |
| query select String like | SELECT * FROM kunden
WHERE nachname like "%H";
| any char before H |
| query select String like | SELECT * FROM kunden
WHERE nachname like "%H%";
| any char before H and after |
| query update | UPDATE user set lastname="Doe"
WHERE id=1;
| |
| table connect | SELECT * FROM Orders
LEFT JOIN Customers
On Orders.CustomerID = Customers.CustomerID
| |
| table connect 3 tables + calculation | CREATE TABLE ProductOrders AS
SELECT Orderdate, ProductName, Quantity, Price,
Quantity * Price AS Gesamtkostenmenge
FROM Orders
LEFT JOIN OrderDetails
On Orders.OrderID = OrderDetails.OrderID
LEFT JOIN Products
ON OrderDetails.ProductID = Products.ProductID
| |
| table connect calculation & filter | SELECT Count(Quantity * Price) AS Orders, sum(Quantity * Price)
Gesamtkostenmenge, AVG(Quantity * Price) AS Durchschnitt
FROM Orders
LEFT JOIN OrderDetails
On Orders.OrderID = OrderDetails.OrderID
LEFT JOIN Products
ON OrderDetails.ProductID = Products.ProductID
WHERE OrderDate LIKE "1996%"
| |
| table connect intersection | SELECT person.first_name, car.make, car.model, car.price
FROM person
JOIN car ON person.car_id = car.id;
| |
| table delete | | |
| table show | | |