Первичные ключи
Первичный ключ (Primary Key) — это особый тип индекса, который является идентификатором записей в таблице. Он обязательно уникальный и указывается при создании таблиц:
CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(128) NOT NULL, `name` varchar(128) NOT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf-8
При использовании таблиц InnoDB всегда определяйте первичные ключи. Если первичного ключа нет, MySQL все равно создаст виртуальный скрытый ключ.
Кластерные индексы
Обычные индексы являются некластерными. Это означает, что сам индекс хранит только ссылки на записи таблицы. Когда происходит работа с индексом, определяется только список записей (точнее список их первичных ключей), подходящих под запрос. После этого происходит еще один запрос — для получения данных каждой записи из этого списка.
Кластерные индексы сохраняют данные записей целиком, а не ссылки на них. При работе с таким индексом не требуется дополнительной операции чтения данных.
Первичные ключи таблиц InnoDB являются кластерными. Поэтому выборки по ним происходят очень эффективно.
Просмотр заблокированных строк в 1С
Ввиду своей деятельности, мне часто приходится рассказывать про различные аспекты оптимизации и в том числе про блокировки.
Очень часто слушатели задают следующие вопросы:
Как посмотреть в реальном времени, какие именно данные сейчас заблокированы?
Как понять, что сейчас заблокировано в терминах 1С?
Если гранулярность блокировки страница, как увидеть, какие данные в ней находятся?
Раньше приходилось отвечать, что инструмента, который показывает все вышеописанное, сейчас просто нет. Но потом мне это надоело, и я решил сделать собственный инструмент, который позволяет ответить на все вышеописанные вопросы.
1 стартмани
25.10.2016
48073
863
Andreynikus
68
Возможные ошибки
Input file appears to be a text format dump. please use psql.
Причина: дамп сделан в текстовом формате, поэтому нельзя использовать утилиту pg_restore.
Решение: восстановить данные можно командой psql <имя базы> < <файл с дампом> или выполнив SQL, открыв файл, скопировав его содержимое и вставив в SQL-редактор.
No matching tables were found
Причина: Таблица, для которой создается дамп не существует. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру.
Решение: проверьте, что правильно написано название таблицы и нет лишних пробелов.
Причина: Утилита pg_dump чувствительна к лишним пробелам.
Решение: проверьте, что нет лишних пробелов.
Aborting because of server version mismatch
Причина: несовместимая версия сервера и утилиты pg_dump. Может возникнуть после обновления или при выполнении резервного копирования с удаленной консоли.
Решение: нужная версия утилиты хранится в каталоге /usr/lib/postgresql/<version>/bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить.
No password supplied
Причина: нет системной переменной PGPASSWORD или она пустая.
Решение: либо настройте сервер для предоставление доступа без пароля в файле pg_hba.conf либо экспортируйте переменную PGPASSWORD (export PGPASSWORD или set PGPASSWORD).
Неверная команда \
Причина: при выполнении восстановления возникла ошибка, которую СУБД не показывает при стандартных параметрах восстановления.
Решение: запускаем восстановление с опцией -v ON_ERROR_STOP=1, например:
psql -v ON_ERROR_STOP=1 users < /tmp/users.dump
Теперь, когда возникнет ошибка, система прекратит выполнять операцию и выведет сообщение на экран.
Групповые привилегии
Роль получает привилегии своих групповых ролей. Нужно ли ей будет для получения привилегий выполнять SET ROLE зависит от атрибута роли, который мы можем указать при создании роли, как было показано на предыдущем уроке:
- INHERIT – атрибут роли, который включает автоматическое наследование привилегий;
- NOINHERIT – атрибут роли, который требует явное выполнение SET ROLE.
В 13 PostgreSQL при инициализации кластера создаются следующие роли вместе с суперпользователем postgres:
- pg_signal_backend – право посылать сигналы обслуживающим процессам, например можно вызвать функцию pg_reload_conf() или завершить процесс с помощью функции pg_terminate_backend();
- pg_read_all_settings – право читать все конфигурационные параметры, даже те, что обычно видны только суперпользователям;
- pg_read_all_stats – право читать все представления pg_stat_* и использовать различные расширения, связанные со статистикой, даже те, что обычно видны только суперпользователям;
- pg_stat_scan_tables – право выполнять функции мониторинга, которые могут устанавливать блокировки в таблицах, возможно, на длительное время;
- pg_monitor – право читать и выполнять различные представления и функции для мониторинга. Эта роль включена в роли pg_read_all_settings, pg_read_all_stats и pg_stat_scan_tables;
- pg_read_server_files – право читать файлы в любом месте файловой системы, куда имеет доступ postgres на сервере. А также выполняя копирование и другие функции работы с файлами;
- pg_write_server_files – право записывать файлы в любом месте файловой системы, куда имеет доступ postgres на сервере. А также выполнять копирование и другие функции работы с файлами.
- pg_execute_server_program – право выполнять программы на сервере (от имени пользователя, запускающего СУБД).
Псевдо роль public
Псевдо роль public не видна, но про неё следует знать. Это групповая роль, в которую включены все остальные роли. Это означает, что все роли по умолчанию будут иметь привилегии наследуемые от public. Поэтому иногда у public отбирают некоторые привилегии, чтобы отнять их у всех пользователей.
Роль public по умолчанию имеет следующие привилегии:
-
для всех баз данных:
- CONNECT – это означает что любая созданная роль сможет подключаться к базам данных, но не путайте с привилегией LOGIN;
- TEMPORARY – любая созданная роль сможет создавать временные объекты во всех база данных и объекты эти могут быть любого размера;
-
для схемы public:
- CREATE (создание объектов) – любая роль может создавать объекты в этой схеме;
- USAGE (доступ к объектам) – любая роль может использовать объекты в этой схеме;
-
для схемы pg_catalog и information_schema
USAGE (доступ к объектам) – любая роль может обращаться к таблицам системного каталога;
:
-
для всех функций
EXECUTE (выполнение) – любая роль может выполнять любую функцию. Ещё нужны ещё права USAGE на ту схему, в которой функция находится, и права к объектам к которым обращается функция.
:
Это сделано для удобства, но снижает безопасность сервера баз данных.
Настройка потребление ресурсов PostgreSQL (Resource Consumption (Usage))
Resource Consumption
Resource Consumption(Usage) — за исключением WAL (write ahead log).
shared_buffers = 2GB # ~ 1/8 RAM and for Linux kernel.shmmax=4294967296 (1/4 of RAM) work_mem = 128MB # ~ 1/20 RAM maintenance_work_mem = 1GB # ~ 1/4 RAM effective_cache_size = 4GB # ~ 2/3 RAM max_prepared_transactions = 0 # zero disables the feature
shared_buffers. Объём совместно используемой памяти(разделяемой между процессами PostgreSQL памяти), выделяемой сервером PostgreSQL для кэширования данных, определяется числом страниц (shared_buffers) по 8 килобайт каждая. Естественно, данные умеет кэшировать не только сам PostgreSQL, но и операционная система сама по себе делает это очень неплохо. Поэтому нет необходимости отводить под кэш всю наличную оперативную память. Оптимальное число shared_buffers зависит от многих факторов, нужно учесть количество оперативной памяти компьютера, размер базы данных, число соединений и сложность запросов.
Увеличение числа shared_buffers и других параметров памяти потребует изменения настроек разделяемой памяти (Shared memory) вашей операционной системы (Shared Memory and Semaphores). Параметр ядра Linux kernel.shmmax всегда должен быть больше параметра shared_buffers PostgreSQL.
- work_mem. Под каждый запрос можно выделить личный ограниченный объём памяти для работы. Этот объём может использоваться для сортировки, объединения и других подобных операций. При превышении этого объёма сервер начинает использовать временные файлы на диске, что может существенно замедлить скорость обработки запросов. Предел для work_mem можно вычислить, разделив объём доступной памяти (физическая память минус объём занятый под другие программы и под совместно используемые страницы shared_buffers) на максимальное число одновременно используемых активных соединений. При необходимости, например, выполнения очень объёмных операций, допустимый лимит можно изменять прямо во время выполнения запроса. Поэтому нет нужды изначально задавать теоретический предел.
- effective_cache_size Этот параметр сообщает PostgreSQL примерный объём файлового кэша операционной системы, оптимизатор использует эту оценку для построения плана каждого запроса. Объём задаётся параметром effective_cache_size в postgresql.conf. Единица измерения – блоки величиной 8 кБ. Например, пусть в вашем компьютере 1,5 ГБ памяти, параметр shared_buffers установлен в 32 МБ, а параметр effective_cache_size в 800 МБ. Если запросу нужно 700 МБ данных, то PostgreSQL оценит, что все нужные данные уже есть в памяти и выберет более агрессивный план с использованием индексов и merge joins. Но если effective_cache_size будет всего 200 МБ, то оптимизатор вполне может выбрать более эффективный для дисковой системы план, включающий полный просмотр таблицы. В качестве начального значения можете использовать 25-50% доступной памяти (т.е. не занятой операционной системой и приложениями). Этот параметр в ОС можно посмотреть в настройках: Для Windows: в Диспетчере задач, Закладка Быстродействие, Физическая память-Системный кэш. Для Linux: наберите команду free, необходимое значение в столбце cached (в kB) Данное значение необходимо разделить на количество конкурентных запросов в один момент времени (среднее количество подключений к базе + запас).
- maintenance_work_mem. Эта память используется для выполнения операций по сбору статистики (ANALYZE), сборке мусора (VACUUM), создания индексов (CREATE INDEX) и для добавления внешних ключей (FOREGIN KEY). Размер выделяемой под эти операции памяти должен быть сравним с физическим размером самого большого индекса на диске. Как и в случае work_mem эта переменная может быть установлена прямо во время выполнения запроса.
- max_prepared_transactions. Определяет максимальное число подготовленных транзакций (команда PREPARE TRANSACTION). Подготовленные транзакции выполняются, но результат их не будет доступен пока их не подтвердят (COMMIT). Так же можно такие транзакции и отклонить (ROLLBACK). Если эта сущность нигде не используется, то переменную можно занулить.
Производительность WAL
По умолчанию, каждый раз, когда транзакция фиксирует изменения, результат должен быть сброшен на диск. Для этого вначале страница сбрасывается из буферной памяти на дисковый кэш. А затем выполняется операция fsync для записи страницы на диск. То есть частые COMMIT приводят к частым fsync.
В PostgreSQL есть другой режим работы – асинхронный. При этом каждый COMMIT не приводит к fsync. Вместо этого страницы сбрасываются на диск по расписанию специальным процессом – WALWRITER. Этот процесс периодически просыпается и записывает на диск всё что изменилось за время пока он спал и опять засыпает.
В асинхронном режиме postgresql работает быстрее, но вы можете потерять некоторые данные при сбое.
Режим работы настраивается с помощью конфигурационного файла и настройки не требуют перезагрузки сервера. Это позволяет приложению устанавливать параметры на лету. Некоторые транзакции сразу запишут изменения на диск, то есть поработают в синхронном режиме. Другие транзакции будут работать в асинхронном режиме. Условно можно поделить операции на критичные и не критичные.
Следующие параметры отвечают за режим работы WAL:
- synchronous_commit – on/off – синхронный или асинхронный режим работы;
- wal_writer_delay = 200ms – период сброса на диск wal записей при асинхронном режиме.
Удаление таблицы в базе данных с помощью PHP (PDO)
<?php
$server = «localhost»;
$user = «root»;
$password = «MySafePass4!»;
$db_name = «Bookstore»;
try {
// Открываем соединение
$db = new PDO(«mysql:host=$server;dbname=$db_name», $user, $password);
// Создание исключения при ошибке
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Запрос на удаление таблицы
$sql = «DROP TABLE books»;
// Выполняем запрос
$db->exec($sql);
echo «Таблица успешно удалена!»;
}
catch(PDOException $e) {
echo «Ошибка при удалении таблицы в базе данных: » . $e->getMessage();
}
// Закрываем соединение
$db = null;
?>
|
1 |
<?php $server=»localhost»; $user=»root»; $password=»MySafePass4!»; $db_name=»Bookstore»; try{ // Открываем соединение $db=newPDO(«mysql:host=$server;dbname=$db_name»,$user,$password); // Создание исключения при ошибке $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); // Запрос на удаление таблицы $sql=»DROP TABLE books»; // Выполняем запрос $db->exec($sql); echo»Таблица успешно удалена!»; } catch(PDOException$e){ echo»Ошибка при удалении таблицы в базе данных: «.$e->getMessage(); } // Закрываем соединение $db=null; ?> |
Изменить несколько столбцов в таблице
Синтаксис
Синтаксис для изменения нескольких столбцов в таблице в PostgreSQL (используя ALTER TABLE):
ALTER TABLE table_name
ALTER COLUMN column_name TYPE column_definition,
ALTER COLUMN column_name TYPE column_definition,
…
;
- table_name
- Имя таблицы для изменения.
- column_name
- Имя столбца, который нужно изменить в таблице.
- column_definition
- Измененный тип данных столбца.
Пример
Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице PostgreSQL с помощью оператора ALTER TABLE.
Например:
PgSQL
ALTER TABLE order_details
ALTER COLUMN notes TYPE varchar(500),
ALTER COLUMN quantity TYPE numeric;
|
1 |
ALTERTABLEorder_details ALTERCOLUMNnotesTYPEvarchar(500), ALTERCOLUMNquantityTYPEnumeric; |
В этом примере ALTER TABLE будут изменены два столбца таблицы order_details — notes и quantity.
Поле notes будет изменен на тип данных varchar(500), а столбец quantity будет изменен на тип данных numeric.
Восстановление
Может понадобиться создать базу данных. Это можно сделать SQL-запросом:
=# CREATE DATABASE users WITH ENCODING=’UTF-8′;
* где users — имя базы; UTF-8 — используемая кодировка.
Если мы получим ошибку:
ERROR: encoding «UTF8» does not match locale «en_US»
DETAIL: The chosen LC_CTYPE setting requires encoding «LATIN1».
Указываем больше параметров при создании базы:
CREATE DATABASE users WITH OWNER ‘postgres’ ENCODING ‘UTF8’ LC_COLLATE = ‘ru_RU.UTF-8’ LC_CTYPE = ‘ru_RU.UTF-8’ TEMPLATE = template0;
Синтаксис:
psql <имя базы> < <файл с дампом>
Пример:
psql users < /tmp/users.dump
С авторизацией
При необходимости авторизоваться при подключении к базе вводим:
psql -U dmosk -W users < /tmp/users.dump
* где dmosk — имя учетной записи; опция W потребует ввода пароля.
Из файла gz
Сначала распаковываем файл, затем запускаем восстановление:
gunzip users.dump.gz
psql users < users.dump
Или одной командой:
zcat users.dump.gz | psql users
Определенную базу
Если резервная копия делалась для определенной базы, запускаем восстановление:
psql users < /tmp/database.dump
Если делался полный дамп (всех баз), восстановить определенную можно при помощи утилиты pg_restore с параметром -d:
pg_restore -d users cluster.bak
Определенную таблицу
Если резервная копия делалась для определенной таблицы, можно просто запустить восстановление:
psql users < /tmp/students.dump
Если делался полный дамп, восстановить определенную таблицу можно при помощи утилиты pg_restore с параметром -t:
pg_restore -a -t students users.dump
С помощью pgAdmin
Запускаем pgAdmin — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим восстановить данные — выбираем Восстановить:
Выбираем наш файл с дампом:
И кликаем по Восстановить:
Использование pg_restore
Данная утилита предназначена для восстановления данных не текстового формата (в одном из примеров создания копий мы тоже делали резервную копию не текстового формата).
Из бинарника:
pg_restore -Fc users.bak
Из тарбола:
pg_restore -Ft users.tar
С создание новой базы:
pg_restore -Ft -C users.tar
Создать уникальное ограничение — используя оператор ALTER TABLE
Синтаксис для создания уникального ограничения с помощью оператора ALTER TABLE в PostgreSQL:
PgSQL
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2,… column_n);
|
1 |
ALTERTABLEtable_name ADDCONSTRAINTconstraint_nameUNIQUE(column1,column2,…column_n); |
- table_name
- Имя таблицы для изменения. Это таблица, к которой вы хотите добавить уникальное ограничение.
- constraint_name
- Имя уникального ограничения.
- column1, column2,… column_n
- Столбцы, составляющие уникальное ограничение.
Пример
Рассмотрим пример того, как добавить уникальное ограничение к существующей таблице в PostgreSQL, используя оператор ALTER TABLE.
PgSQL
ALTER TABLE order_details
ADD CONSTRAINT order_unique UNIQUE (order_id);
|
1 |
ALTERTABLEorder_details ADDCONSTRAINTorder_uniqueUNIQUE(order_id); |
В этом примере мы создали уникальное ограничение для существующей таблицы order_details с именем order_unique. Он состоит из поля с именем order_id.
Мы также можем создать уникальное ограничение с более чем одним полем, как в примере ниже:
PgSQL
ALTER TABLE order_details
ADD CONSTRAINT order_date_unique UNIQUE (order_id, order_date);
|
1 |
ALTERTABLEorder_details ADDCONSTRAINTorder_date_uniqueUNIQUE(order_id,order_date); |
Практика
Настроим журнал, чтобы он захватывал все запросы (log_min_duration_statement=0). Дополнительно в качестве префикса укажем “pid=%p“. Затем заставим сервер перечитать конфигурацию и выполним некоторый запрос (SELECT sum(random()) FROM generate_series(1,1000000)). Далее отключимся от сервера и посмотрим журнал:
postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.
postgres@postgres=# ALTER SYSTEM SET log_min_duration_statement=0;
ALTER SYSTEM
Time: 0,972 ms
postgres@postgres=# ALTER SYSTEM SET log_line_prefix='(pid=%p) ';
ALTER SYSTEM
Time: 0,676 ms
postgres@postgres=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
Time: 4,366 ms
postgres@postgres=# SELECT sum(random()) FROM generate_series(1,1000000);
sum
--------------------
499542.50257688144
(1 row)
Time: 169,902 ms
postgres@postgres=# \q
postgres@s-pg13:~$ tail /home/postgres/logfile
2021-06-28 12:46:31.109 MSK ERROR: syntax error at or near "data_lowlevel" at character 1
2021-06-28 12:46:31.109 MSK STATEMENT: data_lowlevel
2021-06-28 15:10:52.758 MSK LOG: received SIGHUP, reloading configuration files
2021-06-28 15:10:52.759 MSK LOG: parameter "track_io_timing" changed to "on"
2021-06-28 15:10:52.759 MSK LOG: parameter "track_functions" changed to "all"
2021-06-28 16:49:47.333 MSK FATAL: terminating connection due to administrator command
2021-06-28 17:10:07.701 MSK LOG: received SIGHUP, reloading configuration files
2021-06-28 17:10:07.702 MSK LOG: parameter "log_min_duration_statement" changed to "0"
(pid=29389) LOG: parameter "log_line_prefix" changed to "(pid=%p) "
(pid=15653) LOG: duration: 169.197 ms statement: SELECT sum(random()) FROM generate_series(1,1000000);
Как видите, теперь в логах есть информация обо всех командах и время, которое они выполнялись!
Более подробно про настройку журнала можете почитать здесь.
Сводка
Имя статьи
Журнал PostgreSQL. Настройка и анализ
Описание
В этой статье разберём журнал PostgreSQL, а именно как его настраивать, что в него можно записывать и как его анализировать
Использование EXPLAIN для анализа индексов
Инструкция EXPLAIN покажет данные об использовании индексов для конкретного запроса. Например:
mysql> EXPLAIN SELECT * FROM users WHERE email = '';
Колонка key показывает используемый индекс. Колонка possible_keys показывает все индексы, которые могут быть использованы для этого запроса. Колонка rows показывает число записей, которые пришлось прочитать базе данных для выполнения этого запроса (в таблице всего 336 записей).
Как видим, в примере не используется ни один индекс. После создания индекса:
mysql> EXPLAIN SELECT * FROM users WHERE email = '';
Прочитана всего одна запись, так как был использован индекс.
Проверка длины составных индексов
Explain также поможет определить правильность использования составного индекса. Проверим запрос из примера (с индексом на колонки age и gender):
mysql> EXPLAIN SELECT * FROM users WHERE age = 29 AND gender = 'male';
Значение key_len показывает используемую длину индекса. В нашем случае 24 байта – длина всего индекса (5 байт age + 19 байт gender).
Если мы изменим точное сравнение на поиск по диапазону, увидим что MySQL использует только часть индекса:
mysql> EXPLAIN SELECT * FROM users WHERE age <= 29 AND gender = 'male';
Это сигнал о том, что созданный индекс не подходит для этого запроса. Если же мы создадим правильный индекс:
mysql> Create index gender_age on users(gender, age); mysql> EXPLAIN SELECT * FROM users WHERE age < 29 and gender = 'male';
В этом случае MySQL использует весь индекс gender_age, т.к. порядок колонок в нем позволяет сделать эту выборку.
Составные индексы
Рассмотрим такой запрос:
SELECT * FROM users WHERE age = 29 AND gender = 'male'
Нам следует создать составной индекс на обе колонки:
CREATE INDEX age_gender ON users(age, gender);
Устройство составного индекса
Чтобы правильно использовать составные индексы, необходимо понять структуру их хранения. Все работает точно так же, как и для обычного индекса. Но для значений используются значения всех входящих колонок сразу. Для таблицы с такими данными:
id | name | age | gender 1 | Den | 29 | male 2 | Alyona | 15 | female 3 | Putin | 89 | tsar 4 | Petro | 12 | male
значения составного индекса будут такими:
age_gender 12male 15female 29male 89tsar
Это означает, что очередность колонок в индексе будет играть большую роль. Обычно колонки, которые используются в условиях WHERE, следует ставить в начало индекса. Колонки из ORDER BY — в конец.
Поиск по диапазону
Представим, что наш запрос будет использовать не сравнение, а поиск по диапазону:
SELECT * FROM users WHERE age <= 29 AND gender = 'male'
Тогда MySQL не сможет использовать полный индекс, т.к. значения gender будут отличаться для разных значений колонки age. В этом случае база данных попытается использовать часть индекса (только age), чтобы выполнить этот запрос:
age_gender 12male 15female 29male 89tsar
Сначала будут отфильтрованы все данные, которые подходят под условие age <= 29. Затем, поиск по значению “male” будет произведен без использования индекса.
Сортировка
Составные индексы также можно использовать, если выполняется сортировка:
SELECT * FROM users WHERE gender = 'male' ORDER BY age
В этом случае нам нужно будет создать индекс в другом порядке, т.к. сортировка (ORDER) происходит после фильтрации (WHERE):
CREATE INDEX gender_age ON users(gender, age);
Такой порядок колонок в индексе позволит выполнить фильтрацию по первой части индекса, а затем отсортировать результат по второй.
Колонок в индексе может быть больше, если требуется:
SELECT * FROM users WHERE gender = 'male' AND country = 'UA' ORDER BY age, register_time
В этом случае следует создать такой индекс:
CREATE INDEX gender_country_age_register ON users(gender, country, age, register_time);
Синтаксис
В простейшем виде синтаксис для оператора CREATE TABLE в PostgreSQL:
CREATE TABLE table_name
(
column1 datatype ,
column2 datatype ,
…
);
Тем не менее, полный синтаксис для оператора CREATE TABLE в PostgreSQL:
CREATE
TABLE table_name
(
column1 datatype
{ NULL
| NOT NULL
| CHECK ( expression )
| DEFAULT default_value
| UNIQUE index_parameters
| PRIMARY KEY index_parameters
| REFERENCES ref_table
}
,
column2 datatype
{ NULL
| NOT NULL
| CHECK ( expression )
| DEFAULT default_value
| UNIQUE index_parameters
| PRIMARY KEY index_parameters
| REFERENCES ref_table
}
,
…
|
{ CHECK ( expression )
| UNIQUE ( index_col_name,… )
| PRIMARY KEY ( index_col_name,… )
| FOREIGN KEY ( index_col_name,… )
REFERENCES another_table_name (index_col_name,…)
| LIKE source_table
{ INCLUDING | EXCLUDING }
{ DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }
);
Параметры или аргументы
- GLOBAL TEMPORARY and GLOBAL TEMP
- Необязательный. Если один из них указан, таблица является глобальной временной таблицей.
- LOCAL TEMPORARY and LOCAL TEMP
- Необязательный. Если один из них указан, таблица является локальной временной таблицей.
- UNLOGGED
- Необязательный. Если указано, данные в таблице не записываются в журнал предварительной записи. Это повышает производительность таблицы, однако данные в этой таблице будут потеряны в случае сбоя.
- IF NOT EXISTS
- Необязательный. Если указано, инструкция CREATE TABLE не вызовет ошибку, если таблицы уже существуют.
- table_name
- Имя таблицы, которую вы хотите создать.
- column1, column2
- Столбцы, которые вы хотите создать в таблице.
- datatype
- Тип данных для столбца.
- CONSTRAINT constraint_name
- Необязательный. Название ограничения.
- NULL or NOT NULL
- Каждый столбец должен быть определен как NULL или NOT NULL. Если этот параметр опущен, база данных принимает NULL в качестве значения по умолчанию.
- DEFAULT default_value
- Необязательный. Это значение, присваиваемое столбцу, если оно оставлено пустым или равно NULL.
Практическое упражнение № 3
На основе приведенной ниже таблицы departments создайте таблицу SQL с именем employees, в которой хранится информация о номере сотрудника, его имени, отделе и зарплате. Первичный ключ для таблицы employees должен быть номером сотрудника. Создайте внешний ключ в таблице employees, которая ссылается на таблицу departments, на основе поля department_id.
PgSQL
CREATE TABLE departments
( department_id int NOT NULL,
department_name char(50) NOT NULL,
CONSTRAINT departments_pk PRIMARY KEY (department_id)
);
|
1 |
CREATETABLEdepartments department_namechar(50)NOT NULL, CONSTRAINTdepartments_pkPRIMARYKEY(department_id) |
Решение для упражнения № 3
Инструкция SQL CREATE TABLE для таблицы employees.
PgSQL
CREATE TABLE employees
( employee_number int NOT NULL,
employee_name char(50) NOT NULL,
department_id int,
salary int,
CONSTRAINT employees_pk PRIMARY KEY (employee_number),
CONSTRAINT fk_departments
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
|
1 |
CREATETABLEemployees employee_namechar(50)NOT NULL, department_idint, salaryint, CONSTRAINTemployees_pkPRIMARYKEY(employee_number), CONSTRAINTfk_departments FOREIGNKEY(department_id) REFERENCESdepartments(department_id) |
Синтаксис
Простая форма синтаксиса оператора CREATE TABLE в MySQL:
CREATE TABLE table_name
(
column1 datatype ,
column2 datatype ,
…
);
Полный синтаксис оператора MySQL CREATE TABLE:
CREATE TABLE table_name
(
column1 datatype
,
column2 datatype
,
…
| ] PRIMARY KEY (index_col_name, …)
| index_name (index_col_name, …)
| ] UNIQUE
(index_col_name, …)
| {FULLTEXT | SPATIAL} index_name (index_col_name, …)
| ]
FOREIGN KEY index_name (index_col_name, …)
REFERENCES another_table_name (index_col_name, …)
| CHECK (expression)
{ENGINE | TYPE} = engine_name
| AUTO_INCREMENT = value
| AVG_ROW_LENGTH = value
| CHARACTER SET = charset_name
| CHECKSUM = {0 | 1}
| COLLATE = collation_name
| COMMENT = ‘string’
| DATA DIRECTORY = ‘absolute path’
| DELAY_KEY_WRITE = { 0 | 1 }
| INDEX DIRECTORY = ‘absolute path’
| INSERT_METHOD = { NO | FIRST | LAST }
| MAX_ROWS = value
| MIN_ROWS = value
| PACK_KEYS = {0 | 1 | DEFAULT}
| PASSWORD = ‘string’
| RAID_TYPE = { 1 | STRIPED | RAIDO }
RAID_CHUNKS = value
RAID_CHUNKSIZE = value
| ROW_FORMAT = {DEFAULT | DYNAMIC | FIXED | COMPRESSED}
| UNION = (table1, … )
);
Параметры или аргументы
TEMPORARY — необязательный. Он указывает, что таблица является временной таблицей.IF NOT EXISTS — необязательный. Если указано, оператор CREATE TABLE не приведет к возникновению ошибки, если таблицы уже существуют.table_name — имя таблицы, которую вы хотите создать.column1, column2 — столбцы, которые вы хотите создать в таблице.datatype — тип данных для столбца и может быть одним из следующих:
| Значение |
|---|
| CHAR |
| VARCHAR |
| BINARY |
| VARBINARY (length) |
| DATE |
| TIME |
| TIMESTAMP |
| DATETIME |
| YEAR |
| TINYINT |
| SMALLINT |
| MEDIUMINT |
| INT |
| INTEGER |
| BIGINT |
| REAL |
| DOUBLE |
| FLOAT |
| DECIMAL ) ] |
| NUMERIC ) ] |
| TINYBLOB |
| BLOB |
| MEDIUMBLOB |
| LONGBLOB |
| TINYTEXT |
| TEXT |
| MEDIUMTEXT |
| LONGTEXT |
| ENUM(value1, value2, …) |
NULL или NOT NULL — каждый столбец должен быть определен как NULL или NOT NULL. Если этот параметр опущен, база данных предполагает значение NULL по умолчанию.DEFAULT default_value — необязательный. Это значение присваивается столбцу, если он оставлен пустым или NULL.AUTO_INCREMENT — необязательный. Он устанавливает столбец как поле с автонумерацией.constraint_name — необязательный. Имя ограничения, если вы определяете первичный ключ, уникальное ограничение или внешний ключ.index_col_name — необязательный. Это следующий синтаксис:
column_name
