If someone knew little about databases and wanted to learn about them from scratch, which database would you recommend learning with and why?
MySQL seems ubiquitous, but are there others that are more modern that have learned lessons from the past, or others that are simply nicer or more logical to work with?
Universal compatibility/libraries is not a big concern, unless it is something truly obscure. Mac (Unix) compatibility is a must.
Edit: Thanks for the answers, all.
From what I've seen (at least on the web), MySQL and PostgreSQL are the most ubiquitous free database systems. If you're considering learning one of them, check this comparison out.
You may also want to consider learning SQLite, a "self-contained, embeddable, zero-configuration SQL database engine." It's really easy to get up and going, stored in a single file, and as its description says, has no complicated configuration. SQLite has proved enormously popular as a persistent data store for local apps on the desktop/iPhone. If you're going down this route on a Mac/iPhone, you may also want to check out Core Data, which is an abstraction layer Apple developed on top of SQLite(but can work with pretty much any DB), to simplify working with a database. As a bonus, Core Data includes a nice GUI for forming relationships and entities. You can check out this tutorial for more information.
I'd go with mysql. It's easy to setup, easy to mess around with via with mysql client, and it's well documented. If you're just starting out, you probably won't need most of the features offered by other databases, like stored procedures and the like.
First of all, MySQL is both ubiquitous and modern.
ANSI SQL is more or less the same in all RDBMs, so you can learn any of them and you'll be good.
Once you've mastered ANSI SQL, then all you've got left is the localized solutions for each one of them, which won't be portable to other systems - and so, totally discouraged to use, unless they simplify your tasks in a way justifying it.
Sad thing about databases is that each and every one works bit differently. I would most likely pick MySQL first and play with it a bit. Then get Postgre and do the same.
If you need to use databases in corporate environment then I would aim to test also Oracle and SQL Server which both have express versions that can be installed free for yourself.
http://www.microsoft.com/express/sql/download/ http://www.oracle.com/technology/software/products/database/index.html
At start all databases are more or less confusing but I would pick MySQL as first because it can do most of the basic functionalities and has a lot of help available.
If you just want to be learning the SQL language, and not database administration, I would recommend working with SQLite. If you're on a Mac, it should already be installed. It is a much simpler system than most RDBMSes; there is no server to set up, and client to connect to the server. There are no directories of cryptic files, or anything of the sort. To get started, you can just type:
sqlite3 mydatabase.db
And start working with it. It's so much lighter weight and easier to set up and use than the other database systems that I think it's a good choice for a beginner.
Now, SQLite is a fairly small and lightweight language. If you need be getting into any kind of really complex queries and data mining, I would recommend PostgreSQL. It has a fairly advanced query optimizer, and a pretty long list of SQL features.
And if you want to learn a database as something to use for back-end storage for web programming or something of the sort, MySQL is what I'd choose. It's ubiquitous, supported by almost any web host, and it's pretty fast for very simple queries and updates, which is generally what you need for a web system. It has some real gotchas to avoid when setting it up; you have to choose between several different storage engines, and it can take a lot of work to convince it to actually work with Unicode data. But it's good to learn mostly for its ubiquity.
MySQL, PostgreSQL, SQLite - pick one. PostgreSQL is more like Oracle, and in my opinion a bit more mature. It's had stored procedures, triggers, and referential integrity longer than MySQL has. I'll admit that I have both installed, but I use MySQL more often because it's quick and easy.
But do be aware that non-SQL alternatives are out there and growing in importance. BigTable, object databases like db4o, are worth being aware of. "No SQL" is out there.
A database in the cloud: Amazon EC2, Google App Engine or Microsoft Azure
If you really, truly, want to "learn from scratch", then theory is the first thing to learn. And that means : NOT products, not any. Not DB2, not MySQL, not oracle, not any-of-them.
Hugh Darwen has a freely available e-book entitled "An Introduction to Relational Database Theory". The material is quite "accessible" and quite unlike most other theory textbooks. It's also the accompanying textbook for his university course on database technology.
Chris Date has several books, of which "Introduction to database systems" is the most comprehensive, also the standard textbook in the field, but maybe a little too abstract for some.
If you think that all you need is "just to know a product" and that you can do equally well "without all that theory", then in that case, please disregard this response, because the wording of your question is dishonest.
If you are just getting your feet wet, MYSQL is a great one to start with. Easy install on any platform, great community support and lots of free tools to work with (SQLYog is a favorite of mine).
I agree that theory is very important. Depending on how you learn best, digging in and tinkering may be the thing to do before you try to absorb 40 years of thought on relational systems.
Codd and Date are legends in the field and can help you understand the broader points of relational theory, but are hard to absorb before you have context for the topic.
If you are looking for more pragmatic/immediate guidance, I'd suggest a book like "Databases for Mere Mortals" and anything written by Joe Celko.
Once you get comfortable with the basics, there are lots of other platforms to explore as well. As mentioned above, SQLLite and PostGres are two other great choices for the Mac OS.
If you want to learn SQL : the best way is to choose database who implement more features of the SQL standard. So I would recommand Firebird or PostgreSQL
Alex wrote: "Reading a textbook without incrementally testing your knowledge on an actual database is not going to produce good results for the majority of people."
My book and my university course both use Dave Vooorhis's Rel for that very purpose.
Hugh
I might be "sidetracking" a bit with this answer, but I think we're in the same situation!
Check out the "The Manga Guide to Databases"! I haven't read it myself yet, but it's on the way in the mail as we speak! I've heard good things about it from friends and colleges, and it's got some good reviews as well. Albeit a bit "controversial," it's supposed to be a fun and surprisingly in-depth introduction to fundamental techniques and principles!