Основы SQL: выборка данных и однострочные функции

1.

SQL
(Strukture Query
Language)

2.

Концепция реляционной базы данных
• Доктор Эдгар Франк Кодд предложил реляционную модель баз
данных в 1979 г.
• Эта модель лежит в основе систем управления
реляционными базами данных (RDBMS или РСУБД).
• Реляционная модель содержит следующие компоненты:
– Совокупность объектов или отношений.
– Набор операций над отношениями.
– Целостность данных - их точность и
согласованность.

3.

Реляционная база данных - это совокупность
отношений или двумерных таблиц
Сервер БД
Имя таблицы: S_CUSTOMER
SALES_
ID NAME
PHONE
REP_ID
201 Unisports
55-2066101
12
202 Simms Atheletics 81-20101
14
203 Delhi Sports
91-10351
14
204 Womansport
1-206-104-0103
11
Имя таблицы: S_EMP
ID
10
11
12
14
LAST_NAME
Havel
Magee
Giljum
Nguyen
FIRST_NAME
Marta
Colin
Henry
Mai

4.

Концепция реляционной базы данных
• Каждая таблица состоит из строк и столбцов.
Таблица (отношение) S_CUSTOMER)
Строка (кортеж)
SALES_
REP_ID
ID NAME
PHONE
201
202
203
204
55-2066101
81-20101
91-10351
1-206-104-0103
Unisports
Simms Atheletics
Delhi Sports
Womansport
12
14
14
11
Столбец (атрибут)
• Манипулировать данными в строках можно с помощью
команд Структурированного языка запросов (SQL).

5.

Терминология реляционной базы данных
• Каждая строка данных в таблице однозначно
идентифицируется главным ключом (PK).
• С помощью внешних ключей (FK) можно логически
связывать информацию из нескольких таблиц.
Имя таблицы: S_EMP
Имя таблицы: S_CUSTOMER
ID
201
202
203
204
NAME
Unisports
Simms Atheletics
Delhi Sports
Womansport
Главный ключ
SALES_
PHONE
REP_ID
55-2066101
12
81-20101
14
91-10351
14
1-206-104-0103
11
Внешний ключ
ID
10
11
12
14
LAST_NAME
Havel
Magee
Giljum
Nguyen
Главный ключ
FIRST_NAME
Marta
Colin
Henry
Mai

6.

Свойства реляционной базы данных
• Доступ к объектам базы данных и их изменение
осуществляются с помощью команд языка SQL.
• Содержит совокупность таблиц без физических
указателей.
• Используется набор операций.
• Может быть изменена в оперативном
(онлайновом) режиме.
• Полная независимость данных.

7.

Объекты базы данных
Объект
Описание
Таблица
Основная единица хранения данных,
состоящая из строк и столбцов.
Представление
Логическое представление подмножеств
данных из одной или нескольких таблиц.
Последоват.
Генерирует значения первичного ключа.
Индекс
Ускоряет некоторые запросы.
Синоним
Альтернативное имя объекта.
Программн.
единица
Процедура, функция или пакет команд
SQL и PL/SQL.

8.

Ограничения целостности данных
• Сущности:
– Ни одна часть первичного ключа не может иметь неопределенного
значения (NULL). Значение должно быть определенным и
уникальным.
• Ссылки:
– Значение внешнего ключа должно совпадать со значением
первичного ключа или быть неопределенным (NULL).
• Столбцы:
– Значения столбца должны соответствовать заданному типу данных.
• Пользовательские ограничения:
– Значения должны соответствовать правилам бизнеса.

9.

Команды SQL
• Выборка данных:
– SELECT
• Манипулирование данными (DML):
– INSERT, UPDATE, DELETE
• Определение данных (DDL):
– CREATE, ALTER, DROP, RENAME, TRUNCATE
• Управление транзакциями:
– COMMIT, ROLLBACK, SAVEPOINT
• Безопасность (DCL):
– GRANT, REVOKE

10.

Вывод структуры таблицы
Команда SQL: выводит структуру таблицы
(имена столбцов, столбцы NOT NULL и типы
данных).
SQL>
select *
from information_schema.columns where
table_name='s_dept'
• Столбцы NOT NULL должны содержать данные.
• Примеры типов данных и ширины столбцов
▪ NUMERIC(p,s)
▪ VARCHAR(s)
▪ DATE
▪ CHAR(s)

11.

ВЫБОРКА ДАННЫХ

12.

Команды SQL
• Команда может занимать одну или
несколько строк.
• Для удобства чтения команды можно
использовать табуляцию и отступы.
• Сокращение и перенос слов запрещены.
• Символы верхнего и нижнего регистров не
различаются.
• Команды вводятся в буфер SQL.

13.

Структура таблиц

14.

Основной блок запроса
SELECT [DISTINCT] {*,column [alias],....}
FROM
table;
• SELECT задает столбцы, подлежащие выборке.
• FROM указывает, из какой таблицы
Простейший оператор SELECT содержит два предложения:
• Предложение SELECT
– Звездочка (*) обозначает все столбцы
• Предложение FROM
SQL> SELECT
2 FROM
14
*
s_dept;

15.

Выборка всех столбцов и всех строк
SQL> SELECT
2 FROM
*
s_dept;
ID NAME
10
31
32
33
34
35
41
42
43
44
45
50
Finance
Sales
Sales
Sales
Sales
Sales
Operations
Operations
Operations
Operations
Operations
Administration
12 rows selected.
REGION_ID
1
1
2
3
4
5
1
2
3
4
5
1

16.

Выборка заданных столбцов
SQL> SELECT
2 FROM
dept_id, last_name, manager_id
s_emp;
• Перечислить столбцы в предложении SELECT.
• Разделить столбцы в списке запятыми.
• Указать столбцы в порядке, в котором они должны появиться на
выводе.
МЕТКИ СТОЛБЦОВ ПО УМОЛЧАНИЮ
• Выравнивание метки по умолчанию:
– Слева: даты и символьные данные
– Справа: числовые данные
• По умолчанию вывод меток производится в символах верхнего
регистра.

17.

Арифметические выражения
Создание выражений для типов данных NUMBER и DATE с помощью
арифметических операторов.
Сложение
+
Вычитание
-
Умножение
*
Деление
/
SQL> SELECT
2 FROM
last_name, salary * 12, commission_pct
s_emp;
LAST_NAME
...
Havel
Magee
Giljum
Sedeghi
Nguyen
Dumas
Maduro
...
SALARY*12 COMMISSION_PCT
15684
16800
17880
18180
18300
17400
16800
10
12.5
10
15
17.5

18.

Порядок выполнения операторов
• Умножение и деление выполняются до сложения и вычитания.
• Операторы, имеющие один и тот же приоритет, выполняются по
очереди слева направо.
• Для изменения порядка вычислений и удобства чтения
выражений можно использовать скобки.
Скобки используются для изменения порядка выполнения
действий при вычислении выражения.
SQL> SELECT
2 FROM
last_name, salary, 12 * salary + 100
s_emp;
...
Velasquez 2500 30100
SQL> SELECT
2 FROM
last_name, salary, 12 * (salary + 100)
s_emp;
...
Velasquez 2500 31200

19.

Псевдонимы столбцов
Псевдоним столбца заменяет его заголовок.
• Особенно полезен при расчетах.
• Следует сразу за заголовком столбца.
– Между заголовком и псевдонимом столбца может
находиться необязательное ключевое слово AS.
• Если псевдоним содержит пробелы или специальные
символы или если в нем различаются символы верхнего
и нижнего регистров, двойные кавычки обязательны.

20.

Оператор конкатенации
Оператор конкатенации:
• Обозначается двойной вертикальной чертой (||).
• Соединяет столбцы или текстовые строки с другими столбцами.
• Создает столбец, являющийся символьным выражением.
SQL> SELECT
2 FROM
first_name||last_name Employees
s_emp;
Employees
CarmenVelasquez
LaDorisNgao
MidoriNagayama
MarkQuick-To-See
AudryRopeburn
MollyUrguhart
...

21.

Строка символов - литерал
• Литерал — это строка символов, выражение или
число, включенные в список SELECT.
• Символьные литералы и литералы-даты должны
быть заключены в апострофы.
• Каждая строка символов выводится по одному
разу для каждой возвращаемой строки таблицы.
SQL> SELECT
2
3 FROM
first_name ||' '|| last_name
||', '|| title "Employees"
s_emp;

22.

Обработка неопределенных значений
• Неопределенным значением (NULL) называется
недоступное, неприсвоенное, неизвестное или
неприменимое значение.
• Неопределенное значение отличается от нуля и
пробела.
• Результатом арифметического выражения,
содержащего неопределенное значение, также
является неопределенное значение.
SQL> SELECT
2
3 FROM
last_name, title,
salary*commission_pct/100 COMM
s_emp;

23.

Функция COALESCE
Преобразование NULL в фактическое значение с
помощью функции COALESCE.
• Используемые типы данных: дата, символьные и
числовые.
• Типы данных должны совпадать:
– COALESCE (start_date, '01-JAN-95')
– COALESCE (title, 'No Title Yet')
– COALESCE (salary, 1000)
SQL> SELECT last_name, title,
2
salary*COALESCE(commission_pct,0)/100 COMM
3 FROM
s_emp;

24.

Дубликаты строк
• По умолчанию результат запроса включает все строки - в том числе
и дубликаты.
SQL> SELECT
2 FROM
name
s_dept;
Предотвратить вывод дубликатов можно с помощью ключевого слова
DISTINCT в предложении SELECT..
SQL> SELECT
2 FROM
DISTINCT name
s_dept;
• DISTINCT относится ко всем столбцам в списке SELECT.
SQL> SELECT
2 FROM
DISTINCT dept_id, title
s_emp;
Результат применения DISTINCT к нескольким столбцам - вывод строк с
неповторяющимися сочетаниями значений этих столбцов.

25.

ОДНОСТРОЧНЫЕ
ФУНКЦИИ
Выборка строк

26.

Обзор функций в SQL
Функции используются для:
• Выполнения расчетов с данными.
• Изменения отдельных единиц данных.
• Управления выводом групп строк.
• Изменения формата вывода дат.
• Преобразования типов данных в
столбцах.

27.

Два типа функций в SQL
• Однострочные
– Символьные
Функция
– Числовые
– Функции даты
– Функции
преобразования
• Многострочные
– Групповые
Однострочная
Многострочная

28.

Однострочные функции: синтаксис
Однострочные функции:
• Манипулируют элементами данных.
• Принимают аргументы и возвращают одно
значение.
• Работают с каждой строкой, возвращаемой
запросом.
• Возвращают один результат на строку.
• Изменяют тип данных.
• Могут быть вложенными.
Синтаксис:
function_name (column|expression, [arg1, arg2,...])

29.

Символьные функции
LOWER
Преобразование в нижний регистр
UPPER
Преобразование в верхний регистр
INITCAP
CONCAT
Преобразование начальных букв
в верхний регистр
Конкатенация значений
SUBSTR
Возврат подстроки
LENGTH
Возврат количества символов
COALESCE
Преобразование
неопределенного значения

30.

Функции преобразования регистра
Преобразование регистра для строки символов
LOWER('SQL Course')
sql course
UPPER('SQL Course')
SQL COURSE
INITCAP('SQL Course')
Sql Course
SQL> SELECT first_name, last_name
2 FROM
s_emp
3 WHERE
last_name = 'PATEL';
no rows returned
SQL> SELECT first_name, last_name
2 FROM
s_emp
3 WHERE UPPER(last_name) = 'PATEL';
FIRST_NAME
LAST_NAME
Vikram
Radha
Patel
Patel

31.

Символьные и числовые функции
Работа с символьными строками:
• CONCAT('Good', 'String')
GoodString
• SUBSTR('String',1,3)
Str
• LENGTH('String')
6
Числовые функции:
TRUNC
Усекает значение до заданного
количества десятичных знаков
MOD
Возвращает остаток от деления

32.

Функции ROUND, TRUNC, MOD
TRUNC (45.923, 2)
TRUNC (45.923)
TRUNC (45.923, -1)
45.92
45
40
Вычисление остатка от деления одного значения
на другое
MOD(1600,300)
100

33.

Формат даты
• PostgreSQL хранит дату/время во внутреннем двоичном формате с
микросекундной точностью.
• По умолчанию вывод даты выполняется в формате YYYY-MM-DD (ISO).
• Функция CURRENT_DATE возвращает текущую дату, CURRENT_TIMESTAMP
— дату и время с часовым поясом.
• Для получения текущего времени без привязки к таблице используется просто
SELECT CURRENT_TIMESTAMP;.
• Арифметические операции с датами:
➢ Прибавление/вычитание числа к дате даёт новую дату (число интерпретируется
как количество дней).
➢ Вычитание двух дат возвращает количество дней (тип integer или numeric при
дробных значениях).
➢ Для прибавления часов используется интервал: date + INTERVAL '5 hours'.
➢ Можно использовать INTERVAL для любых единиц (дни, месяцы, годы, минуты и
т.д.).

34.

Функции преобразования
• Функция TO_CHAR преобразует число или
строку даты в строку символов.
• Функция TO_NUMBER преобразует строку
символов, состоящую из цифр, в число.
• Функция TO_DATE преобразует строку
символов с датой в значение типа “дата“.
• Функции преобразования могут использовать
модель формата, состоящую из нескольких
элементов.

35.

Функция TO_CHAR с датами
TO_CHAR(date, 'fmt')
Модель формата:
• Должна быть заключена в апострофы. Различает
символы верхнего и нижнего регистров.
• Может включать любые разрешенные элементы
формата даты.
• Отделяется от значения даты запятой.

36.

Элементы формата даты
YYYY - полный год цифрами
MM - двузначное цифровое обозначение месяца
MONTH - полное название месяца
DY - трехзначное алфавитное сокращенное название
дня недели
• DD - двузначное цифровое обозначение дня недели
• DAY - полное название дня
Элементы, которые задают формат части даты, обозначающей время.
– HH24:MI:SS AM
15:45:32 PM
Символьные строки добавляются в кавычках.
– DD " of " MONTH
12 of OCTOBER
Числовые суффиксы используются для вывода числительных
прописью.
– ddspth
fourteenth

37.

Функция TO_CHAR с датами
SQL> SELECT
4
FROM
first_name, last_name,
TO_CHAR(start_date,'dd.mm.yyyy')
s_emp;

38.

Функция TO_CHAR с числами
TO_CHAR(number, 'fmt')
Форматы, используемые с функцией TO_CHAR
для вывода символьного значения в виде числа
9
0
$
L
.
,
- цифра.
- вывод нуля.
- плавающий знак доллара.
- плавающий символ местной валюты
- вывод десятичной точки.
- вывод разделителя троек цифр.

39.

Функция TO_CHAR с числами
SQL> SELECT
2
3
4 FROM
5 WHERE
'Order '||TO_CHAR(id)||
' was filled for a total of '
||TO_CHAR(total,'$9,999,999')
s_ord
ship_date = to_date('21.09.1992','dd.mm.yyyy');
• Выходная строка, состоящая из символов “#”, означает, что в
модели формата недостаточно символов слева от десятичной
точки.
• Сервер округляет десятичные значения, которые хранятся
в базе данных, в соответствии с заданной моделью формата.

40.

Функции TO_NUMBER и TO_DATE
Преобразование строки символов в числовой
формат с помощью функции TO_NUMBER:
TO_NUMBER(char)
Преобразование строки символов в формат даты с
помощью функции TO_DATE:
TO_DATE ('10.06.1992', 'dd.mm.yyyy')
Использование элементов формата.
TO_DATE(char[, 'fmt'])

41.

Вложенные однострочные функции
• Однострочные функции могут быть вложены на
любую глубину.
• Вложенные функции вычисляются от самого
глубокого уровня к внешнему.
F3(F2(F1(col,arg1),arg2),arg3)
Step 1 = Result 1
Step 2 = Result 2
Step 3 = Result 3

42.

Вложенные функции
SQL> SELECT
last_name,
2
COALESCE(TO_CHAR(manager_id),'No Manager')
3 FROM
s_emp
4 WHERE
manager_id IS NULL;
1. Вычисление внутренней функции для преобразования числового значения в
строку символов:
Результат1=TO_CHAR(manager_id)
2. Вычисление внешней функции для замены неопределенного значения текстовой
строкой:
COALESCE(Результат1,'No Manager')

43.

ВЫБОРКА ДАННЫХ
Ограничения на
количество выбираемых
строк

44.

Предложение ORDER BY
Используется для сортировки строк.
• ASC – сортировка по возрастанию, (используется
по умолчанию).
• DESC – сортировка по убыванию.
• Предложение ORDER BY является в команде
SELECT последним.
SQL> SELECT
2 FROM
3 ORDER BY
last_name, dept_id, start_date
s_emp
last_name;

45.

Предложение ORDER BY
• По умолчанию - сортировка возрастающем порядке.
• Для сортировки в обратном порядке используется
слово DESC.
• Возможна сортировка по выражениям или
псевдонимам.
SQL> SELECT
last_name EMPLOYEE, start_date
2 FROM
s_emp
3 ORDER BY EMPLOYEE DESC;
Место неопределенных значений:
– При сортировке по возрастанию - последние.
– При сортировке по убыванию - первые.

46.

Сортировка по нескольким столбцам
Сортировка по позициям для экономии времени.
SQL> SELECT
2 FROM
3 ORDER BY
last_name, salary * 12
s_emp
2;
Сортировка по нескольким столбцам
SQL> SELECT
2 FROM
3 ORDER BY
last_name, dept_id, salary
s_emp
dept_id, salary DESC;
Последовательность сортировки определяется порядком
столбцов в списке ORDER BY.
Сортировать можно и по столбцам, не входящим в список
SELECT.

47.

Ограничение количества строк
Количество выбираемых строк можно ограничить с
помощью предложением WHERE.
• Предложение WHERE следует за предложением
FROM.
• Условия состоят из:
– имен столбцов, выражений, констант;
– операторов сравнения;
– литералов.
SQL> SELECT
2 FROM
3 WHERE
last_name, dept_id, salary
s_emp
dept_id = 42;

48.

Строки символов и даты
• Строки символов и даты заключаются в
апострофы.
• Числовые значения в апострофы не заключаются.
• В символьных значениях различаются символы
верхнего и нижнего регистров.
• Формат даты по умолчанию - “DD-MON-YY”
(число-месяц-год).
SQL> SELECT
2 FROM
3 WHERE
first_name, last_name, title
s_emp
last_name = 'Magee';

49.

Операторы сравнения и логические
• Логические операторы сравнения
= > >= < <=
• Операторы сравнения SQL
– BETWEEN ... AND...
– IN(list)
– LIKE
– IS NULL
• Логические операторы
– AND
– OR
– NOT

50.

Отрицание
Иногда проще исключить строки, которые явно не
требуются:
• Логические операторы
!= <> ^=
• Операторы SQL
– NOT BETWEEN
– NOT IN
– NOT LIKE
– IS NOT NULL

51.

Операторы BETWEEN и IN
Оператор BETWEEN используется для проверки
вхождения значения в интервал значений (включая
границы интервала).
SQL> SELECT
first_name, last_name, start_date
2 FROM
s_emp
3 WHERE
start_date
4 BETWEEN to_date('09.05.1991','dd.mm.yyyy')
5 AND to_date('17.07.1991','dd.mm.yyyy');
Оператор IN используется для проверки
принадлежности значения к списку.
SQL> SELECT
2 FROM
3 WHERE
id, name, region_id
s_dept
region_id IN (1,3);

52.

Оператор LIKE
• Используется для поиска строковых значений с
помощью метасимволов (wildcards).
• Условия для поиска могут содержать
символьные литералы или числа:
– "%" означает отсутствие или некоторое
количество символов;
– "_" означает один символ.
SQL> SELECT
2 FROM
3 WHERE
last_name
s_emp
last_name LIKE 'M%';

53.

Оператор LIKE (продолжение)
• Может использоваться в качестве быстрого
эквивалента некоторых операций BETWEEN.
SQL> SELECT
2 FROM
3 WHERE
last_name, start_date
s_emp
to_char(start_date,’yyyy’) LIKE '%91';
• В критерии поиска символы можно сочетать.
SQL> SELECT
2 FROM
3 WHERE
last_name
s_emp
last_name LIKE '_a%';
• Поиск символов "%" и "_" требует
использования идентификатора ESCAPE.

54.

Оператор IS NULL
• Неопределенные значения проверяются с
помощью оператора IS NULL.
• Пользоваться оператором “=“ не следует.
SQL> SELECT
id, name, credit_rating
2 FROM s_customer
3 WHERE sales_rep_id IS NULL;

55.

Выборка по нескольким условиям
• Использование сложных критериев.
• Сочетание условий с помощью операторов AND и OR.
• AND требует выполнения обоих условий.
SQL> SELECT
2 FROM
3 WHERE
4 AND
last_name, salary, dept_id, title
s_emp
dept_id = 41
title = 'Stock Clerk';
• OR требует выполнения хотя бы одного из
условий.
SQL> SELECT
2 FROM
3 WHERE
4 OR
last_name, salary, dept_id, title
s_emp
dept_id = 41
title = 'Stock Clerk';

56.

Порядок выполнения операций
Стандартный порядок выполнения
операций отменяется скобками.
Порядок вычисления
Оператор
1
Все операторы сравнения
2
AND
3
OR

57.

Порядок выполнения операций (примеры)
Вывод информации о служащих отдела 44 с зарплатой
1000 и более и о всех служащих отдела 42.
SQL> SELECT
2 FROM
3 WHERE
4 AND
5 OR
last_name, salary, dept_id
s_emp
salary >= 1000
dept_id = 44
dept_id = 42;
Вывод информации о всех служащих отделов 44 и 42,
зарплата которых составляет 1000 и более.
SQL> SELECT
2 FROM
3 WHERE
4 AND
5 OR
last_name, salary, dept_id
s_emp
salary >= 1000
(dept_id = 44
dept_id = 42);

58.

Вопросы?