Selasa, 17 April 2012
tugas pertemuan ke -4
1. Menambahkan primary key pada table.
mysql>
alter table absen_siswa
-> add constraint fk primary key(nis);
Query
OK, 6 rows affected (0.68 sec)
Records:
6 Duplicates: 0 Warnings: 0
mysql>
describe absen_siswa;
+--------+----------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
|
nis | int(11) | NO
| PRI | 0 | |
|
nama | char(20) | YES | |
NULL | |
| alamat
| char(60) | YES | | NULL
| |
|
kelas | int(11) | YES
| | NULL |
|
|
wali | int(11) | NO
| | NULL |
|
+--------+----------+------+-----+---------+-------+
5 rows
in set (0.05 sec)
2. Menambahkan
field baru.
mysql>
alter table absen_siswa
-> add jurusan char(20) not null;
Query
OK, 6 rows affected (0.44 sec)
Records:
6 Duplicates: 0 Warnings: 0
mysql>
describe absen_siswa;
+---------+----------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
|
nis | int(11) | NO
| PRI | 0 | |
|
nama | char(20) | YES | |
NULL | |
|
alamat | char(60) | YES | |
NULL | |
|
kelas | int(11) | YES
| | NULL |
|
|
wali | int(11) | NO
| | NULL |
|
|
jurusan | char(20) | NO | | NULL
| |
+---------+----------+------+-----+---------+-------+
6 rows
in set (0.01 sec)
3. Mengisi
field baru.
mysql>
update absen_siswa
-> set jurusan='IPA'
-> where nis='1001';
Query
OK, 1 row affected (0.08 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
mysql>
update absen_siswa
-> set jurusan='IPS'
->
where nis='1002';
Query
OK, 1 row affected (0.38 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
mysql>
update absen_siswa
-> set jurusan='IPA'
-> where nis='1003';
Query
OK, 1 row affected (0.38 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
mysql>
update absen_siswa
-> set jurusan='BAHASA'
-> where nis='1004';
Query
OK, 1 row affected (0.07 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
mysql>
update absen_siswa
-> set jurusan='IPS'
-> where nis='1005';
Query
OK, 1 row affected (0.10 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
mysql>
update absen_siswa
-> set jurusan='BAHASA'
-> where nis='1006';
Query
OK, 1 row affected (0.08 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
Setelah
diisi record adalah:
mysql>
SELECT * FROM ABSEN_SISWA;
+------+-----------------+--------------+-------+-------+---------+
| nis |
nama | alamat | kelas | wali | jurusan |
+------+-----------------+--------------+-------+-------+---------+
| 1001 | MUHAMMAD RIDWAN | jl airlangga | 2 | 11001 | IPA |
| 1002 | JULEHA | praya loteng | 2 | 11002 | IPS |
| 1003 | RIFKY | selong lotim | 2 | 11003 | IPA |
| 1004 | RINA | lobar |
3 | 11004 | BAHASA |
| 1005 | MARWAN | sumbawa |
3 | 11005 | IPS |
| 1006 | MUHAMMAD AZMY | jl airlangga | 2 | 11006 | BAHASA |
+------+-----------------+--------------+-------+-------+---------+
6 rows in set (0.00 sec)
4. Mengupdate
2 record.
mysql>
update absen_siswa
-> set alamat='jl sriwijaya no
23',wali='11012'
-> where alamat='jl airlangga' and
wali='11001';
Query
OK, 1 row affected (0.09 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
mysql>
update absen_siswa
-> set alamat='catur warga no
21',kelas='2'
-> where alamat='jl airlangga' and
kelas='2';
Query
OK, 1 row affected (0.38 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
Tampilan
setelah diupdate adalah:
mysql>
select * from absen_siswa
-> where nama like '%muhammad%';
+------+-----------------+--------------------+-------+-------+---------+
| nis |
nama | alamat | kelas | wali | jurusan |
+------+-----------------+--------------------+-------+-------+---------+
| 1001 | MUHAMMAD RIDWAN | jl sriwijaya no 23 | 2 | 11012 | IPA |
| 1006 | MUHAMMAD AZMY | catur warga no 21 | 2
| 11006 | BAHASA |
+------+-----------------+--------------------+-------+-------+---------+
2 rows
in set (0.00 sec)
5. Mendelete
2 record.
mysql>
delete from absen_siswa
-> where nama='muhammad ridwan';
Query
OK, 1 row affected (0.41 sec)
mysql>
delete from absen_siswa
-> where nis='1006';
Query
OK, 1 row affected (0.37 sec)
Tampilan
setelah 2 record di delete adalah:
mysql>
select * from absen_siswa;
+------+--------+--------------+-------+-------+---------+
|
nis | nama | alamat | kelas | wali | jurusan |
+------+--------+--------------+-------+-------+---------+
| 1002 |
JULEHA | praya loteng | 2 | 11002 |
IPS |
| 1003 |
RIFKY | selong lotim | 2 | 11003 | IPA |
| 1004 |
RINA | lobar |
3 | 11004 | BAHASA |
| 1005 |
MARWAN | sumbawa | 3 | 11005 | IPS |
+------+--------+--------------+-------+-------+---------+
4 rows
in set (0.00 sec)
6. Menambahkan
PK pada table pelanggan.
mysql>
alter table pelanggan add constraint
-> fk primary key(kode);
Query
OK, 16 rows affected (0.60 sec)
Records:
16 Duplicates: 0 Warnings: 0
Tampilan
setelah ditambahkan PK adalah:
mysql> describe
pelanggan;
+------------+----------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| kode | int(11) | NO
| PRI | 0 | |
| nama | char(20) | YES | |
NULL | |
| alamat | char(30) | YES | |
NULL | |
| kota | char(20) | YES | |
NULL | |
| notelp | char(20) | YES | |
NULL | |
| tipe | char(10) | YES | |
NULL | |
+------------+----------+------+-----+---------+-------+
7 rows in
set (0.19 sec)
7. Menambahkan
field baru ( pembayaran ) pada table pelanggan.
mysql>
alter table pelanggan
-> add pembayaran char(30) not null;
Query
OK, 16 rows affected (0.42 sec)
Records:
16 Duplicates: 0 Warnings: 0
Setelah
ditambahkan 1 field baru:
mysql> describe
pelanggan;
+------------+----------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| kode | int(11) | NO
| PRI | 0 | |
| nama | char(20) | YES | |
NULL | |
| alamat | char(30) | YES | |
NULL | |
| kota | char(20) | YES | |
NULL | |
| notelp | char(20) | YES | |
NULL | |
| tipe | char(10) | YES | |
NULL | |
| pembayaran | char(30)
| NO | | NULL
| |
+------------+----------+------+-----+---------+-------+
8.
Mengisi data pada field pembayaran.
mysql> update pelanggan
->
set pembayaran='bulanan'
->
where kode='123';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='tahunan'
->
where kode='124';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='bulanan'
->
where kode='125';
Query OK, 1 row affected (0.40 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='bulanan'
->
where kode='126';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='tahunan'
->
where kode='127';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='mingguan'
->
where kode='128';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='mingguan'
->
where kode='129';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='bulanan'
->
where kode='130';
Query OK, 1 row affected (0.48 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='bulanan'
->
where kode='131';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='tahunan'
->
where kode='132';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='tahunan'
->
where kode='133';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='mingguan'
->
where kode='134';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='mingguan'
->
where kode='135';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='tahunan'
->
where kode='136';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
-> set pembayaran='mingguan'
->
where kode='137';
Query OK, 1 row affected (0.45 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> update pelanggan
->
set pembayaran='bulanan'
->
where kode='138';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1
Changed: 1 Warnings: 0
Isinya adalah:
mysql> select pembayaran from pelanggan;
+------------+
| pembayaran |
+------------+
|bulanan |
|tahunan
|
| tahunan
|
| bulanan
|
| tahunan
|
| mingguan
|
| mingguan
|
| bulanan
|
| bulanan
|
| tahunan
|
| tahunan
|
| mingguan
|
| mingguan
|
| tahunan
|
| mingguan
|
| bulanan
|
+------------+
16 rows in set (0.00 sec)
9. Membuat
databases baru.
mysql>
create database mahasiswa;
Query
OK, 1 row affected (0.00 sec)
mysql>
show databases;
+--------------------+
|
Database |
+--------------------+
|
information_schema |
|
cdcol |
|
latihan1 |
| mahasiswa |
|
mysql |
|
penjualan |
| performance_schema
|
|
phpmyadmin |
|
test |
|
tugas2 |
|
webauth |
+--------------------+
11 ows in set (0.00 sec)
10. Membuat
table.
mysql>
create table daftar_nilai
-> (nama char(20),nim char(30),
-> jurusan char(20),nilai int not null);
Query
OK, 0 rows affected (0.51 sec)
mysql>
show tables;
+---------------------+
|
Tables_in_mahasiswa |
+---------------------+
| daftar_nilai |
+---------------------+
1 row in
set (0.00 sec)
11. Menghapus
table daftar_nilai.
mysql>
drop table daftar_nilai;
Query
OK, 0 rows affected (0.09 sec)
mysql>
show tables;
Empty
set (0.00 sec)
12. Menghapus
database mahasiswa.
mysql>
drop database mahasiswa;
Query
OK, 0 rows affected (0.15 sec)
mysql>
show databases;
+--------------------+
|
Database |
+--------------------+
|
information_schema |
|
cdcol |
|
latihan1 |
|
mysql |
|
penjualan |
|
performance_schema |
|
phpmyadmin |
|
test |
| tugas2 |
|
webauth |
+--------------------+
10 rows in
set (0.00 sec)
Minggu, 15 April 2012
Langganan:
Postingan (Atom)