Rabu, 12 Juni 2013

Belajar Database

Database:

cd C:\apache\mysql\bin

C:\apache\mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.47-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+-------------------+
| Database          |
+-------------------+
| Saya              |
| akademik          |
| db_PTmajuterus    |
| db_akademik       |
| db_kampus         |
| db_pegawai        |
| db_perpustakaan   |
| dbjualan          |
| dbkampus          |
| dbpenjualan       |
| dbsistempenjualan |
| mahasiswa         |
| mysql             |
| penjualan         |
| perpustakaan      |
| test              |
+-------------------+
16 rows in set (0.05 sec)

mysql> create database dpenjualan;
Query OK, 1 row affected (0.00 sec)

mysql> use dpenjualan;
Database changed
mysql> create table pelanggan(kode_pelanggan varchar(5), nama_pelanggan varchar(
30), alamat varchar(100),telp varchar (12));
Query OK, 0 rows affected (0.08 sec)

mysql> desc pelanggan;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| kode_pelanggan | varchar(5)   | YES  |     | NULL    |       |
| nama_pelanggan | varchar(30)  | YES  |     | NULL    |       |
| alamat         | varchar(100) | YES  |     | NULL    |       |
| telp           | varchar(12)  | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
4 rows in set (0.06 sec)

mysql> insert into pelanggan values('P00087','rodesri','Di Bumi','Gak Ada');
Query OK, 1 row affected (0.05 sec)

mysql> insert into pelanggan(nama_pelanggan,telp,alamat,kode_pelanggan) values('
Rona','Rahasia Dong','Disamping Rumah Rodesri','P0002');
Query OK, 1 row affected (0.00 sec)

mysql> select * from pelanggan;
+----------------+----------------+-------------------------+--------------+
| kode_pelanggan | nama_pelanggan | alamat                  | telp         |
+----------------+----------------+-------------------------+--------------+
| P0008          | rodesri        | Di Bumi                 | Gak Ada      |
| P0002          | Rona           | Disamping Rumah Rodesri | Rahasia Dong |
+----------------+----------------+-------------------------+--------------+
2 rows in set (0.05 sec)

mysql> insert into pelanggan values('P0003','Agus','Di Mars','008'),
    -> ('P0004','Andreas','Di Pluto','001'),
    -> ('P0005','Hikma','Matahari','Gak Ada');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from pelanggan;
+----------------+----------------+-------------------------+--------------+
| kode_pelanggan | nama_pelanggan | alamat                  | telp         |
+----------------+----------------+-------------------------+--------------+
| P0008          | rodesri        | Di Bumi                 | Gak Ada      |
| P0002          | Rona           | Disamping Rumah Rodesri | Rahasia Dong |
| P0003          | Agus           | Di Mars                 | 008          |
| P0004          | Andreas        | Di Pluto                | 001          |
| P0005          | Hikma          | Matahari                | Gak Ada      |
+----------------+----------------+-------------------------+--------------+
5 rows in set (0.00 sec)

mysql> select * from pelanggan where telp='Gak Ada';
+----------------+----------------+----------+---------+
| kode_pelanggan | nama_pelanggan | alamat   | telp         |
+----------------+----------------+----------+---------+
| P0008               | rodesri              | Di Bumi   | Gak Ada |
| P0005               | Hikma              | Matahari | Gak Ada |
+----------------+----------------+----------+---------+
2 rows in set (0.06 sec)

mysql> select * from pelanggan where kode_pelanggan='P0004';
+----------------+----------------+----------+------+
| kode_pelanggan | nama_pelanggan | alamat   | telp |
+----------------+----------------+----------+------+
| P0004          | Andreas        | Di Pluto | 001  |
+----------------+----------------+----------+------+
1 row in set (0.00 sec)

mysql> select * from pelanggan order by nama_pelanggan;
+----------------+----------------+-------------------------+--------------+
| kode_pelanggan | nama_pelanggan | alamat                  | telp         |
+----------------+----------------+-------------------------+--------------+
| P0003          | Agus           | Di Mars                 | 008          |
| P0004          | Andreas        | Di Pluto                | 001          |
| P0005          | Hikma          | Matahari                | Gak Ada      |
| P0008          | rodesri        | Di Bumi                 | Gak Ada      |
| P0002          | Rona           | Disamping Rumah Rodesri | Rahasia Dong |
+----------------+----------------+-------------------------+--------------+
5 rows in set (0.05 sec)

mysql> select * from pelanggan order by nama_pelanggan desc;
+----------------+----------------+-------------------------+--------------+
| kode_pelanggan | nama_pelanggan | alamat                  | telp         |
+----------------+----------------+-------------------------+--------------+
| P0002          | Rona           | Disamping Rumah Rodesri | Rahasia Dong |
| P0008          | rodesri        | Di Bumi                 | Gak Ada      |
| P0005          | Hikma          | Matahari                | Gak Ada      |
| P0004          | Andreas        | Di Pluto                | 001          |
| P0003          | Agus           | Di Mars                 | 008          |
+----------------+----------------+-------------------------+--------------+
5 rows in set (0.00 sec)

mysql> select * from pelanggan order by nama_pelanggan;
+----------------+----------------+-------------------------+--------------+
| kode_pelanggan | nama_pelanggan | alamat                  | telp         |
+----------------+----------------+-------------------------+--------------+
| P0003          | Agus           | Di Mars                 | 008          |
| P0004          | Andreas        | Di Pluto                | 001          |
| P0005          | Hikma          | Matahari                | Gak Ada      |
| P0008          | rodesri        | Di Bumi                 | Gak Ada      |
| P0002          | Rona           | Disamping Rumah Rodesri | Rahasia Dong |
+----------------+----------------+-------------------------+--------------+
5 rows in set (0.00 sec)

mysql> update pelanggan set nama_pelanggan='Gulo', alamat='Di Matahari', telp='0
02' where kode_pelanggan='P0004';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from pelanggan;
+----------------+----------------+-------------------------+--------------+
| kode_pelanggan | nama_pelanggan | alamat                  | telp         |
+----------------+----------------+-------------------------+--------------+
| P0008          | rodesri        | Di Bumi                 | Gak Ada      |
| P0002          | Rona           | Disamping Rumah Rodesri | Rahasia Dong |
| P0003          | Agus           | Di Mars                 | 008          |
| P0004          | Gulo           | Di Matahari             | 002          |
| P0005          | Hikma          | Matahari                | Gak Ada      |
+----------------+----------------+-------------------------+--------------+
5 rows in set (0.05 sec)

mysql> delete from pelanggan where kode_pelanggan='P0003';
Query OK, 1 row affected (0.00 sec)

mysql> select * from pelanggan;
+----------------+----------------+-------------------------+--------------+
| kode_pelanggan | nama_pelanggan | alamat                  | telp         |
+----------------+----------------+-------------------------+--------------+
| P0008          | rodesri        | Di Bumi                 | Gak Ada      |
| P0002          | Rona           | Disamping Rumah Rodesri | Rahasia Dong |
| P0004          | Gulo           | Di Matahari             | 002          |
| P0005          | Hikma          | Matahari                | Gak Ada      |
+----------------+----------------+-------------------------+--------------+
4 rows in set (0.00 sec)

mysql>