Лабораторные работы по дисциплине "Проектирование баз данных"

Лабораторные работы по дисциплине "Проектирование баз данных"
Формат - Microsoft SQL Server 2008

Выполнить задание к лабораторной работе по следующим указаниям

2.1 Создание запроса

Все запросы состоят из одиночной команды SELECT с достаточно простой структурой, однако путем ее использования можно выполнять сложную обработку данных. В самой простой форме, команда SELECT просто обращается к БД, чтобы извлечь информацию из таблицы. Например, можно вывести таблицу студентов, дав следующий запрос (данный запрос будет содержать информацию о всех студентах):

SELECT SNUM, SFAM, SIMA, SOTCH, STIP

FROM STUDENTS;

SNUM SFAM SIMA SOTCH STIP

______________________________________________________________

3412 Поляков Анатолий Алексеевич 25.50

3413 Старова Любовь Михайловна 17.00

3414 Гриценко Владимир Николаевич 0.00

3415 Котенко Анатолий Николаевич 0.00

3416 Нагорный Евгений Васильевич 25.50

Комментарий к каждой части команды:

SELECT - ключевое слово, которое сообщает БД, что эта команда является запросом. те. все запросы начинаются этим словом.

SNUM, SFAM, SIMA, SOTCH, STIP - список полей из таблицы, которые выбираются запросом. Поля, не перечисленные здесь, не будут включены в вывод команды, но это, разумеется, не означает, что они будут удалены или информация в них будет стерта из таблиц. Запрос не воздействует на информацию в таблицах, он только показывает данные.

FROM STUDENTS - ключевое слово, подобно SELECT которое должно быть представлено в каждом запросе. Оно сопровождается пробелом и затем именем таблицы используемой в качестве источника информации. В данном случае - это таблица студентов STUDENTS.

Точка с запятой (;) используется во всех интерактивных командах для сообщения БД, что команда заполнена и готова выполниться, а в некоторых системах наклонная черта (\) в строке является индикатором конца команды.

Если необходимо получить каждое поле таблицы, имеется необязательное сокращение в виде символа “звездочка” (*), которое можно использовать для вывода полного списка полей следующим образом:

SELECT * FROM STUDENTS;

это приведет к тому же результату, что и предыдущая команда.

В общем случае запрос начинается с ключевого слова SELECT сопровождаемого пробелом. После этого должен следовать список, разделенный запятыми имен полей, которые необходимо вывести.

Ключевое слово FROM следующее далее, сопровождается пробелом и именем таблицы, запрос к которой делается. В заключении точка с занятой должна использоваться для того, что бы закончить запрос и указать что команда готова к выполнению.

При работе с данными очень часто возникает потребность в удалении избыточности данных. Это реализуется с использованием DISTINCT — аргумент, который обеспечивает возможность устранить повторяющиеся значения из предложения SELECT.

Например, необходимо узнать, какие студенты в настоящее время сдавали учебные предметы, причем не требуется уточнение полученной оценки и сдаваемого предмета. Запрос предоставит следующий вывод, однако в нем есть записи дубликаты:

SELECT SNUM FROM USP;

SNUM

3412

3413

3414

3412

3416

Для получения списка результатов без дубликатов в данном случае целесообразно воспользоваться следующим:

SELECT DISTINCT SNUM FROM USP;

в результате чего будет получено:

SNUM

3412

3413

3414

3416

Другими словами, DISTINCT просматривает значения, которые были выведены ранее, и не дает им дублироваться в списке. Это - полезный способ избежать избыточности данных, однако стоит внимательно следить за его применением, т.к. можно скрыть некоторую нужную информацию. Например, если в таблице студентов появятся однофамильцы. то использование DISTINCT может привести к тому, что о существовании однофамильцев пользователь знать не будет.

Следует иметь в виду, что DISTINCT может указываться только один раз в данном предложении SELECT. Если предложение выбирает многочисленные поля, DISTINCT опускает записи, где все выбранные поля идентичны. Если вместо DISTINCT указать ALL, то это будет иметь противоположный эффект и дублирование строк вывода сохранится.

2.2 Использование условий поиска для отбора строк

Со временем таблицы становятся очень большими, поскольку увеличивается количество добавляемых в них записей. Обычно из всех записей интересуют только определенные, поэтому SQL дает возможность устанавливать критерии выбора записей для вывода.

WHERE - предложение команды SELECT, которое позволяет устанавливать предикаты, условие которых может быть или верным или неверным для любой записи таблицы. Команда извлекает только те записи из таблицы, для которой такое утверждение истинно. Например, необходимо выбрать фамилии и размеры стипендии студентов, при этом интересуют только такие, которые получают стипендию в размере 25.50. Такой запрос будет иметь вид:

SELECT SFAM, STIP

WHERE STIP=25.50;

Вывод для этого запроса будет следующий:

SFAM STIP

Поляков 25.50

Нагорный 25.50

Таким образом, существует несколько способов заставить таблицу предоставлять любую информацию, которая необходима пользователю, а не просто выводить все ее содержание. Наиболее важно и то, что можно устанавливать предикат, определяющий наличие вывода указанной строки таблицы. Предикаты могут становиться очень сложными, предоставляя высокую точность в решении, какие строки выбирать с помощью запроса.

В предикатах требуется не только оценивать равенство оператора как истинного или ложного, но и осуществлять другие виды связей. Это реализуется с помощью булевых операторов и знаков отношения, причем предикат может содержать неограниченное число условий.

В целом, реляционный оператор - это математический символ, который указывает на определенный тип сравнения между двумя значениями:

= равный чему-либо;

> больше чем:

< меньше чем:

> больше чем или равно:

= меньше чем или равно:

<> не равно.

Эти операторы имеют стандартные значения для числовых данных, а для символьных их определение зависит от кодов А$С II символов - они следуют в алфавитном порядке, причем заглавные буквы имеют меньший код, чем строчные. Например, “Z”< “a”.

Предположим, что необходимо вывести список студентов, получающих стипендию, т.е. для которых STIP>0. Для этого воспользуемся следующим запросом:

SELECT *

FROM STUDENTSWHER STIP>0;

SNUM SFAM SIMA SOTCH STIP

3412 Поляков Анатолий Алексеевич 25.50

3413 Старова Любовь Михайловна 17.00

3416 Нагорный Евгений Васильевич 25.50

Стандартные булевые операторы:

AND, OR, NOT

Связывая предикаты с булевыми операторами, можно значительно увеличить возможность выборки данных. Например, по таблице с данными об успеваемости можно получить информацию о всех студентах сдавших предмет с кодом 2003:

SELECT *

FROM USP

WHERE OCENKA>=3 AND PNUM=2003;

UNUM OCENKA UDATE SNUM PNUM

1002 4 10/06/2006 3413 2003

1004 4 12/06/2006 3412 2003

В предложении SELECT могут быть использованы специальные операторы IN, BETWEEN, LIKE, IS NULL

Оператор IN определяет набор значений, в которых данное значение должно быть включено. Например, вывести информацию обо всех студентах, имя которых Анатолий и Владимир

SELCT *

FROM STUDENTS

WHERE SIMA = ‘Анатолий’ OR SIMA = ‘Владимир’;

SNUM SFAM SIMA SOTCH STIP

3412 Поляков Анатолий Алексеевич 25.50

3414 Гриценко Владимир Николаевич 0.00

3415 Котенко Анатолий Николаевич 0.00

Однако имеется и более простой способ получить ту же информацию с помощью запроса:

SELЕCT *

FROM STUDENTS

WHERE SIMA IN (‘Анатолий’ , ‘Владимир’);

Оператор BETWEEN несколько похож на IN, но в отличие от определения из набора, BETWEEN определяется диапозон значений, в которых должны уменьшатся искомые значения, что и делает предикат верным.

Структура оператора BETWEEN следующая: вводится начальное значение, ключевое слово AND и конечное значение.

Пример, помогающий извлекать из таблицы успеваемости номера и оценки всех студентов, оценки которых заключены между 3 и 5

SELЕCT SNUM, OCENKA

FROM USP

WHERE OCENKA BETWEEN 3 AND 5;

SNUM OCENKA

3412 5

3413 4

3414 3

3412 4

3416 5

Оператор LIKE применим только к полям типа CHAR или VARCHAR, в которых он ищет подстроки, т.е. он ищет символы и проверяет, совпадают ли они с условием.

В качестве примера найдем всех преподавателей, чьи фамилии начинаются с буквы К.

SELECT TFAM, TIMA, TOTCH

FROM TEACHERS

WHERE TFAM LIKE ‘K%’;

Результат запроса будет следующий:

TFAM TIMA TOTCH

Костыркин Олег Владимирович

Казанко Виталий Владимирович

2.3 Получение итоговых данных

Запросы могут производить обобщенную групповую обработку значений полей, что реализуется с помощью агрегатных функций.

В SQL допускаются следующие агрегатные функции:

- COUNT – производит подсчет количества строк или не нулевых значений;

- SUM – рассчитывает арифметическую сумму всех выбранных значений данного поля;

- AVG – производит усреднение всех выбранных значений данного поля;

- MAX – находит и возвращает наибольшее из всех выбраннх значений данного поля;

- MIN– находит и возвращает наименьшее из всех выбраннх значений данного поля;

Например, найти сумму всей выплаченной стипендии в таблице с данными о студентах, можно использовать следующий запрос:

SELECT SNUM (STIP)

FROM STUDENTS;

Вывод состоит из единственного числа 68,00

Следующий пример даст вам возможность познакомиться с функцией COUNT. Подсчитать количество студентов, сдававших учебные примеры по таблице успеваемость.

SELECT COUNT (DISTINCT SNUM)

FROM USP;

В качестве результата этого запроса будет получено значение 4.

Обратите внимание на обязательное требование того, чтобы DISTINCT был помещен в круглые скобки. Возможно использование DISTINCT с любыми агрегатными функциями, но наиболее часто он используется с COUNT.

2.4 Объединение полей и агрегатных функций

Команда GROUP BY позволяет определять подмножество значений в поле в терминах другого поля, и применять функцию агрегата к такому подмножеству.

Например, если возникнит необходимость в определении наименьшей оценки, полученной каждым студентом, то можно сделать с использованием GROUP BY следующий запрос:

SELECT SNUM, MIN (OCENKA)

FROM USP

SNUM;

Вывод полученного запроса:

SNUM

________

3412 4

3413 4

3414 3

3416 5

Команда GROUP BY может применяться с агрегатными функциями независимо от серий групп, которые определяются с помощью значения поля в целом.

С учетом вышесказанного с данного момента можно использовать запросы для получения определенных значений и формировать вывод данных в соответствии с требованиями текущей задачи пользователя.