Формат команды insert в oracle sql
Оператор INSERT вставляет новые записи в таблицу. При этом значения столбцов могут представлять собой литеральные константы, либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT ; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.
Синтаксис оператора следующий:
Как видно из представленного синтаксиса, список столбцов не является обязательным (об этом говорят квадратные скобки в описании синтаксиса). В том случае, если он отсутствует, список вставляемых значений должен быть полный, то есть обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, эти значения должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. В качестве примера рассмотрим вставку строки в таблицу Product, созданную следующим оператором CREATE TABLE :
Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором:
Если задать список столбцов, то можно изменить «естественный» порядок их следования:
Казалось бы, это совершенно излишняя возможность, которая делает конструкцию только более громоздкой. Однако она становится выигрышной, если столбцы имеют значения по умолчанию. Рассмотрим следующую структуру таблицы:
Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два — NULL, а последний столбец — type — PC). Теперь мы могли бы написать:
В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию — PC. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL , запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL .
Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT :
Поскольку все столбцы имеют значения по умолчанию, для вставки строки со значениями по умолчанию можно было бы написать:
Однако для этого случая предназначена специальная конструкция DEFAULT VALUES (см. синтаксис оператора), с помощью которой вышеприведенный оператор можно переписать в виде
Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK , ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отклонена. Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Product_D все строки из таблицы Product, относящиеся к моделям персональных компьютеров (type = ‘PC’). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:
Использование в подзапросе символа «*» является в данном случае оправданным, так как порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы применить список столбцов либо в операторе INSERT , либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов:
Здесь, также как и ранее, можно указывать не все столбцы, если требуется использовать имеющиеся значения по умолчанию, например:
В данном случае в столбец type таблицы Product_D будет подставлено значение по умолчанию PC для всех вставляемых строк.
Отметим, что при использовании подзапроса, содержащего предикат, будут вставлены только те строки, для которых значение предиката равно TRUE (не UNKNOWN !). Другими словами, если бы столбец type в таблице Product допускал бы NULL -значение, и это значение присутствовало бы в ряде строк, то эти строки не были бы вставлены в таблицу Product_D.
Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании конструктора строки в предложении VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL . Так если нам требуется вставить несколько строк при помощи одного оператора INSERT , можно написать:
Использование UNION ALL предпочтительней UNION даже, если гарантировано отсутствие строк-дубликатов, так как в этом случае не будет выполняться проверка для исключения дубликатов.
Следует отметить, что вставка нескольких кортежей с помощью конструктора строк уже реализована в Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server 2008. С учетом этой возможности, последний запрос можно переписать в виде:
Заметим, что MySQL допускает еще одну нестандартную синтаксическую конструкцию, выполняющую вставку строки в таблицу в стиле оператора UPDATE:
Рассмотренный в начале параграфа пример с помощью этого оператора можно переписать так:
В этом учебном материале вы узнаете, как использовать оператор INSERT в SQL с синтаксисом и примерами.
Описание
SQL оператор INSERT используется для вставки одной или нескольких записей в таблицу. Существует два синтаксиса для оператора INSERT в зависимости от того, вставляете ли вы одну запись или несколько записей.
Синтаксис
Синтаксис SQL оператора INSERT при вставке одной записи в таблицу:
INSERT INTO table(column1, column2, . )
VALUES
(expression1, expression2, . );
Или синтаксис SQL оператора INSERT при вставке нескольких записей в таблицу:
INSERT INTO table(column1, column2, . )
SELECT expression1, expression2, .
FROM source_tables
[WHERE conditions];
Параметры или аргументы
table Таблица, в которую нужно вставить записи. column1 , column2 Это столбцы в table для вставки значений. expression1 , expression2 Эти значения присваиваются столбцам в таблице. Поэтому column1 будет присвоено значение expression1 , column2 будет присвоено значение expression2 и т.д. , source_tables Используется при вставке записей из другой таблицы. Это исходная таблица при выполнении вставки. WHERE conditions Необязательный. Используется при вставке записей из другой таблицы. Это те условия, которые должны быть соблюдены для вставки записей.Примечание
- При вставке записей в таблицу с помощью SQL оператора INSERT, необходимо указать значение для каждого столбца NOT NULL. Вы можете опустить столбец из оператора INSERT, только если столбец допускает значения NULL.
Пример - использование оператора INSERT для вставки одной записи
Самый простой способ использовать оператор INSERT - вставить одну запись в таблицу с помощью ключевого слова VALUES. Давайте рассмотрим пример того, как сделать это в SQL.
В этом примере у нас есть таблица с categories со следующими данными:
| category_id | category_name |
|---|---|
| 25 | Deli |
| 50 | Produce |
| 75 | Bakery |
| 100 | General Merchandise |
| 125 | Technology |
Давайте вставим новую запись в category . Введите следующий SQL оператор:
Будет вставлена 1 запись. Снова выберите данные из таблицы categories :
Вот результаты, которые вы должны получить:
| category_id | category_name |
|---|---|
| 25 | Deli |
| 50 | Produce |
| 75 | Bakery |
| 100 | General Merchandise |
| 125 | Technology |
| 150 | Miscellaneous |
Этот пример вставит одну запись в таблицу categories . Эта новая запись будет иметь category_id 150 и category_name «Miscellaneous».
В этом примере, поскольку вы предоставляете значения для всех столбцов в таблице categories , вы можете опустить имена столбцов и вместо этого написать оператор INSERT, такой как:
Однако это опасно по двум причинам. Во-первых, оператор INSERT выдаст ошибку, если в таблицу категорий будут добавлены дополнительные столбцы. Во-вторых, данные будут вставлены в неправильные столбцы, если порядок столбцов в таблице изменится. Поэтому, как правило, лучше перечислить имена столбцов в операторе INSERT.
Пример - использование оператора INSERT для вставки нескольких записей
Поместив оператор SELECT в оператор INSERT, вы можете быстро выполнить несколько операций вставки. Давайте рассмотрим пример того, как это сделать.
В этом примере у нас есть таблица employees со следующими данными:
| employee_number | first_name | last_name | salary | dept_id |
|---|---|---|---|---|
| 1001 | Justin | Bieber | 62000 | 500 |
| 1002 | Selena | Gomez | 57500 | 500 |
| 1003 | Mila | Kunis | 71000 | 501 |
| 1004 | Tom | Cruise | 42000 | 501 |
И таблица customers со следующими данными:
Теперь давайте вставим некоторую информацию из таблицы employees в таблицу customers :
Ниже перечислены разрешенные в Oracle типы данных столбцов, с которыми приходится работать чаще других:
- NUMBER – Тип данных, используемый для хранения числовых данных. В столбцах этого типа не допускаются дефисы, текст или любая другая нечисловая информация.
- DATE – Тип данных, используемый для хранения информации о датах. Во внутреннем представлении Oracle хранит даты как числа, которые могут быть затем конвертированы в любой формат DATE по вашему желанию. По умолчанию информация в формате DATE представляется в формате DD-MON-YY (например, 25-DEC-79)/
- VARCHAR2 – Этот тип данных используется для хранения текстовых данных. В столбце типа VARCHAR2 могут храниться любые текстовые символы (включая спецсимволы, числа, буквы, дефисы и тому подобное).
- CHAR – Этот тип данных используется для хранения текстовых данных. В столбце типа CHAR могут храниться любые текстовые символы (включая спецсимволы, числа, буквы, дефисы и тому подобное). В этом случае, если записанный текст имеет длину, меньшую указанной в определении переменной, он будет дополнен справа пробелами. Следовательно, фамилия SCOTT, если ее поместить в столбец, определенный как CHAR(10), будет дополнена справа пять пробелами.
Главное различие между столбцами CHAR и VARCHAR2 состоит в том, что размер памяти, требующейся для хранения текстовых данных в столбце CHAR, всегда превышает размер памяти для хранения той же информации в столбце VARCHAR2. Это связано с тем, что столбцы CHAR имеют фиксированную длину и всегда содержат одинаковое количество байтов, в то время как столбцы VARCHAR2 имеют переменную длину и содержат ровно столько байтов, сколько вы предоставили для записи.
В Oracle существуют и типы данных для хранения других типов информации; однако их не так много, как в базах данных других производителей. Например, в Oracle отсутствует тип данных для хранения валют. Значение этого типа рассматриваются как простые числа и как таковые их можно хранить в столбцах типа NUMBER.
Для работы с данными в базе данных Oracle используются SQL-операторы, которые могут включать в себя DDL, DML и другие типы операторов. Для начала давайте вспомним, как выглядят базовые операторы SQL.
Оператор SELECT
Оператор SELECT является наиболее часто используемым оператором SQL (и также называется проекцией — projection). Он позволяет извлекать все или некоторые данные из таблицы на основе определенных критериев.
Проще всего оператор SELECT выглядит, когда применяется для извлечения всех данных таблицы:
Для извлечения только определенных столбцов необходимо указывать имена этих столбцов после ключевого слова SELECT , например:
При желании извлечь только первых десять строк таблицы можно использовать оператор SELECT следующим образом:
Если требуется узнать только о том, какое количество строк содержится в таблице, оператор SELECT применяется так:
Если в таблице присутствуют дубликаты, можно использовать в операторе SELECT конструкцию DISTINCT для исключения дублированных значений, как показано ниже:
С помощью необязательной конструкции WHERE в операторе SELECT можно задавать различные условия и тем самым обеспечивать возврат только определенных строк. В таблице А.1 перечислены некоторые наиболее типичные условия, которые можно указывать в конструкции WHERE .
| Символ | Условие |
| = | Равно |
| > | Больше |
| < | Меньше |
| <= | Меньше или равно |
| >= | Больше или равно |
| <> или ! | Не равно |
Ниже приведено несколько примеров применения конструкции WHERE :
Условие LIKE
Условие LIKE позволяет ограничивать количество возвращаемых оператором SELECT строк за счет применения операции сопоставления с образцом. Ниже приведен пример использования этого условия:
Образец, с которым должно выполняться сопоставление, должен быть заключен в одинарные кавычки ( '' ). В приведенном выше примере знак процента ( % ) обозначает, что за буквами Fa может идти любая строка символов. То есть знак процента выступает в роли группового символа, способного замещать один или более символов, и, по сути, делает то же самое, что во многих операционных системах делает символ звездочки ( * ). Важно обратить внимание на то, что один символ подчеркивания ( _ ) тоже может выступать групповым символом, но в отличие от знака процента способен замещать только один символ.
Оператор INSERT
Оператор INSERT позволяет добавлять в таблицу новые данные, в том числе и дублированные в случае отсутствия требований уникальности со стороны первичного ключа или индекса. В общем, синтаксис оператора INSERT выглядит следующим образом:
Ниже приведен пример применения оператора INSERT :
В этом примере имена столбцов были указаны потому, что заполнить данным требовалось только некоторые из столбцов вставлявшейся строки. Остальные столбцы были оставлены пустыми, что вполне приемлемо при условии, что ни один из них не был определен как не допускающий нулевых значений ( NOT NULL ).
При желании вставить значения во все столбцы таблицы можно использовать более простой оператор INSERT , подобный тому, что показан ниже:
Если требуется вставить все столбцы из одной таблицы в другую, можно использовать такой оператор INSERT :
В случае если окажется, что таблицы b не существует, можно использовать оператор CREATE TABLE имя_таблицы AS SELECT * FROM (CTAS) , как показано ниже:
Оператор DELETE
Оператор DELETE служит для удаления строк из таблицы. Его синтаксис, в общем случае, выглядит так:
Например, для удаления пользователя Fay из таблицы employees потребуется использовать такой оператор DELETE :
Если ограничивающее условие WHERE не указано, оператор DELETE удалит все строки из таблицы:
Удалять все строки из таблицы можно также и с помощью команды TRUNCATE , но отменять последствия этой команды нельзя, а отменять действие оператора DELETE можно, воспользовавшись оператором ROLLBACK :
Оператор UPDATE
Оператор UPDATE применяется для изменения значения (или значений) одного или нескольких столбцов в строке (или строках) внутри таблицы. Выражение, используемое для установки или изменения значения столбца, может представлять собой константную, арифметическую или строковую операцию или же результат выполнения оператора SELECT .
В общем, синтаксис оператора UPDATE выглядит следующим образом (элементы в квадратных скобках являются необязательными):
При желании изменить или модифицировать значения столбца во всех строках таблицы можно использовать оператор UPDATE без конструкции WHERE :
Для модификации только определенных строк нужно обязательно задавать в операторе UPDATE конструкцию WHERE :
Фильтрация данных
За счет использования в операторе SELECT , INSERT , DELETE или UPDATE конструкции WHERE можно еще также и фильтровать данные, т.е. ограничивать количество строк, в отношении которых должен выполняться данный оператор. Ниже приведен простой пример:
Сортировка результатов запроса
Довольно часто бывает необходимо сортировать результаты запроса в каком-нибудь порядке. Конструкция ORDER BY позволяет сортировать данные на основе значения одного или нескольких столбцов. Вместе с ней можно указывать желаемый порядок сортировки (по возрастанию или убыванию), использовать для сортировки псевдонимы столбцов, а также выполнять сортировку по нескольким столбцам. Ниже приведен простой пример:
Изменение порядка сортировки
По умолчанию конструкция ORDER BY предусматривает выполнение сортировки в порядке возрастания. При желании, чтобы сортировка выполнялась по убыванию, нужно указать ключевое слово DESC :
Выполнение сортировки по нескольким столбцам
При желании результаты можно сортировать на основе значений более чем одного столбца. Например, ниже приведен запрос, предусматривающий выполнение сортировки на основании значений двух столбцов — salary и dept :
Читайте также: