Оператор INSERT вставляет новые записи в таблицу. При этом значения столбцов могут представлять собой литеральные константы, либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT ; во втором случае будет вставлено столько строк, сколько возвращается подзапросом.
Синтаксис оператора следующий:
| { DEFAULT VALUES }
INSERT INTO [ (,...) ]
{ VALUES (,…) }
Как видно из представленного синтаксиса, список столбцов не является обязательным (об этом говорят квадратные скобки в описании синтаксиса). В том случае, если он отсутствует, список вставляемых значений должен быть полный, то есть обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, эти значения должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. В качестве примера рассмотрим вставку строки в таблицу Product, созданную следующим оператором CREATE TABLE :
CREATE TABLE product
maker char (1 ) NOT NULL ,
model varchar (4 ) NOT NULL ,
type varchar (7 ) NOT NULL
Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором:
INSERT INTO Product
VALUES ("B" , 1157 , "PC" ) ;
Если задать список столбцов, то можно изменить «естественный» порядок их следования:
INSERT INTO Product (type, model, maker)
VALUES ("PC" , 1157 , "B" ) ;
Казалось бы, это совершенно излишняя возможность, которая делает конструкцию только более громоздкой. Однако она становится выигрышной, если столбцы имеют значения по умолчанию. Рассмотрим следующую структуру таблицы:
CREATE TABLE product_D
maker char (1 ) NULL ,
model varchar (4 ) NULL ,
type varchar (7 ) NOT NULL DEFAULT "PC"
Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два - NULL, а последний столбец - type - PC). Теперь мы могли бы написать:
INSERT INTO Product_D (model, maker)
VALUES (1157 , "B" ) ;
В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию - PC. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL , запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL .
Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT :
INSERT INTO Product_D
VALUES ("B" , 1158 , DEFAULT ) ;
Поскольку все столбцы имеют значения по умолчанию, для вставки строки со значениями по умолчанию можно было бы написать:
INSERT INTO Product_D
VALUES (DEFAULT , DEFAULT , DEFAULT ) ;
Однако для этого случая предназначена специальная конструкция DEFAULT VALUES (см. синтаксис оператора), с помощью которой вышеприведенный оператор можно переписать в виде
INSERT INTO Product_D DEFAULT VALUES ;
Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK , ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отклонена. Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Product_D все строки из таблицы Product, относящиеся к моделям персональных компьютеров (type = ‘PC’). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы:
Использование в подзапросе символа «*» является в данном случае оправданным, так как порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы применить список столбцов либо в операторе INSERT , либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов:
Здесь, также как и ранее, можно указывать не все столбцы, если требуется использовать имеющиеся значения по умолчанию, например:
В данном случае в столбец type таблицы Product_D будет подставлено значение по умолчанию PC для всех вставляемых строк.
Отметим, что при использовании подзапроса, содержащего предикат, будут вставлены только те строки, для которых значение предиката равно TRUE (не UNKNOWN !). Другими словами, если бы столбец type в таблице Product допускал бы NULL -значение, и это значение присутствовало бы в ряде строк, то эти строки не были бы вставлены в таблицу Product_D.
Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании конструктора строки в предложении VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL . Так если нам требуется вставить несколько строк при помощи одного оператора INSERT , можно написать:
INSERT INTO Product_D
SELECT "B" AS maker, 1158 AS model, "PC" AS type
UNION ALL
SELECT "C" , 2190 , "Laptop"
UNION ALL
SELECT "D" , 3219 , "Printer" ;
Использование UNION ALL предпочтительней UNION даже, если гарантировано отсутствие строк-дубликатов, так как в этом случае не будет выполняться проверка для исключения дубликатов.
Следует отметить, что вставка нескольких кортежей с помощью конструктора строк уже реализована в Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft.
Язык структурированных запросов) - универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных.
SQL
Server
2008. С учетом этой возможности, последний запрос можно переписать в виде:
INSERT INTO Product_D VALUES
("B" , 1158 , "PC" ) ,
("C" , 2190 , "Laptop" ) ,
Общий синтаксис:
а) вставка одной новой строки в таблицу:
INSERT <таблица> [(столбцы)]
VALUES (значения)
б) Вставка в таблицу одной или более строк, взятых из источника, указанного в подзапросе:
INSERT <таблица> [(столбцы)]
Правила:
Количество элементов в разделе «Столбцы» должно совпадать с количеством элементов в разделе «Значения»; типы данных должны совпадать либо допускать неявное преобразование;
В случае, когда раздел «Столбцы» отсутствует, раздел «Значения» должен содержать столько же значений, сколько всего столбцов в редактируемой таблице, кроме того, значения должны перечисляться в том же порядке, в каком объявлены столбцы таблицы; поэтому рекомендуется всегда явно указывать список столбцов, в которые будут добавляться новые значения;
В случае использования синтаксиса (б) подзапрос SELECT может быть адресован либо к этой же таблице, либо к другой таблице базы данных, либо к другой базе данных на сервере;
Если для некоторого столбца таблицы задано значение по умолчанию, и требуется в добавляемой записи использовать именно это значение, то в разделе «Значения» для этого столбца необходимо написать ключевое слово 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, "Смирнов С. С.")
Модификация существующих записей (update)
Общий синтаксис:
UPDATE <таблица>
SET <столбец> = <значение> [, <столбец> = <значение>]
Примеры :
SET weight = 210
SET material_name = "Олово"
WHERE material_id = 2
SET department_id = NULL,
delivery_date = delivery_date + 1 /*увеличение даты поставки на день
WHERE delivery_id = 1 OR department_id > 3
SET detail_name = detail_name + "!!!"
WHERE detail_name LIKE "_а%" AND weight BETWEEN 6 AND 10
Удаление записей из таблицы (delete)
Общий синтаксис:
DELETE<таблица>
<условие>
Примеры :
WHERE detail_id IN (2, 5, 8)
WHERE weight IS NULL
Задание:
Заполните базу данных средствами ManagementStudio. Для этого следует воспользоваться командой «Открыть таблицу» контекстного меню таблицы в «Обозревателе объектов» (рис. 4.1).
Запросы на выборку данных
Общий синтаксис запроса select
SELECT <список столбцов>
FROM <таблица(-ы) источник>
Разделы SELECT и FROM обязательно должны присутствовать в каждом запросе; остальные разделы могут отсутствовать или присутствовать частично.
Примеры реализации запросов
Наиболее простые запросы:
1. Выбрать название и вес деталей
Рис. 5.1. Результаты выполнения запроса №1
2. Выбрать всю информацию из таблицы материалов
Рис. 5.2. Результаты выполнения запроса №2
Уникальность DISTINCT
3. Выбрать уникальные коды поставщиков из таблицы поставок
Рис. 5.3. Результаты выполнения запроса №3
Ограничение WHERE
4. Выбрать количество и даты поставки детали с кодом 1
Рис. 5.4. Результаты выполнения запроса №4
5. Выбрать названия поставщиков с кодами 1, 4 и 6
Рис. 5.5. Результаты выполнения запроса №5
6. Выбрать всю информацию о поставках, сделанных до 1.10.2009
Рис. 5.6. Результаты выполнения запроса №6
7. Выбрать всю информацию о деталях, не начинающихся на букву «В» (в любом регистре) и чей вес меньше 50
Рис. 5.7. Результаты выполнения запроса №7
8. Выбрать название и код материала для деталей с весом от 5 до 10 г или имеющих в названии букву «н» в третьей позиции
Рис. 5.8. Результаты выполнения запроса №8
9. Выбрать названия поставщиков длиной не больше 15-и символов
Рис. 5.9. Результаты выполнения запроса №9
10. Выбрать месяца и годы поставок деталей
Рис. 5.10. Результаты выполнения запроса №10
Сортировка ORDER BY
11. Упорядочить поставки сначала по коду поставщика, а затем по дате поставки
Рис. 5.11. Результаты выполнения запроса №11
12. Выбрать названия поставщиков с кодами 4, 6, 8, 9, упорядоченных по алфавиту в обратном порядке
Рис. 5.12. Результаты выполнения запроса №12
Агрегация и группировка GROUP BY
Рис. 5.13. Результаты выполнения запроса №13
14. Определить средний вес деталей из материала с кодом 2
Рис. 5.14. Результаты выполнения запроса №2
15. Из поставок, совершенных до 1.10.2009, выбрать самую крупную поставку и самую мелкую
Рис. 5.15. Результаты выполнения запроса №15
16. Для поставщиков с кодами в диапазоне 5 - 8 посчитать суммарное количество поставленных ими деталей
Рис. 5.16. Результаты выполнения запроса №16
Рис. 5.17. Результаты выполнения запроса №17
Ограничение на группировки HAVING
18. Выбрать материалы, для которых суммарный вес выполненных из них деталей не больше 20
Рис. 5.18. Результаты выполнения запроса №18
19. Из поставок 2008-го года выбрать детали, поставлявшиеся более одного раза
Рис. 5.19. Результаты выполнения запроса №19
Преобразование типов CAST
20. Получить сведения о датах поставок в текстовом виде
Рис. 5.20. Результаты выполнения запроса №20
21. Получить сведения из таблицы деталей в виде строк «Деталь X имеет вес Y»
Рис. 5.21. Результаты выполнения запроса №21
Альтернативы CASE
22. Разделить детали на легкие (весом до 20), средние (между 20 и 50) и тяжелые
Рис. 5.22. Результаты выполнения запроса №22
Обработка NULL-значений
23. Получить сведения о деталях и их весах, причем если у детали вес не задан, то вместо NULL написать -100
Рис. 5.23. Результаты выполнения запроса №23
Функция существования EXISTS
24. Выбрать название и код материала только тех деталей, которые когда-либо поставлялись
Рис. 5.24. Результаты выполнения запроса №24
25. Выбрать названия тех материалов, из которых не изготовлена ни одна деталь
Рис. 5.25. Результаты выполнения запроса №25
Подзапросы
26. Получить сведения о самой последней (по дате) поставке
Рис. 5.26. Результаты выполнения запроса №26
27. Получить все поставки деталей из материала с кодом 2
Рис. 5.27. Результаты выполнения запроса №27
28. Для каждого поставщика получить сведения о самой первой (по дате) его поставке
Рис. 5.28. Результаты выполнения запроса №28
29. Для каждого поставщика получить его имя и дату последнего заказа
Рис. 5.29. Результаты выполнения запроса №29
Объединение таблиц JOIN
30. Получить таблицу вида: название детали, название материала, из которого выполнена эта деталь
Рис. 5.30. Результаты выполнения запроса №30
31. Получить таблицу вида: поставщик, название детали, количество и дата поставки для деталей, у которых задан вес
Рис. 5.31. Результаты выполнения запроса №31
32. Выбрать всю информацию о тех деталях, которые когда-либо поставлялись
Рис. 5.32. Результаты выполнения запроса №32
Рис. 5.33. Результаты выполнения запроса №33
34. Выбрать названия материалов и сделанных из них деталей
Рис. 5.34. Результаты выполнения запроса №34
35. Получить все данные о поставщиках, поставках и деталях
Рис. 5.35. Результаты выполнения запроса №35
Объединение таблиц UNION
36. Получить таблицу названий и весов деталей, причем последняя строка таблицы должна содержать итоги в виде суммарного веса всех деталей
Рис. 5.36. Результаты выполнения запроса №36
37. Получить таблицу из двух полей, где первое поле – название детали, материала, поставщика или дата поставки, а второе поле – длина строки из первого поля
Рис. 5.37. Результаты выполнения запроса №37
Содержание статьи
1.
Самые простые MySQL запросы
2.
Простые SELECT (выбрать) запросы
3.
Простые INSERT (новая запись) запросы
4.
Простые UPDATE (перезаписать, дописать) запросы
5.
Простые DELETE (удалить запись) запросы
6.
Простые DROP (удалить таблицу) запросы
7.
Сложные MySQL запросы
8.
MySQL запросы и переменные PHP
1. Самые простые SQL запросы
1. Выведет список ВСЕХ баз.SHOW databases;
2. Выведет список ВСЕХ таблиц в Базе Данных base_name.
SHOW tables in base_name;
2. Простые SELECT (выбрать) запросы к базе данных MySQL
SELECT – запрос, который выбирает уже существующие данные из БД. Для выбора можно указывать определённые параметры выбора. Например, суть запроса русским языком звучит так - ВЫБРАТЬ такие-то колонки ИЗ такой-то таблицы ГДЕ параметр такой-то колонки равен значению.1. Выбирает ВСЕ данные в таблице tbl_name.
SELECT * FROM tbl_name;
2. Выведет количество записей в таблице tbl_name.
SELECT count(*) FROM tbl_name;
3. Выбирает (SELECT) из(FROM) таблицы tbl_name лимит (LIMIT) 3 записи, начиная с 2.
SELECT * FROM tbl_name LIMIT 2,3;
4. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id по порядку.
SELECT * FROM tbl_name ORDER BY id;
5. Выбирает (SELECT) ВСЕ записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id в ОБРАТНОМ порядке.
SELECT * FROM tbl_name ORDER BY id DESC;
6. Выбирает (SELECT
) ВСЕ (*) записи из (FROM
) таблицы users
и сортирует их (ORDER BY
) по полю id
в порядке возрастания, лимит (LIMIT
) первые 5 записей.
SELECT * FROM users ORDER BY id LIMIT 5;
7. Выбирает все записи из таблицы users
, где поле fname
соответствует значению Gena
.
SELECT * FROM users WHERE fname="Gena";
8. Выбирает все записи из таблицы users
, где значение поля fname
начинается с Ge
.
SELECT * FROM users WHERE fname LIKE "Ge%";
9. Выбирает все записи из таблицы users
, где fname
заканчивается на na
, и упорядочивает записи в порядке возрастания значения id
.
SELECT * FROM users WHERE fname LIKE "%na" ORDER BY id;
10. Выбирает все данные из колонок fname
, lname
из таблице users
.
SELECT fname, lname FROM users;
11.
Допустим у Вас в таблице пользовательских данных есть страна. Так вот если Вы хотите вывести ТОЛЬКО список встречающихся значений (чтобы, например, Россия не выводилось 20 раз, а только один), то используем DISTINCT. Выведет, из массы повторяющихся значений Россия, Украина, Беларусь. Таким образом, из таблицы users
колонки country
будут выведены ВСЕ УНИКАЛЬНЫЕ значения
SELECT DISTINCT country FROM users;
12. Выбирает ВСЕ данные строк из таблицы users
где age
имеет значения 18,19 и 21.
SELECT * FROM users WHERE age IN (18,19,21);
13.
Выбирает МАКСИМАЛЬНОЕ значение age
в таблице users
. То есть если у Вас в таблице самое большее значение age
(с англ. возраст) равно 55, то результатом запроса будет 55.
SELECT max(age) FROM users;
14. Выберет данные из таблицы users
по полям name
и age
ГДЕ age
принимает самое маленькое значение.
SELECT name, min(age) FROM users;
15. Выберет данные из таблицы users
по полю name
ГДЕ id
НЕ РАВЕН 2.
SELECT name FROM users WHERE id!="2";
3. Простые INSERT (новая запись) запросы
INSERT – запрос, который позволяет ПЕРВОНАЧАЛЬНО вставить запись в БД. То есть создаёт НОВУЮ запись (строчку) в БД.1. Делает новую запись в таблице users , в поле name вставляет Сергей, а в поле age вставляет 25. Таким образом, в таблицу дописывается новая строки с данными значениями. Если колонок больше, то они оставшиеся останутся либо пустыми, либо с установленными по умолчанию значениями.
INSERT INTO users (name, age) VALUES ("Сергей", "25");
4. Простые UPDATE запросы к базе данных MySQL
UPDATE – запрос, который позволяет ПЕРЕЗАПИСАТЬ значения полей или ДОПИСАТЬ что-то в уже существующей строке в БД. Например, есть готовая строка, но в ней нужно перезаписать параметр возраста, так как он изменился со временем.1. В таблице users age становится 18.
UPDATE users SET age = "18" WHERE id = "3";
2.
Всё то же самое, что и в первом запросе, просто показан синтаксис запроса, где перезаписываются два поля и более.
В таблице users
ГДЕ id равно 3 значение поля age
становится 18, а country
Россия.
UPDATE users SET age = "18", country = "Россия" WHERE id = "3";
5. Простые DELETE (удалить запись) запросы к базе данных MySQL
DELETE – запрос, который удаляет строку из таблицы.1. Удаляет строку из таблицы users ГДЕ id равен 10.
DELETE FROM users WHERE id = "10";
6. Простые DROP (удалить таблицу) запросы к базе данных MySQL
DROP – запрос, который удаляет таблицу.1. Удаляет целиком таблицу tbl_name .
DROP TABLE tbl_name;
7. Сложные запросы к базе данных MySQL
Любопытные запросы, которые могут пригодиться даже опытным пользователямSELECT id,name,country FROM users,admins WHERE TO_DAYS(NOW()) - TO_DAYS(registration_date) <= 14 AND activation != "0" ORDER BY registration_date DESC;
Данный сложный запрос ВЫБИРАЕТ колонки id,name,country
В ТАБЛИЦАХ users,admins
ГДЕ registration_date
(дата) не старше 14
дней И activation
НЕ РАВНО 0
, СОРТИРОВАТЬ по registration_date
в обратном порядке (новое в начале).
UPDATE users SET age = "18+" WHERE age = (SELECT age FROM users WHERE male = "man");
Выше указан пример так называемого запроса в запросе
в SQL. Обновить возраст среди пользователей на 18+, где пол - мужской. Подобные варианты запроса не рекомендую. По личному опыту скажу, лучше создать несколько отдельных - они будут прорабатываться быстрее.
8. Запросы к базе данных MySQL и PHP
В MySQL запросы в PHP странице можно вставлять переменные в качестве сравниваемых и тп значений. Пара примеров1. Выбирает все записи из таблицы users , где поле fname соответствует значению переменной $name .
SELECT * FROM users WHERE fname="$name";
2. В таблице users
ГДЕ id равно 3 значение поля age
изменяется на значение переменной $age.
UPDATE users SET age = "$age" WHERE id = "3";
Внимание! Если Вам интересен какой-либо ещё пример, то пишите вопрос в комментарии!
В этой статье мы разберём, пожалуй, одни из самых важных SQL-запросов . Это запросы на добавление и удаление записей из таблицы базы данных . Поскольку, ОЧЕНЬ часто приходится добавлять новые записи в таблицу , причём делать это в автоматическом режиме, то данный материал обязателен к изучению.
Для начала SQL-запрос на добавление новой записи в таблицу :
INSERT INTO users (login, pass) values("TestUser", "123456")
При добавлении записи вначале идёт команда "INSERT INTO ", затем название таблицы, в которую мы вставляем запись. Далее идёт в круглых скобках названия полей, которые мы хотим заполнить. А затем в круглых скобках после слова "values " начинаем перечислять значения тех полей, которые мы выбрали. После выполнения этого запроса в нашей таблице появится новая запись.
Иногда требуется обновить запись в таблице , для этого существует следующий SQL-запрос :
UPDATE users SET login = "TestUser2", pass="1234560" WHERE login="TestUser"
Данный запрос является более сложным, так как он имеет конструкцию "WHERE ", но о ней чуть ниже. Вначале идёт команда "UPDATE ", затем имя таблицы, а после "SET " мы описываем значения всех полей, которые мы хотим изменить. Было бы всё просто, но встаёт вопрос: "А какую именно запись следует обновлять? ". Для этого существует "WHERE ". В данном случае мы обновляем запись, поле "login " у которой имеет значение "TestUser ". Обратите внимание, что если таких записей будет несколько, то обновятся абсолютно все ! Это очень важно понимать, иначе Вы рискуете потерять свою таблицу.
Давайте немного ещё поговорим о "WHERE ". Помимо простых проверок на равенство существуют так же и неравенства, а также логические операции: AND и OR .
UPDATE users SET login = "TestUser2", pass="1234560" WHERE id < 15 AND login="TestUser"
Данный SQL-запрос обновит те записи, id которых меньше 15 И поле "login " имеет значение "TestUser ". Надеюсь, Вы разобрались с конструкцией "WHERE ", потому что это очень важно. Именно "WHERE " используется при выборке записей из таблиц , а это самая частоиспользуемая задача при работе с базами данных.
И, напоследок, простой SQL-запрос на удаление записей из таблицы :
DELETE FROM users WHERE login="TestUser2"
После команды "DELETE FROM " идёт имя таблицы, в которой требуется удалить записи. Дальше описываем конструкцию "WHERE". Если запись будет соответствовать описанным условиям, то она будет удалена. Опять же обратите внимание, в зависимости от количества записей, удовлетворяющих условию после "WHERE ", может удалиться любое их количество.
Временами у программистов возникает жгучее желание прочитать что-то из базы данных. Программисты становятся нервными и раздражительными, теряют сон и лихорадочно тыкают пальцами в клавиатуру. Ради всеобщего блага и мира во всем мире рассмотрим несложную работу с базой данных из C# с помощью ADO.NET и OLE DB. Хотя данный механизм поддерживает разные базы данных вроде Oracle, здесь и сейчас будем использовать MS SQL Server.
Две основных задачи при работе с базой данных
2. Выполнение sql-команды, что-то делающей на сервере (insert, update, delete, вызов функции или хранимой процедуры
3. Последовательное чтение из select-выборки строки за строкой. В основном используется в веб-приложениях, в настольных проще скачать сразу всю выборку в оперативную память, экономия от чтения только нужных строк незначительна.
4. Редкий случай. Автоматическое обновление таблицы в базе на основании изменений в DataTable (как правило редактируемой через визуальный интрфейс). В реальной жизни данные обычно читаются через сложный запрос с кучей join или представление, так что автоматическая синхронизация не подходит.
Основные классы, используемые для этих целей: OleDbConnection - соединение с базой, создаем со строкой, содержащей параметры соеднинения, открываем, закрываем, OleDbCommand - создаем с экземпляром соединения и sql-командой, если нужно просто выполнить update или получить единичное значение, то хватит этого класса, OleDbDataAdapter - создается с OleDbCommand, специализируется на разовом чтении наборов строк в DataTable, может автоматически создавать колонки DataTable на основании выборки, переносить изменения из DataTable в таблицу в базе, OleDbDataReader - последовательное чтение строк в DataTable по одной за раз (он работает внутри OleDbDataAdapter), DataTable / DataSet - основной контейнер для данных. В перечислении OleDbType хранятся типы данных базы данных.
Using System.Data; using System.Data.OleDb; // строка соединения, используется система пользователей Windows (Integrated Security=SSPI;) string connString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=databaseName;Data Source=serverName"; // альтернативная строка соединения с иcпользованием аутентификации MS SQL Server // connString = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=databaseName;Connect Timeout=20;Data Source=serverName;Uid=userName;Pwd=userPassword;" OleDbConnection dbConn = new OleDbConnection(connString); dbConn.Open(); someDataTable = new DataTable(); OleDbDataAdapter dbAdapter = new OleDbDataAdapter("select COLUMN1, COLUMN2 from TEST_TABLE ORDER BY COLUMN2", dbConn); // внутренняя структура пустой таблицы будет создана автоматически на основании прочитанных данных, если структура таблицы уже задана (например через типизированный DataSet), то данные будут писаться в столбцы с совпадающими именами или добавятся новые столбцы dbAdapter.Fill(someDataTable); // альтернативный вариант для заполнения таблицы в DataSet // dbAdapter.Fill(someDataSet, "someDataTable"); dbConn.Close();
2. Выполнение sql-команды, что-то делающей на сервере (insert, update, delete, вызов функции или хранимой процедуры .
Особенно стоить отметить проблемы с датами. Реализация дат в.Net крайне кривая - изначально даты не могут быть пустыми, а реальной жизни они пустые сплошь и рядом. Самое правильное решение - использовать специальный класс даты, исправляющий косяки программистов из Microsoft. Более ленивые разработчики держат все даты в коде строками и конвертируют их в DateTime только при необходимости, например при записи в базу или DataTable. Nullable DateTime не спасает, так как пустая дата в интерфейсе должна выглядеть пустой строкой, а при записи в базу как DBNull.Value - и банальный null в коде не конвертируется в эти значения без дополнительных плясок с бубном.
Первый вариант подразумевает банальное склеивание строки запроса. Считается плохой практикой и особенно опасен в веб-приложениях, так как уязвим для хакерских атак. Проблема с пустыми датами так просто не решается. Кроме того при работе с датами появляется дополнительная проблема - разные форматы строк даты в зависимости от региональный настроек.Net Framework, среды разработки и sql-сервера. Выглядеть это может сюрреалистически - один и тот же запрос работает в SQL Managment Studio, но вылетает при выполнении из кода. Частично спасает особый формат строки дат, Тем не менее так нередко делают в маленьких программках для внутреннего пользования, о существовании которых внешний мир никогда не узнает.
OleDbCommand dbCommand = dbConn.CreateCommand(); dbCommand.CommandText = "INSERT INTO TEST_TABLE (INT_COLUMN, VARCHAR_COLUMN, DATETIME_COLUMN) VALUES (" + intVariable", " + stringVariable" , " + dateTimeVariable.ToString("yyyyMMdd") +")"; dbCommand.ExecuteNonQuery();
Правильный вариант подразумевает создание команды с набором строго типизированных параметров. Считайте, что предыдущего абзаца не было.
OleDbCommand dbCommand = dbConn.CreateCommand(); dbCommand.CommandText = "INSERT INTO TEST_TABLE (INT_COLUMN, VARCHAR_COLUMN, DATETIME_COLUMN) VALUES (?, ?, ?)"; dbCommand.Parameters.Add("INT_COLUMN", OleDbType.Integer).Value = intVariable; dbCommand.Parameters.Add("VARCHAR_COLUMN", OleDbType.VarChar).Value = stringVariable; if (stringDate == "") { dbCommand.Parameters.Add("DATETIME_COLUMN", OleDbType.DateTime).Value =DBNull.Value; } else { dbCommand.Parameters.Add("DATETIME_COLUMN", OleDbType.DateTime).Value = Convert.ToDateTime(stringDate); } dbCommand.ExecuteNonQuery();
Хранимая процедура вызывается точно так же, разнообразия ради другой вариант записи значений в параметры (он не связан именно с хранимой процедурой):
OleDbCommand someDbComm = new OleDbCommand("someStoredProcedure", this.dbConn); someDbComm.CommandType = CommandType.StoredProcedure; someDbComm.Parameters.Add("@parameter1", OleDbType.VarChar); someDbComm.Parameters.Add("@parameter2", OleDbType.VarChar); someDbComm.Parameters.Value = "У всякой проблемы всегда есть решение - простое, удобное и, конечно, ошибочное"; someDbComm.Parameters.Value = "Генри Луис Менкен"; someDbComm.ExecuteNonQuery();
Расширенный вариант описания параметра с указанием размера поля и привязкой к конкретной колонке таблицы.
DbCommand.Parameters.Add("VARCHAR_COLUMN", OleDbType.VarChar, 100, "VARCHAR_COLUMN").Value = stringVariable;
Если нам не нужна привязка параметра команды к определенному полю DataTable, то лучше всего не указывать размер вообще, так например если строка будет меньше указанной длинны Varchar, то добрый.Net фреймворк добавит в строку пробелов до указанной длины, испортив передаваемые на сервер данные.
Значение единичного поля читается методом ExecuteScalar()
OleDbCommand dbCommand = dbConn.CreateCommand(); dbCommand.CommandText = "SELECT TEST_COLUMN FROM TEST_TABLE WHERE ID_COLUMN = ?"; dbCommand.Parameters.Add("INT_COLUMN", OleDbType.Integer).Value = intVariable; int result = Convert.ToInt32(dbCommand.ExecuteScalar());
Надо особо отметить, что ExecuteScalar возвращает Object и если запрос не вернул вообще ничего, то результат будет null и конвертация в нормальный тип данных вылетит с ошибкой. Если возможна ситуация, когда в ответ мы ничего не получим, то надо делать так:
OleDbCommand dbCommand = dbConn.CreateCommand(); dbCommand.CommandText = "SELECT TEST_COLUMN FROM TEST_TABLE WHERE ID_COLUMN = ?"; dbCommand.Parameters.Add("INT_COLUMN", OleDbType.Integer).Value = intVariable; object resultObj = dbCommand.ExecuteScalar() int result = -1; // значение по умолчанию, означающее пустой результат if(resultObj != null) { result = Convert.ToInt32(dbCommand.ExecuteScalar()); }
3. Последовательное чтение из select-выборки строки за строкой
Чтение одной строки (несколько читаются в цикле);
OleDbCommand dbCommand = new OleDbCommand(select PERSON_ID, NAME, SURNAME from TEST_TABLE, dbConn); OleDbDataReader dbReader = dbCommand.ExecuteReader(); dbReader.Read(); string name = Convert.ToString(dbReader["NAME"]); string surname = Convert.ToString(dbReader["SURNAME"]); dbReader.Close();
4. Редкий случай. Автоматическое обновление таблицы в базе на основании изменений в DataTable (как правило редактируемой через визуальный интерфейс).
Необходимо прописать для DbAdapter четыре команды на каждый возможный случай - select, insert, update, delete.
DbAdapter.InsertCommand = new OleDbCommand("insert into TEST_TABLE (NAME, FAMIL, AGE) values (?, ?, ?)", dbConnection); dbAdapter.InsertCommand.Parameters.Add("NAME", OleDbType.VarChar, 100, "NAME"); dbAdapter.InsertCommand.Parameters.Add("FAMIL", OleDbType.VarChar, 100, "FAMIL"); // для типов данных с неизменной длинной указанная в команде длинна игнорируется dbAdapter.InsertCommand.Parameters.Add("AGE", OleDbType.Integer, 100, "AGE"); // добавляем команду обновления dbAdapter.UpdateCommand = new OleDbCommand("update TEST_TABLE set NAME = ?, FAMIL = ?, AGE = ? where ID = ?", dbConnection); dbAdapter.UpdateCommand.Parameters.Add("NAME", OleDbType.VarChar,100, "NAME"); dbAdapter.UpdateCommand.Parameters.Add("FAMIL", OleDbType.VarChar, 100, "FAMIL"); dbAdapter.UpdateCommand.Parameters.Add("AGE", OleDbType.Integer, 100, "AGE"); dbAdapter.UpdateCommand.Parameters.Add("ID", OleDbType.Integer, 100, "ID"); // добавляем команду удаления dbAdapter.DeleteCommand = new OleDbCommand("delete from TEST_TABLE where ID = ?", dbConnection); dbAdapter.DeleteCommand.Parameters.Add("ID", OleDbType.Integer, 100, "ID"); try { // переносит все изменения из DataTable в таблицу в базе данных dbAdapter.Update(table); } catch (Exception error) { MessageBox.Show("Ошибка при сохранении данных! " + error.Message); return; } MessageBox.Show("Изменения сохранены!");