Latihan Membuat Database Mahasiswa menggunakan mysql


Pada kesempatan kali ini saya gunakan untuk menuliskan catatan saya yang berisi latihan membuat database mahasiswa menggunakan mysql yang bertujuan sebagai catatan pengingat saya dan semoga juga bermanfaat untuk orang lain

Ada saatnya dimana kita harus mengganti struktur table yang pernah kita buat. Untungnya MySql mendukung/menyediakan perintah untuk melakukan kegiatan tersebut. Perintahnya adalah ALTER TABLE. Penggunaanya sedikit sulit. Mudah-mudahan penjelasan saya bisa menambah pemahaman bagi saya dan pembaca semua supaya lebih mudah untuk dipelajari. Bentuk umum perintahnya adalah sebagai berikut :

ALTER TABLE merupakan perintah dasar untuk mengubah tabel.
nama_tabel merupakan nama tabel yang akan diubah strukturnya.
alter_options merupakan pilihan perubahan tabel.
Contoh Option yang bisa digunakan adalah sebagai berikut:

Nama Option Fungsi Option
1.ADD nama_field/kolom_baru untuk menambahkan field/kolom baru.
2.ADD PRIMARY KEY (nama_field/kolom)untuk menambahkan primary key pada tabel
3.CHANGE nama_field_lama nama_field_baru_definisi_field_baru untuk mengubah nama_field_lama menjadi nama_field_baru_definisi_field_baru
4.definisi_field_baru artinya adalah type data.
5.MODIFY nama_field type_data untuk mengubah type_data suatu field
6.DROP nama_field untuk menghapus field nama_field
7.RENAME TO nama_tabel_baru untuk mengganti nama tabel
Sekarang saatnya kita bahas satu persatu supaya bisa paham.

contoh soal:
1-10

SQL>-- 1. desc mhs
 Name                                                  Type
 ----------------------------------------- ---------------------------- NIM_MHS                                            VARCHAR(15)
 NAMA_MHS                                           VARCHAR(50) ALAMAT_MHS                                         VARCHAR(50)SQL> -- 2. ubah nama field nim pada table mhs menjadi 'MHS_NPM'
SQL> -- dan nama field JENKEL pada table mhs menjadi 'MHS_JK'SQL> -- 3. ubah field type data pada field alamat_mhs menjadi varchar(100)SQL> -- 4. tambahkan field MHS_EMAIL varchar(35) kedalam table mhsSQL> -- 5. tambahkan  field MHS_JK setelah field NAMA_MHS
SQL> -- 6. ubah tipe data 'MHS_NPM' pada table menjadi int(10)
SQL> -- 7. tambahkan  primary key pada field mhs_npmSQL> -- 8. tambahkan  field MHS_TELP setelah field ALAMAT_MHS
SQL> -- 9. Hapus field MHS_EMAIL pada table MHS
SQL> -- 10. Ubah nama table MHS menjadi MAHASISWA

Catatan: yang berwarna merah saya tidak menggerti atau tidak mudeng asal nya dari mana

pengerjaan

- buka comandprompt

dengan cara menekan tombol keyboard windows+R atau start search programs and files ketik run

masukan perintah cmd "comandprompt"

catatan: tulisan yang berwarna merah jangan diikuti karna salah sengaja saya masukan karena untuk pembelajaran saya pengkoreksian dimana letak kesalahan yang saya lakukan

SQL>-- 1. desc mhs
 Name                                                  Type
 ----------------------------------------- ---------------------------- NIM_MHS                                            VARCHAR(15)
 NAMA_MHS                                           VARCHAR(50) ALAMAT_MHS                                         VARCHAR(50)

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

C:\Users\USER>cd ..

C:\Users>cd ..

C:\>xampp\mysql\bin
'xampp\mysql\bin' is not recognized as an internal or external command,
operable program or batch file.

C:\>cd c:\xampp\mysql\bin

c:\xampp\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 680
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

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

mysql> create database muhammadrizal;
ERROR 1007 (HY000): Can't create database 'muhammadrizal'; database exists
mysql> createdatabase muhammadrizaluye;
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 'creat
edatabase muhammadrizaluye' at line 1
mysql> create database muhammadrizaluye;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| dbjilbab           |
| muhammadrizal      |
| muhammadrizaluye   |
| mysql              |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
9 rows in set (0.00 sec)

mysql> use muhammadrizaluye;
Database changed
mysql> create table mhs(nim_mhs varchar(15),nama_mhs varchar(50),alamat_mhs varc
har(50));
Query OK, 0 rows affected (1.75 sec)

mysql> use muhammadrizaluye;
Database changed
mysql> desc mhs;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| nim_mhs    | varchar(15) | YES  |     | NULL    |       |
| nama_mhs   | varchar(50) | YES  |     | NULL    |       |
| alamat_mhs | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

2. ubah nama field nim pada table mhs menjadi 'MHS_NPM'


mysql> alter field rename nim_mhs to npm_mhs;
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 'field
 rename nim_mhs to npm_mhs' at line 1
mysql> alter table nim_mhs rename to npm_mhs;
ERROR 1146 (42S02): Table 'muhammadrizaluye.nim_mhs' doesn't exist
mysql> alter table nim_mhs rename npm_mhs;
ERROR 1146 (42S02): Table 'muhammadrizaluye.nim_mhs' doesn't exist
mysql> alter table nim_mhs change npm_mhs;
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 '' at
line 1
mysql> alter table mhs change nim_mhs npm_mhs char(1);
Query OK, 0 rows affected (1.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs
    -> desc mhs;
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 'desc
mhs' at line 2
mysql> desc mhs;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| npm_mhs    | char(1)     | YES  |     | NULL    |       |
| nama_mhs   | varchar(50) | YES  |     | NULL    |       |
| alamat_mhs | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

3. ubah field type data pada field alamat_mhs menjadi varchar(100)


mysql> alter table mhs moodifly alamat_mhs varchar(100);
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 'moodi
fly alamat_mhs varchar(100)' at line 1
mysql> alter table mhs modifly alamat-mhs varchar(100);
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 'modif
ly alamat-mhs varchar(100)' at line 1
mysql> alter talbe mhs modify alamat_mhs varchar(100);
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 'talbe
 mhs modify alamat_mhs varchar(100)' at line 1
mysql> alter table mhs modify alamat_mhs varchar(100);
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| npm_mhs    | char(1)      | YES  |     | NULL    |       |
| nama_mhs   | varchar(50)  | YES  |     | NULL    |       |
| alamat_mhs | varchar(100) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table modify npm_mhs varchar(15);
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 'npm_m
hs varchar(15)' at line 1
mysql> alter table mhs modify npm_mhs varchar(15);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| npm_mhs    | varchar(15)  | YES  |     | NULL    |       |
| nama_mhs   | varchar(50)  | YES  |     | NULL    |       |
| alamat_mhs | varchar(100) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4. tambahkan field MHS_EMAIL varchar(35) kedalam table mhs


mysql> alter table mhs email_mhs varchar(35) after alamat_mhs;
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 'email
_mhs varchar(35) after alamat_mhs' at line 1
mysql> alter table mhs email_mhs varchar(35) after alamat_mhs;
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 'email
_mhs varchar(35) after alamat_mhs' at line 1
mysql> alter table mhs add email_mhs varchar(35) after alamat_mhs;
Query OK, 0 rows affected (1.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| npm_mhs    | varchar(15)  | YES  |     | NULL    |       |
| nama_mhs   | varchar(50)  | YES  |     | NULL    |       |
| alamat_mhs | varchar(100) | YES  |     | NULL    |       |
| email_mhs  | varchar(35)  | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

5. tambahkan  field MHS_JK setelah field NAMA_MHS


mysql> alter table mhs_ add JK_mhs after nama_mhs;
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 'after
 nama_mhs' at line 1
mysql> alter table mhs add jk_mhs after nama_mhs;
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 'after
 nama_mhs' at line 1
mysql> alter tabele mhs add jk_mhs varchar(2) after nama_mhs;
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 'tabel
e mhs add jk_mhs varchar(2) after nama_mhs' at line 1
mysql> alter table mhs add jk_mhs varchar(2) after nama_mhs;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| npm_mhs    | varchar(15)  | YES  |     | NULL    |       |
| nama_mhs   | varchar(50)  | YES  |     | NULL    |       |
| jk_mhs     | varchar(2)   | YES  |     | NULL    |       |
| alamat_mhs | varchar(100) | YES  |     | NULL    |       |
| email_mhs  | varchar(35)  | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

6. ubah tipe data 'MHS_NPM' pada table menjadi int(10)


mysql> alter table mhs modify npm_mhs int(10);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| npm_mhs    | int(10)      | YES  |     | NULL    |       |
| nama_mhs   | varchar(50)  | YES  |     | NULL    |       |
| jk_mhs     | varchar(2)   | YES  |     | NULL    |       |
| alamat_mhs | varchar(100) | YES  |     | NULL    |       |
| email_mhs  | varchar(35)  | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

7. tambahkan  primary key pada field mhs_npm


mysql> alter table mhs add primary key npm_mhs;
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 '' at
line 1
mysql> alter table mhs add primary key(npm_mhs);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| npm_mhs    | int(10)      | NO   | PRI | 0       |       |
| nama_mhs   | varchar(50)  | YES  |     | NULL    |       |
| jk_mhs     | varchar(2)   | YES  |     | NULL    |       |
| alamat_mhs | varchar(100) | YES  |     | NULL    |       |
| email_mhs  | varchar(35)  | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.10 sec)


8. tambahkan  field MHS_TELP setelah field ALAMAT_MHS

mysql> alter table mhs add tlp_mhs varchar(3) after alamat_mhs;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| npm_mhs    | int(10)      | NO   | PRI | 0       |       |
| nama_mhs   | varchar(50)  | YES  |     | NULL    |       |
| jk_mhs     | varchar(2)   | YES  |     | NULL    |       |
| alamat_mhs | varchar(100) | YES  |     | NULL    |       |
| tlp_mhs    | varchar(3)   | YES  |     | NULL    |       |
| email_mhs  | varchar(35)  | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

9Hapus field MHS_EMAIL pada table MHS


mysql> alter table mhs drop column email_mhs;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mhs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| npm_mhs    | int(10)      | NO   | PRI | 0       |       |
| nama_mhs   | varchar(50)  | YES  |     | NULL    |       |
| jk_mhs     | varchar(2)   | YES  |     | NULL    |       |
| alamat_mhs | varchar(100) | YES  |     | NULL    |       |
| tlp_mhs    | varchar(3)   | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

10Ubah nama table MHS menjadi MAHASISWA

mysql> alter table mhs rename to MAHASISWA;
Query OK, 0 rows affected (0.07 sec)

mysql> desc mahasiswa;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| npm_mhs    | int(10)      | NO   | PRI | 0       |       |
| nama_mhs   | varchar(50)  | YES  |     | NULL    |       |
| jk_mhs     | varchar(2)   | YES  |     | NULL    |       |
| alamat_mhs | varchar(100) | YES  |     | NULL    |       |
| tlp_mhs    | varchar(3)   | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

mysql>



Demikianlah catatan saya mengenai Latihan Membuat Database Mahasiswa menggunakan mysql semoga dapat berguna untuk diri saya dan pembaca lain nya


















  









2 Responses to "Latihan Membuat Database Mahasiswa menggunakan mysql"

  1. rumit jugga yah gan membuat database seperti ini

    ReplyDelete
  2. kunjungan ke dua.....
    manfaat banget website/Blog nya.....
    keep posting yang positif gan/sist..
    ijin lihat-lihat blog/website nya ya.....

    jangan lupa kunjungi website nya ya :)

    cluster murah bekasi
    info rumah murah bekasi
    rumah murah bekasi

    ReplyDelete