Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Which SQL is the best? 2
- Thread starter skia
- Start date
Even MS Access accepts SQL queries. What you need to know is wich DBMS you need.
SQL is a standardized and very simplelanguage.However, you may find some syntax differences, and limitations due to DBMS nature. I've learn't lately that we can't use nested queries with mysql (!) That's not a big inconvient if you intend to use it with a scripting language.
I knew also that beside to oracle & sql server (wich are not inexpensive at all) there are others DBMS like ovrimos ?!?
Conclusion : my sql is "enough free and performant (?) to get the preference of all programmers".
SQL is a standardized and very simplelanguage.However, you may find some syntax differences, and limitations due to DBMS nature. I've learn't lately that we can't use nested queries with mysql (!) That's not a big inconvient if you intend to use it with a scripting language.
I knew also that beside to oracle & sql server (wich are not inexpensive at all) there are others DBMS like ovrimos ?!?
Conclusion : my sql is "enough free and performant (?) to get the preference of all programmers".
- Thread starter
- #4
url: book: Martin Gruber "Mastering SQL"
-
1
- #7
MYSQL is the BEST trade off of price(actually, its father WAS free and is STILL less capable, but is now MORE expensive!), performance(can be one of the fastest), capabilities(it lacks foreign keys, and SUPERSET features), compatible(perhaps THE most compatible), and simplicity(one of the simplest to install! postgresql is ALSO free. and has all the high end capabilities, but is much harder to install).
BTW, SOME people will tell you about triggers and stored procedures, etc.... This is not REALLY SQL. You need superset features to at least work with it. The reason for this is because they are attempts to do what SQL was never made for, and each company implemented it in their own way.
With M/S SQL and Sybase, this superset is called T-SQL or Transact SQL. There are enough differences between the two to give you problems. With Oracle it is PL/SQL. DB2, postgresql, and informix have OTHER standards.
WHICH do you want to learn?
By ALL means, LEARN MYSQL. Almost EVERYTHING in the language works in almost every other database using SQL! It has a FEW different types, and a couple different names for things like an autoincrementing field, but it is basically just SQL!
As for what you should learn next? These are in order by capability and relative popularity, based on my experience.
1st choice for WINDOWS? M/S SQL!!!!!!!! (Might as well learn Sybase as well)
1st choice for UNIX or a close second for windows? ORACLE
2nd and 3rd choices? Probably DB2 and Informix.
REMEMBER though, that you should learn BASIC SQL, and you can go from there. BASIC SQL is easy to learn. It was intended simply as a simple datamanipulation language with the application doing the bulk of the processing. Stored procedures, triggers. etc.... On the other hand can be quite extensive, and lack only a screen I/O interface.
Steve
BTW, SOME people will tell you about triggers and stored procedures, etc.... This is not REALLY SQL. You need superset features to at least work with it. The reason for this is because they are attempts to do what SQL was never made for, and each company implemented it in their own way.
With M/S SQL and Sybase, this superset is called T-SQL or Transact SQL. There are enough differences between the two to give you problems. With Oracle it is PL/SQL. DB2, postgresql, and informix have OTHER standards.
WHICH do you want to learn?
By ALL means, LEARN MYSQL. Almost EVERYTHING in the language works in almost every other database using SQL! It has a FEW different types, and a couple different names for things like an autoincrementing field, but it is basically just SQL!
As for what you should learn next? These are in order by capability and relative popularity, based on my experience.
1st choice for WINDOWS? M/S SQL!!!!!!!! (Might as well learn Sybase as well)
1st choice for UNIX or a close second for windows? ORACLE
2nd and 3rd choices? Probably DB2 and Informix.
REMEMBER though, that you should learn BASIC SQL, and you can go from there. BASIC SQL is easy to learn. It was intended simply as a simple datamanipulation language with the application doing the bulk of the processing. Stored procedures, triggers. etc.... On the other hand can be quite extensive, and lack only a screen I/O interface.
Steve
steve1, I looked further in mysql manual and I discovered more about compatibility of mysql with other SQLs...
also I discovered a thing that seems to be ignored -- or may be I'm wrong -- that even if mysql don't support nested queries, it uses join very simply... correct me :
select employee_name, dept_name
from employees, departments
where employees.dept_nb = departments.dept_nb
it is not possible in other SQLs, no ?
another question, about security ?
dbases are dump in sql files and not in native format... any security issue against that ?
thanks
also I discovered a thing that seems to be ignored -- or may be I'm wrong -- that even if mysql don't support nested queries, it uses join very simply... correct me :
select employee_name, dept_name
from employees, departments
where employees.dept_nb = departments.dept_nb
it is not possible in other SQLs, no ?
another question, about security ?
dbases are dump in sql files and not in native format... any security issue against that ?
thanks
Yeah, I forgot. Sub queries ARE standard SQL, and not CURRENTLY supported in MYSQL. In MY uses of mysql, and many other real world places, it isn't much of a limitation. In a reporting app, for example, the sub query may be run seperately to load a selection list, and the items selected from there would be used to limit the main query. I program around everything else. Some of the more powerful features of a subquery(such as cross query updates) are NOT standard SQL.
As for joins, It handles only the inner and left outer joins. That covers MOST cases! It also doesn't support UNION, MINUS, or INTERSECT. Quite frankly, these are RARELY used.
As for database dumps, these are ALSO utilities that were not standardized. You DON'T want security in dumps because it limits their use. Of course, you COULD pipe them through encryption programs, etc.... Frankly, most databases don't handle this any differently. They tend to export human readable data in delimited or fixed format or sql statements.
Oh well, it doesn't really change what I said. You can learn:
BASIC SQL (including select, insert, update, delete, group, order, etc....)
IN, BETWEEN, LIKE, and other operators
ALIASES
Most INDEXES
Most standard DDL
Various admin concerns
Most JOINS
AND, you can run MOST software, including PERL, ODBC, web, etc... and do it for FREE! Heck, some of the stuff they don't support is SO overused it is SICK! For example... A join is preferable to a subselect when you can use it. Full outer joins are RARELY used except for exception processing, and improper key retrieval is OFTEN a problem. Left v. Right outer joins are EXACTLY the same, only the order of statement operands is different.
When *I* was learning SQL, they didn't have M/S SQL, and the closest you could get without spending a fortune was PostQuel(the old postgres) or Quel(the old ingres)! I thought mSQL was great when it came out. MYSQL, even today, is like mSQL on steroids.
NOBODY, not even the authors of MYSQL, claimed it was 100%. In another 2 years though, it COULD end up nipping at Oracles heels with all the bells and whistles. It is ALREADY hurting Oracles market share. Oracle claims they have 90% of the web. Read the fine print. They REALLY say that the top 45 of the top 50 .com companies use their product. Heck, the top 50 .com companies PROBABLY use MYSQL and M/S SQL as well. There are a few things that are nicer than Oracle, and MOST of the rest is planned. I was disappointed not to see as much in 4.0, as they had planned. Let's wait until 4.2 though.
Steve
As for joins, It handles only the inner and left outer joins. That covers MOST cases! It also doesn't support UNION, MINUS, or INTERSECT. Quite frankly, these are RARELY used.
As for database dumps, these are ALSO utilities that were not standardized. You DON'T want security in dumps because it limits their use. Of course, you COULD pipe them through encryption programs, etc.... Frankly, most databases don't handle this any differently. They tend to export human readable data in delimited or fixed format or sql statements.
Oh well, it doesn't really change what I said. You can learn:
BASIC SQL (including select, insert, update, delete, group, order, etc....)
IN, BETWEEN, LIKE, and other operators
ALIASES
Most INDEXES
Most standard DDL
Various admin concerns
Most JOINS
AND, you can run MOST software, including PERL, ODBC, web, etc... and do it for FREE! Heck, some of the stuff they don't support is SO overused it is SICK! For example... A join is preferable to a subselect when you can use it. Full outer joins are RARELY used except for exception processing, and improper key retrieval is OFTEN a problem. Left v. Right outer joins are EXACTLY the same, only the order of statement operands is different.
When *I* was learning SQL, they didn't have M/S SQL, and the closest you could get without spending a fortune was PostQuel(the old postgres) or Quel(the old ingres)! I thought mSQL was great when it came out. MYSQL, even today, is like mSQL on steroids.
NOBODY, not even the authors of MYSQL, claimed it was 100%. In another 2 years though, it COULD end up nipping at Oracles heels with all the bells and whistles. It is ALREADY hurting Oracles market share. Oracle claims they have 90% of the web. Read the fine print. They REALLY say that the top 45 of the top 50 .com companies use their product. Heck, the top 50 .com companies PROBABLY use MYSQL and M/S SQL as well. There are a few things that are nicer than Oracle, and MOST of the rest is planned. I was disappointed not to see as much in 4.0, as they had planned. Let's wait until 4.2 though.
Steve
-
1
- #10
>BTW, SOME people will tell you about triggers and stored procedures, etc.... This is not REALLY SQL.
Were you implying that such things as foreign keys, transactions, and views belong in the above list also? If so, all I can say is ouch! What is or is not SQL can be argued (SQL89, SQL92, SQL99?) but all of that "stuff" is definitely part of the relational data model, of which SQL is just an incomplete implementation.
If you want to face the serious facts about databases, spend some time here: (A tip: C.J. Date, one of the contributors to this site, is also one of the guys who actually *discovered* the concept of the relational data model. He worked directly with the main dude; Codd)
I will start by saying that I have enjoyed using MySQL for some purposes, but you have to be careful to use the right tool for the job. Programming around certain deficiencies is fine if you are always the one dealing with a certain application. If your application becomes part of a larger project, with multiple people, look out! If your data is truly important to you, a true relational model is critical, and MySQL does not offer that.
I agree that MySQL is often a good choice for the job, but still--a little restraint, please. I have used MySQL for years, and have even done some fairly complex web apps in it. But on a couple of these, I fairly quickly realized that MySQL was not going to do the job, especially when related foreign keys got corrupted, or when I had to use a proliferation of temporary tables to emulate views, or when I wanted to do such a simple thing as "DELETE table1.* FROM table1,table2 WHERE table1.id = table2.related_id" (not in MySQL).
For a web-based system that is mainly read-only, or for systems where the data is not strongly interdependent, MySQL can be great. It is definitely fast and easy to work with. But the above poster obviously has not dealt with PostgreSQL in quite awhile. The current version installs just as easy as you can imagine, especially if you just want to use the binary install for your platform.
While MySQL might "nip" at the heels of Oracle in a couple years (I doubt it) PostgreSQL is doing that now, with almost perfect SQL99 compliance.
Here's my take:
You want a quick and easy place to log page visits on your site? Use MySQL. Want to store the overall structure of your website in a database, and use PHP to output? Use MySQL. Want an online product list, etc... fine: use MySQL. You want to manage a multi-user web-based application with critical business data? Use PostgreSQL. Want to process online transactions where you separate people from their money? Use PostgreSQL. Fair enough. -------------------------------------------
"Calculus is just the meaningless manipulation of higher symbols"
-unknown F student
Were you implying that such things as foreign keys, transactions, and views belong in the above list also? If so, all I can say is ouch! What is or is not SQL can be argued (SQL89, SQL92, SQL99?) but all of that "stuff" is definitely part of the relational data model, of which SQL is just an incomplete implementation.
If you want to face the serious facts about databases, spend some time here: (A tip: C.J. Date, one of the contributors to this site, is also one of the guys who actually *discovered* the concept of the relational data model. He worked directly with the main dude; Codd)
I will start by saying that I have enjoyed using MySQL for some purposes, but you have to be careful to use the right tool for the job. Programming around certain deficiencies is fine if you are always the one dealing with a certain application. If your application becomes part of a larger project, with multiple people, look out! If your data is truly important to you, a true relational model is critical, and MySQL does not offer that.
I agree that MySQL is often a good choice for the job, but still--a little restraint, please. I have used MySQL for years, and have even done some fairly complex web apps in it. But on a couple of these, I fairly quickly realized that MySQL was not going to do the job, especially when related foreign keys got corrupted, or when I had to use a proliferation of temporary tables to emulate views, or when I wanted to do such a simple thing as "DELETE table1.* FROM table1,table2 WHERE table1.id = table2.related_id" (not in MySQL).
For a web-based system that is mainly read-only, or for systems where the data is not strongly interdependent, MySQL can be great. It is definitely fast and easy to work with. But the above poster obviously has not dealt with PostgreSQL in quite awhile. The current version installs just as easy as you can imagine, especially if you just want to use the binary install for your platform.
While MySQL might "nip" at the heels of Oracle in a couple years (I doubt it) PostgreSQL is doing that now, with almost perfect SQL99 compliance.
Here's my take:
You want a quick and easy place to log page visits on your site? Use MySQL. Want to store the overall structure of your website in a database, and use PHP to output? Use MySQL. Want an online product list, etc... fine: use MySQL. You want to manage a multi-user web-based application with critical business data? Use PostgreSQL. Want to process online transactions where you separate people from their money? Use PostgreSQL. Fair enough. -------------------------------------------
"Calculus is just the meaningless manipulation of higher symbols"
-unknown F student
petersJazz
Programmer
Much can be argued about mySQL and standard SQL. The site has a validator where you can enter a sql statement and check if its standard.
So, stored procedure is part of the standard (but only used by MimerSQL och som IBM dbms). And views.
I think mySQL is good, but I would like transaction handling, subselects and views.
So, stored procedure is part of the standard (but only used by MimerSQL och som IBM dbms). And views.
I think mySQL is good, but I would like transaction handling, subselects and views.
- Status
