板凳-------Mysql cookbook学习 （十一--------1)

第11章：生成和使用序列
11.0 引言

11.1 创建一个序列列并生成序列值

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
   );
字段说明
‌id‌：主键，自动递增。
‌name‌：字符串类型，存储昆虫的名称。
‌date‌：日期类型，存储采集日期。
‌origin‌：字符串类型，存储采集地点。

mysql> select * from insect;
+----+-------------------+------------+------------------+
| id | name              | date       | origin           |
+----+-------------------+------------+------------------+
|  1 | housefly          | 2014-09-10 | kitchen          |
|  2 | millipede         | 2014-09-10 | driveway         |
|  3 | grasshopper       | 2014-09-10 | front yard       |
|  4 | stink bug         | 2014-09-10 | front yard       |
|  5 | cabbage butterfly | 2014-09-10 | garden           |
|  6 | ant               | 2014-09-10 | back yard        |
|  7 | ant               | 2014-09-10 | back yard        |
|  8 | termite           | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
8 rows in set (0.01 sec)

mysql> describe insect;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30)  | NO   |     | NULL    |                |
| date   | date         | NO   |     | NULL    |                |
| origin | varchar(30)  | NO   |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

id 字段是自增主键（auto_increment），这意味着 MySQL 会自动为新记录分配唯一的 ID 值。
更新现有记录：如果需要修改 ID 为 3 的记录，使用UPDATE语句：
sql
UPDATE insect 
SET name = 'cricket', 
    date = '2014-09-10', 
    origin = 'basement' 
WHERE id = 3;
忽略重复：如果允许重复数据，可以使用INSERT IGNORE（但会静默失败）：
sql
INSERT IGNORE INTO insect (id, name, date, origin) 
VALUES (3, 'cricket', '2014-09-10', 'basement');

替换数据：使用REPLACE INTO覆盖现有记录：
sql
REPLACE INTO insect (id, name, date, origin) 
VALUES (3, 'cricket', '2014-09-10', 'basement');

11.2 为序列列选择数据类型

11.3 序列生成的行删除的效果

mysql> select * from insect order by id;
+----+-------------------+------------+------------------+
| id | name              | date       | origin           |
+----+-------------------+------------+------------------+
|  1 | housefly          | 2014-09-10 | kitchen          |
|  2 | millipede         | 2014-09-10 | driveway         |
|  3 | grasshopper       | 2014-09-10 | front yard       |
|  4 | stink bug         | 2014-09-10 | front yard       |
|  5 | cabbage butterfly | 2014-09-10 | garden           |
|  6 | ant               | 2014-09-10 | back yard        |
|  7 | ant               | 2014-09-10 | back yard        |
|  8 | termite           | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
8 rows in set (0.00 sec)

mysql> delete from insect where id in(2, 6);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from insect order by id;
+----+-------------------+------------+------------------+
| id | name              | date       | origin           |
+----+-------------------+------------+------------------+
|  1 | housefly          | 2014-09-10 | kitchen          |
|  3 | grasshopper       | 2014-09-10 | front yard       |
|  4 | stink bug         | 2014-09-10 | front yard       |
|  5 | cabbage butterfly | 2014-09-10 | garden           |
|  7 | ant               | 2014-09-10 | back yard        |
|  8 | termite           | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
6 rows in set (0.00 sec)

mysql> delete from insect where id in(8);
Query OK, 1 row affected (0.01 sec)

mysql> select * from insect order by id;
+----+-------------------+------------+------------+
| id | name              | date       | origin     |
+----+-------------------+------------+------------+
|  1 | housefly          | 2014-09-10 | kitchen    |
|  3 | grasshopper       | 2014-09-10 | front yard |
|  4 | stink bug         | 2014-09-10 | front yard |
|  5 | cabbage butterfly | 2014-09-10 | garden     |
|  7 | ant               | 2014-09-10 | back yard  |
+----+-------------------+------------+------------+
5 rows in set (0.00 sec)

mysql> select engine from information_schema.tables
    -> where table_schema='cookbook' and table_name='insect';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.01 sec)

mysql> alter table insect engine = MyISAM;
Query OK, 5 rows affected (0.12 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select engine from information_schema.tables
    -> where table_schema='cookbook' and table_name='insect';
+--------+
| ENGINE |
+--------+
| MyISAM |
+--------+
1 row in set (0.00 sec)  

2025年6月29日星期日
11.4 查询序列值

mysql> select * from  insect order by id;
+----+-------------------+------------+------------+
| id | name              | date       | origin     |
+----+-------------------+------------+------------+
|  1 | housefly          | 2014-09-10 | kitchen    |
|  3 | grasshopper       | 2014-09-10 | front yard |
|  4 | stink bug         | 2014-09-10 | front yard |
|  5 | cabbage butterfly | 2014-09-10 | garden     |
|  7 | ant               | 2014-09-10 | back yard  |
+----+-------------------+------------+------------+
5 rows in set (0.01 sec)

mysql> delete from insect where id in (2, 6, 8);
Query OK, 0 rows affected (0.00 sec)

mysql> alter table tbl_name engine = MyISAM;
Query OK, 6 rows affected (0.36 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select engine from information_schema.tables
    -> where table_schema = 'cookbook' and table_name = 'insect';
+--------+
| ENGINE |
+--------+
| MyISAM |
+--------+
1 row in set (0.00 sec)

想清除一张表的所有信息
Truncate table
Truncate table tbl_name

mysql> select max(id) from insect;
+---------+
| max(id) |
+---------+
|       7 |
+---------+
1 row in set (0.00 sec)

mysql> insert into insect (name, date, origin)
    -> values('cricket', '2006-09-11', 'basement');
Query OK, 1 row affected (0.01 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                9 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into insect (name, date, origin)
    -> values('month', '2006-09-14', 'windowsill');
Query OK, 1 row affected (0.01 sec)

mysql> select * from insect where id = last_insert_id();
+----+-------+------------+------------+
| id | name  | date       | origin     |
+----+-------+------------+------------+
| 10 | month | 2006-09-14 | windowsill |
+----+-------+------------+------------+
1 row in set (0.00 sec)

Notepad++  insert.pl  
 
use strict;
use warnings;
use DBI;

# Connect to your database
my $dbh = DBI->connect("DBI:mysql:database=cookbook;host=localhost", 
                      "cbuser", "cbpass") 
          or die "Could not connect to database: $DBI::errstr";

# Execute your insert
$dbh->do("insert into insect (name, date, origin) values('moth', '2006-09-14','windowsill')");

# Get the insert ID
my $seq = $dbh->{mysql_insertid};  # or $dbh->last_insert_id(...) depending on your driver

print "Inserted record with ID: $seq\n";

# Disconnect
$dbh->disconnect;

C:\Users\lenovo>perl insert.pl
Inserted record with ID: 11

C:\Users\lenovo>mysql -u cbuser -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 185
Server version: 8.0.40 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use cookbook;
Database changed
mysql> select * from insect;
+----+-------------------+------------+------------+
| id | name              | date       | origin     |
+----+-------------------+------------+------------+
|  1 | housefly          | 2014-09-10 | kitchen    |
|  3 | grasshopper       | 2014-09-10 | front yard |
|  4 | stink bug         | 2014-09-10 | front yard |
|  5 | cabbage butterfly | 2014-09-10 | garden     |
|  7 | ant               | 2014-09-10 | back yard  |
|  9 | cricket           | 2006-09-11 | basement   |
| 10 | month             | 2006-09-14 | windowsill |
| 11 | moth              | 2006-09-14 | windowsill |
+----+-------------------+------------+------------+
8 rows in set (0.00 sec)

# Execute your insert  第一种方法
#$dbh->do("insert into insect (name, date, origin) values('moth', '2006-09-14','windowsill')");
替换成以下方法

#第二种方法
my $sth = $dbh->prepare("insert into insect (name, date, origin) values('moth2', '2006-09-14','windowsill')");
$sth->execute();
C:\Users\lenovo>perl insert.pl
Inserted record with ID: 12

import mysql.connector
from mysql.connector import Error
from mysql.connector import constants

try:
    conn = mysql.connector.connect(
        host='localhost',
        database='cookbook',
        user='cbuser',
        password='cbpass',
        client_flags=[constants.ClientFlag.FOUND_ROWS]
    )
    
    if conn.is_connected():
        print('已成功连接到数据库')
        
        stmt = "insert into insect (name, date, origin) values('moth3', '2006-09-14', 'windowsill')"
        print("执行查询:", stmt)
        
        cursor = conn.cursor()
        cursor.execute(stmt)
        
        print(f"查询返回 {cursor.rowcount} 条记录")
except Error as e:
print(f'数据库操作错误: {e}')

已成功连接到数据库
执行查询: insert into insect (name, date, origin) values('moth3', '2006-09-14', 'windowsill')
查询返回 1 条记录

import mysql.connector
from mysql.connector import Error
from mysql.connector import constants

try:
    conn = mysql.connector.connect(
        host='localhost',
        database='cookbook',
        user='cbuser',
        password='cbpass',
        client_flags=[constants.ClientFlag.FOUND_ROWS]
    )
    
    if conn.is_connected():
        print('已成功连接到数据库')
        
        # 创建游标
        cursor = conn.cursor()
        
        # 执行插入操作（使用您要求的格式）
        cursor.execute("""
            insert into insect (name, date, origin)
            values('moth4', '2006-09-14', 'windowsill')
        """)
        
        # 获取最后插入的ID
        last_id = cursor.lastrowid
        print(f"最后插入的记录ID: {last_id}")
        
        # 提交事务
        conn.commit()
        
        print(f"影响的行数: {cursor.rowcount}")
        
except Error as e:
    print(f'数据库操作错误: {e}')
    # 发生错误时回滚
    if 'conn' in locals():
        conn.rollback()
    
finally:
    # 关闭游标和连接
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals() and conn.is_connected():
        conn.close()
        print('数据库连接已关闭')

已成功连接到数据库
最后插入的记录ID: 14
影响的行数: 1
数据库连接已关闭

mysql> select * from insect;
+----+-------------------+------------+------------+
| id | name              | date       | origin     |
+----+-------------------+------------+------------+
|  1 | housefly          | 2014-09-10 | kitchen    |
|  3 | grasshopper       | 2014-09-10 | front yard |
|  4 | stink bug         | 2014-09-10 | front yard |
|  5 | cabbage butterfly | 2014-09-10 | garden     |
|  7 | ant               | 2014-09-10 | back yard  |
|  9 | cricket           | 2006-09-11 | basement   |
| 10 | month             | 2006-09-14 | windowsill |
| 11 | moth              | 2006-09-14 | windowsill |
| 12 | moth2             | 2006-09-14 | windowsill |
| 13 | moth3             | 2006-09-14 | windowsill |
| 14 | moth4             | 2006-09-14 | windowsill |
+----+-------------------+------------+------------+
11 rows in set (0.00 sec)

```sql
在这里插入代码片

11.5 对一个已有的序列进行重新计数

mysql> alter table insect drop id;
Query OK, 11 rows affected (0.07 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> alter table insect
    -> add id int unsigned not null auto_increment first,
    -> add primary key(id);
Query OK, 11 rows affected (0.04 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from insect order by id;
+----+-------------------+------------+------------+
| id | name              | date       | origin     |
+----+-------------------+------------+------------+
|  1 | housefly          | 2014-09-10 | kitchen    |
|  2 | grasshopper       | 2014-09-10 | front yard |
|  3 | stink bug         | 2014-09-10 | front yard |
|  4 | cabbage butterfly | 2014-09-10 | garden     |
|  5 | ant               | 2014-09-10 | back yard  |
|  6 | cricket           | 2006-09-11 | basement   |
|  7 | month             | 2006-09-14 | windowsill |
|  8 | moth              | 2006-09-14 | windowsill |
|  9 | moth2             | 2006-09-14 | windowsill |
| 10 | moth3             | 2006-09-14 | windowsill |
| 11 | moth4             | 2006-09-14 | windowsill |
+----+-------------------+------------+------------+
11 rows in set (0.00 sec)

Mysql->alter table insert
Drop id, 
Add id int unsigned not null auto_increment first;