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)

Insert Multiple Data dengan Check Box dan PHP

Sekedar Tips bagi yang merasa kesulitan dengan query insert multiple data dengan checkbox dan PHP, 

Sebagai contoh kita buat dulu databasenya, DBMS yang saya gunakan adalah PostgreSQL, untuk MySQL hampir sama,

contoh :

Table Mahasiswa 

CREATE TABLE mahasiswa (nim char(9) primary key, nama varchar(30) not null);

Table Status

 CREATE TABLE status (nim char(9) references mahasiswa (nim),status varchar(10)); 

Insert beberapa data di Table Mahasiswa

Buat file koneksi.php

<?php
$conn_string ="host=localhost dbname=mydb user=abby password=acakadud";
$connection = pg_pconnect($conn_string);
?>

Sekarang buat form untuk insert beberapa data dari tabel mahasiswa ke tabel status dengan checkbox simpan dengan nama index.php

<!DOCTYPE html>
<html>
<!-- Code By : abby -->
<head>
<title>Multiple Checkbox PHP</title>
<meta http-equiv="content-type" content="text/html;charset=utf-8" />
<meta name="generator" content="Geany 0.21" />
</head>
<body>
<h3 align='center'>Multiple Checkbox dengan PHP</h3>
<?php
require "koneksi.php";
echo"
<form action='proses.php' method='post'>
<table border='1' align='center'>";
$q=pg_query("select * from mahasiswa");
while($d=pg_fetch_array($q)){
echo"
<tr>
<td align='center'><input type='checkbox' name='item[]' value='$d[nim]'></td>
<td>$d[nama]</td>
</tr> ";
}
echo"
<tr>
<td><input type='reset' value='Reset'></td>
<td><input type='submit' name='submit' value='Simpan'></td>
</tr>
</table>
</from>";
?>
</body>
</html>

Hasilnya seperti berikut :

sekarang buat file proses.php

<?php
require "koneksi.php";
$jumlah = count($_POST["item"]);
for($i=0; $i < $jumlah; $i++)
{
    $nim=$_POST["item"][$i];
    $q=pg_query("insert into status values ('$nim','aktif')");
}
if ($q){
echo"Data Berhasil disimpan";
}
else
{
echo"Data Gagal Disimpan";
}
?>

Sekarang kita coba :

Setelah Berhasil kita cek di Database :

SELECT * from status;

Semoga Membantu

Query Group By di PostgreSQL

Untuk penggunaan query Group By di PostgreSQL berbeda dengan MySQL, agar lebih di mengerti coba dilihat contoh berikut :

MySQL 

mysql> create table pengunjung (id_pengunjung int not null, nama_pengunjung varchar(30) not null, tanggal date not null);Query OK, 0 rows affected (0.15 sec)

mysql> desc pengunjung;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id_pengunjung   | int(11)     | NO   |     | NULL    |       |
| nama_pengunjung | varchar(30) | NO   |     | NULL    |       |
| tanggal         | date        | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into pengunjung values     -> (1,'abby','2013-01-01'),    -> (2,'yoga','2013-01-01'),    -> (3,'dono','2013-01-02'),    -> (2,'yoga','2013-01-04'),    -> (1,'abby','2013-01-13'),    -> (1,'abby','2013-01-23');Query OK, 6 rows affected (0.19 sec)Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from pengunjung;

+---------------+-----------------+------------+
| id_pengunjung | nama_pengunjung | tanggal    |
+---------------+-----------------+------------+
|             1 | abby            | 2013-01-01 |
|             2 | yoga            | 2013-01-01 |
|             3 | dono            | 2013-01-02 |
|             2 | yoga            | 2013-01-04 |
|             1 | abby            | 2013-01-13 |
|             1 | abby            | 2013-01-23 |
+---------------+-----------------+------------+
6 rows in set (0.00 sec)

contoh query Group By dengan MySQL seperti berikut :

mysql> select * from pengunjung group by id_pengunjung;

+---------------+-----------------+------------+
| id_pengunjung | nama_pengunjung | tanggal    |
+---------------+-----------------+------------+
|             1 | abby            | 2013-01-01 |
|             2 | yoga            | 2013-01-01 |
|             3 | dono            | 2013-01-02 |
+---------------+-----------------+------------+
3 rows in set (0.00 sec)

Sekarang coba kita coba tabel data yang sama dengan PostgreSQL :

postgres=# SELECT * from pengunjung;

+---------------+-----------------+------------+
| id_pengunjung | nama_pengunjung |  tanggal   |
+---------------+-----------------+------------+
|             1 | abby            | 2013-01-01 |
|             2 | yoga            | 2013-01-01 |
|             3 | dono            | 2013-01-02 |
|             2 | yoga            | 2013-01-04 |
|             1 | abby            | 2013-01-13 |
|             1 | abby            | 2013-01-23 |
+---------------+-----------------+------------+
(6 rows)

Dan kita coba jalankan query Group By yang sama :

postgres=# select * from pengunjung group by id_pengunjung;

ERROR:  column "pengunjung.nama_pengunjung" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select * from pengunjung group by id_pengunjung;

Solusinya adalah dengan menggunakan fungsi DISTINCT ON  seperti berikut :

postgres=# select distinct on (id_pengunjung) id_pengunjung, nama_pengunjung, tanggal from pengunjung;

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

| id_pengunjung | nama_pengunjung |  tanggal   |

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

|             1 | abby            | 2013-01-01 |

|             2 | yoga            | 2013-01-01 |

|             3 | dono            | 2013-01-02 |

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

(3 rows)

Jadi DISTINCT akan mengabaikan nilai yang sama dalam suatu kolom.

Semoga Membantu 

Merubah Owner Suatu Table di PostgreSQL

Masih dengan DBMS PostgreSQL, dan hanya sekedar untuk catatan tentang Owner atau Kepemilikan suatu Table yang pasti ada kaitannya dengan Peringatan Error "Permission Denied".

Agar lebih mudah dalam penjelasan, coba kita buat suatu kasus sebagai berikut :

1. Masuk postgreSQL dengan perintah berikut :

$ psql

dan hasilnya :

postgres=#

Ok, bisa dilihat tanda setelah "=" yaitu tanda "#" bisa diartikan adalah root, artinya user dengan hak akses root mempunya hak penuh atas kepemilikannya, misal Database atau Table.

2. Selanjutnya coba buat user dengan perintah berikut :

# create user abby;

dan tambahkan password :

# \password abby;

3.  Kemudian coba buat lagi user yang berbeda :

# create user dika;

# \password dika;

untuk melihat user bisa dengan perintah query berikut :

# select * from pg_user;

 

4. Buat Database :

# create database mydb;

dan lihat owner  dari database tersebut :

# \l

5. Rubah Owner database mydb ke user abby :

# alter database mydb owner to abby;

dan lihat owner dari databasenya :

# \l

6. Keluar dan Masuk ke Database mydb dengan user abby;

# \q

# psql mydb abby;

 perbedaan user abby dengan postgres  bisa dilihat di tanda "#" dan ">" artinya user abby tidah mempunyai hak penuh seperti user postgres.

7. Buat Table dengan nama table_abby :

> create table table_abby (id char(2),nama varchar(10));

hasilnya :

8. Sekarang coba keluar dan Masuk ke Database mydb dengan User dika :

> \q

> psql mydb dika;

9. Buat Table dengan nama table_dika :

> create table table_dika (id char(2),nama varchar(10));

10. Sekarang coba rubah owner user untuk masing-masing table :

dan lihat ownernya :

> \d

 
Masih dengan user dika, untuk mencobanya , Lakukan perintah Insert untuk table_abby :

> INSERT INTO table_abby values ('1','nama');

Muncul Pesan Error "Permission Denied"

dan sekarang Lakukan perintah Insert untuk table_dika :

> INSERT INTO table_dika values ('1','nama');

 Mungkin dari hasil diatas bisa di mengerti apa Owner itu dari suatu Table ..

Semoga Membantu..

Membuat User di MySQL [Debian]

Berhubung lagi ngeremote, dan masih berhubungan dengan DBMS MySQL, sekedar membuat catatan bagaimana membuat user di DBMS MySQL :

1. Masuk MySQL dengan user root :

# mysql -u root -p

2. Buat Hash Password dengan perintah berikut :

contoh : abby

> select password ('abby');

hasilnya seperti berikut :

3. Buat User dengan perintah berikut dan password dengan hasil hash tadi :

> create user 'abby'@'localhost' identified by password '*8DF79545DB6D735C372251BA785BF66DCB982705';

4. Lihat hasilnya di dalam tabel user database mysql :

> select user, password from mysql.user where user='abby';

5. Sekarang coba buat Database untuk User yang sudah dibuat :

> create database db_abby;

6. Beri Hak Akses User untuk database yang sudah dibuat :

> grant all on db_abby.* to 'abby'@'localhost';

7. Keluar dan Masuk dengan user yang sudah dibuat :

Semoga Membantu..

Rename Table di PostgreSQL

Rename Table di PostgreSQL :

postgres=# CREATE TABLE sample (id char);

postgres=# \d 

postgres=# ALTER TABLE sample RENAME to sampletable;

postgres=# \d

Returns Table Function on PostgreSQL

Ok, sesuai judulnya, bagaimana cara penggunaan returns table di suatu fungsi dbms postgresql, ceritanya kemarin ada salah satu mahasiswa yang bertanya , contohnya seperti berikut :

Ada 3 buah table :

1. mahasiswa

2. mata_kuliah

3. khs

          Table "public.mahasiswa"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 nim    | character(9)          | not null
 nama   | character varying(50) | not null

          Table "public.mata_kuliah"
  Column   |         Type          | Modifiers
-----------+-----------------------+-----------
 kode_mk   | character(5)          | not null
 nama_mk   | character varying(30) |
 sks       | integer               |
 kurikulum | character(9)          |

         Table "public.khs"
 Column  |     Type     | Modifiers
---------+--------------+-----------
 nim     | character(9) |
 kode_mk | character(5) |
 ta      | character(9) |
 smt     | integer      |
 nilai   | character(1) |
Foreign-key constraints:

Dan isi dari masing-masing table :

nama_kelompok=> select * from mahasiswa;
    nim    | nama
-----------+------
 091051080 | abby
 091051081 | yoga
(2 rows)

nama_kelompok=> select * from mata_kuliah ;
 kode_mk |     nama_mk     | sks | kurikulum
---------+-----------------+-----+-----------
 tifs1   | Pemrograman SQL |   3 | 2007/2008
 tifs2   | Pemrograman Web |   3 | 2007/2008
 tifs3   | Jarkom          |   3 | 2007/2008
(3 rows)

nama_kelompok=> select * from khs;
    nim    | kode_mk |    ta     | smt | nilai
-----------+---------+-----------+-----+-------
 091051080 | tifs1   | 2012/2013 |   5 | A
 091051080 | tifs2   | 2012/2013 |   5 | A
 091051080 | tifs3   | 2012/2013 |   5 | A
(3 rows)

Nah, yang jadi pertanyaan adalah Buat fungsi yang jika di panggil :

nama_kelompok=> SELECT * from trans('091051080')

Hasilnya :

    nim    | kode_mk |     nama_mk     | nilai
-----------+---------+-----------------+-------
 091051080 | tifs1   | Pemrograman SQL | A
 091051080 | tifs2   | Pemrograman Web | A
 091051080 | tifs3   | Jarkom          | A

Jadi sederhannya adalah menampilkan query dari suatu table yang di joinkan :

Berikut fungsi yang sudah saya coba buat :

CREATE OR REPLACE FUNCTION trans(char) RETURNS table (nim char, kode_mk char, nama_mk varchar,nilai char) AS
$$
BEGIN
 return query select khs.nim, khs.kode_mk, mata_kuliah.nama_mk, khs.nilai from khs join mata_kuliah using (kode_mk) where khs.kode_mk=mata_kuliah.kode_mk and khs.nim=$1 ;

    RETURN;
END;
$$
LANGUAGE 'plpgsql' ;

semoga membantu :)

Merubah Type Column di PostgreSQL

Tak jarang banyak yang akan merubah type column di suatu table postgreSQL, lalu bagaimana caranya ?.. Ok, diumpamakan sebagai berikut :

1. Table yang berisi (id_user,nama_user) ternyata terlanjur melakukan kesalahan dalam membuat type column nama_user yang seharusnya varchar menjadi int, Berikut perintahnya :

# CREATE TABLE pengguna (id_user char(5) primary key, nama_user int);

2. Sekarang coba rubah type column nama_user dengan perintah berikut :

# ALTER TABLE pengguna alter column nama_user type varchar(30);

Semoga Membantu..

Membuat Type Enum di PostgreSQL

Mungkin ada yang bingung bagaimana membuat suatu type itu enum di postgreSQL, sebenarnya tidak ada yang sulit untuk membuatnya karena kita bisa membuat (create) type sendiri. Ok, berikut contohnya :

1. Buat Type Enum dengan perintah berikut :

# CREATE TYPE jk AS enum ('L','P');

jk = nama type

2. Kemudian Buat Tabelnya :

# CREATE TABLE jeniskelamin (nama varchar(30), jenis jk);

3. Sekarang coba insert data dengan data yang benar :

# insert into jeniskelamin values ('abby','L');

4. Tetapi jika insert data dengan data yang salah :

# insert into jeniskelamin values ('abby','X');

maka akan muncul peringatan :

ERROR:  invalid input value for enum jk: "X"
LINE 1: insert into jeniskelamin values ('abby','X');

Semoga Membantu..

Membuat Auto_Increment di PostgreSQL

Sebelumnya sempat bingung dengan yang namanya auto_increment di PostgreSQL, Berbeda dengan Mysql yang cukup sangat mudah, Ok, berikut saya contohkan :

Perintah di Mysql :

CREATE TABLE data (id_data int auto_increment primary key);
Query OK, 0 rows affected (0.13 sec)

Tetapi Jika perintah diatas dilakukan di PostgreSQL maka akan muncul :

CREATE TABLE data (id_data int auto_increment primary key);
ERROR:  syntax error at or near "auto_increment"
LINE 1: CREATE TABLE data (id_data int auto_increment primary key);

Berikut caranya :

Contoh untuk table yang belum dibuat :

1. Buat Sequence :

# CREATE SEQUENCE data_sequence;

2. Buat Table :

# CREATE TABLE data (id_data int default nextval('data_sequence'));

3. Lihat type table nya :

# \d data;

Contoh untuk table yang sudah dibuat :

1. Buat Sequence :

# CREATE SEQUENCE data_sequence;

 2. Rubah dengan perintah Alter :

# ALTER TABLE data alter id_data set default nextval('data_sequence');

Semoga Membantu..