数据库的简单操作1

数据库的简单操作1

create database EmpMana

drop table dept
create table dept
(deptid int primary key ,
 deptname nvarchar(50),
 deptloc nvarchar(50)
 )

create table emp
( empid int primary key,
  empname nvarchar(50),
  empjob nvarchar(50),
  empmgr int,
  emphiredate datetime,
  empsal numeric(10,2),
  empcomn numeric(10,2),
  deptid int foreign key references dept(deptid)
)

delete from dept
insert into dept values(1,'销售部门','广州')
insert into dept values(2,'软件开发部门','上海')
insert into dept values(3,'软件研发部门','北京')

select * from dept
delete from emp
insert into emp values(1,'张三','总裁',null,'2001-01-01',10000.01,1000.02,1)
insert into emp values(2,'lisi','销售总监',1,'2002-02-02',9000.01,9000.02,1)

insert into emp values(3,'li3','软件部技术总监',2,'2002-02-02',8000.01,8000.02,2)
insert into emp values(35,'li31','软件部',3,'2002-02-02',8000.01,8000.02,2)
insert into emp values(36,'li31','软件部',35,'2002-02-02',8000.01,8000.02,2)
insert into emp values(37,'li31','软件部',36,'2002-02-02',8000.01,8000.02,2)
insert into emp values(38,'li31','软件部',37,'2002-02-02',8000.01,8000.02,2)

insert into emp values(4,'li4','软件研发部技术总监',2,'2002-02-02',8000.01,8000.02,3)
insert into emp values(41,'li41','软件',4,'2002-02-02',8000.01,8000.02,3)
insert into emp values(42,'li42','研发部',41,'2002-02-02',8000.01,8000.02,3)
insert into emp values(43,'li43','研发部',42,'2002-02-02',8000.01,8000.02,3)
insert into emp values(44,'li44','研发部',43,'2002-02-02',8000.01,8000.02,3)
insert into emp values(45,'li45','研发部',44,'2002-02-02',8000.01,8000.02,3)

select distinct deptid,empname from emp

--显示每个雇员的年工资
select distinct empname as '员工姓名',empsal*13 as '年工资' from emp

select distinct empname as '员工姓名',empsal*13 + empcomn*13 as '年工资' from emp


select * from emp where emphiredate>'2010-04-04'

select * from emp where empsal>2000 and empsal<2500

select * from emp where empsal>9000 and empsal<1000000

select * from emp
select empname,empsal from emp where empname like 'LI3%'

select empname,empsal from emp where empname like '__4%'

select * from emp where empid in(3,35,36,37)

select * from emp where empmgr is null

select * from emp order by empsal asc

select * from emp order by empsal desc

select min(empsal) from emp

--如何显示最低工资和该雇员的名字
select min(empsal) from emp

select empname,empsal from emp where empsal=(select max(empsal) from emp)

--显示所有员工的平均工资和工资总和
select avg(empsal) as '平均工资' ,sum(empsal) as '总工资' from emp

--把高于平均工资的雇员的名字和他的工资

--统计共有多少个员工
select count(*) from emp


--如何显示每个部门的平均工资和最高工资

select avg(empsal),deptid from emp group by deptid

select avg(empsal),deptid ,max(empsal) from emp group by deptid

--显示每个部门的每种岗位的平均工资和最低工资
select avg(empsal),min(empsal),deptid,empjob from emp group by deptid,empjob

select avg(empsal),min(empsal),deptid,empjob from emp group by deptid,empjob order by deptid

--显示平均工资低于2000的部门号和他的平均工资
--having 往往和group by 结合使用，可以对分组查询结果进行筛选

select avg(empsal),deptid from emp group by deptid having avg(empsal) <80000
select avg(empsal) vv,deptid from emp group by deptid having avg(empsal)<20000
--having 往往和group by 结合使用，可以对分组查询结果进行筛选 并从高到低排列
select avg(empsal) vv,deptid from emp group by deptid having avg(empsal)<20000
order by avg(empsal) asc