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
|
Fluffy
|
Harold
|
cat
|
f
|
1993-02-04
|
|
Claws
|
Gwen
|
cat
|
m
|
1994-03-17
|
|
Buffy
|
Harold
|
dog
|
f
|
1989-05-13
|
|
Fang
|
Benny
|
dog
|
m
|
1990-08-27
|
|
Bowser
|
Diane
|
dog
|
m
|
1979-08-31
|
1995-07-29
|
Chirpy
|
Gwen
|
bird
|
f
|
1998-09-11
|
|
Whistler
|
Gwen
|
bird
|
1997-12-09
|
||
Slim
|
Benny
|
snake
|
m
|
1996-04-29
|
·
Seleksi semua data yang ada pada table pet:
mysql > select * from pet;
·
Seleksi baris tertentu
·
mysql> SELECT * FROM pet WHERE
name = 'Bowser';
·
mysql> SELECT * FROM pet WHERE
birth >= '1998-1-1';
·
mysql> SELECT * FROM pet WHERE
species = 'dog' AND sex = 'f';
·
mysql> SELECT * FROM pet WHERE
species = 'snake' 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;.....
1 komentar:
interesant
Posting Komentar