Group Functions
Übersicht
• where selektiert die Gesamtmenge, auf der danach gruppiert werden kann.
• group by gruppiert und wendet ggf. Aggregierungsfunktionen an. Achtung: Diese können in der where Bedingung nicht verwendet werden.
• having ist eine Bedingung an die Ergebnisse der Gruppierung, hier kann das Schlüsselwort where nicht verwendet werden.
• Selektierte Attribute müssen in der group by Bedingung vorkommen, da sonst deren Wert innerhalb der Gruppierung nicht eindeutig und nicht sinnvoll ist.
• Ausdrücke können mit einem Alias versehen werden, der in having benutzt werden kann.
• Gruppierung kann auch Joins umfassen.
SELECT
SELECT column1, column2, ...
FROM table_name;
WHERE
FROM table_name
WHERE condition;
AND,OR,NOT
WHERE condition1 AND condition2 AND condition3 ...;
WHERE condition1 OR condition2 OR condition3 ...;
WHERE NOT condition;
VIEW
CREATE VIEW view_name AS
ORDER BY
ORDER BY column1, column2, ... ASC|DESC;
INSERT INTO
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
LIKE
WHERE columnN LIKE pattern;
IN
SELECT column_name(s)
WHERE column_name IN (value1, value2, ...);
WHERE column_name IN (SELECT STATEMENT);
BETWEEN
WHERE column_name BETWEEN value1 AND value2;
ALIAS
SELECT column_name AS alias_name
INNER JOIN
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
LEFT JOIN
LEFT JOIN table2 ON table1.column_name = table2.column_name;
RIGHT JOIN
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
GROUP BY
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
HAVING
HAVING condition
CREATE
CREATE DATABASE databasename;
DROP
DROP DATABASE databasename;
CREATE TABLE
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
DROP TABLE
DROP TABLE table_name;
ALTER TABLE
ALTER TABLE table_name
DROP/ADD column_name datatype;
FUNKTIONEN
SELECT SUM(column_name)FROM table_nameWHERE condition;
SELECT AVG(column_name)FROM table_nameWHERE condition;
SELECT COUNT(column_name)FROM table_nameWHERE condition;
SELECT MIN(column_name)FROM table_nameWHERE condition;
SELECT MAX(column_name)FROM table_nameWHERE condition;
Last changeda year ago