Perintah perintah SQL Database Dasar
stmik@stmik-lab:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 117
Server version: 5.1.41-3ubuntu12.3+kharisma1 (Kharisma)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use yulychocho;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from pegawai;
+-----+-------------+---------+------------+----------+
| nip | nama | gaji | departemen | kota |
+-----+-------------+---------+------------+----------+
| 1 | faud | 3000000 | akunting | jogja |
| 2 | john | 3600000 | pemasaran | jogja |
| 3 | freska | 1500000 | personalia | klaten |
| 4 | kadarisman | 960000 | akunting | sleman |
| 5 | krisna | 1200000 | personalia | magelang |
| 6 | rianto | 750000 | produksi | bantul |
| 7 | fajar | 2500000 | akunting | klaten |
| 8 | ida lestari | 750000 | produksi | sleman |
| 9 | dian | 8000000 | personalia | jogja |
| 10 | kartono | 7000000 | akunting | jogja |
+-----+-------------+---------+------------+----------+
10 rows in set (0.02 sec)
mysql> desc pegawai;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| nip | varchar(2) | NO | PRI | NULL | |
| nama | varchar(30) | YES | | NULL | |
| gaji | varchar(12) | YES | | NULL | |
| departemen | varchar(20) | YES | | NULL | |
| kota | varchar(15) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select departemen, avg(gaji) from pegawai where departemen="akunting" group by departemen;
+------------+-----------+
| departemen | avg(gaji) |
+------------+-----------+
| akunting | 3365000 |
+------------+-----------+
1 row in set (0.00 sec)
mysql> select count(nama) from pegawai;
+-------------+
| count(nama) |
+-------------+
| 10 |
+-------------+
1 row in set (0.00 sec)
mysql> select count(nama) from pegawai group by departemen;
+-------------+
| count(nama) |
+-------------+
| 4 |
| 1 |
| 3 |
| 2 |
+-------------+
4 rows in set (0.00 sec)
mysql> select departemen, count(nama) as total from pegawai group by departemen;
+------------+-------+
| departemen | total |
+------------+-------+
| akunting | 4 |
| pemasaran | 1 |
| personalia | 3 |
| produksi | 2 |
+------------+-------+
4 rows in set (0.00 sec)
mysql> select departemen, count(nama) > 1 as total from pegawai group by departemen;
+------------+-------+
| departemen | total |
+------------+-------+
| akunting | 1 |
| pemasaran | 0 |
| personalia | 1 |
| produksi | 1 |
+------------+-------+
4 rows in set (0.00 sec)
mysql> select departemen, count(nama) < 1 as total from pegawai group by departemen;
+------------+-------+
| departemen | total |
+------------+-------+
| akunting | 0 |
| pemasaran | 0 |
| personalia | 0 |
| produksi | 0 |
+------------+-------+
4 rows in set (0.00 sec)
mysql> select count(nama)< 1 as total from pegawai group by departemen;
+-------+
| total |
+-------+
| 0 |
| 0 |
| 0 |
| 0 |
+-------+
4 rows in set (0.00 sec)
mysql> select count(nama)> 1 as total from pegawai group by departemen;
+-------+
| total |
+-------+
| 1 |
| 0 |
| 1 |
| 1 |
+-------+
4 rows in set (0.00 sec)
mysql> select count(nama)> 1 as total from pegawai;
+-------+
| total |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
mysql> select departemen, count(nama) as total from pegawai where count(nama) > 1 group by departemen;;
ERROR 1111 (HY000): Invalid use of group function
ERROR:
No query specified
mysql> select departemen, count(nama) > 1 as total from pegawai group by departemen;
+------------+-------+
| departemen | total |
+------------+-------+
| akunting | 1 |
| pemasaran | 0 |
| personalia | 1 |
| produksi | 1 |
+------------+-------+
4 rows in set (0.00 sec)
mysql> select departemen, count(nama) as total from pegawai group by departemen having count(nama) > 1 ;
+------------+-------+
| departemen | total |
+------------+-------+
| akunting | 4 |
| personalia | 3 |
| produksi | 2 |
+------------+-------+
3 rows in set (0.00 sec)
mysql> select count(departemen) as total from pegawai group by departemen ;
+-------+
| total |
+-------+
| 4 |
| 1 |
| 3 |
| 2 |
+-------+
4 rows in set (0.00 sec)
mysql> select count(departemen) as total from pegawai;
+-------+
| total |
+-------+
| 10 |
+-------+
1 row in set (0.00 sec)
mysql> select count(departemen) as total from pegawai group by departemen;
+-------+
| total |
+-------+
| 4 |
| 1 |
| 3 |
| 2 |
+-------+
4 rows in set (0.00 sec)
mysql> select count(departemen) as total from pegawai;
+-------+
| total |
+-------+
| 10 |
+-------+
1 row in set (0.00 sec)
mysql> select count(DISTINCT departemen) as total from pegawai;
+-------+
| total |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)
mysql> select max(gaji) from pegawai;
+-----------+
| max(gaji) |
+-----------+
| 960000 |
+-----------+
1 row in set (0.02 sec)
mysql> select min(gaji) from pegawai;
+-----------+
| min(gaji) |
+-----------+
| 1200000 |
+-----------+
1 row in set (0.00 sec)
mysql> select sum(gaji) from pegawai;
+-----------+
| sum(gaji) |
+-----------+
| 29260000 |
+-----------+
1 row in set (0.00 sec)
mysql> select * from pegawai
-> ;
+-----+-------------+---------+------------+----------+
| nip | nama | gaji | departemen | kota |
+-----+-------------+---------+------------+----------+
| 1 | faud | 3000000 | akunting | jogja |
| 2 | john | 3600000 | pemasaran | jogja |
| 3 | freska | 1500000 | personalia | klaten |
| 4 | kadarisman | 960000 | akunting | sleman |
| 5 | krisna | 1200000 | personalia | magelang |
| 6 | rianto | 750000 | produksi | bantul |
| 7 | fajar | 2500000 | akunting | klaten |
| 8 | ida lestari | 750000 | produksi | sleman |
| 9 | dian | 8000000 | personalia | jogja |
| 10 | kartono | 7000000 | akunting | jogja |
+-----+-------------+---------+------------+----------+
10 rows in set (0.00 sec)
mysql> select * from pegawai limit 5;
+-----+------------+---------+------------+----------+
| nip | nama | gaji | departemen | kota |
+-----+------------+---------+------------+----------+
| 1 | faud | 3000000 | akunting | jogja |
| 2 | john | 3600000 | pemasaran | jogja |
| 3 | freska | 1500000 | personalia | klaten |
| 4 | kadarisman | 960000 | akunting | sleman |
| 5 | krisna | 1200000 | personalia | magelang |
+-----+------------+---------+------------+----------+
5 rows in set (0.00 sec)
mysql> select nama, 0.2*(gaji) from pegawai;
+-------------+------------+
| nama | 0.2*(gaji) |
+-------------+------------+
| faud | 600000 |
| john | 720000 |
| freska | 300000 |
| kadarisman | 192000 |
| krisna | 240000 |
| rianto | 150000 |
| fajar | 500000 |
| ida lestari | 150000 |
| dian | 1600000 |
| kartono | 1400000 |
+-------------+------------+
10 rows in set (0.00 sec)
mysql> select nama, 1.2*(gaji) from pegawai;
+-------------+------------+
| nama | 1.2*(gaji) |
+-------------+------------+
| faud | 3600000 |
| john | 4320000 |
| freska | 1800000 |
| kadarisman | 1152000 |
| krisna | 1440000 |
| rianto | 900000 |
| fajar | 3000000 |
| ida lestari | 900000 |
| dian | 9600000 |
| kartono | 8400000 |
+-------------+------------+
10 rows in set (0.00 sec)
mysql> select nama, 1.2*(gaji) as gaji from pegawai;
+-------------+---------+
| nama | gaji |
+-------------+---------+
| faud | 3600000 |
| john | 4320000 |
| freska | 1800000 |
| kadarisman | 1152000 |
| krisna | 1440000 |
| rianto | 900000 |
| fajar | 3000000 |
| ida lestari | 900000 |
| dian | 9600000 |
| kartono | 8400000 |
+-------------+---------+
10 rows in set (0.00 sec)
0 comments