В случае выполнения инструкции delete обязательно

В этом учебном материале вы узнаете, как использовать оператор DELETE, с синтаксисом и примерами.

Описание

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

Синтаксис

Синтаксис оператора DELETE в SQL:

DELETE FROM table
[WHERE conditions];

Параметры или аргументы

table

Таблица, из которой вы хотите удалить записи.

WHERE conditions

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

Примечание

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

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

Давайте начнем с простого примера запроса DELETE, который имеет одно условие в предложении WHERE.

В этом примере у нас есть таблица suppliers со следующими данными:

supplier_id	supplier_name	city	state
100	Yandex	Moscow	Moscow
200	Google	Lansing	Michigan
300	Oracle	Redwood City	California
400	Bing	Redmond	Washington
500	Yahoo	Sunnyvale	Washington
600	DuckDuckGo	Paoli	Pennsylvania
700	Qwant	Paris	Ile de France
800	Facebook	Menlo Park	California
900	Electronic Arts	San Francisco	California

Введите следующий оператор DELETE:

Будет удалена 1 запись. Снова выберите данные из таблицы поставщиков:

Вот результаты, которые вы должны получить:

supplier_id	supplier_name	city	state
200	Google	Lansing	Michigan
300	Oracle	Redwood City	California
400	Bing	Redmond	Washington
500	Yahoo	Sunnyvale	Washington
600	DuckDuckGo	Paoli	Pennsylvania
700	Qwant	Paris	Ile de France
800	Facebook	Menlo Park	California
900	Electronic Arts	San Francisco	California

В этом примере удаляются все записи из таблицы suppliers, где supplier_name — Yandex.

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

Этот запрос вернет количество записей, которые будут удалены при выполнении оператора DELETE.

COUNT(*)
1

Пример — оператор DELETE с более чем одним условием

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

Давайте рассмотрим пример использования оператора DELETE с двумя условиями с использованием условия AND.
В этом примере у нас есть таблица products со следующими данными:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Введите следующий оператор DELETE:

Будет удалены 3 записи. Снова выберите данные из таблицы products:

Вот результаты, которые вы получите:

product_id	product_name	category_id
1	Pear	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

В этом примере удаляются все записи из таблицы products, у которых category_id равен 50, а product_name НЕ ‘Pear’.

Пример — использование EXISTS с оператором DELETE

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

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

В этом примере у нас есть таблица customer со следующими данными:

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
5000	Selena	Gomez	bing.com
6000	Mila	Kunis	yahoo.com
7000	Tom	Cruise	oracle.com
8000	Johnny	Depp	NULL
9000	Russell	Crowe	google.com

И таблица orders со следующими данными:

order_id	customer_id	order_date
1	7000	2019/06/18
2	5000	2019/06/18
3	8000	2019/06/19
4	4000	2019/06/20
5	NULL	2019/07/01

Введите следующий оператор DELETE:

Будет удалена 1 запись. Выберите данные из таблицы orders еще раз:

Вот результаты, которые вы должны получить:

order_id	customer_id	order_date
1	7000	2019/06/18
2	5000	2019/06/18
3	8000	2019/06/19
5	NULL	2019/07/01

В этом примере удаляются все записи из таблицы orders, где в таблице customers есть запись с фамилией ‘Bieber’ и совпадающим значением customer_id в обеих таблицах. В этом примере была удалена запись для order_id = 4.

Подготовка среды MySQL

Как и в предыдущих статьях, я использовал в этой статье те же самые базу данных и таблицы (база данных travel и таблицы manufacturers и airplanes). В данном случае, однако, я рекомендую вам для простоты начать с нуля и перестроить базу данных и таблицы. Для установки базы данных выполните следующий скрипт:

DROP DATABASE IF EXISTS travel;

CREATE DATABASE travel;

USE travel;

CREATE TABLE manufacturers (

  manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  manufacturer VARCHAR(50) NOT NULL,

  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  last_update TIMESTAMP NOT NULL 

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (manufacturer_id) )

ENGINE=InnoDB AUTO_INCREMENT=1001;

CREATE TABLE airplanes (

  plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  plane VARCHAR(50) NOT NULL,

  manufacturer_id INT UNSIGNED NOT NULL,

  engine_type VARCHAR(50) NOT NULL,

  engine_count TINYINT NOT NULL,

  max_weight MEDIUMINT UNSIGNED NOT NULL,

  wingspan DECIMAL(5,2) NOT NULL,

  plane_length DECIMAL(5,2) NOT NULL,

  parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,

  icao_code CHAR(4) NOT NULL,

  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  last_update TIMESTAMP NOT NULL 

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (plane_id),

  CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) 

    REFERENCES manufacturers (manufacturer_id) ) 

ENGINE=InnoDB AUTO_INCREMENT=101;

После создания базы данных вы можете добавить тестовые данные, необходимые для работы с примерами статьи. Начните с выполнения следующего оператора INSERT, чтобы добавить данные в таблицу manufacturers:

INSERT INTO manufacturers (manufacturer)

VALUES ('Bombardier'), ('Beagle Aircraft Limited');

SELECT *

FROM   manufacturers;

Оператор добавляет две строки в таблицу manufacturers и выводит эти строки. Столбцу manufacturer_id для этих строк должны были быть присвоены значения 1001 и 1002.

После подтверждения наличия данных в таблице manufacturers вы должны выполнить следующий оператор INSERT, который заполнит таблицу airplanes:

INSERT INTO airplanes 

  (plane, manufacturer_id, engine_type, engine_count, 

    wingspan, plane_length, max_weight, icao_code)

VALUES

  ('Learjet 24',1001,'Jet',2,35.58,43.25,13000,'LJ24'),

  ('Learjet 24A',1001,'Jet',2,35.58,43.25,12499,'LJ24'),

  ('Challenger (BD-100-1A10) 300',1001,'Jet',2,63.83,68.75,38850,'CL30'),

  ('Challenger (BD-100-1A10) 350',1001,'Jet',2,69,68.75,40600,'CL30'),

  ('Challenger (CL-600-1A11) 600',1001,'Jet',2,64.33,68.42,36000,'CL60'),

  ('Challenger (CL-600-2A12) 601',1001,'Jet',2,64.33,68.42,42100,'CL60'),

  ('A.109 Airedale',1002,'piston',1,36.33,26.33,2750,'AIRD'),

  ('A.61 Terrier',1002,'piston',1,36,23.25,2400,'AUS6'),

  ('B.121 Pup',1002,'piston',1,31,23.17,1600,'PUP'),

  ('B.206',1002,'piston',2,55,33.67,7500,'BASS'),

  ('D.4-108',1002,'piston',1,36,23.33,1900,'D4'),

  ('D.5-108 Husky',1002,'piston',1,36,23.17,2400,'D5');

SELECT *

FROM   airplanes;

Значения 1001 и 1002 из таблицы manufacturers предоставляют значения внешнего ключа для столбца manufacturer_id в таблице airplanes. После выполнения второго оператора INSERT, запрос SELECT должен подтвердить, что 12 строк был вставлены в таблицу airplanes. Первой строке должно быть присвоено значение 101 в столбце plane_id, для остальных строк значения plane_id должны последовательно возрастать.

Синтаксис оператора DELETE

Базовый синтаксис оператора DELETE довольно прост и включает много тех же самых элементов, которые вы видели в других операторах DML:

DELETE [IGNORE] FROM table_name

[WHERE where_condition]

[ORDER BY order_list]

[LIMIT row_count]

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

• Предложение DELETE, которое включает подчиненное предложение FROM, является единственным обязательным предложением в операторе DELETE. Это предложение задает таблицу, из которой будут удаляться данные. Вы можете указать несколько таблиц, что включает определение условий соединения, но я сфокусируюсь в этой статье на удалении из одной таблицы. Предложение DELETE также поддерживает использование модификатора IGNORE для возвращения предупреждающих сообщений, а не ошибок при возникновении проблем.
• Предложение WHERE определяет, какие строки удалять на основе одного или более поисковых критериев. Предложение работает во многом подобно предложению WHERE в операторах SELECT и UPDATE. Хотя предложение WHERE не является обязательным, следует быть очень осторожным при выполнении оператора DELETE, который его не содержит. Без предложения WHERE оператор удалит каждую строку в целевой таблице, если не включено предложение LIMIT.
• Предложение ORDER BY задает порядок, в котором должны удаляться строки. Это предложение используется, главным образом, в сочетании с предложением LIMIT, чтобы точнее указать, какие строки следует удалить. Предложение ORDER BY подобно аналогичному предложению в операторах SELECT и UPDATE. Это предложение не является обязательным и не может использоваться для удаления из нескольких таблиц.
• Предложение LIMIT ограничивает число удаляемых строк. При использовании с предложением ORDER BY удаляемые строк будут определяться порядком сортировки, заданной этим предложением. Предложение LIMIT не является обязательным и не может использоваться для удаления из нескольких таблиц.

Поработав с примерами в этой статье, вы лучше поймете, как различные элементы оператора работают совместно. Как уже говорилось, эти предложения, по большей части, понятны без объяснения, и вы редко будете испытывать затруднения при работе с ними. БОльшую проблему может вызвать потеря множества данных, если вы не будете внимательны при использовании этого оператора, поэтому всегда соблюдайте осторожность и убедитесь, что все данные надежно защищены. Прежде всего не работайте в производственной среде при изучении работы оператора DELETE.

Удаление данных из таблицы MySQL

Как отмечалось выше, предложение DELETE является единственным обязательным предложением оператора DELETE, Если вы выполните оператор DELETE только с этим предложением, он удалит все данные из целевой таблицы — соображение, которое не следует воспринимать легкомысленно. Если именно это вам и нужно, достаточно указать только ключевые слова DELETE и FROM с последующим именем таблицы, как в следующем примере:

DELETE FROM airplanes;

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

Как и для оператора UPDATE, если режим безопасности включен в вашем экземпляре MySQL, вы получите следующее сообщение об ошибке при попытке выполнить приведенный выше оператор:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
(Вы используете режим безопасного обновления и пытаетесь обновить таблицу без предложения WHERE, которое использует ключевой столбец. Для отключения безопасного режимы переключите опцию в Preferences -> SQL Editor и повторно соединитесь.)

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

Чтобы временно выключить безопасный режим, используйте оператор SET для изменения значения системной переменной SQL_SAFE_UPDATES на 0 до выполнения оператора DELETE, а затем установкой переменной в 1 после выполнения оператора, как показано в следующем примере:

SET SQL_SAFE_UPDATES = 0;

DELETE FROM airplanes;

SET SQL_SAFE_UPDATES = 1;

Операторы SET выключают системную переменную, а затем включают во время текущей сессии. При этом имейте в виду, что если оператор DELETE генерирует ошибку, выполнение оператора прерывается, и второй оператор SET не будет выполнен. Поэтому убедитесь, что вы выполнили этот оператор, чтобы сбросить переменную SQL_SAFE_UPDATES в 1. Заметим также, что оператор SET поддерживает необязательный модификатор GLOBAL, который определяет переменную на глобальном уровне. Однако я рекомендую вам не использовать эту опцию при отключении безопасного удаления. Менее рисковано отключать режим безопасности на уровне сессии, чтобы избежать непреднамеренных модификаций данных. Используйте GLOBAL, только если это существенно в вашей ситуации.

Если безопасный режим выключен, выполнение оператора DELETE не должно вызывать ошибки. После выполнения вы можете подтвердить изменения с помощью простого оператора SELECT, который вернет все данные из таблицы airplanes. Этот оператор не должен возвращать строк.

Добавление предложения WHERE в оператор DELETE

В большинстве случаев вы будете включать предложение WHERE в операторы DELETE для того, чтобы указать строки в таблице, которые должны быть удалены (вместо удаления всех строк). Предложение WHERE определяет одно или более условий поиска, которые точно задают строки для удаления. Чтобы увидеть как это работает, вы должны сначала снова вернуть данные в таблицу airplanes (предполагается, что вы пытались выполнять примеры):

INSERT INTO airplanes 

  (plane, manufacturer_id, engine_type, engine_count, 

    wingspan, plane_length, max_weight, icao_code)

VALUES

  ('Learjet 24',1001,'Jet',2,35.58,43.25,13000,'LJ24'),

  ('Learjet 24A',1001,'Jet',2,35.58,43.25,12499,'LJ24'),

  ('Challenger (BD-100-1A10) 300'

               ,1001,'Jet',2,63.83,68.75,38850,'CL30'),

  ('Challenger (BD-100-1A10) 350'

                ,1001,'Jet',2,69,68.75,40600,'CL30'),

  ('Challenger (CL-600-1A11) 600'

                ,1001,'Jet',2,64.33,68.42,36000,'CL60'),

  ('Challenger (CL-600-2A12) 601'

               ,1001,'Jet',2,64.33,68.42,42100,'CL60'),

  ('A.109 Airedale',1002,'piston',1,36.33,26.33,2750,'AIRD'),

  ('A.61 Terrier',1002,'piston',1,36,23.25,2400,'AUS6'),

  ('B.121 Pup',1002,'piston',1,31,23.17,1600,'PUP'),

  ('B.206',1002,'piston',2,55,33.67,7500,'BASS'),

  ('D.4-108',1002,'piston',1,36,23.33,1900,'D4'),

  ('D.5-108 Husky',1002,'piston',1,36,23.17,2400,'D5');

SELECT *

FROM airplanes;

Подтвердите, что данные были добавлены в таблицу airplanes, проверив вывод оператора SELECT. Таблица должна теперь содержать те же 12 строк с одним заметным отличием. Первое значение plane_id теперь равно 113, а не 101, поскольку MySQL отслеживает последнее автоинкрементируемое значение, присвоенное строке, даже если эта строка была удалена.

После вставки данных в таблицу airplanes вы можете выполнить следующий оператор DELETE, который содержит базовое предложение WHERE:

DELETE FROM airplanes

WHERE icao_code = 'pup';

Условие поиска указывает, что значение icao_code должно равняться pup для удаляемых строк. Однако, если попытаться выполнить этот оператор, и безопасный режим включен, MySQL снова вернет ошибку 1175:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
(Вы используете безопасный режм обновления и пытаетесь обновить таблицу без предложения WHERE, которое использует ключевой столбец. Для выключения безопасного режима переключите опцию в Preferences -> SQL Editor и повторно соединитесь.)

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

SET SQL_SAFE_UPDATES = 0;

DELETE FROM airplanes

WHERE icao_code = 'pup';

SET SQL_SAFE_UPDATES = 1;

Если выполнить запрос к таблице airplanes после выполнения этих операторов, в таблице теперь окажется только 11 строк, а не 12. Одна строка удовлетворяла условиям поиска, поэтому только она и была удалена.

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

DELETE FROM airplanes

WHERE plane_id = 121;

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

SET SQL_SAFE_UPDATES = 0;

DELETE FROM airplanes

WHERE engine_type = 'piston' AND max_weight < 2500 ;

SET SQL_SAFE_UPDATES = 1;

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

Предложение WHERE включает два условия поиска. Первое указывает, что значение engine_type должно быть piston, а второе означает, что max_weight должно быть меньше 2500. Условия поиска соединяются логическим оператором AND, который означает, что оба условия должны быть истинны для удаляемых строк.

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

Добавление предложений ORDER BY и LIMIT в оператор DELETE

Предложения ORDER BY и LIMIT вместе помогают лучше управлять удалением строк из таблицы. Для того, чтобы увидеть их работу, начнем с возвращения строки в таблицу airplanes:

INSERT INTO airplanes 

  (plane, manufacturer_id, engine_type, engine_count, 

    wingspan, plane_length, max_weight, icao_code)

VALUES

  ('D.4-108',1002,'piston',1,36,23.33,1900,'D4');

Поскольку эта строка добавляется позже по времени, когда были добавлены предыдущие строки, она будет иметь отличное от других строк значение create_date. (Значение — временная метка.) Вы можете использовать это значение, чтобы выделить эту строку при построении оператора DELETE:

DELETE FROM airplanes

ORDER BY create_date DESC

LIMIT 1;

Обратите внимание, что оператор не включает предложения WHERE с ключевым столбцом, и что отсутствуют операторы SET. Вы можете так поступить здесь, поскольку оператор DELETE включает предложение LIMIT. При необходимости вы можете включить предложение WHERE с предложениями ORDER BY и LIMIT, но в этом нет необходимости в данном случае.

Предложение ORDER BY определяет, что строки должны удаляться на основе значений create_date, отсортированным в убывающем порядке. Это гарантрует, что последняя вставленная строка является первой удаляемой строкой в предположении уникальности значений create_date. Предложение LIMIT затем указывает, что удаляться должна только одна строка. Это будет первая строка, как определено в предложении ORDER BY. Вы можете применить этот прием при удалении архивных данных, за тем исключением, что указываться в предложении LIMIT будет не 1, а значения 100, 1000 или т.п.

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

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

Использование модификатора IGNORE в операторе DELETE

Как отмечалось ранее, оператор DELETE поддерживает использование необязательного модификатора IGNORE, который вы также видели в операторах INSERT и UPDATE. При использовании IGNORE оператор DELETE будет возвращать предупреждение, а не ошибку, при возникновении проблем. Кроме того, MySQL продолжит выполнение оператора. Если IGNORE не используется, MySQL будет возвращать ошибку и прерывать выполнение оператора, включая любые операторы в пакете, которые следуют за оператором DELETE.

Чтобы увидеть как это работает, начнем с выполнения следующего оператора INSERT, который добавляет несколько строк в таблицу manufacturers:

INSERT INTO manufacturers (manufacturer)

VALUES ('Airbus'), ('Beechcraft'), ('Cessna'), ('Piper');

SELECT *

FROM   manufacturers;

В выводе этого пакета вы должны обнаружить, что строка Airbus имеет значение manufacturer_id, равное 1003. Вы будете использовать это значение как внешний ключ при добавлении строки в таблицу airplanes для самолета Airbus. Чтобы добавить эту строку, выполните следующий оператор INSERT:

INSERT INTO airplanes 

  (plane, manufacturer_id, engine_type, engine_count, 

    wingspan, plane_length, max_weight, icao_code)

VALUES

('A220-100',1003,'Jet',2,115.08,114.75,134000,'BCS1');

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

DELETE FROM manufacturers

WHERE manufacturer_id IN (1003, 1004, 1005, 1006);

Когда вы пытаетесь выполнить этот оператор, MySQL прерывает его выполнение и возвращает следующую ошибку, которая указывает на нарушение внешнего ключа:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))

MySQL вернула эту ошибку, поскольку вы пытались удалить строку, на которую ссылалась таблица airplanes. В результате весь оператор потерпел неудачу, и никакие строки не были удалены. Однако вы можете гарантировать продолжение выполнения оператора DELETE, даже если одно из удалений сбоит, включив модификатор IGNORE:

DELETE IGNORE FROM manufacturers

WHERE manufacturer_id IN (1003, 1004, 1005, 1006);

Теперь оператор возвращает следующее сообщение, которое показывает число строк, которое было обработано, и предупреждение:

3 row(s) affected, 1 warning(s): 1451 Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))

Из этого сообщения следует, что три строки были удалены, и что имелось нарушение ограничения внешнего ключа. Если обратиться к таблице manufacturers, вы обнаружите, что она содержит теперь только три строки, включая одну для Airbus. Чтобы удалить все данные Airbus, вы должны сначала удалить любые ссылающиеся записи из таблицы airplanes:

DELETE FROM airplanes

WHERE manufacturer_id = 1003;

Затем вы сможете выполнить следующий оператор DELETE для удаления записи Airbus из таблицы manufacturers, в которой останется только две строки:

DELETE  FROM manufacturers

WHERE manufacturer_id = 1003;

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

Работа с оператором DELETE в MySQL

Наряду с операторами SELECT, INSERT и UPDATE, оператор DELETE представляет наиболее важный оператор, который используется для работы с данными MySQL. Однако это не все операторы DML. MySQL также поддерживает такие операторы DML, как CALL, LOAD DATA, REPLACE и TABLE.

Но рассмотренные в этой серии четыре оператора отлично подходят для начала работы с данными, при этом оператор DELETE завершает картину. Этот оператор делает возможным легко удалять данные, являющиеся некорректными или устаревшими. Фактически оператор слишком легок в использовании, и вы должны быть осторожны, чтобы непреднамеренно не удалить данные по ошибке. Тем не менее, оператор DELETE чрезвычайно полезен, и вы должны быть уверены, что вполне понимаете, как его использовать наряду с операторами SELECT, INSERT и UPDATE.

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

delete
FROM
имя_таблицы

[WHERE
условие];

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

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

delete
FROM
staff

WHERE
lname=‘Иванов’;

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

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

Удалить
информацию обо всех сотрудниках
организации:

DELETE
FROM staff;

В
результате выполнения приведенной
инструкции таблица STAFF становится
пустой, но из базы данных она не удаляется,
так как определение таблицы и ее столбцов
остается и в нее по-прежнему можно
добавлять новые строки. Чтобы удалить
из базы данных определение таблицы,
необходимо использовать DDL-инструкцию
DROP TABLE.

Инструкция
DELETE, приведенная в примере несет в себе
потенциальную угрозу удаления необходимых
строк, поэтому всегда следует задавать
условие отбора и обращать внимание на
то, отбирает ли оно действительно
ненужные строки. Желательно вначале
проверить предложение WHERE в интерактивном
режиме в составе инструкции SELECT и
отобразить выбранные строки на экране.
Убедившись, что это именно те строки,
которые требуется удалить, можно
использовать предложение WHERE в инструкции
на удаление.

Инструкция
DELETE с подчиненным запросом.
Инструкции DELETE с простыми условиями
отбора отбирают строки для удаления
исключительно на основании содержимого
этих строк. Но иногда отбор строк
необходимо производить, опираясь на
данные из других таблиц. Предположим,
вы хотите удалить все объекты, принадлежащие
какому-либо владельцу. Не зная его
идентификатора, невозможно найти
принадлежащие ему объекты. Чтобы найти
эти объекты, надо обратиться с запросом
к таблице владельцев (OWNER). Для решения
подобных задач SQL предоставляет
возможность задания подзапросов в
предложении WHERE инструкции на удаление.
Задача удаления информации об объектах,
принадлежащих владельцу Иванову,
решается следующим образом:

DELETE
FROM property_for_rent

WHERE
property_for_rent.ono=(SELECT
ono

FROM
owner

WHERE
lname=‘Иванов’);

Подчиненные
запросы в предложении WHERE могут иметь
несколько уровней вложенности. Они
могут также содержать внешние ссылки
на целевую таблицу инструкции DELETE. В
этом случае предложение FROM инструкции
DELETE играет такую же роль, как и предложение
FROM инструкции SELECT.

15. Обновление данных (инструкция update)

Инструкция
UPDATE обновляет значения одного или
нескольких столбцов в выбранных строках
одной таблицы.

update
имя_таблицы

set
имя_столбца_1=значение_1
[, имя_столбца_2=значение_2
…]

[WHERE
условие];

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

Увеличить
заработную плату менеджеров на 10%

update
staff

set
salary=salary*1.1

WHERE
position=‘manager’;

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

Как
и инструкция DELETE, инструкция UPDATE может
одновременно обновить несколько строк,
соответствующих условию отбора. Например:

Перевести
всех служащих из отделения № 2 в отделение
№ 1

update
staff

set
bno=1

WHERE
bno=2;

Предложение
SET в инструкции представляет собой
список операций присваивания, отделяемых
друг от друга запятыми. В каждой операции
идентифицируется столбец, который
должен обновляться, и определяется
новое значение для этого столбца. Каждый
целевой столбец должен встречаться в
списке только один раз. Согласно стандарту
ANSI/ISO, для столбцов необходимо использовать
простые имена, но некоторые СУБД допускают
использование полных имен столбцов.

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

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

Обновление
всех строк.
Предложение
WHERE в инструкции UPDATE является необязательным.
Если оно опущено, то обновляются все
строки целевой таблицы. Например:

Увеличить
заработную плату всех сотрудников на
5%:

update
staff

set
salary=1.05*salary;

Инструкции
UPDATE
с подчиненным запросом.
В инструкции UPDATE, так же как и в инструкции
DELETE, подчиненные запросы могут играть
важную роль, поскольку они дают возможность
отбирать строки для обновления, опираясь
на информацию из других таблиц. Например:

Всем
служащим отделения Минского отделения
увеличить зарплату на 10%

update
staff

set
salary=1.1*salary

WHERE
bno
IN (SELECT bno

FROM
branch

WHERE
city=‘Минск’);

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

Инструкция
MERGE.
Начиная с версии Oracle 9i, появилась
возможность использовать инструкцию
MERGE INTO, которая объединяет в себе
возможности инструкций вставки,
обновления и удаления, делая их более
удобными для пользователей.

Общий
синтаксис инструкции MERGE INTO:

MERGE
INTO destination_table dest

USING
(SELECT col1, col2, col3 FROM source_table) source1

ON
(dest.col1 = source1.col1)

WHEN
MATCHED THEN

UPDATE
SET dest.col2 = source1.col2,

dest.col3
= source1.col3

WHERE
source1.col2 IS NOT NULL

DELETE
source1.col2 IS NULL

WHEN
NOT MATCHED THEN

INSERT
(dest.col1, dest.col2, dest.col3)

VALUES
(source1.col1, source1.col2, source1.col3)

WHERE
source1.col2 IS NOT NULL

Пример
использования:

MERGE
INTO copy_emp c

USING
employees e

ON
(c.employee_id = e.employee_id)

WHEN
MATCHED THEN

UPDATE
SET

c.first_name
= e.first_name,

c.last_name
= e.last_name,

c.department_id
= e.department_id

WHEN
NOT MATCHED THEN

INSERT
VALUES(e.employee_id, e.first_name, e.last_name,

e.email,
e.phone_number, e.hire_date, e.job_id,

e.salary,
e.commission_pct, e.manager_id,

e.department_id);

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #