MySQL_training_manual

MySQL: A guide for new users for database management

MySQL is an open-source database. It is used for query and operate relational databases.

The website of MySQL: https://www.mysql.com/

To download MySQL, visit: MySQL :: MySQL Downloads

This guide is for using the MySQL Workbench

1 Interface

The MySQL window is divided into four sections. The Navigator panel features two tabs: Administration and Schemas; the Query panel where code is written; the Information panel which also has two tabs: Context Help and Snippets; and the Output window. There is also a ribbon above which contains assorted buttons. These can be used to customize the workbench to the user’s preferences.

Button

Function

Execute or run

2 Relational Databases

MySQL was created to manage data related to other data inside of a database. To get the most use out of the program it is important to know how your data relates to all of the other data you are working with.

For example, say you were to do a study on the health of dairy cows during their lactation cycle, and you had data for categories that included the milk yield, the movement, and the body condition of individual cows throughout the months of their cycle in varying environmental conditions. How can the data be sorted to reduce errors and increase the integration of the various tables?

Look for a unique value that can be cross referenced by you and the software. If the cows in the study have a unique ID that identifies them no matter the day or condition they are in - use it! What matters most is that it is unique in its column and its purpose.

3 Creating Databases

In the navigation panel of your Workbench window, there is a Schemas tab. This is where the program internally organizes the databases you create. Schema and Database are used interchangeably in MySQL but they will refer to the same thing.

Click on the new schema button in the ribbon.

Button

Function

New Schema

4 Adding Tables

There are two ways of bringing tables into MySQL: Importing and Creating. Here we will focus on importing.

To Covert a File:

  1. Open your file in Excel. Its okay if your file isn’t .xlsx, the file should still open if it is tabular data.

  2. Click Save As and change the document type from the default .xlsx to .csv from the drop down menu. Save in a location you can find it later.

To Import the File: There are several ways to import the file.

Approach A: The Import Table button in the MySQL Work Bench can be used to import the file. The steps are detailed as follow:

  1. Click the import button in the MySQL Work Bench.

Button

Function

Import table

  1. Locate the newly converted .csv version of your table and click Open.

  2. Verify that MySQL is adding the data as you intended in the review window, then click Apply

  3. Verify that MySQL is adding the data to the correct database, then click Apply

  4. You should now see your table added to the correct database in the Schema Tab

Approach B: The process can be done using the Table data Import Wizard. You will still need a .csv or .json file so refer to the conversion steps above if the format is incorrect.

  1. Right click your mouse while in the Scheme Tab. The Import Wizard is in the drop-down menu

  2. Click browse and navigate to the file you wish to import

  3. Choose whether to add to an existing table or create a new table

  4. Verify that MySQL is adding the data as you intended in the preview window, then click Next

  5. Monitor the execution of your request, then click Next

  6. You should now see your table added to the correct database in the Schema Tab

Approach C: The process can be automatically done using SQL language

  1. Save your excel file as an csv (comma delimited) (*.csv) file

  2. Save your file to the folder that is similar to “programData/MySQL/MySQL Server 8.0/Uploads”

  3. In MySQL, first create an empty table that has the same structure as the spreadsheet, and then use the “load data infile” statement to import the csv file into MySQL (see the example below).

Approach D: the online tools such as sqlizer tool can also be used to import Excel into MySQL:

  1. Visit the link below, drag and browse your file to that place.

The SQL code should be generated for you to create a table and then insert the data into the table.

Easily convert files into SQL Databases | SQLizer

  1. Copy and paste the SQL code into MySQL, and execute, something like this:

5 Using MySQL

After the table is created or imported into MySQL, the standard SQL statements can be used to query and manipulate the data, such as update data, delete data, insert data, etc.

6 Additional Help

There are many uses for MySQL that have not been covered in this short guide. Learning new software and coding is made trickier if you do not have someone to direct your questions to. Below I have included a list internet resources I found helpful when learning MySQL.

  1. MySQL official developer website. The team behind MySQL wants to help you navigate their software with as much ease as they can provide. Their website includes short tutorials and answers to frequently asked questions. https://dev.mysql.com/

  2. MySQL Tutorial This is third party website that does not seem to be affiliated with the main team at MySQL, even so they have the same goal. https://www.mysqltutorial.org/

  3. W3Schools W3Schools is an online tutorial hub for several coding languages, such as HTML, CSS, JavaScript, Python, and MySQL. It is easy to follow for beginners. https://www.w3schools.com/

PreviousStudent survey Evaluations

Last updated