SQL_LektsiiRus

Например, в заказе номер 11000 присутствуют три разные позиции, составляющие в сумме 85, а 85 : 3 = 28.33. Среднее значение равно 28. 33, так зачем его округлять до 28. Причиной получения этих данных является то, что на них распространяются правила приведения типа. В данном случае расчеты в системе начинались с целых чисел, поэтому система обеспечила и возврат результатов в виде целых чисел (несмотря на то, что это привело к потере десятых долей в данных).

Функции MIN и MAX. Определяют минимальное и максимальное значения для каждой группировки в выбранном столбце.

Применим функцию MIN:

А что было бы, если бы потребовалось одновременно воспользоваться функциями MIN() и МАХ() Это также возможно. достаточно включить обе эти функции в запрос:

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

имена столбцов в результирующем наборе и создаются с использованием ключевого слова АS, как показано ниже.

Ключевое слово АS является необязательным. Его можно не писать. Псевдоним можно задать для любого столбца (и даже для имени таблицы), а не только для результатов агрегирования. Настоятельно рекомендуется использовать слово АS.

Запрос, в котором зададим псевдонимы для каждого столбца, а в некоторых позициях не будем использовать ключевое слово АS:

Функция COUNT (Ехрression|*). Предназначена для вычисления количества строк в результатах запроса. Рассмотрим одну из наиболее широко применяемых разновидностей запроса:

Поэтому вначале выясним, что представляет собой этот набор записей. Он содержит количество строк, соответствующих условию WHERE запроса, относящемуся к таблице (таблицам) в конструкции FROM.

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

Данный запрос выведет общее количество строк в таблице Employees. Эта функция применяется с выражением (обычно с именем столбца).

Запрос, в котором для подсчета количества строк применяется конкретный столбец (Fax):

При выполнении операций агрегирования или других операций с множествами NULL—значения не учитываются.

Функция СОUNТ при ее использовании в любой форме, отличной от СОUNТ (*), игнорирует NULL-значения. Попытаемся проверить, действительно ли Причиной расхождения являются NULL -значения:

В действительности NULL-значения игнорируются во всех агрегирующих функциях, кроме COUNT (*). В частности, многие пользователи полагают, что при вычислении средних величин в столбцах с числовыми данными NULL -значения рассматриваются как равные нулю, но NULL -значения не равны нулю и не должны использоваться как таковые. Если функция AVG или другая агрегирующая функция применяется к столбцу с NULL-значениями, то эти значения не войдут в состав операции агрегирования, если с помощью каких-либо манипуляций они не будут преобразованы в значения, отличные от NULL, в пределах вызова самой функции (например, с использованием функции СОАLЕSСЕ () или ISNUL().

Прежде чем закончить описание конструкций функции СОUNT, рассмотрим, как эта функция применяется с конструкцией GROUP BY.

Предположим, что программист получил задание определить количество служащих, которые подчинены каждому руководителю. В тех операторах, которые применялись перед этим, подсчитывалось либо общее количество строк в таблице (СОUNT (*)), либо количество всех строк в таблице, не имеющих NULL-значений (СОUNT (СоlumnName). А после введения конструкции GROUP BY те же варианты агрегирования продолжают действовать, как и до сих пор, за исключением того, что происходит возврат данных о количестве, относящихся к каждой группировке, а не ко всей таблице. Именно этот подход можно применить для получения данных о количестве служащих, которые подчинены каждому руководителю:

Обратите внимание на то, что группирование осуществляется только по столбцу ReportsTO, а функция СOUNT () выполняет роль агрегирующей функции, поэтому включать используемый в ней столбец в конструкцию GROUP BY не требуется.

На основании полученных результатов можно судить о том, что руководитель с идентификатором ManagerID, равным 2, имеет в подчинении пять служащих, а руководителю с идентификатором ManagerID, равным 5, подчиняются трое служащих. Кроме того, можно сделать вывод, что в одной записи EmployeesID в поле ReportsTo содержится NULL-значение; на этом основании можно сделать вывод, что данный служащий никому не подчиняется (по-видимому это — президент компании).

Очевидно, следует отметить, что с формальной точки зрения конструкция GROUP BY может использоваться без каких-либо агрегирующих функций, но такой способ ее применения не имеет смысла. Дело в том, что в этом случае СУБД SQL Server обрабатывает последовательно все строки в целях их группирования, но с точки зрения осуществляемых при этом действий тот же результат может быть получен с применением опции DISTINCT.

Все строки с повторяющимися значениями в поле OrderID исключены еще до выполнения операции агрегирования, поэтому количество строк стало намного меньше.

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

предусмотрен в предикате ALL, происходит во всех тех случаях, когда не используется предикат DISTINCT.

3.3Оператор INSERT

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

INSERT [INTO]<таблица> [(список столбцов)] VALUES (значения данных)

Рассмотрим последовательно структуру этого оператора.

Ключевое слово INSERT сообщает СУБД SQL Server, что должна быть выполнена вставка данных, а все, что следует за этим ключевым словом, является уточнением деталей требуемого действия. Его единственным назначением является повышение удобства чтения оператора. Без ключевого слова INTO можно полностью обойтись, но его рекомендуют использовать

— благодаря нему значительно повышается удобство чтения.

За ключевым словом INТО следует имя таблицы, в которую должны быть вставлены данные, а также определен список столбцов. Явно заданный список столбцов (в котором должен быть конкретно указан каждый столбец, принимающий вводимые значения) является необязательным. Если явно заданный список столбцов не предусмотрен, то предполагается, что каждое значение в операторе INSERT должно соответствовать столбцу, находящемуся в той же порядковой позиции в строке таблицы, что и само вводимое значение (первое значение вводится в первый столбец, второе значение — во второй и т.д.). Кроме того, значение должно быть задано для каждого столбца, который не принимает NULL-значений и не имеет значения, заданного по умолчанию, пока не будет достигнут последний столбец. Короче говоря, в этой части оператора должен быть приведен список из одного или нескольких столбцов.

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

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

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

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

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

Рассмотрим следующий запрос:

Не включать столбец в список столбцов и не предоставлять для него никаких данных в операторе INSERT допускается в нескольких ситуациях:

-столбец не является обязательным, иными словами, он допускает наличие NULL-значений;

-столбец определен со значением, заданным по умолчанию;

-столбец определен как предназначенный для получения значения, вырабатываемого системой, т.е. является столбцом идентификации. Система его заполняет сама, путем автоматической выработки

значения идентификации. Наиболее часто таковым является значение IDENTITY. Заполняя этот столбец, система, как правило, начинает отсчет в первой строке с 1, увеличивает это значение до 2, заполняя вторую строку, и т.д. Значения IDENTITY фактически нельзя рассматривать как ―номера строк‖, поскольку в дальнейшем строки могут быть удалены, а при определенных обстоятельствах происходит пропуск некоторых из этих значений, т.е. строго последовательная нумерация нарушается, но они оправдывают свое назначение в том, что обеспечивают наличие в каждой строке ее собственного уникального идентификатора.

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

Ранее мы рассматривали режим работы, при котором происходит единовременно вставка только одной строки, но иногда возникает необходимость во вставке целого блока данных.

Данные, вставляемые в таблицу, формируются из источников, перечисленных ниже.

Другая таблица в той же базе данных.

Полностью иная база данных на том же сервере.

Разнородный запрос на выборку информации из другой СУБД SQL Server или другие данные.

Та же таблица (в таком случае в операторе SELECT обычно предусмотрено выполнение над данными каких-либо

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

Все эти действия позволяет выполнять оператор INSERT INТО…SELECT. По своему синтаксису этот оператор представляет собой

комбинацию двух операторов — INSERT и SELECT. Данный синтаксис выглядит примерно так:

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

SELECT.

3.5 Модификация данных с помощью оператора

UPDATE

Оператор UPDATE обновляет существующие данные. Структура этого оператора имеет небольшие отличия по сравнению с оператором SELECT, но между этими двумя операторами можно обнаружить определенные аналогии. Синтаксис оператора UPDATE выглядит так:

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

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

Вконструкции SЕТ вместо явно заданных значений можно привести выражение.

Вданном случае операция LIKE используется для выборки строк, в которых значения в столбце title_id начинаются с подстроки ВU, за которой могут следовать любые символьные данные (на что указывает подстановочный символ %). В результате выполнения запроса выведутся значения поля title_id, начинающиеся с ВU и значения поля price.

Можно использовать также способ обновления, в котором в операторе UPDATE используется выражение:

После выполнения оператора UPDATE для каждого идентификатора title_id, который начинается с подстроки BU, значение в поле price увеличится на 10%, и будет иметь, например, следующий вид: 21.9890, т.е. четыре знака после запятой, а для цены нужно только два знака. Поэтому нужно что-то сделать для их округления.

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

3.6Оператор DELETE

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

WHERE:

Конструкция WHERE действует точно так же, как и все конструкции WHERE, рассматривавшиеся до сих пор. Список столбцов задавать не требуется, поскольку удаляется целая строка (например, невозможно удалить половину строки).

СУБД SQL Server не позволяет удалять строку если на нее имеется ссылка, на которую распространяется ограничение внешнего ключа. Если одна строка ссылается на другую строку (либо в той же, либо в другой таблице; это не имеет значения) с использованием внешнего ключа, то необходимо вначале удалить ссылающуюся строку и только затем удалить строку на которую была сделана ссылка. В последнем операторе INSERT в таблицу sales была вставлена строка со значением stor_id, равным ТЕSТ, а именно эта строка ссылается на строку, которую мы только что пытались удалить.

Поэтому прежде чем удалить указанную строку из таблицы stores, необходимо удалить строку таблицы sales, которая на нее ссылается:

Теперь можно повторно вызвать на выполнение оператор DELETE, который на сей раз будет выполнен успешно: