WEBVTT 1 00:00:00.340 --> 00:00:01.840 Hi guys and welcome back. 2 00:00:01.840 --> 00:00:03.120 In this video, we're going to learn 3 00:00:03.120 --> 00:00:05.120 how to interact with PostgreSQL 4 00:00:05.120 --> 00:00:08.350 using ElephantSQL here in the browser. 5 00:00:08.350 --> 00:00:10.830 I've left a link to the e-book in the resources section 6 00:00:10.830 --> 00:00:12.770 of this lecture so that you can read 7 00:00:12.770 --> 00:00:16.163 how to use HeidiSQL instead if you prefer. 8 00:00:17.210 --> 00:00:18.990 So the first thing to do is navigate yourselves 9 00:00:18.990 --> 00:00:21.250 to the Browser tab here on the left, 10 00:00:21.250 --> 00:00:23.150 and then you get our SQL Browser. 11 00:00:23.150 --> 00:00:24.960 Here we can type out our queries. 12 00:00:24.960 --> 00:00:26.120 Also remember you can make this 13 00:00:26.120 --> 00:00:28.030 bigger if you want more space, 14 00:00:28.030 --> 00:00:31.170 and you always zoom in and out if you want. 15 00:00:31.170 --> 00:00:33.060 At this point, I'm gonna zoom in so that you guys 16 00:00:33.060 --> 00:00:35.290 can see a bit better what I'm typing, 17 00:00:35.290 --> 00:00:37.220 but of course it's gonna make things look a bit chunky. 18 00:00:37.220 --> 00:00:38.630 Don't worry too much about that. 19 00:00:38.630 --> 00:00:40.590 And also I've lost the side of my field, 20 00:00:40.590 --> 00:00:41.423 so I'm gonna make it 21 00:00:41.423 --> 00:00:44.160 a little bit smaller first, there we go. 22 00:00:44.160 --> 00:00:45.540 Okay, so now we're zoomed in, 23 00:00:45.540 --> 00:00:48.040 and we can see what's going on a bit more easily. 24 00:00:48.040 --> 00:00:50.900 So when we type in here, 25 00:00:50.900 --> 00:00:53.080 we're gonna be typing our SQL queries, 26 00:00:53.080 --> 00:00:55.170 and then we can press Execute to run them. 27 00:00:55.170 --> 00:00:56.270 Whenever we run queries, 28 00:00:56.270 --> 00:00:58.940 we'll get this Previous query here that we can use 29 00:00:58.940 --> 00:01:01.390 if we want to go back to a previous query 30 00:01:01.390 --> 00:01:02.630 that we've typed before, 31 00:01:02.630 --> 00:01:03.810 and we've got Table queries 32 00:01:03.810 --> 00:01:06.230 as well that will be helpful later on. 33 00:01:06.230 --> 00:01:07.730 We don't have any tables at the moment though, 34 00:01:07.730 --> 00:01:09.590 so we're going to create one. 35 00:01:09.590 --> 00:01:12.380 We're gonna do, for example, create table. 36 00:01:12.380 --> 00:01:13.760 We're gonna called it users, 37 00:01:13.760 --> 00:01:16.440 and the ID's gonna be integer primary key, 38 00:01:16.440 --> 00:01:18.690 and the name's gonna be text. 39 00:01:18.690 --> 00:01:21.000 So here we've got a perfectly valid query. 40 00:01:21.000 --> 00:01:23.850 Notice that one of the drawbacks of ElephantSQL 41 00:01:23.850 --> 00:01:26.310 is that we don't get syntax highlighting, 42 00:01:26.310 --> 00:01:28.470 so it's not really clear when we 43 00:01:28.470 --> 00:01:29.750 make a mistake or something like that. 44 00:01:29.750 --> 00:01:31.520 That's one of the benefits of using 45 00:01:31.520 --> 00:01:34.440 a local database viewer like HeidiSQL. 46 00:01:34.440 --> 00:01:35.630 But for now, this works, 47 00:01:35.630 --> 00:01:37.440 so we're gonna press Execute, 48 00:01:37.440 --> 00:01:39.470 and that is going to create our table, 49 00:01:39.470 --> 00:01:41.220 nothing is returned though. 50 00:01:41.220 --> 00:01:43.040 Then we can insert some data. 51 00:01:43.040 --> 00:01:48.040 For example, insert into users the values one and Bob Smith. 52 00:01:50.170 --> 00:01:51.140 We can execute this, 53 00:01:51.140 --> 00:01:53.540 and again we won't get any rows returned. 54 00:01:53.540 --> 00:01:55.950 Now, you can see that we still get no tables here. 55 00:01:55.950 --> 00:01:57.400 We can refresh the page, 56 00:01:57.400 --> 00:01:59.510 and that is now going to change 57 00:01:59.510 --> 00:02:01.580 and allow us to get this table. 58 00:02:01.580 --> 00:02:02.530 If we click on it, 59 00:02:02.530 --> 00:02:06.700 we get this select star from public.users, 60 00:02:06.700 --> 00:02:08.700 and we get only 100 rows. 61 00:02:08.700 --> 00:02:12.040 Notice that here is PostgreS we've got public.users, 62 00:02:12.040 --> 00:02:14.100 and they are inside quotation marks. 63 00:02:14.100 --> 00:02:16.770 This is not necessary, we can remove the quotation marks, 64 00:02:16.770 --> 00:02:18.920 and it still works in the same way. 65 00:02:18.920 --> 00:02:21.060 However, it is convention to put 66 00:02:21.060 --> 00:02:23.840 quotation marks around table names, 67 00:02:23.840 --> 00:02:25.520 and that's double quotation marks. 68 00:02:25.520 --> 00:02:28.140 If you use single quotation marks, that is for strings, 69 00:02:28.140 --> 00:02:29.990 and we don't wanna be using that. 70 00:02:29.990 --> 00:02:32.610 Also in PostgreS, we've got another layer 71 00:02:32.610 --> 00:02:34.350 above the table which is used 72 00:02:34.350 --> 00:02:37.160 to group together multiple tables that are related 73 00:02:37.160 --> 00:02:39.060 and maybe a few other things as well, 74 00:02:39.060 --> 00:02:42.660 and those are called schemas, and that is what public is. 75 00:02:42.660 --> 00:02:44.850 By default, when you create a new PostgreS table, 76 00:02:44.850 --> 00:02:47.830 you have one schema called public, 77 00:02:47.830 --> 00:02:49.970 and that's the one where we create things 78 00:02:49.970 --> 00:02:51.640 by default into as well. 79 00:02:51.640 --> 00:02:53.950 So now we have created the users table 80 00:02:53.950 --> 00:02:56.060 inside the public schema. 81 00:02:56.060 --> 00:02:59.260 If we run this, we get back our data. 82 00:02:59.260 --> 00:03:01.980 As you can see, we get the ID and the name. 83 00:03:01.980 --> 00:03:04.630 Now this query here is saved into our previous queries. 84 00:03:04.630 --> 00:03:06.370 You can see that I've got a few others there as well, 85 00:03:06.370 --> 00:03:08.600 but you can reaccess that one if you want. 86 00:03:08.600 --> 00:03:10.610 Notice I forget the semicolon at the end, 87 00:03:10.610 --> 00:03:12.460 but that's not necessary unless you're 88 00:03:12.460 --> 00:03:14.910 running multiple queries at once. 89 00:03:14.910 --> 00:03:16.510 Also, I'll repeat at this point that you 90 00:03:16.510 --> 00:03:18.590 can always access any tables, 91 00:03:18.590 --> 00:03:21.000 and you can create tables, and select from tables, 92 00:03:21.000 --> 00:03:24.540 and all that stuff without declaring the schema. 93 00:03:24.540 --> 00:03:26.410 For example, just saying users, 94 00:03:26.410 --> 00:03:28.770 or indeed just saying users 95 00:03:28.770 --> 00:03:30.880 without the quotation marks if you prefer. 96 00:03:30.880 --> 00:03:32.730 And whenever you do that, 97 00:03:32.730 --> 00:03:35.960 whenever you access any table without declaring the schema, 98 00:03:35.960 --> 00:03:38.630 that is going to be accessing the public schema. 99 00:03:38.630 --> 00:03:41.000 You can of course put public if you want, 100 00:03:41.000 --> 00:03:42.860 and that's also totally fine. 101 00:03:42.860 --> 00:03:44.600 Later on when you create new schemas, 102 00:03:44.600 --> 00:03:47.420 if you ever do, which I have never had the need to do, 103 00:03:47.420 --> 00:03:49.330 then you will put your schema there. 104 00:03:49.330 --> 00:03:51.460 All right, now that we can interact with our database 105 00:03:51.460 --> 00:03:53.000 and we've put some data in here, 106 00:03:53.000 --> 00:03:55.540 let's learn how to do it using Python. 107 00:03:55.540 --> 00:03:57.103 I'll see you in the next video.