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.

Sequences
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 
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
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
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
PHPNodeJSJavaPython
To create a sequence on a column of a table through a PHP program, we need to specify auto_increment for a specific column while creating the table using the mysqli function query() as follows 
$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);
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.');
//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();
Output
The output obtained is as shown below 
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   
﻿
﻿
﻿
﻿
﻿
﻿

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

Sequences

Sequences in MySQL

Syntax

Example

Output

Retrieving AUTO_INCREMENT Values

PERL Example

PHP Example

Renumbering an Existing Sequence

Example

Output

Starting a Sequence at a Specific Value

Example

Output

Sequence Using a Client Program

Syntax

Example

Output