Curso MySQL #16 - INNER JOIN com várias tabelas

♫ Humming. ♫ opening vignette. ♫ Hello, little grasshopper!

Welcome to another lesson from your database with MySQL course! Unfortunately, the last class. My name is Gustavo Guanabra, I am your teacher, and this last class of its database course we'll finish a subject that was open the last class.

We will return to talk about the relationship between tables, and this is the third and last part about it Siiiim! What we had seen last class was the relationship of a table one to many. And there was that doubt: Yeah, but we have the relationship of one to one, He taught you how that makes the key.

We have the relationship of one to many, you also taught in the last class and showed up in practice as it does. But it was open that issue of the relationship many to many. I have many instances on one side and many other instances, and they are relating.

How do I do it in practice? Because it is a kind of relationship that happens constantly and you'll have to learn to use. So this last class, I booked to say goodbye to you and to show this important feature you will need if you want to create a database a little more stocky.

Again I want to inform . . .

"Wait a minute", again I want to take this glasses because I can not look with this business here Come on! So let's go back. Again, I wanted to thank you for all this affection, and I wanted to change a little bit the protocol I wanted to tell you the following: Never forget, O look here: That "thing" ever.

By clicking here, you sign the channel. The course're running low, signs the channel will come yet another course there. will not amend a week in the other not, but the next course ta coming up with great news.

Here on this side, you will be diverted directly to the Playlist. Watch our course always the Playlist's. For thus it is easier for you to organize, you see what you've seen and what you did not attend, makes it easy for us, because we keep following you, and another thing, never forget, never Remember, you need to do these classes doing in practice.

It's no use. Some people speak: ". Oh, I did not understand anything of HTML" You practiced?

"Oh, no, no. Just watch the class. " No use, my son!

You need to practice, MySQL also, many commands are many details, we made a "funkzinho", right ? ! Remember a funkzinho?

"What's there . . .

♫ Create table, create a table, ♫ ♫ Alter table, move the table . . .

♫ I tried to do everything to facilitate their learning. I sincerely hope, heart, I have achieved. Part of the effort is mine, to build class, make music, build the slides .

. . And part is yours, to practice, to train, to put everything and .

. . to create a database that comes from your head, or you need ta, their need.

Only this way, only this way you will be able to learn database Let's stop stalling, let's go to our slides they are neat. Let's take a remembered what we did last class before starting our need from now on. If you remember well, this was the entity relationship diagram we built last class.

And we did the relationship where the locust prefers a particular course And like, we got a consensus that you do not prefer a lot of things, you prefer something. Is your favorite course, it is his favorite course. This creates a relationship in an instance of a hand, with many instances of another.

So what we decided is that: Each locust can only choose one course, And each course may be preferred by many locusts. The technique I used to do this kind of relationship It was to bring the primary key from the one to the many side. So I brought the "idcurso" of course, directly to the side of the grasshopper.

So preferred course would turn a foreign key. That's because it was a primary key on the other side. So far so good, so far so perfect.

But what about the many to many relationship? How am I going to make the dynamics of the keys? We saw this in the first part of this class relationships.

So if you have not seen, or if you have seen but forgotten, gives a revised ever, O Playlist here: Search class fourteen. If I'm not mistaken is the lesson fourteen. What is the lesson that talks about diagram entity relationship.

It is the class that talks about the relational model. But we'll see another situation here. Let's see another kind of relationship that can exist between grasshopper and courses.

You do not necessarily have a kind of relationship between two entities, They can have multiple relationships. The example I separated here O: It is the grasshopper attend a particular course. So grasshopper - watch - course.

How do I do here? I will make every grasshopper can attend various courses, then it will generate a "n" there, and each course can be attended by several grasshoppers too. This leads to a "watch" is the cardinality many to many, then I classify this relationship as a relationship cardinality many to many.

And you must remember very well. Or else you will need to take a lesson remembered in fourteen. I'm not sure if it's not fourteen.

Take a look there, that is the class of relational model. What happens when I have a relationship many to many? Follows here with his uncle.

I will do the following: This 'n' that was the end, I'll bring it to the middle, I will turn "watch" in an entity. Look here what happened. And I will put the cardinality 1 on the outside, creating new relationships.

So that's the technique. And from there I generated two relationships one to many, and then I will use the same techniques I used previously. I will create new attributes, for example, those who watch, I'll have a handle to watch and I will have the date he began attending a course, for example, and I'll have to bring the keys.

Then I'll bring the primary key grasshopper, I'll bring the "id" grasshopper into "watches" as a foreign key. I'll call "idgafanhoto. " I will also bring the primary key of course, is "idcurso" into the "watch" also as a foreign key, and I'll call "idcurso.

" So I used the technique of the last lesson, which is to bring the primary key from the one to the many side twice, because I have two relationships. And then I have this this central entity, and this central entity will have both normal attributes her which are "id" and "date. " And also two special attributes that are foreign keys.

To do this then I'll have to use the "CREATE TABLE", which is the one command that we already used for some time. Then I'll put it there, CREATE TABLE, gafanhoto_assiste_curso (. The name of this table can be anyone.

I will use this from now didactic issues, but there are people who only puts, for example, "watch", some people put it, for example, "gassistec", right . . .

. But I will use more full names, just so we can better understand and fix these contents. And then I'll put the attributes, as I said there O, for example, I'll create a handle "watch.

" So I'll put "id int NOT NULL AUTO_INCREMENT," The "NOT NULL" means there is optional because when I put it as primary key automatically it will turn "NOT NULL". But I'll put too explicit for educational issues. The second attribute is the date.

I'll put "date," which is a "date". I put a comma. I'll put the two attributes that will serve as a foreign key.

Oh, the "idgafanhoto", which is of type "int" Never forget, it is not necessarily the type "int". Not every foreign key is of type "int", but, it has to be the same type as the primary key original. Then, the foreign key must be the same type as the primary key.

Because, the foreign key is a primary key that is another place. So they have to be the same type. You need not be the same name, but the type has to be followed.

I will also create the "idcurso". And now we'll set the primary key. I'll put "PRIMARY KEY (id)".

This "id" is the "id" from above. It is this "id" here. So I'll put here, my primary key of this table will be "id", which is this attribute from up here.

Now I have to set down here, the two foreign keys. Yes, two foreign keys, because we saw, which will see a primary key on each side, and will join this new entity. To do this, we will use the technique table last class.

We will put there, FOREIGN KEY (idgafanhoto) REFERENCES grasshoppers (id) That is, I'll make this "idgafanhoto" here, connect with the "id" of grasshoppers table. If you have difficulty understanding this, it is because you did not attend law last class. So go there in fifteen class and watch right, little grasshopper.

This is the advantage of people go to the playlist. I'm always playing you from one side to the other. Take a look at the playlist.

I do not remember if this side or that side which is a list. On one side is playslist. Or else up here, click here on the "i" interactive.

Clicked, will appear here on the side, there you click on the playlist and goes directly to class fifteen. So in class fifteen I taught the use of FOREIGN KEY with the REFERENCES. You need to understand this because I'm going to do it twice.

If you do not understand how does one go, how you will be able to understand two? And you will also be able to understand referential integrity. How it works referential integrity.

I will not explain it again. You create relationships between tables, where you do not allows updates, deletes unnecessary or have related data. It's very important that you understand this, even to you to justify the use of FOREIGN KEYS.

And I'll create a second FOREIGN KEY, as I have suggested here, which is the FOREIGN KEY of "idcurso" I ondeu I will make the "idcurso" that this is my attribute here relates to the "idcurso" table course. So now, that time has come. Snaps his fingers, stretching your back, open your environment, open all your server, open the Workbench, and we will work.

♫ Transition effect. ♫ So I'm already here in the Workbench, my database is not open, so I'll put in there "Use register;" So, I'm there with the order. I have the tables and grasshopper course, it's all there cute, like last class.

Let's do that "CREATE TABLE" we used now. I put "create table" will put "gafanhoto_assiste_curso" open parentheses, closing parentheses. I will put here my "default charset" pro "utf8", only not to forget there at the end.

And now let's put the attributes here. In here I will have the "id" of my table, I create with the "int not null" and "auto_increment". It has an underline here.

With the strength it is very limited in this regard. Semicolon not, damn! Comma.

I'll put here also the date, which is "date" I will also put my foreign keys, "Idgafanhoto int" and "int idcurso". The foreign key as well. If there in your primary key you have any with a special strength such as default and such, you also have to put here.

We do now the primary key. So I'll put here "primary key" in brackets I'll put "id", referring to this "id" "Id" from up here. we will also put some "foreign key.

" Some people question how it is to be able to show this simplified here. If you hurry it does not appear, but if you press "f" and wait one second, one second and a half, it will begin to help you. "Foreign key" in brackets "idgafanhoto", which is the field from up here "references" my table, which is locusts and the "id" field, which is the "id" field of locusts.

If you have questions, you come here in tables, open the locusts table, opens the columns table, right, as column are the attributes of the table. I got there, O, "id. " I'll leave here closed.

And I will establish my second "foreign key" that is "idcurso" You will refer, courses in the table, The "idcurso". It turned out that was the same name, right ? !

"Idcurso" here, "idcurso" here, you can confuse. This "idcruso" here, is that "idcurso" up here. This "idcurso" here, is that "idcurso" courses.

So if I open the table similarly courses, will get there "Idcursos" which is my attribute. We put the structure here. We will press Ctrl + Enter.

He has already created my table. We will update here and see if it has already created the table here, O. Now I have courses, grasshoppers, and I have gafanhoto_assiste_curso.

Our table is ready. Now you have to understand how I put the data in there, what kind of relationship we will do. Again, I'll bring my example that I I always do with you.

I'll give that neat expanded here in my entities, so I'm watching grasshopper course, and then we'll put some locusts on the left and we will put some courses on the right. Let's do the following: First there grasshopper above, he will attend the course HTML 5, for example. Then I'll do the grasshopper, watches, will create an instance in the middle, and I will make him attend the course HTML5.

Then I'll put on that black square, you see there in the middle, I'll put the handle Grasshopper, and I will also put the course identifier. I just created a relationship saying that this grasshopper will attend the HTML course. I will also make him attend another course, O.

I will create another instance and I will make him watch for example the course of Word. So just create another connection. The first grasshopper is there watching two courses.

Is watching the HTML 5 course, and the course of Word, which as I said earlier, the same grasshopper You can attend various courses. We will do godofredo there also attend a course. I will do godofredo attend HTML 5, you know, just to show that a course can be seen by more of a person.

Then I will make this relationship there, O. I do godofredo point to watch, and watch point to HTML5. I will also make the godofredo attend a second course, which is of course PHP.

Then there're the teeny upstairs're watching two courses, and godofredo will also watch two courses. And here comes the Dolores and will attend 3 courses, going right away, I will make three references to it here. She will attend the course HTML5, PHP course and the course of Word.

It was a little messy, right ? ! But only you pay attention it gives to understand cute.

Pay attention here O. Each locust can attend various courses. So has this guy watching two courses, has this guy watching two, and this one watching three.

And each course can be attended by several people. Here for example O, HTML5 is being assisted by three people. PHP is being assisted by only two people, yellowing and green O, the godofredo and Dolores.

And the course of Word is also being assisted by two people who are red, it's little boy the ringlets gold, and green, which is Dolores. I just explain to you how it works a relationship many to many see in practice. Now we put data in there.

How do I put data in this table? The thing is actually quite simple. I will insert records as I taught you in previous lessons.

I'll use the "insert into" this table. Then I'll put "insert into gafanhoto_assiste_curso" so I can enter the data. And I will put the data.

For example, "default" 03/01/2014 "," 1 ', 2' ' What does it mean? I'm saying this "default" identifies that my id Grasshopper assists course will be generated automatically, I'm saying that a certain grasshopper which is the grasshopper 1, she began to attend the course 2, which are the id's grasshopper, on the first of March 2014. Get the idea?

So let's add this data directly into our bank. We do there, O, "insert into gafanhoto_assiste_curso values" I think I forgot the "values" there on business. The "values" is, let's put here, "default", without quotes, please, the date will be 2014 March, first day User 1's doing the course 2, Ctrl + Enter, added and now we can give a select here to see if it was.

"Select * from gafanhoto_assiste_curso" ta seeing? "You have two options, grasshopper and gafanhoto_assiste_curso. I want to see that this here.

So I got there, the locust 1, attended the course and 2 began on that date here. So let's do the following, we will facilitate ? !

Let me give a pull up here. I will register some people, for example, 22. 12.

2015, 22 3 students began attending the course 6. I have 60 students and 30 courses have if I'm not mistaken. I'll put here 01.

01. 2014, the grasshopper 22 began attending the course 12, and also in on 05/12/2016. I will do another student, O, the student 1're also watching the course 19.

So you see? I created this relationship. Let's click below on "apply" never forget.

"Apply" again. Finish. And now the data is already registered.

The big problem is this, I give a "select" in this middle table, that my relational table, and what appears is a lot number, what I want is a listing, the guy's name, the name Grasshopper, and what course he's doing. This is tricky, because a're on one side, the other're another and I have a table in the middle to kind of, mess up. In fact it will help you, but the big problem is how will I gather it all.

The answer is that all the joints. Remember the last class when I taught join ? !

We will use this class especially the inner join, which is the traditional join. If I just lay join, is an inner join. We've seen it before.

Today we will use a concept that we have seen in class last to join a join in the other. Calm down, there's nothing too hard, let's go party. Let's start with a simple select.

First of all I'm going to put the body diagram relationship for you to remember. But I put in a simplified way, just so you remember. But I'll put it in a simplified way, only with the keys.

So you see there the locust only with your primary key, the course on the other side only with the key primary, and only watch with their foreign keys. That is what will matter to us here. Let's start our select.

I will put select * from locusts and will put "g" as a nickname, and I will join this table of locusts with the result of assists. So I'll put gafanhoto_assiste_curso join, and we will call "a" of watches. Then the "g" will be the nickname for grasshoppers, and will be the nickname for grasshopper-watching.

If I put just like that, we've seen in the last class that will give a tremendous mess. I need "on" clause. What grasshopper joins watch?

I will join the primary key grasshopper with the foreign key assists. Then I'll use the id of the grasshopper with id grasshopper watch table. So I'll do next, O, on g.

id, this is the id of the grasshopper, equal to id grasshopper table "a", which is the gafanhoto_assiste_curso. Beauty? !

We will make up there for us to see how the business works. So let's come back here to normal. And I want the following here, O.

Let's create a new select here. select * from grasshoppers, with nickname "g" will put join, which is the inner join. He will join references and two with gafanhoto_assiste_curso.

Beauty, with the nickname of "a". And I will use the "on" here to be able to connect. I'll call g.

id with a. idgafanhto Bridge and puts comma, Ctrl + Enter. He will show me the Grasshopper data, and here I have the id Grasshopper and the id of the course and the date.

From now on, it is given the assists. From now here are the Grasshopper data. Let's take a filtered here to show only the name Grasshopper, g.

nome. I also want to show the Grasshopper code. idgafanhto.

In the table "a". I will also put the g. id, which is the Grasshopper identifier id.

Look here. I showed the id of my grasshopper, which is 1, and I caught the id grasshopper there watching the table that which also came. Let's add here too, the id of the course.

Ctrl + Enter. So I have this relationship, idgafanhoto, which is the connection I'm using. This is always equal to this.

It must always be equal to that because I used the inner join. So I'm saying here that Daniel Morais's doing the course 2. Emerson Gabriel's doing the course 6.

Guilherme de Souza's doing the course 12. So I can now to get the id here to become clear to us can understand. I'll get here.

need not show the id of the grasshopper, we have seen how it works the business. Then Daniel Morais's doing the course 2, Emerson Gabriel's doing the course 6, William 12, and Daniel Morais're 2 times. Let's put this here in order.

order by g. nome Daniel Morais's doing 2 courses, 2 and 19. The Emerson Gabriel's doing one course and Guillermo Souza's doing one course too.

But there, Guanabara, I do not want the code of the course, I want the name of the course, and the course name're in a third table. How do I do to pull the course name there from the other table? For over a join, my dear!

Attached here with his uncle, let's step by step. So we stopped at the moment here. I joined the id of the grasshopper, the id of the grasshopper's there in table watches.

Now I'll have to get the data there of course, there the right. So I'll use another join, I join this I'm going to join with courses, and I have to say on. How am I going to mount this on?

I want the id of the course will join with the id of the course assists. I will relate the primary key to foreign key again. So I will do so, on the watch the course of the id will be equal to the id of the course the course.

Confused? Let's practice now you understand. So at this point I'm only showing the id of the course here, but I want to show the name of the course.

How do I do? You have to come here after that on, and I will do a join. Go down the order by, okay!

With courses, I will give the c surname, on, and then I can choose either side. I'll do here c. idcurso = a.

idcursos. I made the junction here. So the idea is that, with this I can join the two together.

Let's see if this works. So I put here c. idcurso, a.

idcurso, Ctrl + Enter . . .

He gave an error here saying that id. curso is ambiguous. Oh yes!

! Here I put only id. curso.

I'll put a. idcurso because I have idcurso in both I give Ctrl + Enter. Now it worked.

Just give Ctrl + Enter, it does not show the name of the course. That's because you did not show him the course name yet. I'll put here c.

nome, and will show the name of the course. Here's Grasshopper name here is the name of the course. Ctrl + Enter.

Now I have. Daniel Morais's doing Algorithm and Networks Emerson Gabriel's doing MySQL. Guilherme de Souza's doing C ++.

♫ Transition effect. ♫ Saw? Now I'm managing to pull 3 tables data.

With that, I need to use the two join's. This kind of concept is already a little mies complex, and is beginning to enter the most difficult MySQL And that's why we closed the course here. I showed you, most things MySQL It has fundamental.

We saw the beginning of the relational model, that is, we saw almost everything the database basics. Of course, I say again, with this course you can create simple database and even intermediaries, but if you need more advanced database, you'll have to delve into concept, will have to study more about the relational model, a little more about attribute types, more relationships, create ternary relationships . .

. You have much more thing to learn. This course is an introductory course database with MySQL.

I sincerely hope, heart, I've helped you, I have contributed to their learning, I have shown you in a more fun way, a little more relaxed, a little different from what your teacher used in the classroom. I am very happy to receive the reports, the crowd that comes to me and says, "Gee, Guanabara, you helped me hell, I was not understanding the concept and now I understand. " I am very happy.

And the way to ask for your thanks is always you stay here the Course on video. Subscribe to this channel just for signing up is of no use to me. I could come and talk like this: Look, let's do a promotion here, I want subscribers.

Call your grandmother, your grandfather, your uncle, your aunt. Yeah, but they are not interested in what I'm talking about. So I need active subscribers, I need members who always watch the videos nosos.

What does a growing channel is not only signing it, is watching his videos, is appreciating the ads that are appearing. It is giving view. "Oh no, this course here I do not want to do now!

" It gives a view, see if you can at least give an introduction to the concept. We're now doing other types of video. We're doing the course in video replies, we're doing those vlog's trip, where you follow my day-to-day things that I do .

. . So I really want to invite you to become a frequent visitor of the course on video, YouTube video channel in progress.

You can be sure that you will make the course grow with the channel grow and we get increasingly advertisers, and getting more and more advertisers will have more and more courses Once again I bid you farewell here, but it is not that feeling of sadness, is a sense of accomplishment, the feeling I have managed to do a course with 16 lessons, you can be sure, It was the best quality I could give you. I made a tremendous effort, we started recording with a lot of noise, a lot of work. Thank God this work is over, I to recording this 16th class, look at the silence, listen to the silence .

. . .

. . But I struggled as much as possible.

I'm sorry it was missing something, I apologize if some concept you could not quite understand. Watch the new class, consult your teacher. Your teacher he is not useless.

I never said that, on the contrary, I always valued many teachers. Teacher, want to use this material? Yeah there in cursoemvideo.

com site, there you will be able to download the slides, access all cute. You can use my content, you can use my class, my videos in your classroom, provided it is kept . .

. You can not download the class and deliver to their students, but send the link to them, speaks to that assistas, pass as homework . .

. Grasshoppers, watch, glorifies, reclaims his teacher, this guy shows the course for you. And if you're a grasshopper who discovered the course and his teacher adorably not know, show your teacher too.

Let's do the double way here, teacher shows pro student, student teacher shows pro and we grow, we evolve, and sure enough, I'm contributing pro better education in a'rea technology. At least I'm trying to do this. Again I bid you farewell, this time we will come back with other types of courses, with other content.

Give me that trista the heart can not write any more database class. We are saying goodbye, unfortunately the godofredo, Dolores and Godolores, that beautiful thing Cuti Cuti Cuti titititi . .

. And that's how I do I close the course in database video. See you next time, I hope you like it.

It has a really cool thing that next course We're leaving. We will start a new series, a new subject, the classes are already being recorded, but I'll save the surprise because it will be pretty cool. I want to thank everyone here who by their efforts, helps the course in video, I wanted to thank here Yuri, Ariel, which make the cuts in class.

This lesson's being cut by Yury, Sincerely, Yuri. I also want to thank all these visual things that appear on the screen, I wanted to thank Ramon and Ina, who are our leaders, our finishers, the risks that appear on the screen . .

. all this was produced by a team, I did not buy an animation package, because these packages do not there with this quality we produce here. So values, even values ​​the independent content production we doing here in Video course.

Sincerely, little grasshopper! Study always, never stop studying See you on the next course with much more information for you. A strong hug and the next course.

Watch our courses always the paylist.