Web-Datenbanken: MySQL

Einleitung

• Datenbanksysteme DBMS

• Relationale Datenbanksysteme RDBMS

• Structured Query Language (SQL), in 1970

• Datenbanken und das Web

• LAMP = Linux + Apache + MySQL + PHP

• WAMP

• LAMPS = LAMP + SSL

• Alternativen: mSQL, Solid, Postgres (PostgreSQL), Oracle, etc.

MySQL Historie

• vor 1994 Postgres (mit PostQUEL) in Nachfolge zu Ingres
  Entwickelt als Universitätsprojekt von Michael Stonebreaker

• mSQL als SQL-Interface zu Postgres, jetzt eigene DB-Engine,
  Entwickler David Hughes

• Mai 1995 MySQL mit mSQL Interface und eigener DB-Engine (UNIREG B+ ISAM, seit 1979),
  Entwickler Michael Widenius

MySQL Überblick

Features

• hohe Performance, Multithreaded

• Plattform unabhängig

• Entry Level SQL92 Unterstützung

  insbesondere:

  SELECT, Joins, WHERE-Klausel, GROUP BY

  Erweiterungen:

  AUTO_INCREMENT, DROP column, REPLACE

  in Versionen ab 3.23.34:

  Transaktionen, Commit/Rollback bei Berkeley DB / InnoDB Tabellen

  ab Version 4.0:

  volle Unterstützung für InnoDB Tabellen, Foreign Keys

  ab Version 4.1:

  Sub-SELECTs/Subqueries, Unicode

  in Version 5.0 geplant:

  Views, Stored Procedures, Trigger

  Unterschiede:

  GRANT, CREATE/DROP INDEX

   
  

• viele APIs, ODBC, JDBC

• ISO8859_1 (Latin1) Unterstützung

• nur der Weiterverkauf kostet Lizenzgebühren

• Zugang zur Datenbank nur über TCP/IP

• eigenes Sicherheitssystem

Installation

• Binär-Versionen oder Source-Code

• jede Datenbank befindet sich in einem eigenen Unterverzeichnis

• jede (ISAM) DB Tabelle befindet sich in 3 Dateien (*.frm, *.ISM, *.ISD)

• Sicherheitssystem in DB ´mysql´

• Perl Interface

• PHP und Apache Zugang

• MySQL gibt es z.B. bei SuSE (fast) fertig installiert

wierum{admin}[/usr/local/mysql]516: bin/mysqladmin ver
bin/mysqladmin  Ver 6.9 Distrib 3.21.33b, for sun-solaris2.5.1 on sparc
TCX Datakonsult AB, by Monty

Server version          3.21.33b-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 11 days 3 hours 13 min 45 sec

Running threads: 2  Questions: 56093  Opened_tables: 19  
Flush tables: 1  Open tables: 13

Sicherheitssystem

• Script: mysql_install_db

• ´user´-Tabelle regelt Zugriff auf MySQL-Server
  Aufbau: (host, user, password, privs, ...)

  mysql> select host, user, password from user;
  +-----------+--------+------------------+
  | host      | user   | password         |
  +-----------+--------+------------------+
  | localhost | admin  | 5f6b52670x3f4407 |
  | warum     | admin  | 5a6b5y67023f4c07 |
  | localhost | gast   |                  |
  | %         | kredel | 5f6b526c023f4407 |
  +-----------+--------+------------------+
  4 rows in set (0.00 sec)
  

  Benutzer 'gast' darf sich von 'localhost' ohne Passwort verbinden. Die Zugriffsprivilegien sind alle negativ. Benutzer 'kredel' darf sich von überall ('%') mit Passwort verbinden.

• ´db´-Tabelle regelt Zugriff auf Datenbanken
  Aufbau: (host, db, user, privs, ...)

  mysql> select host, user, db from db;
  +-----------+-------+---------+
  | host      | user  | db      |
  +-----------+-------+---------+
  | %         |       | test    |
  | %         |       | test\_% |
  | localhost | gast  | webtech |
  +-----------+-------+---------+
  3 rows in set (0.01 sec)
  

  Benutzer 'gast' darf von 'localhost' auf die Datenbank 'webtech' zugreifen. Die Zugriffsprivilegien sind bis auf 'Select_priv' alle negativ.

• ´host´-Tabelle erlaubt Zugriff von Rechnern oder Netzen
  Aufbau: (host, db, privs, ...)

  mysql> select * from host;
  Empty set (0.01 sec)
  

  Es sind keine Regeln definiert.

• Das Tool mysqlaccess prüft die definierten Rechte.

  wierum{admin}[/usr/local/mysql]515: bin/mysqlaccess localhost gast webtech
  mysqlaccess Version 2.03, 27 Feb 1997
  By RUG-AIV, by Yves Carlier (Yves.Carlier@rug.ac.be)
  This software comes with ABSOLUTELY NO WARRANTY.
  +++USING FULL WHERE CLAUSE+++
  +++USING FULL WHERE CLAUSE+++
  +++USING FULL WHERE CLAUSE+++
  
  Access-rights
  for USER 'gast', from HOST 'localhost', to DB 'webtech'
          +-----------------+---+ +-----------------+---+
          | Select_priv     | Y | | Shutdown_priv   | N |
          | Insert_priv     | N | | Process_priv    | N |
          | Update_priv     | N | | File_priv       | N |
          | Delete_priv     | N | | Grant_priv      | N |
          | Create_priv     | N | | References_priv | N |
          | Drop_priv       | N | | Index_priv      | N |
          | Reload_priv     | N | | Alter_priv      | N |
          +-----------------+---+ +-----------------+---+
  BEWARE:  Everybody can access your DB as user `gast' from host `localhost'
        :  WITHOUT supplying a password.
        :  Be very careful about it!!
  
  The following rules are used:
   db    : 'localhost','webtech','gast','Y','N','N','N','N','N','N','N','N','N'
   host  : 'Not processed: host-field is not empty in db-table.'
   user  : 'localhost','gast','','N','N','N','N','N','N','N','N','N','N','N','N','N','N'
  
  BUGs can be reported by email to Yves.Carlier@rug.ac.be
  

MySQL Tools

mysqld, safe_mysqld

MySQL server daemon und Start-Script

mysqlshow

Anzeige diverser Informationen

mysql

SQL shell (GNU readline)

mysqladmin

Administrations Tool, z.B. Start, Stop, Refresh, Create/Drop DB

mysqlaccess

Tool zum Testen und Anzeigen der Sicherheitskonfiguration

mysql_install_db

Initialisierung des Sicherheitssystems

isamchk

Reparatur-Tool für Datenbanken

mysqlshow

mysqlshow Databases

wierum{admin}[/usr/local/mysql]502: bin/mysqlshow 
+-----------+
| Databases |
+-----------+
| infoad    |
| mysql     |
| news      |
| rum       |
| stichwort |
| test      |
| ub        |
| uni       |
+-----------+

mysqlshow Database: stichwort

wierum{admin}[/usr/local/mysql]503: bin/mysqlshow stichwort
Database: stichwort
+--------+
| Tables |
+--------+
| links  |
+--------+

mysqlshow Database: stichwort Table: links

wierum{admin}[/usr/local/mysql]504: bin/mysqlshow stichwort links
Database: stichwort  Table: links  Rows: 145
+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| link  | char(255) | YES  |     |         |                |
| name  | char(255) | YES  |     |         |                |
| hit   | int(11)   | YES  |     |         |                |
| ID    | int(11)   |      | PRI | 0       | auto_increment |
| ename | char(255) | YES  |     |         |                |
| elink | char(255) | YES  |     |         |                |
+-------+-----------+------+-----+---------+----------------+

mysql

show tables

mysql> show tables from stichwort;
+---------------------+
| Tables in stichwort |
+---------------------+
| links               |
+---------------------+
1 row in set (0.00 sec)

show columns

mysql> show columns from links from stichwort;
+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| link  | char(255) | YES  |     | NULL    |                |
| name  | char(255) | YES  |     | NULL    |                |
| hit   | int(11)   | YES  |     | NULL    |                |
| ID    | int(11)   |      | PRI | 0       | auto_increment |
| ename | char(255) | YES  |     | NULL    |                |
| elink | char(255) | YES  |     | NULL    |                |
+-------+-----------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

show databases

mysql> show databases;
+-----------+
| Database  |
+-----------+
| infoad    |
| mysql     |
| news      |
| rum       |
| stichwort |
| test      |
| ub        |
| uni       |
+-----------+
8 rows in set (0.01 sec)

phpMyAdmin

Mit phpMyAdmin existiert ein funktionales Web-Interface zur Verwaltung der MySQL Datenbanken.

Beispiele

(My)SQL Sprache

MySQL unterstützt ANSI SQL92 mit den schon genannten Ausnahmen.

SQL Grundkonstrukte

• SELECT zu Auslesen von Daten aus der DB.

  mysql> select count(*) from links;
  +----------+
  | count(*) |
  +----------+
  |      145 |
  +----------+
  1 row in set (0.01 sec)
  

  mysql> select sum(hit) from links;
  +----------+
  | sum(hit) |
  +----------+
  |     9672 |
  +----------+
  1 row in set (0.02 sec)
  

  mysql> select name, hit from links where id="88";
  +-------------------------+------+
  | name                    | hit  |
  +-------------------------+------+
  | Philosophische Fakultät |   41 |
  +-------------------------+------+
  1 row in set (0.00 sec)
  

  mysql> select id, name, hit from links order by hit desc limit 10;
  +-----+------------------------+------+
  | id  | name                   | hit  |
  +-----+------------------------+------+
  |  21 | Bibliothek             |  275 |
  | 130 | Universitätsbibliothek |  275 |
  |  19 | Beurlaubung            |  231 |
  |  20 | Bewerbungsverfahren    |  231 |
  |  38 | Exmatrikulation        |  231 |
  |  58 | Immatrikulation        |  231 |
  |  93 | Prüfungsverwaltung     |  231 |
  | 100 | Rückmeldung            |  231 |
  | 146 | Zulassungsverfahren    |  231 |
  |  35 | Email-Verzeichnis      |  215 |
  +-----+------------------------+------+
  10 rows in set (0.01 sec)
  

• INSERT zum Einfügen neuer Zeilen in eine Tabelle.

  mysql> insert into links (name, hit) values ("Fakultät VWL", "1");
  Query OK, 1 row affected (0.03 sec)
  

• UPDATE zum Ändern von Werten in den Zeilen.

  mysql> update links set name="Fakultaet VWL" where id="149";
  Query OK, 1 row affected (0.05 sec)
  

• DELETE zum Löschen von Zeilen.

  mysql> delete from links where id="149";
  Query OK, 1 row affected (0.00 sec)
  

SQL Administrationskonstrukte

• (My)SQL Datentypen,
  z.B. INT(d), CHAR(m), FLOAT, DATE, TIME, VARCHAR(m), TEXT, BLOB.

• CREATE TABLE zum Anlegen von neuen Tabellen.

  mysql> create table xlink ( 
                id int(11) not null, 
                name char(255), 
                link char(255), 
                primary key (id) ); 
  Query OK, 0 rows affected (0.15 sec)
  

  mysql> show columns from xlink from stichwort;
  +-------+-----------+------+-----+---------+-------+
  | Field | Type      | Null | Key | Default | Extra |
  +-------+-----------+------+-----+---------+-------+
  | id    | int(11)   |      | PRI | 0       |       |
  | name  | char(255) | YES  |     | NULL    |       |
  | link  | char(255) | YES  |     | NULL    |       |
  +-------+-----------+------+-----+---------+-------+
  3 rows in set (0.04 sec)
  

• DROP TABLE zum Löschen von existierenden Tabellen.

  mysql> drop table xlink;
  Query OK, 0 rows affected (0.10 sec)
  

---

PHP Schnittstelle (API)

Das MySQL API ist stark an das mSQL API angelehnt. MySQL unterstützt auch ODBC (Open Database Conectivity) und JDBC (Java Database Conectivity).

Id = mysql_pconnect(hostname, username [, password])

Aufbau einer persistenten Verbindung zu einem MySQL Server

Id = mysql_connect(hostname, username [, password])

Aufbau einer Verbindung zu einem MySQL Server

mysql_close(Id)

Schliessen der Verbindung zum MySQL Server Id

Fehler = mysql_error(Id)

Beschreibung des letzten Fehlers beim Zugriff zum MySQL Server Id

DBid = mysql_select_db(database [, Id])

Auswahl einer Datenbank auf einem MySQL Server Id
 

Res = mysql_query(query [, Id])

Stellen einer SQL Anfrage an einen MySQL Server Id

Res = mysql_db_query(database, query [, Id])

Stellen einer SQL Anfrage an eine Datenbank auf einem MySQL Server Id

num = mysql_num_rows(Res)

Anzahl der Zeilen der Anfrage Res

col = mysql_num_fields(Res)

Anzahl der Spalten (Felder) der Anfrage Res

Data = mysql_result(Res, zeile [, spalte])

Entnahme einer Zeile (oder von Teilen einer Zeile) aus der Anfrage Res

Array = mysql_fetch_row(Res)

Entnahme der nächsten Zeile aus der Anfrage Res

num = mysql_data_seek(Res, zeile)

Positioniert einen Zeiger auf eine Zeile der Anfrage Res

Beispiel:

<?php
Function dbQuery ($statement) {
   global $dbconfig;
   mysql_pconnect($dbconfig["sqlserver"],
                  $dbconfig["sqlusername"],
                  $dbconfig["sqlpassword"]);
   mysql_select_db($dbconfig["defaultdb"]);
   $result=@mysql_query($statement);
   if (mysql_error()) { PrintError(mysql_error()); }
   return $result;
}
?>

PHP Beispiele

PHP Stichwortindex

Die Datenbank "webtech" besteht aus einer Tabelle "zaehler" mit zwei Spalten "file" und "count".

CREATE TABLE `zaehler` (
  `file` char(200) default NULL,
  `count` int(11) default NULL
) TYPE=ISAM PACK_KEYS=1;

MySQL PHP Counter

---

Java Schnittstelle JDBC

Das Java API Java Database Conectivity (JDBC) ist nicht an das mSQL API angelehnt, sondern erweitert das ODBC (Open Database Conectivity) API.

JDBC Versionen ab Java 1.4:

1.0 und 1.1:

in etwa die Funktionalität von ODBC, Package java.sql

2.1 Core in SE:

Navigation in Resultsets, Batch-Updates, Package java.sql

2.0 Optional in EE:

Connection Pooling, Distributed Transactions, neue Datenquellen, Package javax.sql

3.0 ab JDK 1.4:

vereinigt 2.1 Core und 2.0 Optional, mehrfache Resultsets, Pooled Statements, XML (Schema) Support, besserer BLOB und CLOB Support, Packages java.sql, javax.sql, javax.sql.RowSet

Die wichtigsten Klassen und Methoden aus java.sql sind:

Class.forName( mysqlDriver )

laden des MySQL Treibers: com.mysql.jdbc.Driver

DriverManager.getConnection(db,user,pword)

erzeugen eines Connection Objekts zur Datenbank db = "jdbc:mysql://host/db"

dbCon.close()

schliessen der Verbindung zu dbCon-Connection
 

stmt = dbCon.createStatement()

erzeugen eines SQL-Statements zur dbCon-Connection

rs = stmt.executeQuery( query )

durchführen der SQL-Abfrage query und erzeugen des ResultSet rs

pstmt = dbCon.prepareStatement( query )

vorbereiten eines SQL-Statements zur dbCon-Connection, Parameterpositionen werden durch ? gekennzeichnet

pstmt.setString(i, param );

pstmt.setInt(i, param );

einsetzen eines Parameters an die i. Stelle in dem SQL-Statement

rs = pstmt.executeQuery()

durchführen des vorbereiteten Statements und erzeugen des ResultSet rs

r = pstmt.executeUpdate()

durchführen eines vorbereiteten Statements und zählen der veränderten Tabellen Zeilen in r

stmt.close()

schliessen / freigeben des Statements stmt
 

rs.next()

prüfen, ob das ResultSet weitere Zeilen enthält und positionieren des "Cursors" auf die nächste Zeile

rs.getString(i);

rs.getInt('name');

entnehmen des Werts der i. Spalte, bzw. der Spalte 'name' aus der aktuellen Zeile

rs.close()

schliessen / freigeben des ResultSets rs
 

dbCon.setAutoCommit( false )

einschalten der Transaktionsverwaltung für die dbCon-Connection

dbCon.commit()

positives Beenden einer Transaktion, bestätigen aller Änderungen

dbCon.rollback()

negatives Beenden einer Transaktion, verwerfen aller Änderungen
 

SQLException

wird von fast allen Methoden ausgelöst, wenn etwas schief geht

JDBC Beispiele

Die Datenbank "inet" besteht aus einer Tabelle "counter" mit zwei Spalten "url" und "count".

CREATE TABLE `counter` (
  `url` varchar(255) NOT NULL default '',
  `count` int(11) NOT NULL default '0',
  KEY `url` (`url`)
) TYPE=MyISAM;

MySQL JDBC Counter

MySQL JDBC Counter Add

Schlussbemerkungen

• Welche Datenbank ist geeignet?

  einfache Anforderungen:

  dbm, mSQL, etc.

  mittlere Anforderungen:

  MySQL, Solid, PostgreSQL, etc.

  maximale Anforderungen:

  Oracle, DB2, Informix, Sybase, etc.

   
  

• Empfehlungen:
  nur SQL verwenden
  ggf. ODBC Interface verwenden