Базы данных и СУБД SQL

SQL
Университет машиностроения
Кафедра «Автоматика и процессы управления»
Дисциплина
Информационные технологии
Тема 13
1 семестр

expr1,..., exprN
FROM source1,..., sourceN
[ [ INNER ] JOIN othersource [ON

join_predicate1 ] ]
[ WHERE predicate2 ]
[ GROUP BY group_expr1,..., group_exprN ]
[ HAVING group_predicate ]
[ ORDER BY field1,..., fieldN [ ASC | DESC ] ]
[ LIMIT [ offset, ] rowcount ]

SQL

SQL
Используется для отбора записей – указывает оператору языка управления

данными (DML) записи, на которые он действует
Не обязательный в SQL (DML) выражениях
В выражениях с SELECT, DELETE, UPDATE предваряет критерии отбора данных
Критерии отбора должны быть записаны в форме предикатов булевского вида (TRUE, FALSE или NULL)
Действует на исходный набор записей (до группировки)

predicate
Все записи, для которых значением предиката является истина – будут

задействованы (возвращены)

Записи, для которых значением предиката является ложь или неопределённость (NULL) – будут исключены из обработки (выборки)

DELETE
FROM mytable
WHERE mycol IS NULL OR mycol = 100

SQL

в SQL
Указывает условия на результат агрегатных функций (MAX, SUM,

AVG,…)
Предикаты строятся только из выражений, указанных в разделе GROUP BY и значений агрегатных функций, вычисленных для каждой группы, образованной GROUP BY
Необходимо, чтобы в SELECT были заданы только столбцы, перечисленные в GROUP BY и/или агрегированные значения
Если параметр GROUP BY не указан в SELECT, HAVING дублирует WHERE

Sales
WHERE SaleDate = ‘01-Jan-2000’
GROUP BY DeptID
HAVING SUM(SaleAmount) > 1000
SELECT d.DeptName,

COUNT(*)
FROM employee e, departament d
WHERE e.DeptID = d.DeptID
GROUP BY d.DeptName
HAVING COUNT (*) > 1000

Получение
идентификаторов отделов, продажи которых превысили 1000 за 1 января 2000 года

Получение
списка отделов, в которых работает более чем один сотрудник

Тема 13. Применение SQL

Условный предикат – выражение, которое должно возвращать значения TRUE, FALSE

или NULL
Значение NULL возвращается если арифметическая, логическая операция или операция сравнения выполняется над операндом со значением NULL), за исключением операций проверки на пустое значение (EXISTS, IS NULL)
Выражения, используемые для построения предиката могут использовать парные круглые скобки [ ( , ) ] любой степени вложенности
Для выполнения логических операций над частями предиката используются операторы AND, OR и NOT

Условный предикат может включать подзапросы
В выражениях могут использоваться функции

определённые в SQL (NOW, YEAR etc.) или определённые в базе данных (CREATE FUNCTION…)
Для проверки значения можно использовать: LIKE –для сравнения с шаблоном, IS – для специальных значений, IN – для вхождения в список
Контроль вхождения значения в заданный диапазон выполняется с использованием BETWEEN … AND …
(в Access) Для построения выражений в роли констант могут использоваться статистические функции по подмножеству для вычисления вспомогательных числовых значений (DSum , DAvg, DMax, DMin, Dcount etc.)

(2)

Production
WHERE Name IN (''Blade'', ''Crown Race'', ''Spokes'');
Наличие в списке значений
SELECT

ProductID, Name, Color
FROM Production
WHERE ProductID BETWEEN 725 AND 734;

Вхождение в диапазон между двумя значениями

SELECT ProductID, Name, Color
FROM Production
WHERE Name LIKE (''%Frame%'')
AND ProductID <= 12
AND Color = ''Red'' ;

Совпадение с шаблоном, сравнение, объединение условий

SQL

LIKE pattern ESCAPE esc_char
Проверяет символьную строку на совпадение с

заданным шаблоном
Используется как часть выражения WHERE smth

Person.PersonPhone AS ph
INNER JOIN Person.Person AS p
ON ph.BusinessEntityID = p.BusinessEntityID
WHERE

ph.PhoneNumber LIKE '415%'
ORDER BY p.LastName;

Получение
списка телефонных номеров с кодом города 415

FirstName LastName Phone
-------------- ------------------- ------------
Ruben Alonso 415-555-124
Shelby Cook 415-555-0121
Karen Hu 415-555-0114
John Long 415-555-0147
David Long 415-555-0123

Применение SQL

WHERE
Множества (списки значений) в предикатах оператора WHERE могут использоваться

для проверки наличия заданного значения (в списке) или для выполнения однотипной операции сравнения с элементами списка
Для проверки наличия значения в списке можно использовать оператор IN (…)
Проверка списка на наличие элементов выполняется предикатом EXISTS (…)
Операция сравнения значения со всеми элементами списка составляется с использованием предикатов ANY (…), SAME (…) и ALL (…). ANY эквивалентно объединению выражений посредством OR, а ALL – с помощью AND
В некоторых случаях пустой список может выступать эквивалентом «пустого» значения (NULL)

Production
WHERE Vendor IN (''Toyota'', ''Nissan'', ''Mitsubishi'', ''Mazda'');
Совпадение со значением из

списка

SELECT ProductID, Name, Color
FROM Production
WHERE ProductID = ANY ( 725 , 730 , 734 );

Равенство любому значению из списка

SELECT ProductID, Name, Color
FROM Production
WHERE EXISTS ( SELECT ProductID
FROM Production
WHERE Vendor =''Ford'');

Наличие в списке значений

Условный предикат может включать подзапрос
В составе предиката подзапрос заключается

в круглые скобки [ ( , ) ]
Вложенный запрос (подзапрос) должен возвращать скалярное (одно) логическое значение (или NULL) либо выступать операндом в операции, возвращающем подобное значение
Операции с подзапросами, возвращающими список значений, должны использовать предикаты ANY (SOME) и ALL для группового сопоставления, EXISTS для проверки наличия результатов или IN для анализа вхождения значения

Production
WHERE Cost >= ( SELECT AVG( Cost )
FROM Production );
Сравнение

с единственным возвращаемым значением

SELECT Name, NCost
FROM ( SELECT Name, Cost * 1.15 AS NCost
FROM Production )
WHERE ProductID >= 725 );

Использование подзапроса как источника данных

SELECT ProductID, Name, Color
FROM Production
WHERE EXISTS ( SELECT ProductID
FROM Production );

Наличие в списке хотя бы одного результата

SQL

операцию соединения реляционной алгебры для раздела FROM
В схему таблицы-результата

входят столбцы обеих таблиц-операндов («сцепление» схем операндов)
Каждая строка таблицы-результата является «сцеплением» строк таблиц-операндов
Результирующий набор строк зависит от типа операции соединения и условия соединения
При необходимости соединения нескольких таблиц операция соединения применяется несколько раз (последовательно)

table1
[ INNER | [ LEFT | RIGHT | FULL ]

OUTER | CROSS ] JOIN
table2
[ ON condition ]

SELECT expessions [,... n]
FROM table1,…, tableN

Для перекрёстного соединения (декартова произведения) CROSS JOIN можно использовать (,)

JOIN – внутреннее соединение таблиц
OUTER JOIN – внешнее соединение

таблиц
LEFT OUTER JOIN – левое внешнее соединение таблиц (несимметричное)
RIGHT OUTER JOIN – правое внешнее соединение таблиц (несимметричное)
FULL OUTER JOIN – полное внешнее соединение таблиц

CROSS JOIN – перекрёстное соединение таблиц

Исходные данные

оператор
Результат – таблица из соединённых строк таблиц-операндов по предикату
Person-City
SELECT *

FROM Person
INNER JOIN City
ON Person.CityId = City.Id

соединение таблиц
Несимметричный оператор
Результат – таблица из соединённых строк таблиц-операндов по

предикату дополненных оставшимися строками левой таблицы (дополняются NULL)

SELECT * FROM Person
LEFT OUTER JOIN City
ON Person.CityId = City.Id

соединение таблиц
Несимметричный оператор
Результат – таблица из соединённых строк таблиц-операндов по

предикату дополненных оставшимися строками правой таблицы (дополняются NULL)

SELECT * FROM Person
RIGHT OUTER JOIN City
ON Person.CityId = City.Id

соединение таблиц
Симметричный оператор
Результат – таблица из соединённых строк таблиц-операндов по

предикату дополненных оставшимися строками обеих таблиц (дополняются NULL)

SELECT * FROM Person
FULL OUTER JOIN City
ON Person.CityId = City.Id

оператор
Результат – таблица из соединённых строк таблиц-операндов, давая все возможные

сочетания строк двух таблиц

SELECT * FROM Person
CROSS JOIN City
[ WHERE predicate ]

OUTER JOIN
LEFT OUTER JOIN с фильтрацией

SQL

SQL-запросов в единую таблицу, состоящую их схожих строк
Оба запроса должны

возвращать одинаковое число столбцов с совместимые типы данных

<запрос1>
UNION [ALL]
<запрос2>
UNION [ALL]
<запрос3>
.....;

В объединение не включаются (скрываются) повторяющиеся строки
Оператор ALL требует включение всех строк в результирующий набор

SQL

13. Применение SQL

- подсчет среднего арифметического значения столбца или выражения,
DCount - подсчет

количества записей,
DFirst - нахождение первого значения столбца из группы,
DLast - нахождение последнего значения столбца из группы,
DМах - определение максимального значения столбца или выражения,
DMin - определение минимального значения столбца или выражения,
DSum - подсчет суммы значений столбца или выражения.

Синтаксис операторов следующий:
<имя_функции> ("выражение";"источник";"критерий")

fio, score, groupID
From students
Where score > DAvg ("God",
"students",
"groupID1")

and groupID <> 1 ;

Важно! Параметры запроса записываются как строковые литералы (в кавычках)

score
From students
Where score >= DAvg (subject,"students”);
Существует возможность использования переменных выражений

в качестве параметров статистических функций по подмножеству

SQL

From
Group by

строк>
Pivot <заголовки столбцов>;

Transform Sum(God) As Sum-God
Select Izd, Sum(god) as SumGod, Count(*) asKolFirm
From Firm
Group by Izd
Pivot Nazf;
<итоговые функции>

процессы управления»
Блок дисциплин
Далее:
Использование Access 2007
Табличные процессоры
Текстовые процессоры
Информатика и информационные технологии
Контакты:
mami.ru/index.php?id=466
timid@mami.ru
inform437@gmail.com