1 00:00:02,070 --> 00:00:04,340 So now we had a first look at 2 00:00:04,340 --> 00:00:06,410 inserting data and selecting data. 3 00:00:06,410 --> 00:00:07,550 What's of course missing, 4 00:00:07,550 --> 00:00:11,480 is that we can also update and delete data. 5 00:00:11,480 --> 00:00:14,480 And therefore that's what we'll have a look at now. 6 00:00:14,480 --> 00:00:18,250 I'll again create a new query here in a new tab, 7 00:00:18,250 --> 00:00:21,270 and we can update elements in a database table 8 00:00:21,270 --> 00:00:23,493 with the UPDATE command. 9 00:00:25,060 --> 00:00:28,510 Now with the UPDATE command, we first of all 10 00:00:28,510 --> 00:00:32,500 need to tell SQL or the MySQL server, 11 00:00:32,500 --> 00:00:36,760 which database table holds the value which we wanna update. 12 00:00:36,760 --> 00:00:39,310 In my case, that's of course the restaurants table. 13 00:00:40,890 --> 00:00:43,970 Then we use the SET keyword to make it clear 14 00:00:43,970 --> 00:00:47,700 which values or which columns in that table 15 00:00:47,700 --> 00:00:49,683 should be set to a new value. 16 00:00:50,570 --> 00:00:52,850 And for that I could, for example say, 17 00:00:52,850 --> 00:00:57,850 that I want to change the name to Web Dev Meals. 18 00:01:01,250 --> 00:01:03,300 I want to change the name of the restaurant 19 00:01:03,300 --> 00:01:07,463 with the name Web Dev Mealery, to Web Dev Meals. 20 00:01:08,760 --> 00:01:11,900 Now this instruction would set the name to that value 21 00:01:11,900 --> 00:01:16,290 for all the records in the database table. 22 00:01:16,290 --> 00:01:17,870 Sometimes that is what you want. 23 00:01:17,870 --> 00:01:21,860 But in this case here, I only wanna adjust the column value 24 00:01:21,860 --> 00:01:25,110 for a single record in this database table. 25 00:01:25,110 --> 00:01:28,430 I wanna adjust the name off the restaurant 26 00:01:28,430 --> 00:01:30,343 with the name Web Dev Mealery. 27 00:01:31,330 --> 00:01:33,000 And that's why we can also use 28 00:01:33,000 --> 00:01:36,870 the WHERE keyword here as well, to narrow down 29 00:01:36,870 --> 00:01:39,510 the amount of records that should be updated, 30 00:01:39,510 --> 00:01:41,590 because you can update all the records, 31 00:01:41,590 --> 00:01:44,210 a bunch of records, or a single record, 32 00:01:44,210 --> 00:01:46,083 whatever meets your condition here. 33 00:01:47,060 --> 00:01:49,200 In this case, I know I only wanna update 34 00:01:49,200 --> 00:01:51,400 the restaurant with the ID one, 35 00:01:51,400 --> 00:01:54,393 and therefore we can say where ID equals one, 36 00:01:55,750 --> 00:01:56,583 like this. 37 00:01:57,830 --> 00:02:01,590 If I now execute this it succeeds. 38 00:02:01,590 --> 00:02:03,870 And if I quickly run another statement here 39 00:02:03,870 --> 00:02:07,153 where I select all the values from restaurants, 40 00:02:08,889 --> 00:02:11,220 like this, we can see that now, indeed, 41 00:02:11,220 --> 00:02:14,220 this was changed to Web Dev Meals. 42 00:02:14,220 --> 00:02:16,323 So updating this value worked. 43 00:02:18,560 --> 00:02:21,400 Now what if we want to get rid of it altogether? 44 00:02:21,400 --> 00:02:24,010 Well, then we can write another query 45 00:02:24,010 --> 00:02:26,343 where we delete our record. 46 00:02:27,320 --> 00:02:31,420 For this we use the DELETE keyword, the DELETE command. 47 00:02:31,420 --> 00:02:33,460 And then we first of all need to add FROM 48 00:02:33,460 --> 00:02:37,370 and specify from which table we want to delete a value. 49 00:02:37,370 --> 00:02:39,493 For example, in this case, restaurants. 50 00:02:40,640 --> 00:02:44,300 Now, again, we also need to specify which records to delete. 51 00:02:44,300 --> 00:02:48,600 And that's again where we use WHERE to narrow down 52 00:02:48,600 --> 00:02:50,580 the records that should be deleted, 53 00:02:50,580 --> 00:02:53,400 because with DELETE, you can delete one record 54 00:02:53,400 --> 00:02:57,120 or multiple records depending on the condition you add here 55 00:02:57,120 --> 00:03:00,283 and how many records get selected by that condition. 56 00:03:01,290 --> 00:03:03,870 So if I know I wanna delete the restaurant 57 00:03:03,870 --> 00:03:06,383 with the ID one, I can say this. 58 00:03:07,440 --> 00:03:10,380 And here, as well as with the UPDATE command, 59 00:03:10,380 --> 00:03:13,760 you see why that ID thing is so important. 60 00:03:13,760 --> 00:03:18,760 Why having a unique identifier per record is so important. 61 00:03:19,100 --> 00:03:21,650 If we would want to delete the first restaurant 62 00:03:21,650 --> 00:03:25,240 with that name, we could also select it by name, 63 00:03:25,240 --> 00:03:27,520 but if we have a large amount of restaurants 64 00:03:27,520 --> 00:03:30,040 in our database, there is a high chance 65 00:03:30,040 --> 00:03:33,250 that we have multiple restaurants with the same name, 66 00:03:33,250 --> 00:03:35,080 especially for something like Pizza House 67 00:03:35,080 --> 00:03:36,560 or anything like that. 68 00:03:36,560 --> 00:03:39,930 That's why we want some unique identifier like the ID 69 00:03:39,930 --> 00:03:42,990 to make sure we really only update or delete 70 00:03:42,990 --> 00:03:45,273 the record we wanna delete or update. 71 00:03:46,320 --> 00:03:48,760 So therefore here, that's not a command we can execute 72 00:03:48,760 --> 00:03:50,693 to get rid of that first restaurant. 73 00:03:51,850 --> 00:03:55,610 It succeeded, and if I now select all my data again, 74 00:03:55,610 --> 00:03:58,870 and I run this query again, we indeed see that now 75 00:03:58,870 --> 00:04:01,615 we only have two restaurants left. 76 00:04:01,615 --> 00:04:05,150 Please note that the ID of the remaining restaurants 77 00:04:05,150 --> 00:04:06,960 did not change. 78 00:04:06,960 --> 00:04:08,470 And that hopefully makes a lot of sense. 79 00:04:08,470 --> 00:04:11,490 It would be very bad if you could delete one item 80 00:04:11,490 --> 00:04:15,280 and all the IDs of all the other records would change. 81 00:04:15,280 --> 00:04:17,490 You don't want to have randomness like this 82 00:04:17,490 --> 00:04:18,760 in your database. 83 00:04:18,760 --> 00:04:21,550 Your other records should not be affected 84 00:04:21,550 --> 00:04:24,170 just because you deleted a record. 85 00:04:24,170 --> 00:04:26,470 So these records were not affected 86 00:04:26,470 --> 00:04:28,943 but the first restaurant was deleted. 87 00:04:29,920 --> 00:04:32,010 And therefor now we had a first look 88 00:04:32,010 --> 00:04:36,210 at the basic CRUD functionality, at inserting, 89 00:04:36,210 --> 00:04:40,260 so creating, reading, updating, and deleting elements 90 00:04:40,260 --> 00:04:43,070 or records into our database tables. 91 00:04:43,070 --> 00:04:46,140 We also saw how we can create a database and a table 92 00:04:46,140 --> 00:04:47,790 in the first place. 93 00:04:47,790 --> 00:04:52,160 And we had a first glimpse at the SQL syntax 94 00:04:52,160 --> 00:04:55,990 and how we write these SQL instructions. 95 00:04:55,990 --> 00:04:59,290 It's definitely quite different from JavaScript. 96 00:04:59,290 --> 00:05:02,040 We don't have files with all those instructions, 97 00:05:02,040 --> 00:05:03,660 which we then execute. 98 00:05:03,660 --> 00:05:07,210 Instead, we can just execute these instructions on the fly 99 00:05:07,210 --> 00:05:10,530 whenever we wanna perform a certain action. 100 00:05:10,530 --> 00:05:13,670 But on the other hand, that makes a lot of sense hopefully, 101 00:05:13,670 --> 00:05:16,690 because whilst a web server is an ongoing thing 102 00:05:16,690 --> 00:05:19,980 that needs to react to different incoming requests, 103 00:05:19,980 --> 00:05:22,990 this here really are one-time actions 104 00:05:22,990 --> 00:05:26,630 that we wanna execute to perform a certain action. 105 00:05:26,630 --> 00:05:29,740 And this is how SQL generally works. 106 00:05:29,740 --> 00:05:32,670 Now with that, let's go back to this course goal, 107 00:05:32,670 --> 00:05:36,310 which we defined and let's plan the actual schema 108 00:05:36,310 --> 00:05:38,780 and the amount of tables we need 109 00:05:38,780 --> 00:05:41,920 to store the restaurant data and the reviews. 110 00:05:41,920 --> 00:05:44,130 And then let's implement that all 111 00:05:44,130 --> 00:05:46,453 with our newly gained SQL knowledge.