My SQL

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\student>cd\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 |
+-------------+
| dbKampus |
| dbMahasiswa |
| mysql |
| test |
+-------------+
4 rows in set (0.00 sec)


Menghapus Database yang sudah di buat

mysql> drop database dbKampus;
Query OK, 6 rows affected (0.00 sec)

Membuat Database yang benar(yang ingin di gunakan)

mysql> create database dbkampus;
Query OK, 1 row affected (0.03 sec)

Menampilkan Database yang sudah di buat

mysql> show databases;
+-------------+
| Database |
+-------------+
| dbMahasiswa |
| dbkampus |
| mysql |
| test |
+-------------+
4 rows in set (0.00 sec)

Memilih database yang akan digunakan

mysql> use dbkampus;
Database changed



Tabel-Tabel :

Tabel Mahasiswa (tmhs)
Membuat Tabel tmhs 

mysql> create table tmhs(
  -> Nim varchar(10) primary key not null,
  -> Nama varchar(30) not null);
Query OK, 0 rows affected (0.00 sec)

Menampilkan Tabel yang ada dalam database kampus (dbkampus)

mysql> show tables;
+--------------------+
| Tables_in_dbkampus |
+--------------------+
| tmhs |
+--------------------+
1 row in set (0.00 sec)

Menampilkan Tabel yang telah dibuat (tmhs)

mysql> describe tmhs;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Nim | varchar(10) | | PRI | | |
| Nama | varchar(30) | | | | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Menginputkan data kedalam tmhs

mysql> insert into tmhs values
  -> ('2001','budi'),
  -> ('2002','caca'),
  -> ('2003','dina'),
  -> ('2004','adi'),
  -> ('2005','deni');
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0

Menampilkan isi yang ada dalam table mhs (tmhs)

mysql> select * from tmhs;
+------+------+
| Nim | Nama |
+------+------+
| 2001 | budi |
| 2002 | caca |
| 2003 | dina |
| 2004 | adi |
| 2005 | deni |
+------+------+
5 rows in set (0.00 sec)

Tabel Nilai (tnilai)

Membuat table nilai(tnilai)

mysql> create table tnilai(
  -> id int(2) primary key auto_increment,
  -> nim varchar(20),
  -> nilai char(1),
  -> kodemk varchar(3));
Query OK, 0 rows affected (0.00 sec)


Menginputkan data kedalam table Nilai (tnilai)

mysql> insert into tnilai (nim,nilai,kodemk) values
  -> ('2001','A','m01'),
  -> ('2001','B','M02'),
  -> ('2001','A','M03'),
  -> ('2002','B','M01'),
  -> ('2003','C','M02'),
  -> ('2004','D','M03'),
  -> ('2005','A','M01');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

Menampilkan isi yang ada dalam table Nilai (tnilai)


mysql> select * from tnilai;
+----+------+-------+--------+
| id | nim | nilai | kodemk |
+----+------+-------+--------+
| 1 | 2001 | A | m01 |
| 2 | 2001 | B | M02 |
| 3 | 2001 | A | M03 |
| 4 | 2002 | B | M01 |
| 5 | 2003 | C | M02 |
| 6 | 2004 | D | M03 |
| 7 | 2005 | A | M01 |
+----+------+-------+--------+
7 rows in set (0.00 sec)


Tabel Mata Kuliah (tmk)


Membuat table mata kuliah (tmk)

mysql> create table tmk (
  -> kodemk varchar(10),
  -> namamk varchar(10),
  -> sks int);
Query OK, 0 rows affected (0.00 sec)

Menampilkan Tabel yang telah dibuat (tmk)

mysql> describe tmk;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| kodemk | varchar(10) | YES | | NULL | |
| namamk | varchar(10) | YES | | NULL | |
| sks | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Menginputkan data kedalam Tabel MataKuliah(tmk)

mysql> insert into tmk values
  -> ('M01','Matematika',3),
  -> ('M02','Agama',2),
  -> ('M03','Basis Data',3)
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

Menampilkan isi yang ada dalam table mata Kuliah (tmk)

mysql> select * from tmk;
+--------+------------+------+
| kodemk | namamk | sks |
+--------+------------+------+
| M01 | Matematika | 3 |
| M02 | Agama | 2 |
| M03 | Basis Data | 3 |
+--------+------------+------+
3 rows in set (0.00 sec)










Latihan
Perintah SQL


Contoh :
Mengurutkan Nama berdasarkan alphabet dari table Mahasiswa (tmhs)

mysql> select * from tmhs order by nama;
+------+------+
| Nim | Nama |
+------+------+
| 2004 | adi |
| 2001 | budi |
| 2002 | caca |
| 2005 | deni |
| 2003 | dina |
+------+------+
5 rows in set (0.00 sec)

Soal :

1.Tampilkan Nama Mahasiswa (nama) dan Nama MataKuliah (namamk) yg telah diambil??

mysql> select tmhs.nama,tmk.namamk from
  -> tmhs,tnilai,tmk where
  -> tmhs.nim = tnilai.nim and
  -> tnilai.kodemk = tmk.kodemk;
+------+------------+
| nama | namamk |
+------+------------+
| budi | Matematika |
| caca | Matematika |
| deni | Matematika |
| budi | Agama |
| dina | Agama |
| budi | Basis Data |
| adi | Basis Data |
+------+------------+













2.Tampilkan NIM,nama,namamk ??

mysql> select * from tmk order by namamk,sks;
+--------+------------+------+
| kodemk | namamk | sks |
+--------+------------+------+
| M02 | Agama | 2 |
| M03 | Basis Data | 3 |
| M01 | Matematika | 3 |
+--------+------------+------+
3 rows in set (0.00 sec)


mysql> select * from tnilai order by nilai;
+----+------+-------+--------+
| id | nim | nilai | kodemk |
+----+------+-------+--------+
| 1 | 2001 | A | m01 |
| 3 | 2001 | A | M03 |
| 7 | 2005 | A | M01 |
| 2 | 2001 | B | M02 |
| 4 | 2002 | B | M01 |
| 5 | 2003 | C | M02 |
| 6 | 2004 | D | M03 |
+----+------+-------+--------+
7 rows in set (0.00 sec)

mysql> select tmhs.nama,tmk.namamk from
  -> tmhs,tnilai,tmk where
  -> tmhs.nim = tnilai.nim and
  -> tnilai.kodemk = tmk.kodemk;
+------+------------+
| nama | namamk |
+------+------------+
| budi | Matematika |
| caca | Matematika |
| deni | Matematika |
| budi | Agama |
| dina | Agama |
| budi | Basis Data |
| adi | Basis Data |
+------+------------+
7 rows in set (0.00 sec)

mysql> select











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

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

mysql> use dbkampus;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_dbkampus |
+--------------------+
| tmhs |
| tmk |
| tnilai |
+--------------------+
3 rows in set (0.00 sec)

7 rows in set (0.00 sec)

mysql> select tmhs.nim,tmhs.nama,tmk.namamk from
  -> tmhs,tnilai,tmk where
  -> tmhs.nim = tnilai.nim and
  -> tmk.kodemk = tnilai.kodemk and
  -> tnilai.nilai = 'A';
+------+------+------------+
| nim | nama | namamk |
+------+------+------------+
| 2001 | budi | Matematika |
| 2005 | deni | Matematika |
| 2001 | budi | Basis Data |
+------+------+------------+
3 rows in set (0.01 sec)








mysql> select tmhs.nim,tmhs.nama,tmk.namamk from
  -> tmhs,tnilai,tmk where
  -> tmhs.nim = tnilai.nim and
  -> tnilai.kodemk = tmk.kodemk and
  -> tmk.sks = '3';

+------+------+------------+
| nim | nama | namamk |
+------+------+------------+
| 2001 | budi | Matematika |
| 2002 | caca | Matematika |
| 2005 | deni | Matematika |
| 2001 | budi | Basis Data |
| 2004 | adi | Basis Data |
+------+------+------------+
5 rows in set (0.00 sec)