MySQL ist das am weitesten verbreiteteste relationalen Datenbankverwaltungssysteme, wenn es um dynamische Webauftritte geht. Es steht als Open-Source-Software und als kommerzielle Enterprise Version zur Verfügung.
DROP TABLE IF EXISTS lotto;
CREATE TABLE lotto (
ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Tag INT NOT NULL,
Monat INT NOT NULL,
Jahr INT NOT NULL,
Zahl1 INT NOT NULL,
Zahl2 INT NOT NULL,
Zahl3 INT NOT NULL,
Zahl4 INT NOT NULL,
Zahl5 INT NOT NULL,
Zahl6 INT NOT NULL,
Superzahl INT,
ErstelltAm TIMESTAMP DEFAULT NOW());
DROP TRIGGER IF EXISTS lotto_tr_count;
CREATE TRIGGER lotto_tr_count
BEFORE INSERT ON lotto
FOR EACH ROW
BEGIN
-- do whatever you want
END;
SELECT
DATE_FORMAT(CURDATE(), '%d.%m.%Y') as Aktuell
, DATE_FORMAT(CURDATE() + INTERVAL 1 DAY, '%d.%m.%Y') as Morgen
, DATE_FORMAT(CURDATE() - INTERVAL 1 DAY, '%d.%m.%Y') as Gestern;
+------------+------------+------------+
| Aktuell | Morgen | Gestern |
+------------+------------+------------+
| 10.07.2010 | 11.07.2010 | 09.07.2010 |
+------------+------------+------------+
SELECT
DATE_FORMAT(NOW(), '%d.%m.%Y %H:%i:%s') as Aktuell
, DATE_FORMAT(NOW() + INTERVAL 1 DAY, '%d.%m.%Y %H:%i:%s') as Morgen
, DATE_FORMAT(NOW() - INTERVAL 1 DAY, '%d.%m.%Y %H:%i:%s') as Gestern;
+---------------------+---------------------+---------------------+
| Aktuell | Morgen | Gestern |
+---------------------+---------------------+---------------------+
| 10.07.2010 09:38:03 | 11.07.2010 09:38:03 | 09.07.2010 09:38:03 |
+---------------------+---------------------+---------------------+
SELECT
TIME_FORMAT(CURTIME(), '%H:%i:%s') as Aktuell
, TIME_FORMAT(DATE_ADD(NOW(), INTERVAL 1 HOUR), '%H:%i:%s') as Nachfolger
, TIME_FORMAT(DATE_SUB(NOW(), INTERVAL 1 HOUR), '%H:%i:%s') as Vorgänger;
+----------+------------+------------+
| Aktuell | Nachfolger | Vorgänger |
+----------+------------+------------+
| 09:56:29 | 10:56:29 | 08:56:29 |
+----------+------------+------------+
SELECT DATEDIFF('2010-07-30 23:59:59','2010-07-21') as Differenz;
+-----------+
| Differenz |
+-----------+
| 9 |
+-----------+
SELECT DATEDIFF('2010-07-30','2010-07-21') as Differenz;
+-----------+
| Differenz |
+-----------+
| 9 |
+-----------+
SELECT
DAYOFMONTH('2010-07-30') as Tag
, DAYNAME('2010-07-30') as Tagname
, DAYOFWEEK('2010-07-30') as Wochentag
, MONTH('2010-07-30') as Monat
, MONTHNAME('2010-07-30') as Monatname;
+------+---------+-----------+-------+-----------+
| Tag | Tagname | Wochentag | Monat | Monatname |
+------+---------+-----------+-------+-----------+
| 30 | Friday | 6 | 7 | July |
+------+---------+-----------+-------+-----------+
SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2010-07-30 10:15:32 | 0 | 2010-07-30 10:15:34 |
+---------------------+----------+---------------------+
SELECT
STR_TO_DATE('01.7.2010','%d.%m.%Y') as Datum
, STR_TO_DATE('01.7.2010 09:30:17','%d.%m.%Y %h:%i:%s') as DatumZeit
, STR_TO_DATE('09:30:17','%h:%i:%s') as Zeit;
+------------+---------------------+----------+
| Datum | DatumZeit | Zeit |
+------------+---------------------+----------+
| 2010-07-01 | 2010-07-01 09:30:17 | 09:30:17 |
+------------+---------------------+----------+
CREATE VIEW digits_view AS
SELECT 0 AS digits_view UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9;
CREATE VIEW numbers_view AS
SELECT ones.digits_view +
tens.digits_view * 10 +
hundreds.digits_view * 100 +
thousands.digits_view* 1000 AS number
FROM
digits_view as ones,
digits_view as tens,
digits_view as hundreds,
digits_view as thousands;
CREATE VIEW dates_view AS
SELECT SUBDATE(CURRENT_DATE(), number) AS date
FROM numbers_view;
-- Test Datum
SELECT date as Datum
FROM dates_view
WHERE date BETWEEN '2010-07-10' AND '2010-07-14'
ORDER BY date;
+------------+
| Datum |
+------------+
| 2010-07-10 |
| 2010-07-11 |
| 2010-07-12 |
| 2010-07-13 |
| 2010-07-14 |
+------------+
-- Test Jahr
SELECT DISTINCT
DATE_FORMAT(STR_TO_DATE(date,'%Y-%m-%d'), '%Y') as Jahr
FROM dates_view
WHERE date BETWEEN '2008-01-01' AND '2010-01-01'
ORDER BY date;
+------+
| Jahr |
+------+
| 2008 |
| 2009 |
| 2010 |
+------+