Создание, изменение и удаление таблиц

Лекция 2. Создание, изменение и удаление таблиц Реляционная база данных (РБД, или далее в контексте просто БД) представляет из себя совокупность таблиц, связанных между собой. Если говорить грубо, то БД – файл в котором данные хранятся в структурированном виде. СУБД – Система Управления этими Базами Данных, т.е. это комплекс инструментов для работы с конкретным типом БД (MS SQL, Oracle, MySQL, Firebird, …). В конкретной СУБД, язык SQL может иметь специфичную реализацию (свой диалект). Таблица представляет из себя совокупность столбцов. Столбцы, так же могут называть полями или колонками, все эти слова будут использоваться как синонимы, выражающие одно и тоже. Таблица – это главный объект РБД, все данные РБД хранятся построчно в столбцах таблицы. Строки, записи – тоже синонимы. Для каждой таблицы, как и ее столбцов задаются наименования, по которым впоследствии к ним идет обращение. Наименование объекта (имя таблицы, имя столбца, имя индекса и т.п.) в MS SQL может иметь максимальную длину 128 символов. DDL и DML — подмножество языка SQL:  Язык DDL служит для создания и модификации структуры БД, т.е. для создания/изменения/удаления таблиц и связей.  Язык DML позволяет осуществлять манипуляции с данными таблиц, т.е. с ее строками. Он позволяет делать выборку данных из таблиц, добавлять новые данные в таблицы, а так же обновлять и удалять существующие данные. 1 DDL – Data Definition Language (язык описания данных) Для примера рассмотрим таблицу с данными о сотрудниках, в привычном для человека виде: Табельный номер ФИО Дата рождения E-mail Должность Отдел 1000 Иванов И.И. 19.02.1955 i.ivanov@test.tt Директор Администрация 1001 Петров П.П. 03.12.1983 p.petrov@test.tt Программист ИТ 1002 Сидоров С.С. 07.06.1976 s.sidorov@test.tt Бухгалтер Бухгалтерия 1003 Андреев А.А. 17.04.1982 a.andreev@test.tt Старший программист ИТ охарактеризовать по Каждый из этих столбцов можно типу содержащемся в нем данных (подробнее смотреть в Лекции 1):  Табельный номер – целое число  ФИО – строка  Дата рождения – дата  E-mail – строка  Должность – строка  Отдел – строка Так же значение поля, в том случае если это не запрещено, может быть не указано, для этой цели используется ключевое слово NULL. Для создания базы данных используется команда: CREATE DATABASE Database_Name Удалить базу данных можно командой: DROP DATABASE Database_Name Для создания таблиц в БД используется команда CREATE TABLE. При наименовании столбцов на кириллице необходимо заключать имена в квадратные скобки […]. Однако для большего удобства все 2 наименования объектов лучше задавать на латинице и не использовать в именах пробелы. CREATE TABLE [Сотрудники]( [Табельный номер] int, [ФИО] nvarchar(30), [Дата рождения] date, [E-mail] nvarchar(30), [Должность] nvarchar(30), [Отдел] nvarchar(30) ) В MS SQL обычно в данном случае каждое слово начинается с прописной буквы, например, для поля «Табельный номер», мы могли бы задать имя PersonnelNumber. Так же в имени можно использовать цифры, например, PhoneNumber1. Наиболее предпочтительнее наименования столбцов перевести в латиницу:  Employees - Таблица «Сотрудники»  ID – Табельный номер (Идентификатор сотрудника)  Name – ФИО  Birthday – Дата рождения  Email – E-mail  Position – Должность  Department – Отдел Очень часто для наименования поля идентификатора используется слово ID. В данном случае создание таблицы будет иметь вид: CREATE TABLE Employees( ID int, 3 Name nvarchar(30), Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30) ) Для того, чтобы задать обязательные для заполнения столбцы, можно использовать опцию NOT NULL (то есть поле не может быть незаполненным): CREATE TABLE Employees( ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30) ) Для изменения таблицы можно воспользоваться командой: ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NOT NULL Для заполнения таблицы данными используется команда INSERT: INSERT Employees(ID,Position,Department) VALUES (1000,N'Директор',N'Администрация'), (1001,N'Программист',N'ИТ'), (1002,N'Бухгалтер',N'Бухгалтерия'), (1003,N'Старший программист',N'ИТ') Однако в данном случае команда выдаст ошибку, так как в созданной таблице поле Name не может быть пустым. Правильная запись скрипта: 4 INSERT Employees(ID,Position,Department,Name) VALUES (1000,N'Директор',N'Администрация',N'Иванов И.И.'), (1001,N'Программист',N'ИТ',N'Петров П.П.'), (1002,N'Бухгалтер',N'Бухгалтерия',N'Сидоров С.С.'), (1003,N'Старший программист',N'ИТ',N'Андреев А.А.') Первичный ключ При создании таблицы желательно, чтобы она имела уникальный столбец или же совокупность столбцов, которая уникальна для каждой ее строки – по данному уникальному значению можно однозначно идентифицировать запись. Такое значение называется первичным ключом таблицы. Обычно таким уникальным значением является столбец ID. Создать первичный ключ к уже существующей таблице можно при помощи команды: ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID) где «PK_Employees» – это имя ограничения, отвечающего за первичный ключ. Обычно для наименования первичного ключа используется префикс «PK_» после которого идет имя таблицы. Если первичный ключ состоит из нескольких полей, то эти поля необходимо перечислить в скобках через запятую: ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения PRIMARY KEY(поле1,поле2,…) ВАЖНО! в MS SQL все поля, которые входят в первичный ключ, должны иметь характеристику NOT NULL. Так же первичный ключ можно определить непосредственно при создании таблицы, т.е. в контексте команды CREATE TABLE: CREATE TABLE Employees ( ID int NOT NULL, Name nvarchar(30) NOT NULL, 5 Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), CONSTRAINT PK_Employees PRIMARY KEY(ID) ) Если первичный ключ в таблице состоит только из значений одного столбца, то можно использовать следующую запись: CREATE TABLE Employees( ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, -указываем как характеристику поля Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30) ) Индексы Индексы – это специальные поля поиска, которые поисковая система базы данных можно использовать для ускорения поиска данных. Проще говоря, индекс представляет собой указатель на данные в таблице. Индекс в базе данных очень похож на индекс в конце книги. Например, если Вы хотите ссылки на все страницы в книге, посвященной определенной теме, сначала обратитесь к индексу, в котором перечислены все темы в алфавитном порядке, а затем передается одному или нескольким конкретным номерам страниц. Индекс помогает ускорить для запросов SELECT и предложения WHERE, но это замедляет ввод данных, с заявлениями UPDATE и INSERT. Индексы могут быть созданы или удалены без влияния на данные. 6 Создание индекса предполагает команду CREATE INDEX, которое позволяет назвать индекс, чтобы указать таблицу и какой столбец или столбцы индексировать и указать, является ли индекс в порядке возрастания или убывания. Основной синтаксис CREATE INDEX выглядит следующим образом: CREATE INDEX index_name ON table_name; Индексы также могут быть уникальными, с ограничением UNIQUE, для того, чтобы индекс предотвращал дублирование записей в столбце или комбинации столбцов, на которых есть индекс. Одноколоночные индексы Индекс для одного столбца создается на основе только одного столбца таблицы. Базовый синтаксис выглядит следующим образом. CREATE INDEX index_name ON table_name (column_name); Уникальные индексы Уникальные индексы используются не только для работы, но и для обеспечения целостности данных. Уникальный индекс не позволяет какиелибо повторяющиеся значения, которые будут вставлены в таблицу. Базовый синтаксис выглядит следующим образом. CREATE UNIQUE INDEX index_name on table_name (column_name); Составные индексы Составной индекс является индексом для двух или более столбцов таблицы. Его основной синтаксис выглядит следующим образом. CREATE INDEX index_name on table_name (column1, column2); Независимо от того, какой создать индекс, для одного столбца или составной индекс, примите во внимание столбец(ы), которые вы можете использовать очень часто в запросе WHERE в качестве условия фильтра. 7 Если есть только один используемый столбец, индекс должен быть выбран для одного столбца. Если существуют два или несколько столбцов, которые часто используются в предложении WHERE в качестве фильтров, композитный индекс будет лучшим выбором. Неявные индексы Неявные индексы – это индексы, которые автоматически создаются на сервере базы данных при создании объекта. Индексы автоматически создаются для первичного ключа и ограничения уникальности. Удаление индекса Индекс может быть удален с помощью SQL команды DROP. Следует соблюдать осторожность при удалении индекса, поскольку производительность может либо замедлиться или улучшиться. Базовый синтаксис выглядит следующим образом: DROP INDEX index_name; Вы можете посмотреть пример ограничения INDEX, чтобы увидеть некоторые реальные примеры по индексам. Когда следует избегать индексов? Хотя индексы предназначены для повышения производительности работы с базой данных, есть моменты, когда их следует избегать.  Индексы не должны использоваться на небольших таблицах.  Таблицы, которые имеют частые большие операции обновления или вставки.  Индексы не должны использоваться на колонках, содержащих большое количество нулевых значений.  Столбцы, которыми часто манипулируют, не должны быть проиндексированы. 8