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

Tu bude popis nasledujúcich parametrov:

• IF NOT EXISTS - príkaz neprevedie žiadnu akciu
• db_name - názov databázy
• CHARACTER SET - Ak by ste nepoužili príkaz SET CHARACTER SET utf8, databáza by sa snažila vkladané textové reťazce ešte raz konvertovať do UTF-8. Vzniklo by tzv. "dvojité" kódovanie
• COLLATE - usporiadanie reťazcov

Príklad:

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

   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}

kde pri definícii tabuľky:

• TEMPORARY - ten označuje, že sa má vykonať následujúca operacia, ale že se bude jednať len o dočasný príkaz
• IF NOT EXISTS - zabránenie chyby dochádza, ak tabuľka existuje. Avšak, nie je tam žiadné overenie, že existujúce tabuľky majú rovnakú štruktúru ako údaje uvedené v príkaze CREATE TABLE.
• tbl_name - názov tabuľky

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

• CONSTRAINT - Môžete umiestniť obmedzenia na obmedzenie typu dát, ktoré môžu ísť do tabuľky.
• PRIMARY KEY - primarny kľúč
• INDEX - Index v SQL je vytvorený na základe existujúcich tabuliek pre rýchle načitávanie riadkov.
• KEY - kľúč

pri definícii konkrétneho typu (column_definition):

• NOT NULL - vybrané polia vždy obsahujú nejakú hodnotu. To znamená, že nie je možné vložiť nový záznam, či aktualizovať záznam bez pridania hodnoty k tejto oblasti.
• NULL - hodnoty predstavujú chýbajúce neznáme údaje. V predvolenom nastavení môže stĺpec tabuľky držať hodnoty NULL.
• DEFAULT - je príkaz ktorý nám vraví čo sa má stať
• AUTO_INCREMENT - umožňuje jedinečné číslo, ktoré vznikná, keď je nový záznam vložený do tabuľky.
• UNIQUE - bude vytvorený unikátný index z hodnôt tochto slpca, hodnota NULL je z indexu vylúčena
• PRIMARY - primárny kľúč
• COMMENT - Poznamka autora k vysvetleniu čo daný skript robí

Pri definícii typu indexu (index_type):

• BTREE - spôsob umiestnenia a umiestnenia súborov (tzv. záznamy alebo kľúče) v databáze.
• HASH - Z auditu zmeny dát na zachytenie údajov pre načítanie dátového skladu, hash polia majú viac praktické použitie.

Ú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;

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 - Funkcia nahradí všetky výskytnuté určené reťazce ako hľadaný reťazec s náhradným reťazcom.
• ALGORITHM - používa sa na dolovanie dát.
• DEFINER - môže v rámci konštantného výrazu použiť skôr definovanú symbolickú konštantu
• SQL SECURUTY - vytvorí na databázovom serveri uloženú procedúru
• select_statement - definuje a vytvára nemenovanú tabuľku výsledkov.

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

CREATE ALGORITHM = UNDEFINED VIEW `clenenie_projektov` AS SELECT * 
FROM `projekt` 
ORDER BY `projekt`.`typ_projektu` ASC

Vytvorenie pohľadu rodelenie_projektov

CREATE ALGORITHM = UNDEFINED VIEW `Rozdelenie_projektov` AS SELECT id, typ_projektu
FROM `projekt`

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 - uložené 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 - nastavuje užívateľskú premennú alebo zobrazí jej hodnotu
• sp_name - názov procedúry
• proc_parameter - procedúra parametrov
  • IN - operátor umožňuje zadať viac hodnôt v klauzule WHERE.
  • OUT - je odovzdaný na riadenie, ale môže byť menený a odlišný od zmeneného stavu, kedy konanie skončí.
  • INOUT - premenná je odovzdaná do riadenia alebo funkcie, a to môže byť používaná v zmenenom bloku.

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

CREATE PROCEDURE zvysKV(.....

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

   CALL zvysKV(1)

CREATE TRIGGER

Príkaz CREATE TRIGGER vytvorí v databáze trigger (spúšťač, resp. automaticky spúšťanú procedúru). Jeho 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 - nastavuje užívateľskú premennú alebo zobrazí jej hodnotu
• trigger_name - názov triggeru
• trigger_time - čas triggeru
• trigger_event - akcia triggeru
• FOR EACH ROW - táto klauzula hovorí, či sa trigger má vykonať zvlášť pre každý riadok, alebo iba raz

Úloha: Vytvorte trigger, ktorý sa bude spúšťať pred modifikáciou záznamu v tabuľke rozpočet. Tento trigger nedovolí urobiť zmenu rozpočtu aktuálneho projektu mimo obdobia riešenia projektu. Teda ak je projekt naplánovaný od roku 2010 do 2012, tak ak sa niekto pokúsi urobiť zmenu mimo tohoto obdobia, tento trigger mu to nedovolí.

Riešenie:

...riešnie

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 [TEMPORARY] TABLE 'projekt' AND '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 | SCHEMA} [IF EXISTS] 'projekty'

Mazanie ostatných objektov

Mazanie pohľadu:

DROP VIEW `rozdelenie_projektov`

Mazanie uloženej procedúry:

DROP VIEW 'PROCEDURE zvysKV'

Mazanie triggera: