MySQL is an open-source relational database management system that allows us to store and manage large volume of data. One of its key feature is to export data from a table into various formats and CSV is one of it. CSV stands for “Comma Separated Values” file.

Export MySQL Table into CSV File

To export the MySQL table data into a CSV file, we can use the MySQL “SELECT INTO … OUTFILE” statement. Before exporting any table data into CSV files in the database server, we must ensure the following things −

• The MySQL server”s process must have the read/write privileges to the specified target folder, where CSV file will be created.

• The specified CSV file should be already present in the system (No duplicate file).

The exported CSV file can contain data from one or more tables, and it can be modified to include only particular columns or rows.

Syntax

Following is the syntax of SELECT INTO … OUTFILE statement −

SELECT column_name1, column_name2,...
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file_name.csv''
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''rn

Where,

• INTO OUTFILE is the path and name of the CSV file that we want to export the table data to.

• FIELDS TERMINATED BY is the delimiter that separates the fields in the exported CSV file.

• LINES TERMINATED is the line terminator character for exported CSV file.

Storage Location of Exported .csv File

In MySQL, when you export a file, such as a .csv file, the default storage location for the exported file is determined by the “secure_file_priv” variable.

To find out the default path for exported files, you can use the following SQL query −

SHOW VARIABLES LIKE "secure_file_priv";

We get the following output −

Before exporting data to a .csv file, you will need to have at least one table in your MySQL database. Let us create a table named “CUSTOMERS” using the following SQL query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, we are inserting data into the above created table as shown below −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, NULL, 2000.00 ),
(4, ''Chaitali'', NULL, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, NULL, 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The CUSTOMERS table obtained is as shown below −

Exporting MySQL Data in CSV Format

You can export MySQL data in CSV file using the SELECT INTO … OUTFILE statement. Here, we are exporting the data of CUSTOMERS table into a CSV file named “CUSTOMERS_BACKUP” using the following query −

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn

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.01 sec)

Following is the image of “CUSTOMERS_BACKUP.csv” file when we opened it −

Handling File Already Exists Error −

If you attempt to export data into a file that already exists, MySQL will generate an error −

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn

Following is the error obtained −

ERROR 1086 (HY000): File ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'' already exists

To avoid this error, you can either choose a different filename or delete the existing file before executing the export query.

Removing Enclosing Quotes for Numeric Records −

By default, all records in the CSV file will be enclosed in double quotes, including numeric values. If you want to remove the quotes for numeric records, you can use the OPTIONALLY clause before the ENCLOSED BY clause, as shown below −

SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''rn

Following is the output obtained after executing the above query −

Query OK, 7 rows affected (0.00 sec)

As we can see the image of CSV file below, the double quotes (“”) are removed for the numeric records.

Exporting Table Data Along with Column Headings

To export table data along with their respective column headings, you can use the UNION ALL statement. This allows you to create a row with column names and then append the data rows. Here is an example query −

SELECT ''ID'', ''NAME'', ''EMAIL'', ''PHONE'', ''CITY''
UNION ALL
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS TERMINATED BY '',''
OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''rn

Output

The output obtained is as follows −

Query OK, 8 rows affected (0.01 sec)

Verification

If we verify the .csv file, we can see that the respective column names are added −

Exporting Table Data Without Specifying Column Names

You can also export table data into a CSV file without specifying column names.

Syntax

Following is the syntax to export table data into a CSV file without specifying column names −

TABLE table_name ORDER BY column_name LIMIT 100
INTO OUTFILE ''/path/filename.txt''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn;

Example

In the following query, we are exporting the CUSTOMERS table data into “CUSTOMERS_BACKUP.csv” file without specifying their column names −

TABLE CUSTOMERS ORDER BY NAME LIMIT 100
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn

Output

The result produced is as follows −

Query OK, 7 rows affected (0.01 sec)

Verification

As we can see in the “CUSTOMERS_BACKUP.csv” file, the table data got exported −

Replacing NULL Values

If your table contains NULL values, you can use the IFNULL() function to replace them with specific values before exporting the data to a CSV file.

Example

In the following query, the IFNULL() function is used to replace NULL values in the “ADDRESS” column with “NULL_VALUE” before exporting the data as shown below −

SELECT ID, NAME, AGE, IFNULL(ADDRESS, ''NULL_VALUE'') FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn

Output

The result obtained is as shown below −

Query OK, 7 rows affected (0.00 sec)

Verification

The NULL values (N) are replaced with “NULL_VALUE” −

Using Client Program

We can also export table into CSV file using Client Program.

Syntax

$sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY
'' ESCAPED BY '''' LINES TERMINATED BY ''rn''";
$mysqli->query($sql);

sql = `SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new1.csv'' FIELDS ENCLOSED BY ''"'' TERMINATED BY
'' ESCAPED BY ''"'' LINES TERMINATED BY ''rn''`;
con.query(sql);

String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY
'' ESCAPED BY '''' LINES TERMINATED BY ''\r\n''";
statement.execute(sql);

sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''FIELDS ENCLOSED BY ''"''TERMINATED BY ''
'' ESCAPED BY ''"''LINES TERMINATED BY ''rn''"
cursorObj.execute(sql)

Example

Following are the programs −

$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 = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY '' ESCAPED BY '''' LINES TERMINATED BY ''rn''";
if($result = $mysqli->query($sql)){
   printf("Table data exported successfully....!n");
   print_r($result);
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table data exported successfully....!
1

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = `SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new1.csv'' FIELDS ENCLOSED BY ''"'' TERMINATED BY '' ESCAPED BY ''"'' LINES TERMINATED BY ''rn''`;
 con.query(sql, function(err, result){
    console.log("Table data exported successfully...!");
    console.log("Data: ")
    if (err) throw err;
    console.log(result);
    });
});

Table data exported successfully...!
Data:
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 7,
  insertId: 0,
  info: '''',
  serverStatus: 34,
  warningStatus: 0,
  changedRows: 0
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ExportTableToCSVFile {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP_new.csv'' FIELDS ENCLOSED BY '''' TERMINATED BY '' ESCAPED BY '''' LINES TERMINATED BY ''\r\n''";
            st.execute(sql);
            System.out.println("Successfully...! table exported into CSV file..");
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Successfully...! table exported into CSV file..

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Query to export table into csv file
sql = """
SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv''
FIELDS ENCLOSED BY ''"''
TERMINATED BY ''
ESCAPED BY ''"''
LINES TERMINATED BY ''rn
"""
cursorObj.execute(sql)
print("Table data expoted successfully")
# Reading and displaying the exported CSV file
with open(''C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/CUSTOMERS_BACKUP.csv'', ''r'') as csvfile:
    for row in csvfile:
        # Use strip() to remove extra newlines
        print(row.strip())
# Closing the cursor and connection
cursorObj.close()
connection.close()

Table data expoted successfully
"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 – Arithmetic Operators

Arithmetic operators in MySQL are tools for performing mathematical calculations, similar to how we use them in basic math. They allow us to manipulate numeric values in your database, just like we would in simple math problems. Following are the arithmetic operators in MySQL −

The MySQL Common Table Expression

The MySQL Common Table Expression (CTE) is a temporary result-set or table that exists for the duration of a single query. We can use the CTEs to reference the result-set within the context of a single SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, or MERGE statement.

The scope of a CTE is limited to that specific query. It makes complex queries simple by breaking them into simple blocks.

The MySQL WITH Clause

The MySQL WITH clause is used to create CTEs by having one or more comma-separated subclauses. The subclauses provide a subquery that generates the result-set.

WITH clause cannot be used in MySQL versions before 8.0.

Syntax

Following is the syntax of the MySQL WITH clause −

WITH name_for_summary_data AS (SELECT Statement)
SELECT columns
FROM name_for_summary_data
WHERE conditions  (
   SELECT column
   FROM name_for_summary_data
)
[ORDER BY columns]

Example

Assume we have created a table named DATA containing details such as id, name, age and salary −

CREATE TABLE DATA(
   ID INT,
   NAME CHAR(20),
   AGE INT,
   SALARY INT
);

Now, let us insert values into the table created above using the INSERT INTO statement as shown below −

INSERT INTO DATA VALUES
(101, ''John'', 25, 55452),
(102, ''Jane'', 29, 66458),
(103, ''Arub'', 35, 36944);

In the following example, the WITH clause is used to create a CTE named CTE, which is then queried to retrieve the data from the DATA table −

WITH CTE AS
(Select ID, NAME, AGE, SALARY FROM DATA)
SELECT * FROM CTE;

After executing the above code, we get the output as follows −

CTE from Multiple Tables

We can create CTEs from multiple tables by separating each CTE subclause using a comma (”,”).

Example

Suppose we have created a table with name EMPLOYEE and populated data into it as shown below −

CREATE TABLE EMPLOYEE(
   ID INT NOT NULL,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);

Here, we are inserting records into the EMPLOYEE table −

INSERT INTO EMPLOYEE VALUES
(101, ''Serena'', ''Williams'', 27, ''F'', 9000, 101),
(102, ''Virat'', ''Kohli'', 20, ''M'', 6000, 102);

The EMPLOYEE table obtained is as follows −

Now, we create another table CONTACT −

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);

Let us insert some records in the CONTACT table −

INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
(101, ''serena@mymail.com'', ''Hyderabad''),
(102, ''virat@mymail.com'', ''Vishakhapatnam'');

The CONTACT table produced is as follows −

Following example uses Common Table Expressions (CTEs) named “exp1” and “exp2” to separately select specific columns from the EMPLOYEE and CONTACT tables. The final SELECT statement joins these CTEs, combining the chosen columns from each −

WITH
exp1 AS (SELECT ID, FIRST_NAME, LAST_NAME FROM EMPLOYEE),
exp2 AS (SELECT EMAIL, PHONE FROM CONTACT)
SELECT * FROM exp1 JOIN exp2;

Following is the output of the above code −

In MySQL, we have a set of functions using which we can manipulate the date and time values. Following are the MySQL date time functions −

The MySQL UUID function

The MySQL UUID() function is used to generate “Universal Unique Identifiers” (UUIDs) in accordance with RFC 4122. UUIDs are designed to be universally unique, even when generated on different servers. The UUID is generated using a combination of the current timestamp, the unique identifier of the server, and a random number.

UUID Format

The UUID value is represented as a UTF-8 string and is a 128-bit number. The format of the UUID value is in hexadecimal number, and it consists of five segments which are separated by hyphens.

The general format of the UUID value is: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee, where each segment represents a hexadecimal value.

Generating a UUID

Following is the basic example to generate a UUID using the UUID() function in MySQL −

SELECT UUID();

Output

It will display a universal unique identifier as shown below −

Generating Multiple UUIDs

You can generate multiple UUIDs in a single query, and each UUID will be different as shown below −

SELECT UUID() AS ID1, UUID() AS ID2;

Output

The output will show two different UUIDs, with differences generally in the first segment −

UUIDs in a Database Table

You can use UUIDs as unique identifiers in a database table. Following is an example of how to create a table with a UUID column and insert data −

Here, we are first creating a table with the name “ORDERS”, with an ORDER_ID column of type VARCHAR using the following query −

CREATE TABLE ORDERS(
   ID int auto_increment primary key,
   NAME varchar(40),
   PRODUCT varchar(40),
   ORDER_ID varchar(100)
);

Now, we are inserting data into the ORDERS table, using the UUID() function to generate unique values for the ORDER_ID column −

INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Varun", "Headphones", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Priya", "Mouse", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Nikhil", "Monitor", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Sarah", "Keyboard", UUID());
INSERT INTO ORDERS (NAME, PRODUCT, ORDER_ID) VALUES ("Vaidhya", "Printer", UUID());

Following is the ORDERS table obtained −

Modifying UUIDs

You can modify UUIDs without losing their uniqueness. For example, you can remove hyphens or convert them to base64 notation using functions like REPLACE() and TO_BASE64().

Example

Here, we are updating the UUID value for the record where ID = 1 using the following query −

UPDATE ORDERS SET ORDER_ID = UUID() WHERE ID=1;

Output

Following is the output of the above code −

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the modified UUID values, we can use the following SELECT query −

SELECT * FROM ORDERS;

As we can see in the output below, every time we execute the UUID() function, we get a different UUID value −

Example

Assume the previously created table and let us remove hyphens from the UUID of the row with ID = 2 using the REPLACE() function as shown below −

UPDATE ORDERS
SET ORDER_ID = REPLACE(UUID(), ''-'', '''')
WHERE ID = 2;

Output

Output of the above code is as follows −

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the modified UUID value, we can use the following SELECT query −

SELECT * FROM ORDERS;

As we can see in the output below, the UUID of row = 2 is modified without disturbing the “unique” part of it −

Example

In the following query, we are converting the UUID of ID = 4 to base64 notation using the TO_BASE64() function −

UPDATE ORDERS
SET ORDER_ID = TO_BASE64(UNHEX(REPLACE(UUID(),''-'','''')))
WHERE ID=4;

Output

The result produced is as follows −

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Let us verify the modified UUID value using the following SELECT query −

SELECT * FROM ORDERS;

The output produced is as given below −

In general, aggregation is a consideration of a collection of objects that are bound together as a single entity. MySQL provides a set of aggregate functions that perform operations on all the entities of the column of a table considering them as a single unit. Following are the MySQL aggregate functions −

MySQL – String Functions

MySQL string functions are used to manipulate the string values. The following table details the string functions that are available in the MySQL.

MySQL – Numeric Functions

MySQL numeric functions are used primarily for numeric manipulation and/or mathematical calculations. The following table details the numeric functions that are available in the MySQL.

The MySQL NULL Values

MySQL uses the term “NULL” to represent a non-existent data value in the database. These values are different from an empty string or zero and do not occupy any storage space in the database. They are used to indicate the absence of a value or an unknown value in a data field.

There are some common reasons why a value may be NULL −

• The value may not be provided during data entry.

• The value is not yet known.

Since NULL values are non-existent, you cannot use standard comparison operators such as “=”, “<,” or “>” with them. Instead, you can use the “IS NULL,” “IS NOT NULL,” or “NOT NULL” operators to check if a value is NULL.

Creating a Table without NULL Values

To create a table without NULL values, you can use the “NOT NULL” keyword while defining the columns. If a column is specified as “NOT NULL,” an error will occur when attempting to insert NULL values into that specific column.

Syntax

The basic syntax for creating a table with “NOT NULL” columns is as follows −

CREATE TABLE table_name (
   column1 datatype NOT NULL,
   column2 datatype NOT NULL,
   ...
   columnN datatype
);

Where, “NOT NULL” indicates that a column must always contain a specific value of the defined data type. Columns marked as “NOT NULL” cannot accept NULL values. On the other hand, you can insert NULL values into the columns without the “NOT NULL” constraint.

Example

Let us create a table named “CUSTOMERS” using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert some records into the above-created table −

INSERT INTO CUSTOMERS 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, ''Hyderabad'', NULL),
(7, ''Muffy'', 24, ''Indore'', NULL);

The CUSTOMERS table obtained is as follows −

Now, to retrieve records that are not NULL, you can use the “IS NOT NULL” operator as shown below−

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;

Following is the output of the above code −

To retrieve records that are NULL, you can use the “IS NULL” operator as shown below −

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;

The output produced is as follows −

Updating NULL Values in a Table

To update NULL values in a table, you can use the “UPDATE” statement with the “IS NULL” operator. This filter the rows containing NULL values and set new values using the “SET” keyword.

Example

Here, we are updating the NULL values in the SALARY column of the CUSTOMERS table as shown below −

UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL;

Output

After executing the above code, we get the following output −

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

You can verify whether the records in the table are updated or not using the following query −

SELECT * FROM CUSTOMERS;

The output displayed is as follows −

Deleting Records with NULL Values

To delete records with NULL values from a table, you can use the “DELETE FROM” statement with the “IS NULL” operator in the “WHERE” clause.

Example

Now, we are deleting records with NULL values in the SALARY column as shown below −

DELETE FROM CUSTOMERS WHERE SALARY IS NULL;

Output

Output of the above code is as shown below −

Query OK, 2 rows affected (0.01 sec)

Verification

You can verify whether the records in the table is deleted or not using the SELECT statement as follows −

SELECT * FROM CUSTOMERS;

The table produced is as shown below −

NULL Value Using a Client Program

We can also execute NULL value using the client program.

Syntax

$sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NULL";
$mysqli->query($sql);

sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NOT NULL";
con.query(sql);

String sql = "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL";
statement.executeQuery(sql);

null_values_query = "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL"
cursorObj.execute(null_values_query)

Example

Following are the programs −

$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 = "SELECT * from tcount_tbl WHERE tutorial_count IS NULL";
if($result = $mysqli->query($sql)){
    printf("Table record: n");
    while($row = mysqli_fetch_array($result)){
        printf("Tutorial_author %s, Tutorial_count %d",
        $row[''tutorial_author''],
        $row[''tutorial_count'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table record:
Tutorial_author mahnaz, Tutorial_count 0
Tutorial_author Jen, Tutorial_count 0

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT * from tcount_tbl WHERE tutorial_count IS NOT NULL";
 if(con.query(sql)){
    console.log("Select query executed successfully....!");
 }
 else{
    console.log("Error");
 }
 console.log("Table records: ");
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Select query executed successfully....!
Table records:
[
  { tutorial_author: ''mahran'', tutorial_count: 20 },
  { tutorial_author: ''Gill'', tutorial_count: 20 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NullValues {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = " SELECT * FROM tcount_tbl WHERE tutorial_count = NULL";
            rs = st.executeQuery(sql);
            System.out.println("Table records(tutorial_count = null): ");
            while(rs.next()) {
               String tutorial_author = rs.getString("tutorial_author");
               String tutorial_count = rs.getString("tutorial_count");
               System.out.println("Author: " + tutorial_author + ", Tutorial_count: " + tutorial_count);
            }
            String sql1 = "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL";
            rs = st.executeQuery(sql1);
            System.out.println("Table records(tutorial_count is null): ");
            while(rs.next()) {
               String tutorial_author = rs.getString("tutorial_author");
               String tutorial_count = rs.getString("tutorial_count");
               System.out.println("Author: " + tutorial_author + ", Tutorial_count: " + tutorial_count);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

This will generate the following output -
Table records(tutorial_count = null):
Table records(tutorial_count is null):
Author: mahnaz, Tutorial_count: null
Author: Jen, Tutorial_count: null

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
null_values_query = f"""
SELECT * FROM tcount_tbl
WHERE tutorial_count IS NULL
"""
cursorObj.execute(null_values_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

(''mahnaz'', None)
(''Jen'', None)

A sequence is a series of integers, starting from 1 and incrementing by 1 with each successive value. These sequences are usually used in databases, as many applications require each row in a table to have a unique identifier, and sequences provide an easy way to generate such values.

Sequences in MySQL

MySQL does not have a built-in sequence feature but provides an alternative in the form of the AUTO_INCREMENT column, which serves a similar purpose.

In MySQL, the AUTO_INCREMENT attribute is used to automatically generate unique integer values (sequences) for a column. By default, this sequence begins with an initial value of 1 and increments by 1 for each new row that is added.

Syntax

Following is the syntax of AUTO_INCREMENT attribute in MySQL −

CREATE TABLE table_name (
   column1 datatype AUTO_INCREMENT,
   column2 datatype,
   column3 datatype,
   ...
   columnN datatype
);

Example

In the following example, we are creating a table named “CUSTOMERS” and, in addition, defining the AUTO_INCREMENT attribute for the “ID” column of the table −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert some records into the above-created table −

INSERT INTO CUSTOMERS VALUES
(NULL, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(NULL, ''Khilan'', 25, ''Delhi'', 1500.00),
(NULL, ''Kaushik'', 23, ''Kota'', 2000.00),
(NULL, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(NULL, ''Hardik'', 27, ''Bhopal'', 8500.00),
(NULL, ''Komal'', 22, ''Hyderabad'', 4500.00),
(NULL, ''Muffy'', 24, ''Indore'', 10000.00);

Output

We can see in the table displayed below that the values in the “ID” column are automatically incremented −

Retrieving AUTO_INCREMENT Values

To obtain AUTO_INCREMENT values in MySQL, you can use the LAST_INSERT_ID() SQL function. This function can be used in any client that can issue SQL statements. Alternatively, in PERL and PHP scripts, specific functions are available to retrieve the auto-incremented value of the last record.

PERL Example

You can access the AUTO_INCREMENT value generated by a query using the mysql_insertid attribute. This attribute can be accessed either through a database handle or a statement handle, depending on how you execute the query.

The following example references it through the database handle −

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES(''moth'',''2001-09-14'',''windowsill'')");
my $seq = $dbh->{mysql_insertid};

PHP Example

After executing a query that generates an AUTO_INCREMENT value, you can retrieve the value using the mysql_insert_id( ) command −

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES(''moth'',''2001-09-14'',''windowsill'')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Renumbering an Existing Sequence

In some cases, you may need to re-sequence records in a table, especially if you have deleted many records. Be careful when resequencing if your table is related to other tables through joins.

If you determine that the resequencing of an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the AUTO_INCREMENT column from the table, then add it again.

Example

The following example shows how to renumber the id values in the table using this technique.

ALTER TABLE CUSTOMERS DROP id;
ALTER TABLE CUSTOMERS
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

Output

Following is the output obtained −

Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

Starting a Sequence at a Specific Value

By default, MySQL starts sequences from 1, but you can specify a different initial value when creating the table.

Example

The following example demonstrates how to start the sequence from 100 during table creation −

CREATE TABLE CUSTOMERS (
   ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (ID),
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
)AUTO_INCREMENT = 100;

Output

Output of the above code is as shown below −

Query OK, 0 rows affected (0.04 sec)

Alternatively, you can create the table first and then set the initial sequence value using the ALTER TABLE command as shown below −

ALTER TABLE CUSTOMERS AUTO_INCREMENT = 100;

Sequence Using a Client Program

We can also create a sequence using the client program.

Syntax

$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
$mysqli->query($sql);

sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
con.query(sql);

String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
statement.execute(sql);

create_table_query = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) NOT NULL, date DATE NOT NULL, origin VARCHAR(30) NOT NULL)"
cursorObj.execute(create_table_query)

Example

Following are the programs −

$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.
'');
//create table with autoincrement sequene
$sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
if($mysqli->query($sql)){
    printf("Table created successfully....!n");
}
//let''s insert some record
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen'')";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!n");
}
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''millipede'',''2001-09-10'',''driveway'')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!n");
}
$sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''grasshopper'',''2001-09-10'',''front yard'')";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!n");
}
printf("Table records: n");
$sql = "SELECT * FROM insect";
if($result = $mysqli->query($sql)){
    printf("Table record: n");
    while($row = mysqli_fetch_array($result)){
        printf("Id: %d, Name: %s, Date: %s, Origin %s",
        $row[''id''],
        $row[''name''],
        $row[''date''],
        $row[''origin'']);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records:
Table record:
Id: 1, Name: housefly, Date: 2001-09-10, Origin kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin front yard

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 //create table
 sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
 con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Table created successfully....!");
    });
//now let''s insert some record
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("First record inserted successfully...!");
    });
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''millipede'',''2001-09-10'',''driveway'')";
con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Second record inserted successfully...!");
    });
sql = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''grasshopper'',''2001-09-10'',''front yard'')";
    con.query(sql, function(err, result){
    if (err) throw err;
    console.log("Third record inserted successfully...!");
    });
sql = "SELECT * FROM INSECT";
con.query(sql, function(err, result){
console.log("Table records(with ID auto_increment sequence).");
    if (err) throw err;
    console.log(result);
    });
});

Table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Table records(with ID auto_increment sequence).
[
  {
    id: 1,
    name: ''housefly'',
    date: 2001-09-09T18:30:00.000Z,
    origin: ''kitchen''
  },
  {
    id: 2,
    name: ''millipede'',
    date: 2001-09-09T18:30:00.000Z,
    origin: ''driveway''
  },
  {
    id: 3,
    name: ''grasshopper'',
    date: 2001-09-09T18:30:00.000Z,
    origin: ''front yard''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Sequence {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create table
            String sql = "CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL,date DATE NOT NULL,origin VARCHAR(30) NOT NULL)";
            st.execute(sql);
            System.out.println("Table ''insect'' created successfully...!");
            //lets insert some records into the table
            String sql1 = "INSERT INTO insect (id,name,date,origin) VALUES(NULL,''housefly'',''2001-09-10'',''kitchen''), (NULL,''millipede'',''2001-09-10'',''driveway''), (NULL,''grasshopper'',''2001-09-10'',''front yard'')";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //lets print table records
            String sql2 = "SELECT * FROM INSECT";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("name");
               String date = rs.getString("date");
               String origin = rs.getString("origin");
               System.out.println("Id: " + id + ", Name: " + name + ", Date: " + date + ", Origin: " + origin);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Table ''insect'' created successfully...!
Records inserted successfully...!
Table records:
Id: 1, Name: housefly, Date: 2001-09-10, Origin: kitchen
Id: 2, Name: millipede, Date: 2001-09-10, Origin: driveway
Id: 3, Name: grasshopper, Date: 2001-09-10, Origin: front yard

import mysql.connector
# Connecting to MySQL
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="tut"
)
# Creating a cursor object
cursorObj = con.cursor()
# Creating the table
create_table_query = """
CREATE TABLE insect (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL,
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)
"""
cursorObj.execute(create_table_query)
print("Table ''insect'' is created successfully!")
# Inserting records
sql = "INSERT INTO insect (name, date, origin) VALUES (%s, %s, %s)"
values = [
    (''housefly'', ''2001-09-10'', ''kitchen''),
    (''millipede'', ''2001-09-10'', ''driveway''),
    (''grasshopper'', ''2001-09-10'', ''front yard'')
]
cursorObj.executemany(sql, values)
print("Data inserted into the ''insect'' table.")
con.commit()
# Retrieving records
cursorObj.execute("SELECT * FROM insect")
records = cursorObj.fetchall()
# Printing the records
print("Table records(with ID auto_increment sequence).")
for record in records:
    print(record)
# Closing the connection
cursorObj.close()
con.close()

Table ''insect'' is created successfully!
Data inserted into the ''insect'' table.
Table records(with ID auto_increment sequence).
(1, ''housefly'', datetime.date(2001, 9, 10), ''kitchen'')
(2, ''millipede'', datetime.date(2001, 9, 10), ''driveway'')
(3, ''grasshopper'', datetime.date(2001, 9, 10), ''front yard'')