1 / 59

DML (Data Manipulation Language)

DML (Data Manipulation Language). Pertemuan Minggu Ke- 9. Kompetensi Khusus. Mahasiswa mampu menggunakan perintah dan fungsi manipulasi data pada database menggunakan MySQL (C3). Aljabar Relasional.

quanda
Download Presentation

DML (Data Manipulation Language)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DML (Data Manipulation Language) Pertemuan Minggu Ke-9

  2. Kompetensi Khusus • Mahasiswa mampu menggunakan perintah dan fungsi manipulasi data pada database menggunakan MySQL (C3)

  3. Aljabar Relasional • Aljabarrelasionalmendefinisikancarateoritisdalammemanipulasiisitabelmenggunakan 8 operator relasionalsbb: • SELECT, atauRESTRICT, menampilkannilaiuntuksemuabaris yang ditemukandalamtabel yang memenuhikondisi yang diberikan (menampilkanbagian horizontal daritabel). • PROJECTmenampilkansemuanilaiuntukatribut yang dipilih (menampilkanbagianvertikaldaritabel). • UNIONmenggabungkansemuabarisdari 2 tabel, termasukbaris yang duplikat. Kolom & domain daritabel yang digabungkanharussama. • INTERSECTmenampilkanhanyabaris yang muncul di keduatabel. • DIFFERENCEmenampilkansemuabarisdalam 1 tabel yang tidakditemukandalamtabel lain. • PRODUCTmenampilkansemuapasanganbaris yang adadari 2 tabel, disebutjugadenganproduk Cartesian.

  4. Contoh SELECT

  5. Contoh PROJECT

  6. Contoh UNION Contoh INTERSECT Contoh DIFFERENCE

  7. Contoh PRODUCT

  8. JOINmenggabungkaninformasidari 2 ataulebihtabel. • Natural join: menghubungkantabeldenganmemilihhanyabarisdengannilai yang samadalamatribut yang sama. Berikut 3 tahap proses natural join yaitu PRODUCT, SELECT, & PROJECT. • Equijoin: menghubungkantabeldengankondisikesetaraan (equal) yang membandingkankolomtertentudaritiaptabel. Menggunakan operator samadengan (=). • Theta join: menghubungkantabelmenggunakan operator lain selainsamadengan(=). • Inner join: hanyamengembalikan record yang sesuaidaritabel yang digabungkan. • Outer join: pasangan yang sesuaiakandipertahankan, & nilai yang tidaksesuaidalamtabel lain akandikosongkan. • Left outer join: menampilkansemuabarisdalamtabelpertama (sebelahkiri), termasuksemuabaris yang tidaksesuaidengannilaidalamtabelkedua (sebelahkanan). • Right outer join: kebalikandari left outer join.

  9. Contoh Tabel untuk Ilustrasi JOIN

  10. Contoh Tahapan Natural Join 1. PRODUCT

  11. 2. SELECT 3. PROJECT

  12. LEFT OUTER JOIN RIGHT OUTER JOIN

  13. DIVIDEmenggunakantabelpertamadengan 2 kolomsebagai yang dibagi & tabelkeduadengan 1 kolomsebagaipembagi. Hasilnyaadalahkolomtunggal yang berisisemuanilaidarikolomkeduadari yang tabelpertama yang berhubungandengantiapbarisdalamtabelkedua.

  14. Perintah Manipulasi Data SQL

  15. Menambah Baris Tabel • SQL menggunakanperintah INSERT untukmemasukkan data kedalamtabel. • BerikutsintaksSQLnya: INSERT INTO tablename VALUES (value1, value2, …, valuen) • Contoh: INSERT INTO VENDOR VALUES (21225, ‘Bryson, Inc.’, ‘Smithson’, ‘615’, ‘223-3234’, ‘TN’, ‘Y’);

  16. Dalam baris entri data di atas, amati bahwa: • Isi baris diinput di antara tanda kurung. Perhatikan bahwa karakter pertama setelah VALUES adalah tanda kurung buka & karakter terakhir adalah tanda kurung tutup. • Nilai karakter (string) & tanggal harus diinput di antara tanda petik satu ( ‘ ). • Nilai numerik tidak perlu ditutup dengan tanda petik satu. • Nilai atribut dipisahkan dengan tanda koma. • Nilai dibutuhkan untuk tiap kolom dalam tabel.

  17. MenambahBarisdenganAtribut NULL • Perhatikanbahwanilai NULL diterimahanyakarenaatributnyaopsional. INSERT INTO PRODUCT VALUES (‘BRT-345’, ‘Titanium drill bit’, ’18-Oct-11’, 75, 10, 4.50, 0.06, NULL); • MenambahBarisdenganAtributOpsional • Satutabelbisamemilikibanyakatributopsional. Daripadaharusmendeklarasikantiapatributsebagai NULL dalamperintah INSERT, user dapatmenyebutkanhanyaatribut yang membutuhkannilaisaja. INSERT INTO PRODUCT (P_CODE, P_DESCRIPT) VALUES (‘BRT-345’, ‘Titanium drill bit’);

  18. Menyimpan Perubahan Tabel • Setiapperubahan yang dilakukanpadaisitabeltidakdisimpandalam disk sampai user menutup database, menutup program yang digunakan, ataumenggunakanperintahCOMMIT. • Jika database dibuka& terjadipemadamanlistrikataubeberapainterupsi lain terjadisebelum user memberikanperintahCOMMIT, makaperubahannyaakanhilang & hanyaisitabelasli yang dipertahankan. • SintaksnyaadalahCOMMIT [WORK] • PerintahCOMMITmenyimpansemuaperubahan – sepertipenambahanbaris, perubahanatribut, & penghapusanbaris – yang dibuatketabeldalam database.

  19. Menampilkan Baris Tabel • PerintahSELECTdigunakanuntukmenampilkanisitabel. Berikutsintaksnya: SELECT columnlist FROM tablename • Columnlistmewakili 1 ataulebihatribut, yang dipisahkandengantandakoma. Contoh: SELECT P_CODE, P_DESCRIPT, P_INDATE FROM PRODUCT; • User dapatmenggunakanasterik( * ) sebagaikarakter wildcard untukmenampilkansemuaatribut. Contoh: SELECT * FROM PRODUCT; • Karakter wildcard adalahsimbol yang dapatdigunakansebagaipenggantiuntukkarakteratauperintah lain.

  20. Mengubah Baris Tabel • Perintah UPDATE digunakanuntukmengubah data dalamtabel. Berikutsintaksnya: UPDATE tablename SET columnname = expression [, columnname = expression] [WHERE conditionlist]; • Contohjika user inginmengubah P_INDATE dari 13 Des 2011 ke 18 Jan 2012 dengan P_CODE 13-Q2/P2: UPDATE PRODUCT SET P_INDATE=’18-JAN-2012’ WHERE P_CODE=‘13-Q2/P2’;

  21. Jikalebihdari 1 atribut yang akandiubah, makagunakantandakomauntukmemisahkanatributnya. Cth: UPDATE PRODUCT SET P_INDATE=’18-JAN-2012’, P_PRICE=17.99, P_MIN=10 WHERE P_CODE=‘13-Q2/P2’; • Ingatuntukmenggunakankondisi WHERE agar perubahantidakdilakukankesemuabarisdalamtabel.

  22. Mengembalikan Isi Tabel • Jika user belum menggunakan perintah COMMIT untuk menyimpan perubahan dalam database, user dapat mengembalikan database ke kondisi sebelumnya menggunakan perintah ROLLBACK. • Untuk menggunakannya cukup ketik: ROLLBACK; • COMMIT & ROLLBACK berdampak hanya pada perintah manipulasi data yang menambah, mengubah, atau menghapus baris tabel. • Semua perintah definisi data (CREATE TABLE) di-COMMIT secara otomatis ke kamus data & tidak dapat di-ROLLBACK.

  23. Menghapus Baris Tabel • Untuk menghapus baris tabel, gunakan perintah DELETE dengan sintaks berikut: DELETE FROM tablename [WHERE conditionlist]; • Contoh jika ingin menghapus produk dengan kode ‘BRT-345’: DELETE FROM PRODUCT WHERE P_CODE=‘BRT-345’; • Kondisi WHERE adalah opsional. Akan tetapi, jika tidak menggunakan kondisi WHERE maka semua baris dari tabel akan terhapus.

  24. Memasukkan Baris Tabel dengan Subquery SELECT • User dapat memasukkan lebih dari 1 baris ke tabel menggunakan sumber data dari tabel lain. Berikut sintaksnya: INSERT INTO tablename SELECT columnlist FROM tablename; • Pernyataan INSERT di atas menggunakan subquery SELECT. • Subquery, disebut juga nested query atau inner query, adalah query yang disisipkan ke dalam query lain. • Inner query selalu dieksekusi terlebih dahulu oleh RDBMS. Output dari inner query akan menjadi input untuk outer query. • Nilai yang dikembalikan oleh subquery SELECT harus sesuai dengan atribut & tipe data dari tabel dalam INSERT.

  25. Query SELECT • SELECT merupakan query yang dapat mengubah data menjadi informasi. • Query SELECT dapat ditambahkan batasan untuk kriteria pencarian. • Berikut akan dibahas beberapa variasi penggunaan query SELECT.

  26. Memilih Baris dengan Kondisi • User dapat memilih sebagian isi tabel dengan membatasi baris yang akan dimasukkan dalam output. Caranya dengan menambahkan WHERE ke pernyataan SELECT: SELECT columnlist FROM tablelist [WHERE conditionlist]; • SELECT akan menampilkan semua baris yang sesuai dengan kondisi yang ditentukan – disebut juga kriteria kondisi – yang disebutkan dalam WHERE. • Conditionlist dalam WHERE diwakilkan oleh 1 atau lebih ekspresi kondisi, dipisahkan dengan operator logis. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE=21344;

  27. Hasil Query dari Tabel PRODUCT untuk Kode Vendor 21344

  28. Operator Perbandingan

  29. Menggunakan Operator Perbandingan pada Atribut Karakter • Karena komputer mengidentifikasi semua karakter menggunakan kode ASCII, maka operator perbandingan dapat digunakan untuk membatasi atribut karakter. • Perbandingan string (karakter) dilakukan dari kiri ke kanan. Contoh: string “Ardmore” akan dinilai lebih besar dari string “Aarenson” tetapi lebih kecil dari string “Brown”. • Jika karakter 0-9 disimpan sebagai string maka akan menyebabkan anomali, misalnya string “5” akan dinilai lebih besar dari “44” karena karakter pertama dari “44” lebih kecil dari “5”. Hal ini juga terjadi jika tanggal disimpan dalam format karakter. • Oleh karena itu, gunakan tipe data yang sesuai dengan nilai yang disimpan oleh atribut.

  30. Menggunakan Operator Perbandingan pada Tanggal • Prosedur tanggal lebih mengacu ke software (software-specific) dibanding prosedur SQL lainnya. • Contoh, query berikut akan menampilkan semua baris dengan tgl stok di atas 20 Jan 2012: SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= ‘20-Jan-2012’; • MS Access menggunakan tanda pagar (#) untuk tanggal. Cth: #20-Jan-12#.

  31. Hasil Query dari Tabel PRODUCT dengan Batasan Tanggal

  32. Menggunakan Kolom Alias • Ketika user ingin menghitung total nilai dari tiap produk di inventori, maka logikanya adalah mengalikan jumlah produk dengan harganya. Berikut perintahnya: SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH * P_PRICE FROM PRODUCT • SQL menerima setiap ekspresi (formula) dalam kolom. Formula dapat berisi operator matematika & fungsi yang diaplikasikan ke atribut dalam tabel yang disebutkan di FROM dari SELECT. • Kolom hasil perhitungan akan diberi label secara otomatis oleh RDBMS. Ms Access memberi label Expr. Oracle menggunakan teks formula sebagai label.

  33. SQL memperbolehkan penggunaan alias untuk tiap kolom dalam SELECT. Alias adalah nama pengganti yang diberikan untuk kolom atau tabel dalam pernyataan SQL. Cth: SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH * P_PRICE AS TOTVALUE FROM PRODUCT; • Misalnya user ingin mendapatkan daftar produk yang telah disimpan lebih dari 90 hari, maka query di Ms Access sbb: SELECT P_CODE, P_INDATE, DATE() – 90 AS CUTDATE FROM PRODUCT WHERE P_INDATE <= DATE() - 90; • DATE() adalah fungsi khusus yang mengembalikan tanggal berjalan dalam Ms Access & dapat digunakan di INSERT, UPDATE, atau SELECT. • Operator perhitungan juga dapat digunakan dengan atribut tanggal, cth manajer menginginkan daftar semua produk yang diterima, tgl diterima, & tgl kadaluwarsa (90 hari setelah diterima): SELECT P_CODE, P_INDATE, P_INDATE + 90 AS EXPDATE FROM PRODUCT;

  34. Operator Hitung: Aturan Prioritas • Ketika melakukan operasi matematika pada atribut, perhatikan aturan prioritas yang menentukan urutan perhitungan sbb: • Operasi dalam tanda kurung. • Operasi pangkat. • Operasi perkalian & pembagian. • Operasi penambahan & pengurangan.

  35. Operator Logis: AND, OR, NOT • SQL memperbolehkan user memasukkanbeberapakondisimelaluipenggunaan operator logis. • Contoh: user inginmenampilkandaftartabeldengan V_CODE = 21344 atau 24288: SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288; • Operator logisANDmemilikikebutuhansintaks SQL yang samadenganOR. • Contoh: user inginmenampilkandaftartabeldengan P_PRICE lebihkecildari $50 & P_INDATE setelah 15 Jan 2012: SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE < 50 AND P_INDATE > ‘15-Jan-2012’;

  36. User dapatmenggabungkan operator OR & AND untukmembatasi output. Contohjikainginmendapatkantabeldengankondisiberikut: • P_INDATE setelah 15 Jan 2012 & P_PRICE lebihkecildari $50. • Atau V_CODE = 24288. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE (P_PRICE < 50 AND P_INDATE > ‘15-Jan-2012’) OR V_CODE = 24288; • Tandakurungdigunakanuntukmenggabungkanbeberapabatasanlogis. Kondisididalamtandakurungselaludieksekusiterlebihdahulu.

  37. Operator logisNOTdigunakanuntukmeniadakanhasildariekspresikondisional. Dalam SQL, semuaekspresikondisionalmenghasilkan true atau false. Jikaekspresinya true, makabarisakanterpilih; sebaliknyajika false, makabarisnyatidakterpilih. • NOTdigunakanuntukmenemukanbaris yang tidaksesuaidengankondisi yang diberikan. Contohjikainginmenampilkansemuabarisdengankodeselain 21344, makaberikutperintahnya: SELECT * FROM PRODUCT WHERE NOT (V_CODE = 21344);

  38. Operator Khusus • SQL memperbolehkanpenggunaanoperasikhusus yang dihubungkandengan WHERE, mencakup: • BETWEEN: untukmemeriksaapakahnilaiatributmasukdalamkisaran. • IS NULL: untukmemeriksaapakahnilaiatributadalah NULL. • LIKE: untukmemeriksaapakahnilaiatributsesuaidenganpola string yang diberikan. • IN: untukmemeriksaapakahnilaiatributsesuaidengansalahsatunilai yang adadalamdaftarnilai. • EXISTS: untukmemeriksaapakahsubquerymengembalikanbaris.

  39. Contoh BETWEEN  tampilkansemuaprodukdenganhargadiantara $50 sampai $100: SELECT * FROM PRODUCT WHERE P_PRICE BETWEEN 50.00 AND 100.00; • Jika DBMS tidakmendukung BETWEEN, gunakanperintahsbb: SELECT * FROM PRODUCT WHERE P_PRICE > 50.00 AND P_PRICE < 100.00; • Contoh IS NULL tampilkansemuaproduk yang tidakmemiliki vendor: SELECT P_CODE, P_DESCRIPT, V_CODE FROM PRODUCT WHERE V_CODE IS NULL;

  40. SQL memperbolehkanpenggunaantandapersen (%) & garisbawah (_) untukmencarikesamaanketikaseluruh string tidakdiketahui: • %berartisalahsatuatausemuakaraktersebelumatausesudah yang memenuhisyarat. Cth: ‘J%’ mencakup Johnson, Jones, July, & J-231Q. ‘%n’ mencakup Johnson & Jernigan. • _berartisetiapsatukarakterdapatmenggantikantandagarisbawah. Cth: ‘_23-456-6789’ mencakup 123-456-6789, 223-456-6789. ‘_o_es’ mencakup Jones, Cones, Cokes, Totes, & Roles. • Contoh LIKE ‘%’  tampilkansemua vendor yang memilikikontakdengannamabelakangdiawalidengan Smith: SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM VENDOR WHERE V_CONTACT LIKE ‘Smith%’; • Perludiingatbahwa SQL melakukanpencarian case sensitive. Hal tsbdikarenakankarakterhurufbesarmemilikikode ASCII yang berbedadengankarakterhurufkecil.

  41. Contoh LIKE ‘_’  jika user lupaapakannamakontak vendor adalah Johnson atauJohnsenmakaberikutquerynya : SELECT * FROM VENDOR WHERE V_CONTACT LIKE ‘Johns_n’; • Contohkombinasi ‘%’ & ‘_’ string ‘_l%’ akanmencakup string “Al”, “Alton”, “Elgin”, “Blakeston”, “blank”, dsb.

  42. PenggunaanORdapatdigantikandenganIN. • Contoh query dengan OR: SELECT * FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288; • Dapatditanganisecaraefisiendengan IN: SELECT * FROM PRODUCT WHERE V_CODE IN (21344, 24288); • Semuanilaidalamdaftarnilaiharusmemilikitipe data yang sama. Jikatipe data karakter yang digunakan, makanilainyaharusdiapitdengantandakutiptunggal. • Operator INbergunakhususnyaketikadigabungkandengansubquery. Contohjikainginmenampilkan V_CODE & V_NAME dari vendor yang hanyamenyediakanproduk: SELECT V_CODE, V_NAME FROM VENDOR WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT);

  43. Operator EXISTSdigunakanketikaterdapatkebutuhanuntukmengeksekusiperintahberdasarkanhasildari query lain, yaitujikasubquerymengembalikanbarismakajalankan query utama. • Contoh EXISTS  tampilkansemua vendor denganproduk yang tersedia di bawah 2x jumlah minimum: SELECT * FROM VENDOR WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_QOH < P_MIN * 2);

  44. Update Data • PerintahUPDATEdigunakanuntukmengubah data yang sudahadadalamtabel. • Contoh UPDATE  mengubah P_SALECODE menjadi ‘2’ dari P_CODE ‘1546-QQ2’: UPDATE PRODUCT SET P_SALECODE = ‘2’ WHERE P_CODE = ‘1546-QQ2’; • Contoh UPDATE menggunakan operator aritmatika menambahkanproduk 2232/QWE sebanyak 20 unit: UPDATE PRODUCT SET P_QOH = P_QOH + 20 WHERE P_CODE = ‘2232/QWE’;

  45. Mengkopi Sebagian Data Tabel • SQL memperbolehkan user untukmengkopiisidarikolomtabel yang dipilihsehinggadatanyatidakperludimasukkansecara manual ketabelbaru. Berikutsintaksnya: INSERT INTO target_tablename [(target_columnlist)] SELECT source_columnlist FROM source_tablename; • Perhatikanbahwadaftarkolom target dibutuhkanjikadaftarkolomsumbertidaksesuaidengannama & karakteristikatributdaritabel target (termasukurutankolomnya). Selainitu, tidakperlumenyebutkandaftarkolom target.

  46. ContohINSERT data ketabelbaru: INSERT INTO PART (PART_CODE, PART_DESCRIPT, PART_PRICE, V_CODE) SELECT P_CODE, P_DESCRIPT, P_PRICE, V_CODE FROM PRODUCT; • Contoh CREATE TABLE berdasarkanbaris & kolomdaritabel yang ada: CREATE TABLE PART AS SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT, P_PRICE AS PART_PRICE, V_CODE FROM PRODUCT; • Perhatikanbahwa PK atau FK tidakakanikutdikopiketabelbaru.

  47. Menghapus Tabel dari Database • PerintahDROP TABLE digunakanuntukmenghapustabeldari database. • Contoh DROP TABLE  menghapustabel PART: DROP TABLE PART; • Jika user menghapustabel yang memiliki FK di tabel lain maka RDBMS akanmenghasilkanpesankesalahanberisipelanggaranintegritas FK.

  48. Kata Kunci Query SELECT Tambahan • Mengurutkan Data • ORDER BY digunakanuntukmengurutkan data tabel. Sintaksnya: SELECT columnlist FROM tablelist [WHERE conditionlist] [ORDER BY columnlist [ASC | DESC] ]; • Jika user tidakmenyebutkanjenisurutannya– menaikataumenurun – makasecara default adalahmenaik. • Contohmengurutkan data secaramenurun: SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE DESC;

More Related