Sql запрос insert: INSERT | SQL | SQL-tutorial.ru

Sql-запросы на модификацию данных

      1. Добавление новых записей в таблицу (insert)

Общий синтаксис:

а)
вставка одной новой строки в таблицу:

INSERT
[INTO] <таблица> [(столбцы)]

VALUES (значения)

б)
вставка в таблицу одной
или более строк из подзапроса:

INSERT [INTO] <таблица>
[(столбцы)]

<SELECT запрос>.

Правила

  1. Количество
    элементов в разделе «Столбцы» должно
    совпадать с количеством элементов в
    разделе «Значения»; типы данных должны
    совпадать либо допускать неявное
    преобразование.

  2. Если
    раздел «Столбцы» отсутствует, раздел
    «Значения» должен содержать столько
    же значений, сколько всего столбцов в
    редактируемой таблице, кроме того,
    значения должны перечисляться в том
    же порядке, в каком объявлены столбцы
    таблицы.

  3. В
    случае использования синтаксиса (б)
    подзапрос SELECT может быть адресован
    либо к этой же таблице, либо к другой
    таблице базы данных, либо к другой базе
    данных на сервере;
    структура и типы полей запроса должны
    соответствовать структуре и типам
    полей таблицы.

  4. Если
    для некоторого столбца задано значение
    по умолчанию, в разделе «Значения» для
    этого столбца можно
    написать ключевое слово DEFAULT.

Примеры:

INSERT INTO detail
(detail_name)

VALUES
(‘Подшипник’)

INSERT
INTO supply (supplier_id, supply_quantity, supply_cost,

supply_date, detail_id)

VALUES (4, 177,
453.45, ‘25.09.2009’, 1)

INSERT supplier
(supplier_type, supplier_name)

VALUES
(DEFAULT, ‘Смирнов С.
С.’)

      1. Модификация существующих записей (update)

Общий синтаксис:

UPDATE
<таблица>

SET
<столбец> = <значение> {[, <столбец>
= <значение>]}*

[FROM
<таблица(ы) источников>]

[WHERE <условие>]

Примеры:

UPDATE detail

SET weight = 210

UPDATE material

SET
material_name = ‘Олово’

WHERE
material_id = 2

UPDATE delivery

SET department_id = NULL,

delivery_date
= delivery_date
+ 1 /*увеличение
даты поставки на день

WHERE delivery_id =
1 OR department_id > 3

UPDATE detail

SET detail_name =
detail_name + ‘!!!’

WHERE
detail_name LIKE ‘_а%’ AND weight BETWEEN
6 AND 10

      1. Удаление записей из таблицы (delete)

Общий синтаксис:

DELETE<таблица>

[WHERE] <условие>

Примеры:

DELETE detail

WHERE detail_id IN
(2, 5, 8)

DELETE detail

WHERE weight IS NULL

DELETE
detail

Задание:заполните БД с помощью
команды «Изменить первые 200 строк»
контекстного меню таблицы (рис. 4.1).

Составьте 9 запросов на модификацию
данных: три – на вставку записей (INSERT),
три – на обновление (UPDATE), три – на
удаление (DELETE).

Контрольные вопросы

  1. Какие
    команды языка SQL
    используются для актуализации созданной
    базы данных?

  2. Как
    определяется размер создаваемой базы
    данных?

  3. Какова
    технология загрузки информации в базу
    данных, поддерживаемая средой Management
    Studio?

  4. Что
    такое транзакция? Охарактеризуйте
    механизм управления транзакциями СУБД.

  5. Когда
    на пользовательском уровне требуется
    пользоваться механизмом управления
    транзакциями?

  6. Какая
    команда языка SQL
    используется для ввода информации в
    базу данных? Охарактеризуйте варианты
    использования этой команды.

  7. Как
    работает многострочная конструкция
    команды INSERT?

  8. Охарактеризуйте
    правило согласованности значений в
    команде INSERT.

  9. Сформулируйте
    правила оформления значений различных
    типов данных в СУБД MS
    SQL
    Server.

  10. Какая
    команда языка SQL
    используется для обновления записей
    таблиц базы данных?

  11. Какие
    команды SQL
    используются для удаления строк таблицы
    и в целом всей таблицы?

  12. Для
    чего используется команда ALTER
    TABLE?
    Как посредством среды Management Studio
    воспользоваться этой функцией?

  13. Посредством
    какой опции прописываются вторичные
    ключи при создании таблицы?

  14. Как
    можно добавить новое поле в ранее
    созданную таблицу?

  15. Что
    такое индексы в базе данных? Как они
    создаются?

  16. Как
    задаются права доступа к созданной
    базе данных?

  17. Как
    в среде Management
    Studio
    удалить базу данных?

SQL.RU | Перекрестные запросы в Т-SQL

Перекрестные запросы (Crosstab Query) являются еще одной специфической разновидностью запросов на выборку. Предназначены они для более глубокого анализа информации, хранящейся в таблицах.

Ключевым словом SQL-оператора перекрестного запроса, задающим его тип, является слово TRANSFORM (преобразовать). Это подразумевает, что значения одного из столбцов (полей) выборки, будут преобразованы в названия столбцов итоговой выборки.

Результаты перекрестного запроса группируются по двум наборам данных, один из которых расположен в левом столбце (столбцах) таблицы, а второй — в верхней строке. В остальном пространстве таблицы отображаются результаты статистических расчетов (Sum, Count и т.д.), выполненных над данными трансформированного поля.

Для демонстрации создадим тестовую таблицу:

CREATE TABLE Sales
(
SaleID int IDENTITY PRIMARY KEY CLUSTERED,
ClientID int,
Date datetime,
Amount money
)
insert Sales values(1,'20010401', 15.48)
insert Sales values(1,'20020302', 134.01)
insert Sales values(1,'20031003', 2346.03)
insert Sales values(2,'20030203', 754. 88)
insert Sales values(3,'20010301', 73.07)
insert Sales values(3,'20030402', 734.46)
insert Sales values(4,'20010301', 1567.10)
insert Sales values(4,'20020404', 6575.70)
insert Sales values(4,'20030307', 6575.77)
insert Sales values(4,'20030309', 6575.37)
insert Sales values(5,'20011201', 1975.73)
insert Sales values(5,'20030306', 178965.63)
insert Sales values(6,'20020103', 16785.34)
insert Sales values(6,'20030304', 1705.44)
GO

До версии SQL Server 2005, в котором появился оператор PIVOT, перекрестные запросы выполнялись через оператор CASE (BOL->Cross-Tab Reports):

SELECT
  MONTH(Date) AS SaleMonth,
  SUM(CASE YEAR(Date)
        WHEN 2001 THEN Amount
        ELSE 0
      END) AS [2001],
  SUM(CASE YEAR(Date)
        WHEN 2002 THEN Amount
        ELSE 0
      END) AS [2002],
  SUM(CASE YEAR(Date)
        WHEN 2003 THEN Amount
        ELSE 0
      END) AS [2003]
FROM Sales
GROUP BY MONTH(Date)
ORDER BY MONTH(Date)
GO

Результат выполнения запросa:

SaleMonth   2001                  2002                  2003                 
———— ——————— ——————— ———————
1           . 0000                 16785.3400            .0000
2           .0000                 .0000                 754.8800
3           1640.1700             134.0100              193822.2100
4           15.4800               6575.7000             734.4600
10          .0000                 .0000                 2346.0300
12          1975.7300             .0000                 .0000

(6 row(s) affected)

Теперь тот же запрос, но уже с использованием оператора PIVOT (версия SQL Server 2005 и выше):

SELECT * FROM
(SELECT YEAR(Date) y, MONTH(Date) SaleMonth, Amount FROM Sales) as s
PIVOT
(SUM(Amount) for y in ([2001], [2002], [2003])) pv

Всё бы хорошо, когда мы за ранее знаем кол-во лет, но что делать, если мы не имеем такой информации на входе, либо нужно, чтобы код был более универсальным и при добавлении записей с более старшей датой код был «рабочим». Выход-динамический перекрестный запрос (Dynamic Crosstab Queries). Усложним наши тестовые данные добавив хотя бы одну запись с другим годом:

insert Sales values(1,'20010401', 15. 48)

Очень интересное решение этой задачи предложил Ицик Бен-Ган (http://am.rusimport.ru/MsAccess/topic.aspx?id=285):

CREATE PROCEDURE sp_CrossTab
  @table       AS sysname,-- Таблица для построения crosstab отчета
  @onrows      AS nvarchar(128),-- Значение для группировки по строкам
  @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки
  @oncols      AS nvarchar(128),-- Значение для группировки по колонкам
  @sumcol      AS sysname = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
  @sql AS varchar(8000),
  @NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

-- Шаг 1: начало строки SQL.
SET @sql =
  'SELECT' + @NEWLINE +
  '  ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
  END

-- Шаг 2: Хранение ключей во временной таблице.
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table

EXEC (@keyssql)

-- Шаг 3: Средняя часть строки SQL. 
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ','                   + @NEWLINE +
    '  SUM(CASE CAST(' + @oncols +
                     ' AS nvarchar(100))' + @NEWLINE +
    '        WHEN N''' + @key +
           ''' THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '1'
                          ELSE @sumcol
                        END + @NEWLINE +
    '        ELSE 0'                      + @NEWLINE +
    '      END) AS [' + @key+']'

  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END

-- Шаг 4: Конец строки SQL.
SET @sql = @sql         + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows

SET NOCOUNT OFF
PRINT @sql  + @NEWLINE-- для отладки
EXEC (@sql)
GO

Вызов этой процедуры:

EXEC sp_CrossTab
  @table       = 'Sales',
  @onrows      = 'MONTH(Date)',
  @onrowsalias = 'SaleMonth',
  @oncols      = 'YEAR(Date)',
  @sumcol      = 'Amount'
GO

 Но хотелось бы немного упростить эту процедуру с помощью оператора PIVOT и вот, что получилось:

CREATE PROCEDURE sp_CrossTab_PIVOT
  @table       AS sysname,-- Таблица для построения crosstab отчета
  @onrows      AS nvarchar(128),-- Значение для группировки по строкам
  @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки
  @oncols      AS nvarchar(128),-- Значение для группировки по колонкам
  @sumcol      AS sysname = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
  @sql AS nvarchar (max),
  @case AS varchar(1000)
SET @case=''
SELECT @sql='
SELECT @[email protected]+''[''+CONVERT(VARCHAR, '[email protected]+')+''], '''+
' FROM '[email protected]+' GROUP BY '[email protected]+' ORDER BY '[email protected]
 
EXEC sp_executesql @sql,N'@case varchar(1000) out', @[email protected] out
SET @case=LEFT(@case, LEN(@case)-1)

SELECT @sql='SELECT * FROM (
SELECT '[email protected]+' y, '[email protected]+' '[email protected]+', '[email protected]+' FROM '[email protected]+
') as s
PIVOT
(SUM ('[email protected]+') for y in ('[email protected]+')) as pv'
PRINT @sql -- для отладки 
EXECUTE (@sql)

Вызов моей процедуры идентичен вызову предыдущей процедуры:

EXEC sp_CrossTab_PIVOT
  @table       = 'Sales',
  @onrows      = 'MONTH(Date)',
  @onrowsalias = 'SaleMonth',
  @oncols      = 'YEAR(Date)',
  @sumcol      = 'Amount'
GO

Ещё одно решение этой задачи можно найти в FAQ на сайте SQL. RU (http://www.sql.ru/faq/faq_topic.aspx?fid=358)

Примеры запроса на добавление Microsoft Access и синтаксис запроса SQL INSERT

Запрос INSERT добавляет записи в конец таблицы.

Запрос на добавление нескольких записей

ВСТАВИТЬ В цель [(поле1[ поле2[ ]])]
[ВО внешней БД]

ВЫБЕРИТЕ [источник.]поле1[ поле2[ ]]
ИЗ источника
[присоединиться]
ГДЕ критерии
 

Оператор UPDATE состоит из следующих частей:

цель Имя таблицы для вставки (добавления) новых записей
полей Имена полей для получения новых значений (порядок должен соответствовать полям в разделе источника SELECT)
внешняя БД Предложение IN используется, если данные передаются в таблицу
в другой базе данных. Должен быть указан полный путь к базе данных в кавычках.
исходные поля Подобно стандартной инструкции SELECT,
список полей здесь должен соответствовать порядку полей в цели
список полей (также можно заменить запросом)
присоединиться Предложение JOIN при ссылке на другую таблицу (таблицы), чтобы указать, какие записи извлекаются
критерии Выражение, определяющее, какие записи обновляются. Обновляются только записи, удовлетворяющие выражению.

Запрос на добавление одной записи

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

ВСТАВИТЬ В цель [(поле1[ поле2[ ]])]
ЗНАЧЕНИЯ (значение1[ значение2[ ]])
 

Примечания (от Microsoft)

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

Если указан запрос, ядро ​​базы данных Microsoft Access добавляет записи во все таблицы.
указывается запросом. INSERT INTO необязателен, но при его включении предшествует оператору SELECT.

Если ваша целевая таблица содержит первичный ключ, убедитесь, что вы добавили уникальные ненулевые значения в
поле или поля первичного ключа; если вы этого не сделаете, ядро ​​базы данных Microsoft Access не добавит
записи. Если вы добавляете записи в таблицу с полем автонумерации и хотите перенумеровать добавленные
записи, не включайте в запрос поле «Счетчик». Включите поле AutoNumber в запрос, если
вы хотите сохранить исходные значения из поля.

Используйте предложение IN для добавления записей в таблицу в другой базе данных. Чтобы создать новую таблицу, используйте
SELECT … INTO вместо того, чтобы создать
запрос на создание таблицы. Чтобы узнать, какие записи будут добавлены, прежде чем вы запустите запрос на добавление, сначала выполните
и просмотреть результаты запроса на выборку, в котором используются те же критерии выбора. Запрос на добавление копирует записи
из одной или нескольких таблиц в другую.