В этой статье мы расскажем, как создавать новые базы данных и таблицы, используя команды SQL в клиенте mysql. Предполагается, что этот инструмент запущен и подключен к серверу базы данных MySQL.
Создание новой базы данных MySQL
Новая база данных создается с помощью оператора SQL CREATE DATABASE, за которым следует имя создаваемой базы данных. Для этой цели также используется оператор CREATE SCHEMA. Например, для создания новой базы данных под названием MySampleDB в командной строке mysql нужно ввести следующий запрос:
CREATE DATABASE MySampleDB;
Если все прошло нормально, команда сгенерирует следующий вывод:
Query OK, 1 row affected (0.00 sec)
Если указанное имя базы данных конфликтует с существующей базой данных MySQL, будет выведено сообщение об ошибке:
ERROR 1007 (HY000): Can"t create database "MySampleDB"; database exists
В этой ситуации следует выбрать другое имя базы данных или использовать опцию IF NOT EXISTS. Она создает базу данных только в том случае, если она еще не существует:
CREATE DATABASE IF NOT EXISTS MySampleDB;
Создание таблиц с помощью SQL
Новые таблицы добавляются в существующую базу данных с помощью оператора SQL CREATE TABLE. За оператором CREATE TABLE следует имя создаваемой таблицы, а далее через запятые список имен и определений каждого столбца таблицы:
CREATE TABLE имя_таблицы (определение имени_столбца, определение имени_таблицы …, PRIMARY KEY = (имя_столбца)) ENGINE = тип_движка;
В определении столбца задается тип данных, может ли столбец быть NULL, AUTO_INCREMENT. Оператор CREATE TABLE также позволяет указать столбец (или группу столбцов) в качестве первичного ключа.
Прежде чем будет создавать таблицу, нужно выбрать базу данных. Это делается с помощью оператора SQL USE:
Создадим таблицу, состоящую из трех столбцов: customer_id , customer_name и customer_address . Столбцы customer_id и customer_name не должны быть пустыми (то есть NOT NULL). customer_id содержит целочисленное значение, которое будет автоматически увеличиваться при добавлении новых строк. Остальные столбцы будут содержать строки длиной до 20 символов. Первичный ключ определяется как customer_id.
CREATE TABLE customer (customer_id int NOT NULL AUTO_INCREMENT, customer_name char(20) NOT NULL, customer_address char(20) NULL, PRIMARY KEY (customer_id)) ENGINE=InnoDB;
Значения NULL и NOT NULL
Если для столбца указано значение NULL, тогда пустые строки будут добавляться в таблицу. И наоборот, если столбец определяется как NOT NULL, тогда пустые строки не будут добавлены.
Первичные ключи
Первичный ключ — это столбец, используемый для идентификации записей в таблице. Значение столбца первичного ключа должно быть уникальным. Если несколько столбцов объединены в первичный ключ, то комбинация значений ключей должна быть уникальной для каждой строки.
Первичный ключ определяется с помощью оператора PRIMARY KEY во время создания таблицы. Если используется несколько столбцов, они разделяются запятой:
PRIMARY KEY (имя_столбца, имя_столбца...)
В следующем примере создается таблица с использованием двух столбцов в качестве первичного ключа:
CREATE TABLE product (prod_code INT NOT NULL AUTO_INCREMENT, prod_name char(30) NOT NULL, prod_desc char(60) NULL, PRIMARY KEY (prod_code, prod_name)) ENGINE=InnoDB;
AUTO_INCREMENT
Когда столбец определяется с помощью AUTO_INCREMENT, его значение автоматически увеличивается каждый раз, когда в таблицу добавляется новая запись. Это удобно при использовании столбца в качестве первичного ключа. Благодаря AUTO_INCREMENTне нужно писать инструкции SQL для вычисления уникального идентификатора для каждой строки.
AUTO_INCREMENT может быть присвоен только одному столбцу в таблице. И он должен быть проиндексирован (например, объявлен в качестве первичного ключа).
Значение AUTO_INCREMENT для столбца можно переопределить, указав новое при выполнении инструкции INSERT.
Можно запросить у MySQL самое последнее значение AUTO_INCREMENT, используя функцию last_insert_id() следующим образом:
SELECT last_insert_value();
Определение значений по умолчанию при создании таблицы
Значения по умолчанию используются, когда значение не определено при вставке в базу данных.
Значения по умолчанию задаются с помощью ключевого слова DEFAULT в операторе CREATE TABLE. Например, приведенный ниже запрос SQL задает значение по умолчанию для столбца sales_quantity:
CREATE TABLE sales { sales_number int NOT_NULL, sales_quantity int NOT_NULL DEFAULT 1, sales_desc char(20) NOT_NULL, PRIMARY KEY (sales_number)) ENGINE=MyISAM;
Типы движков баз данных MySQL
Каждый из примеров создания таблицы в этой статье до этого момента включал в себя определение ENGINE= . MySQL поставляется с несколькими различными движками баз данных, каждый из которых имеет свои преимущества. Используя директиву ENGINE =, можно выбрать, какой движок использовать для каждой таблицы. В настоящее время доступны следующие движки баз данных MySQL:
- InnoDB — был представлен вMySQL версии 4.0 и классифицирован как безопасная среда для транзакций.Ее механизм гарантирует, что все транзакции будут завершены на 100%. При этом частично завершенные транзакции (например, в результате отказа сервера или сбоя питания) не будут записаны. Недостатком InnoDB является отсутствие поддержки полнотекстового поиска.
- MyISAM — высокопроизводительный движок с поддержкой полнотекстового поиска. Эта производительность и функциональность обеспечивается за счет отсутствия безопасности транзакций.
- MEMORY — с точки зрения функционала эквивалентен MyISAM, за исключением того, что все данные хранятся в оперативной памяти, а не на жестком диске. Это обеспечивает высокую скорость обработки. Временный характер данных, сохраняемых в оперативной памяти, делает движок MEMORY более подходящим для временного хранения таблиц.
Движки различных типов могут сочетаться в одной базе данных. Например, некоторые таблицы могут использовать движок InnoDB, а другие — MyISAM. Если во время создания таблицы движок не указывается, то по умолчанию MySQL будет использовать MyISAM.
Чтобы указать тип движка, который будет использоваться для таблицы, о поместите соответствующее определение ENGINE= после определения столбцов таблицы:
CREATE TABLE tmp_orders { tmp_number int NOT_NULL, tmp_quantity int NOT_NULL, tmp_desc char(20) NOT_NULL, PRIMARY KEY (tmp_number)) ENGINE=MEMORY;
Данная публикация представляет собой перевод статьи «Creating Databases and Tables Using SQL Commands » , подготовленной дружной командой проекта
В предыдущем уроке мы узнали, как создать соединение с сервером БД. Следующий шаг - создание БД и таблиц.
Рассмотрим два способа создания БД и таблиц. Первый, как это делается в PHP, второй - с помощью более дружественной утилиты PhpMyAdmin, которая является стандартной на большинстве вэб-хостов и в XAMPP.
Если у вас на хосте есть сайт с поддержкой PHP и MySQL, БД, возможно, уже создана для вас, и вы можете пропустить это раздел урока и начать создавать таблицы. Не забывайте консультироваться со страницами помощи хоста.
Создание БД и таблиц с помощью PHP
Функция mysql_query используется для отправки запроса БД MySQL. Запросы записываются на языке S tructured Q uery L anguage (SQL). SQL - наиболее широко распространённый язык запросов БД - не только для БД MySQL - он очень логичен и прост для изучения. В этом и следующем уроках вы узнаете всё самое важное о SQL-запросах.
При создании БД используется SQL-запрос CREATE DATABASE с таким синтаксисом:
CREATE DATABASE имя БД
Легко и просто, не так ли!? Попробуем вставить это в PHP-скрипт:
Mysql_connect("mysql.myhost.com", "user", "sesame") or die(mysql_error()); mysql_query("CREATE DATABASE mydatabase") or die(mysql_error()); mysql_close();
Сначала соединяемся с MySQL-сервером. Затем создаём БД "mydatabase". И наконец - закрываем соединение с MySQL-сервером.
Пока неплохо... но всё несколько усложняется, когда мы хотим создать таблицы в PHP. При создании таблиц мы используем SQL-запрос CREATE TABLE с таким синтаксисом:
CREATE TABLE имя таблицы (имя_столбца1 ТИП_ДАННЫХ, имя_столбца2 ТИП_ДАННЫХ, имя_столбца3 ТИП_ДАННЫХ, ...)
имя_таблицы и имя_столбца это, понятное дело, соответствующие имена. ТИП_ДАННЫХ используется для специфицирования типа данных, вставляемых в столбец. Вот наиболее распространённые типы:
Целые числа Десятичные числа Краткий текст до 255 символов длиной Обычный текст до 65,535 символов Длинные куски текста до 4,294,967,295 символов Даты в формате ГГГГ-MM-ДД Время в формате ЧЧ:MM:СС Время и дата в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС
В целом логично и относительно просто. Попробуем вставить это в наш пример:
Mysql_connect("mysql.myhost.com", "user", "sesame") or die(mysql_error()); mysql_select_db("people") or die(mysql_error()); mysql_query("CREATE TABLE MyTable (id INT AUTO_INCREMENT, FirstName CHAR, LastName CHAR, Phone INT, BirthDate DATE PRIMARY KEY(id))") Or die(mysql_error()); mysql_close ();
В этом примере мы начинаем с соединения с MySQL-сервером. Затем используем функцию для выбора БД "people". Далее создаём таблицу "persons" из 5 столбцов.
Обратите внимание, что для столбца "id" мы используем для специфицирования того, что столбец содержит числа, а затем добавляем для автоматического инкремента этих чисел и гарантирования тог, что для каждого ряда будет сгенерирован уникальный ID.
В конце мы используем для установки столбца "id" как первичного ключа/primary key. Первичный ключ уникально идентифицирует каждую запись (/ряд) в таблице, что особенно пригодится при последующих обновлениях БД.
Создание БД и таблиц с использованием phpMyAdmin
Бывает полезно иметь возможность создавать БД и таблицы напрямую в PHP. Но часто проще использовать phpMyAdmin (или любую иную утилиту администрирования MySQL), которая является стандартной на большинстве вэб-хостов и в XAMPP. Скриншот ниже показывает, как создавать БД и таблицы с применением phpMyAdmin.
Последнее обновление: 04.05.2018
Создание таблицы
Для создания таблиц используется команда CREATE TABLE . Эта команды применяет ряд операторов, которые определяют столбцы таблицы и их атрибуты. Общий формальный синтаксис команды CREATE TABLE:
CREATE TABLE название_таблицы (название_столбца1 тип_данных атрибуты_столбца1, название_столбца2 тип_данных атрибуты_столбца2, ................................................ название_столбцаN тип_данных атрибуты_столбцаN, атрибуты_уровня_таблицы)
После команды CREATE TABLE идет название таблицы. Имя таблицы выполняет роль ее идентификатора в базе данных, поэтому оно должно быть уникальным. Затем в скобках перечисляются названия столбцов, их типы данных и атрибуты. В самом конце можно определить атрибуты для всей таблицы. Атрибуты столбцов, а также атрибуты таблицы указывать необязательно.
Создадим простейшую таблицу. Для этого выполним следующий скрипт:
CREATE DATABASE productsdb; USE productsdb; CREATE TABLE Customers (Id INT, Age INT, FirstName VARCHAR(20), LastName VARCHAR(20));
Таблица не может создаваться сама по себе. Она всегда создается в определенной базе данных. Вначале здесь создается база данных productsdb. И затем, чтобы указать, что все дальнейшие операции, в том числе создание таблицы, будут производиться с этой базой данных, применяется команда USE .
Далее собственно идет создание таблицы, которая называется Customers. Она определяет четыре столбца: Id, Age, FirstName, LastName. Первые два столбца представляют идентификатор клиента и его возраст и имеют тип INT , то есть будут хранить числовые значения. Следующие столбцы представляют имя и фамилию клиента и имеют тип VARCHAR(20) , то есть представляют строку длиной не более 20 символов. В данном случае для каждого столбца определены имя и тип данных, при этом атрибуты столбцов и таблицы в целом отсутствуют.
И в результате выполнения этой команды будет создана база данных productsdb, в которой будет создана таблица Customers.
Переименование таблиц
Если после создания таблицы мы захотим ее переименовать, то для этого нужно использовать команду RENAME TABLE , которая имеет следующий синтаксис:
RENAME TABLE старое_название TO новое_название;
Например, переименуем таблицу Customers в Clients:
RENAME TABLE Customers TO Clients;
Полное удаление данных
Для полного удаления данных, очистки таблицы применяется команда TRUNCATE TABLE . Например, очистим таблицу Clients:
TRUNCATE TABLE Clients;
Удаление таблиц
Для удаления таблицы из БД применяется команда DROP TABLE , после которой указывается название удаляемой таблицы. Например, удалим таблицу Clients.
Вот универсальный синтаксис SQL для создания таблиц MySQL:
CREATE TABLE table_name (column_name column_type);
Теперь, мы создадим следующую таблицу в учебники базы данных.
Tutorials_tbl(tutorial_id INT NOT NULL AUTO_INCREMENT,
tutorial_title VARCHAR(100) NOT NULL,
tutorial_author VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY (tutorial_id));
Вот несколько пунктов, которые нуждаются в пояснении:
- Поле атрибута не равно NULL , используется потому что мы не хотим, чтобы она была нулем. Поэтому если пользователь попытается создать запись со значением NULL, то MySQL будет вызвана ошибка.
- Поле атрибута auto_increment в MySQL не говорит, чтобы идти вперед и добавить следующий доступный номер в поле ID.
- Ключевое слово первичный ключ используется для определения столбца в качестве первичного ключа. Вы можете использовать несколько столбцов, разделенных запятыми, чтобы определить первичный ключ.
Создание таблиц из командной строки:
Это легко создать MySQL таблицу из MySQL> подсказка. Вы будете использовать команды SQL создать таблицу чтобы создать таблицу.
Пример:
Вот пример, который создает tutorials_tbl:
Root@host# mysql -u root -p Enter password:******* mysql> use TUTORIALS; Database changed mysql> CREATE TABLE tutorials_tbl(-> tutorial_id INT NOT NULL AUTO_INCREMENT, -> tutorial_title VARCHAR(100) NOT NULL, -> tutorial_author VARCHAR(40) NOT NULL, -> submission_date DATE, -> PRIMARY KEY (tutorial_id) ->); Query OK, 0 rows affected (0.16 sec) mysql>
Создание таблиц с помощью PHP скрипта:
Чтобы создать новую таблицу в любой существующей базы данных необходимо использовать функции PHP функции mysql_query(). Вы будете проходить свой второй аргумент при правильной команды SQL для создания таблицы.
Пример:
Вот пример создания таблицы с помощью PHP скрипта:
Creating MySQL Tables
";
$sql = "CREATE TABLE tutorials_tbl(".
"tutorial_id INT NOT NULL AUTO_INCREMENT, ".
"tutorial_title VARCHAR(100) NOT NULL, ".
"tutorial_author VARCHAR(40) NOT NULL, ".
"submission_date DATE, ".
"PRIMARY KEY (tutorial_id)); ";
mysql_select_db("TUTORIALS");
$retval = mysql_query($sql, $conn);
if(! $retval)
{
die("Could not create table: " . mysql_error());
}
echo "Table created successfullyn";
mysql_close($conn);
?>
Еще примеры:
CREATE TABLE IF NOT EXISTS `users` (`id` int(11) NOT NULL auto_increment,
`role_id` int(11) NOT NULL default "1",
`username` varchar(25) collate utf8_bin NOT NULL,
`password` varchar(34) collate utf8_bin NOT NULL,
`email` varchar(100) collate utf8_bin NOT NULL,
`banned` tinyint(1) NOT NULL default "0",
`ban_reason` varchar(255) collate utf8_bin default NULL,
`newpass` varchar(34) collate utf8_bin default NULL,
`newpass_key` varchar(32) collate utf8_bin default NULL,
`newpass_time` datetime default NULL,
`last_ip` varchar(40) collate utf8_bin NOT NULL,
`last_login` datetime NOT NULL default "0000-00-00 00:00:00",
`created` datetime NOT NULL default "0000-00-00 00:00:00",
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3 ;
//С двумя ключами:
CREATE TABLE IF NOT EXISTS `ci_sessions` (session_id varchar(40) DEFAULT "0" NOT NULL,
ip_address varchar(16) DEFAULT "0" NOT NULL,
user_agent varchar(120) NOT NULL,
last_activity int(10) unsigned DEFAULT 0 NOT NULL,
user_data text NOT NULL,
PRIMARY KEY (session_id),
KEY `last_activity_idx` (`last_activity`));
В отдельных статьях расписаны другие основы работы с базами данных и не только MySQL.
Создание таблицы MySQL через PHP и другие подробности
Теперь рассказываю более подробно для любознательных, где и что куда совать.
Итак, давайте создадим простенькую таблицу, чтобы было легче понимать материал без лишнего фанатизма и умных слов. Ниже приведен скрипт, который легко можно выполнить в закладке SQL панели управления phpmyadmin, для этого достаточно всего лишь скопировать текст и нажать кнопку выполнить:
CREATE TABLE `base_name`.`table_name` (`id` INT NULL AUTO_INCREMENT COMMENT "ID номер записи", `text_` VARCHAR(500) NULL COMMENT "Текстовое поле", PRIMARY KEY (`id`)) ENGINE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT="Экспериментальная таблица";
Теперь в нашей базе данных (БД) есть таблица с именем `table_name`
Несколько важных замечаний.
- Возьмите в привычку сразу заполнять комментарии к полям, это позволит вам выработать привычку комментирования и впоследствии не будет проблем при разработке больших и динамических полей, так как я сомневаюсь в том, что в вы наизусть будете помнить 10 тысяч полей и для чего и на кой каждое поле нужно.
- Забудьте про все ранее существующие кодировки и работайте только в UTF. перед остальными очевидны, читайте информацию по ссылке.
- Все поля пишите либо только маленькими, либо только большими буквами. К сожалению PHP придирчив к регистру и в случае, если регистр будет скакать, то многие скрипты и поля могут не выводиться у вас, либо выводиться, но не корректно.
Вот как будет выглядеть уже совсем готовый код HTML страницы:
Запись id=".$row["id"].". Текст: ".$row["text_"].""; }// /while ?>
Проблема с UTF-8 кодировкой
Как показала статистика, это настолько популярная проблема, что я решил вывести ее в отдельную главу. Но на самом деле нет никаких проблем, только сразу после процедуры соединения с базой данных добавьте три строчки, выделенные внизу, отвечающие за переключение кодировки выводимых результатов, вот пример:
// установка связи с сервером (@ подавление возможных ошибок) @mysql_connect($sdd_db_host,$sdd_db_user,$sdd_db_pass); // переключение на нужную базу данных @mysql_select_db($sdd_db_name); // установка кодировки, при помощи которой отправляются данные от PHP скрипта в MySQL @mysql_query("set character_set_client="utf8""); // установка кодировки, в которой выдаются данные от БД MySQL в PHP скрипт @mysql_query("set character_set_results="utf8""); // установка кодировки, в которой будут посылаться служебные команды для сервера @mysql_query("set collation_connection="utf8_general_ci"");
Многие употребляют строчку:
Mysql_query("set NAMES utf8");
Это действует, но в следующих версиях MySQL данный вызов будет отменен. Об этом заявлено на сайте MySQL, поэтому крайне не рекомендуется использовать данную форму переключения кодировки.
Если вы совсем потеряли рассудок и решили вернуться к программированию в 90 годы и мертвую кодировку cp1251 считаете своим идеалом то, просто замените UTF на соответствующие значения cp1251 и cp1251_general_ci .
Неприятно, но факт, люди которые занимаются программированием, особенно в последнее время, совершенно разучились читать сопроводительную документацию, где большая часть проблем описана.
Основные проблемы и хитрости работы с таблицами в MySQL через PHP
Вот некоторые хитрости которые также часто возникают у новичков:
Зачем нужен знак @ перед командами mysql?
Этот знак подавляет вывод информации об ошибках на движке. Так как статья для новичков, то до корректной вы дойдете еще не скоро, поэтому чтобы вас не грузить морем лишней информации и ни что не отвлекало я ставлю этот знак.
Что можно сделать по сложнее?
По сложнее есть целый на этом сайте и также расписаны основные и самые распространенные , которых для началу на первых год изучения вам вполне будет достаточно на все ваши нужды.
Спасибо diego_lott26 за указание ошибки в коде.
