mysql数据库基础

笑着的程序员

于 2023-07-16 22:58:34 发布

阅读量82

点赞数 1

分类专栏：数据库能力提升文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_45541762/article/details/131756320

版权

数据库能力提升专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、mysql事务：

mysql中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

1、mysql中默认是开启事务的。

默认事务开启的作用是什么？
当我们去执行一个sql语句的时候，效果会立即体现出来，且不能回滚。

mysql> select @@autocommit
    -> ;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

create table user(
id int primary key,
name varchar(20),
money int
);

insert into user values(1, 'a', 1000);
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.02 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

--设置mysql自动提交为false
mysql> set autocommit=0;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into user values(2, 'b', 1000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
mysql> rollback; --可以生效
Query OK, 0 rows affected (0.07 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

--上面的操作，关闭了mysql的自动提交（commit）

--自动提交？@@autocommit=1
--手动提交？commit
--事务回滚？rollback
--如果这个时候转账：
update user set money=money-100 where name='a';
update user set money=money+100 where name='b';
mysql> update user set money=money-100 where name='a';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money=money+100 where name='b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
begin;
--或者
start transaction;
--都可以手动开启一个事务。

--事务开启之后，一旦commit提交，就不可以回滚（也就是当前这个事务在提交的时候就结束了）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-100 where name='a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money=money+100 where name='b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   800 |
|  2 | b    |  1200 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> rollback;--回滚生效
Query OK, 0 rows affected (0.07 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

--事务的四大特征：
A:原子性：事务是最小的单位，不可再分割
C 一致性：事务要求，同一事务中的sql语句，必须保证同时成功或者同时失败。
I 隔离性：事务1和事务2之间是具有隔离性的。
D 持久性：事务一旦结束（commit，rollback），就不可以返回。

--事务开启：
1、修改默认提交 set autocommit=0;
2、begin;
3、start transaction;
--事务手动提交：
commit;
--事务手动回滚
rollback;
--事务的隔离性
1、read uncommitted; --读未提交的
--如果有实物a和b
--a事务对数据进行操作，在操作的过程中，事务没有被提交，但是b可以看见a的操作结果。
2、read commited; --读已提交的
3、repeatable read; --可以重复读
4、seralizable;  --串行化

2、脏读

1、read uncommitted; --读未提交的
--脏读：一个事务读到了另外一个事务没有提交的数据，就叫做脏读。
--实际开发是不允许脏读出现的。

bank数据库user表
insert into user values(3,'小明',1000);
insert into user values(4,'淘宝店',1000);
mysql> insert into user values(3,'小明',1000);
Query OK, 1 row affected (0.13 sec)

mysql> insert into user values(4,'淘宝店',1000);
Query OK, 1 row affected (0.12 sec)

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+
--如何查看数据库的隔离级别？
mysql 8.0;
--系统级别
select @@global.transaction_isolation;
--会话级别
select @@transaction_isolation;
mysql> select @@global.transaction_isolation;
+--------------------------------+ --默认隔离级别
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
--如何修改隔离级别？
set global transaction isolation level read uncommited;

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

--转账：小明在淘宝店买鞋子：800块钱
小明->成都 ATM
淘宝店->广州 ATM
start transaction;
update user set money=money-800 where name='小明';
update user set money=money+800 where name='淘宝店';
--如果两个不同的地方，都在进行操作，如果事务a开启之后，它的数据可以被其他事务读取到。
--这样就会出现脏读
--脏读：一个事务读到了另外一个事务没有提交的数据，就叫做脏读。
--实际开发是不允许脏读出现的。

3、不可重复读

2、read commited; --读已提交的
set global transaction isolation level read committed;
select @@global.transaction_isolation;

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+
小张：银行会计
start transaction;
select * from user;
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+
小张出去上厕所。。。抽烟
小王：
start transaction;
insert into user values(5, 'c', 100);
commit;
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(5, 'c', 100);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+
小张上完厕所回来：
mysql> select avg(money) from user;
+------------+
| avg(money) |
+------------+
|   820.0000 |
+------------+
--虽然我只能读到另外一个事务提交的数据，还是会出现问题，
--就是读取同一个表的数据，发现前后不一致。
--不可重复读现象：read committed

4、幻读

3、repeatable read; --可以重复读
set global transaction isolation level repeatable read;
select @@global.transaction_isolation;
mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

--张全蛋-成都
start transaction;

--王尼玛-北京
start transaction;

--张全蛋-成都
insert into user values(6, 'd', 1000);

mysql> insert into user values(6, 'd', 1000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+

--王尼玛-北京
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+
insert into user values(6, 'd', 1000);
mysql> insert into user values(6, 'd', 1000);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
--上面这种现象就是幻读。
--事务a和事务b同时操作一张表，事务a提交的数据，不能被事务b读到，就可以造成幻读。

5、串行化

4、seralizable;  --串行化
set global transaction isolation level seralizable;
select @@global.transaction_isolation;
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
--张全蛋-成都
start transaction;

--王尼玛-北京
start transaction;

--张全蛋-成都
mysql> insert into user values(7, '赵铁柱', 1000);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  7 | 赵铁柱    |  1000 |
+----+-----------+-------+

--王尼玛-北京
插入之前查询：
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  7 | 赵铁柱    |  1000 |
+----+-----------+-------+
--张全蛋-成都
insert into user values(8, '王小花', 1000);
卡住了
--当user表被另外一个事务操作的时候，其他事务里面的写操作，是不可以进行的
--进行排队状态（串行化），直到王尼玛那边事务结束后，张全蛋这个写入操作才会执行。
--在没有等待超时的情况下。

--王尼玛-北京
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

--张全蛋-成都
mysql> insert into user values(8, '王小花', 1000);
Query OK, 1 row affected (10.31 sec)

--串行化问题是，性能特差！
READ-UNCOMMITTED>READ-COMMITTED>REPEATABLE-READ>SERIALIZABLE;
--隔离级别越高性能越差
--mysql默认隔离级别是REPEATABLE-READ

二、sql的四种连接方式

内连接
inner join 或者 join

外连接
1、左连接left join或者left outer join
2、右连接right join或者right outer join
3、完全外连接full join

--创建两个表：
create database testJoin;
--person表
id,
name,
cardId
create table person(
id int,
name varchar(20),
cardId int
);

--card表
id,
name

create table card(
id int,
name varchar(20)
);

--插入数据
insert into card values(1,'饭卡');
insert into card values(2,'建行卡');
insert into card values(3,'农行卡');
insert into card values(4,'工商卡');
insert into card values(5,'邮政卡');


insert into person values(1,'张三',1);
insert into person values(2,'李四',3);
insert into person values(3,'王五',6);

1、内连接

--并没有创建外键
--inner join查询
--内联查询，其实就是两张表中的数据，通过某个字段相对，查询出相关记录数据
select * from person join card on person.cardId=card.id;
mysql> select * from person join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+
select * from person inner join card on person.cardId=card.id;
mysql> select * from person inner join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+

2、左外连接

--2、left join（左外连接）
select * from person left join card on person.cardId=card.id;
mysql> select * from person left join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+
mysql> select * from person left outer join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+
--左外连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来
--如果没有，就会补NULL

3、右外连接

--3、right join（右外连接）
select * from person right join card on person.cardId=card.id;
mysql> select * from person right join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
--右外连接，会把右边表里面的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来
--如果没有，就会补NULL

4、全外连接

--4、full join（全外连接）mysql暂时不支持
select * from person full join card on person.carId=card.id;

select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.carId=card.id;

笑着的程序员

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql数据库基础

mysql中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。当我们去执行一个sql语句的时候，效果会立即体现出来，且不能回滚。2、右连接right join或者right outer join。1、左连接left join或者left outer join。inner join 或者 join。3、完全外连接full join。默认事务开启的作用是什么？
复制链接

扫一扫