MySQL vs Microsoft SQL

I've been learning Microsoft Access 2007 in a computer class at college and see that it's a very powerful database program... I realize that it might not be the same as a web server database, but I was thinking about switching to MSSQL.

What are the biggest differences between开发者_JAVA百科 MySQL and MSSQL? Does MS have any pros or cons that My might not have? How would one benefit me more than the other?

I have used extensively MS SQL Server. The main difference is MS SQL Server works only with Windows platform. MySQL works with all platforms. MySQL is open source and now bought by Oracle, so no one knows the fate of MySQL. If you working with .NET or Windows platform, I would suggest working with MS SQL, else the obvious choice is MySQL. LAMP uses MySQL. Big companies like Facebook uses LAMP and hence MySQL. Many banks like Chase use .NET and hence mostly MS SQL Server. If you are a Windows fan, go for MS SQL server. If you are a guy who believes in Open source, go for MySQL.

You can read a complete comparision in mysql and mssql here. They have completely described each and every thing

Both platforms are good to learn on but it probably will come down to preference and what it is you want your database to do. MySQL comparatively is a much simpler product in terms of extra features but still manages to be a powerfull plateform especially when you compare it to access.

From a development standpoint MS SQL is going to have a much richer version of SQL where MySQL is going to have a much more basic version of SQL. The Management Studio that comes with MS SQL as well is also an advantage over MySQL - although there are some other ones you can download to interact with MySQL.

From an administrative/implementation/server side in terms of extras MS SQL is going to win hands down with the rich High Availability features (edition depending) as well all the other little bits and pieces that come packaged with the system MySQL just cant compare to what comes packaged natively.

It is likely going to come down to personal preference though and what it is you want to build. MySQL rules on the web and linux fronts but really lacks a lot of advanced functionality that comes with MS SQL.

Depends what your end goal is. If it's preparing for a job then I suggest exposing yourself to both so that you're familiar and in doing so you hopefully will be able to find which one you have a preference and from there you can start to focus on that particular version to improve your skills and develop experience. Regardless though I would recommend getting familiar with both. My job uses MS SQL but I just got a consulting gig doing MySQL and I keep getting caught up on small differences - it's frustrating. Wish I'd seen it before in college.

Here are the differences in SQL syntax between MSSQL and MySQL

In MSSQL the table names and column names are enclosed in double quotes or square brackets whereas in MySQL table names and column names are enclosed in backtick (“`”) character

Example

MSSQL CREATE TABLE "Employees" ("Empno" VARCHAR(10),"EmpName" Varchar(100) ......

SELECT [Empno],[EmpName] from "Employees" ...... MySQL CREATE TABLE Employees (Empno VARCHAR(10),EmpName Varchar(100) ......

SELECT Empno,EmpName from Employees ......

Enclosing identifier names in enclosing character is optional in both database softwares, but if the identifier name contains blank spaces then it becomes mandatory to enclose it within double quotes or square brackets.

For Example the following statement can be written like this

MSSQL CREATE TABLE "Employees" ("Empno" VARCHAR(10),"EmpName" Varchar(100) ...... MySQL CREATE TABLE Employees (Empno VARCHAR(10),EmpName Varchar(100) ...... or without enclosing character like this.

MSSQL CREATE TABLE Employees (Empno VARCHAR(10),EmpName Varchar(100) ......

MySQL CREATE TABLE Employees (Empno VARCHAR(10),EmpName Varchar(100) ......

but if the identitfier name contains blank spaces then you have to enclosed it with double quotes or square brackets

MSSQL CREATE TABLE "Employees Table" ("Emp No" VARCHAR(10),"EmpName" Varchar(100) ......

MySQL CREATE TABLE Employees Table (Emp No VARCHAR(10),EmpName Varchar(100) ......

you can't write it like this

MSSQL CREATE TABLE Employees Table (Emp No VARCHAR(10),EmpName Varchar(100) ......

MySQL CREATE TABLE Employees Table (Emp No VARCHAR(10),EmpName Varchar(100) ......

In MySQL if you turn on ANSI_QUOTES SQL mode option with the following command

mysql> SET sql_mode='ANSI_QUOTES';

then MySQL also allows to quote identifiers within double quotation marks. But remember when you enable this option you cannot quote literal strings in double quotes in SQL statements. you have to use only single quotes for quoting literal strings in SQL statements

Case Sensitive In MS SQL if a database is created with Case Sensitive COLLATION then table names and column names are case sensitive otherwise, if the database is created with a Case Insensitive Collation then identifier names are case insensitive

For Example

If you created a table in Case Sensitive Collation database, like this

create table Employee (SNo int,Name Varchar(100),Sal money) Notice the captial E in tablename

Then if you give the following command

select * from employee It will give error