The SQL world outside MySQL - Well I never knew

I have been using MS SQL Server this week in my new job. I believe several things of this before I even touched it, some bias some guesses.
A. I do not like most MicroSoft work. Some times they have done something usable like Word 5 but then they add a lot of rubbish.
B. I have seen it lock up alot in a dev environment when I have not seen the same of MySQL.
C. I would rather have linux live servers not Windows.
D. I have not used it much.

I do not think MySQL is the best database but it is damn good with websites and PHP for my sites so far. It might not be what I'd choose for a bank for example. But straight data it is fine.

I have been using it to add stuff to a Intranet admin and to a website. Now the connection etc is happening in a library so I have not seen anything getting it to PHP it is just through SQL in the gui tool that I am learning.

Now the first thing is I do not seem to able to be lazy and edit the tables phpMyAdmin style. But I think I have seen that done before. I have liked that you can have lots of selects in the same window and running it brings a table for each. And highlighting it runs just that command. But then I came to date fields. It seems that the server returns the date column as "27 Aug 2007 10:20:22" and it does not have simple formating in the SQL to getting to return differently! Why is it returning words in the date format when I have not asked for it. There was a small extra bit of code that I was shown that you passed a single format id to to return a different format, not very nice though. So with a couple of functions it would return "27.08.2007 10:20:22.234" which is more what I would like.

The SQL is TRANSACT-SQL so I know I have to learn more syntax. That I do not mind so much. But when I asked if I could format the date I was told use PHP to do it. That seemed like a fair answer but it is a database dates are a key part. The thing that annoyed me about that was that feels like a Microsoft developer response, "It just is that way, use another tool to hack around with the result." I think it should work better.

I had something else too, just have to remember what.

I Remember GROUP BY in SQLServer you have to have all the fields in the SELECT also in the GROUP BY. Now on MySQL I can happy group by something and then pull out all other data in the select now limited.

I learn this from my manager and then on the www.themysqlguy.com podcast on the same day, that MySQL has a mode to behave more like a traditional DB and not so forgiving. This "MODE" or TRADITIONAL also then has the option to turn on all fields for a group by must be in the select. Then from that I read in the manual, from his links. SQL Mode in the Manual

TRADITIONAL

Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. Note: The INSERT/UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a non-transactional storage engine, because data changes made prior to the error may not be rolled back, resulting in a “partially done” update.

And

ONLY_FULL_GROUP_BY

Do not allow queries for which the SELECT list refers to non-aggregated columns that are not named in the GROUP BY clause. The following query is invalid with this mode enabled because address is not named in the GROUP BY clause:

SELECT name, address, MAX(age) FROM t GROUP BY name;

As of MySQL 5.1.11, this mode also restricts references to non-aggregated columns in the HAVING clause that are not named in the GROUP BY clause.

Also that in this traditional mode when inserting data for columns that are tool long will cause an out of range error, rather that truncating. This might be good for one insert but several on a non-transactional table could mean you only add half the data before the error.

So I learned something I did not know before about how data is treated on insert. I might not think the Group by is right and the Date rubbish but I learned something new.