数据库概述 3-MySQL多表查询内连接外连接子查询事务

最新推荐文章于 2021-11-21 19:42:08 发布

AdamCafe

最新推荐文章于 2021-11-21 19:42:08 发布

阅读量255

点赞数

分类专栏：数据库知识文章标签：数据库知识

本文链接：https://blog.csdn.net/AdamCafe/article/details/89300679

版权

数据库知识专栏收录该内容

6 篇文章 1 订阅

订阅专栏

内容介绍

1  范式（理解）
2  多表查询（重点）
3  SQL分类之TCL（事务）

范式（理解）

范式:设计数据库表的规则

好的数据库表设计对数据的存储和后期的程序开发，都会产生重要的影响。

建立科学的，规范的数据库表就需要满足一些规则，这些规则就称为范式

范式的分类：

第一范式（1NF）
在这里插入图片描述
第二范式（2NF）

在这里插入图片描述

第三范式（3NF）
在这里插入图片描述

巴斯-科德范式（BCNF）

第四范式(4NF）

第五范式（5NF，又称完美范式）

满足最低要求的范式是第一范式（1NF）在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），

其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

三大范式：

第一范式（1NF）
	规则：数据库表中的某一列如果有多个值的话，必须拆分成为不同的列
	      

第二范式（2NF）
	规则：在满足第一范式的基础上，需要满足2点
	      1 一张表只能用来描述一件事情
	      2 表中的每个字段都依赖主键

第三范式（3NF）
	规则：在满足第一范式和第二范式基础上，从表的外键必须使用主表的主键（表关系建立）

小结：

范式: 设计数据库表的规范

1NF：数据库表的列具有原子性 列不能再次被拆分
2NF：一张表只能描述一件事情，表中得有主键
3NF：若存在关系，从表的外键必须指向主表的主键

多表查询

在这里插入图片描述

多表查询：从多张表中获取到有关系的数据

比如：查询分类信息以及分类下的所有商品

多表查询的语法分类：

1.交叉查询（企业开发避免使用的，查出来的数据是脏数据）

在这里插入图片描述
2.内连接查询（查询多张表中有关系的数据，没关系的查不出）

在这里插入图片描述

隐式内连接

显示内连接  （查询效率比隐式内连接效率更高一些）

3.外连接查询

在这里插入图片描述
内连接与外连接的区别

左外连接连接

右外连接查询

4.子查询

子查询的作用：（内连接查询到的，子连接都能查到）
作用1：可以将一条sql语句作为另外一条sql语句的条件
作用2：可以将一条sql语句作为一张临时表匹配

在这里插入图片描述

#多表查询之子查询
#子查询

#作用1：可以将一条sql语句作为另外一条sql语句的条件
#单列单值或着单列多值
#单列单值 SELECT COUNT(*) FROM emp;
#单列多值 SELECT NAME FROM emp;
#格式：select * from 表 where 字段 >= / <= / like /<> (SELECT COUNT(*) FROM emp)
#格式：select * from 表 where 字段 in(SELECT COUNT(*) FROM emp)

#1 查询工资最高的员工是谁 (最高工资结果是单列单值)
#查询工资最高
SELECT MAX(salary) FROM emp;
#根据最高工资查询所属的员工
SELECT NAME FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);

#2 查询工资小于平均工资的员工有哪些 (平均工资 结果是单列单值)
#查询平均工资
SELECT AVG(salary) FROM emp;
#根据平均工资找小于它的员工
SELECT NAME FROM emp WHERE salary<(SELECT AVG(salary) FROM emp);

#3 查询工资大于5000的员工部门ID，来自于哪些部门(工资大于5000的员工部门ID 结果是单列多值)
#查询工资大于5000的员工部门ID
SELECT dept_id FROM emp WHERE salary>=5000;
#根据员工部门ID去部门表中找部门名称
SELECT NAME FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary>=5000);

#4 查询开发部与财务部所有的员工信息(开发部与财务部的id 结果是单列多值)
#先去部门表中查开发部与财务部的id
SELECT id FROM dept WHERE NAME IN("开发部","财务部");
#根据查到的id去员工表找对应的员工名称
SELECT NAME FROM emp WHERE 
dept_id IN(SELECT id FROM dept WHERE NAME IN("开发部","财务部"));
#内连接查询
SELECT e.name FROM dept d,emp e WHERE d.id=e.dept_id
AND d.name IN("开发部","财务部");

#作用2：可以将一条sql语句作为一张临时表匹配
#多列多值  SELECT * FROM emp WHERE gender='男';
#格式：select * from A表,(SELECT * FROM emp WHERE gender='男') L WHERE A.字段=L.字段
#查询出2011-01-01以后入职的员工信息，包括部门名称(结果是多列多值)
# 查询2011-01-01以后入职的员工信息 做为一张临时表
SELECT * FROM emp WHERE join_date>"2011-01-01";
# 查询部门表和临时表之间有关系的数据
SELECT * FROM dept d,(SELECT * FROM emp WHERE join_date>"2011-01-01") l
WHERE d.id=l.dept_id;
#内连接
SELECT * FROM dept d,emp e WHERE d.id=e.dept_id
AND e.join_date>"2011-01-01";

CREATE DATABASE db3;
USE db3;
-- 部门表
CREATE TABLE dept (
	id INT PRIMARY KEY, -- 部门id
	dname VARCHAR(50), -- 部门名称
	loc VARCHAR(50) -- 部门位置
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表，职务名称，职务描述
CREATE TABLE job (
	id INT PRIMARY KEY,
	jname VARCHAR(20),
	description VARCHAR(50)
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 员工表
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,40),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 工资等级表,最低工资 最高工资
CREATE TABLE salarygrade (
	grade INT PRIMARY KEY,
	losalary INT,
	hisalary INT
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

#4张表： dept(部门表)  emp（员工表） job（职务表）  salarygrader(工资等级表)
#关联关系：
#emp（员工表）和 dept(部门表)  1对多的关系 
#emp（员工表）和 job(职务表)   1对多的关系 
#关联关系的字段：
#emp（员工表).dept_id=dept(部门表).id
#emp（员工表).job_id=job(职务表).id

#查询所有员工信息。显示员工编号，员工姓名，员工工资，职务名称，职务描述
#1要查几张表      员工表  职务表
#2关联关系是谁    emp（员工表).job_id=job(职务表).id
#3其余的筛选条件  无
#4要显示的内容是什么  员工编号  员工姓名  员工工资 || 职务名称  职务描述
SELECT e.id,e.ename,e.salary,j.* FROM emp e,job j
WHERE e.job_id=j.id;

#查询所有员工信息。显示员工编号，员工姓名，员工工资，职务名称，职务描述，部门名称，部门位置
#1 要查几张表 员工表  职务表  部门表
#2 关联关系字段 emp（员工表).dept_id=dept(部门表).id   emp（员工表).job_id=job(职务表).id
#3 其余筛选条件 无
#4 要显示的内容 员工编号，员工姓名，员工工资 || 职务名称，职务描述 || 部门名称，部门位置
SELECT e.id,e.ename,e.salary,j.jname,j.description,d.dname,d.loc 
FROM emp e,job j,dept d
WHERE e.dept_id=d.id AND e.job_id=j.id;
#查询所有员工信息。显示员工姓名，员工工资，职务名称，职务描述，部门名称，部门位置，工资等级
#1 要查几张表 员工表 部门表  职务表  工资等级表
#2 关联关系 emp（员工表).dept_id=dept(部门表).id   emp（员工表).job_id=job(职务表).id
#3 其余筛选条件 员工.工资 between 工资等级表.losalary and n工资等级表.hisalary
#4 要显示内容 员工姓名，员工工资 || 职务名称，职务描述 || 部门名称，部门位置 || 工资等级
SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,s.grade
FROM emp e,dept d,job j,salarygrade s
WHERE e.dept_id=d.id AND e.job_id=j.id
AND e.salary BETWEEN s.losalary AND s.hisalary;
#查询经理的信息。显示员工姓名，员工工资，职务名称，职务描述，部门名称，部门位置，工资等级
#1 要查几张表 员工表 部门表  职务表  工资等级表
#2 关联关系 emp（员工表).dept_id=dept(部门表).id   emp（员工表).job_id=job(职务表).id
#3 其余筛选条件 员工.工资 between 工资等级表.losalary and n工资等级表.hisalary || j.jname='经理'
#4 要显示内容 员工姓名，员工工资 || 职务名称，职务描述 || 部门名称，部门位置 || 工资等级
SELECT * FROM emp e JOIN dept d JOIN job j JOIN salarygrade s
ON e.dept_id=d.id AND e.job_id=j.id 
WHERE e.salary BETWEEN s.losalary AND s.hisalary AND j.jname='经理';

#查询出部门编号、部门名称、部门位置、每个部门的员工人数
#1 要查几张表 部门表  员工表
#2 关联关系 emp（员工表).dept_id=dept(部门表).id
#3 其余筛选条件 聚合 根据部门分组
#4 要显示内容 部门编号  部门名称  部门位置  员工人数
SELECT d.id AS "部门编号",d.dname AS "部门名称",d.loc AS "部门位置",COUNT(*) AS "每个部门的总人数" FROM dept d,emp e
WHERE e.dept_id=d.id
GROUP BY d.dname;

交叉连接(了解)

select * from 表1,表2;

会产生2张表的乘积数据，简称笛卡尔积数据

我们发现这种方式查询出来的数据对我们来说没有任何意思
对于多表查询来说：我们最终要得到的数据其实是2张表有关系的数据
那么对于有关系的数据我们只能通过条件筛选，过滤掉没有关系的数据
所以：条件过滤对多表查询致关重要
条件过滤：可以寻找2张表之间有关联的字段进行过滤

内连接查询

隐式内连接:

语法格式：select * from 表1,表2 where 关联条件 and 筛选条件 and..; 

ps:可以使用表别名简化书写

显示内连接

语法格式：select * from 表1 [inner] join 表2  on 关联条件 where 筛选条件  and ....; 

on: 后跟的是关联条件

where: 后跟的是对结果的筛选条件

需求：查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称

可以使用内连接去查询2张表之间有关系的数据

外连接查询

左外连接: 以join 左边的表为主,查询出来的是2张表之间有关系的数据以及左边表所有的数据

如果右表中没有满足条件的对应数据,则填充 null

	语法格式：select * from 表1 left [outer] join 表2 on 关联条件 where 筛选条件;

右外连接: 以join 右边的表为主,查询出来的是2张表之间有关系的数据以及右边表所有的数据

如果左表中没有满足条件的对应数据,则填充null

	语法格式：select * from 表1 rigth [outer] join 表2 on 关联条件 where 筛选条件;

子查询

用处1：一条sql执行的结果是另一条sql的条件，适用于单列单值或者单列多值
单列单值: select * from 表名 where (子查询结果)
单列多值: select * from 表名 where (子查询结果)

用处2：一条sql执行的结果作为一张表存在，适用于多列多值
多列多值: select * from  表名，(子查询结果)  where 条件

需求：

查询出2011-01-01以后入职的员工信息，包括部门名称(结果是多列多值)

事务技术

在这里插入图片描述
在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次

访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。

如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。

例如转账:
	1   tom  1000	
	2   rose 1000	
完成转账这个业务需要操作2次数据库     
1.从tom的帐号上减去500
     update account set money = money - 500 where id = 1 ;
2.将500加到rose帐号上
     update account set money = money + 500 where id = 2 ;

如何控制2条sql语句要不全部算成功，要不全部算失败呢？
我们得使用数据库提供的事务技术帮我们达成目的

事务技术的作用：

可以控制一件完整事情的多个步骤

只要有一个步骤出现了错误就算整件事情是失败了，只要所有步骤全部成功才控制当前这件事情是成功的

需求：创建账户表，通过事务技术完成转账的控制

事务操作

mysql中默认每一条sql语句都会自动提交

但是我们得让每一条sql语句都得有自己手动提交，所以要关闭自动提交

进行事务操作：

start transaction;开启一个事务(关闭自动提交)

commit; 提交事务并关闭当前的手动提交

rollback;事务回滚并关闭当前的手动提交

结论：我们可以将一个需要操作多次数据库的业务放入到一个事务中处理，方便我们管理该业务对数据库数据的每一次影响

了解：更改数据库的提交方式

show variables like '%autocom%'	

set autocommit=off

但是该方式只能是临时的



了解：事务可以定义回滚点 （A库的数据---B库的数据）

savepoint 回滚点名字;

rollback to 回滚点名字;

面试题：事务的四大特性(ACID)

原子性（Atomicity）

事务是一个不可分割的单位，事务中的操作要么都成功，要么都失败。

一致性（Consistency）

事务前后数据的完整性必须保持一致

持久性（Durability）

指一个事务一旦被提交，它对数据库中数据的改变就是永久性的

隔离性（Isolation）

是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离，不能相互影响。

事务的隔离性

问题:假如事务不具备隔离性，用户会在读取数据库数据的时候发生读取问题

脏读 : 读未提交的数据（必须避免）

一个事务读取到了另一个事务中尚未提交的数据

不可重复读 (针对update修改的)

一个事务中两次读取的数据内容不一致

虚读/幻读(针对insert插入的)

一个事务中两次读取的数据的数量不一致

解决

数据库的隔离级别

read uncommitted		    读未提交	 以上三个问题都会发生
read committed(oracle默认)	读已提交	 避免脏读的发生
repeatable read(mysql默认)	重复读	 避免脏读和不可重复的读的发生
serializable			    串行化    避免所有问题的发生（安全高，效率最低）



查询mysql的隔离级别

    show variables like "%isolation%";  

设置事务的隔离级别

set global transaction isolation level 级别字符串;

1.读未提交 (最低级别，以上情况均无法保证)

read uncommitted:

2.读已提交 (可避免脏读情况发生。)

read committed

3.可重复读 (可避免脏读、不可重复读情况的发生。 mysql的默认隔离级别)

repeatable read

4.串行化 (可避免脏读、不可重复读、幻读(虚读)情况的发生)

使用serializable隔离级别，一个事务没有执行完，其他事务的SQL执行不了	

serializable

AdamCafe

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库概述 3-MySQL多表查询内连接外连接子查询事务

内容介绍1 范式（理解）2 多表查询（重点）3 SQL分类之TCL（事务）范式（理解）范式:设计数据库表的规则好的数据库表设计对数据的存储和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库表就需要满足一些规则，这些规则就称为范式范式的分类：第一范式（1NF）第二范式（2NF）第三范式（3NF）巴斯-科德范式（BCNF）第四范式(4NF）第五范式（5N...
复制链接

扫一扫