4.1. Системные базы данных
В MS SQL Server 2005 имеются следующие стандартные системные базы данных (см. рис. 1):
1) master
2) resource
3) model
4) msdb
5) tempdb
1) Master - главная база данных. Она содержит всю системную информацию СУБД SQL Server, в том числе общие метаданные, такие как сведения об учетных записях входа, конечных точках и связанных серверах, а также параметры конфигурации системы. В базе данных master также регистрируются все остальные базы данных и хранится информация о расположении их файлов. Кроме того, в ней хранится информация об инициализации СУБД SQL Server. Таким образом, если база данных master недоступна, запустить SQL Server невозможно.
2) База данных Resource доступна только для чтения, она содержит все системные объекты, входящие в SQL Server 2005. База данных Resource не содержит ни пользовательских данных, ни метаданных. Физически база данных Resource находится в файле Mssqlsystemresource.mdf. По умолчанию этот файл расположен в каталоге Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\. Нельзя перемещать и переименовывать этот файл! Иначе SQL Server не сможет запуститься.
3) База данных model используется в качестве шаблона для всех баз данных, созданных для экземпляра SQL Server. База данных model всегда должна существовать в системе SQL Server.
4) База данных msdb используется агентом SQL Server для создания расписания предупреждений и заданий.
5) База данных tempdb - это глобальный ресурс, доступный всем пользователям, подключенным к данному экземпляру SQL Server, в котором хранятся все временные таблицы и временные хранимые процедуры. Она также играет роль временного хранилища, например для рабочих таблиц, формируемых SQL Server. База данных tempdb повторно создается при каждом запуске SQL Server, так что система всегда начинает работу с чистой копией этой базы данных. Временные таблицы и хранимые процедуры удаляются автоматически при отключении. Поэтому в базе данных tempdb ничего не сохраняется от одного сеанса SQL Server до следующего.
4.2. Объекты базы данных
По заданиям этого пункта нужно сделать текстовый отчет, поместив его в вашу папку на сетевом диске (при необходимости можно вставить в него изображения экрана).
Объекты базы данных (таблицы, поля, ключи, представление, процедуры и т.д.) можно просмотреть на панели Обозреватель объектов (см. рис. 1) либо процедурой sp_help.
Вместе с MS SQL Server 2005 поставляется БД AdventureWorksDW в качестве примера, образца. Именно с ней мы и будем работать.
Задание 1. Просмотрите объекты БД AdventureWorksDW обоими способами. Ответьте на вопросы:
• в чем отличия указанных способов просмотра объектов?
• как обозначены первичные ключи таблиц для каждого из способов?
• как обозначены внешние ключи таблиц для каждого из способов?
Структуру БД можно просмотреть в ее диаграмме – в ней отображаются таблицы с атрибутами и связи в БД. При открытии диаграммы БД в окне свойств (справа на экране) отображаются основные свойства выделенной таблицы, поля или свойства. Если окно свойств не отображено на экране, его можно вывести через меню Вид / Окно свойств.
В этом окне свойств при выделении на диаграмме таблицы ее основные свойства отражены в разделе Идентификация (а также сведения о первичном ключе в разделе Конструктор баз данных), при выделении поля таблицы – в разделе Общие, при выделении связи – в разделе Спецификация таблиц и столбцов.
Задание 2. Просмотрите диаграмму БД AdventureWorksDW и свойства некоторых (любых) таблиц, полей и связей. Ответьте на вопросы:
• каковы основные свойства таблицы?
• каковы основные свойства поля?
• каковы основные свойства связи?
Просмотреть содержимое таблицы можно, выбрав ее в Обозревателе объектов и открыв через контекстное меню.
Задание 3. В БД AdventureWorksDW откройте таблицу DimEmployee (Сотрудники). Найдите и откройте таблицу, отражающую информацию о продажах.
4.3. Правила записи ссылок на объекты базы данных
Так как в запросах, процедурах и др. используются имена (ссылки) объектов БД (таблицы, поля и т.д.), рассмотрим, как принято в Transact-SQL записывать их. В общем случае все ссылки языка Transact-SQL на имена объектов БД могут быть четырехчастными именами в следующей форме:
[ Имя_Сервера ] . [ Имя_БД ] . [ Имя_Схемы ] . Имя_Объекта
При ссылке на конкретный объект нет необходимости всякий раз указывать сервер, базу данных и схему - компонент SQL Server 2005 Database Engine попытается определить этот объект. Однако если объект не удается найти, возвращается ошибка.
Чтобы пропустить промежуточные узлы, для обозначения их позиций используются точки. Если пропущены узлы в начале записи – точки не ставятся. Например,
Сервер.БД..Объект - имя схемы пропущено
Сервер..Схема.Объект - имя базы данных пропущено
Сервер...Объект - имя базы данных и имя схемы пропущены
БД..Объект - имя сервера и имя схемы пропущены
Объект - имена сервера, базы данных и схемы пропущены
4.4. Простая выборка данных
Целью любой системы управления базами данных является управление данными: ввод, изменение, удаление и выборка. При этом выборка данных является наиболее часто используемым аспектом управления данными. Выборка осуществляется с помощью команды SELECT.
Результатом выполнения данной команды является итоговый набор данных, состоящий из заголовков полей и записей данных. Этот набор должен содержать не менее одного поля, а также ноль или более записей.
Напомню общий синтаксис команда SELECT:
SELECT [ ALL | DISTINCT ] [ TOP n [ PERCENT ] ] СписокВыборки
FROM ИмяТаблицы
[WHERE УсловиеОтбора]
[GROUP BY ИменаПолейГруппировки]
[HAVING ОграничениеНаГруппы]
[ORDER BY ИменаПолейСортировки {ASC | DESC}]
По умолчанию команда SELECT возвращает все записи, включая дубликаты, что определяется ключевым словом ALL, которое может быть опущено.
Для получения набора уникальных неповторяющихся записей необходимо указывать ключевое слово DISTINCT.
Использование ключевого слова TOP предписывает выводить не все записи итогового набора, а только n первых. Можно выбирать не фиксированное количество записей, а определенный процент от всех строк – для этого указывается ключевое слово PERCENT.
После GROUP BY перечисляются поля, по которым производится группировка.
После HAVING можно наложить ограничение на группы (используется только при наличии группировки).
После ORDER BY перечисляются поля, по которым производиться сортировка. По умолчанию сортировка осуществляется по возрастанию, что соответствует зарезервированному слову ASC, которое может опускаться, для сортировки в убывающем порядке указывается – DESC. Вместо имен полей после ORDER BY можно указывать их порядковые номера в списке выборки или псевдонимы.
Список выборки
Список выборки может содержать следующие элементы:
* | ИмяПоля | Выражение [ AS Псевдоним ]
Для выборки всех полей из таблицы в списке выборки достаточно указать звездочку (*).
Ключевое слово AS позволяет заменить в итоговом наборе данных обычные имена полей псевдонимами. Имя псевдонима должно удовлетворять стандартным правилам именования объектов. При необходимости включить недопустимые символы, имя псевдонима заключается в квадратные скобки.
Например, рассмотрим получения списка заказчиков с указанием адреса электронной почты из таблицы DimCustomer БД AdventureWorksDW:
SELECT FirstName AS Имя,
LastName AS Фамилия,
EmailAddress AS [E-mail]
FROM AdventureWorksDW..DimCustomer
Если БД AdventureWorksDW является текущей, то ее явное указание может быть опущено. Поля данных будут представлены пользователю в порядке, определенном в списке выборки.
Задание 4. Отобразите информацию об именах, фамилиях, днях рождениях и телефонах сотрудников так, чтобы имена полей были написаны по-русски.
Элемент Выражение в списке выборки задет выражение, которое включается в итоговый набор данных. Выражение может содержать константы, имена полей, функции и их комбинации. По умолчанию имя колонки с выражением не определено, поэтому можно указать псевдоним.
Например, список сотрудников с указанием фамилии и первого символа имени в одном поле, а также идентификационного номера может быть получен в результате запроса:
SELECT LastName+’ ‘+Substring(FirstName,1,1)+’.’ AS [Имя сотрудника], EmployeeKey
FROM DimEmployee
Задание 5. Сконструируйте запрос, возвращающий отсортированный по алфавиту список сотрудников с указанием их фамилии с инициалами имени и «среднего имени» в одном поле, а также даты рождения (в формате dd/mm/yyyy).
Подсказка: для вывода даты в другом формате используйте функцию convert, преобразуйте дату в текстовый тип с указанием стиля – номера стилей см. в справке по этой функции.
Условия отбора
Условие отбора определяет критерий отбора записей, включаемых в итоговый набор. В результат будут включены только те строки, которые соответствуют наложенным условиям.
Условие может включать выражения, образованные с помощью операторов сравнения или логических операторов. Условия могут также объединяться и с помощью логических операндов AND, OR и NOT.
Например, чтобы получить список товаров, цена которых находится в диапазоне от $12 до $20, необходимо выполнить любым из следующих вариантов:
SELECT EnglishProductName, ListPrice
FROM DimProduct
WHERE (ListPrice>=12) and (ListPrice<=20)
ORDER BY 2
SELECT EnglishProductName, ListPrice
FROM DimProduct
WHERE ListPrice BETWEEN 12 and 20
ORDER BY 2
Задание 6. Определите список товаров, для которых не указан класс, но указан размер.
Для поиска по шаблону символьных строк используется логический оператор LIKE, который чаще всего используется в ситуациях, когда не известно точное совпадение.
В шаблоне могут используются следующие универсальные символы: % – подразумевает любую строку, состоящую из 0 или более символов; _ – ровно один символ; [ ] – любой символ из заданного множества (например, [adfh]) или диапазона (например, [0-9]), [^] – любой символ не попадающий в заданный диапазон или множество.
Например, чтобы вывести фамилии сотрудников, принятых на работу в 2000 году, можно выполнить запрос:
SELECT LastName, StartDate
FROM DimEmployee
WHERE StartDate LIKE '%2000%'
Задание 7. Создайте запрос, выводящий список велосипедных товаров белого цвета.
Задание 8. Создайте запрос, выводящий список наименований товаров, для которых значения альтернативного ключа начинаются с букв A, B, или C (с использованием перечисления множества значений).
Для определения соответствия выражения одному из перечисленных в заданном списке значений применяется логический оператор IN. Данный оператор всегда может быть записан и в виде группы условий, объединенных операндом OR.
Например, с помощью следующего запроса может быть получен список городов Германии и Франции из таблицы DimGeography:
SELECT City, EnglishCountryRegionName
FROM DimGeography
WHERE EnglishCountryRegionName IN (‘Germany’, ‘France’)
Однако в список значений нельзя включать неопределенное значение NULL, для работы с такими значениями используется функция выборки IS NULL.
Задание 9
Измените запрос из предыдущего примера так, чтобы выводился список всех городов Германии и Франции (без повторений) в алфавитном порядке.
Задание 10
Определите 10 самых дорогих товаров. Решение оформить в виде хранимой процедуры.
Грамотная косметология: обсуждение, практические советы, обмен опытом
горящие туры на Кипр
Куба
