SQL.cpp

Начало работы {
    MySQL {
        // Команды применяемые извне MySQL по отношению к базе данных.
        //Вход
        mysql -u root -p // для запуска MySQL с корневым пользователем. Далее нужно ввести пароль.
        mysql -u root -p myPassword //  есть и такой вариант. Сразу пишется пароль.

        // Команды применяемые внутри MySQL
        quit // выход из базы данных
        exit // также выход из базы данных
    }
    Postgres {
        Пользователь по умолчанию: postgres
        Пароль устанавливается при установке

        psql -U postgres // войти в консоль postgres под корневым пользователем
		
		psql -U postgres -h <hostname or ip> -p <port> -d <database> // remote connect

        Если появляется ПРЕДУПРЕЖДЕНИЕ: "Кодовая страница консоли (866) отличается от основной страницы Windows (1251)". {
        	Полный текст: {
        		ПРЕДУПРЕЖДЕНИЕ: Кодовая страница консоли (866) отличается от основной
        					страницы Windows (1251).
        					8-битовые (русские) символы могут отображаться некорректно.
        					Подробнее об этом смотрите документацию psql, раздел
        					"Notes for Windows users".
        	}
        	Исправление: {
        		До входа в postgres написать в консоли "chcp 1251" или другой код, в зависимости от кодировки
        	}
        }
    }
}
Дамп (MySQL) {
    // Импорт дампа
    mysql -u root -p database_name < путь_до_дампа// для загрузки дампа. База данных должна уже существовать

    // Создание дампа
    mysqldump -uroot -ppassword database_name > путь_до_дампа // выгрузка дампа в файл.
}
Слеш команды в Postgres {
	\l // список БД
	\c databaseName // подключиться к БД
    \dn // показать список схем
    \dt // показать список таблиц
	\d tableName // описать таблицу
}

Работа с базами данных {

	SHOW DATABASES; { // Показыввает Список баз данных
		Mysql: 
			show databases;
		Postgres:
		    \list { // показать список баз данных
				\l // синоним
			}
			
	}
	CREATE DATABASE myDatabaseName; // создать базу данных
	CREATE DATABASE [IF NOT EXISTS] имя_базы_даных
	CREATE DATABASE IF NOT EXISTS Skillbox // форма команды, которая создаёт базу, если её ещё нет.

	CREATE SCHEMA schemaName // создать схему
	DROP DATABASE  myDatabaseName; // удаление базы данных

	DROP DATABASE [IF EXISTS] имя_базы_даных; // удалить базу данных

	USE DATABASE <database name>  { // Подключиться к БД
		MySQL:
			USE DATABASE <database name> // 
		Postgres:
			\connect myDatabase { 
				\c myDatabase //  синоним
			}
	}
	
}

Работа с таблицами {
	
	SHOW TABLES; { // показать ссписок таблиц в БД
		MySQL:
			SHOW TABLES;
		Postgres:
			\dt
	}
	CREATE {
		CREATE TABLE название_таблицы (
			название_столбца1 тип_данных атрибуты_столбца1,
			название_столбца2 тип_данных атрибуты_столбца2,
			..............................
			название_столбцаN тип_данных атрибуты_столбцаN,
			атрибуты_уровня_таблицы
		)
		
		Типы данных {
			Mysql {
				INT
				VARCHAR
				VARCHAR(20)
				...
			}
			Postgres {			
				CHARACTER VARYING 	// строковый тип
				CHARACTER VARYING 	// то же самое, но с указанием ораничения по числу символов
				varchar 			// синоним для строки. Получается всё равно CHARACTER VARYING
				varchar(50)			// то же с ограничением
				INTEGER				// целочисленный
				int					// то же самое
				serial				// можно указать вместо типа - тогда это будет целочисленный с автоинкрементом
				...
			}
		}

		// пример создания таблицы (MySQL)
		CREATE TABLE Customers
		(
			Id INT,
			Age INT,
			FirstName VARCHAR(20),
			LastName VARCHAR(20)
		);
		
		// пример создания таблицы (POSTGRES)
        CREATE TABLE customers
        (
            Id SERIAL PRIMARY KEY,
            FirstName CHARACTER VARYING(30),
            LastName CHARACTER VARYING(30),
            Email CHARACTER VARYING(30),
            Age INTEGER
        );
		
		Автоинкремент (Postgres) {
            SERIAL - синтаксическая контрукция, которая создаёт поле типа int с автоинкрементом
            В последнийх версиях рекомендуется использовать вместо SERIAL слеующей конструкцией:
            GENERATED BY DEFAULT AS IDENTITY
            То есть код содания таблицы будет выглядеть так:

            CTREATE TABLE Person(
                id int GENERATED BY DEFAULT AS IDENTITY,
                name varchar,
                age int,
                email varchar
            )

            // Добавляем PRIMART KEY - Алишев говорит, что это нормально
            CTREATE TABLE Person(
                id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                name varchar,
                age int,
                email varchar
            )

			Вместо 	GENERATED BY DEFAULT 	AS IDENTITY можно испольовать 
					GENERATED ALWAYS 		AS IDENTITY
			Отличие в том, что при добавлении строки в таблицу 
			BY DEFAULT позволяет как указывать значение поля, так и не указывать;
			Опция ALWAYS не позволит указать поле - поле всегда генерируется автоматически
        }
	}

	DESCRIBE table_name; { // выводит информацию о полях таблицы и их свойствах
		MySQL:
			DESCRIBE table_name;	
			DESCRIBE table_name\G // другой формат
		Posthres:
			\d
	}

	RENAME TABLE старое_название TO новое_название; // переименование таблицы
	RENAME TABLE Customers TO Clients;

	DROP TABLE Clients; // удаление таблицы 

	TRUNCATE TABLE Clients; // удалание всех строк в таблице. Таблица остаётся
	TRUNCATE Clients;       // так тоже должно работать
	
	ALTER, изменения таблиц и колонок {
		
		// изменить ограничение. В данном случае изменение стретегии при удалении сущности, на которую ссылаются в другой таблице
		ALTER TABLE tableName
		DROP CONSTRAINT constraintName,
		ADD CONSTRAINT constraintName
		   FOREIGN KEY (id)
		   REFERENCES games(inotherTableId)
		   ON DELETE CASCADE; 		// или RESTRINC или SET NULL

		Изменения колонок {
			ALTER TABLE table_name ADD COLUMN field_name тип_даных; // добавить столбец в таблицу

			ALTER TABLE table_name RENAME COLUMN old_name TO new_name; // изменить название таблицы.

			ALTER TABLE tableName DROP COLUMN columnName; // удаление
		}
		
	}


	
}

Работа с SEQUENCE {
	Создание {
	create sequence [if not exists] mySequence [increment [by] myStepSize] [...]
	Примеры	
		create sequence customer_seq;
		create sequence customer_seq increment by 50;
	}
	
	Использование {
		select next value for some_seq;
		
		insert into cat (id, name) values (next value for CAT_SEQ, 'shit cat12');
	}
}

Ещё о работе с таблицами (Postgres точно, в MySQL не проверял, но должно быть также) {

	Constraints (Ограничения) {
		UNIQUE // уникальность
		NOT NULL
		PRIMARY KEY { // включает в себя два ограничения сразу:
			UNIQUE
			NOT NULL
		}
		FOREIGN KEY {
			UNIQUE 		// ? в таблице может быть несколько записей с одним и тем же FOREIN KEY. Возможно строить удалить эту строчку
			NOT NULL	//да и это тоже
		}
		CHECK (условие) // Можно указать какое-то условие

		Пример:
		CREATE TABLE customers
		(
			Id SERIAL PRIMARY KEY,
			FirstName CHARACTER VARYING(30) NOT NULL,
			LastName CHARACTER VARYING(30) NOT NULL,
			Email CHARACTER VARYING(30) NOT NULL UNIQUE,
			Age INTEGER CHECK (age > 0)
		);
	}

	Составной ключ {
		Исключает добавление двух записей, у которых одинаковы два поля соответственно
		Пример:
			CREATE TABLE Actor_Movie(
				actor_id REFEFRENCES Actor(actor_id),
				movie_id REFEFRENCES Movie(movie_id),
				PRIMARY KEY (actor_id, movie_id)	// PRIMARY KEY указывается не как обычно, а в отдельной строке, чтобы сделать его составным
			)
	}

	Отношения и ключи {
		Для добавления связи используется слово REFERENCES
		в зависимой (child) таблице на колонке,
		где указывается внешний id.
		Это и будет forein key.

		One to one {
			Для связи One to one RREFERENCES указывается например 
			на PRIMARY KEY в child таблице.
			Значит внешний id будет и внутренним.
			Пример:
			// Каждому человеку соответствует один паспорт и наоборот
			CREATE TABLE Person(
				id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
				name VARCHAR(100)
			);
			CREATE TABLE Passport(
				person_id int PRIMARY KEY REFEFRENCES Person(id), // указываем REFERENCES на PRIMARY KEY
				name VARCHAR(100)
			);
			
			Самое важное для OneToOne то, что поле являющееся FOREIGN KEY 
			должно иметь граничение UNIQUE. 
			То есть, либо используется PRIMARY KEY как в примере выше,
			либо явно используется ограничение UNIQUE
		}

		One to many {
			Для связи One to many REFERENCES указывается на отдельном столбце.
			То есть поле FOREIGN KEY не обязано быть UNIQUE.
			Свой PRIMARY KEY в child таблице может быть сделан как обычно
			Пример:
			// Режиссёр может снять несколько фильмов, но у фильма один режиссёр (в нашем случае)
			CREATE TABLE Director(
				id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
				name VARCHAR(100)
			);
			CREATE TABLE Movie(
				id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
				director_id int REFERENCES Director(id),		// указываем на что именно ссылаемся
				name VARCHAR(200)
			)
		}

		Many to many {
			Для связи Many to many REFERENCES указывается во внешней таблице у двух столбцов
			Чтобы не было дублей записей, рекомендуется добавить составной первичный ключ, который будет состоять из двух внешних
			Пример:
			CREATE TABLE Actor(
				id int generated by default as identity primary key,
				name varchar(100)
			)
			CREATE TABLE Movie(
				id int generated by default as identity primary key,
				name varchar(100)
			)
			
			// Связывающая таблица
			CREATE TABLE Actor_Movie(
				actor_id int REFEFRENCES Actor(id),
				movie_id int REFEFRENCES Movie(id),
				PRIMARY KEY (actor_id, movie_id)		// PRIMARY KEY указывается не как обычно, а в отдельной строке, чтобы сделать его составным
			)
		}
	}

	Каскадирование {
		Определяет то делать, если попробовать удалить строку из одной таблицы, а строка из другой таблицы будет ссылаться на удаляемую?
		3 стратегии:
			RESTRICT 	// запрещает удаление - по умолчанию
			CASCADE		// удаляет все зависимые строки из других таблиц
			SET NULL	// оставляет в зависимой строке null
		Указывается со словом ON DELETE ...
		Пример:
		CREATE TABLE Director(
			director_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			name VARCHAR(100)
		);
		CREATE TABLE Movie(
			movie_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			director_id int REFERENCES Director(director_id) ON DELETE CASCADE,	// указываем стратегию
			name VARCHAR(200)
		)

	}

}

Работа с данными {	
	SELECT {
		// SELECT FROM
		SELECT * FROM table_name; // * - обозначает вывод всех полей из таблицы
		SELECT field FROM table_name; // вывод конткретного поля из таблицы
		SELECT field_1, field_2 FROM table_name; // вывод нескольких полей из таблицы

		// WHERE
		SELECT * FROM table_name WHERE <условие>;
		SELECT * FROM table_name WHERE <условие> AND <условие>; // условий может быть больше
		SELECT * FROM teachers WHERE age < 30;
		SELECT * FROM teachers WHERE age < 30 AND salary < 5000;

		// ORDER BY
		SELECT * FROM teachers ORDER BY age; // вывод всех учителей из таблицы в порядке возрастания их возраста.
		SELECT * FROM teachers WHERE age < 30 ORDER BY age; // то же самое, но только тех, кто моложе 30
		SELECT * FROM teachers WHERE age < 30 ORDER BY age DESC; // DESC от слова descending - убывающий. Т.е. обратный порядок
		SELECT * FROM teachers ORDER BY age DESC, salary; // Сортировка по двум параметрам. Если будет 2 и более человека с одним возрастом, то они относительно друг друга будут отсортированы по зарплате. У отдельных колонок по которым сортируем можем написать DESC

		// LIMIT
		SELECT * FROM teachers WHERE age < 30 AND type = 'programming' ORDER BY age DESC, salary LIMIT 3; // LIMIT - ограничивает количество строк в выдаче - только первые 3 строки

		// DISTINCT
		SELECT DISTINCT field_name FROM table_name; // Для исключения повторяющихся результатов, чтобы не повторялись.
		SELECT DISTINCT type FROM courses;
		
			//можно применять простые выражения
		SELECT name, salary, salary * 12 FROM teachers; // пример умножения // выведет кроме имени и зарплыты за месяц, годовую зарплату.

		// Можно назвать колонки по-своему
		SELECT name, salary AS month_salary, salary * 12 AS annual_salary FROM teachers;
	}
	INSERT {

		INSERT [INTO] имя_таблицы [(список_столбцов)] VALUES (значение1, значение2, ... значениеN);

		
		INSERT some_table(field_name, field_name) VALUES(value1, value2); // вставка в конкретные поля

		INSERT some_table(field_name, field_name) VALUES(value1, value2), (value1, value2); // множественная всставка
		
		INSERT some_table VALUES(value1, value2);  // вставка во все имеющиеся поля. Перечислять поля, в которые будет вставка в этом случае не обязательно
		
		Примеры:
		INSERT INTO Products VALUES (1, 'Galaxy S9', 'Samsung', 4, 63000);
		insert into shit(name, age) values ('Tim', 10);
    	insert into shit(name, age) values ('Sam', 15), ('John', 35);
		// Если есть поля, которые генеруриются автоматически, например id, их лучше не вставлять
	
		// INSERT SELECT
		// Конструкция позволяет заполнить данными таблицу, результатом запроса.
		INSERT INTO table_name(field1, field2) SELECT ... // дальше идёт запрос.
		
		
	}	
	UPDATE {
		UPDATE tableName SET columnName = 'example@test.ru' WHERE ...;
    	UPDATE shit SET email = 'example@test.ru' WHERE id = 1;
    	UPDATE shit SET email = 'Fuck@You.ru', age = 99 WHERE id = 3;	// несколько колонок
		UPDATE courses SET price = price * 0.95 WHERE id = 46;
    }
	DELETE {
		DELETE FROM table_name WHERE условие; 
		Примеры:
			DELETE FROM book WHERE id = 1; // удаление одной сроки из таблицы
			DELETE FROM book WHERE age > 20; // удаление всех строк, где поле age больше 20
		
		Полная очистка таблицы. Таблиц остаётся, все строки удаляются
		TRUNCATE tableName;
	}
	UNION {
		<запрос> UNION <запрос>; /*Объединение результатов двух запросов.
		Особенности:
			- должно быть одинаковое количесто запрашиваемых полей
			- условия могут быть у каждого свои и они не пересекаются
			- может быть только один LIMIT на оба выражния
			- выборка будет отсортирована

		*/
		SELECT name FROM teachers WHERE salary < 5000 UNION SELECT age FROM students WHERE age < 20 LIMIT 100;

		// Можно писать в одну строку, как выше, а можно переносить строки:
		SELECT name FROM teachers WHERE salary < 5000
		UNION
		SELECT age FROM students WHERE age < 20 LIMIT 100;

		/* UNION по умолчанию исключает одинаковые результаты из выборки. Т.е. одинаковых результатов не будет даже среди результатов отдельных запросов
		UNION ALL не делат исключения*/
		SELECT age FROM teachers WHERE age < 25
		UNION ALL
		SELECT age FROM students WHERE age < 20;

		// Можно исключить повторяющиеся результаты внутри запросов, но обединить их без исключения. В таком случае, если в выборке и будут одинаковые результаты, то не более, чем число объединяемых запросов.
		SELECT DISTINCT age FROM teachers WHERE age < 25
		UNION ALL
		SELECT DISTINCT age FROM students WHERE age < 20;
	}	
	Функции {
		NOW() // возвращает текущее время
		DATEDIFF(first_date, second_date) // разость дат. первое минус второе.
		// Пример
		SELECT DATEDIFF(NOW(), registration_date) FROM students;

		IF(boolean, if_true, if_false) // уловный оператор
		// Пример
		SELECT name, IF(students_count > 500, 'Full', 'Not full') AS status FROM courses; // Будет создан дополнительный столбик status с результатами функции IF

		CONCAT(first, second, ..., last) // принимает нграниченное число парметров, склеивает строки

		// пример
		SELECT CONCAT('Зарплата у ', name, ' равна ', salary) FROM teachers;

		// COUNT()
		SELECT COUNT(*) FROM students;
		SELECT COUNT(name) FROM students; // будет анлогичным

		// AVG() - возвращает среднее
		// MAX() - возвращает максимальное значение поля
		// SUM() - сумма
	}	
	JOIN/GROUP BY {
		SELECT table1.field, table2.field FROM table1
		JOIN table2 ON table1.id = table2.id /*после ON следует правило соединения таблиц*/
		<JOIN ON> /* можно делть несколько JOIN*/
		<WHERE><ORDER BY><LIMIT>; /* только после этого следуют остальные выражения*/

		По умолчанию все JOIN являются INNER JOIN - если есть строки, которым не нашлось соответствия во второй таблице, то строка не показывается в результате
		Есть также LEFT JOIN - если строке из левой (которая упоминается слева от JOIN) таблице нет соответствия, то она всё равно будет выведена в результат
		Соответственно есть RIGHT JOIN (допускает NULL слева и результат справа) и OUTER JOIN (допускает NULL с обоих сторон)

		// GROUP группировка
		// следующий запрос позволит подсчитать количество взятых курсов у каджого студента. То есть сначала произойдет группировка по имени, а затем агрегирующая функция подсчитает количество строк.
		SELECT students.name AS Student, count(*) AS 'Courses count' FROM students
		JOIN subscription ON students.id = subscriptons.students_id
		GROUP BY name
		ORDER BY name;
	}	
	Подзапросы {
		// Следующий запрос выдаёт для каждого студента количесвто учителей, которые их старше.
		SELECT name, (SELECT count(*) FROM teachers WHERE teachers.age > students.age) AS older_count FROM students ORDER BY older_count DESC LIMIT 5;
	}	
	VIEW {
		Предоставляет результат запроса в качестсве новой таблицы.
		
    	CREATE VIEW viewName AS selectStatement;
    	CREATE VIEW viewName (newColumnNameInViewForFirstColumn, sameForSecond) AS selectStatement; // названий новых колонок может быть меньше, но не больше.

		
    	Пример:
		// Тут вместо того, чтобы каждый раз делать сложный запрос, мы сделаем это один раз и сохраним в представлении 'pp'. Дальше получать данные оттуда будет проще
    	create view pp (na, num) as select name, number from person join passport on passport.some_id = person.id;

    }
}

TRANSACTIONS {
	
	Usage {	
		begin; // начать транзакцию
		commit; // успешно завершить транзакцию
		chackpoint some_text; // не уверен, что правильно написал
		rollback; { // откатиться
			rollback to // от
		}
		set transaction isolation level {read commited, repeatable read, serializable} //  нужно писать внутри транзакции (по крайней мере в postgres)
		show transaction_isolation; // показать текущий уровень изоляции
					
	}
	
	
	Isolation levels {
		Read Uncommited (sees non commited changes, and new rows)
			Have all problems
		Read Commited (sees commited changes, and new rows)
			Excludes dirty read problem
		Repeatable Read (sees commitad changes only before this transaction begins, but new rows during transaction too)
			Excludes non-repeatable read
		Serializable (some like Repeateble read, but don't sees new rows. So insertions and deletions are serial with this isolation)
			Excludes phantom reads
	}
	
	ACID {
		Atomicity - операции в транзакции либо выполняются все, либо не выполняется ничего.
		Consistency - транзацикции позволяют сохранить согласованность данных
		Isolation - транзакции работают независимо, не видны промежуточные результаты внутри транзакции
		Durability - при получении инфмормации о завершении транзакции, данные гарантировано сохранены
	}
}
}

Other {
	MySQL 
		// Other
		show variables like "character_set_database" // показывает текущую кодировку
		show variables like '%charac%'; // можно вести поиск по переменным по частичному названию
		
		mysql -uroot -ppassword -e "USE my_database; set names utf8mb4; select * from table" > file_name.txt // Так можно выгрузить данные в файл. Однако в моих тестах требовалось прописывать set names как в примере, так как без него были проблемы с кодировкой. Видимо потому что Windows по-умолчанию использует что-то традиционое
}

INDEXES {
	CREATE UNIQUE CLUSTERED INDEX ix_oriderid_lineid ON dbo.Sales(OrderID, LineID); // create complex index on two fields
}