SQL server2014 sql语句

--查询所有列

--select * from employee

-- 查询指定列
--select EmployeeNo,EmployeeName from employee;


--查询SMITH的薪水，工作 所在部门 不区分大小写
--select EmployeeName,EmployeeSalary,EmployeeJob,EmployeeDeptNo from employee where EmployeeName = 'SMITH';


--统计有多少个部门 distinct 会把重复的去除掉 只能消除完全一样的行 保留一行
--select distinct EmployeeDeptNo from employee


--显示雇员年薪  在薪水的基础上*13多发一个月薪水 as 别名
--年薪加奖金 isnull可以判断该值是否为空 为空就返回0 null与任何数做运算都是null
--select EmployeeName ,EmployeeSalary*13+(isnull(EmployeeComm,0)*13) as "年薪"  from employee;


--显示工资高于3000的员工
--select* from employee where EmployeeSalary>3000


--显示1982.1.1后入职的员工
--select *from employee where HireData>'1982-1-1'


--显示工资在2000到2500的员工情况
--select* from employee where EmployeeSalary>2000 and EmployeeSalary<2500
--between是显示>= <=
--select * from employee where EmployeeSalary between 2000 and 2500


--显示首字母为S的员工信息
--%表示0到多个字符 


--select * from employee where EmployeeName like 's%'
 
--查找第三个字符为o的员工信息
-- -表示单个字符 也可以是汉字
--select * fro m employee where EmployeeName like '_家%'


--查找编号为9999,7934,7902的信息 使用in关键字
--select *from employee where EmployeeNo in (9999,7934,7902)


--查找没有上级的员工编号 is null
--select *from employee where  EmployeeBoss  is null;


--查询工资高于500 或是 岗位为 manager的雇员 同时还要满足他们的姓名首字母为j
--select * from employee where (EmployeeSalary > 500 or EmployeeJob = 'manager') and EmployeeName like 'j%'


--升序 或者降序显示薪水 order by
--asc升序 desc降序
--select * from employee order by EmployeeSalary desc


--int 入职时间也可以显示 名字也可以
--select *from employee order by HireData asc


--按照部门升序   --在按照 薪水降序
--select * from employee order by EmployeeDeptNo ,EmployeeSalary desc;


--算出每个员工的年薪按高到低排序 用别名排序会更省资源
--select EmployeeName,(EmployeeSalary + isnull(EmployeeComm,0))*13 年薪 from employee order by 年薪 desc;


--找出最低工资 sql语句先从右往左执行  select min(EmployeeSalary) from employee 取出最小值800
--select * from employee where EmployeeSalary = (select min(EmployeeSalary) from employee)


--找出员工的薪水最高值 max 
--select * from employee where EmployeeSalary = (select max(EmployeeSalary) from employee)


--取出总工资--取出平均工资
--select sum(EmployeeSalary) 需要发的工资 ,avg(EmployeeSalary) 平均工资 from employee;
--把高于平均工资的员工信息显示
--select EmployeeName , EmployeeSalary , (select avg(EmployeeSalary) from employee) 平均工资 from employee where EmployeeSalary > (select avg(EmployeeSalary) from employee)


--显示有多少个员工
--select count(*) 员工数 from employee;


--显示每个组的最高工资和平均工资 group by按组分配
--select max(EmployeeSalary) as 最高工资,avg(EmployeeSalary)as 平均工资,EmployeeDeptNo 部门 from employee group by EmployeeDeptNo;


--显示每个部门的平均工资和最低工资
--select avg(EmployeeSalary),min(EmployeeSalary),EmployeeDeptNo,EmployeeJob from employee group by EmployeeDeptNo, EmployeeJob order by EmployeeDeptNo


--显示平均工资低于2000的部门号和它的平均工资
--having 一般与group by 结合使用对分组结果进行查询
select avg(EmployeeSalary) as 部门平均工资,EmployeeDeptNo from employee group by EmployeeDeptNo having avg(EmployeeSalary)<2000;