begini perintahnya ketikan mysql -u root -p, kemudian tuliskan password sobat seperti contoh dibawah ini
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\RABEL>mysql -u root -p
Enter password: ***********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
2. Melihat isi database begini perintahnya show databases; seperti contoh dibawah ini
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| personaliadb |
| praktikum |
| test |
| latihan |
+--------------------+
5 rows in set (0.05 sec)
3.membuat databases seperti ini perintahnya
create database latihan;
seperti contoh dibawah ini
mysql> create database latihan;
Query OK, 1 row affected (0.03 sec)
4. membuat tabel
mysql> create table karyawan
-> (noid int unsigned auto_increment primary key,
-> nama varchar(35) not null,
-> jeniskelamin char(2),
-> kota varchar(25),
-> tgllahir date);
Query OK, 0 rows affected (0.03 sec)
5. Melihat isi tabel
mysql> describe karyawan;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| noid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| nama | varchar(35) | NO | | | |
| jeniskelamin | char(2) | YES | | NULL | |
| kota | varchar(25) | YES | | NULL | |
| tgllahir | date | YES | | NULL | |
+--------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
6. mengisi tabel cara pertama dengan perintah di bawah ini
mysql> insert into karyawan (noid,nama,jeniskelamin,kota,tgllahir) values ('1','
Abdullah Hamim','P','Cileungsi','1977-01-01');
Query OK, 1 row affected (0.02 sec)
mysql> select*from karyawan;
+------+----------------+--------------+-----------+------------+
| noid | nama | jeniskelamin | kota | tgllahir |
+------+----------------+--------------+-----------+------------+
| 1 | Abdullah Hamim | P | Cileungsi | 1977-01-01 |
+------+----------------+--------------+-----------+------------+
1 row in set (0.00 sec)
7. menambah field pada sebuah kolom
mysql> alter table karyawan add kodepos varchar(5);
Query OK, 1 row affected (0.19 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> describe karyawan;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| noid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| nama | varchar(35) | NO | | | |
| jeniskelamin | char(2) | YES | | NULL | |
| kota | varchar(25) | YES | | NULL | |
| tgllahir | date | YES | | NULL | |
| kodepos | varchar(5) | YES | | NULL | |
+--------------+------------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
8. mengisi tabel cara ke dua
mysql> insert into karyawan values
-> ("2","Dion","P","Bogor","1978-02-02","40123"),
-> ("3","Celine","P","Jakarta","1976-05-22","40333"),
-> ("4","Deemaz","L","Jakarta","1977-07-07","40223"),
-> ("5","Zachryal","L","Bogor","1979-04-11","40331");
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select*from karyawan;
+------+----------------+--------------+-----------+------------+---------+
| noid | nama | jeniskelamin | kota | tgllahir | kodepos |
+------+----------------+--------------+-----------+------------+---------+
| 1 | Abdullah Hamim | P | Cileungsi | 1977-01-01 | NULL |
| 2 | Dion | P | Bogor | 1978-02-02 | 40123 |
| 3 | Celine | P | Jakarta | 1976-05-22 | 40333 |
| 4 | Deemaz | L | Jakarta | 1977-07-07 | 40223 |
| 5 | Zachryal | L | Bogor | 1979-04-11 | 40331 |
+------+----------------+--------------+-----------+------------+---------+
5 rows in set (0.00 sec)
sebagai tambahan saja karena ada yang salah dengan penulisan jenis kelamin
abdullah hamim harusnya
L maka akan saya ganti dengan perintah di bawah ini
mysql> update karyawan
-> set jeniskelamin='L'
-> where noid='1';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
karena kode pos abdullah hamim null atau kosong maka kita tambahkan dengan perintah seperti dibawah ini
mysql> update karyawan
-> set kodepos="16820"
-> where noid='1';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select*from karyawan;
+------+----------------+--------------+-----------+------------+---------+
| noid | nama | jeniskelamin | kota | tgllahir | kodepos |
+------+----------------+--------------+-----------+------------+---------+
| 1 | Abdullah Hamim | L | Cileungsi | 1977-01-01 | 16820 |
| 2 | Dion | P | Bogor | 1978-02-02 | 40123 |
| 3 | Celine | P | Jakarta | 1976-05-22 | 40333 |
| 4 | Deemaz | L | Jakarta | 1977-07-07 | 40223 |
| 5 | Zachryal | L | Bogor | 1979-04-11 | 40331 |
+------+----------------+--------------+-----------+------------+---------+
5 rows in set (0.00 sec)
memasukan data yang banyak jumlahnya tidak mungkin lewat mysql tapi data
itu di tulis dalam notaped dengan format.sql contoh masukandata.sql
sobat copas saja data dibawah ini ke dalam notaped kemudian save dengan format.sql
# setelah itu baru data-data saya masukkan
INSERT INTO KARYAWAN
VALUES
("15","Data 1","4","Bogor","1978-02-02","40123"),
("16","Data 2","4","Bogor","1978-02-02","40123"),
("17","Data 3","4","Bogor","1978-02-02","40123"),
("18","Data 4","4","Bogor","1978-02-02","40123"),
("19","Data 5","4","Bogor","1978-02-02","40123"),
("20","Data 6","4","Bogor","1978-02-02","40123"),
("21","Data 7","4","Bogor","1978-02-02","40123"),
("22","Data 8","4","Bogor","1978-02-02","40123"),
("23","Data 9","4","Bogor","1978-02-02","40123"),
("24","Data 10","4","Bogor","1978-02-02","40123"),
("25","Data 11","4","Bogor","1978-02-02","40123")
;
kemudian buka command prompt tuliskan perintah mysql -h localhost -u root -p latihan < C:\masukandata.sql saya menyimpan data tersebut di drive C: dengan nama file nya masukan data Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\RABEL>mysql -h localhost -u root -p latihan < C:\masuk andata.sql Enter password: *********** C:\Documents and Settings\RABEL>
Memang tidak ada reaksi apa saja coba sekarang buka mysql ketikan perintah
use latihan lau
tuliskan perintah ini select*from karyawan; bisa sobat lihat hasilnya dibawah ini
mysql> select*from karyawan;
+------+----------------+--------------+-----------+------------+---------+
| noid | nama | jeniskelamin | kota | tgllahir | kodepos |
+------+----------------+--------------+-----------+------------+---------+
| 1 | Abdullah Hamim | L | Cileungsi | 1977-01-01 | 16820 |
| 2 | Dion | P | Bogor | 1978-02-02 | 40123 |
| 3 | Celine | P | Jakarta | 1976-05-22 | 40333 |
| 4 | Deemaz | L | Jakarta | 1977-07-07 | 40223 |
| 5 | Zachryal | L | Bogor | 1979-04-11 | 40331 |
| 15 | Data 1 | 4 | Bogor | 1978-02-02 | 40123 |
| 16 | Data 2 | 4 | Bogor | 1978-02-02 | 40123 |
| 17 | Data 3 | 4 | Bogor | 1978-02-02 | 40123 |
| 18 | Data 4 | 4 | Bogor | 1978-02-02 | 40123 |
| 19 | Data 5 | 4 | Bogor | 1978-02-02 | 40123 |
| 20 | Data 6 | 4 | Bogor | 1978-02-02 | 40123 |
| 21 | Data 7 | 4 | Bogor | 1978-02-02 | 40123 |
| 22 | Data 8 | 4 | Bogor | 1978-02-02 | 40123 |
| 23 | Data 9 | 4 | Bogor | 1978-02-02 | 40123 |
| 24 | Data 10 | 4 | Bogor | 1978-02-02 | 40123 |
| 25 | Data 11 | 4 | Bogor | 1978-02-02 | 40123 |
+------+----------------+--------------+-----------+------------+---------+
16 rows in set (0.00 sec)
Sekarang lebih lanjut kepada perintah dasar 2 mysql
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
asc cocok
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-09 yang 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)
Perintah Alterterdiri dari beberapa bagiandaiantaranya:
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