Лабораторная работа "Извлечение данных средствами Transact SQL"

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

Тема: "Извлечение данных средствами Transact SQL"

Цель работы:  усвоить способы создания выборки данных в среде СУБД MS SQL Server 2016.

Краткие теоретические сведения

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

Построитель запросов (Query Designer)

Окно Query Editor отображается в верхней части окна SQL Server Management Studio. Для запуска редактора запросов можно либо нажать в панели инструментов SQL Server Management Studio кнопку New Query, либо выбрать в меню пункт File - New - SQL Server Query - SQL Server Query.

Преимуществом редактора запросов является то, что он может работать как при открытом соединении, так и без него. По умолчанию редактор запросов не подключен к серверу, однако как только разработчик начинает создание нового запроса, Query Editor самостоятельно определяет параметры зарегистрированного сервера и устанавливает с ним соединение. Еще одним преимуществом редактора запросов является то, что он поддерживает возможность автоматического выделения цветом содержащегося в окне кода. Это позволяет выделить в коде примечания, ошибки и фрагменты кода, что значительно упрощает работу программиста. Более того, Query Editor, поддерживает понятие проекта, в котором файлы сгруппированы по логическому принципу. Помимо этого в редакторе запросов поддерживается возможность использовать Visual SourceSafe для контроля над версиями создаваемых проектов и отдельных файлов со сценариями. Это позволяет отслеживать изменения в отдельных версиях файлов, а также сохранять сценарии T-SQL в виде отдельных файлов, чтобы обеспечить возможность дальнейшего переноса. Использование специального программного обеспечения для контроля версий позволяет реализовать возможность хранения исходных кодов программ, проектов и отдельных сценариев, ассоциированных с выбранной схемой базы данных. Это позволяет однозначно идентифицировать автора изменений или отдельных фрагментов кода, а также ограничить применение программного обеспечения для модификации кода.

Редактор обладает средствами автоматической генерации кода скриптов.

Первая возможность — воспользоваться средствами автоматической генерации скриптов из Object Explorer

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

Второй вариант — воспользоваться графическим построителем запросов Query Designer в SQL Server Management Studio. Это средство особенно удобно в тех ситуациях, когда вам нужно создать большой запрос, со множеством соединений, условий и сортировок. С его помощью можно создавать очень сложные запросы, вообще не имея никакого представления о синтаксисе языка Transact-SQL.

Проще всего использовать построитель запроса так: нужно создать в окне редактора скриптов пустой скрипт и щелкнуть правой кнопкой мыши по пустому пространству в этом окне. Затем в контекстном меню нужно выбрать пункт Design Query in Editor (Спроектировать запрос в редакторе)

Откроется окно Query Designer, в котором можно будет выбрать нужные таблицы, столбцы в них, назначить столбцам псевдонимы, выбрать порядок сортировки, фильтры и т. п.

Кроме того, если вы хотите создать скрипт для какой-либо административной операции (создание логина, предоставление разрешений и т. п.), то в вашем распоряжении — кнопка Script (Скрипт) в верхней части окна SQL Server Management Studio. При помощи этой кнопки можно автоматически создать скрипт, в который будут подставлены введенные вами на графическом экране значения.

Меню Query содержит следующие основные команды:

КОМАНДА SELECT

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

               SELECT snum, sname, sity, comm 

      FROM  Salespeople;

Если вы хотите видеть все столбцы таблицы, вы можете заменить  список звездочкой (*).

       SELECT * 

       FROM Salespeople; 

DISTINCT (ОТЛИЧИЕ) - аргумент который обеспечивает Вас способом устранять повторяющиеся значения из вашего предложения SELECT. DISTINCT может указываться только один раз в данном предложении SELECT.

          SELECT DISTINCT snum 

          FROM Orders; 

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

      SELECT * 

      FROM Customers 

      WHERE rating = 100; 

Основные Булевы операторы также распознаются в SQL. Выражения Буля - являются или верными или неверными, подобно предикатам. Булевы операторы связывают одно или более верных/неверных значений и производят единственное верное/или/неверное значение. Стандартными операторами Буля распознаваемыми в SQL являются: AND, OR, и NOT. Связывая предикаты с операторами Буля, вы можете значительно увеличить их возможности.

    SELECT  * 

    FROM Customers 

             WHERE city = " San Jose'  AND rating > 200;

NOT может использоваться для инвертирования значений Буля. Имеется пример запроса с NOT:

                       SELECT * 

                       FROM Customers 

                       WHERE city = " San Jose'  OR NOT rating > 200; 

Оператор IN определяет набор значений в которое данное значение может или не может быть включено.

          SELECT * 

          FROM Salespeople 

                   WHERE city IN ( 'Barcelona', 'London' );

Как вы можете видеть, IN определяет набор значений с помощью имен членов набора заключенных в круглые скобки и отделенных запятыми. Он затем проверяет различные значения указанного пол пытаясь найти совпадение со значениями из набора. Если это случается, то предикат верен

Оператор BETWEEN похож на оператор IN. В отличии от определения по номерам из набора, как это делает IN, BETWEEN определяет диапазон, значения которого должны уменьшаться что делает предикат верным. Вы должны ввести ключевое слово BETWEEN с начальным значением, ключевое AND и конечное значение. В отличие от IN, BETWEEN чувствителен к порядку, и первое значение в предложении должно быть первым по алфавитному или числовому порядку. (Обратите Внимание что, в отличие от Английского языка, SQL не говорит что "значение находится (между) BETWEEN значением и значением", а просто "значение BETWEEN значение значение". Это применимо и к оператору LIKE). Следующий пример будет извлекать из таблицы Продавцов всех продавцов с комиссионными между 10 и 12 (вывод показывается в Рисунке 5.4):

             SELECT * 

             FROM Salespeople 

                      WHERE comm BETWEEN 10 AND 12;

подобно реляционным операторам, BETWEEN может работать с символьными полями в терминах эквивалентов ASCII. Это означает что вы можете использовать BETWEEN чтобы выбирать ряд значений из упорядоченных по алфавиту значений.

               SELECT * 

               FROM Customers 

                        WHERE cname BETWEEN 'A' AND 'G';

LIKE применим только к полям типа CHAR или VARCHAR, с которыми он используется чтобы находить подстроки. Т.е. он ищет поле символа чтобы видеть, совпадает ли с условием часть его строки. В качестве условия он использует групповые символы(wildkards) - специальные символы которые могут соответствовать чему-нибудь. Имеются два типа групповых символов используемых с LIKE:

·                   символ подчеркивания ( _ ) замещает любой одиночный символ. Например, 'b_t' будет соответствовать словам 'bat' или 'bit', но не будет соответствовать 'brat'.

·                    знак процента (%) замещает последовательность любого числа символов (включая символы нуля). Например '%p%t' будет соответствовать словам 'put', 'posit', или 'opt', но не 'spite'.

Давайте найдем всех заказчиков чьи имена начинаются с G:

         SELECT 

          FROM Customers 

                   WHERE cname LIKE 'G%';

Часто, будут иметься записи в таблице которые не имеют никаких значений для каждого пол, например потому что информация не завершена, или потому что это поле просто не заполнялось. SQL учитывает такой вариант, позволяя вам вводить значение NULL(ПУСТОЙ) в поле, вместо значения. Так как NULL указывает на отсутствие значения, вы не можете знать каков будет результат любого сравнения с использованием NULL. Когда NULL сравнивается с любым значением, даже с другим таким же NULL, результат будет ни верным ни неверным, он - неизвестен. SQL предоставляет специальный оператор IS, который используется с ключевым словом NULL, для размещения значения NULL. Найдем все записи в нашей таблице Заказчиков с NULL значениями в city столбце:

               SELECT * 

               FROM Customers 

               WHERE city IS NULL; 

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

SELECT COUNT (*) 

 FROM Customers 

COUNT со звездочкой включает и NULL и дубликаты, по этой причине DISTINCT не может быть использован. Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого пол, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять пол и агрегатные функции в едином предложении SELECT. Например, предположим что вы хотите найти наибольшую сумму приобретений полученную каждым продавцом. 

               SELECT snum, MAX (amt)                           

                FROM  Orders                                   

                GROUP BY snum;                                 

Вы не сможете использовать агрегатную функцию в предложении WHERE (если вы не используете подзапрос, описанный позже), потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции оцениваются в терминах групп строк. Аргументы в предложении HAVING следуют тем же самым правилам что и в предложении SELECT, состоящей из команд использующих GROUP BY. Они должны иметь одно значение на группу вывода.

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

Содержание отчета:

1.Название и цель работы

2.Индивидуальное задание

3.Скрипты запросов

Индивидуальное задание

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

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

1.Возможности меню Query Editor

2.Опишите конструкцию оператора SELECT