1.创建名称为bumen、yuangong、gognzi的表。
create table bumen
(bumen_id int primary key,
bumen_mch nvarchar(100) not null
)
create table yuangong
(
yuangong_id int primary key,
yuangong_name nvarchar(100) not null,
yuangong_gz int,
bumen_id int
)
create table gongzi
(
gongzi_id int primary key,
gongzi_hi int,
gongzi_lw int
)
2.向表中添加数据
insert into bumen values(10,'研发');
insert into bumen values(20,'测试');
insert into bumen values(30,'人力');
select * from bumeninsert into yuangong values(1,'张三',5000,10);
insert into yuangong values(2,'李四',10000,20);
insert into yuangong values(3,'王五',15000,10);
delete from yuangong where yuangong_id=3;
select * from yuangonginsert into gongzi values(1,5000,1500);
insert into gongzi values(2,10000,5001);
insert into gongzi values(3,15000,10001);
select * from gongzi
3.语句的顺序也是不可以改变的。
select *
--"B".bumen_mch "部门名称",AVG("Y".yuangong_gz) "部门平均工资"
from bumen "B"
join yuangong "Y"
on "B".bumen_id="Y".bumen_id
join gongzi "G"
on "Y".yuangong_gz<="G".gongzi_hi and "Y".yuangong_gz>="G".gongzi_lw
执行结果:
bumen_id | bumen_mch | yuangong_id | yuangong_name | yuangong_gz | bumen_id | gongzi_id | gongzi_hi | gongzi_lw |
10 | 研发 | 1 | 张三 | 5000 | 10 | 1 | 5000 | 1500 |
20 | 测试 | 2 | 李四 | 10000 | 20 | 2 | 10000 | 5001 |
10 | 研发 | 3 | 王五 | 15000 | 10 | 3 | 15000 | 10001 |
select *
--"B".bumen_mch "部门名称",AVG("Y".yuangong_gz) "部门平均工资"
from bumen "B"
join yuangong "Y"
on "B".bumen_id="Y".bumen_id
join gongzi "G"
on "Y".yuangong_gz<="G".gongzi_hi and "Y".yuangong_gz>="G".gongzi_lw
where "Y".yuangong_gz>5000 --对聚合表的详细内容进行筛选。
执行结果:
bumen_id | bumen_mch | yuangong_id | yuangong_name | yuangong_gz | bumen_id | gongzi_id | gongzi_hi | gongzi_lw |
20 | 测试 | 2 | 李四 | 10000 | 20 | 2 | 10000 | 5001 |
10 | 研发 | 3 | 王五 | 15000 | 10 | 3 | 15000 | 10001 |
select "B".bumen_mch "部门名称",AVG("Y".yuangong_gz) "部门平均工资"
from bumen "B"
join yuangong "Y"
on "B".bumen_id="Y".bumen_id
join gongzi "G"
on "Y".yuangong_gz<="G".gongzi_hi and "Y".yuangong_gz>="G".gongzi_lw
where "Y".yuangong_gz>5000
group by "B".bumen_mch --对聚合表进行分组
部门名称 | 部门平均工资 |
测试 | 10000 |
研发 | 15000 |
select "B".bumen_mch "部门名称",AVG("Y".yuangong_gz) "部门平均工资"
from bumen "B"
join yuangong "Y"
on "B".bumen_id="Y".bumen_id
join gongzi "G"
on "Y".yuangong_gz<="G".gongzi_hi and "Y".yuangong_gz>="G".gongzi_lw
where "Y".yuangong_gz>5000
group by "B".bumen_mch
having AVG("Y".yuangong_gz)>10000 --对分组表的内容进行筛选。
执行结果:
部门名称 | 部门平均工资 |
研发 | 15000 |