**Mastering MySQL: A Step-by-Step Guide**

When it comes to managing relational databases, MySQL is one of the most popular and powerful tools available. In this course, we'll take you through the ins and outs of working with MySQL, covering topics from creating and managing databases to running queries and troubleshooting common issues.

**Understanding Database Size and Management**

Before we dive into the nitty-gritty of MySQL, let's start with something simple: understanding database size. We begin by logging into our MySQL server as the root user. To verify the size of the actual database file, we run a command to display its size: `SHOW DATABASES;` This will show us all the databases present on the server, including their sizes.

For example, in this case, the employee database is 176 megabytes. We can see that it's much smaller than the actual database file, which suggests that some of the data may have been removed or deleted. To confirm our suspicions, we log out of the MySQL server and then back in as the root user.

**Dropping and Restoring Databases**

To drop a database, we simply type `DROP DATABASE` followed by the name of the database we want to delete. In this case, we'll drop the employee database. To verify that it's gone, we run `SHOW DATABASES;` again and see that the database is no longer present.

However, since we created a backup of our original database before dropping the employee database, we can restore it by creating a new database with the same name and then importing the backup file. We create a new database called `employees` using the command `CREATE DATABASE employees;`. Then, we import the backup file using the command `mysql -u roots < /path/to/backup/file.sql;`.

**Verifying Database Import**

To verify that our database has been successfully imported, we log back into MySQL as the root user and run a series of commands to ensure everything is working as expected. We start by running `USE employees;` to switch to the new database.

Next, we run `SHOW TABLES;` to see if all the tables have been restored correctly. Since our original database had several tables, including an audit table with a trigger associated with it, we can confirm that all these tables are present in the new database.

To further verify that everything is working as expected, we run a few select queries on each table to ensure that the data has been successfully imported. For example, we run `SELECT * FROM employees;` to see if the employees table contains the correct data.

**Conclusion**

With these steps, you've successfully completed our MySQL course and gained a valuable insight into this powerful relational database management system. Whether you're a beginner or already have some experience with MySQL, practicing and refining your skills will help you become proficient in using this tool. Remember, MySQL is an essential tool for anyone dealing with data, and mastering it will open doors to new opportunities in web development, data analysis, and more.

In the next part of our guide, we'll explore advanced topics such as indexing, caching, and replication, as well as discuss how to troubleshoot common issues and optimize your MySQL performance.

"WEBVTTKind: captionsLanguage: enunlock the power of databases with this MySQL course for beginners whether you're new to databases or looking to level up your skills this course has you covered you'll learn how to create manage and manipulate data using MySQL you'll discover the secrets of keys learn to write queries like a pro and automate tasks with triggers no jargon just practical knowledge Josh from keepit techie teaches this course he works as a SQL Server database administrator so he's the perfect person to teach this course what's up guys this Josh from keep techi and welcome to my comprehensive course on MySQL now MySQL is a relational database management system and in this course I'll try to explore the ins and outs of my SQL on Rocky Linux which is a powerful combination for managing your data effectively now my SQL is not just any database it's the fundamental Port of the lamp stack which stands for Linux Apache MySQL and PHP and then also the limp stack which is Linux engine X MySQL and PHP and these Stacks are widely used for Content Management Systems like WordPress jumer and various other web applications and I'll start with the basics covering the installation login procedures and a little bit of database management so whether you're a beginner or have some experience this course will help you master my SQL on this fantastic Linux distribution so let's head over to to Rocky Linux 9 server and get started with the installation process okay so here we are logged into Rocky Linux 99 server and to install my SQL we'll follow these simple steps and I just brought up the OS release information under so you guys can see that I am on Rocky Linux 9 which is a one toone comparison to Red Hat 9 so let's go on and clear and first let's ensure that our system is up to date and we need to run the following command to refresh the package repositories and apply any available updates and that command is pseudo dnf and update and we'll wait for this to finish but this will basically check to see if there is any updates and if there are it'll upgrade those packages on the server and as you can see we don't have any updates so we're good to go I believe I updated this before I started this video so now to install my SQL all we have to do is type pseudo dnf install and then my SQL server and I'll just tab it out it'll pull up the latest version of my SQL so that's basically the version number and all that stuff for this actual package so let's go on press enter that'll go through and resolve all the dependencies for MySQL as well as the package for MySQL so all we have to do is type why for yes we want to install this package and use the amount of space that it will use use on the operating system so scroll on press enter it'll go through like I said download all those dependencies as well as the MySQL server package and then install it on the system so I'll be back when it finishes all right so the installation is complete and now we need to check the MySQL Services status and we can do that by using the system CTL command so let's type pseudo system CTL and one thing I noticed on Rocky Linux it doesn't start the service after the installation or part of the installation process similar to like you buntu typically when you install MySQL it'll go down and start the service and you may have to go in and enable the service but most of the time it starts the service right off but on Rocky Linux or rail it doesn't so system CTL and then status and then what we want to do is check that my SQL status and you'll see that it's not started boom and it'll say right here where where it says active it says inactive so right now that lets you know that it's not started and then also Al the enabled portion of it this is essentially for when you reboot the server itself that service will store it up right now it's disabled we want to enable that so it's two commands we can run right fast we basically have to run the pseudo system CTL start and this will start the actual service so let's go and press enter all right and so we can run the status command again and this will show you that that service is running now so as you can see it says active and running but we still need to enable it so let's let's go down and press Q that'll drop us back down to the terminal again and we get up arrow and then let's remove the status and type enable press enter and it'll go through and enable This Server so each time you reboot your Rocky Linux server my SQL will stored and so let's check the status again we can go back in here and look at it and this is what we're looking for where it says enabled that lets you know it's enabled so that service is stored up and so that's pretty much it we've successfully installed my SQL and now it's time to show you you guys how to log into it and you use a command called MySQL and you also want to put the user account and if there is a password then you want to put that password there as well or the option to ask for a password but since this root account that's on the system of the default root account it doesn't have a password you can log right in and so let me go down and type the command in and we are using a pseudo account so we have to type pseudo in order to run the MySQL command so let's type pseudo MySQL and then we need to put our user so we have to put our option in there- you and then the username so I'm going to use that that root account like I said that's the default account that's there after installation and it doesn't have a password on there but we need to put- P at the end of it so it will access for the password and you'll see once we get past this step It'll ask for the password but we don't have to put a password in because there is no password set for it now I want to point this out to you as well though you want to make sure you follow best practices and create a dedicated user account for your projects you don't want to be using this root account with no password on it so go on and press enter It'll ask you for a password it says enter password and since we ran pseudo it normally will ask you for a pseudo account so if you you may see it asking for the pseudo account so you want to type that in of course but then it'll pop up with this enter password for the MySQL account so I just want to know let you guys know that the way you're seeing it on mine it may not show up on yours you might have to type in a password for pseudo and then press enter because there is no password so let's press enter right fast and that'll get us into our MySQL server now that we're in let's explore some basic MySQL commands and functionality now once you're inside the MySQL prompt which is what it's showing right here with the my SQL and then that right arrow you can begin executing commands and so let me walk you guys through a few ones the first one you need to know is the help command so if we type help and then every command pretty much every command you have to type a semicolon behind it similar to typing out a query in order for it to run that statement for you so help will basically give you a whole bunch of information on how to use my SQL if you scroll through here you'll see a Le list of all MySQL commands and some of these commands they use that left slash in front of it and that's what I was going to show you guys as well after this by typeing help you know as one word and then the semicolon there's another way of actually getting to the help and that's the back SL and H but the other ones are there and they have an explanation of what they actually do and I won't go through them all cuz I typically don't use a lot of these commands these my SQL commands right here but I just wanted to show you guys where to get that information from if you need it now let me go to and run that back slash and then H and that way you guys can look at the same thing and it looks like it didn't change because it just basically ran it again and display the same information so we are good to go now the first thing I wanted to show you guys is how to to find databases within your database management system and so I'll show you guys that command now and it's essentially the show command so all we have to do is type show and then we want to type what we what we're looking for which is databases so databases with an S and then semic calling at the end of it and this will show you all the databases that are currently on the system so let's go down and press enter and what you'll see is some built-in databases for the database management system like information schema like the performance schema this my SQL database as well as the cyst database and I recommend you don't touch any of those those are vital to the system running properly but this is how you actually look at all the databases that are on the system all right so now that we know how to look at the databases that are currently on the system I wanted to show you guys how to create a database cuz this is like one of the most important steps after that because we need to understand how to create those databases so all we have to do is type create and a lot of these commands you'll you'll start remembering them almost like a sentence cuz that's essentially what it is like show databases simple you know create database so we want to create a database and that's the command right there so create database with no s cuz it's singular you know so no s is needed and then the database name so we can name it whatever we want to I'm going just do DB name and then put a semicolon at the end of it and go down and press and it'll create our database as you can see query okay one row affected so and what row they're talking about is the built-in table that list out all the databases that are in the database management system so we can go back and look at that show database command show databases command and we'll see that that new database is there is called DB name that's what we named it now probably the next question you have is how do I create tables within the database that we just created and I need to show you another C command beforehand and you need to specify the database you want to run queries against by using the use command so like I said almost like a sentence you just type use and we want to use a specific database and which database we want to use is that DB name and then you can end out your sentence with the semicolon and press enter and this will make a change so that we are using database name for the following commands and you need to do that every single time so my SQL will know what database you're creating things with or even running other queries like select or update it needs to know what database is working with so you have to use this command in order to change it to the database that you want to work with and now I'm going to show you guys how to create a table and like I said it's almost like a sentence so create table and then we want to create a table name so we have to name that table so I'm going just name it employees now this is a little bit more complicated because it requires multiple lines so after this we have to specify each one of the columns within that table and you can press enter in here and that's why it's important to understand the purpose of that semicolon that closes out your query until you type that semicolon it will not close out the query so let's go down and press enter boom and as you can see it'll go to the next line allowing you to type some more information and we want to go through and create each one of the columns within our database and so the first one is employee ID a lot of times when you create a table you want to create what they call a primary key and this is a unique identifier for each row of data within the database and that just keeps things a little bit more cleaner in the database as well as a way to make sure we don't have any duplicate information and if the information is duplicate in other columns it will still recognize it as a different record so the next thing we want to use is integer so this is an integer meaning a number so and then also with our primary key there's an option you can put here called autoincrement and this will allow it to create numbers in sequence so 1 2 3 4 5 6 7 89 10 it will you know keep going as new records are added to the database or to the table and so let's type that in right fast so autocore increment and then also this is a primary so we have to type that out as well and then we have to put a Comm at the end of it that'll let us know that that is one of the columns within our table and then we can press enter and we can type in our next column and let's start off with first name and just like the init we have to specify what type of characters are in it the init is integer like I said that's a number well this is a variable you know all those letters and so we have to type V chore and then we can specify the amount of characters that you can type within that and so the default is like 255 you'll see a lot of people put 255 so that's a good length for the amount of characters that can be put into it and then we can also specify that this column should always have information in it you don't you can't create another row unless information is in that field within that column so let's go type not null and that's what that mean null meaning not empty or blank and so we can type a comma at the end of it and let's go on and type in our next column which I go with lastore name and then same thing so V chore and then 255 and we can close that out and then not all as well for that one hit a comma and let's do one more and that'll be the birth date of our employee so that's just some information we want to put in there for this table or require for this table and then we can put dates and then we could leave it not and all off because we don't need the birthday I'm going just I'm just showing you guys that and then once it's the last one you don't have to put a comma at the end of it but we do have to press enter and close out these parentheses if you open a parentheses you need to close it so let's go on and close it right fast and then what is the last thing I'm sure you guys remember but is the semicolon that will close out your query and so all of this information here it will create each one of the columns within that table so it'll create the table and then it'll create all the columns within that table here so let's go and press enter and as long as you see query okay zero rows affected that means it successfully created that table and so now we have that employees table within the database we can check the structure of it to verify that we typed it in correctly by using the describe commands so let's go on and type describe and then our table so it is the employees table and of course at the end of each statement you have to put that semicolon at the end of it and this will give you information about this table so as you can see the fills we got our fills our different columns within the table and we got employee ID then it's uh integer and then null and then this is the primary key and then also this is auto increment you'll see that listed there and this will break down basically the structure of this table so this is what that describe is for now I won't go into adding data yet but I'll show you guys how to drop this table and with my SQL or with SQL the actual language that we're using drop is actually how you delete something within the database so let's say we want to drop that employees table that we just created so all we have to do is type drop and then we want to specify that we want to drop a table and then the table name so like I said employees and then type a semicolon at the end and this will drop that table and I forgot before I drop that table I wanted to show you guys something now so I'm going just press uh up arrow and find that create table employees table and I'm going bring it back right fast so let's press enter and of course that's one way of showing you guys you could type this out without the separate lines it's just easier to see if you put it on a separate line you see what I'm saying but you could type it out just like that the way it's listed there so I wanted to show you guys the show tables command and let's go on and type that in so show tables and like I said we're still using that DB name database and so let's go on and press enter boom and that will show you the actual table that's there and now let's go back and remove that table I'm going to drop it again so drop table employees boom and then we can run that show tables again and this will show you that there are no more tables on there so I didn't want to move forward without showing you guys that now the last thing I want to show you guys is how to actually drop a database and essentially that's deleting the entire database just like we deleted the entire table we can delete the entire database so let's go on and type that command in right fast but it's simply drop and then database and like I said it's just like a sentence and then we need to specify that database name so DB name and then semic at the end of it press enter and that will drop that database and now we can type show databases and then the semicolon at the end of it and press enter and this will let you verify that that name database is gone so you won't see it anymore so we get to go now be very cautious when running that command because it will remove all tables and data within that database so just be very careful with it okay so moving on to the next part of the course we need to have a database with lots of data so you can see examples clearly and I'm going to import a test database name to inlo and this is on GitHub and let me switch over to my browser so you guys can see the link and if you want to do the same you can find a link in the description to import the database and this database was created by bik base on GitHub and like I said I'll have the link so you can get here but let me just show you guys the directory structure so so they have a MySQL dump that you can import and this will generate a full SQL database for you it's called the employees database so if we go under MySQL you can see they have a postgress as well if you need a PO example but MySQL is what we'll be using and they have a data full or a data set full data set large data set small in the full we'll have the full amount of data which is the one I recommend you guys use they also have a large which is a smaller version of the f doesn't have as much data in it and then the small is kind of like a sample size of the data so you can use that as well it really doesn't matter just pick which one you want I'll import the data set full essentially what we're going to do is install this data dump it's a SQL dump and I'll get to this in a later part of the video where I show you guys how to back up databases and import databases or restore databases but this is the five whee import and let me go down and go here first and what I want to do is clone this repository to the server so we're going to copy that link and then go back to our Ser server terminal and exit out of my SQL so we could just type exit and this is a good time for me to show you guys how to exit you could just type exit and press enter and it'll get you out of my squel and and now that we back at the terminal we can go down and install get cuz we need get installed so we can just type P sudo dnf install and then get and press enter type in our Pudo password boom and it'll go through install G as well as all this dependencies and we type why for yes boom now go on and install get on our system and now we can get clone that actual repository and we don't need to change directories we can use our home directory and so all we have to do is type get clone and then we can paste that link and press enter and it'll clone down that actual repository for us and now we can import this test database so first thing you need to do let's LS right fast I show you guys the directory but we need to CD into that directory and we want to go into my SQL and then we need to go into the directory of the location of the size of the database that you want to in install so let's T it out right fast but there is that full like I said so that's the directory we need to go into now if you want to go into the other ones you just select large or the small one as well you can select that one as well like I said I'm going to do the full boom press enter and if we LS this directory this will show you all the files that are in there and it's so allow you to see the file that we want to import and we need to use Pudo cuz we're running MySQL under a regular account which is that Josh account so we get sudo my SQL and then what we want to do is use the left arrow and then the actual SQL file that we want to import or the database that we want to import which is that employees. SQL file so scroll on press enter this will take a little time because the full is pretty big it has about 300,000 records in at least in the employee table and so it does take a while let me go down and press enter we're going to need this in order so I can show you guys this information in a better way as far as running the queries and the different things I want to show you guys in the examples it's best to have a database with a lot of data in it and this is the quickest and easiest way to set that up and like I said this see that table right there it says loading employee that's the biggest table that's in there and it's going to take a while to actually import everything so I'll be back when it finishes and then we can log back in Sequel and go through the more of the examples all right so the import completed successfully it took about 3 minutes and 57 seconds and so let me show you guys how to test it right fast I'm going LS this directory again so you guys can see but there is a test file in here it's a md5 hash test so we can run this test by typing pseudo MySQL and then- T and we need to put our left arrow in there again and then that testore employee md5 SQL and that will test the actual database and just make sure everything was imported successfully on the server so just go on and run it right fast and it'll have some output you got you guys will see and as long as you don't see any errors that means everything was imported successfully all right so the test is complete and we can scroll through this and just see that it it just verifies that everything is okay so all the records match you know what's supposed to be imported when you run this thing and as you can see it took a while cuz this Server doesn't have many resources assigned to it it would have ran a little faster if I had more processing power and probably a little bit more RAM on the system but it's fine it ran through and did it it's not affecting you guys that's watching the video it's only affecting me cuz this is the computation time that it took all right so let's go on the CD right fast just to get out that directory we don't need to and I'm going to clear again and let's go on and get back in our my SQL database so typees sudo my SQL dasu for user and we want to go back into that root account and d p press enter it'll access for our shudo password boom and it'll access for the password for the rout and if you guys remember from earlier there is no password set for the root account so let's press enter that'll get us in MySQL and then what I wanted to do was show the databases so that same command we ran earlier so show databases semicolon at the end press enter and we'll see that new database is added to our server so we got the employees database so now we can move on to the rest of our examples of how to to manage my SQL all right so the next section I want to cover is managing user accounts in my SQL and this is a very important section because this basically manages how people access your MySQL server even applications how those applications connect to your MySQL server as well as the databases in it and so the first thing I want to do is show you guys how to create a new user account and this allows you to control who has access and can manage your databases and and and so it's not that difficult of a command and it's just like I talked about in previous commands it's almost like writing a sent and so all you got to do is type create and then user and then we have to put the user information in there so we have to put it in single quotes So the name first so let's just name this DB user and then after that quote we have to put an at because you have to say how you want this user to connect to the server and you put this in single quotes as well and this is considered the host name and most most of the time you will see Local Host which is what I'll use currently and that basically means that this user can connect to this my SQL Server via Local Host so you have to be connected to this server in order to access this my SQL database and then after that you want to give it a way to access the server and that is via a password so identifi so you have to type it in properly just make sure you type it in properly because I used to spell this incorrectly a lot of times and I ran into errors but it's identified by and then our password which I'll just put password to three and then close these single quotes and then after that for our SQL statement we have to close it out by typing a semicolon at the end of it and this will create our new user account as you can see long as you don't run into any errors it says query okay zero row is affected you're good to go you created that user account and like I said earlier this command sets up a user account with access limited to the local host meaning that the user can only connect to mySQL server from the same system where MySQL is running and I just wanted you guys understand that because if you want to Grant access from let's say another host and you have to replace that Local Host with the remote machine IP address so let's say you have an application running on a application server and you wanted that user account to connect from that application server cuz let's say this user account is what allows that application to connect to the my squ server you have to put that IP address of where it's connecting from otherwise it won't have access it'll just have access via Local Host like it states now there is a wild cord and the wild cord it will allow access from any host and let's go on and type it out again so create user and I'll create another user account so let's put it in single quotes So DB user 2 and this is a totally different account press the at symbol and then the host we wanted to be able to connect from I'll use that wild cord and wild cord is the percent sign and so that's uh shift five and then we have to close those single quotes and then next let's put identified by and then we put our password so I'm going to use that same password one two and then our single quote to close it out and then our semicolon to close that query out and let's press enter we going run into any issues we typed everything correctly and this is considered that wild cord now let's say you need to change the SQL user accounts password well let me walk you guys through that query right now and it's using the alter command and the way you do it is by typing alter user and like I said it's just like a sentence so alter user and let's say we want to alter that DB user that first one let's say we want to change it to and then we have to type it all out just make sure you type it all out Local Host then single quote and then identified so that's basically all all you doing is typing it out the exact same way we did to create that user but you're using alter so identify by and then we just type in a new password so let's go password 456 and let's close out those quotes and then the semicolon at the end of it and press enter and that will alter the password for it and like I said as long as you don't see any errors you get to go now another command I want to show you guys is how to list out all the MySQL user accounts and there are some built-in user accounts you obviously got that root one and you got a couple more and you guys will see them once I type in this next command so we're going into select statement so select user and then comma host and basically we're selecting two columns within a specific table and this is from the MySQL do user table so let's go on and uh put that cic colon at the end press enter and it'll grab those two columns for us and as you can see we can see all our accounts that we created even the new ones that we just created so DB user is only accessible via the Local Host and DB user 2 has that wild card there and just to show you guys the other accounts that are there so my. infos schema I don't recommend you mess around with any of these Accounts at least until you understand fully what they do but these three accounts are systems accounts and then we got our root account as well which we're logged in using that now the next step is deleting those user accounts so let's say we want to delete the DB user account so it's the same as with tables and databases is the drop command and so drop user and then the user account so we have to put that in single quotes and it's just like when we created it the exact same format so DB user and let me move the cursor out the way and then close out that single quote then at and then open up a single quote let me close it right fast I always do that that's just a habit but Local Host and then we go back to the end and type that semi colon at the end and press enter and that will drop that so let's press the up arrow and go back in here and select those user accounts right fast from that MySQL users table and you'll see that that DB user is gone from the system now let's test out this user account that we still have available on the server and so what I'm going to do is type exit that'll get us out of that roote account and let's run the MySQL command but let's log in using the DB user 2 account and so you basically type it in just like we typed in for roots press enter and then type in our pseudo password for our Josh accounts just make sure you understand that it's going to ask you that for that password twice let's go down and press enter and then now it's going to ask you for the DB user 2 password which we set when we created the account and that password was password with a capital P and then 1 2 3 and press enter and that allow us to log into my so now let me show you guys some of the differences because right now this account doesn't have any permissions to any databases on the server so let's run show databases and then semicolon at the end and you guys should see the difference we only see two databases and these are the schema databases that are built into my SQL and so that lets you know that this user account doesn't have access to any of the databases on there and so I want to go into the grant section of managing these user accounts so let's go on and exit out because we need to add some permissions to this account so they can actually see the databases or do certain things to the database like select statements and all that good stuff so let's go on log back in using our root accounts type in our password which is nothing and let's go on and show that those user accounts again on the system so select user and then host at that my SQL do user database that way we can have a reference when we're typing everything out so what we want to do is Grant privileges to that user account so all we have to do is type Grant and there's a whole bunch of privileges that could be set for a user account there's all privileges you can set certain things like alter they can only alter a specific table they can create tables create databases that's to create privileges but what I'm going to do is just do all privileges just show you guys the base level of actually giving an account and actually that's not the base level I recommend you give the least access that a user needs so if they only need access to a specific database and they only need to let's say select certain things then you want to just give it select permissions you don't want want to give it a all privileges if it doesn't need it for that account but like I said I'm going to just do all privileges just to make it simple just so you guys can see but I recommend you guys look at all the different types of privileges so you can understand them and give the least privileges needed for the account so privilege and make sure you type it in right but that's Privileges and let's say we only want to give them access to a specific database well you put on that database name and we'll use that employee database it's employees and you want to give it access to all the tables so you just put a period and then the store at the end of it that's like a wild cord for all the tables under that database and now we have to put in the user account we want to apply this to so that's why I say it's like a sentence you know what I'm saying Grant all privileges on this database all tables to the user account and so that user account is the DB user 2 and then at the single quote and then wild cord single quotes and then our semiot in and press enter and it will grant those privileges now let's go on exit out again cuz I I just want to show you guys but if we log back in under that user account that we created so password 1 2 3 Center skip back in it and let's run that show databases command and see me coling at the end press enter as you can see we now have access to that employees database let's go on exit out and get back into our root account but I just want to show you guys that's how you grant those privileges now let's say you want to Grant all priv is on all databases let me go back up here to that and all you have to do is put in a wild cord so the wild cord in front of the period and that specifies all databases and all tables under each one of those databases and so we got all those permissions now so we can exit out and then log back into that account and type in our password again and press enter and then let's show databases and this is something you definitely don't want to do I just want to show you guys that you can do it you see how you can see the other SQL database on here like before we couldn't see my SQL or that CIS database now we have access to see pretty much all databases and all tables and let's exit out go back into our root accounts boom and it doesn't make sense to go through everything so I'll just show you guys a few other things let's say you wanted to go employee database and I know one of the specific tables in there is department and so employee Department you only want this user to have access to just that database with that table you only want that user to have access to that specific thing like I said you can narrow this down you know deep so people only have or user accounts only have access to specific ports of a database this can be accomplished by typing out the commands like this and then I won't run this I'll just go back in let's put a store in there cuz I wanted to show you guys something else but let's say you instead of graning all privileges to This Server we can go back in here remove those privileges and we can add a whole bunch of other privileges so we could just do select let's say you want to do a insert and then you can also put like delete and then on you know this database and all tables so you can narrow down like I said even more with this grant command and let me just go to run that and just see what happens and let's actually exit it out I want to see what it looks like uh when we go over there boom I want to see if it removed all the other privileges so let's log in and then run these databases and semicolon boom and we can see all the table so it still has those permissions for all the databases so you might want to run a revoke before that and then run it so it specifies okay that makes sense so it still has that those settings still there for all privileges all tables so let's exit out and now let's walk through how to revoke permissions so this is an important part you want to revoke permissions or at least understand how to revoke voke all privileges for a specific user and we'll go through and do that for this account and let's go back in here and select all the user accounts I always like to do that so I can have a reference when I'm typing out things so I don't mess up misspell certain things but the command is revoke and revoke all privileges on and I'm going actually put store there as for all databases and then period all tables and then from that specific account which is our DB and forgot about our quotes but yeah single quotes in there make sure you type that in and then this this one has the wild cord on the end of it so we'll type it out exactly how it is up there so from that user account on this server so let's go on and press enter and we forgot about our semicolon so if you do forget about it it'll bring it up on a second line you can just type in the semicolon press enter and it run the query and so let's exit out again I know I keep jumping back and forth but I just want to verify a lot of this stuff and that I'm showing you guys the right thing and I want you guys to follow along so you can get used to it cuz once you start typing these commands out you get the hang of it you know fairly quickly so let's go on and type out our show databases again and then put that semic colon at the end boom you'll see we don't have access to any of the databases on the server and then let's exit out and what I want to do is show you guys something else after this let's log back in our root account and Grant some privileges again and I want to get real specific with this account and that way the next command I show you guys will make a lot more sense and so let's grants and then select and let's also do that insert as well and then the delete on the employees database and let's get that departments table within that database and it's not a s now that I think about it it's not a s and then let's hpe two and then our user account which is that that DB user to and then at then a wild card and then the see me coling at the end and press enter and it'll Grant those specific permissions for that user account and now let me show you guys the last command which is the show grants so we can show all the grants that we have for a specific user so and let type it in correctly and we have to type in that user account the exact same way so DB user 2 and then at our wild cord and then the semicolon at the end and press enter and this will show you the specific permissions that this user has and as you can see it has Grant select insert delete on the employees database and then the departments table for that user so it's very specific you can get very specific with these privileges now this next section we'll be diving into queries and this is where that employees database will come into effect and I'll show you guys different types of queries throughout the demonstration so first thing we need to do is Select that database that we want to use so first off let's show our databases and I just want to get you guys in the habit of actually doing this but show databases semicolon at the end press enter that will show us all our databases that we have on the server and what we're looking for is that employees database and that's the one we want to select by using the use command so use and then the database name so employee and then let's put a semicolon at the end press enter and now our database has been changed to the employees database that's the one we are currently using now the core of quering data is the select statement and it retrieves data from tables and you can customize what you want to see now first first off we need to find out what the tables are in the database and we can go back to that command that says show tables and then semicolon at the end press enter and it will show us all the tables within the employees database and now let me show you guys the basic structure of the select statement and all we have to do is type select and then we'll start off with store which is for everything is pretty much the wild cord for all columns within the table and then we want to specify that table so from a specific table so we can pick one in here let's start with the Departments table and then put a semicolon at the end press enter and that will list out all the data within that table and you can do this for each table within the database and so let's look up here we have another table and I'll start with the employees one as well so there's the employee table as well so let's run that select from but instead of selecting the department let's look at the employees and let's put a semicolon at the end press enter and you'll see it'll pull up a lot of data this table has about 500,000 records within this one table and that's representative of all the employees within this organization and so that select statement will pull everything with that store wild cord in there in the select statement now let's say we want to look at the first 10 rows of this table well we can limit the results that are put in front of us by using the limit Clause within our select statement so we can go to the end of this select statement and we can limit the results to let's say 20 we only want to see the first 20 records so we can go down and press enter and this will limit the results and you'll see that we only have 20 rows pulled and like I said this is just a way of limiting it really for you guys that are watching this video so you guys can see less data on the screen now let's work on work on these columns let's say we only want to see specific columns so certain data we only need for whatever we're running this select statement for and so let's go through and let's hit the up arrow and I'll be using the up Arrow a lot and this allows me to run previous queries but let's say we don't want to pull all those columns like I said you can specify specific columns so let's say we only want to look at the employee number and the first name and the last name so we can type empore number and then we put a comma behind it and then first uncore name and then let's let look at the last name as well so lastore name and then we can press enter we don't have to put a comma after that that is the last column that we want to pull and also let's change the limit as well so I can show you guys the information right on the table so it doesn't pull in too much too much more information let's go down hit 10 and press enter and boom as you can see it only pulls in those particular columns that we specified in our select statement so employee number first name last name and there you go within the table so employee number first name last name boom now one other thing with the select statement you can pull in this information in whatever order you want to and what I mean by that is the columns let's say we don't want to see the we want to see the employee ID after our first name and last name so let's go back to the end of it put a comma and let's pull in that employee number and let's pull it in that order we want to see first name last name or actually let's do it a little bit different as well we can do first name name right here so let's put a space and go first underscore name and then put that comma there and then let's remove the first name up here at the front and we want to pull last name first name and then employee number so let's go on and press enter and as you can see it'll put it in that order for us so last name first name and then employee number at the end now let's say we want to filter the data and I don't know how many results will come up let's say if I select a specific name so I'm going just select one just to see and I want to see how many employees have this same last name so bam Ford and I know this is a fake name but there is a way of filtering and this is using the wear clause in our query so we can up arrow and then let's go back in here and put the store let's remove the columns put the store in there for the wild cord from employees and then where we want to type the wear claw in there so let's take this limit off we I'm not going to use it I'm going to see what pulls up but where the last name underscore and we need to put a equal sign in it because we're searching for something specific so we're searching for the last name to be a specific variable and so let's put it in quotes and let's uh paste it in there right fast I don't think you guys can see that but bam forward we want to look for people with the last name of bam for within the employees table so let's go on and press enter and see how many results we get and we get a whole bunch of results so it's 178 rows that have that meet criteria and that's essentially how you filter using the wear claw and then we can filter this down even more let's say we want to look for all the females that have the last name banford so let's put the up Arrow up there and then after the banord we could put another clause which is and so and the gender equals and thenone want to specify female so let's put it in a single quote as well so with a capital f and boom we are good to go let's see see what it pulls up and it pulls up everybody that has the last name of banford with the gender of female so that's super cool and then you could specify that query you know columns if you want to you can go in here and pull in the last name and then the first name and then the gender and then let's go down and pull in an employee number as well so and let's go down press enter and boom it'll pull in just those four columns that we wanted as well as searching for that specific criteria so we got our last name of B of bonford and then all female employees with all the columns re requested to see and then you can modify it you can change this to let's say male we want to see all the male employees like that and as you can see it pulls in you know specifically males for this these results and it's 103 males versus 75 you know females so let me show you guys another option you can run with the select statement and that is the order buy and first off let me run a normal select so nor select store from the employee table and semicolon at the end press enter and I will pull in all the information and what I wanted to do is look at this column right here this is the actual hire date of the employee so let's say we want to order the results by that hire date and let's look at the oldest employee up to the newest employee and so let's run and select again but let's limit it to just 10 and I'm only doing that so you guys can see a little clearer so that shows pretty much the first 10 records or the first 10 results and their hire date and like I said I want to show you guys the order by clause and so we can type that select store from employees I'm going to leave that limit there but the other option is order by and it's two words and what we want to order it by is the column so that's the hire date so let type hore dates and and press enter and as you can see it'll pull up the oldest dates and so that's 1985 January 1st and it goes up from there so these are our oldest employees and the reason we know it's going up because the next employee on a different date that was hired is January 14th so 1985 so it'll go up from there and I don't want to I just want to limit the results so you guys can kind of see it in that order and you can order it by a specific column A lot of times you want to look for that and then also so you can do the exact same thing for this this is another date column but there is a birth date and so we can change that up and not use the higher date but instead of that let's look at the birth dates and this could be for let's say HR purposes or something to that effect let's say you want to look at the birth date of all your employees for whatever reason you can order you know all the results by those dates and then you can also throw your filtering in there with the wear claw so go on press enter boom and as you can see that's our oldest employee right there or a couple of our oldest employees right here that have the same birth date which is February 1st of 195 is what what's that about six employees that have that same birth date and so this is a good reason to show you guys another way of filtering by using that wear clause and let's say we were looking for all the people with a birth date but in just the year of 1950 so we can go through and just search for specific people with a birth date of of 1952 and let me show you guys that break out this order by let's remove it and let's use our wear claw so let's go wear the birth dates uncore and there's another clause in there you can put and that's like and this allows you to search for text that match but also have different things either in front of it or behind it and what we're looking for is that year two and so we have to put it in single quotes so single quotes and then there is a wild cord of the percent sign so anything before which it doesn't have anything before we really don't need to put that there I just wanted to put it there so you guys can see but's say it put that wild cord at the end of it so anything after that as well and then the quotes at the end of it to close it out and let's go on and press enter boom and that'll pull in all the employees with a birth date in within two now let's explore how to add data to a table and first off let's show the tables again right fast so let's go show tables semicolon press enter and what I want to do is look at the Departments table cuz that's the easiest one to work with when it comes to inserting data and let's run a select on the Departments table right fast select store from departments and then semicolon at the end press enter as you can see we only have a couple records so nine rows so let's say we want to add a new Department well to insert new data we have to use the insert into statement and so let me give you guys a quick example we can run through and type inserts into and then we want to insert it into the department table and then now we have to specify our columns so we have the department number and the department name so we need to put parentheses in here and this is to specify our columns so let's start off with the first one so department so dtore number and then put a comma after that and then a space and then the department name so D _ name and now that we have our columns selected where we want to insert data we have to type in the values that we want to put into each one of those columns for this new Ro that we're adding so values and then we have to put it in the same order as they're represented up here so our department number and Department name and we just put those values and we have to put them in a single quote so let's put the first one in which is our department number and as you you can see the next number is Department 10 so let's put D 010 and that's Department 10 and I know you guys can't tell but it's out of order but Department 9 is the highest one so I want to go with Department 10 that's how I figured that out but let's put a comment in here now let's add our department name and let's say this is the technology department so or information technology whatever you want to put in here you want to put it in quotes again sorry about that I didn't put the quote in but put it in quotes and then close out our parentheses anything you open you need to close and we close out that parenthesis and then put semicolon at the end and this will insert that data into the Departments table so let's go on and press enter boom query okay one row affected and now let's select from that department table again and you guys should see our new row which is our technology R so D 0000 and that's thep the technology department now let's say we made a mistake while inserting that data into it well we can use another statement which is a update statement which will allow you to make changes to any of the data within the table and when you're building out this query you want to make sure you find some form of a unique identifier that's why majority of tables will have a primary key or some type of number that identifies that role especially if you don't want duplicates within the table and so our unique identifier is the deportment number because that's different per row that's in the table and so we'll use that as our wear Clause when we type out our update query so let's go on and break this out so let's type updates and what we want to update is something in our departments table so update department and let's say we want to change the technology to help Des so we have to type set and then Department name or the column that we're looking for which is that department name that we're changing so Deport uncore name and we want it to be so we can do equals and we put it in single quotes so let's say we want to change that to help Des so the help desk Department if you you know I'm just coming up with something but we need that unique identifier so where department number equals and we can put this in single quotes this is just basically to search for it but the d010 and then let's close it out with semicolon and then updates that department name where the department number is the 010 so scroll on and press enter and that should make changes to it so as you can see right there it says rows matched and then rows changed and no warnings as long as we don't see any warnings or errors we get to go so let's go down and select from that table again and as you can see we have a help desk department now and that's d010 now let's say we want to delete that row that help Des row we just created so there is a statement you can run and that is the delete statement so let's go on and type this out so delete and then we want to delete from the Department table so we have to type that out and then where the department and I already know what to use I'm going to use department number because that's our unique identifier and plus it's super short and specific as far as the department number and there's no other number like that so you can't make a mistake but department number equals and we use that exact same format so single quotes and then d010 and then put a semicolon at the end but that will delete that Row from the table so boom and as you see query okay and then we can go back in select from the Departments and you'll see that help desk is gone I know it's kind of hard to see because it's not ordered properly but yeah help desk is gone from this table so the next thing I want to cover is primary keys and foreign Keys now first off let me explain what a primary key is a primary key in my SQL is like a special idid for each row within the table it's a unique number or code that helps you identify and organize the data so if you think about it it's just like your fingerprint is unique to you a primary key ensures that each row within a table is different from the others and this makes it easier to find and manage information within a database and let me start off by showing you guys a primary key right fast which I showed you one earlier in the video when we created our first table but let's show you the primary key within one of the tables within this employees database that we're looking at now so let's start off with the employees one by running the describe command and this will basically give you the information about a particular table and let's type employee and then semicolon at the end press enter and like I said this will give you all the information about this table and as you can see right here under key this column right here it says Prime that stands for prime primary so our employee number is our primary key and typically the data type is integer and it's and pretty much a primary key is unique so this number is different for each row within the table now let's talk talk about a foreign key and a foreign key is like a bridge between two tables it helps one table understand information from another table so for instance this employees table and let's say the Department's table well a foreign key in the employees table links to the Department's table showing which department each employee belongs to and it's like a connection that keeps data organized and related between tables and what I want to do is create a foreign key using both of those tables and let's describe the Departments table right fast so you guys can see the information about that table as well and departments and then semic and press enter and it'll describe that table as well and as you can see the primary key is is the department number and so what we want to do is add the department key which is that primary key or the department number to this table and that way we can have a foreign key or a foreign connection between the department as well as the employees so each employee will know what department they work for and so this is a great example and the first thing we need to do is add another column here because you can't use a column any of the columns that are already there cuz they already have data in it so you need to add another col colum to this table and what I typically do when I'm adding a foreign key I'll use the same field name that way you don't get confused with it and so let's go down and alter this table so alter table and then we want to alter the employees table and what we want to do is add a column and I haven't showed you guys this yet but add column and then what we want to do is specify the column name so we want to add department number and then we have to tell the character type and that's why I described this table as well the apartment's table the apartment number is Char so characters and four characters so let's go down and put the exact same thing so chore and then in parentheses we can put the number four in there so four characters and let's go down and put a semicolon at the end of it and this will alter that table boom and we're good to go so query okay you know zero rows affected and then let's describe our employees table again let's go back in here and look at describe employee boom as you can see we have a new column in there it's that deportment numbers it has that same data type which is TR 4 and I put in there uh yes I left it yes as it could be no and the default is no and now let's go down and alter our employees table in order to make that a foreign key and so let's walk through this we can type alter and then table and then the employee table and what we need to do is add a constraint and you can name the actual foreign key and that's what this line actually means so add constraint and let's name our foreign key let's name it uh foreign key let's see uncore departments underscore number and so I just give it something unique forign key so that FK and then underscore deportment number that's the name of our constraint and then let's press enter and then let's add our foreign key so let's go foreign key and then we have to specify that column within our table our employee table and that is the department uncore number and let's go down and close that out press enter and then we have to reference our forign key so the reference is going to be in our deportment table so we have to type references and then deportment and then specify that column as well which is basically the same so department uncore number and let's go down and close that out and then we put our CA colon at the end and let's press enter and it should add our forign key and as you can see that kind of took a little while it was about 53 seconds that it took almost a minute minute because of the 300,000 rows that it has to go through and basically it's just updating that column and marking it as a foreign key location now let's go to and run a quick select right fast so you guys can see that column in action so select store from our employee table and let's limit it to 10 cuz I don't want to see all those rows let's go on press enter boom and as you can see we have that department number column and then also let's describe that table as well so let's go in here and look at our employees table again under the describe and as you can see it is a foreign key and just to explain what this means right here it says Ma so that basically means multiple and this is our foreign key so that means that multiple occurrences of a given value are permitted within that column so that's essentially what that means now next I'll run a quick query and this is a update query it'll actually take me a while to actually run this because I'll do a update and what I'm doing is grabbing random deportment IDs and dropping it into our employees table under that new department number column and this is so I can next show you guys how to work with joins so you can follow along if you want to if you don't this will take some time depending on the resources on your server because it has to go through 300,000 rows and update them so what I want to do is update the employee and what we want to do is set that appartment number within that table and we want it to equal some information that we select basically and this is basically a nested query so to speak so I'm going to just throw this in there and that's to open up the parentheses for the other query and what I'm doing is a select and then we want to select that deportment number and what we want to select it from is the Departments table so let's press enter from Department table and we want to order it because there's other options like I said I didn't go too deep into a lot of these options but there are plenty of options out there I didn't want to it'll take me all day to go through all the options that you could do within a select query I just wanted to show you the base level and then you can build from there by looking at the documentation and what I want to do is use a variable that's built into my SQL which is called random and so r a n d and then we put the parentheses at the end of it it'll basically pull random deportment numbers for each row within this table and so let's type a limit in there and we want a limit to one and then let's close out our top parentheses up here anything you open you must close so close that parentheses and then put our semicolon at the end and like I said this is going to take some time and if you want to just pause the video that way you can copy this and that way you can run this query as well to update your employees table with a random deportment number so let's go on and press enter and like I said this is going to run for a little while and I'll be back and it should tell you at the end of it how long it took me and let's compare times be right back all right cool so that wasn't too long it was uh 35 seconds so 35.2 eight let me know if you beat my time down in the comments but it updated each one of those rows with a random department number and let's verify this by selecting store from our employee table and semic calling at the end press enter and it'll give us our results and as you can see it has a random department number throughout the whole table and so that'll allow me to show you guys joints now one other thing I did was I went back and ran another query that'll change some of the department numbers to null so that way the inner joins and the left joins and all that stuff will show properly so you guys can get a good example of how they work and so let's start off by explaining what a joint is it's like bringing two tables together to find related information and there are three types types of joins and the first joint I want to show you guys is a in joint and it shows only common data between both tables for example if we join that employees table and the Department's table using the department number which is our foreign key within that employees table we'll get a list of employees belonging to specific departments and so it'll exclude all the rols that have null in in it it doesn't have a specific Department in it and let's go on and run a enter join right fast and let's type select and I'll put this stuff on multiple lines so select store from employee and then what we want to do is a inner join between two different tables and the other table is our department table because they both have something in common which is that department ID and so we have to specify that so hit on type on and this is basically a injin in departments on these specific column so let's type employees first which is our first table and we want to match our Department's number column within this table so let's make sure I type it in correctly so Department no and then we want to make sure it equals the department and then department uncore number and then let's close out our query and press enter and this will give you a good clear picture of what it actually does so what it'll do is it'll bring in all the information from both tables so we got our employee the ID we have that date of birth we have that first name last name and then their gender and then their hire date and then we also have that employee ID and we have it twice and this is the one represented within our employees table and then this one right here is the representation of the one in our department table and then ourselves so that's considered a inner joint and basically it's joining on those two columns just matching them up and one other thing I want to point out as you can see the row amount it's actually 300,000 rows within there but some of them are set to null or they have null within that department ID and so they're excluded and so that's what makes the difference between a inner join and a left join and a right join the inner join will only bring in roles that match 100% so if this doesn't have a department ID it will exclude it from the results and let's run this query again right fast and let me show you something a little bit different so we're going to run a enter join again and this applies to all the different types of joins a left join and a right join as well you can select specific columns and so let's say we only want to see the employee number so let's put that there and then the first name as well as our last name and then the department that they work for so this is from that other table that is joined so let's type department and then underscore name so we want to see their employee ID or employee number their first name last name and then the department that they work for and we don't have to put a comma at the end of it and let's go on and press enter and that should pull in just that information that we requested and as you can see you don't have to see that department ID you can just see the department name and that's a great way of organizing the results and using a join to bring in information from other tables now a left join this type shows all the data from the left table which is our employees table and only matching data from the right table so it'll list all the employees and their departments if they're available so this is where we'll bring in those NS because certain records won't have a department ID but it'll still bring in those employees and it'll just show null next to it so let's go on and do a left join right fast and all I'm going to do is go over here I'm going leave everything the same and we're going to type left here and like I said it's going to pull everything from the employees table and it's all based on that department number you know all that good stuff so let's go down and run it again and you'll see that it'll pull in all the records and then you'll start seeing all the NS in there so this will bring in all our employees and it'll also show you the ones that have an employee ID associated with it it'll show that employee name as well and then n for the ones that don't have one so that's what a left join will do it'll basically bring in all the records from the left table and then the information that's related from the other table instead of excluding all the NS or the exact matches and then the right join is the exact opposite it'll basically display all the data from the right table and the matching data from the left table and this will give you a list of all departments and their employees if any and so this right join it may look a little weird let's see let's actually see what happens but let's go right and this may not work too well well and this is based on my mind just thinking through what it will actually show so let's go through and look at it right fast and it's it kind of looks like that inner join because it'll only bring in records from that table that match so that right join won't really work for this example but you can see it actually works it works it pulls in only those that match the right table which excludes all the dos and so that's pretty much how to use joins in my squel all right so let me show you a little bit more Advan of a view and I'll just kind of type this one out so you guys can see but first let me explain exactly what I'm trying to do so if we select our departments table so select store from department and then semic colon at the end press Center boom let's say we want to add into this table the amount of employees that are in each department and so you could do this or accomplish this by using a joint and essentially what we'll be adding is another column here from our employees table that will count the amount of employees that fall under each one of these departments and so me go on to show you guys how to do this right fast and we can type select and then department. department number and this is a way to drill down to all of the columns that we want to use within this table we can specify it by putting the table as well as the column and so sometimes you have to do this because if you leave it kind of generic especially when some of these tables have the same column title you have to kind of specify it like for instance the employees table does have that department number column in it so you want to specify it and then let's do Department table again and then we want to get that department name and we need to put that underscore in there and then now we'll use a built-in variable in order to create our third column here and what we want to do is count and you have to put the information within par parentheses that you're trying to do as far as the variable what it's actually doing so we want to count the employees table and then the employee number column so empore number and now we want to continue out our join so as number and this is what we want to name it so we we're naming this column as num of employees so we type that in and then we want to catch that from the Departments table so boom and now let's build out our join so let's do a left join and that way it'll pull everything from the Department's table and then all the information that matches within the employees table so let's go employee and then on and then we specify what we need to match and then that is the department. department number and then we want that to equal our employee table and then that department number that we have in there soore number and now let's add a little more in here let's Group by Department's number and then departments and that is the department name that we want to do so we want to group it by that so let's go on and close it out this query should run perfectly as long as I didn't type it in incorrectly or type anything incorrectly but we can go back and check if we need to so let's go on and press enter see what happens and yes it worked correct so we got our department number we got our department name and this is pulling the number of employees from the employee table that fall under each one of these departments and I kind of just wanted to show you guys something a little bit more complex and what you can actually do with these joins all right so let's talk about views in my SQL and Views are like a saved query that you can use to simplify your data retrieval and it's essentially a virtual table created from one or more existing tables it doesn't store data itself and so views allow you to see specific data from your table without altering the original data and one thing about of you is it's constantly updated and it's all based on the data in those original tables so if you delete data in a specific table that's associated with a view the next time you run that view it will show you the updated information for that table and it's all based on how you run the query within the view and so let me go down and show you guys a quick example right fast and let's just start with the basic syntax and that is create View and like I said I've said throughout this whole course that these queries sound like sentences so we want to create a view and we want to name that view so we want to name it whatever we want to name it so I'm going to name this one employee gender because I want this view to be specific to the genders of the employees within the table and then we have to type as because this is the query we want to run every time we run this View and so let's type select and then let's look at the first name so of that employees table and then and the last name of that employees table and then also the gender and then let's press enter and then we want to specify what we want this information pulled from which is the employees table let's put a semicolon at the end to close out our query press enter and it will create our view long as you don't see an error that mean the query was okay it ran successfully it created that view now let's run show tables that way we can see the actual View and it looks as though it's a table and I'll show you guys that second so show tables and press enter and as you can see we got our employees gender view there now it doesn't put anything next to it or it doesn't show in any way that it's a view the only way you can kind of see it is if you're in let's say MySQL workbench you'll see it'll show up under a different section when you're connected to the database but we know that it's a view and so you can interact with this just like any table and so let's go on in and type in our select statement and store from and then let's select that view so employee uncore gender then see me coling at the end press enter boom and that's our view and it'll pull in all the data and it's all based on what you put within that query for the view and it'll always show that information the exact same way and then let's say one of these employees is removed from the original table then it will update this view automatically you don't have to update in two different places like update in the original table and then update Within the view the view will automatically update based on the original table and data so let me show you guys how to drop that view so let's run show tables again and it's essentially like dropping a table so let's go show tables and then all you have to do is type drop View and since we know that's a view you know tables won't work so if you if you type drop table that won't work we have to drop view because we know it's a view so drop view then employee gender and then semicolon at the end press enter that'll drop that view for us so we don't we won't see it anymore now let me give you guys another example and we'll look at a different table because I don't like using that employees table because of amount of data that's in it let's focus in on the Departments table and let's just run select right fast store from department and then semicolon at the end press enter boom and as you can see that's all the data within there let's say let's add a column to this table and you guys remember the alter table command so let's type alter table and then department and then let's add a colum so add column and then let's create a email column and let me tell you how this makes sense so let's say each department has an email address that's associated with that department and typically what that's called is a distribution list so each department will have a distribution list and so when someone sends an email to that distribution list it will send send a email to all the employees that work for that department and so that's the concept behind what I'm doing so let's add the column and let's call it emails and then let's specify the type of data to be stored in that column so let's go vure and then let's give it about 50 characters cuz like I said it's it's a email address so it probably shouldn't be that long you know what I'm saying so 50 characters should work so let's go and press enter boom that'll go down in create that column for us and then we can at the tables again the deportment table and press enter and as you can see we have that column now and so let's add some data to this column and I'm building this out you'll see once we get to the view section what I'm actually doing and so let's update the data by adding a email address for each one of the Departments so let's go update the Departments table and let's set our email for the first one which is marketing to equal and we can put those single quotes in there that way it'll add that information in there for us and let's go with marketing atcom and so that's our distribution email address and then we have to run a where in so we can specify where that needs to go cuz right now it doesn't know where to put that information and we want to put that under the marketing department email address so we could go where department number equals and then we want to search for one so d001 and we know that that is the marketing role and so let's put a semicolon at the end and press enter and that should update that Row for us and let's just run a quick select all right fast select from departments boom and as you can see we got our email address in there for the marketing department and so I won't bore you guys with watching me do this I'm basically going to run the last command multiple times just updating and adding an email address for each one of them so I'll be back when this finishes all right cool so I updated the table let's go go on and run a select again so select store from department and press enter and as you can see we have an email address or a distribution email address for each one of the Departments within our organization now let's go down and create another view so let's type create View and then we want to call this view deportment email so that's our department email and we want to set our query as and let's go on and type it out but let's type our select and then let's pull in just two columns within here and let's look at the Departments uncore number and then also let's look at the email and then we want to do this from our Department's table boom and press enter and it'll run that query it'll create that view and so let's go show and then you'll see that we have our department email view there and let's go down and select that view right fast so select store from Department _ email and semicolon press enter and that is our view that we created now here is the full example so let's go into let's actually inser insert some more data into our departments tables like a new Department similar to what we did earlier as far as adding let's say a help test department so let's gooll on and write out a quick query to insert into department and this will add us some data in there so department and then specify those columns that we want to update or input data in we have to put those in parentheses so departments uncore number and then departments uncore name and then we want to use our new column which is that email column so let going to close out our parentheses and then let's type in the values so values and then let's put those in quotes so let's create a new department number Department 10 so 0 one 0 Let's close out those quotes and then let's name let's give it a department name so our department name is help Des we'll just use that same one we used for so help des and then the email address is we can name it let's say help and also let's make sure we add our quotes I don't want to forget that so let's put our quotes in there and then let's put our quotes for our email address as well so let's name it help at abc.com close out our quotes close out our parentheses and then put our semicolon at the end of it and press enter and cool so we added that new line in there and let's run a select of our just the normal table so from departments and just to show you guys that the information has been updated so help des and then it we added in that email address for the help Des department or that distribution list and then as you can see this is our view so we updated it just in our Department's table and so with our view I'm going just run this same select query for our department email View and you'll see that it'll update based on what's in the original table so we don't have to updated in two places that's the whole purpose of me showing you guys this exercise so as you can see we got the help Des change that we made to the original departments table so hopefully that gives you a better understanding of how views work and it's the same way let's say we removed that column out of there it will update our view as well all right so the next thing I want to cover is indexes and a index is basically a quick reference for your database and it helps speed up data retrieval it's a structure that organizes data in a way that makes searching for specific information much faster so think of it as a index in a book that tells you where to find a particular topic without reading the whole book so let's go through a few examples right fast and first off let me show you guys how to see the indexes that are currently there for a particular table and what I want to do is look at our employees table so all we have to do is type show indexes and then from from a specific table which we will look at the employee table and just put a semicolon at the end press enter and this will show you all the indexes and there currently are two indexes on the employees table currently and the first one is associated with the primary key and the second one is the foreign key but let's say with this table we do a lot of searches by last name well we could create a index to structure and organize that data to make it searchable a whole lot faster and so let me give you an example of creating that index and all you have to do is type create index and then you want to name that index and what I typically do or the format I do X and whatever column I'm working with so last name is what we want to search so or what we typically search a lot and then now we have to specify the actual column within the table we have to specify the table and then the column so all we have to do is type on our table which is that employee table and then we have to specify the column and we put that in parth pares so we can go back in here and type our column name which is last name and then put a semicolon at the end press enter and that will create our index it takes some time depending on how big the data is cuz it has to structure it but as you can see that query ran successfully and it created our index now let's go back and run these show indexes for employee and let's see our new index that's in there and as you can see it's the idx last name that's the name that we gave it and then it looks at that last name column within the table and so let's just run a quick query right fast and you guys may or may not see a difference when we last ran the select on the employees table because it's all dependent on the amount of data that's in the database whether this will benefit you or not and then how often this information is updated as well as searched so let's type store and then from employee and let's put a wear clause in there so wear last name equals and let's look for a employee last name of bass I know that's one of the names in there so let's type ba a a z and then close that out and then semicolon at the end press enter and as you can see it took no time to actually find that it was less than a second to actually pull all the data for a bass and that's what it'll do it'll increase that speed on searching within that based on the column that you specify in your index and so let's remember that so it only to it took less than a second to actually search for all the employees with the last name of baz and so let's drop that index and this is a good opportunity for me to show you guys how to do that and essentially all you have to do is type alter table employee and then we want to drop our index and then we have to specify that name so id id X and underscore last name and it'll drop that index for us so let's go on put a semicolon at the end press enter that'll drop that index and then now let's run run that same select where we search for the last name of bass and press enter and it took 10 seconds so you'll see you know a difference depending on how much data it is within that table and so there are some downsides to using a index for instance there are slower rights so let's say this table is updated a lot then it slows down the insertion or the updates and the deletion operations because they must be maintained alongside the data and I mean the indexes they have to be maintained and then also there may be an issue with storage because these indexes they consume extra disc space and so if the database is super large then those resources could be significant and also the complexity of these indexes you know what I'm saying they require regular maintenance and then also like one of the other cons so to speak of using indexes is the memory usage it does use a lot of memory and that can affect effect these servers performance so you have to think about that when creating these indexes and be very strategic all right so next I want to talk about subqueries and a subquery is basically a query within a query and it's a way to retrieve data from one table based on the results of another query and for example you can use a subquery to find employees in a specific Department by first querying the department and then using the results to search the employees table so let's go through a demonstration right fast and I actually wrote out a query for this and I'll show you guys that demonstration now and let's just type select and then store from our employees table and so this is our main query this is the main query that we want to use and I'll show you guys the subquery in a second but I'll use it under the wear Clause within this query so let's go select store from employees and then where department number equals and this is where we we will put our subqueries and we have to put our subquery in parentheses cuz it's a totally separate query and then let's type another select and then department number and then what I'll do is pull this information from another table which is that Department's table so so let's talk that in department and then where Department name equals sales let's look for the sales department and let's put that in quotes single quotes and then let's close out our query and it should work for us so let's press enter both as you can see it pulls in all the people from sales so that's super dope right it goes through and searches and finds all those people with the Department name of sales and it's all based on the department number so that's essentially a subquery example and I only wanted to cover like one example because subqueries can be get very complex well queries in general can get very complex and then when you start adding subqueries into the mix it takes a little time to build out those queries queries and so I want you guys to practice writing out queries as well as subqueries based on documentation that you look out there for my SQL or SQL queries all right so next I wanted to cover store procedures now store procedures are like a reusable script that contains a series of SQL statements and it's like creating a custom function that allows you to perform specific tasks especially if they're repeatable for instance if you guys remember the query we ran during the join ction and I'll actually paste it in here so you guys can see but this is the select statement that we created in order to pull the count of employees based on the Departments and so let's go down and run it right fast so you guys can get a picture of what what I'm saying so department number Department name and then the amount of employees that are in each one of these departments and so let's say we had to type this out a whole bunch of times you know this is something that we reuse all the time like every day or so well it'll be a pain to have to retype this every single time so we can create what is called a store procedure with this query built into it and so let me give you example of how to actually do that so the first thing let me show you guys something right fast so if we type select store from departments and I'm just showing you a basic query so you can understand what I'm going to show you so at the end of each SQL statement or query you have to put what they call a delimiter and the delimiter by default is the semicolon within SQL but within our store procedure while we creating that procedure we have to put this query in the middle of another query and so in order to end the creation of the store procedure we have to change that delimiter so it doesn't mess up our actual query that we're going to put in the inside of it and so I'm not going to run this query let's go on and back it off but I'm going to show you guys how to change the delimiter and all you have to do is type delimiter and then we can specify what that delimiter is most people use these slashes that's typically what you'll see in documentation but there's others you can use but I'll just use the slashes so four slashes let's press enter and that will change our delimiter for ending a SQL statement now let's go on and type out our store procedure and the way you create it is simply create and then we want to create a procedure and then we want to name that procedure so I'll just name it employee accountor by department so department and then at the end of the name of the procedure we have to put C's and so this is essentially like a custom function so after this we have to continue writing but we have to specify the beginning of our query that we want to run within our store procedure so by specifying you have to type begin and this is just a designator to let SQL know that this is the beginning of the script and so let's press enter and then let's paste our query in there boom and and right now if we didn't go in and change our delimiter when I press enter it's going to to finish out this this whole statement that we wrote out which we did don't want it to do that's why we changed the delimiters so just like we put the begin here of where our select statement starts we have to put an end so if we didn't change that delimiter we wouldn't be able to end the statement so let's press enter now and as you can see it didn't complete out our SQL statement and so let's go down to type in and then our slashes and that will allow us to end this statement that we wrote so let's go on and press enter as you can see query Okay so it ran successfully it created our procedure now let's go on and change our delimiter back you don't want to forget to do this otherwise you're going to be running other queries and you'll be like what why is it not running properly and that's because the delimiter hasn't been changed back so let's type delimiter and then the semicolon like what is typically used for our queries press enter and that'll change our delimiter back to that semicolon now let me show you guys how to call that store procedure that we have cuz it will use this name right here so employee counts department so I'm going to copy it right fast and then all you have to do is type call and it's like calling a function so call and then the procedure name and then let's go to put the semicolon at the end and press enter and that will run that query for us and so that's a great way of running complex queries you know that you have to use multiple times or you run daily or multiple times a day this is a great way of running those queries by creating a store procedure and then you can call it store procedure and plus it's less text you have to type and so lastly let me show you guys how to drop that store procedure so all we have to do is type drop and this will basically remove the procedure so procedure and then the procedure name and I'm going to paste it in here but you don't need these uh parentheses at the end of it that's mainly just for the function or calling it and then when you create it you have to put those parentheses there but all it is is a name so you want to just drop that procedure by its name without the parentheses so let's go on and press enter that will drop that story procedure and as you can see if we call it again it'll say error because it does not exist anymore we deleted it all right so the next important thing I want to show you guys in my squel is triggers and triggers is like a set of predefined actions that automatically occur when specific events take place within your database and this could be things such as inserting or updating or even deleting data and so let me go down and show you guys a quick example and I have to go through a couple processes one of the first thing I want first things I want to do is create a new table I want to show you guys how to create a audit log and basically the purpose of this is every time we make an update to the employees table I want this information tracked within another table and so let's start off by creating that table so we can type create table and then let's name this employee Audits and then let's put our information for the table in there let's start off with a audit ID boom and then we can make this Sager so int and then let's make this Auto increment so autocore increment and this is basically to auton number each row within our table and then let's make this the primary key boom and then let's put a comma there and let's create our next column and let's start with the department number because I want to do this based on the department so let's say and this is the only example I could think of to show you guys a trigger so let's say an employee is assigned to a different department and we need to update and let's say we want to track those changes well we'll put this in here so it can track the new department for that user and then this will be at integer and press comma and let's get the next step so let's go with our action so this is the actual action of what happens as far as the type of query that's running and like I said I want to show you guys after an update so let's go action and then vour and let's make the number of characters around 50 boom just go on put a commment there and then lastly Let's uh put the action dates so this will track the actual time that this change happened within that table and then we can set the type to time stamp that's a good one to actually use and let's press enter and then close out our parentheses as well as the semicolon press enter and that should create our table for us and let's go with a select right fast just to check it out so select store from our employee _ audit table and let's close it out with the semicolon press enter and it won't show anything cuz there's no data in there yet but our table is created so now let's go on and create our trigger and we have to change our delimiter again because of the way I'm writing this trigger so let's change it to our for slashes again and press enter and the way you create a trigger is as follows so so create trigger and then let's name our trigger so we can call this employee uncore Audits and then underscore we can just specify that it is a trigger and let's go on and type out the details of our trigger so after an update on the employee table and then for each row enter and then this will be the beginning of our statement so let's type begin and press enter and then let's do our insert into our employ employee audit table and then we want to update specific columns within that table so let's start off with the department number and then the action we want to update the action column as well as our action date and let's close out those parentheses and now let's type in our values so values and there's a built-in way of looking at the change like you can type new and this will show you the new information that's being inserted into the table or you can also look at old which is the current data that's there but we want to look at the new data we just want to track that new data that's being inserted into the table so new and then it's coming from the Departments number and then also let's say we want to just we know this is a update trigger so let's say we only want to put text in there that just says update so we can specify you know specific text within a column so we could just put updates so each one of the triggers that will happen it will put update in there and that's a way of tracking our updates then also the action date we can use a built-in variable which is now and I'm going to type it in all cap so you guys can understand it but it's just a built-in function that you can use and that will bring the current time and date and put that into our field so let's close out our main parentheses and then let's go down and put our semicolon at the end CU that'll end out our insert query and let's press enter and just like we had to type begin we have to type in that was the whole purpose of changing our delimiter so let's type in then our delimiter that we have currently set which is the for slash4 slash so let's go and press enter boom so that took and that created our trigger now let's change our delimiter back right fast and we want to change it back to our semicolon so when we run our following queries they'll work properly and so let me show you guys how to show the triggers so so it's a very simple command all you have to do is type show triggers with an S put semicolon at the end and this will show all our triggers right there as you can see that is our trigger that we just created so the employee audit trigger and it gives you all the information about the actual trigger and if you have other triggers they'll be listed here as well so now let's find some information to update so let's type select and let's just select some information from our employees table so let's type that in semicolon at the end press enter and maybe I should have did it this way let's throw in the limits after that so we can just pull up the top 10 records that will be displayed so this is a easy way of finding something to update so let's say we want to update this first line and it's all based on the department number like I said once you update the department number it will track that in our audit table so let's go on and run a update query so let's type updates and then employee table and then let's set our department number because that's what set Sofi trigger so department number and then let's change the department number to let's say Georgie forcillo works for the help Des department so let's go and throw the help Des ID in there department number in there so it's 010 and then close that out and then now let's do our wear Clause so wear employee number equals and let's go to and grab their employee number right fast and drop that in there and put our semicolon at the end and let's go to and run our update query and looks like we have an issue hold on let's see and I believe I made a mistake when I created the table so this is a good way of showing you guys how to alter the table right fast and let's describe our employees table and I just want to see what the character is set for and yeah it looks like I made the wrong setup for the audit table that we created and yeah we can look at that right there just by describing this table as well so it's our audit table so employee Audits and let's check this out and it looks like yeah I put the department number as an integer when it should be character for so that's the mistake I made and so I'm glad this actually happened so I can show you guys how to fix issues like this or track them down because as you can see this error it just kind of gives you it gives you information you know don't get me wrong it gives you information but a lot of times you got to dig sometimes the errors are vague and you got to kind of dig through where the issue lies and so let's alter that table right fast that employees audit let's go right here just type in the front so alter table and then employees audits let's delete that off and then let's modify a column and this is changing the type so all we have to do is specify that column which is our department uncore number and then we can specify what we want to change it to and so that's why I described this so I could just typeing in you know properly so Char and then put four right here and let's put the semicolon at the end and move the mouse out the way so you guys can see but this will alter our table and now let's describe it again and that way we could see the changes and boom we are good to go so let's go on and make our changes in our employees table again so let's scroll up and let's see where we can find that change yeah this is the change we want to make so update employee set department number to d010 where employee number is 10,000 so let's press enter and we shouldn't run into that area yeah we don't so let's select our our employees audit table so select store from employee audit so you guys can see the information that's presented to that table and that lets us know our trigger actually worked and as you can see it pulls in the department number the new department number and see this is just a basic understanding or basic concept of setting this up you can set up Tri triggers that are way more complex like for instance you can track the old data as well as the new data you can create more columns in here and that's typically how you'll see it in a production environment it'll track old and new changes that way you can correct those changes if need be by looking through the Audits and now let me show you guys a little bit more of this audit log or this trigger in action like a while back I created a query that would go in and randomly insert departments within all of the users within the employees table and so let's run that again so you guys can see the rows that are inserted throughout this table and I know it'll populate this audit there'll be a whole bunch of changes or updates within this table but I just want to show you guys how these triggers actually can be very beneficial so let's hit paste let's paste it in there and this is something that I wrote a while back and I'll just break it down for you right fast but it's basically going to update the employees table it's going to set the department number to equal and then we have a nested query and this is basically select the department number from Department table and then we ordering it by random so this is a built-in function and then we can limit it to one and that will do that for every rle within the employees table and so let's go down and press enter and let this run and then we'll look back at our audit table up here our employees audit table once this completes and we'll see all the Okay cool so our update query has ran successfully as you can see it made a couple changes or not a lot of changes and so let's select our audits table again and press enter and let's see all the changes as you can see it's like 30,000 cuz it went through every record you know what I'm saying and this will keep going as you run updates on that table so let's say you make a lot of changes depending on the department it'll list them all here and that way you can trace back any issues that may have been made in the table unintentionally all right so after showing you guys these store procedures and triggers I wanted to show you guys another command within my SQL which is process list and this is essentially like a to-do list for the database server and it keeps track of all the tasks the server is currently working on and each task similar to a operating system is called a process and can include things like executing queries handling connections or performing updates and so let me show you guys this Command right fast and it's very simple all you have to do is type show process list and then semicolon at the end and this will show you all the process is currently running on a server as you can see it breaks everything in columns so you got your ID you got your user you got your host the database that's associated with that user so to speak the commands the time State you know information about the server so as you can see it's pulling in that process right there show process list that's the query that I just ran using my root account so you can see pretty much any and everything that's going on on the server and I wanted to let you guys know this an important command to understand because it helps you manage all the processes and then also understand the performance of the server okay so the next thing I want to cover is MySQL dump and MySQL dump is a built-in utility that's included with mySQL that allows you to make backups of your entire database and you can also get very specific with it and back up just specific parts of the database as well and this tool will help you save your data so you can restore it if something something goes wrong or let's say you need to move the database to a different server that provides more performance for the amount of data that's included in it well this allow you to do that as well and so just to kind of cover it MySQL dumps it takes all the data and structure of your database and save it into a file and it's typically a SQL file so if you guys remember earlier in the course I imported that test database it had the extension of SQL so I'm sure they created that test database for others to use using MySQL dump and so when we get into the examples I want to show you that it's is like very super easy to use and you can run it right from the command line and another thing you don't have to stop like my SQL in order to create the backup it'll work while the database is running and a super cool part about it it will ensure that your data's Integrity is maintained during the backup process and so let's go through a couple examples right fast and what I need to do is exit out of my SQL because like I said it's a separate utility that comes along with my SQL and so let's go down and back up that employees table with all those changes that we made so let's go with mySQL dump and then we have to specify a user in order to connect to our mySQL database and we want to use our root account and then we can put- P for our password and it's similar to how we logged into it and also I forgot about this we have to put pseudo in there so we can connect to the database and now let's do the employees database and then let's specify the file that we want to name it so we have to put the right arrow just like when we import it we use the left arrow pointing to the left we need to use the one that points to the right because we're grabbing all the data or the database from within the SQL server and dumping it to a file and this is the structure and everything associated with it it even includes the data that's within the database and so let's name name our file EMP and then let's goore backup and then it will store it in the current working directory we are in which is our home directory so that's fine for now but you can specify another directory by typing out the full path we'll just roll with it like this and press enter and then it'll ask us for our pseudo password because we had to type pseudo and then there is no password for our roote account so let's press enter and go down and Export or dump our database and as you can see it didn't take long at all and so let's LS our current working directory and you guys will see the employees back up and then let's put this in human readable form so you guys can see the size of the actual database file so it's 176 megabytes that's how big the actual database is and let's log back into our database right fast so P sudo my SQL and then our user which is roots and then- P for password press enter and press enter no password and let's go show databases and semicolon at the end press enter it'll show us all our databases so what I'm going to do is drop the employees database and we all remember how to drop a database and all we have to do is type drop and then the database and then the database name we're going to drop that employee database and semicolon at the end and let's go enter and that will delete that database and so let's run these show again right fast and we'll see that that database is gone and we're like oh crap made a mistake I did not mean to delete that database well one good thing about this we created a backup of our database now in order to restore the database we have to have a database within the MySQL server with that previous name and so let's go on and create a database and this will be a empty database so create database and then you want to name it the exact same name that it was before so employees with a comma press enter just go through and show the databases again we'll see that we have that database there but it doesn't have any data or tables or anything in it we're going to get that from our backup file so now that we have everything set up we can exit out of my SQL and let me show you the command to restore the database so P sudo my SQL and then we have to put our user in there so roots and then- P for the password and then we have to specify that database that we just created that empty database because it needs somewhere for it to go it's not going to just create that database for you it's just going to modify that current name database that we have in there to represent what we had previously within the backup so now we just put that left arrow in there just like we did when we imported before and then we pull in our employee backup and press enter it's going to ask us for our password we don't need a password and press enter and it'll go through and import all the database all the tables and everything within that database and then we'll verify this back up once we complete this I'll go through and show you guys the tables that all the tables are restored and pretty much everything the data is there as well by running a couple select queries so I'll be back when this finishes all right and so the import is done let's go in to my SQL and check our database and just verify that the tables are there and we can also run couple queries to see if the data is still there and so first off let's uh show databases which we know it was there before cuz we created it before we logged out of the system and imported our file now let's type use employee database and press enter and then now let's show tables and semicolon press enter so as you can see we can tell right off that we have our full database back so like for instance I created that audit table for the auditing so it has a trigger associated with it so if any updates to the employees table departments column it will track it within this a audit table that we created so that's a Telltale sign that we have our database back and that's different from the original database that I installed on the system and actually let's just run a select on that table right fast so you guys can see that that information is back and so let's go from employee audit and semicolon press enter as you can see it has all those records there it hasn't changed so all our records are back as well so we can do the exact same thing by checking other tables if we need to like this is the employees table and we know from the original database we didn't have this column in here with the Department ID and so that lets you know that this is the database that we dropped and there you have it you have successfully completed my MySQL course and I hope you've gained a valuable insight into this powerful relational database management system so whether you're a beginner or already had some experience I trust that you're now more confident in working with mySQL and also MySQL is a fundamental tool for anyone dealing with data and your new found knowledge will be an asset in various domains from web development to data analyst and remember the best way to become proficient in myql is through practice and so keep experimenting building and refining your skills with determination and dedication you can become a master of my sequel so thanks for choosing my course I wish you the best of luck in your database Endeavors and may your MySQL journey be filled with success and of course keep protect okayunlock the power of databases with this MySQL course for beginners whether you're new to databases or looking to level up your skills this course has you covered you'll learn how to create manage and manipulate data using MySQL you'll discover the secrets of keys learn to write queries like a pro and automate tasks with triggers no jargon just practical knowledge Josh from keepit techie teaches this course he works as a SQL Server database administrator so he's the perfect person to teach this course what's up guys this Josh from keep techi and welcome to my comprehensive course on MySQL now MySQL is a relational database management system and in this course I'll try to explore the ins and outs of my SQL on Rocky Linux which is a powerful combination for managing your data effectively now my SQL is not just any database it's the fundamental Port of the lamp stack which stands for Linux Apache MySQL and PHP and then also the limp stack which is Linux engine X MySQL and PHP and these Stacks are widely used for Content Management Systems like WordPress jumer and various other web applications and I'll start with the basics covering the installation login procedures and a little bit of database management so whether you're a beginner or have some experience this course will help you master my SQL on this fantastic Linux distribution so let's head over to to Rocky Linux 9 server and get started with the installation process okay so here we are logged into Rocky Linux 99 server and to install my SQL we'll follow these simple steps and I just brought up the OS release information under so you guys can see that I am on Rocky Linux 9 which is a one toone comparison to Red Hat 9 so let's go on and clear and first let's ensure that our system is up to date and we need to run the following command to refresh the package repositories and apply any available updates and that command is pseudo dnf and update and we'll wait for this to finish but this will basically check to see if there is any updates and if there are it'll upgrade those packages on the server and as you can see we don't have any updates so we're good to go I believe I updated this before I started this video so now to install my SQL all we have to do is type pseudo dnf install and then my SQL server and I'll just tab it out it'll pull up the latest version of my SQL so that's basically the version number and all that stuff for this actual package so let's go on press enter that'll go through and resolve all the dependencies for MySQL as well as the package for MySQL so all we have to do is type why for yes we want to install this package and use the amount of space that it will use use on the operating system so scroll on press enter it'll go through like I said download all those dependencies as well as the MySQL server package and then install it on the system so I'll be back when it finishes all right so the installation is complete and now we need to check the MySQL Services status and we can do that by using the system CTL command so let's type pseudo system CTL and one thing I noticed on Rocky Linux it doesn't start the service after the installation or part of the installation process similar to like you buntu typically when you install MySQL it'll go down and start the service and you may have to go in and enable the service but most of the time it starts the service right off but on Rocky Linux or rail it doesn't so system CTL and then status and then what we want to do is check that my SQL status and you'll see that it's not started boom and it'll say right here where where it says active it says inactive so right now that lets you know that it's not started and then also Al the enabled portion of it this is essentially for when you reboot the server itself that service will store it up right now it's disabled we want to enable that so it's two commands we can run right fast we basically have to run the pseudo system CTL start and this will start the actual service so let's go and press enter all right and so we can run the status command again and this will show you that that service is running now so as you can see it says active and running but we still need to enable it so let's let's go down and press Q that'll drop us back down to the terminal again and we get up arrow and then let's remove the status and type enable press enter and it'll go through and enable This Server so each time you reboot your Rocky Linux server my SQL will stored and so let's check the status again we can go back in here and look at it and this is what we're looking for where it says enabled that lets you know it's enabled so that service is stored up and so that's pretty much it we've successfully installed my SQL and now it's time to show you you guys how to log into it and you use a command called MySQL and you also want to put the user account and if there is a password then you want to put that password there as well or the option to ask for a password but since this root account that's on the system of the default root account it doesn't have a password you can log right in and so let me go down and type the command in and we are using a pseudo account so we have to type pseudo in order to run the MySQL command so let's type pseudo MySQL and then we need to put our user so we have to put our option in there- you and then the username so I'm going to use that that root account like I said that's the default account that's there after installation and it doesn't have a password on there but we need to put- P at the end of it so it will access for the password and you'll see once we get past this step It'll ask for the password but we don't have to put a password in because there is no password set for it now I want to point this out to you as well though you want to make sure you follow best practices and create a dedicated user account for your projects you don't want to be using this root account with no password on it so go on and press enter It'll ask you for a password it says enter password and since we ran pseudo it normally will ask you for a pseudo account so if you you may see it asking for the pseudo account so you want to type that in of course but then it'll pop up with this enter password for the MySQL account so I just want to know let you guys know that the way you're seeing it on mine it may not show up on yours you might have to type in a password for pseudo and then press enter because there is no password so let's press enter right fast and that'll get us into our MySQL server now that we're in let's explore some basic MySQL commands and functionality now once you're inside the MySQL prompt which is what it's showing right here with the my SQL and then that right arrow you can begin executing commands and so let me walk you guys through a few ones the first one you need to know is the help command so if we type help and then every command pretty much every command you have to type a semicolon behind it similar to typing out a query in order for it to run that statement for you so help will basically give you a whole bunch of information on how to use my SQL if you scroll through here you'll see a Le list of all MySQL commands and some of these commands they use that left slash in front of it and that's what I was going to show you guys as well after this by typeing help you know as one word and then the semicolon there's another way of actually getting to the help and that's the back SL and H but the other ones are there and they have an explanation of what they actually do and I won't go through them all cuz I typically don't use a lot of these commands these my SQL commands right here but I just wanted to show you guys where to get that information from if you need it now let me go to and run that back slash and then H and that way you guys can look at the same thing and it looks like it didn't change because it just basically ran it again and display the same information so we are good to go now the first thing I wanted to show you guys is how to to find databases within your database management system and so I'll show you guys that command now and it's essentially the show command so all we have to do is type show and then we want to type what we what we're looking for which is databases so databases with an S and then semic calling at the end of it and this will show you all the databases that are currently on the system so let's go down and press enter and what you'll see is some built-in databases for the database management system like information schema like the performance schema this my SQL database as well as the cyst database and I recommend you don't touch any of those those are vital to the system running properly but this is how you actually look at all the databases that are on the system all right so now that we know how to look at the databases that are currently on the system I wanted to show you guys how to create a database cuz this is like one of the most important steps after that because we need to understand how to create those databases so all we have to do is type create and a lot of these commands you'll you'll start remembering them almost like a sentence cuz that's essentially what it is like show databases simple you know create database so we want to create a database and that's the command right there so create database with no s cuz it's singular you know so no s is needed and then the database name so we can name it whatever we want to I'm going just do DB name and then put a semicolon at the end of it and go down and press and it'll create our database as you can see query okay one row affected so and what row they're talking about is the built-in table that list out all the databases that are in the database management system so we can go back and look at that show database command show databases command and we'll see that that new database is there is called DB name that's what we named it now probably the next question you have is how do I create tables within the database that we just created and I need to show you another C command beforehand and you need to specify the database you want to run queries against by using the use command so like I said almost like a sentence you just type use and we want to use a specific database and which database we want to use is that DB name and then you can end out your sentence with the semicolon and press enter and this will make a change so that we are using database name for the following commands and you need to do that every single time so my SQL will know what database you're creating things with or even running other queries like select or update it needs to know what database is working with so you have to use this command in order to change it to the database that you want to work with and now I'm going to show you guys how to create a table and like I said it's almost like a sentence so create table and then we want to create a table name so we have to name that table so I'm going just name it employees now this is a little bit more complicated because it requires multiple lines so after this we have to specify each one of the columns within that table and you can press enter in here and that's why it's important to understand the purpose of that semicolon that closes out your query until you type that semicolon it will not close out the query so let's go down and press enter boom and as you can see it'll go to the next line allowing you to type some more information and we want to go through and create each one of the columns within our database and so the first one is employee ID a lot of times when you create a table you want to create what they call a primary key and this is a unique identifier for each row of data within the database and that just keeps things a little bit more cleaner in the database as well as a way to make sure we don't have any duplicate information and if the information is duplicate in other columns it will still recognize it as a different record so the next thing we want to use is integer so this is an integer meaning a number so and then also with our primary key there's an option you can put here called autoincrement and this will allow it to create numbers in sequence so 1 2 3 4 5 6 7 89 10 it will you know keep going as new records are added to the database or to the table and so let's type that in right fast so autocore increment and then also this is a primary so we have to type that out as well and then we have to put a Comm at the end of it that'll let us know that that is one of the columns within our table and then we can press enter and we can type in our next column and let's start off with first name and just like the init we have to specify what type of characters are in it the init is integer like I said that's a number well this is a variable you know all those letters and so we have to type V chore and then we can specify the amount of characters that you can type within that and so the default is like 255 you'll see a lot of people put 255 so that's a good length for the amount of characters that can be put into it and then we can also specify that this column should always have information in it you don't you can't create another row unless information is in that field within that column so let's go type not null and that's what that mean null meaning not empty or blank and so we can type a comma at the end of it and let's go on and type in our next column which I go with lastore name and then same thing so V chore and then 255 and we can close that out and then not all as well for that one hit a comma and let's do one more and that'll be the birth date of our employee so that's just some information we want to put in there for this table or require for this table and then we can put dates and then we could leave it not and all off because we don't need the birthday I'm going just I'm just showing you guys that and then once it's the last one you don't have to put a comma at the end of it but we do have to press enter and close out these parentheses if you open a parentheses you need to close it so let's go on and close it right fast and then what is the last thing I'm sure you guys remember but is the semicolon that will close out your query and so all of this information here it will create each one of the columns within that table so it'll create the table and then it'll create all the columns within that table here so let's go and press enter and as long as you see query okay zero rows affected that means it successfully created that table and so now we have that employees table within the database we can check the structure of it to verify that we typed it in correctly by using the describe commands so let's go on and type describe and then our table so it is the employees table and of course at the end of each statement you have to put that semicolon at the end of it and this will give you information about this table so as you can see the fills we got our fills our different columns within the table and we got employee ID then it's uh integer and then null and then this is the primary key and then also this is auto increment you'll see that listed there and this will break down basically the structure of this table so this is what that describe is for now I won't go into adding data yet but I'll show you guys how to drop this table and with my SQL or with SQL the actual language that we're using drop is actually how you delete something within the database so let's say we want to drop that employees table that we just created so all we have to do is type drop and then we want to specify that we want to drop a table and then the table name so like I said employees and then type a semicolon at the end and this will drop that table and I forgot before I drop that table I wanted to show you guys something now so I'm going just press uh up arrow and find that create table employees table and I'm going bring it back right fast so let's press enter and of course that's one way of showing you guys you could type this out without the separate lines it's just easier to see if you put it on a separate line you see what I'm saying but you could type it out just like that the way it's listed there so I wanted to show you guys the show tables command and let's go on and type that in so show tables and like I said we're still using that DB name database and so let's go on and press enter boom and that will show you the actual table that's there and now let's go back and remove that table I'm going to drop it again so drop table employees boom and then we can run that show tables again and this will show you that there are no more tables on there so I didn't want to move forward without showing you guys that now the last thing I want to show you guys is how to actually drop a database and essentially that's deleting the entire database just like we deleted the entire table we can delete the entire database so let's go on and type that command in right fast but it's simply drop and then database and like I said it's just like a sentence and then we need to specify that database name so DB name and then semic at the end of it press enter and that will drop that database and now we can type show databases and then the semicolon at the end of it and press enter and this will let you verify that that name database is gone so you won't see it anymore so we get to go now be very cautious when running that command because it will remove all tables and data within that database so just be very careful with it okay so moving on to the next part of the course we need to have a database with lots of data so you can see examples clearly and I'm going to import a test database name to inlo and this is on GitHub and let me switch over to my browser so you guys can see the link and if you want to do the same you can find a link in the description to import the database and this database was created by bik base on GitHub and like I said I'll have the link so you can get here but let me just show you guys the directory structure so so they have a MySQL dump that you can import and this will generate a full SQL database for you it's called the employees database so if we go under MySQL you can see they have a postgress as well if you need a PO example but MySQL is what we'll be using and they have a data full or a data set full data set large data set small in the full we'll have the full amount of data which is the one I recommend you guys use they also have a large which is a smaller version of the f doesn't have as much data in it and then the small is kind of like a sample size of the data so you can use that as well it really doesn't matter just pick which one you want I'll import the data set full essentially what we're going to do is install this data dump it's a SQL dump and I'll get to this in a later part of the video where I show you guys how to back up databases and import databases or restore databases but this is the five whee import and let me go down and go here first and what I want to do is clone this repository to the server so we're going to copy that link and then go back to our Ser server terminal and exit out of my SQL so we could just type exit and this is a good time for me to show you guys how to exit you could just type exit and press enter and it'll get you out of my squel and and now that we back at the terminal we can go down and install get cuz we need get installed so we can just type P sudo dnf install and then get and press enter type in our Pudo password boom and it'll go through install G as well as all this dependencies and we type why for yes boom now go on and install get on our system and now we can get clone that actual repository and we don't need to change directories we can use our home directory and so all we have to do is type get clone and then we can paste that link and press enter and it'll clone down that actual repository for us and now we can import this test database so first thing you need to do let's LS right fast I show you guys the directory but we need to CD into that directory and we want to go into my SQL and then we need to go into the directory of the location of the size of the database that you want to in install so let's T it out right fast but there is that full like I said so that's the directory we need to go into now if you want to go into the other ones you just select large or the small one as well you can select that one as well like I said I'm going to do the full boom press enter and if we LS this directory this will show you all the files that are in there and it's so allow you to see the file that we want to import and we need to use Pudo cuz we're running MySQL under a regular account which is that Josh account so we get sudo my SQL and then what we want to do is use the left arrow and then the actual SQL file that we want to import or the database that we want to import which is that employees. SQL file so scroll on press enter this will take a little time because the full is pretty big it has about 300,000 records in at least in the employee table and so it does take a while let me go down and press enter we're going to need this in order so I can show you guys this information in a better way as far as running the queries and the different things I want to show you guys in the examples it's best to have a database with a lot of data in it and this is the quickest and easiest way to set that up and like I said this see that table right there it says loading employee that's the biggest table that's in there and it's going to take a while to actually import everything so I'll be back when it finishes and then we can log back in Sequel and go through the more of the examples all right so the import completed successfully it took about 3 minutes and 57 seconds and so let me show you guys how to test it right fast I'm going LS this directory again so you guys can see but there is a test file in here it's a md5 hash test so we can run this test by typing pseudo MySQL and then- T and we need to put our left arrow in there again and then that testore employee md5 SQL and that will test the actual database and just make sure everything was imported successfully on the server so just go on and run it right fast and it'll have some output you got you guys will see and as long as you don't see any errors that means everything was imported successfully all right so the test is complete and we can scroll through this and just see that it it just verifies that everything is okay so all the records match you know what's supposed to be imported when you run this thing and as you can see it took a while cuz this Server doesn't have many resources assigned to it it would have ran a little faster if I had more processing power and probably a little bit more RAM on the system but it's fine it ran through and did it it's not affecting you guys that's watching the video it's only affecting me cuz this is the computation time that it took all right so let's go on the CD right fast just to get out that directory we don't need to and I'm going to clear again and let's go on and get back in our my SQL database so typees sudo my SQL dasu for user and we want to go back into that root account and d p press enter it'll access for our shudo password boom and it'll access for the password for the rout and if you guys remember from earlier there is no password set for the root account so let's press enter that'll get us in MySQL and then what I wanted to do was show the databases so that same command we ran earlier so show databases semicolon at the end press enter and we'll see that new database is added to our server so we got the employees database so now we can move on to the rest of our examples of how to to manage my SQL all right so the next section I want to cover is managing user accounts in my SQL and this is a very important section because this basically manages how people access your MySQL server even applications how those applications connect to your MySQL server as well as the databases in it and so the first thing I want to do is show you guys how to create a new user account and this allows you to control who has access and can manage your databases and and and so it's not that difficult of a command and it's just like I talked about in previous commands it's almost like writing a sent and so all you got to do is type create and then user and then we have to put the user information in there so we have to put it in single quotes So the name first so let's just name this DB user and then after that quote we have to put an at because you have to say how you want this user to connect to the server and you put this in single quotes as well and this is considered the host name and most most of the time you will see Local Host which is what I'll use currently and that basically means that this user can connect to this my SQL Server via Local Host so you have to be connected to this server in order to access this my SQL database and then after that you want to give it a way to access the server and that is via a password so identifi so you have to type it in properly just make sure you type it in properly because I used to spell this incorrectly a lot of times and I ran into errors but it's identified by and then our password which I'll just put password to three and then close these single quotes and then after that for our SQL statement we have to close it out by typing a semicolon at the end of it and this will create our new user account as you can see long as you don't run into any errors it says query okay zero row is affected you're good to go you created that user account and like I said earlier this command sets up a user account with access limited to the local host meaning that the user can only connect to mySQL server from the same system where MySQL is running and I just wanted you guys understand that because if you want to Grant access from let's say another host and you have to replace that Local Host with the remote machine IP address so let's say you have an application running on a application server and you wanted that user account to connect from that application server cuz let's say this user account is what allows that application to connect to the my squ server you have to put that IP address of where it's connecting from otherwise it won't have access it'll just have access via Local Host like it states now there is a wild cord and the wild cord it will allow access from any host and let's go on and type it out again so create user and I'll create another user account so let's put it in single quotes So DB user 2 and this is a totally different account press the at symbol and then the host we wanted to be able to connect from I'll use that wild cord and wild cord is the percent sign and so that's uh shift five and then we have to close those single quotes and then next let's put identified by and then we put our password so I'm going to use that same password one two and then our single quote to close it out and then our semicolon to close that query out and let's press enter we going run into any issues we typed everything correctly and this is considered that wild cord now let's say you need to change the SQL user accounts password well let me walk you guys through that query right now and it's using the alter command and the way you do it is by typing alter user and like I said it's just like a sentence so alter user and let's say we want to alter that DB user that first one let's say we want to change it to and then we have to type it all out just make sure you type it all out Local Host then single quote and then identified so that's basically all all you doing is typing it out the exact same way we did to create that user but you're using alter so identify by and then we just type in a new password so let's go password 456 and let's close out those quotes and then the semicolon at the end of it and press enter and that will alter the password for it and like I said as long as you don't see any errors you get to go now another command I want to show you guys is how to list out all the MySQL user accounts and there are some built-in user accounts you obviously got that root one and you got a couple more and you guys will see them once I type in this next command so we're going into select statement so select user and then comma host and basically we're selecting two columns within a specific table and this is from the MySQL do user table so let's go on and uh put that cic colon at the end press enter and it'll grab those two columns for us and as you can see we can see all our accounts that we created even the new ones that we just created so DB user is only accessible via the Local Host and DB user 2 has that wild card there and just to show you guys the other accounts that are there so my. infos schema I don't recommend you mess around with any of these Accounts at least until you understand fully what they do but these three accounts are systems accounts and then we got our root account as well which we're logged in using that now the next step is deleting those user accounts so let's say we want to delete the DB user account so it's the same as with tables and databases is the drop command and so drop user and then the user account so we have to put that in single quotes and it's just like when we created it the exact same format so DB user and let me move the cursor out the way and then close out that single quote then at and then open up a single quote let me close it right fast I always do that that's just a habit but Local Host and then we go back to the end and type that semi colon at the end and press enter and that will drop that so let's press the up arrow and go back in here and select those user accounts right fast from that MySQL users table and you'll see that that DB user is gone from the system now let's test out this user account that we still have available on the server and so what I'm going to do is type exit that'll get us out of that roote account and let's run the MySQL command but let's log in using the DB user 2 account and so you basically type it in just like we typed in for roots press enter and then type in our pseudo password for our Josh accounts just make sure you understand that it's going to ask you that for that password twice let's go down and press enter and then now it's going to ask you for the DB user 2 password which we set when we created the account and that password was password with a capital P and then 1 2 3 and press enter and that allow us to log into my so now let me show you guys some of the differences because right now this account doesn't have any permissions to any databases on the server so let's run show databases and then semicolon at the end and you guys should see the difference we only see two databases and these are the schema databases that are built into my SQL and so that lets you know that this user account doesn't have access to any of the databases on there and so I want to go into the grant section of managing these user accounts so let's go on and exit out because we need to add some permissions to this account so they can actually see the databases or do certain things to the database like select statements and all that good stuff so let's go on log back in using our root accounts type in our password which is nothing and let's go on and show that those user accounts again on the system so select user and then host at that my SQL do user database that way we can have a reference when we're typing everything out so what we want to do is Grant privileges to that user account so all we have to do is type Grant and there's a whole bunch of privileges that could be set for a user account there's all privileges you can set certain things like alter they can only alter a specific table they can create tables create databases that's to create privileges but what I'm going to do is just do all privileges just show you guys the base level of actually giving an account and actually that's not the base level I recommend you give the least access that a user needs so if they only need access to a specific database and they only need to let's say select certain things then you want to just give it select permissions you don't want want to give it a all privileges if it doesn't need it for that account but like I said I'm going to just do all privileges just to make it simple just so you guys can see but I recommend you guys look at all the different types of privileges so you can understand them and give the least privileges needed for the account so privilege and make sure you type it in right but that's Privileges and let's say we only want to give them access to a specific database well you put on that database name and we'll use that employee database it's employees and you want to give it access to all the tables so you just put a period and then the store at the end of it that's like a wild cord for all the tables under that database and now we have to put in the user account we want to apply this to so that's why I say it's like a sentence you know what I'm saying Grant all privileges on this database all tables to the user account and so that user account is the DB user 2 and then at the single quote and then wild cord single quotes and then our semiot in and press enter and it will grant those privileges now let's go on exit out again cuz I I just want to show you guys but if we log back in under that user account that we created so password 1 2 3 Center skip back in it and let's run that show databases command and see me coling at the end press enter as you can see we now have access to that employees database let's go on exit out and get back into our root account but I just want to show you guys that's how you grant those privileges now let's say you want to Grant all priv is on all databases let me go back up here to that and all you have to do is put in a wild cord so the wild cord in front of the period and that specifies all databases and all tables under each one of those databases and so we got all those permissions now so we can exit out and then log back into that account and type in our password again and press enter and then let's show databases and this is something you definitely don't want to do I just want to show you guys that you can do it you see how you can see the other SQL database on here like before we couldn't see my SQL or that CIS database now we have access to see pretty much all databases and all tables and let's exit out go back into our root accounts boom and it doesn't make sense to go through everything so I'll just show you guys a few other things let's say you wanted to go employee database and I know one of the specific tables in there is department and so employee Department you only want this user to have access to just that database with that table you only want that user to have access to that specific thing like I said you can narrow this down you know deep so people only have or user accounts only have access to specific ports of a database this can be accomplished by typing out the commands like this and then I won't run this I'll just go back in let's put a store in there cuz I wanted to show you guys something else but let's say you instead of graning all privileges to This Server we can go back in here remove those privileges and we can add a whole bunch of other privileges so we could just do select let's say you want to do a insert and then you can also put like delete and then on you know this database and all tables so you can narrow down like I said even more with this grant command and let me just go to run that and just see what happens and let's actually exit it out I want to see what it looks like uh when we go over there boom I want to see if it removed all the other privileges so let's log in and then run these databases and semicolon boom and we can see all the table so it still has those permissions for all the databases so you might want to run a revoke before that and then run it so it specifies okay that makes sense so it still has that those settings still there for all privileges all tables so let's exit out and now let's walk through how to revoke permissions so this is an important part you want to revoke permissions or at least understand how to revoke voke all privileges for a specific user and we'll go through and do that for this account and let's go back in here and select all the user accounts I always like to do that so I can have a reference when I'm typing out things so I don't mess up misspell certain things but the command is revoke and revoke all privileges on and I'm going actually put store there as for all databases and then period all tables and then from that specific account which is our DB and forgot about our quotes but yeah single quotes in there make sure you type that in and then this this one has the wild cord on the end of it so we'll type it out exactly how it is up there so from that user account on this server so let's go on and press enter and we forgot about our semicolon so if you do forget about it it'll bring it up on a second line you can just type in the semicolon press enter and it run the query and so let's exit out again I know I keep jumping back and forth but I just want to verify a lot of this stuff and that I'm showing you guys the right thing and I want you guys to follow along so you can get used to it cuz once you start typing these commands out you get the hang of it you know fairly quickly so let's go on and type out our show databases again and then put that semic colon at the end boom you'll see we don't have access to any of the databases on the server and then let's exit out and what I want to do is show you guys something else after this let's log back in our root account and Grant some privileges again and I want to get real specific with this account and that way the next command I show you guys will make a lot more sense and so let's grants and then select and let's also do that insert as well and then the delete on the employees database and let's get that departments table within that database and it's not a s now that I think about it it's not a s and then let's hpe two and then our user account which is that that DB user to and then at then a wild card and then the see me coling at the end and press enter and it'll Grant those specific permissions for that user account and now let me show you guys the last command which is the show grants so we can show all the grants that we have for a specific user so and let type it in correctly and we have to type in that user account the exact same way so DB user 2 and then at our wild cord and then the semicolon at the end and press enter and this will show you the specific permissions that this user has and as you can see it has Grant select insert delete on the employees database and then the departments table for that user so it's very specific you can get very specific with these privileges now this next section we'll be diving into queries and this is where that employees database will come into effect and I'll show you guys different types of queries throughout the demonstration so first thing we need to do is Select that database that we want to use so first off let's show our databases and I just want to get you guys in the habit of actually doing this but show databases semicolon at the end press enter that will show us all our databases that we have on the server and what we're looking for is that employees database and that's the one we want to select by using the use command so use and then the database name so employee and then let's put a semicolon at the end press enter and now our database has been changed to the employees database that's the one we are currently using now the core of quering data is the select statement and it retrieves data from tables and you can customize what you want to see now first first off we need to find out what the tables are in the database and we can go back to that command that says show tables and then semicolon at the end press enter and it will show us all the tables within the employees database and now let me show you guys the basic structure of the select statement and all we have to do is type select and then we'll start off with store which is for everything is pretty much the wild cord for all columns within the table and then we want to specify that table so from a specific table so we can pick one in here let's start with the Departments table and then put a semicolon at the end press enter and that will list out all the data within that table and you can do this for each table within the database and so let's look up here we have another table and I'll start with the employees one as well so there's the employee table as well so let's run that select from but instead of selecting the department let's look at the employees and let's put a semicolon at the end press enter and you'll see it'll pull up a lot of data this table has about 500,000 records within this one table and that's representative of all the employees within this organization and so that select statement will pull everything with that store wild cord in there in the select statement now let's say we want to look at the first 10 rows of this table well we can limit the results that are put in front of us by using the limit Clause within our select statement so we can go to the end of this select statement and we can limit the results to let's say 20 we only want to see the first 20 records so we can go down and press enter and this will limit the results and you'll see that we only have 20 rows pulled and like I said this is just a way of limiting it really for you guys that are watching this video so you guys can see less data on the screen now let's work on work on these columns let's say we only want to see specific columns so certain data we only need for whatever we're running this select statement for and so let's go through and let's hit the up arrow and I'll be using the up Arrow a lot and this allows me to run previous queries but let's say we don't want to pull all those columns like I said you can specify specific columns so let's say we only want to look at the employee number and the first name and the last name so we can type empore number and then we put a comma behind it and then first uncore name and then let's let look at the last name as well so lastore name and then we can press enter we don't have to put a comma after that that is the last column that we want to pull and also let's change the limit as well so I can show you guys the information right on the table so it doesn't pull in too much too much more information let's go down hit 10 and press enter and boom as you can see it only pulls in those particular columns that we specified in our select statement so employee number first name last name and there you go within the table so employee number first name last name boom now one other thing with the select statement you can pull in this information in whatever order you want to and what I mean by that is the columns let's say we don't want to see the we want to see the employee ID after our first name and last name so let's go back to the end of it put a comma and let's pull in that employee number and let's pull it in that order we want to see first name last name or actually let's do it a little bit different as well we can do first name name right here so let's put a space and go first underscore name and then put that comma there and then let's remove the first name up here at the front and we want to pull last name first name and then employee number so let's go on and press enter and as you can see it'll put it in that order for us so last name first name and then employee number at the end now let's say we want to filter the data and I don't know how many results will come up let's say if I select a specific name so I'm going just select one just to see and I want to see how many employees have this same last name so bam Ford and I know this is a fake name but there is a way of filtering and this is using the wear clause in our query so we can up arrow and then let's go back in here and put the store let's remove the columns put the store in there for the wild cord from employees and then where we want to type the wear claw in there so let's take this limit off we I'm not going to use it I'm going to see what pulls up but where the last name underscore and we need to put a equal sign in it because we're searching for something specific so we're searching for the last name to be a specific variable and so let's put it in quotes and let's uh paste it in there right fast I don't think you guys can see that but bam forward we want to look for people with the last name of bam for within the employees table so let's go on and press enter and see how many results we get and we get a whole bunch of results so it's 178 rows that have that meet criteria and that's essentially how you filter using the wear claw and then we can filter this down even more let's say we want to look for all the females that have the last name banford so let's put the up Arrow up there and then after the banord we could put another clause which is and so and the gender equals and thenone want to specify female so let's put it in a single quote as well so with a capital f and boom we are good to go let's see see what it pulls up and it pulls up everybody that has the last name of banford with the gender of female so that's super cool and then you could specify that query you know columns if you want to you can go in here and pull in the last name and then the first name and then the gender and then let's go down and pull in an employee number as well so and let's go down press enter and boom it'll pull in just those four columns that we wanted as well as searching for that specific criteria so we got our last name of B of bonford and then all female employees with all the columns re requested to see and then you can modify it you can change this to let's say male we want to see all the male employees like that and as you can see it pulls in you know specifically males for this these results and it's 103 males versus 75 you know females so let me show you guys another option you can run with the select statement and that is the order buy and first off let me run a normal select so nor select store from the employee table and semicolon at the end press enter and I will pull in all the information and what I wanted to do is look at this column right here this is the actual hire date of the employee so let's say we want to order the results by that hire date and let's look at the oldest employee up to the newest employee and so let's run and select again but let's limit it to just 10 and I'm only doing that so you guys can see a little clearer so that shows pretty much the first 10 records or the first 10 results and their hire date and like I said I want to show you guys the order by clause and so we can type that select store from employees I'm going to leave that limit there but the other option is order by and it's two words and what we want to order it by is the column so that's the hire date so let type hore dates and and press enter and as you can see it'll pull up the oldest dates and so that's 1985 January 1st and it goes up from there so these are our oldest employees and the reason we know it's going up because the next employee on a different date that was hired is January 14th so 1985 so it'll go up from there and I don't want to I just want to limit the results so you guys can kind of see it in that order and you can order it by a specific column A lot of times you want to look for that and then also so you can do the exact same thing for this this is another date column but there is a birth date and so we can change that up and not use the higher date but instead of that let's look at the birth dates and this could be for let's say HR purposes or something to that effect let's say you want to look at the birth date of all your employees for whatever reason you can order you know all the results by those dates and then you can also throw your filtering in there with the wear claw so go on press enter boom and as you can see that's our oldest employee right there or a couple of our oldest employees right here that have the same birth date which is February 1st of 195 is what what's that about six employees that have that same birth date and so this is a good reason to show you guys another way of filtering by using that wear clause and let's say we were looking for all the people with a birth date but in just the year of 1950 so we can go through and just search for specific people with a birth date of of 1952 and let me show you guys that break out this order by let's remove it and let's use our wear claw so let's go wear the birth dates uncore and there's another clause in there you can put and that's like and this allows you to search for text that match but also have different things either in front of it or behind it and what we're looking for is that year two and so we have to put it in single quotes so single quotes and then there is a wild cord of the percent sign so anything before which it doesn't have anything before we really don't need to put that there I just wanted to put it there so you guys can see but's say it put that wild cord at the end of it so anything after that as well and then the quotes at the end of it to close it out and let's go on and press enter boom and that'll pull in all the employees with a birth date in within two now let's explore how to add data to a table and first off let's show the tables again right fast so let's go show tables semicolon press enter and what I want to do is look at the Departments table cuz that's the easiest one to work with when it comes to inserting data and let's run a select on the Departments table right fast select store from departments and then semicolon at the end press enter as you can see we only have a couple records so nine rows so let's say we want to add a new Department well to insert new data we have to use the insert into statement and so let me give you guys a quick example we can run through and type inserts into and then we want to insert it into the department table and then now we have to specify our columns so we have the department number and the department name so we need to put parentheses in here and this is to specify our columns so let's start off with the first one so department so dtore number and then put a comma after that and then a space and then the department name so D _ name and now that we have our columns selected where we want to insert data we have to type in the values that we want to put into each one of those columns for this new Ro that we're adding so values and then we have to put it in the same order as they're represented up here so our department number and Department name and we just put those values and we have to put them in a single quote so let's put the first one in which is our department number and as you you can see the next number is Department 10 so let's put D 010 and that's Department 10 and I know you guys can't tell but it's out of order but Department 9 is the highest one so I want to go with Department 10 that's how I figured that out but let's put a comment in here now let's add our department name and let's say this is the technology department so or information technology whatever you want to put in here you want to put it in quotes again sorry about that I didn't put the quote in but put it in quotes and then close out our parentheses anything you open you need to close and we close out that parenthesis and then put semicolon at the end and this will insert that data into the Departments table so let's go on and press enter boom query okay one row affected and now let's select from that department table again and you guys should see our new row which is our technology R so D 0000 and that's thep the technology department now let's say we made a mistake while inserting that data into it well we can use another statement which is a update statement which will allow you to make changes to any of the data within the table and when you're building out this query you want to make sure you find some form of a unique identifier that's why majority of tables will have a primary key or some type of number that identifies that role especially if you don't want duplicates within the table and so our unique identifier is the deportment number because that's different per row that's in the table and so we'll use that as our wear Clause when we type out our update query so let's go on and break this out so let's type updates and what we want to update is something in our departments table so update department and let's say we want to change the technology to help Des so we have to type set and then Department name or the column that we're looking for which is that department name that we're changing so Deport uncore name and we want it to be so we can do equals and we put it in single quotes so let's say we want to change that to help Des so the help desk Department if you you know I'm just coming up with something but we need that unique identifier so where department number equals and we can put this in single quotes this is just basically to search for it but the d010 and then let's close it out with semicolon and then updates that department name where the department number is the 010 so scroll on and press enter and that should make changes to it so as you can see right there it says rows matched and then rows changed and no warnings as long as we don't see any warnings or errors we get to go so let's go down and select from that table again and as you can see we have a help desk department now and that's d010 now let's say we want to delete that row that help Des row we just created so there is a statement you can run and that is the delete statement so let's go on and type this out so delete and then we want to delete from the Department table so we have to type that out and then where the department and I already know what to use I'm going to use department number because that's our unique identifier and plus it's super short and specific as far as the department number and there's no other number like that so you can't make a mistake but department number equals and we use that exact same format so single quotes and then d010 and then put a semicolon at the end but that will delete that Row from the table so boom and as you see query okay and then we can go back in select from the Departments and you'll see that help desk is gone I know it's kind of hard to see because it's not ordered properly but yeah help desk is gone from this table so the next thing I want to cover is primary keys and foreign Keys now first off let me explain what a primary key is a primary key in my SQL is like a special idid for each row within the table it's a unique number or code that helps you identify and organize the data so if you think about it it's just like your fingerprint is unique to you a primary key ensures that each row within a table is different from the others and this makes it easier to find and manage information within a database and let me start off by showing you guys a primary key right fast which I showed you one earlier in the video when we created our first table but let's show you the primary key within one of the tables within this employees database that we're looking at now so let's start off with the employees one by running the describe command and this will basically give you the information about a particular table and let's type employee and then semicolon at the end press enter and like I said this will give you all the information about this table and as you can see right here under key this column right here it says Prime that stands for prime primary so our employee number is our primary key and typically the data type is integer and it's and pretty much a primary key is unique so this number is different for each row within the table now let's talk talk about a foreign key and a foreign key is like a bridge between two tables it helps one table understand information from another table so for instance this employees table and let's say the Department's table well a foreign key in the employees table links to the Department's table showing which department each employee belongs to and it's like a connection that keeps data organized and related between tables and what I want to do is create a foreign key using both of those tables and let's describe the Departments table right fast so you guys can see the information about that table as well and departments and then semic and press enter and it'll describe that table as well and as you can see the primary key is is the department number and so what we want to do is add the department key which is that primary key or the department number to this table and that way we can have a foreign key or a foreign connection between the department as well as the employees so each employee will know what department they work for and so this is a great example and the first thing we need to do is add another column here because you can't use a column any of the columns that are already there cuz they already have data in it so you need to add another col colum to this table and what I typically do when I'm adding a foreign key I'll use the same field name that way you don't get confused with it and so let's go down and alter this table so alter table and then we want to alter the employees table and what we want to do is add a column and I haven't showed you guys this yet but add column and then what we want to do is specify the column name so we want to add department number and then we have to tell the character type and that's why I described this table as well the apartment's table the apartment number is Char so characters and four characters so let's go down and put the exact same thing so chore and then in parentheses we can put the number four in there so four characters and let's go down and put a semicolon at the end of it and this will alter that table boom and we're good to go so query okay you know zero rows affected and then let's describe our employees table again let's go back in here and look at describe employee boom as you can see we have a new column in there it's that deportment numbers it has that same data type which is TR 4 and I put in there uh yes I left it yes as it could be no and the default is no and now let's go down and alter our employees table in order to make that a foreign key and so let's walk through this we can type alter and then table and then the employee table and what we need to do is add a constraint and you can name the actual foreign key and that's what this line actually means so add constraint and let's name our foreign key let's name it uh foreign key let's see uncore departments underscore number and so I just give it something unique forign key so that FK and then underscore deportment number that's the name of our constraint and then let's press enter and then let's add our foreign key so let's go foreign key and then we have to specify that column within our table our employee table and that is the department uncore number and let's go down and close that out press enter and then we have to reference our forign key so the reference is going to be in our deportment table so we have to type references and then deportment and then specify that column as well which is basically the same so department uncore number and let's go down and close that out and then we put our CA colon at the end and let's press enter and it should add our forign key and as you can see that kind of took a little while it was about 53 seconds that it took almost a minute minute because of the 300,000 rows that it has to go through and basically it's just updating that column and marking it as a foreign key location now let's go to and run a quick select right fast so you guys can see that column in action so select store from our employee table and let's limit it to 10 cuz I don't want to see all those rows let's go on press enter boom and as you can see we have that department number column and then also let's describe that table as well so let's go in here and look at our employees table again under the describe and as you can see it is a foreign key and just to explain what this means right here it says Ma so that basically means multiple and this is our foreign key so that means that multiple occurrences of a given value are permitted within that column so that's essentially what that means now next I'll run a quick query and this is a update query it'll actually take me a while to actually run this because I'll do a update and what I'm doing is grabbing random deportment IDs and dropping it into our employees table under that new department number column and this is so I can next show you guys how to work with joins so you can follow along if you want to if you don't this will take some time depending on the resources on your server because it has to go through 300,000 rows and update them so what I want to do is update the employee and what we want to do is set that appartment number within that table and we want it to equal some information that we select basically and this is basically a nested query so to speak so I'm going to just throw this in there and that's to open up the parentheses for the other query and what I'm doing is a select and then we want to select that deportment number and what we want to select it from is the Departments table so let's press enter from Department table and we want to order it because there's other options like I said I didn't go too deep into a lot of these options but there are plenty of options out there I didn't want to it'll take me all day to go through all the options that you could do within a select query I just wanted to show you the base level and then you can build from there by looking at the documentation and what I want to do is use a variable that's built into my SQL which is called random and so r a n d and then we put the parentheses at the end of it it'll basically pull random deportment numbers for each row within this table and so let's type a limit in there and we want a limit to one and then let's close out our top parentheses up here anything you open you must close so close that parentheses and then put our semicolon at the end and like I said this is going to take some time and if you want to just pause the video that way you can copy this and that way you can run this query as well to update your employees table with a random deportment number so let's go on and press enter and like I said this is going to run for a little while and I'll be back and it should tell you at the end of it how long it took me and let's compare times be right back all right cool so that wasn't too long it was uh 35 seconds so 35.2 eight let me know if you beat my time down in the comments but it updated each one of those rows with a random department number and let's verify this by selecting store from our employee table and semic calling at the end press enter and it'll give us our results and as you can see it has a random department number throughout the whole table and so that'll allow me to show you guys joints now one other thing I did was I went back and ran another query that'll change some of the department numbers to null so that way the inner joins and the left joins and all that stuff will show properly so you guys can get a good example of how they work and so let's start off by explaining what a joint is it's like bringing two tables together to find related information and there are three types types of joins and the first joint I want to show you guys is a in joint and it shows only common data between both tables for example if we join that employees table and the Department's table using the department number which is our foreign key within that employees table we'll get a list of employees belonging to specific departments and so it'll exclude all the rols that have null in in it it doesn't have a specific Department in it and let's go on and run a enter join right fast and let's type select and I'll put this stuff on multiple lines so select store from employee and then what we want to do is a inner join between two different tables and the other table is our department table because they both have something in common which is that department ID and so we have to specify that so hit on type on and this is basically a injin in departments on these specific column so let's type employees first which is our first table and we want to match our Department's number column within this table so let's make sure I type it in correctly so Department no and then we want to make sure it equals the department and then department uncore number and then let's close out our query and press enter and this will give you a good clear picture of what it actually does so what it'll do is it'll bring in all the information from both tables so we got our employee the ID we have that date of birth we have that first name last name and then their gender and then their hire date and then we also have that employee ID and we have it twice and this is the one represented within our employees table and then this one right here is the representation of the one in our department table and then ourselves so that's considered a inner joint and basically it's joining on those two columns just matching them up and one other thing I want to point out as you can see the row amount it's actually 300,000 rows within there but some of them are set to null or they have null within that department ID and so they're excluded and so that's what makes the difference between a inner join and a left join and a right join the inner join will only bring in roles that match 100% so if this doesn't have a department ID it will exclude it from the results and let's run this query again right fast and let me show you something a little bit different so we're going to run a enter join again and this applies to all the different types of joins a left join and a right join as well you can select specific columns and so let's say we only want to see the employee number so let's put that there and then the first name as well as our last name and then the department that they work for so this is from that other table that is joined so let's type department and then underscore name so we want to see their employee ID or employee number their first name last name and then the department that they work for and we don't have to put a comma at the end of it and let's go on and press enter and that should pull in just that information that we requested and as you can see you don't have to see that department ID you can just see the department name and that's a great way of organizing the results and using a join to bring in information from other tables now a left join this type shows all the data from the left table which is our employees table and only matching data from the right table so it'll list all the employees and their departments if they're available so this is where we'll bring in those NS because certain records won't have a department ID but it'll still bring in those employees and it'll just show null next to it so let's go on and do a left join right fast and all I'm going to do is go over here I'm going leave everything the same and we're going to type left here and like I said it's going to pull everything from the employees table and it's all based on that department number you know all that good stuff so let's go down and run it again and you'll see that it'll pull in all the records and then you'll start seeing all the NS in there so this will bring in all our employees and it'll also show you the ones that have an employee ID associated with it it'll show that employee name as well and then n for the ones that don't have one so that's what a left join will do it'll basically bring in all the records from the left table and then the information that's related from the other table instead of excluding all the NS or the exact matches and then the right join is the exact opposite it'll basically display all the data from the right table and the matching data from the left table and this will give you a list of all departments and their employees if any and so this right join it may look a little weird let's see let's actually see what happens but let's go right and this may not work too well well and this is based on my mind just thinking through what it will actually show so let's go through and look at it right fast and it's it kind of looks like that inner join because it'll only bring in records from that table that match so that right join won't really work for this example but you can see it actually works it works it pulls in only those that match the right table which excludes all the dos and so that's pretty much how to use joins in my squel all right so let me show you a little bit more Advan of a view and I'll just kind of type this one out so you guys can see but first let me explain exactly what I'm trying to do so if we select our departments table so select store from department and then semic colon at the end press Center boom let's say we want to add into this table the amount of employees that are in each department and so you could do this or accomplish this by using a joint and essentially what we'll be adding is another column here from our employees table that will count the amount of employees that fall under each one of these departments and so me go on to show you guys how to do this right fast and we can type select and then department. department number and this is a way to drill down to all of the columns that we want to use within this table we can specify it by putting the table as well as the column and so sometimes you have to do this because if you leave it kind of generic especially when some of these tables have the same column title you have to kind of specify it like for instance the employees table does have that department number column in it so you want to specify it and then let's do Department table again and then we want to get that department name and we need to put that underscore in there and then now we'll use a built-in variable in order to create our third column here and what we want to do is count and you have to put the information within par parentheses that you're trying to do as far as the variable what it's actually doing so we want to count the employees table and then the employee number column so empore number and now we want to continue out our join so as number and this is what we want to name it so we we're naming this column as num of employees so we type that in and then we want to catch that from the Departments table so boom and now let's build out our join so let's do a left join and that way it'll pull everything from the Department's table and then all the information that matches within the employees table so let's go employee and then on and then we specify what we need to match and then that is the department. department number and then we want that to equal our employee table and then that department number that we have in there soore number and now let's add a little more in here let's Group by Department's number and then departments and that is the department name that we want to do so we want to group it by that so let's go on and close it out this query should run perfectly as long as I didn't type it in incorrectly or type anything incorrectly but we can go back and check if we need to so let's go on and press enter see what happens and yes it worked correct so we got our department number we got our department name and this is pulling the number of employees from the employee table that fall under each one of these departments and I kind of just wanted to show you guys something a little bit more complex and what you can actually do with these joins all right so let's talk about views in my SQL and Views are like a saved query that you can use to simplify your data retrieval and it's essentially a virtual table created from one or more existing tables it doesn't store data itself and so views allow you to see specific data from your table without altering the original data and one thing about of you is it's constantly updated and it's all based on the data in those original tables so if you delete data in a specific table that's associated with a view the next time you run that view it will show you the updated information for that table and it's all based on how you run the query within the view and so let me go down and show you guys a quick example right fast and let's just start with the basic syntax and that is create View and like I said I've said throughout this whole course that these queries sound like sentences so we want to create a view and we want to name that view so we want to name it whatever we want to name it so I'm going to name this one employee gender because I want this view to be specific to the genders of the employees within the table and then we have to type as because this is the query we want to run every time we run this View and so let's type select and then let's look at the first name so of that employees table and then and the last name of that employees table and then also the gender and then let's press enter and then we want to specify what we want this information pulled from which is the employees table let's put a semicolon at the end to close out our query press enter and it will create our view long as you don't see an error that mean the query was okay it ran successfully it created that view now let's run show tables that way we can see the actual View and it looks as though it's a table and I'll show you guys that second so show tables and press enter and as you can see we got our employees gender view there now it doesn't put anything next to it or it doesn't show in any way that it's a view the only way you can kind of see it is if you're in let's say MySQL workbench you'll see it'll show up under a different section when you're connected to the database but we know that it's a view and so you can interact with this just like any table and so let's go on in and type in our select statement and store from and then let's select that view so employee uncore gender then see me coling at the end press enter boom and that's our view and it'll pull in all the data and it's all based on what you put within that query for the view and it'll always show that information the exact same way and then let's say one of these employees is removed from the original table then it will update this view automatically you don't have to update in two different places like update in the original table and then update Within the view the view will automatically update based on the original table and data so let me show you guys how to drop that view so let's run show tables again and it's essentially like dropping a table so let's go show tables and then all you have to do is type drop View and since we know that's a view you know tables won't work so if you if you type drop table that won't work we have to drop view because we know it's a view so drop view then employee gender and then semicolon at the end press enter that'll drop that view for us so we don't we won't see it anymore now let me give you guys another example and we'll look at a different table because I don't like using that employees table because of amount of data that's in it let's focus in on the Departments table and let's just run select right fast store from department and then semicolon at the end press enter boom and as you can see that's all the data within there let's say let's add a column to this table and you guys remember the alter table command so let's type alter table and then department and then let's add a colum so add column and then let's create a email column and let me tell you how this makes sense so let's say each department has an email address that's associated with that department and typically what that's called is a distribution list so each department will have a distribution list and so when someone sends an email to that distribution list it will send send a email to all the employees that work for that department and so that's the concept behind what I'm doing so let's add the column and let's call it emails and then let's specify the type of data to be stored in that column so let's go vure and then let's give it about 50 characters cuz like I said it's it's a email address so it probably shouldn't be that long you know what I'm saying so 50 characters should work so let's go and press enter boom that'll go down in create that column for us and then we can at the tables again the deportment table and press enter and as you can see we have that column now and so let's add some data to this column and I'm building this out you'll see once we get to the view section what I'm actually doing and so let's update the data by adding a email address for each one of the Departments so let's go update the Departments table and let's set our email for the first one which is marketing to equal and we can put those single quotes in there that way it'll add that information in there for us and let's go with marketing atcom and so that's our distribution email address and then we have to run a where in so we can specify where that needs to go cuz right now it doesn't know where to put that information and we want to put that under the marketing department email address so we could go where department number equals and then we want to search for one so d001 and we know that that is the marketing role and so let's put a semicolon at the end and press enter and that should update that Row for us and let's just run a quick select all right fast select from departments boom and as you can see we got our email address in there for the marketing department and so I won't bore you guys with watching me do this I'm basically going to run the last command multiple times just updating and adding an email address for each one of them so I'll be back when this finishes all right cool so I updated the table let's go go on and run a select again so select store from department and press enter and as you can see we have an email address or a distribution email address for each one of the Departments within our organization now let's go down and create another view so let's type create View and then we want to call this view deportment email so that's our department email and we want to set our query as and let's go on and type it out but let's type our select and then let's pull in just two columns within here and let's look at the Departments uncore number and then also let's look at the email and then we want to do this from our Department's table boom and press enter and it'll run that query it'll create that view and so let's go show and then you'll see that we have our department email view there and let's go down and select that view right fast so select store from Department _ email and semicolon press enter and that is our view that we created now here is the full example so let's go into let's actually inser insert some more data into our departments tables like a new Department similar to what we did earlier as far as adding let's say a help test department so let's gooll on and write out a quick query to insert into department and this will add us some data in there so department and then specify those columns that we want to update or input data in we have to put those in parentheses so departments uncore number and then departments uncore name and then we want to use our new column which is that email column so let going to close out our parentheses and then let's type in the values so values and then let's put those in quotes so let's create a new department number Department 10 so 0 one 0 Let's close out those quotes and then let's name let's give it a department name so our department name is help Des we'll just use that same one we used for so help des and then the email address is we can name it let's say help and also let's make sure we add our quotes I don't want to forget that so let's put our quotes in there and then let's put our quotes for our email address as well so let's name it help at abc.com close out our quotes close out our parentheses and then put our semicolon at the end of it and press enter and cool so we added that new line in there and let's run a select of our just the normal table so from departments and just to show you guys that the information has been updated so help des and then it we added in that email address for the help Des department or that distribution list and then as you can see this is our view so we updated it just in our Department's table and so with our view I'm going just run this same select query for our department email View and you'll see that it'll update based on what's in the original table so we don't have to updated in two places that's the whole purpose of me showing you guys this exercise so as you can see we got the help Des change that we made to the original departments table so hopefully that gives you a better understanding of how views work and it's the same way let's say we removed that column out of there it will update our view as well all right so the next thing I want to cover is indexes and a index is basically a quick reference for your database and it helps speed up data retrieval it's a structure that organizes data in a way that makes searching for specific information much faster so think of it as a index in a book that tells you where to find a particular topic without reading the whole book so let's go through a few examples right fast and first off let me show you guys how to see the indexes that are currently there for a particular table and what I want to do is look at our employees table so all we have to do is type show indexes and then from from a specific table which we will look at the employee table and just put a semicolon at the end press enter and this will show you all the indexes and there currently are two indexes on the employees table currently and the first one is associated with the primary key and the second one is the foreign key but let's say with this table we do a lot of searches by last name well we could create a index to structure and organize that data to make it searchable a whole lot faster and so let me give you an example of creating that index and all you have to do is type create index and then you want to name that index and what I typically do or the format I do X and whatever column I'm working with so last name is what we want to search so or what we typically search a lot and then now we have to specify the actual column within the table we have to specify the table and then the column so all we have to do is type on our table which is that employee table and then we have to specify the column and we put that in parth pares so we can go back in here and type our column name which is last name and then put a semicolon at the end press enter and that will create our index it takes some time depending on how big the data is cuz it has to structure it but as you can see that query ran successfully and it created our index now let's go back and run these show indexes for employee and let's see our new index that's in there and as you can see it's the idx last name that's the name that we gave it and then it looks at that last name column within the table and so let's just run a quick query right fast and you guys may or may not see a difference when we last ran the select on the employees table because it's all dependent on the amount of data that's in the database whether this will benefit you or not and then how often this information is updated as well as searched so let's type store and then from employee and let's put a wear clause in there so wear last name equals and let's look for a employee last name of bass I know that's one of the names in there so let's type ba a a z and then close that out and then semicolon at the end press enter and as you can see it took no time to actually find that it was less than a second to actually pull all the data for a bass and that's what it'll do it'll increase that speed on searching within that based on the column that you specify in your index and so let's remember that so it only to it took less than a second to actually search for all the employees with the last name of baz and so let's drop that index and this is a good opportunity for me to show you guys how to do that and essentially all you have to do is type alter table employee and then we want to drop our index and then we have to specify that name so id id X and underscore last name and it'll drop that index for us so let's go on put a semicolon at the end press enter that'll drop that index and then now let's run run that same select where we search for the last name of bass and press enter and it took 10 seconds so you'll see you know a difference depending on how much data it is within that table and so there are some downsides to using a index for instance there are slower rights so let's say this table is updated a lot then it slows down the insertion or the updates and the deletion operations because they must be maintained alongside the data and I mean the indexes they have to be maintained and then also there may be an issue with storage because these indexes they consume extra disc space and so if the database is super large then those resources could be significant and also the complexity of these indexes you know what I'm saying they require regular maintenance and then also like one of the other cons so to speak of using indexes is the memory usage it does use a lot of memory and that can affect effect these servers performance so you have to think about that when creating these indexes and be very strategic all right so next I want to talk about subqueries and a subquery is basically a query within a query and it's a way to retrieve data from one table based on the results of another query and for example you can use a subquery to find employees in a specific Department by first querying the department and then using the results to search the employees table so let's go through a demonstration right fast and I actually wrote out a query for this and I'll show you guys that demonstration now and let's just type select and then store from our employees table and so this is our main query this is the main query that we want to use and I'll show you guys the subquery in a second but I'll use it under the wear Clause within this query so let's go select store from employees and then where department number equals and this is where we we will put our subqueries and we have to put our subquery in parentheses cuz it's a totally separate query and then let's type another select and then department number and then what I'll do is pull this information from another table which is that Department's table so so let's talk that in department and then where Department name equals sales let's look for the sales department and let's put that in quotes single quotes and then let's close out our query and it should work for us so let's press enter both as you can see it pulls in all the people from sales so that's super dope right it goes through and searches and finds all those people with the Department name of sales and it's all based on the department number so that's essentially a subquery example and I only wanted to cover like one example because subqueries can be get very complex well queries in general can get very complex and then when you start adding subqueries into the mix it takes a little time to build out those queries queries and so I want you guys to practice writing out queries as well as subqueries based on documentation that you look out there for my SQL or SQL queries all right so next I wanted to cover store procedures now store procedures are like a reusable script that contains a series of SQL statements and it's like creating a custom function that allows you to perform specific tasks especially if they're repeatable for instance if you guys remember the query we ran during the join ction and I'll actually paste it in here so you guys can see but this is the select statement that we created in order to pull the count of employees based on the Departments and so let's go down and run it right fast so you guys can get a picture of what what I'm saying so department number Department name and then the amount of employees that are in each one of these departments and so let's say we had to type this out a whole bunch of times you know this is something that we reuse all the time like every day or so well it'll be a pain to have to retype this every single time so we can create what is called a store procedure with this query built into it and so let me give you example of how to actually do that so the first thing let me show you guys something right fast so if we type select store from departments and I'm just showing you a basic query so you can understand what I'm going to show you so at the end of each SQL statement or query you have to put what they call a delimiter and the delimiter by default is the semicolon within SQL but within our store procedure while we creating that procedure we have to put this query in the middle of another query and so in order to end the creation of the store procedure we have to change that delimiter so it doesn't mess up our actual query that we're going to put in the inside of it and so I'm not going to run this query let's go on and back it off but I'm going to show you guys how to change the delimiter and all you have to do is type delimiter and then we can specify what that delimiter is most people use these slashes that's typically what you'll see in documentation but there's others you can use but I'll just use the slashes so four slashes let's press enter and that will change our delimiter for ending a SQL statement now let's go on and type out our store procedure and the way you create it is simply create and then we want to create a procedure and then we want to name that procedure so I'll just name it employee accountor by department so department and then at the end of the name of the procedure we have to put C's and so this is essentially like a custom function so after this we have to continue writing but we have to specify the beginning of our query that we want to run within our store procedure so by specifying you have to type begin and this is just a designator to let SQL know that this is the beginning of the script and so let's press enter and then let's paste our query in there boom and and right now if we didn't go in and change our delimiter when I press enter it's going to to finish out this this whole statement that we wrote out which we did don't want it to do that's why we changed the delimiters so just like we put the begin here of where our select statement starts we have to put an end so if we didn't change that delimiter we wouldn't be able to end the statement so let's press enter now and as you can see it didn't complete out our SQL statement and so let's go down to type in and then our slashes and that will allow us to end this statement that we wrote so let's go on and press enter as you can see query Okay so it ran successfully it created our procedure now let's go on and change our delimiter back you don't want to forget to do this otherwise you're going to be running other queries and you'll be like what why is it not running properly and that's because the delimiter hasn't been changed back so let's type delimiter and then the semicolon like what is typically used for our queries press enter and that'll change our delimiter back to that semicolon now let me show you guys how to call that store procedure that we have cuz it will use this name right here so employee counts department so I'm going to copy it right fast and then all you have to do is type call and it's like calling a function so call and then the procedure name and then let's go to put the semicolon at the end and press enter and that will run that query for us and so that's a great way of running complex queries you know that you have to use multiple times or you run daily or multiple times a day this is a great way of running those queries by creating a store procedure and then you can call it store procedure and plus it's less text you have to type and so lastly let me show you guys how to drop that store procedure so all we have to do is type drop and this will basically remove the procedure so procedure and then the procedure name and I'm going to paste it in here but you don't need these uh parentheses at the end of it that's mainly just for the function or calling it and then when you create it you have to put those parentheses there but all it is is a name so you want to just drop that procedure by its name without the parentheses so let's go on and press enter that will drop that story procedure and as you can see if we call it again it'll say error because it does not exist anymore we deleted it all right so the next important thing I want to show you guys in my squel is triggers and triggers is like a set of predefined actions that automatically occur when specific events take place within your database and this could be things such as inserting or updating or even deleting data and so let me go down and show you guys a quick example and I have to go through a couple processes one of the first thing I want first things I want to do is create a new table I want to show you guys how to create a audit log and basically the purpose of this is every time we make an update to the employees table I want this information tracked within another table and so let's start off by creating that table so we can type create table and then let's name this employee Audits and then let's put our information for the table in there let's start off with a audit ID boom and then we can make this Sager so int and then let's make this Auto increment so autocore increment and this is basically to auton number each row within our table and then let's make this the primary key boom and then let's put a comma there and let's create our next column and let's start with the department number because I want to do this based on the department so let's say and this is the only example I could think of to show you guys a trigger so let's say an employee is assigned to a different department and we need to update and let's say we want to track those changes well we'll put this in here so it can track the new department for that user and then this will be at integer and press comma and let's get the next step so let's go with our action so this is the actual action of what happens as far as the type of query that's running and like I said I want to show you guys after an update so let's go action and then vour and let's make the number of characters around 50 boom just go on put a commment there and then lastly Let's uh put the action dates so this will track the actual time that this change happened within that table and then we can set the type to time stamp that's a good one to actually use and let's press enter and then close out our parentheses as well as the semicolon press enter and that should create our table for us and let's go with a select right fast just to check it out so select store from our employee _ audit table and let's close it out with the semicolon press enter and it won't show anything cuz there's no data in there yet but our table is created so now let's go on and create our trigger and we have to change our delimiter again because of the way I'm writing this trigger so let's change it to our for slashes again and press enter and the way you create a trigger is as follows so so create trigger and then let's name our trigger so we can call this employee uncore Audits and then underscore we can just specify that it is a trigger and let's go on and type out the details of our trigger so after an update on the employee table and then for each row enter and then this will be the beginning of our statement so let's type begin and press enter and then let's do our insert into our employ employee audit table and then we want to update specific columns within that table so let's start off with the department number and then the action we want to update the action column as well as our action date and let's close out those parentheses and now let's type in our values so values and there's a built-in way of looking at the change like you can type new and this will show you the new information that's being inserted into the table or you can also look at old which is the current data that's there but we want to look at the new data we just want to track that new data that's being inserted into the table so new and then it's coming from the Departments number and then also let's say we want to just we know this is a update trigger so let's say we only want to put text in there that just says update so we can specify you know specific text within a column so we could just put updates so each one of the triggers that will happen it will put update in there and that's a way of tracking our updates then also the action date we can use a built-in variable which is now and I'm going to type it in all cap so you guys can understand it but it's just a built-in function that you can use and that will bring the current time and date and put that into our field so let's close out our main parentheses and then let's go down and put our semicolon at the end CU that'll end out our insert query and let's press enter and just like we had to type begin we have to type in that was the whole purpose of changing our delimiter so let's type in then our delimiter that we have currently set which is the for slash4 slash so let's go and press enter boom so that took and that created our trigger now let's change our delimiter back right fast and we want to change it back to our semicolon so when we run our following queries they'll work properly and so let me show you guys how to show the triggers so so it's a very simple command all you have to do is type show triggers with an S put semicolon at the end and this will show all our triggers right there as you can see that is our trigger that we just created so the employee audit trigger and it gives you all the information about the actual trigger and if you have other triggers they'll be listed here as well so now let's find some information to update so let's type select and let's just select some information from our employees table so let's type that in semicolon at the end press enter and maybe I should have did it this way let's throw in the limits after that so we can just pull up the top 10 records that will be displayed so this is a easy way of finding something to update so let's say we want to update this first line and it's all based on the department number like I said once you update the department number it will track that in our audit table so let's go on and run a update query so let's type updates and then employee table and then let's set our department number because that's what set Sofi trigger so department number and then let's change the department number to let's say Georgie forcillo works for the help Des department so let's go and throw the help Des ID in there department number in there so it's 010 and then close that out and then now let's do our wear Clause so wear employee number equals and let's go to and grab their employee number right fast and drop that in there and put our semicolon at the end and let's go to and run our update query and looks like we have an issue hold on let's see and I believe I made a mistake when I created the table so this is a good way of showing you guys how to alter the table right fast and let's describe our employees table and I just want to see what the character is set for and yeah it looks like I made the wrong setup for the audit table that we created and yeah we can look at that right there just by describing this table as well so it's our audit table so employee Audits and let's check this out and it looks like yeah I put the department number as an integer when it should be character for so that's the mistake I made and so I'm glad this actually happened so I can show you guys how to fix issues like this or track them down because as you can see this error it just kind of gives you it gives you information you know don't get me wrong it gives you information but a lot of times you got to dig sometimes the errors are vague and you got to kind of dig through where the issue lies and so let's alter that table right fast that employees audit let's go right here just type in the front so alter table and then employees audits let's delete that off and then let's modify a column and this is changing the type so all we have to do is specify that column which is our department uncore number and then we can specify what we want to change it to and so that's why I described this so I could just typeing in you know properly so Char and then put four right here and let's put the semicolon at the end and move the mouse out the way so you guys can see but this will alter our table and now let's describe it again and that way we could see the changes and boom we are good to go so let's go on and make our changes in our employees table again so let's scroll up and let's see where we can find that change yeah this is the change we want to make so update employee set department number to d010 where employee number is 10,000 so let's press enter and we shouldn't run into that area yeah we don't so let's select our our employees audit table so select store from employee audit so you guys can see the information that's presented to that table and that lets us know our trigger actually worked and as you can see it pulls in the department number the new department number and see this is just a basic understanding or basic concept of setting this up you can set up Tri triggers that are way more complex like for instance you can track the old data as well as the new data you can create more columns in here and that's typically how you'll see it in a production environment it'll track old and new changes that way you can correct those changes if need be by looking through the Audits and now let me show you guys a little bit more of this audit log or this trigger in action like a while back I created a query that would go in and randomly insert departments within all of the users within the employees table and so let's run that again so you guys can see the rows that are inserted throughout this table and I know it'll populate this audit there'll be a whole bunch of changes or updates within this table but I just want to show you guys how these triggers actually can be very beneficial so let's hit paste let's paste it in there and this is something that I wrote a while back and I'll just break it down for you right fast but it's basically going to update the employees table it's going to set the department number to equal and then we have a nested query and this is basically select the department number from Department table and then we ordering it by random so this is a built-in function and then we can limit it to one and that will do that for every rle within the employees table and so let's go down and press enter and let this run and then we'll look back at our audit table up here our employees audit table once this completes and we'll see all the Okay cool so our update query has ran successfully as you can see it made a couple changes or not a lot of changes and so let's select our audits table again and press enter and let's see all the changes as you can see it's like 30,000 cuz it went through every record you know what I'm saying and this will keep going as you run updates on that table so let's say you make a lot of changes depending on the department it'll list them all here and that way you can trace back any issues that may have been made in the table unintentionally all right so after showing you guys these store procedures and triggers I wanted to show you guys another command within my SQL which is process list and this is essentially like a to-do list for the database server and it keeps track of all the tasks the server is currently working on and each task similar to a operating system is called a process and can include things like executing queries handling connections or performing updates and so let me show you guys this Command right fast and it's very simple all you have to do is type show process list and then semicolon at the end and this will show you all the process is currently running on a server as you can see it breaks everything in columns so you got your ID you got your user you got your host the database that's associated with that user so to speak the commands the time State you know information about the server so as you can see it's pulling in that process right there show process list that's the query that I just ran using my root account so you can see pretty much any and everything that's going on on the server and I wanted to let you guys know this an important command to understand because it helps you manage all the processes and then also understand the performance of the server okay so the next thing I want to cover is MySQL dump and MySQL dump is a built-in utility that's included with mySQL that allows you to make backups of your entire database and you can also get very specific with it and back up just specific parts of the database as well and this tool will help you save your data so you can restore it if something something goes wrong or let's say you need to move the database to a different server that provides more performance for the amount of data that's included in it well this allow you to do that as well and so just to kind of cover it MySQL dumps it takes all the data and structure of your database and save it into a file and it's typically a SQL file so if you guys remember earlier in the course I imported that test database it had the extension of SQL so I'm sure they created that test database for others to use using MySQL dump and so when we get into the examples I want to show you that it's is like very super easy to use and you can run it right from the command line and another thing you don't have to stop like my SQL in order to create the backup it'll work while the database is running and a super cool part about it it will ensure that your data's Integrity is maintained during the backup process and so let's go through a couple examples right fast and what I need to do is exit out of my SQL because like I said it's a separate utility that comes along with my SQL and so let's go down and back up that employees table with all those changes that we made so let's go with mySQL dump and then we have to specify a user in order to connect to our mySQL database and we want to use our root account and then we can put- P for our password and it's similar to how we logged into it and also I forgot about this we have to put pseudo in there so we can connect to the database and now let's do the employees database and then let's specify the file that we want to name it so we have to put the right arrow just like when we import it we use the left arrow pointing to the left we need to use the one that points to the right because we're grabbing all the data or the database from within the SQL server and dumping it to a file and this is the structure and everything associated with it it even includes the data that's within the database and so let's name name our file EMP and then let's goore backup and then it will store it in the current working directory we are in which is our home directory so that's fine for now but you can specify another directory by typing out the full path we'll just roll with it like this and press enter and then it'll ask us for our pseudo password because we had to type pseudo and then there is no password for our roote account so let's press enter and go down and Export or dump our database and as you can see it didn't take long at all and so let's LS our current working directory and you guys will see the employees back up and then let's put this in human readable form so you guys can see the size of the actual database file so it's 176 megabytes that's how big the actual database is and let's log back into our database right fast so P sudo my SQL and then our user which is roots and then- P for password press enter and press enter no password and let's go show databases and semicolon at the end press enter it'll show us all our databases so what I'm going to do is drop the employees database and we all remember how to drop a database and all we have to do is type drop and then the database and then the database name we're going to drop that employee database and semicolon at the end and let's go enter and that will delete that database and so let's run these show again right fast and we'll see that that database is gone and we're like oh crap made a mistake I did not mean to delete that database well one good thing about this we created a backup of our database now in order to restore the database we have to have a database within the MySQL server with that previous name and so let's go on and create a database and this will be a empty database so create database and then you want to name it the exact same name that it was before so employees with a comma press enter just go through and show the databases again we'll see that we have that database there but it doesn't have any data or tables or anything in it we're going to get that from our backup file so now that we have everything set up we can exit out of my SQL and let me show you the command to restore the database so P sudo my SQL and then we have to put our user in there so roots and then- P for the password and then we have to specify that database that we just created that empty database because it needs somewhere for it to go it's not going to just create that database for you it's just going to modify that current name database that we have in there to represent what we had previously within the backup so now we just put that left arrow in there just like we did when we imported before and then we pull in our employee backup and press enter it's going to ask us for our password we don't need a password and press enter and it'll go through and import all the database all the tables and everything within that database and then we'll verify this back up once we complete this I'll go through and show you guys the tables that all the tables are restored and pretty much everything the data is there as well by running a couple select queries so I'll be back when this finishes all right and so the import is done let's go in to my SQL and check our database and just verify that the tables are there and we can also run couple queries to see if the data is still there and so first off let's uh show databases which we know it was there before cuz we created it before we logged out of the system and imported our file now let's type use employee database and press enter and then now let's show tables and semicolon press enter so as you can see we can tell right off that we have our full database back so like for instance I created that audit table for the auditing so it has a trigger associated with it so if any updates to the employees table departments column it will track it within this a audit table that we created so that's a Telltale sign that we have our database back and that's different from the original database that I installed on the system and actually let's just run a select on that table right fast so you guys can see that that information is back and so let's go from employee audit and semicolon press enter as you can see it has all those records there it hasn't changed so all our records are back as well so we can do the exact same thing by checking other tables if we need to like this is the employees table and we know from the original database we didn't have this column in here with the Department ID and so that lets you know that this is the database that we dropped and there you have it you have successfully completed my MySQL course and I hope you've gained a valuable insight into this powerful relational database management system so whether you're a beginner or already had some experience I trust that you're now more confident in working with mySQL and also MySQL is a fundamental tool for anyone dealing with data and your new found knowledge will be an asset in various domains from web development to data analyst and remember the best way to become proficient in myql is through practice and so keep experimenting building and refining your skills with determination and dedication you can become a master of my sequel so thanks for choosing my course I wish you the best of luck in your database Endeavors and may your MySQL journey be filled with success and of course keep protect okay\n"