MySQL - 序列

要透過 JavaScript 程式在表的列上建立序列，我們需要在使用mysql2庫的query()函式建立表時為特定列指定 auto_increment，如下所示：

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);

要透過 Java 程式在表的列上建立序列，我們需要在使用JDBC函式execute()建立表時為特定列指定 auto_increment，如下所示：

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);

要透過 Python 程式在表的列上建立序列，我們需要在使用MySQL Connector/Python的execute()函式建立表時為特定列指定 auto_increment，如下所示：

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)

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://: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')