~Nožka

Troška MySQL

Obsah:

1. Úvodem
2. Reference
3. Instalace
4. Konzola MySQL
5. Testovací databáze
6. Příklad několika SQL dotazů
7. Python a MySQL

Úvodem

SQL je standardizovaný dotazovací jazyk používaný pro práci s daty v relačních databázích. V relační databázi jsou data data uložena v tabulkách, které jsou navzájem v nějakém vztahu -- relaci.

Přístup k databázi je realizován softwarem označovaným jako Systém řízení báze dat. Jednotlivé systémy řízení báze dat se liší. V současné době se ve výuce používáme PostgreSQL. Tato stránka o MySQL je historickým pozůstatkem.

Reference

• MySQL
• MySQL datové typy
• MySQL na Wikipédii
• MySQL na Linuxsoftu
• phpMyAdmin
• http://www.phpmyadmin.net/
• Adminer
• http://www.adminer.org/

Instalace

$ aptitude install  mysql-server mysql-client phpmyadmin

Bezprostředně po instalaci je vhodné spustit skript, který se snaží omezit bezpečnostní rizika:

$ mysql_secure_installation

Konzola MySQL

K manipulaci s MySQL a pro spouštění SQL příkazu lze použít MySQL-konzola. Tu spustím příkazem:

$ mysql --user=franta --password=tojetajny mojedabaze 

Abychom nemuseli znovu a znovu zadávat jméno a heslo můžeme si ho "uložit" do souboru ~/.my.cnf.

# ~/.my.cnf
##############################

[client]
user=franta
password=tojetajny

`--> stáhnout

Souboru je nutné nastavit přístupová práva:

$ chmod 600 ~/.my.cnf

Jedna malé vylepšení: Colour MySQL console

Testovací databáze

Vytvořil jsem pro vás testovací dadabázi. Předpokládám, že máte vytvořenou nějakou databázi. Já jsem ji nazval lididb. Pro rychlý a pohodlný import můžete vložit do příkazové řádky:

$ wget -O - http://hroch.spseol.cz/~nozka/site/sql/lididb.sql | mysql lididb

Příklad několika SQL dotazů

Výběr

-- tohle používat jen vyjímečně
SELECT * FROM lidi;

SELECT jmeno, data FROM lidi;

SELECT jmeno, rok_narozeni
FROM lidi 
WHERE rok_narozeni <1980 
ORDER BY rok_narozeni DESC  /* DESC říká, že se bude řadit sestupně*/;
/* ASC by bylo vzestupně */

-- V dotazu lze rok narození přepočítat na věk
SELECT jmeno, Year(CURDATE())-rok_narozeni AS 'věk'
FROM lidi  
WHERE rok_narozeni >1980  ORDER BY rok_narozeni ASC;

`--> stáhnout

Spojování více tabulek

-- Vypíše všechny kuchaře
SELECT lidi.jmeno, nazev_zamestnani
FROM lidi, zamestnani 
WHERE lidi.id_zamestnani = zamestnani.id_zamestnani 
         AND 
      nazev_zamestnani = 'kuchař';

-- Seřadí lidi podle jejich zaměstnání
SELECT lidi.jmeno, nazev_zamestnani AS zamestnani
FROM lidi, zamestnani 
WHERE lidi.id_zamestnani = zamestnani.id_zamestnani 
ORDER BY zamestnani;      

/* pomocí AS lze přejmenovat sloupec */

-- Všichni kuchaři narozní po a v roce 1990
SELECT lidi.jmeno, lidi.rok_narozeni AS 'rok narození'
FROM lidi JOIN zamestnani ON lidi.id_zamestnani = zamestnani.id_zamestnani
WHERE nazev_zamestnani = 'kuchař' 
         AND 
      lidi.rok_narozeni >=1990;


-- můžeme použít zkratky
SELECT L.jmeno, L.rok_narozeni AS 'rok narození'
FROM lidi L, zamestnani Z  
WHERE L.id_zamestnani = Z.id_zamestnani
        AND 
      Z.nazev_zamestnani = 'kuchař'     
        AND 
      L.rok_narozeni <=1990;

/* ************************ */

-- vypíše zaměstnání a lidi, kteří je vykonávají
SELECT nazev_zamestnani AS zamestnani, jmeno 
FROM lidi JOIN zamestnani ON lidi.id_zamestnani = zamestnani.id_zamestnani 
ORDER BY zamestnani;

-- ... vypíše i lidi bez zaměstnání
SELECT nazev_zamestnani AS zamestnani, jmeno 
FROM lidi LEFT JOIN zamestnani ON  lidi.id_zamestnani = zamestnani.id_zamestnani 
ORDER BY zamestnani;


-- ... vypíše i zaměstnání, která nemají přiřazeno žádného člověka
SELECT nazev_zamestnani AS zamestnani, jmeno 
FROM lidi RIGHT JOIN zamestnani ON  lidi.id_zamestnani = zamestnani.id_zamestnani 
ORDER BY zamestnani;


/* ************************ */

-- osoby a jejich koníčky
SELECT lidi.jmeno, konicky.nazev_konicku 
FROM konicky_lidi 
    JOIN konicky ON konicky_lidi.id_konicku = konicky.id_konicku 
    JOIN lidi ON konicky_lidi.id_osoby = lidi.id_osoby

-- všichni, co milují omalovánky
SELECT lidi.jmeno, konicky.nazev_konicku 
FROM konicky_lidi 
    JOIN konicky ON konicky_lidi.id_konicku = konicky.id_konicku 
    JOIN lidi ON konicky_lidi.id_osoby = lidi.id_osoby
WHERE nazev_konicku='omalovánky'

`--> stáhnout

Vkládání

INSERT INTO lidi 
SET jmeno='Ivan', rok_narozeni=1972, id_zamestnani=15 ;

INSERT INTO lidi (jmeno ,rok_narozeni, id_zamestnani ) 
          VALUES ('Franta', '1980', 3);

INSERT INTO zamestnani VALUES (0,'fotograf');
INSERT INTO lidi VALUES ('0' ,'Šimon', '1976','4', 'dobrej borec' ) ; 

`--> stáhnout

Změna

-- můžu upravit jen jeden řádek
UPDATE zamestnani SET nazev_zamestnani = 'řidič'
WHERE nazev_zamestnani = 'řidIč';

-- nebo víc řádků najednou
UPDATE lidi SET data = 'nic o něm nevím' 
WHERE data IS NULL;

`--> stáhnout

Smazání

DELETE FROM lidi WHERE data IS NULL;

DELETE FROM zamestnani WHERE nazev_zamestnani='učitel'

`--> stáhnout

Python a MySQL

• python mysqldb
• http://www.tutorialspoint.com/python/python_database_access.htm

Python řeší přístup k MySQL pomocí modulu MySQLdb.

# aptitude install pyton-mysqldb

import MySQLdb

`--> stáhnout

K databázi se nejprve připojíme:

db = MySQLdb.connect(host='localhost',
                     user='franta',
                     passwd='tojetajny',
                     db='moje_databaze')

`--> stáhnout

Vytvoříme objekt kurzor:

cursor = db.cursor()

`--> stáhnout

Spustíme dotaz: Pokud SQL příkaz mění data, je nutné použít k metodě execute() ještě metodu commit().

cursor.execute('INSERT INTO obce VALUES(0,'Benátky');')
db.commit()  # nebo nastavím db.autocommit(True)

`--> stáhnout

Návratová hodnota metody .execute() je počet řádků, které SQL dotaz vrátil.

cursor.execute('SELECT * FROM jmena;')

`--> stáhnout

Data si můžeme vybrat postupně pomocí metody .fetchone():

radek = cursor.fetchone()

`--> stáhnout

Každé další zavolání metody .fetchone() vydá další řádek výsledku SQL dotazu. Po vydání posledního řádku vrací metoda .fetchone() už jen None. Seznam vše řádků vrátí metoda .fetchall() a zadaný počet řádků metoda .fetchmany(pocet).

Objekt cursor je iteratovatelný proto lze použí něco jako:

for radek in cursor:
    print radek

`--> stáhnout

Nakonec se od databáze odpojíme:

db.close()

`--> stáhnout

Příklad

#!/usr/bin/python
# -*- coding: utf8 -*-
import MySQLdb

db = MySQLdb.connect(host='localhost',
                     user='franta',
                     passwd='tojetajny',
                     db='obybatele')

query="""
SELECT jmeno,vek
FROM lidi
WHERE vek > 50 
"""
cursor = db.cursor()
cursor.execute(query)

data = cursor.fetchall()

db.close()

for radek in data:
    print "jméno:",radek[0],"věk:",radek[1]

`--> stáhnout

| navigace |