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

Типы данных MySQL

Перед созданием базы данных необходимо точно определить, какие типы данных будут использованы.

Все типы данных, с которыми работает MySQL, можно разбить на три группы: числовые, текстовые и даты-времени. Рассмотрим эти типы данных по порядку.

1.1. Числовые типы данных

Числовые типы столбцов используются для хранения чисел, все числовые типы можно разбить на два подтипа: для хранения точных чисел и чисел с плавающей точкой. Все числовые типы характеризуются длиной хранимых чисел, а типы с плавающей точкой еще и числом десятичных разрядов. Эти значения указываются после объявления типа столбца, например, FLOAT(10, 2). В примере указано, что запись числа содержит 10 символов и два знака после десятичного разделителя.

Объявление числовых типов можно заканчивать ключевыми словами ZEROFILL и (или) USIGNED. Ключевое слово USIGNED означает, что столбец содержит только положительные числа или нули. ZEROFILL означает, что число будет отображено с ведущими нулями.

Типы данных NUMERIC и DECIMAL (DEC) идентичны. Эти типы данных используются для хранения чисел с плавающей точкой. Обычно их используют для хранения денежных значений.

Тип данных INTEGER можно сократить до INT. Это означает целое число в заданном диапазоне. Для хранения этого типа данных отводится 4 байта, что соответствует числам от -2 147 483 648 до 2 147 483 647. Также существует несколько вариантов типа INTEGER.

Тип данных TINYINT используется для однобайтных чисел от -128 до 127. Для хранения двухбайтных чисел (от -32 768 до 32 767) используется тип данных SMALLINT, а для хранения самых больших целых чисел (от -2⁶³ до 2⁶³-1) используется тип BIGINT .

Числа с плавающей точкой имеют либо тип FLOAT (четырехбайтные числа в диапазоне от -3.4 * 10 ³⁸ до 3.4 * 10³⁸ ), либо тип DOUBLE (восьмибайтные числа в диапазоне от -10 ^-308 до 10³⁰⁸ )

1.2. Текстовые типы данных

В типах данных char и varchar хранятся данные, состоящие из следующих компонентов:

- знаки в верхнем и нижнем регистре, например a, b и C.

- цифры, например 1, 2 и 3.

- специальные знаки, например символы @, & !.

Тип CHAR используется для хранения строк фиксированной длины. После ключевого слова CHAR обычно указывается длина строки, например, CHAR(50). Если длина строки не указана, то считается, что длина равна одному символу. Максимальная длина поля данного типа равняется 255 символам. Если число переданных в строку символов меньше указанной длины, то строка будет дополнена пробелами, если больше, то обрезана. При возврате значения пробелы будут удалены из строки.

Тип VARCHAR предназначен для хранения строк переменной длины. Как и в предыдущем типе данных задается максимальная длина строки, например, VARCHAR(30). При этом, более длинные строки, переданные в этот столбец, будут обрезаны.

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

Тип полей TEXT используется для хранения более длинных фрагментов текста, чем допускается предыдущими типами. Тип BLOB означает большой двоичный объект. Эти два типа одинаковы за исключением того, что в типе BLOB сравнение строк идет с учетом регистра символов, а в типе TEXT без учета регистра. Оба типа имеют переменную длину и оба имеют некоторые вариации:

- TINYTEXT и TINYBLOB могут хранить до 255 символов;

- TEXT и BLOB могут хранить до 64 килобайт информации;

- MEDIUMTEXT и MEDIUMBLOBмогут хранить до 16 мегабайт;

- LONGTEXT и LONGBLOB могут хранить до 4 гигабайт.

Тип ENUM позволяет перечислить набор возможных значений для ввода в поле и хранит только одно значение из представленного списка. Например, ENUM(`m`,`a`,`z`) . Если не указать, какое значение используется в поле по умолчанию, то будет использовано первое значение списка.

Тип SET аналогичен типу ENUM, но позволяет хранить несколько значений из списка значений в поле.

1.3. Типы даты и времени

Тип DATE применяется для хранения дат в формате ( гггг-мм-дд ), а тип TIME применяется для хранения времени в формате (чч:мм:сс).

Тип DATETIME объединяет два предыдущих типа и имеет формат (гггг-мм-дд чч:мм:сс ) .

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

Поле типа YEAR содержит значение года. Возможны две длины: YEAR(2) и YEAR(4) для двух, и четырех цифр года соответственно. При использовании YEAR(2) диапазон дат принимается с 1970 по 2069 годы.

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

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

База данных taxi создается командой CREATE DATABASE:

При правильном вводе сервер выдает сообщение «Query OK, 1 row affected»

Затем нужно сообщить серверу о том, что вы собираетесь работать с базой данных “taxi”. Это делается командой

mysql>USE taxi;

которую нужно подавать каждый раз перед работой с базой данных “taxi”.

Таблицы создаются командой CREATE TABLE. При создании нужно указать не только имя таблицы, но и ее полное определение, состоящее из определений отдельных полей (столбцов таблицы).

Создадим таблицу cars, которая будет содержать следующие поля:

model – название модели автомобиля;

madein – года выпуска автомобиля;

reg _number – государственный регистрационный номер;

color – цвет автомобиля.

Необходимо помнить, что названия баз данных, таблиц и полей должны задаваться одним словом (без использования символов-разделителей).

Кроме того, нам понадобится столбец id, однозначно идентифицирующий запись об автомобиле – первичный ключ. Значения этого поля не должны повторяться и не могут быть пустыми, желательно, чтобы эти значения были целыми числами, увеличивающимися на единицу в каждой новой записи.

mysql>CREATE TABLE cars (

model CHAR(50) NON NULL,

madein YEAR(4) NOT NULL,

reg_number CHAR(12) NOT NULL,

color CHAR(15) NOT NULL,

id SMALLINT AUTO_INCREMENT,

PRIMARY KEY(id)

);

Атрибут NOT NULL означает, что все строки таблицы должны иметь значение в этом столбце. Если NOT NULL не указано, поле в отдельных записях может не заполняться (т.е. иметь значение NULL).

Ключевое выражение PRIMARY KEY после имени столбца определяет, что этот столбец является первичным ключом для таблицы. Данные в этом столбце должны быть уникальными.

AUTO_INCREMENT – атрибут, используемый для создания столбца с уникальными значениями. Если при вставке строк оставлять такое поле пустым, MySQL автоматически генерирует уникальное значение идентификатора. Это значение будет на единицу больше максимального значения, уже существующего в столбце. В каждой таблице может быть не больше одного такого поля.

MySQL использует следующий алгоритм для инициализации счетчика в столбце id, имеющем атрибут AUTO_INCREMENT: после запуска сервера MySQL при первом запросе на добавление данных в таблицу cars сервер высчитывает максимальное значение в столбце id. Полученное значение увеличивается на единицу, заносится в новую запись и в счетчик. Если таблица была пуста, то счетчик устанавливается в единицу.

Таблица drivers содержит столбцы для хранения имени (name), отчества (second_name), фамилии (family_name) водителя, даты его рождения (birth), домашнего адреса (address) и даты поступления на работу (startdate).

mysql>CREATE TABLE drivers (

name CHAR(30) NOT NULL,

second_name CHAR(30),

family_name CHAR(30) NOT NULL,

birth DATE NOT NULL,

address CHAR(150) NOT NULL DEFAULT ‘unknown’,

startdate DATE NOT NULL DEFAULT ‘2002-01-01’,

id SMALLINT AUTO_INCREMENT,

PRIMARY KEY (id)

);

Кроме того, при создании таблицы для некоторых полей могут применяться дополнительные ключевые слова, уточняющие диапазон возможных значений.

Ключевое слово UNSIGNED может применяться после указания целочисленного типа и означает, что значение в поле может быть либо положительным, либо нулевым.

После ключевого слова DEFAULT указывается значение по умолчанию для данного столбца. Например: DEFAULT ‘no description’.

Таблица timetable содержит следующие поля: дату поездки (use_on), номер машины (car_number) и идентификатор водителя (driver_number).

mysql>CREATE TABLE timetable (

use_on DATE DEFAULT NULL,

car_number SMALLINT,

driver_number SMALLINT,

id SMALLINT AUTO_INCREMENT,

PRIMARY KEY(id),

INDEX (car_number),

INDEX (driver_number),

FOREIGN KEY (car_number) REFERENCES cars (id),

FOREIGN KEY (driver_number) REFERENCES drivers (id)

);

MySQL поддерживает ссылочную целостность базы с помощью ограничения внешнего ключа FOREIGN KEY. Назначение этого ключа – проверять соответствие значений в столбцах родительского и внешнего ключей. Эти столбцы должны иметь одинаковый тип данных, и только те значения, которые встречаются в родительском ключе, могут использоваться во внешнем ключе. Родительский ключ должен содержать неповторяющиеся значения и не содержать значения NULL.

Последние два столбца таблицы timetable являются внешними ключами, и MySQL 5 требует, чтобы перед созданием внешних ключей на этих столбцах были созданы индексы.

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

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

mysql>SHOW TABLES;

Можно просмотреть структуру таблицы командой

mysql>DESCRIBE table_name;

Здесь table_name – имя таблицы. Эта команда позволит просмотреть типы данных столбцов и дополнительные атрибуты, указанные при создании таблицы.

Например, просмотрим структуру таблицы cars.

2.1. Запись данных в таблицы

. Для внесения данных в базу данных используется оператор insert. Самый простой вариант - внесение данных во все поля таблицы:

mysql>INSERT INTO cars VALUES

('Volga', '2003', 'A786YC78', 'white', '1' );

mysql>INSERT INTO cars VALUES

->('Volga', '2002 ' , 'A788YC78', 'yellow', ‘2’ );

mysql>INSERT INTO cars VALUES

->('Volkswagen', '2003', 'A789YC78', 'red', ’3’ );

Аналогично можно добавлять данные только в избранные поля, при этом нужно указать имена полей явно:

mysql>INSERT INTO cars (model, madein, reg_number, color) VALUES

->('Renault', '2005' , 'A790YC78', 'white');

Добавим еще несколько записей в таблицы:

mysql>INSERT INTO drivers VALUES (

->'Stanislav', 'Petrovitch', 'Nikolaev',

->'1970-01-22', 'Nevsky prospect 11, flat 54', '2004-02-25', 1);

mysql>INSERT INTO drivers (name, second_name, family_name, birth,

->address, startdate)

->VALUES ('Fedor', 'Sergeevitch', 'Sentsov',

->'1960-11-02', 'Moskovsky prospect 92, flat 112', '2003-12-30');

mysql>INSERT INTO timetable VALUES ('2008-11-25', 3,1,1),

->('2008-11-30', 2,2,2), ('2008-12-01', 2,2,3);

Последняя запись обычно делается диспетчером автопарка каждый день утром. Чтобы упростить его работу, можно использовать встроенную в MySQL функцию curdate(), которая возвращает текущую дату как раз в нужном формате:

mysql> INSERT INTO timetable VALUES (curdate(), 2,2,4);

Если вы попытаетесь оставить пустыми поля, отмеченные not null и не имеющие значения по умолчанию, то сервер MySQL выдаст сообщение об ошибке и не выполнит команду.

Кроме того, сервер MySQL 5 проверяет связи между таблицами при вводе записей. То есть нельзя ввести в поле, являющееся внешним ключом, значение, отсутствующее в связанной таблице.