Difference between MySQL/SQLite/etc databases?

Question

This is my first time trying to work with a database, so bear with me.

I need to write a program that will use a database that I do not have access to yet. I know there is MySQL, SQLite, and a bunch of other SQL things, but I'm not sure what the difference between them is. Do I need to know what kind of database it is before I can use it (i.e., is a MySQL .db file different from a SQLite .db file?), or is the file itself going to be the same and the difference is how it is accessed?

EDIT: I am programming for an Android tablet, that probably matters. But I will not be creating the database, it will be given to me and I have to work with it.

Answer 1

You might want to run through a view tutorials creating a MySQL database, and a SQLite instance to see how they work and how you can easily interact with them.

The access to each involves adapters, notwithstanding creating the tables you'll need, the user access (username / password / role to access the table).

Here's a straight-forward sqlite tutorial: http://zetcode.com/db/sqlite/ https://zetcode.com/php/mysqli/ http://zetcode.com/php/pdo/

Here's a staight-forward sql with C# tutorial: https://zetcode.com/csharp/mysql/

I remember being overwhelmed with databases my first time. My advice is to start with something well-supported, with a nice community, and search results a-plenty of tutorials, where you can grasp the fundamentals.

Then, based on your application requirements, and platform constraints, you can determine where to pivot. But reviewing the differences between databases without actually having worked with them is like trying pieces of sushi without ever eating fish.

EDIT:

If you're building on Android, yep, learn SQLite.

I highly recommend this tutorial to help you see SQLite used in an Android project: https://developer.android.com/training/data-storage/sqlite

For data abstraction / ORM, you should familiarize yourself with Content Providers and Cursors (http://developer.android.com/guide/topics/providers/content-providers.html)

Good Querying!

Answer 2

First off, you'll need to know what type of database it is before you can connect to it as you'll need to use the appropriate database driver for your platform. mySQL, Oracle, Postgres, SQL Server, etc will all use different drivers as the binary protocol used to talk to them (authenticate and transfer information back and forth) is different.

As far as your actual SQL code, it depends. For the most part, you can count on SELECT * FROM Foo working with any SQL compliant database on the planet. However, once you start doing anything more complicated or using non-standard keywords, you might run into trouble. For example, some databases support the USING keyword for JOINs and some don't.

One thing you might look into is an ORM. This will allow you to abstract the actual SQL dialect from your program and then (for the most part) not have to worry about the actual SQL. Some popular ORMs are ActiveRecord (for Ruby on Rails) and Entity Framework for .NET. Hope this helps!

UPDATE:

Since the Android tag was just added to the post, I did a quick search for SqlLite ORMs that support Android and came up with this. Maybe worth checking into, or perhaps someone can comment on whether it's any good :)