Perintah SQL part 2

 

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;

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

| Field      | Type        | Null | Key | Default | Extra |

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

| id_peg     | varchar(8)  | NO   | PRI | NULL    |       |

| nama_peg   | varchar(50) | YES  |     | NULL    |       |

| alamat_peg | varchar(50) | YES  |     | NULL    |       |

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

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;

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

| Field     | Type        | Null | Key | Default | Extra |

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

| id_parkir | varchar(5)  | NO   | PRI | NULL    |       |

| plat_no   | varchar(12) | YES  | UNI | NULL    |       |

| merk      | varchar(10) | YES  |     | NULL    |       |

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

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;

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

| Field | Type   | Null | Key | Default | Extra |

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

| 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;

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

| Field    | Type        | Null | Key | Default | Extra |

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

| id_mobil | varchar(8)  | NO   | PRI | NULL    |       |

| type     | varchar(20) | YES  |     | NULL    |       |

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

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;

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

| 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)

       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;

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

| 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)

      

       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;

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

| 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)

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

Tweet

Artikel keren lainnya:

Ditulis oleh kata-quotes pada tanggal Thursday, August 27, 2015