Основные команды sql oracle

Обновлено: 05.07.2024

SQL*Plus - программа для работы с СУБД Oracle посредством командной строки. Используя SQL*Plus можно выполнять команды SQL и PL/SQL в интерактивном режиме или используя сценарий.

Основное преимущество SLQ*Plus - доступность, т.к. инструмент доступен практически всегда, когда в системе установлен Oracle. При этом это достаточно мощный инструмент, используя который можно решать различные задачи на удаленных машинах.

К написанию этой статьи меня подтолкнула книга "Oracle SQL*Plus: The Definitive Guide, 2nd Edition", написанная Jonathan Gennick. Как обычно, появилось желание систематизировать информацию и представить ее в удобном виде.

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

Область возможного использования SQLplus при этом гораздо шире, например - построение отчетов, в том числе в формате HTML.

Параметры, подключение к базе, запуск скриптов

Выполнить несколько строк кода (не передавая отдельный файл со скриптом), unix:

Выполнение SQL запросов

Запрос может состоять из нескольких строк, содержать комментарии, но не может содержать внутри пустые строки.

Запрос может быть выполнен тремя способами:

точка с запятой в конце запроса

строка с слешем "/" после запроса

пустая строка после запроса (будет помещен в буфер, но не выполнен немедленно)

Выполнение PL/SQL блоков

Пример PL/SQL блока:

Правила выполнения PL/SQL блоков:

Первое слово в PL/SQL блоке должно быть из списка: BEGIN, DECLARE, CREATE PROCEDURE,
CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, CREATE TYPE, CREATE TYPE BODY. Регистр не важен.
Блок может состоять из нескольких строк
Можно вставлять /* комментарии */, они также могут быть на несколько строк
Пустые строки не разрешены внутри блока

Сигнал к выполнению блока может быть подан двумя путями:

Строка, содержащая только слеш "/" после блока - выполнить сразу

Строка, содержащая точку "." после блока - поместить в буфер. Содержимое буфера может быть выведено командой LIST и выполнено, путем указания одиночного слеша "/" или команды RUN.

Одиночное выражение PL/SQL может быть выполнено, используя:

В случае ошибок, при выполнении процедуры, можно отобразить их выполнив команду:

Работа с переменными

Переменные могут быть заданы двумя способами:

Если уже была определена &&variable, то значение будет подставлено во все дальнейшие переменные как &variable так и &&variable.

Если была определена &&variable, и скрипт запущен повтороно в ходе той же сессии работы с SQLplus - будет использовано старое значение переменной. Чтобы этого избежать - можно запрашивать интерактивный ввод в скрипте принудительно, испольтзуя команду:

ACCEPT можно использовать для валидации:

Для ввода дат в определенном формате:

SQL*Plus поддерживает четыре типа переменных: CHAR, NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. При вводе с клавиатуры переменная будет типа CHAR.

Несмотря на это, можно использовать NEW_VALUE, чтобы задать числовую переменную, полученную как результат запроса.

Bind-переменные могут использоваться для передачи данных между PL/SQL и SQL блоками:

Присвоить bind-переменной значение &-переменной:

Вывести значение bind-переменной:

Присвоить &-переменной значение bind-переменной:

Получаем OUT-параметр процедуры в bind-переменную:

Условное выполнение в SQLplus:

Пример скрипта, принимающего несколько переменных на вход в формате c возможностью задать дефолтные значения:

Настройки выполнения скриптов

Действуют на протяжении сессии в SQLplus.

Запомнить настройки перед выполнением скрипта и вернуть обратно по завершении выполнения:

Обратите внимание, что в некоторых системах баз данных требуется указывать точку с запятой в конце каждого оператора. Точка с запятой является стандартным указателем на конец каждого оператора в SQL. В примерах используется MySQL, поэтому точка с запятой требуется.

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

Варианты спецификаций SQL разрабатываются компаниями и сообществами и служат, соответственно, для работы с разными СУБД (Системами Управления Базами Данных) – системами программ, заточенных под работу с продуктами из своей инфраструктуры.

Наиболее применяемые на сегодня СУБД, использующие свои стандарты (расширения) SQL:

MySQL – СУБД, принадлежащая компании Oracle;
PostgreSQL – свободная СУБД, поддерживаемая и развиваемая сообществом;
Microsoft SQL Server – СУБД, принадлежащая компании Microsoft. Применяет диалект Transact-SQL (T-SQL).

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

Общими чертами диалектов являются основные конструкции, применимые практически без отличий во многих реляционных БД. Основные отличия диалектов состоят в различиях использованных типов данных, количеством, реализацией и детальными возможностями команд. Разные диалекты применяют как разные наборы зарезервированных слов, так и разные наборы команд.

SQL запосы

Коснемся классификации SQL запросов.

Выделяют такие виды SQL запросов:

Основные типы SQL запросов по их видам

Создание и настройка базы данных

Нам нужна будет для примеров БД MS SQL Server 2017 и MS SQL Server Management Studio 2017.

Рассмотрим последовательность действий того, как создать SQL запрос. Воспользовавшись Management Studio, для начала создадим новый редактор скриптов. Чтобы это сделать, на стандартной панели инструментов выберем «Создать запрос». Или воспользуемся клавиатурной комбинацией Ctrl+N.

Нажимая кнопку «Создать запрос» в Management Studio, мы открываем тестовый редактор, используя который можно производить написание SQL запросов, сохранять их и запускать.

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

Создадим новую БД с именем «b_library» для библиотеки книг. Чтобы это делать наберем в редакторе такой SQL запрос:

Далее выделим введенный текст и нажмем F5 или кнопку «Выполнить». У нас создастся БД «b_library».

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

В БД «b_library» создадим таблицу авторов «tAuthors» с такими столбцами: AuthorId, AuthorFirstName, AuthorLastName, AuthorAge:

CREATE TABLE tAuthors (
AuthorId INT IDENTITY (1, 1) NOT NULL,
AuthorFirstName NVARCHAR (20) NOT NULL,
AuthorLastName NVARCHAR (20) NOT NULL,
AuthorAge INT NOT NULL
);

Заполним нашу таблицу таким авторами: Александр Пушкин, Сергей Есенин, Джек Лондон, Шота Руставели и Рабиндранат Тагор. Для этого используем такой SQL запрос:

Мы можем посмотреть в «tAuthors» записи, путем отправления в СУБД простого SQL запроса:

В нашей БД «b_library» мы создали первую таблицу «tAuthors», заполнили «tAuthors» авторами книг и теперь можем рассмотреть различные примеры SQL запросов, которыми мы сможем взаимодействовать с БД.

Примеры простых запросов SQL к базам данных.

Рассмотрим основные запросы SQL.

SELECT

1) Выведем все имеющиеся у нас БД:

SELECT name, database_id, create_date
FROM sys.databases;

2) Выведем все таблицы в созданной нами ранее БД «b_library»:

SELECT * FROM tAuthors
ORDER BY AuthorId
OFFSET 3 ROWS
FETCH NEXT 2 ROWS ONLY; SELECT AuthorFirstName, AuthorLastName FROM tAuthors;

INSERT

INSERT – это вид запроса SQL, при применении которого СУБД выполняет добавление новых записей в БД.

Добавим в «tAuthors» нового автора – Уильяма Шекспира, 51 год. Соответственно в поле AuthorFirstName добавится Уильям, в AuthorLastName добавится Шекспир, в AuthorAge – 51. В AuthorId, в нашем случае, автоматически добавится значение, инкрементированное от предыдущего на 1.

UPDATE

UPDATE – SQL запрос, позволяющий внести изменения или дописывать новую информацию в те записи, которые уже существуют.

Внесем корректировки в шестую запись (AuthorId = 6). Значения изменим для полей имени, фамилии и возраста автора.

Затем, обратимся к БД, чтобы вывести все имеющиеся записи:

Мы видим изменения информации в записи автора под номером 6.

DELETE

DELETE – SQL запрос, выполняя который в СУБД производится операция удаления определенной строки из таблицы в БД.

Обратимся к «tAuthors» с командой на удаление строки, где AuthorId = 5:

Чтобы увидеть изменения, снова обратимся к базе для вывода всех записей:

Мы видим, что запись автора под номером 5 теперь отсутствует в «tAuthors» и, соответственно, не выводится с другими записями.

DROP

DROP – ключевое слово в SQL, применяемое для удаления данных с помощью запроса. К примеру удаление некоторой таблицы из БД.

После рассмотрения ряда простых запросов к БД мы можем полностью удалить нашу таблицу «tAuthors» целиком, выполнив простой SQL запрос:

Далее рассмотрим сложные запросы SQL.

SQL команды

Выделяют следующие группы команд SQL:

Команды языка определения данных

Команды языка определения данных DDL (Data Definition Language, язык определения данных) — это подмножество SQL, используемое для определения и модификации различных структур данных.
К данной группе относятся команды предназначенные для создания, изменения и удаления различных объектов базы данных. Команды CREATE (создание), ALTER (модификация) и DROP (удаление) имеют большинство типов объектов баз данных (таблиц, представлений, процедур, триггеров, табличных областей, пользователей и др.). Т.е. существует множество команд DDL, например, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, CREATE USER, CREATE ROLE и т.д.

Некоторым кажется, что применение DDL является прерогативой администраторов базы данных, а операторы DML должны писать разработчики, но эти два языка не так-то просто разделить. Сложно организовать аффективный доступ к данным и их обработку, не понимая, какие структуры доступны и как они связаны. Также сложно проектировать соответствующие структуры, не зная, как они будут обрабатываться.

Команды языка управления данными

С помощью команд языка управления данными ( DCL (Data Control Language) ) можно управлять доступом пользователей к базе данных. Операторы управления данными включают в себя применяемые для предоставления и отмены полномочий команды GRANT и REVOKE, а также команду SET ROLE, которая разрешает или запрещает роли для текущего сеанса.

Команды языка управления транзакциями

Команды языка управления транзакциями ( TCL (Тгаnsасtiоn Соntrol Language) ) команды позволяют определить исход транзакции.
Команды управления транзакциями управляют изменениями в базе данных, которые осуществляются командами манипулирования данными.
Транзакция (или логическая единица работы) – неделимая с точки зрения воздействия на базу данных последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации) такая, что либо результаты всех операторов, входящих в транзакцию, отображаются в БД, либо воздействие всех этих операторов полностью отсутствует.
COMMIT — заканчивает («подтверждает») текущую транзакцию и делает постоянными (сохраняет в базе данных) изменения, осуществленные этой транзакцией. Также стирает точки сохранения этой транзакции и освобождает ее блокировки. Можно также использовать эту команду для того, чтобы вручную подтвердить сомнительную распределенную транзакцию.
ROLLBACK — выполняет откат транзакции, т.е. отменяет все изменения, сделанные в текущей транзакции. Можно также использовать эту команду для того, чтобы вручную отменить работу, проделанную сомнительной распределенной транзакцией.
Понятие транзакции имеет непосредственную связь с понятием целостности базы данных. Очень часто база данных может обладать такими ограничениями целостности, которые просто невозможно не нарушить, выполняя только один оператор изменения БД. Например, невозможно принять сотрудника в отдел, название и код которого отсутствует в базе данных.
В системах с развитыми средствами ограничения и контроля целостности каждая транзакция начинается при целостном состоянии базы данных и должна оставить это состояние целостными после своего завершения. Несоблюдение этого условия приводит к тому, что вместо фиксации результатов транзакции происходит ее откат (т.е. вместо оператора COMMIT выполняется оператор ROLLBACK), и база данных остается в таком состоянии, в котором находилась к моменту начала транзакции, т.е. в целостном состоянии.
В связи со свойством сохранения целостности БД транзакции являются подходящими единицами изолированности пользователей, т.е., если с каждым сеансом работы с базой данных ассоциируется транзакция, то каждый пользователь начинает работу с согласованным состоянием базы данных, т.е. с таким состоянием, в котором база данных могла бы находиться, даже если бы пользователь работал с ней в одиночку.

Команды языка манипулирования данными

Команды языка манипулирования данными DML (Data Manipulation Language) позволяют пользователю перемещать данные в базу данных и из нее:

INSERT — осуществляет вставку строк в таблицу.
DELETE — осуществляет удаление строк из таблицы.
UPDATE — осуществляет модификацию данных в таблице.
SELECT — осуществляет выборку данных из таблиц по запросу.

Каждый, кто работает с SQL в среде Oracle, должен вооружиться книгами: справочником по языку SQL, таким как «Oracle SQL: The Essential Reference? (O’Reilly), руководством по оптимизации производительности, например «Oracle SQL Tuning Pocket Reference» (O’Reilly).

Для простоты ниже мы приведем несколько основных команд SQL.

Основные команды SQL

Настройка базы данных для примеров

Создайте базу данных для демонстрации работы команд. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql. После этого откройте терминал и войдите в консоль MySQL с помощью следующей команды (статья предполагает, что MySQL уже установлен в системе):

Затем введите пароль.

Выполните следующую команду. Назовём базу данных «university»:

Команды SQL для работы с базами данных

3. Выбор базы данных для использования

4. Импорт SQL-команд из файла .sql

5. Удаление базы данных

6. Просмотр таблиц, доступных в базе данных

7. Создание новой таблицы

Ограничения целостности при использовании CREATE TABLE

Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:

ячейка таблицы не может иметь значение NULL;
первичный ключ — PRIMARY KEY (col_name1, col_name2, …) ;
внешний ключ — FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .

Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.

Пример

Создайте таблицу «instructor»:

8. Сведения о таблице

Можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы следующей командой:

9. Добавление данных в таблицу

При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.

10. Обновление данных таблицы

11. Удаление всех данных из таблицы

12. Удаление таблицы

13. SELECT

SELECT используется для получения данных из определённой таблицы:

Следующей командой можно вывести все данные из таблицы:

14. SELECT DISTINCT

В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных.

15. WHERE

Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

В запросе можно задавать следующие условия:

сравнение текста;
сравнение численных значений;
логические операции AND (и), OR (или) и NOT (отрицание).

Пример

Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE :

16. GROUP BY

Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений.

Пример

Выведем количество курсов для каждого факультета:

17. HAVING

Ключевое слово HAVING было добавлено в SQL потому, что WHERE не может быть использовано для работы с агрегатными функциями.

Пример

Выведем список факультетов, у которых более одного курса:

18. ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC .

Пример

Выведем список курсов по возрастанию и убыванию количества кредитов:

19. BETWEEN

BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.

Пример

Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

20. LIKE

Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения.

OEM (Oracle Enterprise Manager) Enterprise Manager, основанная на Java каркасная система, которая объединяет несколько компонентов, чтобы предоставить пользователям мощный графический интерфейс пользователя.

База данных База данных> Табличное пространство Табличное пространство> Файлы данных Файлы данных.

Табличное пространство является самой большой логической единицей и единицей пространства хранения в базе данных Oracle. Система базы данных выделяет пространство для объектов базы данных через табличное пространство. Табличные пространства физически представлены в виде файлов данных на диске. Каждое табличное пространство состоит из одного или нескольких файлов данных. Файл данных может быть связан только с одним табличным пространством. Это объединение логики и физики.

1. Табличное пространство

1.1 Создать табличное пространство

create tablespace test

autoextend on next 10M

1.2 Изменить размер файла данных

alter database datafile 'E:\oracle\product\10.2.0\oradata\test\test.dbf'

1.3 Создать временное табличное пространство для временного хранения данных

create temporary tablespace test_temp

autoextend on next 32M

extend management local;

1.4 Установите для табличного пространства режим только для чтения или режим чтения-записи

alter tablespace test read only;

alter tablespace test read write;

1.5 Удалить табличное пространство

drop tablespace test including contents and datafiles;

1.6 Добавить файлы данных в табличное пространство

alter tablespace test add datafile 'E:\oracle\product\10.2.0\oradata\test\test02.dbf' size 1M;

1.7 Просмотр табличного пространства и информации о файле данных

select * from v$datafile;

select * from v$tablespace;

2. Создать пользователя

2.1 Создать нового пользователя

create user pao

identified by pao

default tablespace test

temporary tablespace test_temp;

2.2 Изменить пароль пользователя

alter user pao identified by newpassword;

2.3 Удалить пользователя

Удалить пользователя Pao Cascade; (Удалить пользователя и объекты, принадлежащие пользователю)

2.4 Разблокировать пользователя

Сначала войдите в систему с идентификатором DBA,

alter user pao account unlock;

3. Разрешения, роли

Разрешение относится к праву на выполнение определенных команд или доступ к объектам базы данных.

Есть два типа разрешений
Authority Системные полномочия: система устанавливает полномочия пользователя на использование базы данных (системные полномочия предназначены для пользователя)
PerОбзоры объектов: разрешить пользователям выполнять определенные операции с объектами базы данных (такими как таблицы, представления, последовательности и т. Д.) (Для таблиц или представлений).

Роль - это комбинация связанных разрешений. Вы можете предоставлять разрешения ролям, а затем назначать роли пользователям, чтобы упростить управление разрешениями.
RacleOracle предоставляет три стандартные роли:

CONNECT, пользователи с разрешением Connect могут входить только в Oracle, не могут создавать сущности и не могут создавать структуры базы данных;

RESOURCE, роль RESOURCE позволяет пользователям создавать таблицы, процедуры, триггеры, последовательности и другие привилегии;

DBA, роль DBA имеет все системные разрешения, включая неограниченное пространство и возможность предоставлять различные разрешения другим пользователям.

3.1 Предоставление разрешений

grant resource to test;

3.2 Отменить полномочия

revoke resource from test;

3.3 Создание разрешения сеанса

grant create session to test;

revoke create session from test;
3.4 Разрешение на создание таблиц
grant create table to test;

revoke create table from test;
3.5 Разрешение на использование табличных пространств
grant unlimited tablespace to test;

revoke unlimited tablespace from test;
3.6 Предоставить разрешение на создание сеанса всем пользователям, public представляет всех пользователей
grant create session to public;

4. Импорт и экспорт

5. Создание, изменение, удаление таблиц

5.1 Новая таблица

5.2 Добавить столбцы

ALTER TABLE [USER.] table_name ADD column_definition;

5.3 Добавить ограничения

ALTER TABLE [USER.] table_name ADD talbe_constraint;

5.4 Добавить ограничение первичного ключа

5.5 Добавить непустые ограничения

5.6 Добавить уникальные ограничения

5.7 Изменить столбец

5.8 Изменить ограничения

ALTER TABLE table_name ADD [ограничение CONSTRAINT name] ограничение (столбец);

5.9 Добавить ограничения внешнего ключа

5.10 Удалить ограничения внешнего ключа

5.11 Удалить столбец

ALTER TABLE [USER.] table_name DROP COLUMN column_names [CASCADE constraints];

В SQL имеется около сорока инструкций (наиболее важные и часто используемые из них представлены в таблицах ниже). Каждая из них "просит" СУБД выполнить определенное действие, например извлечь данные, создать таблицу или добавить в таблицу новые данные. Все инструкции SQL имеют подобную структуру, которая изображена на рис. 1.

Инструкции SQL для обработки данных

Извлекает данные из базы данных

Добавляет новые строки в базу данных

Обновляет данные, имеющиеся в базе данных

Добавляет/обновляет/удаляет новые и старые строки на основе условий

Удаляет строки из базы данных

Команды SQL по Определению данных

Добавляет новую таблицу в базу данных

Удаляет таблицу из базы данных

Изменяет структуру существующей таблицы

Добавляет новое представление в базу данных

Удаляет представление из базы данных

Создает индекс для столбца

Удаляет индекс столбца

Добавляет новую схему в базу данных

Удаляет схему из базы данных

Добавляет новый домен значений данных

Изменяет определение домена

Удаляет домен из базы данных

Инструкции SQL по Управлению доступом

Предоставляет пользователю привилегии доступа

Отменяет пользовательские привилегии доступа

Добавляет в базу данных новую роль

Предоставляет роль, содержащую привилегии доступа

Удаляет роль из базы данных

Инструкции SQL по Управлению транзакциями

Завершает текущую транзакцию

Отменяет текущую транзакцию

Определяет характеристики доступа к данным в текущей транзакции

Явно начинает новую транзакцию

Устанавливает точку восстановления транзакции

Команды Программного SQL

Определяет курсор запроса

Возвращает описание плана доступа к данным в запросе

Открывает курсор для получения результатов запроса

Извлекает строку из результатов запроса

Подготавливает инструкцию SQL к динамическому выполнению

Динамически выполняет инструкцию SQL

Описывает подготовленный запрос

Рис. 1. Структура инструкции SQL

Каждая инструкция SQL начинается с команды, т.е. ключевого слова, описывающего действие, выполняемое инструкцией. Типичными командами являются CREATE (создать), INSERT (добавить), delete (удалить) и COMMIT (зафиксировать). После команды идет одно или несколько предложений. Предложение может описывать данные, с которыми работает инструкция, или содержать уточняющую информацию о действии, выполняемом инструкцией. Каждое предложение также начинается с ключевого слова, такого как WHERE (где), FROM (откуда), into (куда) или HAVING (имеющий). Одни предложения в инструкции являются обязательными, а другие — нет. Конкретная структура и содержимое предложения могут изменяться. Многие предложения содержат имена таблиц или столбцов; некоторые из них могут содержать дополнительные ключевые слова, константы и выражения.

В стандарте ANSI/ISO определен набор зарезервированных (а также незарезервированных) ключевых слов, которые используются в инструкциях SQL. В соответствии со стандартом, зарезервированные ключевые слова нельзя использовать для именования объектов базы данных, таких как таблицы, столбцы и пользователи. Во многих реализациях СУБД этот запрет ослаблен, но тем не менее следует избегать использования ключевых слов в качестве имен таблиц и столбцов. В табл. 1 перечислены ключевые слова, включенные в стандарт ANSI/ISO SQL:2006.

Таблица 1. Зарезервированные ключевые слова SQL:2006

Читайте также: