Diberdayakan oleh Blogger.
RSS

Pages

Post Icon

Praktikum - Bagian 3 : Alter Table

Diposting oleh Unknown |

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)

  • Digg
  • Del.icio.us
  • StumbleUpon
  • Reddit
  • RSS
Read User's Comments(0)
Diposting oleh Unknown
Post Icon

Bagian 2 : Kunci - kunci dan perintah dalam MySQL

Diposting oleh Unknown |

1.    PRIMARY KEY


Membuat tabel primary key
mysql> create table t_pegawai1(id_pegawai varchar(12) primary key, nama_peg varchar(50),
alamat_peg varchar(50));
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+---------------+
| Tables_in_km2 |
+---------------+
| t_buku        |
| t_mhs1        |
| t_pegawai1    |
+---------------+
3 rows in set (0.00 sec)

mysql> insert into t_pegawai1 values ('HRD-001','Ade','Sentolo');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_pegawai1 values ('HRD-002','Rian','Wates');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_pegawai1 values ('HRD-003','Utomo','Sedayu');
Query OK, 1 row affected (0.03 sec)

mysql> select*from t_pegawai1;
+------------+----------+------------+
| id_pegawai | nama_peg | alamat_peg |
+------------+----------+------------+
| HRD-001    | Ade      | Sentolo    |
| HRD-002    | Rian     | Wates      |
| HRD-003    | Utomo    | Sedayu     |
+------------+----------+------------+
3 rows in set (0.00 sec)



2. UNIQUE

mysql> create table t_parkir1(id_parkir varchar(5) primary key, plat_no varchar(12),
merk varchar(10),unique(plat_no));
Query OK, 0 rows affected (0.06 sec)

mysql> desc t_parkir1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id_parkir | varchar(5)  | NO   | PRI |         |       |
| plat_no   | varchar(12) | YES  | UNI | NULL    |       |
| merk      | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t_parkir1 values ('00111','AB 1234 CD','Outlander');
Query OK, 1 row affected (0.03 sec)

mysql> insert into t_parkir1 values ('00112','AB 4321 CD','Civic');
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     |
+-----------+------------+-----------+
2 rows in set (0.00 sec)

mysql> insert into t_parkir1 values ('00114','AB 4321 CD','Honda');
ERROR 1062 (23000): Duplicate entry 'AB 4321 CD' for key 2  //tidak dapat menginput data baru karena plat nomor mobil tersebut sudah ada di data

4. IF NOT EXISTS
mysql> create table IF NOT EXISTS t_pegawai1(id_parkir varchar(5) primary key, plat_no
varchar(12), merk varchar(10),unique(plat_no));
Query OK, 0 rows affected, 1 warning (0.00 sec)

5. COPY
Mengcopy/menyalin tabel dari tabel lain beserta isi data-datanya
mysql> create table t_parkir1_copy as select*from t_parkir1;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select*from t_parkir1_copy; menampilkan data dari tabel hasil copy tadi
+-----------+------------+-----------+
| id_parkir | plat_no    | merk      |
+-----------+------------+-----------+
| 00111     | AB 1234 CD | Outlander |
| 00112     | AB 4321 CD | Civic     |
+-----------+------------+-----------+
2 rows in set (0.02 sec)

6. TEMPORARY KEY
Digunakan untuk menyimpan data sementara

mysql> create temporary table t_temporary(id int(8));  membuat tabel temporary
Query OK, 0 rows affected (0.05 sec)

mysql> desc t_temporary; value dalam tabel
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | int(8) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_km2  |
+----------------+
| t_buku         |
| t_mhs1         |
| t_parkir1      |
| t_parkir1_copy |
| t_pegawai1     |
+----------------+
5 rows in set (0.00 sec)

mysql> insert into t_temporary values('11100010');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_temporary values('11100011');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_temporary values('11100012');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_temporary values('11100013');
Query OK, 1 row affected (0.03 sec)

mysql> select*from t_temporary;
+----------+
| id       |
+----------+
| 11100010 |
| 11100011 |
| 11100012 |
| 11100013 |
+----------+
4 rows in set (0.00 sec)

7. FOREIGN KEY
mysql> create table t_mobil(id_mobil varchar(8) primary key, type varchar(20));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t_mobil values ('HON-001','CIVIC');
Query OK, 1 row affected (0.03 sec)

mysql> insert into t_mobil values ('TOY-001','AVANZA');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_mobil values ('TOY-002','SOLUNA');
Query OK, 1 row affected (0.03 sec)

mysql> select*from t_mobil;
+----------+--------+
| id_mobil | type   |
+----------+--------+
| HON-001  | CIVIC  |
| TOY-001  | AVANZA |
| TOY-002  | SOLUNA |
+----------+--------+
3 rows in set (0.00 sec)

mysql> create table t_mobil(id_mobil varchar(8) primary key, type varchar(20));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t_mobil values ('HON-001','CIVIC');
Query OK, 1 row affected (0.03 sec)

mysql> insert into t_mobil values ('TOY-001','AVANZA');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_mobil values ('TOY-002','SOLUNA');
Query OK, 1 row affected (0.03 sec)

mysql> select*from t_mobil;
+----------+--------+
| id_mobil | type   |
+----------+--------+
| HON-001  | CIVIC  |
| TOY-001  | AVANZA |
| TOY-002  | SOLUNA |
+----------+--------+
3 rows in set (0.00 sec)

mysql> create table t_stok1(id_stok varchar(8) primary key, id_mobil varchar(8), stok int(5), foreign key(id_mobil) references t_mobil(id_mobil));  (Membuat tabel dengan foreign key)

Query OK, 0 rows affected (0.09 sec)

mysql> create table t_stok1(id_stok varchar(8) primary key, id_mobil varchar(8), stok int(5), foreign key(id_mobil) references t_mobil(id_mobil));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t_stok1 values ('ST-001','HON-001','1000');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_stok1 values ('ST-002','TOY-001','500');
Query OK, 1 row affected (0.03 sec)

mysql> insert into t_stok1 values ('ST-003','TOY-002','435');
Query OK, 1 row affected (0.03 sec)

mysql> select*from t_stok1;
+---------+----------+------+
| id_stok | id_mobil | stok |
+---------+----------+------+
| ST-001  | HON-001  | 1000 |
| ST-002  | TOY-001  |  500 |
| ST-003  | TOY-002  |  435 |
+---------+----------+------+
3 rows in set (0.00 sec)

mysql> delete from t_mobil where id_mobil='HON-001';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`km2/t_stok1`, CONSTRAINT `t_stok1_ibfk_1` FOREIGN KEY (`id_mobil`) REFERENCES `t_mobil` (`id_mobil`))
 menjelaskan bahwa tabel tidak dapat di hapus karena merupakan primay key pada tabel t_mobil sedangkan pada tabel t_stok1 merupakan kunci tamu sehingga tidak memilki hak akses untuk menghapus data pada primary key

mysql> drop table t_stok1; (men-drop table t_stok1)
Query OK, 0 rows affected (0.03 sec)

mysql> create table t_stok1(id_stok varchar(8) primary key, id_mobil varchar(8), stok int(5), foreign key(id_mobil) references t_mobil(id_mobil) on delete cascade on update cascade);
Query OK, 0 rows affected (0.06 sec)

mysql> create table t_stok1(id_stok varchar(8) primary key, id_mobil varchar(8), stok int(5), foreign key(id_mobil) references t_mobil(id_mobil) on delete cascade on update cascade);
Query OK, 0 rows affected (0.06 sec)

8.MENGHAPUS DATA
mysql> delete from t_mobil where id_mobil='HON-001';
Query OK, 1 row affected (0.02 sec)

mysql> select*from t_mobil;  (menghapus salah satu data dalam tabel)
+----------+--------+
| id_mobil | type   |
+----------+--------+
| TOY-001  | AVANZA |
| TOY-002  | SOLUNA |
+----------+--------+
2 rows in set (0.00 sec)

  • Digg
  • Del.icio.us
  • StumbleUpon
  • Reddit
  • RSS
Read User's Comments(0)
Diposting oleh Unknown
Post Icon

Bagian 1 : Latihan Dasar MySQL

Diposting oleh Unknown |

Latihan 1

1.Membuat Database
mysql> create database KM2_LATIHAN1;  (membuat database baru dengan nama KM2_Latihan1

mysql> show databases;                                 dengan perintah ‘create databse’)
+--------------------+
| Database           |                                  
+--------------------+
| information_schema |
| km2                |
| km2_latihan1       |       (menampilkan database yang tadi telah dibuat
| mysql              |              dengan perintah “show databases”)
| rb4                |
| rb4_latihan1       |
| sn2                |
| sn2_latihan        |
| sn4                |
+--------------------+
9 rows in set (0.00 sec)

2.Membuat Tabel
Query OK, 1 row affected (0.02 sec)
mysql> create table T_Buku(id_buku varchar(8), nama_buku varchar(50), pengarang varchar(50), penerbit varchar(50), harga int(6));
Query OK, 0 rows affected (0.05 sec) (membuat tabel dengan perintah ‘create table T_Buku’
mysql> desc T_Buku;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id_buku   | varchar(8)  | YES  |     | NULL    |       |
| nama_buku | varchar(50) | YES  |     | NULL    |       |
| pengarang | varchar(50) | YES  |     | NULL    |       |
| penerbit  | varchar(50) | YES  |     | NULL    |       |
| harga     | int(6)      | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3. Masukkan Data
mysql> insert into T_Buku values('AA-001',' Pemrograman Pascal', 'Alfred Riedl', 'Erlangga', '50000');
Query OK, 1 row affected (0.05 sec)

mysql> insert into T_Buku values('AA-002',' SQL Server 5.0 ', 'Jono Ricardo', 'MyBook', '85000');
Query OK, 1 row affected (0.06 sec)

mysql> insert into T_Buku values('BB-001',' Obat Anti Galau', 'Tim Galauers', 'Gramedia', '40000');
Query OK, 1 row affected (0.02 sec)

mysql> insert into T_Buku values('BB-002',' Humor Indonesia', 'Hendi Susan', 'Andi Ho', '30000');
Query OK, 1 row affected (0.03 sec)

(Perintah diatas digunakan untuk menginput data berupa ID buku, nama buku, pengarang,  penerbit dan harga dengan nama T_Buku)


4. Menampilkan data
mysql> select*from T_Buku; (perintah untuk menampilkan buku yang telah di input tadi)
+---------+---------------------+--------------+----------+-------+
| id_buku | nama_buku           | pengarang    | penerbit | harga |
+---------+---------------------+--------------+----------+-------+
| AA-001  |  Pemrograman Pascal | Alfred Riedl | Erlangga | 50000 |
| AA-002  |  SQL Server 5.0     | Jono Ricardo | MyBook   | 85000 |
| BB-001  |  Obat Anti Galau    | Tim Galauers | Gramedia | 40000 |
| BB-002  |  Humor Indonesia    | Hendi Susan  | Andi Ho  | 30000 |
+---------+---------------------+--------------+----------+-------+
4 rows in set (0.00 sec)

  • Digg
  • Del.icio.us
  • StumbleUpon
  • Reddit
  • RSS
Read User's Comments(0)
Diposting oleh Unknown
Langganan: Postingan (Atom)