Contoh Perintah Database Penjualan

C:\Users\priza>cd/xampp/mysql/bin

C:\xampp\mysql\bin>myql -u root
'myql' is not recognized as an internal or external command,
operable program or batch file.

C:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use database_penjualan;
Database changed
mysql> show database_penjualan;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'datab
ase_penjualan' at line 1
mysql> show tables;
+------------------------------+
| Tables_in_database_penjualan |
+------------------------------+
| barang                       |
| customer                     |
| detail_transaksi             |
| jenis                        |
| lokasi                       |
| petugas                      |
| satuan                       |
| supplyer                     |
| transaksi                    |
+------------------------------+
9 rows in set (0.18 sec)

mysql> desc barang;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_barang   | char(12)    | YES  |     | NULL    |       |
| nama_barang   | varchar(20) | YES  |     | NULL    |       |
| kode_supplyer | char(6)     | YES  |     | NULL    |       |
| kode_jenis    | int(11)     | YES  |     | NULL    |       |
| stock         | int(4)      | YES  |     | NULL    |       |
| kode_satuan   | char(5)     | YES  |     | NULL    |       |
| kode_lokasi   | char(5)     | YES  |     | NULL    |       |
| harga         | int(8)      | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
8 rows in set (0.06 sec)

mysql> alter table barang
    -> add constraint
    -> pkb1 primary key(kode_barang);
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc barang;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_barang   | char(12)    | NO   | PRI |         |       |
| nama_barang   | varchar(20) | YES  |     | NULL    |       |
| kode_supplyer | char(6)     | YES  |     | NULL    |       |
| kode_jenis    | int(11)     | YES  |     | NULL    |       |
| stock         | int(4)      | YES  |     | NULL    |       |
| kode_satuan   | char(5)     | YES  |     | NULL    |       |
| kode_lokasi   | char(5)     | YES  |     | NULL    |       |
| harga         | int(8)      | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

mysql> desc customer;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_customer | varchar(6)  | YES  |     | NULL    |       |
| nama_customer | varchar(20) | YES  |     | NULL    |       |
| alamat        | varchar(50) | YES  |     | NULL    |       |
| no_telp       | varchar(15) | YES  |     | NULL    |       |
| email         | varchar(20) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.05 sec)

mysql> alter table customer
    -> add constraint
    -> pkc1 primary key(kode_customer);
Query OK, 0 rows affected (0.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc costumer;
ERROR 1146 (42S02): Table 'database_penjualan.costumer' doesn't exist
mysql> desc costumer;
ERROR 1146 (42S02): Table 'database_penjualan.costumer' doesn't exist
mysql> use database_penjualan;
Database changed
mysql> desc customer;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_customer | varchar(6)  | NO   | PRI |         |       |
| nama_customer | varchar(20) | YES  |     | NULL    |       |
| alamat        | varchar(50) | YES  |     | NULL    |       |
| no_telp       | varchar(15) | YES  |     | NULL    |       |
| email         | varchar(20) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> desc detail_transaksi;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| kode_detail    | int(11)     | NO   | PRI | NULL    | auto_increment |
| kode_transaksi | varchar(15) | YES  |     | NULL    |                |
| kode_barang    | char(12)    | YES  |     | NULL    |                |
| harga          | int(10)     | YES  |     | NULL    |                |
| qty            | int(4)      | YES  |     | NULL    |                |
| sub_total      | int(10)     | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
6 rows in set (0.33 sec)

mysql> desc jenis;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| kode_jenis | int(11)     | NO   | PRI | NULL    | auto_increment |
| nama_jenis | varchar(10) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
2 rows in set (0.33 sec)

mysql> desc satuan;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| kode_satuan | char(5)     | YES  |     | NULL    |       |
| nama_satuan | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

mysql> alter table satuan
    -> add constraint
    -> pks1 primary key(kode_satuan);
Query OK, 0 rows affected (0.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc satuan;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| kode_satuan | char(5)     | NO   | PRI |         |       |
| nama_satuan | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc lokasi;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| kode_lokasi | char(5)     | YES  |     | NULL    |       |
| nama_lokasi | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table lokasi
    -> add constraint
    -> pkl1 primary key(kode_lokasi);
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc lokasi;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| kode_lokasi | char(5)     | NO   | PRI |         |       |
| nama_lokasi | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table petugas
    -> add constraint
    -> pkp1 primary key(kode_petugas);
ERROR 1068 (42000): Multiple primary key defined
mysql> desc petugas;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| kode_petugas | int(11)     | NO   | PRI | NULL    | auto_increment |
| nama_petugas | varchar(20) | YES  |     | NULL    |                |
| alamat       | varchar(50) | YES  |     | NULL    |                |
| no_telp      | varchar(15) | YES  |     | NULL    |                |
| password     | varchar(9)  | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> desc suplyer;
ERROR 1146 (42S02): Table 'database_penjualan.suplyer' doesn't exist
mysql> desc supplyer;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_supplyer | char(10)    | YES  |     | NULL    |       |
| nama_supplyer | varchar(20) | YES  |     | NULL    |       |
| alamat        | varchar(50) | YES  |     | NULL    |       |
| email         | varchar(20) | YES  |     | NULL    |       |
| no_telp       | varchar(15) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.05 sec)

mysql> alter table supplyer;
Query OK, 0 rows affected (0.00 sec)

mysql> desc supplyer;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_supplyer | char(10)    | YES  |     | NULL    |       |
| nama_supplyer | varchar(20) | YES  |     | NULL    |       |
| alamat        | varchar(50) | YES  |     | NULL    |       |
| email         | varchar(20) | YES  |     | NULL    |       |
| no_telp       | varchar(15) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> alter table supplyer
    -> add constraint
    -> pks1 primary key(kode_supplyer);
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc supplyer;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_supplyer | char(10)    | NO   | PRI |         |       |
| nama_supplyer | varchar(20) | YES  |     | NULL    |       |
| alamat        | varchar(50) | YES  |     | NULL    |       |
| email         | varchar(20) | YES  |     | NULL    |       |
| no_telp       | varchar(15) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> desc transaksi;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| kode_transaksi | varchar(15) | YES  |     | NULL    |       |
| tanggal              | date            | YES  |     | NULL    |       |
| jam                   | time            | YES  |     | NULL    |       |
| kode_customer | char(5)        | YES  |     | NULL    |       |
| total                  | int(10)        | YES  |     | NULL    |       |
| kode_petugas   | char(10)      | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.05 sec)

mysql> alter table transaksi
    -> add constraint
    -> pkt1 primary key(kode_transaksi);
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc transaksi;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| kode_transaksi | varchar(15) | NO   | PRI |         |       |
| tanggal               | date             | YES  |          | NULL    |       |
| jam                    | time             | YES  |          | NULL    |       |
| kode_customer  | char(5)        | YES  |           | NULL    |       |
| total                   | int(10)         | YES  |           | NULL    |       |
| kode_petugas    | char(10)      | YES  |           | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> alter table transaksi
    -> add constraint
    -> fkt2 foreign key(kode_customer)
    -> references customer(kode_customer);
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc transaksi;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| kode_transaksi | varchar(15) | NO   | PRI |         |       |
| tanggal        | date        | YES  |     | NULL    |       |
| jam            | time        | YES  |     | NULL    |       |
| kode_customer  | char(5)     | YES  | MUL | NULL    |       |
| total          | int(10)     | YES  |     | NULL    |       |
| kode_petugas   | char(10)    | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> alter table barang
    -> add constraint
    -> fkb3 foreign key(kode_supplyer)
    -> references supplyer(kode_supplyer);
Query OK, 0 rows affected (0.77 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table barang
    -> add constraint
    -> fkb6 foreign key(kode_jenis)
    -> references jenis(kode_jenis);
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc barang;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_barang   | char(12)    | NO   | PRI |         |       |
| nama_barang   | varchar(20) | YES  |     | NULL    |       |
| kode_supplyer | char(6)     | YES  | MUL | NULL    |       |
| kode_jenis    | int(11)     | YES  | MUL | NULL    |       |
| stock         | int(4)      | YES  |     | NULL    |       |
| kode_satuan   | char(5)     | YES  |     | NULL    |       |
| kode_lokasi   | char(5)     | YES  |     | NULL    |       |
| harga         | int(8)      | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> alter table barang
    -> add constraint
    -> fkb7 foreign key(kode_satuan)
    -> references satuan(kode_satuan);
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table barang
    -> add constraint
    -> fkb8 foreign key(kode_lokasi)
    -> references lokasi(kode_lokasi);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc barang;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_barang   | char(12)    | NO   | PRI |         |       |
| nama_barang   | varchar(20) | YES  |     | NULL    |       |
| kode_supplyer | char(6)     | YES  | MUL | NULL    |       |
| kode_jenis    | int(11)     | YES  | MUL | NULL    |       |
| stock         | int(4)      | YES  |     | NULL    |       |
| kode_satuan   | char(5)     | YES  | MUL | NULL    |       |
| kode_lokasi   | char(5)     | YES  | MUL | NULL    |       |
| harga         | int(8)      | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

mysql> desc detail_transaksi;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| kode_detail    | int(11)     | NO   | PRI | NULL    | auto_increment |
| kode_transaksi | varchar(15) | YES  |     | NULL    |                |
| kode_barang    | char(12)    | YES  |     | NULL    |                |
| harga          | int(10)     | YES  |     | NULL    |                |
| qty            | int(4)      | YES  |     | NULL    |                |
| sub_total      | int(10)     | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
6 rows in set (0.03 sec)

mysql> alter table detail_transaksi
    -> add constraint
    -> fktd1 foreign key(kode_transaksi)
    -> references transaksi(kode_transaksi);
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table detail_transaksi
    -> add constraint
    -> fktd2 foreign key(kode_barang)
    -> references barang(kode_barang);
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0



mysql> desc detail_transaksi;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| kode_detail    | int(11)     | NO   | PRI | NULL    | auto_increment |
| kode_transaksi | varchar(15) | YES  | MUL | NULL    |                |
| kode_barang    | char(12)    | YES  | MUL | NULL    |                |
| harga          | int(10)     | YES  |     | NULL    |                |
| qty            | int(4)      | YES  |     | NULL    |                |
| sub_total      | int(10)     | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)