Презентация Язык SQL онлайн

Содержание слайда: Базы данных Structured Query Language (SQL)

Содержание слайда: Введение SQL: Structured Query Language = Структурированный язык запросов Средство работы с реляционными БД Имеются стандарты SQL2 Включает DDL, DML, QL Включает дополнительные средства управления параллельной обработкой данных и администрирования БД Термины «Отношение» = «таблица», «атрибут» = «столбец»

Содержание слайда: Введение Типы данных (выборочно) CHAR(n) – символьная строка VARCHAR(n) – символьная строка переменной длины NVARCHAR(n) – символьная строка переменной длины в кодировке Unicode DECIMAL(n,m) – число с фиксированной точкой BIT – для хранения логических данных (0 или 1) INT - целое DATETIME – для хранения даты и времени FLOAT(n) – для вещественных чисел

Содержание слайда: Введение

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

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

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

Содержание слайда: Оператор SELECT

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

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

Содержание слайда: Соединения 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 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

Содержание слайда: Агрегатные функции Агрегатные функции (обобщающие функции): COUNT({[ALL | DISTINCT] <имя атрибута> | *}) – количество строк с непустыми значениями атрибута. Если *, то количество всех строк таблицы, не зависимо от содержания. Для числовых и символьных атрибутов SUM([ALL | DISTINCT] <имя атрибута>) – сумма значений. Для числовых атрибутов AVG([ALL | DISTINCT] <имя атрибута>) – среднее значение. Для числовых атрибутов MIN(<имя атрибута>) – минимальное значение. Для числовых и символьных атрибутов. [ALL | DISTINCT] можно использовать, но не имеет смысла 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 [ФИО] = ‘Петров П.П.’

Содержание слайда: Группировка результатов

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

Содержание слайда: Группировка результатов Определить для каждой академической группы и дисциплины количество успешно сдавших экзамен и средний бал по дисциплине SELECT G.[Группа], E.[Дисц], COUNT(*) AS counter, AVG([Оценка]) AS average FROM G JOIN E ON E.[ФИО] = G.[ФИО] WHERE E.[Оценка] > 2 GROUP BY G.[Группа], E.[Дисц]

Содержание слайда: Группировка результатов Для отбора групп используется HAVING. Агрегатная функция в HAVING вычисляется для каждой группы

Содержание слайда: Вложенные запросы Внутренний запрос генерирует таблицу, используемую в предикате внешнего запроса в предложении WHERE или HAVING

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

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

Содержание слайда: Вложенные запросы Получить список студентов, которые сдали все экзамены на оценки не ниже 4 SELECT DISTINCT a.[ФИО] FROM E a WHERE 4 <=ALL (SELECT E.[Оценка] FROM E WHERE E.[ФИО] = a.[ФИО])

Содержание слайда: Вложенные запросы Получить список студентов группы, в которой учится Иванов И.И. SELECT G.[ФИО] FROM G WHERE G.[Группа] = (SELECT G.[Группа] FROM G WHERE G.[ФИО] = ‘Иванов И.И.’) Здесь подзапрос всегда возвращает единственное значение – название группы, в которой числится ‘Иванов И.И.’

Содержание слайда: Операторы манипулирования данными Добавление строк INSERT INTO <имя таблицы> [(<список столбцов>)] VALUES (<список значений>) INSERT INTO Exemplars (inv, isbn, reader_id, date_out) VALUES (202, ‘5-132’, 533, GetDate()) Если значения в порядке объявления столбцов INSERT INTO Exemplars VALUES (202, ‘5-132’, 533, GetDate()) Вставка из другой таблицы 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 <определение столбца> | DROP <имя столбца> [… опускаем] } Переименовать столбец EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';