目录
1.事务及其特性
当执行多条mysql语句或者查询多个表时,启用事务,使他们同时成功或者同时失败
ACID:
原子性(Atomicity):每个事务都是一个整体,不可再拆分,事务中sql语句要么都执行成功,要么都执行失败
一致性(Consistency):事务在执行前的数据库的状态与执行后的数据库的状态保持一致,如转账前两个人总钱数是2000,转账后两个人的总钱数还是2000;
隔离性(Isolation):事务和事务之间不应该互相影响,执行时保持隔离的状态
持久性(Durability):一旦事务执行成功,对数据库的修改是持久的,就算关机也保存下来了
2.事务的隔离级别
级别 名字 隔离级别 脏读 不可重复读 幻读 数据库默认隔离级别
1 读未提交 read uncommitted 是 是 是
2 读已提交 read committed 否 是 是 Oracle 和 SQL Server
3 可重复读 repeatable read 否 否 是 MySQL
4 串行化 serializable 否 否 否
脏读: 一个事务读取到了另一个事务中尚未提交的数据
不可重复读:
一个事务中两次读取的数据内容不一致,要求的是一个事务中多次读取时数据是一致的,这 是事务 update 时引发的问题
幻读:
一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致 的,这是 insert 或 delete 时引发的问题
查询数据库默认隔离级别:
SELECT @@transaction_isolation;
3.数据库的三大范式
第一范式(1NF):表的每一列不可被拆分
例子:如姓名一列不可被拆分中文名和英文名
第二范式(2NF):在第一范式的基础上,非主键列必须绝对依赖于主键列,一张表只描述一个事物
例子:如学生基本信息中不可以出现学校图书表相关的信息
第三范式(3NF):在2NF基础上,数据库的每列不得传递依赖于主键
例子:一个学生可以选择多个课程,一个课程可以被多个学生选择
4.多表查询
创建一个员工表-
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(10), -- 员工名称
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT, -- 部门编号
FOREIGN KEY (dept_id) REFERENCES dept(id)-- 外键,关联部门表(部门表的主键)
)
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男 ',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男 ',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2020-08-20',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女 ',5000,'2020-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女 ',4500,'2021-01-14',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('高圆圆','女 ',16000,'2022-05-17',1);
笛卡尔积
查询多张表:查询员工表和部门表信息 注意事项:如果使用这种语法 select 字段列表 from 表名1,表名2;
会出现"笛卡尔乘积",原因:两张表是有关联关系,就是员工表的dept_id要关联部门表id A表的记录数 *B表的记录数 = 总记录数
多表查询分类三种
1)内连接
隐式内连接---看不到join关键字,使用where条件
内连接之隐式内连接 :
select 字段列表 from 表名名1,表名名2 where 连接条件 ;
1:查询哪张表
2:查询哪些字段
3:表和表之间的关联关系:连接条件
- 需求:查询员工表的id以及name,工资,入职日期以及关联同时查询部门表的部门编号以及部门名称
select
e.`id` '员工编号',
e.`name` '员工姓名',
e.`salary` '工资',
e.`join_date` '入职日期',
d.`id` '部门编号',
d.`name` '部门名称'
from
emp e,
dept d
where e.`dept_id` = d.`id` ;
显示内连接—inner join 关键字:inner可以省略
select 字段列表 from 表名名1 inner join 表名名2 on 连接条件
-- 查询所有员工以及所有的部门信息
SELECT
e.*,
d.*
FROM
emp e
INNER JOIN dept d
ON
e.`dept_id` = d.`id` ;
2)外连接
上面这种情况,查询出来只是查询来满足连接条件的数据 dept_id和dept的id一致的
– 但是没有部门的员工应该查询出来—使用外连接
– 左外连接和右外连接
– 左外连接:将左边表数据以及满足两种表条件的数据全部展示;
– 语法:select 字段列表 from 表名1 left outer join 表名2 on 连接条件
– outer可以省略
左外连接(推荐)
-- 查询员工的id,name,salary,join_date以及部门编号以及所有部门的名称,没有部门的员工也得展示
SELECT
e.`id` '员工编号',
e.`name` '员工姓名',
e.`salary` '员工工资',
e.`join_date` '入职日期',
e.`dept_id` '部门编号',
d.`name` '部门名称'
FROM
emp e
LEFT JOIN dept d
ON e.`dept_id` = d.`id` ;
右外连接
SELECT
d.`name` '部门名称' ,
e.`id` '员工编号',
e.`name` '员工姓名',
e.`salary` '员工工资',
e.`join_date` '入职日期',
e.`dept_id` '部门编号'
FROM
emp e
RIGHT OUTER JOIN dept d
ON e.`dept_id` = d.`id` ;
3)子查询:select 语句嵌套select语句,
这种场景实际开发使用很少
查询的效率比较低
常用where条件使用比较运算符
或者使用in集合语句
或者使用一条select语句的结果作为一个"虚表"和其他表查询
情况1:使用where 条件使用比较运算符
-- 需求:查询工资大于平均工资的员工信息
-- 1)查询平均工资是多少
-- select avg(salary) from emp ;-- 7900
-- 2)查询工资大于7900的员工信息
-- select * from emp where salary > 7900 ;
-- 一步走
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp) ;
子查询第二情况2:使用in集合语句
-- 需求:查询在市场部或者财务部的员工信息
-- 1)先查询出市场部和财务部的id
-- select id from dept where name = '市场部' or name = '财务部' ;
-- 2)查询dept_id是2或者3的员工信息
-- select * from emp where emp.`dept_id` in(2,3) ;
-- 一步走
SELECT
* FROM
emp
WHERE emp.`dept_id` IN
(SELECT
id
FROM
dept
WHERE NAME = '市场部'
OR NAME = '财务部') ;
情况三 虚表查询
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
SELECT
e1.ename '员工姓名',
e2.ename '领导姓名'
FROM
(SELECT ename,mgr FROM emp) e1
LEFT OUTER JOIN
(SELECT id , ename FROM emp) e2
ON
e1.mgr = e2.id