Основы языка SQL Structured Query Language История

Основы языка SQL Structured Query Language

История SQL n n n 1970. Е. Ф. Кодд (исследовательская лаборатория IBM) - реляционная модель 1974. Д. Чамберлин (там же) – определение языка «Sructured English Query Language» SEQUEL 1976. SEQUEL/2, позже получил название SQL (прототип СУБД System R) 2

История SQL (продолжение) n n 1978 -79 г. г. СУБД Oracle – первая коммерческая СУБД, ориентированная на SQL 1981 IBM выпускает SQL/DS 1982 Американский национальный институт стандартов (ANSI) - начата разработка стандарта языка RDL (Relation Database Language) 1983 Международный комитет по стандартизации (ISO) 3

Стандарты языка SQL Американский национальный институт стандартов (ANSI) – 1986 n Международная организация стандартов (ISO) – 1987 n 1989 выпущено дополнение ISO n ISO 1992 – официальный единый стандарт SQL 2 (SQL 92) n 4

Запись SQL-операторов n Зарезервированные слова – являются Слова, определяемые постоянной частьюзадаются пользователем – SQL и имеют фиксированное значение, непосредственно пользователем в записываютсясв точности как соответствие установленными установлено стандартом, нельзя синтаксическими правилами, переносить и разбивать на части представляют собой имена таблиц, полей, представлений, индексов и т. д. 5

Правила записи SQL-операторов n n n Каждая фраза в операторе начинается с новой строки Начало каждой фразы должно быть выравнено с началом остальных фраз в операторе Если фраза состоит из нескольких частей, то каждая часть начинается с новой строки с некоторым отступом относительно начала фразы, указывая на подчиненность 6

Расширенная форма BNF-нотации (Backus-Naur-Form) определения формата SQL-операторов ПРОПИСНЫЕ БУКВЫ использовать для записи зарезервированных слов n строчные буквы использовать для записи слов, определяемых пользователем n Вертикальная черта (|) оказывает на выбор одного из приведенных параметров – например, a | b | c n 7

Расширенная форма BNF-нотации (Backus-Naur-Form) определения формата SQL-операторов {} Фигурные скобки определяют обязательный элемент, например {a} n [] Квадратные скобки определяют необязательный элемент, например [a] n (…) Многоточие используется для указания необязательной возможности повторения конструкции от нуля до нескольких раз n 8

Пример n {a | b} [, c…] – означает, что после выбора обязательных a или b может следовать от нуля до нескольких повторений c, разделенных запятыми 9

Операторы манипулирования данными SELECT – выборка данных из базы n INSERT – вставка данных в таблицу n UPDATE – обновление (изменение) данных в таблице n DELETE – удаление данных из таблицы n 10

Литералы n Литерал – константа, используемая в SQL-операторах ¨ INSERT INTO holl(id_holl, number, type_holl) ¨ VALUES (3, 412, ‘лаборатория ЭВТ’) 11

Оператор SELECT [DISTINCT | ALL] {* | [col_expr [AS new_name]] [, …]} FROM table_name [alias] [, …] [WHERE condition] [GROUP BY col_list] [HAVING condition] [ORDER BY col_list] 12

Оператор SELECT (продолжение) col_expr – имя поля или выражение из нескольких имен new_name – новое имя поля table_name – имя существующих в базе данных таблицы или представления, к которым необходимо получить доступ alias – необязательный параметр, сокращение, устанавливаемое для table_name condition – логическое выражение, условие col_list – список имен полей 13

Порядок обработки элементов оператора SELECT n n FROM – определяются имена используемой таблицы или нескольких таблиц WHERE – выполняется фильтрация строк объекта в соответствии с заданными условиями GROUP BY – образуются группы строк, имеющих одно и то же значение в указанном поле HAVING – фильтруются группы строк объекта в соответствии с заданными условиями 14

Порядок обработки элементов оператора SELECT (продолжение) HAVING – фильтруются группы строк объекта в соответствии с заданными условиями n SELECT – устанавливается, какие поля должны присутствовать в выходных данных n ORDER BY – определяется упорядоченность результатов выполнения оператора n 15

Выборка всех данных student(key, family, name, spec, age) SELECT key, family, name, spec, age FROM student SELECT * FROM student 16

Выборка по некоторым полям student(key, family, name, spec, age) SELECT key, family, spec FROM student 17

Выборка данных с удалением дублирующих значений student(key, family, name, spec, age) SELECT spec FROM student SELECT DISTINCT spec FROM student 18

Вычисляемые поля manager(key, family, name, rang, salary) SELECT key, family, rang, salary*12 FROM manager SELECT family+name AS manager, rang FROM manager 19

Предложение WHERE n Сравнение n Диапазон n Принадлежность к множеству n Соответствие шаблону n Значение NULL 20

Сравнение условий поиска manager(key, family, name, rang, salary) SELECT family, rang, salary FROM manager WHERE salary>5000 =, >, <, >=, <> != 21

Сравнение условий поиска (продолжение) manager(key, family, name, rang, salary) SELECT family, rang, salary FROM manager WHERE rang=‘специалист’ OR rang=‘ведущий специалист’ AND, OR, NOT 22

Использование диапазонов BETWEEN / NOT BETWEEN SELECT family, rang, salary FROM manager WHERE salary BETWEEN 5000 AND 10000 SELECT family, rang, salary FROM manager WHERE salary >= 5000 AND salary <= 10000 23

Поиск с проверкой вхождения во множество IN / NOT IN SELECT key, family, spec FROM student WHERE spec IN (‘физика’, ‘математика’) SELECT key, family, spec FROM student WHERE spec = ‘физика’ OR spec = ‘математика’ 24

Поиск с указанием шаблонов LIKE / NOT LIKE % - любая последовательность из нуля и более символов _ - любой одиночный символ 25

Примеры шаблонов family LIKE ‘И%’ – первый символ значения, соответствующий шаблону, равен «И» , остальные не имеет значения name LIKE ‘И____’ – значение соответствующее шаблону длиной 5 символов, первый символ равен «И» family NOT LIKE ‘%ов’ – любая последовательность символов оканчивающаяся на «ов» 26

Пример запроса student(key, family, adress) SELECT family, adress FROM student WHERE adress LIKE ‘%Бирск%’ 27

Использование значения NULL IS NULL / IS NOT NULL student(key, family, adress, icq) WHERE ICQ=‘’ SELECT key, family FROM student WHERE icq IS NULL 28

Сортировка результатов (ORDER BY) ASC – сортировка по возрастанию n DESC – сортировка по убыванию n n Сортировка выполняется по имени поля в списке SELECT или по его номеру, самый левый столбец имеет номер (1) 29

Сортировка по значениям одного столбца manager(key, family, name, rang, salary) SELECT family, rang, salary FROM manager ORDER BY salary DESC «» ORDER BY 3 DESC 30

Сортировка по значениям нескольким столбцам Главный ключ – определяет общую упорядоченность строк результирующей таблицы n Младший(ие) ключ(и) – определяет(ют) дополнительную упорядоченность результатов n 31

Пример сортировки по значениям нескольких столбцов manager(key, family, name, rang, salary) SELECT family, rang, salary FROM manager ORDER BY family, salary DESC *Стандарт ISO требует, чтобы значения NULL воспринимались системой либо больше, либо меньше любого значения. Выбор оставлен на усмотрение разработчиков СУБД. 32

Использование обобщающих функций языка SQL n n n COUNT – возвращает количество значений в указанном столбце SUM – возвращает сумму значений в указанном столбце AVG – возвращает среднее значение в указанном столбце MIN – возвращает минимальное значение в указанном столбце MAX – возвращает максимальное значение в указанном столбце 33

Замечания 1. 2. 3. Все обобщающие функции оперируют со значениями в одном столбце и возвращают единственное значение Функции SUM и AVG могут использоваться только для числовых полей COUNT(*) – особый случай использования функции COUNT, возвращает число всех сток в результирующей таблице, независимо от наличия пустых или дублирующихся значений 34

Замечания n n n 4. Для исключения дублирующихся значений перед именем столбца в определении обобщающей функции следует использовать ключевое слово DISTINCT 5. Ключевое слово DISTINCT не имеет смысла для функций MIN и MAX 6. Ключевое слово DISTINCT в каждом запросе может быть указано не более одного раза 35

Замечания n n 7. Все обобщающие функции могут использоваться только в списке фраз SELECT и HAVING 8. Если список фразы SELECT содержит обобщающие функции, а в тексте запроса отсутствует фраза GROUP BY, то ни один элемент списка фразы SELECT не может содержать ссылку на столбец SELECT key, COUNT(salary) FROM manager 36

Использование функции COUNT(*) manager(key, family, name, rang, salary) SELECT COUNT(*) AS count_manager FROM manager WHERE rang=‘инспектор’ 37

Использование функции COUNT(DISTINCT) manager(key, family, name, rang, salary) SELECT COUNT(DISTINCT rang) AS count_rang FROM manager WHERE salary BETWEEN 5400 AND 10200 38

Использование функции COUNT и SUM manager(key, family, name, rang, salary) SELECT COUNT(key) AS count, SUM(salary) AS SUM FROM manager WHERE rang=‘консультант’ 39

Использование функций MIN, MAX и AVG manager(key, family, name, rang, salary) SELECT MIN(salary) AS min_sal, MAX(salary) AS max_sal, AVG(salary) AS avg_sal FROM manager 40

Группирование результатов (GROUP BY) Запрос, в котором присутствует фраза GROUP BY, называется группирующим запросом n Поля, перечисленные во фразе GROUP BY, называются группируемыми полями n Каждый элемент списка фразы SELECT должен иметь единственное значение для всей группы n 41

Группирование результатов (GROUP BY) (продолжение. . ) Фраза SELECT может содержать только следующие типы элементов ¨ Имена полей; ¨ Обобщающие функции; ¨ Константы; ¨ Выражения, включающие комбинации перечисленных выше элементов 42

Группирование результатов (GROUP BY) (продолжение. . ) Все имена полей, приведенные во фразе SELECT, должны присутствовать во фразе GROUP BY, за исключением имен полей в обобщающей функции. Если с фразой GROUP BY используется фраза WHERE, то она обрабатывается первой, группировке подвергаются строки удовлетворяющие условию WHERE 43

Группирование результатов (GROUP BY) (продолжение. . ) В стандарте ISO определено, что все строки таблицы, содержащие значения NULL в группируемых полях, помещаются в одну группу 44

Пример использование фразы GROUP BY manager(key, family, name, rang, salary) Определить количество сотрудников, работающих на каждой должности и их суммарную зарплату SELECT rang, COUNT(key) AS count, SUM(salary) AS salary_sum FROM manager GROUP BY rang ORDER BY rang 45

При обработке этого запроса выполняются следующие действия: 1. Строки таблицы manager распределяются в группы в соответствии со значениями в поле rang 2. Для каждой группы строк вычисляется их количество и сумма в поле salary, после чего генерируется итоговая строка по группе 3. Полученные итоговые строки сортируются в порядке возрастания значений поля rang 46

Ограничения на выполнение группирования (HAVING) manager(key, family, name, dep, salary) Для каждого отдела, имеющего в штате больше одного сотрудника, определить количество сотрудников, работающих в каждом отделе, их среднюю зарплату SELECT dep, COUNT(key) AS count, AVG(salary) AS salary_avg FROM manager GROUP BY dep HAVING COUNT(key)>1 ORDER BY rang 47

Замечание Стандарт ISO требует, чтобы имена полей, указанные во фразе HAVING, обязательно присутствовали во фразе GROUP BY или применялись в обобщающих функциях. Применение обобщающих функций во фразе WHERE недопустимо 48