SQL (Structured Query Language) - Язык структурированных запросов.
Один из инструментов работы с БД - SQL Developer
БД - совокупность данных, которые хранятся по определенным правилам и используются для удовлетворения информационных потребностей пользователей.
Реляционная БД - это, где вся информация предоставляется в виде таблиц.
Нормализация — это процесс организации данных в базе данных, включающий создание таблиц и установление отношений между ними в соответствии с правилами, которые обеспечивают защиту данных и делают базу данных более гибкой, устраняя избыточность и несогласованные зависимости.
Еще существуют, например, иерархические БД
DML - Data Manipulation Language
Предназначены для манипуляции данными
DDL - Data Definition Language
Предназначены для определения структуры БД и работы с объектами (CRUD)
TCL - Transaction Control Language
Предназначены для реализации и управления транзакциями
DCL - Data Control Language
Предназначены определения доступа к данным
DDL
CREATE
ALTER
DROP
RENAME
TRUNCATE
DCL
GRANT
REVOKE
Это далеко не все команды...
Тип данных - это множество допустимых значений этих данных, а также совокупность операция над ними.
INTEGER
NUMBER(p,s)
p(precision) - общее максимальное кол-во цифр
s(scale) - сколько из общего отводится на дробную часть
Важно! Кол-во на целую часть = p - s
CHAR(length)
Фиксированная длина
VARCHAR2(size)
Переменная длинна
DATE
Содержит информацию о голе, месяце, дне, часе, минуте, секунде.
TIMESTAMP(f)
Содержит информацию о голе, месяце, дне, часе, минуте, секунде, долях секунды (f - кол-во десятичных знаков)
NULL - это отсутствие данных.
0, пробел или строка 'null' - это не null. Они занимают место в памяти.
Результат арифметических операций с null - всегда null.
TIMESTAMP WITH TIMEZONE
Такой же как TIMESTAMP, но добавляется данные от часовом поясе.
TIMESTAMP WITH LOCAL TIMEZONE
Отличается от предыдущего, тем что не содержит информацию от часовом поясе.
Т.е. дата и время переводится в то время в котором вы находитесь.
Важно!!! Два пользователя могут получить разное время.
INTERVAL YEAR TO MONTH
Содержит значение год и месяц
INTERVAL DAY TO SECOND
Содержит значение дни, часы, минуты и секунды.
CLOB - Character Large Object
Можно хранить огромные текстовые элементы.
BLOB - Binary Large Object
Можно хранить огромные бинарные элементы (фото, видео).
LONG - Устаревший
Рекомендуется хранить в CLOB.
Два столбца с типом LONG не могут быть в одной таблице.
ROWID - Row ID
Относится только к Oracle. Физический адрес строки. Зашифрован.
Data Dictionary - место где Oracle хранит данные о таблицах. Нужна для DDL.
Вывод данных о структуре таблице (имя, тип).
SELECT * FROM dba_objects; // Покажет все объекты БД (если есть доступ)
SELECT * FROM user_objects; // Покажет все объекты пользователя БД
SELECT * FROM all_objects; // Покажет все объекты доступные пользователю
USER - это сущность, которая может совершить logon-процесс.
Есть имя и пароль.
SCHEMA - контейнер для всех объектов, принадлежащих одному юзеру.
По-умолчанию пустая.
Объект не может быть вне схемы!!!
Возможно получение доступа к объектам другого пользователя, но нужно предоставить доступ.
Есть пользователи по-умолчанию, например SYS и SYSTEM. Их объекты используются для служебных целей и мониторинга.
Ограничения для имен:
Если взять имя в двойные кавычки, то данные правила можно игнорировать, за исключением первого.
Но и использовать придется только с двойными кавчками.
В NAMESPACE объекты имеют уникальные имена.
INDEX и CONSTRAIN имеют свои неймспейсы, и поэтому они могут совпадать, например, с именами таблиц (но так лучше никогда не делать).
PROJECTION - выбор столбцов из таблицы.
SELECTION - выбор строк из таблицы.
JOINING - объединение таблиц.
SELECT *
FROM table_name;
// базовый синтаксис
SELECT column(s)
FROM table_name;
// projection
Выбор уникальных значений (сочетаний).
SELECT DISTINCT column(s)
FROM table_name;
Если столбцов несколько, то выводится уникальные сочетания.
Select list - это то что мы выбираем.
SELECT {Select list}
FROM table_name;
SELECT column(s), expression(s)
FROM table_name;
Оператор конкатенации - ||
Alias(псевдоним) - альтернативное имя для столбца или выражения.
SELECT column(s) alias, expression(s) alias
FROM table_name;
SELECT column(s) AS alias, expression(s) AS alias
FROM table_name;
AS можно не использовать, но оно добавляет читаемость.
SELECT column(s) "alias text", expression(s) "alias text"
FROM table_name;
Если нужен alias с пробелами в тексе то использовать двойные кавычки.
Кроме того, если нет кавычек alias будет отображен большими буквами.
Служебная таблица с одним столбцом и одной строкой, и с фиктивными данными.
Если в тексте хочется использовать одинарную кавычку то ее надо экранировать другой.
Пример: 'It's my life' - не сработает, 'It''s my life' - сработает.
Кроме этого можно использовать оператор q (от Quote):
после первого знакомства
SELECT *|{DISTINCT column(s) alias, expression(s) alias}
FROM table_name;
SELECT *|{DISTINCT column(s) alias, expression(s) alias}
FROM table_name
WHERE condition(s);
= > < >= <= != <>
BETWEEN IN
IS NULL
| LIKE |
Служебные символы могут быть заменены: % - на любое кол-во символов; _ - на один символ; Для экранирования: ... LIKE 'St\_%' ESCAPE '\'; |
AND OR NOT
| Приоритет | Оператор |
|---|---|
| 1 | ( ) |
| 2 | / * |
| 3 | + - |
| 4 | || |
| 5 | = > < >= <= |
| 6 | [NOT] LIKE IS [NOT] NULL [NOT] IN |
| 7 | [NOT] BETWEEN |
| 8 | != <> |
| 9 | NOT |
| 10 | AND |
| 11 | OR |
SELECT *|{DISTINCT column(s) alias, expression(s) alias}
FROM table_name
WHERE condition(s)
ORDER BY {column(s)|expressions(s)|numeric position} {ASC|DESC} {NULLS FIRST|LAST};
numeric position считается из select list'а
Функции которые работают с текстом
Важно! Переводится первый символ каждого слова.
Важно! Считаются все символы, включая проблелы и т.п.
По умолчанию: тип удаления both, а символ для удаления - пробел.
Важно: удалять можно только один какой-то символ.
Где s - строка в которой ищем. searchString - искомый текст; startPosition - позиция с которой начинается поиск; n-ное появление;
Где s - строка в которой ищем. startPosition - позиция с которой начинаем извлекать; n - кол-во извлекаемых символов;
Если startPosition<0 то позиция считается с конца s.
Важно: Если длина строки s меньше startPosition то функция вернет null.
Где s - строка в которой производим замену. searchString - текст, который нужно заменить; replacementString - текст, на который нужно заменить;
Если replacementString не указан, то функция удалит searchString из s.
Функции которые работают с числами
Математическое округление. При precision<0 округляется целая часть.
Отбрасывание числа. При precision<0 происходит замена на 0 в целой части числа.
Интересно: можно использовать дробные числа.
Функции для работы с датами
При вычитании даты из даты получаем число кол-во дней между датами.
Можно прибавлять/вычитать число в результате дата + число дней
Складывать/делить/умножать даты нельзя.
Возвращает текущее время db-server'а
Возвращает дробное число, кол-во месяцев между датами.
Стандарт oracle месяц = 31 день.
Если первый аргумент больше второго - возвращает отрицательное число.
Добавление/вычитание кол-ва месяцев.
Дробная часть в аргументе игнорируется, т.е. добавляются только целые месяцы.
Где, date - дата, day_of_the_week - день недели.
Возвращает дату следующего указанного дня недели.
Возвращает дату, последний день месяца в указанной дате.
Где, date - дата, date_precision_format - точность округления*.
Где, date - дата, date_precision_format - точность округления*.
| Код | Граница |
|---|---|
| CC | век |
| YYYY | год |
| Q | четверть |
| MM | месяц |
| W | неделя |
| DD | день |
| HH | час |
| MI | минута |
Функции конвертации данных из одного типа в другой
Возвращает тип VARCHAR2
| Элемент | Описание | Формат | Число | Текст |
|---|---|---|---|---|
| 9 | Ширина | 99999 | 18 | 18 |
| 0 | Отображение нуля | 099999 | 18 | 000018 |
| . | Позиция десятичной точки | 099999.99 | 18.35 | 000018.350 |
| D | Позиция десятичного разделителя | 099999D999 | 18.35 | 000018.350 |
| , | Позиция запятой | 099,999,999 | 1234567 | 001,234,567 |
| G | Позиция разделителя групп | 099999G999 | 1234567 | 001,234,567 |
| $ | Знак $ | $099999 | 18 | $000018 |
| L | Локальная валюта | L099999 | 18 | $000018 |
| MI | Позиция знака - | 099999MI | -18 | 000018- |
| PR | Скобки для отрицательных чисел | 099999PR | -18 | <000018> |
| S | Префикс + или - | S099999 | 18 | +000018 |
* Если число не влазит в формат получаем строку из символа #
Возвращает тип VARCHAR2
| Элемент | Описание | Текст |
|---|---|---|
| Y | Последняя цифра года | 9 |
| YY | Последние две цифры года | 19 |
| YYY | Последние три цифры года | 019 |
| YYYY | Год (целиком) | 2019 |
| RR | Год в формате 2-х цифр | 19 |
| YEAR | Буквенное описание года (Чувствительно к регистру) | TWENTY NINETEEN |
| MM | Месяц в формате 2-х цифр | 09 |
| MON | Месяц в формате 3-х букв (Чувствительно к регистру) | SEP |
| MONTH | Буквенное написание месяца (Чувствительно к регистру) | SEPTEMBER |
| D | День недели | 6 |
| DD | День месяца: две цифры | 20 |
| DDD | День года | 263 |
| DY | День недели: три буквы (Чувствительно к регистру) | FRI |
| DAY | День недели: текстовое название (Чувствительно к регистру) | FRIDAY |
| W | Неделя месяца | 3 |
| WW | Неделя года | 38 |
| Q | Квартал года | 3 |
| СС | Век | 21 |
| AM, PM, A.M., P.M. | Индикатор | PM |
| HH, HH12 и HH24 | Формат времени | 04, 04, 16 |
| MI | Минуты | 17 |
| SS | Секунды | 38 |
| SSSSS | Секунды после полуночи | 58638 |
| - / . , ? # ! | Пунктуация: 'MM.YY' | 09.19 |
| "Текст" | '"Quarter " Q " of " Year' | Quarter 3 of Twenty Nineteen |
| TH | 'DDth " of " Month' | 20TH of September |
| SP | Буквенное написание 'MmSp Month Yyyysp' | Nine September Two Thousand Nineteen |
| THSP, SPTH | Комбинация 'hh24SpTh' | sixteenth |
* Если появляются лишние пробелы, то перед шаблоном надо использовать fm, т.е. ,например, fmMON
Используем те же маски
Используем те же маски
Для упрощения работы с NULL
Если if_null = null вернется null
Парамтры if_not_null и if_null должны быть одного типа (или автоматически приводимы), но не могут быть Long.
Если параметры равны возвращается NULL, если неравны - первый параметр.
Возвращает первое значение из списка не равное NULL.
Если все параметры NULL, то будет возвращен NULL.
Функции if-then-else логики
Чисто oracle фишка, в чистом SQL ее нет.
Выражение expr сравнивается с comp1, если совпадает - возвращает if_true1. И так N раз. Если нет не одного совпадения возвращает if_else. Если if_else не указан - вернется NULL.
CASE expr
WHEN comp1 THEN if_true1
WHEN comp2 THEN if_true2
...
WHEN compN THEN if_trueN
ELSE if_false
END
Сравниваем expr с разными comp.
CASE
WHEN cond1 THEN if_true1
WHEN cond2 THEN if_true2
...
WHEN condN THEN if_trueN
ELSE if_false
END
Отсутствует expr, т.е. внутри cond мы можен сравнивать что угодно.
В обоих случах, если нет if_false и ни одно из условий не выполнилось вернется NULL
Group или Multiple-row работают с агрегированной информацией
подсчет по любому типу, null значения игнорируются
expression - выражение или столбец
сумма, на вход только числовые значения, null значения игнорируются
SUM({DISTINCT|ALL} expression)expression - выражение или столбец
среднее арифметическое, на вход только числовые значения, null значения игнорируются
AVG({DISTINCT|ALL} expression)максимальное и минимальное, на вход можно число, дату и строку
MAX({DISTINCT|ALL} expression) и MIN({DISTINCT|ALL} expression)При группировке указывать алиас нельзя, только название столбца.
HAVING работает с группами, если нет GROUP BY, то он работает с текущей группой (но так лучше не делать и использовать в паре).
Максимальная вложенность multiple-row функций = 2.
еще
EQUIJOIN - это объединения в условиях которых используется равенство критериев.
NONEEQUIJOIN - если в условии не равенство, сравнение, between, ...
и отдельно
В INNER JOIN результат попадают только строки удовлетворяющие условию объединения. Иные не выводятся!!!
Таблица к, которой присоединяем называется source, та в которой ищем - target.
Объединяет по столбцам с одинаковыми именами, т.е. не дает выбрать критерий.
Если несколько столбцов, то объединение пойдет по всем.
Если нет столбцов с одинаковыми именами произойдет CROSS JOIN.
При соединении поиск проходит по всем строкам таргета, а не останавливается когда совпало.
Поэтому может быть несколько совпадений!!!
Если в таргете не найдено совпадения, но в результат строка не попадает.
SELECT column(s)
FROM table_1
NATURAL JOIN table_2;
Если нужно в селект листе указать столбец, по которому происходит объединение, то он указывается без имени таблицы или алиаса таблицы (иначе ошибка)
SELECT column(s)
FROM table_1
JOIN table_2 USING (column(s));
В скобках указываем имена столбцов, по которым хотим произвести объединение.
Если указать все одинаковые какие есть, то результат будет такой же как у NATURAL JOIN.
SELECT column(s)
FROM table_1
JOIN table_2 ON (column1 = column1));
В OUTER JOIN попадают также строки которы не соответствуют условию объединения. Какие определяется типом запроса. Очень часто используется в реальной жизни.
LEFT OUTER JOIN - выводится результат INNER JOIN, а потом добавляются все строки из левой таблицы, по которым условие не было соблюдено.
RIGHT OUTER JOIN - выводится результат INNER JOIN, а потом добавляются все строки из правой таблицы, по которым условие не было соблюдено.
FULL OUTER JOIN - выводится результат INNER JOIN, а потом добавляются все строки из левой таблицы, а потом из правой, по которым условие не было соблюдено.
SELECT column(s)
FROM table_1
LEFT|RIGHT|FULL OUTER JOIN table_2
ON (column1 = column2);
По факту, это объединение всех строк одной таблицы со всеми строками другой.
Т.е. если в первой 25 строк, во второй - 5, то в итоге 100
SELECT column(s)
FROM table_1
CROSS JOIN table_2;
Это еще называют - Cartesian product или Декартово произведение
Таблицы через запятую, условие объединения в WHERE.
SELECT column(s)
FROM table_1 t1, table_2 t2
WHERE t1.column1 = t2.column2;
Для OUTER JOIN используем оператор соединения (+) в условии в WHERE, который указывает на то, какая из таблиц является таблицей добавления. Т.е. строки из этой таблицы попадают в результат только при соответствии условию.
SELECT column(s)
FROM table_1 t1, table_2 t2
WHERE t1.column1(+) = t2.column2;
Аналогично используем (+), только с другой стороны.
SELECT column(s)
FROM table_1 t1, table_2 t2
WHERE t1.column1 = t2.column2(+);
Просто указываем таблицы через запятую.
SELECT column(s)
FROM table_1 t1, table_2 t2;
С помощью oracle синтаксиса сделать нельзя - надо использовать базовый синтаксис SQL.
SINGLE ROW
Возвращает одну строку.
SCALAR
Частный случай SINGLE ROW, когда возвращается одна строка и один столбец.
MULTIPLE ROW
Результат - несколько строк.
Не зависимо от типа подзапрос один раз выполняется и запоминается. Дальше используется сохраненное значение.
Такие подзапросы, которые связаны с внешним подзапросом.
Т.е. если в подзапросе используется что-то из данных текущей строки, а не агрегированное. Т.е. то, что требует пересчета для каждой строки.
Коррелированные запросы выполняются каждый раз. (что ресурсоемко!)
COMPOUND QUERY - сложный запрос. Состоит из нескольких SELECT'ов, которые не являются SUBQUERY.
SELECT x FROM table1
UNION
SELECT y FROM table2
UNION ALL - просто объединяет, не производя никаких действий.
UNION - сортирует (естественным путем), удаляет дубликаты и объединяет. Результат тоже сортирован.
INTERSECT - пересечение. Находит общие значения во множествах, исключает дубликаты и сортирует результат.
MINUS - оставляет те значения из первого множества, которых нет во втором. Также убирает дубликаты и делает сортировку.
В класcическом SQL оператор называется EXCEPT.
Важно: сортировка множеств происходит с самого начала.
Если заведомо известно, что дубликатов нет, то лучше всего использовать UNION ALL.
В столбцах должны совпадать типы данных (или должны быть из одного семейства).
Пример:
SELECT1 = DATE, INTEGER, VARCHAR2
SELECT2 = TIMESTAMP, NUMBER, CHAR
В результате будут выбраны наиболее широкие(точные) типы:
RESULT = TIMESTAMP, NUMBER, VARCHAR2
Если не устаивает стандартная сортировка (сортируются все столбцы в натуральном порядке), то после можно использовать простой ORDER BY.
SELECT x FROM table1
UNION
SELECT y FROM table2
ORDER BY name DESC
Сортировать можно только конечный результат.
Если в селект-листе указана звездочка, то для сортировки нельзя использовать имя столбца:
SELECT * FROM table1
UNION
SELECT * FROM table2
ORDER BY name DESC // Будет ошибка
Однако!!! Сортировку можно выполнить по номеру столбца:
SELECT * FROM table1
UNION
SELECT * FROM table2
ORDER BY 3 DESC // Будет отсортировано по третьему столбцу
В результат попадают алиасы из первого select'а. Из второго игнорируются.
Можно использовать несколько set операторов.
Выполняются последовательно.
Изменить приоритет можно с использованием скобок.
Select рассматривали ранее. Он самый большой и часто используемый.
Далее все остальные команды.
Вставляет одну или несколько строк в таблицу.
INSERT INTO table_name (column(s))
VALUES (value(s));
Если не использовать название колонок, то необходимо в запросе указать данные для всех столбцов.
Причем порядок в значениях должен быть такой же, как и у колонок.
Если указываются имена столбцов, то порядок значений должен быть такой, как запросе.
Правило хорошего тона: всегда использовать названия столбцов. Т.к. если в таблицу будет добавлено новое поле, то запрос без указания колонок перестанет работать, а с указанием - нет.
Для вставки лучше сразу в запросе использовать функции.
На пример: INITCAP() - первая буква большая, UPPER() - все буквы большие.
Но самое главное - конвертация времени, например:
TO_DATE('28-NOW-2023', 'DD-MON-YYYY')
Потому что, если в базе изменят формат, то автоконвертация из строки в дату может не сработать.
Так же можно вставлять несколько строк:
INSERT INTO table_name (column(s))
SUBQUERY;
Еще можно вставлять в несколько таблиц, но это этим обычно не пользуются.
Изменение информации в уже имеющихся строках.
Может менять одну или несколько строк, а так же один или несколько столбцов.
Не может изменять данные в нескольких таблицах.
UPDATE table_name
SET column(s)=value(s)
WHERE condition(s);
Возможно использование SUBQUERY
UPDATE table_name
SET column(s)=subquery(s)
WHERE column=subquery;
Удаляет строки из таблицы.
Удаляет строку полностью.
Может удалить одну или несколько строк.
DELETE
FROM table_name
WHERE conditions;
Использование SUBQUERY возможно только в WHERE
DELETE
FROM table_name
WHERE column=subquery;
Может совмещать предыдущие операторы
Используется очень редко.
MERGE INTO table_name1 t1
USING {table_name2|subquery} t2
ON (t1.column=t2.column)
WHEN MATCHED THEN
UPDATE SET column=value
DELETE WHERE condition
WHEN NOT MATCHED THEN
INSERT (value1, value2)
VALUES (column1, column2)
ATOMICITY - атомарность
Транзакция атомарна(неделима). Т.е. либо выполняются все DML команды внутри транзакции, либо не выполняется ни одна.
CONSISTENCY - консистентность
При запуске SELECT учитывается только состояние таблицы на момент запуска.
ISOLATION - изолированность
Изменения видны другим пользователям только после завершения транзакции.
DURABLE - долговечность
После окончания транзакции данные не могут быть потеряны.
Транзакция запускается непосредственно перед стартом DML команды: INSET, UPDATE, DELETE или MERGE.
Транзакция запускается не явно, и нет никакой команды для запуска транзакции
При наличии открытой транзакции, DML команды не открывают новую, а выполняются в уже открытой.
Транзакция закрывается только тогда будет вызваны COMMIT или ROLLBACK.
Важно!!! SAVEPOINT не завершает транзакцию.
Транзакция не связана ни с какой таблицей.
Любая DDL или DCL команда завершают транзакцию коммитом.
Важно!!! Когда выходишь из тула происходит закрытие транзакции, а вот чем (commit или rollback) зависит от настроек.
Также закрытие транзакции происходит при завершении сессии (тут только rollback).
Отмена внесенных изменений.
ROLLBACK TO SAVEPOINT savepoint_name;
Может выполняться значительное время.
Важно!!!
ROLLBACK без параметров заканчивает транзакцию.
ROLLBACK TO SAVEPOINT не заканчивает транзакцию.
Это маркер, который используется при ROLLBACK'е.
Создается в текущей транзакции.
После закрытия транзакции - уничтожается.
SAVEPOINT не является стандартной командой SQL
Если очень хочется что-бы после каждой DML команды проходил коммит то можно использовать следующе:
Но лучше это никогда не использовать!
SELECT *
FROM table_name
...
FOR UPDATE;
На все выведенные строки будет поставлен lock, и при попытке изменить эти данные из другой сессии произойдет "зависание", до тех пор пока не будет снят lock.
Lock будет снят при закрытии транзакции, в которой он был вызван.
CREATE TABLE schema.table ORGANIZATION HEAP
(column_name datatype DEFAULT expr,
column_name datatype DEFAULT expr,
...);
Таблица будет сожержать результат запроса.
Добавление столбца
ALTER TABLE table_name
ADD (column_name data_type DEFAULT expr);
Изменение столбца
ALTER TABLE table_name
MODIFY (column_name data_type DEFAULT expr);
Нельзя изменить если может быть нарушение целосности данных.
Удаление столбца
ALTER TABLE table_name
DROP COLUMN column_name;
Нельзя удалить если на этот столбец что-то ссылается.
Неиспользуемые столбцы
ALTER TABLE table_name
SET UNUSED COLUMN column_name;
ALTER TABLE table_name
DROP UNUSED COLUMNS;
SET UNUSED исключает колонку из таблицы, но физического удаления не происходит. Некоторая альтернатива DROP, но с возможностью отложенного удаления данных. Удобно если в таблице куча данных, и их удаление занимает много времени, то при таком раскладе можно выбоать время когда данные физически будут удалены.
Переименование столбца
ALTER TABLE table_name
RENAME COLUMN column_name_old TO column_name_new;
Запрет на изменение таблицы
ALTER TABLE table_name
READ ONLY;
Данные нельзя изменить (вставить, удалить и т.п.), но саму таблицу можно удалить.
Пока в другой сессии открыта транзакция, то в текущей сессии DDL команда не может быть выполнена.
Она будет ждать закрытия транзакции.
По факту перед и после DDL команд стоит автокоммит.
Таблица осталась, а строк в ней нет. Не изменяет структуру таблицы.
Удаляет все строки.
Нельзя восстановить с помощью ROLLBACK.
Удалена информация и сама таблица с ее структурой.
Нельзя удалить, если в другой сессии есть открытая транзакция, или есть связи с другими таблицами.
CONSTRAINT - это бизнес правило, которому должно удовлетворять значение.
Нужно обязательно что-то вставлять.
Для уникальной идентификации. Уникальный и not null.
Для указания связи с primary key. Может быть null.
Проверка соответствия условию.
Допускаются только уникальные значения.
... CONSTRAINT constraint_name constraint_type ...
Constraint можно создавать прямо при создании таблицы (CREATE TABLE) в описании поля (inline level) или же после описания всех строк (table level), но тогда после надо указывать имя столбца.
Constraint можно создавать на несколько столбцов (composite constraint). Используется вариант table level, и в скобках указываются те колонки, на которые необходимо распространить правило.
Если не указать имя, оно будет присвоено автоматически.
Если назначать constraint на уже имеющуюся таблицу с данными (ALTER TABLE), то данные должны удовлетворять условию, иначе будет ошибка и constraint не будет создан.
Есть два варианта, через оператор MODIFY или через ADD.
Удалить constraint можно в любое время.
Принуждает столбец(ы) содержать только уникальные значения. Исключение - null.
Создает индекс. Он делает поиск быстрым
При поиске по null делает full table scan!
Не разрешает столбцам содержать null.
Нужно указывать для каждого столбца (inline level), в table level этот constraint не создать.
Если у поля есть DEFAULT, то он будет использоваться если добавляем без указания имени столбца в VALUES. При попытке добавить null "руками" - будет ошибка.
Принуждает столбец(ы) содержать только уникальные значения и не разрешает содержать значение null.
По факту, это UNIQUE и NOT NULL вместе.
В таблице не может быть больше одного PRIMARY KEY столбца.
Может быть композитным (состоять из нескольких столбцов.)
Принуждает использовать значение из определенного столбца другой таблицы или null.
FOREIGN KEY определен в child-таблице и его столбец зависит от столбца в parrent-таблице. Т.е. создается parent-child relationship. Отношение Many-To-One.
Должен ссылаться на UNIQUE или PRIMARY KEY.
Inline level
... CONSTRAINT constraint_name REFERENCES parent-table_name(column_name) ...
Table level
... CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES parent-table_name(column_name) ...
Если не указать column_name, то ссылка будет на FOREIGN KEY.
Для задания поведения при удалении используется ON DELETE с параметром:
Так же, при наличии связи FOREIGN KEY нельзя очистить(TRUNCATE) или удалить(DROP) таблицу.
Принуждает использовать значения, которые удовлетворяются его условию(ям).
В условиях можно использовать некоторые функции, но не все.
В условии нельзя использовать SUBQUERY.
Где может быть использован constraint:
| Constraint | Inline level | Table level | MODIFY | ADD |
|---|---|---|---|---|
| UNIQUE | Y | Y | Y | Y |
| NOT NULL | Y | N | Y | N |
| PRIMARY KEY | Y | Y | Y | Y |
| FOREIGN KEY | Y | Y | Y | Y |
| CHECK | Y | Y | Y | Y |
INDEX - это объект БД, создаваемый с целью повышения производительности поиска данных.
Ускорение работы, с помощью индексов, достигается, в первую очередь, за счет того, что индекс имеет структуру, оптимизированную конкретно под поиск.
Используются при работе PRIMARY KEY и UNIQUE. Индексы создаются автоматически.
CREATE {UNIQUE|BITMAP} INDEX
schema.index_name ON
schema.table_name (column1 , collumn2, ...);
Если не указать UNIQUE или BITMAP будет создан B-TREE
Если не указать UNIQUE будет создан B-TREE NOT UNIQUE
Индексы могут быть композитными.
Нельзя удалить индексы, которые используются в constraint.
Огромный плюс - индексы ускоряют поиск (почти всегда), т.е. ускоряют SELECT.
Огромный минус - замедляют выполнение других DML команд.
rowid - присутствует всегда.
Что бы его увидеть в SELECT надо указать руками.
По нему можно делать SELECT
И rowid глобально уникален!
Используется по умолчанию.
На основе сбалансированного дерева.
UNIQUE(по-умолчанию) - не поддерживает создание дубликатов.
NOT_UNIQUE - поддерживает создание дубликатов.
Будет эффективен когда:
Создают битовую карту использования значения
Типа такого: 001010101
Могут использовать null, т.к. это просто значение.
Это считается большим преимуществом над B-TREE
Могут сильно ускорять работу где используются AND, OR и NOT операторы.
Будет эффективен когда:
Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.
Почему используют VIEW:
Select стоящий за view обычно оптимизируется программистом, что бы пользователь не задумывался о доработке.
SIMPLE
COMPLEX
SIMPLE - Можно сделать INSERT(UPDATE, DELETE)
COMPLEX - Нельзя сделать INSERT(UPDATE, DELETE)
Т.е. можно выполнить операции только, в том случае если строку из view можно однозначно сопоставить со строкой в таблице.
Иногда в COMPLEX может быть четкая связь для UPDATE или DELETE и запрос может пройти.
Если используется rownum то это всегда COMPLEX.
CREATE OR REPLACE {FORSE|NOFORSE} VIEW
schema.view_name (alias1. alias2, ...)
AS subquery
WITH CHECK OPTION {CONSTRAINT constraint_name}
Для того что бы не вставить строки, которые будут не видны в представлении.
WITH READ ONLY {CONSTRAINT constraint_name};
Что бы запретить использование DML команд. Только для селекта.
CONSTRAINT constraint_name - никакого отношения к constraint, используется для того что бы в ошибке выводилось нормальное имя.
Таблицы и представления находятся в одном namespace, поэтому имена должны быть разными.
Проверка работоспособности view.
Изменить view невозможно.
Что бы внести изменения надо удалить, а затем создать новое.
Альтернативное имя объекта БД.
Полное имя таблицы выглядит так:
schema.table@database
Позволяет использовать таблицы из других схем и баз, без их указания.
Public synonym находятся в public_namespace.
Private synonym - в namespace, поэтому названия не должны совпадать с таблицами и представлениями.
Приоритет поиска - сначала ищется в схеме (private), а уже потом в public_namespace.
CREATE PUBLIC SYNONYM synonym_name
FOR object_name;
Создание синонима.
ALTER PUBLIC SYNONYM synonym_name
COMPILE;
Проверка работоспособности синонима.
DROP PUBLIC SYNONYM synonym_name;
Удаление синонима.
Не привязан к таблицам!
Не привязан к сесиям!
Не привязан к транзакциям!
CREATE SEQUENCE schema.sequence_name
INCREMENT BY number
START WITH number
{MAXVALUE number|NOMAXVALUE}
{MINVALUE number|NOMINVALUE}
{CYCLE|NOCYCLE}
{CACHE number|NOCACHE};
Можем узнать следующее значение последовательности используя nextval.
Можем узнать текущее значение последовательности используя currval, но это будут не последнее значение последовательности, а последнее значение используемое в этой сессии.
select s1.nextval from dual;
select s1.currval from dual;
Вызов currval, если nextval ни разу не вызван, приведет к ошибке.
CACHE default = 20
При совместном использовании START WITH и CYCLE при переходе на следующий виток цикла отсчет начинается с 1, а не с параметра указанного в START WITH.
CREATE SEQUENCE s1
START WITH 7
INCREMENT 4
MAXVALUE 17
CYCLE
CACHE 2
Результат работы: 7, 11 , 15, 1, 5 ...
ALTER SEQUENCE schema.sequence_name
INCREMENT BY number
{MAXVALUE number|NOMAXVALUE}
{MINVALUE number|NOMINVALUE}
{CYCLE|NOCYCLE}
{CACHE number|NOCACHE};
Можем поменять все параметры кроме START WITH
DROP SEQUENCE schema.sequence_name;