Программирование в Microsoft SQL Server 2000

Компоненты языка Transact-SQL

Разбить на страницы
Показывать лекцию целиком

Вы научитесь:

  • использовать арифметические действия в операторе SELECT;
  • использовать операции сравнения в фразе WHERE;
  • использовать логические операции в операторе SELECT;
  • использовать побитные операции в операторе SELECT;
  • использовать операции конкатенации в операторе SELECT;
  • использовать функции даты;
  • использовать математические функции;
  • использовать функции агрегирования;
  • использовать функции метаданных;
  • использовать функции безопасности;
  • использовать строковые функции;
  • использовать системные функции.
    •

    Команды Transact-SQL

    В основе языка Transact-SQL лежат команды: "стержневые" операторы, описывающие фундаментальные операции, которые может выполнить язык.

    Зарезервированные слова

    Зарезервированное слово – это одно из средств, используемых языком Transact-SQL. Если вы используете зарезервированное слово как идентификатор, например, в качестве имени столбца, вы должны окружить это имя специальными символами, называемыми ограничителями (delimiters). В Microsoft SQL Server ограничительными символами являются [ и ]. Например, если вы используете SELECT в качестве имени столбца, вы должны при ссылке на этот столбец в запросе указать [SELECT], чтобы SQL Server воспринял это как идентификатор. (По возможности старайтесь избегать использования зарезервированных слов в качестве идентификаторов.)

    То, что мы называем командой, в документации SQL Server Books Online обозначается как "зарезервированные ключевые слова" (reserved keywords). Этот термин не очень удачен, поскольку нет большого различия между "зарезервированные ключевые слова" и любым другим зарезервированным словом. По этой причине мы будем использовать термин команда (command), который означает определенный набор зарезервированных ключевых слов, которые представляют действия, выполняемые SQL Server.

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

    Изучать команды Transact-SQL будет намного проще, если разделить их на группы по типу выполняемых действий, например таких, как манипулирование данными в таблице или представлении, или управление операционным окружением SQL Server.

    Мы уже использовали команды Transact-SQL. Например, вводили их в панели редактирования Editor Pane окна Query (Запрос) анализатора запросов Query Analyzer, а также в панели SQL Pane конструктора запросов Query Designer в Enterprise Manager. Кроме того, мы использовали их косвенно применяя утилиты, которые выполняют команды Transact-SQL "за сценой". Конструктор таблиц Table Designer в Enterprise Manager, например, формирует операторы CREATE и ALTER, основываясь на заданных вами параметрах.

    Общение с SQL Server

    Большинство приложений баз данных используют традиционный язык программирования, такой как Microsoft Visual Basic, для создания интерактивного интерфейса с SQL Server. Используя средства интерфейса, предоставляемые языком, эти приложения представляют данные пользователям в удобной и "дружественной" форме. "За сценой" же они, тем не менее, используют команды Transact-SQL. Как Enterprise Manager, так и анализатор запросов Query Analyzer в SQL Server как раз и являются приложениями для работы с базами данных, которые выполняют эту задачу.

    Когда вы используете обычные языки программирования, язык сам определяет, как исполнить команды. Некоторые окружения, например такие, как Microsoft Access, предоставляют интерактивные программные инструменты, схожие с Enterprise Manager и Query Analyzer. Другие, такие как Visual Basic или Microsoft Visual C++, используют объектную модель типа ADO для взаимодействия с сервером.

    Команды манипулирования данными

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

    Команды DML представлены в таблице 24.1. Большинство из них вам хорошо знакомы из уроков частей 3 и 4. Командами, с которыми мы не сталкивались, являются BULK INSERT – позволяющая вставлять множество строк из файла данных, и USE – указывающая на базу данных, которая будет использоваться в SQL-сценарии.

    Команды DML
    Команда Функция
    INSERT Вставляет строки в таблицу или представление.
    UPDATE Изменяет строки в таблице или представлении.
    DELETE Удаляет строки из таблицы или представления.
    SELECT Извлекает строки из таблицы или представления.
    TRUNCATE TABLE Удаляет все строки из таблицы или представления.
    BULK INSERT Вставляет строки из файла данных в таблицу или представление.
    USE Выполняет соединение с базой данных.

    Команды определения данных

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

    Команды DDL.
    Команда Функция
    CREATE Определяет новый объект базы данных.
    ALTER Изменяет определение объекта базы данных.
    DROP Удаляет объект базы данных из базы данных.

    Команды администрирования базы данных

    Большинство команд Transact-SQL, поддерживающие администрирование базы данных, доступны интерактивно через средства Enterprise Manager. Собственно команды администрирования позволяют выполнить эти же задачи программно.

    Команды администрирования базы данных показаны в таблице 24.3. Команды GRANT, DENY и REVOKE управляют средствами ограничения доступа и защиты базы данных (безопасностью). Команды BACKUP, RESTORE и UPDATE STATISTICS дублируют функциональные возможности планировщика обслуживания в Enterprise Manager.

    Команда SET используется совместно с ключевыми словами, например такими, как DATEFORMAT и LANGUAGE, для управления текущим сеансом SQL Server. В Enterprise Manager большинство из этих переменных доступны из диалогового окна свойств базы данных.

    Последние две команды администрирования базы данных, KILL и SHUTDOWN, используются для управления работой SQL Server. Команда KILL заканчивает выполнение операций, ассоциированных с соединением с определенным пользователем. Команда SHUTDOWN безусловно завершает работу SQL Server.

    Команды администрирования базы данных.
    Команда Функция
    GRANT Устанавливает определенные разрешения для объекта безопасности.
    DENY Отключает определенные разрешения для объекта безопасности, и предотвращает наследование объектом разрешений через его членство в роли или группе.
    REVOKE Удаляет определенное разрешение для объекта безопасности.
    BACKUP Создает резервную копию базы данных или журнала трансакций.
    RESTORE Восстанавливает данные после резервирования.
    UPDATE STATISTICS Обновляет статистику, используемую обработчиком запросов.
    SET Управляет окружением SQL Server.
    KILL Завершает соединение и все связанные с ним процессы.
    SHUTDOWN Отключает SQL Server.

    Другие команды

    Остались нерассмотренными еще три набора команд Transact-SQL. Первый набор команд управляет использованием программных переменных. Мы рассмотрим эти команды в уроке 25.

    Набор команд управления потоком контролирует выполнением операторов в SQL-сценарии. Команды управления потоком мы рассмотрим в уроке 26. Набор команд для работы с курсорами управляет поведением объекта специального типа – курсора, который указывает на определенную запись в таблице или представлении. Курсоры мы рассмотрим в уроке 27.

    Операции Transact-SQL

    Операцией (operator) мы будем называть символ, обозначающий действие, которое будет выполнено программой SQL Server. В уроке 12 уроке мы использовали операцию конкатенации + для создания вычисляемого столбца в операторе SELECT.

    Операции Transact-SQL классифицируются по количеству значений, которыми они могут оперировать. Это свойство называется кардинальным числом (cardinality) операции. Операции Transact-SQL по их кардинальному числу различаются на унарные и бинарные.

    Большинство операций являются бинарными. Операция называется бинарной, если она оперирует с двумя значениями. Операция + в выражении 4+3, и операция < в выражении MonthSales < MonthBudget являются примерами бинарных операций. Операция является унарной, если она оперирует только с одним значением. В выражении -10 операция (-) является унарным.

    Приоритет операций

    Когда вы создаете составной оператор Transact-SQL, важно представлять себе порядок, в котором должны выполняться операции – их приоритет (precedence). Определение приоритета часто не представляет проблемы, но иногда незнание приоритета может ввести вас в заблуждение при работе с операциями. Например, 3*(4+1) равно 15, в то время как 3*4+1 равно 13, поскольку операция умножения выполняется первой. Операция умножения имеет наивысший приоритет.

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

  • + (положительное число), - (отрицательное число), и ~ (побитная инверсия NOT)
  • *, /, %
  • + (сложения), + (конкатенации), - (вычитания)
  • = (сравнения), >, <, >=, <=, <>
  • ^, , |
  • NOT
  • AND
  • OR
  • = (присваивания)
    •

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

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

    Операторы комментариев

    Transact-SQL поддерживает два специальных оператора, которые не используются для операций вычисления, а предписывают SQL Server игнорировать определенный текст в сценарии. Transact-SQL поддерживает два оператора комментариев. Двойное тире (--) предписывает SQL Server игнорировать всю строку после этого символа. Этот оператор может использоваться в начале строки, в результате чего SQL Server будет игнорировать всю строку, или же он может использоваться внутри строки, в результате чего SQL Server будет игнорировать все, что находится после двойного тире до конца строки.

    Другим оператором комментариев являются два оператора, /* и */, которые используются вместе. SQL Server будет игнорировать все, что находится между первым оператором комментария /* и вторым оператором комментария */, причем не важно сколько строк расположено между ними.

    На рис. 24.1 показано использование оператора комментариев.

    (рис 24.1) Transact-SQL поддерживает два оператора комментариев.

    Совет. Operator /* и */ полезны при временном отключении operators Transact-SQL во время отладки.

    Арифметические операции

    Transact-SQL предоставляет операции для выполнения основных арифметических действий. Соответствующие операторы показаны в таблице 24.4. Эти операторы в точности выполняют то, что они обозначают. Только один оператор может оказаться для вас незнакомым, это арифметический модуль (modulo), который возвращает целую часть (целое число) остатка от деления. Например, результатом выражения 16 % 3 будет 1, а не 5 1/3.

    Арифметические операторы.
    Оператор Назначение
    + Сложение.
    - Вычитание.
    * Умножение.
    / Деление.
    % Остаток от деления.
    + Положительное число.
    - Отрицательное число.

    Используйте арифметические операции в операторе SELECT

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • В корневой директории, в папке SQL 2000 Step by Step выберите файл Arithmetic и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query(Запрос).
    •

    Операции сравнения

    В уроке 13 мы уже рассматривали использование операций сравнения при конструировании фразы WHERE. Соответствующие операторы отображены в таблице 24.5. Операторы сравнения возвращает булевые значения "истина" (TRUE) или "ложь" (FALSE).

    Операторы сравнения.
    Оператор Значение
    = Равно
    > Больше
    < Меньше
    >= Больше или равно
    <= Меньше или равно
    <> Не равно

    Используйте операции сравнения в фразе CLAUSE

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла сценария).
  • Выберите файл с именем Comparison и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Зарос).
  • Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Зарос).
    •

    Логические операции

    Как и операции сравнения, логические операции возвращают булевые значения "истина" ( TRUE ) или "ложь" ( FALSE ), но их использование ограничивается сравнением булевых значений. В таблице 24.6 представлены три логических оператора, поддерживаемых SQL Server.

    Логические операторы.
    Оператор Значение
    AND TRUE, если оба значения есть TRUE.
    NOT Инвертирует значения булевого оператора.
    OR TRUE, если хотя бы один из операторов есть TRUE.

    Используйте логические операции в операторе SELECT

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла сценария).
  • Выберите файл с именем Logical и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Побитные операции

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

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

    Оператор ^ не имеет соответствующего логического эквивалента. В булевой алгебре, булевое OR (ИЛИ) возвращает TRUE (истина), если хотя бы одно или оба значения есть TRUE (истина). Однако булевое исключающее OR (ИЛИ) возвращает TRUE (истина), если одно, но не оба сравниваемых значения есть TRUE (истина). То же самое делает и оператор ^, который возвращает TRUE (истина), только если одно, но не оба сравниваемых бита есть TRUE (истина).

    Побитные операторы.
    Оператор Значение
    Побитное AND (И).
    | Побитное OR (ИЛИ).
    ^ Побитное исключающее OR (ИЛИ).
    ~ Побитное NOT.

    Битовое представление

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

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

    Используйте побитные операции в операторе SELECT

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выберите файл с именем Bitwise и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Другие операции

    Transact-SQL предоставляет еще две полезных операции, которые описаны в таблице 24.8. В уроке 12 мы уже использовали операцию конкатенации строк +. Операция конкатенации прибавляет содержимое одной строки к другой.

    Операция присвоения =, присваивает значение, стоящее справа от, оператора значению, стоящему слева от оператора. Учтите, что здесь порядок отличается от того, который вы изучали в школе: не "a+b=c", а "c=a+b". Мы будем использовать операцию присвоения далее в этом уроке при изучении переменных.

    Другие операторы
    Оператор Значение
    + Конкатенация строк.
    = Присвоение.

    Используйте операцию конкатенацию в операторе SELECT

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла сценария).
  • Выберите файл с именем Concatenation и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Функции Transact-SQL

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

    Нововведением в SQL Server 2000 является возможность создания своих собственных функций, которые называются пользовательскими функциями (user-defined). Их мы рассмотрим в уроке 30.

    Transact-SQL также предоставляет несколько встроенных функций, которые мы и будем рассматривать.

    Примечание. В таблицах в этом разделе представлены не все функции. Полный перечень функций доступен в панели Object Browser в папке Common Objects.

    Использование функций

    Встроенные функции Transact-SQL классифицируются по характеру возвращаемого ими результата: они могут быть либо детерминированными (deterministic), либо недетерминированными (non-deterministic). Детерминированная функция, получая одни и те же значения данных, которыми будет оперировать, всегда будет возвращать одинаковый результат: SQRT(9) всегда возвращает 3, следовательно, функция SQRT детерминированная. Недетерминированная функция, такая как RAND, наоборот, при каждом обращении всегда возвращает различные значения.

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

    Функции даты и времени

    Функции даты и времени принимают в качестве входных значений дату и время и возвращают либо строковые, числовые значения, либо значения в формате даты и времени. (Помните, что в SQL Server, время считается компонентом типа данных datetime). Параметр единицы, фигурирующий во многих функциях, обычно обозначает единицы измерения времени, например такие, как "год" или "минута". В таблице 24.9 представлены функции даты и времени Transact-SQL.

    Функции даты и времени.
    Функция Параметры Операция
    DATEADD единицы, число, дата Рассчитывает новую дату, добавляя к существующей указанное число единиц (дней, месяцев, часов и т.д.).
    DATEDIFF единицы, нач_дата, кон_дата Возвращает количество единиц времени, между двумя указанными датами.
    DATENAME единицы, дата Возвращает имя указанной единицы времени даты в виде строки.
    DATEPART единицы, дата Возвращает имя указанной единицы времени даты в виде числа.
    DAY дата Возвращает день для указанной даты в виде числа.
    GETDATE Возвращает текущее системное время и дату.
    MONTH дата Возвращает месяц для указанной даты в виде числа.
    YEAR дата Возвращает год для указанной даты в виде числа.

    Используйте функции даты

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла сценария).
  • Выберите файл с именем DateTime и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Математические функции

    Математические функции, представленные в таблице 24.10, выполняют числовые вычисления.

    Математические функции
    Функция Параметры Операция
    ABS numeric_expression Возвращает абсолютное значение выражения numeric_expression.
    ACOS float_expression Возвращает арккосинус выражения float_expression.
    ASIN float_expression Возвращает арксинус выражения float_expression.
    ATAN float_expression Возвращает арктангенс выражения float_expression.
    ATN2 float_expression, float_expression Возвращает угол в радианах, тангенс которого находится между двумя значениями float_expression.
    CEILING numeric_expression Возвращает ближайшее число, большее или равное выражению numeric_expression.
    COS float_expression Возвращает тригонометрический косинус выражения float_expression.
    COT float_expression Возвращает тригонометрический котангенс выражения float_expression.
    DEGREES numeric_expression Данный угол numeric_expression в радианах возвращает в градусах.
    EXP float_expression Возвращает экспоненциальное значение выражения float_expression.
    FLOOR numeric_expression Возвращает ближайшее число, меньшее или равное выражению numeric_expression.
    LOG float_expression Возвращает натуральный логарифм выражения float_expression.
    LOG10 float_expression Возвращает десятичный логарифм выражения float_expression.
    PI Возвращает значение константы pi.
    POWER numeric_expression, y Возвращает значение выражения numeric_expression, возведенное в степень y.
    RADIANS numeric_expression Данный угол numeric_expression в градусах возвращает угол в радианах.
    RAND [seed] Возвращает случайное значение в интервале от 0 до 1.
    ROUND numeric_expression, lenght Возвращает округленное с указанной точностью значение выражения numeric_expression.
    SIGN float_expression Возвращает +1, если numeric_expression положительно, 0 если numeric_expression ноль, и -1 если numeric_expression отрицательно.
    SIN float_expression Возвращает тригонометрический синус даваемого в радианах угла float_expression.
    SQUARE float_expression Возвращает квадрат float_expression.
    SQRT float_expression Возвращает квадратный корень из float_expression.
    TAN float_expression Возвращает тангенс выражения float_expression.

    Используйте математические функции

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открыть файл запроса).
  • Выберите файл с именем Mathematical и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Функции агрегирования

    Функции агрегирования, представленные в таблице 24.11, принимают в качестве параметра коллекцию значений и возвращают одиночное значение.

    Функции совокупности
    Функция Операция
    AVG Возвращает среднее значение из коллекции, игнорируя нулевые (NULL) значения.
    COUNT Возвращает количество значений в коллекции, включая и нулевые.
    MAX Возвращает наибольшее значение из коллекции.
    MIN Возвращает наименьшее значение из коллекции.
    SUM Возвращает сумму значений из коллекции, игнорируя нулевые значения.
    STDEV Возвращает стандартное статистическое отклонение для каждого из значений в коллекции.
    STDEVP Возвращает стандартное статистическое отклонение все совокупности значений в коллекции.
    VAR Возвращает статистическую вариацию значений в группе.
    VARP Возвращает статистическую вариацию всех значений в коллекции.

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

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выберите файл с именем Aggregate и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Функции метаданных

    Функции метаданных возвращают информацию о данных, а не сами данные. Имеется очень большое количество функций метаданных. В таблице 24.12 представлен перечень наиболее часто используемых функций метаданных.

    Функции метаданных
    Функция Параметры Операция
    COL_LENGTH table, column Возвращает количество байт столбца column.
    COL_NAME tableID, columnId Возвращает имя columnID.
    COLUMNPROPERTY ID, column, property Возвращает информацию о свойстве property столбца column.
    DATABASEPROPERTY database, property Возвращает значение свойства property.
    DB_ID database_name Возвращает идентификационный номер базы данных Database_name.
    DB_NAME databaseID Возвращает имя базы данных по идентификатору databaseID.
    INDEX_COL table, indexID, keyed Возвращает имя индексированного столбца по идентификаторам indexID и keyID.
    INDEXPROPERTY tableID, index, property Возвращает информацию о свойстве property индекса index.
    OBJECT_ID object Возвращает идентификационный номер объекта object базы данных.
    OBJECT_NAME objectID Возвращает имя объекта по его идентификационному номеру objectID.
    OBJECTPROPERTY ID, property Возвращает информацию о свойстве property объекта по его идентификационному номеру ID.
    SQL_VARIANT_PROPERTY SQL_variant, property Возвращает указанное свойство property варианта Sql_variant.
    TYPEPROPERTY datatype, property Возвращает информацию о свойстве property для типа данных datatype.

    Используйте функции метаданных

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выберите файл с именем Metadata и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Функции безопасности

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

    Функции безопасности
    Функция Параметры Операция
    HAS_DBACCESS database_name Показывает, имеет ли текущий пользователь доступ к базе данных database_name.
    IS_MEMBER group_or_role Показывает, имеет ли текущий пользователь членство в группе или роли group_or_role.
    IS_SRVROLEMEMBER role [, login] Показывает, имеет ли текущая или указанная учетная запись login членство в роли role.
    SUSER_SID [login] Для текущей или указанной учетной записи login возвращает идентификационный номер безопасности (SID).
    SUSER_SNAME [] Возвращает имя учетной записи по ее идентификационному номеру безопасности SID.
    USER_ID [user] Возвращает идентификационный номер текущего или указанного пользователя user.
    USER Возвращает имя текущего пользователя базы данных.

    Используйте функции безопасности

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer кнопку нажмите New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выберите файл с именем Security и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Строковые функции

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

    Строковые функции
    Функция Параметры Операция
    ASCII char_expression Возвращает ASCII-код самого левого символа в строке char_expression.
    CHAR integer_expression Возвращает ASCII-символ, код которого равен integer_expression.
    CHARINDEX char_expression, char_expression [, start_position] Возвращает позицию первого выражения char_expression во втором выражении char_expression.
    LEFT char_expression, integer_expression Возвращает крайние слева символы integer_expression в выражении char_expression.
    LEN char_expression Возвращает количество символов в выражении char_expression.
    LOWER char_expression Возвращает выражение char_expression, в котором все символы приведены к нижнему регистру.
    LTRIM char_expression Возвращает выражение char_expression с удаленными начальными пробелами.
    NCHAR integer_expression Возвращает символ UNICODE, код которого задает integer_expression.
    REPLACE char_expression, char_expression, char_expression Находит все вхождения второй строки char_expression в первую char_expression и заменяет их на третью char_expression.
    RIGHT char_expression, integer_expression Возвращает крайние справа символы integer_expression в строке char_expression.
    RTRIM char_expression Возвращает строку char_expression с удаленными конечными пробелами.
    SOUNDEX char_expression Возвращает четырехзначный код SOUNDEX для char_expression.
    SPACE integer_expression Возвращает число integer_expression пробелов.
    SUBSTRING char_expression start, lenght Возвращает подстроку char_expression указанной длины lenght, начиная с символа start.
    UNICODE unicode_expression Возвращает значение UNICODE для первого символа в unicode_expression.
    UPPER char_expression Возвращает выражение char_expression, в котором все символы приведены к верхнему регистру.

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

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открыие файла запроса).
  • Выберите файл с именем String и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Системные функции

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

    Системные функции
    Функция Параметры Операции
    APP_NAME Возвращает имя приложения.
    DATALENGHT выражение Возвращает количество байт, используемых для хранения выражения.
    ISDATE выражение Определяет, корректно ли данное выражение.
    ISNULL выражение Определяет, является ли данное выражение нулем.
    ISNUMERIC выражение Определяет, является ли данное выражение числом.
    NEWID Создает новый уникальный идентификационный номер uniqueidentifier.
    NULLIF выражение, выражение Возвращает NULL, если первое и второе выражение одинаковы.
    PARSENAME object_name, name_part Возвращает часть имени name_part объекта object_name.
    SYSTEM_USER Возвращает текущее имя пользователя системы.
    USER_NAME [id] Возвращает имя текущего пользователя или пользователя по указанному id.

    Используйте системные функции

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить запрос).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выберите файл с именем System и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •
    Страницы:

    Вы научитесь:

  • использовать арифметические действия в операторе SELECT;
  • использовать операции сравнения в фразе WHERE;
  • использовать логические операции в операторе SELECT;
  • использовать побитные операции в операторе SELECT;
  • использовать операции конкатенации в операторе SELECT;
  • использовать функции даты;
  • использовать математические функции;
  • использовать функции агрегирования;
  • использовать функции метаданных;
  • использовать функции безопасности;
  • использовать строковые функции;
  • использовать системные функции.
    •

    Команды Transact-SQL

    В основе языка Transact-SQL лежат команды: "стержневые" операторы, описывающие фундаментальные операции, которые может выполнить язык.

    Зарезервированные слова

    Зарезервированное слово – это одно из средств, используемых языком Transact-SQL. Если вы используете зарезервированное слово как идентификатор, например, в качестве имени столбца, вы должны окружить это имя специальными символами, называемыми ограничителями (delimiters). В Microsoft SQL Server ограничительными символами являются [ и ]. Например, если вы используете SELECT в качестве имени столбца, вы должны при ссылке на этот столбец в запросе указать [SELECT], чтобы SQL Server воспринял это как идентификатор. (По возможности старайтесь избегать использования зарезервированных слов в качестве идентификаторов.)

    То, что мы называем командой, в документации SQL Server Books Online обозначается как "зарезервированные ключевые слова" (reserved keywords). Этот термин не очень удачен, поскольку нет большого различия между "зарезервированные ключевые слова" и любым другим зарезервированным словом. По этой причине мы будем использовать термин команда (command), который означает определенный набор зарезервированных ключевых слов, которые представляют действия, выполняемые SQL Server.

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

    Изучать команды Transact-SQL будет намного проще, если разделить их на группы по типу выполняемых действий, например таких, как манипулирование данными в таблице или представлении, или управление операционным окружением SQL Server.

    Мы уже использовали команды Transact-SQL. Например, вводили их в панели редактирования Editor Pane окна Query (Запрос) анализатора запросов Query Analyzer, а также в панели SQL Pane конструктора запросов Query Designer в Enterprise Manager. Кроме того, мы использовали их косвенно применяя утилиты, которые выполняют команды Transact-SQL "за сценой". Конструктор таблиц Table Designer в Enterprise Manager, например, формирует операторы CREATE и ALTER, основываясь на заданных вами параметрах.

    Общение с SQL Server

    Большинство приложений баз данных используют традиционный язык программирования, такой как Microsoft Visual Basic, для создания интерактивного интерфейса с SQL Server. Используя средства интерфейса, предоставляемые языком, эти приложения представляют данные пользователям в удобной и "дружественной" форме. "За сценой" же они, тем не менее, используют команды Transact-SQL. Как Enterprise Manager, так и анализатор запросов Query Analyzer в SQL Server как раз и являются приложениями для работы с базами данных, которые выполняют эту задачу.

    Когда вы используете обычные языки программирования, язык сам определяет, как исполнить команды. Некоторые окружения, например такие, как Microsoft Access, предоставляют интерактивные программные инструменты, схожие с Enterprise Manager и Query Analyzer. Другие, такие как Visual Basic или Microsoft Visual C++, используют объектную модель типа ADO для взаимодействия с сервером.

    Команды манипулирования данными

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

    Команды DML представлены в таблице 24.1. Большинство из них вам хорошо знакомы из уроков частей 3 и 4. Командами, с которыми мы не сталкивались, являются BULK INSERT – позволяющая вставлять множество строк из файла данных, и USE – указывающая на базу данных, которая будет использоваться в SQL-сценарии.

    Команды DML
    Команда Функция
    INSERT Вставляет строки в таблицу или представление.
    UPDATE Изменяет строки в таблице или представлении.
    DELETE Удаляет строки из таблицы или представления.
    SELECT Извлекает строки из таблицы или представления.
    TRUNCATE TABLE Удаляет все строки из таблицы или представления.
    BULK INSERT Вставляет строки из файла данных в таблицу или представление.
    USE Выполняет соединение с базой данных.

    Команды определения данных

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

    Команды DDL.
    Команда Функция
    CREATE Определяет новый объект базы данных.
    ALTER Изменяет определение объекта базы данных.
    DROP Удаляет объект базы данных из базы данных.

    Команды администрирования базы данных

    Большинство команд Transact-SQL, поддерживающие администрирование базы данных, доступны интерактивно через средства Enterprise Manager. Собственно команды администрирования позволяют выполнить эти же задачи программно.

    Команды администрирования базы данных показаны в таблице 24.3. Команды GRANT, DENY и REVOKE управляют средствами ограничения доступа и защиты базы данных (безопасностью). Команды BACKUP, RESTORE и UPDATE STATISTICS дублируют функциональные возможности планировщика обслуживания в Enterprise Manager.

    Команда SET используется совместно с ключевыми словами, например такими, как DATEFORMAT и LANGUAGE, для управления текущим сеансом SQL Server. В Enterprise Manager большинство из этих переменных доступны из диалогового окна свойств базы данных.

    Последние две команды администрирования базы данных, KILL и SHUTDOWN, используются для управления работой SQL Server. Команда KILL заканчивает выполнение операций, ассоциированных с соединением с определенным пользователем. Команда SHUTDOWN безусловно завершает работу SQL Server.

    Команды администрирования базы данных.
    Команда Функция
    GRANT Устанавливает определенные разрешения для объекта безопасности.
    DENY Отключает определенные разрешения для объекта безопасности, и предотвращает наследование объектом разрешений через его членство в роли или группе.
    REVOKE Удаляет определенное разрешение для объекта безопасности.
    BACKUP Создает резервную копию базы данных или журнала трансакций.
    RESTORE Восстанавливает данные после резервирования.
    UPDATE STATISTICS Обновляет статистику, используемую обработчиком запросов.
    SET Управляет окружением SQL Server.
    KILL Завершает соединение и все связанные с ним процессы.
    SHUTDOWN Отключает SQL Server.

    Другие команды

    Остались нерассмотренными еще три набора команд Transact-SQL. Первый набор команд управляет использованием программных переменных. Мы рассмотрим эти команды в уроке 25.

    Набор команд управления потоком контролирует выполнением операторов в SQL-сценарии. Команды управления потоком мы рассмотрим в уроке 26. Набор команд для работы с курсорами управляет поведением объекта специального типа – курсора, который указывает на определенную запись в таблице или представлении. Курсоры мы рассмотрим в уроке 27.

    Операции Transact-SQL

    Операцией (operator) мы будем называть символ, обозначающий действие, которое будет выполнено программой SQL Server. В уроке 12 уроке мы использовали операцию конкатенации + для создания вычисляемого столбца в операторе SELECT.

    Операции Transact-SQL классифицируются по количеству значений, которыми они могут оперировать. Это свойство называется кардинальным числом (cardinality) операции. Операции Transact-SQL по их кардинальному числу различаются на унарные и бинарные.

    Большинство операций являются бинарными. Операция называется бинарной, если она оперирует с двумя значениями. Операция + в выражении 4+3, и операция < в выражении MonthSales < MonthBudget являются примерами бинарных операций. Операция является унарной, если она оперирует только с одним значением. В выражении -10 операция (-) является унарным.

    Приоритет операций

    Когда вы создаете составной оператор Transact-SQL, важно представлять себе порядок, в котором должны выполняться операции – их приоритет (precedence). Определение приоритета часто не представляет проблемы, но иногда незнание приоритета может ввести вас в заблуждение при работе с операциями. Например, 3*(4+1) равно 15, в то время как 3*4+1 равно 13, поскольку операция умножения выполняется первой. Операция умножения имеет наивысший приоритет.

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

  • + (положительное число), - (отрицательное число), и ~ (побитная инверсия NOT)
  • *, /, %
  • + (сложения), + (конкатенации), - (вычитания)
  • = (сравнения), >, <, >=, <=, <>
  • ^, , |
  • NOT
  • AND
  • OR
  • = (присваивания)
    •

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

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

    Операторы комментариев

    Transact-SQL поддерживает два специальных оператора, которые не используются для операций вычисления, а предписывают SQL Server игнорировать определенный текст в сценарии. Transact-SQL поддерживает два оператора комментариев. Двойное тире (--) предписывает SQL Server игнорировать всю строку после этого символа. Этот оператор может использоваться в начале строки, в результате чего SQL Server будет игнорировать всю строку, или же он может использоваться внутри строки, в результате чего SQL Server будет игнорировать все, что находится после двойного тире до конца строки.

    Другим оператором комментариев являются два оператора, /* и */, которые используются вместе. SQL Server будет игнорировать все, что находится между первым оператором комментария /* и вторым оператором комментария */, причем не важно сколько строк расположено между ними.

    На рис. 24.1 показано использование оператора комментариев.

    (рис 24.1) Transact-SQL поддерживает два оператора комментариев.

    Совет. Operator /* и */ полезны при временном отключении operators Transact-SQL во время отладки.

    Арифметические операции

    Transact-SQL предоставляет операции для выполнения основных арифметических действий. Соответствующие операторы показаны в таблице 24.4. Эти операторы в точности выполняют то, что они обозначают. Только один оператор может оказаться для вас незнакомым, это арифметический модуль (modulo), который возвращает целую часть (целое число) остатка от деления. Например, результатом выражения 16 % 3 будет 1, а не 5 1/3.

    Арифметические операторы.
    Оператор Назначение
    + Сложение.
    - Вычитание.
    * Умножение.
    / Деление.
    % Остаток от деления.
    + Положительное число.
    - Отрицательное число.

    Используйте арифметические операции в операторе SELECT

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • В корневой директории, в папке SQL 2000 Step by Step выберите файл Arithmetic и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query(Запрос).
    •

    Операции сравнения

    В уроке 13 мы уже рассматривали использование операций сравнения при конструировании фразы WHERE. Соответствующие операторы отображены в таблице 24.5. Операторы сравнения возвращает булевые значения "истина" (TRUE) или "ложь" (FALSE).

    Операторы сравнения.
    Оператор Значение
    = Равно
    > Больше
    < Меньше
    >= Больше или равно
    <= Меньше или равно
    <> Не равно

    Используйте операции сравнения в фразе CLAUSE

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла сценария).
  • Выберите файл с именем Comparison и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Зарос).
  • Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Зарос).
    •

    Логические операции

    Как и операции сравнения, логические операции возвращают булевые значения "истина" ( TRUE ) или "ложь" ( FALSE ), но их использование ограничивается сравнением булевых значений. В таблице 24.6 представлены три логических оператора, поддерживаемых SQL Server.

    Логические операторы.
    Оператор Значение
    AND TRUE, если оба значения есть TRUE.
    NOT Инвертирует значения булевого оператора.
    OR TRUE, если хотя бы один из операторов есть TRUE.

    Используйте логические операции в операторе SELECT

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла сценария).
  • Выберите файл с именем Logical и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Побитные операции

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

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

    Оператор ^ не имеет соответствующего логического эквивалента. В булевой алгебре, булевое OR (ИЛИ) возвращает TRUE (истина), если хотя бы одно или оба значения есть TRUE (истина). Однако булевое исключающее OR (ИЛИ) возвращает TRUE (истина), если одно, но не оба сравниваемых значения есть TRUE (истина). То же самое делает и оператор ^, который возвращает TRUE (истина), только если одно, но не оба сравниваемых бита есть TRUE (истина).

    Побитные операторы.
    Оператор Значение
    Побитное AND (И).
    | Побитное OR (ИЛИ).
    ^ Побитное исключающее OR (ИЛИ).
    ~ Побитное NOT.

    Битовое представление

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

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

    Используйте побитные операции в операторе SELECT

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выберите файл с именем Bitwise и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Другие операции

    Transact-SQL предоставляет еще две полезных операции, которые описаны в таблице 24.8. В уроке 12 мы уже использовали операцию конкатенации строк +. Операция конкатенации прибавляет содержимое одной строки к другой.

    Операция присвоения =, присваивает значение, стоящее справа от, оператора значению, стоящему слева от оператора. Учтите, что здесь порядок отличается от того, который вы изучали в школе: не "a+b=c", а "c=a+b". Мы будем использовать операцию присвоения далее в этом уроке при изучении переменных.

    Другие операторы
    Оператор Значение
    + Конкатенация строк.
    = Присвоение.

    Используйте операцию конкатенацию в операторе SELECT

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла сценария).
  • Выберите файл с именем Concatenation и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Функции Transact-SQL

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

    Нововведением в SQL Server 2000 является возможность создания своих собственных функций, которые называются пользовательскими функциями (user-defined). Их мы рассмотрим в уроке 30.

    Transact-SQL также предоставляет несколько встроенных функций, которые мы и будем рассматривать.

    Примечание. В таблицах в этом разделе представлены не все функции. Полный перечень функций доступен в панели Object Browser в папке Common Objects.

    Использование функций

    Встроенные функции Transact-SQL классифицируются по характеру возвращаемого ими результата: они могут быть либо детерминированными (deterministic), либо недетерминированными (non-deterministic). Детерминированная функция, получая одни и те же значения данных, которыми будет оперировать, всегда будет возвращать одинаковый результат: SQRT(9) всегда возвращает 3, следовательно, функция SQRT детерминированная. Недетерминированная функция, такая как RAND, наоборот, при каждом обращении всегда возвращает различные значения.

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

    Функции даты и времени

    Функции даты и времени принимают в качестве входных значений дату и время и возвращают либо строковые, числовые значения, либо значения в формате даты и времени. (Помните, что в SQL Server, время считается компонентом типа данных datetime). Параметр единицы, фигурирующий во многих функциях, обычно обозначает единицы измерения времени, например такие, как "год" или "минута". В таблице 24.9 представлены функции даты и времени Transact-SQL.

    Функции даты и времени.
    Функция Параметры Операция
    DATEADD единицы, число, дата Рассчитывает новую дату, добавляя к существующей указанное число единиц (дней, месяцев, часов и т.д.).
    DATEDIFF единицы, нач_дата, кон_дата Возвращает количество единиц времени, между двумя указанными датами.
    DATENAME единицы, дата Возвращает имя указанной единицы времени даты в виде строки.
    DATEPART единицы, дата Возвращает имя указанной единицы времени даты в виде числа.
    DAY дата Возвращает день для указанной даты в виде числа.
    GETDATE Возвращает текущее системное время и дату.
    MONTH дата Возвращает месяц для указанной даты в виде числа.
    YEAR дата Возвращает год для указанной даты в виде числа.

    Используйте функции даты

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла сценария).
  • Выберите файл с именем DateTime и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Математические функции

    Математические функции, представленные в таблице 24.10, выполняют числовые вычисления.

    Математические функции
    Функция Параметры Операция
    ABS numeric_expression Возвращает абсолютное значение выражения numeric_expression.
    ACOS float_expression Возвращает арккосинус выражения float_expression.
    ASIN float_expression Возвращает арксинус выражения float_expression.
    ATAN float_expression Возвращает арктангенс выражения float_expression.
    ATN2 float_expression, float_expression Возвращает угол в радианах, тангенс которого находится между двумя значениями float_expression.
    CEILING numeric_expression Возвращает ближайшее число, большее или равное выражению numeric_expression.
    COS float_expression Возвращает тригонометрический косинус выражения float_expression.
    COT float_expression Возвращает тригонометрический котангенс выражения float_expression.
    DEGREES numeric_expression Данный угол numeric_expression в радианах возвращает в градусах.
    EXP float_expression Возвращает экспоненциальное значение выражения float_expression.
    FLOOR numeric_expression Возвращает ближайшее число, меньшее или равное выражению numeric_expression.
    LOG float_expression Возвращает натуральный логарифм выражения float_expression.
    LOG10 float_expression Возвращает десятичный логарифм выражения float_expression.
    PI Возвращает значение константы pi.
    POWER numeric_expression, y Возвращает значение выражения numeric_expression, возведенное в степень y.
    RADIANS numeric_expression Данный угол numeric_expression в градусах возвращает угол в радианах.
    RAND [seed] Возвращает случайное значение в интервале от 0 до 1.
    ROUND numeric_expression, lenght Возвращает округленное с указанной точностью значение выражения numeric_expression.
    SIGN float_expression Возвращает +1, если numeric_expression положительно, 0 если numeric_expression ноль, и -1 если numeric_expression отрицательно.
    SIN float_expression Возвращает тригонометрический синус даваемого в радианах угла float_expression.
    SQUARE float_expression Возвращает квадрат float_expression.
    SQRT float_expression Возвращает квадратный корень из float_expression.
    TAN float_expression Возвращает тангенс выражения float_expression.

    Используйте математические функции

  • Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открыть файл запроса).
  • Выберите файл с именем Mathematical и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Функции агрегирования

    Функции агрегирования, представленные в таблице 24.11, принимают в качестве параметра коллекцию значений и возвращают одиночное значение.

    Функции совокупности
    Функция Операция
    AVG Возвращает среднее значение из коллекции, игнорируя нулевые (NULL) значения.
    COUNT Возвращает количество значений в коллекции, включая и нулевые.
    MAX Возвращает наибольшее значение из коллекции.
    MIN Возвращает наименьшее значение из коллекции.
    SUM Возвращает сумму значений из коллекции, игнорируя нулевые значения.
    STDEV Возвращает стандартное статистическое отклонение для каждого из значений в коллекции.
    STDEVP Возвращает стандартное статистическое отклонение все совокупности значений в коллекции.
    VAR Возвращает статистическую вариацию значений в группе.
    VARP Возвращает статистическую вариацию всех значений в коллекции.

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

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выберите файл с именем Aggregate и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Функции метаданных

    Функции метаданных возвращают информацию о данных, а не сами данные. Имеется очень большое количество функций метаданных. В таблице 24.12 представлен перечень наиболее часто используемых функций метаданных.

    Функции метаданных
    Функция Параметры Операция
    COL_LENGTH table, column Возвращает количество байт столбца column.
    COL_NAME tableID, columnId Возвращает имя columnID.
    COLUMNPROPERTY ID, column, property Возвращает информацию о свойстве property столбца column.
    DATABASEPROPERTY database, property Возвращает значение свойства property.
    DB_ID database_name Возвращает идентификационный номер базы данных Database_name.
    DB_NAME databaseID Возвращает имя базы данных по идентификатору databaseID.
    INDEX_COL table, indexID, keyed Возвращает имя индексированного столбца по идентификаторам indexID и keyID.
    INDEXPROPERTY tableID, index, property Возвращает информацию о свойстве property индекса index.
    OBJECT_ID object Возвращает идентификационный номер объекта object базы данных.
    OBJECT_NAME objectID Возвращает имя объекта по его идентификационному номеру objectID.
    OBJECTPROPERTY ID, property Возвращает информацию о свойстве property объекта по его идентификационному номеру ID.
    SQL_VARIANT_PROPERTY SQL_variant, property Возвращает указанное свойство property варианта Sql_variant.
    TYPEPROPERTY datatype, property Возвращает информацию о свойстве property для типа данных datatype.

    Используйте функции метаданных

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выберите файл с именем Metadata и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Функции безопасности

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

    Функции безопасности
    Функция Параметры Операция
    HAS_DBACCESS database_name Показывает, имеет ли текущий пользователь доступ к базе данных database_name.
    IS_MEMBER group_or_role Показывает, имеет ли текущий пользователь членство в группе или роли group_or_role.
    IS_SRVROLEMEMBER role [, login] Показывает, имеет ли текущая или указанная учетная запись login членство в роли role.
    SUSER_SID [login] Для текущей или указанной учетной записи login возвращает идентификационный номер безопасности (SID).
    SUSER_SNAME [] Возвращает имя учетной записи по ее идентификационному номеру безопасности SID.
    USER_ID [user] Возвращает идентификационный номер текущего или указанного пользователя user.
    USER Возвращает имя текущего пользователя базы данных.

    Используйте функции безопасности

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer кнопку нажмите New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выберите файл с именем Security и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Строковые функции

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

    Строковые функции
    Функция Параметры Операция
    ASCII char_expression Возвращает ASCII-код самого левого символа в строке char_expression.
    CHAR integer_expression Возвращает ASCII-символ, код которого равен integer_expression.
    CHARINDEX char_expression, char_expression [, start_position] Возвращает позицию первого выражения char_expression во втором выражении char_expression.
    LEFT char_expression, integer_expression Возвращает крайние слева символы integer_expression в выражении char_expression.
    LEN char_expression Возвращает количество символов в выражении char_expression.
    LOWER char_expression Возвращает выражение char_expression, в котором все символы приведены к нижнему регистру.
    LTRIM char_expression Возвращает выражение char_expression с удаленными начальными пробелами.
    NCHAR integer_expression Возвращает символ UNICODE, код которого задает integer_expression.
    REPLACE char_expression, char_expression, char_expression Находит все вхождения второй строки char_expression в первую char_expression и заменяет их на третью char_expression.
    RIGHT char_expression, integer_expression Возвращает крайние справа символы integer_expression в строке char_expression.
    RTRIM char_expression Возвращает строку char_expression с удаленными конечными пробелами.
    SOUNDEX char_expression Возвращает четырехзначный код SOUNDEX для char_expression.
    SPACE integer_expression Возвращает число integer_expression пробелов.
    SUBSTRING char_expression start, lenght Возвращает подстроку char_expression указанной длины lenght, начиная с символа start.
    UNICODE unicode_expression Возвращает значение UNICODE для первого символа в unicode_expression.
    UPPER char_expression Возвращает выражение char_expression, в котором все символы приведены к верхнему регистру.

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

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).Query Analyzer отобразит диалоговое окно Open Query File (Открыие файла запроса).
  • Выберите файл с именем String и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •

    Системные функции

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

    Системные функции
    Функция Параметры Операции
    APP_NAME Возвращает имя приложения.
    DATALENGHT выражение Возвращает количество байт, используемых для хранения выражения.
    ISDATE выражение Определяет, корректно ли данное выражение.
    ISNULL выражение Определяет, является ли данное выражение нулем.
    ISNUMERIC выражение Определяет, является ли данное выражение числом.
    NEWID Создает новый уникальный идентификационный номер uniqueidentifier.
    NULLIF выражение, выражение Возвращает NULL, если первое и второе выражение одинаковы.
    PARSENAME object_name, name_part Возвращает часть имени name_part объекта object_name.
    SYSTEM_USER Возвращает текущее имя пользователя системы.
    USER_NAME [id] Возвращает имя текущего пользователя или пользователя по указанному id.

    Используйте системные функции

  • Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).Query Analyzer откроет пустое окно Query (Запрос).
  • В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить запрос).Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
  • Выберите файл с именем System и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
  • Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).Query Analyzer отобразит результаты в панели сетки Grids Pane.
  • Закройте окно Query (Запрос).
    •
    Предыдущее занятие Следующее занятие
    Вернуться к учебному плану
    ^