SQLite Tutorial for Beginners in 1 Hour

hello everyone do you want to understand about sqlite then you have come to the right place hi my name is sandeep and in this video we will give basic introduction about sqlite database tool and all the sql commands basically all the important sql commands that can be performed in it so without wasting much time let's get started [Music] all right if you're new to this channel consider subscribing to our channel so that you get more tutorials on technical videos all right first to download sqlite go to sqlite.org slash download and based on your operating system in my case i have windows just come here pre-compile binary for windows and just click on this third link okay in case if you have a mac you can come and click on this particular zip file or for linux you can just click on this zip file all right so in my case i'll check this third link because it contains a bundle of command line tools along with my sqlite database files all right so once you click on this so actually i've already clicked on this and you'll get a file like this and what you have to do is you have to just extract the file okay so let me just extract it here itself and you see here we have three files so sqlite3.exe is the most important file because it is the actual executable and sql diff and sqlite3 underscore analyzer are optional file if you want you can keep it else you can delete this as well now before showing any sql operation on sqlite first you should know what exactly is sqlite okay so sqlite is a software package which is self-contained and which is having zero configuration that means you do not have to have any configuration for it okay you do not have to define any configuration in fact it is self-contained that means it is self-contained database okay and it is serverless you do not have to have any server for this all right so these are the basic things which i have told you about sqlite and in fact you can save the sqlite in your local file local pc as well i'll show you that as well for example in my case i'll just show you that you can access the database and tables directly from your system in a particular folder so first let me just copy these three files okay i'll just give control c and i'll just go to my c drive here i'll just click on new folder and i'll just create a folder with the name sqlite so in this particular folder what i'll do is i'll copy all these three files okay as i told you this is the most important file that is sqlite3 other two files are optional if you want you can keep it else you can delete it but i would suggest you to keep all these three files it is always better now what you have to do is you have to go to command prompt okay you have to go to command prompt and if you just give your sql i3 now you notice it will give an error you see it says that it is not recognized because you have downloaded sqlite i mean you have copied the files to this particular folder so this is the path but you have not defined the path so first what i'll do is i'll just exit it from here and again what i'll do is i'll just go to system environment okay so i'll just go to edit the system environment variables here under advanced i'll go to environment variables and again i'll come to path you can go and you can define the path under this user variable also you see you will be having a path here in fact at this moment i don't have a path but you can just click on new and you can define a path for example variable name as path and you can just give the variable value as this particular path location but it is always better to define the path in system variable because it will be applicable to the entire system all right so i'll just click on this and i'll just give edit and go to new so click new and here give the path so just give okay and come out now if i just go to command prompt now you'll notice if i just give sqlite 3 you see that it says the sqlite version okay and it says that it is connected to transient in-memory database now transient in-memory database means that it is not a permanent database that means data is not saved on your local machine all right so for learning purpose what we'll do is we'll create a permanent database in fact this is what we are going to do here and just to see all the cell commands you can just give dot help so that you will get all the help and it will open all the commands which you can perform here all right and to come out you can just give dot q or dot quit and now you see that you have come outside it all right now first what i'll do is i'll just go to cd and this particular path okay so this is my path all right and i'll come here and i'm going to create a particular database in this particular folder and then what i'll do is i'll create tables inside this particular folder now if i just come to my command prompt you see if i just give dot cell space cd you see that it will show the directory in which you are in so you see here at this moment it is not showing okay so i've done a mistake here it is not recognizing this particular shell command is because first i have to open this particular database right inside the database i'll perform all the sqlite operations okay so to create a permanent database what i can do is i give something like this that is sqlite3 and then mydb.db so this is the database which i'm going to create okay that is mydb.db in fact you can give any name as per your choice you have enter and you see that it says sqlite version okay so i've created a particular database and at this moment you see there is nothing inside this particular folder okay because we have not created any tables and in fact i have not saved anything in this particular database as well okay so let me do one thing first i'll just give dot tables okay if i just give dot tables it will show you the tables inside this particular database but at this moment we do not have any tables that is the reason you see that it does not give anything now first let me just create a table okay so i can just give for example let's say create table and name of the table for example i can just give stores or i can just give a store i can just give store id as integer or int okay so this will store a number then i can just give here item id as let's say integer okay i'll just give item name this one will be text field for that i can have the text and attend i'll just give a semicolon now please note that it is not mandatory to have the sql commands in uppercase but it is always better to have the sql commands in uppercase so that you are following the proper standard okay and it is better to give semicolon at end that means the command is going to end here so if i just give enter now you see that it is not showing anything here that means it is success now if i just give dot tables actually i have given in uppercase so it should be dot tables okay so please note all the sql commands you can give in either uppercase or lowercase but these are all cell commands for example dot tables okay this is not a sql command so for this i have to give in small later so if i just give dot tables let me just give dot tables enter and you see you have this particular table all right and if you just come here you see here you have this particular database which is created and in fact inside this particular database you have this particular table right that is store table so there are five major data types okay so what you will do is if i just go to i'll just show you if i just go to sqlite.org slash datatype3.html you can see everything about data types okay so on a higher level we have five types of data types okay the first one is null so if you do not have a defined value so if you think that a particular field can have undefined values or it can have null values then you can have that particular data type as null you can have integer for example you have int that is that is one type of integer then you can have small end then you have signed integer right then we have real so it is a floating value right then you have text so it is going to store a particular text for example name or you know if you have a city or anything which is having a text string it will contain this and then we have blob for example images and so on so it will convert that images and pixels and it will store it so what i'll do is i'll just provide you this particular link you can go through this link and you can see for all these data types it has been explained in detail and let's move forward so we have created this table that is store table and now what we'll do is let me just do one thing first let me insert some records okay so i'll just give insert so for inserting any record in the table we use insert command and to create table i've shown you that we use create table command that is create table and name of the table and then this is my first field that is store id which is integer or end then we have item id which will be int okay in fact you can give any field and you can have any type of data i mean any data type you can attach with that particular field based on your requirement then we have name and name cannot be a numbered or it cannot be integer and that is the reason i have just given here text all right so it's very simple and if i just give insert into table name so my table is store so the command to insert values into table is insert into table name values and here you can give a particular field name for example store id comma item id comma item name and i can just give sorry for that actually it will be insert into a store and then let me just come here and i'll just give the field okay and i'll just give values and for store id let me just give a value of one for item id for example let's say i'll just give one one and item name for example i'll just give for example pen okay you can give anything in item name just hit enter and now one record is inserted it is not giving a message here because there is a successful insertion of the record if there is an error it will give an error here okay again what i'll do is instead of giving all the fields you see if you want to insert the records for all the fields okay then you can also give something like this for example insert into store and you can omit the fields here okay you can just give something like this that is insert into store values to and let me just give for example 2 2 okay there is some issue here ok so i have to just give you a 2 2 and this time i'll just give for example pencil and just give enter and now you see that again this record is inserted just to see how many records we have inserted you can just give for example select star or it does better i'll just given capital later that is select star that is select all the fields or all the records from the table so select star from stores okay and you see we have two records that is 1 11 and pen and you see there is a delimiter here right so this is the separator by default in this particular case now if you want to save this database what you can do is you can just give for example dot save and name of this particular database or in fact you can save the table also so if i just give dot save and name of the table for example store you see if i just give enter this particular table is saved now if i just go to my sqlite you see i have this particular table as well okay in the same way you can also save a particular database now in this particular case i have already created this particular database that is mydb.db and exclusively if you want to save this you can just give dot save and name of this particular database and it is mydb right if i just give enter now you see this particular database is saved and if you don't want this particular background color of this command prompt you can just come here you can change the color from here okay so you can just go to colors you can change the color in fact you can just go to terminal you can do some changes here if you want to change the font you can just come here and you can change the font so if i just want to make it for example let's say 28 i can do it i think 24 would be fine now you see i have a bigger font okay and you can change a lot of thing and as i told you you can just give dot q or dot quit to quit from this particular database so if i just give dot quit now you see that i have quit from this particular database all right and you see my database is there okay i have this particular file okay that is mydb then this particular file is for this particular database then we have this particular table as well now if i just come back here you can again go back and open the particular database okay and i have come to this particular location now what i'll do is i'll just open my database with dot open command or i'll just give sqlite3 and then name of the database so this time i'll just give dot open because i have already given sqlite3 and then mydb.db previously so this time i'll just show you that you can use mydb.db as well and actually there is some problem here okay so it is not able to recognize this dot open command because you have not given sqlite 3 at top okay so you have to just give sqlite 3 so that you have come inside this particular sqlite all right so you see we are using this particular sqlite3.exe for this okay and now if i just give dot open and if i just give my db dot db now this time it is successful okay and it is not showing any error and this is how you can open a particular database as well or what i'll do is i'll just give sqlite3 a name of my database all right so let me just give sqlite3 and mydb dot db now this time you can give you can see some message that data that the database is locked you see here it is giving an it is not giving a particular message because first let me just force it to come out so for that i'll just give control c to force it okay and again i'll just go to cd and then c colon sqlite okay so this is my folder and i'll directly give here this time sqlite3 mydb dot db and i have come inside this particular folder right now first let me just show you first thing is like you see this is the command to select all the records from the store table okay now i have these two records i'll just show you that we can use where command so for example if you want to put a condition for example i want to retrieve all the records where the store id is only one so for that what i can do is i can just give select let me just given capital select star from store where store id equal to one okay just give enter and you see it has faced me only one record and here in the first record we have 1 11 and pen okay and if you want to have the header as well what you can do is you can just give dot header on again this time i have to give in small case okay dot header on okay just give enter and please note here that i'm not going to give a semicolon here okay this is wrong you do not have to give semicolon for shell commands okay this is not sql command that you have to put semicolon for sql commands you are going to put a semicolon at the end so that the command is going to end there okay but in this case i'm not going to put anything there okay just give enter and now this time you see here i have just given dot header on that means if i just give here select star from store where store id is this time let's say if i just give or let me just delete okay what i'll do i'll just give here for example where item name is equal to i'll just give for example let's say pen or i'll just give pencil this time okay and if i just give semicolon and enter now you see here we have a header as well that is store id item id and item name okay so for that i have just given dot header on if you want to have header on all right and you can also have and command for example if i just give for example let's say select star from store where item name is pencil and i'll just give item name is equal to for example pen okay just give semicolon and what here i want to say is that i want to select all the records from show table where item name is equal to pencil and item name is equal to pen but you see that there is no record as such where item name is also pencil and pen so it will give no record you see here and you see if i just give select star from so table where item name is pencil and i'll just give item id so let me just give here item id is equal to let's say i'll just give 2 2. okay so what i'm saying is that select are select records from so table where item name is pencil and item id is to do again it will not give any record you see here it says that i think okay so there is no column you see here it says error here because i have given item underscore id so i'll just give upper arrow in my keyboard to retrieve the previous command itself and here i think we were having item id right so just hit enter and you see here we have item name okay so we have one record okay so we have pencil and then item id is 2 2 right so that is the reason it faced this particular record you can also have r command so what you can do is you can just give something like this that is select star from show table where item name is equal to pencil and you can just give for example instead of and you'll just give or you can just give a store name or store underscore in fact let me just check that we have store id we do not have store name right so i'll just have store id as equal to this time i'll just give store id as one so i'll just give enter you see that for the first record store id is one and for the second record the item name is pencil so or command means that select record from the table either of these conditions are successful then you fetch these record so item name is pencil okay so this is also faced and the store id is one so this is also phased right so there are two records and just to make it more clearer what i'll do is i'll insert more records into table so just to make this course little fast i'm just giving in small case so please remember it is better for you to have in upper case but in this case i'll just give insert into table name that is store i'll just give values and let me just give for example 3 4 4 and item name as for example book okay just give a semicolon attend give enter and again if i just give select star from store i do not put anywhere clause now you see i have three records in the same way i'll put more records so i'll just give here for example five okay and here let me just give for example six seven and i can just give for example this time notebook all right give enter and again i have four records all right so in this way you can have lot of records okay and you see here i can also have something like this that is insert into this particular store that is store id as one i can have more records for example 4 4 and i can also have book okay so there is no problem with that because we do not have any constraint that store id will have on the unique values okay so you see here i can also have 144 book okay so for store id 1 we have item id at 11 as 11 and 44 and for item id 11 this is called as pen for item id 44 this is called as book okay so this is all about and and or in fact you can group based on a particular column so what you can do is for example you can just give select star from table group by you can just give for example item underscore id okay so it is in fact it is item id okay now you see it says one two three and five okay so to explain this group by clause in detail what i'm going to do is first let me create another table so i'll just give create table this time let's create a invoice table so i'll just give invoice first i'll just give invoice id as integer or you can just give complete integer okay and i'll just give invoice name i'll just mark this as text and i'll just have invoice amount and i'll just make it as for example let's say you can have it as float or let's say just keep it simple we'll just have integer okay so create table table name and then i have just given the fields along with the data type all right just give enter and if i just give insert into table that is invoice and i'll just give values i'll just give invoice id as for example let's say 10 invoice name i'll just give for example let's say some dummy name i have just given and invoice amount let me just give 100 rupees okay so i'll just give 100 rupees or you can think it as dollar so in fact i have to just give 100 because i should not give in double quotes or single quote because it is not a text field so for integer you have to just pass the value as it is for text string you have to just give double quotes or single quotes all right just give semicolon just give enter and if i just give select start from invoice you see we have one record in the same way i can have multiple records for example for 10 that is for this particular invoice id i can have another name okay and i'll just give for example one five double zero just give enter again i'll just give another name here okay and here i'll just give for example 6 5 0 enter again i'll just change the invoice so instead of 1 0 i'll just have for example 30 and here i'll just change it to for example let's say this particular invoice name and i can just have for example one seven double zero give enter okay so i'll just create more records just to show you that the behavior of group by clause order by clause and so on will be very easy for your understanding so this time i'll just give for example 20 and again i'll just give some records and i'll just give for example 16 or let's say 6500 give enter again let me just give 50 okay just change it to some other value okay again i'll just give eight five double zero okay again let me just give 50 and again let me just give something like this again i'll just give 7 500 enter now what i want you to do is that i want to retrieve first of all let me just give select star from invoice okay now what i want is let's say that i have this table that is invoice table and you see if i just give select star from this particular table we have three records for invoice id 10 right similarly we have two records for invoice id 50. so i want to understand the count of invoice id 10 20 and so on so what i'll do is for that i can just give the group by clause i'll just show you if i just give select invoice id and i'll just give count of invoice id okay count of invoice id okay from this table that is invoice group by invoice id okay so if i just give enter now you see that for invoice id 10 the count is three right then for invoice id 20 you see the count is one for 30 we have one and for 50 we have two counts that means we have two records right so in this way you can use the group by clause similarly you can sort the records by order by so i can just give for example at this moment if i just give select let's say star from this table that is invoice order by let's say i want to sort the records based on at this moment we have this 10 30 20 50 right so let me just short the records based on this invoice amount so i can just give select start from invoice order by invoice amount just give enter and you see by default it will be in ascending order okay and if you see if you just want to sort the records based on descending order you can just give here the esc just give semicolon and now you see it is in descending order right so in this way you can just use order by clause okay in fact you can have between operator as well so i can just give select star from invoice where i can just give her invoice amount between 1700 and let's say 6500 okay just give semicolon and you see there are only two records where invoice amount is between 1700 and 6500 you see here 6500 and 1700 both are included so the starting and end are also included in the same way you can also have in operator for example you can just give select star from invoice where you see this time what i'll do is i don't want to select all the fields from invoice table what i'll do is i just want to select only let's say invoice id so i'll just give here invoice id okay select invoice id from invoice where invoice amount or where this time i'll just give let's say invoice id itself so i'll just give invoice id in for n operator we have to use parenthesis so i'll just select all the invoices i mean all the invoice id from this particular table where invoice id in 10 comma let's say 50 okay so if i just give enter now there is no such column as invoice id so there is some problem here so i have just given here invoice which is wrong it should be c e not c so it is invoice id and just give enter and you see that we have invoice id okay these three noise id that is 10 10 10 again 50 and 50. okay so in this case we have used inoperator because i want to select all the noise id where invoice id is either 10 or 50. so you can consider this in operator as or operator where in voice id is 10 or 50 you can also have something like this so it is same as where invoice id is equal to 10 or invoice id is equal to 50. you see both will give the same result okay now this is all about select operators now let's talk about update so at this moment if i just give let's say select star from this particular invoice table i have these records right now what i want is i want to update this particular name okay what i'll do is i'll just use update so this is the update sql command so i'll just give update table name that is update invoice set invoice name is equal to for example let's say test where invoice id is equal to 10 now you see if i just give like this it will update these three records all these three records so what i'll do is i'll just give where invoice id is 10 and invoice amount is equal to 100 so in this way it will update only the first record and if i just give enter now you see if i just give select star from invoice now you see that i have this test so i have updated this particular record that is the invoice name where invoice id is 10 and invoice amount is 100 in fact this where clause is optional if i just give update invoice set invoice name as test what it will do is it will set the invoice name as test for all the records okay so be careful when you're using update command okay and if you want to check the structure or schema of a particular table you can just give dot schema and you see we have two table that is store table and this is the schema right and we have invoice table if you want to know the schema of only one table for example invoice table you can just give dot schema and name of the table that is invoice now you see it has given the schema of this particular table only all right now let's talk about some date functions okay so if you just go to this particular link that is sqlite.org and then lang underscore date func here you'll see all the date related functions okay so you can just go through this particular list and you can understand in a detailed manner at this moment let me just show you few commands for example if i just want to show the date in gmt i'll just give select date and it is going to give me this particular date that is in gmt okay so it is 2022 iphone 07 hyphen 12 okay so this is year then this is the month and then this is the date okay similarly i can just give for example let's say select time and it will give the time as well right if i want to get date along with time i can just give select date time okay and you see we have date along with time so just go to this link and you can explore we have date we have time date time we have julian day okay we have strf time so it is for a particular format okay and you see these are the formats here for strf time okay so you can try all these functions okay and now let's come back here and first let me just discuss about constraints okay so there are a lot of constraints for example first one we have is primary key so in a table let's say that if you want to have a primary key that means a key which will not contain any null values and which will only contain unique records then we can have primary key so let's say if i just want to create a table so i'll just give let's say create table and let's say the name of the table as student and i'll just give student id so i'll just give s id as integer student name and i'll just give as text and if i just give for example let's say uh as phone number okay so i don't know the structure of this so i can just give as text so you can argue that why we are not giving as uh number field so i don't know how the students are going to enter the phone number so they can give phone number something like this that is for example plus 87 like this okay or they can just give something like this that is with country code for example plus 9 1 for india and then you know some number okay or they can give only the phone number for example something like this okay so that is the reason i have just given this as phone as text okay you can also have the address also so student address or you can just have email okay you can also have this as text okay now if i just create this table the issue here is that student id what i want is that a student id should be unique or it should be a primary key now the name can be different for example there can be multiple students with the same name but id should be unique and it should not contain any null values okay so what i'll do is i'll just make this as primary key so if i just give here primary key okay and now what i'll do is i'll just give enter so this particular table is created now i'll try to insert record into this particular table so i'll just give insert into student and for restore id uh sorry for student id let me just give one and name i'll just give for example tom and phone number i'll just give for example let's say some random number okay and let's say for email i'll just give test1 at the rate gmail.com and if i just give enter and before that i'll just give insert into student it will be values okay and just give enter now if i just give select star from student you see i have this particular record right now this time if i want to insert a record with the same student id that is for let's say the name i'll just keep as for example ricky and phone number i can keep same or i can change it for example i can just have for example you know 888 and something like that and i can just have here for example test2 now in this case you see here i'm trying to insert a record with the same student id but if you notice while creating table we have defined the student id as integer that means a student id as integer along with primary key that means it will not contain duplicate values okay if i just give enter now you see it says that front time error that is unique constraint field and you notice it has given this particular error right because there cannot be two records with the same student id because student id is a primary key so in this case if i just give for example now if i just change it to let's say 2 now if i just give enter now you see there is no issue and if i just give select star from table you see i have two records right so this is all about primary key you can also have null for example let's say that at this moment if i just give here let's say insert into student values and i'll just give here 3 and let's say i don't give the name so for student id 3 i have not provided any name okay so now you see here if i just give enter you see it is fine and we did not get any error now if i just give select star from student you see that i do not have any record for this particular value that is for student id 3 the name is not there so what i'll do is i'll just give create table this time let's say i'll just give student uh let's say not null because i'm just showing about not null constraint so i have just given the name of the table as student notal you can give any name that doesn't matter so i'll just give here student id let's say i'll just give integer then i'll just give so a name i'll just give text field and i'll just keep these two field only okay just give enter and this table is created i'll just give insert okay i missed to create table with not null constraint okay so let me just go back here sorry for that because i am talking about not null in fact this table is also valid but i am explaining about not null constraint right so i'll just give here store id int and i'll just give as for example this one as primary key okay and store name i'll just give as text not null that means it will not contain null values and i'll just give not null 1 because not null table is already created now just hit enter and you notice if i just give insert into table name that is student not null 1 values if i just give for example let's say 1 comma let's say sam now if i just give enter it is fine again i'll just add another record this time i'll just give 2 and what i'll do is here i have not passed any values now you see if i just give enter it is working fine because you see here if i just give let's say select star from this particular table okay select star from student not null 1 okay now you see here i have 1 and sam then 2 and then we do not have any record now please do not get confused between no value and null values okay so what i mean is you see here if i just give for example let's say if i just give insert into the student not null 1 and here i'll just give s id comma as name values now you see here i have just given three and this time what i'll do is i'll just give something like this for example i will not pass anything for us name now previously i did pass an empty value you notice the difference here i passed an empty value here i'm not passing any value at all that means this particular field this time that means student name will have null values but we have defined that our table will not have null values for name column now if i just give enter now you see here if i just come here if i just give enter it says that not null constraint field because we have just given that for this particular table we have this column for which the value is 3. we have not defined any value for student name so just to fix this if i just give something like this you see sid as three comma as name and here i'll just give for example let's say michael okay just hit enter now this time it is fine you see here it has three and michael so do not get confused with empty record and null values okay that is very important now let's talk about unique constraint now before that please note that primary key is similar to unique constraint but there is a difference the primary key can have one column or a group of column considered as a primary key and there will be only one primary key in a table but in case of unique constraint it will force a particular field or the field on which unique constraint is defined to contain unique values okay so for example let's say i'll just give create table t1 i'll just give tid as let's say integer and unique you can also give not null if you want it is up to you you can have combination of all these and i can just have for example the name as text field and just give enter and i can just insert a chord so i'll just give insert into t1 i can just give one and i'll just give for example let's say hello just give semicolon enter and oh sorry actually it should be t1 and then values sorry so it will be values just give enter and i'll just give select star from t1 you see i have one hello and if i just insert another record so i'll just give insert into t1 and if i just give for example let's say 2 and if i just give let's say hi and you see this is fine but if i just want to give for example insert into t1 values 2 again and if i just give for example let's say here welcome now you see that it says that unique constraint failed because for t1 we have defined that it will contain only unique values you see here we have given create table t1 t1 as integer unique and not null so it is must that this particular field should contain unique values similarly you have another constraint for example default constraint so if i just give for example let's say another table that is create table t2 and i'll just give tid integer unique not null and t name let's say i'll just give text and i'll just say for example default as for example let's say any or i can just give for example unknown okay and if i just give enter this particular table is created i'll just give insert into t2 values 2 and you see if i don't pass any value you see if i just give let's say t2 and if i just give let's say t id comma or if i just give tid values and then two so what i'm trying to say is that in this particular table that is t2 table i have a particular tid s2 now by default it will take the t name as unknown because what we have defined here while creating the table that t name will be text field and by default it will take the value as unknown okay so if i just give enter you see here if i just give enter and if i just give let's say okay let me just give select star from t2 now you see that it says 2 and then unknown okay so this is called as default similarly you can have check consent for example what i'll do is i'll just create another table that is t3 and i'll just have tid and this time i'll just give for example t amount okay and i'll just give t amount as for example let's say integer or float and i'll just give check you can also have real for example okay it is better for amount field you have real okay so i'll just give real check and i'll just give t amount is greater than let's say 800 or 900 let's say okay and here again i have to give a parenthesis to end now this particular table is created now if i just want to insert record i'll just give insert into t3 values i'll just give for example one and you see if i just give let's say 950 okay just give semicolon now if i just give select star from t3 you see that i have this particular record now if i just try to insert a value which is lesser than 900 for example you see here i have given a check so we have given a check that the t amount should always be greater than 900 but if i just give for example let's say 90 you see it will fail now it says that error check constant fail because we have a check that t amount should be greater than 900 okay so again if i just give even 900 you see that 900 greater than 900 is again a failure so again it is an error every time we have to insert a record greater than 900 so if i just give 901 i think it should work uh it says that okay we have given one comma nine zero one okay so if you just see that we have unique consent also right so that is the reason we cannot have one comma 950 so we have to give something different from one because one id is already existing now you see here it will be fine right and you see we have two records right so this is all about all the consent you can even alter the structure of the table so for example i can just give alter table and if you want to change the structure of the table in this case let's say if i want to change t3 so you have to give alter table table name and let's say i want to add another field here so i'll just give add and let's say that i want to add a field for example let's say t name okay and i want to add this as a text field now if i just hit enter now you see it is working fine and if i just give select start from t3 now you see that we have two field earlier now we have three field that is tid t amount and t name and even i can insert record so i'll just say insert into t3 i'll just say for example 3 comma let's say 890.0 and actually 890 will not work because it should be greater than 900 and for text field i'll just give for example let's say some dummy text and after semicolon it says that it is an error insert into oh sorry for that again i have to give values okay now you see if i just give select star from t3 you see that we have a particular value for this particular field right so this is all about alter in fact you can rename also so i can just give rename command so i let's say that i want to rename this particular field that is t name to some other name for example let's say uh t name one so for that i can just give alter table table name that is t3 and i'll just give rename t name to t name one let's give semicolon and give enter and now if i just give let's say if i just give select star from t3 now you see we have tid t amount and t name one right in the same way you can even alter or in fact you can rename the table name itself so i can just give something like this that is altered table table name rename to the new name for example t3 new okay you can give any name and now if i just give select star from t3 now you see that it will give an error because t3 does not exist in fact we have the new name as t3 new and you see these are the records now if you want to drop a particular table that means if you want to drop a table permanently you can drop it so at this moment if i just give dot tables you see i have these many tables that is store we have student we have student not null we have t1 t2 invoice and so on okay so let's say that i want to drop this t1 table so first i'll just give select star from t1 and you see we have two records for t1 now if i want to drop it what i can do is i can just give drop table and name of the table that is t1 just hit enter and now if i just give select start from t1 you see that it says no such table because drop will permanently drop the table that means it will delete the table permanently okay and if you want to delete particular record from table what you can do is you can just use delete command okay so for example let's say i have for example t3 new okay so we have three records let's say that i want to delete this record okay so what i can do is i can just give delete from table name that is t3 new where we have t id is equal to and if i just give enter now if i just give select star from t3 new now you see that second record is gone that means the record with tid s2 is gone right so be very careful with delete if you don't give where clause it will delete all the records for example let's say that i have t2 right so if i just give select star from t2 you see we have two records in fact actually we have only one record if i just give delete or first let me just insert few records so insert insert into t2 values let's say i'll just give three comma for example let's say i have just given some dummy text again i'll insert and i'll just say for example let's say 4 okay again i'll just say for example let's say nine okay and here i'll just give for example this particular text now if i just give select star from t2 i have four records right one two three and four now if i just give delete from t2 or delete from t2 okay if i just give delete from t2 without any where clause now you see if i just give enter now if i just give select star from t2 all the records are gone so the difference between drop and delete is that the table will still exist in case of delete but the records will be deleted but in case of drop it will permanently delete the table so be very careful while executing delete and drop command you can also take the backup of particular database for example i have this particular table right i mean if you just come here we have mydb right that is mydb.db if you want to take the backup of this particular database you can just give the command that is dot backup and you can give the name of the backup for example i can just give my db underscore bkp dot db you can give any name now you see i have a backup ready okay now you see here i have this particular db also so this is a backup database now let's say that again if i just come here and again if i just give dot tables now you see that we have so many tables right now let's say that i want to delete this table that is t2 if i just give drop t2 just give semicolon and enter oh sorry actually it will be drop table t2 okay just give enter and this particular table is gone right so if i just give dot schema or you can just give dot schema t2 now you see that actually t2 does not exist i'll just show you if i just give select star from t2 you see it says no such table okay and even when you give schema t2 it did not give anything that means this particular table does not exist and hence there is no schema for this table now we have already taken a backup here if you remember we have taken a backup here right that is mydb underscore backup if you want to restore from this particular backup what you have to do is you have to just give dot restore and you have to name the particular backup so mydb underscore backup so dot db so what it is going to do is it will restore all the tables from this particular backup that is mydb underscore bkp and now you see if i just give this time dot tables now you see that we have t2 still present because we have restored the data from this particular database to my current database which is mydb.db now you see that there are various modes so at this moment you see if i just give for example select star from invoice you see by default there is a pipe symbol here and by default the mode is list and let's say that you want to change the mode so for that you see if i just show you if you just see here we have sqlite.org slash cli.html here we have different cli commands okay so you can just go through these cli commands to understand various modes in detail okay and i'll just show you a few of them so if you just come back here and if i just give for example dot mode as let's say csv just give enter now if i just give select star from invoice now you see that this particular result is in the csv format that is the separator is comma similarly if i just give let's say otherwise i'll just give let's say dot mode and insert so this is an insert mode and again if i just give select star from invoice now you see that we have inserted table command okay that is insert into table right so these are the values for this particular table right and this is showing with insert command because we have given that dot mode as insert similarly i can just have dot mode as tab if i just give tab now if i just give select star from invoice now you see that the data is in tab format okay so there is a tab between each field right similarly if you want in column format you can just give dot mode space column okay just give enter and now you see that it is a column right at this moment i'll just set the mode as list by default okay and now this is what we were having previously right now one last thing which i wanted to show you is that i would request all of you to save the database explicitly that means you can just give save that is dot save and name of your database that is mydb dot db it is always better at this moment it is showing that your database is locked because we are performing some operations in this particular database so if i just give dot q or dot quit now i have quit from this particular database right now if i just give sqlite 3 and now if i just give dot save and my db dot db that means i'm just trying to save my database right now you see here this particular database is saved right now if i just come back here i think we have covered most of the things right now i just wanted to show you one last thing is that you see here if you just go to this particular website that is d beaver dot io you see this is a d beaver website wherein it is containing a database tool so if you don't want to work with this particular command prompt you can just go to this particular tool you can just download it and you can just try it okay so you can just see what all things are there in this particular tool it is up to you or you can use another tool for example we have the sqlitebrowser.org this is also a very famous tool okay so you can use this particular tool as well okay it is up to you or if you don't want to use these two tools or even command prompt you can use simple text editor or code editor for example i have created a video on how to run sqlite in visual studio code you can just have a look into this particular video i'll just give this particular link to you so that you can just run sqlite in visual studio code which is a very famous code editor alright so with this i'll just close this video if you have any doubts do reach out to me via the comment section and do let me know whether you like this video even if you dislike the video do let me know and if some information is missing please reach out to me in case if you like this video hit on the like button do subscribe to our channel and share this video to maximum number of friends or family members who wants to understand about sqlite so that's it this was just a basic tutorial on sqlite and there are a lot of commands that can be explained in sqlite so in fact i'm trying to create a complete course on sqlite so stay tuned and once that course will be published i will convey to you one thing which you have to do is you just have to subscribe to a channel so that you will get all those notification so that's it in this video till then take care and bye [Music]

Channel: TutorialBrain

Views: 32,875

Keywords: SQLite, SQLite tutorial, SQLite3, sqli, sqlitestudio, sqlite for beginners, sqlite crash course, sqlite course, sqlite under 1 hour, sqlite under 2 hour

Id: GMHK-0TKRVk

Length: 61min 39sec (3699 seconds)

Published: Wed Jul 13 2022