Teori
Sebuah tabel terdiri dari baris (row) dan kolom (column), di mana jumlah dan nama kolom harus didefinisikan terlebih dahulu di awal. Sedangkan baris merupakan sebuah variabel yang dapat dihapus dan diisi kapan pun, sehingga jumlahnya selalu berubah sesuai dengan jumlah data di dalamnya. Setiap kolom dalam tabel mempunyai tipe data, tipe data digunakan untuk membatasi jenis data yang bisa dimasukkan, sehingga akan mempermudah dalam menggunakannya dan melakukan pengelolaan selanjutnya. Sangat disarankan pada saat akan membuat tabel, sebaiknya membuat sebuah konvensi khusus dalam penentuan tabel-tabel yang akan dibuat dan memberikan nama kolom maupun tipe datanya.
Dan dalam modul ini kita menggunakan 2 kategori besar sesuai fungsinya, yaitu :
- DDL - Data Definition Languagemerupakan kumpulan perintah SQL yang digunakan untuk membuat, mengubah dan menghapus struktur dan definisi metadata dari objek-objek database.
- DML - Data Manipulation Language
merupakan kumpulan perintah SQL yang digunakan untuk proses pengolahan isi data di dalam table seperti memasukkan, merubah dan menghapus isi data - dan tidak terkait dengan perubahan struktur dan definisi tipe data dari objek database.
DDL - Data Definition Language
Seperti definisi yang telah dijelaskan di atas, DDL adalah kumpulan perintah SQL yang digunakan untuk membuat (create), mengubah (alter) dan menghapus (drop) struktur dan definisi tipe data dari objek-objek database.
Objek-objek database pada yang dimaksud - pada MySQL - adalah sebagai berikut :
- Database
- Table
- View
- Index
- Procedure (Stored Procedure)
- Function
- Trigger
DML - Data Manipulation Language
DML sendiri adalah kumpulan perintah SQL yang berhubungan dengan pekerjaan mengolah data di dalam table - dan tidak terkait dengan perubahan struktur dan definisi tipe data dari objek database seperti table, column, dan sebagainya.
>>http://mysql.phi-integration.com/sql/apa-itu-dml-ddl
Tugas & Praktikum
PostgreSQL >>
Shell 1:
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.3.5)
WARNING: Console code page (850) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
innamul | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | C
type | Access privileges
-----------------+----------+----------+---------------------------+------------
---------------+-----------------------
innamul13650019 | postgres | UTF8 | Indonesian_Indonesia.1252 | Indonesian_
Indonesia.1252 |
postgres | postgres | UTF8 | Indonesian_Indonesia.1252 | Indonesian_
Indonesia.1252 |
template0 | postgres | UTF8 | Indonesian_Indonesia.1252 | Indonesian_
Indonesia.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
template1 | postgres | UTF8 | Indonesian_Indonesia.1252 | Indonesian_
Indonesia.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
(4 rows)
postgres=# \c innamul13650019;
WARNING: Console code page (850) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
You are now connected to database "innamul13650019" as user "postgres".
innamul13650019=# create table identitasNIM (Id_idn integer not null, namaDepan_
idn varchar(10), namaBelakang_idn varchar(10), tgl_lahir_idn date, status_idn bo
olean, alamat_idn text);
CREATE TABLE
innamul13650019=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | identitasnim | table | postgres
(1 row)
innamul13650019=# z
innamul13650019-# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------+-------+-------------------+--------------------------
public | identitasnim | table | |
(1 row)
innamul13650019-# \d identitasNIM;
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019-# create table pegawai (Id_idn integer not null, namaDepan_idn v
archar(10), namaBelakang_idn varchar(10), tgl_lahir_idn date, status_idn boolean
, alamat_idn text);
ERROR: syntax error at or near "z"
LINE 1: z
^
innamul13650019=# create table pegawai (Id_idn integer not null, namaDepan_idn v
archar(10), namaBelakang_idn varchar(10), tgl_lahir_idn date, status_idn boolean
, alamat_idn text);
CREATE TABLE
innamul13650019=# \d pegawai;
Table "public.pegawai"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=# alter table pegawai drop column "alamat_idn";
ALTER TABLE
innamul13650019=# \d pegawai;
Table "public.pegawai"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
innamul13650019=# alter table pegawai add column "pekerjaan_idn" varchar(20);
ALTER TABLE
innamul13650019=# \d pegawai;
Table "public.pegawai"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
pekerjaan_idn | character varying(20) |
innamul13650019=# alter table pegawai rename to pekerjaNIM;
ALTER TABLE
innamul13650019=# \d pekerjanim
Table "public.pekerjanim"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
pekerjaan_idn | character varying(20) |
innamul13650019=# alter table pekerjaNIM column pekerjaan_idn type alamat_idn;
ERROR: syntax error at or near "column"
LINE 1: alter table pekerjaNIM column pekerjaan_idn type alamat_idn;
^
innamul13650019=# alter table pekerjaNIM rename column pekerjaan_idn type alamat
_idn;
ERROR: syntax error at or near "type"
LINE 1: ...lter table pekerjaNIM rename column pekerjaan_idn type alama...
^
innamul13650019=# alter table pekerjaNIM column pekerjaan_idn type text;
ERROR: syntax error at or near "column"
LINE 1: alter table pekerjaNIM column pekerjaan_idn type text;
^
innamul13650019=# alter table pekerjaNIM alter column pekerjaan_idn type text;
ALTER TABLE
innamul13650019=# \d pekerjanim
Table "public.pekerjanim"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
pekerjaan_idn | text |
innamul13650019=# alter table pekerjanim rename column "pekerjaan_idn" to alamat
_idn;
ALTER TABLE
innamul13650019=# \d pekerjanim
Table "public.pekerjanim"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=# drop table pekerjaNIM;
DROP TABLE
innamul13650019=# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------+-------+-------------------+--------------------------
public | identitasnim | table | |
(1 row)
innamul13650019=# \d identitas
Did not find any relation named "identitas".
innamul13650019=# \d identitasNIM
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=# create temporary table mahasiswa_sementara (id integer not nul
l, nama varchar(15), tanggal_lahir date);
CREATE TABLE
innamul13650019=# \d mahasiswa_sementara
Table "pg_temp_3.mahasiswa_sementara"
Column | Type | Modifiers
---------------+-----------------------+-----------
id | integer | not null
nama | character varying(15) |
tanggal_lahir | date |
innamul13650019=# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------+-------+-------------------+--------------------------
public | identitasnim | table | |
(1 row)
innamul13650019=# \d mahasiswa_sementara
Table "pg_temp_3.mahasiswa_sementara"
Column | Type | Modifiers
---------------+-----------------------+-----------
id | integer | not null
nama | character varying(15) |
tanggal_lahir | date |
innamul13650019=# \du
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------+-----------
alfan | Create DB | {}
badz | Create DB | {}
fairuz | Create DB | {}
fairuz13650100 | | {}
innamul | Create DB | {}
ismail | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
syahrul | Create DB | {}
innamul13650019=# revoke all on table identitasNIM from alfan;
REVOKE
innamul13650019=# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privi
leges
--------+--------------+-------+---------------------------+--------------------
------
public | identitasnim | table | postgres=arwdDxt/postgres |
(1 row)
innamul13650019=# grant all on table identitasNIM to innamul;
GRANT
innamul13650019=# \du
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------+-----------
alfan | Create DB | {}
badz | Create DB | {}
fairuz | Create DB | {}
fairuz13650100 | | {}
innamul | Create DB | {}
ismail | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
syahrul | Create DB | {}
innamul13650019=# \l
List of databases
Name | Owner | Encoding | Collate |
Ctype | Access privileges
-----------------+----------+----------+----------------------------+-----------
-----------------+-----------------------
Coba | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
alfan10650046 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
badz10650116 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
fairuz13650100 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
innamul13650019 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
ismail13650047 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
postgres | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
sahrul13650106 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
(10 rows)
innamul13650019=# revoke all on table identitasNIM from postgres;
REVOKE
innamul13650019=# \l
List of databases
Name | Owner | Encoding | Collate |
Ctype | Access privileges
-----------------+----------+----------+----------------------------+-----------
-----------------+-----------------------
Coba | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
alfan10650046 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
badz10650116 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
fairuz13650100 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
innamul13650019 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
ismail13650047 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
postgres | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
sahrul13650106 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
(10 rows)
innamul13650019=# \du
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------+-----------
alfan | Create DB | {}
badz | Create DB | {}
fairuz | Create DB | {}
fairuz13650100 | | {}
innamul | Create DB | {}
ismail | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
syahrul | Create DB | {}
innamul13650019=# \d identitasNIM
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=# grant all on table identitasNIM to postgres;
GRANT
innamul13650019=# \d identitasNIM
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=#
innamul13650019=# create temporary table Gaji (gaji_kotor integer, pajak integer
, gaji_bersih integer);
CREATE TABLE
innamul13650019=# \d identitasNIM
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=# create temporary table Gaji (gaji_kotor integer, pajak integer
, gaji_bersih integer)inherits (identitasNIM);
ERROR: relation "gaji" already exists
innamul13650019=# create temporary table Gaji (gaji_kotor integer, pajak integer
, gaji_bersih integer)inherit (identitasNIM);
ERROR: syntax error at or near "inherit"
LINE 1: ...kotor integer, pajak integer, gaji_bersih integer)inherit (i...
^
innamul13650019=# create temporary table Gaji (gaji_kotor integer, pajak integer
, gaji_bersih integer) inherits (identitasNIM);
ERROR: relation "gaji" already exists
innamul13650019=# \d gaji
Table "pg_temp_3.gaji"
Column | Type | Modifiers
-------------+---------+-----------
gaji_kotor | integer |
pajak | integer |
gaji_bersih | integer |
innamul13650019=# drop table gaji;
DROP TABLE
innamul13650019=# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privi
leges
--------+--------------+-------+---------------------------+--------------------
------
public | identitasnim | table | innamul=arwdDxt/postgres +|
| | | postgres=arwdDxt/postgres |
(1 row)
innamul13650019=# create temporary table Gaji (gaji_kotor integer, pajak integer
, gaji_bersih integer) inherits (identitasNIM);
CREATE TABLE
innamul13650019=# \d identitasNIM
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
Number of child tables: 1 (Use \d+ to list them.)
innamul13650019=# \d gaji
Table "pg_temp_3.gaji"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
gaji_kotor | integer |
pajak | integer |
gaji_bersih | integer |
Inherits: identitasnim
innamul13650019=# revoke all on table identitasNIM from innamul;
REVOKE
innamul13650019=# grant all on table identitasNIM to innamul;
GRANT
innamul13650019=# grant select on table identitasNIM to innamul;
GRANT
innamul13650019=#
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.3.5)
WARNING: Console code page (850) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
innamul | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | C
type | Access privileges
-----------------+----------+----------+---------------------------+------------
---------------+-----------------------
innamul13650019 | postgres | UTF8 | Indonesian_Indonesia.1252 | Indonesian_
Indonesia.1252 |
postgres | postgres | UTF8 | Indonesian_Indonesia.1252 | Indonesian_
Indonesia.1252 |
template0 | postgres | UTF8 | Indonesian_Indonesia.1252 | Indonesian_
Indonesia.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
template1 | postgres | UTF8 | Indonesian_Indonesia.1252 | Indonesian_
Indonesia.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
(4 rows)
postgres=# \c innamul13650019;
WARNING: Console code page (850) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
You are now connected to database "innamul13650019" as user "postgres".
innamul13650019=# create table identitasNIM (Id_idn integer not null, namaDepan_
idn varchar(10), namaBelakang_idn varchar(10), tgl_lahir_idn date, status_idn bo
olean, alamat_idn text);
CREATE TABLE
innamul13650019=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | identitasnim | table | postgres
(1 row)
innamul13650019=# z
innamul13650019-# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------+-------+-------------------+--------------------------
public | identitasnim | table | |
(1 row)
innamul13650019-# \d identitasNIM;
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019-# create table pegawai (Id_idn integer not null, namaDepan_idn v
archar(10), namaBelakang_idn varchar(10), tgl_lahir_idn date, status_idn boolean
, alamat_idn text);
ERROR: syntax error at or near "z"
LINE 1: z
^
innamul13650019=# create table pegawai (Id_idn integer not null, namaDepan_idn v
archar(10), namaBelakang_idn varchar(10), tgl_lahir_idn date, status_idn boolean
, alamat_idn text);
CREATE TABLE
innamul13650019=# \d pegawai;
Table "public.pegawai"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=# alter table pegawai drop column "alamat_idn";
ALTER TABLE
innamul13650019=# \d pegawai;
Table "public.pegawai"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
innamul13650019=# alter table pegawai add column "pekerjaan_idn" varchar(20);
ALTER TABLE
innamul13650019=# \d pegawai;
Table "public.pegawai"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
pekerjaan_idn | character varying(20) |
innamul13650019=# alter table pegawai rename to pekerjaNIM;
ALTER TABLE
innamul13650019=# \d pekerjanim
Table "public.pekerjanim"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
pekerjaan_idn | character varying(20) |
innamul13650019=# alter table pekerjaNIM column pekerjaan_idn type alamat_idn;
ERROR: syntax error at or near "column"
LINE 1: alter table pekerjaNIM column pekerjaan_idn type alamat_idn;
^
innamul13650019=# alter table pekerjaNIM rename column pekerjaan_idn type alamat
_idn;
ERROR: syntax error at or near "type"
LINE 1: ...lter table pekerjaNIM rename column pekerjaan_idn type alama...
^
innamul13650019=# alter table pekerjaNIM column pekerjaan_idn type text;
ERROR: syntax error at or near "column"
LINE 1: alter table pekerjaNIM column pekerjaan_idn type text;
^
innamul13650019=# alter table pekerjaNIM alter column pekerjaan_idn type text;
ALTER TABLE
innamul13650019=# \d pekerjanim
Table "public.pekerjanim"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
pekerjaan_idn | text |
innamul13650019=# alter table pekerjanim rename column "pekerjaan_idn" to alamat
_idn;
ALTER TABLE
innamul13650019=# \d pekerjanim
Table "public.pekerjanim"
Column | Type | Modifiers
------------------+-----------------------+-----------
id_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=# drop table pekerjaNIM;
DROP TABLE
innamul13650019=# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------+-------+-------------------+--------------------------
public | identitasnim | table | |
(1 row)
innamul13650019=# \d identitas
Did not find any relation named "identitas".
innamul13650019=# \d identitasNIM
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=# create temporary table mahasiswa_sementara (id integer not nul
l, nama varchar(15), tanggal_lahir date);
CREATE TABLE
innamul13650019=# \d mahasiswa_sementara
Table "pg_temp_3.mahasiswa_sementara"
Column | Type | Modifiers
---------------+-----------------------+-----------
id | integer | not null
nama | character varying(15) |
tanggal_lahir | date |
innamul13650019=# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------+-------+-------------------+--------------------------
public | identitasnim | table | |
(1 row)
innamul13650019=# \d mahasiswa_sementara
Table "pg_temp_3.mahasiswa_sementara"
Column | Type | Modifiers
---------------+-----------------------+-----------
id | integer | not null
nama | character varying(15) |
tanggal_lahir | date |
innamul13650019=# \du
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------+-----------
alfan | Create DB | {}
badz | Create DB | {}
fairuz | Create DB | {}
fairuz13650100 | | {}
innamul | Create DB | {}
ismail | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
syahrul | Create DB | {}
innamul13650019=# revoke all on table identitasNIM from alfan;
REVOKE
innamul13650019=# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privi
leges
--------+--------------+-------+---------------------------+--------------------
------
public | identitasnim | table | postgres=arwdDxt/postgres |
(1 row)
innamul13650019=# grant all on table identitasNIM to innamul;
GRANT
innamul13650019=# \du
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------+-----------
alfan | Create DB | {}
badz | Create DB | {}
fairuz | Create DB | {}
fairuz13650100 | | {}
innamul | Create DB | {}
ismail | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
syahrul | Create DB | {}
innamul13650019=# \l
List of databases
Name | Owner | Encoding | Collate |
Ctype | Access privileges
-----------------+----------+----------+----------------------------+-----------
-----------------+-----------------------
Coba | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
alfan10650046 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
badz10650116 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
fairuz13650100 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
innamul13650019 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
ismail13650047 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
postgres | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
sahrul13650106 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
(10 rows)
innamul13650019=# revoke all on table identitasNIM from postgres;
REVOKE
innamul13650019=# \l
List of databases
Name | Owner | Encoding | Collate |
Ctype | Access privileges
-----------------+----------+----------+----------------------------+-----------
-----------------+-----------------------
Coba | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
alfan10650046 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
badz10650116 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
fairuz13650100 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
innamul13650019 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
ismail13650047 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
postgres | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
sahrul13650106 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_Un
ited States.1252 | =c/postgres +
| | | |
| postgres=CTc/postgres
(10 rows)
innamul13650019=# \du
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------+-----------
alfan | Create DB | {}
badz | Create DB | {}
fairuz | Create DB | {}
fairuz13650100 | | {}
innamul | Create DB | {}
ismail | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
syahrul | Create DB | {}
innamul13650019=# \d identitasNIM
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=# grant all on table identitasNIM to postgres;
GRANT
innamul13650019=# \d identitasNIM
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=#
innamul13650019=# create temporary table Gaji (gaji_kotor integer, pajak integer
, gaji_bersih integer);
CREATE TABLE
innamul13650019=# \d identitasNIM
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
innamul13650019=# create temporary table Gaji (gaji_kotor integer, pajak integer
, gaji_bersih integer)inherits (identitasNIM);
ERROR: relation "gaji" already exists
innamul13650019=# create temporary table Gaji (gaji_kotor integer, pajak integer
, gaji_bersih integer)inherit (identitasNIM);
ERROR: syntax error at or near "inherit"
LINE 1: ...kotor integer, pajak integer, gaji_bersih integer)inherit (i...
^
innamul13650019=# create temporary table Gaji (gaji_kotor integer, pajak integer
, gaji_bersih integer) inherits (identitasNIM);
ERROR: relation "gaji" already exists
innamul13650019=# \d gaji
Table "pg_temp_3.gaji"
Column | Type | Modifiers
-------------+---------+-----------
gaji_kotor | integer |
pajak | integer |
gaji_bersih | integer |
innamul13650019=# drop table gaji;
DROP TABLE
innamul13650019=# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privi
leges
--------+--------------+-------+---------------------------+--------------------
------
public | identitasnim | table | innamul=arwdDxt/postgres +|
| | | postgres=arwdDxt/postgres |
(1 row)
innamul13650019=# create temporary table Gaji (gaji_kotor integer, pajak integer
, gaji_bersih integer) inherits (identitasNIM);
CREATE TABLE
innamul13650019=# \d identitasNIM
Table "public.identitasnim"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
Number of child tables: 1 (Use \d+ to list them.)
innamul13650019=# \d gaji
Table "pg_temp_3.gaji"
Column | Type | Modifiers
------------------+-----------------------+-----------
idn_idn | integer | not null
namadepan_idn | character varying(10) |
namabelakang_idn | character varying(10) |
tgl_lahir_idn | date |
status_idn | boolean |
alamat_idn | text |
gaji_kotor | integer |
pajak | integer |
gaji_bersih | integer |
Inherits: identitasnim
innamul13650019=# revoke all on table identitasNIM from innamul;
REVOKE
innamul13650019=# grant all on table identitasNIM to innamul;
GRANT
innamul13650019=# grant select on table identitasNIM to innamul;
GRANT
innamul13650019=#
Shell 2 (buat ngecek 'grant' dan 'revoke' dari shell pertama):
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]: innamul
Password for user innamul:
psql (9.3.5)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=> \c innamul13650019
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
You are now connected to database "innamul13650019" as user "innamul".
innamul13650019=> select * from identitasnim
innamul13650019->
innamul13650019-> ;
idn_idn | namadepan_idn | namabelakang_idn | tgl_lahir_idn | status_idn | alama
t_idn
---------+---------------+------------------+---------------+------------+------
------
(0 rows)
innamul13650019=> select * from identitasnim;
ERROR: permission denied for relation identitasnim
innamul13650019=> select * from identitasnim;
idn_idn | namadepan_idn | namabelakang_idn | tgl_lahir_idn | status_idn | alama
t_idn
---------+---------------+------------------+---------------+------------+------
------
(0 rows)
innamul13650019=> select * from identitasnim;
idn_idn | namadepan_idn | namabelakang_idn | tgl_lahir_idn | status_idn | alama
t_idn
---------+---------------+------------------+---------------+------------+------
------
(0 rows)
innamul13650019=>
Database [postgres]:
Port [5432]:
Username [postgres]: innamul
Password for user innamul:
psql (9.3.5)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=> \c innamul13650019
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
You are now connected to database "innamul13650019" as user "innamul".
innamul13650019=> select * from identitasnim
innamul13650019->
innamul13650019-> ;
idn_idn | namadepan_idn | namabelakang_idn | tgl_lahir_idn | status_idn | alama
t_idn
---------+---------------+------------------+---------------+------------+------
------
(0 rows)
innamul13650019=> select * from identitasnim;
ERROR: permission denied for relation identitasnim
innamul13650019=> select * from identitasnim;
idn_idn | namadepan_idn | namabelakang_idn | tgl_lahir_idn | status_idn | alama
t_idn
---------+---------------+------------------+---------------+------------+------
------
(0 rows)
innamul13650019=> select * from identitasnim;
idn_idn | namadepan_idn | namabelakang_idn | tgl_lahir_idn | status_idn | alama
t_idn
---------+---------------+------------------+---------------+------------+------
------
(0 rows)
innamul13650019=>
MySQL (SQLyog) >>
Evaluasi Perbandingan DBMS
Pada postingan sebelumnyaada kecelakaan, gara-gara Dosennya post ke facebook ginian “Evaluasi Perbandingan 2 DBMS yaitu PostgreSQL dan MySQL. baik dari kemudahan atau kelebihan dan kelemahannya”, rata-rata temenku plus aku sendiri jadi bingung gimana maksudnya, dan akhirnya semua pada buat kelebihan dan kekurangan kedua DBMS secara umum sampe beberapa hari kemudianbaru ada yang jelasin kalo yang dimaksud adalah perbedaan query yang dipake disetiap DBMS, kalo secara umum sampe akhir modul pun ngga ada bedanya ya, -_-
Walaupun satu keluarga, tapi antar dua adik-kakak (DBMS) ini tetap aja punya perbedaan, soal pertama sih masih mirip-mirip, tapi pas di pertengahan soal baru punya beda, ini dia perbedaannya
INSTRUKSI | MySQL | PostgreSQL |
Rename Column | alter table namatable change namakolomasal namakolombaru tipedata; | alter table namatablerename column “namakolom asal” to namakolombaru; |
Struktur table | desc namatable; | \d namatabel; |
Revoke | revoke all on table namatabel from ‘namauser’@’host’; | revoke all on table namatabel from namauser; |
Grant | grant all on table namatabel to ‘namauser’@’host’; | grant all on table namatabel to namauser; |
Revoke select | revoke select on tabel namatabel from ‘namauser’@’host’; | revoke select on tabel namatabel from namauser; |
Melihat Hak Akses User | show grants for ‘namauser’@’host’ | buka shell baru dan login dengan user lain |
Kesimpulan
Sistem database sangat dibutuhkan dalam berbagai sistem yang berkaitan dengan pendataan, misalnya saja sistem pandataan pasien rumah sakit, sistem parkir dan lain sebagainya, dibanding menggunakan cara manual seperti menulis di buku, cara elektronik seperti ini jelas lebih mempermudah user maupun pengelola sistem itu sendiri, oleh karena itu pembuatan partikel-partikel sistem database seperti pembuatan tabel sangat perlu untuk dipelajari.
Dan dalam pembuatan tabel seperti diatas, jika kamu ingin menggunakan PostgreSQL atau MySQL, itu ngga masalah selama kamu serius ingin mempelajari dan menguasainya, dan lebih baik lagi jika kamu bisa menguasai kedua-duanya. Tapi kalo pendapat aku sendiri sih lebih enakan pake MySQL, tapi yang SQLyog loh, SQLyog itu seperti apa ya, aku juga bingung, pokoknya sejenis connectoryang menghungkan kita ke MySQL yang biasanya hanya bisa diakses lewat browser dengan localhost gitu, jadi si SQLyog bakal mempermudah kita dan dengan tampilan yang bagus tentunya, dan semua query, database dan user yang kita buat di SQLyog bakal otomatis terupload ke browser dan kalo mau dilanjutkan di browser juga bisa kok.
Tugas ERD II
Lanjutan dari tugas sebelumnya yaitu : membuat tabel dari ERD yang telah aku buat kemarin di
Laporan Praktikum Desain Basis Data I, pembuatan tabel berikut menggunakan MySQL dan output tabelnya seperti gambar-gambar dibawah
Kamu senang atau engga silakan tinggalkan komentar dibawah, kalo mau kopas bilang-bilang dulu sama akunya, biar aku bisa mikir “kasih ato engga”, persentase keberhasilan tergantung mood, kalo abis makan gratis biasanya mood-nya aku bakal bagus, 3:)
No comments:
Post a Comment