Как сделать ограничение sql
Добавил пользователь Алексей Ф. Обновлено: 05.10.2024
Ограничения являются средством, с помощью которого БД может принуждать к выполнению бизнес-правил и гарантировать что данные соответсвуют модели сущность-связь определённой системным анализом определяя структуры данных приложения. Например, бизнес-аналитик в компании решил что каждый покупатель и каждый заказ должен определяться уникальным числом, что нельзя создать заказ до создания покупателя и каждый заказ должен иметь валидную дату и сумму большую нуля. Это может быть достигнуто путём создания первичных ключей для столбца CUSTOMER_ID таблицы CUSTOMERS и столбца ORDER_ID таблицы ORDERS, создания внешнего ключа для таблица ORDERS ссылающегося на таблицу CUSTOMERS, создания ограничения “not null” для столбца ORDER_DATE (тип данных DATE сам проверит удовлятворяет ли введённое значение типа дата или нет) и ограничения проверки для столбца ORDER_AMOUNT таблицы ORDERS.
Если какой либо DML запрос к таблице с ограничениями нарушит ограничение, тогда весь запрос будет отменем автоматически. Надо помнить что DML запрос может обновлять много строк и частично обновление было успешным до нарушения ограничения для конкретной строки. Если запрос это часть транзакции состоящей из нескольких запросов, то результат выполнения запросов которые уже выполнились успешно остаётся но не подтверждённым.
A constraint violation will force an automatic rollback of the entire statement that hit the problem, not just the single action within the statement, and not the entire transaction.
Типы ограничений
Ограничения поддерживаемые Orace это
- UNIQUE
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
- CHECK
У ограничений есть имя. Хорошей практикой считается именовать их согласно конвенции имен, но если явно не указывать имя, Oracle будет использовать сгенерированное имя.
Ограничение уникальности
Ограничение уникальности назначает столбец (или группу столбцов) для которых значение должно быть разным для каждой строки в таблице. Если ограничение настроено для одного столбца, это называется столбец-ключ (key column). Если ограничение состоит из нескольких столбцов (называется составной ключ (composite key)), столбцы не должны быть одинакового типы и располагаться последовательно в таблице.
Странностью уникального ограничения можно считать возможность добавления значения NULL в столбцы ключа; более того можно иметь неограниченное количество строк со значение NULL в столбцах ключа. Таким образом запрос строк по ключу ограничения гарантирует одну строку для значения – пока не используется NULL, в таком случае все строки со значением NULL будут результатом запроса.
It is possible to insert many rows with NULLs in a column with a unique constraint. This is not possible for a column with a primary key constraint.
Ограничения уникальности работают с помощью индексов. Когда мы определяем ограничение, Oracle проверит существует ли индекс для столбцов ограничения, и если индекс не сущетвует, он будет создан. Затем когда происходи попытка вставки новой строки, Oracle просматривает индекс на существование значения ключа; если значение сущесвтует – строка будет отклонена. Индекс ограничения – B* Tree индекс, и он не хранит значения NULL, вот почему ограничение не работает для NULL (т.е. можно добавлять много строк со значением NULL). Как мы обсуждали выше, целью индексов является не только обеспечение работоспособности механизма ограничений, но и повышение производительности запросов с секцией WHERE и объединением таблиц. Если в запросе используется WHERE key_column IS NULL – Oracle не может использовать индекс (потому что индекс не хранит значения NULL) и всегда будет использовано полное чтение таблицы.
Ограничения NOT-NULL
Ограничения NOT-NULL принуждают указать данные для столбцов ключа. Эти ограничения накладываются на столбец и иногда их называют обязательными для заполнения столбцами (mandatory columns). Если несколько столбцов должны иметь значения, то вы не можете создать одно ограничение для группы – необходимо создавать ограничения для каждого столбца.
Любая попытка записи строки без указания значения для обязательного столбца вернёт ошибку. Возможно обойти эту проверку указав директиву DEFAULT при создании таблицы.
Первичный ключ
Первичный ключ является средством для поиска любой одной строки в таблице. Реляционная парадигма утверждает что каждая таблица должна иметь первичный ключ: столбец (или группу столбцов) которые можно использовать для выделения каждой строки. Oracle позволяет создавать таблицы (как и некоторые другие РСУБД) без первичного ключа.
Реализацией первичного ключа является комбинация ограничений целостности и обязательности. Значение в столбцах ключе должно быть уникальным и не NULL. Так же как и для ограничения уникальности индекс должен существовать для столбцов ключа, иначе он будет создан. У таблицы может быть только один первичный ключ. Но у таблицы может быть сколько угодно ограничений уникальности и not-null ограничений. Если у вас есть несколько столбцов которые должны быть уникальны и обязательны вы можете использовать часть из них для создания первичного ключа, а для остальных создать ограничения уникальности и обязательности. Например таблица сотрудников: все сотрудники должны иметь уникальный номер (первичный ключ) и также значения email, номер соц. страха должны быть уникальны и обязательны для заполнения.
Unique and primary key constraints need an index. If one does not exist, one will be created automatically.
Внешние ключи
Внешний ключ определяет отношение родитель-наследник. Это ограничение назначает столбец (или столбцы) в дочерней таблице которые должны соответствовать столбцу(ам) первичного ключа родительской таблицы. Соответствующие столбцы необязательно должны иметь одинаковые названия, но они должны быть одного типа данных. Внешний ключ определяет отношения один-ко-многим в третьей нормальной форме.
Если у родительской таблицы есть ограничение уникальности вместе (или вместо) первичного ключа, эти столбцы тоже могут использоваться как основа для внешнего ключа, даже если они не обязательны (nullable).
A foreign key constraint is defined on the child table, but a unique or primary key constraint must already exist on the parent table.
Как и ограничение уникальности внешний ключ допускает значение NULL в столбцах ключа. Вы можете вставить строки в дочернюю таблицу со значением NULL в столбцах внешнего ключа – даже если в родительской таблице нет строки со значением NULL. Это создаст строки призраки (orphaned rows) и может вызвать определённые недоразмения. Как правило все столбцы ограничения уникальности и все столбцы внешнего ключа лучше определять вместе с ограничениями not-null: так же это обычно требование бизнеса.
При попытки вставить строку в дочернюю таблицу со значением ключа которого нет в родительской таблице – произойдёт ошибка выполнения запроса. Также попытка удаления строки в родительской таблице вернёт ошибку если существуют строки в дочерней которые ссылаются на эту строке. Существует два способа обойти это ограничение. Во-первых, ограничения могут быть созданы с директивой ON DELETE CASCADE. Это значит что если мы удаляется строка из родительской таблицы – Oracle автоматически найдёт все строки ссылающиеся на эту строку в дочерней таблице и удалит их тоже. Менее кардинальный способ – использование директивы ON DELETE SET NULL. В этом случае если строка родительской таблицы удаляется – Oracle найдёт все соответствующие строки в дочерней таблице и установит значение ключа в NULL. Это значит что строки в дочерней таблице станут строками-призраками – но они будут существовать в таблице. Если столбцы внешнего ключа имеют ограничение not-null – то удаление из родительской таблицы вернёт ошибку. Нельзя ни удалить ни выполнить операцию TRUNCATE для родительской таблицы, даже если нет ни одной строки в дочерней таблице. Даже если использованы ON DELETE SET NULL или ON DELETE CASCADE.
Отдельным видом внешнего ключа является внешний ключ ссылающийся сам на себя (self-referencing). Такой ключ определяет отношение где родительский и дочернии строки находятся в одной таблице. Хорошим примером является таблица сотрудников, в которой есть столбец MANAGER_ID. И менеджер и сотрудник являются сотрудниками. Если первичный ключ EMPLOYEE_ID то для внешнего ключа используем EMPLOYEE_ID как родительский столбец и MANAGER_ID как дочерний. Если у сотрудника нет менеджера, то такая строка указывает сама на себя.
Ограничения проверки значения (Check constraints)
Ограничения проверки значения используются для проверки данных на удовлетворение простым правилам, к примеру введённое значение должно попадать в период значений. Правилом должно быть выражение результатом проверки которого будет либо ПРАВДА либо ЛОЖЬ. Правила могут использовать литералы, или другие столбцы той же строки и они могут использовать некоторые функции. Сколько угодно правил можно применить к одному ограничение проверки значения, но нельзя использовать подзапросы или такие функции как SYSDATE.
The not-null constraint is in fact implemented as a preconfigured check constraint.
Определение ограничений
Ограничение можно определить при создании таблицы или добавить позже. При создании таблицы, ограничения можно указать в строке определяющей столбец используемые в ограничении, либо в конце оператора создания таблицы. В конце оператора создания вы можете определять ограничения более гибко. В строке определения столбца вы не можете создать ограничение использующее другие столбцы таблицы. При определении ограничения требующего индекс в момент создания таблицы также создастся индекс.
Расмотрим два запроса создания таблицы
1 create table dept(
2 deptno number(2,0) constraint dept_deptno_pk primary key
3 constraint dept_deptno_ck check (deptno between 10 and 90),
4 dname varchar2(20) constraint dept_dname_nn not null);
5 create table emp(
6 empno number(4,0) constraint emp_empno_pk primary key,
7 ename varchar2(20) constraint emp_ename_nn not null,
8 mgr number (4,0) constraint emp_mgr_fk references emp (empno),
10 hiredate date,
11 deptno number(2,0) constraint emp_deptno_fk references dept(deptno)
12 on delete set null,
13 email varchar2(30) constraint emp_email_uk unique,
14 constraint emp_hiredate_ck check (hiredate >= dob + 365*16),
15 constraint emp_email_ck
16 check ((instr(email,’@’) > 0) and (instr(email,’.’) > 0)));
- Первая таблица создаётся с именем DEPT и предназначена для хранения одной строки для каждого департамента.
- DEPTNO – числовое поле, 2 цифры, нет точек после запятой. Первичный ключ таблицы – имя ограничения DEPT_DEPTNO_PK.
- Второе ограничение столбца DEPTNO это проверка значения на период от 10 до 90. Имя этого ограничения DEPT_DEPTNO_CK.
- Столбец DNAME – это строка нефиксированной длины, обязательна для заполнения – ограничение DEPT_DNAME_NN.
- Вторая табилца это EMP, предназаченная для хранения строки для каждого сотрудника.
- Столбец EMPNO типа данных NUMERIC, 4 цифры без дробной части первичный ключ EMP_EMPNO_PK. ENAME
- Столбец ENAME строка произвольной длины до 20 символов с not null ограничением EMP_ENAME_NN
- Столбец MGR определён так же как и EMPNO плюс является внешним ключом таблицы к самой себе по полям MGR-EMP_NO. Нет ограничения на not-null поэтому может быть NULL
- DOB – дата рождения сотрудника, тип данных дата нет ограничений
- HIREDATE – дата приёма на работу, нет ограничений
- DEPTNO департамент, определение столбца такое же как столбец DEPTNO таблицы DEPT. Внешний ключ к таблице департаментов: нельзя вставить строку со значением которого нет в таблице DEPT. Но значение может быть
- Внешний ключ EMP_DEPTO_FK определён с директивой ON DELETE SET NULL, т.е. при удалении строки из таблицы DEPT значение столбца DEPTNO во всех относящиеся к удаляемой строке будет обновлено в значение NULL.
- EMAIL – строка переменной длины и имеет ограничение уникальности (но может быть NULL)
- Определение дополнительного ограничения EMP_HIREDATE_CK. Дата приёма на работу должна быть не раньше чем 16 лет после даты рождения. Данное ограничение невозможно создать в строке определения столбца HIREDATE, так как ограничение ссылается на другой столбец
- Определение дополнительного ограничения проверки значения для строки EMAIL. Вызываются функции которые проверяют наличие @ и “.” в значении и если хотя бы одна функция вернёт ЛОЖЬ то строка будет отклонена.
Эти примеры показывают некоторые возможности определения ограничения при создании таблицы. Остальные возможности это
Управление созданием индексов для внешних ключей и ограничений уникальности
Определение времени проверки ограничения: во время вставки (значение по умолчанию) или позже, в момент подтверждения транзакции
Включено ли ограничение (по умолчанию) или отключено
Возможно создать таблицу без ограничений а затем добавить ограничения командой ALTER TABLE. Результат будет одинаковый но определение таблицы будет разбито на несколько запросов вместо одного.
Состояния ограничений
В любой момент времени, каждое ограничение включено или выключена и проверяется или не проверяется. Любая комбинация этих двух состояний возможна
- ENABLEVALIDATE – Невозможно добавить строку которая нарушает ограничение и все строки таблицы удовлетворяют ограничениям
- DISABLENOVALIDATE – Любые данные возможно ввести и в таблицу уже могут быть данные нарушающие ограничения
- ENABLENOVALIDATE – В таблице могут существовать данные не удовлетворяющие ограничениям, но все добавляемые данные должны удовлетворять ограничениям
- DISABLEVALIDATE – невозможная ситуация: все данные в таблице удовлетворяют ограничениям, но новые данные должны неудовлетворять. Т.е. нельзя вставить данные в таблицу.
Идеальной ситуацией (и состоянием по умолчанию при определении ограничения) является ENABLE VALIDATE. Это гарантирует что данные корректны и некорректные данные не могут быть добавлены.
Статус DISABLE NOVALIDATE полезен когда загружается много данных в таблицу одним запросом. Данные могут содержать плохие значения, но вместо ошибки выполнения запроса этот статус позволит загрузить данные. Сразу после загрузки данных, переведите состояние в ENABLE NOVALIDATE чтобы предотвратить вставку других неправильных данных пока вы будете проверять уже добавленные данные на удовлетворение ограничениям. И затем опять установите правильное состояние ENABLE VALIDATE.
Примером такой задачи может быть скрипт, читающий данные из активных данных в архив. Преположим что есть ограничение NOT-NULL на столбце архивной таблицы, которого нет у столбца активной
alter table sales_archive modify constraint sa_nn1 disable novalidate;
insert into sales_archive select * from sales_current;
alter table sales_archive modify constraint sa_nn1 enable novalidate;
update sales_archive set channel=’NOT KNOWN’ where channel is null;
alter table sales_archive modify constraint sa_nn1 enable validate;
Проверка ограничений
set constraint sa_nn1 deferred;
insert into sales_archive select * from sales_current;
update sales_archive set channel=’NOT KNOWN’ where channel is null;
set constraint sa_nn1 immediate;
Для поддержки отложенного (DEFERRED) ограничения оно должно быть создано используя синтаксис
alter table sales_archive add constraint sa_nn1
check (channel is not null) deferrable initially immediate;
То есть невозможно сделать ограничение отложенным если директива deferrable не была указана при создании. В нашем случае ограничение SA_NN1 по умолчанию проверяется на момент вставки строки (или изменения), но проверку можно отложить до подтверждения транзакции. Часто отложенные ограничения используются для внешних ключей. Если операции обновления и изменения затрагивают и дочернюю и родительскую таблицу, и если внешний ключ не отложенный то операция может выполниться с ошибкой в зависимости от порядка обработки строк.
Изменение состояния ограничения затрагивает все сессии. Изменение времени проверки ограничения (IMMEDIATE или DEFERRED) затрагивает текущую сессию, но начальное состояние применяется ко всем сессиям.
By default, constraints are enabled and validated, and they are not deferrable.
Ограничения SQL используются для определения правил данных в таблице. Если есть поведение данных, которое нарушает ограничение, поведение будет прекращено ограничением. Ограничения можно указать при создании таблицы (с помощью оператора create table) или после создания таблицы (с помощью оператора alter table).
создать таблицу + синтаксис ограничения
- not null - указывает, что столбец не может хранить значения NULL.
- unique-гарантия того, что каждая строка столбца должна иметь уникальное значение.
- первичный ключ - комбинация не нулевого и уникального. Убедитесь, что определенный столбец (или комбинация двух столбцов и нескольких столбцов) имеет уникальный идентификатор, который помогает легче и быстрее найти конкретную запись в таблице.
- внешний ключ - ссылочная целостность, которая гарантирует, что данные в одной таблице соответствуют значению в другой таблице.
- check-Убедитесь, что значения в столбце соответствуют указанным условиям.
- default-указывает значение по умолчанию, если столбцу не присвоено значение
Далее мы по очереди поделимся с вами каждым ограничением.
Ограничение sql not null
По умолчанию столбцы таблицы принимают значения NULL. Ограничение not null заставляет столбец не принимать значения NULL. Ограничение not null заставляет поле всегда содержать значение. Это означает, что если вы не добавите значение в поле, вы не сможете вставлять новые записи или обновлять записи.
Добавить ненулевое ограничение
Удалите ненулевое ограничение
Примечание: при добавлении и удалении ненулевых ограничений я всегда сообщаю об ошибках с помощью команд change и modify. Это связано с тем, что я использую базу данных сервера sql, а команды изменения и изменения представляют собой грамматику, которую можно использовать только в моих sql и oracle. .
Ограничение уникальности sql
Ограничение уникальности однозначно идентифицирует каждую запись в таблице базы данных. Ограничения как уникального, так и первичного ключа обеспечивают гарантию уникальности столбца или коллекции столбцов. Ограничение первичного ключа имеет автоматически определяемое ограничение уникальности. Обратите внимание, что каждая таблица может иметь несколько уникальных ограничений, но каждая таблица может иметь только одно ограничение первичного ключа.
уникальное ограничение при создании таблицы
MySQL：
SQL Server / Oracle / MS Access：
Чтобы назвать ограничение уникальности и определить ограничения уникальности для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access：
уникальное ограничение на изменение
MySQL / SQL Server / Oracle / MS Access：
Чтобы назвать уникальные ограничения и определить уникальные ограничения для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access：
Отменить уникальное ограничение
Чтобы отменить уникальное ограничение, используйте следующий SQL:
MySQL：
SQL Server / Oracle / MS Access：
ограничение первичного ключа
Ограничение первичного ключа однозначно идентифицирует каждую запись в таблице базы данных. Первичный ключ должен содержать уникальное значение. Столбец первичного ключа не может содержать значения NULL. Каждая таблица должна иметь первичный ключ, а каждая таблица может иметь только один первичный ключ.
Ограничения первичного ключа при создании таблицы
MySQL：
SQL Server / Oracle / MS Access：
Чтобы назвать ограничение первичного ключа и определить ограничение первичного ключа для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access：
Примечание. В приведенном выше примере есть только один первичный ключ (pk_PersonID). Однако значение pk_PersonID состоит из двух столбцов (PersonID и Name).
Ограничения первичного ключа при изменении таблицы
MySQL / SQL Server / Oracle / MS Access：
Чтобы назвать ограничение первичного ключа и определить ограничение первичного ключа для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access：
Примечание. Если вы используете оператор alter table для добавления первичного ключа, вы должны объявить столбец первичного ключа как не содержащий значений NULL (при первом создании таблицы).
Отменить ограничение первичного ключа
Чтобы отменить ограничение первичного ключа, используйте следующий SQL:
MySQL：
SQL Server / Oracle / MS Access：
ограничения внешнего ключа
Внешний ключ в одной таблице указывает на уникальный ключ в другой таблице.
Давайте объясним внешний ключ на примере. См. Следующие две таблицы:
Таблица лиц:
| PersonId | Name | Address | City |
|---|---|---|---|
| 1 | Сяо Мин | Timoteivn | Sandnes |
| 2 | Сяо Хун | Timoteivn | Sandnes |
| 3 | Сяоцзюнь | Timoteivn | Stavanger |
| OrderId | OrderNo | PersonId |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
Ограничения внешнего ключа при создании таблицы
MySQL：
SQL Server / Oracle / MS Access：
Чтобы назвать ограничение внешнего ключа и определить ограничение внешнего ключа для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access：
Ограничения внешнего ключа при изменении таблицы
MySQL / SQL Server / Oracle / MS Access：
Чтобы назвать ограничение внешнего ключа и определить ограничение внешнего ключа для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access：
Отменить ограничение внешнего ключа
Чтобы отменить ограничение внешнего ключа, используйте следующий SQL:
MySQL：
SQL Server / Oracle / MS Access：
проверить ограничение
Ограничение проверки используется для ограничения диапазона значений в столбце. Если вы определяете ограничение проверки для одного столбца, для этого столбца разрешены только определенные значения. Если вы определяете ограничение проверки для таблицы, то это ограничение будет ограничивать значение в конкретном столбце на основе значений других столбцов в строке.
Проверить ограничения при создании таблицы
MySQL：
SQL Server / Oracle / MS Access：
Чтобы назвать проверочные ограничения и определить проверочные ограничения для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access：
Проверять ограничения при изменении таблицы
MySQL / SQL Server / Oracle / MS Access:
Чтобы назвать проверочные ограничения и определить проверочные ограничения для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access：
Отменить ограничение проверки
Чтобы отменить ограничение проверки, используйте следующий SQL:
SQL Server / Oracle / MS Access：
MySQL：
Ограничение по умолчанию sql
Ограничение по умолчанию используется для вставки значений по умолчанию в столбец. Если другое значение не указано, значение по умолчанию будет добавлено ко всем новым записям.
Ограничение по умолчанию при создании таблицы
My SQL / SQL Server / Oracle / MS Access：
Используя такие функции, как getdata (), ограничение по умолчанию также можно использовать для вставки системных значений:
Ограничение по умолчанию при изменении таблицы
Часть 12.6: Ограничения выборки данных: LIMIT и SELECT в SQLite
Ограничения выборки данных: LIMIT и SELECT в SQLite
Как сделать ограничение строк выборки данных из базах данных SQLite: OFFSET, LIMIT и SELECT
Ограничение выборки из базы данных определяется уточняющей фразой LIMIT. LIMIT используется вместе с командой SELECT, но может быть использована и в других SQL запросах. Когда вы составляете SQL запрос SELECT, вы можете ограничить количество строк, которое будет отображено в результирующей таблице при помощи клаузулы LIMIT. Предикат LIMIT указывается после других уточняющих фраз, либо если вы используете самый простой вариант выборки данных из базы данных, то LIMIT идет сразу же после указания имени таблицы.
Ограничение выборки данных в базах данных SQLite реализуется очень просто: сперва вы указываете, что и откуда хотите получить, а затем используете ключевое слово LIMIT, для которого указываете целое число. Это целое число является ограничением количества строк, которые покажет SQLite в результирующей таблице. Обратите внимание: SQLite3 позволяет в качестве ограничения использовать сложные выражения, но с тем условием, что результат выражение – это целое число, либо значение, которое можно преобразовать в целое число.
Когда вы хотите ограничить количество строк при помощи предиката LIMIT, вы можете использовать еще и ключевое слово OFFSET. Выборку данных в базах данных SQLite можно не только ограничить, но и отбросить некоторое количество строк, перед тем, как ограничить. OFFSET, как и LIMIT в качестве значения принимает только целые числа.
LIMIT – очень простая уточняющая фраза, поэтому не имеет смысла ее долго и нудно описывать, давайте лучше посмотрим на примеры ограничения выборки данных из базы данных SQLite3. Скомбинируем SELECT, LIMIT и OFFSET, чтобы понять, как это всё работает.
Примеры ограничения выборки данных в базах данных SQLite3: SELECT, LIMIT и OFFSET
Как и всегда, не будем придумывать ничего сложно и на простых и понятных примерах разберемся с тем, как работают SELECT, LIMIT и OFFSET. Попробуем ограничить выборку данных в базе данных под управлением SQLite3. И как всегда, мы начинаем с создания таблицы, которое реализуем командой CREATE:
Для решения многих стандартных задач не требуется быть SQL-виртуозом, достаточно изучить азы работы с базами:
- создание и редактирование таблиц;
- сохранение и обновление записей;
- выборка и фильтрация данных;
- индексирование полей.
Этими азами мы и займемся: разберем синтаксис SQL-запросов в теории и на реальных примерах. К счастью, язык баз данных очень похож на простые английские предложения, так что вы легко с ним справитесь.
Чтобы учиться эффективнее, сразу же закрепляйте новые знания практикой. Поиграть с SQL можно на этом замечательном ресурсе. В левой панели вы должны ввести весь код, относящийся к структуре базы данных. После этого начинайте экспериментировать с SELECT’ами в правом поле.
* В примерах используется SQL-синтаксис для MySQL 5.6. Запросы, предназначенные для разных СУБД, могут различаться.
Терминология
База данных состоит из таблиц, а таблица – из колонок и строк.
Каждая колонка, или поле таблицы, представляет собой конкретный вид информации, например, имя студента (строка) или зарплата сотрудника (число).
Каждая строка, или запись таблицы, – это описание конкретного объекта, например, студента или сотрудника.
Уровень: Новичок
Создание и редактирование таблиц
CREATE
Несложно догадаться, что оператор CREATE создает новую таблицу в базе. Ему нужно передать описания всех полей таблицы в формате:
Создадим таблицу с данными о собаках и их рационе питания:
ALTER
Не всегда получается создать идеальную таблицу с первого раза. Не бойтесь вносить изменения, добавлять, удалять или изменять существующие поля:
DROP и TRUNCATE
Оператор DROP удаляет таблицу из базы целиком:
Если вам нужно удалить только записи, сохранив саму таблицу, воспользуйтесь оператором TRUNCATE:
Атрибуты и ограничения
Можно ограничить диапазон данных, которые попадают в поле, например, запретить устанавливать в качестве возраста или веса отрицательные числа.
Самые распространенные в SQL ограничения целостности (CONSTRAINTS):
- DEFAULT – устанавливает значение по умолчанию;
- AUTO_INCREMENT – автоматически инкрементирует значение поля для каждой следующей записи;
- NOT NULL – запрещает создавать запись с пустым значением поля;
- UNIQUE – следит, чтобы поле или комбинация полей оставались уникальны в пределах таблицы;
- PRIMARY KEY – UNIQUE + NOT NULL. Первичный ключ должен однозначно идентифицировать запись таблицы, поэтому он должен быть уникальным и не может оставаться пустым;
- CHECK – проверяет значение поля на соответствие некоторому условию.
Ограничения можно добавлять при создании таблицы, а затем при необходимости добавлять/изменять/удалять. Они могут действовать на одно поле или комбинацию полей.
Первичный ключ, автоматический инкремент, NOT NULL и значение по умолчанию мы уже использовали в примере с собаками.
Решим новую задачу – составление списка президентов:
Ограничение уникальности не позволит занести в таблицу одного и того же президента одной страны дважды. Кроме того, не попадут в список и слишком молодые политики.
Для добавления и удаления ограничений к существующим таблицам используйте оператор ALTER. Ограничениям можно давать имя, чтобы ссылаться на них впоследствии. Для этого предназначена конструкция CONSTRAINT.
Еще одно удобное ограничение в SQL – внешний ключ (FOREIGN KEY). Он позволяет связать поля двух разных таблиц.
Для примера возьмем базу данных организации с таблицами сотрудников и отделов:
Теперь в поле department таблицы employees нельзя будет указать произвольный отдел. Он обязательно должен содержаться в таблице departments.
Сохранение и обновление записей
INSERT
Добавить в таблицу новую запись (или даже сразу несколько) очень просто:
Вы даже можете скопировать записи из одной таблицы и вставить их в другую одним запросом. Для этого нужно скомбинировать операторы INSERT и SELECT:
UPDATE
Оператор UPDATE используется для изменения существующих записей таблицы.
Вот так легким движением руки мы обнулили зарплату сразу у всех сотрудников.
Запрос можно уточнить, добавив секцию WHERE с условием отбора записей.
С условиями мы подробно разберемся чуть позже, когда будем говорить о выборке данных из базы.
DELETE
Можно удалить из таблицы все записи сразу или только те, которые соответствуют некоторому условию:
Уровень: уверенный пользователь
Выборка и фильтрация данных
Для получения данных из базы служит оператор SELECT. В SQL есть множество способов отфильтровать именно те данные, которые вам нужны, а также отсортировать их и разбить по группам.
Вот небольшая демо-база, на которой вы можете попрактиковаться:
SELECT
Можно переименовывать поля для вывода:
Ограничение количества результатов:
Агрегатные функции и группировка
SQL позволяет привести несколько записей таблицы к некоторому единому значению:
Агрегатные функции могут работать со всеми записями таблицы разом, а могут и с отдельными группами. Чтобы эти группы сформировать, используйте оператор GROUP BY:
Полученные группы тоже можно отфильтровывать: для этого предназначена конструкция HAVING. Например, не будем учитывать в выборке отделы, в которых работает меньше трех человек:
Объединение таблиц
Очень часто нужная вам информация хранится в разных таблицах – это обусловлено законами нормализации. Поэтому важно уметь объединять их.
В запросе, захватывающем несколько таблиц, нужно указать следующее:
- все интересующие вас поля, которые могут принадлежать разным таблицам;
- тип соединения;
- правило, по которому поля одной таблицы будут поставлены в соответствие полям другой таблицы.
Соединение бывает внутреннее (INNER) и внешнее (OUTER).
Внутреннее соединение
При внутреннем соединении вы получите в результате только те записи, для которых нашлось соответствие во всех таблицах.
SQL просмотрит каждую запись из таблицы employees и попытается поставить ей в соответствие каждую запись из таблицы departments. Если это удастся (id отделов совпадают), запись будет включена в результат, иначе – не будет.
Таким образом, вы не увидите Kenny Washington, у которого отдел не указан, а также все отделы, в которых нет сотрудников.
Если не указано условие для соединения таблиц, SQL создаст все возможные комбинации сотрудников и отделов.
Внешнее соединение
При внешнем соединении в результат попадают также записи без соответствий. При этом вы можете регулировать, из какой таблицы такие записи берутся, а из какой – нет.
Например, чтобы увидеть в результате Kenny Washington, потребуется левое внешнее соединение. Слово OUTER можно не указывать – соединение по умолчанию внешнее:
Теперь в результате есть все данные из левой таблицы (employees), даже если для них нет соответствия.
Правое соединение соответственно проигнорирует Кенни, но выведет все пустые отделы:
И наконец, полное внешнее соединение выведет и соответствия, и пустые отделы, и сотрудников без отдела.
Декартово произведение
Оператор CROSS JOIN позволяет получить все возможные комбинации записей из двух таблиц:
Автосоединение
Кроме того, таблицу можно соединять с самой собой. Это пригодится, чтобы найти босса для каждого сотрудника. Сейчас в поле boss находится идентификатор другого сотрудника, необходимо вывести его имя:
Благодаря использованию левого соединения мы можем вывести также сотрудников, не имеющих руководителей.
Объединение выборок
SQL позволяет сделать две отдельные выборки, а затем объединить их результаты по определенному правилу:
UNION
Объединить штатных и внештатных сотрудников
INTERSECT
Найти всех сотрудников, которые участвуют в сборной предприятия по спортивной ходьбе
MINUS
Найти всех сотрудников, которые не участвуют в сборной предприятия по спортивной ходьбе и заставить участвовать :
Уровень: SQL-мастер
Представления
Views, или представления, в SQL – это SELECT-запрос, который вы можете сохранить для дальнейшего использования. Один раз написали, а потом можете пользоваться полученной таблицей, которая – внимание! – всегда остается актуальной в отличие от результата обычных запросов.
У представлений есть еще одна важная миссия: обеспечение безопасности. Под view вы легко можете скрыть бизнес-логику и архитектуру базы и защитить свое приложение от нежелательных вторжений.
Представление может извлекать данные из одной или нескольких таблиц. Кроме того, при соблюдении ряда условий представление может быть изменяемым, то есть совершая операции над ним, можно изменять базовые таблицы.
Читайте также: