员工管理系统
之后很多例子都是基于下面所建数据库和表的
1. ---创建dept表
2. create table dept
3. (
4. int primary key, --部门编号
5. --部门名称
6. --所在地
7. );
8.
9. --创建emp表
10. create table emp
11. (
12. int primary key, --员工编号
13. --员工姓名
14. --员工职位
15. int, --上级编号
16. --入职日期
17. numeric(8, 2), --薪水
18. numeric(8, 2), --奖金
19. int foreign key references dept(deptno) --deptno是外键
20. );
21.
22. --外键
23. --①外键引用的键必须是主键
24. --②外键的数据类型必须和引用的主键数据类型一致
25.
26. --向dept表中添加数据
27. insert into dept values (10,'accounting','new york');
28. insert into dept values (20,'research','dallas');
29. insert into dept values (30,'sales','chicago');
30. insert into dept values (40,'operations','boston');
31.
32. --向emp表中添加数据
33. insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7369,'smith','clerk',7902,'1980-12-17',800.00,20);
34. insert into emp values(7499,'allen','salesman',7698,'1981-2-20',1600.00,300.00,30);
35. insert into emp values(7521,'ward','salesman',7698,'1981-2-22',1250.00,500.00,30);
36. insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7566,'jones','manager',7839,'1981-4-2',2975.00,20);
37. insert into emp values(7654,'martin','salesman',7698,'1981-9-28',1250.00,1400.00,30);
38. insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7698,'blake','manager',7839,'1981-5-1',2850.00,30);
39. insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7782,'clark','manager',7839,'1981-6-9',2450.00,10);
40. insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7788,'scott','analyst',7566,'1987-4-19',3000.00,20);
41. insert into emp (empno,ename,job,hiredate,sal,deptno) values (7839,'king','president','1981-11-17',5000.00,10);
42. insert into emp values (7844,'turner','salesman',7698,'1981-9-8',1500.00,0.00,30);
43. insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7876,'adams','clerk',7788,'1987-5-23',1100.00,20);
44. insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7900,'james','clerk',7698,'1981-12-3',950.00,30);
45. insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7902,'ford','analyst',7566,'1981-12-3',3000.00,20);
46. insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7934,'miller','clerk',7782,'1982-1-23',1300.00,10);
表的简单查询
[sql] view plain copy
1. --表的简单查询
2. --查询部门信息
3. select * from dept;
4.
5. --查询员工信息
6. select * from emp;
7.
8. --查询SMITH的薪水，工作，所在部门
9. select sal, job, deptno
10. from emp where ename = 'SMITH';
11.
12.
13. --查询员工表中有多少个部门
14. --distinct会消除全部属性一样的行,只保留一行
15. select distinct deptno from emp;
16. select distinct deptno, ename from emp;
17.
18.
19. --显示每个员工的年工资
20. --as后面是别名，可以不加as，别名也可以不加引号
21. --四则运算，空值问题,用isnull()解决
22. select ename, (sal * 12) + isnull(comm * 12, 0) as '年工资' from emp;
23.
24. --显示工资高于3000的员工
25. select * from emp where sal > 3000;
26.
27. --查找.1.1以后入职的员工
28. select * from emp where hiredate > '1982-1-1';
29.
30. --显示工资在到的员工
31. select * from emp where sal > 2000 and sal < 2500;
32.
33. --between A and B表示A >= X <= B
34. select * from emp where sal between 2001 and 2499;
35.
36. --显示首字符为S的员工的姓名和工资
37. select ename, sal from emp where ename like 'S%';
38.
39. --显示第三个字符是O的员工姓名和工资
40. select ename, sal from emp where ename like '__O%';
41.
42. --显示empno为123,345,800的员工
43. select * from emp where empno = 123 or empno = 345 or empno = 800;
44. select * from emp where empno in(123, 345, 800);
45.
46. --查询没有上级的员工
47. select * from emp where mgr is null;
48.
49. --查询工资高于或者是岗位为MANAGER的员工，并且姓名首字母为J
50. select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
51.
52. --按照工资从低到高显示员工
53. --order by ASC 默认升序
54. --order by DESC 降序
55. select * from emp order by sal ASC;
56.
57. --按照入职先后顺序显示员工
58. select * from emp order by hiredate ASC;
59.
60. --按照名字升序排列
61. select * from emp order by ename ASC;
62.
63. --按照部门升序，员工的工资降序显示
64. --order by 可以根据不同的字段排序，order by a, b;
65. select * from emp order by deptno ASC, sal DESC;
66.
67. --算出每个人的年薪，并按照升序显示
68. select ename, sal * 13 + isnull(comm * 13, 0) as '年薪' from emp order by '年薪' ASC;
表的复杂查询
数据分组 -max(最大),min(最小),avg(平均),sum(和),count(统计)
group by和having子句
group by用于对查询的结果分组统计
having子句用于限制分组显示结果
[sql] view plain copy
1. --表的复杂查询
2. --显示所有员工中工资最高的员工
3. select ename, sal
4. from emp
5. where sal = ( select max(sal) as '最高薪水' from emp);
6.
7. --显示所有员工中工资最低的员工
8. select ename, sal
9. from emp
10. where sal = ( select min(sal) as '最低薪水' from emp);
11.
12. --显示所有员工的平均工资和总工资
13. select avg(sal) as '平均工资', sum(sal) as '总工资' from emp;
14.
15. --把高于平均工资的员工的名字和工资显示
16. select ename, sal from emp where sal > (select avg(sal) from emp);
17.
18. --显示员工名并显示平均工资
19. select ename, sal,(select avg(sal) from emp) as '平均工资' from emp where sal > (select avg(sal) from emp );
20.
21. --计算一共有多少员工
22. select count(*) as '员工总数' from emp;
23.
24. --显示每个部门的平均工资和最高工资
25. select deptno,avg(sal) as '平均工资', max(sal) as '最高工资' from emp group by deptno;
26.
27. --显示每个部门的每个岗位的平均工资和最低工资
28. select deptno, job, avg(sal) as '平均工资', min(sal) as '最低工资' from emp group by deptno, job order by deptno ASC, '平均工资' DESC;
29.
30. --显示平均工资低于2000的部门号和平均工资
31. --group by ... having ... having是对分组后的数据进行筛选
32. select deptno, avg(sal) as '平均工资' from emp group by deptno having avg(sal) < 2000;
对数据分组的总结
1、分组函数只能出现在选择列表，having、order by子句中
2、如果在select语句中同时包含有group by，having，ovrder by那么他们的顺序是group by，having，order by
3、在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必需有一个出现在group by子句中，否则就会出错
4、如果想在分组情况下，选择列中显示某列，那么这行必须要在group by子句中，否则会出错
如：select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000
这里deptno就一定要出现在group by中
表的复杂查询--多表查询
多表查询是指基于两个或两个以上的表或是视图的查询，在实际应用中，查询单个表可能不能满足需求，(如显示sales部门位置和其员工的姓名)，这种情况下需要使用到(dept表和emp表)
[sql] view plain copy
1. --多表查询
2. --如果多张表都有相同名字的字段，则需要带表名(别名)
3. --显示sales部门位置和其员工的姓名
4. select loc, ename from dept, emp where dname = 'sales' and dept.deptno = emp.deptno;
5.
6. --显示员工名，部门名和部门编号
7. --连接的表中有相同的字段，需要带表名(别名)
8. select dept.deptno, dname, ename from dept, emp where dept.deptno = emp.deptno;
9.
10. --显示部门号为10的部门名，员工名和工资
11. select d.deptno, e.ename, e.sal from emp e, dept d where e.deptno = d.deptno and d.deptno = 10;
12.
13. --显示员工名，员工工资及所在部门的名称，并按部门排序
14. select e.ename, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno order by d.deptno;
表的复杂查询--自连接查询
自连接是指在同一张表的连接查询
[sql] view plain copy
1. --自关联查询，内连接
2. --显示'FORD'的上级领导的姓名
3. select e2.ename from emp e1, emp e2 where e1.mgr = e2.empno and e1.ename = 'FORD';
4.
5. --显示每个员工的名字和他的上级姓名
6. select e1.ename,e2.ename from emp e1 emp e2 where e1.mgr = e2.empno;
表的复杂查询--子查询
子查询是批嵌入在其它sql语句中的select语句，也叫嵌套查询
单行子查询
单行子查询是指子查询只返回一行数据的子查询语句
[sql] view plain copy
- --显示与SMITH同一部门的所有员工
- select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
多行子查询
多行子查询指子查询返回多行数据的子查询
[sql] view plain copy
1. --查询和部门10的工作相同的员工的名字
2. select ename from emp where job = any(select distinct job from emp where deptno = 10);
3. select ename from emp where job in(select distinct job from emp where deptno = 10);
4.
5. --查询和部门的工作相同的员工的名字并且不包括号部门的员工
6. select ename from emp where job in(select distinct job from emp where deptno = 10) and deptno != 10;
在from子句中使用子查询
当在from子句中使用子查询时，该子查询会被作为一个临时表来对待，当在from子句中使用子查询时，必需给子查询指定别名
[sql] view plain copy
- --
1. 显示高于部门平均工资的员工的信息
2. select emp.ename, emp.deptno, emp.sal
3. from emp, (select deptno, avg(sal) as '部门平均工资' from emp group by deptno) e2
4. where emp.deptno = e2.deptno and emp.sal > e2.部门平均工资;
分页查询
[sql] view plain copy
1. --显示第5个到第10个入职的员工(按照时间的先后顺序)
2. --top后面的数表示要取出几条记录
3. select top 6 * from emp where empno not in
4. (select top 4 empno from emp order by hiredate)
5. order by hiredate;
6.
7. --显示第11个到第13个入职的员工(按照时间的先后顺序)
8. select top 3 * from emp where empno not in
9. (select top 10 empno from emp order by hiredate)
10. order by hiredate;
11.
12. --显示第5个到第9个员工(按照薪水的降序)
13. select top 5 * from emp where empno not in
14. (select top 4 empno from emp order by sal DESC)
15. order by sal DESC;
16.
17. --演示分页性能，PS:可以试试看自己电脑性能怎样
18. --第一步：建一个表
19. --第二步：利用一条语句成指数增长插入数据
20. --第三步：测试查询全部的时间
21. --第四步：测试分页查询的时间
22.
23.
24. --第一步：建表
25. create table test
26. (
27. int primary key identity(1, 1), --自增主键
28. varchar(30), --账号
29. varchar(30) --密码
30. );
31.
32. --第二步：插入数据
33. insert into test values('test','test');
34.
35. --指数增长复制插入,一共插入w+记录
36. insert into test(tName, tPass) select tName,tPass from test;
37.
38. --第三步：查询全部的时间
39.
40. --查询全部信息用时:45s
41. select * from test;
42.
43. --查询tId 用时:39s
44. select tId from test;
45.
46. --第四步：查询分页的时间
47.
48. --查询第100-105的数据按照id排序用时:0.1s
49. select top 6 * from test where tId not in
50. (select top 99 tId from test);
51.
52. --查询第500000-550000的数据按照id排序用时:6s
53. select top 50001 * from test where tId not in
54. (select top 499999 tId from test);
55.
56.
57. --删除一张表的重复记录
58. --把test的记录distincth后的结果，放入#temp
59. select distinct tName, tPass into #temp from test
60. --清除test中数据表
61. delete from test
62. --把#temp表的数据(没有重复记录),插入到test表中
63. insert into test(tName, tPass) select tName, tPass from #temp
64. --删除#temp
65. drop table #temp;
表的复杂查询--外连接查询
[sql] view plain copy
1. --显示每个员工和他上级的名字，没有上级的人，也要显示出来
2. --左外连接:指左边的表的记录全部显示，如果没有匹配的记录就用NULL填写
3. select e1.ename as '下级', e2.ename as '上级' from emp e1 left join emp e2 on e1.mgr = e2.empno;
4. --右外连接:指右边的表的记录全部显示，如果没有匹配的记录就用NULL填写
5. select e1.ename as '下级', e2.ename as '上级' from emp e1 right join emp e2 on e1.mgr = e2.empno;本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
