Hilbert's second problem asks if the axioms of arithmetic are consistent. Has this problem been resolved? Shouldn't an axiomatic system ideally be consistent and complete(given that we have the freedom to choose the axioms)? I have read Godel's Incompleteness Theorem but I don't understand it completely.
What implication does this problem have for mathematics as a whole since mathematics can be regarded as the study of patterns and structures within the framework of an axiomatic system?
On
A first order theory is inconsistent if it can prove every well-formed sentence. This is equivalent to being able to a sentence $A$ and it's negation $\neg A$.
The Incompleteness Theorem of Godel asserts that sufficiently strong axiom systems which include well-known theories such as Peano Arithmetics and ZFC set theory can not prove its own consistency unless it already inconsistent.
This above answers Hilbert's question : If Peano Arithemtics could prove itself to be consistent, then it was already able to prove every statement, i.e. is inconsistent. A axiom system in which everything is proveable is not very interesting.
These incompleteness result do not cause any major harm to mathematics as it is practiced. Indeed, you can not prove in Peano Arithmetics or ZFC that Peano Arithmetic or ZFC, respectively, is consistent, but, as most mathematicians do, you can just assume your axiom system is consistent (as no one has yet to find an inconsistency) and continue on your business.
So the consistency of theories is not mathematically proveable within itself. Assuming the consistency of stronger axiom systems, you can prove the consistency of the weaker. For instance assuming the consistency of ZFC, you can prove the consistency of Peano Arithmetics. Again by the incompleteness theorem, it is impossible to establish the consistency of ZFC in itself. In end, you will run into the trouble of convincing someone the stronger theories are really consistent.
Hence, you will be able to prove the consistency of your theory using method that are entirely formalizable within some first order theory. Philosophically, it is a very interesting as to whether certain axiom systems are really consistent even if you can't actually prove it formally. Some can argue that Peano arithmetic is a model of some portion of the human reasoning and is as consistent as human reasoning. Some people can informally argue that people have used the concept of arithmetics for thousands of year and applied it to myriads of real world applications without encountering any major problems. All of these are philosophical.
Ultimately, you should consider how much do you believe in arithmetics and how useful it is to you? What do you believe in more that the moon really exists or that arithmetic is consistent!
You raise two issues different (though not unconnected) issues here.
(1) Are the axioms of arithmetic consistent? Which axioms? Suppose we fix on first-order Peano Arithmetic. We know that no theory weaker than PA can prove the consistency of PA. That's because of Gödel's Second Incompleteness Theorem for PA, which says that we can't prove the consistency of PA even assuming PA, so we can't prove it with less. But proving PA's consistency using a theory stronger than PA wouldn't be much use (though we can do it, e.g. in ZF set theory): that's because if we have doubts about the consistency of PA we will presumably have doubts about any theory which is stronger than it. However that leaves the possibility of proving the consistency of PA in a theory which is weaker in some respects but stronger in others. And that can be done. Gentzen did it in 1936, and Gödel himself did it in his Dialectica paper in 1958. Though neither proof is easy to sketch and make plausible in the confines of an answer such as this.
(You might wonder whether either proof could be used to convince someone who had doubts about the consistency of PA. That would depend on the source of those doubts. If the worry is about PA's use of an unrestricted induction rule, then Gentzen's and Gödel's proofs -- since they only use induction for quantifier-free predicates -- might soothe those doubts. And it is certainly a moot point whether either Gentzen's or Gödel's proof is the sort of consistency proof that Hilbert was hoping for -- it is debatable what kinds of reasoning should be acceptable to a finitist working on the Hilbert Programme.)
(2) "Shouldn't an axiomatic system ideally be consistent and complete?" Ideally, maybe. But in this life, we can rarely get the ideal! And no sensibly axiomatized theory can even be complete for the truths of the first-order arithmetic of successor, addition and multiplication. That's what Gödel's First Incompleteness Theorem shows us. (There are plenty of good expositions of that theorem out there: but you can always try my Gödel Without (Too Many) Tears notes, which you can get at http://www.logicmatters.net )