SQL Tutorial 2021

well hello internet and welcome to my ultimate SQL tutorial in this tutorial you're going to learn everything you pretty much want to know about SQL using MySQL this is basically a 500 page book all crammed into one video and underneath the video you're gonna see table of contents that you can click on to jump throughout the video and learn exactly what you're looking for I'm basically gonna cover everything from installation on both Windows as well as Mac OS through stored procedures functions triggers database design and everything else and I have a lot to do so let's get into it alright so the very first thing I'm going to do is cover how to install MySQL in my school workbench and everything else you're gonna need so what you're gonna want to do is go to dev dot MySQL comm /downloads forward slash installer and you're specifically going to want to come down here where you have MySQL installer community and click on download whenever you do you're going to be forced to log in so you're either gonna log in using your Oracle account or you're going to click on sign up everything's free so do whatever you need to do in this situation I'm gonna log in so I'm gonna type in my user ID and password click on sign in then it's going to allow me to download the community installer I'm gonna click on download now whenever I do it's going to install here and I'm going to be able to click on open to start the installer I have to give it permission to actually run so I'm gonna click on yes this message is gonna pop up I would just click on yes we are specifically going to use the developer default so select that and click on next this is just gonna pop up again click on next and basically what we're gonna be doing here is you're going to be downloading and then installing all these different requirements and all these different parts of the MySQL ecosystem and as you do it some of the Downloads are gonna fail you're in a requirement issues and all kinds of stuff I'm gonna walk you through that entire process it's very easy to solve all you basically I just have to do is keep clicking retry retry retry and eventually everything installs so come down here and click on execute and then you're gonna get all the individual parts of the installation I'm gonna click on I agree with the licensing terms and click on stall and then all the individual parts are going to download and install and then whenever they do just click on close again another part comes up here we just need to accept the license and click on install and then the installation process will continue and whenever the installation is finished we are just going to click on finish and you can see all the different checkmarks as the different things are being installed click on next you're gonna get a message like this which says requirements have not been satisfied just click on yes and then you're gonna see all the individual parts that we are going to download your list might be slightly different than mine because I don't have Microsoft Excel you might have Microsoft Excel in here or something else don't worry about that just click on execute and then all the individual parts are going to be downloaded and installed if there's a checkmark inside of there that means everything's good sometimes the installation or the download is gonna fail in that situation you're going to see these exclamation marks don't worry about them just click on try again and they will install and they will work properly and if you keep clicking on try again over and over and over again eventually you're gonna see complete everywhere here in that situation you know that everything's been downloaded and installed click on next you're gonna see a message like this just click on next I'm gonna use the standalone MySQL server so just put a mark inside of there and click on next don't change anything here and click on next I decided to use the legacy authentication method just to ease the learning process it's not going to change anything it's just gonna get some things out of your way whenever you're learning how to work with MySQL and SQL so I would choose to use the legacy at the education method with a check mark there and click on next you're then gonna have to define a root password this is going to be like the master password for working with your databases so type in a password inside of there and click on next I also chose to click add user and enter in a new username you don't have to do that but I did and then you're gonna have to give that user a password as well and click on OK if you so choose to do so don't change anything here again click on next everything here is perfectly fun and just click on execute as you do everything's going to be set up properly for you and at that you can click on finish' this message is gonna pop up click on next don't change anything here and click on finish this message is going to pop up you're just going through you're going to be clicking on next a couple times you're gonna have to type in your root password here whenever you do you'll be able to click on next you can see here the connection was successful that's good click on next and then just click on execute everything's going to be completed and you can click on finish and then just click on next and I chose to start the MySQL workbench after the setup as well as the shell click on finish and this is the MySQL workbench which is what we're gonna be using in this tutorial and this is the shell we're not going to use it but you will be able to use the shell you based off of what I'm gonna teach you in MySQL workbench a lot of the stuff that works over here is gonna work exactly the same over here so you're just gonna type in quit over on this guy and then you're going to click on local instance my school 80 right here you're gonna have to enter in or your root password and then click on OK whenever you do MySQL workbench is gonna be ready and you will be ready to work with this tutorial and here it is full screen and we know that everything is properly working so that is how we get everything working on Windows and now I'm gonna show you how to set up everything on Mac OS basically the installation on Mac OS is gonna be very similar you're gonna go to dev - Qualcomm downloads MySQL you're gonna select your operating system right here the easiest way to install everything is to use the dmg archive that you see right here so just click on download no doubt whenever you do if you're not currently logged into your Oracle account it's gonna either have you sign up for one or login it's free so don't worry about it in my situation I'm gonna login type in my user ID and password click on sign in and then it will allow you to download everything so just click on download now it's gonna pop up down here we're gonna come down and click on open whenever you do you're gonna see this pop-up just double click on this message is going to pop up we're gonna click on continue and then we're gonna click on continue and then we're gonna click on continue you're gonna have to agree to the terms so click on agree and then click on install I chose to use the legacy password encryption because it's gonna get a whole bunch of things out of your way as someone who's how SQL MySQL works so I would recommend you do the same so click here and click on next it's not gonna keep you from learning anything it's just a there's different things that are just gonna get in your way as a beginner or either an intermediate or either an advanced person working with SQL so that's why I chose to do that you're gonna have to define a password for your root user which is going to be the master of your database so choose a good password and click on start MySQL server once the installation is complete and then click on finish everything is gonna get set up then what you're gonna want to do is come down here where it says System Preferences double click on that whenever you do you will see MySQL down here at the bottom if you double click on that you're going to see that the server is running which is a great thing so you can now come in here and close that then we're going to execute MySQL workbench so just click up here where the little magnifying glass is and type in MySQL workbench and then double click on it and here it is and then we just come over here and double click on this enter in your root password click on OK and MySQL workbench opens up and everything looking good all right so now you have MySQL workbench in my school on numerous other different things installed everything's ready for the tutorial and so now I'm gonna jump into the tutorial ok so a small part of this tutorial is going to be slides the vast majority of the of it is gonna be me typing in code and executing it but just wanted to let you know right there I'm just going to use some slides to help explain the overview of what we're gonna be doing now a database is just data that is structured into rows and columns like a spreadsheet and to receive or change data in a database you send commands called queries and then the database intern returns a result based on that request databases contain many tables of data that is organized into rows and columns and each column represents one type of data the database stores and then each row contains multiple pieces of data that are specific to each entity you are describing so for example we store information on customers here each individual you stored is called a cell and primary keys are used to define unique entities in your tables and you can also have non unique keys which are just referred to as non primary keys so for example let's say we want to store possible countries in a table with an associated key for each country we could then refer to those countries in our tables but because they aren't unique for each customer they can't be used as primary keys in this situation customer ID in the sales table is called a foreign key because it refers to a row of data in a separate table being the customer table now you're gonna be able to have multiple foreign keys in a table but only one primary key and if any of this is confusing don't worry about it I'm going to be showing you so many examples using primary keys and foreign keys and all these other things and I'm mentioning that by the end you will completely understand them now primary keys can be made up of multiple columns in that case they are called composite primary keys but don't worry about that for now I'll cover that later and here it is possible to have multiple references to the customer table in the sales table and you can see that customer nine is actually listed twice and in that situation that is known as a one-to-many relationship and I'll get more into that later as well so now we're gonna jump over into the MySQL workbench and I'll show you how to create a database now to create a database all you need to do is click this icon up here which looks like three cylinders stacked on top of each other so just click on that and that little guy right there is actually called create new schema and MySQL just refers to databases as schemas so that's all that miss so what you want to do here is go in and give a name for your database I'm gonna call this sales DB leave everything else exactly the same and you're gonna come down here where it says apply and click on apply whenever you do it's actually going to show you the query you would use for creating this new sales database just come down here and click on okay apply again and it says SQL script was successfully applied to the database so click on close and now you can see your sales database is right here so now what we're gonna do is create a customer table and you can come in here and close this to get rid of it and click on your sales database that you have over here you can double click on it it's going to show you tables views stored procedures and functions I'm going to explain to you what every single one of those things are and we're going to work through examples on them so just double click on the sales DB if you haven't already and then we're gonna click on the query one tab right here and you're gonna come in and we're gonna create a customer table now to create a table we're just going to type in create table and I said that it's gonna be called customer and then what you have to do is just to define all of the pieces of data inside of it and the type of data so for a customer you might want to know something like their first name you're then going to define that this is a string of characters that are going to be 30 characters in length and we're gonna type in not null that means that if somebody tries to enter data into our customer table we must receive a first name as part of the data that is received I'm also going to come in and say that I want the last name so I'm just gonna type in last name again I'm gonna say 30 characters in length then we're also going to ask them for their email and you may note that the cantle commands that are specific to MySQL are going to be in uppercase letters and everything else is in lowercase letters my email I'm going to say that I want that to be 60 characters in length again not in all not in all just means I must get that otherwise I'm not going to allow you to put information in my table company again variable number of characters not and all and we'll get into other David different types of data of course also going to say that I want to know the street variable number of characters let's change that to 50 what else do we want to know about our cost murmurs well we would also like to know the city in which they reside and let's say that the number of characters there is 40 the state I'm going to say that that is going to be two characters in length I could also come in here and go characters and two zip now there's going to be different types of ways to store numbers I'm going to show you that in a second the specifics of them but I'm going to use a medium int here because that makes sense for the size of the zip code that I'm going to be expected I'm going to say that I expect this data to be unsigned that means I don't have a negative zip code and again I'm going to say that I require it so not null null is not a available piece of data to put inside of here I'm going to say that I expect the phone number let's just come in here and do this whoops wrong thing here there we are I'm gonna say that I want my phone number so I'm gonna say phone variable number of characters and I'm gonna say 20 again not in all I'm gonna say that I want to get their birthdate and there is a date data type that I want to use in that situation but let's say that it might be hard to get a customer's birthdate and for some reason or another in that situation I must say that nollie is acceptable sex is going to be either male or female so I'm gonna use an enum which is going to be a list of potential pieces of data so we're saying that I expect male or female to be input inside of there again I'm gonna say that I demand to get that piece of information let's say that I also want to track when the customer was entered into our database in that situation I'm going to say that I want to use a timestamp data type which is going to allow me to store both dates as well as times and I'll say that I want that to be definitely put inside of there and then you're going to see our ID this is going to be our primary key this is going to be a unique number that is going to be specific to each individual customer that I put into the database it is going to be an integer it is going to be unsigned so it's not gonna be negative it is going to be not null I can also go down to the next line without causing any type of an error I'm also going to say that I want this to be Auto incremented so the first time I enter a customer into the database it's going to receive a value of 1 and the next number is going to be 2 and so forth and so on that's what Auto increment means and then finally I'm gonna say that it's a primary key and that just means it's a unique key that is going to represent each customer then after I go and finish defining what I want my table to look like I just click on this lightening bolt up here and then I can come over here on the right side well first off if you come down here you're gonna see there's no errors so that's a good thing then I can go over here and right-click on this and say refresh all and now you're gonna see that I have customer table right here and if I click on it you're gonna see all the specifics of how that customer table is set up so I said I was going to talk about the different data types that are available and these are the most common numeric data types and you can see their minimum as well as their maximum values and the flute data type is purely theoretical and it's precision is going to be dependent on your specific machine and actually it's nothing even to worry about because the float data type has been deprecated which means that it can be used but it may be removed at some point in the future and what has replaced them well the decimal data type and decimals are exact numeric data values and whenever you are defining them you use the form of decimal and then 6 is going to represent the number of significant digits of the entire number while this second value here is going to represent the number of decimal precision wise so if you typed in 6 2 you're gonna see 1 2 3 4 5 6 see 6 digits and to precision points for the decimal you're also gonna see the format for date as well as date/time and you're gonna see the maximum value for your date data types that you input this is what a timestamp is going to look like you can see here it has the year Matt this is the maximum value you can store in a timestamp all of these are the maximum values and you also see that we have the hour minute seconds and milliseconds that you can store time is going to take this format and years are going to go from 1901 up to 21:55 like I explained when I was creating the customer table you can define how many characters you wish to store in a variable character and that's gonna be as long as the maximum is 255 which is offhandedly the maximum size for storing an 8-bit number a blob is going to be able to store up to 65535 characters and then you can also see how many characters are going to be able to be stored in the other different blobs enums are going to store distinct elements such as male or female like you saw previously and that's how many of those you can work with and sets are basically just like enums here's some other different data constraints so you already saw not and all that just means that it must have a value it can't have a value of an all default what you're gonna see later is going to designate a default value whenever a row is created without data unsigned means that you cannot use negative values primary keys are identified as unique values assigned to a row and if you mark data as Auto increment each time a row of data is created you would mark anything that is set for auto increment to null and the database will automatically go in and increment that value but it's important to know that you can only use auto increment on one column per table and in that situation that column must either be a primary or a unique key now whenever you are designing a database it's very important to understand that each table should represent one real-world object also each column stores one piece of information about that object you should define how tables may relate to each other using either primary or foreign keys or both and you need to reduce redundant data and this is called normal databases one really great way to think about how to create a customer table and a database overall is to use a real world way of marking down and tracking information in regards to orders so I went and created a fake invoice which is basically what invoices look like and I'm gonna take this company invoice and then translate it into a database so very first thing that I'm gonna want to do is whenever I want to create a customer table I could have just went in here and created one field named address but in doing so it is much better when an element contains two or more pieces of information to split those up into separate elements and you can see that's exactly what I did on top of that I added additional information that may be important in regards to dealing with a customer such as the birth date and sex as well as the date of the cut that the customer was added to our database I'm going to also create another table that is going to be extremely similar which is going to be sales person and I can create that here in a second you can see all the individual pieces of information there one thing that is different however as I got rid of the company because it's our you know our sales person so we know it working that that sales person works for us and also I changed a date entered to date hired here which makes more sense so why don't I jump over and actually create this sales person alright so this is just gonna be sales person so I'm going to go sales person and largely this is gonna be exactly the same remember I said company isn't gonna be here so get rid of that and we're going to change date entered into date hired so put date hired inside of there you know than that everything else is exactly the same so let's just come in here and click our lightning bolt and then come up here where it says refresh all and now we can see the sales person has been properly entered you can see here an example of different table data and this is going to be specific to we're running a shoe store in this example in this tutorial so this is going to be a representative that we are this is going to be a business shoe this is going to be the supplier the specific name the individual size as well as any color names-- discount tax rates and quantities available for the individual shoe types that we sell and you can see right here that duplication is a sign that the data should be in its own table so what we're going to do is create a table called product type which is going to be associated with name either business casual or athletic so we're gonna create that product table right now we can just come in here and we can come up and create a new query if we would like so file new query tab there it is so we got that guy inside of there and let's go and create it so we're gonna say create table and this is gonna be product type parentheses and then what we wanted to have in here it's gonna represent business casual or athletic so the name is either gonna be business casual or athletic variable number of characters 30 should be enough and we're gonna say that it must have a value so not null and then what are we gonna do after that we're gonna give it a unique ID which is gonna be this guy right here so let's just copy that out of there and paste that inside of there and that's all we need to do and then we can come in and click on our little lightning bolt and come over to tables and refresh all and now we can see product type is right here and everything's looking good you can see an example here of our sales orders which once again we got from that invoice real world thing that we worked with previously so basically what I'm going to be putting inside of here for each of the individual sales orders is the item ID the quantity discounts whether it was taxable or not you can see another example of a data type here a boolean which is going to be either true or false and the falses are going to be represented with the value of 0 and 1 and anything else is going to be considered true you can see I went and put default inside of there you can also see I defined a decimal this is going to represent the individual customer database or the customer table that I defined this is gonna represent the salesperson that was involved in the sale the time in which the order was processed per sorter numbers credit card numbers and all of all these different guys but whenever you look at this gigantic table you're going to see a lot of problems and this is where we get into better organizing data so that it is easier to work with tables as I have mentioned previously should not contain duplicate data so for example if we had individual items ordered all in one table as well as sales persons and purchase order numbers and time of sales as well as four different pieces of credit card data repeated over and over again this is going to cause us some serious problems so what we need to do instead is to take this gigantic sales order and break it into smaller and smaller pieces so it's more easy to work with so one thing that I can do here is go and create another table called sales item and we are going to link these two tables using both a primary key right here as well as a foreign key which is going to be right here then what it can do is break out item ID into its own table as well as quantities and discounts and taxation information and be able to easily work with these and avoid duplication which would have occurred in our sales order if we didn't break this individual sales items out into their individual pieces and just remember that primary keys are unique identifiers while foreign keys are going to be primary keys that are in another table so what I'm going to do is come out here and define all the individual pieces that are going to be representative of my individual products so I'm gonna come in and let's call this product instead and after that let's just get rid of well I'll leave the primer or the primary key inside of there so for my products I'm gonna have type ID which is going to store either business casual or athletic inside of it so I'm going to call that type ID int and it's gonna be unsigned and not null and where do I get that from well this guy down here this is product type say unsigned and that's going to be primary key obviously this isn't a primary key however but so I'm referencing that this is going to be a foreign key and I just need to type in type ID to reference that that is a foreign key and then references product type and ID which is the specific part of the product type table that we are referencing then my product of course is gonna have a name which is gonna be a variable number of characters that I'm gonna say is thirty in length and it must have a value what else am I gonna have supplier so this could be like the name is gonna be like the speedforce and the supplier is gonna be naik in this example and i have no idea if mike has a shoe called the speedforce but you I think you get the point of that again this is gonna be not in all four supplier then I can have a description that goes and explains what I'm working with I need a blob a bigger number of characters to describe the shoe so I'm gonna use the blob data type and then I'm gonna have a primary key just like I did before and click the lightning bolt no errors down here and if I come over to tables and refresh now you're gonna see that I have a product and you can see all the different data types that are stored there so now let's create our other individual pieces so I want to come in and create an item type and basically this table is just going to describe just the quality of an item so it's going to have a reference to the Product ID as well as the size color a picture and a price so that's all of those things so let's just get rid of all this and then I'll show you here in a second how these are all going to link together so I'm going to link to product ID int unsigned not null and again define that this is a foreign key that I am referencing which is going to be Product ID this is referencing this right here and then it's going to say specifically that it references the table product and the individual column ID in it and then I'm going to list all the individual things that I want to we're in here like the size of the shoe not null color of shoe that's gonna be variable number of characters which is gonna be 30 in length as a max again not null picture I'm not actually gonna store a picture but I'm going to store a URL that's going to link to a picture or a place on my machine my server where I'm going to be storing those pictures so you don't store the actual picture you store a link to it of course we're gonna need a price and this is going to be a decimal as well well actually the picture should not be this would be a variable number of character let's copy this throw that there and then this is going to be a variable number of characters and I'm going to make this really long I'm going to say 256 and length naught and all and I could go and give it a default value but whatever I'll just keep it the way it is right there price and I'm gonna have that be nominal okay so you see I'm breaking up the descriptions of all these individual tables into individual pieces so this is going to be an item to specific information click that tables and refresh all and now you can see that item is right here and all the data types that are defined for it now I'm going to create my sales orders so come in here get rid of all of this and this table is only going to have information that's going to be pertaining to the order and it's not going to contain information about products and prices and all those other different things and basically what I'm doing with this sales order table is simulating two people agreeing to do business so what am I going to want to store in that situation I'm one I'm going to want to store the time of that event a purchase order and a means of payment so I'm just thinking through the whole entire process so I'm going to say sales order and then what am I going to have well I need to define the two people that are doing business so we're gonna have customer ID integer it's gonna be unsigned and we must have a value for it what else am I going to have well I'm agreeing the two people are doing business so I'm also going to have a sales person ID which again is going to be an int which is going to be unsigned and not null and then I need to reference that these are foreign keys so foreign key and then say customer ID is going to be associated or is going to reference references the customer table and the ID inside of it then I'm gonna do the same thing for my salesperson so let's just copy this basically inside of there and change this to salesperson ID this is where that comes from so I'm just gonna copy it paste it inside of there and then it's gonna reference the salesperson table so let's just go and get this paste that right there and ID specifically in the salesperson table so if you want to make sure this is what I'm referencing that's gonna be the foreign key that's where it's coming from and that's how I'm doing it what else did I say well remember we're simulating two people agreeing to do business well I'd like to also in that situation know when the order was taken so time order taken and this is a time so what we're gonna do is give it the timestamp data type not null we're going to want to track the purchase order number cause a purchase order was written up and each purchase order has a number so there we go this is going to be an integer and it's going to be not and all again then we're going to have a credit card being handed over so a credit card number I'm gonna have this to be a variable number of characters 16 and length naught and all what else do we need to know about credit cards credit card expiration month this is going to be a tiny int because of how small the number is we're also going to have credit card expiration year and this is also going to be a tiny int not null and then each credit car also has the secret code so credit card secrets code again this is going to be a tiny int not null then we're gonna have name on card variable number of characters and let's say I want this to be 60 and again not and all and then we have our primary key alright and if we type 2 up credit card let's go in here make sure we typed everything in right expiration month credit card number purchase order number time order taken and all of those individual pieces and everything looks good so let's go and run it and create it I mean down here there's no problems come up here to tables refresh all and now we have our sales order as well and you can see all that information right there if you want to pause your screen if we do of course make an error I'll will show you here in a minute how to go and fix an alter two tables alright so we have our sales orders now we want to have our sales items just want to review exactly how these are going to relate we're gonna have our individual product type that is going to have an identification number which is going to be referenced in the individual products so this is going to be business casual or athletic that is going to be referenced in the product table its ID for the products which have the name supplier descriptions are going to be referenced in the item table so that's primary key is gonna go to this foreign key right here the items have the size color picture and price that ID is going to be referenced in these sales items and then the sales items are going to be quantity discount taxable and tax rate that ID is going to be referenced in our sales orders so that just means the only thing left is to go and put our sales item information in and so that you didn't have to watch me type all that in again I went and created it ahead of time and this is the sales items so basically each item that is part of an order is going to go in its own table and it's then going to be linked to the order with the sales order ID and this is just going to simulate picking up a quantity of an individual item with a certain discount and a certain tax rate and the item itself is hidden in the item table and specifically a hundred percent defined specifically there in a way that makes it easy to refer to it just with its ID and the reason we're doing all this is if I were to go and list color size or anything else that's going to break that ability to consider items in a self-contained way which would be a bad thing which is why we're breaking the tables up the way that we are and whenever you are creating a database design it's very important to consider that the data in the database be just that data it's often better to perform calculations outside of the database versus storing those calculations such as total sales amounts and things like that which I will show you here very soon how to do so let's say now that we have all this created well first off I have to go and actually create my sales item table and come over here and refresh and there it is so sales items and now we have all of our individual different tables for our database setup so let's talk about altering tables because I said that we can alter tables let's come in here - well we don't need any of these so yeah don't save that all right so we're inside of here how do you go about altering a table so let's say that I want to add a new column well what I would do is say alter table and let's say that I want to add it to these sales items table what I would do is let's say I wanted to add the day of the week that this sales item was created or whatever I could do add day of week and say that it's going to be a variable number of characters eight in length I would then follow that up with a semicolon and go and create it and if I did let's come over to tables refresh all sales item click on sales item and you're gonna see day of the week is right there I can also come in and modify that column someone again say alter table sales item modify so instead of ad I'm gonna say modify day of the week and let's say that I want it to be a variable number of characters that are 9 in length and that I want the mark this as not and all execute it again you have to come in and refresh it and if I go into sales item you're now gonna see that it's variable number of characters 9 okay so that's another way to alter another thing that I could do is let's say that I would want to come in here and drop this column let's decide I don't like having weekday inside of there after all well I could just go in alter table sales item which is the table name and drop column and weekday and execute it and now if we come in and refresh this we're going to see that inside of sales item we no longer have weekday we do have day of the week let's go and get rid of day of the week execute that and refresh it again sales item and now you see it's going alright well what would happen if we would like to come in here and let's create another table so let's create a table and let's call this transaction type and let's do all kinds of funky things with it so let's say we have a name variable number of characters and 30 and not null let's say we have payment type variable number of characters and have this be 30 as well not null and then it's going to have a primary key and auto increment and this will be the primary key close that off and let's create that table fresh transaction types right there we could then come in and rename our table if we would like so just go rename table transaction type to say we want it to be called transaction instead execute refresh transaction type became transaction we're also going to be able to create an index and let's say we want to create an index based off of a single column we can say create eight index transaction ID on transaction name create that we could also come in and create an index based off of two columns so let's change this to transaction ID to on and then say transaction name as well as payment type execute that and we created another one we'd also be able to come in here and delete data that is in a table just by saying truncate table transaction there's nothing in it right now but that doesn't matter that work and then finally we could drop the table all altogether just by saying drop table transaction and it's going you can see over here alright so there you go there's a whole bunch of different ways in which we can alter columns and tables and now I'm going to show you how to alter tables inside the workbench now you're also going to be able to view as well as alter tables just by coming over to your table and right-clicking on it and then coming down to where it says alter table you can see then it gives you all the information about your table in this situation it's our customer table you can change the name just by clicking on it and then typing in a different name you can change all of the different constraints as well as the data types you can come in here and mark this as a primary key this stands for not null this means unique this is binary this is unsigned this is zero fill which means it pads the displayed field with zeros this AI stands for auto increment this is going to be generated which means that it contains functions that auto create values and then you can also put a default value inside of here you're also going to be able to double click to add columns as well as right click to delete selected pieces of information and of course if you make any of these changes whenever you're done come down here and click on apply to apply those changes but we're not going to change anything right now instead I'm going to show you how to both insert as is update and delete database values so let's click on our query tab once again I'm gonna first off go in here and insert some information into our product type table to do so you just go insert into whatever your name is for the table you want to insert it into this is gonna be product type you're then gonna list the pieces of data you want to enter by their name so i'm gonna say i want to enter name and ID follow that up with values and i'm gonna put this on the next line so it's easier to fit everything and then inside of parenthesis what you want to put in so I'm gonna say business and then for the automatically generated primary key you just put in null and it automatically generates it for you whenever I do that I can execute it and then that piece of information will go inside of there I'm then gonna also put casual inside of there and we'll look at the information here in a second so I'm gonna go casual and again click on that and then athletic is gonna be the last piece of data and go and execute that now we would be able to come in here and go select and a star from and product type is the name of our table and execute that oops product type I said product type and then probably typed in product ID and whatever you do you can see our pieces of data which is casual business and athletic and the IDS that Rado created for us well you can also input data in without listing over and over the column names as long as you put them in order so let's just go out of this and go back to this part so we can say insert into and this time I'm going to put individual products inside of here and then I'm gonna put values like this let's bring values up here and as long as I input them in the correct order I don't need to go and list the column names so I went and created this information ahead of time paste it in there so now you're going to see if we have product here here's product so there's going to be the type which is either gonna be business casual or athletic the name the supplier the description and ID is auto by putting in all you can see I put product types all the different product types the individual name as well as the supplier a description and then in all at the very end and they're gonna separate all those with of course parentheses around all the data and then commas and then at the very end put a semicolon and I have all of this available in the description you can get all of this stuff and we execute it and then what we can do is do whoops do a select all on this and select everything from product like that and execute it and you can see all those individual pieces of data that are inside of there and all of the auto-generated IDs alright so pretty cool stuff now I'm gonna do the same thing for our customer information and I actually auto-generated all this stuff using a Python script which is also in the description if you want to go and get that if you want to generate all these different pieces or like I said I have all these commands inside here you can see right here I'm going insert into customer first name last name email companies street city all of the information for all of our individual customers all surrounded with parentheses with a comma at the end to separate all of the customer data semicolon at the very end execute that then we can come in and go select everything from the customer table execute it and you can see all of our customer information is being stored in the database as well all right so neat neat neat stuff now I'm gonna do the same thing for our salespeople so once again come in here paste that inside of there here's insert into sales person all of the individual pieces and at the very end put a semicolon don't have as many salespeople I only have five of them and we can come in here boom and then go and select everything select everything from sales person table and that's all been properly and put it as well one thing that I have noticed is with my sales order table I recommend click on that if I come down here with my purchase orders the way I organized this was the purchase order numbers are going to be the year month day as well as the time the purchase took place well I actually need a bigger number than an integer inside of there for that so what I want to do is come in and alter that table so I'm going to say alter table sales order and I want to modify the data type so I'm gonna say purchase order number and I'm going to change it into a big integer that's gonna be unsigned and not and all and I can put this on a separate line if I'd like so purchase order number which was an integer right here I'm gonna make it into a big int instead that's unsigned and not null execute that I could see down here response is where there would be an error message if there was an error but there isn't so we come in here and we can refresh this and now you can see it's a big int and it's gonna be able to hold my gigantic purchase order numbers had an other error here also with my sales order in which I was trying to auto generate the secret codes so here's credit card secret code well I knew that the secret code could be up to 999 but I didn't set it up that way because it's set up as a tiny int I need that also to be altered that's also in sales order so I'm gonna go sales order modify and then I'm gonna say credit card secret code and I'm gonna change that into a small int so let's change it into a small int unsigned knot and all again and we can go and execute that and we can see if we come in here and refresh this refresh all whoops let's come in sales order and you can see that that's now a small int right there okay so a couple little changes that we make as we create our database totally normal now what I want to do come in and insert my individual items and they're all those are so insert into items and the values we click on item gonna see here size color picture price ID and you can see right here all of those individual pieces of data again surrounded with parentheses separated with commas and make sure that the very last one is a semicolon which it is and then we can execute that and we'll see that item is also going to be set up properly so you can also type in these commands like this it's just kind of a pro can be a problem so harder to read select from item like this run it and there's all those items I can then come in and put in my sales orders so there's all that information a whole bunch of information and sales orders of course is going to be customer ID salesperson ID time order taken purchase orders all that information that is all right here again just make sure that I have a semicolon here at the very very end I do and there it is and we can go and execute that and then we can copy sales order and delete that and select everything from sales order okay and whenever we put through those sales orders we'll be able to see all of them here just a special note here all of my IDs start at 2:01 whenever this is created accidentally hit this twice so it goes from 201 down to 300 for the IDS but everything else there is okie-dokie and very good next I'm going to need to do to finish up all of my input is to input my sales items and there are all the sales items and specifically sales items and this is item ID sales order quantity discount taxable sales rate and then the primary key which is auto-generated there's all of those and this is the reference to these sales orders as you can see they're all in the 200s and go down the bottom and throw a semicolon then execute that 200 rows were affected so that's good stuff and sales items is what we just changed here so let's just copy that get rid of this and then go select everything from sales item just to verify that it went through and it did and everything looks good and there we go now we have all of our data inside of our database and now we can go start working with it okay so we're going to be able to define different ways that we want to look at our data one way is through the use of conditional operators and equals is just a simple equal sign less than greater than less than or equal to greater than or equal to and not equal there's actually two ways to define not equal so let's say we wanted to say something like we wanted to show all sales with a discount greater than 15% how would we do that well we would say select all that's what the star stands for from sales item and then we would define our condition where discount is greater than 15% and there we go and if we execute it you're gonna see all of those although those sales and you can see the different discounts right here so lots of discounts were handed out we're also going to have what are called logical operators which are going to allow us to combine conditions and those are going to be Anne's or or not and let's go and let's see what one of those looks like so let's say we wanted to find order dates for all orders in December of 2018 how would we do that well I'm gonna say specifically that I'm interested in the time the order was taken so I'm gonna say time order taken and from sales orders and then I'm gonna say where time order taken let's just throw this in here like that is greater than and then I'll define my date which is going to be 2018 12:01 and I'll say time or taken again less than and then we'll go and do the same thing with our date and we can throw this on the next line and throw this here and then change this to 31 instead and then we can run it and you'll see that there were two actual orders taken you'd also be able to list out any other additional information so inside of sales order we could say for example customer ID so if we wanted to see what the customer was that did that we just tack that in there with a comma in between them run it and there you can see the customer ID that matches up with it you're also going to be able to do the same type of thing but yet by using between so you can say time order taken and then inside of here go beetween and then get rid of that space this guy right here and get rid of this bring that over here and then throw that up there and then run it again and you're gonna see you get the same results so two different ways to get to the same information order by is going to be another command we can use to determine the order of the information that's being output so we could go do our discount again here it is so we can say that we want to get everything so that's gonna be star from sales item where our discount is greater than 0.15 and then we could say order by and discount and if we execute that you're now gonna see that the discounts start off low and grow as they go up so that's how order by can be used you're also going to be able to let's say we wanted to go from high to low we just put descending inside of here and now we can change that up we're also let's say we wanted to get just the top five discounts at the very end of this we could say that we just want the first five just by putting limit inside of there five and there those are and then we could get the next five by just putting a comma and 10 after that and then we would get the next grouping okay some more stuff we can get we're also going to be able to come in here and combine data so let's say we wanted to come in and get the first name as well as phone and state for everybody that lives in the state of Texas so we could go select and concat and follow that up with our first name and then put a space inside of here another comma and last name and could cat we'll combine those two or concatenate that's what the word stands for and another thing we can do an alias let's say we wanted to list this as name in our output we just put as name and that's gonna show up in the table whenever we go and execute this we could then get phone and state and then from customer where state is going to be equal to Texas there it is so that could be useful for a salesperson or something and you can see right here there is the name alias that I referenced and then you can see the phone number and the state that have shown there also so a whole bunch of those guys we'd also be able to perform all sorts of different calculations inside of here using these queries so let's say we wanted to go select and get the total value of all business shoes in our inventory for example we could go select and product ID and then go sum and price and we could change this we can use alias again as value from use the item table where the product ID is equal to one and you can see we got our total right there distinct is going to be another command we can use to help eliminate duplication inside of our results so let's say we wanted to get a list of states we have customers in we could say select and then distinct state from our customer table and let's say we want to order them alphabetically order by state run it and there we go got the five states where we have customers we could also come in and say we wanted to get all the states where we have customers not including California and to do that we'll say we would do all of our conditions inside of where clauses so we'll say state not equal to California run it and then we just get all of our states excluding California the in phrase can also be used to test if a value is inside of a list so we have select distinct States from customer and then we'll say where state in and we'll say that we just want those states that match with either California or New Jersey so we can say do we have customers in California or New Jersey and comes back and you can see that yes indeed we have both all right so there's a couple different ways we can use queries and I'm going to cover many more but now I want to talk about getting data from multiple tables now we're gonna be able to get results from multiple tables with either inner joins outer joins or unions and I'm going to show you examples of all of those and the most common join is the inner join that's what you're gonna use most of the time and with it you're gonna join data from two tables in the from clause with the join keyword and then again we're gonna have an on key word which is going to be used to define the join conditions so let's say we wanted to do something like get all items ordered ever and then sort them by ID while listing their price so we could say select item and ID and price from and we'll say item and then we want to do an inner join so that we join the items table with our sales item table and our conditions going to be defined in after on and the cadet condition is going to be item ID is equal to our sales item which is item ID okay and you can see how we're able to use primary keys and foreign keys to join data and then we'll say we want to order them by the item ID run it and there you can see and there is all of our pieces of information for our item ID as well as the price for that and that is just the beginning of the usefulness of joins mm-hmm we're also going to be able to use the join condition to find IDs that are equal in the tables item and sales item and in this situation where we join tables while checking for equality between a common column this is called an equal join we're also going to be able to define multiple different join conditions with logical operators so we have item ID and price from our item table in our sales table let's say we wanted to come in here and keep this part right here where we're joining using the foreign key and the primary key but we also want to say and the price is greater than 120 dollars for example run it and there you can see that but now we're gonna get into some funky stuff because what I want to do here is actually show you how to join three tables so let's say we wanted to get the orders quantity and the total sale so we could say select sales order and we'll get our ID follow that up with sales item quantity and the individual item price and then we want to say sales item quantity times the item price and we'll go and create an alias which is going to be total and we'll say that we want to get that from the sales order table which we will join to our sales item table and the condition is going to be that our sales item sales order ID is equal to whoops I accidentally put a space inside of there is going to be equal to our sales order ID and then we can do another joint and we'll say join item on item ID equal to sales item item ID and then let's order this by our sales order ID so now we're getting into some really good stuff and we run it you're gonna see here quantity 1 the price of the individual item was $139 26 cents and the total of course is the same and then as we add in additional information here we have quantity 2 you can see the total sale and how they match up with the different order IDs all right but as we go we're gonna get into even better stuff but one thing that I left off are the arithmetic operators that we have so of course we have plus minus subtraction and integer division as well as regular division and modulus is just it provides the remainder of a division and we'll see more on that and we'll do more with them as well another thing it's gonna be available to you but it's kind of frowned upon and it's you're not supposed to do it but you can do it so I'm gonna show you is actually defining the on condition with where instead of on so you could actually come in and go select item in price from our item table as well as our sales items table and then you could say where item ID is equal to sales item dot item ID and price is greater than 120 and order by item ID and run it and you can see you get those same types of numbers that we saw before so just another option and that's just the beginning energy lines much more is coming but now I want to talk about outer joints now an outer join is going to return all of the rows from one of the tables being joined even if no matches are found so a there's gonna actually be two of them a left outer join is going to return all rows from the table being joined on the left while a right outer join is gonna return all rows from the table on the right now it's common practice to avoid right outer joins so I'm just going to use left in this situation so here let me just give you an example so we can just say select and let's say well name and supplier and price from product left join with item so here's the product table and the item table and we're using a left join here on item to find our condition for how this is going to match up so product ID equal to product ID and then order by name whoops name there it is run it and there you can see all of our results once again there is our name and there's the supplier and there is the price for the joining of the product and the item tables alright so a quick example of an outer join we'll do some more later on but now I want to call talk about cross joins now a cross join is going to include data from each row in both of our tables so what I want to do is grab information from the item as well as the sales item table and this is going to produce a whole ton of different results and because there is note no join conditions whenever you're using a cross join you're really rarely going to see them but I just want to cover it just so that I can so what you do here is you say select sales order ID and quantity and product ID from item table and you type in cross join that's where the name comes from and sales item and then see there's no conditions here you get it all order by and sales order ID and if you execute it there you can see there's every single single thing all right so that's a cross join and while we talk about unions basically a union is going to combine the results of two or more select statements and one result now one thing that's important to understand what the union is each result must return the same number of columns and data in each column and on top of that the data and each column must have the same data type so let's say we wanted in just as an example to send birthday cards to all our customers as well as our salespeople for the month of December how could we do that well we're gonna say select and first name and last name and Street and city and zip see if I can spell zip right there we go and birth date and we're gonna do this from the customer table where month is how you get the month only out of see if we have this is customer right you see here is a birth date and there's a date that's the Burton tape data type if you wanted to just get the month out of it you could also use this little time stamp you could just type in this and then birth date equal to 12 and see here is where Union comes in it's gonna allow you to combine data from two different selects so we don't this is almost exactly the same so let's just copy this and paste this and here all of this is exactly the same except we're gonna change this to salesperson and everything else is the same and we'll end this off with order by birth date and run it and there you can see all of the customers as well as sales people who have birth dates in December and when those birth dates occur all right and all that other information so neat stuff so that's how unions work they allow you to basically unite to select queries on a side note probably also should talk a little bit more about Nall you're gonna be able to go and check a for null and null is used whenever a value is not known so what we'd be able to do here is say something like select Product ID and price from the item table where price is equal to null and nothing comes back that's the reason why is because I'm all of my products have prices assigned to them you're also ever gonna be able to come in here and just change this to is and that's gonna give you the same result but you can also come in and go is not null and that's gonna give you some returns here because now everything has prices all right so just a little trick for using null and checking for null and so forth and so on and now I want to talk about like and regular expressions okay so like is gonna be used to search for simple string matches so let's just give you some examples let's say I wanted to find any customers whose name begins with an Emma so I can say first name and last name from a customer and then where first name like and then follow that up with Emma and then I can put a percent sign inside of there and that is going to give me my matches and you can see there is a good number of them are okay another thing well basically what the percent sign is gonna match for this guy is it's gonna match for zero or more characters you're also gonna have an underscore which is gonna match any single character so let's go and do something similar for this let's say I want it to find any first names that begin with a letter a I can come in here and say hey like that and then let's say I want to find if there is an Ashley so that would be a sh le why I don't know if there's an Ashley but we'll find out yes there is so you can see you do an underscore for each individual character and that's basically what you can do with like you could however also let's say we wanted to return all customers whose first name begins with a D or whose last name ends with an N well we can do that so we'll say first name like and we'll throw a D inside of here and percent and the if we were gonna say or and then last name like and then we'll go % and and I think I did oops I have to put that there okay so we got those and let's run it and you can see there we go all those matches alright so that's basically what likes gonna give you it's gonna give you an ability to match or 0 or more characters or match for any single characters then we get to regular expressions they are a lot more complicated and I'm not going to be able to cover everything B but I have an entire tutorial just on regular expressions but I am gonna give you some examples and I'm also gonna give you an overview of what is available to you with regular expressions alright so here are some regular expression patterns a period is gonna be able to be used for any single character just like with like and underscores any single character well periods of Sam star is gonna match for 0 or more of what comes before it plus is gonna be one or more the caret symbol is gonna represent the beginning of a string match that you're looking for while a dollar sign is gonna represent the end of a string anything any time you want to match like for example the characters a and B just put them inside of brackets and if you want to match anything except a and B put the caret symbol inside of there this guy is gonna match all uppercase letters this is gonna match all lowercase and all numbers if you put a carrot in there it'll match anything except those things and if you use the curly brackets an n after something it's gonna match n instances of whatever it is so let's say you wanted to look for mall and you wanted to match for two L's well you would have L and then you'd have inside of curly brackets - so it's gonna match those two L's you can also match between a M or n instances so it would match for Mall or whatever else pal I don't know and you can also use this or symbol expression to come in here and match for one thing or another thing so here's some examples of using regular expressions let's say we wanted to match for a first name that starts with the letters M and a we could just go in first name again we're gonna use the customer and then inside of here instead of like we're gonna put this here and then we will say we're first name regular expression and then symbolize that we're looking for the beginning of the string that's what that carrot does and M a like that and then like this and then it will give us all our matches for names and start with M a we're also going to be able to get let's say we wanted to get the last two letters be easy well we would put a dollar sign inside of there and nothing comes back in it don't know why but that's how you would match for that oh I know why because let's change its the last name now to work and there we see all the martinezes that pop up we could also come in and match last names that contain just certain letters so let's say we wanted to look for Easy's or put an or sign in there s o n run that and you can see all those show up as well and we could also come in and say like a dollar sign inside of there and you're gonna get the same type of results but just want to run that so you can't combine all those different little tools and patterns and you can also could come in here and say that you wanted to match for last names that contain letters W through Z for example put those in brackets and there you go and you could do last names that have at least two L's inside of them so go this and like that and whoops only put that there okay and run it and you'll see Williams comes up so just a whole bunch of different things like I said in the description underneath I will put a link to my regular expression tutorial to give you an overview it takes I could do an hour on regular expressions or more so that's just an overview of how you can use regular expressions when a MySQL and now what I'm going to do is show you some ways to summarize your results okay so group-by is going to define how the results are going to be grouped and we have another function called count which is going to return the total number of records that match so what we want to do here is use group by to return a single row for each unique value and like what we're going to do here is say or look up how many customers have birthdays in certain months so we'll go select and month and birthdate and there's tons of these functions I'll show them to you after I cover group by so I'm going to say as month that's just going to say what is going to be displayed at the top of the table and let's say we want to count all the individual amounts that's going to allow us to do that and we'll name this amount then what we want to do is define we want to take this from our customer table and we want to group whoops go down here group by month so it'll just have everything grouped into all the individual months and order by month okay so that's going to show us this and you can see right here that in the month of January we have one customer and so forth and so on for all of the other months of the year all right so that's one way to use group by we're later on in the tutorial we're gonna do more complicated stuff but let's say we wanted to find those months that had at least two or more customers with a birthday in them well to do that we another command called having and you have to put having after group by so we could say something like having amount greater than one and run it and now you can see it's only giving you the results where we have two or more people having birthdays in individual months so pretty neat stuff and let's go and do another things kind of interesting to finish up working with group by let's say we wanted to get the total purchases from each company how would we do that well you go select and customer ID and the company involved and we want to count them so like that and we'll call this as purchases and we're gonna get this from our sales order and we're gonna join this to our customer table and we're gonna do that using our condition here where the customer ID is equal to customer ID and then we will group by customer ID customer ID and run it and you can see all the individual companies and how many purchases each of those companies made so pretty neat stuff it's it's just so easy to work with data using SQL if you know how to use it properly and now what I want to do is jump over and show you some of the other aggregate built-in functions alright so here is a whole bunch of them and I've been using them in my statements so I think you'll be able to pretty easily figure out how to use them and as the tutorial continues I'm gonna use a couple of these so you can see we can get the absolute number there's all kinds of trig functions you can average and by averaging we would average the column name ceiling is going to round up degrees is gonna convert from radians to degrees you can see that there's exponent floor it's going to round down there's two different log functions the mod stands for modulus which gets you the remainder of a division pi is built-in to MySQL we can go and find the power of different values we're going to be able to convert from degrees to radians you're going to be able to generate random values from zero point zero to one point zero and then you can go and add or multiply those values to get all kinds of different random values you're going to be able to round basically it's going to round down if it's less than then you know like for example four point five is going to turn into four and if it's four point six is going to turn into five that's what the round function does we have square root we have standard deviation and a whole bunch of other different things but those are the general functions that I personally have not used in this tutorial or don't plan to use so there you go rough overview of those and now what I want to do is talk about some other different aggregate functions and give you some examples on how they can be used so just to start off your simple let's say that I would want to sum the prices of all my items that I have I can just go select and sum and price and from item and that's gonna run and it's gonna give me my total inventory value of seven thousand two hundred thirty one dollars and fifty eight cents okay so let's go and get even more things so let's say I want to use a whole bunch of these aggregate functions and an aggregate function is just a function that returns a single value from multiple parameters and it doesn't have to be multiple parameters must be one alright so we could say as item and I'm counting all of my items that I have I'm just gonna show you a whole bunch of them I'm going to sum the price as value and what else can we do let's I'm going to show you round and what I'm going to do is go and get the average price divided by two decimal places or I want to use two decimal places and my result and then I'm gonna say that's called average and let's go and get the minimum price as well and we'll call that men do a couple more let's also get our max price and this is for all my different items I have in my items database and let's just do from item okay so that gives me a rundown of my inventory and you can see right here I have 50 items total value the average cost the minimum cost and the maximum cost alright so that's a rundown of aggregate functions through examples as well as a list and now I want to talk about working with views all right so views are select statements that's results are going to be stored in your database so what I want to do here to give you kind of a real-world type of example is I'm going to create a view that contains our main purchase order information so what I'm gonna do is I'm gonna say create view purchase order overview is what I'm calling this view purchase order overview as and then afterwards I'm gonna put in I select with a whole bunch of different joins to combine a whole bunch of data from a whole bunch of tables so what I'm gonna do is I'm gonna say that I want to get my purchase order number from my sales order table so purchase order number this is gonna be like a real useful thing I'm also going to get whatever the company is that made that purchase I'm going to say that I want to get the quantity that they bought so sales item and quantity I'm going to get the product supplier so that's gonna be like Nike or whatever and I'm also going to get the individual product name so product followed with name I'm going to get the items price I am going to go and get the quantity and multiply that times the price to get the total so I'll go sales item underscore item and that's gonna be quantity and multiply that times the items individual price and I'm gonna give that an alias of total alright so I think that's pretty now you know what let's put even more in here I'm also going to go and put the sales person that made the sale into this so concatenate and I'm gonna put sales person and their first name and sales person and their last name and let's give this the alias of sales person okay and now to get that I'm going to join some tables so I'm gonna say from sales order table I'm going to join sales item so those two tables go together and how are they going to go together they're gonna go together by comparing the foreign and the primary keys so I'm going to say on sales item go like that and sales order ID sales order ID being equal to the sales order ID and then I'm gonna join even more so I'm gonna say join item and how I'm gonna join it well I have to give it a condition again it's gonna be the primaries to the regular foreign keys so I'll say item ID is equal to the sales item item id and let's join some more so it's just gonna keep going I just wanted to give you some real-world examples from now on the rest of the tutorial is going to be pretty much real world stuff I'm also going to need to join our customer so join customer two all these tables and how I'm going to do that is going to be on sales so anytime you want to join two different tables what you're going to need to do is find those foreign keys and primary keys that match up so this is going to be sales order let's get out of there there we are border dot customer ID equal to customer ID and I'm also joining the product table so this is a really complicated guy here I'm also gonna go product and how am I gonna match those up guess how I am going to go product ID equal to item dot product ID and then also I have to join the salesperson so I'll say join and this is gonna be sales person she's run out of screen here their sales person and on this is gonna be sales person dot ID equal to sales order sales person underscore ID and then as a final thing I'll say order by purchase order number alright so that is a long query that's gonna give us some crazy stuff and it's gonna store everything interview so let's run it as long as I ain't typed anything wrong it should work and it looks like it did so let's come in here and just refresh all and now you're gonna see over in views that there is a view and there it is right there you're also gonna be able to see our different results here if we go and look at it and how we're going to do that is let's just select all of this stuff and to see our purchase order overview which is going to be stored in the database and it's going to update also as other parts of the database update on its own so I'm going to say select everything from the purchase order overview like that and run it and whenever you do it's going to give you this view with all of that information we requested so pretty neat stuff and very very useful and that's how we go and create views and how they work all right now you're going to be able to drop this view if you decide you don't want it any more just by coming in and saying drop view and then follow that up with whatever the view is you want to drop I don't want to do that and it's also important to know that and as a side benefit to a view is that they are going to allow you to hide or change data and other tables while keeping the view the same and this is beneficial in hiding data from users who shouldn't have access to it directly and one thing however to be aware of is if you want a view to be updatable it can't include the terms distinct union it can't have any aggregate functions in it nor can it have group by or having so knowing that I need to change my view that I had previously and this would be the new format of it basically what I had to change here about this is I had to remove the aggregate functions because I didn't have any of the other issues so that means I have to get rid of concat and all that stuff let's just come in here and go sales F name well I could do a concat in in another way but I'm just gonna do it this way right now so this is basically the way the view is gonna look that is gonna allow it to be updated like I said just remove the total aggregate you can pause your video if you want to type that in or like I said all this is available for download for free so this is the new one so we can come in here and run that or actually you know what I have to go and drop the other one first so let's do that okay so let's get rid of this and to purchase order overview so let's go drop view purchase order overview like that run that get rid of it and then go in and create the new one throw that inside of there now run it and now we have the new one and now if we wanted to get that aggregate function like the totals and things like that we still can all we need to do is to come in and do something like so act everything and then we can add to it so then we'll also say quantity times price as total from purchase order overview and there we go now we've got our same results flip so you don't have to save that and run it and you're gonna see we get the same results that we were expecting all right so pretty neat stuff and there's an example of how views work and some of the issues with them and how to solve them and now what I'm going to do is talk about stored programs now you're going to be able to write programs that are similar to traditional programming languages and there are different types of stored programs there are stored procedures stored procedures and there are stored functions and there are triggers and events and I'm going to show you all these basically a stored procedure can be executed by an application that has access to your database so PHP can access it basically anything can and a stored function is going to be executed using your SQL queries like you keep saying triggers are going to execute when either insert update or delete commands are executed and events are going to execute at scheduled time so I'm going to show you examples of all of them so what I'm going to talk about first is how to create a stored procedure that's gonna let you get a list of customer company names so how you create it is first off you need to define the database you're using so I'm gonna say use sales DB so there's the database we're using and then what you need to do is say drop a procedure if exists if I already created a procedure with a name as you saw previously it's gonna give me an error unless I say that I want to delete the previous and update it with the new stuff so that's what this is gonna do so I'm gonna call this get customers that's the name of my stored procedure so it's going to if I change it it's gonna just say go and overwrite then what you need to do is switch the delimiter like this and I'm gonna switch the common delimiter is with a semicolon I'm going to switch it and as you see every statement ends with a semicolon I'm gonna change the delimiter to two forward slashes and this is going to allow me to use semicolons inside of my stored procedure and then whenever I'm done I'll switch it back so now to actually create the stored procedure you go create procedure and you already saw the name of it get customers and you can pass values inside of here so I'll say get customers I'm not gonna receive anything though to start off and then begin is going to assign the beginning of where the procedure code is gonna lie and then you're gonna do whatever you want to do so I'm gonna say select customer company from customer just do something pretty simple here and that with a semicolon and then we're gonna say end and two forward slashes and then at this point I can say that I want to switch the delimiter back to semicolons and then to call for this stored procedure to execute I say call get costly you can call this you know outside of here but I'm just calling it here so you can see it execute and I'll run it whoops get customers there we are and I have that the same alright good get customers and you can see it gave us a list of all of our customers alright so neat stuff and we're gonna do way more complicated ones after this let's say that for example I'd like to create a procedure that returns inventory value based on supplier a lot of this stuff is gonna say the same sales DB is gonna be the same delimiter is gonna be the same down here I'm gonna say get supplier value so I'm gonna say get whoops do it here to whoops get supplier value so plier underscore value and create that procedure so get supplier value and then this guy's actually going receive a argument which is going to be the suppliers name so I'm going to say in is defining that I'm going to be receiving information into this into this procedure so I'll say the supplier is what I'm going to call it and you have to define what the data type is going to be so there's that and then I want to declare a couple things inside of here so I'm gonna say I'm going to declare some variables that are only gonna be used in this stored procedure and they're gonna hold results of a query so I'm gonna say supplier name variable number of characters 30 in length you have to define the variable name as well as the datatype for it and you'll see how a little data is gonna be stored inside of it I'm gonna go and get price sum which is gonna be a decimal that's gonna be 8 potential total numbers with two decimal places and then I go and issue my query get the results and store them so select and product supplier and I'm gonna sum the item prices and this is where you define where those values go so product supplier is gonna go in supplier name and sum is going to go in price um so you just say into supplier name this is where the results go comma and then price um is where the other one goes so throw that right there then I just continue the way I would always do it I'm gonna say that I want to get information from the product table and the item table and then the condition which is going to be that the supplier is going to be equal to the supplier that they passed into the stored procedure up here see this guy right there and there it is and then after that I can output this information and how you output information from a stored procedure is with using select so I can concatenate or join information so I'll say supplier name and then separate everything with calm and I'll say inventory value dollar son and then after that I can say price sum and then if I want to give it a custom title as output for example and that's everything I need to do Oh actually no I'm gonna go get supplier value so I'll get all the values for whatever I throw inside here and I'm gonna say that I want the total value of inventory from naik so there's everything and if I run it you're gonna see Nike inventory comes back and it has a total value of twenty one thousand six hundred ninety-four dollars so that it out of money in like tennis shoes and now I'm gonna show you some more stored procedures okay so now I'm gonna show you how to accept multiple values and you already saw this in parameter right here and what it does is it just designates a parameter expected to be passed or a procedure but what I didn't tell you is that it's passed by value and its value is only affected inside of the procedure so it's important to know so now we're going to create another procedure that's going to get customer birthdays so let's just come in here and let's call this get customer birthday and then of course we're going to put that down here as well in the procedure name so let's throw that there and then we're going to use an out parameter and what it's going to do is pass values back to the code that called for the procedure to execute in the first place so we'll come in here where it says in and we'll just have this be the month so let's just call it the month and it's an integer but then we're gonna list a whole bunch of other different things inside of it so we're going to also say out and we'll say birthday month also an integer out birthday day also an integer we'll go and get the first name variable number of characters and let's say it's 20 out again which is going to be the last name again variable number of characters which is 20 and let's say that we want to call the customer to wish them a happy birthday let's go and get their phone number as well variable number of characters also 20 all right so now we defined all those let's go and get all that information so inside of begin and ends we're gonna say select month and we'll say birth date and then we'll do day and again birth date first name last name and phone number and we're gonna be taking this from the customer database of course so from customer and then we'll say where month birth date is equal to the month that they passed in so they'll be able to pass in a month and it'll automatically send you back all the customers who have a birth date in that month everything else here is the same and except we're gonna call our new get customer birthday pass inside of it the month of what would be September and then what you have to do is put at and all of your different amounts that you have up here so you would have birthday month this is where it's going to be coming from and again at and birthday day at first name at last name and at phone number and close that off and I think everything is right there and if we come in and execute it oops I went I have an error down here that that uh what's going correct that's somewhere I put BIR day all right here get customer birthday so everything else is fine and run it again and you'll see all the different customer birthdays that we have which are right there another thing I don't know if I even showed you is if we go and refresh all under these stored procedures you're going to see all of those procedures that we have created so far all stored inside of the database as well as the view that we had before so pretty cool stuff now what I want to do is go and demonstrate if and else if and else alright so what we're gonna do here is we are going to check for our monthly orders and to see if we are performing well or not so I'm going to keep everything basically the same except I'm going to change a couple different words here a couple different names of course so I'm gonna call this check month orders and if it's at a certain number it's gonna say it's good and if it's not it's gonna say it's bad so let's go and change this name of the procedure here we're going to receive a month which is an integer again and then down inside of begin we're going to declare a variable and it is going to be total orders which is an integer and then we'll go a select count and purchase order number and then we're gonna throw that into our total orders variable that we have so that's where that's going to go the value that is returned whenever we call count on purchase orders numbers and then we'll say from sales border where and we'll say month time order taken equal to the month that the user passed in now what we're going to do is we'll use if Elson else if statements to check the to basically provide output so we'll say if total orders is greater than five then we went out put a message so we'll say select and cat total orders and then we can say orders doing good we'll say that if it's five orders or more we're doing good and then we can change our output for this to just output as the top of the table that's output then we can say else if total orders is less than five then and again we're going to use something very similar to this and we'll just copy it paste that inside of there and we can say doing bad and then finally as a default if neither of those is true well we know we have exactly five orders so we could say something like on target and then after you're done with the if block you have to say and if and put a semicolon and now to test this we can come in and let's just go check month orders and call get month orders we don't need all this stuff so let's just get rid of it and then the month that we want to check let's just check September again and let's run it and we see that we have nine orders in this month of September so we're doing good all right so there is another stored procedure and of course it's going to be stored over here and there it is and now let's go and do another one where we're basically going to do everything we did here except we're going to use case all right so basically all everything is exactly the same except we're gonna use case in this situation and basically case is gonna execute different code depending on an exact value for total orders or a range of values so we're gonna say case and then follow that up with win total orders is less than one then and if that's true we will execute well I thought I had it saved but I don't so we'll say select concat and total orders and again we're gonna print out exactly the same thing so it's just another way of doing the same thing we'll say terrible in this chelation and then we'll put that as output like we did before and now let's go and copy this because we're gonna be doing the same type of thing then we can say when total orders is we can say greater than 1 less than 5 so you can stack them oddly like that shortcut and in this situation we can say get better again do another win and we'll say total orders equal to 5 then and do another one inside of here and we'll say that this is on target and then finally as a default we'll say else and paste in another one again and in this situation we'll say doing good as a default and then after we have all those done we have to come in and say in case and you could also have these bead you know just individual numbers and so forth and so on of course and then check monthly orders let's just leave it at September again and run it and you can see get better shows up alright so that's how we can use case and how closely it relates to the use of if-else and else if and just so you know we would also be able to say case and put total orders inside of it like this and then afterwards say win one then and then execute certain code all right so more cool stuff and now I'm gonna show you how to sum up values to a maximum number using while alright so there's multiple different ways we can do loops here so I'm going to show you the while loop first and I'm gonna call this loop test and of course change this to loop test and this is going to receive a value I'm just gonna call it I and then down inside of begin I'm gonna declare a couple variables so I'm going to declare J which is going to be an integer and it's gonna have a default starting value of one spell declare right of course then down inside of here and we'll declare another one and this was going to be total sum int default value of zero and then we'll say while J is less than or equal to I continue looping and here's how we set a value so I'll go total sum is equal to total sum plus J and then also we will increment the value of J plus one and then after this we have to call end while loop and then after we do that we can output on the screen the total sum so sum is equal to and we'll follow that up with total sum and we'll call this as sum course down here this is called loop test so we'll go a loop test call that procedure and then throw a 5 inside of there as an example and run it and you can see that it gives you a final sum of 15 so there is one loop and now I'm going to show you how to replace the while with the repeat loop alright so I'm going to keep everything exactly the same except I'm going to say repeat and set total sum is equal to total sum plus J and again set J equal to J plus 1 and then what I'll do is say until J is greater than I and then after we do that we can say and repeat and if we run that you say we get the same exact result so that's how repeat works in much the same way as well and cover a lot of things there so why don't we now go and talk about cursors okay so cursors are basically going to allow us to iterate through rows and work with them individually so in this example what I want to do is get a comma-separated list of companies using a cursor so I'm just gonna call this get companies get companies and let's go and copy that and paste that inside of there and I'm gonna use in out which is going to allow us to both receive a value and then output a value so I'm gonna call it company list variable number of characters and it's gonna be 1,000 and get rid of this integer because it's gonna return a list in this so that's the reason why I need so many values inside of there first thing I'm gonna do inside to begin is say declare finished and this will be integer default 0 which is gonna stand for false and whenever this is set to the value of 1 we know that we've reached the end of the list and we know that we can just output the information at that point also going to declare company name and this is gonna hold each company name from our list whenever we output them so variable number of characters and 30 should be enough and I'll have the default set nothing and now what I need to do is declare a cursor for their our customer list which is gonna be populated by select so to do that you say declare and I'm gonna call this purse or company and then you go cursor for and then the select which it's gonna be select company from the customer table I'll then come in and handle whenever no more companies are going to be found so declare continue handler we'll get into error handling here soon for not found set finished two one and that's whenever we'll know that we have all of our companies then we'll call open cursor company which is going to initialize our result set and then we're gonna say get company call one and loop which will continue looping until a leave message is called and to get them would say fetch cursor company into company name and then we're also going to say if finished ever has a value of one well in that situation we will call leave which is going to leave our loop leave get company and then we can define and if and then after we do all of that we can go and add the company to our results a set company list equal to call can cat and company name and then let's put a comma between all of them and company oh this will be company list close that off and then we can end our loop get company and then after we have all of that set we can release the memory assign for our cursor and to do that is close cursor company and there we go and then down here we're gonna generate our list of companies using our cursor so first off we're gonna say set at company list and equal to nothing in the beginning of course then we'll call get company or companies is what I called it right get companies yep there it is and like this and what we'll pass into it is our company list which is going to be populated and then after we have all that done we can just say select and output our company list and if we were on it you can see there is our generated company list all right so that's how we can use cursors and now I want to talk about error handling all right so you're going to be able to handle errors and your procedures so that you can give understandable error messages as well as maybe try to head off the error before it occurs using error handling so what I'm going to do here is I'm going to actually on purpose create an error where the user tries to use a key already in use and then provide a custom error message so I'm gonna call this get product type actually let's call it create product type and go and copy that and paste this inside of here this is going to receive a value and that's gonna be a product name so product name and that's gonna be a variable number of characters but we only need like 30 in this situation and then we're also going to receive another one and that's going to be product ID which is going to be an integer all right so down inside of here what we're gonna do is to block what is called a duplicate key error so we'll say declare exit handler for 106 - and that is the specific code for it and then inside of it will say begin and we'll define a message that we can put on the screen say something like you tried to use a key ID that all ready exists you come over here and there's a backslash with a quotes if I didn't show you how to use those quotes and the output you can't use ID close that off and then follow that up with the product ID that they passed inside of it which is product ID close that off and then we'll just say end and let's tab that in so we can see where that's at and then what we're gonna do is try to catch any errors that are not caught by the above this is like a default way to handle errors so we'll say declare exit handler for SQL State this is like the default and again you're gonna do pretty much the same thing you're gonna say begin and then you're gonna output a message so let's just copy this and in this situation because it's not a specific exception that was triggered we're just gonna say an unknown error occurred and I'll put that on the screen and then we'll say end again and now we're gonna actually create the error itself or we're gonna try to insert into our product type an ID that's gonna trigger an error so we'll go insert into product type and we'll pull it name and ID and values and that's gonna be product name that was passed inside and product ID and then I will have let's say I want to output all of my product types if this does go through but like I said I'm gonna make it go on purpose and mess up if however up here where we have exit if instead we typed in continue this would execute no matter if there was an error or not but we don't want it to do that we want to just show our error message and then down here we can say call create product type and we'll say business casual which is something that we don't have in our product type but we're gonna say we want to put it in the first ID and that's going to cause the problem and if we run it you're gonna see that we get our error message you tried to use a key that already exists you can't use ID one all right so there it is so that's how we would use a built-in exception that's inside of MySQL and now I'm gonna show you how to raise user-defined errors actually I'm not gonna change that many things in this I'm gonna keep it basically the same so here I'm gonna say that I want to call this insert product type and what I'm going to do here is block a user from entering a duplicate value in the product type table because that would also be bad so let's call this insert product type and they're gonna send in a product name as well as a product ID just like they did before and then I'm going to inside of here declare a new variable so I'm gonna go declare name in table and and this is gonna hold the result of checking for a name in product type and you know what this is all getting in the way so let's just get rid of it so there we go got a clean surface to work for him and we'll call this go insert product type and then this is going to be athletic so we will allow them they're actually going to try to put this in ID 4 which would be legitimate since we only have 3 of them bought athletics already in there and we want to block them from putting in duplicate data so we'll go declare duplicate product name condition for SQL state and I'm going to call it put 50,000 inside of there which is just something that's not taken and this is going to define the name for our custom error event and then I'm gonna say declare exit handler for duplicate product name and this is going to of course be called if the custom error is thrown again we'll say begin and this is going to be the message that displays on the screen and it's just going to be you and I can just go and put regular old quote inside of there I'm gonna need a back slash it since I have double quotes around it enter a product name already in the table and that's all I need to do put the error message inside of there and now what I want to do is check if the name past is already in the table so I'm going to go set name in table equal to and throw select inside of here and I can do a count that's a very easy way to figure out if we got a match or not from product type where and i'll put product type name is equal to come over here and product name which is what was passed inside of it put that there and that is of course this guy see product name then after I do that I can go in here and use as an if statement so I'll say if name in table whoops spell name correctly name in table is equal to one well then and I can signal for my air handling too or signal that there has been an error so signal duplicate product name and else I will allow them to insert it insert into product type in the name and ID cells the values product name which words passed in and product ID which was also passed in and then I have to say and if everything else here is fine that's fine that's fine that's fine as long as I don't have any errors I ran it and you can see what's you have an error in your SQL syntax check the manual but oh I accidentally typed condition wrong it's always something silly condition there we go and run it and now you can see that you can't enter a product name that's already in the table comes back alright ok so there's a way to handle custom errors that could pop up inside of your code and now I want to talk about transactions now you're going to be able to combine multiple SQL statements using a transaction and within the transaction you can define when statements are committed as well as when they are rolled back and of course they would be rolled back in situations when something goes wrong so what we're gonna do is we're going to call this insert sales item so let's go and copy that and let's paste it down inside of here and this one is not going to receive anything so let's just go and get rid of all those parameters and then down inside it here we'll declare SQL error which will be either true or false depending upon if an error occurs so I'm gonna have this be a tiny int and the default value is going to be false then we will say declare continue this time I'm not gonna exit I'm going to continue and we're going to look for an SQL exception and if we get one well in that situation we will say SQL error is going to be equal to true and now we can start our transaction and how you do that is go start transaction and then we'll just list a whole bunch of them so we'll say insert into sales item values and we can throw 3 263 one actually there's no point in me going typing all those in there they are I put them all in there and you can pause your screen if you want to type them in then after I have those all in there I'm going to say if SQL error is equal to false then that means I want to commit all of these so I'll just say commit and then I can now put some information on it so I'll say select flips this actually and I'll say sales items inserted else come out here well then I know that I don't want to put him through so in that situation I'll say roll back and none of those values will be inserted and then I can put out a message that says that they were not inserted and we can call this transaction rolled back and then come in here and say and if and change this to insert sales item and then that's not gonna receive anything so let's just get rid of it everything's built in and let's run it and you can see that everything went through properly all right so cool stuff and that's how transactions work and now I want to talk about stored functions now much as you created stored procedures you're also going to be able to create your own functions which are called stored functions and your functions can only return a single value however it's very important to remember and what we're gonna do here is add up a order that you define so let's come in what are we gonna call this guy let's call it get order total so get order total get order total and we'll change this from drop procedure to drop function and we'll copy this get order total and we'll paste this inside of here of course this is going to be changed also from procedure to function and sales order ID will be passed in and it's an integer and what's important here is we have to define the data type that's going to be returned this is going to return a decimal so returns decimal and we can put it on the second line if we'd like we're then going to say deterministic reads SQL data and deterministic means it returns the same result when the same exact argument is going to be passed inside of it and we're also defining that we're going to be reading SQL data but we are not going to be inserting it inside of begin will say declare order total and this is a decimal 8 total digits with two decimal places and order total and then we're gonna sum all the totals based off the sales order ID that's passed inside of it let's get rid of that don't know how I got in there and we'll say select some sales item quantity times item price as total we'll put those into our order total that we defined right here and then we just need to combine a whole bunch of different tables so we'll say from sales order and do a join with sales item so combine those two tables and then we have to define how they're going to be combined of course using IDs just like we have in the past so sales order ID equal to sales order ID and then we're also going to have to join this to the item table so join item and how we'll do that is on item id being equal to sales item item ID and then we will also well that's all we need so then we'll say where sales order ID is equal to the sales order ID that was passed inside and then we can use a group by sales order ID and then we can order by sales order ID as well then after we do all that we just say return order total this ends and then down inside of here we can go well that's basically all we need to do so we can just hit our lightning bolt and we're gonna actually call this from a different quarry as long as no error occurs doesn't look like it has so let's go over here do a refresh and we can look over in functions and we could say get order total there it is right there and then we can come let's close this go file new query tab close that there we are and what we're going to be able to do now is do something like select sales order ID get order total pass in sales order ID display it as order total and then we're gonna get this from our sales order table and then order by sales order ID as long as the function works right and it looks like it did and you can see right there it's listing out all of the sales order IDs along with the total value and in some situations these orders don't have actual items in that and that's the reason why you have null values alright so cool stuff and now I want to talk about triggers all right so triggers are going to be executed automatically anytime a SQL query is executed on a specific table so what I want to do here is create a procedure that checks if a customer with a new first and last name already exists and if it does I want to throw an error so what I'm gonna do here first is I'm going to go drop procedure if exists and this guy is going to be called customer in DB and create procedure Sager and customer in DB it's going to be the name here throw that in there and then of course we're going to be passed in a first name which is a variable number of characters which it's a 30 in length and then we're gonna also get passed in a last name so let's throw that there change this last name and that's good and get rid of that extra comma there we don't need a return statement here and inside we don't need deterministic either or define that we're working with that and none of that stuff all right so inside of begin we're gonna say declare and we'll say match found again this is either going to be 0 or 1 depending upon if it was found or not and then we'll say select count into and we'll put match found so if it comes back it wasn't a match it's going to be 0 which is gonna be false and otherwise it'll give you another value and then it'll be true and we're gonna do this from our customer table and we'll say we're first name for our customer is equal to the first name passed in and last name is equal to the last name that was passed in close that off then we'll say if match found which means greater than 0 because it could be a value bigger than that so we'll say if match found whoops put that there in that situation I want a signal that we had an error I'll just have this be 50,000 again and in that situation will say set message text which is going to be our error message and we'll put customer already exists come down here put and if all of that is all perfectly fine and now what I'm going to do is create a trigger so let's just get this out of here and I'll just go delimiter whoops spell that right create trigger customers be for insert and we'll say before insert on and we're gonna tie this to our customer table and we'll say for each row I want to do something and I'll say begin call and check if it's in the debate customer in DB and it's going to be the new customer that we're trying to insert into the database new again last name and then we could say and reset the delimiter and then we can come in and get it selfs a new query tab and we'll go and try to find a customer select every whoops everything from customer and run this and we see Christopher or Jones let's look at these other different customers we have here Jennifer Thomas will have Jennifer Thomas be what we're trying to work with so we got this here just put a lightning bolt and then let's go and update this refresh all customer and triggers and you can see customers before insert shows up inside of there and that's the trigger we created it so that's where it is and what we were we gonna do will do melissa more instead okay try to insert something that's already in there so Melissa Moore is already there all right cool and then come back here and do the lightning bolt again on this guy and then let's come in here and let's execute it and you can see that since it's already in there it shows up the customer already exists alright so there you go guys that is a heck of a lot of information about using SQL with MySQL hopefully you guys enjoyed that and like always please leave your questions and comments below otherwise until next time

Channel: Derek Banas

Views: 92,642

Keywords: SQL Tutorial, Learn SQL, MySQL, MySQL Tutorial, MySQL Programming, SQL Programming

Id: XqIk2PwP0To

Length: 137min 34sec (8254 seconds)

Published: Tue Oct 01 2019