Manipulace s daty v SQL
Práce s daty, 6. dubna 2023
Příkaz CREATE
• Vytvoří nový objekt, zejména databázi či tabulku
• Ale také např. uživatele, funkci, pohled, index, spouštěča další
• Vytvoření databáze
• CREATE database databáze;
• Vytvoření tabulky
• CREATE TABLE tabulka (definice) [nastavení];
• CREATE TABLE tabulka LIKE jiná_tabulka;
• Zkopíruje definici existující tabulky
• CREATE TABLE tabulka [AS] SELECT dotaz;
• Vytvoří novou tabulku na základě výsledku specifikovaného dotazu
Příklad vytvoření tabulky
CREATE TABLE tabulka (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
male_cislo TINYINT,
desetinne_cislo DECIMAL(10,4),
popisek VARCHAR(10),
fejeton TEXT COMPRESSED,
datum DATE,
PRIMARY KEY(id)
) ENGINE=MyISAM
Datové typy v databázích (výběr)
• Číselné
• TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
• FLOAT, DOUBLE, DECIMAL
• Textové
• CHAR, VARCHAR
• TEXT, MEDIUMTEXT, LONGTEXT
• ENUM
• Datum a čas
• DATE, TIME, DATETIME,YEAR
• TIMESTAMP
Možnosti sloupců
• UNSIGNED | SIGNED (výchozí) – záporné hodnoty (ne)povoleny
• NOT NULL – hodnota NULL není v daném sloupci povolena
• DEFAULT hodnota – výchozí hodnota v daném sloupci
• AUTO_INCREMENT – automaticky přiřazené číslo max+1
• COMPRESSED – komprimované uložení textových proměnných
• KEY – indexovaný sloupec
• UNIQUE [KEY] – ve sloupci nesmí být duplicity (kromě NULL)
• PRIMARY KEY – primární klíč tabulky
• ON UPDATE CURRENT TIMESTAMP – aktualizace časového razítka
Příkaz SHOW
Vypisuje užitečné informace o stavu a objektech databáze
• Definice stávajících databází či tabulek:
• SHOW CREATE DATABASE databáze;
• SHOW CREATE TABLE tabulka;
• Výpis aktuálního nastavení databázového systému:
• SHOW STATUS;
• SHOW VARIABLES [LIKE 'sql%'];
• Výpis varování z předchozího příkazu:
• SHOW WARNINGS;
• A mnoho dalšího
Cvičení I
Vytvořte definice následujících tabulek
• Letadla
• označení, název, přepravní kapacita, doletová vzdálenost, cena, …
• Letiště
• kód, název, země, …
• Lety
• identifikátor, letadlo, odkud, kam, datum a čas odletu, datum a čas příletu
• Vzdálenosti
• letiště A, letiště B, vzdálenost
Příkazy INSERT, REPLACE a UPDATE
• Vložení nového řádku dat do tabulky
• INSERT INTO tabulka [(sl1, sl2, …)] VALUES (hd1, hd2, …);
• INSERT INTO tabulka SET sl1=hd1, sl2=hd2, …;
• INSERT INTO tabulka [(sl1, sl2, …)] SELECT …;
• Výsledek dotazu musí odpovídat datovým typům sloupců tabulky, do které vkládáme
• REPLACE tabulka …
• Přidá nový řádek nebo nahradí stávající se stejným unikátním (primárním) klíčem
• Aktualizace řádku(ů) tabulky
• UPDATE tabulka SET sl1=hd1, sl2=hd2, … [WHERE podmínka];
• Pokud podmínka nebude zadána, aktualizují se všechny řádky tabulky
• Při UPDATElze spojovat tabulky a tedy podmínku vztáhnout na údaje odjinud
Příkazy DELETE a DROP
Mazání dat z tabulky
• DELETE FROM tabulka WHERE podmínka;
• Smaže všechny vyhovující řádky v dané tabulce
• Pokud podmínka není zadána, smaže všechny řádky tabulky (!!!)
• DELETE je opakem příkazu INSERT
Odstranění objektu
• DROP TABLE tabulka;
• DROP DATABASE databáze;
• DROP je opakem příkazu CREATE
Cvičení II
Naplňte své nové tabulky daty
• Tj. tabulky s letadly, letišti, lety i vzdálenostmi
• Datum a čas zadáváme ve tvaru '2023-04-05 11:20:36'
Vyzkoušejte si také aktualizaci těchto dat
A vyzkoušejte si také mazání vybraných řádků tabulky
• I odstranění tabulky, ale s rozmyslem
Import dat do databáze
Hromadně lze data do tabulky importovat z prostých textových
souborů, kde jsou hodnoty odděleny vhodným znakem (např. středník):
LOAD DATA [LOCAL] INFILE 'C:\\data.csv'
[REPLACE | IGNORE ] INTO TABLE tabulka
[FIELDS TERMINATED BY 'znak']
[LINES TERMINATED BY 'řetězec']
[IGNORE počet LINES]
[(sl1, sl2, …)]
[SET sl1=hd1, sl2=hd2, …];
Vkládání existujících dat / objektů
V databázi nelze mít dvě tabulky se stejným jménem: nahrazení:
• CREATE OR REPLACE tabulka (definice sloupců);
Vkládání nebo aktualizace dat skončí chybou, pokud
• vkládáme data (zejména čísla) mimo možný rozsah
• vkládáme hodnotu NULL tam, kde to není povoleno
• vkládáme duplicitní hodnoty tam, kde to není povoleno
Řešení = ignorování problému:
• INSERT IGNORE…, UPDATE IGNORE…, LOAD DATA… IGNORE…
Export dat z databáze
Uložení dat do prostého textového souboru:
• SELECT … INTO OUTFILE 'soubor'
[FIELDS TERMINATED BY 'znak'];
Jiné možnosti
• Klient mysql – s pomocí direktivy „-e“ lze spustit příkaz a jeho
výsledek přesměrovat do souboru
• Program mysqldump / mariadb-dump – vytvoří zálohu databáze
Cvičení III
Importujte do své databáze data ze souboru knihy2022.csv
• Tento soubor je dostupný ve studijních materiálech v IS MU
• Nejdříve budete muset vytvořit tabulku
• S předpisem může pomoci import na sqliteonline.com
• Soubor můžete vložit lokálně „od sebe“, ze svého počítače
• Nebo využít verze na serveru: /var/lib/mysql/knihy2022.csv
• Bude nutné si nějak poradit s tím, že u některých záznamů není
uvedena cena