Perintah Alter Mysql

1. Saya disini mempunyai tabel jurusan akan saya rubah nama fieldnya nama_jur menjadi namajurusan
mysql> describe jurusan;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| kode_jur | char(2) | NO | PRI | | |
| nama_jur | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

dengan perintah seperti ini
alter table nama_table change nama_field_lama nama_field_baru tipe_data(panjang_karakter);
Pada perintah ini, tipe_data dan panjang_karakter dapat dirubah. Dalam kasus kita:
mysql> alter table jurusan change nama_jur namajurusan varchar(20);
Query OK, 7 rows affected (0.31 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> describe jurusan;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| kode_jur | char(2) | NO | PRI | | |
| namajurusan | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2. Merubah tipe Data
mysql> describe jurusan;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| kode_jur | char(2) | NO | PRI | | |
| namajurusan | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Dengan menggunakan sperintah seperti dibawah ini
mysql> alter table jurusan modify kode_jur char(4);
Query OK, 7 rows affected (0.13 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> describe jurusan;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| kode_jur | char(4) | NO | PRI | | |
| namajurusan | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3. Menambah tipe data
mysql> alter table jurusan
-> add nama_mahasiswa char(20)
-> ;
Query OK, 7 rows affected (0.13 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> describe jurusan;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| kode_jur | char(4) | NO | PRI | | |
| namajurusan | varchar(20) | YES | | NULL | |
| nama_mahasiswa | char(20) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4. Menghapus field atau kolom
mysql> alter table jurusan
-> drop nama_mahasiswa;
Query OK, 7 rows affected (0.17 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> describe jurusan;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| kode_jur | char(4) | NO | PRI | | |
| namajurusan | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

5. Memasukan data-data
mysql> insert into jurusan values
-> ("PGSD","GURU_SD"),
-> ("PGTK","GURU_TK");
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select*from jurusan;
+----------+--------------------+
| kode_jur | namajurusan |
+----------+--------------------+
| EK | Ekonomi |
| KI | Teknik Kimia |
| PGSD | GURU_SD |
| PGTK | GURU_TK |
| PH | Perhotelan |
| PR | Pariwisata |
| TE | Teknik Elektro |
| TI | Teknik Informatika |
| TS | Teknik Sipil |
+----------+--------------------+
9 rows in set (0.00 sec)

6. mengubah data-data
mysql> update jurusan set namajurusan="GURUSD" where kode_jur="PGSD";
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select*from jurusan;
+----------+--------------------+
| kode_jur | namajurusan |
+----------+--------------------+
| EK | Ekonomi |
| KI | Teknik Kimia |
| PGSD | GURUSD |
| PGTK | GURU_TK |
| PH | Perhotelan |
| PR | Pariwisata |
| TE | Teknik Elektro |
| TI | Teknik Informatika |
| TS | Teknik Sipil |
+----------+--------------------+
9 rows in set (0.01 sec)

Catatan : Jangan Lupa Baca dan Klik Dibawah ini ya

Artikel Keren Lainnya