alright so i ended the last video by saying that we can put any kind of data
into any column in sql lite which is a bit strange
so let's actually try doing that and what I might do is just clear and do a .quit
and going to a clear and then just go back in so we can sort of see this at
the top a little bit easy to read and i'm just going to do a select here
.....so let's now try and put in
any kind of data into these columns so going to type....
....
that would have work but forgot semicolon...now it work I have
just put string data into an integer column which is actually believe it or
not is fine in sql lite just to confirm that will select....
....and there's our record you can see the string number we put in the
second entry here which was numbers in other cases has worked quite happily so
we enter the string wherein where number would ordinarily have been placed now
as a Java programmer who's learning java in android course you might actually
be horrified by what you've just seen and in fact doing things like that can
cause problems when you try to get the data back from a java program now if your
code tries to put that phone number into an integer variable then it is going to
crash if you switch databases and try to use the same code in say Microsoft sql
server then it won't work either because the main client server sql
databases do actually check the type of data that goes into columns so make
sure you use an appropriate type for the columns when you create your tables
now one thing that does sql lite lacks is an altar table command for
changing things like the type of the columns
there's actually ways around that creating a new table and moving the data
from the old table into it for example but it's really best to get it right
first time
alright so now we know how to create a table and insert some data or some
rows into it but we can also update the data that's in their now firstly
going to use the . backup command to make a backup of the table you'll see
why in a minute
the command is . back up and then you tell it which database to backup then
the filename you want to back up to
if we don't tell which database you want to backup and it does the current one
which is fine and makes the command very easy to use for this case I'm just going
to do test back up like so
notice that this is a sql lite command not a sql statement so
there's no need to put a semicolon at the end if it starts with a . it's a
sql lite command . first or semicolon last but not both gonna press enter
their alright so I backed it up so moving on let's say we now have steves email
address we want to update his record in the table we actually do that using the
update statement so we type in update....
.....so here i'm updating the email address in the contacts table but you
actually have to be careful with this command i haven't at the moment told
it which row to update
so it's going to update every row in the table i'm going to add the semicolon
now and press enter and now if I type....you can see
what happened there
everyone has the same email address which is almost certainly not what we
want to happen so the update command is a very powerful command and a single
sql statement can update hundreds of thousands of rows in the database so you
want to be very careful when using the update command
especially in an interactive session like this you can render the data in
your database useless and believe me I've done it updated tens of thousands of records
when I only intended to update one in a production database before and just
without going into too much detail cause a lot of grief for all concerned but
luckily this time I backed up the database first so we can get it back and
do the update properly so i can type in . restore test back up and then i can
actually check the data is back doing....you can see
we've got our data back with the original entries alright so how do we
update just steve record to do that what we need to do is we still need to use
the update command but we need to add a where clause i'm going to type....
......
.....and press enter now that's more like what was
required only steves record has now been updated so that's how to use a where
clause is just the word where followed by condition that identifies a row or
set of rows to be updated and you probably see now that's why back ups
are also very important now where clause can be used with many sql statements
so you could display just a subset of the data by using a where clause with
the select statement just do a select just to make sure all
entries are there and we've got his email address has been updated you can
see they've all got individual email addresses and steves email addresses now
been updated so we can also use that where clause in a select statement so we
can do something like.....you can
see that's come back and showed only one entry perhaps more useful though if we
already know the name theirs no point retrieving data that we don't need so we
could do something like....
....you see that just returns
the email the phone number and the email address so that is select insert update
and we can also delete records
no prizes for guessing what the command is you gotta it delete so.....
and once again we have to be very carefully here without a where clause to
specify which row should be deleted the commandant will apple to the entire set of
rows in the database and yes i have done that as well so putting the where clause
in here....
we know that 1234 was the phone number that we entered for brian so i'm going to
press ENTER there and I'm going to do a select command....
and you can see that Brian is now missing from that list and that's
because we've deleted his record by doing using the delete sql statement
and using the where clause which specified his phone number so we've now
seen a few sql statements create insert select update and delete these
are the most common commands that you need and you can do a lot with sql
databases with just those commands there are a few ways to modify the command
especially the Select statement and we'll be having a look at using join in
the next video to relate tables together but that's the basics and hopefully you
feel a bit happy about having to learn a new language and you've seen its
really not going to be perhaps as difficult as you thought it might be
working with sql lite from the command line like this is very useful
because you can concentrate on the details of your tables and columns and
get everything right before trying to include the command in code it gets better
too because there's a couple of sql lite commands that you can use once
everything set up so let's have a look at a few of those the first one we can
type is . tables and . tables lists all the tables in the database which can be
handy when you have a lot of them and forget what about when you know what you
actually called one
the next one . schema that print out the structure of your tables now we only
have one table in this database you can see how it shows the sql commands
that was used to create it so you can copy that command and paste it into
code when you want to create tables in code will create that table in code and
you've got several tables then . schema followed by a table name will put the
structure for just that one table and there's also . dump and that
gives you the sequel statement for creating the table but all the inserts
necessary to populate it with the data that's in it so it wraps the whole thing
and what's called a transaction you can see we've got the begin transaction
and commits there and we'll be talking about that little bit later but again you can
copy and paste the output from dumped into your code and finally . exit or .
quit will actually exit the sql lite shell and take you back to your command
prompt or terminal session so that's the basic introduction to sql lite and
these sql language now the sql lite shell is useful when you need to
design your database and it's generally easy to use some sort of front end to the
database when setting things up so that you can make sure you've got all the
tables created correctly with the right columns and so on
you can also test the queries that you be using your code before you get
around to writing the code so you know that the sql side of things has been
set up correctly and is working ok so we now seen how to create tables and insert
update and delete the record in them and we've also had a brief look at querying
the data in a table so in the next video we're going to work with a database that
already has some data in it so we can practice querying data a bit more and
also look at to how to join tables together so see you in the next video