MEMBANGUN
DATABASE
mysql>
show databases;
+----------+
| Database |
+----------+
| Kampus |
+----------+
1 row in set
(0.00 sec)
mysql>
create database mahasiswa;
Query OK, 1
row affected (0.00 sec)
mysql> use
mahasiswa;
Database
changed
mysql>
create table mhs(Nim varchar(10) not null, Nama varchar(30) not null, TglLahir
date, Alamat text, Primary key(Nim));
Query OK, 0
rows affected (0.02 sec)
mysql>
desc mhs;
+------------+----------------+-------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------+-------+------+---------+-------+
| Nim | varchar(10) | | PRI | |
|
| Nama |
varchar(30) | | |
| |
| TglLahir | date | YES
| | NULL
| |
| Alamat | text
| YES | | NULL
| |
+----------+-------------+------+-----+---------+-------+
4 rows in set
(0.00 sec)
mysql>
alter table mhs add Agama varchar(15) not null;
Query OK, 0
rows affected (0.01 sec)
Records:
0 Duplicates: 0 Warnings: 0
mysql>
desc mhs;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim | varchar(10) | | PRI | |
|
| Nama | varchar(30) | |
| | |
| TglLahir |
date | YES | |
NULL | |
| Alamat | text
| YES | | NULL
| |
| Agama | varchar(15) | |
| | |
+----------+-------------+------+-----+---------+-------+
5 rows in set
(0.00 sec)
mysql>
alter table mhs change agama agama varchar(10);
Query OK, 0
rows affected (0.00 sec)
Records:
0 Duplicates: 0 Warnings: 0
mysql>
desc mhs;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim | varchar(10) | | PRI | |
|
| Nama | varchar(30) | |
| | |
| TglLahir |
date | YES | |
NULL | |
| Alamat | text
| YES | | NULL
| |
| agama | varchar(10) | YES | |
NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set
(0.02 sec)
mysql>
alter table mhs modify agama char(2) not null;
Query OK, 0
rows affected (0.00 sec)
Records:
0 Duplicates: 0 Warnings: 0
mysql>
desc mhs;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim | varchar(10) | | PRI | |
|
| Nama | varchar(30) | |
| | |
| TglLahir |
date | YES | |
NULL | |
| Alamat | text
| YES | | NULL
| |
| agama | char(2) |
| | |
|
+----------+-------------+------+-----+---------+-------+
5 rows in set
(0.00 sec)
mysql>
alter table mhs drop agama;
Query OK, 0
rows affected (0.01 sec)
Records:
0 Duplicates: 0 Warnings: 0
mysql>
desc mhs;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim | varchar(10) | | PRI | |
|
| Nama | varchar(30) | |
| | |
| TglLahir |
date | YES | |
NULL | |
| Alamat | text
| YES | | NULL
| |
+----------+-------------+------+-----+---------+-------+
4 rows in set
(0.00 sec)
mysql>
rename table mhs to anggota;
Query OK, 0
rows affected (0.00 sec)
mysql>
desc anggota;
+----------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim | varchar(10) | | PRI | |
|
| Nama | varchar(30) | |
| | |
| TglLahir |
date | YES | |
NULL | |
| Alamat | text
| YES | | NULL
| |
+----------+-------------+------+-----+---------+-------+
4 rows in set
(0.00 sec)
mysql>
alter table anggota rename to mhs;
Query OK, 0
rows affected (0.00 sec)
mysql> desc
mhs;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Nim | varchar(10) | | PRI | |
|
| Nama | varchar(30) | |
| | |
| TglLahir |
date | YES | |
NULL | |
| Alamat | text
| YES | | NULL
| |
+----------+-------------+------+-----+---------+-------+
4 rows in set
(0.00 sec)
mysql>
insert into mhs values('0411500121','Achmad solichin','1982-06-05','Jakarta
Selatan');
Query OK, 1
row affected (0.00 sec)
mysql>
insert into mhs values('0411500123','Bajuri','1983-03-25','Jakarta Selatan');
Query OK, 1
row affected (0.01 sec)
mysql>
insert into mhs values('0411500111','Oneng','1980-03-22','Jakarta Utara');
Query OK, 1
row affected (0.00 sec)
mysql>
insert into mhs values('0411500115','Unyil','1980-08-29','Tangerang');
Query OK, 1
row affected (0.00 sec)
mysql>
insert into mhs values('0411500116','Harvei','1984-02-11','Medan
Query OK, 1
row affected (0.00 sec)
mysql>
select * from mhs;
+------------+-----------------+------------+-----------------+
| Nim | Nama | TglLahir | Alamat |
+------------+-----------------+------------+-----------------+
| 0411500121
| Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123
| Bajuri | 1983-03-25 | Jakarta
| 0411500111
| Oneng | 1980-03-22 | Jakarta
| 0411500115
| Unyil | 1980-08-29 | Tangerang |
| 0411500116
| Harvei | 1984-02-11 | Medan
+------------+-----------------+------------+-----------------+
5 rows in set
(0.00 sec)
mysql>
update mhs set alamat='Tangerang' where nim='0411500116';
Query OK, 1
row affected (0.00 sec)
Rows matched:
1 Changed: 1 Warnings: 0
mysql>
select * from mhs;
+------------+-----------------+------------+-----------------+
| Nim | Nama | TglLahir | Alamat |
+------------+-----------------+------------+-----------------+
| 0411500121
| Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123
| Bajuri | 1983-03-25 | Jakarta
| 0411500111
| Oneng | 1980-03-22 | Jakarta
| 0411500115
| Unyil | 1980-08-29 | Tangerang |
| 0411500116
| Harvei | 1984-02-11 |
Tangerang |
+------------+-----------------+------------+-----------------+
5 rows in set
(0.00 sec)
mysql>
update mhs set tgllahir='1986-03-11', alamat='Medan
Query OK, 1
row affected (0.00 sec)
Rows matched:
1 Changed: 1 Warnings: 0
mysql>
select * from mhs;
+------------+-----------------+------------+-----------------+
| Nim | Nama | TglLahir | Alamat |
+------------+-----------------+------------+-----------------+
| 0411500121
| Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123
| Bajuri | 1983-03-25 | Jakarta
| 0411500111
| Oneng | 1980-03-22 | Jakarta
| 0411500115
| Unyil | 1980-08-29 |
Tangerang |
| 0411500116
| Harvei | 1986-03-11 | Medan
+------------+-----------------+------------+-----------------+
5 rows in set
(0.00 sec)
mysql>
delete from mhs where nim='0411500116';
Query OK, 1
row affected (0.00 sec)
mysql>
select * from mhs;
+------------+-----------------+------------+-----------------+
| Nim | Nama | TglLahir | Alamat |
+------------+-----------------+------------+-----------------+
| 0411500121
| Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123
| Bajuri | 1983-03-25 | Jakarta
| 0411500111
| Oneng | 1980-03-22 | Jakarta
| 0411500115
| Unyil | 1980-08-29 | Tangerang |
+------------+-----------------+------------+-----------------+
4 rows in set
(0.00 sec)
mysql>
select nim, nama from mhs;
+------------+-----------------+
| nim | nama |
+------------+-----------------+
| 0411500121
| Achmad solichin |
| 0411500123
| Bajuri |
| 0411500111
| Oneng |
| 0411500115
| Unyil |
+------------+-----------------+
4 rows in set
(0.00 sec)
mysql>
select * from mhs where nim='0411500121';
+------------+-----------------+------------+-----------------+
| Nim | Nama | TglLahir | Alamat |
+------------+-----------------+------------+-----------------+
| 0411500121
| Achmad solichin | 1982-06-05 | Jakarta Selatan |
+------------+-----------------+------------+-----------------+
1 row in set
(0.00 sec)
mysql>
select * from mhs where alamat !='Jakarta Selatan';
+------------+-------+------------+---------------+
| Nim | Nama
| TglLahir | Alamat |
+------------+-------+------------+---------------+
| 0411500111
| Oneng | 1980-03-22 | Jakarta
| 0411500115
| Unyil | 1980-08-29 | Tangerang |
+------------+-------+------------+---------------+
2 rows in set
(0.00 sec)
mysql>
select * from mhs where alamat='Jakarta Selatan' && year(tgllahir)='1982'
;
+------------+-----------------+------------+-----------------+
| Nim | Nama | TglLahir | Alamat |
+------------+-----------------+------------+-----------------+
| 0411500121
| Achmad solichin | 1982-06-05 | Jakarta Selatan |
+------------+-----------------+------------+-----------------+
1 row in set
(0.00 sec)
mysql>
select * from mhs where alamat='Jakarta Selatan' &&
month(tgllahir)='03';
+------------+--------+------------+-----------------+
| Nim |
Nama | TglLahir | Alamat |
+------------+--------+------------+-----------------+
| 0411500123
| Bajuri | 1983-03-25 | Jakarta
+------------+--------+------------+-----------------+
1 row in set
(0.00 sec)
mysql>
select nim, nama, year(now())-year(tgllahir) as Umur from mhs;
+------------+-----------------+------+
| nim | nama | Umur |
+------------+----------------------------- +------+
| 0411500121
| Achmad solichin | 27 |
| 0411500123
| Bajuri | 26 |
| 0411500111
| Oneng | 29 |
| 0411500115
| Unyil | 29 |
+------------+----------------------------- +------+
4 rows in set
(0.00 sec)
mysql>
select * from mhs where substring(nim,3,2)='11';
+------------+-----------------+------------+-----------------+
| Nim | Nama | TglLahir | Alamat |
+------------+-----------------+------------+-----------------+
| 0411500121
| Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123
| Bajuri | 1983-03-25 | Jakarta
| 0411500111
| Oneng | 1980-03-22 | Jakarta
| 0411500115
| Unyil | 1980-08-29 |
Tangerang |
+------------+-----------------+------------+-----------------+
4 rows in set
(0.00 sec)
mysql>
select * from mhs order by nim desc;
+------------+-----------------+------------+-----------------+
| Nim | Nama | TglLahir | Alamat |
+------------+-----------------+------------+-----------------+
| 0411500123
| Bajuri | 1983-03-25 | Jakarta
| 0411500121
| Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500115
| Unyil | 1980-08-29 |
Tangerang |
| 0411500111
| Oneng | 1980-03-22 | Jakarta
+------------+-----------------+------------+-----------------+
4 rows in set
(0.01 sec)
mysql>
select * from mhs order by nim asc;
+------------+-----------------+------------+-----------------+
| Nim | Nama | TglLahir | Alamat |
+------------+-----------------+------------+-----------------+
| 0411500111
| Oneng | 1980-03-22 | Jakarta
| 0411500115
| Unyil | 1980-08-29 |
Tangerang |
| 0411500121
| Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123
| Bajuri | 1983-03-25 | Jakarta
+------------+-----------------+------------+-----------------+
4 rows in set
(0.00 sec)
mysql>
select * from mhs order by nim limit 0,5;
+------------+-----------------+------------+-----------------+
| Nim | Nama | TglLahir | Alamat |
+------------+-----------------+------------+-----------------+
| 0411500111
| Oneng | 1980-03-22 | Jakarta
| 0411500115
| Unyil | 1980-08-29 |
Tangerang |
| 0411500121
| Achmad solichin | 1982-06-05 | Jakarta Selatan |
| 0411500123
| Bajuri | 1983-03-25 | Jakarta
+------------+-----------------+------------+-----------------+
4 rows in set
(0.00 sec)
Tidak ada komentar:
Posting Komentar