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
不知不觉 又半夜了
呜呜呜呜呜呜呜呜