???? SQL ????????? ?? ???????? ?? by b7CWM77

VIEWS: 49 PAGES: 19

									          Език SQL
Оператори за описание на данни
             DDL

       Базови таблици
      Виртуални таблици
                          Съдържание

   Оператор CREATE TABLE
       • Типове данни в Oracle
       • Ограничения за цялостност
     Оператор DROP TABLE
     Оператор ALTER TABLE
     Обект SEQUENCE
     Виртуални таблици
     Синоними


Бази данни, М. Филипова                2
                    Оператор CREATE TABLE
CREATE TABLE име-на-таблица
( име-колона тип [DEFAULT значение][NOT NULL]
  [ ограничение-за-цялостност-за-колоната ]
[, ...]
[, ограничение-за-цялостност-за-таблицата ]
[, ...]
);
 Описание на колона
    -   Име на колоната
    -   Тип на данните
    -   Значение по премълчаване – DEFAULT
    -   Ограничение, забраняващо неопределено значение – NOT NULL.
    -   Ограничения за цялостност, отнасящи се само до описваната колона.
 ограничение-за-цялостност-за-таблицата
   “Пълната” форма при задаване на ограничения за цялостност, отнасящи
   се до всички описани вече колони.
Бази данни, М. Филипова                                                  3
Типове данни в Oracle
CHAR(n [BYTE|CHAR])
     Символен низ с фиксирана дължина n (1<=n<=2000)
VARCHAR2(m [BYTE|CHAR])
     Символен низ с променлива дължина максимум m
     (1<= m <=4000)
NUMBER(p[,s])
     Число с p десетични цифри и s след десетичната точка
     (1<=p<=38, -84<=s<=127)
DATE               Дата
TIMESTAMP          Дата и време
INTERVAL YEAR(n) TO MONTH
     Период от време в години и месеци
INTERVAL DAY (d) TO SECOND(f)
     Период от време в дни, часове, минути, секунди
Бази данни, М. Филипова                                     4
Ограничения за цялостност
Ограничения за цялостност за колона
[ CONSTRAINT име-на-ограничение ]
{ {UNIQUE | PRIMARY KEY}
| REFERENCES име-таблица-pk (име-колона-pk)
      [ON DELETE {CASCADE | SET NULL}]
| CHECK ( условие ) }
Ограничения за цялостност за таблица
[ CONSTRAINT име-на-ограничение ]
{{UNIQUE | PRIMARY KEY} (име-колона-pk [,...])
| FOREIGN KEY (име-колона-fk [,...])
   REFERENCES име-таблица-pk (име-колона-pk [,...])
  [ON DELETE {CASCADE | SET NULL}]
| CHECK ( условие ) }

Бази данни, М. Филипова                               5
                      Оператор DROP TABLE
DROP TABLE име-на-таблица
  [CASCADE CONSTRAINTS];
 Унищожава таблица
   – данните в таблицата
   – ограниченията за цялостност върху таблицата
   – други обекти в БД, свързани с таблицата – синоними,
     индекси
 CASCADE CONSTRAINTS - Автоматично се унищожават и
  всички ограничения за външен ключ, свързани с първичния
  (възможен) ключ на таблицата.
 Ако унищожаваната таблица се използва в определението на
  view, то става невалидно (не може да се използва, докато не
  се създаде отново).

Бази данни, М. Филипова                                     6
                     Оператор ALTER TABLE
ALTER TABLE име-на-таблица
{
 ADD фраза
|DROP фраза
|MODIFY фраза
|MODIFY CONSTRAINT фраза
};
 Добавя колона или ограничение за цялостност.
 Унищожава колона или ограничение за цялостност.
 Изменя описанието на колона
    –   Изменя типа на данните на колона.
    –   Добавя или изменя значение по премълчаване за колона.
    –   Разрешава или забранява значение NULL за колона.
    –   Добавя ограничение за цялостност за колоната.
Бази данни, М. Филипова                                         7
Добавяне, унищожаване и изменение на колона
ADD (име-нова-колона тип [DEFAULT значение]
     [NOT NULL]
     [ограничение-за-цялостност-за-колоната]
[,...])
DROP (име-колона [, ...])[CASCADE CONSTRAINTS]
MODIFY (име-колона [тип] [DEFAULT значение]
      [[NOT] NULL]
     [ограничение-за-цялостност-за-колоната]
[,...])
RENAME COLUMN старо-име TO ново-име
 DROP – Унищожават се индекси по колоната, ограничения за колоната.
  CASCADE CONSTRAINTS - Ако има външен ключ, свързан с колоната
  или ограничение CHECK с няколко колони (иначе - грешка).
 MODIFY – Всяка от характеристиките, която не е зададена, не се променя.

Бази данни, М. Филипова                                                8
Добавяне, унищожаване на ограничение за цялостност
ADD ограничение-за-цялостност-за-таблица
  [,...]
DROP CONSTRAINT име-на-ограничение [CASCADE]
MODIFY CONSTRAINT име-на-ограничение
   {ENABLE[VALIDATE|NOVALIDATE] | DISABLE}
RENAME CONSTRAINT старо-име TO ново-име
 DROP CONSTRAINT
  CASCADE - Ако се унищожава ограничение за първичен
  (възможен) ключ, с който има свързан външен ключ.




Бази данни, М. Филипова                                9
Пример – описание на БД-служители
CREATE TABLE dep
( dno NUMBER(4) CONSTRAINT dep_pk PRIMARY KEY,
   dname VARCHAR2(30) NOT NULL UNIQUE,
   budget NUMBER(7,2) NOT NULL,
   mgr NUMBER(6)
);
CREATE TABLE emp
( eno NUMBER(6) CONSTRAINT emp_pk PRIMARY KEY,
   ename VARCHAR2(50) NOT NULL,
   sal NUMBER(6,2) DEFAULT 0 NOT NULL,
   job VARCHAR2(20),
   addr VARCHAR2(50),
   dno NUMBER(4),
   CONSTRAINT dno_fk FOREIGN KEY (dno) REFERENCES dep(dno)
);
Бази данни, М. Филипова                                  10
Пример (прод.)
CREATE TABLE project
( pno NUMBER(4) CONSTRAINT pro_pk PRIMARY KEY,
   pname VARCHAR2(30) NOT NULL,
   description VARCHAR2(100)
);
CREATE TABLE emp_pro
( eno NUMBER(6),
   pno NUMBER(4) ,
   ptime INTERVAL YEAR TO MONTH,
   CONSTRAINT ep_pk PRIMARY KEY(eno, pno),
   CONSTRAINT eno_fk FOREIGN KEY (eno)
        REFERENCES emp(eno),
   CONSTRAINT pno_fk FOREIGN KEY (pno)
        REFERENCES project(pno)
);


Бази данни, М. Филипова                          11
Пример (прод.)
ALTER TABLE dep
  MODIFY (budget NUMBER(10,2) DEFAULT 0);
ALTER TABLE dep
  ADD CONSTRAINT budget_chk CHECK (budget >= 0);
ALTER TABLE emp
  ADD (comm NUMBER(7,2) DEFAULT 0 NOT NULL,
      email VARCHAR2(25));
ALTER TABLE emp
  ADD CONSTRAINT sal_chk CHECK (sal + comm < 5000);
ALTER TABLE emp
  DROP (comm) CASCADE CONSTRAINTS;
ALTER TABLE dep DROP UNIQUE (dname);
ALTER TABLE dep
  ADD CONSTRAINT dname_chk CHECK (dname = UPPER(dname));

Бази данни, М. Филипова                                12
                          Обект SEQUENCE
CREATE SEQUENCE име-sequence
  [ INCREMENT BY стъпка ]
  [ MAXVALUE максимум | NOMAXVALUE ]
  [ MINVALUE минимум | NOMINVALUE ]
  [ START WITH начално-значение ];
DROP SEQUENCE име-sequence;
ALTER SEQUENCE име-sequence
  [ INCREMENT BY стъпка ]
  [ MAXVALUE максимум | NOMAXVALUE ]
  [ MINVALUE минимум | NOMINVALUE ];
 Изменя стъпката за изменение на значението.
 Добавя или отменя максимално и минимално значение.
Бази данни, М. Филипова                                13
Примери

CREATE SEQUENCE emp_seq
  START WITH 100
  INCREMENT BY 1;
ALTER SEQUENCE emp_seq MAXVALUE 2000;
CREATE SEQUENCE dep_seq INCREMENT BY 1;

INSERT INTO dep (dno, dname, budget)
  VALUES (dep_seq.nextval, 'Катедра ИС', 1400);
INSERT INTO emp (eno, ename, job, sal, dno)
  VALUES (emp_seq.nextval , 'Асен Петров', 'ас.', 400,
  dep_seq.currval);

Бази данни, М. Филипова                                  14
                   Виртуални таблици (Views)
CREATE [OR REPLACE] VIEW име-view
  [(списък-имена-на-колони)]
  AS оператор-select
  [WITH CHECK OPTION];
 OR REPLACE – Да се измени дефиницията на съществуващо view като се
  избегне drop/create.
 WITH CHECK OPTION - не позволява да се изпълни оператор INSERT
  или UPDATE чрез име-view, който нарушава условието в CREATE
  VIEW.
DROP VIEW име-view;
 Не се унищожават други views, определени над име-view, а стават
  невалидни.
ALTER VIEW име-view COMPILE;
 Ако няма грешки, име-view става валидно.
 Когато базовите таблици в определението на име-view са изменяни с
  ALTER TABLE, така се проверява дали то е валидно.
 Не променя дефиницията на име-view.
Бази данни, М. Филипова                                               15
                 Използване на виртуални таблици
Виртуална таблица е напълно обновяема
– Не съдържа ключовата дума DISTINCT.
– Във фразата FROM има само едно име на таблица.
– В целевия списък няма изрази или агрегатни функции.
– Условието в WHERE не съдържа подзаявка.
– Няма групиране – фрази GROUP BY и HAVING.
– Не съдържа UNION, INTERSECT, MINUS.

Виртуална таблица е частично обновяема
– Ако има изрази в целевия списък - ограничен UPDATE.
– Ако има съединение, в някои случаи може частични
  обновявания.

Бази данни, М. Филипова                                 16
Примери

CREATE VIEW vemp1
 AS SELECT eno, ename, sal, job, addr, dno
 FROM emp
 WHERE dno = 1
 WITH CHECK OPTION;
CREATE VIEW vemp
 AS SELECT eno, ename, job, addr
 FROM emp;
CREATE VIEW vpay (nom, name, ysal)
 AS SELECT eno, ename, sal*12
 FROM emp;
Бази данни, М. Филипова                      17
Примери

CREATE VIEW vemp_dep (eno, ename, dno,dname)
 AS SELECT e.eno, e.ename, d.dno, d.dname
 FROM emp e, dep d
 WHERE e.dno = d.dno;
CREATE VIEW vdep (dno, losal, hisal, avgsal)
 AS SELECT dno, MIN(sal), MAX(sal), AVG(sal)
 FROM emp
 GROUP BY dno;



Бази данни, М. Филипова                        18
                          Синоними
  CREATE [PUBLIC] SYNONYM синоним
  FOR { име-таблица | име-sequence };
  DROP [PUBLIC] SYNONYM синоним;




Бази данни, М. Филипова                 19

								
To top