MySQL - Cheat Sheet

2012-02-07T08:51:03Z

94.216.253.248: mySQL Code

=== Query ===

SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT field1, field2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
SELECT ... FROM t1 JOIN t2 USING(id) WHERE condition

=== Conditionals ===
field1 = value1
field1 <> value1
field1 LIKE 'value _ %'
field1 IS NULL
field1 IS NOT NULL
field1 IN (value1, value2)
field1 NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2

=== Data Manipulation ===

INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)
INSERT table1 SET field1=value_1, field2=value_2 ...

DELETE FROM table1 / TRUNCATE table1
DELETE FROM table1 WHERE condition
-- join:
DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition

UPDATE table1 SET field1=new_value1 WHERE condition
-- join:
UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ...
WHERE table1.id1 = table2.id2 AND condition

=== Browsing ===

SHOW DATABASES
SHOW TABLES
SHOW FIELDS FROM table / SHOW COLUMNS FROM table / DESCRIBE table / DESC table / EXPLAIN table
SHOW CREATE TABLE table
SHOW PROCESSLIST
KILL process_number

$ mysqlshow
$ mysqlshow database

=== Create / delete / select / alter database ===

CREATE DATABASE [IF NOT EXIST] mabase [CHARACTER SET charset] [COLLATE collation]
CREATE DATABASE mabase CHARACTER SET utf8
DROP DATABASE mabase
USE mabase

ALTER DATABASE mabase CHARACTER SET utf8

=== Create/delete/modify table ===

CREATE TABLE table (field1 type1, field2 type2, ...)
CREATE TABLE table (field1 type1, field2 type2, ..., INDEX (field))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1, field2))
CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA))
[ON UPDATE|ON DELETE] [CASCADE|SET NULL]
CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
CREATE TABLE table IF NOT EXISTS (...)

CREATE TABLE new_tbl_name LIKE tbl_name
[SELECT ... FROM tbl_name ...]

CREATE TEMPORARY TABLE table (...)

DROP TABLE table
DROP TABLE IF EXISTS table
DROP TABLE table1, table2, ...
DROP TEMPORARY TABLE table

ALTER TABLE table MODIFY field1 type1
ALTER TABLE table MODIFY field1 type1 NOT NULL ...
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
ALTER TABLE table ALTER field1 SET DEFAULT ...
ALTER TABLE table ALTER field1 DROP DEFAULT
ALTER TABLE table ADD new_name_field1 type1
ALTER TABLE table ADD new_name_field1 type1 FIRST
ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
ALTER TABLE table DROP field1
ALTER TABLE table ADD INDEX (field);
ALTER TABLE table ADD PRIMARY KEY (field);

-- Change field order:
ALTER TABLE table MODIFY field1 type1 FIRST
ALTER TABLE table MODIFY field1 type1 AFTER another_field
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER another_field

ALTER TABLE old_name RENAME new_name;

=== Keys ===

CREATE TABLE table (..., PRIMARY KEY (field1, field2))
CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2 (t2_field1, t2_field2))
ALTER TABLE table ADD PRIMARY KEY (field);

=== Privileges ===

GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions

SET PASSWORD = PASSWORD('new_pass')
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass')
SET PASSWORD = OLD_PASSWORD('new_pass')

DROP USER 'user'@'host'

=== Main data types ===

TINYINT (1o: -127+128) SMALLINT (2o: +-65 000)
MEDIUMINT (3o: +-16 000 000) INT (4o: +- 2 000 000 000)
BIGINT (8o: +-9.10^18)
Precise interval: -(2^(8*N-1)) -> (2^8*N)-1
/!\ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"

INT NOT NULL auto_increment PRIMARY KEY -- auto-counter for PK

FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53)
/!\ 8,3 -> 12345,678 -- NOT 12345678,123!

TIME (HH:MM) YEAR (AAAA) DATE (AAAA-MM-JJ) DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)

VARCHAR (single-line; explicit size) TEXT (multi-lines; max size=65535) BLOB (binary; max size=65535)
Variants for TEXT&BLOB: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go)
Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT

ENUM ('value1', 'value2', ...) -- (default NULL, or <nowiki>''</nowiki> if NOT NULL)

=== Forgot root password? ===

$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables
$ mysql # on another terminal
mysql> UPDATE mysql.user SET password=PASSWORD('nouveau') WHERE user='root';
## Kill mysqld_safe from the terminal, using Control + \
$ /etc/init.d/mysql start

=== Repair tables after unclean shutdown ===

mysqlcheck --all-databases
mysqlcheck --all-databases --fast

[[Category:{{FULLBOOKNAME}}|{{FULLCHAPTERNAME}}]]

MySQL

2012-02-07T08:50:43Z

94.216.253.248: /* mySQL - Tips und Tricks */

Informationen zur Datenbank aber auch Tipps zur Bedienung etc.
== mySQL - Tips und Tricks ==
[[mySQL - Tips und Tricks]]

== mySQL - Cheat Sheet ==
[[mySQL - Cheat Sheet]]

== mySQL auf der Kommandozeile ==
[[mySQL - Kommandozeile]]

== Codeschnipsel ==
=== Typ der Tabellen festlegen ===
<pre>
CREATE TABLE `foo` {
...
} ENGINE=InnoDB

CREATE TABLE `bar` {
...
} ENGINE=MyISAM
</pre>

=== InnoDB aktivieren ===

Zitat aus http://www.marmotli.ch/mysql/node35.html

5.2.1 Aktivieren der InnoDB-Tabellen

Das Arbeiten mit dem Tabellentyp 'InnoDB' setzt voraus, dass der MySQL-Server mit diesem Tabellentyp hochgefahren wird.

Unter Debian 3.0 d.h. falls wir MySQL gemäss dieser Anleitung aufgesetzt haben, steht der InnoDB-Tabellentyp zunächst nicht zur Verfügung. Nicht weil MySQL 4.0.13 das nicht unterstützen würde, sondern weil wir die my.cnf-Datei von der Version 3.23.x übernommen haben und das dort nicht aktiviert war. Um die InnoDB-Tabellen nun zu aktivieren, müssen wir einige Änderungen in der Datei '/etc/mysql/my.cnf' vornehmen:

# Read the manual if you want to enable InnoDB!
# skip-innodb
innodb_data_home_dir =
innodb_data_file_path = /var/lib/mysql/ibdata/ibdata1:100M:autoextend

Zunächst muss 'skip-innodb' deaktiviert werden. Danach müssen wir den Speicherort für die Tabellen angeben. Falls wir 'innodb_data_home_dir' leer belassen, verwendet MySQL den normalen Datenpfad der Datenbanken. Dort werden einige Temporär-Dateien erstellt. Den Speicherort für die InnoDB-Tabellen legen wir mit dem Wert 'innodb_data_file_path' fest. Wir verwenden wiederum den Speicherort für die MySQL-Datenbanken sowie das Unterverzeichnis 'ibdata'. Dieses Verzeichnis muss von Hand angelegt werden:

mkdir /var/lib/mysql/ibdata
chown -R mysql /var/lib/mysql/ibdata
chgrp -R mysql /var/lib/mysql/ibdata

Um die InnoDB-Tabellen endgültig nutzen zu können, müssen wir den MySQL-Server neu initialisieren:

/etc/init.d/mysql restart

Der Neustart dauert einige Sekunden, weil die InnoDB-Tabellen zunächst initialisiert werden müssen. Um zu überprüfen, ob die InnoDB-Tabellen nun aktiviert sind, können wir in '/var/lib/mysql/ibdata' nachsehen. Falls Sie dort eine Datei vorfinden, ist alles ok.

=== Werte aus Komma getrennter Liste auswerten ===
Vor allem im Zusammenhang mit Typo3 Datenbanken kommt es oft dazu, daß man integer Werte in Komma-separierten Listen in einem Datenbank vorfindet. Auf diese Weise erzeugt Typo3 in der Standardeinstellung n:m Verknüpfungen (aber auch 1 : n Verknüpfungen).

Beim Auslesen ist das aber eher nachteilig. Eine geschicktere Methode als IN ab mysql 4 (meines Wissens) ist der Befehl FIND_IN_SET.

Die IN - Abfrage ist nur für Ganze Feldlängen zutreffend, also
'1','2','3','4','5',usw, ...

Bei der Funktion FIND_IN_SET sucht MySQL in einer bestimmten Zeile und gibt die
Stelle zurück an der der Wert in der LISTE steht.

FIND_IN_SET(WERT,LISTE)

In Typo3 gibts auch die Methode:
AND '.$GLOBALS['TYPO3_DB']->listQuery('feldname',5,'tabellenname').'
Die aber ein kompliziertes und wenig Performantes SQL Konstrukt erzeugt.

Vielleicht wird die Funktion ja noch umgebaut ;-)

== MySQL - Daten importieren und exportieren ==
[[MySQL - Daten importieren und exportieren]]

Wikizone - Benutzerbeiträge [de]

MySQL - Cheat Sheet

MySQL