Практическая работа 1. 2 Описание проектирования базы данных 2 практическая работа 2 10
 Скачать 2.52 Mb.
|
|
Как видно из синтаксиса оператора SELECT, обязательными являются только предложение SELECT с перечнем выдаваемых колонок и предложение FROM. Пример простейшего оператора SELECT: Выдать перечень всех служащих: SELECT * FROM Employee; Ниже приведено несколько упрощенных вариантов синтаксиса оператора SELECT, помогающих научиться составлять простые запросы. Упрощенный синтаксис внутреннего соединения (стандарт SQL-92): SELECT Колонка [, Колонка ...] | * FROM [WHERE <условие поиска>]; Упрощенный синтаксис внешнего соединения: SELECT Колонка [, Колонка ...] | * FROM {LEFT | RIGHT | FULL} [OUTER] JOIN [ON <условие поиска>] [WHERE <условие поиска>]; Упрощенный синтаксис использования подзапроса: SELECT [DISTINCT] Колонка [, Колонка ...] FROM WHERE {expression {[NOT] IN | <оператор сравнения>} | [NOT] EXISTS } (SELECT [DISTINCT] Колонка [, Колонка ...] FROM WHERE <условие поиска> ); 5.5. Задание Практическую работу следует выполнять в следующем порядке: Изучить синтаксис оператора SELECT и примеры запросов к учебной базе данных ‘University.mdf’. Выполнить в окне ‘SQL Editor’ 27 запроса к базе данных, согласно приведенным практической работе образцам выполнения запросов и сохранять каждый под именами ‘Lab5-k.sql’, где k – номер запроса по порядку, в своей рабочей папке. Каждый запрос должен иметь комментарии с описанием, а файл в целом должен иметь комментарии со сведениями об авторе и дате создания. 5.6. Ход работы Примечание. У вас должны быть перед выполнением этой практической работы созданы все таблицы базы данных университета, созданы ключи, а также заполнены данными. Выполнение sql-запросов Для выполнения запросов SELECT в программе ‘SQL Server Managmant Studio’ необходимо выполнить следующие действия: Подключиться к базе данных и выполнить команду ‘Создать запрос’. В результате откроется окно ‘Конструктора запросов’ (рис. 1). Рис. 1. Окно выполнения запросов Ввести текст запроса согласно рис.1. Нажать на панели инструментов кнопку  [Выполнить] . Если запрос правильный, то в результате произойдет его выполнение и результат будет отображен на вкладке ‘Результаты’ (рис. 2). Рис. 2. Окно с результатом выполнения запроса Количество извлеченных в результате выполнения запроса строк отображается над сеткой с данными справа. На рис.2 там содержится строка ‘3 строк’. В данном примере извлечено столько строк, сколько требуется, чтобы заполнить сетку (в ней помещается только 3 строки) * . Чтобы узнать, сколько всего строк соответствуют выполненному оператору, надо перейти в конец отображаемого набора данных. Чтобы выполнить другой запрос, надо вернуться на вкладку ‘Редактора’, создать новый запрос и повторить те же действия. тексту ранее выполнявшихся правильных запросов можно вернуться, если перейти на вкладку ‘История’. Тема Примеры создания запросов с отбором строк по условию. SQL дает возможность определить критерии отбора необходимых строк во фразе WHERE предложения SELECT. В этом случае строки исходных таблиц будут включены результирующую только если строка соответствует указанным критериям. Условие - это выражение, которое может быть истинным или ложным (логическое выражение или предикат), то есть принимать логические значения TRUE или FALSE соответственно. В результирующую таблицу включаются только те строки, для которых указанное во фразе WHERE условие равно TRUE (иными словами, которые удовлетворяют заданному условию). случае одной таблицы механизм работы предложения SELECT с фразой WHERE следующий. 1. Из таблицы, указанной во фразе FROM, выбирается очередная строка. 2. Она проверяется на соответствие условию во фразе WHERE. 3. Если результат равен TRUE, строка включается в результирующую таблицу и форматируется в соответствии с фразой SELECT, а если он равен FALSE, строка пропускается. Далее будут рассмотрены основные выражения, допустимые для условия во фразе WHERE. Использование простейших условий Простейшими считаются условия, в которых используются операторы сравнения и логические операторы. Хотя такие условия являются простейшими в смысле семантики конструкций, они могут иметь довольно сложную структуру из многих операторов сравнений и вложенных друг в друга логических связок. Операторы сравнения Особенностью операторов сравнения является то, что независимо от типов операндов их результатом являются логические значения. Предположим, вы хотите получить список всех профессоров. Создайте новый запрос, введите sql-запрос, выполните его, сохраните его в рабочую папку ЛАБ6_SQL под именем 1.sql. Запрос 1. Вывести фамилии профессоров. SELECT NAME_TEACHER AS ‘Cписок профессоров’ FROM TEACHER WHERE DOLGNOST = 'профессор'; Чтобы выполнить sql-команду нажмите на панели редактора кнопку  . В результате выполнения данного кода будут выданы все профессора. Например, Слово 'npофeccop' в запросе является строковой константой, поэтому ее следует заключить в кавычки. Обратите внимание, что мы указали фразу SELECT без ключевого слова DISTINCT, так как тогда от нас была бы скрыта информация о существовании среди профессоров однофамильцев. Чтобы при выводе результирующий столбец имел содержательный заголовок, мы поименовали его как Список профессоров. Это первый пример использования предиката над строковым типом данных. Здесь столбец строкового типа сравнивается со строковой константой. Запрос выполнен правильно, однако нужно всегда помнить о том, что предикаты над строками являются чувствительными к регистру букв. Например, предикат 'ИВАНОВ' = 'Иванов' будет ложным. Поэтому, если для некоторого профессора его должность была введена в таблицу TEACHER как 'Профессор', он не будет найден по условию WHERE DOLGNOST = 'профессор'. Чтобы на предикаты над строками не влиял регистр букв, нужно использовать обычно имеющиеся в СУБД функции преобразования букв в прописные и строчные. В стандарте SQL, например, указаны функции UPPER и LOWER, выполняющие такие преобразования. Следовательно, для предыдущего запроса правильней будет записать условие фразы WHERE одним из следующих способов: WHERE LOWER(DOLGNOST) = 'профессор' WHERE UPPER(DOLGNOST) = 'ПРОФЕССОР' Самостоятельно измените в исходном запросе строку условия с использованием функции изменения регистра. Чтобы сохранить запрос нажмите правую кнопу и из контекстного меню выберите Сохранить в файл. Присвойте имя 1.sql и сохраните в папку ЛАБ5_SQL. Запрос 2. Найти всех студентов с стипендией, превышающим 300 грн. sql-редакторе создайте новый запрос и введите следующий код: SELECT SUTNAME, SUTFNAME FROM STUDENT WHERE STIPEND > 300; Выполните его. Приведем несколько примеров использования операторов сравнения для столбцов строкового и временного типа. Обратите внимание, что сравнивать можно не только значение столбца с константой, но и значения столбцов между собой. Запрос 3. Вывести фамилии и должности преподавателей, принятых на работу после 01.01.2002. SELECT NAME_TEACHER AS 'Фамилия', DOLGNOST AS 'Должность' FROM TEACHER WHERE DATA_HIRE > '1/01/2002'; Запрос 4. Вывести фамилии и должности преподавателей, фамилии которых в алфавитном порядке располагаются после фамилии Сычева. SELECT NAME_TEACHER, DOLGNOST FROM TEACHER WHERE UPPER(NAME_TEACHER) > 'Сычева'; Самостоятельно создайте запрос 5. Вывести фамилии преподавателей, у которых надбавка меньше ставки в 2,5 и более раз. Логические операторы Операндами и результатом логических операторов являются логические значения. Стандартными логическими операторами являются AND, OR и NOT. Их действие показано в так называемых истинностных таблицах. Использование операторов сравнения вместе с логическими операторами предоставляет возможность формулировать составные условия для отбора строк таблиц. Использование логического оператора AND Логический оператор AND во многих случаях действует как связка ‘и’ в русском языке. Рассмотрим несколько примеров с использованием этого оператора. Запрос 6. Вывести фамилии студентов, проживающих в городе Макарово и имеющих стипендию больше 100 грн. SELECT SUTFNAME FROM STUDENT WHERE CITY = 'Макарово' AND STIPEND >100; Самостоятельно создайте запрос 7. Вывести фамилии преподавателей, которые являются профессорами и ставка которых превышает 4500. Самостоятельно создайте запрос 8. Вывести фамилии студентов учащихся на кафедре под порядковым номером 2 (Прикладная математика) с стипендией в диапазоне 100-500 грн. Использование логического оператора OR Логический оператор OR во многих случаях действует как связка ‘или’ в русском языке. Рассмотрим несколько примеров. Запрос 9. Вывести названия кафедр, расположенных либо в 1 либо в 8 корпусе. SELECT NAME_KAFEDRU, NUM_KORPUSA FROM KAFEDRA WHERE NUM_KORPUSA =1 OR NUM_KORPUSA =8; Использование логического оператора NOT Логический оператор NOT в русском языке передается словами ‘не’ и ‘кроме’. Запрос 10. Вывести названия всех факультетов, кроме факультета математики и информатики. SELECT NAME_FACULTETA FROM FACULTET WHERE NOT LOWER(NAME_FACULTETA) = 'математики и информатики'; Обратите внимание, что оператор NOT должен предшествовать выражению сравнения, а не ставиться перед оператором сравнения. То есть запись LOWER(NAME_FACULTETA) NOT =''математики и информатики'' будет неверной. Учитывая, что отрицанием оператора = является оператор <>, вместо указанного условия можно было бы записать LOWER(NAME_FACULTETA) <> ''математики и информатики''. Это относится ко всем операторам сравнения, так как каждый из них имеет оператор, являющийся его отрицанием. Комбинирование логических операторов Логические операторы можно объединять, формируя составные условия. Возможность комбинирования обеспечивается тем, что любой логический оператор возвращает истинностное значение, а значит, его результат может использоваться в другом логическом операторе. Рассмотрим несколько примеров. Запрос 11. Вывести фамилии, должность, ставку и надбавку ассистентов, у которых либо ставка меньше 550, либо надбавка больше 60. SELECT NAME_TEACHER, DOLGNOST, Salary, Rise FROM TEACHER WHERE LOWER(DOLGNOST) ='ассистент' AND (Salary < 550 OR Rise > 60); Использование выражений над столбцами До сих пор в выражениях фразы WHERE мы использовали в качестве значения одного или обоих аргументов имена столбцов. Однако аргументами могут быть и выражения над столбцами. Запрос 12. Показать фамилии преподавателей, чья зарплата (ставка плюс надбавка) превышает 3500. SELECT NAME_TEACHER AS ‘Фамилия преподавателя’, Salary + Rise AS ‘Его зарплата’ FROM TEACHER WHERE Salary + Rise > 3500; Запрос 13. Показать фамилии преподавателей, половина зарплаты которых превышает пятикратную надбавку. SELECT NAME_TEACHER FROM TEACHER WHERE (Salary + Rise) / 2 > 5 * Rise; Использование специальных операторов SQL имеются операторы сравнения, позволяющие проверять значения столбцов выражений над ними на соответствие некоторым специальным условиям: принадлежность множеству; принадлежность диапазону; соответствие шаблону; соответствие регулярному выражению; неопределенное значение. этом разделе вы узнаете, как их использовать и как с их помощью создавать составные условия. Проверка на принадлежность множеству Оператор IN позволяет проверить, входит ли значение в указанное множество значений. В простейшем случае этот оператор имеет следующий синтаксис: имя_столбца [NOT] IN (список_эначений) Здесь список значений представляет собой перечень разделенных запятыми констант, тип которых должен соответствовать типу столбца, чье имя приведено слева. Семантика этого предиката такова: он принимает значение TRUE, если значение столбца соответствует одной из констант списка. Приведем пример. Запрос 14. Вывести названия и номер корпуса кафедр, расположенных в корпусах 1, 3, 12. SELECT Name_Kafedru, NUM_KORPUSA FROM KAFEDRA WHERE NUM_KORPUSA IN ('1', '3', '12'); Использование отрицания Так как предикат IN возвращает истинностное значение, к нему можно применить логическое отрицание. Для этого следует воспользоваться нотацией NOT IN. В этом случае предикат будет истинным, если значение столбца не входит в указанный список. Запрос 15. Вывести названия и номер корпуса кафедр, расположенных в любых корпусах, кроме 1, 3, или 12. SELECT Name_Kafedru AS ‘Название кафедры’, NUM_KORPUSA AS “Корпус’ FROM KAFEDRA WHERE NUM_KORPUSA NOT IN (‘1', 'З', '12'); Использование выражений над столбцами левой части оператора IN вместо имени столбца можно использовать любое допустимое над столбцами таблицы выражение языка. Запрос 16. Вывести фамилии преподавателей, зарплата которых (ставка + надбавка) равна 800, 900, 1000, 1100 или 1200. SELECT NAME_TEACHER AS ‘Фамилия преподавателя’, Salary + Rise AS ‘Зарплата преподавателя’ FROM TEACHER WHERE Salary + Rise IN (1150, 2400, 3150, 4300); Более того, элементами списка в правой части оператора IN тоже могут быть выражения над столбцами, как это показано в следующем примере: Запрос 17. SELECT NAME_TEACHER, Salary, Salary + Rise FROM TEACHER WHERE Salary + Rise IN (Salary + 100, Salary + 200, Salary + 300, Salary + 400, Salary + 500); Проверка на принадлежность диапазону значений Еще одной формой проверки вхождения элемента во множество является проверка на его принадлежность диапазону значений. Для этого применяется предикат BETWEEN, который определяет нахождение значения столбца между указанными минимальным и максимальным значениями. Синтаксис предиката следующий: имя_столбца [NOT] BETWEEN минимум AND максимум Проверять можно значения числовых, строковых и временных типов (для строк символов предполагается алфавитное упорядочение). Оператор BETWEEN является включающим - это означает, что крайние значения диапазона включаются в допустимые. Запрос 18. Вывести фамилии преподавателей со ставкой в диапазоне 1000- 2000. SELECT NAME_TEACHER FROM TEACHER WHERE Salary BETWEEN 1000 AND 2000; Использование строковых значений Использование в операторе BETWEEN в качестве границ диапазона строковых значений имеет особенности, связанные с упорядочением (это же относится и к другим операторам сравнения). Запрос 19. Вывести фамилии преподавателей, начинающиеся на буквы от ‘3’ до ‘Л’. SELECT NAME_TEACHER FROM TEACHER WHERE UPPER(NAME_TEACHER) BETWEEN 'З' AND 'Л'; Среди строк результата нет фамилий, начинающихся на букву ‘Л’. Дело в том, что при сравнении строк символов разной длины SQL предварительно дополняет более короткую строку символами пробела, а он в упорядочениях символов предшествует всем остальным. Поэтому строка, состоящая из буквы ‘Л’ (дополненная пробелами), всегда будет меньше любой другой строки, в которой за начальной буквой 'Л’ следуют отличающиеся от пробела символы. Чтобы это учесть, в качестве верхнего значения диапазона лучше всего указывать следующую по алфавиту букву (в данном случае — ‘М’). |