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.

Reset Auto-Increment
Most of the tables in MySQL use sequential values to represent records, like serial numbers. Instead of manually inserting each value one by one, MySQL uses the "AUTO_INCREMENT" to handle this automatically.
AUTO-INCREMENT in MySQL
AUTO_INCREMENT in MySQL is used to generate unique numbers in ascending order automatically as you add new records to a table. It is very useful for applications that require each row to have a distinct value.
When you define a column as an AUTO_INCREMENT column, MySQL takes care of the rest. It starts with the value 1 and increments it by 1 for each new record you insert, creating a sequence of unique numbers for your table.
Example
The following example demonstrates the usage of AUTO_INCREMENT on a column in database table. Here, we are creating a table named 'insect' with AUTO_INCREMENT applied to the 'id' column.
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
);
Now, you don't need to manually specify values for the 'id' column when inserting records. Instead, MySQL handles it for you, starting with 1 and incrementing by 1 for each new record. To insert values in other columns of the table, use the following query 
INSERT INTO insect (name,date,origin) VALUES
('housefly','2001-09-10','kitchen'),
('millipede','2001-09-10','driveway'),
('grasshopper','2001-09-10','front yard');
The insect table displayed is as follows. Here, we can see that the 'id' column values are automatically generated by MySQL 
id
name
date
origin
1
housefly
2001-09-10
kitchen
2
millipede
2001-09-10
driveway
3
grasshopper
2001-09-10
front yard
The MySQL RESET Auto-Increment
The default AUTO_INCREMENT values on a table start from 1, i.e., the values being inserted usually start from 1. However, MySQL also has a provision to reset these AUTO-INCREMENT values to another number, enabling the sequence to start inserting from the specified reset value.
You can reset the AUTO_INCREMENT value in three ways: using ALTER TABLE, TRUNCATE TABLE, or dropping and recreating the table.
RESET using ALTER TABLE Statement
The ALTER TABLE statement in MySQL is used to update a table or make any alterations in it. Hence, using this statement to reset an AUTO_INCREMENT value is perfectly valid choice.
Syntax
Following is the syntax to reset autoincrement using ALTER TABLE 
ALTER TABLE table_name AUTO_INCREMENT = new_value;
Example
In this example, we are using the ALTER TABLE statement to reset the AUTO_INCREMENT value to 5. Note that the new AUTO_INCREMENT value be greater than the number of records already present in the table 
ALTER TABLE insect AUTO_INCREMENT = 5;
Following is the output obtained 
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
Now, let us insert another value into the table 'insect' created above and check the new result-set, using the following queries
INSERT INTO insect (name,date,origin) VALUES 
('spider', '2000-12-12', 'bathroom'),
('larva', '2012-01-10', 'garden');
We get the result as shown below 
Query OK, 2 row affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
To verify whether the new records you inserted will start with the AUTO_INCREMENT value set to 5, use the following SELECT query 
SELECT * FROM insect;
The table obtained is as shown below 
id
name
date
origin
1
housefly
2001-09-10
kitchen
2
millipede
2001-09-10
driveway
3
grasshopper
2001-09-10
front yard
5
spider
2000-12-12
bathroom
6
larva
2012-01-10
garden
RESET using TRUNCATE TABLE Statement
Another way to reset auto-incrementing column to the default value is by using the TRUNCATE TABLE command. This will delete the existing data of a table, and when you insert new records, the AUTO_INCREMENT column starts from the beginning (usually 1).
Example
Following is an example to reset the AUTO_INCREMENT value to default, i.e. '0'. For that, firstly truncate the 'insect' table created above using the TRUNCATE TABLE Command as follows 
TRUNCATE TABLE insect;
The output obtained is as follows 
Query OK, 0 rows affected (0.04 sec)
To verify whether the records of the table is deleted, use the following SELECT query 
SELECT * FROM insect;
The result produced is as follows 
Empty set (0.00 sec)
Now, insert values again using the following INSERT statement.
INSERT INTO insect (name,date,origin) VALUES
('housefly','2001-09-10','kitchen'),
('millipede','2001-09-10','driveway'),
('grasshopper','2001-09-10','front yard'),
('spider', '2000-12-12', 'bathroom');
After executing the above code, we get the following output 
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
You can verify whether the records in the table have been reset using the following SELECT query 
SELECT * FROM insect;
The table displayed is as follows 
id
name
date
origin
1
housefly
2001-09-10
kitchen
2
millipede
2001-09-10
driveway
3
grasshopper
2001-09-10
front yard
4
spider
2000-12-12
bathroom
Resetting Auto-Increment Using Client Program
We can also reset auto-increment using client program.
Syntax
PHPNodeJSJavaPython
To reset auto-increment through a PHP program, we need to execute the "ALTER TABLE" statement using the mysqli function query() as follows 
$sql = "ALTER TABLE INSECT AUTO_INCREMENT = 5";
$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.');
//lets create a 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)";
if($mysqli->query($sql)){
    printf("Insect table created successfully....!\n");
}
//now lets insert some records
$sql = "INSERT INTO insect (name,date,origin) VALUES ('housefly','2001-09-10','kitchen'), ('millipede','2001-09-10','driveway'), ('grasshopper','2001-09-10','front yard')";
if($mysqli->query($sql)){
    printf("Records inserted successfully....!\n");
}
//display table records
$sql = "SELECT * FROM INSECT";
if($result = $mysqli->query($sql)){
    printf("Table records: \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");
    }
}
//lets reset the autoincrement using alter table statement...
$sql = "ALTER TABLE INSECT AUTO_INCREMENT = 5";
if($mysqli->query($sql)){
    printf("Auto_increment reset successfully...!\n");
}
//now lets insert some more records..
$sql = "INSERT INTO insect (name,date,origin) VALUES ('spider', '2000-12-12', 'bathroom'), ('larva', '2012-01-10', 'garden')";
$mysqli->query($sql);
$sql = "SELECT * FROM INSECT";
if($result = $mysqli->query($sql)){
    printf("Table records(after resetting autoincrement): \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();
Output
The output obtained is as shown below 
Insect table 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
Auto_increment reset successfully...!
Table records(after resetting autoincrement):
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
Id: 5, Name: spider, Date: 2000-12-12, Origin: bathroom
Id: 6, Name: larva, Date: 2012-01-10, Origin: garden    
﻿
﻿
﻿
﻿


﻿

id	name	date	origin
1	housefly	2001-09-10	kitchen
2	millipede	2001-09-10	driveway
3	grasshopper	2001-09-10	front yard

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

Reset Auto-Increment

AUTO-INCREMENT in MySQL

Example

The MySQL RESET Auto-Increment

RESET using ALTER TABLE Statement

Syntax

Example

RESET using TRUNCATE TABLE Statement

Example

Resetting Auto-Increment Using Client Program

Syntax

Example

Output