This is because different database vendors have implemented different extension to standard SQL as it suits their needs. So, technically, using auto_increment is not SQL (as it is defined in the ANSI standard).
You can avoid these issues by using an abstraction layer such as Hibernate. [ October 18, 2007: Message edited by: Paul Sturrock ]
Or more simply, learn the concepts and then get a good reference book for your particular flavor. I think it's safe to say that most people will only be working with one RDBMS at any particular time, and learning your system's syntax is not going to be that difficult.