Here we go. I'm going to explain SQL in one sitting. I'm writing this without doing any research, just from my own experience, so do check the facts yourself, and I welcome feedback and will happily make edits. If it doesn't make much sense, well I'm injured and on painkillers today so whatever.
SQL stands for Structured Query Language. It is a long running standard that has been around for decades, long before the internet at least. It's used to query databases. Because it's a standard, there's a lot of overlapping things between different database providers that you can use the same SQL for.
Like Microsoft's SQL Server, Oracle's MySQL, The open source PostgreSQL, open source SQLite, all database providers. They have lots of different characteristics around deploying them and what they cost and who is responsible for them, but they all understand Structured Query Language, SQL.
SQL is a language primarily for querying Tables of data in databases. Tables are similar to spreadsheets in Excel. As a matter of fact, a lot of database implementations come in to play because an Excel spreadsheet got too big. I've seen it happen first-hand several times in my career. If you have a big spreadsheet and need help making it into a database and a web app, you can hire me.
Anyway, tables are based in Relational Algebra, which is a "math thing" that isn't particularly useful for applications, but if you're making your own database provider, you had better know it well. Data is stored in rows and columns on long-term storage like a hard-disk drive.
Data is (hopefully) normalized over several tables. So, let's make an example table "customer"
CREATE TABLE customer (name nvarchar(255), address nvarchar(255))
You can think of this table as an excel spreadsheet with two columns: one for name, and one for address. Well what if you need to associate more than one address with one customer? The not-so-great way is to make two rows of data in this table for the same customer. A better way is to "normalize" the data and make an Addresses table, and associate the two together. It looks like this when creating the tables:
CREATE TABLE customer (customerid uniqueidentifier, name nvarchar(255)) CREATE TABLE address (addressid uniqueidentifier, customerid uniqueidentifier, address nvarchar(255))
So now you have two tables:
address. Customers with one address will have one row in each table. Customers with no address will have only one row in the customer table, and customers with two or more addresses will have a row in the customer table and two or more rows in the address table.
How, might you ask, do you insert data into a sql table? Well, you do that with
INSERT statements. Let's insert a customer into our tables created above. We can use the
newid() SQL method to generate an id, or you can be cheeky and grab one from I Need A GUID!
INSERT INTO customer (newid(), 'app devs r us')
then what if we need to include an address to that customer?
INSERT INTO address(newid(), (SELECT top 1 customerid from customer where name = 'app devs r us'), '123 main street)
This will shove a row into the address table and the associated ids will match up. I had to include a select statement in there to make this example make sense. Bear with me.
Retrieving data from SQL databases is what comes up a lot and I feel like I've helped a lot of folks over the years get up and running with this. Allow me to continue with my run down.
This is the simplest SELECT statement there is.
SELECT * FROM customer
This retrieves all the data in the table
We did this in our INSERT example.
SELECT customerid FROM customer
This will return only the
customerid row. You could think of it like going into Excel and only copying column 'A' from a spreadsheet.
This is the real magic of SQL. JOIN. JOINs get crazy complicated but there's one JOIN that has been my bread and butter over the years. The LEFT JOIN. It ties together data so you can see it in one big view, even if it's across tables. Let's use JOIN to get some data from our customers and addresses.
SELECT * from customers LEFT JOIN addresses ON customers.customerid = addresses.customerid
This will dump both tables into one view. If a customer has multiple addresses, they'll show up in multiple rows with each address. If a customer has no address, they won't show up in this query at all.
SQL is very detailed and complicated but if you're just starting to pick it up, hopefully this helps you get started learning SQL.