create database test
use test

部门表
create table department(
dept_id int not null identity primary key,主键
dept_no char(4) not null unique,编号
dept_name varchar(20) not null名称
)
insert into department values('D001','财务部')
insert into department values('D002','研发部')
insert into department values('D003','业务部')
职位表
create table job(
job_id int not null identity primary key,主键
job_no char(4) not null unique,编号
job_name varchar(20) not null,名称
job_salary float not null工资
)
insert into job values('J001','web前端',5000)
insert into job values('J002','java开发',8000)
insert into job values('J003','c#开发',6000)
insert into job values('J004','财务管理',7000)
insert into job values('J005','业务管理',7000)
员工表
create table employee(
emp_id int not null identity primary key,主键
emp_no char(4) not null unique,编号
emp_name varchar(20) not null,姓名
emp_sex nchar(1) not null,性别
emp_phone char(11) not null,手机号
emp_date date not null,入职日期
job_no char(4) foreign key references job(job_no),职位编号，外键管理职位表的职位编号
dept_no char(4) foreign key references department(dept_no)部门编号，外键管理部门表的部门编号
)
insert into employee values('E001','张三','男','11111111111','20170101','J001','D002')
insert into employee values('E002','莉莉','女','45341321324','20170101','J001','D002')
insert into employee values('E003','李四','男','22222222222','20170101','J002','D002')
insert into employee values('E004','张凯','男','11212121212','20170101','J002','D002')
insert into employee values('E005','王丹','女','35453155125','20170101','J002','D002')
insert into employee values('E006','王五','男','33333333333','20170101','J003','D002')
insert into employee values('E007','孙迪','男','47651215451','20170101','J003','D002')
insert into employee values('E008','赵六','男','44444444444','20170101','J004','D001')
insert into employee values('E009','唐舞','女','83212133200','20170101','J005','D003')

内连接
1.查询所有职员的:职员编号,姓名,职位名称,工资,部门名称
select 字段 fr 表1 inner join 表2 on 条件匹配
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.dept_no=c.dept_no
2.查询性别是女的职员的:职员编号,姓名,职位名称,工资,部门名称,性别
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no
inner join department c on a.dept_no=c.dept_no
where a.emp_sex='女'
3.查询每个部门的:部门名称,人数,平均工资,最大工资,最小工资,工资总和
select c.dept_name,
COUNT(*) as 人数,
avg(b.job_salary) as 平均工资,
max(b.job_salary) as 最大工资,
min(b.job_salary) as 最小工资,
sum(b.job_salary) as 工资总和
fr employee a inner join job b on a.job_no=b.job_no
inner join department c on a.dept_no=c.dept_no
group by c.dept_name
子查询
1.查询部门是 研发部 的所有职员的:职员编号,姓名,职位名称,工资,部门名称
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.dept_no=(select dept_no fr department where dept_name='研发部')

2.查询职位是 web前段 的所有职员的:职员编号,姓名,职位名称,工资,部门名称
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.job_no=(select job_no fr job where job_name='web前端')

3.查询工资大于等于7000的所有职员的:职员编号,姓名,职位名称,工资,部门名称
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.job_no in(select job_no fr job where job_salary>=7000)

外连接
1.将三张表做一个全链接关联查询全部信息
select * fr employee a full join department b on a.dept_no=b.dept_no full join job c on a.job_no=c.job_no

集合操作
1.部门 财务部和业务部 并集查询:职员编号,姓名,职位名称,工资,部门名称
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.dept_no=(select dept_no fr department where dept_name='财务部')
union all
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.dept_no=(select dept_no fr department where dept_name='业务部')

2.职位 java开发和c#开发 差集查询：职员编号,姓名,职位名称,工资,部门名称
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.job_no=(select job_no fr job where job_name='java开发')
except
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.job_no=(select job_no fr job where job_name='c#开发')
3.职员表 id<=3和id<=7 交集查询：职员编号,姓名,职位名称,工资,部门名称
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.emp_id<=3
intersect
select a.emp_no,a.emp_name,b.job_name,b.job_salary,c.dept_name
fr employee a inner join job b on a.job_no=b.job_no inner join department c
on a.dept_no=c.dept_no
where a.emp_id<=7