MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Import CSV into database
Import MySQL CSV into Database
To import the MySQL data from a CSV file into a database table, we can use the MySQL LOAD DATA INFILE statement.
﻿
Before importing the CSV file into the database server, we must ensure the following things 
Database Table − Ensure you have a database table already set up to receive the incoming data.
CSV File − You need a CSV file containing the data to be imported.
User Privileges − Ensure your account has the necessary privileges, specifically FILE and INSERT, to perform this operation.
Matching Columns − The target table and the CSV file should have matching columns with the same data types.
CSV Format − The CSV file should be in a comma-separated format, with each row representing a record.
Syntax
Following is the syntax of the LOAD DATA INFILE statement in MySQL 
LOAD DATA INFILE file_path
INTO TABLE table_name
FIELDS TERMINATED BY delimiter
ENCLOSED BY enclosure
LINES TERMINATED BY line_separator
IGNORE number_of_lines_to_skip;
Where,
file_path is the path to the CSV file that contains the data to be imported.
table_name is the name of the target table, where the data will be imported.
delimiter is a character that separates each record in the CSV file.
Enclosure is a character that encloses string records in the CSV file.
line_seperator is a character that marks the end of a line in the CSV file.
number_of_lines_to_skip is the number of lines to ignore at the beginning of the CSV file.
Example
First of all, let us create a table with the name EMPLOYEES using the following query 
CREATE TABLE EMPLOYEES(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25) NOT NULL,
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);
Now, let us insert rows into the above created table 
INSERT INTO EMPLOYEES VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'MP', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
The EMPLOYEES table obtained is as shown below 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
MP
4500.00
7
Muffy
24
Indore
10000.00
Export Data to CSV −
Now, we export the data from the EMPLOYEES table into a CSV file named "EMPLOYEES_BACKUP" using the following query 
SELECT * FROM EMPLOYEES
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
After executing the above query, the CSV format file will be created at the specified path. Following is the output obtained after executing the above query 
Query OK, 7 rows affected (0.00 sec)
Following is the image of "EMPLOYEES_BACKUP.csv" file when we opened it 
﻿
Create Another Table −
Now, let us create another table named "CUSTOMERS" with the same columns and data types as EMPLOYEES table 
CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25) NOT NULL,
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);
Following is the output of the above code 
Query OK, 0 rows affected (0.03 sec)
Import Data from CSV 
Now, we import all the data from "EMPLOYEES_BACKUP.csv" file into the CUSTOMERS table using the following query 
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv'
INTO TABLE CUSTOMERS
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
The result obtained is as follows 
Query OK, 7 rows affected (0.01 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0
Verify Data Import 
To verify that the data has been successfully imported into the CUSTOMERS table, we can use the following SELECT statement 
SELECT * FROM CUSTOMERS;
As we can see the output below, the CUSTOMERS table contains the same data as the EMPLOYEES table, as the CSV data has been imported successfully 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
MP
4500.00
7
Muffy
24
Indore
10000.00
Importing a CSV File Using Client Program
We can also import CSV file into database using Client Program.
Syntax
PHPNodeJSJavaPython
To import CSV file into database through a PHP program, we need to execute the "LOAD DATA INFILE" statement using the mysqli function query() as follows 
$sql = "LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv' INTO TABLE CUSTOMERS FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n'";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
$sql = "CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )";
if($mysqli->query($sql)){
    printf("Employees table created successfully...!\n");
}
//now lets insert some data into it..
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 
32, 'Ahmedabad', 2000.00 )";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!\n");
}
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 
25, 'Delhi', 1500.00 )";
 if($mysqli->query($sql)){
    printf("Second record inserted successfully...!\n");
}
$sql = "INSERT INTO EMPLOYEES (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 
23, 'Kota', 2000.00 )";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!\n");
}
//display table record
$sql = "SELECT * FROM EMPLOYEES";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        printf("ID: %d, NAME: %s, AGE: %d, ADDRESS: %s, SALARY: %f", 
            $row['ID'],
            $row['NAME'],
            $row['AGE'],
            $row['ADDRESS'],
            $row['SALARY']);
            printf("\n");
    }
}
//now let's export the table data into csv file
$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM EMPLOYEES INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv' FIELDS ENCLOSED BY '' TERMINATED BY ';' ESCAPED BY '' LINES TERMINATED BY '\r\n'";
if($result = $mysqli->query($sql)){
   printf("Table data exported successfully....!\n");
   print_r($result , "\n");
}
//now let's create a customers table
$sql = "CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25) NOT NULL, SALARY DECIMAL(18, 2), PRIMARY KEY(ID) )";
if($mysqli->query($sql)){
    printf("Customers table created successfully...!\n");
}
//now lets import employees table data csv file into customers table
$sql = "LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EMPLOYEES_BACKUP.csv' INTO TABLE CUSTOMERS FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n'";
if($mysqli->query($sql)){
    printf("CSV file data imported successfully into Customers table....!\n");
}
//print customers table records
$sql = "SELECT * FROM CUSTOMERS";
if($result = $mysqli->query($sql)){
    printf("Customers table records after importing csv file data into it: \n");
    while($row = mysqli_fetch_array($result)){
        printf("ID: %d, NAME: %s, AGE: %d, ADDRESS: %s, SALARY: %f", 
            $row['ID'],
            $row['NAME'],
            $row['AGE'],
            $row['ADDRESS'],
            $row['SALARY']);
            printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as shown below 
Employees table created successfully...!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records:
ID: 1, NAME: Ramesh, AGE: 32, ADDRESS: Ahmedabad, SALARY: 2000.000000
ID: 2, NAME: Khilan, AGE: 25, ADDRESS: Delhi, SALARY: 1500.000000
ID: 3, NAME: kaushik, AGE: 23, ADDRESS: Kota, SALARY: 2000.000000
Table data exported successfully....!
Customers table created successfully...!
CSV file data imported successfully into Customers table....!
Customers table records after importing csv file data into it:
ID: 1, NAME: Ramesh, AGE: 32, ADDRESS: Ahmedabad, SALARY: 2000.000000
ID: 2, NAME: Khilan, AGE: 25, ADDRESS: Delhi, SALARY: 1500.000000
ID: 3, NAME: kaushik, AGE: 23, ADDRESS: Kota, SALARY: 2000.000000
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	MP	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Import CSV into database

Import MySQL CSV into Database

Syntax

Example

Importing a CSV File Using Client Program

Syntax

Example

Output