Chapter 1: Introduction
Chapter 1: Introduction κ΄λ ¨
Structured Query Language, or SQL, is the primary programming language used to manage and interact with relational databases. SQL can perform various operations such as creating, updating, reading, and deleting records within a database.
What is a SQL Select Statement?
Let's write our own SQL statement from scratch. A SELECT statement is the most common operation in SQL β often called a "query". SELECT retrieves data from one or more tables. Standard SELECT statements do not alter the state of the database.
SELECT id from users;
How to select a single field
A SELECT statement begins with the keyword SELECT followed by the fields you want to retrieve.
SELECT id from users;
How to select multiple fields
If you want to select more than one field, you can specify multiple fields separated by commas like this:
SELECT id, name from users;
How to select all fields
If you want to select every field in a record, you can use the shorthand * syntax.
SELECT * from users;
After specifying fields, you need to indicate which table you want to pull the records from using the from statement followed by the name of the table.
We'll talk more about tables later, but for now, you can think about them like structs or objects. For example, the users table might have 3 fields:
idnamebalance
And finally, all statements end with a semi-colon ;.
Which Databases Use SQL?
SQL is just a query language. You typically use it to interact with a specific database technology. For example:
And others.
Although many different databases use the SQL language, most of them will have their own dialect. It's critical to understand that not all databases are created equal. Just because one SQL-compatible database does things a certain way, doesn't mean every SQL-compatible database will follow those exact same patterns.
We're using SQLite
In this course, we'll be using SQLite specifically. SQLite is great for embedded projects, web browsers, and toy projects. It's lightweight, but has limited functionality compared to the likes of PostgreSQL or MySQL β two of the more common production SQL technologies.
And I'll make sure to point out to you whenever some functionality we're working with is unique to SQLite.
NoSQL vs SQL
When talking about SQL databases, we also have to mention the elephant in the room: NoSQL.
To put it simply, a NoSQL database is a database that does not use SQL (Structured Query Language). Each NoSQL typically has its own way of writing and executing queries. For example, MongoDB uses MQL (MongoDB Query Language) and ElasticSearch simply has a JSON API.
While most relational databases are fairly similar, NoSQL databases tend to be fairly unique and are used for more niche purposes. Some of the main differences between a SQL and NoSQL database are:
- NoSQL databases are usually non-relational, SQL databases are usually relational (we'll talk more about what this means later).
- SQL databases usually have a defined schema, NoSQL databases usually have dynamic schema.
- SQL databases are table-based, NoSQL databases have a variety of different storage methods, such as document, key-value, graph, wide-column, and more.
Types of NoSQL databases
A few of the most popular NoSQL databases are:
Comparing SQL Databases
Let's dive deeper and talk about some of the popular SQL Databases and what makes them different from one another. Some of the most popular SQL Databases right now are:
Source: db-engines.com
While all of these Databases use SQL, each database defines specific rules, practices, and strategies that separate them from their competitors.
SQLite vs PostgreSQL
Personally, SQLite and PostgreSQL are my favorites from the list above. Postgres is a very powerful, open-source, production-ready SQL database. SQLite is a lightweight, embeddable, open-source database. I usually choose one of these technologies if I'm doing SQL work.
SQLite is a serverless database management system (DBMS) that has the ability to run within applications, whereas PostgreSQL uses a Client-Server model and requires a server to be installed and listening on a network, similar to an HTTP server.
See a full comparison here.
Again, in this course we will be working with SQLite, a lightweight and simple database. For most backend web servers, PostgreSQL is a more production-ready option, but SQLite is great for learning and for small systems.