Создать таблицу в схеме oracle
Обновлено: 02.07.2024
Схема Schema с точки зрения базы данных представляет собой контейнер объектов типа таблиц, триггеров, хранимых процедур и т.п. В данной статье будут рассмотрены вопросы создания и удаления схемы БД следующих СУБД :
- Oracle : Schema привязывается к пользователю, т.е. наименование схемы, как правило, является учетной записью пользователя. Схема создается при создании пользователем первого объекта, и все последующие объекты созданные этим пользователем становятся частью этой схемы. Кроме этого Oracle позволяет создавать схему как контейнер одновременно с объектами базы данных.
- MSSQL : в Microsoft SQL Server начиная с версии 2005 жесткая связь между пользователями и схемами была отменена. Пользователи могут получить доступ на выполнение определенных операций с объектами схемы : чтение, запись, обновление или выполнение.
- PostgreSQL : Schema создается внутри объекта базы данных. Сервер может управлять несколькими базами данных, каждая из которых может включать несколько схем. То есть, как и в MSSQL, схема не связана с учетной записью пользователя.
- MySQL : понятие Schema имеет тождественный смысл с Database. База данных Database является контейнером объектов, к которым пользователь получает доступ.
- Derby : Schema не имеет жесткой связи с пользователем и является контейнером объектов, для доступа к которым пользователь должен иметь соответствующие привилегии.
Для создания схемы необходимо использовать SQL скрипт CREATE SCHEMA. Разные схемы могут включать одноименные объекты. При обращении к объектам разных схем необходимо указывать наименование схемы как префикс. Для создания схемы пользователь должен иметь соответствующие привилегии. Конечно же, superuser'ы данной привилегией владеют.
Создание схемы Oracle
Oracle относится к тем платформам СУБД, которые не имеют явной поддержки команды CREATE SCHEMA. Однако он все же неявно создаёт схему, когда пользователь создаёт свой первый объект базы данных. Данная СУБД использует команду "CREATE SCHEMA" для создания за одну транзакцию таблиц и представлений вместе с предоставлением доступа к ним.
Необходимо отметить, что Oracle разрешает дополнительно использовать в инструкции CREATE SCHEMA стандартные скрипты CREATE TABLE, CREATE VIEW и GRANT. Нельзя использовать любые расширения этих команд, имеющиеся в Oracle, если эти команды включены в инструкцию CREATE SCHEMA. Синтаксис создания объектов со схемой.
В следующем примере для схемы "painter"" создаются таблица и представление. Коме этого в инструкции CREATE SCHEMA определен доступ к объектам.
Порядок команд создания объектов и предоставления прав доступа в инструкции CREATE SCHEMA не критичен, но все же следует соблюдать синтаксис. Oracle выполняет инструкцию CREATE SCHEMA только в том случае, если все входящие в нее инструкции CREATE и GRANT были выполнены успешно.
Создание схемы MS SQL
В СУБД MS SQL при помощи транзакции CREATE SCHEMA можно создать схему одновременно с созданием в ней таблиц, представлений и предоставить или запретить доступ на эти объекты с использованием операторов GRANT, DENY или REVOKE.
- schema_name
Идентификационного наименование схемы. - AUTHORIZATION owner_name
Учетная запись пользователя базы данных, который является владельцем схемы. - table_definition
SQL скрипт создания таблицы внутри схемы. Пользователь должен иметь права CREATE TABLE в текущей базе данных. - view_definition
SQL скрипт создания представления внутри схемы. Пользователь должен иметь права CREATE VIEW в текущей базе данных. - grant_statement
Предоставление разрешения на любой объект, за исключением новой схемы. - revoke_statement
Отмена разрешения на любой объект, за исключением новой схемы. - deny_statement
Запрещение разрешения на любой объект, за исключением новой схемы.
Транзакция CREATE SCHEMA являются атомарной. Если в процессе выполнения инструкции CREATE SCHEMA возникают ошибки, то ни один из указанных объектов не создается и ни одно разрешение не предоставляется.
Объекты, которые необходимо создать при помощи инструкции CREATE SCHEMA, могут быть перечислены в любом порядке, за исключением представлений, ссылающихся на другие представления. В этом случае ссылающееся представление должно быть создано после того представления, на которое оно ссылается.
При помощи инструкции GRANT можно предоставлять разрешения на объект еще до того, как он будет создан, а инструкция CREATE VIEW может появляться раньше инструкций CREATE TABLE, создающих таблицы, на которые ссылается представление. Кроме того, инструкции CREATE TABLE могут декларировать внешние ключи к таблицам, определенным позже в инструкции CREATE SCHEMA.
Создание схемы PostgreSQL
Новая схема создается в текущей базе данных сервера, с которым установлено соединение. Наименование схемы должно быть уникально для данной Database.
- username
имя пользователя (role name) владельца схемы. Если username отсутствует, то будет использоваться имя пользователя, исполняющего данный скрипт. Чтобы создать схему для владельца с другой ролью (role) необходимо иметь права данной роли или быть superuser'ом. - schema_element
определяет SQL скрипт одновременного создания внутри схемы объектов. Вместе со схемой можно создать TABLE, VIEW, INDEX, SEQUENCE, TRIGGER с получением на них соответствующих прав. Если в скрипте присутствует AUTHORIZATION username то все права будут принадлежать username. После создания схемы можно также создать в ней другие объекты.
Примеры создания схемы в PostgreSQL :
Примечание : Согласно SQL стандарту, владелец схемы всегда является "хозяином" всех находящихся внутри объектов. PostgreSQL, также как и MSSQL, разрешает создание внутри схем объектов, "хозяином" которых может быть не владелец схемы, но имеющий соответствующие привилегии данной схемы.
Создание базы данных MySQL
В MySQL понятие базы данных database равносильно понятию schema в других СУБД типа MSSQL, PostgreSQL, Derby и имеет тот же смысл - контейнер не привязанных к пользователю объектов. Создание базы данных выполняется с помощью оператора CREATE DATABASE.
- db_name
Имя создаваемой базы данных. - IF NOT EXISTS
Если данный паарметр не указать, то при создании базы данных с уже существующим именем, возникнет ошибка выполнения команды. - CHARACTER SET
Определение кодировки таблиц базы данных. - COLLATE
Определение порядка сортировки данных.
Если при создании таблицы эти параметры CHARACTER SET и COLLATE не указываются, то кодировка и порядок сортировки вновь создаваемой таблицы берутся из значений, указанных для текущей базы данных.
Примеры использования CREATE DATABASE
Создание схемы Derby
Наименование схемы не должно содержать более 128 символов и быть уникальным внутри базы данных. Также наименование не должно начинаться с префикса SYS.
Только владелец базы данных может создавать схему с наименованием, отличным от имени/логина пользователя, и только владелец базы данных может определять AUTHORIZATION username с именем/логином пользователя, отличным от текущего логина.
Примечание : username может принадлежать только пользователю, а не role.
Для удаления схемы необходимо использовать SQL скрипт drop schema.
Удаление схемы Oracle
Для удаление схемы СУБД Oracle необходимо удалить пользователя; объекты схемы удаляются автоматически :
Ключевое слово CASCADE означает удалить все связанное со схемой (пользователем) объекты.
Удаление схемы MSSQL
Удаляемая схема не должна содержать никаких объектов. Если схема содержит объекты, выполнение инструкции DROP заканчивается сбоем. Сведения о схемах можно увидеть в представлении каталога sys.schemas.
Удаление схемы PostgreSQL
Схема может быть удалена только её владельцем или superuser'ом. Необходимо помнить, что владелец owner может удалить схему и все содержащиеся в ней объекты даже если они ему не принадлежат.
При удалении схемы в PostgreSQL можно дополнительно включить параметры :
- IF EXISTS
Проверка наличия схемы. Если схемы нет, то исключительная ситуация не возникнет. - CASCADE
Автоматически удалять объекты, содержащиеся в схеме. - RESTRICT
Не удалять схему, если она содержит объекты. Этот параметр используется по умолчанию.
Пример удаления схемы orders вместе с содержащимися в ней объектами :
Удаление базы данных MySQL
В СУБД MySQL удалить можно не только пустую базу данных.
Если не указать параметр IF EXISTS, то при попытке удаления не существующей базы данных, возникнет ошибка выполнения команды. Данный параметр доступен в MySQL 3.22 и более поздних версиях. При выполнении команды DROP DATABASE удаляется как сама база данных, так и все объекты, которые в ней находятся.
В следующем примере удаляется база данных "forum" :
Удаление схемы Derby
В СУБД Derby удалить можно только пустую схему. Схемы SYS и APP (схема пользователя по умолчанию) не могут быть удалены.
Ключевое слово RESTRICT является обязательным и обязывает выполнение проверки наличия объектов в удаляемой схеме.
В SQL стандарте скрипт ALTER SCHEMA не определен.
В PostgreSQL владельца или наименование схемы можно изменить скриптом ALTER SCHEMA.
Чтобы использовать ALTER SCHEMA необходимо быть владельцем схемы и иметь соответствующие привилегии. При изменении наименования схемы нужно иметь привилегии CREATE для текущей базы данных. Чтобы сменить владельца, необходимо быть членом соответствующей роли и иметь в ней привилегии CREATE.
В СУБД MSSQL с помощью скрипта ALTER SCHEMA можно перенести объекты из одной схемы в другую.
Пользователи и схемы в MSSQL полностью разделены. Инструкция ALTER SCHEMA применяется только для перемещения объектов между схемами в пределах одной базы данных. В следующем примере схема Customers изменяется путем перемещения в нее таблицы Cities из схемы Persons.
Таблицы могут храниться в базе данных несколькими способами. Простейший из них это heap таблица. Таблица может хранить строки различной длины в случайном порядке. Может быть какая-либо корреляция между порядком записи и порядком в котором данные хранятся но на это нельзя рассчитывать. Более сложные структуры таблицы, такие как представленные ниже, могут влиять на порядок и группировку строк или использовать случайное распределение
Использование сложных структур таблицы не вляет на SQL. Любой SQL запрос выполненные к таблицам созданным с этими опциями вернёт абсолютно одинаковый результат такой же как будто таблица являются обычными heap таблицами. Но если программист понимает как они работаеют то можно существенно увеличить производительность.
Создание таблицы с указанием столбцов
Для создания таблицы используется следующий синтаксис
CREATE TABLE [schema.]tablename [ORGANIZATION HEAP]
(columnname datatype [DEFAULT expression]
[,columnname datatype [DEFAULT expression]);
Как минимум необходимо указать имя таблицы (по умолчанию таблицы создаются в схеме текущего аккаунта, если вы не указываете другую) и один столбец с типом данных. Практически никто не указывает явно ORGANIZATION HEAP, так как это значение по умолчанию и стандарт SQL. Ключевое слово DEFAULT в определении столбца позволяет указать выражение которое будет использоваться при выполнении команды INSERT если не указано значение для этого столбца.
CREATE TABLE SCOTT.EMP
HIREDATE DATE DEFAULT TRUNC(SYSDATE),
COMM NUMBER(7,2) DEFAULT 0.03);
Создаем таблицу EMP в схеме SCOTT. Этот запрос выполнится если подключен аккаунт SCOTT (и тогда указание схемы необязательно) или другой пользователь у которого есть доступ на создание таблиц в схеме SCOTT. Столбцы
EMPNO число длиной 4 цифры без дробной части. Если будет указана дробная часть при выполнении команды INSERT значение будет округлено к ближайшему целому.
ENAME строка с максимальной длиной в 10 символов
HIREDATE возможно использовать значение любой даты с указанием времени, если время не указано используется значение полночь
SAL предназначен для хранения зарплаты, принимает число длиной 7 цифр. Цифры после семи будут обрезаны а значение округлено
COMM имеет значение по умолчанию 0.03, которое будет использоваться если явно не указанть значение при выполнении команды INSERT
После создания таблицы можно записывать данные и выполнять команду SELECT
Обратите внимание что столбцы не указанные при команде INSERT использовали значения указанные в DEFAULT. Если бы не были указаны значения по умолчанию в секции DEFAULT использовалось бы значение NULL. Также обратите внимание что значение SAL было округлено.
The DEFAULT clause can be useful, but it is of limited functionality. You cannot use a subquery to generate the default value: you can only specify literal values or functions.
Создание таблиц с использованием подзапросов
Вместо создания таблиц с нуля и затем вставки строк (как мы только что рассмотрели) таблицы можно создавать используя другие таблицы – с помозью подзапросов. Этот метод позволяет определить таблицу и записать данные используя один запрос. Любой запрос может быть использован как источник для описания столбцов и как набор данных (строк). Синтаксис команды
CREATE TABLE [schema.]tablename AS subquery;
Любой запрос возвращает двумерный набор строк; результат хранится как новая таблица. Просто пример создания таблицы используя подзапрос
create table employees_copy as select * from employees;
Данный запрос создаст таблицу EMPLOYEES_COPY, которая будет полной копией таблицы EMPLOYEES, идентичной как по структуре столбцов и так и хранящихся строк. Все проверки на обязательность значения и значения по умолчанию также применяются к новой таблице, но первичные ключи, уникальные или внешние ключи не создаются (ограничения constraints обсудим далее), потому что эти три типа ограничений требуют создания индексов которые могут быть не нужными пользователю.
Рассмотрим более сложный пример
create table emp_dept as select
from employees natural join departments order by dname,ename;
Строки в новой таблице это результат объекдинения двух таблиц-источников, и два столбца изменяют название. Новый столбец SERVICE будет заполнен результатом арифметической операции и получено количество дней с момента приёма на работу. Строки буду вставлены в определённом порядке. Этот порядок не будет управляться последующими DML командами, но предполагая что в схеме HR данные по умолчанию новая таблица будет выглядеть следующим образом
Подзапрос конечно может включать секцию WHERE для ограничего строк для добавления в новую таблицу. Для создания таблицы без строк помжно использовать условие WHERE которое никогда не выполняется.
create table no_emps as select * from employees where 1=2;
Изменение таблицы после создания
Доступно много изменений которые возможны после создания таблицы. Изменения которые затрагивают физическую структуру ложатся на плечи администратора БД, но большинство изменения являются логическими и могут быть применены SQL разработчиками. Раасмотрим примеры (большинтсво из них говорят сами за себя)
- Добавление столбца
alter table emp add (job_id number);
- Изменение столбца
alter table emp modify (commission_pct number(4,2) default 0.05);
- Удаление столбца
alter table emp drop column commission_pct;
- Маркировка столбца как неиспользуемого
alter table emp set unused column job_id;
- Переименование столбца
alter table emp rename column hire_date to recruited;
- Включение режима только-чтения для таблицы
alter table emp read only;
Все изменения этих DDL команд содержат встроенный COMMIT. Эти изменения нельзя отменить и они не смогут отработать если имеется активная транзакция к таблице. Также эти операции происходят практически мгновенно за исключением удаления столбца. Удаление столбца может затребовать длительное время так как поскольку удаляется столбец, каждая строка должна быть изменена. Команда SET UNUSED делает столбец недоступным для SQL команд, и часто является более лучшей альтернативой в сочетании с командой
ALTER TABLE tablename DROP UNUSED COLUMNS;
которая удалит все неиспользуемы столбцы за один проход таблицы.
После перевода таблицы в режим только-чтения все DML команды не выполнятся. Но таблица может быть удалена, так как удаление всего лишь удалит строки из словаря данных.
Удаление и сокращение таблицы (drop и truncate)
Команда TRUNCATE TABLE удаляет вссе строки из таблицы и оставляет определение таблицы. Команда DROP TABLE удаляет также и определение таблицы (саму таблицу вместе с данными). Синтаксис команды
DROP TABLE [schema.]tablename ;
Если схема не указана удалится таблица с этим именем в схеме текущего аккаунта.
SQL не выдает предупреждений перед выполнением команды DROP и как и любая команда DDL содержит встроенный COMMIT. То есть удаление таблицы нельзя отменить. При выполнении определенных условий, удаление может быть отменено используя определенные методики.
У команды DROP есть несколько ограничений: команда не выполнится если существует транзакция к этой таблице, или если данные этой таблицы используются как внешний ключ другой таблица, тогда для удаления исходной таблица необходимо вначале удалить ссылающуюся таблица или внешний ключ.
Основные типы данных, столбцов таблицы БД, я вам уже приводил в "Шаг 7 - Язык SQL и примитивные типы данных", так что загляните туда. Так же замечу, что мы еще остановимся на типах данных, когда в плотную подойдем к PL/SQL. Пока я пытаюсь создать для вас целостную картину БД, так как если не разъяснить немного эти основные понятия, то PL/SQL будет трудно освоить! Чего-то я отвлекся! :) Итак немного забегая вперед, разберем это понятие чуть ближе!
Создание таблицы.
Табличка успешно создана! После этого не забудьте ввести команду COMMIT! Иначе после завершения сеанса, можете не найти вашу табличку! Вот так:
Это означает, что "транзакция", то есть ваши осмысленные действия БД приняла и подтвердила. Теперь давайте убедимся, что наша таблица действительно создана и появилась успешно в схеме MILLER. Отправьте следующий запрос к представлению USER_TABLES, которое содержит информацию обо всех таблицах данной схемы:
ДА! Таблица создана, располагается в табличном пространстве USERS, и имеет ноль записей. Давайте дадим еще вот такой запрос к представлению USER_TAB_COLUMNS, которое содержит информацию обо всех столбцах всех таблиц данной схемы:
Получаем примерно следующее:
Вот теперь видно, что из себя представляет наша таблица в схеме! Полностью соответствует тому что мы с вами и проделывали. Замечательно, но кому нужна пустая таблица? Давайте введем в нее немного данных! И посмотрим, что из этого получится! Ввести данные в таблицу нам поможет, нет не киножурнал "Хочу все знать", а оператор INSERT. Итак, введите следующее:
После отправки команд увидите, что каждая запись отрапортовала о своем успешном введении и все изменения в таблице подтверждены!
А затем убедимся, что в таблице появились значения дав следующий запрос:
Теперь, видно, что таблица имеет три записи:
Так же еще добавлю, что информация обо всех таблицах содержится в представлении DBA_TABLES, а обо всех столбцах всех таблиц БД в представлении DBA_TAB_COLUMS. Пока на этом остановимся, дальше еще много объектов, с которыми необходимо познакомится. А пока, можете поработать с созданием табличек! :)
Инструкция CREATE TABLE используется для создания новой таблицы в базе данных.
Синтаксис
CREATE TABLE table_name (column1 datatype,
column2 datatype,
column3 datatype,
.
);
Параметры столбцов задают имена столбцов таблицы.
Параметр datatype указывает тип данных, которые может содержать столбец (например, varchar, integer, date и т.д.).
Совет: Для получения обзора доступных типов данных перейдите Справочник Типы данных.
SQL Пример CREATE TABLE
В следующем примере создается таблица "Persons", содержащая пять столбцов: PersonID, LastName, FirstName, Address, и City:
Пример
CREATE TABLE Persons (PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Столбец PersonID имеет тип int и будет содержать целое число.
Столбцы LastName, FirstName, Address, and City имеют тип varchar и будут содержать символы, а максимальная длина этих полей составляет 255 символов.
Пустая таблица "Persons" теперь будет выглядеть так:
Совет: Пустая таблица "Persons" теперь может быть заполнена данными с помощью инструкции SQL INSERT INTO.
Создать таблицу, используя другую таблицу
Копия существующей таблицы также может быть создана с помощью команды CREATE TABLE.
Новая таблица получает те же определения столбцов. Можно выбрать все столбцы или отдельные столбцы.
Если вы создадите новую таблицу, используя существующую таблицу, новая таблица будет заполнена существующими значениями из старой таблицы.
Синтаксис
CREATE TABLE new_table_name ASSELECT column1, column2.
FROM existing_table_name
WHERE . ;
Следующий SQL создает новую таблицу под названием "TestTables" (которая является копией таблицы "Customers"):
Читайте также: