Category Archives: Programming/Database

anything related to programming stuffs or tools
database related stuffs

Protected: Oracle : oracle 12c installation on Oracle Linux 7 ( and CentOS 7 )

This content is password protected. To view it please enter your password below:


MySQL : drop table with prefix

    AS statement FROM information_schema.tables 
    WHERE table_schema = 'nama_pengkalandata' AND table_name LIKE 'namaprefix_%';

run those SQL in mysq terminal or phpmyadmin or any mysql client. It will generate ‘drop table’ syntax.


Mysql show only column names

2 type of sql to show only column name

1.   SELECT column_name
FROM information_schema.columns
WHERE table_schema = ‘DBNAME’
AND table_name = ‘TABLENAME’;


2.  SELECT column_name
FROM information_schema.columns
WHERE table_name = ‘TABLENAME’



Mysql Mass Kill

This method I use to do mass kill on mysql process. After run “show processlist” and notice something.

mysql> select concat('KILL ',id,';') from information_schema.processlist
 where Host like '%oss%' and time > 200 into outfile '/tmp/lock.txt';
mysql> source /tmp/lock.txt;

done.. all the process where host like oss and time more than 200.

mysqlperformanceblog, stackoverflow
tested  on centos 6.5 mysql 5.5

Mysql: insert if record not exist (in case no unique key)

here is the sql syntax used to insert record when not exist :-

INSERT INTO pengguna(nokp,nama,idlevel,statusdata) SELECT * FROM ( SELECT ‘888888’,’namasaya,’1000′,’1′ ) AS tmp WHERE NOT EXISTS ( SELECT nokp FROM pengguna WHERE nokp=’999′) LIMIT 1



mysql : UPDATE table and CONCAT data

Syntax : UPDATE and CONCAT(first,second)

table name : jos_data
| id   | name | images     |
|    1 | John | jonh.jpg   |
|    2 | Ram  | rem.jpg    |
|    6 | Jack | jek.jpg    |
|    9 | Joke | jok.jgp    |

situation 1: 
I want to prefix all data in "images" column to look like "/path/to/img/file.jpg"

solution 1: 
UPDATE jos_data SET images = CONCAT('/path/to/img/', images) WHERE id IN (1,2,6,9);

situation 2:
I want to prefix all data in "images" column to look like "file.jpg--noext"

solution 2:
UPDATE jos_data SET images = CONCAT(images, '--noext') WHERE id IN (1,2,6,9);