WEBVTT 1 00:00:00.350 --> 00:00:01.570 Hi guys, and welcome back. 2 00:00:01.570 --> 00:00:02.680 I hope you're doing great. 3 00:00:02.680 --> 00:00:04.940 In this video, we're going to be setting up our 4 00:00:04.940 --> 00:00:08.610 local SQL development environment when you're programming, 5 00:00:08.610 --> 00:00:11.520 and that includes programming in SQL is a good idea 6 00:00:11.520 --> 00:00:14.480 to type things out and try them out as you go along. 7 00:00:14.480 --> 00:00:19.110 So that's why it's a good idea to download a SQL Data Viewer 8 00:00:19.110 --> 00:00:21.020 so that you can create your database 9 00:00:21.020 --> 00:00:22.650 and start typing out the commands 10 00:00:22.650 --> 00:00:25.300 and trying them out as we go along. 11 00:00:25.300 --> 00:00:26.453 So let's get to it. 12 00:00:27.900 --> 00:00:30.380 I recommend you download SQLite browser 13 00:00:30.380 --> 00:00:35.360 to download it just go to sqlitebrowser.org/dl. 14 00:00:35.360 --> 00:00:37.067 And you'll find they've got Windows, 15 00:00:37.067 --> 00:00:39.490 Mac OS and Linux builds and so forth. 16 00:00:39.490 --> 00:00:42.360 We're going to cover how to instal it on Mac OS here in 17 00:00:42.360 --> 00:00:45.210 this video and on windows in a future video, 18 00:00:45.210 --> 00:00:47.240 but it's really quite straightforward. 19 00:00:47.240 --> 00:00:49.980 In Windows, you wanna download the standard installer 20 00:00:49.980 --> 00:00:52.730 for either 32 bits or 64 bits. 21 00:00:52.730 --> 00:00:55.670 For most of you, you'll want 64 bits 22 00:00:55.670 --> 00:00:58.453 and for Mac OS just download the latest release here. 23 00:00:59.890 --> 00:01:01.870 Now that that's downloaded, we can click it 24 00:01:01.870 --> 00:01:03.823 and go ahead and instal it. 25 00:01:06.470 --> 00:01:09.240 You'll see that when opening it up on Mac OS, 26 00:01:09.240 --> 00:01:12.160 you get your installer icon in your desktop. 27 00:01:12.160 --> 00:01:13.700 And then when that opens, 28 00:01:13.700 --> 00:01:15.930 you can simply drag the application over 29 00:01:15.930 --> 00:01:17.420 to your applications. 30 00:01:17.420 --> 00:01:18.960 And in my case, I've already got it. 31 00:01:18.960 --> 00:01:20.490 So it's asking me what I wanna replace, 32 00:01:20.490 --> 00:01:23.250 which I do wanna do, since this is a later version. 33 00:01:23.250 --> 00:01:24.700 And that's it, you've got it. 34 00:01:25.710 --> 00:01:29.810 Now I've gone ahead and opened DB Browser for SQLite here. 35 00:01:29.810 --> 00:01:33.100 And you can see that it looks kinda tricky, 36 00:01:33.100 --> 00:01:33.940 kind of complicated. 37 00:01:33.940 --> 00:01:35.420 You've got a few things going on. 38 00:01:35.420 --> 00:01:37.620 But don't worry, we're gonna go over exactly 39 00:01:37.620 --> 00:01:38.970 how you use it. 40 00:01:38.970 --> 00:01:41.470 The first thing to do when you're working with SQLite 41 00:01:41.470 --> 00:01:43.600 is you want to create a database. 42 00:01:43.600 --> 00:01:46.120 A database is gonna hold tables, 43 00:01:46.120 --> 00:01:49.330 tables are gonna hold your rows and columns. 44 00:01:49.330 --> 00:01:52.110 So let's go ahead and create a new database. 45 00:01:52.110 --> 00:01:54.290 I'm going to put it just in my home folder 46 00:01:54.290 --> 00:01:56.250 anywhere you want is fine. 47 00:01:56.250 --> 00:01:58.490 I'm gonna call it data.db. 48 00:01:58.490 --> 00:02:00.660 It's quite common for SQLite databases 49 00:02:00.660 --> 00:02:05.170 to have the.db end file suffix there. 50 00:02:05.170 --> 00:02:07.210 So you can use that if you want. 51 00:02:07.210 --> 00:02:10.690 Alternatively, you can use .sqlite.sqlite3 52 00:02:10.690 --> 00:02:12.033 or.db3. 53 00:02:12.870 --> 00:02:14.240 Now that we've got that, 54 00:02:14.240 --> 00:02:16.880 and the DB Browser will ask us whether we want 55 00:02:16.880 --> 00:02:19.650 to add a table and it pops up with this window 56 00:02:19.650 --> 00:02:21.700 for adding your table definition, 57 00:02:21.700 --> 00:02:23.240 I'm just gonna cancel out of that 58 00:02:23.240 --> 00:02:25.080 and show you how you can create your own tables 59 00:02:25.080 --> 00:02:25.913 whenever you want. 60 00:02:25.913 --> 00:02:27.360 And that's what this button here on the left, 61 00:02:27.360 --> 00:02:30.630 just click CREATE TABLE, and you get the same thing back. 62 00:02:30.630 --> 00:02:32.580 We're going to create a table here 63 00:02:32.580 --> 00:02:34.950 that I'm gonna call users. 64 00:02:34.950 --> 00:02:36.920 And notice that as you type here, 65 00:02:36.920 --> 00:02:41.330 you get down here the SQL equivalent of all the things 66 00:02:41.330 --> 00:02:43.300 that we're doing with our graphical interface. 67 00:02:43.300 --> 00:02:44.900 So we're going to learn more about create table 68 00:02:44.900 --> 00:02:47.290 as we go along and also in the next video. 69 00:02:47.290 --> 00:02:51.610 And but this shows you what the application is executing on 70 00:02:51.610 --> 00:02:55.220 the SQLite database in text format. 71 00:02:55.220 --> 00:02:56.750 Now we can add fields 72 00:02:56.750 --> 00:03:00.760 and one field is one column in traditional database peak. 73 00:03:00.760 --> 00:03:02.320 So we're gonna add a new column 74 00:03:02.320 --> 00:03:05.250 and I'm gonna call it first name. 75 00:03:05.250 --> 00:03:08.100 And the type that I'm gonna use is text. 76 00:03:08.100 --> 00:03:11.850 You've got integers for whole numbers, reals, and numerics 77 00:03:11.850 --> 00:03:16.130 for non-whole numbers blob for like all other data, 78 00:03:16.130 --> 00:03:19.550 text is simply for text, these check boxes here, 79 00:03:19.550 --> 00:03:20.760 we're going to learn more about 80 00:03:20.760 --> 00:03:22.240 them as we go along the course. 81 00:03:22.240 --> 00:03:23.910 So don't worry about them just now, 82 00:03:23.910 --> 00:03:26.510 we're also going to add another field that is surname, 83 00:03:26.510 --> 00:03:29.140 that is also going to be text. 84 00:03:29.140 --> 00:03:31.110 Again, we'll explore the other data types 85 00:03:31.110 --> 00:03:34.200 and everything else in here in due course. 86 00:03:34.200 --> 00:03:35.880 Okay, so let's press OK. 87 00:03:35.880 --> 00:03:38.400 And we've got our first table here. 88 00:03:38.400 --> 00:03:40.690 It tells you how it was created, 89 00:03:40.690 --> 00:03:42.450 and you've got the name in there. 90 00:03:42.450 --> 00:03:43.283 Now at any point, 91 00:03:43.283 --> 00:03:44.830 you can create new tables by clicking 92 00:03:44.830 --> 00:03:47.670 the CREATE TABLE icon up there. 93 00:03:47.670 --> 00:03:50.451 If we want to execute arbitrary SQL commands, 94 00:03:50.451 --> 00:03:52.650 as we will be learning throughout the course, 95 00:03:52.650 --> 00:03:55.580 we want to go to the execute SQL tab. 96 00:03:55.580 --> 00:04:00.030 And here we've got a place to write our SQL code, 97 00:04:00.030 --> 00:04:04.740 I'm going to type a SQL query here, select star from users, 98 00:04:04.740 --> 00:04:07.200 we're going to learn everything about this syntax 99 00:04:07.200 --> 00:04:09.420 as we go along in the next few videos. 100 00:04:09.420 --> 00:04:12.230 But for now, I'm just gonna type this query in here end it 101 00:04:12.230 --> 00:04:13.300 with a semicolon, 102 00:04:13.300 --> 00:04:17.130 and then press this button here to play the query. 103 00:04:17.130 --> 00:04:19.630 Notice that if we have any results, i.e, 104 00:04:19.630 --> 00:04:21.840 if we had already put some data into our table, 105 00:04:21.840 --> 00:04:23.560 which we haven't done yet, 106 00:04:23.560 --> 00:04:27.510 then we would get that data output in this square here. 107 00:04:27.510 --> 00:04:29.990 Finally, the output from the server 108 00:04:29.990 --> 00:04:31.890 that gives us a little bit of information about 109 00:04:31.890 --> 00:04:35.180 what happened is shown at the bottom and it says result, 110 00:04:35.180 --> 00:04:37.470 zero rows returned in two milliseconds. 111 00:04:37.470 --> 00:04:38.870 So that was pretty quick. 112 00:04:38.870 --> 00:04:41.420 And then it tells you what run. 113 00:04:41.420 --> 00:04:43.750 So at line one, we run this query here, 114 00:04:43.750 --> 00:04:46.760 notice that that matches exactly what we typed. 115 00:04:46.760 --> 00:04:49.840 You've got a few more options up here in terms of you know, 116 00:04:49.840 --> 00:04:52.280 printing out your results and things like that. 117 00:04:52.280 --> 00:04:55.200 You can explore them if you want, but we will only be using 118 00:04:55.200 --> 00:04:59.200 the play command, which allows us to run the queries here. 119 00:04:59.200 --> 00:05:00.760 All right, that's it for getting set up. 120 00:05:00.760 --> 00:05:02.250 As you can see, it's pretty straightforward. 121 00:05:02.250 --> 00:05:03.710 We've created a table. 122 00:05:03.710 --> 00:05:05.200 And now in the next video we're going 123 00:05:05.200 --> 00:05:08.240 to learn more about the CREATE TABLE command 124 00:05:08.240 --> 00:05:10.420 and what we can do with it by typing 125 00:05:10.420 --> 00:05:13.960 the SQL out ourselves and exploring a few of its options. 126 00:05:13.960 --> 00:05:15.713 So I'll see you in the next video.