CREATE DB & TABLE

.

CREATE DATABASE shop;

DROP DATABASE shop;

CREATE TABLE users(
	id INT
);

CREATE TABLE people(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT,
    bio TEXT,
    birth DATE
);

ALTER TABLE people ADD pass TEXT(32); 

ALTER TABLE people DROP COLUMN pass; 

-- Create a new table without the 'pass' column
CREATE TABLE new_people AS
SELECT id, name, email, bio, birth FROM people;

-- Drop the old table
DROP TABLE people;

-- Rename the new table to the original table name
ALTER TABLE new_people RENAME TO people;

ALTER TABLE people CHANGE birth birth DATE NOT NULL; # can't be done in sqlite

INSERT

.

INSERT INTO people (name,  bio, birth, email) VALUES ('Alex', 'some coder', '2050-04-04', 'some@email.com')

INSERT INTO people (name, email, birth)
	VALUES
	('BOB', 'TEST@MAIL.COM', '2050-04-04'),
		('BOB', 'TEST@MAIL.COM', '2050-04-04'),
			('BOB', 'TEST@MAIL.COM', '2050-04-04');

UPDATE people SET name = "Max" WHERE id = 3;

UPDATE people SET name = "Ivan", email="ivan@meial.ru" WHERE name = "Max";

UPDATE people SET bio = "some new text" WHERE name = "Ivan" AND id = 5;

DELETE

.

DELETE FROM test WHERE id = 2;

DELETE FROM test; # deletes everything from the table

TRUNCATE test; # deletes everything from the table

DROP TABLE test; # delete table, itself

Where, Order, Limit

SELECT FROM WHERE ORDER BY LIMIT - порядок подлежит соблюдению

SELECT name, bio FROM people  WHERE id >=  2 AND id < 4;

SELECT id, name, bio FROM people  WHERE id <> 5 AND id <> 2 AND bio IS NULL; # doesn't equal to

SELECT id, name, bio FROM people  WHERE name = 'Alex' OR id =4;

SELECT DISTINCT name FROM people;

SELECT * FROM people LIMIT 2;

SELECT * FROM people ORDER BY id DESC LIMIT 2, 3; # сколько пропускаем, сколько выводим

SELECT * FROM people WHERE id BETWEEN 2 AND 6;

SELECT * FROM people WHERE id IN (2, 6);

SELECT * FROM people WHERE name LIKE 'Iva%';

SELECT * FROM people WHERE name LIKE '%va%';

SELECT * FROM people WHERE name LIKE '%n';

INDEXES

.

CREATE INDEX NIndex ON people(name);

DROP INDEX NIndex ON people;

CREATE TABLE orders(
	id INT NOT NULL,
	orderNumber INT,
	shopId INT,
	personId INT,
	date_time DATETIME DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	FOREIGN KEY(shopId) REFERENCES shop(id),
	FOREIGN KEY(personId) REFERENCES people(id)
);

JOINs

* INNER JOIN — получение записей с одинаковыми значениями в обеих таблицах, т.е. получение пересечения таблиц. * FULL OUTER JOIN — объединяет записи из обеих таблиц (если условие объединения равно true) и дополняет их всеми записями из обеих таблиц, которые не имеют совпадений. Для записей, которые не имеют совпадений из другой таблицы, недостающее поле будет иметь значение NULL. * LEFT JOIN — возвращает все записи, удовлетворяющие условию объединения, плюс все оставшиеся записи из внешней (левой) таблицы, которые не удовлетворяют условию объединения. * RIGHT JOIN — работает точно так же, как и левое объединение, только в качестве внешней таблицы будет использоваться правая. * JOIN CROSS - ??????

SELECT orders.orderNumber, people.name, people.email FROM people
INNER JOIN orders ON people.id = orders.personId
ORDER BY orders.orderNumber DESC;

SELECT shop.title, people.name, people.email FROM people
INNER JOIN orders ON people.id = orders.personId
INNER JOIN shop ON shop.id = orders.shopId
ORDER BY orders.orderNumber DESC;

SELECT people.name, orders.orderNumber FROM people 
LEFT JOIN orders ON people.id = orders.personId
ORDER BY people.name DESC

INSERT INTO second_table
SELECT * FROM first_table
WHERE condition;

AS , GROUP BY, FUNCTIONS

.

SELECT name AS 'Имя', birth AS 'День рождения' FROM people;

SELECT CONCAT ('Имя: ', name, '. День рождения ', birth,'. Почта', email)  AS 'Информация' FROM people;

SELECT p.id, p.name, s.title, s.price FROM people AS p, shop AS s;

SELECT COUNT (id) FROM shop;

SELECT MIN(price) FROM shop; # AVG, SUM;

SELECT UCASE(title) FROM shop;

SELECT price, COUNT(price) AS 'Количество' FROM shop GROUP BY price;

SELECT price, COUNT(price) AS 'Количество' FROM shop GROUP BY price HAVING COUNT(price) > 1;

SELECT email, COUNT(email) FROM customers
GROUP BY email HAVING COUNT(email) > 1; # Поиск дублей

SELECT * FROM workers
WHERE salary > (SELECT AVG (salary) FROM workers);

UPDATE table SET salary =
CASE
WHEN salary = 900 THEN 1000
ELSE 1500
END;