当前位置：首页 > article >正文

MYSQL——多表查询、事务和索引

article 2024/11/23 20:49:36

概括

出现查询结果个数为笛卡尔积的原因是sql语句：

select * from tb_emp,tb_dept;

没有加上where tb_emp.dept_id = tb_dept.id;（where条件可以消除笛卡尔积）

select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

查询类型

连接查询：用于结合两个或多个表中的数据。
- 内连接：查询两个表（A和B）的交集部分数据，即两个表中都有的匹配数据。
- 外连接：包括左外连接和右外连接。
  - 左外连接：查询左表的所有数据，包括与右表的交集部分数据。
  - 右外连接：查询右表的所有数据，包括与左表的交集部分数据。
子查询

连接查询

内连接

隐式内连接

select 字段列表 from 表1, 表2 where 连接条件...;

显式内连接（[inner]关键字是可选的）

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

如：

select c.name,max(d.price)
from dish d,
     category c
where d.category_id = c.id
group by c.name;

外连接

左就是指sql语句的左边，右就是指sql语句的右边。

左外连接:

select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;

右外连接:

select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;

如：

select d.name, d.price, c.name
from dish d
         left join category c on d.category_id = c.id
where d.price between 10 and 50
  and d.status = 1;

子查询

介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

形式：

select * from t1 where column1=(select column1 from t2...);

子查询外部的语句可以是insert / update / delete / select的任何一个，最常见的是select。

如：

-- a. 查询'方东白'的入职时间  ->标量子查询
select entrydate from tb_emp where name = '方东白';

-- b. 查询在'方东白'入职之后的员工信息
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

分类

标量子查询：子查询返回的结果为单个值
列子查询：子查询返回的结果为一列
行子查询：子查询返回的结果为一行
表子查询：子查询返回的结果为多行多列

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式
常用的操作符：= <> > >= < <=

列子查询

子查询返回的结果是一列（可以是多行）
常用的操作符：in 、not in、or等

行子查询

子查询返回的结果是一行（可以是多列）。
常用的操作符：= 、<>、in、not in

select * from tb_emp where (entrydate, job) = (select entrydate, job from tb_emp where name = '韦一笑');

表子查询

子查询返回的结果是多行多列，常作为临时表。
常用的操作符：in

select e.*, d.name from (select * from tb_emp where entrydate > '2006-01-01') e, tb_dept d where e.dept_id = d.id;

事务

事务：一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

如：删除部门以及部门下的员工
-- 删除部门
delete from tb_dept where id = 1;
-- 删除部门下的员工
delete from tb_emp where dept_id = 1;

开启事务:start transaction; / begin;
提交事务:commit;
回滚事务:rollback;

START TRANSACTION;  

UPDATE accounts SET balance = balance - 100 WHERE user = 'Alice';  
UPDATE accounts SET balance = balance + 100 WHERE user = 'Bob';  

-- 如果更新操作成功  
COMMIT;  -- 提交事务  

-- 如果发生错误  
ROLLBACK;  -- 回滚事务，恢复到之前的状态

事务具有四大特性

在MySQL中，事务具有四大特性，通常被称为ACID特性，具体如下：

原子性 (Atomicity)：
事务中的所有操作要么全部成功，要么全都失败。如果事务中的部分操作失败，整个事务都会被回滚，数据库保持在事务开始之前的状态。这确保了事务的完整性。
一致性 (Consistency)：
事务必须使数据库从一种一致性状态转换到另一种一致性状态。即，所有的约束、规则和数据完整性都在事务执行前后得到保持，确保数据库的状态是有效的。
隔离性 (Isolation)：
事务的执行不应受到其他事务的干扰。不同事务之间的操作是互相隔离的，确保并发执行的事务不会影响彼此的结果。MySQL通过多种隔离级别（如读未提交、读已提交、可重复读和串行化）来控制事务之间的隔离程度。
持久性 (Durability)：
一旦一个事务提交，其结果是永久性的，并且即使系统崩溃，也不会丢失。数据在写入数据库记录后，必须保留在数据库中，确保不会因任何故障而丢失。