Агрегатные функции SQL - SUM, MIN, MAX, AVG, COUNT. Введение в структурированный язык запросов - SQL Функция sql с входными параметрами

26.08.2020

Функции языка Transact-SQL могут быть агрегатными или скалярными. Эти типы функций рассматриваются в этой статье.

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

Агрегатные функции выполняют вычисления над группой значений столбца и всегда возвращают одно значение результата этих вычислений. Язык Transact-SQL поддерживает несколько обычных агрегатных функций:

AVG

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

MIN и MAX

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

SUM

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

COUNT

Подсчитывает количество значений, отличных от null в столбце. Функция count(*) является единственной агрегатной функцией, которая не выполняет вычисления над столбцами. Эта функция возвращает количество строк (независимо от того, содержат ли отдельные столбцы значения null).

COUNT_BIG

Аналогична функции count, с той разницей, что возвращает значение данных типа BIGINT.

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

Скалярные функции

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

числовые функции;

функции даты;

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

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

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

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

Числовые функции

Числовые функции языка Transact-SQL - это математические функции для модифицирования числовых значений. Список числовых функций и их краткое описание приводится в таблице ниже:

Числовые функции Transact-SQL

Функция	Синтаксис	Описание	Пример использования
ABS	ABS(n)	Возвращает абсолютное значение (т. е. отрицательные значения возвращаются, как положительные) числового выражения n.	SELECT ABS(-5.320) -- Вернет 5.320 SELECT ABS(8.90) -- Вернет 8.90
ACOS, ASIN, ATAN, ATN2	ACOS(n), ASIN(n), ATAN(n), ATN2(n, m)	Обратные тригонометрические функции, вычисляющие арккосинус, арксинус, арктангенс значения n (для ATN2 вычисляется арктангенс n/m). Исходные значения n, m и результат имеют тип данных FLOAT.
COS, SIN, TAN, COT	COS(n), SIN(n), TAN(n), COT(n)	Тригонометрические функции, вычисляющие косинус, синус, тангенс, котангенс значения n. Результат имеет тип данных FLOAT.
DEGREES, RADIANS	DEGREES(n), RADIANS(n)	Функция DEGREES преобразует радианы в градусы, RADIANS соответственно наоборот.	SELECT DEGREES(PI() / 4) -- Вернет 45 SELECT COS(RADIANS(60.0)) -- Вернет 0.5
CEILING	CEILING(n)	Округляет число до большего целого значения.	SELECT CEILING(-5.320) -- Вернет -5 SELECT CEILING(8.90) -- Вернет 9
ROUND	ROUND(n, p, [t])	Округляет значение n с точностью до p. Когда аргумент p положительное число, округляется дробная часть числа n, а когда отрицательное - целая часть. При использовании необязательного аргумента t, число n не округляется, а усекается (т.е. округляется в меньшую сторону).	SELECT ROUND(5.3208, 3) -- Вернет 5.3210 SELECT ROUND(125.384, -1) -- Вернет 130.000 SELECT ROUND(125.384, -1, 1) -- Вернет 120.000
FLOOR	FLOOR(n)	Округляет до меньшего целого значения.	SELECT FLOOR(5.88) -- Вернет 5
EXP	EXP(n)	Вычисляет значение e n .
LOG, LOG10	LOG(n), LOG10(n)	LOG(n) - вычисляет натуральный логарифм (т.е. с основанием e) числа n, LOG10(n) - вычисляет десятичный (с основанием 10) логарифм числа n.
PI	PI()	Возвращает значение π (3,1415).
POWER	POWER(x, y)	Вычисляет значение x y .
RAND	RAND()	Возвращает произвольное число типа FLOAT в диапазоне значений между 0 и 1.
ROWCOUNT_BIG	ROWCOUNT_BIG()	Возвращает количество строк таблицы, которые были обработаны последней инструкцией Transact-SQL, исполненной системой. Возвращаемое значение имеет тип BIGINT.
SIGN	SIGN(n)	Возвращает знак значения n в виде числа: +1, если положительное, -1, если отрицательное.
SQRT, SQUARE	SQRT(n), SQUARE(n)	SQRT(n) - вычисляет квадратный корень числа n, SQUARE(n) - возвращает квадрат аргумента n.

Функции даты

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

Функции даты Transact-SQL

Функция	Синтаксис	Описание	Пример использования
GETDATE	GETDATE()	Возвращает текущую системную дату и время.	SELECT GETDATE()
DATEPART	DATEPART (item, date)	Возвращает указанную в параметре item часть даты date в виде целого числа.	Вернет 1 (Январь) SELECT DATEPART(month, "01.01.2012") -- Вернет 4 (Wednesday) SELECT DATEPART(weekday, "02.01.2012")
DATENAME	DATENAME (item, date)	Возвращает указанную в параметре item часть даты date в виде строки символов.	Вернет January SELECT DATENAME(month, "01.01.2012") -- Вернет Wednesday SELECT DATENAME(weekday, "02.01.2012")
DATEDIFF	DATEDIFF (item, dat1, dat2)	Вычисляет разницу между двумя частями дат dat1 и dat2 и возвращает целочисленный результат в единицах, указанных в аргументе item.	Вернет 19 (19 лет промежуток между датами) SELECT DATEDIFF(year, "01.01.1990", "01.01.2010") -- Вернет 7305 (7305 дней промежуток между датами) SELECT DATEDIFF(day, "01.01.1990", "01.01.2010")
DATEADD	DATEADD (item, n, date)	Прибавляет n-е количество единиц, указанных в аргументе item к указанной дате date. (Значение аргумента n также может быть отрицательным.)	Прибавит 3 дня к текущей дате SELECT DATEADD(day, 3, GETDATE())

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

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

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

Функция	Синтаксис	Описание	Пример использования
ASCII, UNICODE	ASCII(char), UNICODE(char)	Преобразовывает указанный символ в соответствующее целое число кода ASCII.	SELECT ASCII("W") -- 87 SELECT UNICODE("ю") -- 1102
CHAR, NCHAR	CHAR(int), NCHAR(int)	Преобразовывает код ASCII (или Unicode если NCHAR) в соответствующий символ.	SELECT CHAR(87) -- "W" SELECT NCHAR(1102) -- "ю"
CHARINDEX	CHARINDEX (str1, str2)	Возвращает начальную позицию вхождения подстроки str1 в строку str2. Если строка str2 не содержит подстроки str1, возвращается значение 0	Вернет 5 SELECT CHARINDEX ("морф", "полиморфизм")
DIFFERENCE	DIFFERENCE (str1, str2)	Возвращает целое число от 0 до 4, которое является разницей между значениями SOUNDEX двух строк str1 и str2. Метод SOUNDEX возвращает число, которое характеризует звучание строки. С помощью этого метода можно определить подобно звучащие строки. Работает только для символов ASCII.	Вернет 2 SELECT DIFFERENCE ("spelling", "telling")
LEFT, RIGHT	LEFT (str, length), RIGHT (str, length)	Возвращает количество первых символов строки str, заданное параметром length для LEFT и последние length символов строки str для функции RIGHT.	DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "Синх" SELECT LEFT(@str, 4) -- Вернет "зация" SELECT RIGHT(@str, 5)
LEN	LEN(str)	Возвращает количество символов (не количество байт) строки str, указанной в аргументе, включая конечные пробелы.
LOWER, UPPER	LOWER(str), UPPER(str)	Функция LOWER преобразовывает все прописные буквы строки str1 в строчные. Входящие в строку строчные буквы и иные символы не затрагиваются. Функция UPPER преобразовывает все строчные буквы строки str в прописные.	DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "СИНХРОНИЗАЦИЯ" SELECT UPPER(@str) -- Вернет "синхронизация" SELECT LOWER(@str)
LTRIM, RTRIM	LTRIM(str), RTRIM(str)	Функция LTRIM удаляет начальные пробелы в строке str, RTRIM соответственно удаляет пробелы в конце строки.
QUOTENAME	QUOTENAME (char_string)	Возвращает строку в кодировке Unicode с добавленными ограничителями, чтобы преобразовать строку ввода в действительный идентификатор с ограничителями.	DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "[Синхронизация]" SELECT QUOTENAME(@str)
PATINDEX	PATINDEX (%p%, expr)	Возвращает начальную позицию первого вхождения шаблона p в заданное выражение expr, или ноль, если данный шаблон не обнаружен.	Вернет 4 SELECT PATINDEX("%хро%", "Синхронизация")
REPLACE	REPLACE (str1, str2, str3)	Заменяет все вхождения подстроки str2 в строке str1 подстрокой str3.	Вернет "Десинхронизация" SELECT REPLACE("Синхронизация", "Синхр", "Десинхр")
REPLICATE	REPLICATE (str, i)	Повторяет i раз строку str.	Вернет "aBaBaBaBaB" SELECT REPLICATE("aB", 5)
REVERSE	REVERSE (str)	Выводит строку str в обратном порядке.	Вернет "яицазинорхниС" SELECT REVERSE("Синхронизация")
SOUNDEX	SOUNDEX (str)	Возвращает четырехсимвольный код soundex, используемый для определения похожести двух строк. Работает только для символов ASCII.
SPACE	SPACE (length)	Возвращает строку пробелов длиной, указанной в параметре length. Аналог REPLICATE(" ", length).
STR	STR (f[, len[, d]])	Преобразовывает заданное выражение с плавающей точкой f в строку, где len - длина строки, включая десятичную точку, знак, цифры и пробелы (по умолчанию равно 10), а d - число разрядов дробной части, которые нужно возвратить.	Вернет "3.14" SELECT STR (3.1415, 4, 2)
STUFF	STUFF (str1, a, length, str2)	Удаляет из строки str1 length-символов, начиная с позиции a, и вставляет на их место строку str2.	Note in a book SELECT STUFF("Notebook", 5, 0," in a ") -- Handbook SELECT STUFF("Notebook", 1, 4, "Hand")
SUBSTRING	SUBSTRING (str1, a, length)	Извлекает из строки str, начиная с позиции a, подстроку длиной length.

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

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

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

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

Функция	Синтаксис	Описание	Пример использования
CAST	CAST (w AS type [(length)]	Преобразовывает выражение w в указанный тип данных type (если это возможно). Аргумент w может быть любым действительным выражением.	Вернет 3 SELECT CAST (3.1258 AS INT)
COALESCE	COALESCE (a1, a2)	Возвращает первое значение выражения из списка выражений a1, a2, ..., которое не является значением null.
COL_LENGTH	COL_LENGTH (obj, col)	Возвращает длину столбца col объекта базы данных (таблицы или представления) obj.	Вернет 4 SELECT COL_LENGTH ("Employee", "Id")
CONVERT	CONVERT (type[(length)], w)	Эквивалент функции CAST, но аргументы указываются по-иному. Может применяться с любым типом данных.
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	Возвращает текущие дату и время.
CURRENT_USER	CURRENT_USER	Возвращает имя текущего пользователя.
DATALENGTH	DATALENGTH (z)	Возвращает число байтов, которые занимает выражение z.	Этот запрос возвращает длину каждого поля SELECT DATALENGTH(FirstName) FROM Employee
GETANSINULL	GETANSINULL ("dbname")	Возвращает 1, если использование значений null в базе данных dbname отвечает требованиям стандарта ANSI SQL.
ISNULL	ISNULL (expr, value)	Возвращает значение выражения expr, если оно не равно NULL; в противном случае возвращается значение value.
ISNUMERIC	ISNUMERIC (expr)	Определяет, имеет ли выражение expr действительный числовой тип.
NEWID	NEWID()	Создает однозначный идентификационный номер ID, состоящий из 16-байтовой двоичной строки, предназначенной для хранения значений типа данных UNIQUEIDENTIFIER.
NEWSEQUENTIALID	NEWSEQUENTIALID()	Создает идентификатор GUID, больший, чем любой другой идентификатор GUID, созданный ранее этой функцией на указанном компьютере. (Эту функцию можно использовать только как значение по умолчанию для столбца.)
NULLIF	NULLIF (expr1, expr2)	Возвращает значение null, если значения выражений expr1 и expr2 одинаковые.	Запрос возвращает NULL для проекта, -- у которого Number = "p1" SELECT NULLIF(Number, "p1") FROM Project
SERVERPROPERTY	SERVERPROPERTY (propertyname)	Возвращает информацию о свойствах сервера базы данных.
SYSTEM_USER	SYSTEM_USER	Возвращает ID текущего пользователя.
USER_ID	USER_ID ()	Возвращает идентификатор пользователя username. Если пользователь не указан, то возвращается идентификатор текущего пользователя.
USER_NAME	USER_NAME ()	Возвращает имя пользователя с указанным идентификатором id. Если идентификатор не указан, то возвращается имя текущего пользователя.

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

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

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

Функция	Синтаксис	Описание	Пример использования
COL_NAME	COL_NAME (tab_id, col_id)	Возвращает имя столбца с указанным идентификатором col_id таблицы с идентификатором tab_id.	Вернет имя столбца "LastName" SELECT COL_NAME (OBJECT_ID("Employee"), 3)
COLUMNPROPERTY	COLUMNPROPERTY (id, col, property)	Возвращает информацию об указанном столбце.	Вернет значение свойства PRECISION -- для столбца Id таблицы Employee SELECT COLUMNPROPERTY (OBJECT_ID("Employee"), "Id", "precision")
DATABASEPROPERTY	DATABASEPROPERTY (database, property)	Возвращает значение свойства property базы данных database.	Вернет значение свойства IsNullConcat -- для базы данных SampleDb SELECT DATABASEPROPERTY ("SampleDb", "IsNullConcat")
DB_ID	DB_ID ()	Возвращает идентификатор базы данных db_name. Если имя базы данных не указано, то возвращается идентификатор текущей базы данных.
DB_NAME	DB_NAME ()	Возвращает имя базы данных, имеющей идентификатор db_id. Если идентификатор не указан, то возвращается имя текущей базы данных.
INDEX_COL	INDEX_COL (table, i, no)	Возвращает имя индексированного столбца таблицы table. Столбец указывается идентификатором индекса i и позицией no столбца в этом индексе.
INDEXPROPERTY	INDEXPROPERTY (obj_id, index_name, property)	Возвращает свойства именованного индекса или статистики для указанного идентификационного номера таблицы, имя индекса или статистики, а также имя свойства.
OBJECT_NAME	OBJECT_NAME (obj_id)	Возвращает имя объекта базы данных, имеющего идентификатор obj_id.	SELECT OBJECT_NAME(245575913);
OBJECT_ID	OBJECT_ID (obj_name)	Возвращает идентификатор объекта obj_name базы данных.	Вернет 245575913 - ID таблицы Employee SELECT OBJECT_ID("Employee")
OBJECTPROPERTY	OBJECTPROPERTY (obj_id, property)	Возвращает информацию об объектах из текущей базы данных.

Основные функциональные возможности языка SQL приведены ниже.

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

Создание базы данных . Для создания новой базы данных используется оператор CREATE DATABASE. В структуре оператора указывается имя создаваемой базы данных.

Создание таблиц. Базовая таблица создается с помощью оператора CREATE TABLE. В этом операторе указываются имена полей, типы данных для них, длина (для некоторых типов данных). В SQL используются следующие типы данных:

INTEGER – целое число;

CHAR – символьное значение;

VARCHAR – символьное значение, сохраняются только непустые символы;

DECIMAL – десятичное число;

FLOAT – число с плавающей запятой;

DOUBLE PRECISION – удвоенная точность с плавающей точкой;

DATETIME – дата и время;

BOOL – булевое значение.

В операторе создания таблицы указываются ограничения на значения столбцов и на таблицу. Возможные ограничения показаны в табл. 4.8

Таблица 4.8 Ограничения на определяемые данные

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

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

Нельзя использовать оператор создания таблицы несколько раз для одной и той же таблицы. Если после ее создания обнаружились неточности в ее определении, то внести изменения можно с помощью оператора ALTER TABLE. Этот оператор предназначен для изменения структуры существующей таблицы: можно удалить или добавить поле к существующей таблице.

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

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

Модификация данных предполагает изменения значений в одном или нескольких столбцах таблицы и выполняется с помощью оператора UPDATE. Пример:

SET сумма=сумма+1000.00

WHERE сумма>0

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

FROM таблица

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

Обеспечение целостности данных. Язык SQL позволяет определить достаточно сложные ограничения целостности, удовлетворение которым будет проверяться при всех модификациях базы данных. Контроль за результатами транзакций, обработка возникающих ошибок и координирование параллельной работы с базой данных нескольких приложений или пользователей обеспечивается операторами COMMIT(фиксирует удачное окончание текущей транзакции и начало новой) и ROLLBACK (необходимость отката – автоматического восстановления состояния базы данных на начало транзакции)

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

В SQL можно создавать вложенные последовательности запросов (подзапросы). Существуют определенные типы запросов, которые лучше реализовывать с помощью подзапросов. К таким запросам относятся так называемые проверки существования. Предположим, что требуется получить данные о студентах, которые не имеют оценку «семь баллов». Если будет возвращено пустое множество, то это означает лишь одно – у каждого студента есть, по крайней мере, одна такая оценка.

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

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

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

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

Доступ к данным в многопользовательской среде регулируется с помощью операторов GRANT и REVOKE. В каждом операторе необходимо указать пользователя, объект (таблицу, представление), по отношению к которому задаются полномочия, и сами полномочия. Например, оператор GRANT задает пользователю Х возможность производить выборку данных из таблицы ТОВАР:

GRANT SELECT ON ТОВАР TO X

Оператор REVOKE аннулирует все предоставленные ранее полномочия.

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

Стандартом фирмы IBM для SQL-продуктов регламентировано использование встроенного языка SQL. При написании прикладной программы ее текст представляет собой смесь команд основного языка программирования (например, C, Pascal, Cobol, Fortran, Assembler) и команд SQL со специальным префиксом, например. ExecSQL. Структура SQL-предложений расширена для размещения переменных основного языка в SQL-конструкции.

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

Диалекты языка SQL

В современных реляционных СУБД для описания и манипулирования данными используются диалекты языка SQL. Подмножество языка SQL, позволяющее создавать и описывать БД, называется DDL (Data Definition Language).

Первоначально язык SQL назывался SEQUEL(Structured English Query Language), потом SEQUEL/2, а затем просто – SQL. Сегодня язык SQL –фактический стандарт для реляционных СУБД.

Первый стандарт языка появился в 1989 г. – SQL-89 и поддерживался практически всеми коммерческими реляционными СУБД. Он имел общий характер и допускал широкое толкование. Достоинствами SQL-89 можно считать стандартизацию синтаксиса и семантики операторов выборки и манипулирования данными, а также фиксацию средств ограничения целостности базы данных. Однако в нем отсутствовал такой важный раздел как манипулирование схемой базы данных. Неполнота стандарта SQL-89 привела к появлению в 1992г. следующей версии языка SQL.

SQL2 (или SQL-92) охватывает практически все необходимые проблемы: манипулирование схемой базы данных, управление транзакциями и сессиями, поддерживает архитектуры клиент-сервер или средства разработки приложений.

Дальнейшим шагом развития языка является вариант SQL 3. Эта версия языка дополняется механизмом триггеров, определением произвольного типа данных, объектным расширением.

В настоящее время существует три уровня языка: начальный, промежуточный и полный. Многие производители своих СУБД применяют собственные реализации SQL, основанные как минимум на начальном уровне соответствующего стандарта ANSI, и содержащие некоторые расширения, специфические для той или иной СУБД. В табл. 4.9 приведены примеры диалектов SQL.

Таблица 4.9 Диалекты языка SQL

СУБД	Язык запросов
СУБД System R	SQL
DB2	SQL
Access	SQL
SYBASE SQL Anywhere	Watcom-SQL
SYBASE SQL Server	Transact_SQL
My SQL	SQL
Oracle	PL/SQL

В объектно-ориентированных БД используется язык объектных запросов OQL (Object Query Language). За основу языка OQL была взята команда SELECT языка SQL2 и добавлены возможность направлять запрос к объекту или коллекции объектов, а также возможность вызывать методы в рамках одного запроса.

Совместимость многих используемых диалектов SQL обусловливает совместимость СУБД. Так, СУБД SYBASE SQL Anywhere максимально, насколько это возможно для СУБД такого класса, совместима с СУБД SYBASE SQL Server. Одной из сторон такой совместимости является поддержка в SYBASE SQL Anywhere такого диалекта языка SQL как Transact-SQL . Этот диалект используется в SYBASE SQL Server и может применяться в SYBASE SQL Anywhere наряду с собственным диалектом языка SQL - Watcom-SQL .

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

1. Как можно классифицировать СУБД?

2. Какие модели баз данных существуют?

3. Что является основными элементами инфологических моделей?

4. Какие типы связей между сущностями существуют?

5. Что такое ER-диаграммы и для чего они используются?

6. Что позволяет делать процедура нормализации таблиц?

7. Назовите языковые и программные средства СУБД?

8. К каому типу относится СУБД MS Access?

9. Назовите основные объекты СУБД MS Access?

10. Для чего используются основные операторы языка SQL?

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

Шаг 15. Функции SUM, AVG, MIN, MAX, COUNT…

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

SELECT AVG(D_STAFF.S_EXPERIENCE) AS [СРЕДНИЙ СТАЖ СОТРУДНИКОВ] FROM D_STAFF

SQL функция AVG.

Аналогично можно вычислить минимальное и максимальное значения (MIN, MAX), общую сумму (SUM) и т.д. Советую попробовать это выполнить с использованием обучающей программы. Стоит попробовать определить дополнительные критерии отбора записей, участвующих в определении итогового значения функции с использованием оператора WHERE.

Перечисленные выше функции для определения своего значения используют результат запроса целиком. Такие функции называются агрегатными . Также, есть ряд функций, аргументом которых являются не все значения определенной в запросе колонки, а каждое отдельное значение каждой отдельной строки результата. Примером такой функции является SQL функция вычисления длины текстового поля LEN:

SELECT S_NAME, LEN(D_STAFF.S_NAME) AS [ДЛИНА] FROM D_STAFF

Можно использовать суперпозицию SQL функций , как показано ниже, и вычислить максимальное значение длины поля S_NAME.

SELECT MAX(LEN(D_STAFF.S_NAME)) AS [МАКСИМАЛЬНАЯ ДЛИНА] FROM D_STAFF

SQL функция MAX.

Ну и в заключении все вместе.

SELECT SUM(D_STAFF.S_EXPERIENCE) AS [СУММА], AVG(D_STAFF.S_EXPERIENCE) AS [СРЕДНЕЕ], MIN(D_STAFF.S_EXPERIENCE) AS [МИНИМУМ], MAX(D_STAFF.S_EXPERIENCE) AS [МАКСИМУМ], COUNT(*) AS [КОЛИЧЕСТВО ЗАПИСЕЙ], MAX(LEN(D_STAFF.S_NAME)) AS [МАКСИМАЛЬНАЯ ДЛИНА] FROM D_STAFF

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

Обратите внимание на аргумент функции COUNT. Я указал в качестве аргумента (*), поскольку хочу получиться именно общее число записей. Если указать, например COUNT(S_NAME), то результатом будет число непустых значений S_NAME (S_NAME IS NOT NULL). Можно было бы написать COUNT(DISTINCT S_NAME) и получить количество уникальных значений S_NAME, но MS Access такой вариант, к сожалению, не поддерживает. В нашем примере COUNT(S_NAME) и COUNT(*) дают абсолютно одинаковый результат.

Шаг 16. Преобразование текста

Часто, текстовые значения заполняются пользователями программного обеспечения по-разному: кто пишет Ф.И.О. с заглавной буквы, кто нет; кто-то пишет все заглавными буквами. Многие отчетные формы требуют унифицированного подхода, да и не только отчетные формы. Для решения этой задачи в SQL есть две функции UCASE и LCASE. Пример запроса и результат его обработки приведены ниже:

SELECT UCASE(D_STAFF.S_NAME) AS , LCASE(D_STAFF.S_NAME) AS FROM D_STAFF

SQL функции UCASE и LCASE.

Шаг 17. SQL и работа со строками

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

SELECT UCASE(MID(P_NAME,3,5)) FROM D_PROFILE

Суперпозиция SQL функций UCASE и MID.

Мы “вырезали” из значений наименований профилей по 5 символов, начиная с 3-го, и получили кучу повторяющегося “мусора”. Для того чтобы оставить только уникальные значения будем использовать ключевое слово DISTINCT.

SELECT DISTINCT UCASE(MID(P_NAME,3,5)) AS FROM D_PROFILE

Выбор уникальных значений агрегатной функции.

Иногда приходится в качестве аргументов функции MID использовать выражения с функцией LEN. В следующем примере мы уже выводим последние 5 символов в наименованиях профилей.

SELECT UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) FROM D_PROFILE

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

Шаг 18. Использование SQL функций в критерии отбора записей. Оператор HAVING

Разобравшись с функциями, практически сразу возникает вопрос, как их можно использовать в критериях отбора записей? Некоторые функции, а именно те, которые не являются агрегатными - использовать достаточно легко. Вот, например, список сотрудников, чье Ф.И.О. более 25 символов.

SELECT S_NAME FROM D_STAFF WHERE LEN(D_STAFF.S_NAME) > 25

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

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

SELECT S_POSITION FROM D_STAFF WHERE COUNT(S_POSITION)>1

Для таких случаев в SQL ввели ключевое слово HAVING, которое поможет нам решить проблему с должностями и сотрудниками.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION HAVING COUNT(S_POSITION)>1

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

Шаг 19. Группировка данных в результатах SQL запроса оператором GROUP BY

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

SELECT S_POSITION FROM D_STAFF

А это два варианта, позволяющие вывести только уникальные значения S_POSITION.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION

SELECT DISTINCT S_POSITION FROM D_STAFF

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

SELECT PROFILE_ID AS , COUNT(PROFILE_ID) AS [КОЛИЧЕСТВО ЗАПИСЕЙ] FROM D_STAFF_PROFILE GROUP BY PROFILE_ID

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

Оператор GROUP BY позволяет также группировать результат запроса более чем по одному полю, перечисляя их через запятую. Я надеюсь, что после всего вышесказанного дополнительные комментарии к результату последнего запроса не нужны.

SELECT S.S_POSITION AS , S.S_NAME AS [СОТРУДНИК], COUNT(SP.STAFF_ID) AS [КОЛИЧЕСТВО ЗАПИСЕЙ В ТАБЛИЦЕ D_STAFF_PROFILE] FROM D_STAFF S, D_STAFF_PROFILE SP WHERE S.XD_IID=SP.STAFF_ID GROUP BY S.S_POSITION, S.S_NAME

Группировка строк результата SQL запроса по нескольким полям.

Основные команды SQL, которые должен знать каждый программист

Язык SQL или Structured Query Language (язык структурированных запросов) предназначен для управления данными в системе реляционных баз данных (RDBMS). В этой статье будет рассказано о часто используемых командах SQL, с которыми должен быть знаком каждый программист. Этот материал идеально подойдёт для тех, кто хочет освежить свои знания об SQL перед собеседованием на работу. Для этого разберите приведённые в статье примеры и вспомните, что проходили на парах по базам данных.

Обратите внимание, что в некоторых системах баз данных требуется указывать точку с запятой в конце каждого оператора. Точка с запятой является стандартным указателем на конец каждого оператора в SQL. В примерах используется MySQL, поэтому точка с запятой требуется.

Настройка базы данных для примеров

Создайте базу данных для демонстрации работы команд. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql . После этого откройте терминал и войдите в консоль MySQL с помощью следующей команды (статья предполагает, что MySQL уже установлен в системе):

Mysql -u root -p

Затем введите пароль.

Выполните следующую команду. Назовём базу данных «university»:

CREATE DATABASE university; USE university; SOURCE ; SOURCE

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

1. Просмотр доступных баз данных

SHOW DATABASES;

2. Создание новой базы данных

CREATE DATABASE;

3. Выбор базы данных для использования

USE ;

4. Импорт SQL-команд из файла.sql

SOURCE ;

5. Удаление базы данных

DROP DATABASE ;

Работа с таблицами

6. Просмотр таблиц, доступных в базе данных

SHOW TABLES;

7. Создание новой таблицы

CREATE TABLE ( , , PRIMARY KEY (), FOREIGN KEY () REFERENCES ());

Ограничения целостности при использовании CREATE TABLE

Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:

ячейка таблицы не может иметь значение NULL;
первичный ключ - PRIMARY KEY (col_name1, col_name2, …) ;
внешний ключ - FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .

Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.

Пример

Создайте таблицу «instructor»:

CREATE TABLE instructor (ID CHAR(5), name VARCHAR(20) NOT NULL, dept_name VARCHAR(20), salary NUMERIC(8,2), PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department(dept_name));

8. Сведения о таблице

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

DESCRIBE ;

9. Добавление данных в таблицу

INSERT INTO (, , , …) VALUES (, , , …);

При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.

INSERT INTO VALUES (, , , …);

10. Обновление данных таблицы

UPDATE SET = , = , ... WHERE ;

11. Удаление всех данных из таблицы

DELETE FROM ;

12. Удаление таблицы

DROP TABLE ;

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

13. SELECT

SELECT используется для получения данных из определённой таблицы:

SELECT , , … FROM ;

Следующей командой можно вывести все данные из таблицы:

SELECT * FROM ;

14. SELECT DISTINCT

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

SELECT DISTINCT , , … FROM ;

15. WHERE

Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

SELECT , , … FROM WHERE ;

В запросе можно задавать следующие условия:

сравнение текста;
сравнение численных значений;
логические операции AND (и), OR (или) и NOT (отрицание).

Пример

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

SELECT * FROM course WHERE dept_name=’Comp. Sci.’; SELECT * FROM course WHERE credits>3; SELECT * FROM course WHERE dept_name="Comp. Sci." AND credits>3;

16. GROUP BY

Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений.

SELECT , , … FROM GROUP BY ;

Пример

Выведем количество курсов для каждого факультета:

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name;

17. HAVING

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

SELECT , , ... FROM GROUP BY HAVING

Пример

Выведем список факультетов, у которых более одного курса:

SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name HAVING COUNT(course_id)>1;

18. ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC .

SELECT , , … FROM ORDER BY , , … ASC|DESC;

Пример

Выведем список курсов по возрастанию и убыванию количества кредитов:

SELECT * FROM course ORDER BY credits; SELECT * FROM course ORDER BY credits DESC;

19. BETWEEN

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

SELECT , , … FROM WHERE BETWEEN AND ;

Пример

Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000;

20. LIKE

Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения.

Есть два свободных оператора, которые используются в LIKE:

% (ни одного, один или несколько символов);
_ (один символ).

SELECT , , … FROM WHERE LIKE ;

Пример

Выведем список курсов, в имени которых содержится «to» , и список курсов, название которых начинается с «CS-»:

SELECT * FROM course WHERE title LIKE ‘%to%’; SELECT * FROM course WHERE course_id LIKE "CS-___";

21. IN

С помощью IN можно указать несколько значений для оператора WHERE:

SELECT , , … FROM WHERE IN (, , …);

Пример

Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:

SELECT * FROM student WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);

22. JOIN

JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:

SELECT , , … FROM JOIN ON = ;

Пример 1

Выведем список всех курсов и соответствующую информацию о факультетах:

SELECT * FROM course JOIN department ON course.dept_name=department.dept_name;

Пример 2

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

SELECT prereq.course_id, title, dept_name, credits, prereq_id FROM prereq LEFT OUTER JOIN course ON prereq.course_id=course.course_id;

Пример 3

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

SELECT course.course_id, title, dept_name, credits, prereq_id FROM prereq RIGHT OUTER JOIN course ON prereq.course_id=course.course_id;

23. View

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

Создание

CREATE VIEW AS SELECT , , … FROM WHERE ;

Удаление

DROP VIEW ;

Пример

Создадим view , состоящую из курсов с 3 кредитами:

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

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

COUNT (col_name) - возвращает количество строк;
SUM (col_name) - возвращает сумму значений в данном столбце;
AVG (col_name) - возвращает среднее значение данного столбца;
MIN (col_name) - возвращает наименьшее значение данного столбца;
MAX (col_name) - возвращает наибольшее значение данного столбца.

25. Вложенные подзапросы

Вложенные подзапросы - это SQL-запросы, которые включают выражения SELECT , FROM и WHERE , вложенные в другой запрос.

Пример

Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:

SELECT DISTINCT course_id FROM section WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (SELECT course_id FROM section WHERE semester = ‘Spring’ AND year= 2010);

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

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

ABS (значение ) – возвращает абсолютное значение числа;

Round (значение, точность ) – возвращает числовое значение, округленное до указанного аргументом точность количества десятичных разрядов;

SIGN (значение ) – возвращает минус, если число отрицательное, и плюс – в противном случае;

POWER (значение, степень ) – возводит число в степень;

SQRT (значение ) – извлекает квадратный корень числа;

CEILING (значение) – возвращает ближайшее целое число большее или равное значению;

- FLOOR (значение) – возвращает ближайшее целое число меньшее или равное значению.

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

ASCII (строка ) – возвращает ASCII код первого символа строки;

CH A R (число )– возвращают символ по ASCII коду;

LEN (строка )– возвращает длину строки в символах, исключая конечные пробелы;

L TRIM (строка) / RTRIM (строка)- удаляет пробелы в начале/конце строки;

LEFT (строка, число) / R IGHT (строка, число) – возвращает указанное аргументом число количество символов строки, начиная с левого/правого края;

SUBSTRING (строка, позиция, длина ) – возвращает подстроку указанной длины из строки, начиная с указанной позиции;

LOWER (строка) / UPPER (строка ) – возвращает строку, преобразованную в нижний / верхний регистр и т.д.

Функции для работы с датами:

GETDATE () – возвращает значение, которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server;

DAY (значение_дата) –возвращает число из указанной даты;

MONTH (значение_дата) – возвращает номер месяца из указанной даты;

YEAR (значение_дата) – возвращает значение года из указанной даты;

DATENANE(часть, значение_дата ) – возвращает символьную строку, представляющую указанную часть (Day , Month , Hour и т.д. ) из указанной даты;

DATEPART(часть, значение_дата ) – возвращает целое число, представляющее указанную часть (Day , Month , Hour и т.д. ) из указанной даты.

Функции преобразования типов данных

CAST (значение AS тип_данных )

CONVERT (тип_данных , значение )

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

7.3. Команды языка определения данных

Язык определения данных содержит команды, которые предназначены для создания, изменения и удаления базы данных и ее объектов.

Создание таблицы

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

Синтаксис команды:

CREATE TABLE имя_таблицы ({ описание_столбца | имя_вычисляемого_столбца AS выражение | ограничения_целостности_уровня_таблицы} [, ...])

Именем таблицы является идентификатор длинной не более 128 символов.

Таблица может содержать вычисляемый столбец, тогда значение столбца определяется выражением, которое хранится в структуре таблицы. Изменять данные вычисляемого столбца нельзя, поэтому для него не могут быть установлены ограничения целостности NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY и значение DEFAULT.

Синтаксис описания столбца таблицы имеет вид:

имя_столбца тип_данных [(размер) ]

[{DEFAULT значение_по_умолчанию | IDENTITY [(значение, шаг) ]}]

[ограничения_целостности_уровня_столбца]

DEFAULT - позволяет задать значение, присваиваемое столбцу во вновь добавляемой записи.

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

Существует две группы ограничений целостности, обрабатываемых СУБД:

Декларативные ограничения целостности, которые объявляются при создании или изменении таблицы;

Процедурные ограничения целостности, которые обрабатываются триггерами.

Декларативные ограничения целостности могут быть ограничениями на уровне таблицы и ограничениями на уровне таблицы. Ограничения на уровне столбца применяется только к одному столбцу. Каждому декларативному ограничению целостности может быть присвоено имя.

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

{{PRIMARY KEY | UNIQUE | NOT NULL } |FOREIGN KEY REFERENCES имя_таблицы(имя_столбца )

|CHECK логическое_выражение}

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

Ограничение по первичному ключу PRIMARY KEY. Все значения первичного ключа таблицы должны быть уникальными и отличаться от значения Null. В таблице может быть только один первичный ключ. Если он является составным, то ограничения целостности по первичному ключу задаются на уровне таблицы;

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

Ограничение NOT NULL, запрещающее хранить в столбце значение NULL;

Ограничение по внешнему ключу FOREIGN KEY (ограничение ссылочной целостности). Для столбца, который является внешним ключом, с помощью REFERENCES указывается имя таблицы, с которой устанавливается связь, и имя столбца этой таблицы, по которому будет устанавливаться связь. Такая таблица является главной (родительской) по отношению к создаваемой таблице. Для столбца главной таблицы, по значениям которого устанавливается связь, должно быть установлено ограничение PRIMARY KEY.

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

Ссылочная целостность устанавливает правила добавления и изменения данных в таблице при помощи внешнего ключа и соответствующего ему ограничения первичного ключа. Предложения ON UPDATE и ON DELETE для внешнего ключа определяют следующие правила изменения связанных данных:

NO ACTION – разрешает изменять (удалять) только те значения в главной таблице, которые не имеют соответствующих значений внешнего ключа в дочерней таблице. Данное правило действует по умолчанию;

CASCADE означает, что каждое значение внешнего ключа дочерней таблицы будет автоматически изменяться (удаляться) при модификации значения первичного ключа родительской таблице;

SET NULL означает, что в случае изменения (удаления) первичного ключа родительской таблицы, во всех ссылающихся строках дочерней таблицы значениям внешнего ключа будут автоматически присвоены значения NULL;

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

Дополним пример учебной базы данных «Университет», проектирование которой рассматривалось в гл. 4.3 таблицами ДИСЦИПЛИНА и ОБЩАЯ ВЕДОМОСТЬ. В таблицах 6,7 описана логическая структура таблиц.

Таблица 6

Логическая структура информационного объекта ДИСЦИПЛИНА

Таблица 7

Логическая структура информационного объекта ОБЩАЯ ВЕДОМОСТЬ

		Признак ключа	Формат поля
	Наименование	Признак ключа			Точность
Номер зачетной книжки	Зарегистрированный номер зачетной книжки студента		текстовый
Код дисциплины	Код дисциплины		Числовой	Длинное целое
			числовой

Приведем запросы на создание таблиц в соответствии с приведенной на рис. 35 инфологической моделью БД.

Рис. 35. Схема базы данных «Университет»

Как видно из схемы БД таблица ФАКУЛЬТЕТ является независимой таблицей, поэтому она создается первой. Запрос на создание таблицы с учетом описания логической структуры в табл. 4 (стр.61) будет иметь вид:

CREATE TABLE факультет

([номер факультета] tinyint PRIMARY KEY , [наименование факультета] char(50))

Таблица СПЕЦИАЛЬНОСТЬ также является независимой, ее создаем второй. При создании запроса использует описание логической структуры в табл. 5 (стр.62).

CREATE TABLE [специальность] (

[номер специальности] int PRIMARY KEY,

[наименование специальности] char (60),

[стоимость обучения] )

Таблица ГРУППА является зависимой от ФАКУЛЬТЕТА и СПЕЦИАЛЬНОСТИ таблицей. Используем таблицу 3 (стр. 61) при создании запроса и учтем, что столбцы номер факультета и номер специальности являются внешними ключами:

CREATE TABLE [группа] (

[номер группы] smallint PRIMARY KEY,

[номер специальности] int FOREIGN KEY REFERENCES специальность(номер специаль - ности )ON DELETE CASCADE ON UPDADE CASCADE,

[номер факультета] tinyint FOREIGN KEY REFERENCES факультет(номер факультета ) ON DELETE CASCADE ON UPDADE CASCADE, [номер курса] tinyint)

Таблица СТУДЕНТ является зависимой от ГРУППЫ таблицей. На основании данных таблицы 2 (стр. 60) составим запрос. Также учтем, что столбец номер группы является внешними ключами:

CREATE TABLE [студент] (

[номер группы] smallint NOT NULL FOREIGN KEY REFERENCES группа(номер группы ) ,

[фамилия] char(15) NOT NULL ,

[дата рождения] datetime NOT NULL ,

[коммерческий] bit NOT NULL ,

[имя регистрации] char(9))

Данные таблицы ОБЩАЯ ВЕДОМОСТЬ зависят от таблиц СТУДЕНТ и ДИСЦИПЛИНА. В этой таблице первичный ключ составной и каждый из столбцов первичного ключа является внешним ключом (см. табл. 7 и рис. 35).

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

CREATE TABLE [дисциплина] (

[код дисциплины] int PRIMARY KEY,

[наименование дисциплины] char(50))

Теперь можно создать запрос на создание таблицы общая ведомость. Так как первичный ключ таблицы является составным, то ограничение PRIMARY KEY должно задаваться на уровне таблицы. Для примера зададим ограничения FOREIGN KEY также на уровне таблицы. Запрос будет иметь вид:

CREATE TABLE [общая ведомость] (

[код дисциплины] int,

[номер зачетной книжки] char(8),

[оценка] NOT NULL , PRIMARY KEY ([код дисциплины],[номер зачетной книжки]), FOREIGN KEY ([код дисциплины]) REFERENCES [дисциплина] ([код дисциплины]), FOREIGN KEY ([номер зачетной книжки]) REFERENCES [студент] ([номер зачетной книжки]))

Изменение структуры таблицы

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

Удаление таблицы

Удаление таблицы выполняется при помощи команды DROP TABLE. Синтаксис команды:

DROP TABLE таблица

Например, запрос на удаление таблицы СТУДЕНТ имеет следующий вид:

DROP TABLE Студент

При удалении таблицы СЛЕДУЕТ учитывать связи установленные в базе данных между таблицами. Если на удаляемую таблицу с помощью ограничения целостности FOREIGN KEY ссылается другая таблица, то СУБД не разрешит ее удаление.

Создание индекса

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

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

Создание индекса выполняется командой CREATE INDEX:

CREATE INDEX

имя_ индекса ON имя_таблицы (столбец [,…])

где UNIQUE – указывает на то, что индекс должен хранить только уникальные значения.

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

Пример: Создать составной индекса в таблице СТУДЕНТ для полей Фамилия и Дата рождения

CREATE INDEX Ind_Fam ON

Студент(Фамилия, [Дата рождения] DESC)

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

Удаление индекса таблицы

Удаляет индекс из таблицы команда DROP. Синтаксис команды DROP на удаление индекса:

DROP INDEX индекс ON таблица

Перед удалением индекса из таблицы или самой таблицы ее необходимо закрыть.

Пример: Удалить индекс Ind_Fam из таблицы СТУДЕНТ

DROP INDEX Ind_Fam ON Студент