主键 primary key

主键是一种用于唯一标识表中每个记录的列或列组合。主键可以由单个列或多个列组成，并保证了每行数据的唯一性。
在MySQL中，主键必须满足以下条件：
唯一性：每行数据的主键值必须唯一，不能重复。
非空性：主键值不能为空，即每行数据的主键列不能包含NULL值。
无法更改：主键值在插入后不能更改，这确保了每行数据的唯一性。
通常情况下，主键是自增长的整数类型（如INT或BIGINT），但也可以使用其他数据类型，例如字符串、日期等等。
使用主键可以方便地对表中记录进行快速查找和更新操作，并且提高了数据查询和处理的效率。
因此，在设计数据库表结构时，应该为每个表选择一个合适的主键并加以定义。
#### 创建主键
```asp
create table db2.t3(name char(10) primary key,age int);		#创建表指定主键，方法1
```
```asp
create table db2.t3(name char(10),age int, primary key(name));	#创建表指定主键，方法2
```
```asp
desc db2.t3;	#查看
```
```asp
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | NO   | PRI | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
```
#### 添加主键
主键值唯一，不允许重复
```asp
desc stuinfo;
```
```asp
+-------+-----------------------------------+------+-----+-----------+-------+
| Field | Type                              | Null | Key | Default   | Extra |
+-------+-----------------------------------+------+-----+-----------+-------+
| class | char(7)                           | YES  |     | nsd1905   |       |
| age   | tinyint(3) unsigned               | YES  |     | 19        |       |
| name  | varchar(15)                       | NO   |     |           |       |
| likes | set('eat','drink','game','happy') | NO   |     | eat,drink |       |
| sex   | enum('m','w')                     | NO   |     | m         |       |
+-------+-----------------------------------+------+-----+-----------+-------+
```
```asp
alter table stuinfo add primary key(name);	#添加主键
```
```asp
desc stuinfo;	#查看
```
```asp
+-------+-----------------------------------+------+-----+-----------+-------+
| Field | Type                              | Null | Key | Default   | Extra |
+-------+-----------------------------------+------+-----+-----------+-------+
| class | char(7)                           | YES  |     | nsd1905   |       |
| age   | tinyint(3) unsigned               | YES  |     | 19        |       |
| name  | varchar(15)                       | NO   | PRI |           |       |
| likes | set('eat','drink','game','happy') | NO   |     | eat,drink |       |
| sex   | enum('m','w')                     | NO   |     | m         |       |
+-------+-----------------------------------+------+-----+-----------+-------+
```
#### 创建复合主键
复合主键是指由多个列组合而成的主键
与单列主键不同的是，复合主键可以保证每行记录在多个列上的唯一性
复合范围内不允许出现重复
```asp
create table t5(
    -> class char(7),
    -> name char(10),
    -> money enum("no","yes"),
    -> primary key(class,name)
    -> );	#创建复合主键
```
```asp
desc t5;	#查看
```
```asp
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| class | char(7)          | NO   | PRI | NULL    |       |
| name  | char(10)         | NO   | PRI | NULL    |       |
| money | enum('no','yes') | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
```
```asp
insert into db2.t5 values("nsd1905","bob","yes"); 		#插入第一条数据
```
```asp
insert into db2.t5 values("nsd1905","bob","no");  		#插入第二条数据班级名字重复插入失败
```
```asp
ERROR 1062 (23000): Duplicate entry 'nsd1905-bob' for key 'PRIMARY'
```
```asp
mysql> insert into db2.t5 values("nsd1906","bob","no");	#班级不一样插入成功
```
```asp
insert into db2.t5 values("nsd1906","tom","no");   		#班级一样名字不一样插入成功
```
```asp
select * from db2.t5;	#查看
```
```asp
+---------+------+-------+
| class   | name | money |
+---------+------+-------+
| nsd1905 | bob  | yes   |
| nsd1906 | bob  | no    |
| nsd1906 | tom  | no    |
+---------+------+-------+
```
#### 删除/添加复合主键
```asp
alter table t5 drop primary key; 					#删除复合主键
```
```asp
alter table t5 add stu_num char(5) first;			#添加一个新字段
```
```asp
alter table t5 change stu_num id char(5);			#修改字段名
```
```asp
alter table t5 add primary key(id,class,name);   	#添加三个字段为复合主键
```
```asp
desc t5;								#查看
```
```asp
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | char(5)          | NO   | PRI | NULL    |       |
| class | char(7)          | NO   | PRI | NULL    |       |
| name  | char(10)         | NO   | PRI | NULL    |       |
| money | enum('no','yes') | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
```
```asp
insert into t5 values("001","nsd1905","bob","yes");		#插入数据
```
```asp
insert into t5 values("001","nsd1905","bob","no");		#插入数据复合主键重复无法写入
```
```asp
ERROR 1062 (23000): Duplicate entry '001-nsd1905-bob' for key 'PRIMARY'
```
```asp
insert into t5 values("002","nsd1905","bob","no");		#插入数据主键不重复可以写入
```
```asp
select * from t5;	#查看
```
```asp
+-----+---------+------+-------+
| id  | class   | name | money |
+-----+---------+------+-------+
| 001 | nsd1905 | bob  | yes   |
| 002 | nsd1905 | bob  | no    |
+-----+---------+------+-------+
```
#### 主键与auto_increment连用
自增长
```asp
create table db2.t6(
    -> id int primary key auto_increment,
    -> name char(10),
    -> age int
    -> );				#创建自增长字段并且指定主键
```
```asp
desc t6;				 #查看
```
```asp
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
| age   | int(11)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
```
```asp
insert into t6 (age,name) values("19","bob");			#写入
```
```asp
insert into t6 (age,name) values("19","tom");			#写入
```
```asp
insert into t6 (age,name) values("19","lucy");   		#写入
```
```asp
insert into t6 values(5,19,"lucy");					  #写入id自定义
```
```asp
insert into t6 (age,name) values("19","jenny");  		#写入是根据最大值自加1
```
```asp
select * from t6;	#查看，id值自增长
```
```asp
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | bob   |   19 |
|  2 | tom   |   19 |
|  3 | lucy  |   19 |
|  5 | lucy  |   18 |
|  6 | jenny |   19 |
+----+-------+------+
```
```asp
delete from t6;									#删除表中的记录
```
```asp
insert into t6 (age,name) values("19","jenny");	#再次写入自加数字会按照上次的最大值自加1
```
```asp
insert into t6 values(null,"done",19);		 	#指定null就是指定空自动赋值
```
```asp
select * from t6;								  #查看
```
```asp
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  7 | jenny |   19 |
|  8 | done  |   19 |
+----+-------+------+
```