SQLite beginner crash course in Visual Studio Code

hey everyone how's it going so all you're going to need for this video is visual studio code thanks to the beauty of open source code there are some extensions we can get that will let us both see and use sqlite write in visual studio code so if you don't already have visual studio code be sure to install it before you watch the rest of this video if you want to follow along i'm going to split this up into two parts the first part is going to be a slideshow it's just going to be explaining what sql and sqlite is if that doesn't sound interesting to you or you already have a good basic understanding of sql feel free to check the description for the time stamps and skip right to when we start actually working with it in visual studio code all right so of course the first thing here is going to be what's a database for any of you who don't know a database is simply just a place where you can store data so think about programs that we've made so far like the random password generator let's say you wanted to store each password you made for that you would need a database a common thing to do is use something called sql which stands for structured query language this is essentially how you can communicate with a database this is how you put information into a database how you take it out and how you just read what's in it so for this we're going to be using something called sqlite this is essentially a lighter version of sql actual sql or mysql databases use what's known as a server this is serverless so we can just do it out of a file it still uses the basic ideas of sql but it's a lot easier to work with and it's better for if you're just making a little project yourself now let's see what a database would look like here's a simple example of a database this database here is called my database it has two tables inside of it one is called a users table the other is called users videos so imagine this you have a website which allows you to create an account log in and then upload videos does that sound familiar because that's what you're watching this on you'll see here each table can have whatever you want in it these are all things that i would have set up so we have one column for username one column for password and one column for email now this table can have as many rows as we want right these are the columns which we set and then we just insert rows of data so for each new user they're going to have a username a password and an email i already filled one out to give you an idea of what it would look like so the username here is my username the password here is password um it doesn't have to be the same as that i should have named it my password sorry if that's confusing and the email here is mike gmail.com now in the users videos you'll see the username here is the same as the username there which we'll get into in a little bit there's also a video title which here is something important and a video link which is just a link so let's say my username that user wanted to make another video what we would be doing is we'd put another video with the same username probably a different title and definitely a different link inside of this table but we wouldn't be adding any more columns just rows and each row would contain all three of these values this way if we wanted to do what's called querying a table which is just searching through the table let's say we wanted to get all of the videos by somebody with this username similar to if you click on my channel it'll show you all of my videos we can do that and i'll show you how to do that in a bit by using the select statement but we're going to get into that once we get into the code right now i just want to let you know that that's a possible thing and that's why these tables exist and are laid out the way they are additionally of course you could add another user in the same way you'd add another video right it would have a username a password and an email now we're going to talk about sqlite data types now there's five data types we've already covered a few of these right a null variable is not empty it is null it's slightly different an integer which is just a number that doesn't have a decimal text is really just a string so think of it like a string in php a real is really just a float so think of it just like a float in php and a blob is a bit interesting a blob can be thought of as just whatever's thrown into that column this could be something unique perhaps an image or a document it doesn't necessarily have to follow the rules that these other ones have to follow and that's something else i should mention they all follow rules you couldn't have you know an integer that's a string you couldn't have a string that's an integer it's a little bit more strict than php and of course you couldn't upload an image and say that it's a number or null next up we're going to talk about primary and foreign keys so a primary key can be thought of as a unique key this is something that will be unique in the table so i'm going to flip to the next slide for a second you'll see here username has a star right username here could be a primary key and the reason for that is because username would be unique right how many times have you tried to sign up for a website and they've said i'm sorry this username is already taken that's why they say it it's because it's a primary key all usernames have to be unique and this is important because since it's unique we can use it in the user's videos as well imagine trying to get my youtube channels videos if it didn't have a primary key it would be impossible right because you'd be pulling in so many different videos that aren't mine i gave it a star to show that it's a primary key so hopefully that helps you understand it a little bit better i should also mention that all sqlite tables can only have one primary key they can't have any more than that now next up we're going to go back and we're going to talk about foreign keys foreign keys are a unique attribute that you can give a column similar to primary keys but they're slightly different foreign keys are used to link one table to another so here with this star example what we could say is this is the primary key but it's also the foreign key right because you have a username here and a username here so they're linking the two tables now you don't need it to be a primary key for it to be a foreign key but in a lot of cases it can be helpful it's what's known as a one-to-one relationship now i'm not going to get too into relationships and all the various database schemas because i want this to be a simple crash course but if you're interested you could totally look into it and get more in-depth with sql and with that that's the end of our slide this is just a very basic understanding of sql that we're going to be using for programming sqlite hopefully this all made sense if it didn't let me know all right so now we're moving on to the setup what you're going to want to do is open up visual studio code and make sure it is visual studio code the text editor does matter for this one and then you're going to want to open a new folder i just named mine sqlite and i opened it now inside the extensions you're going to type sqlite sql ite just like that and you're going to want to download this one probably the first one it's by alex cvz once that's downloaded make a new file let's just call it test.db this is going to be our database the next thing we can do is make a new file let's call it sql dot sql now inside here what we're going to be doing is typing our sql that we're going to run so let's say ctrl shift p all at the same time hit those keys if you're on a mac it's going to be command and then type sq alright you're going to want to pick the one that says run query and you should see this open up now what we can do is type our sql and then we can run it and it'll go right to the database if it asks you to select a database which probably will just select test.db or whatever you named your database but do make sure that it has that db extension now the first thing we're going to do is create a table i'm going to be creating the same two tables that we saw previously so i'm going to say create table this is going to be called users and then what we're going to want to do here is in these parentheses pass it all of the columns we have a username column password and email with the semicolon at the end okay i'm going to close this and then what we want to do is make sure that they're all not null you can do that by saying not null and then i'm going to copy that and i'm going to put the same thing after each one of these you space it out a little bit after not know let's also say that we want it to be of type text for the username and we want that to be the primary key once this is all set you can hit ctrl shift and p at the same time and run the query error in your text sorry text has to go before not no and there we go we have our table created you'll see that if i try to run it again it'll give me an error that the table already exists that's perfect what we're going to do now is comment it out now you could also just delete it but it'd probably be better for you to comment it so that you'll know how to do this again in the future so something to reference now let's do an insert statement we're going to do this in order to put some data into the table what we can say here is insert into users now let's get all the columns so we have username password and email now we're going to give it some values first one's going to be mike the second one is going to be my password and the third one is going to be an email so let's say mike yahoo.com now something that i should note is that for all three of these they're strings right so what do strings get they get quotes so let's put our quotes around it and let me just explain a little bit as to how this works it goes in chronological order so it's going to say we're going to insert into user names the first value because it's the first column that we list password second column email third column so let's run this and now it might look like nothing changed but something you can do here is you can say open database i'm going to pick that database and then i can open this sqlite explorer and you'll see inside of this users table down here sorry if it's a little small but you'll see that we actually do have one user right and there they are it's me okay i'm gonna close this out though and i'm gonna show you a more traditional way to do this comment out this value and in visual studio code if you highlight something and you hold control and you press the quote button and the question mark button at the same time it'll comment an uncommented out which is super handy when you have a lot of lines what i'm going to say here is select star from also something else i should mention is that things that i cap make capital like i can make this capital or i cannot it doesn't matter it's just good practice to make words like this capital so you know what is a keyword and what isn't and it's easier to read but you really don't have to do it if you don't want to so let's say select all from users and then again we're going to run it and you'll see it does the exact same thing we just saw because what we're doing here is we're saying give me all of the users so let's comment this out uncomment the insert and i'm going to change this let's change this to joe joe's password make the email joe yahoo.com run it comment it out and then we'll do another select statement and you'll see now we get mike and joe because it's giving us everything select is basically how we read from it in php we're going to be using this and in any programming language you would do something like this and then you would set a variable equal to that right so maybe if i wanted to get all the usernames that could make an array of all the usernames using the array push function or something like that now let's say we don't want to select all what if we only want to select the username there we go now we just have the username but we're still getting all of the usernames well there's something that we can do here where it's called a where clause and we can say where this is very similar to saying if in a programming language we can say where let's say password but you know what let's say select password from users where username equals joe and joe is a string so let's not forget our quotes and run it and you'll see all we got here is joe's password we didn't get my password if we delete the where we get both the passwords so it's basically saying give me all the passwords from the users but only where joe where the username equals joe now you've got to remember the only primary key here is the username so it's only going to give us one password but what if we went by email right right now email isn't a primary key so we could have six users with the same email and if we were to say where email equals you know yahoo.com we might get six passwords and that's really the beauty of this this is how you would limit your database query because you know you could have a two terabyte database full of an obscene amount of data or even a small database could still be full of an obscene amount of users i mean just imagine having to look at a list of three thousand users when all you need is the one who's got the username joe or maybe the first name joe and one other thing that's interesting i want to show you is what happens when we try to copy a primary key so let's run this again let's make it jack leave the password and the email the same it gives us no problems now let's run it again jack's told the username we get an error and the reason we get this error is because it's a primary key and like i said before the primary key has to be unique in the table you don't necessarily need one but more times than not you're going to want one and that's what makes sql so powerful one thing i'm going to talk about next is the in operator so here we can say where username and instead of equals let's say in and in parentheses let's say joe comma mike actually i think i made mike lower case and let's run that and awesome you'll see we get both joe's password and my password which was mike's but we don't get jacks right now in is basically an or statement so you can think of it like this everyone that has this username is going to have is going to be considered valid right and we're going to get their password in this case of course if we change this something like email we'll get their email there's a few other things we could do we could say between which would be great if we were doing numbers right let's say we're doing numbers and we wanted to get everything between maybe 1 and 10 and instead of username we could say let's get the email or everyone who has emails sent that's between 1 and 10. that's not a column in our database we aren't tracking emails but imagine if we were that's what we could do we aren't tracking any integers here so there's no point in using between i just wanted to show you that it exists one other thing that i'd like to show you is like so what we can say is where username like and what we can do here is we can put some kind of a symbol and i'm going to pull this up really quick and not actually show you in code because i think it'll be more useful this is w3schools it's a great resource for learning all kinds of programming this is the regular sql they don't have an sqlite version but the like operator works the same in sqlite now you'll see here they're trying to find any value museum in a little bit they're trying to find any value that starts with an a and in order to do that they're putting a percent after the a and it's a quote right so if we wanted anything that starts with an m what we could say is like m percent and ideally we'd get mike's email let's run it and see perfect we did now let's change this j we got two emails right because we have jack and joe i really should have given jack a jack email to make it more obvious i'm sorry about that but there's other things we could do too right if we put the percent sign at the beginning you could find any value that ends with an a you could do double percent which finds any value that has whatever you put in between in any position so maybe i'd put mike and that way if it was a list of full names i'd get all the people who have the first name mike but it would also give me somebody who might have the last name mike they're somebody who has two first names we could put an underscore at the beginning and a percent sign at the end which is gonna find it if it's in the second position the same thing but with the underscore after the first letter which is going to find it if it starts with that character and is at least two characters in length then we can do the same thing but with two of them which will find it if it's three characters in length and lastly we could do percent and then something else which is going to find something that starts the one letter and ends with another and that's the last one i'm going to test here so let's say m percent and let's just throw it off let's say i we don't have any usernames that start with m and end with i so let's see what we get and we get nothing like we should but if i switch this to e we do get mine because my name starts with an m and ends with an e so hopefully that makes sense the next topic i'd like to talk about is updating instead of just inserting we can also update a table so to start us off let's just say select email from this user right from mike now we know when we run this what we're going to get is mike yahoo.com so let's comment this out and let's make a new thing let's say update users and then i'm going to say set now what i want to do here is set the email equal to not mike gmail.com and then we're going to do this where the username equals mike let's run this okay we didn't get any errors so let's comment this out and uncomment our select statement and you'll see now the email is not mike gmail.com so that's essentially updating it what we do here is we say we're going to update something we give it the name of the table and then we say set and we're going to give it the columns here that we want to set if i wanted to also set the password i could put a comma and say password equals sorry that was a period but i could put a comma and say password equals and then just say something and then the last thing we're doing is where we're saying where we want to update right because we don't want to update every email to this so that's updating the last thing is delete so we know now when we select this we get our email right well let's delete it let's say delete from users and we're going to delete the row where username equals mike i'm going to comment out the select again and i'm going to run it okay we didn't get an error let's uncomment this let's run it we're not getting anything right it seems like it's almost broken but you'll see here if we change this to username equals joe we do get something and that's because what we just did was we deleted the entire row of mic from the database so to give you an idea of what this looks like now let's do a select all from users and you'll see that the table and you'll see that the row in the table is completely gone there's no longer a mic that's going to be it for this tutorial there's a whole lot more that you can learn about sql and sqlite this is just a crash course to get you up and running please reach out in the comments if you have any questions or you'd like to know something i'm going to be continuing my tutorial series after this by going on to using this with php if that's something that sounds interesting to you feel free to check it out and subscribe if you want to see more videos like this thanks for watching i hope you have a great day

Channel: Coding With Mike

Views: 2,565

Keywords: sqlite crash course, sqlite tutorial, sql tutorial, using sqlite in vs code, using sqlite in visual studio code, crash course, database in visual studio code, database tutorial, database crash course, data tutorial, data crash course, sqlite database programming language tutorial, sqlite lesson, sqlite database, sqlite, sqlite3, sqlitestudio, sqlite editor, sql crash course

Id: IBgWKTaG_Bs

Length: 26min 33sec (1593 seconds)

Published: Sat Oct 02 2021