пятница, 13 января 2012 г.

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. Найти книги, в соавторстве которых принимали участие авторы: Иванов и Петров (вместе)

31 комментарий:

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

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

    Это точно правильно? :)

    ОтветитьУдалить
  2. Ольга, так как правильно? Вопрос к предыдущему комменту.

    ОтветитьУдалить
  3. Хочу сказать спасибо Вам огромное за серию статей "Как изучить основы SQL за 2 дня"!! Я первый раз читаю про SQL, а у Вас все просто и доступно, и пошагово расписано! Благодарю Вас за такую полезную инфо для новичков)

    ОтветитьУдалить
    Ответы
    1. Карина, пожалуйста! Рада, что статьи вам помогли )))

      Удалить
    2. Карина, неистово рекомендую приложение для Android LearnSQL - очень эффективная форма обучения, ознакомление с SQL происходит на лету. Если что - я не рекламирую апп, я просто им восхищаюсь :)

      Удалить
    3. Карина, неистово рекомендую приложение для Android LearnSQL - очень эффективная форма обучения, ознакомление с SQL происходит на лету. Если что - я не рекламирую апп, я просто им восхищаюсь :)

      Удалить
  4. Уточните, пожалуйста, по комменту выше, какие области захватывает? с желтой или без?

    ОтветитьУдалить
  5. Вместо cross join в статье должно быть full join. Его результат как раз объединение left join и right join.
    a cross join b on - такой конструкции вообще нет, есть просто a cross join b без on.
    cross join - это декартово произведение двух множеств.

    ОтветитьУдалить
  6. Прошу! напишите пожалуйста код к этому запросу - " Найти авторов, которые имеют свои и участвовали в соавторстве хотя бы одной книги". Хочу разобраться, но никак не выходит.. понимаю что надо через вложенный селект..начинаю с внутреннего где нахожу произведения имеющие более одного автора, потом хочу вывести фио и задать внешний селект для этих авторов , чтобы у каждого было более 1 книги...но застряла на выводе фио...

    ОтветитьУдалить
    Ответы
    1. Расскажите, что пробовали, какие результаты получили, какие выводы сделали :)

      Удалить
    2. Ок. Как вижу это я( может это не с той степи...но) - при группировке и подсчете autorId для каждого произведения мы выбираем те у которых autorId>1 = и это указывает на то, что у произведения 2 автора. Следовательно, если у именно этих определенных авторов подсчитать bookId для каждого из них и выбрать те у которых bookId>1 - то и удовлетвориться условие и для участия в соавторстве и написании своих отдельных произведений.
      select b.id,b.name , count(BA.autorId)
      from dbo.BookAutorship BA
      join dbo.Book b on b.id = BA.bookId
      join dbo.Autor a on a.id = BA.autorId
      group by b.id , b.name
      having count (BA.autorId) >1
      Так выводятся произведения у которых более одного автора
      но именно произведения...а мне надо чтобы выходили имя и фамилия, авторов этих произведений..чтобы задать внешний селект...
      или я вообще не с того ракурса все это вижу..

      Удалить
    3. Все. Я сделала. Но мне кажется - какой-то странный "маленький" код получится...
      select a.firstName , a.lastName , count (BA.bookId) as Num_Cr from dbo.BookAutorship BA
      join dbo.Book b on b.id = BA.bookId
      join dbo.Autor a on a.id = BA.autorId
      join
      (select b.id, b.name , BA.autorId from
      (dbo.BookAutorship BA
      join dbo.Book b on b.id = BA.bookId
      join dbo.Autor a on a.id = BA.autorId
      join (select b.id, b.name , count(BA.autorId) as Num_Writ
      from dbo.BookAutorship BA
      join dbo.Book b on b.id = BA.bookId
      join dbo.Autor a on a.id = BA.autorId
      group by b.id , b.name
      having count (BA.autorId) >1) p on p.id = BA.bookId)
      group by b.id , b.name , BA.autorId ) f on f.autorId = a.id
      group by a.firstName , a.lastName
      having count (BA.bookId) >1

      Удалить
    4. может кто-то знает путь покороче..

      Удалить
    5. Да, код получился неслабый))) Но с ним лучше прийти на форум - http://software-testing.ru/forum/. Я постараюсь не забыть попробовать у себя, но не обещаю)) Базы такой у меня давно нет, надо подготовить дамп на будущее будет)))

      Удалить
  7. По последнему запросу. Jana, он вы водит как авторов так и соавторов (у которых больше одной книги). Я думаю нужно ограничиться только авторами. У меня такой вариант:

    SELECT a.firstName AS 'First Name', a.lastName AS 'Last Name', ba.qty_books as '# of Books'
    FROM (
    SELECT authorId, COUNT(*) as qty_books
    FROM BooksAutorship
    GROUP BY authorId, isAuthor
    HAVING isAuthor = 1 AND COUNT(*) > 1
    ) ba
    INNER JOIN Author a
    ON ba.authorId = a.id;

    ОтветитьУдалить
  8. По последнему запросу Jana:

    SELECT firstName, lastName
    FROM
    Author
    WHERE
    id = (SELECT author.id
    FROM
    (SELECT author.id FROM
    BookAuthorship
    WHERE
    isAuthor = TRUE) a
    INNER JOIN
    (SELECT author.id FROM
    BookAuthorship
    WHERE
    isAuthor = FALSE) b
    ON
    a.author.id = b.author.id);

    ОтветитьУдалить
  9. Всё очень просто, нужно выбрать из таблицы BookAuthorship пересечение множеств тех людей, которые являются авторами книг и тех, которые являются соавторами книг. Дальше выводим firstName и lastName из таблицы Author тех, чьи id находятся в этом пересечении множеств.

    ОтветитьУдалить
  10. Я не понял, как заполнять таблицу BookAuthorship, объясните пожалуйста!

    ОтветитьУдалить
    Ответы
    1. Если речь про инструмент из статьи, то тут вроде все написано. Если про другой — то гуглите "insert" команду

      Удалить
    2. Я просто не понял колонку isAuthor. Я сам ее заполняю? И когда там 1, а когда 0

      Удалить
  11. Объясните в поле isAuthor что попадает? Откуда данные?

    ОтветитьУдалить
    Ответы
    1. Эта колонка была в предыдущей статье о создании базы и таблиц.

      Удалить
  12. статья супер, спасибо большое!

    ОтветитьУдалить
  13. Ольга спасибо за подробное освещение тематики. На просторах нэта ваш ресурс один из .. достоин внимания. Продолжайте в таком же духе, Вы Молодец.

    ОтветитьУдалить