2.2.История развития и стандарты языка sql. Наборы команд sql и примеры операторов. Типы данных, управляющие конструкции языка Transact-sql.

История развития и стандарты.

SQL (Structured Query Language, структурированный язык запросов) – универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных.

1986 – первый вариант стандарта, принятый институтом ANSIи одобренныйISOв 1987г.

1992 – стандарт SQL 92 или SQL 2.

1999 – стандарт SQL 3. Добавлена поддержка регулярных выражений, рекурсивных запросов, поддержка триггеров, базовые процедурные расширения, нескалярные типы данных и некоторые объектно-ориентированные возможности.

2003 – SQL:2003. Введены расширения для работы сXML-данными, оконные функции, генераторы последовательностей и основанные на них типы данных.

2006 – SQL:2006. Функциональность работы сXML-данными значительно расширена. Появилась возможность совместно использовать в запросахSQLиXQuery.

2008 – SQL:2008. Улучшены возможности оконных функций, устранены некоторые неоднозначности стандартаSQL:2003

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

"+" Независимость от конкретной СУБД, наличие стандартов (поддержка совместимости), декларативность (пользователь производит только описание данных).

"–" Несоответствие реляционной модели данных, сложность, отступления от стандартов, сложность работы с иерархическими структурами

Наборы команд SQL.

Подмножества команд SQL (перечислены не все):

1) DDL – Data Definition Language – язык определения данных .

CREATETABLE- Создание новой таблицы в базе данных

DROPTABLE- Удаление таблицы из базы

ALTERTABLE- Изменение структуры таблицы или ограничения целостности таблицы

CREATEVIEW- Создание представления (виртуальной таблицы на основе запроса)

ALTERVIEW- Изменение структуры представления

DROPVIEW- Удаление представления

CREATEINDEX- Создание индекса (таблицы быстрого доступа к данным)

DROPINDEX- Удаление индекса

2) DMP – Data Manipulation Language – язык манипулирования данными.

DELETE- Удаление строк из таблицы

INSERT- Вставка строк в таблицу

UPDATE- Обновление значений полей в таблице

3) DQL – Data Query Language ­– язык запросов.

SELECT - Выборка строк из таблицы

4) TCS – Transactional Control Statement – cредства управления транзакциями.

COMMIT- Завершить транзакцию

ROLLBACK- Отменить транзакцию

SAVEPOINT- Сохранить промежуточную точку выполнения транзакции

5) Средства администрирования данных.

CREATEDATABASE- Создать новую базу данных

DROPDATABASE- Удалить базу данных

ALTERDATABASE- Изменить свойства и объекты базы данных

GRANT- Предоставить права доступа к объектам базы данных

REVOKE- Лишить прав доступа к объектам базы данных.

Transact-SQL

Версия языка SQL, используемого SQL Server, называется Transact-SQL, или T-SQL, в память о том, что в именно этом в диалекте SQL появились операторы управления транзакциями. Дополнительно T-SQL позволяет указывать оптимизатору запросов порядок выполнения операций и используемые индексы. Типы данных, управляющие конструкции языка и функции Transact - SQL .

Типы данных и преобразование типов.

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

SELECT * FROM systypes

1. Бинарные: требуется указывать числа в 16-ричном виде, например 0х FF . binary ( n ) – n определяет количество байт для хранения (до 8000 байт); под каждое значение будет выделено указанное количество байт вне зависимости от фактических размеров данных.varbinary ( n ) – n определяет максимальное количество байт для хранения (до 8000 байт); если массив данных m < n , то фактически отводится m +4 байта, если m > n , то данные усекаются до размера поля.image – хранятся бинарные данные размером до 2 31 -1 байт (~2 Гбайт), память выделяется страницами по 8000 байт. Использование этого поля оправдано, если требуется хранить очень длинные данные. Пример:DECLARE @ VA binary (10), @ VB varbinary (10) SELECT @ VA = 0 xFF , @ VB = 0 xAC SELECT 'Значение переменной VA : ' = @ VA , 'Значение переменной VB : ' = @ VB SELECT 'Длина VA : ' = DATALENGTH (@ VA ), 'Длина VB : ' = DATALENGTH (@ VB )

2. Символьные: интерпретируются в зависимости от того, какая кодовая страница была установлена при инсталляции сервера. char ( n ) – n <=8000, ASCII символы (1 байт), резервируется всегда n символов, если размер данных меньше, строка дополняется пробелами справа.varchar ( n ) – то же что и char ( n ), но резервируется максимум n символов. Если размер данных m < n , то дополнения пробелами не происходит.nchar ( n ) – n <=4000, Unicode символы (2 байта), остальное как в char ( n ).nvarchar ( n ) – n <=4000, Unicode символы (2 байта), остальное как в varchar ( n ). Примеры:DECLARE @ name nvarchar (25) SET @ name = N 'Д''Артаньян' SELECT @ name -- Что получится в каждом из столбцов? SELECT 1+1,'1'+'1','1'+'2'+3 Типичные функции для работы со строками:LEN (…) – возвращает длину строки в символах;LTRIM (…) иRTRIM (…) – удаляет пробелы в начале и в конце строкиSUBSTRING (…) – возвращает подстроку из строкиREPLACE (…) – заменяет подстроку указанным значением

3. Текстовые: позволяют хранить достаточно большие объёмы текстовой информации text – хранение ASCII текстовых блоков размером <=2 31 -1 символов (~2 Гбайт), выделяемых постранично.ntext – хранение Unicode текстовых блоков размером <=2 30 -1 символов (~1 Гбайт), выделяемых постранично. Некоторые функции для работы с текстовыми полями:SUBSTRING (…) – возвращает подстроку текстового поля;READTEXT (…) – считывает данные из текстового поля;DATALENGTH (…) – возвращает количество байт, занимаемых данными;

4. Целочисленные: следует учитывать, что результат вычисления выражения приводится к типу данных, имеющих максимальный размер из всех участвующих в выражении. Результатом деления целого на целое будет целое с отброшенной дробной частью. tinyint – диапазон данных от 0 до 255 (длина поля 1 байт).smallint – диапазон данных от -2 15 до 2 15 -1 (длина поля 2 байта).int илиinteger – диапазон данных от -2 31 до 2 31 -1 (длина поля 4 байта).bigint – диапазон данных от -2 63 до 2 63 -1 (длина поля 8 байт).

5. Нецелочисленные: с фиксированной и плавающей точкой. dec илиdecimal [( p [, s ])] илиnumeric [( p [, s ])] – диапазон от –(10^38 -1) до (10^38 -1). Содержит фиксированное количество знаков до и после точки: p – общее число знаков, s – число знаков после точки (0 <= s <= p <= 38). Длина поля от 5 до 17 байт.float [( n )] – число в виде мантиссы и порядка. Максимальный диапазон от -1.79 308 до 1.79 308 . Значение n определяет количество бит, используемых для хранения мантиссы ( n <=53). Длина поля 4 или 8 байт.double precision – частный случай float (53).real – частный случай float (24). Диапазон данных от -3.4 38 до 3.4 38 .

Примеры: DECLARE @VR real SET @VR=1.23E4 SELECT @VR, DATALENGTH(@VR) Не рекомендуется указ. нецелочисленные типы данных в конструкциях WHERE и для постр-ия индексов или первичных ключей, так как скорость обработки таких полей существенно ниже чем целочисл-х. Преобразование типов данных из числа в строку:

DECLARE @VR float, @VS varchar(20) SET @VR=3.14 SET @VS=STR(@VR,4,2)+'15' – простая функция преобразования типов SELECT @VS SET @VS=CAST($123.45 AS varchar(10)) – универсальная функция преобразования SELECT @VS SELECT CAST ('$54321' AS money) Взаимозаменяемой для функции CAST является функция CONVERT. Некоторые функции для работы с числовыми типами:ISNUMERIC (…) – проверяет, имеет ли выражение числовой тип данных (1, если да);RAND () – вычисляет случайное число с плавающей точкой в диапазоне [0…1];POWER (…) – возведение числа в степень ( SELECT power(2,8) );PI (…) – возвращает значение Пи.

7. Даты и времени: типы данных позволяют одновременно хранить время и дату. datetime – В первых 4 байтах хранится смещение относительно 1 января 1753 г. (до этого летоисчисление шло по Григорианскому и Юлианскому календарям) до 31.12.9999. Последние 4 байта – время после полуночи с точн. 3.33 мс.smalldatetime – диапазон от 1.01.1900 до 6.06.2079 с точностью до 1 мин. Дополнительные возможности для ввода и вывода дат предоставляет командаSET DATEFORMAT xxx , где xxx может быть: mdy , dmy , ymd , ydm , myd , dym .Примеры:SET LANGUAGE 'русский' DECLARE @ DV datetime SET @ DV ='21 октябрь 2003 23:19' SELECT @ DV ФункцияISDATE (<выражение>) возвращает 1, если <выражение> может быть правильно конвертировано в дату, и 0 в противном случае. Некоторые функции для работы с датой и временем:GETDATE () – возвращает текущее системное время;YEAR (…) – возвращает год из указанной даты;DATEADD (…) – добавляет к дате указанный временной интервалSELECT year(getdate()) DECLARE @Str1 char(10) IF DAY ( GETDATE ())<15 SET @ Str 1='первая' ELSE SET @Str1='вторая' SELECT 'Сейчас '+RTRIM(@Str1)+' половина месяца'

8. Денежные: поддерживается точность 4 знака после десятичной точки. money – диапазон от -922 337 203 685 477.5808 до [phone] 477.5807, длина 8 байт.smallmoney – диапазон от -214   748.3648 до +214   748.3647, длина 4 байта. Примеры : CREATE TABLE MyMoney ( ID bigint IDENTITY (1,1) PRIMARY KEY, Value money NULL ) INSERT MyMoney VALUES ($127.35) SELECT * FROM MyMoney Специальные : bit – данные принимают значения 0 / 1 / NULL. Память выделяется побайтно.timestamp – счетчик-идентификатор записей, уникальный в пределах одной базы данных (не имеет ничего общего с меткой времени). Длина 8 байт. Не может использоваться для объявления переменных. Не должен использоваться в составе первичного ключа.uniqueidentifier – глобально уникальный идентификатор записи ( GUID ). Идентификатор уникален в масштабе планеты. Представляет собой 16-байтовую последовательность, составляющуюся с помощью функцииNEWID () из MAC -адреса сетевой карты и внутреннего таймера процессора.sysname – предназначен для хранения имён объектов баз данных SQL Server – столбцов, таблиц, индексов, представлений, хранимых процедур и др.sql _ variant – позволяет в одном и том же столбце хранить значения любого другого доступного типа данных (за исключением text , ntext , image , timestamp , cursor , table и самого sql _ variant )

Пример:

DECLARE @Var1 int, @Var2 nvarchar(15), @Var3 datetime, @VA sql_variant SET @Var1=10 SET @Var2='Просто строка' SET @Var3='23.08.1969' SET @VA=@Var1+5 SELECT @VA SET @VA=@Var2 SELECT @VA SET @VA=@Var3 SELECT @VA ФункцияSQL_VARIANT_PROPERTYвозвращает информ-ю о природе данных, хранящихся под типомsql_variant.cursor – ссылка на объект базы данных – курсор. Подробнее о курсорах см. далее.table – временная таблица (массив). Может использоваться только для переменных и значений, возвращаемых функциями пользователя. Пример : DECLARE @VarTable TABLE

( Col1 int NOT NULL IDENTITY (1,1) PRIMARY KEY, Col2 nvarchar(15) ) INSERT INTO @VarTable (Col2) VALUES (' Первая строка ') SELECT * FROM @VarTable

1. Пользовательские типы данных: также возможно создавать в MS SQL Server . Для этих целей предусмотрена специальная хранимая процедура sp _ addtype . Первым параметром при вызове этой процедуры указывается имя пользовательского типа, вторым – имя системного типа, на основе которого строится пользовательский. Третий параметр указывает, разрешены или запрещены значения NULL .

Пример: sp_addtype nvc15, 'nvarchar(15)', NONULL

Управляющие конструкции Transact - SQL

Их весьма мало. К ним относятся:

•  Блок BEGIN … END . Выполняет группировку двух и более TSQL команд. Используется в конструкциях ветвлений, условий и циклов. Допускаются вложенные друг в друга блоки.

•  Конструкция IF … ELSE . Переход по условию. Допускается в каждой ветви исполнять одну команду или использовать блок BEGIN …END .

Пример:

PRINT GetDate()

IF Day(GetDate())=1 AND Month(GetDate())=1

BEGIN

PRINT 'Сегодня Новый Год!'

IF CURRENT_USER='dbo'

PRINT 'Администратор СУБД - это судьба...'

END

ELSE PRINT 'Сегодня точно не Новый Год.'

•  Конструкция CASE … END. Реализует множественное ветвление. Особенностью конструкции является то, что её можно использовать непосредственно в выражениях, например, при выполнении запроса.

Пример:

SELECT au_lname, au_fname,

CASE state

WHEN ‘CA' THEN ‘ Калифорния '

WHEN ‘UT' THEN ‘ Юта '

ELSE state

END FROM authors

WHERE au_lname LIKE 'O%'

•  Конструкция COALESCEвозвращает первое значение, не равное NULL , из перечня аргументов функции.

Пример:

DECLARE @I1 int, @C1 char, @C2 char, @C3 char

SET @I1 = 10

SET @C1 = 'A'

SET @C2 = @C1 + @C3

PRINT COALESCE(@C2,@I1)

Так как конкатенацией любой строки со значением NULL является NULL , то результатом выполнения этой последовательности команд будет значение 10 .

•  Конструкция WHILE… . Организует циклы в TSQL . Это единственный способ организовать циклы в этом языке. Цикл можно принудительно остановить, если в его теле выполнить команду BREAK , и перезапустить из любого места внутри тела цикла с помощью команды CONTINUE .

DECLARE @I1 int

SET @I1 = 1

WHILE @I1 < 8

BEGIN

PRINT ' Квадрат числа '+Str(@I1)+' есть '+Str(Square(@I1))

SET @I1 = @I1 + 1 END

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

Оператор SELECT .

Весь запрос SELECT разбивается на отдельные разделы, каждый из которых имеет своё назначение. Упрощённый вариант синтаксиса оператора SELECT:

1 SELECT[ALL|DISTINCT] < список вывода >

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

3 FROM <список таблиц и условий соединения>

4 [ WHERE <условие отбора или соединения> ]

5 [ GROUP BY <список полей группировки> ]

6 [ HAVING <условия, накладываемые на группу> ]

7 [ ORDER BY <список полей для сортировки вывода> ]

8 [ UNION <запрос на выборку для объединения>]…

<список вывода>::= { * |

[<имя таблицы> | <алиас>.] {<имя столбца> | <выражение>} [AS <алиас>] |

<имя столбца> = <выражение>} […n]

Символ звёздочка означает, что в результирующий набор включаются все столбцы из указанных исходных таблиц: SELECT * FROM publishers

Декартово произведение отношений: SELECT * FROM publishers, authors

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

SELECT ALL p.country FROM publishers AS p

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

SELECT DISTINCT state, contract FROM authors

Простейшие вычисления в разделе SELECT:

SELECT ' Название книги : ', title, yearpub-1992 FROM titles WHERE yearpub > 1992;

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

SELECT au_lname, au_fname, state FROM authors WHERE state<>'CA'

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

1) Предикаты сравнения: = , <> , < , > , >= , <= ;

SELECT * FROM authors WHERE 1=1

2) AND – соединение нескольких логических выражений;

SELECT title FROM titles WHERE yearpub>=1995 AND yearpub<=1997

3) OR – если одно из двух условий истинно, то результат True ;

SELECT title FROM titles WHERE yearpub<1995 OR yearpub>1997

4) NOT – отрицание, может ставиться непосредственноперед нижеследующими предикатами;

5) Предикат диапазона: Between A and B – принимает значение True, если сравниваемое значение лежит между A и В;

SELECT title FROM titles WHERE yearpub NOT BETWEEN 1995 AND 1997

6) Вхождение во множество: IN (<список значений>) – принимает True, если сравниваемое значение входит во множество заданных значений;

SELECT title FROM titles WHERE yearpub IN (1995, 1996, 1997)

7) Сравнение с образцом: LIKE. В шаблон могут входить специальные символы «_» – для обозначения любого одиночного символа, и «% » – для обозначения произвольной последовательности символов;

SELECT publisher, url FROM publishers WHERE publisher LIKE ‘%Wiley%'

8) Предикат сравнения с неопределённым значением: IS NULL .

SELECT publisher, “url not defined !” FROM publishers WHERE url IS NULL

Связь между таблицами с использованием раздела WHERE (стандарт SQL 89)

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

SELECT titles.title, titles.yearpub, publishers.publisher

FROM titles, publishers

WHERE titles.pub_id = publishers.pub_id AND titles.yearpub>1996

В данном запросе в разделе WHERE указаны условия связи и условия фильтрации данных. Связь между таблицами с использованием раздела FROM (стандарт SQL 2, внешние объединения).

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

1. [INNER] JOIN . Данный тип связи используется по умолчанию. Строки левой таблицы, для которых не имеется пары в правой таблице, в результат выборки не включаются. Строки правой таблицы, для которых не имеется пары в левой таблице, также в результат не включаются.

2. LEFT [ OUTER ] JOIN . Все строки левой таблицы включаются в результат выборки. При этом, если отсутствуют строки в правой таблице, то в соответствующих столбцах правой таблицы, включенных в результат запроса, будет установлено значение NULL . Строки правой таблицы, для которых не имеется пары в левой таблице, в результат не включаются.

3. RIGHT [ OUTER ] JOIN . Все строки правой таблицы включаются в результат выборки. Для соответствующих столбцов левой таблицы, включенных в запрос, устанавливается значение NULL . Строки левой таблицы, для которых не имеется пары в левой таблице, в результат не включаются.

4. FULL [ OUTER ] JOIN . В результат будут включены все строки как левой, так и правой таблицы.

5. CROSS JOIN – выражение эквивалентно просто запятой между таблицами.

Пример связи двух таблиц:

SELECT authors.au_lname, authors.au_fname, titleauthor.royalty

FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id

WHERE authors.state='CA'

Раздел GROUP BY позволяет выполнять группировку строк таблиц по определённым критериям. Типичным примером использования GROUP BY является суммирование однотипных значений. GROUP BY почти всегда используется вместе с функциями агрегирования. GROUP BY разделяет таблицу на группы, а функция агрегирования вычисляет для каждой из них итоговое значение.

Основные функции агрегирования:

AVG(<поле>) Среднее значение для указанного столбца или выражения

COUNT(<поле>) Количество строк, исключая NULL-строки в указанном столбце

COUNT(*) Общее количество строк, включая NULL-строки

MAX(<поле>) Максимальное значение в указанном столбце

MIN(<поле>) Минимальное значение в указанном столбце

SUM(<поле>) Сумма всех значений в указанном столбце

STDEV(<поле>) Статистическое стандартное отклонение для значений столбца

VAR(<поле>) Несмещенная оценка дисперсии величин указанного столбца

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

SELECT publishers.publisher, COUNT(titles.title)

FROM titles, publishers

WHERE titles.pub_id = publishers.pub_id

GROUP BY publisher

Правила использования группировок в запросах:

1) Функции агрегирования не работают со значениями NULL .

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

3) Раздел WHERE не допускает использования функций агрегирования.

Пример: подсчитать, сколько записей ввёл каждый из операторов по каждому региону за 2003 год.

SELECT max(subjects.name_rus) as [ субъект ], COUNT(*) AS [ количество записей ],

max(users.u_name) as [ оператор ]

FROM subjects, data, users

WHERE data.subject = subjects.subject and data.id_user = users.id_user and data.year=2003

GROUP BY data.subject, data.id_user

ORDER BY [ субъект ]

Простой запрос, использующий функцию агрегирования без группировки:

SELECT Count(*) FROM data

Раздел HAVING. Этот раздел практически аналогичен по назначению с разделом WHERE (горизонтальная фильтрация), однако используется для задания условий групповой фильтрации. В этом разделедопускается использование функций агрегирования.

Определим количество книг каждого издательства, исключая случаи единственного экземпляра.

SELECT publishers.publisher, COUNT(titles.title)

FROM titles, publishers

WHERE titles.pub_id = publishers.pub_id

GROUP BY publisher

HAVING COUNT (*)>1;

Др. пример: получить номера деталей, суммарное количество которых на складе превышает 400 шт.

SELECT number, SUM(volume) FROM warehouse

GROUP BY number HAVING SUM(volume)>400

Раздел ORDER BY предназначен для упорядочения набора данных, возвращаемых после выполнения запроса. Используются ключевые слова ASC (по возрастанию, используется по умолчанию) и DESC (по убыванию). При этом в сортировке могут участвовать столбцы, не входящие в раздел SELECT . Приоритет в сортировке по столбцам, указанным первыми.

SELECT data.* FROM data ORDER BY subject, msu, year

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

SELECT publisher, url FROM publishers

UNION

SELECT site , url FROM wwwsites

Использование вложенных запросов. Команда SELECT позволяет использовать подзапросы в предикатах главного (т.е. в разделах WHERE и HAVING ). Совместно с подзапросом можно использовать предикат EXIST, который возвращает истину, если вывод подзапроса не пуст. Задача: найти названия всех изданий, выпущенные издательством « Wiley » SELECTtitleFROMtitlesWHEREpub_idIN

(SELECT pub_id FROM publishers WHERE publisher='Wiley');

Более сложные задачи: даны отношения

Supplier s ( id _ supplier , name ) – поставщики (код поставщика, ФИО поставщика)

Supply ( id _ supplier , number ) – поставки (код поставщика, номер детали)

Components ( number , title ) – детали (номер детали, наименование детали).

Получить список поставщиков, поставляющих деталь с номером 222.

SELECT * FROM suppliers

WHERE EXIST

(SELECT * FROM supply

WHERE suppliers.id_supplier = supply.id_supplier AND supply.number = 222);

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

Простой пример: контроль ссылочной целостности вручную: SELECT*FROMdataWHEREitemNOTIN(SELECTitemFROMitems) Реализация реляционной алгебры средствами оператора SELECT (Реляционная полнота SQL ). Для того, чтобы показать, что язык SQL является реляционно полным, нужно показать, что любой реляционный оператор может быть выражен средствами SQL.

1 . Объединение : SELECT * FROM A UNION SELECT * FROM B	2. Пересечение : SELECT * FROM A INTERSECT SELECT * FROM B	3 . Разность : SELECT * FROM A EXCEPT SELECT * FROM B	4. Декартово произведение: SELECT A .*, B .* FROM A , B
5. Ограничение : SELECT * FROM A WHERE С ;	6. Проекция отношения : SELECT DISTINCT X, Y, Z FROM A	7. Соединение по условию: SELECT A .*, B .* FROM A , B WHERE С;	8. Деление : SELECT DISTINCT A.X FROM A WHERE NOT EXIST (SELECT * FROM B WHERE NOT EXIST (SELECT * FROM A A1 WHERE A1.X = A.X AND A1.Y = B.Y));

2.4. Понятия таблиц, представлений и индексов. Создание, модификация и удаление таблиц, представлений и индексов средствами SQL. Операторы манипулирования данными (INSERT, UPDATE, DELETE). Примеры использования.

Создание, удаление и модификация таблиц.

Создание таблицы: CREATE TABLE <имя_таблицы>

(<имя_столбца> {<тип_данных> | AS <выражение>}

{[ DEFAULT <значение>] | [ IDENTITY [(начальное значение, инкремент)]]}

{[NULL | NOT NULL] | [UNIQUE | PRIMARY KEY]}

[ REFERENCES <имя главной таблицы> [(<имя столбца>)]] , ...)

Имя таблицы должно быть уникальным в пределах БД, а имена столбцов – уникальными в пределах таблицы. Наиболее часто используется повторяющаяся конструкция <имя столбца> <значение>: NULL указывает на то, в этом столбце возможны неопределённые значения (естественно, такой столбец не может быть ключевым).

UNIQUE (ограничение целостности) указывает, что каждое значение в столбце является уникальным в пределах этого столбца. Для такого ограничения целостности автоматически создаётся индекс.

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

DEFAULT – указывается значение по умолчанию для данного столбца.

IDENTITY – создаётся столбец-счётчик. Только один столбец может быть счётчиком.

REFERENCES – определяет, что столбец будет служить внешним ключом для таблицы, указанной с помощью параметра <имя главной таблицы>. Столбцы, входящие во внешний ключ, могут ссылаться только на столбцы первичного ключевого ограничения или ограничения UNIQUE . Дополнительно ограничение может быть уточнено ключевыми словами ON DELETE { CASCADE | NO ACTION } ON UPDATE { CASCADE | NO ACTION }

Примеры создания таблиц (работа с которыми была рассмотрена выше):

CREATE TABLE authors (au_id INT PRIMARY KEY, author CHAR(25) NOT NULL);

CREATE TABLE publishers (pub_id INT PRIMARY KEY, publisher VARCHAR(255) NOT NULL, url VARCHAR(255) DEFAULT ‘ неизвестен ');

CREATE TABLE titles (title_id INT NOT NULL PRIMARY KEY, title CHAR(255) NOT NULL, yearpub INT, pub_id INT REFERENCES publishers(pub_id);

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

CREATE TABLE MyTable (FirstCol int, SecondCol int, ThirdCol AS (FirstCol+SecondCol)/2.0)

Удаление таблицы:DROP TABLE <имя_таблицы>

Модификация таблицы:Команда ALTER TABLE берёт на себя все действия по копированию данных во временную таблицу, удалению старой таблицы, созданию вместо неё новой таблицы с нужной структурой и последующим переписыванием в неё данных. Все эти действия происходят без участия пользователя, при этом установленные права доступа к таблице сохраняются.

Добавление столбцов: ALTER TABLE <имя_таблицы> ADD

(< возможное содержание аналогично содержимому в скобках для команды CREATE TABLE > ,...)

Удаление столбцов: ALTER TABLE <имя_таблицы> DROP (<имя столбца>, …)

Модификация столбцов: ALTER TABLE <имя_таблицы> MODIFY

(< возможное содержание аналогично содержимому в скобках для команды CREATE TABLE > ,...)

Пример : ALTER TABLE MyTable ADD DateCol datetime DEFAULT GETDATE() NOT NULL UNIQUE

Представления (Views) :

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

Представление может быть использовано: (1) для защиты конфиденциальной информации; (2) для упрощения доступа к информации и (3) сокращения времени доступа. Для таких целей представление может быть проиндексировано.

«+»: представление может выбирать данные (1) из таблиц текущей и любой другой базы данных, (2) из представлений текущей и любой другой базы данных (в том числе расположенные на разных серверах).

«--»: (1) представление не может ссылаться на временные таблицы, и невозможно создать временные представления; (2) в запросе, определяющим представление, нельзя использовать разделы ORDER BY и INTO ; (3) имеется ряд ограничений на изменение, добавление и удаление данных в представлении, созданном для нескольких таблиц.

Команды T-SQL , используемые для работы с представлениями:

CREATE VIEW <имя представления> AS < SELECT -запрос> – создание представления.

CREATE VIEW My_view AS SELECT au_lname, au_fname, address

FROM authors WHERE state='CA'

ALTER VIEW <имя представления> AS < SELECT -запрос> – изменение представления.

DROP VIEW <имя представления>, …– удаление представлений (одного или нескольких)

Хранимая процедура sp _ help возвращает информацию о различных параметрах представления, в качестве единственного аргумента которой указывается имя представления. Эта же процедура возвращает и об объектах других типов – таблицах, триггерах, индексах, хранимых процедурах и др.

Создание, удаление и модификация индексов.

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

К настоящему времени разработаны эффективные математические алгоритмы поиска данных в упорядоченной последовательности. Одним из таких алгоритмов является метод половинного деления. В простейшем варианте для поиска любого значения берётся начальное приближение в середине упорядоченной последовательности значений, и хранимая в ней величина сравнивается с искомым значением. Если искомая величина меньше выбранного значения, то дальнейшему делению пополам подвергается первая половина списка, иначе – вторая. Поиск заканчивается, как только достигнуто совпадение искомой величины и анализируемого значения.

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

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

CREATE [UNIQUE] INDEX <имя_индекса> ON <имя_таблицы> (<имя_столбца>,...)

Пример: CREATE INDEX au_index ON authors (au_id);

CREATE INDEX title_index ON titles (title_id);

Удаление индекса:

DROP INDEX <имя_индекса>