1.Memasukan data kolom tertentu
saya punya tabel kelas
mysql> select*from kelas;
+--------+--------+-----------+-------+--------+-------+
| No_kel | kode_m | kapasitas | waktu | hari | tahun |
+--------+--------+-----------+-------+--------+-------+
| B-09 | EE2073 | 30 | 09:00 | RABU | 2001 |
| B-10 | EE2073 | 30 | 09:00 | RABU | 2001 |
| B-101 | EE2423 | 50 | 07:00 | SENIN | 2001 |
| B-102 | EE2353 | 50 | 10:00 | SELASA | 2001 |
| B-103 | EE2313 | 50 | 08:00 | RABU | 2001 |
| B-104 | EE2313 | 50 | 08:00 | RABU | 2001 |
| B-105 | EE112 | 50 | 08:00 | JUMAT | 2001 |
| B-106 | EE2323 | 50 | 15:00 | SELASA | 2001 |
| B-107 | EE1012 | 50 | 13:00 | SENIN | 2001 |
| B-108 | EE2013 | 50 | 10:00 | KAMIS | 2001 |
+--------+--------+-----------+-------+--------+-------+
10 rows in set (0.01 sec)
mysql> select no_kel,kode_m,kapasitas
-> from kelas;
+--------+--------+-----------+
| no_kel | kode_m | kapasitas |
+--------+--------+-----------+
| B-09 | EE2073 | 30 |
| B-10 | EE2073 | 30 |
| B-101 | EE2423 | 50 |
| B-102 | EE2353 | 50 |
| B-103 | EE2313 | 50 |
| B-104 | EE2313 | 50 |
| B-105 | EE112 | 50 |
| B-106 | EE2323 | 50 |
| B-107 | EE1012 | 50 |
| B-108 | EE2013 | 50 |
+--------+--------+-----------+
10 rows in set (0.00 sec)
2. menampilkan data kolom tertntu dengan urutan
mysql> select no_kel,kode_m,kapasitas
-> from kelas
-> order by No_kel;
+--------+--------+-----------+
| no_kel | kode_m | kapasitas |
+--------+--------+-----------+
| B-09 | EE2073 | 30 |
| B-10 | EE2073 | 30 |
| B-101 | EE2423 | 50 |
| B-102 | EE2353 | 50 |
| B-103 | EE2313 | 50 |
| B-104 | EE2313 | 50 |
| B-105 | EE112 | 50 |
| B-106 | EE2323 | 50 |
| B-107 | EE1012 | 50 |
| B-108 | EE2013 | 50 |
+--------+--------+-----------+
10 rows in set (0.00 sec)
3. Menampilkan data baris dan kolom tertentu
mysql> select no_kel,kode_m,kapasitas
-> from kelas
-> where kapasitas=30;
+--------+--------+-----------+
| no_kel | kode_m | kapasitas |
+--------+--------+-----------+
| B-09 | EE2073 | 30 |
| B-10 | EE2073 | 30 |
+--------+--------+-----------+
2 rows in set (0.00 sec)
4. Menampilkan data baris dan kolom tertentu dengan urutan
mysql> select no_kel,kode_m,kapasitas
-> from kelas
-> where kapasitas=30
-> order by No_kel;
+--------+--------+-----------+
| no_kel | kode_m | kapasitas |
+--------+--------+-----------+
| B-09 | EE2073 | 30 |
| B-10 | EE2073 | 30 |
+--------+--------+-----------+
2 rows in set (0.00 sec)
5. operator logika (and, or,not)
mysql> select no_kel,kode_m,kapasitas,waktu,hari
-> from kelas
-> where (hari='rabu'
-> or kapasitas='50')
-> and no_kel='B-09';
+--------+--------+-----------+-------+------+
| no_kel | kode_m | kapasitas | waktu | hari |
+--------+--------+-----------+-------+------+
| B-09 | EE2073 | 30 | 09:00 | RABU |
+--------+--------+-----------+-------+------+
1 row in set (0.02 sec)
6. Urutan terbalik desc
mysql> select no_kel,kode_m,kapasitas,waktu,hari
-> from kelas
-> order by no_kel desc;
+--------+--------+-----------+-------+--------+
| no_kel | kode_m | kapasitas | waktu | hari |
+--------+--------+-----------+-------+--------+
| B-108 | EE2013 | 50 | 10:00 | KAMIS |
| B-107 | EE1012 | 50 | 13:00 | SENIN |
| B-106 | EE2323 | 50 | 15:00 | SELASA |
| B-105 | EE112 | 50 | 08:00 | JUMAT |
| B-104 | EE2313 | 50 | 08:00 | RABU |
| B-103 | EE2313 | 50 | 08:00 | RABU |
| B-102 | EE2353 | 50 | 10:00 | SELASA |
| B-101 | EE2423 | 50 | 07:00 | SENIN |
| B-10 | EE2073 | 30 | 09:00 | RABU |
| B-09 | EE2073 | 30 | 09:00 | RABU |
+--------+--------+-----------+-------+--------+
10 rows in set (0.00 sec)
7. urutan sesuai no abjad
apa ya kata yang cocok tidak terbalik mungkin cocok asc
mysql> select no_kel,kode_m,kapasitas,waktu,hari
-> from kelas
-> order by no_kel asc;
+--------+--------+-----------+-------+--------+
| no_kel | kode_m | kapasitas | waktu | hari |
+--------+--------+-----------+-------+--------+
| B-09 | EE2073 | 30 | 09:00 | RABU |
| B-10 | EE2073 | 30 | 09:00 | RABU |
| B-101 | EE2423 | 50 | 07:00 | SENIN |
| B-102 | EE2353 | 50 | 10:00 | SELASA |
| B-103 | EE2313 | 50 | 08:00 | RABU |
| B-104 | EE2313 | 50 | 08:00 | RABU |
| B-105 | EE112 | 50 | 08:00 | JUMAT |
| B-106 | EE2323 | 50 | 15:00 | SELASA |
| B-107 | EE1012 | 50 | 13:00 | SENIN |
| B-108 | EE2013 | 50 | 10:00 | KAMIS |
+--------+--------+-----------+-------+--------+
10 rows in set (0.00 sec)
8. perintah update
Perintah UPDATE digunakan untuk mengubah nilai suatu record. Berikut adalah contoh penggunaan UPDATE.
no_kel B-0 9yang kapasitasnya 30 menjadi 20
mysql> select*from kelas;
+--------+--------+-----------+-------+--------+-------+
| No_kel | kode_m | kapasitas | waktu | hari | tahun |
+--------+--------+-----------+-------+--------+-------+
| B-09 | EE2073 | 30 | 09:00 | RABU | 2001 |
| B-10 | EE2073 | 30 | 09:00 | RABU | 2001 |
| B-101 | EE2423 | 50 | 07:00 | SENIN | 2001 |
| B-102 | EE2353 | 50 | 10:00 | SELASA | 2001 |
| B-103 | EE2313 | 50 | 08:00 | RABU | 2001 |
| B-104 | EE2313 | 50 | 08:00 | RABU | 2001 |
| B-105 | EE112 | 50 | 08:00 | JUMAT | 2001 |
| B-106 | EE2323 | 50 | 15:00 | SELASA | 2001 |
| B-107 | EE1012 | 50 | 13:00 | SENIN | 2001 |
| B-108 | EE2013 | 50 | 10:00 | KAMIS | 2001 |
+--------+--------+-----------+-------+--------+-------+
10 rows in set (0.01 sec)
setelah pakai perintah update menjadi 20
mysql> update kelas
-> set kapasitas='20'
-> where no_kel='B-09';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select*from kelas;
+--------+--------+-----------+-------+--------+-------+
| No_kel | kode_m | kapasitas | waktu | hari | tahun |
+--------+--------+-----------+-------+--------+-------+
| B-09 | EE2073 | 20 | 09:00 | RABU | 2001 |
| B-10 | EE2073 | 30 | 09:00 | RABU | 2001 |
| B-101 | EE2423 | 50 | 07:00 | SENIN | 2001 |
| B-102 | EE2353 | 50 | 10:00 | SELASA | 2001 |
| B-103 | EE2313 | 50 | 08:00 | RABU | 2001 |
| B-104 | EE2313 | 50 | 08:00 | RABU | 2001 |
| B-105 | EE112 | 50 | 08:00 | JUMAT | 2001 |
| B-106 | EE2323 | 50 | 15:00 | SELASA | 2001 |
| B-107 | EE1012 | 50 | 13:00 | SENIN | 2001 |
| B-108 | EE2013 | 50 | 10:00 | KAMIS | 2001 |
+--------+--------+-----------+-------+--------+-------+
10 rows in set (0.01 sec)
9. DELETE
Untuk menghapus satu atau lebih data, kita menggunakan perintah DELETE. Berikut adalah beberapa contoh cara penghapusan record.
saya akan menghapus no_kelas B-09 begini perintahnya
mysql> delete from kelas
-> where No_kel='B-09';
Query OK, 1 row affected (0.02 sec)
mysql> select*from kelas;
+--------+--------+-----------+-------+--------+-------+
| No_kel | kode_m | kapasitas | waktu | hari | tahun |
+--------+--------+-----------+-------+--------+-------+
| B-10 | EE2073 | 30 | 09:00 | RABU | 2001 |
| B-101 | EE2423 | 50 | 07:00 | SENIN | 2001 |
| B-102 | EE2353 | 50 | 10:00 | SELASA | 2001 |
| B-103 | EE2313 | 50 | 08:00 | RABU | 2001 |
| B-104 | EE2313 | 50 | 08:00 | RABU | 2001 |
| B-105 | EE112 | 50 | 08:00 | JUMAT | 2001 |
| B-106 | EE2323 | 50 | 15:00 | SELASA | 2001 |
| B-107 | EE1012 | 50 | 13:00 | SENIN | 2001 |
| B-108 | EE2013 | 50 | 10:00 | KAMIS | 2001 |
+--------+--------+-----------+-------+--------+-------+
9 rows in set (0.02 sec)
10. Manupilasi Table
ALTER
Perintah ALTER digunakan untuk merubah, menambah atau menghapus properti tabel. Kita bisa menambah / menghapus / mengganti
nama field, nama tabel, menambah / menghapus auto_increment dan primary key, dsb.
mysql> describe kelas;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| No_kel | char(5) | NO | PRI | | |
| kode_m | char(6) | YES | | NULL | |
| kapasitas | int(2) | YES | | NULL | |
| waktu | varchar(5) | YES | | NULL | |
| hari | char(8) | YES | | NULL | |
| tahun | int(4) | YES | | NULL | |
+-----------+------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
a.Menambah field
mysql> alter table kelas
-> add nama varchar(50);
Query OK, 9 rows affected (0.17 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> describe kelas;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| No_kel | char(5) | NO | PRI | | |
| kode_m | char(6) | YES | | NULL | |
| kapasitas | int(2) | YES | | NULL | |
| waktu | varchar(5) | YES | | NULL | |
| hari | char(8) | YES | | NULL | |
| tahun | int(4) | YES | | NULL | |
| nama | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.02 sec)
b. modify
digunakan untuk menguabah suatu kolom
mysql> alter table kelas
-> modify nama char(20);
Query OK, 9 rows affected (0.14 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> describe kelas;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| No_kel | char(5) | NO | PRI | | |
| kode_m | char(6) | YES | | NULL | |
| kapasitas | int(2) | YES | | NULL | |
| waktu | varchar(5) | YES | | NULL | |
| hari | char(8) | YES | | NULL | |
| tahun | int(4) | YES | | NULL | |
| nama | char(20) | YES | | NULL | |
+-----------+------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
c. Drop
Digunakan untuk menghapus sebuah field
mysql> alter table kelas
-> drop nama;
Query OK, 9 rows affected (0.13 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> describe kelas;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| No_kel | char(5) | NO | PRI | | |
| kode_m | char(6) | YES | | NULL | |
| kapasitas | int(2) | YES | | NULL | |
| waktu | varchar(5) | YES | | NULL | |
| hari | char(8) | YES | | NULL | |
| tahun | int(4) | YES | | NULL | |
+-----------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
cukup sekian hanya itu yang bisa saya sampaikan kepada sobat semoga bermanfaat insyaalah nanti kalau da waktu dan kesempatan akan saya tambah lagi tutorial nya
cape juga ya ternyata ngetiknya tapi seneng ko itung-itung saya juga lagi belajar Catatan : Jangan Lupa Baca dan Klik Dibawah ini ya
0 komentar:
Posting Komentar