day26-多表关系&多表查询

最新推荐文章于 2024-11-15 19:10:02 发布

·V

最新推荐文章于 2024-11-15 19:10:02 发布

阅读量147

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 mysql sql

本文链接：https://blog.youkuaiyun.com/weixin_71214112/article/details/130392852

本文详细介绍了如何使用MySQL命令和可视化工具进行数据库的备份与恢复，包括如何在命令行和图形界面下操作。同时，讲解了数据库密码的重置流程。接着，文章深入讨论了一对多和多对多关系的数据库设计，以及如何通过外键约束确保数据完整性。最后，文章展示了多表查询的各种方法，如内连接、外连接和子查询的用法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第一章 SQL备份、恢复、数据库密码重置

MySQL命令备份

数据库导出sql脚本的格式：

mysqldump  -u用户名 -p密码 数据库名>生成的脚本文件路径

例如:

mysqldump  -uroot  -proot day04>d:\day03.sql

以上备份数据库的命令中需要用户名和密码，即表明该命令要在用户没有登录的情况下使用

可视化工具备份

选中数据库，右键 ”备份/导出” ，指定导出路径，保存成.sql文件即可。

1.2 SQL恢复

数据库的恢复指的是使用备份产生的sql文件恢复数据库，即将sql文件中的sql语句执行就可以恢复数据库内容。

MySQL命令恢复

使用数据库命令备份的时候只是备份了数据库内容，产生的sql文件中没有创建数据库的sql语句，在恢复数据库之前需要自己动手创建数据库。

在数据库外恢复
- 格式:mysql -uroot -p密码数据库名 < 文件路径
- 例如:mysql -uroot -proot day03<d:\day03.sql
在数据库内恢复
- 格式:source SQL脚本路径
- 例如:source d:\day03.sql
- 注意:使用这种方式恢复数据，首先要登录数据库.

可视化工具恢复

数据库列表区域右键“从SQL转储文件导入数据库”，指定要执行的SQL文件，执行即可。

1.3 MySQL数据库密码重置

停止mysql服务器运行输入services.msc 停止mysql服务
在cmd下,输入mysqld --console --skip-grant-tables 启动服务器,出现一下页面,不要关闭该窗口
新打开cmd,输入mysql -uroot 不需要密码

use mysql;
 flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

关闭两个cmd窗口重启服务使用新密码即可

第二章多表操作

2.1 表与表之间的关系

一对多关系：
- 常见实例：客户和订单，分类和商品，部门和员工.
- 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键.
多对多关系：
- 常见实例：学生和课程、用户和角色
- 多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

2.2 外键约束

外键特点：
- 从表外键的值是对主表主键的引用。
- 从表外键类型，必须与主表主键类型一致。

声明外键约束

语法：
alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

[外键名称]用于删除外键约束的，一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称

使用外键目的：
- 保证数据完整性

2.3 一对多操作

分析

category分类表，为一方，也就是主表，必须提供主键cid
products商品表，为多方，也就是从表，必须提供外键category_id

实现：分类和商品

#创建分类表
create table category(
  cid varchar(32) PRIMARY KEY ,
  cname varchar(100) -- 分类名称
);

# 商品表
CREATE TABLE `products` (
  `pid` varchar(32) PRIMARY KEY  ,
  `name` VARCHAR(40) ,
  `price` DOUBLE 
);

#添加外键字段
alter table products add column category_id varchar(32);

#添加约束
alter table products add constraint product_fk foreign key (category_id) references category (cid);

操作

#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');

#2 向商品表添加普通数据,没有外键数据，默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');

#3 向商品表添加普通数据，含有外键信息(category表中存在这条数据)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');

#4 向商品表添加普通数据，含有外键信息(category表中不存在这条数据) -- 失败,异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');

#5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 'c001';

2.4 多对多

分析

商品和订单多对多关系，将拆分成两个一对多。
products商品表，为其中一个一对多的主表，需要提供主键pid
orders 订单表，为另一个一对多的主表，需要提供主键oid
orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid

实现：订单和商品

#商品表[已存在]

#订单表
create table `orders`(
  `oid` varchar(32) PRIMARY KEY ,
  `totalprice` double   #总计
);

#订单项表
create table orderitem(
  oid varchar(50),-- 订单id
  pid varchar(50)-- 商品id
);

#订单表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid);

#商品表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid);

#联合主键（可省略）
alter table `orderitem` add primary key (oid,pid);

操作

#1 向商品表中添加数据
INSERT INTO products (pid,pname) VALUES('p003','商品名称');

#2 向订单表中添加数据
INSERT INTO orders (oid ,totalprice) VALUES('x001','998');
INSERT INTO orders (oid ,totalprice) VALUES('x002','100');

#3向中间表添加数据(数据存在)
INSERT INTO orderitem(pid,oid) VALUES('p001','x001');
INSERT INTO orderitem(pid,oid) VALUES('p001','x002');
INSERT INTO orderitem(pid,oid) VALUES('p002','x002');

#4删除中间表的数据
DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002';

#5向中间表添加数据(数据不存在) -- 执行异常
INSERT INTO orderitem(pid,oid) VALUES('p002','x003');

#6删除商品表的数据 -- 执行异常
DELETE FROM products WHERE pid = 'p001';

第三章多表查询

提供表结构如下：

CREATE DATABASE day02;
USE day02;

# 分类表
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);

#商品表
CREATE TABLE products(
  pid  INT PRIMARY KEY AUTO_INCREMENT ,
  pname VARCHAR(50),
  price DOUBLE,
  flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
  cid VARCHAR(32),
  CONSTRAINT products_fk FOREIGN KEY (cid) REFERENCES category (cid)
);

4.1 初始化数据

#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,cid) VALUES(1,'联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,cid) VALUES(2,'海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,cid) VALUES(3,'雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,cid) VALUES(4,'JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES(5,'真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES(6,'花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,cid) VALUES(7,'劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,cid) VALUES(8,'香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,cid) VALUES(9,'相宜本草',200,'1','c003');

4.2 多表查询

交叉查询

--交叉查询取的是两张表的笛卡尔积
select * from category,products;

select * from category cross join products;

内连接

--内连接查询的是两张表的交集
-- 隐式内连接

select p.*,c.cname from category c,products p where c.cid = p.cid;


-- 显示内连接

select  p.*,c.cname from  category c inner join products p on c.cid = p.cid;
select  p.*,c.cname from  category c join products p on c.cid = p.cid;

外连接

-- 左外连接 以左表为基准 左表有的都查询出来 右表没有 显示null值

select *  from category c left join products p  on c.cid = p.cid;


-- 左外连接 查询左表有 右表没有的记录

select  * from  category c left join products p on c.cid = p.cid where p.cid is null;

-- 右外连接以右表为基准右表有的内容都查询出来左表没有显示null值 select * from category c right join products p on c.cid = p.cid ; -- 右外连接查询右表有但是左表没有的数据 select * from category c right join products p on c.cid = p.cid where c.cid is null;

全外连接(mysql不支持)

-- 查询两张表的并集  mysql不支持
select * from category c full join products p  on c.cid = p.cid;
-- 可以通过union实现 
SELECT * FROM category c LEFT JOIN products p  ON c.cid = p.cid 
UNION
SELECT * FROM category c RIGHT JOIN products p ON c.cid = p.cid;

-- 查询张表不相交的 A表有B表没有   B表有A表没有
select  * from  t_category c full outer join t_product p on c.cid = p.cid where c.cid is null
or  p.cid is null;

-- 可以通过union实现 
SELECT * FROM category c LEFT JOIN products p  ON c.cid = p.cid  WHERE p.cid IS NULL
UNION
SELECT * FROM category c RIGHT JOIN products p ON c.cid = p.cid WHERE c.cid IS NULL;

union

#union 将多条语句的查询结果 合并成一个结果
CREATE TABLE us(
    id INT PRIMARY KEY AUTO_INCREMENT,
    uname VARCHAR(100),
    gender VARCHAR(100)
);  
INSERT INTO us VALUES
(NULL,'john','male'),
(NULL,'lucy','female'),
(NULL,'jack','male'),
(NULL,'rose','female');

CREATE TABLE ch(
    id INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100),
    sex VARCHAR(100)
);
INSERT INTO ch VALUES
(NULL,'张三','男'),
(NULL,'李四','女'),
(NULL,'王五','男');

#查询所有性别为男的信息 
SELECT  id,cname FROM  ch WHERE sex ='男'
UNION ALL
SELECT id,uname FROM us WHERE gender = 'male';

4.3 子查询

子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。

语法：select ....查询字段 ... from ... 表.. where ... 查询条件

#3 子查询, 查询“化妆品”分类上架商品详情
#隐式内连接
SELECT p.* 
FROM products p , category c 
WHERE p.category_id=c.cid AND c.cname = '化妆品';

#子查询
##作为查询条件
SELECT * 
FROM products p 
WHERE p.category_id = 
    ( 
        SELECT c.cid FROM category c 
            WHERE c.cname='化妆品'
    );
    
##作为另一张表
SELECT * 
FROM products p , 
     (SELECT * FROM category WHERE cname='化妆品') c 
WHERE p.category_id = c.cid;


子查询练习：

#查询“化妆品”和“家电”两个分类上架商品详情
SELECT * 
FROM products p 
WHERE p.category_id in 
    (SELECT c.cid 
     FROM category c 
     WHERE c.cname='化妆品' or c.name='家电'
    );

相关子查询(了解)

-- 查询 分类id 分类名称 以及每个商品的个数
-- 连接查询
select c.cid,c.cname,count(p.cid) num
    from category  c left join products p
        on c.cid = p.cid group by p.cid;
-- 连接查询
select  c.cid,c.cname,if(p.num is null,0,p.num) from
    (select cid ,count(*) num from products  group by  cid ) p
    right join category c on c.cid = p.cid;        

-- 相关子查询
-- 先执行主查询，再针对主查询返回的每一行数据执行子查询。


select cid,cname,(select count(*) from products p where p.cid = category.cid) num from category;



-- 非相关子查询 非相关子查询执行顺序是先执行子查询，再执行主查询。
select * from products where cid in (select  cid from category where cname = '化妆品'  );
--相关子查询 
-- 相关子查询：先执行主查询，再针对主查询返回的每一行数据执行子查询，如果子查询能够返回行，则这条记录就保留，否则就不保留。
/*
    （1）从外层查询中取出一个元组，将元组相关列的值传给内层查询。
    （2）执行内层查询，得到子查询操作的值。
    （3）外查询根据子查询返回的结果或结果集得到满足条件的行。
    （4）然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。 

*/
select * from products p where exists (select * from category c where c.cid = p.cid  and  c.cname ='化妆品'  );

-- 如果查询数量比较小时：非相关子查询比相关子查询效率高。
-- 如果查询数量比较大时：一般不建议使用in，因为in的效率比较低，我们可以使用相关子查询。

4.4 case when语法

CREATE TABLE persons(
    pid INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100),
    age INT ,
    sex VARCHAR(100)
);
INSERT INTO persons(NAME,age,sex) VALUES
('liuyan',38,'女'),
('tangyan',18,'女'),
('jinlian',28,'女'),
('dalang',8,'男'),
('ximenqing',80,'男');

SELECT  *,IF(sex = '男', 'man', 'woman') gender FROM persons;
-- 第一种格式
SELECT *,
       CASE sex
           WHEN '男' THEN 'man'
           WHEN '女' THEN 'woman'
           ELSE 'other' END AS english_sex
FROM persons;
-- 第二种格式
SELECT *,
       CASE           
           WHEN sex = '男' THEN 'man'
           WHEN sex = '女' THEN 'woman'
           ELSE 'other'
           END AS english_sex
FROM persons;


-- 对年龄进行分段 
/*
    如果年龄在 [10,20)之间  则 显示 10~20
    如果年龄在 [20,30)之间  则 显示 20~30
    如果年龄在 [30,40)之间  则 显示 30~40
    如果不在这些范围 则显示 other

*/
SELECT *,
       IF(age >= 10 AND age < 20, '10~20',
            IF(age >= 20 AND age < 30, '20~30', IF(age > 30 AND age < 40, '30~40', 'other'))) AS age_stage
FROM persons;

select *,
       case
           when age >= 10 and age < 20 then '10~20'
           when age >= 20 and age < 30 then '20~30'
           when age >= 30 and age < 40 then '30~40'
           else 'other' end as age_stage
from t_user;