belajar edit tabel dan struktur tabel mysql

Ada saatnya dimana kita harus mengganti struktur table yang pernah kita buat. Untungnya MySql mendukung/menyediakan perintah untuk melakukan kegiatan tersebut. Perintahnya adalah ALTER TABLE. Penggunaanya sedikit sulit. Mudah-mudahan penjelasan saya bisa menambah pemahaman supaya lebih mudah untuk dipelajari. Bentuk umum perintahnya adalah sebagai berikut :

ALTER TABLE nama_tabel alter_options;

dimana :

• ALTER TABLE merupakan perintah dasar untuk mengubah tabel.
• nama_tabel merupakan nama tabel yang akan diubah strukturnya.
• alter_options merupakan pilihan perubahan tabel. Contoh Option yang bisa digunakan adalah sebagai berikut:
  

  Nama Option	Fungsi Option
  ADD nama_field/kolom_baru	untuk menambahkan field/kolom baru.
  ADD PRIMARY KEY (nama_field/kolom)	untuk menambahkan primary key pada tabel
  CHANGE nama_field_lama nama_field_baru_definisi_field_baru	untuk mengubah nama_field_lama menjadi nama_field_baru_definisi_field_baru definisi_field_baru artinya adalah type data.
  MODIFY nama_field type_data	untuk mengubah type_data suatu field
  DROP nama_field	untuk menghapus field nama_field
  RENAME TO nama_tabel_baru	untuk mengganti nama tabel

  Sekarang saatnya kita bahas satu persatu supaya bisa paham. Sebelumnya, buatlah sebuat table terlebih dahulu untuk percobaan. Untuk mempelajari CREATE TABLE lebih lanjut bisa dilihat di sini.
  Misal di sini kita buat table sederhana dengan nama peserta dan strukturnya sebagai berikut :
  

  Nama Kolom	Type data	Panjang Data	Keterangan
  NIP	integer	5	bukan primary key bukan auto increment
  nama	varchar	30

  Perintahnya adalah sebagai berikut :
  

  CREATE TABLE peserta (
  NIP int(5) NOT NULL,
  nama varchar(30) NOT NULL);

  *sengaja tidak di beri “primary key” dan “auto increment”
  seharusnya jika diberi perintah
  

  DESC peserta;

  maka hasilnya adalah sebagai berikut :
  

  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | NIP   | int(5)      | NO   |     | NULL    |       |
  | nama  | varchar(30) | NO   |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.01 sec)
  

  Karena perintah alter table terdapat banyak sekali variasinya, maka saya akan membahas beberapa saja yang sering saya gunakan. Panjang banget dong..?? Betul sekali. Tenang saja, saya sudah membuat link sehingga memudahkan untuk belajar. Berikut adalah link-link-nya. Silahkan dinikmati :
  
  • Tambah Field/Kolom Baru
  • Tambah/hapus Primary Key
  • Hapus Field/Kolom
  • Ubah Nama Field/Kolom
  • Ubah Type Data Suatu Field/Kolom
  • Ganti Nama Tabel

  Tambah Field/Kolom Baru.

  Misal kita ingin menambah Field/Kolom alamat pada table di atas, maka perintahnya adalah :
  

  ALTER TABLE peserta ADD alamat varchar(30);

  Setelah itu, ketik lagi perintah “DESC peserta;”, seharusnya hasilnya adalah sebagai berikut:
  

  +--------+-------------+------+-----+---------+-------+
  | Field  | Type        | Null | Key | Default | Extra |
  +--------+-------------+------+-----+---------+-------+
  | NIP    | int(5)      | NO   |     | NULL    |       |
  | nama   | varchar(30) | NO   |     | NULL    |       |
  | alamat | varchar(30) | YES  |     | NULL    |       |
  +--------+-------------+------+-----+---------+-------+
  3 rows in set (0.01 sec)
  

  Field/Kolom alamat sudah terbentuk. Timbul pertanyaan bagaimana menambah table telepon di tengah-tengah antara kolom nama dan alamat. Maka perintahnya adalah :
  

  ALTER TABLE peserta ADD telepon varchar(30) AFTER nama;

  Kita bisa menggunkan kata AFTER untuk membantu menambahkan kolom sesuai dengan yang anda inginkan. Kata BEFORE tidak bisa digunakan. Hasilnya adalah sebagai berikut setelah ketik perintah ” DESC peserta; “
  

  +---------+-------------+------+-----+---------+-------+
  | Field   | Type        | Null | Key | Default | Extra |
  +---------+-------------+------+-----+---------+-------+
  | NIP     | int(5)      | NO   |     | NULL    |       |
  | nama    | varchar(30) | NO   |     | NULL    |       |
  | telepon | varchar(30) | YES  |     | NULL    |       |
  | alamat  | varchar(30) | YES  |     | NULL    |       |
  +---------+-------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  

  Terlihat bahwa kolom telepon sudah berhasil ditambahkan.

  Tambah/hapus Primary Key

  Untuk menambah Primary Key pada table, perintahnya adalah:
  

  ALTER TABLE nama_table ADD PRIMARY KEY (nama_kolom);

  Contoh dan hasilnya adalah sebagai berikut :
  

  mysql> ALTER TABLE peserta ADD PRIMARY KEY (NIP);
  Query OK, 0 rows affected (0.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> desc peserta;
  +---------+-------------+------+-----+---------+-------+
  | Field   | Type        | Null | Key | Default | Extra |
  +---------+-------------+------+-----+---------+-------+
  | NIP     | int(5)      | NO   | PRI | NULL    |       |
  | nama    | varchar(30) | NO   |     | NULL    |       |
  | telepon | varchar(30) | YES  |     | NULL    |       |
  | alamat  | varchar(30) | YES  |     | NULL    |       |
  +---------+-------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  

  Untuk hapus Primary Key pada table, perintahnya adalah:
  

  ALTER TABLE nama_table DROP PRIMARY KEY;

  Contoh dan hasilnya adalah sebagai berikut :
  

  mysql> ALTER TABLE peserta DROP PRIMARY KEY;
  Query OK, 0 rows affected (0.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> desc peserta;
  +---------+-------------+------+-----+---------+-------+
  | Field   | Type        | Null | Key | Default | Extra |
  +---------+-------------+------+-----+---------+-------+
  | NIP     | int(5)      | NO   |     | NULL    |       |
  | nama    | varchar(30) | NO   |     | NULL    |       |
  | telepon | varchar(30) | YES  |     | NULL    |       |
  | alamat  | varchar(30) | YES  |     | NULL    |       |
  +---------+-------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  

  Hapus Field/Kolom

  Bentuk perintahnya adalah sebagai berikut :
  

  ALTER TABLE nama_tabel DROP COLUMN nama_kolom;
  

  Misalnya kita ingin menghapus kolom telepon, maka contohnya bisa dilihat seperti di bawah ini:
  

  mysql> desc peserta;
  +---------+-------------+------+-----+---------+-------+
  | Field   | Type        | Null | Key | Default | Extra |
  +---------+-------------+------+-----+---------+-------+
  | NIP     | int(5)      | NO   |     | NULL    |       |
  | nama    | varchar(30) | NO   |     | NULL    |       |
  | telepon | varchar(30) | YES  |     | NULL    |       |
  | alamat  | varchar(30) | YES  |     | NULL    |       |
  +---------+-------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  
  mysql> ALTER TABLE peserta DROP COLUMN telepon;
  Query OK, 0 rows affected (0.09 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> desc peserta;
  +--------+-------------+------+-----+---------+-------+
  | Field  | Type        | Null | Key | Default | Extra |
  +--------+-------------+------+-----+---------+-------+
  | NIP    | int(5)      | NO   |     | NULL    |       |
  | nama   | varchar(30) | NO   |     | NULL    |       |
  | alamat | varchar(30) | YES  |     | NULL    |       |
  +--------+-------------+------+-----+---------+-------+
  3 rows in set (0.01 sec)
  

  Ubah Nama Field/Kolom

  Mengubah nama kolom/field dapat menggunakan option change. Sebelumnya, kita buat kolom baru dulu bernama jenis_kelamin dengan jenis data “char” yang berjumlah 1 dan diletakkan setelah kolom alamat.
  

  mysql> alter table peserta add jenis_kelamin char(1) after alamat;
  Query OK, 0 rows affected (0.10 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> desc peserta;
  +---------------+-------------+------+-----+---------+-------+
  | Field         | Type        | Null | Key | Default | Extra |
  +---------------+-------------+------+-----+---------+-------+
  | NIP           | int(5)      | NO   |     | NULL    |       |
  | nama          | varchar(30) | NO   |     | NULL    |       |
  | alamat        | varchar(30) | YES  |     | NULL    |       |
  | jenis_kelamin | char(1)     | YES  |     | NULL    |       |
  +---------------+-------------+------+-----+---------+-------+
  4 rows in set (0.07 sec)
  

  Setelah berhasil, kita rubah kolom jenis_kelamin tersebut menjadi sex.Berikut ini contoh perintahnya:
  

  mysql> alter table peserta change jenis_kelamin sex char(1);
  Query OK, 0 rows affected (0.01 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> desc peserta;
  +--------+-------------+------+-----+---------+-------+
  | Field  | Type        | Null | Key | Default | Extra |
  +--------+-------------+------+-----+---------+-------+
  | NIP    | int(5)      | NO   |     | NULL    |       |
  | nama   | varchar(30) | NO   |     | NULL    |       |
  | alamat | varchar(30) | YES  |     | NULL    |       |
  | sex    | char(1)     | YES  |     | NULL    |       |
  +--------+-------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  

  Ubah Type Data Field/Kolom

  Tadi kita sudah membuat kolom/field baru bernama sex. Kita ingin membuat kolom/field tersebut hanya terisi oleh karakter “L” untuk laki-laki dan “P” untuk perempuan. Jadi kita merubah dari type data char(1) menjadi enum (‘L’,'P’). Kita bisa mengubah hal tersebut dengan perintah MODIFY. Berikut ini adalah contohnya :
  

  mysql> desc peserta;
  +--------+-------------+------+-----+---------+-------+
  | Field  | Type        | Null | Key | Default | Extra |
  +--------+-------------+------+-----+---------+-------+
  | NIP    | int(5)      | NO   |     | NULL    |       |
  | nama   | varchar(30) | NO   |     | NULL    |       |
  | alamat | varchar(30) | YES  |     | NULL    |       |
  | sex    | char(1)     | YES  |     | NULL    |       |
  +--------+-------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  
  mysql> alter table peserta modify sex enum('L','P') not null;
  Query OK, 0 rows affected (0.11 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> desc peserta;
  +--------+---------------+------+-----+---------+-------+
  | Field  | Type          | Null | Key | Default | Extra |
  +--------+---------------+------+-----+---------+-------+
  | NIP    | int(5)        | NO   |     | NULL    |       |
  | nama   | varchar(30)   | NO   |     | NULL    |       |
  | alamat | varchar(30)   | YES  |     | NULL    |       |
  | sex    | enum('L','P') | NO   |     | NULL    |       |
  +--------+---------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  

  Skenario selanjutnya adalah : kita ingin merubah NIP menjadi auto_increment. Hal tersebut bisa kita lakukan dengan option modify dengan catatan yang di-auto_increment-kan harus sebagai primary key. Langkahnya ada 2 tahap, yaitu :
  

  • Ubah kolom NIP menjadi primary key

  mysql> desc peserta;
  +--------+---------------+------+-----+---------+-------+
  | Field  | Type          | Null | Key | Default | Extra |
  +--------+---------------+------+-----+---------+-------+
  | NIP    | int(5)        | NO   |     | NULL    |       |
  | nama   | varchar(30)   | NO   |     | NULL    |       |
  | alamat | varchar(30)   | YES  |     | NULL    |       |
  | sex    | enum('L','P') | NO   |     | NULL    |       |
  +--------+---------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  
  mysql> alter table peserta add primary key (NIP);
  Query OK, 0 rows affected (0.05 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> desc peserta;
  +--------+---------------+------+-----+---------+-------+
  | Field  | Type          | Null | Key | Default | Extra |
  +--------+---------------+------+-----+---------+-------+
  | NIP    | int(5)        | NO   | PRI | NULL    |       |
  | nama   | varchar(30)   | NO   |     | NULL    |       |
  | alamat | varchar(30)   | YES  |     | NULL    |       |
  | sex    | enum('L','P') | NO   |     | NULL    |       |
  +--------+---------------+------+-----+---------+-------+
  4 rows in set (0.01 sec)
  

  • Seting kolom NIP menjadi auto_increment

  mysql> alter table peserta modify NIP int(5) not null auto_increment;
  Query OK, 1 row affected (0.05 sec)
  Records: 1  Duplicates: 0  Warnings: 0
  
  mysql> desc peserta;
  +--------+---------------+------+-----+---------+----------------+
  | Field  | Type          | Null | Key | Default | Extra          |
  +--------+---------------+------+-----+---------+----------------+
  | NIP    | int(5)        | NO   | PRI | NULL    | auto_increment |
  | nama   | varchar(30)   | NO   |     | NULL    |                |
  | alamat | varchar(30)   | YES  |     | NULL    |                |
  | sex    | enum('L','P') | NO   |     | NULL    |                |
  +--------+---------------+------+-----+---------+----------------+
  4 rows in set (0.01 sec)
  

  Misalnya kita ingin men-SET auto_increment menjadi 11111 sebagai angka awal, karena kita sudah set auto_increment sebelumnya, kita bisa melakukannya sebagai berikut :

  mysql> alter table peserta auto_increment=11111;
  Query OK, 0 rows affected (0.01 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> insert into peserta values ('','testing','klaten bersinar','L');
  Query OK, 1 row affected, 1 warning (0.05 sec)
  
  mysql> select * from peserta;
  +-------+---------+-----------------+-----+
  | NIP   | nama    | alamat          | sex |
  +-------+---------+-----------------+-----+
  | 11111 | testing | klaten bersinar | L   |
  +-------+---------+-----------------+-----+
  1 row in set (0.00 sec)
  

  Karena sudah auto_increment, maka kolom NIP bisa dikosongkan dan akan terisi otomatis sesuai angka yang kita SET sebelumnya.

  Ganti Nama Tabel

  mengganti nama table bisa memakai perintah alter table juga lho.. Syntax-nya adalah sebagai berikut ini :
  

  ALTER TABLE nama_table_lama RENAME TO nama_table_baru;
  

  berikut ini contoh before-afternya
  

  mysql> show tables;
  +----------------+
  | Tables_in_coba |
  +----------------+
  | peserta        |
  +----------------+
  1 row in set (0.00 sec)
  
  mysql> alter table peserta rename to percobaan;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> show tables;
  +----------------+
  | Tables_in_coba |
  +----------------+
  | percobaan      |
  +----------------+
  1 row in set (0.00 sec)

  PERINTAH MEMBUAT FOREIGN KEY (tabel harus bentuk INNODB)

  ALTER TABLE namatabel

  2.ADD CONSTRAINT FOREIGN KEY (nama filed) REFERENCES namatabel (nama field)

  3.ON DELETE CASCADE

  4.ON UPDATE CASCADE;

   UNTUK MERUBAH TYPE TABLE DARI MYISAM JADI INNODB

  ALTER TABLE namatabel ENGINE = InnoDB;