As always, the official SQLite documentation is a good place to start learning about SQLite. The official site also has a comprehensive list of tools and resources for various platforms and programming languages.
If you're programming for iOS, you can use the SQLite C library, which is included with XCode. However, it can be cumbersome to use, so FMDB is a nice wrapper around it.
On the desktop, SQLite is easy to use - just use the .NET library mentioned above. On iOS however, SQLite can be tricky to get working with Unity, but it is possible. Here is a good tutorial walking through it.
For desktop Java applications, you can use the JDBC driver for SQLite.
SQLite is included with Android, so there's no need to add any special libraries to an Android project.
If you want to include a prepopulated SQLite database with an Android app, SQLite Asset Helper can be useful for including and loading the database file.