select * from dbo.Employee
insert into dbo.Employee values
('name9',10,'男',2,2, cast(ceiling(rand() *4000) as int)+1000),
('name10',10,'女',2,2,cast(ceiling(rand() *4000) as int)+1000),
('name11',10,'男',2,2,cast(ceiling(rand() *4000) as int)+1000),
('name12',10,'女',2,2,cast(ceiling(rand() *4000) as int)+1000),
('name13',10,'男',2,2,cast(ceiling(rand() *4000) as int)+1000),
('name14',10,'男',2,2,cast(ceiling(rand() *4000) as int)+1000),
('name15',10,'女',2,2,cast(ceiling(rand() *4000) as int)+1000)
select AVG(age) from dbo.Employee where sex='男'
select * from Employee m,Education d
where m.Education=d.id
and d.EduName='本科'
select sex,MAX(e.Salary), MIN(e.Salary) from Employee e
group by e.Sex
-- 男女各多少人
select COUNT(1) ,sex
from Employee
group by sex
-- 每个岗位有多少人
select COUNT(1), j.JobName
from Employee e,Job j
where e.Job=j.id
group by j.JobName
-- 每个学历有多少人
select COUNT(1),d.EduName
from Employee e, Education d
where e.Education=d.id
group by d.EduName
-- 每个岗位每个月发多少钱
select SUM(salary),j.JobName
from Employee e,Job j
where e.Job=j.id
group by j.JobName
-- 那个岗位发的钱最多
select top 1 SUM(salary),j.JobName
from Employee e,Job j
where e.Job=j.id
group by j.JobName
order by SUM(salary) desc
-- 把查询到数据插入到一张表里面
-- 如果表存在
insert into [新表名]
select [列名] from [原表名]
-- 如果表不存在
select [列名] into [新表名]
from[原表名]
group by 随机数 查询保存到临时表
最新推荐文章于 2021-12-05 22:02:22 发布