Latihan Database 04

• Menampilkan database

mysql> show databases;

• Membuat database

mysql> create database pjual;

(catatan : membuat database dengan nama pjual)

• Menghapus database

mysql> drop database barang;

(catatan : menghapus database dgn nama barang)

• Mengaktifkan database

mysql> use pjual;

(catatan : memilih database dengan nama pjual)

• Menampilkan tabel

mysql> show tables;

• Membuat tabel

mysql> create table detrans(no_fak varchar(3), ko_brg varchar(3), jlh_brg int(3));

mysql> create table xpjual.detrans(no_fak varchar(3), ko_brg varchar(3), jlh_brg

        ->  int(3));

• Menampilkan struktur tabel

mysql> describe detrans;

• Mengcopy tabel

mysql> create table xbarang select * from barang;

 (catatan : barang = nama tabel yang dicopy, xbarang = nama tabel hasil copy)

• Mengcopy tabel ke database non aktif

mysql> create table xpjual.xbarang select * from barang;

 (catatan : barang = nama tabel yang dicopy, xbarang = nama tabel hasil copy, xpjual = nama database)

• Mengcopy tabel dari database non aktif

mysql> create table pbarang select * from xpjual.barang;

 ( catatan : xpjual = nama database, xbarang = nama tabel yang dicopy, pbarang = nama tabel hasil copy)

•Merubah nama tabel

mysql> alter table pbarang rename jualbrg;

 (catatan : pbarang = nama tabel yang diganti namanya, jualbrg = nama tabel pengganti)

•Menghapus tabel

mysql> drop table jualbrg;

(catatan : jualbrg = nama tabel yang dihapus)

•Menambah field (kolom)

mysql> alter table xbarang add jenis varchar(20);

•Merubah nama field (kolom)

mysql> alter table xbarang change jenis kategori varchar(20);

•Menghapus field (kolom)

mysql> alter table xbarang drop kategori;

•Merubah tipe data

mysql> alter table xbarang modify harga int(8);

•Merubah ukuran field (kolom)

mysql> alter table xbarang modify harga int(10);

•Merubah NULL menjadi NOT NULL dan sebaliknya

mysql> alter table xbarang modify nama varchar(20) not null;

(catatan : merubah field nama menjadi not null)

mysql> alter table xbarang modify nama varchar(20);

(catatan : merubah field nama menjadi null)

•Membuat Primary Key

mysql> alter table xbarang add primary key (kode);

•Menghapus Primary Key

mysql> alter table xbarang drop primary key;

•Membuat Index

mysql> alter table xdetrans add index kobrg_ndx(ko_brg);     

ATAU

mysql> create index kobrg_ndx on xdetrans(ko_brg);          

•Menghapus Index

mysql> alter table xdetrans drop index kobrg_ndx;

•Membuat Unique

mysql> alter table xbarang add unique id_uni(id);

(catatan : buat kode sebagai primary key)

•Menghapus Unique

mysql> alter table xbarang drop index id_uni;

•Merubah Default

mysql> alter table xbarang alter nama set default 'KULKAS';

 (catatan : field yang Not Null dan default tidak ada, bisa tidak diisi dan field tersebut tetap kosong)

•Menghapus Default

mysql> alter table xbarang alter nama drop default;

•Menampilkan isi tabel barang

mysql> select * from barang;

 (catatan : pilih database pjual)

•Mengisi/menambah data ke tabel barang

mysql> insert into barang(kode,nama,harga,stok) values('A11','VIDEO',500000,2);

ATAU

mysql> insert into barang values('A12','RADIO',250000,2);

•Mengcopy tabel

mysql> create table tbarang select * from barang;

•Mengcopy struktur tabel

mysql> create table tdetrans select * from detrans where no_fak=' ';

•Mengcopy data

mysql> insert into tdetrans select * from detrans;

ATAU

mysql> insert into tdetrans select * from tdetrans where no_fak='001';

Merubah Data

mysql> update tbarang set harga=600000

       –> where kode='A11';

ATAU

mysql> update tbarang set nama='KULKAS',

       –> harga=1500000 where kode='A12';

ATAU

mysql> update tbarang set kode='B22',

       –> nama='SANDAL', harga=100000

       –> where kode='B21';

ATAU

mysql> update tbarang set harga=harga*2;

ATAU

mysql> update tdetrans set jlh_brg=4 where

       –> no_fak='001' and ko_brg='A11';

             

Menghapus Data

mysql> delete from tdetrans

       –> where no_fak='002';

ATAU

mysql> delete from tdetrans where

       –> no_fak='003' and ko_brg='A11';

ATAU

mysql> delete from tdetrans where

       –> no_fak='003' or no_fak='005';

ATAU

mysql>  delete from tdetrans where (no_fak='004' or no_fak='006') and jlh_brg=2;

ATAU

mysql> delete from tdetrans;

mysql> select * from detrans;

mysql> select no_fak, ko_brg from detrans;

mysql> select no_fak as 'No. Faktur',

       –> ko_brg as 'Kode Barang',

       –> jlh_brg as 'Jumlah' from detrans;

mysql> select no_fak as nofak, ko_brg as kobrg,

       –> jlh_brg as jumbrg from detrans;

mysql> select distinct ko_brg from detrans;

mysql> select distinct ko_brg, jlh_brg

       –> from detrans;

mysql> select * from detrans limit 5;

mysql> select * from detrans limit 3,5;

mysql> select * from detrans

       –> where ko_brg='A11';

mysql> select * from detrans

       –> where ko_brg='A11' and jlh_brg=1;

mysql> select * from detrans where

       –> ko_brg='A11' or ko_brg='B21';

mysql> select * from detrans where

       –>(ko_brg='A11' or ko_brg='B21')

       –> and jlh_brg>1;

mysql> select * from transaksi

       –> where tanggal='2006-01-02';

mysql> select * from transaksi

       –> where tanggal>='2006-01-02';

mysql> select * from transaksi

       –> where tanggal>='2006-01-02'

       –> and tanggal<='2006-01-10';

mysql> select * from transaksi where tanggal

       –> between '2006-01-02' and '2006-01-10';

mysql> select * from detrans

       –> where ko_brg like '%2%';

mysql> select count(*) from detrans;

mysql> select ko_brg, sum(jlh_brg)

       –> from detrans group by ko_brg;

mysql> select ko_brg, sum(jlh_brg)

       –> from detrans group by ko_brg

       –> having sum(jlh_brg)>5;

mysql> select ko_brg, sum(jlh_brg) as jumlah

       –> from detrans group by ko_brg having

       –> sum(jlh_brg)>5 order by jumlah;

•Left Join dan Right Join

Query semua data dari sebuah tabel dan membatasi data dari tabel lainnya

mysql> select no_fak, nama, harga, jlh_brg

       –> from barang left join detrans

       –> on kode=ko_brg order by no_fak;

mysql> select no_fak, nama, harga, jlh_brg

       –> from detrans right join barang

       –> on ko_brg=kode order by no_fak;

mysql> select no_fak, nama, harga, jlh_brg

       –> from detrans left join barang

       –> on ko_brg=kode order by no_fak;

mysql> select kode, nama, sum(jlh_brg) as jumlah from barang left join detrans

       –> on kode=ko_brg group by ko_brg

       –> order by kode;

•Equa Join

Query data yang memiliki pasangan di kedua tabel, bila tidak ada pasangan data tersebut tidak di query.

mysql> select no_fak, nama, harga, jlh_brg

       –> from detrans, barang

       –> where ko_brg=kode order by no_fak;

mysql> select transaksi.no_fak, transaksi.tanggal,

       –> detrans.ko_brg, detrans.jlh_brg

       –> from transaksi, detrans

       –> where transaksi.no_fak=detrans.no_fak;

mysql> select t.no_fak, t.tanggal, d.ko_brg, d.jlh_brg from transaksi as t,

       –> detrans as d where t.no_fak=d.no_fak;

mysql> select t.no_fak, t.tanggal, d.ko_brg,

       –> d.jlh_brg from transaksi t, detrans d where t.no_fak=d.no_fak;

mysql> select t.no_fak, t.tanggal, b.nama,b.harga, d.jlh_brg from

       –> transaksi t, detrans d, barang b where t.no_fak=d.no_fak and

       –> d.ko_brg=b.kode;

mysql> select d.no_fak, b.nama, b.harga,

       –> d.jlh_brg, b.harga*d.jlh_brg as hartot

       –> from detrans d, barang b

       –> where ko_brg=kode;

mysql> select d.no_fak, b.nama, b.harga,

       –> d.jlh_brg, b.harga * d.jlh_brg as hartot,

       –> if(b.harga*d.jlh_brg>500000,0.1*b.harga*d.jlh_brg,0) as diskon

       –> from detrans d, barang b

      –> where d.ko_brg=b.kode;

•Sub Query

mysql> select * from transaksi where no_fak >

       –>(select count(*) from transaksi)-3;

mysql> select * from (select * from detrans

       –> where ko_brg='B21') as dt

       –> where jlh_brg>1;

mysql> select * from detrans where jlh_brg >

       –> any (select stok from barang);

(catatan : any adalah memilih stok yang terkecil dari hasil select stok from barang)

mysql> select * from detrans where jlh_brg >

       –> some (select stok from barang);

(catatan : some sama dengan any)

mysql> select * from detrans where jlh_brg >

       –> all (select stok from barang);

(catatan : all adalah memilih stok yang terbesar dari hasil select stok from barang)

mysql> select * from detrans where jlh_brg

       –>= any (select stok from barang);

mysql> select * from detrans where jlh_brg

       –> in (select stok from barang);

(catatan : in sama dgn = any, not in sama dgn <> any)

mysql> select no_fak, sum(jlh_brg) as jumlah

       –> from detrans group by no_fak having

       –> sum(jlh_brg) > (select sum(jlh_brg) from

       –> detrans where no_fak='002' group by

       –> no_fak);

Administrasi User

•Login melalui username root

c:\xampp\mysql\bin>mysql -u root –p

•Menggunakan database mysql

mysql> use mysql;

•Menampilkan tabel-tabel database mysql

mysql> show tables;

•Menampilkan struktur tabel user

mysql> describe user;

atau

mysql> describe user \G

•Menampilkan struktur tabel host

mysql> describe host;

•Menampilkan struktur tabel db

mysql> describe db;

•Menampilkan struktur tabel tables_priv

mysql> describe tables_priv \G;

•Menampilkan struktur tabel columns_priv

mysql> describe columns_priv \G;

•Menampilkan isi tabel user

mysql> select user, host, password from user;

mysql> select * from user \G;

mysql> select * from user where user='root' \G

•Mendefinisikan user dan hak ke semua database

mysql> insert into user(host, user, password, select_priv,insert_priv)

       –> values('localhost', 'ti..cb1',password('ti..cb1'),’Y’,’Y’);

(catatan ; haknya hanya select dan insert ke semua database)

mysql> flush privileges;

mysql> quit;

(catatan : menyesuaikan/menerapkan perubahan user name dan privilege(hak) pada server MySQL untuk pengujian)

c:\xampp\mysql\bin >mysql –u ti..cb1 –p

Enter password: *******

•Merubah username dan password

mysql> update user set user='ti..ak1', password= password('ti..ak1') where

       –> user='ti..cb1';

 (catatan : login kembali melalui user root dan gunakan database mysql)

•Menambah hak (update dan delete)

mysql> update user set update_priv='Y', delete_priv='Y'  where user='ti..ak1';

•Menghapus hak (insert dan update)

mysql> update user set insert_priv='N', update_priv='N' where user='ti..ak1';

• Mendefinisikan user dan hak ke database tertentu

mysql> insert into user(host, user, password)

       –> values('localhost','ti..ak2',

       –> password('ti..ak2'));

mysql> insert into db(host, db, user, select_priv, delete_priv)

       –> values('localhost','pjual', 'ti..ak2','Y',’Y’);

 (catatan : haknya hanya select dan delete ke semua tabel pada database pjual)

•Menambah hak (insert dan update)

mysql> update db set insert_priv='Y', update_priv=’Y’

       –> where user='ti..ak2' and db='pjual';

 (catatan : haknya insert dan update ke semua tabel pada database pjual)

•Menghapus hak (update dan delete)

mysql> update db set update_priv='N', delete_priv=’N’

       –> where user='ti..ak2' and db='pjual';