Create MySQL Database - MySQL Workbench Tutorial

Create MySQL Database – MySQL Workbench Tutorial



Upgrade your Clever Techie learning experience:

UPDATE! (9/13/19) New features and improvements for Clever Techie Patreons:

1. Download full source code with detailed comments – easy to learn and understand code
2. Weekly source code file updates by Clever Techie – every time I learn new things about a topic I will add it to the source file and let you know about the update – keep up with the latest coding technologies
3. Library of custom Clever Techie functions with descriptive, easy to understand comments – skyrocket coding productivity, code more efficiently by using Clever library of custom re-usable functions
4. Syntax code summary – memorize and review previously learned code faster
4. Organized file structure – access all Clever Techie lessons, source code, graphics, diagrams and cheat sheet from a single workspace – no more searching around for previously covered material and source code – save enormous amount of time and effort
5. Outline of topics the source file covers – fast review of all previously learned coding lessons
6. Access to all full HD 1080p videos with no ads
7. Console input examples – interactive examples that make it easier to understand and learn coding
8. Access to updated PHP Programming Book by Clever Techie

Download this video’s source code and additional files here:

““““““““““““““““““““““““““““““““““““““““““““““`

We model a MySQL database using a diagram in MySQL workbench. We then forward engineer the diagram to create MySQL database on the server. MySQL workbench is a powerful tool for modeling and creating databases and we take advantage of it’s relationship tool to create many to many relationship between our main movie table, and stars, directors and genres tables. I don’t go into too much detail on how the relationships work in this video on purpose, I wanted to make a general overview of how MySQL workbench can be used to quickly created the model of a database with relationships between tables.

hey guys what's up its clever ticky and in this video we're going to be using MySQL workbench to model and create a MySQL database okay so you can see the model opened up here what we're going to be doing is creating a database and tables which will hold all the information from from the IMDB internet movie database website the movies table is going to be in the main table which will have all the information about the movies then we'll have a director's table stars and genres to hold all the information about those guys and then we're going to have three joining tables which is going to be our many to many relationships because many movies can have many directors and directors can have many movies many movies can have many stars and stars can have many movies as well as movies can have many genres and genres can have many movies so that's why we have the many many relationship between all the three tables and the main movies table now if you don't know what I'm talking about in the previous video we created a script code I mean we created a yeah regular expression script to parse out or scrape internet movie IMDB comm website and here's the complete script you guys can go ahead and download the source code from the description of this video and I've actually modified it a little bit and I made it so that all the data is provided in a nicely formatted array and then all it data is returned and this whole thing is a big function so here's an example of how you would use it you can store everything in a data array we call the function you specify the year start as well as a year end and then the page start and page end then we can print our our data okay just before you guys start using the script it's kind of a hacking thing that we're doing by creating the script that will parse out the imdb.com website and if you do it too much they might not like it and they might actually ban your IP if you overdo it so when you're just testing out the script careful with how many pages you specify here start very small so this is why I'm only specifying two pages to scrape to show you guys how this works okay so let's go ahead and see what happens here when I run the script okay so it's it's going to load the as a script is loading it's going to parse out all the records and then it's going to display this big array I'm going to go to view page source and you can see how the array is organized very nicely it's got all the movie information along with the title ear image etc so now we're ready to work with this array and and we're ready to create the actual MySQL database that looks like this okay so MySQL workbench is a free program you guys can download it and I'm going to I'm going to go ahead and open up the workbench here and create a new connection by clicking on this plus icon I'm going to name my connection localhost for the username I'm going to live it as root and press them in store in Vault button and enter my password then I'm going to test the connection and it should say successfully made the MySQL connection at this point I'm going to click OK I'm going to double click on this connection to connect to it and then I'm going to go to file new model necessary we're going to be modeling our database I'm going to double click on an ad diagram and it's going to have this toolbar on the left side and these are all the tools we can use to model our database so we're going to click on this icon down here which says place a new table to place our first table and then I'm going to double click on this table and a new box will pop up with where we can name our table as well as specify all the column names ok so I'm going to name this table movies and then under the first column name I'm going to enter ID I'm going to expand this box a little bit so you guys can see this better and this is going to be our unique identifier so and it's going to be the primary key which is going to autoincrement so make sure the primary key checkbox is checked make sure that not null is checked make sure that the auto increment checkbox is checked as well okay so at this point in order to figure out what the column names are we already have we're ready know all the information that we're going to be needing so I'm just going to refer back to this model here and in order to know what all the fields are so we got a title here an image URL and I'm going to go ahead and enter those right now title here image URL I'm going to go back to the image and certificate runtime IMDB arraying so I'm just going to keep going back and entering always description meta score votes and drawers okay so now that we entered all the column names we're ready to specify what the data type will be I already have that so for the title we're going to have a varchar' year is going to be an INT image URL is going to have a bar chart which is going to be 255 characters so we're going to specify that over here in the data types okay so the year is going to be int and the image URL is going to be a little bit longer I think because well if we go to back to this data here you can also refer to all the data in this array and you can see that the image URL is in fact a little bit long so we want to account for that okay so next field is going to be certificate and it's going to be a very small text so we want to leave it as 45 characters now the wrong time we actually want to have it as int because that's going to be an integer the amount of seconds or rather the amount of minutes the movie runs I am BD IMDb rateing that's going to be a float because it's going to be a fractional number so that's the way they store it now the description is going to be text because it's a description of the movie so it's going to be a paragraph or more of text meta score is just going to be int votes is going to be int and Ross is going to be int as well and I'm going to delete this extra column here okay so the description is text Metascore intros in grass end okay so now that we have all of our fields for the movies let me see I'm going to go ahead and close this and expand them if I click on the table now I can go ahead and drag this little box here to expand it and then it's going to show what the actual movie table looks like now and we actually want to make the title unique because we only want to have unique movie titles in our movies database so I'm going to double click on the movies table again I'm going to expand the window here and for the title I'm going to say I'm going to check this checkbox which is unique and also it's going to be not know now the year and an image URL should also be not null because those are the values that I'm DB always has all the rest you can leave it as that as as null values because sometimes all those see what they're going to be null okay at this point I think our movies table is done so I'm going to go ahead and save this file under movies and maybe movies okay all right so now that our movies table is complete I can we can also create a directors which is only going to have that i D named about we can create the stars which is going to have exactly the same thing and then the genres which is also going to have just the name okay let's go ahead and create those three tables now okay so go back to our diagram and click on the table to create a new table double-click click on this table and name in directors under ID we're going to have a primary key make sure it's out of incremented create another column name name name and about just to make sure name is going to be a varchar and about it's going to be text okay so vowels can be a text and I'm just going to leave we're just going to leave name as 45 characters because I don't think there's any name that's going to exceed that and I'm just going to say the name is going to be not null and unique as well check the unique box okay so so now we can also create another table called stars and it's pretty much going to be the same thing is going to be really similar to directors I'm going to double click on it name it stars create the ID primary key Auto increment not null name varchar' and about text not know for the name and unique the name okay so that's done and then the final table is going to be in sonorous I'm going to double click on that name is onerous create the primary key ID again make sure its primary key not null and auto increment and for the field name for the column name just going to have one name and name it name and for the for the text I'm also going to leave it as varchar' 45 and make it not null and unique okay at this point all of our tables are complete now we're ready to specify what the relationship is going to be between these tables because like I mentioned in the beginning of this video many movies can have many directors many movie many movies can have many stars and many movies can have many genres which means it's a many-to-many relationship and in order to create a many-to-many relationship in MySQL workbench and this is what makes it really awesome on the tool bar over here let me just expand this a little bit so it becomes visible okay so okay you should be able to see now so on a toolbar down here you can see Jesus okay okay so you can see this icon here which says place a new and Colin M identifying relationship so what you do is click on this icon to create a many-to-many relationship then you click on the movies table and then you click on the table on the target table for example directors at this point is going to create a new table named movie head directors and you have successfully created and many to many relationship between movies and directors and it's really that easy ok next up I'm going to create I'm going to do exactly the same thing click on this icon again then click on the movies and then stars that's going to create a many to many relationship between movies and stars and then do the same thing with genres and now we have successfully created the many to many relationships between movies directors movie stars and movies genres now I'm not sure why it's naming it movies has directors I prefer to name it movies directors because directors can have many movies so it's like directors have movies as well and do the same thing with the movies stars and movies genres double click on it rename it to movies genres and at this point let me just check to make sure we have everything if I go back here so at this point we have successfully modeled and created our diagram now let's actually physically create it in the database so the last step here is to make sure you save this diagram by going to file save model and name it whatever you want and then you're going to click on this database tab and then for word engineer which is just a fancy way of saying we're going to create this database now on our localhost and then you want to you're going to have this window here at the bottom I'm going to click Next click Next again click Next again next and then is it's going to say forward engineering progress and all these boxes should be checked that means the database has been forward engineered and finished successfully you can close this window now and then you can either use your command prompt or open up the minus QL word MySQL PHP myadmin getting confused with all those names ok so PHP myadmin I'm going to log into it I'm just going to make sure the database has in fact been created under the database you should see movies oh whoops ok so I know what happened here let me let's fix it and it's going to be really fast so under under the model here um okay so you can you should see the catalog tree here go ahead and double-click on it and it should say my DB rename it to the movies database and also go ahead and create the movies database create database movies you can do that in command prompt or you can also do that in the PHP myadmin by going to databases and then create database just enter the name then click create okay so now that the database has been created we can forward engineer the script again and it should work now forward engineer click next next next next and looks like it's successful go back to the PHP myadmin and all the fields are there so let's test it out and click on the movies table structure all those fields are there and go back to the movies database and you can see all the tables have been created and you can play around with it and check to make sure all the columns and database data types are correct and as you can see all of our relationship tables have been created as well and that's how you create and model the database using MySQL workbench if you guys found this video useful please go ahead and like share and subscribe and please make sure to watch the next video where we're going to be inserting all the data that we're scraping from the from the IMDB database and we're going to be inserting all this data the inside the database that we just modeled so we're going to create the script for that and make sure you don't miss that until next time Claire we take you out

32 thoughts on “Create MySQL Database – MySQL Workbench Tutorial

  1. Thanks a lot for the tutorial video. Just a suggestion to make your chanel even: can you organize your playlist in a clearer way, i am having trouble finding the "next video" that you mentioned in the end of this video.

  2. Database is used usually for PHP programing lanangue, u know how to make in html a database.. i dont know if its possible I'm just 70% percent but please if u can or anyone… Explain me . I want to understand 🙂

  3. Nice presentation.
    Pls can you help me with the video on how to link MySQL workbench with login page using VS text editor.
    Thanx.

  4. This is helping me pass a class in uni. I'm working on a database about boardgame tournaments, so it's different but if I pass this class, it's gonna be in large part thanks to this video. All the kudos to you.

  5. YOU ARE A GOD! Thank you so much for making this video. I have been struggling for 4 days trying to figure out how to connect to my own database using a java connector and I think this may just be the key I was missing. thank you SO much! HUGS

  6. man… this video, sure if you know what he is talking about its good but if you are starting from literally 0 (you just installed workbench and don't know anything else) and want to know how to do it this is NOT the video to learn how, so many unanswered questions from the start.

  7. One movie can be directed by one or many directors, one director can direct none, one or many movies. 😎

  8. You're better than my professor; mind you I pay tuition and he doesn't teach me what you are teaching me. I question Education in 2018, because Youtube is doing it for FREE.

  9. This was super helpful. Is there a way to make changes to tables in MySQL Workbench and sync them back to the database easily?

  10. Thank you for the movie. I'm a beginning user and couldn't figure out how to export a sql file. After viewing this I learned I needed to forward engineer the model before I could export it. Thank you.

  11. hi there, great work btw. When I'm trying to connect localhost/phpmyadmin, it says browser can't connect to the server. I've checked MySQL status(up and running) and also I am able to connect in my terminal and create db etc… How can't I connect to my phpmyadmin page?

Leave a Reply