Placeholder Image

字幕列表 影片播放

  • creating these classes requires equipment and service.

  • Is that cost money?

  • If you appreciate this education, please think about going to Eli the computer guy dot com and offering a one time or monthly recurring donation.

  • Welcome back.

  • As you know I am.

  • He lied to Computer Guy, and in today's class, I'm going to be showing you how to up today and delete records within tables within your my sequel database.

  • So at this point, you should know how to be create databases.

  • You should know how to create tables.

  • You should know how to in a certain records into tables and how to use the select statement to be about pole records out of tables.

  • So at this point, we're going to show you how to basically modify those records.

  • So in the previous examples, and basically I put a number of kids as records into the user's table that we created.

  • And so when the questions is, what happens if one of those kids get older?

  • You know, they went for me nine years old, and now they're 10 years old, so you need to go in there and you'd actually be able to modify that record So that is what I'm going to show you today.

  • It is basically using what is called the update commands.

  • You'll do update on.

  • You'll be able to update records within your tables within my Siegel.

  • Then, of course, you know what happens if a user leaves, or for whatever reason, you want to delete a record within your my sequel table, I will be showing you how to use the police statement in order to do that.

  • And so that is what we're going to be learning today.

  • So now that we're ready to learn how to be ableto update records and delete records within your my sequel database tables, let's go over to the computer so I can show you how this works.

  • So here we are, back at my lab environment again, I'm running a Mac book professional using virtual box within virtual box.

  • I have spun up an instance of a bunch of desktop 18.0 force that's going to give us a graphical user environment, and this is what I have installed my sequel onto.

  • So if you have my sequel installing on Tween normal machine, that should work fine for you.

  • I guess to explain what's going on here.

  • You go down here to show applications, then we're gonna type in terminal because we need to get that command prompt when you the terminal.

  • And now we are in the terminal session so we can actually get into my sequel.

  • Now, if your brand new to this series of classes you have not watched the previous series of classes.

  • I don't log in to my sequel the normal way.

  • So the normal way of logging into my sequel is my sequel Space Life in You User name Space Hyphen piece.

  • So this is the normal way that you will be along in N to you're my secret database server.

  • We have not created user accounts yet dealt with privileges and all that.

  • I'm kind of teaching some way, some would say and have an ass backwards way, But I think it's an ass front.

  • Words away.

  • I don't know that came out wrong.

  • The way that I'm doing it right now is to do my sequel.

  • There are no do do pseudo pseudo my sequel.

  • So what this will do Is this Well, actually a log us in as the root a local host account, eh?

  • So this gives us all the full privileges on this particular database server.

  • If you already have a normal user account on, you're my secret database server with the appropriate privileges.

  • Deal with that.

  • I'm just doing it this way because that's how this particular series of classes is going.

  • And it's hit.

  • Enter super secret password.

  • 123456 on You Hit Return and you were in my sequel.

  • Uh, so I thought I talked about the previous class that whenever you're doing thes these little labs that you should try to recreate your database, recreate your table's recreate your rose or your records for every class to get you used to actually doing that.

  • I'm not going to do that today for this class because I don't want to waste the time.

  • I'm simply going to be using the database and the table that we already created in the previous class so we could go here so show that base is a semi colon and we could go take a look at our databases.

  • The one that we're gonna be using here is class the D B.

  • That's not be our database we're gonna go use, uh, Class B.

  • Now we have changed into the class d be allowed to control l to clear the screen.

  • And then just to make sure that we are in the right database, will d'oh select that a base, this command open brothers, he's close parentheses, semi colon enter.

  • And so this just shows us that we are, in fact, in the correct data base.

  • This is important, remember?

  • Remember, in the my secret world, they don't give you warnings.

  • They don't ask you if you want to do something, you type a command, and it does the command.

  • So if you're in the wrong database, you can get bad really quick.

  • So always, always verify you're in the right past that.

  • Then what we're gonna do is going to show the tables to see what tables we have in here.

  • Okay, so we have a user's table.

  • We'll use the describe command.

  • DSC users could get an idea of what the user's table looks like.

  • So again.

  • So for the user's table, we've got to use your idea.

  • You got a name?

  • We got AIDS.

  • We have a gender user.

  • I d is an entity air and it is an auto increments.

  • That is our primary key name is text Ade is entered.

  • Your and gender is test.

  • So this is important again.

  • One of the things that's very important is if you're going to be updating records to know what data types you can use to be able to update.

  • Is it a text?

  • Is it afloat and again, like with interviewers?

  • Right End is a whole number 11.

  • 25,000 and 25.

  • Those air enters floats or decimals would be 10.25.

  • Ah, 100.

  • Not 50 1150 not 25.

  • Right.

  • So if you have a decimal point, that would be a float.

  • So this is important, something very be thinking about.

  • Like if you have something like a JJ, if you try to type in, you know, 11 dot that 11.0.5 for an age, you might run into some problems.

  • So you do need to make sure you know what data types are dealing with here.

  • And then finally, just to get an idea of what the dad looks like with our database, would you select all from users?

  • So this is select all columns from users and we don't have anything else.

  • Nowhere statements.

  • Here s oh, it's going to take all the records and then we're going to hit inner and so we can see the user I d.

  • So number one is Bob, ages nine under his boy, Sue is he's ready to age 15.

  • Fred, this is righty 388 Gender boy on this is kind of interesting.

  • Here.

  • Just kind of see show you how this works.

  • So before I did the Chris class, I do a little Testing has make sure writing out all the statements properly.

  • And so I was the leading and adding some records.

  • And so one of the interesting things just to realize with this whole auto increments or user i d auto increments.

  • Every time a new record is created, it ticks up one place.

  • So you'll notice Pat.

  • Even though Pat is in the fourth position, the user i d is six because we've actually this point created six user ideas.

  • I've simply deleted a couple of them because I don't need them anymore.

  • But it is just kind of interesting to look at that from the auto increment standpoint to realize that Pat is not user.

  • I d four is actually user i d six and again when you're doing things like circuit is when you're doing things like updates, keeping that in mind could be could be important again.

  • Just because they're in the fourth position doesn't mean that's what, like their user i d or the part I d or whatever ISS s.

  • Oh, that's basically what we're looking at here.

  • Uh, so let's do the clearest.

  • Great.

  • Yeah.

  • So clear the screen, and then we just do the slept all from users.

  • So we're taking a look at this.

  • And so let's take a look at this thing up, OK?

  • So let's look at Bob.

  • Let's say bub has increased in eight by one year.

  • So Bob is now 10 years old.

  • Yeah, Bob is 10 years old.

  • So how are we going toe up a date, Bob?

  • So the way that we're going to update Bob is around to update, I'm gonna say the user's cable, so whatever the table is user stable and we're gonna set, um, So what we're gonna do?

  • Look, here is a JJ.

  • So we're going to say the column A.

  • We're gonna set 8 to 10 right?

  • And then we're going to say where, uh, for this gun, it's a user I d.

  • So use a righty.

  • Here is one equals one, and then we're gonna do a semi colon.

  • It's out.

  • Since these are numbers, since these air integers, I don't have to say to put single quotation marks around them.

  • I can simply put a number there, and then I will hit enter and we see a query.

  • Okay, One row affected, blase, blase, blase s so that if I go up, I do select all from users again.

  • We hit enter.

  • And so now we can see that Bob's age is now 10 years old, where it used to be nine years old in the past.

  • So that's all you have to do to be able to update a record.

  • So what you do is update the table, so update the user's table set Whatever column it ISS.

  • So we're doing a here, eh?

  • So we're gonna set it to be equal 10.

  • So the age will be 10.

  • Where and then, user, I d right.

  • That's a user, I d their equals one.

  • And then you do the same, Michael.

  • And then it's able to update Let me clear the screen.

  • And now again, let's do the select Oh, from users.

  • And now let's say I want to change a string.

  • Right.

  • So string is just where you have to use the single quotation marks, Eh?

  • So what we can do here is ah, update users, um, set, uh, said let's say name.

  • Um equals, Um uh, Billy Bob's.

  • Let's say Bob just can't.

  • His name's We now want to call him Billy Bob, and we're going to close the single quotation marks.

  • So again, when you're dealing with text, you want those single quotation marks and they were going to say where and again user underscore i d.

  • People's one there do semicolon there and then we're going to hit enter.

  • Hopefully everything's as query.

  • Okay, again, if you fat finger it and you put in anything in here wrong, it will work, right?

  • Uh, up So again, select all from users were going to hit in her.

  • And now we can see that Bob's name has been changed to Billy Bob.

  • So that is a basic idea of how to do update so again, when we're gonna be updating a number, so is an interviewer or afloat.

  • You don't have to use those single quotation marks.

  • Whereas if you were trying to update again a string, something like a name or a description or something like that, you will need to use the single quotation marks.

  • So with that, let's clear the screen again.

  • So now let's look at updating a record based off with a value of two columns.

  • Right?

  • So, uh, so one of problems you run into the my sequel world is as soon as you put an update.

  • As soon as you put a change through, it just goes through again.

  • You don't You don't get a question of Do you actually want to do this?

  • So imagine if you have multiple records that are similar, and so you're a little careful.

  • You're not sure if you wanna update based off of a single column alone.

  • Maybe you wanna update based off of a couple of different variables, right?

  • So we go up, we take a select all from users and we take a look and let's say we want to update sue.

  • But we're worried, right?

  • So we want to update Sue, but we're worried that there might be a couple of other shoes in this particular table.

  • So we say update, Sue, It will update all suits bit also So we don't wanna update all suits.

  • So what we're going to say is we want to update Soo, who also has a user idea of two.

  • And then we're going to change her age to 16 s.

  • What we can do is they do up date as we did before the user's table set a to equal with you now 15.

  • And so it's going to be 16.

  • Ah, where user i d equals two sues user I d equals two, um, and name equals Ah, single quotation Mark Su close single British mark because again, that is a string.

  • And then you're going to do the semi colon.

  • So basically it is on Lee going to update the age to 16.

  • If the user i d is too.

  • And the name is Sue so again, especially when you got large tables right now, I'm showing you a table of four records in it.

  • This may seem a little bit ridiculous, but imagine if it was 40,000 records.

  • So you really want to make sure If you're updating the record, you really want to make sure you're updating the right record on we got query.

  • Okay, so everything went through.

  • If you go up when we take a look at select all from users, we can see that Sue is now at age 16.

  • So that is where you can change of basically that one.

  • That one value based off of a long as two columns are correct so that let's do control, elegant, clear the screen.

  • So now the next question is, is how can we update multiple values at the same time?

  • Right.

  • So if we go up, we do Ah, select, you know?

  • Oh, all from users.

  • Semi colon again.

  • And so, uh, let's say we have a friend here.

  • So Fred, Fred, Fred has had a birthday.

  • Fred is now nine years old, and Fred doesn't like to be called friend anymore.

  • No, he's 99 year olds get to be called Freddie, so he wants his age to be updated to be a nine, and he wants his name be updated to Freddie.

  • So how do we do?

  • That s o use the update command again.

  • Uh, then we do users table.

  • Uh, Then what we're gonna do is we're going to set name equals single quotation marks because we're dealing with a string here, and we're going to call him Freddy.

  • Close single quotation marks.

  • Then we're gonna put a comma in.

  • Don't forget to that comma, if you don't put a comma there, there is an error on.

  • Then what we're going to do is we're gonna say aid equals nine.

  • So we're going to change his name to Freddie, and we're going to king his age to nine.

  • And they were, Say, where?

  • User underscore I d equals.

  • Make sure this is right again.

  • Make sure this is right.

  • Three s.

  • So we're gonna do three, and then we're going to do semi colon.

  • Then we're going to hit.

  • Enter query.

  • Okay, one row.

  • Effective, blah, blah, blah.

  • So you do select all from users again, and we can now see that user i d.

  • He is now called Freddie, and his aide is now nine s.

  • So this is how you're able to update.

  • Multiple variables are multiple values within, uh, one update command.

  • So now all I have to show you is how to be able to delete records.

  • Eso again.

  • We're gonna type in that select all from from users again just to see what all the people in the users are.

  • We're gonna take a look at this and we're gonna look at Pat.

  • Oh, Pat just can't make up her mind.

  • Is she in?

  • Is she out?

  • So she's decided to leave.

  • She decided to leave our little school or what we have.

  • So we're going to delete the record for past in order to lead the record for paddle we have to do is we just simply do delete from and then the user's table So delete from whatever table that you're dealing with, Where, where and then, let's say use user underscore I d equals six.

  • So Pat has a user idea of six.

  • So we're going to delete her.

  • So six and then we're going to do semi Colon s o.

  • This should delete pat for us.

  • Then we're gonna hit Enter one row Affected.

  • Okay, So they were to select again.

  • Oh, from users semi colon.

  • And now we can see that Pat is gone.

  • Now again, just like with the Update command.

  • We can also put in multiple multiple conditions.

  • So if column one is true and the column two is true, then do whatever it is we want to dio esto.

  • Let's take a look at Sue here.

  • So let's say we're going to delete Sue.

  • So if user I d equals two and name Equal Sue, then we will actually do that Delayed.

  • So again we do the same elite command from the user's table where and then we're going to say user underscore i d equals to pursue.

  • And so we do.

  • And here, uh, name equals and then single quotation Mark Su Close single quotation mark.

  • And then we do see Michael so delete from the user's table where user I d equals two and name equal Su enter.

  • OK, eso that should go through.

  • So if we do select oh, all from users again Semi colon.

  • Now we can see that only a 1,000,000,000 Bob and Freddie are there now.

  • The next thing I want to show you is why you have to be careful with these update and elite statements, because if it's true, the my sequel will do it so with that.

  • Let's let's clear this great again.

  • And now let's just say we're having a brain for last.

  • You're saying we're having a brain fart.

  • So again, we give select, you know, all from users.

  • We take a look.

  • We're thinking, OK, we need we need to believe, Freddie, we need to delete Friday.

  • But for some reason, I can't have a boy in my head.

  • I'm like, boy s.

  • So what if we do delete from users where, uh, gender equals just simply, boy, as always?

  • Do we guess that we just typing typing boys?

  • So especially this week, this could be a problem with things like wild card characters, keys.

  • You can actually use wild card asterisks, that kind of thing in here.

  • So what if you just kind of do something stupid?

  • You do an update Where you where you put in the wrong column or you do it Delete.

  • Right.

  • So if we hit her now, you can see okay to Rose affected.

  • We're going.

  • Okay, so we got rid of that.

  • We got rid of one of these people, so let's do you select all from users Semi colon.

  • Oh, Oh, Okay.

  • Ah, crap.

  • right, and you'll notice you will notice here.

  • It did not ask.

  • You did not say yes, Didn't say.

  • Are you sure?

  • It just simply to rose affected.

  • It took 0.0 seconds to do it, and now you have an empty table.

  • So do be careful again we start dealing with the columns.

  • This is one of the issues that you need to worry about, because if you do an update or if you do a delete based off of one of the columns, and within that there are multiple values that are identical.

  • Basically, what will happen is my sequel will perform that task on every time that the condition is true.

  • So again, if you had multiple users named Sue and then you change sues aid from, let's say, 9 to 10 right?

  • Remember, my sequel didn't ask what what age was before.

  • So if you say set sues 8 to 10 well, the nine year old will get her age set to 10 and the three year old will get hurry.

  • Set the 10 and the 45 year old will get her age set to 10 any time that value is true.

  • Any time that condition is true, it will go through and it will process, and it will change.

  • He ate two, whatever you've said.

  • So that's why it's important to be thinking about using and command.

  • They're basically and conditional Sze.

  • So if if the name is Sue and the aid is nine, then then changed it 10.

  • But again, you have to be careful there, because what if you have two kids named Sue who currently r are nine years old?

  • One of them just had her birthday, so she and easy Oh, up to number 28 10.

  • But the other one doesn't have her birthday for another six months, right?

  • These are the kinds of things that you really have to think about Not only when your ability at your database, but then when you're doing these statements to make sure that when you type in a statement, it only does what you actually wanted to do, and there aren't larger ramifications.

  • So there you go.

  • You Now how a foal database system sort of, You know, you actually have something that you can now really use.

  • You know how to create a database, you know, how to create a table.

  • You know how to insert records into the table.

  • You know how to be able to surf through basically select records at a table.

  • You now know how to update records in the table and you know how to delete records.

  • So really, when you think about it, that's what the databases.

  • Because obviously we're learning my sequel and really, you're learning a database, but really right, that's about what it is.

  • So So right now we understand how to be a bit interact with a single table as we d'oh, I'll show you the whole concept of a relation A ll database where basically, you have multiple tables and are able to interact with multiple tables at one time.

  • But right now, if you if you really think about it, being able to use a single table might be very valuable for you again.

  • If you have, like little io ti temperature sensors on, they're basically just paying your database in putting whatever the temperature is into the database that might be saying something that you need a single table for again, just a simple way of tracking students for a class.

  • You may only be a single table.

  • There s o.

  • You know, you really should think about it.

  • Like what applications could you create, where all you need is a single table?

  • Ah, now you're able terrible.

  • Create that again.

  • You know, in the real world, you will be using basically a scripting or programming language to be able to interact with your my secret database again, something like PHP that will then create an HTML the front end.

  • So again you have a form, and that form will take information from the front end and put it into the database on.

  • Then you'll be able to pull down out of the database and be able to write that again to an HTML form.

  • But when you really need to be thinking about is basically all that a female form is doing is basically just sending information for the sequel statements.

  • The core concept again is simply being able to add records to be above you records, be able modify records and viable delete records past that everything is relatively simple.

  • With what I show you today again, with the update and the delete commands, you do have to be very careful here because, remember, remember, my sequel is a very specific and very unforgiving, very unforgiving.

  • It doesn't ask you if you want to do it.

  • Are you sure?

  • No, that's for the kids.

  • That's where the Windows world asking, you know, are Are you sure you want to do that?

  • That's for the Windows world.

  • We're dealing with my sequel.

  • You tell it to do something and then we'll do it even if you told her to do something stupid again.

  • If you do, if you tell it to do something like, you know, update sues age to be 10 If you have multiple people named Sue, every person named Sue will get their age to be 10.

  • And so that's why you have to think about how how are you going to identify records within your table?

  • So again, that's one nice thing with the user.

  • I d right, since the user ideas should always be unique, since you're using an auto increment thing so that if you reference a record by the user, I d.

  • Then you will only modifying that particular user idea.

  • Another way you can do it is by doing the an.

  • So if if the user.

  • The name is Sue and her ages nine, then then changed the age.

  • But again, you have to be careful, because once you start dealing with tables where it's not 44 records, it's 40,000 records, right?

  • What are what are the possibilities?

  • You have multiple users that both have the name of Sue and are currently nine years old again.

  • Imagine you have one, Sue, who's nine years old on.

  • And you know, they have six months until they were gonna be 10.

  • Whereas you have another su who is nine years old in the system.

  • But she just had a birthday yesterday.

  • So you need to update the system to have her be 10.

  • So if you then have to records where Sue is nine and both circumstances, if you simply put, you know, update blah, blah, blah, set to 10 where Sue and nine.

  • Well, it'll actually update both of those records.

  • So you really have to be careful about how you think about this again.

  • The same thing is true with the delete, right?

  • If you want to delete a record, if you delete based off of value and then there are multiple records within that table with the exact same value.

  • Then you didn't delete one record.

  • You deleted all the records where that condition happens to be true.

  • So you have to be really, really, really careful how you use thes the's statements because you can screw things up that quick.

  • And so this is where we talk about liking the technology world.

  • There's the technological way of doing things right.

  • There's understanding commands that's pretty using again, select or inserted up day.

  • That's all simple.

  • The complicated thing is, is figuring out how to use those commands properly So you don't destroy your database server.

  • That that gets a little bit more complicated.

  • But this point, you have a pretty pretty good idea how a lot of these things work.

  • S oh, definitely, you know, play around with this and then we're gonna do some further classes to try to get Maur involved again.

  • We're gonna talk about how to have multiple tables were going to finally get around to creating user accounts and basically you flesh out what you know now.

  • But now, at this point, you have the basic concept of what makes my seek will be my sequel, and so you should definitely play around with it.

  • And, you know, again, try things you know, do do multiple conditions, do different kinds of things and see what works.

  • See what doesn't work.

  • Changed data types play around.

  • See what?

  • See how how it does for you.

  • So with that as always, I enjoy doing this class I look for to see if the next one apparently the type of content you just saw is not what Susan W.

  • Wants for the future of YouTube.

  • This means that recommendations by YouTube to this channel have dropped massively, and views are becoming a comically small I hate to ask.

  • I used to say I would never ask, but if you could subscribe like common and most importantly, share the videos that you appreciate, that may help slow the death of this channel.

creating these classes requires equipment and service.

字幕與單字

單字即點即查 點擊單字可以查詢單字解釋

B1 中級

MySQL - 更新和刪除記錄(更新、刪除) (MySQL - Update and Delete Records (update, delete))

  • 3 0
    林宜悉 發佈於 2021 年 01 月 14 日
影片單字