本文详细介绍了MySQL数据库中的主键概念，包括如何创建主键约束、自增主键和非空约束表。此外，还讲解了数据库事务的基础知识，如ACID特性，以及事务的管理与查询SQL语法。文章最后探讨了MySQL索引原理，强调了索引在查询优化中的重要作用，并给出了索引创建与管理的方法。
一、数据库主键
（一）、主键及自增
每一张表通常会有一个且只有一个主键，来表示每条数据的唯一性
特点：1、值不能重复 2、非空
创建主键约束表
格式：create table 表名称 (字段1 int primary key,字段2 int);
例:
mysql> create table ku(id int primary key,name int);
Query OK, 0 rows affected (0.02 sec)
mysql> desc ku;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)删除主键
mysql> alter table ku drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建主键加自增表
格式：create table 表名称 (字段1 int primary key auto_increment,字段2 int);
例：
mysql> create table ku1(di int primary key auto_increment,name int);
Query OK, 0 rows affected (0.00 sec)
mysql> desc ku1;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| di | int(11) | NO | PRI | NULL | auto_increment |
| name | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
删除主键：如果按照上次步骤删除会报错
#先删除自增加
mysql> alter table ku1 modify di int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#在删除主键
mysql> alter table ku1 drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc ku1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| di | int(11) | NO | | NULL | |
| name | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
创建非空约束表
格式：create table 表名称 (字段1 int primary key auto_increment,字段2 int not null);
例：
mysql> create table ku2(id int primary key auto_increment,name int not null);
Query OK, 0 rows affected (0.00 sec)
mysql> desc ku2;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
（二）、表字段的注释
1、创建表时候注释
格式：create table 表名称 (字段1 int comment '注释内容',字段1 int comment '注释内容');
例：
mysql> create table ku3(id int comment 'ID号',name int comment '名字');
Query OK, 0 rows affected (0.00 sec)
mysql> show create table ku3\G;
*************************** 1. row ***************************
Table: ku3
Create Table: CREATE TABLE `ku3` (
`id` int(11) DEFAULT NULL COMMENT 'ID号',
`name` int(11) DEFAULT NULL COMMENT '名字'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified2、在以创建表中修改注释
格式：alter table 表名称 modify 字段1 int comment '注释内容';
例：
mysql> alter table ku3 modify name int comment '用户名';
mysql> show create table ku3\G;
*************************** 1. row ***************************
Table: ku3
Create Table: CREATE TABLE `ku3` (
`id` int(11) DEFAULT NULL COMMENT 'ID号',
`name` int(11) DEFAULT NULL COMMENT '用户名'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
3、表的设计
在设计表字段的时候，如果某个字段有可能出现大量的重复(称为数据冗余)，需要把该字段单独提出创建一张新表，把可能重复的放到新表内，在原表只需要使用新表的id即可
二、数据库事务概述
数据库：关系型数据库（支持事务）
非关系型数据库（不支持）
事务（transaction）：保证成批操作要么完全执行，要么完全不执行，维护数据的完整性。简单来说就是要么成功要么不成功
一个事务中包含多条 SQL 语句，在 SQL 语句之间的关系：
1、事务可以是 N 条 SQL 语句（N >= 0）
2、不是所有数据库存储引擎都支持事务，InnoDB 存储引擎支持事务处理
（一）、数据库事务特性（ACID）
| 事务特性 | 作用 |
| 原子性（Atomic） | 事务的所有操作，要么全部完成，要么全部不完成，不会结束在某个中间环节 |
| 一致性（Consistency） | 事务开始之前和事务结束之后，数据库的完整性限制未被破坏 |
| 隔离性（Isolation） | 当多个事务并发访问数据库中的同一数据时，所表现出来的是相互关系 |
| 持久性（Durability） | 事务完成之后，所做的修改会进行持久化保存，不会丢失 |
| 事务并发不进行事务隔离 | |
| 脏读 | 事务 A 读到未提交事务 B 修改的数据，如果此时事务 B 中途执行失败回滚，那么此时事务 A 读取到的就是脏数据 |
| 不可重复读 | 同一个事务中，对同一份数据读取的结果不一致（针对某一个数据） |
| 幻读 | 同一个事务中，同一个查询多次返回的结果不一样（针对表中总行） |
| 区别 | |
| 脏读和不可重复读 | 脏读是事务读取了还未提交事务的更新数据。不可重复读是同一个事务中，几次读取的数据不同 |
| 不可重复读和幻读 | 都是在同一个事务中，前者是几次读取数据不同，后者是几次读取数据整体不同 |
| 隔离级别 | 作用 |
|---|---|
SERIALIZABLE（串行化） |
避免脏读、不可重复读、幻读 |
REPEATABLE-READ（可重复读） |
避免脏读、不可重复读 |
READ-COMMITTED（读已提交） |
避免脏读 |
READ-UNCOMMITTED（读未提交） |
无作用 |
| ▼ mysql支持上面4种隔离级别，默认是可重复读 | |
1、 查看 mysql事务级别
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)
2、临时修改事务级别
mysql> set global tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| SERIALIZABLE |
+-----------------------+
1 row in set (0.00 sec)
重启mysql服务在出查看
[root@C7-15 ~]# systemctl restart mysql
........
....
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)
3、修改my.conf配置文件，配置永久生效
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
[root@C7-15 ~]# vim /etc/my.cnf
[mysqld]
....
..
transaction-isolation=serializable #添加内容
保存
[root@C7-15 ~]# systemctl restart mysql
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| SERIALIZABLE |
+-----------------------+
1 row in set (0.00 sec)
（二）、MySQL 数据库管理事务
| 管理事务的三个命令 | |
| BEGIN | 开始事务，后面有多条数据库操作语句开始执行 |
| COMMIT | 开始提交一个事务，对应前面的 BEGIN 操作，将事务处理的结果保存到数据文件中 |
| ROLLBACK | 开始回滚一个事务，在 BEGIN 和 COMMIT 中间，将事务中的全部语句撤销，恢复到 BEGIN 之前的数据状态 |
| set autocommit = 0/1 | 禁用或开启自动提交，自动提交为退出 MySQL 连接程序时，或执行下一条 DML (数据操纵语言) 语句 |
管理事务
准备表
mysql> create database gs;
Query OK, 1 row affected (0.00 sec)
mysql> use gs;
Database changed
mysql> create table yg(职业 varchar(10),姓名 varchar(10),编号 int,学历 varchar(10),工资 int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into yg values("云计算",'小V',220,'初中',6000),("java工程师",'王小红',200,'高中',9080),("云计算",'小i',180,'本科',16000),('IT工程师','小乔',170,'大专',7600),("云计 算",'小光',100,'本科',15000);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> show table status where name="yg"\G
*************************** 1. row ***************************
Name: yg
Engine: MyISAM #我们的存储引擎不是innoDB
Version: 10
Row_format: Dynamic
Rows: 5
Avg_row_length: 39
Data_length: 196
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2021-12-07 20:50:25
Update_time: 2021-12-07 20:50:32
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> alter table yg engine=InnoDB; #我们修改成InnoDB存储引擎
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from yg;
+---------------+-----------+--------+--------+--------+
| 职业 | 姓名 | 编号 | 学历 | 工资 |
+---------------+-----------+--------+--------+--------+
| 云计算 | 小V | 220 | 初中 | 6000 |
| java工程师 | 王小红 | 200 | 高中 | 9080 |
| 云计算 | 小i | 180 | 本科 | 16000 |
| IT工程师 | 小乔 | 170 | 大专 | 7600 |
| 云计算 | 小光 | 100 | 本科 | 15000 |
+---------------+-----------+--------+--------+--------+
5 rows in set (0.00 sec)
mysql> begin; #开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into yg values('apache工程师','蒙牛',666,'高中',5555);
Query OK, 1 row affected (0.00 sec)
mysql> commit; #提交事务
Query OK, 0 rows affected (0.00 sec)
mysql> select * from yg;
+-----------------+-----------+--------+--------+--------+
| 职业 | 姓名 | 编号 | 学历 | 工资 |
+-----------------+-----------+--------+--------+--------+
| 云计算 | 小V | 220 | 初中 | 6000 |
| java工程师 | 王小红 | 200 | 高中 | 9080 |
| 云计算 | 小i | 180 | 本科 | 16000 |
| IT工程师 | 小乔 | 170 | 大专 | 7600 |
| 云计算 | 小光 | 100 | 本科 | 15000 |
| apache工程师 | 蒙牛 | 666 | 高中 | 5555 |
+-----------------+-----------+--------+--------+--------+
6 rows i
最低0.47元/天 解锁文章
3400
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
