数据库笔记2- 事物，多表查询，笛卡尔积，JOIN

本文链接：https://blog.csdn.net/qq_51778285/article/details/121576772

事物
事物的特点：
原子性（密不可分）
一致性（总体保持一致）
隔离性（高并发，加锁）
持久性（数据修改）

事物的隔离级别
读未提交：Read uncommittde 安全性最差，高并发可能出现数据问题，性能最好
读提交：Read committed Oracle默认的隔离级别
可重复读：Repeatable read MySQL默认的隔离级别，安全性较好，性能一般
串行化：Serializable 表级锁，读写都加锁，效率低下，安全性高，不能并发

在默认的情况下，MySQL每执行一条SQL语句，都是一个单独的事物，如果需要在一个事物中包含多条
SQL语句，那么需要手动开启事物和结束事物。
运行十条SQL语句创建了十个事物

开启事物：start transaction
结束事物：commit（提交事务）//提交并改变或者rollback（回滚事务）//返回最初状态

运行流程：
执行SQL语句之前，先执行start transaction 执行SQL语句，然后commit提交事物

start transaction
insert into xxx values(sssssssss);
这时插入的数据仅仅只是暂时的数据，没有提交之前别人查不到
commit;(提交语句)
rollback;(回滚)，没有任何效果，回到之前状态

事务处理：
MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
MySQL默认数据库的事务是开启的，执行SQL后自动提交。
MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。

六大约束
1.默认约束：default xx
create table x1(
id int default 5 );
2.主键约束：primary key
create table x2(
id int primary key auto_increment);
3.非空约束：not null
create table x3(
id int not null);
4.唯一约束：unique
create table x4(
id int unique);
5.外键约束： foreign key子表中的主键的值必须取自主表
主表没有的话，子表会报错，子表中有人在使用，主表没法删除和修改
6.检查约束： check

多表联合查询

先建表，需要的表如下

courses表 scores

students表

teachers表

emp表

dept表

笛卡尔积，join连接查询

#多表联合查询
#这个叫联合主键
#primary key (sno, cno)

# 多表联查
#多表联合查询方式1：笛卡尔积 直接查询两张表
#注意！！！！！！！！！！！！！！：这种方式会造成极大的资源浪费，一般不用

#练习：查询部门表和员工表的所有数据
SELECT * FROM dept,emp;  #直接查询两张表，逗号隔开
#添加查询条件，表名两张表的关系
SELECT*FROM dept,emp WHERE dept.deptno=emp.deptno；#使用表名.属性，找到两个都有的属性建立关系

#查询课程表和老师表的所有数据
SELECT teachers.*,courses.* FROM teachers,courses  #teachers.*查询teachers所有的属性
WHERE teachers.tno=courses.tno;

#查询在一区办公的员工
SELECT emp.ename,COUNT(1) FROM emp,dept 
WHERE emp.deptno=dept.deptno
 AND dept.loc='一区';
 
#查询在research部门工作的员工的信息         #这里采用混合查询，聚合函数，需要分组
SELECT emp.* FROM emp,dept
WHERE emp.deptno=dept.deptno
AND dept.dname='research'AND job='员工'
GROUP BY emp.deptno
ORDER BY emp.deptno;


#多表联合查询方式2：连接查询
#语法，多表间用join连接，用on来描述关系，where筛选
#练习：查询在一区办公的员工名称
SELECT emp.ename FROM emp JOIN dept   #两表之间需要进行join连接
ON emp.deptno=dept.deptno       #描述关系
WHERE dept.loc='一区';          #筛选条件

#查询课程表和老师表的所有数据
SELECT teachers.*,courses.* FROM
teachers JOIN courses
ON teachers.tno = courses.tno;

#查询research部门工作的员工的信息
SELECT emp.* FROM emp JOIN dept
ON emp.deptno=dept.deptno
WHERE dept.dname='research'AND job='员工';

#查询易天老师能讲的课程名称
SELECT courses.cname FROM teachers JOIN courses
ON teachers.tno=courses.tno
WHERE teachers.tname='易天'; 

#查询学员李军的总得分
SELECT sname a,SUM(degree) FROM scores JOIN students  #给sname起别名 a
ON scores.sno=students.sno
GROUP BY sname
HAVING a='李军';          #这里使用别名a

连接查询：内连接，外连接
内连接：inner join取交集
左外连接：left join 取左表的所有和右表满足的
右外连接：right join取右表的所有和左表满足的

inner join、left join、right join的区别？

INNER JOIN两边都对应有记录的才展示，其他去掉
LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充
RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充

#查询部门表和员工表的所有数据
SELECT emp.*,dept.* FROM emp INNER JOIN dept
ON emp.deptno=dept.deptno;

SELECT emp.*,dept.* FROM emp LEFT JOIN dept
ON emp.deptno=dept.deptno;

SELECT emp.*,dept.* FROM emp RIGHT JOIN dept
ON emp.deptno=dept.deptno;

右外连接的结果：出现了两行null

左外连接的结果和内连接的结果相同：

子查询 subquery

概念

子查询是指嵌入在其他select语句中的select语句也叫嵌套查询。

子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用

记录多时最好使用其它方式替代。

#多表联合查询方式3：子查询
#练习：查询accounting部门的员工信息
#先查部门编号：
select deptno from dept where dept,dname='accounting';
#再根据编号查信息
select emp.* from emp where deptno=1; #部门编号,嵌套查询用(写第一次查询语句)

SELECT emp.* FROM emp WHERE deptno=(
SELECT deptno FROM dept 
WHERE dept.dname='accounting'
);

#查询tony所在的部门名称
SELECT dept.dname FROM dept WHERE deptno=(   #根据编号查部门名称
SELECT deptno FROM emp WHERE ename='tony'    #查询出名字是tony的编号
);

#查询在二区办公的员工姓名
#第一次查：根据部门地址查部门号
SELECT deptno FROM dept WHERE loc='二区';
#第二次查：根据部门编号查员工名字
SELECT ename FROM emp WHERE deptno IN (2,3)#查到多条用in
SELECT ename FROM emp WHERE deptno IN (         #所以这里使用in
SELECT deptno FROM dept WHERE dept.loc='二区'   #这里会查出来两条数据
);

#查询高于平均薪资的员工信息
SELECT emp.* FROM emp WHERE sal>(
SELECT AVG(sal) FROM emp              #查平均薪资
);

SQL执行顺序
(1) FROM [left_table] 选择表

(2) ON <join_condition> 链接条件

(3) <join_type> JOIN <right_table> 链接

(4) WHERE <where_condition> 条件过滤

(5) GROUP BY <group_by_list> 分组

(6) AGG_FUNC(column or expression),... 聚合

(7) HAVING <having_condition> 分组过滤

(8) SELECT (9) DISTINCT column,... 选择字段、去重

(9) ORDER BY <order_by_list> 排序

(10) LIMIT count OFFSET count; 分页