Создаем, удаляем и редактируем таблицы с помощью SQL-запросов

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

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

Символы 1 и ∞ указывают тип связи таблиц. Что это значит? Например, в нашем случае одна и та же должность может быть у многих людей, поэтому здесь связь один ко многим, то же самое и со страной – в одной стране могут проживать много лиц.

Для начала, надо выбрать БД, в которой будем создавать наши таблицы, в предыдущей статье мы ее создали и назвали study. Для выбора служит команда USE:

USE study;

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

CREATE TABLE persons
(
 id INT NOT NULL AUTO_INCREMENT,
 first_name VARCHAR(50) NOT NULL,
 last_name VARCHAR(150) NOT NULL,
 age INT(3) NULL DEFAULT '25',
 registration_date DATE NOT NULL,
 country INT NOT NULL,
 profession INT NULL,
 PRIMARY KEY (id)
)
ENGINE=MyISAM;

Теперь все по порядку, команда CREATE TABLE аналогична CREATE DATEBASE, только если во втором случае мы создаем базу данных, то в нашем – таблицу этой БД; persons – это имя нашей создаваемой таблицы. После этого мы в обычных скобках через запятую указываем имена и свойства полей, как не трудно догадаться, у нас создались поля с такими названиями: id, first_name, last_name, age, registration_date, country и profession.

Типов данных MySQL существует немало, ознакомиться с ними можно, например, здесь: http://www.mysql.ru Я не говорю, что их необходимо знать все, но основные надо выучить.

Разберем подробнее наши поля:
id – поле, которое будет хранить идентификатор каждой записи в таблице, тип данных для которого указываем integer (условное обозначение INT). Свойство NOT NULL означает, что данное поле не должно содержать пустых значений, проще говоря – оно является обязательным к заполнению. Это поле-идентификатор должно содержать только уникальные значения, поэтому для него мы указываем свойство AUTO_INCREMENT, которое увеличивает, при добавлении новых данных в таблицу, значение id на 1, по сравнению с предыдущей записью.
Например, у нас в таблице есть четыре записи, у которых идентификаторы 1, 2, 3 и 4. Мы удаляем запись с id = 4, а после этого добавляем новую. Теперь в таблице будут такие идентификаторы: 1, 2, 3 и 5. То есть, значение будет увеличиваться на 1 независимо от того, удалялись с таблицы данные, или нет.

first_name – имя человека. Для этого поля мы указываем тип данных VARCHAR с максимальной его длинной в 50 символов (значение VARCHAR(50)). Так же делаем его обязательным для заполнения.

last_name – поле отвечает за хранение фамилии: тип данных VARCHAR длинной 150 символов, не пустое.

age – это возраст. Мы указали для него тип данных integer с длиной в 3 знака (INT(3)), поскольку надо учитывать и долгожителей, которым, например, больше 100 лет. Задав такое значение, мы можем хранить возраст от 0 до 999 (минусовые значения не в счет), более чем достаточно. Если NOT NULL – обязательное заполнение, то понятно, что NULL разрешает не заполнять это поле. В нашем случае оно будет заполняться по любому, поскольку задано значение по умолчанию в 25 (DEFAULT ‘25’). То есть, если мы не заполняем это поле, то оно будет заполнено автоматически, и человеку будет присвоен возраст 25 лет.

registration_date – хранит дату регистрации человека в БД. Здесь все стандартно – тип данных дата (DATE) и не пустое.

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

После описания всех наших полей указываем, что поле id является первичным ключом для данной таблицы (PRIMARY KEY (id)). Как альтернативу, первичный ключ можно задавать и при описании самого поля после AUTO_INCREMENT, вы увидите, как это сделать когда мы будем создавать вторую таблицу.

Ну и, наконец, ENGINE=MyISAM. Так мы задаем тип самой таблицы persons, в разных версиях этот параметр может задаваться по-разному, например, так TYPE=MyISAM. Обо всех типах таблиц, их преимуществах и недостатках есть множество информации в интернете, поэтому желательно было бы с ней ознакомиться.

Чтобы посмотреть структуру нашей созданной таблицы, следует выполнить следующую команду:

DESCRIBE persons;

В результате чего, получаем на экран такой результат:

При создании таблиц можно, как и в случае с БД, использовать условие IF NOT EXISTS – создать, если еще не создана. Создадим таблицу countries с полями идентификатор и название страны; зададим для нее первичный ключ при описании поля:

CREATE TABLE IF NOT EXISTS countries
(
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 country_name VARCHAR(100) NOT NULL
)
ENGINE=MyISAM;

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

ALTER TABLE countries ADD acronym_name VARCHAR(100) NOT NULL;

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

ALTER TABLE countries CHANGE acronym_name acronym_name VARCHAR(2);

Так мы установили размер поля acronym_name в 2 символа, ведь для сокращенного названия этого вполне достаточно (RU, UA, EN, US и т.д.). Переведя всю эту команду на русский язык, можно сказать, что она звучит, примерно, так:

ИЗМЕНЕНИЕ ТАБЛИЦЫ имя_таблицы ИЗМЕНИТЬ старое_имя_поля новое_имя_поля ПАРАМЕТРЫ_НОВОГО_ПОЛЯ

Выполнив команду:

DESCRIBE countries;

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

Кстати, просмотреть структуру таблицы можно еще и так, результат будет тот же:

SHOW COLUMNS FROM countries;

Чтобы изменить имя самой таблицы используется следующая команда:

ALTER TABLE старое_имя_таблицы RENAME новое_имя_таблицы;

Можете попробовать функционал данного SQL-запроса, я же оставлю прежние имена наших созданных таблиц.

Осталось рассмотреть команду удаления таблицы, здесь аналогично работе с базой данных используется DROP. Пример:

УДАЛИТЬ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЕТ, messages

Выполняем: 

DROP TABLE IF EXISTS messages;

Понятно, что здесь ничего не удалиться, поскольку у нас просто-напросто таблицы с названием messages нет в базе данных, но если бы была, то удалилась бы. Чтобы удалить с таблицы ненужные поля, используется такой запрос:

ALTER TABLE имя_таблицы DROP поле_1, DROP поле_2, … ;

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

ALTER TABLE имя_таблицы DROP название_поля;

Вот мы и рассмотрели основные команды для работы с таблицами. Это далеко не весь синтаксис, но мы рассмотрели основной, который чаще всего используется. Как рекомендация, почитайте об индексных и уникальных полях (свойства INDEX и UNIQUE) – это тоже немаловажная информация.

Третью таблицу professions я не создавал, оставив ее для закрепления материала начинающим SQL-никам :) Попробуйте создать ее сами, свойства полей следующие:

• id – целочисловое, автоинкрементное, первичный ключ, не пустое;
• profession_name – строка в 255 символов, не пустое.

После этого посмотрите список всех ваших созданных таблиц командой:

SHOW TABLES FROM study;