efi pantu: 2013

MATERI PART 2 – Prak. BASISDATA

TABEL dan MANIPULASI DATA

LABORATORIUM 4 – BASISDATA

MENU PART INI :

1. Tabel dengan PRIMARY KEY

2. Memberikan nilai UNIQUE

3. Parameter IF NOT EXISTS

4. Menyalin tabel dengan statement SELECT

5. Membuat TEMPORARY TABLE

6. Tabel dengan FOREIGN KEY

7. Memodifikasi tabel dengan ALTER TABLE

i. Menambah kolom pada tabel

ii. Memodifikasi nama tabel

iii. Memodifikasi definisi tabel

iv. Mengganti nama kolom

v. Menghapus kolom

vi. Menambah primary key

vii. Menghapus semua data dalam tabel

8. Memasukkan data lebih dari 1 data dengan INSERT

9. INSERT menggunakan data dari tabel lain

10. Mengubah data menggunakan UPDATE

11. Mengganti data dari tabel

12. Menghapus data dari tabel

1. Tabel dengan PRIMARY KEY

PRIMARY KEY

- untuk menjadikan filed kunci agar tidak terjadi duplikasi data

CODE :

mysql> create table t_pegawai(id_peg varchar(8) primary key, nama_peg varchar(50), alamat_peg varchar(50));

Query OK, 0 rows affected (0.07 sec)

LIHAT DESKRIPSINYA…

mysql> desc t_pegawai;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

ISI TABELNYA

mysql> select*from t_pegawai;

+---------+----------+------------+

| id_peg | nama_peg | alamat_peg |

+---------+----------+------------+

| HRD-001 | Chandra | Jakarta |

| HRD-002 | Cindy | Bandung |

+---------+----------+------------+

2 rows in set (0.00 sec)

LIHAT ERRORNYA..

mysql> insert into t_pegawai values('HRD-001','Budi','Jogja');

ERROR 1062 (23000): Duplicate entry 'HRD-001' for key 'PRIMARY'

2. Table dengan nilai UNIQUE

- memberikan batasan bahwa nilai dalam sebuah kolom harus distinct (atau tidak ada data yang nilainya sama)

CODE:

mysql> create table t_parkir(id_parkir varchar(5) primary key, plat_no varchar(12), merk varchar(10), UNIQUE(plat_no));

Query OK, 0 rows affected (0.09 sec)

LIHAT DESKRIPSINYA…

mysql> desc t_parkir;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

ISI TABEL

mysql> select*from t_parkir;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

+-----------+------------+--------+

2 rows in set (0.00 sec)

LIHAT ERRORNYA..

mysql> insert into t_parkir values('11103','AB-1234-OP','JAGUAR');

ERROR 1062 (23000): Duplicate entry 'AB-1234-OP' 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_minggu2 |

+-------------------+

| t_parkir |

| t_pegawai |

+-------------------+

2 rows in set (0.00 sec)

CODE..

mysql> create table IF NOT EXISTS t_pegawai(id_peg varchar(8) primary key, nama_peg varchar(50), alamat_peg varchar(50));

Query OK, 0 rows affected, 1 warning (0.00 sec)

4. Menyalin tabel dengan statement SELECT

Kita juga bisa membuat tabel baru dengan mengkopy isi dari tabel yang sudah ada, caranya

Pastikan kita sudah punya tabel yang sudah ada isinya..

Contoh :

Kita gunakan table t_parkir

mysql> select*from t_parkir;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

+-----------+------------+--------+

2 rows in set (0.00 sec)

Lalu kita buat tabel baru dengan nama t_parkir_copy dengan mengduplikasi seluruh isi dari tabel t_parkir, caranya :

CODE..

mysql> create table t_parkir_copy as select*from t_parkir;

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

Lalu kita lihat hasil dari tabel t_parkir_copy :

mysql> select*from t_parkir_copy;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

+-----------+------------+--------+

2 rows in set (0.00 sec)

Kita bisa lihat isi dari tabel t_parkir_copy sama dengan isi tabel dari t_parkir.

5. Membuat TEMPORARY TABLE

Temporary table adalah pembuatan table secara temporary atau sementara, jadi tabel tersebut akan ada sewaktu kita buat hingga kita mematikan SQL kita.

Sebagai contoh kita akan membuat tabel t_temporary

CODE:

mysql> create temporary table t_temporary(id int(8));

Query OK, 0 rows affected (0.09 sec)

Lihat strukturnya..

mysql> desc t_temporary;

+-------+--------+------+-----+---------+-------+

+-------+--------+------+-----+---------+-------+

| id | int(8) | YES | | NULL | |

+-------+--------+------+-----+---------+-------+

1 row in set (0.01 sec)

Jika kita lihat dengan perintah SHOW TABLES maka temporary tabel tidak akan muncul

mysql> show tables;

+-------------------+

| Tables_in_minggu2 |

+-------------------+

| t_parkir |

| t_parkir_copy |

| t_pegawai |

+-------------------+

3 rows in set (0.00 sec)

Tetapi kita bisa menginputkan nilai ke temporary table, contohnya :

mysql> insert into t_temporary values('11100010');

Query OK, 1 row affected (0.03 sec)

mysql> insert into t_temporary values('11100011');

Query OK, 1 row affected (0.02 sec)

mysql> insert into t_temporary values('11100012');

Query OK, 1 row affected (0.02 sec)

mysql> insert into t_temporary values('11100013');

Query OK, 1 row affected (0.01 sec)

mysql> select*from t_temporary;

+----------+

| id |

+----------+

| 11100010 |

| 11100011 |

| 11100012 |

| 11100013 |

+----------+

4 rows in set (0.00 sec)

Tapi jika kita matikan atau tutup MySQL kita maka temporary tabel akan langsung hilang..

mysql> \q

bye

mysql> use minggu2;

Database changed

mysql> select*from t_temporary;

ERROR 1146 (42S02): Table 'minggu2.t_temporary' doesn't exist

6. Tabel dengan 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

Misal kita punya dua 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 dulu tabel induknya :

mysql> create table t_mobil(id_mobil varchar(8) primary key, type varchar(20));

Query OK, 0 rows affected (0.07 sec)

mysql> desc t_mobil;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

Isi tabel :

mysql> select*from t_mobil;

+----------+--------+

| id_mobil | type |

+----------+--------+

| HON-01 | Jazz |

| TOY-01 | Avanza |

| TOY-02 | Innova |

+----------+--------+

3 rows in set (0.00 sec)

Buat tabel anak :

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.08 sec)

mysql> desc t_stok;

+----------+------------+------+-----+---------+-------+

+----------+------------+------+-----+---------+-------+

| stok | int(5) | YES | | NULL | |

+----------+------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

ISI TABEL :

mysql> select*from t_stok;

+---------+----------+------+

| id_stok | id_mobil | stok |

+---------+----------+------+

| ST-001 | TOY-01 | 1000 |

| ST-002 | TOY-02 | 521 |

| ST-003 | HON-01 | 875 |

+---------+----------+------+

3 rows in set (0.00 sec)

Kita akan buktikan apakah FOREIGN KEY kita berfungsi dengan baik, kita bisa menggunakan code :

mysql> delete from t_mobil where id_mobil='TOY-01';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`minggu2`. t_stok`, CONSTRAINT `t_stok_ibfk_1` FOREIGN KEY (`id_mobil`) REFERENCES `t_mobil` (`id_mobil`))

Maksudnya adalah tidak dapat menghapus atau mengupdate kolom pada table induk karena bereferensi pada table t_stok.

Lalu bagaimana solusinya?

Pada MYSQL, kita harus menambahkan perintah ON DELETE [opsi] dan ON UPDATE [opsi]pada table yang mereferensikan foreign key. Opsi pada perintah tersebut jelasnya dibawah ini.

A. RESTRICT

Jika tabel anak berisi nilai dalam kolom yang mengkait yang nilainya sama dengan di kolom terkait pada tabel induk, baris dalam tabel induk tidak bisa dihapus, dan nilai di kolom terkait tidak dapat diupdate. Ini adalah opsi default jika klausa ON DELETE atau ON UPDATE tidak dispesifikasikan.

B. CASCADE

Baris-baris dalam tabel anak yang berisi nilai-nilai yang juga terdapat dalam kolom terkait dari tabel induk dihapus ketika barisbaris yang berkaitan dihapus dari tabel induk. Baris-baris dalam tabel anak yang berisi nilai-nilai yang juga terdapat dalam kolom terkait dari tabel induk diupdate ketika nilai-nilai yang berkaitan diupdate dalam tabel induk.

C. SET NULL

Nilai-nilai dalam kolom yang mengkait dari tabel anak diset ke NULL saat baris-baris dengan data terkait dalam tabel induk dihapus dari tabel induk atau ketika data terkait dalam tabel induk diupdate. Untuk menggunakan opsi ini, semua kolom-kolom yang mengkait dalam tabel anak harus mengijinkan nilai NULL.

D. NO ACTION

Tidak ada aksi yang diambil dalam tabel anak ketika baris-baris dihapus dari tabel induk atau nilai-nilai dalam kolom terkait dalam tabel induk diupdate.

E. SET DEFAULT

Nilai-nilai dalam kolom-kolom yang mengkait dari tabel anak diset ke nilai default mereka ketika baris-baris dihapus dari tabel induk atau kolom terkait dari tabel induk diupdate.

Untuk kali ini kita akan belajar tentang CASCADE, jadi data yang terdapat di tabel induk dan tabel anak akan sama – sama terhapus atau terupdate.

Langkah pertama kita ganti struktur dari tabel anak agar memuat perintah untuk mengaktifkan “fitur” CASCADE

Hapus dulu tabel t_stok.

Mysql> drop table t_stok;

Rubah struktur tabel t_stok

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) on delete cascade on update cascade);

Query OK, 0 rows affected (0.06 sec)

mysql> desc t_stok;

+----------+------------+------+-----+---------+-------+

+----------+------------+------+-----+---------+-------+

| stok | int(5) | YES | | NULL | |

+----------+------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

Isi tabelnya lagi

mysql> insert into t_stok values('ST-001','TOY-01','1000'),('ST-002','TOY-02','521'),('ST-003','HON-01','875');

Query OK, 3 rows affected (0.01 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 | 521 |

| ST-003 | HON-01 | 875 |

+---------+----------+------+

3 rows in set (0.00 sec)

Setelah itu, kita coba delete lagi :

mysql> delete from t_mobil where id_mobil='TOY-01';

Query OK, 1 row affected (0.01 sec)

Kita lihat hasilnya…

mysql> select*from t_mobil;

+----------+--------+

Jadi setelah kita rubah struktur tabel anak, maka akan bisa ter delete.

Data di tabel anak dan induk akan sama – sama ter hapus atau ter update.

Itulah contoh penggunaan on delete cascade dan on upodate cascade

| id_mobil | type |

+----------+--------+

| HON-01 | Jazz |

| TOY-02 | Innova |

+----------+--------+

2 rows in set (0.01 sec)

mysql> select*from t_stok;

+---------+----------+------+

| id_stok | id_mobil | stok |

+---------+----------+------+

| ST-002 | TOY-02 | 521 |

| ST-003 | HON-01 | 875 |

+---------+----------+------+

2 rows in set (0.00 sec)

7. Memodifikasi Tabel dengan ALTER TABLE

a. Menambah Kolom pada Tabel

Dalam menambah kolom dalam tabel kita bisa menggunakan perintah alter table, contohnya kita akan menambahkan kolom pada tabel t_mobil :

mysql> alter table t_mobil add harga bigint(11);

Query OK, 2 rows affected (0.16 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc t_mobil;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

Kita juga bisa mengatur letak kolom yang akan kita buat

mysql> alter table t_mobil add series varchar(8) after type;

Query OK, 2 rows affected (0.12 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc t_mobil;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

b. Modifikasi Nama Tabel

Dalam memodifikasi definisi tabel contohnya jika kita ingin mengganti definisi dari t_mobil maka caranya :

mysql> alter table t_mobil rename to t_car;

Query OK, 0 rows affected (0.07 sec)

mysql> desc t_car;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

4 rows in set (0.04 sec)

c. Modifikasi Definisi Tabel

Dalam memodifikasi definisi tabel contohnya jika kita ingin mengganti definisi dari t_car maka caranya :

mysql> alter table t_car modify type varchar(15), modify series varchar(10);

Query OK, 2 rows affected (0.14 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc t_car;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

d. Mengganti Nama Kolom

Dalam memodifikasi nama kolom contohnya jika kita ingin mengganti nama kolom dari t_car maka caranya :

mysql> alter table t_car change harga harga_mobil bigint(11);

Query OK, 2 rows affected (0.14 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc t_car;

+-------------+-------------+------+-----+---------+-------+

+-------------+-------------+------+-----+---------+-------+

+-------------+-------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

e. Menghapus Kolom

Cara untuk menghapus kolom dengan perintah ALTER TABLE adalah sbb, misal kita akan menghapus salah satu kolom di t_car, maka caranya :

mysql> alter table t_car drop harga_mobil;

Query OK, 2 rows affected (0.12 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc t_car;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

f. Menghapus & Menambah Primary Key

Cara untuk menghapus & menambah primary key dengan perintah ALTER TABLE adalah sbb, misal kita akan menghapus & menambah primary key di suatu table, maka caranya :

Hapus primary key

mysql> alter table t_pegawai drop primary key;

Query OK, 0 rows affected (0.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc t_pegawai;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

Tambah primary key

mysql> alter table t_pegawai add primary key (id_peg);

Query OK, 0 rows affected (0.53 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc t_pegawai;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

g. Menghapus Semua Data dalam Tabel

Untuk menghapus seluruh isi dalam tabel menggunakan perintah TRUNCATE

Misal kita akan menghapus seluruh table dari tabel t_parkir_copy maka sytxnya sbb:

mysql> select*from t_parkir_copy;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

+-----------+------------+--------+

2 rows in set (0.03 sec)

Menggunakan perintah TRUNCATE dalam menghapus seluruh ISI tabel

mysql> truncate table t_parkir_copy;

Query OK, 0 rows affected (0.05 sec)

mysql> select*from t_parkir_copy;

Empty set (0.00 sec)

8. Memasukkan data lebih dari 1 data dengan INSERT

Perintah INSERT dalam memasukkan data juga melayani pemasukkan data lebih dari 1 data sekaligus, misal kita akan memasukkan data di table t_parkir maka syntax yang digunakan sbb:

mysql> insert into t_parkir values('11103','AB-5555-UX','HONDA'), ('11104','BH-36-B','SUZUKI'),('11105','N-7876-OK','KIA');

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select*from t_parkir;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

| 11103 | AB-5555-UX | HONDA |

| 11104 | BH-36-B | SUZUKI |

| 11105 | N-7876-OK | KIA |

+-----------+------------+--------+

5 rows in set (0.00 sec)

9. INSERT Menggunakan Data dari Tabel Lain

Perintah ini mirip dengan perintah copy tabel SELECT tapi yang membedakan perintah ini digunakan setelah terdapat tabel yang akan digunakan untuk menyimpan hasil copyan file dan bisa diatur seluruh field atau sebagian field, syntxnya adalah sbb :

Kita siapkan dulu tabel baru, bernama table t_user

mysql> create table t_user(no_plat varchar(10), merk_kend varchar(20));

Query OK, 0 rows affected (0.05 sec)

mysql> desc t_user;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

Lalu kita isi t_user dengan sebagian tabel dari t_parkir

mysql> insert into t_user(no_plat, merk_kend) select plat_no, merk from t_parkir;

Query OK, 5 rows affected (0.05 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select*from t_user;

+------------+-----------+

| no_plat | merk_kend |

+------------+-----------+

| AB-1234-OP | TOYOTA |

| B-3454-SDE | BMW |

| AB-5555-UX | HONDA |

| BH-36-B | SUZUKI |

| N-7876-OK | KIA |

+------------+-----------+

5 rows in set (0.00 sec)

10. Mengubah data menggunakan UPDATE

Mengubah data dalam suatu tabel bisa dilakukan juga tanpa menggunakan perintah ALTER TABLE yaitu menggunakan perintah UPDATE, contohnya

mysql> update t_user set merk_kend='NISSAN' where no_plat='B-3454-SDE';

Query OK, 1 row affected (0.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select*from t_user;

+------------+-----------+

| no_plat | merk_kend |

+------------+-----------+

| AB-1234-OP | TOYOTA |

| B-3454-SDE | NISSAN |

| AB-5555-UX | HONDA |

| BH-36-B | SUZUKI |

| N-7876-OK | KIA |

+------------+-----------+

5 rows in set (0.00 sec)

11. Mengganti data dari tabel

Kali ini cara mengganti data tabel tapi menggunakan perintah REPLACE, yang membedakan perintah ini dengan yang lain adalah, jika REPLACE digunakan pada tabel yang memiliki PRIMARY KEY atau nilai UNIQUE

Contoh :

mysql> replace into t_parkir( id_parkir, plat_no , merk) values ('11104','BH-36-B','ISUZU');

Query OK, 2 rows affected (0.03 sec)

mysql> select*from t_parkir;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

| 11103 | AB-5555-UX | HONDA |

| 11104 | BH-36-B | ISUZU |

| 11105 | N-7876-OK | KIA |

+-----------+------------+--------+

5 rows in set (0.00 sec)

12. Menghapus data dari tabel

Menghapus data dalam basisdata bisa menggunakan perintah DELETE,

Contoh :

mysql> delete from t_parkir where id_parkir='11105';

Query OK, 1 row affected (0.16 sec)

mysql> select*from t_parkir;

+-----------+------------+--------+

| id_parkir | plat_no | merk |

+-----------+------------+--------+

| 11101 | AB-1234-OP | TOYOTA |

| 11102 | B-3454-SDE | BMW |

| 11103 | AB-5555-UX | HONDA |

| 11104 | BH-36-B | ISUZU |

+-----------+------------+--------+

4 rows in set (0.00 sec)

efi pantu

Selasa, 17 Desember 2013

TABEL DAN MANIPULASI DATA