Тема: "Создание таблиц базы данных"
Цель работы: -усвоить способы создания таблиц, умолчаний, правил, ограничений БД средствами СУБД MS SQL Server 2008;
Краткие теоретические сведения
Поддерживаемые типы данных
Таблица - это объект базы данных, который хранит данные в виде совокупности строк и колонок. Таблица определяется содержащимися в ней колонками. Чтобы задать таблицу, вы должны решить, сколько колонок она будет иметь и данные каких типов будут в ней храниться. При задании типа данных у колонки задаются следующие атрибуты.
• Категория (тип) данных, которые могут содержаться в колонке (например, символьные данные, целые числа или изображения).
• Размер (длина) данных, хранимых в колонке.
• Точность чисел (этот атрибут применяется только для числовых типов данных), т.е. количество цифр, содержащихся в числах.
• Масштаб чисел (этот атрибут применяется только для числовых типов данных), т.е. количество цифр, способных помещаться справа от десятичной точки.
Тип данных | Описание | Сколько места занимает |
bigint | Восьмибайтное целое число (полное целое). Позволяют использовать целые числа от -263 до 263-1 | 8 байт |
binary[(n)] | Двоичные данные фиксированной длины, состоящие из n байтов, где n может принимать значение от 1 до 8000. Тип binary следует применять, когда предполагается, что элементы данных, хранимые в колонке, будут иметь примерно одинаковый размер | n +4 байта |
bit | Целый тип данных, которые могут иметь значения 1, 0 или NULL. Колонки типа bit не могут иметь индексов | 1 байт для таблиц с 8-битными колонками |
char(n) | Символьные данные (не в кодировке Unicode) фиксированной длины, имеющие длину n символов, где n может принимать значение от 1 до 8000. Значения данных с длиной короче заданной, добавляются пробелами до указанной длины | n байт |
cursor | Ссылка на курсор. Может применяться только для переменных и параметров хранимых процедур | 1 байт |
date | Сохраняет значения дат за период с 1 января 1 года по 31 декабря 9999 года. Понимает значение даты в формате стандарта ANSI (YYYY-MM-DD) | 3 байта |
datetime | Датам и время от 1 января 1753 года до 31 декабря 9999 года, с точностью до трех сотых секунды. Не зависит от часового пояса | 8 байт |
datetime2 | Обновленный вариант datetime. Поддерживает более крупные диапазоны дат и большую точность определения времени. Не зависит от часового пояса | от 6 до 8 байт |
datetimeOffset | Аналогичен типу данных datetime, но дополнительно требует задание смещения в пределах от -14:00 до +14:00 по отношению к всемирному скоординированному времени. | от 8 до 10 байт |
decimal [(p, [s])] или numeric [(p, [s])] | Числа фиксированной точности и фиксированного масштаба от -1038 до 1038-1. (Тип данных numeric является синонимом типа данных decimal.) Точность р определяет общее количество цифр, которые могут храниться в числе, и слева, и справа от десятичной точки. Масштаб определяет максимальное количество цифр, которые могут храниться справа от десятичной точки. Масштаб не может быть больше точности. Минимальная точность равна 1, а максимальная равна 28 | от 5 до 17 байт, в зависимости от точности |
float[(n)] | Числовые данные с плавающей точностью, которые могут находиться в диапазоне от -1.79x10 +308 до 1.79xl0+308. Параметр и определяет количество битов, применяемых для хранения мантиссы числа с плавающей точкой и может иметь значение от 1 до 53. Является синонимом для типа real) | от 4 до 8 байт, в зависимости от точности |
hierarchyID | Позволяет задавать информацию о положении в иерархии. Размер внутреннего представления зависит от количества и средней глубины узлов в иерархии | Определяется по особому принципу |
image | Устаревший тип данных, применяется для совместимости с предыдущими версиями СУБД. Вместо него рекомендуется использовать тип данных varchar (max). Элемент данных, хранящийся в колонке типа image, является указателем на местоположение данных типа image. Эти данные хранятся отдельно от данных в таблицах | 16 байт для указателя |
integer или int | Целочисленные данные (полное целое) от -231 (-2 147 483 648) до 231 -1 (2 147 483 647) | 4 байта |
money | Данные для денежных величин от -263 (-922337203685477.5808) до 263- 1 (922 337 203 685 477. 5807), с точностью до одной десятитысячной доли от денежной единицы | 8 байт |
nchar [(n)] | Символьные данные в кодировке Unicode фиксированной длины, имеющие длину п символов, где п может принимать значение от 1 до 4000. Кодировка Unicode применяют по 2 байта на один символ данных и поддерживает все символы, имеющиеся в мире | 2 байта помножить на количество введенных символов |
ntext | Устаревший тип данных, применяется для совместимости с предыдущими версиями СУБД. Вместо него рекомендуется использовать тип данных nvarchar (max). | |
nvarchar | Данные в кодировке Unicode переменной длины, длиной до n символов, где n может принимать значение от 1 до 4000. Помните, что для одного символа в кодировке Unicode потребуется 2 байта; кодировка Unicode поддерживает все символы, имеющиеся в мире | 2 байта помножить на количество введенных символов |
real | Числовые данные с плавающей точностью, которые могут находиться в диапазоне от -3.40x10+38 до 3.40х10+38. Синонимом для типа real является float(24) | 4 байта |
SQL_variant | Представляет собой контейнер, который обеспечивает хранение большинства других типов данных. Используется, когда необходимо представить в одном столбце или функции несколько разных типов данных | Определяется по особому принципу |
small datetime | Данные для даты и времени от 1 января 1900 года до 6 июня 2079 года, с точностью до одной минуты (точность меньше, чем у типа данных datatime). | 4 байта |
smallint | Целочисленные данные от -215 (-32768) до 215-1 (32767) | 2 байта |
small money | Данные для денежных величин от -214 748.3648 до 2 14 748.3647, с точностью до одной десятитысячной доли от денежной единицы | 4 байта |
table | Аналогично использованию временной таблицы - такое объявление типа содержит список колонок и типов данных. Может применяться для задания локальных переменных или для возвращения результатов функций, определенных пользователем | Зависит от определения таблицы |
text | Устаревший тип данных, применяется для совместимости с предыдущими версиями СУБД. Вместо него рекомендуется использовать тип данных varchar (max) | 16 байт для указателя |
time | Сохраняет значения времени с точностью, выбранной пользователем | от 3 до 5 байт |
tinyint | Целочисленные данные в диапазоне от 0 до 255. | 1байт |
unique-identifier | Хранит 16-байтной двоичное значение, являющееся глобальным уникальным идентификатором (GUID) | 16 байт |
varbinary | Двоичные данные переменной длины, состоящие из п байтов, где п может принимать значение от 1 до 8000. Применяйте тип varbinary, если предполагаете, что элементы данных, хранимые в колонке, будут сильно отличаться по своим размера | Фактическая длина введенных данных плюс 4 байта |
varchar [(n)] | Символьные данные переменной длины не в кодировке Unicode, длиной в п символов, где п может принимать значение от 1 до 8000 | Факт.длина введенных данных |
XML | Определяет символьное поле как содержащее данные XML | Определяется по особому принципу |
Создание пользовательских типов данных
Пользовательские типы данных (типы данных задаваемые пользователями) являются индивидуально настроенными системными типами данных. Такая настройка полезна, когда вы имеете несколько таблиц, в колонках которых должны храниться данные одинаковых типов, а вы хотите генерировать точное соответствие колонок каждой из таблиц по типу, длине и возможности применения null-значений. Создав тип данных с осмысленным именем, вы упростите себе программирование и согласованность данных в ваших таблицах. Например, в вашей базе данных есть таблица, в которой хранятся сведения личного характера о сотрудниках организации и есть другая таблица, хранящая сведения о доходах и расходах сотрудников вашей организации. Чтобы гарантировать, что в данные в колонках обеих таблиц, содержащих сведения о табельных номерах сотрудников будут иметь одинаковый тип данных, можно создать пользовательский тип данных и присвоить его обеим колонкам. При создании типа данных вы должны задать следующую информацию: имя типа данных; системный тип данных, на основании которого создается новый тип данных; возможность хранения null-значения.
Создание пользовательских типов данных с помощью Management Studio
- Находясь в Management Studio и нажимая на значок-плюс рядом с папкой, раскройте группу SQL Server, а затем сервер
- Раскройте папку Databases, а затем вашу базу данных. В ней найдите папку Programmabiliti и раскройте ее. В ней найдите папку Types и раскройте ее.
- Нажмите правой кнопкой мыши на User Defined Data Types и выберите New User Defined Data Types в контекстном меню.
- Появится окно свойств пользовательского типа данных. В поле name введите имя нового типа данных, затем задайте системный тип и длину вашего пользовательского типа данных. Если ваш тип данных позволяет использовать null-значения, то установите флажок Allows Nulls. Если ваш тип данных должен использовать какие-либо предопределенные правила и значения, то выберите их в соответствующих полях со списками. Чтобы сохранить ваш тип данных, нажмите «Ok»
Если вы создадите пользовательский тип данных в пользовательской БД и захотите посмотреть новый тип через Management Studio, то выберите команду Refresh в меню Active Management Studio.
Создание таблиц
Создание таблиц с помощью Management Studio
Для создания таблицы базы данных при помощи Management Studio выполните следующую последовательность шагов:
- Находясь в Management Studio, раскройте группу SQL Server, а затем раскройте сервер.
- Раскройте папку Database, чтобы стали видны имеющиеся базы данных
- Раскройте базу данных, в которой вы хотите работать
- Нажмите правой кнопкой мыши на папку Table (таблицы) и в появившемся контекстном меню выберите New Table.
- Каждая строка таблицы в окне New Table обозначает одну колонку таблицы базы данных. Каждая колонка таблицы в окне New Table обозначает какой-либо атрибут колонки таблицы – тип данных, длину или способность хранить null-значения. Задайте каждую из колонок вашей таблиц базы данных, заполняя поочередно строки таблицы окна: введите имена таблиц в колонке Column Name, выберите тип данных в выпадающих меню в колонке Data Type и выберите длину типа данных (если это допустимо). Для переключения флажков в колонке Allow Nulls (Разрешаются Null-значения) пользуйтесь клавишей Shift или нажимайте мышью. В результате будет запрещаться или разрешаться применение Null-значений. Данные в строках таблицы базы данных будут физически храниться в порядке, в котором вы задали колонки. Если вы захотите вставить в окно New Table строчку с определением колонки между двух уже имеющихся определений, то нажмите правой кнопкой мыши на строчку окна, под которой вы хотите вставить новую строчку, и в появившемся контекстном меню выберите команду Insert Column (Вставить колонку). Чтобы удалить строчку, нажмите правой кнопкой мыши на эту строчку и выберите Delete Column (Удалить колонку) в контекстном меню. Одну из колонок можно задать как колонку первичного ключа (при необходимости), нажав на ее имя правой кнопкой мыши и выбрав Set Primary Key (Задать первичный ключ) в контекстном меню. Рядом с именем колонки появится изображение ключа.
- В нижней части окна New Table имеется вкладка с названием Columns, при помощи которой можно менять некоторые атрибуты колонки, выбранной в верхней части окна, например, устанавливать значения по умолчанию. Вы можете создавать и другие ограничения и индексы для таблицы, нажимая на имена колонок правой кнопкой мыши и выбирая в контекстном меню Indexes/Keys (Индексы/Ключи), Relationships (Взаимоотношения), Constraints (Ограничения) или Properties (Свойства таблиц и индексов) рядом со значком-иконкой Save (Сохранить) в панели инструментов. Независимо от выбранного способа, появится окно свойств таблиц и индексов. Имя вашей таблицы будет обозначаться как table1 и т.д. При сохранении таблицы ее имя можно изменить.
Значение по умолчанию | Свойство IDENTITY |
Ограничение CHECK | Сохранение таблицы |
- Чтобы дать имя новой таблице, нажмите значок-иконку Save. Появится диалоговое окно, в котором вы можете задать имя таблицы. Введите с клавиатуры нужное имя и нажмите OK, и тогда спроектированная таблица будет создана, а заданная информация о свойствах сохраниться. Теперь можно закрыть окно New Table, и имя вашей таблицы появится в правой панели Management Studio.
Создание таблиц с помощью шаблона Query Editor
Применение операторов T-SQL для создания таблиц
Чтобы создать таблицу БД нужно:
- Запустить Query Editorи убедиться, что вы выбрали вашу базу данных
- В панели запросов наберите (приведенный ниже текст) операторы и запустите их, нажатием клавиш F5 или Ctrl-E:
CREATE TABLE Product_Info
(ProductJD smallint IDENTITY (1,1) NOT NULL,
Product_Name char(20) NOT NULL,
Description char(30) NULL,
Price smallmoney NOT NULL,
Brand_ID smallint)
3. После чего нажмите Refresh в Object Explorer, чтобы обновить дерево объектов БД и имя вашей таблицы появится в правой панели Management Studio