数据库开发——MySQL——primary key
四，primary key
关系型数据库中的一条记录中有若干个属性，若其中某一个属性组（注意是组）能唯一标识一条记录，该属性组就可以成为一个主键。
例如
学生表（学号，姓名，性别，班级） 其中每个学生的学号是唯一的，学号就是一个主键，这叫单列主键
课程表（课程编号，课程名，学分） 其中课程编号是唯一的，课程编号就是一个主键，这叫单列主键成绩
表（学号，课程号， 成绩）成绩表中唯一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以学号和课程号的属性组是一个主键，这叫多列主键
成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表
中的学号是学生表的主键，则称成绩表中的学号是学生表的外键。
同理成绩表中的课程号是课程表的外键 primary key关键字用于定义列为主键。
单列主键
在某一个字段后用primary key
create table t6(
id int primary key,
name varchar(20),
number char(10));
desc t6;
执行结果为：
mysql> create table t6( id int primary key, name varchar(20), number char(10));
Query OK, 0 rows affected (0.53 sec)
mysql> desc t6;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| number | char(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
在所有前端后单独定义主键
create table t7 （
id int，
name varchar （20)，
number char （10），
constraint pk_id primary key（id)); ＃创建主键并为此命名
desc t7;
执行结果为：
mysql> create table t7(
-> id int,
-> name varchar(20),
-> number varchar(20),
-> constraint pk_id primary key(id));
Query OK, 0 rows affected (0.68 sec)
mysql> desc t7;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| number | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
多列主键
mysql> create table t8(
-> student_number char(10),
-> class_number char(10),
-> achievement int not null default 0,
-> primary key(student_number, class_number));
Query OK, 0 rows affected (0.73 sec)
mysql> desc t8;
+----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| student_number | char(10) | NO | PRI | NULL | |
| class_number | char(10) | NO | PRI | NULL | |
| achievement | int(11) | NO | | 0 | |
+----------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t8 values
-> ("18023300", "186000", 100),
-> ("19205200", "192000", 90);
Query OK, 2 rows affected (0.18 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t8 values("18023300", "186000", 90);
ERROR 1062 (23000): Duplicate entry '18023300-186000' for key 'PRIMARY'
mysql> select * from t8;
+----------------+--------------+-------------+
| student_number | class_number | achievement |
+----------------+--------------+-------------+
| 18023300 | 186000 | 100 |
| 19205200 | 192000 | 90 |
+----------------+--------------+-------------+
2 rows in set (0.00 sec)
auto_increment
我们每次往表中插入数据的时候都得记住id，这样非常考验脑力，有的时候我们可能记得不是很清楚，那就容易报错，使用auto_increment约束字段会自动增长，被约束的字段必须同时被key约束。
create table t9(
id int primary key auto_increment,
name varchar(20),
sex enum("male", "female"));
desc t9;
insert into t9(name) values("Alex"),("Coco");
select * from t9;
insert into t9 values(5, "BeiBei", "female");
select * from t9;
delete from t9;
select * from t9;
insert into t9(name, sex) values("Alex", "male"),("Coco", "female");
select * from t9;
truncate t9;
insert into t9(name, sex) values("Alex", "male"),("Coco", "female");
select * from t9;
执行结果为：
mysql> create table t9(
-> id int primary key auto_increment, # 定义自增长字段
-> name varchar(20),
-> sex enum("male", "female"));
Query OK, 0 rows affected (1.60 sec)
mysql> desc t9;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)
mysql> insert into t9(name) values("Alex"),("Coco");
Query OK, 2 rows affected (0.31 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t9; # 不指定id，则自动增长
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | Alex | NULL |
| 2 | Coco | NULL |
+----+------+------+
2 rows in set (0.00 sec)
mysql> insert into t9 values(5, "BeiBei", "female"); # 也可指指定id
Query OK, 1 row affected (0.23 sec)
mysql> select * from t9;
+----+--------+--------+
| id | name | sex |
+----+--------+--------+
| 1 | Alex | NULL |
| 2 | Coco | NULL |
| 5 | BeiBei | female |
+----+--------+--------+
3 rows in set (0.00 sec)
mysql> delete from t9;
Query OK, 3 rows affected (0.52 sec)
mysql> select * from t9;
Empty set (0.00 sec)
mysql> insert into t9(name, sex) values("Alex", "male"),("Coco", "female");
Query OK, 2 rows affected (0.18 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t9; # 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 6 | Alex | male |
| 7 | Coco | female |
+----+------+--------+
2 rows in set (0.00 sec)
mysql> truncate t9; # 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
Query OK, 0 rows affected (0.87 sec)
mysql> insert into t9(name, sex) values("Alex", "male"),("Coco", "female");
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t9;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | Alex | male |
| 2 | Coco | female |
+----+------+--------+
2 rows in set (0.00 sec)
创建完表后可以修改自增细分的初始值
create table t10(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male');
alter table t10 auto_increment = 5; # 修改步长
show create table t10;
insert into t10(name) values("Alex");
select * from t10;
show create table t10;
执行结果为：
mysql> create table t10(
-> id int primary key auto_increment,
-> name varchar(20),
-> sex enum('male','female') default 'male');
Query OK, 0 rows affected (0.72 sec)
mysql> alter table t10 auto_increment = 5;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t10;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t10 | CREATE TABLE `t10` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`sex` enum('male','female') DEFAULT 'male',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into t10(name) values("Alex");
Query OK, 1 row affected (0.24 sec)
mysql> select * from t10;
+----+------+------+
| id | name | sex |
+----+------+------+
| 5 | Alex | male |
+----+------+------+
1 row in set (0.00 sec)
mysql> show create table t10;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t10 | CREATE TABLE `t10` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`sex` enum('male','female') DEFAULT 'male',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看MySQL的步长：
show session variables like "auto_inc%";
执行结果为：
mysql> show session variables like "auto_inc%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 | # auto_increment_increment 表示步长
| auto_increment_offset | 1 | # auto_increment_offset 表示其实偏移量
+--------------------------+-------+
2 rows in set, 1 warning (0.15 sec)
可以通过下列命令设置步长：
set session auth_increment_increment=2 #修改会话级别的步长
set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）
设置全局的起始偏移量和步长需要重新启动MySQL。
如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略。
