Меню

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

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

SQL Оператор DELETE

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

Описание

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

Синтаксис

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

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

Примечание

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

Пример оператора 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 со следующими данными:

Источник

Лекция 14. Создание, модификация и удаление таблиц

План:

  1. Создание таблицы
  2. Модификация таблиц
  3. Удаление таблиц

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

[UNIQUE | PRIMARY KEY]

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

  • NOT NULL — в этом случае элементы столбца всегда должны иметь определенное значение (не NULL)
  • один из взаимоисключающих параметров UNIQUE — значение каждого элемента столбца должно быть уникальным или PRIMARY KEY — столбец является первичным ключом.
  • REFERNCES [ ] — эта конструкция определяет, что данный столбец является внешним ключом и указывает на ключ какой мастер_таблицы он ссылается.

Контроль за выполнением указанных условий осуществляет СУБД

Модификация таблиц

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

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

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

Команда ALTER TABLE берет на себя все действия по копированию данных во временную таблицу, удалению старой таблицы и созданию вместо нее новой таблицы с нужной структурой и последующим переписыванием в нее данных.

Назначение многих параметров и ключевых слов команды ALTER TABLE аналогично назначению соответствующих параметров и ключевых слов команды CREATE TABLE.

Рассмотрим основные режимы использования команды ALTER TABLE:

  • добавление столбца;
  • удаление столбца;
  • модификация столбца;
  1. Добавление столбца

ALTER TABLE ADD
(
[NOT NULL]
[UNIQUE | PRIMARY KEY]
[REFERENCES ( )]
. )

  1. Модификация столбца

ALTER TABLE
ALTER COLUMN(
[NOT NULL]
[UNIQUE | PRIMARY KEY]
[REFERENCES ( )]
. )

Изменение столбца невозможно, если:

  • столбец участвует в ограничениях PRIMARY KEY или FOREIGN KEY;
  • на столбец наложены ограничения целостности, например UNIQUE (исключение – столбцы, имеющие тип данных переменной длины;
  • со столбцом связано значение по умолчанию.

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

  1. Удаление столбца

ALTER TABLE DROP

Нельзя удалять столбцы с ограничением целостности CHECK, FOREIGN KEY, UNIQUE или PRIMARY KEY, а также столбцы, для которых определены значения по умолчанию.

Удаление таблиц

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

Вопросы для самоконтроля:

  1. Приведите общий синтаксис SQL-оператора для создания таблицы.
  2. Приведите общий синтаксис SQL-оператора для добавления столбца в таблицу.
  3. Приведите общий синтаксис SQL-оператора для модификации столбца.
  4. В каких случаях модификация столбца невозможна?
  5. Приведите общий синтаксис SQL-оператора для удаления столбца.
  6. В каких случаях удаление столбца невозможно?

Источник



SQL DELETE

Команда DELETE

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

Команда DELETE. Синтаксис команды DELETE

DELETE

Синтаксис команды DELETE

Команда DELETE. Основные ключевые слова и параметры команды DELETE

  • schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя
  • table view — имя таблицы, из которой удаляются строки; если определяется представление, сервер удаляет строки из основной таблицы представления
  • subqueryподзапрос, с помощью которого выбираются строки для удаления; сервер выполняет подзапрос и использует строки его результата как таблицу фразы FROM
  • WHERE — удаляет только строки, которые удовлетворяют условию; условие может ссылаться на таблицу и содержать подзапрос.

При выдаче утверждения DELETE включается любой DELETE-триггер, определенный на таблице.
Команда DELETE Пример №1
Удаление всех строк из таблицы:

DELETE FROM temp_assign;

В данном примере команда DELETE удаляет все строки без исключения.
Команда DELETE Пример №2.
Удаляет из таблицы всех продавцов, у которых комиссионные меньше 100 у.е. в месяц:

DELETE FROM emp WHERE JOB = ‘SALESMAN’ AND COMM < 100;

В данном примере команда DELETE удаляет все строки, которые попадают под условие JOB = ‘SALESMAN’ AND COMM < 100;
Команда DELETE Пример №3
Предыдущий пример можно записать по-другому:

DELETE FROM (select * from emp) WHERE job = ‘SALESMAN’ AND comm < 100;

Для удаления всех записей в MySQL можно использовать следующую команду DELETE:

DELETE * FROM table_nam;

Источник

Удаление таблицы (оператор DROP TABLE)

Таблицы можно удалить из базы данных посредством оператора DROP TABLE.

DROP TABLE имя_таблицы CASCADE | RESTRICT

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

Стандарт SQL2 требует, чтобы оператор DROP TABLE включал в себя либо параметр CASCADE, либо RESTRICT, которые определяют, как влияет удаление таблицы на другие объекты базы данных. Если задан параметр RESTRICT и в базе данных имеются объекты, которые содержат ссылку на удаляемую таблицу, то выполнение оператора DROP TABLE закончится неуспешно. В большинстве коммерческих СУБД допускается применение оператора DROP TABLE без каких-либо параметров.

Изменение определения таблицы (оператор ALTER TABLE)

В процессе работы с таблицей иногда возникает необходимость добавить в таблицу некоторую информацию. Для изменения структуры таблицы используется оператор ALTER TABLE, с помощью которого возможно:

1. добавить в таблицу определение столбца;

2. изменить значение по умолчанию для какого-либо столбца;

3. добавить или удалить первичный ключ таблицы;

4. добавить или удалить новый внешний ключ таблицы;

5. добавить или удалить условие уникальности;

6. добавить или удалить условие проверки.

ALTER TABLE имя_таблицы

ALTER имя_столбца SET DEFAULT значение | DROP DEFAULT

DROP имя_столбца CASCADE | RESTRICT

DROP CONSTRAINT имя_ограничения CASCADE | RESTRICT

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

В стандарте SQL2 формально определение домена реализовано как часть определения базы данных. Согласно этому стандарту, домен является именованной совокупностью значений данных и широко используется в определении базы данных как дополнительный тип данных. Домен создается посредством оператора CREATE DOMAIN на основе базовых типов данных. После создания домена на него можно ссылаться внутри определения таблицы как на тип данных.

Индексы (операторы CREATE/DROP INDEX)

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

Для создания и удаления индексов применяются операторы CREATE INDEX и DROP INDEX, синтаксис которых описан ниже.

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

CREATE [UNIQUE] INDEX имя_индекса ON имя_таблицы (имя_столбца [ASC | DESC],…)

DROP INDEX имя_индекса

Когда СУБД встречает в операторе SQL ссылку на представление, она отыскивает его определение, сохраненное в базе данных. Затем СУБД преобразует пользовательский запрос, ссылающийся на представление, в эквивалентный запрос к исходным таблицам представления, и выполняет этот запрос. Таким образом, СУБД создает иллюзию существования представления в виде отдельной таблицы и в то же время сохраняет целостность исходных таблиц.

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

1. Безопасность. Каждому пользователю можно разрешить доступ к небольшому числу представлений, содержащих только ту информацию, которую ему позволено знать. Таким образом, можно осуществить ограничение доступа пользователей к хранимой информации.

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

3. Простота структуры. С помощью представлений для каждого пользователя можно создать собственную «структуру» базы данных, определив ее как множество доступных пользователю виртуальных таблиц.

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

5. Целостность данных. Если доступ к данным или ввод данных осуществляется с помощью представления, СУБД может автоматически проверять, выполняются ли определенные условия целостности.

Наряду с перечисленными выше преимуществами, представления обладают и двумя существенными недостатками;

1. Производительность. Представление создает лишь видимость существования соответствующей таблицы, и СУБД приходится преобразовывать запрос к представлению в запрос к исходным таблицам. Если представление отображает многотабличный запрос, то простой запрос к представлению становится сложным объединением и на его выполнение может потребоваться много времени.

2. Ограничения на обновление. Когда пользователь пытается обновить строки представления, СУБД должна установить их соответствие строкам исходных таблиц, а также обновить последние. Это возможно только для простых представлений; сложные представления обновлять нельзя, они доступны только для чтения.

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

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

Оператор CREATE VIEW, синтаксис которого изображен ниже, используется для создания представлений. В нем указываются имя представления и запрос, лежащий в его основе. Для успешного создания представления необходимо иметь разрешение на доступ ко всем таблицам, входящим в запрос.

CREATE VIEW имя_представления (имя_столбца,…) AS запрос

При необходимости в операторе CREATE VIEW можно задать имя для каждого столбца создаваемого представления. Если указывается список имен столбцов, то он должен содержать столько элементов, сколько столбцов содержится в запросе. Обратите внимание на то, что задаются только имена. Например, создать представление, содержащее фамилии студентов группы A-98-51.

Источник

Читайте также:  Покажите таблицу роста и веса