PERINTAH QUERY UPDATE DAN DELETE (DATABASE

PERINTAH QUERY UPDATE & DELETE DI CMD

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\EDI SANTOSO>cd/xampp/mysql/bin

C:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
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

PERINTAH QUERY UPDATE PRIMARY KEY TABEL SATUAN

mysql> select * from satuan;
+-------------+-------------+
| kode_satuan | nama_satuan |
+-------------+-------------+
| s01 | kilogram |
| S02 | pack |
| S03 | gram |
| S04 | sachet |
| S05 | pcs |
+-------------+-------------+
5 rows in set (0.00 sec)

mysql> update satuan set kode_satuan ='s01'
-> where kode_satuan ='S01';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update satuan set kode_satuan ='s02'
-> where kode_satuan ='S02';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update satuan set kode_satuan ='s03'
-> where kode_satuan ='S03';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update satuan set kode_satuan ='s04'
-> where kode_satuan ='S04';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update satuan set kode_satuan ='s05'
-> where kode_satuan ='S05';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from satuan;
+-------------+-------------+
| kode_satuan | nama_satuan |
+-------------+-------------+
| s01 | kilogram |
| s02 | pack |
| s03 | gram |
| s04 | sachet |
| s05 | pcs |
+-------------+-------------+
5 rows in set (0.00 sec)

PERINTAH QUERY UPDATE NAMA ISI DATA

mysql> update satuan set nama_satuan ='gram'
-> where kode_satuan ='s02';
Query OK, 1 row affected (0.36 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update satuan set nama_satuan ='dus'
-> where kode_satuan ='s03';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update satuan set nama_satuan ='liter'
-> where kode_satuan ='s04';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update satuan set nama_satuan ='sachet'
-> where kode_satuan ='s05';
Query OK, 1 row affected (0.37 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from satuan;
+-------------+-------------+
| kode_satuan | nama_satuan |
+-------------+-------------+
| s01 | kilogram |
| s02 | gram |
| s03 | dus |
| s04 | liter |
| s05 | sachet |
+-------------+-------------+
5 rows in set (0.00 sec)

PERINTAH QUERY UPDATE PRIMARY KEY LOKASI

mysql> select*from lokasi;
+-------------+-------------+
| kode_lokasi | nama_lokasi |
+-------------+-------------+
| L1 | Rak A1 |
| L2 | Rak A2 |
| L3 | Rak A3 |
| L4 | Rak A4 |
| L5 | Rak A5 |
+-------------+-------------+
5 rows in set (0.36 sec)

mysql> update lokasi set kode_lokasi ='l01'
-> where kode_lokasi ='L1';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update lokasi set kode_lokasi ='l02'
-> where kode_lokasi ='L2';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update lokasi set kode_lokasi ='l03'
-> where kode_lokasi ='L3';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update lokasi set kode_lokasi ='l04'
-> where kode_lokasi ='L4';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update lokasi set kode_lokasi ='l05'
-> where kode_lokasi ='L5';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from lokasi;
+-------------+-------------+
| kode_lokasi | nama_lokasi |
+-------------+-------------+
| l01 | Rak A1 |
| l02 | Rak A2 |
| l03 | Rak A3 |
| l04 | Rak A4 |
| l05 | Rak A5 |
+-------------+-------------+
5 rows in set (0.00 sec)

PERINTAH QUERY UPDATE NAMA ISI DATA

mysql> update lokasi set nama_lokasi ='rak A1'
-> where kode_lokasi ='L01';
Query OK, 1 row affected (0.28 sec)
Rows matched: 1 Changed: 1 Warnings: 0
i
mysql> update lokasi set nama_lokasi ='rak A2'
-> where kode_lokasi ='L02';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update lokasi set nama_lokasi ='rak A3'
-> where kode_lokasi ='l03';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update lokasi set nama_lokasi ='rak A4'
-> where kode_lokasi ='l04';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update lokasi set nama_lokasi ='rak A5'
-> where kode_lokasi ='l05';
Query OK, 1 row affected (0.36 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from lokasi;
+-------------+-------------+
| kode_lokasi | nama_lokasi |
+-------------+-------------+
| l01 | rak A1 |
| l02 | rak A2 |
| l03 | rak A3 |
| l04 | rak A4 |
| l05 | rak A5 |
+-------------+-------------+
5 rows in set (0.00 sec)

PERINTAH QUERY UPDATE TYPE DATA PADA KOLOM FIELD

mysql> desc supplier;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_supplier | char(6) | NO | PRI | | |
| nama_supplier | 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.00 sec)

mysql> alter table supplier
-> change email email varchar (40);
Query OK, 2 rows affected (0.23 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> desc supplier;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| kode_supplier | char(6) | NO | PRI | | |
| nama_supplier | varchar(20) | YES | | NULL | |
| alamat | varchar(50) | YES | | NULL | |
| email | varchar(40) | YES | | NULL | |
| no_telp | varchar(15) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

PERINTAH QUERY UPDATE ISI DATA DALAN KOLOM EMAIL

mysql> select * from supplier;
+---------------+----------------+-----------+-------------------------+--------------+
| kode_supplier | nama_supplier | alamat | email | no_telp |
+---------------+----------------+-----------+-------------------------+--------------+
| sp01 | m.edi santoso | indramayu | muhamad.edi25@gmail. | 087726xxxxxx |
| sp02 | erni nurzhaety | cirebon | erni.zha@gmail.com | 087777xxxxxx |
+---------------+----------------+-----------+-------------------------+--------------+
2 rows in set (0.00 sec)

mysql> update supplier set email ='muhamad.edi25@gmail.com'
-> where kode_supplier ='sp01';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from supplier;
+---------------+----------------+-----------+-------------------------+--------------+
| kode_supplier | nama_supplier | alamat | email | no_telp |
+---------------+----------------+-----------+-------------------------+--------------+
| sp01 | m.edi santoso | indramayu | muhamad.edi25@gmail.com | 087726xxxxxx |
| sp02 | erni nurzhaety | cirebon | erni.zha@gmail.com | 087777xxxxxx |
+---------------+----------------+-----------+-------------------------+--------------+
2 rows in set (0.00 sec)

PERINTAH QUERY INSERT YANG BELUM SELESAI TAPI SUDAH KE ENTER,,HASIL NYA LIHAT DI BAWAH

mysql> insert into supplier value
-> ('sp03','
'> mia miun','cikarang','mia.miun23@gmail.com','085678xxxxxx');
Query OK, 1 row affected (0.06 sec)

mysql> insert into supplier value
-> ('sp04','tofik survey','cikarang','tofik.surv@gmail.com','082123xxxxxx');
Query OK, 1 row affected (0.06 sec)

mysql> insert into supplier value
-> ('sp05','linah karlina','indramayu','lina.kar@yahoo.com','087818xxxxxx');
Query OK, 1 row affected (0.06 sec)

mysql> select * from supplier;
+---------------+----------------+-----------+-------------------------+--------------+
| kode_supplier | nama_supplier | alamat | email | no_telp |
+---------------+----------------+-----------+-------------------------+--------------+
| sp03 |
mia miun | cikarang | mia.miun23@gmail.com | 085678xxxxxx |
| sp04 | tofik survey | cikarang | tofik.surv@gmail.com | 082123xxxxxx |
| sp05 | linah karlina | indramayu | lina.kar@yahoo.com | 087818xxxxxx |
+---------------+----------------+-----------+-------------------------+--------------+
5 rows in set (0.00 sec)

PERINTAH QUERY HAPUS SALAH SATU ISI DATA PADA TABEL SUPPLIER

mysql> select * from supplier;
+---------------+----------------+-----------+-------------------------+--------------+
| kode_supplier | nama_supplier | alamat | email | no_telp |
+---------------+----------------+-----------+-------------------------+--------------+
| sp03 |
mia miun | cikarang | mia.miun23@gmail.com | 085678xxxxxx |
| sp04 | tofik survey | cikarang | tofik.surv@gmail.com | 082123xxxxxx |
| sp05 | linah karlina | indramayu | lina.kar@yahoo.com | 087818xxxxxx |
+---------------+----------------+-----------+-------------------------+--------------+
5 rows in set (0.00 sec)

mysql> delete from supplier
-> where kode_supplier ='sp03';
Query OK, 1 row affected (0.06 sec)

mysql> select * from supplier;
+---------------+----------------+-----------+-------------------------+--------------+
| kode_supplier | nama_supplier | alamat | email | no_telp |
+---------------+----------------+-----------+-------------------------+--------------+
| sp04 | tofik survey | cikarang | tofik.surv@gmail.com | 082123xxxxxx |
| sp05 | linah karlina | indramayu | lina.kar@yahoo.com | 087818xxxxxx |
+---------------+----------------+-----------+-------------------------+--------------+
4 rows in set (0.00 sec)

PERINTAH QUERY UPDATE ISI DATA DALAM FIELD-FIELD TABEL SUPPLIER

mysql> select * from supplier;
+---------------+----------------+-----------+-------------------------+--------------+
| kode_supplier | nama_supplier | alamat | email | no_telp |
+---------------+----------------+-----------+-------------------------+--------------+
| sp01 | m.edi santoso | indramayu | muhamad.edi25@gmail.com | 087726xxxxxx |
| sp02 | erni nurzhaety | cirebon | erni.zha@gmail.com | 087777xxxxxx |
| sp03 | mia miun | cikarang | mia.miun23@gmail.com | 085678xxxxxx |
| sp04 | tofik survey | cikarang | tofik.surv@gmail.com | 082123xxxxxx |
| sp05 | linah karlina | indramayu | lina.kar@yahoo.com | 087818xxxxxx |
+---------------+----------------+-----------+-------------------------+--------------+
5 rows in set (0.00 sec)

mysql> update supplier set nama_supplier ='pt.deterjen nusantara',alamat ='jakarta'
-> ,email ='deterjen.01@yahoo.com',no_telp ='021777xxxxxx'
-> where kode_supplier ='sp01';
Query OK, 1 row affected, 1 warning (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> update supplier set nama_supplier ='pt.lifeboy global',alamat ='jakarta'
-> ,email ='lifeboy.02@yahoo.com',no_telp ='021232xxxxxx'
-> where kode_supplier ='sp02';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update supplier set nama_supplier ='pt.djarum indonesia',alamat ='banten'
-> ,email ='djarum.03@gmail.com',no_telp ='021999xxxxxx'
-> where kode_supplier ='sp03';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update supplier set nama_supplier ='pt.bimoli nusantara',alamat ='jakarta'
-> ,email ='bimoli.04@gmail.com',no_telp ='021222xxxxxx'
-> where kode_supplier ='sp04';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update supplier set nama_supplier ='pt.clear indonesia',alamat ='indramayu'
-> ,email ='clear.05@gmail.com',no_telp ='021234xxxxxx'
-> where kode_supplier ='sp05';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from supplier;
+---------------+----------------------+-----------+-----------------------+--------------+
| kode_supplier | nama_supplier | alamat | email | no_telp |
+---------------+----------------------+-----------+-----------------------+--------------+
| sp01 | pt.deterjen nusantar | jakarta | deterjen.01@yahoo.com | 021777xxxxxx |
| sp02 | pt.lifeboy global | jakarta | lifeboy.02@yahoo.com | 021232xxxxxx |
| sp03 | pt.djarum indonesia | banten | djarum.03@gmail.com | 021999xxxxxx |
| sp04 | pt.bimoli nusantara | jakarta | bimoli.04@gmail.com | 021222xxxxxx |
| sp05 | pt.clear indonesia | indramayu | clear.05@gmail.com | 021234xxxxxx |
+---------------+----------------------+-----------+-----------------------+--------------+
5 rows in set (0.00 sec)

PERINTAH QUERY KURANG LENGKAP SAAT INSERT FIELD JAM,TOTAL,,,HASIL NYA LIHAT DI BAWAH

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

mysql> insert into transaksi value
-> ('tr01','2014-10-25','08.00','cs01','rp.500.000','p001');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from transaksi;
+----------------+------------+----------+---------------+-------+--------------+
| kode_transaksi | tanggal | jam | kode_customer | total | kode_petugas |
+----------------+------------+----------+---------------+-------+--------------+
| tr01 | 2014-10-25 | 00:00:08 | cs01 | 0 | p001 |
+----------------+------------+----------+---------------+-------+--------------+
1 row in set (0.00 sec)

PERINTAH QUERY KURANG LENGKAP SAAT INSERT FIELD TANGGAL,JAM,,HASIL NYA LIHAT DI BAWAH URUTAN KE DUA

mysql> insert into transaksi values
-> ('tr02','20-09-2014','00.08','cs02','500000','p002');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from transaksi;
+----------------+------------+----------+---------------+--------+--------------+
| kode_transaksi | tanggal | jam | kode_customer | total | kode_petugas |
+----------------+------------+----------+---------------+--------+--------------+
| tr01 | 2014-10-25 | 00:00:08 | cs01 | 0 | p001 |
| tr02 | 0000-00-00 | 00:00:00 | cs02 | 500000 | p002 |
+----------------+------------+----------+---------------+--------+--------------+
2 rows in set (0.00 sec)

PERINTAH QUERY INSERT YANG BENER UNTUK FIELD TANGGAL,JAM,,,HASIL NYA LIHAT DI BAWAH URUTAN KE TIGA

mysql> insert into transaksi values
-> ('tr03','2014-09-23','09:30','cs03','900000','p003');
Query OK, 1 row affected (0.06 sec)

mysql> select * from transaksi;
+----------------+------------+----------+---------------+--------+--------------+
| kode_transaksi | tanggal | jam | kode_customer | total | kode_petugas |
+----------------+------------+----------+---------------+--------+--------------+
| tr01 | 2014-10-25 | 00:00:08 | cs01 | 0 | p001 |
| tr02 | 0000-00-00 | 00:00:00 | cs02 | 500000 | p002 |
| tr03 | 2014-09-23 | 09:30:00 | cs03 | 900000 | p003 |
+----------------+------------+----------+---------------+--------+--------------+
3 rows in set (0.00 sec)

PERINTAH QUERY UPDATE ISI DATA PADA FIELD-FIELD TANGGAL,JAM DALAM TABEL TRANSAKSI

mysql> select * from transaksi;
+----------------+------------+----------+---------------+--------+--------------+
| kode_transaksi | tanggal | jam | kode_customer | total | kode_petugas |
+----------------+------------+----------+---------------+--------+--------------+
| tr01 | 0000-00-00 | 00:00:08 | cs01 | 100000 | p001 |
| tr02 | 0000-00-00 | 00:00:20 | cs02 | 200000 | p002 |
| tr03 | 2014-09-23 | 09:30:00 | cs03 | 900000 | p003 |
| tr04 | 2014-09-25 | 12:30:00 | cs04 | 800000 | p004 |
| tr05 | 2014-08-17 | 18:30:00 | cs05 | 100000 | p005 |
+----------------+------------+----------+---------------+--------+--------------+
5 rows in set (0.00 sec)

mysql> update transaksi set tanggal='2014-07-20',jam='08:45'
-> ,kode_customer='cs01',total='100000',kode_petugas='p001'
-> where kode_transaksi='tr01';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update transaksi set tanggal='2014-08-15',jam='20:10'
-> ,kode_customer='cs02',total='200000',kode_petugas='p002'
-> where kode_transaksi='tr02';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from transaksi;
+----------------+------------+----------+---------------+--------+--------------+
| kode_transaksi | tanggal | jam | kode_customer | total | kode_petugas |
+----------------+------------+----------+---------------+--------+--------------+
| tr01 | 2014-07-20 | 08:45:00 | cs01 | 100000 | p001 |
| tr02 | 2014-08-15 | 20:10:00 | cs02 | 200000 | p002 |
| tr03 | 2014-09-23 | 09:30:00 | cs03 | 900000 | p003 |
| tr04 | 2014-09-25 | 12:30:00 | cs04 | 800000 | p004 |
| tr05 | 2014-08-17 | 18:30:00 | cs05 | 100000 | p005 |
+----------------+------------+----------+---------------+--------+--------------+
5 rows in set (0.00 sec)

PERINTAH QUERY UPDATE FIELD SUB_TOTAL BARIS KE TIGA

mysql> select * from detail_transaksi;
+-------------+----------------+-------------+-------+------+-----------+
| kode_detail | kode_transaksi | kode_barang | harga | qty | sub_total |
+-------------+----------------+-------------+-------+------+-----------+
| 1 | tr01 | br01 | 2000 | 200 | 400000 |
| 2 | tr02 | br02 | 3000 | 300 | 900000 |
| 3 | tr03 | br03 | 15000 | 700 | 10500 |
+-------------+----------------+-------------+-------+------+-----------+
3 rows in set (0.00 sec)

mysql> update detail_transaksi set
-> sub_total='10500000' where kode_detail='3';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from detail_transaksi;
+-------------+----------------+-------------+-------+------+-----------+
| kode_detail | kode_transaksi | kode_barang | harga | qty | sub_total |
+-------------+----------------+-------------+-------+------+-----------+
| 1 | tr01 | br01 | 2000 | 200 | 400000 |
| 2 | tr02 | br02 | 3000 | 300 | 900000 |
| 3 | tr03 | br03 | 15000 | 700 | 10500000 |
+-------------+----------------+-------------+-------+------+-----------+
3 rows in set (0.00 sec)

PERINTAH QUERY UPDATE TYPE DATA KOLOM FIELD NAMA_JENIS MENJADI VARCHAR (25)

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.00 sec)

mysql> alter table jenis
-> change nama_jenis nama_jenis varchar(25);
Query OK, 2 rows affected (0.25 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc jenis;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| kode_jenis | int(11) | NO | PRI | NULL | auto_increment |
| nama_jenis | varchar(25) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

PERINTAH QUERY UPDATE ISI DATA PADA KOLOM NAMA_JENIS DI DALAM TABEL JENIS,,HASIL NYA LIHAT DI BARIS KEDUA

mysql> update jenis set nama_jenis ='sabun mandi'
-> where kode_jenis ='2';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from jenis;
+------------+-------------+
| kode_jenis | nama_jenis |
+------------+-------------+
| 1 | deterjen |
| 2 | sabun mandi |
+------------+-------------+
2 rows in set (0.00 sec)

Cari Blog Ini

DATABASE (PHP NEW BEE CONTINUE)

PERINTAH QUERY UPDATE DAN DELETE (DATABASE_PENJUALAN)

Komentar

Posting Komentar

Postingan populer dari blog ini

QUERY DATA BASE

PERINTAH FUNGSI MAX, MIN, COUNT, SUM, & AVG

PERINTAH QUERY SELECT LIKE (PENCARIAN)