 Welcome to the tutorial of my films by Chris. I'm Chris with a cave films like chris.com link in the description and today We are going to be playing around with SQLite 3 So I don't claim to be a database as expert But I do use them to store data and I thought I'd show you a little bit of SQLite 3 Specifically version 3 For most my web stuff I use my SQL and but SQLite is great For applications you might have it's very commonly used it You know if you have a smartphone and you have you're depending on what application you use But a lot of patients will use this type of database though of your contacts possibly or your text messages So it's really neat to to just be able to understand it and for the most part the commands themselves are very or the same or very similar to SQL or my SQL and I'm pretty sure you know SQL is created by Microsoft and there's my SQL and SQLite and a lot of people pronounce them SQL SQLite or Or my SQL but according to the creators of the programs I'm pretty sure that they're not SQL but SQL the only one that's SQL as far as I know is the Microsoft version Anyway, just want to get out of it. If I actually say SQLite. I apologize Just point that out because someone's gonna complain if I don't anyway, let's go ahead and get started I'm in an empty directory right now, and I have SQLite installed if you are on a Debian based system You can use apt apt to apt get or wherever package manager use on whatever destroy you're on. I'm gonna use apt to search real quick I'm gonna search for SQL oops SQLite And you'll see you get a bunch packages because there's different packages for different languages We're gonna work with the SQLite Program itself in the command line and as you can see I have two here listed I have SQLite, which if you look at the description says that it's version two But I'm going to be working with SQLite three here specifically and there are some differences So make sure if you're going to be working with a database that already exists figure out which one it is As you can see I already have this installed if you don't have installed use your package manager install for example You can do pseudo apt install SQLite three and go ahead and install I already have it installed So let's go ahead and get working with it once it's installed All you have to do is type in the name of the command and then give the name of database if database Doesn't already exist it will create so we'll just call make create one called example dot DB And now we have created you can see we're at the SQLite Prompt here and you can do help for help, but I'm going to show you some basic commands here So first thing we're going to do is create a table So if you have a database and that base you have tables and these tables are where you store your information You can have multiple tables within one database and you can kind of think of that Tables like spreadsheets. They aren't really, but they can be displayed like that But just think of it as like rows and columns is the easiest way to think about it and here I'm going to take this in commands and it's usually common for the actual commands to be all capitalized and then the information you're entering to be lowercase Doesn't have to be that way, but I'm gonna try to do it that way just to keep it What's the word I'm looking for just to do it the same way all time and make it easier to read So I'm gonna type and create I'm gonna take it all capital, but again, it doesn't have to be all capital I'm gonna create a table. What am I gonna call this table? I will call this table table one and then here I'm gonna say parentheses and What I'm gonna and then you want to end every command with a semi-colon if you don't you'll go down to the next line You realize what what did I do wrong? You just put a semi-colon hit enter. We'll give an example of that later I'm gonna create this table with well, I'm gonna create two columns But really it's gonna create three so I'm gonna say the first comes gonna call F name and I was gonna make it a text You can do different types of input. I'm not gonna get into that much detail in this text is Obviously, there's different types like characters and dates and numbers types of formats for these um these entry points But I'm just gonna do text just to keep things simple But if you're really gonna get into database stuff, you really want to learn more about the different formats. Anyway, I'm gonna say F name text comma L name this this is the name of my columns So I'm gonna have a column for names the first name and the last name I can call this first name last time I just chose to call them L name and F name just keep things short and again text is the type of text It is you can also put in here Not null meaning that you have to have an entry in there You'll return an error if you leave that blank and that's up to you depending on what's entering if we're creating a database of names a table of names of people you might have their first name their last name their Middle name their phone number their address, but maybe someone doesn't have a middle name So you don't want to leave that null, but maybe you always want a first and last name You can say not null So I'll go ahead and put that in there for both of these and again Capitalizing this because their actual commands of SQLite as I hit enter and we have created that table no no output means everything was successful now let's go ahead and Insert something into that table So I'm gonna say insert Into and again name our table is table one and then here I'm going to insert these values Oops, let's go ahead and put a space here and spell things properly and here I should be able to Put in a name. I'll put in the first name like John and Then separate by comma and I'm using quotations here You can use single or double quotes and I'm just name him John Smith And if everything goes right we have now insert that names the table. Let's go ahead and quickly insert a name I'm gonna hit up arrow to bring up that last command and I'll just put in someone named Sam Smith and Then I'm gonna put in someone named Rick Smith son and Then we'll also put a name. We'll say Rick Johnson and Let's just put one more name in there We'll say I'm just trying to give a name Tim and we'll say Give this guy a name of Peterson There we go. So now we have another names in there. How do we view what's in the database? Well, we can say now select and what do we want to select right now? I'm just gonna do an asterisk meaning select all the information from the database or from the table We're gonna say from table one And I'm gonna go ahead and hit enter and as you can see I forgot to put a semicolon Which I forget to do quite often And what I'll just do here is it's semicolon that means that's the end of that command then I'll hit enter and there you go it displayed all the names in the database and All the information from the database We can also now do a query and grab certain rows if they equal something so I Can say Here I'll say select all from table one and then I can say where and I can say F name and I can say is Rick, okay, don't forget the semicolon there So we're selecting we want to display all the information from the table one where the first name equals Rick No such column Rick. Okay, I think I have to put this in quotations. There we go So now we have we have Rick Smithson and Rick Johnson, okay Now let's go in a little bit further into this and let's say we want to do partial matches What I can do is or and also we can do cases. So if I do a lowercase r here You'll see it won't return anything But if I do instead is I can say like so it's like Rick now it's going case and sensitive So let's go here and say instead of F name. Let's do L name for last name And I'm going to say Smith so now I'm going to find all the information from table one where the last name is like Smith I do that and we get Smith and Smith, but we don't get Smith's son So what we can do here is I can come in here and say a percent sign on there And now it's going to autocomplete the rest of that and I'm going to try something right here real quick I'm not sure if this is going to work. Okay, that doesn't work. So it's got to be the percent sign So there we go. We got everyone whose last name is Smith or Smith's son We can also go the other way and we can say any name that is like and maybe ends in Sun So we got Rick Smith's son Rick Johnson and Tim Peterson and of course you can put The percent sign on both sides of those I don't have any names that really are like that where I can put let's just say select everything from table one again and Yeah, not really So but you can put if I want to percent percent like this And it will match everything that has Sun in the middle there again case and set for sensitive And that's because we're saying like Okay, so we've searched for exact matches and we've searched for partial matches case and sensitive But let's say you have two people with the same name and you want to pick a particular one well By default if you don't set a primary key column Which we can talk about in a moment It automatically creates one and it's not showing it by default But what I can do here is I can tell it specific columns. So let's go here Let's go back here and say okay We want to select everything from table one and we can see that we've now we're now looking at everything But if I want I can say instead of everything I can say F name and now it's just playing first names Or I can say L name and it's only displaying the last name What I can also do is put in here row ID and now each column has its own number That SQL light has automatically added and increments each time you add an entry and this comes in handy When you're trying to make sure you're working with the exact right Entry again, you don't want to delete if you're gonna delete or update a column Which we're gonna talk about in a moment You want to make sure you're doing the exact one where this column here this row ID is what you're gonna really want to Work with at that point So real quick. Let's look at deleting a row from from there. So let's say I wanted to remove Here let's go ahead and just list everything out again So we have John Smith's Sam Smith Rick Smith's son Rick Johnson and Tim Peterson and let's say I wanted to Delete Sam Smith son or I'm sorry Sam Smith Now I can say delete any match where it's Sam and Smith What if there's two Sam Smiths? Well, what I can do here again is I can say let's grab everything from this table Let's clear the screen Where the last name is like Smith and Instead of showing everything let's go ahead and just say row ID and actually we want to do something like Row ID, and I think it's just a comma here and we can say L name and I can say F name and again it's displaying it here in the order I put it so even though in the database it goes Row ID last name or first name last name. I can display it last name first name or whatever parts of that I want so now I can quickly see okay. He has an ID of two. I can now say I want to delete From table one Where The ID are sorry row ID equals and I can say to and I know for sure. It's only going to leave because that row ID is set to be I Don't want to say individual. What's the word? I'm looking for unique unique note to the database won't let to have the same ID So now that I do that I can do that Success because there was no error output and now we can say let's go ahead and look at everything in table one again And you can see that that gentleman Sam Smith is now removed from the database And again if we do it like this you can see that he's gone And let's go ahead and just say everything from that table You can see everything and you can see that we have one and two is gone We can I guess directly put in another two But really if I if we enter a new one, so let's say we want to add something else We'll just add a new person here. I'll say Insert Into So again, that doesn't have to be capital. It's just considered proper as far as making it easier to read table one and we will say Values and We will say Peter Thompson, how about that? Semi-column there and now if we list them all out you can see Let's do all of them It's continued on doesn't go back and fill in number two. It just looks at the last one and continues from there Okay, so we've deleted people. We've added people here's another thing. Let's let's add a new column So we already have a database with a table. We want to add something into that table a new column We want to have a phone number in there so that we have now have contact information So what we're gonna do is we're gonna alter the table. So we'll say alter Table whoops and we're gonna alter table one and again, you can have more than one table add and we're gonna add a column Yeah, yeah, we go column and we're gonna add a phone column and again There's different types. I'm just gonna say text here for The type of column it is but really just probably better options for that We're gonna go and do that and now there's a column now if I go through here and I list All this out again. It's just let's just say everything Show the row ID and you can see there's another pipe symbol there as far as the output So they all have a column for phone number, but they don't necessarily have a phone number Entered in there. So let's go ahead and update somebody's phone number So again, let's do this. Let's let's go here So now we can see let's say we want to update Tim Peterson's phone number. I am going to now say update Table one and we're going to set the phone Which is what we named that column to 555 dash 555 dash 1 2 3 4 And we're gonna say we're gonna do that where the row ID Equals and we said we'll do that for Tim Peterson. We'll say five. So we're making sure we're only editing updating that one person So we can go ahead and do that I forgot the semi-colon put the semi-colon hit enter and now if we show these all again, but we say Here we can say phone as well There we go, you can see that Tim Peterson now has a phone number Now if I didn't put the ID there I think if I just said update this I think this will work. I haven't tried this yet update table set phone to that So a timing might want to do this. Let's see if it works. Yeah You can see now everybody has that phone number And that would have overwritten if I gave it a different phone number It would have overwritten to Peterson So again if I if I come in here, and I'll say 555 and display that out everyone has the phone number 555 That could be useful if you create a new column. Let's say and let's say you're creating a database and everyone's going to have a Avatar and little image of themselves, but you're creating this database And you don't have the avatars for everybody yet, but you have a default avatar You could theoretically put in a value there whether it's the you know information for the avatar or the location The avatar or even just some sort of text saying that it hasn't been filled in yet Because obviously when you're writing your code to display the avatar you can say if empty display this But let's say you wanted to put in an avatar for everybody that links to something in particular You could do that database wouldn't be the way I would do it But that's just a random example that you could do. So we've done all that Yeah, so let me exit out of this actually it's dot quit to get out and don't forget your semi-colon there I thought it was dot quit With this program. Oh, I guess without the semi-colon the one time. I remember the semi-colon. I did not need it Okay, and as you can see there's lots of information in there on Commands, it's right there and they'll help for you But let's list this out so you can see the database we created and if I do file on it It will tell us that SQLite 3.x database Last written using this version of if I said sequel I meant SQLite Database so yeah again a lot of cases use probably on your phone There's you probably have a number of databases like this if you can access them again You're your contacts possibly your text messages You can pull it out and look at all your information all your your conversations and whatnot back them up and access them on your desktop Like this, but you have to make sure that you're using the right version So writing the file command again on the database will tell you what type of database it is whether it's version 2 or 3 So that's one way there could be other databases formats that it's using out there. So That is our first look at Working with these databases and from there we were working within the application the whole time But let's say you want to write a shell script that does all this Well, you know 99% of how to do it already because the commands are almost the same But we'll look at that in the next video. So I do. Thank you for watching. Please is it films by chris.com? That's chris the K. There's a link in the description check out there You can search through all my videos from both my channels and if you think want to support There's links to that in the description of the video as well as well as on my website I thank you for watching as always. I hope that you have a great day