Лабораторная работа № 3
Вставка, удаление и обновление данных
Теоретические сведения
Рассмотрим следующие вопросы:
вставка данных с помощью оператора INSERT;
удаление данных операторами DELETE и TRUNCATE;
обновление данных с помощью оператора UPDATE.
После создания БД и таблиц перед разработчиком встает задача заполнения таблиц данными. В реляционных БД традиционно применяют три подхода:
однострочный оператор insert – добавляет в таблицу новую запись;
многострочный оператор insert – добавляет в таблицу несколько записей;
пакетная загрузка LOAD DATA INFILE – добавление данных из файла.
Вставка данных с помощью оператора INSERT. Однострочный оператор insert может использоваться в нескольких формах. Упрощенный синтаксис первой формы:
insert [IGNORE] [INTO] имя_таблицы [(имя_столбца, ... )]
VALUES (выражение, ... );
Оператор вставляет новую запись в таблицу имя_таблицы. Значения полей записи перечисляются в списке (выражение, ... ). Порядок следования столбцов задается списком (имя_столбца, ... ). Список столбцов (имя_столбца, ... ) позволяет менять порядок следования столбцов при добавлении.
Первичный ключ таблицы является уникальным, и попытка добавить уже существующее значение приведет к ошибке. Чтобы новые записи с дублирующим ключом отбрасывались без генерации ошибки, следует добавить после оператора insert ключевое слово IGNORE.
Другая форма оператора insert предполагает использование слова set:
insert [IGNORE] [INTO] имя_таблицы
SET имя_столбца1 = выражение1, имя_столбца2 = выражение2, ... ;
Оператор заносит в таблицу имя_таблицы новую запись, столбец имя_столбца в которой получает значение выражение.
Многострочный оператор INSERT совпадает по форме с однострочным оператором, но после ключевого слова values добавляется через запятую несколько списков (выражение, ... ).
Практические примеры использования оператора insert для заполнения учебной БД book см. ниже, в пункте «Пример выполнения работы».
Удаление данных. Для удаления записей из таблиц предусмотрены:
оператор DELETE;
оператор TRUNCATE TABLE.
Оператор DELETE имеет следующий синтаксис:
DELETE FROM имя_таблицы
[Where условие]
[ORDER BY имя_поля]
[LIMIT число_строк];
Оператор удаляет из таблицы имя_таблицы записи, удовлетворяющие условию. В следующем примере из таблицы catalogs удаляются записи, имеющие значение первичного ключа catalog_id больше двух.
Если в операторе отсутствует условие where, удаляются все записи таблицы.
Ограничение limit позволяет задать максимальное число записей, которые могут быть удалены. Следующий запрос удаляет все записи таблицы orders, но не более 3 записей.
Конструкция order by обычно применяется вместе с ключевым словом limit. Например, если необходимо удалить 20 первых записей таблицы, то производится сортировка по полю типа datetime – тогда в первую очередь будут удалены самые старые записи.
Оператор truncate table полностью очищает таблицу и не допускает условного удаления. Он аналогичен оператору delete без условия where и ограничения limit. Удаление происходит гораздо быстрее, т. к. осуществляется не перебор записей, а полное очищение таблицы.
Обновление данных. Обновление данных (изменение значений полей в существующих записях) обеспечивают:
оператор Update;
оператор Replace.
Оператор UPDATE позволяет обновлять отдельные поля в существующих записях. Имеет следующий синтаксис
Update [IGNORE] имя_таблицы
SET имя_столбца1= выражение1 [, имя_столбца2 = выражение2 … ]
[WHERE условие]
[ORDER BY имя_поля ]
[LIMIT число_строк] ;
После ключевого слова update указывается таблица, которая изменяется. В предложении set указывается, какие столбцы обновляются и устанавливаются их новые значения. Необязательное условие WHERE позволяет задать критерий отбора строк (обновляться будут только строки, удовлетворяющие условию).
Если указывается необязательное ключевое слово ignore, то команда обновления не будет прервана, даже если при обновлении возникнет ошибка дублирования ключей. Строки, породившие конфликтные ситуации, обновлены не будут.
Запрос, изменяющий в таблице catalogs «Сети» на «Компьютерные сети».
Обновлять можно всю таблицу. Пусть требуется уменьшить на 5 % цену на все книги. Для этого следует старую цену в рублях умножить на 0,95.
Инструкции limit и order by позволяют ограничить число изменяемых записей. При этом за один запрос можно обновить несколько столбцов таблицы. Например, необходимо в таблице books для десяти самых дешевых товарных позиций уменьшить количество книг на складе на единицу, а цену – на 5 %.
Оператор REPLACE работает как оператор insert, за исключением того, что старая запись с тем же значением индекса unique или primary key перед внесением новой будет удалена. Если не используются индексы unique или primary key, то применение оператора replace не имеет смысла.
Синтаксис оператора REPLACE аналогичен синтаксису оператора insert:
REPLACE [INTO] имя_таблицы [(имя_столбца, ... )]
VALUES (выражение, ... )
В таблицу вставляются значения, определяемые в списке после ключевого слова VALUES. Задать порядок столбцов можно при помощи необязательного списка, следующего за именем таблицы. Как и оператор Insert, оператор replace допускает многострочный формат.
Практическая работа
При выполнении лабораторной работы необходимо для заданной предметной области средствами MySQL:
заполнить согласованными данными таблицы БД;
при необходимости исправить введенную информацию;
составить отчет по лабораторной работе.
Пример выполнения работы
Операторы заполнения БД book имеют следующий вид.
USE book;
SET CHARACTER SET cp1251;
DELETE FROM catalogs;
INSERT INTO catalogs VALUES (1,'Программирование');
INSERT INTO catalogs VALUES (2,'Интернет');
INSERT INTO catalogs VALUES (3,'Базы данных');
INSERT INTO catalogs VALUES (4,'Сети');
INSERT INTO catalogs VALUES (5,'Мультимедиа');
DELETE FROM books;
INSERT INTO books VALUES (1,'JavaScript в кармане','Рева О.Н.', 2008, 42.00, 10, 1);
INSERT INTO books VALUES (2,'Visual FoxPro 9.0','Клепинин В.Б.', 2007, 660.00, 2, 1);
INSERT INTO books VALUES (3,'C++ Как он есть','Тимофеев В.В.',2009, 218.00, 4, 1);
INSERT INTO books VALUES (4,'Создание приложений с помощью C#','Фаронов В.В.', 2008, 169.00, 1, 1);
INSERT INTO books VALUES (5,'Delphi. Народные советы','Шкрыль А.А.',2007,243.00,6,1);
INSERT INTO books VALUES (6,'Delphi. Полное руководство','Сухарев М.',2008,500.00,6,1);
INSERT INTO books VALUES (7,'Профессиональное программирование на PHP', 'Шлосснейгл Дж.', 2006, 309.00, 5, 1);
INSERT INTO books VALUES (8,'Совершенный код','Макконнелл С.', 2007, 771.00, 1, 1);
INSERT INTO books VALUES (9,'Практика программирования','Керниган Б.', 2004, 214.00, 12, 1);
INSERT INTO books VALUES (10,'Принципы маршрутизации в Internet','Хелеби С.', 2001, 428.00, 4, 2);
INSERT INTO books VALUES (11,'Поиск в Internet','Гусев В.С.',2004,107.00,2,2);
INSERT INTO books VALUES (12,'Web-конструирование','Дуванов А.А.', 2003, 177.00, 6, 2);
INSERT INTO books VALUES (13,'Самоучитель Интернет','Константинов Ю.П.', 2009, 121.00, 4, 2);
INSERT INTO books VALUES (14,'Популярные интернет-браузеры','Маринин С.А.', 2007, 82.00, 6, 2);
INSERT INTO books VALUES (15,'Общение в Интернете','Экслер А.', 2006, 85.00, 5, 2);
INSERT INTO books VALUES (16,'Базы данных','Малыхина М.П.', 2006, 326.00, 2, 3);
INSERT INTO books VALUES (17,'Базы данных. Разработка приложений','Рудикова Л.В.', 2006, 189.00, 6, 3);
INSERT INTO books VALUES (18,'Раскрытие тайн SQL','Оппель Э.', 2007, 200.00, 3, 3);
INSERT INTO books VALUES (19,'Практикум по Access','Золотова С.И.', 2007, 87.00, 6, 3);
INSERT INTO books VALUES (20,'Компьютерные сети','Танненбаум Э.', 2007, 630.00, 6, 4);
INSERT INTO books VALUES (21,'Сети. Поиск неисправностей','Бигелоу С.', 2005, 434.00, 4, 4);
INSERT INTO books VALUES (22,'Безопасность сетей','Брегг Р.', 2006, 462.00, 5, 4);
INSERT INTO books VALUES (23,'Анализ и диагностика компьютерных сетей', 'Хогдал Дж.', 2001, 344.00, 3, 4);
INSERT INTO books VALUES (24,'Локальные вычислительные сети', 'Епанешников А.' , 2005, 82.00, 8, 4);
INSERT INTO books VALUES (25,'Цифровая фотография','Надеждин Н.', 2004, 149.00, 20,5);
INSERT INTO books VALUES (26,'Музыкальный компьютер для гитариста', 'Петелин Р.Ю.', 2004, 217.00, 15, 5);
INSERT INTO books VALUES (27,'Видео на ПК','Федорова А.',2003,231.00,10,5);
INSERT INTO books VALUES (28,'Мультипликация во Flash','Киркпатрик Г.', 2006, 211.00, 20, 5);
INSERT INTO books VALUES (29,'Запись CD и DVD','Гультяев А.К.', 2003, 167.00, 12, 5);
INSERT INTO books VALUES (30,'Запись и обработка звука на компьютере', 'Лоянич А.А.', 2008, 51.00, 8, 5);
DELETE FROM users;
INSERT INTO users VALUES (1,'Александр','Валерьевич','Иванов','58-98-78', 'ivanov@email.ru', 'active');
INSERT INTO users VALUES (2,'Сергей','Иванович','Лосев','90-57-77', 'losev@email.ru', 'passive');
INSERT INTO users VALUES (3,'Игорь','Николаевич','Симонов','95-66-61', 'simonov@email.ru', 'active');
INSERT INTO users VALUES (4,'Максим','Петрович','Кузнецов',NULL, 'kuznetsov@email.ru', 'active');
INSERT INTO users VALUES (5,'Анатолий','Юрьевич','Петров', NULL, NULL, 'lock');
INSERT INTO users VALUES (6,'Александр','Александрович','Корнеев','89-78-36', 'korneev@email.ru', 'gold');
DELETE FROM orders;
INSERT INTO orders VALUES (1,3,8,'2009-01-04 10:39:38',1);
INSERT INTO orders VALUES (2,6,10,'2009-02-10 09:40:29',2);
INSERT INTO orders VALUES (3,1,20,'2009-02-18 13:41:05',4);
INSERT INTO orders VALUES (4,4,20,'2009-03-10 18:20:00',1);
INSERT INTO orders VALUES (5,3,20,'2009-03-17 19:15:36',1);
|
