Pages

Subscribe:

Selasa, 13 Mei 2014

Cara Mengakses CMD dengan data INNER JOIN,LEFT JOIN< RIGHT JOIN dan VIEW

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| akademik           |
| cdcol              |
| db_penilaian       |
| ifsu               |
| kepegawaian        |
| latihan            |
| mahasiswa          |
| mysql              |
| penilaian_euisayu  |
| penilaian_maesaroh |
| phpmyadmin         |
| test               |
| toko               |
+--------------------+
14 rows in set (0.00 sec)

mysql> use toko;
Database changed
mysql> show tables;
+----------------+
| Tables_in_toko |
+----------------+
| brg            |
| ktg            |
| plg            |
| trans          |
+----------------+
4 rows in set (0.00 sec)

mysql> Select * From brg INNER JOIN ktg ON ktg.kode_ktg=brg.kode_ktg;
+----------+---------------+------+-------+----------+----------+------------+
| kode_brg | nama_brg      | stok | harga | kode_ktg | kode_ktg | kategori   |
+----------+---------------+------+-------+----------+----------+------------+
| B001     | Buku Tulis    |    8 |  1500 | K001     | K001     | Inventaris |
| B002     | Kertas HVS    |    5 | 35000 | K001     | K001     | Inventaris |
| B003     | Ballpoint     |   25 |  1000 | K002     | K002     | Modal      |
| B004     | Penghapus     |   30 |  2500 | K002     | K002     | Modal      |
| B005     | Rautan Pensil |   10 |  2000 | K002     | K002     | Modal      |
+----------+---------------+------+-------+----------+----------+------------+
5 rows in set (0.02 sec)

mysql> Select * From brg LEFT JOIN ktg ON ktg.kode_ktg=brg.kode_ktg;
+----------+---------------+------+-------+----------+----------+------------+
| kode_brg | nama_brg      | stok | harga | kode_ktg | kode_ktg | kategori   |
+----------+---------------+------+-------+----------+----------+------------+
| B001     | Buku Tulis    |    8 |  1500 | K001     | K001     | Inventaris |
| B002     | Kertas HVS    |    5 | 35000 | K001     | K001     | Inventaris |
| B003     | Ballpoint     |   25 |  1000 | K002     | K002     | Modal      |
| B004     | Penghapus     |   30 |  2500 | K002     | K002     | Modal      |
| B005     | Rautan Pensil |   10 |  2000 | K002     | K002     | Modal      |
| B006     | Sapu Lidi     |   18 |  4000 |          | NULL     | NULL       |
+----------+---------------+------+-------+----------+----------+------------+
6 rows in set (0.00 sec)

mysql> Select * From brg LEFT JOIN ktg ON ktg.kode_ktg=brg.kode_ktg;
+----------+---------------+------+-------+----------+----------+------------+
| kode_brg | nama_brg      | stok | harga | kode_ktg | kode_ktg | kategori   |
+----------+---------------+------+-------+----------+----------+------------+
| B001     | Buku Tulis    |    8 |  1500 | K001     | K001     | Inventaris |
| B002     | Kertas HVS    |    5 | 35000 | K001     | K001     | Inventaris |
| B003     | Ballpoint     |   25 |  1000 | K002     | K002     | Modal      |
| B004     | Penghapus     |   30 |  2500 | K002     | K002     | Modal      |
| B005     | Rautan Pensil |   10 |  2000 | K002     | K002     | Modal      |
| B006     | Sapu Lidi     |   18 |  4000 |          | NULL     | NULL       |
+----------+---------------+------+-------+----------+----------+------------+
6 rows in set (0.00 sec)

mysql> Select * From brg RIGHT JOIN ktg ON ktg.kode_ktg=brg.kode_ktg;
+----------+---------------+------+-------+----------+----------+------------+
| kode_brg | nama_brg      | stok | harga | kode_ktg | kode_ktg | kategori   |
+----------+---------------+------+-------+----------+----------+------------+
| B001     | Buku Tulis    |    8 |  1500 | K001     | K001     | Inventaris |
| B002     | Kertas HVS    |    5 | 35000 | K001     | K001     | Inventaris |
| B003     | Ballpoint     |   25 |  1000 | K002     | K002     | Modal      |
| B004     | Penghapus     |   30 |  2500 | K002     | K002     | Modal      |
| B005     | Rautan Pensil |   10 |  2000 | K002     | K002     | Modal      |
| NULL     | NULL          | NULL |  NULL | NULL     | K003     | Titipan    |
+----------+---------------+------+-------+----------+----------+------------+
6 rows in set (0.00 sec)

mysql> CREATE VIEW QBrg AS (SELECT * FROM brg);
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM QBrg;
+----------+---------------+------+-------+----------+
| kode_brg | nama_brg      | stok | harga | kode_ktg |
+----------+---------------+------+-------+----------+
| B001     | Buku Tulis    |    8 |  1500 | K001     |
| B002     | Kertas HVS    |    5 | 35000 | K001     |
| B003     | Ballpoint     |   25 |  1000 | K002     |
| B004     | Penghapus     |   30 |  2500 | K002     |
| B005     | Rautan Pensil |   10 |  2000 | K002     |
| B006     | Sapu Lidi     |   18 |  4000 |          |
+----------+---------------+------+-------+----------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM brg;
+----------+---------------+------+-------+----------+
| kode_brg | nama_brg      | stok | harga | kode_ktg |
+----------+---------------+------+-------+----------+
| B001     | Buku Tulis    |    8 |  1500 | K001     |
| B002     | Kertas HVS    |    5 | 35000 | K001     |
| B003     | Ballpoint     |   25 |  1000 | K002     |
| B004     | Penghapus     |   30 |  2500 | K002     |
| B005     | Rautan Pensil |   10 |  2000 | K002     |
| B006     | Sapu Lidi     |   18 |  4000 |          |
+----------+---------------+------+-------+----------+
6 rows in set (0.00 sec)

mysql> CREATE VIEW JOINBARANG AS(Select brg.*,ktg.kategori FROM brg INNER JOIN k
tg ON brg.kode_ktg=ktg.kode_ktg);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM JOINBARANG;
+----------+---------------+------+-------+----------+------------+
| kode_brg | nama_brg      | stok | harga | kode_ktg | kategori   |
+----------+---------------+------+-------+----------+------------+
| B001     | Buku Tulis    |    8 |  1500 | K001     | Inventaris |
| B002     | Kertas HVS    |    5 | 35000 | K001     | Inventaris |
| B003     | Ballpoint     |   25 |  1000 | K002     | Modal      |
| B004     | Penghapus     |   30 |  2500 | K002     | Modal      |
| B005     | Rautan Pensil |   10 |  2000 | K002     | Modal      |
+----------+---------------+------+-------+----------+------------+
5 rows in set (0.02 sec)

mysql> SELECT ('DATA ANADA AMAN');
+-----------------+
| DATA ANADA AMAN |
+-----------------+
| DATA ANADA AMAN |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT ('DATA ANADA AMAN JIKA DI FOLDER TERENKRIPSI');
+--------------------------------------------+
| DATA ANADA AMAN JIKA DI FOLDER TERENKRIPSI |
+--------------------------------------------+
| DATA ANADA AMAN JIKA DI FOLDER TERENKRIPSI |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE VIEW KALIMAT AS(SELECT('DATA ANADA AMAN JIKA DI FOLDER TERENKRIPSI
'));
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM KALIMAT;
+--------------------------------------------+
| DATA ANADA AMAN JIKA DI FOLDER TERENKRIPSI |
+--------------------------------------------+
| DATA ANADA AMAN JIKA DI FOLDER TERENKRIPSI |
+--------------------------------------------+
1 row in set (0.00 sec)