Maybe you've been studying SQL for some time now and have mastered all the commands and syntax. What next? The knowledge you gained will quickly disappear if you don't have a way to practice on meaningful data.
Most tutorials use sample data and simple problems (such as finding employee salaries). While they are good to learn basics, honing your skill further requires practice on real-world datasets.
For this reason, you can install MySQL on your machine and upload datasets from CSV files to local server. You can then run SQL commands on this dataset.
To install MySQL, you can visit the official site (https://dev.mysql.com/doc/mysql-getting-started/en/) and follow the instructions as per your machine (Windows/Mac).
You can download step-by-step guide on setting up MySQL server by clicking on the "Download MySQL setup guide" below.
Basic Operations with MySQL
Showing existing databases
Creating a new database
Creating a new table inside newly created database
Check existing tables
Get information about a particular table
Add records into a table
Select all columns from table, limited to 10 rows
Select using "WHERE" condition
Delete a record
Drop a table from the database
Loading Data from CSV file to MySQL table using Command Line Interface
If your data is present in a excel file (.xls or .xlsx) then first save it as a comma-seperated-values file (.csv)
Check whether loading local data is enabled using following steps:
Launch "MySQL 8.0 Command Line Client" from start menu or open Windows run menu (windows + R) and type CMD > Enter
Type mysql -u root -p
Login using your root password
run the command: mysql > show global variables like 'local_infile';
If the Value is OFF, then enable it using the command: mysql > SET GLOBAL local_infile=1;
Ensure the value is now ON, then exit using the command: mysql> exit
3. Load data using the following commands:
mysql> use db_name; -- mention your database name
mysql> load data local infile '/path/file_name.csv' into table table_name; -- mention your path, file name and table name
Ensure your path name has forward slashes / and not backward \
Ensure your dates are in the format "yyyy-mm-dd"
4. If you get an error as "The MySQL server is running with the --secure-file-priv option so it cannot execute this statement", then try placing your .csv file in the following location and upload it from there: C:\ProgramData\MySQL\MySQL Server 8.0\Uploads
Check this documentation from MySQL: https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html
Loading Data from CSV file to MySQL table using MySQL Workbench
Create a new schema by (a) clicking on the "new schema" button or (b) right click on the white space in your "Schemas" window and select "Create Schema" option
Give any name and click on "Apply"
Expand the schema by clicking on the small drop-down arrow on the left side.
Right-click on "Tables" and select "Table Data Import Wizard".
Navigate to your .csv file and click on "next" button
If you get any error, close the box and click on the "wrench" icon at the top and ensure that separator is selected as comma (,) and not anything else such as semi-colon ";" or pipe "|", etc.
If you are still getting error, check that your csv file is not having any quotes or commas (" or ' or ,) in any of the fields. If so, replace them with other characters such as underscore ( _ ) or blank or nothing.
Try from step-4 again.
Once you are able to see the preview of your file in the "Configure Import settings" window, simply click on Next button twice.
Refresh the tables (or schema) in your Workbench, and your table should now be visible.
Frequently Asked Questions (FAQs)
How to install MySQL on local computer?
One can find detailed instructions on installing MySQL at this link: https://dev.mysql.com/doc/mysql-getting-started/en/.
Having visual studio 2015,2017 or 2019 is a requirement for MySQL installation. If its not present on your machine already, then you may have to download and install it first. You can download visual studio from this link: https://visualstudio.microsoft.com/. Select 2019 version.
What is the Difference between mysql-installer-web-community and mysql-installer-community?
Both type of installations can result in the same final product. The "web-community" version does not come pre-bundled with any MySQL applications but instead downloads the individual products chosen during installation. The "web-community" is online installer, i.e. one needs to connect to the online server and installations files get downloaded during installation. The non-web standard version is an offline installer and comes pre-loaded with full features and products. This is also the reason that the standard non-web version is larger in size than the web-version, but installed files might take approximately the same space if you install all the products/features.