1 00:00:02,230 --> 00:00:04,550 So with everything set up, 2 00:00:04,550 --> 00:00:07,950 I'm back here in this MySQL Workbench 3 00:00:07,950 --> 00:00:10,883 connected to my running MySQL Server. 4 00:00:11,860 --> 00:00:13,810 As we learned in the last lecture, 5 00:00:13,810 --> 00:00:16,170 we now need a bunch of databases 6 00:00:16,170 --> 00:00:19,560 or at least one database to hold the tables 7 00:00:19,560 --> 00:00:22,620 which will then hold our data in the end. 8 00:00:22,620 --> 00:00:26,780 Now, don't forget our plan for this course section 9 00:00:26,780 --> 00:00:31,330 was to actually work on this restaurants use case here 10 00:00:31,330 --> 00:00:33,960 where we have a bunch of restaurants 11 00:00:33,960 --> 00:00:37,070 with the reviews that belong to it. 12 00:00:37,070 --> 00:00:40,810 Therefore, I would like to start by creating a table 13 00:00:40,810 --> 00:00:42,960 for the restaurants data. 14 00:00:42,960 --> 00:00:46,480 And therefore, we first of all need a database. 15 00:00:46,480 --> 00:00:48,540 Now, in MySQL Workbench, 16 00:00:48,540 --> 00:00:52,680 you can go to Schemas here in this tab. 17 00:00:52,680 --> 00:00:55,880 And then there, you can create your databases. 18 00:00:55,880 --> 00:01:00,080 They are also called schemas here in MySQL Workbench, 19 00:01:00,080 --> 00:01:02,810 but these are our databases. 20 00:01:02,810 --> 00:01:05,260 We actually got a database already, 21 00:01:05,260 --> 00:01:07,650 but that's an internal database 22 00:01:07,650 --> 00:01:10,770 that holds some settings for this MySQL Server. 23 00:01:10,770 --> 00:01:14,570 And therefore, we will not use this sys database, 24 00:01:14,570 --> 00:01:16,520 but instead, we can create a new one 25 00:01:16,520 --> 00:01:20,223 by right-clicking in there and choosing Create Schema. 26 00:01:21,580 --> 00:01:25,950 Alternatively, you can also go up here to this icon 27 00:01:25,950 --> 00:01:29,270 and click that to create a new schema. 28 00:01:29,270 --> 00:01:31,030 Now you can give this schema, 29 00:01:31,030 --> 00:01:33,570 so this database a name 30 00:01:33,570 --> 00:01:36,853 and I will name it restaurant_finder. 31 00:01:39,270 --> 00:01:42,580 The name is up to you, but we could be building an app, 32 00:01:42,580 --> 00:01:46,040 a website which may be is called restaurant_finder 33 00:01:46,040 --> 00:01:49,620 because it helps people find and rate restaurants. 34 00:01:49,620 --> 00:01:51,620 And therefore, I give the database 35 00:01:51,620 --> 00:01:54,610 that belongs to that website the same name, 36 00:01:54,610 --> 00:01:55,510 restaurant_finder. 37 00:01:57,050 --> 00:02:02,050 Now here, we can choose a char set and a collation. 38 00:02:02,220 --> 00:02:05,040 In the end, this will influence how characters, 39 00:02:05,040 --> 00:02:10,020 so how text will be stored and the defaults are fine here. 40 00:02:10,020 --> 00:02:11,640 If you wanna learn more about that, 41 00:02:11,640 --> 00:02:14,700 the official SQL docs or a complete SQL course 42 00:02:14,700 --> 00:02:15,740 is the way to go, 43 00:02:15,740 --> 00:02:18,230 but the defaults will make sure that we can store 44 00:02:18,230 --> 00:02:21,633 any kind of characters in our database tables. 45 00:02:22,470 --> 00:02:25,993 And therefore, all we have to do here is click Apply. 46 00:02:27,220 --> 00:02:29,530 Now, what you see on that next screen 47 00:02:29,530 --> 00:02:33,940 is actually the SQL query that will be executed 48 00:02:33,940 --> 00:02:37,320 to create this new database. 49 00:02:37,320 --> 00:02:39,730 So here it's called CREATE SCHEMA 50 00:02:39,730 --> 00:02:43,323 and then our name between those back ticks here. 51 00:02:44,220 --> 00:02:48,340 Now, actually, we could also remove those back ticks. 52 00:02:48,340 --> 00:02:49,770 That would still work. 53 00:02:49,770 --> 00:02:54,350 And instead of SCHEMA, we could also write CREATE DATABASE. 54 00:02:54,350 --> 00:02:56,650 That's also accepted. 55 00:02:56,650 --> 00:03:01,150 Now, speaking of accepted values or commands here, 56 00:03:01,150 --> 00:03:04,870 what you see here as mentioned is SQL syntax 57 00:03:04,870 --> 00:03:09,140 and SQL is all about issuing commands like this 58 00:03:09,140 --> 00:03:13,580 to our database server, to our MySQL Server in this case, 59 00:03:13,580 --> 00:03:16,870 so that the server is then able to execute these commands 60 00:03:16,870 --> 00:03:18,563 and do something based on them. 61 00:03:19,420 --> 00:03:21,910 Of course, for that, we have to follow certain rules 62 00:03:21,910 --> 00:03:24,750 and execute commands the server understands 63 00:03:24,750 --> 00:03:27,370 and there is a long list of built-in commands 64 00:03:27,370 --> 00:03:29,240 the server does understand. 65 00:03:29,240 --> 00:03:30,750 You can check the official docs 66 00:03:30,750 --> 00:03:33,210 if you wanna dive into all the supported commands, 67 00:03:33,210 --> 00:03:34,850 but don't do this right now. 68 00:03:34,850 --> 00:03:38,080 It's overwhelming and most commands are rarely used 69 00:03:38,080 --> 00:03:40,690 or are pure administration commands, 70 00:03:40,690 --> 00:03:43,200 but CREATE DATABASE is one of the commands 71 00:03:43,200 --> 00:03:45,270 the SQL Server understands, 72 00:03:45,270 --> 00:03:46,970 and it does what the name implies. 73 00:03:46,970 --> 00:03:49,593 It creates a database of a given name. 74 00:03:50,840 --> 00:03:54,920 Now, you can execute multiple SQL statements together. 75 00:03:54,920 --> 00:03:56,290 Here, we only have one, 76 00:03:56,290 --> 00:03:59,000 but we could add more in that box here 77 00:03:59,000 --> 00:04:02,050 and they are then separated by semicolons 78 00:04:02,050 --> 00:04:05,010 like you see it here at the end of the line. 79 00:04:05,010 --> 00:04:06,760 You don't need that semicolon 80 00:04:06,760 --> 00:04:10,000 if you only have one command that will be executed. 81 00:04:10,000 --> 00:04:13,060 But if you, for example, want to create a database 82 00:04:13,060 --> 00:04:14,670 and then in a second step 83 00:04:14,670 --> 00:04:17,600 already create a table in that database 84 00:04:17,600 --> 00:04:19,910 and you wanna execute that in one go, 85 00:04:19,910 --> 00:04:23,170 then you could put that table creation command 86 00:04:23,170 --> 00:04:26,803 below this command and separate them with semicolons. 87 00:04:27,650 --> 00:04:29,920 But here, we're going to create a table 88 00:04:29,920 --> 00:04:32,883 in a separate second step in a couple of seconds. 89 00:04:33,750 --> 00:04:35,500 So this is SQL syntax. 90 00:04:35,500 --> 00:04:39,420 Now, what's worth noting here is that CREATE and DATABASE 91 00:04:39,420 --> 00:04:42,340 are written all uppercase. 92 00:04:42,340 --> 00:04:44,570 Technically, that's not required. 93 00:04:44,570 --> 00:04:48,403 CREATE and DATABASE in this combination are keywords 94 00:04:48,403 --> 00:04:51,430 that will be understood by the SQL Server 95 00:04:51,430 --> 00:04:54,293 no matter if you write them uppercase or not, 96 00:04:55,200 --> 00:04:58,950 but it's a convention to use all caps notation here 97 00:04:58,950 --> 00:05:02,550 to make it clear for us humans and for other developers 98 00:05:02,550 --> 00:05:05,020 that these are reserved keywords 99 00:05:05,020 --> 00:05:07,700 so that these are built-in commands 100 00:05:07,700 --> 00:05:10,410 and that restaurant_finder in this case 101 00:05:10,410 --> 00:05:15,040 is the only flexible value that's up to us in this command. 102 00:05:15,040 --> 00:05:17,540 That's why the built-in keywords and commands 103 00:05:17,540 --> 00:05:21,520 are typically written all uppercase when working with SQL 104 00:05:22,500 --> 00:05:25,630 and that's then how the SQL language looks like. 105 00:05:25,630 --> 00:05:29,390 It's definitely different than what we had in JavaScript. 106 00:05:29,390 --> 00:05:33,420 It's also new that we just issue commands like that 107 00:05:33,420 --> 00:05:36,030 and we don't write a file full of code 108 00:05:36,030 --> 00:05:37,930 that is then executed, 109 00:05:37,930 --> 00:05:41,520 but it makes sense if you think about what we're doing. 110 00:05:41,520 --> 00:05:45,800 With our NodeJS code or our frontend code, 111 00:05:45,800 --> 00:05:48,050 we of course had to write code 112 00:05:48,050 --> 00:05:50,780 that can be evaluated by a browser 113 00:05:50,780 --> 00:05:53,190 or that can act as a web server, 114 00:05:53,190 --> 00:05:57,180 so code that can handle multiple incoming requests. 115 00:05:57,180 --> 00:06:01,240 Here, we have a one-time task. We wanna create a database. 116 00:06:01,240 --> 00:06:03,860 So that's just one thing that needs to be done. 117 00:06:03,860 --> 00:06:06,500 And therefore, we write query commands like this 118 00:06:06,500 --> 00:06:10,820 to issue these one-time tasks to the database server. 119 00:06:10,820 --> 00:06:14,380 And therefore, SQL always looks like this. 120 00:06:14,380 --> 00:06:17,410 There's a broad bandwidth of built-in commands, 121 00:06:17,410 --> 00:06:19,560 but we always issue them like this, 122 00:06:19,560 --> 00:06:22,070 possibly multiple commands at the same time, 123 00:06:22,070 --> 00:06:24,160 separated by semicolons, 124 00:06:24,160 --> 00:06:25,470 but we issue them like this 125 00:06:25,470 --> 00:06:27,840 so that the server can then execute them 126 00:06:27,840 --> 00:06:30,313 and do whatever we tell the server to do. 127 00:06:31,440 --> 00:06:33,390 And therefore, long story short, 128 00:06:33,390 --> 00:06:36,350 now we have this instruction defined here 129 00:06:36,350 --> 00:06:39,620 and we can now click on Apply here to execute it 130 00:06:39,620 --> 00:06:42,490 and send this command to the SQL Server 131 00:06:42,490 --> 00:06:44,490 so that on that SQL Server, 132 00:06:44,490 --> 00:06:47,140 that database will be created. 133 00:06:47,140 --> 00:06:48,700 And therefore here on the left, 134 00:06:48,700 --> 00:06:51,720 we now see this database here. 135 00:06:51,720 --> 00:06:55,210 And in that database, we now also have tables 136 00:06:55,210 --> 00:06:57,350 or at the moment we have no tables, 137 00:06:57,350 --> 00:07:00,500 but we have the capabilities to analyze this database 138 00:07:00,500 --> 00:07:02,743 and look at our tables once we have some. 139 00:07:03,730 --> 00:07:05,480 And speaking of that, 140 00:07:05,480 --> 00:07:08,300 that's therefore the next step I wanna dive in. 141 00:07:08,300 --> 00:07:10,623 I wanna create a new table.