MySQL: Everything You Need To Know To Get Started

Welcome to this beginners guide to SQL. If you are here, you probably have some experience with databases or programming. This guide is intended to give you a general overview of SQL.

In this guide, you’ll learn:

1. Whether learning SQL is worth learning in 2022
2. The basics of how SQL works
3. SQL syntax
4. Core Queries and Functions

The real aim here is to give you all the info so that you can make a decision about whether SQL programming is a skill you want to add to your arsenal!

What is SQL

SQL (Structured Query language) is a (perhaps the) database management programming language. Put simply, it’s a language that communicates with databases!

In practice, you can use SQL (“Sequel”) to create, pull, edit, delete or otherwise manipulate data you are storing.

How Does SQL Work

If you are already familiar with databases, then maybe skip this bit!

Ok, we are going to go a bit grass roots here, but I don’t want to assume that everyone reading already has a basic understanding of databases. If you are coming to this completely green, then what you’ll notice a lot of guide missing is a simple explanation of what it is that is actually being discussed!

Data Warehouses

A common way of thinking about databases is like warehouses filled with filing cabinets. Each warehouse is a database, each filing cabinet is a data ‘table’, and each file contains the actual data.

So the warehouse was built using SQL, right? Well, no, the warehouse and its contents (think SQL server, MS Access etc) was built using languages like C++ or Java.

What SQL is the language that the staff at the warehouse understand. If you can talk SQL you can retrieve, delete, update and add files to cabinets.

For example, you have a warehouse that stores files on every pet in your town. Each filing cabinet contains information on a different type of pet. If you want to find out the names of all the labradors in your town, you tell the staff:

https://www.thiscodeworks.com/embed/6299e8baa4b94f00157d0852

In the example, ‘name’ is the column and ‘dogs’ is the table. The ‘WHERE’ clause acts like a filter, only returning records where the ‘breed’ equals ‘labrador’.

Without knowing the language that the warehouse staff speak, you can’t make use of the data stored within.

But we are getting ahead of ourselves, before we get into the nuts and bolts of how SQL actually works, let’s look at whether it’s actually worth knowing in 2022!

Is SQL Relevant?

It’s a bit of a cop out, but it really does depend on who you are and what situation you are in. As a rule of thumb though, if you are going to be having anything to do with databases, at least a basic understanding of SQL is pretty much indispensable for a programmer. In 2021’s Stack Overflow’s developer survey, SQL ranked fourth in a list of the most popular programming languages.

Oracle (formerly known as Relational Software, Inc) released the first commercially available version of SQL all the way back in 1979. In 2022, SQL is still seen as the standard RDBMS (Relational Database Management System) programming language.

But why has the standard set in 1979 not been replaced by a flashy new language in 43 years?

Simplicity

The popularity and success of SQL is probably due to its simplicity. Compared to other languages, SQL is reasonably easy to learn, in fact, we cover the basics of SQL programming in this article!

It’s Everywhere!

Simplicity has led to ubiquity. Databases are a part of all sorts of software development and SQL and RDBMS is by far the most common means of managing data. No wonder then that having a knowledge of SQL is considered an essential skill for most developers.

Because it is the standard, there is a thriving community of programmers on hand to lend a hand to newbies!

Interoperability

SQL breeds interoperability. There are so many databases that are accessible through SQL, it means that there is a high level of interoperability between systems. Having said that, there are some differences between the syntax used by the big RDBMS systems. These variations are normally slight though.

Database Management Systems

The systems for managing managing systems are perhaps more responsible for perpetuating the popularity of SQL than the language itself. Tools like SQL Server and MS Access have fully realized the power of SQL, thus

SQL Just Works

SQL works and has worked for 4 decades. SQL solved the problem of database management- and has become synonymous with relational databases. If you think of “data”, you’re only one step away from SQL!

NoSQL Databases

The fact that the accepted term for non-relational, non-tabular database solutions has ‘SQL’ in it, speaks more convincingly of the importance and popularity of the language than anything else!

That’s not to say that NoSQL is inferior. In fact, companies with vast quantities of data opt to work with NoSQL, and for good reason. If you want to scale quickly, NoSQL can handle that with minimal costs. Of Course, we are talking about going from nothing to millions of records in a few weeks. SQL can handle millions of records, but this would involve a hardware investment that would be high cost and practically tricky.

Is SQL Worth Learning in 2022?

Yes. Of course. Even if you are hoping to land a data science job at Netflix (who use a NoSQL solution) it would be odd indeed if you didn’t also know SQL. The reality is that SQL is still massively popular. Even if non-relational databases become the standard for big data, SQL will almost certainly still be the best solution for a huge portion of databases for decades to come.

Start Working With SQL Databases

Convinced that SQL is worth your time? Great! Now it’s time to dive in! Before we actually looking at a few lines of code- let’s remind ourselves of the basics:

• Relational databases

A database is that groups information in tables

• Table

Data stored in rows and columns. The table in the example is called ‘Dogs.’

• Rows

A ‘Row’ is one record in a table. In the example, there are 8 rows.

• Columns

The different fields of data in the table. In the example, there are the following columns: ‘ID’, ‘Name’, ‘Breed’, ‘Color’

CRUD — Create, Read, Update, Update

Statements and Clauses — When learning the basics of SQL coding, you should be thinking about

A statement is an instruction to the RDBMS, it always ends in a semicolon. Below are some examples of some different statements.

Create Table

The above snippet is an example of a SQL statement.

The above statement will create the table also pictured above.

• ‘CREATE TABLE’ is a clause. Clauses are in-built instructions that act like standardized clumps of code. The ‘CREATE TABLE’ clause creates a new table (surprisingly!)
• ‘Dogs’ is the table name.
• Lines 2,3,4 & 5 are all parameters. In the statement, the parameters specify the column names and the data type.

Insert Row

The above statement will add row 1 from our example table.

The ‘INSERT INTO’ is a clause that inserts a new row into the table. ‘Dogs’ is the table, and the text in the brackets are the parameters. ‘VALUES’ is a clause that is used to identify the data itself.

Select

Select statements are a common way of fetching information from a database. In the snippet, there are two statements. The first, will return all data in the ‘name’ column:

The second, will return all data from the table.

So that’s how individual statements work. These can get complicated with larger databases, with 100s or even 1000s of lines of code. For now though, we’ll get more familiar with the individual components that make up a SQL statement.

Commands

Here, we will take a brief look at a few SQL keywords that are commonly used when querying databases.

• As

This will change the name of a column or table. In the below example,

• And

The and keyword is particularly useful for when you want to apply multiple conditions. In the snippet below, all columns and rows will be returned from the ‘owner’ table when the ‘age’ is greater than 49 and the gender is ‘male.’

#

• Or

Like and, the or keyword lets you add multiple conditions. In the snippet below, the table

• Distinct

When working with lots of information, it can be helpful to know what individual values exist in a given table. That’s what the ‘DISTINCT’ keyword does. So, if you wanted to know how many distinct breeds of dogs in a table, you could use the snippet below.

Learning a programming language works best if you get the basics, and experiment. For a more complete list of all available SQl keywords, check out this page.

Operators

The table sets out some of the common operators used in SQL. Bear in mind that not all operators will work with all data. Mathematical operators like ‘+’ will only work with numerical data, but some operators will work with both string and integer data.

Working With Multiple Tables

A single table database wouldn’t be much of a database! Afterall, you wouldn’t rent a warehouse to store one filing cabinet! Relational databases have tables that relate to each other.

In our pets example, let’s imagine there is a separate table that stores all the information about the owner, we’ll call this table ‘Owners’.

Check out the two tables below:

This is our original table, but ID has become ‘Pet_ID’ and we now have an ‘Owner_ID’ column.

Visually, you can see how to join the information in these two tables. If you want to now who owns ‘Rex,’ you can look up the ‘Owner_ID’ and see that ‘Gemma’ owns Rex.

But how does this work from a SQL point of view?

That’s where ‘JOINS’ come in. ‘JOINS’ are instructions that tell the database how to join and display tables of data.

Let’s go through this.

• This statement will return a combined table that will return all the columns and rows that match the conditions of the ‘JOIN.’
• So, we are selecting all the columns (as indicated by the ‘*’ symbol) from our combined table.
• The ‘JOIN’ clause says what table to combine the ‘Dogs’ table with.
• Line 4 says what column to join with what column.
• So this statement will link the two tables based on the pet_id.

That’s the basic concept of joining in SQL databases. The type of join will dictate what records are returned from which tables.

Here are the different types of the JOINs in SQL:

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

Of course, you can then create subsequent joins to other tables. In our examples, imagine that there is another table called ‘cats’ and you want to find out what dogs live with cats. You could link the ‘dogs’ table to ‘owners’ and then link to the ‘cats’ table.

Summary

That’s a bit of a crash course in SQL and you will undoubtedly need a lot of practice and additional research before you are fully proficient. However, as we have already discussed, the true success of SQL is its simplicity, and the topics covered in this guide truly are the main building blocks of working with relational databases professionally, albeit in a simple form. If you have understood the concepts, then you can build upon this knowledge. You can get to a professional standard, it just takes hard work and a love of data!