Contoh Query Yang Umum

Berikut ini diberikan contoh tentang bagaimana memecahkan masalah yang umum ditemukan pada MySQL.

Beberapa contoh menggunakan tabel shop unuk menyimpan harga dari tiap article untuk penjual tertentu. Dianggap bahwa tiap penjual memiliki harga tetap untuk tiap article, lalu (item,trader) adalah kunci primer untuk record-nya.

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

proses penambahan/insert data dengan sintaks berikut
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);

Kemudian, contoh datanya adalah :

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

Untuk melihat nomer article terbesar, Anda dapat menggunakan :

SELECT MAX(article) AS article FROM shop
+———+
| article |
+———+
|       4 |
+———+

Untuk menemukan baris yang berisi nilai maximum untuk kolom tertentu, pada ANSI SQL hal ini dapat mudah dilakukan dengan subquery :

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

Pada MySQL (yang tidak memiliki subquery), lakukanlah dalam 2 langkah :

1.      Ambil nilai harga (price) maximum dari tabel dengan pernyataan SELECT.
2.      Menggunakan nilai ini, buatlah query sebenarnya :

SELECT article, dealer, price
FROM   shop
WHERE  price=19.95

Cara lain adalah dengan mengurutkan semua baris secara descending (dari besar ke kecil) dan hanya mengambil baris pertama menggunakan kalusa spesifik LIMIT :

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

Catatan: Bila terdapat beberapa baris dengan nilai price maximum yang sama, cuma ditampilkan salah satu saja, yaitu yang pertama.

Untuk menunjukkan nilai maximum dari kolom per grup cuma nilainya, dapat menggunakan :
“What’s the highest price per article?”

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

Untuk mencari dealer dengan price yang paling mahal untuk tiap article-nya, pada ANSI SQL, dapat menggunakan :

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

Pada MySQL, cara terbaik untuk melakukannya dengan langkah berikut :
1.      Ambil daftar (article,maxprice).
2.      Untuk tiap article ambil baris yang bersangkutan yang menyimpan price maximum.

Hal ini paling mudah dilakukan dengan sebuah tabel temporary :

CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT ‘0000’ NOT NULL,
price   DOUBLE(16,2)  DEFAULT ‘0.00’ NOT NULL);
LOCK TABLES article read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT article, dealer, price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;

Jika Anda tidak menggunakan tabel TEMPORARY, Anda juga harus mengunci (LOCK) tabel tmp. Masalah ini juga dapat dilakukan dengan query tunggal, tetapi dengan menggunakan trik yang tidak efisien :

SELECT article,
SUBSTRING( MAX( CONCAT(LPAD(price,6,’0′),dealer) ), 7) AS dealer,
0.00+LEFT( MAX( CONCAT(LPAD(price,6,’0′),dealer) ), 6) AS price
FROM   shop
GROUP BY article;

+———+——–+——-+
| article | dealer | price |
+———+——–+——-+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+———+——–+——-+

Pada MySQL Anda tidak memerlukan foreign key untuk menggabungkan (join) dua tabel. Satu hal yang tidak dilakukan MySQL adalah mengecek untuk memastikan bahwa key yang digunakan benar-benar ada pada tabel yang Anda referensi dan tidak secara otomatis menghapus baris dari tabel yang berisi definisi foreign key.

CREATE TABLE persons (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE shirts (
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 persons,
PRIMARY KEY (id)
);

INSERT INTO persons VALUES (NULL, ‘Antonio Paz’);
INSERT INTO shirts VALUES
(NULL, ‘polo’, ‘blue’, LAST_INSERT_ID()),
(NULL, ‘dress’, ‘white’, LAST_INSERT_ID()),
(NULL, ‘t-shirt’, ‘blue’, LAST_INSERT_ID());

INSERT INTO persons VALUES (NULL, ‘Lilliana Angelovska’);
INSERT INTO shirts VALUES
(NULL, ‘dress’, ‘orange’, LAST_INSERT_ID()),
(NULL, ‘polo’, ‘red’, LAST_INSERT_ID()),
(NULL, ‘dress’, ‘blue’, LAST_INSERT_ID()),
(NULL, ‘t-shirt’, ‘white’, LAST_INSERT_ID());

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

SELECT * FROM shirts;
+—-+———+——–+——-+
| 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 persons p, shirts s
WHERE p.name LIKE ‘Lilliana%’
AND s.owner = p.id
AND s.color <> ‘white’;

+—-+——-+——–+——-+
| id | style | color  | owner |
+—-+——-+——–+——-+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+—-+——-+——–+——-+

Pada pencarian dua key, MySQL belum mengoptimasikan pencarian Anda pada kombinasi dua key yang berbeda dengan OR :

SELECT field1_index, field2_index FROM test_table WHERE field1_index = ‘1’ OR  field2_index = ‘1’

Untuk saat ini Anda dapat memecahkan masalah ini dengan sangat efisien dengan menggunakan tabel TEMPORARY, optimasi tipe ini juga sangat bagus jika Anda menggunakan query yang sangat kompleks dimana server SQL melakukan optimasi dengan cara yang salah.

CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = ‘1’;
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = ‘1’;
SELECT * from tmp;
DROP TABLE tmp;