Выполнение запросов microsoft sql server. Табличные выражения

Запросы написаны без экранирующих кавычек, так как у MySQL , MS SQL и PostGree они разные.

SQL запрос: получение указанных (нужных) полей из таблицы

SELECT id, country_title, count_people FROM table_name

Получаем список записей: ВСЕ страны и их население. Название нужных полей указываются через запятую.

SELECT * FROM table_name

* обозначает все поля. То есть, будут показы АБСОЛЮТНО ВСЕ поля данных.

SQL запрос: вывод записей из таблицы исключая дубликаты

SELECT DISTINCT country_title FROM table_name

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

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

SELECT id, country_title, city_title FROM table_name WHERE count_people>100000000

Получаем список записей: страны, где количество людей больше 100 000 000.

SQL запрос: вывод записей из таблицы с упорядочиванием

SELECT id, city_title FROM table_name ORDER BY city_title

Получаем список записей: города в алфавитном порядке. В начале А, в конце Я.

SELECT id, city_title FROM table_name ORDER BY city_title DESC

Получаем список записей: города в обратном (DESC ) порядке. В начале Я, в конце А.

SQL запрос: подсчет количества записей

SELECT COUNT(*) FROM table_name

Получаем число (количество) записей в таблице. В данном случае НЕТ списка записей.

SQL запрос: вывод нужного диапазона записей

SELECT * FROM table_name LIMIT 2, 3

Получаем 2 (вторую) и 3 (третью) запись из таблицы. Запрос полезен при создании навигации на WEB страницах.

SQL запросы с условиями

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

SQL запрос: конструкция AND (И)

SELECT id, city_title FROM table_name WHERE country="Россия" AND oil=1

Получаем список записей: города из России И имеют доступ к нефти. Когда используется оператор AND , то должны совпадать оба условия.

SQL запрос: конструкция OR (ИЛИ)

SELECT id, city_title FROM table_name WHERE country="Россия" OR country="США"

Получаем список записей: все города из России ИЛИ США. Когда используется оператор OR , то должно совпадать ХОТЯ БЫ одно условие.

SQL запрос: конструкция AND NOT (И НЕ)

SELECT id, user_login FROM table_name WHERE country="Россия" AND NOT count_comments<7

Получаем список записей: все пользователи из России И сделавших НЕ МЕНЬШЕ 7 комментариев.

SQL запрос: конструкция IN (В)

SELECT id, user_login FROM table_name WHERE country IN ("Россия", "Болгария", "Китай")

Получаем список записей: все пользователи, которые проживают в (IN ) (России, или Болгарии, или Китая)

SQL запрос: конструкция NOT IN (НЕ В)

SELECT id, user_login FROM table_name WHERE country NOT IN ("Россия","Китай")

Получаем список записей: все пользователи, которые проживают не в (NOT IN ) (России или Китае).

SQL запрос: конструкция IS NULL (пустые или НЕ пустые значения)

SELECT id, user_login FROM table_name WHERE status IS NULL

Получаем список записей: все пользователи, где status не определен. NULL это отдельная тема и поэтому она проверяется отдельно.

SELECT id, user_login FROM table_name WHERE state IS NOT NULL

Получаем список записей: все пользователи, где status определен (НЕ НОЛЬ).

SQL запрос: конструкция LIKE

SELECT id, user_login FROM table_name WHERE surname LIKE "Иван%"

Получаем список записей: пользователи, у которых фамилия начинается с комбинации «Иван». Знак % означает ЛЮБОЕ количество ЛЮБЫХ символов. Чтобы найти знак % требуется использовать экранирование «Иван\%».

SQL запрос: конструкция BETWEEN

SELECT id, user_login FROM table_name WHERE salary BETWEEN 25000 AND 50000

Получаем список записей: пользователи, которые получает зарплату от 25000 до 50000 включительно.

Логических операторов ОЧЕНЬ много, поэтому детально изучите документацию по SQL серверу.

Сложные SQL запросы

SQL запрос: объединение нескольких запросов

(SELECT id, user_login FROM table_name1) UNION (SELECT id, user_login FROM table_name2)

Получаем список записей: пользователи, которые зарегистрированы в системе, а также те пользователи, которые зарегистрированы на форуме отдельно. Оператором UNION можно объединить несколько запросов. UNION действует как SELECT DISTINCT, то есть отбрасывает повторяющиеся значения. Чтобы получить абсолютно все записи, нужно использовать оператор UNION ALL.

SQL запрос: подсчеты значений поля MAX, MIN, SUM, AVG, COUNT

Вывод одного, максимального значения счетчика в таблице:

SELECT MAX(counter) FROM table_name

Вывод одного, минимальный значения счетчика в таблице:

SELECT MIN(counter) FROM table_name

Вывод суммы всех значений счетчиков в таблице:

SELECT SUM(counter) FROM table_name

Вывод среднего значения счетчика в таблице:

SELECT AVG(counter) FROM table_name

Вывод количества счетчиков в таблице:

SELECT COUNT(counter) FROM table_name

Вывод количества счетчиков в цехе №1, в таблице:

SELECT COUNT(counter) FROM table_name WHERE office="Цех №1"

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

SQL запрос: группировка записей

SELECT continent, SUM(country_area) FROM country GROUP BY continent

Получаем список записей: с названием континента и с суммой площадей всех их стран. То есть, если есть справочник стран, где у каждой страны записана ее площадь, то с помощью конструкции GROUP BY можно узнать размер каждого континента (на основе группировки по континентам).

SQL запрос: использование нескольких таблиц через алиас (alias)

SELECT o.order_no, o.amount_paid, c.company FROM orders AS o, customer AS с WHERE o.custno=c.custno AND c.city="Тюмень"

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

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

SELECT o.order_no, o.amount_paid, z.company FROM orders AS o LEFT JOIN customer AS z ON (z.custno=o.custno)

Вложенные подзапросы

SELECT * FROM table_name WHERE salary=(SELECT MAX(salary) FROM employee)

Получаем одну запись: информацию о пользователе с максимальным окладом.

Внимание! Вложенные подзапросы являются одним из самых узких мест в SQL серверах. Совместно со своей гибкостью и мощностью, они также существенно увеличивают нагрузку на сервер. Что приводит к катастрофическому замедлению работы других пользователей. Очень часты случаи рекурсивных вызовов при вложенных запросах. Поэтому настоятельно рекомендую НЕ использовать вложенные запросы, а разбивать их на более мелкие. Либо использовать вышеописанную комбинацию LEFT JOIN. Помимо этого данного вида запросы являются повышенным очагом нарушения безопасности. Если решили использовать вложенные подзапросы, то проектировать их нужно очень внимательно и первоначальные запуски сделать на копиях баз (тестовые базы).

SQL запросы изменяющие данные

SQL запрос: INSERT

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

Вариант №1. Часто используется инструкция:

INSERT INTO table_name (id, user_login) VALUES (1, "ivanov"), (2, "petrov")

В таблицу «table_name » будет вставлено 2 (два) пользователя сразу.

Вариант №2. Удобнее использовать стиль:

INSERT table_name SET id=1, user_login="ivanov"; INSERT table_name SET id=2, user_login="petrov";

В этом есть свои преимущества и недостатки.

Основные недостатки:

• Множество мелких SQL запросов выполняются чуть медленнее, чем один большой SQL запрос, но при этом другие запросы будут стоять в очереди на обслуживание. То есть, если большой SQL запрос будет выполняться 30 минут, то в все это время остальные запросы будут курить бамбук и ждать своей очереди.
• Запрос получается массивнее, чем предыдущий вариант.

Основные преимущества:

• Во время мелких SQL запросов, другие SQL запросы не блокируются.
• Удобство в чтении.
• Гибкость. В этом варианте, можно не соблюдать структуру, а добавлять только необходимые данные.
• При формировании подобным образом архивов, можно легко скопировать одну строчку и запустить ее через командную строку (консоль), тем самым не восстанавливая АРХИВ целиком.
• Стиль записи схож с инструкцией UPDATE, что легче запоминается.

SQL запрос: UPDATE

UPDATE table_name SET user_login="ivanov", user_surname="Иванов" WHERE id=1

В таблице «table_name » в записи с номером id=1, будет изменены значения полей user_login и user_surname на указанные значения.

SQL запрос: DELETE

DELETE FROM table_name WHERE id=3

В таблице table_name будет удалена запись с id номером 3.

1. Все названия полей рекомендуются писать маленькими буквами и если надо, разделять их через принудительный пробел «_» для совместимости с разными языками программирования, таких как Delphi, Perl, Python и Ruby.
2. SQL команды писать БОЛЬШИМИ буквами для удобочитаемости. Помните всегда, что после вас могут читать код и другие люди, а скорее всего вы сами через N количество времени.
3. Называть поля с начала существительное, а потом действие. Например: city_status, user_login, user_name.
4. Стараться избегать слов резервных в разных языках которые могут вызывать проблемы в языках SQL, PHP или Perl, типа (name, count, link). Например: link можно использовать в MS SQL, но в MySQL зарезервировано.

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

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

• подключение базы данных к приложению;
• отображение таблиц базы данных на форме;
• добавление новой записи в базу данных;
• редактирование записи;
• удаление записи.

За основу взята база данных Education.dbo , процесс создания которой подробно описывается

На рисунке 1 отображена структура базы данных Education.dbo . Как видно из рисунка, база данных называется:

sasha-pc\sqlexpress.Education.dbo

Здесь «sasha-pc » – идентификатор компьютера в сети, «sqlexpress » – название сервера баз данных, «Education.dbo » – название базы данных.

Рис. 1. База данных Education.dbo

База данных содержит две таблицы: Student и Session. Структура таблиц следующая.

Таблица Student .

Таблица Session .

Выполнение

1. Создать новый проект в MS Visual Studio как Windows Forms Application .

Создать новый проект типа Windows Forms Application . Пример создания нового проекта подробно описывается

2. Создание нового представления (view ) для отображения данных таблицы Student .

Чтобы отображать данные таблиц базы данных, можно использовать разные способы. Один из них – это создание представлений (views ), которые формируются средствами MS Visual Studio .

В нашем случае данные таблиц будут отображаться на форме в элементе управления типа DataGridView . После создания представлений, очень удобно их связать с элементами DataGridView .

Для создания представления, нужно вызвать команду «Add New View » из контекстного меню, которое вызывается кликом правой кнопкой мышки на элементе «Views » базы данных Education.dbo (рисунок 2).

Рис. 2. Вызов команды добавления нового представления

В результате откроется окно «Add Table » (рисунок 3). В окне нужно выбрать таблицы, которые добавляются к представлению.

Рис. 3. Выбор таблиц, на которых будет базироваться новое представление

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

После выполненных действий будет сформировано окно, в котором нужно выбрать поля, которые должны отображаться в представлении (на форме). Выбираем все поля (рисунок 4).

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

Соответственно, в средней области окна отображаются названия выбранных полей, таблица в которой они есть (см. рис. 4), возможность сортировки, фильтрование и прочее.

В нижней части области отображается соответствующий текст SQL -запроса, который используется для создания представления.

Рис. 4. Выбор полей таблицы Student для их отображения в представлении

После выбора команды

File->Save All File->Save View1

откроется окно, в котором нужно указать имя представления. Задаем имя «View Student » (рисунок 5).

Рис. 5. Указание имени для представления

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

Рис. 6. Представление View Student в базе данных

Теперь можно размещать элемент управления DataGridView и связывать его с представлением.

3. Размещение элемента управления DataGridView и настройка связи с базой данных.

Перед размещением элемента управления DataGridView нужно перейти в режим проектирования формы «Form1.cs «.

Элемент DataGridView представляет собой таблицу, которая может отображать данные. Данный элемент управления размещается в панели ToolBox . Сначала немного корректируем размеры формы, а потом размещаем на ней элемент управления DataGridView (рисунок 7). В результате будет создан экземпляр объекта с именем dataGridView1 по умолчанию.

Рис. 7. Элемент управления DataGridView и окно выбора источника данных

После размещения элемента управления типа DataGridView на форме в правом верхнем углу можно выбрать настройку источника данных. Соответственно откроется окно «DataGridView Tasks «. В этом окне нужно выбрать сплывающее меню «Choose Data Source «.

В открывшемся меню выбирается команда «Add Project Data Source… » (рис. 7). После этого открывается окно мастера, в котором последовательно выбирается источник данных.

На рисунке 8 показано окно «Data Source Configuration Wizard «, в котором выбирается тип источника данных. В нашем случае устанавливаем «Database «.

Рис. 8. Выбор типа источника данных

В следующем окне (Рисунок 9) выбирается модель источника данных. Нужно выбрать DataSet .

Рис. 9. Выбор модели источника данных

В окне, изображенном на рисунке 10 , нужно задать соединение данных, которое нужно использовать для подключения к базе данных. В нашем случае нужно выбрать базу данных «sasha-pc\sqlexpress\Education.dbo «.

Рис. 10. Выбор соединения данных

В следующем окне (рисунок 11) предлагается сохранить строку соединения Connection String в конфигурационный файл приложения. Оставляем все как есть и переходим к следующему окну.

Рис. 11. Предложение сохранения строки соединения с базой данных Connection String в конфигурационном файле приложения

После создания соединения с базой данных отображается множество объектов базы данных (рисунок 12). В нашем случае нужно выбрать представление «View Student » и все поля из него. Отмеченные поля будут отображаться в компоненте типа DataGridView .

Рис. 12. Выбор объектов базы данных, которые нужно отображать в DataGridView

После выбора кнопки Finish будут отображены выбранные объекты (представление View Student ) базы данных Education.dbo (рисунок 13).

Рис. 13. Элемент управления типа DataGridView с выбранными полями представления View Student

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

4. Настройка вида элемента управления типа DataGridView .

Если запустить приложение на выполнение, то будут получены данные представления View Student , которое отвечает таблице Student базы данных (рисунок 14).

Рис. 14. Запуск приложения на выполнение

Как видно из рисунка 14, данные в таблице dataGridView1 отображаются нормально, но оформление можно скорректировать.

Элемент управления типа DataGridView допускает корректирование вида полей, которые отображаются.

Для вызова команд редактирования полей, достаточно вызвать контекстное меню кликом правой кнопкой мышки на элементе управления dataGridView1 .

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

• команда блокирования элемента управления (Lock Controls );
• команда редактирования полей, которые отображаются в представлении (Edit Columns… );
• команда добавления новых полей, например вычисляемых (Add Column ).

В нашем случае нужно выбрать команду «Edit Columns… » (рисунок 15).

Рис. 15. Команда «Edit Columns… » из контекстного меню

В результате откроется окно «Edit Columns «, в котором можно настроить вид полей представления по собственному вкусу (рисунок 16).

Рис. 16. Окно настройки вида полей в представлении «View Student «

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

5. Строка Connection String

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

Существуют разные способы получения строки соединения с базой данных. Один из них базируется на чтении этой строки в окне Properties базы данных Education.dbo (рис. 17).

Рис. 17. Определение строки Connection String

Для сохранности строки в программе вводится внутренняя переменная типа string . С помощью буфера обмена копируем строку Connection String в описанную переменную типа string .

В тексте файла «Form1.cs » в начале описания класса Form1 надо описать переменную:

string conn_string = ;

На данный момент текст класса Form1 следующий:

public partial class Form1 : Form { string conn_string = @"Data Source=(local)\SQLEXPRESS;Initial Catalog=Education;Integrated Security=True;Pooling=False" ; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { // TODO: This line of code loads data into the "educationDataSet.View_Student" table. You can move, or remove it, as needed. } }

6. Создание новой формы для демонстрации команд манипулирования данными.

Для того, чтобы иметь возможность обрабатывать данные текущей записи нужно создать новую форму. Процесс создания новой формы в MS Visual Studio — C# подробно описывается .

Добавление новой формы осуществляется командой:

Project -> Add Windows Form...

В открывшемся окне «New Item » нужно выбрать элемент «Windows Form «.

Имя файла новой формы оставляем по умолчанию «Form2.cs «.

На рисунке 18 изображен вид новой формы.

Размещаем на форме элементы управления следующих типов:

• два элемента управления типа Button (кнопки OK и Cancel ). Соответственно будет получено два объекта с именами button1 и button2 ;
• четыре элемента управления типа Label для создания информационных сообщений;
• четыре элемента управления типа TextBox для ввода данных в полях Num_book , Name , Group , Year .

Нужно настроить следующие свойства элементов управления:

• в элементе управления button1 свойство Text = «OK «;
• в элементе управления button2 свойство Text = «Cancel «;
• в элементе управления button1 свойство DialogResult = «OK «;
• в элементе управления button2 свойство DialogResult = «Cancel «;
• в элементе управления label1 свойство Text = «Num_book «;
• в элементе управления label2 свойство Text = «Name «;
• в элементе управления label3 свойство Text = «Group «;
• в элементе управления label4 свойство Text = «Year «.

Также настраиваем видимость элементов управления типа TextBox . Для этого во всех элементах управления textBox1 , textBox2 , textBox3 , textBox4 значение свойства Modifiers = «public «.

Рис. 18. Вид новосозданной формы

7. Добавление кнопок вызова команд манипулирования данными таблицы Student .

Для дальнейшей работы нужно с помощью мышки переключиться на главную форму Form1 .

На главную форму приложения Form1 добавляем три кнопки (Button ). Автоматически будет создано три переменных-объекта с именами button1 , button2 , button3 . В каждой из этих кнопок вносим следующие настройки (окно Properties ):

• в кнопке button1 свойство Text = «Insert … » (вставить запись);
• в кнопке button2 свойство Text = «Edit … » (изменить запись);
• в кнопке button3 свойство Text = «Delete «.

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

Рис. 19. Главная форма приложения

8. Программирование события клика на кнопке «Insert… «.

Обработчик события клика на кнопке «Insert… » имеет вид:

private void button1_Click_1(object sender, EventArgs e) { string cmd_text; Form2 f2 = new Form2 (); if (f2.ShowDialog() == DialogResult .OK) { cmd_text = "INSERT INTO Student VALUES (" + """ + f2.textBox1.Text + "" , "" + f2.textBox2.Text + "" , "" + f2.textBox3.Text + "" , " + f2.textBox4.Text + ")" ; // создать соединение с базой данных SqlConnection sql_conn = new SqlConnection (conn_string); // создать команду на языке SQL SqlCommand sql_comm = new SqlCommand (cmd_text, sql_conn); sql_conn.Open(); // открыть соединение sql_comm.ExecuteNonQuery(); // выполнить команду на языке SQL sql_conn.Close(); // закрыть соединение this .view_StudentTableAdapter.Fill(this .educationDataSet.View_Student); } }

Сначала вызывается форма Form2 . После получения результата «OK » (нажатие соответствующей кнопки), в форме Form2 заполненные поля в элементах типа TextBox включаются в строку SQL -запроса. SQL -запрос добавления новой строки имеет вид:

INSERT INTO Student VALUES (value1, value2, value3, value4)

где value1 отвечает номеру зачетной книжки; value2 – фамилия студента; value3 – группа, в которой учится студент; value4 – год вступления.

Строка соединения с базой данных Connection String описывается в переменной conn_string (см. п. 5). Объект класса SqlConnection осуществляет соединение приложения с источниками данных. Кроме того, класс Connection решает задачи аутентификации пользователей, работу с сетями, идентификацию баз данных, буферизацию соединений и обработку транзакций.

Команда на языке SQL , добавляющая запись к таблице, инкапсулирована в классе SqlCommand . Конструктор класса SqlCommand принимает два параметра: строку запроса на языке SQL (переменная cmd_text ) и объект класса SqlConnection .

Метод ExecuteNonQuery() реализован в интерфейсе IDBCommand . Метод реализует SQL -команды, которые не возвращают данные. К таким командам относятся команды INSERT , DELETE , UPDATE а также хранимые процедуры, которые не возвращают данных. Метод ExecuteNonQuery() возвращает количество задействованных ним записей.

9. Программирование события клика на кнопке «Edit… «.

Обработчик события клика на кнопке «Edit… » имеет вид:

private void button2_Click(object sender, EventArgs e) { string cmd_text; Form2 f2 = new Form2 (); int index; string num_book; index = dataGridView1.CurrentRow.Index; num_book = Convert .ToString(dataGridView1.Value); f2.textBox1.Text = num_book; f2.textBox2.Text = Convert .ToString(dataGridView1.Value); f2.textBox3.Text = Convert .ToString(dataGridView1.Value); f2.textBox4.Text = Convert .ToString(dataGridView1.Value); if (f2.ShowDialog() == DialogResult .OK) { cmd_text = "UPDATE Student SET Num_book = "" + f2.textBox1.Text + "", " + " = "" + f2.textBox2.Text + "", " + " = "" + f2.textBox3.Text + "", " + "Year = " + f2.textBox4.Text + "WHERE Num_book = "" + num_book + """ ; SqlConnection sql_conn = new SqlConnection (conn_string); SqlCommand sql_comm = new SqlCommand (cmd_text, sql_conn); sql_conn.Open(); sql_comm.ExecuteNonQuery(); sql_conn.Close(); this .view_StudentTableAdapter.Fill(this .educationDataSet.View_Student); } }

В этом обработчике выполняется SQL -команда UPDATE , которая изменяет текущее значение активной записи.

10. Программирование события клика на кнопке «Delete «.

Обработчик события клика на кнопке «Delete » имеет вид:

private void button3_Click(object sender, EventArgs e) { string cmd_text = "DELETE FROM Student" ; int index; string num_book; index = dataGridView1.CurrentRow.Index; num_book = Convert.ToString(dataGridView1.Value); cmd_text = "DELETE FROM Student WHERE . = "" + num_book + """ ; SqlConnection sql_conn = new SqlConnection (conn_string); SqlCommand sql_comm = new SqlCommand (cmd_text, sql_conn); sql_conn.Open(); sql_comm.ExecuteNonQuery(); sql_conn.Close(); this .view_StudentTableAdapter.Fill(this .educationDataSet.View_Student); }

В этом обработчике выполняется SQL -команда удаления записи DELETE .

Связанные темы

• Вывод таблицы базы данных Microsoft Access

Пример создания запроса (Query ) в базе данных MS SQL Server . База данных размещена в локальном файле *.mdf

Условие задачи

Задана база данных, которая размещается в файле Education.mdf . База данных содержит две связанные между собою таблицы Student и Session .

Таблицы связаны между собою за полем ID_Book .

Используя средства Microsoft Visual Studio создать запрос с именем Query1 , который будет иметь следующую структуру:

Название поля	Таблица
Num_Book	Student
Name	Student
Mathematics	Session
Informatics	Session
Philosophy	Session
Average	Вычислительное поле

Выполнение (пошаговая инструкция)

1. Загрузить Microsoft Visual Studio

2. Подключить базу данных Education.mdf к перечню баз данных утилиты Server Explorer

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

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

• выбором команды «Connect to Database…» с меню Tools ;
• выбором кнопки (команды) «Connect to Database…» из утилиты Server Explorer .

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

Рис. 1. Способы добавления/подключения базы данных

Подробное описание того, как осуществляется подключение базы данных типа Microsoft SQL Server в Microsoft Visual Studio , приведено в теме:

• Пример создания/подключени я локальной базы данных Microsoft SQL Server , которая размещается в *.mdf -файле

После подключения, окно утилиты Server Explorer будет выглядеть как показано на рисунке 2.

Рис. 2. Утилита Server Explorer с подключенной базой данных Education.mdf

3. Добавление нового запроса. Команда «New Query»

К базе данных можно создавать запросы. В нашем случае нужно создать запрос в соответствии с условием задачи.

Запрос создается с помощью команды «New Query» , которая вызовется из контекстного меню (рисунок 3). Чтобы вызвать команду, достаточно сделать клик правой кнопкой мышки в области поля, которое выделено для отображения элементов базы данных Education.mdb . Следует отметить, что запросы не сохраняются системой. Для отображения сохраненных (сложных) запросов используется представление (Views ).

На рисунке 3 отображено контекстное меню, которое вызывается при нажатии на вкладке Views (представление). В этом меню нужно выбрать команду «New Query» . Эта команда есть в перечне контекстных меню других составляющих базы данных (таблиц, диаграмм и т.п.).

Рис. 3. Команда New Query

В результате откроется окно «Add Table» , в котором нужно выбрать таблицы, данные из которых будут использоваться в запросе (рисунок 4).

Рис. 4. Выбор таблиц, которые будут использоваться в запросе

Для нашего случая нужно выбрать обе таблицы.

В результате окно Microsoft Visual Studio будет выглядеть, как показано на рисунке 5.

Рис. 5. Окно MS Visual Studio после создания запроса

В таблицах нужно выделить поля, которые будут использоваться в запросе. Порядок выбора полей должен соответствовать отображению их в запросе в соответствии с условием задачи. Это означает, что сначала выбираются поля таблицы Student (NumBook , Name ), а потом выбираются поля таблицы Session (Mathematics , Informatics , Philosophy ).

Для нашего случая выбор полей изображен на рисунке 6.

Рис. 6. Выбор полей для запроса

Как видно из рисунка 6, в нижней части окна отображается запрос на языке SQL , сформированный системой

SELECT Student.Num_Book, Student.Name, Session.Mathematics, Session.Informatics, Session.Philosophy FROM

4. Добавление вычисляемого поля Average

Чтобы создать вычисляемое поле Average , нужно в окне, где отображается SQL -запрос изменить текст этого запроса. Например:

SELECT Student.Num_Book, Student.Name, Session.Mathematics, Session.Informatics, Session.Philosophy, (Session.Mathematics + Session.Informatics + Session.Philosophy) / 3.0 AS Average FROM Session INNER JOIN Student ON Session.ID_Book = Student.ID_Book

Добавляется вычислительное поле Average , которое есть средним арифметическим (рисунок 7).

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

производные таблицы;

обобщенные табличные выражения.

Эти две формы табличных выражений рассматриваются в следующих подразделах.

Производные таблицы

Производная таблица (derived table) - это табличное выражение, входящее в предложение FROM запроса. Производные таблицы можно применять в тех случаях, когда использование псевдонимов столбцов не представляется возможным, поскольку транслятор SQL обрабатывает другое предложение до того, как псевдоним станет известным. В примере ниже показана попытка использовать псевдоним столбца в ситуации, когда другое предложение обрабатывается до того, как станет известным псевдоним:

USE SampleDb; SELECT MONTH(EnterDate) as enter_month FROM Works_on GROUP BY enter_month;

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

Msg 207, Level 16, State 1, Line 5 Invalid column name "enter_month". (Сообщение 207: уровень 16, состояние 1, строка 5 Недопустимое имя столбца enter_month)

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

Эту проблему можно решить, используя производную таблицу, содержащую предшествующий запрос (без предложения GROUP BY), поскольку предложение FROM исполняется перед предложением GROUP BY:

USE SampleDb; SELECT enter_month FROM (SELECT MONTH(EnterDate) as enter_month FROM Works_on) AS m GROUP BY enter_month;

Результат выполнения этого запроса будет таким:

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

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

Обобщенные табличные выражения

Обобщенным табличным выражением (OTB) (Common Table Expression - сокращенно CTE) называется именованное табличное выражение, поддерживаемое языком Transact-SQL. Обобщенные табличные выражения используются в следующих двух типах запросов:

нерекурсивных;

рекурсивных.

Эти два типа запросов рассматриваются в следующих далее разделах.

OTB и нерекурсивные запросы

Нерекурсивную форму OTB можно использовать в качестве альтернативы производным таблицам и представлениям. Обычно OTB определяется посредством предложения WITH и дополнительного запроса, который ссылается на имя, используемое в предложении WITH. В языке Transact-SQL значение ключевого слова WITH неоднозначно. Чтобы избежать неопределенности, инструкцию, предшествующую оператору WITH, следует завершать точкой с запятой.

USE AdventureWorks2012; SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") AND Freight > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005")/2.5;

Запрос в этом примере выбирает заказы, чьи общие суммы налогов (TotalDue) большие, чем среднее значение по всем налогам, и плата за перевозку (Freight) которых больше чем 40% среднего значения налогов. Основным свойством этого запроса является его объемистость, поскольку вложенный запрос требуется писать дважды. Одним из возможных способов уменьшить объем конструкции запроса будет создать представление, содержащее вложенный запрос. Но это решение несколько сложно, поскольку требует создания представления, а потом его удаления после окончания выполнения запроса. Лучшим подходом будет создать OTB. В примере ниже показывается использование нерекурсивного OTB, которое сокращает определение запроса, приведенного выше:

USE AdventureWorks2012; WITH price_calc(year_2005) AS (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT year_2005 FROM price_calc) AND Freight > (SELECT year_2005 FROM price_calc)/2.5;

Синтаксис предложения WITH в нерекурсивных запросах имеет следующий вид:

Параметр cte_name представляет имя OTB, которое определяет результирующую таблицу, а параметр column_list - список столбцов табличного выражения. (В примере выше OTB называется price_calc и имеет один столбец - year_2005.) Параметр inner_query представляет инструкцию SELECT, которая определяет результирующий набор соответствующего табличного выражения. После этого определенное табличное выражение можно использовать во внешнем запросе outer_query. (Внешний запрос в примере выше использует OTB price_calc и ее столбец year_2005, чтобы упростить употребляющийся дважды вложенный запрос.)

OTB и рекурсивные запросы

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

Параметры cte_name и column_list имеют такое же значение, как и в OTB для нерекурсивных запросов. Тело предложения WITH состоит из двух запросов, объединенных оператором UNION ALL . Первый запрос вызывается только один раз, и он начинает накапливать результат рекурсии. Первый операнд оператора UNION ALL не ссылается на OTB. Этот запрос называется опорным запросом или источником.

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

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

Наконец, параметр outer_query определяет внешний запрос, который использует OTB для получения всех вызовов объединения обеих членов.

Для демонстрации рекурсивной формы OTB мы используем таблицу Airplane, определенную и заполненную кодом, показанным в примере ниже:

USE SampleDb; CREATE TABLE Airplane (ContainingAssembly VARCHAR(10), ContainedAssembly VARCHAR(10), QuantityContained INT, UnitCost DECIMAL (6,2)); INSERT INTO Airplane VALUES ("Самолет", "Фюзеляж",1, 10); INSERT INTO Airplane VALUES ("Самолет", "Крылья", 1, 11); INSERT INTO Airplane VALUES ("Самолет", "Хвост",1, 12); INSERT INTO Airplane VALUES ("Фюзеляж", "Салон", 1, 13); INSERT INTO Airplane VALUES ("Фюзеляж", "Кабина", 1, 14); INSERT INTO Airplane VALUES ("Фюзеляж", "Нос",1, 15); INSERT INTO Airplane VALUES ("Салон", NULL, 1,13); INSERT INTO Airplane VALUES ("Кабина", NULL, 1, 14); INSERT INTO Airplane VALUES ("Нос", NULL, 1, 15); INSERT INTO Airplane VALUES ("Крылья", NULL,2, 11); INSERT INTO Airplane VALUES ("Хвост", NULL, 1, 12);

Таблица Airplane состоит из четырех столбцов. Столбец ContainingAssembly определяет сборку, а столбец ContainedAssembly - части (одна за другой), которые составляют соответствующую сборку. На рисунке ниже приведена графическая иллюстрация возможного вида самолета и его составляющих частей:

Таблица Airplane состоит из следующих 11 строк:

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

USE SampleDb; WITH list_of_parts(assembly1, quantity, cost) AS (SELECT ContainingAssembly, QuantityContained, UnitCost FROM Airplane WHERE ContainedAssembly IS NULL UNION ALL SELECT a.ContainingAssembly, a.QuantityContained, CAST(l.quantity * l.cost AS DECIMAL(6,2)) FROM list_of_parts l, Airplane a WHERE l.assembly1 = a.ContainedAssembly) SELECT assembly1 "Деталь", quantity "Кол-во", cost "Цена" FROM list_of_parts;

Предложение WITH определяет список OTB с именем list_of_parts, состоящий из трех столбцов: assembly1, quantity и cost. Первая инструкция SELECT в примере вызывается только один раз, чтобы сохранить результаты первого шага процесса рекурсии. Инструкция SELECT в последней строке примера отображает следующий результат.

SQL - Структурированный Язык Запросов.
В данном обзоре мы рассмотрим наиболее часто встречающиеся виды SQL-запросов.
Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов ).
SQL — это язык, ориентированный специально на реляционные базы данных.

Разделение SQL:

DDL (Язык Определения Данных ) — так называемый Язык Описания Схемы в ANSI, состоит из команд, которые создают объекты (таблицы, индексы, просмотры, и так далее) в базе данных.
DML (Язык Манипулирования Данными ) — это набор команд, которые определяют, какие значения представлены в таблицах в любой момент времени.
DCD (Язык Управления Данными ) состоит из средств, которые определяют, разрешить ли пользователю выполнять определенные действия или нет. Они являются составными частями DDL в ANSI. Не забывайте эти имена. Это не различные языки, а разделы команд SQL сгруппированных по их функциям.

Типы данных:

SQL Server - Типы данных	Описание
bigint (int 8 )	bigint (int 8 )
binary (n)	binary (n) или image
character (синоним char )	national character или ntext
character varying (синоним char varying varchar )	national character varying или ntext
Datetime	datetime
decimal	он же numeric
double precision	double precision
integer (int 4 ) (синоним:int )	integer (int 4 )
national character (синоним: national character , nchar )	national character
Numeric (сининимы: decimal , dec )
national character varying (синонимы: national char varying , nvarchar )	National character varying
Smalldatetime	datetime
smallint (int 2 )	smallint (int 2 )
Smallmoney
sql_variant	Больше не поддреживается
	Ntext Начиная с SQL Server 2005 не рекомендуется для использования.
Timestamp	Не поддреживается
tinyint (int 1 )	tinyint (int 1 )
Uniqueidentifier	uniqueidentifier
varbinary (n)	varbinary (n) или image
smalldatetime	datetime
smallint (int 2 )	smallint (int 2 )
smallmoney
sql_variant	Не поддерживается
timestamp	Не поддерживается
tinyint (int 1 )	tinyint (int 1 )
uniqueidentifier	uniqueidentifier
varbinary (n)	varbinary (n) или image

Таблица типов данных в SQL Server 2000

ЧТО ТАКОЕ ЗАПРОС?

Запрос — команда, которую вы даете вашей программе базы данных. Запросы это часть языка DML. Все запросы в SQL состоят из одиночной команды. Структура этой команды обманчиво проста, потому что вы должны расширять ее так, чтобы выполнить высоко сложные оценки и обработки данных.

Команда SELECT:

SELECT “Выбор” - самая часто используемая команда, с помощью её идет выборка данных из таблицы.
Вид запроса с применением SELECT:

SELECT id, user_name, city, birth_day FROM users_base;

Такой запрос выведет из таблицы users_base все значения столбцов указанных через запятую после команды SELECT. Также, можно выводить все столбцы одним символом, * т.е. SELECT * FROM users_base ; - такой запрос выведет все данные из таблицы.

Структура команды SELECT:

SELECT {Имена столбцов через запятую которые необходимо вывести в запросе} FROM {имя таблицы в базе данных}
- это простейший вид запроса. Существуют дополнительные команды для удобства извлечения данных (см. далее “Функции”)

DML команды:

Значения могут быть помещены и удалены из полей, тремя командами языка DML (Язык Манипулирования Данными):
INSERT (Вставка)
UPDATE (Обновление, модификация),
DELETE (Удаление)

Команда INSERT:

INSERT INTO users_base (user_name, city, birth_day) VALUES (‘Александр’, ‘Ростов’, ’20.06.1991’);

Команда INSERT идет вместе с приставкой INTO (in to - в), далее в скобках идут имена столбцов, в которые мы должны вставить данные, далее идет команда VALUES (значения) и в скобках по очереди идут значения (обязательно нужно соблюдать очередность значений со столбцами, значения должны идти в той же очередности, как и столбцы указанные вами).

Команда UPDATE:

UPDATE users_base SET user_name = ‘Алексей’;

Команда UPDATE обновляет значения в таблице. Сначала идет сама команда UPDATE затем имя таблицы, после команда SET (установит) далее имя столбца и его значение в кавычках (кавычки ставятся в том случае если значение имеет string формат, если это числовое значение и столбец не привязан к типу данных vchar и любых других строковых типов, то кавычки не имеют смысла.)

Команда DELETE:

DELETE FROM users_base WHERE user_name = ‘Василий’;

Команда DELETE удаляет строку целиком, определяет строку по критерию WHERE (Где). В данном случае этот запрос удалил бы все строки, в которых значение столбца user_name было бы Василий. О критерии WHERE и других мы поговорим немного позже.

Критерии, функции, условия и т.п. что помогает нам в SQL:

WHERE- предложение команды SELECT и других DML команд, которое позволяет вам устанавливать предикаты, условие которых может быть или верным или неверным для любой строки таблицы. Команда извлекает только те строки из таблицы, для которых такое утверждение верно.
Пример:
SELECT id, city, birth_day FROM users_base WHERE user_name = ‘Алексей’; - такой запрос выведет только те строки, которые будут соответствовать условию WHERE, а именно все строки в которых столбец user_name имеет значение Алексей.

ORDER BY - условие для сортировки выбранных строк. Имеет 2 критерия ASC и DESC. ASC (сортировка от А до Я или от 0 до 9)

DESC (противоположно от ASC).
Пример:
SELECT id, city, birth_day FROM users_base ORDER BY user_name ASC; - такой запрос выведет значения отсортированные по столбцу user_name от А до Я (A-Z; 0-9)

Также это условие можно использовать совместно с условием WHERE.
Пример:
SELECT id, city, birth_day FROM users_base WHERE user_name = ‘Алексей’ ORDER BY id ASC;

DISTINCT (Отличный) — аргумент, который обеспечивает вас способом устранять двойные значения из вашего предложения SELECT. Т.е. если у вас имеются повторные значения в столбце, допустим, user_name то DISTINCT выведет вам только одно, например у вас в базе есть 2 человека по имени Алексей то запрос с использованием функции DISTINCT выведет вам только 1 значение, которое встретит первым...
Пример:
SELECT DISTINCT user_name FROM users_base; - такой запрос выведет нам значения всех записей в столбце user_name но они не будут повторяться, т.е. если вы имели бесконечное число повторяющихся значений, то они показаны не будут…

AND - берет два Буля (в форме A AND B) как аргументы и оценивает их по отношению к истине, верны ли они оба.
Пример:
SELECT * FROM users_base WHERE city = ‘Ростов’ AND user_name = ‘Александр’; - выведет все значения из таблицы где в одной строке встречается название города (в данном случае Ростов и имя пользователя Александр.

OR - берет два Буля (в форме A OR B) как аргументы и оценивает на правильность, верен ли один из них.

SELECT * FROM users_base WHERE city = ‘Ростов’ OR user_name = ‘Александр’; - выведет все значения из таблицы где в строке встречается название города Ростов или Имя пользователя Александр.

NOT - берет одиночный Булев (в форме NOT A) как аргументы и заменяет его значение с неверного на верное или верное на неверное.
SELECT * FROM users_base WHERE city = ‘Ростов’ OR NOT user_name = ‘Александр’; - выведет все значения из таблицы где в одной строке встретится имя города Ростов или имя пользователя не будет ровно Александр.

IN - определяет набор значений в которое данное значение может или не может быть включено.
SELECT * FROM users_base WHERE city IN (‘Владивосток’, ‘Ростов’); - такой запрос выведет все значения из таблицы в которых встретятся наименования указанных городов в столбце city

Between - похож на оператор IN. В отличии от определения по номерам из набора, как это делает IN, BETWEEN определяет диапазон, значения которого должны уменьшаться что делает предикат верным.
SELECT * FROM users_base WHERE id BETWEEN 1 AND 10; - выводит все значения из таблицы которые будут находиться в диапазоне от 1 до 10 в столбце id

COUNT - производит номера строк или не NULL значения полей, которые выбрал запрос.
SELECT COUNT (*) FROM users_base ; - выведет количество строк в данной таблице.
SELECT COUNT (DISTINCT user_name) FROM users_base ; - выведет кол-во строк с именами пользователей (не повторяющихся)

SUM - производит арифметическую сумму всех выбранных значений данного поля.
SELECT SUM (id) FROM users_base ; - выведет сумму значений всех строк столбца id.

AVG - производит усреднение всех выбранных значений данного поля.
SELECT AVG (id) FROM users_base ; - выведет среднее значение всех выбранных значений столбца id

MAX - производит наибольшее из всех выбранных значений данного поля.

MIN - производит наименьшее из всех выбранных значений данного поля.

Создание таблиц:

CREATE TABLE users_base (id integer, user_name text, city text, birth_day datetime); - выполнение такой команды приведёт к созданию таблицы, по которой я приводил примеры… Тут всё просто, пишем команду CREATE TABLE далее имя таблицы, которую хотим создать, далее в скобках через запятую имена столбцов и их тип данных. Это стандартный вид создания таблицы в SQL. Сейчас я приведу пример создания таблиц в SQL Server 2005:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N".") AND type in (N"U"))
BEGIN
CREATE TABLE .(

NOT NULL,
NOT NULL,
NOT NULL,
PRIMARY KEY CLUSTERED
ASC


END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N".") AND type in (N"U"))
BEGIN
CREATE TABLE .(
IDENTITY(1,1) NOT NULL,
NULL,
NULL,
PRIMARY KEY CLUSTERED
ASC
)WITH (IGNORE_DUP_KEY = OFF) ON
) ON TEXTIMAGE_ON
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N".") AND type in (N"U"))
BEGIN
CREATE TABLE .(
IDENTITY(1,1) NOT NULL,
NULL,
NULL,
PRIMARY KEY CLUSTERED
ASC
)WITH (IGNORE_DUP_KEY = OFF) ON
) ON
END

Синтаксис в SQL Server 2005 это уже другая тема, я просто хотел показать что я описал основы SQL программирования, до вершин вы сможете дойти сами зная основы.

При вознекновении вопросов по этой теме, пишите мне на мыло