BASIS DATA-RELASI DAN JOIN
1. Relationship
Relationship adalah suatu hubungan antara beberapa entitas. Konsep ini
sangat penting sekali di dalam basis data, di mana memungkinkan entitas-
entitas untuk saling berhubungan satu sama lain.
Di dalam sebuah relationship, primary key memiliki peran penting untuk
mengaitkan entitas. Selain itu, primary key juga digunakan untuk
mendefinisikan batasan keterhubungan.
2. JOIN
Join merupakan salah satu konstruksi dasar dari SQL dan basis data. Join
dapat didefinisikan sebagai kombinasi record dari dua atau lebih tabel di
dalam basis data relasional dan menghasilkan sebuah tabel (temporary)
baru—yang disebut sebagai joined table.
Join dapat diklasifikasikan ke dalam dua jenis: inner dan outer.
a. Inner Join
Inner join pada dasarnya adalah menemukan persimpangan
(intersection) antara dua buah tabel.
Awali setiap aktivitas dengan doa, semoga berkah dan mendapat kemudahan.
Pahami tujuan, dasar teori, dan latihan-latihan praktikum dengan baik dan benar.
Kerjakan tugas-tugas praktikum dengan baik, sabar, dan jujur.
Tanyakan kepada asisten/dosen apabila ada hal-hal yang kurang jelas.
Modul Praktikum Basis Data 24
Teknik Elektro -UM
Sintaks inner join diperlihatkan sebagai berikut:
SELECT A1, A2, ..., An
FROM r1
INNER JOIN r2
ON r1.join_key = r2.join_key
Inner join juga dapat direpresentasikan dalam bentuk implisit.
SELECT A1, A2, ..., An
FROM r1, r2
WHERE r1.key = r2.key
b. Outer Join
Outer join dibagi ke dalam tiga jenis: Left , Right, dan Full outer join.
Left Outer Join
Left outer join (atau left join) mengembalikan semua nilai dari tabel
kiri ditambah dengan nilai dari tabel kanan yang sesuai (atau NULL
jika tidak ada nilai yang sesuai).
Sintaks left outer join diperlihatkan sebagai berikut:
SELECT A1, A2, ..., An
FROM r1
LEFT OUTER JOIN r2
ON r1.join_key = r2.join_key
c. Right Outer Join
Right outer join (atau right join) pada dasarnya sama seperti left join,
namun dalam bentuk terbalik—kanan dan kiri.
Sintaks right outer join diperlihatkan sebagai berikut:
SELECT A1, A2, ..., An
FROM r1
RIGHT OUTER JOIN r2
ON r1.join_key = r2.join_key
d. Full Outer Join
Full outer join (atau full join) pada hakekatnya merupakan kombinasi
dari left dan right join.
Sintaks full outer join diperlihatkan sebagai berikut:
SELECT A1, A2, ..., An
FROM r1
FULL OUTER JOIN r2
ON r1.join_key = r2.join_key
Selain empat jenis join yang utama di atas, masih ada beberapa
variasi join lainnya, seperti CROSS JOIN (cartesian product),
NATURAL JOIN, dan sebagainya. SELF JOIN
Perintah join yang menggabungkan sebuah table dengan table itu sendiri
Contoh :
pres.pres_name AS 'president Name',
vice.pres_name AS 'vice president Name'
FROM admin_pr_vp AS pres
INNER JOIN admin_pr_vp AS vice
ON pres.vice_pres_name = vice.pres_name;
VIEW bentuk representasi data yang dapat dibuat dengan melibatkan data yang ada pada satu atau beberapa table
Contoh
create view vicepres as
select pres_name, yrs_serv, party
from president;
Pemanggilannya
SELECT pres_name, yrs_serv, party FROM vicepres;
ALTER VIEW (mengubah view)
Contoh
ALTER VIEW vicepres AS
SELECT pres_name, yrs_serv from president;
SHOW TABlES (melihat dafar view)
untuk melihat definisi view SHOW CREATE VIEW
Contoh
SHOW CREATE VIEW VICEPRES;
DROP VIEW (menghapus view)
Contoh
DROP VIEW VICEPRES;
SUBQUERY
Yaitu suatu query yang menjadi bagian dari satu query. Digunakan untuk menangani masalah yang kompleks yang mungkin sulit untuk dilakukan hanya dengan sebuah query. Meneyediakan cara alternative untuk melakukan operasi yang membutuhkan join atau union yang rumit.
Contoh mencari president yang berkuasa kurang dari 4 tahun dari tahun 1949 – 1961 dengan database president
SELECT PRESI.PRES_NAME, YEAR_INAUGURATED, YRS_SERV FROM
(SELECT * FROM PRESIDENT WHERE YRS_SERV < 4) AS PRESI INNER JOIN ADMINISTRATION ON PRESI_NAME = ADMINISTRATION.PRES_NAME AND YEAR_INAUGURATED BETWEEN 1849 AND 1961; UNION Yaitu penggabungan dua atau lebih perintah select dalam satu perintah sql Contoh SELECT PRES_NAME,PARTY FROM PRESIDENT UNION SELECT PR_AGE, SP_AGE FROM PRES_MARRIAGE; Contoh Mencari siapa saja yang menjadi kepala Negara amerika serikat SELECT PRES_NAME AS “KEPALA DAN WAKIL” FROM PRESIDENT UNION SELECT VICE_PRES_NAME AS “KEPALA DAN WAKIL” FROM ADMIN_PR_VP; UNION ALL Contoh Mencari siapa saja yang menjadi kepala Negara amerika serikat SELECT PRES_NAME AS “KEPALA DAN WAKIL” FROM PRESIDENT UNION ALL SELECT VICE_PRES_NAME AS “KEPALA DAN WAKIL” FROM ADMIN_PR_VP; USING FUNCTION Berfungsi untuk memanipulasi data LEFT FUNCTION Fungsi untuk menampilkan karakter sesuai dengan jumlah karakter pada query, dihitung dengan posisi awal dari sebelah kiri/karakter awal. Contoh SELECT LEFT (‘NILAI_KARAKTER’,NOMOR_KARAKTER) AS ‘KOLOM_BARU’; RIGHT FUNCTION Fungsin untuk menampilkan karakter sesuai dengan jumlah karakter pada query dihitung dengan posisi awal dari sebelah kanan/karakter paling akhir Contoh SELECT RIGHT (‘NILAI_KARAKTER’,NOMOR_KARAKTER) AS ‘KOLOM_BARU’; SUBSTRING Fungsi untuk menampilkan karakter mulai dari posisi awal hingga jumlah karakter yang terlebih dahulu ditentukan dalam query. Contoh SELECT SUBSTRING(‘NILAI_KARAKTER’,POSISI_AWAL_KARAKTER,JUMLAH_KARAKTER) AS ‘KOLOM_BARU’; RTRIM Fungsi untuk menghapus spasi yang berada di sebelah kanan karakter Contoh SELECT RTRIM(‘NILAI_KARAKTER’) AS ‘KOLOM_BARU’; LTRIM Berfungsi untuk menghapus spasi yang berada di sebelah kiri karakter Contoh SELECT LTRIM(‘NILAI_KARAKTER’) AS ‘KOLOM_BARU’; UPPER Adalah fungsi untuk merubah huruf kecil menjadi huruf besar/capital Contoh SELECT UPPER (‘NILAI_KARAKTER’) AS ‘KOLOM_BARU’; LOWER Adalah fungsi untuk merubah huruf besar/capital menjadi huruf kecil Contoh SELECT LOWER (‘NILAI_KARAKTER’) AS ‘KOLOM_BARU’; CONCAT Fungsi untuk menggabungkan dua/lebih data dari kolom yang berbeda Contoh SELECT KOLOM_1/PRIMARY KEY, KOLOM_2, KOLOM_3, CONCAT (KOLOM_2,’ ’,KOLOM_3) AS ‘KOLOM_BARU’ FROM NAMA_TABLE; COMPOSITE FUNCTIONS Berfungsi karakteristik dengan beberapa tipe data yang dapat di kompilasi menjadi sebuah fungsi gabungan DATA/TIME FUNCTIONS Berfungsi untuk memanipulasi tanggal dan waktu GETDATE Fungsi untuk memasukkan/mendapatkan data waktu dan tanggal pada saat query dimasukkan Contoh SELECT GETDATE() FROM NAMA_TABLE; DATEPART Adalah fungsi untuk menganalisa tanggal secara spesifik Contoh SELECT (DATEPART/MONTH/DAY/WEEK/WEEKDAY,DATEVALUE) FROM NAMA_TABLE; DATEDIFF Adalah fungsi untuk menghitung jumlah hari/minggu/bulan/tahun Contoh SELECT DATEDIFF (DATEPART/DAY/WEEK/MONTH/YEAR,TANGGAL_MULAI,TANGGAL_SELESAI) FROM NAMA_TABLE; NUMERIC FUNCTION/MATEMATICAL FUNCTION Berfungsi untuk memanipulasi data numeric ROUND Berfungsi untuk mengolah data numeric yang berupa bilangan positif dan negative maupun bilangan decimal Contoh SELECT ROUND (NILAI_NUMERIK, JUMLAH_DIGIT_DESIMAL) FROM NAMA_TABLE; RAND Adalah fungsi untuk menampilkan angka secara acak Contoh SELECT RAND (NILAI_RENTANG_ANGKA) AS ‘KOLOM_BARU’ FROM NAMA_TABLE; CONVERTION FUNCTION Fungsi yang secara khussu untuk memanipulasi beberapa tipe data seperti karakter, tanggal/waktu, atau tipe data numeric supaya menjadi data yang diinginkan Contoh SELECT CAST (KONDISI AS TIPE_DATA) FROM NAMA_TABLE; SORTING DATA Adalah metode untuk mengurutkan data pada SQL atau database ASCENDING / ASC Adalah metode pengurutan data dimulai dari nilai terkecil hingga nilai terbesar Contoh SELECT KOLOM_YANG_AKAN_DIURUTKAN FROM NAMA_TABLE ORDER BY KOLOM_YANG _AKAN_DIURUTKAN ASC; DESCENDING / DESC Adalah metode pengurutan data dimulai dari nilai terbesar hingga nilai terkecil Contoh SELECT KOLOM_YANG_AKAN_DIURUTKAN FROM NAMA_TABLE ORDER BY KOLOM_YANG _AKAN_DIURUTKAN DESC; MULTIPLE COLOUMN Adalah metode pengurutan data dengan cara mengurutkan kolom pertama terlebih dahulu dan kemudian mengurutkan data pada kolom berikutnya Contoh SELECT KOLOM1,KOLOM2 FROM NAMA_TABLE ORDER BY KOLOM_PERTAMA, KOLOM_BERIKUTNYA; CALCULATED FIELD Adalah metode pengurutan data dengan cara menggabungkan nilai 2 buah kolom atau lebih menjadi 1 kolom baru Contoh SELECT KOLOM1+’,’+KOLOM2 AS ‘NAMA_KOLOM_BARU’ FROM NAMA_TABLE ORDER BY NAMA_KOLOM BARU; SEQUENCES Adalah metode pengurutan data apabila semua data yang akan diurutkan bertipe data numeric (misalnya:integer) maka akan diurutkan secara ascending, tetapi bila terdapat beberapa tipe data, maka akan diurutkan secara berkelompok sesuai tipe datanya masing-masing. Contoh SELECT NAMA_KOLOM FROM NAMA_TABLE ORDER BY NAMA_KOLOM; COLOUMN BASED LOGIC Merupakan penggunaan logika if-then-else pada database IF KONDISI BENAR THEN LAKUKAN LANGKAH PERTAMA ELSE LAKUKAN LANGKAH KEDUA Contoh SELECT KOLOM WHEN NILAI1 THEN HASIL1 WHEN NILAI2 THEN HASIL2 ELSE HASIL3 END AS ‘KOLOM_BARU1’,KOLOM2 AS ‘KOLOM_BARU2’ FROM NAMA_TABLE; ELIMINATING DUPLICATES Menghilangkan duplikat DISTINCT Contoh SELECT DISTINCT NAMA_KOLOM FRO NAMA_TABLE; AGGREGATE FUNCTION Digunakan untuk mengoperasikan suatu data dari hasil query SUM Digunakan untuk mendapatkan nilai total dari suatu kolom dalam suatu table Contoh SELECT SUM (NAMA_KOLOM) FROM NAMA_TABLE; AVG Digunakan untuk mencari rata-rata nilai dalam suatu kolom dari suatu table Contoh SELECT AVG(NAMA_KOLOM) FROM NAMA_TABLE; MIN Digunakan untuk mencari nilai terkecil dari suatu kolom dalam suatu table Contoh SELECT MIN(NAMA_KOLOM) FROM NAMA_TABLE; MAX Digunakan untuk mencari nilai terbesar dari suatu kolom dalam suatu table Contoh SELECT MAC (NAMA_KOLOM) FROM NAMA_TABLE; COUNT Digunakan untuk menghitung jumlah record/baris dari suatu kolom atau suatu table Contoh SELECT COUNT(NAMA_KOLOM) FROM NAMA_TABLE; SELECT COUNT(1) FROM NAMA_TABLE; Menggunakan distict dalam fungsi count untuk menghitung record yang sudah di hilangkan duplikatnya Contoh COUNT(DISTINCT NAMA_KOLOM) FORM NAMA_TABLE; Menggunakan grup by dalam fungsi count untuk menghitung jumlah data table yang sama Contoh SELECT NAMA_KOLOM,COUNT(1)FROM NAMA_TABLE GROUP BY NAMA_KOLOM; GROUPING DATA Digunakan untuk mengelompokkan data berdasarkan atribut tertentu dengan sesuai dan berurut Contoh SELECT KOLOM1,KOLOM2 FROM NAMA_TABLE GROUP BY (KOLOM1,KOLOM2); MULTIPLE COLOUMN AND SORTING Mengurutkan lebih dari satu field menggunakan kata kunci gabungan dari group by, having dan order by Contoh SELECT NAMA_KOLOM1,NAMA_KOLOM2,(NAMA_ KOLOM3)//MANIPULASI//FROM NAMA_TABLE GROUP BY NAMA_KOLOM1,NAMA_KOLOM2 HAVING//MANIPULASI//>NILAI ORDER BY NAMA_KOLOM1,NAMA_KOLOM2;
SELECTION CRITERIA ON AGGREGATE
Menggunakan kata kunci where, group by dan order by
Contoh
SELECT NAMA_KOLOM1, SUM(NAMA_KOLOM2) FROM NAMA_TABLE WHERE NAMA_KOLOM3 > NILAI GROUP BY NAMA_KOLOM1 ORDER BY NAMA_KOLOM1;
INNER JOIN
Adalah sintak dalam Mysql yg digunakan untuk menggabungkan 2 table menjadi satu table baru
Contoh
SELECT * FROM ADMINISTRATION INNER JOIN PRES_HOBY ON ADMINISTRATION.PRES_NAME=PRES_HOBBY.PRES_NAME;
Contoh lain
SELECT FROM ADMINISTRATION,PRES_HOBBY WHERE ADMINISTRATION.PRES_NAME=PRES_HOBBY.PRESS_NAME;
OUTER JOIN
Adalah kata kunci yang digunakan untuk menggabungkan dan menyesuaikan 2 table atau lebih yang berbeda jumlah record
LEFT JOIN
adalah outer join yang tampilannya berurutan dari kiri ke kanan
Contoh
Select
Admin_pr_vp.pres_name as “Nama Presiden”,
Admin_pr_vp.vice_pres_name as “Nama Wakil Presiden”,
Administration.year_inaugurated as “Tahun Inagurasi”,
Pres_hobby.hobby as “hobi”
From admin_pr_vp
Left join administration
On admin_pr_vp.admin_nr = administration.admin_nr
Left join pres_hobby
On administration.pres_name = pres_hobby.pres_name
Order by admin_pr_vp.admin_nr, administration.admin_nr, pres_hobby.pres_name;
Catatan LEFT JOIN
Dari penulisan sintak mysql ini dapat dilihat nama table yang ditulisakan sebelum LEFT JOIN akan menjadi primary table sedangkan nama table yang ditulis setelah sintak LEFT JOIN akan menjadi secondary table
Ketika penggabungan antara primary table dan secondary table dilakakan maka akan ditampilkan seluruh baris(record) dari primary table walaupun jumlah baris tidak sesuai dengan secondary table
Contoh lain
Select
Admin_pr_vp.pres_name as “Nama Presiden”,
Admin_pr_vp.vice_pres_name as “Nama Wakil Presiden”,
Administration.year_inaugurated as “Tahun Inagurasi”,
Pres_hobby.hobby as “Hobi”
From admin_pr_vp
Left join administration
On admin_pr_vp.admin_nr = administration.admin_nr
Left join pres_hobby
On administration.pres_name = pres_hobby.pres_name
Where administration.admin_nr is not null
And pres_hobby.pres_name is null
Order by admin_pr_vp.admin_nr, administration.admin_nr, pres_hobby.pres_name;
RIGHT JOIN
Primary table terletak di sebelah kanan kata kunci right join dan secondary table terletak di sebelah kiri right join
Contoh
Select
Admin_pr_vp.pres_name as “Nama Presiden”,
Admin_pr_vp.vice_pres_name as “Nama Wakil Presiden”,
Administration.year_inaugurated as “Tahun Inagurasi”,
Pres_hobby.hobby as “Hobi”
From pres_hobby
RIGHT JOIN administration
On administration.pres_name = pres_hobby.pres_name
RIGHT JOIN admin_pr_vp
On admin_pr_vp.admin_nr = administration.admin_nr
Where administration.admin_nr is not null
And pres_hobby.pres_name is null
Order by admin_pr_vp.admin_nr, administration.admin_nr, pres_hobby.pres_name;
CONTOH LEFT JOIN DAN RIGHT JOIN BERSAMAAN
Select
Admin_pr_vp.pres_name as “Nama Presiden”,
Admin_pr_vp.vice_pres_name as “Nama Wakil Presiden”,
Administration.year_inaugurated as “Tahun Inagurasi”,
Pres_hobby.hobby as “Hobi”
From admin_pr_vp
LEFT JOIN (pres_hobby
RIGHT JOIN administration
On administration.pres_name = pres_hobby.pres_name)
On admin_pr_vp.admin_nr = administration.admin_nr
Where administration.admin_nr is not null
And pres_hobby.pres_name is null
Order by admin_pr_vp.admin_nr, administration.admin_nr, pres_hobby.pres_name;
FULL JOIN
Digunakan untuk menggabungkan dua table dan meampilkan seluruh isi table walaupun terdapat data-data yang saling berkaitan diantara kedua table tersebut
STORED PROCEDURES AND PARAMETERS
Merupakan kumpulan perintah atau statement yang disimpan dan di eksekusi di server database mysql
Lebih cepat
Menghilangkan duplikasi proses, pemeliharaan yang mudah
Meningkatkan keamanan database
Contoh
Create function uch (x char(10)) returns char(20) return concat(‘permisi,’,x,’!’);
Pemanggilannya menggunakan
Select uch (‘mantab gan’);
STORED PROCEDURE
Delimiter$$
Create procedure nama_spnya()
Begin
Select * from president;
End$$
Delimiter;
Pemanggilannya menggunakan
Call uch;
DROP STORED FUNCTION
DROP FUNCTION NAMA_FUNCTIONNYA;
DROP STORED PROCEDURE
DROP PROCEDURE NAMA_PROCEDURENYA;
INSERT INTO
INSERT INTO PRESIDENT VALUES
('Carter J E','1924','4','NULL','Democratic','Georgia') ; INSERT INTO PRESIDENT VALUES
('Reagan R','1911','3','NULL','Republican','Illinois') ;
DELETE TABLE
Delete from PRESIDENT where PRES_NAME
=‘Reagan R’;
Delete from PRESIDENT where PRES_NAME
=‘Carter J E’;
TRUNCATE
TRUNCATE PRESIDENT;
PERBEDAAN DELETE DAN TRUNCATE
Delete Termasuk DML (Data Manipulasi Language) Bisa di RollBack Proses nya lebih lambat Query dapat digunakan pada table yang menjadi referensi Forign Key Tidak bisa melakukan reset Identity pada table Bisa mengaktifkan trigger
Truncate Termasuk DDL(Data Definition Language) Bisa di RollBack(Digital Forensic) Proses lebih cepat Query hanya menghapus record dalam table nya saja tanpa menghapus struktur nya Melakukan reset Identity pada table Tidak dapat mengaktifkan trigger
UPDATE
Update PRESIDENT set
PRES_NAME=‘Luna Maya’
where PRES_NAME=‘Reagan R’;
PRIMARY KEY
Satu atau beberapa kolom pada table yang mengidentifikasi tiap kolom dan baris pada table tersebut
INDEXES ATAU PENGINDEXAN
Digunakan untuk melakukan pencarian data per baris dengan nilai yang spesifik secara cepat pada tiap table nya
Kegunaan Index Melakukan pencarian cepat, dengan menambahkan syarat di query dengan WHERE Mendapatkan Record dari table lain ketika melakukan join antar table Melakukan pencarian nilai maksimal atau nilai minimal dari kolom index yang spesial
FOREIGN KEY
Satu atau beberapa kolom pada table yang merupakan primary key pada table satu namun diletakan pada table dimana tablenya berelasi dengan table dirinya
CREATE TABLE
Create table presiden(
Pres_name char(16),
Birth_yr smallint,
Yrs_serv smallint,
Death_age smallint,
Party char(11),
State_born char(15));
DROP TABLE
DROP TABLE PRESIDENT;
INDEX
Create index index1 on
PRESIDENT (PRES_NAME(16));
Pemanggilannya dengan mengetikkan
SHOW INDEX FROM PRESIDENT;
MULTI INDEX
Create index index1 on PRESIDENT
(PRES_NAME(16),Party(11));
Pemanggilannya dengan mengetikkan
Show index from PRESIDENT;
DROP INDEX
DROP INDEX1 ON PRESIDENT;
Catatan penting !!!
Kelompok yang TIDAK mengumpulkan PPT nya ke ane untuk diringkas adalah kelompok
1. Relationship
Relationship adalah suatu hubungan antara beberapa entitas. Konsep ini
sangat penting sekali di dalam basis data, di mana memungkinkan entitas-
entitas untuk saling berhubungan satu sama lain.
Di dalam sebuah relationship, primary key memiliki peran penting untuk
mengaitkan entitas. Selain itu, primary key juga digunakan untuk
mendefinisikan batasan keterhubungan.
2. JOIN
Join merupakan salah satu konstruksi dasar dari SQL dan basis data. Join
dapat didefinisikan sebagai kombinasi record dari dua atau lebih tabel di
dalam basis data relasional dan menghasilkan sebuah tabel (temporary)
baru—yang disebut sebagai joined table.
Join dapat diklasifikasikan ke dalam dua jenis: inner dan outer.
a. Inner Join
Inner join pada dasarnya adalah menemukan persimpangan
(intersection) antara dua buah tabel.
Awali setiap aktivitas dengan doa, semoga berkah dan mendapat kemudahan.
Pahami tujuan, dasar teori, dan latihan-latihan praktikum dengan baik dan benar.
Kerjakan tugas-tugas praktikum dengan baik, sabar, dan jujur.
Tanyakan kepada asisten/dosen apabila ada hal-hal yang kurang jelas.
Modul Praktikum Basis Data 24
Teknik Elektro -UM
Sintaks inner join diperlihatkan sebagai berikut:
SELECT A1, A2, ..., An
FROM r1
INNER JOIN r2
ON r1.join_key = r2.join_key
Inner join juga dapat direpresentasikan dalam bentuk implisit.
SELECT A1, A2, ..., An
FROM r1, r2
WHERE r1.key = r2.key
b. Outer Join
Outer join dibagi ke dalam tiga jenis: Left , Right, dan Full outer join.
Left Outer Join
Left outer join (atau left join) mengembalikan semua nilai dari tabel
kiri ditambah dengan nilai dari tabel kanan yang sesuai (atau NULL
jika tidak ada nilai yang sesuai).
Sintaks left outer join diperlihatkan sebagai berikut:
SELECT A1, A2, ..., An
FROM r1
LEFT OUTER JOIN r2
ON r1.join_key = r2.join_key
c. Right Outer Join
Right outer join (atau right join) pada dasarnya sama seperti left join,
namun dalam bentuk terbalik—kanan dan kiri.
Sintaks right outer join diperlihatkan sebagai berikut:
SELECT A1, A2, ..., An
FROM r1
RIGHT OUTER JOIN r2
ON r1.join_key = r2.join_key
d. Full Outer Join
Full outer join (atau full join) pada hakekatnya merupakan kombinasi
dari left dan right join.
Sintaks full outer join diperlihatkan sebagai berikut:
SELECT A1, A2, ..., An
FROM r1
FULL OUTER JOIN r2
ON r1.join_key = r2.join_key
Selain empat jenis join yang utama di atas, masih ada beberapa
variasi join lainnya, seperti CROSS JOIN (cartesian product),
NATURAL JOIN, dan sebagainya. SELF JOIN
Perintah join yang menggabungkan sebuah table dengan table itu sendiri
Contoh :
pres.pres_name AS 'president Name',
vice.pres_name AS 'vice president Name'
FROM admin_pr_vp AS pres
INNER JOIN admin_pr_vp AS vice
ON pres.vice_pres_name = vice.pres_name;
VIEW bentuk representasi data yang dapat dibuat dengan melibatkan data yang ada pada satu atau beberapa table
Contoh
create view vicepres as
select pres_name, yrs_serv, party
from president;
Pemanggilannya
SELECT pres_name, yrs_serv, party FROM vicepres;
ALTER VIEW (mengubah view)
Contoh
ALTER VIEW vicepres AS
SELECT pres_name, yrs_serv from president;
SHOW TABlES (melihat dafar view)
untuk melihat definisi view SHOW CREATE VIEW
Contoh
SHOW CREATE VIEW VICEPRES;
DROP VIEW (menghapus view)
Contoh
DROP VIEW VICEPRES;
SUBQUERY
Yaitu suatu query yang menjadi bagian dari satu query. Digunakan untuk menangani masalah yang kompleks yang mungkin sulit untuk dilakukan hanya dengan sebuah query. Meneyediakan cara alternative untuk melakukan operasi yang membutuhkan join atau union yang rumit.
Contoh mencari president yang berkuasa kurang dari 4 tahun dari tahun 1949 – 1961 dengan database president
SELECT PRESI.PRES_NAME, YEAR_INAUGURATED, YRS_SERV FROM
(SELECT * FROM PRESIDENT WHERE YRS_SERV < 4) AS PRESI INNER JOIN ADMINISTRATION ON PRESI_NAME = ADMINISTRATION.PRES_NAME AND YEAR_INAUGURATED BETWEEN 1849 AND 1961; UNION Yaitu penggabungan dua atau lebih perintah select dalam satu perintah sql Contoh SELECT PRES_NAME,PARTY FROM PRESIDENT UNION SELECT PR_AGE, SP_AGE FROM PRES_MARRIAGE; Contoh Mencari siapa saja yang menjadi kepala Negara amerika serikat SELECT PRES_NAME AS “KEPALA DAN WAKIL” FROM PRESIDENT UNION SELECT VICE_PRES_NAME AS “KEPALA DAN WAKIL” FROM ADMIN_PR_VP; UNION ALL Contoh Mencari siapa saja yang menjadi kepala Negara amerika serikat SELECT PRES_NAME AS “KEPALA DAN WAKIL” FROM PRESIDENT UNION ALL SELECT VICE_PRES_NAME AS “KEPALA DAN WAKIL” FROM ADMIN_PR_VP; USING FUNCTION Berfungsi untuk memanipulasi data LEFT FUNCTION Fungsi untuk menampilkan karakter sesuai dengan jumlah karakter pada query, dihitung dengan posisi awal dari sebelah kiri/karakter awal. Contoh SELECT LEFT (‘NILAI_KARAKTER’,NOMOR_KARAKTER) AS ‘KOLOM_BARU’; RIGHT FUNCTION Fungsin untuk menampilkan karakter sesuai dengan jumlah karakter pada query dihitung dengan posisi awal dari sebelah kanan/karakter paling akhir Contoh SELECT RIGHT (‘NILAI_KARAKTER’,NOMOR_KARAKTER) AS ‘KOLOM_BARU’; SUBSTRING Fungsi untuk menampilkan karakter mulai dari posisi awal hingga jumlah karakter yang terlebih dahulu ditentukan dalam query. Contoh SELECT SUBSTRING(‘NILAI_KARAKTER’,POSISI_AWAL_KARAKTER,JUMLAH_KARAKTER) AS ‘KOLOM_BARU’; RTRIM Fungsi untuk menghapus spasi yang berada di sebelah kanan karakter Contoh SELECT RTRIM(‘NILAI_KARAKTER’) AS ‘KOLOM_BARU’; LTRIM Berfungsi untuk menghapus spasi yang berada di sebelah kiri karakter Contoh SELECT LTRIM(‘NILAI_KARAKTER’) AS ‘KOLOM_BARU’; UPPER Adalah fungsi untuk merubah huruf kecil menjadi huruf besar/capital Contoh SELECT UPPER (‘NILAI_KARAKTER’) AS ‘KOLOM_BARU’; LOWER Adalah fungsi untuk merubah huruf besar/capital menjadi huruf kecil Contoh SELECT LOWER (‘NILAI_KARAKTER’) AS ‘KOLOM_BARU’; CONCAT Fungsi untuk menggabungkan dua/lebih data dari kolom yang berbeda Contoh SELECT KOLOM_1/PRIMARY KEY, KOLOM_2, KOLOM_3, CONCAT (KOLOM_2,’ ’,KOLOM_3) AS ‘KOLOM_BARU’ FROM NAMA_TABLE; COMPOSITE FUNCTIONS Berfungsi karakteristik dengan beberapa tipe data yang dapat di kompilasi menjadi sebuah fungsi gabungan DATA/TIME FUNCTIONS Berfungsi untuk memanipulasi tanggal dan waktu GETDATE Fungsi untuk memasukkan/mendapatkan data waktu dan tanggal pada saat query dimasukkan Contoh SELECT GETDATE() FROM NAMA_TABLE; DATEPART Adalah fungsi untuk menganalisa tanggal secara spesifik Contoh SELECT (DATEPART/MONTH/DAY/WEEK/WEEKDAY,DATEVALUE) FROM NAMA_TABLE; DATEDIFF Adalah fungsi untuk menghitung jumlah hari/minggu/bulan/tahun Contoh SELECT DATEDIFF (DATEPART/DAY/WEEK/MONTH/YEAR,TANGGAL_MULAI,TANGGAL_SELESAI) FROM NAMA_TABLE; NUMERIC FUNCTION/MATEMATICAL FUNCTION Berfungsi untuk memanipulasi data numeric ROUND Berfungsi untuk mengolah data numeric yang berupa bilangan positif dan negative maupun bilangan decimal Contoh SELECT ROUND (NILAI_NUMERIK, JUMLAH_DIGIT_DESIMAL) FROM NAMA_TABLE; RAND Adalah fungsi untuk menampilkan angka secara acak Contoh SELECT RAND (NILAI_RENTANG_ANGKA) AS ‘KOLOM_BARU’ FROM NAMA_TABLE; CONVERTION FUNCTION Fungsi yang secara khussu untuk memanipulasi beberapa tipe data seperti karakter, tanggal/waktu, atau tipe data numeric supaya menjadi data yang diinginkan Contoh SELECT CAST (KONDISI AS TIPE_DATA) FROM NAMA_TABLE; SORTING DATA Adalah metode untuk mengurutkan data pada SQL atau database ASCENDING / ASC Adalah metode pengurutan data dimulai dari nilai terkecil hingga nilai terbesar Contoh SELECT KOLOM_YANG_AKAN_DIURUTKAN FROM NAMA_TABLE ORDER BY KOLOM_YANG _AKAN_DIURUTKAN ASC; DESCENDING / DESC Adalah metode pengurutan data dimulai dari nilai terbesar hingga nilai terkecil Contoh SELECT KOLOM_YANG_AKAN_DIURUTKAN FROM NAMA_TABLE ORDER BY KOLOM_YANG _AKAN_DIURUTKAN DESC; MULTIPLE COLOUMN Adalah metode pengurutan data dengan cara mengurutkan kolom pertama terlebih dahulu dan kemudian mengurutkan data pada kolom berikutnya Contoh SELECT KOLOM1,KOLOM2 FROM NAMA_TABLE ORDER BY KOLOM_PERTAMA, KOLOM_BERIKUTNYA; CALCULATED FIELD Adalah metode pengurutan data dengan cara menggabungkan nilai 2 buah kolom atau lebih menjadi 1 kolom baru Contoh SELECT KOLOM1+’,’+KOLOM2 AS ‘NAMA_KOLOM_BARU’ FROM NAMA_TABLE ORDER BY NAMA_KOLOM BARU; SEQUENCES Adalah metode pengurutan data apabila semua data yang akan diurutkan bertipe data numeric (misalnya:integer) maka akan diurutkan secara ascending, tetapi bila terdapat beberapa tipe data, maka akan diurutkan secara berkelompok sesuai tipe datanya masing-masing. Contoh SELECT NAMA_KOLOM FROM NAMA_TABLE ORDER BY NAMA_KOLOM; COLOUMN BASED LOGIC Merupakan penggunaan logika if-then-else pada database IF KONDISI BENAR THEN LAKUKAN LANGKAH PERTAMA ELSE LAKUKAN LANGKAH KEDUA Contoh SELECT KOLOM WHEN NILAI1 THEN HASIL1 WHEN NILAI2 THEN HASIL2 ELSE HASIL3 END AS ‘KOLOM_BARU1’,KOLOM2 AS ‘KOLOM_BARU2’ FROM NAMA_TABLE; ELIMINATING DUPLICATES Menghilangkan duplikat DISTINCT Contoh SELECT DISTINCT NAMA_KOLOM FRO NAMA_TABLE; AGGREGATE FUNCTION Digunakan untuk mengoperasikan suatu data dari hasil query SUM Digunakan untuk mendapatkan nilai total dari suatu kolom dalam suatu table Contoh SELECT SUM (NAMA_KOLOM) FROM NAMA_TABLE; AVG Digunakan untuk mencari rata-rata nilai dalam suatu kolom dari suatu table Contoh SELECT AVG(NAMA_KOLOM) FROM NAMA_TABLE; MIN Digunakan untuk mencari nilai terkecil dari suatu kolom dalam suatu table Contoh SELECT MIN(NAMA_KOLOM) FROM NAMA_TABLE; MAX Digunakan untuk mencari nilai terbesar dari suatu kolom dalam suatu table Contoh SELECT MAC (NAMA_KOLOM) FROM NAMA_TABLE; COUNT Digunakan untuk menghitung jumlah record/baris dari suatu kolom atau suatu table Contoh SELECT COUNT(NAMA_KOLOM) FROM NAMA_TABLE; SELECT COUNT(1) FROM NAMA_TABLE; Menggunakan distict dalam fungsi count untuk menghitung record yang sudah di hilangkan duplikatnya Contoh COUNT(DISTINCT NAMA_KOLOM) FORM NAMA_TABLE; Menggunakan grup by dalam fungsi count untuk menghitung jumlah data table yang sama Contoh SELECT NAMA_KOLOM,COUNT(1)FROM NAMA_TABLE GROUP BY NAMA_KOLOM; GROUPING DATA Digunakan untuk mengelompokkan data berdasarkan atribut tertentu dengan sesuai dan berurut Contoh SELECT KOLOM1,KOLOM2 FROM NAMA_TABLE GROUP BY (KOLOM1,KOLOM2); MULTIPLE COLOUMN AND SORTING Mengurutkan lebih dari satu field menggunakan kata kunci gabungan dari group by, having dan order by Contoh SELECT NAMA_KOLOM1,NAMA_KOLOM2,(NAMA_ KOLOM3)//MANIPULASI//FROM NAMA_TABLE GROUP BY NAMA_KOLOM1,NAMA_KOLOM2 HAVING//MANIPULASI//>NILAI ORDER BY NAMA_KOLOM1,NAMA_KOLOM2;
SELECTION CRITERIA ON AGGREGATE
Menggunakan kata kunci where, group by dan order by
Contoh
SELECT NAMA_KOLOM1, SUM(NAMA_KOLOM2) FROM NAMA_TABLE WHERE NAMA_KOLOM3 > NILAI GROUP BY NAMA_KOLOM1 ORDER BY NAMA_KOLOM1;
INNER JOIN
Adalah sintak dalam Mysql yg digunakan untuk menggabungkan 2 table menjadi satu table baru
Contoh
SELECT * FROM ADMINISTRATION INNER JOIN PRES_HOBY ON ADMINISTRATION.PRES_NAME=PRES_HOBBY.PRES_NAME;
Contoh lain
SELECT FROM ADMINISTRATION,PRES_HOBBY WHERE ADMINISTRATION.PRES_NAME=PRES_HOBBY.PRESS_NAME;
OUTER JOIN
Adalah kata kunci yang digunakan untuk menggabungkan dan menyesuaikan 2 table atau lebih yang berbeda jumlah record
LEFT JOIN
adalah outer join yang tampilannya berurutan dari kiri ke kanan
Contoh
Select
Admin_pr_vp.pres_name as “Nama Presiden”,
Admin_pr_vp.vice_pres_name as “Nama Wakil Presiden”,
Administration.year_inaugurated as “Tahun Inagurasi”,
Pres_hobby.hobby as “hobi”
From admin_pr_vp
Left join administration
On admin_pr_vp.admin_nr = administration.admin_nr
Left join pres_hobby
On administration.pres_name = pres_hobby.pres_name
Order by admin_pr_vp.admin_nr, administration.admin_nr, pres_hobby.pres_name;
Catatan LEFT JOIN
Dari penulisan sintak mysql ini dapat dilihat nama table yang ditulisakan sebelum LEFT JOIN akan menjadi primary table sedangkan nama table yang ditulis setelah sintak LEFT JOIN akan menjadi secondary table
Ketika penggabungan antara primary table dan secondary table dilakakan maka akan ditampilkan seluruh baris(record) dari primary table walaupun jumlah baris tidak sesuai dengan secondary table
Contoh lain
Select
Admin_pr_vp.pres_name as “Nama Presiden”,
Admin_pr_vp.vice_pres_name as “Nama Wakil Presiden”,
Administration.year_inaugurated as “Tahun Inagurasi”,
Pres_hobby.hobby as “Hobi”
From admin_pr_vp
Left join administration
On admin_pr_vp.admin_nr = administration.admin_nr
Left join pres_hobby
On administration.pres_name = pres_hobby.pres_name
Where administration.admin_nr is not null
And pres_hobby.pres_name is null
Order by admin_pr_vp.admin_nr, administration.admin_nr, pres_hobby.pres_name;
RIGHT JOIN
Primary table terletak di sebelah kanan kata kunci right join dan secondary table terletak di sebelah kiri right join
Contoh
Select
Admin_pr_vp.pres_name as “Nama Presiden”,
Admin_pr_vp.vice_pres_name as “Nama Wakil Presiden”,
Administration.year_inaugurated as “Tahun Inagurasi”,
Pres_hobby.hobby as “Hobi”
From pres_hobby
RIGHT JOIN administration
On administration.pres_name = pres_hobby.pres_name
RIGHT JOIN admin_pr_vp
On admin_pr_vp.admin_nr = administration.admin_nr
Where administration.admin_nr is not null
And pres_hobby.pres_name is null
Order by admin_pr_vp.admin_nr, administration.admin_nr, pres_hobby.pres_name;
CONTOH LEFT JOIN DAN RIGHT JOIN BERSAMAAN
Select
Admin_pr_vp.pres_name as “Nama Presiden”,
Admin_pr_vp.vice_pres_name as “Nama Wakil Presiden”,
Administration.year_inaugurated as “Tahun Inagurasi”,
Pres_hobby.hobby as “Hobi”
From admin_pr_vp
LEFT JOIN (pres_hobby
RIGHT JOIN administration
On administration.pres_name = pres_hobby.pres_name)
On admin_pr_vp.admin_nr = administration.admin_nr
Where administration.admin_nr is not null
And pres_hobby.pres_name is null
Order by admin_pr_vp.admin_nr, administration.admin_nr, pres_hobby.pres_name;
FULL JOIN
Digunakan untuk menggabungkan dua table dan meampilkan seluruh isi table walaupun terdapat data-data yang saling berkaitan diantara kedua table tersebut
STORED PROCEDURES AND PARAMETERS
Merupakan kumpulan perintah atau statement yang disimpan dan di eksekusi di server database mysql
Lebih cepat
Menghilangkan duplikasi proses, pemeliharaan yang mudah
Meningkatkan keamanan database
Contoh
Create function uch (x char(10)) returns char(20) return concat(‘permisi,’,x,’!’);
Pemanggilannya menggunakan
Select uch (‘mantab gan’);
STORED PROCEDURE
Delimiter$$
Create procedure nama_spnya()
Begin
Select * from president;
End$$
Delimiter;
Pemanggilannya menggunakan
Call uch;
DROP STORED FUNCTION
DROP FUNCTION NAMA_FUNCTIONNYA;
DROP STORED PROCEDURE
DROP PROCEDURE NAMA_PROCEDURENYA;
INSERT INTO
INSERT INTO PRESIDENT VALUES
('Carter J E','1924','4','NULL','Democratic','Georgia') ; INSERT INTO PRESIDENT VALUES
('Reagan R','1911','3','NULL','Republican','Illinois') ;
DELETE TABLE
Delete from PRESIDENT where PRES_NAME
=‘Reagan R’;
Delete from PRESIDENT where PRES_NAME
=‘Carter J E’;
TRUNCATE
TRUNCATE PRESIDENT;
PERBEDAAN DELETE DAN TRUNCATE
Delete Termasuk DML (Data Manipulasi Language) Bisa di RollBack Proses nya lebih lambat Query dapat digunakan pada table yang menjadi referensi Forign Key Tidak bisa melakukan reset Identity pada table Bisa mengaktifkan trigger
Truncate Termasuk DDL(Data Definition Language) Bisa di RollBack(Digital Forensic) Proses lebih cepat Query hanya menghapus record dalam table nya saja tanpa menghapus struktur nya Melakukan reset Identity pada table Tidak dapat mengaktifkan trigger
UPDATE
Update PRESIDENT set
PRES_NAME=‘Luna Maya’
where PRES_NAME=‘Reagan R’;
PRIMARY KEY
Satu atau beberapa kolom pada table yang mengidentifikasi tiap kolom dan baris pada table tersebut
INDEXES ATAU PENGINDEXAN
Digunakan untuk melakukan pencarian data per baris dengan nilai yang spesifik secara cepat pada tiap table nya
Kegunaan Index Melakukan pencarian cepat, dengan menambahkan syarat di query dengan WHERE Mendapatkan Record dari table lain ketika melakukan join antar table Melakukan pencarian nilai maksimal atau nilai minimal dari kolom index yang spesial
FOREIGN KEY
Satu atau beberapa kolom pada table yang merupakan primary key pada table satu namun diletakan pada table dimana tablenya berelasi dengan table dirinya
CREATE TABLE
Create table presiden(
Pres_name char(16),
Birth_yr smallint,
Yrs_serv smallint,
Death_age smallint,
Party char(11),
State_born char(15));
DROP TABLE
DROP TABLE PRESIDENT;
INDEX
Create index index1 on
PRESIDENT (PRES_NAME(16));
Pemanggilannya dengan mengetikkan
SHOW INDEX FROM PRESIDENT;
MULTI INDEX
Create index index1 on PRESIDENT
(PRES_NAME(16),Party(11));
Pemanggilannya dengan mengetikkan
Show index from PRESIDENT;
DROP INDEX
DROP INDEX1 ON PRESIDENT;
Catatan penting !!!
Kelompok yang TIDAK mengumpulkan PPT nya ke ane untuk diringkas adalah kelompok
