Операторы манипулирования данными
Лабораторная работа № 7.
Работа со средствами языка SQL.
SQL (Structured Query Language – язык структурированных запросов) является информационно-логическим языком для описания, изменения и извлечения данных, хранимых в реляционных базах данных. SQL является механизмом связи между прикладным программным обеспечением и базой данных. Изначально SQL был единственным способом работы пользователя с базой данных. Современные СУБД предоставляют пользователю развитые средства визуального интерактивного взаимодействия с базой данных.
Каждое предложение SQL – это либо запрос данных из базы, либо обращение к базе данных, которое приводит к изменению данных в ней. Язык SQL содержит некоторую совокупность операторов, инструкций и вычисляемых функций.
Операторы SQL делятся на следующие типы:
операторы определения данных (CREATE – создает объект базы данных, ALTER – изменяет объект, DROP – удаляет объект);
операторы манипулирования данными (SELECT – считывает данные, удовлетворяющие заданным условиям, INSERT – добавляет данные и др.);
операторы определения доступа к данным;
операторы управления транзакциями (под транзакцией понимается неделимая последовательность операций, переводящая базу данных из одного состояния в другое таким образом, что если по каким-либо причинам, одно из действий транзакции невыполнимо, база данных возвращается в исходное состояние, которое было до начала транзакции).
|
|
|
Назначение оператора SELECT состоит в выборке и отображении данных одной или нескольких таблиц БД.
Синтаксис оператора SELECT:
SELECT [DISTINCT| ALL] {[ * <СПИСОК СТОЛБЦОВ>]} FROM <СПИСОК ТАБЛИЦ> [WHERE <предикат-условие выборки или соединения;>] [GROUP BY <список полей результата>] [HAVING <предикат-условие для группы>] [ORDER BY <список полей, по которым требуется упорядочить вывод>]
Поясним каждую фразу данного оператора.
Фраза SELECT:
• наличие слова ALL (по умолчанию) означает, что в результирующую таблицу включаются все строки, удовлетворяющие условиям запроса, что может привести к появлению в результирующей таблице одинаковых строк;
• ключевое слово DISTINCT предназначено для приведения таблицы в соответствие с принципами теории отношений, где предполагается отсутствие дубликатов строк;
• символ "*" определяет очень часто встречаемую ситуацию, когда в результирующий набор включаются все столбцы из исходной таблицы
Во фразе FROM задается перечень исходных таблиц запроса.
Во фразе WHERE определяются условия отбора строк результата или условия соединения строк исходных таблиц, подобно операции условного соединения в реляционной алгебре.
|
|
|
В качестве условий отбора могут быть использованы следующие операторы:
• сравнения " = , <>, >, <, >=, <=" – для сравнения результатов вычисления двух выражений; более сложные выражения строятся с помощью логических операторов AND, OR, NOT;
• BETWEEN А AND В – предикат истинен, когда вычисленное значение выражения попадает в заданный диапазон;
• IN – предикат истинен тогда, когда сравниваемое значение входит в множество заданных значений;
• LIKE и NOT LIKE – предикаты, смысл которых противоположен, требуют задания шаблона, с которым сравнивается заданное значение;
• IS NULL – предикат, применяющийся для выявления равенства значения некоторого атрибута неопределенному значению:
• EXIST и NOT EXIST, используемые во встроенных подзапросах.
Во фразе GROUP BY задается список полей группировки.
Во фразе HAVING задаются предикаты-условия, накладываемые на каждую группу.
Во фразе ORDER BY задается список полей упорядочения результата, то есть список полей, который определяет порядок сортировки в результирующей таблице.
Для создания в Access SQL-запроса выберите команду меню Вид, Режим SQL или щелкните по стрелке на кнопке Вид на панели управления и выберите элемент Режим SQL. Откроется диалоговое окно, в котором выведена инструкция SQL, соответствующая построенному запросу.
|
|
|
Рассмотрим ряд простых запросов.
Запрос 1
Вывести номера телефонов менеджеров.
Результат такого запроса должен содержать только два столбца: ФИО и Телефон, поэтому сам запрос должен выглядеть следующим образом:
SELECT ФИО, Телефон FROM Менеджер;
Запрос 2
Вывести сведения о товаре Компьютер. Запрос будет выглядеть
следующим образом:
SELECT * FROM Товар WHERE Наименование = 'Компьютер';
Ответ на такой запрос будет содержать только одну строку с наименованием Компьютер и всех столбцов со сведениями о данном товаре.
Запрос 3
Вывести сведения о товарах с ценой от 1000 до 3000 рублей.
Запрос будет выглядеть следующим образом:
SELECT * FROM Товар WHERE Цена BETWEEN 1000 AND 3000;
Запрос 4
Вывести сведения о товарах в виде, отсортированном по столбцу Наименование в порядке возрастания.
Запрос будет выглядеть следующим образом:
SELECT *
FROM Товaр
ORDER BY Наименование;
Агрегатные функции языка
В стандарте языка SQL определено несколько агрегатных функций:
• COUNT – возвращает количество значений в указанном столбце;
• SUM – возвращает сумму значений в указанном столбце;
|
|
|
• AVG – возвращает усредненное значение в указанном столбце;
• MIN – возвращает минимальное значение в указанном столбце;
• МАХ – возвращает максимальное значение в указанном столбце.
В качестве операнда данных функций может использоваться наименование только одного столбца, и все они возвращают единственное значение.
С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При вызове всех перечисленных выше функций, кроме функции COUNT (*), осуществляется исключение всех пустых значений» только после этого операция применяется к оставшимся значениям столбца. Функция COUNT (*) осуществляет подсчет всех строк таблицы независимо от того, какие значения в них находятся.
Запрос 5
Подсчитать и вывести общее количество товаров. Запрос будет выглядеть следующим образом:
SELECT COUNT (*) AS count
FROM Товар;
Определить среднюю цену товаров.
Запрос будет выглядеть следующим образом:
SELECT AVG(Цена) AS Средняя_цена FROM Товар;
Группирование результатов
Часто встречаются ситуации, когда в отчет необходимо поместить и промежуточные результаты, опирающиеся на вычисления обобщенных групповых значений. Для применения агрегатных функций в подобных случаях предполагается предварительная операция группировки. Суть операции группировки состоит в том, что все множество строк таблицы разбивается на группы, в каждой из которых собираются строки, имеющие одинаковые значения атрибутов, которые заданы в списке группировки.
Обработка такой информации реализуется путем применения агрегатных функций уже к каждой отдельной группе и выдаче полученных итогов.
В языке SQL для осуществления операции группировки в оператор SELECT включается фраза GROUP BY. Запрос, в котором присутствует фраза GROUP BY, называется группирующим запросом, а столбцы, перечисленные в этой фразе, называются группирующими столбцами.
Определить количество реализаций каждого товара.
Запрос будет выглядеть следующим образом:
SELECT Товар, COUNT (*) AS count
FROM Реализация
GROUP BY Товар
ORDER BY Товар;
Определить товары с количеством реализаций > 10.
SELECT Товар, COUNT (Товар) AS Количество
FROM Реализация
GROUP BY Товар
HAVING COUNT (Товар) > 1
ORDER BY Товар.
Операторы манипулирования данными
Оператор ввода данных INSERT
Оператор ввода данных INSERT имеет следующий синтаксис:
INSERT INTO имя таблицы [(<список столбцов>)] VALUES (<список значений}>
Подобный синтаксис позволяет ввести только одну строку в таблицу.
Например, введем новый товар в таблицу Товар БД Склад:
INSERT INTO Товар (Наименование) VALUES ('Монитор');
Задание списка столбцов необязательно тогда, когда, как в данном случае, вводится строка с заданием значений всех столбцов. При таком вводе предполагается, что информация будет вводиться в том порядке, в котором они описаны в операторе CREATE TABLE. Так как в рассматриваемом примере вводится полная строка, то можно не задавать список столбцов, ограничиться только заданием перечня значений, в этом случае оператор ввода будет выглядеть следующим образом:
INSERT INTO Товар
VALUES ('Монитор', '1000');
Между списком имен столбцов и списком значений должно быть следующее соответствие:
• количество элементов в обоих списках должно быть одинаковым;
• между положением элементов в списках должно быть строгое соответствие, которое определяется слева направо: первый элемент одного списка соответствует первому элементу второго списка и т. д.;
• типы данных соответствующих элементов списков должны быть одинаковые и принадлежать к одному и тому же домену.
Оператор удаления данных DELETE
Оператор удаления данных позволяет удалить одну или несколько строк из таблицы в соответствии с условиями, которые задаются для удаляемых строк. Синтаксис оператора DELETE следующий:
DELETE FROM имя_таблицы
[WHERE условия_Отбора]
Условия отбора определяют, какие строки должны быть удалены.
Если условия отбора не задаются, то из таблицы удаляются все существующие в ней строки. Однако это не означает, что удаляется вся таблица. Исходная таблица остается, но она остается пустой, незаполненной.
Условия отбора в части WHERE имеют тот же вид, что и условия фильтрации в операторе SELECT. Эти условия определяют, какие строки из исходного отношения будут удалены.
Например, удаление товара Наушники из таблицы Товар можно выполнить оператором:
DELETE FROM Товар
WHERE Наименование = 'Наушники';
Операция обновления данных UPDATE
Операция обновления данных UPDATE требуется тогда, когда требуется изменить содержимое базы данных. Данный оператор, также как и другие операторы обновления информации БД, применяется к одной конкретной таблице и имеет следующий формат:
UPDATE имя_таблицы
SET имя_столбца1 = новое_значение1 [,имя__столца2 = новое_значение2...]
[WHERE условие_отбора]
Здесь в предложении UPDATE указывается имя обновляемой таблицы, в предложении SET указываются имена столбцов и новые данные. Новые данные должны быть совместимы с теми данными, которые они призваны заменить.
Часть WHERE является необязательной, также как и в операторе DELETE. Она играет здесь ту же роль, что и в операторе DELETE, –позволяет отобрать строки, к которым будет применена операция модификации. Если условие отбора не задается, то операция модификации будет применена ко всем строкам таблицы.
Рассмотрим операцию обновления данных таблицы базы данных Склад. Предположим, что решено все начисления менеджерам увеличить на 10 %. Операция обновления информации в связи с этим будет выглядеть следующим образом:
UPDATE Менеджер
SET Заработная_плата = Заработная_плата * 1.1;
В том случае, когда модификацию информации необходимо производить выборочно, требуется использование предложения WHERE. Допустим, что в БД Склад следует произвести изменение данных, поскольку повысилась цена товара Компьютер.
Для решения поставленной задачи необходимо выполнить следующую операцию:
UPDATE Товар SET Товар.Цена = 10000
WHERE Товар.Наименование = 'Компьютер'
Операторы определения данных
Создание таблиц
Создание таблицы осуществляется посредством оператора CREATE TABLE. Его упрошенная версия выглядит следующим образом:
CREATE TABLE Имя_таблииы
(Имя_столбца Тип_данъи [NULL | NOT NULL ] [,...]}
Оператор такого вида приведет к созданию таблицы с именем <Имя_таблицы>, которая будет содержать столько столбцов, сколько их задано в операторе. При определении столбца необходимо задать его имя, тип данных, к которому будут относиться значения этого столбца, а также определить, можно ли в качестве значения рассматриваемого столбца использовать ключевое слово NULL. Ключевым словом NULL помечается такой столбец, который может содержать неопределенные значения.
Определения столбцов первичных ключей отношений всегда должны содержать ключевые слова NOT NULL.
Для того чтобы создать таблицу Клиент БД Склад, необходимо использовать оператор вида
CREATE TABLE Клиент (ФИО VARCHAR (20) NOT NULL, Адрес VARCHAR (80) NOT NULL, Телефон VARCHAR (11) NOT NULL);
Полное описание оператора CREATE TABLE должно включать средства поддержки целостности данных. Такие средства представляют собой спецификаторы, позволяющие задать ограничения для предотвращения попыток нарушить согласованность данных.
Базовое определение оператора CREATE TABLE имеет следующий формат:
CREATE TABLE имя_таблицы
({ имя_столбца тип_даных [NOT NULL] [UNIQUE]
[DEFAULT значение по умолчанию]
[CHECK (условие проверки на допустимость) [,...] }
[PRIMARY KEY (список столбцов),]
{[UNIQUE (список столбцов),] [,...]}
{[FOREIGN KEY {список столбцов внешних ключей)
REFERENCES имя родительской таблицы [(список столбцов
ключей-кандидатов)],
[MATCH {PARTIAL | FULL}
[ON UPDATE правило ссылочной целостности]
[ON DELETE правило ссылочной целостности]] [,...]}
{[CHECK (условие проверки на допустимость)] [,...]})
Перепишем оператор создания таблицы Клиент БД Склад следующим образом:
CREATE TABLE Клиент (
ФИО VARCHAB (20) NOT NULL,
Адрес VAHCHAR (80) NOT NULL,
Телефон VARCHAR (11) NOT NULL);
PRIMARY KEY (Код_клиента),
FOREIGN KEY ФИО REFERENCES Менеджер
ON UPDATE CASCADE
ON DELETE CASCADE);
Учитывая то, что операторы языка SQL транслируются в режиме интерпретации, создавать таблицы необходимо в определенном порядке:
вначале родительские, а затем дочерние. В противном случае появятся сообщения об ошибке в том случае, когда в определении дочерней таблицы будут присутствовать ссылки на еще не существующую родительскую таблицу.
Обновление таблиц
В уже созданную таблицу изменения могут быть внесены с помощью оператора ALTER TABLE, который имеет следующий обобщенный формат:
ALTER TABLE имя_таблицы
[ADD [COLUMN] имя столбца тип даных [NOT NULL] [UNIQUE]
[DEFAULT значение по умолчанию] [CHECK (условие проверки на
допустимость)]]
[DROP [COLUMN] ] имя_столбца [RISTRICT | CASCADE]]
[ADD [CONSTRAINT [имя ограничения)] ограничение]
[DROP CONSTRAINT имя ограничения [RISTRICT I CASCADE]]
[ALTER [COLUMN] SET DEFAULT значение по умолчанию]
[ALTER (COLUMN] DROP DEFAULT]
В данном формате предусмотрены возможности для выполнения ряда действий:
• добавить новый столбец в существующую таблицу – ADD COLUMN;
• удалить столбец из существующей таблицы – DROP COLUMN;
• добавить в определение таблицы новое ограничение – ADD CONSTRAINT;
• удалить из определения таблицы существующее ограничение – DROP CONSTRAINT;
• задать для существующего столбца значение по умолчанию – ALTER [ COLUMN ] SET DEFAULT;
• отменить установленное для столбца значение по умолчанию ALTER [ COLUMN ] DROP DEFAULT.
Добавить в таблицу Менеджер столбец Email, содержащий символьный тип данных, можно с помощью оператора:
ALTER TABLE Менеджер
ADD Email varchar (30) NOT NULL;
Удаление таблиц
Таблица может быть удалена из базы данных оператором
DROP TABLE имя таблицы [RESTRICT I CASCADE].
Ключевые слова RESTRICT и CASCADE используются для определения условий удаления таблицы в том случае, если в базе данных присутствуют ее дочерние таблицы. Ключевое слово RESTRICT при наличии в базе данных зависимых от удаляемой таблицы объектов вызовет отмену удаления.
Ключевое слово CASCADE в этой ситуации вызовет автоматическое удаление всех объектов базы данных, существование которых зависит от данной таблицы.
Для удаления таблицы Клиент необходимо записать следующий запрос:
DROP TABLE Клиент
Практические задания
1. Необходимо создать SQL-запросы, изложенные в теоретических сведениях лабораторной работы.
2. Создать произвольные запросы на выборку, на выборку с условием, запрос на обновление и добавление записи.
Дата добавления: 2020-04-08; просмотров: 371; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!