лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
UNION ALL предпочтительней UNION даже, если гарантировано отсутствие строк-дубликатов, так как в этом случае не будет выполняться проверка для исключения дубликатов. Следует отметить, что вставка нескольких кортежей с помощью конструктора строк уже реализована в SQL Server 2008. С учетом этой возможности, последний запрос можно переписать в виде: 1. INSERT INTO Product_D VALUES 2. ( 'B' , 1158 , 'PC' ) , 3. ( 'C' , 2190 , 'Laptop' ) , 4. ( 'D' , 3219 , 'Printer' ) ; Заметим, что MySQL допускает еще одну нестандартную синтаксическую конструкцию, выполняющую вставку строки в таблицу в стиле оператора UPDATE : 1. INSERT [ INTO ] <имя таблицы> 2. SET { <имя столбца>= { <выражение> | DEFAULT }} , ... Рассмотренный в начале параграфа пример с помощью этого оператора можно переписать так: 1. INSERT INTO Product 2. SET maker = 'B' , 3. model = 1157 , 4. type = 'PC' ; Вставка строк в таблицу, содержащую автоинкрементируемое поле Многие коммерческие продукты допускают использование автоинкрементируемых столбцов в таблицах, то есть столбцов, значение которых формируется автоматически при добавлении новых записей. Такие столбцы широко используются в качестве первичных ключей таблицы, так как они автоматически обеспечивают уникальность за счет того, что генерируемые значения не повторяются. Типичным примером столбца такого типа является последовательный счетчик, который при вставке строки генерирует значение на единицу большее предыдущего значения (значения, полученного при вставке предыдущей строки). Ниже приводится пример создания таблицы Printer_Inc с автоинкрементируемым столбцом (code) в MS SQL Server. 1. CREATE TABLE Printer_Inc 2. ( 3. code int IDENTITY ( 1 , 1 ) PRIMARY KEY , 4. model varchar ( 4 ) NOT NULL , 5. color char ( 1 ) NOT NULL , 6. type varchar ( 6 ) NOT NULL , 7. price float NOT NULL 8. ) ; Автоинкрементируемое поле определяется посредством конструкции IDENTITY (1, 1). При этом первый параметр свойства IDENTITY (1) определяет, с какого значения начнется отсчет, а второй, — какой шаг будет использоваться для приращения значения. Таким образом, в нашем примере первая вставленная запись будет иметь в столбце code значение 1, вторая — 2 и т. д. Поскольку в поле code значение формируется автоматически, оператор 1. INSERT INTO Printer_Inc 2. VALUES ( 15 , 3111 , 'y' , 'laser' , 599 ) ; приведет к ошибке, даже если в таблице нет строки со значением в поле code, равным 15. Поэтому для вставки строки в таблицу просто не будем указывать это поле точно так же, как и в случае использования значения по умолчанию, то есть 1. INSERT INTO Printer_Inc ( model, color, type, price ) 2. VALUES ( 3111 , 'y' , 'laser' , 599 ) ; В результате выполнения этого оператора в таблицу Printer_Inc будет вставлена информация о модели 3111 цветного лазерного принтера, стоимость которого равна $599. В поле code окажется значение, которое только случайно может оказаться равным 15. В большинстве случаев этого оказывается достаточно, так как значение автоинкрементируемого поля, как правило, не несет никакой информации; главное, чтобы оно было уникальным. Однако бывают случаи, когда требуется подставить вполне конкретное значение в автоинкрементируемое поле. Например, нужно перенести уже имеющиеся данные во вновь создаваемую структуру; при этом эти данные участвуют в связи «один-ко-многим» со стороны «один». Таким образом, мы не можем допустить тут произвола. С другой стороны, не хочется отказываться от автоинкрементируемого поля, так как оно упростит обработку данных при последующей эксплуатации базы данных. Поскольку стандарт языка SQL не предполагает наличия автоинкрементируемых полей, то не существует и единого подхода. Здесь мы покажем, как это реализуется в MS SQL Server. Оператор 1. SET IDENTITY_INSERT < имя таблицы > { ON | OFF } ; отключает (значение ON) или включает (OFF) использование автоинкремента. Поэтому чтобы вставить строку со значением 15 в поле code, нужно написать: 1. SET IDENTITY_INSERT Printer_Inc ON ; 2. INSERT INTO Printer_Inc ( code, model, color, type, price ) 3. VALUES ( 15 , 3111 , 'y' , 'laser' , 599 ) ; Обратите внимание, что список столбцов в этом случае является обязательным, то есть мы не можем написать так: 1. SET IDENTITY_INSERT Printer_Inc ON ; 2. INSERT INTO Printer_Inc 3. VALUES ( 15 , 3111 , 'y' , 'laser' , 599 ) ; ни, тем более, так: 1. SET IDENTITY_INSERT Printer_Inc ON ; 2. INSERT INTO Printer_Inc ( model, color, type, price ) 3. VALUES ( 3111 , 'y' , 'laser' , 599 ) ; В последнем случае в пропущенный столбец code значение не может быть подставлено автоматически, так как автоинкрементирование отключено. Важно отметить, что если значение 15 окажется максимальным в столбце code, то далее нумерация продолжится со значения 16. Естественно, если включить автоинкрементирование: 1. SET IDENTITY_INSERT Printer_Inc OFF; Наконец, рассмотрим пример вставки данных из таблицы Product в таблицу Product_Inc, сохранив значения в поле code: 1. SET IDENTITY_INSERT Printer_Inc ON ; 2. INSERT INTO Printer_Inc ( code, model,color,type,price ) 3. SELECT * FROM Printer; По поводу автоинкрементируемых столбцов следует добавить следующее. Пусть последнее значение в поле code было равно 16, после чего строка с этим значением была удалена. Какое значение будет в этом столбце после вставки новой строки? Правильно, 17, так как последнее значение счетчика сохраняется, несмотря на удаление строки, его содержащей. Поэтому нумерация значений в результате удаления и добавления строк не будет последовательной. Это является еще одной причиной для вставки строки с заданным (пропущенным) значением в автоинкрементируемом столбце. Рассмотрим теперь таблицу с единственным автоинкрементируемым столбцом (SQL Server): 1. CREATE TABLE test_Identity ( 2. id int IDENTITY ( 1 , 1 ) PRIMARY KEY 3. ) ; Как вставить в нее строки? Попытка не указывать значение 1. INSERT INTO test_Identity VALUES () ; или использовать значение по умолчанию 1. INSERT INTO test_Identity VALUES ( DEFAULT ) ; к успеху не приводит - ошибка. Понятно, что мы можем вставить конкретное значение, если отключим счетчик: 1. SET IDENTITY_INSERT test_Identity ON ; 2. INSERT INTO test_Identity ( id ) VALUES ( 5 ) ; 3. SELECT * FROM test_Identity; но тогда в нем нет для нас никакого смысла. Уточним вопрос: как вставить в таблицу именно последовательные значения счетчика? Оказывается, что ответ лежит на поверхности, а именно, в стандартном синтаксисе: 1. SET IDENTITY_INSERT test_Identity OFF; 2. INSERT INTO test_Identity DEFAULT VALUES ; Вряд ли вы будете использовать DEFAULT VALUES в других случаях, т.к. при наличии первичного ключа воспользоваться значениями по умолчанию для всех столбцов таблицы можно будет только один раз. Тут же мы можем повторить этот оператор столько раз, сколько последовательных значений счетчика нам потребуется добавить в таблицу. Обратимся теперь к другим СУБД, которые имеют в своем арсенале автоинкрементируемые столбцы. MySQL MySQL не поддерживает предложения DEFAULT VALUES. Вставить строку со значениями по умолчанию в таблицу можно другим стандартным способом, используя ключевое слово DEFAULT для каждого столбца таблицы - VALUES(DEFAULT, DEFAULT, ...). А как здесь вставить очередное значение счетчика в единственный автоинкрементируемый столбец? 1. CREATE TABLE test_Identity ( 2. id int ( 11 ) NOT NULL AUTO_INCREMENT , 3. PRIMARY KEY ( id ) 4. ) ; Очень просто. Оказывается будут работать те интуитивные приемы, которые мы безуспешно пытались применить в случае SQL Server, а именно, так 1. INSERT INTO test_Identity VALUES () ; или так 1. INSERT INTO test_Identity VALUES ( DEFAULT ) ; После выполнения обоих этих операторов получим: id 1 2 Заметим, что после вставки конкретного значения в автоинкрементируемый столбец (в MySQL это можно сделать обычным оператором вставки), которое будет превышать максимальное имеющееся, приращение счетчика продолжится уже с него. Например: 1. INSERT INTO test_Identity VALUES ( 8 ) ; 2. INSERT INTO test_Identity VALUES ( DEFAULT ) ; 3. SELECT * FROM test_Identity; id 1 2 8 9 PostgreSQL 1. CREATE TABLE identity_table ( id serial PRIMARY KEY ) ; Для вставки очередных значений счетчика мы можем воспользоваться следующими рассмотренными выше приемами: 1. INSERT INTO identity_table DEFAULT VALUES ; 2. INSERT INTO identity_table VALUES ( DEFAULT ) ; 3. INSERT INTO identity_table ( id ) VALUES ( DEFAULT ) ; 4. SELECT * FROM identity_table; id 1 2 3 Однако, если вставить конкретное значение (превышающее максимальное значение, достигнутое счетчиком) 1. INSERT INTO identity_table ( id ) VALUES ( 5 ) ; и продолжить заполнять значения счетчика, 1. INSERT INTO identity_table VALUES ( DEFAULT ) ; то нумерация продолжается не с максимального значения, а с последнего достигнутого при генерации: id 1 2 3 5 4 При этом, когда счетчик достигает 5 при генерации очередного значения, получаем ошибку, связанную с нарушением ограничения первичного ключа: ERROR: duplicate key value violates unique constraint "identity_table_pkey" DETAIL: Key (id)=(5) already exists. Если же таблица не имеет ключа на автоинкрементируемом столбце, то мы получим дубликаты, после чего нумерация продолжится дальше. Вот скрипт, который поясняет сказанное: 1. CREATE TABLE identity_table_wo ( id serial ) ; 2. INSERT INTO identity_table_wo ( id ) VALUES ( DEFAULT ) ; 3. INSERT INTO identity_table_wo ( id ) VALUES ( 2 ) ; 4. INSERT INTO identity_table_wo ( id ) VALUES ( DEFAULT ) , ( DEFAULT ) ; 5. SELECT * FROM identity_table_wo; id 1 2 2 3 Как сбросить значение счетчика в заданное значение (MySQL)? Воспользуемся таблицей, рассмотренной на предыдущей странице , и вставим в неё 3 строки. 1. CREATE TABLE test_Identity ( 2. id int ( 11 ) NOT NULL AUTO_INCREMENT , 3. PRIMARY KEY ( id ) 4. ) ; 5. INSERT INTO test_Identity VALUES () , () , () ; 6. SELECT * FROM test_Identity; id 1 2 3 Если мы удалим последнюю строку, нумерация продолжится не с 3, а с 4. Т.е. последнее значение счётчика сохраняется и используется при последующем добавлении строк: 1. DELETE FROM test_Identity WHERE id= 3 ; 2. INSERT INTO test_Identity VALUES () ; 3. SELECT * FROM test_Identity; id 1 2 4 Возникает вопрос: "А можно ли сделать так, чтобы нумерация продолжилась с последнего имеющегося значения?" Оставляя в стороне вопрос о том, зачем это нужно, ответим - можно. Но устанавливать это значение счётчика нужно вручную. Итак, 1. DELETE FROM test_Identity WHERE id= 4 ; 2. ALTER TABLE test_Identity AUTO_INCREMENT = 3 ; 3. INSERT INTO test_Identity VALUES () , () , () ; 4. SELECT * FROM test_Identity; id 1 2 3 4 5 Конструктор значений таблицы Синтаксис конструктора значений таблицы: 1. VALUES 2. ( <элемент конструктора>, <элемент конструктора>, ... ) , 3. ( <элемент конструктора>, <элемент конструктора>, ... ) , 4. ... 5. ( <элемент конструктора>, <элемент конструктора>, ... ) При этом элементом конструктора может быть: - выражение, вычисляющее значение, совместимое с типом данных соответствующего столбца таблицы; - DEFAULT - для подстановки значения по умолчанию для соответствующего столбца таблицы; - NULL; - подзапрос, возвращающий одно значение, совместимое с типом данных соответствующего столбца таблицы. Конструктор значений таблицы может использоваться для вставки набора строк в существующую таблицу с помощью одного оператора INSERT Создадим следующую таблицу для выполнения примеров: 1. CREATE TABLE Items ( 2. item_no int PRIMARY KEY , 3. maker char ( 10 ) , 4. type char ( 10 ) DEFAULT 'PC' , 5. value int 6. ) ; Вставим в таблицу 4 строки, используя конструктор. 1. INSERT INTO Items VALUES 2. ( 1 , 'A' , 'Laptop' , 12 ) , 3. ( 2 , 'B' , DEFAULT , NULL ) , 4. ( 3 , 'C' , 'Printer' , ( SELECT CAST ( model AS int ) FROM Printer WHERE code= 1 )) , 5. ( 4 , 'C' , 'Printer' , ( SELECT CAST ( model AS int ) FROM Printer WHERE code= 77 )) ; 1. SELECT * FROM Items; item_no maker type value 1 A Laptop 12 2 B PC NULL 3 C Printer 3001 4 C Printer NULL Последнее значение в двух последних строках было получено с помощью подзапроса, который возвращает либо одно значение (поскольку выполняется отбор по ключу) с номером модели из таблицы Printer, либо ни одного. Последнее имеет место для четвертой строки, поскольку коду 77 не отвечает никакая строка таблицы Printer. В этом случае будет записано NULL-значение. Конструктор значений таблицы может использоваться также в предложении FROM. В параграфе, посвященном генерации числовой последовательности , последний пример, который находит 100 последовательных незанятых номеров моделей, с учетом этой возможности можно переписать более компактно: 1. SELECT ( SELECT MAX ( model ) 2. FROM Product 3. ) + 5 * 5 * ( a -1 ) + 5 * ( b -1 ) + c AS num 4. FROM 5. ( VALUES ( 1 ) , ( 2 ) , ( 3 ) , ( 4 ) , ( 5 )) x ( a ) CROSS JOIN 6. ( VALUES ( 1 ) , ( 2 ) , ( 3 ) , ( 4 ) , ( 5 )) y ( b ) CROSS JOIN 7. ( VALUES ( 1 ) , ( 2 ) , ( 3 ) , ( 4 ) , ( 5 )) z ( c ) 8. WHERE 5 * 5 * ( a -1 ) + 5 * ( b -1 ) + c <= 100 9. ORDER BY 1 ; Еще один пример использования конструктора значений таблицы для трансформации строки в столбец можно увидеть в главе, посвященной оператору CROSS APPLY UPDATE_Оператор_UPDATE'>Оператор UPDATE Оператор UPDATE изменяет имеющиеся данные в таблице. Команда имеет следующий синтаксис: 1. UPDATE <имя таблицы> 2. SET { <имя столбца> = { <выражение для вычисления значения столбца> 3. | NULL 4. | DEFAULT } ,... } 5. [ { WHERE <предикат> }] С помощью одного оператора могут быть заданы значения для любого количества столбцов. Однако в одном и том же операторе UPDATE можно вносить изменения в каждый столбец указанной таблицы только один раз. При отсутствии предложения WHERE будут обновлены все строки таблицы. Если столбец допускает NULL-значение, то его можно указать в явном виде. Кроме того, можно заменить имеющееся значение на значение по умолчанию (DEFAULT) для данного столбца. Ссылка на «выражение для вычисления значения столбца» может относиться к текущим значениям в изменяемой таблице. Например, мы можем уменьшить все цены портативных компьютеров на 10 процентов с помощью следующего оператора: 1. UPDATE Laptop 2. SET price = price* 0.9 ; Разрешается также значения одних столбцов присваивать другим столбцам. Пусть, например, требуется заменить жесткие диски менее 10 Гбайт в портативных компьютерах. При этом емкость новых дисков должна составлять половину объема RAM, имеющейся в данных устройствах. Эту задачу можно решить следующим образом: 1. UPDATE Laptop 2. SET hd = ram/ 2 WHERE hd < 10 ; Естественно, типы данных столбцов hd и ram должны быть совместимы. Для приведения типов может использоваться выражение |