Лабораторная работа № 4 (1). Лабораторная работа Многотабличные запросы Задания. Для заданной предметной области создать
 Скачать 0.54 Mb.
|
|
Лабораторная работа № 4. Многотабличные запросы Задания. Для заданной предметной области создать: 1. многотабличные запросы на выборку с использованием объединения; 2. запросы с использованием строковых, числовых и итоговых функций. Используя функции даты и времени, создать запросы для формирования отчетных данных; 3. представления и выполните к ним запросы. В реальных приложениях часто требуется использовать сразу несколько таблиц БД. Запросы, которые обращаются одновременно к нескольким таблицам, называются многотабличными или сложными запросами. Предположим, мы хотим узнать, какие темы, и какими авторами были созданы. Для этого проще всего обратиться к таблице Темы (topics): Но что, если нам необходимо, чтобы в ответе на запрос были не идентификаторы авторов, а их имена? Вложенные запросы нам не помогут, т.к. в конечном итоге они выдают данные из одной таблицы. А нам надо получить данные из двух таблиц (Темы и Пользователи) и объединить их в одну. Запросы, которые позволяют это сделать, в SQL называются объединениями. Синтаксис самого простого объединения следующий: SELECT имена_столбцов_таблицы_1, имена_столбцов_таблицы_2 FROM имя_таблицы_1, имя_таблицы_2; Создадим простое объединение: Такое объединение называется декартовым произведением, когда каждой строке первой таблицы ставится в соответствие каждая строка второй таблицы. Такое объединение не имеет практического смысла. Чтобы результирующая таблица содержала те данные, которые нам нужны, необходимо указать условие объединения. Свяжем наши таблицы по идентификатору автора (условие объединения). Запрос выведет только те строки, в которых значения поля id_author таблицы topics совпадают со значениями поля id_user таблицы users: Объяснение приведено на схеме: Т.е. мы в запросе сделали следующее условие: если в обеих таблицах есть одинаковые идентификаторы, то строки с этим идентификатором необходимо объединить в одну результирующую строку. Обратите внимание на две вещи: – если в одной из объединяемых таблиц есть строка с идентификатором, которого нет в другой объединяемой таблице, то в результирующей таблице строки с таким идентификатором не будет. В нашем примере есть пользователь Oleg (id=5), но он не создавал тем, поэтому в результате запроса его нет; – при указании условия название столбца пишется после названия таблицы, в которой этот столбец находится (через точку). Это сделано во избежание путаницы, ведь столбцы в разных таблицах могут иметь одинаковые названия, и MySQL может не понять, о каких конкретно столбцах идет речь. Вообще, корректный синтаксис объединения с условием выглядит так: SELECT имя_таблицы_1.имя_столбца1_таблицы_1, имя_таблицы_1.имя_столбца2_таблицы_1, имя_таблицы_2.имя_столбца1_таблицы_2, имя_таблицы_2.имя_столбца2_таблицы_2 FROM имя_таблицы_1, имя_таблицы_2 WHERE имя_таблицы_1.имя_столбца_по_которому_объединяем = имя_таблицы_2.имя_столбца_по_которому_объединяем; Если имя столбца уникально, то название таблицы можно опустить (как мы делали в примере), но делать это не рекомендуется. Объединения дают возможность выбирать любую информацию из любых таблиц, причем объединяемых таблиц может быть и три, и четыре, и условие для объединения может быть не одно. Для примера создадим запрос, который покажет все сообщения, к каким темам они относятся и авторов этих сообщений. Данная информация хранится в таблице Сообщения (posts): Но чтобы вместо идентификаторов отображались имена и названия, необходимо объединить три таблицы: Выполнено объединение таблиц Сообщения и Пользователи условием posts.id_author=users.id_user, а таблицы Сообщения и Темы – условием posts.id_topic=topics.id_topic. Объединения, которые были рассмотрены, называются внутренними объединениями. Такие объединения связывают строки одной таблицы со строками другой таблицы (а может еще и третьей таблицы). Но бывают ситуации, когда необходимо, чтобы в результат были включены строки, не имеющие связанных. Например, когда мы создавали запрос, какие темы и какими авторами были созданы, пользователь Oleg в результирующую таблицу не попал, т.к. тем не создавал, а потому и связанной строки в объединяемой таблице не имел. Поэтому, если потребуется составить несколько иной запрос – вывести всех пользователей и темы, которые они создавали, если таковые имеются – то придется воспользоваться внешним объединением, позволяющим выводить все строки одной таблицы и имеющиеся связанные с ними строки из другой таблицы. Объединение таблиц (внешнее объединение) Синтаксис для внешнего объединения следующий: SELECT имя_таблицы_1.имя_столбца, имя_таблицы_2.имя_столбца FROM имя_таблицы_1 ТИП ОБЪЕДИНЕНИЯ имя_таблицы_2 ON условие_объединения; где ТИП ОБЪЕДИНЕНИЯ - либо LEFT OUTER JOIN, либо RIGHT OUTER JOIN. Итак, надо вывести всех пользователей и темы, которые они создавали, если таковые имеются. Если мы воспользуемся внутренним объединением, рассмотренным ранее, то получим в итоге следующее: Т.е. в результирующей таблице есть только те пользователи, которые создавали темы. Но необходимо, чтобы выводились все имена. Для этого немного изменим запрос: SELECT users.name, topics.topic_name FROM users LEFT OUTER JOIN topics ON users.id_user=topics.id_author; Получаем нужный результат – все пользователи и темы, ими созданные. Если пользователь не создавал тему, но в соответствующем столбце стоит значение NULL. Итак, мы добавили в запрос ключевое слово – LEFT OUTER JOIN, указав тем самым, что из таблицы слева надо взять все строки, и поменяли ключевое слово WHERE на ON. Кроме ключевого слова LEFT OUTER JOIN может быть использовано ключевое слово RIGHT OUTER JOIN. Тогда будут выбираться все строки из правой таблицы и имеющиеся связанные с ними из левой таблицы. И наконец, возможно полное внешнее объединение, которое извлечет все строки из обеих таблиц и свяжет между собой те, которые могут быть связаны. Ключевое слово для полного внешнего объединения – FULL OUTER JOIN. Примем правостороннего объединения: Как видно, теперь есть все темы (все строки из правой таблицы), а вот пользователи только те, которые темы создавали (т.е. из левой таблицы выбираются только те строки, которые связаны с правой таблицей). |