1
00:00:05,010 --> 00:00:09,540
alright so i ended the last video by
saying that we can put any kind of data

2
00:00:09,540 --> 00:00:12,870
into any column in sql lite which is a
bit strange

3
00:00:12,870 --> 00:00:19,940
so let's actually try doing that and what I might do is just clear and do a .quit

4
00:00:19,940 --> 00:00:25,610
and going to a clear and then just go
back in so we can sort of see this at

5
00:00:25,610 --> 00:00:29,310
the top a little bit easy to read and
i'm just going to do a select here

6
00:00:29,310 --> 00:00:37,530
.....so let's now try and put in

7
00:00:37,530 --> 00:00:43,820
any kind of data into these columns so
going to type....

8
00:00:43,820 --> 00:01:05,360
....

9
00:01:05,360 --> 00:01:10,590
that would have work but forgot semicolon...now it work I have

10
00:01:10,590 --> 00:01:14,280
just put string data into an integer
column which is actually believe it or

11
00:01:14,280 --> 00:01:18,420
not is fine in sql lite just to
confirm that will select....

12
00:01:18,420 --> 00:01:24,870
....and there's our record you can
see the string number we put in the

13
00:01:24,870 --> 00:01:31,020
second entry here which was numbers in
other cases has worked quite happily so

14
00:01:31,020 --> 00:01:35,880
we enter the string wherein where number
would ordinarily have been placed now

15
00:01:35,880 --> 00:01:39,210
as a Java programmer who's learning
java in android course you might actually

16
00:01:39,210 --> 00:01:43,410
be horrified by what you've just seen
and in fact doing things like that can

17
00:01:43,410 --> 00:01:48,630
cause problems when you try to get the
data back from a java program now if your

18
00:01:48,630 --> 00:01:52,400
code tries to put that phone number into
an integer variable then it is going to

19
00:01:52,400 --> 00:01:57,240
crash if you switch databases and try to
use the same code in say Microsoft sql

20
00:01:57,240 --> 00:02:01,590
server then it won't work either
because the main client server sql

21
00:02:01,590 --> 00:02:06,210
databases do actually check the type of
data that goes into columns so make

22
00:02:06,210 --> 00:02:10,050
sure you use an appropriate type for the columns when you create your tables

23
00:02:10,050 --> 00:02:15,740
now one thing that does sql lite lacks is an altar table command for

24
00:02:15,740 --> 00:02:18,050
changing things like the type of the
columns

25
00:02:18,050 --> 00:02:21,860
there's actually ways around that
creating a new table and moving the data

26
00:02:21,860 --> 00:02:25,520
from the old table into it for example
but it's really best to get it right

27
00:02:25,520 --> 00:02:26,720
first time

28
00:02:26,720 --> 00:02:30,740
alright so now we know how to create a
table and insert some data or some

29
00:02:30,740 --> 00:02:35,960
rows into it but we can also update the
data that's in their now firstly

30
00:02:35,960 --> 00:02:39,890
going to use the . backup command to
make a backup of the table you'll see

31
00:02:39,890 --> 00:02:40,910
why in a minute

32
00:02:40,910 --> 00:02:47,660
the command is . back up and then you
tell it which database to backup then

33
00:02:47,660 --> 00:02:50,150
the filename you want to back up to

34
00:02:50,150 --> 00:02:54,140
if we don't tell which database you want
to backup and it does the current one

35
00:02:54,140 --> 00:02:57,770
which is fine and makes the command very
easy to use for this case I'm just going

36
00:02:57,770 --> 00:03:01,430
to do test back up like so

37
00:03:01,430 --> 00:03:06,320
notice that this is a sql lite
command not a sql statement so

38
00:03:06,320 --> 00:03:10,820
there's no need to put a semicolon at
the end if it starts with a . it's a

39
00:03:10,820 --> 00:03:16,520
sql lite command . first or semicolon
last but not both gonna press enter

40
00:03:16,520 --> 00:03:22,610
their alright so I backed it up so moving on
let's say we now have steves email

41
00:03:22,610 --> 00:03:27,680
address we want to update his record in
the table we actually do that using the

42
00:03:27,680 --> 00:03:41,480
update statement so we type in update....

43
00:03:41,480 --> 00:03:49,310
.....so here i'm updating the email
address in the contacts table but you

44
00:03:49,310 --> 00:03:52,760
actually have to be careful with this
command i haven't at the moment told

45
00:03:52,760 --> 00:03:54,200
it which row to update

46
00:03:54,200 --> 00:03:57,860
so it's going to update every row in the
table i'm going to add the semicolon

47
00:03:57,860 --> 00:04:08,450
now and press enter and now if I type....you can see

48
00:04:08,450 --> 00:04:09,290
what happened there

49
00:04:09,290 --> 00:04:13,010
everyone has the same email address
which is almost certainly not what we

50
00:04:13,010 --> 00:04:18,350
want to happen so the update command is
a very powerful command and a single

51
00:04:18,350 --> 00:04:23,350
sql statement can update hundreds of
thousands of rows in the database so you

52
00:04:23,350 --> 00:04:26,130
want to be very careful when using the
update command

53
00:04:26,130 --> 00:04:30,120
especially in an interactive session
like this you can render the data in

54
00:04:30,120 --> 00:04:34,800
your database useless and believe me I've done
it updated tens of thousands of records

55
00:04:34,800 --> 00:04:39,150
when I only intended to update one in a
production database before and just

56
00:04:39,150 --> 00:04:43,860
without going into too much detail cause
a lot of grief for all concerned but

57
00:04:43,860 --> 00:04:48,330
luckily this time I backed up the
database first so we can get it back and

58
00:04:48,330 --> 00:04:56,070
do the update properly so i can type in
. restore test back up and then i can

59
00:04:56,070 --> 00:05:02,550
actually check the data is back doing....you can see

60
00:05:02,550 --> 00:05:06,480
we've got our data back with the
original entries alright so how do we

61
00:05:06,480 --> 00:05:10,920
update just steve record to do that what
we need to do is we still need to use

62
00:05:10,920 --> 00:05:15,750
the update command but we need to add a
where clause i'm going to type....

63
00:05:15,750 --> 00:05:29,400
......

64
00:05:29,400 --> 00:05:36,990
.....and press
enter now that's more like what was

65
00:05:36,990 --> 00:05:41,280
required only steves record has now been
updated so that's how to use a where

66
00:05:41,280 --> 00:05:46,200
clause is just the word where followed
by condition that identifies a row or

67
00:05:46,200 --> 00:05:50,160
set of rows to be updated and you
probably see now that's why back ups

68
00:05:50,160 --> 00:05:55,020
are also very important now where clause
can be used with many sql statements

69
00:05:55,020 --> 00:05:58,890
so you could display just a subset of
the data by using a where clause with

70
00:05:58,890 --> 00:06:04,470
the select statement just do a select just to make sure all

71
00:06:04,470 --> 00:06:08,910
entries are there and we've got his
email address has been updated you can

72
00:06:08,910 --> 00:06:12,120
see they've all got individual email
addresses and steves email addresses now

73
00:06:12,120 --> 00:06:18,030
been updated so we can also use that
where clause in a select statement so we

74
00:06:18,030 --> 00:06:30,000
can do something like.....you can

75
00:06:30,000 --> 00:06:34,020
see that's come back and showed only one
entry perhaps more useful though if we

76
00:06:34,020 --> 00:06:37,020
already know the name theirs no point
retrieving data that we don't need so we

77
00:06:37,020 --> 00:06:38,850
could do something like....

78
00:06:38,850 --> 00:06:48,810
....you see that just returns

79
00:06:48,810 --> 00:06:54,030
the email the phone number and the email
address so that is select insert update

80
00:06:54,030 --> 00:06:56,340
and we can also delete records

81
00:06:56,340 --> 00:07:03,360
no prizes for guessing what the command
is you gotta it delete so.....

82
00:07:03,360 --> 00:07:08,760
and once again we have to be very
carefully here without a where clause to

83
00:07:08,760 --> 00:07:13,620
specify which row should be deleted the
commandant will apple to the entire set of

84
00:07:13,620 --> 00:07:18,900
rows in the database and yes i have done
that as well so putting the where clause

85
00:07:18,900 --> 00:07:23,310
in here....

86
00:07:24,810 --> 00:07:29,490
we know that 1234 was the phone number
that we entered for brian so i'm going to

87
00:07:29,490 --> 00:07:36,150
press ENTER there and I'm going to do a
select command....

88
00:07:36,150 --> 00:07:42,090
and you can see that Brian is now
missing from that list and that's

89
00:07:42,090 --> 00:07:46,290
because we've deleted his record by
doing using the delete sql statement

90
00:07:46,290 --> 00:07:51,210
and using the where clause which
specified his phone number so we've now

91
00:07:51,210 --> 00:07:58,050
seen a few sql statements create
insert select update and delete these

92
00:07:58,050 --> 00:08:01,890
are the most common commands that you
need and you can do a lot with sql

93
00:08:01,890 --> 00:08:06,240
databases with just those commands there
are a few ways to modify the command

94
00:08:06,240 --> 00:08:10,770
especially the Select statement and
we'll be having a look at using join in

95
00:08:10,770 --> 00:08:15,510
the next video to relate tables together
but that's the basics and hopefully you

96
00:08:15,510 --> 00:08:19,380
feel a bit happy about having to learn a
new language and you've seen its

97
00:08:19,380 --> 00:08:21,750
really not going to be perhaps as
difficult as you thought it might be

98
00:08:21,750 --> 00:08:25,350
working with sql lite from the
command line like this is very useful

99
00:08:25,350 --> 00:08:29,610
because you can concentrate on the
details of your tables and columns and

100
00:08:29,610 --> 00:08:34,200
get everything right before trying to
include the command in code it gets better

101
00:08:34,200 --> 00:08:37,620
too because there's a couple of sql lite commands that you can use once

102
00:08:37,620 --> 00:08:41,280
everything set up so let's have a look
at a few of those the first one we can

103
00:08:41,280 --> 00:08:47,880
type is . tables and . tables lists all
the tables in the database which can be

104
00:08:47,880 --> 00:08:51,390
handy when you have a lot of them and
forget what about when you know what you

105
00:08:51,390 --> 00:08:52,440
actually called one

106
00:08:52,440 --> 00:09:01,140
the next one . schema that print out the
structure of your tables now we only

107
00:09:01,140 --> 00:09:05,220
have one table in this database you can
see how it shows the sql commands

108
00:09:05,220 --> 00:09:08,670
that was used to create it so you can
copy that command and paste it into

109
00:09:08,670 --> 00:09:13,320
code when you want to create tables in
code will create that table in code and

110
00:09:13,320 --> 00:09:17,610
you've got several tables then . schema
followed by a table name will put the

111
00:09:17,610 --> 00:09:25,950
structure for just that one table and
there's also . dump and that

112
00:09:25,950 --> 00:09:29,580
gives you the sequel statement for
creating the table but all the inserts

113
00:09:29,580 --> 00:09:33,780
necessary to populate it with the data
that's in it so it wraps the whole thing

114
00:09:33,780 --> 00:09:38,370
and what's called a transaction you can
see we've got the begin transaction

115
00:09:38,370 --> 00:09:42,780
and commits there and we'll be talking about
that little bit later but again you can

116
00:09:42,780 --> 00:09:48,270
copy and paste the output from dumped
into your code and finally . exit or .

117
00:09:48,270 --> 00:09:53,010
quit will actually exit the sql lite
shell and take you back to your command

118
00:09:53,010 --> 00:09:57,570
prompt or terminal session so that's the
basic introduction to sql lite and

119
00:09:57,570 --> 00:10:02,010
these sql language now the sql lite shell is useful when you need to

120
00:10:02,010 --> 00:10:05,940
design your database and it's generally
easy to use some sort of front end to the

121
00:10:05,940 --> 00:10:10,500
database when setting things up so that
you can make sure you've got all the

122
00:10:10,500 --> 00:10:13,920
tables created correctly with the right
columns and so on

123
00:10:13,920 --> 00:10:18,540
you can also test the queries that
you be using your code before you get

124
00:10:18,540 --> 00:10:21,870
around to writing the code so you know
that the sql side of things has been

125
00:10:21,870 --> 00:10:27,180
set up correctly and is working ok so we now
seen how to create tables and insert

126
00:10:27,180 --> 00:10:31,530
update and delete the record in them and
we've also had a brief look at querying

127
00:10:31,530 --> 00:10:35,370
the data in a table so in the next video
we're going to work with a database that

128
00:10:35,370 --> 00:10:39,870
already has some data in it so we can
practice querying data a bit more and

129
00:10:39,870 --> 00:10:43,950
also look at to how to join tables
together so see you in the next video