Базы данных
База данных в SQL Server состоит из коллекции таблиц, в которой хранится особый набор структурированных данных. Таблица содержит коллекцию строк, также называемых записями или кортежами, и столбцов, также называемых атрибутами. Каждый столбец в таблице предназначен для хранения конкретного типа данных, например дат, имен, денежных сумм или чисел.
Основные сведения о базах данных
Базы данных SQL Server хранятся в файловой системе в виде файлов. Файлы могут быть объединены в группы файлов. Дополнительные сведения о файлах и файловых группах см. в разделе Database Files and Filegroups.
При получении доступа к экземпляру SQL Server пользователи идентифицируются согласно имени входа. При получении доступа к базе данных пользователи идентифицируются как пользователи базы данных. Имя пользователя базы данных может быть основано на имени входа. Если автономные базы данных включены, то пользователь базы данных может быть создан не на основе имени входа. Дополнительные сведения о пользователях см. в статье CREATE USER (Transact-SQL).
Пользователь, имеющий доступ к базе данных, может получить разрешения на доступ к объектам этой базы данных. Хотя разрешения и могут быть предоставлены отдельным пользователям, рекомендуется создавать роли базы данных, добавляя при этом пользователей базы данных к соответствующим ролям, а затем предоставлять разрешения ролям. Предоставление разрешений ролям, а не пользователям позволяет легко и понятно управлять процессом распределения разрешений, несмотря на постоянное изменение и рост числа пользователей. Дополнительные сведения о ролях и разрешениях см. в разделах CREATE ROLE (Transact-SQL) и Субъекты (ядро СУБД).
Работа с базами данных
Sql database что это
С другой стороны, производители СУБД вводят в SQL дополнительные возможности, не поддерживаеме стандартами, нарушая тем самым совместимость SQL для разных СУБД.
DDL и DML
Типы данных
Каждый столбец и домен, из которого берутся значения в этот столбец, имеют свой тип данных. В стандарте SQL определены следующие семь основных типов данных:
Каждый основной тип имеет один или несколько подтипов.
Разработчики СУБД нарушают стандарты SQL. Осбенно сильно отличаются от стандартных типы данных. Более того, наборы типов данных в разных СУБД тоже отличаются друг от друга. Например, в СУБД MS SQL Server и MS ACCESS есть тип данных MONEY, а в Oracle и в MySQL отсутствует. Различие типов данных является одной из причин несовместимости различных СУБД и возникающих трудностей при переносе базы данных из одной СУБД в другую. В таблице 1 перечислены типы данных СУБД ACCESS.
Таблица 1. Типы данных в ACCESS
Структура реляционной базы данных
Для работы с базами данных создаются информационные системы, состоящие из прикладных программ, СУБД и базы данных. Пользователи, как правило, называют базой данных всю информационную систему. Такая терминологическая путаница очень мешает в ситуациях, когда пользователь пытается рассказать разработчику о неполадках в информационной системе.
Описание структуры базы данных называется метаданными и хранится в базе вместе с основными данными.
Нотация Бэкуса-Наура
Нотация, или форма, Бэкуса-Наура используется для описания синтаксиса операторов языков программирования. В ней применяются следующие обозначения:
Более полно смысл перечисленных обозначений станет ясен после их использования для описания операторов.
Операторы языка описания данных
Перечислим основные операторы языка SQL, с помощью которых создаётся и изменяется cтруктура базы данных:
Оператор CREATE TABLE
Пример. Создание таблицы из четырёх столбцов.
Ограничения столбца имеют следующие значения:
Ограничение может иметь имя, которое задаётся перед ограничением ключевым словом CONSTRAINT:
Пример создание двух связанных таблиц. Рассмотрим сущности писатель и книга. Для простоты будем считать, что у книги может быть только один автор. Тогда между сущностями писатель и книга существует связь типа один ко многим. Для отображения этой связи в реляционной модели создадим в таблице pisatel первичный ключ Id_p, а в таблицу kniga добавим внешний ключ Id_pisatel. Кроме того, зададим адрес писателя по умолчанию и проверку числа страниц в книге. Соответствующие запросы будут выглядеть так:
В таблице kniga поле naim = потенциальный ключ.
Пример составного первичного ключа. В таблице tovar пара значений наименование, изготовитель должна быть уникальной.
Оператор ALTER TABLE
Оператор ALTER TABLE служит для изменения структуры существующих таблиц. В реляционной базе данных существует множество часто неявных, скрытых связей, которые при изменении структуры таблиц могут быть нарушены. Поэтому применять оператор ALTER TABLE следует крайне осторожно.
Синтаксическая формула оператора ALTER TABLE:
Пример добавления столбца. К таблице заказ добавляется столбец цена, имеющий тип MONEY.
Оператор DROP TABLE
Синтаксическая формула оператора DROP TABLE:
Оператор CREATE INDEX
Синтаксическая формула оператора CREATE INDEX:
Пример. Содаётся индекс kniga_ind для таблицы kniga.
Оператор DROP INDEX
Синтаксическая формула оператора DROP INDEX:
Оператор CREATE VIEW
В контексте реляционных баз данных термин VIEW переводится на русский язык как представление. Синтаксическая формула оператора CREATE VIEW:
Оператор CREATE VIEW необычен тем, что содержит в себе оператор SELECT, принадлежащий языку манипулирования данными. Заметьте, что в синтактической формуле используется не имя представления,что было бы логично, а имя таблицы. Когда в каком-либо запросе языка манипулирования данными встречается имя таблицы, объявленное в операторе CREATE VIEW, то выполняется запрос SELECT, объявленный в том же CREATE VIEW. Результаты этого запроса рассматриваются как обыкновенная таблица!
Данные могут извекаться в представление из одной или из некольких настоящих таблиц. Источником формирования представления может быть другое представление. В том случае, когда предсавление связано только с одной таблицей, оно может использоваться для изменения (оператор UPDATE), удаления (оператор DELETE) и добавления (оператор INSERT) данных в породившую его таблицу.
Предложение CHECK OPTION служит для проверки нарушения целостности данных при использовании представления в операторах INSERT и UPDATE. CASCADED распространяет проверку на все уровни вложенности представления, а LOCAL ограничивает проверку только одним уровнем.
Пример. Создаётся представление, в которое отбираются только дешёвые товары из таблицы товар
Оператор DROP VIEW
Синтаксическая формула оператора удаления представления DROP VIEW:
RESTRICT вызывает сообщение об ошибке при существовании ссылки на это представление.
При задании CASCADE удаляются все объекты, в которых есть ссылки на удаляемое представление.
Оператор GRANT
Оператор GRANT служит для назначения прав (привилегий) пользователям. Синтаксическая формула оператора GRANT:
GRANT OPTION даёт возможность передавать права другим пользователям
PUBLIC предоставляет указанные в операторе GRANT права всем пользователям.
1. Пользователю Петрову предоставляется право добавлять данные в таблицу книга.
2. Пользователям Lada и Genja предоставляется право просматриваь таблицу книга и добавлять в неё данные.
3. Пользователям Andre и Peter предоставляется право обновлять поле naim в таблице книга.
Оператор REVOKE
Оператор REVOKE служит для отмены привилегий. Синтаксическая формула оператора REVOKE:
В состав оператора языка SQL могут входить другие операторы языка SQL, математические операторы (арифметические, логические, операторы отношения) и функции, строковые операторы и функции.
Операция над данными строкового типа
Конкатенация данных строкового типа и сложения числовых данных обозначаются одним значком «+», но дают разный результат, например,
Оператор SELECT
Общая синтаксическая формула оператора SELECT очень сложна и мало подходит для начинающего изучать язык SQL. Поэтому начнём с самых простых частных случаев.
Выборка всех строк
Запрос на выборку всей таблицы имеет самый простой вид:
Пример. Из базы выбирается вся таблица заказ.
| N_заказа | изделие | фирма | к_во |
|---|---|---|---|
| 1 | Ноутбук | Альфа | 2 |
| 2 | Мышка | Бета | 4 |
| 3 | Принтер | Альфа | 1 |
| 4 | флешка | Гамма | 5 |
| 5 | Мышка | Бета | 1 |
| изделие | фирма |
|---|---|
| Ноутбук | Альфа |
| Мышка | Бета |
| Принтер | Альфа |
| флешка | Гамма |
| Мышка | Бета |
Из примера видно, что реальная таблица реляционной базы данных в отличие от теоретического отношения может содержать одинаковые строки. Чтобы исключить дублирование строк, нужно вставить DISTINCT после SELECT:
| изделие | фирма |
|---|---|
| Ноутбук | Альфа |
| Мышка | Бета |
| Принтер | Альфа |
| флешка | Гамма |
Псевдонимы. Названия столбцов часто неудобны при просмотре результатов запроса. Для замены имён столбцов синонимами используется следующий синтаксис:
| Номер заказа | изделие | фирма | количество |
|---|---|---|---|
| 1 | Ноутбук | Альфа | 2 |
| 2 | Мышка | Бета | 4 |
| 3 | Принтер | Альфа | 1 |
| 4 | флешка | Гамма | 5 |
| 5 | Мышка | Бета | 1 |
Вычисляемые поля. Вместо имени поля можно использовать арифметическое или строковое выражение. Подсчитаем стоимость товаров в таблице затраты, изменив единицу измерения стоимости на тыс. руб.
| Товар | Цена | к_во |
|---|---|---|
| Стол | 12000 | 5 |
| Стул | 1700 | 20 |
| Шкаф | 18500 | 2 |
Запрос выглядит так:
В запросе использована функция str(числовое выражение), преобразующая выражение в строковый тип. Результат запроса
| Товар | Цена | Количество | Стоимость |
|---|---|---|---|
| Стол | 12000 | 5 | 60 тыс. руб. |
| Стул | 1700 | 20 | 34 тыс. руб. |
| Шкаф | 18500 | 2 | 37 тыс. руб. |
Агрегатные функции служат для вычисления характеристик таблицы в целом. Всего агрегатных функций пять: count, sum, max, min и avg.
| К-во товаров | Стоимость всех товаров | Макс. цена | Мин. цена | Средняя цена |
|---|---|---|---|---|
| 3 | 131000 | 18500 | 1700 | 10733 |
Выборка строк, удовлетволяющих условию
Для выборки из таблицы только тех строк, содержимое которых удовлетворяет заданному условию, дополним синтактическую формулу предложением WHERE:
1. Использование операций сравнения.
Выберем из таблицы Затраты строки, в которых цена больше 5000.
| Товар | Цена | Количество |
|---|---|---|
| Стол | 12000 | 5 |
| Шкаф | 18500 | 2 |
В этом примере цена имеет числовой тип. Можно сравнивать и текстовую (строковую) информацию. Вся текстовая информация хранится в памяти ЭВМ в виде последовательности цифровых кодов символов. Таблицы кодирования составлены так, что следующая буква алфавита имеет больший код, чем предыдущая, то есть, А клиенты все строки с фамилиями, начинающимися на буквы с А по И включительно, нужно выполнить следующий запрос
Рассмотрим два примера использования в СУБД ACCESS функций СDATE и DatePart.
Функция СDATE( дата в текстовом виде) преобразует дату в тип DATE. Выберем из таблицы студенты все строки с датами родения позже 31.12.1994.
Функция DatePart(часть даты, дата) возвращает часть даты. Параметр часть даты может принимать следующие значения:
Пример выделения из даты года и месяца. Из таблицы клиенты выбираются ФИО и Год рождения клиентов, родившихся в мае.
| ФИО | Дата рождения |
|---|---|
| Сергеева Е.И. | 16.02.1994 |
| Петров А.П. | 16.05.1995 |
| Петров В.П. | 07.10.1989 |
| Иванов С.В. | 21.05.1956 |
| ФИО | Год рождения |
|---|---|
| Петров А.П. | 1995 |
| Иванов С.В. | 1956 |
2. Применение логических операторов AND, OR, NOT
Выберем из таблицы клиенты все клиентов, родившихся между 1990-м и 1999-м годами или родившихся не в мае.
| ФИО | Дата рождения |
|---|---|
| Сергеева Е.И. | 16.02.1994 |
| Петров А.П. | 16.05.1995 |
| Петров В.П. | 07.10.1989 |
3. Применение специальных операторов сравнения IN, BETWEEN, LIKE, IS NULL
Перечисленные операторы иначе называют предикатами. Предикаты IN и BETWEEN служат для сокращении записи условия выборки.
Предикат IN используется в тех случаях, когда в условиях выборки нужно задать не диапазон, а список значений. Выберем из таблицы Заказы заказы для городов БЕРН, Женева и Грасс.
Предикат BETWEEN, наоборот, используется для задания диапазона значений, включая границы диапазона. Выберем заказы с ценой от 200 до 1000.
При задании диапазона дат необходимо преобразовать даты из тектового типа в тип DATE’.
Можно задавать текстовый диапазон. В результате выполнения запроса
выберутся заказы для стран на буквы от А до И включительно.
Предикат LIKE служит для поиска текстовой инфомации по шаблону. Заменим в предыдущем операторе предикат BETWEEN на LIKE, используя шаблоны, принятые в СУБД ACCESS.
В стандарте SQL есть только два символа для поиска по шаблону:
Примеры шаблонов в ACCESS
Если пользователи ничего не записали в какое-либо поле, то считается что оно хранит признак пустоты NULL. Для того, чтобы найти строки, в которых заданное поле хранит NULL, нужно написать условие
Для задания обратного условия нужно написать
Предложения GROUP BY и HAVING
Предложение GROUP BY служит для разбиения всех строк таблицы на группы и последующего применения к каждой группе агрегатных функций. Например, для фирмы, занимающейся доставкой товаров, представляет интерес не только общее количество заказов, но и распределение заказов по странам. Такие данные получаются в результате выполнения запроса
| Страна получателя | Количество заказов | Сумм стоим доставки | Макс стоим доставки |
|---|---|---|---|
| Австрия | 39 | 6 637,24р. | 789,95р. |
| Аргентина | 16 | 598,58р. | 217,86р |
| Бельгия | 20 | 1 341,89р. | 424,30р. |
| . | . | . | . |
| Франция | 80 | 4 276,20р. | 487,38р. |
| Швейцария | 18 | 1 368,53р. | 232,42р. |
| Швеция | 36 | 2 992,81р. | 328,74р. |
Предложение HAVING служит для отбора групп, удовлетворяющих условию. В условие могут входить агрегатные функции. Перепишем предыдущий запрос, включив в него условие отбора только тех стран, максимальная стоимость доставки в которые больше четырёхсот рублей.
| Страна получателя | Количество заказов | Сумм стоим доставки | Макс стоим доставки |
|---|---|---|---|
| Австрия | 39 | 6 637,24р. | 789,95р. |
| Бельгия | 20 | 1 341,89р. | 424,30р. |
| Бразилия | 83 | 4 880,19р. | 890,78р. |
| Германия | 117 | 11 341,09р. | 1 007,64р. |
| Ирландия | 19 | 2 755,24р. | 603,54р. |
| США | 122 | 13 771,29р. | 830,75р. |
| Франция | 80 | 4 276,20р. | 487,38р. |
Общая структура однотабличного запроса имеет вид:
| Страна получателя | Количество заказов | Сумм стоим доставки | Макс стоим доставки |
|---|---|---|---|
| Германия | 53 | 4 148,10р. | 810,05р. |
| США | 51 | 5 778,58р. | 830,75р. |
| Бразилия | 35 | 2 513,43р. | 890,78р. |
| Австрия | 14 | 2 311,57р. | 789,95р. |
| Ирландия | 9 | 1 890,41р. | 603,54р. |
| Бельгия | 9 | 793,16р. | 424,30р. |
Предложени ORDER BY
Многотабличные запросы
На практике часто с помощью одного запроса данные собираются сразу из нескольких таблиц. Некоторые СУБД позволяют в одном запросе делать выборку из нескольких баз данных.
Рассмотрим простейший двухтабличный запрос. Необходимо выбрать из базы данных названия всех городов вместе с названиями стран, в которых они находятся. Для этого используем связанные между собой таблицы Города и Страны
В таблице Города специально не указана страна для Лондона, хотя по населению можно догадаться, что это не столица Англии. Стране Чили не соответсствует ни один город.
В запросе для сокращения записи использованы псевдонимы имён таблиц. Связь между таблицами задаётся в предложении WHERE. В одной строке таблицы результатов объединяется строка с внешним ключом (город) со строкой с равным первичным ключом. Лондон не вошёл в таблицу результатов, так как у него не указан внешний ключ. Если в этом запросе заменить INNER на LEFT (левое внешее соединение), то будут выбраны все строки, обеих таблиц, удовлетворяющие условию соединения, и из левой таблицы строки, которым нет соответствия в правой таблице. В нашем примере к таблице результатов добавится одна строка. Запрос с LEFT JOIN
|