15822

Основы Transact SQL: Простые выборки данных

Лекция

Информатика, кибернетика и программирование

Основы Transact SQL: Простые выборки данных Создание вычисляемых полей Конструкция SELECT кроме имен столбцов таблиц может также включать так называемые вычисляемые поля. В отличие от всех выбранных нами ранее столбцов вычисляемых полей на самом деле в таблицах базы дан...

Русский

2013-06-18

199.5 KB

7 чел.

Основы Transact SQL: Простые выборки данных

Создание вычисляемых полей

Конструкция SELECT кроме имен столбцов таблиц может также включать так называемые вычисляемые поля. В отличие от всех выбранных нами ранее столбцов, вычисляемых полей на самом деле в таблицах базы данных нет. Они создаются "на лету" SQL-оператором SELECT. Рассмотрим следующий пример.

 

SELECT IdCust AS 'Номер клиента', FName + ' ' +LName AS 'Фамилия и имя клиента'

FROM Customer

 

Здесь создается вычисляемое поле, которому с помощью ключевого слова AS дан псевдоним ‘Фамилия и имя клиента’. Оно позволяет объединить (произвести конкатенацию) с помощью оператора + фамилию, пробел и имя клиента в одно поле (столбец). Псевдоним может быть задан и для обычного столбца таблицы. В частности здесь столбцу IdCust задан псевдоним ‘Номер клиента’.

 Еще одним способом использования вычисляемых полей является выполнение математических операций над выбранными данными. Рассмотрим пример.

 SELECT IdProd, Qty, Price, Qty * Price AS 'Стоимость'

FROM OrdItem

WHERE IdOrd = 1

 Здесь с помощью оператора умножения * вычисляется общая стоимость каждого товара в заказе с кодом 1 как произведение количества на цену.

 Исключение дублирующих записей

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

 SELECT DISTINCT LName

FROM Customer

 Задание для самостоятельной работы: Сформулируйте на языке SQL запросы на выборку следующих данных:

 Список всех заказов за определенный период времени (например, сентябрь 2010 года) отсортированный по дате заказа;

  •  Список всех товаров, названия которых включают слово ‘монитор’ с указанием их остатка на складе.

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

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

 Общий формат унарной агрегатной функции следующий:

 имя_функции([АLL | DISTINCT] выражение)

 где DISTINCT указывает, что функция должна рассматривать только различные значения аргумента, а ALL — все значения, включая повторяющиеся (этот вариант используется по умолчанию). Например, функция AVG с ключевым словом DISTINCT для строк столбца со значениями 1, 1, 1 и 3 вернет 2, а при наличии ключевого слова ALL вернет 1,5.

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

 Функция COUNT имеет два формата. В первом случае возвращается количество строк входной таблицы, во втором случае — количество значений аргумента во входной таблице:

 COUNT(*)

  •  COUNT([DISTINCT | ALL] выражение)

 Простейший способ использования этой функции — подсчет количества строк в таблице (всех или удовлетворяющих указанному условию). Для этого используется первый вариант синтаксиса.

 Запрос: Количество видов продукции, информация о которых имеется в базе данных.

 SELECT COUNT(*) AS 'Количество видов продукции'

FROM Product

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

 Запрос: Количество различных имен, содержащихся в таблице Customer.

 SELECT COUNT(DISTINCT FNAME)

FROM Customer

 Использование остальных унарных агрегатных функции аналогично COUNT за тем исключением, что для функций MIN и MAX использование ключевых слов DISTINCT и ALL не имеет смысла. С функциями COUNT, MAX и MIN кроме числовых могут использоваться и символьные поля. Если аргумент агрегатной функции не содержит значений, функция COUNT возвращает 0, а все остальные - значение NULL.

 Запрос: Дата последнего заказа до 1 сентября 2010 года.

 SELECT MAX(OrdDate)

FROM [Order]

WHERE OrdDate<'1.09.2010' 

 Задание для самостоятельной работы: Сформулируйте на языке SQL запросы на выборку следующих данных:

 Суммарная стоимость всех заказов;

  •  Количество различных городов, содержащихся в таблице Customer.

 Запросы с группировкой строк

 Описанные выше агрегатные функции применялись ко всей таблице. Однако часто при создании отчетов появляется необходимость в формировании промежуточных итоговых значений, то есть относящихся к данным не всей таблицы, а ее частей. Для этого предназначена фраза GROUP BY. Она позволяет все множество строк таблицы разделить на группы по признаку равенства значений одного или нескольких столбцов (и выражений над ними). Фраза GROUP BY должна располагаться вслед за фразой WHERE (если она отсутствует, то за фразой FROM).

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

  •  константой;
  •  агрегатной функцией, которая оперирует всеми значениями аргумента в пределах группы и агрегирует их в одно значение (например, в сумму);
  •  выражением, идентичным стоящему во фразе GROUP BY;
  •  выражением, объединяющим приведенные выше варианты.

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

 Запрос: Количество клиентов по городам.

 SELECT IdCity, COUNT(*) AS 'Кол-во клиентов'

FROM Customer

GROUP BY IdCity

 Если в запросе используются фразы и WHERE, и GROUP BY, строки, не удовлетворяющие условию фразы WHERE, исключаются до выполнения группировки. Вследствие этого группировка производится только по тем строкам, которые удовлетворяют условию.

 Запрос: Количество клиентов по городам с фамилией ‘Иванов’.

 SELECT IdCity, COUNT(*) AS 'Кол-во клиентов'

FROM Customer

WHERE LName = 'Иванов'

GROUP BY IdCity

 SQL позволяет группировать строки таблицы и по нескольким столбцам. В этом случае имена столбцов перечисляются во фразе GROUP BY через запятую.

 Запрос: Количество клиентов по каждой фамилии и имени.

 SELECT LName, FName, COUNT(*)

FROM Customer

GROUP BY LName, FName

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

 Фразой WHERE отбираются строки, удовлетворяющие указанному в ней условию;

  1.  Фраза GROUP BY группирует отобранные строки;
  2.  Фразой HAVING отбираются группы, удовлетворяющие указанному в ней условию.

 Значение условия, указываемого во фразе HAVING, должно быть уникальным для всех строк каждой группы. Поэтому правила использования имен столбцов и агрегатных функций во фразе HAVING такие же, как и для фразы SELECT при наличии фразы GROUP BY. Это значит, что во фразе HAVING в качестве операндов сравнения можно использовать только группируемые столбцы или агрегатные функции.

 Запрос: Список городов, количество клиентов из которых больше 10.

 SELECT IdCity

FROM Customer

GROUP BY IdCity

HAVING COUNT(*)>10

 Задание для самостоятельной работы: Сформулируйте на языке SQL запросы на выборку следующих данных:

  •  Список всех заказов с указанием их суммарной стоимости;
  •  Список клиентов, которые за заданный период (например, сентябрь 2010 года) совершили более 3 заказов.


 

А также другие работы, которые могут Вас заинтересовать

40235. Подходы к оценке инвестиционных проектов 21.5 KB
  Отличие между этими двумя классами состоит в учете фактора стоимости денег во времени. Сложные динамические показатели основаны на технике сложного процента и учитывают метод начисления а также фактор стоимости денег во времени.
40236. Финансовые инвестиции виды, определение 28.5 KB
  Финансовые инвестиции представляют собой финансовые вложения в ценные бумаги такие как акции облигации векселя депозиты банков и другие финансовые инструменты которые позволяют извлекать прибыль или доход от финансовых вложений. Основная цель финансовых инвестиций получение прибыли. Размер дохода может быть различным но нужно помнить что как правило чем выше доходность финансовых инвестиций тем выше финансовые риски. Одним из основных видов финансовых инвестиций являются вложения в ценные бумаги.
40237. Подходы к определению эффективности финансовых вложений 28 KB
  Подходы к определению эффективности финансовых вложений. Хачатуровым были выпущены различные отраслевые методики и инструкции по оценке эффективности капитальных вложений. Эффективность капитальных вложений оценивалась на основе коэффициента эффективности рассчитываемого как отношение среднегодовой суммы прибыли к объему капитальных вложений и срока окупаемости показателя обратного коэффициенту окупаемости. Использование для расчетов показателей базирующихся на одинаковых исходных данных объем прибыли и объем капитальных вложений вело...
40238. Аудит 36.5 KB
  Слово аудит происходит от латинского слова udio что значит слушатель или слушающий . По аналогии со специальными врачебными инструментами используемыми для определения физического здоровья пациента с помощью аудита устанавливается экономическое здоровье организаций банков корпораций и т. Аудиторская деятельность аудит представляет собой предпринимательскую деятельность аудиторов аудиторских фирм по осуществлению вневедомственных проверок бухгалтерской финансовой отчетности документов бухгалтерского учета налоговых деклараций и...
40239. Понятие и формирование учетной политики 33 KB
  Под учетной политикой организации понимается принятая ею совокупность способов введения бухучета первичного наблюдения стоимостного измерения текущей группировки и итогового обобщения фактов хоз. деятельности погашения стоимости активов организации документооборота инвентаризации способы применения счетов бухучета системы регистров бухучета обработки информации и иные соответствующие способы и приемы. вступил в действие первый вариант стандарта по бухгалтерскому учету Учетная политика организации ПБУ 1 95. № 60н и содержит...
40240. Понятие об экономической эффективности и проблемы ее повышения в сельском хозяйстве 36 KB
  Прибыль убыток полученные от применения удобрений эффективность руб. Экономическую эффективность сельскохозяйственного производства характеризуют валовой и чистый доход прибыль уровень рентабельности окупаемость затрат. Прибыль как экономическая категория характеризует финансовый результат предпринимательской деятельности предприятия. Различают валовую прибыль прибыль от реализации продукции и услуг чистую прибыль.
40241. Понятие ошибок и недобросовестных действий аудитора 32 KB
  Ошибка непреднамеренное искажение в финансовой бухгалтерской отчетности в том числе неотражение какоголибо числового показателя или нераскрытие какойлибо информации. В настоящем федеральном правиле стандарте аудиторской деятельности рассматриваются только недобросовестные действия являющиеся причиной существенных искажений финансовой бухгалтерской отчетности. Различают два типа преднамеренных искажений возникающих в результате недобросовестных действий рассматриваемых в ходе аудита: искажения возникающие в процессе...
40242. Понятие производительности труда, показатели, факторы и пути повышения 31.5 KB
  Производительность труда это способность конкретного труда работников производить определенное количество продукции или выполнять определенный объем работ в единицу рабочего времени. Чем больше производится продукции в единицу рабочего времени или чем меньше затрачивается времени на производство единицы продукции тем выше производительность труда. Уровень производительности труда трудоотдача рассчитывают делением количества соответствующего вида продукции сельского хозяйства в натуральном или денежном выражении или объема...
40243. Понятие собственного капитала организации‚ характеристика его элементов 29 KB
  Собственный капитал состоит из уставного капитала добавочного капитала резервного капитала нераспределенной прибыли целевого финансирования. В настоящее время для характеристики той части собственного капитала размер которой указывается в учредительных документах используют понятия Уставной капитал Складочный капитал Уставной фонд паевой фонд. Уставной капитал совокупность в денежном выражении вкладов долей акций по номинальной стоимости учредителей участников в имущество организации при её создании для обеспечения...