NO:user
  READ:@ALL
  EDIT:@ALL

====== Лабораторная работа №3 ======

====== Использование операторов манипулирования данными в Microsoft SQL Server======



** Цель работы ** – научиться использовать операторы манипулирования данными Select, Insert, Update, Delete.

===== Содержание работы: =====

  - Создать с помощью приведенных операторов пример базы данных «Книжное дело», описанный в предыдущей лабораторной работе (если БД отсутствует на сервере).
  -  С помощью операторов Insert создать программу в SQL Server Management Studio через «Создать запрос» для заполнения таблиц данными (по 3-5 записей).
  -  С помощью оператора Select по заданиям выполнить запросы к БД.

===== Пояснения к выполнению работы =====

===== Общие положения =====

Создать новую базу данных с названием DB_Books с помощью оператора Create Database, создать в ней перечисленные таблицы c помощью
операторов Create table по примеру лабораторной работы №1. Сохранить файл программы с названием ФамилияСтудента_ЛАб_1_DB_Books. В
утилите SQL Server Management Studio с помощью кнопки «Создать запрос» создать отдельные программы по каждому запросу, которые сохранять на диске с названием: ФамилияСтудента_ЛАб_2_№_задания. В сами программы копировать текст задания в виде комментария. Можно сохранять все выполненные запросы в одном файле. Для проверки работы операторов SELECT предварительно создайте программу, которая с помощью операторов INSERT заполнит все таблицы БД DB_Books несколькими записями, сохраните программы с названием ФамилияСтудента_ЛАб_2_Insert.

Оператор Select используется для выбора данных из таблиц.

Для выбора данных из некоторой таблицы нет необходимости знать имена всех ее полей. Звездочка (*) после оператора SELECT означает выбор всех столбцов таблицы. Другими словами, эта команда просто выводит все данные таблицы. Синтаксис: 
<code sql>
SELECT * 
FROM <имя таблицы> 
</code>

Если необходимо выбрать только определенные столбцы, то нужно их непосредственно указать после слова Select.

**Пример.** Выбрать сведения о количестве страниц из таблицы Books (поле Pages).

<code sql>
SELECT Pages
FROM Books
</code>

**Пример.** Выбрать все данные из таблицы Books.

<code sql>
SELECT *
FROM Books
</code>


===== Сортировка =====
Для упорядочения данных по какому-то полю необходимо выполнить команду
<code sql> ORDER BY <имя поля> </code> 
Записи можно упорядочивать в восходящем (параметр сортировки ASC) или в нисходящем (параметр сортировки DESC) порядке. Параметр сортировки ASC используется по умолчанию. 

**Пример.** Выбрать все сведения о книгах из таблицы Books и отсортировать результат по коду книги (поле Code_book). 

<code sql>
SELECT *
FROM Books
ORDER BY Code_book
</code>

Самостоятельно:
  *   Выбрать из таблицы Books коды книг, названия и количество страниц (поля Code_book, Title_book и Pages), отсортировать результат по названиям книг (поле Title_book по возрастанию) и по полю Pages (по убыванию).
  *   Выбрать из таблицы Deliveries список поставщиков (поля Name_delivery, Phone и INN), отсортировать результат по полю INN (по убыванию).

===== Изменение порядка следования полей =====

**Пример.** Выбрать все поля из таблицы Deliveries таким образом, чтобы в результате порядок столбцов был следующим: Name_delivery, INN, Phone, Address, Code_delivery.
<code sql>
SELECT Name_delivery, Phone, INN, Address_del, Code_delivery
FROM Deliveries
ORDER BY INN DESC
</code>

Самостоятельно:
  * Выбрать все поля из таблицы Publishing_house таким образом, чтобы в результате порядок столбцов был следующим: Publish, City, Code_publish.

===== Выбор некоторых полей из двух таблиц =====
Когда нужно выбрать данные, находящиеся в разных таблицах, применяют объединение таблиц. Пусть, например, необходимо выбрать данные, находящиеся в таблицах Books и Authors.

Простейший шаблон объединения двух таблиц выглядит следующим образом: 

<code sql>
SELECT * 
FROM <таблица1> <тип объединения> <таблица2> 
ON <таблица1>.<столбец1> = <таблица2>.<столбец2> 
</code>

Есть 4 типа объединения.
==== Внутреннее объединение (INNER JOIN) ====
 
При внутреннем объединении в таблицах А и В соединяются только те строки, для которых найдено совпадение, указанное в критерии объединения (после ключевого слова ON). Это наиболее подходящий в нашем случае вариант. Следующий запрос объединяет две таблицы Books и Authors, связанные по полю Code_author.

<code sql>
SELECT *
FROM Books INNER JOIN Authors
ON Books.Code_author = Authors.Code_author
</code>
 
==== Левое внешнее объединение (LEFT OUTER JOIN) ==== 
Левое внешнее объединение таблиц А и В включает в себя все строки из левой таблицы А и те строки из правой таблицы В, для которых обнаружено совпадение, указанное в критерии объединения (после ключевого слова ON). Для строк из таблицы А, для которых не найдено соответствия в таблице В, в столбцы, извлекаемые из таблицы В, заносятся значения NULL. Пример запроса:

<code sql>
SELECT *
FROM Books LEFT OUTER JOIN Authors
ON Books.Code_author = Authors.Code_author
</code>

==== Правое внешнее объединение (RIGHT OUTER JOIN) ====
 
Правое внешнее объединение таблиц А и В включает в себя все строки из правой таблицы В и те строки из левой таблицы А, для которых обнаружено совпадение, указанное в критерии объединения (после ключевого слова ON). Для строк из таблицы В, для которых не найдено соответствия в таблице А, в столбцы, извлекаемые из таблицы А заносятся значения NULL. Пример запроса:

<code sql>
SELECT *
FROM Books RIGHT OUTER JOIN Authors
ON Books.Code_author = Authors.Code_author
</code>
==== Полное объединение (FULL JOIN) ====

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

<code sql>
SELECT *
FROM Books FULL JOIN Authors
ON Books.Code_author = Authors.Code_author
</code>

Если необходимо вывести не все столбцы, например, два столбца из таблицы Books (Title_book и Authors) и один столбец из таблицы Authors. Для этого нужные столбцы надо явно указать (с учетом идентификатора таблицы, к которой они принадлежат):

<code sql>
SELECT Books.Title_book, Books.Pages, Authors.Name_author
FROM Books INNER JOIN Authors
ON Books.Code_author = Authors.Code_author
</code>

Самостоятельно:
  *  Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Deliveries выбрать имя соответствующего поставщика книги (поле Name_delivery).
  *  Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Publishing_house выбрать название соответствующего издательства и места издания (поля Publish и City).

===== Выбор строк с указанием критериев поиска (WHERE) =====
Предложение WHERE применяется для выбора записей, соответствующих определенным критериям. Критерий поиска состоит из одного или нескольких предикатов. Предикат задает проверку, выполняемую для каждой записи таблицы. Результат проверки может принимать одно из трех значений: «true», «false», «unknown». Команда извлекает для вывода только те строки из таблицы, для которых результат проверки равен «true». В таблице 1 приведен синтаксис различных видов сравнения.

{{:users:ruslan312:my_project:lab3_1.png|}}

Таблица 1

==== Точное совпадение значений одного из полей ====
Пример: вывести список названий издательств (поле Title_book) из таблицы Books, которые  находятся в(поле Publish) из таблицы Publishing_house . 
<code sql>
Select books.title_book, publishing_house.publish
From books Inner Join publishing_house
on books.code_publish = publishing_house.code_publish 
WHERE publishing_house.publish='Лань'
</code>

==== Использование вложенного запроса ====

**Пример:** Найти название книг, автором которых является Толстой
<code sql>
Select title_book
From books
Where code_author= ( Select code_author
                     From authors
                     Where name_author= 'Толстой')
</code>      
Этот запрос работает если вложенный подзапрос выдает одно число. Если в базе данных есть несколько записей, соответствующих фамилия писателя "Толстой" то запрос завершится с ошибкой  в этом случае лучше использовать конструкцию In: 
<code sql>
Select title_book
From books
Where code_author in ( Select code_author
                     From authors
                     Where name_author= 'Пушкин')  
</code>           
==== Точное несовпадение значений одного из полей ====
**Пример.** Вывести список названий издательств (поле Publish) из таблицы Publishing_house, которые не находятся в городе ‘Москва’ (условие по полю City).
<code sql>
SELECT Publish
FROM Publishing_house
WHERE City <> 'Москва'
</code>

Самостоятельно:
  *  Вывести список названий книг (поле Title_book) из таблицы Books,которые выпущены любыми издательствами, кроме издательства ‘Питер-Софт’ (поле Publish из таблицы Publishing_house).

==== Выбор записей по диапазону значений (Between) ====
**Пример.** Вывести фамилии, имена, отчества авторов (поле Name_author) из таблицы Authors, у которых дата рождения (поле Birthday) находится в диапазоне 01.01.1840 – 01.06.1860.
<code sql>
SELECT name_author
FROM Authors
WHERE Birthday BETWEEN '01.01.1940' AND '01.01.1960'
</code>

Самостоятельно:
  *  Вывести список названий книг (поле Title_book из таблицы Books)и количество экземпляров (поле Amount из таблицы Purchases),которые были закуплены в период с 12.03.2003 по 15.06.2003 (условие по полю Date_order из таблицы Purchases).
  *  Вывести список названий книг (поле Title_book) и количество страниц (поле Pages) из таблицы Books, у которых объем в страницах укладывается в диапазон 200 – 300 (условие по полю Pages).
  *  Вывести список фамилий, имен, отчеств авторов (поле Name_author) из таблицы Authors, у которых фамилия начинается на одну из букв диапазона ‘В’ – ‘Г’ (условие по полю Name_author).

==== Выбор записей по диапазону значений (In) ====
**Пример.** Вывести список названий книг (поле Title_book из таблицы Books)и количество (поле Amount из таблицы Purchases), которые были поставлены поставщиками с кодами 3, 7, 9, 11 (условие по полю Code_delivery из таблицы Purchases).

<code sql>
SELECT Books.Title_book, Purchases.Amount
FROM Books INNER JOIN Purchases
ON Books.Code_book = Purchases.Code_book
WHERE Purchases.Code_delivery IN (3,7,9,11)
</code>

Самостоятельно:
  *  Вывести список названий книг (поле Title_book) из таблицы Books, которые выпущены следующими издательствами: ‘Питер-Софт’, ‘Альфа’,‘Наука’ (условие по полю Publish из таблицы Publishing_house).
  *  Вывести список названий книг (поле Title_book) из таблицы Books,которые написаны следующими авторами: ‘Толстой Л.Н.’, ‘Достоевский Ф.М.’, ‘Пушкин А.С.’ (условие по полю Name_author из таблицы Authors).

==== Условие неточного совпадения. Выбор записей с использованием Like ====
**Пример.** Выбрать из справочника поставщиков (таблица Deliveries) названия компаний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) начинается с ‘ОАО’.
<code sql>
SELECT Name_company, Phone, INN
FROM Deliveries
WHERE Name_company LIKE 'ОАО%'
</code>

Самостоятельно:
  *  Выбрать из таблицы Books названия книг и количество страниц (поля Title_book и Pages), а из таблицы Authors выбрать имя соответствующего автора книг (поле Name_ author), у которых название книги начинается со слова ‘Мемуары’.
  * Выбрать из таблицы Authors фамилии, имена, отчества авторов (поле Name_ author), значения которых начинаются с ‘Иванов’.
  * Вывести названия издательств (поле Publish) из таблицы Publishing_house, которые содержат в названии сочетание ‘софт’.
  * Выбрать названия компаний (поле name_company) из таблицы Deliveries, у которых значение оканчивается на ‘ский’.

==== Выбор записей по нескольким условиям ====
**Пример.** Выбрать коды поставщиков (поле Code_delivery), даты заказов (поле Date_order) и названия книг (поле Title_book), если количество книг (поле Amount) в заказе больше 100 или цена (поле Cost) за книгу находится в диапазоне от 200 до 500.
<code sql>
SELECT Code_delivery, Date_order, Title_book
FROM Purchases INNER JOIN Books 
on books.code_book=Purchases.code_book
WHERE Amount > '100' OR
Cost BETWEEN '200' AND '500'
</code>

Самостоятельно:
  *  Выбрать коды авторов (поле Code_author), имена авторов (поле Name_author), названия соответствующих книг (поле Title_book), если код издательства (поле Code_Publish) находится в диапазоне от 10 до 25 и количество страниц (поле Pages) в книге больше 120.
  *  Вывести список издательств (поле Publish) из таблицы Publishing_house, в которых выпущены книги, названия которых (поле Title_book)начинаются со слова ‘Труды’ и город издания (поле City) – ‘Новосибирск’.

==== Многотабличные запросы (выборка из двух таблиц, выборка из трех таблиц с использованием JOIN) ====
**Пример.** Вывести список названий компаний-поставщиков (поле Name_company) и названия книг (поле Title_book), которые они поставили в период с 01.01.2002 по 31.12.2003 (условие по полю Date_order).
<code sql>
SELECT books.title_book, deliveries.name_company
FROM (books INNER JOIN purchases
ON books.code_book=purchases.code_book) INNER JOIN deliveries
     ON purchases.code_delivery=deliveries.code_delivery
     WHERE purchases.date_order BETWEEN '01.01.2010' AND '31.12.2011'
</code>

Самостоятельно:
  *  Вывести список авторов (поле Name_author), книги которых были выпущены в издательстве ‘Лань’ (условие по полю Publish).
  *  Вывести список поставщиков (поле Name_company), которые поставляют книги издательства ‘Питер’ (условие по полю Publish).
  *  Вывести список авторов (поле Name_author) и названия книг (поле Title_book), которые были поставлены поставщиком ‘ОАО Книготорг’ (условие по полю Name_company).

==== Вычисления ====

Рассмотрим вычисления с использованием агрегатных функций 

Агрегатные функции – это функции столбца, предназначенные для того, чтобы вычислять некоторое значение для заданного множества строк. Таким множеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или вся таблица. Если запросом определено условие WHERE, то для вычисления используются только значения выбранных таким образом строк (то есть тех записей, которые удовлетворяют условию WHERE). 

Рассмотрим следующие агрегатные функции: 
  *  SUM – возвращает сумму значений столбца группы записей; 
  *  COUNT – возвращает количество записей группы; 
  *  AVG – возвращает среднее значение столбца группы записей; 
  *  MIN – возвращает минимальное значение столбца группы записей; 
  *  MAX – возвращает максимальное значение столбца группы записей; 
 
Агрегатные функции используются подобно именам полей  в  предложении SELECT запроса, но с одним исключением: они берут имя поля как аргумента. Только числовые поля могут использоваться с SUM и AVG. С COUNT, MAX, и MIN, могут использоваться и числовые, и символьные поля. 
 
**Пример.** Определим количество поставщиков книг 
<code sql>
SELECT COUNT(code_delivery) 
FROM Deliveries
</code> 

**Пример.** Определить количество книг, имеющих 300 страниц
<code sql> 
SELECT COUNT(code_book) 
FROM Books 
WHERE Pages >= 300 
</code>

Предложение GROUP BY используется для определения группы выходных строк, к которым могут применяться агрегатные функции. 
**Пример.** Определить количество книг, соответствующих определенным авторам
<code sql>
SELECT code_author, COUNT (code_book) 
FROM Books 
WHERE Pages >= 300 
GROUP BY code_author
</code>
 
Предложение HAVING определяет критерии отбора для групп записей, является аналогом предложения WHERE, которое определяет критерии отбора для индивидуальных строк. В запросе SQL предложение HAVING следует за предложением GROUP BY. 
Пример. Вывести коды авторов, написавших более одной книги
<code sql> 
SELECT code_author, COUNT (code_book) 
FROM Books 
WHERE Pages >= 300 
GROUP BY code_author
HAVING COUNT (code_book) >1
</code>

Самостоятельно: 

  *  Вывести суммарную стоимость партии одноименных книг (использовать поля Amount и Cost) и название книги (поле Title_book) в каждой поставке.
  *  Вывести стоимость одной печатной страницы каждой книги (использовать поля Cost и Pages) и названия соответствующих книг (поле Title_book).
  *  Вывести количество лет с момента рождения авторов (использовать поле Birthday) и имена соответствующих авторов (поле Name_author).

==== Вычисление итоговых значений с использованием агрегатных функций ====
**Пример.** Вывести общую сумму поставок книг (использовать поле Cost),выполненных `Спектр` (условие по полю Name_company).
<code sql>
USE DB_book
SELECT  SUM(purchases.cost*purchases.amount) AS value
FROM deliveries INNER JOIN purchases 
ON deliveries.code_delivery = purchases.code_delivery
WHERE deliveries.name_company = 'Спектр'
</code>
  *  Вывести общее количество всех поставок (использовать любое поле из таблицы Purchases), выполненных в период с 01.01.2010 по 01.02.2013 (условие по полю Date_order).
  *  Вывести среднюю стоимость (использовать поле Cost) и среднее количество экземпляров книг (использовать поле Amount) в одной поставке, где автором книги является `Акунин` (условие по полю Name_author).
  *  Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с минимальной общей стоимостью (использовать поля Cost и Amount).
  *  Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с максимальной общей стоимостью (использовать поля Cost и Amount).

==== Изменение наименований полей ====
**Пример.** Вывести название книги (поле Title_book), суммарную стоимость партии одноименных книг (использовать поля Amount и Cost), поместив в результат в поле с названием Itogo, в поставках за период с 01.01.2010 по 01.06.2012 (условие по полю Date_order).
<code sql>
USE DB_book
SELECT books.title_book, purchases.cost*purchases.amount AS Itogo
FROM books INNER JOIN purchases 
ON books.code_book = purchases.code_book
WHERE purchases.date_order BETWEEN  '01.01.2010' AND '01.06.2012' 
</code>
  *  Вывести стоимость одной печатной страницы каждой книги (использовать поля Cost и Pages), поместив результат в поле с названием One_page, и названия соответствующих книг (поле Title_book).
  *  Вывести общую сумму поставок книг (использовать поле Cost) и поместить результат в поле с названием Sum_cost, выполненных ‘ОАО Луч’ (условие по полю Name_company).

==== Использование переменных в условии ==== 
**Пример.** Вывести список книг (поле Title_book), которых закуплено меньше, чем указано в запросе пользователя (условие с использованием поля Amount).
<code sql>
USE DB_book 
DECLARE @min_amount INT -- объявляем целочисленную переменную 
SET @min_amount = 10    -- присваиваем этой переменной значение 10
SELECT books.title_book, purchases.amount
FROM books INNER JOIN purchases 
ON books.code_book = purchases.code_book
WHERE  amount < @min_amount  -- выбираем из таблицы только те записи названий книг, 
                             -- количество которых меньше @min_amount 
</code>
  *  Вывести список сделок (все поля из таблицы Purchases) за последний месяц (условие с использованием поля Date_order).
  *  Вывести список авторов (поле Name_author), возраст которых меньше заданного пользователем (условие с использованием поля Birthday).


===== Дополнительные задания =====
=== Оператор обработки данных Update ===

  *  Изменить в таблице Books содержимое поля Pages на 300, если код автора (поле Code_author) =56 и название книги (поле Title_book) =’Мемуары’.
  *  Изменить в таблице Deliveries содержимое поля Address на ‘нет сведений’, если значение поля является пустым.
  *  Увеличить в таблице Purchases цену (поле Cost) на 20 процентов, если заказы были оформлены в течение последнего месяца (условие по полю Date_order).

=== Оператор обработки данных Insert ===

  *  Добавить в таблицу Purchases новую запись, причем так, чтобы код покупки (поле Code_purchase) был автоматически увеличен на единицу, а в тип закупки (поле Type_purchase) внести значение ‘опт’.
  *  Добавить в таблицу Books новую запись, причем вместо ключевого поля поставить код (поле Code_book), автоматически увеличенный на единицу от максимального кода в таблице, вместо названия книги (поле Title_book) написать ‘Наука. Техника. Инновации’.
  *  Добавить в таблицу Publish_house новую запись, причем вместо ключевого поля поставить код (поле Code_publish), автоматически увеличенный на единицу от максимального кода в таблице, вместо названия города – ‘Москва’ (поле City), вместо издательства – ‘Наука’ (поле Publish).

=== Оператор обработки данных Delete ===

  *  Удалить из таблицы Purchases все записи, у которых количество книг в заказе (поле Amount) = 0.
  *  Удалить из таблицы Authors все записи, у которых нет имени автора в поле Name_Author.
  *  Удалить из таблицы Deliveries все записи, у которых не указан ИНН (поле INN пусто)



====== Приложение ======
====== Создание базы данных ======
<code sql>
create database DB_BOOKS

use DB_BOOKS
CREATE TABLE Authors(
   Code_author INT PRIMARY KEY NOT NULL, -- Код автора 
   Name_author CHAR(30),                 -- Фамилия автора
   Birthday DATETIME                     -- Дата рождения
)

CREATE TABLE Publishing_house(
    Code_publish INT PRIMARY KEY NOT NULL, -- Код издательства
    Publish CHAR(30),                      -- Название издательства
    City CHAR(20)                          -- Город издательства
)

CREATE TABLE Books(
    Code_book INT PRIMARY KEY NOT NULL,    -- Код книги
    Title_book CHAR(40),                   -- Название книги
    Code_author INT FOREIGN KEY REFERENCES Authors(Code_author),
    Pages INT,
    Code_publish INT FOREIGN KEY REFERENCES Publishing_house(Code_publish)
)

CREATE TABLE Deliveries(
    Code_delivery INT PRIMARY KEY NOT NULL, -- Код доставщика
    Name_delivery CHAR(30),                 -- Наименование доставщика
    Name_company CHAR(20),                  -- Наименование компании
    Address_company VARCHAR(100),           -- Адрес
    Phone BIGINT,                           -- Телефон
    INN CHAR(13)                            -- ИНН
)

CREATE TABLE Purchases(
    Code_purchase INT PRIMARY KEY NOT NULL, -- Код продажи
    Code_book INT FOREIGN KEY REFERENCES Books(Code_book),
    Date_order SMALLDATETIME,               -- Дата
    Code_delivery INT FOREIGN KEY REFERENCES Deliveries(Code_delivery), 
    Type_purchase BIT,                      -- Тип продажи
    Cost FLOAT,                             -- Цена
    Amount INT                              -- Количество
)

</code>


====== Контрольные вопросы. ======
  * Для чего применяется предложение WHERE?
  * Сколько существует типов объединения? Опишите их.
  * Как сделать выбор записей по диапазону значений?
  * Что такое вложенный запрос?
  * Для чего используются агрегатные функции?
  * Для чего используется конструкция SELECT * ?
  * Как можно объединить в запросе три таблицы?



\ 

Назад: [[users:ruslan312:my_project:index]]

{{tag>}}