简单排列与复杂查询

实例创建数据库及员工和部门表

--创建新数据库
create database spdbl
--创建部门表
create table dept
(
	deptno int primary key,
	dname nvarchar(30),
	loc nvarchar(30)
)

select*from dept

--创建员工表
create table emp
(
	empno int primary key,
	ename nvarchar(30),
	job nvarchar(30),
	mgr int,
	hiredate datetime,
	sal numeric(10,2),
	comm numeric(10,2),

	deptno int foreign key references dept(deptno)			--因为deptno需要设置为外键
)

select*from emp

--1外键只能指向主键
--2外键和主键的类型要一致

添加数据

-- 插入部门数据；
insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');

insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS');

insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO');

insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON')

--插入雇员数据

insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,20);

insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);

insert into emp values (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500.00,30);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,'JONES','MANAGER',7839,'1981-4-2',2975,null,20);

insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);

简单排列(order by)

--按照工资从低到高的顺序显示员工信息
--order by（asc）默认是升序
--order by desc为降序
select*from emp order by sal desc

--按照入职的先后顺序排列
select*from emp order by hiredate 

--按照部门号升序而员工工资降序排列
--order by可以根据多个字段排序
select*from emp order by deptno ,sal desc

--统计每个人的年薪，并按升序排列
select ename ,(sal+isnull(comm,0))*13 nian from emp order by nian

复杂查询

--显示员工中工资最高和最低的
select ename,sal from emp where sal=(select min(sal)from emp)

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

--计算员工总数
select count(*) from emp

--显示每个部门的平均工资和最高工资
--并显示部门名称（多表查询）
select avg(sal),deptno,max(sal) from emp group by deptno 

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

--显示平均工资高于2000的部门号和它的平均工资
--having 往往和group by 结合使用，可以对分组查询结果进行筛选,并升序排列
select avg(sal),deptno from emp group by deptno having  avg(sal)>2000 order by avg(sal) asc