Getting Started with SQLite

[00:00:00]
>> Brian Holt: We are gonna go ahead and get this running. You're gonna notice that everything today is SQLite 3. As you might guess, it's the third major version of SQLite. That's where the 3 comes from. Feel free to hop over to I'm gonna be using probably 346 today for my version.

[00:00:20]
But SQLite is so slow to change. Unless it says SQLite 4, your version's probably gonna work for this because I'm not gonna show you anything too crazy here today. So if you want to, feel free to pop over here. I linked you to the download page. You can see that 346 is still the correct version.

[00:00:39]
Looks like they've, that have updated it, I think to point one which I don't think I'm on, I think I'm on point, doesn't matter. I did it through brew install SQLite, that worked as well. And then I just aliased it, because all I did was like, what is it?

[00:00:57]
Homebrew? Info, SQLite 3. Or brew, sorry,
>> Brian Holt: It tells you where it is, and then I just went ahead, and then I made a alias for it. So you can see here, this is just an alias that Brian Holt is using for his class. But you can see I'm using 346 here.

[00:01:23]
If I just do SQLite 3, I'll get this up a little bit. You can see this is 340 3.2 from 2023, you're gonna be fine with both. There's no reason that you need to be on you know, 346 that's just gonna be totally up to you. If you wanna an alternative way, I think this is the OS X version, if you click on that link there.

[00:01:53]
But yeah, I would just get it directly from their website, if you care. Otherwise, just stick with whatever's installed on your OS by default.
>> Brian Holt: They should have instructions for Windows and Linux on there as well, but it works everywhere. You could run this class totally on a Raspberry Pi, no problems, right?

[00:02:12]
Or an Arduino, probably, that would be a lot more difficult for no little gain, but go for it. Okay. And then we're gonna be using something called Chinook. So click here, it's just a data set. I have you linked to 1.4.5. I don't know how frequently they update this, but this is just a guy that basically put together a free data set.

[00:02:36]
You'll find it everywhere, everyone uses it for all their examples. It's just consistent. It's basically an invoicing system for buying digital media. So there's like stuff in there about Led Zeppelin and AC/DC, and Lost the tv show, and a bunch of stuff like that. Go down here and click on
>> Brian Holt: This one here, Chinook, SQLite.SQLite.

[00:03:02]
I would download that, and then I call my databases .db. I don't know why they went with .SQLite. I don't really see too many people do that. I'm not doing the auto increment one. We're just doing the SQLite one. You can also download the .sql one, which is going to be a really big SQL file, and you can load that into SQLite.

[00:03:22]
Both of those will be just fine. But you can see they have it for Postgres, they have it for DB2, a bunch of stuff like that. That should get you all set up, and we're gonna hop into actually getting something going.
>> Brian Holt: So,
>> Brian Holt: I have a fresh copy of it here that the Chinook database.

[00:03:52]
And you can just do SQLite3, and the name of the database, which I have called data.db, but yours, might be called, like Chinook, something or other. You can also do it with just the SQLite extension. Doesn't matter, and that should drop you into something that looks like this.

[00:04:11]
Let's first talk about dot commands. They're just really helpful to know up front. So if I do dot help, it'll just give you a bunch of like a dot commands that you can run, they're kind of like administrative ones so that you can figure out what the name of the tables are, right?

[00:04:28]
Dot tables, right? You can do it like what is the name of the schema of album, right? Tab completion does work, at least on my computer, so that which is pretty nice as well. Version, there's a bunch of stuff that you can do with those kind of administrative commands there.

[00:04:49]
We won't do it today, but you can do like .connect and you can connect another database. You can have multiple databases connected at the same time and you can be reading and writing to two different files. Bunch of really cool stuff like that. And then, as always, you can hit .exit to get out of it.

[00:05:07]
Another thing you can do is you can just do, I can do Control D, and that will also get me out of there as well.
>> Brian Holt: So those are dot commands. They're all super helpful. I use dot help all the time, because I can't keep all this stuff in my head.

[00:05:25]
Dot exit is useful. If you run SQLite3 and just give it nothing, you can see here it's doing transit in memory, which means, like, if you want to just like, open a session to like, no database, if I put dot tables, there's nothing, right? This can be helpful to kind of just be a playground to try stuff out as well.

[00:05:45]
And you can also do dot read. And do what is it I called the data dot DB.
>> Brian Holt: Maybe that won't work. Yeah because that's a database. It's not an SQL, but if it was an SQL file, which I did not download, but if you download the other dot SQL one, you can actually read it in there, and they can start doing stuff without actually modifying any sort of database or leaving anything at the end.

[00:06:12]
Because when you just do exit here, anything that was in this transit in memory database is now deleted.