SQL - DDL

DDL (Data Definition Language) - jazyk pre 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, 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 indentifikujeme 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 sa robí 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) - celé číslo
  • kapitálové výdavky (kapitalove) - celé číslo

CREATE

CREATE DATABASE

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

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 ukadanie 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 tabľ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íoade 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átný 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úceom 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	Lorem ipsum dolor sit amet, consectetur adipiscing...
2	projek 2	IP	2009-03-02	2010-12-22	Nulla eget pretium dui. Donec feugiat dui a lorem ...
3	projek 3	STREP	2008-05-07	2009-02-19	Duis ullamcorper nisi non urna auctor sed tincidun...
4	projek 4	CRP	2009-04-15	2011-07-21	Quisque congue tristique enim, eleifend eleifend n...
5	projek 5	CRP	2008-07-15	2012-10-17	Nullam ac nunc vitae est tempus blandit adipiscing...

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 SECURUTY - určuje, kto môže pohľad spustiť. Povolené hdnoty sú DEFINER a INVOKER
• select_statement - [SQL - select|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:

   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
• 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ú 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 rozpočet:

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

Ak si skontrolujete tabuľkku rozpočet 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 triggerch 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
• trigger_name - názov triggeru
• trigger_time - čas spustenie triggera. Povolené hodnoty:
  • BEFORE
  • AFTER
• trigger_event - akcia triggeru. Povolené hodnoty:
  • INSERT
  • UPDATE
  • DELETE
• FOR EACH ROW - Pri datazoch, ktoré ovplyvnia viaceré záznamy v tabuľke, sa trigger spustí pre každý riadok tabuľky zvlášť.

Úloha: Vytvorte trigger, ktorý sa bude spúšťať pred vložením záznamu do tabuľky rozpočet. Tento trigger nedovolí urobiť zmenu rozpočtu aktuálneho projektu mimo obdobia riešenia projektu. Hodnota atribútu 'rok' môže byť len 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;
USE `projekty`$$

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 zmany 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ácie 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 dupliuká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ázv stĺpca v tabuľke.
• DROP - Slúži na odstranienie objektu v tabulke, napr: DROP INDEX slúži na odstránenie indexu v tabuľke.
• RENAME - Premenovanie tabuľky

Úloha: Zmente 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:

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] ...
    [RESTRICT | CASCADE]

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