1. 关于MySQL数据库连接方式介绍【笛卡尔积连接、内连接、外连接-左连接、外连接-右连接 】
笛卡尔积连接:
SELECT article.id AS id, article.title AS title, article.state as state, article_cate.title as cate
FROM article,article_cate WHERE article.cate_id = article_cate.id ORDER BY id ;
注:order by id 是指代,搜索后以id为标准排序。
内连接:
SELECT * FROM lesson INNER JOIN lesson_student ON lesson.id = lesson_student.lesson_id AND lesson_student.student_id =1
#### from 条件后面的跟的+ 表名称 inner josn + 表名称 on + 查询条件 出来的图就是对应顺序不一致
SELECT * FROM lesson_student INNER JOIN lesson ON lesson.id = lesson_student.lesson_id AND lesson_student.student_id =1
注: 内连接 inner join 关联表名称 on 表连接条件 ------通过 ORDER BY 排序字段名称 id ;
外连接 (左连接)
SELECT * FROM lesson LEFT JOIN lesson_student ON lesson.id = lesson_student.lesson_id AND lesson_student.student_id =1
执行结果:
外连接(右连接)
SELECT * FROM lesson RIGHT JOIN lesson_student ON lesson.id = lesson_student.lesson_id AND lesson_student.student_id =1
执行结果:
注:使用左右链接,具体表现 以那一个表作为参照对象,那么数据的呈现方式也不一样。具体看图……
2.MySQL 索引
索引的建立,对于mysql的高效运行是很重要的,索引可以大大提高 MySQL 的检索速度。
其中MySQL的常见的索引类型有 普通索引、唯一索引、全文索引、空间索引Sqatial
以50万条数据为例,进行演示操作:
insert into users(`username`) select username from users
步骤一、创建一个有50万条数据的数据库;
SET NAMES utf8mb4;
set names utfmb4;设置字符集
## 第一个语句SET NAMES utf8mb4;用于设置会话字符集为utf8mb4。这是为了确保数据的正确存储和检索,特别是对于包含表情符号等特殊字符的文本数据。
SET FOREIGN_KEY_CHECKS = 0;
set foreign_key_checks = 1; 默认值为1,同时表示打开外键约束检查。
##第二个语句SET FOREIGN_KEY_CHECKS = 0;用于关闭外键约束检查。在插入或更新数据时,外键约束会检查关联表中是否存在匹配的键。关闭这个检查可以避免在插入或更新数据时引发外键约束错误。注意,在完成插入或更新操作后,应该重新打开外键约束检查,即将SET FOREIGN_KEY_CHECKS设置回默认值1。
备注: 这两语句应该在执行表创建语句之前执行
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`balance` decimal(10, 2) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
### collate 排列规律 MySQL 8.0版本上以上用 上面的语句;如果是8.0版本以前的本地就用下满语句 创建表
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`balance` decimal(10, 2) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', 0.00);
INSERT INTO `user` VALUES (2, '李四', 200.00);
INSERT INTO `user` VALUES (5, 'wangwu', 100.00);
关于如何创建 50万条数据;
### 如何插入50万条数据步骤,如下:
(-- 查看mysql是否允许创建函数:)
1. SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
show variables like 'log_bin_trust_function_createors'
(-- 查看mysql是否允许创建函数:)
1. SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
(-- 命令开启:允许创建函数设置:(global-所有session都生效)
2. SET GLOBAL log_bin_trust_function_creators=1;
set global log_bin_trust_function_creators=1;
(-- 命令开启:允许创建函数设置:(global-所有session都生效)
2. SET GLOBAL log_bin_trust_function_creators=1;
-- 检查存储过程是否存在
SHOW PROCEDURE STATUS WHERE name = 'insert_user';
-- 如果存在,删除存储过程
DROP PROCEDURE IF EXISTS `insert_user`;
DELIMITER $$
CREATE PROCEDURE insert_user(START INT, max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO `user` ( id , username, balance ) VALUES ((START+i) ,rand_string(6), rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
-- 插入50万条数据
CALL insert_user(100000,500000);
具体详情
下面是代码段的详细解读:
```sql
DELIMITER $$
```
`DELIMITER` 语句用于更改 MySQL 的语句分隔符,将其设置为 `$$`。这是为了避免与存储过程中的分号冲突。
```sql
CREATE PROCEDURE insert_emp(START INT, max_num INT)
```
`CREATE PROCEDURE` 语句用于创建一个存储过程,名为 `insert_emp`。它接受两个参数:`START` 和 `max_num`,都是整数类型。
```sql
BEGIN
```
`BEGIN` 标记了存储过程的开始。
```sql
DECLARE i INT DEFAULT 0;
```
`DECLARE` 语句声明了一个名为 `i` 的局部变量,并初始化为0。该变量用于存储循环迭代的计数器。
```sql
SET autocommit = 0;
```
`SET` 语句将 `autocommit` 的值设置为0。这可以禁用自动提交事务,使得事务会在显式提交之前保持打开状态。
```sql
REPEAT
SET i = i + 1;
INSERT INTO `user` (id, username, balance) VALUES ((START + i), rand_string(6), rand_num(1, 10000));
UNTIL i = max_num END REPEAT;
```
这是一个重复循环块(REPEAT...UNTIL),在每次循环中,`i` 的值递增,并将根据指定的规则生成的数据插入到 `user` 表中。循环迭代直到 `i` 的值等于 `max_num`。
```sql
COMMIT;
```
`COMMIT` 语句用于提交当前事务,将之前所有的插入操作持久化到数据库。
```sql
END$$
```
`END` 标记了存储过程的结束。
综上所述,该存储过程的功能是将从 `START` 开始递增的编号、随机生成的用户名和随机生成的余额插入到 `user` 表中,直到插入的记录数达到 `max_num`,然后提交事务。同时,为了确保事务的一致性,禁用了自动提交和手动提交事务。
请注意,在执行这段代码之前,需要保证 `rand_string` 和 `rand_num` 函数的存在,并在代码上下文中正确使用该存储过程。
在其他详情的操作可自行操作查看文档,进行实验。
3. Mysql 事务:
1.创建user表作为演示
## 由于版本不一样创建数据库执行语句有一点差异化:
## collate = utf8mb4_0900_ai_ci 可能需要改成:utf8mb4_general_ci
## 也就是说排序规则改成 utf8mb4_general_ci 是因为本地的navicat for mysql 工具导致的
## 作者本地使用的工具是 v11.0.10 -企业版本
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL
DEFAULT NULL,
`balance` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '熊大', 100.00);
INSERT INTO `user` VALUES (2, '光头强', 100.00);
SET FOREIGN_KEY_CHECKS = 1;
---------------------------------分界线-------------------------------------------
## 高级版本执行以下语句
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(0) NOT NULL AUTO_INCREMENT, `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL
DEFAULT NULL, `balance` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '熊大', 100.00);
INSERT INTO `user` VALUES (2, '光头强', 100.00);
SET FOREIGN_KEY_CHECKS = 1;
2.案例:熊大账户转账转出 100 元到光头强的账户
如何实现思路:
1、熊大 的账户减去 100 元
UPDATE user set balance = balance-100 WHERE id=1
2、光头强 的账户增加 100 元
UPDATE user set balance = balance+100 WHERE id=2
UPDATE user set balance = balance-100 WHERE id=1
UPDATE user set balance = balance+100 WHERE id=2
反馈结果:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE user set balance = balance+100 WHERE id=2' at line 3
反馈的结果,分析问题:
1. 如果是单句执行sql语句 updata数据更新肯定是可行并且无明显报错信息;
2. 两句sql 更新 updata 不同id 值下的其他属性值,这个时候反馈的结果就是err的mysql语法错误然后告诉你报错的点在哪里几行。
3. 引入使用事务的概念,或者说使用事务的时机
事务的主要概念:
BEGIN (begin) 开始一个事务
ROLLBACK(rollback) 事务回滚
COMMIT (commit) 事务确认
实际操作的使用时机:
引入上一个问题的深入思考
1. 熊大 的账户减少100
2. 光头强的账户增加100
那么是不是相对的事务使用就是这样:
begin;
update user set balance = balance-100 where id=1;
rollback; 只有执行报错时候才可以使用 回滚操作;
![](https://i-blog.csdnimg.cn/blog_migrate/37b3380dcb8002a6fdac2318110acfb5.png)
![](https://i-blog.csdnimg.cn/blog_migrate/c717d7b3c054b23a2a2e1b1b63307b01.png)
begin;
update user set balance = balance-100 where id=1;
update user set balance = balance+100 where id=2;
commit;
以上就是对应的事务,
4. mysql 锁
1.添加读锁
使用场景:
对表user添加读锁:lock table user read;
解除user的读锁: unlock tables;
![](https://i-blog.csdnimg.cn/blog_migrate/50ad9f953a46393949e556a0aacd3777.png)
2. 添加 写锁
使用场景:
lock table user write;
unlock tables;
补充:
行级锁:每次操作锁住对应的行数据。
行级锁,每次操作锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。对于行级锁,主要分为以下三类:
- 行锁〈Record Lock)∶锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
- 间隙锁(Gap Lock):锁定索引记录间腺(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在R隔离级别下都支持。
- 临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持。
InnoDB实现了以下两种类型的行锁:
- 共享锁(S)∶允许一个事务去读一行,阻止其他事务获得相同数据集的排它锁。
- 排他锁(X)∶允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁。 公
行锁中,共享锁与共享锁不互斥,共享锁与排它锁之间互斥;排它锁与排它锁之间互斥
InnoDB引擎中,执行SQL时的加行锁情况:
SQL | 行锁类型 | 说明 |
---|---|---|
INSERT ... | 排他锁 | 自动加锁 |
UPDATE ... | 排他锁 | 自动加锁 |
DELETE ... | 排他锁 | 自动加锁 |
SELECT (正常) | 不加任何锁 | |
SELECT ... LOCK IN SHARE MODE | 共享锁 | 需要手动在SELECT之后加LOCK IN SHARE MODE |
SELECT ...FOR UPDATE | 排他锁 | 需要手动在SELECT之后加FOR UPDATE |
默认情况下,InnoDB在REPEATABLE READ(RR)事务隔离级别运行,InnoDB使用next-key(临键)锁进行搜索和索引扫描,以防止幻读。
- 针对唯一索引进行检索时,对已存在的记录进行等值匹配时,将会自动优化为行锁。
- InnoDB的行锁是针对于索引加的锁,不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,此时就会升级为表锁。
有关间隙锁锁临键锁的说明(防止幻读):
- 索引上的等值查询(唯一索引),给不存在的记录加锁时,优化为间隙锁。
- 索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询需求时,next-key lock退化为间隙锁。
- 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。
具体说明:
-- 首先,准备一张表,id为主键,id分别为 1,3,10,15
mysql> select * from user_innodb;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 1 | 张三 | 1 |
| 3 | 李四 | 4 |
| 10 | 王五 | 9 |
| 15 | 赵六 | 15 |
+----+----------+------+
4 rows in set (0.00 sec)
-- 对于1、索引上的等值查询(唯一索引),给不存在的记录加锁时,优化为间隙锁。
-- 说明,当开启事务后,如果 此时执行 下面update语句
update user_innodb set username = '王五' where id = 7
-- 因为不存在 id = 7 的数据,这是就会给 id = 2 到 id = 9(不包含 4和 10)之间加间隙锁,此时其他客户端想insert (或 update 或 delete)id在2到9之间的数据就会进入阻塞状态。
-- 对于2、索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询需求时,next-key lock退化为间隙锁。
-- 首先创建一个 非唯一的普通索引 比如 age
mysql> create index idx_user_age on user_innodb(age);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看索引
mysql> show index from user_innodb;
+-------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| user_innodb | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | YES |
| user_innodb | 1 | idx_user_age | 1 | age | A | 4 | NULL | NULL | YES | BTREE | | | YES |
+-------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.09 sec)
-- 首先开启事务
-- 当执行下列语句时,若执行的是 select * from user_innodb where age = 4; 不会加锁
select * from user_innodb where age = 4 lock in share mode;
-- 查看锁的情况,与表锁中查看意向锁的SQL一致
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+--------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+--------------+-----------+-----------+-----------+
| heima_learn | user_innodb | NULL | TABLE | IS | NULL |
| heima_learn | user_innodb | idx_user_age | RECORD | S | 4, 3 |
| heima_learn | user_innodb | PRIMARY | RECORD | S | 3 |
| heima_learn | user_innodb | idx_user_age | RECORD | S,GAP | 9, 10 |
+---------------+-------------+--------------+-----------+-----------+-----------+
-- 因为 age 是一个普通索引,可能会出现重复的数据,所以说此时会加 3 个共享锁
-- 第一个 | S | 4, 3 |
-- 这个index_name =idx_user_age,lock_mode中没有GAP (这里的 4, 3 指的是主键 age 的值为 4,id的值为 3) 的临键锁,表示数据 id = 3 的临键锁,值id = 2,3
-- 第二个 | S | 3 |
-- 第二个的 index_name 为 PRIMARY,表示 给id = 3 (age = 4)的数据加了行锁
-- 第三个 | S,GAP | 9, 10 |
-- 这里(9 代表 age = 9, 10 代表 id = 10) index_name = idx_user_age,在 lock_mode 存在 GAP,表示是一个间隙锁,锁住的数据为 id = 4 到 id = 9。
-- 这么做的操作就是为了防止出现 幻读
-- 对于3、索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。
-- 重新开启事务
-- 执行下列语句
select * from user_innodb where id >= 10 lock in share mode;
-- 查看锁的情况
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+------------------------+
| heima_learn | user_innodb | NULL | TABLE | IS | NULL |
| heima_learn | user_innodb | PRIMARY | RECORD | S | 10 |
| heima_learn | user_innodb | PRIMARY | RECORD | S | supremum pseudo-record |
| heima_learn | user_innodb | PRIMARY | RECORD | S | 15 |
+---------------+-------------+------------+-----------+-----------+------------------------+
4 rows in set (0.00 sec)
-- 可以看出加了,id = 10的行锁,id = 15及id > 10 到 id = 15的临键锁,id = 15 及id > 15 的临键锁
-- 当继续执行下面SQL时
select * from user_innodb where id >= 9 lock in share mode;
-- 查看锁的情况
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+------------------------+
| heima_learn | user_innodb | NULL | TABLE | IS | NULL |
| heima_learn | user_innodb | PRIMARY | RECORD | S | 10 |
| heima_learn | user_innodb | PRIMARY | RECORD | S | supremum pseudo-record |
| heima_learn | user_innodb | PRIMARY | RECORD | S | 10 |
| heima_learn | user_innodb | PRIMARY | RECORD | S | 15 |
+---------------+-------------+------------+-----------+-----------+------------------------+
--可以看出,此时的锁的情况是 id = 10的行锁,id = 10 到 id > 10 的临键锁,id = 15 的行锁
![](https://i-blog.csdnimg.cn/blog_migrate/82802f95f113eb54c391f3d312f846c8.png)