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 -
• db_name -
• CHARACTER SET -
• COLLATE -

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 -
• IF NOT EXISTS -
• tbl_name -

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

• CONSTRAINT -
• PRIMARY KEY -
• INDEX -
• KEY -

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

• NOT NULL -
• NULL -
• DEFAULT -
• AUTO_INCREMENT -
• UNIQUE -
• PRIMARY -
• COMMENT -

Pri definícii typu indexu (index_type):

• BTREE -
• HASH -

Ú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` INT NOT NULL ,
`kapitalove` INT NOT NULL ,
PRIMARY KEY ( `projekt_id` , `projekt_rok` ) 
) ENGINE = MYISAM ;

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 -
• ALGORITHM -
• DEFINER -
• SQL SECURUTY -
• select_statement -

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
• sp_name
• proc_parameter
  • IN
  • OUT
  • INOUT

Ú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
• trigger_name
• trigger_time
• trigger_event
• FOR EACH ROW

Ú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
• FIRST, AFTER
• ADD
• ALTER
• CHANGE
• MODIFY
• DROP
• RENAME

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

...riešenie

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:

...riešenie

Mazanie ostatných objektov

Mazanie pohľadu:

DROP VIEW `rozdelenie_projektov`

Mazanie uloženej procedúry:

Mazanie triggera: