When you first getting started learning how to write SQL code, the first thing that can be very confusing is understanding the different terminology involved. As with any learning endeavor, you have to learn the vocabulary first. So what exactly is the difference between SQL and MySQL? And what about SQL Server and PostgreSQL?
This can be a little difficult to understand when you’re beginning to learn SQL. So let’s take a few minutes to understand the different terminology involved and what it means.
What is SQL?
When we talk about SQL, we are usually talking about Structured Query Language. This is the actual programming language that we use to communicate with a database.
SQL is used for interacting with relational databases. These are databases that store data in tables. You define the columns that you need to store within a table. Then you create rows where each row has every column you defined. The relational part indicates that you can link data in one table to data in another table. We do this using what we call foreign key relationships.
All of this work is done using the SQL programming language. We usually pronounce it as “sequel” (like the sequel to a movie). But some people will spell out “s q l”. They’re referencing the same thing when they do that.
What is MySQL?
MySQL is one of many different SQL database platforms. When you need to store data in a database, you need a piece of software to do that. MySQL is one piece of software you can use. You would install MySQL on a computer – it could be your laptop or a cloud server or a physical server. At that point, you have a MySQL server setup and running. That server is special software that allows you to send SQL commands to create a database, add data to it, and query the data that’s there.
This can be confusing when you first get started. Don’t worry though because as you learn more it gets easier and makes much more sense. The main thing to know is that you need to have software that will run a database server.
You also need software called a SQL client that allows you to connect to that database server to run SQL commands. MySQL provides a tool called the MySQL Workbench that allows you to do this. Note that we also call this an Integrated Development Environment or IDE. This is software that provides you with a graphical user interface for interacting with a database server.
You’ll also be glad to know that MySQL is open source software. This means that there is a community of developers that contributes voluntarily to improve the software. MySQL can be used for free, even for commercial production usage. It is most definitely free to use MySQL for learning how to develop databases as well.
What is SQL Server?
SQL Server is a product that Microsoft created. It is a MySQL competitor and it is broadly used by small and large companies around the world. Microsoft SQL Server is not an open source product. There are several editions of the software, though, and two of them are free.
Microsoft SQL Server Express is free to use for small databases even in commercial production settings. It works well for learning how develop SQL databases as well. You should know that there are limitations to the SQL Server Express edition, most notably the maximum database size that you can deploy on it.
SQL Server Developer Edition is also free to use for databases of any size. However, this version of SQL Server can only be used for non-production systems. In our Introduction to SQL Course, we use SQL Server Developer Edition because we are working in non-production and this edition of SQL Server does not come with any limitations.
When you are working with SQL Server, you will usually use SQL Server Management Studio as your IDE for interacting with databases. Our online course on learning SQL will walk you through the installation of both SQL Server Developer Edition and SQL Server Management Studio.
What is PostgreSQL?
PostgreSQL is another open source database platform. It’s similar to MySQL and SQL Server in that it is database server software. You would connect to it using an IDE, just as with the others. PostgreSQL provides a free IDE called pgAdmin.
The PostgreSQL community is very active. There has been a huge amount of growth in companies and applications that use PostgreSQL. This is because the software is easy to install, easy to learn, and extremely powerful.
What Are the Differences Between MySQL, PostgreSQL, and SQL Server?
There are actually a lot of differences between these three software platforms. The more in-depth you go with each one, the more will see the differences.
But the good news is that they are all based on the ANSI SQL Standard. What this really means is that the key functionality that you need to know will remain more or less the same between each one of these databases. Most of the time if you do run into a difference, it’s very easy to research and determine how to modify your SQL commands to work with another database platform.
It does become more challenging if you have built a large application using one database platform and then decide to move it to another one. The commands for creating more advanced functionality like stored procedures or user defined functions might differ. The built-in functions might also be different from one database platform to another.
You can easily learn the differences between these three database systems. It doesn’t take long to become proficient with all of them. Although it might take a good amount of time and effort to master just one of them. You should understand the fundamental SQL concepts and commands first. Then you can choose the database server that you want to master and spend most of your time learning that.