Mencari Data Duplicate MySQL

Mencari Data Duplicate MySQL 

SELECT * FROM `table_name`  HAVING COUNT(id) > 1

Fungsi INSTR di MySQL

mysql> select * from aa;

+---------+---------+

|   id    |  angka  |

+---------+---------+

|       1 | 2,3,5,4 |
|       4 | 4       |
|       2 | 2       |
|       3 | 2,6     |
+---------+---------+
4 rows in set (0.00 sec)

mysql> select * from aa where INSTR(CONCAT(',', angka,','), ',2,');

+---------+---------+

|   id    |  angka  |

+---------+---------+

|       1 | 2,3,5,4 |

|       2 | 2       |

|       3 | 2,6     |

+---------+---------+

3 rows in set (0.00 sec)

mysql> select * from aa where INSTR( angka, '4');

+---------+---------+

|   id    |  angka  |

+---------+---------+

|       1 | 2,3,5,4 |

|       4 | 4       |

+---------+---------+

2 rows in set (0.00 sec)

Contoh Query Select For Update SQL

function cek($key){
 $this->db->query("start transaction");
 return $this->db->query("SELECT jumlah from mytable where kode='$key' for update")->row();
}

...

Sub Query Sederhana untuk membuat Laporan MySQL

mysql> select * from pegawai;
+------+--------+
| nik  | nama   |
+------+--------+
| PEG1 | Pampam |
| PEG2 | Agnes  |
| PEG3 | Catur  |
+------+--------+

mysql> select * from produksi;
+-------------+------+---------+--------+
| id_produksi | nik  | barang  | jumlah |
+-------------+------+---------+--------+
|           1 | PEG1 | barang1 |      4 |
|           2 | PEG1 | barang1 |      7 |
|           3 | PEG1 | barang1 |      1 |
|           4 | PEG2 | barang1 |      3 |
|           5 | PEG2 | barang1 |      1 |
|           6 | PEG3 | barang1 |      9 |
|           7 | PEG3 | barang2 |      9 |
|           8 | PEG2 | barang2 |      1 |
|           9 | PEG1 | barang2 |      5 |
+-------------+------+---------+--------+
9 rows in set (0.00 sec)

Query berdasarkan nik pegawai: 

mysql> select nik as nik_pegawai,nama,(select sum(jumlah)  from produksi where nik=nik_pegawai) as jumlah_produksi from produksi inner join pegawai using (nik) group by nik;
+-------------+--------+-----------------+
| nik_pegawai | nama   | jumlah_produksi |
+-------------+--------+-----------------+
| PEG1        | Pampam |              17 |
| PEG2        | Agnes  |               5 |
| PEG3        | Catur  |              18 |
+-------------+--------+-----------------+
3 rows in set (0.01 sec)

Query berdasarkan nik dan barang: 

mysql> select nik as nik_pegawai,nama,barang as barang_produksi,(select sum(jumlah)  from produksi where nik=nik_pegawai and barang=barang_produksi) as jumlah_produksi from produksi inner join pegawai using (nik) group by nik,barang;
+-------------+--------+-----------------+-----------------+
| nik_pegawai | nama   | barang_produksi | jumlah_produksi |
+-------------+--------+-----------------+-----------------+
| PEG1        | Pampam | barang1         |              12 |
| PEG1        | Pampam | barang2         |               5 |
| PEG2        | Agnes  | barang1         |               4 |
| PEG2        | Agnes  | barang2         |               1 |
| PEG3        | Catur  | barang1         |               9 |
| PEG3        | Catur  | barang2         |               9 |
+-------------+--------+-----------------+-----------------+
6 rows in set (0.00 sec)

Tampilkan dalam bentuk file PHP :

<?php
$con=mysql_connect('localhost','abby','abby');
if(!mysql_select_db('abby',$con))
    {die("database salah");}

$nik='xxx';
echo"<table align='center' border='1'>
<tr>
<th>NIK</th>
<th>Nama Pegawai</th>
<th>Nama Barang</th>
<th>Jumlah Produksi</th>
</tr>";
$q=mysql_query("select nik as nik_pegawai,nama,barang as barang_produksi,(select sum(jumlah)  from produksi where nik=nik_pegawai and barang=barang_produksi) as jumlah_produksi from produksi inner join pegawai using (nik) group by nik,barang");
while($d=mysql_fetch_array($q))
{
if($nik!=$d[nik_pegawai])
{
$nik=$d['nik_pegawai'];
$nama=$d['nama'];
}
else
{
$nik='';
$nama='';
}

echo "<tr>
<td>$nik</td>
<td>$nama</td>
<td>$d[barang_produksi]</td>
<td>$d[jumlah_produksi]</td>
</tr>";

$nik=$d['nik_pegawai'];
$nama=$d['nama'];

}
echo"</table>";
?>

Hasil :

Mengatasi Permission Denied to Create Database postgreSQL

# ALTER USER namauser CREATEDB;

Mencari Minimal dari Hasil Count MySQL

mysql> desc produksi;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id_pegawai | char(4) | YES  |     | NULL    |       |
| id_barang  | char(4) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)



mysql> select * from produksi;
+------------+-----------+
| id_pegawai | id_barang |
+------------+-----------+
| PEG1       | BAR1      |
| PEG1       | BAR2      |
| PEG1       | BAR3      |
| PEG2       | BAR1      |
| PEG2       | BAR2      |
| PEG2       | BAR3      |
| PEG2       | BAR4      |
| PEG3       | BAR1      |
| PEG3       | BAR2      |
| PEG3       | BAR3      |
| PEG3       | BAR4      |
| PEG3       | BAR5      |
| PEG3       | BAR6      |
+------------+-----------+
13 rows in set (0.00 sec)

Mencari Jumlah dengan Query Count :

mysql> select count(id_barang) hasilcount from produksi group by id_pegawai;

+------------+

| hasilcount |

+------------+

|          3 |

|          4 |

|          6 |

+------------+

3 rows in set (0.00 sec)

Mencari Minimal dari hasil count dengan Query Min :

mysql> select min(hasilcount) from (select count(id_barang) hasilcount from produksi group by id_pegawai) as minimal;

+-----------------+

| min(hasilcount) |

+-----------------+

|               3 |

+-----------------+

1 row in set (0.00 sec)