SQL - DDL

Materiály k cvičeniam

>SQL - dátové typy
>SQL - select
>SQL - update, insert, delete
>SQL - DDL
>SQL - DCL
>SQL - vstavané funkcie
>SQL - pohľady
>SQL - procedúry
>SQL - triggery

MySQL a web

Databázové systémy - neriešené príklady

Toto je projekt, na ktorom sa ešte stále pracuje!!

Aj keď sú v tomto dokumente použiteľné informácie, ešte nie je dokončený. Svoje návrhy môžete vyjadriť v diskusii o tejto stránke.

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.

Obsah

1 Prípadová štúdia - evidencia projektov
2 CREATE
3 ALTER
- 3.1 ALTER DATABASE
- 3.2 ALTER TABLE
4 DROP
5 Zdroje a odkazy

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 ^[1]
- 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^[2]

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^[3]

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 - príkaz neprevedie žiadnu akciu
tbl_name - názov tabuľky

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

CONSTRAINT - jedná sa v podstate o obmedzené pomenovanie
PRIMARY KEY - primarny kľúč
INDEX - spracujú vždy všetky vety v databáze
KEY - kľúč

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

NOT NULL - hodnota bez nuly
NULL - nula
DEFAULT - je príkaz ktorý nám vraví čo sa má stať
AUTO_INCREMENT - pomáha vygenerovať hodnotu
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 - Je špecifický tím, že má rád n a limity na maximálne (n), i minimálne (\left \lceil \frac{n}{2} \right \rceil) počet potomkov vrcholu.
HASH - Pre každý príkaz vyhľadá a zapametá polohu v ceste pre hľadanie príkazu.

Ú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^[4] 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 - základy kryptografie
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ľka 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^[5] 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 - V
- OUT - nesprávna procedúra
- INOUT - v nesprávnej procedúre

Ú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^[6] 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 DATABASE^[7]dovoľ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 TABLE^[8]dovoľ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 -
ADD - pridávanie dát
ALTER - zmeniť
CHANGE -
MODIFY -
DROP - Odeberie jeden alebo viac databáz alebo databazové snímky z instance SQL Server
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^[9]. 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^[10]. 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:

Zdroje a odkazy

↑ Typy projekov - http://www.bic.sk/projects.php?id=32&tid=24
↑ Create database (mysql) http://dev.mysql.com/doc/refman/5.5/en/create-database.html
↑ Create tabel (mysql) http://dev.mysql.com/doc/refman/5.5/en/create-table.html
↑ create view (mysql) http://dev.mysql.com/doc/refman/5.5/en/create-view.html
↑ CREATE PROCEDURE (mysql) http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html
↑ CREATE TRIGGER (mysql) http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html
↑ ALTER DATABASE (mysql) http://dev.mysql.com/doc/refman/5.5/en/alter-database.html
↑ ALTER TABLE(mysql) http://dev.mysql.com/doc/refman/5.5/en/alter-table.html
↑ Drop table (mysql) http://dev.mysql.com/doc/refman/5.5/en/drop-table.html
↑ Drop database (mysql) http://dev.mysql.com/doc/refman/5.5/en/drop-database.html

[1] Typy projekov - http://www.bic.sk/projects.php?id=32&tid=24

[2] Create database (mysql) http://dev.mysql.com/doc/refman/5.5/en/create-database.html

[3] Create tabel (mysql) http://dev.mysql.com/doc/refman/5.5/en/create-table.html

[4] reate view (mysql) http://dev.mysql.com/doc/refman/5.5/en/create-view.html

[5] CREATE PROCEDURE (mysql) http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html

[6] CREATE TRIGGER (mysql) http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

[7] ALTER DATABASE (mysql) http://dev.mysql.com/doc/refman/5.5/en/alter-database.html

[8] ALTER TABLE(mysql) http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

[9] Drop table (mysql) http://dev.mysql.com/doc/refman/5.5/en/drop-table.html

[10] Drop database (mysql) http://dev.mysql.com/doc/refman/5.5/en/drop-database.html

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

SQL - DDL

Obsah

Prípadová štúdia - evidencia projektov

CREATE

CREATE DATABASE^[2]

CREATE TABLE^[3]

CREATE VIEW

CREATE PROCEDURE

CREATE TRIGGER

ALTER

ALTER DATABASE

ALTER TABLE

DROP

DROP TABLE

DROP DATABASE

Mazanie ostatných objektov

Zdroje a odkazy

Navigačné menu

Osobné nástroje

Menné priestory

Varianty

Zobrazenia

Viac

Hľadať

Navigácia

Rýchle linky

Nástroje

SQL - DDL

Obsah

Prípadová štúdia - evidencia projektov

CREATE

CREATE DATABASE[2]

CREATE TABLE[3]

CREATE VIEW

CREATE PROCEDURE

CREATE TRIGGER

ALTER

ALTER DATABASE

ALTER TABLE

DROP

DROP TABLE

DROP DATABASE

Mazanie ostatných objektov

Zdroje a odkazy

Navigačné menu

Hľadať

CREATE DATABASE^[2]

CREATE TABLE^[3]