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)

Read More ->>

Query SQL

Assalamualikum para Agan yang stay di sini.,.,kali ini ane mau ngebahas sedikit tentang SQL,sedikit bagi-bagi biar berkah..,ok langsung sajaa.,.cekidroottt.,. 

SQL ( Structured Query Language ) adalah bahasa stndart yang digunakan untuk mengakses server database . Semenjak tahun 70-an bahasa ini telah dikembangkan oleh IBM, yang kemudian diikuti dengan adanya Oracle, Informix dan Sybase. Dengan menggunakan SQL, proses akses database menjadi lebih user-friendly dibandingkan dengan misalnya dBase ataupun Clipper yang masih menggunakan perintah – perintah pemrograman murni.

ok .,.,itu tadi penjelasanya yuukk kita pelajari Querynya.,.,.,

Perintah dari SQL yang digunakan untuk meminta sebuah tindakan kepada DBMS.

 ž  ALTER         : Merubah struktur tabel

ž  COMMIT       : Mengakhiri eksekusi transaksi

ž  CREATE        : Membuat tabel, indeks

ž  DELETE        : Menghapus baris pada sebuah tabel

ž  DROP            : Menghapus tabel, indeks

ž  GRANT         : Menugaskan hak terhadap basis data kepada user

ž  INSERT         : Menambah baris pada tabel

ž  REVOKE       : Membatalkan hak kepada basis data

ž ROLLBACK  : Mengembalikan pada keadaan semula apabila transaksi gagal dilaksanakan

 SELECT        : Memilih baris dan kolom pada sebuah tabel

ž  UPDATE       : Mengubah value pada baris sebuah tabel

1.Pernyataan SQL kelompok DDL (Data Defination Language) DDL berfungsi untuk mendefinisikan atribut basis data, table, atribut(kolom), batasan-batasan terhadap suatu atribut, serta hubungan antar tabel. Perintah yang digunakan biasanya : CREATE, ALTER, dan DROP

ž  Membuat Database

ž  Syntax yang digunakan : CREATE DATABASE namadatabase;

ž  Menghapus Database

ž  Syntax yang digunakan : DROP DATABASE namadatabase;

ž  Membuat Tabel

ž  Syntax yang digunakan : CREATE TABLE namatabel;

ž  Menghapus Tabel

ž  Syntax yang digunakan : DROP TABEL namatabel;

ž   

Membuat Primary key

                ALTER TABLE namatabel

                ADD CONSTRAINT

                Namafiel PPRIMARY KEY (namafield)

Membuat Foreign key

                ALTER TABLE namatabel

                ADD CONSTRAINT

                namaFK FOREIGN KEY (namafield)

                REFERENCES namatable (namafield)

2. DML (Data Manipulation Language

ž  INSERT

 Menambah baris pada tabel. Syntax yang paling sering digunakan : INSERT
 INTO namatabel VALUES (nilai1, nilai2, nilai-n);

ž  DELETE

 Menghapus baris pada tabel. Syntax : DELETE FROM namatabel [where 

 kondisi];

ž  UPDATE
 Mengubah isi beberapa kolom pada tabel. Syntax : UPDATE namatabel SET

 kolom1=nilai1, kolom2=nilai2 [where kondisi];

ž  SELECT

 Menampilkan isi dari suatu tabel yang bisa dihubungkan dengan tabel yang       

 lainnya;

  Mungkin sekian dari Ane.,.,semoga bermanfaat

 

Read More ->>