Как заполнять таблицы в sql

Предложения ORDER BY и GROUP BY

ORDER BY

Предложение ORDER BY используется для сортировки данных по возрастанию ( ASC ) или убыванию ( DESC ). Многие СУБД по умолчанию выполняют сортировку по возрастанию.

SELECT col1, col2, . colN FROM tableName [WHERE condition] [ORDER BY col1, col2, . colN] [ASC | DESC];

Обратите внимание: колонки для сортировки должны быть указаны в списке колонок для выборки.

Сделаем выборку пользователей, отсортировав их по городу и возрасту:

SELECT * FROM users ORDER BY city, age;

Теперь выполним сортировку по убыванию:

SELECT * FROM users ORDER BY city, age DESC;

Определим собственный порядок сортировки по убыванию:

SELECT * FROM users ORDER BY (CASE city WHEN 'Ekaterinburg' THEN 1 WHEN 'Moscow' THEN 2 ELSE 100 END) ASC, city DESC;

GROUP BY

Предложение GROUP BY используется совместно с инструкцией SELECT для группировки записей. Оно указывается после WHERE и перед ORDER BY .

SELECT col1, col2, . colN FROM tableName WHERE condition GROUP BY col1, col2, . colN ORDER BY col1, col2, . colN;

Сгруппируем активных пользователей по городам:

SELECT city, COUNT(city) AS amount FROM users WHERE status = active GROUP BY city ORDER BY city;

Ключевое слово DISTINCT

Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).

SELECT DISTINCT col1, col2, . colN FROM tableName [WHERE condition];

Сделаем выборку городов проживания пользователей:

SELECT DISTINCT city FROM users;

Соединения

Соединения (joins) используются для комбинации записей двух и более таблиц.

Предположим, что кроме users , у нас имеется таблица orders с заказами пользователей следующего содержания:

Сделаем выборку полей userId , userName , age и amount из наших таблиц посредством их соединения:

SELECT userId, userName, age, amount FROM users, orders WHERE users.userId = orders.userId;

При соединении таблиц могут использоваться такие операторы, как = , < , >, <> , = , != , BETWEEN , LIKE и NOT , однако наиболее распространенным является = .

Существуют разные типы объединений:

• INNER JOIN — возвращает записи, имеющиеся в обеих таблицах
• LEFT JOIN — возвращает записи из левой таблицы, даже если такие записи отсутствуют в правой таблице
• RIGHT JOIN — возвращает записи из правой таблицы, даже если такие записи отсутствуют в левой таблице
• FULL JOIN — возвращает все записи объединяемых таблиц
• CROSS JOIN — возвращает все возможные комбинации строк обеих таблиц
• SELF JOIN — используется для объединения таблицы с самой собой

Предложение UNION

Предложение/оператор UNION используется для комбинации результатов двух и более инструкций SELECT . При этом, возвращаются только уникальные записи.

В случае с UNION , каждая инструкция SELECT должна иметь:

• одинаковый набор колонок для выборки
• одинаковое количество выражений
• одинаковые типы данных колонок и
• одинаковый порядок колонок

Однако, они могут быть разной длины.

SELECT col1, col2, . colN FROM table1 [WHERE condition] UNION SELECT col1, col2, . colN FROM table2 [WHERE condition];

Объединим наши таблицы users и orders :

 SELECT userId, userName, amount, date FROM users LEFT JOIN orders ON users.useId = orders.userId UNION SELECT userId, userName, amount, date FROM users RIGHT JOIN orders ON users.userId = orders.userId;

Предложение UNION ALL

Предложение UNION ALL также используется для объединения результатов двух и более инструкций SELECT . При этом, возвращаются все записи, включая дубликаты.

SELECT col1, col2, . colN FROM table1 [WHERE condition] UNION ALL SELECT col1, col2, . colN FROM table2 [WHERE condition];

Существует еще два предложения, похожих на UNION :

• INTERSECT — используется для комбинации результатов двух и более SELECT , но возвращаются только строки из первого SELECT , совпадающие со строками из второго SELECT
• EXCEPT|MINUS — возвращаются только строки из первого SELECT , отсутствующие во втором SELECT

Синонимы

Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. "Временно" означает, что новое название используется только в текущем запросе, в БД название остается прежним.

Синтаксис синонима таблицы:

SELECT col1, col2, . colN FROM tableName AS aliasName [WHERE condition];

Синтаксис синонима колонки:

SELECT colName AS aliasName FROM tableName [WHERE condition];

Пример использования синонимов таблиц:

SELECT U.userId, U.userName, U.age, O.amount FROM users AS U, orders AS O WHERE U.userId = O.userId;

Пример использования синонимов колонок:

SELECT userId AS user_id, userName AS user_name, age AS user_age FROM users WHERE status = active;

Индексы

Создание индексов

Индексы — это специальные поисковые таблицы (lookup tables), которые используются движком БД в целях более быстрого извлечения данных. Проще говоря, индекс — это указатель или ссылка на данные в таблице.

Индексы ускоряют работу инструкции SELECT и предложения WHERE , но замедляют работу инструкций UPDATE и INSERT . Индексы могут создаваться и удаляться, не оказывая никакого влияния на данные.

Для создания индекса используется инструкция CREATE INDEX , позволяющая определять название индекса, индексируемые колонки и порядок индексации (по возрастанию или по убыванию).

К индексам можно применять ограничение UNIQUE для того, чтобы обеспечить их уникальность.

Синтаксис создания индекса:

CREATE INDEX indexName ON tableName;

Синтаксис создания индекса для одной колонки:

CREATE INDEX indexName ON tableName (colName);

Синтакис создания уникальных индексов (такие индексы используются не только для повышения производительности, но и для обеспечения согласованности данных):

CREATE UNIQUE INDEX indexName ON tableName (colName);

Синтаксис создания индексов для нескольких колонок (композиционный индекс):

CREATE INDEX indexName ON tableName (col1, col2, . colN);

Решение о создании индексов для одной или нескольких колонок следует принимать на основе того, какие колонки будут часто использоваться в запросе WHERE в качестве условия для сортировки строк.

Для ограничений PRIMARY KEY и UNIQUE автоматически создаются неявные индексы.

Удаление индексов

Для удаления индексов используется инструкция DROP INDEX :

DROP INDEX indexName;

Несмотря на то, что индексы предназначены для повышения производительности БД, существуют ситуации, в которых их использования лучше избегать.

К таким ситуациям относится следующее:

• индексы не должны использоваться в маленьких таблицах
• в таблицах, которые часто и в большом объеме обновляются или перезаписываются
• в колонках, которые содержат большое количество нулевых значений
• в колонках, над которыми часто выполняются операции

Обновление таблицы

Команда ALTER TABLE используется для добавления, удаления и модификации колонок существующей таблицы. Также эта команда используется для добавления и удаления ограничений.

-- добавление новой колонки ALTER TABLE tableName ADD colName datatype; -- удаление колонки ALTER TABLE tableName DROP COLUMN colName; -- изменение типа данных колонки ALTER TABLE tableName MODIFY COLUMN colName newDatatype; -- добавление ограничения `NOT NULL` ALTER TABLE tableName MODIFY colName datatype NOT NULL; -- добавление ограничения `UNIQUE` ALTER TABLE tableName ADD CONSTRAINT myUniqueConstraint UNIQUE (col1, col2, . colN); -- добавление ограничения `CHECK` ALTER TABLE tableName ADD CONSTRAINT myUniqueConstraint CHECK (condition); -- добавление первичного ключа ALTER TABLE tableName ADD CONSTRAINT myPrimaryKey PRIMARY KEY (col1, col2, . colN); -- удаление ограничения ALTER TABLE tableName DROP CONSTRAINT myUniqueContsraint; -- mysql ALTER TABLE tableName DROP INDEX myUniqueContsraint; -- удаление первичного ключа ALTER TABLE tableName DROP CONSTRAINT myPrimaryKey; -- mysql ALTER TABLE tableName DROP PRIMARY KEY;

Добавляем в таблицу users новую колонку — пол пользователя:

ALTER TABLE users ADD sex char(1);

Удаляем эту колонку:

ALTER TABLE users DROP sex;

Очистка таблицы

Команда TRUNCATE TABLE используется для очистки таблицы. Ее отличие от DROP TABLE состоит в том, что сохраняется структура таблицы ( DROP TABLE полностью удаляет таблицу и все ее данные).

TRUNCATE TABLE tableName;

Очищаем таблицу users :

TRUNCATE TABLE users;

Проверяем, что users пустая:

SELECT * FROM users; -- Empty set (0.00 sec)

Представления

Представление (view) — это не что иное, как инструкция, записанная в БД под определенным названием. Другими словами, представление — это композиция таблицы в форме предварительно определенного запроса.

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

Представления — это виртутальные таблицы, позволяющие делать следующее:

• структурировать данные способом, который пользователи находят наиболее естественным или интуитивно понятным
• ограничивать доступ к данным таким образом, что пользователь может просматривать и (иногда) модифицировать только то, что ему нужно и ничего более
• объединять данные из нескольких таблиц для формирования отчетов

Создание представления

Для создания представления используется инструкция CREATE VIEW . Как было отмечено, представления могут создаваться на основе одной или нескольких таблиц, и даже на основе другого представления.

CREATE VIEW viewName AS SELECT col1, col2, . colN FROM tableName [WHERE condition];

Создаем представление для имен и возраста пользователей:

CREATE VIEW usersView AS SELECT userName, age FROM users;

Получаем данные с помощью представления:

SELECT * FROM usersView;

WITH CHECK OPTION

WITH CHECK OPTION — это настройка инструкции CREATE VIEW . Она позволяет обеспечить соответствие всех UPDATE и INSERT условию, определенном в представлении.

Если условие не удовлетворяется, выбрасывается исключение.

CREATE VIEW usersView AS SELECT userName, age FROM users WHERE age IS NOT NULL WITH CHECK OPTION;

Обновление представления

Представление может быть обновлено при соблюдении следующих условий:

• SELECT не содержит ключевого слова DISTINCT
• SELECT не содержит агрегирующих функций
• SELECT не содержит функций установки значений
• SELECT не содержит операций установки значений
• SELECT не содержит предложения ORDER BY
• FROM не содержит больше одной таблицы
• WHERE не содержит подзапросы
• запрос не содержит GROUP BY или HAVING
• вычисляемые колонки не обновляются
• все ненулевые колонки из базовой таблицы включены в представление в том же порядке, в каком они указаны в запросе INSERT

Пример обновления возраста пользователя с именем Igor в представлении:

UPDATE usersView SET age = 31 WHERE userName = 'Igor';

Обратите внимание: обновление строки в представлении приводит к ее обновлению в базовой таблице.

В представление могут добавляться новые строки с помощью команды INSERT . При выполнении этой команды должны соблюдаться те же правила, что и при выполнении команды UPDATE .

С помощью команды DELETE можно удалять строки из представления.

Удаляем из представления пользователя, возраст которого составляет 26 лет:

DELETE FROM usersView WHERE age = 26;

Обратите внимание: удаление строки в представлении приводит к ее удалению в базовой таблице.

Удаление представления

Для удаления представления используется инструкция DROP VIEW :

DROP VIEW viewName;

Удаляем представление usersView :

DROP VIEW usersView;

HAVING

Предложение HAVING используется для фильтрации результатов группировки. WHERE используется для применения условий к колонкам, а HAVING — к группам, созданным с помощью GROUP BY .

HAVING должно указываться после GROUP BY , но перед ORDER BY (при наличии).

SELECT col1, col2, . colN FROM table1, table2, . tableN [WHERE condition] GROUP BY col1, col2, . colN HAVING condition ORDER BY col1, col2, . colN;

Транзакции

Транзакция — это единица работы или операции, выполняемой над БД. Это последовательность операций, выполняемых в логическом порядке. Эти операции могут запускаться как пользователем, так и какой-либо программой, функционирующей в БД.

Транзакция — это применение одного или более изменения к БД. Например, при создании/обновлении/удалении записи мы выполняем транзакцию. Важно контролировать выполнение таких операций в целях обеспечения согласованности данных и обработки возможных ошибок.

На практике, запросы, как правило, не отправляются в БД по одному, они группируются и выполняются как часть транзакции.

Свойства транзакции

Транзакции имеют 4 стандартных свойства (ACID):

• атомарность (atomicity) — все операции транзакции должны быть успешно завершены. В противном случае, транзакция прерывается, а все изменения отменяются (происходит откат к предыдущему состоянию)
• согласованность (consistency) — состояние должно изменться в полном соответствии с операциями транзакции
• изоляция или автономность (isolation) — транзакции не зависят друг от друга и не оказывают друг на друга никакого влияния
• долговечность (durability) — результат звершенной транзакции должен сохраняться при поломке системы

Управление транзакцией

Для управления транзакцией используются следующие команды:

• BEGIN|START TRANSACTION — запуск транзакции
• COMMIT — сохранение изменений
• ROLLBACK — отмена изменений
• SAVEPOINT — контрольная точка для отмены изменений
• SET TRANSACTION — установка характеристик текущей транзакции

Команды для управления транзакцией могут использоваться только совместно с такими запросами как INSERT , UPDATE и DELETE . Они не могут использоваться во время создания и удаления таблиц, поскольку эти операции автоматически отправляются в БД.

Удаляем пользователя, возраст которого составляет 26 лет, и отправляем изменения в БД:

BEGIN TRANSACTION DELETE FROM users WHERE age = 26; COMMIT;

Удаляем пользователя с именем Oleg и отменяем эту операцию:

BEGIN DELETE FROM users WHERE username = 'Oleg'; ROLLBACK;

Контрольные точки создаются с помощью такого синтаксиса:

SAVEPOINT savepointName;

Возврат к контрольной точке выполняется так:

ROLLBACK TO savepointName;

Выполняем три запроса на удаление данных из users , создавая контрольные точки перед каждый удалением:

START TRANSACTION SAVEPOINT sp1; DELETE FROM users WHERE age = 26; SAVEPOINT sp2; DELETE FROM users WHERE userName = 'Oleg'; SAVEPOINT sp3; DELETE FROM users WHERE status = 'inactive';

Отменяем два последних удаления, возвращаясь к контрльной точке sp2 , созданной после первого удаления:

ROLLBACK TO sp2;

Делаем выборку пользователей:

SELECT * FROM users;

Как видим, из таблицы был удален только пользователь с возрастом 26 лет.

Для удаление контрольной точки используется команда RELEASE SAVEPOINT . Естественно, после удаления контрольной точки, к ней нельзя будет вернуться с помощью ROLLBACK TO .

Команда SET TRANSACTION используется для инициализации транзакции, т.е. начала ее выполнения. При этом, можно определять некоторые характеристики транзакции. Например, так можно определить уровень доступа транзакции (доступна только для чтения или для записи тоже):

SET TRANSACTION [READ WRITE | READ ONLY];

Временные таблицы

Некоторые СУБД поддерживают так называемые временные таблицы (temporary tables). Такие таблицы позволяют хранить и обрабатывать промежуточные результаты с помощью таких же запросов, как и при работе с обычными таблицами.

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

Временная таблица создается с помощью инструкции CREATE TEMPORARY TABLE , в остальном синтаксис создания таких таблиц идентичен синтаксису создания обычных таблиц.

Временная таблица удаляется точно также, как и обычная таблица, с помощью инструкции DROP TABLE .

Клонирование таблицы

Может возникнуть ситуация, когда потребуется получить точную копию существующей таблицы, а CREATE TABLE или SELECT окажется недостаточно в силу того, что мы хотим получить не только идентичную структуру, но также индексы, значения по умолчанию и т.д. копируемой таблицы.

В mysql , например, это можно сделать так:

• вызываем команду SHOW CREATE TABLE для получения инструкции, выполненной при создании таблицы, включая индексы и прочее
• меняем название таблицы и выполняем запрос. Получаем точную копию таблицы
• опционально: если требуется содержимое копируемой таблицы, можно также использовать инструкции INSERT INTO или SELECT

Подзапросы

Подзапрос — это внутренний (вложенный) запрос другого запроса, встроенный (вставленный) с помощью WHERE или других инструкций.

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

Подзапросы могут использоваться в инструкциях SELECT , INSERT , UPDATE и DELETE , а также с операторами = , < , >, >= ,

Правила использования подзапросов:

• они должны быть обернуты в круглые скобки
• подзапрос должен содержать только одну колонку для выборки, если основной запрос не содержит несколько таких колонок, которые сравниваются в подзапросе
• в подзапросе нельзя использовать команду ORDER BY , это можно сделать в основном запросе. В подзапросе для замены ORDER BY можно использовать GROUP BY
• подзапросы, возвращающие несколько значений, могут использоваться только с операторами, которые работают с наборами значений, такими как IN
• список SELECT не может содержать ссылки на значения, которые оцениваются (вычисляются) как BLOB , ARRAY , CLOB или NCLOB
• подзапрос не может быть сразу передан в функцию для установки значений
• команду BETWEEN нельзя использовать совместно с подзапросом. Тем не менее, в самомподзапросе указанную команду использовать можно

Подзапросы, обычно, используются в инструкции SELECT .

SELECT col1, col2, . colN FROM table1, table2, . tableN WHERE colName operator (SELECT col1, col2, . colN FROM table1, table2, tableN [WHERE condition]);

SELECT * FROM users WHERE userId IN ( SELECT userId FROM users WHERE status = 'active' );

Подзапросы могут использоваться в инструкции INSERT . Эта инструкция добавляет в таблицу данные, возвращаемые подзапросом. При этом, данные, возвращаемые подзапросом, могут быть модифицированы любыми способами.

INSERT INTO tableName col1, col2, . colN SELECT col1, col2, . colN FROM table1, table2, . tableN [WHERE operator [value]];

Подзапросы могут использоваться в инструкции UPDATE . При этом, данные из подзапроса могут использоваться для обновления любого количества колонок.

UPDATE tableName SET col = newVal [WHERE operator [value] ( SELECT colName FROM tableName [WHERE condition] ) ];

Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.

DELETE FROM tableName [WHERE operator [value] ( SELECT colName FROM tableName [WHERE condition] ) ];

Последовательности

Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.

Приведенные ниже примеры рассчитаны на mysql .

Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:

CREATE TABLE tableName ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), -- другие строки );

Для того, чтобы заново пронумеровать строки с помощью автоматически генерируемых значений (например, при удалении большого количества строк), можно удалить колонку, содержащую такие значения и создать ее заново. Обратите внимание: такая таблица не должна быть частью объединения.

ALTER TABLE tableName DROP id; ALTER TABLE tableName ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);

По умолчанию значения, генерируемые с помощью AUTO_INCREMENT , начинаются с 1. Для того, чтобы установить другое начальное значение достаточно указать, например, AUTO_INCREMENT = 100 — в этом случае нумерация строк начнется со 100.

• Веб-разработка
• Программирование
• SQL

Оператор SQL INSERT для вставки данных в таблицу БД

Оператор SQL INSERT предназначен для вставки значений столбцов в таблицу баз данных. В результате его применения в таблице базы данных появляется новая строка.

Оператор INSERT имеет следующий синтаксис:

INSERT INTO ИМЯ_ТАБЛИЦЫ [(ИМЕНА СТОЛБЦОВ)] VALUES (ВСТАВЛЯЕМЫЕ_ЗНАЧЕНИЯ)

Квадратные скобки [], в которые заключен элемент запроса (ИМЕНА СТОЛБЦОВ), означают, что этот элемент является необязательным.

Вставка значений в таблицу с указанием или без указания столбцов

Рассмотрим подробнее применение оператора INSERT с указанием имён столбцов и без указания, остановимся на случаях, когда указывать имена столбцов всё же требуется.

Будем работать с базой данных портала объявлений. В ней есть таблица ADS, содержащая данные о объявлениях, поданных за неделю.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

А скрипт для создания базы данных "Портал объявлений 1", её таблицы и заполения таблицы данных - в файле по этой ссылке .

Для использующих же MySQL приводим содержание оператора CREATE для создания таблицы:

CREATE TABLE ADS ( Id INT (11) NOT NULL DEFAULT '100', Category varchar (25) DEFAULT 'Some Category', Part varchar (25) DEFAULT 'Some Part', Units INT (5) DEFAULT NULL , Money INT (10) DEFAULT NULL , PRIMARY KEY (Id) )

Пример 1. Итак, есть база данных портала объявлений. Таблица ADS выглядит так:

Для вставки новой строки в эту таблицу на MySQL используем следующий запрос:

INSERT INTO ADS (Id, Category, Part, Units, Money) VALUES (13, 'Недвижимость', 'Гаражи', 22, 4620)

Или без указания имён столбцов:

INSERT INTO ADS VALUES (13, 'Недвижимость', 'Гаражи', 22, 4620)

MS SQL Server в ответ на такой запрос выдаст сообщение об ошибке, так как при создании таблицы было указано, что значения столбца Id являются идентификаторами и вставляются при добавлении новых строк автоматически с приращением 1. Поэтому на MS SQL Server нужно использовать следующий запрос (можете скопировать его и вставить в окно запросов):

USE adportal1; INSERT INTO ADS (Category, Part, Units, Money) VALUES ('Недвижимость', 'Гаражи', 22, 4620);

В результате выполнения запроса в таблице появится новая строка:

Из примера видно, что для вставки числовых значений в таблицу значения нужно указывать без кавычек, а для вставки строковых значений - в одинарных кавычках.

В запросе на вставку данных можно список столбцов можно указать не в том порядке, который задан при создании таблицы, и тогда данные следует указывать также в изменённом порядке.

Пример 2. Таким будет запрос на MySQL, в котором порядок следования столбцов изменён:

INSERT INTO ADS (Category, Id, Money, Part, Units) VALUES ('Недвижимость', 13, 4620, 'Гаражи', 22)

В результате выполнения запроса в таблице появится такая же новая строка, как и в примере 1.

Если вы используете MS SQL Server, то в запросе не нужно указывать столбец Id и запрос с изменённым порядком следования столбцов будет таким:

USE adportal1; INSERT INTO ADS (Category, Money, Part, Units) VALUES ('Недвижимость', 4620, 'Гаражи', 22)

Вставка значений по умолчанию (DEFAULT) и неопределённых значений (NULL)

Заметим, что при создании таблицы было предусмотрено, что значения столбцов могут иметь значения по умолчанию (DEFAULT). На MySQL для столбца Id можно предусмотреть значение 100. На MS SQL Server со строгим автоматическим приращением идентификатора это не допускается. В остальном же всё одинаково: для стобцов Category и Part значения по умолчанию - соответственно Some Category и Some Part, для столбцов Units и Money - значения NULL. Если в запросе на вставку данных на MySQL некоторые столбцы отсутствуют, то в них будут вставлены значения по умолчанию. На MS SQL в качестве значений нужно указать DEFAULT.

Пример 3. База данных и таблица - те же.

Запрос на MySQL. Вставим новые значения, указывая лишь столбец Id и его значение:

INSERT INTO ADS (Id) VALUES (14)

Все столбцы кроме Id получили значения по умолчанию. После выполнения этого запроса новая строка будет содержать следующие данные:

На MS SQL Server такой запрос недопустим.

Поскольку все столбцы могут иметь значения по умолчанию, можно использовать в запросе на вставку данных ключевое слово DEFAULT и не указывать имена столбцов.

Пример 4. Запрос на MySQL. Вставим новые значения, используя ключевое слово DEFAULT и не указывая имён столбцов:

INSERT INTO ADS VALUES ( DEFAULT , DEFAULT , DEFAULT , DEFAULT , DEFAULT )

Теперь все столбцы получили значения по умолчанию. После выполнения этого запроса новая строка будет содержать следующие данные:

Запрос на MS SQL Server (без указания столбца Id):

USE adportal1; INSERT INTO ADS VALUES ( DEFAULT , DEFAULT , DEFAULT , DEFAULT )

Вместо многократного использования слова DEFAULT можно использовать конструкцию DEFAULT VALUES, которая есть во многих диалектах SQL. Следует помнить, что в MySQL эта конструкция отсутствует.

Пример 5. Вставим новые значения, используя констукцию DEFAULT VALUES (запрос можно использовать и на MS SQL Server c предваряющей конструкцией USE adportal1):

INSERT INTO ADS DEFAULT VALUES

После выполнения этого запроса новая строка будет содержать следующие данные:

Вставка значений с использованием оператора SET в MySQL

В MySQL дополнительно существует альтернативная конструкция для вставки значений в таблицу с использованием оператора SET. Она похожа на конструкцию оператора UPDATE и имеет следующий синтаксис:

INSERT INTO ИМЯ_ТАБЛИЦЫ SET ИМЯ_СТОЛБЦА_1=ЗНАЧЕНИЕ, ИМЯ_СТОЛБЦА_2=ЗНАЧЕНИЕ, . ИМЯ_СТОЛБЦА_N=ЗНАЧЕНИЕ

В подобных запросах можно указывать имена не всех столбцов, при этом не указанные столбцы принимают значения по умолчанию.

Пример 6. База данных и таблица - те же, что и в предыдущих примерах.

Вставим в таблицу строку, при этом столбцы Units и Money примут значения по умолчанию:

INSERT INTO ADS SET Category='Недвижимость', Part='Гаражи'

В результате выполнения запроса в таблице появится новая строка:

Использование механизма автоматического приращения при вставке данных

Механизм автоматического приращения предназначен для автоматического генерирования уникальных значений первичного ключа в таблице. При его использовании не требуется проверять все значения первичного ключа, чтобы определить максимальное значение. При создании новой записи можно указать в качестве значения первичного ключа 0 или NULL и в таблицу будет автоматически вставлено значение, увеличенное на единицу по сравнению с максимальным значением.

В разных диалектах SQL синтаксис автоматического приращения различается. В MySQL используется ключевое слово AUTO_INCREMENT. Таблица с использованием механизма AUTO_INCREMENT в MySQL создаётся так:

CREATE TABLE ADS ( Id INT (11) NOT NULL AUTO_INCREMENT , Category varchar (25) DEFAULT 'Some Category', Part varchar (25) DEFAULT 'Some Part', Units INT (5) DEFAULT NULL , Money INT (10) DEFAULT NULL , PRIMARY KEY (Id) )

В SQL Server используется ключевое слово IDENTITY (N, M), где N - начальное значение столца, M - шаг приращения. Так, указав IDENTITY (1, 1) мы обеспечим начальное значение первичного ключа 1 и приращение на 1 значения при каждой вставке новой строки:

CREATE TABLE ADS ( Id int IDENTITY (1, 1) PRIMARY KEY , Category varchar (25) DEFAULT 'Some Category', Part varchar (25) DEFAULT 'Some Part', Units INT (5) DEFAULT NULL , Money INT (10) DEFAULT NULL )

Вставка нескольких строк в таблицу

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

Для этого применяется либо однострочный, либо многострочный оператор INSERT. Рассмотрим сначала синтаксис однострочного варианта.

Пример 7. Если используется механизм автоматического приращения значений первичного ключа, то на MySQL вставить новые строки в таблицу можно, применив несколько раз оператор INSERT и указав в качестве значений первичного ключа 0 или NULL:

INSERT INTO ADS VALUES ( NULL , 'Электротехника', 'Телевизоры', 127, 8255); INSERT INTO ADS VALUES ( NULL , 'Электротехника', 'Холодильники', 137, 8905); INSERT INTO ADS VALUES ( NULL , 'Стройматериалы', 'Регипс', 112, 11760); INSERT INTO ADS VALUES ( NULL , 'Досуг', 'Книги', 96, 6240);

Допустим, перед вставкой новых строк записи в таблице завершались строкой со значением первичного ключа 5. В результате выполнения запроса в таблице появятся новые строки:

На MS SQL Server нет необходимости указывать значения столбца Id. Аналогичный запрос будет следующим:

USE adportal1; INSERT INTO ADS VALUES ('Электротехника', 'Телевизоры', 127, 8255); INSERT INTO ADS VALUES ('Электротехника', 'Холодильники', 137, 8905); INSERT INTO ADS VALUES ('Стройматериалы', 'Регипс', 112, 11760); INSERT INTO ADS VALUES ('Досуг', 'Книги', 96, 6240);

В MySQL и SQL Server существует многострочный оператор INSERT. Его отличие от однострочного варианта в том, что для вставки нескольких строк он используется один раз, а после ключевого слова VALUES указывается не один, а несколько списков значений добавляемых строк.

Пример 8. Вставим строки с теми же значениями, что и в предыдущем примере, используя многострочный оператор INSERT.

Запрос на MySQL:

INSERT INTO ADS VALUES ( NULL , 'Электротехника', 'Телевизоры', 127, 8255), ( NULL , 'Электротехника', 'Холодильники', 137, 8905), ( NULL , 'Стройматериалы', 'Регипс', 112, 11760), ( NULL , 'Досуг', 'Книги', 96, 6240);

Запрос на MS SQL Server:

USE adportal1; INSERT INTO ADS VALUES ('Электротехника', 'Телевизоры', 127, 8255), ('Электротехника', 'Холодильники', 137, 8905), ('Стройматериалы', 'Регипс', 112, 11760), ('Досуг', 'Книги', 96, 6240);

Результат применения - тот же, что и в предыдущем примере.

Примеры запросов к базе данных "Портал объявлений-1" есть также в уроках об операторах UPDATE, DELETE, HAVING и UNION.

SQL INSERT INTO: примеры вставки строк в таблицу БД MySQL

SQL оператор INSERT используется для вставки записей в существующую таблицу.

Синтаксис этого оператора следующий:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name. )] VALUES (expression. ),(. ). или INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name. )] SELECT . или INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name=expression, col_name=expression, .

Создадим тестовую таблицу

Давайте создадим таблицу table1 со столбцами a, b, c в нашей MySQL базе данных:

CREATE TABLE `table1` ( `a` INT(11), `b` INT(11), `c` INT(11) );

Запрос на вставку строки

Простой запрос, который вставляет строку со столбцами 111, 222 и 333 выглядит так:

INSERT INTO table1 (a, b, c) VALUES (111, 222, 333);

Еще один способ сделать то же самое:

INSERT INTO table1 SET a=111, b=222, c=333;

Столбцы, которые вы не перечислите заполняются значениями по умолчанию, которые вы предусматриваете при создании таблицы, даже если это просто NULL.

У таблиц обычно есть поле id с первичным ключом (PRIMARY KEY) таблицы. Если этому полю установлено значение AUTOINCREMENT т.е. оно заполняется автоматически, то в таком случае вы не должны его перечислять в списке столбцов оператора INSERT.

Вставка без перечисления столбцов

Если количество значений, которые мы вставляем = количеству столбцов в таблице, то можно не перечислять столбцы, и наш запрос может выглядеть так:

INSERT INTO table1 VALUES (111, 222, 333);

Этот способ крайне не рекомендуется. Дело в том, что со временем вы можете менять таблицы, например добавлять в них новые столбцы, а это значит, что все запросы записанные таким способом просто перестанут работать и вам придется менять их по всему вашему приложению. Поэтому, навсегда забываем этот способ. Я его привел, только чтобы вы так не делали.

Вставка сразу нескольких строк с помощью INSERT INTO

Если нам нужно вставить несколько строк, то мы просто перечисляем группы значений через запятую выглядит это так:

INSERT INTO table1 (a, b, c) VALUES (1, 222, 333), (2, 555, 666), (3, 555, 333);

Таким образом мы вставили 3 строки в нашу таблицу table1. Их может быть и больше. В MySQL четкого предела нет, однако он все таки существует и зависит от параметра max_allowed_packet который ограничивает размер запроса. Если вы установите SET GLOBAL max_allowed_packet=524288000; то размер запроса будет ограничен 500MB но делайте это в очень крайнем случае. Обычно всегда можно найти решение и разделить 1 большой запрос, на несколько более мелких и вставлять например не больше 1000 строк за один цикл.

Как вставить значение из другой таблицы INSERT INTO . SELECT .

Допустим у нас есть еще одна таблица table2 которая по структуре точно такая же как и первая. Нам в таблицу table2 нужно вставить все строки из table1.

Вставляем значения из table1 в таблицу table2:

INSERT INTO table2 (a, b, c) SELECT a, b, c FROM table1;

Вам следует позаботиться об уникальности ключей, если они есть в таблице, в которую мы вставляем. Например при дублировании PRIMARY KEY мы получим следующее сообщение об ошибке:

/* ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY' */

Если вы делаете не какую-то единичную вставку при переносе данных, а где-то сохраните этот запрос, например в вашем PHP скрипте, то всегда перечисляйте столбцы.

Как не рекомендуется делать (без перечисления столбцов):

INSERT INTO table2 SELECT * FROM table1;

Если у вас со временем изменится количество столбцов в таблице, то запрос перестанет работать. При выполнении запроса MySQL в лучшем случае просто будет возвращать ошибку:

/* Ошибка SQL (1136): Column count doesn't match value count at row 1 */

Либо еще хуже: значения вставятся не в те столбцы.

Вставка из другой таблицы с условием INSERT INTO . SELECT . WHERE .

А теперь представим, что нам нужно вставить только те строки из table1, у которых столбец "c" равен 333. Тогда наш запрос будет выглядеть так

INSERT INTO table2 (a, b, c) SELECT a, b, c FROM table1 WHERE c = 333;

То есть мы просто вставляем данные в таблицу, которые выбрали из другой таблицы при помощи обычного SELECT запроса

Теперь представим, что у нас в таблице table2 - 4 столбца, а в table1 - 3. При этом четвертый столбец в table2 обязательный. Чтобы выйти из этой ситуации, нужно передать какое-нибудь подходящее значение в этот лишний столбец. У нас чисто абстрактная задача, поэтому давайте передадим туда просто единицу.

INSERT INTO table2 (a, b, c, d) SELECT a, b, c, 1 FROM table1 WHERE c = 333;

Теперь в столбец d у нас записалась единица и проблема решена.

Вставка в определенный раздел INSERT INTO . PARTITION .

Если вам нужно вставить строки в определенный раздел таблицы, то нужно после таблицы указать PARTITION (название раздела), например так:

INSERT INTO table1 PARTITION (p1) VALUES(1, 2, 3);

Вставка в несколько разделов. Первая строка вставляется в раздел p1, а вторая в p2

INSERT INTO table1 PARTITION (p1, p2) VALUES(1, 2, 3), (4, 5, 6);

Вставка строк, некоторые из которых уже существуют в целевой таблице

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

Игнорирование INSERT IGNORE INTO

Например если мы вставляем строку с PK = 1, и при этом в таблице уже есть PK = 1 то MySQL выдаст ошибку:

/* ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' */

Выполнение запроса на этом прервется, однако нам в некоторых случаях хотелось бы просто вставить данные, игнорируя ошибки. В этом нам поможет INSERT IGNORE INTO:

INSERT IGNORE INTO table1 (a, b, c) VALUES (1, 222, 333), (2, 555, 666), (3, 555, 333);

Просто добавляем IGNORE в наш запрос и ошибки будут игнорироваться

Вставка с заменой существующих значений REPLACE INTO

REPLACE работает также INSERT, но если совпадают уникальные ключи, то старая строка (или строки!) удаляется до вставки новой.

В таком случае наш пример выглядит следующим образом:

REPLACE INTO table1 (a, b, c) VALUES (1, 222, 333), (2, 555, 666), (3, 555, 333);

Обновление некоторых полей, при существовании строк ON DUPLICATE KEY UPDATE

При совпадении ключей, мы можем также заменить некоторые или все поля в строке.

Наш запрос будет выглядеть так:

INSERT INTO table1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=VALUES(c);

В данном примере если у нас какой-то уникальный ключ совпадает, то мы не производим вставку, а обновляем существующую строку или строки путем присваивания столбцу "c" значения, которое у нас перечислено в VALUES.

Иными словами, если ключ совпадает, то мы просто обновим данные столбца "с" а остальные столбцы трогать не будем.

Иногда нам нужно при совпадении ключей обновить все значения. Этом можно сделать просто перечислив все столбцы:

INSERT INTO table1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c);

При обновлении столбцов мы также можем использовать разные выражения, например:

INSERT INTO table1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Выражения для вставляемых значений в VALUES

При вставке значений, мы можем использовать выражения и даже использовать в своих выражениях значения других столбцов.

Пример использования выражений:

INSERT INTO table1 (a, b, c) VALUES (1, 222, 333 + a), (2, 555, 666 + b);

Таким образом мы для формирования столбца "c" использовали столбцы "a" и "b".

Приоритет вставки INSERT LOW_PRIORITY / HIGH_PRIORITY

Установление приоритета нужно для решение проблем с конкурентными вставками. При вставках происходит блокировка строк и если 2 INSERT запроса требуют блокировки одних и тех же строк, для своего выполнения, то иногда может потребоваться повысить или понизить приоритет некоторых запросов, по отношению к другим. Это можно сделать указав приоритет LOW_PRIORITY или HIGH_PRIORITY

Наш запрос будет выглядеть так для LOW_PRIORITY:

INSERT LOW_PRIORITY INTO table1 (a, b, c) VALUES(1, 2, 3);

INSERT HIGH_PRIORITY INTO table1 (a, b, c) VALUES(1, 2, 3);

Свежие записи

• SQL UPDATE: примеры обновления строк в таблице
• PHP: substr и мощные альтернативы, чтобы вырезать часть строки
• Степень в C, C++ и С#: как возвести число в любую степень, побитовые операции
• Скачать ShowKeyPlus: ссылка на последнюю официальную версию, скачивание, установка
• Как создать файл в Linux: 12 способов
• SQL INSERT INTO: примеры вставки строк в таблицу БД MySQL
• PHP: str_replace - замена или удаление подстроки в строке
• Функция date() в php: распространенные форматы, примеры, советы
• cURL в PHP: примеры POST, GET запросов с headers, cookie, JSON и многопоточностью
• JSON в PHP: примеры json_encode, json_decode, работа с кириллицей и utf-8
• Файл gitignore - примеры и документация
• Сортировка массивов в php: ksort, asort и прочие sort'ы
• jQuery onclick: как выполнить код при клике на кнопку
• 500 Internal Server Error - в чем причина?
• SMTP от Yandex: как отправить письма из PHP - пример настроек
• No such file or directory - в чем причина?
• Как получить первый элемент массива в php
• Cтроку в массив по разделителю в PHP: explode, str_split, strtok - что выбрать?
• 301 редирект на https с www через htaccess
• Как в php добавить к массиву другой массив?
• 301 редирект на https без www с помощью htaccess
• Регулярные выражения в PHP
• PHP json_decode — как декодировать результат в массив?
• Yii2 ActiveRecord шпаргалка по составлению запросов
• Поиск подстроки в строке с помощью PHP
• Отправка почты на php
• Как подключить php код в html
• Конвертировать массив в строку при помощи PHP
• Подключение одного php файла в другой
• Подборка ссылок для веб-разработчика
• Проблема с кириллицей в PHPWord