ЦЕЛЬ РАБОТЫ
Научиться программировать запросы к базе данных (БД) и освоить основы языка SQL
ПРАКТИЧЕСКОЕ ЗАДАНИЕ
1. Согласно выданному варианту спроектировать схему таблицы данных:
a) присвоить таблице и атрибутам уникальные имена;
b) выбрать тип и точность представления данных для каждого атрибута.
2. Используя какую-либо SQL - среду (например, MS Access), создать или открыть файл БД.
3. Используя оператор CREATE TABLE, создать таблицу.
4. Занести в таблицу 10 строк.
5. Используя оператор INSERT INTO, добавить в таблицу одну запись.
6. Используя оператор DELETE FROM, выполнить запрос на удаление записи согласно заданию.
7. Используя оператор UPDATE, выполнить 2 запроса на обновление записей согласно заданию.
8. Используя оператор CREATE TABLE AS, выполнить запрос на создание новой таблицы на основе выбора записей из имеющейся таблицы согласно заданию.
9. Используя оператор SELECT, выполнить 5 запросов на выборку данных из имеющейся таблицы согласно заданию.
10. Выполнить 4 запроса на выборку данных с использованием агрегатных функций.
11. Выполнить подзапрос согласно заданию.
12. Создать представление согласно заданию.
ТЕОРЕТИЧЕСКОЕ ВВЕДЕНИЕ
Язык SQL поддерживает операции:
· Арифметические операции: +, –, *, /;
· Операторы сравнения: =, <>, <, <=, >, >=;
· Логические операции: AND, OR и NOT.
Приведенные выше операции имеют тот же смысл, что и в других языках программирования.
Предикаты отношений.
1. Предикат сравнения:
Например,
1) (5,9) >= (5,7) True
2) (5,9) >= (7,NULL) False
3) (5,NULL) >= (5,22) Unknown
4) ‘арба‘ < ‘арбуз‘ True
5) ’31.01.05’ < ’01.01.05’ False
2. Предикат BETWEEN (NOT BETWEEN).
Например,
Сотрудники.Дата_рождения BETWEEN ’01.01.65’ AND ’31.12.65’
(выбираются все даты рождения сотрудников, родившихся в 1965 году)
3. Предикат IN (NOT IN).
Например, предикат 5 IN (3,66,5,21) вырабатывает значение True.
4. Предикат LIKE (NOT LIKE).
Например, Сотрудники.ФИО LIKE ’И%’
(выбираются все фамилии сотрудников, начинающихся с буквы 'И')
5. Предикат IS NULL (NOT NULL).
Например, 4 IS NOT NULL принимает значение True
Агрегатные функции.
Язык SQL содержит следующие агрегатные функции:
· COUNT(*) - подсчитывает количество кортежей;
· AVG(X) - вычисляет среднее арифметическое значений атрибута X.
· SUM(X) - вычисляет сумму значений атрибута X;
· MAX(X) - возвращает наибольшее из значений атрибута X;
· MIN(X) - возвращает наименьшее из значений атрибута X.
Создание, модификация и удаление таблиц.
Создание таблицы осуществляется с помощью оператора CREATE TABLE, который имеет вид:
CREATE TABLE имя_таблицы (список атрибутов, их типов и возможно ограничений);
Ограничения могут быть следующие:
· NOT NULL - запрещает задавать значение NULL;
· PRIMARY KEY - разрешает хранить только уникальные и отличные от NULL значения;
· FOREIGN KEY – внешний ключ для связи с другими таблицами;
· CHECK – контролирует ограничение, указанное в скобках и вырабатывающее значения TRUE, FALSE или UNKNOWN.
Например,
CREATE TABLE Сотрудники (ФИО СНАR(25) PRIMARY KEY, Отдел CHAR(15) NOT NULL, Оклад FLOAT(2) CHECK (Оклад> 0));
Модификация существующей таблицы может быть выполнена с помощью оператора ALTER TABLE, который имеет вид:
ALTER TABLE имя_таблицы ADD COLUMN определение_атрибута;
или
ALTER TABLE имя_таблицы DROP COLUMN атрибут.
Например, оператор
ALTER TABLE Сотрудники ADD COLUMN Разряд INT;
добавляет в существующую таблицу атрибут Разряд.
Для удаления таблицы используется оператор
DROP TABLE имя_таблицы;
Внесение данных в таблицы.
Данные заносятся в таблицу с помощью оператора INSERT, который имеет вид:
INSERT INTO имя_таблицы VALUES (список значений атрибутов);
Если данные присваиваются определённым атрибутам, то за именем таблицы в круглых скобках указываются имена соответствующих атрибутов:
INSERT INTO имя_таблицы (список атрибутов) VALUES (список значений атрибутов);
Например,
INSERT INTO Сотрудники VALUES (‘Иванов‘, ‘АСУ‘, ‘инженер‘, 4500);
или
INSERT INTO Сотрудники (ФИО, Отдел, Должность) VALUES (‘Петров‘, ‘АСУ‘, ‘ст.инженер‘);
Выборка данных.
Для выборки данных из БД используется оператор SELECT, который может содержать следующие фразы:
SELECT список атрибутов, выражений или агрегатных функций
AS список псевдонимов атрибутов
FROM таблица [список таблиц]
WHERE условие отбора группы кортежей
GROUP BY имя атрибута, образующего группу
HAVING условие отбора кортежей внутри группы
ORDER BY имя атрибута для сортировки отбираемых кортежей
ASC для сортировки по возрастанию
DESC для сортировки по убыванию (по умолчанию параметр ASC)
Например,
1) SELECT ФИО AS Фамилия, Оклад FROM Сотрудники WHERE Оклад>=3000 ORDER BY ФИО DESC;
2) SELECT Разряд, SUM(Оклад) AS Сумма FROM Сотрудники GROUP BY Разряд HAVING Разряд>10;
Обновление данных.
Для обновления данных в БД используется оператор UPDATE, который может изменять значения одного или нескольких атрибутов и имеет вид:
UPDATE имя_таблицы SET имя_атрибута = новое значение WHERE условие отбора обновляемых кортежей;
Изменения происходят в кортежах, определяемых фразой WHERE. Если фраза WHERE отсутствует, то изменения происходят во всех кортежах таблицы.
Например,
UPDATE Сотрудники SET Оклад=Оклад*1.5 WHERE ФИО = ’Иванов’;
В результате выполнения запроса оклад Иванова в таблице Сотрудники увеличится в полтора раза.
Удаление записей из таблиц.
Для удаления кортежей из таблицы используется оператор DELETE. Удаляемые кортежи определяются фразой WHERE.
DELETE FROM имя_таблицы WHERE условие отбора удаляемых кортежей;
Если фраза WHERE отсутствует, то удаляются все кортежи.
Например,
DELETE FROM Сотрудники WHERE Отдел =’АСУ‘ AND ФИО = ‘Смирнов’;
Многотабличные запросы.
Многотабличные запросы позволяют связывать данные из двух или более таблиц.
Например, имеются две таблицы:
1) Сотрудники
| Код_сотрудника | ФИО | Отдел | Должность |
2) Начисления
| Код_сотрудника | Код_начисления | Начислено |
Между первыми столбцами этих таблиц имеется многозначная зависимость: Код_сотрудника (1) --- >> Код_сотрудника (2)
SELECT Код_сотрудника, ФИО, SUM(Начислено) FROM Сотрудники, Начисления WHERE Сотрудники.Код_сотрудника = Начисления.Код_сотрудника GROUP BY Код_сотрудника;
(в выборке просуммируются все начисления сотрудников).
Подзапросы.
Если внутри одного запроса содержится другой, то последний называется подзапросом. Подзапросы всегда заключаются в круглые скобки и могут использоваться в операторах SELECT, DELETE и UPDATE и, кроме того, могут быть вложенными.
Например,
1)SELECT ФИО FROM Сотрудники WHERE Код_сотрудника IN
(SELECT Код_сотрудника FROM Начисления WHERE Начислено WHERE >2000);
(Выбираются фамилии сотрудников, у которых начисленная сумма больше 2000)
2) SELECT ФИО, Оклад FROM Сотрудники WHERE Оклад IN
(SELECT Оклад FROM Сотрудники WHERE ФИО =‘Смирнов’);
(Выбираются фамилии и оклады сотрудников, у которых оклад такой же, как и у Смирнова.)