Слайд 1SQL – язык структурированных запросов
Слайд 2Типы команд SQL
DDL – язык определения данных,
DML – язык манипулирования
данными,
DQL – язык запросов,
DCL – язык управления данными,
команды администрирования данных,
команды
управления транзакциями.
Слайд 3Data Definition Language (DDL)
Основные команды языка DDL:
CREATE TABLE
DROP TABLE
ALTER TABLE
CREATE
INDEX
DROP INDEX
ALTER INDEX
Слайд 4Data Definition Language (DDL)
определение данных
Создание таблиц
CREATE TABLE имя таблицы [CHECK(условие)](имя_поля
тип данных [NULL|NOT NULL][,…]), где [CHECK(условие)] – ограничение на значение
столбца.
Например:
CREATE TABLE stud (ФИО varchar(20) NOT NULL,
Дисциплина varchar(20) NOT NULL,
Оценка smallint NOT NULL);
Слайд 5CREATE TABLE
Кроме того должны включаться средства поддержки целостности данных
PRIMARY KEY
FOREIGN KEY ()
ON UPDATE CASCADE
ON DELETE CASCADE
Слайд 6CREATE TABLE
Пример1:
CREATE TABLE Клиент
(КодКлиента INT IDENTITY (1,1) PRIMARY KEY,
Фирма VARCHAR(50) NOT NULL,
ФИО VARCHAR(50) NOT NULL,
Город VARCHAR(50) NOT
NULL,
Телефон CHAR(10) NOT NULL CHECK(Телефон LIKE ‘[1-9][0-9][0-9]- [0-9][0-9]- [0-9][0-9]’))
Слайд 7CREATE TABLE
Пример2:
CREATE TABLE Заказ
(КодЗаказа INT IDENTITY (1,1) PRIMARY KEY,
КодКлиента INT
NOT NULL,
КодТовара INT NOT NULL,
Количество INT NOT NULL DEFAULT 0,
Дата DATETIME NOT NULL DEFAULT GETDATE(),
CONSTRAINT fk_Товар
FOREIGN KEY(КодТовара) REFERENCES Товар,
CONSTRAINT fk_Клиент
FOREIGN KEY(КодКлиента) REFERENCES Клиент)
Слайд 8Изменение структуры таблицы
Для добавления столбцов в таблицу
ALTER TABLE
таблицы> ADD ( )
Возможно изменение описания столбцов
ALTER
TABLE <имя таблицы> MODIFY <имя столбца> <тип данных> <размер/точность>
Слайд 9Возможно изменение описания столбцов, если
столбец пуст, то можно изменить тип
данных и размер/точность;
столбец заполнен, то можно размер/точность можно увеличить, но
нельзя понизить;
ни одно значение столбца не содержит NULL, можно установить NOT NULL.
Разрешается изменять значения по умолчанию.
Слайд 10Data Manipulation Language (DML)
Используется для манипулирования информацией внутри объектов РБД.
Содержит
всего три команды:
INSERT
UPDATE
DELETE
Слайд 11Data Query Language (DQL)
Имеет всего один оператор, но зато какой?!
SELECT
Слайд 12Оператор SELECT
Предназначен для выборки информации из таблиц БД и представления
нужным образом результата.
Для выборки необходимо указать как минимум две
вещи:
что выбрать,
откуда выбрать.
Слайд 13Data Control Language (DCL)
Позволяет управлять доступом к информации, хранящейся внутри
БД,
Используется для создания объектов, связанных с доступом к данным,
Служит для
контроля над привилегиями пользователей
GRANT, REVOKE
Слайд 14Команды управления транзакциями
COMMIT – для сохранения изменений,
ROLLBACK - для отмены
изменений,
SAVEPOINT – для установки точек возврата,
SET TRANSACTION – для установки
режима транзакции
Слайд 15Преимущества языка SQL
Стандартность (стандартизовано международными организациями),
Независимость от конкретной СУБД,
Возможность использования
как для локальных так и для многопользовательских СУБД,
Реляционная основа языка,
а потому простота изучения,
Возможность создания интерактивных запросов,
Слайд 16Преимущества языка SQL (продолжение)
Возможность программного доступа к БД – язык
легко использовать в приложениях,
Возможности, предоставляемые представлениями,
Возможность манипулировать структурой БД,
Поддержка архитектуры
клиент-сервер.
Слайд 17Типы данных языка SQL
Символьные:
char (длина) 1 символ -
1 байт
varchar (длина) символьное поле переменной длины
nchar (длина)
символьное поле UNICODE
nvarchar символьное поле переменной длины UNICODE
UNICODE
1символ = 2 байта
Слайд 18Числовые:
Int - целое 4 байта
Smallint - целое 2
байта
Datetime - дата/время (~до микросекунд)
Smalldatetime- дата/время (~1 минута,
1.01.1900-6.06.2079).
Слайд 19Отсутствующие данные
Для обозначения неизвестного в данный момент времени значения атрибута
используется ключевое слово NULL.
Использования в качестве аргумента функций count и
AVG атрибутов, имеющих значение NULL, дает правильный результат.
Слайд 20Функция IDENTITY
Для типов данных int или decimal можно создать автоинкрементный
столбец, чьи значения будут гарантировано уникальными, но он должен быть
определен как NOT NULL. Такой столбец удобен в качестве первичного ключа.
IDENTITY(n,m), где n-начальное значение, m-приращение.
Слайд 21Получение информации о типах данных
Список всех используемых типов данных можно
узнать из системной таблицы systypes
SELECT * FROM systypes
Слайд 22Приступим к изучению оператора SELECT
SELECT [ALL | DISTINCT] {* |
[имя столбца [AS новое имя]]} [,…n]
FROM имя таблицы [[AS]
псевдоним] [,…n]
[WHERE <условие поиска>] [GROUP BY имя столбца [,…n] [HAVING <условие для группы>]
[ORDER BY имя столбца [,…n]]
Слайд 23ЧТО, СТРАШНО?
Нет, все достаточно просто, если разобраться.
Начнем с простого случая,
когда данные извлекаются из одной таблицы.
Итак, что главное.
Слайд 24SELECT что вывести
(какие столбцы)
FROM откуда вывести (из каких таблиц)
Примеры
Выбрать
номера зачеток и фамилии всех студентов
SELECT N_зачетки, ФИО
FROM Студент;
2. Выбрать
все данные о преподавателях из таблицы преподаватели
SELECT *
FROM Преподаватели
Слайд 25Вычисляемые поля
SELECT [список полей,] выражение [AS имя поля результата]
FROM …
Следует
помнить, что полученный результат не сохраняется в таблице.
Слайд 26Сортировка выбранных данных – предложение ORDER BY
Предложение ORDER
BY должно быть последним в операторе SELECT.
Сортировать можно по нескольким
столбцам.
По умолчанию сортировка по возрастанию (ASC).
Для сортировки по убыванию необходимо в конце указать ключевое слово DESCENDING сокращенно DESC
ПРИМЕРЫ:
1. Вывести по алфавиту фамилии всех студентов и номера их зачеток
SELECT ФИО, N_зачетки
FROM Студент
ORDER BY ФИО;
Слайд 27ПРИМЕРЫ:
2. Вывести список студентов по убыванию значений поля «год рождения»
SELECT
ФИО, N_зачетки, год_рождения
FROM Студент
ORDER BY ФИО, год_рождения DESC ;
Слайд 28Предложение WHERE для фильтрации записей в соответствии с условием
Существует 5
основных типов условий поиска:
Сравнение
Попадание в заданный диапазон
Принадлежность множеству,
Соответствие строкового значения
заданному шаблону
Проверка на значение NULL
Слайд 29СРАВНЕНИЕ
WHERE логическое выражение
Например:
SELECT *
FROM Ведомость
WHERE Оценка>3
Логическое выражение может быть сложным
Слайд 30Порядок действий при вычислении выражений
Выражение вычисляется слева направо.
Сначала вычисляется то,
что в скобках.
Логические операции выполняются в следующей последовательности сначала NOT
затем AND потом OR.
Слайд 31Диапазон в WHERE
Оператор BETWEEN используется для поиска значения внутри диапазона
BETWEEN
минимальное значение диапазона AND максимальное значение диапазона (граничные значения включаются)
NOT
BETWEEN – для поиска значений вне диапазона
Слайд 32Диапазон в WHERE
ПРИМЕР:
SELECT Наименование, Цена
FROM Товар
WHERE Цена BETWEEN 500 AND
1500
Слайд 33Принадлежность множеству
Оператор IN(список заданных значений) проверяет соответствие списку заданных значений,
NOT
IN – наоборот несоответствие.
Например:
SELECT *
FROM Ведомость
WHERE оценка IN(4,5);
Слайд 34Соответствие строкового значения заданному шаблону
Используется оператор LIKE ”шаблон”.
В шаблоне используются
следующие символы:
% -заменяет любое количество любых символов;
_-заменяет один символ;
[]-предлагает набор
символов;
[^]-предлагает все символы кроме указанных.
Слайд 35Например:
Найти студентов, у которых в номере телефона:
Третья цифра 8
…WHERE Студент.Телефон
LIKE ”_ _8%”
Вторая цифра 5 или 9
WHERE Студент.Телефон LIKE ”_[59]%”
Вторая
цифра 5,6 или 7
WHERE Студент.Телефон LIKE ”_[5-7]%”
Встречается 23
WHERE Студент.Телефон LIKE ”%23% ”
Слайд 36продолжение
WHERE Дисциплина LIKE ”математика% ”
WHERE Дисциплина LIKE ”% математика% ”
WHERE
Дисциплина LIKE ”м%а ”
Во всех этих случаях поиск идет медленно
особенно, если метасимвол используется вначале шаблона.
Слайд 37Проверка на значение NULL
Найти студентов без телефона:
SELECT ФИО, Телефон
FROM Студент
WHERE
Телефон IS NULL;
Найти студентов с телефоном:
…
WHERE Телефон IS NOT NULL;
Слайд 38Вычисления в SELECT
SELECT список полей, выражение AS имя поля-результата.
Например, вывести
список фамилий студентов с указанием только года рождения:
SELECT ФИО, YEAR(дата_рождения)
AS Год_Рождения
FROM СТУДЕНТЫ;
Слайд 39Подведение итогов в запросах.
SELECT имя поля, функция AS имя
результата
FROM имя таблицы;
Пример 1: вычислить общее количество студентов
SELECT COUNT(*) AS
всего_студентов
FROM СТУДЕНТЫ;
Слайд 40Подведение итогов в запросах (продолжение).
Пример 2:вычислить общее количество студентов в
группе 434
SELECT COUNT(ФИО) AS всего_студентов
FROM СТУДЕНТЫ
WHERE группа=‘434’;
Слайд 41Подведение итогов в запросах (продолжение).
А как быть, если надо подсчитать
количество студентов в каждой группе по факультету?
Слайд 42Подведение итогов в запросах (продолжение).
Правильно, нужно использовать группировку.
Слайд 43В чем состоит суть операции группировки?
При группировке все множество записей
таблицы разбивается на группы, в которых собираются записи, имеющие одинаковые
значения атрибутов, которые заданы в списке группировки.
Агрегатные функции вычисляют одиночное значение для всей группы таблицы.
Слайд 44Список агрегатных функций:
COUNT - Количество строк или непустых значений полей,
которые выбрал запрос
SUM- Сумма всех выбранных значений данного поля
AVG -
Среднеарифметическое значение всех выбранных значений данного поля
MIN - Наименьшее из всех выбранных значений данного поля
MAX - Наибольшее из всех выбранных значений данного поля
Слайд 45Агрегатные функции используются подобно именам полей в операторе SELECT, но
с одним исключением: они берут имя поля как аргумент. С
функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями МАХ и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке.
Слайд 46Предложение GROUP BY
GROUP BY имя поля, по которому нужно группировать.
В
нашем примере будет
SELECT Группа, COUNT(ФИО) AS всего_студентов
FROM СТУДЕНТЫ
GROUP BY Группа;
Выражение
COUNT(ФИО) вычисляется по одному разу для каждой группы
Слайд 47А как применить условие к сгруппированным данным?
Например, необходимо найти группы
с количеством студентов > 20.
Слайд 48Предложение HAVING
HAVING логическое выражение
HAVING для групп тоже, что WHERE для
записей.
WHERE фильтрует строки, а HAVING – группы.
WHERE фильтрует до группировки,
а HAVING – после.
Слайд 49Пример 1
Определить группы с количеством студентов более 20
SELECT Группа, COUNT(ФИО)
AS всего_студентов
FROM СТУДЕНТЫ
GROUP BY Группа
HAVING COUNT(ФИО)>20;
Слайд 50Пример 2
Получить список клиентов, сделавших хотя бы 2 заказа.
SELECT Код_клиента,
COUNT(*) AS всего_заказов
FROM Заказы
GROUP BY Код_клиента
HAVING COUNT(*)>=2;
Слайд 51А можно ли использовать WHERE, если есть HAVING?
Конечно, если нужно.
Например, нужно найти группы только 4-го курса, где количество студентов
>20
SELECT Группа, COUNT(ФИО) AS всего_студентов
FROM СТУДЕНТЫ
WHERE Группа LIKE ”_4*”
GROUP BY Группа
HAVING COUNT(ФИО)>20;
Слайд 52ОБЪЕДИНЕНИЯ ТАБЛИЦ
Объединение – это самая мощная операция SQL.
Чтобы извлечь данные,
хранящиеся в нескольких таблицах с помощью одного оператора SELECT, необходимо
их объединить.
Чтобы объединить, нужно указать все необходимые таблицы и «объяснить», как они между собой связаны.
Слайд 53Объединение таблиц с помощью предложения WHERE
Пусть нужно выбрать сведения об
оценках, полученных студентами на экзаменах
SELECT ФИО, N_зачетки, Оценка FROM Студент,
Ведомость;
Результатом такого запроса будет декартово произведение. Например, если в таблице Студент 30 записей, а таблице Ведомость 120 записей, то в результате такого запроса получится 3600 записей.
Как вам такой результат?!
Слайд 54Объединение таблиц с помощью предложения WHERE – правильное решение
SELECT ФИО,
N_зачетки, Оценка FROM Студент, Ведомость
WHERE Студент.N_зачетки= Ведомость. N_зачетки;
Слайд 55WHERE для объединения
Если требуется объединить несколько таблиц, то это записывается
в одном предложении WHERE c использованием операции AND.
Например, пусть
нужно выбрать сведения об оценках, полученных студентами на экзаменах, с указанием дисциплины
Слайд 56РЕШЕНИЕ:
SELECT ФИО, N_зачетки, Дисциплина, Оценка
FROM Студент, Ведомость, Дисциплины
WHERE Студент.N_зачетки=
Ведомость. N_зачетки AND Ведомость.Код_дисциплины=Дисциплины.Код_дисциплины;
Слайд 57Внутреннее соединение с помощью конструкции INNER JOIN ON
Объединение задается в
предложении FROM, вместо WHERE используется предложение ON
Слайд 58Пример:
SELECT ФИО, N_зачетки, Оценка
FROM Студент INNER JOIN Ведомость
ON Студент.N_зачетки=
Ведомость. N_зачетки;
Слайд 59А если таблиц больше двух?
Ничего страшного!
…
FROM tabl1 INNER JOIN
(tabl2 INNER JOIN tabl3 ON tabl2.id2=tabl3.id3) ON tadl1.id1=tabl3.id3
В нашем примере
будет:
Слайд 60В нашем примере будет:
SELECT ФИО, N_зачетки, дисциплина, Оценка
FROM Студент
INNER JOIN (Ведомость INNER JOIN Дисциплины ON Ведомость.Код_дисциплины=Дисциплины.Код_дисциплины)
ON Студент.N_зачетки= Ведомость.
N_зачетки;
Слайд 61Ну, ОЧЕНЬ громоздко! Легко ошибиться в именах таблиц. Есть выход.
В
части FROM оператора SELECT допустимо применять синонимы (псевдонимы) к именам
таблицы, если при формировании запроса нам требуется более чем один экземпляр некоторого отношения. Синонимы задаются с использованием ключевого слова AS, которое может быть вообще опущено. Поэтому часть FROM может выглядеть следующим образом:
FROM Rl AS A, Rl AS В.
Слайд 62Псевдонимы
Например:
SELECT ФИО, N_зачетки, дисциплина, Оценка
FROM Студент AS С INNER
JOIN (Ведомость AS В INNER JOIN Дисциплины AS Д ON
В.Код_дисциплины=Д.Код_дисциплины)
ON С.N_зачетки= В. N_зачетки;
Слайд 63Подзапросы
Применяют, когда в предложении WHERE значение, с которым надо сравнивать
должно быть вычислено в момент выполнения оператора SELECT.
Текст подзапроса заключается
в скобки.
Существует два типа подзапросов:
Скалярный – возвращает единственное значение,
Табличный – возвращает множество значений.
Слайд 64Подзапросы
С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно
внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса
(в предложении WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если вывод подзапроса не пуст.
Слайд 65В сочетании с другими возможностями оператора выбора, такими как группировка,
подзапрос представляет собой мощное средство для достижения нужного результата. Предикат
EXISTS ( SubQuery) истинен, когда подзапрос SubQuery не пуст, то есть содержит хотя бы один кортеж, в противном случае предикат EXISTS ложен.
Предикат NOT EXISTS обратно — истинен только тогда, когда подзапрос SubQuery пуст.
Слайд 66Пример 1
Определить даты, когда продажи превышали средний уровень.
SELECT Дата, Количество
FROM
Продажа
WHERE Количество > (SELECT AVR(Количество) FROM Продажа);
Слайд 67Пример 2
Определить даты, когда среднее количество проданного за день товара
оказалось >25 единиц.
SELECT Продажа.Дата, AVG(Продажа.Количество) AS Среднедневное
FROM Продажа
GROUP BY
Продажа.Дата
HAVING AVG (Продажа.Количество) >25;
Слайд 68Правильно, это простой запрос без подзапроса!
А если требуется найти тоже,
но больше среднего показателя по всем сделкам вообще. Тогда:
…
GROUP BY
Продажа.Дата
HAVING AVG (Продажа.Количество) >
(SELECT AVG(Продажа.Количество) FROM Продажа)
Слайд 69Подзапросы, возвращающие множество значений
Можно добавить данные из одной таблицы в
другую
INSERT INTO табл1
SELECT *
FROM табл2
WHERE поле IN(SELECT …)
Слайд 70Подзапросы, возвращающие множество значений
Можно удалять данные из одной таблицы, по
результатам, полученным в другой
DELETE
FROM студенты
WHERE номер_зачетки
IN (SELECT номер_зачетки
FROM ведомость
WHERE
оценка=2)
Слайд 71Условный оператор и оператор цикла в SQL
Условие: Условный оператор IF
условие оператор или Begin опреатор1,…END else оператор или блок
Select №группы
From группы Where №группы=@группа
if Exists (Select №группы From группы Where № группы =@группа) ELSE Print “Нет такой группы”
Слайд 72Цикл
WHILE (Select avg(цена) FROM товары)
(Select max(цена) FROM товары)>1000 Break ELSE Continue
Слайд 73Комбинированные запросы. Оператор UNION
Например, создать общий список студентов и преподавателей,
фамилии которых начинаются на букву К
SELECT ФИО, Адрес
FROM Студенты
WHERE Студенты.ФИО
LIKE”K%”
UNION
SELECT ФИО, Адрес
FROM Преподаватели
WHERE Преподаватели.ФИО LIKE”K%”;
Слайд 74Язык манипулирования данными DML
Ввод данных:
INSERT INTO имя_таблицы [(список полей)]
VALUES
(список значений)
Например:
INSERT INTO STUD (ФИО,ГРУППА)
VALUES (‘ИвановИИ’, ‘425’)
Слайд 75Язык манипулирования данными DML (продолж.)
Правила:
Если задаются значения всех полей, то
список полей не нужен.
Если столбец при описании таблицы имеет признак
NOT NULL, то ввод данных в это поле в каждой записи обязателен.
Если имеется хотя бы один необязательный столбец, в который не вводится значение, задание списка имен столбцов обязательно.
Можно по запросу извлечь значения из одной таблицы и разместить их в другой:
INSERT INTO табл.куда
FROM табл. Откуда
WHERE условие
Слайд 76Язык манипулирования данными DML (продолж.)
Добавление выбранных данных
INSERT INTO табл.куда (список
полей)
SELECT …
Копирование данных из одной таблицы в другую
SELECT *
INTO новая
табл
FROM старая табл
Слайд 77INSERT INTO
Можно по запросу извлечь значения из одной таблицы и
разместить их в другой
INSERT INTO табл.-куда
FROM табл.-откуда
WHERE л.в.
Слайд 78Язык манипулирования данными DML
Обновление данных:
UPDATE имя таблицы
SET имя_поля1=нов.знач.1 [,имя поля2=нов.знач.2]
и т.д.
[WHERE условие отбора]
Например:
UPDATE Ведомость
SET Ведомость.Оценка=5
WHERE Ведомость.Оценка=2 AND Ведомость.Дисциплина=’23’
Слайд 79Язык манипулирования данными DML
DELETE
FROM имя-таблицы
[WHERE условие отбора]
Если не указать
условие, то удаляются все строки и таблица будет пуста!
Следите за
нарушением целостности!
Слайд 80Представление
это именованная виртуальная таблица, содержимое которой является результатом запроса, заданного
при описании представления.
CREATE VIEW имя_представления AS
SELECT список_столбцов
FROM …
WHERE …
Слайд 81Представление является хранимой инструкцией SELECT.
Представления запрашиваются так же, как таблицы,
и не принимают параметры.
Слайд 82Представление позволяет:
Ограничивать число столбцов;
Ограничивать число строк;
Выводить дополнительные столбцы, преобразованные из
других столбцов;
Выводить группы строк.
Слайд 83Для чего нужны представления:
Обеспечивают независимость пользовательских программ от изменений логической
структуры БД.
Для каждого пользователя может быть создано «свое окно в
БД».
От определенных пользователей м.б. скрыты некоторые данные.
Для повторного использования операторов SQL.
Для упрощения выполнения сложных операций, например, объединения таблиц.
Для изменения форматирования и отображения данных.