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

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

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

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

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

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

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

8
00:00:43,840 --> 00:01:05,379
....

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

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

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

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

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

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

15
00:01:35,900 --> 00:01:40,510
as a programmer you might be horrified by what you've just seen
and in fact doing things like that can

16
00:01:40,510 --> 00:01:45,730
cause problems when you try to get the
data back from a java program now if your

17
00:01:45,730 --> 00:01:49,500
code tries to put that phone number into
an integer variable then it is going to

18
00:01:49,500 --> 00:01:54,340
crash if you switch databases and try to
use the same code in say Microsoft sql

19
00:01:54,340 --> 00:01:58,690
server then it won't work either
because the main client server sql

20
00:01:58,690 --> 00:02:03,310
databases do actually check the type of
data that goes into columns so make

21
00:02:03,310 --> 00:02:07,150
sure you use an appropriate type for the columns when you create your tables

22
00:02:07,150 --> 00:02:12,840
now one thing that does sql lite lacks is an altar table command for

23
00:02:12,840 --> 00:02:15,150
changing things like the type of the
columns

24
00:02:15,150 --> 00:02:18,960
there's actually ways around that
creating a new table and moving the data

25
00:02:18,960 --> 00:02:22,620
from the old table into it for example
but it's really best to get it right

26
00:02:22,620 --> 00:02:23,820
first time

27
00:02:23,820 --> 00:02:27,840
alright so now we know how to create a
table and insert some data or some

28
00:02:27,840 --> 00:02:33,060
rows into it but we can also update the
data that's in their now firstly

29
00:02:33,060 --> 00:02:36,990
going to use the . backup command to
make a backup of the table you'll see

30
00:02:36,990 --> 00:02:38,010
why in a minute

31
00:02:38,010 --> 00:02:44,760
the command is . back up and then you
tell it which database to backup then

32
00:02:44,760 --> 00:02:47,250
the filename you want to back up to

33
00:02:47,250 --> 00:02:51,240
if we don't tell which database you want
to backup and it does the current one

34
00:02:51,240 --> 00:02:54,870
which is fine and makes the command very
easy to use for this case I'm just going

35
00:02:54,870 --> 00:02:58,530
to do test back up like so

36
00:02:58,530 --> 00:03:03,420
notice that this is a sql lite
command not a sql statement so

37
00:03:03,420 --> 00:03:07,920
there's no need to put a semicolon at
the end if it starts with a . it's a

38
00:03:07,920 --> 00:03:13,620
sql lite command . first or semicolon
last but not both gonna press enter

39
00:03:13,620 --> 00:03:19,710
their alright so I backed it up so moving on
let's say we now have steves email

40
00:03:19,710 --> 00:03:24,780
address we want to update his record in
the table we actually do that using the

41
00:03:24,780 --> 00:03:38,580
update statement so we type in update....

42
00:03:38,580 --> 00:03:46,410
.....so here i'm updating the email
address in the contacts table but you

43
00:03:46,410 --> 00:03:49,860
actually have to be careful with this
command i haven't at the moment told

44
00:03:49,860 --> 00:03:51,300
it which row to update

45
00:03:51,300 --> 00:03:54,960
so it's going to update every row in the
table i'm going to add the semicolon

46
00:03:54,960 --> 00:04:05,550
now and press enter and now if I type....you can see

47
00:04:05,550 --> 00:04:06,390
what happened there

48
00:04:06,390 --> 00:04:10,110
everyone has the same email address
which is almost certainly not what we

49
00:04:10,110 --> 00:04:15,450
want to happen so the update command is
a very powerful command and a single

50
00:04:15,450 --> 00:04:20,450
sql statement can update hundreds of
thousands of rows in the database so you

51
00:04:20,450 --> 00:04:23,230
want to be very careful when using the
update command

52
00:04:23,230 --> 00:04:27,220
especially in an interactive session
like this you can render the data in

53
00:04:27,220 --> 00:04:31,900
your database useless and believe me I've done
it updated tens of thousands of records

54
00:04:31,900 --> 00:04:36,250
when I only intended to update one in a
production database before and just

55
00:04:36,250 --> 00:04:40,960
without going into too much detail cause
a lot of grief for all concerned but

56
00:04:40,960 --> 00:04:45,430
luckily this time I backed up the
database first so we can get it back and

57
00:04:45,430 --> 00:04:53,170
do the update properly so i can type in
. restore test back up and then i can

58
00:04:53,170 --> 00:04:59,650
actually check the data is back doing....you can see

59
00:04:59,650 --> 00:05:03,580
we've got our data back with the
original entries alright so how do we

60
00:05:03,580 --> 00:05:08,020
update just steve record to do that what
we need to do is we still need to use

61
00:05:08,020 --> 00:05:12,850
the update command but we need to add a
where clause i'm going to type....

62
00:05:12,850 --> 00:05:26,500
......

63
00:05:26,500 --> 00:05:34,090
.....and press
enter now that's more like what was

64
00:05:34,090 --> 00:05:38,380
required only steves record has now been
updated so that's how to use a where

65
00:05:38,380 --> 00:05:43,300
clause is just the word where followed
by condition that identifies a row or

66
00:05:43,300 --> 00:05:47,260
set of rows to be updated and you
probably see now that's why back ups

67
00:05:47,260 --> 00:05:52,120
are also very important now where clause
can be used with many sql statements

68
00:05:52,120 --> 00:05:55,990
so you could display just a subset of
the data by using a where clause with

69
00:05:55,990 --> 00:06:01,570
the select statement just do a select just to make sure all

70
00:06:01,570 --> 00:06:06,010
entries are there and we've got his
email address has been updated you can

71
00:06:06,010 --> 00:06:09,220
see they've all got individual email
addresses and steves email addresses now

72
00:06:09,220 --> 00:06:15,130
been updated so we can also use that
where clause in a select statement so we

73
00:06:15,130 --> 00:06:27,100
can do something like.....you can

74
00:06:27,100 --> 00:06:31,120
see that's come back and showed only one
entry perhaps more useful though if we

75
00:06:31,120 --> 00:06:34,120
already know the name theirs no point
retrieving data that we don't need so we

76
00:06:34,120 --> 00:06:35,950
could do something like....

77
00:06:35,950 --> 00:06:45,910
....you see that just returns

78
00:06:45,910 --> 00:06:51,130
the email the phone number and the email
address so that is select insert update

79
00:06:51,130 --> 00:06:53,440
and we can also delete records

80
00:06:53,440 --> 00:07:00,460
no prizes for guessing what the command
is you gotta it delete so.....

81
00:07:00,460 --> 00:07:05,860
and once again we have to be very
carefully here without a where clause to

82
00:07:05,860 --> 00:07:10,720
specify which row should be deleted the
commandant will apple to the entire set of

83
00:07:10,720 --> 00:07:16,000
rows in the database and yes i have done
that as well so putting the where clause

84
00:07:16,000 --> 00:07:20,410
in here....

85
00:07:21,910 --> 00:07:26,590
we know that 1234 was the phone number
that we entered for brian so i'm going to

86
00:07:26,590 --> 00:07:33,250
press ENTER there and I'm going to do a
select command....

87
00:07:33,250 --> 00:07:39,190
and you can see that Brian is now
missing from that list and that's

88
00:07:39,190 --> 00:07:43,390
because we've deleted his record by
doing using the delete sql statement

89
00:07:43,390 --> 00:07:48,310
and using the where clause which
specified his phone number so we've now

90
00:07:48,310 --> 00:07:55,150
seen a few sql statements create
insert select update and delete these

91
00:07:55,150 --> 00:07:58,990
are the most common commands that you
need and you can do a lot with sql

92
00:07:58,990 --> 00:08:03,340
databases with just those commands there
are a few ways to modify the command

93
00:08:03,340 --> 00:08:07,870
especially the Select statement and
we'll be having a look at using join in

94
00:08:07,870 --> 00:08:12,610
the next video to relate tables together
but that's the basics and hopefully you

95
00:08:12,610 --> 00:08:16,480
feel a bit happy about having to learn a
new language and you've seen its

96
00:08:16,480 --> 00:08:18,850
really not going to be perhaps as
difficult as you thought it might be

97
00:08:18,850 --> 00:08:22,450
working with sql lite from the
command line like this is very useful

98
00:08:22,450 --> 00:08:26,710
because you can concentrate on the
details of your tables and columns and

99
00:08:26,710 --> 00:08:31,300
get everything right before trying to
include the command in code it gets better

100
00:08:31,300 --> 00:08:34,720
too because there's a couple of sql lite commands that you can use once

101
00:08:34,720 --> 00:08:38,380
everything set up so let's have a look
at a few of those the first one we can

102
00:08:38,380 --> 00:08:44,980
type is . tables and . tables lists all
the tables in the database which can be

103
00:08:44,980 --> 00:08:48,490
handy when you have a lot of them and
forget what about when you know what you

104
00:08:48,490 --> 00:08:49,540
actually called one

105
00:08:49,540 --> 00:08:58,240
the next one . schema that print out the
structure of your tables now we only

106
00:08:58,240 --> 00:09:02,320
have one table in this database you can
see how it shows the sql commands

107
00:09:02,320 --> 00:09:05,770
that was used to create it so you can
copy that command and paste it into

108
00:09:05,770 --> 00:09:10,420
code when you want to create tables in
code will create that table in code and

109
00:09:10,420 --> 00:09:14,710
you've got several tables then . schema
followed by a table name will put the

110
00:09:14,710 --> 00:09:23,050
structure for just that one table and
there's also . dump and that

111
00:09:23,050 --> 00:09:26,680
gives you the sequel statement for
creating the table but all the inserts

112
00:09:26,680 --> 00:09:30,880
necessary to populate it with the data
that's in it so it wraps the whole thing

113
00:09:30,880 --> 00:09:35,470
and what's called a transaction you can
see we've got the begin transaction

114
00:09:35,470 --> 00:09:39,880
and commits there and we'll be talking about
that little bit later but again you can

115
00:09:39,880 --> 00:09:45,370
copy and paste the output from dumped
into your code and finally . exit or .

116
00:09:45,370 --> 00:09:50,110
quit will actually exit the sql lite
shell and take you back to your command

117
00:09:50,110 --> 00:09:54,670
prompt or terminal session so that's the
basic introduction to sql lite and

118
00:09:54,670 --> 00:09:59,110
these sql language now the sql lite shell is useful when you need to

119
00:09:59,110 --> 00:10:03,040
design your database and it's generally
easy to use some sort of front end to the

120
00:10:03,040 --> 00:10:07,600
database when setting things up so that
you can make sure you've got all the

121
00:10:07,600 --> 00:10:11,020
tables created correctly with the right
columns and so on

122
00:10:11,020 --> 00:10:15,640
you can also test the queries that
you be using your code before you get

123
00:10:15,640 --> 00:10:18,970
around to writing the code so you know
that the sql side of things has been

124
00:10:18,970 --> 00:10:24,280
set up correctly and is working ok so we now
seen how to create tables and insert

125
00:10:24,280 --> 00:10:28,630
update and delete the record in them and
we've also had a brief look at querying

126
00:10:28,630 --> 00:10:32,470
the data in a table so in the next video
we're going to work with a database that

127
00:10:32,470 --> 00:10:36,970
already has some data in it so we can
practice querying data a bit more and

128
00:10:36,970 --> 00:10:41,050
also look at to how to join tables
together so see you in the next video