数据库基础-多表查询

create database test;
use test;

create table student(
	id int primary key auto_increment comment '主键ID',
	name varchar(10) comment '姓名',
	no varchar(10) comment '学号'
)comment '学生表';

insert into student values(null, '我是一号', '2024001'),(null, '我是二号', '2024002'),(null, '我是三号', '2024003');


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

insert into course(name) values('Python'), ('数据库概论'), ('离散数学'), ('Java');

create table student_course(
	id int auto_increment primary key comment '主键ID',
	studentid int not null comment '学生ID',
	courseid int not null comment '课程ID',
	constraint fk_studentid foreign key(studentid) references student(id),
	constraint fk_sourseid foreign key(courseid) references course(id)
) comment '学生课程中间表'

insert into student_course values(null, 1, 1), (null, 1, 2), (null, 2, 1), (null, 2, 4), (null, 3, 2), (null, 3, 3); 

-- 若直接查询就等于
select * from 表名1, 表名1; -- 这样会产生笛卡尔积，会输出大量无效信息

-- 所以应该where在外键等于主键的时候输出
select * from emp, dept where emp.dept_id = dept.id;

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


-- 查询员工姓名，及关联的部门名称(显式内连接实现)
-- 表结构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;

select  字段列表  
from  表1  
left 【outer】 join  表2  
on  条件...

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

select  字段列表  
from  表1  
right 【outer】 join  表2  
on  条件...

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

-- 自连接查询语法
select 字段列表 from 表A 别名A join 表A 别名B on 条件;

select a.name, b.name
    from 
        emp as a, emp as b
    where a.managerid = b.id;

select a.name, b.name
    form emp as a
    left outer join emp as b
on a.managerid = b.id;

select 字段列表 from 表A..
union [all]
select 字段列表 from 表B...;

-- 加上all指吧两次查询结果全部输出
-- 不加则是去重之后输出

SQL语句中嵌套select语句， 称为嵌套查询， 又称子查询
select * from t1 where colum1 = (select column1 from t2);

# 1.查询销售部的成员信息
-- dept是部门信息表
-- emp是员工信息表

a.从dept中查询销售表的主键ID
select id from dept where name = '销售部';

b.通过第一步查询到的ID到emp表中查询相关的员工信息
-- select * from emp where dept_id = id; 是这样的一个形式，id就是步骤a的查询结果

-- 两次操作合并后就等于
select * from emp where dept_id = (select id from dept where name = '销售部');



# 2.查询入职时间晚于李牛的入职时间
a.从emp中查询李牛的入职时间
select entrydate from emp where name = '李牛'; -- 返回结果为date

b.再根据查询到的入职时间date查询
select * from emp where entrydate > date;
-- 联合之后为
select * from emo where entrydate > (select entrydate from emp name = '李牛');

# 1.查询销售部和市场部的所有员工信息
-- dept是部门信息表
-- emp是员工信息表

a.查询dept表中这两个部门的主键ID
select id from dept where name in('销售部', '市场部'); -- 得到结果2,4

b.根据部门ID在emp表中查询员工信息
select * from emp where dept_id in(2, 4);
-- 等价于
select * from emp where dept_id in(select id from dept where name in('销售部', '市场部'));


# 2.查询比财务部所有人工资都高的员工信息 -- 主要是使用all操作，可以用max更简单的操作
a.查询所有财务部人员工资
select id from dept where name = '财务部';

select salary from emp where dept_id = (select id from dept where name = '财务部');

b.比财务部所有人工资都高的员工信息
select * from emp where salary > all(select salary from emp where dept_id = (
                                    select id from dept where name = '财务部'));

# 3.查询比财研发部任意一人工资高的员工信息 -- 使用any操作,同2
select * from emp where salsry > any(select salary from emp where dept_id = (
                                    select id from dept where name = '研发部'));

# 查询和张无忌直属部门相同且领导相同的信息
a.查询张无忌的直属部门和领导
select dept_id, managerid from emp where name = '张无忌';

b.查询与张无忌部门与领导相同的信息
select * from emp where (dept_id, managerid) = (select dept_id, managerid 
                                                from emp where name = '张无忌');

# 1.查询和李牛，张无忌，职位和薪资相同的员工信息
a.查询他们的薪资和职位
select job, salary from emp where name in ( '李牛', '张无忌' );

b.查询信息相同的员工信息
select * fromm emp where (job, salary) in ( select job, salary from emp where 
                                                name in ( '李牛', '张无忌' ) );



# 2.查询入职信息在2006-01-01之后的员工信息，及其部门信息
a.查询入职时间在2006-01-01之后的员工信息
select * from emp where entrydate > '2006-01-01';

b.将上述信息作为一张表来处理
select e.*, d.name from (select * from emp where entrydate > '2006-01-01') as e left join
                                                        dept d on e.dept_id = d.id;