1.
Membuat
dan menghapus table serta menentukan primary key table entitas
-
Membuat table dan menentukan primary key table
entitas
Dalam MySQL
kita dapat membuat tabel baru dengan menggunakan perintah sebagai berikut :
Create table “nama table” ;
Seperti
contoh dibawah, kita akan membuat suatu tabel baru yang bernama “pelanggan”,
maka perintahnya sebagai berikut :
mysql>
create table pelanggan
Kemudian dilanjutkan dengan membuat
field field pada tabel tersebut dengan type data yang telah disesuaikan dan
dalam setiap tabel di tentukan sebuah primary key sebagai field yang unik dalam
tersebut, seperti implementasi dibawah ini, kami menentukan ‘id_pelanggan’
sebagai primary key-nya. Berikut penulisan secara lengkap dalam MySQL :
Membuat table Barang :
mysql> create table
Barang
-> (Id_barang varchar (10) primary key
not null,
-> judul char (50) not null,
-> jenis varchar (50) not null,
-> edisi int (10) not null,
-> harga_beli int (9) not null,
-> harga_jual int (9) not null);
Query OK, 0 rows affected (0.01 sec)
Kemudian tabel yang dibuat, ditampilkan :
mysql> desc barang;
+------------+-------------+------+-----+---------+-------+
| Field |
Type | Null | Key | Default |
Extra |
+------------+-------------+------+-----+---------+-------+
| Id_barang |
varchar(10) | NO | PRI | NULL |
|
| judul |
char(50) | NO |
| NULL | |
| jenis |
varchar(50) | NO | | NULL
| |
| edisi |
int(10) | NO |
| NULL | |
| harga_beli | int(9) | NO
| | NULL |
|
| harga_jual | int(9) | NO
| | NULL |
|
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.06 sec)
Membuat tabel distributor :
mysql> create table
distributor
-> (id_distributor char (10) primary key
null,
-> nama_distributor char (10),
-> alamat_distributor (50));
Query OK, 0 rows affected (0.01 sec)
Kemudian tabel yang dibuat, ditampilkan :
mysql> desc distributor;
+--------------------+----------+------+-----+---------+-------+
| Field
| Type | Null | Key | Default
| Extra |
+--------------------+----------+------+-----+---------+-------+
| id_distributor
| char(10) | NO | PRI | |
|
| nama_distributor
| char(20) | YES | | NULL
| |
| alamat_distributor | char(50) | YES | |
NULL | |
+--------------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Membuat tabel Penerbit :
mysql> create table penerbit
-> (id_penerbit varchar (10) primary key
not null,
-> nama_penerbit char (50) not null,
-> Lokasi_penerbit varchar (50));
Query OK, 0 rows affected (0.01 sec)
Kemudian tabel yang dibuat, ditampilkan :
mysql> desc penerbit;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| Id_penerbit | varchar(10) | NO | PRI | NULL |
|
| Nama_penerbit | char(50)
| NO | | NULL
| |
| Lokasi_penerbit |
varchar(50) | YES | | NULL
| |
+-----------------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
Membuat tabel Level :
mysql> create table level
-> (id_level char (10) primary key null,
-> quota int(10),
-> diskon float);
Query OK, 0 rows affected (0.01 sec)
Kemudian tabel yang dibuat, ditampilkan :
mysql> desc level;
+----------+----------+------+-----+---------+-------+
| Field |
Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id_level | char(10) | NO | PRI | |
|
| quota |
int(11) | YES | |
NULL | |
| diskon |
float | YES | |
NULL | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
-
Menghapus
table
Tabel sebelum dihapus:
mysql> show tables;
+------------------+
| Tables_in_contoh |
+------------------+
| contoh1 |
| contoh2 |
+------------------+
2 rows in set (0.00 sec)
Perintah untuk menghapus tabel:
mysql> drop table contoh1;
Tampilan setelah tabel dihapus:
mysql>
show tables;
+------------------+
|
Tables_in_contoh |
+------------------+
|
contoh2 |
+------------------+
1 row in
set (0.00 sec)
-
Membuat table relasi dan foreign key
mysql> create table
nota(
-> id_nota char(10),
-> id_distributor char(10),
-> id_majalah char(10),
-> tanggal_ambil date,
-> tanggal_kembali date,
-> primary key(id_nota),
-> foreign key(id_distributor)
references distributor(id_distributor));
Query OK, 0 rows affected
(0.07 sec)
Tampilan table nota :
mysql>
desc nota;
+-----------------+----------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
|
id_nota | char(10) | NO | PRI | |
|
|
id_distributor | char(10) | YES | MUL | NULL |
|
|
id_majalah | char(10) | YES | |
NULL | |
|
tanggal_ambil | date | YES
| | NULL |
|
|
tanggal_kembali | date | YES | |
NULL | |
+-----------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
-
Menghapus field tabel
>
alter table nota drop id_majalah;
mysql
Query OK, 0 rows affected (0.07 sec)
Records:
0 Duplicates: 0 Warnings: 0
Tampilan
table nota setelah field id_majalah dihapus :
mysql>
desc nota;
+-----------------+----------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
|
id_nota | char(10) | NO | PRI | |
|
|
id_distributor | char(10) | YES | MUL | NULL |
|
|
tanggal_ambil | date | YES
| | NULL |
|
|
tanggal_kembali | date | YES | |
NULL | |
+-----------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
-
Menambah
field table
mysql>
alter table nota add id_barang char(10);
Query
OK, 0 rows affected (0.05 sec)
Records:
0 Duplicates: 0 Warnings: 0
Tampilan
table nota setelah penambahan field baru :
mysql>
desc nota;
+-----------------+----------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
|
id_nota | char(10) | NO | PRI | |
|
|
id_distributor | char(10) | YES | MUL | NULL |
|
|
tanggal_ambil | date | YES
| | NULL |
|
|
tanggal_kembali | date | YES | |
NULL | |
|
id_barang | char(10) | YES | |
NULL | |
+-----------------+----------+------+-----+---------+-------+
5
rows in set (0.00 sec)
-
Menambah
foreign key
mysql>
alter table nota add foreign key(id_barang) references barang(id_barang);
Query
OK, 0 rows affected (0.07 sec)
Records:
0 Duplicates: 0 Warnings: 0
Tampilan
table nota setelah penambahan foreign key :
mysql>
desc nota;
+-----------------+----------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
|
id_nota | char(10) | NO | PRI | |
|
|
id_distributor | char(10) | YES | MUL | NULL |
|
|
tanggal_ambil | date | YES
| | NULL |
|
|
tanggal_kembali | date | YES | |
NULL | |
|
id_barang | char(10) | YES | MUL | NULL |
|
+-----------------+----------+------+-----+---------+-------+
5
rows in set (0.00 sec)
-
Mengganti
nama tabel
mysql>
alter table nota rename to nota_distributor;
Query
OK, 0 rows affected (0.00 sec)
Tampilan
table nota setelah di rename menjadi nota_distributor :
mysql>
desc nota_distributor;
+-----------------+----------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
|
id_nota | char(10) | NO | PRI | |
|
|
id_distributor | char(10) | YES | MUL | NULL |
|
|
tanggal_ambil | date | YES
| | NULL |
|
|
tanggal_kembali | date | YES | | NULL
| |
|
id_barang | char(10) | YES | MUL | NULL |
|
+-----------------+----------+------+-----+---------+-------+
5
rows in set (0.00 sec)
-
Meghapus
table relasi
mysql>
drop table nota_distributor;
Query
OK, 0 rows affected (0.00 sec)
Tampilan
database penjualan majalah setelah table nota_distributor dihapus :
mysql>
show tables;
+-----------------------------+
|
Tables_in_penjualan_majalah |
+-----------------------------+
|
barang |
|
distributor |
|
level |
|
mempunyai |
|
penerbit |
|
produksi |
|
transaksi |
+-----------------------------+
7
rows in set (0.00 sec)
-
Mengisi record table
mysql>
insert into penerbit values(
-> 'P001','komputerMedia','jl.hayamwuruk
21 jakarta');
Query
OK, 1 row affected (0.00 sec)
Tampilan
record pada table penerbit :
mysql>
select * from penerbit;
+-------------+---------------+--------------------------+
|
id_penerbit | nama_penerbit | alamat_penerbit |
+-------------+---------------+--------------------------+
|
P001 | komputerMedia |
jl.hayamwuruk 21 jakarta |
+-------------+---------------+--------------------------+
1
row in set (0.00 sec)
Pengisian
record kedua :
mysql>
insert into penerbit values('P002','lalalaMedia','jl. duta niaga tangerang');
Query
OK, 1 row affected (0.00 sec)
Tampilan
record setelah pengisian kedua :
mysql>
select * from penerbit;
+-------------+---------------+--------------------------+
|
id_penerbit | nama_penerbit | alamat_penerbit |
+-------------+---------------+--------------------------+
|
P001 | komputerMedia |
jl.hayamwuruk 21 jakarta |
|
P002 | lalalaMedia | jl. duta niaga tangerang |
+-------------+---------------+--------------------------+
2
rows in set (0.00 sec)
-
Mengganti (update) record pada suatu field
mysql>
update penerbit set nama_penerbit='setelahUpdate Media' where id_penerbit='P002';
Query
OK, 1 row affected (0.00 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
Tampilan
record pada table penerbit setelah di update :
mysql>
select * from penerbit;
+-------------+---------------------+--------------------------+
|
id_penerbit | nama_penerbit |
alamat_penerbit |
+-------------+---------------------+--------------------------+
|
P001 | komputerMedia | jl.hayamwuruk 21 jakarta |
|
P002 | setelahUpdate Media | jl.
duta niaga tangerang |
+-------------+---------------------+--------------------------+
2 rows in set (0.00 sec)
-
Menghapus record
mysql>
delete from penerbit where id_penerbit='P002';
Query
OK, 1 row affected (0.00 sec)
Tampilan
table penerbit setelah record kedua dihapus :
mysql>
select * from penerbit;
+-------------+---------------+--------------------------+
|
id_penerbit | nama_penerbit | alamat_penerbit |
+-------------+---------------+--------------------------+
|
P001 | komputerMedia | jl.hayamwuruk
21 jakarta |
+-------------+---------------+--------------------------+
1
row in set (0.00 sec)
No comments:
Post a Comment