sql
 

What are the main differences between mySQL, postgreSQL, and MS access.

Do all use the same sql query language.

If I know a little sql from using mySQL, will I be able to move easily into postgreSQL or Access?

Are there many differences when using with C, Python, Java, php, etc.

I am familiar with mysql, but I may have to use others for work.

Thank You,
naflan

If you have experience with the mysql client, you won't have problems with the others.

I'd say the main differences are the slight sql command differences in the implementation, and how each one of those packages stores data. Also some features may be absent (ex. stored procedures in mysql, at least in the 3.* series).

Only in syntax, conceptually the approaches are similar.

The differences between the three can be minor ... or they can be major. Depending on what particular things you're looking for, what subset of SQL you use, and what particular version of the product you have.

Of the three you mentioned:

1. PostgreSQL is the one "industrial strength" relational database, one that's arguably on par with Oracle and SQL Server

2. I'm biased about mySQL, because with the last version I used (mySQL 3.23, about a year ago), I was literally *shocked* at some of the "basic SQL commands" that mySQL *didn't* support (sub-selects, triggers and stored procedures come immediately to mind).

Nevertheless, times have changed (the current version is 5.1), and I'm sure mySQL's capabilities have increased dramatically.

Furthermore, even in 3.23 and before, mySQL had the following advantages:
a) the subset of SQL it supported (combined with the Perl, Java and/or PHP environments was typically used with) arguably supported all the SQL anybody ever needed
b) it ran blindingly fast and was solid as a rock
c) it was (and, of course, still is) a true client/server relational database

3. Which brings us to MS-Access.
MS-Access is Windows-only (the .mdb file contains data; it requires the Microsoft Jet runtime to use that data); it is file-based (not client-server), it's not terribly secure, and it's not terribly scalable.

However, if you want to throw together something quick'n'dirty and run it on a Windows client - it can be convenient as all heck.

Soooooo:
1. If you want a quick'n'dirty Windows database, I would suggest you look at SQL-Server first ... but you'll probably find yourself going with MS-Access.

2. If you want a robust, high-performance web site without paying Oracle's, Microsoft's or IBM's usurious license fees, then mySQL will probably be your best choice.

3. If you're a student trying to "learn SQL", you should *not* (under ANY circumstances!) use MS-Access, and you probably shouldn't (based on my past experience) use mySQL.

As far as "learning SQL", either postgreSQL (Open Source), MS SQL Server Developer Edition (a nominal fee: soemthing like $20.00). Oracle Developer Edition (last I heard, available as a free download) or IBM DB2 (also, I believe, a free download) are your best choices.

IMHO .. PSM

as far as i understand, the 'front-end' for m$ access is vb. so there is no sql going on in there.

correct?

my vote is for my-sql, if you are a linux developer (especially with web apps) you cannot live without a lamp configuration. -e.g.- look at bugzilla source.

No, even in VB you send "command strings" consisting of SQL "select"s, "updates" and the like.

My original recommendations still stand:
1. Quick'n'dirty, Windows-only: MS SQL-Server (e.g. Developer Edition) first ... but you'll probably find yourself going with MS-Access.

2. Good, reliable, cross-platform ... but not necessary "standard SQL": mySQL

3.To "learn SQL": 1st choice: postgreSQL
Good alternatives (with their own pros/cons): MS-SQL, Oracle, IDB2 (developer editions ONLY, else mega-$$$)
Honorable mention: mySQL (at last look, too much "standard SQL" not available to be good learning tool)
No-way-not-under-any-circumstances!: MS-Access

I agree with Paul regarding MS-Access. Avoid it at all costs. If you want an MS-only solution, MS SQL Server is much better. (And you can download the MSDE edition for free now. It's more limitted than the full thing, and doesn't come with the client tools, but still much better than Access.)

As he also said, MySQL doesn't have some stuff that is fairly standard in most DBMS apps like sub-selects. I still use it as my main DBMS in Linux, though. Most sub-selects can be turned into various joins. (Which are generally more efficient, anyway.) I think I've seen that the latest version adds stored procs, but I haven't really looked into it much. It may add sub-selects too, but I'm really not sure of that, either.

FYI, sub-selects arrived in MySQL 4.1.
Watch out for MySQL silently truncating values if they don't fit &/or ignoring other probs eg cvt string to num. One thing that bothers me is data is not case sensitive in eg varchars, unless you also specify BINARY as col sub-type eg
mycol varchar(16) binary