Curso MySQL #07 - Manipulando Linhas (UPDATE, DELETE e TRUNCATE)

🎵Opening of music🎵 Hello, little grasshopper! Welcome to another lesson of their course in MySQL Video. My name is Gustavo Guanabara, I am your teacher!

And this seventh Database class we'll work a concept continuing what we saw last class. And this time we will learn to manipulate records. And in other books or tutorials you can find this with other names.

It may also be manipulating lines or manipulating tuples. All these are synonymous. You can see.

. . Thus, registration is the most common to appear, but if by chance is quoted line, the line concept is the same as the record.

It is a line of the table. You already will ever see and you will understand why, we call line and why the fields we call columns But a name that is very curious is the name "tuple". Also refers to a line from a database.

or else a database record, so . . .

so if by chance, somewhere up the line term, record or tuple. You know you're talking about the same thing. In one of the previous videos we saw how to add lines.

Did you see the command "INSERT INTO". What some people call "inserti INTU" So, you, little grasshopper, already know include line. What?

! You do not know include lines ? !

So what are you waiting for, my dear ? ! Here, look, it has a playlist.

If you fall in this class handling records, there know why. Know that there is a complete course. And you need to follow this course.

If you do not know how to use the INSERT INTO command, right yet . . .

You click here in the middle, look. You will get the playlist. And here in this playlist you will have access to this class.

This and many other classes . . .

As I said right at the beginning this is the seventh database class. So you already assumes that there are other 6. I guess.

So let's stop talking and let's work. I'm already here, with my open WAMP. I'm already here, with my open Workbench.

And if you come now, certainly it is lost. So back to give tip: watches playlist, my dear, you will understand everything well, you will learn MySQL correctly. So if you see here, I'm not with any open database .

. . I'm with the bank "test" here, which is what comes in Mysql and I am with the "register".

Does not exist, because none of them are in bold. To open it, you have two ways: or you double-click, as we are doing, if you double-click it opens. Or, you boot the command "use register;" Press Ctrl + Enter.

He has opened the registration database. That simple. Here you will take a look, we already have the tables "courses" and "locusts".

The table "locusts", I have the columns: id, name, profession, birth, sex, weight, height and nationality. And its database has to be that way, and in the table courses . .

. I have columns: id of the course, name, description, the time course load, the total classes he has and the year in which it was released. And if we come here and give, "select * from locusts.

" Press Ctrl + Enter. And it will show you here, Godofredo, Maria Creuza, Adalgisa, Claudio, Peter and Janaina. What do you have against the names I put here?

But take a look here on the screen for you to understand right what line and what column. If you pay attention . .

. Our list, you know, our "result set". Laugh He liked the name, right ?

! The name of this is "Result set"! Which is the result of an instruction.

In my case, here, the "select" statement. If you pay close attention, my lines are my records here Oh, just select the record of Maria just select the Claudio record So all that is online is record everything in column is field see here O, "birth", all this here is my column "birth" all this here is my column "weight" all here is my column "nationality" and so on. Then guard there in your little grasshopper head.

The lines are tuples or records Columns are my fields and my attributes What I want to do here is to manipulate lines if you want to manipulate columns I've taught you the command. Go to class ALTER TABLE you will understand. The ALTER TABLE command allows you to change the columns.

The commands you will learn here will allow you to manipulate lines. We see how we are moving forward here? Let's take a SELECT here in "courses" CTRL + ENTER And since changed Oh!

We see here that we have no line It has a asteriscozinho here in front that indicates that it has no record of any So what we'll do here, first of all is to include new records, we will include new courses. And then little grasshopper, to facilitate and to speed up the class I already typed a command if you pay attention here Oh, give a look here on the screen. Already have a typed command.

I'll show it to you. What I created here, it was an INSERT INTO giant. Oh!

Enter the table "courses", the values ​​and set several records with values. Found difficult? It is a sign that you did not attend class INSERT INTO right Take a look Oh, up here.

I'll give you a moral and'll put here the class of INSERT INTO, then as who does not want anything, click here if you have difficulty, click here to watch continue in that class, if you feel some difficulty Click here and watch the class INSERT INTO before. So this command here will add multiple records if you pay attention there are some typos here oh. "Jarva".

And if you watched the video course responds. You know what I'm talking about. You do not watch video course respond?

Will take a look there in the Play List It has an exclusive Play List only course in video answers I put here too, I'll give you this moral Have here Oh, "PGP" in place of "PHP" So have some little mistakes, I put on purpose. This is because we will handle lines. What I recommend you is the following.

Pause the video. You want to see! I will go to the screen Then oh, at that time, pause the video and type in your MySQL WORKBENCH this command This is very important because we need these lines, to be able to handle And you know only learn how?

Practicing. So stop laziness. I will go back there.

You pause the video. Pause the video! I'll wait in!

- Go, pause the video! Break! ✷Esperando.

✷ (Mute) will not pause right! ✷Risos✷ I hope you have paused. I hope you have entered.

Just so you will learn! So, I put here oh. I will give Ctrl + ENTER Pressing Ctrl + ENTER he has added.

And let's take a SELECT here now again ! ? SELECT * FROM courses CTRL + ENTER Now Oh, I already have the data included ✷Beleza!

✷ So, I consider that we are already at that point. So to make it clear. Our database has records of both.

"Locusts" and "courses" The "locusts", we have the following structure. We have these attributes. And if you notice the "id" is underlined there.

That why he is what? Primary key And the structure of "courses" is as follows idcurso, name, description, load, total classes and year. Then, after making inclusion.

Our course schedule is with this structure I put the columns there And I put the lines We have various online that were included With that command agent just used realize there are 10 records that were included including typos Errs in typing for you also keep track of what we're doing here to make your life I will mark the mistakes I made here So I put there, look there! The course "HTML4," is not "HTML4" is HTML5. I put there Oh, "PGP" which was done in "2010" was not "2010" right!

It was the course of PHP that was done in 2015, and the course "Jarva" which're "Jarva" that's with the load "10" hours, which are not "10" hours are 40 and tá year "2000", it is also in 2015. If you see you have some 2018 courses that are half Trolling calm it will be necessary also, type, type it all the time you paused So the first thing we have to do, is to manipulate these lines I can not handle multiple lines at the same time a command, handles a line but I can not move within a row, in multiple columns at once. Let's learn how to do this!

first thing we will do is modify the line 1 there Oh, the "HTML4" in fact the only thing that has to change is "HTML4" to "HTML5" The command to do this is very simple then there Oh, put on the right, all the changes that we have to do let's start with line 1 and if you noticed, the column of "idcurso" is checked I'll explain why. So look, column "idcurso" I put green to identify Let the command! So you can modify a row You will use the UPDATE so I'll put UPDATE "courses" "Courses" with the table name and I will put SET name = 'HTML5' this is I will modify the table "courses" the name for 'HTML5' ta see that name there is name?

so I'm changing from HTML4 to HTML5 But beauty. How will I identify which line will be modified? It is also easy, just you be based in one of the fields.

In my case here I will base myself on the primary key field, 'cause then I'm sure there is only one line. Remember? Primary key that identifies each record .

. . identifies each row.

There are two rows with the same primary key. To do this you, complete the command, you will put: WHERE idcurso = '1' So look at that! We go to a small class of fast English.

UPDATE is update, SET is set and WHERE is where. So let's try to read in Portuguese. Modify the courses, setting the name to 'HTML5 where "idcurso 'is equal to' 1 ' You see how it is simpler when you simply translates from English pro Portuguese!

Then I'll modify the table "courses" I will modify the contents of the "Name" column for 'HTML5' Where "id" is '1' We will enter and see what happens So I've been here with my open SELECT, and will enter the command Oh, I cleaned the previous command and I type this now in fact you do not even clean up right, I just cleaned up to class become more cute So come on! UPDATE, the table name table name "courses" UPDATE "courses" setting the name for 'HTML5' remembering that this is not crase or acute accent, it's single quotes where (Where "idcurso '=' 1 ') That simple! Then I'll change where the "idcurso" for = '1' there is only one line that "idcurso 'equal' 1 ' can search, there is no other and will never exist because "idcurso" is the primary key, do not forget that.

Then it will modify this line here I'll press Ctrl + Enter he disappeared SELECT Oh! I'll put down here SELECT * FROM courses; Ctrl + Enter Ala Oh, what was 'HTML4' now it's 'HTML5' You see how easy it is? Just manipulate a row but do not stop there not, we will continue if you notice, the line 4 that is the course of 'PHP' first the name is 'PGP' and the year is as '2010' I have to change both at the same time How do I do that?

It is also easy, let's start the command to start the previous Allah! UPDATE "courses" but now my the SET will be different look at that! Considering the line 4 there SET name = 'PHP', year = '2015'; You see how easy it is?

I just put on the same line all the separate changes by commas but in which I will change? Now I will indicate in which WHERE the line I'll change WHERE "idcurso" = '4'; Let's enter this command. Come on Oh!

UPDATE "courses," modifying "name" for 'PHP' and "Year" for '2015' where "idcurso 'is' 4' Then it will change Oh! This line here Oh! It will change.

The "name" to "PHP" and "Year" for '2015' Click on the line. It can be anywhere As long as it is within the command Ctrl + Enter He modified. To verify that really changed We will select here the SELECT Ctrl + Enter Note here Oh!

I modified for 'PHP' I changed to '2015' Now it's easy right! We will continue, which is over the wrong line. If you notice there Oh!

The fifth line is also wrong because the "name" of course is 'Jarva' "load" is not '40' and "year is not '2015' And you're thinking. For that you will spend time talking about this is the same command is like but I will add another parameter to be able to limit the action of the command. So let's start!

UPDATE "course" SET "name" 'java' and 'load' '40' and "Year" '2015' WHERE "idcurso" = '5' all the same, all perfect, without any problem I'm simply modifying three columns then separate them by commas you are a smart grasshopper and already understood this the fact is the following, with the UPDATE, if you move the primary key, you just move a line but there is a possibility, there is the "RISK" you move in various online at the same time If this is the case. For example. I want to change all I want to change every year to '2020' or all of the courses that have '40' hours, I want to change the name to ✷sei lá✷ "Paiton" I can do it, and that's dangerous!

you will be able to do is if you really want to limit to a line you can use a special parameter, and easy to understand at the end of the command I'll put a LIMIT '1' and I put a semicolon LIMIT this is to limit how many lines will be able to be affected then watch here! I want to change: I want to modify the course name to JAVA, I want to modify the load to 40 and I want to change the year for 2015. Bad I do not want this change affects more than one line if I have the wrong command.

clear that if I use the primary key will only affect one line. So LIMIT, it will allow me to limit the action of my command Let's see how it works Then I put there O, UPDATE courses Change the name to 'Java' the year for '2015' and load . .

. to '40' I do not even need to put in the same order Where id Oh I want to change that here, where the id is '5' But I'll put here oh, um . .

. limit 1 him . .

. limit to a record only CTRL + ENTER Let's take select And here you realize that he has changed to 'Java', '40' hours, and '2015' Until then, you're thinking: "The result was the same" Look how the update can be dangerous I will move directly on these lines here O I will move these three lines, you realize here that a standard feature of them It is that the course is 2018 What actually not happened yet I'll make here an update courses Modifying the year to 2050 And the load to 800 Let's exaggerate, let's exaggerate Only instead of touching the idcurso I'll move . .

. where year equal to 2018 What I'm doing here? this while it will not only pick up a row It will take All three lines oh, it will put These three lines For that, of course I'll take the 'limit' oh, so If I give this update here It will modify all these records OK?

Let's prove it! Thanks! he gave error here oh This will happen to you too Calm that was not a typo not This is a WorkBench Protection For you!

Let Off this protection (For Inquanto). I recommend that you keep it on! by default The Workbench only lets you make updates In a line Messing here on the primary key To modify it You click Edit Prefences (Preference).

And on the screen that will open Click on 'SQL Editor' Below, you have a Check What is the 'Safe Updates' What are Updates Secure. I'll clear because we do not want secure updates We saw how dangerous it is? Even tool here already blocked me.

click "OK" click this button here to reconnect press Ctrl + Enter Do not forget to click connect and now we will give the select to see what happened Click here Ctrl + Enter Let's close the window down here oh, just click on the top And now you will realize, looking only . . .

All courses were in 2018 They stayed with the year 2050 and the hourly load of 800 I do not know if you could understand how dangerous this is. For example: Imagine that your database has the registration of its 5000 customers For example: I have a Bank of Course Data Video of 120,000 students incritos And I give a wrong update and it changes neighborhood everyone to Madureira for example imagine the risk that happens, then why the Workbench has the Save Update We temporarily turned off and I recommend that after you make these classes you call again Yes while you're in class keep it off you can play with business But it is very important that you keep for example a backup of your database That's why the next class will teach you that How are you going to create a copy of your database here So Small grasshopper very careful when using the update It can end your database and there you need to have a backup for that So let's do this, let's do an update now with the limitation for you to understand Then you notice the following here, I will update the courses where the year is 2050. '' Ne ''?

I now have 2050 I have several courses I will change to 2018 again and I will put zero hour charge If I do that it will change, how many records have in 2050? Beauty I come here and here So I have these three lines here in 2050, then I give this command here It will change all that for 2018 and all this here to zero But now we see the limit working, I put down here oh, '' limit 1 '' What will happen is this, even though I have these three line with 2050 It will limit will only change the first one let's see how it works I will give Ctrl and Enter here, then immediately select the Note that I still have two line with 2050 It is the change that I sent to 2018 and 0, only happened in the first row. This is how it works limit, then the limit and safety.

The tip I give you is, try to avoid using the update. Especially in your database to be currently active. It is if you're going to use, connects the server update there directly on the preferences of your workbench Combined Then, and how it works to update records So after all that we did, our table as you just saw, be that way.

All "alteratinha" all cute, almost all cute right. This is because they have three records that I do not. if you pay attention , This 3 records from below They are completely useless, I will not make progress on video, tap dance or Arabic cuisine and not a course to teach you to be "YouTeber".

So I do not want these lines, I want to delete these lines, then you can think of, Gives an update by placing all empty.