04 Язык SQL.ppt

БД
Имеются стандарты SQL2
Включает DDL, DML, QL
Включает дополнительные средства управления параллельной

обработкой данных и администрирования БД
Термины «Отношение» = «таблица», «атрибут» = «столбец»

CREATE TABLE
staff(sno VARCHAR(15),
lname VARCHAR(23),
salary DECIMAL (7,2));

INSERT INTO staff
VALUES (‘SG6’, ‘Петров’, 3500)

SELECT sno, lname, salary
FROM staff
WHERE salary > 1000

длины
NVARCHAR(n) – символьная строка переменной длины в кодировке Unicode
DECIMAL(n,m) –

число с фиксированной точкой
BIT – для хранения логических данных (0 или 1)
INT - целое
DATETIME – для хранения даты и времени
FLOAT(n) – для вещественных чисел

Примеры констант:
56.61 – вещественное число
2.9E-4 – вещественное число
‘произвольный текст’ – символьная константа
’11-mar-2000’ – символьная константа, содержащая дату

- конкатенация), - вычесть, % получение остатка от деления,
Логические:

OR – логическое ИЛИ, AND – логическое И, NOT – отрицание; операции сравнения <, >, <=, >=, <>, =,
Операции над множествами: IN, NOT IN

Операнды выражений:
Константы
Переменные – идентификатор, начинающийся с @
Имена столбцов – должны быть идентификаторами, в противном случае использовать [ ] или “ ”.
Функции
Подзапросы

возвращает номер месяца из указанной даты
YEAR(date) – возвращает номер года

из указанной даты
RTRIM(string) – удаляет завершающие пробелы в строке
LTRIM(string) – удаляет лидирующие пробелы в строке

данных>
[WHERE ]
[GROUP BY ]
[HAVING ]
[ORDER BY

<список полей>]

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

course, [Оценка] AS mark FROM E
Псевдонимы таблиц SELECT DISTINCT a.[ФИО] FROM E

a WHERE a.[Дисц] = ‘БД’

- результаты сдачи экзаменов
G= - состав группы
P= -

набор дисциплин, по которым надо сдавать экзамены группам

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

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 [ФИО], [Оценка] FROM E
ORDER BY [ФИО]
Получить список студентов, сдававших экзамены, в порядке убывания оценок
SELECT [ФИО], [Оценка] FROM E
ORDER BY [Оценка] DESC, [ФИО] ASC

какое-либо значение отсутствует. Использование null приводит к трехзначной логике.
Всегда ложь: a.field

= NULL NULL = NULL NULL <> NULL
Имеется специальный предикат, он равен true, если операнд есть null <операнд> IS NULL

Найти студентов, которые еще не распределены по группам
SELECT [ФИО] FROM G WHERE G.[Группа] IS NULL

различные источники данных
::= [[AS] ] |

[AS] <псевдоним>| <соединение таблиц> | …

<соединение таблиц> ::= <источник данных> <тип соединения><источник данных> ON <предикат соединения> | <источник данных> CROSS JOIN <источник данных>

| FULL} [OUTER] }] JOIN

INNER – (по умолчанию) внутреннее соединение.

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

результаты сдачи экзаменов
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.[Группа]

Результат левого соединения

должен был сдавать, но не сдавал экзамены)
SELECT G.[ФИО], P.[Дисц], E.[Оценка] FROM

G INNER JOIN P ON G.[Группа] = P.[Группа] LEFT JOIN E ON G.[ФИО] = E.[ФИО] AND P.[Дисц] = E.[Дисц]

– уникальный код издания, название, автор)
Readers = (номер

читательского билета, фамилия читателя)
Exemplars = (инвентарный номер – естественный первичный ключ, ISBN издания, номер читательского билета, дата выдачи)

книги есть у читателей и где находятся книги
SELECT Readers.name, Exemplars.inv FROM

Readers FULL JOIN Exemplars ON Readers.reader_id = exemplars.reader_id

*}) – количество строк с непустыми значениями атрибута. Если *,

то количество всех строк таблицы, не зависимо от содержания. Для числовых и символьных атрибутов
SUM([ALL | DISTINCT] <имя атрибута>) – сумма значений. Для числовых атрибутов
AVG([ALL | DISTINCT] <имя атрибута>) – среднее значение. Для числовых атрибутов
MIN(<имя атрибута>) – минимальное значение. Для числовых и символьных атрибутов. [ALL | DISTINCT] можно использовать, но не имеет смысла
MAX(<имя атрибута>) – максимальное значение. Для числовых и символьных атрибутов. [ALL | DISTINCT] можно использовать, но не имеет смысла

Применяются к результату выборки, либо к группам (если есть)

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 [ФИО] = ‘Петров П.П.’

которым выполняется группировка строк
Имена столбцов, указанные в GROUP BY, или использующие

их функции, в т.ч. агрегирующие

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.[Дисц]

После группировки

Группы самого низкого уровня

Результат

вычисляется для каждой группы
Получить список тех групп, в которых по

одной дисциплине на экзаменах получено более одной двойки
SELECT DISTINCT G.[Группа] FROM E JOIN G ON E.[ФИО] = G.[ФИО] WHERE 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 JOIN P ON G.[Группа] = P.[Группа] WHERE G.[ФИО] = a.[ФИО])
Здесь вложенный запрос определяет количество экзаменов, которые должен сдавать студент a.[ФИО]

если результат подзапроса не пуст
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 [Дисц] = ‘БД’)

Здесь подзапрос возвращает либо одно значение (a.[ФИО] того, кто сдал экзамен), либо ни одного

не ниже 4
SELECT DISTINCT a.[ФИО] FROM E a WHERE 4

(SELECT E.[Оценка] FROM E WHERE E.[ФИО] = a.[ФИО])

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

G.[ФИО] FROM G WHERE G.[Группа] = (SELECT G.[Группа] FROM G WHERE G.[ФИО] =

‘Иванов И.И.’)
Здесь подзапрос всегда возвращает единственное значение – название группы, в которой числится ‘Иванов И.И.’

Получить список студентов, которые сдали экзамен на оценку выше средней, с указанием отличия и дисциплины
SELECT [ФИО], [Дисц], [Оценка] – (SELECT AVG(Оценка) FROM E) AS [Разность] FROM E WHERE [Оценка] > (SELECT AVG(Оценка) FROM E)

значений>)
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

G WHERE [ФИО] = ‘Петров’
Удалить студентов, имеющих две или более двоек
DELETE

FROM G WHERE G.[ФИО] IN (SELECT E.[ФИО] FROM E WHERE [Оценка]=2 GROUP BY E.[ФИО] HAVING COUNT(*) >=2)

= , … 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’))

базы данных 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';