Работа с базами данных Лямин Андрей Владимирович

Типы данных Тип Описание NUMBER(p, s) Вещественное число с точностью p вплоть до 38 цифр (общим количеством цифр) и масштабом s (числом цифр справа от десятичной точки). Масштаб может находится в диапазоне от -84 до 127. CHAR(size) Символьные данные фиксированной длины равной size байт. Максимум size составляет 2000 байт, а минимум 1 байт. VARCHAR 2(size) Строка переменной длины, максимальный размер которой не может превышать size символов или байтов. Максимальное значение size - 4000. DATE Сохраняет информацию о дате. Использует формат "YYYY-MM-DD".

Вещественный тип данных Данные Описание Результат 7456123. 89 NUMBER(9, 2) 7456123. 89 NUMBER(6) Exceeds precision 7456123. 89 NUMBER(7, -2) 7456100 7456123. 89 NUMBER(7, 2) Exceeds precision 0. 000127 NUMBER(4, 5) 0. 00013 0. 0000012 NUMBER(2, 7) 0. 00000123 NUMBER(2, 7) 0. 0000012

Литералы • 'Hello' • 'ORACLE. dbs' • 'Jackie''s raincoat' • '09 -MAR-98'

Комментарии /* Таблица с описание печатных изданий */ CREATE TABLE Publication ( ID NUMBER(4) PRIMARY KEY, -- Первичный ключ Description VARCHAR 2(500) NOT NULL -- Описание );

Имена • Имя не может иметь длину более 30 символов • Имя должно начинаться только с символа • Имена должны быть уникальными • Имя не должно совпадать с ключевыми словами в системе Oracle.

Арифметические операторы • ‘ + ’, ‘ - ’, ‘ * ’, ‘ / ’, ‘ || ’ Примеры: SELECT * FROM employees WHERE -salary < 0; UPDATE employees SET salary = salary * 1. 1; SELECT 'Name is ' || last_name FROM employees;

Операции над результатами выполнения двух запросов Оператор Описание UNION Возвращает все строки выбранные запросами без дублирования UNION ALL Возвращает все строки выбранные запросами, включая дублирование INTERSECT Возвращает строки, которые выбраны и первым, и вторым запросом MINUS Возвращает строки, которые выбраны первым запросом и отсутствуют во втором

Примеры SELECT location_id UNION SELECT location_id FROM locations SELECT location_id UNION ALL SELECT location_id FROM locations SELECT location_id INTERSECT SELECT location_id FROM locations SELECT location_id MINUS SELECT location_id FROM locations FROM departments;

Условия Оператор Описание =, !=, <, >, <=, >= Сравнение IS [NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS Сравнение NOT Отрицание AND Конъюнкция OR Дизъюнкция

Примеры SELECT last_name FROM employees WHERE last_name LIKE 'A_b%'; SELECT last_name FROM employees WHERE upper(last_name) LIKE 'A_B%'; SELECT * FROM employees WHERE salary BETWEEN 2000 AND 3000; SELECT * FROM employees WHERE department_id NOT IN (10, 20);

NULL-значения SELECT Last. Name, First. Name, Middle. Name FROM Person WHERE Status IS NULL; SELECT Last. Name, First. Name, Middle. Name FROM Person WHERE Status IS NOT NULL;

Установления порядка сортировки SELECT * FROM employees WHERE job_id = 'CLERK' ORDER BY commission DESC; SELECT last_name, department_id, salary FROM employees ORDER BY department_id ASC, salary DESC;

Иерархические операции SELECT employee_id, last_name, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id = manager_id; EMPLOYEE_ID -----101 108 109 110 111 112 113 200 LAST_NAME MANAGER_ID -------------Kochhar 100 Greenberg 101 Faviet 108 Chen 108 Sciarra 108 Urman 108 Popp 108 Whalen 101

Функции Функция Описание min Возвращает минимальное значения max Возвращает максимальное значения count Возвращает количество записей sum Возвращает сумму значений to_date Преобразует строку в дату to_char Преобразует дату или число в строку

Объединение записей в запросе SELECT count(*) FROM employees; SELECT department_id, MIN(salary), MAX(salary) FROM employees GROUP BY department_id ASC; SELECT department_id, MIN(salary), MAX(salary) FROM employees WHERE job_id = 'CLERK' GROUP BY department_id DESC;

Использование условия SELECT department_id, MIN(salary), MAX(salary) FROM employees GROUP BY department_id HAVING MIN(salary) < 5000; DEPARTMENT_ID MIN(SALARY) MAX(SALARY) ----------10 4400 30 2500 11000 50 2100 8200 60 4200 9000

Вложенные запросы SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department_id =30); SELECT * FROM employees WHERE salary NOT IN (SELECT salary FROM employees WHERE department_id = 30);

Запросы, использующие EXISTS SELECT DISTINCT A. a FROM A WHERE NOT EXISTS (SELECT X. x FROM X WHERE NOT EXISTS (SELECT AX. * FROM AX WHERE AX. a=A. a AND AX. x=X. x));

Исключение дублирующихся записей SELECT DISTINCT city, state FROM address WHERE state IN (‘CA’, ’NY’) AND city IS NOT NULL;

Запросы, использующие соединения SELECT pr. * FROM person pr, status s WHERE pr. status=s. id AND UPPER(s. name) LIKE '%ГОСТЬ%'; SELECT p. description AS “Название книги”, COUNT(c. publication) AS “Кол-во экземпляров” FROM publication p, copy c WHERE p. id=c. publication GROUP BY c. publication;

Удаление записей DELETE FROM person WHERE id=3; DELETE FROM person WHERE id NOT IN (SELECT person FROM request);

Добавление записей INSERT INTO person(last. Name, first. Name, middle. Name) VALUES ('Петров', 'Дмитрий', 'Геннадьевич'); INSERT INTO staff(person, position) SELECT ID, 'Программист' FROM person;

Обновление записей UPDATE shelf SET description='Верхняя полка' WHERE id = 1; UPDATE copy SET shelf = 1;

Создание таблиц CREATE TABLE person( id NUMBER(4), first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), login VARCHAR 2(9), password VARCHAR 2(9) );

Ограничение NOT NULL CREATE TABLE person( id NUMBER(4) CONSTRAINT person_id NOT NULL, first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), login VARCHAR 2(9), password VARCHAR 2(9) );

Сокращенная форма CREATE TABLE person( id NUMBER(4) NOT NULL, first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), login VARCHAR 2(9), password VARCHAR 2(9) );

Ограничение UNIQUE CREATE TABLE person( id NUMBER(4) CONSTRAINT person_id UNIQUE, first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), login VARCHAR 2(9), password VARCHAR 2(9) );

Ограничение UNIQUE CREATE TABLE person( id NUMBER(4) UNIQUE, first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), login VARCHAR 2(9), password VARCHAR 2(9) );

Ограничение UNIQUE CREATE TABLE person( id NUMBER(4), first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), login VARCHAR 2(9), password VARCHAR 2(9), CONSTRAINT person_id UNIQUE (id) );

Ограничение UNIQUE CREATE TABLE person( id NUMBER(4), first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), login VARCHAR 2(9), password VARCHAR 2(9), UNIQUE (id) );

Первичный ключ CREATE TABLE person( id NUMBER(4), first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), login VARCHAR 2(9), password VARCHAR 2(9), CONSTRAINT person_id PRIMARY KEY (id) );

Первичный ключ CREATE TABLE person( id NUMBER(4) PRIMARY KEY, first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), login VARCHAR 2(9), password VARCHAR 2(9) );

Внешний ключ CREATE TABLE person( id NUMBER(4), status REFERENCES status(id), first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), login VARCHAR 2(9), password VARCHAR 2(9), CONSTRAINT person_id PRIMARY KEY (id) );

Внешний ключ CREATE TABLE person( id NUMBER(4), status NUMBER(4) CONSTRAINT person_status REFERENCES Status(id), first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), CONSTRAINT person_id PRIMARY KEY (id) );

Внешний ключ CREATE TABLE person( id NUMBER(4), status NUMBER(4), first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), CONSTRAINT person_id PRIMARY KEY (id), CONSTRAINT person_status FOREIGN KEY status REFERENCES status(id) );

Реакция на удаление CREATE TABLE person( id NUMBER(4), status NUMBER(4), first. Name VARCHAR 2(50), last. Name VARCHAR 2(50), middle. Name VARCHAR 2(50), CONSTRAINT person_id PRIMARY KEY (id), CONSTRAINT person_status FOREIGN KEY status REFERENCES status(id) ON DELETE CASCADE );

Проверка условий CREATE TABLE status( id NUMBER(4) PRIMARY KEY, name VARCHAR 2(9) NOT NULL, CONSTRAINT status_name CHECK (name IN ('Owner', 'Guest')) ); CREATE TABLE status( id NUMBER(4) PRIMARY KEY, name VARCHAR 2(9) NOT NULL, CHECK (name IN ('Owner', 'Guest')) );

Удаление таблиц DROP TABLE Publication CASCADE CONSTRAINTS;

Изменение таблиц ALTER TABLE countries ADD (cnt NUMBER(2, 2) CHECK (cnt < 10. 5), visa VARCHAR 2(3)); ALTER TABLE countries MODIFY (cnt NUMBER(3, 2)); ALTER TABLE departments DROP PRIMARY KEY CASCADE;

Последовательности CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

Триггеры CREATE OR REPLACE TRIGGER person_t BEFORE INSERT ON person FOR EACH ROW BEGIN SELECT person_s. NEXTVAL INTO : new. id FROM DUAL; END; /

Инфологическая модель

Create table Status ( id number(4) primary key, Name varchar 2(9) not null, check (Name in (‘Owner’, ’Guest’)) );

Create table Person ( id number(4) primary key, status references Status(id), firstname varchar 2(50) not null, lastname varchar 2(50), middlename varchar 2(50), login varchar 2(9), password varchar 2(9) ); Create sequence Person _s nocache order; Create or replace trigger Person _t Before insert on Person For each row Begin Select Person _s. nextval Into : new. id from dual; End; /

Create table Logon ( person references Person(id), date_ date, IPAddress varchar 2(15) );

Create table Request ( id number(4) primary key, person references Person(id), begindate not null, enddate not null, requestdate default sysdate ); Drop sequence Request _s; Create sequence Request _s nocache order; Create or replace trigger Request _t Before insert on Request For each row Begin Select Request _s. nextval Into : new. id from dual; End; /

Create table Publication ( id number(4) primary key, Description varchar 2(500) not null ); Create sequence Publication _s nocache order; Create or replace trigger Publication _t Before insert on Publication For each row Begin Select Publication _s. nextval Into : new. id from dual; End; /

Create table Shelf ( id number(4) primary key, Description varchar 2(100) ); Create sequence Shelf _s nocache order; Create or replace trigger Shelf _t Before insert on Shelf For each row Begin Select Shelf _s. nextval Into : new. id from dual; End; /

Create table Copy ( id number(4) primary key, Publication references Publication(id), Shelf references Shelf(id) ); Create sequence Copy _s nocache order; Create or replace trigger Copy _t Before insert on Copy For each row Begin Select Copy _s. nextval Into : new. id from dual; End; /

Create table Request_Copy ( request references request(id), copy references copy(id) );