目录

多表关系

#一对多

#多对多

-- 创建学生表
create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
)comment '学生表';
-- 插入数据
insert into student values (null,'黛绮丝','2000100101'),(NULL,'谢逊','2000100102'),(null,'鹰天正','2000100103'),(null,'韦一笑','2000100104');

-- 创建课程表
create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
)COMMENT '课程表';

-- 插入数据
insert into course values (null,'Java'),(null,'PHP'),(null,'Hadoop');
insert into course values (null,'MySQL');

-- 创建关联表
create table student_course (
    id int auto_increment comment'主键'PRIMARY KEY ,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course(id),
    constraint fk_studentid foreign key (studentid) references student(id)
)comment '学生课程中间表';
-- 插入数据
insert into  student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

#一对一

/*-----------------------------一对一----------------------------------*/
-- 创建用户信息表
create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1：男，2：女',
    phone char(11) comment '手机号'
)comment '用户基本信息表';

alter table tb_user change gennder gender char(1) comment'1：男，2：女';

create table tb_user_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    maior varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    universy varchar(50) comment '大学',
    userid int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
)comment '用户教育信息表';

insert into tb_user(id,name,age,gender,phone)values
                                                 (null,'黄渤',45,'1','18800001111'),
                                                 (null,'冰冰',35,'2','18800002222'),
                                                 (null,'马云',55,'1','18800008888'),
                                                 (null,'李彦宏',50,'1','18800009999');

insert into tb_user_edu(id, degree, maior, primaryschool, middleschool, universy, userid) values
                                                                                              (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',5),
                                                                                              (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',6),
                                                                                              (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',7),
                                                                                              (null,'本科','应用数学','阳泉曲第一小学','阳泉曲第一中学','清华大学',8);

多表查询

#概述

#多表查询分类

#连接查询——内连接

select * from emp,dept;

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

-- 内连接演示
-- 1、查询每一个员工的姓名，及关联部门的名称（隐式内连接实现）
-- 表结构：emp,dept
-- 连接条件：emp,dept_id=dept.id
select emp.name,dept.name from dept,emp where emp.dept_id=dept.Id;

select emp.name e,dept.name d from emp,dept where emp.dept_id=dept.Id;

-- 2.查询每一个员工的姓名，及关联的部门名称（显示内连接实现）
-- 表结构：emp,dept
-- 连接条件：emp,dept_id=dept.id
select e.name,d.name from emp e inner join dept d on e.dept_id=d.Id;

#连接查询——外连接

-- 外连接演示
-- 1、查询emp表的所有数据，和对应的部门信息（左外连接）
-- 表结构：emp，dept
-- 连接条件：emp.dept_id=dept.ID

select e.*,d.name from emp e left outer join dept d on e.dept_id=d.Id;

select e.*,d.name from emp e left join dept d on e.dept_id=d.Id;

-- 2、查询dept表的所有数据，和对应的员工信息（右外连接）

select d.*,e.name from emp e right outer join dept d on e.dept_id = d.Id;

-- 一般情况下左外连接用的比较多，因为右外连接可以转换为左外连接
select d.*,e.name from dept d left join emp e on d.Id = e.dept_id;

#连接查询——自连接

-- 1. 创建示例表（员工表，包含员工ID、姓名、上级ID）
CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  manager_id INT, -- 上级ID，关联自身的id字段
  department VARCHAR(50),
  -- 自连接的外键约束（可选，用于保证数据完整性）
  FOREIGN KEY (manager_id) REFERENCES employee(id)
);

-- 2. 插入测试数据
INSERT INTO employee (id, name, manager_id, department) VALUES
(1, '张三', NULL, '管理层'), -- 最高级领导，没有上级
(2, '李四', 1, '技术部'),    -- 上级是张三（id=1）
(3, '王五', 1, '市场部'),    -- 上级是张三（id=1）
(4, '赵六', 2, '技术部'),    -- 上级是李四（id=2）
(5, '孙七', 3, '市场部');    -- 上级是王五（id=3）

-- 查询每个员工和其对应的上级姓名
-- e:员工 m:上级
select
    e.name 员工姓名,m.name 上级姓名
from employee e
Left join employee m on e.manager_id=m.id;

-- 4. 进阶：查询技术部员工及其上级（仅显示有上级的员工）
SELECT
  e.name AS 员工姓名,
  m.name AS 上级姓名
FROM employee e
INNER JOIN employee m ON e.manager_id = m.id
WHERE e.department = '技术部';

#联合查询

-- 1. 创建示例表（学生表和教师表，结构相似）
CREATE TABLE student (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  department VARCHAR(50), -- 院系
  role VARCHAR(10) DEFAULT '学生' -- 角色标识
);

CREATE TABLE teacher (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  department VARCHAR(50), -- 院系
  role VARCHAR(10) DEFAULT '教师' -- 角色标识
);

-- 2. 插入测试数据
INSERT INTO student (id, name, department) VALUES
(1, '张三', '计算机系'),
(2, '李四', '数学系'),
(3, '王五', '计算机系');

INSERT INTO teacher (id, name, department) VALUES
(101, '赵老师', '计算机系'),
(102, '钱老师', '数学系'),
(103, '孙老师', '计算机系');

-- 3. 基本联合查询（UNION）：合并计算机系的学生和教师
-- 特点：自动去除重复行（如果存在完全相同的记录）
SELECT name, department, role
FROM student
WHERE department = '计算机系'
UNION
SELECT name, department, role
FROM teacher
WHERE department = '计算机系';

-- 4. 保留重复行的联合查询（UNION ALL）
-- 场景：如果需要保留所有结果（包括重复项），用UNION ALL（效率比UNION高）
SELECT name, department, role
FROM student
WHERE department = '计算机系'
UNION ALL
SELECT name, department, role
FROM teacher
WHERE department = '计算机系';

-- 5. 联合查询后排序（对合并后的结果整体排序）
SELECT name, department, role
FROM student
WHERE department = '计算机系'
UNION
SELECT name, department, role
FROM teacher
WHERE department = '计算机系'
ORDER BY department, role; -- 先按院系排序，再按角色排序

样例给的不行，结果都是一样的

#子查询

#标量子查询

-- 创建部门表
CREATE TABLE dept (
    id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

-- 插入部门数据
INSERT INTO dept (id, dept_name) VALUES
(1, '开发部'),
(2, '市场部'),
(3, '财务部'),
(4, '销售部'),
(5, '行政部');

-- 创建员工表
CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    job VARCHAR(50),
    salary INT,
    entrydate DATE,
    managerid INT,
    dept_id INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id),
    CONSTRAINT fk_emp_manager FOREIGN KEY (managerid) REFERENCES employee(id)
);
-- 删除表
-- DROP TABLE employee;

-- 插入数据
INSERT INTO employee (id, name, age, job, salary, entrydate, managerid, dept_id) VALUES
(1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5),
(2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
(3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
(4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
(5, '常遇春', 43, '开发', 10500, '2000-04-07', 3, 1),
(6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
(7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
(8, '周芷若', 19, '会计', 4800, '2006-06-02', 7, 3),
(9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
(10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
(11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
(12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
(13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
(14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
(15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
(16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
(17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL);

-- 1、查询销售部的所有员工信息
-- a：查询'销售部'的所有员工信息
select id from dept where dept_name='销售部';
-- b:根据销售部的id查询员工信息
select * from employee where employee.dept_id=4;
-- 合并
select * from employee where employee.dept_id=(select id from dept where dept_name='销售部');

-- 2、查询在东方白入职之后的员工信息
-- a查询东方白的入职信息
select employee.entrydate from employee where  name='方东白';
-- b查询制定入职时间之后的员工信息
select * from employee where entrydate>'2009-02-12';
-- 合并
select * from employee where entrydate>(select employee.entrydate from employee where  name='方东白');

#列表子查询

-- 列子查询
-- 查询销售部和市场部的所有员工信息
select * from employee where dept_id in (select id from dept where dept.dept_name='销售部' or dept_name='市场部');

- 查询比财务部所有人工资都高的员工信息
select id from dept where dept_name='财务部';

select employee.salary from employee where employee.dept_id=(select id from dept where dept_name='财务部');

select * from employee where salary > all(select employee.salary from employee where employee.dept_id=(select id from dept where dept_name='财务部'));

-- 查询比研发部其中任意一人工资都高的员工信息
select id from dept where dept_name='开发部';

select employee.salary from employee where employee.dept_id=(select id from dept where dept_name='开发部');

select * from employee where salary > any (select employee.salary from employee where employee.dept_id=(select id from dept where dept_name='开发部'));

#行子查询

-- 行子查询
-- 1、查询与"张无忌"的薪资及直属领导相同的员工信息
-- a查询“张无忌”的薪资及直属领导
select employee.salary,employee.managerid from employee where name='张无忌';
-- b查询与'张无忌'的薪资及直属领导相同的员工信息
select * from employee where (salary,managerid) =(select employee.salary,employee.managerid from employee where name='张无忌');

#表子查询

-- 表子查询
-- 1。查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
-- a查询鹿杖客宋远桥的职位和薪资
select employee.job,employee.salary from employee where name='宋远桥' or name ='鹿杖客';

-- 查询与鹿杖客宋远桥的职位和薪资
select * from employee where (job,salary)in (select employee.job,employee.salary from employee where name='宋远桥' or name ='鹿杖客');

-- 2查询入职时间是2006-01-01之后的员工信息，及其部门信息
-- a入职日期是“2006-01-01”之后的员工信息
select * from employee where entrydate>'2006-01-01';

-- b查询这部分员工，
-- b查询这部分员工，对应的部门信息
select e.*,d.* from (select * from employee where entrydate>'2006-01-01') e left join dept d on e.dept_id=d.id;

总结