Содержание

NO:user
READ:@ALL
EDIT:@ALL

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

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

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

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

  1. Создать с помощью приведенных операторов пример базы данных «Книжное дело», описанный в предыдущей лабораторной работе (если БД отсутствует на сервере).
  2. С помощью операторов Insert создать программу в SQL Server Management Studio через «Создать запрос» для заполнения таблиц данными (по 3-5 записей).
  3. С помощью оператора 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 означает выбор всех столбцов таблицы. Другими словами, эта команда просто выводит все данные таблицы. Синтаксис: 

SELECT * 
FROM <имя таблицы>

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

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

SELECT Pages
FROM Books

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

SELECT *
FROM Books

Сортировка

Для упорядочения данных по какому-то полю необходимо выполнить команду 

 ORDER BY <имя поля>

Записи можно упорядочивать в восходящем (параметр сортировки ASC) или в нисходящем (параметр сортировки DESC) порядке. Параметр сортировки ASC используется по умолчанию.

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

SELECT *
FROM Books
ORDER BY Code_book

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

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

Пример. Выбрать все поля из таблицы Deliveries таким образом, чтобы в результате порядок столбцов был следующим: Name_delivery, INN, Phone, Address, Code_delivery. 

SELECT Name_delivery, Phone, INN, Address_del, Code_delivery
FROM Deliveries
ORDER BY INN DESC

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

Выбор некоторых полей из двух таблиц

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

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

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

Есть 4 типа объединения.

Внутреннее объединение (INNER JOIN)

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

SELECT *
FROM Books INNER JOIN Authors
ON Books.Code_author = Authors.Code_author

Левое внешнее объединение (LEFT OUTER JOIN)

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

SELECT *
FROM Books LEFT OUTER JOIN Authors
ON Books.Code_author = Authors.Code_author

Правое внешнее объединение (RIGHT OUTER JOIN)

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

SELECT *
FROM Books RIGHT OUTER JOIN Authors
ON Books.Code_author = Authors.Code_author

Полное объединение (FULL JOIN)

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

SELECT *
FROM Books FULL JOIN Authors
ON Books.Code_author = Authors.Code_author

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

SELECT Books.Title_book, Books.Pages, Authors.Name_author
FROM Books INNER JOIN Authors
ON Books.Code_author = Authors.Code_author

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

Выбор строк с указанием критериев поиска (WHERE)

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

Таблица 1

Точное совпадение значений одного из полей

Пример: вывести список названий издательств (поле Title_book) из таблицы Books, которые находятся в(поле Publish) из таблицы Publishing_house . 

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='Лань'

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

Пример: Найти название книг, автором которых является Толстой 

SELECT title_book
FROM books
WHERE code_author= ( SELECT code_author
                     FROM authors
                     WHERE name_author= 'Толстой')

Этот запрос работает если вложенный подзапрос выдает одно число. Если в базе данных есть несколько записей, соответствующих фамилия писателя «Толстой» то запрос завершится с ошибкой в этом случае лучше использовать конструкцию In: 

SELECT title_book
FROM books
WHERE code_author IN ( SELECT code_author
                     FROM authors
                     WHERE name_author= 'Пушкин')

Точное несовпадение значений одного из полей

Пример. Вывести список названий издательств (поле Publish) из таблицы Publishing_house, которые не находятся в городе ‘Москва’ (условие по полю City). 

SELECT Publish
FROM Publishing_house
WHERE City <> 'Москва'

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

Выбор записей по диапазону значений (Between)

Пример. Вывести фамилии, имена, отчества авторов (поле Name_author) из таблицы Authors, у которых дата рождения (поле Birthday) находится в диапазоне 01.01.1840 – 01.06.1860. 

SELECT name_author
FROM Authors
WHERE Birthday BETWEEN '01.01.1940' AND '01.01.1960'

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

Выбор записей по диапазону значений (In)

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

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)

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

Условие неточного совпадения. Выбор записей с использованием Like

Пример. Выбрать из справочника поставщиков (таблица Deliveries) названия компаний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) начинается с ‘ОАО’. 

SELECT Name_company, Phone, INN
FROM Deliveries
WHERE Name_company LIKE 'ОАО%'

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

Выбор записей по нескольким условиям

Пример. Выбрать коды поставщиков (поле Code_delivery), даты заказов (поле Date_order) и названия книг (поле Title_book), если количество книг (поле Amount) в заказе больше 100 или цена (поле Cost) за книгу находится в диапазоне от 200 до 500. 

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'

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

Многотабличные запросы (выборка из двух таблиц, выборка из трех таблиц с использованием JOIN)

Пример. Вывести список названий компаний-поставщиков (поле Name_company) и названия книг (поле Title_book), которые они поставили в период с 01.01.2002 по 31.12.2003 (условие по полю Date_order). 

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'

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

Вычисления

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

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

Рассмотрим следующие агрегатные функции:

Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением: они берут имя поля как аргумента. Только числовые поля могут использоваться с SUM и AVG. С COUNT, MAX, и MIN, могут использоваться и числовые, и символьные поля.

Пример. Определим количество поставщиков книг 

SELECT COUNT(code_delivery) 
FROM Deliveries

Пример. Определить количество книг, имеющих 300 страниц 

SELECT COUNT(code_book) 
FROM Books 
WHERE Pages >= 300

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

SELECT code_author, COUNT (code_book) 
FROM Books 
WHERE Pages >= 300 
GROUP BY code_author

Предложение HAVING определяет критерии отбора для групп записей, является аналогом предложения WHERE, которое определяет критерии отбора для индивидуальных строк. В запросе SQL предложение HAVING следует за предложением GROUP BY. Пример. Вывести коды авторов, написавших более одной книги 

SELECT code_author, COUNT (code_book) 
FROM Books 
WHERE Pages >= 300 
GROUP BY code_author
HAVING COUNT (code_book) >1

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

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

Пример. Вывести общую сумму поставок книг (использовать поле Cost),выполненных `Спектр` (условие по полю Name_company). 

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 = 'Спектр'

Изменение наименований полей

Пример. Вывести название книги (поле Title_book), суммарную стоимость партии одноименных книг (использовать поля Amount и Cost), поместив в результат в поле с названием Itogo, в поставках за период с 01.01.2010 по 01.06.2012 (условие по полю Date_order). 

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'

Использование переменных в условии

Пример. Вывести список книг (поле Title_book), которых закуплено меньше, чем указано в запросе пользователя (условие с использованием поля Amount). 

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

Дополнительные задания

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

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

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

Приложение

Создание базы данных

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                              -- Количество
)

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

 

Назад: Лабораторная работа №3