MySQL数据库学习日志（四）：表关系 多表查询 数据库的备份和还原

最新推荐文章于 2023-04-11 19:25:52 发布
最新推荐文章于 2023-04-11 19:25:52 发布
阅读量214 收藏 1
点赞数 1
分类专栏： 数据库 文章标签： 数据库 sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_52344565/article/details/123810608
版权

MySQL数据库学习日志（四）：表关系 多表查询 数据库的备份和还原

表关系

（一）概念和分类

现实生活中，实体与实体之间是存在关系的，比如：老公和老婆，部门和员工，老师和学生等。我们设计的表是对现实中实体的描述，那么我们在设计表的时候，就应该体现出表与表之间的这种关系。

表与表之间的关系主要包括：一对多（多对一）、一对一、多对多三种。

（二）一对多（多对一）

一对多（1:n）。 例如：班级和学生，部门和员工，客户和订单，分类和商品等

一对多建表原则: 在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键。

在这里插入图片描述

建表操作和讲解外键时相同。

（三）多对多

多对多（m:n）。例如：老师和学生，学生和课程，用户和角色。

多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段 。这两个字段分别作为外键指向各自一方的主键。

在这里插入图片描述

示例

-- 创建学生表student
create table student(
     id int primary key auto_increment,
	 name varchar(20)
)
-- 创建课程表course
create table course(
     id int primary key auto_increment,
	 name varchar(20)
)

-- 创建中间关系表 student_course,中间关系表中student_id和course_id联合做主键，分别做外键参照多方的两端
create table student_course(
      student_id int,
	  course_id int,
	  primary key(student_id,course_id),
	  constraint foreign key(student_id) references student(id),
	  constraint foreign key(course_id) references course(id)
)

（四）一对一

一对一（1:1）。 在实际应用中并不多见，因为一对一可以创建成一张表。

1. 主键关联方式

主表的主键和从表的主键，形成主外键关系。

示例

-- 创建丈夫表husband
create table husband(
      id int primary key auto_increment,
	  name varchar(20)
)

-- 创建妻子表wife，wife的主键列id作为外键参照husband的主键列id
create table wife(
      id int primary key,
	  name varchar(20),
	  constraint foreign key(id) references husband(id)
)

在这里插入图片描述

2. 唯一外键方式

主表的主键和从表的外键（唯一），形成主外键关系。

示例

-- 创建person表
create table person(
     id int primary key auto_increment,
	 name varchar(20)
);
-- 创建idcard表，idcard表中的person_id做外键参照person表的id，在person_id列上添加唯一约束，就表示出了person和idcard的一对一关系。
create table idcard(
	 id int primary key,
	 card_num varchar(20),
	 person_id int unique,
	 constraint foreign key(person_id) references person(id)
)

在这里插入图片描述

多表查询

（一）什么是多表查询

之前我的查询都是从一张表中返回数据。同时查询多张表获取到需要的数据，就是多表查询。比如：要查询员工的姓名和所在的部门名称，需要从employee表和department表两个表中查询数据。

在这里插入图片描述

准备数据

-- 创建部门表 dept
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

-- 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT
);

-- 添加数据
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,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

（二）笛卡尔积

1. 什么是笛卡尔积现象

多表查询时左表的每条数据和右表的每条数据组合，这种效果成为笛卡尔积。

语法

select * | 列1,列2,...,列n from 表1,表2,...,表n

示例

-- 需求：查询部门和员工信息
SELECT * FROM dept, emp;

在这里插入图片描述

以上数据其实是左表的每条数据和右表的每条数据组合。左表有3条，右表有5条，最终组合后3*5=15条数据。

左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积，笛卡尔积中有很多没有意义的数据。

在这里插入图片描述

2. 如何消除笛卡尔积现象

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

在这里插入图片描述

SELECT * FROM dept, emp WHERE emp.dept_id = dept.id;

在这里插入图片描述

（三）多表查询

要清除笛卡尔积现象，可以使用连接查询。连接查询分为内连接和外连接。

在这里插入图片描述

1. 内连接

用左边表的记录去匹配右边表的记录，符合条件的才显示。内连接又分为隐式内连接和显示内连接两种。

（1）隐式内连接

隐式内连接：不使用JOIN关键字，条件使用WHERE指定。

语法

SELECT 字段名 FROM 左表, 右表 WHERE 条件

示例

-- 查询员工的姓名和所在的部门的名称
select emp.name emp_name,dept.name dept_name from emp,dept where emp.dept_id = dept.id
-- 可以使用表别名的方式简化查询语句
select e.name emp_name,d.name dept_name from emp e,dept d where e.dept_id = d.id

在这里插入图片描述

where 条件中的 e.dept_id = d.id 就是连接条件

-- 查询员工孙悟空的信息和他所在的部门名称
select e.*,d.name dept_name from emp e,dept d where e.dept_id=d.id and e.name='孙悟空'

在这里插入图片描述

（2）显示内连接

显示内连接：使用INNER JOIN … ON语句，可以省略INNER。查询的效果和隐式内连接相同。

语法

SELECT 字段名 FROM 左表 INNER JOIN 右表 ON 条件；

示例

-- 查询唐僧的信息，显示员工id、姓名、性别、工资和所在的部门名称，我们发现需要联合两张表才能同时查询出需要的数据，我们使用内连接。
select e.id emp_id,e.name emp_name,e.gender emp_gender,e.salary emp_salary,d.name dept_name from emp e inner join dept d on e.dept_id=d.id and e.name='唐僧'
2. 外连接
（1）左外连接

左外连接：使用LEFT OUTER JOIN … ON，OUTER可以省略。

左表数据全部显示，右表显示和左表有关联的数据。

语法

 SELECT 字段名 FROM 左表 LEFT OUTER JOIN 右表 ON 条件

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示NULL。

示例

新入职了一名员工 金角大王，但该员工没有分配部门。

在这里插入图片描述

现在要查询员工的信息和所在部门的名称。如果使用内连接查询，没有部门的员工将不会显示，因为不满足连接条件。

select e.*,d.name dept_name from emp e,dept d where e.dept_id=d.id

想要显示全部员工，可以把员工emp 当成左表，部门dept表当成右表，使用左外连接查询。

在这里插入图片描述

（2）右外连接

右外连接：使用RIGHT OUTER JOIN … ON，OUTER可以省略。右表数据全部显示，左表显示和右表有关联的数据。

语法

SELECT 字段名 FROM 左表 RIGHT OUTER JOIN 右表 ON 条件；

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示NULL。

示例

-- 使用右外连接解决上面左外连接解决的问题
select e.*,d.name dept_name from dept d right outer join emp e on e.dept_id = d.id;

在这里插入图片描述

使用右外连接，把原来的左右表位置互换，产生同样的查询效果。

（四）子查询

一条SELECT语句结果作为另一条SELECT语法的一部分。

语法

select 字段，... from 表  where 字段  运算符 (select  字段,... from 表)

子查询 (内查询) 在主查询之前执行一次。

子查询的结果被用于主查询 (外查询)

示例

-- 查询出工资最高的员工信息
SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee)
-- 子查询先查询出最高工资，主查询查询出拥有最高工资的员工信息

在这里插入图片描述

在这里插入图片描述

1. 子查询使用规则

1、子查询放在圆括号中。

2、将子查询放在比较条件的右边。（非强制要求）

3、在子查询中的 ORDER BY 子句不需要。

4、在单行子查询中用单行运算符，在多行子查询中用多行运算符。

2. 单行子查询

子查询的结果是一个值

在这里插入图片描述

单行运算符

= > >= < <= != 等我们学习过的众多运算符都是单行运算符

示例

-- 查询工资比白骨精高的员工信息
select * from emp where salary>(select salary from emp where name='白骨精')
-- 子查询先查询出'白骨精'的工资，作为条件交给主查询使用

在这里插入图片描述

在这里插入图片描述

3. 多行子查询

子查询结果是单例多行

多行运算符

in、any、all

示例

-- 1. 查询工资大于5000的员工，来自于哪些部门的名字 
-- 先查询大于5000的员工所在的部门id
SELECT dept_id FROM emp WHERE salary > 5000

在这里插入图片描述

-- 再查询在这些部门id中部门的名字
SELECT dept.name FROM dept WHERE dept.id IN (SELECT dept_id FROM emp WHERE salary > 5000)

在这里插入图片描述

-- 2. 查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的id
SELECT id FROM dept WHERE NAME IN('开发部','财务部');

在这里插入图片描述

-- 再查询在这些部门id中有哪些员工
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('开发部','财务部'))

在这里插入图片描述

any 和 all 不是单独使用的运算符，要和 比较运算符一起使用

示例

修改表中数据，把蜘蛛精的name修改为白骨精

在这里插入图片描述

select salary from emp where name='白骨精'

在这里插入图片描述

any表示任意值（比最小的大）

select name,salary from emp where salary >any(select salary from emp where name='白骨精')

在这里插入图片描述

all表示全部值（比最大的大）

select name,salary from emp where salary >all(select salary from emp where name='白骨精')

在这里插入图片描述

4. 多列子查询

子查询结果是单列，在WHERE后面作为条件 子查询结果

子查询结果是多列，肯定在FROM后面作为表

语法

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件
-- 子查询结果只要是多行多列，肯定在FROM后面作为表 。
-- 子查询作为表需要取别名，否则这张表无法访问表中的字段。

示例

-- 查询出2011年以后入职的员工信息，包括部门名称
-- 在员工表中查询2011-1-1以后入职的员工
SELECT * FROM emp WHERE join_date > '2011-1-1'

在这里插入图片描述

-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
SELECT e.id emp_id,e.name emp_name,e.gender emp_gender,e.salary emp_salary,e.join_date emp_join_date,d.id dept_id,d.name dept_name FROM dept d, (SELECT * FROM emp WHERE join_date > '2011-1-1') e WHERE e.dept_id = d.id

在这里插入图片描述

数据库的备份和还原

（一）备份的应用场景

我们在服务器上进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

（二）备份

1. 命令方式备份

语法

mysqldump -u用户名 -p密码 数据库 > 文件的路径/文件名.sql

示例

备份mydb1数据库中的数据
mysqldump -uroot -pmysql mydb1 > D:/abc.sql

在这里插入图片描述

sql文件中将会包含相应的建表语句和插入语句

2. 使用Navicat方式备份

在这里插入图片描述

再选择路径保存相应的sql文件。

（三）还原

1. 命令方式还原

语法

SOURCE 导入文件的路径;

示例

注意：还原时要先登录到MySQL中
创建一个新数据库mydb4，把前面导出的mydb1中的数据导入到mydb4中。
use mydb4;
source D:/abc.sql

在这里插入图片描述

在这里插入图片描述

2. 使用Navicat方式还原

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

end

不知不觉 又半夜了
呜呜呜呜呜呜呜呜
在这里插入图片描述

程序猿小张的日常笔记
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
day25_SQL语言（六）多表查询
qq_36972345的博客
08-09 696
一、多表查询 （一）什么是多表查询 之前我的查询都是从一张表中返回数据。同时查询多张表获取到需要的数据，就是多表查询。比如：要查询员工的姓名和所在的部门名称，需要从employee表和department表两个表中查询数据。 准备数据 创建部门表 deptCREATE TABLE dept ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) );添加数据INSERT INTO dept (NAME) VALUES ('开...
mysql员工表部门表_部门表 department 部门编号 dept_id 部门名称 汇总表
weixin_39743695的博客
02-08 6005
有两张表部门表 department 部门编号 dept_id 部门名称 dept_mementcreate table department(dept_id tinyint auto_increment primary key,dept_mement varchar(30))engine=InnoDB default charset utf8;insert into department(d...
数据库的备份与恢复
ymy101的博客
10-06 1149
哈喽~亲爱的小伙伴们大家好啊！ 没想到吧十一假期我还在哈哈哈哈哈~ 我还是你们熟悉的那个程序袁啊，上一篇呢主要讲述了如何安装和使用mysql。 而在今天教大家一个很厉害的东西，恢复和备份。 在我们的日常工作中呢，往往会因为多种因素导致我们的数据库丢失 数据的丢失，所以今天就来教大家如何恢复和备份！ 首先我们要安装mysql数据库，创建一个grade数据库并且录入一些数据，记不清的小伙伴可以点击这里来查看如何安装mysql数据库以及基本的使用方法，当我们录入好...
MySQL 表之间的关系
Waller_的博客
08-20 324
表之间的关系 # 定义一张部门员工表 emp id name gander dep_name dep_desc 1 ming male 教学部 教书 2 lilei male 教学部 教书 3 hammm female 销售部 销售工作 4 hong female 技术部 ...
数据库日常练习
m0_67321782的博客
03-23 1793
本文主要使用三个示例表：员工表（emp）、部门表（dept）和职位表（job）。下面是这些示例表的结构图： 部门表（dept），包含部门编号（id）和部门名称（name）字段，主键为部门编号。 职位表（job），包含职位编号（id）和职位名称（title）字段，主键为职位编号。 员工表（emp），包含员工编号（id）、员工姓名（name）、性别（sex）、年龄（age）、入职日期（hiredate）、月薪（salary）、奖金（bonus）以及电子邮箱（email）、部门编号（did）、...
数据库 = MySQL多表查询与权限，数据库备份与还原
人生起落，生活苦甜，坚持追求！
05-09 422
多表查询，用户权限DCL，数据库的备份与还原 一.多表查询 同时查询多张表获取到需要的数据组成完整的信息返回给用户。 多表查询分类： 数据准备： -- 多表查询 create database crmpro; use crmpro; -- 创建部门表（主表） CREATE TABLE dept ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); INSERT INTO dept (NAME) VALUES ('开发部.
数据库基础知识--------MySQL 日志管理、备份与恢复
认真学习
06-28 95
目录一、MySQL 日志管理二、数据库备份的重要性与分类1.数据备份的重要性2. 造成数据丢失的原因3.从物理与逻辑的角度，备份分为4.从数据库的备份策略角度，备份可分为三、常见的备份方法1.物理冷备2.专用备份工具mydump或mysqlhotcopy3.启用二进制日志进行增量备份4.第三方工具备份四、MySQL完全备份优点缺点五、数据库完全备份分类1.物理冷备份与恢复2.mysqldump备份与恢复（1）完全备份一个或多个完整的库（包括其中所有的表）（2）完全备份 MySQL 服务器中所有的库（3）完全
MySQL多表查询
weixin_30606461的博客
09-30 131
多表查询： 一、基础语法 * 查询语法： select 列名列表 from 表名列表 where....* 准备sql -- 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ...
SQL结构化查询语言
xh_ssh的博客
02-13 390
前言 眼看着考研的成绩就出来了 ，莫名奇妙的自信让我感觉初试肯定能过，所以再次对复习的数据库的知识进行一定的总结 SQL结构化查询语言 是一种介于关系代数和元组之间的一种结构化查询语言，广泛用于商用系统中，一个SQL数据库是表的汇集 SQL的功能特点和体系结构 SQL的功能包括 数据定义，数据操作，数据控制 ，主要特点包括 综合统一：sql是结合数据定义，数据控制，数据操作为一体的 ，语言风格...
SQL——结构化查询语言
m0_56060952的博客
01-12 596
SQL——结构化查询语言
SQL数据库（分页查询、DCL 数据控制语言、通用函数、约束、多表查询）
最新发布
qq_52847866的博客
04-11 236
分页查询、DCL 数据控制语言、通用函数、约束、多表查询
SQL语言---数据的查询
weixin_52042488的博客
09-29 5174
本文主要讲解了SQL语言的数据查询的相关语法，并且给出了详实的例子供读者参考
SQL语言快速入门(转)
cuiji1279的博客
08-13 109
SQL语言快速入门(转)[@more@] 　　SQL是英文Structured Query Language的缩写，意思为结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通。按照ANSI（美国国家标准协会）...
多表查询
h294590501的专栏
05-20 488
多表查询-- 数据准备 ​ # 创建部门表 ​ CREATE TABLE dept( ​ id INT PRIMARY KEY AUTO_INCREMENT, ​ NAME VARCHAR(20) ​ ); ​ INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');   ​ # 创建员工表 ​ CREATE TABLE emp ( ​...
MySQL学习笔记八：多表查询
我们仨
04-03 307
创建两张表：dept表和emp表 /*创建部门表*/ CREATE TABLE dept( deptno INT PRIMARY KEY, dname VARCHAR(50), loc VARCHAR(50) ); /*创建雇员表*/ CREATE TABLE emp( empno INT PRIMARY KEY, ename VARCHAR(50), job ...
左外连接、右外连接、内连接、全连接
wangfengqingyang的专栏
09-11 702
1、内联接（典型的联接运算，使用像 =  或 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。       2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。     在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：     1）LEFT  JOIN或LEFT OU
1.4.3.7 Hive的查询操作
寒暄的博客
04-11 342
总目录:https://www.jianshu.com/p/e406a9bc93a9 Hadoop - 子目录：https://www.jianshu.com/p/9428e443b7fd SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition...
mysql查找表中员工姓名性别_SQL 常见面试题解析
weixin_42300798的博客
02-08 8577
内容简介本文介绍并分析了 100 道常见 SQL 面试题，主要分为三个模块：SQL 初级查询、SQL 高级查询以及数据库设计与开发。文章内容结构如下图所示：本文主要使用三个示例表：员工表(employee)、部门表(department)和职位表(job)。下面是这些示例表的结构图：部门表(department)，包含部门编号(dept_id)和部门名称(dept_name)字段，主键为部门编号。...
sql语句 case_使用SQL Case语句查询数据
culuo4781的博客
07-18 5179
sql语句 case The Case statement in SQL is mostly used in a case with equality expressions. The SQL Case statement is usually inside of a Select list to alter the output. What it does is evaluates a l...
达梦数据库：日志备份与还原策略解析
本文将深入探讨数据库备份与还原的基础知识，重点聚焦在达梦数据库(DM数据库)上，这是中国自主研发的高性能数据库管理系统，其7.0版本DM7具有通用性、高性能、高可用性、跨平台以及高可拓展性等特点。相较于流行的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序猿小张的日常笔记

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值