so we got the computer setup so that we can use sql lite from the command
line we're going to have a look at how to create databases and tables and start
to see how the sql language is used so we'll be using sql lite in our
programs later but for now we're just going to focus on the sql language so
that we can concentrate on learning a bit about sql without worrying about
the java side of things so you need to start a terminal session or command
prompt on your computer on windows click on the start menu and type command
to launch a command prompt or you could just use the same methods that I used
in the setup video to start a command prompt for Windows 10 depending on what
version you're running on a Mac command space and type in terminal and on
Linux you can launch this terminal quickly using ctrl alt t alright I'm
on a mac as I said so I'm going to start sql lite and the way we start
that is by typing sql lite 3 and press enter
so that starts the sql lite shell program and we can also specify the name
of a database on the command line so I'm going to show you how to do this I'm
going to quit out of this by typing .quit
as I mentioned we can specify the name of a database on the command line so i'm
going to call this database test so to do that we type a space after the
sql lite 3 program name and type test . DB and press enter incidentally
there's also command to open a database file if you forget to put the name
on the command line and we'll have a look at that a bit later you will find
that this sql lite program is a fairly minimal interface that just
really tells us the version of sql lite that were using and that we can use
. help to get some instructions we can also enter sql statements and with
some versions there's also a helpful reminder that sql statement must be
terminated with a semi colon and you can see earlier i put a semicolon to
finish to finish up the command quit but the quit was meant to be a . quit which
doesn't have a semicolon but in any event will talk more about semicolons in
and the need to out those in a little bit later and it is normal that you forget
to do this quite often
you'll have forget to put a semicolon in but as you say it's not the end of the
world you can just enter the semi colon on the next line and the statement will
then be executed but we'll get to that in a minute but let's start off that by
typing . help you can see that sql lite is helpfully are telling us that
if we type .help we'll get some help so we'll do that . help press enter and you
can see it's got a whole page of information on the screen there and I'm
just scrolling up and down with my mouse lots of different command options
they're available for us is well its not a lot but really in the scheme of things it's
not really a lot of commands they're comparing that so to the Java language
is a significantly higher number of things you need to know in java than
sql lite but even with that said you probably won't remember them all
straight away if ever though so . help is a useful way to remind yourself of them
if you ever need to go back and see what a particular command all about
alright so before creating a new table in the database you want to type in .
headers....this actually shows the column names at the
start of the data which is a handy reminder of what we call the columns
ok so those are a list of the commands that sql lite recognizes but
when creating and querying tables we just use sql statements so let's
create a simple contacts table with the sql commander we are about the type i'm going
to type in.....
....
....and i'm going to press enter now that doesn't
seem to do much and that's something you'll notice on the sql lite if you
do something wrong it will let you know but if everything works fine then it is
keeps quiet so this case because we haven't got anything back other
than the prompt asking us to type in something else on the next line it's
nice and quiet and it generally doesn't mean that the command worked so in this
case it's created that table for us that table called contacts has three columns
the name the phone and the email but to sql lite doesn't tell us that it
worked and again if it hadn't worked would get an error but otherwise we'll
just move on to the next instruction
alright so with this table now let's actually put some data into that table
and we can use the sql insert statement to do that so we can type....
....
....press enter once again we get no confirmation that
it worked but in this case no news is good news and I use single quotes there but
you can also use double quotes thing to remember those if your embedding sql
commands in java that makes sense to use double quotes for the strings and single
quotes around the sql statements and you'll see me do that when it comes to
creating programs that work on our databases
alright so how do we know that this actually worked these commands actually
did something we can actually check that it has we can query the table now the
Select statement is very useful in sql or SQL and it's how you query the
data at the table
now it's a very flexible command but as at its simplest you can just tell it
what columns you want and the name of the table to get it from now i'm going
to actually type the sql reserved words in capitals in this statement and
it's actually useful to do that especially in programs and scripts but to
sql itself doesn't care
people generally just do it to make it obvious which other sql reserved
words and which are things like tables and columns names so to see whats in the contacts
table we use this we type....
....and press enter
we can now see the record that we just inserted now the asterix there by the
way means all columns you saw me type in the Select statement we could
have been more explicit and type something like this....
.....that give us the same result and we just wanted an
email addresses for example we could just do select email from contacts and
that would just give us the email alright I'm gonna do that command again
but this time I'm going to forget to put a semicolon at the end so select email
from contacts press enter you can see that nothing is printed and sql lite has
just to put another prompt up waiting for more input and notice it's got the
three dots and then the greater than sign here rather than the sql lite
greater than that normally starts up the command prompt when we are about to
type command in now you can add other clauses after select and it's nice to be
able to spit them on two different lines to make it more readable so sql lite
will keep letting you type a sql command and won't try to execute it until
you type the semicolon now at the moment I don't want to add anything to the
statement so I'm just going to enter a semicolon and press enter the statement
executes as you can see and we get the email address for our one record so when
you forget the semicolon just type it on the next line and generally everything
will work fine
ok so let's add a couple of additional records are going to use double quotes
for the first ones just to show you it's still works fine....
so.....
notice that's a slightly different form of the insert statement and because we're
providing values for all the fields and giving them in the order that the fields
were defined in the table there's no need in this case to specify the list of
fields so it actually works out to be a bit simpler if we try this insert.....
....
....press enter we actually get an error and obviously that's because we've been
specified two values but the table contacts got three columns like it's
telling us on the screen so i could fix that by adding another value but if we
don't know the email address and that wouldn't be an option so instead i can
tell which columns i want to add data for just provide value to insert into
those columns so i could do something like....
....you can see there's no error in
that case and we can now check what's in the table so let's look at that so
select star from contacts and you can see we've got 3 entries showing on
the screen now and note that timand Brian have email addresses but steve
hasn't incidentally you can see the column names appearing at the start of
the list
that's because we use that command . headers on earlier without that would
see the data but the commons wouldn't be labeled for us and that's maybe not that
important in this example as there aren't any three fields and they
all contain obvious values in other words it's easy to see that brian is a
name and Brian@email.com is an email address but if there
are a lot of say numeric fields then it could be useful to have a reminder of
what's in each column now talking about numbers we probably shouldn't store the
phone number in an integer column i just did that to show you how you can specify
the type of columns when you create the table and a phone number is really best
stored as text field now sql lite doesn't actually have type for its fields
it's strange in that respect although you specify a type when defining the columns
that's really just what you intend to put into them and in fact because the
sql lite implement standard sql it has to use that standard form for
creating tables
the columns have a data type but you can actually put any kind of data into any
column and will look at doing that when we continue this in the next video