SQL - PL/SQL > Datenbank MySQL

Datenbank MySQL

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.

Create Table


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

Create Trigger


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;

DATE_FORMAT, CURDATE, INTERVAL


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

TIME_FORMAT, CURTIME, DATE_ADD, DATE_SUB, INTERVAL


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

DATEDIFF


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

DAYOFMONTH, DAYNAME, DAYOFWEEK, MONTH, MONTHNAME


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

SYSDATE, SLEEP


SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2010-07-30 10:15:32 |        0 | 2010-07-30 10:15:34 |
+---------------------+----------+---------------------+

STR_TO_DATE


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

Ein vordefiniertes Intervall von Datum oder Jahren ausgehen.


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

Example

Sitemap Kontakt Impressum