SQL - DDL

DDL (Data Definition Language) - jazyk pre definovanie dátových štruktúr. V databázových systémoch predstavuje podskupinu jazyka SQL pre vytváranie a modifikáciu databázových objektov.

Prípadová štúdia - evidencia projektov

V tejto kapitole bude opísaný postup pri vytváraní novej databázy a nových tabuliek. Následne sa vytvorené tabuľky pokúsime zmodifikovať (pridanie atribútov, zmena názvu atribútov...) a na koniec zmažeme všetky tabuľky aj databázu.

Pre túto kapitolu si zoberme príklad evidencie projektov a ich rozpočtu. V našom príklade identifikujeme 2 základné entity: projekt a rozpočet na projekt. Atribúty týchto entít:

• projekt
  • id projektu (id) - primárny kľúč,
  • názov projektu (nazov) - reťazec,
  • typ projektu (typ_projektu) - vymenovaný typ: IP, ESF, STREP, CRAFT, CRP ,
  • začiatok riešenia projektu (zaciatok) - dátum,
  • koniec riešenia projektu (koniec) - dátum,
  • obsah riešenia projektu (obsah) - text.

Keďže v projektoch je na každý rok iný rozpočet, v tabuľke rozpočet bude predstavovať jeden záznam rozpočet pre daný projekt na jeden rok. Ak by sa projekt riešil 5 rokov, v tabuľke rozpočet bude pre tento projekt 5 záznamov.

• rozpočet
  • id projektu, pre ktorý je tento rozpočet (projekt_id) - primárny kľúč
  • rok riešenia projektu (projekt_rok) - primárny kľúč
  • bežné výdavky (bezne) - reálne číslo
  • kapitálové výdavky (kapitalove) - reálne číslo

CREATE

CREATE DATABASE

Pomocou príkazu CREATE DATABASE vytvárame novú databázu. Syntax je nasledujúca:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification::=
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

Význam kľúčových slov v predchádzajúcej definícii:

• IF NOT EXISTS - databáza sa vytvorí iba ak neexistuje. Pri pokuse o vytvorenie novej databázy s názvom ktorý je už použitý sa nevykoná žiadna akcia.
• db_name - názov databázy.
• CHARACTER SET - Definícia preddefinovanej znakovej sady pre databázu. Pri neuvedení znakovej sady sa použije latin-1.
• COLLATE - definovanie spôsobu usporiadavania reťazcov.

Príklad:

Vytvorte databázu projekty. Znakovú sadu pre celú databázu nastavte utf-8.

Riešenie:

   CREATE DATABASE projekty CHARACTER SET utf8

CREATE TABLE

Pomocou príkazu CREATE TABLE vytvárame novú tabuľku. Syntax je nasledujúca:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

create_definition::=
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...

column_definition::=
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']

data_type::=
    pozri "Dátové typy"

index_type::=
    USING {BTREE | HASH}

Jednotlivé časti definície majú nasledujúci význam:

• TEMPORARY - vytvorí sa dočasná tabuľka. Táto tabuľka bude uložená len v pamäti. Takáto tabuľka nemá fyzickú reprezentáciu na disku. Používa sa na ukladanie vypočítaných hodnôt v procedúrach.
• IF NOT EXISTS - tabuľka sa vytvorí, len ak neexistuje. Týmto sa zabráni chybe, ktorá by vznikla pri pokuse o vytvorenie novej tabuľky s názvom rovnakým aký je už v databáze použitý.
• tbl_name - názov tabuľky.

Pri definícii štruktúry tabuľky (create_definition):

• CONSTRAINT - za kľúčovým slovom CONSTRAINT sa uvádzajú obmedzenia nad tabuľkou ako je napríklad definícia kľúčov (primárnych, cudzích).
• PRIMARY KEY - definuje primárny kľúč.
• INDEX - Nad zadaným atribútom vytvorí index.
• KEY - kľúčové slovo KEY je synonymom pre INDEX.

Definícia konkrétneho typu (column_definition):

• NOT NULL - určujeme, že atribút musí mať nejakú hodnotu. Čiže vloženie prázdnej hodnoty (NULL) je zakázané.
• NULL - určujeme, že atribút nemusí mať hodnotu. Pri vkladaní môžeme hodnotu pre daný atribút vynechať. V tomto prípade nebude mať žiadnu hodnotu (čiže bude mať hodnotu NULL).
• DEFAULT - nastavíme preddefinovanú hodnotu. Ak pri vkladaní hodnôt pre tento atribút neuvedieme nič, bude vložená hodnota DEFAULT.
• AUTO_INCREMENT - používa sa len pre atribúty s celočíselným dátovým typom. Používa sa hlavne pri primárnych kľúčoch, kde sa hodnota pri vkladaní zväčšuje automaticky.
• UNIQUE - nad atribútom bude vytvorený unikátny index. Pri vkladaní nie je dovolené vkladanie opakujúcich sa hodnôt. Hodnota NULL je povolená, ale iba raz.
• PRIMARY - definujeme primárny kľúč.
• COMMENT - komentár k vytváraciemu dotazu. Nemá žiadny vplyv na SQL dotaz.

Pri definícii typu indexu (index_type):

• BTREE, HASH - určujeme typ indexu. Index typu 'Binárny strom' alebo 'Hašovacia tabuľka'.

Úloha:

Podľa prípadovej štúdie (evidencia projektov) vytvorte tabuľky projekt a rozpocet. Typy jednotlivých atribútov sú opísané taktiež v tejto prípadovej štúdii na začiatku tejto kapitoly.

Riešenie:

Tabuľka projekt:

CREATE TABLE `projekty`.`Projekt` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`nazov` VARCHAR( 64 ) NOT NULL ,
`typ_projektu` ENUM( 'IP', 'ESF', 'STREP', 'CRAFT', 'CRP' ) NOT NULL ,
`zaciatok` DATE NOT NULL ,
`koniec` DATE NOT NULL ,
`obsah` TEXT NOT NULL
) ENGINE = InnoDB;

Tabuľka rozpocet:

CREATE TABLE `projekty`.`rozpocet` (
`projekt_id` INT NOT NULL ,
`projekt_rok` INT NOT NULL ,
`bezne` REAL NOT NULL ,
`kapitalove` REAL NOT NULL ,
PRIMARY KEY ( `projekt_id` , `projekt_rok` ) 
) ENGINE = InnoDB;

Tabuľky projekt a rozpocet naplníme. V nasledujúcom texte uvažujeme s nasledujúcom obsahom tabuliek:

Tabuľka Projekt

id	nazov	typ_projektu	zaciatok	koniec	obsah
1	projek 1	IP	2010-01-01	2010-12-14	Prvý projekt sa zaoberá výskumom efektívnej zážitkovej metódy (EZM)...
2	projek 2	IP	2009-03-02	2010-12-22	Projekt 2 kladie EZM do kontrastu s telesnými trestami pre zvýšenie efektu...
3	projek 3	STREP	2008-05-07	2009-02-19	Tento projekt skúma metódy psychodiagostiky v EZM...
4	projek 4	CRP	2009-04-15	2011-07-21	V projekte je praktická implementácia sedliackeho rozumu v psychológii
5	projek 5	CRP	2008-07-15	2012-10-17	Tento projekt je len 'tunelovací'...

Tabuľka Rozpocet

projekt_id	projekt_rok	bezne	kapitalove
1	1	1234	12345.2
2	1	32	123
2	2	134.3	1123.12
3	1	903	10000
3	2	1234	34224
4	1	42134	234543
4	2	5234563	34556543
5	1	2343	34521
5	2	5654	23456
5	3	65	4567
5	4	343	5678
5	5	2345	45675

CREATE VIEW

Príkazom CREATE VIEW môžeme vytvoriť pohľad. Pohľadom sa venuje samostatná kapitola. Syntax príkazu CREATE VIEW je nasledovná:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement

kde:

• OR REPLACE - sa používa pre aktualizáciu pohľadu. Ak pohľad existuje, tak sa aktualizuje, ak pohľad este neexistuje, tak sa vytvorí.
• ALGORITHM - Určuje spôsob práce s pohľadom (na úrovni databázového serveru). Preddefinované hodnota je UNDEFINED.
• DEFINER - určuje používateľa, ktorý pohľad vytvoril. Ak sa nepoužije, bude použitý aktuálne prihlásený používateľ.
• SQL SECURITY - určuje, kto môže pohľad spustiť. Povolené hodnoty sú DEFINER a INVOKER.
• select_statement - SQL dotaz pre vytvorenie pohľadu.

Príklad:

Vytvorte pohľad na základe dotazu: Koľko je v databáze projektov, rozdelených podľa typov projektov. Nezaujíma nás teda celkový počet projektov, ale počet projektov podľa typu projektu.

Riešenie: SQL dotaz, pomocou ktorého dosiahneme požadovaný výsledok

   SELECT typ_projektu, COUNT(*) FROM projekt
   GROUP BY typ_projektu

Vytvorenie pohľadu rodelenie_projektov

  CREATE ALGORITHM = UNDEFINED VIEW  `pocetProjektov` 
   (projekt,pocet) AS
      SELECT typ_projektu, COUNT(*) 
      FROM projekt
      GROUP BY typ_projektu

Použitie pohľadu je rovnaké ako práca s tabuľkou:

   SELECT * FROM pocetProjektov

Výsledok:

projekt	pocet
IP	2
STREP	1
CRP	2

CREATE PROCEDURE

Príkaz CREATE PROCEDURE vytvorí na databázovom serveri uloženú procedúru. Podrobnejšie o uložených procedúrach hovorí kapitola SQL - procedúry. Syntax je nasledujúca:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    routine_body

proc_parameter::=
    [ IN | OUT | INOUT ] param_name type

type::=
    Ľubovoľný dátový SQL typ.

routine_body::=
    SQL príkaz

kde:

• DEFINER - definujeme vlastníka procedúry. Význam je rovnaký ako pri pohľade.
• sp_name - názov procedúry.

Parametre procedúry: proc_parameter

• IN - parameter bude vstupný.
• OUT - parameter bude výstupný.
• INOUT - parameter bude vstupno - výstupný.

Úloha: Vytvorte uloženú procedúru, ktorá v tabuľke rozpočet zvýši určitému projektu (jeho ID bude vstupný parameter procedúry) bežné a kapitálové výdavky 1.1x.

Riešenie:

DELIMITER $$

CREATE PROCEDURE `projekty`.`zvysKV` (IN  idp INT)
BEGIN
     UPDATE rozpocet
     SET kapitalove=kapitalove*1.1 WHERE projekt_id=idp;
END$$

Ukážka použitia uloženej procedúry

   CALL zvysKV(3)

Výsledný efekt na tabuľke rozpocet:

projekt_id	projekt_rok	bezne	kapitalove
3	1	903	11000
3	2	1234	37646.4

Ak si skontrolujete tabuľku rozpocet na začiatku tejto kapitoly, tak pre projekt č. 3 tam boli hodnoty 10000 a 34224.

CREATE TRIGGER

Príkaz CREATE TRIGGER vytvorí v databáze trigger (spúšťač, resp. automaticky spúšťanú procedúru). Podrobnejšie o triggeroch je v časti SQL - triggery. Syntax je nasledovná:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

kde:

• DEFINER - určuje vlastníka triggera. Význam je rovnaký ako pri pohľade.
• trigger_name - názov triggera.
• trigger_time - čas spustenie triggera. Povolené hodnoty:
  • BEFORE
  • AFTER
• trigger_event - akcia triggeru. Povolené hodnoty:
  • INSERT
  • UPDATE
  • DELETE
• FOR EACH ROW - Pri dotazoch, ktoré ovplyvnia viaceré záznamy v tabuľke sa trigger spustí pre každý riadok tabuľky zvlášť.

Trigger je možné definovať pri nasledujúcich akciách:

• pred vložením nových údajov do tabuľky (BEFORE INSERT),
• pred aktualizáciou údajov v tabuľke (BEFORE UPDATE),
• pred zmazaním údajov v tabuľke (BEFORE DELETE),
• po vložení údajov do tabuľky (AFTER INSERT),
• po aktualizácii údajov (AFTER UPDATE),
• po zmazaní údajov v tabuľke (AFTER DELETE).

V tele triggera máme k dispozícii prístup k hodnotám, ktoré sa budú meniť. Slúžia na to virtuálne tabuľky old a new. Výraz old.stlpec odkazuje na existujúcu položkou v tabuľke pred jej zmenou alebo zmazaním. Výraz new.stlpec odkazuje na hodnotu, ktorá sa bude do tabuľky vkladať alebo aktualizovať. V triggeri INSERT máme k dispozícii len 'nové' údaje, teda len tabuľku new, pretože pri vkladaní nemá zmysel uvažovať o starých hodnotách. V triggeri DELETE máme k dispozícii len 'staré' údaje, teda len tabuľku old, pretože pri mazaní nemá zmysel uvažovať o nových hodnotách. V triggeri UPDATE máme k dispozícii aj 'nové', aj 'staré' údaje. Výraz new.stlpec odkazuje na údaj, ktorý má nahradiť aktuálny údaj v tabuľke. Výraz old.stlpec odkazuje na údaj pred aktualizáciou, teda ten, ktorý ideme meniť.

Úloha:

Vytvorte trigger, ktorý sa bude spúšťať pred vložením záznamu do tabuľky rozpocet. Tento trigger nedovolí urobiť zmenu rozpočtu aktuálneho projektu mimo obdobia riešenia projektu. Hodnota atribútu 'rok' (poradové číslo roku riešenia projektu) môže byť maximálne taká, koľko projekt trvá. Ak teda trvá od 1.1.2009 do 1.1.2010, tak hodnota atribútu rok môže byť len 1.

Riešenie:

-- Trigger DDL Statements
DELIMITER $$
CREATE   TRIGGER kontrolaRokov 
    BEFORE INSERT
    ON rozpocet
    FOR EACH ROW 
BEGIN  
   IF new.projekt_rok > (SELECT YEAR( koniec ) - YEAR( zaciatok ) 
                    FROM projekt WHERE id =new.projekt_id) THEN
      SET new.projekt_id = NULL;
   END IF;     
END;

Otestovanie triggera:

   INSERT INTO rozpocet(projekt_id,projekt_rok,bezne,kapitalove)
          VALUES (1, 3, 1, 12)

Hlásenie databázového serveru:

#1048 - Column 'projekt_id' cannot be null 

ALTER

Príkaz ALTER slúži na modifikovanie štruktúry databázových objektov.

ALTER DATABASE

Príkaz ALTER DATABASEdovoľuje zmeniť vlastnosti databázy ako napríklad meno, znakovú sadu, zotriedenie. Syntax je nasledujúca:

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...

alter_specification::=
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

Kde alter_specification predstavuje zmeny v databáze.

• CHARACTER SET - znaková sada pre databázu.
• COLLATE - definovanie zotrieďovania údajov.

Úloha: Zmeňte zotriedenie databázy projekty na slovenské (utf8_slovak_ci).

Riešenie:

   ALTER DATABASE projekty COLLATE = 'utf8_slovak_ci'

ALTER TABLE

Príkaz ALTER TABLEdovoľuje zmeniť vlastnosti tabuľky ako napríklad meno, dátový typ, znakovú sadu, zotriedenie. Syntax je nasledujúca:

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] 

alter_specification::=
    ADD [COLUMN] create_definition [FIRST | AFTER column_name ] 
| ADD [COLUMN] (create_definition, create_definition,...) 
| ADD INDEX [index_name] (index_col_name,...) 
| ADD PRIMARY KEY (index_col_name,...) 
| ADD UNIQUE [index_name] (index_col_name,...) 
| ADD FULLTEXT [index_name] (index_col_name,...) 
| CHANGE [COLUMN] old_col_name create_definition 
| MODIFY [COLUMN] create_definition 
| DROP [COLUMN] col_name 
| DROP PRIMARY KEY 
| DROP INDEX index_name 
| RENAME [TO] new_tbl_name

kde:

• IGNORE - Špecifikácia IGNORE je nadstavba MySQL nad štandard SQL. Určuje ako sa má vykonať príkaz ALTER TABLE pri narazení na duplikátne kľúče. Ak sa IGNORE neuvedie, tak pri narazení na duplikátne kľúče sa transakcia zruší (rollback). Ak sa uvedie, tak pri narazení na duplikátne hodnoty sa daný riadok vymaže.
• FIRST, AFTER - Určuje, kde sa nový atribút umiestni:
  • FIRST - Nový atribút (stĺpec) bude ako prvý.
  • AFTER 'column_name' - Nový atribúr sa umiestni za atribút 'column_name'.
• ADD - Pomocou tohoto príkazu môžeme do tabuľky pridávať ďalšie stĺpce, indexy, obmedzenia.
• MODIFY - Pomocou tohoto príkazu môžeme v tabuľke meniť existuúce stĺpce, indexy, obmedzenia.
• CHANGE - Mení názov stĺpca v tabuľke.
• DROP - Slúži na odstránenie objektu v tabuľke, napr: DROP INDEX slúži na odstránenie indexu v tabuľke.
• RENAME - Premenovanie tabuľky.

Úloha: Zmeňte názov atribútu zaciatok na zaciatok_riesenia a názov atribútu koniec na koniec_riesenia.

Riešenie:

   ALTER TABLE `projekt` CHANGE `zaciatok` `zaciatok_riesenia` DATE NOT NULL
   ALTER TABLE `projekt` CHANGE `koniec` `koniec_riesenia` DATE NOT NULL

Predchádzajúci SQL dotaz spôsobil zmenu štruktúry tabuľky projekt:

Štruktúra tabuľky projekt po zmene atribútov zaciatok a koniec

Field	Type	Collation
id	int(11)
nazov	varchar(64)	utf8_general_ci
typ_projektu	enum('IP','ESF','STREP','CRAFT','CRP') utf8_general_ci
zaciatok_riesenia	date
koniec_riesenia	date
obsah	text	utf8_general_ci

DROP

Pomocou príkazu DROP sa mažú databázové objekty. Príkaz DROP môžeme použiť na mazanie:

• databáz - DROP DATABASE,
• tabuliek - DROP TABLE,
• procedúr - DROP PROCEDURE,
• triggerov - DROP TRIGGER,
• pohľadov - DROP VIEW.

DROP TABLE

Zmazanie tabuľky sa realizuje pomocou príkazu DROP TABLE. Syntax je nasledujúca:

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...

kde:

• tbl_name - názov tabuľky, ktorú ideme mazať.

Úloha: Zmažte všetky tabuľky na databáze projekty.

Riešenie:

  DROP TABLE projekt
  DROP TABLE rozpocet

DROP DATABASE

Zmazanie databázy sa realizuje pomocou príkazu DROP DATABASE. Syntax je nasledujúca:

   DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Úloha: Zmažte databázu projekty.

Riešenie:

   DROP DATABASE projekty

Mazanie ostatných objektov

Mazanie pohľadu:

   DROP VIEW pocetProjektov

Mazanie uloženej procedúry:

  DROP PROCEDURE zvysKV

Mazanie triggera:

   DROP TRIGGER kontrolaRokov

Poznámka: Mazanie pohľadov, procedúr a triggerov treba vykonať pred zmazaním tabuliek projekt a rozpocet.