Продвинутые SQL запросы.

Общие сведения

Продвинутые запросы SQL отличаются от базовых запросов тем, что тут идёт работа не с 1 таблицей, а с 2-мя и более. Так же возможность создавать отдельные таблицы и так же проводить с ними операции, как удаление, изменение и прочее. Далее возможность работы с индексами, которые позволяют ускорять поиск по данным.

Запросы на выборку данных

Выбрать все из нескольких таблиц:

Запрос SQL:

select*

fromStudents,Rooms

Листинг 11 – Код запроса

Рисунок 29 – Результат запроса

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

Запрос SQL:

select*

fromStudents,Rooms

whereStudents.RoomCodelikeRooms.RoomCode

Листинг 30 – Продвинутый запрос с условием

Рисунок 31 – Результат запроса

Чтобы посмотреть за каким комендантом закреплено общежитие, контактный телефон, то необходимо использовать запрос: выбрать что-то из несколько таблиц и условие.

Запрос SQL:

selectComendants.Name,Comendants.SecondName,Comendants.NmberPhone,Obsejitia.Addres

fromComendants,Obsejitia

whereComendants.ComendantCodelikeObsejitia.ComendantCode

Листинг 32 – Запрос с условием

Рисунок 33 – Результат запроса

Чтобы посмотреть данные определённого коменданта и узнать адрес его работы, то необходимо использовать запрос: выбрать что-то из несколько таблиц и составное условие (И).

Запрос SQL:

selectComendants.Name,Comendants.SecondName,Comendants.NmberPhone,Obsejitia.Addres

fromComendants,Obsejitia

whereComendants.ComendantCodelikeObsejitia.ComendantCodeandObsejitia.ComendantCode=’2′

Листинг 14 – Запрос с условием (И)

Рисунок 34 – Результат запроса

Если нам нужно найти студента по коду заселения или по номеру зачётки, то необходимо использовать запрос: выбрать что-то из несколько таблиц и составное условие (ИЛИ):
https://yastatic.net/safeframe-bundles/0.83/1-1-0/protected/render.htmlРЕКЛАМА

Запрос SQL:

SelectStudents.Surname,Students.Name,Students.SecondName

FromStudents,ZaselenieStudents

WhereStudents.NumberZachetki=’11ВЕ124’orZaselenieStudents.NumberZaselenia=’4′

Листинг 15 – Запрос с условием (ИЛИ)

Рисунок 35 – Результат запроса

4.3 Запрос на добавление таблиц

В процессе разработки БД стало необходимо добавить четыре новые таблицы для более удобной работы: Успеваемость, Кураторы, Преподаватели, Кафедры, из которых таблицы “Куратор”, “Преподаватели” являются справочными, остальные оперативными. Их поля и ключи представлены в таблице 7. Для создания таблицы про помощи встроенных функций SQL, можно использовать команду CREATTABLE.

Таблица 7 – Новые таблицы

Сущность	Атрибут	Тип данных	Ключ
Uspevaemost(Успеваемость)	StudentCode	bigint	FK
UspevaemostCode	bigint	PK
Otsenka_zachet	nvarchar	–
Kuratory(Кураторы)	StudentCode	bigint	FK
Surname	nvarchar	–
Name	nvarchar	–
SecondName	nvarchar	–
KuratoryCode	bigint	FK
KafedryCode	bigint	–
Prepodavateli(Преподаватели)	Surname	nvarchar	PK
Name	nvarchar	–
SecondName	nvarchar	–
KafedryCode	bigint	FK
kafedry(Кафедры)	KafedryCode	bigint	PK
KafedryName	nvarchar	–

ЗапросSQL:

CREATETABLEUspevaemost(

StudentCodebigintNOTNULL,

UspevaemostCodebigintNOTNULL,

Otsenka_zachetnvarchar(55)NOTNULL,

);

Листинг 16 – Создание таблицы

Рисунок 36 – Результат запроса

Оставшиеся четыре таблицы создаются таким же способом.

CREATETABLEKuratory(

StudentCodebigintNOTNULL,

KuratoryCodebigintNOTNULL,

Surnamenvarchar(55)NOTNULL,

Namenvarchar(55)NOTNULL,

SecondNamenvarchar(55)NOTNULL,

KafedryCodebigintNOTNULL,

);

Листинг 17 – Создание таблицы

Рисунок 37 – Результат запроса

CREATETABLEPrepodavateli(

KafedryCodebigintNOTNULL,

Surnamenvarchar(55)NOTNULL,

Namenvarchar(55)NOTNULL,

SecondNamenvarchar(55)NOTNULL,

);

Листинг 18 – Создание таблицы

Рисунок 38 – Результат запроса

Далее задается PK и FK в созданных таблицах. Сделаем это через графическую среду. Для этого создадим диаграмму в SQL

Рисунок 39 – Диаграмма после добавления таблиц

Чтобы выделить ключи, у ключевого поля нужно вызвать контекстное меню.

Рисунок 40 – Контекстное меню для выделения PK

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

Рисунок 41 – Создание Связи между таблицами

Оставшиеся связи добавляем аналогичным способом, результат добавления представлен на рисунке 42.

Рисунок 42 – Диаграмма после добавления таблиц и связей

4.4 Запрос на удаление и добавление

Для удаления ненужного столбца используется следующий запрос:

ЗапросSQL:

altertablePrepodavateli

dropcolumnSurname

Листинг 20 – Удаления столбца

Рисунок 43 – Результат до удаления

Рисунок 44 – Результат удаления

Чтобы добавить столбец используется команда ALTERTABLE. Воспользуемся ей, чтобы вернуть наш столбец.

ЗапросSQL:
altertablePrepodavateli

addSurnamenvarchar(55)NOTNULL

Листинг 21 – Добавление столбца

Рисунок 44 – Результат добавления

4.5 Создание и удаление индексов

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

useKursov;

createindexstudentsonStudents(StudentCode)

Листинг 22 – Создание индекса

Рисунок 45 – Результат добавления индекса

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

createuniqueindexroomsonRooms(RoomCode)

Листинг 23 – Создание уникального индекса

Рисунок 46 – Результат создания

Для удаления индекса используется команда DROPINDEX.

dropindexroomsonRooms

Листинг 24 – Удаление индекса

Рисунок 47 – Результат удаления

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

Запрос SQL:

select*intoStudentCodefromStudents

whereStudentCode>’1′

Листинг 25 – Вынесение информации в отдельную таблицу

Рисунок 48 – Результат добавления

Рисунок 49 – Результат добавления

Для создания одной таблицы из нескольких требуется следующий запрос:

selectSecondName,Name,NumberRoom

intotable1

fromStudentCode,Rooms

Листинг 26 – Создание таблицы

Рисунок 50 – Результат создания

Рисунок 51 – Результат создания

Для удаления таблицы можно воспользоваться командой DROPTABLE.

ЗапросSQL:

droptableTable1

Листинг 27 – Удаление таблицы

Рисунок 52 – Результат удаления

Выводы по разделу

В данном разделе были созданы продвинутые запросы к БД, такие как добавление таблиц и индексов, так же их редактирование и удаление, проделана работа по созданию связей между таблицами.

ЗАКЛЮЧЕНИЕ

В результате проделанной курсовой работы была разработана и реализована БД карточка студента.

Были изучены основы языка SQL, которые позволяют создать таблицу, поля, связи, отредактировать все записи, таблицы и поля. Заполнение БД 2-мя способами, как графическим, так и при помощи языка SQL. Удаление самой БД, отдельных частей(таблицу или информацию).

В базе данных хранится информация о студентах. Так же есть таблица, в которой хранится база данных, хранится информация о студентах. Так же есть таблица, в которой хранится информация о комнатах в общежитиях. В первом разделе подробно рассматривается предметная область. Определяются данные, которые будут храниться в базе данных. Так же определяются таблицы и поля, которые будут создаваться. В зависимости от типа хранимой информации, выбирается тип полей. Составляются предполагаемые запросы к БД.

Второй раздел посвящён проектированию базы данных в прикладной программе EnterpriseArchitect(создание таблиц, полей и связей между таблицами). А так же экспорт БД в целевую СУБД, в нашем случае это MSSQLServer 2008 R2. Построение диаграммы базы, средствами MSSQLServer 2008 R2, и заполнение данными базу.

Последующие разделы посвящены созданию запросов к БД на языке SQL. Используются простые запросы, включающие работу с одной таблицей, так и продвинутые запросы, которые работают с несколькими таблицами в БД.

В заключении приводятся основные результаты и выводы по работе.

Приложения содержат схему БД и полный скрипт её создания в среде MSSQLServer 2008 R2. В ходе курсового проектирования все поставленные задачи были успешно выполнены.