MySQL Tutorial

well hello internet and welcome to my MySQL tutorial in this tutorial I'm gonna cover about 95 to 98% of everything you will ever you need to use MySQL and I have a lot to do so let's get into it okay so this is MySQL workbench and you should install it whenever you get MySQL oh and I'm not gonna go through the whole installation process because that's pretty easy and everything will be exactly the same on Mac PC Linux anything if you want to come in here and go and create a new user you're just gonna come in here open up my school workbench click on server administration and then you're gonna have this pop-up and click on OK then gonna have to type in your password and then specifically you're gonna go to accounts manage users and you're gonna see all the users the user I'm gonna use in this tutorial is gonna be MySQL it man this guy right here if you want to create a new user just click on Add Account and then you're gonna come in here give a username a password and all that stuff and then you can go into administration roles and all of the different things that that user will be able to do I'm just gonna cut it short right there and jump right into MySQL ok here I am in a terminal like I said everything is exactly the same on PCs and on Linux and on Macs and everything so this would be the command line in Windows or a terminal inside of Linux or Mac OS X so you're just going to start it up you might type in MySQL 5 or you might have to type in MySQL it depends on whatever your system is set up for and I'm just gonna come in here and I'm going to log in like I said this is a custom made user and you just type in - you for the user ID and - P for the password type in whatever password I set up for this user and here I am inside of MySQL now if I want to quit out of MySQL I just type in quit and I'm out can also just press up and log back in and then type in my password again and Here I am again clear my scroll back if I want to show all the databases that I have inside of my database I just type the head in show databases and you can see all of them pop up right there and I'm going to try to stick with convention so normally whenever you're typing in all of your commands and - queue all you want to use uppercase letters so if I want to create a database and let's say I want to call it test 3 there you go went and create that and we can also come in here show databases you can see test3 is there on our screen if I we want to then use that database I'm gonna try and stick with the upper case here I just type in test3 and there you can see that is gonna be the current database I'm using if I want to show my currently selected database I just type in select database and there you can see I'm using test3 and then if I want to destroy that database just type in drop database if exists test 3 show databases and now you can see test 3 is going and I have a link in the description for this video that has all of this listed out and it's heavily commented you can use it as a cheat sheet now when creating our database what we have to do is very often makes a lot of sense to print out everything just write it out exactly what you want to store so what I'm gonna do here is create a student database and you can see here I have student and we're also going to have scores for both quizzes and tests and you can see all of the information I'm gonna have a science class and I'm gonna have an English class and a whole bunch of other different classes as well and I'm just gonna model out exactly what I want my data to look like then after I model all of this out and I give each of my students an ID as well as test scores and all of the other things you can see right here I can then break all of this down into tables and we're gonna go through these tables one at a time but as you saw here it makes a lot of sense for each of the students to have the first last name email street and all of the other different things that you see right here and you can see down here where I have student ID that's gonna be the unique ID for each of our students and as you're gonna hear here in a moment this is known as a primary key I'm also going to have my classes and my tests and absences and all of the different scores also stored in different tables and as this tutorial continues I'm going to go over how all of these are going to be created so we're gonna come in here and we're gonna create our student table so to create our table you just type in create table and I'm gonna call this student put your bracket inside of there and you're gonna list out all of the data that you want now I believe that the maximum length for my first name is going to be 30 characters so variable number of characters and this is just an estimate this isn't a cut in stone announcement we're saying we expect 30 characters but it might go over that if I want to guarantee that this data is entered gonna type in not null and then I'm gonna do exactly the same thing for my last name variable number of characters that I believe is gonna have a maximum size of 30 and I want to guarantee it's in the database so not null then I come in to my email I'm gonna again variable number of characters and this is the data types that we're defining here variable number of characters you can see more here in a second I'm gonna say that I expect it to be no more than 60 but in this situation I'm going to type in null maybe the person or the student doesn't have an email or maybe we won't be able to get it whenever we want it first off so we're gonna say that that doesn't necessarily need to be filled in street I'm gonna say a variable number of characters again put it at 50 and I'm gonna say that I must have a street address for my student make sure you put a comma at the end of all of these city on all again state in this situation I know that the state is only ever gonna have a maximum of two characters so I'm going to actually put this in stone and say I expect two characters here and that's exactly what that says and then if I want to put a default value inside of here like Pennsylvania or PA that's how we would do that for my zip I'm gonna have a medium side integer and I'm gonna go over exactly why we want that in a moment if there's no such thing as a negative zip so I'm gonna say that it's unsigned phone I could just say that this is going to be numbers but in this situation who knows if they're gonna put dashes or whatever inside of there so I'm gonna say a variable number of characters and I think that's gonna be around 20 characters and not all again birthdate I'm gonna mark this as a date and not null like I said I'm gonna go over these data types here in a moment numerated type if I know that we're either going to have a male or a female well I'm gonna define those very specific options by using an enumerated type or like that let's say I also want to mark the date that they signed up for our class I could use something called a timestamp which is also going to give me date and time let's say that I also want to have cost for lunch I don't know I'm just making these things up in that situation I'm going to need decimal values and that's gonna be a float and then the final thing we're going to define here is student ID that's going to be a unique number and it is going to be an integer and we're going to this is unsigned not null again and if I want this to automatically increment every single time a new student is created I type in Auto increment and this is going to be a primary key which is going to allow me to have a unique ID for each of these students which is gonna help me a lot and after I create that database I can come in here and go show tables and there you're gonna see the student database is created another thing you need to do of course is make sure that you go and create your database because I deleted it previously and then say use test three and that's the name of the database you can change the name to whatever you want now each of the students is gonna have what I call it a primary key and let's just go over briefly exactly what a primary key is and some rules about them they're going to be used to uniquely identify a row or a record or student information in this situation each primary key must be unique to the row it must be given a value when the row is created and that value cannot be null that original value cannot be changed and it's probably best auto-increment like I just showed you know it's time to take a little bit of time to talk about what are called atomic tables now as your database increases in size you're going to want to be able to keep everything organized and the main reason you're gonna want to do this is so you're gonna be able to perform queries really quickly with your database now if your tables are set up properly your database will is gonna be able to crank through hundreds of thousands of bits of data in a matter of seconds if it isn't however then you're gonna have everything be very very slow so you may ask yourself well how do you know how to best set up your tables well basically you just need to follow a couple simple rules every table should focus on describing just one thing for example a customer table would have name age location and contact information it shouldn't contain lists of any things such as interest job history past addresses products purchased etc none of that nonsense just the main bit of information that you need then after you decide what one thing your table will describe such as we had students then what you need to do is decide what things you need to describe that thing then you're gonna write out all the ways to describe that thing and if any of those things require multiple inputs pull them out and create a new table for them so for example with our students if we had a list of past classes and past performance we would put that in another table then once your table values have been broken down we refer to these values as being atomic however you want to make sure that you're careful and you don't break them down to a point in which the data is harder to work with so that you have to type in mile-long queries just to be able to get at your data and as this tutorial continues gonna see exactly how I build all of these tables one by one some additional rules that might help you make your data a little bit more atomic and if you can't wrap your head around all this right now don't worry about it we're gonna give examples but one thing you want to do is make sure that you don't have multiple columns with the same sort of information so for example if you wanted to include employment history for an employee database you shouldn't create job one job to a job three columns what you want to do is make a new table with that data instead you also don't want to include multiple values in one cell so for example you wouldn't create a cell named jobs and then list that the person worked at McDonald's RadioShack Walmart and so forth and so on you want to split all that out out and that brings us back to creating our tables and all of the different variable types so you have your variable number of characters right here you have variable number of characters here you also saw here I have a character here I have a medium ends here I have an enumerated type here I have a date primary key of course and timestamp what do all these mean I'm gonna briefly go over all of them right now okay for the numeric types if you are only ever going to store a value between 127 to negative 128 you would use what's called a tiny int you could also see here the minimums and maximums for small ends medium ends and intz in general here are all the string types you have characters which is going to be a fixed length series of characters variable characters which you saw that I used a lot and whenever I created the last table and it's gonna have a variable length even though you put a number in there that is just an estimate and then you have blobs which can be quite large more numeric types you have big integers you can see they're the minimum and maximum floats which is what you're gonna use with decimals as well as doubles then you have your string type so you're gonna have enumerated types which is just like you saw there whenever you know that you're gonna have a limited number of possible values like male or female numeric type works well and you have sets which I'm actually going to skip over in this the tutorial which is this gonna be a list of legal possible character strings and the main difference between an enumerated type in a set is that a set can contain multiple values meaning that you could pick male and female for example if you wanted to whenever an enumerated type can only ever have one real value and we have date types as you can see their date is just gonna be year month and day you're gonna have x which is hours minutes and seconds date times timestamp and year okay so we're back in MySQL and if you want to see a description of any of your tables that you create you're gonna type in describe and then student and there you can see it's kind of messy but if I shrink it down in size you're gonna see that everything is listed out there exactly as we typed it out so how exactly do you go and enter values inside of here what we're gonna do is we're going to insert student information you just type in insert into and the table you want to insert that data into and then you're going to type out values and you can skip around on two different lines if you'd like and here I'm gonna type in a student named Dale and you're gonna want to type this in an exact order that you set up the database so we had first name we had last name and then we had email address and if you're working with characters you're gonna want to make sure that you surround them with quotes and then we could type in our address and you can see here I can type in bounced around as much as I want and my city and my state zip code which is an integer so we don't have to put quotes around it phone number which is a character that put quotes around that could also use double quotes if you like that for our birthday male if you want to get the current time this is the information on when this information was entered into the database you just type in now with the two brackets there and that's gonna give you that and the amount they're going to be charged for lunch and then Nall is going to represent the primary kid so I'm gonna automatically increment itself and there you can say it and there you can say I went and filled in a whole bunch of other student information just like we did before and clear that out of there we then want to show all that data we're gonna go select and we're gonna put star inside of here that says basically we want everything and then we're gonna say what table we're gonna pull that information from and there you can see it's kinda messy again if I shrink it down up you're gonna see all the student information entered in there I have to shrink it down quite a bit just be able to fit it all in there but you can see the first name last name email Street city state zip phone numbers birthday sex date entered you can also see how those are changing and exactly what the timestamp looks like lunch cost and the student ID and you can see those Auto incremented ok the next day we're gonna create is going to be class which is going to have a class ID for each of the possible classes as you saw right here we had science we're also gonna have English and a whole bunch of other different things and then this class ID is going to be put inside of the test area and that's how we're going to be able to keep track of everything I'm gonna keep everything normalized like I said before or atomic by separating out everything that doesn't need to be there and we're going to have Class B in its own table because there's no need to type in science multiple different times here or English or whatever inside of the test area so to create this table we're gonna go create table and we're gonna call it class just like we defined there and each of these gonna have a name and I'm gonna say variable number of characters no longer than 30 and not in all of course we want to make sure we have something in each one of those and then we're gonna need a primary key for this unsigned not null just like before Auto increment so we don't need to worry about it primary key of course close that off and show tables you can see class was created then I can just paste this in here another insert save myself some time you could see English and all those other different things and all again is going to allow this to auto increment and there you can see that's been created and if I want to see everything select again star for everything from class and there you can see they're all listed if I want to create my test table create table test inside an app for each of these tests we're gonna have a date and date type of course and not null remember we had an enumerated type that was either a test or a quiz so we're gonna create that all again class ID is gonna link out to the table that we just created here and like I said before this is called a foreign key which I'm going to talk about in a second and then test ID which is going to be the primary key for this guy again int unsigned not null and every primary key is gonna look like this primary boom and you can of course use lowercase letters and there you can see all of those are create no as I said in a second ago a class ID like we created in the test table is known as a foreign key and what they're used to do is make references to primary keys of other tables so class ID inside of the test table is a foreign key that references the primary key over in the class table and to give you another example let's say we had a customer and a city table if the city table had a column which listed the unique primary keys for all the different customers that primary key listing in the city table would be considered a foreign key or just like you saw previously with the test isn't and the classes now the foreign key can have a different name from the primary key but very often it makes sense to keep them the same the value of a foreign key can have a value of null whoever that wouldn't be advantageous most of the time but it's possible most definitely and a foreign key does not need to be unique so and but that's quite obvious because we could have multiple different people to in a science class taking a test so you would have science numerous different times inside of there for the to represent the foreign key and of course they wouldn't be unique back over MySQL and now let's create our score table Oh create table score student ID int unsigned not null event ID which is going to represent the test that was taken and I'm gonna go back in here as I show you other different things and change names and so forth inside of these we're gonna have our score which is gonna be an int because these are all small numbers and then what I'm gonna do is I'm going to combine the event and student ID to make sure that we don't have duplications in regards to scores it's also gonna make it quite easy to change scores later on so I'm gonna create a primary key and if I want to use the event ID and the student ID to make sure they're unique that's exactly how you do that and there that's created and once again the reason why we're combining the event ID and the student ID is because in the scores table we're gonna have multiple different tests they're going to be taken by each of the different students so that's not unique and also the student IDs because they're going to be taking multiple tests are also not going to be unique however if we combine the two of them together that is going to be unique last table we want to create here is the absence table which we're going to make use to make sure that the students are going to be able to take tests that they missed and it's gonna have a student ID inside of it it's gonna be unsigned not at all gonna have the date of the absence date datatype not at all again and once again we're going to create a primary key that is going to combine the student ID because students could have multiple absences so we have to figure that out and date since they can only miss one date there we go now we have a unique primary key and there you can see all those are created now if we want to come in here to the test area and describe that now let's say that we wanted to have multiple different types of quizzes and multiple different types of tests meaning that there would be different types of and different numbers of questions inside of them how exactly we go in here and add in a new row that's going to have the maximum score for each of these tests and quizzes well we're gonna go in here and we're gonna type in alter table the one we want to alter it's called test and then we want to add a new row of data inside of this we're gonna go add and let's say that I want this to be called max score and I'm gonna define the data types not null and if I also want to put it after type for example to keep it away from the different keys I can type in after height and there you go and if we go describe test you're gonna see max scores in there another way to enter data inside of these let's say I want to add in some test data let's go and put that on the screen so we can see exactly what's going on here we can also go insert into tests and values and then we can put all this in here at one time so let's say that the test is in 2014 8:25 and it's quiz and the maximum score is 15 and the class ID in this situation is gonna be the first and then we're gonna make sure you please call them in there and then you're gonna put in all and then you're gonna put a column after that and then put in all that other additional data and there you go that's how you would be able to enter all of those different types of data all on in one query inside of MySQL and then we can of course see all of them select from test and there you can see them all in one place now how exactly would you go in here and change the name for a row so let's say we go into we want to see what's in score and we decide that event ID doesn't really make any sense and we would prefer to have test ID inside of there how do we go in and change that you're gonna go alter again as you're gonna see use the same commands over and over again and then you're gonna type in score which is the table you want to change and then you're gonna type in change and if you want to change event ID to test ID instead you're then going to have to define the data type for our new test ID unsigned and not null I'm gonna be able to go describe score and see that it's been changed from event ID a to test ID I'm then going to enter in all my score data and you can see right here where I call minute edit this one line this is how you put a single line comment inside of your queries you can see here insert into score values and I have all that they're just a save time and you can see that I updated that if I also want to come in here and put some data in the absence there you can see that it's often a good idea to use describe to be able to see exactly the order you should put your data in and you can see there I'm using insert command again and there are all of my absences for all the different students or you can see right there now to list all of this data out you're not always gonna use the star command which is gonna list out everything so let's say with student database I just want first name and you could also come in here nothing's case-sensitive so you could go first name or last name whatever you want then you just come in just like before and go student and that's gonna list out all of our student names you can also see here however that whatever you type in here for first name is gonna show up there and last name there we're gonna correct that in a moment now another thing is sort of bothering me about this is if we show our tables you're gonna see everything here is absence class score student and tests well I kind of would prefer these to be plural so that's gonna mean I need to go in and rename my table and I'm going to rename all of them instead of just some of them so we're gonna go into absence and I could just do one or I could do multiple ones and we're gonna change this to AB since this class two classes score two scores and then test to test and if we then come in here show tables you're gonna see all those are plural now so let's go in and let's say that we just want to be able to select the first name last name and the state from our students database from students and this is where we're comes in where is used when you want to limit your data so I'm gonna type in state and let's say I just want all students that are from Washington there you go and we could also do more than that way more than that say I want to get first name last name and birthdate from students and if I just want the people that are born after 1965 I can specifically get just the year from the date of birth part and I just type in birthdate and greater than or equal to 1965 and there you can see that data and of course you're going to be able to compare all of these using equals to greater than less than greater than or equal to less than or equal to or not equal to I could also go in there and get the month so I'll get first name last name birthdate from students and it doesn't matter that the from has a lowercase M and let's say we're and if I want to just get the month like let's look say we're looking for birth dates or something for the month I don't know just type in month birth date is equal to two or another thing we could use is an or statement to put in multiple conditions or state is equal to California so this is gonna match if they were born in February or they were born in the state of California and there you can see you're gonna be able to combine these conditions either using or as you just saw and and another guy you can use is not and instead of or if you wanted to put in here two of those guys or four and you want to put in and you could do that and for not another replacement for that is an exclamation mark which I'm going to show you here in examples and let's make another query here that's a little bit more complicated that uses those logical operators I just showed you so let's go last name state birthdate from students again and let's say we want all the students that have a birthday that is greater than or equal to twelve and we could put an end inside of there or we could put and inside of there like that put brackets here if you want to combine these guys or they're born in the state of California or put that in there or put over doesn't matter I'm gonna put that just to show you they're the same there's state they were born in was Nevada there you can see all those matches another weird guy is null if you want to check if a value is null you cannot use the greater than or equal to or any of those different things what you have to use is either is not null or is null and select so let's say last name from students and if we want to check if a last name was not entered at all we would go is it null and that would list every single last name student that didn't have a value in there or we would come in and go is not null if we go is not know it's gonna list all of them because all of them have last names another thing we can do is decide how our data is gonna be ordered inside of MySQL so let's say first and last names from the students table I want to order them by last name there you can see all of them are now if about a quarter now if you wanted to put them in reverse alphabetical order you would just type in order by and whatever they are calling name is followed by a DSC that's gonna be reverse alphabetical order you could also combine those let's say when it first last name is state from students and the most important thing is to order by state and let's just do it in descending order and secondly you wanted last name in alphabetical order which is ASC and there you can see they went and had all the states putting in the order but it made sure that this was in alphabetical order like that just another weird query if you want to limit the amount of data that you're going to get to say the first five results to stipend limit five if you then wanted to get the next five you just come in here where the five part is and type in five through ten and there you can see you get the next five now these guys are kind of messy where you see first name and last name so there's a couple different ways that we can clean that up and also clean this data up so that the names are not divided on the screen we're gonna use concat which is gonna allow us to combine data so let's say we wanted to get the first name and then we wanted to have the last name as well all in the same area but we want the title at the top to be just name we're gonna type in as and name like that and let's say we want to get a city in the state and combine those all into one block as well city state and let's say we want this to be hometown and we want that from students and there you can see everything's listed and you can see names right here that's because of this as right there and hometowns there because of that right there and all this data is combined because we use the concat command another thing we can do is use like which is going to allow us to get data that fits certain constraints so let's go select and let's say we wanted our last name and first name from students where and let's say that we wanted everybody that's first name began with the letter D we would say like D and then we would put a percent sign inside of there or last name like and let's say we wanted everybody that had a last name that ended with the letter n there you can see all those and this percent sign basically matches any sequence of characters so that's what it's used for another thing we can do with like is we could use underscores which are going to match any single character so let's say we wanted a four letter name that ended with the letter Y name from students where first name there's like again we use underscore that represents one character so we want three characters and an ending with y and there you can see those matches now let's say we in our results we only want to show something once we would use distinct in that situation so let's say we wanted a listing of states from students and we went to order by state so we're gonna see every single state that every single students from but we're only gonna see at one time there you can see that's what distinct does it only gives us a result one time to avoid duplication if we then wanted to go in and count the number of students from each of those states we would use count and then we would use distinct the only shows are just our state one time from students again so you can see that our students come from only eight distinct states we could also use count to come in and count the total number of students that we have in our class select count from students see we have a total of ten students could also use count to go and count the number of boys in the class count all so that's gonna give us all the students and then we're gonna say from students and then we're gonna use where to issue our conditions you see we have six boys in each of the class or six males another thing we could do is group our data so select and let's say we wanted sex and the count for each of the sexes sex types from students and then let's say we want to group them by sex so the thing that's gonna be different between any two of these or it's either gonna be male or female and there you can see we have six males and four females do a little bit more with group by so let's say we want select the month birth date and let's change the column name for this two month and we want to count all of those different birthday months from students and we want to group by month and that's a reference to this month up here that's what that month is and then we want to order by month as well and there you can see for each of these months the number of students that were born in those months another thing we can do is narrow our query results after the quarry has been issued using a command called allow so we're gonna say that we want the state and we want to count the number of states and we're gonna give this the name amount we're gonna pull this data from students and we're gonna group by the state we're only gonna list a state if more than one student is from it so that's what having allows you to do so I'm gonna say having an amount greater than one and there you can say Atlas California Washington because two students in both situations were born there it's also going to be a whole bunch of math functions you're gonna be able to do so let's say that we want let's put these all in separate lines so let's have our test ID as test and let's say that we want to get the minimum score on that test score give this the name as min get our max score get there a range of scores did the max minus the min and there's all of them including some and the average and gonna be pulling that from the scores table we're gonna group by test ID and there you can see all of those now you can see all a whole bunch of the different numeric functions that are available in my school I'm not gonna give examples of every single one of them but as you can see we can get an absolute number or an absolute value for a variable there's all the different trigonometric functions you can get average like I just showed ceiling which is gonna return the smallest number not less than whatever it's passed to you're going to see count like we've seen multiple different time you also have the option to get degrees gonna be able to use exponents gonna get floor which is going to return the largest number not greater than the value passed to it the logarithm function max min modulus which is gonna return the remainder of a division you can get the value of Pi gonna be able to make calculations based off the power radians you're gonna move it random numbers you're gonna be able to round you can a square root you're gonna have standard deviation some like we saw before and you're gonna be able to truncate so you can play around with those different mathematical functions on your own and let's go in here and just do a couple other different things let's take a look at absences everybody has an absence from our classes and there they are let's say that I wanted to come in here and have a student that missed a test go in and retake that test and get a score so we're gonna look in the scores table here now what I'm gonna do is I'm gonna go select and I'm gonna get a student ID and a test ID just to show me what's going on here from scores and I'm gonna look up the specific student ID that is equal to six because student ID right here missed that test so I want that student take that test you can see them right here you can see they missed test number three now I would go and I give them test number three and they go and take it and they get a result now I'm gonna go in here and actually change the values for that and to do so I'm just gonna type in six which is their ID three which is the test ID and 24 which is gonna be their score then come in here and I'll now see that they actually took that test well now what I'm going to want to do because the absence is still going to show up there is go in and remove that so if I want to remove data from my table I just go delete from and the absences where and in my condition student ID is equal to sex and there you can say select everything from absences and there you can see it's gone now let's say that since we're looking at absences here we might and let's look at it close let's say that we would like to have true or false inside of here in regards to whether they took the test or not so let's say we wanted to keep track of all absences and just wanted to put true or false if they took the test or needed to take the test or whatever inside of here well we're gonna use alter again and table absences want to add a column let's call it test taken and it's only ever gonna have either a true or false inside of here character and let's just put not in all give it a default value of F and let's say that we want it to come after the student ID and like I said you could put before in there as well and would be useful to be able to spell absences right so instead of ancestors absences and there you can say that's changed and described is gonna show the test Aikens in there well now what if we in using some logic we realize well it's only ever gonna have a value of true or false so this should actually be a numerated type so let's go in there and let's fix that and once again to change the data type for our table we're gonna go alter table absences modify column and test taken is the guy we want to change and we want to change it to an enumerated type that's only ever gonna have a value of true or value of false and it's gonna be not null again and it's gonna have a default value of f that they did not take it and there you can see that's fixed and then let's say that we come in here and we say you know what let's not have that at all as you can probably tell I'm just messing around here and we decide that we don't even want test taken in there we can go alter table absences and drop column it's gonna allow us to delete a column and test taken will be deleted and there you can see it has been now I'm gonna go in and show all of my scores where the student ID is equal to four now let's say I want to update a score let's maybe a score was entered incorrectly so we would go update scores and I'm going to set the score equal to 25 where and the condition here it's gonna be the student ID is equal to four and the test ID is equal to three and then if we pull that back up again you're gonna see that that's been changed another thing that's useful is a command called between and what betweens gonna allow us to do is find matches between a minimum and maximum so let's say we wanted to find the first last name and birthdate for any student that was born between 1960 and 1970 there is between and there you can see that's exactly what it did for us another useful command is to be able to go in here and find matches based off of a list so and one way to do that is to use the command in so we're gonna say is one first and last names in which the first name is in our list so either Bobby Lucy or Andi and it grabbed it for us there and in is a great way to narrow results based off of a predefined list of possible options and this brings us to actually joining data together now if you want to combine data from multiple different tables you can perform what is called a join and a join works by matching up comm and data like I'm going to show you right now so let's say that I wanted to get the student ID date score and max score on the test well this stat all comes from two different tables so if I want to join two different tables I'm gonna say from both tests and scores don't put that column in there where date so I'm gonna get all of these student IDs data scores and max scores for the date of 2014 25th then what I'm gonna have to do is decide on how this these two tables are going to join together well as we can see right here with our two our different tables that we created we have our tests table right here and our scores table right here well we're gonna have to find something that is like that they both have so that we can join them together I actually had to come in here and correct this the two things that they both have being test and score is test ID so we're gonna join these two tables together by pointing out those two things that are equal and to do so we're gonna go and test test ID is equal to scores test ID and there you can see we got exactly what we wanted so you can see it's quite easy to join two different tables in this situation all we need to do is find like data like we did right there now whenever you're joining these different tables together and different table data it's often going to be advantageous to proceed each of the pieces of data you want with the tables names so you're not going to get confused so we're gonna go scores student ID tests date scores the score TAS max score and then we're gonna hit say the two different tables we want to join the condition and then and pass test ID is equal to scores dot test ID and there you can see the same data so how exactly would we go in and join three tables for example let's do a big giant thing let's go select concat and students first name and students last name as name let's get test date scores score say we want to get the max score as well and let's say we want to pull this data from three different tables which is gonna be from test scores and the students tables and we want data in the situation where the date is gonna be equal to 2014 and then we're gonna have to just use multiple different conditions and we're gonna have to match up like data between the different tables so test ID is going to be in the tests table it's also gonna be in the scores table and then we have to match up the last one which is scores student ID is equal to students student ID and there you can sees all that information just go through a couple other different commands let's say if we wanted to come in here and list the number of absences per student well we're gonna put in our student ID we're going to put in the first and last name then we're gonna count the number of absences from the absence of stable we would then want to group by that specific piece of data that we're going to want to get otherwise we're just gonna get one result and in this situation you're going to see a quick way to get the total number of absences with each student name another way to do a join if we want to make sure that we include all information from the table listed on the left side of the screen even if it doesn't exist in the table that we're combining it with is to use what is called a left joint so let's go select first and last name and then let's say that we want to count the total number of absences once again we're going to be using this because we want to list the students that have absences and the students that do not have absences that's why we're gonna use the left joint and use it you're gonna say from students you want to make sure you list all the students names whether they have absences or not and the other table you want to join to and then you're going to put in your condition or the two things that are the same between these tables so I'm going to say the student IDs are equal and then finally we're gonna group these by the student IDs and there you can see we're listing the absences even if they are a zero and that is all occurring because of the left join that we just did and the final join we're gonna cover here or what are called inner joins and an inner join just gets all the rows of data from both of the tables and puts those together so let's just do something quick students first name students last name test ID score and we're gonna use the students table and do a complete join between the students and the scores table with an inner join and then your two things that these tables have in common are going to be defined which is going to be the student ID is equal to the scores student ID and the condition in this situation is we want to list all the scores that are less than or equal to 15 and let's say we want also order them by scores test ID and there you go every single score and this is basically just gonna give us all of our quiz data for every single quiz that was taken by every single student okay guys so that's a lot of information on MySQL please leave a comment if you watch the whole thing I'd be excited to see that so Eze leave your questions or comments below otherwise till next time [Music]

Channel: Derek Banas

Views: 1,709,674

Keywords: MySQL Tutorial, MySQL Video Tutorial, MySQL (Software), Learn MySQL, SQL (Programming Language), SQL Tutorial, Learn SQL

Id: yPu6qV5byu4

Length: 41min 10sec (2470 seconds)

Published: Fri Aug 29 2014