2018-02-13
847
Оператор DROP производит удаление объекта из БД.
Синтаксис удаления объекта:
DROP тип_объекта имя_объекта
Рассмотрим примеры удаления объектом БД, а так же опции, которые могут применяться при удалении таблицы. В первом примере произовдится удаление таблицы с именем «s_fiz_lic2» при этом таблица отправляется в спецмальную область для хранения удаленных объектов, называемой «Корзина».
| DROP TABLE s_fiz_lic2; |
Для удаления таблицы полностью без отправки ее в корзину применяется опция PURGE. Если производится удаление таблицы, на которую ссылается другая таблица, то есть существует ограничение целостности, то такую таблицу нельзя удалить без опции CASCADE CONSTRAINTS, данная опция удалет в месте с таблицей все ссылающиеся на нее ограничения целостности.
| DROP TABLE dolj CASCADE CONSTRAINTS PURGE; |
В следующем примере мы произодим удаление ранее созданного нами представления.
| DROP VIEW v_fiz_lic; |
В этом примере производим удаление последовательности с именем «my_seq».
| DROP SEQUENCE my_seq; |
Пересоздадим удаленные объекты
| CREATE TABLE dolj (kod NUMBER(10) NOT NULL PRIMARY KEY, naimen VARCHAR2(50)); ALTER TABLE s_fiz_lic3 ADD CONSTRAINT fk_dolj FOREIGN KEY (kod_dolj) REFERENCES dolj (KOD); |
Язык манипулирования данными (DML - Data Manipulation Language)
Язык манипулирования данными (DML) включает операторы, управляющие содержанием таблиц базы данных и извлекающими информацию из этих таблиц.
DML включает в себя четыре основные команды:
- INSERT – вставляет данные в таблицу;
- UPDATE – обновляет данные таблицы;
- DELETE – удаляет данные из таблицы;
- SELECT – возвращает (выбирает) данные из объекта базы данных.
Команда INSERT
Оператор INSERT предназначен для добавления строк в таблицу или представление данных.
Синтаксис команды:
INSERTINTO имя_таблицы/представления (столбец1, столбец2,...)
VALUES(значение1, значение2,...);
INSERT INTO имя_таблицы/представления (столбец1, столбец2,...) SELECT...;
Имена в списке столбцов могут быть перечислены в любом порядке. В столбцы, не указанные в списке, заносится пустое значение. Все столбцы с признаком NOT NULL должны быть указанны, и иметь предназначающиеся для них значения. В предложении VALUES перечисляются конкретные значения столбцов в добавляемой строке. Каждый указанный столбец должен иметь соответствующее ему значение в предложении VALUES. Типы данных значения и столбца должен быть совместимы или преобразуемы. Значения типа CHAR, VARCHAR2 и DATE надо заключать в апострафы (‘абв’). Чтобы добавить строки из другой таблицы, следует использовать подзапрос. Оператор SELECT в этом подзапросе должен извлекать значения для каждого перечисленного столбца.
Рассмотрим примеры вставки данных в таблицы. В первом примере мы производим вставку данных во всех столбцы таблицы и в том порядке, в каком она была создана, поэтому здесь мы можем опустить спецификацию столбцов для вставки.
| INSERT INTO s_fiz_lic VALUES (1,'Иванов','Иван','Иванович','ПВС Западного округа','3310 124568'); INSERT INTO s_fiz_lic VALUES (2,'Петров','Петр','Петрович', 'ПВС Центрального округа','3250 145868'); INSERT INTO s_fiz_lic VALUES (3,'Сидоров','Иван','Иванович', 'ПВС Западного округа','7810 124879'); INSERT INTO dolj VALUES (1, ‘Слесарь’); INSERT INTO dolj VALUES (2, ‘Программист’); INSERT INTO dolj VALUES (3, ‘Бухгалтер’); |
В следующем примере в последние два столбца вставляем NULL значения, для того чтобы указать, что у нас отсутствуют данные.
| INSERT INTO s_fiz_lic VALUES (4, ’Иванов’, ’Иван’,’Иванович’,NULL,NULL); |
В данном примере мы производим аналогичную вставку, но со спецификацией столбцов, которая определяет в каком порядке вставлять данные.
| INSERT INTO s_fiz_lic (kod,im,otch,fam, p_vidan,p_ser_nom) VALUES (5, 'Иван', 'Иванович', 'Иванов', 'ПВС Западного округа', '3310 124568'); |
Рассмотрим пример вставки данных с использованием запроса. В этом примере мы создаем дублирующие данные таблицы «s_fiz_lic», при этом для всех выбранных строк из таблицы, мы увеличиваем значение поля «kod» на 5. В рельзультате в таблице мы получим дубликаты строк физизческих лиц, но с разными значениями в поле «kod». В последней строке мы производим фиксацию транзакции.
| INSERT INTO s_fiz_lic (SELECT kod+5,im,otch,fam, p_vidan,p_ser_nom FROM s_fiz_lic); COMMIT; |
В следующем примере мы вставляем данные из таблицы «s_fiz_lic» в таблицу «s_fiz_lic3».
| INSERT INTO s_fiz_lic3 (kod, fio) (SELECT kod, UPPER(fam||’ ’||im||’ ’||otch) FROM s_fiz_lic); COMMIT; |
Команда UPDATE
Оператор UPDATE заменяет значения одного или нескольких указанных столбцов на значения выражений или результат запроса.
Синтаксис команды:
UPDATE таблица/имя_предст_данных SET
столбец_имя = выражение
столбец_имя = (SELECT_с_одним_результатом)
(столбец_имя, столбец_имя,...) = оператор_SELECT
WHERE_предложение;
Оператор SELECT в этом запросе должен возвращать как минимум одну строку и обеспечивать значения для каждого столбца, стоящего слева от знака =. Этот оператор SELECT не может содержать фразы INTO. Для определения набора строк, подлежащих обновлению, используется предложение WHERE. В нем указываются условия, которым должна отвечать обновляемая строка. Если предложение WHERE опустить, то будут обновлены все строки.
Рассмотрим примеры обновления данных. В первом примере строке с кодом 5, мы устанавливаем новые значения поля «fam», «im», «otch».
| UPDATE s_fiz_lic SET Fam=‘Сидоров’, im=’Петр’, otch=’Иванович’ WHERE kod=5; |
В следующем примере мы соединяем значения полей «fam», «im», «otch» в верхнем регистре строки с кодом 5 из таблицы «s_fiz_lic3» и присваиваем его полю «fio» таблицы «s_fiz_lic» с кодом 4.
| UPDATE s_fiz_lic3 SET fio=(SELECT UPPER(fam||’ ’||im||’ ’||otch) FROM s_fiz_lic WHERE kod=4) WHERE kod=5; |
Обновим таблицу «s_fiz_lic3» проставив код должности из таблицы «dolj».
| UPDATE s_fiz_lic3 SET kod_dolj=1 WHERE kod IN (1,2,5,6); UPDATE s_fiz_lic3 SET kod_dolj=2 WHERE kod IN (3,7); UPDATE s_fiz_lic3 SET kod_dolj=3 WHERE kod IN (8); COMMIT; |
Команда DELETE
Оператор DELETE производит удаление строк таблицы. Для определения набора строк, подлежащих удалению, используется предложение WHERE. В нем указываются условия, которым должна отвечать удаляемая строка. Если предложение WHERE опустить, то будут удалены все строки.
Синтаксис команды:
DELETEFROM таблица/имя_предст_данных WHERE_предложение;
Рассмотрим примеры удаления данных. В первом примере мы производим удаление строки из таблицы с кодом 5.
| DELETE FROM s_fiz_lic WHERE kod=5; |
В следующем примере выполянется удаление всех строк таблицы «s_fiz_lic».
| DELETE FROM s_fiz_lic; |
Команда SELECT
Команда SELECT извлекает данные из столбцов одной или нескольких таблиц. Команда SELECT сам по себе является запросом. Если он используется как предложение внутри другого оператора, то он называется подзапросом. В операторе SELECT обязательно должно присутствовать предложение FROM. Остальные предложения не являются необходимыми.
Предложение SELECT может использоваться как:
- самостоятельная команда на получение и вывод строк таблицы, сформированной из столбцов и строк одной или нескольких таблиц (представлений);
- фраза выбора в командах CREAT VIEW, DECLARE CURSOR или INSERT;
- средство присвоения глобальным переменным значений из строк сформированной таблицы (INTO-фраза).
Синтаксис команды:
SELECT alias.столбец_1, alias.столбец_2 ….,
функция(столбец_3), (столбец_1+ столбец_2) as выражение
FROM таблица_1 alias, таблица_2 alias, (запрос_1) alias
WHERE условие_1 [OR [NOT]| AND [NOT]] условие_2
GROUP BY Групповой_столбец_1, групповой_столбец2
HAVING условие_по_групповому_столбцу
ORDER BY столбец_1 ASC | DESC
После указания перечня выбираемых столбцов и операций над ними, указывается служебное слово FROM – оно определяет ресурс данным, т.е. откуда будут выбираться данные, в качестве ресурса данных могут выступать таблицы, представления, запросы.
Таблице или столбцу можно присвоить альтернативное имя (алиас - alias), действие которого будет действительно только в пределах оператора, в котором оно определено. Если альтернативное имя стоит после имени столбца в списке оператора SELECT, то оно будет использоваться вместо настоящего имени как заголовок для данного столбца. Использование имен столбцов и алиаса приведен в примере ниже.
| SELECT f.kod “Код”, f.fio “Ф.И.О.” FROM s_fiz_lic3 f; |
Альтернативное имя таблицы можно использовать при соединении таблицы с самой собой в соотносящемся запросе. При использовании таблиц с одинаковыми именами полей требуется указание имени таблицы перед именем колонки таблицы.
Для выбора данных по условию применяют предложение WHERE. После его указания определяется перечень условий, разделяемые между собой логикой – элементы AND, NOT AND, OR, NOT OR. Условием в языке SQL называется сочетание одного или нескольких выражений и логических операторов, вырабатывающих значение TRUE (истина) или FALSE (ложь). Каждое условие можно представить в виде операторов сравнения (см. таблица 5).
Рассмотрим примеры с использованием операторов сравнения и прекдиката WHERE. В первом примере мы выбираем все столбцы таблицы «Должности» и ту должность, поле «kod», которой равно 1. Для вывода всех столбцов в запросе используется символ «*».
| SELECT * FROM dolj WHERE kod=1; |
Покажем все должности, у которых значение поле «kod» больше 2 и не равно 5.
| SELECT * FROM dolj WHERE kod>2 AND kod!=5; |
Таблица 5 – Операторы сравнения
| Оператор | Значение/действие в SQL |
| = | равно |
| != или <> | не равно |
| >= | больше или равно |
| <= | меньше или равно |
| IN | равен любому элементу в |
| NOT IN | не равен любому элементу в |
| ANY | Сравнивает с любым из значений в списке. Употребляется после =,!=, >, <, <=, >=. |
| ALL | Сравнивает с каждым значением в списке. Употребляется после =,!=, >, <, <=, >=. |
| BETWEEN | больше или равно значения_1 и меньше или равно значение_2 |
| NOT | не больше и не равно |
| EXISTS | Истина, если подзапрос извлекает хотя бы одну строку |
| IS NULL | Истина, значение есть NULL. Проверки типа x=NULL – являются неправильными. |
| IS NOT NULL | Истина, если значение не пустое |
Выберем должности значение поля «kod» у которых находится между 2 и 4 включительно.
| SELECT * FROM dolj WHERE kod between 2 AND 4; |
Покажем всeх людей, у которых поле «kod_dolj» не пустое.
| SELECT kod, fio, kod_dolj FROM s_fiz_lic3 WHERE kod_dolj IS NOT NULL; |
Покажем должности входящие в определенный перечень.
| SELECT naimen FROM dolj WHERE kod IN (1,2,3); |
Для группировки результирующего запроса по столбцам используется выражение GROUP BY, после которого указывается список столбцов, по которым будет группироваться таблица, для вычисляемых столбцов применяются групповые функции SUM, AVG, MIN, MAX, COUNT при определении списка выбираемых столбцов.
Рассмотрим запрос, который показывает количество людей каждой должности в таблице «s_fiz_lic», где поле «kod_dolj» имеет не пустое значение.
| SELECT kod_dolj, COUNT(*) kolvo FROM s_fiz_lic3 WHERE kod_dolj IS NOT NULL GROUP BY kod_dolj; |
Для получения данных по условию для сгруппированной таблицы применяется элемент HAVING, после чего указывается условие для сгруппированной таблицы. Условие определяется аналогично условиям WHERE. В нижеприведенном примере, мы оставили те группы должностей, у которых количество физических лиц больше одного.
| SELECT kod_dolj, COUNT(*) kolvo FROM s_fiz_lic3 WHERE kod_dolj IS NOT NULL GROUP BY kod_dolj HAVING COUNT(*)>1; |
Для сортировки полученных данных в запросе применяется ORDER BY. После указания выражения перечисляются столбцы, покоторым будет сортировка, и тип сортировки по возрастанию (ASC) или убыванию (DESC). По умолчанию сортировка по возрастанию.
Отсортируем по убыванию физических лиц по полю «fio».
| SELECT kod, fio FROM s_fiz_lic3 ORDER BY fio DESC; |
Произведем сортировку по двум столбца – «fio» и «kod».
| SELECT kod, fio FROM s_fiz_lic3 ORDER BY 2,1 ASC; |
Для выбора всех или уникальных записей применяют выражения [ DISTINCT | UNIQUE | ALL ] после слово SELECT, затем перечисляются столбцы, по которым будет отслеживаться уникальность данных.
Синтаксис команды:
SELECT [ DISTINCT | UNIQUE | ALL ] alias.столбец_1, alias.столбец_2
FROM таблица_1 alias
Выражения DISTINCT или UNIQUE позволяют осуществить выбор только уникальных записей. Выражение ALL – выбор всех записей из таблицы.
Для получения объединения, пересечения и разности запросов используется служебные слова UNION, UNION ALL, MINUS, INTERSECT:
Синтаксис команды:
SELECT запрос1
[UNION | UNION ALL | MINUS | INTERSECT]
SELECT запрос2
UNION – объединяет не дублирующиеся данные нескольких запросов
UNION ALL – объединяет все данные
MINUS – производит вычитание данных из запроса1 запрос2
INTERSECT – показывает одинаковые данные имеющиеся в запросах
Оператор UNION объединяет выходные строки каждого из запросов в один результирующий набор. Если определен параметр ALL, то сохраняются все дубликаты выходных строк, в противном случае в результирующем наборе остаются только уникальные строки. Заметим, что можно связывать вместе любое число запросов. Кроме того, с помощью скобок можно менять порядок объединения.
При этом должны выполняться следующие условия:
- Количество выходных столбцов каждого из запросов должно быть одинаковым.
- Выходные столбцы каждого из запросов должны быть сравнимыми между собой (в порядке их следования) по типам данных.
- В результирующем наборе используются имена столбцов, заданные в первом запросе.
- Предложение ORDER BY применяется к результату соединения, поэтому оно может быть указано только в конце составного запроса.
Для выполнения операций пересечения и разности запросов. Этими предложениями являются INTERSECT (пересечение) и MINUS (разность), которые работают аналогично предложению UNION. В результирующий набор попадают только те строки, которые присутствуют в обоих запросах (INTERSECT) или только те строки первого запроса, которые отсутствуют во втором (MINUS).
Рассмотрим примеры. В первом примере получаем объединение запрососв с дубликатами строк.
| SELECT kod, fio FROM s_fiz_lic3 UNION ALL SELECT kod, fio FROM s_fiz_lic3; |
Объединяем запросы с устранением дубликатов.
| SELECT kod, fio FROM s_fiz_lic3 UNION SELECT kod, fio FROM s_fiz_lic3; |
