Как сделать таблицу в mysql
Обновлено: 04.07.2024
В этом уроке вы узнаете, как создавать таблицы в базе данных MySQL с помощью PHP.
Создание таблиц с помощью SQL
В предыдущем уроке мы узнали, как создать базу данных на сервере MySQL. Шаги по созданию таблицы аналогичны созданию баз данных. Разница в том, что вместо создания новой базы данных мы подключимся к существующей базе данных и создадим таблицу в этой базе данных. Для подключения к существующей базе данных мы можем передать дополнительную переменную "имя базы данных" при подключении к MySQL.
Теперь пришло время создать таблицу внутри базы данных, которые фактически будут хранить данные. Информация в таблице организована в строки и столбцы.
Новые таблицы добавляются в существующую базу данных с помощью оператора SQL CREATE TABLE .
За оператором CREATE TABLE следует имя создаваемой таблицы, за которым следует список имен и определений каждого столбца таблицы, разделенный запятыми.
Мы создадим таблицу с именем "persons" и столбцами: "id", "first_name", "last_name" и "email":
После выбора базы данных в следующем примере создается таблица с именем "persons" и столбцами: "id", "first_name", "last_name" и "email". В first_name, last_name и email столбцы должны содержать значение (т.е. NOT NULL). Столбец id имеет целочисленное значение, которое имеет автоматическое приращение (AUTO_INCREMENT):
CREATE TABLE persons (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(70) NOT NULL UNIQUE
)
Пояснения к таблице:
Обратите внимание, что за каждым именем поля следует объявление типа данных — это объявление указывает, какой тип данных может содержать столбец, будь то целое число, строка, дата и т.д.
В таблице выше используются следующие типы данных:
- VARCHAR: содержит строку переменной длины, которая может содержать буквы, числа и специальные символы. Максимальный размер указан в скобках.
- INT: тип данных INTEGER принимает числовые значения с подразумеваемой шкалой нуля. Он хранит любое целое число от -2147483648 до 2147483647.
После типа данных вы можете указать другие необязательные атрибуты для каждого столбца:
- NOT NULL — каждая строка должна содержать значение для этого столбца, нулевые значения не допускаются.
- DEFAULT — установите значение по умолчанию, которое добавляется, когда не передается никакое другое значение.
- UNSIGNED - используется для числовых типов, ограничивает хранимые данные положительными числами и нулем.
- AUTO INCREMENT — MySQL автоматически увеличивает значение поля на 1 каждый раз, когда добавляется новая запись.
- PRIMARY KEY — используется для однозначной идентификации строк в таблице. Столбец с настройкой PRIMARY KEY часто является идентификационным номером и часто используется с AUTO_INCREMENT.
Каждая таблица должна иметь столбец первичного ключа (в данном случае столбец "id"). Его значение должно быть уникальным для каждой записи в таблице.
Понимание значений NULL и NOT NULL
Если для столбца указано значение NULL, то в базу данных можно добавить строку, если этому столбцу не присвоено значение. И наоборот, если столбец определен как NOT NULL, то перед добавлением строки в таблицу ему должно быть присвоено значение.
Первичный ключ PRIMARY KEY
Первичный ключ — это столбец, используемый для идентификации отдельных записей в таблице. Значение столбца первичного ключа должно быть уникальным в контексте таблицы, в которой он существует, или, если несколько столбцов объединены, чтобы составить первичный ключ, комбинация значений ключа должна быть уникальной для каждой строки.
Первичный ключ определяется с помощью оператора PRIMARY KEY во время создания таблицы. Если используется несколько столбцов, они разделяются запятыми:
Автоматическое приращение AUTO_INCREMENT
AUTO_INCREMENT — одно из самых простых, но наиболее полезных определений столбцов в языке SQL. По сути, когда столбец определяется с помощью AUTO_INCREMENT, значение столбца автоматически увеличивается каждый раз, когда в таблицу добавляется новая строка. Это особенно полезно при использовании столбца в качестве первичного ключа. Используя AUTO_INCREMENT, нет необходимости писать операторы SQL для вычисления нового уникального идентификатора для каждой строки. Все это обрабатывается сервером MySQL при добавлении строки.
При использовании AUTO_INCREMENT необходимо соблюдать два правила. Во-первых, статус AUTO_INCREMENT может быть присвоен только одному столбцу в таблице. Во-вторых, столбец AUTO_INCREMENT должен быть проиндексирован (например, объявлен как первичный ключ).
Можно переопределить значение AUTO_INCREMENT столбца, просто указав значение при выполнении оператора INSERT. Пока указанное значение является уникальным, предоставленное значение будет использоваться в новой строке, а последующие приращения начнутся с вновь вставленного значения.
MySQL может быть запрошен для получения самого последнего значения приращения, используя функцию last_insert_id() следующим образом:
Создание таблиц БД в трех различных версиях
Теперь, когда мы понимаем SQL-запрос, давайте воспользуемся PHP для его выполнения.
Ниже описано создание таблиц в трех различных версиях: с использованием синтаксиса объектно-ориентированной процедуры MySQLi, процедурный MySQLi и процедуры PDO.
Таблица базы данных имеет свое уникальное имя и состоит из строк и столбцов.
MySQL Создание таблицы, используя mysqli и PDO
Заявление CREATE TABLE используется для создания таблицы в MySQL.
Мы создадим таблицу с именем "MyGuests" , с пятью столбиками: "id" , "firstname" , "lastname" , "email" и "reg_date" :
CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)
Примечания к таблице выше:
Тип данных определяет, какой тип данных столбец может содержать. Для полного ознакомления со всеми доступными типами данных перейдите к нашей Справочник на типы данных.
После, типов данных можно указать другие необязательные атрибуты для каждого столбца:
- NOT NULL - Каждая строка должна содержать значение для этого столбца, значения NULL не разрешены
- DEFAULT value - Задайте значение по умолчанию, добавляемое, когда никакое другое значение не передается
- UNSIGNED - Используется для типов чисел, ограничивает сохраненные данные положительными числами и нулем
- AUTO INCREMENT - MySQL автоматически увеличивает значение поля на 1 при каждом добавлении новой записи
- PRIMARY KEY - Используется для уникальной идентификации строк в таблице. Столбец с параметром PRIMARY KEY первичного ключа часто является ID идентификационным номером и часто используется с AUTO_INCREMENT
Каждая таблица, должна иметь столбец первичного ключа (в этом случае: "id" столбец). Его значение должно быть уникальным для каждой записи в таблице.
В следующих примерах показано, как создать таблицы в PHP:
Пример MySQLi - объектно-ориентированный
connect_error) <
die("Ошибка подключения: " . $conn->connect_error);
>
// Создание таблицы
$sql ;
if ($conn->query($sql) === TRUE) echo "Таблица MyGuests создана успешно";
> else echo "Ошибка создания таблицы: " . $conn->error;
>
// Закрыть подключение
$conn->close();
?>
Пример MySQLi - процессуальный
Пример PDO
// Создание таблицы
$sql ;
// Используйте exec (), поскольку результат не возвращается
$conn->exec($sql);
echo "Таблица MyGuests создана успешно";
>
catch(PDOException $e)
echo $sql . "
" . $e->getMessage();
>
Итак, мы познакомились с типами данных, теперь будем усовершенствовать таблицы для нашего форума. Сначала разберем их. И начнем с таблицы users (пользователи). В ней у нас 4 столбца:
id_user - целочисленные значения, значит будет тип int, ограничим его 10 символами - int (10).
name - строковое значение varchar, ограничим его 20 символами - varchar(20).
email - строковое значение varchar, ограничим его 50 символами - varchar(50).
password - строковое значение varchar, ограничим его 15 символами - varchar(15).
Все значения полей обязательны для заполнения, значит надо добавить тип NOT NULL.
id_user int (10) NOT NULL
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
Первый столбец, как вы помните из концептуальной модели нашей БД, является первичным ключом (т.е. его значения уникальны, и они однозначно идентифицируют запись). Следить за уникальностью самостоятельно можно, но не рационально. Для этого в SQL есть специальный атрибут - AUTO_INCREMENT, который при обращении к таблице на добавление данных высчитывает максимальное значение этого столбца, полученное значение увеличивает на 1 и заносит его в столбец. Таким образом, в этом столбце автоматически генерируется уникальный номер, а следовательно тип NOT NULL излишен. Итак, присвоим атрибут столбцу с первичным ключом:
id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
Теперь надо указать, что поле id_user является первичным ключом. Для этого в SQL используется ключевое слово PRIMARY KEY (), в скобочках указывается имя ключевого поля. Внесем изменения:
id_user int (10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
PRIMARY KEY (id_user)
Итак, таблица готова, и ее окончательный вариант выглядит так:
create table users (
id_user int (10) AUTO_INCREMENT,
name varchar(20) NOT NULL,
email varchar(50) NOT NULL,
password varchar(15) NOT NULL,
PRIMARY KEY (id_user)
);
Теперь разберемся со второй таблицей - topics (темы). Рассуждая аналогично, имеем следующие поля:
id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)
Но в модели нашей БД поле id_author является внешним ключом, т.е. оно может иметь только те значения, которые есть в поле id_user таблицы users. Для того, чтобы указать это в SQL есть ключевое слово FOREIGN KEY (), которое имеет следующий синтаксис:
FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);
Укажем, что id_author - внешний ключ:
id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)
FOREIGN KEY (id_author) REFERENCES users (id_user)
Таблица готова, и ее окончательный вариант выглядит так:
create table topics (
id_topic int (10) AUTO_INCREMENT,
topic_name varchar(100) NOT NULL,
id_author int (10) NOT NULL,
PRIMARY KEY (id_topic),
FOREIGN KEY (id_author) REFERENCES users (id_user)
);
create table posts (
id_post int (10) AUTO_INCREMENT,
message text NOT NULL,
id_author int (10) NOT NULL,
id_topic int (10) NOT NULL,
PRIMARY KEY (id_post),
FOREIGN KEY (id_author) REFERENCES users (id_user),
FOREIGN KEY (id_topic) REFERENCES topics (id_topic)
);
Обратите внимание, внешних ключей у таблицы может быть несколько, а первичный ключ в MySQL может быть только один. В первом уроке мы удалили нашу БД forum, пришло время создать ее вновь.
Запускаем сервер MySQL (Пуск - Программы - MySQL - MySQL Server 5.1 - MySQL Command Line Client), вводим пароль, создаем БД forum (create database forum;), выбираем ее для использования (use forum;) и создаем три наших таблицы:
Обратите внимание, одну команду можно писать в несколько строк, используя клавишу Enter (MySQL автоматически подставляет символ новой строки ->), и только после разделителя (точки с запятой) нажатие клавиши Enter приводит к выполнению запроса.
Помните, если вы сделали что-то не так, всегда можно удалить таблицу или всю БД с помощью оператора DROP. Исправлять что-то в командной строке крайне неудобно, поэтому иногда (особенно на начальном этапе) проще писать запросы в каком-нибудь редакторе, например в Блокноте, а затем копировать и вставлять их в черное окошко.
Итак, таблицы созданы, чтобы убедиться в этом вспомним о команде show tables:
И, наконец, посмотрим структуру нашей последней таблицы posts:
Теперь становятся понятны значения всех полей структуры, кроме поля DEFAULT. Это поле значений по умолчанию. Мы могли бы для какого-нибудь столбца (или для всех) указать значение по умолчанию. Например, если бы у нас было поле с названием "Женаты\Замужем" и типом ENUM ('да', 'нет'), то было бы разумно сделать одно из значений значением по умолчанию. Синтаксис был бы следующий:
married enum ('да', 'нет') NOT NULL default('да')
Т.е. это ключевое слово пишется через пробел после указания типа данных, а в скобках указывается значение по умолчанию.
Но вернемся к нашим таблицам. Теперь нам необходимо внести данные в наши таблицы. На сайтах, вы обычно вводите информацию в какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java. ) извлекает эти данные из формы и заносит их в БД. Делает он это посредством SQL-запроса на внесение данных в базу. Писать сценарии на php мы пока не умеем, а вот отправлять SQL-запросы на внесение данных сейчас научимся.
Для этого используется оператор INSERT. Синтаксис можно использовать двух видов. Первый вариант используется для внесения данных во все поля таблицы:
INSERT INTO имя_таблицы VALUES ('значение_первого_столбца','значение_второго_столбца', . 'значение_последнего_столбца');
Давайте попробуем внести в нашу таблицу users следующие значения:
Второй вариант используется для внесения данных в некоторые поля таблицы:
INSERT INTO имя_таблицы ('имя_столбца', 'имя_столбца') VALUES ('значение_первого_столбца','значение_второго_столбца');
В нашей таблице users все поля обязательны для заполнения, но наше первое поле имеет ключевое слово - AUTO_INCREMENT (т.е. оно заполняется автоматически), поэтому мы можем пропустить этот столбец:
Но прежде внесем информацию еще о нескольких пользователях. Чтобы добавить сразу несколько строк, надо просто перечислять скобки со значениями через запятую:
Теперь внесем данные во вторую таблицу - topics (темы). Все тоже самое, но надо помнить, что значения в поле id_author должны присутствовать в таблице users (пользователи):
Теперь давайте попробуем внести еще одну тему, но с id_author, которого в таблице users нет (т.к. мы внесли в таблицу users только 5 пользователей, то не существует):
Сервер выдает ошибку и говорит, что не может внести такую строку, т.к. в поле, являющемся внешним ключом, стоит значение, отсутствующее в связанной таблице users.
Итак, у нас есть 3 таблицы, в которых есть данные. Встает вопрос - как посмотреть, какие данные хранятся в таблицах. Этим мы и займемся на следующем уроке.
Видеоуроки php + mysql
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Для создания таблиц используется оператор CREATE TABLE.
Для примера используем ранее созданные таблицы.
Итак, в результате в базе данных Institute мы собираемся получить следующие таблицы:
Используем следующие правила:
- имя таблицы указывается после ключевого слова CREATE TABLE (если имя состоит из нескольких слов, то его следует заключить в одинарные кавычки);
- далее в круглых скобках следуют имена столбцов таблицы (полей), после которых указывается тип данных, которому будет принадлежать поле;
- не обязательно: затем указывается может ли поле содержать пустые значения ( NULL — может быть пустым или NOT NULL — обязательно для заполнения);
- одно из полей назначается первичным ключом ( Primary key );
- поля отделяются запятыми.
Первая таблица называется teachers , соответственно первая строка кода:
CREATE TABLE `teachers` ( . )
Название таблицы может быть написано и без кавычек, так как состоит из одного слова.
Далее создаем поле id , тип данных которого — целые числа (ограничим их одиннадцатью INT(11) ), поле обязательно для заполнения, поэтому добавим NOT NULL :
`id` INT(11) NOT NULL,
Поле name будет строковым, ограничим его в 25 символов ( VARCHAR(25) ), поле тоже обязательно для заполнения, поэтому добавим NOT NULL :
`name` VARCHAR(25) NOT NULL,
Поле zarplata , тип данных которого — целые числа (ограничим их одиннадцатью INT(11) ). То же самое касается поля premia :
`zarplata` INT(11), `premia` INT(11),
После перечисления всех полей указываем ключевое поле:
Важно: желательно для каждой таблицы базы данных выбирать ключевое поле. Это поле уникально идентифицирует каждую запись таблицы, т.е. значения этого поля для каждой записи (строки) должны быть уникальными и не могут повториться для нескольких строк. Обычно ключевое поле используется для организации связей между таблицами
Получаем код создания таблицы teachers :
CREATE TABLE `teachers` ( `id` INT(11) NOT NULL, `name` VARCHAR(25) NOT NULL, `zarplata` INT(11), `premia` INT(11), PRIMARY KEY (`id`) );
Для заполнения таблицы данными используется оператор INSERT языка SQL.
INSERT INTO teachers VALUES (1, 'Иванов',1,10000,500), (2, 'Петров',1,15000,1000) , (3, 'Сидоров',1,14000,800), (4,'Боброва',1,11000,800);
Обновление таблиц: удаление и добавление полей
Обновление таблиц выполняется при помощи ключевых слов sql ALTER TABLE . Обновляя таблицу можно:
-
Добавление поля в SQL таблицу, ADD
ALTER TABLE teachers ADD phone CHAR (20);
CREATE TABLE `teachers` ( `id` INT(11) NOT NULL, `name` VARCHAR(25) NOT NULL, `zarplata` INT(11), `premia` INT(11), PRIMARY KEY (`id`) ); ALTER TABLE teachers ADD phone CHAR (20); INSERT INTO teachers VALUES (1, 'Иванов',10000,500,222222), (2, 'Петров',15000,1000,245322) , (3, 'Сидоров',14000,800,233222), (4,'Боброва',11000,800,263222);
а в правое окно:
select * from teachers;
В итоге получаем:
ALTER TABLE teachers DROP COLUMN phone
CREATE TABLE `teachers` ( `id` INT(11) NOT NULL, `name` VARCHAR(25) NOT NULL, `zarplata` INT(11), `premia` INT(11), `phone` char(20), PRIMARY KEY (`id`) ); ALTER TABLE teachers DROP COLUMN phone; INSERT INTO teachers VALUES (1, 'Иванов',10000,500), (2, 'Петров',15000,1000), (3, 'Сидоров',14000,800), (4,'Боброва',11000,800);
Читайте также: