Лабораторная работа №1. Создание базы данных и определение ее структуры

Введение

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

В реальном проектировании структуры базы данных применяется метод семантического моделирования, который представляет собой моделирование структуры данных, опираясь на смысл этих данных. В качестве инструмента семантического моделирования используются различные варианты диаграмм «сущность-связь» (Entity-Relationship - ER). Все варианты диаграмм «сущность-связь» используют графическое изображение сущностей (объектов) предметной области, их свойств и взаимосвязей между сущностями. Результатом проектирования базы данных является концептуальная схема (ER-диаграмма) моделируемой предметной области.

После разработки проекта базы данных можно приступать к его реализации в конкретной системе управления базами данных (СУБД). Современная волна информационных технологий управления данными основывается на использовании систем управления реляционными базами данных.

Все языки манипулирования данными, созданные до появления реляционных баз данных, были ориентированы на операции с данными, представленными в виде логических записей файлов. Это требовало от пользователей детального знания организации хранения данных и достаточных усилий для указания не только того, какие данные нужны, но и того, где они размещены и как шаг за шагом получить их. Рассматриваемый в методических указаниях непроцедурный язык SQL (Structured Query Language - структурированный язык запросов) ориентирован на операции с данными, представленными в виде логически взаимосвязанных совокупностей таблиц. Особенность предложений этого языка состоит в том, что они ориентированы в большей степени на конечный результат обработки данных, чем на процедуру этой обработки. SQL сам определяет, где находятся данные, какие индексы и даже наиболее эффективные последовательности операций следует использовать для их получения: не надо указывать эти детали в запросе к базе данных.

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

В методических указаниях рассматриваются вопросы разработки проекта баз данных и его реализации в выбранной системе управления базами данных: от создания объектов базы данных, манипуляции данными - до конструирования сложных запросов по поиску информации, а также средства профессионального пользователя - процедуры, триггеры, вопросы разработки приложений пользователей. Для реализации проекта предлагается система управления базами данных Microsoft SQL Server 2005, которая ориентирована на создание и ведение базы данных на уровне предприятия. Стандартные средства для доступа к данным различных баз данных предоставляет язык запросов SQL. Большинство баз данных имеют свою собственную версию этого языка. Но, несмотря на это, понимание основ SQL поможет работать со многими базами данных на самых разных платформах.

Продукция на рынке информационных услуг обновляется каждые 4-6 месяцев. В связи с этим вполне вероятно, что версия Microsoft SQL Server, используемая на лабораторных занятиях в настоящий момент, может измениться. В помощь студентам для освоения основных возможностей языка SQL на кафедре «Инженерная кибернетика» была разработана виртуальная обучающая система SQL_Education по проектированию баз данных и изучению языка структурированных запросов. Эта программа имеет наглядный интерфейс, снабжена средствами видеоанимации, сопровождается множеством примеров. При подготовке к лабораторным занятиям студент должен изучить соответствующий раздел этой программы (согласно методическим указаниям) и использовать полученные навыки для выполнения заданий.

Ориентированный на работу с таблицами, SQL не имеет достаточных средств для создания сложных прикладных программ. Поэтому в большинстве СУБД он используется вместе с языками программирования высокого уровня. В лабораторной работе № 7 рассматриваются вопросы разработки приложения пользователя. Для иллюстрации того, как должен выглядеть конечный результат разработки, методические указания к этой работе снабжены exe-файлом, представляющим собой пример разработки.

Последняя лабораторная работа посвящена вопросам шифрования данных в MS SQL Server. Эта работа демонстрирует результат применения алгоритмов, рассмотренных в курсе «Защита информации» к защите данных в базе данных.

Кафедра выражает благодарность студентам специальности «Автоматизация и управление» Алтурмесову Е. и Цой М. (гр. БАУ-05) за помощь в реализации пользовательского интерфейса обучающей программы.

Типы команд SQL

Основные категории команд, реализующих в SQL выполнение различных функций:

- DDL (Data Definition Language - язык определения данных);

DML (Data Manipulation Language - язык манипуляций данными);

- DQL (Data Query Language - язык запросов к данным);

DCL (Data Control Language - язык управления данными);

команды администрирования данных;

команды управления транзакциями.

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

 

Основные типы данных

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

Самыми общими типами в SQL, как и в большинстве других языков, являются: символьные строки; числовые строки; значения даты и времени.

Символьные (character) типы данных позволяют хранить буквенные, числовые и специальные (например,? или >) символы. При загрузке в область хранения (такую, как столбец таблицы) символьные данные вводятся в одинарных или двойных кавычках.

Тип char(n). При хранении данных этого типа для каждого символа используется один байт. Число n определяет размер области хранения максимального количества символов данного столбца. Если вводится значение, меньшее n, SQL Server добавит пробелы после последнего символа, чтобы общая длина равнялась n.

Для экономии дискового пространства, когда хранящиеся в столбце значения имеют разную длину, можно использовать тип varchar(n). В отличие от предыдущего типа данных, размер области хранения для данных этого типа меняется в соответствии с фактическим количеством символов, хранящихся в каждом столбце таблицы, пробелы в конце введенного значения не добавляются.

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

Числовые (numeric) типы данных. Стандартными для SQL являются следующие типы: integer, smallint - для хранения целых чисел; real - для хранения положительных или отрицательных дробей с точностью до семи цифр; float(n) - для хранения положительных или отрицательных дробей с точностью до пятнадцати цифр.

Типы данных datetime и smalldatetime. Они используются для хранения даты и времени. Гораздо удобнее хранить дату и время в формате одного из предназначенных для этого типов данных, а не в виде строки символов. В этом случае дата и время выводятся на экран в привычном формате. Тип datetime позволяет определить дату и время, начиная с 1/1/1753 и заканчивая 12/31/9999; а тип smalldatetime - с 1/1/1900 по 6/6/2079.

Перечисленные типы данных позволяют хранить до 90% информации. Кроме этих типов, Transact SQL содержит набор специальных типов данных. Можно определить собственный тип данных - пользовательский, который затем будет использоваться для сохраняемых структур.

При назначении типа данных столбцу таблицы возможно использование ограничений NULL/NOT NULL, которые позволяют указать, какие из столбцов должны обязательно иметь значения во всех строках таблицы.

 

Работа с базой данных

Создание базы данных

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

Работа начинается с создания базы данных. Команда создания базы данных Create Database имеет следующий синтаксис:

 

CREATE DATABASE имя_базы_данных[PRIMARY]

(NAME = имя_базы_данных_data, ='…\имя_базы_данных _data.mdf ', size = размер, = максимальный размер, filegrowth = приращение)

LOG ON

(NAME = имя_базы_данных _log, =’…\имя_базы_данных _log.ldf ', size = размер,

maxsize = максимальный размер, filegrowth = приращение)

 

Здесь и далее при описании общего вида команды, размещение опции в квадратных скобках означает, что этот параметр не всегда обязателен. Например, в данном случае параметр PRIMARY определяет файл, содержащий логическое начало базы данных и системных таблиц. В базе данных может быть только один первичный (PRIMARY) файл. Если этот параметр пропущен, то первичным считается первый файл в списке. По умолчанию файлам типа primary присваивается расширение.mdf. Опции разделены вертикальной чертой - это означает возможность выбора из двух альтернативных вариантов. Многоточие означает путь.

 

Удаление базы данных

Удаление базы данных приводит к освобождению всего занимаемого ею пространства во всех файлах, где эта база данных находилась, а также к удалению всех содержащихся в ней объектов:

а) удаление базы данных в графическом режиме предполагает выполнение следующих действий:

щелкните мышью имя базы данных, которую хотите удалить;

выберите в контекстном меню команду Delete; в появившемся окне сообщений подтвердите необходимость удаления базы данных.

б) для удаления базы данных с помощью Transact-SQL достаточно выполнить командуDATABASE имя_базы_данных

 

Восстановление базы данных

Если появилась необходимость перенести сохраненную резервную копию на компьютер, это тоже выполняется посредством специальных возможностей MS SQL Server 2005:

выберите базу данных для проведения операции восстановления;

в контекстном меню выберите строку Tasks-Restore;

появится страница с общими параметрами восстановления; укажите источник и местоположение резервной копии;

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

подтвердите выбранные операции.

Система сообщит об успешном восстановлении. Если появится сообщение о невозможности восстановления базы данных и система предложит изменить скрипт, необходимо выполнить это требование (в отдельном окне со скриптом).

 

Модификация таблицы

 

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

Стандартный синтаксис команды ALTER TABLE следующий:

 

ALTER TABLE имя_таблицы [MODIFY] [COLUMN имя_столбца]

[ТИП ДАННЫХ|NULL NOT NULL] [RESTRICT|CASCADE]

[DROP] [CONSTRAINT имя_ограничения]

[ADD] [COLUMN] определение столбца

 

а) модификация элементов таблицы. Атрибуты столбца задают правила представления данных в столбце. С помощью команды ALTER TABLE можно менять атрибуты столбца. Под атрибутами здесь понимается следующее: тип данных в столбце; длина, точность или масштаб данных в столбце; разрешение или запрет иметь в столбце значение NULL.

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

добавьте столбец, задав ему атрибут NULL (это значит, что в столбце не обязательно должны присутствовать данные);

введите данные в каждую строку нового столбца таблицы;

убедившись, что столбец содержит значение в каждой из строк таблицы, можно изменить атрибут столбца на NOT NULL;

б) изменение столбцов. При изменении столбцов таблиц нужно учитывать целый ряд моментов. Общие правила следующие:

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

ширину столбца можно уменьшить только до наибольшей длины имеющихся в этом столбце значений;

для столбцов с числовыми данными ширину всегда можно увеличить;

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

для числовых данных можно увеличивать или уменьшать число десятичных знаков;

тип данных в столбце обычно можно изменить.

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

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

 

ALTER TABLE имя_таблицыCONSTRAINT имя_таблицы_PK

PRIMARY KEY (имя_поля1, имя_поля2)

 

г) внешние ключи можно назначить таблице следующим образом:

 

ALTER TABLE имя_таблицыCONSTRAINT ID_FK FOREIGN REY (имя_поля)

REFERENCES имя_таблицы (имя_поля)

 

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

 

DROP TABLE имя_таблицы [RESTRICT|CASCADE]

 

Если используется опция RESTRICT, либо на таблицу ссылается представление или ограничение, используемый для удаления оператор DROP возвратит ошибку. При использовании опции CASCADE будет выполнено удаление не только самой таблицы, но и всех ссылающиеся на таблицу представлений и ограничений.

 

Задание на лабораторную работу

 

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

Создание базы данных. TradeCompany

а) создание базы данных в графическом режиме.

При этом способе выполняются следующие действия:

запустите программу MS SQL Server 2005 посредством основного меню: Пуск-Все программы- MS SQL Server 2005-SQL Server Management Studio;

в появившемся диалоговом окне предлагается ввести имя сервера, оно установлено по умолчанию, щелкните на Connect;

в следующем окне Object Explorer на строке Databases из контекстного меню выберите New Database;

появится окно, в котором надо ввести имя создаваемой базы данных и ОК; в нижней части этого окна в таблице располагается информация о файлах базы данных (разверните окно, чтобы видеть все колонки таблицы); здесь кнопка Add предназначена для случая, если база данных располагается не в одном файле;

закройте окно; убедитесь, что ваша база появилась в списке Database;

удалите базу данных (команда Delete из контекстного меню);

б) создание базы данных посредством команды Transact-SQL.

В окне Object Explorer щелкните на кнопку New Query. В правой части экрана появится окно редактора запросов. Введите в него команду на создание базы данных. Если команда выполнилась успешно, в нижней части окна должно появиться сообщение: Command (s) completed successfully.

В отличие от графического режима, чтобы увидеть в списке баз данных только что созданную базу, необходимо на строке Database из контекстного меню выбрать Refresh.

Создание и модификация таблиц базы данных TradeCompany

Таблицы тоже можно создавать несколькими способами:

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

откройте папку своей базы данных; отметьте строку Tables и в контекстном меню выберите New Table;

в появившемся окне введите наименования столбцов таблицы, выберите тип данных, установите (или уберите) флажок Allows Null; внизу окна установите свойства столбцов;

закройте текущее окно, подтвердите сохранение изменений, введите имя таблицы;

б) нажмите в окне Object Explorer кнопку New Query, откроется окно редактора команд, вводя в это окно команды создания таблицы (создайте таким способом таблицы PRODUCTS_TBL, ORDERS_TBL), нажимая кнопку Execute (выполнение команды), создайте еще одну таблицу;

в) чтобы изменить структуру таблицы, выбираем из контекстного меню Modify Table;

г) для быстрого изучения команд SQL, выделите таблицу базы данных, выберите в контекстном меню Script Table As/ … /New Query Editor Window (вместо многоточия выберите требуемую команду), появится окно редактора команд, в котором можно просмотреть заготовки изучаемых скриптов на создание, удаление, обновление и т.д.

Просмотрите диаграмму связей созданной базы данных (раздел Diagrams)

Требования к отчету

 

Отчет по работе выполняется на бумажном носителе и должен содержать:

описание вариантов создания базы данных;

обоснование выбора типов данных для полей таблиц;

определение первичных и внешних ключей таблиц;

команды создания структур таблиц.

 

1.7 Контрольные вопросы

 

Что означает аббревиатура SQL?

Перечислите типы команд SQL.

Какие способы создания базы данных вы знаете, в чем их отличия?

Сколько файлов используется для базы данных?

Для чего предназначен журнал транзакций?

Объясните создание резервной копии и восстановление базы данных.

Объясните способы удаления базы данных.

Дайте определение реляционной базы данных.

В каких объектах хранится исходная информация базы данных?

Что называется полем таблицы, записью таблицы?

Какая команда определяет структуру таблицы?

Дайте определение первичного ключа таблицы.

Объясните назначение внешних ключей.

С помощью какой команды можно модифицировать структуру таблицы?

 

Заполнение таблиц данными

 

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

Возможны следующие ситуации:

а) ввод новых данных в таблицу. Для ввода новых данных в таблицу используется оператор INSERT. Он имеет вид:

INTO имя_таблицы ('значение1', 'значение2', [NULL])

 

Согласно представленному здесь синтаксису оператора INSERT, в список VALUES необходимо поместить значения для всех столбцов соответствующей таблицы. Значения в списке разделяются запятыми. Символьные значения и значения дат должны быть заключены в кавычки. Для числовых значений и пустых значений, задаваемых ключевым словом NULL, кавычки не нужны. Должны быть указаны значения для всех столбцов таблицы;

б) ввод данных в определенные столбцы таблицы. Имеется возможность ввести данные не во все, а только в определенные столбцы. В этом случае в операторе INSERT вместе со списком значений VALUES нужно указать и список соответствующих им столбцов:

INTO имя_таблицы ('СТОЛБЕЦ1', 'СТОЛБЕЦ2') ('ЗНАЧЕНИЕ1', 'ЗНАЧЕНИЕ2');

 

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

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

 

INSERT INTO имя_схемы.имя_таблицы

VALUES ('ЗНАЧЕНИЕ!', NULL, 'ЗНАЧЕНИЕЗ'}

 

Удаление данных из таблиц

 

Для удаления данных из таблиц используется команда DELETE. Команда DELETE предназначена не для того, чтобы удалять значения отдельных столбцов, а для того, чтобы удалять целые записи. Оператор DELETE следует применять с осторожностью - слишком уж безотказно он работает. Чтобы удалить одну или несколько записей из таблицы, используйте следующий синтаксис оператора DELETE.

 

DELETE [FROM] имя_таблицы

[WHERE УСЛОВИЕ];

 

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

 

Задание на лабораторную работу

 

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

Измените структуру таблицы ORDERS_TBL - добавьте в нее ключевое поле ORDERS_ID (это идентификатор строки в таблице). Это связано с тем, что иногда необходимо однозначно определять каждую сделку по проданному товар).

Заполните данными все таблицы вашей базы данных. используйте все возможности ввода данных:

графический режим: выделить таблицу, в контекстном меню выбрать Open Table;

выделить таблицу и из контекстного меню выбрать Insert To-New Query Editor Window:

выбрать меню New Query.

В таблицах CUSTOMER_TBL и PRODUCT_TBL должно быть не менее пяти записей, в таблице ORDERS_TBL - не менее 15-ти (покупка всевозможных товаров всеми клиентами в различные дни).

Добавьте в таблицу ORDERS_TBL столбец TOTAL_COST (в этом столбце будет храниться стоимость проданного товара). Заполните этот столбец значениями NULL.

Используя команду UPDATE, рассчитайте значения поля TOTAL_COST как произведение цены товара (PRODUCT_TBL.COST) на количество проданного товара (ORDERS_TBL.QTY).

Измените цену некоторых товаров в таблице PRODUCT_TBL. Измените количество проданного товара. Убедитесь, что данные столбца ORDERS_TBL.TOTAL_COST изменяются.

Создайте таблицу MANUFACTURER_TBL, в которой будут храниться данные о производителях товаров (поля таблицы выберите по своему усмотрению). В соответствии с этим изменится структура таблицы PRODUCT_TBL: первичный ключ таблицы MANUFACTURER_TBL будет внешним ключом таблицы PRODUCT_TBL. Внесите эти изменения.

таблицу SELLER_TBL, в которой будут храниться данные о торговых агентах (продавцах). В соответствии с этим изменится таблица ORDERS_TBL - в нее надо добавить первичный ключ таблицы SELLER_TBL (надо учитывать, кем совершена сделка по продаже).

Просмотрите диаграмму связей преобразованной реляционной схемы базы данных.

Заполните созданные таблицы и поля данными.

Удалите записи о продажах, совершенных в марте текущего года.

Требования к отчету

Отчет по работе выполняется на бумажном носителе и должен содержать:

команды добавления данных в таблицы базы данных;

все команды изменения реляционной схемы данных;

команды обновления данных в таблицах базы данных;

диаграмму связей базы данных;

листинги таблиц с данными.

 

2.5 Контрольные вопросы

 

Что такое DML?

Какие команды манипуляции данными вы знаете?

Что означает определение поля NOT NULL?

Что указывается в списке VALUES команды INSERT?

Можно ли вводить данные только в определенные столбцы таблицы?

Можно ли командой UPDATE менять данные в нескольких таблицах?

Можно ли командой UPDATE менять данные в нескольких столбцах одной таблицы?

Всегда ли необходимо использовать условие в операторе удаления записей таблицы?

В чем отличие DDL от DML?

В чем отличие структуры таблицы и данных таблицы?

 

Оператор SELECT

 

Этот раздел языка представлен только одной командой, однако для пользователя реляционной базы данных язык запросов к данным (DQL) является самой главной частью SQL. Этой командой является команда SELECT. Команда, имеющая множество опций и необязательных параметров, используется для построения запросов к реляционным базам данных. С ее помощью можно конструировать запросы любой сложности - от самых общих до специальных и от самых простых до невероятно сложных.

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

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

В операторе SELECT ключевое слово SELECT используется в совокупности с ключевым словом FROM для того, чтобы организовать извлечение данных из базы данных в удобном для чтения формате. Часть запроса, заданная ключевым словом SELECT, определяет источник отбора данных. Синтаксис простого оператора SELECT следующий:

 

SELECT * | ALL | DISTINCT [СТОЛБЕЦ1, СТОЛБЕЦ2]

FROM ТАБЛИЦА1 [, ТАБЛИЦА2]

 

За ключевым словом SELECT в запросе следует список столбцов, значения которых вы хотели бы видеть в результате запроса. За ключевым словом FROM следует список таблиц, из которых должны извлекаться данные. Звездочка (*) используется для указания того, что в результате запроса должны быть показаны значения всех столбцов таблицы. Опция ALL используется тогда, когда нужно показать все значения столбца, включая и повторяющиеся. Опция DISTINCT используется для того, чтобы повторения исключить. Из этих опций используемой по умолчанию опцией является ALL, которую поэтому указывать не обязательно. Обратите внимание на то, что имена столбцов в списке, следующем за ключевым словом SELECT, разделяются запятыми, точно так же, как имена таблиц, следующие за ключевым словом FROM.

 

Задание на лабораторную работу

 

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

Выполните поиск информации в отдельных таблицах базы данных TradeCompany:

список всех клиентов фирмы;

список всех товаров, имеющихся на фирме;

список товаров, цена которых не превышает указанную сумму;

список счетов, выданных в текущем месяце;

список товаров определенного наименования.

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

список клиентов фирмы, приобретавших товар определенного вида;

список всех клиентов и их счетов;

список счетов, выставленных определенному клиенту;

список клиентов и наименование товаров, приобретенных ими в определенную дату;

список товаров и их количество, приобретенных определенным клиентом;

найдите дату счета, выписанного определенному клиенту за приобретение определенного товара;

список всех клиентов, которые не приобретали определенный товар.

 

Требования к отчету

 

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

 

3.6 Контрольные вопросы

 

К какому разделу SQL относится оператор SELECT?

Назовите обязательные составляющие оператора SELECT.

Для всех ли данных в выражении ключевого слова WHERE обязательно нужно использовать кавычки?

Можно ли в выражении для ключевого слова WHERE задать несколько условий?

Допустимы ли кавычки для значений числовых полей?

Можно ли оператор SELECT использовать без ключевого слова FROM?

Что такое псевдоним таблицы?

При связывании таблиц должны ли они связываться в том же порядке, в каком они указаны в выражении ключевого слова FROM?

При использовании в операторе запроса таблицы-связки обязательно ли выбирать в запросе ее столбцы?

Можно ли связывать в запросе не один, а несколько столбцов таблиц? Какая часть оператора SQL задает условия связывания таблиц?

Что будет, если в запросе указать выборку из двух таблиц, но не связать их?

Что такое рекурсивное связывание?

 

Операторы специального вида

 

Под операторами специального вида мы будем понимать использование различных ключевых слов в операторах выборки:

IN определяет список значений, в который должно входить значение поля. Набор значений для оператора IN заключается в круглые скобки, значения разделяются запятыми. Например, предложение WHERE ADDRESS IN (‘Samal’, ‘Tastak’) выберет из списка сотрудников, проживающих в микрорайонах Самал и Тастак;

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

LIKE применим только к символьным полям, с которыми он используется, чтобы находить подстроки. В качестве условия он использует специальные символы: символ подчеркивания _ - замещает любой одиночный символ; знак процента % - замещает последовательность любого числа символов. LIKE удобен при поиске значений - можно использовать ту часть значения, которую помните;

IS NULL - используется для проверки равенства данного значения значению NULL. Например, WHERE PHONE IS NULL - поиск сотрудников, не имеющих телефонов.

Для всех этих операций можно построить их отрицания (ключевое слово NOT), чтобы рассмотреть противоположные условия. Ключевое слово NOT используется с операциями следующим образом: NOT BETWEEN, IS NOT NULL, NOT IN, NOT LIKE.

 

Сортировка вывода

 

Обычно требуется, чтобы выводимые данные были как-то упорядочены. Выводимые данные можно упорядочить с помощью выражения, связанного с ключевым словом ORDER BY. Упорядочение, задаваемое с помощью ключевого слова ORDER BY, по умолчанию будет упорядочением по возрастанию, обозначается A-Z (А-Я) в случае сортировки имен. Алфавитное упорядочение по убыванию соответствует порядку Z-А (Я-А). Для числовых значений между 1 и 9 упорядочение по возрастанию обозначается 1-9, а по убыванию - 9-1. Синтаксис оператора SELECT, использующего выражение ORDER BY, следующий:

 

SELECT [ALL | * | DISTINCT СТОЛБЕЦ1, СТОЛБЕЦ2]

FROM ТАБЛИЦА1 [, ТАБЛИЦА2] [УСЛОВИЕ1 | ВЫРАЖЕНИЕ1]

[AND УСЛОВИЕ2 | ВЫРАЖЕНИЕ2] BY СТОЛБЕЦ1|ЦЕЛОЕ_ЗНАЧЕНИЕ [ASC|DESC];

 

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

В SQL предлагаются и некоторые сокращения. Столбец, указанный в списке ключевого слова ORDER BY, можно заменить числом. ЦЕЛОЕ_ЗНАЧЕНИЕ является значением, замещающим действительное имя столбца и соответствующим порядку столбца в списке после ключевого слова SELECT.

 

Задание на лабораторную работу

 

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

Выберите для работы базу данных TradeCompany.

Получите список клиентов, адреса которых начинаются на определенную букву.

Подсчитайте количество клиентов фирмы.

Подсчитайте количество наименований товаров.

Получите список счетов, выданных в течение последних трех месяцев.

Получите список клиентов, общая сумма счетов которых превышает указанную сумму.

Получите список клиентов и наименование товаров, приобретенных ими в течение двух последних месяцев.

Подсчитайте общую сумму товара, приобретенного определенными клиентами.

Подсчитайте количество всех счетов, выписанных определенному клиенту.

Предположим, что не все продавцы имеют телефоны (приведите данные в соответствие с этим предположением). Подсчитайте число продавцов, имеющих телефоны.

Подсчитайте сумму зарплат всех продавцов (пусть зарплата каждого продавца вычисляется как некоторый % от сделки).

Подсчитайте среднюю зарплату продавцов.

Найдите зачение максимальной и минимальной зарплаты.

 

Требования к отчету

 

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

 

4.6 Контрольные вопросы

 

Какие операторы специального вида вы знаете? Назначение каждой операции?

Какие операции отношения используются в условиях отбора данных?

Какие логические операции используются в операциях отбора данных?

Что такое агрегатная функция?

Играет ли роль тип данных при использовании функции COUNT.

Чтобы группировать данные по столбцу, должен ли этот столбец быть указан в списке ключевого слова SELECT?

Для чего используются псевдонимы таблиц?

 

Задание на лабораторную работу

 

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

представление, содержащее только описание товара и его цену;

представление, содержащее название клиента и его адрес;

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

Создайте хранимые процедуры:

для добавления новых строк в таблицу PRODUCTS_TBL;

для поиска товаров, цена которых превышает определенную сумму;

для увеличения цены всех товаров на 12%;

для поиска счетов клиентов с максимальной общей суммой;

для формирования списка клиентов с их телефонами;

для формирования списка товаров с их ценами;

проверьте работу всех созданных процедур.

 

Требования к отчету

 

Отчет по работ