• 已更新!宝藏教程！MYSQL-第六章节多表查询(一对一，多对多，一对多)，连接查询（内，外连接），联合查询，子查询 代码例题详解这一篇就够了（附数据准备代码）

       c++知识点合集已经完成欢迎前往主页查看，点点赞点点关注不迷路哦

    点我进入c++第一章知识点合集

    MYSQL第一章节DDL数据定义语言的操作----点我进入

    MYSQL第二章节DDL-数据库操作语言 DQL-数据查询语言----点我进入

    MYSQL第三章节DCL-管理用户，控制权限----点我进入

    MYSQL第四章节常用函数说明----点我进入

    MYSQL第五章节有关约束操作详解----点我进入

    MYSQL-第六章节多表查询

    目录

    多表查询

    多对多

    例子 ：将学生表与课程表建立联系

     一对一

    例子

    多表查询例题

    多表查询 -- 笛卡尔积

    连接查询-内连接

    隐式内连接

    显示内连接 

    例子 

    连接查询-外连接

    左外连接 

    右外连接 

    例子 

    连接查询-自连接 

    例题

    联合查询 -unio,unio all

    例子

    子查询 

    标量子查询

    例子

    列子查询 

    例子

    多表查询

    多表查询基本分为三种(一对多，多对多，一对一) 

    多对多

    对于多对多的关系而言应该需要建立第三张中间表，中间表至少包含两个外键，分别关联两个主键

    例子 ：将学生表与课程表建立联系

    ​创建学生表

    1. CREATE TABLE studen (
    2. id int auto_increment primary key comment '主键id',
    3. name varchar(50),
    4. no int 
    5. )comment '学生表';
    6. insert into student (name,no)  values ('黛绮丝',200100101),('谢逊',200100102),('嬴政天',200100103),('韦一笑',200100104);

    创建课程表

    1. CREATE TABLE course(
    2. id int auto_increment primary key comment '主键id',
    3. name varchar(50) comment '课程名称'
    4. )comment '课程表';
    5. insert into course VALUES (NULL,'JAVA'),(NULL,'PHP'),(NULL,'Mysql'),(null,'hadoop');

    建立第三张表（学生课程关系表）并于上面两张表关联

    1. CREATE TABLE student_course(
    2. id int auto_increment primary key comment '主键id',
    3. studentid int not null comment '学生id', 
    4. courseid int not null comment '课程id',
    5. constraint fk_studentid FOREIGN KEY (studentid) REFERENCES student(id),
    6. constraint fk_courseid foreign key (courseid) references course(id)
    7. )comment '学生课程中间表';
    8. insert into student_course (studentid,courseid) values(1,1),(1,2),(1,3),(2,1),(2,4);

     一对一

     在任意一方加入外键，关联另一方的主键，并且设置外键的唯一约束(UNIQUE)

    例子

    ​

    创建用户基本信息表

    1. CREATE TABLE tb_user(
    2. id int auto_increment primary key comment '主键id',
    3. name varchar(50) comment '姓名',
    4. age int comment '年龄',
    5. gender char(1) comment '性别',
    6. phone varchar(11) comment '电话号码'
    7. )comment '用户基本信息表';
    8. insert into tb_user (name,age,gender,phone) values('黄渤',45,'1','17263648590'),('冰冰',35,'2','17276448590'),
    9. ('码云',55,'1','17364950374'),('李彦宏',50,'1','17263495064');

    创建用户教育信息表并添加外键约束

    1. CREATE TABLE tb_user_edu(
    2. id int auto_increment primary key ,
    3. degree char(2),
    4. major varchar(50),
    5. primaryschool  varchar(50),
    6. middleschool varchar(50),
    7. university varchar(50),
    8. userid int,
    9. constraint fk_userid foreign key (userid) references tb_user(id)
    10. )
    11. insert into tb_user_edu (degree,major,primaryschool,middleschool,university,userid) values
    12. ('本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
    13. ('硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
    14. ('本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
    15. ('本科','应用数学','阳泉第一小学','阳泉第一中学','清华大学',3);

    多表查询例题

    数据准备

    1. -- 准备数据
    2. create table dept(
    3.     id   int auto_increment comment 'ID' primary key,
    4.     name varchar(50) not null comment '部门名称'
    5. )comment '部门表';
    6.  
    7. create table emp(
    8.     id  int auto_increment comment 'ID' primary key,
    9.     name varchar(50) not null comment '姓名',
    10.     age  int comment '年龄',
    11.     job varchar(20) comment '职位',
    12.     salary int comment '薪资',
    13.     entrydate date comment '入职时间',
    14.     managerid int comment '直属领导ID',
    15.     dept_id int comment '部门ID'
    16. )comment '员工表';
    17.  
    18. -- 添加外键
    19. alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
    20.  
    21. INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');
    22. INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
    23.             (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
    24.  
    25.             (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
    26.             (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
    27.             (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
    28.             (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
    29.             (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
    30.  
    31.             (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
    32.             (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
    33.             (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
    34.  
    35.             (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
    36.             (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
    37.             (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
    38.             (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
    39.  
    40.             (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
    41.             (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
    42.             (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
    43.             (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

    ​ ​

    多表查询 -- 笛卡尔积
     

    select * from emp , dept where emp.dept_id = dept.id; 

    连接查询-内连接

    隐式内连接

    SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

    显示内连接 

    SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

    内连接是两张表交集的部分

    例子 

    数据见上面的数据准备

    查询每一位员工的姓名,以及关联部门的名称(隐示内连接实现 )

    SELECT emp.name,DEPT.name FROM emp,dept WHERE emp.dept_id = dept.id;

    ​

    数据见上面的数据准备

    查询每一位员工的姓名,以及关联部门的名称(显示内连接实现 )

    SELECT emp.name ,dept.name from emp join dept on emp.dept_id = dept.id;

    ​

    连接查询-外连接

    左外连接 

    SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

    相当于查询表1(左表)的所有数据包含表1和表2的所有数据

    右外连接 

    SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

     相当于查询表2(右表)的所有数据包含表1和表2的所有数据

    例子 

    数据见上面的数据准备

    查询emp表的所有数据，和对应部门的信息(左外连接)

    select emp.*,dept.name from emp left outer join dept on emp.dept_id = dept.id;

    ​

    数据见上面的数据准备

    查询dept表的所有数据，和对应员工的信息(右外连接)

    select dept.* ,emp.* from dept right outer join emp on emp.dept_id = dept.id;

     ​

    连接查询-自连接 

    SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

    自连接查询，可以是内连接查询，也可以是外连接查询。

    例题

    查询每个员工直属领导的名字

    SELECT a.name,b.name FROM emp a,emp b where a.managerid=b.id;

     查询所有员工及其领导的名字，如果员工没有领导，也需要查询出来

    SELECT a.name,b.name FROM emp a left join dept b on a.managerid=b.id;

     

    联合查询 -unio,unio all

    对于unio查询，就是把多次查询的结果组合起来，形成一个新的查询结果

    1. SELECT 字段名 FROM 表A...
    2. UNION [ALL]
    3. SELECT 字段名 FROM 表B...;

    例子

    将薪资低于5000的员工，和年龄大于50岁的员工全部查寻出来

    1. SELECT * FROM emp where salary <5000
    2. union all
    3. SELECT * FROM emp where age>50;

    有查询出来但是出现了重复的数据我们可以做如下修改 

    1. SELECT * FROM emp where salary <5000
    2. union 
    3. SELECT * FROM emp where age>50;

     

    注意：

    联合查询的多张表需要保持一致，字段类型也需要保持一致。

    union all 会将全部数据直接合并在一起，union会对合并以后的数据去重。

    子查询 

    子查询又称为嵌套查询SQL语句里面嵌套SELECT语句

    SELECT * FROM T1 WHERE COLUMN1 = (SELECT COULMN1 FROM T2);

    子查询外部的语句可以是INSERT/DPDATE/DELETESELECT的任何一个 

    根据子查询的结果不同，分为：

    标量子查询（结果为单个值）

    列子查询（结果为一列）

    行子查询（结果为一行）

    表子查询（结果为多行或者多列）

    标量子查询

    常用操作符：= <> > >= < <=

    例子

    数据准备见上面的数据准备代码

    查询销售部的所有员工信息

    1. --查询销售部的所有员工信息--
    2.  
    3. --查询销售部门的id
    4. SELECT id FROM dept where name='销售部';
    5.  
    6. --根据销售部门的id来查询员工信息--
    7. SELECT * FROM emp where (SELECT id FROM dept where name='销售部');

    查询“方东白”入职之后的员工信息

    1. --查询“方东白”入职之后的员工信息--
    2.  
    3. --查询房东白的入职时间--
    4.  SELECT ENTRYDATE FROM EMP WHERE NAME='方东白';
    5.  
    6.  --查询指定日期之后的入职员工--
    7.  SELECT * FROM emp WHERE entrydate > ( SELECT ENTRYDATE FROM EMP WHERE NAME='方东白');

    列子查询 

    常用操作符：IN NOT IN ANY SOME ALL

    例子

    查询销售部和市场部的所有员工信息

    1.  --查询销售部和市场部的所有员工信息--
    2.  
    3.  --查询销售部和市场部的id--
    4.  SELECT ID FROM dept WHERE NAME='销售部'or name='市场部';
    5.  
    6.  ----根据id查询销售部和市场部的所有员工信息--
    7.  SELECT * FROM emp where dept_id in ( SELECT ID FROM dept WHERE NAME='销售部'or name='市场部');

     查询比财务部所有员工工资都高的员工信息

    1.  -- 查询比财务部所有员工工资都高的员工信息--
    2.  
    3.  --查询财务部的id
    4.  SELECT id FROM DEPT WHERE NAME='财务部';
    5.  
    6.  --查询ID查询财务部所有员工的工资--
    7.  SELECT salary from emp where dept_id=(SELECT id FROM DEPT WHERE NAME='财务部');
    8.  
    9.  --查询比财务部所有员工工资都高的员工信息--
    10.  SELECT * FROM emp where salary >all(SELECT salary from emp where dept_id=(SELECT id FROM DEPT WHERE NAME='财务部'));

     查询比研发部任意员工工资都高的员工信息 

    1.  -- 查询比研发部任意员工工资高的员工信息--
    2.  
    3.  --查询研发部的id
    4.  SELECT id FROM DEPT WHERE NAME='研发部';
    5.  
    6.  --查询ID查询研发部所有员工的工资--
    7.  SELECT salary from emp where dept_id=(SELECT id FROM DEPT WHERE NAME='研发部');
    8.  
    9.  --查询比研发部任意员工工资高的员工信息--
    10.  SELECT * FROM emp where salary >any(SELECT salary from emp where dept_id=(SELECT id FROM DEPT WHERE NAME='研发部'));

    行子查询

    常用穿制符号 = <>  IN  NOT IN

    例子

    查询与张无忌的薪资及直属领导相同的员工信息

    1. --查询与张无忌的薪资及直属领导相同的员工信息--
    2.  
    3. --查询张无忌的薪资以及直属领导--
    4. SELECT salary,managerid  FROM emp where name='张无忌';
    5.  
    6. --查询与张无忌的薪资及直属领导相同的员工信息--
    7. SELECT * FROM emp where (salary,managerid)=(SELECT salary,managerid  FROM emp where name='张无忌');

    表子查询 

    常用操作符: IN

    --查询与鹿杖客，宋远桥的职位和薪资相同的员工信息--

    1. --查询鹿杖客，宋远桥的职位和薪资--
    2. SELECT salary,job FROM emp where name='鹿杖客'or name='宋远桥';
    3.  
    4. --查询与鹿杖客，宋远桥的职位和薪资相同的员工信息--
    5. SELECT * FROM emp where (salary,job)in(SELECT salary,job FROM emp where name='鹿杖客'or name='宋远桥');

    查询入职日期是“2006-01-01”之后的员工信息，及其部门信息 

        

    1. --查询入职日期是“2006-01-01”之后的员工信息，及其部门信息 --
    2.  
    3. --查询入职日期是“2006-01-01”之后的员工信息--
    4. SELECT * FROM emp where entrydate>'2006-01-01';
    5.  
    6. --查询入职日期是“2006-01-01”之后的员工信息，及其部门信息 --
    7. SELECT * FROM (SELECT * FROM emp where entrydate>'2006-01-01') a,DEPT WHERE (a.dept_id=dept.id);

  • 相关阅读:
    简单的图片二维码制作教程，你感兴趣吗？
    【第十一章 Set接口概述，HashSet,LinkedHashSet,TreeSet】
    c++复习——名字粉碎
    什么是透明背景格式logo？Logo白底变透明工具测评
    联邦学习（Federated Learning）：技术角度的理解
    第8章：React生命周期
    记一次因为C#官方扩展导致自动补全出错的情况 (C# & Godot)
    知识管理系统如何提升企业核心竞争力
    Mybatis第三天
    Flink - ProcessFunction 使用缓存详解
  • 原文地址：https://blog.csdn.net/weixin_54201821/article/details/134045586