WEBVTT 00:07.130 --> 00:10.040 I will call you back again to another lecture on MySQL. 00:10.070 --> 00:15.710 And in this lecture we go ahead and learn how to create a table in a database. 00:16.100 --> 00:19.820 Oh I saw over here on the database we created in the previous lecture. 00:19.820 --> 00:23.870 You can see we have no tables because the table is too empty. 00:23.870 --> 00:27.380 So if we refresh this there is no table read in here. 00:27.440 --> 00:32.630 Now to create a table you go ahead and say create and say table. 00:32.660 --> 00:34.460 Then you give the name of the table. 00:34.460 --> 00:38.960 So you are free to choose any name based on something related to what you are doing. 00:38.960 --> 00:43.640 So I have my school, so I want my table name to be students. 00:43.640 --> 00:50.600 And then I'll go ahead and add in the details I need to collect from the students. 00:50.600 --> 00:55.880 So I need to create ID because each of the students are going to have a unique ID. 00:55.880 --> 01:00.120 And this ID is very, very important in database Creation. 01:00.120 --> 01:01.710 So I'll go ahead and say it. 01:01.950 --> 01:09.300 Studies underscore ID and the ID is going to be integer numbers or integer data types. 01:09.300 --> 01:16.020 So it's going to be integer data types because you deal with the data types whenever you are creating 01:16.050 --> 01:16.800 a table. 01:16.800 --> 01:24.330 So that is an integer or is a decimal or is a character or dates and so on. 01:24.330 --> 01:27.570 So for id id is always integer. 01:27.570 --> 01:31.020 So it's going to be int and put a semicolon a comma. 01:31.020 --> 01:32.700 So put a comma at the end. 01:32.700 --> 01:35.730 And then I need the student's first name and last name. 01:35.730 --> 01:38.070 So first underscore name. 01:38.070 --> 01:39.540 Then this is a character. 01:39.540 --> 01:42.150 So I'll go ahead and say it is varchar. 01:42.180 --> 01:46.860 And now you give the number of characters you want might be 25. 01:46.860 --> 01:50.580 And at the end and then I need the last name. 01:50.580 --> 01:52.950 So it's also a character. 01:52.980 --> 01:56.220 That is why the data type is going to be varchar. 01:56.250 --> 02:00.400 So we comma at the end. 02:00.430 --> 02:02.440 Then I'm going to collect that GPA. 02:02.470 --> 02:03.790 So the GPA. 02:03.820 --> 02:05.860 So go ahead and make it simple like GPA. 02:05.980 --> 02:08.230 And the GPA is always a decimal. 02:08.230 --> 02:13.060 It's going to be 4.13.52.0 and so on. 02:13.060 --> 02:17.110 So I'm going to say this is decimal. 02:17.980 --> 02:23.020 So now I'll go ahead and say this is one by one decimal place. 02:23.050 --> 02:23.590 Okay. 02:23.590 --> 02:24.940 So comma. 02:24.970 --> 02:28.030 And then I'm going to give the enrollment date. 02:28.060 --> 02:33.190 Let me say the enrollment date is going to be in raw underscore date. 02:33.310 --> 02:35.440 And then this is a date. 02:35.440 --> 02:38.140 So the data type is going to be date. 02:38.260 --> 02:39.280 Oh no it's cool. 02:39.280 --> 02:40.930 We have created a table. 02:41.110 --> 02:45.250 And then I can go ahead and uh execute this query. 02:45.280 --> 02:47.050 Go ahead and execute your query. 02:47.050 --> 02:52.150 And uh down here you can see I have this query successfully. 02:52.180 --> 02:57.800 Now if you go ahead and refresh that table, you can see we have a column where now No. 02:57.800 --> 02:58.730 And there are. 02:58.760 --> 03:07.100 This column contains these students ID first name, last name, GP and zero date. 03:07.760 --> 03:08.210 All right. 03:08.240 --> 03:10.580 Now you can write a query to the table. 03:10.850 --> 03:13.400 Go ahead and go over here and remove this. 03:13.520 --> 03:16.730 Go ahead and say Select and Auto select. 03:16.730 --> 03:20.960 All because I want to populate every data we have right in there. 03:20.990 --> 03:23.240 So in order to populate the column. 03:23.240 --> 03:24.980 So go ahead and select all. 03:24.980 --> 03:27.830 So the asterisk means auto select all. 03:27.830 --> 03:31.520 So go ahead and select all from there students. 03:32.330 --> 03:33.950 So that is the name of the table. 03:33.950 --> 03:42.170 And if I go ahead and these days I have the column printed out student ID first name last name GP and 03:42.170 --> 03:43.610 enroll date. 03:43.610 --> 03:45.140 So hope that is cool. 03:45.980 --> 03:47.570 All right that is very interesting. 03:47.600 --> 03:52.070 Let's go ahead and write a query that we rename this to this table to something else. 03:52.070 --> 03:55.460 And go ahead and say how to rename the student table to something else. 03:55.460 --> 04:02.940 So I'm going to say Rename devil, and the devil I want to rename is, uh, students. 04:02.940 --> 04:05.790 And I want to rename that to maybe like, purple. 04:06.060 --> 04:06.510 All right. 04:06.540 --> 04:07.890 There's a purpose for purple. 04:07.920 --> 04:08.490 Anyone? 04:08.520 --> 04:11.580 Now I'll go ahead and execute this query. 04:11.610 --> 04:12.570 Let's check it out. 04:13.740 --> 04:14.220 All right. 04:14.250 --> 04:19.680 Now we have our students have changed this guy out of freshness. 04:20.310 --> 04:23.040 And we have the table change to purple. 04:23.040 --> 04:26.520 So you can see it has changed from students to pupils. 04:27.030 --> 04:27.240 All right. 04:27.240 --> 04:27.780 That is cool. 04:27.810 --> 04:31.770 I will go ahead and put this back to let's go ahead and take Popus. 04:32.130 --> 04:34.920 Go ahead and cut these and I'm going to replace that. 04:34.920 --> 04:40.860 And I'm going to turn that to students because I'm going to maintain this to the end of this course. 04:40.890 --> 04:48.930 Cut head and the kids and I'll refresh my table and I have my students table back and we have it here. 04:49.200 --> 04:50.580 So that is cool. 04:50.610 --> 04:52.830 Now what if we want to drop your table? 04:52.860 --> 04:54.090 Go ahead and drop this table. 04:54.120 --> 04:58.960 Go ahead and say I want to drop my table and say drop table. 05:00.970 --> 05:01.510 Students. 05:01.540 --> 05:02.800 I put a semicolon at the end. 05:02.830 --> 05:05.080 Then if I hit on that, your table is gone. 05:05.110 --> 05:07.480 You go ahead and create a table of fresh. 05:07.480 --> 05:10.210 But I won't be dropping this table. 05:10.240 --> 05:11.230 I wouldn't drop this table. 05:11.260 --> 05:11.680 Here. 05:11.680 --> 05:15.340 I'm going to leave that table the way it is, because I've learned how to drop a database. 05:15.340 --> 05:17.740 And it can also the same way drop your table. 05:17.770 --> 05:21.490 So I just want to show us so we can know what is going on. 05:21.520 --> 05:24.490 Now I want to alter my table, which I just created. 05:24.490 --> 05:27.520 And I want to add in maybe like a phone number of the students. 05:27.520 --> 05:30.850 And all I need to do is I'll go ahead and say alter. 05:30.880 --> 05:32.410 I want this to be an uppercase. 05:32.410 --> 05:35.410 So let me go ahead and remove this and go and say alter. 05:35.500 --> 05:37.810 And then I'll go ahead and say table. 05:37.840 --> 05:41.470 Then give the name of the table students. 05:41.590 --> 05:43.750 And then I want to add in. 05:43.780 --> 05:48.670 Let me go ahead and say add phone underscore. 05:49.480 --> 05:50.800 How can I say phone underscore. 05:50.830 --> 05:52.570 No, that should be the phone number. 05:52.600 --> 05:52.990 All right. 05:53.020 --> 05:59.090 So instead of writing it to the end And this is going to be a varchar. 05:59.150 --> 05:59.960 Okay. 06:01.640 --> 06:01.940 All right. 06:01.970 --> 06:06.140 So it can either be a varchar or it can be an integer. 06:06.170 --> 06:06.860 You understand. 06:06.890 --> 06:08.480 So it can actually use any one. 06:08.480 --> 06:09.710 But I'm going to use varchar. 06:09.740 --> 06:18.170 And I want to specify that this is like a 12 characters that I need and are semicolon at the end. 06:18.200 --> 06:20.210 So all that is cool. 06:20.240 --> 06:24.410 Now if you go ahead and execute this let's go ahead and see. 06:24.440 --> 06:27.770 This is executed and got out a fresh piece. 06:27.830 --> 06:29.150 And uh. 06:31.250 --> 06:33.440 Over here I can see phone number. 06:33.650 --> 06:34.670 Oh I hope that's cool. 06:34.700 --> 06:42.620 And now if we go ahead and write another query to populate, this is going to say select uh all from 06:44.090 --> 06:45.140 students. 06:45.170 --> 06:50.210 And then with semicolon at the end then let's go ahead and select this. 06:50.240 --> 06:56.190 We can see stress ID first name last name GP in row date and phone number. 06:56.370 --> 06:57.960 Well, that is cool. 06:58.140 --> 07:05.460 And, uh, you can actually go ahead and put this down for a run with it and try as much as possible 07:05.460 --> 07:07.200 to add some things to that. 07:07.230 --> 07:11.220 I can also change this column, maybe from phone number to email. 07:11.250 --> 07:12.660 Yes, you can actually do that. 07:12.690 --> 07:15.030 I can rename this from phone number. 07:15.060 --> 07:17.310 I can rename that to be email. 07:17.340 --> 07:18.450 What if I want to do that? 07:18.480 --> 07:28.140 What I need to do is also let's say over to my table and I'm going to say statements. 07:28.170 --> 07:31.440 And then I'll go ahead and uh rename that. 07:31.470 --> 07:32.910 Let's go ahead and put this to the upper case. 07:32.940 --> 07:33.480 Okay. 07:33.630 --> 07:34.800 Causes a keyword. 07:35.640 --> 07:37.440 So rename column. 07:37.500 --> 07:39.720 Go ahead and say column. 07:39.750 --> 07:48.750 Then when underscore number and I'm going to rename that to email. 07:48.780 --> 07:50.490 Then put a semicolon at the end. 07:50.520 --> 07:52.620 Then let's go ahead and populate that immediately. 07:52.620 --> 07:56.370 So we can actually do the same thing if two at the same time. 07:56.550 --> 08:08.910 So go ahead and say from and the students now I've got the kids and let's check it out so we can see 08:08.910 --> 08:11.190 we have our stress ID, we have our first name. 08:11.190 --> 08:16.530 We have the last name, we have the GPA, the enrolled date, and we have the email. 08:16.530 --> 08:20.340 So I'm going to drag this a little bit down so it doesn't get up. 08:20.370 --> 08:22.020 Now that is cool. 08:22.050 --> 08:24.300 And I'm going to reduce this a little. 08:24.330 --> 08:25.920 So it goes below. 08:26.220 --> 08:26.850 All right. 08:26.880 --> 08:30.630 That's cool I have to rename them back to email. 08:30.630 --> 08:32.580 So I have renamed the phone number to email. 08:32.580 --> 08:33.930 So go ahead and check it out. 08:33.930 --> 08:41.700 And now if I refresh your table you can see it is now email and not phone number. 08:41.700 --> 08:44.580 And you can actually change the size. 08:44.670 --> 08:46.320 You can actually also change the size. 08:46.320 --> 08:47.520 So let me drag this a little. 08:47.550 --> 08:48.030 All right. 08:48.060 --> 08:53.950 Now if we want to change the size of these is, uh. 08:54.370 --> 08:54.820 Okay. 08:54.850 --> 08:56.470 Let's go ahead and click on this. 08:56.470 --> 09:00.490 And I want this to appear down here because he has synthesized down here. 09:00.490 --> 09:04.150 And the email says it is our 12 characters. 09:04.150 --> 09:06.550 What if I want to make this to be like 50 characters. 09:06.580 --> 09:10.300 Well what I'm going to do or how am I going to alter that? 09:10.300 --> 09:15.730 So if I want to alter that, what I need to do is, uh, I can actually go back here and I'm going to 09:15.760 --> 09:17.110 change this to rename. 09:17.140 --> 09:23.110 I'll get over to this point changes rename and I'm going to say modify. 09:26.950 --> 09:28.780 So it is now modify. 09:28.780 --> 09:31.630 And I'm going to say I'm modifying a column. 09:32.590 --> 09:35.950 And that the column I want to modify is the email column. 09:35.950 --> 09:38.530 And I want to give the Varchar. 09:38.680 --> 09:43.930 And then the size is going to be 50 and no longer 12. 09:43.930 --> 09:46.120 So the semicolon at the end. 09:46.120 --> 09:47.950 And let's go ahead and skip this. 09:47.950 --> 09:49.720 And down here we have it. 09:49.720 --> 09:52.760 So you can see our guys are on a freshness. 09:52.760 --> 09:57.380 And now I have a chore and it is 50 and no longer 12. 09:57.380 --> 10:00.710 So in that way I've been able to modify that. 10:00.740 --> 10:03.980 Now what if I want to move this email to call? 10:04.010 --> 10:08.810 Maybe after the first name or last name or GP, then how can you be able to do that? 10:08.840 --> 10:11.090 Let's say I have first name, last name, date. 10:11.090 --> 10:12.350 Your email comes next. 10:12.380 --> 10:13.970 Be first before your GP. 10:14.150 --> 10:15.590 How can you be able to do that? 10:15.590 --> 10:16.970 Because it's very important. 10:16.970 --> 10:21.230 So what if I go over here and I'm going to keep altering this. 10:21.230 --> 10:30.650 And anytime I alter what I need to do right now is I will go ahead and modify and we're going to say 10:30.680 --> 10:31.670 modify. 10:31.670 --> 10:38.000 And then the email which is a varchar. 10:39.890 --> 10:43.910 So Varchar and that is 50. 10:43.940 --> 10:50.360 Then we now say it comes after the last name. 10:50.360 --> 10:59.910 So after the last underscore name curriculum guide, I'll make this last underscore name semicolon at 10:59.910 --> 11:00.600 the end. 11:00.600 --> 11:02.880 And this guy had on the run this. 11:02.880 --> 11:06.720 And now we can confirm that that is okay. 11:06.720 --> 11:09.660 So you can see students first name last name. 11:09.660 --> 11:15.300 And we have email comes up then GP enroll date. 11:15.300 --> 11:17.610 So in that way I can be able to modify this. 11:17.640 --> 11:20.550 You can see how that comes and I can actually do that. 11:20.550 --> 11:22.860 So maybe comes first. 11:22.860 --> 11:24.720 So go ahead I'm going to come first. 11:24.750 --> 11:26.760 Maybe that should be your assignment. 11:26.760 --> 11:34.920 And you can also drop the email if you want to drop the email okay I'm going to also bring it to actually 11:34.920 --> 11:35.550 come first. 11:35.550 --> 11:41.940 If you want to come first, then what you need to do instead of you writing after students is very easy 11:41.940 --> 11:46.560 to just see first. 11:46.650 --> 11:52.990 All right, let me make this to be uppercase First place Omicron at the end and this guy had not run 11:52.990 --> 11:54.010 this and check it out. 11:56.290 --> 11:56.770 All right. 11:56.770 --> 11:57.970 So my email has come. 11:58.000 --> 11:59.260 First you see that. 11:59.290 --> 12:00.340 So that's cool. 12:00.340 --> 12:02.020 So the email has come first. 12:02.020 --> 12:03.190 So I hope that is cool. 12:03.220 --> 12:10.630 Now if you want to drop your email just a like you want to delete this email, you don't want it to 12:10.660 --> 12:11.440 exist. 12:11.440 --> 12:13.630 So also alter the table. 12:13.630 --> 12:23.650 And what you need to do is just go ahead and drop the column and uh, email. 12:23.890 --> 12:29.350 Then placed the record at the end and go ahead and run this query. 12:29.350 --> 12:35.980 And you can see we have students ID first name, last name GP enroll date. 12:36.010 --> 12:36.400 Right. 12:36.400 --> 12:38.530 So let me go ahead and say I want to alter that. 12:38.560 --> 12:44.380 I'll go ahead and say alter table and the number of table students. 12:44.380 --> 12:48.070 And then we go ahead and say modify. 12:48.100 --> 12:53.540 And then we select the column and there that is GP. 12:53.840 --> 12:57.170 Then we cannot see decimal. 12:57.230 --> 13:03.320 And I can increase this maybe like a five by three decimal place. 13:03.350 --> 13:03.980 No problem. 13:03.980 --> 13:04.760 That is cool. 13:04.760 --> 13:07.460 And then place semicolon at the end. 13:07.460 --> 13:11.030 And then we go ahead and run this query and check it out. 13:12.620 --> 13:15.590 So we can see down here we are able to modify docs. 13:15.620 --> 13:17.510 Go ahead and refresh this. 13:17.510 --> 13:22.550 And now here you can see the GP decimal is five by three decimal place. 13:22.550 --> 13:23.450 So that's cool. 13:23.450 --> 13:26.450 So that is how you can be able to make some modifications to a table. 13:26.450 --> 13:29.390 And I will go ahead and continue with that. 13:29.390 --> 13:35.690 So let's go ahead and move to the next video lecture and see how to insert rows into a table. 13:35.720 --> 13:38.870 Thank you and see you in the next video lecture.