Kvadeck
8/18/2017 - 10:41 PM

Изучение реляционных баз данных и запросов

Изучение реляционных баз данных и запросов


USE $Database_name \ Выберает базу для дальнейшего использования.

SHOW TABLES; \ Выводит название всех таблиц в базе.
SHOW CREATE TABLE my_contacts; \ Показывает текст команды для выполнения в терминале.
DESCRIBE $TABLENAME; \ Выводит подробные данные о таблице в базе.

CREATE USER jack IDENTIFIED BY '1223'; \\ Создание пользователя
SET PASSWORD FOR 'root'@'localhost' = PASSWORD ('b4dc10wnZ'); \\ Назначение пароля для учетной записи root.
Команда GRANT точно определяет, какие операции с таблицами и столбцами могут выполняться пользователями.
GRANT SELECT ON clown_info TO jack;
GRANT SELECT ALL ON clown_info TO jack; \\ Пользователю разрешается выполнять команду SELECT и All.
REVOKE SELECT ON clown_info FROM jack; \\ Лишает прав jack на использование SELECT в таблице clown_info.
REVOKE DELETE ON clown_info FROM jack CASCADE; \\ CASCADE означает, что отзыв затронет не только заданного пользователя, но и всех пользователей по цепочке.
REVOKE DELETE ON clown_info FROM jack RESTRICT; \\ Если лишение привилегии затронет других пользователей, команда REVOKE c RESTRICT выдает ошибку.

CREATE ROLE data_entry;
GRANT SELECT, INSERT ON some_table TO data_entry; \\ Назначение роли.
GRANT data_entry TO doc; \\ Назначение роли пользователю.
DROP ROLE data_entry; \\ Удаление роли.
GRANT data_entry TO doc WITH ADMIN OPTION \\ WITH ADMIN OPTION разрешает пользователю doc назначить роль data_entry любому другому пользователю.

CREATE USER jack IDENTIFIED BY '1223';
SELECT CURRENT_USER; \\ Узнать какой пользователь в данный момент.
GRANT SELECT ON
clown_info
TO jack; \\ Использование сразу двух команд CREATE USER и GRANT.

SELECT * FROM my_contacts WHERE location LIKE '%CA'; \ Выводит данные из таблице содержащие CA и любое количество символов.
SELECT RIGHT или LEFT (location, 2) FROM my_contacts; \ Выборка двух символов с правого края или левого края.
SELECT SUBSTRING_INDEX (location,',',1) FROM my_contacts_id; \ Вывод из столбца предшествующих данных до символа во втором параметре. Третий параметр указывает количество повторейний поиска этого символа.

Знак % — обозначает любое количество произвольных символов.
Знак _ - обозначает ровно один произвольный символ.
Знак \ - обозначает экранирование символов. Чаще всего применяется для апострофов.
Знак <>- этот знак означает «не равно». Его результат прямо противоположен результату знака =.
Ничто не гарантирует, что последняя запись таблицы была добавлена позднее других записей.
Значения NULL могут создать проблемы при будущих запросах. А лучше ввести какое-нибудь значение, чем оставлять NULL в столбце,потомму что NULL не находится по условию «равно».
Если нет полной уверенности относительно того, что условие WHERE удалит только нужные записи, сначала выполните команду SELECT.
Атомарность данных — пер­вый шаг на пути создания НОРМАЛИЗОВАННОЙ таблицы.
Внешний ключ - столбец таблицы, в котором хранятся значения ПЕРВИЧНОГО КЛЮЧА другой таблицы.
Представление всех структур базы данных (таблиц,столбцов, итд) и логических связей между ними называется схемой.
При вставке внешний ключ будет принимать только значения, существующие в первичном ключе родительской таблицы. Это требование называется целостностью данных.

Один-к-одному : ровно одна запись родительской таблицы связывается с одной записью дочерней таблицы.
Один-ко-многим : запись в таблице А может быть связана с МНОГИМИ записями в таблице B, но запись в таблице В может быть связана только с ОДНОЙ записью в таблице А.
Многие-ко-многим : обычно состоят из двух связей один-ко-многим, объединенных при помощи соединительной таблицы.

СОСТАВНЫМ КЛЮЧОМ называется ПЕРВИЧНЫЙ КЛЮЧ, состоящий из нескольких столбцов, комбинация которых образует уникальные значения ключа.
РЕФЛЕКСИВНЫЙ внешний ключ - первичный ключ таблицы, используемый в той же таблице для других целей.

Если изменение содержимого одного столбца должно приводить к изменению другого, говорят, что второй столбец функционально зависим от первого.
Для компактного описания функциональных зависимостей часто используется следующая запись: Т.х->Т.у
Зависимым называется столбец с данными, которые могут измениться в случае изменения другого столбца. Независимые столбцы существуют сами по себе.
Частичная функциональная зависимость означает, что не-ключевой столбец зависит от некоторых, но не от всех столбцов составного первичного ключа.
Если изменение не-ключевого столбца может привести к изменению других не-ключевых столбцов, значит, существует транзитивная зависимость.

Первая нормальная форма - Столбцы содержат только атомарные значения. В таблице нет повторяющихся групп данных.
Вторая нормальная форма - Определяет связь первичного ключа таблицы с хранящимися в ней данными.
Третья нормальная форма - Таблица находиться во второй нормальной форме. Таблица не имеет транзитивной функциональной зависимости.

Псевдоним : Укажите псевдоним после первого использования исходного имени столбца в запросе. Псевдоним изменяет имя столбца в результатах, но не изменяет исходного имени столбца. Назначение псевдонима без ключевого слова AS. Псевдоним должен указываться сразу же после имени таблицы или столбца, с которым он связывается.

Перекрестное со­единение (CROSS JOIN) возвращает комбинации каждой записи первой табли­цы с каждой записью второй таблицы. Вместо CROSS JOIN можно поставить запятую.
Внутренним соединением (INNER JOIN) называется перекрестное соединение, из результатов которого часть записей исключается по условию запроса.
Внутреннее соединение комбинирует записи из двух таблиц в соответствии с заданным условием.
Эквивалентное соеди­нение — внутреннее соединение с проверкой равенства.
Неэквивалентное соединение возвращает записи, у которых задан­ные значения столбцов не равны. Неэквивалентное соединение про­веряет несовпа­дение значений.
Естественные соединения (NATURAL JOIN) возможны только в том случае, если столбец, по которому выполняется соединение, имеет одинаковые имена в обеих таблицах.

В левом внешнем соединении (LEFT OUTER JOIN) для КАЖДОЙ ЗАПИСИ ЛЕВОЙ таблицы ищется соответствие среди записей правой таблицы.
Значение NULL в результатах левого внешнего соединения означает, что правая таблица не содержит значений, соответствующих левой таблицы.

При САМОСОЕДИНЕНИЕ запрос строиться так, как если бы одна таблица была бы другой таблицей но с одинаковыми значениями.

Существует еще один способ получения объединенных результатов таблиц - так называемые союзы (ключевое слово UNION). Есть некоторые правила для UNION Количество столбцов в командах SELECT должно быть одинаковым. Нельзя выбрать два столбца одной командой и еще один столбец другой.


INTERSECT возвращает данные их результатов первого запроса, которые также присутствуют во втором запросе.(Не поддерживается MYSQL)
EXCEPT возвращает только те значения, которые присутствуют в результатах первого запроса, но не встречаются в результатах второго.(Не поддерживается MYSQL)

Если подзапрос использует оператор =, он возвращает одно значение, одну запись и за одного столбца (иногда называется «ячейкой», но в SQL используется термин скалярное значение). Это значение сравнивается со столбцами и в условии WHERE.

Подзапрос представля­ет собой запрос, упакованный» в другом запросе. Также он может называться внутренним запросом».

Подзапрос, исполь­зуемый в качестве выражения столбца в команде SELECT, может возвращать только одно значение из одного столбца.

Автономный подзапрос, не содержаищй ссылок на данные внешнего запроса, называется НЕКОРРЕЛИРОВАННЫМ ПОДЗАПРОСОМ.

В модели с некоррелированным подзапросом внутренний запрос (то есть подзапрос) интерпретируется РСУБД после внешнего запроса.

Некоррелированный подзапрос при помощи IN или NOT IN проверяет факт наличия (или отсутствия) значений, возвращенных подзапросом, в наборе. 

Ограничение CHECK определяет значения, которые могут вставляться в столбец базы данных. В нем используются те же условные операторы, что и в условии WHERE.
Конструкция CHECK OPTION проверяет каждую попытку выполнения вставки или обновления данных на соответствие условию WHERE представления.

В сущности, ПРЕДСТАВЛЕНИЕ - это таблица, существующая только во время использования представления в запросе. Представление называется виртуальной таблицей, потому что ведет себя как настоящая таблица и с ним можно выполнять те же операции, что и с обычными таблицами. Если в ПРЕДСТАВЛЕНИЕ используются агрегатные функции(такие, как SUM, COUNT или AVG), оно не может использоваться для обновления данных. Кроме того, если представление содержит условия GROUP BY, DISTINCT или HAVING, изменение данных также невозможно.

Обновляемое представление включает все столбцы NOT NULL из своих таблиц. По сути могу выполняться все команды : INSERT, UPDATE, DELETE. Если в представление включаются все столбцы NOT NULL. Стоит учесть что обновление возможно только без использования агрегатных операторов(SUM, COUNT, AVG и.т.д.), а так же BETWEEN, HAVING, IN ,NOT IN

Ограничения CHECK и представления помогают сохранить контроль над базой данных при одновременной работе нескольких пользователей.

Если в ходе транзакции не удается выполнить хотя бы одну операцию, то не выполняется ни одна операция.
При работе с тразакциями в SQL используются три команды. 
START TRANSACTION; \\ Команда сообщает SQL о начале транзакции
COMMIT \\ Команда закрепляет результаты всего выполненного кода.
ROLLBACK \\ Команда возвращает базу данных к состоянию до начала транзакции.

Изменения вносятся в базу данных только при выполнении команды COMMIT

Функция SUBSTRING (текст, начало, длина) возвращает часть строкового значения текст, начиная
с буквы в позиции начало. Параметр длина определяет длину возвращаемой строки.
Функция SUBSTR получает текст столбца interest и возвращает его заданную часть. Мы выделяем символы, которые были скопированы в interest1, а так же еще два символа (запятая и пробел). 
Функции UPPER (текст) и LOWER (текст)
Функция REVERSE (текст) переставляет символы строки в обратном порядке.
Функции LTRIM(TeKCT) и RTRIM(TeKCT) возвращают строку, полученную удалением лишних пробе­лов в начале (у левого края) или в конце (у правого края) строки.
Функция LENGTH (текст) возвращает количество символов в строке.
Функция SUM - суммирует значение столбца.

Заполнение таблицы командой SELECT должно выполняется после псевдонима так называймой командой AS.

ADD COLUMN Last,First и.т.д. - без указания столбика, столбик размещается в конце.
DECIMAL (7,2) - Значение 7 количество символов, значение 2 количество символов после запятой.
AUTO_INCREMENT - помните, что в таблице может быть только одно поле, типа INT и не NULL.

contact_id INT NOT NULL AUTO_INCREMENT, \ Добавляет параметр AUTO_INCREMENT, автоматическое присваивание PRIMARY KEY, так же есть INDEX для установки начала нумерации.

ALTER TABLE my_contacts \ Добавление PRIMARY KEY первым столбцом.
ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (contact_id);

ALTER TABLE your_table CHANGE your_id your_id 
INT(11) NOT NULL AUTO_INCREMENT; \ Назначение параметра AUTO_INCREMENT выбранному столбцу.

ALTER TABLE projects_list
CHANGE COLUMN descriptionofproj proj_desc VARCHAR(100),\ Переименование столбцов
CHANGE COLUMN contractoronjob company_name VARCHAR(30);

ALTER TABLE my_contacts \ Добавление нового столбца, команда AFTER указывает после какого добавить.
ADD COLUMN telephone INT NOT NULL AFTER first_name;

ALTER TABLE projects_list
ADD COLUMN company_phone VARCHAR(10); \ Добавление нового столбца.

ALTER TABLE projects_list \ Удаление столбца.
DROP COLUMN start_date;

ALTER TABLE your_table \ Удаление ПЕРВИЧНОГО КЛЮЧА.
DROP PRIMARY KEY;

CREATE TABLE my_contacts(last_name VARCHAR(30),first_name VARCHAR(20),email VARCHAR(50), gender CHAR(1), birthday DATE,profession VARCHAR(50),location VARCHAR(50),status VARCHAR(20), interests VARCHAR(100),seeking VARCHAR(100));

INSERT INTO my_contacts (last_name , first_name, email, gender, birthday, profession, location, status, interests, seeking) VALUES ('Андерсон','Джилиан','jill_anderson@gmail.com','Ж','1980-05-09','Писатель', 'Пало-Алльто, CA','Не замужем','Каяк,террариум','Друзья');

CREATE TABLE easy_drinks (drink_name VARCHAR(100) NOT NULL, main VARCHAR(50) NOT NULL DEFAULT 'Содовая', amount1 DEC(4,2), second VARCHAR(50) NOT NULL, amount2 DEC(4,2), directions TEXT(300) );
CREATE TABLE my_contacts(last_name VARCHAR(30),first_name VARCHAR(20),email VARCHAR(50), gender CHAR(1), birthday DATE,profession VARCHAR(50),location VARCHAR(50),status VARCHAR(20), interests VARCHAR(100),seeking VARCHAR(100));

INSERT INTO my_contacts (last_name , first_name, email, gender, birthday, profession, location, status, interests, seeking) VALUES ('Андерсон','Джилиан','jill_anderson@gmail.com','Ж','1980-05-09','Писатель', 'Пало-Алльто, CA','Не замужем','Каяк,террариум','Друзья');

CREATE TABLE easy_drinks (drink_name VARCHAR(100) NOT NULL, main VARCHAR(50) NOT NULL DEFAULT 'Содовая', amount1 DEC(4,2), second VARCHAR(50) NOT NULL, amount2 DEC(4,2), directions TEXT(300) );

INSERT INTO easy_drinks VALUES ('Сода плюс', 'Содовая', '2', 'Виноградный сок', '1', 'Взболтать в бокале, подвать без льда');
INSERT INTO easy_drinks VALUES ('Сода плюс', 'Содовая', '2', 'Виноградный сок', '1', 'Взболтать в бокале, подвать без льда');

SELECT * FROM easy_drinks WHERE main = 'Спрайт';
SELECT drink_name FROM easy_drinks;
SELECT drink_name FROM easy_drinks WHERE second = 'лимонад';
SELECT drink_name FROM easy_drinks WHERE main='Содовая' and amount1>=2.00;

SELECT color FROM drink_info WHERE color LIKE '%ый';
SELECT drink_name FROM drink_info WHERE drink_name LIKE '____ая';
SELECT drink_name FROM drink_info WHERE calories BETWEEN 30 and 60;
SELECT drink_name FROM drink_info WHERE NOT carbs BETWEEN 3 AND 5;

INSERT INTO clown_info
(name, last_seen, appearance, activities)
VALUES
('Элси','Дом престарелых Черри Хилл','Ж, рыжие волосы, зеленый костюм, огромные ботинки','шарики, машинки'),
('Пиклз','Вечеринка Джека Грина','М, оранжевые волосы, синий костюм, огромные ботинки','мим'),
('Снаглз','Болмарт','ж, желтая рубашка, красные штаны','рожок, зонтик'),
('Мистер Хобо','Цирк ВС','М, сигара, черные волосы, маленькая шляпа','скрипка'),
('Кларабелл','Дом престарелых Бельмонт','Ж, розовые волосы, большой цветок, синее платье','кричалки, танцы'),
('Скутер','Больница Окленд','М, синие волосы, красный коаюм, большой нос','шарики'),
('Зиппо','Торговый центр Милстоун','Ж, оранжевый костюм, штаны','танцы'),
('Бэйб','Автошкола Эрла','Ж, розовый костюм с блестками','эквилибристика, машинки'),
('Бонзо','','М, женское платье в горошек','пение, танцы'),
('Снифлз','Заведение Трэйси','М, зелено-фиолетовый костюм, длинный нос','');

DELETE FROM clown_info WHERE activities = 'танцы'; \ Удаление всеx строки, если хоть один столбик в этой строке содержит 'танцы'
DELETE FROM $table_name / удаление всех записей из таблицы.
DELETE FROM clown_info WHERE activities= 'кричалки, танцы' AND name = 'Кларабелл';

UPDATE clown_info
SET activities ='игра на баяне'
WHERE activities = 'Игра на баяне';

UPDATE drink_info
SET cost = cost + 1
WHERE drink_name= 'Голубая луна' OR drink_name=  'Вот тебе на' OR drink_name = 'Лаймовый физ';

ALTER TABLE projects RENAME TO projects_list ; \ Переименование таблицы

ALTER TABLE projects_list
MODIFY COLUMN proj_desc VARCHAR(120); \ Изменение типа данных без изменения именя столбца.

ALTER TABLE car_table 
MODIFY COLUMN color AFTER model; \ Изменение порядка столбца с помощью команды MODIFY.

UPDATE my_contacts 
SET state = RIGHT(location, 2); \ Обработка команды RIGHT в связки с UPDATE;

UPDATE movie_table \ Выполнение условий для столбцов в таблице movie_table
SET category
CASE 
WHEN drama = 'Y' THEN 'Драма'
WHEN comedy = 'Y' THEN 'Комедия'
WHEN actioon = 'Y' THEN 'Боевик'
WHEN gore = 'Y' THEN 'Ужасы'
WHEN scifi = 'Y' THEN 'Фантастика'
WHEN for_kids = 'Y' THEN 'Для детей'
WHEN cartoon = 'Y' AND rating = 'G' THEN 'Семейное'
ELSE 'Разное'
END;

UPDATE movie_table \ Выполнение двух условий для столбца category в таблице movie_table
SET category
CASE 
WHEN drama = 'Y' and rating = 'R' THEN 'Драма-r'
WHEN comedy = 'Y' and rating = 'R' THEN 'Комедия-r'
WHEN actioon = 'Y' and rating = 'R' THEN 'Боевик-r'
WHEN gore = 'Y' and rating = 'R' THEN 'Ужасы-r'
WHEN scifi = 'Y' and rating = 'R' THEN 'Фантастика-r'
WHEN category = 'Разное' AND rating = 'G' THEN 'Семейное'
END;

SELECT title, category \ Сортировка title по алфавиту где category = 'Драма'
FROM movie_table
WHERE
category = 'Драма'
ORDER BY title;

SELECT title, category,purchased \ Выбор трёх столбцов и сортировка по столбцу purchased. Если стоит в конце DESC то сортировка будет в обратном порядке.
FROM movie_table
ORDER BY category,purchased DESC;

SELECT SUM(sales) \ Складывает значения столбца sales где в first_name встречается Николь. 
FROM cookie_sales
WHERE first_name = 'Николь';

SELECT firs_name, SUM(sales) \ Суммирует данные столбца, группирует значение столбца first_name, вывод значений по убыванию.
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales)DESC;

SELECT first_name, AVG(sales) \ Расчте среднего значения в столбце sales, группировка по first_name.
FROM cookie_sales
GROUP BY first_name;

SELECT first_name, MAX(sales) \ Возвращает наибольшее значение в столбце sales, так же можно вернуть наименьшее значение MIN.
FROM cookie_sales
GROUP BY first_name;

SELECT COUNT(sale_date) \ Функция COUNT возвращает количество записей в столбце 'sale_date', если запись NULL то не попадает в подсчет.
FROM cookie_sales;

SELECT DISTINCT sale_date \ Ключевое слово DISTINCT убирает дубликаты в sale_date, а потом группирует.
FROM cookie_sales
ORDER BY sale_date;

SELECT COUNT(DISTINCT sale_date) \ Считает и убирает дубликаты в столбце sale_date.
FROM cookie_sales;

SELECT first_name, sales \ Лимит с указанием параметра. Первый с чего начинать лимит,а второй сколько результатов выводить.
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales)DESC
LIMIT 1,1;

SELECT * FROM my_contacts \ Поиск по многим параметрам включая SUBSTRING.
WHERE gender = 'Ж'
AND status = 'Heзамужем'
AND state = 'MA'
AND seeking LIKE '%Мужчина%'
AND birthday> '1950-28-08'
AND birthday<'1960-28-08'
AND SUBSTRING INDEX(interests,','1') = 'животные';

---------------------------------------------------------------------------------------------------------------------------

CREATE TABLE interests (

int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, \ Создание авто инкремента и назначение PRIMARY KEY.
interest VARCHAR(50) NOT NULL, \ Создание столбика interest.
contact_id INT NOT NULL, \ Создание столбика с классом INT и с условием NOT NULL.
CONSTRAINT my_contacts_contact_id_fk \ Создание ограничения. Ограничению присваиваеться имя, по которому можно определить, из какой таблицы взят ключ
(my_contacts), как он называется (contact_id) и что он является внешним (fk).
FOREIGN KEY (contact_id) \ В скобках указываеться имя ключа. (любое подходящие название).
REFERENCES {1.my_contacts} {2.(contact_id)}; \ 1. Указывает, из какой таблицы взят внешний ключ. 2. И как он назывался в этой таблице. 

)
---------------------------------------------------------------------------------------------------------------------------
Создание таблицы по типу «многие-ко-многим»
---------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `woman_shoes` ( \ Первая таблица по типу «многие-ко-многим».
  `woman_id` int(11) NOT NULL AUTO_INCREMENT,
  `woman` varchar(45) NOT NULL,
  PRIMARY KEY (`woman_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE TABLE `shoes_woman` ( \ Вторая таблица по типу «многие-ко-многим».
  `shoe_id` int(11) NOT NULL AUTO_INCREMENT,
  `shoe_name` varchar(45) NOT NULL,
  PRIMARY KEY (`shoe_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE TABLE `woman_all` ( \ Исходная таблица по типу «многие-ко-многим».
  `woman_id` int(11) NOT NULL,
  `shoe_id` int(11) NOT NULL,
  KEY `fk_woman_all_1_idx` (`woman_id`),
  KEY `fk_woman_all_2_idx` (`shoe_id`),
  CONSTRAINT `fk_woman_all_1` FOREIGN KEY (`woman_id`) REFERENCES `woman_shoes` (`woman_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_woman_all_2` FOREIGN KEY (`shoe_id`) REFERENCES `shoes_woman` (`shoe_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
---------------------------------------------------------------------------------------------------------------------------

Изменение таблицы my_contacts с уже внесенными данными. Для подбора пары.
---------------------------------------------------------------------------------------------------------------------------

ALTER TABLE my_contacts
ADD COLUMN interest1 VARCHAR(50),
ADD COLUMN interest2 VARCHAR(50),
ADD COLUMN interest3 VARCHAR(50),
ADD COLUMN interest4 VARCHAR(50); \\ Создание новых столбцов

UPDATE my_contacts
SET interest1 = SUBSTRING_INDEX(interests, ',', 1 ); \\ Вырезает первое слово до запятой и помещает в столбец interest1

UPDATE my_contacts
SET interests = TRIM(RIGHT(interests,(LENGTH(interests) - LENGTH(interest1) - 1))); \\ Убирает пробелы с левой стороны. Вычитает правую сторону текущего из прошлого значения учитывая -1 на запятую.

UPDATE interest_test
SET interest = SUBSTR(interest, LENGTH(interest1)+2); \\ Функция SUBSTR получает текст столбца interest и возвращает его заданную часть. Мы выделяем символы, которые были скопированы в interest1, а так же еще два символа (запятая и пробел).

INSERT INTO profession (profession)
	SELECT profession FROM my_contacts \\ Заполнение столбца profession таблицы profession значениями, выдаваемыми командой SELECT.
	GROUP BY profession
	ORDER BY profession;

INSERT INTO test_product (product)
	SELECT sku FROM `brusnika.store`.oc_product WHERE sku LIKE 'Giorgio%'; \\ Заполнение столбца таблицы с помощью поиска нужного значения.
---------------------------------------------------------------------------------------------------------------------------	

CREATE TABLE profession
(
	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY 
	profession VARCHAR(20) 
)AS
	SELECT profession FROM my_contacts
	GROUP BY profession
	ORDER BY profession; \\ Создание таблицы profession с первичным ключом и столбцом profession который немедленно заполняется данными с помощью SELECT
---------------------------------------------------------------------------------------------------------------------------	

SELECT product.product_id, description.description \\ Внутреннее соединение по условию двух разных таблиц.
FROM oc_product AS product
INNER JOIN
oc_product_description AS description
ON product.product_id = description.product_id;
---------------------------------------------------------------------------------------------------------------------------	

SELECT product.sku, product.price, description.name, description.description 
FROM oc_product AS product
NATURAL JOIN
oc_product_description AS description; \\ Натуральное соединение определяющие равные столбцы (если совпадают имена) в следствие выводит все командой SELECT. 
---------------------------------------------------------------------------------------------------------------------------	

SELECT mc.last_name, mc.first_name, mc.phone
FROM my_contacts AS mc
	NATURAL JOIN
	job_desired AS jd
WHERE jd.title = 'Веб-разработчик'
AND jd.salary_low <105000; \\ Натуральное соединение с заданными условиями.

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURAL JOIN my_contacts AS mc
WHERE 
jc.title IN ('Веб-дизайнер', 'Веб-разработчик','Официант'); \\ Натуральное соединение с условием которые выбирает значения в скобках. 	
SELECT some_column, another_column
FROM table
WHERE column = (SELECT column FROM table); \\ Образец составления подзапроса.

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURAL JOIN my_contacts AS mc
WHERE 
jc.title IN ('SELECT title FROM job_listings'); \\ Натуральное соединение... Но теперь используется внешний запрос, в котором находиться внутренний.
---------------------------------------------------------------------------------------------------------------------------	

SELECT mc.first_name, mc.last_name, jc.salary
FROM my_contacts AS mc NATURAL JOIN job_current AS jc
WHERE jc.salary = 
(SELECT MAX (jc.salary) FROM job_current AS jc); \\ Натуральное соединение с таблицей job_current и использование функции MAX для нахождения максимального значения в столбце jc.salary.

SELECT dr.drink_name, dr.cost, dr.carbs, easy_drinks.amount2
FROM drink_info AS dr
NATURAL JOIN
easy_drinks
WHERE easy_drinks.amount2= (SELECT MAX(easy_drinks.amount2) FROM easy_drinks); \\ Тоже самое что и запрос выше, только переписан на мой лад.

---------------------------------------------------------------------------------------------------------------------------	

SELECT mc.first_name, mc.last_name,
(SELECT state 
FROM zip_code
WHERE mc.zip_code = zip_code) AS state
FROM my_contacts AS mc; \\ Определение псевдонима для столбца state
---------------------------------------------------------------------------------------------------------------------------	

SELECT mc.first_name, mc.last_name, jc.salary
FROM
my_contacts AS mc NATURAL JOIN job_current AS jc
WHERE
	jc.salary > (SELECT jc.salary
	FROM my_contacts mc NATURAL JOIN job_current jc
	WHERE email = 'andy@weather.com'); \\ Пример подзапроса для сравнения зарплаты

SELECT dr.drink_name, dr.cost, dr.carbs, dr.calories, ea.amount2
FROM drink_info AS dr
NATURAL JOIN
easy_drinks AS ea
WHERE ea.amount2 > (SELECT ea.amount2 FROM easy_drinks AS ea NATURAL JOIN drink_info AS dr WHERE ea.amount2 = 5.00 ); \\Мой пример использования   джойна в подзапросе.
--------------------------------------------------------------------------------------------------------------------------- 
SELECT mc.first_name, mc.last_name
FROM my_contacts AS mc
WHERE 
3 = (SELECT COUNT (*) FROM contact_interest 
WHERE contact_id=mc.contact_id ));
---------------------------------------------------------------------------------------------------------------------------
SELECT mc.first_name first_name, mc.last_name lastname, mc.email email
FROM my_contacts mc
WHERE NOT EXISTS
(SELECT * FROM job_current jc 
WHERE mc.contact_id = jc.contact_id); \\ Запрос с NOT EXISTS находит имена, фамилии и адреса электронной почты тех людей
из таблицы my_contacts, которые в настоящее время не представлены в таблице job_current.
---------------------------------------------------------------------------------------------------------------------------
SELECT last_name, first_name FROM my_contacts
WHERE zip_code IN (SELECT mc.zip_code FROM my_contacts AS mc)
NATURAL JOIN job_current AS js
WHERE jc.salary = (SELECT MAX(salary) FROM job_current)); \\ Выводит список людей, у которых почтовый индекс (zip_code) совпадает с почтовым индексом человека, получающего максимальную зарплату.
---------------------------------------------------------------------------------------------------------------------------
SELECT mc.email FROM my_contacts mc WHERE
EXISTS
(SELECT * FROM contact_interest ci WHERE mc.contact_ID = ci.contact_ID)
AND
NOT EXISTS
(SELECT * FROM job_current js
WHERE mc.contact_id = jc.contact_id);
---------------------------------------------------------------------------------------------------------------------------
SELECT g.girl, t.toy 
FROM girls g
LEFT OUTER JOIN toys t
ON g.toy_id = t.toy_id; \\ Левое внешнее соединение выбирает с таблица girls столбцы girl и toy по условию если g.toy_id = t.toy_id

SELECT clowns.name, location.location 
,(SELECT clowns.description.gender FROM clowns.description WHERE clowns.id = clowns.description.id) AS gender
FROM clowns.clown_info AS clowns
LEFT OUTER JOIN clowns.location AS location
ON clowns.id = location.location_id; \ Левое внешнее соединение с подзапросом.
---------------------------------------------------------------------------------------------------------------------------
SELECT c.name, cp.name AS boss, c.boss_id
FROM clowns.clown_info AS c
INNER JOIN clowns.clown_info_copy AS cp
WHERE c.boss_id = cp.id; \\ Внутреннее соединение столбцов по принадлежности с выводом их имен.

SELECT c1.name, c2.name, c2.boss_id
FROM clowns.clown_info AS c1
INNER JOIN clowns.clown_info AS c2
WHERE c2.boss_id = c1.id; \\ Тот же самый вывод только с использованием самосоединения.
---------------------------------------------------------------------------------------------------------------------------
SELECT name FROM clowns.clown_info
UNION ALL
SELECT activity FROM clowns.activities; \\ Объдиняет два запроса в один столбик и показывает дубликаты.
---------------------------------------------------------------------------------------------------------------------------
CREATE TABLE my_union AS
SELECT clowns.clown_info.name FROM clowns.clown_info
UNION ALL
SELECT clowns.activities.activity FROM clowns.activities;
; \\ Создание таблицы на основе UNION с последующим просмотром типа данных.
---------------------------------------------------------------------------------------------------------------------------
SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURAL JOIN my_contacts AS mc
WHERE jc.title IN (SELECT title FROM job_listings); \\ Использование подзапроса.

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURAL JOIN my_contacts AS mc
{INNER JOIN job_listings j1
ON jc.title = j1.title;} \\ Замена подзапроса на соединение.
---------------------------------------------------------------------------------------------------------------------------
SELECT title FROM job_listings WHERE salary = (SELECT MAX(salary) FROM job_listings); \\ Запрос до оптимизации

SELECT title FROM job_listings
ORDER BY salary DESC LIMIT 1; \\ Запрос после оптимизации.
---------------------------------------------------------------------------------------------------------------------------
SELECT c1.name, c2.name, c2.boss_id (SELECT clowns.clown_info ) AS c2
FROM clowns.clown_info AS c1
INNER JOIN  AS c2
WHERE c2.boss_id = c1.id; \\ Самосоединения в подзапросе в начале SELECT (не дописано).
---------------------------------------------------------------------------------------------------------------------------
CREATE TABLE piggy_bank
(
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  coin CHAR(1) CHECK (coin IN ('P','N','D','Q'))
) \\ Определяет значения в новой таблице, которые должны быть равно при вставке иначе показывает сообщение об ошибке.

ALTER TABLE my_contacts
ADD CONSTRAINT CHECK gender IN ('М','Ж'); \\ Изменяет таблицу и ставит ограниченае на ввод в столбец gender. Только значения М,Ж.
---------------------------------------------------------------------------------------------------------------------------
CREATE VIEW boss AS
SELECT c1.name
FROM clowns.clown_info AS c1; \\ Создание представления.

SELECT * FROM boss; \\ Использование представления.

CREATE VIEW pb_dimes AS SELECT * FROM piggy_bank WHERE coin = 'D' WITH CHECK OPTION; \\ Проверка условия. Данные, вводимые в представление, проверются по условию WHERE перед вставкой.

SHOW TABLES \ Вывод таблиц и представлений.
DESC \\ Показ представления.
DROP VIEW boss \\ Удаление представления.
---------------------------------------------------------------------------------------------------------------------------
CREATE VIEW job_raises AS
SELECT mc.first_name, mc.last_name, mc.email, mc.phone, jc.contact_id, jc.salary, jd.salary_low, jd.salary_low-jc.salary AS raise
FROM job_current AS jc
INNER JOIN job_desired jd
INNER JOIN my_contacts AS mc
WHERE jc.contact_id = jd.contact_id
AND jc.contact_id = mc.contact_id; \\ Использование двух соединений, вычитание разницы двух столбцов и использование представления.
---------------------------------------------------------------------------------------------------------------------------
START TRANSACTION;
SELECT * FROM piggy_bank;
UPDATE piggy_bank set coin = 'Q' where coin = 'P';
SELECT * FROM piggy_bank; \\ Здесь изменения еще видны
ROLLBACK;
SELECT * FROM piggy_bank \\ А здесь их уже нет.