MySQL Full Course for free 🐬 (2023)

hey what's going on everybody it's your bro hope you're doing well and in this video I'm going to show you how we can start writing SQL using the MySQL workbench so sit back relax and enjoy the show all right let's get started everybody SQL it's an acronym meaning structured query language SQL is used to create retrieve update and delete data from a database suppose we own a business like the Krusty Krab from SpongeBob if we had to manually track all of the transactions by pen and paper well that would be a lot of extra work if we had a database we could keep track of these records electronically and save ourselves a lot of trouble right there's two types of databases I'll discuss relational and non-relational a table in a relational database it resembles an Excel spreadsheet there's rows and columns tables within a relational database can form well relationships with one another and that is done by this concept of keys which I'll explain in a further topic then there's non-relational databases that's where our data is organized in any format but a table this could include Json files key value pairs graph data structures entities of that nature to utilize data in a relational database we would use SQL then with a non-relational database we would use a different language named nosql meaning not only SQL but since this is an SQL series we will be working with SQL and relational databases not non-relational databases to write SQL statements we would need the help of a special piece of software known as a database management system people shorten this to Simply dbms it's a workspace for us to write SQL statements and generally just work with our database it'll make our lives easier there are different dbms systems you can use one of which is MySQL Microsoft SQL Server Oracle and postgres SQL but there's still many more out there each of these database Management Systems all use SQL but there's subtle nuances between these syntax of each database management system if you're familiar with one database management system transitioning to another will take little to no effort at all in this series we will be working with the mySQL database management system and I'll show you how to download that for both Windows and Mac OS hey everybody in this topic I'm going to explain how we can download MySQL using the Windows operating system first of all head to this website mysql.com then go to the downloads tab scroll down look for MySQL Community downloads we'll click on that click on MySQL installer for Windows make sure that we have our Windows operating system selected then download the first installer no thanks just start my download we will open this download once it's complete there are various setup types depending on what packages you need for this series all we need is the server and the workbench we'll select the custom radio button click next we will need the most recent MySQL server add that to products to be installed open applications go to mySQL workbench we will add the most recent workbench we can close out of that there is a shell if you're interested in using that but I will be sticking with the workbench in this series once we have our server and our workbench I will click next then execute looks like there's an error downloading the workbench I'm going to try again and it worked this time for some reason let's click next execute give it some time once the installation status for the server and the workbench is complete we can click on next next I'll keep these default configurations next use strong password encryption for authentication let's click next here we're going to set the root password to access the server think of some password you would like I'm just going to set mine to be password I'll keep it simple yeah of course the password strength is weak you can add user accounts but that'll be outside the scope of this series click next you can start the MySQL server at system startup if you would like I'll keep that on click next then execute then finish then next yeah we might as well start the MySQL workbench after setup if this window doesn't pop up you can easily just search for it just look for MySQL workbench now we should have a local instance we can click on this to access our server let's pretend that this wasn't here I'm going to right click delete connection if you need to set up a connection hit this plus button we'll need a connection name I'll name this Local Host connection method should be standard TCP IP the host name is 127.0.0.1 at Port 3306 then press ok so now that we have our connection set up we can click on it type in the password you set for the server mine was simply password you could save the password if you want I might as well then okay and here we are within the MySQL workbench all right everybody in this topic I'm going to explain how we can download MySQL using the Mac operating system first head to this URL mysql.com we will go to the downloads tab scroll down look for MySQL Community downloads we'll need both the server and the workbench but let's start with the server click on this file Mac OS 12 DMG archive click the download button click on this link no thanks just start my download when this DMG archive is finished downloading we can double click on it just give it a second double click on the DMG archive allow click continue you can read the license agreement I'm going to pretend I did hit continue install type in your computer's password if this prompt comes up use strong password encryption hit next then we'll need a password for our server type in whatever password you would like to keep it simple for this lesson I'm just going to set my password to be well password by checking this box MySQL server will start once the installation is complete you might as well keep this checked but for demonstration purposes I'm going to show you how to start the server manually then close I'll go ahead and move the installer to the trash bin we have the server downloaded next we need to download the workbench again head to mysql.com go to downloads scroll down to mySQL Community downloads click on MySQL workbench then hit the blue download button no thanks just start my download then we can double click on this DMG archive when it's finished downloading so double click drag and drop the workbench icon into your applications folder before we access the workbench let's be sure that the MySQL server is running click on the top left Apple logo go to system preferences at the bottom left corner look for MySQL click on it then start MySQL server if it's not started already and you can check this checkbox so that your computer starts up with it running all right you may need to type in your password the server is now running to run the workbench we can go to finder then applications look for the MySQL workbench icon double click on it and here we are within the MySQL workbench to access the server you can click on this local instance connection then type in the password that you originally set for the server but if you're missing this connection you can hit the plus button then create a new connection name I'll name this Local Host use standard tcpip for the connection method hostname should be 127.0.0.1 and Port 3306 then press ok but I already have my connection set up click on your connection type in the password you set for the server then press OK and here we are within MySQL workbench hey everybody in this topic I'm going to show you how we can create alter and drop a database what is a database exactly think of it as a folder it acts as a container tables on the other hand would be the files found within the folder in this topic I'm going to show you how we can create the database itself but we won't be working with any tables until the next topic we'll need to begin by heading to our query window which is this large window right here we're going to write a statement to create a database we will type create there is no case sensitivity in MySQL these keywords such as create they can be all uppercase just the first letter could be capital or you could do all lowercase with the keywords I like to make them all uppercase but that's just me create database and then we'll need a unique database name what about my DB then it's important with all SQL statements to end each statement with a semicolon it's like the period at the end of a sentence that's how we know that the statement is complete so you'll need these three words create database then a database name we just named our database mydb hit this lightning bolt button to execute this statement and in my output window unfortunately I can't increase the font size but I can zoom in this action was successful we have created database mydb if you head to your schemas tab then hit the refresh button you should see a database in here named mydb there's also a database for sys sys is the internal database that MySQL uses we'll want to be sure that we're using the database that we just created to use a database we can either right click on the database then click on set as default schema or you could use a statement use then the name of the database my DB semicolon execute the statement we are now using my database to drop a database type drop database then the name of the database and our database is gone unfortunately we do need a database for this series so let's go ahead and recreate our database create database mydb refresh then just to be sure we're using this database we can either use that keyword use or you can right click on the database and set as default schema all right so we can create use and drop a database how about alter there's two features for beginners I'll mention setting a database to read only the other is enabling encryption let's set our database to be read only type alter database the name of the database read only equals one this statement would make our database read only if a database is in read-only mode we can't make any modifications to it but we can still access the data Within let's attempt to drop our database drop database my DB then we have a red X right here that means we could not complete this action drop database mydb schema my DB is in read-only mode to disable read-only mode you would set read only equal to zero now we would be able to drop this database alright everybody that is how to create use drop and alter a database think of a database as just a folder a folder can hold files the files will be the tables that we'll create which will store within the database and in the next topic I'll show you how we can create some tables hey welcome back everybody in this video I'm going to show you how we can make some tables in MySQL a table in a relational database they consist of rows and columns kind of like an Excel spreadsheet in this topic we're going to create the table and the columns but we'll be populating the rows in the next video to create a table you'll type create table than the name of the table I'll create a table named employees then add a set of parentheses semicolon at the end within the set of parentheses we will list the columns for employees let's have an employee ID comma each column is separated with a comma then a first name last name how about hourly pay that'd be good higher date which date were they hired there's one thing that's missing after each column we need to set the data type of what we're storing within each column exactly is the data going to be whole integers decimals text a timestamp those are data types an employee ID that could be a whole integer the data type will be int add int after the column name a first name that's a series of characters the data type for some text would be VAR Char then within parentheses the maximum amount of characters I think 50 characters is enough for a first name last name varchar 50 is good hourly pay maybe our hourly pay is in dollars and cents we'll need a decimal portion there is a data type that is decimal add a set of parentheses we'll add the maximum amount of digits for our decimal I think 5 should be good then a Precision two for two decimal places the reason that I set the maximum digits of my hourly pay to be five is that I don't anticipate anybody's hourly pay being over 999.99 per hour that's pretty absurd I think five is enough then we have higher date there is a data type which is date if you need to work with dates otherwise there's date time if you need to include the time just the date is fine let's execute the script we have created our table employees if I were to refresh my schemas window underneath tables we have our employees table now I'll show you how we can select a table if you need to select your table you can type select asterisk from the name of the table employees let's take a look here's our table so far there's no rows but there are columns we have employee ID first name last name hourly pay and hire date if you need to rename a table you could type rename table the original name employees two whatever the new name is perhaps workers let's try that rename table employees to workers was successful if I refresh my schemas window the table employees is now known as workers but I think that's stupid let's go back and change it rename table workers to employees to drop a table I discussed this earlier you would type drop table the name of the table I don't want to drop this so I'm not going to execute the statement but that's how you would drop a table drop table the name of the table if you need to alter a table there is the alter keyword let's add a phone number alter table employees I'm going to write this next part of the statement on a new line what do we want to add uh what about a phone number add phone number then we'll need to list the data type of this column varchar is good Max size of 15 characters then we will end this statement execute the script all right we have altered our table let's take a look at our table select asterisk asterisk means all I don't know if I explained that select all from employees we have employee ID first name last name hourly pay higher date then our column phone number let's rename phone number and change it to something else we will again use the altar keyword alter table employees rename column phone number to let's say email okay then let's take a look again select all from employees yeah employee ID first name last name hourly pay higher date then email however the data type has not changed for email I'm going to refresh my schemas then underneath information underneath employees our email column has a max size of 15 characters let's change that alter table employees modify column email then the new data type varchar maybe a hundred characters for an email let's execute this statement refresh our schemas yeah now our email column has a size of 100 characters maybe we need to change the position of our email column if you need to move columns around this is how you can do so let's move our email column so it's after our last name column to do that we would type alter table the name of the table modify the name of the column to be moved email in this case then the data type this email column has a data type of varchar100 then after then the name of the column we would like our email column to follow I need my email column to come after my last name column I will type after last name then afterwards I might as well just display everything select all from employees you can write more than one SQL statement here we are employee ID first name last name email hourly pay higher date if you're modifying a column and need it to be first you could just say first email employee ID first name last name hourly pay higher date you can use the after keyword then a column or first if you need the column to be first now to drop a column you would type alter table employees drop column the name of the column to be dropped email our email column is no longer there all right everybody that's how we can create a table and work with the columns in the next video we're either going to talk about data types or learn about inserting rows into our tables I haven't decided yet but we'll probably cover one of those two hey everybody in this topic I'm going to show you how we can insert rows into a table we have a table named employees I will select everything from my table employees here are the columns we have an employee ID first name last name hourly pay and higher date to insert a row into a table we would type insert into the name of the table followed by values parentheses semicolon between this set of parentheses we will add all of the data for a row we will follow this order beginning with employee ID first name last name so on and so forth but we do have to pay attention to the data types too each piece of data will be separated with a comma let's begin with an employee ID the first employee will be Mr Krabs he's the first employee I'll give him an employee ID of one then we'll need a first name I'll put that within quotes because the data type of our first name is varchar basically speaking that's text Mr crab's first name is Eugene according to SpongeBob lore then we have a last name last name crabs hourly pay this is a decimal Mr Krabs hourly pay will be 25.50 I'm just making up numbers here higher date here's the format for a date with anesthetic quotes we will list the year followed by the month then the day I'll set Mr Krabs hired date to be January 2nd 2023 now we can execute these statements yeah here we go here's the first row employee ID one first name Eugene last name crabs hourly pay 2550 higher date January 2nd 2023 it can be somewhat tedious to manually insert each row one statement at a time let's insert multiple rows at once to do that after my set of parentheses I will add another set of parentheses each separated with a comma depending on how many rows I would like to enter so I have four employees I would like to enter I will add four sets of parentheses each separated with the combo and let me just do some formatting to make this look pretty each set of parentheses corresponds to a new row so let's enter all the data that we'll need for this table employee ID number two will be Squidward tentacles Squidward's hourly pay will be maybe a solid fifteen dollars Squidward's higher date the year 2023 January I think January 3rd is good all right so that is the next row let's work on employee number three employee number three will be a SpongeBob last name will be Square Pants SpongeBob's hourly pay will be 12.50 SpongeBob's higher date is the year 2023 January 4th employee for will be Patrick star Patrick's hourly pay will be 12.50 as well higher day 2023 January 5th okay last employee employee ID number five first name Sandy last name cheeks Sandy's hourly pay will be 17.25 she'll be an assistant manager Sandy's start date will be the year 2023 January 6th we can execute this statement to insert multiple rows all at once yep here's our table we have five rows each corresponding to a different employee we have Mr Krabs Squidward SpongeBob Patrick and Sandy I'm going to demonstrate what happens when we attempt to insert a row with missing data I will insert employee number six their first name is Sheldon last name Plankton but we will not insert an hourly rate of pay or a higher date here's what will happen so we have an error column count doesn't match the value at count at Row one we're missing data for our hourly pay and higher date you could insert just select columns that is done by adding after the name of the table add a set of parentheses then name the columns you would like to insert data into all we're adding is an employee ID first name then last name okay now we've inserted a new employee but there is data missing and that's okay we plan on hiring Mr Plankton but we have not yet negotiated an hourly pay or start date we still need to run a background check on him but we want to add him to the system at least so if you ever need to insert data into a row but you need to Omit certain columns you can simply just list the name of the columns you would like to sort data into all right everybody that is how to insert rows into a table in MySQL hey everybody in this video I'm going to show you how we can select data from a table to query all of the data from the table we would type select asterisk meaning all from the name of the table in my case we have table employees this statement select all from employees will give me all columns and all rows but sometimes you may not want all of the data here's a scenario your boss gives you a task of retrieving the full name of every employee well we don't need employee ID hourly pay or higher date for that right we just need a list of the first and last names of our employees table in place of the asterisk meaning all we can select specific columns such as first name and last name we have all of the first and last names from our employees table you can change up the order of the columns too this time I would like last name then first name with our query this returned all of the last and first names of all the employees point being you can select specific columns depending on what you're looking for or everything with the asterisk there is a clause known as the where Clause if we're looking for something specific let's add where then what are we looking for exactly we can write some criteria let's select all from employees where employee ID equals one then end your statement with a semicolon this will give us a specific employee the employee that has an ID of one if I set employee ID to B2 that would give us Squidward three would be SpongeBob 4. Patrick I think you get the idea here's an exercise let's find all the data from employees where the first name equals SpongeBob where first name equals Sponge Bob this query will return employee ID3 the first name last name hourly pay and higher date here's another example let's find all employees that have an hourly pay greater than or equal to 15. our criteria will be where hourly pay is greater than we'll use the greater than operator or equal to 15. this returns three employees Mr Krabs Squidward and Sandy all three of them have an hourly pay of 15 per hour or greater it is possible your query can return multiple results let's find anybody that has a higher date less than or equal to perhaps the year 2023 January 3rd this query returns two results Eugene Krabs and Squidward Tentacles they both match this criteria and in this case we use the less than or equals to operator another operator you should know about is the not comparison operator it's an exclamation point and an equal sign we will check if something is not equal let's find where employee ID does not equal one who are all the employees that have an ID not equal to one that gives a Squidward SpongeBob Patrick Sandy and Plankton on the other hand if I set this to one that would give us Mr Krabs that is the not comparison operator if you need to check if something is not equal in this next example let's pretend that we're human resources we need to find any employees that do not have a higher date and then begin the paperwork for them currently Plankton's hourly pay and higher date are both set to null null meaning no value in place of saying you know higher date equals null in place of using the equal sign we would type is select all from employees where higher date is null that would return Sheldon Plankton because his higher date is null not equals null that doesn't work it would be is null you could even say is not null that would return every employee that does have a higher date all right everybody so that is how to query data from a table select whatever you're looking for it can be everything or specific columns from a table of your choosing where the results equal some criteria that you set it really depends on what you're looking for and that is how to query data from a table hey everybody in this video I'm going to show you how we can update and delete data from a table in my example we have a table of employees however Sheldon Plankton is missing some information and hourly pay and a higher date let's update those fields to update some data in a table we would use the update keyword the name of the table employees in my example then set which column would we like to interact with first let's interact with hourly pay set hourly pay let's pay Plankton 10 and 25 cents per hour we should probably add a where Clause I need to specifically select Plankton where let's select his employee ID where employee ID equals six then semicolon 10 the statement you could also select Plankton by his first name or last name as well when I execute the script Plankton's hourly pay is now 10.25 cents per hour to update multiple columns you can change more than one field at once after your first change you can add a comma then change another field let's change Plankton's higher date to be the following higher date equals 10 23-0 Dash zero seven I'll make his hourly pay 10.50 I'm feeling generous here we are Plankton's hourly pay is now ten dollars fifty cents his higher date is January 7th 2023. to set a field to null meaning no value you would just say equals null for example let's take Plankton's higher date set the higher date equal to null Plankton's higher date is now null so maybe we're gonna fire him or something in this next example I'll give you a demonstration but you don't want to follow along to update all of the rows within a column you would exclude the where clause if I were to set hourly pay equal to 10.25 that would affect all of the rows after executing this statement the hourly pay for everybody is now 10.25. that's an example of how you could set a column to be one consistent value to delete a row from a table you would type delete from the name of the table now don't write just the statement it will delete all rows in your table here's an example do not do this I will delete from employees there my whole table is gone do not do this be sure to add a where Clause when deleting from a table otherwise this will delete all your rows where employee ID equals six yep and Plankton is no longer there all right everybody that is a super quick video on how to update and delete data from a table hey everybody in this topic I'm going to explain Auto commit commit and rollback Auto commit is a mode by default Auto commit is set to on whenever you execute a transaction within MySQL that transaction is saved what if we were to make a transaction and we need to undo that transaction for example what if we accidentally delete all of the rows of this table don't do what I do but I'll give you a demonstration I accidentally type in delete from employees but I forget to add a where Clause well all my rows are now gone how do I undo these changes well what we're going to do is set auto commit to equal off with this setting set to off our transactions will not save automatically we would need to manually save each transaction it creates a save point so to say I'm going to create a save point by typing commit then execute now I'm going to select my table select all from the name of my table then I'm going to go ahead and delete all the rows delete from employees oh shoot but I forgot to add a where Clause my whole table is gone now luckily we created a safe point with that commit statement to undo these changes I can execute the roll back statement this will restore my current transaction back to the previous save point where we used commit now if I were to select all from the name of my table our table is back to what it previously was and again to save any changes let's say I delete my whole table again delete from employees if I want to save this change I would commit then again I will select my table and that last change is now saved my whole table is gone so yeah that is auto commit commit and roll back by default Auto commit is set to be on any transactions after executing are saved if that mode is set to off you can commit any changes manually to undo any changes use that rollback statement so yeah that is auto commit commit and roll back in MySQL hey everybody in this topic I have a super quick video on getting the current date and time in MySQL let's create a temporary table at the end of this topic we'll delete it create table I'll name this table test we will have three columns the current date I'll name the column my date the data type is date my time the data type is time then we'll combine both my date time the data type is date time let's create this table it was created successfully then I will select all from our table test we have three columns a date a time and a date time which combines both so how do we get the current date maybe we need to create a timestamp of when some event happened maybe a higher date for our employees so I'm going to insert into the name of my table test some values for the current date there is a built-in function current underscore date then add a set of parentheses this function will return the current date for the current time there is a current time function add a set of parentheses to the end of that function for the date time you could just say now the net a set of parentheses after inserting this row let's select all from our table test here's the current date of me filming this video October 21st 2022 the time is seven in the morning the current date time is October 21st 2022 7 in the morning a date time would probably be good for some sort of transaction you need to record for the time being I'm going to set the current time to be null as well as the date time to be null now if you were to take your current date then add plus one this part of my statement would refer to tomorrow October 22nd if you were to take your current date minus one that would technically be yesterday October 20th you could do something similar with your time and date time you can add or subtract seconds but I think that's pretty self-explanatory we don't need this test table anymore so we can delete it drop table test and it's gone all right everybody so that was a super quick topic but I thought I needed to cover it that is how to get the current date time and date time in MySQL hey everybody I have a quick video on the unique constraint the unique constraint ensures that all values in a column are all different that's basically it we can add this constraint when we create a table or after let's create a new table create table let's create a table of products what products do we offer we'll list the columns let's add a product ID the data type will be int product name the data type will be varchar maybe 25 characters then a price the data type will be decimal our price will have a Max size of four digits and a Precision of two decimal places to add the unique constraint select a column after the data type add the keyword unique and that's all you have to do with this keyword unique we can't insert any product names that are the same they all have to be well unique let's create this table let's say that you forget the unique keyword then you create the table what you could do instead is type alter table the name of the table products which we just created add constraint unique the name of the column within parentheses product name if you forget to add a unique constraint to a column after you already create the table this is what you can write however when I run this I do have a warning I already have a unique constraint on that column but yeah that's how you would add a unique constraint to a column in a table you have already created let's select our table select all from products we have a product ID column product name and a price let's insert all of our values insert into the name of our table which is products values will insert maybe four rows we'll need a product ID name then a price for a product name we have a hamburger and the product ID I'm just making up a number is 100. the name is hamburger the price will be 3.99 fries will be our next item then we have prize the price will be 189. 102 will be soda the price is one dollar even 103 will be ice cream the price will be 149. Let's test the unique constraint that we have added to the product name column suppose that I forget that I have fries on the menu already and I will add another row 104 frize 189 actually let me change the price to maybe something different like 175. here's what happens when I attempt to add two values that are the same duplicate entry fries for Key Products dot product name MySQL is telling us that we have a duplicate under the product name column we have fries twice since we added the unique constraint all values in this column must be different if I were to remove the last column where we have that duplicate well then this would run just fine now let's select everything from products yeah there we go here are the products we have a hamburger fries soda and ice cream all on the menu so yeah that's the unique constraint you can add that constraint when you create a table or after whatever column has that unique constraint all the values need to be different so yeah that's the unique constraint in MySQL hey everybody in this topic I'm going to explain the not null constraint the notch null constraint can be added to a column when you create a table whenever we enter a new row the value within that column can't be null for example I have table products we have a hamburger fries soda ice cream if I were to recreate this table I would type something like this create table products I would list the columns we have product ID the data type is integer product name the data type is varchar I believe I set this to be 25 last time then price that is a decimal we gave this column a Max size of four digits and a Precision of two any column that I do not want to have null values I will simply just add not no this is a constraint now I've already created this table so I'm not going to execute the statement but that's how you would create a table that has the notch null constraint after the column name you just type not null to add the not null constraint to a table that already exists you would instead type alter table the name of the table modify the name of the column the data type of the column in this case decimal four comma two then not null it's a little bit different than the unique constraint in the last topic so let's execute the statement that seemed to work let's add a new item to the menu I will select everything from my products table select from products let's add a new item to the menu and we will test that not null constraint insert into products values we have 104 product ID 104 will be how about a cookie I'm not going to set a price I will say null for the price maybe I'm not decided on what the price is yet after executing the statement well we have an error column price cannot be null since we said that not null constraint on the product price we can't enter a null value we could set this to be zero that's acceptable but it can't be null so yeah everybody that's the not null constraint it's a useful constraint to verify input if there's any column that you don't want to have any null values just add that constraint and yeah that is the not null constraint in MySQL hey everybody in this topic I'm going to explain the check constraint the check constraint is used to limit what values can be placed in a column for example I live in the United States depending on which state you live in there is a minimum hourly wage that employers have to pay in this example let's set an hourly pay to our employees table every employee needs to be paid at least the minimum wage in that region and we can do that with the check constraint we have our table of employees if I were to recreate this table it would look like this I think that was pretty close to add a check to a column at the end of our table this is what we would write check then within parentheses what sort of condition do we want let's check to see if our hourly pay column is greater than or equal to maybe ten dollars per hour that will be the minimum wage in this region this would work if we were to create this table however it's typically a good idea to give your check a name just in case you need to drop it later you can easily identify it so precede check with constraint then a constraint name what about chk meaning check underscore hourly pay this check will be known as check hourly pay so we can identify it then we can drop it or make any changes to it if we need to I do already have an employees table so I'm not going to execute this statement but to add a check constraint to a table you would just add a row like this to the bottom if you instead need to add a check constraint to a table that already exists this would be the syntax alter table the name of the table add constraint then we need a unique name for this constraint check hourly pay that's fine then the check within parentheses hourly pay is greater than or equal to 10. ten dollars per hour okay let's run this yeah that appears to have worked I'm going to select all from employees let's take a look at our table then we will attempt to add a new employee insert into employees values we need an employee ID first name last name hourly pay then a higher date employee ID will be six first name Sheldon plankton for hourly pay let's attempt to pay Plankton maybe five dollars per hour then a higher date [Music] 2023-01-07 let's see if that check constraint kicks in we're paying Plankton under ten dollars per hour yeah it doesn't appear that we can do so check constraint check hourly pay is violated now let's pay Plankton ten dollars per hour yeah that worked just fine if you need to delete a check you would type alter table the name of the table drop check then the name that you gave that check in my case it was check hourly pay and that check has been dropped yeah everybody that is the check constraint it's used to limit what values can be placed in a column it's another useful method for checking input and that is the check constraint in MySQL hey everybody in this topic I'm going to explain the default constraint when inserting a new row if we do not specify a value for a column by default we can add some value that we set here's an example let's select all from our products table select all from products in an earlier example we have a table of products a few items on the menu for sale are hamburgers fries soda and ice cream let's add a few items on the menu that would be free this could include napkins straws forks and spoons something you would commonly see at a fast food restaurant if we're not using the default constraint we would have to enter in those prices manually so let's insert into our table products our values product ID 104 will be a straw the price will be zero dollars and zero cents let's do this all together we have 105. 106 107. product number 105 will be a napkin the napkin is zero dollar zero cents 106 is a plastic fork again zero dollars and zero cents 107 is a plastic spoon the price is zero dollars and zero cents then I will select all from my products table here's our new products table we have our four items that actually cost something then our free items which are included with whatever we buy in place of explicitly stating a price one way in which we can make our lives easier if we're adding a bunch of free items to the menu we could set a default constraint where if we don't explicitly set a price the price will default to be zero it will be free so I'm going to undo everything that we just did delete from products where product ID is greater than or equal to 104. and then we can execute that statement if we were recreating this table with the default constraint we would type create table the name of the table products in this case add our columns we have a product ID the data type is integer we have a product name the data type is VAR chart and I forgot what the size was looks like 25. than a price which is a decimal Max digit size of four Precision of two now let's use the default constraint after the column you would like to add that constraint to type default then some value I will set the default value to be zero dollars and zero cents or you could just say zero too that'd be fine you would just add this constraint when creating a new table however I do already have a products table so this technically wouldn't work because that table already exists now to alter a table to include that constraint you would instead type alter table the name of the table alter the name of the column price set default constraint then the value so zero and that appears to have worked let's select our products table select all from products then we will insert some new rows insert into products than our values so we had four rows 104. that was a straw 105 was a napkin 106. was a fork 107. is a spoon then finished the statement with a semicolon so since we set that default constraint we don't need to explicitly list the price but we are missing one thing though when I execute this statement the column count doesn't match so when we insert into products we will list what we're inserting explicitly the product ID and the product name after our table name within parentheses type product ID then product name now this should work yeah and here's our products table with our four inserts all of the prices were set to zero and we did not need to explicitly state that this time if you don't specify a value you'll use whatever the default is here's another example I think you'll like this one let's say we have a table of transactions after inserting a new transaction we will insert a timestamp of when that transaction took place and that time stamp will be the default create table transactions at the end of this topic we will delete this table so don't get too attached to it we will have three columns transaction ID which will be of the data type integer a transaction amount let's just say amount the data type will be decimal five digits Precision of two is good then a transaction date the data type will be date actually let's make this date time I'd like to include the time of when this transaction happened now for the default constraint after the date time let's add default then the Now function we don't need to explicitly add the date and time that will be done automatically which is kind of cool so I'm going to create this table let's select all from transactions if we're going to insert some values we'll need at least a transaction ID and amount the transaction date will be included automatically so let's insert into transactions some values let's do one at a time the first transaction of the day will have a transaction ID of one I suppose a customer came in and bought a hamburger and a soda for a total of 4.99 then we do need to list the transaction ID then the amount after our table name add transaction ID then amount so let's run this yeah cool so there's our transaction ID this was order number one the total amount was 4.99 and we do have a time stamp of when this transaction occurred so the second order of the day will be for a total of 2.89 and that happened at this time then the third order of the day will have a total price of eight dollars 37 cents yep there we are so we don't need this table anymore I'm going to drop table transactions we will recreate this table in the future but I'd rather start fresh all right everybody that is the default constraint when you create a table or alter a table you can set a default value of some value you specify then whenever you insert a row that value will be included automatically which is pretty useful but yeah that is the default constraint in MySQL hey everybody in this topic I'm going to explain the primary key constraint in MySQL the primary key constraint can be applied to a column where each value in that column must both be unique and not null it's typically used as a unique identifier for example I live in the United States each citizen within the United States has a unique social security number there is a strong possibility that two citizens in the United States share the same first name and last name John Smith for example if we're trying to find John Smith well which one are we referring to we could instead search for a citizen with a unique social security number then we know for sure we have the right person that's kind of the same concept with the primary key also a table can only have one primary key constraint here's an example let's create a temporary table of transactions create table transactions there will only be two columns a transaction ID the data type is int I will set the transaction ID to be the primary key there can be no duplicate transaction IDs with the same value and none of them can be null then we will also have an amount how much was each transaction for the data type will be decimal Max size of five digits Precision of two and that is good enough then I will select all from our table transactions here's our table now to add a primary key constraint to a table that already exists this is the syntax alter table the name of your table add constraint the name of the constraint primary key within parentheses the column we would like to apply the primary key to transaction ID in this example then semicolon however we have already applied the primary key constraint to our transaction ID column so this isn't going to work but that's how you would add a primary key constraint to a table that already exists Let's test that theory of that limit of one primary key per table I'll attempt to add a primary key to our amount column and it does not appear we can do so multiple a primary key to find so like I said you can only have one primary key per table and that is typically used as the identifier let's take a look at our transactions let's populate this table with a few rows we will insert into our table transactions some values all we need is a transaction ID and an amount for our transaction ID I'll just make up a number all transactions will start in the thousands this will be our first transaction so the amount a customer bought a hamburger and a soda the total was 4.99 so let's run this there is our first row for the second transaction the transaction ID will be 1001 the customer bought fries and a soda for 289. so that appears to have worked as well now the next customer buys fries and ice cream for 3.38 cents Let's test that theory of each transaction ID having to be unique I'll attempt to insert this row with the same transaction ID as the previous row then let's see what happens duplicate entry 1001 for key transactions.primary yeah it appears that we cannot have two rows with the same primary key they each need to be unique let's change the transaction ID to 1002. that appears to have worked now the next transaction will be a hamburger and soda for 4.49 I will attempt to set the transaction ID to be null which we can't do either column transaction ID cannot be null each value that's set as a primary key can't be null and it needs to be unique so this transaction will have a transaction ID of 1003 two rows can share similar data we have two customers that bought both a hamburger and a soda for the same price but each transaction is uniquely identified by the transaction ID which we set as the primary key let's say that somebody comes in to request a refund and they give us a transaction ID well we can easily look that up and refund them whatever the amount was select amount from transactions where transaction ID equals 1003. so we are going to refund to the customer 4.99 well all right then everybody that is the primary key constraint it's most commonly used as a unique identifier you usually want a primary key in every table you make each value within a column that is set as the primary key cannot be no and it needs to be unique there can only be one primary key per table typically it's used as a unique identifier for each row and yeah everybody that is the primary key constraint in MySQL hey everybody in today's topic I'm going to explain the auto increment attribute in MySQL the auto increment attribute can be applied to a column that is set as a key whenever we insert a new row our primary key can be populated automatically then each subsequent row is auto incremented I'm going to recreate this table transactions but first I need to drop it drop table transactions then we will recreate this table but apply that auto increment feature create table transactions there were two columns a transaction ID the data type was int and an amount the data type was decimal five digits Precision of two the auto increment feature can only be applied to a column that is set as a key our transaction ID will set to be the primary key to apply the auto increment feature add the keyword Auto increment Now by default the primary key is going to be set to 1. so after creating this table I will select all from transactions okay we have successfully created our table then I will insert some values insert into transactions R values so we don't need to explicitly insert a transaction ID that'll be done for us automatically but we still need an amount I will insert 4.99 for the first amount since we're not inserting all of the values into a row explicitly we would need to specify what column this value is for that is for the amount okay let us execute this and take a look yeah so the transaction ID was set to one even though we did not manually insert that value let's insert the next amount so we have 289 yep transaction ID 2 is 289 3 38 transaction ID3 4.99 transaction ID of four we could set our primary key to begin at a different value to do that we would type alter table the name of the table Auto increment let's begin at 1000 instead of one then I'm going to go ahead and drop all the rows we'll start fresh delete from transactions select all from transactions okay then let's insert those rows again and see what happens insert into transactions R values 4.99 was the first amount this value is for our amount column now this transaction ID it begins at one thousand then it should Auto increment the next value was 289 our next transaction ID is 1001 338 1002 then 4.99 1003 all right everybody that is the auto increment feature it's a keyword that can be applied to a column that is set as a key whenever we insert a new row our key will be incremented by one for each row that we insert but yeah that is auto increment in MySQL hey everybody so in this topic I'm going to explain what the foreign key constraint is a foreign key think of it as a primary key from one table that can be found within a different table using a foreign key we can establish a link between two tables there's two primary benefits to this in my transactions table if I were to take a look at the customer ID of who initiated this transaction I could refer to the customers table then find the first and last name of that customer we can do that when we reach the topic of joins but in this video we're going to focus more or less on just creating foreign Keys another benefit when you create a foreign key constraint that would create a link between two tables which prevents any actions that would destroy that link between them so here's an example I have three tables employees products and transactions I'm going to create a new table of customers I'm going to create this table and speed it up feel free to pause the video if you need to here's my table we have a customer ID which is the primary key a first name and a last name let's populate our customers table we have three customers Fred fish Larry Lobster bubble bass each has a unique customer ID we're going to create a link between our customers table and our transactions table via our customer ID I'm going to drop our table transactions drop table transactions we will recreate this table but apply that foreign key constraint so again I'll speed up the footage now what I'm going to do is create a third column to hold our customer ID and the data type isn't I would like to add a foreign key constraint to our customer ID column on the next line I will type foreign key list the column we're applying this foreign key constraint too within parentheses customer ID references our second table the customers table customers then the column of the primary key which was customer ID and that's all you need to do then I will select all from my table transactions here is our table we have a transaction ID this is the primary key the amount of the transaction then the customer ID of who initiated that transaction our customer ID column is the foreign key it points to the customers table based on what the customer ideas we can find the first and last name of that customer to find any active foreign keys go to your table in this case transactions underneath foreign keys this section will display any foreign keys that are applied to this table we currently have one transactions underscore ibfk underscore one if you need to drop a foreign key you would type alter table the name of the table that has the foreign key constraint that would be transactions drop foreign key then the name of this foreign key transactions underscore ibfk underscore one and that foreign key should be gone you could also give your foreign key a unique name alter table the name of the table add constraint then some unique name let's rename that constraint as FK underscore customer ID foreign key the name of the column customer ID references the name of the second table customers then the column that has the primary key customer ID so to apply a foreign key to a table that already exists this would be the syntax you don't necessarily need to name the constraint but if you would like to just add that line add constraint then some unique name let's add this foreign key constraint to a table that already exists it worked if I were to refresh my navigator window we now have a foreign key and it's the one that we gave a name to foreign key customer ID what we're going to do is drop the rows from our transactions table then reinsert them but we will add a value for each foreign key so let's get rid of this delete from transactions then select all from transactions here we are okay so let's insert some new rows I do want to set auto increment to be a thousand so I'm going to do that before we insert some rows alter table transactions Auto increment equals one thousand so let's take a look at transactions then we will insert some new rows insert into transactions R values we had four transactions we will need an amount and the customer ID of who initiated the transaction the first order is for 4.99 customer number three initiated this transaction the second order is 289 customer number two initiated that transaction 338 this is also customer three they returned back the same day 4.99 customer number one initiated that transaction we have our primary key from the transactions table as well as our foreign key each of these customer IDs references the customer ID column from the customers table we won't get to explore that until we reach the topic on joins so the other benefit of a foreign key constraint is that we now have a link between our transactions table and to the customers table MySQL will prevent any actions that would destroy that link between them unless we explicitly drop that foreign key constraint I'm going to delete some of our customers delete from customers where customer ID equals three we were right it doesn't appear that we can do so cannot delete or update a parent row a foreign key constraint fails so yeah that's the foreign key constraint it's a primary key from one table that's found also within a different table but when we're working with that different table we would refer to that key as a foreign key we'll have more practice with this when we reach the topic on joins but yeah that is the foreign key constraint in MySQL hey everybody welcome back again in today's video I'm going to be explaining joins in MySQL a join is a clause that is used to combine rows from two or more tables based on a related column between them such as a foreign key here's an example I have two tables a table of transactions and a table of customers think of these two tables as a Venn diagram transactions will be the table on the left customers will be the table on the right whatever data they have in common is the middle part of our Venn diagram for my demonstration to make more sense I will need to add a few extra rows feel free to pause the video if you need to catch up I will insert into transactions a new row the amount is one dollar the customer ID is null so not all transactions can have a customer ID that foreign key here's a scenario suppose that somebody comes in pays for a soda with cash well we wouldn't have a customer ID right if a customer instead paid with a credit card we could track who that customer was there may be a customer ID who initiated that credit card charge I'm going to insert this row and here's our new transactions table not all rows have a customer ID then let's add one more customer insert into customers first name last name will be poppy Puff let's select all of our customers now not all transactions have a customer ID and if not all customers have ever initiated a transaction they could be registered as a customer but they have never actually bought anything yet using joins let's take a look at what data these two tables have in common we'll discuss inner joins left joins and right joins let's begin with an inner join to create an inner join between these two tables you would type select all from which table would you like to be on the left think of that Venn diagram our transactions table will be on the left from transactions inner join whatever table you would like to be on the right in this case customers on we're going to join these two tables together by the foreign key from transactions we'll take transactions.the name of the foreign key column which was customer ID equals the table on the right dot the name of the primary key column which was customer ID then we will execute the statement and here's our new table we have created an inner join from these two tables based solely on what they have in common if you remember we don't have that Row for that transaction for one dollar that doesn't have a customer ID as well as our customer poppy puff there's no role for her in this table what we're telling MySQL is to select all rows from these two tables that have matching customer ideas so that's why some data was excluded one thing you could do with a join this applies to left joins and right joins as well you don't necessarily need to display every single column from both tables you can select specific columns let's select our transaction ID the amount the first name then the last name this would make it a lot easier to find the first and last name of who initiated a transaction at a given time we know who bought which order so that is an inner join join together any matching rows based on some Link in this case we're joining these two tables together by their customer ID now with the left join we are going to display everything from the table on the left but let's select all with the left join we will display everything from the table on the left our transactions however if there is a matching customer ID pull in any relevant data from the table on the right even though there's no customer ID with this latest row we're still going to display it with a left join but there's no data to pull in from the right table because there's no registered customer ID with the right join we will display the entire table on the right if there's any matches we will pull in any matching rows from the left we are displaying all of our customers if any of these customers ever initiated a transaction we will include the data from those transactions we still have poppy puff in our table but she has no relevant transactions she never initiated one so yeah everybody those are joins a join is used to combine rows from two or more tables based on a related column between them such as a foreign key like customer ID and that is a quick introduction to joins in MySQL hello again everyone today I will be explaining functions in MySQL a function is a stored program that you can pass parameters into to return a value if you look on mysql's main website there is a huge comprehensive list of functions in this video I'll only be showing you a few of the more useful ones for beginners but just so you know there's a lot of functions out there that do all sorts of things what if I would like to count how many transactions took place on a certain date to do that we can use the count function select count parentheses within my set of parentheses I can place a column then calculate how many rows are within that column let's calculate amount we will count how many rows are within the column amount then we will need a table from our table transactions so you could add a where Clause 2 like where transaction date is on a certain date but I want to keep this video as simple as possible this function will return all of the rows within this column amount which is five we have five transactions within this table now this column header is kind of ugly the column name is count the amount column you could give a column an alias by adding as then some nickname let's say count it's not necessary but if you want to make the column header look pretty or rename it as something you could do that count five we could even rename this column as today is transactions that works too do whatever you want to do let's find you the maximum value of our column amount by using the max function within the set of parentheses pass in your amount column I will give this column an alias of maximum and that is the maximum amount the largest order was 4.99 there's also Min to find the minimum as minimum the smallest order was for one dollar you could do average which is AVG as average the average order is three dollars and 45 cents we can find the sum of a column by using the sum function as some the sum of all of our transactions was 17 and 25 cents for this next example we're going to concatenate the first and last name of our employees select all from employees we have two columns one for a first name the other for a last name we're going to combine these two columns together with the concat function then create a new column named full name here's how to do that select concat first name last name then I'll add an alias as ball name here's what we have so far so we are concatenating the first and last names of our employees uh however we should separate each name with the space within our concat function I'll add a space character surrounded with commas to separate each argument there that's much better so that is the concat function we can concatenate values together such as if you need a column for somebody's full name but yeah those are just a few functions there's still many more we haven't covered but these are a few of the beginner ones you may be interested in but yeah those are functions in MySQL why hello again everybody guess who it's me again today I will be explaining logical operators in MySQL I would think of them as keywords that are used to combine more than one condition for my examples to make more sense I will add one more column a job column so let's alter our table of employees alter table employees add column job the data type will be varchar25 I'll add this column after our hourly pay column then select all from my table employees here is our job column let's add some jobs to our employees let's update employees set job equal to Let's select Mr Krabs he will be a manager then be sure to add a where Clause employee ID equals one Mr Krabs is a manager Squidward will be a cashier employee ID equals two SpongeBob employee ID3 he will be a cook Patrick will also be a cook Sandy will be an assistant manager let's abbreviate the word assistant manager Plankton Plankton will be a janitor how is this related to logical operators you're wondering well using logical operators we can check more than one condition suppose that our boss wants us to find any Cooks that were hired before January 5th how can we write a query that can satisfy those two conditions here's how we will use the and logical operator select all from our table employees using a where Clause we will write two conditions find any employees where the higher date is less than the year 2023 January 5th so far this would give us three results Mr Krabs Squidward and SpongeBob from these three employees how do we find any Cooks well we can add the and logical operator and we can write some other condition job is equal to cook there we have SpongeBob he is a cook that was hired before January 5th so that's the and logical operator you will return any results that match these two criteria another logical operator is or you can check more than one condition as long as one of those conditions is true that row will be true let's find any cooks or cashiers where job is equal to cook that gives us both SpongeBob and Patrick they're both Cooks or job is equal to cashier that would also include Squidward he's not a cook but he is a cashier with the or logical operator only one condition needs to be true whereas with the andological operator both conditions must be true so that's the main difference between the and as well as or logical operators with the not logical operator not is a little different not basically reverses anything you say let's find any employees that are not a manager where not job equals manager we have all of our employees besides Mr Krabs we have cashiers Cooks assistant managers and janitors you can combine logical operators too let's find anybody that's not a manager and not an assistant manager let's add and not job equals assistant manager we have everybody besides a manager and besides an assistant manager Mr Krabs and Sandy are not within the results so that is the not logical operator it basically reverses whatever condition you write there's also between is used within a single column it's very similar to the andalogical operator except people prefer to use the between logical operator when working with the same column just because of its readability let's find any employees where the higher date is between January 4th and January 7th where higher date between two values 2023 0 1 0 4 and 2023 0 1-07 so we have SpongeBob Patrick Sandy Sheldon there's also the in logical operator we can find any values that are within a set let's find where job is in cook cashier or a janitor yep we have a cashier a cook a cook and a janitor there is more than one way to write a query where you need to match more than one condition really the best way depends on your data set basically speaking logical operators are used to check more than one condition but they're done so in different ways depending on the logical operator that you're using but yeah those are logical operators in MySQL hello again people today I'm going to be explaining Wild Card characters there are two the percent and the underscore each of these is used to substitute one or more characters in a string here's an example I have a table of employees what if I need to find any employees whose first name begins with an S well you would think you could write something like this where first name is equal to s now this technically doesn't work here what we're doing is telling MySQL to find any first names that are equal to the character s if I instead need to find any first names that begin with the character S I can type S then the percent wildcard operator the percent wildcard operator represents any number of random characters but there's one more change we need to make still replace the equals operator with the like operator like when used with an aware Clause searches any patterns we will return any first names that begin with the letter s so the results are Squidward SpongeBob Sandy Sheldon their first names all begin with the letter s we could do this with dates too let's find any higher dates in the year 2023 technically that would return everything in my example because all the higher dates are in 2023. we can even find if something ends with a character let's find any last names that end with the character r that would return Patrick star star with an r at the end you could add more than one character let's find any first names that start with SP then change last name to first name that would return one result SpongeBob SP on the other hand we have the underscore wildcard character the underscore wildcard character represents one random letter let's find any jobs that have one random character followed by oh okay that would return any Cooks the underscore kind of reminds me of that game Wheel of Fortune MySQL is going to attempt to fill in any blanks with a matching character if we had two underscores well there technically wouldn't be any matches MySQL would technically be looking for a five-letter word where the middle three characters are oh okay but there's no matches here's another example find any higher dates in January higher date is like the year is going to be random we have four digits one two three four a dash character zero one Dash then a day of the month so two underscores again that would return pretty much all of our employees they were all hired in January maybe we don't care about the year or the month but we care about the day of the month so let's replace the month with underscores zero two that would return Mr Krabs zero three that would be Squidward now you could combine these two different wild cards together too let's take a look at our jobs let's find any jobs where the second character is an a I know it's kind of a weird example but hopefully it'll make sense select all from employees where job is like now we're going to combine these two wild characters the second character has to be an a we could use the underscore for the first character a then any amount of random characters afterwards that would return a manager cashier or janitor so yeah those are wild card characters you can use these to substitute one or more characters in a string it's pretty helpful if you need to find if a string starts with some value or ends with some value and those are wild card characters and MySQL hey everyone I have a super quick video on the order by Clause the order by Clause sorts the results of a query in either ascending or descending order based on which column we list here's an example I have a list of employees how can I list all of these employees alphabetically currently they are listed by their employee ID well at the end of my statement I can add an order by Clause order buy then a column to order our Rose by let's order all of these employees by their last name Now by default they will be ordered in ascending order or alphabetical order if you're working with characters for descending order or reverse alphabetical order just add desc for descending now these employees are listed in Reverse alphabetical order or descending order for us sending order that would be ASC but that is the default so you don't explicitly need ASC let's order these employees by their first name first name these employees are listed alphabetically by their first name let's do reverse alphabetical order yeah there we are let's order these employees by their higher date higher date and ascending order you don't even technically need that keyword ascending it would still work just fine now descending I think we have the hang of it now let's select all from transactions there's one more thing I want to show you select all from transactions we have two transactions with the same amount let's order by amount when ordering a column if two values share the same amount we can add an additional column to compare by order by amount but if two amounts are the same order by maybe customer ID these two rows are now flipped so you can order by more than one column if two columns share the same amount then order by some other column then you can add ascending descending however you want to order your table so yeah that is the order by Clause you can order your rows by some column you specify in ascending or descending order but yeah that is the order by clause in MySQL hello it's me again today I will be explaining the limit Clause the limit Clause is used to limit the number of records that are quarried it's very useful if you're working with a lot of data one use is that you can display a large data set on different pages also known as pagination here's an example I have a few customers not very many I'm working with a small data set if I would ever need to limit the amount of customers that are displayed I can add a limit Clause then set how many I would like to display I would like the first customer limit one that would return Fred fish in my example if I need the first two that would be Fred then Larry three is Fred Larry bubble that's kind of a weird first name and then four would be poppy currently these records are organized by customer ID we could combine the limit Clause with the order by clause let's order by last name then limit one that would return bubble bass it's an alphabetical order or we could do last name descending limit one poppy puff has the name and least alphabetical order I guess basically speaking limit limits the number of records that are returned it's very useful in combination with the order by clause which we learned about in the previous topic now with the limit Clause you can add an offset limit one one the first number is the offset limit to one record after the first that should technically return Larry Lobster yep Larry so limit two would add an offset of two which returns bubble Bass limit three would return poppy puff using an offset would be very helpful when working with a large data set if you need to display your records on different pages suppose that our data set is maybe 100 customers I need to display 10 customers per page so the first 10 would be just limit 10. but you know my data set's really small so it's not going to be apparent if I need the next set of 10 customers I can add an offset of 10. although I don't have that many customers to begin with the next set of 10 customers would be an offset of 20 then displayed 10 you know then 30 40 so on and so forth so yeah that's the limit Clause you will limit the results that are queried type limit then some number you could add an offset so display some number of Records after an offset but yeah that is the limit clause in MySQL hey welcome back today I will be explaining the union operator the union operator combines the results of two or more select statements I have created two additional tables I'm only going to be using them temporarily I'll delete them once we're done with this example I have a table of income we have two columns an income name and an amount I've just made up some numbers they're probably not realistic I also have a table of expenses here's my table of expenses we have an expense name and some amount again I just made up some numbers suppose that our boss wants us to print a list of all of our different income versus expenses all in one window well we can easily do that with the Union operator after our first select statement delete the semicolon add Union we now have a window with the results of these two select statements we have income and expenses let's perform a similar operation with our employees and customers table I'm going to drop these tables though they were only temporary drop table income drop table expenses let's begin Mr Krabs our boss he wants us to create a list of everybody that enters the store all employees and all customers while we can join together those select statements with the Union operator but first let's take a look at each of these tables select all from employees we're going to have one problem our employees table has six columns whereas our customers table our customers table has three columns if you're combining two different select statements that have a different amount of columns a union isn't going to work we have a problem the used select statements have a different number of columns in order to join two select statements together they need the same number of columns my employees table has six columns my customers table has three well we could select distinct columns all I need is the first name and the last name of each of these tables first name last name and look at that that works we have now created a list of all of the different people that enter our store both employees and customers there is a different variation of the Union operator that is Union all that would include any duplicates if more than one value is found within each table just temporarily I'm going to insert Plankton into our customers table insert into customers some values customer ID 5 first name Sheldon last name plankton select all from customers Plankton is both in our customers table and our employees table now what would happen if I add a union to those two select statements with just Union by itself union doesn't allow duplicates Sheldon Plankton is only within our list once with Union all you would include duplicates now Sheldon Plankton is in this list twice so use whatever is more fitting for your situation I'm gonna go ahead and delete Plankton from this table of customers all right yeah so that's a union everybody a union combines the results of two or more select statements in order to do so those two or more select statements need the same number of columns but yeah that is the union operator in MySQL hey everybody today I'm going to be explaining self joins a self-join is really just any type of join where you join together another copy of a table to itself they're used to compare rows of the same table they really help with displaying a hierarchy of data so a self join you just join another copy of a table to itself so here's an example I have a table of customers I'm going to create another column named referred by if a customer refer somebody and they register as a new customer maybe they get like a free meal or something let's create a new column within our customers table alter table customers add referral ID the data type will be int then select all from customers now let's populate this column update customers set referral ID equal to one where customer ID equals to our first customer Fred fish he was not referred by anybody that value will be null however Larry Lobster was referred by Fred fish we'll set the referral ID for him to be won Larry Lobster was referred by Fred fish so Fred fish gets a free meal at the Krusty Krab now Larry Lobster he's going to refer both bubble bass and Poppy puff so he'll get two free meals set referral ID equal to 2 where customer ID equals three and four Fred fish referred to Larry Lobster Larry Lobster referred bubble bass and Poppy puff it makes me think of a pyramid scheme I'm going to use a self-joint to replace our referral ID column with the first and last name of the customer that referred one of these people so I'm going to select all Now when using a self join we'll take from our table customers than inner join customers basically we're just joining another copy of a table to itself but we'll need to give these tables what's known as an alias a nickname let's say from customers as maybe a that's the nickname inner join customers as b the original copy of our customers table will be referred to as a the copy will be B they are the exact same table but we have two of them and we'll stitch them together with a join to link these Tables by the referral ID and the customer ID I'll add this additional line on referral ID equals customer ID let's take a look to see what we have so far column referral ID in on Clause is ambiguous am I referring to the referral ID of table a or table B I'm referring to the referral ID of table a what I'll do is prefix this column name with the name of my table followed by dot a DOT referral ID equals I need the customer ID from my table copy which is known as B I will prefix B to this customer ID column the referral ID of table a is linked to the customer ID of table B oh yeah look at that we have joined these two tables the referral ID of table a is linked to the customer ID of table B I don't necessarily need all of these columns we're displaying everything let's select only specific columns I would like a customer ID first name last name now we have one problem with our first name and last name do we mean the first and last name Columns of table a or table B let's prefix these columns with the name of our table the Alias we're using I would like the first name from table a the last name from table a followed by I'm going to put this on a new line just for readability the first name of table B then the last name of table B then be sure to include that within the customer ID too so the customer ID of table a okay we're getting somewhere customer ID first name last name first name last name okay so if somebody were looking at this table they would think why are there two columns for both first name and last name let's concatenate these two columns concat first name last name I'll add a space in between the first name and last name I'm going to give this column an alias as referred by all right there's our table we have all of our customers who were referred by another customer Larry Lobster was referred by Fred fish bubble bass and Poppy puff were both referred by Larry Lobster you could use a different type of join besides an inner join we could use a left join what we're telling MySQL is to display all of our customers on the left table if one of these customers was referred by another customer join those rows as well you can see that with the left join that Fred fish is still within our table but he wasn't referred by anybody that value is null so that's one example of a self-join we're creating another copy of the same table then joining it together to the original but you'll need to give each table an alias a unique nickname here's another example of a self-join let's select all from our employees table in our employees table Mr Krabs is a manager Sandy Cheeks is an assistant manager let's add an additional column named supervisor ID to who does each of these employees report to Squidward SpongeBob and Patrick and Sheldon they will all report to Sandy who's an assistant manager Sandy will report to Mr Krabs he's the main manager and Mr Krabs doesn't report to anybody let's add another column alter table employees add supervisor ID the data type is int and there's our new column update employees set supervisor ID equal to five that would be Sandy she's the assistant manager where employee ID equals two that would be Squidward Squidward is now supervised by Sandy three that's for SpongeBob four is Patrick six is Sheldon Plankton now Sandy her supervisor idea is going to be one she is supervised by Mr Krabs set supervisor ID equal to one where employee ID equals five and here we are using a self join we are going to display the first and last names of all of our employees along with the full name of the person that's supervising them because currently we only have supervisor ID let's utilize a self-join first let's select all from our table employees we'll need to give this table an alias as a that's the original Let's do an inner join inner join employees as b b will be the copy how are we going to link these on the supervisor ID of table a equals the employee ID of table B all right we still have one massive table but we can see here that Sandy appears in multiple rows which is a good start we don't necessarily need all of this let's select the first name and last name of table a a DOT first name a DOT last name then I'm going to concatenate the first and last name of table B so concat B DOT first name I'll add a space between the first name and last name B dot last name I'm gonna give this column an alias that's really ugly as reports two that's much better all right we have our hierarchy of data Squidward SpongeBob Patrick and Sheldon Plankton they all report to Sandy Sandy she reports to Mr Krabs that Mr Krabs doesn't report to anybody if you would like all of your employees even if they don't report to anybody you could instead use a left join because I left join we will display all of the rows from our table on the left so Eugene Krabs is here but he doesn't report to anybody so yeah basically speaking that's a self join you can use any type of join but the tables you use are the same one you use the original table then a copy of that table but you'll need to give them an alias to distinguish them when we're selecting the first and last names of our tables are we referring to the original table which I named as a or the copy because that makes a difference with self-joints they're great if you ever need to display a hierarchy of data involving the same table but yeah that is a self-join in MySQL hey everybody today I'm going to be explaining views views their virtual tables they're not real they're made up of fields and columns from one or more real tables and they can be interacted with as if they were a real table so here's an example I'm going to select all from my employees table our boss Mr Krabs he would like us to create an employee attendance sheet made up of just the first and last names of all the employees well I could create a view that is made from the first and last name Columns of the employees table now A View isn't a real table but it can behave as if it were I may want to create a view of the employee first and last names instead of another table because we try not to repeat data if we don't have to if I had two tables one of employees and another of employee attendance if I need to remove an employee I would need to do so in two places with the view it's always up to date any changes to one or more of these real tables will also update the view so let's create a view of the first and last name of our employees table to create a view you would type create view then the name of the view let's say employee attendance then as I'm going to zoom in a little bit what would we like to select let's select first name last name from a real table let's say from employees so everything was successful let's refresh our schemas window underneath the views category we have a new view employee attendance I'm going to select all from that view name employee attendance and let's see what we got yeah here's our attendance sheet we have the first and last name columns from the employees table this view can be interacted with as if it were a real table I will add order by last name ascending you can use keywords and operators that we've learned about in previous lessons on A View to drop a view you would type drop view the name of The View employee uh tendons and it's gone all right here's another example so views they're always up to date right let's create a view of customer emails select all from customers let's take a look to see what we have so far I'm going to add one more column a column of customer emails alter table customers add column email data type will be varchar I think varchar 50 should be enough space and there's our new column let's update these rows update customers set email equal to the first email will be for Fred fish f isch gmail.com then we'll need a where Clause where customer ID equals one that's our first email customer ID 2 will be L Lobster gmail.com three is B bass gmail.com four will be puff gmail.com everybody really likes Gmail I guess let's create a view from these customer emails create View customer emails as what are we selecting let's select the email column from our customers table let's refresh our schemas window yeah we have a customer emails View then I will select all from customer emails and look at that we have a listing of all the customer emails and we're going to spam these people with coupons or something I don't know when you update one or more of the real tables in your database those views would reflect any changes let's add one more customer so let's take a look at our customers table select all from customers let's add one more customer insert into our customers table some values we have five columns a customer ID first name first name will be pearl last name is crabs referral ID let's say null email will be P crabs gmail.com okay there's our new customer let's take a look at our view let's see if it updated select all from customer emails yeah look at that it's up to date that is one of the benefits of a view it will update automatically because we're using components from real tables any views that use that data will also be updated the other benefit of a view is that we don't have to repeat data in a real table ideally we'd only want to make a change in one place rather than across multiple tables alright that's a view everybody a view is a virtual table based on the results of an SQL statement they're not real tables but they can be interacted with as if they were real the fields found within a view are fields from one or more real tables in the database so yeah those are views in MySQL yeah it's me again and in today's video I need to explain what indexes are in MySQL and index is a type of data structure they are used to find values within a specific column more quickly it's technically a b tree data structure if you're familiar with what that is but if you don't don't worry about it MySQL normally performs searches sequentially through a column if I'm looking for some specific value just scan each value on the way down to see if those values match depending on what I'm looking for it doesn't really take much time if you have a small data set like I do but imagine if you have millions of transactions searching each transaction one by one is going to take a long time we can speed up that process by using an index by applying an index to a column selecting or searching takes less time however updating takes a lot more time so there are some pros and cons with using an index it really depends on the table if I'm working with a table of transactions transaction actions are being updated all the time people are constantly making purchases I don't think our transactions table would be a good candidate for an index we'll be doing a lot of updating but not a lot of searching however with our customers table I think our customers table could benefit we don't update our customers table very often only a new customer comes in every once in a while let's reduce the time it takes to search for a customer with creating an index how exactly do we want to find the customer believe it or not we do have an index with our customer ID already a customer may give you their last name and or first name let's create an index for these we'll start with last name though to show the current indexes of a table you would type show indexes from the name of the table here are the current indexes for our customers table we do have one already and that's for our customer ID that is the primary key we can search for a customer by their customer ID fairly quickly but not so much by their last name or their first name let's apply an index to those columns to create an index you would type create index then a name I will apply an index to our last name column I will name this index last name idx meaning index on the table customers in this case then list a column I will apply an index to our last name we will be doing a lot of searching by a customer's last name so I might as well apply an index so let's execute the statement let's show our indexes again show indexes from customers and here is our new index last name index it's applied to our column last name if I were to search for a customer by last name that process will be sped up now select all from customers where last name is equal to puff so my data set is already very small to begin with there's not going to be a noticeable difference in this example but if I'm working with a million customers using an index would be a lot faster if I were to search by first name instead where first name equals Poppy uh I spelled copy wrong well we don't have an index applied to our first name as of now it would be slower to search for a customer by their first name compared to their last name that first name doesn't have an index realistically we wouldn't search for a customer just by their first name we would do a last name or a last name and a first name that's where multi-column indexes come in to create a multi-column index you would type create index a unique name let's say last name first name idx on our table customers then list the columns in order the order is very important MySQL has what's known as a leftmost prefix with indexes so we will search by last name then first name let's execute the statement show our indexes show indexes from customers here's our last name first name index there's a sequence we're telling MySQL to search by a last name however if there's also a first name include that too there needs to be a last name to utilize this index if I want to I can get rid of this last name index because our last name first name index would do the same thing we can search for a last name as well as a first name if we would like to drop an index you would type alter table the name of the table drop index the name of the index last name index then I'm going to go ahead and show indexes from customers and the last name index is gone now if I was to search for a customer select all from customers where if I were to search for a last name we would benefit by using this multi-column index if I looked for a last name and a first name we would also benefit there's a sequence we would search through any last names first because that's first in the sequence but if I was to search for just a first name we would not utilize this index well everybody that's an index it's a type of data structure that is used to find values more quickly within a specific column MySQL normally searches through a column sequentially the longer the column the more expensive the operation is going to be if you were to apply an index to a column searching and selecting a value takes less time but updating that table takes longer use it if you think a table is a good candidate where you don't update it very often and well yeah those are indexes in MySQL well hello again everybody today I need to explain subqueries they can be pretty complicated but I'll try my best to explain it simply a sub query is just a query within another query you write a query and close it within a set of parentheses whatever value or values are returned from the subquery you would use within an outer query I'll give you a few demonstrations I have an employees table our manager Mr Krabs he needs us to compare every employee's hourly pay with the average hourly pay of our employees table maybe Mr Krabs is going to give people a pay reduction not an increase a pay reduction but he needs to compare the hourly pay of every employee versus the average how can we write something like that a task like that I would say involves at least two steps we need to find the average hourly pay first and then display every employee's first name last name hourly pay then the average pay let's begin with the average pay select average function hourly pay from my employees table so far so good 15.45 is the average hourly pay of an employee how do I use this number whatever value or values are returned from a sub query we can use within a larger outer query let's delete this semicolon I will surround this statement with a set of parentheses I will then write the outer query let's select the first name last name the hourly pay then comma our subquery from employees there we are although I'm going to give the subquery an alias as average pay and I think I'm going to move this to the next line that is much better so we have accomplished our task we have compared every employee's hourly pay versus the average once we complete the subquery whatever value or values are returned from the sub query we would use within the larger picture the outer query just imagine that we replaced the subquery with 15.45 this value is what was returned although you don't want to write a concrete number like this just because the average pay is bound to change writing a sub query like this would calculate our average pay regardless if we add or remove employees let's try a different example let's find every employee that has an hourly pay greater than the average pay so again we can begin with the sub query we need the average pay select average function hourly pay from employees so again we return 15.45 I'll use this within a where Clause let's select the first name last name hourly pay from our employees table where the hourly pay is greater than our sub query Mr Krabs and Sandy Cheeks are both making more than the average hourly pay what was returned was 15.45 then it's just a matter of completing the outer query select the first name last name hourly pay from employees where hourly pay is greater than 15.45. it looks complicated but when you break it down into steps it's a lot easier to visualize we're going to cover a completely different example we will be working with customers and transactions I'm going to select all from my transactions table we have a few transactions I would like to find the first name and last name of every customer that has ever placed an order we can do this step by step let's select every customer ID from transactions that's not null select customer ID from transactions where customer ID is not no let's take a look so we do have a few repeats we have one two three and three you can add this distinct keyword if there's any repeats that would eliminate them we have three registered customers that have placed orders in the past customer ID is one two and three but we don't know who these customers are we will use these values within a larger outer query I will surround this query within a set of parentheses eventually we'll use this within a where clause let's select the first name and last name from our customers table where customer ID I will then use the in operator then add my sub query there we are there's our three customers that have placed orders in the past Fred fish Larry Lobster bubble bass this sub query return to the values one two and three imagine that once this query resolves we are left with the values one two three so this outer query makes a lot more sense select the first name and last name from customers where the customer ID is one of these values one two or three you could do the inverse two find the first name and last name of every customer where the customer ID is not in the sub query this query would return every customer that has never placed an order let's send them a coupon in the mail to convince them to place an order we were left with values one two three select the first name and last name from any customers where the customer ID is not in one of these values one two or three Pearl Krabs had an idea five poppy pups was four so yeah that's a sub query everybody it's just a query within a larger query it helps to visualize this step by step imagine that we're completing the subquery first whatever value or values are returned we can use within a larger query and well yeah those are subqueries in MySQL hello everybody today I will be explaining the group by Clause the group I Clause will aggregate all rows by a specific column it's often used with aggregate functions such as the sum function Max Min average count just to name a few for my example I have a table of transactions now if you've been following along in my video series I did add an additional column named order date and filled in a few dates as well as two additional rows if you would like to follow along I would recommend pausing the video adding a new column for order date add these order dates then add to additional rows that's all the data that we'll need in this topic now our boss Mr Krabs he needs us to tell him how much money he made per day what is the sum of all of the amounts per day we have three different dates January 1st January 2nd January 3rd what is the sum of every amount on these days so we can use the group I Clause that would help us we will select the sum of every amount as a column we will also display the order date from transactions then we will Group by we are grouping by our order date Group by order date column yeah look at that there we are here is the sum of every amount that is grouped by these dates here's the total amount we made January 1st the total amount January 2nd the total amount January 3rd let's try a different function how about V Max function what was the maximum amount on each day these are the maximum transactions that have occurred on these dates what about men these are the minimum amounts grouped by order date what's the average order every day you're the averages maybe count how many transactions have taken place each day we have two orders on the first two orders on the second three orders on the third let's try a different column let's Group by customer ID this time how much has each customer spent total we will select the sum of every amount we will also need a column for customer ID from our table transactions then we will Group by the customer ID column this time here is the sum of every amount that each customer has ever spent we do have some rows that don't have a customer ID so we would sum those amounts as well so far the customer with an ID of three they have spent the most amount of money at our establishment what about the max here is the maximum order that each customer has placed the min the average then count here is the amount of times that each customer has ever visited our establishment what if we would like to use a where Clause well using a where Clause along with the group I Clause normally doesn't work for example I would like to display all customers that have visited more than once who are the repeat customers so if I attempt to use a where Clause where count our amount column is greater than one well we would run into an error if you need to use a where clause and you're using the group by Clause you would want to instead use the word having that would do the same thing Group by customer ID where the customer ID has made more than one purchase we are also aggregating all of the customer ideas that are null let's use the and logical operator to remove that and customer ID is not null there we are the customer that has an ID of three they have visited our establishment two times they meet the requirements of this having Clause they have visited our establishment more than once and their customer ID is not null so if you ever need to use a where Clause if you're already using the group I Clause you would instead use having so in conclusion the group by Clause will aggregate all rows by a specific column it's often used with aggregate functions such as sum Max Min average count for example you could Group by an order date what are the total sales per date or by a customer ID how many times has each customer visited our establishment those are just a few examples and well yeah that is the group by clause in MySQL hey again it's me today I'm going to be explaining the roll-up Clause like a fruit roll-up it's an extension of the group by Clause it produces another row and shows the grand total it's also known as a super aggregate value here's an example I have a table of transactions what I would like to do is to group each transaction by the order date then produce a grand total here's what we'll do I'll give you a few different examples let's begin by selecting the sum of the amounts and the order date from our table transactions then we will Group by the order date and there we are we have the sum of every amount per order date using this roll-up Clause I can add an additional row that shows a grand total so to say all I have to do after the group by Clause is ADD with roll up and there you go here's the grand total 7.88 plus 8.37 plus 8.97 is 25.22 here's another example let's count the transaction ideas buy order date we have two transactions on the first two on the second three on the third for a total of seven transactions I'll give you two more examples let's select the count of our transaction ideas per customer I'm going to give this count an alias as number of orders as well as the customer ID column from our table transactions we will Group by customer ID let's see what we have so far then I will add with roll up here are the amount of orders per customer ID the customer with an ID of three has visited here more than once the grand total of the number of orders is seven that's the roll up one last example let's select our employees table select all from employees we will group all of these employees by their employee ID then display a roll-up of all of the hourly pay as a business how much are we spending on all of our employees per hour so let's select the sum of our hourly pay I'll give this an alias as hourly pay I guess it really isn't different from the column name we will display the employee ID as a column from our table employees Group by the employee ID with rollup here's the hourly pay of each employee as well as a grand total if all of our employees are working currently we are spending as a business 92.75 cents per hour well yeah that's the roll-up Clause everybody it's an extension of the group by Clause it produces another row and shows essentially the grand total it's also known as a super aggregate value to include a roll-up after the group by Clause just add with roll up it's probably good for accounting and well yeah that's the roll-up clause in MySQL why hello there everybody today I will be explaining the on delete Clause there's two versions on delete Sentinel and on delete Cascade with on delete set null when a foreign key is deleted we can replace the foreign key with the value no otherwise there's and delete Cascade when a foreign key is deleted delete the entire row here's an example let's take a look at our table transactions select all from transactions in my transactions table the customer ID column is the foreign key what would happen if I were to delete one of these customers let's select all from our customers table I'm going to delete poppy puff she has a customer ID of four delete from customers where customer ID equals four if you run into this error cannot delete or update a parent row a foreign key constraint fails there's a foreign key that's preventing us from deleting this customer because this customer is used elsewhere in a different table like that transaction stable so just temporarily for this demonstration I'm going to set foreign key checks to be zero and that should work our customer with an idea 4 is gone one two three five poppy puff is no longer within our customers table before I forget I'm going to set foreign key checks back to one let's take a look at our transactions table underneath this transaction of 1005 we're referencing a customer that doesn't exist the customer with an idea 4 is no longer within our customers table we have a couple options whenever we delete a primary key from another table that's being used as a foreign key as somewhere else we can either replace that value with null or delete the entire row with Cascade with on delete set null we can replace this value with null much like these two rows or on delete Cascade we can delete the entire row let's begin with on delete Sentinel I'm going to reinsert Poppy puff back into our table of customers and there we are poppy puff is back in the customers table now with on delete Sentinel if we're creating a new table let's say I'm recreating my table of transactions after adding the foreign key constraint foreign key our customer ID column references the customer ID column of the customers table I can add this clause on delete set no that's if I'm creating a new table but I already do have a transactions table I don't want to update it however we can update an existing table with this Clause I already do have a foreign key constraint I'm just going to drop it real quick alter table transactions drop foreign key then the name of the foreign key mine is FK underscore customer ID and that key is now dropped we are going to add a foreign key constraint to our transactions table with this clause on delete Sentinel alter table the name of the table add constraint you can give your constraint a name I'll give it the same name as before FK customer ID list the foreign key foreign key my foreign key is customer ID references another table which was customers and what is the primary key of that table customer ID then we will add the clause on delete set no if I delete a customer ID from customers the foreign key of that row will be set to null automatically let's execute this let's take a look at our transactions table select all from transactions I am now going to delete customer number four from the customers table delete from customers where customer ID equals four yeah check that out with our transaction ID of 1005 the customer ID is now null now there's and delete Cascade when a foreign key is deleted we can instead delete the entire row let's go ahead and add poppy puff back to our customers table all right she's back in let's drop the current foreign key constraint of our transactions table alter table transactions drop foreign key FK customer ID or whatever else it's named and that key is now gone we will add the on delete Cascade Clause to a table that already exists alter table transactions add constraint you can come up with the name FK underscore transactions underscore ID list the foreign key foreign key customer ID references the customers table and the primary key of that table is the customer ID then add on delete Cascade let's take a look at our transactions table select all from transactions oh let's update this customer ID real quick I forgot to do that update transactions set customer ID equal to four where transaction ID equals 1005. yeah I forgot to add that back now let's do it delete from customers where customer ID equals four yeah take a look at that the entire row is now gone we have transactions one thousand three one thousand four one thousand six one thousand five is missing because it was referencing the customer with an ID of four we set the foreign key to delete the entire row when that foreign key is deleted that is on delete Cascade well in conclusion everybody on delete is a clause where if you delete a foreign key you can set that value to be null or Cascade which will delete the entire row which has its uses and well yeah that's the on delete clause in MySQL why hello again it's me so today I gotta explain stored procedures a stored procedure is prepared SQL code that you can save it's great if there's a query that you write often for example I have this statement it's very verbose there's a lot we have to write if this statement is something I have to write often I could save it and reuse it later this statement will interjoin the transactions table and my customers table then display all customers that made a transaction this statement is very verbose if I have to write the statement often I could save it as a stored procedure I can refer to the nickname that I gave that snippet of code the name I gave this procedure was get customers then I need to call this stored procedure and that does the same thing and it's a lot less to write there's also a few other benefits which we'll discuss at the end of the video let's begin with something simple I would like to select all from my customers table to turn the statement or statements into a stored procedure I will type create procedure then a name for the statement or statements I will name this statement get customers add a set of parentheses type the keyword begin wherever your statement or statements end type end then for clarity I'm going to tab all of my statements between begin and end so we have a problem we're trying to create this procedure however MySQL is stopping at this semicolon here we end all statements with a semicolon rather than at the end our semicolon is known as a delimiter it's kind of like the period at the end of a sentence we're telling MySQL that our statement ends here but we need our statement to end here after the end keyword we can actually change the delimiter temporarily type delimiter before we create the procedure typically when people change their delimiter temporarily they'll either use two forward slashes or two dollar signs I'll stick with the dollar signs because I think that's cooler after we create our procedure let's change our delimiter back to the semicolon MySQL no longer recognizes our semicolon as the delimiter after the end keyword I will use our new delimiter to end this statement that should work now let's execute the statement we have created our procedure I'm going to refresh my navigator window underneath stored procedures we have our stored procedure of get customers to invoke the stored procedure type call the name of the procedure get customers add a set of parentheses then a semicolon that will execute whatever code you stored within your stored procedure to drop a stored procedure you would type drop procedure the name of the procedure get customers let's try another example this time we will send our procedure a piece of data within the set of parentheses a customer ID number such as 1 2 3 4 so on and so forth we will get a customer by their customer ID we will create a procedure I will name this procedure find customer add a set of parentheses to find a customer by their customer ID when we invoke the stored procedure within the set of parentheses we will need to place a customer ID but when we create the stored procedure we have to set up what is called a parameter we will type in then a nickname for that piece of data we're passing in a transaction ID I'll give this parameter a nickname of ID then we need the data type of what we're passing in we're passing in an integer so we have one parameter set up let's use that begin keyword that end keyword list our statement or statements between these two keywords I will select all from my customers table where my customer ID equals the ID that we pass in that nickname we need to change the delimiter because MySQL thinks we're trying to end our statement here but we need it to end after the end keyword so let's change the delimiter to double dollar signs then change it back [Music] all right we have created that stored procedure let's invoke it call find customer within the set of parentheses we need to pass in a customer ID number let's pass in one that would give us Fred fish two is Larry Lobster three is bubble Bass five is Pearl crabs when you invoke a stored procedure you can pass in some data depending on what you need exactly for your stored procedure let's drop this procedure then do one last example drop procedure find customer [Music] this time we will send two arguments two pieces of data a first name and a last name create procedure find customer within our set of parentheses the first piece of data we'll pass in will be a first name but first is already a keyword maybe F name meaning first name then the data type let's see for customers the data type of first and last names is varchar50 varchar 50. for your next piece of data you would separate that with a comma in our nickname will be lname for last name data type is varchar 50. then I'm just going to put that on a new line for clarity type the begin keyword and write your statements between begin and end we will select all from our customers table where first name equals the name of the data that we pass in and last name equals the other piece of data the second piece the last name again we need to change the delimiter to create this procedure double dollar signs then change it back we have created our stored procedure let's invoke it call find customer within the set of parentheses we will list a first name and a last name Larry comma Lobster get Larry Lobster's information let's find him and there he is customer id2 first name Larry last name Lobster referral ID of one email is L Lobster gmail.com all right everybody that is a sword procedure it's prepared SQL code that you can save it's great if there's a query that you write often a few of the benefits is that it reduces Network traffic it increases performance and is more secure and administrator can grant permission to a user or an application to use a stored procedure one of the downsides though is that it increases memory usage of every connection and well everybody those are stored procedures in MySQL hey everybody today I need to explain triggers when an event happens a trigger does something I know that sounds very generic for example when we insert update or delete a record we can use a trigger to check data handle errors or audit tables we could do a plethora of things here's an example I have a table of employees we have an hourly pay column maybe I would like a salary column whenever we add an employee or update our hourly pay the employee's salary will be changed automatically with the trigger before creating a trigger let's update our employees table We'll add a salary column alter table employees add column salary the data type will be decimal 10 digits Precision of two we will place this column after the hourly pay column then select all from our employees table let's see what we have there we are there's our salary column let's update our employees table set our salary column equal to the hourly pay times to calculate an employee's salary using an hourly pay Sheldon Plankton for example take the hourly pay ten dollars per hour there's typically 40 hours in a work week then 52 weeks in a year Plankton would have a salary of twenty thousand eight hundred dollars so a shortcut you could just take the hourly pay times 2080. there are 2080 work hours in a typical year so take the hourly pay times 2080. then we will select all from employees there is everybody's salary currently whenever we update an employee's hourly pay I would like to also update the salary automatically with the trigger I don't want to have to calculate every employee's salary manually you know using a calculator like I just did we're going to create a trigger create trigger then we need a name for this trigger before we update the hourly pay we're going to do something I will name this trigger before hourly pay update we'll use either the keyword before or after do you want to do something before or after I would like to do something before before what before an insert before delete before an update before an update in this case on our table employees then add this line for each row we may be working with more than one row using a trigger so that's why we're including this line This trigger can involve one or more rows now what are we going to do before we update an employee's hourly pay let's set the salary equal to the hourly pay times 2080 because there's 2080 work hours in a typical year okay there's one more thing I'm going to add let's prefix salary and hourly pay with this new keyword new DOT salary new DOT hourly pay MySQL doesn't know if we're referring to the old salary and the old hourly pay we're telling MySQL we're calculating a new salary use the new hourly pay when that's changed in place of the old one okay let's take a look let's execute this statement our trigger has been created in your schemas window to find any triggers go to tables then find that table where we applied the trigger employees then triggers I probably need to refresh this there it is before hourly pay update otherwise you can type show triggers and here's our trigger here's the trigger name event update the table the statement and the timing let's select all from employees Mr Krabs is going to give himself an hourly pay raise because he's greedy let's update our employees table set the hourly pay equal to fifty dollars per hour where our employee ID equals one so Mr crab's salary is about fifty three thousand dollars per year before we update it we will change the salary of this employee from fifty three thousand dollars to over six figures 104 000 that change happened automatically with the trigger before updating the employee's hourly pay we calculated a new salary automatically let's make another change let's increase every employee's hourly pay by one dollar per hour Mr Krabs is now feeling generous update employees we will set the hourly pay equal to whatever the hourly pay currently is plus one we will apply this for every employee we don't need a where clause there everybody is now making one more dollar per hour and the salaries reflect that let's create a new trigger we will calculate a salary whenever we insert a new employee more specifically whenever we insert a new hourly pay right now we have a trigger that only kicks in when we update an hourly pay okay let's delete plankton Plankton got fired delete from employees where employee ID equals six select all from employees their Plankton is now gone let's create a trigger create trigger before hourly pay insert are we doing something after or before we're doing something before an event before are we updating inserting or deleting we are inserting before we insert a new record on our table employees for each row let's set the new salary equal to the new hourly pay times 2080. here is our new trigger let's take a look at our employees table select all from employees let's insert a new employee insert into employees values I'm just going to follow these columns employee ID 6. first name is Sheldon last name plankton hourly pay is ten dollars per hour for the salary I'm going to set this to null our trigger will kick in and set that position janitor higher date 2023 January 7th supervisor idea five let's see if our salary is calculated it was Plankton's hourly pay is ten dollars per hour meaning he has a salary of twenty thousand eight hundred dollars per year I do have a few more examples triggers are something you'll want to master they're really helpful we're going to create a new table this time create table expenses our expenses table will have three columns an expense ID the data type will be int this will be the primary key you could set this column to Auto increment if you want but but that's overkill for this topic I would say I'm going to try and keep it simple expense name the data type is varchar 50 is good I suppose then expense total I will set that to be decimal 10 digits Precision of 2 is fine then let's select everything from our table expenses let's insert some values insert into expenses we have three rows one two three ID of one the expense name is salaries for the value for now I'm going to set that to be zero the second row will be two supplies zero ID of three maybe taxes and zero I will calculate the expense total of my salaries bro update expenses I will set the expense total equal to I'll use a nested query select the sum of salary from employees where our expense name column right here is equal to salaries this is all of these salaries combined from the employees table the total is two hundred fifty four thousand two hundred eighty dollars whenever we delete an employee we will update this value found within another table our expenses table so let's create a trigger create trigger after salary delete are we doing something before or after we're doing something after deleting a salary after delete on employees for each row we will update our expenses table and set the expense total equal to whatever the expense total currently is minus that employee's salary if we're deleting a salary we will refer to that salary as the old salary the net aware Clause where expense name equals salaries okay we have created our trigger let's take a look at our expenses table I'm going to delete Plankton again he's fired delete from employees where employee ID equals six that's plankton then select all from expenses look at that our expense total changed it's now two hundred thirty three thousand dollars let's create a trigger that will update our salaries whenever we insert a new employee create trigger after salary insert we're doing something after this time after an insert on our table employees for each row we will update our expenses table we will set the expense total equal to the expense total plus the employee's new salary new DOT salary where expense name equals salaries let's insert a new employee let's take a look at our expenses table select all from expenses we will insert a new employee insert into employees our values employee ID 6. first name is Sheldon last name plankton hourly pay of ten dollars per hour the salary will be null that will be calculated automatically with the trigger position is janitor higher date 2023-01-07 and a supervisor idea5 let's see if this expense total will change which it does when we insert a new employee we're specifically a salary our expense total of salaries will be updated one last example that I promised we're done let's create a trigger that will update this value when we change an employee salary and that will probably be the most complicated one let's create a trigger after salary update we're doing something after we update on our table employees for each row let's update expenses set the expense total equal to the expense total Plus the employee's new salary minus the employee's old salary what's the difference we'll find the difference between the employee's new salary and their old salary add that to the expense total where expense name equals salaries we have created our trigger let's select all from expenses Mr Krabs is going to give himself another pay raise update employees set the hourly pay of Mr Krabs to 100 per hour where employee ID equals one our expense total underneath salaries is now changed it's now 356 thousand two hundred dollars well all right everybody that's a trigger in simple words when an event happens do something such as when we insert update or delete something from a table a few of the benefits is that you can check data handle errors and audit tables it's a really helpful tool to have and well everybody those are triggers in MySQL

Channel: Bro Code

Views: 1,603,496

Keywords: sql, sql tutorial, sql course, sql for beginners, Structured Query Language, sql course for beginners, learn sql, free sql course, web development, mysql, mysql tutorial, mysql course, rdbms

Id: 5OdVJbNCSso

Length: 180min 0sec (10800 seconds)

Published: Mon Jan 02 2023