1
00:00:02,500 --> 00:00:04,130
Now to create a new table,

2
00:00:04,130 --> 00:00:06,510
we can click on tables here,

3
00:00:06,510 --> 00:00:09,793
right click on that and choose, create table.

4
00:00:10,730 --> 00:00:14,130
Alternatively, again, with that database selected,

5
00:00:14,130 --> 00:00:17,470
we can also click on create new table up here

6
00:00:17,470 --> 00:00:20,150
and this screen will open up.

7
00:00:20,150 --> 00:00:22,700
If you get an error that no schema was selected,

8
00:00:22,700 --> 00:00:25,830
you might want to double click onto your schema first

9
00:00:26,800 --> 00:00:31,330
and here we can now create a new table in that database.

10
00:00:31,330 --> 00:00:35,100
Now, a table is a bit more complex than the database itself

11
00:00:35,100 --> 00:00:37,940
because now we have to define the columns

12
00:00:37,940 --> 00:00:39,780
that we wanna have in there.

13
00:00:39,780 --> 00:00:44,410
Because remember in relational database management systems,

14
00:00:44,410 --> 00:00:48,340
it's all about having clear schemas and structures

15
00:00:48,340 --> 00:00:50,630
in your database tables.

16
00:00:50,630 --> 00:00:53,640
So that it's very clear, which fields,

17
00:00:53,640 --> 00:00:57,690
every record that's stored in this table should have.

18
00:00:57,690 --> 00:01:02,150
So if it should have some texts or some numbers,

19
00:01:02,150 --> 00:01:05,703
so you have to clearly define your schema for a table here,

20
00:01:06,750 --> 00:01:10,020
but it actually all starts with a name.

21
00:01:10,020 --> 00:01:12,650
And here I will now name this restaurants

22
00:01:14,360 --> 00:01:16,380
regarding the naming convention.

23
00:01:16,380 --> 00:01:18,430
It's generally up to you,

24
00:01:18,430 --> 00:01:21,840
you wanna avoid white spaces here though

25
00:01:21,840 --> 00:01:25,690
instead the convention is to name your databases

26
00:01:25,690 --> 00:01:29,550
and also your tables, all lower case.

27
00:01:29,550 --> 00:01:31,410
And then regarding the tables,

28
00:01:31,410 --> 00:01:34,900
it is quite common to choose to plural form here.

29
00:01:34,900 --> 00:01:37,790
And since we store a bunch of a restaurant items

30
00:01:37,790 --> 00:01:40,670
in that table, we can choose the restaurants

31
00:01:40,670 --> 00:01:42,550
as a table name.

32
00:01:42,550 --> 00:01:45,950
You could all just name it, restaurant, whatever you want,

33
00:01:45,950 --> 00:01:49,150
and you will see different approaches out there in the wild.

34
00:01:49,150 --> 00:01:51,530
I'm a fan of using the plural form here

35
00:01:51,530 --> 00:01:52,953
and name it restaurants.

36
00:01:54,170 --> 00:01:56,550
If your table named would be a name that consists

37
00:01:56,550 --> 00:01:58,350
of multiple words actually

38
00:01:58,350 --> 00:02:01,970
you would separate them with an underscore between

39
00:02:01,970 --> 00:02:03,663
don't use white space here.

40
00:02:04,930 --> 00:02:06,350
So now that's my name,

41
00:02:06,350 --> 00:02:09,259
again, we can choose how characters should be stored

42
00:02:09,259 --> 00:02:11,260
and we can keep the defaults here.

43
00:02:11,260 --> 00:02:14,890
We wanna keep that engine here, which is kind of the,

44
00:02:14,890 --> 00:02:16,920
the storage engine under the hood

45
00:02:16,920 --> 00:02:20,850
that will be used by the SQL server for storing the data

46
00:02:20,850 --> 00:02:24,730
that will end up in the table on actual files,

47
00:02:24,730 --> 00:02:28,110
because remember a database still in the end stores,

48
00:02:28,110 --> 00:02:29,430
data and files,

49
00:02:29,430 --> 00:02:31,900
but it handles that file access for you

50
00:02:31,900 --> 00:02:34,000
in a very efficient way.

51
00:02:34,000 --> 00:02:37,050
And there are different storage engines that can be utilized

52
00:02:37,050 --> 00:02:40,010
for that, but InnoDB is a great engine

53
00:02:40,010 --> 00:02:42,630
and unless you really know what you're doing,

54
00:02:42,630 --> 00:02:44,810
you should stick to it.

55
00:02:44,810 --> 00:02:46,150
Now we don't need a comment,

56
00:02:46,150 --> 00:02:50,640
but instead now here in this area, we can add our columns.

57
00:02:50,640 --> 00:02:54,631
We can define the schema, the structure of this table,

58
00:02:54,631 --> 00:02:56,390
and we can add a first column.

59
00:02:56,390 --> 00:03:00,630
So a first field that has to set by every record

60
00:03:00,630 --> 00:03:03,630
that's added to this table by clicking in here,

61
00:03:03,630 --> 00:03:05,140
double clicking in here,

62
00:03:05,140 --> 00:03:09,220
and then we can add a new column and it is quite normal

63
00:03:09,220 --> 00:03:12,940
that you want to have some unique identifier

64
00:03:12,940 --> 00:03:16,400
for every record that will end up in that table.

65
00:03:16,400 --> 00:03:19,640
And very often data's called ID.

66
00:03:19,640 --> 00:03:21,060
So that's not a must do,

67
00:03:21,060 --> 00:03:24,730
but it's very common that you have a column called ID

68
00:03:24,730 --> 00:03:26,323
in your database table.

69
00:03:27,950 --> 00:03:30,490
Now, the next thing we have to choose

70
00:03:30,490 --> 00:03:34,190
after choosing this name is the data type.

71
00:03:34,190 --> 00:03:36,090
And actually here, if you click in there,

72
00:03:36,090 --> 00:03:39,843
you got a drop down with a lot of options.

73
00:03:39,843 --> 00:03:44,843
MySQL or SQL in general supports a broad variety

74
00:03:45,160 --> 00:03:48,560
of data types that can be stored in a database.

75
00:03:48,560 --> 00:03:51,560
There are some exotic ones which you will rarely use

76
00:03:51,560 --> 00:03:54,890
and then there are a couple of very common ones.

77
00:03:54,890 --> 00:03:58,030
For example, INT is used to store numbers

78
00:03:58,030 --> 00:03:59,893
without decimal places,

79
00:04:00,770 --> 00:04:05,720
Varchar would be used for storing texts that's not too long.

80
00:04:05,720 --> 00:04:09,913
So short pieces of texts like a name or an address.

81
00:04:10,950 --> 00:04:15,950
Decimal, can be used for storing data with decimal places

82
00:04:16,040 --> 00:04:19,440
where you need a high amount of precision, for example,

83
00:04:19,440 --> 00:04:22,053
for storing prices or anything like that.

84
00:04:23,041 --> 00:04:26,800
Datetimes stores a date and a time as the name suggests

85
00:04:26,800 --> 00:04:30,710
and then I'm not going to go through all these data types.

86
00:04:30,710 --> 00:04:33,060
Again, the official docs or a full course

87
00:04:33,060 --> 00:04:35,080
are the place to go for that,

88
00:04:35,080 --> 00:04:37,040
but a couple of other interesting types

89
00:04:37,040 --> 00:04:39,800
that I wanna mention briefly are DATE,

90
00:04:39,800 --> 00:04:43,853
which is used to just store a date without a time portion,

91
00:04:44,730 --> 00:04:49,730
then also Float to store a number with decimal places

92
00:04:49,790 --> 00:04:52,920
that does not need to be too precise.

93
00:04:52,920 --> 00:04:56,600
So where you don't need a high amount of precision

94
00:04:56,600 --> 00:04:59,350
for these decimal places.

95
00:04:59,350 --> 00:05:00,940
And then we, for example,

96
00:05:00,940 --> 00:05:04,890
also have Boolean in case you wanna store true or false.

97
00:05:04,890 --> 00:05:07,890
So a yes or no answer in one of the fields

98
00:05:07,890 --> 00:05:09,363
of your database table.

99
00:05:11,030 --> 00:05:14,340
Now, here I'll stick to INT because the ID

100
00:05:14,340 --> 00:05:18,090
which I wanna assign to every record should be a number

101
00:05:18,090 --> 00:05:20,210
one, two, three and so on

102
00:05:20,210 --> 00:05:22,533
that is different for every record.

103
00:05:23,760 --> 00:05:26,740
Now this should also be the main way

104
00:05:26,740 --> 00:05:30,920
of identifying entries in the restaurants table.

105
00:05:30,920 --> 00:05:35,110
So each restaurant should have a unique ID.

106
00:05:35,110 --> 00:05:37,580
The names might be duplicate,

107
00:05:37,580 --> 00:05:40,250
there could be two restaurants with the same name

108
00:05:40,250 --> 00:05:41,640
in different locations,

109
00:05:41,640 --> 00:05:44,500
but each restaurant should have a unique ID

110
00:05:44,500 --> 00:05:47,720
and this unique identification criteria,

111
00:05:47,720 --> 00:05:50,850
which you need for every database table,

112
00:05:50,850 --> 00:05:54,300
every database table needs at least one field,

113
00:05:54,300 --> 00:05:57,040
one column that holds unique values,

114
00:05:57,040 --> 00:06:00,200
which you can use to clearly identify your records.

115
00:06:00,200 --> 00:06:03,610
This kind of field is called a Primary Key.

116
00:06:03,610 --> 00:06:08,610
And that's why we have this checkbox here in this PK column.

117
00:06:09,790 --> 00:06:11,580
This makes this a Primary Key.

118
00:06:11,580 --> 00:06:14,880
You can also see this down there in the bottom right.

119
00:06:14,880 --> 00:06:18,800
And that simply means that automatically MySQL

120
00:06:18,800 --> 00:06:22,840
will ensure that you cant store duplicate values

121
00:06:22,840 --> 00:06:24,090
in that ID field.

122
00:06:24,090 --> 00:06:29,020
So if you try to store an ID that already was stored before

123
00:06:29,020 --> 00:06:30,350
you would get an error,

124
00:06:30,350 --> 00:06:33,408
if you already have a restaurant with the ID for,

125
00:06:33,408 --> 00:06:36,123
and you would try to store another restaurant with the ID

126
00:06:36,123 --> 00:06:37,780
for you would get an error

127
00:06:38,730 --> 00:06:42,520
and you cannot also check this AI box here

128
00:06:42,520 --> 00:06:44,900
to turn on auto incrementing,

129
00:06:44,900 --> 00:06:47,790
which means that whenever you add a new record

130
00:06:47,790 --> 00:06:49,530
to that restaurant's table,

131
00:06:49,530 --> 00:06:52,990
you don't need to set the ID on your own,

132
00:06:52,990 --> 00:06:54,470
which would mean that you, first of all,

133
00:06:54,470 --> 00:06:57,610
have to look at all the other entries to find out which IDs

134
00:06:57,610 --> 00:06:59,070
are still available,

135
00:06:59,070 --> 00:07:01,810
but instead, whenever you store a new entry

136
00:07:01,810 --> 00:07:03,210
in the restaurants table,

137
00:07:03,210 --> 00:07:07,040
if AI is checked here, if Auto-Increment is checked,

138
00:07:07,040 --> 00:07:11,550
then MySQL this server will automatically

139
00:07:11,550 --> 00:07:16,550
assign a new unique ID to be added entry.

140
00:07:16,770 --> 00:07:20,190
And it will simply take the ID assigned

141
00:07:20,190 --> 00:07:23,430
to the previous entry and incremented by one,

142
00:07:23,430 --> 00:07:25,280
so add one to it.

143
00:07:25,280 --> 00:07:27,660
And that's why I'm using INT as a data type,

144
00:07:27,660 --> 00:07:30,800
because I wanna use a number without decimal places

145
00:07:30,800 --> 00:07:33,560
as an ID so that it can be incremented

146
00:07:33,560 --> 00:07:35,233
for every new record.

147
00:07:36,670 --> 00:07:40,310
I also have an NN checked, which stands for not null,

148
00:07:40,310 --> 00:07:44,290
which simply means that this field must always be set.

149
00:07:44,290 --> 00:07:47,050
You are not allowed to add an entry

150
00:07:47,050 --> 00:07:50,263
into this restaurant's table where you don't have an ID.

151
00:07:52,080 --> 00:07:55,170
Now we don't need to check the other options here.

152
00:07:55,170 --> 00:07:57,720
Unique sounds like you need to check it

153
00:07:57,720 --> 00:08:00,450
and you can check it but if something is set

154
00:08:00,450 --> 00:08:03,960
as a Primary Key, it's automatically unique.

155
00:08:03,960 --> 00:08:06,360
So you don't have to check unique

156
00:08:06,360 --> 00:08:08,753
if you already set it as a Primary Key,

157
00:08:09,930 --> 00:08:12,640
we also don't need the other options and therefore,

158
00:08:12,640 --> 00:08:14,640
we now have our first column.

159
00:08:14,640 --> 00:08:17,020
Now we can add a second column by simply clicking

160
00:08:17,020 --> 00:08:18,350
in that second row here.

161
00:08:18,350 --> 00:08:21,930
When we define our schema and then, well,

162
00:08:21,930 --> 00:08:23,960
it comes down to what else we wanna store.

163
00:08:23,960 --> 00:08:24,793
And for example,

164
00:08:24,793 --> 00:08:27,610
we might wanna store the name of a restaurant.

165
00:08:27,610 --> 00:08:30,860
So I'll give this column a name of name

166
00:08:31,940 --> 00:08:34,720
and the data type here in deed should be some text

167
00:08:34,720 --> 00:08:37,240
and Varchar is the right solution here

168
00:08:37,240 --> 00:08:40,919
if it's not super long text for a super long text,

169
00:08:40,919 --> 00:08:45,920
you would have the text type here

170
00:08:45,960 --> 00:08:49,070
or even long text if it's a very long text,

171
00:08:49,070 --> 00:08:52,330
but here I don't want to have an overly long text,

172
00:08:52,330 --> 00:08:56,710
just a short piece of text and therefore I'll use Varchar

173
00:08:56,710 --> 00:08:58,070
and between those parentheses,

174
00:08:58,070 --> 00:09:00,930
you can then define the maximum amount of characters

175
00:09:00,930 --> 00:09:02,220
that are allowed here.

176
00:09:02,220 --> 00:09:05,773
And I'll choose 255 here, which is a common value.

177
00:09:08,630 --> 00:09:12,210
So therefore for all set this, and I'll also check not null

178
00:09:12,210 --> 00:09:15,870
because I don't want an empty value here.

179
00:09:15,870 --> 00:09:17,853
You have to provide some name.

180
00:09:18,780 --> 00:09:21,760
I don't check anything else because duplicate names

181
00:09:21,760 --> 00:09:22,600
are allowed.

182
00:09:22,600 --> 00:09:25,690
It's not my Primary Key instead, that's my ID

183
00:09:25,690 --> 00:09:29,503
and it's also not auto incrementing or anything like that.

184
00:09:30,560 --> 00:09:32,730
Now we might want to store more data

185
00:09:33,670 --> 00:09:36,030
here I'll just add one more field

186
00:09:36,030 --> 00:09:38,790
and that will be the type of restaurant

187
00:09:38,790 --> 00:09:41,540
and I'll also set this to be text.

188
00:09:41,540 --> 00:09:46,540
Also use Varchar 255 here, and also make this not null.

189
00:09:46,700 --> 00:09:50,583
So that would be something like Italian or Indian or German.

190
00:09:51,690 --> 00:09:54,360
And with that, I have these three columns,

191
00:09:54,360 --> 00:09:57,320
this is not the final scheme I want.

192
00:09:57,320 --> 00:10:00,800
Instead we will soon plan this schema in more details,

193
00:10:00,800 --> 00:10:02,900
here we're just getting started with this

194
00:10:02,900 --> 00:10:04,710
MySQL Workbench tool

195
00:10:04,710 --> 00:10:07,940
and with these SQL language in general.

196
00:10:07,940 --> 00:10:09,990
So we will come back to this later

197
00:10:09,990 --> 00:10:12,610
and create more elaborate tables

198
00:10:12,610 --> 00:10:15,030
once we have our final schema in mind,

199
00:10:15,030 --> 00:10:16,150
but to get started,

200
00:10:16,150 --> 00:10:18,640
this doesn't look too bad and therefore now

201
00:10:18,640 --> 00:10:20,090
with that all defined,

202
00:10:20,090 --> 00:10:24,520
we can leave all the rest and click on apply here.

203
00:10:24,520 --> 00:10:27,670
And again, we then see the SQL statement

204
00:10:27,670 --> 00:10:29,540
that will be executed.

205
00:10:29,540 --> 00:10:31,480
And just to make this very clear,

206
00:10:31,480 --> 00:10:34,420
you don't need to use this graphical user interface,

207
00:10:34,420 --> 00:10:35,920
which we have used here.

208
00:10:35,920 --> 00:10:37,073
You don't need to do that,

209
00:10:37,073 --> 00:10:41,580
neither for creating a database nor for creating a table

210
00:10:41,580 --> 00:10:44,700
once you know SQL, once you know it really well,

211
00:10:44,700 --> 00:10:47,660
you can of course, write those statements on your own

212
00:10:47,660 --> 00:10:49,440
and then just execute them

213
00:10:49,440 --> 00:10:52,540
either through a tool like this, or in other ways,

214
00:10:52,540 --> 00:10:54,630
like directly connecting to a database

215
00:10:54,630 --> 00:10:56,190
through the command line.

216
00:10:56,190 --> 00:10:57,800
But that's all a bit more advanced

217
00:10:57,800 --> 00:11:00,040
and something not the main goal of this course.

218
00:11:00,040 --> 00:11:02,480
Therefore this graphical user interface,

219
00:11:02,480 --> 00:11:05,850
MySQL Workbench is of course perfect to get started

220
00:11:05,850 --> 00:11:08,440
with SQL and to get started with working

221
00:11:08,440 --> 00:11:10,170
with that database.

222
00:11:10,170 --> 00:11:11,003
So therefore here,

223
00:11:11,003 --> 00:11:15,280
we got a number of SQL statement that was generated for us

224
00:11:15,280 --> 00:11:19,410
and here we see that the keywords are a create table.

225
00:11:19,410 --> 00:11:22,680
Then we tell the server for which database

226
00:11:22,680 --> 00:11:24,410
we want to create a new table

227
00:11:24,410 --> 00:11:28,500
and then the table name separated by a dot.

228
00:11:28,500 --> 00:11:32,940
So it's database name dot table name

229
00:11:32,940 --> 00:11:35,940
again, we got those back ticks here around our names,

230
00:11:35,940 --> 00:11:38,990
but if there are no special characters in those names,

231
00:11:38,990 --> 00:11:41,800
you can actually omit these back ticks here,

232
00:11:41,800 --> 00:11:43,820
you don't need to have them here.

233
00:11:43,820 --> 00:11:45,860
So just to prove this I'll remove them

234
00:11:46,840 --> 00:11:49,200
and then between parentheses here,

235
00:11:49,200 --> 00:11:52,550
we have the different columns that we wanna define.

236
00:11:52,550 --> 00:11:55,370
The names are between those back ticks

237
00:11:55,370 --> 00:11:57,819
and again, they are not required here

238
00:11:57,819 --> 00:12:01,990
because there are no special characters in our names.

239
00:12:01,990 --> 00:12:05,890
If there would be, we would need these back ticks.

240
00:12:05,890 --> 00:12:09,440
And then we have to configuration for every column

241
00:12:09,440 --> 00:12:10,713
after the column name.

242
00:12:11,880 --> 00:12:15,830
And then the different columns are separated by commas.

243
00:12:15,830 --> 00:12:18,130
And here we can see that for the ID,

244
00:12:18,130 --> 00:12:19,570
it should be an indenture,

245
00:12:19,570 --> 00:12:23,960
not null and Auto-Increment, names should be a Varchar

246
00:12:23,960 --> 00:12:26,220
and not null and type as well.

247
00:12:26,220 --> 00:12:28,710
And then after defining all these columns,

248
00:12:28,710 --> 00:12:32,160
we also make it clear that the Primary Key off that table

249
00:12:32,160 --> 00:12:34,820
will be the ID column.

250
00:12:34,820 --> 00:12:37,110
And we can all to remove the back ticks here

251
00:12:38,400 --> 00:12:39,320
last but not least.

252
00:12:39,320 --> 00:12:42,280
The engine that should be used for this table is defined

253
00:12:42,280 --> 00:12:45,450
and if we now click apply, that's executed

254
00:12:45,450 --> 00:12:48,730
and if I click finish, now, if we expand tables here,

255
00:12:48,730 --> 00:12:50,640
we see our table.

256
00:12:50,640 --> 00:12:52,200
And if we expand that,

257
00:12:52,200 --> 00:12:55,620
we can also have a look at the columns that we defined here.

258
00:12:55,620 --> 00:12:56,453
And that's, by the way,

259
00:12:56,453 --> 00:13:00,110
just some graphical representation of our tables,

260
00:13:00,110 --> 00:13:02,650
which we can use here in MySQL Workbench

261
00:13:02,650 --> 00:13:06,023
to analyze our database and the tables we got in there.

262
00:13:07,790 --> 00:13:08,850
So now with that,

263
00:13:08,850 --> 00:13:11,310
we created our table in our database

264
00:13:11,310 --> 00:13:14,890
and we saw our first SQL statements.

265
00:13:14,890 --> 00:13:18,140
Now let's also dive into more SQL statements

266
00:13:18,140 --> 00:13:21,900
and add first pieces of data into this restaurant's table

267
00:13:21,900 --> 00:13:24,750
before we then take what we learned thus far

268
00:13:24,750 --> 00:13:27,390
and apply it to the main goal of this section

269
00:13:27,390 --> 00:13:30,260
to build the real schema and the real tables

270
00:13:30,260 --> 00:13:33,833
for this restaurants reviews application.