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 {DATABASE | SCHEMA} db_name
    UPGRADE DATA DIRECTORY NAME

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

kde:

• alter_specification -

Úloha: Zmeňte znakovú sadu databázy projekty na utf8-slovak-ci.

Riešenie:

...riešenie

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,...) 
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} 
| 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 - ignorovať
• FIRST, AFTER - keď všetky operácie uvedené v spustení príkazu SQL sa úspešne implementovali.
• ADD - pridávanie dát
• ALTER - zmeniť
• CHANGE - príkaz možno použiť pre zmenu detailu alebo stĺpca v MySQL
• MODIFY - používa sa ak chcete zmeniť veľkosť stĺpca
• DROP - slúži na odstranienie niečoho v tabulke, napr: DROP INDEX slúži na odstránenie indexu v tabuľke.
• RENAME - zmena mena

Ú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

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 `rozdelenie_projektov`

Mazanie uloženej procedúry:

DROP VIEW 'PROCEDURE zvysKV'

Mazanie triggera: