 |
Левое внешнее соединение. Правое внешнее соединение. Урок 15: Еще фишки и подзапросы
|
|
|
|
Левое внешнее соединение
Выведем табличку books и publishers.
| mysql> SELECT books. book_name, publishers. publisher_name FROM books LEFT JOIN publishers ON books. book_id=publishers. publisher_id; ИЛИ mysql> SELECT books. book_name, publishers. publisher_name FROM books LEFT JOIN publishers ON USING(publisher_id); | LEFT – то, что стоит до LEFT JOIN – то лево, то что стоит после LEFT JOIN – это право. LEFT – мы берем books и сравниваем записи books с таблицей правой. RIGHT – мы берем publishers и все ее записи сравниваем с books по publisher_id |
| book_name | publisher_name |
| Рассказ о цветах | Все книги |
| Физика просто | Прокачай мозги |
| Магия и кровь | Мир фантазий |
| Уникальная вселенная | NULL |
И небольшой эксперимент с поиском NULL
| mysql> SELECT books. book_name, publishers. publisher_name FROM books LEFT JOIN publishers ON books. publisher_id=publishers. publisher_id WHERE books. publisher_id IS NULL; |
| book_name | publisher_name |
| Уникальная вселенная | NULL |
Правое внешнее соединение
| mysql> SELECT books. book_name, publishers. publisher_name FROM books RIGHT JOIN publishers USING(publisher_id); | LEFT – то, что стоит до LEFT JOIN – то лево, то что стоит после LEFT JOIN – это право. LEFT – мы берем books и сравниваем записи books с таблицей правой. RIGHT – мы берем publishers и все ее записи сравниваем с books по publisher_id |
| book_name | publisher_name |
| Магия и кровь | Мир фантазий |
| Физика просто | Прокачай мозги |
| Рассказ о цветах | Все книги |
Урок 15: Еще фишки и подзапросы
Сейчас будет простая тема.
Первым делом, до того, как мы с вами начнем изучать подзапросы, мы с вами попробуем используя соединения, вывести информацию более чем из двух таблиц. Сейчас вы увидите, как это делается. Воспользуемся таблицами из прошлого урока + создадим свою.
| mysql> SHOW TABLES; | Выведем таблицы в нашей БД |
| Tables_in_books_db |
| books |
| publishers |
|
|
|
| mysql> CREATE TABLE reviews ( review_id INT PRIMARY KEY, review_rank INT, review_comment VARCHAR(50) NOT NULL DEFAULT 'Нет комментария', FOREIGN KEY(review_id) REFERENCES books(book_id) ); | по поводу review_id: мы не добавили AUTO_INCREMENT, т. к. дело в том, что у нас будет связь 1 к 1 и review_id будет относиться только к одной книге. review_rank – оценка по 5 бальной шкале |
Подзапрос – это когда мы должны получить какой-то вывод внутри уже существующего запроса.
Мы хотим получить id какой-то книжки, например. Но при этом мы точно не помним id этой книжки, но мы знаем ее имя. И вот тут мы можем использовать подзапрос. Но прежде чем, мы его используем, давайте я выведу табличку, чтобы все видеть.
NB: в подзапросах нельзя использовать «*», потому что в большинстве случаев подзапрос должен вернуть только одну запись.
| book_id | book_name | price | publisher_id |
| Магия и кровь | |||
| Уникальная вселенная | NULL | ||
| Физика просто | |||
| Рассказ о цветах |
| mysql> INSERT INTO reviews VALUES ( (SELECT book_id FROM books WHERE book_name='Магия и кровь'), 8, 'Красочный битвы и хороший сюжет' ); mysql> INSERT INTO reviews VALUES ( (SELECT book_id FROM books WHERE book_name='Уникальная вселенная'), 9, 'Невероятная книга. Просто фантастика! ' ); mysql> INSERT INTO reviews VALUES ( (SELECT book_id FROM books WHERE book_name='Физика просто'), 3, 'Нифига не просто' ); mysql> INSERT INTO reviews VALUES ( (SELECT book_id FROM books WHERE book_name='Рассказ о цветах'), 8, 'Ну средненько так' ); | Внутренний подзапрос |
| mysql> SELECT b. book_name AS Книга, r. review_rank AS Оценка, r. review_comment, b. price AS Цена, p. publisher AS Издатель FROM books b NATURAL JOIN publishers p INNER JOIN reviews r ON b. book_id=r. review_id; | Теперь давайте соединим три таблицы после ON надо указать условие, как мы соединяем таблицы. |
| Книга | Оценка | review_comment | Цена | Издатель |
| Рассказ о цветах | Ну средненько так | Все книги | ||
| Физика просто | Нифига не просто | Прокачай мозги | ||
| Магия и кровь | Красочные битвы и хороший сюжет | Мир фантазий |
|
|
|
| mysql> SELECT b. book_name Книга, r. review_rank Оценка, b. price Цена, p. publisher_name Издатель FROM books b NATURAL JOIN publishers p INNER JOIN reviews r ON b. book_id=r. review_id WHERE b. price> 50; |
| Книга | Оценка | Цена | Издатель |
| Рассказ о цветах | Все книги | ||
| Физика просто | Прокачай мозги | ||
| Магия и кровь | Мир фантазий |
Далее давайте так:
| mysql> SELECT b. book_name Книга, r. review_rank Оценка, b. price Цена, p. publisher_name Издатель FROM books b NATURAL JOIN publishers p INNER JOIN reviews r ON b. book_id=r. review_id WHERE r. review_rank IN(SELECT r. review_rank FROM reviews); | IN(SELECT r. review_rank FROM reviews) – то же самое что и IN(5, 3, 8) IN позволяет указывать возможные значения |
| Книга | Оценка | Цена | Издатель |
| Рассказ о цветах | Все книги | ||
| Физика просто | Прокачай мозги | ||
| Магия и кровь | Мир фантазий |
Итого мы получили точно такой же вывод: Хитрость заключается в том, что он берет и вставляет сюда 3 вывода:
- вывод с 5
- вывод с 3
- вывод с 8
SELECT
u. Units_№Unit AS Номер_агрегата,
u. Units_idCode_of_unit AS Шифр_агрегата,
(SELECT Name_of_company FROM organisations WHERE idOrganisation = u. idOrganisation) AS Организация,
u. Units_idNumber_of_engine AS Номер_двигателя,
u. Units_Date_of_unit_creation AS Дата_создания_агрегата,
u. Units_First_defect_appearence_since_using_of_unit AS СНЭ_агрегата,
(SELECT Type_of_defect FROM types_of_defect WHERE idType_of_defect = d. idType_of_defect) AS Тип_дефекта,
d. Description_of_defect AS Описание_дефекта
FROM defects d
INNER JOIN units_has_defects uhd ON d. idDefect = uhd. Defects_idDefect
INNER JOIN units u ON (
uhd. Units_Units_idCode_of_unit = u. Units_idCode_of_unit AND
uhd. Units_Units_idNumber_of_engine = u. Units_idNumber_of_engine AND
uhd. Units_Units_№Unit = u. Units_№Unit AND
uhd. Units_Units_Type_of_units_Manufacturer_organisation_of_unit = u. idOrganisation
);
|
|
|
