Allgemeine MySQL-Datenbankabfragen

Hier finden Sie einige Beispiele für die Verwendung von MySQL zur Lösung einiger häufiger Probleme.

In einigen Beispielen wird die Datenbanktabelle „Shop“ verwendet, um den Preis jedes Artikels (Artikelnummer) eines bestimmten Händlers (Händlers) zu speichern. Angenommen, jeder Händler hat einen festen Preis für jeden Artikel, dann ist (Artikel, Händler) der Primärschlüssel des Datensatzes.

Starten Sie das Befehlszeilentool MySQL und wählen Sie die Datenbank aus:

shell> mysql your-database-name

(In den meisten MySQL-Datenbanken können Sie die Testdatenbank verwenden).

Sie können eine Beispieltabelle mit der folgenden Anweisung erstellen:

mysql> CREATE TABLE shop (     -> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,     -> dealer  CHAR(20)  
 DEFAULT ''     NOT NULL,     -> price  
 DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,     
 -> PRIMARY KEY(article, dealer)); mysql> INSERT INTO shop VALUES     
 -> (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),     -> (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

Nach der Ausführung der Anweisung sollte die Tabelle Folgendes enthalten:

mysql> SELECT * FROM shop; +---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

1 Wert der Spalte

„Was ist die größte Artikelnummer?“

SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
|       4 |
+---------+

2. Die Zeile mit dem Maximalwert einer bestimmten Spalte

Aufgabe: Finden Sie die Nummer der teuersten Artikel, Verkäufer und Preise. Dies geht ganz einfach mit einer Unterabfrage:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

Eine andere Lösung besteht darin, alle Zeilen nach Preis in absteigender Reihenfolge zu sortieren und nur die erste Zeile mit einer MySQL-spezifischen LIMIT-Klausel zu erhalten:

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

Hinweis: Wenn es mehrere teuerste Artikel gibt (z. B. mit einem Preis von jeweils 19,95), zeigt die LIMIT-Lösung nur einen davon an!

3. Maximaler Wert der Spalte: nach Gruppe

Aufgabe: Wie hoch ist der maximale Preis für jeden Artikel?

Artikel auswählen, MAX(Preis) AS-Preis
VOM Shop
GRUPPE NACH Artikel

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

4. Zwischen Gruppen mit einer bestimmten Feldzeile mit Maximalwert

Aufgabe: Für jeden Artikel den Händler des teuersten Artikels finden.

Sie können dieses Problem mit einer Unterabfrage wie dieser lösen:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

5. Verwendung von Benutzervariablen

Sie können MySQL-Benutzervariablen löschen, um die Ergebnisse aufzuzeichnen, ohne sie zu speichern in temporäre Variablen auf der Client-Seite.

Um beispielsweise den Artikel mit dem höchsten oder niedrigsten Preis herauszufinden, ist die Methode:

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price; +---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

6. Verwendung von Fremdschlüsseln

In MySQL, InnoDB-Tabelle unterstützt Prüfungen auf externe Schlüsselworteinschränkungen.

Beim Verbinden zweier Tabellen ist kein externes Schlüsselwort erforderlich. Für andere Tabellen als InnoDB-Typen können Sie externe Schlüsselwörter verwenden, wenn Sie die Klausel REFERENCES tbl_name(col_name) zum Definieren von Spalten verwenden. Diese Klausel hat keine tatsächliche Wirkung und wird nur als Notiz oder Kommentar verwendet, um Sie daran zu erinnern, dass die Spalte, die Sie gerade definieren zeigt auf eine andere A-Spalte in der Tabelle. Bei der Ausführung dieser Anweisung ist es wichtig, Folgendes zu implementieren:

MySQL führt keine Aktionen in der Tabelle tbl_name aus, z. B. das Löschen von Zeilen als Reaktion auf Aktionen für Zeilen in der Tabelle, die Sie definieren Syntax Verursacht kein ON DELETE- oder ON UPDATE-Verhalten (wenn Sie eine ON DELETE- oder ON UPDATE-Klausel in die REFERENCES-Klausel schreiben, wird diese ignoriert).

· Mit dieser Syntax kann eine Spalte erstellt werden, es wird jedoch kein Index oder Schlüsselwort erstellt.

· Wenn Sie diese Syntax zum Definieren einer InnoDB-Tabelle verwenden, tritt ein Fehler auf.

Sie können die erstellten Spalten wie folgt als Join-Spalten verwenden:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);
CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;

---- -- ------- -------
|. id |
---- ------- -- -------
|. 1 |
| |. 4 |. 2 |
| |. 2 |
---- --------- -------- -------

SELECT s.* FROM person p, shirt s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id

AND s.color <> 'white';

---- -- ----- -------
|. id |
---- ------- --. --------

|. 2 |

| ---- ------- -------- -------

Auf diese Weise verwendet, wird die REFERENCES-Klausel nicht in SHOW CREATE TABLE oder in angezeigt die Ausgabe von DESCRIBE:


In Spaltendefinitionen gilt die Verwendung von REFERENCES als Kommentar oder „Hinweis“ auf diese Weise für die Tabellen MyISAM und BerkeleyDB.

7. 根据两个键搜索

可以充分利用使用单关键字的OR子句，如同AND的处理。

一个比较灵活的例子是寻找两个通过OR组合到一起的关键字：

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

该情形是已经优化过的。

还可以使用UNION将两个单独的SELECT语句的输出合成到一起来更有效地解决该问题。

每个SELECT只搜索一个关键字，可以进行优化：

SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

8. 根据天计算访问量

下面的例子显示了如何使用位组函数来计算每个月中用户访问网页的天数。

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

示例表中含有代表用户访问网页的年－月－日值。可以使用以下查询来确定每个月的访问天数：

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

将返回：

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

该查询计算了在表中按年/月组合的不同天数，可以自动去除重复的询问。

9. 使用AUTO_INCREMENT

可以通过AUTO_INCREMENT属性为新的行产生唯一的标识：

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );
INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');
SELECT * FROM animals;

将返回：

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

你可以使用LAST_INSERT_ID()SQL函数或mysql_insert_id() C API函数来查询最新的AUTO_INCREMENT值。这些函数与具体连接有关，因此其返回值不会被其它执行插入功能的连接影响。

注释：对于多行插入，LAST_INSERT_ID()和mysql_insert_id()从插入的第一行实际返回AUTO_INCREMENT关键字。在复制设置中，通过该函数可以在其它服务器上正确复制多行插入。

对于MyISAM和BDB表，你可以在第二栏指定AUTO_INCREMENT以及多列索引。此时，AUTO_INCREMENT列生成的值的计算方法为：MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。如果想要将数据放入到排序的组中可以使用该方法。

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;

将返回：

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

请注意在这种情况下（AUTO_INCREMENT列是多列索引的一部分），如果你在任何组中删除有最大AUTO_INCREMENT值的行，将会重新用到AUTO_INCREMENT值。对于MyISAM表也如此,对于该表一般不重复使用AUTO_INCREMENT值。

如果AUTO_INCREMENT列是多索引的一部分，MySQL将使用该索引生成以AUTO_INCREMENT列开始的序列值。。例如，如果animals表含有索引PRIMARY KEY (grp, id)和INDEX(id)，MySQL生成序列值时将忽略PRIMARY KEY。结果是，该表包含一个单个的序列，而不是符合grp值的序列。

要想以AUTO_INCREMENT值开始而不是1，你可以通过CREATE TABLE或ALTER TABLE来设置该值，如下所示:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;