MySQL Database Designing | MySQL Tutorial | Eduonix

all right so welcome to your first database section of the full stack web development course we're going to start out with one of the most popular types of database management systems and that's MySQL so MySQL is an open source relational database system and it's one of if not the most popular of its kind now a lot of what we do and talk about in this section is going to pertain to most relational SQL based databases for instance in the next section we'll be looking at postgrads which is also a relational database so a lot of what we learn in this section can be used in that one as well all right - QL can be used with many different programming languages we've already used it with PHP back in the PHP section but it can also be used with languages like Ruby C C++ Python and many more now like with most relational databases the data or the records are stored in a table and the table is made up of columns and rows it's almost identical to a spreadsheet in terms of what it look of what it looks like we have columns at the top and then rows of data in those columns MySQL obviously uses the SQL language which stands for structured query language and this is a language that's dedicated to working with databases all right I'm not going to talk too much about that right now because we do have a lecture that's dedicated to the SQL language all right so let's take a look at some of the advantages of working with MySQL so MySQL offers high performance and it uses a unique storage architecture that allows databases to be configured for specific applications which gives us better performance results ok uses memory caches full text indexes and other performance enhancing solutions it also has high availability and data integrity to minimize any downtime there's also additional solutions that can be used like MySQL cluster and MySQL replication to even further reliability and availability MySQL also offers security features that can ensure absolute data protection only authorized users get access to certain databases SSH and SSL are supported and many other security features ease-of-use is another great advantage like I said MySQL is the system that most developers learn how relational databases work at least in my experience it's really simple to get up and running and it also has a really clean syntax there's also great support and documentation available - Qualcomm has a ton of examples tutorials and support available there's also a huge community of developers and administrators that are willing to help anyone that asks ok many different forms across the internet and finally MySQL is open source and free ok a lot of corporations are hesitant when it comes to releasing open source software but MySQL is able to do so and still provide great support alright so there's a couple of small disadvantages of MySQL it does have some minor stability issues according to digitalocean MySQL tends to be a little less reliable than some of its peers the issues mostly pertain to references transactions and auditing so any projects that will use these features should just look into it a little more the next one is scalability and this isn't really unique to MySQL it's relational databases in general it can be kind of challenging to scale past a certain point some of some other types of databases like no SQL are much easier to scale deployment has also been a bit laggy compared to other systems this has been mostly since Oracle purchased it from Sun Microsystems there's only been one major release in the past several years or so and then finally there's some limitations with MySQL like any software or tech ecology it's built it's not built for every situation imaginable it's not fully SQL compliant and there can be some limitation in areas like data warehousing and fault tolerance so the requirements for MySQL it is cross-platform and you can use it with Linux UNIX solar wind O's Apple CPU recommendation is at least a dual-core 2.0 gigahertz or better 20 gigabytes of hard drive space plus and then at least 2 gigabytes of RAM alright so you can run MySQL on a very very low-end system alright obviously it's not going to give you you know great performance but you can in fact run it on older low-end machines all right so there's a ton of MySQL tools available of course it has its own client its own command-line interface PHP myadmin is one we'll be using that is a web-based GUI we can create tables run queries pretty much do anything and you can do it within a GUI it's not a command line based MySQL workbench is another one I've used that quite a bit nice diagramming capabilities sequel Pro Heidi SQL in my DB studio or some other ones I haven't used these personally but I have heard good things about them and there's a lot more as well alright so it's a very brief introduction to MySQL in the next video I want to talk more about SQL itself structured query language we'll take a look at some examples and then we'll move on to install MySQL and we'll start to work with some queries and different clauses and so on all right so I will see you in the next video all right so we briefly looked at MySQL and what it is some of the advantages disadvantages now we're going to look more at the structured query language that is QL runs on so SQL as I said stands for structured query language and it's a programming language that was designed to manage stored data in a relational database management system ok SQL is defined as declarative programming it uses the declarative paradigm which is basically a language that uses code to express the logic of computations without describing its control flow and also has some elements of procedural programming using procedures and routines SQL is not something that only works with MySQL or is built for MySQL there is actually many database systems that support SQL such as postgrads which we'll also be looking at in this course alright SQL is based on relational algebra and tuple relational calculus and as far as its scope it includes data insert query update and delete okay it has a ton of of different expressions and operators which we'll be looking at throughout this section so some of the language elements we have clauses which are constituent components of statements and queries and these are sometimes optional so something like a where Clause expressions which can produce scalar values or tables consisting of columns and rows predicates which specify conditions that can be evaluated to true false or unknown and are also used to limit the effects of some statements and queries or to change program flow now we have queries which are used to retrieve data based on a specific criteria and then a statement is all of this together which may have a persistent effect on the database schema or may control program flow connections sessions or Diagnostics so here is an example labelled example of a statement so here we have the update Clause saying we want to update the table country and then here we have a set clause we want to set the population equal to this expression which is population plus 1 okay that's considered an expression then we have our where clause we're saying we're name where the seat where the column name is equal to the string of USA which is considered an expression alright so this part here is considered the predicate and then all of it is a statement okay so that's an example from the Y SQL documentation all right so when we create our tables we can define a primary key constraint so this will uniquely identify each record okay primary keys must contain unique values and they cannot contain null values most tables should have a primary key although they're not required especially if you're if you have some kind of junction table or relationship table they're not required and then one can table can only have one primary key all right you can have multiple foreign keys which we'll get to later on but only one primary now we can also set a field to be auto increment and this allows a unique number to be generated when a new record is inserted into a table so for instance let's say we have a table and the last the very last record that was inserted has an ID of five well if we go and we insert another record it'll automatically be six okay and then seven eight and so on and this is only if the field is set to be auto increment all right and this is usually added to the ID field of a table but of course you could call it anything you want it doesn't have to be called ID all right it'll increment by one by default and you don't need to specify an ID when you do your inserts okay so that's a really nice advantage is you don't have to worry about creating an ID all right we can also set a default constraint and this is used to insert a default value into a column when it's created and this will be added to new records so in this example here we're creating a table called test with an ID and a city field and we set for City a default of New York so that means that when we insert a record if we don't if we don't specify what we want the city to be it'll be New York that'll be the default alright and you can also set a time stamp as a default as well which is really nice if you want to get the date and time of when when the data was inserted without having to actually define it in your code alright so let's take a look at some common operations and queries these are just the most the most use you have select which will fetch records and this has no persistent effect on the database it's not going to change anything it's just used to grab the records and display them insert will obviously insert new records update will update records that already exist in the table or in the column and then delete well delete records all right now we have some common clauses and operators such as where which will limit rows returned by a condition alright so we can specify for instance select select name where where ID equals 5 so then it will return the name of the record that has the ID of 5 join will combine rows from two or more tables like excuse me is used in a where clause to search for a specific pattern okay this is used in searching for instance in will allow us to specify multiple values in a where clause and then between will select values within a range alright so in the next video we're going to install MySQL I'm going to give you a couple different ways to to use it to install it and then we'll move on to start to use SQL and write some queries and work with our database alright guys so there's a lot of different ways that we can get MySQL up and running on multiple platforms we have to be in Windows and we could use the MySQL installer from the official - Kuehl website but I want to install xampp or zamp and we actually did this back in the PHP section if you've been following along zamp gives us an Apache server PHP and MySQL okay technically it's Maria DB but it's a drop-in from MySQL so everything is the exact same it's fully backward-compatible and it also gives us PHP myadmin which is a really nice web-based GUI tool that allows us to interact with our database visually through a GUI - a graphical user interface but also gives us a command-line option which is mostly what we'll be working in because the goal is to learn SQL so I think that's a better option than to just work in the command-line so that's what we're going to do you want to go to Apache friends org and we're going to download for Windows okay I'm I'm actually not going to save it because I already have it downloaded so just download that and then open it up alright you might get this um this warning about your user access control system and some conflicts but I've never actually seen any issues with that so we're just going to click OK and then go through the installer now there's a few different components that we don't need here so we're going to keep it patchy in MySQL I'm going to uncheck FileZilla mercury and Tomcat and then I'm also going to uncheck webalizer and fake sendmail okay we don't need those so click Next C Drive xampp folder that's fine and click Next again it's just going to open up this bit Nami com website we can just close that out and then click Next and it should go to proceed to install all right so that's all set now it's going to ask if we want to stock the control panel I'm going to uncheck that and click finish and the reason I didn't start it up there is because we want to run it as an administrator so if we go to the C Drive or wherever you installed xampp' and then just go to this xampp control exe and then click run as administrator all right so you should get something like this all right now we could start started from here but before we do that I just want to install it as a service so we're going to click this red X and if you don't if you can't click this X that means that you didn't run this as administrator so make sure you do that all right we'll also install MySQL as a service and let's start up Apache start up MySQL and we should be all set all right so let's see if we can go to localhost / PHP myadmin and there we go all right now notice down here we're getting an error message or a warning at least that says we're connected we're connected as root with no password so we want to fix that we want to add a password to our root account so if we go to user accounts and then go to let's see where it says root and localhost see how it says no password we want to edit privileges and then click on change password and then just add in a password here alright then we'll click go and now when we try to go back we're going to get booted out all right so to fix this what we need to do is go to our C Drive C C Drive and xampp and then PHP myadmin and then there's a config file which is right here config Inc pH PHP so we're going to go ahead and open that up all right now in this file if we go down to right here it says password you'll see it's blank you just want to put in the password that you just created all right so we'll save that and let's go back and reload and now everything is all set okay we can now get in alright so in the next video we're going to start on SQL now we can easily create databases from here we can can add tables easily through this interface which I'll get to later but the what's important is that you learn how to actually write out queries and stuff so we can use this command line interface here to do that alright so I will see you in the next video alright guys so now we're going to start to work on our database so we're going to build an online store database we're not going to focus on the front end application or programming or anything like that we just want to build the structure the data structure all right so we're using PHP myadmin which is a graphical tool and we can easily create tables and fields and stuff through the graphical user interface but I want to get you familiar with SQL so that's what will mostly be dealing with his SQL commands so if we click on the SQL tab right here it'll take us to a command window and here we're going to say create database and we're just going to call this we'll call it my store alright and let's make this a little bigger and they'll go ahead and click go ok so it gives us a success message and now over here you can see we have my store now that all the commands that I'm running you could just as well run inside the MySQL shell or any other client really alright so the platform the PHP myadmin system we're using is irrelevant when we're talking about SQL commands you could run these for anywhere alright so let's go back actually let's click on the my store database that way we're actually using that particular database and then click on MySQL I'm sorry SQL in our menu here alright so you can see we're in that current database now first thing we're going to do in this database is create a customers table so we'll say create table and these doesn't have to be in caps it's just preference I like to keep my statements in uppercase alright so but it's not required so let's create a customers database I'm sorry table now inside here we want to create all of our fields so let's say we want an ID that's going to be an int and let's say it's going to be not null and we also want this to be auto increment we wanted to increase our increment as we enter records okay so to do that we can say Auto underscore increment you can see there's a little code drop down here that we can select from all right in addition to the ID let's do first name and now as far as you are your formatting for your field names or your column names you can use underscore like this some people will use the camel case like this or even uppercase in the beginning as well but we're going to stick to the underscore method okay so we'll say every every every time there's more than one word we're going to separate it with an underscore alright so first name that's going to be a variable character or a bar char and then we just want to say that the max for this will be 255 all right and let's just copy that all right so let's make this one last name okay and then we'll do email what else let's do we would probably have a password if this is an online store okay let's also do in address or street address ok we'll add a couple more here so street address and we'll do city state and zip code okay so that looks good now we want to let the system know which one of these we want to be our primary key and we want the ID to be the primary key so down here let's add on primary key and we want to say ID all right so that should do it let's go ahead and click go and looks like it went ok and if we look in my store you can see we have a customers table if we click on that there's no data in the table but if we click structure you can see all of our fields alright and everything is a bar chart aside from the ID ok and you can see the ID has a little key next to it telling us it's the primary key and it's also auto increment so now we want to do is we want to insert some data into these fields all right so let's go back to our SQL tab here now notice we have this this columns thing right here with all the different fields and it prefilled this with a select statement that's because right now we're in the command line window but also inside of the customers table all right that's why we're seeing this format I actually don't want to be inside of a certain table so let's click on this database my store and then click SQL and now we're just in the general database not inside of a specific table so now we want to do an insert so we're going to say insert into and then the name of the table we want to use in this case customers and then inside our parentheses here we need to specify the field that we want to add that we want to add data to so let's say first name last name email what else password let me just open this up in another window just oh now I can see them right here all right so we get first name last name email password street address city state and zip I think that's everything all right now notice that I'm not using the I'm not putting the ID because the ID is auto increment so that's going to get inserted automatically now what we want to do is go outside of our parentheses and then say values and then open another set of parentheses and we want to insert the data that we want all right so we're going to go in the same order that we did here all right so let's go and let's say first name we'll do I'll just put my name okay first name last name email password normally you would have your whatever programming language you're using you would have that increment your on increment encrypt your password so that it's not a plaintext password like this but we're not going to worry about that now okay street address we'll say 20 Main Street this isn't my actual address by the way city will say Boston Mass and then the zip code will say oh 1 2 9 4 all right so just make sure that this matches it's in the correct order all right and notice they have quotes around these because they're all strings they're all text strings so let's put a semicolon there and we'll click go ok so it says one row inserted now if I click on database my store and then click on customers you can see that our record is now in the database all right we can we could edit it from here if we want and we can do all kinds of things through the GUI but we're not going to do that we're going to just stick to the command line or the SQL queries now we can also insert multiple records in one query go to SQL and this is pretty long so I'm going to paste it in alright so you can see here we have what six more customers and all we did was we specified insert into customers all the fields and then values and then we just have some parentheses with all our fields and we just separated these with a comma all right so let's go ahead and click go and now if we go to our customers table you can see we have all these different customers added alright so in the next video we're going to look at ways that we can query this data alright guys in the last video we went ahead and created a database a table and added some data so now we're going to go ahead and we're going to run some select queries all right so let's go to SQL and for now let's just do select we're going to use this asterisk here this represents everything so we want to select everything from and then we want to put in the table name so let's say customers and and that was a semicolon click go and I can see it's doing just that it's getting everything it's getting all of the all the data all the rows and returning it back to us alright and this is why I wanted to use PHP myadmin because you can see our result is very clear we can see all the different fields and all the data alright it's rather than in a little command line window so that's selecting all from customers so let's go back now in addition to selecting all of course we can specify what we want to select so if we say select first name last name from customers and we click go now you can see we get our results but we're only getting the first name and the last name ok we're not getting any of the other data now open to this point we have been getting everything all the data whether we're just getting some of the columns or all of them so what if we want to limit it to a certain condition all right so let's go back and let's do I will say select all from customers now if we want to specify a condition we can use the where clause ok so we'll say we're and let's say we want we're state equals MA all right so let's try to run that and we're getting an error and that's because I think this needs to have quotes like that okay so now it's giving us the customers that live in mass you can see we had I had two two of them from New York and those ones not coming up all right and just as well if we wanted to search for let's say a specific user with a specific ID then we could say select all from customers where ID is equal to two all right and now you can see that's only giving me John Doe because his ID is two now we can use other operators aside from the equals for instance let's say we want select all from customers and we'll say we're ID is greater than three okay so instead of equals we can use the greater than operator click go and you can see we get everybody with an ID 4 and higher all right and that's particularly useful with things like money if you have transactions with balances things like that that can come in really handy all right and just to quickly show you some of the different operators let me bring this in all right so this is from w3schools comm you can see we have equals we can use not equal so this less than greater than greater than less than we can also do greater than or equal less than or equal and we have these three here we have between which is between an inclusive range like which we can search for a pattern and then in which allows us to to find multiple possible values for a column all right so let's take a look at these three here so we'll go back to our SQL window and we'll say select let's say we want the email from customers and we'll say we're ID is between two and four all right so let's see what that gets us actually we'll get the ID as well all right so let's go ahead and click go and there we go so we have customers with the idea of two three and four all right so that's between now let's take a look at like let's actually take a look at the data real quick all right so let's say we want to get all the cities that start with the letter B are wanting a data from everyone that lives in the city that starts with the letter B all right so let's go and say select all from customers and then we'll say where to where city like and then inside quotes we're going to say B and then percentage percent symbol all right actually let's make that an uppercase B so click go and now you can see we get everybody Boston and Brooklyn okay so those are the cities that start with B so you can do patterns it doesn't have to just be one letter okay it could be anything so let's say we'll say select all from customers where city like and let's say be Oh percent okay the percent is the wild card so we're saying where it's Bo anything okay percent can be anything and you can have a percent on both sides as well alright so let me just copy that so type it back out and then we'll click go and now you can see we're only getting from Boston because it's B oh okay Brooklyn would be Br all right so if we say let's put a percent and then an O and then another percent this is going to get cities that just basically that have an O okay no matter what is before or after it all right and you can see Boston and Brooklyn is what we're getting okay so that's like now we also have in and what n does is it allows you to easily have multiple values in your where clause now we could do this without in let me show you that way first so if we say select all from customers where let's say we're city is equal to games vary and then we're going to say or city is equal to Salisbury okay so let me just copy that and then we'll run it and now you can see we're getting all the records who are in either Salisbury or Eames very now this is fine if we're just searching for two cities but imagine that we want we have we're searching for let's say ten cities or 20 cities all right so that will get quite long so let's go back and paste that in all right so we have we're City equals something or sit equals something and so on so in allows us to shorten eyes so instead of doing all that we can say we're a city in and then open up set of parentheses and we'll say aims vary Salisbury and you could just keep going on all right so if we run that we get the same result all right so we're going to go ahead and stop here and we will continue in the next video all right guys we're going to get into multiple tables and join soon I just want to touch on something real quick so I want to look at distinct I want to look at aliases and concat okay these three things and this will be really quick all right so we're going to go to our SQL window here and what distinct does is it does just that it returns a set of distinct values so if I were to say select City from customers and we ran that it's going to give us just each record just the city but you can see that we get repeating values here so all is very twice Boston twice so to get just a list of cities with no repeats we can just add in here whoops and I'm going to do that you can just say select distinct city from customers okay so if we go ahead and run that it's just going to give us each city once alright so that can be kind of useful now we also have aliases which allow us to basically format the return column names in a nicer way all right we can also use aliases within our queries so let's say we want the first name first on the score name to be formatted a little nicer so we can use this as keyword and then we could just say first name or something like that but if you want to use a space which I do I want to format it like that you can see that we're getting an error now if we want to use spaces and then we can just use quotes around it like that all right so take first on the score name as first name and then last on the score name as last name from customers okay we'll run that and now you can see that the column names are formatted nice and neat all right now we also have concat which allows us to basically combine columns so if we say select can cat and then able say first name put a comma here and then let's put a space and then last name and let's make that add name I'm going to put that in quotes as name all right and then we'll do another concat and let's say the street address street address then we'll do that and then city and state and we'll do that as address okay and then we'll say from customers all right so let's see what that gives us so now if we go down here you can see that we have just two columns here we have name which can cats the last name - the first name and then address which concatenates the street address the city and the state okay so we can easily combine different results like this different columns alright so I just wanted to touch on those those things before we move on to John alright guys in this video we're going to look at updating deleting and also altering tables with alter table so first thing we'll do is update okay so in our customers let's say that let's say we had someone get married let's say that Janet got married her last name is no longer Thompson let's say she married Kevin so she's going to be Janet Smith so we need to update that record alright so let's go to our database my store and then SQL and we're going to say update and then the name of the table so update customers and let's go let's actually go on the next line and we're going to say set lastname equals Smith and we can put a comma here and we could edit other fields as well but we just want to update the last name and then down here we'll just use a rare clause and we'll say where ID equals Handley see I forget what I'd be she was she's ID for all right so we're ID equals 4 all right so update name of the table set you want to set the fields you want to set and then add the where clause so let's click go alright and then we'll go back to customers and now you can see that Janet is now Janet Smith alright so pretty simple now let's say that Stephanie is no longer customer so we want to delete her she's an ID of 7 so let's go to our SQL window and we're going to say delete from customers where ID is equal to 7 and that's it click go now if we go back to our customers you can see that the customer with the ID of 7 is now gone now what if we wanted to add a column to an already existing table so let's say for instance in customers we want to add a balanced field okay our balanced column so let's go to our SQL and we're going to say alter table customers and then we'll say add and then the column name so let's call it balance and then the data type okay so we'll say var char 255 alright let's go ahead and run that okay successful so now if we go and we look in customers you can see we now have a column called balance and anything that's in it currently is going to be null because we haven't added anything to any of the balance column all right it's just all no so if we want to remove a column we can easily do that too it's actually going to be the same thing except instead of ads will be removed so alter table customers and then you would say remove and then the table name of balance alright but I'm actually going to keep balance so I'm not going to actually run it but what I do want to do is change the data type I set it as a varchar' but I actually want to set it to a float so instead of add or remove we're going to say when say modify column and then the column name which is balance and then the data type which we want as float so let's click go and let's take a look okay it's still no but if we click structure you'll see balance is now a float okay so that's how you can alter tables or all the columns now let's say we want to add a balance for somebody so we'll say update customers and so say set balance equals 500 where ID equals 1 okay let's take a look at that and now you can see the user with 1 has a balance of 500 all right so that's how we can update delete and alter tables all right guys so we've been working with one table so far which is the customers table now we're going to create a couple more okay so this is a store so we need to have products table so let's go ahead and do that okay so we'll go to our SQL window here and let's say create table products and we'll open up some parentheses here and inside here we want our fields so we're going to have an ID which will be an int it's going to be not null and we also want it to be auto increment ok we'll also have a name using a name for the product it will be a varchar' of 255 max it'll have a price and price is going to be an int alright and that's that's what we'll do for fields now we want the primary key to be the ID all right so we're going to go ahead and run that and if we look at our database now you can see we have a product table now let's insert a couple products so in our SQL window we'll say insert into products and we want to define the fields you want to insert into so name and price okay then we'll say values so for the first product we'll just call it product one and then for the price let's say ten all right so if we run that that should put a product into the table all right so let's go back now I'm going to just paste this in we just can add a couple more product two three four and five and we'll click go and now we have a bunch of products in the products table now we're going to create a third table called orders and that's going to hold the customer ID and it's going to hold the product ID okay so let's go to SQL and we'll say create table orders all right and in here let's say we want an ID going to be auto increment ID int not null okay then we're going to have the order number which will be an int as you keep keeps adding the oh okay order number which is an integer then we're going to have the product ID which will be an int okay then we'll have the customer ID then let's do the order date okay the order date will be a date/time field all right let's say we want this primary key to be ID all right now the product ID and the customer ID these are going to be foreign constraints or foreign keys and what that means is that the product this field is going to pertain to the primary key of the products table which is ID this will be to the primary key of the customers table which is ID all right so let's define these as foreign keys from down here we're going to do this just like we did the primary key I'm going to say foreign key and we're going to add in the field name which will be customer underscore ID now in addition to that we have to let it know which table and which field this pertains to so we do that with references so we're going to say references and then this is in the customers table and it's going to be the ID field all right so that's how you can define a foreign key now I'm going to copy that and I'm going to do the same thing for the product ID and it's going to be the products table and the ID field all right now the last thing we want to do is this order date I don't want to have to manually put that in I want it to have a default of the current timestamp so what we can do is just add on default and then current timestamp all right and that will do it automatically for us so let's go ahead and run that all right and if we check it out we can see now we have a table called orders okay and it has all those fields we just created now you see the little gray key here that's telling us that these are foreign key fields now foreign Keys prevent data corruption and what I mean by that is if we have in orders if we have a record and it has a product ID foreign key of three that pertains to the products table ID of three all right now if we were to delete that that product from the products table and then we have this linking to that product that's not there that's going to cause a problem all right so what this does is it makes it so that we're not allowed to delete something if it has a foreign key somewhere else okay so if we have an order that pertains to that that product to that customer then we don't want to be able to delete that product or customer unless we delete that this first the order first all right so hopefully that makes so let's go ahead and add some orders all right so we have some customers so we'll pick one of these and then we have some products so we want to mimic these customers buying a product so let's go to our SQL window and let's say insert into orders okay and here let's say ID yeah ID what other fields do we have here order number then we have customer ID and Product ID and actually I think that's it because the the order date will automatically be put in alright so after that we're going to say values okay so actually the ID we don't need to do because that's Auto increment so order number which I believe is an int so let's just say 0 0 1 and then custom ID we'll take the first customer and then we'll take the first product all right so let's click go and then if we go and we visit the orders table you can see that we have the order number which it just put 1 actually no what we don't really need the order number but we'll just keep it I did do 0 0 1 since this is an int it's going to trim off the the leading zeros alright we'll just leave that for now so just looking at this table we don't we don't get that much information from it alright we can't see the the customers name we can't see the product name it's just some IDs all right so this by itself isn't very helpful but when we use it in a joint statement we can use it to grab information from both tables the products and the customers and that's what we'll be doing in the next video all right I just quickly want to add a couple more orders so I'm going to go in here and paste it in alright so we're just adding in the order number which is just 2 and then we're adding 1 to it and then the customer ID and the product ID okay so we'll go ahead and run that and now we have a bunch of orders okay so in the next video I'll show you how to write joint statements alright guys so our online store database now has products customers and it has some orders which links the customer to the product that they purchased so we want to do now is we want to be able to query orders but if we were to query just the orders table this the data that we get which isn't very helpful all right so we want to be able to get for instance the customers name along with the product title or the product name not just the ID because that doesn't tell us anything okay so what we can do is we can write joint statements okay joins are used to combine rows from either two or more tables based on a common field between them alright so in most cases it's going to be between the primary key of one field of one table and the foreign key of another so there are different types of joins okay so we have inner joins left joins right joins and full joins let me just open up a page from w3schools comm and this this gives you a good explanation all right so an inner join returns all rows when there's at least one match in both tables all right a left join and that's the most common a left join returns all rows from the left table and the match rows from the right table okay the right join will return all rows from the right table and the match rows from the left table so it's the opposite and then a full join will return all roads when there is a match in one of the tables all right and you can read more about this here and there's some other good articles too if you just do a search for in a joint verse right join or left join all right so let's go ahead and open up our SQL window and we're going to write I'm going to write a very simple inner join so let's say select and let's say select customers dot first-name and let's get customers dot last name and notice that I'm using the actual table name all right and I'm doing that because you may have some cases where you have a column name that is a duplicate between tables so you might have a product name and my have a customer name and they're both called name so you can easily just make the distinction by using the table name and then dot and whatever the column all right so we want the first name and last name from the customers table we also want the orders dot ID and let's get orders dot order number all right we're going to say from customers ok so right now it's only from the customers table then what we want to do is we want to join in the orders table using an inner joint so we'll say inner join orders ok now what we need to do is tell it which fields to match so we do that with on and then we'll say customers dot ID equals orders dot customer ID so we're matching them by these fields here alright so let's go ahead and let's copy it and then we're going to run it ok so we're getting an error unknown oh this should be order underscore number ok so now we're getting the first name and last name and we're getting the ID of the order and the order number okay so we're joining those two tables together and we could probably do without the ID just get the order number alright alright so let's go back and let's do let's do a left join now let's let's go do away with the ID and let's get the ordered date so select customers dot first name last name and let's do orders dot order number in orders dot order date I think that's what it's called and we just yeah order date so we want to get this from customers and then let's do left join orders on customers dot ID equals orders dot customer ID and then let's do an order by and we'll do it by the customers dot last name all right so let's go ahead and run that see what's going on here well I spelt last name am I missing here order by customers dot last name oh we don't want this comma right here okay so now we're getting our first name and last name and the order is by last name we're also getting the order numbers and we're getting the order date now with this left join you can see that we're getting we're getting records that don't even have an order number order date it's getting all of the customers even if they don't have an order all right so in this situation and inner join would be the better the better of the two to use now let's see what happens if we do a right join just fix this okay so we'll just change this to right join and now you can see that this actually works out better kind just like the inner join we're not getting customers that that didn't order anything now another thing you might want to do is bring in the product info okay you might want the product name so to do that we can have multiple joins so let's go to our SQL and let's let's paste that in what I just had you're just going to fix this up alright so in addition to this stuff let's add to the end here products dot name okay and then we're going to say from you know what we'll do is we'll say from orders and then let's go ahead and inner join products okay and we want to do an on right here so on orders dot product underscore ID equals products dot ID all right and then we're going to do another inner join and this is going to be the customers so customers on orders dot customer ID equals customers dot ID okay and then you'll do order by and let's let's do it by the order number all right so let's see what that looks like ok so now you can see that we're getting the first name last name the order number the order date and we're also getting the product name so we're joining in two more tables to our orders table alright now we can also use aliases in in these statements so let's paste what we just did in and let's do select customers dot first name as actually don't will do will use concat as well just to kind of blend together everything that we've been learning so we'll say select concat and then let's grab this and we'll cut that out and let's paste that in here and then I'm going to put just a space right here so first name last name as through as name okay and then let's do right here we'll say can cat and then we'll take the order number and the order date some reason I can't write right click and cut so I'm going to just ctrl C and then delete it and paste that in here and let's put a space there and then we're going to say as order okay and then we want product name let's just used an alias here we'll say product name as product all right so let's go ahead and run that and now you can see we have the name which has the first name and last name the order which has the order number and the date and then the product which has the product name all right so that's how we can use aliases and concat along with our joint statement all right so that's going to be it for this video hopefully you enjoyed it and learn something from it and I'll see you in the next alright guys so in the last few videos we created our online store database all right we used SQL queries in the SQL window now what I want to do is show you how we can put a database project together using PHP myadmin using the graphical user interface alright and obviously this is a little this is a little easier than actually writing out SQL statements but I figured I could still show you how to do it alright so we are in the databases page of PHP myadmin alright so we're going to go ahead and new database and let's call it CD collection all right and we're going to create our first table here and the first table is going to be genres alright so we'll click go and we want to have an ID okay which will be an integer and we'll give it a length of 11 we also want to set it to auto increment and primary for the index this is a primary key all right and then we'll just have a name field which will be varchar' 255 all right so John Rose will have an ID in the name so let's click Save okay so there we go we have our John Rich table now let's create another table and let's call this artists and I don't know how many fields we'll have we'll just make it 10 all right so artists will have an ID length will be 11 Auto increment and primary key then let's do name varchar' 255 then let's do a bio and that'll be a text field all right and I don't know why I did 10 fields because it's the albums that will have more field so ID name bio I think that's okay so let's go and click Save okay so now we have our genres our artists now let's create albums okay for albums will just extend that to 8 click go of course we'll have an ID that's going to be auto increment primary key and then let's do artists on the score ID in 211 that's going to be the foreign key for the artists and then we also want John Rhodes so this will be genre underscore ID then let's do the name of the album and let's do a description which will choose text and let's see what else let's do image and that's basically going to be a URL it'll point to on image URL okay let's do release date and let's choose date for that and I think that should be good for now so let's click Save okay so now we have three tables now what I want to do is insert some data and we can do that easily through PHP myadmin so we'll start with genres let's click on that and then click insert and then up here let's say Rock will say hip-hop let's add a couple more rows here and say country and dance let's see this ignore I'm just going to uncheck those let's see what we got rock hip-hop country dance and let's say our D okay we'll click go now if we look at our genres orm-d didn't get at it I must have I didn't uncheck ignore but that's fine now four artists let's click insert and have this page open this Official Charts column slash charts slash album chart and you can see it just gives us the latest albums and we're just going to use some of these all right so let's say this Green Day right here so we'll add in here for the artist three and day and for the bio I'm just going to grab some dummy text for that okay so we'll just grab some of this and paste that in here okay who else do we have here Barry Gibb just paste that in and I'll just change it around a little bit all right and let's add a couple more here oh so they add five more rows it looks like it copied the description alright that's fine so let's get another artist let's see Craig David okay then let's do let's see 21 pilots okay one more we'll grab Bruce Springsteen all right so let's make sure that these ignores are unchecked and then we'll click go and now we have some artists okay if you want to edit these you can just so they're not all exactly the same you can double click them here and edit them okay so you can see that using phpMyAdmin is really easy you don't need to know any SQL code so now we have artists now let's take some of those artists and add some albums so Green Day has the ID of one and as far as a genre that's going to be rock so that's the idea of one so let's go to albums and insert artist ID one genre one okay so the name of the album is revolutionary radio it looks like and if I click buy and then Amazon it'll take us to show us some more info alright so the name is revolutionary radio okay description see if there's a description here alright so we'll just grab this copy it and then for the image what we can do is grab it can we grab it from here maybe not okay so we'll say save image or not save image open image a new tab and then we want to grab this link here so we'll copy that and we'll paste that in here and then release date release date I'm just going to make something up we'll just say October 5th all right so that's a whole album let's add another one so let's say who do we have Craig David so let's grab that following my intuition okay so the ID was let's see let me just open this up in another tab so Craig David is ID 3 and then the genre think it's iron B which we don't have let's just say dance which is ID 4 okay so artist ID 3 and then genre ID 4 okay the name of the album alright and then description we'll just grab some of this okay and then release date will say October 2nd and then the image city can we get into that if we click buy and then iTunes let's see can we grab this open the window open imaging new tab and we'll just grab this URL all right okay we'll paste that in and so let's click go and now we have two albums okay all right now one thing we haven't done yet is created our foreign keys so let's go to albums and structure and then what we need to do is add an index to these two fields where we want the primary key so let's say index okay do you really want to execute alter table add index yes we do okay we also want to do it to genre ID click OK and then what we want to do is up here you see how this is relational view we're going to click on that and then here artist ID we want to let's see we're going to choose artists and then ID and then for genre ID well two genres and then I'd be okay and let's see that's the internal relation down here we want to add the constraint constraint name we'll say we'll just say album key and then column will be I'm sorry artist key and you'll see that the only ones that show up here are ID artist ID and John our ID that's because these are the ones that have an index all right so this will be artist ID and say artist and then ID okay we want to add another column here and we'll say genre ID actually no let's let's just run this oh no add constraint that's right all right so this will be genre there we go all right so let's click Save and now those should be foreign keys so let's see structure and there we go so you see the two gray keys telling us it's a foreign key all right so now what we'll do is let's do a query here we're going to use joins so that we can display the album name the artist name and the genre name which are all in different tables so let's say select two albums dot name artist got name and genres dot name and we're going to select this from albums okay so we need to get data from the other table so let's do in a join to want to enter join the artist table and that's going to be on artist ID to album or albums so if albums dot artists on the score ID okay and then we'll just copy that and then this one will be Jean Rah's that's going to be on genres ID two albums genre ID okay and then let's just to order Phi will say albums dot release date alright so let's go ahead and run that and there we go so we have our album name the artist name and the genre name ok now this doesn't look very good with these headings here they're all they all say names so we might want to use aliases for that so let's go back and I'm just going to paste that in and let's say select albums dot name as album artist name and artist and then genres dot name as genre okay so now that looks a little better with the correct headings alright so we have created a CD collection database with three tables and the last thing I want to show you is how to export an import a database through PHP myadmin alright so make sure you're in the actual database and then we'll go to export okay and then we can leave everything as is and click go and then that's going to prompt us to save a file whatever the database name is and then dot SQL alright so I'm going to go ahead and save that to my desktop alright and I'll be including this in your project files and let's sigh let's take a look at it real quick okay so I'm going to open it not with notepad we'll just open it with Visual Studio code and it has a bunch of info and these are comments okay this doesn't actually run and then it can set the SQL mode and the time zone and then it's going to go and just basically just give us all the queries that we've ran all right whether it was manually in the SQL window or through PHP myadmin so create tables albums we have all the fields it also adds a couple additional things like the database engine which is I know db2 default character set things like that and then this is the insert statement for the albums table and we have the same thing with the artist same thing with the genres down here it's where we added the primary key in the foreign keys okay all the auto increments and so on all right now let's say that you want you have this file and you want to import it into your database so let's go ahead and create I will say test test dB okay which is now an empty database and then if we click import and then choose file and go to desktop and where is it CD collection SQL and then click go okay so import has been successfully finished 22 queries executed now if we look at test DB you'll see that we have all of our tables and we have all of our data alright so very simple I think we're going to go ahead and stop here and hopefully you liked this video and you learn something from it and I'll see you in the next one

Channel: Eduonix Learning Solutions

Views: 186,178

Keywords: mysql tutorial, database mysql tutorial, how to design database, database designing, mysql, database mysql, database mysql android studio, database mysql online, mysql database tutorial for beginners, database mysql online video, MySQL Database, MySQL Course, Eduonix

Id: BQ5xYJMyplw

Length: 85min 33sec (5133 seconds)

Published: Wed Mar 08 2017