бд. метод указ Проектирование БД. Методическое указания для выполнение лабораторных работ по дисциплине
 Скачать 0.74 Mb.
|
|
3 Методические указания к выполнению лабораторной работы 3.1 Извлечение информации из таблиц Запрос к базе данных представляет собой операцию выборки, которая сужает диапазон считываемой информации и ограничивает ее столбцами и строками, соответствующими заданным критериям. Для выборки данных в Transact-SQL используется инструкция SELECT. Большинство реальных запросов SQL предназначено для выборки не всех, а определенных строк и столбцов таблиц. Инструкция SELECT Извлекает строки из базы данных и позволяет выбирать одну или несколько строк или столбцов в одной или нескольких таблицах. Это основной структурный компонент SQL, используемый для отправки запросов. Инструкция SELECT не изменяет, не вставляет и не удаляет данные. Синтаксис SELECT select_list [ FROM table_source ] [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] Замечания Инструкция SELECT описывает запрос к системе. Выполнение запроса не обновляет данные. Результатом запроса является таблица идентично структурированных строк, в каждой из которых содержится одинаковый набор столбцов. Инструкция SELECT однозначно определяет, какие столбцы будут существовать в этой таблице результатов и какие строки ее заполнят. Инструкция SELECT не сообщает системе последовательность выполнения запроса; система сама выполняет запрос оптимальным в данном случае способом, используя внутренний модуль оптимизации на основе сведений о затратах. Результат должен гарантированно отвечать описанной ниже канонической стратегии выполнения. Единственное различие может заключаться в порядке расположения строк в таблице, хотя любой другой порядок может быть задан предложением ORDER BY. Стратегия выполнения 1. Создайте объединение таблиц в предложении FROM. При использовании явного синтаксиса JOIN результат JOIN будет явным. Если в предложении FROM имеется список таблиц, разделенных запятыми, это неявное объединение таблиц векторным произведением. 2. При наличии предложения WHERE следует применять данные условия поиска к строкам, полученным после шага 1, и сохранять только те строки, которые удовлетворяют условию. 3. Если в предложении SELECT нет объединений, как нет и предложения GROUP BY, перейдите к шагу 7. 4. Если есть предложение GROUP BY, разделите строки, полученные после шага 2, на несколько групп так, чтобы у всех строк в каждой группе было одинаковое значение по всем столбцам группирования. Если предложения GROUP BY нет, поместите все строки в одну группу. 5. К каждой группе, полученной после шага 4, примените предложение HAVING, если таковое указано. Останутся только те группы, которые удовлетворят предложению HAVING. 6. Для каждой группы, полученной после шага 5, создайте одну строку результата путем оценки списка выбора в предложении SELECT в данной группе. 7. Если в предложении SELECT содержится ключевое слово DISTINCT, удалите все повторяющиеся строки, полученные в результате шага 6. Если есть предложение ORDER BY, выполните сортировку результатов шага 7, как указано выражением порядкаю SELECT _LIST Указывает столбцы, возвращаемые запросом. Синтаксис SELECT [ ALL | DISTINCT ] TOP (expression) < select_list > < select_list > ::= { * | { table_name | table_alias }.* | { column_name | expression } [ [ AS ] column_alias ] } [ ,...n ] Замечание: аргументы и ограничения рассматриваются в справке оператора SELECT Рассмотрим только аргумент column_alias - альтернативное имя для замены имени столбца в наборе результатов запроса. Например, для столбца «quantity» может быть указан псевдоним «Quantity», «Quantity to Date» или «Qty». Кроме того, псевдонимы используются для указания имен для результатов выражений, например: SELECT AVG(Stud_STIP) AS Средняя_стипендия FROM Students Примечание: column_alias можно использовать в предложении ORDER BY, но нельзя использовать в предложениях WHERE, GROUP BY и HAVING. Предложение FROM используется для указания таблиц для извлечения строк. Предложение FROM является необязательным. Примечание: возвращающие табличное значение функции не поддерживаются Синтаксис и аргументы рассматриваются в справке команды. [ FROM { < table_source > } [ ,...n ] < table_source > ::= table_name [ [ AS ] table_alias ] | < joined_table > | < joined_table > ::= < table_source > < join_type > < table_source > ON | | | ( < joined_table > ) < join_type > ::= [ INNER | { { LEFT | RIGHT } [ OUTER ] } ] JOIN ] left_table_source::= table_source right_table_source::=table_source Рассмотрим особенности применения аргумента JOIN: CROSS JOIN указывает векторное произведение двух таблиц. INNER указывает, что все совпадающие пары строк возвращены. Отмена несовпадающих строк из обеих таблиц. Задается по умолчанию, если тип объединения не указан. LEFT [ OUTER ] указывает, что все строки таблицы слева, которые не соответствуют указанному условию, включаются в результирующий набор в дополнение ко всем строкам, которые возвращаются внутренним объединением. Для выходных столбцов таблицы слева указано значение NULL. RIGHT [ OUTER ] указывает, что все строки таблицы справа, которые не соответствуют указанному условию, включаются в результирующий набор в дополнение ко всем строкам, которые возвращаются внутренним объединением. Для выходных столбцов таблицы справа указано значение NULL. JOIN показывает, что указанные таблицы следует объединить. ON < search_condition > указание условия, на котором основывается объединение. Условие может указать любой допустимый предикат, однако часто используются столбцы и операторы сравнения. Замечания: если тот же запрос может быть написан как с оператором JOIN, так и с ключевым словом APPLY, запрос с применением JOIN может оказаться быстрее; если в right_table_source есть ссылки на таблицы, не привязанные к таблицам, перечисленным в right_table_source, то необходимо либо привести в соответствие имя или псевдоним таблицы в left_table_source, либо привести в соответствие имя или псевдоним таблицы во внешнем предложении FROM (если во вложенном запросе в предложении WHERE или списке SELECT указан оператор APPLY). Если и в первом случае и во втором имеются совпадающие ссылки, первый имеет больший приоритет; операторы APPLY имеют тот же приоритет, что и операторы JOIN. При отсутствии скобок последовательность операторов JOIN и APPLY будет вычисляться слева направо. Дополнительные сведения см. в электронной документации по SQL Server в разделах «Использование предложения FROM» и «Использование APPLY». Примеры Следующий пример предоставляет больше сведений об использовании предложения FROM SELECT Teach_ID, Teach_FAM, Teach_IMYA FROM Teacher Использование простого предложения FROM SELECT Teach_ID, Teach_FAM, Teach_IMYA FROM Teacher ORDER BY Teach_ID Использование CROSS JOIN В следующем примере возвращается перекрестное произведение двух таблиц, Students и Group. Возвращается список всех возможных сочетаний строк Stud_ID и Group_ID: SELECT S.Stud_ID, G.Grup_Id FROM Students S CROSS JOIN Grup G ORDER BY S.Stud_ID, G.Grup_ID Использование LEFT OUTER JOIN Следующий пример соединяет две таблицы по столбцу Group_ID и сохраняет строки из левой таблицы, не имеющие соответствий. Таблица Students сравнивается с таблицей Group по столбцам Group_ID, которые имеются в обеих таблицах. В результирующем наборе отражаются все студенты (как входящие в группы, так и не входящие): SELECT S.Stud_FAM,S.Stud_IMYA, G.GRUP_NAME FROM Students S LEFT OUTER JOIN Grup G ON S.Grup_ID = G.Grup_ID ORDER BY G.Grup_NAME Использование INNER JOIN Следующий пример возвращает имена только тех студентов, кто записан в группы: SELECT S.Stud_FAM,S.Stud_IMYA, G.GRUP_NAME FROM Students S LEFT OUTER JOIN Grup G ON S.Grup_ID = G.Grup_ID ORDER BY G.Grup_NAME Использование RIGHT OUTER JOIN Следующий пример соединяет две таблицы по столбцу Group_ID и сохраняет строки из левой таблицы, не имеющие соответствий. Таблица Group сравнивается с таблицей Students по столбцам Group_ID, которые имеются в обеих таблицах. В результирующем наборе отражаются все группы (как со студентами, так и без): SELECT S.Stud_FAM,S.Stud_IMYA, G.GRUP_NAME FROM Students S RIGHT OUTER JOIN Group G ON S.Grup_ID = G.Grup_ID ORDER BY G.Grup_NAME 3.2 Задание таблиц в запросе Процесс отбора данных начинается с задания нужных нам таблиц базы данных. Для этого в инструкции SELECT используется предложение FROM. В следующей инструкции SELECT предложение FROM указывает, что возвратить надо все данные, которые находятся в таблице “Students ”: SELECT * FROM Students Использование звездочки (*) в инструкции SELECT возвращает все столбцы. Это избавляет от необходимости специально указывать их в запросе. Задание столбцов. При работе с инструкцией SELECT следует помнить, что можно отделить данные по вертикали, ограничив количество возвращаемых столбцов или по горизонтали, когда задаются подходящие строки. Столбцы со значениями, возвращаемыми из таблиц базы данных, указываются в виде списка сразу после ключевого слова SELECT и отделяются друг от друга запятыми: SELECT Stud_ID, Stud_FAM, Stud_IMA, Stud_ADDRESS FROM Students В этой команде: − SELECT - ключевое слово; − Stud_ID, Stud_FAM, Stud_IMA, Stud_ADDRESS - список столбцов из таблицы, которые выбираются запросом. Любые другие столбцы, не указанные в этом списке игнорируются; − FROM - ключевое слово, после него перечисляются таблицы - источник данных. Выбранные строки и столбцы таблиц базы данных всегда собираются во временную таблицу. В большинстве случаев эта таблица существует ровно столько времени, сколько нужно, чтобы данные были переданы запрашивающему их клиенту. Столбцы таблицы можно отобразить в отличном от первоначального порядке (переставить): SELECT Stud_ID, Stud_ADDRESS, Stud_FAM, Stud_IMA FROM Students Возможность изменения порядка отображения столбцов таблицы базы данных соответствует характеристикам реляционной базы данных, поскольку доступ к информации реляционной базы данных не зависит от реального способа хранения информации. Можно отображать один и тот же столбец таблицы в нескольких местах. Это удобно при необходимости улучшить восприятие таблицы. DISTINCT - аргумент, который обеспечивает устранение двойных значений в команде SELECT. Предположим, необходимо узнать, какие студенты в настоящий момент имеют хотя бы по одной оценке (сами оценки пока не нужны, нужен только список кодов студентов): SELECT Stud_ID FROM Progress При выполнении этой команды, коды дублируются (некоторые студенты имеют по несколько оценок). Для получения списка без дубликатов надо ввести: SELECT DISTINCT Stud_ID FROM Progress DISTINCT может указываться только один раз в команде SELECT. Если выбираются несколько столбцов, то DISTINCT опускает строки, где все выбранные поля идентичны. Строки, в которых некоторые значения одинаковы, а некоторые различны, будут сохранены. Если вместо DISTINCT указать ALL, это будет иметь противоположный эффект, дублирование строк сохранится. Поскольку это тот же самый случай, когда не указывается никакой аргумент, то ALL, по существу, лишь пояснительный аргумент. 3.3 Ограничение строк таблицы Таблицы имеют тенденцию становиться очень большими. Поскольку обычно только некоторые их них интересуют Вас в данное время, имеется возможность устанавливать критерии, чтобы определить какие строки будут выбраны. Для этого используется ключевое слово WHERE - предложение команды SELECT, позволяющее задавать условие, которое может быть верным или неверным для любой строки таблицы. Команда извлекает только те строки из таблицы, для которых это условие верно. Синтаксис предложения WHERE: WHERE имя_столбца оператор_сравнения значение Инструкция SELECT с предложением WHERE сужает результирующий набор запроса. Она может возвратить всего одну строку или даже вообще не возвратить ничего, если строки, совпадающие с указанным в инструкции критерием, не найдены. Предположим, необходимо получить имена и фамилии всех студентов с отчеством “Николаевич”: SELECT Stud_FAM, Stud_IMA FROM Students WHERE Stud_OTCH = 'Николаевич' Когда в запросе имеется предложение WHERE, SQL Server просматривает всю таблицу построчно и исследует каждую строку, чтобы определить верно ли утверждение. Примечание: строковые константы типа 'Москва' в Transact-SQL ограничиваются либо апострофами ', либо кавычками ". Теперь попробуем построить запрос с числовым полем в предложении WHERE. Выберем всех студентов со стипендией 1200: SELECT * FROM Students WHERE Stud_STIP = 1200 В следующем примере показано использование предложения WHERE для получения общего количества стипендии приходящегося на группу с номером 5: SELECT SUM(Stud_STIP) AS ' Сумма стипендии' FROM Students WHERE Group_ID=5 Сортировка вывода.Обычно требуется, чтобы данные как-то были упорядочены. Упорядочение задается с помощью ключевого слова ORDER BY (по умолчанию упорядочение по возрастанию). Синтаксис предложения ORDER BY: ORDER BY Столбец1 | Целое_значение [ACS | DESC] Например, выведем список студентов в алфавитном порядке: SELECT Stud_ID, Stud_FAM FROM Students ORDER BY Stud_FAM Если после имени столбца указать имя еще одного столбца, то по значениям второго столбца будут упорядочены строки, содержащие одинаковые значения в первом столбце. Столбец, указанный в списке ключевого слова ORDER BY, можно заменить числом, соответствующим порядку столбца в списке (параметр Целое_значение ): SELECT Grup_ID, Stud_ID, Stud_FAM FROM Students ORDER BY 1, 3 DESC ПараметрыASC (ascending) – по возрастанию, DESC (descending) – по убыванию задают порядок сортировки. Примечания: столбцы данных типа text и image нельзя использовать в предложении ORDER BY; ASC указывает, что сортировку значений в выбранных столбцах следует выполнить в возрастающем порядке, от самого нижнего до самого верхнего; DESC указывает, что сортировку значений в выбранных столбцах следует выполнить в убывающем порядке, от самого верхнего до самого нижнего. Нулевые значения считаются самыми нижними допустимыми значениями. |