sql server学习笔记-day3

--创建部门表
create table dept
(depno int primary key,
depName nvarchar(30),
depLocal nvarchar(30))

-- 插入部门数据
insert into dept values (10, 'ACCOUNTING', 'NEW YORK')
insert into dept values (20, 'RESEARCH', 'DALLAS')
insert into dept values (30, 'SALES', 'CHICAGO')
insert into dept values (40, 'OPERATIONS', 'BOSTON')

--创建员工表
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),
depno int foreign key references dept(depno) --将这里的字段depno设置成表dept的depno字段的外键，		
)
-- 插入雇员数据
insert into emp (empno,ename,job,mgr,hiredate,sal,depno) 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,depno) 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,depno) values (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,depno) values (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,depno) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,depno) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,depno) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,depno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,depno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,depno) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,depno) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);

简单查询

--①查询所有列：select * from 表名 where 条件
select * from emp
select * from dept
--查询指定列：select 字段1，字段2 from 表名 where 条件
--查询smith的薪水，工作，所在部门
select sal,job,depno from emp where ename='smith'

--②如何取消重复行？
select depno from emp
select distinct depno from emp  --distinct只能消除完全一样的行，只保留一行
select distinct depno,ename from emp  --有时depno一样，但ename不同，则将保留重复的depno

--③如何处理null的问题？
--显示每个雇员的年工资
select ename,sal*13 年工资 from emp  --年工资是别名
--加上奖金comm，但是奖金的值可能是null
select ename,sal*13+isnull(comm,0)*13 年工资 from emp

--④如何显示工资高于3000的员工
select * from emp where sal>3000
--如何显示工资在2000-2500之间的员工情况
select * from emp where sal>2000 and sal<2500 
select * from emp where sal between 2000 and 2500 --等价于sal>=2000 and sal<=2500

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

--⑥如何使用like操作符？
-- %:表示0到多个任意字符
-- _：表示单个任意字符

--显示首字符为S的员工姓名和工资
select ename,sal from emp where ename like 'S%'
--显示第三个字符为o的所有员工姓名个工资
select ename,sal from emp where ename like '__o%'

--⑦在where条件中使用in
--如何显示empno为123,345,800...的雇员情况？
select * from emp where empno=123 or emp=345 or empno=800 --这种方式不好
--一般使用 in 这个关键字，效率更高
select * from emp where empno in(123,345,800)

--⑧如何显示没有上级的雇员的情况
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 字句

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

--order by可以根据 不同的字段排序
--按照部门号升序，而部门内部雇员工资降序排列
select * from emp order by depno, sal desc

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

复杂查询

--如何显示所有员工的最高工资(max)和最低工资(min)
select min(sal) from emp
--如何显示最低工资和该雇员的名字
select ename,sal from emp where sal=(select min(sal) from emp)

--显示所有员工的平均工资和工资总和（avg,sum）
select avg(sal) 平均工资,sum(sal) 总工资 from emp
--把高于平均工资的雇员的名字和工资显示出来。并显示平均工资？
select ename,sal from emp where sal>(select avg(sal) from emp)

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

--group by 和 having 字句
--如何显示每个部门的平均工资和最高工资
select depno,avg(sal) 平均工资,max(sal) 最高工资 from emp group by depno --先对部门进行分组，然后在每个部门内部进行计算
--若想同时显示部门名称，部门名称在表dept中，涉及多表查询

--显示每个部门的每种岗位的平均工资和最低工资
select depno,job,avg(sal) 平均工资,min(sal) 最低工资 from emp group by depno,job order by depno --按照条件的先后顺序执行
 
--having往往与group by一起使用，可以对分组查询结果进行筛选
--显示平均工资低于2000的部门号和它的平均工资,并按照从低到高的顺序排列
select depno,avg(sal) from emp group by depno having avg(sal)<2000 --第二个avg(sal)不能用别名代替
order by avg(sal) asc

--注意：如果在select语句中同时包含group by, having, order by字句，那么他们的顺序是group by > having > order by