How to Create a Database, Add Tables and Import Data in MySQL Workbench

[@markward8483]

Thanks Matt, great tutorial easy to follow. All what I needed to learn for my course! Love KZbin, just keeps giving!

[@MattMacarty]

Glad it helped

[@Bella_Noches_]

WOW! This video is absolutely awesome! Never had such clear instructions on using the MySQL workbench and you helped me avoid a headache for my homework assignment. Your instructions worked like a charm!!

[@MattMacarty]

Glad it helped

[@ashleybattiata8912]

I feel like I've learned more in this video than all semester. You rock!

[@MattMacarty]

Glad it helped

[@kothon1]

Dude, your tutorial is just so simple to understand. Thank you!!!

[@MattMacarty]

Glad it helped!

[@simonadong4512]

This is SUPER HELPFUL thank you!! I glanced through so many articles which all made no sense to me...but this video is so on point

[@MattMacarty]

Thank you. Glad it helped

[@oyewoleayorinde7864]

This is one of the best tutorials on this subject matter. Thanks so much

[@MattMacarty]

Thank you. Glad it helped

[@mariaalejandraperpinanbarr3535]

Thaaanks! One of the best tutorials I've seen

[@MattMacarty]

Thanks very much. Glad it helped.

[@derelictmanchester8745]

Thanks, Matt, really clear and concise..

[@MattMacarty]

Glad it helped

[@benlall215]

This was extremely helpful. Thank you! All the other videos say its for beginners and then fail to explain the most rudimentary steps... Thank you!

[@MattMacarty]

Thank you. Glad it helped.

[@rriimt1556]

Excellent tutorial to learn database....thankyou very much sir..

[@MattMacarty]

Thanks. Glad it helped

[@banjoomotola8583]

This was so useful to my learning. So happy I found this. Great job!

[@MattMacarty]

Glad it was helpful!

[@emekaukwuoma3359]

Thanks Matt, this was super explanatory. I can create a database table thanks to you.

[@MattMacarty]

Glad it helped!

[@nitinkymar9468]

One of the best tutorial on mysql. Thank you very much!!

[@MattMacarty]

Thank you. Glad it helped.

[@mikaeluhl]

Thanks! i had to import a csv that my teacher made and i was struggling with the 'date' situation.. his file had the year coming last and i was using date :P when you explained that years in date should come first you gave me the answer i couldn't find after hours of search

[@MattMacarty]

Glad it helped!

[@lebom8076]

The only video that helped me. Because of that, i subscribed🎉

[@MattMacarty]

Thanks. Glad it helped

[@waxtin48]

This is exactly what I needed thanks a million

[@MattMacarty]

Glad it helped

[@maverickpeck6012]

This is extremely helpful, just doing an intro database design class and this video helped me a ton!

[@MattMacarty]

Thanks. Glad it helped.

[@felipecruz3061]

I am so glad i found this video... amazing. Thank you buddy

[@MattMacarty]

Glad it helped

[@Skjoldr16]

Hi Matt, thanks so much for the clear and beginner-friendly tutorial, this definitely helped me a lot.

[@MattMacarty]

Glad it helped

[@unnatirai_07]

Amazing explanation and hat's off to your efforts. Thanks

[@MattMacarty]

Thank you. Glad it helped

[@adrijesus1981]

Oh God, you have a database called "Frodo"! Cool :-D

[@MattMacarty]

Yes it's not my server, but yes a friend for sure.

[@ayanchakraborty76]

Samwise was not wise 😂

[@Cpeters0]

Very easy to follow and understand... 2 thumbs up!!

[@MattMacarty]

Glad it helped

[@browngift3408]

You are a life saver Matt. Thanks so much

[@MattMacarty]

Glad it helped

[@jonlbs7]

Best tutorial I've seen .. Thank you !! ✨✨👍😊

[@MattMacarty]

Thanks very much. Glad it helped.

[@the9263]

This is amazing. I'm following here with my laptop haha

[@MattMacarty]

Nice. Glad it helped

[@BellTollsForThee]

This tutorial is amazing! Thank you a bunch! I loved the explanation of issues with data types!

[@MattMacarty]

Thank you. Glad it helped.

[@lugonzales8980]

Thank you for making this easy!

[@MattMacarty]

Glad it helped

[@Cebuanoontwowheels]

Felt like i was listening to vin Diesel. Thank you dude!!

[@MattMacarty]

LOL. Someone else said the same thing. Glad it helped.

[@ThuHuongHaThi]

Thanks a lot, I have been in trouble with it for so long

[@MattMacarty]

Glad it helped

[@reeseverschelde758]

Incredibly helpful video, thank you.

[@MattMacarty]

Glad it helped

[@keithlamontdavis8047]

Well done. Very useful and very clearly explained. Newly subbed!

[@MattMacarty]

Thank you. Glad it helped

[@BabaYAGA_OW]

Thanks man! Really good tutorial

[@MattMacarty]

Glad it helped

[@mannahidruhidru812]

thank you so much you are just amazing, perfect explanation

[@MattMacarty]

Glad it helped

[@MattMacarty]

Glad it helped

[@daniel3897]

Great tutorial. Thanks a lot Matt!!

[@MattMacarty]

Than kyou. Glad it helped.

[@jayanthim2039]

Nice explaination, and very understandable... Thank you bro..

[@MattMacarty]

Glad it helped

[@nayaroni]

Thank you for this tutorial!!!

[@MattMacarty]

Glad it helped

[@nayaroni]

@@MattMacarty can you do a video on Stored Procedures? I am struggling with it:/

[@charleskp947]

thanks so much.... your voice is awesome too... should be voice over artist

[@MattMacarty]

Thanks for the compliment. I am glad it helped.

[@lukewilliams4758]

thanks for teaching us what you know I can finally make my website

[@MattMacarty]

Glad it helped

[@jayshree144]

very helpful in importing an excel file. thankyou

[@MattMacarty]

Glad it helped

[@lyjunsay2852]

you sound like the guy from Khan Academy. Great job on this! Very helpful tutorial!

[@MattMacarty]

Thanks. Glad it helped.

[@crystella1450]

That's true , he sounds like Sal khan

[@greenblue980]

thanks alot! i forgot about that lightning thing 😂

[@MattMacarty]

Glad it helped

[@SINNER460]

OMG .. Perfect ...well explained. You really helped me. Thank you.

[@MattMacarty]

Glad it helped!

[@ajeetdubey1308]

Really so helpful ❤️

[@MattMacarty]

Thanks. Glad it helped

[@hoormallick879]

Amazing it helped alott💓

[@MattMacarty]

Glad it helped

[@Bakemono10]

Life Saver tutorial!!!

[@MattMacarty]

Glad it helped

[@surgiokilla1011]

Great tutorial. Thanks!

[@MattMacarty]

Glad it helped.

[@zhiqiangzheng5985]

Thank you, it helps a lot

[@MattMacarty]

Glad it helped

[@casperj4784]

Thank you very much for this video. I'll have a look at your others, but I'd be interested to know how to import data that needs to be separated into different tables.

[@MattMacarty]

There are a couple of options. You could import it all at once and then separate into tables or just import one on by one and add relationships when you have imported everything.

[@marcelojunior4301]

Thank you so much for this!

[@MattMacarty]

Glad it helped.

[@augustusprime0_o]

great tutorial, thanks!

[@MattMacarty]

Glad it helped

[@dwest889]

Excellent tutorial

[@MattMacarty]

Thanks. Glad it helped.

[@leniorrb]

Very helpfull! Great tutorial.

[@MattMacarty]

Glad it helped

[@grannah2050]

Very helpful, thank you

[@MattMacarty]

Glad it helped

[@joycewang7373]

Thank you Matt, the vedio did help me to learn mysql as a beginner. Could you please advise if you have more vedio of mysql on KZbin? I would like to study mysql with your further vedioes. Looking forward to more vedioes

[@MattMacarty]

Hi I have a few videos for beginners: kzbin.info/aero/PLiPFKCVZWHKq3RvwDxosNm5Ny7cHyfc4o

[@joycewang7373]

@@MattMacarty Thank you very much!

[@lesliee.4525]

I'm getting an error that says "unhandled exception 'charmap' codec can't decode byte 0x9d in position 2155: character maps to " do u know how I could fix this?

[@MattMacarty]

This sounds like there are some characters in your data that MySql can't deal with. Try opening the data file in a simple text editor to see if you can find and remove any odd looking characters. They should be pretty obvious.

[@lesliee.4525]

@@MattMacarty ok thanks

[@christv0811]

Nice this is so clear

[@MattMacarty]

Thank you. Glad it helped

[@devconnie]

Tks Matt. Appreciate it.

[@MattMacarty]

Glad it helped

[@joeryder5739]

Thank you Matt.

[@MattMacarty]

Glad it helped

[@jonlbs7]

When trying to update the date I got the following: 12:37:14 UPDATE amazon_csv SET t_date = str_to_date(t_date, "%m/%d/%Y") Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. I'm having more problems with the date than you can possibly imagine.

[@MattMacarty]

Ok, yes the date can be a pain. If this don't absolutely need it to be a date type you can probably just leave as a text. My guess is the dates are in a different format then you have specified. With your str to date function.

[@nutsaboutnuts927]

Hi, at about 11.30 in the video, if I try to update using the str-to-text function, I get an error message 1411 incorrect datetime value. The csv file I'm using looks just the same as the one in this demo video (the date format). Any idea what's going wrong? (me = complete newbie!) Thanks

[@MattMacarty]

Really hard to diagnose remotely. Can you try to alter the table to see if that works? ALTER [table name] MODIFY [data column] date; if this works you can skip the str_to_date( ) since the data is now defined as a date.

[@nutsaboutnuts927]

@@MattMacarty Thanks so much for replying. In fact what I did was to change the column header in the csv file, previously it was called 'date' so I changed it to something else and then the str-to-date worked. In the table it still has the Datatype as text though - is that right?

[@MattMacarty]

@@nutsaboutnuts927 Yes. To change the data type you run the ALTER table command from my previous response.

[@adekunleshokunbi9541]

Thank you Matt for this comprehensive video. I still have issues on trying to set up my workbench and server😢 How can I go about this?

[@MattMacarty]

Which version of MySQL are you trying to install? Windows or MAC?

[@armelmahunan851]

Thank you so much. You save me

[@MattMacarty]

Glad it helped

[@tiagoaicapital]

Hi Matt, how could we upload csv files using an sql script to upload instead of doing need manually?

[@MattMacarty]

This tends to be problematic due to security settings in MySQL. You are looking for LOAD DATA INFILE [filename]. Here's a link to the documentation: dev.mysql.com/doc/refman/8.0/en/load-data.html

[@tiagoaicapital]

@@MattMacarty Thank you for the feedback

[@philosophyandreligion]

Great tutorial! 1 question. I’m new to the computer verse. Was that data sheet created in Excel?

[@MattMacarty]

Thanks. The data used started as an Excel file but was converted into .csv from within Excel.

[@philosophyandreligion]

@@MattMacarty “This file type is not supported. Valid options are: CSV, JSON” That’s the notification I keep getting - even after I converted the file to CSv format. Trust me, I tried all of the csv format and I keep getting the same disappointment. I don’t know what to do now. May there is a setting in excel..🤦🏾‍♂️

[@MattMacarty]

@@philosophyandreligion Are you on a MAC? Try installing a simple text editor like sublime text or atom. Open the file with the text editor and then copy and paste the data over itself and save. Try the upload then.

[@aparise1]

Great video! Is it possible to import more than 1000 rows with the wizard? Does creating the DB manually allow for more?

[@MattMacarty]

Thanks. Yes you can import much larger files it just may be a little slow. The limit on output can also be changed to allow more or less results to display in your result set.

[@brisikog]

i am facing this problem while importing data unhandled exception :list index out of range and data is not importing please help me with this if u can???

[@MattMacarty]

It sounds like the data you are trying to import may have to be cleaned. Probably some kind of invalid data. Try opening the file with a text editor. Usually Workbench will tell you what line in the data the problem happens at.

[@cesarl.c.847]

Hi Bhuvan, after view great video, to review LoaderDataPlus tool. This tool import flat files and export to MYSQL, MARIADB sintax and other DMBS, also parsing and detect data type.

[@brisikog]

Thanks both of you there was some problem with the data ....I solved it... thanks again

[@mansitomar8549]

great great great video.........

[@MattMacarty]

Thank you. Glad it helped.

[@maheshdravidar3694]

Hi Matt, First of all thanks for brilliant videos. I am loving it. Now to the question, i am trying to import the file to change the date format but while loading the through import wizard, its loading zero data. I am following all your steps mentioned from 11:50, please advise what am i missing.

[@MattMacarty]

So you mean the date is in some format other than ISO? I would try to import the date column as text and then convert it in MySQL to see if that works better.

[@bsbteck]

Greate Job Sir

[@MattMacarty]

Glad it helped

[@milkandeto1622]

So helpful.

[@MattMacarty]

Glad it was helpful

[@granand]

Thank you Matt. Great tutorial and help. Please may I know where is this database stored which we are creating in the localhost. Can we change that location to external drive? 2. How to load .bak datasets, I have downloaded?

[@MattMacarty]

In Windows you can find your database files here: C:\ProgramData\MySQL\MySQL Server 8.0\Data The programdata folder is hidden by default so go to file explorer and open This PC, then click the view tab at the top and check the Hidden items check box on the right of the ribbon

[@devconnie]

Matt, tks for your reply and tried to download the file. However, the data file did not open and link with the database, and maybe I was doing something wrong. If you put a video about downloading and linking the data that would be great. tks.

[@MattMacarty]

OK. You have a couple of options here. You can download the files as a .zip and then extract them or you can clone the github repository. Let me see if I can get a tutorial together.

[@devconnie]

Hi Matt, tks for the great video. However, how to download the data file and upload

[@MattMacarty]

Here is a link to all the MySQL files: github.com/mjmacarty/mysql

[@pratyushsinha374]

Thank you so much!

[@MattMacarty]

Glad it helped

[@bushidoGF]

Thank you for this

[@MattMacarty]

Glad it helped

[@diazjubairy1729]

could you make tutorial about how to make ER Diagram from excel files to mysql worbench ?

[@MattMacarty]

Sure. I will try to get to this soon.

[@diazjubairy1729]

@@MattMacarty thank you sir

[@Mr.nikk62]

thank you so much sir

[@MattMacarty]

Glad it helped

[@mehdighomshe2346]

thank you for your video

[@MattMacarty]

Glad it helped

[@jimmywheeler3601]

Thanks Matt, I'm running into an issue in which the import wizard is taking a very long time. I'm using the owid covid data which comes out to around 33 MB. Is something wrong that it has been 10 minutes and the progress bar is still at 0? I had tried using the 'LOAD DATA INFILE...' command but kept ending at error 1290 and go stuck on 'secure_file_priv'. Is there a better way to get this data into MySQL? Thank you again

[@MattMacarty]

With a typical installation's security settings, it's nearly impossible to use LOAD data. In Workbench can take a very long time, but it is likely that there is something in your data set that MySQL is getting hung up on. Try taking a look at the data in a txt editor and see if there are any hidden characters that may be causing the problem.

[@jimmywheeler3601]

@@MattMacarty Thank you for the insight! I've made an attempt to split the data in half in R, change all my data types to text to avoid type errors. It's running now and I have yet to run into any errors in the message log but am not holding my breath. I've got 125K entries with 65 columns, could the upload go any smoother if I were to chop the main data set into 15 column chunks that I can join back together later?

[@oluwafemihunsu6200]

Jimmy what do you mean by spliting it into half

[@joseraphaelmarquez8189]

Hello! I just want to ask if it is possible to hide the unwanted database or can I just create new file/project to makd it look more organized? Thank you in advance!! 😊

[@MattMacarty]

The Workbench is really sort of a development environment for interacting with your databases. I don't think you can hid what you dont want to see unless you just close the schema pane. There are other tools you can try like Tables Plus that may do more of what you are looking for

[@khong6460]

A nice and clear intro to SQL workbench - MANY THANKS A qq, do I need to 'drop' the table every time when I wish to run the code before moving on to the next part? I never used the 'drop' trick, so workbench just returned a red cross with table already exists? TA !

[@MattMacarty]

Hi no you don't need to drop table unless you want to start over.

[@khong6460]

@@MattMacarty thanks for replying. I tried to re-run the whole program after inserting data into the table i created in part 1, but SQL workbench can't run that saying the table is already exist ? Cheers

[@MattMacarty]

@@khong6460 OK, just skip the line where you CREATE TABLE. You can run one line at a time in the Workbench editor, or just comment the CREATE line out.

[@alazart3383]

i have an error and i ve been trying to solve for hours please help me when i want to import i get an error called ascii codec cant decode byte 0xef in position 0: ordinaş not in range (128). and i use mysql workbench 8.0.24

[@MattMacarty]

If you are importing data into a pre-designed table you might have defined the first column as a tiny int which will only accept values up to 128. You can alter your table or try to import the data into a new table. You may also have are some characters in your data that you can't see. Try opening the data with a plain text editor and see if you can find it.

[@alazart3383]

@@MattMacarty i solved thankssss ❤️❤️

[@ultrawhiff]

What is the version of your SQL workbench?

[@MattMacarty]

This was done with Windows version 8.0.21

[@ultrawhiff]

@@MattMacarty thank you sir.

[@pavitras6058]

Do u have video on how install n setup MySQL pls share me and I need it on 6.3ce workbench

[@MattMacarty]

I have a document if you email me I will send it to you. Tell me if you are using MAC or Windows

[@divyanshupawar8069]

Very helpful

[@MattMacarty]

Glad it helped

[@samratkundu2799]

What should I do with the large data having more than 20K rows with messy/NULL values..? I may not know if the data type has really maintained to the entire row or not (and not possible to check entire dataset manually).. It's getting very tough to import the data successfully to MySQL table. The Import Data Wizard is really slow in this task. Is there any other way to this more efficiently whithout loosing anything ? please let me know.. I'm unable to complete my portfolio projects 😥

[@MattMacarty]

You can try the import as a generic data type like text. Workbench is very slow with larger datasets. You may wan to try something like TablePlus

[@LalitSoni-s2f]

sir what if we use d/m/y date format instead of m/d/y ..will it work ?

[@MattMacarty]

The goal should be to get dates into ISO format for sorting. Yes you can use %d/%m/%Y to import European format

[@renancatan]

Very nice! Mysql community version is free, right? Do you know how much space can I use? Another question, I saw that youre importing your connection from a local server, is it really on the cloud, how do I do to store my data/retrieve it through a cloud connection without being "local" in python through mysql?

[@MattMacarty]

Yes MySQL is free to download and use, community edition. I have both localhost connection and remote, (cloud or network server). You will just need the address for the remote connection to set it up and a username and password to connect with.

[@fact4education]

Thank you!

[@MattMacarty]

Glad it helped

[@sareenasanil4053]

Can you tell how to treat the blank cells while importing from excel to mysql workbench. I want them to be null and converting datatype from text to int .

[@MattMacarty]

You probably have to do the manipulation after you import. If values are blank they should automatically come in as Null

[@Nanoo-zi4fg]

When loading the data.csv file, I get an error "Unhandled exception: 'ascii' codec can't decode byte 0xef in position 0: ordinal not in range(128)". The file is saved as UTF-8 .csv file but mysql will not load it. I google searched the error message and there are lots of others experiencing the same but no solution.

[@MattMacarty]

Try opening the file with a plain text editor such as notepad++. Look for the character and delete it. You won't see it in Excel. You can try saving the file out in plain .csv after opening with notepad++ as that may fix the problem.

[@vinaybora617]

Awesome!! Dude

[@MattMacarty]

Glad it helped

[@MattMacarty]

Glad it helped

[@samuelribeiro5022]

Thanks for sharing, but you can make another tutorial showing how to integrate this data in a web page?

[@MattMacarty]

Thanks. Do you mean how to serve data from MySQL database to a webpage/site?

[@samuelribeiro5022]

@@MattMacarty Yeah, exactly.

[@MattMacarty]

@@samuelribeiro5022 OK, the problem is there are so many different ways to do this. It will depend on the server-side software you are going to use. One common implementation is PHP and MySQL.

[@Pollanese16]

Hi. Thanks for this! I just went through this exercise but my chart is not appearing at all. Any idea what I am doing wrong? I did not even see a chart when I added in the headings. I'm a total newbie to MySQL

[@MattMacarty]

There is a little refresh icon in the upper right of the navigation pane. Click and the new database should display. They will also display the next time you connect to the server.

[@Pollanese16]

@@MattMacarty Thanks. I appreciate the reply. Yea, I wasn't refreshing and selecting the new database. I figured it would automatically recall it. I was going crazy. Lol

[@azimshikalgar69]

Hello, How to install DVD rental database in MySQL as it's in postgreSQL

[@MattMacarty]

It's probably not directly compatible. Even though most of the query language is the same, these two products diverge in database implementation. I would do a search to see if someone has created a MySQL version. I am not familiar with that databse, but it sounds suspiciously similar to Sakila in MySQL

[@colinz7234]

hi, thanks for sharing this tutorial. But I encounter an issue when I importing the database. I lost some database after upload but there are no error shows. Do you know how to fix it?

[@MattMacarty]

Hi, I will need a bit more to go on. What database are you trying to upload?

[@colinz7234]

@@MattMacarty hi, thank you for replying. I’m using a database called “Biden NY state all contributions”

[@abdulazizamen8286]

can you help me, how to read a list of values from a table and put them in a variable in CSV format

[@MattMacarty]

After you run your query, at the top of the results grid there is a button to export. That is probably the easiest way to do it.

[@abdulazizamen8286]

@@MattMacarty Thank you

[@ic_nivad7767]

Hi Matt, thanks for the video! I have a question: why did my import (9 MB .csv) take so long to finish? 30 minutes passed, and it is still going. Would you happen to have any comments on this? Thanks for the time.

[@MattMacarty]

Workbench can be pretty slow importing data. There could be a problem with the data too. I would install TablePlus and try the import there. TablePlus is a client that can handle large imports much more quickly, and if there is a problem with data you will see that very quickly.

[@ic_nivad7767]

thank you for your time @@MattMacarty

[@SamandarImamaliyev]

which version of MySQL do you use in the video?

[@MattMacarty]

It's MySQL "8"

[@SamandarImamaliyev]

@@MattMacarty i am using MySQL 8.0.32, foreign key is not working properly for int values. which version of 8 are you using i want to download that one so i' ll check if it works properly or not

[@MattMacarty]

That video was probably 29 or 30. To implement a foreign key both data types have to be the same. So maybe you are trying to relate INT and something else? Or if you already have data in the tables you may be violating referential integrity.

[@КамиллаГарифуллина-р4ю]

Thank you a lot, it's the best tutorial! However, I've faced the problem: 0 records imported. Please, can u say me how to fix it?

[@MattMacarty]

This usually means there's a error in the data type you are trying to import. Try importing the data into a new table and then modify the definitions as needed.