Contoh sederhana operasi dengan my sql
mysql> create database soal;
Query OK, 1 row affected (0.00 sec)
mysql> use soal;
Database changed
mysql> create table mahasiswa(nim varchar (12) not null,nama varchar (20) not nu
ll,alamat varchar (30),kota varchar (30),taglahir date,primary key (nim));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+----------------+
| Tables_in_soal |
+----------------+
| mahasiswa |
+----------------+
1 row in set (0.00 sec)
mysql> desc mahasiswa;
+----------+---------------+--------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+--------+-----+---------+-------+
| nim | varchar(12) | | PRI | | |
| nama | varchar(20) | | | | |
| alamat | varchar(30) | YES | | NULL | |
| kota | varchar(30) | YES | | NULL | |
| taglahir | date | YES | | NULL | |
+----------+--------------+--------+-----+----------+-------+
5 rows in set (0.02 sec)
mysql> insert into mahasiswa values('06.230.00063','ciptoro','karanganyar','peka
longan','1988/09/20');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mahasiswa values('06.240.00063','sandy','kajen','jakarta','19
88/08/06');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mahasiswa values('06.240.00012','mendy','banjarnegara','bandu
ng','1985/08/19');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mahasiswa values('04.240.00012','melly','bandengan','batang',
'1983/01/01');
Query OK, 1 row affected (0.01 sec)
mysql> insert into mahasiswa values('05.230.00012','mahmud','bumiayu','sabah','1
986/12/31');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mahasiswa values('05.210.00012','dian','matrapura','semarang'
,'1986/02/27');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mahasiswa values('05.220.00099','dian','matrapura','semarang'
,'1986/02/27');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mahasiswa values('05.220.00009','subur','singaraja','semarang
','1986/02/24');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mahasiswa values('05.220.00054','jako','singaparna','jakarta'
,'1988/03/24');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mahasiswa values('05.210.00054','lily','kajen','bandung','198
8/09/28');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mahasiswa values('05.230.00039','sity','karanganyar','bandung
','1988/02/19');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mahasiswa;
+-----------------+------------+---------------+----------------+------------+
| nim | nama | alamat | kota | taglahir |
+-----------------+-----------+----------------+----------------+------------+
| 06.230.00063 | ciptoro | karanganyar | pekalongan | 1988-09-20 |
| 06.240.00063 | sandy | kajen | jakarta | 1988-08-06 |
| 06.240.00012 | mendy | banjarnegara | bandung | 1985-08-19 |
| 04.240.00012 | melly | bandengan | batang | 1983-01-01 |
| 05.230.00012 | mahmud | bumiayu | sabah | 1986-12-31 |
| 05.210.00012 | dian | matrapura | semarang | 1986-02-27 |
| 05.220.00099 | dian | matrapura | semarang | 1986-02-27 |
| 05.220.00009 | subur | singaraja | semarang | 1986-02-24 |
| 05.220.00054 | jako | singaparna | jakarta | 1988-03-24 |
| 05.210.00054 | lily | kajen | bandung | 1988-09-28 |
| 05.230.00039 | sity | karanganyar | bandung | 1988-02-19 |
+----------------+-----------+----------------+---------------+---------------+
11 rows in set (0.00 sec)
mysql> select count(nama) from mahasiswa where kota <>'pekalongan';
+-------------+
| count(nama) |
+-------------+
| 10 |
+-------------+
1 row in set (0.00 sec)
mysql> select * from mahasiswa where kota <>'pekalongan';
+-----------------+-----------+-----------------+-------------+--------------+
| nim | nama | alamat | kota | taglahir |
+-----------------+-----------+-----------------+------------+--------------+
| 06.240.00063 | sandy | kajen | jakarta | 1988-08-06 |
| 06.240.00012 | mendy | banjarnegara | bandung | 1985-08-19 |
| 04.240.00012 | melly | bandengan | batang | 1983-01-01 |
| 05.230.00012 | mahmud | bumiayu | sabah | 1986-12-31 |
| 05.210.00012 | dian | matrapura | semarang | 1986-02-27 |
| 05.220.00099 | dian | matrapura | semarang | 1986-02-27 |
| 05.220.00009 | subur | singaraja | semarang | 1986-02-24 |
| 05.220.00054 | jako | singaparna | Jakarta | 1988-03-24 |
| 05.210.00054 | lily | kajen | bandung | 1988-09-28 |
| 05.230.00039 | sity | karanganyar | bandung | 1988-02-19 |
+--------------+-------------+--------------+----------+------------+
10 rows in set (0.00 sec)
mysql> select nama,nim,kota from mahasiswa;
+-----------+------------------+--------------+
| nama | nim | kota |
+-----------+------------------+--------------+
| captoro | 06.230.00063 | pekalongan |
| sandy | 06.240.00063 | jakarta |
| mendy | 06.240.00012 | bandung |
| melly | 04.240.00012 | batang |
| mahmud | 05.230.00012 | sabah |
| dian | 05.210.00012 | semarang |
| dian | 05.220.00099 | semarang |
| subur | 05.220.00009 | semarang |
| jako | 05.220.00054 | jakarta |
| lily | 05.210.00054 | bandung |
| sity | 05.230.00039 | bandung |
+-----------+-----------------+--------------+
11 rows in set (0.00 sec)
mysql> select nim, count(*) from mahasiswa group by alamat;
+----------------+----------+
| nim | count(*) |
+----------------+----------+
| 04.240.00012 | 1 |
| 06.240.00012 | 1 |
| 05.230.00012 | 1 |
| 06.240.00063 | 2 |
| 06.230.00063 | 2 |
| 05.210.00012 | 2 |
| 05.220.00054 | 1 |
| 05.220.00009 | 1 |
+--------------+------------+
8 rows in set (0.01 sec)
mysql> select * from mahasiswa where mid( nim,4,3)= '240';
+----------------+---------+------------------+----------+---------------+
| nim | nama | alamat | kota | taglahir |
+----------------+---------+------------------+----------+---------------+
| 06.240.00063 | sandy | kajen | jakarta | 0000-00-00 |
| 06.240.00012 | mendy | banjarnegara | bandung | 1985-08-19 |
| 04.240.00012 | melly | bandengan | batang | 1983-01-01 |
+----------------+---------+------------------+-----------+---------------+
3 rows in set (0.00 sec)
mysql> select * from mahasiswa where( mid( nim,4,3)= '240') and( left(nim,2)= '04');
+-----------------+-------+--------------+--------+---------------+
| nim | nama | alamat nn | kota | taglahir |
+-----------------+-------+--------------+--------+---------------+
| 04.240.00012 | melly | bandengan | batang | 1983-01-01 |
+-----------------+-------+--------------+--------+---------------+
1 row in set (0.02 sec)
mysql> select * from mahasiswa where( mid( nim,4,3)= '230') and( right(nim,2)= '39');
+----------------+------+--------------+-----------+---------------+
| nim | nama | alamat | kota | taglahir |
+----------------+------+--------------+-----------+---------------+
| 05.230.00039 | sity | karanganyar | bandung | 1988-02-19 |
+----------------+------+--------------+------------+--------------+
1 row in set (0.00 sec)
0 comments:
Posting Komentar