Mengetahui ID Record Terakhir MySQL Dengan PHP

Jika kita menetapkan suatu field AUTO INCREMENT misalnya sebuah ID, maka kita bisa mendapatkan ID terakhir saat melakukan proses INSERT atau UPDATE. Seperti pada contoh saat kita melakukan INSERT data pada tabel User pada artikel sebelumnya kita menetapkan field bernama id dengan tipe INTEGER AUTO INCREMENT maka kita bisa menambahkan script untuk mendapatkan ID terakhir dengan cara mengambail nilai yang disebut insert_id.

Berikut ini contoh script PHP untuk mengambil ID record terakhir saat INSERT tabel MySQL seperti pada artikel sebelumnya tentang insert data MySQL dengan PHP.

1.Mengetahui ID Record Terakhir MySQL dengan MySQL Extension

 <?php   
 $dbHost = "localhost";   
 $dbUser = "root";   
 $dbPass = "123";   
 $dbName = 'MySQLDB';   

 // membuat koneksi mysql   
 $conn = mysql_connect($dbHost, $dbUser, $dbPass, $dbName);   

 // Mengecek koneksi mysql   
 if (!$conn) die("Koneksi Gagal: " . mysql_error());   
 else echo "Koneksi MySQL Berhasil ...<br/>";    

 //membuat koneksi database    
 $dbSelected = mysql_select_db($dbName, $conn);    

 // Mengecek koneksi database   
 if (!$dbSelected) die ('Koneksi Gatabase Gagal: ' . mysql_error());   
 else echo "Koneksi Database Berhasil ...<br/>";    

 //menentukan data yang akan diinsertkan   
 $nama = "Sabrina";   
 $password = "123456";   
 $email = "sabrina20080602@gmail.com";   

 //untuk field id dan tgl_reg tidak diisi karena otomatis akan diisi oleh database  

 // insert data ke tabel User   
 $sql = "INSERT INTO User (nama,password,email)   
 VALUES ('$nama', '$password', '$email')";   

 if (mysql_query($sql)) {   
  //mendapatkan id terakhir dengan fungsi mysql_insert_id  
  $last_id = mysql_insert_id($conn);  

  echo "Data baru berhasil dibuat dengan id= ".$last_id."<br/><br/>";   
  echo "<b>Detail Data :</b><br/>";   
  echo "Nama = ".$nama."<br/>";   
  echo "Password = ".$password."<br/>";   
  echo "Email = ".$email."<br/>";   
 }   
 else {   
  echo "Data baru gagal dibuat: " . mysql_error($conn);   
 }   

 //menutup koneksi mysql   
 mysql_close($conn);   
 ?>   

2.Mengetahui ID Record Terakhir MySQL dengan MySQLI Extension (Procedural)

 <?php   
 $dbHost = "localhost";   
 $dbUser = "root";   
 $dbPass = "123";   
 $dbName = "MySQLIDB1";   

 // membuat koneksi mysql   
 $conn = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName);   

 // mengecek koneksi mysql   
 if (!$conn) die("Koneksi Gagal: " . mysqli_connect_error());   
 else echo "Koneksi Database Berhasil ...<br/>";    

 //menentukan data yang akan diinsertkan   
 $nama = "Sabrina";   
 $password = "123456";   
 $email = "sabrina20080602@gmail.com";   

 //untuk field id dan tgl_reg tidak diisi karena otomatis akan diisi oleh database   

 // insert data ke tabel User   
 $sql = "INSERT INTO User (nama,password,email)   
 VALUES ('$nama', '$password', '$email')";   

 if (mysqli_query($conn, $sql)) {   
  //mendapatkan id terakhir dengan fungsi mysqli_insert_id  
  $last_id = mysqli_insert_id($conn);  

  echo "Data baru berhasil dibuat dengan id = ".$last_id." (dengan MySQLI Extension Procedural)<br/><br/>";   
  echo "<b>Detail Data :</b><br/>";   
  echo "Nama = ".$nama."<br/>";   
  echo "Password = ".$password."<br/>";   
  echo "Email = ".$email."<br/>";   
 } 
 else {   
  echo "Data baru gagal dibuat: " . mysqli_error($conn);   
 }   
 // menutup koneksi mysql   
 mysqli_close($conn);   
 ?>   

3.Mengetahui ID Record Terakhir MySQL dengan MySQLI Extension (Object Oriented)

 <?php   
 $dbHost = "localhost";   
 $dbUser = "root";   
 $dbPass = "123";   
 $dbName = "MySQLIDB2";   

 // membuat koneksi mysql   
 $conn = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName);   

 // mengecek koneksi mysql   
 if ($conn->connect_error) die("Koneksi Gagal: " . $conn->connect_error);   
 else echo "Koneksi Database Berhasil ...<br/>";    

 //menentukan data yang akan diinsertkan   
 $nama = "Sabrina";   
 $password = "123456";   
 $email = "sabrina20080602@gmail.com";   

 //untuk field id dan tgl_reg tidak diisi karena otomatis akan diisi oleh database   

 // insert data ke tabel User   
 $sql = "INSERT INTO User (nama,password,email)   
 VALUES ('$nama', '$password', '$email')";   

 if ($conn->query($sql) === TRUE) {   
  //mendapatkan id terakhir pada insert_id  
  $last_id = $conn->insert_id;  

  echo "Data baru berhasil dibuat dengan id = ".$last_id." (dengan MySQLI Object Oriented)<br/><br/>";   
  echo "<b>Detail Data :</b><br/>";   
  echo "Nama = ".$nama."<br/>";   
  echo "Password = ".$password."<br/>";   
  echo "Email = ".$email."<br/>";   
 } else {   
  echo "Data baru gagal dibuat: " . $conn->error;   
 }   

 //menutup koneksi  
 $conn->close();   
 ?>   

4.Mengetahui ID Record Terakhir MySQL dengan PDO

 <?php   
 $dbHost = "localhost";   
 $dbUser = "root";   
 $dbPass = "123";   
 $dbName = "MyPDODB";   

 try {   
  $conn = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUser, $dbPass);   

  // set the PDO error mode untuk exception   
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   

  //menentukan data yang akan diinsertkan   
  $nama = "Sabrina";   
  $password = "123456";   
  $email = "sabrina20080602@gmail.com";   

  //untuk field id dan tgl_reg tidak diisi karena otomatis akan diisi oleh database   

  // insert data ke tabel User   
  $sql = "INSERT INTO User (nama,password,email)   
  VALUES ('$nama', '$password', '$email')";   

  // gunakan exec() karena tidak mengembalikan result   
  $conn->exec($sql);   

  // mendapatkan id terakhir dengan fungsi lastInsertId()  
  $last_id = $conn->lastInsertId();  

  echo "Data baru berhasil dibuat dengan id = ".$last_id." (dengan PDO)<br/><br/>";   
  echo "<b>Detail Data :</b><br/>";   
  echo "Nama = ".$nama."<br/>";   
  echo "Password = ".$password."<br/>";   
  echo "Email = ".$email."<br/>";   
  }   
 catch(PDOException $e)   
  {   
  echo $sql . "<br>" . $e->getMessage();   
  }   

 // menutup koneksi   
 $conn = null;   
 ?>   

Referensi :

• http://www.w3schools.com/php/php_mysql_insert_lastid.asp

Mempercepat kinerja mysql

Lakukan pengecekan fasilitas query_cache Mysql kita dengan menuliskan :

mysql> SHOW VARIABLES LIKE   '%query_cache%';

Menghasilkan keterangan :
have_query_cache = yes
query_cache_type=on
query_cache_size = 0

query_cache_size bernilai 0 (nol) ,  sehingga meskipun query_cache aktif fitur query cache tidak akan berguna jika ukurannya masih Nol.

Edit file konfigurasi mysql  Anda (my.ini)
Pada bagian [mysql] tambahkan baris berikut :

set-variable=query_cache_size=64M

atau

mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1282
Message: Query cache failed to set size 39936;
         new query cache size is 0

mysql> SET GLOBAL query_cache_size = 41984;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 41984 |
+------------------+-------+
For the query cache to actually be able to hold any query results, its size must be set larger:

mysql> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| query_cache_size | 999424 |
+------------------+--------+
1 row in set (0.00 sec)

Restart mysql
Untuk membuktikan bahwa query_cache sudah berjalan ulangi langkah diatas dengan menuliskan :

SHOW VARIABLES LIKE   '%query_cache%';

Menghasilkan keterangan :
have_query_cache = yes
query_cache_type=on
query_cache_size = 67108864

Contoh-contoh query dalam MySQL

Pada kesempatan kali ini saya akan menjelaskan contoh-contoh query dalam MySQL. Tabel-tabel yang akan kita gunakan adalah sebagai berikut:

matakuliah(kodemk, namamk, sks), berisi daftar matakuliah yang ditawarkan
dosen(nip, nama), berisi daftar dosen pengampu kuliah
mahasiswa(nim, nama, dosenpembimbing), berisi daftar mahasiswa
kuliah(kodekuliah, kodemk, nip, thnakademik, semester), berisi daftar matakuliah dan dosen pengampu
peserta(nim, kodekuliah, nilai), berisi kuliah yang diikuti mahasiswa beserta nilainya dalam huruf
Dalam pembahasan kali ini diasumsikan:

matakuliah hanya ditawarkan sekali dalam setahun, yaitu pada semester 1 (ganjil) saja atau semester 2 (genap) saja dan tidak kedua-duanya.
bila pernah mengulang matakuliah, nilai yang diikutkan untuk perhitungan IP adalah nilai terakhir
matakuliah dikatakan lulus bila nilai yang diperoleh minimal ‘D’
Berikut ini adalah contoh-contoh query dalam MySQL:

1.      Menampilkan banyaknya SKS yang telah diselesaikan oleh masing-masing mahasiswa

a.       Bila matakuliah yang diulang semua masuk dalam perhitungan

SELECT nim, SUM( sks ) jumlahsks

FROM peserta, (

SELECT kodekuliah, kuliah.kodemk, sks

FROM kuliah, matakuliah

WHERE kuliah.kodemk = matakuliah.kodemk

) A

WHERE peserta.kodekuliah = A.kodekuliah

GROUP BY nim

Penjelasan:

Pada query ini kita menampilkan jumlah SKS yang telah diselesaikan oleh masing-masing mahasiswa bila matakuliah yang diulang semua masuk dalam perhitungan. Pertama-tama, kita melakukan join tabel kuliah dengan tabel matakuliah pada kondisi kodemk pada tabel kuliah sama dengan kodemk pada tabel matakuliah untuk mendapatkankodekuliah, kodemk dan sks.

…(

SELECT kodekuliah, kuliah.kodemk, sks

FROM kuliah, matakuliah

WHERE kuliah.kodemk = matakuliah.kodemk

) A

…

Lalu hasil join tersebut diberi alias A. Selanjutnya, tabel A tadi kita join-kan dengan tabel peserta pada kondisi kodekuliah pada tabel peserta sama dengan kodekuliah pada tabelA untuk mendapatkan nim dan sks yang selanjutnya kita menjumlahkan sks dengan SUM(sks) yang dikelompokkan berdasarkan nim menggunakan GROUP BY nim.

b.      Bila matakuliah yang diulang hanya yang sesuai dengan asumsi yang masuk dalam perhitungan

SELECT nim, SUM( sks ) totalsks

FROM (

SELECT DISTINCT nim, kodemk

FROM peserta, kuliah

WHERE peserta.kodekuliah = kuliah.kodekuliah

)P, matakuliah

WHERE P.kodemk = matakuliah.kodemk

GROUP BY nim

Penjelasan:

Pada query ini kita menampilkan jumlah SKS yang telah diselesaikan oleh masing-masing mahasiswa bila matakuliah yang diulang hanya dihitung sekali. Pertama-tama kita melakukan join tabel peserta dengan tabel kuliah pada kondisi kodekuliah pada tabel peserta sama dengan kodekuliah pada tabel kuliah untuk mendapatkan nim dan kodemk.

… (

SELECT DISTINCT nim, kodemk

FROM peserta, kuliah

WHERE peserta.kodekuliah = kuliah.kodekuliah

)P …

Kata DISTINCT setelah SELECT digunakan untuk menghilangkan pengulangan record yang mempunyai nim dan kodemk yang sama agar matakuliah yang diulang oleh seorang mahasiswa hanya muncul sekali. Hasil join tersebut diberi alias P. Lalu tabel P kita join-kan dengan tabel matakuliah pada kondisi kodemk pada tabel P sama dengan kodemk pada tabel matakuliah untuk mendapatkan nim dan sks yang dijumlahkan dengan SUM(sks) setelah dikelompokkan berdasarkan GROUP BY nim.

2.      Menampilkan data-data yang bisa untuk melakukan perhitungan IPK (berdasar asumsi di atas)

SELECT nim, kuliah.kodemk, nilai, sks

FROM peserta, matakuliah, kuliah

WHERE kuliah.kodekuliah = peserta.kodekuliah

AND kuliah.kodemk = matakuliah.kodemk

AND (

nim, kuliah.kodemk, thnakademik

)

IN (

SELECT nim, kodemk, MAX( thnakademik ) thnterakhir

FROM peserta, kuliah

WHERE kuliah.kodekuliah = peserta.kodekuliah

GROUP BY nim, kodemk

)

Penjelasan:

Pada query ini kita melakukan join pada tiga tabel, yaitu peserta, matakuliah dan kuliah dengan kondisi kodekuliah pada tabel kuliah sama dengan kodekuliah pada tabelpeserta dan kodemk pada tabel kuliah sama dengan kodemk pada tabel matakuliah. Setelah itu kita lakukan operasi intersection dengan query yang menampilkan tahun terakhir menyelesaikan suatu matakuliah.

…

AND (

nim, kuliah.kodemk, thnakademik

)

IN (

SELECT nim, kodemk, MAX( thnakademik ) thnterakhir

FROM peserta, kuliah

WHERE kuliah.kodekuliah = peserta.kodekuliah

GROUP BY nim, kodemk

)

Pada query untuk menampilkan tahun terakhir menyelesaikan suatu matakuliah, kita melakukan join pada tabel peserta dan tabel kuliah dengan kondisi kodekuliah pada tabel kuliahsama dengan kodekuliah pada tabel peserta. Lalu untuk mencari tahun terakhir digunakan fungsi MAX(thnakademik) setelah dikelompokkan berdasarkan nim dan kodemk denganGROUP BY nim, kodemk. Lalu operasi intersection dilakukan dengan memeriksa apakah record nim, kodemk, thnakademik dari hasil join tiga tabel ada pada hasil query untuk menampilkan tahun terakhir menyelesaikan suatu matakuliah.

3.      Menampilkan dosen yang pernah mengampu kelas matakuliah yang pesertanya tidak lebih dari 15 mahasiswa

SELECT DISTINCT nip

FROM kuliah, (

SELECT kodekuliah, COUNT( * ) jumlahpeserta

FROM peserta

GROUP

BY kodekuliah

) banyakpeserta

WHERE kuliah.kodekuliah = banyakpeserta.kodekuliah

AND jumlahpeserta <=15

Penjelasan:

Pada query ini kita akan menampilkan dosen yang pernah mengampu matakuliah yang pesertanya <= 15 mahasiswa. Awalnya menggunakan tabel peserta, kita hitung dahulu jumlah peserta dari setiap kuliah dengan menggunakan COUNT(*) setelah dikelompokkan berdasarkan kodekuliah dengan GROUP BY kodekuliah.

…(

SELECT kodekuliah, COUNT( * ) jumlahpeserta

FROM peserta

GROUP

BY kodekuliah

) banyakpeserta

…

Setelah itu query tersebut diberi alias banyakpeserta. Lalu pada query utama kita melakukan join pada tabel banyakpeserta dengan tabel kuliah pada kondisi kodekuliah pada tabelkuliah sama dengan kodekuliah pada tabel banyakpeserta serta menambahkan kondisi jumlahpeserta <= 15. Pada query utama kita menampilkan nip yang di dahului dengan kataDISTINCT agar nip yang ditampilkan tidak ada perulangan.

4.      Menampilkan nama mahasiswa yang telah lulus lebih dari 100 SKS

SELECT nama

FROM (

SELECT nim, SUM( sks ) jumlahsks

FROM peserta, kuliah, matakuliah

WHERE nilai <  'E'

AND kuliah.kodekuliah = peserta.kodekuliah

AND kuliah.kodemk = matakuliah.kodemk

GROUP

BY nim

) yanglulus, mahasiswa

WHERE jumlahsks >100

AND yanglulus.nim = mahasiswa.nim

Penjelasan:

Pada query ini kita akan menampilkan nama mahasiswa yang telah lulus > 100 SKS. Mula-mula kita menghitung jumlah SKS dari matakuliah yang lulus untuk masing-masing mahasiswa. Kita melakukan join pada tiga tabel peserta, kuliah dan matakuliah dengan kondisi kodekuliah pada tabel kuliah sama dengan kodekuliah pada tabel peserta dan kodemk pada tabel kuliah sama dengan kodemk pada tabel matakuliah disertai dengan kondisi nilai < ‘E’ yang berarti nilai >= ‘E’ tidak diikutkan. Lalu sks yang memenuhi kondisi dijumlahkan dengan SUM(sks) yang diberi alias jumlahsks setelah dikelompokkan berdasarkan nim dengan GROUP BY nim. Query tersebut diberi alias jumlahlulus.

…(

SELECT nim, SUM( sks ) jumlahsks

FROM peserta, kuliah, matakuliah

WHERE nilai <  'E'

AND kuliah.kodekuliah = peserta.kodekuliah

AND kuliah.kodemk = matakuliah.kodemk

GROUP

BY nim

) yanglulus …

Pada query utama kita melakukan join pada tabel yanglulus dengan mahasiswa dengan kondisi nim pada tabel yanglulus sama dengan nim pada tabel mahasiswa disertai kondisijumlahsks > 100 untuk menampilkan nama mahasiswa yang telah lulus > 100 SKS.

5.      Menampilkan banyaknya siswa yang telah lulus tugas akhir bila kode matakuliah untuk tugas akhir adalah ‘M0012’

SELECT COUNT( * ) lulusTA

FROM peserta, kuliah

WHERE nilai <  'E'

AND kuliah.kodekuliah = peserta.kodekuliah

AND kodemk =  'M0012'

Penjelasan:

Pada query kali ini kita akan menampilkan jumlah mahasiswa yang telah lulus tugas akhir bila kode matakuliah tugas akhir = ‘M0012’. Pertama-tama kita melakukan join pada tabel peserta dankuliah dengan kondisi kodekuliah pada tabel kuliah sama dengan kodekuliah pada tabel peserta untuk mendapatkan nilai dan kodemk. Lalu hasil join tersebut kita saring dengan kondisi nilai < ‘E’ dan kodemk = ‘M0012’ yang selanjutnya setelah disaring kita hitung dengan COUNT(*) dan diberi alias lulusTA untuk menghitung jumlah mahasiswa yang telah lulus tugas akhir.

6.      Menampilkan daftar nomor dan nama mahasiswa beserta IP  berdasar dua asumsi di atas

SELECT buatip.nim, nama, (

SUM( nilaisks ) / SUM( sks )

) ip

FROM mahasiswa, (

SELECT nim, kuliah.kodemk, (

( 69 – ASCII( UPPER( nilai ) ) ) * sks

) nilaisks, sks

FROM peserta, matakuliah, kuliah

WHERE kuliah.kodekuliah = peserta.kodekuliah

AND kuliah.kodemk = matakuliah.kodemk

AND (

nim, kuliah.kodemk, thnakademik

)

IN (

SELECT nim, kodemk, MAX( thnakademik ) thnterakhir

FROM peserta, kuliah

WHERE kuliah.kodekuliah = peserta.kodekuliah

GROUP BY nim, kodemk

)

)buatip

WHERE mahasiswa.nim = buatip.nim

GROUP BY nim

Penjelasan:

Pada query yang cukup panjang ini. kita akan menampilkan nomor dan nama mahasiswa beserta IP berdasar dua asumsi di atas. Pada awalnya kita membuat query untuk menampilkan nim,kodemk dan nilai yang sudah dikali sks. Query ini mirip seperti query yang dijelaskan pada nomor 2 dengan sedikit perubahan.

…(

SELECT nim, kuliah.kodemk, (

( 69 – ASCII( UPPER( nilai ) ) ) * sks

) nilaisks, sks

FROM peserta, matakuliah, kuliah

WHERE kuliah.kodekuliah = peserta.kodekuliah

AND kuliah.kodemk = matakuliah.kodemk

AND (

nim, kuliah.kodemk, thnakademik

)

IN (

SELECT nim, kodemk, MAX( thnakademik ) thnterakhir

FROM peserta, kuliah

WHERE kuliah.kodekuliah = peserta.kodekuliah

GROUP BY nim, kodemk

)

) buatip

Bagian yang ditebalkan merupakan perubahan yang dilakukan untuk query kali ini. Hasil query tersebut diberi alias buatip. Lalu kita melakukan join pada tabel buatip dan tabel nama dengan kondisi nim pada tabel mahasiswa sama dengan nim pada tabel buatip untuk mendapatkan nama. Selanjutnya untuk menghitung IP, kita menjumlahkan semua nilaisks dan membaginya dengan jumlah sks dengan SUM(nilaisks) / SUM(sks) setelah dikelompokkan menurut nim-nya dengan menggunakan GROUP BY nim.

7.      Menghitung IP bila asumsi kedua di atas diganti bahwa nilai yang dipakai untuk penentuan IP adalah nilai yang terbaik (dari serangkaian mengulang matakuliah)

SELECT nim, (

SUM( nilaimaxsks ) / SUM( sks )

)ip

FROM (

SELECT nim, (

( 69 – ASCII( UPPER( nilaimax ) ) ) * sks

)nilaimaxsks, sks

FROM matakuliah, (

SELECT nim, kodemk, MIN( nilai ) nilaimax

FROM peserta, kuliah

WHERE kuliah.kodekuliah = peserta.kodekuliah

GROUP BY nim, kodemk

) nilaiterbaik

WHERE nilaiterbaik.kodemk = matakuliah.kodemk

)buatip

GROUP BY nim

Penjelasan:

Pada query ini kita akan menampilkan IP dengan asumsi kedua diganti menjadi nilai yang dipakai untuk menghitung IP adalah nilai yang terbaik. Mula-mula kita mencari nilai terbaik pada setiap matakuliah untuk masing-masing mahasiswa. Kita melakukan join pada tabel peserta dengan tabel kuliah pada kondisi kodekuliah pada tabel kuliah sama dengan kodekuliah pada tabel peserta untuk mendapatkan kodemk dan nilai. Lalu kita mencari nilai terbaik dari setiap matakuliah untuk masing-masing mahasiswa dengan MIN(nilai) setelah dikelompokkan berdasarkan nim dan kodemk dengan menggunakan GROUP BY nim, kodemk karena nilai ascii ‘A’ < ‘B’ < ‘C’ < ‘D’ < ‘E’.

…(

SELECT nim, kodemk, MIN( nilai ) nilaimax

FROM peserta, kuliah

WHERE kuliah.kodekuliah = peserta.kodekuliah

GROUP BY nim, kodemk

) nilaiterbaik

…

Hasil query tersebut diberi alias nilaiterbaik. Setelah itu kita melakukan join tabel nilaiterbaik dengan tabel matakuliah pada kondisi kodemk pada tabel nilaiterbaik sama dengan kodemk pada tabel kuliah untuk mendapatkan sks.

Selanjutnya ditampilkan nim, nilaimaxsks yang merupakan nilaimax dikali sks serta sks.

…(

SELECT nim, (

( 69 – ASCII( UPPER( nilaimax ) ) ) * sks

)nilaimaxsks, sks

FROM matakuliah, (

SELECT nim, kodemk, MIN( nilai ) nilaimax

FROM peserta, kuliah

WHERE kuliah.kodekuliah = peserta.kodekuliah

GROUP BY nim, kodemk

) nilaiterbaik

WHERE nilaiterbaik.kodemk = matakuliah.kodemk

)buatip

…

Hasil query tersebut diberi alias buatip. Kemudian kita menghitung IP seperti query pada nomor 6 yaitu SUM(nilaimaxsks) / SUM(sks)  setelah dikelompokkan berdasarkan nimdengan menggunakan GROUP BY nim.

DASAR – DASAR SQL (Structured Query Language)

SQL adalah bahasa standar dalam basis data yang digunakan untuk melakukan manipulasi data (merupakan bahasa pemrograman/query standart yang digunakan untuk mengakses basis data relasional). Standardisasi bahasa ini dilakukan oleh ANSI (American National Standards Institution) tahun 86, 89, 92 dan 99, dimana tiap perubahan tahun dilakukan peningkatan kemampuan SQL.

Pada perkembangan saat ini standar yang paling banyak digunakan adalah standar ANSI 92. Hampir semua DBMS menggunakan SQL sebagai fasilitas untuk memanipulasi data seperti Oracle, SQLServer, MySQL, PostgreSQL, Foxpro dsb.

Meskipun awalnya hanya merupakan bahasa untuk memanipulasi data, pada perkembangannya SQL juga dapat digunakan untuk melakukan definisi data maupun control (security) terhadap data.
Sehingga bahasa Query ini dibagi menjadi 3 bagian :
1. DDL (Data Definition Language)
• Membuat database (CREATE DATABASE)
• Menghapus database (DROP DATABASE)
• Menciptakan tabel (CREATE TABLE)
• Menghapus tabel (DROP TABLE)
• Mengubah tabel (ALTER TABLE)

2. DML (Data Manipulation Language)
• Menambah record (INSERT INTO)
• Melihat isi tabel (SELECT)
• Menganti isi record (UPDATE)
• Menghapus record (DELETE)

3. DCL (Data Control Language): kelompok perintah yang dipakai untuk melakukan otorisasi terhadap pengaksesan data dan pengalokasian ruang.
Misal: suatu data bisa diakses si A, tetapi tidak bisa diakses oleh si B
• GRANT
• REVOKE
• COMMIT
• ROLLBACK

TIPE DATA
Setiap data memiliki tipe data. Tipe data menurut dalam SQL, antara lain:

Tipe Data Keterangan
CHAR atau CHARACTER – Menyatakan deretan karakter (huruf, bil, tanda baca, karakter khusus (ex:enter)) atau String
- Biasanya lebarnya ttt / tetap, mk biasanya dipakai sbg kunci primer (NIP, NIM),ttp tdk sbg kunci primer juga bisa (Jurusan, lebarnya 2 digit: MI, TI)
- Cara nulisnya diapit tanda petik bisa tunggal ’-’ atau ganda ”-”
VARCHAR – Untuk menyatakan string dengan panjang tidak tetap (nama, alamat, dll)
- Lebih boros penyimpanan di memori
- Cara nulisnya diapit tanda petik bisa tunggal ’-’ atau ganda ”-”
DECIMAL atau DEC Bilangan pecahan, ttp formatnya lebih jelas ex: 5,2 (—,–) shg tampilan dilayar lebih rapi dibanding float dan double (tergantung nilainya)
INTEGER atau INT – Menyatakan tipe data bilangan bulat, defaulnya max 11 karakter
- Bisa dipakai untuk operasi matematis
- Tdk operasi matematis juga bisa (ex:tinggi badan)
SMALLINT Menyatakan tipe data bilangan bulat yang jangkauannya lebih kecil dari pada INTEGER
FLOAT Tipe data bilangan real/pecahan, lebar dibelakang koma 17 karakter
DOUBLE Tipe data bilangan real/pecahan dengan presisi/teliti lebih tinggi dari pada FLOAT, lebar dibelakang koma sampai 30 digit
DATE Untuk menyatakan tanggal (th-bl-tgl)
TIME Untuk menyatakan waktu
ex: jam chek In, chek Out di hotel
TIMESTAMP Untuk menyatakan tanggal dan waktu
BLOB Untuk menyatakan data biner yang berguna untuk menyimpan gambar atau suara
AUTO-INCREMENT Untuk menyatakan data yang akan dinaikkan sebesar satu secara otomatis
BOOLEAN Untuk menyatakan tipe nalar yi benar atau salah
ENUM Untuk mendefinisikan data yang mempunyai kemungkinan nilai tetentu

KONSTANTA
Konstanta menyatakan nilai yang tetap. Beberapa contoh konstanta:
• Konstanta numerik : 145, -145, 27.5 (tanda pecahan pada suatu angka berupa titik)
• Konstanta string : ’Jl. Solo km.14, Kalasan, DIY’ (ditulis dengan awalan dan akhiran petik tunggal)
• Di dalam konstanta string, tanda \ diikuti dengan karakter tertentu mempunyai makna khusus. Lihat tabel di bawah ini

Penulisan Keterangan
Karakter dengan ASCII = 0 (karakter ke 0 dari tabel ASCII=256 karakter)
\’ Karakter petik tunggal
\” Karakter petik ganda
\b Karakter backspace
\n Newline (pindah baris)
\r Carriage return (karakter enter)
\t Tab
\\ Backslash

Jika ingin menuliskan string :Jum’at, maka penulisannya di mySql : ’Jum\’at’

EKSPRESI
Ekspresi adalah segala sesuatu yang menghasilkan nilai. Contoh:
0.1 * TOTAL_BAYAR
merupakan ekspresi untuk menghitung diskon sebesar 10%
Operator Aritmetika menurut prioritas pengerjaan dari yang tertinggi ke yang terendah

Operator Keterangan
* Perkalian
/ Pembagian
% Sisa pembagian (hanya digunakan untuk bilangan bulat)
+ Penjumlahan
- Pengurangan

Contoh:
1. Select 2+4*5/2;
hasilnya: 12
2. Select (2+4)*5/2;
hasilnya: 15
3. Select 5%2
hasilnya : 1
4. Select 5%3
hasilnya: 2

MySQL adalah
• database server, dapat menyimpan sejumlah database
• diakses oleh program client (MySQL Command Line Client, aplikasi Java, dll)
• akses dengan bahasa SQL (structured query language)





MySQL server dapat diaktifkan (dilihat) lewat
Start > Setting > ControlPanel > Administrative Tools > Services

Klik Start the service, sehingga kolom Status menjadi started

atau lewat:
Klik Start > All Program > AppServ > Control Server by Service > MySQL Start

Menjalankan program MySQL Command line Client
Klik Start > All Programs > AppServ > MySql Command Line Client
Anda akan mengakses MySQL dari client, dengan user root.

• root adalah superuser database server MySQL.
• root berkuasa dalam mengelola manajemen database.

masukkan password: root (default)
setelah dimasukkan password, akan muncul tampilan:
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)

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

mysql>

Keluar dari program MySQL Command line Client
salah satu dari ini:
mysql> EXIT
mysql> QUIT
mysql> \q

Catatan: semua perintah pada prompt mysql harus diakhiri dengan titik koma (;)

Melihat database:
mysql> SHOW DATABASES;
Hasil:
mysql> show databases;
+———-+
| Database |
+———-+
| mysql |
| test |
+———-+
2 rows in set (0.03 sec)

mysql>



Koneksi ke database
Sebelum menggunakan program MySQL, kita harus mengakses basisdata nya terlebih dahulu, dengan perintah

USE nama_basisdata

contoh:
mysql> use akademik;
tanggapan mysql:
Database changed

Melihat tabel:
mysql> SHOW TABLES;
Hasil:
mysql> show TABLES;
+——————–+
| Tables_in_akademik | |
+——————–+
| mhs |
| krs |
+——————–+
2 rows in set (0.03 sec)

mysql>

Melihat struktur tabel
mysql> describe mhs;
Hasil:
mysql> describe mhs;
+———+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+—————+——+—–+———+——-+
| nim | char(9) | NO | PRI | NULL | |
| nama | varchar(30) | YES | | NULL | |
| kelamin | enum(‘L’,'P’) | YES | | NULL | |
| tglahir | date | YES | | NULL | |
| jurusan | char(2) | YES | | NULL | |
| ipkum | decimal(3,2) | YES | | NULL | |
+———+—————+——+—–+———+——-+
6 rows in set (0.03 sec)



MENGENAL OPERASI DASAR SQL
1. DDL (Data Definition Language)
DDL merupakan bahasa yang digunakan untuk membuat atau memodifikasi database dan tabel,

Perintah DDL a.l:
a. CREATE Untuk membuat
Syntak :
- membuat database
CREATE DATABASE nama_database

- membuat tabel
CREATE TABLE nama_tabel ( field1 type_data1 (lebar_data1), field2 type_data2 (lebar_data2)

- Apabila akan menambahkan konstrain integritas PRIMARY KEY maka syntaknya adalah sbb :
CREATE TABLE nama_tabel ( field1 type_data1 (lebar_data1) PRIMARY KEY, field2 type_data2 (lebar_data2))

- Catatan : yang akan dijadikan primary key adalah field1
Misal :CREATE TABLE mahasiswa (nim char(10), nama char(30), jurusan char(2), ipk decimal(4,2))

- Mengatur agar data selalu diisi
Saat menciptakan tabel, pada field ditambah pemodifikasi NOT NULL

CREATE TABLE Barang (
Kode CHAR(3) NOT NULL,
Nama VARCHAR(20) NOT NULL,
Jenis CHAR NOT NULL ,
Jumlah INTEGER,
PRIMARY KEY(kode)
);

Maka pada saat mengisi ke tabel Barang field Kode, Nama, Jenis harus diisi, sedangkan field Jumlah tidak harus diisi (boleh diisi boleh tidak)
- Menentukan kunci primer
dengan menyertakan PRIMARY KEY.

- Membuat kunci komposit
Untuk kunci yang tersusun lebih dari satu kolom.

CREATE TABLE DetilJual (
NoNota CHAR(3),
Kode CHAR(3),
Jumlah INTEGER UNSIGNED,
PRIMARY KEY(NoNota, Kode));

Ctt: tipe Integer lebarnya boleh diisi boleh tidak, kalau diisi maksimum 11 karakter, kalau tidak defaulnya 11 karakter.
Tipe Integer Unsigned (bilangan bulat positif)

- Memberi Nilai Bawaan
Jika suatu kolom tidak diberi nilai, bisa dikehendaki untuk dapat diberi nilai bawaan.
Dengan kata kunci DEFAULT.

CREATE TABLE Barang (
Kode CHAR(3) NOT NULL PRIMARY KEY,
Nama VARCHAR(20) NOT NULL,
Jenis CHAR,
Jumlah INTEGER DEFAULT 0);

Ctt: kalau jumlah tidak diisi maka akan diisi defaultnya yi 0 atau:

CREATE TABLE Barang (
Kode CHAR(3) NOT NULL PRIMARY KEY,
Nama VARCHAR(20) NOT NULL,
Jenis CHAR DEFAULT MI,
Jumlah INTEGER DEFAULT 0);

kalau jenis tidak diisi maka akan diisi defaultnya yi MI (minuman)

- Memberi nilai yang unik
Sebenarnya kunci primer juga bertindak sebagi penjamin bahwa nilai suatu kolom akan bersifat unik (tdk kembar). Namun, kadangkala diperlukan agar suatu kolom yang tidak berfungsi sebagai kunci primer juga bersifat unik. Hal ini dapat dilakukan dengan memberikan kekangan UNIQUE pada kolom yang bersangkutan. Contoh

CREATE TABLE Barang (
Kode CHAR(3) NOT NULL PRIMARY KEY,
Nama VARCHAR(20) NOT NULL UNIQUE,
Jenis CHAR,
Jumlah INTEGER DEFAULT 0);

Penambahan kata UNIQUE pada kolom nama menyatakan bahwa tidak boleh ada nama barang yang sama

- Perbedaan PRIMARY KEY dan UNIQUE
PRIMARY KEY UNIQUE
Dalam sebuah tabel, keberadaannya hanya bisa satu kali Sejumlah kolom yang unik bisa didefinisikan
Kolom-kolom yang menjadi kunci primer tidak boleh berisi NULL Kolom yang didefinisikan sebagai kolom unik boleh berisi NULL sepanjang kolom tidak didefinisikan sebagai NOT NULL
b. DROP Untuk menghapus :
Syntak :
- menghapus database
DROP DATABASE nama_database

- menghapus tabel
DROP TABLE nama_table

c. ALTER Untuk memodifikasi tabel
Syntak :
- menambah field baru
ALTER TABLE nama_tabel ADD [COLUMN] field_baru type_data(lebar_data)
Misal :
ALTER TABLE mahasiswa ADD [COLUMN] alamat varchar(30)

- menghapus field
ALTER TABLE nama_tabel DROP [COLUMN] field_yang_dihapus
Misal :
ALTER TABLE mahasiswa DROP COLUMN alamat atau
ALTER TABLE mahasiswa DROP alamat

- mengedit / mengganti field
ALTER TABLE nama_tabel CHANGE [COLUMN] field_lama field_baru type_data(lebar_data)
Misal :
ALTER TABLE mahasiswa CHANGE nama nama_mhs char(40)

- Mengubah struktur tabel
mysql> alter table mhs modify nama varchar(35);
artinya: mengubah tipe field nama menjadi varchar(35)

2. DML (Data Manipulation Language)
DML merupakan bahasa untuk memanipulasi data (menambah, membaca/menampilkan, mengedit, menghapus)

Perintah DML a.l :
a. INSERT INTO: Untuk Menambah data baru/record/baris/row
Syntak :
INSERT INTO nama_tabel (field1, field2, …) VALUES (value1, value2,…).

Field dan value harus berjumlah sama dan masing-masing berpasangan, artinya : value1 akan diisikan ke field1, value2 akan diisikan ke field2,dst.
Misal:
create table mhs (
nim char(9) not null,
nama varchar(30),
kelamin enum(‘L’,'P’),
tglahir date,
jurusan char(2),
ipkum decimal(3,2),
primary key(nim)
);

INSERT INTO mhs (nim,nama,kelamin, tglahir,jurusan,ipkum)VALUES(‘05023562’,’TOTOK’,’L’,’1991-06-11’,’TI’, 2.45);
atau kalau semua field mau diisi, nama fieldnya tidak perlu ditulis

mysql> insert into mhs
values(’067890001′, ‘Agus Sugiarto’, ‘L’, ’1990-08-17′, ‘TI’, 2.56);

Cobalah untuk mengisi beberapa record lagi. Ingat! nim tidak boleh sama dan harus diisi (krn not null).

Menambah record tetapi hanya field nim, nama, dan kelamin yang diisi:
mysql> insert into mhs(nim, nama, kelamin) values(’067890003′, ‘Amir Hamzah’, ‘L’);
CREATE TABLE Barang (
Kode CHAR(3) NOT NULL,
Nama VARCHAR(20) NOT NULL,
Jenis CHAR(1) NOT NULL ,
Jumlah INTEGER,
PRIMARY KEY(kode)
);
INSERT INTO Barang (Kode, Nama, Jenis, Jumlah)
VALUES (’001′, ‘Beras’, ‘M’, 10);

Jika tidak ingin untuk memasukkan data ke kolom Jenis (Kode,Nama,Jenis: semua NOT NULL)

INSERT INTO Barang (Kode, Nama, Jenis, Jumlah)
VALUES (’001′, ‘Beras’, NULL, 10);

perintah ini akan ditolak: krn jenis tdk diisi padahal harus diisi/NOT NULL (tidak boleh kosong)
Tetapi kalau jenis diisi ’null’ pakai tanda petik, perintah ini akan diterima karena dianggap karakter

INSERT INTO Barang (Kode, Nama, Jenis, Jumlah)
VALUES (’001′, ‘Beras’, ’NULL’, 10);

Atau perintah di bawah ini, juga akan ditolak: krn nama, jenis tidak diisi (padahal not null : tdk boleh kosong)

INSERT INTO Barang (Kode, Jumlah)
VALUES (’006′, 12);

Maka yang benar:
INSERT INTO Barang (Kode, Nama, Jenis, Jumlah)
VALUES (’006′,’Buku’,’alat tulis’,12);

Tetapi kalau field Jumlah tidak diisi maka field jumlah tidak usah ditulis (krn jumlah boleh kosong/bukan NOT NULL)

INSERT INTO Barang (Kode, Nama, Jenis)
VALUES (’006′,’Buku’,’alat tulis’);

atau ditulis juga bisa

INSERT INTO Barang (Kode, Nama, Jenis,jumlah)
VALUES (’006′,’Buku’,’alat tulis’,null);

Dengan menggunakan sebuah INSERT, kita bisa menambahkan sejumlah baris yang berasal dari hasil qeury. Sekarang dicoba membuat tabel baru bernama barangbr dengan perintah sbb:

create table barangbr (
Kd_brg char(3),
nama varchar(10),
Jenis char(1);

dengan menggunakan INSERT, semua data barang, dengan jenis M (jenis ttt) dapat di transfer ke tabel barangbr. Caranya sbb:

INSERT INTO barangbr (Kd_brg, nama, Jenis) SELECT Kode, nama, Jenis FROM barang WHERE Jenis = ’M’;
b. UPDATE: Untuk MengEdit/mengubah data
Syntak :
UPDATE nama_tabel SET field1 = value1, field2 = value2,…[ WHERE kriteria ]

Ctt: WHERE tujuannya untuk mengurangu jumlah record yang ditampilkan, kalau tanpa WHERE semua record akan tampil

Misal :Untuk mengganti nama mahasiwa menjadi ANDI untuk nim 05023562
UPDATE mahasiswa SET nama = “ANDI” WHERE nim=”05023562”

mengubah sebuah kolom sebuah baris
UPDATE barang
SET nama = ‘beras delanggu’
WHERE Kode = ‘001’;

Mengubah beberapa kolom
UPDATE barang
SET Nama = ‘beras delanggu’,
Jumlah = 10
WHERE Kode = ‘001’;

Mengubah beberapa baris
UPDATE barang
SET Jumlah = 0
WHERE Jenis = ‘M’;

atau

UPDATE barang
SET Jumlah = 10
WHERE Kode = ‘001’ OR
Kode = ‘004’;

setelah dieksekusi perintah diatas maka data jumlah milik kode 001 serta 004 berubah menjadi 10

Mengubah semua baris (semua record, field jumlah isinya null semua)
UPDATE barang
SET Jumlah = null;

Misal dalam tabel barang ditambah atribut/field harga, kita bisa menaikkan harga 20% dari harga semula, dengan menggunakan bentuk ekspresi sbb:
UPDATE barang
SET harga = 1.2 * harga;
c. DELETE: Untuk Menghapus Data
Syntak :
DELETE FROM nama_tabel[WHERE Kriteria];
Misal :
- Untuk menghapus data mahasiswa yang mempunyai nim 05023562
DELETE FROM mahasiswa WHERE nim = “05023562”

- Menghpus sebuah baris
DELETE FROM Barang WHERE Kode = ‘001’;

akan menghapus baris yang kolom Kode bernilai ‘001’ (hanya satu karena kunci primer)

- Menghapus beberapa baris
DELETE FROM Barang WHERE Jenis = ‘M’;

akan menghapus baris yang kolom Jenis bernilai ‘M’ (bisa lebih dari satu)

- Menghapus semua baris
DELETE FROM Barang;
d. SELECT: untuk membaca atau menampilkan data
SELECT Daftar_field_yang_akan_ditampilkan
FROM nama_tabel
[WHERE kriteria_data_yang_akan_ditampilkan]

Misal :
- Menampilkan semua kolom semua baris
SELECT * FROM Barang;

- menampilkan nim dan nama semua mahasiswa (menampilkan kolom tertentu)
SELECT nim, nama FROM mahasiswa
SELECT Kode, Nama FROM Barang;

- menampilkan nim dan nama mahasiswa jurusan MI
SELECT nim, nama FROM mahasiswa WHERE jurusan = ‘MI’

- menampilkan semua field dari tabel mahasiswa jurusan MI (memilih baris tertentu)
SELECT * FROM mahasiswa WHERE jurusan = ‘MI’
SELECT * FROM Barang WHERE Jenis=’A’;

- Kalau hasil yang dikehendaki hanya berupa kemungkinan isi dari kolom jenis, maka bisa menyisipkan kata DISTINCT pada klausa SELECT

SELECT DISTINCT jenis FROM barang;
SELECT DISTINCT kota FROM pegawai;

mengetahui di kota mana saja pegawai bertempat tinggal (tdk semua record ditampilkan)

- Menampilkan jam sekarang
SELECT time(now());
- Menampilkan waktu sekarang
SELECT now();

- Menampilkan tahun sekarang
SELECT year(now());

- Menampilkan tanggal sekarang
SELECT date(now());

- Menampilkan hari sekarang
SELECT day(now());

- Operator Relasi
Beberapa operator relasi yang digunakan pada saat akan dibutuhakan suatu kriteria tertentu untuk menampilkan data adalah :
Operator Arti
= Sama dengan
> Lebih dari
>= Lebih dari atau sama dengan
< Kurang dari
<= Kurang dari atau sama dengan
<> Tidak Sama Dengan
LIKE Mengandung suatu kata/huruf tertentu
BETWEEN Rentang antara dua nilai

- untuk memperoleh barang yang jumlahnya antara 4 dan 11
SELECT * FROM Barang WHERE Jumlah >= 4 AND Jumlah <= 11;

dapat diganti dengan perintah
SELECT * FROM Barang WHERE Jumlah BETWEEN 4 AND 11;

SELECT nama,stok FROM barang WHERE Jumlah > 10;
SELECT nama,harga FROM barang where not (harga > 6600 AND harga < 3000);

Operator logika (operator Boolean)
Yang sering digunakan adalah : AND (dan/dua-duanya harus benar), OR (atau/salah satu benar, jawabannya benar) dan NOT (bukan/tidak)

- Menampilkan nim, nama dan alamat mahasiswa jurusan TI dan berjenis kelamin wanita

SELECT nim,nama,alamat FROM mahasiswa WHERE jurusan= ‘TI’ AND jenis_kel = ‘WANITA’;

SELECT * FROM barang WHERE jenis = ‘MI’ AND stok = 10;

SELECT * FROM barang WHERE jenis = ‘MI’ OR stok = 10;

SELECT nama,jenis FROM barang WHERE NOT jenis = ‘MI’;

Operator BETWEEN dan NOT BETWEEN
- untuk memperoleh barang yang jumlahnya antara 4 dan 11
SELECT * FROM Barang WHERE Jumlah BETWEEN 4 AND 11;

- Menampilkan nim, nama dan IPK mahasiswa yang mempunyai IPK 2,5 sampai 3,2
SELECT nim,nama,ipk FROM mahasiswa WHERE ipk BETWEEN 2.5 AND 3.2

- untuk memperoleh barang yang jumlahnya lebih kecil dari 4 dan lebih besar dr 11
SELECT * FROM Barang WHERE Jumlah NOT BETWEEN 4 AND 11;

Operator IN dan NOT IN
untuk melakukan pencocokan dengan salah satu yang termasuk didalam daftar

select * from mhs where jurusan = ‘MI’ or jurusan = ‘KA’;
untuk memperoleh mhs yang jurusannya MI atau KA.

Dapat diganti dengan
select * from mhs where jurusan IN(‘MI’,'KA’);

Bila ditambah NOT di depan IN akan memperoleh data diluar MI dan KA.

Menampilkan semua dari tabel barang yang jenis nya hanya MI dan SA
select * from barang where jenis IN(‘MI’,’SA’);

Menampilkan semua tabel barang yang jenisnya selain MI dan SA
select * from barang where jenis NOT IN(‘MI’,’SA’);
Operator LIKE dan NOT LIKE
- bermanfaat untuk mencari data semacam:
- barang apa saja yang mengandung huruf ‘be’
- perlu menyebutkan tanda wilcard berupa garis bawah ( _ ) atau persen (%).
• tanda _ berarti sebuah karakter apa saja
contoh:
a_i bisa berati ani, ali, abi, dll

• tanda % berarti cocok dengan karakter apa saja dan berapapun panjangnya
contoh:
- %a%  cocok dengan apa saja yang mengandung karakter a atau A.
- %a  cocok dengan yang berakhiran a atau A, hanya berlaku untuk yang bertipe VARCHAR
- a%  cocok dengan yang berawalan a atau A

contoh:
- menampilkan nama mahasiswa yang berawalan “PAR”
SELECT nama FROM mahasiswa WHERE nama LIKE “PAR%”

SELECT * FROM Barang WHERE Nama LIKE ‘b%’;
SELECT * FROM Barang WHERE Nama LIKE ‘%p’;
SELECT * FROM Barang WHERE Nama LIKE ‘%u%’;
SELECT * FROM Barang WHERE Nama LIKE ‘_u%’;
Kalau ditambah NOT berarti menampilkan selain kata yang berawalan b
SELECT * FROM Barang WHERE Nama NOT LIKE ‘b%’;

- Operator IS NULL dan IS NOT NULL
Jika ingin menampilkan data stok yang berisi NULL:
SELECT * FROM barang WHERE stok IS NULL;

Jika ingin menampilkan semua barang yang stok nya tidak berisi NULL, anda bisa memakai operator IS NOT NULL
SELECT * FROM barang WHERE stok IS NOT NULL;

Mengurutkan data dengan ORDER BY
- Menampilkan semua barang yang ada di tabel barang secara urut naik berdasarkan kode barang
SELECT * FROM barang ORDER BY harga;

- Mengurutkan barang berdasar nama secara urut naik (ascending)
SELECT kode, nama FROM barang ORDER BY nama;

- Mengurutkan secara urut turun (descending)
SELECT kode, nama FROM barang ORDER BY nama DESC;

- Mengurutkan barang menurut jenis dan nama
SELECT kode, jenis, nama FROM barang ORDER BY jenis, nama;

- Mengurutkan barang menurut jenis (descending) dan nama (ascending)
SELECT kode, jenis, nama FROM barang
ORDER BY jenis DESC, nama;

atau

SELECT kode, jenis, nama FROM barang
ORDER BY jenis , nama DESC;
- Mengurutkan barang menurut kolom 2 (jenis)
SELECT kode, jenis, nama FROM barang ORDER BY 2;

SELECT kode,jenis,nama FROM barang ORDER BY 2 DESC;
Mengelompokkan data dengan GROUP BY (hampir sama dengan DISTINCT)
Mengetahui jurusan apa saja dalam tabel mhs
SELECT jurusan FROM mhs GROUP BY jurusan;

hasilnya:
+———+
| jurusan |
+———+
| KA |
| MI |
| TI |
| TK |
———–

SELECT jenis FROM barang GROUP BY jenis;
Klausa HAVING (pengganti WHERE)
• Klausa HAVING terkait dengan klausa GROUP BY
• Gunanya untuk menentukan kondisi bagi GROUP BY
• Kelompok yang memenuhi HAVING saja yang akan dihasilkan

- Menampilkan jurusan dari tabel mhs berdasarkan kelompok jurusan, selain jurusan KA

SELECT jurusan FROM mhs group by jurusan
having jurusan <> ‘KA’;

hasilnya:
+———+
| jurusan |
+———+
| MI |
| TI |
| TK |
+———+

perintah ini dapat diganti dengan:

SELECT jurusan FROM mhs WHERE jurusan <> ‘KA’
GROUP BY jurusan;

- Menampilkan jenis dari tabel barang berdasarkan kelompok jenis, yang jenisnya MA

SELECT jenis FROM barang group by jenis
having jenis = ‘MA’;
Penggunaan Fungsi Agregat
Daftar Fungsi Agregat
Fungsi Agregat Keterangan
AVG Memperoleh nilai rata-rata
COUNT Menghitung cacah data
MAX Menghasilkan nilai terbesar
MIN Menghasilkan nilai terkecil
SUM Memperoleh penjumlahan data
Fungsi AVG
- Menghitung harga rata-rata barang
SELECT AVG(harga) FROM barang;

- Menghasilkan rata-rata ipkum
SELECT AVG(ipkum) FROM mhs;

- Menghitung harga rata-rata per jenis
SELECT jenis, AVG(harga) FROM barang GROUP BY jenis;

- Menghasilkan rata-rata ipkum dikelompokkan berdasar jurusan
SELECT jurusan, AVG(ipkum) FROM mhs GROUP BY jurusan;

- Menghasilkan rata-rata ipkum mhs yang mempunyai jurusan MI
SELECT AVG(ipkum) FROM mhs WHERE jurusan = ‘MI’;

- Menampilkan rata-rata harga dari tabel barang yang jenis MI
SELECT AVG(harga) FROM barang WHERE jenis = ‘MI’;

atau

SELECT AVG(harga) FROM barang GROUP BY jenis HAVING jenis = ‘MI’;
Fungsi COUNT
- Menghasilkan cacah baris dari tabel mhs. Kolom nim dalam COUNT dapat diganti dengan kolom apa saja (hanya 1 field) atau cukup ditulis * (sembarang).

SELECT COUNT(nim) FROM mhs;

SELECT jurusan, COUNT(*) FROM mhs GROUP BY jurusan;

menghasilkan:
+———+———-+
| jurusan | count(*) |
+———+———-+
| KA | 1 |
| MI | 2 |
| TI | 2 |
| TK | 1 |
+———+———-+

- catatan: kolom kedua bisa diganti nama kolomnya dengan klausa AS (untuk memberi nama lain terhadap suatu keluaran):

SELECT jurusan, COUNT(*) AS cacah FROM mhs GROUP BY jurusan;

menghasilkan:
+———+———-+
| jurusan | cacah |
+———+———-+
| KA | 1 |
| MI | 2 |
| TI | 2 |
| TK | 1 |
+———+———-+

- Menghitung cacah/jml barang per jenis, khusus untuk yang jumlahnya lebih dari 2

SELECT jenis, COUNT(*) FROM barang GROUP BY jenis HAVING COUNT(jenis) > 2;

- Menghitung cacah jenis yang tertera dalam tabel barang
SELECT COUNT(DISTINCT jenis) FROM barang;

Fungsi MAX
Untuk memperoleh nilai terbesar
- Menampilkan harga terbesar
SELECT MAX(harga) FROM barang;
SELECT nama, MAX(harga) FROM barang;

- Menampilkan harga terbesar per jenis
SELECT jenis, MAX(harga) FROM barang GROUP BY jenis;
SELECT jenis, nama, MAX(harga) FROM barang GROUP BY jenis;

Fungsi MIN
Untuk memperoleh nilai yang terkecil
- Menampilkan harga terkecil
SELECT MIN(harga) FROM barang;
SELECT nama, MIN(harga) FROM barang;

- Menampilkan harga terkecil per jenis
SELECT jenis, MIN(harga) FROM barang GROUP BY jenis;
SELECT jenis, nama, MIN(harga) FROM barang GROUP BY jenis;

Fungsi SUM
Untuk melakukan penjumlahan data
- Menghasilkan jumlah seluruh ipkum
SELECT SUM(ipkum) FROM mhs;

- Menampilkan seluruh stok barang
SELECT SUM(stok) FROM barang;

- Menampilkan total stok untuk setiap jenis
SELECT jenis, SUM(stok) FROM barang GROUP BY jenis;

Klausa LIMIT
untuk membatasi jumlah baris yang ditampilkan,
- SELECT * FROM mhs LIMIT 3;
hanya menampilkan 3 record dari atas

- SELECT nama, harga FROM barang LIMIT 4;
Penggunaan Ekspresi
SELECT harga FROM barang;

- Misal harga diskon 10%
SELECT nama,harga * 0.1 FROM barang;

- berarti harga yang di bayarkan 90%
SELECT nama,harga * 0.9 AS hargabr FROM barang;
SELECT nama,harga * 0.9 AS hargabr FROM barang where Kd_brg=’B07’;

Ctt:kolom harga diberi judul hargabr

- Untuk melihat kekayaan per barang
SELECT harga * stok FROM barang;

- Melihat total kekayaan
SELECT sum(harga * stok) FROM barang;

Fungsi-fungsi dalam Pernyataan SQL

Setiap bahasa pemrograman biasanya memiliki fungsi-fungsi yang memudahkan pengguna-nya, tidak terkecuali SQL. Beberapa fungsi yang tertulis dalam artikel ini merupakan fungsi standar dari bahasa pemrograman SQL.

1. Concatenation

Syntax:
value1 || value2
Digunakan untuk menggabungkan dua nilai karakter (nilai kolom, literal, parameter, atau nilai hasil perhitungan) menjadi data bertipe string. Pernyataan berikut ini mengkombinasikan nilai-nilai kolom dengan karakter literal.

SELECT
  LastName || ", " || FirstName
FROM Names
Fungsi ini tidak dapat digunakan dengan kolom bertipe Memo atau Blob.

2. LOWER

Syntax:
LOWER(column_reference)
Mengkonversi semua nilai karakter (nilai kolom, literal, parameter, atau nilai hasil perhitungan) menjadi huruf kecil. Database seperti SQL Server memakai pernyataan LOWERCASE. Pernyataan SELECT berikut mengubah nilai-nilai dalam kolom Name menjadi huruf kecil.

SELECT
  LOWER(Name)
FROM Country
Perubahan nilai akibat fungsi ini tidak akan disimpan jika diberlakukan dalam pernyataan SELECT. Jika dinyatakan sebagai nilai kondisi (update_atom) dalam pernyataan UPDATE, perubahan nilai tersebut akan disimpan. Fungsi LOWER hanya dapat digunakan dengan kolom bertipe karakter atau literal dan tidak dapat digunakan untuk kolom bertipe Memo dan Blob.

3. UPPER

Syntax:
UPPER(column_reference)
Merupakan kebalikan dari fungsi LOWER, yaitu mengkonversi semua nilai karakter (nilai kolom, literal, parameter, atau nilai hasil perhitungan) menjadi huruf kapital. Sebagai contoh, pernyataan berikut akan menyaring data berdasarkan nilai kolom Name yang dikonversi ke dalam huruf kapital yang diawali karakter PE (nilai lateral).

SELECT
  Name,
  Capital,
  Continent
FROM Country
WHERE UPPER(Name) LIKE UPPER("Pe%")
Sama seperti fungsi LOWER, perubahan nilai akibat fungsi ini tidak akan disimpan jika diakukan dalam pernyataan SELECT. Jika dinyatakan sebagai nilai kondisi dalam pernyataan UPDATE, perubahan nilai tersebut akan disimpan.

4. SUBSTRING

Syntax:
SUBSTRING(column_reference FROM start_index [FOR length]
Mengutip substring dari nilai karakter (nilai kolom, literal, parameter, atau nilai hasil perhitungan) yang dinyatakan dalam kolom yang dimaksud (colom_reference dalam syntax di atas). Klausa FROM adalah posisi karakter untuk memulai pengutipan substring. Karakter pertama nilai sumber diberi indeks 1 (satu). Dan kata kunci FOR menyatakan banyaknya substring yang ingin dikutip. Jika kata kunci FOR tidak dideklarasikan maka substring dimulai dari posisi yang dinyatakan oleh kata kunci FROM sampai akhir string. Contoh berikut mengutip tiga huruf dimulai dari huruf kedua, hasilnya adalah “BCD” jika nilai asalnya adalah “ABCDE” dan dimasukkan dalam kolom lietral bernama Sub

SELECT
  SUBSTRING("ABCDE" FROM 2 FOR 3) AS Sub
FROM Country
Dan pernyataan SELECT berikut hanya akan menampilkan huruf kedua dan seterusnya dari kolom Name, artinya nilai “Belgium” dalam kolom tersebut akan ditampilkan sebagai “elgium”.

SELECT
  SUBSTRING(Name FROM 2)
FROM Country
Perubahan nilai akibat fungsi ini tidak akan disimpan jika diberlakukan dalam pernyataan SELECT. Jika dinyatakan sebagai nilai kondisi (update_atom) dalam pernyataan UPDATE, perubahan nilai tersebut akan disimpan. Fungsi SUBSTRING hanya dapat digunakan dengan kolom bertipe karakter atau literal dan tidak dapat digunakan untuk kolom bertipe Memo dan Blob.

5. TRIM

Syntax :
TRIM([LEADING|TRAILING|BOTH] [trimmed_char] FROM column_reference)
Untuk menghapus karakter pertama, terakhir atau kedua-duanya (pertama dan terkahir) dari nilai karakter (nilai kolom, literal, parameter, atau nilai hasil perhitungan). Berkut ini deskripsi parameter yang ada dalam fungsi TRIM :

Parameter Deskripsi
LEADING Menghapus karakter terakhir sisi kiri string
TRAILING Menghapus karakter terakhir sisi kanan string
BOTH Menghapus karakter terakhir kedua sisi string
Case-sensitivity (huruf besar/kecil mempunyai nilai berbeda) berlaku dalam pernyataan parameter tersebut. FROM merupakan nilai karakter sumber yang akan dihapus. Tabel berikut menunjukkan hasil dari variasi deklarasi fungsi TRIM.

TRIM syntax Hasil
TRIM(LEADING “_” FROM “_ABC_”) “ABC_”
TRIM(TRAILING “_” FROM “_ABC_”) “_ABC”
TRIM(BOTH “_” FROM “_ABC_”) “ABC”
TRIM(BOTH “A” FROM “ABC”) “BC”
Perubahan nilai akibat fungsi ini tidak akan disimpan jika diberlakukan dalam pernyataan SELECT. Jika dinyatakan sebagai nilai kondisi (update_atom) dalam pernyataan UPDATE, perubahan nilai tersebut akan disimpan. Fungsi TRIM hanya dapat digunakan dengan kolom bertipe karakter atau literal dan tidak dapat digunakan untuk kolom bertipe Memo dan Blob.

6. AVG

Syntax:
AVG([ALL] column_reference | DISTINCT column_reference)
Menghitung rerata dari nilai numerik (nilai kolom, literal, parameter, atau nilai hasil perhitungan). Nilai 0 (nol) dalam suatu kolom tetap diperhitungkan, sedangkan jika tidak ada nilai (kosong bukan nol) maka fungsi AVG akan mengabaikannya. Kombinasi nilai kolom dan suatu konstanta dapat digunakan dalam fungsi AVG.

SELECT
  AVG(ItemsTotal),
  AVG(ItemsTotal * 0.0825) AS AverageTax
FROM Orders
ALL untuk mendapatkan nilai rerata semua record sedang DISTINCT akan mengabaikan nilai yang sama suatu kolom pada saat perhitungan nilai rerata. Jika kata kunci DISTINCT tidak dinyatakan, ALL adalah kata kunci default. Jika memakai klausa GROUP BY, fungsi AVG akan menghitung satu nilai untuk tiap kelompok. Nilai tersebut merupakan pengumpulan kolom untuk semua baris pada tiap kelompok. Pernyataan berikut mengelompokkan nilai rerata kolom ItemsTotal dari tabel Orders, menghasilkan subtotal untuk tiap perusahaan dalam tabel Company.

SELECT
  C."Company",
  AVG(O."ItemsTotal") AS Average
FROM "Customer.db" C, "Orders.db" O
WHERE (C."CustNo" = O."CustNo")
GROUP BY C."Company"
ORDER BY C."Company"
Fungsi AVG hanya beroperasi pad nilai numerik. Untuk nilai non-numerik, sebelum menggunakan fungsi ini, gunakan fungsi CAST untuk mengkonversi kolom menjadi bertipe numerik. Dan fungsi ini tidak dapat digunakan dengan kolom betipe Memo dan Blob.

7. COUNT

Syntax:
COUNT(* | [ALL] column_reference | DISTINCT column_reference)
Untuk menghitung banyaknya record yang ditampilkan oleh pernyataan SELECT (tunggal atau multi query). Jika menambahkan klausa WHERE, fungsi COUNT menampilkan banyaknya record yang tersaring.

SELECT COUNT(*)
FROM "Clients.dbf"
WHERE State = "CA"
8. MAX

Syntax:
MAX([ALL] column_reference | DISTINCT column_reference)
Menghitung nilai terbesar dari nilai (nilai kolom, literal, parameter, atau nilai hasil perhitungan) yang menggunakan dataset. Dataset dapat berupa tabel, suatu dataset yang disaring, atau kelompok logika yang dihasilkan oleh klausa GROUP BY. Fungsi MAX memberikan nilai kosong untuk kolom yang bernilai kosong (bukan nol). ALL untuk mendapatkan nilai tertinggi semua baris sedang DISTINCT akan mengabaikan nilai yang sama suatu kolom pada saat menghitung nilai tertinggi. Jika kata kunci DISTINCT tidak dinyatakan, ALL adalah kata kunci default. Kombinasi nilai kolom dan suatu konstanta dapat digunakan dalam fungsi MAX.

SELECT
  MAX(ItemsTotal),
  MAX(ItemsTotal * 0.0825) AS HighestTax
FROM Orders
Jika menggunakan kolom bertipe numerik, nilai yang dihasilkan bertipe sama dengan tipe kolom tersebut (seperti Integer atau Float) . Fungsi MAX tidak dapat digunakan dengan kolom bertipe Memo atau Blob.

SELECT
  C."Company",
  MAX(O."ItemsTotal") AS Biggest
FROM "Customer.db" C, "Orders.db" O
WHERE (C."CustNo" = O."CustNo")
GROUP BY C."Company"
ORDER BY C."Company"
9. MIN

Syntax:
MIN([ALL] column_reference | DISTINCT column_reference)
Fungsi MIN merupakan kebalikan dari fungsi MAX yaitu menghitung nilai terkecil dari nilai (nilai kolom, literal, parameter, atau nilai hasil perhitungan) yang menggunakan dataset. Deskripsi lainnya hampir sama dengan fungsi MAX.

SELECT
  MIN(ItemsTotal)
FROM Orders
10. SUM

Syntax:
SUM([ALL] column_reference | DISTINCT column_reference)
Menghitung total dari nilai (nilai kolom, literal, parameter, atau nilai hasil perhitungan) yang menggunakan dataset. Deskripsi lainnya hampir sama dengan fungsi MAX. Contoh penulisan syntax-nya adalah :

SELECT
  SUM(Salary + (Sarary * 0.1))
FROM "Employee.db"
11. CAST

Syntax:
CAST(column_reference AS data_type)
Mengkonversi kolom tertentu dalam tipe data tertentu. CAST dapat diterapkan juga untuk nilai literal dan nilai hasil perhitungan. Fungsi ini dapat digunakan dalam daftar kolom pernyataan SELECT, predikat untuk klausa WHERE dan modifikasi nilai dalam pernyataan UPDATE. Parameter data_tipe yang dapat digunakan adalah Char, Integer, Numeric dan seterusnya, sedangkan yang tidak dapat dipakai adalah Blob, Memo dan Byte. Pernyataan berikut mengkonversi nilai kolom bertipe TimeStamp ke tipe Date

SELECT
  CAST(SaleDate AS DATE)
FROM ORDERS
Kombinasi fungsi CAST dengan fungsi yang lain dapat digunakan selama tipe datanya sesuai, seperti contoh berikut.

SELECT
  SaleDate,
  SUBSTRING(CAST(CAST(SaleDate AS DATE) AS CHAR(10)) FROM 1 FOR 1)
FROM Orders
Perubahan nilai akibat fungsi ini tidak akan disimpan jika diberlakukan dalam pernyataan SELECT. Jika dinyatakan sebagai nilai kondisi (update_atom) dalam pernyataan UPDATE, perubahan nilai tersebut akan disimpan.

12. EXTRACT

Syntax:
EXTRACT(extract_field FROM column_reference)
Untuk mendapatkan kolom tahun, bulan dan hari dari kolom bertipe Date atau TimeStamp. Jika kolom yang dideklarasikan tidak mempunyai nilai maka hasilnya adalah kosong (bukan nol). Jika nilainya tidak kosong, fungsi EXTRACT akan menyatakan nilai elemen tanggal tertentu sebagai data bertipe SmallInt. Parameter extract_fields yang diperbolehkan adalah : YEAR, MONTH, DAY, HOUR, MINUTE, atau SECOND. Parameter YEAR, MONTH, dan DAY hanya digunakan dengan kolom bertipe Date dan TimeStamp. Sedangkan parameter HOUR, MINUTE, dan SECOND hanya digunakan dengan kolom bertipe TimeStamp dan Time.

SELECT
  SaleDate,
  EXTRACT(YEAR FROM saledate) AS YY,
  EXTRACT(MONTH FROM saledate) AS MM,
  EXTRACT(DAY FROM saledate) AS DD
FROM Orders
Pernyataan berikut menggunakan kolom DOB (berisi tanggal lahir) yang menyaring record dimana tanggal lahirnya adalah bulan MEI (dinyatakan dengan angka 5).

SELECT
  DOB,
  LastName,
  FirstName
FROM People
WHERE (EXTRACT(MONTH FROM DOB) = 5)
Fungsi EXTRACT hanya beroperasi pada data bertipe Date, Time dan TimeStamp. Gunakan fungsi CAST untuk mengkonversi tipe data menjadi data bertipe waktu.

Fungsi SQL GROUP

Fungsi kelompok yang built-in fungsi SQL yang beroperasi pada kelompok baris dan kembali satu nilai untuk seluruh kelompok. Fungsi-fungsi ini adalah: COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): Fungsi ini mengembalikan jumlah baris dalam tabel yang memenuhi kondisi yang ditentukan dalam kondisi WHERE. Jika kondisi MANA tidak ditentukan, maka permintaan mengembalikan jumlah baris dalam tabel.

Sebagai Contoh: Jika Anda ingin jumlah karyawan di departemen tertentu, permintaan akan:

SELECT COUNT (*) FROM employee
WHERE dept = 'Electronics';

Output akan '2' baris.

Jika Anda ingin jumlah karyawan di semua departemen, permintaan akan mengambil bentuk:

SELECT COUNT (*) FROM employee;

Output akan '5' baris.


SQL DISTINCT (): Fungsi ini digunakan untuk memilih baris yang berbeda.

Sebagai Contoh: Jika Anda ingin memilih semua nama departemen yang berbeda dari meja kerja, permintaan akan:

SELECT DISTINCT dept FROM employee;

Untuk mendapatkan jumlah karyawan dengan nama yang unik, permintaan akan:

SELECT COUNT (DISTINCT name) FROM employee;


SQL MAX (): Fungsi ini digunakan untuk mendapatkan nilai maksimum dari kolom.

Untuk mendapatkan gaji maksimum ditarik oleh seorang karyawan, permintaan akan:

SELECT MAX (salary) FROM employee;


SQL MIN (): Fungsi ini digunakan untuk mendapatkan nilai minimum dari kolom.

Untuk mendapatkan gaji minimum yang ditarik oleh seorang karyawan, ia permintaan akan:

SELECT MIN (salary) FROM employee;


SQL AVG (): Fungsi ini digunakan untuk mendapatkan nilai rata-rata kolom numerik.

Untuk mendapatkan gaji rata-rata, permintaan akan

SELECT AVG (salary) FROM employee;


SQL SUM (): Fungsi ini digunakan untuk mendapatkan jumlah kolom numerik

Untuk mendapatkan total gaji yang diberikan kepada karyawan,

SELECT SUM (salary) FROM employee;

Syntax untuk mendapatkan bulan dari date denga SQL

SELECT MONTH( Tanggal ) AS `Tanggal Masuk`
FROM Nama_Tabel
// untuk mendapatkan nama bulan
SELECT MONTHNAME( Tanggal ) AS `Tanggal Masuk`
FROM Nama_Tabel