0:08
So now we're going to get to the heart of the matter,
we're going to actually write some SQL and we're going to make a database.
Okay, you hopefully by now,
I've told you a couple of times, installed the SQLite browser. If you haven't, do it.
You could also write a Firefox plug-in
that can be used to manipulate SQLite databases.
I used this before SQLite browser came out with their newest version.
So, everything I'm going to do could be used by either one.
And there might even be a way to do it with an SQLite
admin tool if you downloaded like a MAMP or an XAMPP.
So it doesn't matter.
You just need some piece of software that gives you a place to type SQL,
You hit a go button, and it sends it to the database and comes back.
That's all you need. Okay.
This is what it looks like, SQLite, SQLite browser.
So let me go ahead and start this thing up.
1:37
That'll just be the name of the database.
Now, it's writing to a file.
This file is not something you should read using anything other than a SQLite library.
It's a funky looking file. So we're going to save it, boop.
And it's asking us if we want to create a table.
I'll just hide that thing.
So we have no tables, no indexes, no views, no triggers, no nothing.
And we have a place that we can execute SQL.
So when I said you're a database administrator,
we got a file back there called sql1 that everything we do is actually reading and
writing to that file, but we don't write code to touch that file directly.
We always are using the SQLite library, okay?
So we're up, let's create a table.
Now this is our first SQL.
And so CREATE TABLE and then the name of the table, Users.
I'm just going to use upper case.
Upper lower case generally matters.
There are a few weird situations where it doesn't matter, so
just pretend that case matters in this situation.
And it teaches you something right away about SQL.
SQL is designed to be somewhat conversational.
And so the keyword, CREATE TABLE,
is kind of like one keyword, we're creating a table.
Then the name of the thing and then a parenthesized list of the fields and
then the types of the field, that's what we're dealing with.
So this is CREATE TABLE named Users, and we're going to put two columns into it,
and one is of variable length character, up to 128 characters,
an email that's also a variable length character up to 128 characters.
So, this is sort of our schema, this is our contract.
We're saying these are the only two things we're putting in this table.
We can't put a thing called age in the table,
we can't put a thing called favorite beverage in the table and
we can't put 129 characters in the name column.
We can put 0 or 1 or 2 or 127 or 128,
that's just fine, but we can't put 129.
This is a contract.
And the database, if we want to put longer characters in, we just say no, no, no, no.
We want to put 1024 in and then the contract will be we could put up to 1024 and
if we put 1025, the database would be mad. And like why do you do this?
Why is it, why is the database so obsessed about this?
And the answer is, as it lays the data out on this disk drive and
it wants to get to it fast,
it really is helpful to know the size, the maximum size.
Because if you say this is only going to be four, versus four megabytes,
it might lay the table out differently on the disk.
You don't need to know that.
You just need to be explicit about the contract. Okay?
So this is part of making the data model.
Figuring out in advance the kind of data that you're going to use,
how you're going to use that data, and then you communicate that to SQL.
And then you don't worry about all the magic that makes that super fast.
So this is the contract that you make so
that your data is really fast. By being very explicit about your data,
7:14
And now we're going to do some SQL to
We've done the create and now we're going to do some inserting and deleting and
retrieving of the data.
So the first thing we're going to do is insert another record.
Now, what we were just doing in that user interface was exactly this.
Here is another example of some SQL where INSERT INTO is
kind of like a single keyword.
The name of the table, a parenthesized list of the columns
and then the word VALUES, that's just part of SQL, and
then a parenthesized list of the corresponding values.
So Kristin is the name and kf@umich.edu is the email address.
And so that is a SQL statement that inserts a new record into our Users table.
7:56
So we'll just cheat and grab this,
copy it, then go to the SQLite browser and we'll go to Execute SQL.
And I will get rid of the CREATE TABLE because we already did that, and
just type in insert users with that parentheses.
It's doing syntax coloring for me which makes it very, very helpful.
And then I got the right SQL.
Now let's just say I make a mistake.
Let's just say I call this ZAP instead of VALUES.
I will run this and it will go like zap is a syntax error and so
it does talk to you and then you can go fix that and say VALUES.
9:13
It's not necessary, but it lets us read it a little. DELETE FROM Users.
Well that sounds like words.
Sounds nice.
DELETE FROM Users, the name of the table,
and then a WHERE clause.
And you can put WHERE clauses on lots of different SQL statements.
And that says, don't delete all the rows, only delete where this is true.
And in this case we put a logical question.
So this is like a question mark in a if statement, right?
DELETE FROM Users WHERE email equals ted@umich.edu.
This could be one row or
there could be a hundred rows that all have that email address.
12:21
And star means all columns.
FROM is a keyword and then Users is the name of the database.
And you can have either with or without a WHERE clause.
And so this says select.
That really says all rows all columns from the database named Users.
SELECT * FROM Users. Okay?
And this says this will turn out to be one row in our little database,
but it might be more than one if this was true.
If this WHERE clause was true for more than one, okay?
So, let's run those commands.
Now, it turns out that we're kind of implicitly running those commands,
the SELECT * FROM Users, every time I hit that browse button.
Whoops, don't do that.
So if I go over here and when I do browse,
I couldn actually see that in SQL log, the last thing was that. Okay?
13:27
Hit that, and now down here we see that and
we also see in this log as the log goes by, that it sent that stuff.
Now it´s also sending more stuff.
We´ll get rid of the log, it´s too complex.
But it´s really just SQL.
I´m giving you the simple stuff at the beginning, okay? So that was a select and
I can add a WHERE clause, WHERE email equals csev@umich.edu.
Let's see if I typed that right.
And that should show us all the rows, but only show the ones for
which that's true.
So that's a WHERE clause, And a SELECT.
You can also throw another clause on the end of a SELECT.
You can have an ORDER BY.
So in this case I'm going to SELECT * FROM Users, which is exactly what I did before.
Select all the columns from the table Users,
ORDER BY, again a two-word keyword that rolls off the tongue more naturally.
email, which is a column.
Sorting is one of the things that databases do really, really well.
And that's because it actually sometimes for sorting it has these things. And it has
these little tricks that say oh that one goes here and this one's the next one.
And so if you really want it in sorted order,
have a really quick way to figure that stuff out.
But that magic, you don't worry about.
That's the database's problem.
So you just say hey, smart database from billion-dollar company,
I would like to order this by email.
15:42
It's like so easy [LAUGH].
It is easy.
That's why I don't teach you SQL until later, so
you learn the hard stuff, so you're tough.
Now I can show you the easy stuff.
Now, of course there's some complexity to come.
But at the end of the day when we're talking about a single table and
the statements that are one table,
it looks like a really big fast smart spreadsheet.
And that's why some of you may recognize what you're learning here.
You may say, you know what, I've actually been doing this!
And I didn't realize how smart I was!
I didn't realize I was doing databases.