SQL - 多表关联

最新推荐文章于 2024-09-18 10:25:05 发布
最新推荐文章于 2024-09-18 10:25:05 发布
阅读量3.4k 收藏 14
点赞数 1
分类专栏： 数据库 文章标签： sql 关联表
原文链接：https://blog.csdn.net/lx678111/article/details/82725548
版权

一、首先，我们可以查看数据库的各种变量：


1.SHOW VARIABLES 查看数据库的变量
2.查看当前数据库的编码
SHOW VARIABLES WHERE variable_name LIKE 'character%';
3.解决中文乱码，如下三个编码保持一致就可以。
character_set_client、character_set_connection、character_set_results


二、子查询

4.case-when then end

SELECT sno,sname,age,saddress,(CASE sex WHEN '0' THEN '女' WHEN '1' THEN '男' ELSE '泰国' END)AS

性别 FROM sstud;

5.无关子查询
//查询具有相同年龄的人 --每年年龄段只显示一个代表

SELECT * FROM sstud GROUP BY age HAVING COUNT(age)>=2;

SELECT * FROM sstud WHERE age IN(

SELECT age FROM sstud GROUP BY age HAVING COUNT(age)>=2

);


//再加条件 age>20

SELECT * FROM sstud WHERE age IN(

SELECT age FROM sstud GROUP BY age HAVING COUNT(age)>=2 AND age>20

);

6.相关子查询

SELECT xs.name,xs.age, th.name, count(*) as c FROM sstud as xs, teacher as th WHERE age IN(

SELECT age FROM sstud where th.age>45 and c>2 GROUP BY age HAVING COUNT(age)>=2;

);

三、表与表之间的关系
数据库实体间有三种对应关系：一对一，一对多，多对多。
（一）、一对一
比如：夫妻就是一对一的关系（以下以夫妻表为例）：

方式1：用两个独立的表来实现---项目用得更多的方式

CREATE TABLE wm(
  id INT PRIMARY KEY,
  NAME VARCHAR(10),
  sex CHAR(1)
);


CREATE TABLE mm(
  id INT PRIMARY KEY,
  NAME VARCHAR(10),
  sex CHAR(1),
  wid INT UNIQUE,
  // 注：外键，这一句表明wid属性与wm表中的id属性是一对一的，若没有这句则是一对多的
  CONSTRAINT mm_fk FOREIGN KEY(wid) REFERENCES wm(id)  // 注：设置外键
);

查询夫妻信息

SELECT mm.name AS 丈夫, wm.name AS 妻子 FROM mm, wm WHERE mm.wid=wm.id

方式2：用一个物理表来实现，同时创建两个虚拟表(视图)
需要两个表。当然做项目时为了省空间，通常只建一个表，如果要实现一对一的查询，可以建两个视图。示例如下：
1)建物理表，初始化数据

CREATE TABLE person(
   id INT,
   NAME VARCHAR(10),
   sex CHAR(1),
   wife INT,
   husband INT
);
INSERT INTO person VALUES(1,'小花','0',0,3);
INSERT INTO person VALUES(2,'玉芬','0',0,4);
INSERT INTO person VALUES(3,'张三','1',1,0);
INSERT INTO person VALUES(4,'李四','1',2,0);
INSERT INTO person VALUES(5,'王五','1',0,0);


2) 建立两个视图

create view women as select * from person where sex='0';

create view men as select * from person where sex='1';

3) 查询夫妻信息

//旧版本
SELECT women.name AS 妻子, men.name AS 丈夫 FROM women, men WHERE women.husband = men.id;

---------------------------------
//采用内联接--98以后的新方式--效率更高
SELECT women.name AS 妻子, men.name AS 丈夫 FROM women INNER JOIN men ON women.husband = men.id;

这里就要讲到 关联了：

INNER JOIN（内联）：两个表a,b 相连接，取出符合连接条件的字段

LEFT JOIN（左联）：先返回左表的所有行，再加上符合连接条件的匹配行

RIGHT JOIN（右联）：先返回右表的所有行，再加上符合连接条件的匹配行

1、内连接——最常用 

定义：仅将两个表中满足连接条件的行组合起来作为结果集。

在内连接中，只有在两个表中匹配的行才能在结果集中出现 

关键词：INNER JOIN

格式：SELECT 列名表 FROM 表名1 [INNER] JOIN 表名2 ON或WHERE 条件表达式 
说明： 
（1）列名表中的列名可以出自后面的两个表，但如果两个表中有同名列，应在列名前标明出处，格式为：表名.列名

（2）若连接的两个表名字太长，可以为它们起个别名。 格式为：表名 AS 别名 

（3）INNER是默认方式，可以省略

 2、左连接
定义：在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL

关键字：LEFT JOIN

注意: 

当在内连接查询中加入条件是，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况

就不同了。

当把条件加入到 join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。

3、右连接
定义：在内连接的基础上，还包含右表中所有不符合条件的数据行，并在其中的左表列填写NULL

关键字：RIGHT JOIN

4、完全连接 （mysql不支持）
定义：在内连接的基础上，还包含两个表中所有不符合条件的数据行，并在其中的左表、和右表列填写NULL

关键字：FULL JOIN

5.外连接（mysql不支持）
关键字:OUTTER JOIN

关联，可理解成把几个合成一个新的表，然后在新表中进行查询
 
（二）、一对多
数据库设计分析
※案例：一个人可以拥有多辆汽车，要求查询出某人所拥有的所有汽车。
方案一(差的设计)：

编号   姓名  性别   年龄   汽车编号   车型  排量  价格
P001  Jack   男     25     C001     BMW    12L   80w
P001  Jack   男     25     C002     Benz   12L   100w
P001  Jack   男     25     C003     Benz   12L   100w
P002  Tom    男     25     C004     BMW    12L   80w
P002  Tom    男     25     C005     Benz   12L   100w
P003  Rose   女     25     C006     Benz   12L   100w

方案二(好的设计)：
1）把一方单独建个表

编号   姓名  性别   年龄
P001  Jack   男     25
P002  Tom    男     25
P003  Rose   女     25


2）把多方也建个表(依赖一方,通过外键--补一个字段)
外键:位于依赖一方，它是被依赖一方是主键

汽车编号   车型  排量  价格    车主
 C001     BMW    12L   80w    P001
 C002     Benz   12L   100w   P001
 C003     Benz   12L   120w   P001
 C004     BMW    12L   80w    P002
 C005     Benz   12L   100w   P002
 C006     Benz   12L   100w   P003

3) 代码实现
CREATE TABLE person2(
   id varchar(32) primary key,
   NAME VARCHAR(30),
   sex CHAR(1),
   age INT
);
INSERT INTO person2 VALUES('P1001','小花','0',25);
INSERT INTO person2 VALUES('P1002','张三','1',22);
INSERT INTO person2 VALUES('P1003','Jack','1',24);
INSERT INTO person2 VALUES('P1004','Rose','0',25);

CREATE TABLE car(
   id varchar(32) primary key,
   NAME VARCHAR(30),
   price numeric(10,2),
   pid varchar(32),  
   /*为字段pid定义一个外键约束(来自person2表的字段id)*/
   constraint car_fk foreign key(pid) references person2(id)
);
INSERT INTO car VALUES('C001','BMW',80.5,'P1001');
INSERT INTO car VALUES('C002','Benz',100,'P1001');
INSERT INTO car VALUES('C003','BMW',120.05,'P1001');
INSERT INTO car VALUES('C004','Benz',88.5,'P1002');
INSERT INTO car VALUES('C005','QQ',8.5,'P1002');
INSERT INTO car VALUES('C006','BIKE',0.5,'P1003');

 1.哪些人有哪些车 

SELECT * FROM person2 INNER JOIN car ON person2.id=car.pid;

SELECT person2.NAME,car.id,car.NAME,car.price FROM  person2 INNER JOIN car ON person2.id=car.pid WHERE car.price>50;

2.Mike的车辆信息

SELECT person2.NAME,person2.age,car.id,car.NAME,car.price FROM  person2 LEFT JOIN car ON person2.id=car.pid WHERE person2.NAME='Mike'

3.哪些人没有车

SELECT person2.NAME,person2.age,car.NAME FROM  person2 LEFT JOIN car ON person2.id=car.pid WHERE car.NAME IS NULL;

4.哪些人至少有两辆车

//新版
SELECT person2.NAME,car.id FROM person2 INNER JOIN car ON person2.id=car.pid GROUP BY person2.NAME HAVING COUNT(*)>=2;
//旧版
SELECT NAME FROM person2 WHERE id IN('P1001','P1002');
SELECT NAME FROM person2 WHERE id IN( SELECT pid FROM car GROUP BY pid HAVING COUNT(*)>1 );

（三）、多对多
数据库设计分析

※案例：一个人可以选择多门课程，一门课程又可以被很多人选择。

方案一(差的设计)：
1)学生表
编号   姓名  性别   年龄  电话 ...
P001  Jack   男     25
P002  Tom    男     25
P003  Rose   女     25
--------------------------
2)课程表
编号   名称   教材  学分...  学生
S001   Java   ...  ......   P001
S001   Java   ...  ......   P002
S001   Java   ...  ......   ...
S002   数据库  ... ......   P001
S002   数据库  ... ......   P002
......

方案二(好的设计:两个实体表+一个关系表)：
1)学生表(独立)---实体
编号   姓名  性别   年龄  电话 ...
P001  Jack   男     25
P002  Tom    男     25
P003  Rose   女     25
2)课程表(独立)---实体
编号   名称   教材  学分...
S001   Java   ...  ......
S002   数据库  ... ......
S003   XML   ... ......
3)选课表(专为体现多对多的关系而新增的表)--关系
课程编号  学生编号
S001      P001
S001      P002
...
S002      P002
S002      P003
...
S003      P001
...

 外键      外键
   |        |
   |━━━━|  
       |
     联合主键
 

4) 代码实现
CREATE TABLE stud2(
   id varchar(32) primary key,
   NAME VARCHAR(30),
   age INT
);


CREATE TABLE ject(
   id varchar(32) primary key,
   NAME VARCHAR(30)
);


CREATE TABLE sj(

studid varchar(32),

subjectid VARCHAR(32)

);


//单独添加约束(注意顺序: 要先添加联合主键，再添加外键)

//创建联合主键

ALTER TABLE sj ADD CONSTRAINT pk_sj PRIMARY KEY(studid,subjectid);

//创建两个外键

alter table sj add constraint fk_stud foreign key(studid) references stud(id);

alter table sj add constraint fk_subj foreign key(subjectid)references ject(id);

//删除外键---先添加外键再添加联合主键时，不行。删除外键，重来

ALTER TABLE sj DROP FOREIGN KEY fk_stud;

ALTER TABLE sj DROP FOREIGN KEY fk_subj;

1.查询哪些人选了哪些课
//92标准

select stud.name,ject.name  from stud,ject,sj  where stud.id=sj.studid and ject.id = sj.subjectid;

//96标准
select stud.name,ject.name  from
    stud inner join sj  on stud.id=sj.studid
         inner join ject on  ject.id = sj.subjectid;

2查询哪些人没有选课

//92标准

SELECT  stud.name FROM stud WHERE stud.id NOT IN (SELECT  studid FROM sj ); 

//96标准--左关联

SELECT  stud.name FROM stud LEFT JOIN sj  ON stud.id=sj.studid LEFT JOIN ject ON  ject.id = sj.subjectid

WHERE ject.NAME IS NULL;

//3查询哪些课程没人选

//96标准--左关联

SELECT  ject.name FROM
    ject LEFT JOIN sj  ON ject.id=sj.subjectid
         LEFT JOIN stud ON  stud.id = sj.studid
    WHERE stud.NAME IS NULL;

//96标准--右关联
SELECT  ject.name,stud.name FROM
    stud RIGHT JOIN sj  ON stud.id=sj.studid
         RIGHT JOIN ject ON  ject.id = sj.subjectid
    WHERE stud.NAME IS NULL;

※演示自动增长列与字段值唯一性约束

CREATE TABLE aa(
       id INT AUTO_INCREMENT PRIMARY KEY,
       nm VARCHAR(32) UNIQUE
);
INSERT INTO aa(nm) VALUES('uuu');
 

MySQL进阶-02-多表设计
JAVA编程——元鸿的博客
02-08 519
二、多表设计 1.一对一(了解) 分析 人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！ 实现原则 在任意一个表建立外键，去关联另外一个表的主键 SQL演示 -- 创建db5数据库 CREATE DATABASE db5; -- 使用db5数据库 USE db5; -- 创建person表 CREATE TABLE person( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); -- 添加数据 INSERT IN
SQL多表连接查询、多表关联
08-24
sql多表连接查询inner join, left join , right join ,full join ,cross join Posted on 2008-05-07 21:55 我开始懂了 阅读(21431) 评论(9) 编辑 收藏 inner join,full outer join,left join,right jion 内部连接 inner join 两表都满足的组合 full outer 全连 两表相同的组合在一起，A表有，B表没有的数据（显示为null）,同样B表有 A表没有的显示为(null) A表 left join B表 左连,以A表为基础，A表的全部数据，B表有的组合。没有的为null A表 right join B表 右连,以B表为基础，B表的全部数据，A表的有的组合。没有的为null
SQL查询数据之多表（关联）查询
热门推荐
weixin_49756833的博客
03-27 3万+
数据表： 关联查询主要分为：（inner join）交叉关联、（left join）左关联、（right join）右关联 （inner join）交叉关联： 定义：（inner join）交叉关联只返回两个表联结相等的字段的行 语法：select * from 表1 inner join 表2 on 表1.字段号=表2.字段号 比如我要查学生表和成绩表的交叉关联，那首先要找到它们联结相等的字段 它们唯一相同的字段就是学号这个字段 那么按照上面的语法写就是： ...
SQL 多表联查
最新发布
Lzcsfg的博客
09-18 3180
内联接（INNER JOIN）：返回两个表中匹配的记录。左外联接（LEFT JOIN）：返回左表的所有记录和右表中匹配的记录（右表可能为NULL右外联接（RIGHT JOIN）：返回右表的所有记录和左表中匹配的记录（左表可能为NULL全外联接（FULL JOIN）：返回两个表的所有记录（MySQL 通过UNION模拟）。交叉联接（CROSS JOIN）：返回两个表的笛卡尔积。自联接（SELF JOIN）：表与自身的联接，通常用于记录间的比较。
左连接，右边接，内连接查询
twtiqfn的专栏
02-23 991
一、外连接 外连接可分为：左连接、右连接、完全外连接。 1、左连接  left join 或 left outer join select * from student left join sc on student.sno=sc.sno 左外连接包含left join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL). 2、右连接  right
sql 多表关联
代号1998的专栏
11-17 489
select a.*,b.deviceName from device_oper a left join device_info b on a.deviceId = b.idSELECT o.*,a.deviceName,p.projectName,b.branchName,r.releaseId FROM device_info a RIGHT JOI
SQL：SQL-92，SQL-99和SQL-2003的BNF语法
01-30
SQL-92、SQL-99和SQL-2003是SQL标准的三个重要版本，分别在1992年、1999年和2003年由国际标准化组织（ISO）发布，它们在SQL语法和功能上进行了多次扩展和改进。 SQL-92是SQL的第一个正式标准，它为SQL提供了更严谨...
SQL update 多表关联更新的实现代码
09-09
- **更新限制**：UPDATE语句本身不支持直接更新多表，但在FROM子句中加入关联的表后，实际上实现了对多表的联合操作。 - **关联条件**：FROM子句中的JOIN或直接的表名组合（如`A, B`）必须有一个或多个关联条件，以...
rsql-jpa-specification:Java库将RSQL转换为Spring Data JPA规范和QueryDSL谓词
04-08
将RSQL查询转换为org.springframework.data.jpa.domain.Specification或com.querydsl.core.types.Predicate和support实体关联查询。 Maven仓库 将RSQL的rsql-jpa-spring-boot-starter添加到Spring JPA转换 Maven对r...
结合mybatis-plus实现简单不需要写sql的多表查询
08-25
在多表查询方面，MyBatis-Plus 提供了简单易用的方法，让开发者能够通过实体类之间的关联关系进行联查。 在 MyBatis-Plus 中，实现多表查询的关键在于实体类的关联和注解的使用。首先，你需要确保项目中已经集成了 ...
6.多表连接（SQL基础）
ruyigongfang的博客
08-22 981
内连接：按照关联条件关联，将两表中都符合关联条件（匹配成功的）的数据展示出来； 左外连接：按照关联条件关联，左表作为主表，将左表所有数据展示出来，右表（匹配表）中不满足关联条件的以空值填充； 右外连接：按照关联条件关联，右表作为主表，将右表所有数据展示出来，左表（匹配表）中不满足关联条件的以空值填充；
[sql server][基础] 多表关联创建
永生天地
07-26 3669
if object_id('tableCD') is not null drop table tableCD---多对多要加个中间表goif object_id('tableD') is not null drop table tableDgoif object_id('tableC') is not null drop table tableCgoif object_id('tableB') is not null drop table tableBgoif object_id('tableA') is
sql多表关联
fulq1234的专栏
06-26 1万+
student表，score表，student的id字段和score的studentid字段关联。student表中有1,2，而score表中有2,3。student表中有score表中没有的1,score表中有student表中没有的3.有一个交集是2。drop table student; create table student( id int not null primary key, ...
SQL多表关联
x_xiaoge的专栏
02-03 504
内连接     左连接   全外连接       Oracle中可以使用“(+) ”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN LEFT OUTER JOIN：左外关联 SELECT e.last_name, e.department_id, d.department_name FROM employees
SQL中表关联
weixin_62487913的博客
06-16 403
在SQL中，表关联是一种将多个表中的数据按特定条件合并在一起的操作。这种操作在涉及复杂数据模型和多表查询时尤为重要。表关联可以帮助我们从不同的表中提取相关数据，并在一个查询中组合它们，从而使得数据分析和处理更加灵活和高效。
【数据库】sql函数和多表关联查询
weixin_48814356的博客
02-13 4188
聚合函数（count函数；AVG函数；SUM函数； MAX函数； MIN函数）；数值型函数；字符串函数；日期和时间函数；流程控制函数；内连接；左连接；右连接；自连接；交叉连接
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值