Hasil Praktikum - Bagian 2 : Kunci - kunci dan perintah dalam MySQL

1.    PRIMARY KEY

Membuat tabel primary key

mysql> create table t_pegawai1(id_pegawai varchar(12) primary key, nama_peg varchar(50),

alamat_peg varchar(50));

Query OK, 0 rows affected (0.05 sec)

mysql> show tables;

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

| Tables_in_km2 |

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

| t_buku        |

| t_mhs1        |

| t_pegawai1    |

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

3 rows in set (0.00 sec)

mysql> insert into t_pegawai1 values ('HRD-001','Ade','Sentolo');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t_pegawai1 values ('HRD-002','Rian','Wates');

Query OK, 1 row affected (0.02 sec)

mysql> insert into t_pegawai1 values ('HRD-003','Utomo','Sedayu');

Query OK, 1 row affected (0.03 sec)

mysql> select*from t_pegawai1;

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

| id_pegawai | nama_peg | alamat_peg |

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

| HRD-001    | Ade      | Sentolo    |

| HRD-002    | Rian     | Wates      |

| HRD-003    | Utomo    | Sedayu     |

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

3 rows in set (0.00 sec)

2. UNIQUE

mysql> create table t_parkir1(id_parkir varchar(5) primary key, plat_no varchar(12),

merk varchar(10),unique(plat_no));

Query OK, 0 rows affected (0.06 sec)

mysql> desc t_parkir1;

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

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

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

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

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

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

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

3 rows in set (0.00 sec)

mysql> insert into t_parkir1 values ('00111','AB 1234 CD','Outlander');

Query OK, 1 row affected (0.03 sec)

mysql> insert into t_parkir1 values ('00112','AB 4321 CD','Civic');

Query OK, 1 row affected (0.01 sec)

mysql> select*from t_parkir1;

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

| id_parkir | plat_no    | merk      |

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

| 00111     | AB 1234 CD | Outlander |

| 00112     | AB 4321 CD | Civic     |

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

2 rows in set (0.00 sec)

mysql> insert into t_parkir1 values ('00114','AB 4321 CD','Honda');

ERROR 1062 (23000): Duplicate entry 'AB 4321 CD' for key 2  //tidak dapat menginput data baru karena plat nomor mobil tersebut sudah ada di data

4. IF NOT EXISTS

mysql> create table IF NOT EXISTS t_pegawai1(id_parkir varchar(5) primary key, plat_no

varchar(12), merk varchar(10),unique(plat_no));

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

5. COPY

Mengcopy/menyalin tabel dari tabel lain beserta isi data-datanya

mysql> create table t_parkir1_copy as select*from t_parkir1;

Query OK, 2 rows affected (0.08 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select*from t_parkir1_copy; menampilkan data dari tabel hasil copy tadi

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

| id_parkir | plat_no    | merk      |

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

| 00111     | AB 1234 CD | Outlander |

| 00112     | AB 4321 CD | Civic     |

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

2 rows in set (0.02 sec)

6. TEMPORARY KEY

Digunakan untuk menyimpan data sementara

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

Query OK, 0 rows affected (0.05 sec)

mysql> desc t_temporary; value dalam tabel

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

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

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

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

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

1 row in set (0.00 sec)

mysql> show tables;

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

| Tables_in_km2  |

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

| t_buku         |

| t_mhs1         |

| t_parkir1      |

| t_parkir1_copy |

| t_pegawai1     |

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

5 rows in set (0.00 sec)

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

Query OK, 1 row affected (0.02 sec)

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

Query OK, 1 row affected (0.00 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.03 sec)

mysql> select*from t_temporary;

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

| id       |

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

| 11100010 |

| 11100011 |

| 11100012 |

| 11100013 |

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

4 rows in set (0.00 sec)

7. FOREIGN KEY

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

Query OK, 0 rows affected (0.08 sec)

mysql> insert into t_mobil values ('HON-001','CIVIC');

Query OK, 1 row affected (0.03 sec)

mysql> insert into t_mobil values ('TOY-001','AVANZA');

Query OK, 1 row affected (0.02 sec)

mysql> insert into t_mobil values ('TOY-002','SOLUNA');

Query OK, 1 row affected (0.03 sec)

mysql> select*from t_mobil;

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

| id_mobil | type   |

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

| HON-001  | CIVIC  |

| TOY-001  | AVANZA |

| TOY-002  | SOLUNA |

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

3 rows in set (0.00 sec)

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

Query OK, 0 rows affected (0.08 sec)

mysql> insert into t_mobil values ('HON-001','CIVIC');

Query OK, 1 row affected (0.03 sec)

mysql> insert into t_mobil values ('TOY-001','AVANZA');

Query OK, 1 row affected (0.02 sec)

mysql> insert into t_mobil values ('TOY-002','SOLUNA');

Query OK, 1 row affected (0.03 sec)

mysql> select*from t_mobil;

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

| id_mobil | type   |

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

| HON-001  | CIVIC  |

| TOY-001  | AVANZA |

| TOY-002  | SOLUNA |

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

3 rows in set (0.00 sec)

mysql> create table t_stok1(id_stok varchar(8) primary key, id_mobil varchar(8), stok int(5), foreign key(id_mobil) references t_mobil(id_mobil));  (Membuat tabel dengan foreign key)

Query OK, 0 rows affected (0.09 sec)

mysql> create table t_stok1(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.09 sec)

mysql> insert into t_stok1 values ('ST-001','HON-001','1000');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t_stok1 values ('ST-002','TOY-001','500');

Query OK, 1 row affected (0.03 sec)

mysql> insert into t_stok1 values ('ST-003','TOY-002','435');

Query OK, 1 row affected (0.03 sec)

mysql> select*from t_stok1;

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

| id_stok | id_mobil | stok |

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

| ST-001  | HON-001  | 1000 |

| ST-002  | TOY-001  |  500 |

| ST-003  | TOY-002  |  435 |

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

3 rows in set (0.00 sec)

mysql> delete from t_mobil where id_mobil='HON-001';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`km2/t_stok1`, CONSTRAINT `t_stok1_ibfk_1` FOREIGN KEY (`id_mobil`) REFERENCES `t_mobil` (`id_mobil`))

 menjelaskan bahwa tabel tidak dapat di hapus karena merupakan primay key pada tabel t_mobil sedangkan pada tabel t_stok1 merupakan kunci tamu sehingga tidak memilki hak akses untuk menghapus data pada primary key

mysql> drop table t_stok1; (men-drop table t_stok1)

Query OK, 0 rows affected (0.03 sec)

mysql> create table t_stok1(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> create table t_stok1(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)

8.MENGHAPUS DATA

mysql> delete from t_mobil where id_mobil='HON-001';

Query OK, 1 row affected (0.02 sec)

mysql> select*from t_mobil;  (menghapus salah satu data dalam tabel)

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

| id_mobil | type   |

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

| TOY-001  | AVANZA |

| TOY-002  | SOLUNA |

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

2 rows in set (0.00 sec)