1
00:00:00,150 --> 00:00:00,770
Okay.

2
00:00:00,780 --> 00:00:03,770
We have done a lot of groundwork talking about tables.

3
00:00:03,780 --> 00:00:07,020
Let's actually see the syntax to create a new table.

4
00:00:07,410 --> 00:00:14,730
So to create a new table, we specify a table name, but we also specify all the column names and their

5
00:00:14,730 --> 00:00:16,110
corresponding data types.

6
00:00:16,350 --> 00:00:22,590
So we run create table that's always create table, and then the name of our table and then parentheses

7
00:00:22,590 --> 00:00:24,450
and then column name data type.

8
00:00:24,450 --> 00:00:26,130
So that could look like this.

9
00:00:26,160 --> 00:00:27,780
Create table cats.

10
00:00:27,780 --> 00:00:30,810
Every cat has a name column that is text.

11
00:00:30,810 --> 00:00:36,300
It's a var char 100 length maximum and age is an integer.

12
00:00:36,630 --> 00:00:38,250
Those are the only two types we've covered.

13
00:00:38,250 --> 00:00:40,290
So we're going to use those two types.

14
00:00:40,410 --> 00:00:44,010
Now, as far as formatting, it doesn't have to go on separate lines like I have it here.

15
00:00:44,010 --> 00:00:48,780
It's just a nightmare to show you on slides if it's one massive line of text.

16
00:00:48,780 --> 00:00:52,650
But remember that my SQL doesn't care about new lines or anything.

17
00:00:52,650 --> 00:00:57,960
Well, it cares about the characters we type and it cares about spaces between things, but it doesn't

18
00:00:57,960 --> 00:01:03,750
care if I hit return and I have new lines here, and that can make it a lot easier to enter your information.

19
00:01:03,960 --> 00:01:09,300
So we also need to separate our different columns that we are declaring with commas.

20
00:01:09,300 --> 00:01:12,780
And then of course, we terminate the whole statement with a semicolon.

21
00:01:13,020 --> 00:01:17,760
So let me show it first in the command line or in the MySQL client here.

22
00:01:17,760 --> 00:01:21,780
What database am I in t shop?

23
00:01:22,050 --> 00:01:24,600
So let's show databases.

24
00:01:24,990 --> 00:01:27,690
Let's switch to let's make a new database.

25
00:01:27,690 --> 00:01:30,090
How about that create database.

26
00:01:30,450 --> 00:01:32,340
Let's call this one pet shop.

27
00:01:33,300 --> 00:01:33,610
Okay.

28
00:01:33,750 --> 00:01:34,860
And then use it.

29
00:01:36,220 --> 00:01:36,850
Okay.

30
00:01:36,880 --> 00:01:39,640
And now what I'm going to do is create my first table.

31
00:01:39,640 --> 00:01:44,620
Let's just do what we have on the slides here, or maybe slightly tweak it, but we'll create a table

32
00:01:44,620 --> 00:01:45,330
for cats.

33
00:01:45,340 --> 00:01:48,010
It's a pet shop, which I feel bad.

34
00:01:48,010 --> 00:01:50,860
It should be a shelter, really, but I don't want to change it now.

35
00:01:50,860 --> 00:01:53,650
So create table once again.

36
00:01:53,650 --> 00:01:56,520
That doesn't have to be capitalized, but it helps it stand out.

37
00:01:56,530 --> 00:02:01,510
I'm going to call this cat and then open my parentheses here or on the next line.

38
00:02:01,510 --> 00:02:02,950
It really doesn't matter.

39
00:02:03,250 --> 00:02:04,540
I'll just do it right there.

40
00:02:04,540 --> 00:02:12,100
And then my first row and my first column rather, is going to be called Name, which is a var char.

41
00:02:14,020 --> 00:02:17,780
And do we need a limit of 100 characters?

42
00:02:17,800 --> 00:02:21,040
Maybe a cat's name has to be 50 characters or smaller.

43
00:02:21,220 --> 00:02:21,700
Sure.

44
00:02:21,880 --> 00:02:23,050
And then a comma.

45
00:02:23,410 --> 00:02:25,030
And then I'll just hit enter again.

46
00:02:25,030 --> 00:02:27,970
And on this line I will do age.

47
00:02:27,970 --> 00:02:33,100
It's an int, so let's just leave it at that and then close my parentheses and add my semicolon.

48
00:02:33,850 --> 00:02:35,350
And it looks like it worked.

49
00:02:35,470 --> 00:02:36,580
How do we know?

50
00:02:36,610 --> 00:02:39,430
Well, there's a couple of ways in this video.

51
00:02:39,460 --> 00:02:47,980
The one that I'll show you is if we just go over to one of our tools like DB Gates or my SQL workbench,

52
00:02:47,980 --> 00:02:54,280
if I refresh the connection, we see Pet Shop and within Pet shop we should see.

53
00:02:54,640 --> 00:02:55,480
Hello.

54
00:02:55,510 --> 00:02:56,350
There we go.

55
00:02:56,560 --> 00:02:58,750
The cat's table exists.

56
00:02:58,750 --> 00:02:59,860
Same thing over here.

57
00:02:59,860 --> 00:03:04,540
If I refresh my view here, we see there's a pet shop database.

58
00:03:04,540 --> 00:03:07,870
It has a table called Cats and there it is.

59
00:03:07,870 --> 00:03:08,800
That's cats.

60
00:03:09,040 --> 00:03:11,680
Now, that's kind of a janky way of verifying that it worked.

61
00:03:11,680 --> 00:03:15,580
But in the next video, I'll show you some other commands that we can run from the command line.

62
00:03:16,000 --> 00:03:18,700
Now, we can also make cats or any other table.

63
00:03:18,700 --> 00:03:24,250
Let's make a dog's table from within one of our SQL graphical interfaces.

64
00:03:24,250 --> 00:03:25,720
So it's the same process.

65
00:03:25,720 --> 00:03:28,420
You make sure you're using the correct database.

66
00:03:28,420 --> 00:03:31,060
And then let's close this.

67
00:03:31,630 --> 00:03:37,000
I will double click on the pet shop, click my plus sign and write my script that will do something

68
00:03:37,000 --> 00:03:39,940
like create table dogs.

69
00:03:40,270 --> 00:03:41,830
Let's just do dogs this time.

70
00:03:42,550 --> 00:03:45,190
And it's going to be the same sort of pattern.

71
00:03:45,190 --> 00:03:47,980
I'll indent if that makes it a little nicer to look at.

72
00:03:47,980 --> 00:03:51,490
So name will be once again a var char.

73
00:03:51,610 --> 00:03:54,490
Let's do 50 characters age.

74
00:03:54,490 --> 00:03:56,170
Let's do breed for dogs.

75
00:03:56,170 --> 00:04:02,500
Breed will be a our car of 50 characters as well and age is an integer.

76
00:04:02,500 --> 00:04:06,870
You can write it out like that or just do int and then I have my semicolon.

77
00:04:07,210 --> 00:04:07,770
Okay.

78
00:04:07,780 --> 00:04:09,640
And then if I click execute here.

79
00:04:10,490 --> 00:04:13,880
That should have inserted a new table for me into pet shop.

80
00:04:13,880 --> 00:04:17,899
And that table, if I refresh down here, is called Dogs.

81
00:04:17,899 --> 00:04:19,010
We see it over there.

82
00:04:19,010 --> 00:04:22,280
Same thing in workbench, my SQL workbench.

83
00:04:22,280 --> 00:04:27,110
It's really hard to see over here with that tiny little text, but there is now a dogs table.

84
00:04:27,530 --> 00:04:29,930
Okay, so that's the basic syntax.

85
00:04:29,930 --> 00:04:32,690
That pattern you'll see all over the place.

86
00:04:32,690 --> 00:04:38,030
You use it all the time, every time you need to make a table, create table the name of the table.

87
00:04:38,030 --> 00:04:42,200
You want to come up with a name that makes sense, of course, And usually it's plural sized, but there's

88
00:04:42,200 --> 00:04:43,610
no rule that it has to be.

89
00:04:43,910 --> 00:04:45,260
And then parentheses.

90
00:04:45,260 --> 00:04:50,120
And then our column names with their corresponding data types, separate them by a space and then separate

91
00:04:50,120 --> 00:04:53,900
each column using a comma, terminate it with a semicolon.

92
00:04:53,900 --> 00:04:55,880
So we'll get a lot of practice creating tables.

93
00:04:55,880 --> 00:04:57,080
But that's the basics.