query mysql collection

Query Mysql Collection

Kumpulan perintah mysql (sql script) yang dikumpulkan saat ada kejadian,, haha ,, maksudnya saat ada kejadian ketika membuat query dan atau membuat struktur table, yang ditulis secara terus menerus insyaa allah :) , berikut adalah contoh sql script yang dikumpulkan saat melakukan pembangunan struktur table untuk membangun database yang sedang dikembangkan.

Alter table add foreign key

alter table users add column cabang_id bigint(20) unsigned;

alter table users add foreign key (cabang_id) references cabangs(id);

Drop Column Foreign Key 

Bagaimana cara menghapus foreign key dari sebuah table? misalnya ada table barang dengan foreign key cabang_id, cara untuk menghapus column yang bersifat foreign key adalah:
(1) Hapus terlebih dahulu foreign key nya pada tabel barang, perintahnya adalah:

ALTER TABLE barangs DROP FOREIGN KEY nama_foreign_key;

untuk mengetahui nama_foreign_key, jalankan perintah sql:

show create table barangs
akan terlihat nama foreign key pada perintah sql untuk pembuatan tabel
alter table barangs drop foreign key barangs_cabang_id_foreign;
(2) setelah menghapus foreign key berhasil, jalankan perintah menghapus column

alter table barangs drop column cabang_id;

Create Database berdasarkan Character Set

create database utf8_db character set = 'utf8';

Alter Database Ubah Character Set ?

alter database utf8_db character set = 'utf8mb4';

Bagaimana Delete Duplicate Row ?

Sebagai contoh kita punya data pada table pasien yang namanya ada beberapa yg double bahkan ada yang 1 nama pasien mempunyai 3 row data pada table tersebut, karena secara struktur table memang belum ada contraint unique pada kolom nama tersebut, contoh query di bawah ini adalah cara menghapus data yang duplicate tersebut

DELETE pasien1 FROM pasiens pasien1
INNER JOIN pasiens pasien2 
WHERE pasien1.id < pasien2.id AND pasien1.nama = pasien2.nama;

Drop Unique Contraint

Bagaimana cara menghapus unique constraint yang ada pada table ? Gunakan perintah drop index terhadap table tersebut, perintah tersebut digabungkan dengan alter table, sebagai contoh ada table master_obat yang awalnya mempunyai kode yang sifatnya unique, ketika kita akan menghapus unique contraint tersebut, maka jalankan perintah ini:
alter table master_obat drop index kode

Merubah Nama Table

Untuk merubah nama table jalankan perintah sql berikut ini:
RENAME TABLE table1 TO tabel2;

Merubah Posisi Kolom

ALTER TABLE returs
   CHANGE resep_id resep_id bigint(20) unsigned NOT NULL AFTER total;

alter table tb_nilai add column bobot decimal(3,2) unsigned after nilai

menambahkan kolom bobot dengan tipe decimal, 2 angka di belakang koma dan total angka antara di depan dan di belakang koma adalah 3, jadi wajib 1 angka di depan koma jika ada 2 angka di belakang koma, kolom yang baru diletakkan setelah kolom nilai.

Alter Table TIMESTAMP Default CURRENT_TIMESTAMP

Merubah tipde data kolom tanggal yang sebelumnya bertipe date kemudian diubah menjadi timestamp dan diset bagian defaultnya adalah current_timestamp, yang mana kolom ini akan diisi oleh mysql/mariadb ketika row / baris dibuat

ALTER TABLE kwitansis change `tanggal` `tanggal` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

jika script tersebut diterapkan pada migration laravel maka akan seperti ini :

$table->date('tanggal')->default(DB::raw('CURRENT_TIMESTAMP'));

Alter Table Enum With Default Value

Berikut ini adalah script sql yang digunakan untuk mengubah kolom status dengan tipe data enum, dan ditambahkan settingan default dengan nilai DAFTAR, sehingga ketika data pertama kami dibuat maka kolom status akan berisi nilai DAFTAR
alter table pendaftarans modify column status enum('DAFTAR','BAYAR_DAFTAR','DU','BTL_AFTER_DU') default 'DAFTAR'

Alter Table Add Column

Ketika akan menambahkan 1 kolom pada tabel yang sudah ada, gunakan perintah alter table add column diikuti dengan nama_kolom kemudian tipe data dari kolom tersebut, contoh akan ditambahkan kolom jurusan_id pada tabel pendaftarans, perintahnya adalah sebagai berikut :

alter table pendaftarans add COLUMN jurusan_id bigint(20) unigned

Kenapa mana kolomnya jurusan_id ? karena ini akan dijadikan sebagai foreig key pada tabel pendaftarans dan akan direferensikan terhadap tabel lain yaitu tabel jurusans.

Alter Table Add Foreign Key

Seperti disebutkan  pada kalimat sebelumnya, kolom jurusan_id akan dijadikan sebagai foreign key yang akan menghubungkan table pendaftarans dengan kolom jurusan, untuk menambahkan foreign key gunakan perintah ini :

alter table pendaftarans add FOREIGN key (jurusan_id) REFERENCES jurusans (id)

Alter Table Change Column Name

Bagaimana cara mengganti nama kolom pada mariadb menggunakan perintah alter table, untuk jalan yang panjang bisa dilakukan caranya : drop column kemudian add column lagi, tapi lebih ringkas dengan perintah change column, seperti perintah di bawah ini :

alter table barangs change column is_activee is_active enum('y','n')

merubah nama kolom is_activee menjadi is_active dengan tipe enum.

Update Join

Misalnya beberapa table yang saling berhubungan dengan foreig key, bagaimana cara melakukan update table dengan join ?
Contoh : pada table kartu_rencana_studi terdapat kolom sks yang nilainya null, sedangkan data sks tersebut berada pada tabel kurikulum, berikut ini adalah contoh skrip sql untuk melakukan update data sks tersebut
update kartu_rencana_studi as krs
join sebaran_details d on d.id = krs.sebaran_detail_id
join kurikulums k on k.id = d.kurikulum_id
set krs.sks = k.sks
where krs.sks is null 

Update Join If Condition

Berikut ini adalah contoh query sql untuk melakukan update data dengan join table dan menggunakan set data dengan kondisi CASE WHEN
update kartu_rencana_studi as krs
join sebaran_details d on d.id = krs.sebaran_detail_id
join kurikulums k on k.id = d.kurikulum_id
set krs.bobot =
CASE
WHEN krs.nilai = 'A' THEN 4
WHEN krs.nilai = 'AB' THEN 3.5
WHEN krs.nilai = 'B' THEN 3
WHEN krs.nilai = 'BC' THEN 2.5
WHEN krs.nilai = 'C' THEN 2
WHEN krs.nilai = 'D' THEN 1
ELSE 0
END
where krs.bobot is null

Alter Tabel Default

alter table kuisioners modify column nilai tinyint(3) unsigned default 0 perintah sql tersebut digunakan untuk merubah / menambah nilai default terhadap kolom nilai

Drop constrain foreign key ALTER TABLE bayars DROP FOREIGN KEY bayars_ibfk_1

Bersambung