Query Data Pada Mysql

1.      Seleksi Data dengan perintah SELECT

Untuk melakukan seleksi data-data yang ada pada table mysql dipergunakan perintah SELECT. Sebelum menggunakan perintah SELECT akan dibuat terlebih dahulu table berikut data-data yang ada didalamnya:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Melakukan penyisipan data pada table pet

mysql >INSERT INTO pet(name,owner,species,sex,birth,death) values('Fluffy','Harold','cat','f','1993-02-04','');

mysql> INSERT INTO pet(name,owner,species,sex,birth,death) values('Claws','Gwen','cat',' m','1994-03-17','');

mysql >INSERT INTO pet(name,owner,species,sex,birth,death) values('Buffy',' Harold','dog','f','1989-05-13','');

Masukkan data lain sehingga data pada table pet

name	owner	species	sex	birth	death
Slark	Slardar	Fish	m	2001-08-09	2010-01-01
Void	Sven	cat	f	1993-03-19	2011-02-03
Balanar	Huskar	dog	m	1999-05-13	2001-05-01
Tuskar	Razor	cheep	m	2000-02-02	1999-08-09
Barathum	Sireen	dog	f	2009-05-30	1995-07-29
Gondar	Lesale	dog	m	2008-02-01	1995-08-08
Meepo	akasha	bird	f	2007-12-03	2001-01-01
Rikimaru	Creeps	snake	m	1998-01-22

·         Seleksi semua data yang ada pada table pet:

mysql > select * from pet;

·         Seleksi baris tertentu

·         mysql> SELECT * FROM pet WHERE name = 'Meepo';

·         mysql> SELECT * FROM pet WHERE birth >= '2007-12-03';

·         mysql> SELECT * FROM pet WHERE species = 'bird' AND sex = 'f';

·         mysql> SELECT * FROM pet WHERE species = 'Creeps' OR species = 'bird';

·         mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');

·         Seleksi kolom tertentu

mysql> SELECT name, birth FROM pet;

·         Seleksi nama pemilik pet

mysql>  SELECT owner FROM pet;

·         Seleksi nama pemilik pet tetapi nama sama ditampilkan satu kali

mysql> SELECT DISTINCT owner FROM pet;

·         Seleksi data yang tergolong cat atau dog

mysql> SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';

2.      Sorting baris pada tabel

Seleksi data diurutkan berdasarkan tanggal lahir

mysql> SELECT name, birth FROM pet ORDER BY birth;

Seleksi data diurutkan berdasarkan tanggal lahir diurutkan secara terbalik

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

Pengurutan data berdasarkan beberapa kolom data :

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

3.      Kalkulasi usia

mysql>  SELECT name, birth, CURDATE(),(YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age FROM pet;

Kalkulasi usia diurutkan berdasarkan nama:

SELECT name, birth, CURDATE(),(YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age FROM pet ORDER BY name;

Kalkulasi usia diurutkan berdasarkan usia

mysql>  SELECT name, birth, CURDATE(),(YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age FROM pet ORDER BY age;

SELECT name, birth, death,(YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5)) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;

mysql> SELECT name, birth, MONTH(birth) FROM pet;

SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

4.      Pattern Matching

Digunakan untuk melakukan seleksi berdasarkan pola tertentu

mysql> SELECT * FROM pet WHERE name LIKE 'b%';

Perintah tersebut digunakan untuk mencari data yang name diawali huruf b.

mysql> SELECT * FROM pet WHERE name LIKE '%fy';

Digunakan untuk mencari data name yang mengandung fy

mysql>  SELECT * FROM pet WHERE name LIKE '%w%';

Selain menggunakan LIKE Pattern Matching dapat menggunakan REGEXP

mysql> SELECT * FROM pet WHERE name REGEXP '^b';

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

mysql> SELECT * FROM pet WHERE name REGEXP 'w';

5.      Menghitung jumlah baris

Untuk menghitung baris dipergunakan operator COUNT.

mysql> SELECT COUNT(*) FROM pet;

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

mysql> SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;

mysql> SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;