September 10, 2018
Lirik Lagu
avg()
Fungsi :
Mencari nilai rata-rata suatu field bertipe numeric.
Contoh :
mysql> select * from barang;
+----------+-------------+----------------+--------+
| kodebrg | namabrg
| satuan |
Harga |
+----------+-------------+----------------+--------+
| BR001
| Rokok | Bungkus |
11000 |
| BR002
| Gula | Kg
| 10000 |
| BR003
| Beras | Kg
| 9000 |
| BR004
| Pepsodent | Buah | 3000 |
| BR005
| Buku | Buah
| 6000 |
| BR006
| Sardent | Kaleng
| 7000 |
| BR007
| Sampo | Shaset
| 500 |
| BR008
| Rinso |
Kg | 8000 |
| BR009
| Telur | Butir
| 1000 |
| BR010
| Polpen | Buah
| 4500 |
| BR011
| Teh Botol | Botol |
2000 |
| BR012
| Coca Cola |
Botol | 2500 |
| BR013
| Kecap | Botol
| 6000 |
| BR014
| Unibis | Bungkus | 7000|
| BR015
| Indomie | Bungkus | 1000|
+----------+-------------+----------------+--------+
15 rows in set (0.13 sec)
mysql> select avg(harga) as 'Harga
Rata-rata' from barang;
+-------------------------+
| Harga Rata-rata |
+-------------------------+
|
5233.3333 |
+-------------------------+
1 row in set (0.22 sec)
count()
Fungsi :
menghitung jumlah record dari suatu field atau tabel
Contoh :
mysql> select count(kodebrg) as 'Jumlah
barang' from barang;
+-------------------------+
| Jumlah barang |
+-------------------------+
|
15 |
+-------------------------+
1 row in set (0.02 sec)
mysql> select satuan,count(satuan) as
'Jumlah barang' from barang group by satuan;
+----------------+-------------------------+
| satuan
| Jumlah barang |
+----------------+-------------------------+
| Botol
| 3 |
| Buah
| 3 |
| Bungkus | 3 |
| Butir
| 1 |
| Kaleng
| 1 |
| Kg
| 3 |
| Shaset
| 1 |
+----------------+-------------------------+
7 rows in set (0.00 sec)
max()
Fungsi :
mencari nilai terbesar dari suatu field
Contoh :
mysql> select max(harga) as 'Harga
tertinggi' from barang;
+-------------------------+
| Harga tertinggi |
+-------------------------+
|
11000 |
+-------------------------+
1 row in set (0.42 sec)
min()
Fungsi :
mencari nilai terkecil dari suatu field
Contoh :
mysql> select min(harga) as 'Harga
terendah' from barang;
+-------------------------+
| Harga terendah |
+-------------------------+
|
500 |
+-------------------------+
1 row in set (0.00 sec)
sum()
Fungsi :
mendapatkan nilai total dari suatu field
Contoh :
mysql> select sum(harga) as 'Total harga'
from barang;
+-------------------------+
| Total harga |
+-------------------------+
|
78500 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select satuan,sum(harga) as 'Total
harga' from barang group by satuan;
+----------------+----------------+
| satuan
| Total harga |
+----------------+----------------+
| Botol
| 10500 |
| Buah
| 13500 |
| Bungkus | 19000 |
| Butir
| 1000 |
| Kaleng
| 7000 |
| Kg
| 27000 |
| Shaset
| 500 |
+----------------+----------------+
7 rows in set (0.00 sec)
Fungsi :
memformat angka ke dalam format desimal dengan susunan ’9,999,999,99’ dengan
pembulatan sejumlah angka yang didefinisikan oleh dec
Contoh :
mysql> select namabrg, format(harga,0) as
’Harga barng’ from barang;
+----------------+----------------+
| namabrg
| Harga barang |
+----------------+----------------+
| Rokok
| 11,000 |
| Gula
| 10,000 |
| Beras
| 9,000 |
| Pepsodent |
3,000 |
| Buku
| 6,000 |
| Sardent
| 7,000 |
| Sampo
| 500 |
| Rinso
| 8,000 |
| Telur
| 1,000 |
| Polpen
| 4,500 |
| Teh Botol |
2,000 |
| Coca Cola |
2,500 |
| Kecap
| 6,000 |
| Unibis
| 7,000 |
| Indomie
| 1,000 |
+----------------+----------------+
15 rows in set (0.14 sec)
pow(x,y) atau
power(x,y)
Fungsi :
menghitung nilai x pangkat y
Contoh :
mysql> select pow(2,2) as '2 pangkat 2';
+----------------+
| 2 pangkat 2 |
+----------------+
|
4 |
+----------------+
1 row in set (0.08 sec)
sqrt(x)
Fungsi :
mencari akar kwadrat angka x
Contoh :
mysql> select sqrt(25) as 'Akar 25';
+----------------+
| Akar 25 |
+----------------+
|
5 |
+----------------+
1 row in set (0.00 sec)
Fungsi :
membulatkan angka x, jika y didefinisikan akan menentukan jumlah desimal
mysql> select round(sqrt(125)) as 'Akar
125';
+----------------+
| Akar 125 |
+----------------+
|
11 |
+----------------+
1 row in set (0.00 sec)
mysql> select round(sqrt(125),3) as 'Akar
125';
+----------------+
| Akar 125 |
+----------------+
|
11.180 |
+----------------+
1 row in set (0.00 sec)
Concat(x,y,z,.....)
Fungsi :
menggabungkan beberapa string
Contoh :
mysql> select concat('Selamat ','Belajar','
MySQL');
+----------------------------------------------------+
| concat('Selamat ','Belajar',' MySQL') |
+----------------------------------------------------+
| Selamat Belajar MySQL |
+----------------------------------------------------+
1 row in set (0.00 sec)
Fungsi :
mengkonversi semua string menjadi huruf kecil
Contoh :
mysql> select lcase('SELAMAT BELAJAR
MYSQL');
+----------------------------------------------------+
| lcase('SELAMAT BELAJAR MYSQL') |
+----------------------------------------------------+
| selamat belajar mysql |
+---------------------------------------------------+
1 row in set (0.00 sec)
Ucase(x) atau
upper(x)
Fungsi :
mengkonversi semua string menjadi huruf BESAR
Contoh :
mysql> select ucase(namabrg) from barang
limit 3;
+-------------------------+
| ucase(namabrg) |
+------------------------+
| ROKOK |
| GULA |
| BERAS |
+-------------------------+
15 rows in set (0.00 sec)
Left(x,y)
Fungsi :
mengambil sejumlah y karakter dari string x dimulai posisi pertama
Contoh :
mysql> select left(namabrg,3) from barang
limit 3;
+-------------------------+
| left(namabrg,3) |
+-------------------------+
| Rok |
| Gul
|
| Ber |
+------------------------+
3 rows in set (0.00 sec)
Right(x,y)
Fungsi :
mengambil sejumlah y karakter dari string x dimulai dari posisi paling kanan
Contoh :
mysql> select right(namabrg,3) from barang
limit 3;
+-------------------------+
| right(namabrg,3) |
+------------------------+
| kok |
| ula |
| ras |
+-------------------------+
3 rows in set (0.00 sec)
Fungsi :
mengambil sejumlah z karakter dari string x dimulai posisi y
Contoh :
mysql> select mid(namabrg,2,3) from barang
limit 3;
+-------------------------+
| mid(namabrg,2,3) |
+-------------------------+
| oko |
| ula |
| era |
+-------------------------+
3 rows in set (0.00 sec)
Length(x)
Fungsi :
mencari panjang suatu string
Contoh :
mysql> select namabrg,length(namabrg) from
barang limit 3;
+----------------+-------------------------+
| namabrg |
length(namabrg) |
+----------------+-------------------------+
| Rokok
| 5 |
| Gula
| 4 |
| Beras
| 5 |
+----------------+-------------------------+
3 rows in set (0.00 sec)
Repeat(x,y)
Fungsi :
menhasilkan string x yang diulang sebanyak y kali
Contoh :
mysql> select repeat('MYSQL',5) as 'String
Berulang';
+----------------------------------------------------+
| String Berulang |
+----------------------------------------------------+
| MYSQLMYSQLMYSQLMYSQLMYSQL |
+----------------------------------------------------+
1 row in set (0.00 sec)
Replace(x,y,z)
Fungsi :
mengganti semua string y di dalam string x oleh string z
Contoh :
mysql> select replace('Belajar','a','A');
+----------------------------------+
| replace('Belajar','a','A') |
+----------------------------------+
| BelAjAr |
+----------------------------------+
1 row in set (0.00 sec)
Fungsi :
membalik urutan penulisan string
Contoh :
mysql> select reverse('Belajar MYSQL');
+----------------------------------+
| reverse('Belajar MYSQL') |
+----------------------------------+
| LQSYM rajaleB |
+----------------------------------+
1 row in set (0.00 sec)
0 komentar:
Posting Komentar