CASCADE ON DELETE CASCADE)

ALTER TABLEРаботник ADDAдрес_РаботникаVARCHAR(50)

ALTER TABLEРаботникDROPМесто_Рождения

Таблицы Студент ( ФИО_студента, код_ студента, ...) и Оценки(…)

CREATE TABLEОценки( Код INTEGER PRIMARY KEY,Оценка INTEGER,

Дата DATE,Код_Студента INTEGER NOT NULL,

Код_Предмета INTEGER NOT NULL, UNIQUE (Код_Студента, Код_Предмета) )

CREATE TABLEОценки( Код INTEGER NOT NULL UNIQUE,Оценка INTEGER,

Дата DATE,Код_Студента INTEGER NOT NULL,Код_Предмета INTEGER NOT NULL, PRIMARY KEY (Код_Студента, Код_Предмета) )

CREATE TABLEОценки(Код INTEGER NOT NULL PRIMARY KEY,

Оценка INTEGER CHECK (Оценка =<5),Дата DATE,Код_Студента INTEGER NOT NULL,Код_Предмета INTEGER NOT NULL, UNIQUE (Код_Студента, Код_Предмета) )

CREATE TABLEОценки( Код INTEGER NOT NULL PRIMARY KEY,

Оценка INTEGER CHECK IN (1, 2, 3, 4, 5),Дата DATE,Код_Студента INTEGER NOT NULL,Код_Предмета INTEGER NOT NULL, UNIQUE (Код_Студента, Код_Предмета) )

CREATE TABLEОценки( Код INTEGER NOT NULL PRIMARY KEY,

Оценка INTEGER CHECK IN (1, 2, 3, 4, 5) DEFAULT = 5,

Дата DATE,Код_Студента INTEGER NOT NULL,Код_Предмета INTEGER NOT NULL,

UNIQUE (Код_Студента, Код_Предмета) )

CREATE TABLEОценки(Код INTEGER NOT NULL PRIMARY KEY,

Оценка INTEGER,Дата DATE,Код_Студента INTEGER NOT NULL,Код_Предмета INTEGER NOT NULL, FOREING KEY (Код_Студента ) REFERENCESСтудент (Код_Студента))

или

CREATE TABLEОценки(Код INTEGER NOT NULL PRIMARY KEY,

Оценка INTEGER,Дата DATE,Код_Студента INTEGER NOT NULL REFERENCESСтудент (Код_Студента),Код_ПредметаINTEGER NOT NULL )

CREATE TABLEОценки(Код INTEGER NOT NULL PRIMARY KEY,

Оценка INTEGER,Дата DATE,Код_Студента INTEGER NOT NULL REFERENCESСтудент,Код_Предмета INTEGER NOT NULL )(если ключ)

CREATE TABLEОценки(Код INTEGER NOT NULL PRIMARY KEY,

Оценка INTEGER,Дата DATE,Код_Студента INTEGER NOT NULL REFERENCESСтудент,Код_Предмета INTEGER NOT NULL, UPDATE OFСтудентCASCADES, DELETE OFСтудентRESTICTED)

CREATE TABLEОценки(Код INTEGER NOT NULL PRIMARY KEY,

Оценка INTEGER,Дата DATE,Код_Студента INTEGER NOT NULL REFERENCESСтудент,Код_Предмета INTEGER NOT NULL,

DELETE OFСтудент NULL)

* ИНДЕКСИРОВАНИЕ ТАБЛИЦ

Индексы можно создать в CREATE INDEX, CREATE TABLE, ALTER TABLE.

CREATE INDEX <индекс> ON <имя_таблицы> ( <aтрибут>)

CREATE UNIQUE INDEX <индекс> ON <имя_таблицы> ( <aтрибут>)

DROP INDEX <индекс>

CREATE INDEX Test_ind ON Test(col1)TABLESPACE users STORAGE (INITIAL 20K NEXT 20K)

CREATE UNIQUE INDEXcol_a_ind ON sample_3(col_a) ;

CREATE INDEX fk_dept_indON emp(dept_no) ;

CREATE INDEXФИО_студент_IndONСтудент ( ФИО_студента)

CREATE UNIQUE INDEXФИО_студент_IndONСтудент ( ФИО_студента)

Выключение индекса (он существует дальше) –

ALTER TABLE emp KEEP INDEX pk_emp;

DROP INDEX Test_ind; удаление

В user_indexes (таблица словаря БД) можно найти индексы:

SELECT index_name FROMuser_indexes WHEREtable_name=’SAMLE_3’;

Рекомендуется индексировать также столбцы связи, указанные в FOREIGN KEY, REFERENCES

ALTER TABLE empADD CONSTRAINT fk_demt FOREIGN KEY (dept_no)

REFERENCESdept(dept_no);

CREATE INDEXfk_dept_indONemp(dept_no) ;

После этого быстрее выполнится:

SELECT e.e_name, d.nameFROM emp e, dept d

WHERE e.dept_no=d.dept_no;

ALTER TABLEtestDISABLE PRIMARY KEY;

ALTER TABLEtestDISABLE PRIMARY KEY CASCADE;

выключит также и внешний ключ

*ВЫБОРКА ДАННЫХ

SELECT [ALL|DISTINCT] * | <список_столбцов>FROM<таблица>WHERE …

Агрегатные (итоговые) функции: COUNT(), SUM(), AVG(), MIN(), MAX()

(*) все, (атрибут) ненулевые значения, COUNT(DISTINCT(атрибут))

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

WHERE: (<,>,<=,>=,<>), (AND, OR, NOT); BETWEEN (AND), IN,

LIKE(образец) Образец заключается в кавычки и содержит шаблон для поиска. Используются символы % (процент) - заменяет любое количество символов,

_ (подчеркивание) - заменяет одиночный символ. Если ищем значение, которое содержит символ шаблона, то используют ESCAPE <ключевой_символ>. Символ, следующий в шаблоне после ключевого символа, рассматривается как обычный символ.

SELECT * FROMSalespeopleWHERE sname LIKE '%@_%' ESCAPE '@';

GROUP BY, HAVING <…>

ORDER BY [ASC|DESC])

SELECT DISTINCT ЗарплатаFROM Работник

SELECT COUNT(ФИО) AS 'Число_работников', SUM(Зарплата)

AS 'Зарпл_всех_работн', AVG(Зарплата) AS 'Сред_зарпл_работн' FROM Работник

SELECT * FROM Работник WHERE ФИО LIKE "Koваль%"

SELECT * FROMРаботник WHERE ФИО LIKE"С__о%"

SELECTМесто_Рождения,COUNT(Код_Работника)FROM Работник

GROUP BY Место_Рождения

SELECT Место_Рождения, SUM(Зарплата) AS 'Суммарная_зарплата', AVG(Зарплата)

AS 'Средняя_зарплата' FROMРаботник GROUP BY Место_Рождения

SELECT Место_Рождения, AVG (Зарплата)AS 'Средняя_зарплата' FROM Работник

GROUP BYМесто_Рождения HAVING AVG(Зарплата) <= 2200

SELECT ФИО, Зарплата FROMРаботник ORDER BY ФИО

SELECTФИО, Зарплата FROM Работник ORDER BY Зарплата DESC, ФИО

Использование таблиц других пользователей (схем)

SELECT … FROMсхема.таблица

*НЕСКОЛЬКО ТАБЛИЦ В ЗАПРОСЕ

* ОБЪЕДИНЕНИЕ (СОЕДЕНЕНИЯ) ДВУХ ТАБЛИЦ (JOIN, ON)

Внутреннее объединение [INNER] JOIN. Во внутреннем объединении возвращаются только те строки, которые соответствуют условию, указанному после ключевого слова ON.

SELECT d_name, … FROM emp1 INNER JOINdept ONemp1.deptno= emp.deptno

или в ORACLE:

SELECT d_name, … FROM emp1 INNER JOINdept USING (deptno)

Внешнее объединение отличаются от внутренних тем, что могут возвращать строки, не соответствующие условию («висячие» строки). Существует три внешних объединений: левое, правое и полное.

Левое внешнее объединение

SELECT d_name, … FROM emp1 LEFT JOINdept ONemp1.deptno= emp.deptno

SELECTd.dept_no, d.d_name, e.e_name FROMdept d, emp e WHEREd.dept_no (+) = e.dept_no; d, e – псевдонимы для имен таблиц

Правое внешнее объединение

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

SELECT d_name, … FROM emp1 RIGTH JOINdept ONemp1.deptno= emp.deptno

SELECTd.dept_no, d.d_name, e.e_name FROMdept d, emp e WHEREd.dept_no = e.dept_no (+);

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

SELECT d_name, … FROM emp1 RIGTH JOINdept ONemp1.deptno= emp.deptno

SELECTd.dept_no, d.d_name, e.e_name FROMdept d, emp e WHEREd.dept_no (+) = e.dept_no (+);

Рекурсивное связывание (SELF JOIN)

Рекурсивное связывание предполагает связывание таблицы с ней же самой, как будто бы это 2 таблицы, применяя временные переименования таблицы в операторе SQL.

SELECT A.name, B.name FROM employee A JOIN employee B, ONA.name = B.name

SELECT ФИО, Сумма, Налог, Дата_Выплаты FROM Работник JOIN Выплаты ON

Работник.Код_работника = Выплаты.Код_работника ORDER BY ФИО

SELECT ФИО,Сумма, Налог, Дата_Выплаты FROM Работник LEFT OUTER JOIN

Выплаты ON Работник.Код_работника = Выплаты.Код_работника ORDER BY ФИО

SELECT Работник1.ФИО, Работник2.ФИО FROM Работник Работник1,

Работник Работник2 WHERE Работник1.ФИО < Работник2.ФИО AND

Работник1. Место_Рождения = Работник2. Место_Рождения

*ПОДЗАПРОСЫ (вложенный SELECT)

Неправильно

или

Результирующая таблица может содержать:

1) одно значение,

2) один столбец a). <столбец> [NOT] IN <вложенный SELECT>

b). [NOT] < столбец > <операция> ALL|ANY< вложенный SELECT>

3) таблицу

a).[NOT] EXISTS < вложенный SELECT>

b).(<столбец> [,<столбец >]…) [NOT] IN< вложенный SELECT >

SELECT … FROM …WHEREv < (SELECT …)

SELECT …FROM … WHEREa NOT IN (SELECT …)

SELECT * FROM Orders WHERE snumIN

SELECT ФИО, Сумма FROMРаботник

WHERE Сумма < (SELECT AVG(Сумма) FROMРаботник)

SELECTФИО, Сумма FROMРаботник

WHERE Сумма+600 >(SELECT MAX(Сумма) FROMРаботник)

SELECTФИО, Код_Работникa FROMРаботник WHERE Код_Работникa NOT IN

(SELECTКод_Работникa FROMВыплаты )

Или

SELECTценаFROM Товары WHERE EXISTS

(SELECTценаFROM Товары WHERE цена>5000)

SELECTФИО, Код_Работникa FROM Работник WHERE

NOT EXISTS (SELECT Код_Работникa FROMВыплаты WHERE Выплаты. Код_Работника = Работник. Код_Работника )

SELECT * FROM Работник WHERE Сумма > ALL (SELECT Сумма FROM Работник

WHERE Место_Рождения LIKE "Ужго%" OR Место_Рождения LIKE"Мук%")

SELECT * FROM Работник WHERE Сумма > ANY (SELECT Сумма FROM Работник

WHERE Место_Рождения LIKE "Ужго%") AND Место_Рождения LIKE"Мук%"

SELECT MAX(Сумма) FROM Работник WHERE NOT EXISTS

(SELECT Сумма FROM Работник WHERE Сумма > 1800)

СРАВНЕНИЕ ТАБЛИЦЫ С СОБОЙ. Найти все заказы со значениями сумм покупок выше среднего для их заказчиков

*Использование значения NULL.

Возможны два варианта: IS NULL, IS NOT NULL

*ВЕРХНИЙ-N АНАЛИЗ.

Использование ROWNUM

ROWNUM– псевдостолбец, содержит порядковый номер строки таблицы. Создается командой CREATEавтоматически.

SELECT ...FROM [список табл.,] подзапрос WHERE ROWNUM<=n;

Найти первые 3 работника по алфавиту.

SELECT ROWNUM ASномер, enameAS имя

FROMemp WHERE ROWNUM<=3;

1 KING

2 BLAKE

3 CLARK

Ошибка!

SELECT ROWNUM AS номер, ename AS имя

FROM (SELECT enameFROM empORDER BY ename)

WHERE ROWNUM<=3;

1 ADAMS

2 ALLEN

3 BLAKE

Правильно!

Или в других обозначениях.

SELECT ROWNUM, ФИО FROM Работник WHERE ROWNUM < = 3

SELECT ROWNUM, ФИО FROM

(SELECT ФИО FROM Работник ORDER BY ФИО )

WHERE ROWNUM < = 3

При помощи скрипта (в ORACLE) можно решить более общую задачу.

ACCEPT kPROMPT’Сколько вывести работников?’

CREATE OR REPLACE VIEW Список_работников (номер, ФИО, Сумма)

AS SELECT ROWNUM, tab.*

FROM (SELECTФИО, Сумма FROM Работник ORDER BY ФИО ) tab

WHERE ROWNUM < =&k;

SELECT * FROMСписок_работников;

DROP VIEWСписок_работников;

UNDEFINE k

Сколько вывести работников: 2

1 ADAMS 1100

2 ALLEN 1600

*Использование ROWID.

Псевдостобец из 18 символов, содержит уникальный логический адрес строки. Создается автоматически для каждой новой строки. Командой SELECT можно прочитать значение ROWID.

*ВСТАВКА, ИЗМЕНЕНИЕ, УДАЛЕНИЕ В ТАБЛИЦАХ

INSERT INTO<имя_таблицы>VALUES(<список_значений>)

INSERT INTO<имя_таблицы> (<список_атрибутов>)VALUES(<список_значений>)

INSERT INTO <имя_таблицы> SELECT …

UPDATE < имя_таблицы> SET <атрибут> = значение> [,<атрибут> = значение>,..] [WHERE …]

DELETE FROM< имя_таблицы>[WHERE<условие>]

INSERT INTOРаботник VALUES ("68545","Иванов Иван", "Львов", 1958.07.12., 2100)

INSERT INTO Работник (Код_Работникa, ФИО)VALUES("9877","Попов Михаил")

INSERT INTO Работник SELECT * FROMРаботник_2 WHERE ...

INSERT INTOРаботник (Код_Работникa, ФИО )SELECTКод_Работникa, ФИО

FROMРаботник_2

UPDATEРаботникSETМесто_Рождения = "Перечин", Дата_Рождения = 1967.05.12., Зарплата = 120000WHEREКод_Работника ="9877"

UPDATEРаботник SETЗарплата = Зарплата *1.2 WHEREЗарплата <1000

UPDATEОценки SETОценка = NULL WHERETantargy_Num = 2003

UPDATEСтудент SETStip = Stip*1.2 WHERE2<=(SELECT COUNT (Код_Студента) FROMОценки WHEREСтудент.Код_Студента = Оценки.Код_Студента )

UPDATEСтудент SETStip = Stip - 5 WHEREКод_СтудентаIN

(SELECT Код_Студента FROMОценки First WHEREОценка =

(SELECT MIN ( Оценка) FROM Оценки Second WHEREFirst.Дата = Second.Дата )

DELETE FROMРаботник WHEREМесто_Рождения = "Иршава"

DELETE FROMОценкиWHEREДата = 10/06/1999

DELETE FROMСтудентWHEREКод_Студента = (SELECT Код_Студента FROMОценки WHEREОценка =3 )

DELETE FROMСтудентWHERE EXISTS (SELECT * FROMОценки WHEREОценка =3 AND Студент.Код_Студента = Оценки.Код_Студента )

*ТРАНЗАКЦИИ

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

Основные свойств транзакций:

· Атомарность –транзакция должна быть выполнена полностью или не выполнена вовсе.

· Согласованность –по мере выполнения транзакций данные переходят из одного согласованного состояния в другое, не разрушая согласованность данных.

· Изолированность – конкурирующие за доступ к базе данных транзакции физически обрабатываются последовательно, изолированно друг от друга, но для пользователей это выглядит так, как будто они выполняются параллельно.

· Долговечность –при успешном завершении транзакции, изменения в данных не могут быть потеряны.

Есливсе операторы транзакции выполнены успешно и в процессе ее выполнения не было сбоев программного или аппаратного обеспечения, транзакция фиксируется –обеспечивается запись на диск изменений в базе данных, которые были сделаны в процессе выполнения транзакции. Результаты станут видимыми другим транзакциям после того, как текущая транзакция будет зафиксирована. До этого момента данные, затрагиваемые транзакцией, будут "видны" пользователю в состоянии на начало текущей транзакции.

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

Операторы COMMIT и ROLLBACK.Транзакция начинается с первого SQL-оператора. Все последующие SQL-операторы составляют тело транзакции. Способы завер