PERINTAH SQL

                                       Perintah perintah di SQL

Structured Query Language (SQL) merupakankomponen bahasa relational database system. SQLmerupakan bahasa baku (ANSI/SQL), non procedural, danberorientasi pada himpunan. SQL dapat digunakan baiksecara interaktif atau ditempelkan pada sebuah programaplikasi.

1. Komponen-Komponen SQL

            Komponen-komponen pada SQL dapat dilihat pada gambar 1.

 

Gambar 1.  Bagan Komponen-komponen SQL

   a.     Data Definition Language (DDL) :

Digunakan untuk mendefinisikan  data denganmenggunakan perintah :  create, drop, alter.

   b.     Data Manipulation Language (DML) :

Digunakan  untuk  memanipulasi  data  dengan menggunakan  perintah  :  select, insert, update, delete.

Data Manipulation Language merupakan bagianterpadu bahasa SQL. Perintah-perintahnya  dapat dibuat  secara  interaktif  atau  ditempelkan  padasebuah program  aplikasi.  Pemakai  hanya  perlu menentukan  'APA'  yang  ia  inginkan,     DBMSmenentukan 'BAGAIMANA' cara mendapatkannya.

   c.         Data Control Language (DCL) :

Digunakan untuk mengontrol hak para  pemakaidata dengan perintah :  grant,   revoke

1.1.  DDL

            Perintah-perintah pada DDL dapat dijabarkan sebagai berikut:

   1.      CREATE TABLE

            Perintah CREATE TABLE berfungsi untuk membuat tabel.

            Sintaks :         CREATE TABLE tbname

                                    (col 1 data type data spec,

                                    col 2  data type  data spec,

                                    .

                                    .

                                    PRIMARY KEY (col1,……))

            Contoh :

CREATE TABLE MAHASISWA

(NPM  CHAR(8)  NOT NULL,

NAMA   CHAR(45)  NOT NULL,

ALAMAT   CHAR(45),

TGL-LAHIR  DATE   NOT NULL WITH DEFAULT,

PRIMARY KEY (NPM))

Pernyataan NULL yang digunakan dapat dispesifikasikan sebagai berikut :

·         NULL

NULL dapat  diinterpretasikan  sebagai  nilai  yang  tidak  diketahui  atau  tidak tersedianya suatu nilai. NULL bukan berarti kosong (blank) atau 0 (Nol).

·         NOT NULL

NOT NULL di mana pemakai atau program harus memberikan nilai-nilai pada saat memasukkan record

·         NOT NULL WITH DEFAULT

Nilai default disimpan pada saat record dimasukkan tanpa nilai yang ditentukan untuk kolom ini

      Nilai default-nya :

·         Nol, untuk tipe field NUMERIC

·         Blank, untuk tipe field CHARACTER CURRENT

·         DATE, untuk tipe field DATE CURRENT

·         TIME, untuk tipe field TIME

            Pada saat membuat tabel, salah satu atribut tersebut di atas dispesifikasikan pada sebuah kolom.

   2.      CREATE VIEW

            Perintah CREATE VIEW berfungsi untuk membuat tabel view. View merupakan  bentuk alternatif  penyajian data dari satu atau lebih tabel. View dapat berisi semua atau sebagian kolom yang terdapat pada tabel di mana kolom tersebut didefinisikan.

            Tujuan membuat view :

§  Meningkatkan keamanan data

§  Meningkatkan kemandirian data

§  Penyederhanaan  bagi end  user (data   yang  sedikit,  nama-nama kolom yang baru dan dapat dibaca dengan lebih baik)

Properti :

§  Tidak terdapatnya data tambahan

§  View mencakup subset kolom dan / atau baris

§  View dapat berisikan data dari beberapa tabel dan / atau tabel-tabel view lainnya

§  View dapat berisikan perolehan data, misal : nilai rata-rata

§  Manipulasi data melalui view terbatas

            Sintaks :         CREATE VIEW  viewname (column1, column2, ……..)

                                    AS SELECT statement FROM tbname

                                    [WITH CHECK OPTION]

Keterangan :

   -        View-name     : nama view yang akan dibuat.

   -        Column          : nama atribut untuk view

   -        Statement       : atribut yang dipilih dari tabel basis data.

   -        Tbname          : nama tabel basis data.

            Contoh :

            CREATE VIEW VMHS (NPM, NAMA)

            AS SELECT  NPM, NAMA FROM MAHASISWA

   3.      CREATE INDEX

            Perintah CREATE INDEX berfungsi untuk  membuat tabel index.

            Sintaks :         CREATE [UNIQUE] INDEX indexname

                                    ON nama_table (nama_kolom)

            Contoh :

            CREATE UNIQUE INDEX MHSIDX ON MAHASISWA(NPM)

Dengan indeks memungkinkan suatu tabel diakses dengan urutan tertentu tanpa harus merubah urutan fisik dari datanya dan dapat pula diakses secara cepat melalui indeks yang dibuat berdasar nilai field tertentu. Spesifikasi  UNIQUE akan menolak key yang sama dalam file.

            DROP TABLE

            Perintah DROP TABLE berfungsi untuk menghapus tabel.

Sintaks :    DROP TABLE tbname

Contoh :    DROP TABLE MAHASISWA

Dengan perintah itu obyek lain yang berhubungan dengan tabel tersebut otomatis akan dihapus atau tidak akan berfungsi seperti :

    -       semua record dalam tabel akan terhapus

    -       index dan view pada tabel akan hilang

    -       deskripsi tabel akan hilang

   5.      DROP VIEW

Perintah DROP VIEW berfungsi untuk menghapus view.

Sintaks :   DROP VIEW viewname

Contoh :   

DROP VIEW VMHS

   6.      DROP INDEX

            Perintah DROP INDEX berfungsi untuk menghapus index.

            Sintaks :    DROP INDEX indexname

            Contoh :   

            DROP INDEX MHSIDX

   7.      ALTER

            Perintah ALTER berfungsi untuk merubah atribut pada suatu tabel.

            Sintaks :         ALTER TABLE  tbname

                        MODIFY (nama_kolom  tipe_kolom)

                        ADD  (nama_kolom   tipe_kolom [[before, nama_kolom]])
                               DROP (nama_kolom  tipe_kolom)

Contoh :    merubah Tabel TABX dengan menambah Field D.

ALTER TABLE TABX ADD D CHAR(3)

1.2. DML

Perintah-perintah pada DML dapat dijabarkan sebagai berikut:

   1.      INSERT

Perintah INSERT berfungsi untuk menambah baris (record) baru.

Sintaks :         INSERT INTO tbname

                        (col1, ...) VALUES (value1, ...)

Catatan :

Sintaks tersebut dapat digunakan jika jumlah kolom = jumlah nilai, tetapi jika dalam tabel semua kolom akan diisi dapat digunakan sintaks berikut ini :

Sintaks :         INSERT INTO tbname

                        VALUES (value1, value2, ...)

Nilai-nilai diisikan sebanyak kolom yang terdapat di tabel tersebut.

Contoh : pada tabel MAHASISWA akan ditambahkan record mahasiswa baru.

INSERT INTO MAHASISWA

VALUES (‘00105543’,’MUHAMMAD ADI’,JL. MERUYA NO.100, JAKARTA’,02-02-1990)

   2.      UPDATE

Perintah UPDATE berfungsi untuk merubah record.

Sintaks :         UPDATE tbname

                        SET field = ekspresi

                        WHERE kondisi

Contoh :akan diubah nama mahasiswa dari MUHAMMAD ADI dengan NPM 00105432 menjadi MUHAMMAD ALI

UPDATE MAHASISWA

SET nama =’MUHAMMAD ALI’

WHERE NPM=’00105432’

   3.      DELETE

Perintah DELETE berfungsi untuk menghapus record.

Sintaks :         DELETE FROM tbname

                        WHERE kondisi

Contoh : akan dihapus data mahasiswa dengan NPM 0010500

DELETE FROM MAHASISWA

WHERE NPM=’00105004

    4.     SELECT

Perintah SELECT berfungsi untuk menampilkan record.

Sintaks :         SELECT [DISTINCT] colname FROM  tbname

                        [WHERE kondisi]

                        [GROUP BY kondisi]

                        [HAVING kondisi]

                        [ORDER BY kondisi]

1.3. Contoh Kasus DDL

            Contoh kasus DDL antara lain :

   1.      Membuat Tabel

   a.      CREATE TABLE PEMASOK

(Kd-P          Char(5)     NOT NULL,

 Nama-P Char(20)     NOT NULL,

 Status   Smallint     NOT  NULL,

 Kota       Char(15)    NOT NULL);

   b.      CREATE TABLE BARANG

            (Kd-B         Char(6)      NOT NULL,

            Nama-B Char(20)      NOT NULL,

            Warna   Char(6)        NOT NULL,

            Berat  Smallint       NOT NULL);

   c.      CREATE TABLE PB

            (Kd-P     Char(5)          NOT NULL,

            Kd-B    Char(6)           NOT NULL,

            Jumlah  INTEGER       NOT NULL);

   d.      CREATE UNIQUE INDEX Sidx  ON PEMASOK(Kd-P);

            CREATE UNIQUE INDEX Pidx  ON BARANG(Kd-B);

            CREATE  INDEX Sdx ON PB(Kd-P);

            CREATE  INDEX Pdx ON PB(Kd-B);

   2.      Modifikasi Table BARANG  dengan perintah  :

RENAME COLUMN PEMASOK.Warna TO Warna-B

ALTER TABLE  BARANG  ADD (Kota CHAR(15) NOT NULL)

   3.      Membuat View

   a.      Membuat view untuk pemasok yang statusnya lebih besar dari 15

            CREATE VIEW PEMASOK-YG-BAGUS

            AS SELECT Kd-P, Status, Kota FROM PEMASOK

            WHERE Status > 15

   b.      Membuat view yang berisi pemasok yang tinggal di Paris

            CREATE VIEW PEMASOK-PARIS

            AS SELECT * FROM PEMASOK

            WHERE Kota = ' Paris '

   c.      Membuat view dengan mengganti nama_atributnya

           

            CREATE VIEW BARANG (Kd-Brg, Nm-Brg, Brt)

            AS SELECT Kd-B, Nama-B, Berat FROM BARANG

            WHERE Warna = 'Red'

1. 4 Contoh Kasus DML

Contoh kasus DML antara lain :

   1.      Menambah record (INSERT)

INSERT INTO S VALUES ('S1','Smith',20,'London');

INSERT INTO S VALUES ('S2','Jones,10,'Paris');

INSERT INTO S VALUES ('S3','Blake',30,'Paris')

Tabel S, P dan SP  berisikan dengan data-data seperti terlihat pada tabel 1, 2. dan 3.

                       Tabel 1.  TABEL PEMASOK

         

Kd-P

Nama-P

Status

Kota

P1

Smith

20

London

P2

Jones

10

Paris

P3

Blake

30

Paris

P4

Clark

20

London

P5

Adams

30

Athens

                              Tabel 2.  Tabel BARANG

Kd-B

Nama-B

Warna

Berat

Kota

B1

Nut

Red

12

London

B2

Bolt

Green

17

Paris

B3

Screw

Bleu

17

Rome

B4

Screw

Red

14

London

B5

Cam

Blue

12

Paris

B6

Cog

Red

19

London

         Tabel 3.  Tabel PEMASOK-BRG

Kd-P

Kd-B

Jumlah

P1

B1

300

P1

B2

200

P1

B3

400

P1

B4

200

P1

B5

100

P1

B6

100

P2

B1

300

P2

B2

400

P3

B2

200

P4

B2

200

P4

B4

300

P4

B5

400

   2.      Merubah record (UPDATE)

   a.      Mengubah  data  (record)  pada  tabel  BARANG  yang  mempunyai  nomor barang B2, warnanya diubah menjadi Kuning dan beratnya ditambah 5

            UPDATE BARANG

            SET Warna  = 'Yellow', Berat = Berat + 5

            WHERE Kd-B  = 'B2'

   b.      Mengubah record pada tabel PEMASOK, statusnya menjadi dua kali status awal untuk pemasok yang bertempat tinggal di kota London

            UPDATE PEMASOK

            SET Status    = 2 * Status

            WHERE Kota = 'London'

   3.      Menghapus record (DELETE)

Menghapus record pada tabel PEMASOK yang nomor pemasoknya P5

DELETE FROM PEMASOK

WHERE Kd-P ='P5'

            4. Menampilkan record (SELECT 1 tabel)

   a.      Menampilkan semua data pemasok

            SELECT * FROM PEMASOK

            atau

            SELECT Kd-P, Nama-P, Status, Kota  FROM PEMASOK

   b..     Menampilkan semua nilai kode barang pada tabel PEMASOK-BRG

            SELECT Kd-B FROM PEMASOK-BRG

   c.      Menampilkan kode pemasok dan status untuk pemasok yang tinggal di Paris

            SELECT Kd-P, Status FROM PEMASOK  WHERE Kota ='Paris'

   d.      Menampilkan kode pemasok yang tinggal di Paris dengan status >  20

            SELECT Kd-P FROM PEMASOK

            WHERE Kota ='Paris” AND Status > 20

   e.      Menampilkan jumlah pengiriman B1

            SELECT COUNT(*) FROM PEMASOK-BRG

            WHERE Kd-B = 'B1'

   f.       Perintah untuk menghindari hasil data yang sama terulang kembali     (distinct)

            SELECT DISTINCT Kd-B FROM PEMASOK-BRG

   g.      Menampilkan kode pemasok dan status bagi pemasok yang tinggal di Paris dalam urutan status menurun

            SELECT  Kd-P,Status FROM PEMASOK

            WHERE Kota = 'Paris'

            ORDER BY Status desc

   h.      Menampilkan kode barang dari semua barang yang dipasok oleh  lebih dari seorang pemasok

            SELECT Kd-B FROM PEMASOK-BRG

            GROUP BY Kd-B           

            HAVING COUNT(*) > 1

   i.       Menampilkan semua barang yang namanya dimulai dengan huruf  "C"

            SELECT * FROM BARANG

            WHERE Nama-B LIKE 'C%'

   5.      Menampilkan record (SELECT lebih dari satu tabel / JOIN)

   a.      Menampilkan semua pemasok dan barang yang keduanya bertempat tinggal pada kota yang sama

            SELECT Kd-P, Nama-P,Status, PEMASOK.Kota , Kd-B, Nama-B, Warna, Berat FROM PEMASOK, BARANG

            WHERE PEMASOK.Kota = BARANG.Kota

   b.      Menampilkan nama pemasok yang memasok barang dengan nomor barang B2

            SELECT Nama-P FROM PEMASOK, PEMASOK-BRG

            WHERE PEMASOK.Kd-P = PEMASOK-BRG.Kd-P

            AND PEMASOK-BRG.Kd-B = 'B2'

   c.      Menampilkan nama pemasok yang memasok barang berwarna merah

            SELECT Nama-P FROM PEMASOK, PEMASOK-BRG, BARANG

            WHERE PEMASOK.Kd-P = PEMASOK-BRG.Kd-P

           AND PEMASOK-BRG.Kd-B = BARANG.Kd-B

            AND BARANG.Warna = 'RED'

   6.      Menampilkan record (SELECT lebih dari satu tabel / SELECT Bertingkat)

Contoh queri :

   a.      Menampilkan nama pemasok yang memasok barang dengan kode barang B2

            SELECT Nama-P FROM PEMASOK

            WHERE Kd-P IN

                           (SELECT Kd-P FROM PEMASOK-BRG

                             WHERE Kd-B = 'B2')

            Atau

            SELECT Nama-P FROM PEMASOK

            WHERE Kd-P = ANY

                           (SELECT Kd-P FROM PEMASOK-BRG

                             WHERE Kd-B = 'B2')

   b.      Menampilkan nama pemasok yang memasok barang berwarna merah

            SELECT Nama-P FROM PEMASOK

            WHERE Kd-P IN

                        (SELECT Kd-P FROM PEMASOK-BRG

                          WHERE Kd-B IN

                                        (SELECT Kd-B FROM BARANG

                                                         WHERE Warna = 'Red'))

   c.      Menampilkan  kode pemasok  dengan  nilai   status  lebih  kecil  daripada  nilai maksimum status yang ada pada tabel PEMASOK

            SELECT Kd-P FROM PEMASOK

            WHERE Status <

                           (SELECT  MAX(Status) FROM PEMASOK)

   d.      Menampilkan nama pemasok yang tidak memasok barang dengan nomor barang B2

            SELECT Nama-P FROM PEMASOK

            WHERE Kd-P NOT IN

                          (SELECT Kd-P FROM PEMASOK-BRG

                            WHERE  Kd-B = 'B2')

   e.      Menampilkan semua kode pemasok yang sama lokasinya dengan pemasok P1

            SELECT Kd-P FROM PEMASOK

            WHERE Kota =

                          (SELECT Kota FROM PEMASOK

                            WHERE Kd-P = 'P1')

   7.      Fungsi Perhitungan

Fungsi-fungsi yang dapat digunakan antara lain:

·         COUNT : jumlah baris dan kolom

·         SUM  : jumlah nilai dam kolom

·         AVG  : rata-rata nilai dalam kolom

·         MAX  : nilai terbesar dalam kolom

·         MIN   : nilai terkecil dalam kolom

Untuk SUM dan AVG nilainya harus numerik (INT, SMALLINT, FLOAT). Fungsi- fungsi tersebut jika dikenakan pada nilai yang NULL maka nilainya akan diabaikan kecuali untuk COUNT(*)

Berikut ini contoh queri yang menggunakan fungsi-fungsi perhitungan :

   a.      Menghitung jumlah pemasok

            SELECT COUNT(*) FROM PEMASOK

              atau

            SELECT COUNT (Kd-P) FROM PEMASOK

   b.      Menampilkan kode barang dan total jumlah pengiriman dari setiap barang

            SELECT Kd-B, SUM(Jumlah) FROM PEMASOK-BRG

           GROUP BY Kd-B

   c.      Menghitung jumlah kuantitas dari B2 yang telah dipasok

            SELECT SUM (Jumlah) FROM PEMASOK-BRG

            WHERE Kd-B = 'B2'

   d.      Menampilkan jumlah pengiriman barang dengan nomor B4 dan dipasok  oleh kode pemasok P1

            SELECT COUNT(*) FROM PEMASOK-BRG

            WHERE Kd-B = 'B4' AND Kd-P = 'P1'

   e.      Menampilkan kode barang dan total kuantitas dari masing-masing barang

            SELECT Kd-B, SUM(Jumlah) FROM PEMASOK-BRG

           GROUP BY Kd-B

1.5. DCL

Berikut ini adalah perintah-perintah pada DCL :

   1.      GRANT

            Perintah GRANT berfungsi untuk memberikan izin akses kepada pemakai.

Sintaks :    GRANT privileges ON tbname TO user

Contoh :

GRANT SELECT ON CLUB TO PUBLIC

GRANT SELECT, INSERT, UPDATE, DELETE ON CLUB TO USER01

   2.      REVOKE

Perintah REVOKE berfungsi untuk mencabut izin akses kepada pemakai.

Sintaks :    REVOKE privileges ON tbname FROM user

Contoh :

REVOKE INSERT, UPDATE, DELETE ON CLUB FROM USER01

REVOKE ALL ON CLUB FROM PUBLIC

Menerapkan penggunaan SQL

Perintah select multi table mysql sekaligus (pencarian multi table pada mysql)

Assalamu alaikum wr wb 

Terkadang kita membuat beberapa Query ke database hanya dibutuhkan untuk pencocokan data saja, seperti misalnya kita akan mengambil data pada table ke 1 ke 2 dan ke 3, dengan acuan data pada table ke 4 dengan membuat Query berulang2,, dan sepertinya metode tersebut kurang efektif, karena terlalu banyak kode yang harus ditulis, dan alangkah baiknya hanya satu Query saja.

Langsung ke Contoh!
Misalkan kita memiliki 2 buah table pada database yaitu: table_user, dan table_produk,

contoh table_user, dan table_produk, pada database toko_tes
table_user /dan contoh isi nya:

CREATE TABLE `table_user` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_nama` varchar(200) NOT NULL,
  `user_alamat` varchar(300) NOT NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `table_user` VALUES (1, 'ujang r', 'subang, jawa barat');
INSERT INTO `table_user` VALUES (2, 'dodi m', 'purwakarta, jawa barat');

table_produk /dan contoh isi nya:

 CREATE TABLE `table_produk` (
  `produk_id` int(11) NOT NULL auto_increment,
  `produk_userid` int(11) NOT NULL,
  `produk_nama` varchar(200) NOT NULL,
  PRIMARY KEY  (`produk_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `table_produk` VALUES (1, 1, 'baju gamis wanita');
INSERT INTO `table_produk` VALUES (2, 1, 'baju lelaki kern');
INSERT INTO `table_produk` VALUES (3, 2, 'celada panjang lelaki');
INSERT INTO `table_produk` VALUES (4, 2, 'celana pendek lelaki');

Untuk mengambil seluruh data pada ke dua table tersebut bisa hanya dengan satu perintah saja, dibawah ini adalah beberapa contoh perintah multi select dengan perintah di program php

Contoh 1: 

mysql_query("SELECT * FROM table_user, table_produk LIMIT 10");

Hasil output nya adalah:
menampilkan semua data yang dibatasi limit 10

Contoh 2: 

  mysql_query("SELECT * FROM table_user, table_produk WHERE user_id = produk_userid LIMIT 10");

 Hasil Output nya adalah:

Contoh 3:

 mysql_query("SELECT * FROM table_user, table_produk WHERE table_user.user_id = table_produk.produk_userid LIMIT 10");

 Hasil output nya adalah:

Untuk contoh penerapan nya pada program php, silahkan cari ide sendiri,, semoga artikel singkat ini menambah wawasan anda, dan selamat berkarya, semoga sukses,,

Wassalam!