WEBVTT

1
00:00:01.760 --> 00:00:06.290
alright so i ended the last video by
saying that we can put any kind of data

2
00:00:06.290 --> 00:00:09.620
into any column in sql lite which is a
bit strange

3
00:00:09.620 --> 00:00:16.699
so let's actually try doing that and what I might do is just clear and do a .quit

4
00:00:16.699 --> 00:00:22.369
and going to a clear and then just go
back in so we can sort of see this at

5
00:00:22.369 --> 00:00:26.060
the top a little bit easy to read and
i'm just going to do a select here

6
00:00:26.060 --> 00:00:34.280
.....so let's now try and put in

7
00:00:34.280 --> 00:00:40.579
any kind of data into these columns so
going to type....

8
00:00:40.579 --> 00:01:02.119
....

9
00:01:02.119 --> 00:01:07.340
that would have work but forgot semicolon...now it work I have

10
00:01:07.340 --> 00:01:11.030
just put string data into an integer
column which is actually believe it or

11
00:01:11.030 --> 00:01:15.170
not is fine in sql lite just to
confirm that will select....

12
00:01:15.170 --> 00:01:21.620
....and there's our record you can
see the string number we put in the

13
00:01:21.620 --> 00:01:27.770
second entry here which was numbers in
other cases has worked quite happily so

14
00:01:27.770 --> 00:01:32.630
we enter the string wherein where number
would ordinarily have been placed now

15
00:01:32.630 --> 00:01:37.240
as a programmer you might be horrified by what you've just seen
and in fact doing things like that can

16
00:01:37.240 --> 00:01:42.460
cause problems when you try to get the
data back from a java program now if your

17
00:01:42.460 --> 00:01:46.230
code tries to put that phone number into
an integer variable then it is going to

18
00:01:46.230 --> 00:01:51.070
crash if you switch databases and try to
use the same code in say Microsoft sql

19
00:01:51.070 --> 00:01:55.420
server then it won't work either
because the main client server sql

20
00:01:55.420 --> 00:02:00.040
databases do actually check the type of
data that goes into columns so make

21
00:02:00.040 --> 00:02:03.880
sure you use an appropriate type for the columns when you create your tables

22
00:02:03.880 --> 00:02:09.570
now one thing that does sql lite lacks is an altar table command for

23
00:02:09.570 --> 00:02:11.880
changing things like the type of the
columns

24
00:02:11.880 --> 00:02:15.690
there's actually ways around that
creating a new table and moving the data

25
00:02:15.690 --> 00:02:19.350
from the old table into it for example
but it's really best to get it right

26
00:02:19.350 --> 00:02:20.550
first time

27
00:02:20.550 --> 00:02:24.570
alright so now we know how to create a
table and insert some data or some

28
00:02:24.570 --> 00:02:29.790
rows into it but we can also update the
data that's in their now firstly

29
00:02:29.790 --> 00:02:33.720
going to use the . backup command to
make a backup of the table you'll see

30
00:02:33.720 --> 00:02:34.740
why in a minute

31
00:02:34.740 --> 00:02:41.490
the command is . back up and then you
tell it which database to backup then

32
00:02:41.490 --> 00:02:43.980
the filename you want to back up to

33
00:02:43.980 --> 00:02:47.970
if we don't tell which database you want
to backup and it does the current one

34
00:02:47.970 --> 00:02:51.600
which is fine and makes the command very
easy to use for this case I'm just going

35
00:02:51.600 --> 00:02:55.260
to do test back up like so

36
00:02:55.260 --> 00:03:00.150
notice that this is a sql lite
command not a sql statement so

37
00:03:00.150 --> 00:03:04.650
there's no need to put a semicolon at
the end if it starts with a . it's a

38
00:03:04.650 --> 00:03:10.350
sql lite command . first or semicolon
last but not both gonna press enter

39
00:03:10.350 --> 00:03:16.440
their alright so I backed it up so moving on
let's say we now have steves email

40
00:03:16.440 --> 00:03:21.510
address we want to update his record in
the table we actually do that using the

41
00:03:21.510 --> 00:03:35.310
update statement so we type in update....

42
00:03:35.310 --> 00:03:43.140
.....so here i'm updating the email
address in the contacts table but you

43
00:03:43.140 --> 00:03:46.590
actually have to be careful with this
command i haven't at the moment told

44
00:03:46.590 --> 00:03:48.030
it which row to update

45
00:03:48.030 --> 00:03:51.690
so it's going to update every row in the
table i'm going to add the semicolon

46
00:03:51.690 --> 00:04:02.280
now and press enter and now if I type....you can see

47
00:04:02.280 --> 00:04:03.120
what happened there

48
00:04:03.120 --> 00:04:06.840
everyone has the same email address
which is almost certainly not what we

49
00:04:06.840 --> 00:04:12.180
want to happen so the update command is
a very powerful command and a single

50
00:04:12.180 --> 00:04:17.180
sql statement can update hundreds of
thousands of rows in the database so you

51
00:04:17.180 --> 00:04:19.960
want to be very careful when using the
update command

52
00:04:19.960 --> 00:04:23.950
especially in an interactive session
like this you can render the data in

53
00:04:23.950 --> 00:04:28.630
your database useless and believe me I've done
it updated tens of thousands of records

54
00:04:28.630 --> 00:04:32.980
when I only intended to update one in a
production database before and just

55
00:04:32.980 --> 00:04:37.690
without going into too much detail cause
a lot of grief for all concerned but

56
00:04:37.690 --> 00:04:42.160
luckily this time I backed up the
database first so we can get it back and

57
00:04:42.160 --> 00:04:49.900
do the update properly so i can type in
. restore test back up and then i can

58
00:04:49.900 --> 00:04:56.380
actually check the data is back doing....you can see

59
00:04:56.380 --> 00:05:00.310
we've got our data back with the
original entries alright so how do we

60
00:05:00.310 --> 00:05:04.750
update just steve record to do that what
we need to do is we still need to use

61
00:05:04.750 --> 00:05:09.580
the update command but we need to add a
where clause i'm going to type....

62
00:05:09.580 --> 00:05:23.230
......

63
00:05:23.230 --> 00:05:30.820
.....and press
enter now that's more like what was

64
00:05:30.820 --> 00:05:35.110
required only steves record has now been
updated so that's how to use a where

65
00:05:35.110 --> 00:05:40.030
clause is just the word where followed
by condition that identifies a row or

66
00:05:40.030 --> 00:05:43.990
set of rows to be updated and you
probably see now that's why back ups

67
00:05:43.990 --> 00:05:48.850
are also very important now where clause
can be used with many sql statements

68
00:05:48.850 --> 00:05:52.720
so you could display just a subset of
the data by using a where clause with

69
00:05:52.720 --> 00:05:58.300
the select statement just do a select just to make sure all

70
00:05:58.300 --> 00:06:02.740
entries are there and we've got his
email address has been updated you can

71
00:06:02.740 --> 00:06:05.950
see they've all got individual email
addresses and steves email addresses now

72
00:06:05.950 --> 00:06:11.860
been updated so we can also use that
where clause in a select statement so we

73
00:06:11.860 --> 00:06:23.830
can do something like.....you can

74
00:06:23.830 --> 00:06:27.850
see that's come back and showed only one
entry perhaps more useful though if we

75
00:06:27.850 --> 00:06:30.850
already know the name theirs no point
retrieving data that we don't need so we

76
00:06:30.850 --> 00:06:32.680
could do something like....

77
00:06:32.680 --> 00:06:42.640
....you see that just returns

78
00:06:42.640 --> 00:06:47.860
the email the phone number and the email
address so that is select insert update

79
00:06:47.860 --> 00:06:50.170
and we can also delete records

80
00:06:50.170 --> 00:06:57.190
no prizes for guessing what the command
is you gotta it delete so.....

81
00:06:57.190 --> 00:07:02.590
and once again we have to be very
carefully here without a where clause to

82
00:07:02.590 --> 00:07:07.450
specify which row should be deleted the
commandant will apple to the entire set of

83
00:07:07.450 --> 00:07:12.730
rows in the database and yes i have done
that as well so putting the where clause

84
00:07:12.730 --> 00:07:17.140
in here....

85
00:07:18.640 --> 00:07:23.320
we know that 1234 was the phone number
that we entered for brian so i'm going to

86
00:07:23.320 --> 00:07:29.980
press ENTER there and I'm going to do a
select command....

87
00:07:29.980 --> 00:07:35.920
and you can see that Brian is now
missing from that list and that's

88
00:07:35.920 --> 00:07:40.120
because we've deleted his record by
doing using the delete sql statement

89
00:07:40.120 --> 00:07:45.040
and using the where clause which
specified his phone number so we've now

90
00:07:45.040 --> 00:07:51.880
seen a few sql statements create
insert select update and delete these

91
00:07:51.880 --> 00:07:55.720
are the most common commands that you
need and you can do a lot with sql

92
00:07:55.720 --> 00:08:00.070
databases with just those commands there
are a few ways to modify the command

93
00:08:00.070 --> 00:08:04.600
especially the Select statement and
we'll be having a look at using join in

94
00:08:04.600 --> 00:08:09.340
the next video to relate tables together
but that's the basics and hopefully you

95
00:08:09.340 --> 00:08:13.210
feel a bit happy about having to learn a
new language and you've seen its

96
00:08:13.210 --> 00:08:15.580
really not going to be perhaps as
difficult as you thought it might be

97
00:08:15.580 --> 00:08:19.180
working with sql lite from the
command line like this is very useful

98
00:08:19.180 --> 00:08:23.440
because you can concentrate on the
details of your tables and columns and

99
00:08:23.440 --> 00:08:28.030
get everything right before trying to
include the command in code it gets better

100
00:08:28.030 --> 00:08:31.450
too because there's a couple of sql lite commands that you can use once

101
00:08:31.450 --> 00:08:35.110
everything set up so let's have a look
at a few of those the first one we can

102
00:08:35.110 --> 00:08:41.710
type is . tables and . tables lists all
the tables in the database which can be

103
00:08:41.710 --> 00:08:45.220
handy when you have a lot of them and
forget what about when you know what you

104
00:08:45.220 --> 00:08:46.270
actually called one

105
00:08:46.270 --> 00:08:54.970
the next one . schema that print out the
structure of your tables now we only

106
00:08:54.970 --> 00:08:59.050
have one table in this database you can
see how it shows the sql commands

107
00:08:59.050 --> 00:09:02.500
that was used to create it so you can
copy that command and paste it into

108
00:09:02.500 --> 00:09:07.150
code when you want to create tables in
code will create that table in code and

109
00:09:07.150 --> 00:09:11.440
you've got several tables then . schema
followed by a table name will put the

110
00:09:11.440 --> 00:09:19.780
structure for just that one table and
there's also . dump and that

111
00:09:19.780 --> 00:09:23.410
gives you the sequel statement for
creating the table but all the inserts

112
00:09:23.410 --> 00:09:27.610
necessary to populate it with the data
that's in it so it wraps the whole thing

113
00:09:27.610 --> 00:09:32.200
and what's called a transaction you can
see we've got the begin transaction

114
00:09:32.200 --> 00:09:36.610
and commits there and we'll be talking about
that little bit later but again you can

115
00:09:36.610 --> 00:09:42.100
copy and paste the output from dumped
into your code and finally . exit or .

116
00:09:42.100 --> 00:09:46.840
quit will actually exit the sql lite
shell and take you back to your command

117
00:09:46.840 --> 00:09:51.400
prompt or terminal session so that's the
basic introduction to sql lite and

118
00:09:51.400 --> 00:09:55.840
these sql language now the sql lite shell is useful when you need to

119
00:09:55.840 --> 00:09:59.770
design your database and it's generally
easy to use some sort of front end to the

120
00:09:59.770 --> 00:10:04.330
database when setting things up so that
you can make sure you've got all the

121
00:10:04.330 --> 00:10:07.750
tables created correctly with the right
columns and so on

122
00:10:07.750 --> 00:10:12.370
you can also test the queries that
you be using your code before you get

123
00:10:12.370 --> 00:10:15.700
around to writing the code so you know
that the sql side of things has been

124
00:10:15.700 --> 00:10:21.010
set up correctly and is working ok so we now
seen how to create tables and insert

125
00:10:21.010 --> 00:10:25.360
update and delete the record in them and
we've also had a brief look at querying

126
00:10:25.360 --> 00:10:29.200
the data in a table so in the next video
we're going to work with a database that

127
00:10:29.200 --> 00:10:33.700
already has some data in it so we can
practice querying data a bit more and

128
00:10:33.700 --> 00:10:37.780
also look at to how to join tables
together so see you in the next video