БАЗЫ ДАННЫХ ЛЕКЦИЯ 12. тема: ОСНОВЫ ЯЗЫКА SQL Общие сведения SQL структурированный язык запросов (Structured Query Language) - презентация

1 БАЗЫ ДАННЫХ ЛЕКЦИЯ 12

2 тема: ОСНОВЫ ЯЗЫКА SQL

3 Общие сведения SQL структурированный язык запросов (Structured Query Language)

4 Стандарты SQL SQL-1 SQL-89 SQL-2 SQL-92

5 Диалекты SQL Диалект – некоторое подмножество стандарта SQL Каждый диалект включает некоторое расширение стандартного SQL

6 Определение данных СОЗДАНИЕ ТАБЛИЦ CREATE TABLE имяТаблицы (описаниеСтолбца,… [ограничение целостности,…]) описаниеСтолбца :: = имяСтолбца типСтолбца [(размер)] [ограничение целостности]

7 Определение данных ОГРАНИЧЕНИЯ ЦЕЛОСТНОСТИ 1) Ограничение первичного ключа PRIMARY KEY (Столбец,…) CONSTRAINT PK_s_no PRIMARY KEY (s_no) 2) Ограничение ключа-кандидата UNIQUE (Столбец)

8 Определение данных ОГРАНИЧЕНИЯ ЦЕЛОСТНОСТИ 3) Ограничение внешнего ключа FOREIGN KEY (Столбец,…) REFERENCES имяРодительскойТаблицы [(столбцы потенциального ключа)] Задание правил ссылочной целостности ON DELETE (RESTRICT, CASCADE, NO ACTION) ON UPDATE (RESTRICT, CASCADE, NO ACTION)

9 Определение данных ОГРАНИЧЕНИЕ ОБЯЗАТЕЛЬНОСТИ NULL / NOT NULL ЗНАЧЕНИЕ ПО УМОЛЧАНИЮ DEFAULT (Значение) ПРОВЕРКА ЗНАЧЕНИЯ CHECK (Условие)

10 Определение данных

11 CREATE TABLE s ( s_no INT IDENTITY(1,1) PRIMARY KEY, sname CHAR(10) NOT NULL, city CHAR(15) NOT NULL, status INT ) CREATE TABLE p ( p_no INT IDENTITY(1,1) PRIMARY KEY, pname CHAR(10) NOT NULL, city CHAR(15) NOT NULL, color CHAR(10), weight INT ) CREATE TABLE sp ( s_no INT, p_no INT, qty INT, PRIMARY KEY(s_no, p_no), FOREIGN KEY(s_no) REFERENCES s(s_no) ON UPDATE CASCADE, FOREIGN KEY(p_no) REFERENCES p(p_no) ON UPDATE CASCADE )

12 Определение данных УДАЛЕНИЕ ТАБЛИЦ DROP TABLE имяТаблицы УДАЛЕНИЕ СТОЛБЦОВ DROP имяСтолбца RESTRICT/CASCADE УДАЛЕНИЕ ОГРАНИЧЕНИЙ DROP CONSTRAINT имяСтолбца RESTRICT/CASCADE

13 Выборка данных SELECT список_столбцов_результата FROM базовая_таблица WHERE условие_отбора_строк GROUP BY критерий_группирования_строк HAVING условие_отбора_групп ORDER BY критерий_сортировки_результата

14 Выборка данных Извлечь все сведения о товарах SELECT * FROM p SELECT p_no, pname, weight, city, color FROM p SELECT p.p_no, p.pname, p.weight, p.city, p.color FROM p

15 Выборка данных Извлечь сведения о поставщиках из Парижа или Лондона со статусом от 20 до 30 с упорядочиванием результата по именам поставщиков SELECT * FROM s WHERE (city=London OR city = Paris) AND status BETWEEN 20 and 30 ORDER BY sname ASC DESC SELECT * FROM s WHERE city IN (London, Paris) AND status BETWEEN 20 and 30 ORDER BY sname ASC DESC

16 Выборка данных Извлечь сведения о поставщиках из Парижа или Лондона со статусом от 20 до 30 с упорядочиванием результата по городам, а внутри города по статусам, а для одинаковых значений статуса – по номерам поставщиков SELECT * FROM s WHERE city IN (London, Paris) AND status BETWEEN 20 and 30 ORDER BY city, status, s_no

17 Выборка данных Извлечь номера всех поставщиков, поставляющих товары SELECT s_no FROM sp Без дубликатов SELECT DISTINCT s_no FROM sp

18 Выборка данных с вычислением результата Перечень товаров с указанием номера, названия и веса в килограммах SELECT p_no, pname, weight*0.001 as kg

19 СТАТИСТИЧЕСКИЕ ФУНКЦИИ SUM (числПоле) MIN (числПоле) MAX(числПоле) AVG(числПоле) COUNT(числПоле) – без учета дубликатов COUNT(*) – подсчет числа строк группы (с учетом дубликатов).

20 Выборка данных из нескольких таблиц Задача: извлечь сведения о поставщиках, поставляющих товары с указанием номера, имени поставщика, номера товара, количества поставляемых товаров этого номера SELECT s.s_no, s.sname, sp.p_no, sp.qty FROM s,sp (декартово произведение этих таблиц)

21 Выборка данных из нескольких таблиц Задача: извлечь сведения о поставщиках, поставляющих товары с указанием номера, имени поставщика, номера товара, количества поставляемых товаров этого номера SELECT s.s_no, s.sname, sp.p_no, sp.qty FROM s,sp WHERE s.s_no=sp.s_no (эквисоединение)

22 Способы соединения таблиц Список таблиц в FROM, условие соединения – во фразе WHERE Запись во фразе FROM с использованием операции JOIN (SQL92) (s JOIN sp ON s.s_no=sp.s_no)

23 Внешнее соединение таблиц RIGHT Outer Join LEFT Outer Join FULL Outer Join

24 Сравнение множеств Задача: извлечь сведения о поставщиках, поставляющих все товары, содержащиеся в таблице s. SELECT * FROM s WHERE NOT EXISTS ( SELECT * FROM p WHERE p_no NOT IN( SELECT p_no FROM sp WHERE sp.s_no=s.s_no )

25 Сравнение множеств Задача: извлечь список поставщиков, поставляющих по крайней мере все те товары, которые поставляет поставщик s2. SELECT * FROM s WHERE NOT EXISTS( SELECT * FROM sp WHERE sp.s_no=s2 AND p_no NOT IN ( SELECT p_no FROM sp WHERE s_no=s.s_no )

26 Ведение баз данных 1. Вставка записи в таблицу INSERT INTO ИмяТаблицы [(список столбцов)] – по умолчанию – все столбцы таблицы в естественном порядке VALUES (список значений) – в соответствии со списком столбцов. 2. Удаление строк из таблицы DELETE FROM имяТаблицы WHERE условие – определяет те записи, которые удаляются. Если нет WHERE, то удаляются все строки из таблицы 3. Изменение строк таблицы UPDATE имяТаблицы SET имяСтолбца = Значение, … WHERE условие – задает строки, которые будут изменены.

27 Виртуальные таблицы CREATE VIEW имяПросмотра AS SELECT … – формирует содержимое виртуальной таблицы на основе реальной таблицы или дргуих просмотров.

28 Хранимые процедуры CREATE PROCEDURE CHAR(5) CHAR(15) CHAR(15) INT OUTPUT AS /тело @status=status FROM s WHERE

29 Триггер CREATE TRIGGER Check_sp_ins ON sp FOR INSERT AS INT BEGIN TRANSACTION FROM INSERTED, = status FROM s WHERE = count(*) FROM sp WHERE BEGIN COMMIT TRANSACTION END ELSE BEGIN RAISERROR( Too many parts,16,1) ROLLBACK TRANSACTION END