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

[@oyewoleayorinde7864]

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

[@MattMacarty]

Thank you. 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

[@kothon1]

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

[@MattMacarty]

Glad it helped!

[@rriimt1556]

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

[@MattMacarty]

Thanks. 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.

[@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!

[@nitinkymar9468]

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

[@MattMacarty]

Thank you. Glad it helped.

[@banjoomotola8583]

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

[@MattMacarty]

Glad it was helpful!

[@felipecruz3061]

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

[@MattMacarty]

Glad it helped

[@lebom8076]

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

[@MattMacarty]

Thanks. 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

[@the9263]

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

[@MattMacarty]

Nice. Glad it helped

[@emekaukwuoma3359]

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

[@MattMacarty]

Glad it helped!

[@unnatirai_07]

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

[@MattMacarty]

Thank you. 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.

[@maverickpeck6012]

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

[@MattMacarty]

Thanks. Glad it helped.

[@Cpeters0]

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

[@MattMacarty]

Glad it helped

[@waxtin48]

This is exactly what I needed thanks a million

[@MattMacarty]

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 😂

[@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

[@ThuHuongHaThi]

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

[@MattMacarty]

Glad it helped

[@greenblue980]

thanks alot! i forgot about that lightning thing 😂

[@MattMacarty]

Glad it helped

[@mannahidruhidru812]

thank you so much you are just amazing, perfect explanation

[@MattMacarty]

Glad it helped

[@MattMacarty]

Glad it helped

[@lugonzales8980]

Thank you for making this easy!

[@MattMacarty]

Glad it helped

[@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.

[@jonlbs7]

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

[@MattMacarty]

Thanks very much. 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.

[@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

[@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

[@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

[@BabaYAGA_OW]

Thanks man! Really good tutorial

[@MattMacarty]

Glad it helped

[@browngift3408]

You are a life saver Matt. Thanks so much

[@MattMacarty]

Glad it helped

[@keithlamontdavis8047]

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

[@MattMacarty]

Thank you. Glad it helped

[@reeseverschelde758]

Incredibly helpful video, thank you.

[@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.

[@jayshree144]

very helpful in importing an excel file. thankyou

[@MattMacarty]

Glad it helped

[@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.

[@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

[@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

[@jayanthim2039]

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

[@MattMacarty]

Glad it helped

[@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

[@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?

[@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

[@sagetimmy6310]

Well explained ❤

[@MattMacarty]

Glad it helped

[@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

[@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 ❤️❤️

[@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

[@dwest889]

Excellent tutorial

[@MattMacarty]

Thanks. 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:/

[@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.

[@ultrawhiff]

What is the version of your SQL workbench?

[@MattMacarty]

This was done with Windows version 8.0.21

[@ultrawhiff]

@@MattMacarty thank you sir.

[@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.

[@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.

[@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

[@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

[@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

[@OluwaseunOgunmade]

Hi, how do I insert integers with a comma delimiter. E.g 10,000 for salary column? When creating a table for the column that fields salary record, what will be the data type?

[@MattMacarty]

Hi if you put a comma in the data type becomes string. You can query the data and output with comma separator using the format function

[@OluwaseunOgunmade]

@@MattMacarty Thank you

[@OluwaseunOgunmade]

Hi, I'm trying to insert a date into a table and I'm getting 'Incorrect date value: '12/01/2020' for column 'OrderDate'. What am I doing wrong?

[@nanthacharee1114]

What is double and why we have insert the text 'double'? is it because we have to repeat above and why we are not giving char for other columns and only gave for 1st column

[@MattMacarty]

Double is the data type specification. This is a general numeric type with decimals. There are other numeric types , but double is a pretty good choice for most numeric data that many have decimals.

[@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

[@dillirajvicky4423]

Sir, In my MySQL workbench in tables not showing (tables data import wizard) Kindly what version u are using tell me sir

[@MattMacarty]

It think it is around 8.29, but that is not why you are having trouble. Can you give a little more detail?

[@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!

[@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

[@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

[@rajandevkota8839]

I am not seeing action output on my Mysql workbench. Any solution?

[@MattMacarty]

Sounds like either the panel has been hidden or you are not connected to the server.

[@delirium6313]

I keep getting: Error Code: 1411. Incorrect datetime value: '2019-05-30' for function str_to_date, when trying to update the TradeDate to the correct format. any work around to this

[@latenightorgandonor]

@Delirium same

[@MattMacarty]

I think maybe you have transposed the month and day in your code. Should be %Y-%m-%d

[@abdifatahmoh]

@Delirium Same here.

[@45_dzikrimufidtsaqif33]

Let me ask, how do we know the latest data that appears from the data that we have imported without importing it again, I mean if we change the data then the new table will also be replaced automatically, how can we do that sir? thank you Sir

[@MattMacarty]

You can either manually import or write a script in something like Python to automate this.

[@SINNER460]

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

[@MattMacarty]

Glad it helped!

[@grannah2050]

Very helpful, thank you

[@MattMacarty]

Glad it helped

[@seankarkhanis4319]

every time I try to import data, I get the same error that says, "Unhandled exception: 'ascii' codec can't decode byte 0xe9 in position 71: ordinal not in range(128) Check the log for more details." Is there anything I can do to fix this issue? Thanks

[@MattMacarty]

Hi. This means there is a problem with the data you are trying to import. You will need to open the file and try the to find the problem. Or it could be a problem with the data definition. Did you try to define something as a TINYINT?

[@seankarkhanis4319]

@@MattMacarty Hi I fixed the problem! Turns out my string data needed to be in quotes so I had to get it into google sheets and change some of the columns for where the strings are in quotes. Is there a way to fix the settings in mySQL workbench so that the strings don't need quotes? Thank You!

[@Divyansh3527]

Matt please help I can not import time column it shows incorrect decimal value

[@MattMacarty]

I will need a little more to go on. What does sample data look like? What are you trying to do?

[@rajeshakkineni5127]

Bro i dont have run option in sql... Its disabled in my sql.. how can i run the my program

[@MattMacarty]

You may have some security on your machine that is preventing you from running scripts.

[@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.

[@zhiqiangzheng5985]

Thank you, it helps a lot

[@MattMacarty]

Glad it helped

[@jalalsubari2040]

how did you create the data.cvs ?

[@MattMacarty]

I have a script I wrote in Python. I have a series on stock analysis with Python. This video starts in the middle, of the series but you can see how to get and transform data here: kzbin.info/www/bejne/hamaoomim7OMhKc

[@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.

[@maximillianus5453]

The navigator (schema) was not found to me ! What is problem

[@MattMacarty]

Try the refresh button (if I understand you correctly)

[@daniel3897]

Great tutorial. Thanks a lot Matt!!

[@MattMacarty]

Than kyou. Glad it helped.

[@christv0811]

Nice this is so clear

[@MattMacarty]

Thank you. Glad it helped

[@moshekhan5548]

Greetings Matt I have done everything as per your video. The data, despite of "being uploaded successfully in the quantity of 258 rows", still does not appear in my TABLE stocks. Any comment or advice. People around, please, fell free to provide with advice. Thanks

[@MattMacarty]

Did you import it into a table named data? Try running show tables;

[@moshekhan5548]

Greetings Matt. Thanks for your response. I have tried the stocks TABLE table as your video, then, the second method with data TABLE. In boths the MYSQL tells me that "258 records imported". I know that this is a kind service from your side. I think that it is related to the data formating or the "silent" way of MYSQL to not process/show the data uploaded. What I did, was to write the query including the 258 rows, for me to have the visual of the dat and to proceed with the rest of your video-guidance, however, I would like to succeeed on doing it with the wizard. Would be so kind to provide me with the data you have used in your uploading?. I took the data from your link, and it appears to be something related to the data format. Or perhaps something else.

[@raxn2673]

Where do these database files save themselves (these schemas)?

[@MattMacarty]

they are in a folder in your home directory AppData\Roaming

[@Cosmic.Perspective]

Sir, how can I and a friend with a separate workbench to write queries into one database and build it together from two locations?

[@MattMacarty]

For that you need a public MySQL server. You can make your server public, but probably safer to have your friend use RDP and then login to Workbench. If you need a public server there are free options that you can administrate, something like bit.io/

[@Cosmic.Perspective]

@@MattMacarty you are awesome Matt! Thank you for the quick response and instructions!! Happy Thanksgiving

[@bsbteck]

Greate Job Sir

[@MattMacarty]

Glad it helped

[@cocobob9291]

What should I do? It keeps saying 0 record imported

[@MattMacarty]

it sounds like there is a problem with the data you are trying to iport. Usually this is a data type problem.

[@cocobob9291]

@@MattMacarty thanks for the info, but it already worked and I don’t know how it happened.

[@augustusprime0_o]

great tutorial, thanks!

[@MattMacarty]

Glad it helped

[@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”

[@AlToor7]

I am trying to load 15K records from a .csv file to MySql and its taking for ever, file is sitting on the same machine. any idea?

[@MattMacarty]

Sounds like the import is hanging somewhere. Could mean the data needs to be cleaned. Try opening the file with a text editor (not Excel) and see if you can identify any strange characters. It's too bad it's not throwing an error since then it will tell you where the problem is. Try a nice clean restart of your machine too.

[@yousufjamal291]

Sir what is the difference between char and vachar...where we can use char /vachar?

[@MattMacarty]

Char days is a fixed size while varchar can vary up to the limit set. Varchar cannot be used for the primary key column. This is the only restriction I am aware of.

[@laughlive6840]

I can't able to get the data in the table after following all the procedures and executing the query , would you please tell me the solution for that and why that happens?

[@MattMacarty]

This usually means there is a problem with either the data itself, or the way you have defined your table in Workbench. You might visually inspect the data in a text editor to see if there are any special characters. You could try importing into a new table and let Workbench determine the data types as an alternative.

[@teaspells9994]

Hello, how am I able to connect this database to a website?

[@MattMacarty]

That is a fairly complex and involved process. You will need a server side programming language to interact and serve data from a database. People commonly use PHP to develop data driven websites.

[@Bakemono10]

Life Saver tutorial!!!

[@MattMacarty]

Glad it helped

[@ajeetdubey1308]

Really so helpful ❤️

[@MattMacarty]

Thanks. Glad it helped

[@joeryder5739]

Thank you Matt.

[@MattMacarty]

Glad it helped

[@hoormallick879]

Amazing it helped alott💓

[@MattMacarty]

Glad it helped

[@marcelojunior4301]

Thank you so much for this!

[@MattMacarty]

Glad it helped.