SQL является одним из самых популярных языков для работы с базами данных. Оператор CREATE DATABASE позволяет создавать новые базы данных и настраивать их параметры. Давайте разберем синтаксис и возможности этого оператора на примере SQL Server и PostgreSQL.
Общие сведения об операторе CREATE DATABASE
Оператор CREATE DATABASE используется для создания новой базы данных SQL. Он позволяет указать основные параметры базы данных:
- Имя базы данных
- Владелец базы данных
- Шаблон, на основе которого создается база данных
- Кодировку данных по умолчанию
- Локаль для сортировки и форматирования данных
- Файлы данных и журналов транзакций
- Дополнительные параметры, такие как уровень изоляции транзакций, настройки производительности и др.
Рассмотрим базовый синтаксис оператора CREATE DATABASE:
CREATE DATABASE имя_базы_данных [OWNER владелец] [TEMPLATE шаблон] [ENCODING кодировка] [LC_COLLATE параметры_сортировки] [LC_CTYPE параметры_классификации_символов] [TABLESPACE табличное_пространство] [CONNECTION LIMIT предел_соединений]; Здесь в квадратных скобках указаны необязательные параметры. Рассмотрим их подробнее в следующих разделах.
Помимо создания базы данных из шаблона, оператор CREATE DATABASE может использоваться для:
- Создания базы данных из файлов существующей базы данных
- Добавления файлов к существующей базе данных
- Изменения параметров существующей базы данных
Имя базы данных
При создании базы данных необходимо указать ее имя, которое должно быть уникальным в пределах SQL Server или PostgreSQL.
Существуют определенные правила именования баз данных:
- Имя может содержать буквы, цифры и символы # $ _
- Первый символ должен быть буквой
- Регистр символов не учитывается
- Длина имени ограничена 63 символами для PostgreSQL и 128 символами для SQL Server
Длинные имена баз данных могут приводить к проблемам. Например, для SQL Server:
- Если имя превышает 123 символа, не получится автоматически создать файл журнала
- Максимальная длина полного пути к файлам данных не должна превышать 260 символов
Поэтому рекомендуется выбирать короткие и запоминающиеся имена баз данных.
Владелец базы данных
При создании базы данных можно явно указать ее владельца - пользователя, который получает все права на эту базу данных и сможет управлять правами других пользователей.
Например, для SQL Server можно указать в качестве владельца существующего пользователя или роль сервера:
CREATE DATABASE mydatabase OWNER = dbo; Для PostgreSQL доступны аналогичные предопределенные роли, такие как postgres, pg_signal_backend и другие.
Если владелец не указан явно, он определяется по умолчанию:
- Для SQL Server это пользователь, выполнивший оператор CREATE DATABASE
- Для PostgreSQL это пользователь, указанный в параметре CREATEUSER в файле pg_hba.conf
При необходимости владельца базы данных можно изменить с помощью оператора ALTER AUTHORIZATION.
Шаблон базы данных
При создании базы данных можно указать шаблон, на основе которого она будет сгенерирована. Шаблон представляет собой существующую базу данных, которая копируется с определенными настройками.
В SQL Server и PostgreSQL по умолчанию используются следующие системные шаблоны:
- template1 - в PostgreSQL
- template0 - "чистый" шаблон в PostgreSQL без объектов и изменений
- model - в SQL Server
- msdb - шаблон для системных баз данных в SQL Server
Кроме того, можно создавать собственные шаблоны баз данных с нужными объектами и настройками.
Кодировка и локализация
Для хранения текстовых данных необходимо выбрать кодировку, например UTF-8, WIN1251 и многие другие. Кодировка должна соответствовать выбранной локали.
Локаль определяет язык и стандарты представления данных, используемые по умолчанию. Локаль влияет на сортировку текста и классификацию символов.
Настройки локали задаются параметрами LC_COLLATE и LC_CTYPE. Например, для русского языка можно указать:
LC_COLLATE = 'ru_RU.UTF-8'; LC_CTYPE = 'ru_RU.UTF-8'; Важно, чтобы кодировка и локаль совпадали с настройками шаблона базы данных.
Файлы и файловые группы
Данные и журнал транзакций SQL базы данных хранятся в файлах на диске. Файлы объединяются в файловые группы.
При создании базы данных можно явно указать файлы и их расположение. Например, для SQL Server:
LOG ON ( NAME = 'AdventureWorks_Log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Log.ldf' ) Если файлы не указаны, SQL Server и PostgreSQL создадут их автоматически в стандартной директории.
Параметры базы данных
CREATE DATABASE позволяет настроить множество параметров базы данных:
- Уровень изоляции транзакций
- Режим восстановления
- Размер кеша запросов
- Настройки производительности и многое другое
Mysql create database определяет множество параметров по умолчанию. Но при необходимости их можно переопределить вручную.
Например, для увеличения производительности запросов имеет смысл увеличить размер буфера индексных страниц:
CREATE DATABASE test WITH MAXDOP = 2, INDEX_PAGE_CONSTRUCTION = ON; Типичные ошибки
Иногда при попытке "create database user" возникают ошибки:
- "Отказано в доступе"
- "Синтаксическая ошибка"
- "Нарушение целостности данных"
Это может происходить по разным причинам:
- Отсутствие прав на создание базы данных
- Некорректное имя базы данных
- Переполнение диска
- Неверные настройки шаблона
Для диагностики проблем очень полезны журналы ошибок СУБД. Например, журнал alert.log в "Oracle create database".
Рекомендации по созданию базы данных
Чтобы избежать типичных ошибок при "create database user", рекомендуется придерживаться следующих правил:
- Использовать уникальное, но короткое и запоминающееся имя базы данных
- Явно назначить владельца базы данных с необходимыми привилегиями
- Выбрать подходящий шаблон в зависимости от требований
- Указать корректную кодировку и локализацию
- При необходимости определить файлы и файловые группы
- Настроить параметры производительности и безопасности
Мониторинг базы данных
После создания базы данных важно организовать мониторинг ее работы - фиксировать ошибки, отслеживать производительность, контролировать целостность данных.
Для этого можно использовать журналы СУБД, а также сторонние инструменты мониторинга. Они помогут своевременно обнаружить и устранить проблему.
Резервное копирование
Настоятельно рекомендуется настроить резервное копирование базы данных сразу после ее создания.
Это позволит восстановить данные в случае сбоя при "create database failed" или аварийной ситуации.
Существуют встроенные механизмы резервного копирования для SQL Server, PostgreSQL и других СУБД.
Удаление базы данных
Иногда возникает необходимость полностью удалить базу данных из SQL Server или другой СУБД.
Это можно сделать при помощи оператора DROP DATABASE. При этом будут безвозвратно удалены все данные.
Поэтому перед удалением очень важно убедиться, что резервная копия существует или данные больше не нужны "user".
Изменение параметров базы данных
После создания базы данных иногда возникает необходимость изменить ее параметры.
Например, может потребоваться изменить кодировку, параметры локализации, размер файлов данных или журнала, настройки производительности.
Для этого используется оператор ALTER DATABASE. Он позволяет модифицировать многие параметры существующей базы данных, не удаляя данные.
Присоединение базы данных
Иногда возникает потребность подключить ранее отсоединенные файлы базы данных SQL Server, PostgreSQL или другой СУБД.
Это можно сделать с помощью того же оператора CREATE DATABASE, но с предложением FOR ATTACH. Подробная инструкция описана в документации.
Миграция базы данных
Для миграции базы данных на новый сервер можно экспортировать базу данных, а затем импортировать ее на целевой сервер.
Также возможен вариант с резервным копированием и последующим восстановлением базы данных на новом сервере.
Существуют и сторонние инструменты для миграции баз данных между разными платформами СУБД.
