SQL - join it! Запросы к БД

Итак, диаграмму Базы Данных мы создали.

Теперь надо к ней как-то обратиться. А прежде чем обращаться к БД, надо ее заполнить. Мы создали таблицы, но не заполнили их.

Исправляем недочет! Раскрываем свою базу данных - Tables - dbo.Author - правый щелчок - редактировать первые 200 значений.

Заполняем Фамилию и Имя, колонка id генерируется сама (так как мы все такие колонки сделали автоинкрементными)

По аналогии заполняем остальные таблицы - Book, BookAuthorship, Author.

Потом создаем запрос. File - New Query. Или нажимаем на кнопку в вытащенном меню

Открывается пустая страница.
Что мы хотим? Хотим вытащить все колонки из таблицы "Book".

Ок, пишем:

select * from dbo.Book

И нажимаем "! Execute".  Именно "Execute", а не "Debug" (зеленый треугольник рядышком)!

Снизу высветился результат - табличка "Book" со всеми колонками и всеми введенными нами значениями.

Что мы, вообще говоря, записали?

select - выбрать\показать (некие колонки из некой таблицы)
* - после select мы перечисляем названия колонок, которые мы хотим вывести. Символ "*" означает "вывести все".
from - откуда будем выбирать, после него указывается название таблиц, данные из которых мы хотим получить.

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

Давайте выведем все колонки таблиц "книги" и "авторы", каждой присвоив краткое название

Результатом стала таблица, склеенная из всех колонок выбранных нами таблиц.

Теперь мы хотим отсортировать полученные значения.
Для этого используется команда "order by".

Давайте выведем несколько колонок из таблицы авторов, отсортировав их вначале по имени автора, а потом - по его id. У нас ведь может быть несколько авторов с одним именем. Вот мы и показываем, как будет идти сортировка внутри сортировки.

Изначально сортировка идет по возрастанию. Чтобы сделать сортировку по убыванию, после названия колонки, по которой идет сортировка, записываем оператор "desc".

Сделаем сортировку по автору - по убыванию, а по id оставим по возрастанию

Следующий оператор - "where" - записывается после "select  from", делает ограничение на выборку значений. Пишется в виде:

where *Название колонки* > 0 - для числовых типов данных можем делать любые неравенства
where *Название колонки* in (int1, int 2) - где значение ячейки выбранной колонки или int1 или int 2. Может быть числом или строкой. Строки берем в одинарные кавычки.
where *Название колонки* like 'строка' - где значение ячейки выбранной колонки содержит строку. Один символ можно заменить "_", много любых символов - "%".

Давайте сделаем выборку по авторам, имена которых начинаются с буквы "Ю".
 

А теперь самое интересное - join, объединение данных.

Какие вообще команды мы используем для объединения множеств?
Пусть у нас есть два множества - бежевое a, голубое b.

Запишем возможные варианты их объединения:

·         from a inner join b on - пересечение областей, выбрана будет желтая часть картинки

·         from a left  join b on - левая область, выбраны будут желтая и бежевая части картинки

·         from a right  join b on - правая область, выбраны будут желтая и голубая части картинки

·         from a cross join b on - обе области, выбраны будут все части картинки.

Казалось бы, зачем нам вообще left  и right, ведь выбраны там будут просто все множество а или b, зачем писать через join? На самом деле это нам нужно, чтобы посмотреть не просто множество, а все связи выбранного множества со вторым множеством. И даже если их нет, мы увидим "null", но будем знать, что пересечений в этих ячейках нет.

Давайте сравним inner и left. 

Выведем весь список жанров, по которым написаны книги.

А теперь выведем вообще весь список жанров, несмотря на то, написаны ли по ним книги

Заодно мы увидели, зачем использовать краткие названия таблиц. Чтобы при записи условий не писать "полноеИмяТаблицы.названиеКолонки", записать через краткое. При использовании двух и более таблиц нам также необходимо давать им имена. Колонки в разных таблицах могут называться одинаково, чтобы вывести их в запросе, надо указывать вначале, к какой таблице принадлежит выбранная колонка.

Отлично, находить данные в БД мы научились. Но ведь мы хотим уметь их изменять! Рассмотрим необходимые для этого команды.

Для добавления записи в таблицу пишем:

insert into имяТаблицы (колонка1, колонка2) values ('значение1', 'значение2')

Добавим нового автора в табличку.

Видим запись "1 строка изменена". Для добавления нескольких записей пишем несколько команд insert.
Убедимся, что запись действительно добавлена, создадим новый запрос и выведем таблицу авторов.

Для изменения уже имеющейся записи пишем такой набор команд:

update имяТаблицы set параметр1= 'Значение1', параметр2= 'Значение2'

where условие

Давайте изменим ФИО автора

Проверим в таблице:

Тут надо учесть, что в таблице могут быть и другие Пети, поэтому, если бы мы оставили условие только по имени, то заменили бы не только запись с id = 8, но и запись с id = 6. Поэтому надо указывать более точные данные изменяемого\удаляемого значения. Или связку имени - фамилии, или уникальный идентификатор, который мы заранее узнаем в другом запросе.

Ну и, наконец, мы хотим уметь записи удалять! Нужная нам команда:

delete from имяТаблицы where условие

Давайте удалим нового Автора из таблицы.

Проверим, что запись удалилась.

Подведем итоги.

Список команд, которые надо знать для работы с Базой Данных:

select   – выбрать.

from – таблицы, из которых идет выборка.

order by – отсортировать по возрастанию.

order by desc – отсортировать по убыванию.

where – ограничение на выборку, можно использовать >, <, «равно».

where  in (перечисление) – где выбранное значение – одно из значений из перечисленного множества.

where  like ‘часть строки%’ – где выбранное значение содержит строку, «%» - любые символы, «_» - один любой символ.

·         from a inner join b on - пересечение областей

·         from a left  join b on - левая область

·         from a right  join b on - правая область

·         from a cross join b on - обе области

insert into имяТаблицы (колонка1, колонка2) values ('значение1', 'значение2')

update имяТаблицы set параметр1= 'Значение1', параметр2= 'Значение2' where условие

delete from имяТаблицы where условие

Ну и вопросы для саморазвития. Ведь, пока сам не сделаешь что-то, до конца не поймешь, как оно работает.

Для разработанной БД написать след. запросы:

1. Найти авторов, которые написали более чем одну книгу
2. Найти авторов, которые имеют свои и участвовали в соавторстве хотя бы одной книги
3. Найти соавторов, которые не написали ни одной своей книги
4. Найти книги, в соавторстве которых принимали участие авторы: Иванов и Петров (вместе)