Jazyk SQL databáze SQLite
připravil ing. petr polách
SQL - úvod Structured Query Language (strukturovaný dotazovací jazyk – 70. léta min. století)
Standardizovaný dotazovací jazyk používaný pro práci s daty v relačních databázích. Pozn.1: Deklarativní programovací jazyk Pozn.2: Jazyk SQL byl navržen tak, aby se maximálně blížil přirozené angličtině.
http://demo-1.sql-vyuka.cz http://owebu.bloger.cz/stitek_?s=SQLite
SQL databáze • MySQL • Microsoft SQL Server • SQLite • PostgreSQL • Oracle • Firebird • … a další.
SQL příkazy - přehled Do jazyka SQL patří: • DML – Data Manipulation Language příkazy pro manipulaci se záznamy: SELECT (výběr záznamů), INSERT (vytváření záznamů), UPDATE (úprava záznamů), DELETE (mazání záznamů) • DDL – Data Definition Language definice db objektů – tabulek a dalších objektů: CREATE, ALTER, DROP… • Práce s transakcemi: COMMIT, ROLLBACK… • Příkazy pro nastavování přístupových práv: GRANT, REVOKE.
SQLite - poznámky SQLite nerozlišuje datové typy. Pouze INTEGER PRIMARY KEY musí být vždy jednoznačné celé číslo. (Primární klíč je pole jednoznačně identifikující záznamy v tabulce. Nesmí zůstat prázdné). Př.: rodné číslo u osob, katalogové číslo u výrobků, identifikační číslo v seznamu podniků apod.
Každá tabulka má mít definovaný právě jeden primární klíč. Primární klíč má základní vlastnosti: • jedinečnost v rámci tabulky • ne-NULL-ovou hodnotu • neměl by se nikdy měnit. . Pozn.: Pokud neexistuje žádný přirozený primární klíč, používá se obvykle jako primární klíč číslo, které záznamu přidělí automaticky sama databáze - pořadové číslo záznamu (ID).
SqLite 1 Úkol: Zkopírujte si na disk H: adresář P:\Zadani\Polach_P\sqlite Obsahuje soubory: • skola.db – cvičná databáze • sqlite3.exe – databázový program • start_skola.db.bat – spouštění cvičení • settings – počáteční nastavení
SqLite 2 Tečkové příkazy × Příkazy SQL Tečkové příkazy (dot commands) ovládání programu sqlite Začínají tečkou, nepíše se za nimi středník!
Pozn.: Příkazový řádek:
- opakování příkazu (kurzorové šipky nahoru a dolů), - kopírování do a ze schránky (přes systémový bod)
.help - nápověda .quit – ukončení programu .databases – otevřená (připojená) databáze .tables – datové tabulky v připojené databázi .schema tablename– struktura tabulky – datové položky a typy (SQL příkaz pro vytvoření tabulky) .read — načte prikaz(y) ze souboru .read settings .output FILENAME – pošle výsledek (SQL) příkazu do souboru .output stdout – pošle výsledek na monitor _______________________________________ .header on – zapne zobrazení názvů sloupců ve výpisu výsledku .mode columns – výpis výsledku do sloupců .echo OFF (.echo ON) – vypne (zapne) vypisování příkazů
SQL příkazy SQL příkazy končí středníkem a nedělá se před nimi tečka. SELECT * FROM zaci; SELECT jmeno,prijmeni,bydliste, sourozencu FROM zaci; SELECT jmeno,prijmeni,bydliste, sourozencu FROM zaci WHERE bydliste="Hodonín"; SELECT jmeno,prijmeni,bydliste FROM zaci WHERE bydliste=‘Hodonín’ AND pohlavi=‘ž’; SELECT jmeno,prijmeni,bydliste FROM zaci WHERE prijmeni >= “R“;
=, <>, >, <, >=, <=
SELECT jmeno,prijmeni,bydliste FROM zaci ORDER BY bydliste DESC; SELECT jmeno,prijmeni,bydliste FROM zaci ORDER BY bydliste DESC, prijmeni ASC; (Pozn.: sqlite nezná české třídění!) (Pozn.: vzestupně ASC, sestupně DESC)
AND, OR, NOT, závorky
SELECT – Další možnosti (změny výpisu, funkce, výpočty, …)
SELECT ‘Nazdar!’; SELECT 1.5+1.6; SELECT ((1.5+10)*2)-1; SELECT 11/2;
lze vypsat i text; desetinná tečka! závorky celočíselné dělení;
SELECT 11.0/2; dělení SELECT 11%2; % zbytek po celočíselném dělení SELECT round(27.56789,2); zaokrouhlení změna záhlaví výpisu, výpočet ve výpisu SELECT prijmeni, sourozencu, sourozencu*100 AS ‘Příspěvek‘ FROM zaci WHERE bydliste= ‘Hodonín’ ;
SELECT date(); SELECT time(); SELECT datetime();
aktuální datum (date, time)
SELECT - agregační funkce SELECT COUNT(prijmeni) FROM zaci; SELECT COUNT(prijmeni) AS ‘Počet žáků’ FROM zaci;
SELECT SUM(sourozencu) FROM zaci WHERE bydliste=“Hodonín“; SELECT ‘Prospěch: ’, MIN(prospech), ’ až ’, MAX(prospech) FROM zaci;
SELECT AVG(prospech) AS ‘Průměrný prospěch dívek’ FROM zaci WHERE pohlavi=‘ž’;
VIEW (pohledy, dotazy) Často opakované složité příkazy můžeme pojmenovat a uložit jako view. Zastávají roli datové tabulky. Data ve view samozřejmě nelze aktualizovat.
Vytvoření view:
CREATE VIEW v_cvicny as SELECT prijmeni, jmeno, prospech FROM zaci… nějaký příkaz; Volání view: SELECT * FROM v_cvicny; SELECT prijmeni, prospech FROM v_cvicny ORDER BY prospech; SELECT COUNT(prijmeni) FROM v_cvicny; Podobně jako u tabulek funguje: .tables (views se vypíšou společně s dat.tabulkami. Je dobré je od nich odlišit jménem. .schema v_cvicny
Cvičení Pomocí výstupu do souboru (příkaz .output) vytvořte soubor vysledek.txt. V něm na začátku bude na samostatném řádku Vaše příjmení a jméno a aktuální datum.
Na dalších řádcích budou vyhledány dívky z tabulky zaci, které nejsou z Hodonína a mají lepší prospěch než 2. Budou vypsány sloupce jmeno, prijmeni, bydliste, prospech, sourozencu a sloupec s názvem soucin, ve kterém bude součin (krát) prospěchu dívky a počtu jejích sourozenců. Výpis bude seřazen podle počtu sourozenců sestupně, v rámci stejného počtu podle prospěchu vzestupně. Za výpisem budou čtyři řádky na nichž bude:
Počet nalezených dívek: skutečný údaj Průměrný prospěch nalezených dívek: skutečný údaj Celkový počet sourozenců: skutečný údaj Součet hodnot sloupce soucin:skutečný údaj Všechny příkazy pro vytvoření souboru vysledek.txt postupně vytvořte, odlaďte a postupně vložte do souboru prikazy.txt, který pak načtete a najednou provedete příkazem .read.
SQL příkazy – DML – • INSERT • UPDATE • DELETE
SELECT,
INSERT, UPDATE, DELETE
přidá záznam do tabulky úprava už existujících záznamů vymazání existujících záznamů
Příklady: INSERT INTO zaci(jmeno, prijmeni, bydliste) VALUES (‘Pavel‘, ‘Polách‘, ‘Hodonín‘) ; UPDATE zaci SET jmeno=‘Petr’, sourozencu=‘1’,prospech=‘1,01’ WHERE prijmeni=‘Polách’; DELETE FROM zaci WHERE prijmeni=‘Polách’;
Příkazy DDL– Návrh a administrace databáze (data definition language)
CREATE DATABASE cv_db; Vytvoření tabulky CREATE TABLE kamaradi ( jmeno char(30), prijmeni char(40) NOT NULL, roknar integer );
vytvoření databáze (sqlite nepodporuje lze vytvořit při spuštění db programu – viz bat)
ALTER TABLE … ; změna tabulky po jejím vytvoření (sqlite nepodporuje) DROP TABLE kamaradi; odstranění tabulky DROP DATABASE cv_db; odstranění databáze
