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 Eli in the computer Guy, and in today's class, we're going to be going over stored procedures within my sequel databases.

  • So essentially all a store procedure is a way of referencing a pre written sequel statement.

  • So as we've gone on in these classes are sequel statements and become more and more complex.

  • So in the beginning, you start with, you know, select all from whatever table and you get the results.

  • Then you start doing select, you know, name something else.

  • Some other column from table any.

  • Then you get the results, and then you do select blah, blah blah from a table inner.

  • Join on you know where order by the whole nine yards and essentially at the end of the day, you can get very, very, very complex sticks, equal statements and become very difficult to type out every time you want a certain results.

  • So wouldn't it be nice?

  • Won't it be nice.

  • If essentially you could write that big old nasty sequel statement once and then be able to call that as a procedure in the future.

  • So instead of having to type out, you know, select blah, blah blah from where order by the whole nine yards.

  • Essentially, all you could do is, you'd say, call nasty procedure and then calling That nasty procedure will then actually send that entire sequel statement that is referencing to You're my sequel database, and then you will get the results out.

  • So we're talking about store procedures.

  • That's all we're doing.

  • Essentially, what we're doing is we're writing a script in the my sequel World.

  • So again, if you're thinking about Oh, if you're using Windows, if you're using Lennox again, you're using any other kind of scripting, scripting language.

  • Basically, you can write out a whole long script and then be able to call that script using a simple name.

  • That's what you're able to do with a store procedure with any my sequel database.

  • What you're able to do is you're able to write out a long, nasty sequel statement, and then you're able to call that sequel statement using simply the name of that store procedure.

  • So we're gonna be going over today is stored procedures.

  • They are very, very useful to use on.

  • Surprisingly easy to set up.

  • Now.

  • We do have to do a little bit of a warning.

  • A warning?

  • Will Robinson or need a warning.

  • There is one thing that I'm going to show you today That could really screw you up for your my sequel databases.

  • Um, most of what I'm going to show you today.

  • You know, if you fat finger, if you do something stupid, it will either error out or the waist.

  • Worst case scenario, you just get a really nasty procedure that you have to delete.

  • But there is one thing here.

  • There is one thing here.

  • If you're not paying attention, it could really make your life a nightmare.

  • Trying to use your my sequel database server going into the future, and that is setting the the limiter.

  • So what?

  • I mean by setting the limiter.

  • So currently you haven't a limiter of a semicolon, right?

  • So whenever you do, you know, select all from table.

  • In order to get that statement to run, you have to use the the limiter of a semicolon.

  • So the default a limiter within my sequel is the semicolon.

  • See, right out the statement you had semi Colon and then that statement runs on.

  • So that could be a real pain in the butt if you don't realize that the 11 there is required where you can write out a statement.

  • And if you don't use a semicolon if you hit inner, all you get is that weird little arrow thing, and nothing actually runs.

  • Nothing, actually, really happens s so you have to put that little semi colon in there as the delimit er That will then have this sequel statement run one of the interesting things when you're creating storm procedures, when you create the store procedure, you have to type in the semi colon and hit enter to go to the next line without what you created.

  • Actually running while you're creating it becomes a bit of a mess.

  • Basically, what happens with my Sequels whenever it sees that delivers?

  • It was a semi colon.

  • As soon as it sees it and you hit enter, it will try to run whatever it is that you've given it, even if you're trying to create a store procedure with basically creates a mess.

  • So what we're going to dio is you're able to use a command, the limiter.

  • So you use a command.

  • The limiter and I used the commanded limiter.

  • You can change the delimit er that is being you.

  • So we're going to do is we're going to change the limiter.

  • Circus adle emitter space, a dollar sign, dollar sign.

  • So what that will dio is that what changes the limiter from being a semicolon to a dollar sign dollar sign?

  • So basically, whatever we type in will not run until we type in dollar sign, dollar sign, and then hit Enter.

  • So why that's important is we're going to create the procedure Within the procedure that we're creating.

  • There will be semi Coghlan's But before we've created procedure, we will change the delimit er $2 sign dollar sign.

  • So when When my sequel sees a semi colon and reads the inner while the delimit er is still dollar sign dollar sign, nothing bad will happen.

  • So basically we're and he was about to deliver a dollar sign dollar sign.

  • You're going to create our procedure with our semi colons in there.

  • But it won't be any big deal because basically the my sequel, but it will not be processing when it C semi colons because of the limiter, will now be dollar sign dollar side.

  • We'll get to the end when we get to the annual new dollar sign dollar sign, so that will then create the procedure for us.

  • And then after that, after that, the next thing that you're going to do is do delimit er space semi colon to reset the delimit er back to being a semicolon.

  • If you set the deal emitter to some weird ass, who the hell knows what?

  • Remember that you're my sequel.

  • Database server will not process sequel statements until it sees that delivered her.

  • So if you can get a limiters to something else on and then you do not can get back to a cynical and then you can't run into problems later, when you go to type in a sequel, statements normally on you will run into issues there.

  • So that's just the big thing.

  • Just to keep in mind is we will be changing the delivered her here, so we'll be changing the limiter from semi Colon $2 sign dollar sign in order to create the procedure as soon as we're don't create the procedure, we will set up the limiter back Thio semi colon so that we don't run into problems in the future and so that we can then run the sequel statements as we have been doing in the past, simply using that semi colon as as the end so that let's go over the computer to show you how this works.

  • So here we are, back at my Mac book pro again.

  • I have virtual box running within a virtual box.

  • We have an instance of a bun to desktop 18.4 Lt s within the soup on to desktop.

  • I use task cell to install the lamp stack.

  • So this has a packing up my sequel and pH piece.

  • That's a live environment that we're gonna be dealing with.

  • And we're going to go down here.

  • We're going to click on the show applications.

  • Then you go up to search and for care you type and terminal to get you to the command prompt.

  • From this point, we're now going to along into our markets.

  • My Siegel database server.

  • If you do not have a user account created yet within your my sequel database server, you can use pseudo my sequel.

  • This will along you in as the root level account, but we created user accounts before, so we're going to my sequel.

  • Space hyphen, You user name Bob Space Hyphen?

  • Peed asked the password.

  • We put a password of 123456 Now we hit inner and we're now in our my sequel database from here said that of a server from here we could do show data basis.

  • This will show us the databases on this particular server, and we can see we have the normal databases and then we have the class database that is the one that we're concerned about.

  • So we will do use Class D B that will get us into the class database, and we will use these semicolon So as I talked about before, the semi Colon is currently the delimit er when you types in my colon and then you hit Enter.

  • That then tells my sequel to run the statement that you put in there.

  • So we're going to hit that hit enter and our database has changed from here.

  • What we're going to dio is we're then going to do show tables.

  • So I want to see the tables within this particular database Semi colon as the DL emitter that we can see.

  • We have a parts table and we have a vendor's table.

  • So normally, what you would do is you do, like, select all from parts.

  • So this is a sequel statement.

  • So this will select all the columns all the way from the parts table.

  • We had dinner, and then we can see.

  • Okay, so we've got part idea partnering in part price vendor I d cog Ride Sprocket.

  • We can see the price.

  • We can see all of that information there.

  • So now what if we wanted to create a store procedure?

  • So instead of having to do select all from parts we get, simply call a stored procedure we're gonna do is clear the screen.

  • And so now, in order to create a store procedure, the first thing that we need to do is we need to change into the limiter.

  • So we're going to do delimit er and they were going to say dollar sign, dollar sign.

  • So the DL emitter is no longer going to be the semi colon after this.

  • So I hit enter.

  • And so now if I do, let's say select all from parts Now, At the end of this, I need to put a dollar sign dollar sign in order for that to run his inner.

  • It now runs if I simply do select, uh, all from parts, as I did before.

  • But I do see Michael.

  • And remember, Semi colon is no longer the delimit er since I can't delimit er up here.

  • So when I hit in her now, it's just gonna give me that stupid little air a thing until I do dollar sign dollar sign.

  • Because that's the true delimit er, At that point, it will now actually process the sequel statement.

  • So that's one of the reasons is very important.

  • Now when you're done, you put the dilemma ter back to being the semi colon or whatever it is that you want.

  • So let's clear the screen again.

  • So the DL emitter is now dollar sign, dollar sign.

  • So what we're going to do is we're going to create the procedure, so we're going to create procedure and So then we need to give the procedure name.

  • So it's a test piece.

  • We just call it test P open parentheses, close parentheses, then we're going to hit her.

  • And then the first thing that we have to do is we have to type in begin.

  • So we're going to say we're beginning.

  • What?

  • This store procedure is that what has hit?

  • Enter again?

  • Just to put this on another line?

  • Theoretically, all of this could be on one the single align.

  • I am simply doing this to make it a little bit easier to read.

  • And then for here, what we're going to do is going to select all from parts and then do the semicolon.

  • So basically, we're saying create the procedure and they were giving the procedure and name open parentheses, close parentheses, your essay begin.

  • And then here.

  • This is where we put in our sequel statement.

  • However, however long or short and maybe and then we do semi colon.

  • Now, since the semicolon is no longer the DL emitter, when I enter, there will not be any issues.

  • Now.

  • If you had not modified the delimit er as soon as I have that semicolon and as soon as I press inner, then my sequel would try to run what I just typed in.

  • And then you will get some kind of Frankenstein mess out of it.

  • So that's why we carry to deliver her.

  • Now we're going to do is we're going to say end So we're going to end.

  • So begin and we have whatever whatever is in the middle, and then we're going to end.

  • And now to say that this is finished, we're going to do a dollar sign.

  • Dollar sign.

  • So this is the noodle emitter.

  • So this says now that says to my sequel process all of this, as soon as I press enter, then I had enter, I get no errors.

  • The first thing that I dio is I changed my delimit er back to being a semicolon soda limiter, space, semi colon.

  • And then from here now all I have to dio is call the procedure.

  • So I do call that I do test p and I do see Michael.

  • And then now I get the same results, right?

  • So, basically, instead of having to type out the entire sequel statement like I did before, I could simply type and call test p.

  • And this sequel statement will run so that let's do something a little bit more complicated.

  • So now what I'm going to do is I'm going to do a joy.

  • Right?

  • So before one of our previous classes I joined, I joined the parts table to the vendor's table based off of vendor I d again.

  • So that gets a little bit longer, eh?

  • So let's let's actually try to create that as a procedure just to give you an idea of why you would use these procedures for a longer sequel statements.

  • So the first thing that we're gonna do is we're going to set the limiter $2 sign dollar sign again, and then from here.

  • What we're going to do is we're going thio create.

  • Uh, it's great.

  • Proceed here.

  • Then we're gonna call that nasty.

  • We're just gonna call it nasty perceived.

  • You're right on then open parentheses, close parentheses.

  • Now we're going to go down, and we're going to say begin, and then we're going to go down one more level and that now this is where I'm going to type in a little bit more of a nasty sequel statement.

  • This isn't the nastiest, really, really, really nasty sequel statements, but just again, just to give you an idea, eh?

  • So let's say around you select, we're still gonna do all from parts, uh, inner joy.

  • Um, vendors on.

  • And then we have to say what we're inner joining on.

  • And so we're going to parts dot of indoor underscore i d equals then doors, Doc of indoor underscore i d and then semi colon.

  • So I'm gonna select all sorts.

  • Like all columns from the parts table.

  • Inner joy through the vendor's table on So parts, not vendor I.

  • D.

  • So there's a column within the parts table called Vendor I D.

  • And then that will equal in the vendor's table.

  • There's a column called Vendor I D.

  • Right.

  • So this will combine those two together.

  • Then we're going to go down one more level.

  • We're going to do end, and then we're going to do a dollar sign, dollar sign.

  • And hopefully if I didn't do something stupid, this will work.

  • So we hit.

  • Enter.

  • Okay, query.

  • Okay, we have no errors.

  • So now all I could do, instead of having to type out all of this or something even more complicated.

  • What I could do is I can say call nasty.

  • Uh, do some, uh, who almost made a space take almost made mistake.

  • Deliver eater, uh, goes back to being a semi colon.

  • So now that the dilemma is now back to being a semi colon, what I can do is I Can you call that?

  • I could do nasty semi colon.

  • And then look, I get that joint statement that I had created up there, And so now if this was a statement that I would have to run periodically instead of having to type all of this in and worrying about fat fingering and you're doing something stupid, I can simply type in call nasty now, And that will get me the exact same results.

  • So again, with thes sequel statements, these could be really long.

  • You'd say select, you know, part name apart Price of Endor name, vendor address from parts inter join vendors on blah, blah, blah.

  • Where, you know, maybe you could say, you know, part price is greater than you know.

  • Five order by maybe vendor name.

  • Right.

  • You could make this really long and tedious.

  • You type that.

  • And once you give it a procedure name, and then you can simply call that procedure in the future without having to type everything in s O that let's clear the screen again.

  • And now what we need to look at is we need to see, you know, what procedures do we have within this particular database?

  • So again, you're coming into a database.

  • We do show databases.

  • So when we log into the my sequel server, we do show databases to show us what databases we have on the server.

  • We go and we do use whatever database we want to go into.

  • So we go into a database, we do show tables to see what tables are in the database.

  • So one of the things that we need to do is we need to see what procedures are in this particular database, right?

  • Eh?

  • So in order to do that, what we do is we do show, uh, pros See jer status, uh, where and then we do d B.

  • So where database equals and then whatever database that we're interested in so class D B is the database that we're in.

  • So we want to say see is we want to see what the procedure status is for any procedures within the class database.

  • Then we do the semi colon again as the delimit er And then we had dinner.

  • And then from here we can see what procedures we have.

  • So we have a nasty procedure and we have a test p procedure.

  • So this shows us our two procedures shows us are modified.

  • Time shows us are created.

  • Time gives us a bit of other information.

  • So now, But one of things you'll see here is that we don't see what the procedure actually does.

  • So we have a name of the proceed here we have who defined it.

  • So who created, you know, Bob at local host.

  • We have some time, we have some other information.

  • But let's say we need to see what the hell nasty procedure is.

  • And again, this is one of the big problems.

  • One of the big problems in the real world of I t.

  • Is simply knowing what the hell something is again.

  • Naming convention is a big deal, right?

  • Like think about this like nasty, nasty tells you nothing.

  • I have no idea what nasty does right.

  • So, again, this is something important to think about when you're creating procedures, create names that are descriptive, like maybe like inner joined parts vendor procedure, something like that.

  • But okay, let me say Let me say OK, nasty.

  • I'm not sure how I feel about nasty.

  • So, uh, so what is this nasty procedure?

  • So in order to see what the nasty procedure is, what we can do is we do the command show create procedure.

  • So basically, what this is saying is show how this procedure was created on, and then we type in nasty because that's a procedure that were interested in We do semi colon, which is the delimit er And then we hit return.

  • So from here, we're going to get a whole bunch of stuff we gotta sort of sort through.

  • It s o nasty.

  • Only grew by Creative finder.

  • Look, Bob, local hosts you get this whole information.

  • Ah.

  • Then the important thing is you come down here to begin.

  • Okay?

  • So they begin.

  • And then what is this procedure?

  • So we can see here?

  • Is this procedure a select all from parts inner joined vendors on parts that vendor vendor equals vendor I d equals vendors dot vendor underscore i d And so this is the sequel statement If we d'oh Oh, and we take a look at that test p that we created so again we could d'oh show create procedure, Test p.

  • That was the other procedure that we created a semi colon as a delimit er s o from here again.

  • You gotta sort through a whole bunch of this.

  • So we have the name.

  • We have some other information up here for this test P procedure.

  • We look down to begin.

  • It's with S P procedure.

  • We can see it select all from parts, and that's all there is to it.

  • So show create procedure than whatever procedure that they issue.

  • Type that in, and then that will show you how the procedure was created from there.

  • If you want to get rid of the procedure, you want to drop it for one of the reason you can dewdrop um and then you d'oh procedure.

  • So you have to tell it what you want to drop, Let's say, wanted to test drop test P.

  • Then we do semi colon.

  • Then that got dropped.

  • Let's say wanna drop pros see Jer Nasty.

  • So we don't want the procedure nasty.

  • Uh, then we do semi colon, but we get no errors from here again.

  • We can try to see what procedures we have within this database on the server.

  • So you do show, uh, proceed your status where the B equals single quotation mark a class D B or whatever your database nickname is close.

  • A single could hash marks in my colon has an a limiter, and then he hit Enter and we can see that we now have an empty set.

  • There are no procedures store procedures within this particular database on this database server.

  • So that's how you create a procedure.

  • That's how you're able to run a procedure you just do call whatever the procedure name is, that shows you what procedures are on your database server showed you how to see what the procedure actually is.

  • And I showed you how to drop the procedure.

  • And again, the most important thing, the one thing out of all of this that you have to remember, it's how we sent that limiter back to set Michael.

  • Because remember if he said that the limited or something else and you don't set about to send like hole and you're going to have a buck.

  • Your problems.

  • So that's a basic overview of the store procedures for you.

  • So now you know how to set your delimit, er, how to create a store procedure.

  • Had a call, a store procedure.

  • Had to see what store procedures air with a database, how to see what those store procedures actually do.

  • And then how to drop basically, how to delete the store procedures.

  • As with all of these things and my sequel, the universe, you can get into much more complicated subjects with store procedures if you're actually going to be doing store procedures.

  • I do recommend you go on the Google.

  • You look a little bit more into store procedures because again, this could be a whole couple our class on its own.

  • I am just giving you a bit of an introduction here to make your life easier again.

  • This is one of those things where a lot of new people they have this idea that they want to type everything out, which is good when you're new.

  • When your new by all means, type out all your sequel statements.

  • If you have a nasty, long, tedious sequel statement, typed that thing out every single time when your brand new it'll get you used to typing out the commands, it'll get you used to making sure you're typing in all the things that you think you're typing it.

  • It'll get you used to when you're supposed to use comments when you're not supposed to use commas, right?

  • Typing out these long sequel statements are really great, Wayne.

  • You're new and you're learning and you're trying too hard.

  • Code this information into your head.

  • It really sucks when you're an old timer and he doesn't need a dance, I guess I don't need to read, right.

  • Uh, one of the problems you get into you again with the command line is remember, the system will process what you type in, And so if you have a really nasty along sequel statement and you you know that they're supposed to be a comma.

  • But for some reason when you hit the Kama didn't register for some reason, or you know there's something else you just fat fingered at right, you type in that thing in an error is out.

  • It could be a real pain in about one of the nice things with the store procedures is is normally when you're using a database, you're going to interact with it more or less the same way Every time you go and deal with the database, right, there are going to be certain sequel statements they're going to use over and over and over and over and over and over and over and over and over and over again.

  • So you might as well just create those a store procedure.

  • And then you can just simply call the store procedure without typing everything out.

  • S o.

  • Again.

  • This is one of those things.

  • While your new type out the sequel statements every single time.

  • Once you get used to typing out the sequel statements and you feel very comfortable with it, you just need to actually get some work done.

  • That's where you can create the stored procedures.

  • And then again, all you do is you call the store procedure, and then that whole statement gets shot out.

  • You don't have to worry about fat finger and things or you know the wrong stuff being in there because you can write at once.

  • Make sure it works properly and then just continue to use that into the future.

  • But again, with sequel statements with with store procedures, there actually is a lot more to this.

  • So if you're interested using store procedures, definitely go out and do some more research because you could do some more cool things with it up.

  • But this was just a brief introduction for you and again.

  • Don't forget that the limiter you set that delivered her a dollar sign, dollar sign and then you do something stupid and you don't set it back.

  • That can cause you problems.

  • So So just just you remember that as always, I enjoy teaching this class and look for to see 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.

  • Do you remember that?

  • If anything at all happens to this channel, you can go to Eli, the computer guy dot com, to view the content and access information not available on YouTube.

creating these classes requires equipment and service.

字幕と単語

ワンタップで英和辞典検索 単語をクリックすると、意味が表示されます

B1 中級

MySQL - ストアドプロシージャの紹介 (MySQL - Stored Procedures Introduction)

  • 2 0
    林宜悉 に公開 2021 年 01 月 14 日
動画の中の単語