30/05/2016
• Setelah Membuat ERD dan Model Data Relasional, what’s next? • Bagaimana cara membangun entitas dan relationship tersebut agar dapat digunakan?
Sistem Basis Data
SQL Introduction
• Bagaimana cara memasukkan data-data ke dalam tabel? Bagaimana cara mengambil data-data yang sudah dimasukkan tadi? • Jawabannya: gunakan SQL
Danny Kriestanto, S.Kom., M.Eng
Structured Query Language
Kategori Utama Bahasa SQL
• Structured Query Language (SQL) merupakan bahasa komputer standar yang digunakan untuk komunikasi dengan sistem basis data relasional.
• Secara umum, SQL hanya dibagi atas 2 bagian, yakni:
• Standarisasi SQL didefinisikan oleh American National Standard Institute (ANSI) dan International Standard Organization (ISO). • SQL versi terakhir adalah SQL-99
Data Definition Language • DDL memiliki ciri khas tertentu, yaitu perintah-perintah yang menggunakan klausa/statement/pernyataan CREATE, ALTER, atau DROP.
• DML (Data Manipulation Language), yang memperbolehkan proses atau manipulasi obyek basis data. • DDL (Data Definition Language), yang mendukung definisi atau pembuatan dari obyek basis data seperti tabel, indeks, sequence, dan view.
• Meskipun pada kenyataannya, beberapa vendor basis data mempunyai lebih dari dua macam ini. • Catatan: penggunaan SQL dalam kuliah ini menggunakan SQL yang digunakan pada Oracle.
Data Definition Language - CREATE
• Sintaks DDL untuk pembuatan tabel: CREATE TABLE nama_tabel ( nama_kolom1 tipe_data_kolom1 opsi1, nama_kolom2 tipe_data_kolom2 opsi2, … ); • Dengan: • nama_kolom adalah nama dari field yang akan dibuat • Tipe_data_kolom adalah tipe data dari kolom tersebut • Opsi memiliki beberapa kemungkinan, seperti: primary key, not null, dan lain sebagainya. Opsi dapat diletakkan di bawah, setelah menyebutkan semua nama_kolom dan tipe_data.
1
30/05/2016
Tipe Data
Data Definition Language - CREATE
• Setiap DBMS memiliki nama tipe data masingmasing, namun secara umumnya ada beberapa tipe data yang wajib ada. • String : Tipe data yang diapit oleh tanda petik tunggal atau petik ganda. Yang termasuk tipe data string adalah VARCHAR, VARCHAR2, dll. • Numerik : berfungsi untuk menampung nilai numerik, seperti : NUMBER, FLOAT, INTEGER, dll • Tanggal : Tanggal merupakan tipe data krusial yang wajib ada di setiap DBMS. Yang termasuk di antaranya adalah DATE, TIMESTAMP, dll. • BLOB : Binary Large Object, yang digunakan untuk menampung data-data berukuran besar, seperti image, file, dsb. Setiap DBMS memiliki macam-macam tipe BLOB yang berbeda, termasuk ukuran data maksimum yang dapat ditampungnya.
CREATE TABLE nama_tabel ( nama_kolom1 tipe_data_kolom1, nama_kolom2 tipe_data_kolom2, …, PRIMARY KEY (nama_kolom), FOREIGN KEY (nama_kolom) REFERENCES nama_tabel (nama_kolom) );
• Pada beberapa DBMS ada juga tipe data lain seperti Boolean.
• String: contoh: Value
Besaran Penyimpanan Data
CHAR(4)
Storage Required
VARCHAR(4)
Storage Required
''
' '
4 bytes
''
1 byte
'ab'
'ab '
4 bytes
'ab'
3 bytes
'abcd'
'abcd'
4 bytes
'abcd'
5 bytes
'abcdefgh'
'abcd'
4 bytes
'abcd'
5 bytes
Type
• Numerik: (MySQL)
Besaran Penyimpanan Data
• Oracle : CHAR: 1- 2000 byte; VARCHAR/VARCHAR2 : 1 – 4000 byte
TINYINT
Storag Minimum Value Maximum Value e (Signed/Unsigned) (Signed/Unsigned) (Bytes) 1 -128 127
SMALLINT MEDIUMINT INT BIGINT
0
255
-32768
32767
0
65535
-8388608
8388607
0
16777215
4
-2147483648
2147483647
8
0 4294967295 92233720368547758 9223372036854775 07 808
2 3
18446744073709551 615
0
Besaran Penyimpanan Data
• Numerik: (Oracle) Type Bilangan Positif Bilangan Negatif
Storage (Bytes) 1 - 22 byte 1 – 22 byte
Minimum Value (Signed/Unsigned) -1 x 10-130 Sepanjang 38 digit 1x
10-130
sepanjang 38 digit
Maximum Value (Signed/Unsigned) 9,99…99 x 10125 sepanjang 38 digit 9.99...9 x
10125 Sepanjang
38 digit
Besaran Penyimpanan Data
• Tanggal: (MySQL) secara default berukuran 1 - 8 byte Data Type
“Zero” Value
Storage (bytes)
DATE
3
'0000-00-00'
TIME
3
'00:00:00'
DATETIME
8
'0000-00-00 00:00:00'
TIMESTAMP
4
'0000-00-00 00:00:00'
YEAR
1
0000
• Tanggal: (Oracle) Secara default berukuran 7 Byte
2
30/05/2016
Besaran Penyimpanan Data
• BLOB: (MySQL) 216 – 2 byte • BLOB: (Oracle) berukuran standar hingga maksimum 232 – 1 byte atau sekitar 4GB per record • Keterangan: ada berbagai tipe variasi tipe data BLOB yang dapat digunakan sesuai kebutuhan.
Setiap Key yang diciptakan pada tabel memiliki fungsi tersendiri. • FOREIGN KEY Konsep tabel induk dan tabel anak, merujuk pada tabel lain yang memiliki PRIMARY KEY, untuk menjaga integrity constraint.
KEY pada Implementasi Tabel
• INDEX KEY Untuk mempercepat pencarian. Efek samping: memperlambat proses INSERT, UPDATE, dan DELETE • UNIQUE KEY Nilai tiap-tiap record/tupel hanya boleh ada satu. • PRIMARY KEY Berfungsi ganda, yakni sebagai referensi dari FOREIGN KEY, sebagai INDEX KEY, dan juga sebagai UNIQUE KEY.
Integrity Constraint
Data Definition Language - CREATE
• Merupakan batasan-batasan yang diberikan pada skema basis data, tujuannya untuk menjaga konsistensi data.
• Contoh kasus (on board) slide 4 halaman 28 untuk tabel PEGAWAI dan KLIEN • Catatan: Di tabel pegawai masih terdapat tupel nilai ganda, hilangkan redundansinya => primary key.
• Klausa-klausa yang termasuk dalam constraint adalah: • • • • •
NOT NULL : kolom tidak boleh bernilai null UNIQUE : kolom hanya memiliki nilai tunggal PRIMARY KEY : identifikasi unik untuk setiap baris pada tabel FOREIGN KEY : hubungan kolom dengan kolom dari tabel referensi CHECK : memberikan suatu kondisi yang bernilai benar
PEGAWAI No_peg
Data Definition Language CREATE
Nama_peg
KLIEN Id_klien
PEGAWAI_KLIEN Nama_klien
No_peg
Id_klien
E37
Nina
K05
Martini
E37
K05
E38
Tono
K08
Anton
E37
K08
E39
Hadi
K02
Sarmini
E37
K02
K04
Eka
E38
K04
K10
Andin
E38
K10
K06
Mitha
E39
K06
K24
Buyung
E39
K24
K90
Indah
E39
K90
DATA DEFINITION LANGUAGE ALTER
• Alter berfungsi untuk merubah/menambahi/menghapus sesuatu pada obyek yang telah dibuat. • Misalnya: • • • •
menambah kolom memodifikasi kolom memberikan nilai default pada kolom baru menghapus kolom
• Namun operasi tersebut tidak terbatas hanya pada tabel saja.
3
30/05/2016
• Perintah dasar ALTER untuk menambah kolom tabel: ALTER TABLE nama_tabel ADD COLUMN ( nama_kolom tipe_data [DEFAULT ekspresi], nama_kolom tipe_data [DEFAULT ekspresi],
DATA DEFINITION LANGUAGE ALTER
… );
DATA DEFINITION LANGUAGE ALTER
• Perintah ALTER untuk menambah foreign key. ALTER TABLE nama_tabel ADD CONSTRAINT nama_kunci FOREIGN KEY (nama_kolom_tabel_anak) REFERENCES nama_tabel_induk (nama_kolom_tabel_induk);
• Perintah ALTER untuk modifikasi tabel ALTER TABLE nama_tabel MODIFY ( nama_kolom tipe_data [DEFAULT ekspresi] nama_kolom tipe_data [DEFAULT ekspresi] … );
• Perintah ALTER untuk menghapus kolom: ALTER TABLE nama_tabel DROP (nama_kolom);
DATA DEFINITION LANGUAGE ALTER
Ringkasan DDL
• CREATE
• CREATE SCHEMA / CREATE DATABASE • CREATE TABLE • CREATE VIEW
• Merubah nama tabel: RENAME nama_lama TO nama_baru;
• ALTER
• Menghapus tabel: DROP TABLE nama_tabel opsi;
• DROP
• Pada Oracle, opsi terdiri atas perintah CASCADE DELETE dan PURGE
• CONSTRAINT, contoh:
• ALTER SCHEMA / ALTER DATABASE • ALTER TABLE • ALTER VIEW • DROP SCHEMA • DROP TABLE • DROP VIEW • CREATE TABLE … FOREIGN KEY … REFERENCES… • ALTER TABLE … ADD FOREIGN KEY … REFERENCES … • ALTER TABLE … DROP FOREIGN KEY …
• CASCADE DELETE untuk ikut menghapus semua integrity constraint • PURGE untuk menghapus tabel tanpa dapat di-undo (hilang selamanya)
• Pada dasarnya, perintah DML terdiri hanya atas 4 model dasar, yakni: SELECT, INSERT, UPDATE, DELETE.
Data Manipulation Language
• PERINTAH ALTER untuk menambah PRIMARY KEY ALTER TABLE nama_tabel ADD CONSTRAINT nama_kunci PRIMARY KEY (nama_kolom);
• Namun pada Oracle, SELECT tidak termasuk dalam kategori ini, dan digantikan dengan perintah MERGE. • Perintah-perintah ini ini dapat dipautkan dengan perintah dari bahasa pemrograman sehingga dapat dikerjakan lewat event tertentu (penekanan tombol, dsb.)
Data Manipulation Language INSERT
• INSERT merupakan perintah untuk memasukkan data ke dalam tabel. • Sintaks dasarnya: INSERT INTO nama_tabel (nama_kolom1, nama_kolom2, …) VALUES (nilai1, nilai2, …); • Jikalau nama_kolom yang akan di-insert urutannya telah sesuai dengan yang ada pada struktur tabel, nama_kolom tidak perlu disebutkan. Sehingga sintaksnya dapat diperpendek menjadi: INSERT INTO nama_tabel VALUES (nilai1, nilai2, …); • Catatan: nilai yang tipe datanya string harus menggunakan tanda petik tunggal yang mengapit nilai tersebut.
4
30/05/2016
Data Manipulation Language - UPDATE • UPDATE digunakan untuk mengubah record/tupel yang telah di-insert sebelumnya. Jika tanpa menggunakan syarat, semua tupel akan diganti. • Sintaks dasarnya: UPDATE nama_tabel SET nama_kolom = nilai [WHERE nama_kolom operator syarat];
• Delete digunakan untuk menghapus tupel. Jika tanpa menggunakan syarat, semua data dalam tabel tersebut akan dihapus.
Data Manipulation Language DELETE
• Sintaks dasarnya: DELETE FROM nama_tabel [WHERE nama_kolom operator syarat;]
• Ada macam-macam tipe operator: • Single value: =, <. >, <=, >= • Multi value : IN, ALL, ANY
Data Manipulation Language - SELECT
Ekspresi Aritmetika
• Perintah ini digunakan untuk mengambil data dari dalam tabel. • Merupakan perintah yang paling sering digunakan jika dibandingkan perintah-perintah SQL yang lain. • Struktur dasar sintaks SELECT: SELECT [DISTINCT] nama_kolom1, nama_kolom2, … FROM daftar_nama_tabel [WHERE nama_kolom operator syarat [AND/OR nama_kolom2 operator syarat …]] [GROUP BY nama_kolom] [HAVING fungsi_agregasi(nama_kolom) operator syarat] [ORDER BY nama_kolom ASC/DESC];
Operator
Deskripsi
+
Penjumlahan
-
Pengurangan
*
Perkalian
/
Pembagian
Urutan Pengerjaan: *, /, +, -
• Fungsi_agregasi akan dibahas nanti.
Operator Perbandingan
Operator = > >= < <= <> != ^= BETWEEN … AND … IN( himpunan ) LIKE IS NULL
Arti Sama dengan Lebih besar dari Lebih besar atau sama dengan Kurang dari Kurang dari atau sama dengan Tidak sama dengan Tidak sama dengan Tidak sama dengan Berada di antara 2 value Yang cocok dengan salah satu yang terdapat dalam set Yang cocok dengan pola karakter tertentu Jika value-nya merupakan nilai null
Logika Kondisi
Operator AND
Arti Menghasilkan TRUE apabila kedua komponen benar
OR
Menghasilkan TRUE apabila salah satu komponen benar
NOT
Menghasilkan TRUE apabila kondisinya false
5
30/05/2016
Tata Urutan Operator • Dari sekian banyak operator yang telah disebutkan sebelumnya, tata urutan pengerjaannya dapat dilihat pada tabel berikut. Urutan Pengerjaan 1 2 3 4 5 6 7 8
Operator Operator aritmetika Operator penggabungan Operator perbandingan IS [NOT] NULL, LIKE, [NOT] IN [NOT] BETWEEN Kondisi logika NOT Kondisi logika AND Kondisi logika OR
6
