Disini cuma mau bikin latihan latihan dasar MySQ database sederhana sebagai latihan dan catatan. Disini kita tetapkan aja ya database yang akan kita buat dan kita pakai sebagai latihan yaitu database “KIOS KELONTONG”. Kita akan mencatatan bagaimana cara membuat database dari awal dan mengolah data yang ada.
. Buatlah database “KIOS KELONTONG”
CREATE
1. Create Database Kios Kelontong
Perintah untuk membuat database:
MySLQ > create database KIOS_KELONTONG; Query OK, 1 row affected (0.020 sec)
2. Show/Lihat list database
Perintah untuk cek apakah database kios_kelontong sudah ada pada list database:
MySQL> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | kios_kelontong | +--------------------+ 5 rows in set (0.001 sec)
3. Create Tabel
Sebelum membuat tabel pastikan kita sedang berada di dalam database kios_kelontong dengan cara :
MySQL [(none)]> use kios_kelontong; Database changed MySQL [kios_kelontong]>
Kemudian buat tabel “Barang” didalam database KIOS_KELONTONG
| FIELD | TYPE | NULL |
| KODE_BRG | CHAR(6) | PRIMARY KEY |
| NAMA_BRG | VARCHAR(30) | NOT NULL |
| HARGA_BRG | INT(8) | NOT NULL |
| JUMLAH | INT(6) | NOT NULL |
Ketikan perintah :
MySQL> create table BARANG
-> (KODE_BRG CHAR(6) PRIMARY KEY,
-> NAMA_BRG VARCHAR(30) NOT NULL,
-> HARGA_BRG INT (8) NOT NULL,
-> JUMLAH INT(6) NOT NULL);
Query OK, 0 rows affected (0.390 sec)
4. Show Field Tabel Barang
Kemudian lihat format tabel barang dengan mengetikkan perintah :
MySQL [kios_kelontong]> desc barang; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | KODE_BRG | char(6) | NO | PRI | NULL | | | NAMA_BRG | varchar(30) | NO | | NULL | | | HARGA_BRG | int(8) | NO | | NULL | | | JUMLAH | int(6) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.109 sec)
5. Insert Table
Untuk memasukkan nilai dari barang-barang yaitu dengan menggunakan perintah pada contoh berikut:
| KODE_BRG | NAMA_BRG | HARGA_BRG | JUMLAH |
| B001 | SANDAL JEPIT | 16000 | 24 |
| B002 | SAPU | 15000 | 20 |
| B003 | SABUN | 8000 | 100 |
| B004 | CHIKI | 5000 | 100 |
| B005 | KOREK API | 2500 | 50 |
MySQL > insert into BARANG
-> VALUES
-> ("B001","Sandal Jepit","16000","24"),
-> ("B002","Sapu","15000","20"),
-> ("B003","Sabun","8000","100"),
-> ("B004","Chiki","5000","100"),
-> ("B005","Korek Api","2500","50");
Query OK, 5 rows affected (0.148 sec)
Records: 5 Duplicates: 0 Warnings: 0
6. Show/Tampilkan Tabel
Kemudian cek tabel barang : MySQL [kios_kelontong]> select * from BARANG; +----------+--------------+-----------+--------+ | KODE_BRG | NAMA_BRG | HARGA_BRG | JUMLAH | +----------+--------------+-----------+--------+ | B001 | Sandal Jepit | 16000 | 24 | | B002 | Sapu | 15000 | 20 | | B003 | Sabun | 8000 | 100 | | B004 | Chiki | 5000 | 100 | | B005 | Korek Api | 2500 | 50 | +----------+--------------+-----------+--------+ 5 rows in set (0.001 sec)
7. Gantilah field “JUMLAH” menjadi “Stok” pada tabel “BARANG”
Cek terlebih dahulu detail field yang akan diubah pada tabel barang :
MySQL [kios_kelontong]> desc barang; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | CODE_BRG | char(6) | NO | PRI | NULL | | | NAMA_BRG | varchar(30) | NO | | NULL | | | HARGA_BRG | int(8) | NO | | NULL | | | JUMLAH | int(6) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.003 sec)
Untuk mengubah field pada tabel gunakan perintah “CHANGE”
MySQL [kios_kelontong]> ALTER TABLE BARANG
-> CHANGE JUMLAH STOK INT(6) NOT NULL;
Query OK, 0 rows affected (0.139 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [kios_kelontong]> DESC BARANG;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| CODE_BRG | char(6) | NO | PRI | NULL | |
| NAMA_BRG | varchar(30) | NO | | NULL | |
| HARGA_BRG | int(8) | NO | | NULL | |
| STOK | int(6) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.003 sec)
8. Ubah nilai harga barang!
Sesuai dengan data yang ada pada table yang telah kita buat, misalnya harga Chiki yang tadinya 5000 kita ubah menjadai 10000.
+----------+--------------+-----------+--------+
| KODE_BRG | NAMA_BRG | HARGA_BRG | JUMLAH |
+----------+--------------+-----------+--------+
| B001 | Sandal Jepit | 16000 | 24 |
| B002 | Sapu | 15000 | 20 |
| B003 | Sabun | 8000 | 100 |
| B004 | Chiki | 5000 | 100 |
| B005 | Korek Api | 2500 | 50 |
+----------+--------------+-----------+--------+
MySQL [kios_kelontong]> update barang
-> set HARGA_BRG='10000'
-> where CODE_BRG='B004';
Query OK, 1 row affected (0.197 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [kios_kelontong]> select * from barang;
+----------+--------------+-----------+--------+
| CODE_BRG | NAMA_BRG | HARGA_BRG | JUMLAH |
+----------+--------------+-----------+--------+
| B001 | Sandal Jepit | 16000 | 24 |
| B002 | Sapu | 15000 | 20 |
| B003 | Sabun | 8000 | 100 |
| B004 | Chiki | 10000 | 100 |
| B005 | Korek Api | 2500 | 50 |
+----------+--------------+-----------+--------+
5 rows in set (0.001 sec)
9. Tambah barang!
Misalnya akan kita tambah data barang yaitu :
| Kode Barang | Nama Barang | Harga |
| B006 | Pencil | 1000 |
| B007 | Buku | 30000 |
| B008 | Bolpoin | 35000 |
Untuk melakukan penambahan barang dapat dilakukan dengan perintah berikut:
MySQL [kios_kelontong]> insert into barang
-> values
-> ("B006","Pensil","1200","12"),
-> ("B007","Penggaris","2000","12"),
-> ("B008","Penghapus","500","12");
Query OK, 3 rows affected (0.064 sec)
Records: 3 Duplicates: 0 Warnings: 0
ERROR: No query specified
MySQL [kios_kelontong]> select*from barang;
+----------+--------------+-----------+--------+
| CODE_BRG | NAMA_BRG | HARGA_BRG | JUMLAH |
+----------+--------------+-----------+--------+
| B001 | Sandal Jepit | 16000 | 24 |
| B002 | Sapu | 15000 | 20 |
| B003 | Sabun | 8000 | 100 |
| B004 | Chiki | 10000 | 100 |
| B005 | Korek Api | 2500 | 50 |
| B006 | Pensil | 1200 | 12 |
| B007 | Penggaris | 2000 | 12 |
| B008 | Penghapus | 500 | 12 |
+----------+--------------+-----------+--------+
8 rows in set (0.001 sec)
10. Hapus salah satu data pada tabel!
Misalnya kita akan menghapus “Penghapus pada tabel barang”
MySQL [kios_kelontong]> select*from barang; +----------+--------------+-----------+--------+ | CODE_BRG | NAMA_BRG | HARGA_BRG | JUMLAH | +----------+--------------+-----------+--------+ | B001 | Sandal Jepit | 16000 | 24 | | B002 | Sapu | 15000 | 20 | | B003 | Sabun | 8000 | 100 | | B004 | Chiki | 10000 | 100 | | B005 | Korek Api | 2500 | 50 | | B006 | Pensil | 1200 | 12 | | B007 | Penggaris | 2000 | 12 | | B008 | Penghapus | 500 | 12 | +----------+--------------+-----------+--------+ 8 rows in set (0.002 sec) MySQL [kios_kelontong]> delete from barang where CODE_BRG="B008"; Query OK, 1 row affected (0.164 sec) MySQL [kios_kelontong]> select*from barang; +----------+--------------+-----------+--------+ | CODE_BRG | NAMA_BRG | HARGA_BRG | JUMLAH | +----------+--------------+-----------+--------+ | B001 | Sandal Jepit | 16000 | 24 | | B002 | Sapu | 15000 | 20 | | B003 | Sabun | 8000 | 100 | | B004 | Chiki | 10000 | 100 | | B005 | Korek Api | 2500 | 50 | | B006 | Pensil | 1200 | 12 | | B007 | Penggaris | 2000 | 12 | +----------+--------------+-----------+--------+ 7 rows in set (0.000 sec)
11. Tampilkan kolom nama barang dan harga barang saja!
MySQL [kios_kelontong]> select NAMA_BRG,HARGA_BRG from barang; +--------------+-----------+ | NAMA_BRG | HARGA_BRG | +--------------+-----------+ | Sandal Jepit | 16000 | | Sapu | 15000 | | Sabun | 8000 | | Chiki | 10000 | | Korek Api | 2500 | | Pensil | 1200 | | Penggaris | 2000 | +--------------+-----------+ 7 rows in set (0.001 sec)
Hil's Diary 
1 thought on “Latihan MySQL (Create,insert,update table)”