Проверить базу sql на ошибки

Статья: Исправление ошибок DBCC CHECKDB (1С, SQL) вручную

Все началось с того, что после проблем с жестким диском на сервере и «не совсем удачным» восстановлением рабочей базы данных 1С начала сообщать «could not continue scan with nolock» при проведении документов и закрываться с непоправимой ошибкой. Бэкап был, но не самый свежий, а данные терять не хотелось. Что же лучше делать?

Такое сообщение говорит, как правило, о том, что данные базы разрушены.

Первым делом нужно сделать резервную копию.

Далее запускаем в SQL Management Studio и выполняем DBCC CHECKDB. Выполняем ее так, чтобы данные не терялись, параметр REPAIR_ALLOW_DATA_LOSS оставим на случай совсем безнадежный.

Например, наша база называется Office

Выполняем следующие запросы:

ALTER DATABASE Office
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB (N’Office’, REPAIR_REBUILD) WITH NO_INFOMSGS
GO

Смотрим что сообщила проверка и видим множество сообщений примерно такого содержания:

CHECKDB found 0 allocation errors and 8 consistency errors in table ‘DT3311’ (object ID 1970106059).
CHECKDB found 0 allocation errors and 43 consistency errors in database ‘Office’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Office, repair_rebuild).

После проверки выполняем запрос для дальнейших операций с базой данных:

ALTER DATABASE Office
SET MULTI_USER;

Как восстанавливать поврежденные страницы писать не буду. Статья рассчитана на простое администрирование и поможет даже при модели Simple. Те, кто делают бэкапы логов журнала транзакций очень-очень часто, сюда заходить не будут. Единственное условие — нам потребуется резервная копия (будем надеяться, что по теории вероятности самые свежие данные мы спасли без повреждений и бэкапы хоть иногда делали).

Как видим, все ошибки относятся к index или index Это говорит о том, что повреждены данные. Но не будем отчаиваться и воспользуемся резервной копией.

Обращаем внимание на сообщенную таблицу DT3311. Пытаемся открыть ее или прочитать данные запросом, возникает сообщение об ошибке:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xacafd5b7; actual: 0x21c9cf6a). It occurred during a read of page (1:8473) in database ID 8 at offset 0x00000004232000 in file ‘E:SQL_DataOffice. mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Обращаем внимание, на какой строке таблицы останавливается запрос при полном показе содержимого таблицы в графическом интерфейсе. Например, он показал нам данные до строки 1915.

Проверяем: запрос Select top 1915 * From DT3311 выполняется, а Select top 1916 * From DT3311 — уже нет.

Смотрим резервную базу, поле IDDOC в таблице начиная со строки 1916, это документ с ID ‘ 1SP ‘

В рабочей базе мы ничего с документом не можем сделать: ни открыть его в 1С, ни прочитать его табличную часть, ни удалить его. Что делать?

Предлагаю перебросить по кусочкам данные из разных баз (рабочей и резервной), удалить таблицу в рабочей базе, создать ее повторно и положить данные на место.

Создаем временную базу test, в ней создаем такую же таблицу DT3311 (для тех, кто не знает как это сделать быстро — картинки ниже на примере создания индексов) и выполняем запрос:

Insert into Test. dbo. DT3311
Select * From DT3311 Where IDDOC <> ‘ 1SP ‘

Запрос выполнился без ошибок. Это говорит о том, что других данных с «мусором» в этой таблице нет.

Данные о поврежденном документе берем из резервной копии. Выполняем запрос в резервной базе:

Insert into Test. dbo. DT3311
Select * From DT3311 Where IDDOC = ‘ 1SP ‘

Теперь в тестовой базе есть полные данные. Удаляем таблицу в рабочей базе, создаем заново и выполняем запрос:

Insert into DT3311
Select * From Test. dbo. DT3311

Пробуем прочитать полностью другие таблицы, ведь мы помним, что не проводились документы. Выясняется, что не могут прочитаться некоторые таблицы итогов регистров (RGXXX). В данном случае можно просто удалить эти таблицы, данные в них восстановит сама 1С. Заходим монопольно в 1С: Предприятие, сдвигаем ТА на самый первый документ, затем на самый последний проведенный документ. В результате итоги по регистрам пересчитаются.

Производим повторную проверку ошибок и убеждаемся в их отсутствии.

Беремся за другую базу, Acc.

Выполняем следующие запросы:

ALTER DATABASE Acc
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB (N’Acc’, REPAIR_REBUILD) WITH NO_INFOMSGS
GO

Для нее мы получили такой перечень ошибок:

Тут картина совсем нестрашная. Данные не повреждены. Ошибки можно исправить удалением и созданием некластерных индексов.

Не забываем вернуть доступ к базе данных:

ALTER DATABASE Acc
SET MULTI_USER;

Для начала запишем скрипты на создание индексов (поочередно):

Затем удаляем индексы:

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

Все исправили, производим повторную проверку и убеждаемся в отсутствии ошибок.

Проверка базы данных 1C на целостность и исправление ошибок MS SQL

Делимся опытом, как исправить ошибки в логической целостности в базе 1С, размещенной на Microsoft SQL Server.

Поступила жалоба от бухгалтера о проблемах с проведением документов в 1С.

Из скриншота выяснилось, что 1С «ругается» на проблемы с согласованностью «внутри» базы данных и предлагает провести проверку на согласованность.

Переходим в SQL Server Management Studio и, сделав, на всякий случай, бэкап текущего состояния, выполняем проверку:

Для начала переводим нужную нам БД в однопользовательский режим

Запускаем Окно запросов (CTRL+N). Выбираем Новый запрос и вводим запрос Transact-SQL (T-SQL) в этом окне:

Далее, вводим запрос на сканирование базы данных:

Проверка продлилась около 15 минут, после чего выдала следующее:

Вариант решения №1: восстановление из бэкапа выявило накопительный характер ошибки: чем раньше сделан бэкап – тем меньше в базе ошибок, вплоть до самого «дальнего» (14 дней). Примерно на третьем бэкапе количество ошибок перестало уменьшаться – стало ясно, что этим путём мы придём только к потере актуальности базы и проблему не решить

Вариант решения №2: В справочной информации описаны три возможных варианта исправления этих ошибок, рассмотрим каждый:

REPAIR_FAST

Синтаксис поддерживается только для обеспечения обратной совместимости. Действия по восстановлению не выполняются.

REPAIR_REBUILD

Выполняет действия по восстановлению данных, которые можно выполнить без риска их потери. Это может быть быстрое восстановление (например, восстановление отсутствующих строк в некластеризованных индексах) или более ресурсоемкие операции (например, перестроение индекса).

REPAIR_ALLOW_DATA_LOSS

Пытается устранить все обнаруженные ошибки. Эти исправления могут привести к частичной потере данных.

Аргумент REPAIR_FAST нам не подходит, REPAIR_ALLOW_DATA_LOSS оставим на крайний случай — пробуем REPAIR_REBUILD:

Не помогло, переводим базу данных обратно в многопользовательский режим:

На всякий случай, я попробовал провести обслуживание базы данных и перепроверил – результат тот же.

Решил провести тестирование и исправление информационной базы средствами 1С, на что получил ошибку

Выгрузить базу данных в *.dt файл тоже не удалось:

Что ж, стало понятно, что часть потерянных данных – меньшее зло, по сравнению с «развалившейся» базой данных, пробуем REPAIR_ALLOW_DATA_LOSS:

И, наконец, после нескольких прогонов, количество ошибок немного уменьшилось:

Ситуацию это не спасло: база, по-прежнему не выгружалась и не «лечилась» средствами 1С.

Дальнейшие попытки (по очереди несколько раз запускал REPAIR_REBUILD и REPAIR_ALLOW_DATA_LOSS) не увенчались успехом: количество ошибок не уменьшилось, база, по-прежнему, не выгружалась и не «лечилась».

Коллеги подсказали попробовать очистить (именно очистить, без удаления самой таблицы) «проблемную» таблицу в MS SQL.

Больше всего ошибок в таблице «_AccRg1051» – ей и было принято решение заняться:

И, после успешного выполнения, прогоняем проверку еще раз:

15 минут ожидания и, о чудо – все ошибки исчезли, в том числе и в остальных таблицах.

Перевожу базу в многопользовательский режим, выгружаю в *.dt файл и загружаю обратно.

Звоню бухгалтеру – прошу проверить проблемные документы: всё работает нормально. Пускаю остальных пользователей в базу.

Через час снова ошибка:

Делаем вывод, что выгрузка в *.dt – не панацея. Выгоняем Вежливо просим пользователей выйти и ещё немного потерпеть и тестируем базу с исправлением ошибок в режиме конфигуратора 1С со следующими параметрами

Пускаем обратно пользователей в 1С и идём молиться настраивать планы обслуживания баз данных.

Статья: Исправление ошибок DBCC CHECKDB (1С, SQL) вручную

Все началось с того, что после проблем с жестким диском на сервере и «не совсем удачным» восстановлением рабочей базы данных 1С начала сообщать «could not continue scan with nolock» при проведении документов и закрываться с непоправимой ошибкой. Бэкап был, но не самый свежий, а данные терять не хотелось. Что же лучше делать?

Такое сообщение говорит, как правило, о том, что данные базы разрушены.

Первым делом нужно сделать резервную копию.

Далее запускаем в SQL Management Studio и выполняем DBCC CHECKDB. Выполняем ее так, чтобы данные не терялись, параметр REPAIR_ALLOW_DATA_LOSS оставим на случай совсем безнадежный.

Например, наша база называется Office

Выполняем следующие запросы:

ALTER DATABASE Office
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB (N’Office’, REPAIR_REBUILD) WITH NO_INFOMSGS
GO

Смотрим что сообщила проверка и видим множество сообщений примерно такого содержания:

CHECKDB found 0 allocation errors and 8 consistency errors in table ‘DT3311’ (object ID 1970106059).
CHECKDB found 0 allocation errors and 43 consistency errors in database ‘Office’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Office, repair_rebuild).

После проверки выполняем запрос для дальнейших операций с базой данных:

ALTER DATABASE Office
SET MULTI_USER;

Как восстанавливать поврежденные страницы писать не буду. Статья рассчитана на простое администрирование и поможет даже при модели Simple. Те, кто делают бэкапы логов журнала транзакций очень-очень часто, сюда заходить не будут. Единственное условие — нам потребуется резервная копия (будем надеяться, что по теории вероятности самые свежие данные мы спасли без повреждений и бэкапы хоть иногда делали).

Как видим, все ошибки относятся к index или index Это говорит о том, что повреждены данные. Но не будем отчаиваться и воспользуемся резервной копией.

Обращаем внимание на сообщенную таблицу DT3311. Пытаемся открыть ее или прочитать данные запросом, возникает сообщение об ошибке:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xacafd5b7; actual: 0x21c9cf6a). It occurred during a read of page (1:8473) in database ID 8 at offset 0x00000004232000 in file ‘E:SQL_DataOffice. mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Обращаем внимание, на какой строке таблицы останавливается запрос при полном показе содержимого таблицы в графическом интерфейсе. Например, он показал нам данные до строки 1915.

Проверяем: запрос Select top 1915 * From DT3311 выполняется, а Select top 1916 * From DT3311 — уже нет.

Смотрим резервную базу, поле IDDOC в таблице начиная со строки 1916, это документ с ID ‘ 1SP ‘

В рабочей базе мы ничего с документом не можем сделать: ни открыть его в 1С, ни прочитать его табличную часть, ни удалить его. Что делать?

Предлагаю перебросить по кусочкам данные из разных баз (рабочей и резервной), удалить таблицу в рабочей базе, создать ее повторно и положить данные на место.

Создаем временную базу test, в ней создаем такую же таблицу DT3311 (для тех, кто не знает как это сделать быстро — картинки ниже на примере создания индексов) и выполняем запрос:

Insert into Test. dbo. DT3311
Select * From DT3311 Where IDDOC <> ‘ 1SP ‘

Запрос выполнился без ошибок. Это говорит о том, что других данных с «мусором» в этой таблице нет.

Данные о поврежденном документе берем из резервной копии. Выполняем запрос в резервной базе:

Insert into Test. dbo. DT3311
Select * From DT3311 Where IDDOC = ‘ 1SP ‘

Теперь в тестовой базе есть полные данные. Удаляем таблицу в рабочей базе, создаем заново и выполняем запрос:

Insert into DT3311
Select * From Test. dbo. DT3311

Пробуем прочитать полностью другие таблицы, ведь мы помним, что не проводились документы. Выясняется, что не могут прочитаться некоторые таблицы итогов регистров (RGXXX). В данном случае можно просто удалить эти таблицы, данные в них восстановит сама 1С. Заходим монопольно в 1С: Предприятие, сдвигаем ТА на самый первый документ, затем на самый последний проведенный документ. В результате итоги по регистрам пересчитаются.

Производим повторную проверку ошибок и убеждаемся в их отсутствии.

Беремся за другую базу, Acc.

Выполняем следующие запросы:

ALTER DATABASE Acc
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB (N’Acc’, REPAIR_REBUILD) WITH NO_INFOMSGS
GO

Для нее мы получили такой перечень ошибок:

Тут картина совсем нестрашная. Данные не повреждены. Ошибки можно исправить удалением и созданием некластерных индексов.

Не забываем вернуть доступ к базе данных:

ALTER DATABASE Acc
SET MULTI_USER;

Для начала запишем скрипты на создание индексов (поочередно):

Затем удаляем индексы:

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

Все исправили, производим повторную проверку и убеждаемся в отсутствии ошибок.

Чтобы произвести переиндексацию необходимо выполнить такой запрос (например в QA):
USE base_1c
EXEC _1sp_DBReindex где base_1c — имя базы данных.
Если хранимой процедуры _1sp_DBReindex нет в базе данных (база не 1С), то можно выполнить такой код (это и есть код хранимой процедуры _1sp_DBReindex):
USE base_1c
DECLARE @TableName char(32)
DECLARE SysCur CURSOR FOR SELECT name FROM sysobjects WHERE type=’U’
OPEN SysCur
FETCH NEXT FROM SysCur INTO @TableName
WHILE @@FETCH_STATUS=0 BEGIN
DBCC DBREINDEX(@TableName)
FETCH NEXT FROM SysCur INTO @TableName
END
CLOSE SysCur
DEALLOCATE SysCur где base_1c — имя базы данных.
Соответсвенно код для создания хранимой процедуры:
if exists (select * from sysobjects where and sysstat & 0xf = 4)
drop procedure dbo._1sp_DBReindex
CREATE PROCEDURE _1sp_DBReindex AS
SET NOCOUNT ON
DECLARE @TableName char(32)
DECLARE SysCur CURSOR FOR SELECT name FROM sysobjects WHERE type=’U’
OPEN SysCur
FETCH NEXT FROM SysCur INTO @TableName
WHILE @@FETCH_STATUS=0 BEGIN
DBCC DBREINDEX(@TableName)
FETCH NEXT FROM SysCur INTO @TableName
END
CLOSE SysCur
DEALLOCATE SysCur Как проверить (восстановить) базу на MS SQL Server средствами сервера

Проверку логической целостности нужно выполнять штатными средствами 1С:Предприятия (Тестирование и исправление ИБ). В случае, если такую проверку не удается выполнить, следует проверить физическую целостность БД средствами MS SQL. Для проверки целостности средствами MS SQL нужно выполнить следующую команду:

DBCC CHECKDB («имя базы», REPAIR_REBUILD)
Перед выполнением этой команды нужно базу данных перевести в режим «single user»:
sp_dboption «имя базы», single user’, true В процессе работы DBCC CHECKDB могут быть обнаружены ошибки и часть может быть сразу же исправлена. Если ошибки остались, то по всей видимости их нельзя восстановить без потери некоторых данных. В этом случае нужно запустить DBCC CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS (перед запуском желательно сделать копию файлов базы данных).
DBCC CHECKDB («имя базы», REPAIR_ALLOW_DATA_LOSS) После выполнения DBCC CHECKDB нужно не забыть вернуться в нормальный режим (выйти из режима «single user»):
sp_dboption «имя базы»,’single user’,false
Переиндексацию базы данных на MS SQL не нужно делать так часто, как в случае с DBF-версией 1С:Предприятия (например, при аварийном завершении работы пользователя). MS SQL автоматически поддерживает индексы в актуальном состоянии. Пересоздавать индексы имеет смысл в одном из следующих случаев:
1) Индекс физически поврежден. Это случается довольно редко и для восстановления нужно использовать вышеупомянутый DBCC CHECKDB.
2) Страницы индекса сильно фрагментированы и требуется их упорядочить.
3) Нужно изменить степень заполнения индексных страниц (fill factor).
4) Требуется изменить тип индекса (кластерный/некластерный). При использовании 1С это обычно неактуально

alexvyrvich

Alex Vyrvich

Рассмотрим несколько вариантов восстановления БД, в зависимости от того, насколько повреждены файлы БД зависит успешность того или иного метода. Все описанные способы были лично мной проверены на практике и все случаи восстановления, за исключением одного, были успешны. Используйте данное руководство на свой страх и риск, за совершенные вами действия ответственность несете, вы сами.

Итак, во-первых останавливаем службу SQL Server и копируем файлы базы данных (*.mdf и *.ldf) в другую папку, чтобы можно было восстановить их в случае неудачи.

Если у вас есть свежий актуальный бэкап, то дальше можете не читать, а просто восстановите БД из него, тем самым сэкономите драгоценное время, далее я приведу алгоритмы восстановления для разных версий SQL Server. Надеюсь вам это поможет, как в свое время помогло и мне.

Для всех версий SQL Server подойдет следующий вариант: делаем Detach database (отсоединить базу данных), удаляем журнал транзакций (файл с расширением ldf) и делаем Attach database(присоединить базу данных). В мастере выбираем наш mdf файл и жмем ОК.

Если mdf файл не поврежден, то он успешно присоединится и мы увидим нашу базу в диспетчере объектов целую и невредимую.

Радуемся успешному восстановлению. (Этот вариант сработает только если mdf файл не поврежден, поэтому срабатывает не всегда). Если не получилось, то создаем новую базу данных с таким же именем, останавливаем сервер. Подменяем файл mdf файлом от нашей базы, стартуем службу SQL Server и открываем Query analyzer(SQL 2000) или Management studio(SQL 2005/2008) в зависимости от нашей версии сервера.

USE master
GO
sp_configure ‘allow updates’, 1
reconfigure WITH override
GO

Если у вас SQL 2000, то далее пишем:

UPDATE sysdatabases SET STATUS= 32768 WHERE name = ‘db_name’
GO

Если SQL 2005 или 2008, то пишем:

ALTER DATABASE db_name SET EMERGENCY, SINGLE_USER
GO

где вместо db_name пишем имя своей БД

Жмем F5. После этого наша БД должна быть видна в статусе EMERGENCY.

В особо тяжелых случаях возникают проблемы с переходом в EMERGENCY, возможны даже проблемы с detach, в таких случаях поврежденная база удаляется, а далее происходит хитрая подмена файлов данных. Для начала создадим новую базу, имена файлов mdf и ldf должны совпадать с именами файлов поврежденной базы. Новую базу переводим в режим EMERGENCY, останавливаем службу MSSQL и подменяем файлы поврежденными. Таким образом мы получим рабочий инстанс в статусе EMERGENCY с поврежденными файлами.

Отлично, приступаем к восстановлению.

Выполним следующие SQL команды.

DBCC REBUILD_LOG(‘db_name’, ‘Полный путь к новому файлу ldf’)
GO

Жмем F5, если все нормально, сервер скажет: Warning: The log for database ‘db_name’ has been rebuilt.

USE master
GO
sp_dboption ‘db_name’, ‘single user’, ‘true’
GO
USE db_name
GO
DBCC CHECKDB(‘db_name’, REPAIR_REBUILD)
GO

Если база не хочет в single mode можно попробовать такую команду

USE db_name
ALTER database db_name set SINGLE_USER with rollback immediate
GO

если DBCC не хочет выполняться, то вместо REPAIR_REBUILD нужно подставить REPAIR_ALLOW_DATA_LOSS

Жмем F5, ждем некоторое время. Сервер вернет кучу сообщений. Если там будут содержаться ошибки, то лучше еще раз выполнить DBCC CHECKDB с параметром REPAIR_REBUILD, пока все ошибки не будут устранены.

Для SQL 2005/2008 действия несколько иные:

DBCC CHECKDB(‘db_name’, REPAIR_ALLOW_DATA_LOSS)
GO

Тут без вариантов. В SQL 2005 и выше нет инструкции REBUILD_LOG, вместо этого выполняется CHECKDB с параметром REPAIR_ALLOW_DATA_LOSS.

После того как сервер закончит выполнять запрос и вернет результат, меняем REPAIR_ALLOW_DATA_LOSS на REPAIR_REBUILD и выполняем запрос еще раз, это должно убрать оставшиеся ошибки в бд.

После всего этого наша база становится в нормальное состояние и уже доступна для работы с ней, но только в однопользовательском режиме, поэтому завершаем наш процесс возвращением бд в многопользовательский режим.

ALTER DATABASE db_name SET ONLINE, MULTI_USER
GO

Все. База онлайн и готова к работе. Радуемся и не забываем делать бэкапы.

Предикат EXISTS SQL и проверка существования набора значений

Это выражение возвращает истину, когда по запросу найдена одна или более строк, соответствующих условию, и ложь, когда не найдено ни одной строки.

Обычно предикат EXISTS применяется в случаях, когда необходимо найти значения, соответствующие основному условию, заданному в секции WHERE, и дополнительному условию, заключённому в подзапрос, являющийся аргументом предиката.

Для NOT EXISTS всё наоборот. Выражение

возвращает истину, когда по запросу не найдено ни одной строки, и ложь, когда найдена хотя бы одна строка.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

Наиболее простые запросы с предикатом SQL EXISTS

В примерах работаем с базой данных библиотеки и ее таблицами «Книга в пользовании» (BOOKINUSE) и «Пользователь» (USER). Пока нам потребуется лишь таблица «Книга в пользовании» (BOOKINUSE).

Этот запрос вернёт следующий результат:

Пример 2. Определить ID пользователей, которым выданы книги Чехова, и которым при этом не выданы книги Ильфа и Петрова. Конструкция запроса аналогична конструкции из предыдущего примера с той разницей, что дополнительное условие задаётся предикатом NOT EXISTS. Запрос будет следующим:

Этот запрос вернёт следующий результат:

Написать запрос SQL с предикатом EXISTS самостоятельно, а затем посмотреть решение

Пример 3. Определить автора (авторов), книги которого выданы пользователю с ID 120, а также с ID 18.

Различия предикатов EXISTS и IN

При первом взгляде на запросы с предикатом EXISTS может возникнуть впечатление, что он идентичен предикату IN. Это не так. Хотя они очень похожи. Предикат IN ведет поиск значений из диапазона, заданного в его аргументе, и если такие значения есть, то выбираются все строки, соответствующие этому диапазону. Результат же действия предиката EXISTS представляет собой ответ «да» или «нет» на вопрос о том, есть ли вообще какие-либо значения, соответствующие указанным в аргументе. Кроме того, перед предикатом IN указывается имя столбца, по которому следует искать строки, соответствующие значениям в диапазоне. Разберём пример, показывающий отличие предиката EXISTS от предиката IN, и задачу, решаемую с помощью предиката IN.

Пример 4. Определить ID пользователей, которым выданы книги авторов, книги которых выданы пользователю с ID 31. Запрос будет следующим:

Результатом выполнения запроса будет следующая таблица:

Запросы с предикатом EXISTS и дополнительными условиями

Если дополнительно к предикату EXISTS в запросе применить хотя бы одно дополнительное условие, например, заданное с помощью агрегатных функций, то такие запросы могут служить уже для простого анализа данных. Продемонстрируем это на следующем примере.

Пример 5. Определить ID пользователей, которым выдана хотя бы одна книга Пастернака, и которым при этом выдано более 2 книг. Пишем следующий запрос, в котором первое условие задаётся предикатом EXISTS со вложенным запросом, а второе условие с оператором HAVING всегда должно следовать после вложенного запроса:

Результат выполнения запроса:

Как видно из таблицы BOOKINUSE, книга Пастернака выдана также пользователю с ID 18, но ему выдана всего одна книга и он не попадает в выборку. Если применить к подобному запросу ещё раз функцию COUNT, но уже для подсчёта выбранных строк (потренируйтесь в этом самостоятельно), то можно получить сведения о том, сколько пользователей, читающих книги Пастернака, при этом читают также книги других авторов. Это уже из сферы анализа данных.

Запросы с предикатом EXISTS к двум таблицам

Запросы с предикатом EXISTS могут извлекать данные из более чем одной таблицы. Многие задачи можно с тем же результатом решить с помощью оператора JOIN, но в ряде случаев использование EXISTS позволяет составить менее громоздкий запрос. Использовать EXISTS предпочительнее в тех случаях, когда в результирующую таблицу попадут столбцы лишь из одной таблицы.

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

Пример 6. Определить авторов, книги которых выданы пользователю по фамилии Краснов. Пишем следующий запрос, в котором предикатом EXISTS задано единственное условие:

Результатом выполнения запроса будет следующая таблица:

Примеры запросов к базе данных «Библиотека» есть также в уроках по операторам GROUP BY, IN и функциям CONCAT, COALESCE.

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

Сейчас мы увидим более предметно, почему использовать EXISTS предпочительнее в тех случаях, когда в результирующую таблицу попадут столбцы лишь из одной таблицы.

Пример 7. Определить клиентов, купивших или взявших в аренду объекты, у которых нет лоджии или балкона. Пишем следующий запрос, в котором предикатом EXISTS задано обращение к результату соединения двух таблиц:

Так как из таблицы Client столбцы выбираются при помощи оператора «звёздочка», то будут выведены все столбцы этой таблицы, в которой будет столько строк, сколько насчитывается клиентов, соответствующих условию, заданному предикатом EXISTS. Из таблиц, к соединению которых обращается вложенный запрос, нам не требуется выводить ни одного столбца. Поэтому для экономии машинного времени извлекается лишь один столбец. Для этого после слова SELECT прописана единица. Этот же приём применён и в запросах в следующих примерах.

Написать запрос SQL с предикатом EXISTS самостоятельно, а затем посмотреть решение

Пример 3. Определить менеджеров, которые провели сделки с объектами с числом комнат больше 2.

Продолжаем писать вместе запросы SQL с предикатом EXISTS

Пример 9. Определить собственников объектов, которые были взяты в аренду. Пишем следующий запрос, в котором предикатом EXISTS также задано обращение к результату соединения двух таблиц:

Как и в предыдущем примере, из таблицы, к которой обращён внешний запрос, будут выведены все поля.

Пример 10. Определить число собственников, с объектами которых провёл менеджер Савельев. Пишем запрос, в котором внешний запрос обращается к соединению трёх таблиц, а предикатом EXISTS задано обращение лишь к одной таблице:

Все запросы проверены на существующей базе данных. Успешного использования!

Примеры запросов к базе данных «Недвижимость» есть также в уроках по операторам GROUP BY и IN.