PRAKTIKUM SISTEM MANAJEMEN BASIS DATA
TUGAS AKHIR
Asisten Laboratorium : 1. Adhika 2. Asti Disusun oleh : Nama
: Ema Setiyaningrum
NIM
: 141051109
Teknik Informatika, SN-2 (Senin, 10.00 -12.30 WIB)
FAKULTAS TEKNOLOGI INDUSTRI INSTITUT SAINS & TEKNOLOGI AKPRIND YOGYAKARTA 2015/2016
1.) 2.) 3.)
SOAL Buat rangkuman tentang mysql dari pertemuan awal (pembuatan database) sampai pertemuan akhir ( pembuatan procedure function ) Buat database nim_kelas Buatlah tabel pasien, dokter,rekam_medis dengan rincian sbb mysql> desc pasien; +-----------+-------------+------+-----+---------+-------+ | Field
| Type
| Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+ | id_pasien | char(5)
| PRI | NULL
|
|
| nama
| varchar(20) | YES
| NO
|
| NULL
|
|
| alamat
| varchar(50) | YES
|
| NULL
|
|
+-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> desc dokter; +-----------+-------------+------+-----+---------+-------+ | Field
| Type
| Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+ | id_dokter | char(5)
| PRI | NULL
|
|
| varchar(20) | YES
|
| NULL
|
|
| spesialis | varchar(50) | YES
|
| NULL
|
|
| nama
| NO
+-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> desc rekam_medis; +-------------+-------------+------+-----+---------+-------+ | Field
| Type
| Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+ | id_rm
| char(5)
| NO
| PRI | NULL
|
|
| id_pasien
| char(5)
| YES
| MUL | NULL
|
|
| id_dokter
| char(5)
| YES
| MUL | NULL
|
|
| penyakit
| varchar(30) | YES
|
| NULL
|
|
|
| NULL
|
|
| tgl_periksa | date
| YES
+-------------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)
4.)
Isi tabel sesuai data berikut: mysql> select*from pasien; +-----------+---------------+-------------+ | id_pasien | nama
| alamat
|
+-----------+---------------+-------------+ | PS001
| Adhika
| Sulawesi
|
| PS002
| Zacky
| Bekasi
|
| PS003
| Jhony Sitogar | Medan
| PS004
| Deny Subagjo
| PS005
| Tony Alamsyah | Kalimantan
|
| Tasikmalaya | |
+-----------+---------------+-------------+ 5 rows in set (0.00 sec)
mysql> select*from dokter; +-----------+--------+----------------+ | id_dokter | nama
| spesialis
|
+-----------+--------+----------------+ | DR001
| Miftah | Jantung
| DR002
| Nina
|
| DR003
| Fahmi
| Kulit
|
| DR004
| Kemal
| Syaraf
|
| DR005
| Stark
| Anak
|
| Penyakit Dalam |
+-----------+--------+----------------+ 5 rows in set (0.00 sec) mysql> select*from rekam_medis; +-------+-----------+-----------+-----------------+-------------+ | id_rm | id_pasien | id_dokter | penyakit
| tgl_periksa |
+-------+-----------+-----------+-----------------+-------------+ | RM001 | PS003
| DR001
| Jantung Koroner | 2015-04-29
|
| RM002 | PS001
| DR003
| Kurap
| 2015-11-27
|
| RM003 | PS002
| DR002
| Asma
| 2015-10-17
|
| RM004 | PS005
| DR005
| Demam
| 2015-09-17
|
| RM005 | PS004
| DR004
| Geger Otak
| 2016-01-01
|
+-------+-----------+-----------+-----------------+-------------+ 5 rows in set (0.00 sec)
5.)
Dengan menggunakan JOIN buatlah query untuk menampilkan tabel berikut ini: +-------+---------------+--------+-----------------+-------------+ | id_rm | nama
| nama
| penyakit
| tgl_periksa |
+-------+---------------+--------+-----------------+-------------+ | RM001 | Jhony Sitogar | Miftah | Jantung Koroner | 2015-04-29
|
| RM002 | Adhika
| Fahmi
| Kurap
| 2015-11-27
|
| RM003 | Zacky
| Nina
| Asma
| 2015-10-17
|
| RM004 | Tony Alamsyah | Stark
| Demam
| 2015-09-17
|
| RM005 | Deny Subagjo
| Geger Otak
| 2016-01-01
|
| Kemal
+-------+---------------+--------+-----------------+-------------+ 5 rows in set (0.07 sec)
Setting environment for using XAMPP for Windows. Ema@TITUT D:\xampp # mysql -u root -p Enter password: Welcome to the MySQL monitor.
Commands end with ; or \g.
Your MySQL connection id is 1 Server version: 5.1.41 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +----------------------------+ | Database
|
+----------------------------+ | information_schema
|
| 141051109_emasetiyaningrum | | cdcol
|
| kampus
|
| mysql
|
| phpmyadmin
|
| test
|
+----------------------------+ 7 rows in set (0.11 sec) Buat database nim_kelas! mysql> create database 141051109_SN2; Query OK, 1 row affected (0.02 sec) mysql> use 141051109_SN2; Database changed Buat tabel pasien, dokter, rekam_medis sesuai rincian sbb! mysql> create table pasien(id_pasien char(5) primary key,nama varchar(20),alamat varchar(50)); Query OK, 0 rows affected (0.08 sec) mysql> create table dokter(id_dokter char(5) primary key,nama varchar(20),spesialis varchar(50)); Query OK, 0 rows affected (0.09 sec)
mysql> create table rekam_medis(id_rm char(5) primary key,id_pasien char(5),id_dokter char(5),penyakit varchar(30),tgl_periksa date, foreign key(id_pasien) references pasien(id_pasien),foreign key(id_dokter) references dokter(id_dokter)); Query OK, 0 rows affected (0.08 sec) mysql> desc pasien; +-----------+-------------+------+-----+---------+-------+ | Field
| Type
| Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+ | id_pasien | char(5)
| NO
| PRI | NULL
|
|
| nama
| varchar(20) | YES
|
| NULL
|
|
| alamat
| varchar(50) | YES
|
| NULL
|
|
+-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> desc dokter; +-----------+-------------+------+-----+---------+-------+ | Field
| Type
| Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+ | id_dokter | char(5)
| PRI | NULL
|
|
| varchar(20) | YES
|
| NULL
|
|
| spesialis | varchar(50) | YES
|
| NULL
|
|
| nama
| NO
+-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> desc rekam_medis; +-------------+-------------+------+-----+---------+-------+ | Field
| Type
| Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+ | id_rm
| char(5)
| NO
| PRI | NULL
|
|
| id_pasien
| char(5)
| YES
| MUL | NULL
|
|
| id_dokter
| char(5)
| YES
| MUL | NULL
|
|
| penyakit
| varchar(30) | YES
|
| NULL
|
|
|
| NULL
|
|
| tgl_periksa | date
| YES
+-------------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)
Isi tabel : mysql> insert into pasien values('PS001','Adhika','Sulawesi'), ('PS002','Zacky','Bekasi'),('PS003','Jhony Sitogar','Medan'),('PS004','Deny Subagjo','Tasikmalaya'), ('PS005','Tony Alamsyah','Kalimantan'); Query OK, 5 rows affected (0.00 sec) Records: 5
Duplicates: 0
Warnings: 0
mysql> select*from pasien; +-----------+---------------+-------------+ | id_pasien | nama
| alamat
|
+-----------+---------------+-------------+ | PS001
| Adhika
| Sulawesi
|
| PS002
| Zacky
| Bekasi
|
| PS003
| Jhony Sitogar | Medan
| PS004
| Deny Subagjo
| PS005
| Tony Alamsyah | Kalimantan
|
| Tasikmalaya | |
+-----------+---------------+-------------+ 5 rows in set (0.00 sec) mysql> insert into dokter values('DR001','Miftah','Jantung'), ('DR002','Nina','Penyakit Dalam'),('DR003','Fahmi','Kulit'),('DR004','Kemal','Syaraf'),('DR005','Star k','Anak'); Query OK, 5 rows affected (0.00 sec) Records: 5
Duplicates: 0
Warnings: 0
mysql> select*from dokter; +-----------+--------+----------------+ | id_dokter | nama
| spesialis
|
+-----------+--------+----------------+ | DR001
| Miftah | Jantung
|
| DR002
| Nina
| Penyakit Dalam |
| DR003
| Fahmi
| Kulit
|
| DR004
| Kemal
| Syaraf
|
| DR005
| Stark
| Anak
|
+-----------+--------+----------------+ 5 rows in set (0.01 sec) mysql> insert into rekam_medis values('RM001','PS003','DR001','Jantung Koroner','2015-04-29'); Query OK, 1 row affected (0.00 sec)
mysql> insert into rekam_medis values('RM002','PS001','DR003','Kurap','2015-11-27'); Query OK, 1 row affected (0.00 sec) mysql> insert into rekam_medis values('RM003','PS002','DR002','Asma','201510-17'); Query OK, 1 row affected (0.00 sec) mysql> insert into rekam_medis values('RM004','PS005','DR005','Demam','2015-09-17'); Query OK, 1 row affected (0.00 sec) mysql> insert into rekam_medis values('RM005','PS004','DR004','Geger Otak','2016-01-01'); Query OK, 1 row affected (0.00 sec) mysql> select*from rekam_medis; +-------+-----------+-----------+-----------------+-------------+ | id_rm | id_pasien | id_dokter | penyakit
| tgl_periksa |
+-------+-----------+-----------+-----------------+-------------+ | RM001 | PS003
| DR001
| Jantung Koroner | 2015-04-29
|
| RM002 | PS001
| DR003
| Kurap
| 2015-11-27
|
| RM003 | PS002
| DR002
| Asma
| 2015-10-17
|
| RM004 | PS005
| DR005
| Demam
| 2015-09-17
|
| RM005 | PS004
| DR004
| Geger Otak
| 2016-01-01
|
+-------+-----------+-----------+-----------------+-------------+ 5 rows in set (0.00 sec) Dengan menggunakan JOIN buatlah query untuk menampilkan tabel sbb : mysql> select rekam_medis.id_rm, pasien.nama, dokter.nama, rekam_medis.penyakit, rekam_medis.tgl_periksa from pasien inner join (dokter inner join rekam_medis on dokter.id_dokter=rekam_medis.id_dokter) on pasien.id_pasien=rekam_medis.id_pasien; +-------+---------------+--------+-----------------+-------------+ | id_rm | nama
| nama
| penyakit
| tgl_periksa |
+-------+---------------+--------+-----------------+-------------+ | RM001 | Jhony Sitogar | Miftah | Jantung Koroner | 2015-04-29
|
| RM002 | Adhika
| Fahmi
| Kurap
| 2015-11-27
|
| RM003 | Zacky
| Nina
| Asma
| 2015-10-17
|
| RM004 | Tony Alamsyah | Stark
| Demam
| 2015-09-17
|
| RM005 | Deny Subagjo
| Geger Otak
| 2016-01-01
|
| Kemal
+-------+---------------+--------+-----------------+-------------+ 5 rows in set (0.03 sec)
RANGKUMAN MATERI 1 PENGANTAR MYSQL 1. Mengaktifkan MySQL di XAMPP : Klik Start pada bagian MySQL kemudian klik “Shell” untuk membuka command prompt pada xampp untuk masuk ke dalam database MySQL.
2. Setelah itu ketikan perintah berikut :
mysql –u root –p
3. Melihat daftar database di komputer : Syntax : show<spasi>databases; Example : mysql> show databases; +----------------------------+ | Database | +----------------------------+ | information_schema | | 141051109_emasetiyaningrum | | 141051109_sn2 | | cdcol | | kampus | | mysql | | phpmyadmin | | test | +----------------------------+ 8 rows in set (0.01 sec)
4. Membuat database baru : Syntax
: create<spasi>database<spasi>nama_database;
Example : mysql> create database sn2; Query OK, 1 row affected (0.01 sec) 5. Memakai database yang telah kita buat : Syntax
: use<spasi>nama_database;
Example : mysql> use sn2; Database changed 6. Membuat tabel baru : Syntax
:
create<spasi>table<spasi>nama_tabel( nama_kolom1<spasi>tipe_data(ukuran),nama_kolom2<spasi> tipe_data(ukuran), nama_kolom3<spasi>tipe_data(ukuran));
Example :
mysql> create table t_mhs(nim varchar(10),
nama_mhs varchar(20),alamat varchar(50)); Query OK, 0 rows affected (0.07 sec) 7. Melihat deskripsi tabel yang telah kita buat : Syntax
: desc<spasi>nama_tabel;
Example : mysql> desc t_mhs; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | nim | varchar(10) | YES | | NULL | | | nama_mhs | varchar(20) | YES | | NULL | | | alamat | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) 8. Menginput data ke dalam tabel : a. Menggunakan syntax INSERT Syntax
: insert<spasi>into<spasi>nama_tabel<spasi>values( ‘data_kolom1’,’data_kolom2’,’data_kolom3’);
Example :mysql> insert into t_mhs values('141051109','Ema Setiyaningrum','Trangkil, Pati'); Query OK, 1 row affected (0.01 sec) mysql> select*from t_mhs; +-----------+-------------------+----------------+ | nim | nama_mhs | alamat | +-----------+-------------------+----------------+ | 141051109 | Ema Setiyaningrum | Trangkil, Pati | +-----------+-------------------+----------------+ 1 row in set (0.00 sec)
b. Menggunakan syntax LOAD DATA
Siapkan file yang berisi data yang akan diinputkan ke tabel didatabase kita, sesuai dengan urutan yg kita buat di MySQL. Simpan dengan format .txt
Contoh
:
(dibuat
di
MS.Excel
dan
disimpan
dengan
nama
data.txt = format TEXT(MS-DOS)) 141051105 Sadika Rhama Kalimantan 141051108 Tinezia Magetan 141051091 Nurmansyah Bengkulu 141051102 Indah Dewi Yogyakarta mysql> load data local infile 'D://data.txt' into table t_mhs; Query OK, 4 rows affected (0.05 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select*from t_mhs; +-----------+-------------------+----------------+ | nim | nama_mhs | alamat | +-----------+-------------------+----------------+ | 141051109 | Ema Setiyaningrum | Trangkil, Pati | | 141051105 | Sadika Rhama | Kalimantan | | 141051108 | Tinezia | Magetan | | 141051091 | Nurmansyah | Bengkulu | | 141051102 | Indah Dewi | Yogyakarta | +-----------+-------------------+----------------+ 5 rows in set (0.00 sec) 9. Menghapus tabel : mysql> create table mat_kul(id_matkul varchar(10), nama_matkul varchar(25), sksint(1), semester int(1), dosen varchar(10)); Query OK, 0 rows affected (0.06 sec) mysql> desc mat_kul; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | id_matkul | varchar(10) | YES | | NULL | | | nama_matkul | varchar(25) | YES | | NULL | | | sks | int(1) | YES | | NULL | | | semester | int(1) | YES | | NULL | | | dosen | varchar(10) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 5 rows in set (0.04 sec) mysql> show tables; +---------------+ | Tables_in_sn2 | +---------------+ | mat_kul | | t_mhs | +---------------+ 2 rows in set (0.00 sec)
Syntax hapus tabel : drop<spasi>table<spasi>nama_tabel; Example : mysql> drop table mat_kul; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +---------------+ | Tables_in_sn2 | +---------------+ | t_mhs | +---------------+ 1 row in set (0.00 sec) 10.
Membuat database baru : mysql> create database coba; Query OK, 1 row affected (0.00 sec)
11.
Melihat database yang telah ada : mysql> show databases; +----------------------------+ | Database | +----------------------------+ | information_schema | | 141051109_emasetiyaningrum | | 141051109_sn2 | | cdcol | | coba | | kampus | | mysql | | phpmyadmin | | sn2 | | test | +----------------------------+ 10 rows in set (0.01 sec)
12.
Menghapus database : mysql> drop database coba; Query OK, 0 rows affected (0.09 sec) mysql> show databases; +----------------------------+ | Database | +----------------------------+ | information_schema | | 141051109_emasetiyaningrum | | 141051109_sn2 | | cdcol | | kampus | | mysql | | phpmyadmin | | sn2 | | test | +----------------------------+ 9 rows in set (0.00 sec)
MATERI 2 TABEL dan MANIPULASI DATA 1. Tabel dengan PRIMARY KEY PRIMARY KEY => untuk menjadikan field kunci agar tidak terjadi duplikasi data. Syntax : mysql> create table t_pegawai(id_pegawai varchar(12) primary key, nama_peg varch ar(20), alamat_peg varchar(50)); Query OK, 0 rows affected (0.08 sec) mysql> desc t_pegawai; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id_pegawai | varchar(12) | NO | PRI | NULL | | | nama_peg | varchar(20) | YES | | NULL | | | alamat_peg | varchar(50) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> select*from t_pegawai; +------------+----------+------------+ | id_pegawai | nama_peg | alamat_peg | +------------+----------+------------+ | HRD-001 | Chandra | Jakarta | | HRD-002 | Cyndy | Bandung | +------------+----------+------------+ 2 rows in set (0.00 sec) mysql> insert into t_pegawai values('HRD-002', 'Cinthia', 'Semarang'); ERROR 1062 (23000): Duplicate entry 'HRD-002' for key 'PRIMARY' 2. Memberikan nilai UNIQUE - memberikan batasan bahwa nilai dalam sebuah tidak ada data yg nilainya sama (distinct).
kolom
harus
mysql> create table t_parkir(id_parkir varchar(5) primary key, plat_no varchar(1 0), merk varchar(10), UNIQUE(plat_no)); Query OK, 0 rows affected (0.06 sec) mysql> desc t_parkir; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id_parkir | varchar(5) | NO | PRI | NULL | | | plat_no | varchar(10) | YES | UNI | NULL | | | merk | varchar(10) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
mysql> select*from t_parkir; +-----------+-----------+-------+ | id_parkir | plat_no | merk | +-----------+-----------+-------+ | 11101 | K-6883-US | HONDA | | 11102 | K-3545-US | HONDA | +-----------+-----------+-------+ 2 rows in set (0.00 sec) mysql> insert into t_parkir values('11103','K-6883-US','Yahama'); ERROR 1062 (23000): Duplicate entry 'K-6883-US' for key 'plat_no' 3. Parameter IF NOT EXISTS - untuk mengecek apakah sebuah tabel dengan nama yang sama sudah ada atau belum sebelum membuat tabel. mysql> show tables; +---------------+ | Tables_in_sn2 | +---------------+ | t_mhs | | t_parkir | | t_pegawai | +---------------+ 3 rows in set (0.00 sec) mysql> create table IF NOT EXISTS t_pegawai(id_peg varchar(8) primary key, nama_peg varchar(25), alamat_peg varchar(50)); Query OK, 0 rows affected, 1 warning (0.00 sec) 4. Menyalin tabel dengan statement SELECT - membuat tabl baru dengan meng-copy isi dari tabel sudah ada, semisal kita menggunakan tabel t_parkir : mysql> select*from t_parkir; +-----------+-----------+-------+ | id_parkir | plat_no | merk | +-----------+-----------+-------+ | 11101 | K-6883-US | HONDA | | 11102 | K-3545-US | HONDA | +-----------+-----------+-------+ 2 rows in set (0.00 sec) Code.. mysql> create table t_parkir_copy as select*from t_parkir; Query OK, 2 rows affected (0.39 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select*from t_parkir_copy; +-----------+-----------+-------+ | id_parkir | plat_no | merk | +-----------+-----------+-------+ | 11101 | K-6883-US | HONDA | | 11102 | K-3545-US | HONDA | +-----------+-----------+-------+ 2 rows in set (0.00 sec)
yang
5. Membuat TEMPORARY TABLE -
Temporary table adalah pembuatan tabel secara sementara, jadi tabel tsb hanya ada saat kita membuat sampai kita mematikan SQL kita.
mysql> create temporary table t_temporary(id int(8) Query OK, 0 rows affected (0.00 sec) mysql> desc t_temporary; +-------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------+------+-----+---------+-------+ | id | int(8) | YES | | NULL | | +-------+--------+------+-----+---------+-------+ 1 row in set (0.02 sec) Jika kita lihat dengan perintah „show tables‟ maka temporary tabel tidak akan muncul mysql> show tables; +---------------+ | Tables_in_sn2 | +---------------+ | t_mhs | | t_parkir | | t_parkir_copy | | t_pegawai | +---------------+ 4 rows in set (0.00 sec) Tetapi kita bisa menginputkan data ke temporary tabel, example : mysql> insert into t_temporary values('111111'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_temporary values('111112'); Query OK, 1 row affected (0.00 sec) mysql> select*from t_temporary; +--------+ | id | +--------+ | 111111 | | 111112 | +--------+ 2 rows in set (0.02 sec) Tetapi jika kita tutup MySQL maka temporary tabel akan langsung hilang mysql> \q Bye mysql> use sn2; Database changed mysql> select*from t_temporary; ERROR 1146 (42S02): Table 'sn2.t_temporary' doesn't exist
6. Tabel dengan FOREIGN KEY -
satu atau beberapa kolom pada tabel yang merupakan primary key pada tabel satu yang direlasikan pada tabel yang lain. Misal kita punya 2 tabel yang saling berkaitan contoh : -
tabel induk : t_mobil dengan field(id_mobil, type)
-
tabel anak
: t_stok dengan field(id_stok, id_mobil, stok)
Kita buat tabel induk : mysql> create table t_mobil(id_mobil varchar(8) primary key, type varchar(20)); Query OK, 0 rows affected (0.08 sec) mysql> desc t_mobil; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id_mobil | varchar(8) | NO | PRI | NULL | | | type | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select*from t_mobil; +----------+--------+ | id_mobil | type | +----------+--------+ | HON-01 | Jazz | | TOY-01 | Avanza | | TOY-02 | Innova | +----------+--------+ 3 rows in set (0.00 sec) mysql> create table t_stok(id_stok varchar(8) primary key, id_mobil varchar(8), stok int(5), foreign key(id_mobil) references t_mobil(id_mobil)); Query OK, 0 rows affected (0.41 sec) mysql> desc t_stok; +----------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------+------+-----+---------+-------+ | id_stok | varchar(8) | NO | PRI | NULL | | | id_mobil | varchar(8) | YES | MUL | NULL | | | stok | int(5) | YES | | NULL | | +----------+------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> insert into t_stok values('ST-001','TOY-01','1000'),('ST-002','TOY02','3 13'),('ST-003','HON-01','875'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select*from t_stok; +---------+----------+------+ | id_stok | id_mobil | stok | +---------+----------+------+ | ST-001 | TOY-01 | 1000 | | ST-002 | TOY-02 | 313 | | ST-003 | HON-01 | 875 | +---------+----------+------+ 3 rows in set (0.00 sec) mysql> delete from t_mobil where id_mobil='TOY-01';
7. Memodifikasi tabel dengan ALTER TABLE, meliputi : a. Menambah kolom pada tabel - menambah kolom pada tabel bisa menggunakan perintah alter table. mysql> alter table t_mhs add semester int(1); Query OK, 5 rows affected (0.08 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> desc t_mhs; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | nim | varchar(10) | YES | | NULL | | | nama_mhs | varchar(20) | YES | | NULL | | | alamat | varchar(50) | YES | | NULL | | | semester | int(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) Kita juga bisa mengatur letak kolom yang akan kita buat mysql> alter table t_mhs add agama varchar(10) after nama_mhs; Query OK, 5 rows affected (0.39 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> desc t_mhs; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | nim | varchar(10) | YES | | NULL | | | nama_mhs | varchar(20) | YES | | NULL | | | agama | varchar(10) | YES | | NULL | | | alamat | varchar(50) | YES | | NULL | | | semester | int(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) b. Memodifikasi nama tabel - Jika kita ingin mengganti definisi tabel dari t_mhs maka caranya : mysql> alter table t_mhs rename to mahasiswa; Query OK, 0 rows affected (0.01 sec) mysql> desc mahasiswa; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | nim | varchar(10) | YES | | NULL | | | nama_mhs | varchar(20) | YES | | NULL | | | agama | varchar(10) | YES | | NULL | | | alamat | varchar(50) | YES | | NULL | | | semester | int(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)
c. Memodifikasi definisi tabel -
Jika kita ingin maka caranya :
mengganti
definisi
tabel
dari
mahasiswa
mysql> alter table mahasiswa modify agama varchar(12), modify alamat varchar(55) ; Query OK, 5 rows affected (0.42 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> desc mahasiswa; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | nim | varchar(10) | YES | | NULL | | | nama_mhs | varchar(20) | YES | | NULL | | | agama | varchar(12) | YES | | NULL | | | alamat | varchar(55) | YES | | NULL | | | semester | int(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec) d. Mengganti nama kolom - Memodifikasi nama kolom contohnya jika kita ingin mengganti nama kolom dari tabl mahasiswa maka caranya : mysql> alter table mahasiswa change varchar(20); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0
nama_mhs
nm_mhs
mysql> desc mahasiswa; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | nim | varchar(10) | YES | | NULL | | | nm_mhs | varchar(20) | YES | | NULL | | | agama | varchar(12) | YES | | NULL | | | alamat | varchar(55) | YES | | NULL | | | semester | int(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec) e. Menghapus kolom -
Cara untuk mnghapus salah satu kolom dalam tabel mahasiswa:
mysql> alter table mahasiswa drop agama; Query OK, 5 rows affected (0.11 sec) Records: 5 Duplicates: 0 Warnings: 0
mysql> desc mahasiswa; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | nim | varchar(10) | YES | | NULL | | | nm_mhs | varchar(20) | YES | | NULL | | | alamat | varchar(55) | YES | | NULL | | | semester | int(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) f. Menambah& menghapus primary key i.
Menambah Primary Key mysql> alter table mahasiswa add primary key(nim); Query OK, 5 rows affected (0.09 sec) Records: 5 Duplicates: 0 Warnings: 0
mysql> desc mahasiswa; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | nim | varchar(10) | NO | PRI | | | | nm_mhs | varchar(20) | YES | | NULL | | | alamat | varchar(55) | YES | | NULL | | | semester | int(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) ii. Menghapus Primary Key mysql> alter table mahasiswa drop primary key; Query OK, 5 rows affected (0.11 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> desc mahasiswa; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | nim | varchar(10) | NO | | | | | nm_mhs | varchar(20) | YES | | NULL | | | alamat | varchar(55) | YES | | NULL | | | semester | int(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) g. Menghapus semua data dalam tabel -
Untuk menghapus seluruh isi menggunakan perintah TRUNCATE :
mysql> select*from t_parkir_copy; +-----------+-----------+-------+ | id_parkir | plat_no | merk | +-----------+-----------+-------+ | 11101 | K-6883-US | HONDA | | 11102 | K-3545-US | HONDA | +-----------+-----------+-------+ 2 rows in set (0.03 sec)
data
dalam
tabel
maka
mysql> truncate table t_parkir_copy; Query OK, 0 rows affected (0.00 sec) mysql> select*from t_parkir_copy; Empty set (0.02 sec) 8. Memasukkan data lebih dari 1 data dengan INSERT mysql> insert into t_parkir values('11103','H-3130KA','YAMAHA'),('11104','K-8888-RA','SUZUKI'),('11105','K-2222A','HONDA'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select*from t_parkir; +-----------+-----------+--------+ | id_parkir | plat_no | merk | +-----------+-----------+--------+ | 11101 | K-6883-US | HONDA | | 11102 | K-3545-US | HONDA | | 11103 | H-3130-KA | YAMAHA | | 11104 | K-8888-RA | SUZUKI | | 11105 | K-2222-A | HONDA | +-----------+-----------+--------+ 5 rows in set (0.00 sec) 9. INSERT menggunakan data dari tabel lain mysql> create table t_user(no_plat varchar(10)); Query OK, 0 rows affected (0.08 sec)
varchar(10),
mysql> insert into t_user(no_plat, merk_kend) merk from t_parkir; Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0
select
merk_kend
plat_no,
mysql> select*from t_user; +-----------+-----------+ | no_plat | merk_kend | +-----------+-----------+ | K-6883-US | HONDA | | K-3545-US | HONDA | | H-3130-KA | YAMAHA | | K-8888-RA | SUZUKI | | K-2222-A | HONDA | +-----------+-----------+ 5 rows in set (0.00 sec) 10.
Mengubah data menggunakan UPDATE
mysql> update t_user set merk_kend='KAWASAKI' where no_plat='K2222-A'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select*from t_user; +-----------+-----------+ | no_plat | merk_kend | +-----------+-----------+ | K-6883-US | HONDA | | K-3545-US | HONDA | | H-3130-KA | YAMAHA | | K-8888-RA | SUZUKI | | K-2222-A | KAWASAKI | +-----------+-----------+ 5 rows in set (0.00 sec) 11.
Mengganti data dari tabel
mysql> replace into t_parkir(id_parkir, ('11102','K-3131-WS','YAMAHA'); Query OK, 2 rows affected (0.00 sec)
plat_no,
mysql> select*from t_parkir; +-----------+-----------+--------+ | id_parkir | plat_no | merk | +-----------+-----------+--------+ | 11101 | K-6883-US | HONDA | | 11102 | K-3131-WS | YAMAHA | | 11103 | H-3130-KA | YAMAHA | | 11104 | K-8888-RA | SUZUKI | | 11105 | K-2222-A | HONDA | +-----------+-----------+--------+ 5 rows in set (0.00 sec) 12.
Menghapus data dari tabel
mysql> delete from t_parkir where id_parkir='11103'; Query OK, 1 row affected (0.00 sec) mysql> select*from t_parkir; +-----------+-----------+--------+ | id_parkir | plat_no | merk | +-----------+-----------+--------+ | 11101 | K-6883-US | HONDA | | 11102 | K-3131-WS | YAMAHA | | 11104 | K-8888-RA | SUZUKI | | 11105 | K-2222-A | HONDA | +-----------+-----------+--------+ 4 rows in set (0.00 sec)
merk)
values
MATERI 3 QUERY I.
SELECT statement mysql> create table pegawai(nip varchar(8) primary key, nama_peg varchar(20), al amat_peg varchar(50), jabatan varchar(12), gaji int(7)); Query OK, 0 rows affected (0.17 sec) mysql> desc pegawai; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | nip | varchar(8) | NO | PRI | NULL | | | nama_peg | varchar(20) | YES | | NULL | | | alamat_peg | varchar(50) | YES | | NULL | | | jabatan | varchar(12) | YES | | NULL | | | gaji | int(7) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec) 1. Mengambil data dengan SELECT mysql> select*from pegawai; +----------+-------------------+------------+--------------+---------+ | nip | nama_peg | alamat_peg | jabatan | gaji | +----------+-------------------+------------+--------------+---------+ | 11111111 | Sadika Rhama | Kalimantan | Manager | 7000000 | | 11111112 | Tinezia | Magetan | Staff Junior | 1500000 | | 11111113 | Ema Setiyaningrum | Pati | Supervisor | 5000000 | | 11111115 | Indah Dewi | Yogyakarta | Staff Senior | 2000000 | +----------+-------------------+------------+--------------+---------+ 4 rows in set (0.00 sec) mysql> select nip, nama_peg from pegawai; +----------+-------------------+ | nip | nama_peg | +----------+-------------------+ | 11111111 | Sadika Rhama | | 11111112 | Tinezia | | 11111113 | Ema Setiyaningrum | | 11111115 | Indah Dewi | +----------+-------------------+ 4 rows in set (0.00 sec)
2. Query menggunakan parameter kondisi WHERE mysql> select*from pegawai WHERE jabatan='Staff Junior'; +----------+----------+------------+--------------+---------+ | nip | nama_peg | alamat_peg | jabatan | gaji | +----------+----------+------------+--------------+---------+ | 11111112 | Tinezia | Magetan | Staff Junior | 1500000 | +----------+----------+------------+--------------+---------+ 1 row in set (0.01 sec) mysql> select nip, nama_peg from pegawai WHERE jabatan='Staff Senior'; +----------+------------+ | nip | nama_peg | +----------+------------+ | 11111115 | Indah Dewi | +----------+------------+ 1 row in set (0.00 sec)
3. Query menggunakan beberapa parameter kondisional mysql> select*from pegawai where alamat_peg='Kalimantan' gaji>2000000; +----------+--------------+------------+---------+---------+ | nip | nama_peg | alamat_peg | jabatan | gaji | +----------+--------------+------------+---------+---------+ | 11111111 | Sadika Rhama | Kalimantan | Manager | 7000000 | +----------+--------------+------------+---------+---------+ 1 row in set (0.00 sec)
AND
4. Memberikan alias hasil query pada SELECT mysql> select nama_peg AS nama_pegawai, alamat_peg AS asal from pegawai where al amat_peg='Pati'; +-------------------+------+ | nama_pegawai | asal | +-------------------+------+ | Ema Setiyaningrum | Pati | +-------------------+------+ 1 row in set (0.00 sec)
5. Query data bertipe teks dengan pattern matching mysql> select nama_peg from pegawai where jabatan like 'M%'; +--------------+ | nama_peg | +--------------+ | Sadika Rhama | +--------------+ 1 row in set (0.00 sec) mysql> select nama_peg from pegawai where jabatan like 'M______'; +--------------+ | nama_peg | +--------------+ | Sadika Rhama | +--------------+ 1 row in set (0.00 sec) mysql> select nama_peg from pegawai where nama_peg like '%A'; +--------------+ | nama_peg | +--------------+ | Sadika Rhama | | Tinezia | +--------------+ 2 rows in set (0.00 sec)
6. Query data unik menggunakan DISTINCT mysql> select distinct alamat_peg from pegawai; +------------+ | alamat_peg | +------------+ | Kalimantan | | Magetan | | Pati | | Yogyakarta | +------------+ 4 rows in set (0.00 sec)
7. Membatasi hasil query dengan LIMIT mysql> select * from pegawai LIMIT 3; +----------+-------------------+------------+--------------+---------+ | nip | nama_peg | alamat_peg | jabatan | gaji | +----------+-------------------+------------+--------------+---------+ | 11111111 | Sadika Rhama | Kalimantan | Manager | 7000000 | | 11111112 | Tinezia | Magetan | Staff Junior | 1500000 | | 11111113 | Ema Setiyaningrum | Pati | Supervisor | 5000000 | +----------+-------------------+------------+--------------+---------+ 3 rows in set (0.00 sec)
8. Mengelompokkan hasil query menggunakan GROUP BY mysql> select * from pegawai GROUP BY gaji; +----------+-------------------+------------+--------------+---------+ | nip | nama_peg | alamat_peg | jabatan | gaji | +----------+-------------------+------------+--------------+---------+ | 11111112 | Tinezia | Magetan | Staff Junior | 1500000 | | 11111115 | Indah Dewi | Yogyakarta | Staff Senior | 2000000 | | 11111113 | Ema Setiyaningrum | Pati | Supervisor | 5000000 | | 11111111 | Sadika Rhama | Kalimantan | Manager | 7000000 | +----------+-------------------+------------+--------------+---------+ 4 rows in set (0.02 sec)
9. Mendapatkan COUNT()
jumlah
anggota
setiap
kelompok
menggunakan
mysql> select gaji, count(*) FROM pegawai GROUP BY gaji; +---------+----------+ | gaji | count(*) | +---------+----------+ | 1500000 | 1 | | 2000000 | 1 | | 5000000 | 1 | | 7000000 | 1 | +---------+----------+ 4 rows in set (0.00 sec)
10. Parameter kondisional menggunakan HAVING mysql> select*from pegawai HAVING gaji > 3500000; +----------+-------------------+------------+------------+---------+ | nip | nama_peg | alamat_peg | jabatan | gaji | +----------+-------------------+------------+------------+---------+ | 11111111 | Sadika Rhama | Kalimantan | Manager | 7000000 | | 11111113 | Ema Setiyaningrum | Pati | Supervisor | 5000000 | +----------+-------------------+------------+------------+---------+ 2 rows in set (0.00 sec)
11. Mengurutkan hasil query menggunakan ORDER BY mysql> select*from pegawai ORDER BY nama_peg; +----------+-------------------+------------+--------------+---------+ | nip | nama_peg | alamat_peg | jabatan | gaji | +----------+-------------------+------------+--------------+---------+ | 11111113 | Ema Setiyaningrum | Pati | Supervisor | 5000000 | | 11111115 | Indah Dewi | Yogyakarta | Staff Senior | 2000000 | | 11111111 | Sadika Rhama | Kalimantan | Manager | 7000000 | | 11111112 | Tinezia | Magetan | Staff Junior | 1500000 | +----------+-------------------+------------+--------------+---------+ 4 rows in set (0.00 sec)
12. Mengurutkan hasil query berdasarkan lebih dari satu kolom
13. Kombinasi ORDER BY dengan LIMIT mysql> select*from pegawai ORDER BY gaji LIMIT 3; +----------+-------------------+------------+--------------+---------+ | nip | nama_peg | alamat_peg | jabatan | gaji | +----------+-------------------+------------+--------------+---------+ | 11111112 | Tinezia | Magetan | Staff Junior | 1500000 | | 11111115 | Indah Dewi | Yogyakarta | Staff Senior | 2000000 | | 11111113 | Ema Setiyaningrum | Pati | Supervisor | 5000000 | +----------+-------------------+------------+--------------+---------+ 3 rows in set (0.00 sec)
14. Operator BETWEEN mysql> select*from pegawai WHERE gaji BETWEEN 2500000 AND 7000000; +----------+-------------------+------------+------------+---------+ | nip | nama_peg | alamat_peg | jabatan | gaji | +----------+-------------------+------------+------------+---------+ | 11111111 | Sadika Rhama | Kalimantan | Manager | 7000000 | | 11111113 | Ema Setiyaningrum | Pati | Supervisor | 5000000 | +----------+-------------------+------------+------------+---------+ 2 rows in set (0.00 sec)
15. MAX, MIN, AVERAGE data dari tabel mysql> select MIN(gaji) from pegawai; +-----------+ | MIN(gaji) | +-----------+ | 1500000 | +-----------+ 1 row in set (0.00 sec) mysql> select MAX(gaji) from pegawai; +-----------+ | MAX(gaji) | +-----------+ | 7000000 | +-----------+ 1 row in set (0.00 sec) mysql> select AVG(gaji) from pegawai; +--------------+ | AVG(gaji) | +--------------+ | 3875000.0000 | +--------------+ 1 row in set (0.00 sec) mysql> select SUM(gaji) from pegawai; +-----------+ | SUM(gaji) | +-----------+ | 15500000 | +-----------+ 1 row in set (0.00 sec)
II.
Sub Query 1. Sub query dengan ALL 2. Sub query dengan ANY 3. Sub query dengan EISTS 4. Sub query dengan IN
III. Table Join Dalam MySQL, table join digunakan untuk melakukan query terhadap dua atau lebih tabel yang memiliki relasi dalam suatu basis data relasional. 1. Cross Join Cross join atau nama lain dari Cartesian join, pada cross join, semua data dalam tabel yang pertama dengan semua data pada tabel yang kedua. 2. Equi-Join / Inner Join
dipasangkan
Inner join menggabungkan tabel dengan membandingkan nilai yang sama antara dua buah kolom, kolom yang dibandingkan dapat kita spesifikasikan. 3. Natural Join Natural join
sebenarnya
mirip
dengan
inner
join,
namun
kita tidak perlu men-spesifikasikan kolom mana yang ingin kita bandingkan. Secara otomatis, MySQL akan mencari kolom pada 2 tabel yang memiliki nilai yang sama dan membandingkannya. 4. Left and Right Join Digunakan untuk menghindari data yang hilang karena munngkin ada data yang belum direferensi oleh tabel yang lain. 5. Update menggunakan join table Kita dapat melakukan update menggunakan join tabel, bentuk syntaknya : UPDATE
, SET = „new_value‟ WHERE 6. Delete menggunakan join table Kita dapat melakukan delete menggunakan join tabel.
MATERI 4 VIEW and TRIGGER I.
VIEW a. Algorithm Attributes b. Updatable View c. Klausa WITH CHECK OPTION d. Merubah View e. Melihat definisi pembuatan view
II.
TRIGGER a. Membuat trigger b. BEFORE TRIGGER c. AFTER TRIGGER d. Melihat trigger yang sudah dibuat
MATERI5 FUNCTION and PROCEDURE I.
Intro Stored Routine
II.
FUNCTION a. Membuat function b. Melihat function yang telah dibuat c. Menghapus function
III. PROCEDURE a. Membuat procedure b. Parameter dalam procedure 1. Parameter IN 2. Parameter OUT 3. Parameter INOUT 4. Melihat procedure yang telah dibuat 5. Menghapus procedure IV.
Pemrograman diFunction dan Procedure a. Variabel b. Kendali Kondisional 1. Kendali IF 2. Kendali CASE c. Perulangan 1. Perulangan WHILE 2. Perulangan REPEAT .. UNTIL 3. Perulangan LOOP
