[4]多表查询

芈子的小屋
68 阅读5分钟

（1）mysql的表关系：

  • 一对一
  • 一对多
  • 多对多

一对一表关系

person表

idname
1张三
2李四

card表

idnamepid外键（person表主键）
14666561
24568662

实操：

CREATE TABLE person (
id int PRIMARY KEY auto_increment,
name VARCHAR(20)
);

INSERT into person VALUES (null,'张三'),(null,'李四');
SELECT * FROM person;

CREATE TABLE card (
id int PRIMARY KEY auto_increment,
name VARCHAR(20),
pid int UNIQUE,
CONSTRAINT p_c FOREIGN KEY (pid) REFERENCES person(id)
);
//这里pid给了一个唯一约束，是因为这里是一对一的，所以pid不能有重复；

//给card表插入数据：
INSERT into card VALUES (null,'466656',1),(null,'456866',2);
SELECT * FROM card;

image.png

image.png

多对多表关系

student表

idname
1张三
2李四

course表

idname
1语文
2数学

stu_course表（中间表）

idsiduid
111
212
321
422

中间表中，sid是一个外键，是记录和表student的关系； cid也是一个外键，是记录和课程表的关系； 从stu_course表（中间表）中可以看出，张三选了语文课和数学课，李四也选了语文课和数学课；

CREATE TABLE student_information(
id int PRIMARY KEY auto_increment,
name VARCHAR(20)
)ENGINE=INNODB;

INSERT into student_information VALUES (null,'张三'),(NULL,'李四');
SELECT * FROM student_information;

CREATE TABLE course (
id int PRIMARY KEY auto_increment,
name VARCHAR(20)
)ENGINE=INNODB;

INSERT into course VALUES (null,'语文'),(NULL,'数学');
SELECT * FROM course;

CREATE TABLE stu_course(
id int PRIMARY KEY auto_increment,
sid int,
cid int,
CONSTRAINT s_c1 FOREIGN KEY (sid) REFERENCES student_information(id),
CONSTRAINT s_c2 FOREIGN KEY (cid) REFERENCES course(id)
)ENGINE=INNODB;
SELECT * FROM stu_course;

INSERT into stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);
SELECT * FROM stu_course;

image.png

image.png

image.png

（2）mysql多表查询：

mindmap
      mysql多表查询
          内连接
          外连接
          union查询
          子查询
user表
idnameage
1张三23
2李四24
3王五25
4赵六26
orderlist表
idnumberuid
1hm0011
2hm0021
3hm0032
4hm0042
5hm0053
6hm0063
7hm007null
category表
idname
1手机数码
2电脑办公
3烟酒茶糖
4鞋靴箱包
product表
idnamecid
1华为手机1
2小米手机1
3联想电脑2
4苹果电脑2
5中华香烟3
6玉溪香烟3
7计生用品null
us_pro表(多对多中间表)
upiduidpid
111
212
313
414
515
616
717
821
922
1023
1124
1225
1326
1427
1531

（1）内连接（inner join）是数据库查询中的一种连接操作，它用于将两个或多个表中的数据按照一定的条件关联起来，形成一个新的虚拟表。这个新的表包含了所有满足连接条件的行。

内连接通常用于查询两个或多个表中相关联的数据，如果想要查询用户和对应的订单信息则可以使用；
语法：sellect 列名  from 表名1 [inner] join 表名2 on 关联条件;
sellect * from user u inner join orderlist o on o.uid=u.id;
也用条件查询实现：
sellect * from user u,orderlist o where o.uid=u.id;
（隐式内连接，本质上没有用内连接，但达到了内连接查询的效果）

sellect u.name,u.age,o.number from user u inner join orderlist o on o.uid=u.id;

（2）外连接（Outer Join）是SQL查询中的一种特殊类型的连接，它允许查询两个或多个表，并返回至少在一个表中匹配的行；

 左外连接：返回左表的所有行，即使右表中没有匹配的行。如果右表中没有匹配的行，则结果中这些行的右表部分将包含null；
 标准语法：sellect 列名 from 表名1 left [outer] join 表名2 on 条件;
 
 右外连接：返回右表(right join 右侧的表)的所有行，即使左表中没有匹配的行，则结果中这些行的左表部分将包含null;
 标准语法：sellect 列名  from 表名1 right [outer] join 表名2 on 条件;

union查询

union查询允许将两个或多个sellect语句的[结果集]合并成一个结果集；这些sellect语句必须拥有相同数量的列，并且对应列的数据类型也需要兼容；

使用union时，默认情况下，SQL会自动去除结果集中的重复行。如果想要包含所有的重复行，可以使用union all；

子查询(联合查询)

子查询：在一个SQL查询语句中嵌套另一个完整的查询语句，以获得更精确或相关的数据；

语法：
(1)
sellect 列名 from 表名 where 列名 = (sellect 列名  from 表名 [where 条件]);
(2)
sellect 列名 from 表名 where 列名 = [not] in (sellect 列名 from 表名 [where 条件]);
(3)
sellect 列名 from 表名 [别名]，(sellect 列名 from 表名 [where 条件][别名][where 条件]);
例如：
sellect name,age from user where age=(sellect max(age) from user);
例如：查询张三、李四的订单详情
sellect id from user where name in ('张三','李四');
sellect * from orderlist where uid in (1,2);
换成子查询就是:
sellect * from orderlist where uid in (sellect id from user where name in ('张三'),('李四'));

例如：从订单详情表里面id>4 的里面，查询user表的name和订单详情表的number：
sellect u.name,o.number from user u,(sellect * from orderlist where id>4) o where o.uid=u.id;

如果按照常见思维来写的话：

sellect max(age),name from user;
则结果会出现找到了最大的年龄，但名字不匹配的情况；
因为max(age)和name之间没有任何关系；
avatar
文章
阅读
粉丝