1)Оператор ввода данных insert
INSERT INTO имя таблицы [(<список столбцов>)] VALUES
(<список значений}
Пример(добавление нового студента в группу)
INSERT INTO s2 ( ФИО, Группа) VALUES ('Сидоров П.П.', '02-КТ-
21');
Пример(упрощенный)
INSERT INTO S2
VALUES ('Сидоров П.П.', '02-КТ-21');
2) Оператор удаления данных delete
DELETE FROM имя_таблицы
[WHERE условия_Отбора]
Пример (удалить все строки из таблицы s1)
DELETE FROM s1;
Пример (удалить студента Крисса P.O из таблицы s2)
DELETE FROM s2
WHERE ФИО = 'Крисс P.O.';
3)Операция обновления данных update
UPDATE имя_таблицы
SET имя_столбца1 = новое_значение1 [имя__столбца2 =
новое_значение2...]
[WHERE условие_отбора]
Пример(увеличиваем начисления специалистам из таблицы r3)
UPDATE r3
SET Начисления = Начисления * 1.1;
Пример (меняем оценку по физике студентке Думской М.Т)
UPDATE s1 SET s1.Оценка = 5
WHERE s1.ФИО = 'Думская М.Т.' AND
s1.Дисциплина = 'ФИЗИКА';
Билет 26
Запись реляционных операций на SQL.
Базовые операции реляционных баз данных:
выборка(Restriction)
проекция(Projection)
соединение(Join)
объединение(Union)
1)Операция выборки позволяет получить все строки (записи) либо часть строк одной таблицы.
Пример(Получить все строки таблицы Country)
SELECT * FROM country
Пример(Получить подмножество строк таблицы Country,удовлетворяющее условию Currency = "Dollar")
SELECT * FROM country
WHERE currency = "Dollar".
2)Операция проекции позволяет выделить подмножество столбцов таблицы.
Пример (Получить список денежных единиц)
SELECT currency FROM country.
Комбинация Restriction и Projection
Пример(Найти денежную единицу Японии)
SELECT currency FROM country WHERE country = "Japan".
Пример(Получить фамилии работников, которых зовут "Roger")
SELECT first_name, last_name
FROM employee
WHERE first_name = "Roger"
3)Операция соединения позволяет соединять строки из более чем одной таблицы (по некоторому условию) для образования новых строк данных.
Пример(Получить список руководителей проектов)
SELECT first_name, last_name, proj_name
FROM employee, project
WHERE emp_no = team_leader
4)Операция объединения позволяет объединять результаты отдельных запросов по нескольким таблицам в единую результирующую таблицу. Предложение UNION объединяет вывод двух или более SQL-запросов в единый набор строк и столбцов.
Пример(Получить список работников и заказчиков, проживающих во Франции)
SELECT first_name, last_name, job_country
FROM employee
WHERE
UNION
SELECT contact_first, contact_last, country customer
Билет 27
SQL. Запросы с подзапросами.
Подзапросы - запросы, которые вложены в другие запросы. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса.
Пример
Мы знаем имя продавца: Motika, но не знаем значение его поля snum, и хотим извлечь все порядки из таблицы Порядков.
SELECT * FROM Orders
WHERE snum = (SELECT snum FROM Salespeople WHERE sname = 'Motika');
Получаем следующее:
SELECT * FROM Orders
WHERE snum = 1004;
Подзапрос должен выбрать один и только один столбец, тип данных этого столбца должен совпадать с тем значением, с которым он будет
сравниваться в предикате.
Правильная форма записи предиката:
<выражение><оператор><подзапрос>
< snum > < = > < (SELECT snum FROM Salespeople WHERE sname = 'Motika'); >
Использование оператора IN
Если результатом подзапроса становится группа строк, то следует использовать оператор IN, осуществляющий выбор одного значения из указываемого множества.
SELECT * from tbl1 WHERE
f2 IN (SELECT f2 FROM tbl2 WHERE f1=1);
В этом случае предикат принимает значение TRUE, если хотя бы одно из значений, возвращаемых подзапросом, удовлетворяет условию.
Операторы BETWEEN, LIKE, и ISNULL не могут использоваться с подзапросами.
Подзапросы в предложении HAVING
Подзапросы в HAVING могут использовать свои собственные агрегатные функции если они не производят многочисленных значений или использовать GROUP BY или HAVING.
Пример(Нахождение заказчиков с оценкой выше среднего в San Jose)
SELECT rating, COUNT ( DISTINCT cnum )
FROM Customers GROUP BY rating
HAVING rating > ( SELECT AVG (rating)
FROM Customers
WHERE city = “San Jose” );
Простые (некоррелирующие) подзапросы вычисляются от внутренних запросов к внешним, в соответствии с общепринятыми правилами скобочной записи.
Коррелирующие подзапросы - это особый тип вложенных запросов. Они вычисляются от внешних подзапросов к внутренним: выборка каждой строки при выполнении внешнего запроса сопровождается выполнением для нее вложенного запроса. Параметры выполнения вложенного запроса зависят от данных, которые находятся в выбранной строке внешнего запроса, поэтому такие запросы называются коррелированными.
Пример(выбор в каждом отделе самого высокооплачиваемого сотрудника)
SELECT department_id, last_name, salary
FROM employee x
WHERE salary >= ALL
(SELECT salary
FROM employee
WHERE employee.department_id=x.department_id)
ORDER BY 1
Билет 28
SQL. Запросы с агрегатными функциями.
З апросы могут вычислять обобщенное групповое значение полей точно также как и значение одного поля с помощью агрегатных функций.
Функции:
COUNT — возвращает количество значений в указанном столбце;
SUM — возвращает сумму значений в указанном столбце;
AVG — возвращает усредненное значение в столбце;
MIN — возвращает минимальное значение в столбце;
МАХ — возвращает максимальное значение в указанном столбце
Используются в предложении SELECT запроса
Аргумент – имя поля.
Возвращают только одно значение.
Только числовые поля могут использоваться с SUM и AVG.
С COUNT, MAX, и MIN, могут использоваться и числовые или символьные поля.
П ример(Подсчитать и вывести общее число кафедр университета)
S ELECT COUNT (*) AS count
FROM kafedra;
Пример(Определить среднее число сотрудников, работающих на кафедрах университета)
S ELECT AVG(Col_sotr) AS avg
FROM kafedra;
Билет 29
SQL. Представления и технологические операции над ними.
Представления — это виртуальные(логические) таблицы, чье содержание выбирается или получается из других таблиц. Они работают в запросах и операторах DML точно также как и основные таблицы, но не содержат никаких собственных данных.
Представление — это фактически запрос, который выполняется(как подзапрос) всякий раз при использовании представления . Вывод запроса при этом в каждый момент становится содержанием представления.
Изменение данных в реальной таблице БД немедленно отражается в содержимом всех представлений, построенных на основании этой таблицы.
Основные приятные особенности представлений:
Упрощение взаимодействия с прикладной программой (сокрытие сложности запросов и структуры таблиц БД)
Обеспечение логической независимости данных
Защитная функция (ограничение действий пользователя только работой с представлениями – своего рода функция интерфейса)
Создание представления: CREATE VIEW <имя> as <запрос> [WITH CHECK OPTION]
Пример:
CREATE VIEW Londonstaff
AS SELECT *
FROM Salespeople
WHERE city = 'London';
Удаление представления: DROP VIEW <имя>
Пример: DROP VIEW Londonstaff;
Обновление(модификация) представлений
Представление может теперь изменяться командами модификации DML(insert, delete, update), но модификация не будет воздействовать на само представление. Команды обновления представления будут перенаправлены к базовой таблице (будут изменяться данные базовой таблицы).
Если указана конструкция WITH CHECK OPTION, то данные, которые не удовлетворяют условию, указанному в конструкции WHERE определяющего запроса представления, не будут добавлены в его базовую таблицу.
Имеются большое количество типов представлений, которые являются доступными только для чтения (необновляемые представления).
Билет 32
Встроенный SQL(+ процедурный SQL)
Встроенный SQL — расширение какого-либо базового языка программирования, позволяющее встраивать команды языка структурированных запросов SQL непосредственно в текст программы. Прежде чем программа, использующая Встроенный SQL, будет откомпилирована компилятором базового языка программирования, она должна быть обработана специальным препроцессором.
Примеры систем встроенного SQL для различных БД и языков программирования: PostgreSql + C/C++ = ECPG, Oracle + C/C++ = Pro*C/C++.
П роцедурный SQL
Transact-SQL - это процедурное расширение языка SQL компаний Microsoft. SQL был расширен такими дополнительными возможностями как:
управляющие операторы,
локальные и глобальные переменные,
различные дополнительные функции для обработки строк, дат, математики и т. п.,
поддержка аутентификации Microsoft Windows
Язык Transact-SQL является ключом к использованию SQL Server. Все приложения, взаимодействующие с экземпляром SQL Server, независимо от их реализации и пользовательского интерфейса, отправляют серверу инструкции Transact-SQL.
PL/SQL (Procedural Language / Structured Query Language) — язык программирования, процедурное расширение языка SQL, разработанное корпорацией Oracle.
PL/SQL встроен в следующие СУБД: Oracle Database, TimesTen и IBM DB2 .
PL/SQL даёт возможность использовать переменные, операторы, массивы, курсоры и исключения. Начиная с версии 8 доступна и объектно-ориентированная модель.
Стандартный SQL является специализированным декларативным языком программирования. PL/SQL же позволяет разработчикам обрабатывать данные в реляционной базе, используя императивный стиль программирования. Операторы SQL могут быть легко вызваны непосредственно из PL/SQL-процедуры, функции или из триггера (иногда с некоторыми ограничениями).
Билет 34
Транзакции в БД
Транзакция - это неделимая, с точки зрения воздействия на СУБД, последовательность операций манипулирования данными. Транзакция выполняется по принципу "все или ничего", т.е. либо транзакция выполняется целиком и переводит базу данных из одного целостного состояния в другое целостное состояние, либо(возникли какие-то проблемы при выполнении) база данных возвращается в исходное состояние, которое было до начала транзакции (происходит откат транзакции).
Пример: перевод денег со счета на счет.
Фиксация транзакции(фиксация сделанных изменений в БД) может производиться принудительно по SQL-оператору или неявно после завершения каждого SQL-оператора(автокоммита).
Новая транзакция начинается с начала каждого сеанса работы с базой данных. Далее все выполняемые SQL-операторы будут входить в одну транзакцию до тех пор, пока не будет выполнен оператор COMMIT WORK(фиксация транзакции) или ROLLBACK WORK(откат транзакции – отмена всех изменений).
Требования к транзакциям: неделимость, согласованность, изолированность, долговечность.
Проблемы параллельного доступа:
Потерянное обновление
В транзакции 1 изменяется значение поля f2, а затем в транзакции 2 также изменяется значение этого поля. В результате изменение, выполненное первой транзакцией, будет потеряно.
«Грязное» чтение
В транзакции 1 изменяется значение поля f2, а затем в транзакции 2 выбирается значение этого поля. После этого происходит откат транзакции 1. В результате значение, полученное второй транзакцией, будет отличаться от значения, хранимого в базе данных.
Неповторяющееся чтение
В транзакции 2 выбирается значение поля f2, затем в транзакции 1 изменяется значение поля f2. При повторной попытке выбора значения из поля f2 в транзакции 2 будет получен другой результат.
Фантомное чтение
В транзакции 2 выполняется SQL-оператор, использующий все значения поля f2. Затем в транзакции 1 выполняется вставка новой строки, приводящая к тому, что повторное выполнение SQL-оператора в транзакции 2 выдаст другой результат. Такая ситуация называется фантомным чтением.
Для предотвращения конфликтов параллельного доступа используется система устанавливаемых для транзакции уровней изоляции и специальных блокировок.
Уровни изоляции устанавливают определенные ограничения на параллелизм и выполняемые действия.
Блокировки - механизмы, применяемые для управления параллельными изменениями данных.