ORACLE - správa tabulek a základy práce s daty
Osnova
textové konstanty
- 'retezec'
- N'řetězec'
- 'That''s it!'
Řetězce
- CHAR(delka)
- řetězec dané délky
- NCHAR(delka)
- řetězec dané délky v nár. znakové sadě
- VARCHAR2(max_delka)
- řetězec proměnné délky
- NVARCHAR2(max_delka)
- VARCHAR2 v nár. znakové sadě
(VARCHAR je synonymum pro VARCHAR2, ale v budoucnu se to může změnit)
Čísla
- NUMBER(pocet_cislic,desetinna_mista)
- čísla s pevnou řádovou čárkou
- NUMBER(pocet_cislic)
- celé číslo
- (NUMBER(pocet_cislic,0))
- NUMBER
- číslo s plovoucí řádovou čárkou (?)
- FLOAT
- číslo s plovoucí řádovou čárkou
Příklady
- 123.45 uloženo do NUMBER(3,-1) se převede na 120
- 0.0019 uloženo do NUMBER(1,3) se převede na 0.002
Další datové typy
- DATE
- datum a čas
- pole SYSDATE tabulky DUAL obsahuje dnešní datum
- LONG
- dlouhé řetězce (až do 2GB)
- nedá se podle nich indexovat, nesmí se použít v GROUP BY, WHERE, ...
- RAW, LONG RAW
- binární data
- při konverzi na CHAR se RAW převádí na hexadecimální zápis
- LOB (Large OBjects)
- rozsáhlá data (až do 4GB)
- BFILE (ukládá se do souboru), BLOB, CLOB, NCLOB
explicitní datové konverze
| TO_CHAR(NUMBER) |
| TO_CHAR(DATE) |
| TO_NUMBER(CHAR) |
| TO_DATE(CHAR) |
| RAWTOHEX(RAW) |
| HEXTORAW(CHAR) |
NULL
| speciální hodnota |
| každý výraz, který obsahuje NULL, má celý hodnotu NULL |
| každé porovnání, které obsahuje NULL, má hodnotu UNKNOWN |
| operátory IS NULL, IS NOT NULL |
| když se mi NULL nehodí, můžu použít funkci NVL(default,promenna) |
Komentáře
- /* rozsáhlý, obsahem bohatý \n komentář na více řádků */
- -- krátká poznámka \n
Operátory
- ||
- konkatenace řetězců
- IN
- je prvkem
SELECT * FROM emp WHERE job IN ('CLERK','ANALYST');
SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30);
- ANY (SOME), ALL
- existenční a obecný kvantifikátor
- možno použít v kombinaci s =,!=,>,<,<=,>=
SELECT * FROM emp WHERE sal >= ALL ( 1400, 3000);
- BETWEEN x AND y
- příslušnost do intervalu
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000;
- x LIKE y [ESCAPE 'z']
- y může obsahovat wildcardy:
- % lib. řetězec (i prázdný)
- _ právě jeden znak
Vytvoření tabulky
Základní formát:
CREATE TABLE table ( column datatype , ... );
Rozšířený formát:
CREATE TABLE [schema.]table ( table_constraint | column datatype
[DEFAULT expr] [column_constraint...] ...) [AS subquery];
...a pořád je vynechané:
objektové rozšíření,ukládání tabulky, ukládání velkých položek tabulky (sloupce
typu LOB), nastavení paralelního zpracování, zapínání/vypínání integritních
omezení
Příklad:
CREATE TABLE hory ( nazev VARCHAR(30),
vyska NUMBER(4,0),
poprve DATE,
zeme VARCHAR(20)
);
Vytvoření tabulky dotazem:
CREATE TABLE table [ ( column, ... ) ] AS subquery;
- v def. sloupců mohou být jména, defaultní hodnoty a integritní omezení
- def. sloupců nesmějí obsahovat dat. typy sloupců
- automaticky se definují NOT NULL int.om.
- pokud se subquery skládá ze sloupců, ne z výrazů, nemusí příkaz
sloupce vůbec definovat
Příklad:
CREATE TABLE emp_tmp AS SELECT * FROM emp WHERE deptno = 10;
schémata
Schémata umožňují současně provést skupiny příkazů CREATE TABLE, CREATE VIEW
a GRANT.
CREATE SCHEMA AUTHORIZATION blair
CREATE TABLE sox (color VARCHAR2(10) PRIMARY KEY, quantity NUMBER)
CREATE VIEW red_sox AS SELECT color, quantity FROM sox WHERE color = 'RED'
GRANT select ON red_sox TO waites;
Integritní omezení
rozlišují se integ.om. pro sloupce (column_constraint) a pro celou tabulku
(table_constraint)
- NOT NULL
- zakáže pro daný sloupec prázdné hodnoty
- UNIQUE
- žádné dvě neprázdné hodnoty nejsou stejné
- mohou se skládat z více polí
- PRIMARY_KEY
- žádné dvě hodnoty nejsou stejné a ve sloupci nejsou
prázdné hodnoty
- mohou se skládat z více polí
- CHECK
- podmínka pro záznamy tabulky
Příklady:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(9),
loc VARCHAR2(10),
CONSTRAINT unq_dname
UNIQUE (dname);
ALTER TABLE census
ADD CONSTRAINT unq_city_state
UNIQUE (city, state);
CREATE TABLE dept
(deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
dname VARCHAR2(9),
loc VARCHAR2(10) );
ALTER TABLE ship_cont
ADD PRIMARY KEY (ship_no, container_no) DISABLE;
CREATE TABLE dept
(deptno NUMBER CONSTRAINT check_deptno
CHECK (deptno BETWEEN 10 AND 99)
DISABLE,
dname VARCHAR2(9) CONSTRAINT check_dname
CHECK (dname = UPPER(dname))
DISABLE,
loc VARCHAR2(10) CONSTRAINT check_loc
CHECK (loc IN ('DALLAS','BOSTON',
'NEW YORK','CHICAGO'))
DISABLE);
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
CHECK (sal + comm <= 5000) );
referenční integritní omezení
- FOREIGN KEY
- odkaz na klíč jiné tabulky
- REFERENCES
- na tento klíč jsou odkazy v jiných tabulkách
- ON DELETE CASCADE
- po mazání nezbudou neplatné odkazy v jiných tab.
- pokud je vypnuté, problematické mazání se nepovolí
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT fk_deptno
REFERENCES dept(deptno)
ON DELETE CASCADE );
zapínání, vypínání a odkládání integritních omezení
constraint [[NOT] DEFERRABLE] [INITIALLY [IMMEDIATE | DEFERRED]]
[ENABLE [VALIDATE | NONVALIDATE] | DISABLE | EXCEPTIONS INTO [schema.]table ]
- DEFERRABLE
- říká, jestli může být test odložen až do potvrzení transakce
- INITIALLY IMMEDIATE | DEFERRED
- říká, jestli má být ověřování odkládáno
- ENABLE VALIDATE
- testuje stávající, nová i měněná data
- ENABLE VALIDATE
- je zapnuté pro nové nebo měněné záznamy
- DISABLE
- integ. om. je vypnuté
- EXCEPTIONS INTO table
- do table se ukládají identifikátory řádek, které
integ. om. nesplňují
CREATE TABLE orders
(ord_num NUMBER CONSTRAINT unq_num UNIQUE (ord_num)
INITIALLY DEFERRED DEFERRABLE);
přidání sloupců
ALTER TABLE [schema.]table ADD ( add_column_options );
add_column_options: column datatype [ DEFAULT expr] [column_constraint...]
| table_constraint
- sloupce s NOT_NULL se smějí přidat jen do prázdné tab.
- def. hodnota v novém sloupci je NULL
ALTER TABLE emp ADD (thriftplan NUMBER(7,2),loancode CHAR(1) NOT NULL);
změna sloupce
ALTER TABLE [schema.table] MODIFY ( modify_column_option );
modify_column_options: column [datatype] [ DEFUALT expr ] [columnt_constraint...]
- změny mezi CHAR a VARCHAR2 jsou možné, pokud neovlivní uložená data
- typy je možné "rozšiřovat" (VARCHAR2(5)->VARCHAR2(10), NUMBER(3,1)->NUMBER(3,2))
- "zužování" je možné, jen když jsou ve sloupci jenom prázdné (NULL) hodnoty
ALTER TABLE accounts MODIFY (bal DEFAULT NULL);
přejmenování tabulky
ALTER TABLE [schema.]name RENAME TO newname;
DROP TABLE [schema.]table CASCADE CONSTRAINTS;
- CASCADE CONSTRAINTS
- všechny ref.integ.om., která se tabulky týkají
se zahodí spolu s tabulkou
INSERT INTO [schema.]table [(column ...)] subquery | VALUES (expr...)
INSERT INTO (subquery_1) [(column ...)] subquery | VALUES (expr...)
- pokud je uveden seznam sloupců, musí počet sloupců odpovídat počtu sloupců
subquery nebo počtu položek ve VALUES
- sloupce, které nejsou uvedeny, se nastaví na default hodnoty
- pokud není uveden sloupec s NOT NULL integ. om., jde o chybu a INSERT se
neprovede
- subqeury - přidá do tabulky řádky vrácené dotazem subquery
INSERT INTO emp (empno, ename, job, sal, comm, deptno)
VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);
INSERT INTO (select empno, ename, job, sal, comm, deptno from emp)
VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);
INSERT INTO bonus
SELECT ename, job, sal, comm
FROM emp
WHERE comm > 0.25 * sal
OR job IN ('PRESIDENT', 'MANAGER');
UPDATE [schema.]table SET col1 = expr1 [, col2 = expr2, ...] [WHERE condition]
UPDATE [schema.]table SET col1 = (subquery) [...] [WHERE condition]
UPDATE [schema.]table SET (column...) = subquery [WHERE condition]
UPDATE (subquery) ...
Příklady:
UPDATE emp
SET job = 'MANAGER', sal = sal + 1000, deptno = 20
WHERE ename = 'JONES';
UPDATE emp a
SET deptno =
(SELECT deptno
FROM dept
WHERE loc = 'BOSTON'),
(sal, comm) =
(SELECT 1.1*AVG(sal), 1.5*AVG(comm)
FROM emp b
WHERE a.deptno = b.deptno)
WHERE deptno IN
(SELECT deptno
FROM dept
WHERE loc = 'DALLAS'
OR loc = 'DETROIT');
Komentáře k druhému příkladu:
- pracuje se zaměstnanci z Dallasu a Detroitu
- jejich deptno nastaví na deptno Bostonu
- každému zaměstnanci nastaví plat na 1.1 násobek prům. platu v jeho oddělení
- každému zaměstnanci nastaví prémie (commision) na 1.5 násobek prům. prémií v oddělení
DELETE [FROM] [schema.]table alias [WHERE condition]
DELETE [FROM] (subquery) [WHERE condition]
Příkady:
DELETE FROM temp_assign;
DELETE FROM emp
WHERE JOB = 'SALESMAN'
AND COMM < 100;
DELETE FROM (select * from emp)
WHERE JOB = 'SALESMAN'
AND COMM < 100;