ALTER tabel
Menambah tabel dengan perintah alter table
mysql> alter table t_mobil add harga bigint(11);
Query OK, 2 rows affected (0.23 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t_mobil;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | | |
| type | varchar(20) | YES | | NULL | |
| harga | bigint(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table t_mobil add series varchar(8) after type;
Query OK, 2 rows affected (0.14 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t_mobil;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | | |
| type | varchar(20) | YES | | NULL | |
| series | varchar(8) | YES | | NULL | |
| harga | bigint(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Mengganti nama tabel menggunakan perintah :
mysql> alter table t_mobil rename to t_car1;
Query OK, 0 rows affected (0.03 sec)
mysql> desc t_car1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | | |
| type | varchar(20) | YES | | NULL | |
| series | varchar(8) | YES | | NULL | |
| harga | bigint(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
Modifikasi definisi tabel
mysql> alter table t_car1 modify type varchar(15), modify series varchar(10);
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t_car1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | | |
| type | varchar(15) | YES | | NULL | |
| series | varchar(10) | YES | | NULL | |
| harga | bigint(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
Mengganti nama kolom
mysql> alter table t_car1 change harga harga_mobil bigint(11)
Query OK, 2 rows affected (0.20 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t_car1;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | | |
| type | varchar(15) | YES | | NULL | |
| series | varchar(10) | YES | | NULL | |
| harga_mobil | bigint(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Menghapus kolom
mysql> alter table t_car1 drop harga_mobil;
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t_car1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_mobil | varchar(8) | NO | PRI | | |
| type | varchar(15) | YES | | NULL | |
| series | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Menghapus dan menambah primary key
mysql> alter table t_pegawai1 drop primary key;
Query OK, 3 rows affected (0.17 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc t_pegawai1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_pegawai | varchar(12) | NO | | | |
| nama_peg | varchar(50) | YES | | NULL | |
| alamat_peg | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table t_pegawai1 add primary key (id_pegawai);
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc t_pegawai1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_pegawai | varchar(12) | NO | PRI | | |
| nama_peg | varchar(50) | YES | | NULL | |
| alamat_peg | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Menghapus seluruh isi tabel
mysql> select*from t_parkir1_copy;
+-----------+------------+-----------+
| id_parkir | plat_no | merk |
+-----------+------------+-----------+
| 00111 | AB 1234 CD | Outlander |
| 00112 | AB 4321 CD | Civic |
+-----------+------------+-----------+
2 rows in set (0.03 sec)
mysql> truncate table t_parkir1_copy;
Query OK, 2 rows affected (0.03 sec)
mysql> select*from t_parkir1_copy;
Empty set (0.00 sec)
Memasukkan data lebih dari 1 data dengan INSERT
mysql> insert into t_parkir1 values('00113','AB-3222-CD','Brio'),('00114','AA-4321','Soluna');
Query OK, 2 rows affected (0.03 sec)
mysql> select*from t_parkir1;
+-----------+------------+-----------+
| id_parkir | plat_no | merk |
+-----------+------------+-----------+
| 00111 | AB 1234 CD | Outlander |
| 00112 | AB 4321 CD | Civic |
| 00113 | AB-3222-CD | Brio |
| 00114 | AA-4321 | Soluna |
+-----------+------------+-----------+
4 rows in set (0.00 sec)
Insert dari tabel lain menggunakan nama kolom tertentu
mysql> create table t_user1(no_plat varchar(10), merk_kend varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql> desc t_user1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| no_plat | varchar(10) | YES | | NULL | |
| merk_kend | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t_user1(no_plat, merk_kend) select plat_no, merk from t_parkir1;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select*from t_user;
mysql> select*from t_user1;
+------------+-----------+
| no_plat | merk_kend |
+------------+-----------+
| AB 1234 CD | Outlander |
| AB 4321 CD | Civic |
| AB-3222-CD | Brio |
| AA-4321 | Soluna |
| AB-6619-KK | Hino |
+------------+-----------+
5 rows in set (0.00 sec)
Mengubah data menggunakan update
mysql> update t_user1 set merk_kend='Brio' where no_plat='CD-6677-DC';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select*from t_user1;
+------------+-----------+
| no_plat | merk_kend |
+------------+-----------+
| AB 1234 CD | Outlander |
| AB 4321 CD | Civic |
| AB-3222-CD | Brio |
| AA-4321 | Soluna |
| AB-6619-KK | Hino |
+------------+-----------+
5 rows in set (0.00 sec)
Mengganti data dari tabel
mysql> replace into t_parkir1(id_parkir, plat_no, merk) values ('00114','BH-36-CD','Ferrari');
Query OK, 2 rows affected (0.03 sec)
mysql> select*from t_parkir1;
+-----------+------------+-----------+
| id_parkir | plat_no | merk |
+-----------+------------+-----------+
| 00111 | AB 1234 CD | Outlander |
| 00112 | AB 4321 CD | Civic |
| 00113 | AB-3222-CD | Brio |
| 00114 | BH-36-CD | Ferrari |
| 00115 | AB-6619-KK | Hino |
+-----------+------------+-----------+
5 rows in set (0.00 sec)
Menghapus data dari tabel
mysql> delete from t_parkir1 where id_parkir='00113';
Query OK, 1 row affected (0.03 sec)
mysql> delete from t_parkir1 where merk='Hino';
Query OK, 1 row affected (0.01 sec)
mysql> select*from t_parkir1;
+-----------+------------+-----------+
| id_parkir | plat_no | merk |
+-----------+------------+-----------+
| 00111 | AB 1234 CD | Outlander |
| 00112 | AB 4321 CD | Civic |
| 00114 | BH-36-CD | Ferrari |
+-----------+------------+-----------+
3 rows in set (0.00 sec)
Tidak ada komentar:
Posting Komentar