Menggunakan Alter di PostgreSQL

Penggunaan Alter sangat berperan penting dalam pembuatan database, alter ini mempunyai banyak fungsi beberapa diantaranya bisa menambah menghapus, bahkan mengubah data atau attribute tabel, menambahkan primary key, menambahkan foreign key dan masih banyak lainnya.Untuk lebih jelasnya pelajari query dibawah ini :

PostgreSQL Alter Table Add Column ( menambahkan colom di tabel)

ALTER TABLE nama_tabelnya ADD nama_kolom_baru varchar (25);

PostgreSQL Alter Table Add Primary Key ( menambahkan primary key )

ALTER TABLE nama_tabelnya ADD PRIMARY KEY (nama_attribut_yg_ diijadikan_p_key);

PostgreSQL Alter Table Add Foreign Key ( menambahkan foreign key )

ALTER TABLE nama_tabelnya ADD FOREIGN KEY (nama_attribut_yg_ diijadikan_p_key) REFERENCES nama_tabel_yg_direference(nama_attribut_pd_tabel_yg _diriferrence);

PostgreSQL Alter Table Change Column Type ( mengubah type data suatu colom )

ALTER TABLE nama_tabelnya ALTER COLUMN nama_colom TYPE char(25);

// pada contoh menganti tipe data “char” valuenya (25)

PostgreSQL Alter Table Drop Column ( menghapus kolom tabel )

ALTER TABLE nama_tabel DROP COLUMN nama_kolom_yg_dihapus;

PostgreSQL Alter Table Drop Constraint ( menghapus constraint )

ALTER TABLE nama_tabel DROP CONSTRAINT nama_constraint_yg_akan_dihpus;

PostgreSQL Alter Table Drop Primary Key ( menghapus primary key )

ALTER TABLE nama_tabel DROP CONSTRAINT nama_constraint_primary_key;

PostgreSQL Alter Table Drop Foreign Key ( menghapus foreign key )

ALTER TABLE nama_tabel DROP CONSTRAINT nama_constraint_foreign_key;

PostgreSQL Rename Table ( mengubah nama tabel )

ALTER TABLE nama_tabel_awal RENAME TO nama_tabel_baru;

PostgreSQL Rename Column ( mengubah nama field / colom )

ALTER TABLE nama_tabel RENAME COLUMN nama_column_awal TO nama_kolom_baru;

Semoga_Bermanfaat ;

Read More

Membuat auto increment di postgree

Pada tulisan yang akan saya bahas kali ini sebenarnya sangat sepele sekali, yaitu membuat auto increment di postgreSQL. Mungkin buat para master yang sudah tau cara membuatnya, sudah bukan masalah lagi. Tetapi buat para newbie yang baru belajar (Postgree) mungkin ini merupakan hal yang lumayan penting untuk dipelajari, karena hampir semua program menggunakan auto increment, untuk membuat nomer atau kode yang unik dari program secara otomatis. contohnya program pendaftaran mahasiswa baru disebuah universitas, setiap mahasiswa baru yang mendaftar akan di diberikan nomer urutan yang unik sesuai urutannya. Itulah salah satu fungsi dari auto increment. Untuk menambahkan auto increment di table pada database di mysql sangat berbeda dengan postgreSQL.

MySQL

Jika menggunakan mysql kita tinggal menambahkan sedikit code auto increment, contoh :

CREATE table contoh ( no_urutan int NOT NULL auto_increment,

Nama varchar(30),

Alamat varchar(30),

Primary key(no_urutan));

untuk memulai urutan angka pertama tambahkan

ALTER TABLE contoh AUTO_INCREMENT = 4; // ex: memulai urutan angka dari 4;

PostgreSQL

Untuk menambahkan auto increment di postgre kita bisa menggunakan type data serial. dengan menggunakan type data serial kita sudah menambahkan auto increment di postgree.

CREATE TABLE contoh(

nomer serial NOT NULL,

nama character varying(30),

alamat character varying(30),

CONSTRAINT contoh_pkey PRIMARY KEY (nomer))

WITH (

OIDS=FALSE

);

ALTER TABLE contoh OWNER TO postgres;

Atau anda juga bisa menggunakan ini.

CREATE SEQUENCE mulai; //membuat SEQUENCE dengan nama “mulai”

ALTER SEQUENCE mulai RESTART WITH 100; //memasukkan nilai SEQUENCE 100;

Selanjutnya kita buat tabelnya ;

CREATE TABLE apples (

id INT4 DEFAULT nextval('mulai') NOT NULL,

price NUMERIC

);

nextval('mulai') //memanggil sequence yang telah dibuat tadi “mulai” yang mempunyai value 100, jadi pada id ini akan bertambah secara otomatis mulai dari angka 100.


semoga bermanfaat ...

Read More

Membuat Functions di PostgreSQL

Kali ini aku menuliskan cara membuat function di PostgreSQL, pada contoh yang akan saya buat ini terdapat saya akan membuat 6 tabel, diantaranya "mahasiswa", "mata_kuliah", "nilai_semester", "nilai_sp", "nilai_upas", "nilai_upta" . langsung saja disimak .

perta kita buat tabel mahasiswa .

CREATE TABLE mahasiswa

(

  nim character varying(10) NOT NULL,

  nama character varying(30),

  prodi character varying(5),

  CONSTRAINT mahasiswa_pkey PRIMARY KEY (nim)

)

WITH (

  OIDS=FALSE);

ALTER TABLE mahasiswa OWNER TO postgres;

buat tabel mata_kuliah 

CREATE TABLE mata_kuliah(

  kdmtk character varying(5) NOT NULL,

  nm_mtk character varying(15),

  sks integer,

  smt integer,

  CONSTRAINT mata_kuliah_pkey PRIMARY KEY (kdmtk)

)

WITH (

  OIDS=FALSE

);

ALTER TABLE mata_kuliah OWNER TO postgres;

membuat tabel nilai_semester

CREATE TABLE nilai_semester(

  nim character varying(10) NOT NULL,

  kdmtk character varying(5),

  smt integer,

  tahun numeric,

  great character varying(1),

  CONSTRAINT nilai_semester_kdmtk_fkey FOREIGN KEY (kdmtk)

      REFERENCES mata_kuliah (kdmtk) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT nilai_semester_nim_fkey FOREIGN KEY (nim)

      REFERENCES mahasiswa (nim) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE

)

WITH (

  OIDS=FALSE

);

ALTER TABLE nilai_semester OWNER TO postgres;

membuat tabel nilai_sp

CREATE TABLE nilai_sp(

  nim character varying(10) NOT NULL,

  kdmtk character varying(5),

  smt integer,

  tahun numeric,

  great character varying(1),

  CONSTRAINT nilai_sp_kdmtk_fkey FOREIGN KEY (kdmtk)

      REFERENCES mata_kuliah (kdmtk) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT nilai_sp_nim_fkey FOREIGN KEY (nim)

      REFERENCES mahasiswa (nim) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE

)

WITH (

  OIDS=FALSE

);

ALTER TABLE nilai_sp OWNER TO postgres;

 membuat tabel nilai_upas

CREATE TABLE nilai_upas(

  nim character varying(10) NOT NULL,

  kdmtk character varying(5),

  smt integer,

  tahun numeric,

  great character varying(1),

  CONSTRAINT nilai_upas_kdmtk_fkey FOREIGN KEY (kdmtk)

      REFERENCES mata_kuliah (kdmtk) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT nilai_upas_nim_fkey FOREIGN KEY (nim)

      REFERENCES mahasiswa (nim) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE

)

WITH (

  OIDS=FALSE

);

ALTER TABLE nilai_upas OWNER TO postgres;

membuat tabel nilai_upta

CREATE TABLE nilai_upta(

  nim character varying(10) NOT NULL,

  kdmtk character varying(5),

  smt integer,

  tahun numeric,

  great character varying(1),

  CONSTRAINT nilai_upta_kdmtk_fkey FOREIGN KEY (kdmtk)

      REFERENCES mata_kuliah (kdmtk) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT nilai_upta_nim_fkey FOREIGN KEY (nim)

      REFERENCES mahasiswa (nim) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE

)

WITH (

  OIDS=FALSE

);

ALTER TABLE nilai_upta OWNER TO postgres;

Pada Data Definition Language (DDL) yang telah kita buat tadi, sudah mempunyai relasi antar tabel. Contoh pada tabel “nilai_semester” terdapat  query.

CONSTRAINT nilai_semester_kdmtk_fkey FOREIGN KEY (kdmtk)

      REFERENCES mata_kuliah (kdmtk) MATCH SIMPLE

Pada query diatas mempunyai foreign key pada attribute (kdmtk) yang mereferences attribute ( kdmtk) di tabel mata_kuliah. jadi jika kita mengisi data pada tabel “nilai_semester” tetapi pada attribute(kdmtk)  pada tabel “mata_kuliah” belum ada atau belum ditambahkan maka terjadi error / gagal menambahkan data..maka dengan relasi ini user tidak bisa menambahkan data sembarangan. Itu salah satu fungsi daripada relasi antar tabel .

Oke setelah kita selesai membuat tabel, sekarang kita memasukkan data ke tabel yang telah kita buat tadi.

memasukkan data ke tabel nilai_semester.

INSERT INTO nilai_semester(

            nim, kdmtk, smt, tahun, great)

    VALUES (2009420028, 'TI01', 3, 2008, 'B'),

    (2009420028, 'TI02', 3, 2008, 'A'),

    (2009420029, 'TI01', 3, 2008, 'C'),

    (2009420029, 'TI02', 3, 2008, 'D'),

    (2009420030, 'TI01', 3, 2008, 'C'),

    (2009420030, 'TI02', 3, 2008, 'A'),

    (2009420031, 'TI01', 3, 2008, 'D'),

    (2009420031, 'TI02', 3, 2008, 'D');

memasukkan data ke tabel mahasiswa

INSERT INTO mahasiswa(

            nim, nama,prodi)

    VALUES (2009420028, 'okin luberto','TIK'),

    (2009420029, 'mahasiswa1','TIK'),

    (2009420030, ' mahasiswa2','TIK'),

    (2009420031, ' mahasiswa3','TIK'),

    (2009420032, ' mahasiswa4','TIK');

memasukkan data ke tabel mata_kuliah

INSERT INTO mata_kuliah(

            kdmtk,nm_mtk,sks,smt)

    VALUES ('TI01', 'ALGO 1',3,5),

    ('TI02', 'SQBD',3,5);

memasukkan data ke tabel nilai_sp

INSERT INTO nilai_sp(

            nim, kdmtk, smt, tahun, great)

    VALUES (2009420028, 'TI01', 3, 2008, 'C'),

    (2009420028, 'TI02', 3, 2008, 'D'),

    (2009420029, 'TI01', 3, 2008, 'C'),

    (2009420029, 'TI02', 3, 2008, 'C'),

    (2009420030, 'TI01', 3, 2008, 'C'),

    (2009420030, 'TI02', 3, 2008, 'C'),

    (2009420031, 'TI01', 3, 2008, 'C'),

    (2009420031, 'TI02', 3, 2008, 'D');   

memasukkan data ke tabel nilai_upas

INSERT INTO nilai_upas(

            nim, kdmtk, smt, tahun, great)

    VALUES (2009420028, 'TI01', 3, 2008, 'A'),

    (2009420028, 'TI02', 3, 2008, 'A'),

    (2009420029, 'TI01', 3, 2008, 'B'),

    (2009420029, 'TI02', 3, 2008, 'B'),

    (2009420030, 'TI01', 3, 2008, 'C'),

    (2009420030, 'TI02', 3, 2008, 'B'),

    (2009420031, 'TI01', 3, 2008, 'C'),

    (2009420031, 'TI02', 3, 2008, 'C');    

memasukkan data ke tabel nilai_upta

INSERT INTO nilai_upta(

            nim, kdmtk, smt, tahun, great)

    VALUES (2009420028, 'TI01', 3, 2008, 'A'),

    (2009420028, 'TI02', 3, 2008, 'C'),

    (2009420029, 'TI01', 3, 2008, 'B'),

    (2009420029, 'TI02', 3, 2008, 'B'),

    (2009420030, 'TI01', 3, 2008, 'C'),

    (2009420030, 'TI02', 3, 2008, 'B'),

    (2009420031, 'TI01', 3, 2008, 'A'),

    (2009420031, 'TI02', 3, 2008, 'D');        

nah setelah memasukkan data ke tabel, kita cek apakah data yang kita masukkan tadi berhasil tersimpan

SELECT * from mata_kuliah

SELECT * from mahasiswa

SELECT * from nilai_semester

SELECT * from nilai_sp

SELECT * from nilai_upas

SELECT * from nilai_upta

setelah semua okke, baru kita buat functions - nya, pada contoh ini saya akan membuat 2 functions .

1. get_khs untuk menampilkan hasil ujiannya diambil dari ( diambil nilai terbesar dari 4 nilai ujian (nilai_semester, nilai_sp, nilai_upta, nilai_upas ) di kalikan jumlah sks dari mata_kulaih yang diambil.

2. get_ipk untuk menampilkan nilai_ipk ( diambil dari (jumlah hasil semua nilai terbesar dari tiap mata_kulaih) / ( jumlah sks yang diambil )

MEMBUAT FUNCTION get_khs.

create or replace function get_khs(varchar)

returns setof record as

$$

declare

     nim_mhs alias for $1;

     out record;

begin

     for out in execute

     'select nim,(select nama from mahasiswa where nim=ddd.nim) as nama,

kdmtk,(select nm_mtk from mata_kuliah where kdmtk=ddd.kdmtk) as nm_mtk,

smt,sks,tahun,nilai_akhir,

(case when nilai_akhir=''A'' then sks*4

when nilai_akhir=''B'' then sks*3

when nilai_akhir=''C'' then sks*2

when nilai_akhir=''D'' then sks*1

else sks*0 end)::integer as nxk from(

select nim,kdmtk,smt,(select sks from mata_kuliah where kdmtk=dd.kdmtk) as sks,tahun,nilai_akhir from (

select nim,kdmtk,smt,tahun,min(great) as nilai_akhir from (

select * from nilai_semester union all

select * from nilai_upta union all

select * from nilai_upas union all 

select * from nilai_sp

) as d group by nim,kdmtk,smt,tahun having nim='''||nim_mhs||''' order by nim

) as dd

) as ddd'

     loop

           return next out;

     end loop;

     return;

end

$$ language 'plpgsql';

tau gg maksud dari query pada function get_khs diatas okelah akan saya jelaskan, agar mudah dipahami saya akan menjelaskan mulai dari query yang paling bawah, atau lapisan yang paling tengah. karena, jika saya menjelaskan mulai dari yang paling atas mungkin akan susah untuk dimengeti ( kalau menurut saya hehehe ).

nah pada query diatas telah saya beri warna yang berbeda dan berlapis2, untuk mempermudah menjelaskannya. untuk memulai membuat function semua hampir sama caranya create or replace function [nama functions](varchar) dan seterusnya seperti pada query diatas.

berikut penjelasan pada lapisan query diatas .

lapisan merah --> terdapat select * [nama tabel] karena pada ke 4 tabel nilai yang saya buat attributnya sama jadi saya bisa menampilkan semua data pada ke 4 tabel secara bersamaan menggunakan union all 

lapisan kuning --> meminta menampilkan attribut nim,kdmtk,smt,tahun,min(great) as nilai_akhir, "min" mengambil nilai terbaik dari ke 4 tabel hasil ujian ."as nilai_akhir" dan diberi nama "nilai_akhir" ."having nim='''||nim_mhs||''' " akan menampilkan data meurut nim yang akan dimasukkan ke variabel "nim_mhs" ,"order by nim" hasil output diurutkan menurut nim .

lapisan hijau --> hampir sama dengan lapisan kuning, "(select sks from mata_kuliah where kdmtk=dd.kdmtk) as sks" mengambil sks dari tabel mata_kuliah dimana kdmtk=dd.kdmtk ( kdmtk hasil pemanggilan sebelumnya ), dd hanya sebagai variabel bayangan yang nantinya query harus diakhiri dengan ") as dd", dan diberi nama "sks"

lapisan biru --> terdapat case when "case when nilai_akhir=''A'' then sks*4", mengambil nilai akhir, jika nilai_akhir sama dengan 'A' maka (keluarannya) sks dikalikan 4, jika 'B' dikalikan 3, jika 'C' dikalikan 2, jika 'D' dikalikan 1, jika else lainnya dikalikan 0, dan hasil (keluarannya) tadi dimasukkan ke attribut "nxk".

 

MEMBUAT FUNCTION get_ipk.

create or replace function get_ipk(varchar)

returns setof record as

$$

declare

     nim_mhs alias for $1;

     out record;

begin

     for out in execute

     'select nim,nama,(sum(nxk)/sum(sks))::double precision as ipk from (

     select nim,(select nama from mahasiswa where nim=ddd.nim) as nama,

kdmtk,(select nm_mtk from mata_kuliah where kdmtk=ddd.kdmtk) as nm_mtk,

smt,sks,tahun,nilai_akhir,

(case when nilai_akhir=''A'' then smt*4

when nilai_akhir=''B'' then sks*3

when nilai_akhir=''C'' then sks*2

when nilai_akhir=''D'' then sks*1

else sks*0 end)::integer as nxk from(

select nim,kdmtk,smt,(select sks from mata_kuliah where kdmtk=dd.kdmtk) as sks,tahun,nilai_akhir from (

select nim,kdmtk,smt,tahun,min(great) as nilai_akhir from (

select * from nilai_semester union all

select * from nilai_upta union all

select * from nilai_upas union all 

select * from nilai_sp

) as d group by nim,kdmtk,smt,tahun having nim='''||nim_mhs||''' order by nim

) as dd

) as ddd

) as dddd group by nim,nama'

loop

           return next out;

     end loop;

     return;

end

$$ language 'plpgsql';

untuk menjelaskan query ini hampir sama dengan query diatas, tetapi saya hanya menambahkan sedikit penjelasan pada kapisan ungu "(sum(nxk)/sum(sks))::double precision as ipk" query ini untuk menghitung IPK ,jumlah "nxk" dibagi jumlah "sks" dan hasilnya nanti dimasukkan di variabel ipk yang ber-tipe data double .

Naaaaah , setelah buat functionnya kita penggil dengan mengetik query seperti ini:

panggil function get_khs yang nimnya= 2009420028

select * from get_khs('2009420028') as (

nim varchar,nama varchar,kdmtk varchar,nm_mtk varchar,smt int,sks int,tahun numeric,nilai_akhir text,nxk int)

 panggil function get_ipk yang nimnya= 2009420028

 select * from get_ipk('2009420028') as (nim varchar,nama varchar,ipk double precision)

Read More

Mengkoneksikan java dengan Mysql

Untuk mengkoneksikan java dengan database MysqlServer yang pertama kali kita butukan adalah konektornya bisa di download disini (494 kb).

Sebelumnya kita harus sudah menginstall software mysqlServer di komputer kita, dan jangan lupa mengaktifkannya. Untuk mengecek mysqlserver kita aktif atau tidak, kita bisa masuk ke services dengan cara klik start –run - ketik services.msc – lalu enter . Setelah program diaktifkan kita masuk ke "MySql Command line client" dan ketikkan perintah seperti gambar dibawah ini :

pertama kita masukkan password kita setelah password berhasil ketikkan "create database coba " yang artinya membuat database dengan nama "coba".

Setelah itu buka program editor, kali ini saya menggunakan netbeans 6.5 . buat project baru dan beri nama sembarang. Setelah itu import konektor.jar yang sudah di download tadi, sebelumnya ekstrak dulu jika file masih berbentuk ".rar".  lalu pada folder library klik kanan dan pilih "Add JAR/Folder.." cari tempat file konektor hasil donlotan mu tadi dan klik "ok".

 

Setelah itu buat new java Class baru dan ketikkan source code dibawah ini.

Terlebih dahulu import class - class yang ada pada koneksi.jar yang kita import tadi dengan cara mengetikkan .

Import java.*,*; ( seperti gambar diatas );

Setelah itu kita menginisialisasikan nama koneksi yang akan kita buat.

"localhost:3309/coba?user=root&password=password" artinya koneksi menggunakan port 3309 ( port ini tergantung pengisian saat pertama menginstall mysqlserver ), dicontoh ini aku menggunakan port 3309, mungkin jika kamu tidak mengubah portnya pada saat menginstall mysqlserver port akan terisi dengan otomatis secara default yaitu "3306" jadi pada program kamu nanti ganti port dengan default ( 3306 ). Pada code "coba" menunjukkan database yang telah kita buwat tadi yang akan kita koneksikan. "user=root" menunjukkan user disini masih menggunakan nama user yang default yaitu "root" dan password isi dengan password sqlmu.

Jika program berhasil maka akan tampil form berhasil seperti pada gambar diatas. Jika program salah, error handling pada trycatch yang kita pasang tadi akan menampilkan error / kesalahan pada program.

SourceCode diatas juga bisa di download disini

Semoga bermanfaat ...

Read More