韩顺平java从入门到精通第59讲，建立数据库代码

create table emp (
  empno int primary key,
  ename nvarchar(30),
  job nvarchar(30),
  mgr int,
  hiredate datetime,
  sal numeric(9,2),
  comm numeric(9,2),
  deptno int foreign key references dept(deptno))
  --因为deptno根据需要得做成外键

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);


create database spdb1

create table dept (
deptno int primary key,
dname nvarchar(30),
loc nvarchar(30))

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')




select * from emp
select * from dept

--查询smith的薪水，工作，所在部门
select sal,job,deptno from emp where ename='smith'
--消除重复行
select distinct deptno from emp

--显示每个雇员的年工资
--姓名和年工资是别名+奖金
select ename '姓名' ,sal*13+isnull(comm,0)*13'年工资'  from emp where ename=ename order by sal
--如何显示工资高于3000的员工
select *from emp where sal>3000

--如何查找1982.1.1后入职的员工
select *from emp where hiredate>'1982.1.1'

--如何显示工资在2000和2500之间的员工情况
select *from emp where sal>2000 and sal<2500

--between把两边都包括在内
select *from emp where sal between 2000 and 2500


--模糊查询，如何显示首字母为s的员工姓名和工资
select ename 姓名,sal 工资 from emp where ename like 's%' 

--如何显示第三个字符为大写0的所有员工的姓名和工资
select ename 姓名,sal 工资 from emp where ename like '__O%'


--如何显示empno为123,345,800..的情况呢
select *from emp where empno=123 or empno=345 or empno=800

select *from emp where empno in(123,345,800)

--查询上级为null的人
select *from emp where mgr is null

--查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为j
select *from emp where (sal>500 or job='MANAGER') and ename like 'j%'

--如何按照工资的从低到高的顺序显示雇员的信息
--order by 默认是升序排列(asc)
--oder by  降序  desc
select *from emp order by sal  


--如何按照入职的先后顺序来排序
select *from emp order by hiredate asc 

--按照部门号升序而雇员的工资降序排列
select *from emp order by deptno,sal desc

--统计每个人的年薪，并按照从低到高的顺序排列
select ename 姓名,(sal+isnull(comm,0))*13 年薪 from emp order by 年薪 


--如何显示所有员工中最高工资和最低工资
select  min(sal) 最小值 from emp
--如何显示所有员工中最高工资和最低工资和他的姓名
select ename 姓名,sal 薪水 from emp where sal=(select  min(sal)  from emp)

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


--把高于平均工资的雇员的名字和他的工资显示出来

select ename 姓名,sal 工资,(select avg(sal) from emp) 平均值 from emp where sal>(select avg(sal) from emp)

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

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

--显示每个部门的每种岗位的平均工资和最低工资

select avg(sal) 平均工资,min(sal) 最低工资,deptno 部门号,job 岗位 from emp group by deptno,job order by avg(sal)

--显示平均工资低于2000的部门号和他的平均工资
--having 往往和group by 结合使用，可以对分组查询结果进行筛选
select avg(sal) 平均工资,deptno 部门号 from emp  group by deptno having avg(sal)<2000