MySQL-Erste-Schritte-Tutorial 7 – Beispiele für häufige Datenbankabfragen
- 黄舟Original
- 2017-02-23 11:44:511149Durchsuche
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. Unter der Annahme, dass jeder Händler einen festen Preis für jeden Artikel hat, 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-Versionen 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 Spalte
Aufgabe: Suchen die teuerste Artikelnummer, Verkäufer und Preis. Das 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 einem MySQLspezifischen LIMIT zu erhalten Klausel:
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
Hinweis: Wenn es mehrere teuerste Artikel gibt (z. B. beträgt der Preis für jeden Artikel 19,95), zeigt die LIMIT-Lösung nur einen davon an!
3. Maximalwert der Spalte: nach Gruppe
Aufgabe: Wie hoch ist der Höchstpreis für jeden Artikel?
SELECT article, MAX(price) AS price FROM shop GROUP BY article +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +---------+-------+
Die Zeile mit dem Maximalwert eines bestimmten Feldes zwischen Gruppen
Aufgabe: Finden Sie für jeden Artikel den Händler des teuersten Artikelgeschäfts .
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 die MySQL-Benutzervariablen löschen, um die aufzuzeichnen Ergebnisse, ohne sie auf der Clientseite in temporären Variablen speichern zu müssen.
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 externe Schlüsselwörter verwendet werden, wenn Spalten mithilfe der Klausel REFERENCES tbl_name(col_name) definiert werden. Diese Klausel hat keine tatsächliche Wirkung und wird nur verwendet als Memo oder Kommentar, um Sie daran zu erinnern, dass die Spalte, die Sie gerade definieren, auf eine Spalte in einer anderen Tabelle verweist. Beim Ausführen dieser Anweisung ist es wichtig, Folgendes zu implementieren:
· MySQL führt keine Aktionen in der Tabelle tbl_name aus, beispielsweise als Reaktion auf Aktionen in Zeilen in der Tabelle Sie Diese Syntax verursacht kein ON DELETE- oder ON UPDATE-Verhalten (wenn Sie eine ON DELETE- oder ON UPDATE-Klausel in die REFERENCES-Klausel schreiben).
· Diese Syntax erstellt eine Spalte, aber keinen Index oder Schlüsselwort.
· Wenn Sie diese Syntax zum Definieren einer InnoDB-Tabelle verwenden, tritt ein Fehler auf.
Sie können Spalten verwenden, die wie folgt als Join-Spalten erstellt wurden:
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 | style | color | owner |+----+---------+--------+-------+| 1 | polo | blue | 1 || 2 | dress | white | 1 || 3 | t-shirt | blue | 1 || 4 | dress | orange | 2 || 5 | polo | red | 2 || 6 | dress | blue | 2 || 7 | t-shirt | white | 2 |+----+---------+--------+-------+ SELECT s.* FROM person p, shirt sWHERE p.name LIKE 'Lilliana%'AND s.owner = p.idAND s.color <> 'white'; +----+-------+--------+-------+| id | style | color | owner |+----+-------+--------+-------+| 4 | dress | orange | 2 || 5 | polo | red | 2 || 6 | dress | blue | 2 |+----+-------+--------+-------+
Auf diese Weise wird die REFERENCES-Klausel nicht in SHOW CREATE TABLE oder DESCRIBE angezeigt. In der Ausgabe:
SHOW CREATE TABLE shirt\G *************************** 1. row *************************** Table: shirt Create Table: CREATE TABLE `shirt` ( `id` smallint(5) unsigned NOT NULL auto_increment, `style` enum('t-shirt','polo','dress') NOT NULL, `color` enum('red','blue','orange','white','black') NOT NULL, `owner` smallint(5) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
In Spaltendefinitionen gilt die Verwendung von REFERENZEN als Kommentar oder „Hinweis“ auf diese Weise für die Tabellen MyISAM und BerkeleyDB.
7. Die Suche auf der Grundlage von zwei Schlüsseln
kann die OR-Klausel mit einem einzigen Schlüsselwort vollständig nutzen, genau wie AND.
Ein flexibleres Beispiel besteht darin, zwei durch ODER kombinierte Schlüsselwörter zu finden:
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1'
Diese Situation wurde optimiert.
Sie können UNION auch verwenden, um die Ausgabe von zwei separaten SELECT-Anweisungen zusammenzufassen, um dieses Problem effektiver zu lösen.
Jedes SELECT sucht nur nach einem Schlüsselwort und kann optimiert werden:
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. Berechnen Sie Besuche basierend auf Tagen
Das folgende Beispiel zeigt, wie die Bitgruppenfunktion verwendet wird um die Anzahl der Tage in jedem Monat zu berechnen, an denen ein Benutzer eine Webseite besucht.
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);Die Beispieltabelle enthält Jahr-Monat-Tag-Werte, die den Besuch des Benutzers auf der Webseite darstellen. Die folgende Abfrage kann verwendet werden, um die Anzahl der besuchten Tage pro Monat zu ermitteln:
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;gibt Folgendes zurück:
+------+-------+------+ | year | month | days | +------+-------+------+ | 2000 | 01 | 3 | | 2000 | 02 | 2 | +------+-------+------+
Diese Abfrage zählt die verschiedenen Tage kombiniert nach Jahr/Monat im In der Tabelle können doppelte Abfragen automatisch entfernt werden.
9. Verwenden Sie AUTO_INCREMENT
, um über das AUTO_INCREMENT-Attribut eine eindeutige Kennung für die neue Zeile zu generieren:
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;gibt Folgendes zurück:
+----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+
Sie können die SQL-Funktion LAST_INSERT_ID() oder die C-API-Funktion mysql_insert_id() verwenden, um den neuesten AUTO_INCREMENT-Wert abzufragen. Diese Funktionen sind verbindungsspezifisch, sodass ihre Rückgabewerte nicht von anderen Verbindungen beeinflusst werden, die Einfügefunktionen ausführen.
Hinweis: Bei mehrzeiligen Einfügungen geben LAST_INSERT_ID() und mysql_insert_id() tatsächlich das Schlüsselwort AUTO_INCREMENT aus der ersten eingefügten Zeile zurück. In einem Replikationssetup ermöglicht diese Funktion die korrekte Replikation mehrzeiliger Einfügungen auf anderen Servern.
Für MyISAM- und BDB-Tabellen können Sie AUTO_INCREMENT und mehrspaltige Indizes in der zweiten Spalte angeben. Zu diesem Zeitpunkt lautet die Berechnungsmethode des von der Spalte AUTO_INCREMENT generierten Werts: MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. Verwenden Sie diese Methode, wenn Sie die Daten in sortierte Gruppen einteilen möchten.
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;gibt Folgendes zurück:
+--------+----+---------+ | 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;
以上就是MySQL入门教程7 —— 常用数据库查询的示例的内容，更多相关内容请关注PHP中文网（www.php.cn）！