perintah dasar SQL

PERINTAH DASAR-DASAR SQL
Pada saat pengoperasian MySQL, kita perlu menggunakan perintah-perintah SQL atau SQL statement. Perintah atau statement SQL tersebut secara garis besar dibagi dalam 2 kelompok yaitu : Data Manipulation Language (DML) dan Data Definition Language (DDL).
Yang termasuk dalam DML adalah :
• SELECT – untuk mengekstrak atau memilih data dari sebuah database
• UPDATE – untuk mengupdate data dari sebuah database
• DELETE – untuk menghapus data dari sebuah database
• INSERT – untuk memasukkan data baru ke dalam sebuah database
Sedangkan DDL yang paling penting adalah :
• CREATE DATABASE – untuk membuat sebuah database baru
• ALTER TABLE – untuk menambah, menghapus atau memodifikasi kolom dari tabel yang sudah ada
• CREATE TABLE – untuk membuat tabel baru
• DROP TABLE – untuk menghapus sebuah tabel
• CREATE INDEX – untuk membuat index (kunci pencarian)
• DROP INDEX – untuk menghapus index
Perlu kita ingat bahwa perintah atau statement SQL tersebut tidak case sensitive dengan kata lain perintah yang anda tulis dalam huruf kapital maupun huruf kecil akan di terjemahkan sama.
Kemudian di setiap akhir dari perintah tersebut harus diberi tanda semi colon atau ; sehingga perintah akan dijalankan untuk setiap statement atau perintah tersebut.
Perlu diketahui bahwa database dapat terdiri dari beberapa tabel dan di setiap tabel dapat terdiri dari beberapa kolom dan beberapa data.
Perintah TOP pada MySQL sebenarnya merupakan varian dari perintah SELECT pada MySQL. Perintah TOP digunakan untuk memilih sejumlah data teratas dari sebuah database, sesuai dengan namanya TOP. Hal ini sangat berguna ketika kita menginginkan data teratas. Misal: dalam sebuah perangkingan kita menginginkan data pemilik nilai tertinggi atau 10 besar nya.
Untuk menggunakannya, perintah TOP mengenal 2 type pemanggilan database yaitu berdasarkan jumlah data atau berdasarkan persentase data. Berikut polanya :

SELECT TOP jumlah baris atau persentase *
FROM nama tabel
Contoh :
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
1 Bambang Pemuda 123 Bekasi Jawa Barat
2 Agung Sudirman 57 Bandung Jawa Barat
3 Satria Majapahit 57 Solo Jawa Tengah
4 Tony Jawa Tengah
Kemudian kita ingin mengambil 2 data yang paling atas:
SELECT TOP 2 *
FROM Mahasiswa
atau kita ingin mengambil separo atau 50% dari data yang ada:
SELECT TOP 50 PERCENT *
FROM Mahasiswa
maka hasil keduanya (kebetulan sama) akan menjadi:
ID Nama Alamat Kota Propinsi
1 Bambang Pemuda 123 Bekasi Jawa Barat
2 Agung Sudirman 57 Bandung Jawa Barat
Perintah UPDATE pada MySQL berfungsi untuk mengupdate nilai yang terdapat dalam sebuah database. Dan pada saat mengaplikasikannya harus ditambahkan SET dan WHERE agar proses update sesuai dengan yang kita harapkan.
Contoh :
Kita ingin menambahkan alamat lengkap Tony dari tabel Mahasiswa berikut ini:
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
1 Bambang Pemuda 123 Bekasi Jawa Barat
2 Agung Sudirman 57 Bandung Jawa Barat
3 Satria Majapahit 57 Solo Jawa Tengah
4 Tony Jawa Tengah
Maka perintah yang perlu kita masukkan adalah :
UPDATE Mahasiswa
SET Alamat=’Kelud 17′ Kota=’Semarang’
WHERE Nama=’Tony’ AND Propinsi=’Jawa Tengah’
Maka tabel akan menjadi sbb:
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
1 Bambang Pemuda 123 Bekasi Jawa Barat
2 Agung Sudirman 57 Bandung Jawa Barat
3 Satria Majapahit 57 Solo Jawa Tengah
4 Tony Kelud 17 Semarang Jawa Tengah
Dan apabila kita tidak memasukkan WHERE maka alamat dan kota akan berubah semua dan tabel menjadi
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
1 Bambang Kelud 17 Semarang Jawa Barat
2 Agung Kelud 17 Semarang Jawa Barat
3 Satria Kelud 17 Semarang Jawa Tengah
4 Tony Kelud 17 Semarang Jawa Tengah
Operator LIKE pada MySQL akan kita gunakan pada saat membuat syarat pencarian atau pemilihan sebuah entry atau data sesuai dengan keinginan kita. Jadi operator LIKE akan menempel pada perintah SELECT bersyarat.
Kalau dalam bahasa Indonesia, like atau LIKE mempunyai menyukai atau bisa juga menyerupai. Dalam MySQL, LIKE lebih condong menyerupai dan menyerupai berarti hanya mirip-mirip atau tidak sama persis seperti pada saat kita memilih entry atau data menggunakan WHERE namakolom=’datakolom’.
Misal :
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
1 Bambang Pemuda 123 Bekasi Jawa Barat
2 Agung Sudirman 57 Bandung Jawa Barat
3 Satria Majapahit 57 Solo Jawa Tengah
4 Tony Kelud 17 Semarang Jawa Tengah
Berikut aplikasi penggunaan LIKE pada MySQL :
• Mencari Mahasiswa yang berasal dari kota yang berawalan dengan huruf s. Pencarian kita kunci menggunakan ‘s%’ dimana % mewakili sembarang huruf setelah huruf s.
SELECT * FROM Mahasiswa
WHERE Kota LIKE ‘s%’
Maka hasilnya adalah :
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
3 Satria Majapahit 57 Solo Jawa Tengah
4 Tony Kelud 17 Semarang Jawa Tengah
• Mencari mahasiswa yang berasal dari kota yang berakhiran dengan huruf g. Pencarian kita kunci menggunakan ‘%g’ dimana % mewakili sembarang huruf sebelum huruf g.
SELECT * FROM Mahasiswa
WHERE Kota LIKE ‘%g’
Maka hasilnya adalah:
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
2 Agung Sudirman 57 Bandung Jawa Barat
4 Tony Kelud 17 Semarang Jawa Tengah
• Mencari mahasiswa yang berasal dari kota yang mengandung kata ara. Pencarian kita kunci menggunakan ‘%ara%’ dimana % mewakili sembarang huruf sebelum dan sesudah kata ara.
SELECT * FROM Mahasiswa
WHERE Kota LIKE ‘%ara%’
Maka hasilnya adalah:
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
4 Tony Kelud 17 Semarang Jawa Tengah
• Mencari mahasiswa yang berasal dari kota yang tidak mengandung kata ara. Pencarian kita kunci menggunakan ‘%ara%’ dimana % mewakili sembarang huruf sebelum dan sesudah kata ara.
SELECT * FROM Mahasiswa
WHERE Kota NOT LIKE ‘%ara%’
Maka hasilnya adalah:
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
1 Bambang Pemuda 123 Bekasi Jawa Barat
2 Agung Sudirman 57 Bandung Jawa Barat
3 Satria M
Operator IN pada MySQL merupakan operator yang memungkinkan kita untuk memilih data menggunakan perintah SELECT dimana nilai yang dijadikan sebagai persyaratan dapat lebih dari satu nilai. Jadi operator ini akan melengkapi persyaratan WHERE.
Rumus penggunaannya sbb:
SELECT namakolom
FROM namatabel
WHERE namakolom IN (nilai1,nilai2,…)
Sebagai contoh:

Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
1 Bambang Pemuda 123 Bekasi Jawa Barat
2 Agung Sudirman 57 Bandung Jawa Barat
3 Satria Majapahit 57 Solo Jawa Tengah
4 Tony Kelud 17 Semarang Jawa Tengah
Kita ingin mencari mahasiswa yang berasal dari kota Solo dan Semarang.
SELECT Nama
FROM Mahasiswa
WHERE kota IN (‘Solo’,’Semarang’)
maka hasilnya adalah
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
3 Satria Majapahit 57 Solo Jawa Tengah
4 Tony Kelud
Ada kalanya kita perlu menggabungkan beberapa tabel atau setidaknya 2 tabel yang memiliki kolom yang sama. Untuk menggabungkannya kita dapat menggunakan perintah JOIN yang ada pada MySQL.
Setiap tabel memiliki kunci utama yang unik berbeda dengan nilai data yang lain. Sehingga tidak ada data yang terulang. Dan penggabungan 2 tabel tersebut memanfaatkan kunci utama di masing-masing tabel.
Misal :
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
1 Bambang Pemuda 123 Bekasi Jawa Barat
2 Agung Sudirman 57 Bandung Jawa Barat
3 Satria Majapahit 57 Solo Jawa Tengah
4 Tony Kelud 17 Semarang Jawa Tengah
dan tabel berikutnya :
Tabel Nilaitertinggi
Idpelajaran Pelajaran ID
1 Matematika 2
2 Fisika 2
3 Biologi 4
4 Bahasa 7
Kalau kita perhatikan kedua tabel memiliki kunci utama yaitu ID pada tabel Mahasiswa dan Idpelajaran pada tabel Nilaitertinggi. Tetapi keduanya bisa digabungkan karena ID pada tabel Mahasiswa dan ID pada tabel Nilaitertinggi mengacu pada orang yang sama.
Bila digabungkan dengan perintah berikut :
SELECT Mahasiswa.Nama, Mahasiswa.Kota, Nilaitertinggi.Pelajaran
FROM Mahasiswa
JOIN Nilaitertinggi
ON Mahasiswa.ID=Nilaitertinggi.ID
ORDER BY Mahasiswa.Nama
Maka akan menghasilkan
Nama Kota Pelajaran
Agung Bandung Matematika
Agung Bandung Fisika
Tony Semarang Biologi
Kalau kita perhatikan ada ID no 7, yang tidak terdapat pada Tabel Mahasiswa, tidak ditampilkan pada tabel akhir.
Sebelumnya kita telah mempelajari perintah JOIN pada MySQL dan kali ini dengan arti yang hampir sama tetapi berbeda fungsi yaitu perintah UNION pada MySQL. Perintah Union ini digunakan untuk menggabungkan 2 atau lebih hasil dari perintah SELECT pada MySQL.
Kalau perintah JOIN digunakan untuk menggabungkan apabila ada kolom yang sama sebagai kunci antara tabel 1 dengan yang lainnya. Sedangkan UNION akan menggabungkan semua, sekalipun tidak ada kolom yang dijadikan sebagai kunci. Jadi hasil pemanggilan SELECT beberapa tabel dapat digabungkan menggunakan UNION.
Contoh penggunaanya adalah sebagai berikut:
Tabel Mahasiswa
ID Nama Alamat Kota Propinsi
1 Bambang Pemuda 123 Bekasi Jawa Barat
2 Agung Sudirman 57 Bandung Jawa Barat
3 Satria Majapahit 57 Solo Jawa Tengah
4 Tony Kelud 17 Semarang Jawa Tengah
Kemudian ada tabel baru yang berisikan mahasiswa baru pindahan dari luar kota dengan tabel berikut :
Tabel Mahasiswa Jawa Timur
ID Nama Alamat Kota Propinsi
1 Ridwan Ahmad Yani 50 Surabaya Jawa Timur
2 Adi S Parman 32 Gresik Jawa Timur
Karena keduanya adalah tabel mahasiswa sebuah universitas, maka keduanya harus digabungkan dalam sebuah tabel mahasiswa dengan cara sbb:
SELECT Nama FROM Mahasiswa
UNION
SELECT Nama FROM Mahasiswa Jawa Timur
maka akan menghasilkan :
Bambang
Agung
Satria
Tony
Ridwan
Adi
Dengan perintah UNION, semua nama akan digabung dan apabila ada kesamaan nama, maka hanya akan tertulis satu nama saja. Bila ingin semua nama ditampilkan walaupun mempunyai kesamaan nama, maka kita menggunakan UNION ALL.
Perintah perintah SQL
Nama database : dblatihan
Nama table : latihan
Action Command
Membuat database Create database dblatihan;
Membuat tabel Create table latihan (nama_kolom tipedata(ukuran))
Menggunakan db Use dblatihan;
Memasukkan data Insert into latihan values(‘isi field1’, ‘isi field2’)
Melihat isi table(semua field) Select *from latihan.
Select field1,field2 from latihan (untuk menampilkan field tertentu)
Menampilkan database Show databases;
Menampilkan table Show tables;
Menghapus table Drop table latihan
Delete from nama_table where [kondisi]
Menghapus database Drop database dblatihan
Menambahkan field pada table Alter table latihan add kelamin char(9)
Menambahkan field kelamin pada table
Menghapus field Alter teble latihan drop kelamin ;
Merename field Alter table latihan rename nama_lama to nama_baru;
Mengubah ukuran field Alter table latihan change nama_field tipe_data(ukuran)
Melihat struktur table Desc nama_table;
Mengupdate isi field Update latihan set field1, field2 where [kondisi]
Menggabungkan beberapa field Select field1,field2, field 3 from nama_table
Mengurutkan secara descending Select *from latihan order by desc;
Mengurutkan secara ascending Select *from latihan order by asc;
Membatasi jumlah Limit nilai_limit

Fungsi-fungsi SQL
Nama fungsi Kegunaan
Avg Menghitung nilai rata rata
Count Menghitung cacah data
Max Manghasilkan nilai terbesar
Min Menghasilkan nilai tekecil
Sum Memperoleh penjumlahan data
GROUP BY
Untuk Mengelompokkan data berdasarkan ekspresi group
Syntax : SELECT
FROM
WHERE
GROUP BY ;
Contoh : SELECT a.No_Induk, b.Nama, c.Nl_Angka
FROM Pelajar.a, Nilai b
WHERE a.No_Induk=c.No_Induk and b.kode=c.kode
GROUP BY a.No_Induk, b.Nama, c.Nl_Angka;
ORDER BY
Untuk mengurutkan data hasil seleksi
Syntax : SELECT
FROM
WHERE
ORDER BY [DESC];
Contoh : SELECT * FROM Pelajar
ORDER BY No_Induk;
HAVING
Untuk mendefenisikan batasan seleksi berdasarkan GROUP BY
Syntax : SELECT
FROM
WHERE
GROUP BY
HAVING ;
Contoh : SELECT a.No_Induk, b.Nama, c.Nl_Angka
FROM Pelajar.a, Nilai b
WHERE a.No_Induk=c.No_Induk and b.kode=c.kode
GROUP BY a.No_Induk, b.Nama, c.Nl_Angka
HAVING Nilai>80;
VV.. PREDIKAT COMPARISON
Pembanding dua nilai dengan syarat type data yang dibandingkan harus sama
Sama dengan =
Tidak sama dengan
Lebih kecil
Lebih kecil dan sama dengan >=
Lebih besar dan sama dengan = : untuk lebih besar atau sama dengan,
3. <= : lebih kecil atau sama dengan.
4. : tidak samadengan,
dan banyak lagi operator-operator yang bisa digunakan yang tidak bisa jelaskan disini dan nantinya akan saya jelas pada session tersendiri saja.
INNER JOIN.
perintah ini merupakan untuk merelasikan data antara 2(dua) atau lebih table yang berbeda
contoh :
1. SELECT* FROM products
2. SELECT* FROM categories
3. SELECT SELECT a.productID, a.ProductName, b.CategoryName FROM products a inner join categories b
ON a.categoryID = b.categoryID
perintah 1 : menampilkan isi products
perintah 2 : menampilkan isi categories
perintah 3 : menampilkan isi dari 2(dua) table yang berbeda yang di relasikan berdasarkan field categoryID. Untuk ‘a’ dan ‘b’ diatas merupakan suatu ALIAS untuk meringkas nama table.
Dalam merelasikan table ini ada juga yang menggunakan LEFT JOIN dan RIGHT JOIN yang nantinya juga akan saya jelaskan pada sesion tersendiri saja.
Untuk perintah SELECT saya rasa sudah cukup sampai disini, dan pada pembahasan berikutnya kita akan menggunakan DATABASE bawaannya SQL Server saja untuk meringkas pembahasan.
Berikut ini adalah query untuk mencari nim yang memiliki IPK terendah
SELECT ambilmk.nim
FROM ambilmk, mk
WHERE ambilmk.kodeMK = mk.kodeMK
GROUP BY ambilmk.nim
ORDER BY sum(ambilmk.nilai * mk.sks)/sum(mk.sks) ASC
LIMIT 0, 1;
Secara logika, untuk mencari nim dengan IPK terendah adalah, dengan menampilkan semua nim dan IPK nya terlebih dahulu, lalu mensorting berdasarkan IPK secara ASCENDING. Dengan demikian nim yang ber IPK terendah akan berada pada record pertama (setelah sorting). Lalu ambil record pertama tersebut dengan LIMIT 0, 1. Sehingga berdasarkan logika itu, jadilah query di atas. Tapi hal ini belum menjawab pertanyaan No. 4.
Selanjutnya berdasarkan hasil query untuk mencari nim ber IPK terendah itu, kita gunakan sebagai syarat penghapusan data mahasiswa di tabel mahasiswa.
DELETE FROM mhs
WHERE nim = (
SELECT ambilmk.nim
FROM ambilmk, mk
WHERE ambilmk.kodeMK = mk.kodeMK
GROUP BY ambilmk.nim
ORDER BY sum(ambilmk.nilai * mk.sks)/sum(mk.sks)
LIMIT 0, 1
);
Dari query di atas, mahasiswa yang akan terhapus adalah yang bernim ’002′, dengan IPK 2.4 Kasihan ya… Kejam sekali kebijakannya :’-(
Sekarang akan kita bahas soal No. 5
Pada soal ini, kita diminta menampilkan mahasiswa (nim dan nama) yang memiliki IPK di bawah 3.5.
Untuk menjawab soal ini, logika yang kita gunakan adalah: langkah pertama tampilkan dahulu semua nim dan IPK nya masing-masing (gunakan GROUP BY nim). Lalu persempit filternya dengan menambahkan syarat bahwa yang ditampilkan hanya yang ber IPK < 3.5 (gunakan HAVING).
Berikut ini querynya
SELECT ambilmk.nim,
sum(ambilmk.nilai*mk.sks)/sum(mk.sks) as IPK
FROM ambilmk, mk
WHERE ambilmk.kodeMK = mk.kodeMK
GROUP BY ambilmk.nim
HAVING IPK < 3.5;
Mengapa syarat IPK < 3.5 tidak diletakkan di WHERE? alasannya adalah IPK bukan suatu field tabel, tapi suatu alias untuk kalkulasi yang menggunakan aggregate function sum().
OK… selanjutnya soal No. 6 (the last soal)
Pada soal ini, kita diminta menaikkan semua nilai sebesar 1 point untuk matakuliah ‘A03′. Alasannya sang dosen melakukan kesalahan dalam memberi nilai
Dalam hal ini, sama saja kita mengupdate data nilai dengan syarat hanya pada matakuliah ‘A03′. Sehingga query SQL nya adalah:
UPDATE ambilmk
SET nilai = nilai + 1
WHERE kodeMK = 'A03';
Maksud dari SET nilai = nilai + 1 adalah mengupdate nilai yang baru yaitu nilai yang lama ditambah 1.
Dalam sebuah artikel yang saya publish tentang SQL SELECT untuk multi tabel, ada seorang teman yang berkomentar sekaligus menanyakan seperti ini:
Gmna caranya cari siswa yang blm bayar SPP
printah d SQL-nya gimana??
Perintah SQL untuk membuat tabel ‘SISWA’
CREATE TABLE `siswa` (
`nis` varchar(10),
`namasiswa` varchar(30),
`alamat` varchar(50),
PRIMARY KEY (`nis`)
);
dan berikut ini contoh data siswanya (tabel induk siswa)
INSERT INTO siswa VALUES
('10001', 'ROSIHAN ARI YUANA', 'SOLO'),
('10002', 'DWI AMALIA F', 'KUDUS'),
('10003', 'FAZA FAUZAN', 'SOLO'),
('10004', 'NADA HASANAH', 'SOLO'),
('10005', 'MUH. AHSANI TAQWIM', 'SOLO');
Berikutnya kita buat pula tabel ‘BAYARSPP’
CREATE TABLE `bayarspp` (
`nis` varchar(10),
`tglbyr` date,
`byrbln` int(11),
`byrthn` varchar(4),
PRIMARY KEY (`nis`,`byrbln`,`byrthn`)
);
Field ‘byrbln’ digunakan untuk menyimpan data terkait dengan bulan SPP yang mau dibayarkan. Field ‘byrthn’ terkait dengan tahun SPP yang dibayarkan, sedangkan ‘tglbyr’ menyimpan informasi tanggal berapa si siswa membayar.
Berikut ini contoh data pembayaran SPP siswa
INSERT INTO bayarspp VALUES
('10001', '2008-01-10', 1, '2008'),
('10002', '2008-01-12', 1, '2008'),
('10003', '2008-01-10', 1, '2008'),
('10001', '2008-02-09', 2, '2008'),
('10003', '2008-02-13', 2, '2008');
Untuk record pertama, kita bisa mengetahui bahwa siswa ber NIS ’10001′ telah membayar SPP untuk bulan 1 (Januari) tahun 2008 pada tanggal 10/01/2008. Sedangkan untuk record yang lain, cara membacanya sama.
Sebagai contoh, berikut ini adalah perintah SQL untuk menampilkan NIS dan NAMA SISWA yang belum membayar SPP bulan Januari 2008.
SELECT nis, namasiswa
FROM siswa
WHERE nis NOT IN (SELECT nis FROM bayarspp WHERE byrbln = 1 AND byrthn = '2008');
Dari query SQL di atas, akan tampak NIS dan NAMA SISWA yang belum membayar SPP bulan Januari 2008 yaitu
NIS NAMA SISWA
1004 NADA HASANAH
1005 MUH. AHSANI TAQWIM

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: