板凳-------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: 0mysql> 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: 0mysql> 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_namemysql> 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 driverprint "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 - GPLCopyright (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 constantstry: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 constantstry: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')""")# 获取最后插入的IDlast_id = cursor.lastrowidprint(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: 0mysql> 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: 0mysql> 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;