|
|
СОЗДАНИЕ И ЗАПУСК ПРОДВИНУТЫХ ЗАПРОСОВ 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 – Результат запроса Если нам нужно найти студента по коду заселения или по номеру зачётки, то необходимо использовать запрос: выбрать что-то из несколько таблиц и составное условие (ИЛИ): Запрос SQL: SelectStudents.Surname,Students.Name,Students.SecondName FromStudents,ZaselenieStudents WhereStudents.NumberZachetki='11ВЕ124'orZaselenieStudents.NumberZaselenia='4' Листинг 15 – Запрос с условием (ИЛИ)
Рисунок 35 – Результат запроса 4.3 Запрос на добавление таблиц В процессе разработки БД стало необходимо добавить четыре новые таблицы для более удобной работы: Успеваемость, Кураторы, Преподаватели, Кафедры, из которых таблицы “Куратор”, “Преподаватели” являются справочными, остальные оперативными. Их поля и ключи представлены в таблице 7. Для создания таблицы про помощи встроенных функций SQL, можно использовать команду CREATTABLE. Таблица 7 – Новые таблицы
Запрос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: 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. В ходе курсового проектирования все поставленные задачи были успешно выполнены. СПИСОК ЛИТЕРАТУРЫ: Основная литература: 1. Аткинсон, Леон MySQL. Библиотека профессионала; М.: Вильямс, 2010. - 624 c. 2. Горев А., Ахаян Р., Макашарипов С. Эффективная работа с СУБД. - СПб.: Питер, 1997. - 704 с. 3. Соломон Девид и др. Microsoft SQL Server 6.5. Энциклопедия пользователя. - К.: ДиаСофт, 1998. - 784 с. 4. Джеймс Р.Грофф, Пол Н. Вайнберг. SQL: Полное руководство . - К.: BHV, 1998. - 608 c. 5. Ладани Ханс. SQL. Энциклопедия пользователя. - М.: ДиаСофт, 1998. - 624 с Дополнительная литература: 1 Горелов А., Макашарипов С., Владимиров Ю. Microsoft SQL Server 6.5 для профессионалов. - СПб: Питер, 1998. - 464 с. 2 РобертД. Шнайдер. Microsoft SQL Server. Проектирование высокопроизводительных баз данных. - М.: ЛОРИ, 1998. - 361 с. 3 РонСаукап. Основы Microsoft SQL Server 6.5. – М.: Издательский отдел “Русская редакция” ТОО "ChannelTraidingLtd". – 1999. – 704 c. 4 Уанкуп Д. MS SQL Server 6.5 в подлиннике. – М.: Издательский дом “Вильямс”, 1999. – 912 с. 5 Бек, Кент Шаблоны реализации корпоративных приложений; М.: Вильямс, 2008. -369 c. 6 Роберт Сигнор, Михаэль О. Стегман. Использование ODBC для доступа к базам данных. - М.: БИНОМ, 1995. - 384 с. 7 Кузнецов С.Д. Стандарты языка реляционных баз данных SQL: краткий обзор // СУБД. - N 2, 1996. - C.6-36 8 Венкатрао М., Пиццо М. SQL/CLI - новый стиль связывания для SQL // СУБД. - N 2, 1996. - C.37-44 9 Боуман Дж., Эмерсон С., Дарновски М. Практическое руководство по SQL. - М.: Диалектика, 1997. - 320 с. 10 Райан Стивенс, Рональд Р. Плю. SQL. - М.: БИНОМ, 1997. - 400с. Интернет ресурсы: 1. Форум программистов и сисадминов CyberForum.ru. URL: http://www.cyberforum.ru/ (дата обращения: 15.10.2014; 10.01.2015). 2. Самоучитель по SQLonlinesamoucka.ru. URL: http://samoucka.ru/document29118.html (дата обращения: 15.10.2014; 10.01.2015). 3. О проекте “Самсон” – аналог БД samson-rus. URL: http://samson-rus.com/content/view/15/37/ (дата обращения 15.10.2014; 10.01.2015). 4. Публикации про SQLsql.ru.URL: http://www.sql.ru/articles/publications.shtml (дата обращения 15.10.2014; 10.01.2015).
СПИСОК СОКРАЩЕНИЙ: 1. EA – EnterpriseArchitect 11 БД – база данных 12 СУБД – система управления базами данных 13 PK – первичный ключ 14 FK – вторичный ключ 15 SQL - StructuredQueryLanguage (Структурированный язык запросов)
Приложение А Схема БД
Приложение Б SQL код БД
USEKursov ;
IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=object_id('FK_ComendantCode')ANDOBJECTPROPERTY(id,'IsForeignKey')= 1) ALTERTABLEObsejitiaDROPCONSTRAINTFK_ComendantCode ;
IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=object_id('FK_NumberObsejitia')ANDOBJECTPROPERTY(id,'IsForeignKey')= 1) ALTERTABLERoomsDROPCONSTRAINTFK_NumberObsejitia ;
IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=object_id('FK_RoomCode')ANDOBJECTPROPERTY(id,'IsForeignKey')= 1) ALTERTABLEZaselenieStudentsDROPCONSTRAINTFK_RoomCode ;
IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=object_id('FK_StudentCode')ANDOBJECTPROPERTY(id,'IsForeignKey')= 1) ALTERTABLEZaselenieStudentsDROPCONSTRAINTFK_StudentCode ;
IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=object_id('Comendants')ANDOBJECTPROPERTY(id,'IsUserTable')= 1) DROPTABLEComendants ;
IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=object_id('Obsejitia')ANDOBJECTPROPERTY(id,'IsUserTable')= 1) DROPTABLEObsejitia ;
IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=object_id('Rooms')ANDOBJECTPROPERTY(id,'IsUserTable')= 1) DROPTABLERooms ;
IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=object_id('Students')ANDOBJECTPROPERTY(id,'IsUserTable')= 1) DROPTABLEStudents ;
IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=object_id('ZaselenieStudents')ANDOBJECTPROPERTY(id,'IsUserTable')= 1) DROPTABLEZaselenieStudents ;
CREATETABLEComendants( ComendantCodebigintNOTNULL, Surnamenvarchar(50)NOTNULL, Namenvarchar(50)NOTNULL, SecondNamenvarchar(50)NOTNULL, NmberPhonenvarchar(20)NOTNULL ) ;
CREATETABLEObsejitia( NumberObsejitiabigintNOTNULL, Addresnvarchar(50)NOTNULL, ComendantCodebigintNOTNULL ) ;
CREATETABLERooms( RoomCodebigintNOTNULL, NumberObsejitiabigintNOTNULL, NumberRoombigintNOTNULL, FloorbigintNOTNULL, KolvoMestbigintNOTNULL ) ;
CREATETABLEStudents( StudentCodebigintNOTNULL, Surnamenvarchar(50)NOTNULL, Namenvarchar(50)NOTNULL, SecondNamenvarchar(50)NOTNULL, NumberZachetkinvarchar(40)NOTNULL, RoomCodebigintNOTNULL ) ;
CREATETABLEZaselenieStudents( NumberZaseleniabigintNOTNULL, StudentCodebigintNOTNULL, RoomCodebigintNOTNULL ) ;
ALTERTABLEComendants ADDCONSTRAINTUQ_Comendants_ComendantCodeUNIQUE (ComendantCode) ;
ALTERTABLEObsejitia ADDCONSTRAINTUQ_Obsejitia_NumberObsejitiaUNIQUE (NumberObsejitia) ;
ALTERTABLERooms ADDCONSTRAINTUQ_Rooms_RoomCodeUNIQUE (RoomCode) ;
ALTERTABLEStudents ADDCONSTRAINTUQ_Students_StudentCodeUNIQUE (StudentCode) ;
ALTERTABLEZaselenieStudents ADDCONSTRAINTUQ_ZaselenieStudents_NumberZaseleniaUNIQUE (NumberZaselenia) ;
ALTERTABLEComendantsADDCONSTRAINTPK_Comendants PRIMARYKEYCLUSTERED (ComendantCode) ;
ALTERTABLEObsejitiaADDCONSTRAINTPK_Obsejitia PRIMARYKEYCLUSTERED (NumberObsejitia) ;
ALTERTABLERoomsADDCONSTRAINTPK_Rooms PRIMARYKEYCLUSTERED (RoomCode) ;
ALTERTABLEStudentsADDCONSTRAINTPK_Students PRIMARYKEYCLUSTERED (StudentCode) ;
ALTERTABLEZaselenieStudentsADDCONSTRAINTPK_ZaselenieStudents PRIMARYKEYCLUSTERED (NumberZaselenia) ;
ALTERTABLEObsejitiaADDCONSTRAINTFK_ComendantCode FOREIGNKEY (ComendantCode)REFERENCESComendants(ComendantCode) ;
ALTERTABLERoomsADDCONSTRAINTFK_NumberObsejitia FOREIGNKEY (NumberObsejitia)REFERENCESObsejitia(NumberObsejitia) ;
ALTERTABLEZaselenieStudentsADDCONSTRAINTFK_RoomCode FOREIGNKEY (RoomCode)REFERENCESRooms(RoomCode) ;
ALTERTABLEZaselenieStudentsADDCONSTRAINTFK_StudentCode FOREIGNKEY (StudentCode)REFERENCESStudents(StudentCode) ;
|
|
