BAB 1
Tujuan:
1. Mengenal Database dan DBMS
2. Mengenal MySQL
3. Bisa menjalankan MySQL Server dan Client
DATABASE DAN DBMS
Database Management System (DBMS) berisi satu koleksi data yang saling berhubungan dan satu set program untuk mengakses data tersebut. DBMS terdiri dari Database dan Set Program pengelola untuk menambah data, menghapus data, mengambil, dan membaca data.
Database adalah kumpulan file-file yang saling berelasi, relasi tersebut biasa ditunjukan dengan kunci dari tiap file yang ada. Satu database menunjukan satu kumpulan data/tabel yang dipakai dalam satu lingkup perusahaan, instansi. Artinya bahwa basis data adalah kumpulan data yang berhubungan dengan suatu obyek, topik atau tujuan khusus tertentu. Sebagai contoh buku telepon, kamus bahasa, katalog buku di perpustakaan, data koleksi musik dan video, data pelanggan, data supplier, data mahasiswa, data pegawai, dan lain-lain.
Dalam satu file data/tabel terdapat record-record yang sejenis, sama besar, sama bentuk, merupakan satu kumpulan entity yang seragam. Satu record terdiri dari field-field yang saling berhubungan untuk menunjukan bahwa field tersebut dalam satu pengertian yang lengkap dan direkam dalam satu record.
Untuk menyebut isi dari field maka digunakan atribute atau merupakan judul dari satu kelompok entity tertentu, misalnya atribute Alamat menunjukan entity alamat dari siswa. Entity adalah suatu obyek yang nyata dan akan direkam.
Set program pengelola merupakan satu paket program yang dibuat agar memudahkan dan mengefisienkan pemasukan atau perekaman informasi dan pengambilan atau pembacaan informasi kedalam database, biasa disebut dengan paket DBMS. Ada beberapa macam paket DBMS, diantaranya adalah MySQL, postgresql, Foxpro, Oracle, DB2, dll.
MySQL
MySQL adalah salah satu contoh software/paket DBMS yang sangat populer. Kepopuleran MySQL dimungkinkan karena kemudahannya untuk digunakan, cepat secara kinerja query, dan mencukupi untuk kebutuhan database perusahaan-perusahaan skala menengah-kecil.
MySQL merupakan DBMS yang bersifat client-server dan multi user. Maksudnya adalah ada software MySQL yang berjalan sebagai client, dan adapula yang berjalan sebagai server. Server berfungsi untuk memberi layanan atas permintaan yang dilakukan oleh Klien. Ada banyak user yang bisa mengakses database server secara bersamaan melalui program klien.
Oleh karena itu sebelum kita melakukan pengolahan terhadap database, maka kita harus menjalankan program Database Server MySQL terlebih dulu yang menyediakan layanan berupa data. Setelah itu kita baru bisa melakukan permintaan kepada server berupa layanan data melalui program klien. Layanan yang diberikan bisa berupa pemasukan atau perekaman informasi dan pengambilan atau pembacaan informasi ke/dari dalam database.
Menjalankan database server MySQL
di Linux
1. Masuk ke terminal / konsole
caranya:
a. Tekan alt – F2 (alt ditekan dan jangan dilepas kemudian tekan F2)
b. Ketika muncul kotak dialog Run Application, tulis konsole, kemudian klik Ok atau tekan ENTER
2. perintahkan:
/etc/init.d/mysqld start (login sebagai root)
Jika berhasil, maka akan muncul tulisan Ok
di Windows
1. Masuk Command Prompt/Windows Explorer
2. Aktifkan direktori bin dimana mysql diinstal
3. jalankan mysqld.exe
jika wamp berada pada forder C.
- masuk ke-Run, cmd ok
cd\
d:
cd wamp\mysql\bin
mysql –uroot
use mysql;
select * from user;
Untuk mengatur tampilan pada mysql:
-klik kanan pada layar atas
Pilih properties+layout
Semua ukuran diatur 9999+ok
Pilih save properties+ok
Backup
Exit
Mysqldump –uroot perpustakaan_tya > F:\perpustakaan_tya.sql
Source D:\backup\perpustakaan_tya.sql
Pengurutan data :
Tampilkan data buku berdasarkan judul dari abjad A – Z
1. select * from buku order by judul ASC;
2. select * from buku order by DESC;
Group by
Select * from buku group by pengarang;
Ø buku apakah yang diinputkan pada bulan maret
select * from buku where month(tglinput) = ‘03’;
Ø buku apakah yang diinputkan pada hari ini??
Select * from buku where day(tglinput) =’04’ and month(tglnput) =05’ and year (tglinput) = ‘2012’;
Ø menamplkan buku apa saja kah yan diinputkan pada 6 hari yang lalu.
Select judul from buku where tglinput = subdate (curdate(),6);
Ø tambahkan field tgl_lahir pada tabel anggota
alter table anggota add tgl_lahir date after alamat;
Ø update field
update anggota set tgl_lahir =’2012-05-16’ where nim =’100’;
Ø siapakah angota yang berulang tahun 12 hari lagi??
Select * from anggota where tgl_lahir = adddate (curdate(),12);
Ø select * from date_add(curdate(),interval 6 month) from buku;
Ø siapakah anggota yang meminjamkan buku bulan ini??
Select nama from anggota inner join pinjam on anggota.nim = pinjam.nim where month(tgl_pinjam) =’05’;
Ø Buku apakah yang dipinjam pada minggu pertama bulan ini??
Select judul from buku inner join pinjam on buku.id_buku=pinjam.id_buku where month(tgl_pinjam)=’05’ and day(tgl_pinjam) < 8;
Ø Buku apakah yang dipinjam pada minggu ketiga bulan ini??
Select judul from buku inner join pinjam on buku.id_buku=pinjam.id_buku where month(tgl_pinjam)=’08’ and day(tgl_pinjam) >14 and day (tgl_pinjam) < 22;
Ø Siapakah yang meminjam buku dalam 3 minggu yang lalu?
Select nama from anggota inner join pinjam on anggota.nim = pinjam.nim where month(tgl_pinjam)=’05’ and day(tgl_pinjam) > 7 and day(tgl_pinjam) <22;
Select nama from anggota inner join pinjam on anggota.nim=pinjam.nim where SUBDATE(curdate(),21);
Ø Siapakah anggota yang akan berulang tahun pada 5 hari lagi??
Select * from anggota where tgl_lahir = ADDDATE(curdate(),5);
Ø Bagaimana cara mengetahui tanggal lahir anggota termuda?
Select min(tgl_lahir) from anggota;
Ø Bagaimana cara mengurutkan data berdasarkan nama anggota jika diberikan field nama_anggota, tmp_lahir dan tgl_lahir??
Select nama_anggota,tmp_lahir,tgl_lahir from anggota order by nama_anggota
Ø Buku apakah yang dipinjam pada minggu kedua pada u bulan lalu??
Select nama from anggota inner join pinjam on anggota.nim=pinjam.nim where tgl_pinjam=subdate(curdate(),interval 21 day);
Ø Tampilkan judul,penerbit dan pengarang yang diinputkan pada 7 hari yang lalu??
Select judul,penerbit,pengarang from buku where tgl_input=subdate(curdate(),interval 7 day);
Ø Buku apa sajakah yang sedang dipinjam dan belum dikembalikan??
Select judul from buku inner join pinjam on buku.id_buku=pinjam.id_buku where tgl_pinjam
VIEWSyarat dari view adalah query yang sifatnya statis(ribuan).
Koneksi ke MySQL server melalui MySQL klien
Seperti telah disebutkan bahwa MySQL adalah program yang multi user. Oleh karena itu, ketika kita akan melakukan koneksi, maka kita harus mempunyai hak akses terhadap data yang ada di dalam database server dan masing-masing user bisa memiliki password yang berbeda-beda. Ini merupakan salah satu fasilitas keamanan yang biasanya diberikan oleh software DBMS yang multi user. Selain itu juga klien bisa melakukan koneksi dari komputer manapun yang terhubung di dalam jaringan dan memiliki hak akses untuk melakukan koneksi dari komputer lain yang ditentukan. Untuk melakukan koneksi, digunakan program klien yang bernama mysql. Dalam penggunaannya, mysql mempunyai beberapa parameter. Parameter yang sering digunakan untuk melakukan koneksi adalah namauser dan password serta nama host (jika koneksinya melalui komputer lain). Penulisan parameter bisa dibalik-balik antar parameter, bukan antar nilainya.
Caranya:
[user@localhost user]$ mysql -h<nama host> -u<nama user> -p<paswd>
Sebagai contoh user yang bernama 'uun' dengan password 'kbmokbos' akan melakukan koneksi ke database server MySQL, maka yang dilakukan:
[user@localhost user]$ mysql -hserver -uuun -pkbmokbos
Atau supaya passwordnya tidak kelihatan, caranya:
[user@localhost user]$ mysql -uuun -p
Enter password:<masukkan passwordnya di sini dan tidak kelihatan>
Jika koneksi berhasil, maka akan ditampilkan:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 3.23.58
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Ketika sudah terjadi koneksi, maka akan ditampilkan prompt mysql, yang artinya bahwa klien sudah siap untuk meminta request/layanan kepada server, dan server pun sudah siap untuk melayani permintaan dari klien.
Ada beberapa prompt di mysql, dan masing-masing memiliki arti sendiri.
| Prompt | Arti |
| mysql> | Siap untuk perintah baru. |
| -> | Menunggu baris perintah berikutnya. |
| '> | Menunggu baris perintah berikutnya, karena pernyataan yang diawali dengan tanda petik satu(') belum ditutup dengan tanda yang sama. |
| “> | Menunggu baris perintah berikutnya, karena pernyataan yang diawali dengan tanda petik dua(“) belum ditutup dengan tanda yang sama. |
Jika database server belum dijalankan, maka akan ditampilkan pesan berikut:
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Database sudah dijalankan, tapi password yang dimasukkan salah atau user tidak dikenal:
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
atau jika passwordnya tidak diisi:
ERROR 1045: Access denied for user: 'aku@localhost' (Using password: NO)
Perintah Dalam Lingkungan MySQL
Perintah untuk lingkungan MySQL dapat diberikan dengan menggunakan perintah \h pada prompt MySQL. \h atau help adalah perintah untuk menampilkan daftar perintah yang dapat digunakan dalam lingkungan MySQL.
Berikut tampilan yang dihasilkan jika kita memberikan perintah help atau \h dengan diikuti ENTER.
mysql> \h
MySQL commands:
Note that all text commands must be first on line and end with ';'
help (\h) Display this help.
? (\?) Synonym for `help'.
clear (\c) Clear command.
Connect (\r) Reconnect to the server. Optional arguments are db and host.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute a SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
Catatan:
Perintah-perintah dalam MySQL tidak menerapkan aturan case sensitif, perintah bisa dituliskan dalam huruf besar ataupun huruf kecil.
Aturan case sensitif diterapkan pada penamaan objek-objek dalam database atau nama tabel khusus untuk lingkungan *nix (Unix dan Linux).
Contoh, pernyataan berikut adalah sama:
mysql> SELECT USER(), VERSION();
+----------------+-----------+
| USER() | VERSION() |
+----------------+-----------+
| root@localhost | 3.23.58 |
+----------------+-----------+
mysql> select user(), version();
+----------------+-----------+
| user() | version() |
+----------------+-----------+
| root@localhost | 3.23.58 |
+----------------+-----------+
Perintah berikut terdapat kesalahan, karena nama database/table tidak sesuai (harus case sensitif)
mysql> use MySql;
ERROR 1049: Unknown database 'MySql'
mysql> desc HOST;
ERROR 1146: Table 'mysql.HOST' doesn't exist
mysql> select User from uSer;
ERROR 1146: Table 'mysql.uSer' doesn't exist
nama field tidak case sensitif
mysql> Select UsEr,Host,password from user;
+------+-----------------------+------------------+
| UsEr | Host | password |
+------+-----------------------+------------------+
| root | localhost | 47b3026f73b8c496 |
| root | localhost.localdomain | 47b3026f73b8c496 |
| uun | % | 3e4d039e1127f2fb |
+------+-----------------------+------------------+
BAB 2
Tujuan:
1. Mengetahui dan Mengenal Bahasa SQL beserta pembagiannya
2. Menggunakan Bahasa SQL
3. Mengenal dan Menggunakan Sub Bahasa DDL (Data Definition Language)
Mengelola Database
Sebelum kita membuat suatu tabel yang digunakan untuk menyimpan data, maka harus terlebih dahulu dibuat database yang merupakan kumpulan atau berisi tabel-tabel yang saling berhubungan dengan menggunakan kunci-kunci yang ditentukan. Tapi bagaimana caranya memerintahkan mysql untuk membuat database, tabel, dan lainnya yang kita perlukan.
Untuk itu kita perlu untuk mempelajari bahasa gaul yang akan digunakan untuk berkomunikasi dengan MySQL. Bahasa ini disebut dengan SQL (Structured Query Language) dan orang sering mengakronimnya dengan “sequel”. SQL merupakan bahasa standar untuk pengolahan database. Ini berarti bahwa DBMS yang lain juga mengenal bahasa gaul ini. Walau ada beberapa istilah yang di salah satu DBMS tidak dikenal, tapi dikenal di DBMS yang lain.
Di dalam bahasa SQL, perintah dibedakan menjadi 3 sub bahasa:
a. DDL (Data Definition Language)
b. DML (Data Manipulation Language)
c. DCL (Data Control Language)
Data Definition Language
Kelompok perintah ini bisa digunakan untuk melakukan pendefinisian database dan pendefinisian tabel. Sehingga dengan menggunakan perintah-perintah ini, kita bisa memerintahkan untuk membuat database, membuat tabel, mengubah strukturnya, menghapus tabel, membuat index tabel dan lain-lain yang berhubungan dengan pendefinisian database dan tabel.
Membuat Database
Untuk mengetahui atau melihat database yang sudah ada, bisa digunakan perintah:
mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
Dalam pembuatan database perlu perhatikan penulisan nama database tidak boleh menggunakan spasi dan karakter non standar. Bentuk penulisan perintah untuk membuat database baru adalah create database <nama_database>;
mysql> create database perpustakaan;
Untuk memastikan bahwa database yang kita buat sudah jadi, perintahkan show databases, sehingga semua database yang ada di server dan bisa diakse oleh user akan ditampilkan.
mysql> show databases;
+--------------+
| Database |
+--------------+
| myperpus |
| mysql |
| perpustakaan |
| test |
+--------------+
Membuat Tabel
Selanjutnya untuk memulai membuat tabel di dalam database perpustakaan, maka kita harus mengaktifkan terlebih dulu database perpustakaan dengan menggunakan perintah use <nama_database>.
mysql> use perpustakaan;
Setelah database aktif, kita baru bisa memulai untuk membuat tabel yang kita perlukan. Pada database perpustakaan, akan disimpan data-data tentang buku, judul buku, kelompok buku, pengarang, dan penerbitnya. Selain itu juga untuk menyimpan data anggota beserta transaksi yang dilakukan.
Pertama kali, kita akan membuat tabel buku yang fungsinya untuk menyimpan data tentang buku.
mysql> create table buku (
-> id_buku bigint(5) NOT NULL auto_increment primary key,
-> index_buku varchar(15) NOT NULL default '',
-> no_urut tinyint(3) default 0,
-> referensi tinyint(1)) type=myISAM;
Untuk membuktikan bahwa kita berhasil, maka tabel yang ada database bisa kita tampilkan dengan cara:
mysql> show tables;
+------------------------+
| Tables_in_perpustakaan |
+------------------------+
| buku |
+------------------------+
Pada hasil perintah diatas ditampilkan, bahwa tabel yang sudah ada di database perpustakaan adalah buku. Untuk menampilkan struktur dari tabel buku, perintahkan:
mysql> describe buku;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id_buku | bigint(5) | | PRI | NULL | auto_increment |
| index_buku | varchar(15) | | | | |
| no_urut | tinyint(3) | YES | | 0 | |
| referensi | tinyint(1) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
id_buku : menyimpan no_id tidap buku walaupun judulnya sama, sifatnya unik. Dan semua field yang lain bergantung hanya kepada id_buku. Karena itu field ini dijadikan primary key.
index_buku : digunakan untuk menyimpan indeks setiap judul buku yang tiap judul bukunya tersimpan di dalam tabel judul_buku yang akan kita buat berikutnya.
no_urut : digunakan untuk menyimpan no_urut buku no_urut buku untuk judul yang sama.
referensi : digunakan untuk mengetahui apakah buku boleh dipinjam/dibawa keluar atau tidak, jika boleh maka nilainya 0 jika tidak maka nilainya 1. Seperti ini apabila tiap judul buku ada yang boleh dibawa keluar ada yang tidak. Seandainya setiap judul buku yang sama memiliki kondisi referensi yang sama, maka field ini dimasukkan kedalam tabel judul_buku.
Berikutnya kita akan membuat tabel judul_buku.
mysql> create table judul_buku (
-> index_buku varchar(10) NOT NULL primary key,
-> judul varchar(90),
-> kd_pengarang varchar(5),
-> kd_kelompok smallint(3),
-> edisi tinyint(2),
-> thn_terbit int(4))type=myISAM;
index_buku : buku dengan judul, pengarang, penerbit, dan edisi yang sama akan memiliki index yang sama.
judul : judul buku.
kd_pengarang : kode pengarang yang nama pengarangnya ada di tabel pengarang.
kd_kelompok : kode kelompok isi buku sesuai ilmu, yang domainnya dari tabel kelompok.
edisi : cetakan ke berapa untuk judul, penerbit, dan pengarang yang sama.
thn_terbit : tahun terbit buku.
untuk tabel-tabel yang selanjutnya, coba buatlah sendiri dengan struktur sebagai berikut:
bonus: untuk mempermudah pengeditan perintah sql, kita bisa menuliskannya pada salah satu text editor, misalnya kwrite. Setelah disimpan, kemudian kita bisa menjalankannya dengan perintah source <[[/]nama_dir/]nama_file>
Tabel Kelompok
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| kd_kelompok | smallint(3) | | PRI | 0 | |
| kelompok | varchar(20) | | | | |
+-------------+-------------+------+-----+---------+-------+
Tabel Penerbit
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id_penerbit | varchar(5) | | PRI | | |
| penerbit | varchar(20) | YES | | NULL | |
| kota | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
Tabel Pengarang
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id_pengarang | varchar(5) | | PRI | | |
| nama | varchar(30) | | | | |
+--------------+-------------+------+-----+---------+-------+
Tabel Anggota
+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| kd_anggota | varchar(7) | YES | | NULL | |
| nama_anggota | varchar(50) | YES | | NULL | |
| tgllahir | date | YES | | NULL | |
| jklmn | tinyint(1) | YES | | NULL | |
| alamat | varchar(50) | YES | | NULL | |
| no_hp | varchar(12) | YES | | NULL | |
| no_telp | varchar(15) | YES | | NULL | |
| tmp_lahir | varchar(20) | YES | | NULL | |
| Sekolah_Universitas | varchar(30) | | | | |
+---------------------+-------------+------+-----+---------+-------+
Tabel Peminjam
+-------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| id_anggota | varchar(7) | | | | |
| id_buku | varchar(5) | | | | |
| tgl_pinjam | date | YES | | NULL | |
| tgl_kembali | date | YES | | NULL | |
+-------------+------------+------+-----+---------+-------+
Merubah dan Memodifikasi Tabel
Terkadang ketika kita, sedang membuat tabel, ternyata ada nama field yang kurang, atau nama_fieldnya susah dipahami, atau bisa juga type data serta ukurannya tidak/ kurang sesuai. Sekarang coba kita perhatikan tabel yang sudah kita buat sebelumnya.
Pada tabel buku terdapat field id_buku dengan type bigint ukurannya 5, sedangkan pada tabel peminjam id_buku typenya varchar ukurannya 5. Ini tidak sama, bisa-bisa timbul kekacauan ini (emangnya apaan, kok sampai terjadi kekacauan segala) Maksudnya ini harus disamakan supaya kedua tabel bisa dihubungkan. Sekarang, id_buku pada tabel peminjam diubah menjadi bertype bigint(5), caranya:
mysql> alter table peminjam modify id_buku bigint(5);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
Coba sekarang ditampilkan lagi struktur dari tabel peminjam, adakah perubahan? (harus dong... kita harus selalu berubah untuk menuju yang lebih baik...)
+-------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| id_anggota | varchar(7) | | | | |
| id_buku | bigint(5) | YES | | NULL | |
| tgl_pinjam | date | YES | | NULL | |
| tgl_kembali | date | YES | | NULL | |
+-------------+------------+------+-----+---------+-------+
Sekarang kita perhatikan field index_buku pada tabel buku dan pada tabel judul_buku, ukurannya juga berbeda, nah sekarang tugas Anda/Antum semuanya adalah menyamakan dengan ukuran 15 pada tabel judul_buku. Sudah tahu caranya? (masa gitu aja gak tahu, sich)
Merubah Nama Field
Pada tabel buku terdapat field id_buku dan index_buku, kalau melihat namanya, maka seolah-olah fungsinya sama, kenapa ada dua field id(index) buku pada satu tabel. Padahal fungsi kedua field tersebut berbeda. Field index_buku fungsinya untuk menyimpan indeks judul buku sedangkan field id_buku untuk menyimpan indeks untuk setiap buku yang domainnya ada di tabel judul_buku. Judul buku yang sama memiliki index_buku yang sama tapi id_bukunya berbeda. Sebetulnya namanya tetap seperti itu juga tidak ada masalah, tapi akan membingungkan. (bagi orang-orang yang bingung... :) )
Untuk lebih mempermudah pemahaman hanya dengan melihat namanya saja, maka sebaiknya namanya diganti dengan id_judul. Caranya?
mysql> alter table buku change index_buku id_judul varchar(15);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
Sudah tahu tugas berikutnya? Nah... Ubahlah field index_buku yang ada di tabel judul_buku dengan id_judul. Ubah juga field yang berawalan dengan kd dengan id, misalnya kd_penerbit menjadi id_penerbit. Ini hanya untuk melatih cara merubah nama field serta untuk menyeragamkan penamaan saja. Selain itu, ubah juga field sekolah_universitas menjadi sekolah saja. Untuk apa panjang-panjang, universitas juga sekolah kan? Apakah SD itu universitas? Kenapa nggak sekolah_sma, atau sekolah_lpk, atau sekolah_stmik saja, kenapa sekolah_universitas? he... he... he... :)
Menambah Field
Kalau kita perhatikan tabel penerbit, dengan tabel manakah tabel ini berhubungan? Kalau tabel pengarang berhubungan dengan tabel judul_buku menggunakan field kd_pengarang / id_pengarang (jika sudah diganti). Sehingga untuk mengetahui nama pengarang dari suatu judul buku tinggal menghubungkan tabel judul_buku dengan pengarang menggunakan field kunci id_pengarang. Harusnya tabel penerbit diperlukan juga oleh tabel judul_buku untuk mengetahui nama penerbit dari suatu judul buku. Tapi jangan khawatir, SQL punya juga bahasa yang digunakan untuk menambah/menyisipkan field kedalam suatu tabel.
mysql> alter table judul_buku add id_penerbit varchar(5) after judul;
Perintah di atas digunakan untuk menyisipkan field id_penerbit ke dalam tabel judul_buku diletakkan setelah field judul. Untuk membuktikan, tampilkan saja struktur tabel judul_buku.
Menghapus Field
Di dalam tabel anggota, kita bisa melihat di sana terdapat field no_hp dan no_telp. Perlukah kedua-duanya disimpan? Tidak semua anggota punya no. hp, dan tidak pula semuanya memiliki no. telpon. (macam mana pula ini, kok disimpan semuanya? ini pemborosan namanya. instruksi presiden kan disuruh hemat energi. Lho... kok sampai bbm?) Untuk menghapus field no_hp gunakan perintah berikut:
mysql> alter table anggota drop no_hp;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
Merubah Nama Tabel
Tabel peminjam berfungsi untuk menyimpan data anggota yang melakukan peminjaman dan pengembalian buku. Mungkin akan lebih jelasnya kalau nama peminjam diganti dengan transaksi. Untuk melakukannya gunakan perintah :
mysql> alter table peminjam rename transaksi;
Menghapus Table dan Database
Terkadang kita sudah membuat suatu tabel atau database, tapi ternyata tabel tersebut sebetulnya tidak diperlukan. Mungkin karena pembatasan masalah, ataupun mungkin karena diketahui setelahnya bahwa tabel tersebut tidaklah diperlukan, dan hanya memboroskan tempat penyimpanan saja, atau dengan tabel tersebut menjadikan tidak efektif dan efisien. Atau bisa saja tabel tersebut diciptakan hanya untuk keperluan sementara saja(temporari), maka kita harus atau perlu untuk menghapus tabel tersebut. Begitu juga dengan database yang sudah kita buat ,ternyata database tersebut sudah kita perlukan lagi. Perintah yang digunakan untuk menghapus adalah DROP.
Menghapus Tabel
struktur perintah:
drop table <nama_table>
contoh:
mysql> drop table pengarang;
Menghapus Database
struktur perintah:
drop database <nama_table>
contoh:
mysql> drop database pengarang;
Tampilkan semua tabel yang ada di dalam database, atau tampilkan semua database yang ada di dalam server, masih adakah tabel atau database yang diberlakukan padanya perintah drop? Jika perintah yang dilakukan sesuai dengan aturannya (syariat) tanpa mengurangi ataupun menambahnya (dengan kebid'ahan-kebid'ahan), pasti akan mendapatkan hasilnya sesuai yang sudah disebutkan (dijanjikan, kecuali jika ada kesalahan penulisan maka diluar tanggung jawab percetakan. lho...?).
BAB 3
Tujuan:
1. Mengenal Sub Bahasa DML (Data Manipulation Language)
2. Menggunakan Sub Bahasa DML
3. Mengenal Sub Bahasa DCL (Data Control Language)
Data Manipulation Language
DML adalah kelompok perintah yang berfungsi untuk memanipulasi data dalam basis data, misalnya untuk pengambilan, penyisipan, pengubahan, dan penghapusan data. Perintah yang masuk kategori DML adalah :
1. Insert
Perintah Insert digunakan untuk menyisipkan data baru ke dalam sebuah tabel database. Ada dua macam perintah Insert, yaitu yang digunakan untuk menyisipkan data satu persatu dan yang menyisipkan banyak data sekaligus. Sintaksnya adalah sebagai berikut :
Untuk menyisipkan banyak record :
Untuk menyisipkan satu record :
INSERT INTO tujuan [(t_field1[, t_field2[, …]])] VALUES (nilai1[, nilai2[, … ] ])
Arti parameter-parameter yang disertakan:
Tujuan adalah nama tabel yang akan disisipi data.
Sumber adalah nama tabel sumber data.
T_field1, t_field2 adalah nama field yang akan disisipi data.
s_field1, s_field2 adalah nama field sumber data.
Nilai1, nilai2 adalah nilai data yang akan disisipkan.
Sebagai latihan, akan ditambahkan kedalam tabel buku beberapa data.
mysql> insert into buku values(1,'KD-OD9-HW-EMK-1',1,1);
Perintah di atas bertujuan menambahkan/menyisipkan tabel kedalam tabel buku dengan data sesuai urutan field ketika membuat tabel (bisa ditampilkan dengan perintah describe).
Bisa juga menggunakan cara:
mysql> INSERT INTO buku(id_buku,id_judul,no_urut,referensi)
-> VALUES (2,'AF-HMA-MA-MDK-5',1,1);
Ataupun
mysql> INSERT INTO buku (id_judul,referensi, no_urut)
-> VALUES ('KP-PDM-RS-AOF,3',1,1);
Tampilkan semua data yang ada di dalam tabel buku
mysql> select * from buku;
+---------+-----------------+---------+-----------+
| id_buku | id_judul | no_urut | referensi |
+---------+-----------------+---------+-----------+
| 1 | KD-OD9-HW-EMK-1 | 1 | 1 |
| 2 | AF-HMA-MA-MDK-5 | 1 | 1 |
| 3 | KP-PDM-RS-AOF,3 | 1 | 1 |
+---------+-----------------+---------+-----------+
mysql> INSERT INTO buku
-> SET id_judul='KP-PWP-IFB-4',
-> no_urut=1,
-> referensi=1;
mysql> INSERT INTO buku
-> VALUES
-> (5,'KP-PWP-BS-IFB-4',2,1),
-> (6,'KP-PWP-BS-IFP-4',3,2),
-> (7,'KP-PWP-BS-IFP-4',4,2);
mysql> INSERT INTO buku (no_urut,referensi,id_judul)
-> VALUES
-> (1,1,'KD-MDS-FU-AOF-3'),
-> (2,2,'KD-MDS-FU-AOF-3'),
-> (3,1,'KD-MDS-FU-AOF-3'),
-> (4,1,'KD-MDS-FU-AOF-3'),
-> (5,2,'KD-MDS-FU-AOF-3');
mysql> INSERT INTO buku values(NULL,'AN-NIQ-IQ-TBY-7',1,2);
mysql> INSERT INTO buku values('','AN-NIQ-IQ-TBY-7',2,2),('','KP-PAT-LP-AOF-6',1,2),('','KP-PWH-BS-IFB-8',1,1);
Tampilkan semua data yang ada di tabel buku. Dan buatlah kesimpulan dari perintah-perintah yang ada di atas.
Mengimport Tabel
MySQL mempunyai fasilitas yang dapat kita gunakan untuk mengimport data tabel dari file lain dengan format teks.
Kita lanjutkan dengan tabel yang lain dengan cara yang lain lagi. Untuk tabel judul buku, buatlah file teks (bisa menggunakan kwrite di linux, atau kalau di windows bisa pakai notepad atau editor teks yang lain) data berikut:
| KD-OD9-HW-EMK-1 | Oracle 9i Database | EMK | HENWI | 211 | 2 | 2003 |
| AF-HMA-MA-MDK-5 | Hukum membaca Al Quran untuk orang Mati | MDK | MUHAB | 111 | 6 | 2004 |
| KP-PDM-RS-AOF-3 | Pemrograman Database MySQL 4 dg Bahasa C | AOF | RIDSJ | 212 | 1 | 2004 |
| KP-PWP-BS-IFB-4 | Pemrograman Web dengan PHP | IFB | BETSI | 213 | 2 | 2002 |
| KD-MDS-FU-AOF-3 | Mengelola Database Server di Linux dan Windows | AOF | FIRUT | 211 | 1 | 2002 |
| AN-NIQ-IQ-TBY-7 | Nasehat Ibnul Qayyim Kepada Setiap Muslim | TBY | IBNQA | 112 | 3 | 2001 |
| KP-PAT-LP-AOF-6 | Program Akutansi Terpadu | AOF | LIMPH | 214 | 1 | 2004 |
Antar field/kolom dipisah dengan koma, dan antar record/baris dipisah dengan enter. Sebagai contoh:
AN-NIQ-IQ-TBY-7,Nasehat Ibnul Qayyim Kepada Setiap Muslim,TBY,IQ1,112,3,2001
AF-HMA-MA-MDK-5,Hukum membaca Al Quran untuk orang Mati,MDK,MA1,111,6,2004
Setelah file dibuat, simpanlah di tempat yang jelas (sebagai contoh di /home/uunboy/ dengan nama ). Selanjutnya jalankan perintah:
load data infile "/home/uunboy/buku" into table judul_buku fields terminated by ",";
Isikan untuk tabel-tabel yang lain seperti pada tabel berikut:
Tabel Penerbit
+-------------+----------------------+------------+
| id_penerbit | penerbit | kota |
+-------------+----------------------+------------+
| MDK | Media Dakwah | Jakarta |
| EMK | Elex Media Komputind | Jakarta |
| AOF | Andi Offset | Yogyakarta |
| IFB | Informatika Bandung | Bandung |
| MDH | Media Hidayah | Surakarta |
| TBY | Pustaka At Tibyan | Surakarta |
| MKD | Menara Kudus | Yogyakarta |
+-------------+----------------------+------------+
Tabel Pengarang
+--------------+----------------------------+
| id_pengarang | nama |
+--------------+----------------------------+
| HENWI | Ir. Hendra Wijaya |
| MUHAB | Muhammad Ahmad Abdul Salam |
| RIDSJ | Ridwan Sanjaya S.Kom |
| BETSI | Betha Sidik |
| FIRUT | Firrar Utdirartatmo ST MT |
| IBNQA | Ibnul Qayyim Aljauziah |
| LIMPH | Lim Phek Yiong |
| ABDKD | Abdul Kadir |
| JOGIY | Jogiyanto |
+--------------+----------------------------+
Tabel Kelompok
+-------------+----------------------+
| kd_kelompok | kelompok |
+-------------+----------------------+
| 110 | Aqidah |
| 111 | Fikih |
| 112 | Muamalah |
| 113 | Ibadah |
| 211 | Komputer Database |
| 212 | Pemrograman Database |
| 213 | Pemrograman Web |
| 214 | Pemrograman Akutansi |
+-------------+----------------------+
Buatlah file teks dengan data sebagai berikut:
INSERT INTO anggota VALUES ('0540001','Farmadi','1981-12-15',2,'Karangkajen MG III / 890 55153 Yk','','Yogyakarta','STMIK El Rahma');
INSERT INTO anggota VALUES ('0520002','Ana','1990-01-15',2,'Karangkajen MG III / 847 55153 Yk','','Yogyakarta','SMP 16 Yk');
INSERT INTO anggota VALUES ('0540003','Irma M','1984-12-22',2,'Karangkajen MG III / 915 55153 Yk','(0274)448549','Yogyakarta','UNY');
INSERT INTO anggota VALUES ('0540004','Untung Subagyo Al-Kabumaeni','1982-06-03',1,'Jl. Sisingamangaraja 76 Yogyakarta','(0274)377982','Kebumen','UGM');
INSERT INTO anggota VALUES ('0530005','Tashya','1988-10-04',2,'Karangkajen MG III / 935 55153 Yk','08164896920','Yogyakarta','MoEHa Punya');
INSERT INTO anggota VALUES ('0540006','Dyaning Utami Putri','1984-06-27',2,'Karangkajen Jl. Menukan MG III / 235 55153 Yk','','Yogyakarta','UST');
INSERT INTO anggota VALUES ('0530007','Agatha kenang M','1991-03-30',2,'Karangkajen MG III 55153 Yk','08164896920','Yogyakarta','SMPN 2 Yk');
INSERT INTO anggota VALUES ('0540008','Ririn Rikhul J','1986-03-05',2,'Karangkajen MG III / 983 55153 Yk','(0274)382031','Yogyakarta','UAD');
INSERT INTO anggota VALUES ('0540009','Nurul Madaniyah','1987-08-28',2,'Karangkajen 70','08562951646','Yogyakarta','UAD');
INSERT INTO anggota VALUES ('0540010','Dyah Kurniawati','1983-10-18',2,'Karangkajen MG III / 812 55153 Yk','(0274)379539','Yogyakarta','UWM');
INSERT INTO anggota VALUES ('0520011','Adelia Rosharyati','1990-12-30',2,'Karangkajen MG III / 965 55153 Yk','(0274)372666','Yogyakarta','SMPN 10 Yk');
INSERT INTO anggota VALUES ('0540012','Nur Rahmania Fitriastuti','1985-06-27',2,'Karangkajen MG III / 817 55153 Yk','0274-7493752','Yogyakarta','UIN Sunan Kalijaga');
INSERT INTO anggota VALUES ('0540013','Vina Agustina','1985-08-09',2,'Karangkajen MG III / 980 B 55153 Yk','085228362358','Kudus','STMIK El Rahma');
INSERT INTO anggota VALUES ('0520014','Yossi Dwiyuanna Septiani','1991-09-09',2,'Karangkajen MG III / 873 55153 Yk','085643148538','Yogyakarta','MTsN 2 Yk');
INSERT INTO anggota VALUES ('0540015','Dwian Soffa Ardafit','1986-05-09',2,'Karangkajen MG III / 966 55153 Yk','085228134770','Cilacap','STMIK El Rahma');
INSERT INTO anggota VALUES ('0540016','Siti Marfu\'ah','1986-06-07',2,'Karangkajen MG III / 927 55153 Yk','08882759801','Ngawi','STMIK El Rahma');
INSERT INTO anggota VALUES ('0540017','Noor Hidayah','1986-05-17',2,'Karangkajen MG III / 980 B 55153 Yk','085225110799','Kudus','STMIK El Rahma');
INSERT INTO anggota VALUES ('0510018','Afifah Yuniar Al-Buny','1995-06-08',2,'Karangkajen MG III / 857 55153 Yk','','Yogyakarta','SD Muhamadiyah');
INSERT INTO anggota VALUES ('0520019','Ulfias Trinta Noor Andira','1992-07-16',2,'Karangkajen MG III / 847 55153 Yk','','Yogyakarta','SMP 16 Yk');
INSERT INTO anggota VALUES ('0540022','Munawir','1986-01-15',1,'Karangkajen MG III / 840 55153 Yk','(0274)448554','Wakai','UGM');
Kemudian Jalankan file tersebut untuk mengisi data pada tabel anggota.
- Select
Perintah Select digunakan untuk memilih data-data yang diperlukan dari sebuah database ke dalam sebuah database sementara. Ada beberapa istilah dalam menyebut database sementara ini, misalnya query, recordset, view. Sintaks selengkapnya adalah sebagai berikut:
SELECT { * | namafield } FROM namatabel [INTO tujuan] [WHERE kriteria] [GROUP BY grup] [HAVING kriteria] [ORDER BY namafield] [{ASC|DESC}]
Tanda * berarti menetapkan bahwa semua field pada tabel akan dipilih.
NamaField adalah nama field yang akan dipilih.
NamaTabel adalah nama tabel yang akan dipilih.
Kriteria merupakan suatu ekspresi logika yang mjd syarat pemilihan data.
Tujuan merupakan nama dari tabel baru yang dibentuk.
Grup merupakan pengelompokan data berdasarkan nama field.
ASC atau DESC adalah pengurutan data berdasarkan abjad atau angka.
Jalankan perintah-perintah berikut:
select * from buku;
select * from buku where referensi=2;
select id_judul,judul,id_penerbit,thn_terbit from judul_buku;
select nama_anggota,tmp_lahir,sekolah from anggota
select nama_anggota,tmp_lahir from anggota where sekolah='STMIK El Rahma';
select nama_anggota,tmp_lahir from anggota where tmp_lahir='Yogyakarta';
select nama_anggota,tmp_lahir from anggota where nama_anggota like 'Untung%';
select nama_anggota,tmp_lahir from anggota where nama_anggota like '%ma%';
- Delete
Perintah Delete digunakan untuk menghapus record pada suatu tabel. Sintaks dari perintah Delete adalah sebagai berikut :
DELETE FROM nama_tabel [WHERE kriteria]
hati-hati dalam memberikan perintah delete ini, karena jika terlupa memberikan pernyataan kondisi , perintah ini akan menyebabkan terhapusnya seluruh isi tabel.
Sebagai contoh kita hapus dari tabel anggota, anggota dengan id_anggota='0520002', maka perintahnya:
DELETE FROM anggota WHERE id_anggota='0520002'
- Update
Perintah Update digunakan untuk memodifikasi atau memperbaharui nilai-nilai record pada sebuah tabel. Sintaksnya adalah sebagai berikut :
UPDATE nama_tabel
SET field1 = nilai1 [,field2 = nilai2 [,...]]
[WHERE kondisi]
wah..., ternyata data anggota yang kita masukan tadi ada yang salah. Coba tampilkan nama_anggota, beserta sekolahnya, kalau pingin tahu, boleh juga dengan tgl_lahirnya :), untuk anggota yang tempat lahirnya di Kebumen. (masih ingat caranya? Tentu dong.....).
Nah, ternyata kita menginputkan sekolah yang salah. Coba jalankan perintah berikut:
UPDATE anggota SET sekolah='STMIK El Rahma' WHERE tmp_lahir LIKE '%bumen';
Hati-hati juga dalam menjalankan perintah update ini, jangan sampai lupa untuk menuliskan kondisi data dalam tabel yang akan diperbaiki. Maksud hati ingin merubah data yang tempat lahirnya di 'bumen' saja, apa daya karena lupa menuliskan kondisi, jadi berubah semua datanya.
Jika ingin menampilkan huruf yang ada d’depan kata
UPDATE [nama table] SET field1 = ‘nama yang diganti’ WHERE field2 LIKE ‘nama yang sudah diganti%’;
c. Data Control Language
DCL berisi perintah-perintah untuk mengendalikan pengaksesan data. Pengendalian dapat dilakukan berdasar per pengguna , per tabel, per kolom, maupun per operasi yang boleh dilakukan.
DCL terdiri atas sekelompok perintah SQL untuk memberikan hak akses database, mangalokasikan space, pendefinisian space, , dan penggunaan database. Beberapa perintah ini merupakan fungsi dari Database Administrator.
Perintah-perintah yang termasuk dalam DCL adalah :
GRANT : Memberikan kendali pengaksesan data
REVOKE : Mencabut kemampuan pengaksesan data
Selain itu ada juga COMMIT dan ROLLBACK. Detail mengenai perintah-perintah ini akan kita pelajari pada bab mengenai pengaturan hak akses dan user.
contoh soal :
buku apakah yang diinputkan pada bulan mei
select * from buku where month(tgl_input)= ‘5’;
buku apakah yang diinputkan pada bulan maret tanggal 12?
Select * from buku where month(tgl_input)=’3’ and day(tgl_input)=’12’;
Buku apakah yang diinputkan pada minggu kedua bulan maret
Select * from buku where month(tgl_input)=’3’ and day(tgl_input) > 7 and day(tgl_input) < 15;
BAB 4
Tujuan:
1. Menggunakan Bahasa SQL dengan dua tabel atau lebih
Menggabungkan Dua Tabel atau Lebih
Jika kita memperhatikan tabel buku, maka disitu hanya ada field id_buku, id_judul, no_urut, dan referensi. Bagaimana untuk mengetahui judul bukunya? Sedangkan judul ada di tabel yang lain. Terkadang atau bahkan sering, kita memang akan bekerja dengan dua tabel atau lebih. Kalau kita menggabungkan dua tabel atau lebih, seperti permasalahan di atas hanya menyebut field beserta tabel-tabel yang digunakan, misalnya perintah berikut:
select id_judul,judul,no_urut from buku,judul_buku;
maka akan ditampilkan kesalahan
ERROR 1052: Column: 'id_judul' in field list is ambiguous
Jika kebetulan nama field id-nya berbeda, atau perintah yang digunakan adalah:
select buku.id_judul,judul,no_urut from buku,judul_buku;
maka akan ditampilkan data sebanyak jumlah data buku * jumlah data judul buku, yaitu dengan menghubungkan tiap id_judul di tabel buku dengan judul yang ada di tabel judul_buku.
Jika yang diinginkan adalah data yang bertepatan, yaitu data id_judul yang ada di tabel buku dihubungkan dengan id_judul yang sama yang ada di tabel judul buku, maka perintahnya:
SELECT buku.id_judul,judul,no_urut FROM buku,judul_buku
-> WHERE buku.id_judul=judul_buku.id_judul;
atau
SELECT buku.id_judul,judul,no_urut
-> FROM buku INNER JOIN judul_buku
-> ON buku.id_judul=judul_buku.id_judul;
Menampilkan data judul, nama penerbit dan nama pengarangnya
SELECT judul, penerbit, nama
FROM judul_buku
INNER JOIN penerbit ON judul_buku.id_penerbit = penerbit.id_penerbit
INNER JOIN pengarang ON judul_buku.kd_pengarang = pengarang.id_pengarang;
SELECT judul, penerbit
FROM judul_buku
INNER JOIN penerbit ON judul_buku.id_penerbit = penerbit.id_penerbit;
SELECT judul, penerbit
FROM judul_buku
LEFT JOIN penerbit ON judul_buku.id_penerbit = penerbit.id_penerbit;
SELECT judul, penerbit
FROM judul_buku
RIGHT JOIN penerbit ON judul_buku.id_penerbit = penerbit.id_penerbit;
Perhatikan perbedaan ketiga perintah terakhir di atas beserta hasilnya, dan simpulkan.
Cobalah untuk menghapus data penerbit 'Andi offset' yang ada di tabel penerbit
Kemudian ulangi ketiga perintah di atas
Perhatikan perbedaan perubahan hasil ketiga perintah terakhir di atas beserta hasilnya, dan simpulkan.
BAB 5
Tujuan:
1. Menggunakan Bahasa SQL dengan dua tabel atau lebih
2. Mengenal dan Menggunakan Fungsi Agregat
3. Mengelompokan dan Mengurutkan Tabel
4. Membatasi hasil Query
Fungsi Agregat
Fungsi agregat merupakan fungsi yang disediakan oleh SQL untuk melakukan ringkasan data, bukan menampilkan data per record.
a. sum(nama_field)
Fungsi sum() digunakan untuk melakukan penjumlahan isi field yang bertipe numerik yang namanya disebutkan pada namafield yang dijadikan parameter pada fungsi sum().
select sum(thn_terbit) from judul_buku;
+-----------------+
| sum(thn_terbit) |
+-----------------+
| 14020 |
+-----------------+
Perintah diatas digunakan untuk menampilkan jumlah dari tahun terbit yang ada pad tabel judul_buku. (masa..., tahun kok dijumlahkan??? ya... itu merupakan contoh perintah yang benar, jika dijalankan tidak muncul peringatan salah. Tapi merupakan perintah yang salah penggunaannya. Sekali lagi ini hanya contoh saja. Tetapi ini jangan diamalkan dalam kehidupan nyata. Masih banyak amalan yang lain, yang amalan itu merupakan amalan yang sunnah atau baik, tapi penempatannya tidak benar. Kita harus hati-hati dengan hal ini... ????? :)).
b. count(nama_field)
Fungsi ini dimaksudkan untuk mengetahui jumlah record dari suatu tabel, berdasarkan kondisi yang disertakan. Jika kondisi tidak ditulis, maka akan ditampilkan jumlah (semua) record dari tabel. Parameter yang disertakan bisa berupa nama field boleh juga diganti dengan tanda *.
Untuk mengetahui jumlah anggota yang terdaftar, maka kita bisamenggunakan perintah berikut ini:
select count(*) from anggota;
c. avg(nama_field)
Fungsi ini dipakai untuk memperoleh nilai rata-rata suatu field yang bertipe numerik yang nama fieldnya disebutkan sebagai parameter.
d. max(nama_field)
Dengan menggunakan fungsi max, kita akan mendapatkan nilai terbesar dari field yang bertipe numerik, yang nama fieldnya dituliskan dalam parameter. Kebalikan dari fungsi ini adalah min(nama_field).
Kalau kita ingin mengetahui tanggal lahir anggota yang termuda, maka kita bisa menggunakan perintah:
select max(tgllahir) from anggota;
Sedangkan tgllahir anggota yang tertua, perintahnya:
select min(tgllahir) from anggota;
Kalau hanya seperti itu..., hanya muncul tanggalnya saja, kalau pingin tahu siapa namanya gimana? Sabar dulu... itu nanti... Bukankah Nabi Yakub telah bersabar dalam menghadapi putra-putranya? Bukankah Nabi Yusuf pun telah bersabar untuk menghadapai berbagai cobaan? Mulai dari perlakuan saudara-saudaranya, cobaan dengan seorang wanita, dan cobaan akan kekuasaan. Maaf, ini hanya selingan saja....
Mengelompokan dan Mengurutkan Data
a. Group By
Untuk mengelompokkan data, kita bisa menggunakan perintah group by. Misalnya kita pingin mengetahui anggota dari perpustakaan ini lahirnya dimana saja sih? Maka kita bisa menggunakan perintah:
select tmp_lahir from anggota;
tapi semua kota tampil dan ada yang berulang. Nah untuk mengatasinya, perintahkan:
select tmp_lahir from anggota group by tmp_lahir;
Sekarang akan ditampilkan Jumlah anggota dari tiap-tiap sekolah.
select sekolah,count(*) from anggota group by sekolah;
+--------------------+----------+
| sekolah | count(*) |
+--------------------+----------+
| MoEHa Punya | 1 |
| MTsN 2 Yk | 1 |
| SD Muhamadiyah | 1 |
| SMP 16 Yk | 1 |
| SMPN 10 Yk | 1 |
| SMPN 2 Yk | 1 |
| STMIK El Rahma | 6 |
| UAD | 2 |
| UGM | 1 |
| UIN Sunan Kalijaga | 1 |
| UNY | 1 |
| UST | 1 |
| UWM | 1 |
+--------------------+----------+
Tampilkan jumlah anggota dikelompokkan berdasarkan tempat lahirnya.
Mengurutkan Data
Order By adalah solusi yang bisa kita gunakan untuk mengurutkan data.
Misalkan kita akan menampilkan data anggota urut berdasarkan nama:
select nama_anggota,tmp_lahir,tgllahir from anggota order by nama_anggota;
Untuk mengurutkan berdasarkan tanggal lahir, caranya:
select nama_anggota,tmp_lahir,tgllahir from anggota order by tgllahir;
Mengurutkan data anggota berdasarkan dua kriteria, tmp_lahir dan nama
select nama_anggota,tmp_lahir from anggota order by tmp_lahir, nama_anggota;
Untuk mengurutkan secara turun(descending) defaultnya urut naik (ascending).
select nama_anggota,tmp_lahir,tgllahir from anggota order by tgllahir desc;
Menggabungkan Antara Fungsi Agregat, Pengelompokan dan Pengurutan
Menampilkan data jumlah anggota tiap-tiap kota tempat lahir urut dari jumlah terkecil sampai terbesar dan urut berdasarkan nama kota tempat lahir:
select tmp_lahir,count(nama_anggota) as jumlah from anggota group by tmp_lahir order by jumlah,tmp_lahir;
+------------+--------+
| tmp_lahir | jumlah |
+------------+--------+
| Cilacap | 1 |
| Kebumen | 1 |
| Ngawi | 1 |
| Wakai | 1 |
| Kudus | 2 |
| Yogyakarta | 13 |
+------------+--------+
Menambahkan parameter limit
Menampilkan 5 data pertama
select kd_anggota,nama_anggota from anggota limit 5;
Menampilkan data mulai data ke 12 (setelah data ke 11) sampai data ke 17 (sebanyak 5 data)
select kd_anggota,nama_anggota from anggota limit 11,5;
Coba jalankan perintah berikut:
select nama_anggota,tgllahir from anggota order by tgllahir limit 1;
select nama_anggota,tgllahir from anggota order by tgllahir desc limit 1;
(Adakah suatu permasalahan yang sudah terselesaikan?)
BAB 6
Tujuan:
1. Menggunakan Program Klien dari Prompt Sistem Operasi
2. Mengenal dan Menggunakan Fungsi di dalam MySQL
Program klien MySQL dari prompt Sistem Operasi
Keluarlah dari mysql, dan masuklah ke konsole. Kemudian jalankan perintah-perintah berikut, dan buat kesimpulannya:
mysql -e "select * from pengarang" perpustakaan -uroot -p
mysql -e "select * from judul_buku" perpustakaan -uroot -p
mysql -e "delete from judul_buku" perpustakaan -uroot -p
mysql -e "load data infile '/home/uunboy/judul' into table judul_buku fields terminated by ','" perpustakaan -uroot -pbismillah
mysqlshow -uroot -p
mysqlshow perpustakaan -uroot -p
mysqlshow perpustakaan anggota -uroot -p
Fungsi
Fungsi adalah suatu rutin khusus yang disediakan oleh MySQL untuk melakukan manipulasi suatu data.
Fungsi digunakan sebagai bagian dari suatu perintah SQL (select). Secara umum fungsi dinyatakan dalam sQL select adalah sebagai berikut:
SELECT fungsi(ekspresi) [FROM namatabel];
Penulisan fungsi dalam MySQL adalah case insensitive.
Fungsi dalam MySQL dibedakan menjadi:
Fungsi Sistem
Kelompok ini digunakan untuk memberikan informasi tentang pemakaian server database oleh pemakai.
DATABASE()
LAST_INSERT_ID()
SESSION_USER()
SISTEM_USER()
USER()
VERSION()
BENCHMARK(x,ekspresi)
LOAD_FILE(namafile)
Fungsi Agregat
Fungsi Agregat digunakan untuk melakukan summary, merupakan fungsi statistik standar yang dikenakan pada suatu tabel atau query.
AVG(ekspresi)
COUNT(x)
MAX(ekspresi)
MIN(ekspresi)
MAX(ekspresi)
STD(ekspresi)
STDEV(ekspresi)
SUM(ekspresi)
Fungsi Aritmatika
Fungsi Aritmatika dipakai untuk melakukan manipulasi data numerik, seperti penjumlahan(+), pengurangan(-), perkalian(*), dan pembagian(/), sisa hasil bagi(%) yang disertakan dalam perintah select, atau biasa dikenal dengan istilah operator aritmatika.
Selain operator-operator di atas, ada juga fungsi aritmatika yang lain, yang dapat digunakan untuk kebutuhan yang lebih kompleks dalam memanipulasi data secara aritmatik.
ABS(x)
ACOS(x)
ASIN(x)
ATAN(x)
ATAN2(x)
BIN(x)
CEILING(x)
CONV(x,daribasis, kebasis)
COS(x)
COT(x)
DEGREES(x)
EXP(x)
FLOOR(x)
FORMAT(num,dec)
GREATEST(x,y,...)
HEX(x)
LEAST(x,y,z,...)
LOG(x)
LOG10(x)
MOD(x,y)
OCT(x)
PI()
POW(x,y)
POWER(x,y)
RADIANS(x)
RAND()
ROUND(x)
ROUND(x,y)
SIGN(x)
SIN(x)
SQRT(x)
TAN(x)
TRUNCATE(x,y)
Fungsi String
Fungsi string digunakan untuk melakukan manipulasi data teks(string). MySQL menyediakan banyak fungsi built in untuk melakukan manipulasi teks ini.
ASCII(x)
CHAR(x,y,z,...)
COALESCE(ekspresi1,ekspresi2,...)
CONCAT(x,y,z,...)
DECODE(string_biner,string_enkripsi)
ELT(x,a,b,c,...)
ENCODE(word,string_enkripsi)
ENCRYPT(word[,seed])
FIELD(x,y,z,...)
FIND_IN_SET(x,stringlist)
INSERT(x,y,z,j)
INSTR(x,y)
LCASE(x)
LEFT(x,y)
LENGTH(x)
LOAD_FILE(namafile)
LOCATE(x,y,z)
LOWER(x)
LPAD(x,y,z)
LTRIM(x)
MID(x,y,z)
PASSWORD(password)
POSITION(x,y)
REPEAT(x,y)
REPLACE(x,y,z)
REVERSE(x)
RIGHT(string,length)
RPAD(x,y,z)
RTRIM(x,y,z)
SOUNDEX(x)
SPACE(x)
STRCMP(x)
STRCMP(string1,string2)
SUBSTRING_INDEX(x,y,z)
TRIM([[BOTH OR LEADING or TRAILING][x] FROM] y)
UCASE(x)
UPPER(x)
Fungsi Tanggal
tanggal dalam MySQL menggunakan tanggal dari sistem UNIX, tidak ada masalah sampai dengan tahun 2069. Semua tahun yang ditulis dengan dua digit diasumsikan tahun tersebut ada pada range tanggal antara tahun 1970 sampai 2069.
Fungsi-fungsi built-in untuk data tanggal dalam MySQL:
ADDDATE(date, INTERVAL ekspresi_interval)
CURDATE()
CURRENT_DATE()
CURTIME()
CURRENT_TIME()
DATE_ADD(date, INTERVAL ekspresi_interval)
DATE_FORMAT(date,simbolformat)
DATE_SUB(date, INTERVAL ekspresi_interval)
DAYNAME(date)
DAYOFMONTH(date)
DAYOFWEEK(date)
DAYOFYEAR(date)
EXTRACT(value FROM date)
FROM_DAYS(days)
FROM_UNIXTIME(unixtime[,simbolformat])
HOUR(time)
MINUTE(time)
MONTH(date)
MONTHNAME(date)
NOW()
PERIOD_ADD(x,y)
PERIOD_DIFF(x,y)
QUARTER(date)
SECOND(time)
SEC_TO_TIME(second)
SUBDATE()
SYSDATE()
TIME_FORMAT(time, simbolformat)
TIME_TO_SEC(time)
TO_DAYS(date)
UNIX_TIMESTAMP([date])
WEEK(date[,start])
WEEKDAY(date)
YEAR(date)
Fungsi Logika
Fungsi logika digunakan untuk melakukan evaluasi suatu ekspresi.
IF(ekspresi1,ekspresi2,ekspresi3)
IFNULL(ekspresi1,ekspresi2)
ISNULL(ekspresi)
BAB 7
Tujuan:
1. Mengadministrasi Database MySQL
2. Mengatur Hak Akses User
Administrasi Database MySQL
Data organisasi atau perusahaan sebaiknya tidak bisa dilihat oleh semua anggota, user atau pegawai, hanya orang tertentu saja yang diberi hak untuk mengetahui dan menggunakan untuk kepentingan analisis dan pengambilan keputusan.
Privilege adalah hak dan wewenang yang diberikan kepada pemakai yang telah didefinisikan untuk dapat mengakses dan mengelola database MySQL. Dalam bahasa yang lebih sederhana adalah hak akses atau permission.
Superuser database MySQL adalah penanggung jawab dan pengelola database MySQL dengan level tertinggi, superuser akan menentukan privilege dari setiap pemakai. Privilege mengatur hak mengakses database, data mana saja yang boleh diakses, dari mana saja bisa mengaksesnya.
Dalam istilah database secara umum superuser database sering disebut dengan administrator database atau system manager, tergantung pada database yang digunakan. Dalam MySQL superuser bernama root.
MySQL menggunakan tabel-tabel khusus untuk melakukan pengelolaan pemakai dari privilegenya yang tersimpan di dalam database dengan nama mysql.
use mysql;
show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-----------------+
Tabel columns_priv
Tabel ini digunakan untuk menyimpan informasi privilege pemakai sampai dengan level field.
mysql> desc columns_priv;
+-------------+----------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Table_name | char(64) binary | | PRI | | |
| Column_name | char(64) binary | | PRI | | |
| Timestamp | timestamp(14) | YES | | NULL | |
| Column_priv | set('Select','Insert','Update','References') | | | | |
+-------------+----------------------------------------------+------+-----+---------+-------+
Tabel db
Berisi informasi database mana saja yang bisa diakses oleh setiap pemakai, dan dari komputer mana.
mysql> desc db;
+-----------------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
+-----------------------+-----------------+------+-----+---------+-------+
Tabel func
mysql> desc func;
+-------+------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+----------+-------+
| name | char(64) binary | | PRI | | |
| ret | tinyint(1) | | | 0 | |
| dl | char(128) | | | | |
| type | enum('function','aggregate') | | | function | |
+-------+------------------------------+------+-----+----------+-------+
Tabel host
Digunakan untuk menyimpan informasi kompyuter mana saja yang dapat mengakses database MySQL, siapa nama pemakainya.
mysql> desc host;
+-----------------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
+-----------------------+-----------------+------+-----+---------+-------+
Tabel tables_priv
Tabel ini menyimpan informasi privilege pemakai pada level tabel
mysql> desc tables_priv;
+-------------+---------------------------------------------------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------------------------------------------------------------------------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Table_name | char(60) binary | | PRI | | |
| Grantor | char(77) | | MUL | | |
| Timestamp | timestamp(14) | YES | | NULL | |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') | | | | |
| Column_priv | set('Select','Insert','Update','References') | | | | |
+-------------+---------------------------------------------------------------------------------------+
Tabel user
Digunakan untuk menyimpan data pemakai database dan data privilegenya pada saat mengakses ke dalam database.
mysql> desc user;
+----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-----------------------------------+------+-----+---------+-------+
| Host | varchar(60) binary | | PRI | | |
| User | varchar(16) binary | | PRI | | |
| password | varchar(16) | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Show_db_priv | enum('N','Y') | | | N | |
| Super_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv| enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
| Execute_priv | enum('N','Y') | | | N | |
| Repl_slave_priv | enum('N','Y') | | | N | |
| Repl_client_priv | enum('N','Y') | | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | | | | |
| ssl_cipher | blob | | | | |
| x509_issuer | blob | | | | |
| x509_subject | blob | | | | |
| max_questions | int(11) unsigned | | | 0 | |
| max_updates | int(11) unsigned | | | 0 | |
| max_connections | int(11) unsigned | | | 0 | |
+----------------------+-----------------------------------+------+-----+---------+-------+
Pengelolaan Privilege Pemakai
Cara mendefinisikan pemakai yang diperbolehkan mengakses database MySQL adalah dengan menambahkan sebuah record kepada tabel user dengan menggunakan perintah Sql secara langsung.
Setelah pemakai dimasukan kedalam tabel user, maka berikutnya yang dilakukan adalah mendefinisikan privilege pemakai tersebut dalam mengakses dan mengelola database MySQL.
Cara mendefinisikan privilege pemakai dalam MySQL:
a. menggunakan DML
b. Menggunakan DCL
Mendefinisikan Pemakai dan Privilegenya dengan DCL
Perintah yang digunakan : GRANT
sintax:
GRANT [privilege] [all | nama-nama_kolom}
ON namadb.namatbl|*
TO namauser[@hostname]
[IDENTIFIED BY “password”
[WITH GRANT OPTION]
Privilege dalam Grant
| Nama Privilege | Keterangan |
| ALL | Digunakan untuk memberikan privilege kepada pemakai |
| ALTER | Privilege yang diberikan agar pemakai dapat melakukan perubahan pada tabel, kolom, dan indeks |
| CREATE | Privilege yang memperbolehkan user untuk membuat tabel atau database |
| DELETE | Privilege yang memperbolehkan user untuk menghapus record dari tabel |
| DROP | Privilege yang memperbolehkan user untuk menghapus tabel atau database |
| FILE | Privilege yang memperbolehkan user supaya dapat melakukan penulisan dan pembacaan file di dalam server |
| INDEX | Privilege yang memperbolehkan user untuk menambah atau menghapus indeks |
| INSERT | Privilege yang memperbolehkan user untuk menambahkan record ke dalam tabel |
| PROCESS | Privilege yang memperbolehkan user supaya dapat melihat dan mematikan proses sistem MySQL |
| REFERENCES | Privilege ini belum diimplementasikan |
| RELOAD | Privilege yang memperbolehkan user untuk dapat memberikan perintah FLUSH |
| SELECT | Privilege yang memperbolehkan user untuk menggunakan perintah SELECT |
| SHUTDOWN | Privilege yang memperbolehkan user untuk mematikan server MySQL |
| UPDATE | Privilege yang memperbolehkan user untuk menggunakan fungsi UPDATE |
Pencabutan privilege dengan DCL
Perintah yang digunakan : REVOKE
sintax:
REVOKE [privileges][nama-nama kolom]
ON namadb.namatbl
FROM namauser.hostname
Lakukan eksplorasi untuk perintah-perintah pendefinisian dan pencabutan pemakai dan privilegenya baik dengan perintah DML maupun DCL, kemudian simpulkan hasilnya.
FLUSH PRIVILEGES
Setelah selesai melakukan pendefinisian/pencabutan user maupun privilege, maka aturan tidak akan diberlakukan sebelum dijalankan perintah Flush Privilege. Flush Privilege merupakan perintah yang memaksa MySQL untuk menggunakan daftar pemakai dan privilegenya dari data pemakai dan privilege database yang baru.
BAB 8
Tujuan:
1. Backup, Restore, dan Import, Eksport data di MySQL
Backup dan Restore
Menyalin dan membaca kembali isi database ke dalam dan dari file teks.
SELECT * FROM namatabel INTO OUTFILE namafile
contoh:
SELECT * FROM anggota INTO OUTFILE “anggota.txt”
Menambah Data dari File Teks
load data infile nama_file into table nama_tabel;
cara lain:
mysqlimport namadb namafiledata/namatbl
Menyalin dan membaca kembali isi database ke dalam dan dari perintah SQL
mysqldump [option] namadb [namatabel] > namafile[.sql | .txt ]
Mengeluarkan Struktur Database dan Data
mysqldump -uroot -p perpustakaan > perpus.sql;
Mengeluarkan data saja
mysqldump -t -uroot -p perpustakaan > perpus.sql;
Mengeluarkan Struktur Tabel
mysqldump --no-data -uroot -p perpustakaan > perpus.sql;
Memasukan kembali Data hasil Dump ke dalam Database
mysql [option] namadb < namafile.sql
Menyalin dan membaca kembali isi database ke dalam dan dari satu server ke server lain.
Sintaks :
mysqldump dbygdibackup | mysql --host=namaserver dbpenampung
BAB 9
Tujuan:
1. Administrsi Database menggunakan Program Bantu Aplikasi
2. Menggunakan mysqlcc
Lakukanlah eksplorasi dengan program MySQLcc
BAB 10
Tujuan:
1. Administrsi Database menggunakan Program Bantu Aplikasi berbentuk Web
2. Menggunakan phpMyadmin
Lakukanlah eksplorasi dengan program phpMyAdmin
