Главная | Обратная связь | Поможем написать вашу работу!

Лекция 7: Создание запросов и фильтров. Вычисление при помощи оператора SELECT. Встроенные функции

Цели:

1. Изучить создание запросов и фильтров

2. Понять процесс выполнения вычислений при помощи оператора SELECT. Встроенные функции

Создание запросов и фильтров

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

Для реализации запросов используют специальный язык запросов SQL (Structured Query Language).

В ИС Запросы могут находиться как на стороне клиентского приложения, так и на стороне сервера. Если запрос хранится на стороне клиента, то он прописывается внутри объекта связи. В этом случае клиентское приложение не зависит от файла данных. Файл данных содержит только таблицы, поэтому, мы легко можем модифицировать клиентское приложение, не затрагивая файл данных, но в этом случае запрос передается серверу через сеть, что может вызвать проблемы с безопасностью.

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

Все запросы делятся на:

1. статические;

2. динамические

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

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

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

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

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

Существует четыре вида связи между таблицами:

1. Одна к одной - одному полю в первичной таблице соответствует одно поле во вторичной таблице;

2. Одна ко многим - одному полю в первичной таблице соответствует несколько полей во вторичной таблице;

3. Многие к одной - нескольким полям в первичной таблице соответствует одно поле во вторичной таблице;

4. Многие ко многим - одному полю в первичной таблице соответствует несколько полей во вторичной таблице и наоборот.

Запросы с первым видами связи называются простыми, а с остальными видами связи - сложными. Если в БД есть хотя бы две связанных таблицы, то БД называется реляционной.

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

SELECT [ALL|DISTINCT]

[TOP|PERCENT n]

<Список полей>

[INTO <Имя новой таблицы>]

[FROM <Имя таблицы >]

[WHERE <Условие>]

[GROUP BY <Поле>]

[ORDER BY <Поле > [ASC|DESC]]

[COMPUTE AVG|COUNT|MAX|MIN|SUM(<Выражение>)]

Здесь параметры ALL|DISTINCTпоказывают, какие записи обрабатываются: ALLобрабатывает все записи, DISTINCTтолько уникальные, удаляются повторения записей.

TOP nопределяет какое количество записей обрабатывают, если указан PERCENT, то n указывает процент от общего числа записей. <Список полей>- здесь указываются отображаемые поля из таблиц через запятую.

Замечания:

1. Если имена отображаемых полей в разных таблицах не повторяются, то мы можем указывать только имена столбцов или полей без указания самих таблиц (ФИО, Должность). Если отображаются поля из разных таблиц с одинаковыми именами нужно указывать и имя таблицы <Имя поля>. <Имя таблицы>;

2. Здесь же можно присваивать псевдонимы полям, следующим образом <Имя поля> AS <Псевдоним>

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

Раздел INTO. Если присутствует этот раздел, то на основе результатов запроса создается новая таблица. Параметр INTO это имя новой таблицы.

Раздел FROM. Здесь указываются таблицы и запросы, через запятую, которые участвуют в новом запросе.

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

FROM <Таблица1> INNER JOIN <Таблица2> ON <Таблица1>.<поле1> оператор <Таблица2>.<поле2> …

Здесь устанавливается взаимосвязь Таблицы 1и Таблицы2по Полю1и Полю2в зависимости от оператора сравнения. Таких разделов INNER JOINможет быть сколько угодно.

Раздел WHERE. Данный раздел используют для создания простых запросов, в этом случае в качестве условия указываем связываемые поля, либо этот раздел используют для создания фильтров, здесь указывают условия отбора. В условиях отбора мы можем использовать стандартные логические операторы NOT, OR, AND.

Замечание: В своем стандартном виде запросы могут реализовывать только статичные фильтры, но не динамические. Для реализации динамических фильтров используются хранимые процедуры.

Раздел GROUP BY- определяет поле для группировки записей в запросе.

Раздел ORDER BY- определяет поле для сортировки записей в запросе. Если указан параметр ASC, то будет производиться сортировка по возрастанию, если DESC- по убыванию. По умолчанию используется сортировка по возрастанию.

Раздел COMPUTEпозволяет в конце результатов выполнения запроса вывести некоторые итоговые вычисления по запросу. Возможны следующие виды вычислений: AVG- средняя параметра; COUNT- количество значений параметра не равных NULL; MAXи MIN- максимальные и минимальные значения параметра; SUM- сумма всех значений параметра, где <Выражение>- сам параметр. В качестве параметра обычно выступают какие-либо поля таблиц, участвующих в запросе.

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

SELECT TOP 20 PERCENT *. Cотрудники, Должность.Должности

FROM Сотрудники, Должности

WHERE Код.Сотрудники = Код.Должности

COMPUTE COUNT (ФИО.Сотрудники)

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

SELECT ALL Операция, Сумма

INTO [Сделки за Май]

FROM Операции

WHERE Месяц = 'Май'

GROUP BY Операция

ORDER BY Сумма

COMPUTE SUM (Сумма)

Замечание: В данном запросе при обозначении названия полей таблицы явно не указываются, так как использовалась только одна таблица.

Выполнение вычислений при помощи оператора SELECT. Встроенные функции

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

SELECT <Выражение>

где <выражение>- какое-то математическое выражение или функция. Выражение имеет стандартный вид (как в Visual Basic), оно может включать в себя встроенные функции сервера.

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

В SQL Server существуют следующие встроенные функции, разбитые на группы.

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

Замечание: В качестве параметров функции будем указывать соответствующий им тип данных.

· ABS (numeric)- модуль числа;

· ACOS/ASIN/ATAN (Float)- арккосинус, арксинус, арктангенс в радианах;

· COS/SIN/TAN/COT (Float)- косинус, синус, тангенс, котангенс;

· CEILING (Numeric)- наименьшее целое, большее или равное параметру в скобках;

· DEGREES (Numeric)- преобразует радианы в градусы;

· EXP(Float)- экспонента, е^х;

· FLOOR(Numeric)- наибольшее целое меньшее или равное выражению numeric;

· LOG(Float)- натуральный логарифм ln;

· LOG10(Float)- десятичный логарифм log₁₀;

· PI ()- число пи;

· POWER (Numeric,y)- возводит выражение Numeric в степень у;

· RADIANS (Numeric)- преобразует градусы в радианы;

· RAND ()- генерирует случайное число типа данных Float, расположенное между нулем и единицей;

· ROUND (Numeric, Длина)- округляет выражение Numeric до заданной Длины (количество знаков после запятой);

· SIGN (Numeric)- выводит знак числа +/- или ноль;

· SQUARE (Float)- вычисляет квадрат числа Float;

· SQRT (Float)- вычисляет квадратный корень числа Float.

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

· SELECT ABC(-10)результат 10

· SELECT SQRT (16)результат 4

· SELECT ROUND (125.85,0)результат 126

· SELECT POWER (2,4)результат 16

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

Строковые функции позволяют производить операции с одной или несколькими строками.

· 'Строка1'+ 'Строка2'присоединяет Строку1 к Строке2;

· ASCII(Char)- возвращает ASCII код с самого левого символа выражения Char;

· CHAR(Int)- выводит символ соответствующий ASCII коду в выражении Int;

· CHARINDEX(Образец, Выражение)- выводит позицию Образца выражения, то есть где находится Образец в Выражении;

· DIFFERENCE(Выражение1, Выражение2)- сравнивает два выражения, выводит числа от 0 до 4: 0 - выражения абсолютно различны; 4 - выражения абсолютно идентичны. Оба выражения типа данных Char;

· LEFT(Char, Int)- выводит из строки Char Int символов слева;

· RIGHT(Char, Int)- выводит из строки Char Int символов справа;

· LTRIM(Char)- удаляет из строки Char пробелы слева;

· RTRIM(Char)- удаляет из строки Char пробелы справа;

· WCHAR(Int)- выводит выражение Int в формате Unicode;

· REPLACE(Строка1, Строка2, Строка3)- меняет в Строке1 все элементы Строка2 на элементы Строка3;

· REPLICATE(Char, Int)- повторяет строку Char Int раз;

· REVERSE(Сhar)- производит инверсию строки Char, то есть располагает символы в обратном порядке;

· SPACE(Int)- выводит Int пробелов;

· STR(Float)- переводит число Float в строку;

· STUFF(Выражение1, Начало, Длина, Выражение2)- удаляет из Выражения1 начиная с позиции символа Начало количество символов равное параметру Длина, вместо них подставляет Выражение2;

· SUBSTRING(Выражение, Начало, Длина)- из Выражения выводится строка заданной Длины начиная с позиции Начало;

· UNICODE(Char)- выводит код в формате Unicode первого символа в строке Char;

· LOWER(Char)- переводит строку Char в маленькие буквы;

· UPPER(Char)- переводит строку Char в заглавные буквы.

Примеры применения строковых функций:

· SELECT ASCII('G')результат 71.

· SELECT LOWER('ABC')результат abc.

· SELECT RIGHT('ABCDE',3)результат CDE

· SELECT REVERSE('МИР')результат РИМ.

Замечание. Во всех строковых функциях значения выражения типа Charзаключаются в одинарные кавычки.

Функции дат

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

· dd - число дат (от 1 до 31);

· dy - день года (число от 1 до 366);

· hh - значение часа (0-23)

· ms - значение секунд (от 0 до 999)

· mi - значение минут (0-59)

· qq - значение (1-4)

· mm - значение месяцев (1-12)

· ss - значение секунд (0-59)

· wk - значение номеров недель в году

· dw - значение дней недели, неделя начинается с воскресенья (1-7).

· yy - значение лет (1753 -999)

Функции дат предназначены для работы с датами или времени. Существуют несколько следующие функции дат:

· DATEADD(часть, число, date)- добавляет к дате date часть даты увеличенное на число;

· DATEDIFF(часть, date1, date2)- выводит количество частей даты между date1 и date2;

· DATENAME(часть, date)- выводит символьное значение частей даты к заданной дате (название дней недели);

· DATEPART(часть, date)- выводит числовое значение части даты из заданной даты (номер месяца);

· DAY(date)- выводит количество дней в заданной дате;

· MONTH (date)- выводит количество месяцев в заданной дате;

· YEAR(date)- выводит количество лет в заданной дате;

· GETDATE()- выводит текущую дату установленную на компьютере;

Замечание: Даты выводятся в Американском формате: месяц/день/год.

Примеры функции работ с датами:

· SELECT DATEADD(dd,5,11/20/07)результат Nov/25/2007.

· SELECT DATEDIFF(dd,11/20/07, 11/25/07)результат 5 дней.

· SELECT DATENAME(mm, 11/20/07)результат November.

· SELECT DATEPART(mm, 11/20/07)результат 11.

Замечание: В выражениях оператора SELECTможно использовать операции сравнения. В результате будет либо истина TRUE, либо ложь FALSE. Можно использовать следующие операторы: =, <, >, >=, <=, <>,!<(не меньше),!>(не больше),!=(не равно). Приоритет операции задается круглыми скобками.

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

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

· COL_LENGTH(таблица, поле)- выводит ширину поля;

· DATALENGTH(выражение)- выводит длину выражения;

· GETANSINULL(имя БД)- выводит допустимо или недопустимо использовать в БД значение NULL;

· IDENT_INCR(таблица)- выводит шаг увеличения поля счетчика в таблице;

· IDENT_SEED(таблица)- выводит начальное значение счетчиков в таблице;

· ISDATE(выражение)- выводит единицу, если выражение является датой и ноль, если не является;

· ISNUMERIC(выражение)- выводит единицу, если выражение является числовым и ноль, если не числовым;

· NULIFF(выражение1, выражение2)- выводит NULL если выражение1 равно выражению 2.

Агрегатные функции

Агрегатные функции - позволяют вычислять итоговые значения по полям таблицы.

· AVG(поле)- выводит среднее значение поля;

· COUNT(*)- выводит количество записей в таблице;

· COUNT(поле)- выводит количество всех значений поля;

· MAX(поле)- выводит максимальное значение поля;

· MIN(поле)- выводит минимальное значение поля;

· STDEV(поле)- выводит среднеквадратичное отклонение всех значений поля;

· STDEVP(поле)- выводит среднеквадратичное отклонение различных значений поля;

· SUM(поле)- суммирует все значения поля;

· TOP n [Percent]- выводит n первых записей из таблицы, либо n% записей из таблицы;

· VAR(поле)- выводит дисперсию всех значений поля;

· VARP(поле)- выводит дисперсию всех различных значений поля.

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

· SELECT AVG(возраст) FROM Студенты- выводит средний возраст студента из таблицы "Студенты".

· SELECT COUNT(ФИО) FROM Студенты- выводит количество различных ФИО из таблицы "Студенты".

· SELECT Top 100 * FROM Студенты- выводит первые 100 студентов из таблицы "Студенты".

На этом мы заканчиваем рассмотрение запросов и фильтров. Дополнительную информацию можно найти в лабораторной работе №4.

12 3 Следующая ⇒