Glossar - telltec

Glossar
MySQL
…ist eine Datenbanksoftware/Art, die sehr weit verbreitet ist. In einer Datenbank
kann man Tabellen anlegen, in die die einzelnen Informationen gespeichert
werden. Ein Beispiel:
+-------------------------------+
| ID
Name Eintrag |
| 01
Andi
Hallo !! |
| 02
Rafi
Hallo !! |
+-------------------------------+
So könnte eine kleine Tabelle für ein Gästebuch aussehen. Aus einer Tabelle wird normal mit PHP
gelesen. MySQL verwendet man in aller Regel zusammen mit einem Webserver (z.B. Apache).
DDL – Data Definition Language
Zur Erzeugung eines Datenmodells.
DML – Data Manipulation Language
Zur Modifikation der verwaltenden Daten
DRL – Data Retrieval Language
Abfragen an Datenbanken
DCL – Data Control Language
Administration. Verwalten der Rechte
User Anlegen
Mysql> use mysql
Insert into user set user=“Dominic“, password=password(„geheim“);
Select user from user;
MySQL Dienst starten und beende
Normal CMD öffnen und dann „net start-„ oder „net Stopp mysql“ eingeben und mit Enter bestätigen.
Auf Externe MySQL Datenbank zugreifen
????
Datenbank mit Tabelle erstellen und abfüllen
' neue Datenbank erstellen
create database New1;
' Datenbank benutzen
use New1;
'Tabelle erstellen
create table tb2;
'Tabelle abfüllen
(id integer not null auto_increment,
Vorname varchar (50),
Name varchar (50),
Adresse varchar (50),
primary key (id));
'auf Tabelle zugreifen
desc tb1;
'Tabelle mit Daten abfüllen
insert into tb1 (Vorname, Name, Adresse)
values ("Dominic", "Jaeggi", "Himmelrichstrasse 2");
Dominic Jäggi
1 von 6
14.05.2016
Glossar zur MySQL
insert into tb1 (Vorname, Name, Adresse)
values ("André", "Frischknecht", "Teufelstrasse 23c");
insert into tb1 (Vorname, Name, Adresse)
values ("Daniel", "Guggisberg", "Äscherwisplatz 12");
insert into tb1 (Vorname, Name, Adresse)
values ("Tanja", "Schorno", "Äschetürlistrasse 82");
'Tabelle anschauen
select * from tb1;
Operationen für SQL Gleichheitstest
=
<>
<
>
<=
>=
IS NULL
IS NOT NULL
BETWEEN
IN
Create Database „“
Gleichheitstest
Test auf Ungleichheit
Test auf kleiner
Test auf grösser
kleiner oder gleich
grösser oder gleich
Testet ob eine Spalte null ist
Testet ob eine Spalte nicht NULL enthält
Testet ob ein Wert in vorgegebenen Grenzen liegt
Testet ob ein Wert innerhalb einer vorgeg. Menge ist
erstellt eine neue Datenbank
Funktionen für MySQL
Syntax
Beschreibung
Sample
count()
Anzahl Werte einer Ergebnismenge
SELECT count(lname) FROM employee;
count(distinct)
Anzahl unterschiedlicher werte
SELECT count(DISTINCT lname) FROM employee;
min()
Minimum
select min(salary) FROM employee;
max()
Maximum
select max(salary) FROM employee;
avg()
Durchschnitt
select avg(salary) FROM employee;
sum()
Summe
select sum(salary) FROM employee;
abs(zahl)
absoluten Werte einer Zahl
-
ceiling(zahl)
rundet die Zahl auf die nächst höhere
-
floor(zahl)
rundet die Zahl auf die nächst kleinere
-
round(zahl)
rundet normal nach math. Regeln
-
round(zahl,stellen)
rundet normal nach math. Regeln auf
Anzahl Stellen
-
log(zahl)
natürlichen logarithmus ermitteln
-
mod(zahl1,zahl2)
Liefert den Rest der Ganzzahldivison
-
pi()
Liefer pi
-
rand()
Zufallszahl zwischen Null und 1
-
sign(zahl)
Ermiitelt Vorzeichen, liefert -1,0,1
-
sin(zahl)
cos(zahl)
tan(zahl)
Winkelfunktion
-
sqrt(zahl)
Quadratwurzel
-
LOCATE
Ein Zeichen in einem String finden
LOCATE(',',ADDRESS)
SUBSTRING
Nur einen Teil eines Strings
zurückgeben
CONCAT
Dominic Jäggi
SUBSTRING(ADDRESS,2) --> Ab 2. zeichen
SUBSTRING(ADDRESS,-2) --> letzte 2 zeichen
SUBSTRING(ADDRESS,2,7) --> vom 2. bis 7. zeichen
CONCAT('Hello','world','!')
String zusammensetzen
2 von 6
14.05.2016
Glossar zur MySQL
Joins für MySQL
Syntax
Beschreibung
Sample
SELECT datenfelder FROM tabelle1
INNER JOIN tabelle2 ON bedingung;
Inner-Join
SELECT fname,dname FROM employee e
INNER JOIN department d
ON d.dnumber = e.dno;
SELECT datenfelder FROM ((tabelle1
INNER JOIN tabelle2 ON bedingung)
INNER JOIN tabelle 3 ON bedingung) …
;
SELECT DISTINCT datenfelder FROM
tabelle1
INNER JOIN tabelle2 ON bedingung
WHERE bedingung;
SELECT datenfelder FROM tabelle1
LEFT OUTER JOIN tabelle2 ON
bedingung;
Inner-Join über
mehrere Tabellen
-
Natural Join
SELECT DISTINCT e.lname,w.hours FROM employee e
INNER JOIN works_on w ON e.ssn=w.essn
WHERE w.hours=10;
Left Outer Join
SELECT kat.name AS Kategorie, COUNT(p.pk) AS
Anzahl FROM kategorie kat
LEFT OUTER JOIN produkt p ON p.kategorie_fk = kat.pk
GROUP BY Kategorie;
SELECTSELECT datenfelder FROM
tabelle1
RIGHT OUTER JOIN tabelle2 ON
bedingung;
SELECT datenfelder FROM tabelle1
FULL JOIN tabelle2 ON bedingung;
Right Outer Join
SELECT w.essn,w.pno,d.essn,d.dependent_name
FROM dependent AS d
RIGHT OUTER JOIN works_on w ON w.essn=d.essn;
Full Outer Join
wird noch nicht unterstützt
SELECT DISTINCT datenfelder FROM
tabelle1
INNER JOIN tabelle2 ON bedingung
WHERE bedingung;
SELECT datenfelder FROM tabelle1
INNER JOIN tabelle2 ON bedingung
[WHERE bedingung]
[ORDER BY angabe];
Semi Join
SELECT DISTINCT e.fname,e.lname FROM employee e
INNER JOIN works_on w ON e.ssn = w.essn
WHERE w.hours >= 15;
Self Join
SELECT ma1.fname,ma1.lname,ma2.lname AS chef
FROM employee ma1
INNER JOIN employee ma2
ON ma1.superssn = ma2.ssn;
Allgemeines für MySQL
Syntax
Beschreibung
Sample
CREATE DATABASE [IF NOT EXISTS];
eine Datenbank erstellen
CREATE DATABASE beat;
USE;
Datenbank wechseln
USE beat;
SHOW DATABASES;
Alle Datenbanken anzeigen
-
DROP DATABASE;
eine Datenbank löschen
einen neuen Benutzer
erstellen
DROP DATABASE beat;
INSERT INTO user host='localhost',
user='beat',
password=PASSWORD('beat');
INSERT INTO user SET host="<Hostname>",
user="<Benutzername>",
password=PASSWORD("<Passwort>");
FLUSH PRIVILEGES;
GRANT <Zugriffsrechte> ON
<Datenbankobjekt>
TO <Benutzername>@<Hostname>
<Optionen>;
REVOKE <Zugriffsrecht> ON
<Datenbankobjekt>
FROM <Benutzername>@<Hostname>;
DELETE FROM user WHERE
user="<Benutzername>"
[AND host="<Hostname>"]
Dominic Jäggi
Berechtigungstabelle user
neu lesen
Zugriffsrechte für Benutzer
setzen
GRANT ALL PRIVILEGES ON *.* TO
beat@'%';
Zugriffsrechte entziehen
REVOKE ALL PRIVILEGES ON *.* FROM
beat@'%';
Benutzer löschen
Alle anonymen Benutzer
löschen
DELETE FROM user WHERE user='beat';
DELETE FROM user WHERE user=' ';
3 von 6
14.05.2016
Glossar zur MySQL
CREATE TABLE tabellenname
(datenfeld1 datentyp2 [DEFAUKT
standardwert1]
[NULL | NOT NULL] [AUTO_INCREMENT],
…,
PRIMARY KEY(datenfeldname));
Tabelle erstellen
CREATE TABLE adressen
(ID INTEGER NOT NULL
AUTO_INCREMENT,
NAME TEXT NULL,
VORNAME TEXT NULL,
PRIMARY KEY(ID));
CREATE TABLE
inkl. Gültigkeitsprüfung
CREATE TABLE t_ma_abt
(id INTEGER NOT NULL,
abtname VARCHAR(15) DEFAULT
"Produktion",
ma_nr INTEGER,
CONSTRAINT mpruef CHECK(ma_nr > 10));
CREATE TABLE
inkl. ENUM
CREATE TABLE farben (
farbe ENUM('rot','gruen','blau'));
CREATE TABLE
inkl. SET
CREATE TABLE buchstaben (
x SET('a','b','c','d'));
SELECT * FROM buchstaben WHERE x & 4;
SHOW TABLES
[FROM datenbankname] [LIKE "muster"];
Vorhandene Tabellen
anzeigen
-
ALTER TABLE tabellenname
[ADD datenfelddefinition]
[ADD indexdefinition]
[ADD CONSTRAINT contraintname
CHECK (gültigkeitsbedingung)]
[DROP objektname];
Tabellenstruktur ändern
ALTER TABLE adressen ADD STRASSE
TEXT NULL;
DROP TABLE tabellenname;
Tabelle löschen
DROP TABLE adressen;
DESCRIBE tabellenname;
Tabelleninformationen
anzeigen
Daten in Tabelle einfügen
DESCRIBE adressen;
INSERT INTO tabellenname1 (feld1,…,feldx)
SELECT datenfelder FROM tabellenname2
[WHERE bedingung];
Mehrere Datensätze
einfügen aus anderer Tabelle
INSERT INTO einwohner
(vorname,nachname) SELECT vorname,name
FROM mitglieder;
UPDATE tabellenname SET feld1='wert1'
[WHERE bedingung] ;
Daten aktualisieren
DELETE FROM tabellenname [WHERE
bedingung]
SELECT [DISCTINCT] *|Datenfelder FROM
tabelle
[WHERE Bedingung]
[GROUP BY Datenfelder [HAVING
Bedingung] ]
[ORDER BY Datenfelder [ASC | DESC] ]
[LIMIT [Start, ] Anzahl] ;
SELECT [DISCTINCT] *|Datenfelder FROM
tabelle
[WHERE Bedingung]
SELECT DISTINCT Datenfelder AS neu
FROM tabelle;
Daten löschen
UPDATE adressen SET
strasse='Grünaustrasse 16'
WHERE NAME='MEIER' AND
VORNAME='BEAT';
DELETE FROM adressen WHERE
NAME='Meyer';
SELECT * FROM mitarbeiter:
INSERT INTO tabellenname (feld1,…,feldx)
VALUES (wert1,…,wertx);
Daten ausgeben
INSERT INTO adressen (NAME,VORNAME)
VALUES('Meyer','Andreas');
Daten ausgeben
SELECT Name,Vorname
FROM mitarbeiter WHERE Name="English";
Daten ausgeben
SELECT DISTINCT Lohn AS Jahreslohn
FROM mitarbeiter ORDER BY Lohn;
SELECT Datenfelder
FROM tabelle [LIMIT [Start, ] Anzahl] ;
Daten ausgeben
SELECT * FROM mitarbeiter LIMIT 3,2;
SELECT Zahl1*Zahl2 AS Ergebnis 1,
Zahl1/Zahl2 AS Ergebnis 2,
Zahl1+Zahl2 AS Ergebnis 3,
Zahl1-Zahl2 AS Ergebnis 4
FROM tabelle;
Daten rechnen und ausgeben SELECT DISTINCT
Lohn,
ROUND(Lohn/12) as Monatslohn,
(ROUND(Lohn/12)*13) AS Jahreseinkommen
FROM mitarbeiter
WHERE ROUND(Lohn/12) > 3000 ORDER BY
Lohn;
Dominic Jäggi
4 von 6
14.05.2016
Glossar zur MySQL
SELECT
Daten ausgeben mit Aliases
SELECT m.Name,m.Vorname,f.Name
FROM mitarbeiter m,funktionen f
WHERE m.ID_Funktion=f.ID_Funktion;
SELECT datenfelder FROM tabelle
WHERE datenfeld LIKE "Muster" [ESCAPE
"Zeichen "];
Daten ausgeben mit
Fluchtzeichen
SELECT datenfekder FROM tabelle
WHERE datenfeld LIKE "";
Daten ausgeben mit LIKE
SELECT Name,Abteilung
FROM mitarbeiter
WHERE Abteilung LIKE "Abt\ ;%" ESCAPE "\
";
SELECT Name FROM mitarbeiter
WHERE Name LIKE "E%";
SELECT … GROUP BY
datenfeld[,datenfeld,...]
[HAVING Bedingung]
Daten ausgeben mit GROUP
SELECT o.Ort,COUNT(m.Name) AS "Anzahl
Wohnende"
FROM orte o, mitarbeiter m
WHERE m.ID_Ort=o.ID_Ort GROUP BY ort;
SELECT … GROUP BY
datenfeld[,datenfeld,...]
[HAVING Bedingung]
Daten ausgeben mit GROUP
und
HAVING
SELECT o.Ort,COUNT(m.Name) AS "Anzahl
Wohnende"
FROM orte o, mitarbeiter m
WHERE m.ID_Ort=o.ID_Ort
GROUP BY ort HAVING COUNT(m.Name) >
1;
SELECT … ORDER BY datenfeld, …
[ASC|DESC]
CREATE TABLE ( … PRIMARY KEY
(datenfeldname));
Daten ausgeben und
sortieren
Primary Key erstellen
SELECT * FROM mitarbeiter ORDER BY
name,vorname;
CREATE TABLE test (ID INT(11) NOT NULL
PRIMARY KEY (ID), Name TEXT NULL);
CREATE TABLE ( … UNIQUE
schlüsselname
(datenfeldname,…));
CREATE TABLE ( … FOREIGN KEY
(datenfeldname)
REFERENCES
tabellenname(datenfeldname));
FOREIGN KEY (datenfeldname)
REFERENCES
tabellenname(datenfeldname) ON DELETE
CASCADE;
ALTER TABLE tabellenname ADD PRIMARY
KEY
(datenfeldname);
ALTER TABLE tabellenname ADD UNIQUE
schlüsselname(datenfeld,…);
Sekundärschlüssel erstellen
-
Fremdschlüssel erstellen
-
referenzierte Datensätze
beim
Löschen einschliessen
-
Primärschlüssel nachträglich
hinzufügen
-
ALTER TABLE tabellenname ADD FOREIGN
KEY …;
ALTER TABLE tabellenname DROP
PRIMARY KEY;
ALTER TABLE tabellenname DROP INDEX
name;
CREATE INDEX indexname ON
tabelle(datenfeld);
DROP INDEX indexname [ON tabellenname];
Sekundärschlüssel
nachträglich
erstellen
Fremdschlüssel nachträglich erstellen
Primary Key löschen
-
Sekundärschlüssel löschen
-
einen Index erstellen
-
einen Index löschen
-
SHOW INDEX FROM tabellenname;
Indexes für Tabelle anzeigen
-
CREATE VIEW viewname [(datenfeldliste)]
AS SELECT
DROP VIEW viewname;
Eine Sicht erstellen
Eine Sicht löschen
geht noch nicht in MYSQL
INSERT INTO viewname …;
In eine Sicht Daten füllen
-
UPDATE viewname SET … ;
Eine Sicht updaten (wie
TABLE)
Aus einer sicht löschen
-
DECLARE cursorname CURSOR FOR
SELECT …;
OPEN cursorname
FETCH cursorname INTO | USING
:hostvariable, … ;
CLOSE cursorname;
Einen Cursor erstellen
geht noch nicht in MYSQL
Datenzugriff mit dem Cursor
OPEN c_lager;
FETCH c_lager INTO :id, :stueck, :preis;
Einen Cursor schliessen
-
DELIMITER <<Zeichen>>
Neuen Delimiter setzen als ;
DELIMITER //
DELETE FROM viewname …;
Dominic Jäggi
5 von 6
14.05.2016
-
Glossar zur MySQL
SELECT datenfelder FROM tabelle
WHERE feld > ALL (SELECT datenfelder
FROM
tabelle2 WHERE bedingung)
SELECT datenfelder FROM tabelle
WHERE feld > ANY (SELECT datenfelder
FROM
tabelle2 WHERE bedingung)
Dominic Jäggi
Sub-Query mit ALL
hier: grösser alles alle
anderen
SELECT lname,salary FROM employee
WHERE salary > ALL (SELECT salary
FROM employee WHERE dno=5);
Sub-Query mit ANY
hier: mehr als irgend einer
SELECT * FROM employee
WHERE salary > ANY (SELECT e.SALARY
FROM EMPLOYEE e, DEPARTMENT d
WHERE e.SSN = d.MGRSSN);
6 von 6
14.05.2016
Glossar zur MySQL