Как удалить строку в mysql

10 основных примеров команды DELETE в MariaDB / MySQL

Добавить в избранное

Главное меню » Базы данных » База данных MySQL » 10 основных примеров команды DELETE в MariaDB / MySQL

На этом уроке мы расскажем, как использовать DELETE в MySQL с некоторыми полезными примерами.

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

• Удаление конкретной строки
• Удаление нескольких строк (с помощью сопоставления номера столбца)
• Удаление нескольких строк (с помощью сопоставления строки столбца)
• Удаление записей с опцией LIMIT
• DELETE Vs Truncate (для удаления всех строк)
• Удаление записей с вариантом IGNORE
• Как использовать сортировку с операцией DELETE?
• Удаление записей с опцией QUICK
• Удаление записей с опцией LOW_PRIORITY
• Удаление записей из нескольких таблиц с использованием INNER join

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

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

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

1. Удаление определенной строки

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

Следующий пример удаляет одну запись из таблицы сотрудников, который имеет идентификатор 100.

В приведенном выше выводе:

• Выход команды удаления покажет вам, сколько записей он удаляет. Как видно из приведенного выше вывода, говориться: «1 row affected», что означает, что он удалил одну строку.
• Выходной сигнал будет также сказать «Query OK», если запрос был выполнен. Если есть ошибка синтаксиса, он будет отображать его здесь. Даже если он не удалит любую запись, эта строка будет по-прежнему говорить «Query OK», пока не было никаких ошибок синтаксиса и утверждение было чистым. И, наконец, это также покажет, сколько времени потребовалось для MySQL для выполнения запроса (например: 0,01 секунды).

Первая запись с идентификатором 100 будет удалена из таблицы сотрудников.

2. Удаление нескольких строк с использованием столбца типа String Matching

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

Для строки, вы можете использовать ключевое слово «like», который будет делать частичное соответствие. Для частичного соответствия используется % в столбце значений.

В следующем примере будем удалять записи из таблицы сотрудников, где значение в столбце DEPT начинается с «Tech». Таким образом, это удалит все записи, где отдел «IT».

Приведенный выше вывод указывает на то, что он удалил 3 записи. Как мы видим из следующего выхода, мы не видим больше записей отдела «IT».

Мы обсуждали много о различных практических условиях команды WHERE в нашем MySQL учебнике. Это очень полезно, чтобы понять, как эффективно использовать предложение WHERE во время удаления: 25 Основных примеров команды WHERE в MySQL.

3. Удаление нескольких строк, используя номер столбца Matching

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

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

Вышеуказанная команда удалила 4 записи из нашей таблицы сотрудников. У нас в настоящее время осталась только одна запись.

4. Удаление строк с опцией LIMIT

Даже тогда, когда соответствует много записей, вы все еще можете ограничить, сколько записей следует удалить с помощью опции LIMIT.

Это очень полезно, когда команда удаления занимает очень много времени, и вы хотите разорвать операцию удаления на несколько кусков. Например, при удалении более 100 000 записей, вы можете использовать LIMIT 10000, чтобы удалить только 10000 записей в один момент.

Например, следующая команда удаления без какой-либо опции LIMIT будут удалять все соответствующие записи. т.е. этот удалила 5 записей.

Но когда вы используете опцию LIMIT, то следующим будут удалять только 2 записи, даже если запросу соответствует 5 записей.

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

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

Теперь у нас нет каких-либо более совпадающих записей для удаления.

5. DELETE Vs Truncate (для удаления всех строк)

Если вы хотите удалить все строки из таблицы, вы можете использовать либо команду DELETE или команду TRUNCATE, как показано ниже. Оба будут делать то же самое. Но есть разница.

Ниже приведены несколько вещей, которые вы должны знать о DELETE против TRUNCATE:

• TRUNCATE это намного быстрее, чем DELETE. Если вы просто хотите быстрый способ очистить таблицу, используйте TRUNCATE.
• DELETE это заявление DML. TRUNCATE это заявление DDL.
• При выполнении команды TRUNCATE, это действительно удалит таблицу и заново создать пустую таблицу. Именно по этой причине усечения очень быстро, так как она не удаляет записи один за другим, как команда удаления.
• Когда есть замок на конкретной таблице, вы не можете использовать команду TRUNCATE.
• Как видно из приведенного выше вывода команды TRUNCATE не показывает, сколько строк будут удалено. Он просто скажет: «0 rows affected», даже когда он обрезает таблицу, в которой было несколько строк.
• Так что, если вам нужно знать, сколько записей во время очистки таблицы были удалены, вы должны использовать DELETE.
• Если у вас есть триггер on-delete на удаление таблицы, при выполнении команды TRUNCATE, эти триггеры выполняться не будут.
• Наконец, важное отличие заключается в следующем: При выполнении команды TRUNCATE, вы не можете откатить. Но при выполнении команды DELETE, вы можете откатить. Округление делает неявное обязательство.

6. Удалить строки с опцией IGNORE

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

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

Как видно из приведенного выше вывода, DELETE IGNORE удалит записи из таблицы, даже если существует зависимость внешнего ключа. Он просто выведет те реальные сообщение об ошибке как предупреждения.

7. Зачем использовать Order с DELETE?

Вы можете использовать Order вместе с командой Delete.

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

В команде удаления, когда вы объединяете «order by» и опцию «limit», вы можете сделать некоторые трюки.

Например, при выполнении следующей команды удаления без «order by», удалит запись сотрудника с кодом 100 (т.е. самого старого сотрудника в отделе «IT»).

Приведенная выше команда удалила идентификационный номер сотрудника 200 (самый старый работник IT).

Но, вы можете также удалить нового сотрудника в отделе «IT», если объединить и упорядочить по LIMIT, как показано ниже.

Приведенная выше команда удалила идентификационный номер сотрудника 500 (самый новый работник IT).

Кроме того, имейте в виду, что вы можете использовать ORDER BY в предложении, если вы хотите удалить записи в определенной последовательности, чтобы избежать каких-либо ограничений ссылочной целостности.

8. Удаление строк с опцией QUICK

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

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

9. Удаление строк с опцией LOW_PRIORITY

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

Ключевое слово LOW_PRIORITY будет эффективным только на этих двигателях хранения: MyISAM, MEMORY и MERGE. т.е. Вы можете использовать эту функцию только на двигателях для хранения базы данных, которые реализуют блокировки на уровне таблицы.

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

10. Удалить из нескольких таблиц с использованием INNER JOIN

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

Есть два способа. когда можно указать удаление записей из нескольких таблиц.

В первом способе, можно указать таблицы, из которых записи будут удалены перед ключевым словом «FROM», как показано ниже. Ниже будут удалены записи из worker и benefits таблиц, которые соответствуют критериям, указанным в условии where.

Во втором способе, можно указать таблицы, где записи будут удалены, после ключевого слова “FROM”, как показано ниже. Ниже будет удалять записи из worker и benefits, которые соответствуют критериям, указанным в условии where.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Удаление данных в SQL

Для удаления записей из таблиц в MySQL существует две команды:

• TRUNCATE;
• DELETE.

Команда TRUNCATE имеет следующий синтаксис:

TRUNCATE TABLE имя_таблицы;

Если нужно полностью очистить таблицу tovar, то следует выполнить команду:

TRUNCATE TABLE tovar;

Команда TRUNCATE позволяет очистить таблицу сразу и полностью и не позволяет удалять отдельные строки, удовлетворяющие каким-либо условиям.

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

• Удаление из одной таблицы;
• Каскадное удаление из нескольких таблиц.

Удаление из одной таблицы

DELETE [LOW_PRIORITY | QUICK] FROM имя_таблицы

• Если указана инструкция LOW_PRIORITY, то удаление не произойдет, пока все пользователи не завершат чтение таблицы.
• Инструкция QUICK запрещает объединять индексы при выполнении удаления. Это несколько ускоряет процесс удаления.
• Инструкция ORDER BY позволяет упорядочить записи перед удалением. Сама по себе эта опция не имеет смысла. Ее полезно использовать в сочетании с инструкцией LIMIT.
• Инструкция LIMIT задает количество удаляемых записей.

Для удаления из таблицы tovar всех товаров, которые имеют цену выше 300р. Нужно выполнить команду:

DELETE FROM Tovar WHERE price>300;

Если в таблице Tovar присутствует поле timestamp, которое хранит время добавления каждой записи, то можно удалить самый старый товар с ценой больше 300р.:

DELETE FROM Tovar WHERE price>300 ORDER BY timestamp LIMIT (1);

Готовые работы на аналогичную тему

Каскадное удаление

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

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

Для таблиц InnoDB при построении внешнего ключа всегда описывается реакция СУБД на удаление связанных записей. Реакция может быть трех видов:

• RESTRICT – ограничение;
• CASCADE – каскадное удаление;
• SET NULL – установить значение NULL вместо значений удаленного внешнего ключа.

Пусть имеется таблица tovar, содержащая внешний ключ — categ со ссылкой на таблицу category.

Таким образом, каждый товар относится к определенной категории:

Необходимо удалить категорию «Лакокрасочные» из таблицы category и каскадно удалить все связанные с ней записи из таблицы tovar. Если во время создания внешнего ключа categ была указана реакция CASCADE при удалении, то для решения задачи достаточно выполнить команду:

DELETE FROM category WHERE category.categ_name=”лакокрасочные”;

Все товары, относящиеся к категории «лакокрасочные» автоматически удалятся из связанной таблицы tovar.

Однако, если пользователь работает с таблицами MyIsam, то СУБД не знает ни о каких внешних ключах и каскадного удаления не произойдет. В этом случае пригодятся специальные формы команды DELETE, предназначенные для каскадного удаления:

DELETE [LOW_PRIORITY | QUICK] имя_таблицы [ ,имя_таблицы . ]

DELETE [LOW_PRIORITY | QUICK]

Задачу из примера 4 можно решить двумя эквивалентными командами:

DELETE tovar, category FROM tovar join category on category.itcateg=tovar.categ where category.name_categ=”Лакокрасочные”;

DELETE from tovar, category using tovar join category on category.itcateg=tovar.categ where category.name_categ=”Лакокрасочные”;

На первый взгляд кажется, что команды

TRUNCATE имя_таблицы;

DELETE FROM имя_таблицы;

совершенно эквивалентны. Действительно, они обе полностью очищают таблицу от всех записей. Однако, разница все же есть.

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

Как удалить и вставить строку в MySQL

От автора: пришло время познакомиться с несколькими основными операторами, с помощью которых вебмастер работает в СУБД. В MySQL удалить строку, добавить данные или стереть их можно с помощью специальных команд. Но просто знать их название — мало. Важно понимать синтаксис, последствия, которые возникают после применения операторов, и прочие моменты, которые составляют операции в MySQL. Сегодня знакомимся с DELETE и INSERT, как основополагающими в создании таблиц.

INSERT для новых строк

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

Теперь пришло время разобраться с тем, какие характеристики задает каждая из команд:

tbl_name. Одни из основополагающих параметров, так как без него вы не сможете определить, в какую из таблиц необходимо внести новую строку. В нем указано имя той, в которой должны произойти изменения. В строке операторов стоит после INTO. Как бы указывает что новую строку необходимо вставить в (INTO) конкретную таблицу (tbl_name);

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать веб-приложение на PHP с полного нуля

LOW_PRIORITY. С подобными выражениями вы уже могли быть знакомы, если работали с языками программирования, в принципе. Но для MySQL параметр значит, что вставка не будет произведена до момента, пока остальные скрипты не закончат чтение. Таким образом, сбивается приоритет операции до самого низкого уровня;

DELAYED. При включении этого параметра вставка строки произойдет немедленно после введения команды. Но вот сама информация будет вставлена в БД уже после того, когда другие сценарии закончат работу. Таким образом оптимизируются остальные действия в базе данных. Отложенная вставка возможна лишь на тех табах, которые поддерживают движок ISAM. Кстати, если таблица не взаимодействует с другими запросами, то лучше использовать команду INSERT. А добавление DELAYED — только когда речь идет о высоких нагрузках;

IGNORE. К примеру, вы задали приоритет некоторым таблицам, и теперь новые строки несут в себе такое же значение. При обычной вставке операция оборвется, а с атрибутом игнорирования — продолжится;

INSERT … VALUES. При таком выражении прописываем четкий порядок наименований поля и значений каждого их них. К примеру, нам нужен оператор, который вставит sample строку, где будут полями имя, возраст, страна и город, а значениями — Алиса, 20, Великобритания, Кардифф. Вот так она будет выглядеть: