SQL Создаем базу данных

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

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

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

CREATE DATABASE имя;

Назовем нашу базу League, что означает Лига, так что нам нужно выполнить команду

CREATE DATABASE League;

Все значения для базы данных будут выбраны по умолчанию. В зависимости от базы данных настройки могут быть разные и по MS SQL я достаточно подробно рассказал здесь Transact-SQL

Все, что касается конкретных баз данных я стараюсь опускать в этой работе, потому что не хочу привязываться к конкретной реализации, моя цель рассказать базовый язык SQL. Но тут я не могу удержаться и сказать очень важную вещь – кодировка (charset) и сравнения (COLLATION).

Что такое charset? Это кодировка, которая определяет, как будут храниться строки. Можно выбрать какую-то определенную кодировку, которая будет хранить только латинские буквы, если вам никогда не понадобиться хранить какие-либо другие символы и тогда можно в качестве кодировки выбрать латинский алфавит latin1. Но латинские буквы – это только буквы, а в алфавите они могут находиться немного в разных позициях и нам нужно сказать конкретнее – какой алфавит используется для сравнения строк. Сравнение строк влияет на поиск и сортировку.

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

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata 
WHERE schema_name = 'league';

Здесь мы просим сервер показать нам три колонки schema_name, default_character_set_name и default_collation_name из таблицы schemata базы данных information_schema, где имя схемы (schema_name) равна League:

+-------------+----------------------------+------------------------+
| schema_name | default_character_set_name | default_collation_name |
+-------------+----------------------------+------------------------+
| league      | latin1                     | latin1_swedish_ci      |
+-------------+----------------------------+------------------------+

Тут у нас есть серьезная проблема. Раз вы читаете это, то скорей всего знаете русский язык и если вы будете писать приложение, которое хранит данные на русском, то такая база будет неудобно. По умолчанию она использует латинскую кодировку latin1, а в ней нет русских букв. При сравнении используется шведский алфавит и буквы ci говорят о том, что база данных будет игнорировать регистр.

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

Кодировку можно создать сразу при создании базы. Для MySQL это выглядит так:

CREATE DATABASE League
CHARACTER SET latin1 COLLATE latin1_general_cs;

Здесь я снова выбрал латинскую раскладку, но в данном случае уже сказал, что при сравнении строк НУЖНО учитывать регистр.

CREATE DATABASE League 
CHARSET=utf8 COLLATE=utf8_general_ci

В данном случае я выбрал уже utf8 – современная Unicode кодировка, которая позволяет хранить русские буквы. Для сортировки здесь выбрана базовая (general) версия не привязанная к регистру (ci).

У нас база данных уже создана и если выполнить эту команду, то она завершится ошибкой. Проблему можно решить двумя способами – удалить базу данных и создать заново. Чтобы удалить выполняем команду:

DROP DATABASE League;

И теперь мы можем создать базу данных заново. А что, если в этой базе данных уже есть таблицы? Удаление приведет к тому, что база данных потеряет данные. Настройки уже существующей базы данных можно обновить, если выполнить команду

ALTER DATABASE базадынных НОВЫЕ ЗНАЧЕНИЯ

Alter можно воспринимать как – подправить, то есть мы говорим – подправить базу данных. В качестве новых значений можно указать то же самое, что мы можем указать и при создании. Так что команда будет выглядеть так:

ALTER DATABASE League CHARSET=utf8 COLLATE=utf8_general_ci;

Проверяем результат:

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata 
WHERE schema_name = 'league';
+-------------+----------------------------+------------------------+
| schema_name | default_character_set_name | default_collation_name |
+-------------+----------------------------+------------------------+
| league      | utf8                       | utf8_general_ci        |
+-------------+----------------------------+------------------------+

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

Изменение кодировки базы данных повлияет на все новые создаваемые объекты. Если есть существующие таблицы, которые уже используют latin1 кодировку, то они останутся такими. Таблицы придется подправить вручную.

При создании можно сделать еще более тонкие настройки, но это уж точно я оставлю пока, потому что администрирование конкретно MySQL – это отдельная тема. Мы рассматриваем максимально классический SQL язык.

Если база данных уже существует, то мы можем поменять ее настройки с помощью команды alter database – подправить базу данных. Давайте изменим набор символов и collation для testdb базы данных:

ALTER DATABASE testdb
CHARACTER SET utf8 COLLATE utf8_general_ci;

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

Что произошло, можем ли мы теперь добавлять в таблице телефонов из базы данных testdb русские слова? Нет, потому что изменение таким образом настроек базы данных не меняет существующие таблицы. Существующие таблицы останутся с теми настройками, которые они получили при создании, а вот если вы будете создавать новую, то она уже будет иметь utf8 кодировку.