Создание баз данных, таблиц и индексов

Для примеров мы будем использовать базу данных с информацией о служащих некоторой компании. Вот схема этой базы данных:

еmployee (employeeID, name, job, departmentID)

department (departmentID, name)

employeeSkills (employeeID, skill)

client (clientID, name, address, contactPerson, contactNumber)

assignment(clientID, employeelD, workdate, hours)

Для создания базы данных мы будем использовать монитор MySQL. Такой путь был выбран для того, чтобы лучше понять структуру баз данных, таблиц и индексов.

Ключевые слова SQL не зависят от регистра. Это является стандартом в си­стемах баз данных.

Чувствительность к регистру для идентификаторов зависит от системы баз данных, которую вы используете. В MySQL чувствительность к регистру для имен баз данных и таблиц зависит от операционной системы, в которой вы рабо­таете. Причина в том, что каждая база данных будет размещаться в определенном каталоге, а каждая таблица — в определенном файле операционной системы. Име­на каталогов и файлов в различных операционных системах подчиняются разным правилам.

На практике это означает, что при работе в Windows имена баз данных и таб­лиц не будут зависеть от регистра, но если вы используете операционную систему типа Unix, они будут зависеть от регистра.

Чтобы избежать путаницы, удобно считать все идентификаторы зависимыми от регистра, даже при работе в Windows. Это позволит по необходимости без осо­бых усилий перенести базу данных на другую платформу. Имена столбцов, индексов и псевдонимов (которые будут обсуждаться позже) в MySQL никогда не зависят от регистра.

Идентификатор — это имя псевдонима, базы данных, таблицы, столбца или индекса. С его помощью вы уникальным образом идентифицируете соответствую­щий объект. В идентификаторах допускается использовать любые символы, с учетом следующих исключений.

■ Они не должны содержать кавычек, а также символов ACSIl(O) и ASCII(255).

■ Имена баз данных могут содержать любые символы, допустимые для имен каталогов, но не должны содержать символы, имеющие для каталогов специальное значение (это /, \ и.).

■ Имена таблиц могут содержать любые символы, допустимые для имен фай­лов, за исключением символов. и /.

Все идентификаторы, кроме имен псевдонимов, могут содержать до 64 сим­волов. Имена псевдонимов могут содержать до 255 символов.

После разработки структуры было бы логично сообщить MySQL о том, что мы хотим создать новую базу данных. Это делается с помощью оператора SQL

CREATE DATABASE:

create database employee;

Убедиться в том, что этот оператор выполнил свою задачу, можно с помощью команды

show databases;

Вы должны увидеть базу данных employee в списке баз данных, имеющихся в системе.

Прежде чем получить возможность создавать таблицы или делать что-то иное с нашей новой базой данных employee, мы должны сообщить MySQL, что хотим работать с этой базой данных. Для этого используется оператор use:

use employee;

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

Для создания таблиц в базе данных employee используется оператор SQL CREATE TABLE.

В своей типичной форме этот оператор выглядит так:

create table имя_таблицы {определение таблицы)

[tyре=тип__таблицы];

Пример создания таблицы

Создание базы данных employee

drop database if exists employee; проверит, существует ли база данных employee, и, если это так, удалит ее

create database employee;

use employee; создаем базу данных и выбираем ее для использования

Теперь начнем создание таблиц внутри только что созданной базы данных. Сначала создадим таблицу department (отделы):

create table department (departmentID int not null auto_increment primary key, name varchar(30)) type=InnoDB;

Эта таблица содержит два столбца — departmentID (номер отдела), кото­рый будет первичным ключом, и department (название отдела). Для определе­ния столбцов таблицы используется список разделенных запятыми деклараций, заключенный в скобки. Обратите внимание на то, что атрибуты столбцов не раз­деляются запятыми, — разделяются только сами столбцы.

Именем столбца является departmentID, а его типом — int (целое). Это — уникальный номер, который мы будем использовать для идентификации отделов компании.

После типа указана некоторая дополнительная информация о столбце.

Сначала указано, что этот столбец не должен быть пустым — not null, — это значит, что для всех строк таблицы в этом столбце должно быть указано некоторое значение.

Затем мы указали, что столбец должен быть типа auto__increment. Это — очень удобная возможность в MySQL. Если при добавлении данных в эту таб­лицу мы не укажем номер отдела, MySQL поместит в соответствующий столбец уникальный номер, который окажется следующим в последовательности auto_ increment. Наконец, мы указываем, что этот столбец должен быть первичным ключом на­шей таблицы (primary key). Если первичный ключ состоит из одного столбца, мы можем указать его так, как это сделано здесь. Для первичных ключей, состо­ящих из нескольких столбцов, необходимо использовать другой подход, который мы обсудим чуть позже.

Теперь посмотрим на завершающую часть этого оператора SQL. После закрывающей скобки вы видите следующее:

type=InnoDB

Это выражение говорит о том, что таблица должна использовать механизм хранения InnoDB. MySQL поддерживает различные механизмы хранения дан­ных. Типом, ис­пользуемым по умолчанию, является MyISAM. Если бы мы хотели использовать таблицы MyISAM, нам не нужно было бы добавлять определение типа в конце оператора create table. Здесь указан тип InnoDB, поскольку в некоторых примерах мы собираемся использовать внешние ключи. Механизм InnoDB поддерживает внешние ключи и транзакции, a MyISAM — нет. Таблицы типа MyISAM часто работают быстрее, чем таблицы InnoDB. Поэтому для каждой таблицы нужно решить, какой тип будет для нее лучшим.

Можно иметь таблицы разных типов, например, одни таблицы могли бы быть таблицами InnoDB, а другие — таблицами MyISAM (а если нужно, и таблицами каких-то других типов.

Теперь рассмотрим принцип построения следующей таблицы

create table employee (employeeID int not null auto_increment primary key, name varchar(80), job varchar(30), departmentID int not null references department(departmentID)) type=InnoDB;

В этом операторе новым для нас является только один элемент синтаксиса. Последний столбец (departmentID) в таблице employee содержит номера отделов, в которых работают служащие. Это — внешний ключ. Он объявляется в определении таблицы с помощью выражения

references:

Выражение references говорит о том, что departmentID ссылается на столбец departmentID в таблице department.

Обратите внимание на то, что здесь мы имеем возможность использовать оп­цию внешнего ключа потому, что таблица employee является таблицей InnoDB. В таблицах MyISAM использовать внешние ключи нельзя.

create table employeeSkills (employeeID int not null references employee(employeelD), skill varchar (15) not null, primary key (employeelD, skill)) type=InnoDB;

В этой таблице мы тоже имеем внешний ключ, в данном случае это employeeID. Интересно то, что в определении этой таблицы задается первич­ный ключ, состоящий из двух столбцов. Вы можете заметить, что здесь сначала объявляются два столбца таблицы, employeeID и skill, а затем отдельно объ­является первичный ключ с помощью следующей строки: primary key (employeelD, skill)

create table client (clientID int not null auto_increment primary key, name varchar (40), address varchar (100), contactPerson varchar(80), contactNumber char (12)) type=InnoDB;

create table assignment (clientID int not null references client(clientID), employeeID int not null references employee(employeelD), workdate date not null, hours float, primary key (clientID, employeelD, workdate)) type=InnoDB;

Корректность установок таблиц в вашей базе данных можно проверить с по­мощью команды

show tables;

Более пространную информацию о структуре каждой таблицы можно полу­чить с помощью команды

describe:

describe department;