INSERT IGNORE

An error will occur when entering a new record in MySQL, if the primary key specified in the insert query already exists. Using the keyword "IGNORE" to prevent errors occur because there is a duplication of records and other queries can still be executed.

Why ??

You should not try to insert a record without first checking if the primary key you want to use already exists, there may be time for this query is required, such as when some developers need to update their own data backups of the database, and a particular record may already exist in other database tables.


Inserting a single record
The Query is very simple, just add "IGNORE" after "INSERT" as:

INSERT IGNORE INTO pegawai
    (primaryKey, NIK, no_meja)
VALUES
    ('abc', 1, 2);

Inserting multiple records
When inserting multiple records at once, record the exact same as the above data will be ignored and that no records will be in the right insert.

INSERT IGNORE INTO pegawai
    (primaryKey, NIK, no_meja)
VALUES
    ('abc', 1, 2),
    ('def', 3, 4),
    ('ghi', 5, 6);

Well from the above data, record 'abc', 1, 2 will not be in the insert because the data already exists.

Conclusion : Insert ignore used when we want to add records are records that may already exist in the table in the insert so that no data redundancy (duplicate).


Don't miss it
vote web ini yaa klik disini!!

Select the select query (double select)

Running SELECT query in the SELECT will need to do when we want to retrieve database information let the results more quickly. The conventional way which is often used is to query 2 times so that the processing will take longer.
Well as an example, we will create a table in a database table that province, which contains the name of the province and population.

CREATE TABLE `propinsi` (
`nama_propinsi` varchar(100) collate latin1_general_ci NOT NULL,
`jumlah_penduduk` int(20) unsigned NOT NULL,
PRIMARY KEY  (`nama_propinsi`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `propinsi` VALUES ('Jatim', 100000);
INSERT INTO `propinsi` VALUES ('Jateng', 200000);
INSERT INTO `propinsi` VALUES ('Jabar', 300000);
INSERT INTO `propinsi` VALUES ('DKI Jakarta', 400000);




Once the table is formed, we will create a query to display the name of the province whose total population is larger than the population of the province jateng.
Logic generally we find first the total population (n) jateng province, and then we are looking for a larger population of n earlier. 2 query's happening ..
In this case, we will use a SELECT query in the select:

select nama_propinsi from propinsi where jumlah_penduduk >
(select jumlah_penduduk from propinsi where nama_propinsi='Jateng');

The output of the query are the provinces of West Java and Jakarta, which means that provincial population is larger than the population of the province of West Java and Central Java is Jakarta.

So, select the SELECT query is enough to use when we need a filter from another table in one database

Well it is quite simple.

Hopefully this information useful.


Don't miss it
vote web ini yaa klik disini!!

Command AWK

Shell script

As a novice in the world's script' shell, the command 'awk' is something that must I understand to be the level further. Why? because 'awk' proved to simplify my current job 
Well, Let's go .. 
What is the real 'awk' it? 
'AWK' asalah utility similar to 'sed' which serves to find a pattern in a file and perform an operation. 'AWK' own stands behind the names of its creators, namely Alfred V. Aho, Peter J. Weinberger, and Brian W. Kernighan. Hmm ... that's it seemed as the introduction of 'awk', we just go into how to use it. 
Now we try the basics first ... 

bash-3.00$ date

Mon May 17 10:29:45 WIB 2010
bash-3.00$ date | awk '{print $1}'
Mon
bash-3.00$ date | awk '{print $4}'
10:30:01
bash-3.00$ date | awk '{print $3,$2}'
17 May
bash-3.00$ date | awk '{print $3$2}'
17May

'Awk' can also be used in arithmetic operations, like addition (+), subtraction (-), multiplication (*), division (/), the rest of the division / modulus (%), and so on. For its use, can be seen in the following example: 

bash-3.00$ echo 2 3 | awk '{print $1 + $2}'
5
bash-3.00$ echo 2 3 | awk '{print $1 - $2}'
-1
bash-3.00$ echo 2 3 | awk '{print $1 * $2}'
6
bash-3.00$ echo 2 3 | awk '{print $1 / $2}'
0.666667
bash-3.00$ echo 5 3 | awk '{print $1 % $2}'
2

This time we will go next step make the script into a file and run it. 
first create a file with a name pelanggan.awk

bash-3.00$ vi pelanggan.awk

EXCELCOMINDO  INCOMING   42542    32230    254786
HUTCHINSON    INCOMING   2456     16953    573586
MOBILE-8      INCOMING   15944    11477    692939
MOBILSEL      INCOMING   1680     91188    951394
NATRINDO      INCOMING   2198     19826    154859

After that we save 
Now make the script. Create a file named print.awk

bash-3.00$ vi print.awk

BEGIN {
  print "\tKolom 1\tKolom 2"
  kolom1=0
  kolom2=0
}
{
  kolom1+=$3
  kolom2+=$4
  print "\t"$3"\t"$4
}
{
  kolom3=kolom1/5
  kolom4=kolom2/5
}
END {
    print "Jumlah:"kolom1"\t"kolom2
    print "Rata:\t"kolom3"\t"kolom4
}

Then save. To menjalanakannya type:

bash-3.00$ awk -f print.awk pelanggan.awk

The result:

        Kolom 1    Kolom 2
        42542      32230
        2456       16953
        15944      11477
        1680       91188
        2198       19826

Jumlah: 64820      171674
Rata:   12964      34334.8

All functions that are owned by the 'awk' can also be used together. For example, I want to add up the size of some files starting character file name is abe, then simply by typing: bash-3.00$ ls -l abe* | awk '{sum=sum + $5} END {print sum}'

197904

At the command ls-l shows the field size of a file is on the field to-five ($ 5), ls-l abe * be used to list files beginning with abe. As for the command 'awk' is used a variable sum to save the sum of $ 5 on each line / file name, the last line in the file list is displayed the value of the variable sum. 


Actually there are very many secrets of 'awk'. Finally, Thanks for your attention and good bye

Don't miss it

vote web ini yaa klik disini!!