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

keine Unterstützung:

Transaktionen, Commit/Rollback, Views, Foreign Keys, Sub-SELECTs

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 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)

(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)

MySQL PHP 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 Anfrage an einen MySQL Server Id
Res = mysql_db_query(database, query [, Id]): Stellen einer 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;
}
?>

Beispiel

Stichwortindex

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

Web-Datenbanken: MySQL