SQL: CREATE TABLE

Fungsi pernyataan CREATE TABLE

Pernyataan CREATE TABLE termasuk dalam kateogri DDL (Data Definition Languange). Pernyataan ini digunakan untuk menciptakan suatu tabel dalam basis data.

Bentuk umum Pernyataan CREATE TABLE

Adapun bentuk umum pernyataan CREATE TABLE pada mySQL sebagai berikut:

1 2 3 4 5 6 7

CREATE TABLE nama_tabel  (  nama_kolom1 data_type1 [PRIMARY KEY],  nama_kolom2 data_type2,  nama_kolom3 data_type3,  ....  )

Tipe data disesuaikan dengan karakteristik dari data-data setiap kolom atau field. Untuk refferensi tipe data dapat anda baca pada artikel: SQL: Tipe-tipe data

Contoh Pernyataan CREATE TABLE

Jika database belum ada maka buatlah database kepegawaian terlebih dahulu. Dengan perintah, sebagai berikut:

CREATE DATABASE kepegawaian

Jika diketahui contoh data tabel pegawai sebagai berikut:

Perintah SQLnya sebagai berikut:

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE pegawai (  nip CHAR(6) PRIMARY KEY,  nama VARCHAR(50),  alamat VARCHAR(100),  tempat_lahir VARCHAR(30),  tanggal_lahir DATE,  gol CHAR(2),  kode_jabatan CHAR(3),  kode_fungsional CHAR(2) )

Klausa PRIMARY KEY

Klausa PRIMARY KEY dapat diletakkan diakhir pertanyaan, menjadi sebagai berikut:

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE pegawai (  nip CHAR(6),  nama VARCHAR(50),  alamat VARCHAR(100),  tempat_lahir VARCHAR(30),  tanggal_lahir DATE,  gol CHAR(2),  kode_jabatan CHAR(3),  kode_fungsional CHAR(2)  PRIMARY KEY (nip) )

Klausa NULL dan NOT NULL

• Klausa NULL digunakan untuk menentukan sebuah field dapat tidak diisi (NULL)
• Klausa NOT NULL digunakan untuk menentukan sebuah field wajin diisi dan tidak boleh kosong

Contoh penggunaan NULL dan NOT NULL :

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE pegawai (  nip CHAR(6) NOT NULL,  nama VARCHAR(50) NOT NULL,  alamat VARCHAR(100) NOT NULL,  tempat_lahir VARCHAR(30) NULL,  tanggal_lahir DATE NULL ,  gol CHAR(2) NOT NULL,  kode_jabatan CHAR(3) NOT NULL,  kode_fungsional CHAR(2) NOT NULL  PRIMARY KEY (nip) )

Contoh pernyataan diatas menunjukkan bahwa field nip, nama, alamat, gol, kode_jabatan, kode_fungsional wajib diisi. Sedangkan field tempat_lahir dan tanggal_lahir tidak wajib diisi atau dapat dikosongkan.

KLAUSA FOREIGN KEY

Dalam database relational  ditemukan klausa primary key dan foreign key. Tujuan utama dari adanya kedua klausa adalah untuk mengidentifikasi relasi setiap tabel dengan tabel lain.

• Primary key adalah field atau beberapa field pada tabel yang bersifat unik dan menjadi dasar pengurutan dalam suatu tabel .
• Foreign Key adalah satu atau beberapa field pada tabel yang merupakan field yang dihubungkan ke field primarykey tabel induknya.

Sebagai contoh relasi tabel sebagai berikut:


Pada contoh skema basis data diatas dapat diketahui bahwa:

• Tabel jabatan memiliki primary key kode_jabatan
• Tabel fungsional memiliki primary key kode_fungsional
• Tabel pegawai memiliki primary key nip dan foreign key kode_jabatan dan kode_fungsional

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE pegawai (  nip CHAR(6) NOT NULL PRIMARY KEY,  nama VARCHAR(50) NOT NULL,  alamat VARCHAR(100) NOT NULL,  tempat_lahir VARCHAR(30) NULL,  tanggal_lahir DATE NULL ,  gol CHAR(2) NOT NULL,  kode_jabatan CHAR(3) NOT NULL  FOREIGN KEY REFERENCES jabatan(Kode_jabatan),  kode_fungsional CHAR(2) NOT NULL  FOREIGN KEY REFERENCES fungsioanal(Kode_fungsional) )

 Klausa ON DELETE  dan ON UPDATE

Pada MYSQL, kita harus menambahkan perintah ON DELETE [opsi] dan ON UPDATE [opsi]pada table yang mereferensikan foreign key. Contoh:

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE pegawai (  nip CHAR(6) NOT NULL PRIMARY KEY,  nama VARCHAR(50) NOT NULL,  alamat VARCHAR(100) NOT NULL,  tempat_lahir VARCHAR(30) NULL,  tanggal_lahir DATE NULL ,  gol CHAR(2) NOT NULL,  kode_jabatan CHAR(3) NOT NULL  FOREIGN KEY REFERENCES jabatan(Kode_jabatan) ON UPDATE DELETE,  kode_fungsional CHAR(2) NOT NULL,  FOREIGN KEY REFERENCES fungsioanal(Kode_fungsional) ON UPDATE  DELETE )

Opsi pada perintah tersebut jelasnya dibawah ini.

• RESTRICT, Jika tabel anak berisi nilai dalam kolom yang mengkait yang nilainya sama dengan di kolom terkait pada tabel induk, baris dalam tabel induk tidak bisa dihapus, dan nilai di kolom terkait tidak dapat diupdate. Ini adalah opsi default jika klausa ON DELETE atau ON UPDATE tidak dispesifikasikan.
• CASCADE, Baris-baris dalam tabel anak yang berisi nilai-nilai yang juga terdapat dalam kolom terkait dari tabel induk dihapus ketika barisbaris yang berkaitan dihapus dari tabel induk. Baris-baris dalam tabel anak yang berisi nilai-nilai yang juga terdapat dalam kolom terkait dari tabel induk diupdate ketika nilai-nilai yang berkaitan diupdate dalam tabel induk.
• SET NULL, Nilai-nilai dalam kolom yang mengkait dari tabel anak diset ke NULL saat baris-baris dengan data terkait dalam tabel induk dihapus dari tabel induk atau ketika data terkait dalam tabel induk diupdate. Untuk menggunakan opsi ini, semua kolom-kolom yang mengkait dalam tabel anak harus mengijinkan nilai NULL.
• NO ACTION Tidak ada aksi yang diambil dalam tabel anak ketika baris-baris dihapus dari tabel induk atau nilai-nilai dalam kolom terkait dalam tabel induk diupdate.
• SET DEFAULT Nilai-nilai dalam kolom-kolom yang mengkait dari tabel anak diset ke nilai default mereka ketika baris-baris dihapus dari tabel induk atau kolom terkait dari tabel induk diupdate.

Tipe data MySQL

• tipe CHAR umumnya digunakan untuk field yang memiliki panjang data yang seragam. misalnya nip, gol, kode_jabatan, dan kode_fungsional
• tipe VARCHAR digunakan untuk field yang memiliki panjang data yang tidak seragam, misalnya nama, alamat dan tempat_lahir.

Latihan Pernyataan CREATE TABLE

latihan pernyataan CREATE TABLE

1. Gambarkan skema basis data dari contoh data diatas
2. Buatlah struktur tabel-tabel berikut menggunakan pernyataan CREATE TABLE lengkap dengan klausa PRIMARY KEY, FOREIGN KEY dan ON UPDATE DELETE

MySQL: CREATE DATABASE, USE, DROP DATABASE

CREATE DATABASE adalah pernyataaan yang digunakan untuk membuat basis data dengan nama yang diberikan. Agar dapat menggunakan pernyataan ini, maka perlu diperlukan hak akses CREATE secara penuh terhadap basis data, CREATE SCHEMA dalam MySQL adalah sinonim untuk pernyataan CREATE DATABASE.

Ketika pernyataan ini dieksekusi maka dapat saja terjadi kesalahan jika nama basisdata yang disebutkan telah ada, kecuali jika klausa IF NOT EXIST disertakan. Pada MySQL 5.5.3, pernyataan CREATE DATABASE tidak diizinkan selama sesi memiliki pernyataan LOCK TABLES yang masih aktif.

Sintaks pernyataan CREATE DATABASE

1 2 3 4 5 6

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name     [create_specification] ...   create_specification:     [DEFAULT] CHARACTER SET [=] charset_name   | [DEFAULT] COLLATE [=] collation_name

• create_specification menentukan karakteristik basis data. Karakteristik basisdata disimpan dalam file db.opt dalam direktori database.
• CHARACTER SET merupakan klausa yang digunakan menentukan secara default dari Character Set  basis data

Basisdata di MySQL diimplementasikan sebagai direktori yang berisi file-file yang namanya diseuaika sesuai dengan nama-nam tabel dalam basis data.  Karena tidak ada tabel dalam database ketika awalnya dibuat, pernyataan CREATE DATABASE membuat hanya sebuah direktori di bawah direktori data MySQL dan file db.opt.

Jika secara manual anda membuat direktori di bawah direktori data (misalnya, dengan mkdir), server menganggap akan menganggapnya sebagai sebuah direktori basis data dan akan muncul dalam output ketika pernyataan SHOW DATABASES dijalankan

Cara Membuat database MySQL

Dalam penggunaannya sehari-hari, sebuah aplikasi biasanya hanya akan memiliki sebuah database. Di dalam MySQL (dan juga di dalam aplikasi relational database lainnya), Database adalah kumpulan dari tabel-tabel yang saling berhubungan. Database adalah tempat dimana tabel-tabel akan dibuat.

Untuk membuat database, format penulisan querynya adalah:

1	CREATE DATABASE [IF NOT EXISTS] nama_database

Di dalam manual MySQL, dan juga di dalam tutorial ini, format pernyataan yang terdapat di dalam kurung siku menunjukkan kalusa optional, artinya boleh diabaikan jika tidak diperlukan.
Jika kita ingin membuat sebuah basisdata kepegawaian, maka pernyataanya sebagai berikut:

1 2 3

mysql> CREATE DATABASE kepegawaian;   Query OK, 1 row affected (0.00 sec)

Tambahan klausa [IF NOT EXISTS] digunakan untuk membuat MySQL tidak menampilkan pesan error jika database tersebut telah ada sebelumnya dalam database.

Contohnya, jika kita menjalankan lagi query untuk membuat database mahasiswa, MySQL akan menampilkan pesan error.

1 2 3 4

mysql> CREATE DATABASE kepegawaian;   ERROR 1007 (HY000): Can't create database 'kepegawaian'; database exists

Pesan error ini berguna untuk kita mengidentifikasi kesalahan, namun apabila kita membuat kode query yang panjang untuk dieksekusi secara keseluruhan (kita akan mempelajarinya nanti), pesan error akan menyebabkan query berhenti diproses.

Klausa [IF NOT EXISTS] akan membuat database jika database itu belum ada sebelumnya. Jika sudah ada, pernyataan CREATE DATABASE tidak akan menghasilkan apa-apa (database yang lama tidak akan tertimpa).

1 2 3

mysql> CREATE DATABASE IF NOT EXISTS kepegawaian;   Query OK, 1 row affected, 1 warning (0.00 sec)

Pernyataan SHOW DATABASES

Di dalam MySQL Server, biasanya akan terdapat beberapa basisdata, tergantung kebutuhan kita. Untuk melihat seluruh basisdata yang berada pada MySQL Server, gunakan pernyataan:

1 2 3 4 5 6 7 8 9 10 11 12 13

SHOW DATABASES; mysql> SHOW DATABASES;   +--------------------+ | Database           | +--------------------+ | information_schema | | kepegawaian        | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.00 sec)

Kita dapat melihat bahwa MySQL Server telah berisi beberapa database bawaan, selain database mahasiswa yang telah dibuat sebelumnya.

Pernyataan USE

Kita harus memilih sebuah database yang akan digunakan, format query untuk memilih database ini adalah:

1	USE nama_database;

nama_database adalah basisdata yang akan kita gunakan. Jika kita ingin menggunakan basisdata kepegawian, maka querynya adalah:

1 2 3

mysql> USE kepegawaian;   Database changed

Selanjutnya setiap pernyataan pembuatan tabel misalnya, akan membuat sebuah tabel didalam basisdata terpilih, dalam hal ini basisdata mahasiswa.

Pernyataan DROP DATABASE

Jika database sudah tidak digunakan lagi, kita dapat menghapusnya. Proses penghapusan ini akan menghapus basisdata , termasuk seluruh tabel dan isi dari tabel tersebut. Sebuah basisdata yang telah dihapus tidak dapat ditampilkan kembali. Kita harus yakinkan bahwa basisdata tersebut memang tidak akan digunakan lagi.

Sintaks yang digunakan untuk menghapus basisdata :

1	DROP DATABASE [IF EXISTS] database_name;

Sama seperti query pada pembuatan database, pilihan [IF EXISTS] digunakan untuk menghilangkan pesan error jika seandainya database tersebut memang tidak ada.

Jika kita ingin menghapus basisdata kepegawaian, contoh querynya adalah:

1 2 3

mysql> DROP DATABASE kepegawaian;   Query OK, 0 rows affected (0.01 sec)

Jika kita berusaha menghapus database kepegawaian lagi (yang memang sudah tidak ada), akan muncul pesan error:

1 2 3 4

mysql> DROP DATABASE kepegawaian;   ERROR 1008 (HY000): Can't drop database 'mahasiswa'; database doesn't exist

Dengan menggunakan perintah opsional [IF EXISTS], pesan error tidak tampil:

1 2 3

mysql> DROP DATABASE IF EXISTS mahasiswa;   Query OK, 0 rows affected, 1 warning (0.00 sec)

Karena query SQL menggunakan bahasa inggris, kita harus perhatikan kesalahan untuk pengetikkan kata singular (tunggal) dan plural (jamak). Misalnya untuk melihat database, querynya adalah SHOW DATABASES, tetapi jika kita menjalankan query SHOW DATABASE; ini akan menyebabkan error. Karena seharusnya adalah DATABASES, bukan DATABASE. Juga seharusnya adalah IF EXISTS, bukan IF EXIST (Penggunaan huruf S diakhir kata).

1 2 3 4 5

mysql> SHOW DATABASE;   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 'DATABASE' at line 1

Pernyataan SELECT dalam MySQL

Fungsi Pernyataan SELECT

Pernyataan SELECT digunakan untuk meload data/menampilkan data yang dapat difilter dengan kondisi tertentu, dikelompokkan dan diurutkan berdasarkan kolom tertentu.

Sintaks Pernyataan SELECT

Sintak sederhana pernyataan SELECT denga klausa WHERE.

SELECT daftar_field
FROM nama_tabel
[WHERE kriteria]

Catatan:

• daftar_field, menyatakan field-field yang akan ditampilkan
• nama_tabel, meyatakan tabel yang akan ditampilkan
• Klausa WHERE bersifat opsional dan digunakan sebagai filter terhadap data yang akan ditampilkan.
• kriteria merupakan menyatakan ekpresi logika.

Digram berikut menunjukkan sintak pernyataan SELECT secara lebih lengkap.

Diagram Pernyataan SELECT

Latihan Pernyataan SELECT

Menyiapkan database dan Tabel

Sebelum berlatih menggunakan pernyataan SELECT siapkanlah database akademik dan tabel matakuliah dengan langkah-langkah berikut

• Jalankan MySQL command prompt, dengan perintah sebagai berikut:

CD C:\xampp\mysql\bin
mysql -u root -p

• Buat database akademik

mysql> CREATE DATABASE akademik;
Query OK, 1 row affected (0.15 sec)

• Aktifkan database akademik

mysql> USE akademik;
Database changed

• Buat tabel matakuliah dengan struktur sebagai berikut

Field	Type	Index
kode_mk	CHAR(4)	PRIMARY KEY
nama_mk	VARCHAR(30)
sks	INT(1)
semester	INT(1)

mysql> CREATE TABLE matakuliah
 -> (npm CHAR(4) PRIMARY KEY,
 -> nama_mk VARCHAR(50),
 -> sks INT(1),
 -> semester INT(1));
Query OK, 0 rows affected (2.20 sec)

• Tampilkan struktur tabel matakuliah

mysql> DESC matakuliah;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| npm      | char(4)     | NO   | PRI | NULL    |       |
| nama_mk  | varchar(50) | YES  |     | NULL    |       |
| sks      | int(1)      | YES  |     | NULL    |       |
| semester | int(1)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (1.14 sec)

•  Masukkan data-data berikut pada tabel matakuliah

 mysql> INSERT INTO `matakuliah` (`npm`, `nama_mk`, `sks`, `semester`) VALUES
 -> ('MK01', 'Pengantar Teknologi Informasi', 2, 1),
 -> ('MK02', 'Algoritma Dan Pemrograman', 3, 1),
 -> ('MK03', 'Arsitektur Dan Organisasi Komputer', 4, 1),
 -> ('MK04', 'Desain Web', 4, 1),
 -> ('MK05', 'Aljabar Linier', 2, 2),
 -> ('MK06', 'Instalasi Dan Troubleshooting', 4, 2),
 -> ('MK07', 'Bahasa Inggris', 2, 3),
 -> ('MK08', 'Bahasa Rakitan dan C++', 3, 3),
 -> ('MK09', 'Interaksi Manusia dan Komputer', 3, 3),
 -> ('MK10', 'Bahasa Ingrris Teknik Informatika', 2, 4),
 -> ('MK11', 'Interfacing', 3, 4),
 -> ('MK12', 'Grafika Komputer', 3, 5),
 -> ('MK13', 'Algoritma Genetika', 4, 5),
 -> ('MK14', 'Bisnis Berbasis Internet', 3, 6),
 -> ('MK15', 'Cisco dan Mikrotik', 3, 6),
 -> ('MK16', 'Administrasi Sistem Jaringan', 3, 6),
 -> ('MK17', 'Animasi', 3, 7),
 -> ('MK18', 'Asistensi', 1, 7),
 -> ('MK19', 'Data Mining', 2, 7),
 -> ('MK20', 'e-Commerce', 3, 7);
Query OK, 20 rows affected (0.05 sec)
Records: 20 Duplicates: 0 Warnings: 0

Contoh pernyataan SELECT

• Tampilkan seluruh field tabel matakuliah

mysql> SELECT * FROM matakuliah;
+------+------------------------------------+------+----------+
| npm  | nama_mk                            | sks  | semester |
+------+------------------------------------+------+----------+
| MK01 | Pengantar Teknologi Informasi      |    2 |        1 |
| MK02 | Algoritma Dan Pemrograman          |    3 |        1 |
| MK03 | Arsitektur Dan Organisasi Komputer |    4 |        1 |
| MK04 | Desain Web                         |    4 |        1 |
| MK05 | Aljabar Linier                     |    2 |        2 |
| MK06 | Instalasi Dan Troubleshooting      |    4 |        2 |
| MK07 | Bahasa Inggris                     |    2 |        3 |
| MK08 | Bahasa Rakitan dan C++             |    3 |        3 |
| MK09 | Interaksi Manusia dan Komputer     |    3 |        3 |
| MK10 | Bahasa Ingrris Teknik Informatika  |    2 |        4 |
| MK11 | Interfacing                        |    3 |        4 |
| MK12 | Grafika Komputer                   |    3 |        5 |
| MK13 | Algoritma Genetika                 |    4 |        5 |
| MK14 | Bisnis Berbasis Internet           |    3 |        6 |
| MK15 | Cisco dan Mikrotik                 |    3 |        6 |
| MK16 | Administrasi Sistem Jaringan       |    3 |        6 |
| MK17 | Animasi                            |    3 |        7 |
| MK18 | Asistensi                          |    1 |        7 |
| MK19 | Data Mining                        |    2 |        7 |
| MK20 | e-Commerce                         |    3 |        7 |
+------+------------------------------------+------+----------+
20 rows in set (0.00 sec)

• Tampilkan field semester dan nama_mk tabel matakuliah

mysql> SELECT semester, nama_mk 
    -> FROM matakuliah;
+----------+------------------------------------+
| semester | nama_mk                            |
+----------+------------------------------------+
|        1 | Pengantar Teknologi Informasi      |
|        1 | Algoritma Dan Pemrograman          |
|        1 | Arsitektur Dan Organisasi Komputer |
|        1 | Desain Web                         |
|        2 | Aljabar Linier                     |
|        2 | Instalasi Dan Troubleshooting      |
|        3 | Bahasa Inggris                     |
|        3 | Bahasa Rakitan dan C++             |
|        3 | Interaksi Manusia dan Komputer     |
|        4 | Bahasa Ingrris Teknik Informatika  |
|        4 | Interfacing                        |
|        5 | Grafika Komputer                   |
|        5 | Algoritma Genetika                 |
|        6 | Bisnis Berbasis Internet           |
|        6 | Cisco dan Mikrotik                 |
|        6 | Administrasi Sistem Jaringan       |
|        7 | Animasi                            |
|        7 | Asistensi                          |
|        7 | Data Mining                        |
|        7 | e-Commerce                         |
+----------+------------------------------------+
20 rows in set (0.00 sec)

• Tampilkan semester dan nama matakuliah khusus semester satu saja

mysql> SELECT semester, nama_mk 
    -> FROM matakuliah
    -> WHERE semester=1;
+----------+------------------------------------+
| semester | nama_mk                            |
+----------+------------------------------------+
|        1 | Pengantar Teknologi Informasi      |
|        1 | Algoritma Dan Pemrograman          |
|        1 | Arsitektur Dan Organisasi Komputer |
|        1 | Desain Web                         |
+----------+------------------------------------+
4 rows in set (0.04 sec)

• Tampilkan semester dan nama matakuliah khusus semester selain semester satu

mysql> SELECT semester, nama_mk FROM matakuliah
    -> WHERE NOT(semester=1);
+----------+-----------------------------------+
| semester | nama_mk                           |
+----------+-----------------------------------+
|        2 | Aljabar Linier                    |
|        2 | Instalasi Dan Troubleshooting     |
|        3 | Bahasa Inggris                    |
|        3 | Bahasa Rakitan dan C++            |
|        3 | Interaksi Manusia dan Komputer    |
|        4 | Bahasa Ingrris Teknik Informatika |
|        4 | Interfacing                       |
|        5 | Grafika Komputer                  |
|        5 | Algoritma Genetika                |
|        6 | Bisnis Berbasis Internet          |
|        6 | Cisco dan Mikrotik                |
|        6 | Administrasi Sistem Jaringan      |
|        7 | Animasi                           |
|        7 | Asistensi                         |
|        7 | Data Mining                       |
|        7 | e-Commerce                        |
+----------+-----------------------------------+
16 rows in set (0.00 sec)

• Tampilkan semester dan nama matakuliah khusus semester sebelum semester tiga

mysql> SELECT semester, nama_mk FROM matakuliah
    -> WHERE semester < 3;
+----------+------------------------------------+
| semester | nama_mk                            |
+----------+------------------------------------+
|        1 | Pengantar Teknologi Informasi      |
|        1 | Algoritma Dan Pemrograman          |
|        1 | Arsitektur Dan Organisasi Komputer |
|        1 | Desain Web                         |
|        2 | Aljabar Linier                     |
|        2 | Instalasi Dan Troubleshooting      |
+----------+------------------------------------+
6 rows in set (0.00 sec)

• Tampilkan semester dan nama matakuliah untuk semester tiga keatas

mysql> SELECT semester, nama_mk FROM matakuliah
    -> WHERE semester >= 3;
+----------+-----------------------------------+
| semester | nama_mk                           |
+----------+-----------------------------------+
|        3 | Bahasa Inggris                    |
|        3 | Bahasa Rakitan dan C++            |
|        3 | Interaksi Manusia dan Komputer    |
|        4 | Bahasa Ingrris Teknik Informatika |
|        4 | Interfacing                       |
|        5 | Grafika Komputer                  |
|        5 | Algoritma Genetika                |
|        6 | Bisnis Berbasis Internet          |
|        6 | Cisco dan Mikrotik                |
|        6 | Administrasi Sistem Jaringan      |
|        7 | Animasi                           |
|        7 | Asistensi                         |
|        7 | Data Mining                       |
|        7 | e-Commerce                        |
+----------+-----------------------------------+
14 rows in set (0.00 sec)

• Tampilkan semester dan nama matakuliah untuk semester tdua dan empat

mysql> SELECT semester, nama_mk FROM matakuliah
    -> WHERE (semester = 2) OR (semester=4);
+----------+-----------------------------------+
| semester | nama_mk                           |
+----------+-----------------------------------+
|        2 | Aljabar Linier                    |
|        2 | Instalasi Dan Troubleshooting     |
|        4 | Bahasa Ingrris Teknik Informatika |
|        4 | Interfacing                       |
+----------+-----------------------------------+
4 rows in set (0.00 sec)

• Tampilkan matakuliah semester dua yang sks-nya  tiga keatas

mysql> SELECT * FROM matakuliah
    -> WHERE (semester = 1) AND (sks>=3);
+------+------------------------------------+------+----------+
| npm  | nama_mk                            | sks  | semester |
+------+------------------------------------+------+----------+
| MK02 | Algoritma Dan Pemrograman          |    3 |        1 |
| MK03 | Arsitektur Dan Organisasi Komputer |    4 |        1 |
| MK04 | Desain Web                         |    4 |        1 |
+------+------------------------------------+------+----------+
3 rows in set (0.00 sec)

• Tampilkan nama matakuliah  dan sks yang nama matakuliahnya berawalan huruf A

mysql> SELECT nama_mk, sks FROM matakuliah
    -> WHERE nama_mk LIKE 'A%';
+------------------------------------+------+
| nama_mk                            | sks  |
+------------------------------------+------+
| Algoritma Dan Pemrograman          |    3 |
| Arsitektur Dan Organisasi Komputer |    4 |
| Aljabar Linier                     |    2 |
| Algoritma Genetika                 |    4 |
| Administrasi Sistem Jaringan       |    3 |
| Animasi                            |    3 |
| Asistensi                          |    1 |
+------------------------------------+------+
7 rows in set (0.04 sec)

• Tampilkan nama matakuliah  dan sks yang nama matakuliahnya mengandung kata ‘dan’

mysql> SELECT nama_mk, sks FROM matakuliah
    -> WHERE nama_mk LIKE '%dan%';
+------------------------------------+------+
| nama_mk                            | sks  |
+------------------------------------+------+
| Algoritma Dan Pemrograman          |    3 |
| Arsitektur Dan Organisasi Komputer |    4 |
| Instalasi Dan Troubleshooting      |    4 |
| Bahasa Rakitan dan C++             |    3 |
| Interaksi Manusia dan Komputer     |    3 |
| Cisco dan Mikrotik                 |    3 |
+------------------------------------+------+
6 rows in set (0.00 sec)

•  Tampilkan nama matakuliah  dan sks yang nama matakuliahnya diakhir kata ‘Komputer’

mysql> SELECT nama_mk, sks FROM matakuliah
 -> WHERE nama_mk LIKE '%Komputer';
+------------------------------------+------+
| nama_mk                            | sks  |
+------------------------------------+------+
| Arsitektur Dan Organisasi Komputer |  4   |
| Interaksi Manusia dan Komputer     |  3   |
| Grafika Komputer                   |  3   |
+------------------------------------+------+
3 rows in set (0.00 sec)

•  Tampilkan nama matakuliah  dan sks yang nama yang huruf keduanya “a”

mysql> SELECT nama_mk, sks FROM matakuliah
    -> WHERE nama_mk LIKE '_a%';
+-----------------------------------+------+
| nama_mk                           | sks  |
+-----------------------------------+------+
| Bahasa Inggris                    |    2 |
| Bahasa Rakitan dan C++            |    3 |
| Bahasa Ingrris Teknik Informatika |    2 |
| Data Mining                       |    2 |
+-----------------------------------+------+
4 rows in set (0.00 sec)

MySQL: Klausa ORDER BY

Klausa ORDER BY digunkan untuk mengurutkan data berdasarkan fiel tertentu. Dalam klausa ini terdapat dua jenis pengurutan yaitu:

• Ascending (ASC), merupakan pengurutan secara menaik, dari nilai yang terkecil hingga yang terbesar.
• Desecending (DESC), merupakan pengurutan secara menurun dari nilai yang terbesar ke nilai yang terckecil.

Sintaks Klausa ORDER BY

Klausa ORDER BY digunakan pada saat pernyataan SELECT, sintaksnya sebagai berikut:

SELECT daftar_nama_field
FROM nama_tabel
[WHERE syarat]
ORDER BY nama_kolom ASC|DESC

Keterangan:

• daftar_nama_field  adalah nama-nama kolom yang akan ditampilkan
• nama_tabel adalah nama tabel yang akana dikolom
• syarat, merupakan ekspresi logika yang digunakan menfiter record-record
• nama_kolom adalah nama kolom dimana record-record ditampilkan berdasarkan kolom tersebut.
• klausa ASC menyatakan record ditampilkan urut secara menaik (ascending), secara default jika klausa ASC dan DESC tidak disertakan maka pengurutan akan dilakukan secara ascending.

Latihan Klausa ORDER BY

Penyiapan data

Jika database akademik dan matakuliah belum anda buat maka siapkan data terlebih dahulu sebagai berikut:

• Jalankan MySQL command prompt, dengan perintah sebagai berikut:

CD C:\xampp\mysql\bin
mysql -u root -p

• Buat database akademik

mysql> CREATE DATABASE akademik;
Query OK, 1 row affected (0.15 sec)

• Aktifkan database akademik

mysql> USE akademik;
Database changed

• Buat tabel matakuliah dengan struktur sebagai berikut

Field	Type	Index
kode_mk	CHAR(4)	PRIMARY KEY
nama_mk	VARCHAR(30)
sks	INT(1)
semester	INT(1)

mysql> CREATE TABLE matakuliah
 -> (npm CHAR(4) PRIMARY KEY,
 -> nama_mk VARCHAR(50),
 -> sks INT(1),
 -> semester INT(1));
Query OK, 0 rows affected (2.20 sec)

• Tampilkan struktur tabel matakuliah

mysql> DESC matakuliah;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| npm      | char(4)     | NO   | PRI | NULL    |       |
| nama_mk  | varchar(50) | YES  |     | NULL    |       |
| sks      | int(1)      | YES  |     | NULL    |       |
| semester | int(1)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (1.14 sec)

•  Masukkan data-data berikut pada tabel matakuliah

 mysql> INSERT INTO `matakuliah` (`npm`, `nama_mk`, `sks`, `semester`) VALUES
 -> ('MK01', 'Pengantar Teknologi Informasi', 2, 1),
 -> ('MK02', 'Algoritma Dan Pemrograman', 3, 1),
 -> ('MK03', 'Arsitektur Dan Organisasi Komputer', 4, 1),
 -> ('MK04', 'Desain Web', 4, 1),
 -> ('MK05', 'Aljabar Linier', 2, 2),
 -> ('MK06', 'Instalasi Dan Troubleshooting', 4, 2),
 -> ('MK07', 'Bahasa Inggris', 2, 3),
 -> ('MK08', 'Bahasa Rakitan dan C++', 3, 3),
 -> ('MK09', 'Interaksi Manusia dan Komputer', 3, 3),
 -> ('MK10', 'Bahasa Ingrris Teknik Informatika', 2, 4),
 -> ('MK11', 'Interfacing', 3, 4),
 -> ('MK12', 'Grafika Komputer', 3, 5),
 -> ('MK13', 'Algoritma Genetika', 4, 5),
 -> ('MK14', 'Bisnis Berbasis Internet', 3, 6),
 -> ('MK15', 'Cisco dan Mikrotik', 3, 6),
 -> ('MK16', 'Administrasi Sistem Jaringan', 3, 6),
 -> ('MK17', 'Animasi', 3, 7),
 -> ('MK18', 'Asistensi', 1, 7),
 -> ('MK19', 'Data Mining', 2, 7),
 -> ('MK20', 'e-Commerce', 3, 7);
Query OK, 20 rows affected (0.05 sec)
Records: 20 Duplicates: 0 Warnings: 0

Contoh penggunaan Klausa ORDER BY

• Tampilkan kolom nama_mk, sks dan semester pada tabel matakuliah urut berdasarkan nama_mk

mysql> SELECT nama_mk, sks, semester
    -> FROM matakuliah
    -> ORDER BY nama_mk;
+------------------------------------+------+----------+
| nama_mk                            | sks  | semester |
+------------------------------------+------+----------+
| Administrasi Sistem Jaringan       |    3 |        6 |
| Algoritma Dan Pemrograman          |    3 |        1 |
| Algoritma Genetika                 |    4 |        5 |
| Aljabar Linier                     |    2 |        2 |
| Animasi                            |    3 |        7 |
| Arsitektur Dan Organisasi Komputer |    4 |        1 |
| Asistensi                          |    1 |        7 |
| Bahasa Inggris                     |    2 |        3 |
| Bahasa Ingrris Teknik Informatika  |    2 |        4 |
| Bahasa Rakitan dan C++             |    3 |        3 |
| Bisnis Berbasis Internet           |    3 |        6 |
| Cisco dan Mikrotik                 |    3 |        6 |
| Data Mining                        |    2 |        7 |
| Desain Web                         |    4 |        1 |
| e-Commerce                         |    3 |        7 |
| Grafika Komputer                   |    3 |        5 |
| Instalasi Dan Troubleshooting      |    4 |        2 |
| Interaksi Manusia dan Komputer     |    3 |        3 |
| Interfacing                        |    3 |        4 |
| Pengantar Teknologi Informasi      |    2 |        1 |
+------------------------------------+------+----------+
20 rows in set (0.00 sec)

• Tampilkan field nama_mk dan sks pada tabel matakuliah urut jumlah sks terbesar ke sks terkecil.

mysql> SELECT nama_mk, sks
    -> FROM matakuliah
    -> ORDER BY sks DESC
    -> ;
+------------------------------------+------+
| nama_mk                            | sks  |
+------------------------------------+------+
| Instalasi Dan Troubleshooting      |    4 |
| Algoritma Genetika                 |    4 |
| Arsitektur Dan Organisasi Komputer |    4 |
| Desain Web                         |    4 |
| Bisnis Berbasis Internet           |    3 |
| Cisco dan Mikrotik                 |    3 |
| e-Commerce                         |    3 |
| Administrasi Sistem Jaringan       |    3 |
| Animasi                            |    3 |
| Grafika Komputer                   |    3 |
| Interfacing                        |    3 |
| Interaksi Manusia dan Komputer     |    3 |
| Bahasa Rakitan dan C++             |    3 |
| Algoritma Dan Pemrograman          |    3 |
| Bahasa Ingrris Teknik Informatika  |    2 |
| Bahasa Inggris                     |    2 |
| Aljabar Linier                     |    2 |
| Data Mining                        |    2 |
| Pengantar Teknologi Informasi      |    2 |
| Asistensi                          |    1 |
+------------------------------------+------+
20 rows in set (0.00 sec)

• Tampilkan semester, nama_mk, sks pada tabel matakuliah urut semester dan nama_mk

mysql> SELECT semester, nama_mk, sks
    -> FROM matakuliah
    -> ORDER BY semester, nama_mk;
+----------+------------------------------------+------+
| semester | nama_mk                            | sks  |
+----------+------------------------------------+------+
|        1 | Algoritma Dan Pemrograman          |    3 |
|        1 | Arsitektur Dan Organisasi Komputer |    4 |
|        1 | Desain Web                         |    4 |
|        1 | Pengantar Teknologi Informasi      |    2 |
|        2 | Aljabar Linier                     |    2 |
|        2 | Instalasi Dan Troubleshooting      |    4 |
|        3 | Bahasa Inggris                     |    2 |
|        3 | Bahasa Rakitan dan C++             |    3 |
|        3 | Interaksi Manusia dan Komputer     |    3 |
|        4 | Bahasa Ingrris Teknik Informatika  |    2 |
|        4 | Interfacing                        |    3 |
|        5 | Algoritma Genetika                 |    4 |
|        5 | Grafika Komputer                   |    3 |
|        6 | Administrasi Sistem Jaringan       |    3 |
|        6 | Bisnis Berbasis Internet           |    3 |
|        6 | Cisco dan Mikrotik                 |    3 |
|        7 | Animasi                            |    3 |
|        7 | Asistensi                          |    1 |
|        7 | Data Mining                        |    2 |
|        7 | e-Commerce                         |    3 |
+----------+------------------------------------+------+
20 rows in set (0.02 sec)

• Tampilkan secara urut nama_mk untuk matakuliah yang sks=3

mysql> SELECT *
    -> FROM matakuliah
    -> WHERE sks=3
    -> ORDER BY nama_mk;
+------+--------------------------------+------+----------+
| npm  | nama_mk                        | sks  | semester |
+------+--------------------------------+------+----------+
| MK16 | Administrasi Sistem Jaringan   |    3 |        6 |
| MK02 | Algoritma Dan Pemrograman      |    3 |        1 |
| MK17 | Animasi                        |    3 |        7 |
| MK08 | Bahasa Rakitan dan C++         |    3 |        3 |
| MK14 | Bisnis Berbasis Internet       |    3 |        6 |
| MK15 | Cisco dan Mikrotik             |    3 |        6 |
| MK20 | e-Commerce                     |    3 |        7 |
| MK12 | Grafika Komputer               |    3 |        5 |
| MK09 | Interaksi Manusia dan Komputer |    3 |        3 |
| MK11 | Interfacing                    |    3 |        4 |
+------+--------------------------------+------+----------+
10 rows in set (0.00 sec)

Fungsi Agregat dalam MySQL

Fungsi agregat  dalam MySQL adalah fungsi yang menerima koleksi nilai dan mengembalikan nilai tunggal sebagai hasilnya, seperti: jumlah data, nilai minimun, nilai maximum dan nilai rata-rata .

Jenis Fungsi Agregat

Standar ISO mendefinisikan lima jenis fungsi agregat, yaitu:

SUM	digunakan untuk menghitung total nilai dari kolom tertentu
COUNT	digunakan untuk menghitung jumlah record.
AVG	digunakan untuk menampikan nilai rata-rata dari suatu kolom
MAX	digunakan untuk menampikan nilai tertinggi dari suatu kolom
MIN	digunakan untuk menampikan nilai terendah dari suatu kolom

Latihan Fungsi Agregat

Sebelum latihan, kita buat dulu tabel buku dengan struktur tabel sebagai berikut.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

mysql> CREATE TABLE buku (     ->   kode_buku CHAR(5) PRIMARY KEY,     ->   judul_buku VARCHAR(50),     ->   pengarang VARCHAR(30),     ->   penerbit VARCHAR(30),     ->   tahun YEAR,     ->   kategori VARCHAR(30),     ->   harga INT(7),     ->   tgl_inventaris DATE     -> ); Query OK, 0 rows affected (0.15 sec)   mysql> DESC buku; +----------------+-------------+------+-----+---------+-------+ | Field          | Type        | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | kode_buku      | char(5)     | NO   | PRI | NULL    |       | | judul_buku     | varchar(50) | YES  |     | NULL    |       | | pengarang      | varchar(30) | YES  |     | NULL    |       | | penerbit       | varchar(30) | YES  |     | NULL    |       | | tahun          | year(4)     | YES  |     | NULL    |       | | kategori       | varchar(30) | YES  |     | NULL    |       | | harga          | int(7)      | YES  |     | NULL    |       | | tgl_inventaris | date        | YES  |     | NULL    |       | +----------------+-------------+------+-----+---------+-------+ 8 rows in set (0.01 sec)

dengan data sebagai berikut:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> INSERT INTO buku VALUES  -> ('B0001', 'Harry Potter', 'JK Rowling', 'British Press', 2013, 'Fiksi', 50000, '201-03-01'),  -> ('B0002', 'Sistem Basis Data', 'Abdul Kadir', 'Andi Offset', 2013, 'Buku Teks', 40000, '2015-03-01'),  -> ('B0003', 'Sistem Basis Data', 'Fathansyah', 'ITB Press', 2013, 'Buku Teks', 30000, '2015-03-01'),  -> ('B0004', 'Prophet Muhammad', 'Amir Abdullah', 'Madina Press', 2014, 'Biografi', 45000, '2015-03-01'),  -> ('B0005', 'Ketika Cinta Bertasbih', 'Habiburahaman ES', 'Madina Press', 2014, 'Fiksi', 75000, '2015-02-01'),  -> ('B0006', 'Pemrograman Basis Data', 'Abdul Kadir', 'Andi Offset', 2015, 'Buku Teks', 67000, '2015-02-01'); Query OK, 6 rows affected (0.05 sec) Records: 6 Duplicates: 0 Warnings: 0   mysql> SELECT * FROM buku; +-----------+------------------------+------------------+---------------+-------+-----------+------+----------------+ | kode_buku | judul_buku             | pengarang        | penerbit      | tahun | kategori  | harga| tgl_inventaris | +-----------+------------------------+------------------+---------------+-------+-----------+------+----------------+ | B0001     | Harry Potter           | JK Rowling       | British Press |  2013 | Fiksi     | 50000| 2015-03-01     | | B0002     | Sistem Basis Data      | Abdul Kadir      | Andi Offset   |  2013 | Buku Teks | 40000| 2015-03-01     | | B0003     | Sistem Basis Data      | Fathansyah       | ITB Press     |  2013 | Buku Teks | 30000| 2015-03-01     | | B0004     | Prophet Muhammad       | Amir Abdullah    | Madina Press  |  2014 | Biografi  | 45000| 2015-03-01     | | B0005     | Ketika Cinta Bertasbih | Habiburahaman ES | Madina Press  |  2014 | Fiksi     | 75000| 2015-02-01     | | B0006     | Pemrograman Basis Data | Abdul Kadir      | Andi Offset   |  2015 | Buku Teks | 67000| 2015-02-01     | +-----------+------------------------+------------------+---------------+-------+-----------+------+----------------+ 6 rows in set (0.00 sec)

Fungsi Agregat – Data Tabel Buku

Fungsi Agregat: COUNT

Fungsi COUNT Digunakan untuk menghitung jumlah record.

Contoh:

• Hitung jumlah record tabel buku

1 2 3 4 5 6 7 8

mysql> SELECT COUNT(*)     -> FROM buku; +----------+ | COUNT(*) | +----------+ |        6 | +----------+ 1 row in set (0.00 sec)

• Hitung jumlah record tabel buku dengan nama kolom jum_rec

1 2 3 4 5 6 7 8

mysql> SELECT COUNT(*) AS jum_rec     -> FROM buku; +---------+ | jum_rec | +---------+ |       6 | +---------+ 1 row in set (0.03 sec)

• Hitung jumlah record untuk tahun 2013

1 2 3 4 5 6 7 8 9

mysql> SELECT COUNT(*) AS jum_rec     -> FROM buku     -> WHERE tahun = 2013; +---------+ | jum_rec | +---------+ |       3 | +---------+ 1 row in set (0.03 sec)

Fungsi Agregat SUM

Fungsi SUM digunakan untuk menghitung total nilai dari kolom tertentu

Contoh:

• Hitung total harga

1 2 3 4 5 6 7 8

mysql> SELECT SUM(harga) AS total_harga     -> FROM buku; +-------------+ | total_harga | +-------------+ |      307000 | +-------------+ 1 row in set (0.03 sec)

• Hitung total harga untuk tahun 2013

1 2 3 4 5 6 7 8 9

mysql> SELECT SUM(harga) AS total_harga     -> FROM buku     -> WHERE tahun=2013; +-------------+ | total_harga | +-------------+ |      120000 | +-------------+ 1 row in set (0.00 sec)

Fungsi Agregat MAX

Fungsi Max Digunakan untuk menampikan nilai tertinggi dari suatu kolom

Contoh:

• Tampilkan harga tertinggi

1 2 3 4 5 6 7 8

mysql> SELECT MAX(harga) AS harga_tertingi     -> FROM buku; +----------------+ | harga_tertingi | +----------------+ |          75000 | +----------------+ 1 row in set (0.00 sec)

• Tampilkan harga tertinggi untuk tahun 2013

1 2 3 4 5 6 7 8 9

mysql> SELECT MAX(harga) AS harga_tertinggi     -> FROM buku     -> WHERE tahun=2013; +-----------------+ | harga_tertinggi | +-----------------+ |           50000 | +-----------------+ 1 row in set (0.00 sec)

Fungsi Agregat: MIN

Fungsi MIN Digunakan untuk menampikan nilai terendah dari suatu kolom

Contoh:

• Tampilkan harga terendah

1 2 3 4 5 6 7 8

mysql> SELECT MIN(harga) AS harga_terendah     -> FROM buku; +----------------+ | harga_terendah | +----------------+ |          30000 | +----------------+ 1 row in set (0.00 sec)

• Tampilkan harga terendah untuk tahun 2013

1 2 3 4 5 6 7 8 9

mysql> SELECT MIN(harga) AS harga_terendah     -> FROM buku     -> WHERE tahun=2013; +----------------+ | harga_terendah | +----------------+ |          30000 | +----------------+ 1 row in set (0.00 sec)

Fungsi Agregat AVG

Fungsi AVG Digunakan untuk menampikan nilai rata-rata dari suatu kolom

Contoh:

• Tampilkan harga rata-rata

1 2 3 4 5 6 7 8

mysql> SELECT AVG(harga) AS harga_rerata     -> FROM buku; +--------------+ | harga_rerata | +--------------+ |   51166.6667 | +--------------+ 1 row in set (0.00 sec)

• Tampilkan harga rata-rata untuk tahun 2013

1 2 3 4 5 6 7 8 9

mysql> SELECT AVG(harga) AS harga_rerata     -> FROM buku     -> WHERE tahun=2013; +--------------+ | harga_rerata | +--------------+ |   40000.0000 | +--------------+ 1 row in set (0.00 sec)

Sumber

• fairuzelsaid.wordpress.com, SQL: Fungsi Agregat