Базы данных Structured Query Language SQL Введение

Зарегистрируйтесь, чтобы просмотреть полный документ!

Базы данных Structured Query Language (SQL)

Введение SQL: n Structured Query Language = Структурированный язык запросов n Средство работы с реляционными БД n Имеются стандарты SQL 2 n Включает DDL, DML, QL n Включает дополнительные средства управления параллельной обработкой данных и администрирования БД n Термины «Отношение» = «таблица» , «атрибут» = «столбец» CREATE TABLE staff(sno VARCHAR(15), lname VARCHAR(23), salary DECIMAL (7, 2)); INSERT INTO staff VALUES (‘SG 6’, ‘Петров’, 3500) SELECT sno, lname, salary FROM staff WHERE salary > 1000

Введение Типы данных (выборочно) n CHAR(n) – символьная строка n VARCHAR(n) – символьная строка переменной длины n NVARCHAR(n) – символьная строка переменной длины в кодировке Unicode n DECIMAL(n, m) – число с фиксированной точкой n BIT – для хранения логических данных (0 или 1) n INT - целое n DATETIME – для хранения даты и времени n FLOAT(n) – для вещественных числе Примеры констант: n 56. 61 – вещественное число n 2. 9 E-4 – вещественное число n ‘произвольный текст’ – символьная константа n ’ 11 -mar-2000’ – символьная константа, содержащая дату

Введение Операции (выборочно): n Арифметические: * умножить, / разделить, + сложить (для строк - конкатенация), - вычесть, % получение остатка от деления, n Логические: OR – логическое ИЛИ, AND – логическое И, NOT – отрицание; операции сравнения <, >, <=, >=, <>, =, n Операции над множествами: IN, NOT IN Операнды выражений: n Константы n Переменные – идентификатор, начинающийся с @ n Имена столбцов – должны быть идентификаторами, в противном случае использовать [ ] или “ ”. n Функции n Подзапросы

Введение Некоторые полезные функции: n DAY(date) – возвращает число из заданной даты n MONTH(date) – возвращает номер месяца из указанной даты n YEAR(date) – возвращает номер года из указанной даты n RTRIM(string) – удаляет завершающие пробелы в строке n LTRIM(string) – удаляет лидирующие пробелы в строке

Оператор SELECT [ALL | DISTINCT] (<список полей> | *) FROM <список таблиц> [WHERE <предикат условия>] [GROUP BY <список полей результата>] [HAVING <предикат условия>] [ORDER BY <список полей>] ALL – все строки, в том числе повторяющиеся (нарушение реляционной модели) DISTINCT – только различающиеся строки * - все столбцы из исходных таблиц запроса FROM – исходная таблица, операция декартово произведения или операция соединения таблиц WHERE – условия выборки или соединения GROUP BY – образуются группы строк, имеющих одно и то же значение в указанных столбцах HAVING – условие фильтрации групп (а не отдельных строк). Выполняется после группирования выходных данных ORDER BY – список столбцов, по которым должно выполняться упорядочивание (ASC – по возрастанию, DESC – по убыванию)

Оператор SELECT n n Имена полей результата выборки SELECT [ФИО] AS name, [Дисц] AS course, [Оценка] AS mark FROM E Псевдонимы таблиц SELECT DISTINCT a. [ФИО] FROM E a WHERE a. [Дисц] = ‘БД’

Оператор SELECT Концептуальная схема базы данных для примеров E =<[ФИО], [Дисц], [Оценка]> - результаты сдачи экзаменов G=<[ФИО], [Группа]> - состав группы P=<[Группа], [Дисц]> - набор дисциплин, по которым надо сдавать экзамены группам Получить список всех групп без повторений, где должны пройти экзамены SELECT DISTINCT [Группа] FROM P Получить список студентов, сдавших экзамен по БД на 5 SELECT [ФИО] FROM E WHERE [Дисц] = ‘БД’ AND [Оценка] = 5

Оператор SELECT Получить список студентов, которым надо сдавать экзамены SELECT [ФИО], [Дисц] FROM G, P WHERE G. [Группа] = P. ”Группа” Получить список студентов имеющих несколько двоек SELECT DISTINCT a. [ФИО] FROM E a, E b WHERE a. [ФИО] = b. [ФИО] AND a. [Дисц] <> b. [Дисц] AND a. [Оценка] <=2 AND b. [Оценка] <=2 Найти студентов одного потока SELECT [ФИО] FROM G WHERE [Группа] IN (‘ВТ-100’, ‘ВТ-200’, ‘ВТ-300’) Получить список студентов, сдававших экзамены, в алфавитном порядке SELECT [ФИО], [Оценка] FROM E ORDER BY [ФИО] Получить список студентов, сдававших экзамены, в порядке убывания оценок SELECT [ФИО], [Оценка] FROM E 5 Петров ORDER BY [Оценка] DESC, [ФИО] ASC 5 Сидров 4 Иванов 4 Кузнецов

Оператор SELECT Определитель NULL - указывает, что значение атрибута неизвестно, т. е. какое-либо значение отсутствует. Использование null приводит к трехзначной логике. Всегда ложь: a. field = NULL <> NULL Имеется специальный предикат, он равен true, если операнд есть null <операнд> IS NULL Найти студентов, которые еще не распределены по группам SELECT [ФИО] FROM G WHERE G. [Группа] IS NULL

Агрегатные функции (обобщающие функции): n COUNT({[ALL | DISTINCT] <имя атрибута> | *}) – количество строк с непустыми значениями атрибута. Если *, то сумма всех строк таблицы, не зависимо от содержания. Для числовых и символьных атрибутов n SUM([ALL | DISTINCT] <имя атрибута>) – сумма значений. Для числовых атрибутов n AVG([ALL | DISTINCT] <имя атрибута>) – среднее значение. Для числовых атрибутов n MIN([ALL | DISTINCT] <имя атрибута>) – минимально значение. Для числовых и символьных атрибутов n MAX([ALL | DISTINCT] <имя атрибута>) – максимальное значение. Для числовых и символьных атрибутов Применяются к результату выборки, либо к группам (если есть)

Агрегатные функции Сколько раз студенты сдавали экзамен SELECT COUNT(*) FROM E SELECT COUNT(*) AS number FROM E Получить количество дисциплин, по которым есть 2 (ошибочный вариант) SELECT COUNT([Дисц]) as count FROM E WHERE [Оценка] = 2 Получить количество дисциплин, по которым есть 2, без повторения SELECT COUNT(DISTINCT [Дисц]) as count FROM E WHERE [Оценка] = 2 Общее количество баллов, набранных студентом SELECT SUM([Оценка]) AS summa FROM E WHERE [ФИО] = ‘Петров П. П. ’ Количество дисциплин и общая сумма баллов одного студента SELECT COUNT([Дисц]) AS count, SUM([Оценка]) AS summa FROM E WHERE [ФИО] = ‘Петров П. П. ’ Характеристики сдачи экзаменов SELECT MIN([Оценка]) AS minimum, MAX([Оценка]) AS maximum, AVG([Оценка]) AS average FROM E WHERE [ФИО] = ‘Петров П. П. ’

Группировка результатов Исходные таблицы Порядок выполнения операций Результат операции над таблицами FROM Оставшиеся строки WHERE Группы Указываются столбцы, по которым выполняется группировка строк z 2 a 2 b GROUP BY 5 u 1 m 1 HAVING Оставшиеся группы 2 a 2 b Имена столбцов, указанные в GROUP BY, или использующие их функции, в т. ч. агрегирующие z 5 ORDER BY Сортированный результат z 2 a 2 b 5 SELECT Проекция 2 5

Группировка результатов Подсчитать по каждой дисциплине количество студентов, сдававших экзамен SELECT [Дисц], COUNT(*) AS counter FROM E GROUP BY [Дисц] По каждой дисциплине посчитать количество положительных оценок SELECT [Дисц], COUNT(*) FROM E WHERE [Оценка] > 2 GROUP BY [Дисц] После группировки Иванов БД 4 Петров БД 2 Иванов ОС 5 Сидоров ОС 3

Группировка результатов Определить для каждой академической группы и дисциплины количество успешно сдавших экзамен и средний бал по дисциплине SELECT G. [Группа], E. [Дисц], COUNT(*) AS counter, AVG([Оценка]) AS average FROM G, E WHERE E. [ФИО] = G. [ФИО] AND E. [Оценка] > 2 GROUP BY G. [Группа], E. [Дисц] После группировки Иванов ВТ-100 Иванов БД Петров ВТ-100 Петров БД 3 Иванов ВТ-100 Иванов ОС 5 Кузнецов ВТ-200 Кузнецов БД 3 Результат 5 Группы самого низкого уровня ВТ-100 БД 2 4 ВТ-100 ОС 1 5 ВТ-200 БД 1 3

Группировка результатов Для отбора групп используется HAVING. Агрегатная функция в HAVING вычисляется для каждой группы Получить список тех групп, в которых по одной дисциплине на экзаменах получено более одной двойки SELECT DISTINCT G. [Группа] FROM E, G WHERE E. [ФИО] = G. [ФИО] AND E. [Оценка] = 2 GROUP BY G. [Группа], E. [Дисц] HAVING COUNT(*) > 1

Вложенные запросы Внутренний запрос генерирует таблицу, используемую в предикате внешнего запроса в предложении WHERE или HAVING Получить список тех, кто сдал все положенные экзамены не на двойку SELECT [ФИО] FROM E a WHERE [Оценка] > 2 GROUP BY [ФИО] HAVING COUNT(*) = (SELECT COUNT(*) FROM G, P WHERE G. [Группа] = P. [Группа] AND G. [ФИО] = a. [ФИО]) Здесь вложенный запрос определяет количество экзаменов, которые должен сдавать студент a. [ФИО]

Вложенные запросы С вложенными запросами используются предикаты: n EXISTS (<подзапрос>) – равен true, если результат подзапроса не пуст n <выражение> <оператор сравнения> ALL (<подзапрос>) – равен true, если оператор возвращает true для всех возвращаемых подзапросом значений n <выражение> <оператор сравнения> ANY (<подзапрос>) – равен true, если оператор возвращает true хотя бы для одного значения, возвращаемого подзапросом (эквивалент SOME) n <выражение> [NOT] IN (<подзапрос>) – возвращает true, если результат подзапроса содержит результат выражения

Вложенные запросы Получить список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал его SELECT [ФИО] FROM G a, P WHERE G. [Группа] = P. [Группа] AND [Дисц] = ‘БД’ AND NOT EXIST (SELECT [ФИО] FROM E WHERE [ФИО] = a. [ФИО] AND [Дисц] = ‘БД’) Здесь подзапрос возвращает либо одно значение (a. [ФИО] того, кто сдал экзамен), либо ни одного

Вложенные запросы Получить список студентов, которые сдали все экзамены на оценки не ниже 4 SELECT DISTINCT E. [ФИО] FROM E a WHERE 4 <=ALL (SELECT E. [Оценка] FROM E WHERE E. [ФИО] = a. [ФИО]) Получить список дисциплин, которые были сданы студентами из группы ВТ-100 SELECT DISTINCT E. [Дисц] FROM E WHERE [ФИО] IN (SELECT [ФИО] FROM G WHERE [Группа] = ‘ВТ-100’)

Вложенные запросы Получить список студентов группы, в которой учится Иванов И. И. SELECT G. [ФИО] FROM G WHERE G. [Группа] = (SELECT G. [Группа] FROM G WHERE G. [ФИО] = ‘Иванов И. И. ’) Здесь подзапрос всегда возвращает единственное значение – название группы, в которой числится ‘Иванов И. И. ’ Получить список студентов, которые сдали экзамен на оценку выше средней, с указанием отличия и дисциплины SELECT [ФИО], [Дисц], [Оценка] – (SELECT AVG(Оценка) FROM E) AS [Разность] FROM E WHERE [Оценка] > (SELECT AVG[Оценка] FROM E)

Соединения FROM <источник данных> В общем виде предложение FROM может содержать различные источники данных <источник данных> : : = <имя таблицы> [[AS] <псевдоним>] | <подзапрос> [AS] <псевдоним>| <соединение таблиц> | … <соединение таблиц> : : = <источник данных> <тип соединения><источник данных> ON <предикат соединения> | <источник данных> CROSS JOIN <источник данных>

Соединения <тип соединения> : : = [INNER | { { LEFT | RIGHT | FULL} [OUTER] }] JOIN INNER – (по умолчанию) внутреннее соединение. Выбираются пары строк, для которых выполняется условие соединения, заданное предикатом (ON <предикат> ) LEFT – левое внешнее соединение: в результат включается внутреннее соединение таблиц, к которому добавляются строки из левой таблицы, не вошедшие во внутреннее соединение. Строки из левой таблицы, не вошедшие во внутреннее соединение, дополняются значениями NULL в соответствии со схемой результирующей таблицы. RIGHT – правое внешнее соединение: в результат включается внутреннее соединение таблиц, к которому добавляются строки из правой таблицы, не вошедшие во внутреннее соединение. Строки из правой таблицы, не вошедшие во внутреннее соединение, дополняются значениями NULL в соответствии со схемой результирующей таблицы. FULL – полное открытое соединение: в результат включается внутреннее соединение таблиц, к которому добавляются строки из левой таблицы, не вошедшие во внутреннее соединение, и строки из правой таблицы, не вошедшие во внутреннее соединение, которые дополняются значениями NULL в соответствии со схемой результирующей таблицы.

Соединения Концептуальная схема базы данных для примеров E =<[ФИО], [Дисц], [Оценка]> - результаты сдачи экзаменов G=<[ФИО], [Группа]> - состав группы P=<[Группа], [Дисц]> - набор дисциплин, по которым надо сдавать экзамены группам Получить список студентов, которым надо сдавать экзамены SELECT [ФИО], [Дисц] FROM G INNER JOIN P ON G. [Группа] = P. [Группа] Получить список студентов имеющих несколько двоек SELECT DISTINCT a. [ФИО] FROM E a INNER JOIN E b ON a. [ФИО] = b. [ФИО] WHERE a. [Дисц] <> b. [Дисц] AND a. [Оценка] <=2 AND b. [Оценка] <=2 Получить план сдачи экзаменов для всех студентов SELECT G. [ФИО], P. [Дисц] FROM G LEFT JOIN P ON G. [Группа] = P. [Группа] Результат левого соединения Петров ВТ-100 БД Петров ВТ-100 ОС Сидров ВТ-200 NULL

Соединения Получить результаты сдачи сессии для всех студентов (включая тех, кто должен был сдавать, но не сдавал экзамены) SELECT G. [ФИО], P. [Дисц], E. [Оценка] FROM G INNER JOIN P ON G. [Группа] = P. [Группа] LEFT JOIN E ON G. [ФИО] = E. [ФИО] AND P. [Дисц] = E. [Дисц]

Соединения Концептуальная схема базы данных «Библиотека» Publications = <isbn, title, author> (ISBN – уникальный код издания, название, автор) Readers = <reader_id, name> (номер читательского билета, фамилия читателя) Exemplars = <inv, isbn, reader_id, date_out> (инвентарный номер – естественный первичный ключ, ISBN издания, номер читательского билета, дата выдачи)

Соединения Для всех экземпляров книг и для всех читателей показать, какие книги есть у читателей и где находятся книги SELECT Readers. name, Exemplars. inv FROM Readers FULL JOIN Exemplars ON Readers. reader_id = exemplars. reader_id 4 Иванов 112 1 -561 4 1. 01. 2001 4 Иванов 201 5 -318 4 2. 10. 2002 5 Петров NULL NULL 414 2 -456 NULL

Операторы манипулирования данными Добавление строк INSERT INTO <имя таблицы> [(<список столбцов>)] VALUES (<список значений>) INSERT INTO Exemplars (inv, isbn, reader_id, date_out) VALUES (202, ‘ 5 -132’, 533, Get. Date()) Если значения в порядке объявления столбцов INSERT INTO Exemplars VALUES (202, ‘ 5 -132’, 533, Get. Date()) Вставка из другой таблицы INSERT INTO Readers (name) SELECT student_name FROM Students

Операторы манипулирования данными Удаление DELETE FROM <имя таблицы> [WHERE <предикат>] Удалить одну запись DELETE FROM G WHERE [ФИО] = ‘Петров’ Удалить студентов, имеющих более двух двоек DELETE FROM G WHERE G. [ФИО] IN (SELECT E. [ФИО] FROM E WHERE [Оценка]=2 GROUP BY E. [ФИО] HAVING COUNT(*) >=2)

Операторы манипулирования данными Изменение значений в строке UPDATE <имя таблицы> SET <имя столбца> = <выражение>, … n [WHERE <предикат>] Изменение в одной строке UPDATE E SET [Оценка] = 5, [Дисц] = ‘ОС’ WHERE [ФИО] = ‘Петров’ AND [Дисц] = ‘БД’ UPDATE E SET [Оценка] = [Оценка] + 1 WHERE [Дисц] = ‘БД’ AND [Оценка] < 5 Использование подзапроса UPDATE E SET E. [Оценка] = [Оценка] + 1 WHERE E. [ФИО] IN (SELECT G. [ФИО] FROM G WHERE G. [Группа] IN (‘ВТ-100’, ‘ВТ-200’, ‘ВТ-300’))

Создание базы данных и таблиц Создание базы данных CREATE DATABASE University Установление текущей базы данных USE University Создание таблицы (сокращенный вариант) CREATE TABLE <имя таблицы> (<описание элемента таблицы> [, … n]) <описание элемента таблицы> : : = <определение столбца> | [… опускаем] <определение столбца> : : = <имя столбца> <тип данных> [<значение по умолчанию>] [<ограничения столбца>] <значение по умолчанию> : : = DEFAULT {<литерал> | NULL | [… опускаем]} <ограничение столбца> : : = NOT NULL […опускаем] CREATE TABLE E ( [Дисц] CHAR(20) NOT NULL, [ФИО] CHAR(40) NOT NULL, [Оценка] INT DEFAULT 5) Изменение таблицы (сокращенный вариант) ALTER TABLE <имя таблицы> { ADD <определение столбца> | ALTER <имя столбца> TO <новое имя столбца> | DROP <имя столбца> [… опускаем]}

Скачать презентацию Базы данных Structured Query Language SQL Введение

4 Язык SQL.ppt

Количество слайдов: 31