Заполнение таблицы данными — это процесс ввода новых данных в таблицу либо вручную с помощью отдельных команд, либо автоматически с помощью программ или каким-либо иным способом.
На этом уроке вы ознакомитесь с той частью SQL, которая называется языком манипуляций данными (Data Manipulation Language — DML). DML является частью SQL, используемой при изменении данных и таблиц в реляционных базах данных
Основными на этом уроке будут следующие темы.
• Обзор языка манипуляций данными
• Инструкции по манипуляции данными в таблицах
• Концепции, лежащие в основе размещения данных в таблицах
• Удаление данных из таблиц
• Изменение данных в таблицах
Обзор возможностей манипуляции данными
Язык манипуляций данными (DML) является частью SQL, обеспечивающей пользователю базы данных возможность вносить реальные изменения в данные реляционной базы данных. С помощью DML пользователь может пополнять таблицы новыми данными, обновлять уже имеющиеся данные и удалять их из таблиц. С помощью команд DML можно также выполнять и некоторые типы запросов.
В SQL имеется три основных команды DML.
INSERT UPDATE DELETE
Команда SELECT, которую тоже можно использовать с командами DML, будет обсуждаться подробно в ходе урока 7, "Знакомство с запросами".
Заполнение таблиц новыми данными
Заполнение таблицы данными — это процесс ввода новых данных в таблицу либо вручную с помощью отдельных команд, либо автоматически с помощью программ или каким-либо иным способом.
То, какие данные и в каком количестве можно будет при этом вводить в таблицу, зависит от многих факторов, основными из которых являются ограничения, заданные при определении таблицы, физические размеры таблицы, типы данных ее столбцов, ширина столбцов, требования целостности в виде ключей и внешних ключей. В следующих разделах мы обсудим особенности процесса ввода данных в таблицы, рассмотрев ряд соответствующих рекомендаций и предостережений.
Не забывайте, что операторы SQL могут использовать как символы нижнего регистра, так и символы верхнего. Вследствие способа хранения операторов в базе данных, они от выбора регистра букв не зависят. В следующих примерах буквы нижнего и верхнего регистров используются только для того, чтобы показать, что это не влияет на результат.
Чтобы ввести новые данные в таблицу, используйте оператор INSERT. Как ясно из показанного ниже его базового вида, оператор INSERT имеет несколько опций.
insert into имя схемы.имя таблицы
VALUES ('значение!', 'значение2', [ NULL ] );
Согласно представленному здесь синтаксису оператора INSERT, в список VALUES вы должны поместить значения для всех столбцов соответствующей таблицы. Значения в списке разделяются запятыми. Символьные значения и значения дат должны быть заключены в кавычки. Для числовых значений и пустых значений, задаваемых ключевым словом NULL, кавычки не нужны. Должны быть указаны значения для всех столбцов таблицы.
В следующем примере новая запись вводится в таблицу PRODUCTS_TBL.
Структура таблицы:
products_tbl
Имя столбца Null? Тип данных
PROD_ID NOT NULL VARCHAR2(10)
PROD_DESC NOT NULL VARCHAR2(25)
COST NOT NULL NUMBER(6,2)
Пример использования оператора INSERT:
INSERT INTO PRODUCTSJTBL
VALUES ('7725','КОЖАНЫЕ ПЕРЧАТКИ',24.99);
1 строка создана.
В этом примере вводятся три значения в таблицу с тремя столбцами. Порядок вводимых значений соответствует порядку столбцов в таблице. Первые два значения заключены в кавычки, поскольку тип данных соответствующих столбцов является символьным. Третье значение, ассоциированное со столбцом COST, имеет числовой тип данных и поэтому не требует кавычек, хотя их можно использовать и в этом случае.
|
Имя столбца |
Null? |
Тип данных |
||
|
PROD ID PROD DESC COST |
NOT NULL NOT NULL
NOT NULL |
VARCHAR2 (10) VARCHAR2 (25)
NUMBER (6, 2) |
В данном примере не было указано имя схемы или владельца таблицы. Имя схемы не требуется, если вы вошли в базу данных как пользователь, являющийся владельцем соответствующей таблицы.
Ввод данных в определенные столбцы таблицы
Имеется возможность ввести данные не во все, а только в определенные столбцы, например, если нужно ввести всю информацию о служащем, кроме номера его пейджера. В этом случае в операторе INSERT вместе со списком значений VALUES нужно указать и список соответствующих им столбцов.
INSERT INTO EMPLOYEE_TBL
(EMP_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS, CITY, STATE, ZIP, PHONE) VALUES
('123456789','SMITH', 'JOHN', 'JAY', •12 BEACON ST', •INDIANAPOLIS', 'IN', '46222', '3172996868');
1 строка создана
Синтаксис оператора для ввода значений в избранные столбцы таблицы следующий.
INSERT INTO ИМЯ_СХЕМЫ.ИМЯ_ТАБЛИЦЫ ('СТОЛБЕЦ!', 'СТОЛБЕЦ2')
VALUES ('ЗНАЧЕНИЕ!', 'ЗНАЧЕНИЕ2');
В следующем примере в таблицу ORDERS_TBL вводятся значения только для некоторых столбцов.
Структура таблицы:
ORDERSJTBL
Имя столбца Null? Тип данных
ORD_NUM NOT NULL VARCHAR2(10)
CUST_ID NOT NULL VARCHAR2(10)
PROD_ID NOT NULL VARCHAR2(10)
QTY NOT NULL NUMBER(4)
ORD_DATE DATE
Пример использования оператора INSERT:
insert into orders_tbl (ord_num,cust_id,prod_id,qty)
VALUES ('23A16',409','7725',2);
1 строка создана.
Здесь после имени таблицы в скобках указан список столбцов. Это список всех столбцов, в которые вводятся данные. В данном случае в списке нет только столбца ORD_DATE. Из определения таблицы видно, что столбец ORD_DATE не требует обязательного наличия данных в каждой строке, поскольку в определении таблицы для этого столбца не указано NOT NULL. NOT NULL означает, что пустые значения для столбца не допускаются. Порядок в списке значений должен соответствовать порядку ввода значений в таблицу, задаваемому списком столбцов.
Список столбцов в операторе INSERT не обязательно должен соответствовать списку столбцов в определении соответствующей таблицы, а вот список вводимых значений должен обязательно соответствовать списку избранных столбцов
В таблицу можно вводить данные, полученные в результате запроса к другой таблице, воспользовавшись комбинацией операторов INSERT и SELECT. Коротко говоря, запрос — это обращение к базе данных, имеющее целью получение данных. О запросах будет идти речь в ходе урока 7. Запрос можно сравнить с вопросом пользователя к базе данных, а возвращенные данные — с полученным ответом. Если скомбинировать операторы INSERT и SELECT, имеется возможность ввести в таблицу данные, полученные в результате запроса.
Синтаксис оператора для ввода данных из одной таблицы в другую следующий:
insert into имя_схемы.имя_таблицы [('столбец!', 'столбец2')] select [*|('столбец!', 'столбец2')] from имя_таблицы
[where условия] ;
Здесь вы видите три новых ключевых слова SELECT, FROM и WHERE. SELECT является основной командой для построения запросов в SQL. С помощью FROM в запросе указываются имена таблиц, в которых необходимо отыскать данные. С помощью WHERE в запросах задаются условия, определяющие суть запроса. Таким условием может быть, например, WHERE NAME = 'SMITH'. Использование этих трех ключевых слов подробно обсуждается в ходе уроков 7 и 8.
Условие — это способ задания критериев отбора данных, осуществляемого оператором SQL.
В следующем примере используется простой запрос, чтобы увидеть все данные таблицы PRODUCTS_TBL. Здесь SELECT * говорит серверу базы данных, что необходимо получить информацию из всех столбцов таблицы. А отсутствие ключевого слова WHERE означает, что необходимо показать все записи таблицы.
select * from products_tbl;
PROD_ID PROD_DESC COST
11235 КОСТЮМ ВЕДЬМЫ 29.99
222 ПЛАСТИКОВЫЕ ТЫКВЫ 7.75
13 ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ 1.1
90 ФОНАРИ 14.5
15 КОСТЮМЫ В АССОРТИМЕНТЕ 10
9 СЛАДКАЯ КУКУРУЗА 1.35
6 ТЫКВЕННЫЕ КОНФЕТЫ 1.45
87 ПЛАСТИКОВЫЕ ПАУКИ 1.05
119 МАСКИ В АССОРТИМЕНТЕ 4.95
1234 ЦЕПОЧКА ДЛЯ КЛЮЧЕЙ 5.95
2345 ПОЛОЧКА ИЗ ДУБА 59.99
11 строк выбраны.
Теперь данные, полученные в результате этого запроса, введем в таблицу PRODUCTS TMP. Вы увидите, что в этой временной таблице будут созданы 11 строк.
INSERT INTO PRODUCTSJTMP
SELECT * FROM PRODOCTS_TBL;
11 строк создано,
Результат следующего запроса показывает все данные только что созданной таблицы PRODUCTSJTMP.
SELECT * FROM PRODUCTSJTMP;
PROD_ID PROD_DESC COST
Ц235 КОСТЮМ ВЕДЬМЫ 29.99
222 ПЛАСТИКОВЫЕ ТЫКВЫ 7.75
13 ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ 1.1
90 ФОНАРИ 14-5
15 КОСТЮМЫ В АССОРТИМЕНТЕ 10
9 СЛАДКАЯ КУКУРУЗА 1.35
6 ТЫКВЕННЫЕ КОНФЕТЫ 1-45
87 ПЛАСТИКОВЫЕ ПАУКИ 1.05
119 МАСКИ В АССОРТИМЕНТЕ 4.95
1234 ЦЕПОЧКА ДПЯ КЛЮЧЕЙ 5.95
2345 ПОЛОЧКА ИЗ ДУБА 59.99
11 строк выбраны.
Ввести значение NULL в таблицу просто. Это бывает нужно, в частности, когда значение соответствующего столбца не известно. Например, не каждый человек имеет пейджер, и было бы некорректно вводить для этих людей неверные номера пейджера — не говоря уж о напрасном расходовании дискового пространства. Значение NULL можно ввести в столбец с помощью ключевого слова NULL.
Синтаксис оператора для ввода значения NULL следующий.
insert into имя_схемы.имя_таблицы
values ('значение!', NULL, 'значениеЗ'};
В результате в соответствующий столбец таблицы будет введено значение NULL. Столбец, в который вводится NULL, не будет содержать данных. В рамках вышеприведенного синтаксиса значение NULL будет введено на место второго столбца.
Рассмотрим следующие два примера.
INSERT INTO ORDERS_TBL
(ORD_NUM,CUST_ID,PROD_ID,QTY,ORD_DATE) VALUES ('23A16','109','7725',2,NULL);
1 строка создана.
Здесь в списке столбцов перечислены все столбцы таблицы ORDERS_TBL. В столбец ORD_DATE вводится значение NULL, означающее, что дата заказа в данный момент либо не известна, либо не доступна.
INSERT INTO ORDERSJTBL
VALUES ('23A16',409','7725',2, 1');
1 строка создана.
Использованный в этом примере оператор отличается от оператора из первого примера, но результаты их выполнения одинаковы Обратите внимание на то, что здесь, во-первых, отсутствует список столбцов - он не обязателен, если данные'вводятся во все столбцы таблицы. Во-вторых, вместо ввода в последний столбец значения NULL, вводятся ' ' (два идущие подряд знака кавычек), что тоже символизирует значение NULL (ввиду отсутствия чего бы то ни было между ними).
Обновление уже имеющихся данных
Уже существующие в таблице данные можно изменить с помощью команды UPDATE. Команда UPDATE не добавляет новых записей в таблицу и не удаляет их, а только дает возможность изменить данные. С помощью одной такой команды можно изменить данные только одной таблицы, но одновременно можно менять данные нескольких столбцов. Одним таким оператором можно изменить и одну строку данных и целый набор строк.
Обновление значений одного столбца
В своей простейшей форме оператор UPDATE изменяет один столбец таблицы. При изменении одного столбца можно изменить и только одну запись, и сразу несколько Синтаксис оператора для изменения данных в одном столбце следующий
update имя_таблицы
set имя_столбца = 'значение'
[where условие];
В следующем примере значение столбца QTY таблицы ORDERS_TBL устанавливается равным 1 для записи с ORD_NUM, равным '23А16' (последнее специфицируется с помощью ключевого слова WHERE).
UPDATE ORDERS_TBL
SET QTY
WHERE ORD_NUM = '23A16';
I строка обновлена.
Следующий пример отличается от предыдущего только отсутствием параметра, заданного ключевым словом WHERE.
UPDATE ORDERSJTBL SET QTY = 1;
11 строк обновлены.
Как видите, здесь было обновлено 11 строк. В данном случае значение столбца QTY было установлено равным 1 для всех строк таблицы ORDERS_TBL. Вы действительно хотели получить такой результат? В отдельных случаях это может быть и так, но на самом деле оператор UPDATE без ключевого слова WHERE используется исключительно редко.
При использовании оператора UPDATE без ключевого слова WHERE нужно быть исключительно внимательным При отсутствии заданных ключевым словом WHERE условий данные в соответствующем столбце будут обновлены для всех строк данных
Обновление нескольких столбцов в одной или нескольких записях
Теперь давайте разберемся с тем, как обновить с помощью операгора UPDATE несколько столбцов сразу. Рассмотрим синтаксис подходящего оператора.
update имя_таблицы
set столбец! = 'значение'
[, столбецЗ = 'значение']
[, столбецЗ = 'значение'] [where условие];
Обратите внимание на использование ключевого слова SET' оно одно, а описаний столбцов — несколько. Описания столбцов разделяются запятыми К этому моменту вы, должно быть, уже почувствовали логику SQL. В операторах SQL запятая обычно используется для разделения различно! о типа аргументов.
UPDATE ORDERSJTBL
SET QTY = 1,
CUST_ID = '221' WHERE ORD_NUM = '23A16';
1 строка обновлена.
Здесь запятая использована для разделения описаний, вносимых в столбцы изменений. Опять же, использовать задающее условия выражение с ключевым словом WHERE не обязательно, но обычно необходимо.
Ключевое слово SET в операторе UPDATE используется только один раз Если необходимо обновить несколько столбцов, они разделяются запятыми.
Для удаления данных из таблиц используется команда DELETE. Команда DELETE предназначена не для того, чтобы удалять значения отдельных столбцов, а для того, чтобы удалять целые записи. Оператор DELETE следует применять с осторожностью — слишком уж безотказно он работает.
Чтобы удалить одну или несколько записей из таблицы, используйте следующий синтаксис оператора DELETE.
delete from имя_схемы.имя_таблицы
[where условие];
DELETE FROM ORDERS_TBL
WHERE ORD_NUM = '23A16';
1 строка удалена.
Здесь следует обратить внимание на выражение с ключевым словом WHERE. При удалении строк из таблицы это выражение представляет собой очень важную часть оператора DELETE. Оператор DELETE без ключевого слова WHERE может понадобиться вам крайне редко. Если вы им воспользуетесь, результат будет подобен следующему:
DELETE FROM ORDERS_TBL; 11 строк удалены.
Если ключевое слово WHERE в операторе DELETE опущено, будут удалены все строки таблицы. Поэтому примите за правило всегда использовать ключевое слово WHERE в операторе DELETE.
Перед тем, как использовать операторы DELETE и UPDATE по отношению к таблицам нашей базы данных, было бы неплохо поучиться использовать эти операторы с той временной таблицей, которую мы с вами создали ранее на основе данных одной из таблиц базы данных.
Вы ознакомились с тремя основными командами языка манипуляций данными (DML) — операторами INSERT, UPDATE и DELETE. Вы смогли убедиться, что это достаточно мощная часть SQL, дающая пользователю базы данных возможность пополнять таблицы новыми данными, обновлять или удалять уже имеющиеся данные.
Очень важные уроки общения с базами данных можно получить, если пренебречь внимательным отношением к ключевому слову WHERE. С помощью ключевого слова WHERE в операторах SQL задаются условия отбора, в частности, в операторах UPDATE и DELETE с его помощью определяются строки данных, которые будут обрабатываться в ходе транзакции. При отсутствии ключевого слова WHERE будут обработаны все строки, что для базы данных может оказаться разрушительным. Защитите свои данные, и будьте внимательны при работе с данными.
После всех вышеприведенных предостережений относительно операторов UPDATE и DELETE я вообще опасаюсь их использовать. Если я вдруг изменю все данные в таблице из-за пропущенного ключевого слова WHERE, есть ли какая-либо возможность отменить эти изменения?
Для таких серьезных опасений оснований нет, поскольку у вас имеется не слишком много возможностей для непоправимых изменений в базе данных, хотя для восстановления данных может потребоваться немалое время. На следующем уроке мы рассмотрим вопросы управления транзакциями, когда операции изменения данных можно либо принять как окончательные, либо отменить.
Является ли использование оператора INSERT единственным способом ввода данных в таблицу?
Нет, просто оператор INSERT определяется стандартом ANSI. Различные реализации SQL предлагают свои средства для ввода данных в таблицы. Например, в Oracle имеется утилита SQL*Loader. Многие реализации SQL для ввода данных имеют утилиту под названием IMPORT. На рынке имеется множество книг, в которых эти утилиты описываются в деталях.
Задания практических занятий разделены на тесты и упражнения. Тесты предназначены для проверки общего уровня понимания рассмотренного материала. Упражнения дают возможность применить на практике идеи, обсуждавшиеся в ходе текущего урока, в комбинации с идеями из предыдущих уроков. Мы рекомендуем ответить на тестовые вопросы и выполнить упражнения прежде, чем продолжать дальнейшее чтение книги. Ответы можно проверить по Приложению Б, "Ответы".
1. Используйте таблицу EMPLOYEEJTBL с заданной ниже структурой.
Что случится, если выполнить следующие операторы?
a insert into employee_tbl '('JACKSON', 'STEVE', '313546078', '3178523443');
б insert into employee_tbl values
('JACKSON1, 'STEVE', '313546078', '3178523443');
В insert into employee_tbl values
('MILLER', 'DANIEL', '230980012', NULL);
r insert into employee_tbl values
('TAYLOR', NULL, '445761212', '3179221331');
д. delete from employee_tbl;
e. delete from employee_tbl "where last_name = 'SMITH';
X. delete from employee_tbl where last_name = 'SMITH' and first_name = 'JOHN';
з. update employee_tbl
set last_name = 'CONRAD';
и. update employee_tbl
set last_name = 'CONRAD'
where last_name = 'SMITH';
K. update employee_tbl
set last_name = 'CONRAD', first_name = 'LARRY';
Л. update employee_tbl
set last_name = 'CONRAD' first_name = 'LARRY' where ssn = '313546078';
1. Ознакомьтесь с Приложением Г, "Операторы INSERT для данных в примерах книги". Выполните операторы INSERT для заполнения данными тех таблиц, которые были созданы в результате выполнения задания упражнения 1 урока 3. После этого вам будет легче разбираться в примерах и упражнениях книги.
2. Используйте таблицу EMPLOYEE_TBL с заданной ниже структурой.
Используя операторы DML, выполните следующее.
а. Измените SSN для служащего по имени Billy Pierce на 310239857.
б. Добавьте информацию о служащем по имени Ben Moore, тел. 317-5649880, SSN равен 313456789.
в Служащий по имени John Smith уволился, удалите соответствующую запись.