MySQL高级查询

MySQL高级查询

本章的目标

1、掌握外键约束（重点）

2、了解表之间的关系

3、多表连接查询（重点）

4、掌握常用的函数

5、数据库备份（重点）

一、外键约束

MySQL属于RDBMS Relational Database Manage System 关系型数据库管理系统

外键约束能实现引用完整性（引用数据的正确性）

deparment(id,name) 主表

employee(id,name,age,sex,dept_id(部门编号)) 从表/子表

外键约束的作用：

• 添加从表数据时，必须先添加主表数据

• 删除主表数据时，必须先删除从表数据

创建外键的语法：

-- 建表的时候创建
create table 表
(
...
constraint 外键名 foreign key (外键列名) references 主表(主键)
)

外键案例：

-- 创建部门表
create table department
(
    id int primary key auto_increment,
    name varchar(20) not null
);
-- 创建员工表
create table employee
(
    id int primary key auto_increment,
    name varchar(20) not null,
    gender varchar(1) not null,
    address varchar(200) not null,
    age int not null,
    dept_id int ,
    -- 创建表同时添加外键
    constraint fk_dept_id foreign key (dept_id) 
    references department(id)
);
-- 删除外键
alter table employee drop foreign key fk_dept_id;
-- 建表后再添加外键
 
​
insert into department(name) values('技术部'),('销售部'),('运维部');
insert into employee(name,gender,address,age,dept_id) values('张三','男','武汉',20,1);
​
delete from department where id = 1;
select * from employee;
select * from department;

练习：

添加公司表（id、名称、地址）

汽车添加公司id字段，添加外键引用公司的id主键

级联更新和删除

级联更新：更新主表数据时，级联更新子表数据

on update cascade

级联删除: 删除主表数据时，级联删除子表数据

on delete cascade

案例：

-- 建表后再添加外键
alter table employee add constraint fk_dept_id foreign key(dept_id) references department(id)
on update cascade on delete cascade;

二、表之间的关系

表之间的关系分为：

1、1 对 1

人 ---> 身份证

人 主表（id 主键，姓名，性别...）
身份证 从表（id 主键，身份证号码 unique，person_id 外键 unique）

案例：

drop table id_card;
drop table person;
​
-- 创建人员表
create table person
(
    id int primary key auto_increment,
    name varchar(20) not null,
    age int not null,
    gender varchar(20) not null
);
​
-- 创建身份证表
create table id_card
(
    id int primary key auto_increment,
    card_num varchar(20) not null unique,
    person_id int unique,
    constraint fk_person_id foreign key (person_id) references person(id)
);
​
insert into person(name,age,gender) values('张三',20,'男'),('李四',21,'女');
insert into id_card(card_num,person_id) values('420107198811112011',1),('420107198811112012',2);
​
select * from id_card;

总结：一对一，需要在从表的外键列上设置唯一约束

2、1 对 多

最常见的方式，如：部门 和 员工、公司 和 产品、班级 和 学生

主表的1条数据对应从表的多条数据，从表的一条数据对应主表的一条数据

实现方式：

1）创建主表，设置主键

2）创建从表，设置外键，引用主表的主键

3、多 对 多

如：学生选课

一个学生有多门课，一门课也有多个学生

实现方式：加入中间表，包含两个表的外键

-- 多对多关系
drop table student;
create table student
(
    id int primary key auto_increment,
    name varchar(20) not null,
    age int not null,
    gender varchar(20) not null,
    address varchar(200)
);
create table course
(
    id int primary key auto_increment,
    name varchar(20) not null,
    point int not null
);
-- 中间表
create table student_course
(
    id int primary key auto_increment,
    student_id int,
    course_id int,
    constraint fk_stu_id foreign key (student_id) REFERENCES student(id),
    constraint fk_course_id foreign key(course_id) REFERENCES course(id)
);
​
insert into student(name,age,gender,address) values('张三',20,'男','武汉'),('李四',21,'男','武汉'),('张大三',22,'女','武汉'),('李大四',23,'男','武汉');
insert into course(name,point) values('高等数学',10),('物理',10),('英语',10),('语文',10);
-- 给张三选课 物理 英语 语文
insert into student_course(student_id,course_id) values(1,2),(1,3),(1,4);
-- 给李四选数学，语文，英语
insert into student_course(student_id,course_id) values(2,1),(2,4),(2,3);

三、多表查询

多表连接查询能够将多张表的数据连接到一起，查询出来

内连接

将两张表中的相关的数据查询（交集）

语法：

select 列.... from 表1 
[inner] join
表2
on 表1.列 = 表2.列 (主要是：主表.主键 = 从表.外键)

案例：显示员工信息和部门名称

select e.id 员工编号,e.name 员工姓名,e.gender 员工性别,e.address 员工籍贯,e.age 员工年龄,d.name 部门名称
from employee e
inner JOIN
department d
on d.id = e.dept_id;

另一种方式：逗号+where

select e.id 员工编号,e.name 员工姓名,e.gender 员工性别,e.address 员工籍贯,e.age 员工年龄,d.name 部门名称
from employee e,department d
where d.id = e.dept_id;

左外连接

左外连接会显示所有左表的数据，再加两表都有的数据，右表中不相符的地方会补null值

-- 左外连接，显示所有左表数据 + 两表的交集
select e.id 员工编号,e.name 员工姓名,e.gender 员工性别,e.address 员工籍贯,e.age 员工年龄,d.name 部门名称
from department d 
left JOIN
employee e
on d.id = e.dept_id;

右外连接

右外连接会显示所有右表的数据，再加两表都有的数据，左表中不相符的地方会补null值

select e.id 员工编号,e.name 员工姓名,e.gender 员工性别,e.address 员工籍贯,e.age 员工年龄,d.name 部门名称
from employee e
right JOIN
department d 
on d.id = e.dept_id

练习：

查询所有有员工的部门

select e.id 员工编号,e.name 员工姓名,e.gender 员工性别,e.address 员工籍贯,e.age 员工年龄,d.name 部门名称
from department d 
left JOIN
employee e
on d.id = e.dept_id
where e.name is not null;

查询没有员工的部门

select e.id 员工编号,e.name 员工姓名,e.gender 员工性别,e.address 员工籍贯,e.age 员工年龄,d.name 部门名称
from department d 
left JOIN
employee e
on d.id = e.dept_id
where e.name is null;

显示学生姓名以及所选的课程名称

select s.name 学生姓名 , c.name 课程名称 from 
student s
left join student_course sc on s.id = sc.student_id
left join course c on c.id = sc.course_id;

面试题：内连接和外连接的区别

1) 内连接查询两表相交的数据（两表相符的数据）
2) 外连接分为：
    左外连接
        查询所有左表的数据，加两表的交集，右表不相符补null值
    右外连接
        显示所有右表数据。。

四、常用内置函数

字符串函数

数学函数

日期函数

案例：

-- 字符串连接
select concat('Hello','World');
-- 字符串的替换
select replace('Hello!I am studing SQL!','SQL','Cloud');
-- 字符串截取
select substring('Hello!I am studing SQL!',7,12);
-- 向上取整
select ceil(5.9);
select floor(5.9);
-- 产生2~5的随机整数
select ceil(rand() * 100 % 4) + 2;
-- 显示当前时间
select now();
-- 获得年
select year(now());
-- 日期做加法
select date_add('2024-01-01 14:22:11',INTERVAL 10 MONTH);
-- 两个日期的天数差
select DATEDIFF(now(),'2025-01-19');

五、数据库备份

Linux命令实现

1、使用mysqldump命令

mysqldump -u账号 -p密码 数据库名 > 备份文件的路径
​
mysqldump -uroot -p123456 mydb > mydb.sql

2、还原备份文件

1)创建备份的数据库
2)use 数据库
3)source 备份文件的路径

客户端实现