Как очистить содержимое таблицы sql

Оператор SQL DELETE для удаления данных из таблицы

Оператор SQL DELETE и удаление данных с условием

Оператор SQL DELETE предназначен для удаления данных из таблицы. Он имеет следующий синтаксис:

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

В результате в таблице останутся лишь следующие строки:

Пример 4. Чтобы удалить все данные из таблицы ADS, достаточно написать следующий запрос:

Если после выполнения этого запроса обратиться к таблице ADS при помощи оператора SELECT, применяемого для получения выборки данных, то будет выведено сообщение о том, что эта таблица не содержит данных.

Оператору DELETE без условий и ограничений аналогичен оператор TRUNCATE TABLE. Он также удаляет из таблицы все строки, но выполняется намного быстрее.

Примеры запросов к базе данных «Портал объявлений-1» есть также в уроках об операторах INSERT, UPDATE, HAVING и UNION.

Источник

DELETE (Transact-SQL)

Удаляет одну или несколько строк из таблицы или представления в SQL Server.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

WITH
Задает временный именованный результирующий набор, также называемый обобщенным табличным выражением, который определяется в области действия инструкции DELETE. Результирующий набор получается из инструкции SELECT.

Обобщенные табличные выражения также можно использовать в инструкциях SELECT, INSERT, UPDATE и CREATE VIEW. Дополнительные сведения см. в разделе WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]
Задает количество или процент удаляемых случайных строк. expression может быть либо числом, либо процентом от числа строк. Строки, на которые ссылается выражение TOP, используемое с инструкциями INSERT, UPDATE и DELETE, не упорядочиваются. Дополнительные сведения см. в разделе TOP (Transact-SQL).

FROM
Необязательное ключевое слово, которое можно использовать между ключевым словом DELETE и целевым аргументом table_or_view_name или rowset_function_limited.

table_alias
Псевдоним, заданный в предложении FROM table_source и представляющий таблицу или представление, строки которых будут удалены.

server_name
Область применения: SQL Server 2008 и более поздних версий.

Имя сервера (с использованием имени связанного сервера или функции OPENDATASOURCE в качестве имени сервера), на котором расположена таблица или представление. Если указано server_name, необходимо указать database_name и schema_name.

database_name
Имя базы данных.

schema_name
Имя схемы, которой принадлежит таблица или представление.

table_or_view_name
Имя таблицы или представления, откуда удаляются строки.

Табличную переменную в пределах ее области действия также можно использовать в качестве источника таблицы в инструкции DELETE.

Представление, на которое ссылается аргумент table_or_view_name, должно быть обновляемым и ссылаться только на одну базовую таблицу в предложении FROM определения представления. Дополнительные сведения об обновляемых представлениях см. в разделе CREATE VIEW (Transact-SQL).

rowset_function_limited
Область применения: SQL Server 2008 и более поздних версий.

Функция OPENQUERY или OPENROWSET в зависимости от возможностей поставщика.

WITH ( [. n] )
Задает одно или несколько табличных указаний, разрешенных для целевой таблицы. Ключевое слово WITH и круглые скобки обязательны. Использование ключевых слов NOLOCK и READUNCOMMITTED запрещено. Дополнительные сведения о табличных указаниях см. в разделе Табличные указания (Transact-SQL).

Возвращает удаленные строки или выражения, основанные на них, как часть операции DELETE. Предложение OUTPUT не поддерживается ни в каких инструкциях DML, направленных на представления и удаленные таблицы. Дополнительные сведения об аргументах и поведении этого предложения см. в статье Предложение OUTPUT (Transact-SQL).

FROM table_source
Задает дополнительное предложение FROM. Это расширение языка Transact-SQL для инструкции DELETE позволяет задавать данные из и удалять соответствующие строки из таблицы в первом предложении FROM.

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

Дополнительные сведения см. в разделе FROM (Transact-SQL).

WHERE
Указывает условия, используемые для ограничения числа удаляемых строк. Если предложение WHERE не указывается, инструкция DELETE удаляет все строки из таблицы.

Предусмотрено два вида операций удаления в соответствии с тем, что указывается в предложении WHERE.

Операции удаления с поиском указывают условие поиска для уточнения строк, которые будут удалены. Например, WHERE column_name = value.

Операции удаления по позиции используют предложение CURRENT OF для указания курсора. Удаление осуществляется в текущей позиции курсора. Эта операция может быть более точной, чем инструкция DELETE по найденному, которая использует предложение WHERE search_condition для указания удаляемых строк. Инструкция DELETE по найденному удаляет несколько строк, если условие поиска не определяет уникально одну строку.

Указывает ограничивающие условия для удаляемых строк. Количество предикатов, которое может содержать условие поиска, не ограничено. Дополнительные сведения см. в разделе Условие поиска (Transact-SQL).

CURRENT OF
Указывает выполнение инструкции DELETE в текущей позиции указанного курсора.

GLOBAL
Указывает, что аргумент cursor_name ссылается на глобальный курсор.

cursor_name
Имя открытого курсора, из которого производится выборка. Если существует как глобальный, так и локальный курсор с именем cursor_name, этот аргумент ссылается на глобальный курсор, если указан аргумент GLOBAL, в противном случае он ссылается на локальный курсор. Курсор должен позволять производить обновления.

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

Рекомендации

Обработка ошибок

В случае арифметической ошибки (переполнение, деление на ноль или выход за пределы допустимых значений), возникающей в ходе вычисления выражения при выполнении инструкции DELETE, Компонент Database Engine будет обрабатывать эти ошибки, как если бы для SET ARITHABORT было задано значение ON. Оставшаяся часть пакетной операции отменяется и возвращается сообщение об ошибке.

Совместимость

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

При удалении строки, содержащей столбец FILESTREAM, также удаляются и связанные с ней файлы файловой системы. Базовые файлы удаляются сборщиком мусора FILESTREAM. Дополнительные сведения см. в разделе Доступ к данным FILESTREAM с помощью Transact-SQL.

Ограничения

При использовании TOP с DELETE строки, на которые имеются ссылки, не упорядочиваются, а предложение ORDER BY не может быть прямо указано в этой инструкции. Если вам нужно использовать TOP для удаления строк в значимом хронологическом порядке, вы должны использовать TOP вместе с предложением ORDER BY в инструкции подзапроса. См. подраздел «Примеры» далее в этом разделе.

TOP нельзя использовать вместе с инструкцией DELETE для секционированных представлений.

Режим блокировки

По умолчанию инструкция DELETE всегда получает монопольную блокировку намерения (IX) для объекта таблицы и страниц, которые он изменяет, монопольную блокировку (X) для строк, которые он изменяет, и удерживает эти блокировки до завершения транзакции.

Если ресурс удерживается монопольной блокировкой намерения (IX), другие транзакции не могут изменять тот же набор данных. Операции считывания будут допускаться только при наличии подсказки NOLOCK или уровня изоляции незафиксированной операции чтения. Можно переопределить поведение оптимизатора запросов по умолчанию с помощью табличных подсказок на время выполнения инструкции DELETE указанием другого способа блокировки, но использовать подсказки рекомендуется только опытным разработчикам и администраторам баз данных и только при крайней необходимости. Дополнительные сведения см. в разделе Табличные указания (Transact-SQL).

Когда строки удаляются из кучи, компонент Компонент Database Engine может использовать строку или страницу блокировки для операции. В результате пустые страницы, в которых выполняются операции удаления, остаются размещенными для кучи. Если их не освободить, занимаемое ими место не может быть использовано под другие объекты базы данных.

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

Задайте указания TABLOCK в инструкции DELETE. Использование TABLOCK приведет к тому, что при выполнении операции удаления будет установлена блокировка IX объекта, а не блокировка строки или страницы. что позволит освободить страницы. Дополнительные сведения об указании TABLOCK см. в разделе Табличные указания (Transact-SQL).

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

Режим ведения журнала

Инструкция DELETE всегда полностью регистрируется в журнале.

Безопасность

Разрешения

Разрешения DELETE необходимы для целевой таблицы. Разрешения SELECT также необходимы, если инструкция содержит предложение WHERE.

Примеры

Базовый синтаксис

В примерах в этом разделе описывается базовая функциональность инструкции DELETE с помощью минимального необходимого синтаксиса.

A. Использование инструкции DELETE без предложения WHERE

Ограничение удаляемых строк

В примерах в этом разделе описываются способы ограничения количества удаляемых строк.

Б. Использование предложения WHERE для удаления набора строк

В. Использование курсора для определения удаляемой строки

Г. Использование операторов объединения и вложенных запросов к данным в одной таблице для удаления строк в другой таблице

Д. Ограничение числа удаляемых строк с помощью ключевого слова TOP

Если с инструкцией DELETE применяется предложение TOP (n), то операция удаления производится над n случайно выбранными строками. Следующий пример удаляет 20 случайных строк из таблицы PurchaseOrderDetail в базе данных AdventureWorks2012, имеющих дату ранее 1 июля 2006 г.

Если необходимо с помощью предложения TOP удалять строки в значимом хронологическом порядке, то вместе с ним в инструкции вложенного запроса выборки следует использовать ORDER BY. Следующий запрос удаляет из таблицы PurchaseOrderDetail 10 строк, имеющих самую раннюю дату. Чтобы гарантировать удаление только 10 строк, столбец, указанный в инструкции подзапроса выборки ( PurchaseOrderID ) должен являться первичным ключом таблицы. Использование неключевого столбца в инструкции подзапроса выборки может привести к удалению более чем 10 строк, если указанный столбец содержит повторяющиеся значения.

Удаление строк из удаленной таблицы

В примерах в этом разделе описаны способы удаления строк из удаленной таблицы с использованием в качестве ссылки на удаленную таблицу связанного сервера или функции, возвращающей набор строк. Удаленная таблица существует на другом сервере или экземпляре SQL Server.

Область применения: SQL Server 2008 и более поздних версий.

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

Ж. Удаление данных из удаленной таблицы с помощью функции OPENQUERY

З. Удаление данных из удаленной таблицы с помощью функции OPENDATASOURCE

В следующем примере выполняется удаление строк из удаленной таблицы с помощью вызова функции OPENDATASOURCE, возвращающей набор строк. Определите допустимое имя сервера для источника данных, используя формат server_name или server_name\instance_name.

Сбор результатов для инструкции DELETE

И. Использование инструкции DELETE с предложением OUTPUT

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

К. Использование предложения OUTPUT с аргументом в инструкции DELETE

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

Л. Удаление всех строк из таблицы

М. Инструкция DELETE для удаления набора строк из таблицы

Н. Использование предложения LABEL с инструкцией DELETE

В следующем примере используется метка с инструкцией DELETE.

О. Использование метки и указания запроса с инструкцией DELETE

Этот запрос показывает базовый синтаксис для использования указания на соединение с запросом с инструкцией INSERT. Дополнительные сведения об указаниях соединения и использовании предложения OPTION см. в статье Предложение OPTION (Transact-SQL).

П. Удаление с помощью предложения WHERE

В этом запросе показано, как удалить с помощью предложения WHERE вместо предложения FROM.

Т. Удаление по результатам соединения с другой таблицей

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

Источник

DELETE. Удаление записей в таблице базы данных MySQL

Команда DELETE

Синтаксис запроса на удаление записи.

Будьте предельно внимательны при выполнении запросов на удаление записей! Если вы не укажите команду WHERE и последующее условие, то будут удалены все записи в таблице.

Удаление нескольких записей таблицы

Для примера удалим несколько записей из таблицы books, которая хранится в базе данных Bookstore.

Оповестим сервер MySQL о базе данных, для которой будут выполнятся запросы.

Далее выведем записи таблицы books с идентификаторами с 1 по 5.

mysql> SELECT id, title, author, price, discount FROM books WHERE id BETWEEN 1 AND 5;
+—-+————————+——————————+———+———-+
| id | title | author | price | discount |
+—-+————————+——————————+———+———-+
| 1 | Капитанская дочка | А.С.Пушкин | 151.20 | 0 |
| 2 | Мертвые души | Н.В.Гоголь | 141.00 | 0 |
| 3 | Анна Каренина | Л.Н.Толстой | 135.00 | 20 |
| 4 | Бесы | Ф.М.Достоевский | 122.00 | 0 |
| 5 | Нос | Н.В.Гоголь | 105.00 | 0 |
+—-+————————+——————————+———+———-+
5 rows in set (0.00 sec)

Допустим необходимо удалить все записи с книгами за авторством Н.В.Гоголя. Запрос на удаление и его результат будет выглядеть следующим образом.

mysql> DELETE FROM books WHERE author= ‘Н.В.Гоголь’ ;
Query OK, 2 rows affected (0.00 sec)

Удаление всех записей таблицы

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

Следующая команда удалит все записи в таблице books.

Источник

Как очистить таблицу в MySQL

В статье мы расскажем, как в MySQL очистить таблицу. Мы покажем два способа и объясним разницу между ними.

Очистить таблицу можно при помощи одной из команд:

Разница между TRUNCATE и DELETE

Команда TRUNCATE является оператором DDL. DDL (Data Definition Language) — это язык определения данных. Операторы языка DDL управляют объектами баз данных: удаляют, создают или переименовывают объекты БД.

Команда DELETE является DML-оператором. DML (Data Manipulation Language) — это язык манипуляции данными. Операторы языка DML позволяют вставить, удалить, изменить, извлечь или обновить данные в базе.

Сравним работу команд:

Выбор команды зависит от конкретного случая. Если нужно удалить некоторые строки по условию, подойдёт только DELETE. Если нужно полностью очистить таблицу и сбросить идентификаторы, используйте TRUNCATE.

Понять разницу между командами и определиться с их выбором поможет таблица с операторами каждого языка:

Таким образом, операторы DDL управляют структурой, а операторы DML — её содержимым.

Как в MySQL очистить таблицу

Подключитесь к серверу по SSH. Затем подключитесь к MySQL при помощи команды:

Вместо username введите имя пользователя, вместо password — пароль.

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

Если подключение без пароля не настроено, возникнет ошибка:

В этом случае сбросьте пароль от root-пользователя MySQL по инструкции.

TRUNCATE

TRUNCATE полностью очищает таблицу без возможности указать дополнительные условия. Для этого:

Выберите базу данных, в которой находится таблица, которую вы хотите очистить:

Вместо db_name введите имя базы данных.

Очистите таблицу при помощи команды:

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

Вместо db_name введите имя базы данных, а вместо table_name введите имя таблицы.

Готово, вы очистили таблицу.

DELETE

Выберите базу данных, в которой находится таблица, которую вы хотите очистить:

Вместо db_name введите имя базы данных.

Очистите таблицу при помощи команды:

Вы можете указать название БД и таблицы в одном запросе:

Вместо db_name введите имя базы данных, а вместо table_name введите имя таблицы.

Готово, вы очистили таблицу при помощи DELETE.

DELETE позволяет использовать условие WHERE, чтобы удалить некоторые строки из таблицы:

DELETE FROM table_name WHERE condition;

Где condition — это условие.

Пример команды, в котором будут удалены все строки, значение столбца, id которых больше 1000:

DELETE FROM table_name WHERE id > 1000;

Как проверить содержимое таблицы

Если вы хотите убедиться, что в таблице не осталось данных, воспользуйтесь командой:

Вместо db_name введите имя базы данных, а вместо table_name введите имя таблицы.

Если таблица пустая, вывод будет следующим:

Источник

Как очищать данные при помощи SQL

За время работы автору довелось использовать многие инструменты анализа, включая Excel, R и Python. Попробовав PostgreSQL и TimescaleDB, автор поняла, насколько простыми могут быть задачи очистки. Делимся подробностями сравнения PostgreSQL и Python из блога TimescaleDB, пока у нас начинается курс по аналитике данных.

Процесс анализа вкратце

Раньше столбцы и значения данных приходилось редактировать вручную. Приходилось извлекать «сырые» данные из CSV-файла или базы данных, а затем изменять их в Python-скрипте.

Приходилось ждать, пока машина настроит и очистит данные. А чтобы поделиться очищенными данными, нужно было запускать скрипт или передавать его другим людям. Но теперь благодаря PostgreSQL я один раз пишу запрос очистки на SQL прямо в базе данных и сохраняю результаты в таблице.

О наборе данных

Бо́льшую часть работы по очистке я проводила после анализа. Но иногда полезно очистить данные, оценить их и снова очистить. Именно с таким случаем мы и будем работать. В одном из наборов данных Kaggle содержатся показания потребления энергии одной из квартир в Сан-Хосе, штат Калифорния. Данные обновляются каждые 15 минут и следуют приблизительно такой схеме:

Вот что приходит в голову первым в смысле очистки:

Тариф — текстовый тип, а это вызовет проблемы.

Столбцы времени и даты разделены, что может вызвать проблемы при создании графиков или моделей на основе показателей времени.

Может понадобиться отфильтровать данные по временным параметрам, например по дню недели или конкретным праздникам (оба параметра влияют на потребление энергии).

К процессу очистки в PostgreSQL можно подойти по-разному: можно создать таблицу, а затем изменить её при очистке, создать несколько таблиц при добавлении или изменении данных или работать с представлениями. В зависимости от размера данных эти подходы могут иметь смысл, но вычисления будут выполнятся по-разному.

Часто при работе с большим объёмом данных изменение таблицы в PostgreSQL может оказаться дорогим. Я покажу, как с помощью представлений и дополнительных таблиц создать чистые данные.

Проблемы структуры

Разделённые столбцы даты и времени надо преобразовать в метку времени, а столбец тарифов— в тип float4. Подробности ниже.

Гипертаблицы TimescaleDB, и почему важна метка времени

В основе эффективности запроса данных временного ряда и управления этими данными лежат гипертаблицы TimescaleDB. Они разделяются по столбцу времени, который вы укажете при создании таблицы.

Данные разделяются по метке времени на «куски», так что каждая строка таблицы принадлежит какому-то куску исходя из диапазона. Позже эти куски используются в запросах строк, чтобы запросы и манипулирование данными по времени были эффективнее. Ниже вы видите разницу между обычной таблицей и гипертаблицей:

Изменение структуры даты и времени

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

Структура таблицы должна быть такой, чтобы из столбцов date и start_time можно было получать полезное значение метки времени: end_time не даёт столько информации. Иными словами, надо объединить эти два столбца в один с метками времени.

Изменение типов данных столбцов

Благодаря функции TO_NUMBER() в PostgreSQL это просто.

Преобразование ограничим значениями не больше 99,99, поскольку в столбце тарифов нет значений больше 0,65. А что если надо преобразовать столбец с большими числовыми значениями? Тогда добавляем G для запятых.

Представления PostgreSQL

Представление — это объект PostgreSQL, который позволяет определять запрос и вызывать его по имени представления, как если бы это была таблица БД. Сгенерируем данные и создадим представление:

Важно: данные внутри представлений PostgreSQL должны пересчитываться при каждом запросе. Вот почему надо вставлять данные представления в гипертаблицу, как только они подготовлены.

Cоздание или генерирование необходимых данных

Столбец примечаний ( notes ) в этом наборе пуст. Чтобы проверить это, просто включаем оператор WHERE и указываем, где notes не равны пустой строке.

Столбец примечаний пуст, поэтому заменим его различными наборами дополнительной информации, чтобы использовать эту информацию при моделировании.

Добавим столбец дня недели при помощи EXTRACT() — функции даты/времени PostgreSQL, которая позволяет извлекать из даты и времени различные элементы. У наших колонок в PostgreSQL есть обозначение дня недели DOW (day-of-week): 0 — это воскресенье, а 6 — суббота.

Можно добавить столбец, где указывается, приходится ли день на выходные или будни. Для этого воспользуемся оператором CASE:

Обратите внимание: в Python выходные представлены числами 5 и 6, а в PostgreSQL — числами 0 и 6.

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

Вы спросите: «Зачем создавать столбцы логических значений?». Для фильтрации. В PostgreSQL благодаря логическим столбцам очень легко фильтровать данные. Например, если нужно показать данные только за выходные и праздничные дни, добавим WHERE вместе с указанными столбцами.

Добавление данных в гипертаблицу

Подготовив новые столбцы и организовав таблицу, создадим новую гипертаблицу и вставим очищенные данные.

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

Переименование значений

Ещё один ценный метод очистки данных — переименование элементов или повторное отображение категориальных значений.

Его важность подчёркивается популярностью вопроса об анализе данных Python на StackOverflow: «Как во фрейме Pandas поменять одно значение индекса». PostgreSQL и TimescaleDB используют структуры реляционных таблиц, поэтому переименовывать уникальные значения просто.

Внимание: код ниже сделает понедельник равным 7, потому что в функции DOW (day-of-week) Python значение понедельника 0, а воскресенья — 6. Но так и обновляется одно значение в столбце. При этом обновлять значения, скорее всего, не придётся, а эквивалент на Python показан просто для справки.

Аналогичные функции отображения есть в Python.

SQL здесь быстрее и элегантнее. На Python переименование столбцов может стать большой проблемой.

Заполнение недостающих данных

Другая проблема в процессе очистки данных — это их отсутствие. В нашем наборе нет явно отсутствующих точек данных, но наверняка найдутся недостающие данные по часам, возникающие из-за отключения электроэнергии или других обстоятельств. Здесь и пригодятся функции заполнения TimescaleDB.

Недостающие данные часто оказывают большое и негативное влияние на точность или надёжность модели. Иногда проблема решается заполнением недостающих данных данными, которые получены обоснованными оценками. Чтобы получить такие данные, TimescaleDB предоставляет встроенные функции.

Например, при моделировании потребления энергии в отдельные дни недели по некоторым дням из-за отключения электроснабжения или проблем с датчиком данных нет. Данные можно удалить или заполнить недостающие значения обоснованными оценками.

Чтобы добавить эти недостающие значения, используем гиперфункции TimescaleDB; interpolate() — ещё одна гиперфункция TimescaleDB. Она создаёт точки данных, которые следуют линейной аппроксимации с учётом точек данных до и после отсутствующего диапазона.

Продолжить изучение SQL и Python вы сможете на наших курсах:

Другие профессии и курсы

Data Science и Machine Learning

Источник