Installing MySQL

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

1. Showing existing databases

SHOW Databases;

2. Creating a new database

CREATE Database temp_db;

3. Creating a new table inside newly created database

USE temp_db;

CREATE Table test_table

(

id int not null auto_increment,

name varchar(255) not null,

birth_date date,

primary key (id)

)

;

4. Check existing tables

SHOW Tables;

5. Get information about a particular table

DESCRIBE test_table;

6. Add records into a table

INSERT INTO test_table ( name, birth_date) VALUES

  ( 'Amar', '2015-01-03' ),

  ( 'Akbar', '2013-11-13' ),

  ( 'Anthony', '2016-05-21' )

;

7. Select all columns from table, limited to 10 rows

Select * from test_table limit 10;

8. Select using "WHERE" condition

Select * from test_table WHERE name = 'Amar';

9. Delete a record

Delete from test_table where name = 'Anthony';

10. Drop a table from the database

Drop test_table

Loading Data from CSV file to MySQL table using Command Line Interface

1. If your data is present in a excel file (.xls or .xlsx) then first save it as a comma-seperated-values file (.csv)

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

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

2. Give any name and click on "Apply"

3. Expand the schema by clicking on the small drop-down arrow on the left side.

4. Right-click on "Tables" and select "Table Data Import Wizard".

5. Navigate to your .csv file and click on "next" button

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

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

8. Try from step-4 again.

9. Once you are able to see the preview of your file in the "Configure Import settings" window, simply click on Next button twice.

10. Refresh the tables (or schema) in your Workbench, and your table should now be visible.