Pertemuan X Structure Query Language Studi Kasus : Pasundan Mobil Fak Teknik Jurusan Teknik Informatika Fak. Teknik Universitas Pasundan Caca E. Supriana, S.Si.,MT.
[email protected] d
2014
P d M bil Pasundan Mobil (proses bisnis) y Pasundan Mobil adalah sebuah pabrik mobil yang y y y y
menghasilkan lebih dari satu tipe mobil. Setiap mobil membutuhkan banyak suku cadang yang dikirim oleh banyak pemasok. Setiap pemasok mengirim suku cadang ke pabrik k k d k b k dalam tanggal tertentu. Suku cadang disimpan di rak dalam sebuah gudang Suku cadang disimpan di rak dalam sebuah gudang. Beberapa tipe mobil yang berbeda menggunakan mesin yang sama. 2
Database Requirements Menampilkan suku cadang yang dibutuhkan oleh mobil tertentu. 2. Menampilkan suku cadang yang diproduksi oleh M ilk k d di d k i l h pemasok tertentu. 3 Menampilkan suku cadang tertentu yang disimpan 3. digudang. 4. Menampilkan mesin yang digunakan oleh mobil tertentu. 1.
3
kode_ mobil
MOBIL
tipe tipe_ mobi l
1 R1
BBM
nama _mobi l
R1 : menggunakan/digunakan R2 : mengirim/dikirim R3 : menyimpan/disimpan 3 y p / p
tipe_ mesi n
kode _rak
CC_mob il
n
Jumlah _tersed ia
kode _sc
n
1 R3
SUKU CADANG
GUDANG
nama _sc tgl_kiri m
n R2 m PEMASOK
jumla h_sc kode_ pmk
alamat_jl n_pmk k alama t_pm k
nama _pmk tlp_pmk
alamat_kot a_pmk
Entity Relationship p Diagram PASUNDAN MOBIL
4
Functional Dependency y Ketergantungan fungsional entitas Mobil
kode_mobil Æ nama_mobil, tipe_mobil, tipe_mesin ti tipe_mesin i Æ cc_mobil, bbm bil bb y Ketergantungan fungsional entitas Suku Cadang kode sc Æ nama_sc, jumlah_sc kode_sc nama sc jumlah sc y Ketergantungan fungsional entitas Pemasok kode_p pmk Æ nama_p pmk, alamat , _jjln_p pmk, , alamat_kota_pmk, tlp_pmk y Ketergantungan fungsional entitas Gudang k d kode_rak k Æ jumlah_tersedia j l h di 5
Ph i lD t b Physical Database (sebelum normalisasi)
Keterangan : • PK : Primary Key PK P i K • PFK : Primary Foreign Key • UNN : Unique Not Null • NN : Not Null
6
Normalisasi 1NF Normalisasi 1NF contoh isi tabel PEMASOK kode_pmk nama_pmk pmk01 Goodyear Indonesia, Tbk. pmk02 PT Baut Indah Jaya pmk03 PT Carpetindo pmk04 PT Radial Kusuma Pati pmk05 Frameglasses, Tbk. pmk06 Danapaint, Tbk. pmk07 PT Cruise Mission pmk08 PT Asah Skills … …
alamat_jln_pmk Cisadane 45 Komp. Industri X no. 18 Komp. Industri X no. 41 Komp. Industri X no. 23 Kebon Rumput 456 Pulo Gadung Selatan 56 Marunda 678 Komp. Industri X no. 5 …
alamat_kota_pmk Bekasi Karawang Karawang Karawang Cimahi Jakarta Jakarta Karawang …
tlp_pmk (024)7778999 (027)8000777 (027)9998767 (027)8888833 (023)8876756 (021)9987678 (021)9987678 (027)9908789 …
Terjadi pengulangan (repetisi atau bernilai jamak) pada j p g g ( p j )p field alamat_kota_pmk, sehingga tidak memenuhi syarat 1NF, tabel PEMASOK dinormalisasi menjadi sbb. :
7
Normalisasi 2NF y Ketergantungan fungsional entitas Mobil
kode_mobil Æ nama_mobil, tipe_mobil, tipe_mesin tipe mesin Æ cc_mobil, bbm tipe_mesin cc mobil, bbm y Telah memenuhi persyaratan 1NF tetapi tidak memenuhi persyaratan 2NF karena terdapat ketergantungan parsial, dimana atribut non‐key (cc mobil dan bbm) tergantung parsial pada atribut non‐key (cc_mobil dan bbm) tergantung parsial pada atribut non key yang lain (tipe_mesin). y Sehingga tabel MOBIL dinormalisasi menjadi sbb.:
8
Ph i lD t b Physical Database (setelah normalisasi)
9
LLogical Database i lD t b (setelah normalisasi)
10
Membuat DB, tabel Mesin & Mobil mysql> create database pasundan_mobil; pasundan mobil; Query OK, 1 row affected (0.06 sec) mysql> use pasundan_mobil; Database changed mysql> l create t t table bl mesin( i ( -> kode_mesin char(5) primary key not null, -> tipe_mesin char(10) not null, -> cc_mesin int, -> bbm char(20)); Query OK, 0 rows affected (0.27 sec) mysql> create table mobil( -> kode_mobil char(5) primary key not null, -> > nama nama_mobil mobil char(25) not null null, -> tipe_mobil char(25) not null, -> kode_mesin char(5), -> foreign key (kode_mesin) references mesin(kode_mesin) -> on update cascade -> on delete restrict) -> engine=InnoDB; Query OK, 0 rows affected (0.12 sec) 11
Membuat tabel Gudang & Suku_Cadang mysql> create table gudang( -> kode_rak key not k d k char(5) h (5) primary i k t null, ll -> jumlah_tersedia int); Query OK, 0 rows affected (0.14 sec) mysql> create table suku_cadang( -> kode_sc char(5) primary key not null, -> nama_sc char(25) not null, -> > jumlah jumlah_sc sc int int, -> kode_mobil char(5), -> kode_rak char(5), -> foreign key (kode_mobil) references mobil(kode_mobil), -> foreign key (kode_rak) references gudang(kode_rak) -> on update cascade ) -> on delete restrict) -> engine=InnoDB; Query OK, 0 rows affected (0.15 sec) 12
Membuat tabel Kota, Pemasok & Sc_Pmk k mysql> create table kota( -> kode_kota char(5) primary key not null, -> alamat_kota_pmk char(25) not null); Query OK, 0 rows affected (0.17 sec) mysql> create table pemasok( -> kode_pmk char(5) primary key not null, -> nama_pmk char(25) not null, -> alamat_jln_pmk char(25), -> tlp_pmk char(15), -> kode_kota char(5), -> foreign key (kode_kota) references kota(kode_kota) -> on update cascade -> > on delete restrict) -> engine=InnoDB; Query OK, 0 rows affected (0.12 sec) mysql> create table sc_pmk( -> tgl_kirim date not null, -> kode_sc char(5), -> kode_pmk char(5), -> foreign key (kode_sc) references suku_cadang(kode_sc), -> > foreign key (kode_pmk) (kode pmk) references pemasok(kode_pmk) pemasok(kode pmk) -> on update cascade -> on delete restrict) -> engine=InnoDB; Query OK, 0 rows affected (0.08 sec)
13
C hd k i i b l Contoh deskripsi tabel mysql> desc suku_cadang; +------------+----------+------+-----+---------+-------+ + + + + + + + | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | kode_sc | char(5) | NO | PRI | | | | nama nama_sc sc | char(25) | NO | | | | | jumlah_sc | int(11) | YES | | NULL | | | kode_mobil | char(5) | YES | MUL | NULL | | | kode_rak | char(5) | YES | MUL | NULL | | + +------------+----------+------+-----+---------+-------+ + + + + + + 5 rows in set (0.26 sec) mysql> desc sc_pmk; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | tgl_kirim | date | NO | | | | | kode kode_sc sc | char(5) | YES | MUL | NULL | | | kode_pmk | char(5) | YES | MUL | NULL | | +-----------+---------+------+-----+---------+-------+ 3 rows in set (0.08 sec)
14
Contoh isi record tabel (1) mysql> insert into mesin > (kode_mesin,tipe_mesin,cc_mesin,bbm) -> (kode mesin tipe mesin cc mesin bbm) -> values -> ('m01','VVTi-01P',1800,'Pertamax'), -> ('m02','VVTi-01SL',1500,'Solar'), -> ('m03','VVTi-02PP',1250,'Premium'), -> ('m04','VVTi-02SL',1500,'Bio Solar'), -> ('m05','PVVTi-DX',2000,'Pertamax Dex'); Query OK, 5 rows affected (0.11 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from mesin; +------------+------------+----------+--------------+ | kode_mesin | tipe_mesin | cc_mesin | bbm | +------------+------------+----------+--------------+ | m01 | VVTi VVTi-01P 01P | 1800 | Pertama Pertamax | | m02 | VVTi-01SL | 1500 | Solar | | m03 | VVTi-02PP | 1250 | Premium | | m04 | VVTi-02SL | 1500 | Bio Solar | | m05 | PVVTi-DX | 2000 | Pertamax Dex | +------------+------------+----------+--------------+ 5 rows in set (0.06 sec) 15
Contoh isi record tabel (2) Contoh isi record tabel (2) mysql> insert into mobil -> (kode_mobil,nama_mobil,tipe_mobil,kode_mesin) -> values > ( mb01 ,'Cheetah' Cheetah ,'SUV' SUV ,'m05') m05 ), -> ('mb01' -> ('mb02','Walrus','Sedan','m01'), -> ('mb03','White Bear','Sedan Hatchback','m03'), -> ('mb04','White Bear SL','Sedan Hatchback','m02'), -> ('mb05','Brown Sugar','Truck Utility','m04'), -> ('mb06','Walrus XL','Sedan Automatic','m01'), -> ('mb07','Walrus XLS','Sedan Ultra Automatic','m05'); Query OK, 7 rows affected (0.03 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from mobil; +------------+---------------+-----------------------+------------+ | kode_mobil | nama_mobil | tipe_mobil | kode_mesin | +------------+---------------+-----------------------+------------+ | mb01 | Cheetah | SUV | m05 | | mb02 | Walrus | Sedan | m01 | | mb03 | White Bear | Sedan Hatchback | m03 | | mb04 | White Bear SL | Sedan Hatchback | m02 | | mb05 | Brown Sugar | Truck Utility | m04 | | mb06 | Walrus XL | Sedan Automatic | m01 | | mb07 | Walrus XLS | Sedan Ultra Automatic | m05 | +------------+---------------+-----------------------+------------+ 7 rows in set (0.00 sec)
16
Menambah field pada tabel Suku_Cadang mysql> alter table suku_cadang -> add harga int not null; Query OK, 7 rows affected (0.22 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> desc suku_cadang; +------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | + +------------+----------+------+-----+---------+-------+ + + + + + + | kode_sc | char(5) | NO | PRI | | | | nama_sc | char(25) | NO | | | | | jumlah_sc | int(11) | YES | | NULL | | | kode kode_mobil mobil | char(5) | YES | MUL | NULL | | | kode_rak | char(5) | YES | MUL | NULL | | | harga | int(11) | NO | | | | +------------+----------+------+-----+---------+-------+ 6 rows i in set t (0 (0.02 02 sec) )
17
Update mysql> update gudang -> set jumlah_tersedia = 65 -> where kode_rak = 'r14'; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0
18
Show (1) mysql> show tables; +--------------------------+ | Tables_in_pasundan_mobil | +--------------------------+ | gudang | | kota | | mesin | | mobil | | p pemasok | | sc_pmk | | suku_cadang | +--------------------------+ 7 rows in set (0.00 sec) 19
Show (2)
mysql> show create table mesin; +-------+---------------------------------------------------------------------| Table | Create Table +-------+---------------------------------------------------------------------| mesin | CREATE TABLE `mesin` ( `kode kode_mesin mesin` char(5) NOT NULL, NULL `tipe_mesin` char(10) NOT NULL, `cc_mesin` int(11) default NULL, `bbm` char(20) default NULL, PRIMARY KEY (`kode_mesin`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+---------------------------------------------------------------------1 row in set (0.10 sec) 20
Requirement 1 y Menampilkan suku cadang yang dibutuhkan oleh mobil
tertentu.
mysql> l> select l t nama_sc,jumlah_sc j l h -> from mobil,suku_cadang -> where nama_mobil = 'Cheetah' and g _mobil; -> mobil.kode_mobil = suku_cadang.kode +--------------+-----------+ | nama_sc | jumlah_sc | +--------------+-----------+ | wiper wiper-12 12 | 25 | | baut tipe 20 | 40 | +--------------+-----------+ 2 rows in set (0.06 sec)
21
Requirement 2 y Menampilkan suku cadang yang diproduksi
oleh pemasok tertentu.
mysql> select nama_sc,jumlah_sc,nama_pmk -> from suku_cadang,sc_pmk,pemasok -> where suku_cadang.kode_sc = sc_pmk.kode_sc and -> > sc sc_pmk.kode_pmk pmk.kode pmk = pemasok.kode_pmk; pemasok.kode pmk; +--------------+-----------+--------------------------+ | nama_sc | jumlah_sc | nama_pmk | +--------------+-----------+--------------------------+ | ban ukuran 5 | 30 | Goodyear Indonesia, Tbk. | | baut tipe 20 | 40 | PT Baut Indah Jaya | | dashboard 56 | 15 | PT Carpetindo | +--------------+-----------+--------------------------+ 3 rows in set (0.00 sec)
22
Requirement 3 y Menampilkan suku cadang tertentu yang
disimpan digudang.
mysql> select nama_sc,jumlah_sc,gudang.kode_rak -> from suku_cadang,gudang -> where kode_sc='sc01' and -> suku_cadang.kode_rak = gudang.kode_rak; +----------+-----------+----------+ | nama_sc | jumlah_sc | kode_rak | +----------+-----------+----------+ | wiper-12 | 25 | r01 | +----------+-----------+----------+ 1 row i in set (0 (0.08 08 sec) ) 23
Requirement 4 y Menampilkan mesin yang digunakan oleh mobil
tertentu.
mysql> select kode_mobil,tipe_mesin -> from mobil,mesin -> where mobil.kode_mesin = mesin.kode_mesin and -> nama_mobil='Brown Sugar'; +------------+------------+ | kode_mobil | tipe_mesin | +------------+------------+ | mb05 | VVTi-02SL | +------------+------------+ 1 row in set (0 (0.00 00 sec) 24
Distinct y Distinct berfungsi untuk tidak menampilkan redundansi dalam kolom record mysql> select * from mobil; +------------+---------------+-----------------------+------------+ | kode_mobil | nama_mobil | tipe_mobil | kode_mesin | +------------+---------------+-----------------------+------------+ | mb01 | Cheetah | SUV | m05 | | mb02 | Walrus | Sedan | m01 | | mb03 | White Bear | Sedan Hatchback | m03 | | mb04 | White Bear SL | Sedan Hatchback | m02 | | mb05 | Brown Sugar | Truck Utility | m04 | | mb06 | Walrus XL | Sedan Automatic | m01 | | mb07 | Walrus XLS | Sedan Ultra Automatic | m05 | +------------+---------------+-----------------------+------------+ 7 rows in set (0.00 sec) mysql> select distinct tipe_mobil from mobil; +-----------------------+ | tipe p _mobil | +-----------------------+ | SUV | | Sedan | | Sedan Hatchback | | Truck Utility | | Sedan Automatic | | Sedan Ultra Automatic | +-----------------------+ 6 rows in set (0.00 sec)
25
Group By Group By y Mengelompokan hasil query berdasarkan kolom tertentu, repetisi akan dihilangkan _ _ _ _ mysql> select kode_sc,nama_sc,jumlah_sc from suku_cadang; +---------+--------------+-----------+ | kode_sc | nama_sc | jumlah_sc | +---------+--------------+-----------+ | sc01 | wiper-12 | 25 | | sc02 | wiper-22 | 25 | | sc03 03 | b baut t ti tipe 20 | 40 | | sc04 | baut tipe 25 | 30 | | sc05 | ban ukuran 5 | 30 | | sc06 | ban ukuran 6 | 10 | | sc07 | dashboard 56 | 15 | + +---------+--------------+-----------+ + + + 7 rows in set (0.00 sec) mysql> select kode_sc,nama_sc,jumlah_sc from suku_cadang -> group by jumlah_sc; +---------+--------------+-----------+ | kode_sc | nama_sc | jumlah_sc | +---------+--------------+-----------+ | sc06 | ban ukuran 6 | 10 | | sc07 | dashboard 56 | 15 | | sc01 | wiper-12 | 25 | | sc04 04 | b baut t ti tipe 25 | 30 | | sc03 | baut tipe 20 | 40 | +---------+--------------+-----------+ 5 rows in set (0.00 sec) 26
Having y
H i dipergunakan untuk menyeleksi kondisi tertentu dari query di k k l k i k di i d i Having
mysql> select kode_sc,nama_sc,jumlah_sc from suku_cadang; +---------+--------------+-----------+ | kode_sc | nama_sc | jumlah_sc | + + + + +---------+--------------+-----------+ | sc01 | wiper-12 | 25 | | sc02 | wiper-22 | 25 | | sc03 | baut tipe 20 | 40 | | sc04 | baut tipe 25 | 30 | | sc05 | ban ukuran 5 | 30 | | sc06 | ban ukuran 6 | 10 | | sc07 | dashboard 56 | 15 | +---------+--------------+-----------+ 7 rows in set (0 (0.00 00 sec) mysql> select kode_sc,nama_sc,jumlah_sc from suku_cadang -> having nama_sc not like 'ban %'; +---------+--------------+-----------+ | kode_sc | nama_sc | jumlah_sc | +---------+--------------+-----------+ | sc01 | wiper-12 | 25 | | sc02 | wiper-22 | 25 | | sc03 | baut tipe 20 | 40 | | sc04 | baut tipe 25 | 30 | | sc07 | dashboard 56 | 15 | +---------+--------------+-----------+ 5 rows in set (0.00 sec)
27
Adding y Menjumlahkan atribut yang tipenya integer mysql> select gudang.kode_rak, -> j jumlah_sc + j jumlah_tersedia as total -> from suku_cadang,gudang -> where gudang.kode_rak = suku_cadang.kode_rak; +----------+-------+ | kode_rak | total | + +----------+-------+ + + | r01 | 150 | | r01 | 150 | | r02 | 290 | | r02 | 280 | | r03 | 253 | | r05 | 177 | | r07 | 113 | +----------+-------+ 7 rows in set (0 (0.03 03 sec)
28
Delete mysql> delete from gudang -> where kode_rak = 'r15'; Query OK, 1 row affected (0.11 0 sec) y Delete digunakan untuk menghapus satu atau beberapa isi digunakan untuk menghapus satu atau beberapa isi
field dalam sebuah tabel. y Drop digunakan untuk menghapus tabel, contoh SQL statement : drop table gudang;
29
Aggregate Functions (max & min) Aggregate Functions (max & min) mysql> select max(jumlah_tersedia) as jumlah_maks from gudang; +-------------+ | jumlah_maks | +-------------+ | 250 | +-------------+ 1 row in set (0.00 sec) mysql> select min(jumlah_tersedia) as jumlah_min from gudang; +------------+ | jumlah_min | +------------+ | 30 | +------------+ 1 row in set (0.00 sec) 30
Count mysql> select count(*) as jumlah_rak -> from gudang; +------------+ _ | | jumlah_rak +------------+ | 14 | +------------+ 1 row in set (0.00 sec) 31
Sum mysql> select kode_rak,sum(harga) -> from suku_cadang -> group by kode_rak; +----------+------------+ | kode_rak | sum(harga) | +----------+------------+ | r01 | 8125000 | | r02 | 845000 | | r03 | 37500000 | | r05 | 17500000 | | r07 | 78750000 | +----------+------------+ 5 rows in set (0.00 sec) 32
Sub Query mysql> select kode_sc,nama_sc -> from suku_cadang -> > where nama_sc nama sc like 'ban %' -> and jumlah_sc = _ from -> (select min(jumlah_sc) suku_cadang); +---------+--------------+ | kode kode_sc sc | nama_sc nama sc | +---------+--------------+ | sc06 | ban ukuran 6 | +---------+--------------+ 1 row in set (0.00 sec)
33
Between mysql> select kode_rak, jumlah_tersedia -> from gudang -> where jumlah_tersedia between 50 and 100; + +----------+-----------------+ + + | kode_rak | jumlah_tersedia | +----------+-----------------+ | r06 | 75 | | r07 | 98 | | r09 | 77 | | r10 | 55 | | r12 | 80 | | r14 | 66 | | r15 | 78 | +----------+-----------------+ 7 rows i in set t (0 (0.00 00 sec) ) 34
Or mysql> select * from mobil -> where nama_mobil like 'Walrus%' -> or nama_mobil like 'White Bear%'; +------------+---------------+-----------------------+------------+ | kode kode_mobil mobil | nama nama_mobil mobil | tipe tipe_mobil mobil | kode kode_mesin mesin | +------------+---------------+-----------------------+------------+ | mb02 | Walrus | Sedan | m01 | | mb03 | White Bear | Sedan Hatchback | m03 | | mb04 | White Bear SL | Sedan Hatchback | m02 | | mb06 06 | Walrus XL | Sedan Automatic i | m01 01 | | mb07 | Walrus XLS | Sedan Ultra Automatic | m05 | +------------+---------------+-----------------------+------------+ 5 rows in set (0.00 sec)
35
In mysql> select * from mesin; +------------+------------+----------+--------------+ + + + + + | kode_mesin | tipe_mesin | cc_mesin | bbm | +------------+------------+----------+--------------+ | m01 | VVTi-01P | 1800 | Pertamax | | m02 | VVTi VVTi-01SL 01SL | 1500 | Solar | | m03 | VVTi-02PP | 1250 | Premium | | m04 | VVTi-02SL | 1500 | Bio Solar | | m05 | PVVTi-DX | 2000 | Pertamax Dex | + +------------+------------+----------+--------------+ + + + + 5 rows in set (0.00 sec) mysql> select * from mesin -> where bbm in ( ('Pertamax' Pertamax ,'Premium'); Premium ); +------------+------------+----------+----------+ | kode_mesin | tipe_mesin | cc_mesin | bbm | +------------+------------+----------+----------+ | m01 | VVTi-01P | 1800 | Pertamax | | m03 | VVTi-02PP | 1250 | Premium | +------------+------------+----------+----------+ 2 rows in set (0.01 sec)
36
And mysql> select nama_mobil -> from mobil,mesin -> where mesin.kode_mesin = mobil.kode_mesin -> > and d bbm bb lik like 'P 'Pertamax%' t %' -> and tipe_mobil like 'Sedan%'; +------------+ | nama nama_mobil mobil | +------------+ | Walrus | | Walrus XL | | Walrus XLS | +------------+ 3 rows in set (0.09 sec)
37
Regexp mysql> select nama_mobil,tipe_mobil from mobil > where tipe_mobil S ; -> tipe mobil regexp '^S'; +---------------+-----------------------+ | nama_mobil | tipe_mobil | +---------------+-----------------------+ | Cheetah | SUV | | Walrus | Sedan | | White Bear | Sedan Hatchback | | White Bear SL | Sedan Hatchback | | Walrus XL | Sedan Automatic | | Walrus XLS | Sedan Ultra Automatic | +---------------+-----------------------+ 6 rows in set (0.00 sec) mysql> select nama_mobil,tipe_mobil from mobil -> > where tipe tipe_mobil mobil regexp 'ic$'; +------------+-----------------------+ | nama_mobil | tipe_mobil | +------------+-----------------------+ | Walrus XL | Sedan Automatic | | Walrus XLS | Sedan Ultra Automatic | +------------+-----------------------+ 2 rows in set (0.00 sec) 38
Natural Join mysql> select nama_mobil from mobil,mesin -> where tipe_mobil = 'Sedan' -> and mesin.kode_mesin = mobil.kode_mesin; +------------+ | nama nama_mobil mobil | +------------+ | Walrus | +------------+ 1 row in set (0.00 sec) mysql> select nama_mobil -> from mobil natural join mesin -> where tipe_mobil = 'Sedan'; +------------+ | nama_mobil | +------------+ | Walrus | +------------+ 1 row in set (0 (0.00 00 sec)
39
I J i Inner Join y Inner join membuat tabel hasil penggabungan nilai
kolom tertentu dari dua tabel berdasarkan predikat join.
mysql> select nama_sc from suku_cadang -> inner join gudang -> on gudang.kode_rak = suku_cadang.kode_rak; +--------------+ | nama nama_sc sc | +--------------+ | wiper-12 | | wiper-22 | | baut tipe 20 | | baut tipe 25 | | ban ukuran 5 | | ban ukuran 6 | | dashboard 56 | +--------------+ 7 rows in set (0.06 sec)
40
L f J i Left Join y Left join akan menampilkan semua nilai dari table sebelah
kiri (left), dalam hal ini adalah tabel suku_cadang meskipun ada field yang mempunyai nilai null
mysql> select nama_sc,jumlah_sc from suku_cadang -> > left l ft j join i gudang d -> on gudang.kode_rak = suku_cadang.kode_rak; +--------------+-----------+ | nama_sc | jumlah_sc | +--------------+-----------+ | wiper-12 | 25 | | wiper-22 | 25 | | baut tipe 20 | 40 | | baut tipe 25 | 30 | | ban ukuran 5 | 30 | | ban ukuran 6 | 10 | | dashboard 56 | 15 | +--------------+-----------+ 7 rows in set (0.07 sec)
41
Right Join y Right join akan menampilkan semua nilai dari table sebelah kanan (right), dalam hal
ini adalah tabel gudang meskipun ada yang mempunyai nilai null
mysql> select nama_sc,jumlah_sc from suku_cadang -> right join gudang -> on gudang.kode_rak = suku_cadang.kode_rak; +--------------+-----------+ | nama_sc | jumlah_sc | + +--------------+-----------+ + + | wiper-12 | 25 | | wiper-22 | 25 | | baut tipe 20 | 40 | | baut tipe 25 | 30 | | ban b ukuran k 5 | 30 | | ban ukuran 6 | 10 | | NULL | NULL | | dashboard 56 | 15 | | NULL | NULL | | NULL | NULL | | NULL | NULL | | NULL | NULL | | NULL | NULL | | NULL | NULL | | NULL | NULL | | NULL | NULL | +--------------+-----------+ 16 rows in set (0.00 sec)
42
Self Join y Self join adalah sebuah tabel melakukan join dengan tabel itu
sendiri, dimana tabel tersebut menggunakan nama sementara
mysql> select a.harga, b.harga -> from suku_cadang a,suku_cadang b -> where a.harga > b.harga; +----------+----------+ | harga | harga | +----------+----------+ | 4375000 | 3750000 | … … | 78750000 | 17500000 | +----------+----------+ 21 rows in set (0.00 sec)
43
Union y Union digunakan untuk menyatukan beberapa kolom dari
tabel berbeda ke dalam satu atau lebih kolom baru hasil query.
mysql> select alamat_kota_pmk from kota -> union -> select nama_pmk from pemasok; +--------------------------+ | alamat_kota_pmk | + +--------------------------+ + | Jakarta | | Bandung | | Bekasi | | Tangerang | | Karawang | | Semarang | | Surabaya | | Goodyear Indonesia, Tbk. | | PT Baut Indah Jaya | | PT Carpetindo | +--------------------------+ 10 rows in set (0.13 sec) 44
Union All mysql> select nama_sc -> from suku_cadang natural join sc_pmk -> union all -> select nama_pmk -> from pemasok natural join sc_pmk; +--------------------------+ | nama_sc | +--------------------------+ | baut tipe 20 | | ban ukuran 5 | | dashboard 56 | | Goodyear Indonesia, Tbk. | | PT Baut Indah Jaya | | PT Carpetindo | +--------------------------+ 6 rows in set (0.17 sec)
45
View & Drop View mysql> create view jumlah_persediaan as -> select * from gudang -> where jumlah_tersedia > 100; Query OK, 0 rows affected (0.06 sec) mysql> select * from jumlah_persediaan; +----------+-----------------+ | kode_rak | jumlah_tersedia | +----------+-----------------+ | r01 01 | 12 125 | | r02 | 250 | | r03 | 223 | | r05 | 167 | | r08 | 128 | +----------+-----------------+ 5 rows in set (0.00 sec) mysql> drop view jumlah_persediaan; Query OK OK, 0 rows affected (0 (0.00 00 sec)
46
If If mysql> select nama_mobil, tipe_mobil, -> > if (tipe_mobil (ti bil like lik 'Sedan%','Mobil 'S d %' 'M bil Sedan','Non S d ' 'N Sedan') -> as tipe_sedan -> from mobil; +---------------+-----------------------+-------------+ | nama_mobil | tipe_mobil | tipe_sedan | + +---------------+-----------------------+-------------+ + + + | Cheetah | SUV | Non Sedan | | Walrus | Sedan | Mobil Sedan | | White Bear | Sedan Hatchback | Mobil Sedan | | White Bear SL | Sedan Hatchback | Mobil Sedan | | Brown Sugar | Truck Utility | Non Sedan | | Walrus XL | Sedan Automatic | Mobil Sedan | | Walrus XLS | Sedan Ultra Automatic | Mobil Sedan | +---------------+-----------------------+-------------+ 7 rows in set (0.00 sec) 47
Case mysql> select alamat_kota_pmk, > case alamat_kota_pmk -> alamat kota pmk -> when 'Jakarta'then 'DKI Jakarta' -> when 'Bandung'then 'Jawa Barat' -> when 'Bekasi'then 'Jawa Barat' -> when 'Tangerang'then 'Banten' -> when 'Karawang'then 'Jawa Barat' -> when 'Semarang'then 'Jawa Tengah' -> when 'Surabaya'then 'Jawa Timur' -> end as provinsi -> from kota; ; +-----------------+-------------+ | alamat_kota_pmk | provinsi | +-----------------+-------------+ | Jakarta | DKI Jakarta | | Band Bandung ng | Ja Jawa a Barat | | Bekasi | Jawa Barat | | Tangerang | Banten | | Karawang | Jawa Barat | | Semarang | Jawa Tengah | | Surabaya | Jawa Timur | +-----------------+-------------+ 7 rows in set (0.01 sec) 48
Date & Time Functions mysql> select curdate(); +------------+ | curdate() | + +------------+ + | 2014-08-03 | +------------+ 1 row in set (0.06 sec) mysql> select curtime(); +-----------+ | curtime() () | +-----------+ | 20:19:34 | +-----------+ 1 row i in set t (0 (0.06 06 sec) ) 49
Date Format mysql> select kode_sc,tgl_kirim, -> date_format(tgl_kirim,'%W %M %Y') -> from sc_pmk; +---------+------------+-----------------------------------+ | kode_sc | tgl_kirim | date_format(tgl_kirim,'%W %M %Y') | +---------+------------+-----------------------------------+ | sc03 | 2014-01-04 | Saturday January 2014 | | sc05 | 2014-02-01 | Saturday February 2014 | | sc07 | 2014-03-14 | Friday March 2014 | +---------+------------+-----------------------------------+ 3 rows in set (0.00 sec)
50
Dayname & Monthname Dayname & Monthname mysql> select tgl_kirim,dayname(tgl_kirim) as day -> from sc_pmk; +------------+----------+ | tgl_kirim | day | +------------+----------+ | 2014-01-04 | Saturday | | 2014-02-01 | Saturday | | 2014-03-14 | Friday | +------------+----------+ 3 rows in set (0.00 sec) mysql> select tgl_kirim,monthname(tgl_kirim) as month -> from sc_pmk; +------------+----------+ | tgl_kirim i i | month | +------------+----------+ | 2014-01-04 | January | | 2014-02-01 | February | | 2014 2014-03-14 03 14 | March h | +------------+----------+ 3 rows in set (0.00 sec) 51
String Function ‐ lpad mysql> select nama_sc, -> lpad(format(harga,0),12,char(32)) -> as harganya -> from suku_cadang; +--------------+--------------+ | nama_sc | harganya | +--------------+--------------+ | wiper-12 | 3,750,000 | | wiper-22 | 4,375,000 | | baut tipe 20 | 500,000 | | baut tipe 25 | 345,000 | | ban ukuran 5 | 37,500,000 | | ban ukuran 6 | 17,500,000 | | dashboard 56 | 78,750,000 | +--------------+--------------+ 7 rows in set (0.03 sec)
52
String Function ‐ ucase mysql> select kode_mobil,ucase(nama_mobil) -> from mobil; +------------+-------------------+ | k kode_mobil d bil | ucase(nama_mobil) ( bil) | +------------+-------------------+ | mb01 | CHEETAH | | mb02 | WALRUS | | mb03 | WHITE BEAR | | mb04 | WHITE BEAR SL | | mb05 | BROWN SUGAR | | mb06 | WALRUS XL | | mb07 | WALRUS XLS | +------------+-------------------+ 7 rows in set (0.00 sec) 53
String Function ‐ concat mysql> select concat(nama_mobil,tipe_mobil) -> from mobil; +---------------------------------+ | concat(nama_mobil,tipe_mobil) t( bil ti bil) | +---------------------------------+ | CheetahSUV | | WalrusSedan | | White BearSedan Hatchback | | White Bear SLSedan Hatchback | | Brown SugarTruck Utility | | Walrus XLSedan Automatic | | Walrus XLSSedan Ultra Automatic | +---------------------------------+ 7 rows in set (0.02 sec) 54
Eksport / Import Data y Untuk menyalin tabel ke dalam file teks mysql> select * -> > f from mobil bil -> into outfile 'C:/Downloads/ mobil.txt'; Query OK, 8 rows affected (0.21 sec)
y Untuk menyalin file teks ke dalam tabel dapat
menggunakan perintah : k i t h mysql> LOAD DATA LOCAL INFILE 'C:/Downloads/mobil.txt‘ INTO TABLE komputer;
55
